[Noalyss-commit] [noalyss] 99/218: Modify quant_fin , we add a column j_id to have a fk to jrnx and operation_currency

[Dany De Bontridder]

sparkyx pushed a commit to branch entreprise
in repository noalyss.

commit c155f87b65031f9b043bdc880ea58beca32f0945
Author: Dany De Bontridder <address@hidden>
Date:   Tue Nov 27 22:05:20 2018 +0100

    Modify quant_fin , we add a column j_id to have a fk to jrnx and 
operation_currency
---
 include/class/acc_ledger_fin.class.php | 18 +++++++++---------
 include/class/acc_operation.class.php  |  2 +-
 include/template/ledger_detail_fin.php |  4 ++--
 sql/upgrade.sql                        | 11 +++++++++++
 4 files changed, 23 insertions(+), 12 deletions(-)

diff --git a/include/class/acc_ledger_fin.class.php 
b/include/class/acc_ledger_fin.class.php
index 4668b28..8ae133f 100644
--- a/include/class/acc_ledger_fin.class.php
+++ b/include/class/acc_ledger_fin.class.php
@@ -833,14 +833,14 @@ class Acc_Ledger_Fin extends Acc_Ledger
                                }
                                $acc_operation->periode = $tperiode;
                                $acc_operation->qcode = ${"e_other" . $i};
-                               $j_id = $acc_operation->insert_jrnx();
+                               $j_id_currency = $acc_operation->insert_jrnx();
 
                                 // -- Insert into Operation Currency 
                                 $operation_currency = new 
Operation_currency_SQL($this->db);
                                 $operation_currency->oc_amount=$amount_input;
                                 $operation_currency->oc_vat_amount=0;
                                 
$operation_currency->oc_price_unit=$amount_input;
-                                $operation_currency->j_id=$j_id;
+                                $operation_currency->j_id=$j_id_currency;
                                 $operation_currency->insert();
                                 
                                $acc_operation = new Acc_Operation($this->db);
@@ -962,7 +962,7 @@ class Acc_Ledger_Fin extends Acc_Ledger
                                /**
                                 * save also into quant_fin
                                 */
-                               $this->insert_quant_fin($fBank->id, $jr_id, 
$fPoste->id, $amount_eur);
+                               $this->insert_quant_fin($fBank->id, $jr_id, 
$fPoste->id, $amount_eur,$j_id_currency);
 
                                if ($g_parameter->MY_ANALYTIC != "nu")
                                {
@@ -970,11 +970,11 @@ class Acc_Ledger_Fin extends Acc_Ledger
                                        $op = new Anc_Operation($this->db);
                                         $op->set_currency_rate($currency_rate);
                                        $op->oa_group = 
$this->db->get_next_seq("s_oa_group"); /* for analytic */
-                                       $op->j_id = $j_id;
+                                       $op->j_id = $j_id_currency;
                                        $op->oa_date = $e_date;
                                        $op->oa_debit = 'f';
                                        $op->oa_description = 
sql_string($comment);
-                                       $op->save_form_plan($_POST, $i, $j_id);
+                                       $op->save_form_plan($_POST, $i, 
$j_id_currency);
                                }
 
 
@@ -1184,12 +1184,12 @@ class Acc_Ledger_Fin extends Acc_Ledger
         * @param $other is the f_id of the benefit
         * @param $amount is the amount
         */
-       function insert_quant_fin($p_bankid, $p_jrid, $p_otherid, $p_amount)
+       function insert_quant_fin($p_bankid, $p_jrid, $p_otherid, 
$p_amount,$p_j_id_currency)
        {
-               $sql = "INSERT INTO quant_fin(qf_bank, jr_id, qf_other, 
qf_amount)
-                   VALUES ($1, $2, $3, $4);";
+               $sql = "INSERT INTO quant_fin(qf_bank, jr_id, qf_other, 
qf_amount,j_id)
+                   VALUES ($1, $2, $3, $4,$5);";
 
-               $this->db->exec_sql($sql, array($p_bankid, $p_jrid, $p_otherid, 
round($p_amount, 2)));
+               $this->db->exec_sql($sql, array($p_bankid, $p_jrid, $p_otherid, 
round($p_amount, 2),$p_j_id_currency));
        }
 
 }
diff --git a/include/class/acc_operation.class.php 
b/include/class/acc_operation.class.php
index b19ce1a..5b45883 100644
--- a/include/class/acc_operation.class.php
+++ b/include/class/acc_operation.class.php
@@ -872,7 +872,7 @@ class Acc_Fin extends Acc_Detail
     function get()
     {
         parent::get();
-        $sql="SELECT qf_id, qf_bank, jr_id, qf_other, qf_amount
+        $sql="SELECT qf_id, qf_bank, jr_id, qf_other, qf_amount,j_id
              FROM quant_fin where jr_id = $1";
         $this->det->array=$this->db->get_array($sql,array($this->jr_id));
     }
diff --git a/include/template/ledger_detail_fin.php 
b/include/template/ledger_detail_fin.php
index 80b3458..57f7d42 100644
--- a/include/template/ledger_detail_fin.php
+++ b/include/template/ledger_detail_fin.php
@@ -99,8 +99,8 @@ echo td(_('Pièce')).td($itext->input());
  */
 if ($obj->det->currency_id!=0)
 {
-    $currency_amount=$obj->db->get_value("select  oc_amount from 
operation_currency where j_id in (select j_id from jrn join jrnx on 
(j_grpt=jr_grpt_id ) where jr_id=$1)", 
-            [$obj->jr_id]);
+    $currency_amount=$obj->db->get_value("select  oc_amount from 
operation_currency where j_id =$1", 
+            [$obj->det->array[0]['j_id']]);
     $currency_rate=$obj->db->get_value("select currency_rate from jrn where 
jr_id=$1",[$obj->jr_id]);
     $currency_code=$obj->db->get_value("select cr_code_iso from currency where 
id=$1",[$obj->det->currency_id]);
     printf ("%s Taux utilisé %s Montant en devise 
%s",$currency_code,$currency_rate,$currency_amount);
diff --git a/sql/upgrade.sql b/sql/upgrade.sql
index 9a554f1..1285bce 100644
--- a/sql/upgrade.sql
+++ b/sql/upgrade.sql
@@ -28,3 +28,14 @@ ALTER TABLE public.jrn_def ALTER COLUMN currency_id SET NOT 
NULL;
 ALTER TABLE public.jrn_def ADD CONSTRAINT jrn_def_currency_fk FOREIGN KEY 
(currency_id) REFERENCES public.currency(id);
 
 COMMENT ON COLUMN public.jrn_def.currency_id IS 'Default currency for 
financial ledger';
+
+
+alter table quant_fin add j_id bigint;
+
+with j_fin as (
+select jrnx.j_id,quant_fin.qf_id from quant_fin join jrn using (jr_id) join 
jrnx on (j_grpt=jr_grpt_id and f_id=qf_other)
+)
+update quant_fin set j_id =j_fin.j_id from j_fin where 
j_fin.qf_id=quant_fin.qf_id;
+
+alter table quant_fin add constraint jrnx_j_id_fk foreign key (j_id ) 
references jrnx(j_id) on delete cascade on update cascade;
+