--
-- 21-10-2024 JL
--
INSERT INTO `champs` (`id`, `code`, `nom`, `idTheme`, `ordre`, `taille`, `equivalentSql`, `sourceListe`, `idTypeHtml`, `afficher`, `table`) VALUES (NULL, 'commentaireEtatTravaux', 'Commentaire État Travaux', '7', '30', '100', 'commentaireEtatTravaux', '', '7', '0', 'demandes_etat_travaux_historique');

--
-- 22-10-2024 FTH
--
ALTER TABLE `export_comptable_format_struct` ADD `statutResident` ENUM('codeStatut','libelleStatut') NULL DEFAULT NULL ;

--
-- Structure de la table `export_comptable_analytique_site`
--

CREATE TABLE IF NOT EXISTS `export_comptable_analytique_site` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idApiPms` smallint(6) NOT NULL,
  `codeSite` varchar(4) NOT NULL,
  `codeAnalytique` varchar(20) NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- 25-10-2024 AK
--
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `listeResidencesBatimentsTypesLogementLogements`  AS  select `residences`.`nom` AS `nomResidence`,`batiments`.`nom` AS `nomBatiment`,`logements`.`nom` AS `nomLogement`,`logements`.`numero` AS `numeroLogement`,`type_logement`.`nom` AS `nomTypeLogement`,`logements`.`id` AS `idLogement`,`batiments`.`id` AS `idBatiment`,`type_logement`.`id` AS `idTypeLogement`,`residences`.`id` AS `idResidence`,`residences_apipms`.`idApiPms` AS `idApiPms`,`residences`.`idPMS` AS `NO_ETABL`,`batiments`.`idPMS` AS `NO_BATIMENT`,`logements`.`idPMS` AS `NO_LOGT`,`type_logement`.`idPMS` AS `T_TYPE` from ((((`logements` left join `batiments` on((`batiments`.`id` = `logements`.`idBatiment`))) left join `residences` on((`residences`.`id` = `batiments`.`idResidence`))) left join `type_logement` on((`type_logement`.`id` = `logements`.`idTypeLogement`))) left join `residences_apipms` on((`residences_apipms`.`idResidence` = `residences`.`id`))) order by `residences`.`nom`,`batiments`.`nom`,`logements`.`numero` ;

-- 25-10-2024 AK
--
ALTER TABLE `tarifs` ADD `idResidence` SMALLINT NOT NULL DEFAULT '0' AFTER `idTypeLogement`, ADD INDEX (`idResidence`), ADD UNIQUE KEY `idCampagneIdTypeLogementIdResidence` (`idCampagne`,`idTypeLogement`,`idResidence`);

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `listeTarifCampagneTypeLogementResidence` AS select `T`.`idCampagne` AS `idCampagne`,`T`.`idTypeLogement` AS `idTypeLogement`,`T`.`idResidence` AS `idResidence`,`C`.`nom` AS `nomCampagne`,`TL`.`code` AS `codeTypeLogement`,`R`.`nom` AS `nomResidence`,`TP`.`idApiPms` AS `idApiPms`,`TL`.`nom` AS `nomTypeLogement`,`T`.`nom` AS `nomTarif`,`T`.`typeLogementDescription` AS `typeLogementDescriptionTarif` from ((((`tarifs` `T` left join `campagnes` `C` on((`T`.`idCampagne` = `C`.`id`))) left join `residences` `R` on((`T`.`idResidence` = `R`.`id`))) left join `type_logement` `TL` on((`TL`.`id` = `T`.`idTypeLogement`))) left join `typelogement_apipms` `TP` on((`TP`.`idTypeLogement` = `T`.`idTypeLogement`))) where 1 order by `T`.`idCampagne`,`T`.`idTypeLogement`,`T`.`idResidence`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stocksTypeLogementByBatimentResidence` AS select `B`.`idResidence` AS `idResidence`,`TB`.`idBatiment` AS `idBatiment`,`TB`.`idTypeLogement` AS `idTypeLogement`,`R`.`nom` AS `nomResidence`,`B`.`nom` AS `nomBatiment`,`TL`.`code` AS `codeTypeLogement`,`TL`.`nom` AS `nomTypeLogement`,`TB`.`nbrTypeLogement` AS `nbrTypeLogement` from (((`typelogement_batiment` `TB` left join `type_logement` `TL` on((`TL`.`id` = `TB`.`idTypeLogement`))) left join `batiments` `B` on((`B`.`id` = `TB`.`idBatiment`))) left join `residences` `R` on((`R`.`id` = `B`.`idResidence`))) where 1 order by `B`.`idResidence`,`TB`.`idBatiment`,`TB`.`idTypeLogement`;

--
-- 28-10-2024 LDS
--
ALTER TABLE `configuration` ADD `CWRMobile` tinyint NOT NULL DEFAULT '0' COMMENT 'Activation ou non du CWR mobile';

--
-- 07-11-2024 AK
--
ALTER TABLE `tarifs` ADD `afficheGDL` BOOLEAN NOT NULL DEFAULT TRUE AFTER `idResidence`, ADD INDEX (`afficheGDL`);

