Partage de requêtes SQL: Stock à date, temps d'écouler le stock, exportation des mouvements..

DelosTR · May 11, 2023

Bonjour, je partage mes requêtes SQL ici car j'aurai bien voulu les trouver ici, (dont le stock à date plusieurs ont posé la question mais sans réponse) donc je me dis que ca va forcément servir.

On commence par du simple, lister le mouvement du stock d'un produit, en gros c'est ce qu'on a déjà dans stock/mouvements sauf qu'on ne pouvait pas extraire les données, maintenant vous pouvez le faire via phpmyadmin ou via le module big data.

Je précise que je suis sous prestashop 1.7.7.8 et en multishop x3, mon id_lang 1 est le FR

à vous de modifier selon votre config.

-- Remplacez "4127" par l'id de votre produit, et "2022-10-25" par la date jusqu'à laquelle il va lister les mouvements. --

SELECT
    p.id_product,
    pl.name AS Produit,
    GROUP_CONCAT(DISTINCT agl.name SEPARATOR '/') AS Déclinaison,
    CONCAT(IF(sm.sign = 1, '+', '-'), sm.physical_quantity) AS Mouvements,
    sa.quantity AS Stock,
    sm.date_add,
    sm.id_order
FROM ps_stock_mvt sm
INNER JOIN ps_stock_available sa ON sm.id_stock = sa.id_stock_available
INNER JOIN ps_product p ON sa.id_product = p.id_product
INNER JOIN ps_product_lang pl ON p.id_product = pl.id_product
LEFT JOIN ps_product_attribute pa ON sa.id_product_attribute = pa.id_product_attribute
LEFT JOIN ps_product_attribute_combination pac ON pac.id_product_attribute = pa.id_product_attribute
LEFT JOIN ps_attribute a ON a.id_attribute = pac.id_attribute
LEFT JOIN ps_attribute_lang agl ON agl.id_attribute = a.id_attribute
LEFT JOIN ps_product_attribute_shop pav ON sa.id_product_attribute = pav.id_product_attribute AND pav.id_product = p.id_product
WHERE p.id_product = 4127 AND sm.date_add >= '2022-10-25' AND pl.id_lang = 1
GROUP BY sm.id_stock_mvt
ORDER BY sm.date_add DESC

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

Ensuite une requête pour avoir l'état du stock à une certaine date grâce au code ean13 et au mouvement des stock ci dessus.
Dans mon cas si le code ean contient un certain nombre pour cibler une marque:

-- Remplacez "376000484" par l'ean de votre choix x2 ou même rien pour tous ou l'ean complet pour ciblé un produit, et "2021-10-25" par la date jusqu'à laquelle il va calculer le stock. --

SELECT
    IFNULL(pa.ean13, a.ean13) AS ean13,
    SUM(CASE WHEN sm.sign = 1 THEN sm.physical_quantity ELSE -sm.physical_quantity END) AS Mouvements,
    COALESCE(sas.quantity, a.quantity) AS Stock,
    COALESCE(sas.quantity, a.quantity) - SUM(CASE WHEN sm.sign = 1 THEN sm.physical_quantity ELSE -sm.physical_quantity END) AS stockdate
FROM ps_stock_mvt sm
LEFT JOIN ps_stock_available sas ON sm.id_stock = sas.id_stock_available
LEFT JOIN ps_product_attribute pa ON sas.id_product_attribute = pa.id_product_attribute
LEFT JOIN ps_product a ON sas.id_product = a.id_product
WHERE (pa.ean13 LIKE '%376000484%' OR a.ean13 LIKE '%376000484%') AND sm.date_add >= '2021-10-25'
GROUP BY ean13

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

Et enfin le meilleur pour la fin, un tableau avec l'id, marque, ean13, nom du produit, stock, ventes, combien d'années avant d'être à 0 et le stock à cette date.
Il y a plusieurs dates à changer, et à la fin le nom du produit, par contre il y a une requête sans déclinaisons, et une autre avec:

date1 et date2 au format 2022-10-25 AAAA-MM-JJ

sans déclinaison:

SELECT 
a.`id_product`, 
m.`name` AS `brand` ,
a.`ean13` AS ean13, 
b.`name` AS `product_name`,  
COALESCE(sav_a.`quantity`, sav_b.`quantity`) AS `sav_quantity`, 
SUM(odu.`product_quantity`) AS sold,   
ROUND(COALESCE(sav_a.`quantity`, sav_b.`quantity`) / ROUND(SUM(odu.`product_quantity`) / (TO_DAYS('date2') - TO_DAYS('date1'))*365, 0), 2) AS `sav_per_avg` , 
(SELECT sas.quantity - SUM(CASE WHEN sm.sign = 1 THEN sm.physical_quantity ELSE -sm.physical_quantity END) AS stockdate FROM ps_stock_mvt sm 
INNER JOIN ps_stock_available sas ON sm.id_stock = sas.id_stock_available 
INNER JOIN ps_product pp ON sas.id_product = pp.id_product 
WHERE a.`id_product` = pp.`id_product` AND sm.`date_add` >= "date1" ) AS `stockdate` 

FROM `ps_product` a 
JOIN `ps_product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop IN (2,3,1)) 
LEFT JOIN `ps_shop` shop ON (sa.id_shop = shop.id_shop AND shop.id_shop IN (2,3,1)) 
LEFT JOIN `ps_product_lang` b ON (b.`id_product` = a.`id_product` AND b.`id_lang` = 1 AND b.id_shop = sa.id_shop) 
LEFT JOIN `ps_stock_available` sav_a ON (sav_a.`id_product` = a.`id_product` AND sav_a.id_shop = shop.id_shop AND sav_a.`id_product_attribute` = 0) 
LEFT JOIN `ps_stock_available` sav_b ON (sav_b.`id_product` = a.`id_product` AND sav_b.id_shop = 0 AND sav_b.`id_product_attribute` = 0) 
LEFT JOIN (SELECT 
od.`product_quantity`, 
od.`product_id`, 
od.`product_attribute_id`, 
o.`id_shop`, 
o.`valid`, 
o.`current_state` 
FROM `ps_order_detail` od INNER JOIN `ps_orders` o ON o.`id_order` = od.`id_order` AND o.valid = 1 AND o.`date_add` BETWEEN 'date1' AND 'date2') odu ON odu.`product_id` = a.`id_product` 
LEFT JOIN `ps_manufacturer` m ON (m.id_manufacturer = a.id_manufacturer) 
LEFT JOIN `ps_manufacturer_shop` ms ON (ms.id_manufacturer = m.id_manufacturer AND ms.id_shop = shop.id_shop) 

WHERE 1 AND odu.valid = 1 AND COALESCE(sav_a.id_product_attribute, sav_b.id_product_attribute) = 0 AND shop.`id_shop` = 1 AND b.`name` LIKE '%nom du produit%' 
GROUP BY a.`id_product`, shop.`id_shop` 
ORDER BY `product_name` asc

avec déclinaisons:

SELECT 
a.`id_product`, 
m.`name` AS `brand` ,
pra.`ean13` AS ean13,

(SELECT CONCAT(b.`name`, ' - ', GROUP_CONCAT(' ', al.name)) FROM `ps_stock_available` savs 
INNER JOIN `ps_product_attribute_combination` USING(id_product_attribute) 
INNER JOIN `ps_attribute_lang` as al USING(id_attribute) 
WHERE al.id_lang = 1 AND savs.id_product_attribute = COALESCE(sav_a.id_product_attribute, sav_b.id_product_attribute) ) AS `product_name` ,
 
COALESCE(sav_a.`quantity`, sav_b.`quantity`) AS `sav_quantity`,  
SUM(odu.`product_quantity`) AS sold,  
ROUND(COALESCE(sav_a.`quantity`, sav_b.`quantity`) / ROUND(SUM(odu.`product_quantity`) / (TO_DAYS('date2') - TO_DAYS('date1'))*365, 0), 2) AS `sav_per_avg` , 
(SELECT sas.quantity - SUM(CASE WHEN sm.sign = 1 THEN sm.physical_quantity ELSE -sm.physical_quantity END) AS stockdate FROM ps_stock_mvt sm 
INNER JOIN ps_stock_available sas ON sm.id_stock = sas.id_stock_available 
INNER JOIN ps_product_attribute pa ON sas.id_product_attribute = pa.id_product_attribute 
WHERE sas.id_product_attribute = COALESCE(sav_a.id_product_attribute, sav_b.id_product_attribute) AND sm.`date_add` >= "date1" ) AS `stockdate`

FROM `ps_product` a 
JOIN `ps_product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop IN (2,3,1)) 
LEFT JOIN `ps_shop` shop ON (sa.id_shop = shop.id_shop AND shop.id_shop IN (2,3,1)) 
LEFT JOIN `ps_product_lang` b ON (b.`id_product` = a.`id_product` AND b.`id_lang` = 1 AND b.id_shop = sa.id_shop) 
LEFT JOIN `ps_stock_available` sav_a ON (sav_a.`id_product` = a.`id_product` AND sav_a.id_shop = shop.id_shop ) 
LEFT JOIN `ps_stock_available` sav_b ON (sav_b.`id_product` = a.`id_product` AND sav_b.id_shop = 0 ) 
INNER JOIN `ps_product_attribute` pra ON (pra.`id_product` = a.`id_product` AND pra.id_product_attribute = COALESCE(sav_a.id_product_attribute, sav_b.id_product_attribute)) 
LEFT JOIN (SELECT 
od.`product_quantity`, 
od.`product_id`, 
od.`product_attribute_id`, 
o.`id_shop`, 
o.`valid`, 
o.`current_state` 
FROM `ps_order_detail` od INNER JOIN `ps_orders` o ON o.`id_order` = od.`id_order` AND o.valid = 1 AND o.`date_add` BETWEEN 'date1' AND 'date2') odu ON odu.`product_id` = a.`id_product` AND pra.`id_product_attribute` = odu.`product_attribute_id` 
LEFT JOIN `ps_manufacturer` m ON (m.id_manufacturer = a.id_manufacturer) 
LEFT JOIN `ps_manufacturer_shop` ms ON (ms.id_manufacturer = m.id_manufacturer AND ms.id_shop = shop.id_shop) 

WHERE 1 AND odu.valid = 1 AND NOT COALESCE(sav_a.id_product_attribute, sav_b.id_product_attribute) = 0 AND shop.`id_shop` = 1 AND b.`name` LIKE '%nom du produit%' 
GROUP BY pra.`id_product_attribute`, a.`id_product`, shop.`id_shop` 
ORDER BY `product_name` asc

Je précise que qu'il ne calcul pas les produits des commandes en cours.

Voilà j'espère que ca va en aider certains.

Edited May 11, 2023 by DelosTR (see edit history)

DelosTR · May 12, 2023

J'ai oublié de vous en mettre d'autres que j'ai faite avant:

Valeur stock produits
(stock actuel, même si cmd pas payé)

SELECT  

p.id_product 'Id',
cal.name as Type,
m.name AS 'Marque',
CONCAT(pl.name, ' ', CASE WHEN al.name is not null THEN GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") ELSE "" END) as Produit,
s.quantity as Quantité,   
round(IFNULL(ps.wholesale_price,'0'),2)  'P. Achat',
round(1.2 * ps.price, 2) as `P. Vente`, 
if(s.quantity is null, 0, round(s.quantity*ps.wholesale_price,2)) as `T. Achat`, 
if(s.quantity is null, 0, round(1.2 * s.quantity*ps.price,2)) as `T. Vente`,
if(p.active = 0,'Non','Oui') as `Activé`,
if(sp.reduction, 'Oui', 'Non') as Promo

FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product AND (s.id_product_attribute = pa.id_product_attribute OR CASE WHEN pa.id_product_attribute is null THEN s.id_product_attribute = 0 END))
            
INNER JOIN ps_category_product ca ON (ca.id_product = p.id_product)
INNER JOIN ps_category_lang cal ON (cal.id_category = ca.id_category) 
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
INNER JOIN ps_product_shop ps ON (ps.id_product = p.id_product) 
LEFT JOIN ps_specific_price sp ON (sp.id_product = p.id_product) 
            
WHERE pl.id_lang = 1 AND p.id_shop_default = 1 AND pl.id_product NOT IN (1,2,3,4,5,6)
GROUP BY p.id_product,pac.id_product_attribute  
ORDER BY `Type`  ASC

Produits Hors Stock depuis

SELECT
    o.date_add AS Date_Hors_Stock,
    p.id_product AS id,
    pl.name AS product_name,
    IF(pa.id_product_attribute IS NULL, '', GROUP_CONCAT(CONCAT(agl.name, ': ', al.name) SEPARATOR ', ')) AS attributes,
    CONCAT(
        FLOOR(DATEDIFF(NOW(), o.date_add) / 365), ' ans, ',
        FLOOR(MOD(DATEDIFF(NOW(), o.date_add), 365) / 30), ' mois, ',
        MOD(DATEDIFF(NOW(), o.date_add), 30), ' jours'
    ) AS Hors_Stock_Depuis
FROM ps_stock_available sa
JOIN ps_product p ON sa.id_product = p.id_product
JOIN ps_product_lang pl ON p.id_product = pl.id_product AND pl.id_lang = 1 AND pl.id_shop = 1 
JOIN ps_orders o ON o.id_order = (SELECT MAX(id_order) FROM ps_order_detail WHERE product_id = sa.id_product AND product_attribute_id = sa.id_product_attribute)
LEFT JOIN ps_product_attribute pa ON sa.id_product_attribute = pa.id_product_attribute
LEFT JOIN ps_product_attribute_combination pac ON pa.id_product_attribute = pac.id_product_attribute
LEFT JOIN ps_attribute atr ON pac.id_attribute = atr.id_attribute
LEFT JOIN ps_attribute_lang al ON atr.id_attribute = al.id_attribute AND al.id_lang = 1
LEFT JOIN ps_attribute_group ag ON atr.id_attribute_group = ag.id_attribute_group
LEFT JOIN ps_attribute_group_lang agl ON ag.id_attribute_group = agl.id_attribute_group AND agl.id_lang = 1
WHERE sa.quantity = 0
GROUP BY p.id_product, pa.id_product_attribute  
ORDER BY `Hors_Stock_Depuis`  ASC

Voilà, la prochaine fois (je suis en train de bosser dessus) ce sera encore pour avoir le stock à une date mais différemment, plus fiable mais pas rétroactif.
C'est à dire stocker dans une table le stock tous les jours avec le moins de données possible.

DelosTR · May 16, 2023

Bonjour, et je reviens pour un autre partage pour la gestion du stock.

C'est une des données importante qui manque à prestashop, savoir l'état du stock à une date, sur mes précédents posts j'explique comment avoir cette donnée mais avec les mouvements de stock, qui je pense n'est pas très fiable, exemple si on change le stock dans une fiche produit etc..

J'ai donc mis en place un système qui va stocker cette donnée tous les jours avec le moins d'infos possible, juste une colonne, + 1 par jour. L’inconvénient c'est que vous n'aurez pas l'info avant la date où vous l'avez mis en place, mais ca servira plus tard.

Allez on commence par créer une nouvelle table:

CREATE TABLE IF NOT EXISTS `daily_stock` (
  `id_stock` int(11) NOT NULL,
  PRIMARY KEY (`id_stock`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

on créer le fichier: daily_stock.php puis on va le mettre dans un cron, exemple moi tous les jours à 23h.

à partir de là c'est bon vos données seront stockés, ca utilise seulement le id_stock_available, un identifiant unique du stock des produits et déclinaisons.
Maintenant on va voir comment traiter ces données. Déjà simple en sql:

SELECT
    ds.*,
    sa.id_product AS ID,
    IF(pa.ean13 IS NULL, p.ean13, pa.ean13) AS EAN13, 
    pl.name AS Produit,
    al.name AS Déclinaison
FROM daily_stock ds
JOIN ps_stock_available sa ON ds.id_stock = sa.id_stock_available
JOIN ps_product_lang pl ON sa.id_product = pl.id_product
LEFT JOIN ps_product_attribute pa ON sa.id_product_attribute = pa.id_product_attribute
LEFT JOIN ps_product_attribute_combination pac ON pac.id_product_attribute = pa.id_product_attribute
LEFT JOIN ps_attribute a ON a.id_attribute = pac.id_attribute
LEFT JOIN ps_attribute_lang al ON al.id_attribute = a.id_attribute AND al.id_lang = 1
JOIN ps_product p ON sa.id_product = p.id_product
WHERE pl.id_lang = 1 AND pl.id_shop = 1 AND (pa.ean13 LIKE '%votreean13%' OR p.ean13 LIKE '%votreean13%')
ORDER BY sa.id_product DESC

Vous pouvez donc filtrer dans le WHERE, à la place de votreean13 x2 (ca peux être juste le début de l'ean13 aussi)
Mais aussi par nom si vous remplacer AND (pa.ean13 LIKE '%votreean13%' OR p.ean13 LIKE '%votreean13%') par:
AND pl.name LIKE '%nomduproduit%'

Inconvénient (en tout cas j'ai pas réussi) c'est que ca sort toutes les dates, impossible car la date est aussi le nom de la colonne.
Mais j'ai trouvé comment faire grâce à un script:

On créer le fichier daystock.php:

Puis le daystock.html:

Ce petit script va faire une page où vous pourrez filtrer par id, nom, ean et surtout la date.
Du coup grâce à ce script on va pouvoir extraire une partie du nom de la colonne et la traiter.
Et surtout ca sort le résultat en un fichier .csv avec un lien pour le télécharger.

Voilà j'espère que ca va en aider plusieurs, c'est utile j'imagine pour des inventaires, mais dans mon cas c'est pour savoir pendant combien de temps un produit a été hors stock durant l'année, avec tout ca on peux sortir des graphiques pour voir l'évolution du stock.

Edited May 16, 2023 by DelosTR (see edit history)

DelosTR · May 16, 2023

J'arrive pas à intégrer certains codes, je retente ici.
alors le daily_stock.php:

<?php

require_once '../config/config.inc.php';
require_once '../config/settings.inc.php';
$mysqli = new mysqli(_DB_SERVER_, _DB_USER_, _DB_PASSWD_, _DB_NAME_, "3306");


$today = date("Y-m-d");
$column_name = $today;

$query0 = "
INSERT INTO `daily_stock` (`id_stock`)
SELECT sa.`id_stock_available`
FROM `ps_stock_available` sa
WHERE NOT EXISTS (SELECT 1 FROM `daily_stock` ds WHERE ds.`id_stock` = sa.`id_stock_available`)";

$query1 = "ALTER TABLE `daily_stock` ADD `stock:".$column_name."` INT NULL DEFAULT NULL";

$query2 = "
UPDATE daily_stock ds
INNER JOIN `ps_stock_available` sa ON
ds.`id_stock` = sa.`id_stock_available` 
SET `stock:".$column_name."` = sa.`quantity`";

// Exécution de la requête
$mysqli->query($query0);
$mysqli->query($query1);
$mysqli->query($query2);

$mysqli->close();
?>

DelosTR · May 16, 2023

Le daystock.php:

<?php
if ($_SERVER["REQUEST_METHOD"] === "POST") {
    // Vérification des paramètres de date
    if (isset($_POST["dateDebut"]) && isset($_POST["dateFin"])) {
        $dateDebut = $_POST["dateDebut"];
        $dateFin = $_POST["dateFin"];
        $idProduct = $_POST["idProduct"];
        $nomProduit = $_POST["nomProduit"];
        $ean13 = $_POST["ean13"];

        require_once '../config/config.inc.php';
        require_once '../config/settings.inc.php';
        $connexion = new mysqli(_DB_SERVER_, _DB_USER_, _DB_PASSWD_, _DB_NAME_, "3306");

        // Vérification de la connexion
        if ($connexion->connect_error) {
            die("Erreur de connexion à la base de données : " . $connexion->connect_error);
        }

        // Construction de la requête dynamique
        $sql = "SELECT ds.id_stock";
        $result = $connexion->query("SHOW COLUMNS FROM daily_stock");
        while ($row = $result->fetch_assoc()) {
            $columnName = $row['Field'];
            if (strpos($columnName, 'stock:') === 0) {
                $date = substr($columnName, strlen('stock:'));
                if ($date >= $dateDebut && $date <= $dateFin) {
                    $sql .= ", ds.`$columnName`";
                }
            }
        }

        $sql .= ", sa.id_product AS ID, IF(pa.ean13 IS NULL, p.ean13, pa.ean13) AS EAN13, pl.name AS Produit, al.name AS Declinaison
        FROM daily_stock ds
        JOIN ps_stock_available sa ON ds.id_stock = sa.id_stock_available
        JOIN ps_product_lang pl ON sa.id_product = pl.id_product
        LEFT JOIN ps_product_attribute pa ON sa.id_product_attribute = pa.id_product_attribute
        LEFT JOIN ps_product_attribute_combination pac ON pac.id_product_attribute = pa.id_product_attribute
        LEFT JOIN ps_attribute a ON a.id_attribute = pac.id_attribute
        LEFT JOIN ps_attribute_lang al ON al.id_attribute = a.id_attribute AND al.id_lang = 1
        JOIN ps_product p ON sa.id_product = p.id_product
        WHERE pl.id_lang = 1 AND pl.id_shop = 1";

        // Ajout de la condition pour l'ID du produit si spécifié
        if (!empty($idProduct)) {
            $sql .= " AND sa.id_product = $idProduct";
        }

        // Ajout de la condition pour le nom du produit si spécifié
        if (!empty($nomProduit)) {
            $sql .= " AND pl.name LIKE '%$nomProduit%'";
        }

        // Ajout de la condition pour l'EAN13 si spécifié
        if (!empty($ean13)) {
            $sql .= " AND (pa.ean13 LIKE '%$ean13%' OR p.ean13 LIKE '%$ean13%')";
        }

        $sql .= " ORDER BY pl.name ASC";

        $queryResult = $connexion->query($sql);
        $filename = "Daily_Stock.csv";
        $file = fopen($filename, 'w');
        $delimiter = ';';
        $header = array('id_stock');

        $result = $connexion->query("SHOW COLUMNS FROM daily_stock");
        while ($row = $result->fetch_assoc()) {
            $columnName = $row['Field'];
            if (strpos($columnName, 'stock:') === 0) {
                $date = substr($columnName, strlen('stock:'));
                if ($date >= $dateDebut && $date <= $dateFin) {
                    $header[] = $columnName;
                }
            }
        }

        $header[] = 'ID'; $header[] = 'EAN13'; $header[] = 'Produit'; $header[] = 'Declinaison';
        fputcsv($file, $header, $delimiter);

        if ($queryResult->num_rows > 0) {
            while ($row = $queryResult->fetch_assoc()) {
                $rowData = array();
                foreach ($header as $columnName) { $rowData[] = $row[$columnName];
            }
            fputcsv($file, $rowData, $delimiter);
        }} else {
            echo "Aucun résultat trouvé.";
        }

fclose($file);
$connexion->close();

$downloadLink = '<a href="' . $filename . '" download>Télécharger le fichier CSV</a>';
echo '<p>' . $downloadLink . '</p>';
    } else {
        echo "Erreur.";
    }
}
?>

DelosTR · May 16, 2023

Et le daystock.html j'arrive pas à l'intégrer ca me met une erreur donc j'abandonne j'ai autre chose à faire.

Me demander par mp si besoin.

Sign In

Partage de requêtes SQL: Stock à date, temps d'écouler le stock, exportation des mouvements..

Recommended Posts

DelosTR

Link to comment

Share on other sites

DelosTR

Link to comment

Share on other sites

DelosTR

Link to comment

Share on other sites

DelosTR

Link to comment

Share on other sites

DelosTR

Link to comment

Share on other sites

DelosTR

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Browse

Activity

Go back to prestashop.com