[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Phpcompta-dev] r4166 - phpcompta/branches/rel560/sql
From: |
phpcompta-dev |
Subject: |
[Phpcompta-dev] r4166 - phpcompta/branches/rel560/sql |
Date: |
Fri, 17 Jun 2011 22:26:23 +0200 (CEST) |
Author: danydb
Date: 2011-06-17 22:26:23 +0200 (Fri, 17 Jun 2011)
New Revision: 4166
Modified:
phpcompta/branches/rel560/sql/upgrade.sql
Log:
0000296: Calcul automatique fiche
Modified: phpcompta/branches/rel560/sql/upgrade.sql
===================================================================
--- phpcompta/branches/rel560/sql/upgrade.sql 2011-06-17 19:55:06 UTC (rev
4165)
+++ phpcompta/branches/rel560/sql/upgrade.sql 2011-06-17 20:26:23 UTC (rev
4166)
@@ -9,3 +9,131 @@
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
[Prev in Thread] |
Current Thread |
[Next in Thread] |
- [Phpcompta-dev] r4166 - phpcompta/branches/rel560/sql,
phpcompta-dev <=