summaryrefslogtreecommitdiff
path: root/sql/modules/Person.sql
blob: dbc73b3b70ed9355c64c8da1fe9861dcd5a631a7 (plain)
  1. begin;
  2. CREATE OR REPLACE FUNCTION person__save
  3. (in_entity_id integer, in_salutation_id int,
  4. in_first_name text, in_middle_name text, in_last_name text
  5. )
  6. RETURNS INT AS $$
  7. DECLARE
  8. e_id int;
  9. e entity;
  10. loc location;
  11. l_id int;
  12. p_id int;
  13. BEGIN
  14. select * into e from entity where id = in_entity_id and entity_class = 3;
  15. e_id := in_entity_id;
  16. IF NOT FOUND THEN
  17. INSERT INTO entity (name, entity_class)
  18. values (in_first_name || ' ' || in_last_name, 3);
  19. e_id := currval('entity_id_seq');
  20. END IF;
  21. UPDATE person SET
  22. salutation_id = in_salutation_id,
  23. first_name = in_first_name,
  24. last_name = in_last_name,
  25. middle_name = in_middle_name
  26. WHERE
  27. entity_id = in_entity_id;
  28. IF FOUND THEN
  29. RETURN in_entity_id;
  30. ELSE
  31. -- Do an insert
  32. INSERT INTO person (salutation_id, first_name, last_name, entity_id)
  33. VALUES (in_salutation_id, in_first_name, in_last_name, e_id);
  34. RETURN e_id;
  35. END IF;
  36. END;
  37. $$ language plpgsql;
  38. CREATE OR REPLACE FUNCTION employee__save
  39. (in_entity_id int, in_start_date date, in_end_date date, in_dob date,
  40. in_role text, in_ssn text, in_sales bool, in_manager_id int, in_employee_number text)
  41. RETURNS int AS $$
  42. DECLARE out_id INT;
  43. BEGIN
  44. UPDATE entity_employee
  45. SET startdate = in_start_date,
  46. enddate = in_end_date,
  47. dob = in_dob,
  48. role = in_role,
  49. ssn = in_ssn,
  50. manager_id = in_manager_id,
  51. employeenumber = in_employee_number,
  52. person_id = (select id FROM person
  53. WHERE entity_id = in_entity_id)
  54. WHERE entity_id = in_entity_id;
  55. out_id = in_entity_id;
  56. IF NOT FOUND THEN
  57. INSERT INTO entity_employee
  58. (startdate, enddate, dob, role, ssn, manager_id,
  59. employeenumber, entity_id, person_id)
  60. VALUES
  61. (in_start_date, in_end_date, in_dob, in_role, in_ssn,
  62. in_manager_id, in_employee_number, in_entity_id,
  63. (SELECT id FROM person
  64. WHERE entity_id = in_entity_id));
  65. RETURN in_entity_id;
  66. END IF;
  67. END;
  68. $$ LANGUAGE PLPGSQL;
  69. CREATE OR REPLACE FUNCTION person__list_locations(in_entity_id int)
  70. RETURNS SETOF location_result AS
  71. $$
  72. DECLARE out_row RECORD;
  73. BEGIN
  74. FOR out_row IN
  75. SELECT l.id, l.line_one, l.line_two, l.line_three, l.city,
  76. l.state, l.mail_code, c.name, lc.class
  77. FROM location l
  78. JOIN person_to_location ctl ON (ctl.location_id = l.id)
  79. JOIN person p ON (ctl.person_id = p.id)
  80. JOIN location_class lc ON (ctl.location_class = lc.id)
  81. JOIN country c ON (c.id = l.country_id)
  82. WHERE p.entity_id = in_entity_id
  83. ORDER BY lc.id, l.id, c.name
  84. LOOP
  85. RETURN NEXT out_row;
  86. END LOOP;
  87. END;
  88. $$ LANGUAGE PLPGSQL;
  89. CREATE OR REPLACE FUNCTION person__list_contacts(in_entity_id int)
  90. RETURNS SETOF contact_list AS
  91. $$
  92. DECLARE out_row RECORD;
  93. BEGIN
  94. FOR out_row IN
  95. SELECT cc.class, cc.id, c.contact
  96. FROM person_to_contact c
  97. JOIN contact_class cc ON (c.contact_class_id = cc.id)
  98. JOIN person p ON (c.person_id = p.id)
  99. WHERE p.entity_id = in_entity_id
  100. LOOP
  101. RETURN NEXT out_row;
  102. END LOOP;
  103. END;
  104. $$ LANGUAGE plpgsql;
  105. --
  106. CREATE OR REPLACE FUNCTION person__save_contact
  107. (in_entity_id int, in_contact_class int, in_contact_orig text, in_contact_new TEXT)
  108. RETURNS INT AS
  109. $$
  110. DECLARE
  111. out_id int;
  112. v_orig person_to_contact;
  113. BEGIN
  114. SELECT cc.* into v_orig
  115. FROM person_to_contact cc, person p
  116. WHERE p.entity_id = in_entity_id
  117. and cc.contact_class_id = in_contact_class
  118. AND cc.contact = in_contact_orig
  119. AND cc.person_id = p.id;
  120. IF NOT FOUND THEN
  121. -- create
  122. INSERT INTO person_to_contact(person_id, contact_class_id, contact)
  123. VALUES (
  124. (SELECT id FROM person WHERE entity_id = in_entity_id),
  125. in_contact_class,
  126. in_contact_new
  127. );
  128. return 1;
  129. ELSE
  130. -- edit.
  131. UPDATE person_to_contact
  132. SET contact = in_contact_new
  133. WHERE
  134. contact = in_contact_orig
  135. AND person_id = v_orig.person_id
  136. AND contact_class = in_contact_class;
  137. return 0;
  138. END IF;
  139. END;
  140. $$ LANGUAGE PLPGSQL;
  141. --
  142. create or replace function person__save_location(
  143. in_entity_id int,
  144. in_location_id int,
  145. in_location_class int,
  146. in_line_one text,
  147. in_line_two text,
  148. in_line_three text,
  149. in_city TEXT,
  150. in_state TEXT,
  151. in_mail_code text,
  152. in_country_code int
  153. ) returns int AS $$
  154. DECLARE
  155. l_row location;
  156. l_id INT;
  157. t_person_id int;
  158. BEGIN
  159. SELECT id INTO t_person_id
  160. FROM person WHERE entity_id = in_entity_id;
  161. -- why does it delete?
  162. select * into l_row FROM location
  163. WHERE id = in_location_id;
  164. IF NOT FOUND THEN
  165. -- Create a new one.
  166. l_id := location_save(
  167. in_location_id,
  168. in_line_one,
  169. in_line_two,
  170. in_line_three,
  171. in_city,
  172. in_state,
  173. in_mail_code,
  174. in_country_code);
  175. INSERT INTO person_to_location
  176. (person_id, location_id, location_class)
  177. VALUES (t_person_id, l_id, in_location_class);
  178. ELSE
  179. l_id := location_save(
  180. in_location_id,
  181. in_line_one,
  182. in_line_two,
  183. in_line_three,
  184. in_city,
  185. in_state,
  186. in_mail_code,
  187. in_country_code);
  188. -- Update the old one.
  189. END IF;
  190. return l_id;
  191. END;
  192. $$ language 'plpgsql';
  193. CREATE OR REPLACE FUNCTION person__delete_location (
  194. in_entity_id INT, in_location_id INT
  195. ) returns int AS $$
  196. DECLARE
  197. v_loc location;
  198. BEGIN
  199. select loc.* into v_loc FROM location loc
  200. JOIN person_to_location ptl ON loc.id = ptl.location_id
  201. JOIN person p ON p.id = ptl.person_id
  202. WHERE p.entity_id = in_entity_id
  203. AND loc.id = in_location_id;
  204. IF NOT FOUND THEN
  205. RAISE EXCEPTION 'Cannot find records to delete for entity % and location %', in_entity_id, in_location_id;
  206. ELSE
  207. DELETE FROM people_to_location WHERE location_id = in_location_id;
  208. DELETE FROM location WHERE location_id = in_location_id;
  209. END IF;
  210. END;
  211. $$ language plpgsql;
  212. CREATE OR REPLACE FUNCTION person__all_locations (
  213. in_entity_id int
  214. ) returns setof location AS $$
  215. SELECT l.* FROM location l
  216. JOIN person_to_location ptl ON ptl.location_id = l.id
  217. JOIN person p on ptl.person_id = p.id
  218. WHERE p.id = $1;
  219. $$ language sql;
  220. commit;