[Noalyss-commit] [noalyss] 110/162: Currency : purchase oneline + extended html and csv

[Dany De Bontridder]

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

commit 855a853bccb6d15091461f9c07659b79b057f09f
Author: Dany De Bontridder <danydb@noalyss.eu>
AuthorDate: Wed Dec 5 17:46:19 2018 +0100

    Currency : purchase oneline + extended html and csv
---
 .../class/acc_ledger_history_purchase.class.php    | 33 +++++++++++-
 include/class/acc_ledger_purchase.class.php        |  4 ++
 .../acc_ledger_history_purchase_extended.php       | 13 +++--
 .../acc_ledger_history_purchase_oneline.php        | 15 ++++--
 sql/upgrade.sql                                    | 58 +++++++++++++++++++++-
 5 files changed, 113 insertions(+), 10 deletions(-)

diff --git a/include/class/acc_ledger_history_purchase.class.php 
b/include/class/acc_ledger_history_purchase.class.php
index 9db2140..da0aa67 100644
--- a/include/class/acc_ledger_history_purchase.class.php
+++ b/include/class/acc_ledger_history_purchase.class.php
@@ -132,7 +132,14 @@ class Acc_Ledger_History_Purchase extends 
Acc_Ledger_History
                 (select ad_value from fiche_detail where ad_id=32 and 
f_id=x.f_id) as first_name,
                 (select ad_value from fiche_detail where ad_id=23 and 
f_id=x.f_id) as qcode
               from 
-              fiche as x)
+              fiche as x)  ,
+              row_currency as (
+                select sum(oc_amount) as sum_oc_amount,sum(oc_vat_amount) as 
sum_oc_vat_amount,jrnx.j_grpt
+                from 
+                    operation_currency
+                    join jrnx using (j_id)
+                group by j_grpt
+              )
             select   
                     name,
                     first_name,
@@ -152,12 +159,20 @@ class Acc_Ledger_History_Purchase extends 
Acc_Ledger_History
                     noded_vat,
                     private_amount,
                     novat+vat-tva_sided as tvac,
-                    n_text
+                    n_text,
+                    jrn.currency_id,
+                    jrn.currency_rate,
+                    jrn.currency_rate_ref,
+                    sum_oc_amount,
+                    sum_oc_vat_amount,
+                    cr_code_iso
             from
                 jrn
                 join row_purchase on (qp_internal=jr_internal)
                 join supplier_detail on (qp_supplier=f_id)
                 left join jrn_note using (jr_id)
+                left join row_currency as rc on (rc.j_grpt = jrn.jr_grpt_id)
+                left join currency as c on (c.id=jrn.currency_id)
             where
                 jr_def_id in ({$ledger_list})
                 and {$periode}
@@ -268,6 +283,11 @@ class Acc_Ledger_History_Purchase extends 
Acc_Ledger_History
             }
         }
         $title[]=_("TVAC/TTC");
+        $title[]=_("Devise");
+        $title[]=_("Devise HTVA");
+        $title[]=_("Devise TVA");
+        $title[]=_("Taux ref");
+        $title[]=_("Taux utilisé");
         $title[]=_("opérations liées");
         $export->write_header($title);
         
@@ -309,6 +329,15 @@ class Acc_Ledger_History_Purchase extends 
Acc_Ledger_History
             }
             $export->add($line['tvac'],"number");
             /**
+             * Add currency info
+             */
+            $export->add($line['cr_code_iso']);
+            $export->add($line['sum_oc_amount'],'number');
+            $export->add($line['sum_oc_vat_amount'],'number');
+            $export->add($line['currency_rate'],'number');
+            $export->add($line['currency_rate_ref'],'number');
+            
+            /**
              * Retrieve payment if any
              */
              
$ret_reconcile=$this->db->execute('reconcile_date',array($line['jr_id']));
diff --git a/include/class/acc_ledger_purchase.class.php 
b/include/class/acc_ledger_purchase.class.php
index 9a0b74d..2d19e04 100644
--- a/include/class/acc_ledger_purchase.class.php
+++ b/include/class/acc_ledger_purchase.class.php
@@ -1977,6 +1977,10 @@ EOF;
         $array['htva'] = _('HTVA Opération');
         $array['tot_vat'] = _('TVA Opération');
         $array['tot_tva_np'] = _('TVA NP opération');
+        $array['oc_amount'] = _('Mont. Devise');
+        $array['oc_vat_amount'] = _('Mont. TVA Devise');
+        $array['cr_code_iso'] = _('Devise');
+        
         return $array;
     }
 
diff --git a/include/template/acc_ledger_history_purchase_extended.php 
b/include/template/acc_ledger_history_purchase_extended.php
index 8f7b4ee..a009428 100644
--- a/include/template/acc_ledger_history_purchase_extended.php
+++ b/include/template/acc_ledger_history_purchase_extended.php
@@ -45,13 +45,16 @@
         <th>
             <?=_('Description')?>
         </th>
-        <th>
+        <th class="num">
+            <?=_('Devise TVAC')?>
+        </th>
+        <th  class="num">
             <?=_('HTVA')?>
         </th>
-        <th>
+        <th  class="num">
             <?=_('TVA')?>
         </th>
-        <th>
+        <th  class="num">
             <?=_('TVAC')?>
         </th>
     </tr>
@@ -87,6 +90,10 @@ for ($i=0;$i<$nb_data;$i++):
             <?=h($this->data[$i]['jr_comment'])?>
         </td>
         <td class="num">
+            
<?=nbm(bcadd($this->data[$i]['sum_oc_amount'],$this->data[$i]['sum_oc_vat_amount'],4),4)?>
+            <?=$this->data[$i]['cr_code_iso']?>
+        </td>
+        <td class="num">
             <?=nbm($this->data[$i]['novat'])?>
         </td>
         <td class="num">
diff --git a/include/template/acc_ledger_history_purchase_oneline.php 
b/include/template/acc_ledger_history_purchase_oneline.php
index f60544d..8b941ab 100644
--- a/include/template/acc_ledger_history_purchase_oneline.php
+++ b/include/template/acc_ledger_history_purchase_oneline.php
@@ -49,19 +49,22 @@ if (!defined('ALLOWED'))
         <th>
             <?=_('Description')?>
         </th>
-        <th>
+        <th class="num">
             <?=_('HTVA')?>
         </th>
-        <th>
+        <th class="num">
             <?=_('Non ded')?>
         </th>
-        <th>
+        <th class="num">
             <?=_('TVA')?>
         </th>
               
-        <th>
+        <th class="num">
             <?=_('TVAC')?>
         </th>
+        <th class="num">
+            <?=_('Devise')?>
+        </th>
     </tr>
 <?php 
 $nb_data=count($this->data);
@@ -113,6 +116,10 @@ for ($i=0;$i<$nb_data;$i++):
         <td class="num">
             <?=nbm($this->data[$i]['tvac'])?>
         </td>
+        <td class="num">
+            <?=nbm ( 
bcadd($this->data[$i]['sum_oc_amount'],$this->data[$i]['sum_oc_vat_amount']),4)?>
+            <?=$this->data[$i]['cr_code_iso']?>
+        </td>
         <td>
             
         <?php
diff --git a/sql/upgrade.sql b/sql/upgrade.sql
index 5339f75..40ac273 100644
--- a/sql/upgrade.sql
+++ b/sql/upgrade.sql
@@ -49,4 +49,60 @@ WITH m AS (
      JOIN tva_rate ON quant_sold.qs_vat_code = tva_rate.tva_id
      JOIN m ON m.jr_id = jrn.jr_id
      left join operation_currency as oc on (oc.j_id=jrnx.j_id)
-;
\ No newline at end of file
+;
+
+CREATE OR REPLACE VIEW public.v_detail_purchase
+AS WITH m AS (
+         SELECT sum(quant_purchase_1.qp_price) AS htva,
+            sum(quant_purchase_1.qp_vat) AS tot_vat,
+            sum(quant_purchase_1.qp_vat_sided) AS tot_tva_np,
+            jrn_1.jr_id
+           FROM quant_purchase quant_purchase_1
+             JOIN jrnx jrnx_1 USING (j_id)
+             JOIN jrn jrn_1 ON jrnx_1.j_grpt = jrn_1.jr_grpt_id
+          GROUP BY jrn_1.jr_id
+        )
+ SELECT jrn.jr_id,
+    jrn.jr_date,
+    jrn.jr_date_paid,
+    jrn.jr_ech,
+    jrn.jr_tech_per,
+    jrn.jr_comment,
+    jrn.jr_pj_number,
+    jrn.jr_internal,
+    jrn.jr_def_id,
+    jrnx.j_poste,
+    jrnx.j_text,
+    jrnx.j_qcode,
+    quant_purchase.qp_fiche AS item_card,
+    a.name AS item_name,
+    quant_purchase.qp_supplier,
+    b.vw_name AS tiers_name,
+    b.quick_code,
+    tva_rate.tva_label,
+    tva_rate.tva_comment,
+    tva_rate.tva_both_side,
+    quant_purchase.qp_vat_sided AS vat_sided,
+    quant_purchase.qp_vat_code AS vat_code,
+    quant_purchase.qp_vat AS vat,
+    quant_purchase.qp_price AS price,
+    quant_purchase.qp_quantite AS quantity,
+    quant_purchase.qp_price / quant_purchase.qp_quantite AS price_per_unit,
+    quant_purchase.qp_nd_amount AS non_ded_amount,
+    quant_purchase.qp_nd_tva AS non_ded_tva,
+    quant_purchase.qp_nd_tva_recup AS non_ded_tva_recup,
+    m.htva,
+    m.tot_vat,
+    m.tot_tva_np,
+    oc.oc_amount,
+    oc.oc_vat_amount,
+    (select cr_code_iso from currency where jrn.currency_id=currency.id) as 
cr_code_iso
+   FROM jrn
+     JOIN jrnx ON jrn.jr_grpt_id = jrnx.j_grpt
+     JOIN quant_purchase USING (j_id)
+     JOIN vw_fiche_name a ON quant_purchase.qp_fiche = a.f_id
+     JOIN vw_fiche_attr b ON quant_purchase.qp_supplier = b.f_id
+     JOIN tva_rate ON quant_purchase.qp_vat_code = tva_rate.tva_id
+     JOIN m ON m.jr_id = jrn.jr_id
+     left join operation_currency as oc on (oc.j_id=jrnx.j_id)
+;