[Phpcompta-dev] r4405 - phpcompta/trunk/sql

[phpcompta-dev]

Author: danydb
Date: 2011-11-18 15:52:58 +0100 (Fri, 18 Nov 2011)
New Revision: 4405

Modified:
   phpcompta/trunk/sql/tva.sql
Log:
SQL : tva procedure to change

Modified: phpcompta/trunk/sql/tva.sql
===================================================================
--- phpcompta/trunk/sql/tva.sql 2011-11-18 13:58:19 UTC (rev 4404)
+++ phpcompta/trunk/sql/tva.sql 2011-11-18 14:52:58 UTC (rev 4405)
@@ -1,3 +1,74 @@
 alter table tva_rate add tva_both_side integer ;
 alter table tva_rate alter tva_both_side set default 0;
-update tva_rate set tva_both_side=0;
\ No newline at end of file
+update tva_rate set tva_both_side=0;drop FUNCTION 
comptaproc.tva_modify(integer, text, numeric, text, text);
+
+CREATE OR REPLACE FUNCTION comptaproc.tva_modify(integer, text, numeric, text, 
text,integer)
+ RETURNS integer
+ LANGUAGE plpgsql
+AS $function$
+declare
+       p_tva_id alias for $1;
+       p_tva_label alias for $2;
+       p_tva_rate alias for $3;
+       p_tva_comment alias for $4;
+       p_tva_poste alias for $5;
+       p_tva_both_side alias for $6;
+       debit text;
+       credit text;
+       nCount integer;
+begin
+if length(trim(p_tva_label)) = 0 then
+       return 3;
+end if;
+
+if length(trim(p_tva_poste)) != 0 then
+       if position (',' in p_tva_poste) = 0 then return 4; end if;
+       debit  = split_part(p_tva_poste,',',1);
+       credit  = split_part(p_tva_poste,',',2);
+       select count(*) into nCount from tmp_pcmn where 
pcm_val=debit::account_type;
+       if nCount = 0 then return 4; end if;
+       select count(*) into nCount from tmp_pcmn where 
pcm_val=credit::account_type;
+       if nCount = 0 then return 4; end if;
+
+end if;
+update tva_rate set 
tva_label=p_tva_label,tva_rate=p_tva_rate,tva_comment=p_tva_comment,tva_poste=p_tva_poste,tva_both_side=p_tva_both_side
+       where tva_id=p_tva_id;
+return 0;
+end;
+$function$
+drop FUNCTION comptaproc.tva_insert(text, numeric, text, text);
+CREATE OR REPLACE FUNCTION comptaproc.tva_insert(text, numeric, text, 
text,integer)
+ RETURNS integer
+ LANGUAGE plpgsql
+AS $function$
+declare
+       l_tva_id integer;
+       p_tva_label alias for $1;
+       p_tva_rate alias for $2;
+       p_tva_comment alias for $3;
+       p_tva_poste alias for $4;
+       p_tva_both_side alias for $5;
+       debit text;
+       credit text;
+       nCount integer;
+begin
+if length(trim(p_tva_label)) = 0 then
+       return 3;
+end if;
+
+if length(trim(p_tva_poste)) != 0 then
+       if position (',' in p_tva_poste) = 0 then return 4; end if;
+       debit  = split_part(p_tva_poste,',',1);
+       credit  = split_part(p_tva_poste,',',2);
+       select count(*) into nCount from tmp_pcmn where 
pcm_val=debit::account_type;
+       if nCount = 0 then return 4; end if;
+       select count(*) into nCount from tmp_pcmn where 
pcm_val=credit::account_type;
+       if nCount = 0 then return 4; end if;
+
+end if;
+select into l_tva_id nextval('s_tva') ;
+insert into 
tva_rate(tva_id,tva_label,tva_rate,tva_comment,tva_poste,tva_both_side)
+       values 
(l_tva_id,p_tva_label,p_tva_rate,p_tva_comment,p_tva_poste,p_tva_both_side);
+return 0;
+end;
+$function$