Major changes to the Admin interface for Edit User. Minor tweaks to Person, Location and Employee.sql as needed.

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2331 4979c152-3d1c-0410-bac9-87ea11338e46

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;