[Noalyss-commit] [noalyss] 107/218: Currency : export PDF and CSV with currency for printing financial ledger listing(oneline)

[Dany De Bontridder]

sparkyx pushed a commit to branch entreprise
in repository noalyss.

commit 18bb3ec046d737aff5cce5b8efddcdafed657e98
Author: Dany De Bontridder <address@hidden>
Date:   Mon Dec 3 13:53:30 2018 +0100

    Currency : export PDF and CSV with currency for printing financial ledger 
listing(oneline)
---
 include/class/acc_ledger.class.php                 |  1 -
 .../class/acc_ledger_history_financial.class.php   | 10 ++++-
 include/class/acc_ledger_history_generic.class.php | 29 +++++++++++--
 include/class/print_ledger_fin.class.php           | 48 ++++++++++++++++------
 include/constant.php                               |  5 ++-
 include/export/export_ledger_csv.php               |  7 +++-
 .../acc_ledger_history_financial_oneline.php       | 18 +++++++-
 7 files changed, 96 insertions(+), 22 deletions(-)

diff --git a/include/class/acc_ledger.class.php 
b/include/class/acc_ledger.class.php
index 4ac0fbd..40addb8 100644
--- a/include/class/acc_ledger.class.php
+++ b/include/class/acc_ledger.class.php
@@ -1753,7 +1753,6 @@ class Acc_Ledger extends jrn_def_sql
      * @brief retrieve operation from  jrn
      * @param $p_from periode (id)
      * @param $p_to periode (id)
-     * @return Anc_Plan array
      */
     function get_operation($p_from, $p_to)
     {
diff --git a/include/class/acc_ledger_history_financial.class.php 
b/include/class/acc_ledger_history_financial.class.php
index 9b5a1d9..23f6443 100644
--- a/include/class/acc_ledger_history_financial.class.php
+++ b/include/class/acc_ledger_history_financial.class.php
@@ -124,12 +124,20 @@ class Acc_Ledger_History_Financial extends 
Acc_Ledger_History
                     jr_internal,
                     jrn.jr_comment,
                     jr_pj_name,
-                    qf_amount
+                    qf_amount,
+                    currency_id,
+                    currency_rate,
+                    currency_rate_ref,
+                    oc_amount,
+                    oc_vat_amount,
+                    currency.cr_code_iso
             from
                 jrn
                 join quant_fin using (jr_id)
                 join detail as tiers on (tiers.f_id=qf_other) 
                 join detail as bk on (bk.f_id=qf_bank) 
+                left join operation_currency using(j_id)
+                join currency on (currency.id=jrn.currency_id)
             where
                 jr_def_id in ({$ledger_list})
                 and {$periode}
diff --git a/include/class/acc_ledger_history_generic.class.php 
b/include/class/acc_ledger_history_generic.class.php
index 15bd2e6..95729ca 100644
--- a/include/class/acc_ledger_history_generic.class.php
+++ b/include/class/acc_ledger_history_generic.class.php
@@ -433,8 +433,25 @@ class Acc_Ledger_History_Generic extends Acc_Ledger_History
              jrn.jr_grpt_id as grpt_id,
              jrn.jr_pj_name as pj,
              jrn_def_type,
-             jrn.jr_tech_per
+             jrn.jr_tech_per,
+             jrn.currency_id,
+             jrn.currency_rate,
+             jrn.currency_rate_ref,
+             currency.cr_code_iso,
+             coalesce(sum_ocamount,0) as sum_ocamount,
+             coalesce(sum_ocvat_amount,0) as sum_ocvat_amount
              FROM jrn join jrn_def on (jrn_def_id=jr_def_id)
+             join currency on (currency.id=jrn.currency_id)
+             left join (
+               select jrn2.jr_id , sum(coalesce(oc_amount,0)) as 
sum_ocamount,sum(coalesce(oc_vat_amount,0)) as sum_ocvat_amount
+                       from operation_currency
+                               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'
+                               group by jr_id
+               ) as OC1 using (jr_id)
              WHERE $periode and $jrn order by 
jr_date,substring(jrn.jr_pj_number,'[0-9]+$')::numeric asc  $cond_limite";
         $Res=$this->db->exec_sql($sql);
         $Max=Database::num_row($Res);
@@ -503,9 +520,13 @@ class Acc_Ledger_History_Generic extends Acc_Ledger_History
                                      j_qcode,
                                      jrn_def_type,
                                      jr_rapt as oc, j_tech_per as periode,
-                                     j_id
-                                     from jrnx left join jrn on 
-                   jr_grpt_id=j_grpt 
+                                     j_id,
+                                     currency_id,
+                                     currency_rate,
+                                     currency_rate_ref
+                                     from jrnx 
+                                     join jrn on  (jr_grpt_id=j_grpt )
+                                     left join operation_currency using (j_id)
                    left join tmp_pcmn on pcm_val=j_poste 
                    join jrn_def on (jrn_def_id=jr_def_id)
                     where j_jrn_def in (".$ledger_list.") 
diff --git a/include/class/print_ledger_fin.class.php 
b/include/class/print_ledger_fin.class.php
index bfa141c..9d0617d 100644
--- a/include/class/print_ledger_fin.class.php
+++ b/include/class/print_ledger_fin.class.php
@@ -56,12 +56,13 @@ class Print_Ledger_Financial extends PDF
         $this->Cell(40,6,nbm($this->rap_amount),0,0,'R');
         $this->Ln(6);
         $this->SetFont('DejaVu', 'B', 7);
-        $this->Cell(15,6,'Piece');
-        $this->Cell(10,6,'Date');
-        $this->Cell(15,6,'Interne');
-        $this->Cell(40,6,'Dest/Orig');
-        $this->Cell(80,6,'Commentaire');
-        $this->Cell(20,6,'Montant');
+        $this->Cell(15,6,_('Piece'));
+        $this->Cell(10,6,_('Date'));
+        $this->Cell(10,6,_('Interne'));
+        $this->Cell(40,6,_('Dest/Orig'));
+        $this->Cell(60,6,_('Commentaire'));
+        $this->Cell(20,6,_('Device'),0,0,'R');
+        $this->Cell(20,6,_('Montant'),0,0,'R');
         $this->Ln(6);
         
     }
@@ -92,24 +93,47 @@ class Print_Ledger_Financial extends PDF
      */
     function export()
     {
-        $a_jrn=$this->ledger->get_operation($_GET['from_periode'],
-                                            $_GET['to_periode']);
+        $http=new HttpInput();
+        
$a_jrn=$this->ledger->get_operation($http->get('from_periode',"number"),
+                                            $http->get('to_periode','number'));
         $this->SetFont('DejaVu', '', 6);
         if ( $a_jrn == null ) return;
         bcscale(2);
+        $this->ledger->load();
+        $currency=$this->ledger->get_currency()->get_code();
+        $currency_id=$this->ledger->get_currency()->get_id();
+        $this->cn->prepare("amount_cur",
+                "select jrn2.jr_id , 
+                    sum(coalesce(oc_amount,0)) as sum_ocamount,
+                    sum(coalesce(oc_vat_amount,0)) as sum_ocvat_amount
+               from operation_currency
+                    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')
+                    and  jr_id=$1
+                    group by jr_id"
+                );
         for ( $i=0;$i<count($a_jrn);$i++)
         {
             $row=$a_jrn[$i];
             $this->write_cell(15,5,$row['pj']); 
             $this->write_cell(10,5,$row['date_fmt']);
-            $this->write_cell(15,5,$row['internal']);
+            $this->write_cell(10,5,$row['internal']);
 
             $name=$this->ledger->get_tiers($this->jrn_type,$row['id']);
-            $this->write_cell(40,5,$name,0,'L');
+            $this->write_cell(40,5,$name,0,0,'L');
 
-
-            $this->LongLine(80,5,$row['comment'],0,'L');
+            $this->LongLine(60,5,$row['comment'],0,'L');
             $amount=$this->cn->get_value('select qf_amount from quant_fin 
where jr_id=$1',array( $row['id']));
+            if ( $currency_id != 0) {
+                
$ret_amount_cur=$this->cn->execute("amount_cur",array($row['id']));
+                
+                if ( $this->cn->count($ret_amount_cur) == 1) {
+                    $amount_cur=Database::fetch_result($ret_amount_cur, 0,1);
+                    $this->write_cell(20,5,sprintf('%s 
%s',nbm($amount_cur),$currency),0,0,'R');
+                }
+            }
             $this->write_cell(20,5,sprintf('%s',nbm($amount)),0,0,'R');
             $this->line_new(5);
             $this->tp_amount=bcadd($this->tp_amount,$amount);
diff --git a/include/constant.php b/include/constant.php
index 34489ca..f025a83 100644
--- a/include/constant.php
+++ b/include/constant.php
@@ -271,7 +271,10 @@ if ( ! file_exists($ps2pdf) )
 /**
  * Outil pour manipuler les PDF 
  */
-$pdftk='/usr/bin/pdftk';
+if ( ! isset ($pdftk))
+{
+ $pdftk='/usr/bin/pdftk';
+}
 if (file_exists($pdftk))
 {
     define ('PDFTK',$pdftk);  
diff --git a/include/export/export_ledger_csv.php 
b/include/export/export_ledger_csv.php
index 7e43512..16b9e2d 100644
--- a/include/export/export_ledger_csv.php
+++ b/include/export/export_ledger_csv.php
@@ -213,6 +213,8 @@ if ($get_option=="L" && 
($jrn_type=='ODS'||$jrn_type=='FIN'||$jrn_type=='GL') )
     $title[]=_("commentaire");
     $title[]=_("internal");
     $title[]=_("montant");
+    $title[]=_("montant devise");
+    $title[]=_("devise");
     $export->write_header($title);
     foreach ($Row as $line)
     {
@@ -238,12 +240,15 @@ if ($get_option=="L" && 
($jrn_type=='ODS'||$jrn_type=='FIN'||$jrn_type=='GL') )
                     " where jr_id=$1", array($line['jr_id']));
 
             $export->add($positive, "number");
-            $export->add("");
+            //$export->add("");
         }
         else
         {
             $export->add($line['montant'], "number");
         }
+        //-- add currency
+       
$export->add(bcadd($line['sum_ocamount'],$line['sum_ocvat_amount']),"number");
+       $export->add($line['cr_code_iso']);
         //------ Add reconcilied operation ---------------
         $ret_reconcile=$cn->execute('reconcile_date_csv',
                 array($line['jr_id']));
diff --git a/include/template/acc_ledger_history_financial_oneline.php 
b/include/template/acc_ledger_history_financial_oneline.php
index bdcff15..10d2c81 100644
--- a/include/template/acc_ledger_history_financial_oneline.php
+++ b/include/template/acc_ledger_history_financial_oneline.php
@@ -30,10 +30,13 @@ if (!defined('ALLOWED'))
     <tr>
         <th><?= _('Date') ?></th>
         <th><?= _('Banque') ?></th>
+        <th><?= _("Interne") ?></th>
         <th><?=_('Pièce')?></th>
         <th><?= _("Tiers") ?></th>
         <th><?= _("Libellé") ?></th>
-        <th><?= _("Montant") ?></th>
+        <th class="num"><?= _("Montant") ?></th>
+        <th class="num"><?= _("M. Devise") ?></th>
+        <th><?= _("Devise") ?></th>
         <th><?= _("Opérations rapprochées") ?></th>
     </tr>
     <?php
@@ -50,6 +53,11 @@ if (!defined('ALLOWED'))
                 <?= $this->data[$i]['bk_qcode']; ?>
             </td>
             <td>
+                <?php
+                    echo HtmlInput::detail_op($this->data[$i]['jr_id'], 
$this->data[$i]['jr_internal']);
+                ?>
+            </td>
+            <td>
                 <?= $this->data[$i]['jr_pj_number']; ?>
             </td>
             <td>
@@ -61,9 +69,15 @@ if (!defined('ALLOWED'))
             <td>
                 <?= h($this->data[$i]['jr_comment']) ?>
             </td>
-            <td>
+            <td class="num">
                 <?= nbm($this->data[$i]['qf_amount']) ?>
             </td>
+            <td class="num">
+                <?=nbm($this->data[$i]['oc_amount'])?>
+            </td>
+            <td>
+                <?=$this->data[$i]['cr_code_iso']?>
+            </td>
             <td>
                 <?php
                 $ret_reconcile=$this->db->execute('reconcile_date',