[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Noalyss-commit] [noalyss] 33/44: Database upgrade 131
From: |
Dany De Bontridder |
Subject: |
[Noalyss-commit] [noalyss] 33/44: Database upgrade 131 |
Date: |
Wed, 26 Dec 2018 04:29:55 -0500 (EST) |
sparkyx pushed a commit to branch master
in repository noalyss.
commit 8e7dc4f38a723a211de2b3a881a60a8c9e920554
Author: Dany De Bontridder <address@hidden>
Date: Thu Dec 6 11:50:15 2018 +0100
Database upgrade 131
---
include/constant.php | 2 +-
include/sql/patch/upgrade131.sql | 180 +++++++++++++++++++++++++++++++++++++++
sql/upgrade.sql | 55 ------------
3 files changed, 181 insertions(+), 56 deletions(-)
diff --git a/include/constant.php b/include/constant.php
index 34489ca..2eb6daf 100644
--- a/include/constant.php
+++ b/include/constant.php
@@ -108,7 +108,7 @@ if ( !defined ("NOALYSS_PACKAGE_REPOSITORY")) {
if ( ! defined ("SYSINFO_DISPLAY")) {
define ("SYSINFO_DISPLAY",TRUE);
}
-define ("DBVERSION",131);
+define ("DBVERSION",132);
define ("MONO_DATABASE",25);
define ("DBVERSIONREPO",18);
define ('NOTFOUND','--not found--');
diff --git a/include/sql/patch/upgrade131.sql b/include/sql/patch/upgrade131.sql
new file mode 100644
index 0000000..17dd1c8
--- /dev/null
+++ b/include/sql/patch/upgrade131.sql
@@ -0,0 +1,180 @@
+begin;
+
+drop VIEW public.v_detail_sale;
+
+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)
+;
+
+drop VIEW public.v_detail_purchase;
+
+CREATE OR REPLACE VIEW public.v_detail_purchase
+AS WITH m AS (
+ SELECT sum(quant_purchase_1.qp_price) AS htva,
+ sum(quant_purchase_1.qp_vat) AS tot_vat,
+ sum(quant_purchase_1.qp_vat_sided) AS tot_tva_np,
+ jrn_1.jr_id
+ FROM quant_purchase quant_purchase_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_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,
+ 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_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
+ left join operation_currency as oc on (oc.j_id=jrnx.j_id)
+;
+
+ create or replace view v_all_account_currency as
+select sum(oc_amount) as sum_oc_amount,sum(oc_vat_amount) as
sum_oc_vat_amount,x.j_poste,x.j_id
+from
+quant_fin as q1
+join (select j_id ,jr_id,f_id,j_poste
+ from jrnx as j1 join
+ jrn as j on (j1.j_grpt=jr_grpt_id)
+) as x on (q1.jr_id=x.jr_id)
+join operation_currency as oc on (oc.j_id=q1.j_id)
+group by x.j_poste,x.j_id
+union all
+select sum(oc_amount),sum(oc_vat_amount),x.j_poste,x.j_id
+from
+quant_purchase as q1
+join (select j_id ,jr_id,f_id,j_poste,jr_internal
+ from jrnx as j1 join
+ jrn as j on (j1.j_grpt=jr_grpt_id)
+) as x on (q1.qp_internal=x.jr_internal and (x.f_id=q1.qp_fiche or
x.f_id=qp_supplier) )
+join operation_currency as oc on (oc.j_id=q1.j_id)
+group by x.j_poste,x.j_id
+union all
+select sum(oc_amount),sum(oc_vat_amount),x.j_poste,x.j_id
+from
+quant_sold as q1
+join (select j_id ,jr_id,f_id,j_poste,jr_internal
+ from jrnx as j1 join
+ jrn as j on (j1.j_grpt=jr_grpt_id)
+) as x on (q1.qs_internal=x.jr_internal and (x.f_id=q1.qs_fiche or
x.f_id=q1.qs_client) )
+join operation_currency as oc on (oc.j_id=q1.j_id)
+group by x.j_poste,x.j_id
+;
+
+create or replace view v_all_card_currency as
+select sum(oc_amount) as sum_oc_amount,sum(oc_vat_amount) as
sum_oc_vat_amount,x.f_id,x.j_id
+from
+quant_fin as q1
+join (select j_id ,jr_id,f_id,j_poste
+ from jrnx as j1 join
+ jrn as j on (j1.j_grpt=jr_grpt_id)
+) as x on (q1.jr_id=x.jr_id)
+join operation_currency as oc on (oc.j_id=q1.j_id)
+group by x.f_id,x.j_id
+union all
+select sum(oc_amount),sum(oc_vat_amount),x.f_id,x.j_id
+from
+quant_purchase as q1
+join (select j_id ,jr_id,f_id,j_poste,jr_internal
+ from jrnx as j1 join
+ jrn as j on (j1.j_grpt=jr_grpt_id)
+) as x on (q1.qp_internal=x.jr_internal and (x.f_id=q1.qp_fiche or
x.f_id=qp_supplier) )
+join operation_currency as oc on (oc.j_id=q1.j_id)
+group by x.f_id,x.j_id
+union all
+select sum(oc_amount),sum(oc_vat_amount),x.f_id,x.j_id
+from
+quant_sold as q1
+join (select j_id ,jr_id,f_id,j_poste,jr_internal
+ from jrnx as j1 join
+ jrn as j on (j1.j_grpt=jr_grpt_id)
+) as x on (q1.qs_internal=x.jr_internal and (x.f_id=q1.qs_fiche or
x.f_id=q1.qs_client) )
+join operation_currency as oc on (oc.j_id=q1.j_id)
+group by x.f_id,x.j_id
+;
+insert into version (val,v_description) values (132,'Currency : Create view
for managing currency ');
+commit;
diff --git a/sql/upgrade.sql b/sql/upgrade.sql
index f3340f5..e69de29 100644
--- a/sql/upgrade.sql
+++ b/sql/upgrade.sql
@@ -1,55 +0,0 @@
-CREATE OR REPLACE FUNCTION comptaproc.insert_quant_purchase(p_internal text,
p_j_id numeric, p_fiche character varying, p_quant numeric, p_price numeric,
p_vat numeric, p_vat_code integer, p_nd_amount numeric, p_nd_tva numeric,
p_nd_tva_recup numeric, p_dep_priv numeric, p_client character varying,
p_tva_sided numeric, p_price_unit numeric)
- RETURNS void
- LANGUAGE plpgsql
-AS $function$
-declare
- fid_client integer;
- fid_good integer;
- account_priv account_type;
- fid_good_account account_type;
- n_dep_priv numeric;
-begin
- n_dep_priv := p_dep_priv;
- select p_value into account_priv from parm_code where
p_code='DEP_PRIV';
- select f_id into fid_client from
- fiche_detail where ad_id=23 and ad_value=upper(trim(p_client));
- select f_id into fid_good from
- fiche_detail where ad_id=23 and ad_value=upper(trim(p_fiche));
- select ad_value into fid_good_account from fiche_detail where ad_id=5
and f_id=fid_good;
- if strpos( fid_good_account , account_priv ) = 1 then
- n_dep_priv=p_price;
- end if;
-
- insert into quant_purchase
- (qp_internal,
- j_id,
- qp_fiche,
- qp_quantite,
- qp_price,
- qp_vat,
- qp_vat_code,
- qp_nd_amount,
- qp_nd_tva,
- qp_nd_tva_recup,
- qp_supplier,
- qp_dep_priv,
- qp_vat_sided,
- qp_unit)
- values
- (p_internal,
- p_j_id,
- fid_good,
- p_quant,
- p_price,
- p_vat,
- p_vat_code,
- p_nd_amount,
- p_nd_tva,
- p_nd_tva_recup,
- fid_client,
- n_dep_priv,
- p_tva_sided,
- p_price_unit);
- return;
-end;
- $function$
\ No newline at end of file
- [Noalyss-commit] [noalyss] 28/44: reverse for quant_fin, (continued)
- [Noalyss-commit] [noalyss] 28/44: reverse for quant_fin, Dany De Bontridder, 2018/12/26
- [Noalyss-commit] [noalyss] 36/44: Pre_Operation : add attribut, Dany De Bontridder, 2018/12/26
- [Noalyss-commit] [noalyss] 08/44: Merge branch 'master' of gitlab.noalyss.eu:noalyss/noalyss, Dany De Bontridder, 2018/12/26
- [Noalyss-commit] [noalyss] 44/44: Merge branch 'master' of gitlab.noalyss.eu:noalyss/noalyss, Dany De Bontridder, 2018/12/26
- [Noalyss-commit] [noalyss] 42/44: Doc + typo, Dany De Bontridder, 2018/12/26
- [Noalyss-commit] [noalyss] 39/44: cfgcurrency : file for the new version : if the file to include does not exist then display a warning and exit look also into NOALYSS_INCLUDE, Dany De Bontridder, 2018/12/26
- [Noalyss-commit] [noalyss] 11/44: Improve cosmetic, Dany De Bontridder, 2018/12/26
- [Noalyss-commit] [noalyss] 23/44: translate, Dany De Bontridder, 2018/12/26
- [Noalyss-commit] [noalyss] 38/44: cfgcurrency : file for the new version : if the file to include does not exist then display a warning and exit look also into NOALYSS_INCLUDE, Dany De Bontridder, 2018/12/26
- [Noalyss-commit] [noalyss] 41/44: Merge branch 'master' of gitlab.noalyss.eu:noalyss/noalyss, Dany De Bontridder, 2018/12/26
- [Noalyss-commit] [noalyss] 33/44: Database upgrade 131,
Dany De Bontridder <=
- [Noalyss-commit] [noalyss] 43/44: gitlab #3 Manage Table issue with the type SELECT, cannot reproduce bug Back to old version to fix issue with the type "SELECT" from the menu Syndicat.tools->section, Dany De Bontridder, 2018/12/26
- [Noalyss-commit] [noalyss] 12/44: Translate, Dany De Bontridder, 2018/12/26