[Noalyss-commit] [noalyss] branch devel updated: fix bug in upgrade171.sql

[Dany De Bontridder]

This is an automated email from the git hooks/post-receive script.

sparkyx pushed a commit to branch devel
in repository noalyss.

The following commit(s) were added to refs/heads/devel by this push:
     new 20570c8  fix bug in upgrade171.sql
20570c8 is described below

commit 20570c87ffcdc4920be5e2e1798eb4d65c4a94aa
Author: sparkyx <danydb@noalyss.eu>
AuthorDate: Thu Dec 2 17:58:18 2021 +0100

    fix bug in upgrade171.sql
---
 include/sql/patch/ac-upgrade14.sql |  1 +
 include/sql/patch/upgrade171.sql   | 49 ++++++++++++++++++++++++++++++++++++++
 2 files changed, 50 insertions(+)

diff --git a/include/sql/patch/ac-upgrade14.sql 
b/include/sql/patch/ac-upgrade14.sql
index 2c39589..cd5fa14 100644
--- a/include/sql/patch/ac-upgrade14.sql
+++ b/include/sql/patch/ac-upgrade14.sql
@@ -20,6 +20,7 @@ begin
 end;
 $function$
  language plpgsql;
+
 select upgrade_repo(15);
 
 commit;
diff --git a/include/sql/patch/upgrade171.sql b/include/sql/patch/upgrade171.sql
new file mode 100644
index 0000000..d3f3f69
--- /dev/null
+++ b/include/sql/patch/upgrade171.sql
@@ -0,0 +1,49 @@
+begin;
+ALTER TABLE public.parm_periode ADD p_exercice_label text NULL;
+COMMENT ON COLUMN public.parm_periode.p_exercice_label IS 'label of the 
exercice';
+update parm_periode set p_exercice_label =p_exercice ;
+ALTER TABLE public.parm_periode ALTER COLUMN p_exercice_label SET NOT NULL;
+
+COMMENT ON COLUMN public.parm_periode.p_start IS 'Start date of periode';
+COMMENT ON COLUMN public.parm_periode.p_end IS 'End date of periode';
+COMMENT ON COLUMN public.parm_periode.p_exercice IS 'Exercice';
+COMMENT ON COLUMN public.parm_periode.p_closed IS 'is closed';
+COMMENT ON COLUMN public.parm_periode.p_central IS 'is centralized (obsolete)';
+
+
+CREATE OR REPLACE FUNCTION comptaproc.check_periode()
+ RETURNS trigger
+ AS $function$
+declare 
+  nPeriode int;
+  nExerciceLabel int;
+begin
+       nPeriode:=periode_exist(to_char(NEW.p_start,'DD.MM.YYYY'),NEW.p_id);
+-- check that the new - updated period doesn't overlap anything
+       if nPeriode <> -1 then
+       raise info 'Overlap periode start % periode %',NEW.p_start,nPeriode;
+               return null;
+       end if;
+       if new.p_exercice_label is null or trim (new.p_exercice_label ) = '' 
then
+               new.p_exercice_label := new.p_exercice;
+       end if;
+       select count(*) into nExerciceLabel 
+               from parm_periode 
+               where 
+               (p_exercice =new.p_exercice and p_exercice_label <> 
new.p_exercice_label) 
+               or 
+               (p_exercice <> new.p_exercice and p_exercice_label = 
new.p_exercice_label);
+               
+       if nExerciceLabel > 0 then
+               raise exception 'a label cannot be on two exercices';
+               return null;
+       end if;
+
+return NEW;
+end;
+$function$
+LANGUAGE plpgsql;
+
+
+insert into version (val,v_description) values (172,'Add free label for 
exercice');
+commit;
\ No newline at end of file