Getting sales values by day in CakePHP and MySQL

I have a sales 开发者_C百科model, with a salesitems related model, the sales model has some modifiers, ie discount.

To get sales totals, I have done this:

var $virtualFields = array(
  'total' => '@vad:=(SELECT COALESCE(SUM(price*quantity), 0) FROM saleitems WHERE saleitems.sale_id = Sale.id)',
  'paid' => '@pad:=(SELECT COALESCE(SUM(amount), 0) FROM payments WHERE payments.sale_id = Sale.id)',
  'discountamount' => '@dis:=(SELECT COALESCE(SUM(price*quantity), 0) FROM saleitems WHERE saleitems.sale_id = Sale.id)*(0.01 * Sale.discount)',
  'saleamount' => '@vad - @dis',  
);

Which all seems to be working well. However, when I come to do some reporting, and try to get total sales amount per day, I have run up against the limit of brain power. Should I just tot them up in PHP, or run a query? Or is there a way to do this with Cake's ORM?

I tried the query method:

SELECT 
created,  
(@vad:=(SELECT COALESCE(SUM(price*quantity), 0) FROM saleitems WHERE `saleitems`.`sale_id` = `Sale`.`id`)) AS `Sale__total`,
(@pad:=(SELECT COALESCE(SUM(amount), 0) FROM payments WHERE `payments`.`sale_id` = `Sale`.`id`)) AS `Sale__paid`,
(@dis:=(SELECT COALESCE(SUM(price*quantity), 0) FROM saleitems WHERE `saleitems`.`sale_id` = `Sale`.`id`)*(0.01 * `Sale`.`discount`)) AS `Sale__discountamount`, 
sum(@vad - @dis) AS `Sale__saleamount` 
FROM `sales` AS `Sale` WHERE `Sale`.`account_id` = 37 GROUP BY DAY(`Sale`.`created`) order by created

But this is giving me completely incorrect answers.

you can run this query:

SELECT SUM((si.price * si.quantity) * (1 - (0.01 * s.discount))) AS SalesByDay
FROM sales s JOIN saleitems si ON s.id = si.sale_id
WHERE s.account_id = 37
GROUP BY DATE(s.created)

Notes:

• The DAY function, returnes the day of the month, not the date
• I did not join the payments table since i do not see where you use the @pad variable