[Noalyss-commit] [noalyss] 02/02: Upgrade database to 123

[Dany De Bontridder]

sparkyx pushed a commit to branch master
in repository noalyss.

commit 007b37b73a6d33095f9826f7e6f9471d4298bf3c
Author: Dany De Bontridder <address@hidden>
Date:   Mon Dec 14 17:12:11 2015 +0100

    Upgrade database to 123
---
 include/sql/patch/upgrade122.sql |  141 ++++++++++++++++++++++++++++++++++++++
 1 files changed, 141 insertions(+), 0 deletions(-)

diff --git a/include/sql/patch/upgrade122.sql b/include/sql/patch/upgrade122.sql
new file mode 100644
index 0000000..dbdca54
--- /dev/null
+++ b/include/sql/patch/upgrade122.sql
@@ -0,0 +1,141 @@
+begin;
+CREATE OR REPLACE FUNCTION comptaproc.insert_quick_code(nf_id integer, 
tav_text text)
+  RETURNS integer AS
+$BODY$
+       declare
+       ns integer;
+       nExist integer;
+       tText text;
+       tBase text;
+       tName text;
+       nCount Integer;
+       nDuplicate Integer;
+       begin
+       tText := lower(trim(tav_text));
+       tText := replace(tText,' ','');
+        tText:= translate(tText,E' $€µ£%+/\\!(){}(),;&|"#''^<>*','');
+       tText := translate(tText,E'éèêëàâäïîüûùöôç','eeeeaaaiiuuuooc');
+       nDuplicate := 0;
+       tBase := tText;
+       loop
+               -- take the next sequence
+               select nextval('s_jnt_fic_att_value') into ns;
+               if length (tText) = 0 or tText is null then
+                       select count(*) into nCount from fiche_detail where 
f_id=nf_id and ad_id=1;
+                       if nCount = 0 then
+                               tText := 'FICHE'||ns::text;
+                       else
+                               select ad_value into tName from fiche_detail 
where f_id=nf_id and ad_id=1;
+                               
+                               tName := lower(trim(tName));
+                               tName := substr(tName,1,6);
+                               tName := replace(tName,' ','');
+                               tName:= translate(tName,E' 
$€µ£%+/\\!(){}(),;&|"#''^<>*','');
+                               tName := 
translate(tName,E'éèêëàâäïîüûùöôç','eeeeaaaiiuuuooc');
+                               tBase := tName;
+                               if nDuplicate = 0 then
+                                       tText := tName;
+                               else
+                                       tText := tName||nDuplicate::text;
+                               end if;
+                       end if;
+               end if;
+               -- av_text already used ?
+               select count(*) into nExist
+                       from fiche_detail
+               where
+                       ad_id=23 and  ad_value=upper(tText);
+
+               if nExist = 0 then
+                       exit;
+               end if;
+               nDuplicate := nDuplicate + 1 ;
+               tText := tBase || nDuplicate::text;
+               
+               if nDuplicate > 9999 then
+                       raise Exception 'too many duplicate % duplicate# 
%',tText,nDuplicate;
+               end if;
+       end loop;
+
+
+       insert into fiche_detail(jft_id,f_id,ad_id,ad_value) values 
(ns,nf_id,23,upper(tText));
+       return ns;
+       end;
+$BODY$
+LANGUAGE plpgsql;
+
+update fiche_detail set ad_value=replace(ad_value,'''','-') where ad_id=23;
+
+CREATE OR REPLACE FUNCTION comptaproc.update_quick_code(njft_id integer, 
tav_text text)
+  RETURNS integer AS
+$BODY$
+       declare
+       ns integer;
+       nExist integer;
+       tText text;
+       tBase text;
+       old_qcode varchar;
+       num_rows_jrnx integer;
+       num_rows_predef integer;
+       begin
+       -- get current value
+       select ad_value into old_qcode from fiche_detail where jft_id=njft_id;
+       -- av_text didn't change so no update
+       if tav_text = upper( trim(old_qcode)) then
+               raise notice 'nothing to change % %' , tav_text,old_qcode;
+               return 0;
+       end if;
+
+       tText := trim(lower(tav_text));
+       tText := replace(tText,' ','');
+        -- valid alpha is [ . : - _ ]
+       tText := translate(tText,E' $€µ£%+/\\!(){}(),;&|"#''^<>*','');
+       tText := translate(tText,E'éèêëàâäïîüûùöôç','eeeeaaaiiuuuooc');
+       tText := upper(tText);
+       if length ( tText) = 0 or tText is null then
+               return 0;
+       end if;
+
+       ns := njft_id;
+       tBase := tText;
+       loop
+               -- av_text already used ?
+               select count(*) into nExist
+                       from fiche_detail
+               where
+                       ad_id=23 and ad_value=tText
+                       and jft_id <> njft_id;
+
+               if nExist = 0 then
+                       exit;
+               end if;
+               if tText = tBase||ns then
+                       -- take the next sequence
+                       select nextval('s_jnt_fic_att_value') into ns;
+               end if;
+               tText  :=tBase||ns;
+
+       end loop;
+       update fiche_detail set ad_value = tText where jft_id=njft_id;
+
+       -- update also the contact
+       update fiche_detail set ad_value = tText
+               where jft_id in
+                       ( select jft_id
+                               from fiche_detail
+                       where ad_id=25 and ad_value=old_qcode);
+
+
+       return ns;
+       end;
+$BODY$
+  LANGUAGE plpgsql;
+
+DROP FUNCTION comptaproc.insert_quant_purchase(text, numeric, character 
varying, numeric, numeric, numeric, integer, numeric, numeric, numeric, 
numeric, character varying, numeric);
+DROP FUNCTION comptaproc.insert_quant_purchase(text, numeric, text, numeric, 
numeric, numeric, integer, numeric, numeric, numeric, numeric, text, numeric);
+DROP FUNCTION comptaproc.insert_quant_sold(text, numeric, character varying, 
numeric, numeric, numeric, integer, character varying, numeric);
+
+
+update version set val=123;
+
+commit;