Query Sales Profit

 avatar
unknown
plain_text
a month ago
1.5 kB
6
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();
Leave a Comment