complexe vraag
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?
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
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;
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
1 pakje servetten - naam_1
2 rollen cadeaupapier - naam_2
10 dozen knikkers - naam_3
betekent niet dat je dat zo moet opslaan in je database.
Dat ga je eerst uit elkaar halen en in een bruikbaar formaat opslaan.
En als een verpakking 2 rollen van iets bevat, dan verkoop je nog steeds 1 artikel.
Ik bedoel: als je bij de supermarkt een pak haalt met 6 rollen WC papier, dan 1 dat 1 artikel. Niet 6. Idem voor het pak met 10 rollen: nog steeds 1 (ander) artikel.
Verkoop je de rollen los, dan moet je een voorraad registreren als 16 op voorraad en een keer 6 en een keer 10 verkocht.
Maar stap in een zo vroeg mogelijk stadium af van "de eerste cijfers (if any) zeggen iets over het aantal items in de verpakking".
Dat substr_index() maakt je query onoverzichtelijk en waarschijnlijk ook flink trager.
Nog te zwijgen van het risico dat de getallen soms bij de naam horen (3M is een bekend merk) of dat onverwacht meer of minder cijfers aanwezig zijn.
Ik denk dat ik niet helemaal duidelijk ben in de situatie.
Wij gebruiken Prestashop 1.7.8.8. Daarmee kunnen we producten verkopen. Die gaan per stuk. Bijvoorbeeld 1 rol.
Maar het is ook mogelijk om de producten te combineren. Wij combineren dan 2 rollen tot 1 nieuw product. Prestashop regelt de voorraad.
Alleen als ik dan de voorraad bekijk van 2 rollen, is die natuurlijk de helft zo groot. Als ik 21 rollen heb, is de voorraad van het product met 2 rollen 10.
Maar ik wil de voorraad per rol op mijn lijst tonen. In mijn SQL code lees ik dus alleen maar uit de database.
In de titel moet ik er voor zorgen dat de naamgeving juist is. Een merk naam zet ik niet in de titel. Dus dat probleem is er niet.
Frits van Leeuwen op 17/02/2023 09:54:36:
Alleen als ik dan de voorraad bekijk van 2 rollen, is die natuurlijk de helft zo groot. Als ik 21 rollen heb, is de voorraad van het product met 2 rollen 10.
Dat is een wonderbaarlijke vermenigvuldiging. Je lijkt nu niet 21 rollen op voorraad te hebben, maar 41 rollen, namelijk:
10 × 2 rollen
+ 21 × 1 rol
Iemand die bijvoorbeeld 25 rollen nodig heeft, bestelt dan:
10 × 2 rollen
+ 5 × 1 rol
En die heb je niet: je hebt er maar 21. Teleurgestelde kopers haken dan meestal af, verlaten de winkelwagen en komen nóóit meer terug. Een enkeling belt op en die moet je dan alsnog telefonisch teleurstellen.
Je krijgt met deze hard-coded vermenigvuldiging van databasegegevens vroeg of laat onverklaarbare fouten — bijvoorbeeld wanneer je elders weer vergeet te delen door het juiste aantal ... dat je alleen als string vooraan de omschrijving hebt gezet. Dat lijkt een leuk bedachte hack, maar je gebruikt een datamodel zoals het nooit bedoeld is.
Ik zou het daarom anders oplossen: zoek naar een PrestaShop-addon die uit de voeten kan met product bundles of multipacks. Daarvan zijn er genoeg:
https://addons.prestashop.com/en/505-cross-selling-product-bundles
Het gaat mij hier om hoe ik de juiste voorraad op papier kan krijgen van een enkel product.
Ik ben gaan zoeken ik de prestashop database en vond de tabel prstshp_product het veld 'product_type'. In het geval van een combinatie verkoop staat hier pack.
Ook vond ik de tabel prstshp_pack
Hier in staat het veld 'id_product_pack'. Dit bevat de id uit prstshp_products (id_product). En is dus te koppelen.
Ook staat hier het veld id_product_item. Dit bevat eveneens een id uit prstshp_products (id_product) Dit is het product dat verveelvoudigd wordt.
Tenslotten staat hier ook het veld 'quantity'. Dit is het aantal dat bij verkoop wordt afgeboekt bij het gelininkte product.
Hier de code voor zover ik hem nu heb.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
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,
if (-- Is het een combinatie product?
prstshp_product.product_type = 'pack',
-- Het is een combinatie product. Zoek het gecombineerde product en neem daar de voorraad van. (prstshp_pack.id_product_item)
'',
-- Het is geen combinatie product Neem de voorraad die is gegeven.
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
LEFT JOIN prstshp_pack
ON prstshp_pack.id_product_pack = prstshp_product.id_product
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;
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,
if (-- Is het een combinatie product?
prstshp_product.product_type = 'pack',
-- Het is een combinatie product. Zoek het gecombineerde product en neem daar de voorraad van. (prstshp_pack.id_product_item)
'',
-- Het is geen combinatie product Neem de voorraad die is gegeven.
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
LEFT JOIN prstshp_pack
ON prstshp_pack.id_product_pack = prstshp_product.id_product
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;
Deze hele benadering van hoe wordt omgegaan met data is naar mijn mening een héél slecht idee. Het is overdreven omslachtig en foutgevoelig.
Waarom niet gewoon aparte kolommen in de database tabel waarin je hoeveelheid, eenheid en naam uitsplitst?
Je gebruikt nu feitelijk SQL om zo'n beetje een hele lap business logica te regelen. Ik zie zelfs vroeg of laat problemen ontstaan doordat de mogelijkheden in SQL opraken (dus wat je kúnt doen in SQL, welke functies je hebt, etc.) als je business logica ooit verandert of wordt uitgebreid.
Gewijzigd op 20/02/2023 17:41:31 door Mark Hogeveen