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

[..hidden..]

Revision: 6293
          http://sourceforge.net/p/ledger-smb/code/6293
Author:   tshvr
Date:     2013-11-18 10:53:00 +0000 (Mon, 18 Nov 2013)
Log Message:
-----------
audittrail person_id should reference person(id)

Modified Paths:
--------------
    trunk/sql/Pg-database.sql
    trunk/sql/modules/Fixes.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2013-11-16 08:27:24 UTC (rev 6292)
+++ trunk/sql/Pg-database.sql	2013-11-18 10:53:00 UTC (rev 6293)
@@ -2189,7 +2189,8 @@
   formname text,
   action text,
   transdate timestamp default current_timestamp,
-  person_id integer references person(entity_id) not null,
+  --person_id integer references person(entity_id) not null,
+  person_id integer references person(id) not null,
   entry_id BIGSERIAL PRIMARY KEY
 );
 
@@ -2412,6 +2413,7 @@
 DECLARE
    t_reference text;
    t_row RECORD;
+   t_user_id int;
 BEGIN
 
 IF TG_OP = 'INSERT' then
@@ -2426,8 +2428,11 @@
     t_reference := t_row.reference;
 END IF;
 
+SELECT id into t_user_id from users where username = SESSION_USER;
+
 INSERT INTO audittrail (trans_id,tablename,reference, action, person_id)
-values (t_row.id,TG_RELNAME,t_reference, TG_OP, person__get_my_entity_id());
+--values (t_row.id,TG_RELNAME,t_reference, TG_OP, person__get_my_entity_id());
+values (t_row.id,TG_RELNAME,t_reference, TG_OP, t_user_id);
 
 return null; -- AFTER TRIGGER ONLY, SAFE
 END;

Modified: trunk/sql/modules/Fixes.sql
===================================================================
--- trunk/sql/modules/Fixes.sql	2013-11-16 08:27:24 UTC (rev 6292)
+++ trunk/sql/modules/Fixes.sql	2013-11-18 10:53:00 UTC (rev 6293)
@@ -253,3 +253,44 @@
 
 COMMIT;
 
+BEGIN;
+ALTER TABLE entity_class DROP COLUMN IF EXISTS country_id;
+COMMIT;
+
+BEGIN;
+update audittrail set person_id=(select id from person where last_name='Admin') where person_id not in (select id from person) ;
+ALTER TABLE audittrail DROP CONSTRAINT IF EXISTS "audittrail_person_id_fkey";
+ALTER TABLE audittrail ADD CONSTRAINT "audittrail_person_id_fkey" FOREIGN KEY(person_id) REFERENCES person(id);
+
+CREATE OR REPLACE FUNCTION gl_audit_trail_append()
+RETURNS TRIGGER AS
+$$
+DECLARE
+   t_reference text;
+   t_row RECORD;
+   t_user_id int;
+BEGIN
+
+IF TG_OP = 'INSERT' then
+   t_row := NEW;
+ELSE
+   t_row := OLD;
+END IF;
+
+IF TG_RELNAME IN ('ar', 'ap') THEN
+    t_reference := t_row.invnumber;
+ELSE 
+    t_reference := t_row.reference;
+END IF;
+
+SELECT id into t_user_id from users where username = SESSION_USER;
+
+INSERT INTO audittrail (trans_id,tablename,reference, action, person_id)
+--values (t_row.id,TG_RELNAME,t_reference, TG_OP, person__get_my_entity_id());
+values (t_row.id,TG_RELNAME,t_reference, TG_OP, t_user_id);
+
+return null; -- AFTER TRIGGER ONLY, SAFE
+END;
+$$ language plpgsql security definer;
+
+COMMIT;

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


------------------------------------------------------------------------------
DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps
OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access
Free app hosting. Or install the open source package on any LAMP server.
Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native!
http://pubads.g.doubleclick.net/gampad/clk?id=63469471&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-commits mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-commits