SF.net SVN: ledger-smb: [1973] trunk/sql

[..hidden..]

Revision: 1973
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=1973&view=rev
Author:   einhverfr
Date:     2007-12-11 09:28:19 -0800 (Tue, 11 Dec 2007)

Log Message:
-----------
Minor db fixes

Modified Paths:
--------------
    trunk/sql/Pg-database.sql
    trunk/sql/modules/Company.sql
    trunk/sql/modules/Customer.sql
    trunk/sql/modules/Vendor.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2007-12-11 05:47:39 UTC (rev 1972)
+++ trunk/sql/Pg-database.sql	2007-12-11 17:28:19 UTC (rev 1973)
@@ -265,6 +265,7 @@
   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 @@
     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

Modified: trunk/sql/modules/Company.sql
===================================================================
--- trunk/sql/modules/Company.sql	2007-12-11 05:47:39 UTC (rev 1972)
+++ trunk/sql/modules/Company.sql	2007-12-11 17:28:19 UTC (rev 1973)
@@ -16,7 +16,7 @@
 (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 @@
 			
 			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 @@
 --								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 @@
         taxincluded bool,
         creditlimit numeric,
         terms int2,
-        customernumber text,
+        meta_number text,
         business_id int,
         language_code text,
         pricegroup_id int,
@@ -286,6 +290,23 @@
 	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

Modified: trunk/sql/modules/Customer.sql
===================================================================
--- trunk/sql/modules/Customer.sql	2007-12-11 05:47:39 UTC (rev 1972)
+++ trunk/sql/modules/Customer.sql	2007-12-11 17:28:19 UTC (rev 1973)
@@ -16,6 +16,7 @@
 
 $$ 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 @@
     
     end;
     
-$$ language 'plpgsql';
+$$ language 'plpgsql'; 
+*/
 
 COMMIT;

Modified: trunk/sql/modules/Vendor.sql
===================================================================
--- trunk/sql/modules/Vendor.sql	2007-12-11 05:47:39 UTC (rev 1972)
+++ trunk/sql/modules/Vendor.sql	2007-12-11 17:28:19 UTC (rev 1973)
@@ -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 @@
     end;
     
 $$ language 'plpgsql';
-
+*/
 COMMIT;


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.