-- phpMyAdmin SQL Dump
-- version 4.6.6deb4
-- https://www.phpmyadmin.net/
-- AgroParisTech - Michel Cartereau - Septembre 2018 
-- Client :  localhost
-- Genere le :  Sam 29 Septembre 2018 a 15:33
-- Version du serveur :  10.1.26-MariaDB-0+deb9u1
-- Version de PHP :  7.0.30-0+deb9u1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


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

--
-- Base de donnees :  coop
--
CREATE DATABASE IF NOT EXISTS coop DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE coop;

DELIMITER $$

--
-- Fonctions
--
DROP FUNCTION IF EXISTS `liste_stock`$$
CREATE DEFINER=`root`@`%` FUNCTION `liste_stock` () RETURNS VARCHAR(200) CHARSET utf8 
-- renvoi de l'etat du stock code dans un texte : libelle1:taille1/libelle2:taille2/...
-- Michel Cartereau - Septembre 2018 
reads sql data 
BEGIN
declare fait boolean default false;
declare libelle varchar(40);
declare taille  integer;
declare resultat  varchar(200);

declare un_stock cursor for 
    select  produit.nom, sum(taille_reste ) as stock
    from produit join lot on lot.ref_produit = produit.numero
    where taille_reste > 0
    group by lot.ref_produit
    order by stock desc ;
declare continue handler for not found set fait = true;

-- iteration de lecture des effectifs calculesset resultat = '';
open un_stock;
iteration: loop
  fetch un_stock into libelle, taille;
if fait then leave iteration; end if;
  set resultat = concat(resultat, libelle, ':', taille, '/');
end loop iteration;
close un_stock;

return resultat;
END$$

DELIMITER ;

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

--
-- Structure de la table article
--
-- Creation :  Mer 26 Septembre 2018 a 13:17
--

DROP TABLE IF EXISTS article;
CREATE TABLE IF NOT EXISTS article (
  num_article int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  ref_produit int(10) UNSIGNED NOT NULL DEFAULT '0',
  ref_vente int(10) UNSIGNED NOT NULL,
  qte_produit int(10) UNSIGNED NOT NULL DEFAULT '0',
  prix_vendu decimal(7,2) UNSIGNED NOT NULL,
  PRIMARY KEY (num_article),
  KEY ref_produit (ref_produit),
  KEY vente_article (ref_vente)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

--
-- Contenu de la table article
--

INSERT INTO article (num_article, ref_produit, ref_vente, qte_produit, prix_vendu) VALUES
(1, 1, 1, 10, '1.00'),
(2, 2, 1, 30, '2.00'),
(3, 3, 2, 20, '1.80'),
(4, 1, 3, 10, '1.00'),
(5, 5, 3, 30, '2.50'),
(6, 6, 3, 5, '1.90');

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

--
-- Structure de la table emballage
--
-- Creation :  Mer 26 Septembre 2018 a 12:58
--

DROP TABLE IF EXISTS emballage;
CREATE TABLE IF NOT EXISTS emballage (
  num_emballage int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  modele varchar(60) NOT NULL,
  capacite decimal(7,2) NOT NULL,
  PRIMARY KEY (num_emballage)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Contenu de la table emballage
--

INSERT INTO emballage (num_emballage, modele, capacite) VALUES
(1, 'POCHON EN PAPIER', '1.00'),
(2, 'SAC BIODEGRADABLE', '5.00'),
(3, 'CAGETTE RECYCLEE', '10.00');

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

--
-- Structure de la table emballer
--
-- Creation :  Mer 26 Septembre 2018 a 13:00
--

DROP TABLE IF EXISTS emballer;
CREATE TABLE IF NOT EXISTS emballer (
  ref_produit int(10) UNSIGNED NOT NULL,
  ref_emballage int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (ref_produit,ref_emballage),
  KEY emballage_produit (ref_emballage)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Structure de la table lot
--
-- Creation :  Mer 26 Septembre 2018 a 13:28
--

DROP TABLE IF EXISTS lot;
CREATE TABLE IF NOT EXISTS lot (
  num_lot int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  ref_produit int(10) UNSIGNED NOT NULL DEFAULT '0',
  ref_producteur int(10) UNSIGNED NOT NULL DEFAULT '0',
  taille_initiale int(10) UNSIGNED NOT NULL DEFAULT '0',
  taille_reste int(10) UNSIGNED NOT NULL DEFAULT '0',
  prix_achat int(10) UNSIGNED NOT NULL DEFAULT '0',
  date_arrivee date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (num_lot),
  KEY ref_produit (ref_produit),
  KEY ref_producteur (ref_producteur)
) ENGINE=InnoDB AUTO_INCREMENT=62 DEFAULT CHARSET=utf8;

--
-- Contenu de la table lot
--

INSERT INTO lot (num_lot, ref_produit, ref_producteur, taille_initiale, taille_reste, prix_achat, date_arrivee) VALUES
(1, 1, 1, 300, 0, 70, '2005-12-29'),
(2, 3, 2, 100, 93, 300, '2005-12-29'),
(3, 1, 2, 300, 297, 60, '2005-12-30'),
(4, 3, 1, 50, 50, 380, '2009-01-12'),
(5, 5, 2, 100, 70, 200, '2009-01-14'),
(6, 4, 4, 100, 71, 90, '2009-01-14'),
(7, 2, 2, 300, 298, 100, '2009-01-15'),
(8, 1, 2, 100, 100, 100, '2009-01-15'),
(28, 3, 4, 30, 30, 500, '2010-01-13'),
(29, 18, 1, 300, 294, 500, '2010-01-13'),
(30, 1, 2, 1, 1, 100, '2010-01-14'),
(31, 1, 2, 1, 1, 100, '2010-01-14'),
(32, 1, 3, 5, 5, 200, '2010-01-15'),
(33, 3, 2, 1, 1, 100, '2010-01-15'),
(34, 1, 1, 1, 1, 100, '2010-01-15'),
(36, 1, 1, 1, 1, 100, '2010-01-15'),
(37, 1, 1, 1, 1, 100, '2010-01-15'),
(38, 4, 2, 1, 1, 100, '2010-01-15'),
(41, 3, 3, 1, 1, 100, '2010-01-15'),
(43, 3, 3, 1, 1, 100, '2010-01-15'),
(44, 1, 2, 125, 125, 100, '2010-01-15'),
(46, 3, 3, 1, 1, 100, '2010-01-15'),
(48, 1, 2, 1, 1, 100, '2011-01-14'),
(49, 1, 1, 1, 1, 100, '2011-01-14'),
(51, 2, 2, 100, 100, 200, '2011-01-14'),
(58, 10, 1, 300, 300, 150, '2011-01-14'),
(59, 10, 1, 300, 300, 150, '2011-01-14'),
(60, 10, 1, 300, 300, 150, '2011-01-14'),
(61, 9, 2, 10, 8, 100, '2011-01-14');

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

--
-- Structure de la table personne
--
-- Creation :  Mer 26 Septembre 2018 a 12:45
--

DROP TABLE IF EXISTS personne;
CREATE TABLE IF NOT EXISTS personne (
  num_personne int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  nom varchar(40) CHARACTER SET latin1 NOT NULL DEFAULT '?',
  adresse varchar(60) CHARACTER SET latin1 NOT NULL DEFAULT '?',
  code_postal varchar(5) CHARACTER SET latin1 NOT NULL DEFAULT '?',
  ville varchar(40) CHARACTER SET latin1 NOT NULL DEFAULT '?',
  telephone varchar(14) CHARACTER SET latin1 DEFAULT '?',
  PRIMARY KEY (num_personne)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

--
-- Contenu de la table personne
--

INSERT INTO personne (num_personne, nom, adresse, code_postal, ville, telephone) VALUES
(1, 'DE LA RUE', '9, rue Convention', '93100', 'MONTREUIL', '01.48.70.60.00'),
(2, 'KING KING', '1, place Italie', '75013', 'PARIS', '01.44.08.13.13'),
(3, 'LILI', '3, rue Resistance', '95200', 'SARCELLES', '01.34.38.20.00'),
(4, 'CESAR', '80 bd de Gergovie', '92110', 'Clichy', ''),
(20, 'ZHOU MIN', '45B Boulevard Jourdan', '75014', 'Paris', '0623897009');

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

--
-- Structure de la table produit
--
-- Creation :  Mer 26 Septembre 2018 a 12:43
--

DROP TABLE IF EXISTS produit;
CREATE TABLE IF NOT EXISTS produit (
  num_produit int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  libelle_produit varchar(40) CHARACTER SET latin1 NOT NULL DEFAULT '',
  type_produit enum('LEGUME','FRUIT') CHARACTER SET latin1 DEFAULT 'LEGUME',
  prix_vente decimal(7,2) UNSIGNED NOT NULL DEFAULT '0.00',
  PRIMARY KEY (num_produit)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

--
-- Contenu de la table produit
--

INSERT INTO produit (num_produit, libelle_produit, type_produit, prix_vente) VALUES
(1, 'CAROTTE', 'LEGUME', '1.00'),
(2, 'BROCOLIS', 'LEGUME', '2.00'),
(3, 'POMME', 'LEGUME', '1.80'),
(4, 'KIWI', 'FRUIT', '2.20'),
(5, 'CHOU DE CHINE', 'LEGUME', '2.50'),
(6, 'POIRE', 'FRUIT', '1.90'),
(7, 'CHOUX FLEUR', 'LEGUME', '2.50'),
(9, 'AIL', 'LEGUME', '6.00'),
(10, 'BETTERAVE', 'LEGUME', '1.00'),
(17, 'BANANE', 'FRUIT', '5.11'),
(18, 'ABRICOT', 'FRUIT', '4.00'),
(19, 'RAISIN', 'FRUIT', '3.00'),
(20, 'RADIS', 'LEGUME', '1.00'),
(22, 'CERISE', 'FRUIT', '1.00'),
(23, 'CHOU DE BRUXELLES', 'LEGUME', '1.00'),
(24, 'LICHEE', 'FRUIT', '7.00'),
(25, 'ANANAS', 'FRUIT', '3.00'),
(26, 'PANAIS', 'LEGUME', '3.50');

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

--
-- Structure de la table vente
--
-- Creation :  Mer 26 Septembre 2018 a 13:06
--

DROP TABLE IF EXISTS vente;
CREATE TABLE IF NOT EXISTS vente (
  num_vente int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  ref_acheteur int(10) UNSIGNED NOT NULL,
  taux_remise decimal(5,2) UNSIGNED NOT NULL DEFAULT '0.00',
  date_vente date NOT NULL,
  PRIMARY KEY (num_vente),
  KEY ref_acheteur (ref_acheteur)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Contenu de la table vente
--

INSERT INTO vente (num_vente, ref_acheteur, taux_remise, date_vente) VALUES
(1, 1, '0.10', '2005-12-27'),
(2, 3, '0.00', '2005-12-28'),
(3, 4, '0.00', '2006-01-03');

--
-- Contraintes pour les tables exportees
--

--
-- Contraintes pour la table article
--
ALTER TABLE article
  ADD CONSTRAINT produit_vendu FOREIGN KEY (ref_produit) REFERENCES produit (num_produit),
  ADD CONSTRAINT vente_article FOREIGN KEY (ref_vente) REFERENCES vente (num_vente);

--
-- Contraintes pour la table emballer
--
ALTER TABLE emballer
  ADD CONSTRAINT emballage_produit FOREIGN KEY (ref_emballage) REFERENCES emballage (num_emballage),
  ADD CONSTRAINT produit_emballe FOREIGN KEY (ref_produit) REFERENCES produit (num_produit);

--
-- Contraintes pour la table lot
--
ALTER TABLE lot
  ADD CONSTRAINT producteur_lot FOREIGN KEY (ref_producteur) REFERENCES personne (num_personne),
  ADD CONSTRAINT produit_lot FOREIGN KEY (ref_produit) REFERENCES produit (num_produit);

--
-- Contraintes pour la table vente
--
ALTER TABLE vente
  ADD CONSTRAINT personne_achat FOREIGN KEY (ref_acheteur) REFERENCES personne (num_personne);

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