diff options
| -rw-r--r-- | sql/Pg-database.sql | 5 | ||||
| -rw-r--r-- | sql/modules/Company.sql | 29 | ||||
| -rw-r--r-- | sql/modules/Customer.sql | 4 | ||||
| -rw-r--r-- | sql/modules/Vendor.sql | 4 |
4 files changed, 34 insertions, 8 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index 708ca081..85b5c39d 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -265,6 +265,7 @@ CREATE TABLE company_to_contact ( 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 @@ CREATE TABLE entity_credit_account ( 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 diff --git a/sql/modules/Company.sql b/sql/modules/Company.sql index b8359e27..cac1a5ac 100644 --- a/sql/modules/Company.sql +++ b/sql/modules/Company.sql @@ -16,7 +16,7 @@ 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) + in_business_id int, in_legal_name text) RETURNS SETOF company_search_result AS $$ DECLARE out_row company_search_result; @@ -40,7 +40,11 @@ BEGIN 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 @@ BEGIN -- 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 @@ CREATE TYPE entity_credit_search_return AS ( taxincluded bool, creditlimit numeric, terms int2, - customernumber text, + meta_number text, business_id int, language_code text, pricegroup_id int, @@ -286,6 +290,23 @@ CREATE TYPE contact_list AS ( 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 diff --git a/sql/modules/Customer.sql b/sql/modules/Customer.sql index a024b9fc..317405b0 100644 --- a/sql/modules/Customer.sql +++ b/sql/modules/Customer.sql @@ -16,6 +16,7 @@ CREATE OR REPLACE FUNCTION customer_location_save ( $$ 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 @@ CREATE OR REPLACE FUNCTION customer_search(in_pattern TEXT) returns setof custom end; -$$ language 'plpgsql'; +$$ language 'plpgsql'; +*/ COMMIT; diff --git a/sql/modules/Vendor.sql b/sql/modules/Vendor.sql index 1675d5ee..6676643b 100644 --- a/sql/modules/Vendor.sql +++ b/sql/modules/Vendor.sql @@ -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 @@ CREATE OR REPLACE FUNCTION vendor_search(in_name TEXT, in_address TEXT, end; $$ language 'plpgsql'; - +*/ COMMIT; |