Ik heb een vrij eenvoudig lijstje, maar die komt op een (voor mij) complexe manier tot stand.
Ik heb een werkende SQL code die ik hier zo zal delen.
Maar eerst even wat uitleg wat ik wil en wat ik heb.
Ik heb verschillende producsoorten. Die beginnen meestal met een getal en dan een eenheid en dan het fijtelijke productsoort.
Deze product soorten zitten in de titel van een product. Dat ziet er dan als volgt uit.
Voorbeelden:
1 pakje servetten - naam_1
2 rollen cadeaupapier - naam_2
10 dozen knikkers - naam_3
enz.
Met mijn SQL code ontleed ik de titels. De getallen gebruik ik om aantallen weer te berekenen en de eenheden laat ik weg. Maar de product soort toon ik weer wel. net als de naam.
De producten hebben een voorraad. Deze haal ik rechtstreeks uit de database. Zolang er een product altijd met de zelfde productsoort (hoeveelheid) verkocht wordt, is dat geen probleem. Maar als een product soorten met verschillende aantallen zijn, wordt het lastig om de voorraad op te halen of te berekenen.
Stel dat er ook "1 rol cadeaupapier - naam_2" verkocht wordt. Dan heb ik dus een product soort met 1 en een productsoort met 2 de zelfde producten. Ik zou in dat geval de voorraad willen ophalen van de productsoort waar er 1 tegelijk verkocht wordt.
Anders zou ik hem de vooraad vermenigvuldigen met het aantal van de productsoort.
Het is een heel verhaal geworden, en ik hoop dat ik duidelijk ben.
Wie kan mij helpen dit dilemma op te lossen?
SELECT
SUM(
-- producten in meervoud herkennen en er mee rekenen samen met de enkelvoudige producten.
if( -- begint name met een nummer?
SUBSTRING_INDEX(prstshp_order_detail.product_name, ' ', 1) REGEXP '^[0-9]+$',
-- Het begint met een nummer!
-- substring omvormen naar integer
CAST(CAST(SUBSTRING_INDEX(prstshp_order_detail.product_name, ' ', 1) AS FLOAT) AS INT) * prstshp_order_detail.product_quantity,
-- Het begint niet met eeen nummer!
prstshp_order_detail.product_quantity
)
) AS aantal,
-- producten in meervoud herkennen en omvormen tot de enkelvoudige producten.
if( -- begint name met een nummer?
SUBSTRING_INDEX(prstshp_order_detail.product_name, ' ', 1) REGEXP '^[0-9]+$',
-- Het begint met een nummer!
SUBSTRING( prstshp_order_detail.product_name,
LENGTH( SUBSTRING_INDEX( prstshp_order_detail.product_name, ' ', 2) ) +2,
LENGTH( SUBSTRING_INDEX( prstshp_order_detail.product_name, ' - ', 1) ) - LENGTH( SUBSTRING_INDEX( prstshp_order_detail.product_name, ' ', 2 ) ) -1) ,
-- Het begint niet met eeen nummer!
SUBSTRING_INDEX(prstshp_order_detail.product_name, ' - ', 1)
) AS soort,
prstshp_manufacturer.name AS merk,
SUBSTRING(
prstshp_order_detail.product_name,
4+LENGTH(SUBSTRING_INDEX(prstshp_order_detail.product_name, ' - ', 1)),
length(prstshp_order_detail.product_name)-3-LENGTH(SUBSTRING_INDEX(prstshp_order_detail.product_name, ' - ', 1))
) AS titel,
-- producten in meervoud herkennen en er mee rekenen samen met de enkelvoudige producten.
if( -- begint name met een nummer?
SUBSTRING_INDEX(prstshp_order_detail.product_name, ' ', 1) REGEXP '^[0-9]+$',
-- Het begint met een nummer!
-- Dan moet de voorraad vermenigvuldigd worden.
CAST(CAST(SUBSTRING_INDEX(prstshp_order_detail.product_name, ' ', 1) AS FLOAT) AS INT) * prstshp_stock_available.quantity,
-- Het begint niet met eeen nummer!
prstshp_stock_available.quantity
) AS voorraad,
prstshp_product.mpn AS ArtNr
FROM prstshp_orders
LEFT JOIN prstshp_order_detail
on prstshp_order_detail.id_order = prstshp_orders.id_order
LEFT JOIN prstshp_product
on prstshp_product.id_product = prstshp_order_detail.product_id
LEFT JOIN prstshp_stock_available
on prstshp_stock_available.id_product = prstshp_order_detail.product_id
LEFT JOIN prstshp_manufacturer
on prstshp_manufacturer.id_manufacturer = prstshp_product.id_manufacturer
WHERE prstshp_orders.current_state=2 OR prstshp_orders.current_state=11
GROUP BY
soort,
merk,
titel
ORDER BY
soort ASC,
merk ASC,
titel ASC;
Hoe kan ik nu in deze SQL code checken of dat een besteld product per 1 wordt verkocht als hij ook in andere aantallen verkocht wordt.
Met vriendelijke groet,
Frits van Leeuwen
