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

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