[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]

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