--
-- BEGIN
-- Ajout de l'aide à la saisie pour les états/lettres-types
-- (correspondance des champs de fusion pour chaque requête)
--
-- Autorisation archive
UPDATE om_requete SET
merge_fields = '-- Autorisation
[nom]
[datenaissance]
[adresse1]
[adresse2]
[CP]
[ville]
[telephone]
[dcd]
[parente]
[observation]

-- Emplacement
[emplacement]
[datevente]
[dateterme]
[duree]
[terme]
[numeroacte]'
WHERE libelle = 'Requête AUTORISATION ARCHIVE';
-- Courrier
UPDATE om_requete SET
merge_fields = '-- Courrier
[datecourrier]
[complement]

-- Autorisation
[titre]
[nom]
[prenom]
[marital]
[adresse1]
[adresse2]
[cp]
[ville]

[autorisation_nature]

-- Emplacement
[emplacement]
[nature]
[superficie]
[emplacement_adresse_numero]
[emplacement_adresse_complement]
[terme]
[numeroacte]
[dateterme]
[datevente]
[famille]
[duree]
[placeconstat]

-- Voie
[type_de_voie]
[voielib]

-- Zone
[type_de_zone]
[zonelib]

-- Cimetière
[cimetierelib]'
WHERE libelle = 'Requête COURRIER';
-- Défunt
UPDATE om_requete SET
merge_fields = '-- Défunt
[dtitre]
[dnom]
[dprenom]
[datenaissance]
[atedeces]
[lieudeces]

-- Autorisation
[ctitre]
[cnom]
[cprenom]

-- Emplacement
[numero]
[complement]

-- Voie
[type_de_voie]
[voielib]

-- Zone
[type_de_zone]
[zonelib]

-- Cimetière
[cimetierelib]'
WHERE libelle = 'Requête DEFUNT';
-- Défunt archive
UPDATE om_requete SET
merge_fields = '-- Défunt
[nom]
[nature]
[datenaissance]
[dateinhumation]
[dateexhumation]
[datereduction]
[reduction]
[exhumation]
[historique]

-- Emplacement
[emplacement]
[numeroacte]
[datevente]
[dateterme]
[duree]
[terme]'
WHERE libelle = 'Requête DEFUNT ARCHIVE';
-- Emplacement
UPDATE om_requete SET
merge_fields = '-- Emplacement
[famille]
[numero]
[complement]
[placeconstat]
[dateconstat]
[nombreplace]
[placeoccupe]

-- Voie
[type_de_voie]
[voielib]

-- Zone
[type_de_zone]
[zonelib]

-- Cimetière
[cimetierelib]'
WHERE libelle = 'Requête EMPLACEMENT';
-- Emplacement archive
UPDATE om_requete SET
merge_fields = '-- Emplacement
[famille]
[numero]
[complement]
[placeconstat]
[dateconstat]
[nombreplace]
[placeoccupe]

-- Voie
[type_de_voie]
[voielib]

-- Zone
[type_de_zone]
[zonelib]

-- Cimetière
[cimetierelib]'
WHERE libelle = 'Requête EMPLACEMENT ARCHIVE';
-- Opération
UPDATE om_requete SET
merge_fields = '-- Emplacement
[numero]
[complement]
[superficie]
[num_adresse]
[datevente]
[surface]

-- Opération
[dateoperation]
[heureoperation]
[numdossier]
[societe]
[particulier]
[pompesfunebres]
[observation]

-- Voie
[type_de_voie]
[voielib]

-- Zone
[type_de_zone]
[zonelib]

-- Cimetière
[cimetiere]'
WHERE libelle = 'Requête OPÉRATION';
-- Voie
UPDATE om_requete SET
merge_fields = '-- Voie
[voie]
[type_de_voie]
[voielib]

-- Zone
[type_de_zone]
[zonelib]

-- Cimetière
[cimetierelib]'
WHERE libelle = 'Requête VOIE';
--
-- END
-- Ajout de l'aide à la saisie pour les états/lettres-types
-- (correspondance des champs de fusion pour chaque requête)
--


--
-- BEGIN
-- Ajout de l'archivage des opérations
--

CREATE TABLE operation_archive (
    operation integer NOT NULL,
    numdossier character varying(10),
    date date,
    heure time without time zone,
    emplacement integer,
    societe_coordonnee text,
    pf_coordonnee text,
    etat character varying(6),
    categorie character varying(20),
    particulier character(3),
    emplacement_transfert integer,
    observation text
);

ALTER TABLE ONLY operation_archive
    ADD CONSTRAINT operation_archive_pkey PRIMARY KEY (operation);

CREATE SEQUENCE operation_archive_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE operation_archive_seq OWNED BY operation_archive.operation;

-- Droit au profil utilisateur (et au dessus vu hiérarchie)
INSERT INTO om_droit (om_droit, libelle, om_profil)
    VALUES (nextval('om_droit_seq'), 'operation_archive', (SELECT om_profil FROM om_profil WHERE libelle = 'UTILISATEUR'));

CREATE TABLE operation_defunt_archive (
    operation_defunt integer NOT NULL,
    operation integer,
    defunt integer,
    defunt_titre integer,
    defunt_nom character varying(40),
    defunt_marital character varying(40),
    defunt_prenom character varying(40),
    defunt_datenaissance date,
    defunt_datedeces date,
    defunt_lieudeces character varying(40),
    defunt_nature character varying(15)
);

ALTER TABLE ONLY operation_defunt_archive
    ADD CONSTRAINT operation_defunt_archive_pkey PRIMARY KEY (operation);

CREATE SEQUENCE operation_defunt_archive_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE operation_defunt_archive_seq OWNED BY operation_defunt_archive.operation;

-- Droit au profil utilisateur (et au dessus vu hiérarchie)
INSERT INTO om_droit (om_droit, libelle, om_profil)
    VALUES (nextval('om_droit_seq'), 'operation_defunt_archive', (SELECT om_profil FROM om_profil WHERE libelle = 'UTILISATEUR'));
--
-- END
-- Ajout de l'archivage des opérations
--


--
-- BEGIN
-- Récupération du libellé des types de voie et de zone au lieu de l'identifiant
--
-- Courrier
UPDATE om_requete SET
requete = 'SELECT
  to_char(courrier.datecourrier, ''DD/MM/YYYY'') as datecourrier,
  courrier.complement,
  titre_de_civilite.code as titre,
  autorisation.nom, 
  autorisation.prenom, 
  autorisation.marital,
  autorisation.adresse1,
  autorisation.adresse2,
  autorisation.cp,
  autorisation.ville,
  autorisation.emplacement,
  autorisation.nature as autorisation_nature,
  emplacement.nature,
  emplacement.superficie,
  emplacement.numero as emplacement_adresse_numero,
  emplacement.complement as emplacement_adresse_complement,
  voie_type.libelle as type_de_voie,
  voielib,
  zone_type.libelle as type_de_zone,
  zonelib,
  emplacement.terme, 
  emplacement.numeroacte,
  to_char(emplacement.dateterme,''DD/MM/YYYY'') as dateterme,
  to_char(emplacement.datevente,''DD/MM/YYYY'') as datevente,
  emplacement.famille,
  emplacement.duree,
  emplacement.placeconstat,
  cimetierelib

FROM
  &DB_PREFIXEcourrier
  LEFT JOIN &DB_PREFIXEautorisation on courrier.destinataire = autorisation.autorisation
  LEFT JOIN &DB_PREFIXEtitre_de_civilite on autorisation.titre = titre_de_civilite.titre_de_civilite
  LEFT JOIN &DB_PREFIXEemplacement on courrier.emplacement = emplacement.emplacement
  LEFT JOIN &DB_PREFIXEvoie on emplacement.voie = voie.voie
  LEFT JOIN &DB_PREFIXEvoie_type on voie.voietype = voie_type.voie_type
  LEFT JOIN &DB_PREFIXEzone on voie.zone = zone.zone
  LEFT JOIN &DB_PREFIXEzone_type on zone.zonetype = zone_type.zone_type
  LEFT JOIN &DB_PREFIXEcimetiere on zone.cimetiere = cimetiere.cimetiere

WHERE
  courrier.courrier = &idx
;'
WHERE libelle = 'Requête COURRIER';
-- Défunt
UPDATE om_requete SET
requete = 'SELECT
  defunt.titre as dtitre,
  defunt.nom as dnom,
  defunt.prenom as dprenom,
  to_char(defunt.datenaissance,''DD/MM/YYYY'') as datenaissance,
  to_char(defunt.datedeces,''DD/MM/YYYY'') as datedeces,
  defunt.lieudeces as lieudeces,
  autorisation.titre as ctitre,
  autorisation.nom as cnom,
  autorisation.prenom as cprenom,
  emplacement.numero as numero,
  cimetierelib,
  emplacement.complement as complement,
  voie_type.libelle as type_de_voie,
  voielib,
  zone_type.libelle as type_de_zone,
  zonelib

FROM
  &DB_PREFIXEdefunt 
  LEFT JOIN &DB_PREFIXEemplacement on defunt.emplacement = emplacement.emplacement
  LEFT JOIN &DB_PREFIXEautorisation on defunt.emplacement = autorisation.emplacement
  LEFT JOIN &DB_PREFIXEvoie on emplacement.voie = voie.voie
  LEFT JOIN &DB_PREFIXEvoie_type on voie.voietype = voie_type.voie_type
  LEFT JOIN &DB_PREFIXEzone on voie.zone = zone.zone
  LEFT JOIN &DB_PREFIXEzone_type on zone.zonetype = zone_type.zone_type
  LEFT JOIN &DB_PREFIXEcimetiere on zone.cimetiere = cimetiere.cimetiere

WHERE
  autorisation.nature=''concessionnaire''
  AND defunt.defunt=&idx
;'
WHERE libelle = 'Requête DEFUNT';
-- Emplacement
UPDATE om_requete SET
requete = 'SELECT
  famille,
  numero,
  complement,
  voie_type.libelle as type_de_voie,
  voielib,
  zone_type.libelle as type_de_zone,
  zonelib,
  cimetierelib,
  placeconstat,
  dateconstat,
  nombreplace,
  placeoccupe

FROM
  &DB_PREFIXEemplacement
  LEFT JOIN &DB_PREFIXEvoie on emplacement.voie = voie.voie
  LEFT JOIN &DB_PREFIXEvoie_type on voie.voietype = voie_type.voie_type
  LEFT JOIN &DB_PREFIXEzone on voie.zone = zone.zone
  LEFT JOIN &DB_PREFIXEzone_type on zone.zonetype = zone_type.zone_type
  LEFT JOIN &DB_PREFIXEcimetiere on zone.cimetiere = cimetiere.cimetiere

WHERE
  emplacement=&idx
;'
WHERE libelle = 'Requête EMPLACEMENT';
-- Emplacement archivé
UPDATE om_requete SET
requete = 'SELECT
  famille,
  numero,
  complement,
  voie_type.libelle as type_de_voie,
  voielib,
  zone_type.libelle as type_de_zone,
  zonelib,
  cimetierelib,
  placeconstat,
  dateconstat,
  nombreplace,
  placeoccupe

FROM
  &DB_PREFIXEemplacement_archive
  LEFT JOIN &DB_PREFIXEvoie on emplacement_archive.voie = voie.voie
  LEFT JOIN &DB_PREFIXEvoie_type on voie.voietype = voie_type.voie_type
  LEFT JOIN &DB_PREFIXEzone on voie.zone = zone.zone
  LEFT JOIN &DB_PREFIXEzone_type on zone.zonetype = zone_type.zone_type
  LEFT JOIN &DB_PREFIXEcimetiere on zone.cimetiere = cimetiere.cimetiere

WHERE
  emplacement=&idx
;'
WHERE libelle = 'Requête EMPLACEMENT ARCHIVE';
-- Opération
UPDATE om_requete SET
requete = 'SELECT
  e.emplacement as numero,
  e.complement as complement,
  e.superficie as superficie,
  c.cimetierelib as cimetiere,
  to_char(o.date,''DD/MM/YYYY'') as dateoperation,
  o.heure as heureoperation,
  e.numero as num_adresse,
  voie_type.libelle as type_de_voie,
  v.voielib as voielib,
  zone_type.libelle as type_de_zone,
  z.zonelib as zonelib,
  to_char(e.datevente,''DD/MM/YYYY'') as datevente,
  e.superficie as surface,
  o.numdossier as numdossier,
  o.societe_coordonnee as societe,
  o.particulier as particulier,
  o.pf_coordonnee as pompesfunebres,
  o.observation as observation

FROM 
  &DB_PREFIXEoperation o,
  &DB_PREFIXEemplacement e,
  &DB_PREFIXEcimetiere c,
  &DB_PREFIXEzone z
  LEFT JOIN &DB_PREFIXEzone_type on z.zonetype = zone_type.zone_type,
  &DB_PREFIXEvoie v
  LEFT JOIN &DB_PREFIXEvoie_type on v.voietype = voie_type.voie_type

WHERE 
  z.cimetiere = c.cimetiere AND
  v.zone = z.zone AND
  e.voie = v.voie AND
  e.emplacement = o.emplacement AND
  o.operation = ''&idx'''
WHERE libelle = 'Requête OPÉRATION';
-- Voie
UPDATE om_requete SET
requete = 'SELECT
  voie,
  voie_type.libelle as type_de_voie,
  voielib,
  zone_type.libelle as type_de_zone,
  zonelib,
  cimetierelib

FROM
  &DB_PREFIXEvoie
  LEFT JOIN &DB_PREFIXEvoie_type on voie.voietype = voie_type.voie_type
  LEFT JOIN &DB_PREFIXEzone on voie.zone = zone.zone
  LEFT JOIN &DB_PREFIXEzone_type on zone.zonetype = zone_type.zone_type
  LEFT JOIN &DB_PREFIXEcimetiere on  zone.cimetiere = cimetiere.cimetiere

WHERE
  voie=&idx
;'
WHERE libelle = 'Requête VOIE';
--
-- END
-- Récupération du libellé des types de voie et de zone au lieu de l'identifiant
--