From a316a97097bf8bcf9a4f809faf30dd756e60563d Mon Sep 17 00:00:00 2001
From: aurynn_cmd <aurynn_cmd@4979c152-3d1c-0410-bac9-87ea11338e46>
Date: Thu, 25 Sep 2008 23:08:34 +0000
Subject: Saving locations and contacts to a user now works as expected.

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2342 4979c152-3d1c-0410-bac9-87ea11338e46
---
 sql/Pg-database.sql      |  2 +-
 sql/modules/Location.sql | 38 +++++++++++++++++++++++++++++++-------
 sql/modules/Person.sql   | 12 +++++++-----
 3 files changed, 39 insertions(+), 13 deletions(-)

(limited to 'sql')

diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index e81ee254..3373b01b 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -156,7 +156,7 @@ CREATE TABLE location (
   state text check(state ~ '[[:alnum:]_]'),
   country_id integer not null REFERENCES country(id),
   mail_code text not null check (mail_code ~ '[[:alnum:]_]'),
-  created date not null,
+  created date not null default now(),
   inactive_date timestamp default null,
   active boolean not null default TRUE
 );
diff --git a/sql/modules/Location.sql b/sql/modules/Location.sql
index e266f277..0597a241 100644
--- a/sql/modules/Location.sql
+++ b/sql/modules/Location.sql
@@ -46,8 +46,8 @@ BEGIN
 	        line_three,
 	        city,
 	        state,
-	        zipcode,
-	        country)
+	        mail_code,
+	        country_id)
 	    VALUES (
 	        location_id,
 	        in_address1,
@@ -64,7 +64,7 @@ BEGIN
 	    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;
+	        RAISE EXCEPTION 'location_save called with nonexistant location ID %', in_location_id;
 	    ELSE
 	        -- Okay, we're good.
 	        
@@ -74,8 +74,8 @@ BEGIN
 	            line_three = in_address3,
 	            city = in_city, 
 	            state = in_state,
-	            zipcode = in_zipcode,
-	            country = in_country
+	            mail_code = in_zipcode,
+	            country_id = in_country
 	        WHERE id = in_location_id;
 	        return in_location_id;
 	    END IF;
@@ -156,8 +156,32 @@ CREATE TYPE location_result AS (
 	line_three text,
 	city text,
 	state text,
-        mail_code text,
+    mail_code text,
 	country text,
-	class text
 );
 
+CREATE OR REPLACE FUNCTION location__get(in_id int) returns location_result AS $$
+
+declare
+    l_row location_result;
+begin
+    FOR l_row IN 
+        SELECT 
+            l.id,                   
+            l.line_one,             
+            l.line_two,             
+            l.line_three,           
+            l.city,                 
+            l.state,                
+            l.mail_code,            
+            c.name as country,      
+            NULL
+        FROM location l
+        JOIN country c on l.country_id = c.id
+        WHERE l.id = in_id
+    LOOP
+    
+        return l_row;
+    END LOOP;
+END;
+$$ language plpgsql ;
\ No newline at end of file
diff --git a/sql/modules/Person.sql b/sql/modules/Person.sql
index 546398e9..9bdc9e1f 100644
--- a/sql/modules/Person.sql
+++ b/sql/modules/Person.sql
@@ -129,11 +129,12 @@ DECLARE
 BEGIN
     
     SELECT cc.* into v_orig 
-    FROM contact_class cc, person p
+    FROM person_to_contact cc, person p
     WHERE p.entity_id = in_entity_id 
-    and contact_class = in_contact_class
-    AND contact = in_contact_orig
+    and cc.contact_class_id = in_contact_class
+    AND cc.contact = in_contact_orig
     AND cc.person_id = p.id;
+    
     IF NOT FOUND THEN
     
         -- create
@@ -163,6 +164,7 @@ $$ LANGUAGE PLPGSQL;
 create or replace function person__save_location(
     in_entity_id int, 
     in_location_id int,
+    in_location_class int,
     in_line_one text, 
     in_line_two text, 
     in_line_three text,
@@ -197,8 +199,8 @@ create or replace function person__save_location(
     		in_country_code);
     	
         INSERT INTO person_to_location 
-    		(person_id, location_id)
-    	VALUES  (t_person_id, l_id);
+    		(person_id, location_id, location_class)
+    	VALUES  (t_person_id, l_id, in_location_class);
     ELSE
         l_id := location_save(
             in_location_id, 
-- 
cgit v1.2.3