summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authortetragon <tetragon@4979c152-3d1c-0410-bac9-87ea11338e46>2006-11-22 14:35:40 +0000
committertetragon <tetragon@4979c152-3d1c-0410-bac9-87ea11338e46>2006-11-22 14:35:40 +0000
commitbaf9b5c9910ec3c5349a870d13e6f602f9e23d13 (patch)
treec0f86b04c3abd8be99c6d7ff061483c4032466f1
parent1ff34a79a44c4be7c1aec04696cd116eecd45535 (diff)
Adjusting del_exchangerate to work with current defaults table layout
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@681 4979c152-3d1c-0410-bac9-87ea11338e46
-rw-r--r--sql/Pg-database.sql18
-rw-r--r--sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql56
2 files changed, 65 insertions, 9 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index 44ea9a10..afc9852c 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -817,7 +817,7 @@ create unique index language_code_key on language (code);
create index jcitems_id_key on jcitems (id);
--
-CREATE FUNCTION del_yearend() RETURNS OPAQUE AS '
+CREATE FUNCTION del_yearend() RETURNS TRIGGER AS '
begin
delete from yearend where trans_id = old.id;
return NULL;
@@ -828,7 +828,7 @@ end;
CREATE TRIGGER del_yearend AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_yearend();
-- end trigger
--
-CREATE FUNCTION del_department() RETURNS OPAQUE AS '
+CREATE FUNCTION del_department() RETURNS TRIGGER AS '
begin
delete from dpt_trans where trans_id = old.id;
return NULL;
@@ -845,7 +845,7 @@ CREATE TRIGGER del_department AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE
CREATE TRIGGER del_department AFTER DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_department();
-- end trigger
--
-CREATE FUNCTION del_customer() RETURNS OPAQUE AS '
+CREATE FUNCTION del_customer() RETURNS TRIGGER AS '
begin
delete from shipto where trans_id = old.id;
delete from customertax where customer_id = old.id;
@@ -858,7 +858,7 @@ end;
CREATE TRIGGER del_customer AFTER DELETE ON customer FOR EACH ROW EXECUTE PROCEDURE del_customer();
-- end trigger
--
-CREATE FUNCTION del_vendor() RETURNS OPAQUE AS '
+CREATE FUNCTION del_vendor() RETURNS TRIGGER AS '
begin
delete from shipto where trans_id = old.id;
delete from vendortax where vendor_id = old.id;
@@ -871,7 +871,7 @@ end;
CREATE TRIGGER del_vendor AFTER DELETE ON vendor FOR EACH ROW EXECUTE PROCEDURE del_vendor();
-- end trigger
--
-CREATE FUNCTION del_exchangerate() RETURNS OPAQUE AS '
+CREATE FUNCTION del_exchangerate() RETURNS TRIGGER AS '
declare
t_transdate date;
@@ -881,7 +881,7 @@ declare
begin
- select into d_curr substr(curr,1,3) from defaults;
+ select into d_curr substr(value,1,3) from defaults where setting_key = ''curr'';
if TG_RELNAME = ''ar'' then
select into t_curr, t_transdate curr, transdate from ar where id = old.id;
@@ -938,7 +938,7 @@ CREATE TRIGGER del_exchangerate BEFORE DELETE ON ap FOR EACH ROW EXECUTE PROCEDU
CREATE TRIGGER del_exchangerate BEFORE DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
-- end trigger
--
-CREATE FUNCTION check_inventory() RETURNS OPAQUE AS '
+CREATE FUNCTION check_inventory() RETURNS TRIGGER AS '
declare
itemid int;
@@ -964,7 +964,7 @@ CREATE TRIGGER check_inventory AFTER UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE
-- end trigger
--
--
-CREATE FUNCTION check_department() RETURNS OPAQUE AS '
+CREATE FUNCTION check_department() RETURNS TRIGGER AS '
declare
dpt_id int;
@@ -998,7 +998,7 @@ CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON gl FOR EACH ROW EXECUT
CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_department();
-- end trigger
--
-CREATE FUNCTION del_recurring() returns opaque as '
+CREATE FUNCTION del_recurring() RETURNS TRIGGER AS '
BEGIN
DELETE FROM recurring WHERE id = old.id;
DELETE FROM recurringemail WHERE id = old.id;
diff --git a/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql b/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql
index 44d015a8..a7f4a3f1 100644
--- a/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql
+++ b/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql
@@ -287,6 +287,62 @@ SELECT 'appname', 'LedgerSMB'::text;
DROP TABLE old_defaults;
+CREATE OR REPLACE FUNCTION del_exchangerate() RETURNS TRIGGER AS '
+
+declare
+ t_transdate date;
+ t_curr char(3);
+ t_id int;
+ d_curr text;
+
+begin
+
+ select into d_curr substr(value,1,3) from defaults where setting_key = ''curr'';
+
+ if TG_RELNAME = ''ar'' then
+ select into t_curr, t_transdate curr, transdate from ar where id = old.id;
+ end if;
+ if TG_RELNAME = ''ap'' then
+ select into t_curr, t_transdate curr, transdate from ap where id = old.id;
+ end if;
+ if TG_RELNAME = ''oe'' then
+ select into t_curr, t_transdate curr, transdate from oe where id = old.id;
+ end if;
+
+ if d_curr != t_curr then
+
+ select into t_id a.id from acc_trans ac
+ join ar a on (a.id = ac.trans_id)
+ where a.curr = t_curr
+ and ac.transdate = t_transdate
+
+ except select a.id from ar a where a.id = old.id
+
+ union
+
+ select a.id from acc_trans ac
+ join ap a on (a.id = ac.trans_id)
+ where a.curr = t_curr
+ and ac.transdate = t_transdate
+
+ except select a.id from ap a where a.id = old.id
+
+ union
+
+ select o.id from oe o
+ where o.curr = t_curr
+ and o.transdate = t_transdate
+
+ except select o.id from oe o where o.id = old.id;
+
+ if not found then
+ delete from exchangerate where curr = t_curr and transdate = t_transdate;
+ end if;
+ end if;
+return old;
+
+end;
+' language 'plpgsql';
CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR)
RETURNS BOOL AS