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

[phpcompta-dev]

Author: danydb
Date: 2011-05-22 23:30:38 +0200 (Sun, 22 May 2011)
New Revision: 4118

Modified:
   phpcompta/trunk/sql/upgrade.sql
Log:
Put in upgrade93.sql

Modified: phpcompta/trunk/sql/upgrade.sql
===================================================================
--- phpcompta/trunk/sql/upgrade.sql     2011-05-22 16:08:17 UTC (rev 4117)
+++ phpcompta/trunk/sql/upgrade.sql     2011-05-22 21:30:38 UTC (rev 4118)
@@ -1,119 +0,0 @@
-alter table operation_analytique drop column pa_id;
-ALTER TABLE operation_analytique  ADD CONSTRAINT 
operation_analytique_oa_amount_check CHECK (oa_amount >= 0::numeric);
-
-create type anc_table_card_type as (po_id bigint,pa_id bigint,PO_NAME 
TEXT,po_description text,sum_amount numeric(25,4),f_id bigint,card_account 
text,name text);
-
-drop function comptaproc.table_analytic_card (p_from text,p_to text);
-
-create or replace function comptaproc.table_analytic_card (p_from text,p_to 
text)
-returns setof anc_table_card_type  
-as 
-$BODY$
-declare
-       ret ANC_table_card_type%ROWTYPE;
-       sql_from text:='';
-       sql_to text:='';
-       sWhere text:='';
-       sAnd text:='';
-       sResult text:='';
-begin
-if p_from <> '' and p_from is not null then
-       sql_from:='oa_date >= to_date('''||p_from::text||''',''DD.MM.YYYY'')';
-       sWhere:=' where ';
-end if;
-
-if p_to <> '' and p_to is not null then
-       sql_to=' oa_date <= to_date('''||p_to::text||''',''DD.MM.YYYY'')';
-       sWhere := ' where ';
-end if;
-
-if sql_to <> '' and sql_from <> '' then
-       sAnd :=' and ';
-end if;
-
-sResult := sWhere || sql_from || sAnd || sql_to;
-
-for ret in EXECUTE ' SELECT po.po_id, po.pa_id, po.po_name, po.po_description, 
 sum(
-        CASE
-            WHEN operation_analytique.oa_debit = true THEN 
operation_analytique.oa_amount * (-1)::numeric
-            ELSE operation_analytique.oa_amount
-        END) AS sum_amount, jrnx.f_id, jrnx.j_qcode, ( SELECT 
fiche_detail.ad_value
-           FROM fiche_detail
-          WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id = jrnx.f_id) AS 
name
-   FROM operation_analytique
-   JOIN poste_analytique po USING (po_id)
-   JOIN jrnx USING (j_id)'|| sResult ||'
-  GROUP BY po.po_id, po.po_name, po.pa_id, jrnx.f_id, jrnx.j_qcode, ( SELECT 
fiche_detail.ad_value
-   FROM fiche_detail
-  WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id = jrnx.f_id), 
po.po_description
- HAVING sum(
-CASE
-    WHEN operation_analytique.oa_debit = true THEN 
operation_analytique.oa_amount * (-1)::numeric
-    ELSE operation_analytique.oa_amount
-END) <> 0::numeric;'
-
-
-       loop
-       return next ret;
-end loop;
-end;
-$BODY$ language plpgsql;
-
-
-create type anc_table_account_type as (po_id bigint,pa_id bigint,PO_NAME 
TEXT,po_description text,sum_amount numeric(25,4),card_account text,name text);
-
-drop function comptaproc.table_analytic_account (p_from text,p_to text);
-create or replace function comptaproc.table_analytic_account (p_from text,p_to 
text)
-returns setof anc_table_account_type 
-as 
-$BODY$
-declare
-       ret ANC_table_account_type%ROWTYPE;
-       sql_from text:='';
-       sql_to text:='';
-       sWhere text:='';
-       sAnd text:='';
-       sResult text:='';
-begin
-if p_from <> '' and p_from is not null then
-       sql_from:='oa_date >= to_date('''||p_from::text||''',''DD.MM.YYYY'')';
-       sWhere:=' where ';
-end if;
-
-if p_to <> '' and p_to is not null then
-       sql_to=' oa_date <= to_date('''||p_to::text||''',''DD.MM.YYYY'')';
-       sWhere := ' where ';
-end if;
-
-if sql_to <> '' and sql_from <> '' then
-       sAnd:=' and ';
-end if;
-
-sResult := sWhere || sql_from || sAnd || sql_to;
-
-for ret in EXECUTE 'SELECT po.po_id,
-                           po.pa_id, po.po_name, 
-                           po.po_description,sum(
-        CASE
-            WHEN operation_analytique.oa_debit = true THEN 
operation_analytique.oa_amount * (-1)::numeric
-            ELSE operation_analytique.oa_amount
-        END) AS sum_amount, jrnx.j_poste, tmp_pcmn.pcm_lib AS name
-   FROM operation_analytique
-   JOIN poste_analytique po USING (po_id)
-   JOIN jrnx USING (j_id)
-   JOIN tmp_pcmn ON jrnx.j_poste::text = tmp_pcmn.pcm_val::text
-'|| sResult ||'
-  GROUP BY po.po_id, po.po_name, po.pa_id, jrnx.j_poste, tmp_pcmn.pcm_lib, 
po.po_description
- HAVING sum(
-CASE
-    WHEN operation_analytique.oa_debit = true THEN 
operation_analytique.oa_amount * (-1)::numeric
-    ELSE operation_analytique.oa_amount
-END) <> 0::numeric '
-       loop
-       return next ret;
-end loop;
-end;
-$BODY$ language plpgsql;
-
-
-