Alright, so in case anyone searches for it, this is a no-guarantees sql script that sorta fixes the amounts (requires MySQL with JSON support, e.g MariaDB 10.2+ or MySQL 5.7+):
# update cscart_vendor_payouts up JOIN (
select p.order_id,
# commission_amount as current_amount,
# p.total, p.shipping_cost,
# bd.bottle_deposit,p.commission,
ROUND(
LEAST(
ABS(
(
(
p.total - p.shipping_cost - p.surcharge_from_total -
- p.taxes
) *
p.commission / 100
) +
p.fixed_commission + surcharge_to_commission
),
p.total
)
, 2
) as commission_amount
FROM (
select p.*,
CAST(p.extra -> '$.commission_amount' as DECIMAL(10, 2)) as commission_amount,
CAST(p.extra -> '$.total' as DECIMAL(10, 2)) as total,
CAST(p.extra -> '$.shipping_cost' as DECIMAL(10, 2)) as shipping_cost,
o.payment_surcharge as surcharge_from_total,
CAST(p.extra -> '$.taxes' as DECIMAL(10, 2)) as taxes,
CAST(p.extra -> '$.fixed_commission' as DECIMAL(10, 2)) as fixed_commission,
CAST(p.extra -> '$.surcharge_to_commission' as DECIMAL(10, 2)) as surcharge_to_commission
FROM (
SELECT CONVERT(p.extra using utf8mb4) as extra, order_id, commission
from cscart_vendor_payouts p
) as p
join cscart_orders o ON o.order_id = p.order_id
) as p
# ) p ON p.order_id = up.order_id
# SET up.commission_amount = p.calc_amount WHERE up.commission_amount != p.calc_amount
;
And the code can just be rolled back to what it was previously (see diff screenshot above)
Sign In
6
