[Noalyss-commit] [noalyss] 133/219: #0001414: LETTRAGE - montants différents non indiqués Les montants différents pour un lettrage sont marqués avec un triangle orange Changement des requêtes SQL , peut avoir un prob. de performance pour gros volume

[Dany De Bontridder]

sparkyx pushed a commit to branch master
in repository noalyss.

commit 8773676229cbee6f5ebfa951b403d054aaa8287e
Author: Dany De Bontridder <address@hidden>
Date:   Fri Nov 3 21:13:16 2017 +0100

    #0001414: LETTRAGE - montants différents non indiqués
    Les montants différents pour un lettrage sont marqués avec un triangle 
orange
    Changement des requêtes SQL , peut avoir un prob. de performance pour gros 
volume
---
 include/ajax/ajax_history.php              |  9 +++++----
 include/class/acc_account_ledger.class.php | 28 +++++++++++++++++++---------
 include/class/fiche.class.php              | 17 +++++++++++++----
 3 files changed, 37 insertions(+), 17 deletions(-)

diff --git a/include/ajax/ajax_history.php b/include/ajax/ajax_history.php
index e858501..1d9101e 100644
--- a/include/ajax/ajax_history.php
+++ b/include/ajax/ajax_history.php
@@ -79,7 +79,7 @@ if ( isset($_GET['f_id']))
         */
        if ($exercice->count() > 1 )
          {
-           $default=(isset($_GET['ex']))?$_GET['ex']:$year;
+           $default=$http->get("ex","number",$year);
            $dossier=dossier::id();
            if ( $div != 'popup')
              {
@@ -150,8 +150,9 @@ if ( isset($_REQUEST['pcm_val']))
         $array['to_periode']=$limit_periode[1]->last_day();
        if (isset($_GET['ex']))
          {
-           $limit_periode=$per->get_limit($_GET['ex']);
-           if ( $_GET['ex'] < $year)
+            $ex=$http->get("ex","number");
+           $limit_periode=$per->get_limit($ex);
+           if ( $ex < $year)
              $array['from_periode']=$limit_periode[0]->first_day();
            else
              $array['to_periode']=$limit_periode[1]->last_day();
@@ -162,7 +163,7 @@ if ( isset($_REQUEST['pcm_val']))
         */
        if ($exercice->count() > 1 )
          {
-           $default=(isset($_GET['ex']))?$_GET['ex']:$year;
+            $default=$http->get("ex","number",$year);
            $dossier=dossier::id();
            if ( $div != 'popup')
              {
diff --git a/include/class/acc_account_ledger.class.php 
b/include/class/acc_account_ledger.class.php
index 77fb0e7..ac24d2d 100644
--- a/include/class/acc_account_ledger.class.php
+++ b/include/class/acc_account_ledger.class.php
@@ -134,10 +134,10 @@ class Acc_Account_Ledger
         case 0:
                 break;
         case 1:
-            $sql_let=' and j_id in (select j_id from letter_cred union select 
j_id from letter_deb)';
+            $sql_let=' and j_id in (select j_id from letter_cred union all 
select j_id from letter_deb)';
             break;
         case '2':
-            $sql_let=' and j_id not in (select j_id from letter_cred union 
select j_id from letter_deb) ';
+            $sql_let=' and j_id not in (select j_id from letter_cred union all 
select j_id from letter_deb) ';
             break;
         }
        if ( $solded == 1)
@@ -160,25 +160,33 @@ class Acc_Account_Ledger
            if ( $this->db->count() == 0 ) return array();
            if ($r[0]['s_deb']==$r[0]['s_cred']) return array();
          }
-        $this->row=$this->db->get_array("select  
j_id,jr_id,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,j_date,
+        $this->row=$this->db->get_array("
+ with sqlletter as (select j_id,jl_id from letter_cred union all select j_id , 
jl_id from   letter_deb )
+ select  j_id,jr_id,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,j_date,
                                 j_qcode
                                  ,case when j_debit='t' then j_montant else 0 
end as deb_montant,
                                  case when j_debit='f' then j_montant else 0 
end as cred_montant,
                                   case when j_text is null or j_text = '' then 
jr_comment 
                                    else jr_comment||' '||j_text  end
                                  as description,jrn_def_name as jrn_name,
-                                 j_debit, jr_internal,jr_pj_number,
-                                 coalesce(comptaproc.get_letter_jnt(j_id),-1) 
as letter 
+                                 j_debit, jr_internal,jr_pj_number
+                                 ,(select distinct jl_id from sqlletter  where 
sqlletter.j_id=j1.j_id ) as letter 
                                  ,pcm_lib
-                                ,jr_tech_per
+                                                                ,jr_tech_per
                                  ,p_exercice
                                  ,jrn_def_name
                                  ,jrn_def_code
-                                  from jrnx 
+                                 ,(with cred as (select jl_id, sum(j_montant) 
as amount_cred from letter_cred left join jrnx using (j_id)  group by jl_id ),
+                                                                               
                deb as (select jl_id, sum(j_montant) as amount_deb from 
letter_deb left join jrnx using (j_id)   group by jl_id )
+                                                                               
                select amount_deb-amount_cred
+                                                                               
                from 
+                                                                               
                cred 
+                                                                               
                full  join deb using (jl_id) where jl_id=(select distinct jl_id 
from sqlletter  where sqlletter.j_id=j1.j_id  )) as delta_letter
+                                  from jrnx as j1
                                   join jrn_def on (jrn_def_id=j_jrn_def )
                                    join jrn on (jr_grpt_id=j_grpt)
                                    join tmp_pcmn on (j_poste=pcm_val)
-                                  join parm_periode on (p_id=jr_tech_per) 
+                                  join parm_periode on (p_id=jr_tech_per)      
        
                                   where j_poste=$1 and 
                                   ( to_date($2,'DD.MM.YYYY') <= j_date and 
                                     to_date($3,'DD.MM.YYYY') >= j_date )
@@ -361,6 +369,7 @@ class Acc_Account_Ledger
      */
     function HtmlTable($p_array=null,$let=0 , $from_div=0)
     {
+        
         if ( $p_array==null)$p_array=$_REQUEST;
         $this->get_name();
         list($array,$tot_deb,$tot_cred)=$this->get_row_date( 
$p_array['from_periode'],
@@ -405,10 +414,11 @@ class Acc_Account_Ledger
             $vw_operation = sprintf('<A class="detail" 
style="text-decoration:underline;color:red" 
HREF="javascript:modifyOperation(\'%s\',\'%s\')" >%s</A>', $op['jr_id'], 
dossier::id(), $op['jr_internal']);
             $let = '';
                        $html_let = "";
-                       if ($op['letter'] != -1)
+                       if ($op['letter'] != 0)
                        {
                                $let = strtoupper(base_convert($op['letter'], 
10, 36));
                                $html_let = 
HtmlInput::show_reconcile($from_div, $let);
+                                if ( $op['delta_letter'] != 0) $html_let='<img 
src="image/warning.png" style="height:12px"/>'.$html_let;
                        }
                        $tmp_diff=bcsub($op['deb_montant'],$op['cred_montant']);
 
diff --git a/include/class/fiche.class.php b/include/class/fiche.class.php
index 23d6049..ef1db2e 100644
--- a/include/class/fiche.class.php
+++ b/include/class/fiche.class.php
@@ -1167,15 +1167,23 @@ class Fiche
         }
 
         $qcode=$this->strAttribut(ATTR_DEF_QUICKCODE);
-        $this->row=$this->cn->get_array("select distinct 
substring(jr_pj_number,'[0-9]+$'),j_id,j_date,to_char(j_date,'DD.MM.YYYY') as 
j_date_fmt,j_qcode,".
+        $this->row=$this->cn->get_array("
+            with sqlletter as (select j_id,jl_id from letter_cred union all 
select j_id , jl_id from   letter_deb )
+                select distinct 
substring(jr_pj_number,'[0-9]+$'),j_id,j_date,to_char(j_date,'DD.MM.YYYY') as 
j_date_fmt,j_qcode,".
                                  "case when j_debit='t' then j_montant else 0 
end as deb_montant,".
                                  "case when j_debit='f' then j_montant else 0 
end as cred_montant,".
                                  " jr_comment as description,jrn_def_name as 
jrn_name,j_poste,".
                                 " jr_pj_number,".
-                                 "j_debit, 
jr_internal,jr_id,coalesce(comptaproc.get_letter_jnt(j_id),-1) as letter, ".
+                                 "j_debit, jr_internal,jr_id,(select distinct 
jl_id from sqlletter  where sqlletter.j_id=j1.j_id ) as letter , ".
                                 " jr_tech_per,p_exercice,jrn_def_name,
+                                     (with cred as (select jl_id, 
sum(j_montant) as amount_cred from letter_cred left join jrnx using (j_id)  
group by jl_id ),
+                                                                               
                deb as (select jl_id, sum(j_montant) as amount_deb from 
letter_deb left join jrnx using (j_id)   group by jl_id )
+                                                                               
                select amount_deb-amount_cred
+                                                                               
                from 
+                                                                               
                cred 
+                                                                               
                full  join deb using (jl_id) where jl_id=(select distinct jl_id 
from sqlletter  where sqlletter.j_id=j1.j_id  )) as delta_letter,
                                                                  jrn_def_code".
-                                 " from jrnx left join jrn_def on 
jrn_def_id=j_jrn_def ".
+                                 " from jrnx as j1 left join jrn_def on 
jrn_def_id=j_jrn_def ".
                                  " left join jrn on jr_grpt_id=j_grpt".
                                 " left join parm_periode on (p_id=jr_tech_per) 
".
                                  " where j_qcode=$1 and ".
@@ -1384,10 +1392,11 @@ class Fiche
             $vw_operation = sprintf('<A class="detail" 
style="text-decoration:underline;color:red" 
HREF="javascript:modifyOperation(\'%s\',\'%s\')" >%s</A>', $op['jr_id'], 
dossier::id(), $op['jr_internal']);
             $let = '';
             $html_let = "";
-            if ($op['letter'] != -1)
+            if ($op['letter'] != "")
             {
                     $let = strtoupper(base_convert($op['letter'], 10, 36));
                     $html_let = HtmlInput::show_reconcile($from_div, $let);
+                     if ( $op['delta_letter'] != 0) $html_let='<img 
src="image/warning.png" style="height:12px"/>'.$html_let;
             }
             $tmp_diff=bcsub($op['deb_montant'],$op['cred_montant']);