7325U3QP

1CREATE DATABASE `rvo` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci */;
2
3DROP TABLE IF EXISTS `rvo`.`compliance_partenaire`;
4CREATE TABLE  `rvo`.`compliance_partenaire` (
5  `id` int(11) NOT NULL AUTO_INCREMENT,
6  `compliance_partenaire` varchar(50) DEFAULT NULL,
7  PRIMARY KEY (`id`)
8) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
9
10DROP TABLE IF EXISTS `rvo`.`demande_validation`;
11CREATE TABLE  `rvo`.`demande_validation` (
12  `id` int(10) NOT NULL AUTO_INCREMENT,
13  `valideur` int(10) unsigned DEFAULT NULL,
14  `offre` int(11) DEFAULT NULL,
15  `commentaire` text,
16  `statut` tinyint(1) DEFAULT NULL,
17  `created_ta` datetime DEFAULT NULL,
18  PRIMARY KEY (`id`),
19  KEY `offre` (`offre`),
20  KEY `valideur` (`valideur`),
21  CONSTRAINT `FK_demande_validation_offres` FOREIGN KEY (`offre`) REFERENCES `offres` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
22  CONSTRAINT `FK_demande_validation_oft_users` FOREIGN KEY (`valideur`) REFERENCES `oft_users` (`id_user`) ON DELETE CASCADE ON UPDATE CASCADE
23) ENGINE=InnoDB DEFAULT CHARSET=latin1;
24
25DROP TABLE IF EXISTS `rvo`.`devises`;
26CREATE TABLE  `rvo`.`devises` (
27  `id` int(11) NOT NULL AUTO_INCREMENT,
28  `devise` varchar(50) DEFAULT NULL,
29  PRIMARY KEY (`id`)
30) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
31
32DROP TABLE IF EXISTS `rvo`.`documnets`;
33CREATE TABLE  `rvo`.`documnets` (
34  `id` int(11) NOT NULL AUTO_INCREMENT,
35  `offre` int(11) DEFAULT NULL,
36  `nom_document` varchar(50) DEFAULT NULL,
37  `created_at` datetime DEFAULT NULL,
38  PRIMARY KEY (`id`),
39  KEY `offre` (`offre`),
40  CONSTRAINT `FK_documnets_offres` FOREIGN KEY (`offre`) REFERENCES `offres` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
41) ENGINE=InnoDB DEFAULT CHARSET=latin1;
42
43DROP TABLE IF EXISTS `rvo`.`engagement_partenaire`;
44CREATE TABLE  `rvo`.`engagement_partenaire` (
45  `id` int(11) NOT NULL AUTO_INCREMENT,
46  `engagement_partenaire` varchar(50) DEFAULT NULL,
47  PRIMARY KEY (`id`)
48) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
49
50DROP TABLE IF EXISTS `rvo`.`offres`;
51CREATE TABLE  `rvo`.`offres` (
52  `id` int(11) NOT NULL AUTO_INCREMENT,
53  `pays` int(11) DEFAULT NULL,
54  `type_offre` int(11) DEFAULT NULL,
55  `devise_offre` int(11) DEFAULT NULL,
56  `devise_paiement` int(11) DEFAULT NULL,
57  `compliance_partenaire_commercial` int(11) DEFAULT NULL,
58  `reponse_groupement` int(11) DEFAULT NULL,
59  `compliance_partenaire_technique` int(11) DEFAULT NULL,
60  `engagement_partenairer_technique` int(11) DEFAULT NULL,
61  `type_client` int(11) DEFAULT NULL,
62  `type_contrat` int(11) DEFAULT NULL,
63  `reference_poposition` varchar(50) DEFAULT NULL,
64  `statut_proposition` varchar(50) DEFAULT NULL,
65  `reserve` text,
66  `niveau_validation` varchar(50) DEFAULT NULL,
67  `titre_proposition` varchar(200) DEFAULT NULL,
68  `nom_client` varchar(50) DEFAULT NULL,
69  `reference_affaire` varchar(50) DEFAULT NULL,
70  `deadline_remise_proposition` date DEFAULT NULL,
71  `date_proposition` date DEFAULT NULL,
72  `commentaires` text,
73  `proposition_complexe` tinyint(4) DEFAULT NULL,
74  `proposition_renouvellement` tinyint(4) DEFAULT NULL,
75  `contrat_orange_alliance` tinyint(4) DEFAULT NULL,
76  `pourcentage_confiance` float DEFAULT NULL,
77  `client_red_flag` tinyint(4) DEFAULT NULL,
78  `entites_parties_prenantes` text,
79  `porteur_offre_technique` varchar(50) DEFAULT NULL,
80  `montant_total_ht_eur` double DEFAULT NULL,
81  `taux_eur` float DEFAULT NULL,
82  `montant_total_ht_dev` double DEFAULT NULL,
83  `auto_liquidation_tva` tinyint(4) DEFAULT NULL,
84  `marge_nette` float DEFAULT NULL,
85  `marge_brute` float DEFAULT NULL,
86  `mbi` float DEFAULT NULL,
87  `taux_wht` float DEFAULT NULL,
88  `prise_charge_wht` varchar(50) DEFAULT NULL,
89  `bid_bond` float DEFAULT NULL,
90  `performance_bond` float DEFAULT NULL,
91  `delai_paiement` float DEFAULT NULL,
92  `paiement_livraison` tinyint(4) DEFAULT NULL,
93  `acompte` tinyint(4) DEFAULT NULL,
94  `acompte_contre_garantie` tinyint(4) DEFAULT NULL,
95  `cash_flow_positif` tinyint(4) DEFAULT NULL,
96  `echeancier_paiement` text,
97  `nom_partenaire_commercial` varchar(50) DEFAULT NULL,
98  `commission_affaire` tinyint(4) DEFAULT NULL,
99  `nom_partenaire_technique` varchar(60) DEFAULT NULL,
100  `part_sous_traitance` float DEFAULT NULL,
101  `provision_risques_techniques` float DEFAULT NULL,
102  `provision_negociation` float DEFAULT NULL,
103  `provision_frais_bancaires` float DEFAULT NULL,
104  `etablissement_stable_requis` tinyint(4) DEFAULT NULL,
105  `contraintes_juridiques_particulieres` text,
106  `transfert_personnel` tinyint(4) DEFAULT NULL,
107  `immobilisation_partielle_couts` tinyint(4) DEFAULT NULL,
108  `user` int(11) DEFAULT NULL,
109  `rounds` tinyint(4) DEFAULT NULL,
110  `validation` tinyint(4) DEFAULT NULL,
111  `demandes_validation` tinyint(4) DEFAULT NULL,
112  `accords` tinyint(4) DEFAULT NULL,
113  `refus` tinyint(4) DEFAULT NULL,
114  `created_ta` datetime DEFAULT NULL,
115  `updated_ta` datetime DEFAULT NULL,
116  PRIMARY KEY (`id`),
117  KEY `compliance_partenaire_commercial` (`compliance_partenaire_commercial`),
118  KEY `compliance_partenaire_technique` (`compliance_partenaire_technique`),
119  KEY `devise_offre` (`devise_offre`),
120  KEY `engagement_partenairer_technique` (`engagement_partenairer_technique`),
121  KEY `reponse_groupement` (`reponse_groupement`),
122  KEY `devise_paiement` (`devise_paiement`),
123  KEY `pays` (`pays`),
124  KEY `type_offre` (`type_offre`),
125  KEY `FK_offres_type_client` (`type_client`),
126  KEY `FK_offres_type_contrat` (`type_contrat`),
127  CONSTRAINT `FK_offres_compliance_partenaire` FOREIGN KEY (`compliance_partenaire_commercial`) REFERENCES `compliance_partenaire` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
128  CONSTRAINT `FK_offres_compliance_partenaire_technique` FOREIGN KEY (`compliance_partenaire_technique`) REFERENCES `compliance_partenaire` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
129  CONSTRAINT `FK_offres_devises` FOREIGN KEY (`devise_offre`) REFERENCES `devises` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
130  CONSTRAINT `FK_offres_devises_paiement` FOREIGN KEY (`devise_paiement`) REFERENCES `devises` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
131  CONSTRAINT `FK_offres_engagement_partenaire` FOREIGN KEY (`engagement_partenairer_technique`) REFERENCES `engagement_partenaire` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
132  CONSTRAINT `FK_offres_pays` FOREIGN KEY (`pays`) REFERENCES `pays` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
133  CONSTRAINT `FK_offres_reponse_groupement` FOREIGN KEY (`reponse_groupement`) REFERENCES `reponse_groupement` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
134  CONSTRAINT `FK_offres_type_client` FOREIGN KEY (`type_client`) REFERENCES `type_client` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
135  CONSTRAINT `FK_offres_type_contrat` FOREIGN KEY (`type_contrat`) REFERENCES `type_contrat` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
136  CONSTRAINT `FK_offres_types_offres` FOREIGN KEY (`type_offre`) REFERENCES `types_offres` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
137) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=latin1;
138
139DROP TABLE IF EXISTS `rvo`.`oft_acl_resources`;
140CREATE TABLE  `rvo`.`oft_acl_resources` (
141  `id_acl_resource` int(10) unsigned NOT NULL AUTO_INCREMENT,
142  `name` varchar(150) NOT NULL,
143  PRIMARY KEY (`id_acl_resource`),
144  UNIQUE KEY `ux_acl_resource_name` (`name`)
145) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
146
147DROP TABLE IF EXISTS `rvo`.`oft_acl_role_resource`;
148CREATE TABLE  `rvo`.`oft_acl_role_resource` (
149  `id_acl_role` int(10) unsigned NOT NULL,
150  `id_acl_resource` int(10) unsigned NOT NULL,
151  PRIMARY KEY (`id_acl_role`,`id_acl_resource`),
152  KEY `fk_acl_roles_ressource` (`id_acl_resource`),
153  CONSTRAINT `fk_acl_roles_ressource` FOREIGN KEY (`id_acl_resource`) REFERENCES `oft_acl_resources` (`id_acl_resource`) ON DELETE CASCADE ON UPDATE CASCADE,
154  CONSTRAINT `fk_acl_roles_role` FOREIGN KEY (`id_acl_role`) REFERENCES `oft_acl_roles` (`id_acl_role`) ON DELETE CASCADE ON UPDATE CASCADE
155) ENGINE=InnoDB DEFAULT CHARSET=utf8;
156
157DROP TABLE IF EXISTS `rvo`.`oft_acl_role_user`;
158CREATE TABLE  `rvo`.`oft_acl_role_user` (
159  `id_acl_role` int(10) unsigned NOT NULL,
160  `id_user` int(10) unsigned NOT NULL,
161  PRIMARY KEY (`id_acl_role`,`id_user`),
162  KEY `fk_acl_role_user` (`id_user`),
163  CONSTRAINT `fk_acl_role_role` FOREIGN KEY (`id_acl_role`) REFERENCES `oft_acl_roles` (`id_acl_role`) ON DELETE CASCADE ON UPDATE CASCADE,
164  CONSTRAINT `fk_acl_role_user` FOREIGN KEY (`id_user`) REFERENCES `oft_users` (`id_user`) ON DELETE CASCADE ON UPDATE CASCADE
165) ENGINE=InnoDB DEFAULT CHARSET=utf8;
166
167DROP TABLE IF EXISTS `rvo`.`oft_acl_roles`;
168CREATE TABLE  `rvo`.`oft_acl_roles` (
169  `id_acl_role` int(10) unsigned NOT NULL AUTO_INCREMENT,
170  `name` varchar(25) NOT NULL,
171  `fullname` varchar(150) NOT NULL,
172  PRIMARY KEY (`id_acl_role`),
173  UNIQUE KEY `ux_acl_role_name` (`name`)
174) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
175
176DROP TABLE IF EXISTS `rvo`.`oft_migrations`;
177CREATE TABLE  `rvo`.`oft_migrations` (
178  `version` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
179  PRIMARY KEY (`version`)
180) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
181
182DROP TABLE IF EXISTS `rvo`.`oft_users`;
183CREATE TABLE  `rvo`.`oft_users` (
184  `id_user` int(10) unsigned NOT NULL AUTO_INCREMENT,
185  `username` varchar(150) NOT NULL,
186  `password` varchar(255) NOT NULL,
187  `salt` char(8) DEFAULT NULL,
188  `token` char(64) DEFAULT NULL,
189  `token_date` datetime DEFAULT NULL,
190  `active` tinyint(1) NOT NULL DEFAULT '1',
191  `preferred_language` char(2) NOT NULL DEFAULT 'FR',
192  `civility` tinyint(1) NOT NULL DEFAULT '0',
193  `givenname` varchar(64) DEFAULT NULL,
194  `surname` varchar(64) DEFAULT NULL,
195  `mail` varchar(100) DEFAULT NULL,
196  `entity` varchar(100) DEFAULT NULL,
197  `manager_username` varchar(150) DEFAULT NULL,
198  `creation_date` datetime DEFAULT NULL,
199  `update_time` datetime DEFAULT NULL,
200  PRIMARY KEY (`id_user`),
201  UNIQUE KEY `ux_users_username` (`username`)
202) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
203
204DROP TABLE IF EXISTS `rvo`.`pays`;
205CREATE TABLE  `rvo`.`pays` (
206  `id` int(11) NOT NULL AUTO_INCREMENT,
207  `pays` varchar(50) DEFAULT NULL,
208  PRIMARY KEY (`id`)
209) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
210
211DROP TABLE IF EXISTS `rvo`.`reponse_groupement`;
212CREATE TABLE  `rvo`.`reponse_groupement` (
213  `id` int(11) NOT NULL AUTO_INCREMENT,
214  `reponse_groupement` varchar(50) DEFAULT NULL,
215  PRIMARY KEY (`id`)
216) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
217
218DROP TABLE IF EXISTS `rvo`.`risques_opportunites`;
219CREATE TABLE  `rvo`.`risques_opportunites` (
220  `id` int(11) NOT NULL AUTO_INCREMENT,
221  `offre` int(11) DEFAULT NULL,
222  `risque` varchar(50) DEFAULT NULL,
223  `probabilite` varchar(50) DEFAULT NULL,
224  `opportunite` varchar(50) DEFAULT NULL,
225  `commentaire_rique` text,
226  `commentaire_opps` text,
227  `created_at` datetime DEFAULT NULL,
228  `updated_at` datetime DEFAULT NULL,
229  PRIMARY KEY (`id`),
230  KEY `offre` (`offre`),
231  CONSTRAINT `FK_risques_opportunites_offres` FOREIGN KEY (`offre`) REFERENCES `offres` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
232) ENGINE=InnoDB DEFAULT CHARSET=latin1;
233
234DROP TABLE IF EXISTS `rvo`.`type_client`;
235CREATE TABLE  `rvo`.`type_client` (
236  `id` int(11) NOT NULL AUTO_INCREMENT,
237  `type_client` varchar(50) DEFAULT NULL,
238  PRIMARY KEY (`id`)
239) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
240
241DROP TABLE IF EXISTS `rvo`.`type_contrat`;
242CREATE TABLE  `rvo`.`type_contrat` (
243  `id` int(11) NOT NULL AUTO_INCREMENT,
244  `type_contrat` varchar(50) DEFAULT NULL,
245  PRIMARY KEY (`id`)
246) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
247
248DROP TABLE IF EXISTS `rvo`.`types_offres`;
249CREATE TABLE  `rvo`.`types_offres` (
250  `id` int(11) NOT NULL AUTO_INCREMENT,
251  `type_offre` varchar(50) DEFAULT NULL,
252  PRIMARY KEY (`id`)
253) ENGINE=InnoDB DEFAULT CHARSET=latin1;