summaryrefslogtreecommitdiff
path: root/sql/modules/Person.sql
blob: 160eeb2e78028678e631d8dc6557b4d1ded9b17e (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, 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, 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. CREATE OR REPLACE FUNCTION person__save_contact
  106. (in_entity_id int, in_contact_class int, in_contact text)
  107. RETURNS INT AS
  108. $$
  109. DECLARE out_id int;
  110. BEGIN
  111. INSERT INTO person_to_contact(person_id, contact_class_id, contact)
  112. SELECT id, in_contact_class, in_contact FROM person
  113. WHERE entity_id = in_entity_id;
  114. RETURN 1;
  115. END;
  116. $$ LANGUAGE PLPGSQL;
  117. create or replace function person_location_save(
  118. in_entity_id int, in_location_id int,
  119. in_line_one text, in_line_two text,
  120. in_line_three text, in_city TEXT, in_state TEXT, in_mail_code text,
  121. in_country_code int
  122. ) returns int AS $$
  123. DECLARE
  124. l_row location;
  125. l_id INT;
  126. t_person_id int;
  127. BEGIN
  128. SELECT id INTO t_person_id
  129. FROM person WHERE entity_id = in_entity_id;
  130. DELETE FROM person_to_location
  131. WHERE person_id = t_person_id
  132. AND location_id = in_location_id;
  133. SELECT location_save(in_line_one, in_line_two, in_line_three, in_city,
  134. in_state, in_mail_code, in_country_code)
  135. INTO l_id;
  136. INSERT INTO person_to_location
  137. (person_id, location_id)
  138. VALUES (t_person_id, l_id);
  139. RETURN l_id;
  140. END;
  141. $$ language 'plpgsql';
  142. commit;