[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Noalyss-commit] [noalyss] 116/238: Database upgrade 131
From: |
Dany De Bontridder |
Subject: |
[Noalyss-commit] [noalyss] 116/238: Database upgrade 131 |
Date: |
Sat, 26 Oct 2019 04:40:48 -0400 (EDT) |
sparkyx pushed a commit to annotated tag rel7110
in repository noalyss.
commit 47af82452cf8faaf054aea37071e1da17c897612
Author: Dany De Bontridder <address@hidden>
Date: Thu Dec 6 11:50:15 2018 +0100
Database upgrade 131
---
include/constant.php | 2 +-
.../sql/patch/upgrade131.sql | 72 ++++++++++++++
sql/upgrade.sql | 108 ---------------------
3 files changed, 73 insertions(+), 109 deletions(-)
diff --git a/include/constant.php b/include/constant.php
index f025a83..8202fec 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/sql/upgrade.sql b/include/sql/patch/upgrade131.sql
similarity index 59%
copy from sql/upgrade.sql
copy to include/sql/patch/upgrade131.sql
index 40ac273..17dd1c8 100644
--- a/sql/upgrade.sql
+++ b/include/sql/patch/upgrade131.sql
@@ -1,3 +1,7 @@
+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,
@@ -51,6 +55,8 @@ WITH m AS (
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,
@@ -106,3 +112,69 @@ AS WITH m AS (
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 40ac273..e69de29 100644
--- a/sql/upgrade.sql
+++ b/sql/upgrade.sql
@@ -1,108 +0,0 @@
-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)
-;
-
-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)
-;
- [Noalyss-commit] [noalyss] 234/238: fixup! Printtva , under some circumstance tva_summary::check fails, (continued)
- [Noalyss-commit] [noalyss] 234/238: fixup! Printtva , under some circumstance tva_summary::check fails, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 201/238: Merge branch 'dev7109' into entreprise, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 93/238: translate, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 98/238: Fin currency : detail operation show currency , rate and amount, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 104/238: Reverse for Currency, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 101/238: Currency : SQL quant_fin is adapted, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 100/238: Currency : financial use the rate depending of the operation date, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 108/238: acc_ledger_fin . translation, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 99/238: Modify quant_fin , we add a column j_id to have a fk to jrnx and operation_currency, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 109/238: Indentation, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 116/238: Database upgrade 131,
Dany De Bontridder <=
- [Noalyss-commit] [noalyss] 111/238: Currency : sales in listing mode, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 115/238: Currency : ledger printing for FIN + detail operation, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 127/238: Merge commit '3f53de417dd89e9a90a386404f93f8648155e046' into r700-currency, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 118/238: Currency : export CSV history for card and accounting, fix bug with unneeded oc_vat_amount + add the currency rate, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 114/238: Currency : purchase oneline + extended html and csv, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 121/238: Currency : error when displaying, add 4 due to a mistype instruction, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 128/238: Missing bracket, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 136/238: Translation, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 131/238: Translation + use of httpInput, Dany De Bontridder, 2019/10/26
- [Noalyss-commit] [noalyss] 148/238: remove commented code, Dany De Bontridder, 2019/10/26