diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/Pg-database.sql | 6 | ||||
| -rw-r--r-- | sql/modules/Company.sql | 83 |
2 files changed, 85 insertions, 4 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index 3e51a356..2b82eb8e 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -426,6 +426,8 @@ CREATE TABLE entity_credit_account ( startdate date DEFAULT CURRENT_DATE, enddate date, threshold numeric default 0, + employee_id int references employee_entity(employee_id), + primary_contact int references person(id), PRIMARY KEY(entity_id, meta_number) ); -- notes are from entity_note @@ -614,7 +616,7 @@ CREATE TABLE ar ( on_hold bool default false, reverse bool default false, approved bool default true, - credit_account int references entity_credit_account(id) not null, + entity_credit_account int references entity_credit_account(id) not null, description text ); @@ -650,7 +652,7 @@ CREATE TABLE ap ( reverse bool default false, terms int2 DEFAULT 0, description text, - credit_account int references entity_credit_account(id) + entity_credit_account int references entity_credit_account(id) ); COMMENT ON COLUMN ap.entity_id IS $$ Used to be customer_id, but customer is now metadata. You need to push to entity $$; 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; |