summaryrefslogtreecommitdiff
path: root/schema/schema-mysql.sql
diff options
context:
space:
mode:
Diffstat (limited to 'schema/schema-mysql.sql')
-rw-r--r--schema/schema-mysql.sql181
1 files changed, 178 insertions, 3 deletions
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
generated by cgit v1.1 at 2024-05-07 20:06:03 +0000