[Noalyss-commit] [noalyss] 03/05: Add trigger to replace an accounting by the corresponding card if no card has been given (example VAT)

[Dany De Bontridder]

sparkyx pushed a commit to branch master
in repository noalyss.

commit 43d7fe92fc8efd88dae8739849b36e0ba11a5c2e
Author: Dany De Bontridder <address@hidden>
Date:   Sat Mar 19 23:20:46 2016 +0100

    Add trigger to replace an accounting by the
    corresponding card if no card has been given
    (example VAT)
---
 include/constant.php             |    2 +-
 include/sql/patch/upgrade123.sql |   54 ++++++++++++++++++++++++++++++++++++++
 2 files changed, 55 insertions(+), 1 deletions(-)

diff --git a/include/constant.php b/include/constant.php
index 75bdf67..27f3fed 100644
--- a/include/constant.php
+++ b/include/constant.php
@@ -104,7 +104,7 @@ if ( !defined("SITE_UPDATE_PLUGIN"))
 if ( ! defined ("SYSINFO_DISPLAY")) {
     define ("SYSINFO_DISPLAY",TRUE);
 }
-define ("DBVERSION",123);
+define ("DBVERSION",124);
 define ("MONO_DATABASE",25);
 define ("DBVERSIONREPO",16);
 define ('NOTFOUND','--not found--');
diff --git a/include/sql/patch/upgrade123.sql b/include/sql/patch/upgrade123.sql
new file mode 100644
index 0000000..49de684
--- /dev/null
+++ b/include/sql/patch/upgrade123.sql
@@ -0,0 +1,54 @@
+begin;
+CREATE OR REPLACE FUNCTION comptaproc.jrnx_ins()
+ RETURNS trigger
+ 
+AS $function$
+declare
+n_fid bigint;
+nCount integer;
+sQcode text;
+
+begin
+raise info 'comptaproc.jrnx_ins()';
+
+NEW.j_tech_per := comptaproc.find_periode(to_char(NEW.j_date,'DD.MM.YYYY'));
+if NEW.j_tech_per = -1 then
+        raise exception 'Période invalide';
+end if;
+
+raise info 'comptaproc.jrnx_ins : j_qcode is %',NEW.j_qcode;
+if trim(coalesce(NEW.j_qcode,'')) = '' then
+        -- how many card has this accounting
+        select count(*) into nCount from fiche_detail where ad_id=5 and 
ad_value=NEW.j_poste;
+        -- only one card is found , then we change the j_qcode by the card
+raise info 'comptaproc.jrnx_ins : found matching card with accounting 
%',nCount;
+        if nCount = 1 then
+                select f_id into n_fid from fiche_detail where ad_id = 5 and 
ad_value=NEW.j_poste;
+                select ad_value into sQcode  from fiche_detail where 
f_id=n_fid and ad_id = 23;
+                NEW.f_id := n_fid;
+                NEW.j_qcode = sQcode;
+                raise info 'comptaproc.jrnx_ins : found card % qcode 
%',n_fid,sQcode;
+        end if;
+
+end if;
+
+NEW.j_qcode=trim(upper(NEW.j_qcode));
+
+if length (NEW.j_qcode) = 0 then
+    NEW.j_qcode=NULL;
+    else
+   select f_id into n_fid from fiche_detail  where ad_id=23 and 
ad_value=NEW.j_qcode;
+if NOT FOUND then
+raise exception 'La fiche dont le quick code est % n''existe pas',NEW.j_qcode;
+end if;
+end if;
+NEW.f_id:=n_fid;
+return NEW;
+end;
+$function$
+LANGUAGE plpgsql;
+
+
+update version set val=124;
+
+commit;
\ No newline at end of file