SF.net SVN: ledger-smb: [681] trunk/sql

[..hidden..]

Revision: 681
          http://svn.sourceforge.net/ledger-smb/?rev=681&view=rev
Author:   tetragon
Date:     2006-11-22 06:35:40 -0800 (Wed, 22 Nov 2006)

Log Message:
-----------
Adjusting del_exchangerate to work with current defaults table layout

Modified Paths:
--------------
    trunk/sql/Pg-database.sql
    trunk/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2006-11-22 05:31:48 UTC (rev 680)
+++ trunk/sql/Pg-database.sql	2006-11-22 14:35:40 UTC (rev 681)
@@ -817,7 +817,7 @@
 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 @@
 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 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 @@
 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 @@
 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 @@
 
 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 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 @@
 -- 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 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;

Modified: trunk/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql
===================================================================
--- trunk/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql	2006-11-22 05:31:48 UTC (rev 680)
+++ trunk/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql	2006-11-22 14:35:40 UTC (rev 681)
@@ -287,7 +287,63 @@
 
 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
 'BEGIN


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.