More customer/vendor screen/stored proc fixes. Note that since locations are mapped many<->many with companies and persons, I have moved the location class foreign key into the mapping tables.

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

4 files changed, 24 insertions, 48 deletions

diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index 67dc0a5a..9737b474 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -130,7 +130,6 @@ SELECT SETVAL('location_class_id_seq',4);
   
 CREATE TABLE location (
   id serial PRIMARY KEY,
-  location_class integer not null references location_class(id),
   line_one text check (line_one ~ '[[:alnum:]_]') NOT NULL,
   line_two text,
   line_three text,
@@ -156,6 +155,7 @@ COMMENT ON COLUMN company.tax_id IS $$ In the US this would be a EIN. $$;
 
 CREATE TABLE company_to_location (
   location_id integer references location(id) not null,
+  location_class integer not null references location_class(id),
   company_id integer not null references company(id) ON DELETE CASCADE,
   PRIMARY KEY(location_id,company_id));
 
@@ -201,6 +201,7 @@ create table entity_employee (
 
 CREATE TABLE person_to_location (
   location_id integer not null references location(id),
+  location_class integer not null references location_class(id),
   person_id integer not null references person(id) ON DELETE CASCADE,
   PRIMARY KEY (location_id,person_id));
 
diff --git a/sql/modules/Location.sql b/sql/modules/Location.sql
index fb93cf82..d4f8f9f5 100644
--- a/sql/modules/Location.sql
+++ b/sql/modules/Location.sql
@@ -1,6 +1,6 @@
 -- VERSION 1.3.0
 CREATE OR REPLACE FUNCTION location_save
-(in_companyname text, in_address1 text, in_address2 text, 
+(in_address1 text, in_address2 text, in_address3 text,
 	in_city text, in_state text, in_zipcode text, in_country text) 
 returns integer AS
 $$
@@ -10,9 +10,9 @@ DECLARE
 BEGIN
 	
 	SELECT * INTO location_row FROM location
-	WHERE companyname = in_companyname AND
-		address1 = in_address1 AND
-		address2 = in_address2 AND
+	WHERE line_one = in_address1 AND
+		line_two = in_address2 AND
+		line_three = in_address3 AND
 		city = in_city AND
 		state = in_state AND
 		zipcode = in_zipcode AND
diff --git a/sql/modules/Roles.sql b/sql/modules/Roles.sql
index a44ebcf4..864fb4e7 100644
--- a/sql/modules/Roles.sql
+++ b/sql/modules/Roles.sql
@@ -1376,3 +1376,4 @@ GRANT SELECT ON user_preference TO public;
 GRANT SELECT ON custom_table_catalog TO PUBLIC;
 GRANT SELECT ON custom_field_catalog TO PUBLIC;
 grant select on menu_node, menu_attribute, menu_acl to public;
+GRANT select on chart, gifi to public;
diff --git a/sql/modules/Vendor.sql b/sql/modules/Vendor.sql
index d075b62e..e18f88a5 100644
--- a/sql/modules/Vendor.sql
+++ b/sql/modules/Vendor.sql
@@ -170,9 +170,9 @@ $$ language 'plpgsql';
 
 
 create or replace function _entity_location_save(
-    in_company_id int,
-    in_location_class int, in_line_one text, in_line_two text, 
-    in_city_province TEXT, in_mail_code text, in_country_code int,
+    in_company_id int, in_location_id int,
+    in_location_class int, in_line_one text, in_line_two text,
+    in_city TEXT, in_state TEXT, in_mail_code text, in_country_code int,
     in_created date
 ) returns int AS $$
 
@@ -180,46 +180,20 @@ create or replace function _entity_location_save(
         l_row location;
         l_id INT;
     BEGIN
-    
-        SELECT l.* INTO l_row FROM location l 
-        JOIN company_to_location ctl ON ctl.location_id = l.id
-        JOIN company c on ctl.company_id = c.id
-        where c.id = in_company_id;
-        
-        IF NOT FOUND THEN
-        
-            l_id := nextval('location_id_seq');
-            
-            INSERT INTO location (id, location_class, line_one, line_two, 
-                 city_province, country_id, mail_code, created)
-            VALUES (
-                l_id,
-                in_location_class,
-                in_line_one,
-                in_line_two,
-                in_city_province,
-                in_country_code,
-                in_mail_code,
-                in_created
-            );
-            
-            INSERT INTO company_to_location (location_id, company_id)
-            VALUES (l_id, in_company_id);
-        
-        ELSIF FOUND THEN
-        
-            l_id := l.id;
-            update location SET
-                location_class = in_location_class,
-                line_one = in_line_one,
-                line_two = in_line_two,
-                city_province = in_city_province,
-                country_id = in_country_code,
-                mail_code = in_mail_code
-            WHERE id = l_id;        
-        
-        END IF;
-        return l_id;
+	DELETE FROM company_to_location
+	WHERE company_id = in_company_id
+		AND location_class = in_location_class
+		AND location_id = in_location_id;
+
+	SELECT location_save(in_line_one, in_line_two, NULL, in_city,
+		in_state, in_mail_code, in_mail_code, in_country_code) 
+	INTO l_id;
+
+	INSERT INTO company_to_location 
+		(company_id, location_class, location_id)
+	VALUES  (in_company_id, in_location_class, l_id);
+
+	RETURN l_id;    
     END;
 
 $$ language 'plpgsql';