noalyss-commit
[Top][All Lists]
Advanced

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[Noalyss-commit] [noalyss] 34/86: RECONCILE : fix bug : sum incorrect if


From: dwm
Subject: [Noalyss-commit] [noalyss] 34/86: RECONCILE : fix bug : sum incorrect if several items
Date: Thu, 19 Sep 2024 02:41:51 -0400 (EDT)

sparkyx pushed a commit to branch unstable
in repository noalyss.

commit 72243dd3cb3ee2a35059503904618dacbc8dcad8
Author: sparkyx <danydb@noalyss.eu>
AuthorDate: Thu Sep 5 21:20:08 2024 +0200

    RECONCILE : fix bug : sum incorrect if several items
---
 include/class/acc_reconciliation.class.php         | 23 ++++---
 include/constant.php                               |  2 +-
 include/sql/patch/upgrade201.sql                   | 73 ++++++++++++++++++++++
 include/template/impress_reconciliation.php        |  6 +-
 include/template/impress_reconciliation_detail.php | 11 ++--
 5 files changed, 98 insertions(+), 17 deletions(-)

diff --git a/include/class/acc_reconciliation.class.php 
b/include/class/acc_reconciliation.class.php
index 6b4cf8439..2a318efb7 100644
--- a/include/class/acc_reconciliation.class.php
+++ b/include/class/acc_reconciliation.class.php
@@ -282,6 +282,7 @@ j1.j_poste as poste
 
         $array=$this->db->get_array("select distinct jr_id,jr_date from jrn 
where $filter_date and $sql_jrn and jr_id not in (select jr_id from jrn_rapt 
union select jra_concerned from jrn_rapt) order by jr_date");
         $ret=array();
+        \Noalyss\Dbg::echo_var(1, $this->db->sql);
         for ($i=0;$i<count($array);$i++)
         {
             $this->jr_id=$array[$i]['jr_id'];
@@ -463,7 +464,7 @@ j1.j_poste as poste
         }
     }
     /**
-     * Export to CSV
+     * @brief Export to CSV
      * @param type $p_choice 
      * 
      * @note must be set before calling
@@ -595,7 +596,7 @@ j1.j_poste as poste
     }
 
     /**
-     * 
+     * @brief retrieve data
      * @param type $p_choice
      *       - 0 : operation reconcilied
      *       - 1 : reconcilied with different amount
@@ -633,7 +634,7 @@ j1.j_poste as poste
         $seen=1;
     }
     /**
-     * Retrieve the amount VAT included and autoreversed VAT excluded thanks
+     * @brief Retrieve the amount VAT included and autoreversed VAT excluded 
thanks
      * the view v_quant_detail and return it.
      * If the operation is not a sale or a purchase , it doesn't exist in the
      * view then the function just returns the default amount
@@ -648,15 +649,19 @@ j1.j_poste as poste
             $this->prepare_query_detail_quant();
             $p=1;
         }
-        
+        bcscale(2);
         $retdb=$this->db->execute("detail_quant",array($p_jrn_id));
-        if ( Database::num_row($retdb) != 0)
+        $nb_record=Database::num_row($retdb);
+        if ( $nb_record > 0)
         {
+            $total_price=$first_amount=0;
+            for ($i=0;$i<$nb_record;$i++) {
             // then second_amount takes in account the vat_sided
-            $row=Database::fetch_array($retdb, 0);
-            $total_price=bcadd($row['price'],$row['vat_amount']);
-            $total_price=bcsub($total_price,$row['vat_sided']);
-            $first_amount=$total_price;
+                $row=Database::fetch_array($retdb, $i);
+                $total_price=bcadd($row['price'],$row['vat_amount']);
+                $total_price=bcsub($total_price,$row['vat_sided']);
+                $first_amount=bcadd($total_price,$first_amount);
+            }
 
         } else {
             // else take the amount from jrn
diff --git a/include/constant.php b/include/constant.php
index fa0cbef37..be44cfb82 100644
--- a/include/constant.php
+++ b/include/constant.php
@@ -28,7 +28,7 @@ global $version_noalyss;
 define('NOALYSS_VERSION', 9300 );
 
 // Database schema version 
-define("DBVERSION", 201);
+define("DBVERSION", 202);
 
 // version for MONO_DATABASE
 define("MONO_DATABASE", 25);
diff --git a/include/sql/patch/upgrade201.sql b/include/sql/patch/upgrade201.sql
new file mode 100644
index 000000000..a688665be
--- /dev/null
+++ b/include/sql/patch/upgrade201.sql
@@ -0,0 +1,73 @@
+begin;
+
+drop VIEW public.v_quant_detail;
+
+CREATE OR REPLACE VIEW public.v_quant_detail
+AS  WITH quant AS (
+    SELECT quant_purchase.j_id,
+           quant_purchase.qp_fiche AS fiche_id,
+           quant_purchase.qp_supplier AS tiers,
+           quant_purchase.qp_vat AS vat_amount,
+           quant_purchase.qp_price AS price,
+           quant_purchase.qp_vat_code AS vat_code,
+           quant_purchase.qp_dep_priv AS dep_priv,
+           quant_purchase.qp_nd_tva AS nd_tva,
+           quant_purchase.qp_nd_tva_recup AS nd_tva_recup,
+           quant_purchase.qp_nd_amount AS nd_amount,
+           quant_purchase.qp_vat_sided AS vat_sided
+    FROM quant_purchase
+    UNION ALL
+    SELECT quant_sold.j_id,
+           quant_sold.qs_fiche,
+           quant_sold.qs_client,
+           quant_sold.qs_vat,
+           quant_sold.qs_price,
+           quant_sold.qs_vat_code,
+           0,
+           0,
+           0,
+           0,
+           quant_sold.qs_vat_sided
+    FROM quant_sold
+),
+         sum_jrn as(
+             select jrn2.jr_id,
+                    quant2.tiers,
+                    sum(quant2.price) AS price,
+                    quant2.vat_code,
+                    sum(quant2.vat_amount) AS vat_amount,
+                    sum(quant2.dep_priv) AS dep_priv,
+                    sum(quant2.nd_tva) AS nd_tva,
+                    sum(quant2.nd_tva_recup) AS nd_tva_recup,
+                    sum(quant2.nd_amount) AS nd_amount,
+                    sum(quant2.vat_sided) as vat_sided
+             from jrn jrn2
+                      JOIN jrnx ON jrnx.j_grpt = jrn2.jr_grpt_id
+                      JOIN quant quant2 USING (j_id)
+             group by quant2.tiers,jrn2.jr_id,quant2.vat_code
+         )
+    SELECT jrn.jr_id,
+           sum_jrn.tiers,
+           sum_jrn.price,
+           sum_jrn.vat_code,
+           sum_jrn.vat_amount,
+           sum_jrn.dep_priv,
+           sum_jrn.nd_tva,
+           sum_jrn.nd_tva_recup,
+           sum_jrn.nd_amount,
+           sum_jrn.vat_sided,
+           jrn_def.jrn_def_name,
+           jrn_def.jrn_def_type,
+           vw_fiche_name.name,
+           jrn.jr_comment,
+           jrn.jr_montant,
+           tva_rate.tva_label
+    FROM jrn
+             join sum_jrn on sum_jrn.jr_id=jrn.jr_id
+             LEFT JOIN vw_fiche_name ON sum_jrn.tiers = vw_fiche_name.f_id
+             JOIN jrn_def ON jrn_def.jrn_def_id = jrn.jr_def_id
+             JOIN tva_rate ON tva_rate.tva_id = sum_jrn.vat_code
+;
+
+insert into version (val,v_description) values (202,'Bug with reconcile, 
improve view');
+commit;
\ No newline at end of file
diff --git a/include/template/impress_reconciliation.php 
b/include/template/impress_reconciliation.php
index d0c7d6939..4de4fe761 100644
--- a/include/template/impress_reconciliation.php
+++ b/include/template/impress_reconciliation.php
@@ -35,9 +35,9 @@ for ($i=0;$i<count($array);$i++) {
        $r.=td($array[$i]['first']['jr_comment']);
        $r.=td(nbm($tot),'style="text-align:right"');
        echo tr($r);
-        // check if operation does exist in v_detail_quant
-        
$ret=$acc_reconciliation->db->execute('detail_quant',array($array[$i]['first']['jr_id']));
-        $acc_reconciliation->show_detail($ret);
+    // check if operation does exist in v_detail_quant
+    
$ret=$acc_reconciliation->db->execute('detail_quant',array($array[$i]['first']['jr_id']));
+    $acc_reconciliation->show_detail($ret);
        if ( isset($array[$i]['depend']) )
        {
             $tot2=0;
diff --git a/include/template/impress_reconciliation_detail.php 
b/include/template/impress_reconciliation_detail.php
index 9232b5f7f..81831f7e3 100644
--- a/include/template/impress_reconciliation_detail.php
+++ b/include/template/impress_reconciliation_detail.php
@@ -88,7 +88,9 @@ for ($i=0;$i < $nb_record;$i++)    :
 <?php
           endfor;
 ?>
-    <tfoot>
+    <tfoot >
+    <tr class="highlight">
+
     <td>
         Totaux
     </td>
@@ -99,7 +101,7 @@ for ($i=0;$i < $nb_record;$i++)    :
         <?php echo nbm($tot_cum_nd); ?>
     </td>
     <td>
-        
+
     </td>
     <td class="num">
         <?php echo nbm($tot_cum_vat); ?>
@@ -111,8 +113,9 @@ for ($i=0;$i < $nb_record;$i++)    :
         <?php echo nbm($tot_autoliquidation); ?>
     </td>
     <td class="num">
-        <?php 
+        <?php
         echo nbm($tot_cum_tvac); ?>
     </td>
+    </tr>
     </tfoot>
-</table>        
+</table>



reply via email to

[Prev in Thread] Current Thread [Next in Thread]