[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>
- [Noalyss-commit] [noalyss] 41/86: Bug : some db doesn't have the view v_quant_detail, (continued)
- [Noalyss-commit] [noalyss] 41/86: Bug : some db doesn't have the view v_quant_detail, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 42/86: Improve : creation db , mod1 , mod2 and account_repository remove the alter table disable all triggers, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 03/86: Cosmetic: without z-index, the class col is behind the autocomplete div in COMPANY, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 35/86: RECONCILE : VAT ND not in account, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 31/86: RECONCILE : fix bug : sum incorrect if several items, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 07/86: C0OPT1 , C1MENU : improve doc, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 05/86: C0OPT1 , C1MENU : improve doc, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 23/86: Merge branch 'pre-stable' into stable, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 28/86: Cosmetic improve widget bookmark color for hover, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 19/86: Cosmetic, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 34/86: RECONCILE : fix bug : sum incorrect if several items,
dwm <=
- [Noalyss-commit] [noalyss] 53/86: Merge branch 'stable' into unstable, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 59/86: Set the DB to the version 202 for NOALYSS9.3 correct make-sql, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 66/86: Cosmetic , missing bcscale and code cleaning, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 56/86: Merge branch 'stable' into pre-stable, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 75/86: CSS font h2, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 78/86: add detail, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 80/86: Cosmetic : animate bar menu, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 84/86: color, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 11/86: PHP8.2 compatility : strip_tags null, dwm, 2024/09/19
- [Noalyss-commit] [noalyss] 20/86: C0MENU : bug javascript Uniquement note de débit ou crédit ne fonctionne pas, dwm, 2024/09/19