diff options
| -rw-r--r-- | sql/modules/README | 3 | ||||
| -rw-r--r-- | sql/modules/hr.sql | 177 | ||||
| -rw-r--r-- | sql/modules/security.sql | 108 |
3 files changed, 288 insertions, 0 deletions
diff --git a/sql/modules/README b/sql/modules/README new file mode 100644 index 00000000..cd89d7b9 --- /dev/null +++ b/sql/modules/README @@ -0,0 +1,3 @@ +This set of files is just for initial testing and proof of concept purposes. +They will be moved out of here into appropriate upgrade scripts as necessary +and are intended only for collaborative development purposes. diff --git a/sql/modules/hr.sql b/sql/modules/hr.sql new file mode 100644 index 00000000..25cefe05 --- /dev/null +++ b/sql/modules/hr.sql @@ -0,0 +1,177 @@ + +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/modules/security.sql b/sql/modules/security.sql new file mode 100644 index 00000000..fe790f22 --- /dev/null +++ b/sql/modules/security.sql @@ -0,0 +1,108 @@ +create table modules ( +id SERIAL PRIMARY KEY, +mod_name TEXT UNIQUE NOT NULL, +comments text default '' +); +comment on table modules is $$name may be used as an alternate key. +Comments should be used to provide the admin of the system with an +understanding of what the module does. Names and comments are also subject to +string freezes since they may be translated by the application.$$; + +-- not adding comments to these because they are translated anyway. +insert into modules (mod_name) values ('AR'); +insert into modules (mod_name) values ('AP'); +insert into modules (mod_name) values ('HR'); +insert into modules (mod_name) values ('Order Entry'); +insert into modules (mod_name) values ('Goods and Services'); +insert into modules (mod_name) values ('Recurring Transactions'); +insert into modules (mod_name) values ('System'); + +create or replace function add_module (text, text) returns int AS $$ +insert into modules (mod_name, comments) values ($1, $2); +select currval(modules_id_seq); +$$ language sql; + +create or replace function get_all_modules () returns setof modules as $$ +select id, mod_name, comments from modules; +$$ language sql; + +create or replace function get_module_by_id (int) returns modules as $$ +select id, modname, comments from modules where id = $1; +$$ language sql; + +create or replace function get_module_by_name (text) returns modules as $$ +select id, modname, comments from modules where mod_name = $1; +$$ language sql; + +create or replace function save_module (int, text, text) returns bool as $$ +update modules set mod_name = $2, comments=$3 where id = $1; +$$ language sql; + +create table mod_relation ( +id serial primary key, +mod_id int not null references modules(id), +rel_name text NOT NULL, +rel_type "char" CHECK IN ('t', 's') +); + +comment on table mod_relation is $$reltype is 't' for tables or views and 's' +for sequences. rel_name is the name of the table.$$; + +create or replace function register_table (text, text) returns int AS $$ +insert into mod_relation (mod_id, relname, reltype) values +((select id from modules where mod_name = $1), $2, 't'); +select 1; +$$ language sql; + +create or replace function register_sequence (text, text) returns int as $$ +insert into module_relation (mod_id, relname, reltype) values +((select id from modules where mod_name = $1), $2, 's'); +select 1; +$$ language sql; + +select register_table('System', 'modules'); +select register_table('System', 'mod_relation'); +select register_sequence('System', 'modules_id_seq'); +select register_sequence('System', 'mod_relation_id_seq'); + +create or replace function change_my_password(text) returns bool as $$ +begin +execute 'alter user ''' || session_user || ''' with encrypted password ''' + || $1 || ''''; +return true; +end; +$$ language plpgsql security definer; + +comment on function change_my_password is $$ This function must be created as a superuser to work!$$; + +create table db_users ( +id serial primary key, +username text unique not null, +active bool default true not null +); + +comment on db_users is $$This is a list of users applicable to this +dataset. Note that the user creation script must connect to the dataset to be +used and add the username to this table. Otherwise the user will not be able +to log in.$$; + +create table preferences +(id integer primary key references db_users(id) deferrable initially deferred, +employee_id integer references employees(id), deferrable initially deferred, + countrycode text, + currency text, + dateformat text, + menuwidth text, + printer text, + signature text, + stylesheet text, + templates text, + timeout numeric, + vclimit numeric + +); + +create or replace function add_user (text) returns bool as $$ +insert into dataset_users (username) values ($1); +select true; +$$ language sql; |