-- http://www.phpmyadmin.net
--
-- Client: localhost
-- Généré le: Mar 08 Juillet 2014 à 00:08
-- Version du serveur: 5.6.12-log
-- Version de PHP: 5.4.12




--
-- Base de données: 

-- --------------------------------------------------------

--
-- Structure de la table `action`
--

CREATE TABLE IF NOT EXISTS `action` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `projet_id` int(11) DEFAULT NULL,
  `statut` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `date_planifier` date NOT NULL,
  `heure_planifier` time NOT NULL,
  `duree_planifier` int(11) NOT NULL,
  `lieu_planifier` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `support` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `date_realisation` date DEFAULT NULL,
  `lieu_realisation` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `commentaire_planifier` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `compte_rendu` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `next_step` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_406089A4C18272` (`projet_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=15 ;

--
-- Contenu de la table `action`
--

INSERT INTO `action` (`id`, `projet_id`, `statut`, `type`, `date_planifier`, `heure_planifier`, `duree_planifier`, `lieu_planifier`, `support`, `date_realisation`, `lieu_realisation`, `commentaire_planifier`, `compte_rendu`, `next_step`) VALUES
(1, 1, '0', '2', '2014-07-01', '17:03:00', 1, 'Tunis', 'Tunis', NULL, NULL, 'Tunis', NULL, NULL),
(2, 1, '0', '1', '2014-06-25', '00:00:00', 1, 'dd', 'dd', NULL, NULL, 'dd', NULL, NULL),
(3, 2, '0', '1', '2014-06-28', '10:00:00', 1, 'honi', 'honi', NULL, NULL, 'honi', NULL, NULL),
(4, 1, '1', '1', '2014-06-26', '10:00:00', 1, 'honi', 'honi', '2014-06-30', 'honi', 'honi', 'honi', 'honi'),
(5, 1, '0', '1', '2014-06-26', '17:00:00', 1, 'honi', 'honi', NULL, NULL, 'honi', NULL, NULL),
(6, 1, '0', '1', '2014-06-26', '00:00:00', 2, 'D', 'D', NULL, NULL, 'D', NULL, NULL),
(7, 1, '0', '1', '2014-06-25', '00:00:00', 1, 'kk', 'hh', NULL, NULL, 'h', NULL, NULL),
(8, 1, '0', '1', '2014-06-18', '00:00:00', 1, 'z', 's', NULL, NULL, 's', NULL, NULL),
(9, 1, '1', '1', '2014-06-26', '00:00:00', 1, 'g', 'xx', '2014-06-30', 'gg', 'ss', 'gg', 'g'),
(10, 1, '0', '1', '2014-06-03', '00:00:00', 1, 'gg', 'hh', NULL, NULL, NULL, NULL, NULL),
(11, 1, '0', '1', '2014-06-27', '17:00:00', 2, 'aaa', 'aaa', NULL, NULL, 'aaa', NULL, NULL),
(12, 1, '0', '1', '2014-06-28', '08:10:00', 2, 'Bardo', 'cahier de charges', NULL, NULL, NULL, NULL, NULL),
(13, 1, '0', '1', '2014-07-09', '08:05:00', 1, 'hhh', 'hhh', NULL, NULL, 'hhh', NULL, NULL),
(14, 1, '0', '1', '2014-07-09', '08:05:00', 1, 'hhh', 'hhh', NULL, NULL, 'hhh', NULL, NULL);

-- --------------------------------------------------------

--
-- Structure de la table `actions_consultants`
--

CREATE TABLE IF NOT EXISTS `actions_consultants` (
  `action_id` int(11) NOT NULL,
  `consultant_id` int(11) NOT NULL,
  PRIMARY KEY (`action_id`,`consultant_id`),
  KEY `IDX_5190DA279D32F035` (`action_id`),
  KEY `IDX_5190DA2744F779A2` (`consultant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Contenu de la table `actions_consultants`
--

INSERT INTO `actions_consultants` (`action_id`, `consultant_id`) VALUES
(4, 2),
(9, 2);

-- --------------------------------------------------------

--
-- Structure de la table `actions_contacts`
--

CREATE TABLE IF NOT EXISTS `actions_contacts` (
  `action_id` int(11) NOT NULL,
  `contact_id` int(11) NOT NULL,
  PRIMARY KEY (`action_id`,`contact_id`),
  KEY `IDX_ECE7A0509D32F035` (`action_id`),
  KEY `IDX_ECE7A050E7A1254A` (`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Contenu de la table `actions_contacts`
--

INSERT INTO `actions_contacts` (`action_id`, `contact_id`) VALUES
(4, 1),
(9, 1);

-- --------------------------------------------------------

--
-- Structure de la table `actions_invites`
--

CREATE TABLE IF NOT EXISTS `actions_invites` (
  `action_id` int(11) NOT NULL,
  `consultant_id` int(11) NOT NULL,
  PRIMARY KEY (`action_id`,`consultant_id`),
  KEY `IDX_C4248AC09D32F035` (`action_id`),
  KEY `IDX_C4248AC044F779A2` (`consultant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Contenu de la table `actions_invites`
--

INSERT INTO `actions_invites` (`action_id`, `consultant_id`) VALUES
(1, 1),
(1, 2),
(2, 2),
(3, 2),
(4, 1),
(5, 1),
(5, 2),
(6, 1),
(7, 1),
(8, 1),
(9, 1),
(10, 1),
(11, 1),
(11, 2),
(12, 1),
(12, 2),
(13, 1),
(13, 2),
(14, 1),
(14, 2);

-- --------------------------------------------------------

--
-- Structure de la table `activite`
--

CREATE TABLE IF NOT EXISTS `activite` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `secteur_id` int(11) DEFAULT NULL,
  `nom` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_410337436C6E55B5` (`nom`),
  KEY `IDX_410337439F7E4405` (`secteur_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;

--
-- Contenu de la table `activite`
--

INSERT INTO `activite` (`id`, `secteur_id`, `nom`) VALUES
(1, 1, 'Assurance'),
(2, 2, 'Banque'),
(3, 3, 'Leasing'),
(4, 3, 'Factoring'),
(5, 3, 'Microfinance');

-- --------------------------------------------------------

--
-- Structure de la table `consultant`
--

CREATE TABLE IF NOT EXISTS `consultant` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `prenom` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `code` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `User_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_B4F817177153098` (`code`),
  UNIQUE KEY `UNIQ_B4F817168D3EA09` (`User_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

--
-- Contenu de la table `consultant`
--

INSERT INTO `consultant` (`id`, `nom`, `prenom`, `code`, `User_id`) VALUES
(1, 'Weriemi', 'Sofiene', 'SO', 2),
(2, 'Emna', 'Emna', 'Emna', 3);

-- --------------------------------------------------------

--
-- Structure de la table `contact`
--

CREATE TABLE IF NOT EXISTS `contact` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `relation_id` int(11) DEFAULT NULL,
  `nom` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `fonction` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `mail` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `telephone` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_83DFDFA45126AC48` (`mail`),
  KEY `IDX_83DFDFA43256915B` (`relation_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=21 ;

--
-- Contenu de la table `contact`
--

INSERT INTO `contact` (`id`, `relation_id`, `nom`, `fonction`, `mail`, `telephone`) VALUES
(1, 1, 'IBM', 'Directeur commercial', 'IBM@IBM.IBM', '22222222'),
(7, 13, 'IBM', 'Administrateur', 'IBM2@IBM.IBM', '11111111'),
(8, 14, 'Malek', 'Administrateur', 'Malek@Malek', '11111111'),
(9, 15, 'PA', 'Administrateur', 'PA@PA', '11111111'),
(12, 17, 'Biat', 'Administrateur', 'Biat@Biat.Biat', '11111111'),
(13, 18, 'Malek', 'Administrateur', 'Malek@Malekkk', '11111111'),
(14, 18, 'Malek', 'Administrateur', 'Malek@Malekk', '22222222'),
(15, 19, 'azerty', 'Directeur Stratégie', 'azert@azerty', '22333445'),
(16, 20, 'azerty', 'Risk Manager', 'azerty@azerty', '33445566'),
(17, 20, 'rtyuio', 'Directeur commercial', 'azerty@az', '55666777'),
(18, 20, 'Ibrahim Ben  Yedder', 'Directeur Général', 'IbrahimBY@Gmail.com', '34567845'),
(19, 21, 'azetr', 'Risk Manager', 'zaert@ffghh', '334566'),
(20, 21, 'arfer', 'Directeur Stratégie', 'zferf@dzkj', '66777777');

-- --------------------------------------------------------

--
-- Structure de la table `notification`
--

CREATE TABLE IF NOT EXISTS `notification` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `consultant_id` int(11) DEFAULT NULL,
  `msg` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `entity` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `entity_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_A765AD3244F779A2` (`consultant_id`),
  KEY `IDX_A765AD32A76ED395` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=12 ;

--
-- Contenu de la table `notification`
--

INSERT INTO `notification` (`id`, `user_id`, `consultant_id`, `msg`, `entity`, `entity_id`) VALUES
(1, NULL, 1, 'Vous ete charger de la relation: ', 'relation_show', 1),
(2, NULL, 1, 'Vous ete charger de la relation: ', 'relation_show', 13),
(3, NULL, 2, 'Vous ete charger de la relation: ', 'relation_show', 14),
(4, 1, 1, 'Vous ete charger de la relation: ', 'relation_show', 15),
(5, 1, 1, 'Vous ete charger de la relation: ', 'relation_show', 17),
(6, 1, 1, 'Vous ete charger de la relation: ', 'relation_show', 18),
(7, 1, 1, 'Vous ete charger de la relation: ', 'relation_show', 19),
(8, 1, 1, 'Vous ete charger de la relation: ', 'relation_show', 20),
(9, 1, 1, 'Vous ete charger de la relation: ', 'relation_show', 21),
(10, 1, 1, 'Vous ete invite à l''action: ', 'action_show', 14),
(11, 1, 2, 'Vous ete invite à l''action: ', 'action_show', 14);

-- --------------------------------------------------------

--
-- Structure de la table `projet`
--

CREATE TABLE IF NOT EXISTS `projet` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `relation_id` int(11) DEFAULT NULL,
  `consultant_id` int(11) DEFAULT NULL,
  `contact_id` int(11) DEFAULT NULL,
  `nom` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `avancement` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `debut` date NOT NULL,
  `fin` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_57B9999F3256915B` (`relation_id`),
  KEY `IDX_57B9999F44F779A2` (`consultant_id`),
  KEY `IDX_57B9999FE7A1254A` (`contact_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=9 ;

--
-- Contenu de la table `projet`
--

INSERT INTO `projet` (`id`, `relation_id`, `consultant_id`, `contact_id`, `nom`, `type`, `avancement`, `debut`, `fin`) VALUES
(1, 14, 1, 8, 'IBM', '0', '0', '2014-06-01', '2014-06-30'),
(2, 1, 1, 1, 'IM', '1', '1', '2015-12-06', '2016-06-06'),
(3, 1, 1, 1, 'IBM', '0', '0', '2016-02-06', NULL),
(5, 14, 1, 8, 'IBM', '0', '0', '2014-06-01', '2014-06-30'),
(6, 1, 1, 1, 'azerty', '0', '0', '2014-07-01', '2014-07-02'),
(7, 20, 1, 18, 'azerty', '0', '1', '2014-07-04', NULL),
(8, 1, 1, 1, 'MM', '0', '0', '2014-07-08', NULL);

-- --------------------------------------------------------

--
-- Structure de la table `relation`
--

CREATE TABLE IF NOT EXISTS `relation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `secteur_id` int(11) DEFAULT NULL,
  `source_consultant_id` int(11) DEFAULT NULL,
  `activite_id` int(11) DEFAULT NULL,
  `charger_consultant_id` int(11) DEFAULT NULL,
  `nom` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `groupe` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `adresse` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `statut` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_9BFF251F6C6E55B5` (`nom`),
  KEY `IDX_9BFF251F9F7E4405` (`secteur_id`),
  KEY `IDX_9BFF251F819BA8C0` (`source_consultant_id`),
  KEY `IDX_9BFF251F9B0F88B1` (`activite_id`),
  KEY `IDX_9BFF251F1F97BCE3` (`charger_consultant_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=22 ;

--
-- Contenu de la table `relation`
--

INSERT INTO `relation` (`id`, `secteur_id`, `source_consultant_id`, `activite_id`, `charger_consultant_id`, `nom`, `groupe`, `adresse`, `statut`) VALUES
(1, 1, 1, 1, 1, 'IBM', 'IBM', 'IBM', 0),
(13, 1, 2, 1, 1, 'IBM22', 'IBM', 'IBM', 1),
(14, 1, 1, 1, 1, 'querty', 'Malek', 'azerty', 0),
(15, 1, 1, 1, 1, 'PA', 'PA', 'PA', 0),
(17, 2, 2, 2, 1, 'BIAT', 'Biat', 'Biat', 1),
(18, 1, 1, 1, 1, 'abc', 'Malek', 'abc', 0),
(19, 3, 2, 4, 1, 'azerty', 'azerty', 'azerty 56 azerty', 1),
(20, 2, 1, 2, 1, 'RTR', 'azerty', 'azerty', 1),
(21, 3, 1, 3, 1, 'IBMM', 'azerty', 'azerty', 1);

-- --------------------------------------------------------

--
-- Structure de la table `secteur`
--

CREATE TABLE IF NOT EXISTS `secteur` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `code` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_4FF81C836C6E55B5` (`nom`),
  UNIQUE KEY `UNIQ_4FF81C8377153098` (`code`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;

--
-- Contenu de la table `secteur`
--

INSERT INTO `secteur` (`id`, `nom`, `code`) VALUES
(1, 'Assurance', 'ASS'),
(2, 'Banque', 'BQ'),
(3, 'SFS', 'SFS');

--
-- Contraintes pour les tables exportées
--

--
-- Contraintes pour la table `action`
--
ALTER TABLE `action`
  ADD CONSTRAINT `FK_406089A4C18272` FOREIGN KEY (`projet_id`) REFERENCES `projet` (`id`);

--
-- Contraintes pour la table `actions_consultants`
--
ALTER TABLE `actions_consultants`
  ADD CONSTRAINT `FK_5190DA2744F779A2` FOREIGN KEY (`consultant_id`) REFERENCES `consultant` (`id`),
  ADD CONSTRAINT `FK_5190DA279D32F035` FOREIGN KEY (`action_id`) REFERENCES `action` (`id`);

--
-- Contraintes pour la table `actions_contacts`
--
ALTER TABLE `actions_contacts`
  ADD CONSTRAINT `FK_ECE7A0509D32F035` FOREIGN KEY (`action_id`) REFERENCES `action` (`id`),
  ADD CONSTRAINT `FK_ECE7A050E7A1254A` FOREIGN KEY (`contact_id`) REFERENCES `contact` (`id`);

--
-- Contraintes pour la table `actions_invites`
--
ALTER TABLE `actions_invites`
  ADD CONSTRAINT `FK_C4248AC044F779A2` FOREIGN KEY (`consultant_id`) REFERENCES `consultant` (`id`),
  ADD CONSTRAINT `FK_C4248AC09D32F035` FOREIGN KEY (`action_id`) REFERENCES `action` (`id`);

--
-- Contraintes pour la table `activite`
--
ALTER TABLE `activite`
  ADD CONSTRAINT `FK_410337439F7E4405` FOREIGN KEY (`secteur_id`) REFERENCES `secteur` (`id`);

--
-- Contraintes pour la table `consultant`
--
ALTER TABLE `consultant`
  ADD CONSTRAINT `FK_B4F817168D3EA09` FOREIGN KEY (`User_id`) REFERENCES `fos_user` (`id`);

--
-- Contraintes pour la table `contact`
--
ALTER TABLE `contact`
  ADD CONSTRAINT `FK_83DFDFA43256915B` FOREIGN KEY (`relation_id`) REFERENCES `relation` (`id`);

--
-- Contraintes pour la table `notification`
--
ALTER TABLE `notification`
  ADD CONSTRAINT `FK_A765AD3244F779A2` FOREIGN KEY (`consultant_id`) REFERENCES `consultant` (`id`),
  ADD CONSTRAINT `FK_A765AD32A76ED395` FOREIGN KEY (`user_id`) REFERENCES `fos_user` (`id`);

--
-- Contraintes pour la table `projet`
--
ALTER TABLE `projet`
  ADD CONSTRAINT `FK_57B9999F3256915B` FOREIGN KEY (`relation_id`) REFERENCES `relation` (`id`),
  ADD CONSTRAINT `FK_57B9999F44F779A2` FOREIGN KEY (`consultant_id`) REFERENCES `consultant` (`id`),
  ADD CONSTRAINT `FK_57B9999FE7A1254A` FOREIGN KEY (`contact_id`) REFERENCES `contact` (`id`);

--
-- Contraintes pour la table `relation`
--
ALTER TABLE `relation`
  ADD CONSTRAINT `FK_9BFF251F1F97BCE3` FOREIGN KEY (`charger_consultant_id`) REFERENCES `consultant` (`id`),
  ADD CONSTRAINT `FK_9BFF251F819BA8C0` FOREIGN KEY (`source_consultant_id`) REFERENCES `consultant` (`id`),
  ADD CONSTRAINT `FK_9BFF251F9B0F88B1` FOREIGN KEY (`activite_id`) REFERENCES `activite` (`id`),
  ADD CONSTRAINT `FK_9BFF251F9F7E4405` FOREIGN KEY (`secteur_id`) REFERENCES `secteur` (`id`);

