[Noalyss-commit] [noalyss] 03/04: replace trigger jrnx_ins , when qcode was null , an exception was raised and blocked a simple date change

[Dany De Bontridder]

sparkyx pushed a commit to branch master
in repository noalyss.

commit df6f96cf25838e042ec2d174703baaaf98556bf2
Author: Dany De Bontridder <address@hidden>
Date:   Thu Mar 24 20:14:23 2016 +0100

    replace trigger jrnx_ins , when qcode was null , an exception was raised 
and blocked a simple date change
---
 include/sql/patch/upgrade124.sql |   56 ++++++++++++++++++++++++++++++++++++++
 1 files changed, 56 insertions(+), 0 deletions(-)

diff --git a/include/sql/patch/upgrade124.sql b/include/sql/patch/upgrade124.sql
new file mode 100644
index 0000000..4d603d1
--- /dev/null
+++ b/include/sql/patch/upgrade124.sql
@@ -0,0 +1,56 @@
+begin;
+CREATE OR REPLACE FUNCTION comptaproc.jrnx_ins()
+ RETURNS trigger
+ 
+AS $function$
+declare
+n_fid bigint;
+nCount integer;
+sQcode text;
+
+begin
+n_fid := NULL;
+sQcode := NULL;
+
+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;
+
+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
+        if nCount = 1 then
+                select f_id into n_fid from fiche_detail where ad_id = 5 and 
ad_value=NEW.j_poste;
+            if FOUND then
+                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;
+
+end if;
+
+NEW.j_qcode=trim(upper(coalesce(NEW.j_qcode,'')));
+
+if length (coalesce(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=125;
+
+commit;
\ No newline at end of file