[Phpcompta-dev] r4168 - in phpcompta/branches/rel560: html/admin/sql/patch include sql

[phpcompta-dev]

Author: danydb
Date: 2011-06-17 22:57:29 +0200 (Fri, 17 Jun 2011)
New Revision: 4168

Added:
   phpcompta/branches/rel560/html/admin/sql/patch/upgrade95.sql
Modified:
   phpcompta/branches/rel560/include/constant.php
   phpcompta/branches/rel560/sql/upgrade.sql
Log:
0000305: Charge au cr?\195?\169dit

new sql script

Added: phpcompta/branches/rel560/html/admin/sql/patch/upgrade95.sql
===================================================================
--- phpcompta/branches/rel560/html/admin/sql/patch/upgrade95.sql                
                (rev 0)
+++ phpcompta/branches/rel560/html/admin/sql/patch/upgrade95.sql        
2011-06-17 20:57:29 UTC (rev 4168)
@@ -0,0 +1,147 @@
+begin;
+
+delete from fiche_detail where jft_id in (
+    select a.jft_id
+from fiche_detail as a ,fiche_detail as b
+where
+a.f_id=b.f_id
+and a.ad_id = b.ad_id
+and a.jft_id > b.jft_id);
+
+
+create unique index fiche_Detail_f_id_ad_id on fiche_detail (f_id,ad_id);
+
+CREATE OR REPLACE FUNCTION comptaproc.account_insert(p_f_id integer, p_account 
text)
+  RETURNS integer AS
+$BODY$
+declare
+       nParent tmp_pcmn.pcm_val_parent%type;
+       sName varchar;
+       nNew tmp_pcmn.pcm_val%type;
+       bAuto bool;
+       nFd_id integer;
+       sClass_Base fiche_def.fd_class_base%TYPE;
+       nCount integer;
+       first text;
+       second text;
+begin
+
+       if p_account is not null and length(trim(p_account)) != 0 then
+       -- if there is coma in p_account, treat normally
+               if position (',' in p_account) = 0 then
+                       raise info 'p_account is not empty';
+                               select count(*)  into nCount from tmp_pcmn 
where pcm_val=p_account::account_type;
+                               raise notice 'found in tmp_pcm %',nCount;
+                               if nCount !=0  then
+                                       raise info 'this account exists in 
tmp_pcmn ';
+                                       perform 
attribut_insert(p_f_id,5,p_account);
+                                  else
+                                      -- account doesn't exist, create it
+                                       select ad_value into sName from
+                                               fiche_detail
+                                       where
+                                       ad_id=1 and f_id=p_f_id;
+
+                                       
nParent:=account_parent(p_account::account_type);
+                                       insert into 
tmp_pcmn(pcm_val,pcm_lib,pcm_val_parent) values 
(p_account::account_type,sName,nParent);
+                                       perform 
attribut_insert(p_f_id,5,p_account);
+
+                               end if;
+               else
+               raise info 'presence of a comma';
+               -- there is 2 accounts separated by a comma
+               first := split_part(p_account,',',1);
+               second := split_part(p_account,',',2);
+               -- check there is no other coma
+               raise info 'first value % second value %', first, second;
+
+               if  position (',' in first) != 0 or position (',' in second) != 
0 then
+                       raise exception 'Too many comas, invalid account';
+               end if;
+               perform attribut_insert(p_f_id,5,p_account);
+               end if;
+       else
+       raise info 'p_account is  empty';
+               select fd_id into nFd_id from fiche where f_id=p_f_id;
+               bAuto:= account_auto(nFd_id);
+
+               select fd_class_base into sClass_base from fiche_def where 
fd_id=nFd_id;
+raise info 'sClass_Base : %',sClass_base;
+               if bAuto = true and sClass_base similar to '[[:digit:]]*'  then
+                       raise info 'account generated automatically';
+                       nNew:=account_compute(p_f_id);
+                       raise info 'nNew %', nNew;
+                       select ad_value into sName from
+                               fiche_detail
+                       where
+                               ad_id=1 and f_id=p_f_id;
+                       nParent:=account_parent(nNew);
+                       perform account_add  (nNew,sName);
+                       perform attribut_insert(p_f_id,5,nNew);
+
+               else
+               -- if there is an account_base then it is the default
+                     select fd_class_base::account_type into nNew from 
fiche_def join fiche using (fd_id) where f_id=p_f_id;
+                       if nNew is null or length(trim(nNew)) = 0 then
+                               raise notice 'count is null';
+                                perform attribut_insert(p_f_id,5,null);
+                       else
+                                perform attribut_insert(p_f_id,5,nNew);
+                       end if;
+               end if;
+       end if;
+
+return 0;
+end;
+$BODY$
+  LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION comptaproc.account_update(p_f_id integer, p_account 
account_type)
+  RETURNS integer AS
+$BODY$
+declare
+       nMax fiche.f_id%type;
+       nCount integer;
+       nParent tmp_pcmn.pcm_val_parent%type;
+       sName varchar;
+       first text;
+       second text;
+begin
+
+       if p_account is not null and length(trim(p_account)) != 0 then
+               if position (',' in p_account) = 0 then
+                       select count(*) into nCount from tmp_pcmn where 
pcm_val=p_account;
+                       if nCount = 0 then
+                       select ad_value into sName from
+                               fiche_detail
+                               where
+                               ad_id=1 and f_id=p_f_id;
+                       nParent:=account_parent(p_account);
+                       insert into tmp_pcmn(pcm_val,pcm_lib,pcm_val_parent) 
values (p_account,sName,nParent);
+                       end if;
+               else
+               raise info 'presence of a comma';
+               -- there is 2 accounts separated by a comma
+               first := split_part(p_account,',',1);
+               second := split_part(p_account,',',2);
+               -- check there is no other coma
+               raise info 'first value % second value %', first, second;
+
+               if  position (',' in first) != 0 or position (',' in second) != 
0 then
+                       raise exception 'Too many comas, invalid account';
+               end if;
+               end if;
+       end if;
+       
+       update fiche_detail set ad_value=p_account where f_id=p_f_id and 
ad_id=5 ;
+
+return 0;
+end;
+$BODY$
+  LANGUAGE plpgsql;
+
+update operation_analytique set oa_debit=j_debit from jrnx where jrnx.j_id = 
operation_analytique.j_id;
+
+update version set val=96;
+
+commit;
\ No newline at end of file

Modified: phpcompta/branches/rel560/include/constant.php
===================================================================
--- phpcompta/branches/rel560/include/constant.php      2011-06-17 20:49:53 UTC 
(rev 4167)
+++ phpcompta/branches/rel560/include/constant.php      2011-06-17 20:57:29 UTC 
(rev 4168)
@@ -38,11 +38,11 @@
 $version_phpcompta=SVNINFO; 
 define ("DEBUG",false);
 
-//define ("DEBUG",true);
-//$version_phpcompta=4014;
+/*define ("DEBUG",true);
+$version_phpcompta=4014;
+*/
 
-
-define ("DBVERSION",95);
+define ("DBVERSION",96);
 define ("DBVERSIONREPO",13);
 define ('NOTFOUND','--not found--');
 

Modified: phpcompta/branches/rel560/sql/upgrade.sql
===================================================================
--- phpcompta/branches/rel560/sql/upgrade.sql   2011-06-17 20:49:53 UTC (rev 
4167)
+++ phpcompta/branches/rel560/sql/upgrade.sql   2011-06-17 20:57:29 UTC (rev 
4168)
@@ -1,139 +0,0 @@
-delete from fiche_detail where jft_id in (
-    select a.jft_id
-from fiche_detail as a ,fiche_detail as b
-where
-a.f_id=b.f_id
-and a.ad_id = b.ad_id
-and a.jft_id > b.jft_id);
-
-
-create unique index fiche_Detail_f_id_ad_id on fiche_detail (f_id,ad_id);
-
-CREATE OR REPLACE FUNCTION comptaproc.account_insert(p_f_id integer, p_account 
text)
-  RETURNS integer AS
-$BODY$
-declare
-       nParent tmp_pcmn.pcm_val_parent%type;
-       sName varchar;
-       nNew tmp_pcmn.pcm_val%type;
-       bAuto bool;
-       nFd_id integer;
-       sClass_Base fiche_def.fd_class_base%TYPE;
-       nCount integer;
-       first text;
-       second text;
-begin
-
-       if p_account is not null and length(trim(p_account)) != 0 then
-       -- if there is coma in p_account, treat normally
-               if position (',' in p_account) = 0 then
-                       raise info 'p_account is not empty';
-                               select count(*)  into nCount from tmp_pcmn 
where pcm_val=p_account::account_type;
-                               raise notice 'found in tmp_pcm %',nCount;
-                               if nCount !=0  then
-                                       raise info 'this account exists in 
tmp_pcmn ';
-                                       perform 
attribut_insert(p_f_id,5,p_account);
-                                  else
-                                      -- account doesn't exist, create it
-                                       select ad_value into sName from
-                                               fiche_detail
-                                       where
-                                       ad_id=1 and f_id=p_f_id;
-
-                                       
nParent:=account_parent(p_account::account_type);
-                                       insert into 
tmp_pcmn(pcm_val,pcm_lib,pcm_val_parent) values 
(p_account::account_type,sName,nParent);
-                                       perform 
attribut_insert(p_f_id,5,p_account);
-
-                               end if;
-               else
-               raise info 'presence of a comma';
-               -- there is 2 accounts separated by a comma
-               first := split_part(p_account,',',1);
-               second := split_part(p_account,',',2);
-               -- check there is no other coma
-               raise info 'first value % second value %', first, second;
-
-               if  position (',' in first) != 0 or position (',' in second) != 
0 then
-                       raise exception 'Too many comas, invalid account';
-               end if;
-               perform attribut_insert(p_f_id,5,p_account);
-               end if;
-       else
-       raise info 'p_account is  empty';
-               select fd_id into nFd_id from fiche where f_id=p_f_id;
-               bAuto:= account_auto(nFd_id);
-
-               select fd_class_base into sClass_base from fiche_def where 
fd_id=nFd_id;
-raise info 'sClass_Base : %',sClass_base;
-               if bAuto = true and sClass_base similar to '[[:digit:]]*'  then
-                       raise info 'account generated automatically';
-                       nNew:=account_compute(p_f_id);
-                       raise info 'nNew %', nNew;
-                       select ad_value into sName from
-                               fiche_detail
-                       where
-                               ad_id=1 and f_id=p_f_id;
-                       nParent:=account_parent(nNew);
-                       perform account_add  (nNew,sName);
-                       perform attribut_insert(p_f_id,5,nNew);
-
-               else
-               -- if there is an account_base then it is the default
-                     select fd_class_base::account_type into nNew from 
fiche_def join fiche using (fd_id) where f_id=p_f_id;
-                       if nNew is null or length(trim(nNew)) = 0 then
-                               raise notice 'count is null';
-                                perform attribut_insert(p_f_id,5,null);
-                       else
-                                perform attribut_insert(p_f_id,5,nNew);
-                       end if;
-               end if;
-       end if;
-
-return 0;
-end;
-$BODY$
-  LANGUAGE plpgsql;
-
-CREATE OR REPLACE FUNCTION comptaproc.account_update(p_f_id integer, p_account 
account_type)
-  RETURNS integer AS
-$BODY$
-declare
-       nMax fiche.f_id%type;
-       nCount integer;
-       nParent tmp_pcmn.pcm_val_parent%type;
-       sName varchar;
-       first text;
-       second text;
-begin
-
-       if p_account is not null and length(trim(p_account)) != 0 then
-               if position (',' in p_account) = 0 then
-                       select count(*) into nCount from tmp_pcmn where 
pcm_val=p_account;
-                       if nCount = 0 then
-                       select ad_value into sName from
-                               fiche_detail
-                               where
-                               ad_id=1 and f_id=p_f_id;
-                       nParent:=account_parent(p_account);
-                       insert into tmp_pcmn(pcm_val,pcm_lib,pcm_val_parent) 
values (p_account,sName,nParent);
-                       end if;
-               else
-               raise info 'presence of a comma';
-               -- there is 2 accounts separated by a comma
-               first := split_part(p_account,',',1);
-               second := split_part(p_account,',',2);
-               -- check there is no other coma
-               raise info 'first value % second value %', first, second;
-
-               if  position (',' in first) != 0 or position (',' in second) != 
0 then
-                       raise exception 'Too many comas, invalid account';
-               end if;
-               end if;
-       end if;
-       
-       update fiche_detail set ad_value=p_account where f_id=p_f_id and 
ad_id=5 ;
-
-return 0;
-end;
-$BODY$
-  LANGUAGE plpgsql;
\ No newline at end of file