I need help with a difficult query. This has to do with the complex way coupons are implemented in Spree Commerce platform.
I simply need the total amount of outstanding coupons.
The tables are:
preferences
id | value | key
---+---------------------------------------------
1 | 25 | spree/calculator/flat_rate/amount/5
calculators
id | calculable_id | calculable_type
----+---------------+-----------------
5 | 3 | promotion_action
promotion_actions
id | activator_id
----+-------------
3 | 2
activators
id | expires_at | usage_limit
---+------------+------------
2 | 2013-12-01 | 4
adjustments
originator_type | originator_id | amount
-----------------+---------------+-------
promotion_action | 3 | -25
The last number in preferences.key corresponds to the id of the calculator.
First I need the total of preferences.amount, multiplied by activators.usage_limit (unless activators.expires_at < today), where the preferences.key LIKE '%calculator/flat_rate%'.
The result should be this amount minus the total sum of the corresponding adjustments.amount
I got as far as
select
(select sum(value) from spree_preferences
where `key` like "%calculator/flat_rate%")
+
(select sum(amount) from spree_adjustments
where originator_type = 'promotion_action') as total;
,but this doesn't take into account the expires_at and usage_limit.
For updated spree, the answer is as below:
select sum(subAggregate.outstanding)
from
(
select (subDetail.value * subDetail.multiplier) + subDetail.adjustmentAmount as outstanding
from
(
select p.value,
case
when a.expires_at > curDate() then a.usage_limit
else 1
end as multiplier ,
ifNull(adj.amount,0) as adjustmentAmount
from spree_preferences p
left outer join spree_calculators c
on replace(p.key,'spree/calculator/flat_rate/amount/','') = c.id
left outer join spree_promotion_actions pa
on c.calculable_id = pa.id
and c.calculable_type = 'Spree::PromotionAction'
left outer join spree_promotions a
on pa.promotion_id = a.id
left outer join spree_adjustments adj
on pa.id = adj.source_id
and pa.type = 'Spree::PromotionAction'
) subDetail
) subAggregate
select sum(subAggregate.outstanding)
from
(
select (subDetail.value * subDetail.multiplier) + subDetail.adjustmentAmount as outstanding
from
(
select p.value,
case
when a.expires_at > curDate() then a.usage_limit
else 1
end as multiplier ,
ifNull(adj.amount,0) as adjustmentAmount
from preferences p
left outer join calculators c
on replace(p.key,'spree/calculator/flat_rate/amount/','') = c.id
left outer join promotion_actions pa
on c.calculabel_id = pa.id
and c.calculable_type = 'promotion_action'
left outer join activators a
on pa.activator_id = a.id
left outer join adjustments adj
on pa.id = adj.originator_id
and pa.originator_type = 'promotion_action'
) subDetail
) subAggregate