-- MySQL dump 10.13  Distrib 5.5.47, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: gestion
-- ------------------------------------------------------
-- Server version	5.5.47-0+deb8u1

/*!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 NOT NULL,
  `revision` varchar(8) COLLATE utf8_unicode_ci NOT 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,
  `mac` char(17) COLLATE utf8_unicode_ci NOT NULL,
  `uplink_id` int(11) DEFAULT NULL,
  `service_id` int(11) DEFAULT NULL,
  `ipmgmt_id` int(11) DEFAULT NULL,
  `equipement_modele_id` int(11) NOT NULL,
  `equipement_mode_id` char(8) COLLATE utf8_unicode_ci NOT NULL,
  `relais_id` int(11) DEFAULT NULL,
  `description` varchar(32) 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`),
  KEY `relais_id` (`relais_id`),
  KEY `equipement_mode_id` (`equipement_mode_id`),
  KEY `equipement_modele_id` (`equipement_modele_id`),
  KEY `service_id` (`service_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_7` FOREIGN KEY (`ipmgmt_id`) REFERENCES `ipmgmt` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `equipements_ibfk_8` FOREIGN KEY (`uplink_id`) REFERENCES `equipements` (`id`) 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 */;

--
-- 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,
  `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`),
  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
) 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` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip4` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ip6` varchar(39) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ip` (`ip4`),
  UNIQUE KEY `ip6` (`ip6`)
) 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,
  PRIMARY KEY (`id`)
) 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 */;

--
-- 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` (
  `do_conf` tinyint NOT NULL,
  `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
) 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,
  `ip6wanll` 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,
  `routeur_equipement_id` tinyint NOT NULL,
  `antenne1_equipement_id` tinyint NOT NULL,
  `antenne2_equipement_id` tinyint NOT NULL,
  `relais_id` 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;

--
-- 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 */;

--
-- 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 'do_conf' AS `do_conf`,'actif' AS `etat`,concat_ws('-','SER',`s`.`service_type_id`,`a`.`id`,`s`.`id`) AS `svc_id`,`ipr`.`ip4` AS `ip4_nexthop`,`ipp`.`ip4` AS `ip4_public`,coalesce(`ipr`.`ip6`,`CALC_WANIP6LL`(`ipp`.`ip6`)) AS `ip6_nexthop`,`ipp`.`ip6` AS `ip6_prefix` from (((((`services` `s` left join `service_types` `st` on((`st`.`id` = `s`.`service_type_id`))) left join `ippubliques` `ipp` on((`ipp`.`ip4` = `s`.`ippublique_id`))) left join `adherents` `a` on((`s`.`adherent_id` = `a`.`id`))) left join `equipements` `routeur` on(((`routeur`.`service_id` = `s`.`id`) and (`routeur`.`equipement_mode_id` in ('rt-soho','rt-sta'))))) left join `ipmgmt` `ipr` on((`ipr`.`id` = `routeur`.`ipmgmt_id`))) where ((`s`.`service_type_id` in (2,3,4)) and (`s`.`service_statut_id` in (2,4)) 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_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`,`ip2`.`ip4` AS `ip4wan`,`ip2`.`ip6` AS `ip6wanll` 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`))) left join `ipmgmt` `ip2` on((`ip2`.`id` = `e`.`ipmgmt_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_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`,`rt`.`id` AS `routeur_equipement_id`,`a1`.`id` AS `antenne1_equipement_id`,`a2`.`id` AS `antenne2_equipement_id`,`re`.`id` AS `relais_id`,`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 */;
/*!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 2016-03-12  0:30:48
