Untitled

SELECT
(
-- Sales Product Subtotal
COALESCE(SUM(tbl_sales_product.subtotal), 0)
+
-- Repair Product Subtotal
COALESCE(SUM(tbl_repair_product.subtotal), 0)
-
-- Returns Product Subtotal
COALESCE(SUM(tbl_returns_product.subtotal), 0)
)
-
(
-- Adjusted Sales Product Calculation
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)
+
-- Adjusted Repair Product Calculation
COALESCE(SUM(
CASE
WHEN tbl_repair_product.total_tax_val = 0 THEN 0
WHEN tbl_repair_product.second_hand = 1 THEN
(tbl_repair_product.subtotal - tbl_repair_product.cost_price)
- ((tbl_repair_product.subtotal - tbl_repair_product.cost_price) / 1.23)
ELSE
tbl_repair_product.subtotal - (tbl_repair_product.subtotal / 1.23)
END
), 0)
-
-- Adjusted Returns Product Calculation
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_total
FROM
isellpt_isystem_new.tbl_sales_product AS tbl_sales_product
LEFT JOIN isellpt_isystem_new.tbl_repair_product AS tbl_repair_product ON 1 = 1
LEFT JOIN isellpt_isystem_new.tbl_returns_product AS tbl_returns_product ON 1 = 1