From 3a1283ded34845438a3c11b1fb9543dd7166a98d Mon Sep 17 00:00:00 2001 From: einhverfr Date: Wed, 7 Mar 2007 03:17:13 +0000 Subject: Moving HR bit into upgrade script. git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@857 4979c152-3d1c-0410-bac9-87ea11338e46 --- sql/modules/hr.sql | 177 ----------------------------------------------- sql/upgrade/1.2-1.3.sql | 180 ++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 180 insertions(+), 177 deletions(-) delete mode 100644 sql/modules/hr.sql create mode 100644 sql/upgrade/1.2-1.3.sql diff --git a/sql/modules/hr.sql b/sql/modules/hr.sql deleted file mode 100644 index 25cefe05..00000000 --- a/sql/modules/hr.sql +++ /dev/null @@ -1,177 +0,0 @@ - -CREATE TABLE location ( - id SERIAL PRIMARY KEY, - companyname text, - address1 text, - address2 text, - city text, - state text, - country text, - zipcode text -); - -CREATE SEQUENCE employee_id_seq; -SELECT setval('employee_id_seq', (select max(id) + 1 FROM employee)); - -ALTER TABLE employee ADD COLUMN locations_id integer; -ALTER TABLE employee ADD FOREIGN KEY (locations_id) REFERENCES location(id); -ALTER TABLE employee ALTER COLUMN id DROP DEFAULT; -ALTER TABLE employee ALTER COLUMN id SET DEFAULT nextval('employee_id_seq'); - -CREATE OR REPLACE FUNCTION location_save -(in_id int, in_companyname text, in_address1 text, in_address2 text, - in_city text, in_state text, in_zipcode text, in_country text) -returns integer AS -$$ -DECLARE - location_id integer; -BEGIN - UPDATE location - SET companyname = in_companyname, - address1 = in_address1, - address2 = in_address2, - city = in_city, - state = in_state, - zipcode = in_zipcode, - country = in_country - WHERE id = in_id; - IF FOUND THEN - return in_id; - END IF; - INSERT INTO location - (companyname, address1, address2, city, state, zipcode, country) - VALUES - (in_companyname, in_address1, in_address2, in_city, in_state, - in_zipcode, in_country); - SELECT lastval('location_id_seq') INTO location_id; - return location_id; -END; -$$ LANGUAGE PLPGSQL; - -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_city varchar(32), in_state varchar(32), in_zipcode varchar(10), - in_country varchar(32), 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 -$$ -BEGIN - UPDATE employee - 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; - END IF; - INSERT INTO employee - (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; - -CREATE OR REPLACE FUNCTION employee_get -(in_id integer) -returns employee as -$$ -DECLARE - emp employee%ROWTYPE; -BEGIN - SELECT * INTO emp FROM employee WHERE id = in_id; - RETURN emp; -END; -$$ language plpgsql; - -CREATE OR REPLACE FUNCTION employee_list_managers -(in_id integer) -RETURNS SETOF employee as -$$ -DECLARE - emp employee%ROWTYPE; -BEGIN - FOR emp IN - SELECT * FROM employee - WHERE sales = '1' AND role='manager' - AND id <> coalesce(in_id, -1) - ORDER BY name - LOOP - RETURN NEXT emp; - END LOOP; -END; -$$ language plpgsql; - -CREATE OR REPLACE FUNCTION employee_delete -(in_id integer) returns void as -$$ -BEGIN - DELETE FROM employee WHERE id = in_id; - RETURN; -END; -$$ language plpgsql; - --- as long as we need the datatype, might as well get some other use out of it! -CREATE OR REPLACE VIEW employee_search AS -SELECT e.*, m.name AS manager -FROM employee e JOIN employee m ON (e.managerid = m.id); - -CREATE OR REPLACE FUNCTION employee_search -(in_startdatefrom date, in_startdateto date, in_name varchar, in_notes text, - in_enddateto date, in_enddatefrom date) -RETURNS SETOF employee_search AS -$$ -DECLARE - emp employee_search%ROWTYPE; -BEGIN - FOR emp IN - SELECT * FROM employee_search - WHERE coalesce(startdate, 'infinity'::timestamp) - >= coalesce(in_startdateto, '-infinity'::timestamp) - AND coalesce(startdate, '-infinity'::timestamp) <= - coalesce(in_startdatefrom, - 'infinity'::timestamp) - AND coalesce(enddate, '-infinity'::timestamp) <= - 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 (sales = 't' OR coalesce(in_sales, 'f') = 'f') - LOOP - RETURN NEXT emp; - END LOOP; -END; -$$ language plpgsql; diff --git a/sql/upgrade/1.2-1.3.sql b/sql/upgrade/1.2-1.3.sql new file mode 100644 index 00000000..38d5b6ba --- /dev/null +++ b/sql/upgrade/1.2-1.3.sql @@ -0,0 +1,180 @@ +BEGIN; + +CREATE TABLE location ( + id SERIAL PRIMARY KEY, + companyname text, + address1 text, + address2 text, + city text, + state text, + country text, + zipcode text +); + +CREATE SEQUENCE employee_id_seq; +SELECT setval('employee_id_seq', (select max(id) + 1 FROM employee)); + +ALTER TABLE employee ADD COLUMN locations_id integer; +ALTER TABLE employee ADD FOREIGN KEY (locations_id) REFERENCES location(id); +ALTER TABLE employee ALTER COLUMN id DROP DEFAULT; +ALTER TABLE employee ALTER COLUMN id SET DEFAULT nextval('employee_id_seq'); + +CREATE OR REPLACE FUNCTION location_save +(in_id int, in_companyname text, in_address1 text, in_address2 text, + in_city text, in_state text, in_zipcode text, in_country text) +returns integer AS +$$ +DECLARE + location_id integer; +BEGIN + UPDATE location + SET companyname = in_companyname, + address1 = in_address1, + address2 = in_address2, + city = in_city, + state = in_state, + zipcode = in_zipcode, + country = in_country + WHERE id = in_id; + IF FOUND THEN + return in_id; + END IF; + INSERT INTO location + (companyname, address1, address2, city, state, zipcode, country) + VALUES + (in_companyname, in_address1, in_address2, in_city, in_state, + in_zipcode, in_country); + SELECT lastval('location_id_seq') INTO location_id; + return location_id; +END; +$$ LANGUAGE PLPGSQL; + +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_city varchar(32), in_state varchar(32), in_zipcode varchar(10), + in_country varchar(32), 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 +$$ +BEGIN + UPDATE employee + 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; + END IF; + INSERT INTO employee + (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; + +CREATE OR REPLACE FUNCTION employee_get +(in_id integer) +returns employee as +$$ +DECLARE + emp employee%ROWTYPE; +BEGIN + SELECT * INTO emp FROM employee WHERE id = in_id; + RETURN emp; +END; +$$ language plpgsql; + +CREATE OR REPLACE FUNCTION employee_list_managers +(in_id integer) +RETURNS SETOF employee as +$$ +DECLARE + emp employee%ROWTYPE; +BEGIN + FOR emp IN + SELECT * FROM employee + WHERE sales = '1' AND role='manager' + AND id <> coalesce(in_id, -1) + ORDER BY name + LOOP + RETURN NEXT emp; + END LOOP; +END; +$$ language plpgsql; + +CREATE OR REPLACE FUNCTION employee_delete +(in_id integer) returns void as +$$ +BEGIN + DELETE FROM employee WHERE id = in_id; + RETURN; +END; +$$ language plpgsql; + +-- as long as we need the datatype, might as well get some other use out of it! +CREATE OR REPLACE VIEW employee_search AS +SELECT e.*, m.name AS manager +FROM employee e JOIN employee m ON (e.managerid = m.id); + +CREATE OR REPLACE FUNCTION employee_search +(in_startdatefrom date, in_startdateto date, in_name varchar, in_notes text, + in_enddateto date, in_enddatefrom date) +RETURNS SETOF employee_search AS +$$ +DECLARE + emp employee_search%ROWTYPE; +BEGIN + FOR emp IN + SELECT * FROM employee_search + WHERE coalesce(startdate, 'infinity'::timestamp) + >= coalesce(in_startdateto, '-infinity'::timestamp) + AND coalesce(startdate, '-infinity'::timestamp) <= + coalesce(in_startdatefrom, + 'infinity'::timestamp) + AND coalesce(enddate, '-infinity'::timestamp) <= + 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 (sales = 't' OR coalesce(in_sales, 'f') = 'f') + LOOP + RETURN NEXT emp; + END LOOP; +END; +$$ language plpgsql; + +COMMIT; -- cgit v1.2.3