DOCUMENTATION

WASTEELS Louka | MASSON Charlie | GRAVE Théo

I.

MEA :

Schéma Relationnel :

Script de création :

CREATE TABLE admin (id INT AUTO_INCREMENT NOT NULL, username VARCHAR(255) NOT NULL, password
VARCHAR(255) NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci`
ENGINE = InnoDB;

CREATE TABLE contact (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, firstname
VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, message LONGTEXT NOT NULL, sent_at DATETIME
NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE =
InnoDB;

CREATE TABLE file (id INT AUTO_INCREMENT NOT NULL, offer_id INT DEFAULT NULL, name VARCHAR(255) NOT
NULL, file_path VARCHAR(255) NOT NULL, original_name VARCHAR(255) NOT NULL, INDEX
IDX_8C9F361053C674EE (offer_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE
`utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE newsletter_subscriber (id INT AUTO_INCREMENT NOT NULL, email VARCHAR(255) NOT NULL,
checked TINYINT(1) NOT NULL, inscription_at DATETIME NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET
utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;
CREATE TABLE offer (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(75) NOT NULL, text VARCHAR(1000)
NOT NULL, date_start DATETIME NOT NULL, date_end DATETIME NOT NULL, published_at DATETIME DEFAULT
NULL, tariff VARCHAR(1000) NOT NULL, type_of_offer VARCHAR(255) NOT NULL, nb_minimum_places INT NOT
NULL, number_order_page INT NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE
`utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE partnership (id INT AUTO_INCREMENT NOT NULL, file_id INT DEFAULT NULL, name
VARCHAR(255) NOT NULL, text LONGTEXT DEFAULT NULL, link_to_website VARCHAR(255) NOT NULL, UNIQUE
INDEX UNIQ_8619D6AE93CB796C (file_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE
`utf8mb4_unicode_ci` ENGINE = InnoDB;
CREATE TABLE static_content (id INT AUTO_INCREMENT NOT NULL, section VARCHAR(255) NOT NULL, page
VARCHAR(255) NOT NULL, content LONGTEXT NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4
COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE survey (id INT AUTO_INCREMENT NOT NULL, question VARCHAR(255) NOT NULL, activated
TINYINT(1) NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci`
ENGINE = InnoDB;

CREATE TABLE survey_answer (id INT AUTO_INCREMENT NOT NULL, survey_id INT NOT NULL, answer
VARCHAR(255) NOT NULL, sent_at DATETIME DEFAULT NULL, INDEX IDX_F2D38249B3FE509D (survey_id),
PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;
CREATE TABLE survey_option (id INT AUTO_INCREMENT NOT NULL, survey_id INT NOT NULL, label
VARCHAR(255) NOT NULL, INDEX IDX_7288C8DCB3FE509D (survey_id), PRIMARY KEY(id)) DEFAULT CHARACTER
SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE messenger_messages (id BIGINT AUTO_INCREMENT NOT NULL, body LONGTEXT NOT NULL,
headers LONGTEXT NOT NULL, queue_name VARCHAR(190) NOT NULL, created_at DATETIME NOT NULL,
available_at DATETIME NOT NULL, delivered_at DATETIME DEFAULT NULL, INDEX IDX_75EA56E0FB7336F0
(queue_name), INDEX IDX_75EA56E0E3BD61CE (available_at), INDEX IDX_75EA56E016BA31DB (delivered_at),
PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

ALTER TABLE file ADD CONSTRAINT FK_8C9F361053C674EE FOREIGN KEY (offer_id) REFERENCES offer (id);
ALTER TABLE partnership ADD CONSTRAINT FK_8619D6AE93CB796C FOREIGN KEY (file_id) REFERENCES file
(id);ALTER TABLE survey_answer ADD CONSTRAINT FK_F2D38249B3FE509D FOREIGN KEY (survey_id)
REFERENCES survey (id);
ALTER TABLE survey_option ADD CONSTRAINT FK_7288C8DCB3FE509D FOREIGN KEY (survey_id) REFERENCES
survey (id);

Jeu d’essai :
INSERT INTO `admin` (`id`, `username`, `password`) VALUES
(1, 'root', '$2y$10$wAt82gWeDZMrFYgt1jHPNOOcgUTMdhTsOw.LJgFGaFSSE2Ot4W7nm');

INSERT INTO `contact` (`id`, `name`, `firstname`, `email`, `message`, `sent_at`) VALUES
(1, 'Grave', 'Théo', 'theo.grave0@gmail.com', 'message de test', '2023-03-13 14:18:53');

INSERT INTO `file` (`id`, `name`, `file_path`, `original_name`, `offer_id`) VALUES

(1, 'logo-disney', 'logo-disney.png', 'logo-disney', NULL),
(2, 'logo-leonidas', 'logo-leonidas.png', 'logo-leonidas', NULL),
(3, 'logo-nike', 'logo-nike.png', 'logo-nike', NULL),
(4, 'logo-lego', 'logo-lego.png', 'logo-lego', NULL);

INSERT INTO `offer` (`id`, `name`, `text`, `date_start`, `date_end`, `published_at`, `tariff`, `type_of_offer`,
`nb_minimum_places`, `number_order_page`) VALUES
(1, 'Sortie musée permanentes', 'une sortie a un musée', '2023-03-13 14:57:22', '2023-03-31 14:57:22', '2023-
02-20 12:23:02' ,'tarif enfant: 10€, adulte: 20€', 'permanente', 30, 0),
(2, 'Sortie musée temporaire', 'une sortie a un musée limité', '2023-03-13 14:57:22', '2023-03-23 14:57:22',
'2023-02-15 16:04:20', '5 euros', 'limité', 10, 1),
(3, 'sortie au musée', 'let\'s go', '2023-07-15 00:00:00', '2024-01-01 00:00:00', '2023-05-15 16:02:20' ,'30',
'limité', 20, 1);

INSERT INTO `partnership` (`id`, `file_id`, `name`, `text`, `link_to_website`) VALUES

(1, 1, 'Disney', 'texte pour Disney', 'disney.fr'),
(2, 2, 'Leonidas', 'texte pour Leonidas', 'leonidas.com'),
(3, 3, 'Nike', 'texte pour Nike', 'nike.com'),
(4, 4, 'Lego', 'texte pour Lego', 'lego.com');
INSERT INTO `static_content` (`id`, `section`, `page`, `content`) VALUES
(1, 'CSE', 'accueil', '<p>Nous vous souhaitons la bienvenue sur le site du comit&eacute; social et
&eacute;conomique du lyc&eacute;e Saint-Vincent &agrave; Senlis. D&eacute;couvrez l&rsquo;&eacute;quipe
et le r&ocirc;le et missions de votre CSE.</p>'),
(4, '2', 'a-propos-de-nous', '<p style=\"text-align:center\"><strong><span style=\"font-size:18px\">Le
R&egrave;glement du CSE</span></strong></p>\r\n\r\n<p style=\"text-align:center\">&nbsp;</p>\r\n\r\n<p
style=\"text-align:center\">Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor
incididunt ut labore et dolore magna aliqua. Pharetra diam sit amet nisl. Facilisi nullam vehicula ipsum a arcu
cursus vitae congue mauris. Nec nam aliquam sem et tortor consequat id porta. Ipsum faucibus vitae aliquet
nec ullamcorper. Donec adipiscing tristique risus nec feugiat in fermentum posuere urna. Orci nulla
pellentesque dignissim enim sit. Nibh cras pulvinar mattis nunc sed. Dolor sed viverra ipsum nunc aliquet. Quis
lectus nulla at volutpat diam ut venenatis tellus. Sed nisi lacus sed viverra tellus. Accumsan lacus vel facilisis
volutpat est velit.</p>\r\n\r\n<p style=\"text-align:center\">&nbsp;</p>\r\n\r\n<p style=\"text-
align:center\">&nbsp;</p>'),
(5, '3', 'a-propos-de-nous', '<p style=\"text-align:center\">&nbsp;</p>\r\n\r\n<p style=\"text-
align:center\"><strong><span style=\"font-size:18px\">Le Mail du CSE</span></strong></p>\r\n\r\n<p
style=\"text-align:center\">&nbsp;</p>\r\n\r\n<p style=\"text-align:center\"><u><a
href=\"mailto:cse@lyceestvincent.net\" target=\"_blank\"><span dir=\"ltr\"
lang=\"fr\">cse@lyceestvincent.net</span></a></u></p>\r\n\r\n<p style=\"text-align:center\">&nbsp;</p>\r\
n\r\n<p style=\"text-align:center\">&nbsp;</p>\r\n\r\n<p style=\"text-align:center\">&nbsp;</p>'),
(6, '4', 'a-propos-de-nous', '<p style=\"text-align:center\"><strong><span style=\"font-size:18px\">Les Actions
Men&eacute;es par le CSE</span></strong></p>\r\n\r\n<p style=\"text-align:center\">&nbsp;</p>\r\n\r\n<p
style=\"text-align:center\">Egestas fringilla phasellus faucibus scelerisque eleifend donec pretium vulputate.
Justo nec ultrices dui sapien eget. Massa tempor nec feugiat nisl pretium fusce. Platea dictumst quisque sagittis
purus. Dignissim cras tincidunt lobortis feugiat vivamus at augue eget. Proin sagittis nisl rhoncus mattis rhoncus
urna. Pellentesque pulvinar pellentesque habitant morbi tristique senectus et netus. Nisi scelerisque eu ultrices
vitae. Aenean euismod elementum nisi quis eleifend quam adipiscing vitae proin. Aliquet porttitor lacus luctus
accumsan tortor posuere. In aliquam sem fringilla ut morbi tincidunt augue interdum. Suspendisse in est ante in
nibh mauris cursus mattis molestie. Sed augue lacus viverra vitae congue eu. Sit amet aliquam id diam
maecenas. Leo integer malesuada nunc vel risus. Urna et pharetra pharetra massa massa.</p>');

INSERT INTO `survey` (`id`, `question`, `activated`) VALUES

(1, 'le test fonctionne ?', 1);

INSERT INTO `survey_option` (`id`, `survey_id`, `label`) VALUES

(1, 1, 'oui'),
(2, 1, 'non');

II.
Arborescence :

7
III.

BackLog ( 1 image par sprint )

IV. ( Fichier Excel joint )

V.