XML feed synchronisatie
Leveranciers kunnen met een XML feed artikelen opvoeren c.q updaten.
Echter kan uit de feed niet (altijd) worden afgeleid of het een nieuw artikel betreft.
Daarnaast zijn er per artikel verschillende configuraties (sku's) mogelijk (kleur/maat) en dit wordt in een aparte tabel bij gehouden.
Verder kan het zijn dat een reeds ingevoerd artikel niet meer in de feed staat en in dat geval moet de voorraad van het alle tot het artikel behorende sku's op nul gezet worden.
Hetzelfde kan ook bij de sku's voorkomen.
Ik heb hier het volgende voor bedacht:
Ik sla de gegevens uit de feed op in 'tijdelijke' tabellen (met preps).
Daarna ga ik vanuit die tabellen de officiële bewerken:
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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
<?php
/*
* Synchronize existing products
*/
$this->pdo->query("UPDATE products p, temp_products t
SET
p.product_name = t.product_name,
p.product_description = t.product_description,
p.product_new_price = t.product_new_price,
p.material_id = t.material_id,
p.brand_id = t.brand_id
WHERE
p.product_code = t.product_code AND p.supplier_id = " . $supplier_id);
/*
* Insert new products from feed
*/
$this->pdo->query("INSERT INTO products
(product_code, product_name, product_description,
product_barcode, maingroup_id, category_id, subcategory_id,
product_price, product_new_price, material_id, collection_id,
brand_id, supplier_id, created)
SELECT
t.product_code, t.product_name, t.product_description,
t.product_barcode, t.maingroup_id, t.category_id,
t.subcategory_id, t.product_price, t.product_new_price,
t.material_id, t.collection_id, t.brand_id, t.supplier_id, NOW()
FROM
temp_products t
LEFT JOIN
products p
ON
p.product_code = t.product_code
WHERE t.supplier_id = " . $supplier_id . " AND p.product_code IS NULL
");
/*
* insert or update sku's from feed tables
*/
$this->pdo->query("INSERT INTO
products_skus
(product_id, color_id, size_id, stock_amount)
SELECT
p.product_id, s.color_id, s.size_id, s.stock_amount
FROM
temp_skus s
JOIN
temp_products t
ON t.product_id = s.product_id
JOIN
products p USING (product_code, supplier_id)
WHERE p.supplier_id = " . $supplier_id . "
ON DUPLICATE KEY
UPDATE stock_amount =
IF(VALUES(stock_amount) < stock_amount, VALUES(stock_amount),
stock_amount");
/*
* set stock to zero for removed products or removed sku's
*/
$this->pdo->query(
'UPDATE products_skus s
JOIN products p
ON s.product_id = p.product_id
LEFT JOIN temp_products t
ON p.product_code = t.product_code
LEFT JOIN temp_skus ts
ON t.product_id = ts.product_id
AND s.color_id = ts.color_id
AND s.size_id = ts.size_id
SET s.stock_amount = 0
WHERE (t.product_id IS NULL OR ts.size_id IS NULL)
AND p.supplier_id = ' . $supplier_id);
/*
* prepare missing images for upload
*/
$this->pdo->query(
'INSERT INTO upload_batch
(product_id, color_id, link, processed)
SELECT
p.product_id, u.color_id, u.link, 0
FROM
products p
JOIN
temp_products t
ON t.product_code = p.product_code
JOIN
temp_upload u
ON t.product_id = u.product_id
LEFT JOIN
product_images i
ON p.product_id = i.product_id
WHERE p.supplier_id = ' . $supplier_id . '
AND p.created > NOW() - INTERVAL 1 HOUR AND i.image_id IS NULL');
?>
/*
* Synchronize existing products
*/
$this->pdo->query("UPDATE products p, temp_products t
SET
p.product_name = t.product_name,
p.product_description = t.product_description,
p.product_new_price = t.product_new_price,
p.material_id = t.material_id,
p.brand_id = t.brand_id
WHERE
p.product_code = t.product_code AND p.supplier_id = " . $supplier_id);
/*
* Insert new products from feed
*/
$this->pdo->query("INSERT INTO products
(product_code, product_name, product_description,
product_barcode, maingroup_id, category_id, subcategory_id,
product_price, product_new_price, material_id, collection_id,
brand_id, supplier_id, created)
SELECT
t.product_code, t.product_name, t.product_description,
t.product_barcode, t.maingroup_id, t.category_id,
t.subcategory_id, t.product_price, t.product_new_price,
t.material_id, t.collection_id, t.brand_id, t.supplier_id, NOW()
FROM
temp_products t
LEFT JOIN
products p
ON
p.product_code = t.product_code
WHERE t.supplier_id = " . $supplier_id . " AND p.product_code IS NULL
");
/*
* insert or update sku's from feed tables
*/
$this->pdo->query("INSERT INTO
products_skus
(product_id, color_id, size_id, stock_amount)
SELECT
p.product_id, s.color_id, s.size_id, s.stock_amount
FROM
temp_skus s
JOIN
temp_products t
ON t.product_id = s.product_id
JOIN
products p USING (product_code, supplier_id)
WHERE p.supplier_id = " . $supplier_id . "
ON DUPLICATE KEY
UPDATE stock_amount =
IF(VALUES(stock_amount) < stock_amount, VALUES(stock_amount),
stock_amount");
/*
* set stock to zero for removed products or removed sku's
*/
$this->pdo->query(
'UPDATE products_skus s
JOIN products p
ON s.product_id = p.product_id
LEFT JOIN temp_products t
ON p.product_code = t.product_code
LEFT JOIN temp_skus ts
ON t.product_id = ts.product_id
AND s.color_id = ts.color_id
AND s.size_id = ts.size_id
SET s.stock_amount = 0
WHERE (t.product_id IS NULL OR ts.size_id IS NULL)
AND p.supplier_id = ' . $supplier_id);
/*
* prepare missing images for upload
*/
$this->pdo->query(
'INSERT INTO upload_batch
(product_id, color_id, link, processed)
SELECT
p.product_id, u.color_id, u.link, 0
FROM
products p
JOIN
temp_products t
ON t.product_code = p.product_code
JOIN
temp_upload u
ON t.product_id = u.product_id
LEFT JOIN
product_images i
ON p.product_id = i.product_id
WHERE p.supplier_id = ' . $supplier_id . '
AND p.created > NOW() - INTERVAL 1 HOUR AND i.image_id IS NULL');
?>
Dit alles duurt bij elkaar ca. 15 sec. voor zo'n 1700 artikelen.
Lijkt mij acceptabel maar misschien dat iemand een snellere / betere oplossing weet
Gewijzigd op 31/05/2014 16:57:26 door Ger van Steenderen
Ik heb het terug gebracht naar ca. 2.5 seconden.
Ik vul nu maar één tabel (temp_products) met prepared statements, de andere twee met bulk inserts.
Dat scheelde zo'n 9 seconden, de rest was een kwestie van een vergeten index.