Skip to main content

Base de données

Base de données gestion_hjc

CREATE DATABASE IF NOT EXISTS `gestion_hjc` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `gestion_hjc`;

Table fournisseurs

CREATE TABLE `fournisseurs` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(100) NOT NULL,
  `adresse` TEXT DEFAULT NULL,
  `CP` VARCHAR(5) DEFAULT NULL,
  `Ville` VARCHAR(30) DEFAULT NULL,
  `email` VARCHAR(100) DEFAULT NULL,
  `telephone` VARCHAR(20) DEFAULT NULL,
  `mobile` VARCHAR(20) DEFAULT NULL,
  `actif` TINYINT(1) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Table achats

CREATE TABLE `achats` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `date` DATE NOT NULL,
  `semaine` INT(2) NOT NULL,
  `idFournisseur` INT(10) UNSIGNED NOT NULL,
  `montantHT` DECIMAL(10,2) NOT NULL,
  `idUtilisateur` INT(11) NOT NULL,
  `dateSaisie` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `valide` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idFournisseur` (`idFournisseur`),
  CONSTRAINT `achats_ibfk_2` FOREIGN KEY (`idFournisseur`) REFERENCES `fournisseurs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Table clients

CREATE TABLE `clients` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `date` DATE NOT NULL COMMENT 'Date de l enregistrement',
  `nbClientsPoissonnerie` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  `nbClientsComptoir` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  `valide` TINYINT(1) NOT NULL DEFAULT 0,
  `dateSaisie` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `idUtilisateur` INT(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Table modesReglement

CREATE TABLE `modesReglement` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(50) NOT NULL,
  `actif` TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `modesReglement` (`id`, `nom`, `actif`) VALUES
(1, 'CB', 0),
(2, 'AMEX', 0),
(3, 'Bon Cadeau', 0),
(4, 'Chèque', 1),
(5, 'Virement de fond', 1),
(6, 'Espèces', 1),
(7, 'Vente à distance', 0),
(8, 'Sortie de caisse', 1),
(9, 'Uber Virements', 0),
(10, 'CB Stripe', 0),
(12, 'TPE 1 CB CLESS', 1),
(13, 'TPE 1 CB CLESS AMEX', 1),
(14, 'TPE 2 CB CLESS', 1),
(15, 'TPE 2 CB CLESS AMEX', 1),
(16, 'TPE 1 CB EMV', 1),
(18, 'TPE 1 CB EMV AMEX', 1),
(19, 'TPE 2 CB CBEMV', 1),
(20, 'TPE 2 CB CBEMV AMEX', 1),
(23, 'TPE CB EMV', 0),
(24, 'TPE CB EMV AMEX', 0),
(25, 'TPE CB CLESS', 0),
(26, 'TPE CB CLESS AMEX', 0),
(27, 'TPE CB VAD', 0),
(28, 'TPE CB Arrhes', 0),
(29, 'Ticket restaurant', 0),
(30, 'Espèces Arrhes', 0),
(31, 'HT Comptoir', 1);

Table ventes

CREATE TABLE `ventes` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `date` DATE NOT NULL,
  `dateSaisie` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `semaine` INT(2) NOT NULL,
  `valide` TINYINT(1) NOT NULL DEFAULT 0,
  `idUtilisateur` INT(11) NOT NULL DEFAULT 1,
  `idModeReglement` INT(10) UNSIGNED NOT NULL,
  `montant` DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idModeReglement` (`idModeReglement`),
  CONSTRAINT `ventes_ibfk_2` FOREIGN KEY (`idModeReglement`) REFERENCES `modesReglement` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Table roles

CREATE TABLE `roles` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nom` (`nom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `roles` (`id`, `nom`) VALUES
(5, 'Administrateur'),
(2, 'Comptabilité'),
(4, 'Direction'),
(3, 'Responsable'),
(1, 'Utilisateur');

Table users

CREATE TABLE `users` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(50) NOT NULL,
  `last_name` VARCHAR(50) DEFAULT NULL,
  `email` VARCHAR(100) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role_id` INT(10) UNSIGNED NOT NULL,
  `actif` TINYINT(1) DEFAULT 1,
  `token` VARCHAR(255) DEFAULT NULL,
  `last_login` DATETIME DEFAULT NULL,
  `reset_token` VARCHAR(255) DEFAULT NULL,
  `reset_token_expiration` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Table permissions

CREATE TABLE `permissions` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nom` (`nom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `permissions` (`id`, `nom`) VALUES
(6, 'gerer etablissements'),
(5, 'gerer modes reglement'),
(4, 'gerer roles'),
(3, 'gerer utilisateurs'),
(1, 'saisir achat'),
(2, 'saisir vente'),
(7, 'voir synthese');

Table role_permissions

CREATE TABLE `role_permissions` (
  `role_id` INT(11) UNSIGNED NOT NULL,
  `permission_id` INT(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`role_id`, `permission_id`),
  KEY `permission_id` (`permission_id`),
  CONSTRAINT `role_permissions_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `role_permissions_ibfk_2` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

No comments to display

Back to top