[Noalyss-commit] [noalyss] 02/08: fix bug for too long accounting number

[Dany De Bontridder]

sparkyx pushed a commit to branch master
in repository noalyss.

commit c557fcde607c9bd391524eab457c6343a04766ff
Author: Dany De Bontridder <address@hidden>
Date:   Thu Jun 11 23:41:12 2015 +0200

    fix bug for too long accounting number
---
 sql/upgrade.sql |  158 ++++++++++++++++++++++++++++++++++++++++++++++---------
 1 files changed, 133 insertions(+), 25 deletions(-)

diff --git a/sql/upgrade.sql b/sql/upgrade.sql
index c7117ac..52cc7f8 100644
--- a/sql/upgrade.sql
+++ b/sql/upgrade.sql
@@ -107,30 +107,138 @@ CREATE TABLE tmp_bal_aged_child (
   PRIMARY KEY (id));
 COMMENT ON TABLE tmp_bal_aged_child IS 'Table temporaire pour le calcul des 
balances agées';
 */
-CREATE TABLE tmp_bal_aged (
-  id         SERIAL NOT NULL, 
-  create_on timestamp default now(), 
-  PRIMARY KEY (id));
-COMMENT ON TABLE tmp_bal_aged IS 'Table temporaire pour le calcul des balances 
agées';
-
-CREATE TABLE tmp_bal_aged_child
-(
-  id serial primary key,
-  j_id bigint,
-  j_date date,
-  j_date_fmt text,
-  jr_pj_number text,
-  j_montant numeric(20,4),
-  j_debit boolean,
-  jr_comment text,
-  jr_internal text,
-  jr_id integer,
-  jr_def_id integer,
-  letter bigint,
-  letter_diff numeric,
-  date_part double precision,
-  tmp_bal_agedid bigint NOT NULL, 
 
-);
-COMMENT ON TABLE tmp_bal_aged_child IS 'Table temporaire pour le calcul des 
balances agées';
 
+CREATE OR REPLACE FUNCTION comptaproc.account_compute(p_f_id integer)
+  RETURNS account_type AS
+$BODY$
+declare
+       class_base fiche_def.fd_class_base%type;
+       maxcode numeric;
+       sResult text;
+       bAlphanum bool;
+       sName text;
+begin
+       select fd_class_base into class_base
+       from
+               fiche_def join fiche using (fd_id)
+       where
+               f_id=p_f_id;
+       raise notice 'account_compute class base %',class_base;
+       bAlphanum := account_alphanum();
+       if bAlphanum = false  then
+       raise info 'account_compute : Alphanum is false';
+               select count (pcm_val) into maxcode from tmp_pcmn where 
pcm_val_parent = class_base;
+               if maxcode = 0  then
+                       maxcode:=class_base::numeric;
+               else
+                       select max (pcm_val) into maxcode from tmp_pcmn where 
pcm_val_parent = class_base;
+                       maxcode:=maxcode::numeric;
+               end if;
+               if maxcode::text = class_base then
+                       maxcode:=class_base::numeric*1000;
+               end if;
+               maxcode:=maxcode+1;
+               raise notice 'account_compute Max code %',maxcode;
+               sResult:=maxcode::account_type;
+       else
+       raise info 'account_compute : Alphanum is true';
+               -- if alphanum, use name
+               select ad_value into sName from fiche_detail where f_id=p_f_id 
and ad_id=1;
+               raise info 'name is %',sName;
+               if sName is null then
+                       raise exception 'Cannot compute an accounting without 
the name of the card for %',p_f_id;
+               end if;
+               sResult := class_base||sName;
+               sResult := substr(sResult,1,40);
+               raise info 'Result is %',sResult;
+       end if;
+       return sResult::account_type;
+end;
+$BODY$;
+
+CREATE OR REPLACE FUNCTION comptaproc.account_insert(p_f_id integer, p_account 
text)
+  RETURNS text AS
+$BODY$
+declare
+       nParent tmp_pcmn.pcm_val_parent%type;
+       sName varchar;
+       sNew tmp_pcmn.pcm_val%type;
+       bAuto bool;
+       nFd_id integer;
+       sClass_Base fiche_def.fd_class_base%TYPE;
+       nCount integer;
+       first text;
+       second text;
+       s_account 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';
+                               s_account := substr( p_account,1 , 40);
+                               select count(*)  into nCount from tmp_pcmn 
where pcm_val=s_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,s_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(s_account::account_type);
+                                       insert into 
tmp_pcmn(pcm_val,pcm_lib,pcm_val_parent) values 
(s_account::account_type,sName,nParent);
+                                       perform 
attribut_insert(p_f_id,5,s_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 'A000 : 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';
+                       sNew:=account_compute(p_f_id);
+                       raise info 'sNew %', sNew;
+                       select ad_value into sName from
+                               fiche_detail
+                       where
+                               ad_id=1 and f_id=p_f_id;
+                       nParent:=account_parent(sNew);
+                       sNew := account_add  (sNew,sName);
+                       perform attribut_insert(p_f_id,5,sNew);
+
+               else
+               -- if there is an account_base then it is the default
+                     select fd_class_base::account_type into sNew from 
fiche_def join fiche using (fd_id) where f_id=p_f_id;
+                       if sNew is null or length(trim(sNew)) = 0 then
+                               raise notice 'count is null';
+                                perform attribut_insert(p_f_id,5,null);
+                       else
+                                perform attribut_insert(p_f_id,5,sNew);
+                       end if;
+               end if;
+       end if;
+
+return 0;
+end;
+$BODY$  LANGUAGE plpgsql ;