
CREATE OR REPLACE VIEW auth.vtopacces AS 
 SELECT DISTINCT u.idact,
    u.idmodul,
    u.iduser,
    date_part('year'::text, u.datai) AS an,
    date_part('month'::text, u.datai) AS luna,
    date_part('day'::text, u.datai) AS zi,
    replace(u.header::text, 'Afisare '::text, ''::text) AS header
   FROM auth.istoric u
  WHERE u.idmodul > 0 AND u.idmodul < 200 AND date_part('year'::text, u.datai) = date_part('year'::text, now()) AND date_part('month'::text, u.datai) = date_part('month'::text, now());

ALTER TABLE auth.vtopacces
  OWNER TO postgres;


--31.10.2018 - adaugare categorii pentru formulare

CREATE TABLE auth.categoriiformulare
(
  idcategorie serial NOT NULL,
  numecategorie text,
  iconscategorie text,
  colorcategorie text,
  CONSTRAINT pkidcategorie PRIMARY KEY (idcategorie)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE auth.categoriiformulare

CREATE TABLE auth.formulare
(
  id integer NOT NULL DEFAULT nextval('auth.formulare_id_seq'::regclass),
  iduser integer NOT NULL,
  idmodul integer NOT NULL,
  titlu text NOT NULL DEFAULT ''::text,
  datacreare date NOT NULL,
  numefisier character varying(500) DEFAULT NULL::character varying,
  idcategorie integer,
  CONSTRAINT pk_idf PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE auth.formulare


--05.11.2018 schimba idmodul pentru formulare
UPDATE auth.formulare
SET idmodul = 33
-- Creare tabela pentru intranet 
-- 24.02.2023
CREATE TABLE IF NOT EXISTS auth.versiuni
(
    id  serial,   
    datacreare timestamp without time zone NOT NULL DEFAULT now(),
    versiune character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT versiuni_pkey PRIMARY KEY (id)
);
--creare tabela pentru ilegis
--27.02.2023
CREATE TABLE IF NOT EXISTS coduri.versiune_licenta ( 
	id serial,
    datacreare timestamp without time zone NOT NULL DEFAULT now(), 
	idlicenta bigint NOT NULL, 
	versiune character varying(255) COLLATE pg_catalog."default", 
    CONSTRAINT versiune_licenta_pkey PRIMARY KEY (id) 
);
--- Florin a creat tabela cu datainstalare in loc de datacreare
DROP TABLE auth.versiuni;

CREATE TABLE auth.versiuni
(
    id integer NOT NULL DEFAULT nextval('auth.versiuni_id_seq'::regclass),
    datainstalare timestamp without time zone NOT NULL DEFAULT now(),
    versiune character varying(255) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT pk_versiuni PRIMARY KEY (id)
) ;


CREATE TABLE IF NOT EXISTS auth.setari_client
(
    id serial,
    verifica_accesari boolean DEFAULT false,
    nr_useri_simultan integer NOT NULL DEFAULT 4,
    mesaj_depasire_accesari text DEFAULT 'Pagina indisponibila.Vă rugăm verificati resursele si drepturile pe directoare.',
    CONSTRAINT pk_setari_client PRIMARY KEY (id)
);
INSERT INTO auth.setari_client 	DEFAULT VALUES;

---6.03.2023
--- tabela pentru rapoartele de accesare pe perioade,luna,zi,sapt
CREATE TABLE IF NOT EXISTS auth.sesiuni_rpt
(
    id serial,
    datacreare timestamp without time zone NOT NULL DEFAULT now(),
    nr_sesiuni_active bigint NOT NULL,
    CONSTRAINT sesiuni_rpt_pkey PRIMARY KEY (id)
)
--4.04.2023
-- transformare action-uri in lowercase
update auth.permission set permission_name=lower(permission_name) where permission_name<>lower(permission_name);
--27.04.2023
with id_permission as(
insert into auth.permission (permission_name,resource_id) select 'monitoranact',(select id from auth.resource as a where trim(a.module_nr)='m1')
where not exists (  select p.id from auth.permission as p inner join auth.resource as r on r.id=p.resource_id  where trim(p.permission_name)='monitoranact' and trim(r.module_nr)='m1' ) returning id)
insert into auth.role_permission (role_id,permission_id) select 3,id from id_permission  where id_permission is not null;
