[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Phpcompta-dev] r4108 - in phpcompta/trunk: html/admin/sql/patch include
|
From: |
phpcompta-dev |
|
Subject: |
[Phpcompta-dev] r4108 - in phpcompta/trunk: html/admin/sql/patch include sql |
|
Date: |
Mon, 16 May 2011 18:57:33 +0200 (CEST) |
Author: danydb
Date: 2011-05-16 18:57:33 +0200 (Mon, 16 May 2011)
New Revision: 4108
Added:
phpcompta/trunk/html/admin/sql/patch/upgrade93.sql
phpcompta/trunk/sql/downgrade.sql
Modified:
phpcompta/trunk/include/class_anc_acc_list.php
phpcompta/trunk/include/export_anc_table_csv.php
phpcompta/trunk/sql/upgrade.sql
Log:
Fix bug with date in ANC_LIST
Added: phpcompta/trunk/html/admin/sql/patch/upgrade93.sql
===================================================================
--- phpcompta/trunk/html/admin/sql/patch/upgrade93.sql
(rev 0)
+++ phpcompta/trunk/html/admin/sql/patch/upgrade93.sql 2011-05-16 16:57:33 UTC
(rev 4108)
@@ -0,0 +1,122 @@
+begin;
+
+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;
+
+
+update version set val=94;
+commit;
Modified: phpcompta/trunk/include/class_anc_acc_list.php
===================================================================
--- phpcompta/trunk/include/class_anc_acc_list.php 2011-05-16 16:32:01 UTC
(rev 4107)
+++ phpcompta/trunk/include/class_anc_acc_list.php 2011-05-16 16:57:33 UTC
(rev 4108)
@@ -63,11 +63,26 @@
$date=($date != '')?" $date":'';
$sql_from_poste=($this->from_poste!='')?" and po.po_name >=
upper('".Database::escape_string($this->from_poste)."')":'';
$sql_to_poste=($this->to_poste!='')?" and po.po_name <=
upper('".Database::escape_string($this->to_poste)."')":'';
- $this->arow=$this->db->get_array(" SELECT po_id, pa_id, po_name,
po_description, sum_amount, j_poste, name
- FROM v_table_analytic_account
- where
- pa_id=$1 ".$date.$sql_from_poste.$sql_to_poste." order by
po_id,j_poste",array($this->pa_id));
+ $this->arow=$this->db->get_array("
+ 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 ".
+" where
+ pa_id=$1 ".$date.$sql_from_poste.$sql_to_poste."
+ 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 order by po_id,j_poste",array($this->pa_id));
+
}
/**
* load the data
@@ -79,12 +94,26 @@
$date=($date != '')?" $date":'';
$sql_from_poste=($this->from_poste!='')?" and po.po_name >=
upper('".Database::escape_string($this->from_poste)."')":'';
$sql_to_poste=($this->to_poste!='')?" and po.po_name <=
upper('".Database::escape_string($this->to_poste)."')":'';
-
- $this->arow=$this->db->get_array(" SELECT po_id, pa_id, po_name,
po_description, oa_date, sum_amount, f_id,
- j_qcode, name
- FROM v_table_analytic_card
- where
- pa_id=$1 ".$date.$sql_from_poste.$sql_to_poste." order by
po_id,name",array($this->pa_id));
+ $this->arow=$this->db->get_array(" 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) ".
+ " where pa_id=$1
".$date.$sql_from_poste.$sql_to_poste
+ ."
+ 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 order by po_name,name",array($this->pa_id));
}
@@ -98,11 +127,25 @@
$date=($date != '')?" $date":'';
$sql_from_poste=($this->from_poste!='')?" and po.po_name >=
upper('".Database::escape_string($this->from_poste)."')":'';
$sql_to_poste=($this->to_poste!='')?" and po.po_name <=
upper('".Database::escape_string($this->to_poste)."')":'';
- $this->arow=$this->db->get_array(" SELECT po_id, pa_id, po_name,
po_description, sum_amount, j_poste, name
- FROM v_table_analytic_account
- where
- pa_id=$1 ".$date.$sql_from_poste.$sql_to_poste." order by
j_poste,po_name",array($this->pa_id));
+ $this->arow=$this->db->get_array("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 ".
+" where
+ pa_id=$1 ".$date.$sql_from_poste.$sql_to_poste."
+ 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 order by j_poste,po_name",array($this->pa_id));
+
}
/**
@@ -116,12 +159,26 @@
$sql_from_poste=($this->from_poste!='')?" and po.po_name >=
upper('".Database::escape_string($this->from_poste)."')":'';
$sql_to_poste=($this->to_poste!='')?" and po.po_name <=
upper('".Database::escape_string($this->to_poste)."')":'';
- $this->arow=$this->db->get_array(" SELECT po_id, pa_id, po_name,
po_description, oa_date, sum_amount, f_id,
- j_qcode, name
- FROM v_table_analytic_card
- where
- pa_id=$1 ".$date.$sql_from_poste.$sql_to_poste." order by
name,po_name",array($this->pa_id));
-
+ $this->arow=$this->db->get_array(" 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) ".
+ " where pa_id=$1
".$date.$sql_from_poste.$sql_to_poste
+ ."
+ 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 order by name,po_name",array($this->pa_id));
}
/**
address@hidden display the button export CSV
@@ -297,7 +354,7 @@
$tot_card=bcadd($tot_card,$amount);
$tot_glob=bcadd($tot_glob,$amount);
- echo '<td style="padding-left:10">'.HtmlInput::history_card
($this->arow[$i]['f_id'],$this->arow[$i]['f_id'].' '.$this->arow[$i]['name'],'
display:inline').'</td>';
+ echo '<td style="padding-left:10">'.HtmlInput::history_card
($this->arow[$i]['f_id'],$this->arow[$i]['j_qcode'].'
'.$this->arow[$i]['name'],' display:inline').'</td>';
echo td(nbm($amount),' class="num" ');
echo '</tr>';
Modified: phpcompta/trunk/include/export_anc_table_csv.php
===================================================================
--- phpcompta/trunk/include/export_anc_table_csv.php 2011-05-16 16:32:01 UTC
(rev 4107)
+++ phpcompta/trunk/include/export_anc_table_csv.php 2011-05-16 16:57:33 UTC
(rev 4108)
@@ -25,7 +25,7 @@
*/
header('Pragma: public');
-header('Content-type: application/csv');
+header('Content-type: application/csv');
header('Content-Disposition:
attachment;filename="anc-table-export.csv"',FALSE);
require_once('class_anc_table.php');
Added: phpcompta/trunk/sql/downgrade.sql
===================================================================
--- phpcompta/trunk/sql/downgrade.sql (rev 0)
+++ phpcompta/trunk/sql/downgrade.sql 2011-05-16 16:57:33 UTC (rev 4108)
@@ -0,0 +1,4 @@
+alter table operation_analytique add column pa_id bigint;
+ALTER TABLE operation_analytique DROP CONSTRAINT
operation_analytique_oa_amount_check;
+drop type anc_table_card_type cascade;
+drop type anc_table_account_type cascade;
Modified: phpcompta/trunk/sql/upgrade.sql
===================================================================
--- phpcompta/trunk/sql/upgrade.sql 2011-05-16 16:32:01 UTC (rev 4107)
+++ phpcompta/trunk/sql/upgrade.sql 2011-05-16 16:57:33 UTC (rev 4108)
@@ -1,8 +1,39 @@
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 OR REPLACE VIEW v_table_analytic_card AS
- SELECT po.po_id, po.pa_id, po.po_name, po.po_description, sum(
+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
@@ -11,7 +42,7 @@
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)
+ 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
@@ -19,11 +50,50 @@
CASE
WHEN operation_analytique.oa_debit = true THEN
operation_analytique.oa_amount * (-1)::numeric
ELSE operation_analytique.oa_amount
-END) <> 0::numeric;
+END) <> 0::numeric;'
-CREATE OR REPLACE VIEW v_table_analytic_account AS
- SELECT po.po_id, po.pa_id, po.po_name, po.po_description, sum(
+ 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
@@ -32,11 +102,18 @@
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;
+END) <> 0::numeric '
+ loop
+ return next ret;
+end loop;
+end;
+$BODY$ language plpgsql;
-commit;
+
+
| [Prev in Thread] |
Current Thread |
[Next in Thread] |
- [Phpcompta-dev] r4108 - in phpcompta/trunk: html/admin/sql/patch include sql,
phpcompta-dev <=