php - 在 CakePHP 和 MySQL 中获取每日销售值

Question

我有一个销售模型，具有与销售项目相关的模型，该销售模型有一些修饰符，即折扣。

为了获得销售总额，我这样做了:

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',  
);

这一切似乎都运作良好。然而，当我来做一些报告，并试图获得每天的总销售额时，我已经遇到了脑力的极限。我应该用 PHP 将它们加起来，还是运行查询？或者有没有办法用 Cake 的 ORM 来做到这一点？

我尝试了查询方法:

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

但这给了我完全错误的答案。

Answer 1

您可以运行此查询:

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)

注释:

• DAY 函数返回月份中的某一天，而不是日期
• 我没有加入付款表，因为我看不到您在哪里使用 @pad 变量