[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4307] trunk
- Subject: SF.net SVN: ledger-smb:[4307] trunk
- From: ..hidden..
- Date: Tue, 14 Feb 2012 10:08:59 +0000
Revision: 4307
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4307&view=rev
Author: einhverfr
Date: 2012-02-14 10:08:58 +0000 (Tue, 14 Feb 2012)
Log Message:
-----------
Initial database changes for recursive projects and departments. Now the work is in integrating this into the existing codebase
Modified Paths:
--------------
trunk/CONTRIBUTORS
trunk/Changelog
trunk/sql/Pg-database.sql
trunk/sql/modules/LOADORDER
trunk/sql/modules/Menu.sql
Added Paths:
-----------
trunk/sql/modules/Business_Unit.sql
Modified: trunk/CONTRIBUTORS
===================================================================
--- trunk/CONTRIBUTORS 2012-02-13 13:14:33 UTC (rev 4306)
+++ trunk/CONTRIBUTORS 2012-02-14 10:08:58 UTC (rev 4307)
@@ -104,6 +104,9 @@
Rich Walker <rw @ shadowrobot.com> has provided assistance in the dists/deb
documentation for Debian installations.
+Steven Marshall <steven.marshall @ tatylee.com> Contributed a bug fix for the
+menu.
+
Original Authors of SQL-Ledger:
===================================
Dieter Simader <dsimader @ sql-ledger.com>
Modified: trunk/Changelog
===================================================================
--- trunk/Changelog 2012-02-13 13:14:33 UTC (rev 4306)
+++ trunk/Changelog 2012-02-14 10:08:58 UTC (rev 4307)
@@ -3,7 +3,9 @@
* scripts/* files moved to make inheritance possible (Chris T)
* PostgreSQL contrib dependencies removed, now requre Pg 8.4 (Chris T)
-* Re-engineered schema for financial tables (Chris T)
+* Performance enhancements on menu routines (Chris T and Steven M)
+* Projects and Departments can now have subprojects and Departments (Chris T)
+* Project/department mechanism generalized to support funds, etc (Chris T)
Changelog for 1.3 Series
Initial Release: Monday, Oct 12 2011
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2012-02-13 13:14:33 UTC (rev 4306)
+++ trunk/sql/Pg-database.sql 2012-02-14 10:08:58 UTC (rev 4307)
@@ -7,13 +7,6 @@
CREATE SEQUENCE id;
-- As of 1.3 there is no central db anymore. --CT
-CREATE TABLE department (
- id serial PRIMARY KEY,
- description text,
- role char(1) default 'P'
-);
-
-COMMENT ON COLUMN department.role IS $$P for Profit Center, C for Cost Center$$;
--
CREATE TABLE language (
code varchar(6) PRIMARY KEY,
@@ -1104,8 +1097,6 @@
amount numeric not null check (amount <> 'NaN'),
cleared bool not null default false,
reconciliation_report int references cr_report(id),
- project_id int,
- department_id int references department(id) not null,
line_type text references account_link_description,
primary key (id)
);
@@ -1165,8 +1156,7 @@
transdate date DEFAULT current_date,
person_id integer references person(id),
notes text,
- approved bool default true,
- department_id int default 0
+ approved bool default true
);
COMMENT ON TABLE gl IS
@@ -1294,7 +1284,6 @@
source text,
cleared bool DEFAULT 'f',
fx_transaction bool DEFAULT 'f',
- project_id int,
memo text,
invoice_id int,
approved bool default true,
@@ -1341,7 +1330,6 @@
drawing text,
microfiche text,
partsgroup_id int,
- project_id int,
avgcost NUMERIC
);
@@ -1382,7 +1370,6 @@
discount numeric,
assemblyitem bool DEFAULT 'f',
unit varchar(5),
- project_id int,
deliverydate date,
serialnumber text,
notes text
@@ -1490,7 +1477,6 @@
till varchar(20),
quonumber text,
intnotes text,
- department_id int default 0,
shipvia text,
language_code varchar(6),
ponumber text,
@@ -1574,7 +1560,6 @@
till varchar(20),
quonumber text,
intnotes text,
- department_id int DEFAULT 0,
shipvia text,
language_code varchar(6),
ponumber text,
@@ -1739,7 +1724,6 @@
quotation bool default 'f',
quonumber text,
intnotes text,
- department_id int default 0,
shipvia text,
language_code varchar(6),
ponumber text,
@@ -1765,7 +1749,6 @@
precision int,
discount numeric,
unit varchar(5),
- project_id int,
reqdate date,
ship numeric,
serialnumber text,
@@ -1788,21 +1771,54 @@
When you have to pay someone in a foreign currency, the equivalent amount is the amount
you have to spend to acquire the foreign currency (buy rate).$$;
--
-CREATE TABLE project (
+
+CREATE TABLE business_unit_class (
+ id int not null unique,
+ label text primary key,
+ active bool not null default false,
+ ordering int
+);
+
+COMMENT ON TABLE business_unit_class IS
+$$ Consolidates projects and departments, and allows this to be extended for
+funds accounting and other purposes.$$;
+
+INSERT INTO business_unit_class (id, label, active, ordering)
+VALUES (1, 'Department', '0', '10'),
+ (2, 'Project', '0', '20');
+
+CREATE TABLE business_unit (
id serial PRIMARY KEY,
- projectnumber text,
+ class_id int not null references business_unit_class(id),
+ control_code text,
description text,
- startdate date,
- enddate date,
+ start_date date,
+ end_date date,
+ parent_id int references business_unit(id),
+ credit_id int references entity_credit_account(id),
+ UNIQUE(id, class_id)
+);
+
+CREATE TABLE job (
+ bu_id int primary key references business_unit(id),
parts_id int,
production numeric default 0,
- completed numeric default 0,
- credit_id int references entity_credit_account(id)
+ completed numeric default 0
);
-ALTER TABLE journal_line ADD FOREIGN KEY (project_id) REFERENCES project(id);
+CREATE TABLE business_unit_ac (
+ entry_id int references acc_trans(entry_id),
+ class_id int references business_unit_class(id),
+ bu_id int,
+ primary key(bu_id, class_id, entry_id),
+ foreign key(class_id, bu_id) references business_unit(class_id, id)
+);
-COMMENT ON COLUMN project.parts_id IS
+COMMENT ON TABLE business_unit IS
+$$ Tracks Projects, Departments, Funds, Etc.$$;
+
+
+COMMENT ON COLUMN job.parts_id IS
$$ Job costing/manufacturing here not implemented.$$;
--
CREATE TABLE partsgroup (
@@ -1825,14 +1841,6 @@
$$ Whether AR/AP transactions and invoices have been emailed and/or printed $$;
--
--- department transaction table
-CREATE TABLE dpt_trans (
- trans_id int PRIMARY KEY,
- department_id int
-);
-
-COMMENT ON TABLE dpt_trans IS $$Department to Transaction Map$$;
---
-- business table
CREATE TABLE business (
id serial PRIMARY KEY,
@@ -1947,13 +1955,13 @@
COMMENT ON TABLE parts_translation IS
$$ Translation information for parts.$$;
-CREATE TABLE project_translation
+CREATE TABLE business_unit_translation
(PRIMARY KEY (trans_id, language_code)) INHERITS (translation);
-ALTER TABLE project_translation
-ADD foreign key (trans_id) REFERENCES project(id);
+ALTER TABLE business_unit_translation
+ADD foreign key (trans_id) REFERENCES business_unit(id);
-COMMENT ON TABLE project_translation IS
-$$ Translation information for projects.$$;
+COMMENT ON TABLE business_unit_translation IS
+$$ Translation information for projects, departments, etc.$$;
CREATE TABLE partsgroup_translation
(PRIMARY KEY (trans_id, language_code)) INHERITS (translation);
@@ -2025,7 +2033,7 @@
--
CREATE TABLE jcitems (
id serial PRIMARY KEY,
- project_id int,
+ business_unit_id int references business_unit(id),
parts_id int,
description text,
qty numeric,
@@ -2178,15 +2186,12 @@
create index partstax_parts_id_key on partstax (parts_id);
--
--
-create index project_id_key on project (id);
-create unique index projectnumber_key on project (projectnumber);
--
create index partsgroup_id_key on partsgroup (id);
create unique index partsgroup_key on partsgroup (partsgroup);
--
create index status_trans_id_key on status (trans_id);
--
-create index department_id_key on department (id);
--
create index partsvendor_parts_id_key on partsvendor (parts_id);
--
@@ -2211,32 +2216,6 @@
' language 'plpgsql';
-- end function
--
-CREATE FUNCTION check_department() RETURNS TRIGGER AS '
-
-declare
- dpt_id int;
-
-begin
-
- if new.department_id = 0 then
- delete from dpt_trans where trans_id = new.id;
- return NULL;
- end if;
-
- select into dpt_id trans_id from dpt_trans where trans_id = new.id;
-
- if dpt_id > 0 then
- update dpt_trans set department_id = new.department_id where trans_id = dpt_id;
- else
- insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id);
- end if;
-return NULL;
-
-end;
-' language 'plpgsql';
--- end function
-CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_department();
--- end trigger
--
CREATE FUNCTION del_recurring() RETURNS TRIGGER AS '
BEGIN
@@ -3489,7 +3468,7 @@
purchase_date date not null,
start_depreciation date not null,
location_id int references warehouse(id),
- department_id int references department(id),
+ department_id int references business_unit(id),
invoice_id int references eca_invoice(journal_id),
asset_account_id int references account(id),
dep_account_id int references account(id),
@@ -3554,7 +3533,7 @@
asset_id bigint references asset_item(id),
report_id bigint references asset_report(id),
amount numeric,
- department_id int references department(id),
+ department_id int references business_unit(id),
warehouse_id int references warehouse(id),
PRIMARY KEY(asset_id, report_id)
);
@@ -4442,8 +4421,7 @@
entity_credit_id integer references entity_credit_account(id),
employee_id integer references person(id),
currency char(3),
- notes text,
- department_id integer default 0);
+ notes text);
COMMENT ON TABLE payment IS $$ This table will store the main data on a payment, prepayment, overpayment, et$$;
COMMENT ON COLUMN payment.reference IS $$ This field will store the code for both receipts and payment order $$;
@@ -4472,5 +4450,5 @@
This reasoning is hacky and i hope it can dissapear when we get to 1.4 - D.M.
$$;
-
+
commit;
Added: trunk/sql/modules/Business_Unit.sql
===================================================================
--- trunk/sql/modules/Business_Unit.sql (rev 0)
+++ trunk/sql/modules/Business_Unit.sql 2012-02-14 10:08:58 UTC (rev 4307)
@@ -0,0 +1,145 @@
+BEGIN;
+
+CREATE OR REPLACE FUNCTION business_unit__list_classes(in_active bool)
+RETURNS SETOF business_unit_class AS
+$$
+
+SELECT * FROM business_unit_class
+ WHERE active = $1 OR $1 IS NULL
+ORDER BY ordering;
+
+$$ LANGUAGE SQL;
+
+COMMENT ON FUNCTION business_unit__list_classes(in_active bool) IS
+$$ This function lists all business unit clases. If in_active is true, then
+only active classes are listed. If it is false then only inactive classes are
+listed. If it is null, then all classes are listed.$$;
+
+CREATE OR REPLACE FUNCTION business_unit_get(in_id int) RETURNS business_unit
+AS
+$$ SELECT * FROM business_unit WHERE id = $1; $$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION business_unit__list_by_class
+(in_business_unit_class_id int, in_active_on date, in_credit_id int,
+in_strict_credit bool)
+RETURNS SETOF business_unit AS
+$$
+BEGIN
+RETURN QUERY SELECT * FROM business_unit
+ WHERE (in_active_on BETWEEN start_date AND end_date OR
+ in_active_in IS NULL)
+ AND (in_credit_id = credit_id
+ OR (credit_id IS NULL and in_strict_credit IS NOT TRUE)
+ OR (in_credit_id IS NULL))
+ AND class_id = in_business_unit_class_id
+ ORDER BY control_code;
+END;
+$$ LANGUAGE PLPGSQL;
+
+COMMENT ON FUNCTION business_unit__list_by_class
+(in_business_unit_class_id int, in_active_on date, in_credit_id int,
+in_strict_credit bool) IS
+$$ This function retUrns a list of all units (projects, departments, funds, etc)
+active on the in_active_on date, where in_credit_id matches the credit id of the
+customer or vendor requested, and where in_business_uni_class_id is the class id
+of the class of business units (1 for department, 2 for project, etc).
+
+With the exception of in_business_unit_class_id, the null matches all records.
+$$;
+
+DROP TYPE IF EXISTS business_unit_short;
+
+CREATE TYPE business_unit_short AS (
+id int,
+control_code text,
+description text,
+start_date date,
+end_date date,
+parent_id int,
+path int[],
+level int
+);
+
+CREATE OR REPLACE FUNCTION business_unit__get_tree_for(in_id int)
+RETURNS SETOF business_unit_short AS
+$$
+WITH RECURSIVE tree (id, control_code, description, start_date, end_date,
+ parent_id, path, level)
+AS (
+ SELECT id, control_code, description, start_date, end_date, parent_id,
+ ARRAY[parent_id] AS path, 1 as level
+ FROM business_unit WHERE $1 = id
+ UNION
+ SELECT t.id, t.control_code, t.description, t.start_date, t.end_date,
+ t.parent_id,
+ t.path || bu.id AS path, t.level + 1 as level
+ FROM business_unit bu JOIN tree t ON t.parent_id = bu.id
+)
+SELECT * FROM tree ORDER BY path;
+$$ LANGUAGE SQL;
+
+COMMENT ON FUNCTION business_unit__get_tree_for(in_id int) IS
+$$ This function returns tree-related records with the root of the tree being
+the business unit of in_id. $$;
+
+CREATE OR REPLACE FUNCTION business_unit_class__save
+(in_id int, in_label text, in_active bool, in_ordering int)
+RETURNS business_unit_class AS
+$$
+DECLARE retval business_unit_class;
+BEGIN
+
+UPDATE business_unit_class
+ SET label = in_label,
+ active = in_active,
+ ordering = in_ordering
+ WHERE id = in_id;
+
+IF NOT FOUND THEN
+
+ INSERT INTO business_unit_class (id, label, active, ordering)
+ VALUES (in_id, in_label, in_active, in_ordering);
+
+END IF;
+
+SELECT * INTO retval FROM business_unit_class;
+
+RETURN retval;
+
+END;
+
+$$LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION business_unit__save
+(in_id int, in_class_id int, in_control_code text, in_descriptin text,
+in_star_date date, in_end_date date, in_parent_id int, in_credit_id int)
+RETURNS business_unit AS
+$$
+DECLARE retval business_unit;
+
+BEGIN
+
+UPDATE business_unit
+ SET class_id = in_class_id,
+ control_code = in_control_code,
+ description = in_description,
+ start_date = in_start_date,
+ end_date = in_end_date,
+ parent_id = in_parent_id,
+ credit_id = in_credit_id
+ WHERE id = in_id;
+
+IF NOT FOUND THEN
+ INSERT INTO business_unit
+ (class_id, control_code, description, start_date, end_date, parent_id,
+ credit_id)
+ VALUES (in_class_id, in_control_code, in_description, in_start_date,
+ in_end_date, in_parent_id, in_credit_id);
+END IF;
+
+
+RETURN retval;
+END;
+$$ LANGUAGE PLPGSQL;
+
+COMMIT;
Modified: trunk/sql/modules/LOADORDER
===================================================================
--- trunk/sql/modules/LOADORDER 2012-02-13 13:14:33 UTC (rev 4306)
+++ trunk/sql/modules/LOADORDER 2012-02-14 10:08:58 UTC (rev 4307)
@@ -6,6 +6,7 @@
Account.sql
Session.sql
Business_type.sql
+Business_Unit.sql
Location.sql
Company.sql
Customer.sql
Modified: trunk/sql/modules/Menu.sql
===================================================================
--- trunk/sql/modules/Menu.sql 2012-02-13 13:14:33 UTC (rev 4306)
+++ trunk/sql/modules/Menu.sql 2012-02-14 10:08:58 UTC (rev 4307)
@@ -36,7 +36,7 @@
JOIN menu_attribute ma ON (n.id = ma.node_id)
WHERE n.id IN (select node_id
FROM menu_acl acl
- JOIN pg_roles pr on pr.rolname = acl.role_name
+ LEFT JOIN pg_roles pr on pr.rolname = acl.role_name
WHERE CASE WHEN rolname
ilike 'public'
THEN true
@@ -57,7 +57,7 @@
WHERE cn.id IN
(select node_id
FROM menu_acl acl
- JOIN pg_roles pr
+ LEFT JOIN pg_roles pr
on pr.rolname = acl.role_name
WHERE CASE WHEN rolname
ilike 'public'
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.