summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authoreinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2008-05-21 18:07:00 +0000
committereinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2008-05-21 18:07:00 +0000
commit1626a8a24f559067bd67cfe20304d0c02717d2e0 (patch)
tree3a9a1cbf673c4b993c78512401c4e81ac771692c
parent819ab93566e8e604e756ac4646c87e46e59ee7ad (diff)
Activating address-based searches and correcting bugs in vendor search.
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2153 4979c152-3d1c-0410-bac9-87ea11338e46
-rw-r--r--sql/modules/Company.sql83
1 files changed, 44 insertions, 39 deletions
diff --git a/sql/modules/Company.sql b/sql/modules/Company.sql
index df6967a4..16dcde77 100644
--- a/sql/modules/Company.sql
+++ b/sql/modules/Company.sql
@@ -28,7 +28,7 @@ BEGIN
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 @@ BEGIN
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;