Senza categoria

Coupon – Edises

Posted on by FabioS in Senza categoria

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