[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4359] trunk
- Subject: SF.net SVN: ledger-smb:[4359] trunk
- From: ..hidden..
- Date: Fri, 24 Feb 2012 13:27:29 +0000
Revision: 4359
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4359&view=rev
Author: einhverfr
Date: 2012-02-24 13:27:28 +0000 (Fri, 24 Feb 2012)
Log Message:
-----------
Simplifying customer/vendor schema so that persons can be used as well as companies
Modified Paths:
--------------
trunk/Changelog
trunk/LedgerSMB/OE.pm
trunk/sql/Pg-database.sql
trunk/sql/modules/Company.sql
trunk/sql/modules/Customer.sql
trunk/sql/modules/Employee.sql
trunk/sql/modules/Payment.sql
trunk/sql/modules/Person.sql
trunk/sql/modules/Roles.sql
trunk/sql/modules/Vendor.sql
Modified: trunk/Changelog
===================================================================
--- trunk/Changelog 2012-02-24 12:40:29 UTC (rev 4358)
+++ trunk/Changelog 2012-02-24 13:27:28 UTC (rev 4359)
@@ -12,6 +12,7 @@
Customer/Vendor Handling
* Added sales tax id and license number fields for companies (Chris T)
+* Simpified database schema (Chris T)
New CSV Import Module (Chris T)
* Imports GL transactions
Modified: trunk/LedgerSMB/OE.pm
===================================================================
--- trunk/LedgerSMB/OE.pm 2012-02-24 12:40:29 UTC (rev 4358)
+++ trunk/LedgerSMB/OE.pm 2012-02-24 13:27:28 UTC (rev 4359)
@@ -2378,7 +2378,7 @@
$query = qq|
SELECT v.curr, v.taxincluded, t.rate, c.accno
FROM vendor v
- LEFT JOIN vendortax vt ON (v.id = vt.vendor_id)
+ LEFT JOIN eca_tax vt ON (v.id = vt.vendor_id)
LEFT JOIN tax t ON (t.chart_id = vt.chart_id)
LEFT JOIN chart c ON (c.id = t.chart_id)
WHERE v.id = ?|;
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2012-02-24 12:40:29 UTC (rev 4358)
+++ trunk/sql/Pg-database.sql 2012-02-24 13:27:28 UTC (rev 4359)
@@ -504,14 +504,6 @@
SELECT setval('entity_class_id_seq',7);
-CREATE TABLE entity_class_to_entity (
- entity_class_id integer not null references entity_class(id) ON DELETE CASCADE,
- entity_id integer not null references entity(id) ON DELETE CASCADE,
- PRIMARY KEY(entity_class_id,entity_id)
- );
-
-COMMENT ON TABLE entity_class_to_entity IS $$ Relation builder for classes to entity $$;
-
-- USERS stuff --
CREATE TABLE users (
id serial UNIQUE,
@@ -658,13 +650,13 @@
COMMENT ON COLUMN company.tax_id IS $$ In the US this would be a EIN. $$;
-CREATE TABLE company_to_location (
+CREATE TABLE entity_to_location (
location_id integer references location(id) not null,
location_class integer not null references location_class(id),
- company_id integer not null references company(id) ON DELETE CASCADE,
- PRIMARY KEY(location_id,company_id, location_class));
+ entity_id integer not null references entity(id) ON DELETE CASCADE,
+ PRIMARY KEY(location_id, entity_id, location_class));
-COMMENT ON TABLE company_to_location IS
+COMMENT ON TABLE entity_to_location IS
$$ This table is used for locations generic to companies. For contract-bound
addresses, use eca_to_location instead $$;
@@ -711,12 +703,6 @@
COMMENT ON TABLE entity_employee IS
$$ This contains employee-specific extensions to person/entity. $$;
-CREATE TABLE person_to_location (
- location_id integer not null references location(id),
- location_class integer not null references location_class(id),
- person_id integer not null references person(id) ON DELETE CASCADE,
- PRIMARY KEY (location_id,person_id));
-
CREATE TABLE person_to_company (
location_id integer references location(id) not null,
person_id integer not null references person(id) ON DELETE CASCADE,
@@ -736,26 +722,6 @@
may be jd, Joshua Drake, linuxpoet... all are the same person. Currently
unused in the front-end but will likely be added in future versions.$$;
-CREATE TABLE person_to_entity (
- person_id integer not null references person(id) ON DELETE CASCADE,
- entity_id integer not null check (entity_id != person_id) references entity(id) ON DELETE CASCADE,
- related_how text,
- created date not null default current_date,
- PRIMARY KEY (person_id,entity_id));
-
-COMMENT ON TABLE person_to_entity IS
-$$ This provides a map so that entities can also be used like groups.$$;
-
-CREATE TABLE company_to_entity (
- company_id integer not null references company(id) ON DELETE CASCADE,
- entity_id integer check (company_id != entity_id) not null references entity(id) ON DELETE CASCADE,
- related_how text,
- created date not null default current_date,
- PRIMARY KEY (company_id,entity_id));
-
-COMMENT ON TABLE company_to_entity IS
-$$ This provides a map so that entities can also be used like groups.$$;
-
CREATE TABLE contact_class (
id serial UNIQUE,
class text check (class ~ '[[:alnum:]_]') NOT NULL,
@@ -789,26 +755,16 @@
SELECT SETVAL('contact_class_id_seq',17);
-CREATE TABLE person_to_contact (
- person_id integer not null references person(id) ON DELETE CASCADE,
+CREATE TABLE entity_to_contact (
+ entity_id integer not null references person(id) ON DELETE CASCADE,
contact_class_id integer references contact_class(id) not null,
contact text check(contact ~ '[[:alnum:]_]') not null,
description text,
PRIMARY KEY (person_id,contact_class_id,contact));
-COMMENT ON TABLE person_to_contact IS
-$$ This table stores contact information for persons$$;
+COMMENT ON TABLE entity_to_contact IS
+$$ This table stores contact information for entities$$;
-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,
- PRIMARY KEY (company_id, contact_class_id, contact));
-
-COMMENT ON TABLE person_to_contact IS
-$$ This table stores contact information for companies$$;
-
CREATE TABLE entity_bank_account (
id serial not null,
entity_id int not null references entity(id) ON DELETE CASCADE,
@@ -883,7 +839,7 @@
PRIMARY KEY (credit_id, contact_class_id, contact));
COMMENT ON TABLE eca_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single vendor or customer account. For generic
-contacts, use company_to_contact or person_to_contact instead.$$;
+contacts, use entity_to_contact instead.$$;
CREATE TABLE eca_to_location (
location_id integer references location(id) not null,
@@ -897,7 +853,7 @@
COMMENT ON TABLE eca_to_location IS
$$ This table is used for locations bound to contracts. For generic contact
-addresses, use company_to_location instead $$;
+addresses, use entity_to_location instead $$;
-- Begin rocking notes interface
-- Begin rocking notes interface
@@ -1674,22 +1630,14 @@
cumultative sales tax rules (for example, Quebec charging taxes on the federal
taxes collected).$$;
--
-CREATE TABLE customertax (
- customer_id int references entity_credit_account(id) on delete cascade,
+CREATE TABLE eca_tax (
+ eca_id int references entity_credit_account(id) on delete cascade,
chart_id int REFERENCES account(id),
PRIMARY KEY (customer_id, chart_id)
);
-COMMENT ON TABLE customertax IS $$ Mapping customer to taxes.$$;
+COMMENT ON TABLE eca_tax IS $$ Mapping customers and vendors to taxes.$$;
--
-CREATE TABLE vendortax (
- vendor_id int references entity_credit_account(id) on delete cascade,
- chart_id int REFERENCES account(id),
- PRIMARY KEY (vendor_id, chart_id)
-);
---
-COMMENT ON TABLE vendortax IS $$ Mapping vendor to taxes.$$;
-
CREATE TABLE oe_class (
id smallint unique check(id IN (1,2,3,4)),
oe_class text primary key);
@@ -2185,8 +2133,6 @@
create index assembly_id_key on assembly (id);
--
-create index customer_customer_id_key on customertax (customer_id);
---
create index exchangerate_ct_key on exchangerate (curr, transdate);
--
create unique index gifi_accno_key on gifi (accno);
Modified: trunk/sql/modules/Company.sql
===================================================================
--- trunk/sql/modules/Company.sql 2012-02-24 12:40:29 UTC (rev 4358)
+++ trunk/sql/modules/Company.sql 2012-02-24 13:27:28 UTC (rev 4359)
@@ -235,17 +235,20 @@
WHERE in_meta_number IS NULL) ec ON (ec.entity_id = e.id)
LEFT JOIN business b ON (ec.business_id = b.id)
WHERE coalesce(ec.entity_class,e.entity_class) = in_account_class
- AND (c.id IN (select company_id FROM company_to_contact
- WHERE contact ILIKE ALL(t_contact_info))
- OR '' ILIKE ALL(t_contact_info))
+ AND (c.entity_id IN (select entity_id
+ FROM entity_to_contact
+ WHERE contact ILIKE
+ ANY(t_contact_info))
+ OR '' ILIKE
+ ALL(t_contact_info))
AND (c.legal_name ilike '%' || in_legal_name || '%'
OR in_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
+ OR (c.entity_id IN
+ (select entity_id FROM entity_to_location
WHERE location_id IN
(SELECT id FROM location
WHERE line_one
@@ -299,11 +302,9 @@
matches too. All other values specify ranges or may match partially.$$;
CREATE OR REPLACE FUNCTION eca__get_taxes(in_credit_id int)
-returns setof customertax AS
+returns setof eca_tax AS
$$
-select * from customertax where customer_id = $1
-union
-select * from vendortax where vendor_id = $1;
+select * from eca_tax where customer_id = $1;
$$ language sql;
COMMENT ON FUNCTION eca__get_taxes(in_credit_id int) IS
@@ -321,23 +322,12 @@
END IF;
SELECT * FROM entity_credit_account into eca WHERE id = in_credit_id;
- IF eca.entity_class = 1 then
- DELETE FROM vendortax WHERE vendor_id = in_credit_id;
- FOR iter in array_lower(in_tax_ids, 1) .. array_upper(in_tax_ids, 1)
- LOOP
- INSERT INTO vendortax (vendor_id, chart_id)
- values (in_credit_id, in_tax_ids[iter]);
- END LOOP;
- ELSIF eca.entity_class = 2 then
- DELETE FROM customertax WHERE customer_id = in_credit_id;
- FOR iter in array_lower(in_tax_ids, 1) .. array_upper(in_tax_ids, 1)
- LOOP
- INSERT INTO customertax (customer_id, chart_id)
- values (in_credit_id, in_tax_ids[iter]);
- END LOOP;
- ELSE
- RAISE EXCEPTION 'Wrong entity class or credit account not found!';
- END IF;
+ DELETE FROM eca_tax WHERE eca_id = in_credit_id;
+ FOR iter in array_lower(in_tax_ids, 1) .. array_upper(in_tax_ids, 1)
+ LOOP
+ INSERT INTO eca_tax (eca__id, chart_id)
+ values (in_credit_id, in_tax_ids[iter]);
+ END LOOP;
RETURN TRUE;
end;
$$ language plpgsql;
@@ -859,10 +849,10 @@
SELECT l.id, l.line_one, l.line_two, l.line_three, l.city,
l.state, l.mail_code, c.id, c.name, lc.id, lc.class
FROM location l
- JOIN company_to_location ctl ON (ctl.location_id = l.id)
+ JOIN entity_to_location ctl ON (ctl.location_id = l.id)
JOIN location_class lc ON (ctl.location_class = lc.id)
JOIN country c ON (c.id = l.country_id)
- WHERE ctl.company_id = (select id from company where entity_id = in_entity_id)
+ WHERE ctl.entity_id = in_entity_id
ORDER BY lc.id, l.id, c.name
LOOP
RETURN NEXT out_row;
@@ -887,11 +877,9 @@
BEGIN
FOR out_row IN
SELECT cl.class, cl.id, c.description, c.contact
- FROM company_to_contact c
+ FROM entity_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)
+ WHERE c.entity_id = in_entity_id
LOOP
return next out_row;
END LOOP;
@@ -983,8 +971,9 @@
returns bool as $$
BEGIN
-DELETE FROM company_to_contact
- WHERE company_id = in_company_id and contact_class_id = in_contact_class_id
+DELETE FROM entity_to_contact
+ WHERE entity_id = (select entity_id from company where id = in_companu_id)
+ and contact_class_id = in_contact_class_id
and contact= in_contact;
RETURN FOUND;
@@ -1022,10 +1011,9 @@
$$
DECLARE out_id int;
BEGIN
- INSERT INTO company_to_contact(company_id, contact_class_id,
- description, contact)
- SELECT id, in_contact_class, in_description, in_contact FROM company
- WHERE entity_id = in_entity_id;
+ INSERT INTO entity_to_contact
+ (entity_id, contact_class_id, description, contact)
+ VALUES (entity_id, in_contact_class, in_description, in_contact);
RETURN 1;
END;
@@ -1134,8 +1122,8 @@
SELECT id INTO t_company_id
FROM company WHERE entity_id = in_entity_id;
- DELETE FROM company_to_location
- WHERE company_id = t_company_id
+ DELETE FROM entity_to_location
+ WHERE company_id = in_entity_id
AND location_class = in_location_class
AND location_id = in_location_id;
@@ -1143,9 +1131,9 @@
in_state, in_mail_code, in_country_code)
INTO l_id;
- INSERT INTO company_to_location
+ INSERT INTO entity_to_location
(company_id, location_class, location_id)
- VALUES (t_company_id, in_location_class, l_id);
+ VALUES (in_entity_id, in_location_class, l_id);
RETURN l_id;
END;
Modified: trunk/sql/modules/Customer.sql
===================================================================
--- trunk/sql/modules/Customer.sql 2012-02-24 12:40:29 UTC (rev 4358)
+++ trunk/sql/modules/Customer.sql 2012-02-24 13:27:28 UTC (rev 4359)
@@ -13,38 +13,3 @@
$$ 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 $$
-
- -- searches customer name, account number, street address, city, state,
- -- other location-based stuff
-
- declare
- v_row customer_search_return;
- query text;
- begin
-
- for v_row in select c.legal_name, v.* from customer v
- join company c on c.entity_id = v.entity_id
- join entity e on e.id = v.entity_id
- join company_to_location ctl on c.id = ctl.company_id
- join location l on l.id = ctl.location_id
- where l.line_one % in_pattern
- OR l.line_two % in_pattern
- OR l.line_three % in_pattern
- OR l.city_province % in_pattern
- OR c.legal_name % in_pattern
- OR e.name % in_pattern
- LOOP
-
- RETURN NEXT v_row;
-
- END LOOP;
-
- RETURN;
-
- end;
-
-$$ language 'plpgsql';
-*/
Modified: trunk/sql/modules/Employee.sql
===================================================================
--- trunk/sql/modules/Employee.sql 2012-02-24 12:40:29 UTC (rev 4358)
+++ trunk/sql/modules/Employee.sql 2012-02-24 13:27:28 UTC (rev 4359)
@@ -227,8 +227,8 @@
(in_employee int, in_location int)
returns void as $$
- INSERT INTO person_to_location (person_id,location_id)
- VALUES ($1, $2);
-
+ INSERT INTO entity_to_location (entity_id,location_id)
+ SELECT entity_id, $2
+ FROM person WHERE id = $1;
$$ language 'sql';
Modified: trunk/sql/modules/Payment.sql
===================================================================
--- trunk/sql/modules/Payment.sql 2012-02-24 12:40:29 UTC (rev 4358)
+++ trunk/sql/modules/Payment.sql 2012-02-24 13:27:28 UTC (rev 4359)
@@ -932,8 +932,8 @@
SELECT l.id, l.line_one, l.line_two, l.line_three, l.city,
l.state, l.mail_code, c.name, lc.class
FROM location l
- JOIN company_to_location ctl ON (ctl.location_id = l.id)
- JOIN company cp ON (ctl.company_id = cp.id)
+ JOIN entity_to_location ctl ON (ctl.location_id = l.id)
+ JOIN entity cp ON (ctl.entity_id = cp.id)
JOIN location_class lc ON (ctl.location_class = lc.id)
JOIN country c ON (c.id = l.country_id)
JOIN entity_credit_account ec ON (ec.entity_id = cp.entity_id)
Modified: trunk/sql/modules/Person.sql
===================================================================
--- trunk/sql/modules/Person.sql 2012-02-24 12:40:29 UTC (rev 4358)
+++ trunk/sql/modules/Person.sql 2012-02-24 13:27:28 UTC (rev 4359)
@@ -96,8 +96,8 @@
SELECT l.id, l.line_one, l.line_two, l.line_three, l.city,
l.state, l.mail_code, c.id, c.name, lc.id, lc.class
FROM location l
- JOIN person_to_location ctl ON (ctl.location_id = l.id)
- JOIN person p ON (ctl.person_id = p.id)
+ JOIN entity_to_location ctl ON (ctl.location_id = l.id)
+ JOIN person p ON (ctl.person_id = p.entity_id)
JOIN location_class lc ON (ctl.location_class = lc.id)
JOIN country c ON (c.id = l.country_id)
WHERE p.entity_id = in_entity_id
@@ -118,9 +118,9 @@
BEGIN
FOR out_row IN
SELECT cc.class, cc.id, c.description, c.contact
- FROM person_to_contact c
+ FROM entity_to_contact c
JOIN contact_class cc ON (c.contact_class_id = cc.id)
- JOIN person p ON (c.person_id = p.id)
+ JOIN person p ON (c.person_id = p.entity_id)
WHERE p.entity_id = in_entity_id
LOOP
RETURN NEXT out_row;
@@ -137,8 +137,9 @@
returns bool as $$
BEGIN
-DELETE FROM person_to_contact
- WHERE person_id = in_person_id and contact_class_id = in_contact_class_id
+DELETE FROM entity_to_contact
+ WHERE person_id = (SELECT entity_id FROM person WHERE id = in_person_id)
+ and contact_class_id = in_contact_class_id
and contact= in_contact;
RETURN FOUND;
@@ -160,33 +161,30 @@
$$
DECLARE
out_id int;
- v_orig person_to_contact;
+ v_orig entity_to_contact;
BEGIN
SELECT cc.* into v_orig
- FROM person_to_contact cc, person p
- WHERE p.entity_id = in_entity_id
+ FROM entity_to_contact cc
+ JOIN person p ON (p.entity_id = cc.entity_id)
+ WHERE p.entity_id = in_entity_id
and cc.contact_class_id = in_old_contact_class
- AND cc.contact = in_old_contact
- AND cc.person_id = p.id;
+ AND cc.contact = in_old_contact;
IF NOT FOUND THEN
-- create
- INSERT INTO person_to_contact(person_id, contact_class_id, contact, description)
- VALUES (
- (SELECT id FROM person WHERE entity_id = in_entity_id),
- in_contact_class,
- in_contact_new,
- in_description
- );
+ INSERT INTO entity_to_contact
+ (entity_id, contact_class_id, contact, description)
+ VALUES (in_entity_id, in_contact_class, in_contact_new, in_description);
+
return 1;
ELSE
-- edit.
- UPDATE person_to_contact
+ UPDATE entity_to_contact
SET contact = in_contact_new, description = in_description
WHERE contact = in_old_contact
- AND person_id = v_orig.person_id
+ AND entity_id = in_entity_id
AND contact_class_id = in_old_contact_class;
return 0;
END IF;
@@ -240,8 +238,9 @@
$$
BEGIN
-DELETE FROM person_to_location
- WHERE person_id = in_person_id AND location_id = in_location_id
+DELETE FROM entity_to_location
+ WHERE person_id = (select entity_id from person where id = in_person_id)
+ AND location_id = in_location_id
AND location_class = in_location_class;
RETURN FOUND;
@@ -276,9 +275,9 @@
SELECT id INTO t_person_id
FROM person WHERE entity_id = in_entity_id;
- UPDATE person_to_location
+ UPDATE entity_to_location
SET location_class = in_location_class
- WHERE person_id = t_person_id
+ WHERE entity_id = in_entity_id
AND location_class = in_old_location_class
AND location_id = in_location_id;
@@ -295,9 +294,9 @@
in_mail_code,
in_country_code);
- INSERT INTO person_to_location
- (person_id, location_id, location_class)
- VALUES (t_person_id, l_id, in_location_class);
+ INSERT INTO entity_to_location
+ (entity_id, location_id, location_class)
+ VALUES (in_entity_id, l_id, in_location_class);
ELSE
l_id := location_save(
in_location_id,
Modified: trunk/sql/modules/Roles.sql
===================================================================
--- trunk/sql/modules/Roles.sql 2012-02-24 12:40:29 UTC (rev 4358)
+++ trunk/sql/modules/Roles.sql 2012-02-24 13:27:28 UTC (rev 4359)
@@ -69,24 +69,18 @@
GRANT SELECT ON location TO "lsmb_<?lsmb dbname ?>__contact_read";
GRANT SELECT ON person TO "lsmb_<?lsmb dbname ?>__contact_read";
GRANT SELECT ON entity_credit_account TO "lsmb_<?lsmb dbname ?>__contact_read";
-GRANT SELECT ON company_to_contact TO "lsmb_<?lsmb dbname ?>__contact_read";
-GRANT SELECT ON company_to_entity TO "lsmb_<?lsmb dbname ?>__contact_read";
-GRANT SELECT ON company_to_location TO "lsmb_<?lsmb dbname ?>__contact_read";
-GRANT SELECT ON customertax TO "lsmb_<?lsmb dbname ?>__contact_read";
+GRANT SELECT ON entity_to_location TO "lsmb_<?lsmb dbname ?>__contact_read";
+GRANT SELECT ON eca_tax TO "lsmb_<?lsmb dbname ?>__contact_read";
GRANT SELECT ON contact_class TO "lsmb_<?lsmb dbname ?>__contact_read";
GRANT SELECT ON entity_class TO "lsmb_<?lsmb dbname ?>__contact_read";
GRANT SELECT ON entity_bank_account TO "lsmb_<?lsmb dbname ?>__contact_read";
GRANT SELECT ON entity_note TO "lsmb_<?lsmb dbname ?>__contact_read";
-GRANT SELECT ON entity_class_to_entity TO "lsmb_<?lsmb dbname ?>__contact_read";
GRANT SELECT ON entity_other_name TO "lsmb_<?lsmb dbname ?>__contact_read";
GRANT SELECT ON location_class TO "lsmb_<?lsmb dbname ?>__contact_read";
GRANT SELECT ON person_to_company TO "lsmb_<?lsmb dbname ?>__contact_read";
-GRANT SELECT ON person_to_contact TO "lsmb_<?lsmb dbname ?>__contact_read";
-GRANT SELECT ON person_to_contact TO "lsmb_<?lsmb dbname ?>__contact_read";
-GRANT SELECT ON person_to_location TO "lsmb_<?lsmb dbname ?>__contact_read";
-GRANT SELECT ON person_to_location TO "lsmb_<?lsmb dbname ?>__contact_read";
-GRANT SELECT ON company_to_location TO "lsmb_<?lsmb dbname ?>__contact_read";
-GRANT SELECT ON vendortax TO "lsmb_<?lsmb dbname ?>__contact_read";
+GRANT SELECT ON entity_to_contact TO "lsmb_<?lsmb dbname ?>__contact_read";
+GRANT SELECT ON entity_to_contact TO "lsmb_<?lsmb dbname ?>__contact_read";
+GRANT SELECT ON entity_to_location TO "lsmb_<?lsmb dbname ?>__contact_read";
GRANT SELECT ON eca_to_location TO "lsmb_<?lsmb dbname ?>__contact_read";
GRANT SELECT ON eca_to_contact TO "lsmb_<?lsmb dbname ?>__contact_read";
GRANT EXECUTE ON FUNCTION eca__list_notes(int) TO "lsmb_<?lsmb dbname ?>__contact_read";
@@ -123,32 +117,25 @@
GRANT ALL ON person_id_seq TO "lsmb_<?lsmb dbname ?>__contact_create";
GRANT INSERT ON entity_credit_account TO "lsmb_<?lsmb dbname ?>__contact_create";
GRANT ALL ON entity_credit_account_id_seq TO "lsmb_<?lsmb dbname ?>__contact_create";
-GRANT INSERT ON company_to_contact TO "lsmb_<?lsmb dbname ?>__contact_create";
-GRANT INSERT ON company_to_entity TO "lsmb_<?lsmb dbname ?>__contact_create";
GRANT ALL ON note_id_seq TO "lsmb_<?lsmb dbname ?>__contact_create";
-GRANT INSERT ON company_to_location TO "lsmb_<?lsmb dbname ?>__contact_create";
-GRANT INSERT ON customertax TO "lsmb_<?lsmb dbname ?>__contact_create";
+GRANT INSERT ON entity_to_location TO "lsmb_<?lsmb dbname ?>__contact_create";
+GRANT INSERT ON eca_tax TO "lsmb_<?lsmb dbname ?>__contact_create";
GRANT INSERT ON entity_bank_account TO "lsmb_<?lsmb dbname ?>__contact_create";
GRANT ALL ON entity_bank_account_id_seq TO "lsmb_<?lsmb dbname ?>__contact_create";
GRANT INSERT ON entity_note TO "lsmb_<?lsmb dbname ?>__contact_create";
-GRANT INSERT ON entity_class_to_entity TO "lsmb_<?lsmb dbname ?>__contact_create";
GRANT INSERT ON entity_other_name TO "lsmb_<?lsmb dbname ?>__contact_create";
GRANT INSERT ON person_to_company TO "lsmb_<?lsmb dbname ?>__contact_create";
-GRANT INSERT ON person_to_contact TO "lsmb_<?lsmb dbname ?>__contact_create";
-GRANT INSERT ON person_to_contact TO "lsmb_<?lsmb dbname ?>__contact_create";
-GRANT INSERT ON person_to_location TO "lsmb_<?lsmb dbname ?>__contact_create";
-GRANT INSERT ON person_to_location TO "lsmb_<?lsmb dbname ?>__contact_create";
-GRANT INSERT ON company_to_location TO "lsmb_<?lsmb dbname ?>__contact_create";
-GRANT DELETE ON company_to_location TO "lsmb_<?lsmb dbname ?>__contact_create";
-GRANT INSERT ON vendortax TO "lsmb_<?lsmb dbname ?>__contact_create";
+GRANT INSERT ON entity_to_contact TO "lsmb_<?lsmb dbname ?>__contact_create";
+GRANT INSERT ON entity_to_contact TO "lsmb_<?lsmb dbname ?>__contact_create";
+GRANT INSERT ON entity_to_location TO "lsmb_<?lsmb dbname ?>__contact_create";
+GRANT DELETE ON entity_to_location TO "lsmb_<?lsmb dbname ?>__contact_create";
GRANT INSERT ON eca_to_location TO "lsmb_<?lsmb dbname ?>__contact_create";
GRANT DELETE ON eca_to_location TO "lsmb_<?lsmb dbname ?>__contact_create";
GRANT INSERT ON eca_to_contact TO "lsmb_<?lsmb dbname ?>__contact_create";
GRANT DELETE ON eca_to_contact TO "lsmb_<?lsmb dbname ?>__contact_create";
GRANT UPDATE ON eca_to_contact TO "lsmb_<?lsmb dbname ?>__contact_create";
GRANT INSERT ON eca_note TO "lsmb_<?lsmb dbname ?>__contact_create";
-GRANT ALL ON customertax TO"lsmb_<?lsmb dbname ?>__contact_create";
-GRANT ALL ON vendortax TO"lsmb_<?lsmb dbname ?>__contact_create";
+GRANT ALL ON eca_tax TO"lsmb_<?lsmb dbname ?>__contact_create";
INSERT INTO menu_acl (node_id, acl_type, role_name)
@@ -185,23 +172,17 @@
GRANT UPDATE ON location TO "lsmb_<?lsmb dbname ?>__contact_edit";
GRANT UPDATE ON person TO "lsmb_<?lsmb dbname ?>__contact_edit";
GRANT UPDATE ON entity_credit_account TO "lsmb_<?lsmb dbname ?>__contact_edit";
-GRANT UPDATE ON company_to_contact TO "lsmb_<?lsmb dbname ?>__contact_edit";
-GRANT UPDATE ON company_to_entity TO "lsmb_<?lsmb dbname ?>__contact_edit";
-GRANT UPDATE ON company_to_location TO "lsmb_<?lsmb dbname ?>__contact_edit";
-GRANT UPDATE ON customertax TO "lsmb_<?lsmb dbname ?>__contact_edit";
+GRANT UPDATE ON entity_to_location TO "lsmb_<?lsmb dbname ?>__contact_edit";
+GRANT UPDATE ON eca_tax TO "lsmb_<?lsmb dbname ?>__contact_edit";
GRANT UPDATE ON entity_bank_account TO "lsmb_<?lsmb dbname ?>__contact_edit";
GRANT UPDATE ON entity_note TO "lsmb_<?lsmb dbname ?>__contact_edit";
-GRANT UPDATE ON entity_class_to_entity TO "lsmb_<?lsmb dbname ?>__contact_edit";
GRANT UPDATE ON entity_other_name TO "lsmb_<?lsmb dbname ?>__contact_edit";
GRANT UPDATE ON person_to_company TO "lsmb_<?lsmb dbname ?>__contact_edit";
-GRANT UPDATE ON person_to_contact TO "lsmb_<?lsmb dbname ?>__contact_edit";
-GRANT UPDATE ON person_to_contact TO "lsmb_<?lsmb dbname ?>__contact_edit";
-GRANT UPDATE, DELETE ON person_to_location TO "lsmb_<?lsmb dbname ?>__contact_edit";
+GRANT UPDATE ON entity_to_contact TO "lsmb_<?lsmb dbname ?>__contact_edit";
+GRANT UPDATE ON entity_to_contact TO "lsmb_<?lsmb dbname ?>__contact_edit";
GRANT UPDATE ON eca_to_location TO "lsmb_<?lsmb dbname ?>__contact_edit";
-GRANT DELETE, INSERT ON vendortax TO "lsmb_<?lsmb dbname ?>__contact_edit";
GRANT DELETE, INSERT ON entity_bank_account TO "lsmb_<?lsmb dbname ?>__contact_edit";
-GRANT ALL ON customertax TO"lsmb_<?lsmb dbname ?>__contact_edit";
-GRANT ALL ON vendortax TO"lsmb_<?lsmb dbname ?>__contact_edit";
+GRANT ALL ON eca_tax TO"lsmb_<?lsmb dbname ?>__contact_edit";
CREATE ROLE "lsmb_<?lsmb dbname ?>__contact_all_rights"
WITH INHERIT NOLOGIN
Modified: trunk/sql/modules/Vendor.sql
===================================================================
--- trunk/sql/modules/Vendor.sql 2012-02-24 12:40:29 UTC (rev 4358)
+++ trunk/sql/modules/Vendor.sql 2012-02-24 13:27:28 UTC (rev 4359)
@@ -1,42 +1 @@
--- 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 $$
-
- -- searches vendor name, account number, street address, city, state,
- -- other location-based stuff
-
- declare
- v_row vendor_search_return;
- query text;
- begin
-
- for v_row in select c.legal_name, v.* from vendor v
- join company c on c.entity_id = v.entity_id
- join entity e on e.id = v.entity_id
- join company_to_location ctl on c.id = ctl.company_id
- join location l on l.id = ctl.location_id
- where (
- l.line_one % in_address
- OR l.line_two % in_address
- OR l.line_three % in_address
- )
- OR l.city_province % in_city_prov
- OR (
- c.legal_name % in_name
- OR e.name % in_name
- )
- LOOP
-
- RETURN NEXT v_row;
-
- END LOOP;
-
- RETURN;
-
- end;
-
-$$ language 'plpgsql';
-*/
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.