SF.net SVN: ledger-smb: [2153] trunk/sql/modules/Company.sql

[..hidden..]

Revision: 2153
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2153&view=rev
Author:   einhverfr
Date:     2008-05-21 11:07:00 -0700 (Wed, 21 May 2008)

Log Message:
-----------
Activating address-based searches and correcting bugs in vendor search.

Modified Paths:
--------------
    trunk/sql/modules/Company.sql

Modified: trunk/sql/modules/Company.sql
===================================================================
--- trunk/sql/modules/Company.sql	2008-05-19 22:36:45 UTC (rev 2152)
+++ trunk/sql/modules/Company.sql	2008-05-21 18:07:00 UTC (rev 2153)
@@ -28,7 +28,7 @@
 
 	FOR out_row IN
 		SELECT e.id, c.id, ec.id, ec.meta_number, ec.entity_class, 
-			c.legal_name, c.sic_code, b.description , ec.curr
+			c.legal_name, c.sic_code, b.description , ec.curr::text
 		FROM entity e
 		JOIN company c ON (e.id = c.entity_id)
 		JOIN entity_credit_account ec ON (ec.entity_id = e.id)
@@ -38,45 +38,50 @@
 				WHERE contact LIKE ALL(t_contact_info))
 				OR '' LIKE ALL(t_contact_info))
 			
-			AND ec.meta_number = 
-				coalesce(in_meta_number, ec.meta_number)
-			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
---					WHERE line_one 
---						ilike '%' || 
---							coalesce(in_address, '')
---							|| '%'
---						AND city ILIKE 
---							'%' || 
---							coalesce(in_city, '') 
---							|| '%'
---						AND state ILIKE
---							'%' || 
---							coalesce(in_state, '') 
---							|| '%'
---						AND mail_code ILIKE
---							'%' || 
---							coalesce(in_mail_code,
---								'')
---							|| '%'
---						AND country_id IN 
---							(SELECT id FROM country
---							WHERE name LIKE '%' ||
---								in_country ||'%'
---								OR short_name
---								ilike 
---								in_country)))))
-			AND ec.business_id = 
+			AND (ec.meta_number = in_meta_number 
+				OR in_meta_number IS NULL)
+			AND (c.legal_name like '%' || in_legal_name || '%'
+				OR legal_name IS NULL)
+			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
+					WHERE line_one 
+						ilike '%' || 
+							coalesce(in_address, '')
+							|| '%'
+						AND city ILIKE 
+							'%' || 
+							coalesce(in_city, '') 
+							|| '%'
+						AND state ILIKE
+							'%' || 
+							coalesce(in_state, '') 
+							|| '%'
+						AND mail_code ILIKE
+							'%' || 
+							coalesce(in_mail_code,
+								'')
+							|| '%'
+						AND country_id IN 
+							(SELECT id FROM country
+							WHERE name LIKE '%' ||
+								in_country ||'%'
+								OR short_name
+								ilike 
+								in_country)))))
+			AND (ec.business_id = 
 				coalesce(in_business_id, ec.business_id)
---			AND ec.startdate <= coalesce(in_date_to, 
---						ec.startdate)
---			AND ec.enddate >= coalesce(in_date_from, ec.enddate)
+				OR (ec.business_id IS NULL 
+					AND in_business_id IS NULL))
+			AND (ec.startdate <= coalesce(in_date_to, 
+						ec.startdate)
+				OR (ec.startdate IS NULL)
+			AND (ec.enddate >= coalesce(in_date_from, ec.enddate)
+				OR (ec.enddate IS NULL))
 	LOOP
 		RETURN NEXT out_row;
 	END LOOP;


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