public function getSales($start_date = null, $end_date = null)
{
$paid_date_sql = shopOrderModel::getDateSql('o.paid_date', $start_date, $end_date);
$sql = "SELECT\n t.*,\n SUM(ps.price*pcur.rate*oi.quantity) AS sales\n FROM shop_order AS o\n JOIN shop_order_items AS oi\n ON oi.order_id=o.id\n JOIN shop_product AS p\n ON oi.product_id=p.id\n JOIN shop_product_skus AS ps\n ON oi.sku_id=ps.id\n JOIN shop_currency AS pcur\n ON pcur.code=p.currency\n JOIN shop_type AS t\n ON t.id=p.type_id\n WHERE {$paid_date_sql}\n AND oi.type = 'product'\n GROUP BY t.id";
return $this->query($sql)->fetchAll('id');
}
public function getTop($limit, $order = 'sales', $start_date = null, $end_date = null)
{
$paid_date_sql = shopOrderModel::getDateSql('o.paid_date', $start_date, $end_date);
if ($order !== 'sales') {
$order = 'profit';
}
$limit = (int) $limit;
$limit = ifempty($limit, 10);
$sql = "SELECT\n p.*,\n SUM(oi.price*o.rate*oi.quantity) AS sales,\n SUM(IF(oi.purchase_price > 0, oi.purchase_price*o.rate, ps.purchase_price*pcur.rate)*oi.quantity) AS purchase,\n SUM(oi.price*o.rate*oi.quantity - IF(oi.purchase_price > 0, oi.purchase_price*o.rate, ps.purchase_price*pcur.rate)*oi.quantity) AS profit\n FROM shop_order AS o\n JOIN shop_order_items AS oi\n ON oi.order_id=o.id\n JOIN shop_product AS p\n ON oi.product_id=p.id\n JOIN shop_product_skus AS ps\n ON oi.sku_id=ps.id\n JOIN shop_currency AS pcur\n ON pcur.code=p.currency\n WHERE {$paid_date_sql}\n AND oi.type = 'product'\n GROUP BY p.id\n ORDER BY {$order} DESC\n LIMIT {$limit}";
return $this->query($sql);
}
