[Noalyss-commit] [noalyss] 112/218: currency : sale extended html , csv

[Dany De Bontridder]

sparkyx pushed a commit to branch entreprise
in repository noalyss.

commit 318365e618225a5422cfd40f20e33ab55d996f57
Author: Dany De Bontridder <address@hidden>
Date:   Wed Dec 5 10:21:18 2018 +0100

    currency : sale extended html , csv
---
 include/class/acc_ledger_history_sale.class.php    |  5 +-
 include/class/acc_ledger_sold.class.php            |  5 ++
 .../template/acc_ledger_history_sale_extended.php  | 13 ++-
 sql/upgrade.sql                                    | 93 ++++++++++++----------
 4 files changed, 71 insertions(+), 45 deletions(-)

diff --git a/include/class/acc_ledger_history_sale.class.php 
b/include/class/acc_ledger_history_sale.class.php
index ffb8104..9190f14 100644
--- a/include/class/acc_ledger_history_sale.class.php
+++ b/include/class/acc_ledger_history_sale.class.php
@@ -86,13 +86,16 @@ class Acc_Ledger_History_Sale extends Acc_Ledger_History
                 (select f_id,ad_value as qcode 
                 from fiche_detail where ad_id=23)
                 select         
qs_price,qs_quantite,qs_vat,qs_vat_code,qs_unit,qs_vat_sided,name,qcode,tva_label,
-                qs_price+qs_vat-qs_vat_sided as tvac
+                qs_price+qs_vat-qs_vat_sided as tvac,
+                oc_amount,
+                oc_vat_amount
                 from 
                     quant_sold
                     join jrnx using (j_id)              
                     join card_name on (card_name.f_id=qs_fiche)
                     join card_qcode on (card_qcode.f_id=qs_fiche)
                     join tva_rate on ( qs_vat_code=tva_id)
+                    left join operation_currency using (j_id)
                 where
                     qs_internal=$1
                 
diff --git a/include/class/acc_ledger_sold.class.php 
b/include/class/acc_ledger_sold.class.php
index bc1ac28..28b3d35 100644
--- a/include/class/acc_ledger_sold.class.php
+++ b/include/class/acc_ledger_sold.class.php
@@ -1563,6 +1563,11 @@ EOF;
         $array['price_per_unit'] = _('PU');
         $array['htva'] = _('HTVA Opération');
         $array['tot_vat'] = _('TVA Opération');
+        $array['tot_vat_np'] = _('TVA ND');
+        $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_sale_extended.php 
b/include/template/acc_ledger_history_sale_extended.php
index 4e7fa18..a9a76c6 100644
--- a/include/template/acc_ledger_history_sale_extended.php
+++ b/include/template/acc_ledger_history_sale_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/sql/upgrade.sql b/sql/upgrade.sql
index 1285bce..5339f75 100644
--- a/sql/upgrade.sql
+++ b/sql/upgrade.sql
@@ -1,41 +1,52 @@
-CREATE OR REPLACE FUNCTION isnumeric(text) RETURNS BOOLEAN AS $$
-DECLARE x NUMERIC;
-BEGIN
-    x = $1::NUMERIC;
-    RETURN TRUE;
-EXCEPTION WHEN others THEN
-    RETURN FALSE;
-END;
-$$
-STRICT
-LANGUAGE plpgsql IMMUTABLE;
-
-CREATE OR REPLACE FUNCTION isdate(text,text) RETURNS BOOLEAN AS $$
-DECLARE x timestamp;
-BEGIN
-    x := to_date($1,$2);
-    RETURN TRUE;
-EXCEPTION WHEN others THEN
-    RETURN FALSE;
-END;
-$$
-LANGUAGE plpgsql;
-
-ALTER TABLE public.jrn_def ADD currency_id int NULL;
-ALTER TABLE public.jrn_def ALTER COLUMN currency_id SET DEFAULT 0;
-update  public.jrn_def  set currency_id = 0 ; 
-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;
-
+CREATE OR REPLACE VIEW public.v_detail_sale as
+WITH m AS (
+         SELECT sum(quant_sold_1.qs_price) AS htva,
+            sum(quant_sold_1.qs_vat) AS tot_vat,
+            sum(quant_sold_1.qs_vat_sided) AS tot_tva_np,
+            jrn_1.jr_id
+           FROM quant_sold quant_sold_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_sold.qs_fiche AS item_card,
+    a.name AS item_name,
+    quant_sold.qs_client,
+    b.vw_name AS tiers_name,
+    b.quick_code,
+    tva_rate.tva_label,
+    tva_rate.tva_comment,
+    tva_rate.tva_both_side,
+    quant_sold.qs_vat_sided AS vat_sided,
+    quant_sold.qs_vat_code AS vat_code,
+    quant_sold.qs_vat AS vat,
+    quant_sold.qs_price AS price,
+    quant_sold.qs_quantite AS quantity,
+    quant_sold.qs_price / quant_sold.qs_quantite AS price_per_unit,
+    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_sold USING (j_id)
+     JOIN vw_fiche_name a ON quant_sold.qs_fiche = a.f_id
+     JOIN vw_fiche_attr b ON quant_sold.qs_client = b.f_id
+     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