Minor db fixes

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

4 files changed, 34 insertions, 8 deletions

diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index 708ca081..85b5c39d 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -265,6 +265,7 @@ CREATE TABLE company_to_contact (
   company_id integer not null references company(id) ON DELETE CASCADE,
   contact_class_id integer references contact_class(id) not null,
   contact text check(contact ~ '[[:alnum:]_]') not null,
+  description text not null,
   PRIMARY KEY (company_id,contact_class_id,contact));  
 
 COMMENT ON TABLE company_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single company $$;
@@ -426,8 +427,10 @@ CREATE TABLE entity_credit_account (
     startdate date DEFAULT CURRENT_DATE,
     enddate date,
     threshold numeric default 0,
-    employee_id int references entity_employee(employee_id),
+    employee_id int references entity_employee(entity_id),
     primary_contact int references person(id),
+    ar_ap_account_id int references chart(id),
+    cash_account_id int references chart(id),
     PRIMARY KEY(entity_id, meta_number)
 );
 -- notes are from entity_note
diff --git a/sql/modules/Company.sql b/sql/modules/Company.sql
index b8359e27..cac1a5ac 100644
--- a/sql/modules/Company.sql
+++ b/sql/modules/Company.sql
@@ -16,7 +16,7 @@ CREATE OR REPLACE FUNCTION company__search
 (in_account_class int, in_contact text, in_contact_info text[], 
 	in_meta_number text, in_address text, in_city text, in_state text, 
 	in_mail_code text, in_country text, in_date_from date, in_date_to date,
-	in_business_id int)
+	in_business_id int, in_legal_name text)
 RETURNS SETOF company_search_result AS $$
 DECLARE
 	out_row company_search_result;
@@ -40,7 +40,11 @@ BEGIN
 			
 			AND ec.meta_number = 
 				coalesce(in_meta_number, ec.meta_number)
---			AND c.id IN 
+			AND c.legal_name like '%' || coalesce(in_legal_name, '') || '%'
+--			AND ((in_address IS NULL AND in_city IS NULL 
+--					AND in_state IS NULL 
+--					AND in_country IS NULL)
+--				OR (c.id IN 
 --				(select company_id FROM company_to_location
 --				WHERE location_id IN 
 --					(SELECT id FROM location
@@ -67,7 +71,7 @@ BEGIN
 --								in_country ||'%'
 --								OR short_name
 --								ilike 
---								in_country)))
+--								in_country)))))
 			AND ec.business_id = 
 				coalesce(in_business_id, ec.business_id)
 --			AND ec.startdate <= coalesce(in_date_to, 
@@ -116,7 +120,7 @@ CREATE TYPE entity_credit_search_return AS (
         taxincluded bool,
         creditlimit numeric,
         terms int2,
-        customernumber text,
+        meta_number text,
         business_id int,
         language_code text,
         pricegroup_id int,
@@ -286,6 +290,23 @@ 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 contact_list;
+BEGIN
+	FOR out_row IN
+		SELECT cl.class, c.contact
+		FROM company_to_contact c
+		JOIN contact_class cl ON (c.contact_class_id = cl.id)
+		WHERE company_id = 
+			(select id FROM company 
+			WHERE 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/Customer.sql b/sql/modules/Customer.sql
index a024b9fc..317405b0 100644
--- a/sql/modules/Customer.sql
+++ b/sql/modules/Customer.sql
@@ -16,6 +16,7 @@ CREATE OR REPLACE FUNCTION customer_location_save (
 
 $$ language 'plpgsql';
 
+/* Disabling until we can work on this a little more.
 
 CREATE OR REPLACE FUNCTION customer_search(in_pattern TEXT) returns setof customer_search_return as $$
     
@@ -48,6 +49,7 @@ CREATE OR REPLACE FUNCTION customer_search(in_pattern TEXT) returns setof custom
     
     end;
     
-$$ language 'plpgsql';
+$$ language 'plpgsql'; 
+*/
 
 COMMIT;
diff --git a/sql/modules/Vendor.sql b/sql/modules/Vendor.sql
index 1675d5ee..6676643b 100644
--- a/sql/modules/Vendor.sql
+++ b/sql/modules/Vendor.sql
@@ -1,7 +1,7 @@
 BEGIN;
 
 -- TODO:  Move indexes to Pg-database
-
+/*  Disabling until we can do some more refactoring here.
 CREATE OR REPLACE FUNCTION vendor_search(in_name TEXT, in_address TEXT, 
     in_city_prov TEXT) 
     RETURNS SETOF vendor_search_return AS $$
@@ -40,5 +40,5 @@ CREATE OR REPLACE FUNCTION vendor_search(in_name TEXT, in_address TEXT,
     end;
     
 $$ language 'plpgsql';
-
+*/
 COMMIT;