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); }