[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[2639] trunk
- Subject: SF.net SVN: ledger-smb:[2639] trunk
- From: ..hidden..
- Date: Thu, 11 Jun 2009 23:19:31 +0000
Revision: 2639
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2639&view=rev
Author: einhverfr
Date: 2009-06-11 23:19:31 +0000 (Thu, 11 Jun 2009)
Log Message:
-----------
Moving account management to new structure. Not backward compatible, requires data migration.
Modified Paths:
--------------
trunk/UI/ajax_li.html
trunk/bin/am.pl
trunk/sql/Pg-database.sql
trunk/sql/modules/Account.sql
trunk/sql/modules/chart.sql
Added Paths:
-----------
trunk/account.pl
Modified: trunk/UI/ajax_li.html
===================================================================
--- trunk/UI/ajax_li.html 2009-06-10 17:00:53 UTC (rev 2638)
+++ trunk/UI/ajax_li.html 2009-06-11 23:19:31 UTC (rev 2639)
@@ -2,8 +2,3 @@
<?lsmb FOREACH result IN results ?><li id="<?lsmb result.key ?>"><?lsmb result.value ?></li>
<?lsmb END ?></ul>
<ul>
-<?lsmb FOREACH result IN results ?><li id="<?lsmb result.key ?>"><?lsmb result.value ?></li>
-<?lsmb END ?></ul>
-<ul>
-<?lsmb FOREACH result IN results ?><li id="<?lsmb result.key ?>"><?lsmb result.value ?></li>
-<?lsmb END ?></ul>
Copied: trunk/account.pl (from rev 2638, trunk/journal.pl)
===================================================================
--- trunk/account.pl (rev 0)
+++ trunk/account.pl 2009-06-11 23:19:31 UTC (rev 2639)
@@ -0,0 +1,9 @@
+#!/usr/bin/perl
+
+require "lsmb-request.pl";
+#!/usr/bin/perl
+
+require "lsmb-request.pl";
+#!/usr/bin/perl
+
+require "lsmb-request.pl";
Modified: trunk/bin/am.pl
===================================================================
--- trunk/bin/am.pl 2009-06-10 17:00:53 UTC (rev 2638)
+++ trunk/bin/am.pl 2009-06-11 23:19:31 UTC (rev 2639)
@@ -173,11 +173,6 @@
};
}
-##SC: Temporary removal
-## if ( $form->{lynx} ) {
-## require "bin/menu.pl";
-## &menubar;
-## }
}
sub save_account {
@@ -272,7 +267,7 @@
$column_data{class} = 'heading';
$column_data{accno} = {
text => $ca->{accno},
- href => "$form->{script}?action=edit_account&id=$ca->{id}&path=$form->{path}&login=$form->{login}&sessionid=$form->{sessionid}&callback=$callback"};
+ href => "account.pl?action=edit&id=$ca->{id}&path=$form->{path}&login=$form->{login}&sessionid=$form->{sessionid}&charttype=H&callback=$callback"};
$column_data{gifi_accno} = {
text => $ca->{gifi_accno},
href => "$form->{script}?action=edit_gifi&accno=$gifi_accno&path=$form->{path}&login=$form->{login}&sessionid=$form->{sessionid}&callback=$callback"};
@@ -288,7 +283,7 @@
$column_data{i} = $i;
$column_data{accno} = {
text => $ca->{accno},
- href => "$form->{script}?action=edit_account&id=$ca->{id}&path=$form->{path}&login=$form->{login}&sessionid=$form->{sessionid}&callback=$callback"};
+ href => "account.pl?action=edit&charttype=A&id=$ca->{id}&path=$form->{path}&login=$form->{login}&sessionid=$form->{sessionid}&callback=$callback"};
$column_data{gifi_accno} = {
text => $ca->{gifi_accno},
href => "$form->{script}?action=edit_gifi&accno=$gifi_accno&path=$form->{path}&login=$form->{login}&sessionid=$form->{sessionid}&callback=$callback"};
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2009-06-10 17:00:53 UTC (rev 2638)
+++ trunk/sql/Pg-database.sql 2009-06-11 23:19:31 UTC (rev 2639)
@@ -2,16 +2,44 @@
CREATE SEQUENCE id;
-- As of 1.3 there is no central db anymore. --CT
-CREATE TABLE chart (
- id serial PRIMARY KEY,
- accno text NOT NULL,
+CREATE OR REPLACE FUNCTION concat_colon(TEXT, TEXT) returns TEXT as
+$$
+select CASE WHEN $1 IS NULL THEN $2 ELSE $1 || ':' || $2 END;
+$$ language sql;
+
+CREATE AGGREGATE concat_colon (
+ BASETYPE = text,
+ STYPE = text,
+ SFUNC = concat_colon
+);
+
+CREATE TABLE account_heading (
+ id serial not null unique,
+ accno text primary key,
+ parent_id int references account_heading(id),
+ description text
+);
+
+CREATE TABLE account (
+ id serial not null unique,
+ accno text primary key,
description text,
- charttype char(1) DEFAULT 'A',
- category char(1),
- link text,
+ category CHAR(1) NOT NULL,
gifi_accno text,
- contra bool DEFAULT 'f'
+ heading int not null references account_heading(id),
+ contra bool not null default false
);
+
+CREATE TABLE account_link (
+ account_id int references account(id),
+ description text,
+ primary key (account_id, description)
+);
+
+CREATE VIEW chart AS
+SELECT id, accno, description, 'H' as charttype, NULL as category, NULL as link, NULL as account_heading, null as gifi_accno, false as contra from account_heading UNION
+select c.id, c.accno, c.description, 'A' as charttype, c.category, concat_colon(l.description) as link, heading, gifi_accno, contra from account c left join account_link l ON (c.id = l.account_id) group by c.id, c.accno, c.description, c.category, c.heading, c.gifi_accno, c.contra;
+
--
-- pricegroup added here due to references
CREATE TABLE pricegroup (
@@ -109,6 +137,32 @@
declare
locked int;
begin
+
+CREATE TABLE open_forms (
+id SERIAL PRIMARY KEY,
+session_id int REFERENCES session(session_id) ON DELETE CASCADE
+);
+
+--
+CREATE TABLE transactions (
+ id int PRIMARY KEY,
+ table_name text,
+ locked_by int references "session" (session_id) ON DELETE SET NULL,
+ approved_by int references entity (id),
+ approved_at timestamp
+);
+
+COMMENT on TABLE transactions IS
+$$ This table tracks basic transactions across AR, AP, and GL related tables.
+It provies a referential integrity enforcement mechanism for the financial data
+and also some common features such as discretionary (and pessimistic) locking
+for long batch workflows. $$;
+
+CREATE OR REPLACE FUNCTION lock_record (int, int) returns bool as
+$$
+declare
+ locked int;
+begin
SELECT locked_by into locked from transactions where id = $1;
IF NOT FOUND THEN
RETURN FALSE;
@@ -319,7 +373,7 @@
discount numeric,
description text,
discount_terms int default 0,
- discount_account_id int references chart(id),
+ discount_account_id int references account(id),
taxincluded bool default 'f',
creditlimit NUMERIC default 0,
terms int2 default 0,
@@ -335,8 +389,8 @@
threshold numeric default 0,
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),
+ ar_ap_account_id int references account(id),
+ cash_account_id int references account(id),
bank_account int references entity_bank_account(id),
PRIMARY KEY(entity_id, meta_number, entity_class)
);
@@ -519,7 +573,7 @@
CREATE TABLE acc_trans (
trans_id int NOT NULL REFERENCES transactions(id),
- chart_id int NOT NULL REFERENCES chart (id),
+ chart_id int NOT NULL REFERENCES account(id),
amount NUMERIC,
transdate date DEFAULT current_date,
source text,
@@ -815,7 +869,7 @@
taxcategory_id int,
PRIMARY KEY (parts_id, chart_id),
FOREIGN KEY (parts_id) REFERENCES parts (id) on delete cascade,
- FOREIGN KEY (chart_id) REFERENCES chart (id),
+ FOREIGN KEY (chart_id) REFERENCES account(id),
FOREIGN KEY (taxcategory_id) REFERENCES taxcategory (taxcategory_id)
);
--
@@ -826,20 +880,20 @@
validto timestamp default 'infinity',
pass integer DEFAULT 0 NOT NULL,
taxmodule_id int DEFAULT 1 NOT NULL,
- FOREIGN KEY (chart_id) REFERENCES chart (id),
+ FOREIGN KEY (chart_id) REFERENCES account(id),
FOREIGN KEY (taxmodule_id) REFERENCES taxmodule (taxmodule_id),
PRIMARY KEY (chart_id, validto)
);
--
CREATE TABLE customertax (
customer_id int references entity_credit_account(id) on delete cascade,
- chart_id int,
+ chart_id int REFERENCES account(id),
PRIMARY KEY (customer_id, chart_id)
);
--
CREATE TABLE vendortax (
vendor_id int references entity_credit_account(id) on delete cascade,
- chart_id int,
+ chart_id int REFERENCES account(id),
PRIMARY KEY (vendor_id, chart_id)
);
--
@@ -2381,11 +2435,11 @@
135 action backup 352
134 media file 353
135 media email 354
-137 module am.pl 355
+137 module account.pl 355
138 module am.pl 356
139 module am.pl 357
140 module am.pl 358
-137 action add_account 359
+137 action new 359
138 action list_account 360
139 action add_gifi 361
140 action list_gifi 362
@@ -2583,32 +2637,6 @@
204 action create_batch 572
201 batch_type payment 564
\.
-
---
--- Name: menu_attribute_id_key; Type: CONSTRAINT; Schema: public; Owner: ledgersmb; Tablespace:
---
-
-ALTER TABLE ONLY menu_attribute
- ADD CONSTRAINT menu_attribute_id_key UNIQUE (id);
-
-
---
--- Name: menu_attribute_pkey; Type: CONSTRAINT; Schema: public; Owner: ledgersmb; Tablespace:
---
-
-ALTER TABLE ONLY menu_attribute
- ADD CONSTRAINT menu_attribute_pkey PRIMARY KEY (node_id, attribute);
-
-
---
--- Name: menu_attribute_node_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ledgersmb
---
-
-ALTER TABLE ONLY menu_attribute
- ADD CONSTRAINT menu_attribute_node_id_fkey FOREIGN KEY (node_id) REFERENCES menu_node(id);
-
-
---
-- PostgreSQL database dump complete
--
@@ -2743,6 +2771,7 @@
--
-- PostgreSQL database dump complete
--
+
CREATE AGGREGATE as_array (
BASETYPE = ANYELEMENT,
STYPE = ANYARRAY,
Modified: trunk/sql/modules/Account.sql
===================================================================
--- trunk/sql/modules/Account.sql 2009-06-10 17:00:53 UTC (rev 2638)
+++ trunk/sql/modules/Account.sql 2009-06-11 23:19:31 UTC (rev 2639)
@@ -22,3 +22,86 @@
END;
$$ LANGUAGE plpgsql;
+CREATE OR REPLACE FUNCTION account_save
+(in_id int, in_accno text, in_description text, in_category char(1),
+in_gifi text, in_heading int, in_contra bool, in_link text[])
+RETURNS int AS $$
+DECLARE
+ t_summary_links TEXT[] = '{AR,AP,IC}';
+ t_heading_id int;
+ t_text record;
+ t_id int;
+BEGIN
+ -- check to ensure summary accounts are exclusive
+ FOR t_text IN
+ select t_summary_links[generate_series] AS val
+ FROM generate_series(array_lower(t_summary_links, 1),
+ array_upper(t_summary_links, 1))
+ LOOP
+ IF t_text.val = ANY (in_link) and array_upper(in_link, 1) > 1 THEN
+ RAISE EXCEPTION 'Invalid link settings: Summary';
+ END IF;
+ END LOOP;
+ -- heading settings
+ IF in_heading IS NULL THEN
+ SELECT id INTO t_heading_id FROM account_heading
+ WHERE accno < in_accno order by accno desc limit 1;
+ ELSE
+ t_heading_id := in_heading;
+ END IF;
+
+ DELETE FROM account_link WHERE account_id = in_id;
+
+ UPDATE account
+ SET accno = in_accno,
+ description = in_description,
+ category = in_category,
+ gifi_accno = in_gifi,
+ heading = t_heading_id,
+ contra = in_contra
+ WHERE id = in_id;
+
+ IF FOUND THEN
+ t_id := in_id;
+ ELSE
+ INSERT INTO account (accno, description, category, gifi_accno,
+ heading, contra)
+ VALUES (in_accno, in_description, in_category, in_gifi,
+ t_heading_id, in_contra);
+
+ t_id := currval('account_id_seq');
+ END IF;
+
+ FOR t_text IN
+ select in_link[generate_series] AS val
+ FROM generate_series(array_lower(in_link, 1),
+ array_upper(in_link, 1))
+ LOOP
+ INSERT INTO account_link (account_id, description)
+ VALUES (t_id, t_text.val);
+ END LOOP;
+
+ RETURN t_id;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION account_heading_save
+(in_id int, in_accno text, in_description text, in_parent int)
+RETURNS int AS
+$$
+BEGIN
+ UPDATE account_heading
+ SET accno = in_accno,
+ description = in_description,
+ parent_id = in_parent
+ WHERE id = in_id;
+
+ IF FOUND THEN
+ RETURN in_id;
+ END IF;
+ INSERT INTO account_heading (accno, description, parent_id)
+ VALUES (in_accno, in_description, in_parent);
+
+ RETURN currval('account_heading_id_seq');
+END;
+$$ LANGUAGE PLPGSQL;
Modified: trunk/sql/modules/chart.sql
===================================================================
--- trunk/sql/modules/chart.sql 2009-06-10 17:00:53 UTC (rev 2638)
+++ trunk/sql/modules/chart.sql 2009-06-11 23:19:31 UTC (rev 2639)
@@ -1,5 +1,74 @@
-- TODO: Merge with account.sql -CT
+CREATE TYPE trial_balance_line AS (
+ chart_id int,
+ accno text,
+ description text,
+ beginning_balance numeric,
+ credits numeric,
+ debits numeric,
+ ending_balance numeric
+);
+
+CREATE OR REPLACE FUNCTION report_trial_balance
+(in_datefrom date, in_dateto date, in_department_id int, in_project_id int,
+in_gifi bool)
+RETURNS setof trial_balance_line
+AS $$
+DECLARE out_row trial_balance_line;
+BEGIN
+ IF in_department_id IS NULL THEN
+ FOR out_row IN
+ SELECT c.id, c.accno, c.description,
+ SUM(CASE WHEN ac.transdate < in_datefrom
+ AND c.category IN ('I', 'L', 'Q')
+ THEN ac.amount
+ ELSE ac.amount * -1
+ END),
+ SUM(CASE WHEN ac.transdate >= in_date_from
+ AND ac.amount > 0
+ THEN ac.amount
+ ELSE 0 END),
+ SUM(CASE WHEN ac.transdate >= in_date_from
+ AND ac.amount < 0
+ THEN ac.amount
+ ELSE 0 END) * -1,
+ SUM(CASE WHEN ac.transdate >= in_date_from
+ AND c.charttype IN ('I')
+ THEN ac.amount
+ WHEN ac.transdate >= in_date_from
+ AND c.category IN ('I', 'L', 'Q')
+ THEN ac.amount
+ ELSE ac.amount * -1
+ END)
+ FROM acc_trans ac
+ JOIN (select id, approved FROM ap
+ UNION ALL
+ select id, approved FROM gl
+ UNION ALL
+ select id, approved FROM ar) g
+ ON (g.id = ac.trans_id)
+ JOIN chart c ON (c.id = ac.chart_id)
+ WHERE ac.transdate <= in_date_to
+ AND ac.approved AND g.approved
+ AND (in_project_id IS NULL
+ OR in_project_id = ac.project_id)
+ GROUP BY c.id, c.accno, c.description
+ ORDER BY c.accno
+
+ LOOP
+ RETURN NEXT out_row;
+ END LOOP;
+ ELSE
+ FOR out_row IN
+ SELECT ....
+ LOOP
+ RETURN NEXT out_row;
+ END LOOP;
+ END IF;
+END;
+$$ language plpgsql;
+
CREATE OR REPLACE FUNCTION chart_list_all()
RETURNS SETOF chart AS
$$
@@ -35,19 +104,6 @@
END;
$$ language plpgsql;
-CREATE OR REPLACE FUNCTION chart_list_search(search text)
-RETURNS SETOF chart AS
-$$
-DECLARE out_row chart%ROWTYPE;
-BEGIN
- FOR out_row IN
- SELECT * FROM chart WHERE accno ~* ('^'||search) OR description ~* ('^'||search) ORDER BY accno
- LOOP
- RETURN next out_row;
- END LOOP;
-END;$$
-LANGUAGE 'plpgsql';
-
COMMENT ON FUNCTION chart_list_cash(in_account_class int) IS
$$ This function returns the cash account acording with in_account_class which must be 1 or 2 $$;
@@ -95,56 +151,5 @@
END;
$$ language plpgsql;
-CREATE OR REPLACE FUNCTION chart_list_search(search text)
-RETURNS SETOF chart AS
-$$
-DECLARE out_row chart%ROWTYPE;
-BEGIN
- FOR out_row IN
- SELECT * FROM chart WHERE charttype = 'A' AND (accno ~* ('^'||search) OR description ~* ('^'||search)) ORDER BY accno
- LOOP
- RETURN next out_row;
- END LOOP;
-END;$$
-LANGUAGE 'plpgsql';
-
-CREATE OR REPLACE FUNCTION chart_list_search(search text)
-RETURNS SETOF chart AS
-$$
-DECLARE out_row chart%ROWTYPE;
-BEGIN
- FOR out_row IN
- SELECT * FROM chart WHERE charttype = 'A' AND (accno ~* ('^'||search) OR description ~* ('^'||search)) ORDER BY accno
- LOOP
- RETURN next out_row;
- END LOOP;
-END;$$
-LANGUAGE 'plpgsql';
-
COMMENT ON FUNCTION chart_list_cash(in_account_class int) IS
$$ This function returns the overpayment accounts acording with in_account_class which must be 1 or 2 $$;
-
-CREATE OR REPLACE FUNCTION chart_list_discount(in_account_class int)
-RETURNS SETOF chart AS
-$$
-DECLARE resultrow record;
- link_string text;
-BEGIN
- IF in_account_class = 1 THEN
- link_string := '%AP_discount%';
- ELSE
- link_string := '%AR_discount%';
- END IF;
-
- FOR resultrow IN
- SELECT * FROM chart
- WHERE link LIKE link_string
- ORDER BY accno
- LOOP
- return next resultrow;
- END LOOP;
-END;
-$$ language plpgsql;
-
-COMMENT ON FUNCTION chart_list_discount(in_account_class int) IS
-$$ This function returns the discount accounts acording with in_account_class which must be 1 or 2 $$;
\ No newline at end of file
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.