[Noalyss-commit] [noalyss] 12/24: Additional Tax : input in purchase and sale ledger - tax with positive amount - tax with negative amount - tax currency - Reverse operation - Correct in operation detail the amount of linked operation

[Dany De Bontridder]

sparkyx pushed a commit to branch master
in repository noalyss.

commit 91f7c05de155daa4d6f3daa7623ce8a6e48a1e29
Author: sparkyx <danydb@noalyss.eu>
AuthorDate: Sun May 29 22:20:58 2022 +0200

    Additional Tax : input in purchase and sale ledger
    - tax with positive amount
    - tax with negative amount
    - tax currency
    - Reverse operation
    - Correct in operation detail the amount of linked operation
---
 include/class/acc_ledger.class.php        | 12 ++++++++----
 include/class/acc_ledger_search.class.php | 19 +++++++++++++------
 include/class/acc_operation.class.php     | 30 ++++++++++++++++++++++++++----
 include/template/ledger_detail_bottom.php | 10 +++++++++-
 4 files changed, 56 insertions(+), 15 deletions(-)

diff --git a/include/class/acc_ledger.class.php 
b/include/class/acc_ledger.class.php
index 4a52f2184..eda6c451b 100644
--- a/include/class/acc_ledger.class.php
+++ b/include/class/acc_ledger.class.php
@@ -210,6 +210,7 @@ class Acc_Ledger  extends jrn_def_sql
      *    - quant_purchase
      *    - stock
      *    - ANC
+     *    - jrn_tax
      * Add or update a note into jrn_note
      * @param $p_date is the date of the reversed op
      * @exception if date is invalid or other prob
@@ -259,10 +260,6 @@ class Acc_Ledger  extends jrn_def_sql
                 throw new Exception(_('PERIODE FERMEE')." $p_date ");
             }
 
-
-
-
-
             // Mark the operation invalid into the ledger
             // to avoid to nullify twice the same op., add or update a note 
into jrn_note
             if ($this->db->get_value("select count(*) from jrn_note where 
jr_id=$1",[$this->jr_id])>0){
@@ -347,6 +344,13 @@ class Acc_Ledger  extends jrn_def_sql
                 $this->db->exec_sql("insert into operation_currency 
(oc_amount,oc_vat_amount,oc_price_unit,j_id) "
                         . " select oc_amount,oc_vat_amount,oc_price_unit,$j_id 
from operation_currency where j_id=$1",
                         [$row]);
+                // Extourne also into jrnx_tax
+                $jrn_tax_id=$this->db->exec_sql("insert into 
jrn_tax(j_id,pcm_val,ac_id) 
+                            select $j_id,pcm_val,ac_id from jrn_tax where 
j_id=$1 returning jt_id",
+                    [$row]);
+
+
+
             }
             $sql="insert into jrn (
               jr_id,
diff --git a/include/class/acc_ledger_search.class.php 
b/include/class/acc_ledger_search.class.php
index f6e6b14f9..531930e3b 100644
--- a/include/class/acc_ledger_search.class.php
+++ b/include/class/acc_ledger_search.class.php
@@ -398,24 +398,31 @@ class Acc_Ledger_Search
                                             where ad_id=23 
                                                 and f_id=(select qf_other from 
quant_fin where quant_fin.jr_id=x.jr_id))
            end as quick_code,
-           case
+           ( case
             when jrn_def_type='VEN' then
                     (select sum(qs_price)+sum(vat) from
                                (select qs_internal,qs_price,case when 
qs_vat_sided<>0 then 0 
                                     else qs_vat end as vat 
                                     from quant_sold 
                                     where qs_internal=X.jr_internal) as 
ven_invoice
-                         )
+              )
            when jrn_def_type = 'ACH' then
-                       (
-                               select 
sum(qp_price)+sum(vat)+sum(qp_nd_tva)+sum(qp_nd_tva_recup)
+                       (select 
sum(qp_price)+sum(vat)+sum(qp_nd_tva)+sum(qp_nd_tva_recup)
                                from
                                 (select 
qp_internal,qp_price,qp_nd_tva,qp_nd_tva_recup,qp_vat-qp_vat_sided as vat 
                                  from quant_purchase 
                                  where qp_internal=X.jr_internal) as 
invoice_purchase
-                       )
+            )
                else jr_montant
-               end as total_invoice,
+               end  + 
+               coalesce( case  when jrn_def_type='VEN' then
+                       (select sum(case when j102.j_debit is true then 
0-j102.J_montant else j102.j_montant end)
+                  from jrnx j102 join jrn_tax using(j_id) where j102.j_grpt 
=X.jr_grpt_id) 
+                    when jrn_def_type='ACH' then
+                               (select sum(case when j103.j_debit is false 
then 0-j103.J_montant else j103.j_montant end)
+                  from jrnx j103 join jrn_tax using(j_id) where j103.j_grpt 
=X.jr_grpt_id) 
+         else 
+         0 end   ,0)        )       as total_invoice,
             jr_date_paid,
             to_char(jr_date_paid,'DD.MM.YY') as str_jr_date_paid,
             cas.jr_id as analytic_op,
diff --git a/include/class/acc_operation.class.php 
b/include/class/acc_operation.class.php
index 060a13231..1687616fd 100644
--- a/include/class/acc_operation.class.php
+++ b/include/class/acc_operation.class.php
@@ -210,6 +210,28 @@ class Acc_Operation
         return $this->jrnx_id;
 
     }
+
+    /**
+     * @brief get the sum of other tax linked to this operation
+     */
+    function get_sum_other_tax() {
+        if ( $this->jr_id == 0 ) {return 0;}
+        $sum=$this->db->get_value("select 
+       sum(case when j_debit is false and jrn_def.jrn_def_type='ACH' 
+           then 0-j_montant when j_debit is true and 
jrn_def.jrn_def_type='VEN' 
+               then 0-j_montant 
+           else j_montant end) sum_tax
+         from 
+            jrn_tax join jrnx j1 using (j_id)  
+            join jrn on (jr_grpt_id=j1.j_grpt) 
+            join jrn_def on (jrn.jr_def_id=jrn_def.jrn_def_id)
+         where 
+         jrn.jr_id=$1",[$this->jr_id]);
+        ;
+        if ( $this->db->count()==0) {return 0;}
+        return $sum;
+    }
+
     /*!\brief set the pj of a operation in jrn. the jr_id must be set
      *\note if the jr_id it fails
      */
@@ -341,8 +363,8 @@ class Acc_Operation
     {
         $res=$this->db->exec_sql('select jr_id from jrn where jr_internal=$1',
                                  array($p_internal));
-        if ( Database::num_row($Res) == 0 ) return -1;
-        $this->jr_id=Database::fetch_result($Res,0,0);
+        if ( Database::num_row($res) == 0 ) return -1;
+        $this->jr_id=Database::fetch_result($res,0,0);
         return 0;
     }
     /*!\brief retrieve data from jrnx
@@ -748,7 +770,7 @@ class Acc_Operation
         }
     }
     /**
-     * 
+     * @brief set the operation id (jrn.jr_id)
      * @param type $p_id
      */
     function set_id($p_id) 
@@ -831,7 +853,7 @@ class Acc_Operation
         $a_code=$this->db->get_array("select code from v_menu_dependency vmd  
where me_code=$1 and p_id=$2",
                 array( $operation->signature,$g_user->get_profile()));
         if ( empty ($a_code)) {
-            $r.=_("Menu invalide");
+            $r=_("Menu invalide");
             return $r;
         }
        
diff --git a/include/template/ledger_detail_bottom.php 
b/include/template/ledger_detail_bottom.php
index 30cf76b84..6dbd1d3e1 100644
--- a/include/template/ledger_detail_bottom.php
+++ b/include/template/ledger_detail_bottom.php
@@ -179,9 +179,11 @@ endif;
 <?php 
 
 if ($aRap  != null ) {
-    
$amount_tva_include=(isset($total_tvac))?$total_tvac:$detail->det->jr_montant;
+  
$amount_tva_include=(isset($total_tvac))?$total_tvac:$detail->det->jr_montant;
   $tableid="tb".$div;
   $total_rec=0;
+  $operation=new Acc_Operation($cn);
+  $operation->set_id($jr_id);
   echo '<table id="'.$tableid.'">';
   for ($e=0;$e<count($aRap);$e++)  {
     $opRap=new Acc_Operation($cn);
@@ -196,11 +198,17 @@ if ($aRap  != null ) {
             $amount = $cn->get_value("select sum(qp_price+qp_vat-qp_vat_sided) 
from quant_purchase qp 
                                             where qp_internal=$1",
                 array($internal));
+            // add additional tax if any
+            $add=$operation->get_sum_other_tax();
+            $amount=bcadd($amount,$add,2);
             break;
         case 'V':
             $amount=$cn->get_value("select sum(qs_price+qs_vat-qs_vat_sided) 
from quant_sold qs  
                                         where qs_internal=$1",
                 array($internal));
+            // add additional tax if any
+            $add=$operation->get_sum_other_tax();
+            $amount=bcadd($amount,$add,2);
             break;
     }
     $total_rec=bcadd($total_rec,$amount);