[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Noalyss-commit] [noalyss] 21/22: upgrade SQL
From: |
Dany De Bontridder |
Subject: |
[Noalyss-commit] [noalyss] 21/22: upgrade SQL |
Date: |
Thu, 18 Jun 2015 15:56:12 +0000 |
sparkyx pushed a commit to branch master
in repository noalyss.
commit e42fd82a5e7a4cf7ca8f586fae3e69f991250679
Author: Dany De Bontridder <address@hidden>
Date: Wed Jun 17 19:24:10 2015 +0200
upgrade SQL
---
html/admin/sql/patch/ac-upgrade15.sql | 45 ++++++++
html/admin/sql/patch/upgrade117.sql | 184 +++++++++++++++++++++++++++++++++
2 files changed, 229 insertions(+), 0 deletions(-)
diff --git a/html/admin/sql/patch/ac-upgrade15.sql
b/html/admin/sql/patch/ac-upgrade15.sql
new file mode 100644
index 0000000..a5b8f94
--- /dev/null
+++ b/html/admin/sql/patch/ac-upgrade15.sql
@@ -0,0 +1,45 @@
+begin;
+
+ALTER TABLE ac_dossier drop COLUMN dos_jnt_user ;
+delete from jnt_use_dos where jnt_id in (select priv_jnt from priv_user where
priv_priv='X');
+delete from jnt_use_dos where use_id in (select use_id from ac_users where
use_admin=1 or use_active=0);
+ALTER TABLE ac_users ADD COLUMN use_email text;
+COMMENT ON COLUMN ac_users.use_email IS 'Email of the user';
+
+CREATE OR REPLACE FUNCTION public.upgrade_repo(p_version integer)
+ RETURNS void
+AS $function$
+declare
+ is_mono integer;
+begin
+ select count (*) into is_mono from information_schema.tables where
table_name='repo_version';
+ if is_mono = 1 then
+ update repo_version set val=p_version;
+ else
+ update version set val=p_version;
+ end if;
+end;
+$function$
+ language plpgsql;
+
+drop table priv_user;
+
+CREATE TABLE recover_pass
+(
+ use_id bigint NOT NULL,
+ request text NOT NULL,
+ password text NOT NULL,
+ created_on timestamp with time zone,
+ created_host text,
+ recover_on timestamp with time zone,
+ recover_by text,
+ CONSTRAINT recover_pass_pkey PRIMARY KEY (request ),
+ CONSTRAINT ac_users_recover_pass_fk FOREIGN KEY (use_id)
+ REFERENCES ac_users (use_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+);
+
+CREATE INDEX fki_ac_users_recover_pass_fk ON recover_pass USING btree
(use_id );
+
+select upgrade_repo(16);
+commit;
diff --git a/html/admin/sql/patch/upgrade117.sql
b/html/admin/sql/patch/upgrade117.sql
new file mode 100644
index 0000000..dc43576
--- /dev/null
+++ b/html/admin/sql/patch/upgrade117.sql
@@ -0,0 +1,184 @@
+begin;
+
+ALTER TABLE action ALTER COLUMN ac_code TYPE character varying(30);
+
+INSERT INTO action(ac_id, ac_description, ac_module, ac_code)
+ VALUES (1110, 'Enlever une pièce justificative', 'compta', 'RMRECEIPT');
+INSERT INTO action(ac_id, ac_description, ac_module, ac_code)
+ VALUES (1120, 'Effacer une opération ', 'compta', 'RMOPER');
+INSERT INTO action(ac_id, ac_description, ac_module, ac_code)
+ VALUES (1210, 'Partager une note', 'note', 'SHARENOTE');
+INSERT INTO action(ac_id, ac_description, ac_module, ac_code)
+ VALUES (1220, 'Créer une note publique', 'note', 'SHARENOTEPUBLIC');
+INSERT INTO action(ac_id, ac_description, ac_module, ac_code)
+ VALUES (1230, 'Effacer une note publique', 'note', 'SHARENOTEREMOVE');
+
+
+CREATE TABLE todo_list_shared (id serial primary key, todo_list_id int4 NOT
NULL, use_login text NOT NULL, CONSTRAINT unique_todo_list_id_login
+ UNIQUE (todo_list_id, use_login));
+
+ALTER TABLE todo_list_shared ADD CONSTRAINT fk_todo_list_shared_todo_list
FOREIGN KEY (todo_list_id) REFERENCES todo_list (tl_id);
+
+comment on table todo_list_shared is 'Note of todo list shared with other
users';
+comment on column todo_list_shared.todo_list_id is 'fk to todo_list';
+comment on column todo_list_shared.use_login is 'user login';
+
+alter table todo_list add is_public char(1) default 'N';
+comment on column todo_list.is_public is 'Flag for the public parameter';
+ALTER TABLE todo_list ALTER COLUMN is_public SET NOT NULL;
+
+ALTER TABLE todo_list ADD CONSTRAINT ck_is_public CHECK (is_public in
('Y','N'));
+
+update menu_ref set me_menu = 'Favori ★ ' where me_code='BOOKMARK';
+update menu_ref set me_menu = 'Sortie ⎆' where me_code='LOGOUT';
+
+insert into menu_ref(me_code,me_menu,me_file,
me_url,me_description,me_parameter,me_javascript,me_type,me_description_etendue)
+values
+('BALAGE','Balance agée','balance_age.inc.php',null,'Balance
agée',null,null,'ME','Balance agée pour les clients et fournisseurs') ,
+('CSV:balance_age','Export Balance
agée','export_balance_age_csv.php',null,'Balance agée',null,null,'PR','Balance
agée pour les clients et fournisseurs') ;
+
+
+insert into profile_menu (me_code,me_code_dep,p_id,p_order,
p_type_display,pm_default)
+values
+('BALAGE','PRINT',1,550,'E',0),('BALAGE','PRINT',2,550,'E',0),
+('CSV:balance_age',null,1,null,'P',0),('CSV:balance_age',null,2,null,'P',0);
+
+CREATE OR REPLACE FUNCTION comptaproc.account_compute(p_f_id integer)
+ RETURNS account_type AS
+$BODY$
+declare
+ class_base fiche_def.fd_class_base%type;
+ maxcode numeric;
+ sResult text;
+ bAlphanum bool;
+ sName text;
+begin
+ select fd_class_base into class_base
+ from
+ fiche_def join fiche using (fd_id)
+ where
+ f_id=p_f_id;
+ raise notice 'account_compute class base %',class_base;
+ bAlphanum := account_alphanum();
+ if bAlphanum = false then
+ raise info 'account_compute : Alphanum is false';
+ select count (pcm_val) into maxcode from tmp_pcmn where
pcm_val_parent = class_base;
+ if maxcode = 0 then
+ maxcode:=class_base::numeric;
+ else
+ select max (pcm_val) into maxcode from tmp_pcmn where
pcm_val_parent = class_base;
+ maxcode:=maxcode::numeric;
+ end if;
+ if maxcode::text = class_base then
+ maxcode:=class_base::numeric*1000;
+ end if;
+ maxcode:=maxcode+1;
+ raise notice 'account_compute Max code %',maxcode;
+ sResult:=maxcode::account_type;
+ else
+ raise info 'account_compute : Alphanum is true';
+ -- if alphanum, use name
+ select ad_value into sName from fiche_detail where f_id=p_f_id
and ad_id=1;
+ raise info 'name is %',sName;
+ if sName is null then
+ raise exception 'Cannot compute an accounting without
the name of the card for %',p_f_id;
+ end if;
+ sResult := class_base||sName;
+ sResult := substr(sResult,1,40);
+ raise info 'Result is %',sResult;
+ end if;
+ return sResult::account_type;
+end;
+$BODY$
+LANGUAGE plpgsql ;
+
+CREATE OR REPLACE FUNCTION comptaproc.account_insert(p_f_id integer, p_account
text)
+ RETURNS text AS
+$BODY$
+declare
+ nParent tmp_pcmn.pcm_val_parent%type;
+ sName varchar;
+ sNew tmp_pcmn.pcm_val%type;
+ bAuto bool;
+ nFd_id integer;
+ sClass_Base fiche_def.fd_class_base%TYPE;
+ nCount integer;
+ first text;
+ second text;
+ s_account text;
+begin
+
+ if p_account is not null and length(trim(p_account)) != 0 then
+ -- if there is coma in p_account, treat normally
+ if position (',' in p_account) = 0 then
+ raise info 'p_account is not empty';
+ s_account := substr( p_account,1 , 40);
+ select count(*) into nCount from tmp_pcmn
where pcm_val=s_account::account_type;
+ raise notice 'found in tmp_pcm %',nCount;
+ if nCount !=0 then
+ raise info 'this account exists in
tmp_pcmn ';
+ perform
attribut_insert(p_f_id,5,s_account);
+ else
+ -- account doesn't exist, create it
+ select ad_value into sName from
+ fiche_detail
+ where
+ ad_id=1 and f_id=p_f_id;
+
+
nParent:=account_parent(s_account::account_type);
+ insert into
tmp_pcmn(pcm_val,pcm_lib,pcm_val_parent) values
(s_account::account_type,sName,nParent);
+ perform
attribut_insert(p_f_id,5,s_account);
+
+ end if;
+ else
+ raise info 'presence of a comma';
+ -- there is 2 accounts separated by a comma
+ first := split_part(p_account,',',1);
+ second := split_part(p_account,',',2);
+ -- check there is no other coma
+ raise info 'first value % second value %', first, second;
+
+ if position (',' in first) != 0 or position (',' in second) !=
0 then
+ raise exception 'Too many comas, invalid account';
+ end if;
+ perform attribut_insert(p_f_id,5,p_account);
+ end if;
+ else
+ raise info 'A000 : p_account is empty';
+ select fd_id into nFd_id from fiche where f_id=p_f_id;
+ bAuto:= account_auto(nFd_id);
+
+ select fd_class_base into sClass_base from fiche_def where
fd_id=nFd_id;
+raise info 'sClass_Base : %',sClass_base;
+ if bAuto = true and sClass_base similar to '[[:digit:]]*' then
+ raise info 'account generated automatically';
+ sNew:=account_compute(p_f_id);
+ raise info 'sNew %', sNew;
+ select ad_value into sName from
+ fiche_detail
+ where
+ ad_id=1 and f_id=p_f_id;
+ nParent:=account_parent(sNew);
+ sNew := account_add (sNew,sName);
+ perform attribut_insert(p_f_id,5,sNew);
+
+ else
+ -- if there is an account_base then it is the default
+ select fd_class_base::account_type into sNew from
fiche_def join fiche using (fd_id) where f_id=p_f_id;
+ if sNew is null or length(trim(sNew)) = 0 then
+ raise notice 'count is null';
+ perform attribut_insert(p_f_id,5,null);
+ else
+ perform attribut_insert(p_f_id,5,sNew);
+ end if;
+ end if;
+ end if;
+
+return 0;
+end;
+$BODY$ LANGUAGE plpgsql ;
+
+
+update version set val=118;
+
+commit;
- [Noalyss-commit] [noalyss] 16/22: Documentation, (continued)
- [Noalyss-commit] [noalyss] 16/22: Documentation, Dany De Bontridder, 2015/06/18
- [Noalyss-commit] [noalyss] 09/22: plugin : remove trailing space, Dany De Bontridder, 2015/06/18
- [Noalyss-commit] [noalyss] 14/22: Cosmetic : add font, Dany De Bontridder, 2015/06/18
- [Noalyss-commit] [noalyss] 18/22: remove some javascript warnings, Dany De Bontridder, 2015/06/18
- [Noalyss-commit] [noalyss] 02/22: translation, Dany De Bontridder, 2015/06/18
- [Noalyss-commit] [noalyss] 22/22: Cosmetic, Dany De Bontridder, 2015/06/18
- [Noalyss-commit] [noalyss] 01/22: translation, Dany De Bontridder, 2015/06/18
- [Noalyss-commit] [noalyss] 19/22: upgrade SQL scripts, Dany De Bontridder, 2015/06/18
- [Noalyss-commit] [noalyss] 15/22: New : new parameter (choice_cat) for the sub_menu see wiki, Dany De Bontridder, 2015/06/18
- [Noalyss-commit] [noalyss] 17/22: Bug replace "Fermer" by a cross, Dany De Bontridder, 2015/06/18
- [Noalyss-commit] [noalyss] 21/22: upgrade SQL,
Dany De Bontridder <=
- [Noalyss-commit] [noalyss] 20/22: translation, Dany De Bontridder, 2015/06/18
- [Noalyss-commit] [noalyss] 03/22: translation, Dany De Bontridder, 2015/06/18
- [Noalyss-commit] [noalyss] 06/22: translation, Dany De Bontridder, 2015/06/18
- [Noalyss-commit] [noalyss] 11/22: Documentation & indentation, Dany De Bontridder, 2015/06/18