[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Noalyss-commit] [noalyss] 133/219: #0001414: LETTRAGE - montants diffé
From: |
Dany De Bontridder |
Subject: |
[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 |
Date: |
Mon, 18 Dec 2017 13:22:50 -0500 (EST) |
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']);
- [Noalyss-commit] [noalyss] 202/219: ManageTable.js : change Manage.delete by Manage.remove, because delete is a reserved keyword, (continued)
- [Noalyss-commit] [noalyss] 202/219: ManageTable.js : change Manage.delete by Manage.remove, because delete is a reserved keyword, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 181/219: Fix ID problem, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 163/219: Code cleaning, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 213/219: Code rewrite : move the function HtmlInput::select_ledger into Acc_Ledger_Search::select_ledger, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 197/219: Task #001381 : enlève , ajout de la nouvelle liste dans la liste, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 183/219: Icon move / fix, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 211/219: Bug : in recherche the ledger choice is transparent, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 162/219: Constant add an exception code for duplicate, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 190/219: Database : get_value throw an error if SQL failed, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 30/219: Task #0001443: opérations rapprochées : bug quand on utilise des tva avec autoliquidation 1. Correct export CSV 2. Improve code : new function Acc_Reconciliation:get_amount_noautovat, Dany De Bontridder, 2017/12/18
- [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 <=
- [Noalyss-commit] [noalyss] 70/219: Task #0001475: VEN / ACH opération prédéfinie , il manque le bouton "Fermer", Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 131/219: Bug : correct catch clause , getMessage is wrong must be replaced by message, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 203/219: Task #0001507: Click sur détail opérations apparaît trop haut Fixed for IE8 : use documentElement.scrollTop, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 191/219: Database : improve message when exec_sql failed, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 185/219: Fix problem with invalid unicode sequence, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 178/219: NEW : add iselect , display a list with options, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 200/219: Task #0001507: Click sur détail opérations apparaît trop haut Use the function window.pageYOffset instead of window.scrollY, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 123/219: Anc ODS : check date is valid and comment not empty, remove debug, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 52/219: task #0001372: Backup - nom du fichier Bug modele.inc.php, Dany De Bontridder, 2017/12/18
- [Noalyss-commit] [noalyss] 55/219: Task #1469 Code Cleaning: remove Anc_Account Use a CSS with 2 fonts , one for the title and clickable item, and a normal one. Correct Anc_Account_Table::check duplicate for update and insert, Dany De Bontridder, 2017/12/18