-- Structure de la table `inventaire_mat`
-- 

CREATE TABLE `inventaire_mat` (
  `id_inventaire_mat` int(11) NOT NULL auto_increment,
  `date_inventaire_mat` date NOT NULL,
  `qte_inventaire_mat` decimal(10,5) NOT NULL,
  `code_m` int(11) NOT NULL,
  `mois_inventaire_mat` int(6) NOT NULL,
  `annee_inventaire_mat` int(4) NOT NULL,
  `pu_j_inventaire_mat` decimal(10,5) NOT NULL,
  PRIMARY KEY  (`id_inventaire_mat`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=649 ;

-- 
-- Contenu de la table `inventaire_mat`
-- 

INSERT INTO `inventaire_mat` (`id_inventaire_mat`, `date_inventaire_mat`, `qte_inventaire_mat`, `code_m`, `mois_inventaire_mat`, `annee_inventaire_mat`, `pu_j_inventaire_mat`) VALUES 
(1, '2008-06-17', 55.00000, 2, 5, 2008, 0.94000),
(2, '2008-06-17', 2.00000, 50, 5, 2008, 3.75000),
(3, '2008-06-17', 1.00000, 51, 5, 2008, 6.34000),
(4, '2008-06-17', 15.00000, 4, 5, 2008, 1.84800),
(5, '2008-06-17', 42.00000, 3, 5, 2008, 1.92100),
(6, '2008-06-17', 51.00000, 61, 5, 2008, 1.17000),
(7, '2008-06-17', 1.00000, 5, 5, 2008, 10.48000),
(8, '2008-06-17', 9.00000, 44, 5, 2008, 1.70000),
(9, '2008-06-17', 0.00000, 54, 5, 2008, 2.22000),
(10, '2008-06-17', 0.00000, 53, 5, 2008, 4.07000),
(11, '2008-06-17', 13.00000, 65, 5, 2008, 0.55540),
(12, '2008-06-17', 13.00000, 66, 5, 2008, 0.70560),
(13, '2008-06-17', 50.00000, 6, 5, 2008, 8.20000),
(14, '2008-06-17', 9.00000, 29, 5, 2008, 5.35000),
(15, '2008-06-17', 48.00000, 28, 5, 2008, 5.03000),
(16, '2008-06-17', 4.00000, 30, 5, 2008, 5.76000),
(17, '2008-06-17', 6.00000, 25, 5, 2008, 1.96000),
(18, '2008-06-17', 9.00000, 12, 5, 2008, 2.19000),
(19, '2008-06-17', 6.00000, 14, 5, 2008, 2.52000),
(20, '2008-06-17', 15.00000, 16, 5, 2008, 1.70000),
(21, '2008-06-17', 8.00000, 18, 5, 2008, 1.89000),
(22, '2008-06-17', 12.00000, 22, 5, 2008, 2.37000),
(23, '2008-06-17', 11.00000, 11, 5, 2008, 2.47000),
(24, '2008-06-17', 10.00000, 13, 5, 2008, 2.35000),
(25, '2008-06-17', 5.00000, 15, 5, 2008, 2.29000),
(26, '2008-06-17', 74.00000, 17, 5, 2008, 1.66000),
(27, '2008-06-17', 5.00000, 21, 5, 2008, 3.51000),
(28, '2008-06-17', 10.00000, 23, 5, 2008, 2.35000),
(29, '2008-06-17', 128.20000, 56, 5, 2008, 3.28000),
(30, '2008-06-17', 50.00000, 57, 5, 2008, 4.81000),
(31, '2008-06-17', 10.00000, 34, 5, 2008, 0.79990),

-- Structure de la table `matiere`
-- 

CREATE TABLE `matiere` (
  `code_m` int(11) NOT NULL auto_increment,
  `nom_m` varchar(70) NOT NULL COMMENT 'Nom de la matiere premiere',
  `id_four` int(11) NOT NULL COMMENT 'fournisseur',
  `cond_m` int(11) NOT NULL COMMENT 'le nombre de quantité',
  `id_conditionnement` int(10) NOT NULL COMMENT 'au kg en piece',
  `pu_m` decimal(10,5) NOT NULL COMMENT 'prix unitaire',
  `tva_m` decimal(3,2) default '5.50',
  `id_conditionnement_pu` int(10) NOT NULL COMMENT 'mesure kg pice rame',
  `coef_mat_pdr` decimal(10,5) default NULL,
  `pu_mat_pdr` decimal(10,5) default NULL,
  `composup25_m` varchar(30) default NULL,
  `date_m` date NOT NULL COMMENT 'date de maj',
  `id_matiere_type` int(11) NOT NULL,
  `actif_m` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`code_m`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=103 ;

-- 
-- Contenu de la table `matiere`
-- 

INSERT INTO `matiere` (`code_m`, `nom_m`, `id_four`, `cond_m`, `id_conditionnement`, `pu_m`, `tva_m`, `id_conditionnement_pu`, `coef_mat_pdr`, `pu_mat_pdr`, `composup25_m`, `date_m`, `id_matiere_type`, `actif_m`) VALUES 
(94, 'test', 57, 20, 1, 1.54000, 5.50, 1, 1.00000, NULL, '', '2009-07-02', 1, 1),
(2, 'Eau  Fleur Oranger ', 14, 20, 2, 2.70000, 5.50, 2, 1.00000, 0.94000, '', '2009-08-31', 1, 1),
(3, 'Gouttelines Choco 44%', 10, 25, 1, 2.03500, 5.50, 1, 1.00000, 1.88000, '', '2009-01-14', 1, 1),
(4, 'Palet Choco 44%', 10, 25, 1, 1.96300, 5.50, 1, 1.00000, 1.82000, '', '2009-01-14', 1, 1),
(5, 'Arome Citron', 17, 25, 1, 10.48000, 5.50, 1, 1.00000, 10.48000, '', '2008-05-05', 1, 1),
(6, 'Comte', 13, 10, 1, 8.20000, 5.50, 1, 1.00000, 8.20000, '', '2008-01-28', 1, 1),

-- Structure de la table `matiere_type`
-- 

CREATE TABLE `matiere_type` (
  `id_matiere_type` int(11) NOT NULL auto_increment,
  `ref_matiere_type` varchar(32) NOT NULL,
  `nom_matiere_type` varchar(32) NOT NULL,
  PRIMARY KEY  (`id_matiere_type`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- 
-- Contenu de la table `matiere_type`
-- 

INSERT INTO `matiere_type` (`id_matiere_type`, `ref_matiere_type`, `nom_matiere_type`) VALUES 
(1, 'MPS', 'Matiere Premiere Surgele'),
(2, 'MPF', 'Matiere Premier Frais'),
(3, 'FU', 'Fourniture Usine');

WHERE mois IN (1,4,12)

SELECT COALESCE(SUM(cond_m*qte_inventaire_mat*pu_j_inventaire_mat),0) AS total_mat_1, mt.nom_matiere_type 
FROM inventaire_mat 
INNER JOIN matiere ON matiere.code_m=inventaire_mat.code_m 
INNER JOIN matiere_type mt ON mt.id_matiere_type=matiere.id_matiere_type 
WHERE matiere.id_matiere_type='3' 
AND mois_inventaire_mat='2' 
AND annee_inventaire_mat='2009' GROUP BY nom_matiere_type