[Noalyss-commit] [noalyss] 111/162: Currency : ledger printing for FIN + detail operation

[Dany De Bontridder]

sparkyx pushed a commit to annotated tag E-4
in repository noalyss.

commit 3c93e7491ebfd461632681a72f3655614e70e442
Author: Dany De Bontridder <danydb@noalyss.eu>
AuthorDate: Thu Dec 6 10:50:16 2018 +0100

    Currency : ledger printing for FIN + detail operation
---
 include/class/acc_account_ledger.class.php | 53 +++++++++++++++---------------
 include/class/acc_ledger_fin.class.php     |  7 +---
 include/class/fiche.class.php              | 42 +++++++++++------------
 include/class/print_ledger_fin.class.php   |  2 +-
 include/export/export_fiche_detail_csv.php |  3 +-
 include/export/export_poste_detail_csv.php |  3 +-
 6 files changed, 51 insertions(+), 59 deletions(-)

diff --git a/include/class/acc_account_ledger.class.php 
b/include/class/acc_account_ledger.class.php
index f396d84..f588761 100644
--- a/include/class/acc_account_ledger.class.php
+++ b/include/class/acc_account_ledger.class.php
@@ -90,7 +90,9 @@ class Acc_Account_Ledger
                                  "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_debit, jr_internal,jr_pj_number ".
+                                  ",oc_amount,oc_vat_amount".
                                  " from jrnx left join jrn_def on 
jrn_def_id=j_jrn_def ".
+                                 "  left join operation_currency using (j_id) 
".
                                  " left join jrn on jr_grpt_id=j_grpt".
                                  " where j_poste=$1 and $periode ".
                                  " order by j_date",array($this->id));
@@ -162,7 +164,7 @@ class Acc_Account_Ledger
          }
         $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,
+         select j1.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,
@@ -177,24 +179,27 @@ class Acc_Account_Ledger
                                  ,p_exercice
                                  ,jrn_def_name
                                  ,jrn_def_code
-                                 ,(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.currency_rate
-                                    ,jrn.currency_id
-                                    ,(select cr_code_iso from currency where 
id=jrn.currency_id) as cr_code_iso
-                                    ,j_montant
-                          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)      
        
-                                  where j_poste=$1 and 
-                                  ( to_date($2,'DD.MM.YYYY') <= j_date and 
-                                    to_date($3,'DD.MM.YYYY') >= j_date )
+                                 ,(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.currency_rate
+            ,jrn.currency_id
+            ,(select cr_code_iso from currency where id=jrn.currency_id) as 
cr_code_iso
+            ,j_montant
+            ,sum_oc_amount as oc_amount
+            ,sum_oc_vat_amount as oc_vat_amount
+  from jrnx as j1
+    left join v_all_account_currency as va on (j1.j_id = va.j_id and 
j1.j_poste=va.j_poste) 
+          join jrn_def on (jrn_def_id=j_jrn_def )
+           join jrn on (jr_grpt_id=j_grpt)
+           join tmp_pcmn on (j1.j_poste=pcm_val)
+           join parm_periode on (p_id=jr_tech_per)              
+          where j1.j_poste=$1 and 
+          ( to_date($2,'DD.MM.YYYY') <= j_date and 
+            to_date($3,'DD.MM.YYYY') >= j_date )
                                   and $filter_sql  $sql_let 
                                   order by 
j_date,substring(jr_pj_number,'[0-9]+$') asc",array($this->id,$p_from,$p_to));
         $res_saldo = $this->db->exec_sql("select  
sum(deb_montant),sum(cred_montant) from 
@@ -469,13 +474,9 @@ class Acc_Account_Ledger
                 "<TD>".$tiers."</TD>".
              "<TD>".h($op['description'])."</TD>".
                     td($op['jr_optype']);
-            if ( $op['cr_code_iso'] != 'EUR' && $op['cr_code_iso'] != "")
-            {
-             echo        td($op['cr_code_iso']).
-                    
td(nbm(bcdiv($op['j_montant'],$op['currency_rate'])),'style="text-align:right;padding-left:10px;"');
-            } else{
-                echo td().td();
-            }
+             echo   td($op['cr_code_iso']).
+                    
td(nbm(bcadd($op['oc_amount'],$op['oc_vat_amount'],4)),'style="text-align:right;padding-left:10px;"');
+            
             echo 
              "<TD 
style=\"text-align:right;padding-left:10px;\">".nbm($op['deb_montant'])."</TD>".
              "<TD 
style=\"text-align:right;padding-left:10px;\">".nbm($op['cred_montant'])."</TD>".
diff --git a/include/class/acc_ledger_fin.class.php 
b/include/class/acc_ledger_fin.class.php
index d05d0c7..c8bd178 100644
--- a/include/class/acc_ledger_fin.class.php
+++ b/include/class/acc_ledger_fin.class.php
@@ -872,12 +872,7 @@ class Acc_Ledger_Fin extends Acc_Ledger
                                $acc_operation->qcode = $e_bank_account;
                                $j_id=$acc_operation->insert_jrnx();
                                 
-                                $operation_currency = new 
Operation_currency_SQL($this->db);
-                                $operation_currency->oc_amount=$amount_input;
-                                $operation_currency->oc_vat_amount=0;
-                                
$operation_currency->oc_price_unit=$amount_input;
-                                $operation_currency->j_id=$j_id;
-                                $operation_currency->insert();
+          
 
 
                                if (sql_string(${"e_other$i" . "_comment"}) == 
null)
diff --git a/include/class/fiche.class.php b/include/class/fiche.class.php
index 8cf6ac6..6f62be3 100644
--- a/include/class/fiche.class.php
+++ b/include/class/fiche.class.php
@@ -1251,8 +1251,9 @@ class Fiche
 
         $qcode=$this->strAttribut(ATTR_DEF_QUICKCODE);
         $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,".
+            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]+$'),j1.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,".
@@ -1260,19 +1261,22 @@ class Fiche
                                 " jr_optype,".
                                  "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,
+                                     (with cred as (select jl_id, 
sum(j_montant) as amount_cred from letter_cred left join jrnx as j3 on 
(j3.j_id=j1.j_id)  group by jl_id ),
+                                    deb as (select jl_id, sum(j_montant) as 
amount_deb from letter_deb left join jrnx as j2 on (j2.j_id = j1.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,
                                   jrn.currency_rate,
                                     jrn.currency_id,
                                     (select cr_code_iso from currency where 
id=jrn.currency_id) as cr_code_iso,
-                                    j_montant
-                                  from jrnx as j1 left join jrn_def on 
jrn_def_id=j_jrn_def ".
-                                 " left join jrn on jr_grpt_id=j_grpt".
+                                    j_montant,
+                                    sum_oc_amount as oc_amount,
+                                    sum_oc_vat_amount as oc_vat_amount
+                                  from jrnx as j1 left join jrn_def on 
jrn_def_id=j_jrn_def 
+                                  left join v_all_card_currency  as v1 on 
(v1.j_id=j1.j_id ) 
+                                  left join jrn on jr_grpt_id=j_grpt".
                                 " left join parm_periode on (p_id=jr_tech_per) 
".
                                  " where j_qcode=$1 and ".
                                  " ( to_date($2,'DD.MM.YYYY') <= j_date and ".
@@ -1533,13 +1537,8 @@ class Fiche
             "<TD>".h($op['description'])."</TD>".
                     td($op['jr_optype']);
             
-            if ( $op['cr_code_iso'] != 'EUR' && $op['cr_code_iso'] != "")
-            {
              echo   td($op['cr_code_iso']).
-                    
td(nbm(bcdiv($op['j_montant'],$op['currency_rate'])),'style="text-align:right;padding-left:10px;"');
-            } else{
-                echo td().td();
-            }
+                    
td(nbm(bcadd($op['oc_amount'],$op['oc_vat_amount'],4)),'style="text-align:right;padding-left:10px;"');
             echo "<TD 
style=\"text-align:right\">".nbm($op['deb_montant'])."</TD>".
              "<TD 
style=\"text-align:right\">".nbm($op['cred_montant'])."</TD>".
              td(nbm(abs($progress)).$side,'style="text-align:right"').
@@ -1677,12 +1676,11 @@ class Fiche
         if ( $p_cond != "") $p_cond=" and ".$p_cond;
         
         $sql = "
-              select sum(oc_amount) 
+              select sum(sum_oc_amount)
               from 
-              Operation_currency
-              join jrnx using (j_id) 
-              join jrn on (jr_grpt_id=j_grpt)  
-              where f_id=$1
+              v_all_card_currency
+              where 
+              f_id=$1
                 $p_cond";
         $val=$this->cn->get_value($sql,[$this->id]);
         
diff --git a/include/class/print_ledger_fin.class.php 
b/include/class/print_ledger_fin.class.php
index 9d0617d..1f3b82b 100644
--- a/include/class/print_ledger_fin.class.php
+++ b/include/class/print_ledger_fin.class.php
@@ -110,7 +110,7 @@ class Print_Ledger_Financial extends PDF
                     join jrnx using (j_id)
                     join jrn as jrn2 on (j_grpt=jrn2.jr_grpt_Id) 
                where 
-                    j_id in (select j_id from jrnx where 
j_grpt=jrn2.jr_grpt_id and j_debit='t')
+                    j_id in (select j_id from jrnx where 
j_grpt=jrn2.jr_grpt_id )
                     and  jr_id=$1
                     group by jr_id"
                 );
diff --git a/include/export/export_fiche_detail_csv.php 
b/include/export/export_fiche_detail_csv.php
index f3e87c3..60d740e 100644
--- a/include/export/export_fiche_detail_csv.php
+++ b/include/export/export_fiche_detail_csv.php
@@ -123,7 +123,6 @@ if ( ! isset ($_REQUEST['oper_detail']))
         $progress=bcadd($progress,$diff);
         $tot_deb=bcadd($tot_deb,$op['deb_montant']);
         $tot_cred=bcadd($tot_cred,$op['cred_montant']);
-        $currency_amount=bcdiv($op['j_montant'],$op['currency_rate']);
         $export->add($op['j_qcode']);
         $export->add($op['j_poste']);
         $export->add($op['j_date_fmt']);
@@ -135,7 +134,7 @@ if ( ! isset ($_REQUEST['oper_detail']))
         $export->add($op['description']);
         $export->add($op['jr_optype']);
         $export->add($op['cr_code_iso']);
-        $export->add($currency_amount,"number");
+        $export->add(bcadd($op['oc_amount'],$op['oc_vat_amount']),"number");
         $export->add($op['deb_montant'],"number");
         $export->add($op['cred_montant'],"number");
         $export->add(abs($progress),"number");
diff --git a/include/export/export_poste_detail_csv.php 
b/include/export/export_poste_detail_csv.php
index c335366..4a3887d 100644
--- a/include/export/export_poste_detail_csv.php
+++ b/include/export/export_poste_detail_csv.php
@@ -137,7 +137,6 @@ if ( ! isset ($_REQUEST['oper_detail']))
             $tot_cred=bcadd($tot_cred,$op['cred_montant']);
             $diff=bcsub($op['deb_montant'],$op['cred_montant']);
             $prog=bcadd($prog,$diff);
-            $currency_amount=bcdiv($op['j_montant'],$op['currency_rate']);
 
             $export->add($op['j_date_fmt']);
             $export->add($pos['pcm_val']);
@@ -151,7 +150,7 @@ if ( ! isset ($_REQUEST['oper_detail']))
             $export->add($op['description']);
             $export->add($op['jr_optype']);
             $export->add($op['cr_code_iso']);
-            $export->add($currency_amount,"number");
+            
$export->add(bcadd($op['oc_amount'],$op['oc_vat_amount']),"number");
             
             $export->add($op['deb_montant'],"number");
             $export->add($op['cred_montant'],"number");