[Noalyss-commit] [noalyss] 09/20: Protect Qcode in fiche_detail

[dwm]

sparkyx pushed a commit to branch master
in repository noalyss.

commit 7e6a9954b2e92d3f3e609118589b5036c68fbeb0
Author: sparkyx <danydb@noalyss.eu>
AuthorDate: Tue Dec 5 12:14:52 2023 +0100

    Protect Qcode in fiche_detail
---
 sql/upgrade.sql | 40 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 40 insertions(+)

diff --git a/sql/upgrade.sql b/sql/upgrade.sql
index e69de29bb..6f660fa2c 100644
--- a/sql/upgrade.sql
+++ b/sql/upgrade.sql
@@ -0,0 +1,40 @@
+
+
+-- protect against wrong card in fiche_detail
+
+CREATE OR REPLACE FUNCTION comptaproc.fiche_detail_check_qcode()
+ RETURNS trigger
+ LANGUAGE plpgsql
+AS $function$
+declare
+       i record;
+begin
+       if NEW.ad_id=23 and NEW.ad_value != OLD.ad_value then
+               RAISE NOTICE 'new qcode [%] old qcode 
[%]',NEW.ad_value,OLD.ad_value;
+               update jrnx set j_qcode=NEW.ad_value where j_qcode = 
OLD.ad_value;
+               update op_predef_detail set opd_poste=NEW.ad_value where 
opd_poste=OLD.ad_value;
+               raise notice 'TRG fiche_detail update op_predef_detail set 
opd_poste=% where opd_poste=%;',NEW.ad_value,OLD.ad_value;
+               for i in select ad_id from attr_def where ad_type = 'card' or 
ad_id=25 loop
+                       update fiche_detail set ad_value=NEW.ad_value where 
ad_value=OLD.ad_value and ad_id=i.ad_id;
+                       RAISE NOTICE 'change for ad_id [%] ',i.ad_id;
+                       if i.ad_id=19 then
+                               RAISE NOTICE 'Change in stock_goods OLD[%] by 
NEW[%]',OLD.ad_value,NEW.ad_value;
+                               update stock_goods set sg_code=NEW.ad_value 
where sg_code=OLD.ad_value;
+                       end if;
+
+               end loop;
+       end if;
+return NEW;
+end;
+$function$
+;
+
+drop trigger if exists fiche_detail_check_qcode_trg on public.fiche_detail ;
+
+create trigger fiche_detail_check_qcode_trg before insert
+or update on
+public.fiche_detail for each row execute function 
comptaproc.fiche_detail_check_qcode();
+
+
+
+update fiche_detail set ad_value=ad_value where ad_id in (select ad_id from 
attr_def where ad_type='card');