[Noalyss-commit] [noalyss] 10/27: Correct mes Task #1038 - Recherche de fiche par poste comptable

[Dany De Bontridder]

sparkyx pushed a commit to branch master
in repository noalyss.

commit cdda61331813ed02baf4222680d3a46880b46c7e
Author: Dany De Bontridder <address@hidden>
Date:   Mon Oct 13 00:49:20 2014 +0200

    Correct mes
    Task #1038 - Recherche de fiche par poste comptable
---
 html/ajax_card.php               |    1 +
 html/js/infobulle.js             |    2 +-
 include/class_fiche.php          |    2 +-
 include/template/card_result.php |    3 +
 sql/upgrade.sql                  |   89 ++++++++++++++++++++++++++++++++++++++
 5 files changed, 95 insertions(+), 2 deletions(-)

diff --git a/html/ajax_card.php b/html/ajax_card.php
index 0738c89..d1cd66f 100644
--- a/html/ajax_card.php
+++ b/html/ajax_card.php
@@ -366,6 +366,7 @@ case 'fs':
     {
         $array[$i]['quick_code']=$a[$i]['quick_code'];
         $array[$i]['name']=h($a[$i]['vw_name']);
+        $array[$i]['accounting']=$a[$i]['accounting'];
         $array[$i]['first_name']=h($a[$i]['vw_first_name']);
         $array[$i]['description']=h($a[$i]['vw_description']);
         $array[$i]['javascript']=sprintf("set_value('%s','%s');",
diff --git a/html/js/infobulle.js b/html/js/infobulle.js
index 4fe431c..5dadc36 100644
--- a/html/js/infobulle.js
+++ b/html/js/infobulle.js
@@ -27,7 +27,7 @@
 var posX=0,posY=0,offsetX=10,offsetY=10;
 document.onmousemove=getPosition;
 var content=new Array();
-content[0]="Donnez le quickcode ou tapez une partie du nom de la fiche, en 
appuyant sur le bouton, vous pourrez la chercher";
+content[0]="Cherchez en saisissant le quickcode, le poste comptable ou une 
partie du nom de la fiche ou de l'adresse";
 content[1]="(optionnel) La description est un commentaire libre qui sert à 
identifier cette opération";
 content[2]="Selectionnez le journal où l'opération doit être sauvée";
 content[3]="Les périodes comptables servent comme un second contrôle pour la 
date de l'opération. Modifiez dans vos préférence pour avoir une autre période 
par défaut. Pour ne plus avoir à changer la période aller dans COMPANY, et 
mettez \"Afficher la période comptable\" à non";
diff --git a/include/class_fiche.php b/include/class_fiche.php
index b88bb91..30caaef 100644
--- a/include/class_fiche.php
+++ b/include/class_fiche.php
@@ -1855,7 +1855,7 @@ class Fiche
 
             if (strlen(trim($query)) > 1)
             {
-                $filter_query=$and."(vw_name ilike '%$query%' or quick_code 
ilike ('%$query%') or vw_description ilike '%$query%' or tva_num ilike 
'%$query%')";
+                $filter_query=$and."(vw_name ilike '%$query%' or quick_code 
ilike ('%$query%') or vw_description ilike '%$query%' or tva_num ilike 
'%$query%' or accounting = '$query')";
             }
             else
             {
diff --git a/include/template/card_result.php b/include/template/card_result.php
index 89ff046..c8fefb3 100644
--- a/include/template/card_result.php
+++ b/include/template/card_result.php
@@ -15,6 +15,9 @@
 </a>
 </td>
 <td>
+   <?php echo 
HtmlInput::history_account($array[$i]['accounting'],$array[$i]['accounting']); 
?>
+</td>
+<td>
    <?php echo $array[$i]['name']?>
 </td>
 <td>
diff --git a/sql/upgrade.sql b/sql/upgrade.sql
index 0649fd4..c6c2798 100644
--- a/sql/upgrade.sql
+++ b/sql/upgrade.sql
@@ -55,3 +55,92 @@ comment on table key_distribution is 'Distribution key for 
analytic';
 comment on table key_distribution_ledger is 'Legder where the distribution key 
can be used' ;
 comment on table key_distribution_detail is 'Row of activity and percent';
 comment on table key_distribution_activity is 'activity (account) linked to 
the row';
+
+drop view vw_fiche_attr cascade;
+
+CREATE view vw_fiche_attr as 
+SELECT a.f_id, a.fd_id, a.ad_value AS vw_name, k.ad_value AS vw_first_name, 
b.ad_value AS vw_sell, c.ad_value AS vw_buy, d.ad_value AS tva_code, 
tva_rate.tva_id, tva_rate.tva_rate, tva_rate.tva_label, e.ad_value AS vw_addr, 
f.ad_value AS vw_cp, j.ad_value AS quick_code, h.ad_value AS vw_description, 
i.ad_value AS tva_num, fiche_def.frd_id,l.ad_value as accounting
+   FROM ( SELECT fiche.f_id, fiche.fd_id, fiche_detail.ad_value
+           FROM fiche
+      LEFT JOIN fiche_detail USING (f_id)
+     WHERE fiche_detail.ad_id = 1) a
+   LEFT JOIN ( SELECT fiche_detail.f_id, fiche_detail.ad_value
+           FROM fiche_detail
+          WHERE fiche_detail.ad_id = 6) b ON a.f_id = b.f_id
+   LEFT JOIN ( SELECT fiche_detail.f_id, fiche_detail.ad_value
+      FROM fiche_detail
+     WHERE fiche_detail.ad_id = 7) c ON a.f_id = c.f_id
+   LEFT JOIN ( SELECT fiche_detail.f_id, fiche_detail.ad_value
+   FROM fiche_detail
+  WHERE fiche_detail.ad_id = 2) d ON a.f_id = d.f_id
+   LEFT JOIN ( SELECT fiche_detail.f_id, fiche_detail.ad_value
+   FROM fiche_detail
+  WHERE fiche_detail.ad_id = 14) e ON a.f_id = e.f_id
+   LEFT JOIN ( SELECT fiche_detail.f_id, fiche_detail.ad_value
+   FROM fiche_detail
+  WHERE fiche_detail.ad_id = 15) f ON a.f_id = f.f_id
+   LEFT JOIN ( SELECT fiche_detail.f_id, fiche_detail.ad_value
+   FROM fiche_detail
+  WHERE fiche_detail.ad_id = 23) j ON a.f_id = j.f_id
+   LEFT JOIN ( SELECT fiche_detail.f_id, fiche_detail.ad_value
+   FROM fiche_detail
+  WHERE fiche_detail.ad_id = 9) h ON a.f_id = h.f_id
+   LEFT JOIN ( SELECT fiche_detail.f_id, fiche_detail.ad_value
+   FROM fiche_detail
+  WHERE fiche_detail.ad_id = 13) i ON a.f_id = i.f_id
+   LEFT JOIN ( SELECT fiche_detail.f_id, fiche_detail.ad_value
+   FROM fiche_detail
+  WHERE fiche_detail.ad_id = 32) k ON a.f_id = k.f_id
+   LEFT JOIN tva_rate ON d.ad_value = tva_rate.tva_id::text
+   JOIN fiche_def USING (fd_id)
+LEFT JOIN ( SELECT fiche_detail.f_id, fiche_detail.ad_value
+   FROM fiche_detail
+  WHERE fiche_detail.ad_id = 5) l ON a.f_id = l.f_id;
+
+
+create view v_detail_sale  as 
+WITH m AS (
+         SELECT sum(quant_sold.qs_price) AS htva, sum(quant_sold.qs_vat) AS 
tot_vat, jrn.jr_id
+           FROM quant_sold
+      JOIN jrnx USING (j_id)
+   JOIN jrn ON jrnx.j_grpt = jrn.jr_grpt_id
+  GROUP BY jrn.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 pr [...]
+   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;
+
+
+create view v_detail_purchase as
+  WITH m AS (
+         SELECT sum(quant_purchase.qp_price) AS htva, 
sum(quant_purchase.qp_vat) AS tot_vat, jrn.jr_id
+           FROM quant_purchase
+      JOIN jrnx USING (j_id)
+   JOIN jrn ON jrnx.j_grpt = jrn.jr_grpt_id
+  GROUP BY jrn.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_purchase.qp_fiche AS item_card, a.name AS 
item_name, 
+quant_purchase.qp_supplier, b.vw_name AS tiers_name, b.quick_code, 
tva_rate.tva_label, 
+tva_rate.tva_comment, tva_rate.tva_both_side, 
+quant_purchase.qp_vat_sided AS vat_sided, 
+quant_purchase.qp_vat_code AS vat_code, 
+quant_purchase.qp_vat AS vat, 
+quant_purchase.qp_price AS price, 
+quant_purchase.qp_quantite AS quantity,
+quant_purchase.qp_price / quant_purchase.qp_quantite AS price_per_unit, 
+quant_purchase.qp_nd_amount AS non_ded_amount,
+ quant_purchase.qp_nd_tva AS non_ded_tva, 
+quant_purchase.qp_nd_tva_recup AS non_ded_tva_recup,
+ m.htva, m.tot_vat
+   FROM jrn
+   JOIN jrnx ON jrn.jr_grpt_id = jrnx.j_grpt
+   JOIN quant_purchase USING (j_id)
+   JOIN vw_fiche_name a ON quant_purchase.qp_fiche = a.f_id
+   JOIN vw_fiche_attr b ON quant_purchase.qp_supplier = b.f_id
+   JOIN tva_rate ON quant_purchase.qp_vat_code = tva_rate.tva_id
+   JOIN m ON m.jr_id = jrn.jr_id;