summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
authoreinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2007-11-28 05:33:16 +0000
committereinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2007-11-28 05:33:16 +0000
commit13312601ce94bcc19d3f6af0627cb373492c0367 (patch)
tree0e71d14842279a7cda2f3fb7f56458d5b0fec9bb /sql
parent3560cf58d9214c8d80e4fb95ecb42024607d868f (diff)
More Employee fixes
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1912 4979c152-3d1c-0410-bac9-87ea11338e46
Diffstat (limited to 'sql')
-rw-r--r--sql/Pg-database.sql4
-rw-r--r--sql/modules/Company.sql17
-rw-r--r--sql/modules/Person.sql84
3 files changed, 86 insertions, 19 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index 48275a5e..ab575a0c 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -434,7 +434,7 @@ CREATE VIEW employee AS
SELECT s.salutation, p.first_name, p.last_name, ee.person_id, ee.entity_id, ee.startdate, ee.enddate, ee."role", ee.ssn, ee.sales, ee.manager_id, ee.employeenumber, ee.dob
FROM person p
JOIN entity_employee ee USING (entity_id)
- JOIN salutation s ON p.salutation_id = s.id;
+ LEFT JOIN salutation s ON p.salutation_id = s.id;
/*
create view employee as
@@ -521,7 +521,7 @@ CREATE VIEW vendor AS
ein.note as
invoice_notes
FROM entity_credit_account emd
- join entity_bank_account eba on emd.entity_id = eba.entity_id
+ LEFT join entity_bank_account eba on emd.entity_id = eba.entity_id
left join entity_note ein on ein.ref_key = emd.entity_id
join company c on c.entity_id = emd.entity_id
where emd.entity_class = 1;
diff --git a/sql/modules/Company.sql b/sql/modules/Company.sql
index 6b48ddf7..231462a9 100644
--- a/sql/modules/Company.sql
+++ b/sql/modules/Company.sql
@@ -72,7 +72,6 @@ BEGIN
END;
$$ LANGUAGE PLPGSQL;
-CREATE company
CREATE OR REPLACE FUNCTION entity_credit_save (
in_id int, in_entity_class int,
@@ -208,22 +207,6 @@ CREATE TYPE contact_list AS (
contact text
);
-CREATE OR REPLACE FUNCTION company__list_contacts(in_entity_id int)
-RETURNS SETOF contact_list AS
-$$
-DECLARE out_row RECORD;
-BEGIN
- FOR out_row IN
- SELECT cc.class, c.contact
- FROM company_to_contact c
- JOIN contact_class cc ON (c.contact_class_id = cc.id)
- JOIN company cp ON (c.company_id = cp.id)
- WHERE cp.entity_id = in_entity_id
- LOOP
- RETURN NEXT out_row;
- END LOOP;
-END;
-$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION company__list_bank_account(in_entity_id int)
RETURNS SETOF entity_bank_account AS
diff --git a/sql/modules/Person.sql b/sql/modules/Person.sql
index 28e3d4e4..160eeb2e 100644
--- a/sql/modules/Person.sql
+++ b/sql/modules/Person.sql
@@ -79,4 +79,88 @@ BEGIN
END;
$$ LANGUAGE PLPGSQL;
+CREATE OR REPLACE FUNCTION person__list_locations(in_entity_id int)
+RETURNS SETOF location_result AS
+$$
+DECLARE out_row RECORD;
+BEGIN
+ FOR out_row IN
+ SELECT l.id, l.line_one, l.line_two, l.line_three, l.city,
+ l.state, c.name, lc.class
+ FROM location l
+ JOIN person_to_location ctl ON (ctl.location_id = l.id)
+ JOIN person p ON (ctl.person_id = p.id)
+ JOIN location_class lc ON (ctl.location_class = lc.id)
+ JOIN country c ON (c.id = l.country_id)
+ WHERE p.entity_id = in_entity_id
+ ORDER BY lc.id, l.id, c.name
+ LOOP
+ RETURN NEXT out_row;
+ END LOOP;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION person__list_contacts(in_entity_id int)
+RETURNS SETOF contact_list AS
+$$
+DECLARE out_row RECORD;
+BEGIN
+ FOR out_row IN
+ SELECT cc.class, c.contact
+ FROM person_to_contact c
+ JOIN contact_class cc ON (c.contact_class_id = cc.id)
+ JOIN person p ON (c.person_id = p.id)
+ WHERE p.entity_id = in_entity_id
+ LOOP
+ RETURN NEXT out_row;
+ END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION person__save_contact
+(in_entity_id int, in_contact_class int, in_contact text)
+RETURNS INT AS
+$$
+DECLARE out_id int;
+BEGIN
+ INSERT INTO person_to_contact(person_id, contact_class_id, contact)
+ SELECT id, in_contact_class, in_contact FROM person
+ WHERE entity_id = in_entity_id;
+
+ RETURN 1;
+END;
+$$ LANGUAGE PLPGSQL;
+
+create or replace function person_location_save(
+ in_entity_id int, in_location_id int,
+ in_line_one text, in_line_two text,
+ in_line_three text, in_city TEXT, in_state TEXT, in_mail_code text,
+ in_country_code int
+) returns int AS $$
+
+ DECLARE
+ l_row location;
+ l_id INT;
+ t_person_id int;
+ BEGIN
+ SELECT id INTO t_person_id
+ FROM person WHERE entity_id = in_entity_id;
+
+ DELETE FROM person_to_location
+ WHERE person_id = t_person_id
+ AND location_id = in_location_id;
+
+ SELECT location_save(in_line_one, in_line_two, in_line_three, in_city,
+ in_state, in_mail_code, in_country_code)
+ INTO l_id;
+
+ INSERT INTO person_to_location
+ (person_id, location_id)
+ VALUES (t_person_id, l_id);
+
+ RETURN l_id;
+ END;
+
+$$ language 'plpgsql';
+
commit;