crea tabella con tutti i dati necessari
CREATE TABLE sconti_by_percent AS
SELECT
sr.rule_id,
sr.name AS rule_name,
sr.description AS rule_description,
sr.is_active AS rule_is_active,
sr.simple_action,
sr.conditions_serialized,
sr.actions_serialized AS ACTION,
sr.from_date,
sr.to_date,
sr.discount_amount,
sr.uses_per_coupon,
sr.uses_per_customer,
src.coupon_id,
src.code AS coupon_code,
src.times_used AS coupon_times_used,
src.expiration_date AS coupon_expiration_date
FROM
salesrule sr
JOIN
salesrule_coupon src ON sr.rule_id = src.rule_id
WHERE
sr.simple_action = 'by_percent'
ORDER BY
sr.rule_id;
sr.simple_action = by_percent, by_fixed, cart_fixed, ampromo_items
ALTER TABLE sconti_by_percent
ADD COLUMN attribute_value VARCHAR(255),
ADD COLUMN category_value TEXT;
--
UPDATE sconti_by_percent
SET attribute_value = JSON_UNQUOTE(JSON_EXTRACT(ACTION, '$.conditions[0].attribute')),
category_value = JSON_UNQUOTE(JSON_EXTRACT(ACTION, '$.conditions[0].value'));
Per capire quanti coupon esistono per tipologia
SELECT
simple_action,
COUNT(*) AS rule_count
FROM
salesrule
GROUP BY
simple_action
ORDER BY
rule_count DESC;
visualizzare la lista coupon, usare la clausula WHERE sr.simple_action = ” in combinazione con i risultati forniti dalla query precedente
SELECT
sr.rule_id,
sr.name AS rule_name,
sr.description AS rule_description,
sr.is_active AS rule_is_active,
sr.simple_action,
sr.conditions_serialized,
src.coupon_id,
src.code AS coupon_code,
src.times_used AS coupon_times_used,
src.expiration_date AS coupon_expiration_date
FROM
salesrule sr
JOIN
salesrule_coupon src ON sr.rule_id = src.rule_id
WHERE
sr.simple_action = 'by_percent'
ORDER BY
sr.rule_id;
Risorsa utile: https://chatgpt.com/c/bc009109-4fdc-4636-b64d-aa73a0796b9e
Estrarre entity_id dei vari attributi
SELECT attribute_value, COUNT(*) AS count
FROM sconti_by_percent
GROUP BY attribute_value;
In base alla regola che troviamo, ad esempio se hanno attributi, bisogna estrarli e trovare l’entity_id
SELECT entity_id
FROM catalog_product_entity_int
WHERE attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'disalsingoli' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'))
AND value = 1;
trovato l’entity_id bisogna importare la regola, in caso di eb_eserc_sost_anief sappiamo che c’è uno sconto del 100% uso per coupon 1 su entity_id 1358, quindi vanno presi tutti i codici coupon nella tabella sconti_by_percent nel campo_coupon_code