summaryrefslogtreecommitdiff
path: root/sql/modules/Employee.sql
blob: 04f33e64836a4aab6c9f1a2c38699d7e43833a4b (plain)
  1. -- VERSION 1.3.0
  2. BEGIN;
  3. CREATE OR REPLACE FUNCTION employee_save(
  4. in_person int, in_entity int, in_startdate date, in_enddate date,
  5. in_role text, in_sales boolean, in_dob date,
  6. in_managerid integer, in_employeenumber text
  7. )
  8. returns int AS $$
  9. DECLARE
  10. e_ent entity_employee;
  11. e entity;
  12. p person;
  13. BEGIN
  14. select * into e from entity where id = in_entity and entity_class = 3;
  15. IF NOT FOUND THEN
  16. RAISE EXCEPTION 'No entity found for ID %', in_id;
  17. END IF;
  18. select * into p from person where id = in_person;
  19. IF NOT FOUND THEN
  20. RAISE EXCEPTION 'No person found for ID %', in_id;
  21. END IF;
  22. -- Okay, we're good. Check to see if we update or insert.
  23. select * into e_ent from entity_employee where person_id = in_person
  24. and entity_id = in_entity;
  25. IF NOT FOUND THEN
  26. -- insert.
  27. INSERT INTO entity_employee (person_id, entity_id, startdate,
  28. enddate, role, sales, manager_id, employeenumber, dob)
  29. VALUES (in_person, in_entity, in_startdate, in_enddate, in_role,
  30. in_sales, in_managerid, in_employeenumber, in_dob);
  31. return in_entity;
  32. ELSE
  33. -- update
  34. UPDATE entity_employee
  35. SET
  36. startdate = in_startdate,
  37. enddate = in_enddate,
  38. role = in_role,
  39. sales = in_sales,
  40. manager_id = in_managerid
  41. employeenumber = in_employeenumber,
  42. dob = in_dob
  43. WHERE
  44. entity_id = in_entity
  45. AND
  46. person_id = in_person;
  47. return in_entity;
  48. END IF;
  49. END;
  50. $$ language 'plpgsql';
  51. create view employees as
  52. select
  53. e.salutation,
  54. e.first_name,
  55. e.last_name,
  56. ee.*
  57. FROM entity e
  58. JOIN entity_employees ee on e.id = ee.entity_id
  59. where e.entity_class = 3;
  60. -- why is this like this?
  61. CREATE OR REPLACE FUNCTION employee_get
  62. (in_id integer)
  63. returns employees as
  64. $$
  65. DECLARE
  66. emp employees%ROWTYPE;
  67. BEGIN
  68. SELECT
  69. e.salutation,
  70. e.first_name,
  71. e.last_name,
  72. ee.*
  73. INTO emp
  74. FROM employees ee
  75. join entity e on ee.entity_id = e.id
  76. WHERE ee.entity_id = in_id;
  77. RETURN emp;
  78. END;
  79. $$ language plpgsql;
  80. CREATE OR REPLACE FUNCTION employee_list_managers
  81. (in_id integer)
  82. RETURNS SETOF employees as
  83. $$
  84. DECLARE
  85. emp employees%ROWTYPE;
  86. BEGIN
  87. FOR emp IN
  88. SELECT
  89. e.salutation,
  90. e.first_name,
  91. e.last_name,
  92. ee.*
  93. FROM entity_employee ee
  94. JOIN entity e on e.id = ee.entity_id
  95. WHERE ee.sales = 't'::bool AND ee.role='manager'
  96. AND ee.entity_id <> coalesce(in_id, -1)
  97. ORDER BY name
  98. LOOP
  99. RETURN NEXT emp;
  100. END LOOP;
  101. END;
  102. $$ language plpgsql;
  103. CREATE OR REPLACE FUNCTION employee_delete
  104. (in_id integer) returns void as
  105. $$
  106. BEGIN
  107. DELETE FROM employee WHERE entity_id = in_id;
  108. RETURN;
  109. END;
  110. $$ language plpgsql;
  111. -- as long as we need the datatype, might as well get some other use out of it!
  112. --
  113. -- % type is pg_trgm comparison.
  114. CREATE INDEX name_idx ON employee USING gist(name gist_trgm_ops);
  115. CREATE INDEX notes_idx ON entity_note USING gist(note gist_trgm_ops);
  116. CREATE OR REPLACE VIEW employee_search AS
  117. SELECT e.*, em.name AS manager, emn.note, en.name as name
  118. FROM employee e
  119. LEFT JOIN entity en on (e.entity_id = en.id)
  120. LEFT JOIN entity_employee m ON (e.managerid = m.entity_id)
  121. LEFT JOIN entity em on (em.id = m.entity_id)
  122. LEFT JOIN entity_note emn on (emn.ref_key = em.id);
  123. CREATE OR REPLACE FUNCTION employee_search
  124. (in_startdatefrom date, in_startdateto date, in_name varchar, in_notes text,
  125. in_enddateto date, in_enddatefrom date, in_sales boolean)
  126. RETURNS SETOF employee_search AS
  127. $$
  128. DECLARE
  129. emp employee_search%ROWTYPE;
  130. BEGIN
  131. FOR emp IN
  132. SELECT * FROM employee_search
  133. WHERE coalesce(startdate, 'infinity'::timestamp)
  134. >= coalesce(in_startdateto, '-infinity'::timestamp)
  135. AND coalesce(startdate, '-infinity'::timestamp) <=
  136. coalesce(in_startdatefrom,
  137. 'infinity'::timestamp)
  138. AND coalesce(enddate, '-infinity'::timestamp) <=
  139. coalesce(in_enddateto, 'infinity'::timestamp)
  140. AND coalesce(enddate, 'infinity'::timestamp) >=
  141. coalesce(in_enddatefrom, '-infinity'::timestamp)
  142. AND (name % in_name
  143. OR note % in_notes)
  144. AND (sales = 't' OR coalesce(in_sales, 'f') = 'f')
  145. LOOP
  146. RETURN NEXT emp;
  147. END LOOP;
  148. return;
  149. END;
  150. $$ language plpgsql;
  151. create or replace function employee_set_location
  152. (in_employee int, in_location int)
  153. returns void as $$
  154. INSERT INTO person_to_location (person_id,location_id)
  155. VALUES (in_employee, in_location);
  156. SELECT NULL;
  157. $$ language 'sql';
  158. COMMIT;