SELECT DISTINCT S.* ,ART.* ,SUM(S.qte) AS arrive ,SUM(S.qte) - (ART.vendu) AS diff FROM stocks S LEFT JOIN ( SELECT A.articleVendu ,A.id_prod ,SUM(A.articleQuant) as vendu FROM articles A LEFT JOIN produits P ON A.id_prod = P.id_pros GROUP BY A.id_prod ) ART ON ART.id_prod = S.id_pros GROUP BY S.id_pros
CREATE TABLE IF NOT EXISTS `articles` ( `id_art` int(11) NOT NULL, `dateArt` datetime NOT NULL, `num` int(15) NOT NULL, `Designation` varchar(150) NOT NULL, `articleVendu` varchar(50) NOT NULL, `taille` varchar(11) NOT NULL, `articleRef` varchar(15) NOT NULL, `articleQuant` varchar(50) NOT NULL, `articlePU` varchar(50) NOT NULL, `pa` int(11) NOT NULL, `marge` int(10) NOT NULL, `id` int(11) NOT NULL, `id_prod` int(11) NOT NULL, `remise` varchar(11) NOT NULL, `id_com` int(11) NOT NULL, `taux` varchar(15) NOT NULL, `espece` enum('0','1') NOT NULL, `cheque` enum('0','1') NOT NULL, `cheque_status` enum('1','2') NOT NULL, `cheque_enc` enum('3','4') NOT NULL, `cheque_rej` enum('4','5') NOT NULL, `credit` enum('0','1') NOT NULL, `id_cl` int(11) NOT NULL, `clef` varchar(11) NOT NULL, `session` varchar(150) NOT NULL ) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `produits` ( `id` int(11) NOT NULL, `article` varchar(150) DEFAULT NULL, `taille` int(11) NOT NULL, `pa` int(15) NOT NULL, `pv` int(15) NOT NULL, `ref` varchar(30) NOT NULL, `prixmin` int(11) NOT NULL, `prixmax` int(11) NOT NULL, `id_rayon` int(11) NOT NULL, `date` varchar(30) NOT NULL, `id_pros` int(11) NOT NULL ) ENGINE=MyISAM AUTO_INCREMENT=82 DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `stocks` ( `num` int(11) NOT NULL, `taille` varchar(11) NOT NULL, `date` varchar(30) NOT NULL, `qte` int(11) NOT NULL, `unite` varchar(30) NOT NULL, `id_get` int(11) NOT NULL, `id_pros` int(11) NOT NULL, `accuse_stock` enum('0','1') NOT NULL, `accuse_stock2` enum('1','2') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
$sql="INSERT INTO produits(id,article,taille,pa,pv,ref,prixmin,prixmax,id_pros) VALUES('','".$produit."','".$taille."','".$pa."','".$pv."','".$ref."','".$prixmin."','".$prixmax."','".$tab2["id"]."')"; // on insère les informations du formulaire dans la table mysql_query($sql) or die('Erreur SQL !'.$sql.'<br>'.mysql_error()); $id_get = mysql_insert_id(); mysql_query("INSERT INTO stocks(num,taille,date,qte,id_get,id_pros) VALUES('".$num."','".$taille."',NOW(),'".$qte."','".$id_get."','".$tab2["id"]."')");
-- phpMyAdmin SQL Dump -- version 4.1.14 -- http://www.phpmyadmin.net -- -- Client : 127.0.0.1 -- Généré le : Dim 07 Février 2016 à 11:11 -- Version du serveur : 5.6.17 -- Version de PHP : 5.5.12 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 utf8 */; -- -- Base de données : `bdd_bloowell` -- -- -------------------------------------------------------- -- -- Structure de la table `articles` -- CREATE TABLE IF NOT EXISTS `articles` ( `id_art` int(11) NOT NULL AUTO_INCREMENT, `dateArt` datetime NOT NULL, `num` int(15) NOT NULL, `Designation` varchar(150) NOT NULL, `articleVendu` varchar(50) NOT NULL, `taille` varchar(11) NOT NULL, `articleRef` varchar(15) NOT NULL, `articleQuant` varchar(50) NOT NULL, `articlePU` varchar(50) NOT NULL, `pa` int(11) NOT NULL, `marge` int(10) NOT NULL, `id` int(11) NOT NULL, `id_prod` int(11) NOT NULL, `remise` varchar(11) NOT NULL, `id_com` int(11) NOT NULL, `taux` varchar(15) NOT NULL, `espece` enum('0','1') NOT NULL, `cheque` enum('0','1') NOT NULL, `cheque_status` enum('1','2') NOT NULL, `cheque_enc` enum('3','4') NOT NULL, `cheque_rej` enum('4','5') NOT NULL, `credit` enum('0','1') NOT NULL, `id_cl` int(11) NOT NULL, `clef` varchar(11) NOT NULL, `session` varchar(150) NOT NULL, PRIMARY KEY (`id_art`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ; -- -- Contenu de la table `articles` -- INSERT INTO `articles` (`id_art`, `dateArt`, `num`, `Designation`, `articleVendu`, `taille`, `articleRef`, `articleQuant`, `articlePU`, `pa`, `marge`, `id`, `id_prod`, `remise`, `id_com`, `taux`, `espece`, `cheque`, `cheque_status`, `cheque_enc`, `cheque_rej`, `credit`, `id_cl`, `clef`, `session`) VALUES (17, '2016-02-04 17:25:53', 1, '', 'MLS3/38', '38', 'MLS3/38', '5', '200000', 30000, 0, 0, 16, '', 0, '', '0', '0', '1', '3', '4', '0', 1, 'e0234569', 'msi79'), (16, '2016-02-04 17:23:18', 1, '', 'MCP2/38', '38', 'MCP2/38', '10', '30000', 30000, 0, 0, 12, '', 0, '', '0', '0', '1', '3', '4', '0', 1, 'abcf0159', 'msi79'), (15, '2016-02-04 17:23:18', 1, '', 'MLS3/38', '38', 'MLS3/38', '1', '350000', 30000, 0, 0, 16, '', 0, '', '0', '0', '1', '3', '4', '0', 1, 'abcf0159', 'msi79'), (14, '2016-02-04 09:19:10', 3, '', 'MLS3/38', '38', 'MLS3/38', '1', '35000', 30000, 0, 0, 16, '', 0, '', '0', '0', '1', '3', '4', '0', 3, 'cdef0349', 'msi79'), (11, '2016-02-03 21:17:39', 1, '', 'MLS3/38', '', 'MLS3/38', '14', '35000', 30000, 0, 0, 16, '', 0, '18', '0', '0', '1', '3', '4', '0', 1, 'ae125679', 'msi79'); -- -------------------------------------------------------- -- -- Structure de la table `produits` -- CREATE TABLE IF NOT EXISTS `produits` ( `id` int(11) NOT NULL AUTO_INCREMENT, `article` varchar(150) DEFAULT NULL, `taille` int(11) NOT NULL, `pa` int(15) NOT NULL, `pv` int(15) NOT NULL, `ref` varchar(30) NOT NULL, `prixmin` int(11) NOT NULL, `prixmax` int(11) NOT NULL, `id_rayon` int(11) NOT NULL, `date` varchar(30) NOT NULL, `id_pros` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ; -- -- Contenu de la table `produits` -- INSERT INTO `produits` (`id`, `article`, `taille`, `pa`, `pv`, `ref`, `prixmin`, `prixmax`, `id_rayon`, `date`, `id_pros`) VALUES (1, 'MCP2/38', 38, 30000, 35000, 'MCP2/38', 30000, 35000, 0, '', 12), (2, 'MLS3/38', 38, 30000, 40000, 'MLS3/38', 30000, 40000, 0, '', 16), (3, 'MLS3/39', 39, 30000, 35000, 'MLS3/39', 30000, 35000, 0, '', 17), (4, 'CHMZ/18', 18, 30000, 35000, 'CHMZ/18', 30000, 35000, 0, '', 3), (5, 'CHMZ/20', 20, 30000, 35000, 'CHMZ/20', 30000, 35000, 0, '', 4), (6, 'MCP2/41', 41, 30000, 35000, 'MCP2/41', 30000, 35000, 0, '', 15), (7, 'CHMZ/18', 18, 30000, 35000, 'CHMZ/18', 30000, 35000, 0, '', 3), (8, 'MCD1', 0, 30000, 35000, 'MCD1', 30000, 35000, 0, '', 7), (9, 'MLS3/38', 38, 30000, 35000, 'MLS3/38', 30000, 35000, 0, '', 16), (10, 'MLS3/38', 38, 30000, 35000, 'MLS3/38', 30000, 35000, 0, '', 16), (11, 'MLS3/38', 38, 30000, 35000, 'MLS3/38', 30000, 35000, 0, '', 16); -- -------------------------------------------------------- -- -- Structure de la table `stocks` -- CREATE TABLE IF NOT EXISTS `stocks` ( `num` int(11) NOT NULL, `taille` varchar(11) NOT NULL, `date` varchar(30) NOT NULL, `qte` int(11) NOT NULL, `unite` varchar(30) NOT NULL, `id_get` int(11) NOT NULL, `id_pros` int(11) NOT NULL, `session` varchar(100) NOT NULL, `accuse_stock` enum('0','1') NOT NULL, `accuse_stock2` enum('1','2') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Contenu de la table `stocks` -- INSERT INTO `stocks` (`num`, `taille`, `date`, `qte`, `unite`, `id_get`, `id_pros`, `session`, `accuse_stock`, `accuse_stock2`) VALUES (1, '38', '2016-02-04 10:55:05', 100, '', 1, 12, '', '0', '1'), (2, '38', '2016-02-04 11:06:25', 200, '', 2, 16, '', '0', '1'), (3, '39', '2016-02-04 11:07:39', 250, '', 3, 17, '', '0', '1'), (4, '18', '2016-02-04 11:08:18', 100, '', 4, 3, '', '0', '1'), (5, '20', '2016-02-04 11:08:52', 100, '', 5, 4, '', '0', '1'), (6, '41', '2016-02-04 11:09:31', 100, '', 6, 15, '', '0', '1'), (7, '18', '2016-02-04 11:11:31', 100, '', 7, 3, '', '0', '1'), (8, '', '2016-02-04 11:12:04', 100, '', 8, 7, '', '0', '1'), (9, '38', '2016-02-05 18:36:21', 21, '', 9, 16, '', '0', '1'), (10, '38', '2016-02-05 21:50:43', 25, '', 10, 16, '', '0', '1'), (11, '38', '2016-02-05 21:51:28', 35, '', 11, 16, '', '0', '1'); /*!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 */;