summaryrefslogtreecommitdiff
path: root/sql/modules
diff options
context:
space:
mode:
Diffstat (limited to 'sql/modules')
-rw-r--r--sql/modules/Employee.sql2
-rw-r--r--sql/modules/Location.sql65
-rw-r--r--sql/modules/Person.sql106
-rw-r--r--sql/modules/admin.sql1
4 files changed, 136 insertions, 38 deletions
diff --git a/sql/modules/Employee.sql b/sql/modules/Employee.sql
index ae2efc86..0048b873 100644
--- a/sql/modules/Employee.sql
+++ b/sql/modules/Employee.sql
@@ -23,7 +23,7 @@ returns int AS $$
select * into p from person where id = in_person;
IF NOT FOUND THEN
- RAISE EXCEPTION 'No person found for ID %', in_person;
+ RAISE EXCEPTION 'No person found for ID %', in_perso;
END IF;
-- Okay, we're good. Check to see if we update or insert.
diff --git a/sql/modules/Location.sql b/sql/modules/Location.sql
index 4e0cb6b1..e266f277 100644
--- a/sql/modules/Location.sql
+++ b/sql/modules/Location.sql
@@ -27,7 +27,7 @@ END;
$$ language plpgsql;
CREATE OR REPLACE FUNCTION location_save
-(in_address1 text, in_address2 text, in_address3 text,
+(in_location_id int, in_address1 text, in_address2 text, in_address3 text,
in_city text, in_state text, in_zipcode text, in_country int)
returns integer AS
$$
@@ -36,29 +36,54 @@ DECLARE
location_row RECORD;
BEGIN
- SELECT * INTO location_row FROM location
- WHERE line_one = in_address1 AND
- coalesce(line_two, '') = coalesce(in_address2, '') AND
- coalesce(line_three, '') = coalesce(in_address3, '') AND
- city = in_city AND
- coalesce(state, '') = coalesce(in_state, '') AND
- coalesce(mail_code, '') = coalesce(in_zipcode, '') AND
- country_id = in_country
- LIMIT 1;
- IF FOUND THEN
- return location_row.id;
+ IF in_location_id IS NULL THEN
+ -- Straight insert.
+ location_id = nextval('location_id_seq');
+ INSERT INTO location (
+ id,
+ line_one,
+ line_two,
+ line_three,
+ city,
+ state,
+ zipcode,
+ country)
+ VALUES (
+ location_id,
+ in_address1,
+ in_address2,
+ in_address3,
+ in_city,
+ in_state,
+ in_zipcode,
+ in_country
+ );
+ return location_id;
+ ELSE
+ -- Test it.
+ SELECT * INTO location_row WHERE id = in_location_id;
+ IF NOT FOUND THEN
+ -- Tricky users are lying to us.
+ RAISE EXCEPTION "location_save called with nonexistant location ID %", in_location_id;
+ ELSE
+ -- Okay, we're good.
+
+ UPDATE location SET
+ line_one = in_address1,
+ line_two = in_address2,
+ line_three = in_address3,
+ city = in_city,
+ state = in_state,
+ zipcode = in_zipcode,
+ country = in_country
+ WHERE id = in_location_id;
+ return in_location_id;
+ END IF;
END IF;
- INSERT INTO location
- (line_one, line_two, line_three, city, state, mail_code, country_id,
- created)
- VALUES
- (in_address1, in_address2, in_address3, in_city, in_state,
- in_zipcode, in_country, now());
- SELECT currval('location_id_seq') INTO location_id;
- return location_id;
END;
$$ LANGUAGE PLPGSQL;
+
COMMENT ON function location_save
(in_companyname text, in_address1 text, in_address2 text,
in_city text, in_state text, in_zipcode text, in_country int) IS
diff --git a/sql/modules/Person.sql b/sql/modules/Person.sql
index 9eda8335..f942b137 100644
--- a/sql/modules/Person.sql
+++ b/sql/modules/Person.sql
@@ -131,36 +131,108 @@ BEGIN
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,
+
+/*(
+ unknown,
+ unknown,
+ unknown,
+ unknown,
+ unknown,
+ unknown,
+ unknown,
+ unknown)
+
+*/
+create or replace function person__save_location(
+ 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;
+ t_person_id int;
BEGIN
SELECT id INTO t_person_id
FROM person WHERE entity_id = in_entity_id;
+ -- why does it delete?
+
+ select * into l_row FROM location
+ WHERE id = in_location_id;
+
+ IF NOT FOUND THEN
+ -- Create a new one.
+ l_id := location_save(
+ in_location_id,
+ in_line_one,
+ in_line_two,
+ in_line_three,
+ in_city,
+ in_state,
+ in_mail_code,
+ in_country_code);
+
+ INSERT INTO person_to_location
+ (person_id, location_id)
+ VALUES (t_person_id, l_id);
+ ELSE
+ l_id := location_save(
+ in_location_id,
+ in_line_one,
+ in_line_two,
+ in_line_three,
+ in_city,
+ in_state,
+ in_mail_code,
+ in_country_code);
+ -- Update the old one.
+ END IF;
+ return l_id;
+ END;
+$$ language 'plpgsql';
- DELETE FROM person_to_location
- WHERE person_id = t_person_id
- AND location_id = in_location_id;
+CREATE OR REPLACE FUNCTION person__delete_location (
+ in_entity_id INT, in_location_id INT
+) returns int AS $$
- 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;
+DECLARE
+ v_loc location;
+
+BEGIN
+
+ select loc.* into v_loc FROM location loc
+ JOIN person_to_location ptl ON loc.id = ptl.location_id
+ JOIN person p ON p.id = ptl.person_id
+ WHERE p.entity_id = in_entity_id
+ AND loc.id = in_location_id;
+
+ IF NOT FOUND THEN
+ RAISE EXCEPTION "Cannot find records to delete for entity % and location %", in_entity_id, in_location_id;
+ ELSE
+ DELETE FROM people_to_location WHERE location_id = in_location_id;
+ DELETE FROM location WHERE location_id = in_location_id;
+ END IF;
+
+END;
- INSERT INTO person_to_location
- (person_id, location_id)
- VALUES (t_person_id, l_id);
+$$ language plpgsql;
- RETURN l_id;
- END;
+CREATE OR REPLACE FUNCTION person__all_locations (
+ in_entity_id int
+) returns setof location AS $$
-$$ language 'plpgsql';
+ SELECT l.* FROM location l
+ JOIN person_to_location ptl ON ptl.location_id = l.id
+ JOIN person p on ptl.person_id = p.id
+ WHERE p.id = $1;
+
+$$ language sql;
commit;
diff --git a/sql/modules/admin.sql b/sql/modules/admin.sql
index f590aa94..1055ff29 100644
--- a/sql/modules/admin.sql
+++ b/sql/modules/admin.sql
@@ -472,6 +472,7 @@ BEGIN
pg_roles
where
rolname ~ ('^lsmb_' || in_database)
+ order by rolname ASC
LOOP
RETURN NEXT v_rol;
END LOOP;