schema update : more views for api + more enums

2 files changed, 185 insertions, 10 deletions

diff --git a/schema/enum-tables-data-mysql.sql b/schema/enum-tables-data-mysql.sql
index 32a531d..667a9a0 100644
--- a/schema/enum-tables-data-mysql.sql
+++ b/schema/enum-tables-data-mysql.sql
@@ -1,8 +1,8 @@
--- MySQL dump 10.13  Distrib 5.5.47, for debian-linux-gnu (x86_64)
+-- MySQL dump 10.16  Distrib 10.1.26-MariaDB, for debian-linux-gnu (x86_64)
 --
 -- Host: localhost    Database: gestion
 -- ------------------------------------------------------
--- Server version	5.5.47-0+deb8u1
+-- Server version	10.1.26-MariaDB-0+deb9u1
 
 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
@@ -52,7 +52,7 @@ CREATE TABLE `adherent_statuts` (
   `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `title` (`title`)
-) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -61,7 +61,7 @@ CREATE TABLE `adherent_statuts` (
 
 LOCK TABLES `adherent_statuts` WRITE;
 /*!40000 ALTER TABLE `adherent_statuts` DISABLE KEYS */;
-INSERT INTO `adherent_statuts` VALUES (2,'☐ Fiche ☐ Prélev'),(4,'☐ Fiche ☑ Prélev'),(3,'☑ Fiche ☐ Prélev'),(5,'☑ Fiche ☑ Prélev'),(7,'À relancer'),(8,'Bénévole sans service'),(6,'Incohérent'),(1,'Résilié');
+INSERT INTO `adherent_statuts` VALUES (2,'☐ Fiche ☐ Prélev'),(4,'☐ Fiche ☑ Prélev'),(3,'☑ Fiche ☐ Prélev'),(5,'☑ Fiche ☑ Prélev'),(7,'À relancer'),(8,'Bénévole sans service'),(6,'Incohérent'),(1,'Résilié'),(9,'Restauré');
 /*!40000 ALTER TABLE `adherent_statuts` ENABLE KEYS */;
 UNLOCK TABLES;
 
@@ -127,7 +127,7 @@ CREATE TABLE `equipement_modeles` (
   `profile` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
   `revision` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Modèles d''équipements';
+) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Modèles d''équipements';
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -136,7 +136,7 @@ CREATE TABLE `equipement_modeles` (
 
 LOCK TABLES `equipement_modeles` WRITE;
 /*!40000 ALTER TABLE `equipement_modeles` DISABLE KEYS */;
-INSERT INTO `equipement_modeles` VALUES (2,'TP-Link','TL-WR740ND v??','TLWR740','4'),(3,'TP-Link','TL-WR740ND v1-3','',''),(4,'TP-Link','TL-WR740ND v4','TLWR740','4'),(5,'TP-Link','TL-WR740ND v5.0','TLWR740','5'),(6,'TP-Link','TL-WR740ND v5.1','TLWR740','51'),(7,'TP-Link','TL-WR740ND v6','TLWR740','6'),(8,'TP-Link','TL-WR841ND v9','TLWR841','9'),(9,'TP-Link','TL-WR841ND v10','TLWR841','10'),(10,'TP-Link','TL-WR841ND v11','','11'),(11,'TP-Link','TL-WR1043ND v1.10','','110'),(12,'Ubiquiti','NanoBeam M5 16','',''),(13,'Ubiquiti','NanoBeam M5 19','',''),(14,'Ubiquiti','NanoBridge M5 G22','',''),(15,'Ubiquiti','NanoBridge M5 G25','',''),(16,'Ubiquiti','Nanostation M5','',''),(17,'Ubiquiti','Nanostation M5 Loco','',''),(18,'Ubiquiti','PowerBeam M5 400','',''),(19,'Ubiquiti','Rocket M5','',''),(20,'Ubiquiti','AirFiber 5X + Mars 25dBi','',''),(21,'Ubiquiti','AirFiber 5X + AF-5G30-S45','',''),(22,'Ubiquiti','Nanostation M2 Loco','',''),(30,'Ubiquiti','ToughSwitch TS5','',''),(31,'Ubiquiti','ToughSwitch TS8','',''),(32,'Ubiquiti','EdgeMax EdgeSwitch 24-Port 250W','',''),(40,'Netonix','WS-6-MINI','',''),(41,'Netonix','8 ports','',''),(45,'Mikrotik','RouterBoard 750UP','',''),(46,'Mikrotik','RB2011UiAS-2HnD-IN',' ',' '),(47,'Routeur adhérent custom','-','',''),(48,'VM','Linux KVM','','');
+INSERT INTO `equipement_modeles` VALUES (2,'TP-Link','TL-WR740ND v??','TLWR740','4'),(3,'TP-Link','TL-WR740ND v1-3','',''),(4,'TP-Link','TL-WR740ND v4','TLWR740','4'),(5,'TP-Link','TL-WR740ND v5.0','TLWR740','5'),(6,'TP-Link','TL-WR740ND v5.1','TLWR740','51'),(7,'TP-Link','TL-WR740ND v6','TLWR740','6'),(8,'TP-Link','TL-WR841ND v9','TLWR841','9'),(9,'TP-Link','TL-WR841ND v10','TLWR841','10'),(10,'TP-Link','TL-WR841ND v11','TLWR841','11'),(11,'TP-Link','TL-WR1043ND v1.10','','110'),(12,'Ubiquiti','NanoBeam M5 16','',''),(13,'Ubiquiti','NanoBeam M5 19','',''),(14,'Ubiquiti','NanoBridge M5 G22','',''),(15,'Ubiquiti','NanoBridge M5 G25','',''),(16,'Ubiquiti','Nanostation M5','',''),(17,'Ubiquiti','Nanostation M5 Loco','',''),(18,'Ubiquiti','PowerBeam M5 400','',''),(19,'Ubiquiti','Rocket M5','',''),(20,'Ubiquiti','AirFiber 5X + Mars 25dBi','',''),(21,'Ubiquiti','AirFiber 5X + AF-5G30-S45','',''),(22,'Ubiquiti','Nanostation M2 Loco','',''),(30,'Ubiquiti','ToughSwitch TS5','',''),(31,'Ubiquiti','ToughSwitch TS8','',''),(32,'Ubiquiti','EdgeMax EdgeSwitch 24-Port 250W','',''),(40,'Netonix','WS-6-MINI','',''),(41,'Netonix','8 ports','',''),(45,'Mikrotik','RouterBoard 750UP','',''),(46,'Mikrotik','RB2011UiAS-2HnD-IN',' ',' '),(47,'Routeur adhérent custom','-','',''),(48,'VM','Linux KVM','',''),(49,'Synology','RT1900ac','',''),(50,'Ubiquiti','LiteBeam AC 120°','',''),(51,'Ubiquiti','Nanobeam 5AC 19','',''),(52,'Ubiquiti','PowerBeam 5AC 25','',''),(53,'Ubiquiti','PowerBeam 5AC Gen2','',''),(54,'Ubiquiti','EdgePoint 16 ports','','');
 /*!40000 ALTER TABLE `equipement_modeles` ENABLE KEYS */;
 UNLOCK TABLES;
 
@@ -275,4 +275,4 @@ UNLOCK TABLES;
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
--- Dump completed on 2016-08-29 20:20:38
+-- Dump completed on 2018-06-21 22:00:27
diff --git a/schema/schema-mysql.sql b/schema/schema-mysql.sql
index 636ebd5..9a12d26 100644
--- a/schema/schema-mysql.sql
+++ b/schema/schema-mysql.sql
@@ -1,8 +1,8 @@
--- MySQL dump 10.13  Distrib 5.5.47, for debian-linux-gnu (x86_64)
+-- MySQL dump 10.16  Distrib 10.1.26-MariaDB, for debian-linux-gnu (x86_64)
 --
 -- Host: localhost    Database: gestion
 -- ------------------------------------------------------
--- Server version	5.5.47-0+deb8u1
+-- Server version	10.1.26-MariaDB-0+deb9u1
 
 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
@@ -441,6 +441,21 @@ CREATE TABLE `services` (
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
+-- Table structure for table `tmp_mac_seen`
+--
+
+DROP TABLE IF EXISTS `tmp_mac_seen`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `tmp_mac_seen` (
+  `ip` varchar(25) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
+  `mac` varchar(17) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
+  UNIQUE KEY `ip` (`ip`),
+  KEY `mac` (`mac`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
 -- Temporary table structure for view `v_adt_sms`
 --
 
@@ -501,6 +516,75 @@ SET character_set_client = utf8;
 SET character_set_client = @saved_cs_client;
 
 --
+-- Temporary table structure for view `v_compta_svc_no_compte`
+--
+
+DROP TABLE IF EXISTS `v_compta_svc_no_compte`;
+/*!50001 DROP VIEW IF EXISTS `v_compta_svc_no_compte`*/;
+SET @saved_cs_client     = @@character_set_client;
+SET character_set_client = utf8;
+/*!50001 CREATE TABLE `v_compta_svc_no_compte` (
+  `service` tinyint NOT NULL,
+  `adt` tinyint NOT NULL,
+  `adt_title` tinyint NOT NULL,
+  `relais_id` tinyint NOT NULL,
+  `relais` tinyint NOT NULL,
+  `secteur_id` tinyint NOT NULL,
+  `secteur` tinyint NOT NULL,
+  `no_compte` tinyint NOT NULL
+) ENGINE=MyISAM */;
+SET character_set_client = @saved_cs_client;
+
+--
+-- Temporary table structure for view `v_ippubliques_allocations`
+--
+
+DROP TABLE IF EXISTS `v_ippubliques_allocations`;
+/*!50001 DROP VIEW IF EXISTS `v_ippubliques_allocations`*/;
+SET @saved_cs_client     = @@character_set_client;
+SET character_set_client = utf8;
+/*!50001 CREATE TABLE `v_ippubliques_allocations` (
+  `secteur` tinyint NOT NULL,
+  `allouees` tinyint NOT NULL,
+  `utilisee` tinyint NOT NULL,
+  `neuves` tinyint NOT NULL
+) ENGINE=MyISAM */;
+SET character_set_client = @saved_cs_client;
+
+--
+-- Temporary table structure for view `v_rapprochement_compta`
+--
+
+DROP TABLE IF EXISTS `v_rapprochement_compta`;
+/*!50001 DROP VIEW IF EXISTS `v_rapprochement_compta`*/;
+SET @saved_cs_client     = @@character_set_client;
+SET character_set_client = utf8;
+/*!50001 CREATE TABLE `v_rapprochement_compta` (
+  `svc_id` tinyint NOT NULL,
+  `svc_annee_premiere_saisie` tinyint NOT NULL,
+  `actif2015` tinyint NOT NULL,
+  `actif2016` tinyint NOT NULL,
+  `actif2017` tinyint NOT NULL,
+  `svc_derniere_modif` tinyint NOT NULL,
+  `adt` tinyint NOT NULL,
+  `adt_type` tinyint NOT NULL,
+  `raison` tinyint NOT NULL,
+  `nom` tinyint NOT NULL,
+  `prenom` tinyint NOT NULL,
+  `service_type` tinyint NOT NULL,
+  `prix_base_ht` tinyint NOT NULL,
+  `prix_ht` tinyint NOT NULL,
+  `periodicite` tinyint NOT NULL,
+  `date_debut` tinyint NOT NULL,
+  `date_fin` tinyint NOT NULL,
+  `statut` tinyint NOT NULL,
+  `proprio` tinyint NOT NULL,
+  `description` tinyint NOT NULL,
+  `notes` tinyint NOT NULL
+) ENGINE=MyISAM */;
+SET character_set_client = @saved_cs_client;
+
+--
 -- Temporary table structure for view `v_relais`
 --
 
@@ -598,6 +682,21 @@ SET character_set_client = utf8;
 SET character_set_client = @saved_cs_client;
 
 --
+-- Temporary table structure for view `v_tmp_diff_seen_mac`
+--
+
+DROP TABLE IF EXISTS `v_tmp_diff_seen_mac`;
+/*!50001 DROP VIEW IF EXISTS `v_tmp_diff_seen_mac`*/;
+SET @saved_cs_client     = @@character_set_client;
+SET character_set_client = utf8;
+/*!50001 CREATE TABLE `v_tmp_diff_seen_mac` (
+  `mac` tinyint NOT NULL,
+  `seen_ip` tinyint NOT NULL,
+  `gestion_mgmtip` tinyint NOT NULL
+) ENGINE=MyISAM */;
+SET character_set_client = @saved_cs_client;
+
+--
 -- Table structure for table `villes`
 --
 
@@ -738,6 +837,63 @@ DELIMITER ;
 /*!50001 SET collation_connection      = @saved_col_connection */;
 
 --
+-- Final view structure for view `v_compta_svc_no_compte`
+--
+
+/*!50001 DROP TABLE IF EXISTS `v_compta_svc_no_compte`*/;
+/*!50001 DROP VIEW IF EXISTS `v_compta_svc_no_compte`*/;
+/*!50001 SET @saved_cs_client          = @@character_set_client */;
+/*!50001 SET @saved_cs_results         = @@character_set_results */;
+/*!50001 SET @saved_col_connection     = @@collation_connection */;
+/*!50001 SET character_set_client      = utf8mb4 */;
+/*!50001 SET character_set_results     = utf8mb4 */;
+/*!50001 SET collation_connection      = utf8mb4_general_ci */;
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`gestion`@`localhost` SQL SECURITY INVOKER */
+/*!50001 VIEW `v_compta_svc_no_compte` AS select `v`.`service` AS `service`,`v`.`adt` AS `adt`,`v`.`adt_title` AS `adt_title`,`v`.`relais_id` AS `relais_id`,`r`.`title` AS `relais`,`v`.`secteur_id` AS `secteur_id`,`s`.`title` AS `secteur`,(41020039455 + `v`.`secteur_id`) AS `no_compte` from ((`v_relais_detail` `v` left join `relais` `r` on((`r`.`id` = `v`.`relais_id`))) left join `secteurs` `s` on((`s`.`id` = `v`.`secteur_id`))) order by `v`.`adherent_id` */;
+/*!50001 SET character_set_client      = @saved_cs_client */;
+/*!50001 SET character_set_results     = @saved_cs_results */;
+/*!50001 SET collation_connection      = @saved_col_connection */;
+
+--
+-- Final view structure for view `v_ippubliques_allocations`
+--
+
+/*!50001 DROP TABLE IF EXISTS `v_ippubliques_allocations`*/;
+/*!50001 DROP VIEW IF EXISTS `v_ippubliques_allocations`*/;
+/*!50001 SET @saved_cs_client          = @@character_set_client */;
+/*!50001 SET @saved_cs_results         = @@character_set_results */;
+/*!50001 SET @saved_col_connection     = @@collation_connection */;
+/*!50001 SET character_set_client      = utf8mb4 */;
+/*!50001 SET character_set_results     = utf8mb4 */;
+/*!50001 SET collation_connection      = utf8mb4_general_ci */;
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`gestion`@`localhost` SQL SECURITY INVOKER */
+/*!50001 VIEW `v_ippubliques_allocations` AS select `sec`.`title` AS `secteur`,count(`i`.`ip4`) AS `allouees`,sum(if(isnull(`ser`.`id`),0,1)) AS `utilisee`,sum(if(isnull(`ser`.`id`),1,0)) AS `neuves` from ((`ippubliques` `i` left join `secteurs` `sec` on((`i`.`secteur_id` = `sec`.`id`))) left join `services` `ser` on((`i`.`ip4` = `ser`.`ippublique_id`))) where (`i`.`secteur_id` is not null) group by `sec`.`title` */;
+/*!50001 SET character_set_client      = @saved_cs_client */;
+/*!50001 SET character_set_results     = @saved_cs_results */;
+/*!50001 SET collation_connection      = @saved_col_connection */;
+
+--
+-- Final view structure for view `v_rapprochement_compta`
+--
+
+/*!50001 DROP TABLE IF EXISTS `v_rapprochement_compta`*/;
+/*!50001 DROP VIEW IF EXISTS `v_rapprochement_compta`*/;
+/*!50001 SET @saved_cs_client          = @@character_set_client */;
+/*!50001 SET @saved_cs_results         = @@character_set_results */;
+/*!50001 SET @saved_col_connection     = @@collation_connection */;
+/*!50001 SET character_set_client      = utf8mb4 */;
+/*!50001 SET character_set_results     = utf8mb4 */;
+/*!50001 SET collation_connection      = utf8mb4_general_ci */;
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`gestion`@`localhost` SQL SECURITY DEFINER */
+/*!50001 VIEW `v_rapprochement_compta` AS select `s`.`id` AS `svc_id`,(case year(`s`.`created`) when 0 then 'import-TTN' else year(`s`.`created`) end) AS `svc_annee_premiere_saisie`,if(((('2015-01-01' < `s`.`date_fin`) or isnull(`s`.`date_fin`)) and ('2015-12-31' > `s`.`date_debut`)),'oui','non') AS `actif2015`,if(((('2016-01-01' < `s`.`date_fin`) or isnull(`s`.`date_fin`)) and ('2016-12-31' > `s`.`date_debut`)),'oui','non') AS `actif2016`,if(((('2017-01-01' < `s`.`date_fin`) or isnull(`s`.`date_fin`)) and ('2017-12-31' > `s`.`date_debut`)),'oui','non') AS `actif2017`,if((unix_timestamp(`s`.`modified`) < unix_timestamp('2015-01-01 00:00:00')),'jamais',`s`.`modified`) AS `svc_derniere_modif`,concat('CHD',`a`.`id`) AS `adt`,`at`.`title` AS `adt_type`,`a`.`raison` AS `raison`,`a`.`nom` AS `nom`,`a`.`prenom` AS `prenom`,`st`.`title` AS `service_type`,`st`.`prix_base_ht` AS `prix_base_ht`,`s`.`prix_ht` AS `prix_ht`,`p`.`title` AS `periodicite`,`s`.`date_debut` AS `date_debut`,`s`.`date_fin` AS `date_fin`,`sst`.`title` AS `statut`,`a`.`proprio` AS `proprio`,`s`.`description` AS `description`,`a`.`notes` AS `notes` from (((((`services` `s` left join `adherents` `a` on((`a`.`id` = `s`.`adherent_id`))) left join `adherent_types` `at` on((`at`.`id` = `a`.`adherent_type_id`))) left join `service_types` `st` on((`st`.`id` = `s`.`service_type_id`))) left join `service_statuts` `sst` on((`sst`.`id` = `s`.`service_statut_id`))) left join `periodicites` `p` on((`p`.`id` = `st`.`periodicite_id`))) where (`s`.`service_statut_id` <> 1) order by `a`.`id`,`s`.`id` */;
+/*!50001 SET character_set_client      = @saved_cs_client */;
+/*!50001 SET character_set_results     = @saved_cs_results */;
+/*!50001 SET collation_connection      = @saved_col_connection */;
+
+--
 -- Final view structure for view `v_relais`
 --
 
@@ -850,6 +1006,25 @@ DELIMITER ;
 /*!50001 SET character_set_client      = @saved_cs_client */;
 /*!50001 SET character_set_results     = @saved_cs_results */;
 /*!50001 SET collation_connection      = @saved_col_connection */;
+
+--
+-- Final view structure for view `v_tmp_diff_seen_mac`
+--
+
+/*!50001 DROP TABLE IF EXISTS `v_tmp_diff_seen_mac`*/;
+/*!50001 DROP VIEW IF EXISTS `v_tmp_diff_seen_mac`*/;
+/*!50001 SET @saved_cs_client          = @@character_set_client */;
+/*!50001 SET @saved_cs_results         = @@character_set_results */;
+/*!50001 SET @saved_col_connection     = @@collation_connection */;
+/*!50001 SET character_set_client      = utf8mb4 */;
+/*!50001 SET character_set_results     = utf8mb4 */;
+/*!50001 SET collation_connection      = utf8mb4_unicode_ci */;
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`gestion`@`localhost` SQL SECURITY INVOKER */
+/*!50001 VIEW `v_tmp_diff_seen_mac` AS select `s`.`mac` AS `mac`,`s`.`ip` AS `seen_ip`,`e`.`ipmgmt_id` AS `gestion_mgmtip` from (`tmp_mac_seen` `s` left join `equipements` `e` on((`e`.`mac` = `s`.`mac`))) where ((not((`s`.`ip` like '%::%'))) and (isnull(`e`.`ipmgmt_id`) or (`s`.`ip` <> `e`.`ipmgmt_id`))) order by 1,2 */;
+/*!50001 SET character_set_client      = @saved_cs_client */;
+/*!50001 SET character_set_results     = @saved_cs_results */;
+/*!50001 SET collation_connection      = @saved_col_connection */;
 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
@@ -860,4 +1035,4 @@ DELIMITER ;
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
--- Dump completed on 2016-08-29 20:20:38
+-- Dump completed on 2018-06-21 22:00:27