diff options
Diffstat (limited to 'sql/modules/Employee.sql')
| -rw-r--r-- | sql/modules/Employee.sql | 218 |
1 files changed, 160 insertions, 58 deletions
diff --git a/sql/modules/Employee.sql b/sql/modules/Employee.sql index c407e244..a934a3e5 100644 --- a/sql/modules/Employee.sql +++ b/sql/modules/Employee.sql @@ -1,66 +1,157 @@ -- VERSION 1.3.0 +BEGIN; CREATE OR REPLACE FUNCTION employee_save -(in_id integer, in_location_id integer, in_employeenumber varchar(32), - in_name varchar(64), in_address1 varchar(32), in_address2 varchar(32), +(in_id integer, in_employeenumber varchar(32), + in_salutation int, in_first_name varchar(64), in_last_name varchar(64), + in_address1 varchar(32), in_address2 varchar(32), in_city varchar(32), in_state varchar(32), in_zipcode varchar(10), - in_country varchar(32), in_workphone varchar(20), + in_country int, in_workphone varchar(20), in_homephone varchar(20), in_startdate date, in_enddate date, in_notes text, in_role varchar(20), in_sales boolean, in_email text, in_ssn varchar(20), in_dob date, in_iban varchar(34), - in_bic varchar(11), in_managerid integer) returns int -AS -$$ + in_bic varchar(11), in_managerid integer) +returns int AS $$ +DECLARE + e_id int; + e entity; + loc location; + l_id int; + per person; + p_id int; BEGIN - UPDATE employees - SET location_id = in_location_id, - employeenumber = in_employeenumber, - name = in_name, - address1 = in_address1, - address2 = in_address2, - city = in_city, - state = in_state, - zipcode = in_zipcode, - country = in_country, - workphone = in_workphone, - homephone = in_homephone, - startdate = in_startdate, - enddate = in_enddate, - notes = in_notes, - role = in_role, - sales = in_sales, - email = in_email, - ssn = in_ssn, - dob=in_dob, - iban = in_iban, - bic = in_bic, - manager_id = in_managerid - WHERE id = in_id; - IF FOUND THEN - return in_id; + select * into e from entity where id = in_id and entity_class = 3; + + if found then + + select l.* into loc from location l + left join person_to_location ptl on ptl.location_id = l.id + left join person p on p.id = ptl.person_id + where p.entity_id = in_id; + + select * into per from person p where p.entity_id = in_id; + + update location + set + line_one = in_address1, + line_two = in_address2, + city_province = in_city, + mail_code = in_zipcode, + country_id = in_country + where id = loc.id; + + UPDATE employee + SET + employeenumber = in_employeenumber, + startdate = in_startdate, + enddate = in_enddate, + role = in_role, + sales = in_sales, + ssn = in_ssn, + dob = in_dob, + managerid = in_managerid + WHERE entity_id = in_id; + + update entity_note + set + note = in_note + where entity_id = in_id; + + UPDATE entity_bank_account + SET + bic = in_bic, + iban = in_iban + WHERE entity_id = in_id; + + UPDATE person + SET + salutation_id = in_salutation, + first_name = in_first_name, + last_name = in_last_name + WHERE entity_id = in_id; + + UPDATE person_to_contact + set + contact = in_homephone + WHERE person_id = per.id + AND contact_class_id = 11; + + UPDATE person_to_contact + set + contact = in_workphone + WHERE person_id = per.id + AND contact_class_id = 1; + + UPDATE person_to_contact + set + contact = in_email + WHERE person_id = per.id + AND contact_class_id = 12; + + return in_id; + + ELSIF NOT FOUND THEN + -- first, create a new entity + -- Then, create an employee. + + e_id := in_id; -- expect nextval entity_id to have been called. + INSERT INTO entity (id, entity_class, name) VALUES (e_id, 3, in_first_name||' '||in_last_name); + + INSERT INTO entity_bank_account (entity_id, iban, bic) + VALUES (e_id, in_iban, in_bic); + + p_id := nextval('person_id_seq'); + insert into person (id, salutation_id, first_name, last_name, entity_id) + VALUES + (p_id, in_salutation, in_first_name, in_last_name, e_id); + + if in_notes is not null then + insert into entity_note (note_class, note, ref_key, vector) + values (1, in_notes, e_id, ''); + END IF; + + insert into person_to_contact (person_id, contact_class_id, contact) + VALUES (p_id, 1, in_workphone); -- work phone # + insert into person_to_contact (person_id, contact_class_id, contact) + VALUES (p_id, 11, in_homephone); -- Home phone # + insert into person_to_contact (person_id, contact_class_id, contact) + VALUES (p_id, 12, in_email); -- email address. + + INSERT INTO employee + (employeenumber, startdate, enddate, + role, sales, ssn, + dob, managerid, entity_id, entity_class_id) + VALUES + (in_employeenumber, in_startdate, in_enddate, + in_role, in_sales, in_ssn, + in_dob, in_managerid, e_id, 3); + + l_id := nextval('location_id_seq'); + insert into location (id, location_class, line_one, line_two, city_province, country_id, mail_code) + VALUES ( + l_id, + 1, + in_address1, + in_address2, + in_city, + in_country, + in_zipcode + ); + insert into person_to_location (person_id, location_id) + VALUES (p_id, l_id); + + return e_id; END IF; - INSERT INTO employees - (location_id, employeenumber, name, address1, address2, - city, state, zipcode, country, workphone, homephone, - startdate, enddate, notes, role, sales, email, ssn, - dob, iban, bic, managerid) - VALUES - (in_location_id, in_employeenumber, in_name, in_address1, - in_address2, in_city, in_state, in_zipcode, in_country, - in_workphone, in_homephone, in_startdate, in_enddate, - in_notes, in_role, in_sales, in_email, in_ssn, in_dob, - in_iban, in_bic, in_managerid); - SELECT currval('employee_id_seq') INTO employee_id; - return employee_id; END; -$$ LANGUAGE PLPGSQL; +$$ LANGUAGE 'plpgsql'; +-- why is this like this? CREATE OR REPLACE FUNCTION employee_get (in_id integer) -returns employees as +returns employee as $$ DECLARE - emp employees%ROWTYPE; + emp employee%ROWTYPE; BEGIN SELECT * INTO emp FROM employees WHERE id = in_id; RETURN emp; @@ -69,15 +160,15 @@ $$ language plpgsql; CREATE OR REPLACE FUNCTION employee_list_managers (in_id integer) -RETURNS SETOF employees as +RETURNS SETOF employee as $$ DECLARE - emp employees%ROWTYPE; + emp employee%ROWTYPE; BEGIN FOR emp IN - SELECT * FROM employees + SELECT * FROM employee WHERE sales = '1' AND role='manager' - AND id <> coalesce(in_id, -1) + AND entity_id <> coalesce(in_id, -1) ORDER BY name LOOP RETURN NEXT emp; @@ -89,15 +180,25 @@ CREATE OR REPLACE FUNCTION employee_delete (in_id integer) returns void as $$ BEGIN - DELETE FROM employees WHERE id = in_id; + DELETE FROM employee WHERE entity_id = in_id; RETURN; END; $$ language plpgsql; -- as long as we need the datatype, might as well get some other use out of it! +-- +-- % type is pg_trgm comparison. + +CREATE INDEX name_idx ON employee USING gist(name gist_trgm_ops); +CREATE INDEX notes_idx ON entity_note USING gist(note gist_trgm_ops); + CREATE OR REPLACE VIEW employee_search AS -SELECT e.*, m.name AS manager -FROM employees e LEFT JOIN employees m ON (e.managerid = m.id); +SELECT e.*, em.name AS manager, emn.note, en.name as name +FROM employee e +LEFT JOIN entity en on (e.entity_id = en.id) +LEFT JOIN employee m ON (e.managerid = m.entity_id) +LEFT JOIN entity em on (em.id = m.entity_id) +LEFT JOIN entity_note emn on (emn.ref_key = em.id); CREATE OR REPLACE FUNCTION employee_search (in_startdatefrom date, in_startdateto date, in_name varchar, in_notes text, @@ -118,12 +219,13 @@ BEGIN coalesce(in_enddateto, 'infinity'::timestamp) AND coalesce(enddate, 'infinity'::timestamp) >= coalesce(in_enddatefrom, '-infinity'::timestamp) - AND lower(name) LIKE '%' || lower(in_name) || '%' - AND lower(notes) LIKE '%' || lower(in_notes) || '%' + AND (name % in_name + OR note % in_notes) AND (sales = 't' OR coalesce(in_sales, 'f') = 'f') LOOP RETURN NEXT emp; END LOOP; + return; END; $$ language plpgsql; - +COMMIT;
\ No newline at end of file |