[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Noalyss-commit] [noalyss] 145/323: merge rel6919.9
From: |
Dany De Bontridder |
Subject: |
[Noalyss-commit] [noalyss] 145/323: merge rel6919.9 |
Date: |
Wed, 14 Mar 2018 17:38:40 -0400 (EDT) |
sparkyx pushed a commit to branch master
in repository noalyss.
commit 23f0778bd9b3134fb45c587656bb0118dd3c80fa
Author: Dany De Bontridder <address@hidden>
Date: Wed Feb 7 16:01:41 2018 +0100
merge rel6919.9
---
include/constant.php | 2 +-
include/sql/patch/upgrade125.sql | 153 +++++----------------
.../sql/patch/{upgrade125.sql => upgrade126.sql} | 2 +-
3 files changed, 37 insertions(+), 120 deletions(-)
diff --git a/include/constant.php b/include/constant.php
index 5d1c9ac..871816b 100644
--- a/include/constant.php
+++ b/include/constant.php
@@ -108,7 +108,7 @@ if ( !defined("SITE_UPDATE_PLUGIN"))
if ( ! defined ("SYSINFO_DISPLAY")) {
define ("SYSINFO_DISPLAY",TRUE);
}
-define ("DBVERSION",126);
+define ("DBVERSION",127);
define ("MONO_DATABASE",25);
define ("DBVERSIONREPO",18);
define ('NOTFOUND','--not found--');
diff --git a/include/sql/patch/upgrade125.sql b/include/sql/patch/upgrade125.sql
index fe2daf7..b5539c8 100644
--- a/include/sql/patch/upgrade125.sql
+++ b/include/sql/patch/upgrade125.sql
@@ -1,119 +1,36 @@
begin;
-
-set search_path to public,comptaproc;
-
-alter table action_gestion drop ag_ref_ag_id;
-
-drop trigger t_tmp_pcm_alphanum_ins_upd on tmp_pcmn ;
-drop trigger t_tmp_pcmn_ins on tmp_pcmn ;
-
-create sequence tmp_pcmn_id_seq;
-ALTER TABLE tmp_pcmn ADD COLUMN id bigint;
-update tmp_pcmn set id=nextval('tmp_pcmn_id_seq');
-
-ALTER TABLE tmp_pcmn ALTER COLUMN id SET NOT NULL;
-ALTER TABLE tmp_pcmn ALTER COLUMN id SET DEFAULT
nextval('tmp_pcmn_id_seq'::regclass);
-ALTER TABLE tmp_pcmn ADD CONSTRAINT id_ux UNIQUE(id);
-COMMENT ON COLUMN tmp_pcmn.id IS 'allow to identify the row, it is unique and
not null (pseudo pk)';
-update tmp_pcmn set id=nextval('tmp_pcmn_id_seq');
-alter table tmp_pcmn add column pcm_direct_use varchar(1);
-COMMENT ON COLUMN tmp_pcmn.pcm_direct_use IS 'Value are N or Y , N cannot be
used directly , not even through a card';
-ALTER TABLE tmp_pcmn ALTER COLUMN pcm_direct_use SET DEFAULT 'Y';
-update tmp_pcmn set pcm_direct_use='Y';
-update tmp_pcmn set pcm_direct_use='N' where length(pcm_val) < 3 and not
exists (select j_poste from jrnx where j_poste=pcm_val);
-ALTER TABLE tmp_pcmn ALTER COLUMN pcm_direct_use SET NOT NULL;
-alter table tmp_pcmn add constraint pcm_direct_use_ck check (pcm_direct_use in
('Y','N'));
-
-create trigger t_tmp_pcm_alphanum_ins_upd before insert or update on
tmp_pcmn for each row execute procedure comptaproc.tmp_pcmn_alphanum_ins_upd();
-create trigger t_tmp_pcmn_ins before insert on tmp_pcmn for each row
execute procedure comptaproc.tmp_pcmn_ins();
-
-select nextval('bilan_b_id_seq');
-select nextval('bilan_b_id_seq');
-select nextval('bilan_b_id_seq');
-select nextval('bilan_b_id_seq');
-
-insert into bilan (b_name,b_file_template,b_file_form,b_type) values
('ASBL','document/fr_be/bnb-asbl.rtf','document/fr_be/bnb-asbl.form','RTF');
-
-alter table jnt_letter drop jl_amount_deb;
-
-ALTER TABLE operation_analytique ADD COLUMN f_id bigint;
-ALTER TABLE operation_analytique ADD CONSTRAINT
operation_analytique_fiche_id_fk FOREIGN KEY (f_id) REFERENCES fiche
(f_id) MATCH SIMPLE ON UPDATE cascade ON delete cascade;
-COMMENT ON COLUMN operation_analytique.f_id IS 'FK to fiche.f_id , used only
with ODS';
-
-drop FUNCTION comptaproc.table_analytic_account(text,text);
-drop FUNCTION comptaproc.table_analytic_card(text,text);
-
-CREATE TABLE public.user_filter (
- id bigserial,
- login text NULL,
- nb_jrn int4 NULL,
- date_start varchar(10) NULL,
- date_end varchar(10) NULL,
- description text NULL,
- amount_min numeric(20,4) NULL,
- amount_max numeric(20,4) NULL,
- qcode text NULL,
- accounting text NULL,
- r_jrn text NULL,
- date_paid_start varchar(10) NULL,
- date_paid_end varchar(10) NULL,
- ledger_type varchar(5) NULL,
- all_ledger int4 NULL,
- filter_name text NOT NULL,
- unpaid varchar NULL,
- PRIMARY KEY (id)
-);
-
-
-
-
-alter table jrn_periode drop constraint jrn_periode_pk;
-create sequence jrn_periode_id_seq;
-alter table jrn_periode add id bigint;
-alter table jrn_periode alter column id set default
nextval('jrn_periode_id_seq');
-update jrn_periode set id=nextval('jrn_periode_id_seq');
-alter table jrn_periode add constraint jrn_periode_pk primary key (id);
-alter table jrn_periode add constraint jrn_periode_periode_ledger unique
(jrn_def_id,p_id);
-
-CREATE TABLE public.user_active_security (
- id serial not NULL,
- us_login text NOT NULL,
- us_ledger varchar(1) not NULL,
- us_action varchar(1) not NULL
-);
-COMMENT ON COLUMN public.user_active_security.us_login IS 'user''s login' ;
-COMMENT ON COLUMN public.user_active_security.us_ledger IS 'Flag Security for
ledger' ;
-COMMENT ON COLUMN public.user_active_security.us_action IS 'Security for
action' ;
-
-ALTER TABLE public.user_active_security ADD CONSTRAINT user_active_security_pk
PRIMARY KEY (id) ;
-ALTER TABLE public.user_active_security ADD CONSTRAINT
user_active_security_ledger_check CHECK (us_ledger in ('Y','N')) ;
-ALTER TABLE public.user_active_security ADD CONSTRAINT
user_active_security_action_check CHECK (us_action in ('Y','N')) ;
-
-insert into user_active_security (us_login,us_ledger,us_action) select
user_name,'Y','Y' from profile_user;
-
-alter table jrn_def add jrn_enable int;
-alter table jrn_def alter jrn_enable set default 1;
-update jrn_def set jrn_enable=1;
-comment on column jrn_def.jrn_enable is 'Set to 1 if the ledger is enable ';
-
-
-alter table jrn add jr_optype varchar(3);
-alter table jrn alter jr_optype set default 'NOR';
-comment on column jrn.jr_optype is 'Type of operation , NOR = NORMAL , OPE
opening , EXT extourne, CLO closing';
-update jrn set jr_optype='NOR';
-
--- update quant_sold set qs_vat_sided=round(qs_vat_sided,2);
--- update quant_purchase set qp_vat_sided=round(qp_vat_sided,2);
-
-alter table tags add column t_actif char(1);
-update tags set t_actif='Y';
-ALTER TABLE tags ADD CONSTRAINT tags_check CHECK (t_actif in ('N','Y')) ;
-alter table tags alter t_actif set default 'Y';
-COMMENT ON COLUMN tags.t_actif is 'Y if the tag is activate and can be used ';
-alter table version add v_description text;
-alter table version add v_date timestamp;
-alter table version alter v_date set default now();
- alter table version add primary key (val);
-insert into version (val,v_description) values (126,'Add filter for search,
inactive tag or ledger, type of operation, security');
-
-commit;
+CREATE OR REPLACE FUNCTION comptaproc.anc_correct_tvand()
+RETURNS VOID
+AS $function$
+declare
+ n_count numeric;
+ i record;
+ newrow_tva record;
+begin
+ for i in select * from operation_analytique where oa_jrnx_id_source
is not null loop
+ -- Get all the anc accounting from the base operation and insert the
missing record for VAT
+ for newrow_tva in select * from operation_analytique where
j_id=i.oa_jrnx_id_source and po_id <> i.po_id loop
+
+ -- check if the record is yet present
+ select count(*) into n_count from operation_analytique
where po_id=newrow_tva.po_id and oa_jrnx_id_source=i.oa_jrnx_id_source;
+
+ if n_count = 0 then
+ raise info 'insert operation analytique po_id = %
oa_group = % ',i.po_id, i.oa_group;
+ insert into operation_analytique
+
(po_id,oa_amount,oa_description,oa_debit,j_id,oa_group,oa_date,oa_jrnx_id_source,oa_positive)
+ values
(newrow_tva.po_id,i.oa_amount,i.oa_description,i.oa_debit,i.j_id,i.oa_group,i.oa_date,i.oa_jrnx_id_source,i.oa_positive);
+ end if;
+
+ end loop;
+
+
+ end loop;
+end;
+ $function$
+LANGUAGE plpgsql;
+
+select comptaproc.anc_correct_tvand();
+
+update version set val=126;
+
+commit;
\ No newline at end of file
diff --git a/include/sql/patch/upgrade125.sql b/include/sql/patch/upgrade126.sql
similarity index 98%
copy from include/sql/patch/upgrade125.sql
copy to include/sql/patch/upgrade126.sql
index fe2daf7..6cb86ba 100644
--- a/include/sql/patch/upgrade125.sql
+++ b/include/sql/patch/upgrade126.sql
@@ -114,6 +114,6 @@ alter table version add v_description text;
alter table version add v_date timestamp;
alter table version alter v_date set default now();
alter table version add primary key (val);
-insert into version (val,v_description) values (126,'Add filter for search,
inactive tag or ledger, type of operation, security');
+insert into version (val,v_description) values (127,'Add filter for search,
inactive tag or ledger, type of operation, security');
commit;
- [Noalyss-commit] [noalyss] 153/323: FIX : PRINTJRN, ledger PDF, (continued)
- [Noalyss-commit] [noalyss] 153/323: FIX : PRINTJRN, ledger PDF, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 152/323: FIX : PRINTJRN, ledger, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 151/323: CSS : improve classic7, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 156/323: Improve : Exercice suivit par mois puis année, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 147/323: Logo : install / index, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 65/323: Task #0001530: Réécriture PRINTJRN, Base + Sale, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 155/323: Improve : last exercice in first place, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 157/323: typo, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 146/323: Ergonomy : ask exercice and periode separately in preference, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 160/323: bug: error js missing quote in ajax_input, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 145/323: merge rel6919.9,
Dany De Bontridder <=
- [Noalyss-commit] [noalyss] 159/323: bug: error js in Icon_Action::close, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 171/323: History based on the date of the operation which it comes from, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 162/323: esthetic : improve title2, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 158/323: bug: cannot create user, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 167/323: Suppress obsolete files, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 174/323: Improve Documentation, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 172/323: History accounting / card : first column is date, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 176/323: Manage_Table_SQL : Add tips for some col, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 164/323: Periode : change with exercice, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 166/323: PRINTBAL : add opening , correct, Dany De Bontridder, 2018/03/14