-- MySQL dump 10.16  Distrib 10.1.26-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: gestion
-- ------------------------------------------------------
-- 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 */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `adherent_role_types`
--

DROP TABLE IF EXISTS `adherent_role_types`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `adherent_role_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `list_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `adherent_roles`
--

DROP TABLE IF EXISTS `adherent_roles`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `adherent_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `adherent_id` int(11) NOT NULL,
  `adherent_role_type_id` int(11) NOT NULL,
  `ville_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_adherent` (`adherent_id`),
  KEY `id_adherent_role_type` (`adherent_role_type_id`),
  KEY `id_ville` (`ville_id`),
  CONSTRAINT `adherent_roles_ibfk_1` FOREIGN KEY (`adherent_id`) REFERENCES `adherents` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `adherent_roles_ibfk_2` FOREIGN KEY (`adherent_role_type_id`) REFERENCES `adherent_role_types` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `adherent_roles_ibfk_3` FOREIGN KEY (`ville_id`) REFERENCES `villes` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `adherent_statuts`
--

DROP TABLE IF EXISTS `adherent_statuts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `adherent_statuts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `adherent_types`
--

DROP TABLE IF EXISTS `adherent_types`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `adherent_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `adherents`
--

DROP TABLE IF EXISTS `adherents`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `adherents` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id = numéro ADT',
  `nom` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `prenom` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `raison` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tel_mobile1` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `adherent_type_id` int(11) NOT NULL,
  `ville_id` int(11) NOT NULL,
  `adherent_statut_id` int(11) NOT NULL,
  `civilite_id` int(11) NOT NULL,
  `nom2` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `prenom2` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mail1` varchar(48) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mail2` varchar(48) COLLATE utf8_unicode_ci DEFAULT NULL,
  `adresse1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `adresse2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tel_fixe1` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tel_fixe2` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tel_mobile2` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `num_adt_ttn` char(6) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date de la première saisie de l''adhérent',
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date de la dernière modification de l''adhérent',
  `proprio` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `ville` (`ville_id`),
  KEY `adherent_type_id` (`adherent_type_id`),
  KEY `civilite_id` (`civilite_id`),
  KEY `adherent_statut` (`adherent_statut_id`),
  CONSTRAINT `adherents_ibfk_1` FOREIGN KEY (`ville_id`) REFERENCES `villes` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `adherents_ibfk_2` FOREIGN KEY (`adherent_type_id`) REFERENCES `adherent_types` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `adherents_ibfk_3` FOREIGN KEY (`civilite_id`) REFERENCES `civilites` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `adherents_ibfk_4` FOREIGN KEY (`adherent_statut_id`) REFERENCES `adherent_statuts` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Adhérents';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `civilites`
--

DROP TABLE IF EXISTS `civilites`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `civilites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `equipement_modeles`
--

DROP TABLE IF EXISTS `equipement_modeles`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `equipement_modeles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `constructeur` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `modele` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `profile` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
  `revision` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Modèles d''équipements';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `equipement_modes`
--

DROP TABLE IF EXISTS `equipement_modes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `equipement_modes` (
  `id` char(8) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `equipements`
--

DROP TABLE IF EXISTS `equipements`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `equipements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uplink_id` int(11) DEFAULT NULL,
  `service_id` int(11) DEFAULT NULL,
  `mac` char(17) COLLATE utf8_unicode_ci NOT NULL,
  `hostname` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `equipement_modele_id` int(11) NOT NULL,
  `ipmgmt_id` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `relais_id` int(11) DEFAULT NULL,
  `equipement_mode_id` char(8) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `date_achat` date DEFAULT NULL,
  `date_hs` date DEFAULT NULL,
  `notes` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mac` (`mac`),
  UNIQUE KEY `ipmgmt_id` (`ipmgmt_id`),
  UNIQUE KEY `service_id` (`service_id`),
  KEY `relais_id` (`relais_id`),
  KEY `equipement_mode_id` (`equipement_mode_id`),
  KEY `equipement_modele_id` (`equipement_modele_id`),
  KEY `uplink_id` (`uplink_id`),
  CONSTRAINT `equipements_ibfk_1` FOREIGN KEY (`equipement_modele_id`) REFERENCES `equipement_modeles` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `equipements_ibfk_4` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`),
  CONSTRAINT `equipements_ibfk_5` FOREIGN KEY (`relais_id`) REFERENCES `relais` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `equipements_ibfk_6` FOREIGN KEY (`equipement_mode_id`) REFERENCES `equipement_modes` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `equipements_ibfk_8` FOREIGN KEY (`uplink_id`) REFERENCES `equipements` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `equipements_ibfk_9` FOREIGN KEY (`ipmgmt_id`) REFERENCES `ipmgmt` (`ip4`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Matériel en circulation';
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`gestion`@`localhost`*/ /*!50003 TRIGGER `equipements_insert` BEFORE INSERT ON `equipements` FOR EACH ROW BEGIN
	-- INSERT NULL instead of '' for VARCHAR columns (prevents UNIQUE FK FAIL)
	SET NEW.ipmgmt_id = IF(NEW.ipmgmt_id = '', NULL, NEW.ipmgmt_id);
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`gestion`@`localhost`*/ /*!50003 TRIGGER `equipements_update` BEFORE UPDATE ON `equipements` FOR EACH ROW BEGIN
	-- INSERT NULL instead of '' for VARCHAR columns (prevents UNIQUE FK FAIL)
	SET NEW.ipmgmt_id = IF(NEW.ipmgmt_id = '', NULL, NEW.ipmgmt_id);
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Table structure for table `interesse_statuts`
--

DROP TABLE IF EXISTS `interesse_statuts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `interesse_statuts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `interesses`
--

DROP TABLE IF EXISTS `interesses`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `interesses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_demande` date NOT NULL,
  `civilite_id` int(11) NOT NULL,
  `nom` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `prenom` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `raison` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ville_id` int(11) NOT NULL,
  `interesse_statut_id` int(11) NOT NULL,
  `telephone` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mail` varchar(48) COLLATE utf8_unicode_ci DEFAULT NULL,
  `adresse1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `adresse2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `civilite_id` (`civilite_id`),
  KEY `ville_id` (`ville_id`),
  KEY `interesse_statut_id` (`interesse_statut_id`),
  CONSTRAINT `interesses_ibfk_1` FOREIGN KEY (`civilite_id`) REFERENCES `civilites` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `interesses_ibfk_2` FOREIGN KEY (`ville_id`) REFERENCES `villes` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `interesses_ibfk_3` FOREIGN KEY (`interesse_statut_id`) REFERENCES `interesse_statuts` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ipmgmt`
--

DROP TABLE IF EXISTS `ipmgmt`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ipmgmt` (
  `ip4` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`ip4`),
  UNIQUE KEY `ip` (`ip4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ippubliques`
--

DROP TABLE IF EXISTS `ippubliques`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ippubliques` (
  `ip4` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `ip6` varchar(39) COLLATE utf8_unicode_ci DEFAULT NULL,
  `secteur_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`ip4`),
  UNIQUE KEY `ip6` (`ip6`),
  KEY `secteur_id` (`secteur_id`),
  CONSTRAINT `ippubliques_ibfk_1` FOREIGN KEY (`secteur_id`) REFERENCES `secteurs` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `periodicites`
--

DROP TABLE IF EXISTS `periodicites`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `periodicites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `relais`
--

DROP TABLE IF EXISTS `relais`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `relais` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `ville_id` int(11) NOT NULL,
  `secteur_id` int(11) NOT NULL,
  `gps_long` float(10,7) NOT NULL,
  `gps_lat` float(10,7) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  KEY `ville_id` (`ville_id`),
  KEY `id_secteur` (`secteur_id`),
  CONSTRAINT `relais_ibfk_2` FOREIGN KEY (`secteur_id`) REFERENCES `secteurs` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `relais_ibfk_3` FOREIGN KEY (`ville_id`) REFERENCES `villes` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `secteurs`
--

DROP TABLE IF EXISTS `secteurs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `secteurs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Différents secteurs de déploiement';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `service_statuts`
--

DROP TABLE IF EXISTS `service_statuts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `service_statuts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `ident` varchar(7) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ident` (`ident`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `service_types`
--

DROP TABLE IF EXISTS `service_types`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `service_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `prix_base_ht` decimal(7,2) NOT NULL,
  `periodicite_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `periodicite_id` (`periodicite_id`),
  CONSTRAINT `service_types_ibfk_1` FOREIGN KEY (`periodicite_id`) REFERENCES `periodicites` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Services disponibles';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `services`
--

DROP TABLE IF EXISTS `services`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `services` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `adherent_id` int(11) NOT NULL,
  `service_type_id` int(11) NOT NULL,
  `service_statut_id` int(11) NOT NULL,
  `ippublique_id` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `prix_ht` decimal(7,2) DEFAULT NULL,
  `date_debut` datetime DEFAULT NULL,
  `date_fin` datetime DEFAULT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `lat` float(10,7) DEFAULT NULL,
  `lng` float(10,7) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_adherent` (`adherent_id`),
  KEY `id_type` (`service_type_id`),
  KEY `service_statut_id` (`service_statut_id`),
  KEY `ip4publique_id` (`ippublique_id`),
  CONSTRAINT `services_ibfk_1` FOREIGN KEY (`adherent_id`) REFERENCES `adherents` (`id`),
  CONSTRAINT `services_ibfk_2` FOREIGN KEY (`service_type_id`) REFERENCES `service_types` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `services_ibfk_4` FOREIGN KEY (`service_statut_id`) REFERENCES `service_statuts` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `services_ibfk_5` FOREIGN KEY (`ippublique_id`) REFERENCES `ippubliques` (`ip4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Services souscrits par les adhérents';
/*!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`
--

DROP TABLE IF EXISTS `v_adt_sms`;
/*!50001 DROP VIEW IF EXISTS `v_adt_sms`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `v_adt_sms` (
  `id` tinyint NOT NULL,
  `tel_mobile1` tinyint NOT NULL,
  `tel_mobile2` tinyint NOT NULL,
  `tel_contact` tinyint NOT NULL,
  `mail_contact` tinyint NOT NULL,
  `relais` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `v_api_gen_conf`
--

DROP TABLE IF EXISTS `v_api_gen_conf`;
/*!50001 DROP VIEW IF EXISTS `v_api_gen_conf`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `v_api_gen_conf` (
  `etat` tinyint NOT NULL,
  `svc_id` tinyint NOT NULL,
  `ip4_nexthop` tinyint NOT NULL,
  `ip4_public` tinyint NOT NULL,
  `ip6_nexthop` tinyint NOT NULL,
  `ip6_prefix` tinyint NOT NULL,
  `mac_nexthop` tinyint NOT NULL,
  `modified_ts` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `v_api_gen_firm`
--

DROP TABLE IF EXISTS `v_api_gen_firm`;
/*!50001 DROP VIEW IF EXISTS `v_api_gen_firm`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `v_api_gen_firm` (
  `ippublique_id` tinyint NOT NULL,
  `nom` tinyint NOT NULL,
  `prenom` tinyint NOT NULL,
  `raison` tinyint NOT NULL,
  `constructeur` tinyint NOT NULL,
  `modele` tinyint NOT NULL,
  `profile` tinyint NOT NULL,
  `revision` tinyint NOT NULL,
  `ip6prefix` tinyint NOT NULL,
  `ip4wan` tinyint NOT NULL
) ENGINE=MyISAM */;
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`
--

DROP TABLE IF EXISTS `v_relais`;
/*!50001 DROP VIEW IF EXISTS `v_relais`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `v_relais` (
  `relais_id` tinyint NOT NULL,
  `relais` tinyint NOT NULL,
  `secteur` tinyint NOT NULL,
  `nb_connectes` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `v_relais_detail`
--

DROP TABLE IF EXISTS `v_relais_detail`;
/*!50001 DROP VIEW IF EXISTS `v_relais_detail`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `v_relais_detail` (
  `relais` tinyint NOT NULL,
  `adt` tinyint NOT NULL,
  `adt_title` tinyint NOT NULL,
  `adherent_id` tinyint NOT NULL,
  `service_id` tinyint NOT NULL,
  `service` tinyint NOT NULL,
  `routeur_equipement_id` tinyint NOT NULL,
  `antenne1_equipement_id` tinyint NOT NULL,
  `antenne2_equipement_id` tinyint NOT NULL,
  `relais_id` tinyint NOT NULL,
  `relais_degre` tinyint NOT NULL,
  `secteur_id` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `v_secteurs`
--

DROP TABLE IF EXISTS `v_secteurs`;
/*!50001 DROP VIEW IF EXISTS `v_secteurs`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `v_secteurs` (
  `id` tinyint NOT NULL,
  `secteur` tinyint NOT NULL,
  `nb_connectes` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `v_sympa_adherents`
--

DROP TABLE IF EXISTS `v_sympa_adherents`;
/*!50001 DROP VIEW IF EXISTS `v_sympa_adherents`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `v_sympa_adherents` (
  `mail` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `v_sympa_referents`
--

DROP TABLE IF EXISTS `v_sympa_referents`;
/*!50001 DROP VIEW IF EXISTS `v_sympa_referents`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `v_sympa_referents` (
  `mail` tinyint NOT NULL,
  `village_list_name` tinyint NOT NULL,
  `sms_list_name` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `v_sympa_roles`
--

DROP TABLE IF EXISTS `v_sympa_roles`;
/*!50001 DROP VIEW IF EXISTS `v_sympa_roles`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `v_sympa_roles` (
  `mail` tinyint NOT NULL,
  `list_name` tinyint NOT NULL
) ENGINE=MyISAM */;
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`
--

DROP TABLE IF EXISTS `villes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `villes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `cp` char(5) COLLATE utf8_unicode_ci NOT NULL,
  `tel_contact` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping routines for database 'gestion'
--
/*!50003 DROP FUNCTION IF EXISTS `CALC_WANIP6LL` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`gestion`@`localhost` FUNCTION `CALC_WANIP6LL`(`ip6_prefix` VARCHAR(39) CHARSET utf8) RETURNS varchar(39) CHARSET utf8 COLLATE utf8_unicode_ci
    NO SQL
    DETERMINISTIC
BEGIN
    DECLARE len INT(11);
    SET len = LENGTH(ip6_prefix);
    
	RETURN IF(ip6_prefix REGEXP ':....:....::$',
              CONCAT('fe80::',RIGHT(LEFT(ip6_prefix,len-2),9)),
              NULL
             );
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP FUNCTION IF EXISTS `FREE_IP` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`gestion`@`localhost` FUNCTION `FREE_IP`(`arg_service_id` INT(11)) RETURNS varchar(15) CHARSET utf8 COLLATE utf8_unicode_ci
    NO SQL
BEGIN

DECLARE res VARCHAR(15);

SELECT ip.ip4 INTO res
FROM ippubliques ip
LEFT JOIN services s ON (s.ippublique_id = ip.ip4)
WHERE s.id IS NULL
AND ip.secteur_id = (
  SELECT secteur_id FROM v_relais_detail
  WHERE service_id=arg_service_id
)
AND ip.secteur_id IS NOT NULL
ORDER BY INET_ATON(ip.ip4)
LIMIT 1;

RETURN res;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Final view structure for view `v_adt_sms`
--

/*!50001 DROP TABLE IF EXISTS `v_adt_sms`*/;
/*!50001 DROP VIEW IF EXISTS `v_adt_sms`*/;
/*!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_adt_sms` AS select concat('CHD-',`a`.`id`) AS `id`,`a`.`tel_mobile1` AS `tel_mobile1`,`a`.`tel_mobile2` AS `tel_mobile2`,`v`.`tel_contact` AS `tel_contact`,concat(lcase(replace(`v`.`title`,' ','-')),'@chd.sx') AS `mail_contact`,`re`.`title` AS `relais` from (((((((`adherents` `a` left join `villes` `v` on((`v`.`id` = `a`.`ville_id`))) left join `services` `s` on((`s`.`adherent_id` = `a`.`id`))) left join `equipements` `rt` on((`rt`.`service_id` = `s`.`id`))) left join `equipements` `a1` on((`a1`.`id` = `rt`.`uplink_id`))) left join `equipements` `a2` on((`a2`.`id` = `a1`.`uplink_id`))) left join `relais` `re` on((`re`.`id` = coalesce(`rt`.`relais_id`,`a1`.`relais_id`,`a2`.`relais_id`)))) left join `secteurs` `sec` on((`sec`.`id` = `re`.`secteur_id`))) where ((`a`.`adherent_statut_id` <> 1) and (`s`.`service_type_id` in (2,3,4)) and (`s`.`service_statut_id` = 4)) */;
/*!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_api_gen_conf`
--

/*!50001 DROP TABLE IF EXISTS `v_api_gen_conf`*/;
/*!50001 DROP VIEW IF EXISTS `v_api_gen_conf`*/;
/*!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_api_gen_conf` AS select `ss`.`ident` AS `etat`,concat('SER-',`s`.`id`) AS `svc_id`,`routeur`.`ipmgmt_id` AS `ip4_nexthop`,`ipp`.`ip4` AS `ip4_public`,`CALC_WANIP6LL`(`ipp`.`ip6`) AS `ip6_nexthop`,`ipp`.`ip6` AS `ip6_prefix`,NULL AS `mac_nexthop`,unix_timestamp(`s`.`modified`) AS `modified_ts` from (((((`services` `s` left join `service_types` `st` on((`st`.`id` = `s`.`service_type_id`))) left join `service_statuts` `ss` on((`ss`.`id` = `s`.`service_statut_id`))) left join `ippubliques` `ipp` on((`ipp`.`ip4` = `s`.`ippublique_id`))) left join `adherents` `a` on((`a`.`id` = `s`.`adherent_id`))) left join `equipements` `routeur` on(((`routeur`.`service_id` = `s`.`id`) and (`routeur`.`equipement_mode_id` in ('rt-soho','rt-sta'))))) where ((`s`.`service_type_id` in (2,3,4)) and (`s`.`service_statut_id` in (4,5,6)) and (`ipp`.`ip4` is not null)) order by `ss`.`ident` desc,`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_api_gen_firm`
--

/*!50001 DROP TABLE IF EXISTS `v_api_gen_firm`*/;
/*!50001 DROP VIEW IF EXISTS `v_api_gen_firm`*/;
/*!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_api_gen_firm` AS select `s`.`ippublique_id` AS `ippublique_id`,`a`.`nom` AS `nom`,`a`.`prenom` AS `prenom`,`a`.`raison` AS `raison`,`em`.`constructeur` AS `constructeur`,`em`.`modele` AS `modele`,`em`.`profile` AS `profile`,`em`.`revision` AS `revision`,`ip`.`ip6` AS `ip6prefix`,`e`.`ipmgmt_id` AS `ip4wan` from ((((`services` `s` left join `adherents` `a` on((`a`.`id` = `s`.`adherent_id`))) left join `ippubliques` `ip` on((`ip`.`ip4` = `s`.`ippublique_id`))) left join `equipements` `e` on((`e`.`service_id` = `s`.`id`))) left join `equipement_modeles` `em` on((`em`.`id` = `e`.`equipement_modele_id`))) where ((`s`.`service_statut_id` = 4) and (`s`.`ippublique_id` is not null)) */;
/*!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_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`
--

/*!50001 DROP TABLE IF EXISTS `v_relais`*/;
/*!50001 DROP VIEW IF EXISTS `v_relais`*/;
/*!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_relais` AS select `v`.`relais_id` AS `relais_id`,`v`.`relais` AS `relais`,`s`.`title` AS `secteur`,count(0) AS `nb_connectes` from (`v_relais_detail` `v` left join `secteurs` `s` on((`v`.`secteur_id` = `s`.`id`))) group by `v`.`relais_id`,`v`.`relais`,`s`.`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_relais_detail`
--

/*!50001 DROP TABLE IF EXISTS `v_relais_detail`*/;
/*!50001 DROP VIEW IF EXISTS `v_relais_detail`*/;
/*!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_relais_detail` AS select `re`.`title` AS `relais`,concat('CHD-',`a`.`id`) AS `adt`,concat_ws(' ',nullif(`a`.`raison`,''),`a`.`nom`,`a`.`prenom`) AS `adt_title`,`a`.`id` AS `adherent_id`,`s`.`id` AS `service_id`,concat('SER-',`s`.`id`) AS `service`,`rt`.`id` AS `routeur_equipement_id`,`a1`.`id` AS `antenne1_equipement_id`,`a2`.`id` AS `antenne2_equipement_id`,`re`.`id` AS `relais_id`,(case when (`rt`.`relais_id` is not null) then 1 when (`a1`.`relais_id` is not null) then 2 when (`a2`.`relais_id` is not null) then 3 else NULL end) AS `relais_degre`,`sec`.`id` AS `secteur_id` from ((((((`adherents` `a` left join `services` `s` on((`s`.`adherent_id` = `a`.`id`))) left join `equipements` `rt` on((`rt`.`service_id` = `s`.`id`))) left join `equipements` `a1` on((`a1`.`id` = `rt`.`uplink_id`))) left join `equipements` `a2` on((`a2`.`id` = `a1`.`uplink_id`))) left join `relais` `re` on((`re`.`id` = coalesce(`rt`.`relais_id`,`a1`.`relais_id`,`a2`.`relais_id`)))) left join `secteurs` `sec` on((`sec`.`id` = `re`.`secteur_id`))) where ((`a`.`adherent_statut_id` <> 1) and (`s`.`service_type_id` in (2,3,4)) and (`s`.`service_statut_id` in (1,2,4))) order by 1 */;
/*!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_secteurs`
--

/*!50001 DROP TABLE IF EXISTS `v_secteurs`*/;
/*!50001 DROP VIEW IF EXISTS `v_secteurs`*/;
/*!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_secteurs` AS select `s`.`id` AS `id`,`s`.`title` AS `secteur`,count(0) AS `nb_connectes` from (`v_relais_detail` `v` left join `secteurs` `s` on((`v`.`secteur_id` = `s`.`id`))) group by `s`.`id`,`s`.`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_sympa_adherents`
--

/*!50001 DROP TABLE IF EXISTS `v_sympa_adherents`*/;
/*!50001 DROP VIEW IF EXISTS `v_sympa_adherents`*/;
/*!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_sympa_adherents` AS select distinct `a`.`mail1` AS `mail` from `adherents` `a` where ((length(`a`.`mail1`) > 5) and (`a`.`adherent_statut_id` <> 1)) */;
/*!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_sympa_referents`
--

/*!50001 DROP TABLE IF EXISTS `v_sympa_referents`*/;
/*!50001 DROP VIEW IF EXISTS `v_sympa_referents`*/;
/*!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_sympa_referents` AS select `a`.`mail1` AS `mail`,lcase(replace(`v`.`title`,' ','-')) AS `village_list_name`,`v`.`tel_contact` AS `sms_list_name` from ((`adherent_roles` `ar` left join `adherents` `a` on((`ar`.`adherent_id` = `a`.`id`))) left join `villes` `v` on((`ar`.`ville_id` = `v`.`id`))) where ((`ar`.`adherent_role_type_id` = 7) and (length(`a`.`mail1`) > 5)) */;
/*!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_sympa_roles`
--

/*!50001 DROP TABLE IF EXISTS `v_sympa_roles`*/;
/*!50001 DROP VIEW IF EXISTS `v_sympa_roles`*/;
/*!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_sympa_roles` AS select distinct `a`.`mail1` AS `mail`,`art`.`list_name` AS `list_name` from ((`adherent_roles` `ar` left join `adherents` `a` on((`ar`.`adherent_id` = `a`.`id`))) left join `adherent_role_types` `art` on((`ar`.`adherent_role_type_id` = `art`.`id`))) where (length(`a`.`mail1`) > 5) */;
/*!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 */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-06-21 22:00:27