[Phpcompta-dev] r5173 - in phpcompta/tags/rel650: contrib/document_test html/admin/sql/patch include sql

[phpcompta-dev]

Author: danydb
Date: 2013-01-23 22:38:28 +0100 (Wed, 23 Jan 2013)
New Revision: 5173

Added:
   phpcompta/tags/rel650/html/admin/sql/patch/upgrade104.sql
Modified:
   phpcompta/tags/rel650/contrib/document_test/
   phpcompta/tags/rel650/include/constant.php
   phpcompta/tags/rel650/sql/upgrade.sql
Log:

#780 & #776 forbids some character in the quickcode


Property changes on: phpcompta/tags/rel650/contrib/document_test
___________________________________________________________________
Name: svn:ignore
   + .~lock.all_tags.odt#


Added: phpcompta/tags/rel650/html/admin/sql/patch/upgrade104.sql
===================================================================
--- phpcompta/tags/rel650/html/admin/sql/patch/upgrade104.sql                   
        (rev 0)
+++ phpcompta/tags/rel650/html/admin/sql/patch/upgrade104.sql   2013-01-23 
21:38:28 UTC (rev 5173)
@@ -0,0 +1,105 @@
+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;
+       begin
+       tText := lower(trim(tav_text));
+       tText := replace(tText,' ','');
+       tText := translate(tText,E' $€µ£%.+-/\\!(){}(),;_&|"#''^<>*','');
+       tText := translate(tText,E'éèêëàâäïîüûùöôç','eeeeaaaiiuuuooc');
+
+
+       loop
+               -- take the next sequence
+               select nextval('s_jnt_fic_att_value') into ns;
+               if length (tText) = 0 or tText is null then
+                       tText := 'FID'||ns;
+               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;
+               tText:='FID'||ns;
+       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;
+
+
+
+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;
+       old_qcode varchar;
+       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
+               return 0;
+       end if;
+
+       tText := trim(lower(tav_text));
+       tText := replace(tText,' ','');
+       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;
+
+       loop
+               -- av_text already used ?
+               select count(*) into nExist
+                       from fiche_detail
+               where
+                       ad_id=23 and ad_value=tText;
+
+               if nExist = 0 then
+                       exit;
+               end if;
+               if tText = 'FID'||ns then
+                       -- take the next sequence
+                       select nextval('s_jnt_fic_att_value') into ns;
+               end if;
+               tText  :='FID'||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);
+
+
+       update jrnx set j_qcode=tText where j_qcode = old_qcode;
+       return ns;
+       end;
+$BODY$
+LANGUAGE plpgsql;
+
+update version set val=105;
+
+commit;
\ No newline at end of file

Modified: phpcompta/tags/rel650/include/constant.php
===================================================================
--- phpcompta/tags/rel650/include/constant.php  2013-01-20 14:57:40 UTC (rev 
5172)
+++ phpcompta/tags/rel650/include/constant.php  2013-01-23 21:38:28 UTC (rev 
5173)
@@ -47,7 +47,7 @@
 define 
("SITE_UPDATE_PLUGIN",'http://www.phpcompta.eu/plugin_last_version.txt');
 
 
-define ("DBVERSION",104);
+define ("DBVERSION",105);
 
 define ("DBVERSIONREPO",14);
 define ('NOTFOUND','--not found--');

Modified: phpcompta/tags/rel650/sql/upgrade.sql
===================================================================
--- phpcompta/tags/rel650/sql/upgrade.sql       2013-01-20 14:57:40 UTC (rev 
5172)
+++ phpcompta/tags/rel650/sql/upgrade.sql       2013-01-23 21:38:28 UTC (rev 
5173)
@@ -1,33 +1,99 @@
-CREATE OR REPLACE FUNCTION comptaproc.format_account(p_account account_type)
-  RETURNS account_type AS
+
+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;
+       begin
+       tText := lower(trim(tav_text));
+       tText := replace(tText,' ','');
+       tText := translate(tText,E' $€µ£%.+-/\\!(){}(),;_&|"#''^<>*','');
+       tText := translate(tText,E'éèêëàâäïîüûùöôç','eeeeaaaiiuuuooc');
 
-declare
 
-sResult account_type;
+       loop
+               -- take the next sequence
+               select nextval('s_jnt_fic_att_value') into ns;
+               if length (tText) = 0 or tText is null then
+                       tText := 'FID'||ns;
+               end if;
+               -- av_text already used ?
+               select count(*) into nExist
+                       from fiche_detail
+               where
+                       ad_id=23 and  ad_value=upper(tText);
 
-begin
-sResult := lower(p_account);
+               if nExist = 0 then
+                       exit;
+               end if;
+               tText:='FID'||ns;
+       end loop;
 
-sResult := translate(sResult,E'éèêëàâäïîüûùöôç','eeeeaaaiiuuuooc');
-sResult := translate(sResult,E' $€µ£%.+-/\\!(){}(),;_&|"#''^<>*','');
 
-return upper(sResult);
+       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;
 
-end;
+CREATE OR REPLACE FUNCTION comptaproc.update_quick_code(njft_id integer, 
tav_text text)
+  RETURNS integer AS
 $BODY$
-  LANGUAGE plpgsql ;
+       declare
+       ns integer;
+       nExist integer;
+       tText text;
+       old_qcode varchar;
+       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
+               return 0;
+       end if;
 
-COMMENT ON FUNCTION comptaproc.format_account(account_type) IS 'format the 
accounting :
-- upper case
-- remove space and special char.
-';
+       tText := trim(lower(tav_text));
+       tText := replace(tText,' ','');
+       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;
 
-update tmp_pcmn  set pcm_val_parent  = '62' where pcm_val='6202';
+       ns := njft_id;
 
-update fiche_detail set ad_value = (to_number(ad_value,'9.99')*100)::text 
where ad_id in  (21,22,20,31) and ad_value is not null and ad_value <> '';
+       loop
+               -- av_text already used ?
+               select count(*) into nExist
+                       from fiche_detail
+               where
+                       ad_id=23 and ad_value=tText;
 
-update menu_ref set me_code='ACHISTO' WHERE me_code='ACHIMP';
-update menu_ref set me_code='VEHISTO' WHERE me_code='VENIMP';
-update menu_ref set me_code='FIHISTO' WHERE me_code='FIMP';
-update menu_ref set me_code='ODHISTO' WHERE me_code='ODSIMP';
+               if nExist = 0 then
+                       exit;
+               end if;
+               if tText = 'FID'||ns then
+                       -- take the next sequence
+                       select nextval('s_jnt_fic_att_value') into ns;
+               end if;
+               tText  :='FID'||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);
+
+
+       update jrnx set j_qcode=tText where j_qcode = old_qcode;
+       return ns;
+       end;
+$BODY$
+  LANGUAGE plpgsql;



---
PhpCompta est un logiciel de comptabilité libre en ligne (full web)
Projet opensource http://www.phpcompta.eu