Query Sales Profit
unknown
plain_text
10 months ago
1.5 kB
10
Indexable
$this->db->query("
SELECT (
COALESCE(SUM(tbl_sales_product.subtotal), 0)
- COALESCE(SUM(tbl_sales_product.cost_price * tbl_sales_product.quantity), 0)
)
-
(
COALESCE(SUM(tbl_returns_product.subtotal), 0)
+ COALESCE(SUM(tbl_returns_product.cost_price * tbl_returns_product.quantity), 0)
)
-
(
COALESCE(SUM(
CASE
WHEN tbl_sales_product.total_tax_val = 0 THEN 0
WHEN tbl_sales_product.second_hand = 1 THEN
(tbl_sales_product.subtotal - tbl_sales_product.cost_price)
- ((tbl_sales_product.subtotal - tbl_sales_product.cost_price) / 1.23)
ELSE
tbl_sales_product.subtotal - (tbl_sales_product.subtotal / 1.23)
END
), 0)
)
+
(
COALESCE(SUM(
CASE
WHEN tbl_returns_product.total_tax_val = 0 THEN 0
WHEN tbl_returns_product.second_hand = 1 THEN
(tbl_returns_product.subtotal - tbl_returns_product.cost_price)
- ((tbl_returns_product.subtotal - tbl_returns_product.cost_price) / 1.23)
ELSE
tbl_returns_product.subtotal - (tbl_returns_product.subtotal / 1.23)
END
), 0)
) AS final_profit
FROM isellpt_isystem_new.tbl_sales_product AS tbl_sales_product
LEFT JOIN isellpt_isystem_new.tbl_returns_product AS tbl_returns_product ON 1 = 1
")->row();
Editor is loading...
Leave a Comment