[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Noalyss-commit] [noalyss] 146/219: ANC : ANC_TABLE replace PGPLSQL proc
From: |
Dany De Bontridder |
Subject: |
[Noalyss-commit] [noalyss] 146/219: ANC : ANC_TABLE replace PGPLSQL procedure by Php functions : Anc_Table:create_temp_account Anc_Table:create_temp_card |
Date: |
Mon, 18 Dec 2017 13:22:54 -0500 (EST) |
sparkyx pushed a commit to branch master
in repository noalyss.
commit 7e768e63c7f47d99dfbb5cde00e450c190094341
Author: Dany De Bontridder <address@hidden>
Date: Tue Nov 7 13:25:10 2017 +0100
ANC : ANC_TABLE replace PGPLSQL procedure by Php functions :
Anc_Table:create_temp_account
Anc_Table:create_temp_card
---
include/class/anc_acc_link.class.php | 4 +-
include/class/anc_table.class.php | 127 +++++++++++++++++++++++++++++++--
sql/upgrade.sql | 132 +----------------------------------
3 files changed, 127 insertions(+), 136 deletions(-)
diff --git a/include/class/anc_acc_link.class.php
b/include/class/anc_acc_link.class.php
index 9bea597..345363a 100644
--- a/include/class/anc_acc_link.class.php
+++ b/include/class/anc_acc_link.class.php
@@ -44,11 +44,11 @@ class Anc_Acc_Link extends Anc_Print
{
$sql="";
$and=" and ";
- if ( $this->from != "" )
+ if ( $this->from != "" && $this->from=isDate($this->from) )
{
$sql.="$and oa_date >= to_date('".$this->from."','DD.MM.YYYY')";
}
- if ( $this->to != "" )
+ if ( $this->to != "" && $this->to=isDate($this->to))
{
$sql.=" $and oa_date <= to_date('".$this->to."','DD.MM.YYYY')";
}
diff --git a/include/class/anc_table.class.php
b/include/class/anc_table.class.php
index 3d216e5..a1d5667 100644
--- a/include/class/anc_table.class.php
+++ b/include/class/anc_table.class.php
@@ -26,6 +26,10 @@ require_once NOALYSS_INCLUDE.'/class/anc_acc_link.class.php';
class Anc_Table extends Anc_Acc_Link
{
+ function __construct($p_cn)
+ {
+ parent::__construct($p_cn);
+ }
/**
address@hidden display form to get the parameter
* - card_poste 1 by card, 2 by account
@@ -48,8 +52,121 @@ class Anc_Table extends Anc_Acc_Link
$r.=HtmlInput::request_to_hidden(array('ac'));
return $r;
}
+ /**
+ * @brief create the temporary table TABLE_ANALYTIC to store values
+ * for an account.
+ */
+ private function create_temp_table_account()
+ {
+ $sWhere="";
+ $sql_from="";
+ $sql_to="";
+ $sql_and="";
+
+ if ( trim($this->from) != "" && isDate($this->from)!=NULL) {
+ $sql_from="oa_date >= to_date('".$this->from."','DD.MM.YYYY')";
+ $sWhere=" where ";
+ }
+ if ( trim($this->to) != "" && isDate($this->to)!=NULL) {
+ $sql_to="oa_date <= to_date('".$this->to."','DD.MM.YYYY')";
+ $sWhere=" where ";
+ }
+ if ($sql_to != "" && $sql_from !="") {
+ $sql_and=" and ";
+ }
+ $sResult = $sWhere . $sql_from .$sql_and. $sql_to;
+ $sql="
+ create temporary table table_analytic as
+ SELECT po.po_id,
+ po.pa_id, po.po_name,
+ po.po_description,sum(
+ CASE
+ WHEN oa1.oa_debit = true THEN oa1.oa_amount * (-1)::numeric
+ ELSE oa1.oa_amount
+ END) AS sum_amount,
+ coalesce(jrnx.j_poste,fd1.ad_value) as card_account,
+ tmp_pcmn.pcm_lib AS name
+ FROM operation_analytique as oa1
+ JOIN poste_analytique po USING (po_id)
+ left join fiche_detail as fd1 on (oa1.f_id=fd1.f_id and fd1.ad_id=5)
+ left JOIN jrnx USING (j_id)
+ join tmp_pcmn ON (jrnx.j_poste::text = tmp_pcmn.pcm_val::text or
tmp_pcmn.pcm_val=fd1.ad_value)
+ {$sResult}
+ GROUP BY po.po_id, po.po_name, po.pa_id,
coalesce(jrnx.j_poste,fd1.ad_value), tmp_pcmn.pcm_lib, po.po_description
+ HAVING sum(
+ CASE
+ WHEN oa1.oa_debit = true THEN oa1.oa_amount * (-1)::numeric
+ ELSE oa1.oa_amount
+ END) <> 0::numeric ";
+ $this->db->exec_sql($sql);
+ }
+ /**
+ * @brief create the temporary table TABLE_ANALYTIC to store values for a
card
+ */
+ private function create_temp_table_card()
+ {
+ $sWhere="";
+ $sql_from="";
+ $sql_to="";
+ $sql_and="";
+
+ if ( trim($this->from) != "" && isDate($this->from)!=NULL) {
+ $sql_from="oa_date >= to_date('".$this->from."','DD.MM.YYYY')";
+ $sWhere=" where ";
+ }
+ if ( trim($this->to) != "" && isDate($this->to)!=NULL) {
+ $sql_to="oa_date <= to_date('".$this->to."','DD.MM.YYYY')";
+ $sWhere=" where ";
+ }
+ if ($sql_to != "" && $sql_from !="") {
+ $sql_and=" and ";
+ }
+ $sResult = $sWhere . $sql_from .$sql_and. $sql_to;
+ $sql="
+ create temporary table table_analytic as
+ with m as (select oa_id,po_id,
+ coalesce(jrnx.f_id,operation_analytique.f_id) as f_id1,
+ case when jrnx.j_qcode is not null then
+ ( SELECT fiche_detail.ad_value
+ FROM fiche_detail
+ WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id =
jrnx.f_id)
+ when jrnx.f_id is null and operation_analytique.f_id is not
null then
+ ( SELECT fiche_detail.ad_value
+ FROM fiche_detail
+ WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id =
operation_analytique.f_id)
+ end
+ AS name,
+ case when jrnx.j_qcode is not null then
+ jrnx.j_qcode
+ when jrnx.f_id is null then
+ (SELECT fiche_detail.ad_value
+ FROM fiche_detail
+ WHERE fiche_detail.ad_id = 23 AND fiche_detail.f_id =
operation_analytique.f_id) end as j_qcode
+ FROM operation_analytique
+ left JOIN jrnx USING (j_id) )
+ 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,
+ m.f_id1 as f_id,
+ m.name,
+ m.j_qcode as card_account
+ FROM operation_analytique
+ JOIN poste_analytique po USING (po_id)
+ join m using (oa_id)
+ {$sResult}
+ GROUP BY po.po_id, po.po_name, po.pa_id, m.f_id1, m.j_qcode,m.name,
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";
+ $this->db->exec_sql($sql);
+ }
+
/**
* load the data
* does not return anything but give a value to this->aheader and this->arow
@@ -58,8 +175,10 @@ class Anc_Table extends Anc_Acc_Link
{
$sql_from_poste=($this->from_poste!='')?" and po_name >=
upper('".Database::escape_string($this->from_poste)."')":'';
$sql_to_poste=($this->to_poste!='')?" and po_name <=
upper('".Database::escape_string($this->to_poste)."')":'';
- $this->db->exec_sql('create temporary table table_analytic as select *
from comptaproc.table_analytic_account(\''.$this->from.'\',\''.$this->to.'\')');
+
+ $this->create_temp_table_account();
+
$header="select distinct po_id,po_name from table_analytic
where
pa_id=$1 ".$sql_from_poste.$sql_to_poste." order by po_name";
@@ -81,8 +200,8 @@ class Anc_Table extends Anc_Acc_Link
{
$sql_from_poste=($this->from_poste!='')?" and po_name >=
upper('".Database::escape_string($this->from_poste)."')":'';
$sql_to_poste=($this->to_poste!='')?" and po_name <=
upper('".Database::escape_string($this->to_poste)."')":'';
- $this->db->exec_sql('create temporary table table_analytic as select *
from comptaproc.table_analytic_card(\''.$this->from.'\',\''.$this->to.'\')');
-
+ // $this->db->exec_sql('create temporary table table_analytic as select *
from comptaproc.table_analytic_card(\''.$this->from.'\',\''.$this->to.'\')');
+ $this->create_temp_table_card();
$header="select distinct po_id,po_name from table_analytic
where
pa_id=$1 ".$sql_from_poste.$sql_to_poste." order by po_name";
@@ -111,7 +230,7 @@ class Anc_Table extends Anc_Acc_Link
$r.= HtmlInput::hidden("card_poste",$this->card_poste);
$r.= $p_hidden;
$r.= dossier::hidden();
- $r.=HtmlInput::submit('bt_csv',"Export en CSV");
+ $r.=HtmlInput::submit('bt_csv',_("Export en CSV"));
$r.= '</form>';
return $r;
}
diff --git a/sql/upgrade.sql b/sql/upgrade.sql
index a623d02..4a342e2 100644
--- a/sql/upgrade.sql
+++ b/sql/upgrade.sql
@@ -19,133 +19,5 @@ ALTER TABLE operation_analytique ADD COLUMN f_id bigint;
ALTER TABLE operation_analytique ADD CONSTRAINT
operation_analytique_fiche_id_fk FOREIGN KEY (f_id) REFERENCES fiche
(f_id) MATCH SIMPLE ON UPDATE cascade ON cascade;
COMMENT ON COLUMN operation_analytique.f_id IS 'FK to fiche.f_id , used only
with ODS';
-CREATE OR REPLACE FUNCTION comptaproc.table_analytic_account(p_from text, p_to
text)
- RETURNS SETOF anc_table_account_type
- LANGUAGE plpgsql
-AS $function$
-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 oa1.oa_debit = true THEN oa1.oa_amount * (-1)::numeric
- ELSE oa1.oa_amount
- END) AS sum_amount, coalesce(jrnx.j_poste,fd1.ad_value) as j_poste,
tmp_pcmn.pcm_lib AS name
- FROM operation_analytique as oa1
- JOIN poste_analytique po USING (po_id)
- left join fiche_detail as fd1 on (oa1.f_id=fd1.f_id and fd1.ad_id=5)
- left JOIN jrnx USING (j_id)
- join tmp_pcmn ON (jrnx.j_poste::text = tmp_pcmn.pcm_val::text or
tmp_pcmn.pcm_val=fd1.ad_value)
-'|| sResult ||'
- GROUP BY po.po_id, po.po_name, po.pa_id,
coalesce(jrnx.j_poste,fd1.ad_value), tmp_pcmn.pcm_lib, po.po_description
- HAVING sum(
-CASE
- WHEN oa1.oa_debit = true THEN oa1.oa_amount * (-1)::numeric
- ELSE oa1.oa_amount
-END) <> 0::numeric
-'
- loop
- return next ret;
-end loop;
-end;
-$function$
-;
-
-
-CREATE OR REPLACE FUNCTION comptaproc.table_analytic_card(p_from text, p_to
text)
- RETURNS SETOF anc_table_card_type
- LANGUAGE plpgsql
-AS $function$
-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 '
-with m as (select po_id,
- coalesce(jrnx.f_id,operation_analytique.f_id) as f_id1,
- case when jrnx.j_qcode is not null then
- ( SELECT fiche_detail.ad_value
- FROM fiche_detail
- WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id = jrnx.f_id)
- when jrnx.f_id is null and operation_analytique.f_id is not null
then
- ( SELECT fiche_detail.ad_value
- FROM fiche_detail
- WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id =
operation_analytique.f_id)
- end
- AS name,
- case when jrnx.j_qcode is not null then
- jrnx.j_qcode
- when jrnx.f_id is null then
- (SELECT fiche_detail.ad_value
- FROM fiche_detail
- WHERE fiche_detail.ad_id = 23 AND fiche_detail.f_id =
operation_analytique.f_id) end as j_qcode
- FROM operation_analytique
- left JOIN jrnx USING (j_id) )
-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,
- m.f_id1,
- m.name,
- m.j_qcode
- FROM operation_analytique
- JOIN poste_analytique po USING (po_id)
- join m using (po_id)
-'|| sResult ||'
- GROUP BY po.po_id, po.po_name, po.pa_id, m.f_id1, m.j_qcode,m.name,
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;
-$function$
-;
\ No newline at end of file
+CREATE OR REPLACE FUNCTION comptaproc.table_analytic_account(text,text);
+CREATE OR REPLACE FUNCTION comptaproc.table_analytic_card(text,text);
\ No newline at end of file
- [Noalyss-commit] [noalyss] 124/219: indentation, (continued)
- [Noalyss-commit] [noalyss] 124/219: indentation, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 21/219: Improve debugging and improve manage_table_sql element will be added at the right place Manage_Table_SQL can work also with a SQL query or a view Icon place can be modified, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 34/219: rename scenario files, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 31/219: Task #0001443: opérations rapprochées : bug quand on utilise des tva avec autoliquidation Documentation, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 63/219: Analytic : change the name and description in Ajax, improve esthetic, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 116/219: Task #0001297: Perfectionnement menu COMPTA/ADV/OPEN, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 56/219: Task #1469 : Les esperluettes ne passent pas dans les libellés pour «opération identique»., Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 189/219: Task #1506 Fiche : max longueur du poste comptable = 40, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 195/219: Task #001381 : favori de filtre , ajout des favoris de filtre dans les recherches., Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 71/219: Task #0001475: Todo note : il manque le bouton "Fermer", Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 146/219: ANC : ANC_TABLE replace PGPLSQL procedure by Php functions : Anc_Table:create_temp_account Anc_Table:create_temp_card,
Dany De Bontridder <=
- [Noalyss-commit] [noalyss] 102/219: task #0001386: Amélioration visuelle plan comptable - postes utilisés Ajout lien si poste comptable utilisé, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 32/219: Task #0001426: Distinction dans l'affichage des historiques des OD: libellé + fiche Ajout des qcodes pour l'historique par poste Ajout des postes comptables pour l'historique par Fiche Ajout des tiers Fait aussi pour les export PDF & CSV, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 149/219: HttpInput: protect against injection, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 47/219: Task #0001462: CA - apostrophe non affichée Use of ManageTableSQL for improving adding , update and delete analytic accounting, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 29/219: Task #0001443: opérations rapprochées : bug quand on utilise des tva avec autoliquidation Esthetique show the autoreversed VAT, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 204/219: Bug : when pressing on the template button, the input field to focus is not diplaid and the javascript gives an exception, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 206/219: typo, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 88/219: Task #0001481: Encodage Vente, Achat, Fin ou OD 1. S'il n'y a qu'une seule catégorie de fiche , il n'est plus nécessaire d'afficher le choix (puisqu'il n'y en a qu'un) 2. Avoir pour chaque ligne un bouton ajout fiche, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 212/219: Esthetic : show ledger : width of the box, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 179/219: Fix ID problem, Dany De Bontridder, 2017/12/18