summaryrefslogtreecommitdiff
path: root/sql/modules
diff options
context:
space:
mode:
authoreinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2007-12-08 09:07:43 +0000
committereinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2007-12-08 09:07:43 +0000
commitf71583152a52b4d07161af92d286306ba4397988 (patch)
tree6666920d646f4adf80c7d7e30081d718b7d09cdf /sql/modules
parenta4af5b47f7d6942910ae06e13993035a074bcaf2 (diff)
More customer/vendor search enhancements
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1966 4979c152-3d1c-0410-bac9-87ea11338e46
Diffstat (limited to 'sql/modules')
-rw-r--r--sql/modules/Company.sql83
1 files changed, 81 insertions, 2 deletions
diff --git a/sql/modules/Company.sql b/sql/modules/Company.sql
index 8d78c946..b8359e27 100644
--- a/sql/modules/Company.sql
+++ b/sql/modules/Company.sql
@@ -1,4 +1,83 @@
-BEGIN;
+-- BEGIN;
+
+CREATE TYPE company_search_result AS (
+ entity_id int,
+ company_id int,
+ entity_credit_id int,
+ meta_number text,
+ entity_class int,
+ legal_name text,
+ sic_code text,
+ business_type text,
+ curr text
+);
+
+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)
+RETURNS SETOF company_search_result AS $$
+DECLARE
+ out_row company_search_result;
+ loop_count int;
+ t_contact_info text[];
+BEGIN
+ t_contact_info = in_contact_info;
+
+
+ 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
+ FROM entity e
+ JOIN company c ON (e.id = c.entity_id)
+ JOIN entity_credit_account ec ON (ec.entity_id = e.id)
+ LEFT JOIN business b ON (ec.business_id = b.id)
+ WHERE ec.entity_class = in_account_class
+ AND (c.id IN (select company_id FROM company_to_contact
+ 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.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)
+ LOOP
+ RETURN NEXT out_row;
+ END LOOP;
+END;
+$$ language plpgsql;
CREATE OR REPLACE FUNCTION entity__save_notes(in_entity_id int, in_note text)
RETURNS INT AS
@@ -325,4 +404,4 @@ create or replace function _entity_location_save(
$$ language 'plpgsql';
-COMMIT;
+-- COMMIT;