CVE-2023-1361 (PHP, published 2023-03-13T05:15Z)
Description: value: SQL Injection in GitHub repository unilogies/bumsys prior to v2.0.2.
Severity: nan (CVSS v3 base score: 6.5)
Weakness: CWE-89 \u2014 Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')
The product constructs all or part of an SQL command using externally-influenced input from an upstream component, but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command when it is sent to a downstream component. Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data.
Repository: https://github.com/unilogies/bumsys
Files changed:
core/ajax/ajax_data.php
Commit message: Fix SQL Injection


Vulnerable code:
        $selectProductId = easySelecta(array(
        "table"     => "products",
        "fields"    => "product_id, concat(product_name, ' ', if(product_group is null, '', left(product_group, 3))) as product_name, product_type, 0 as product_discount, 
                        round(product_sale_price, 4) as product_sale_price, round(product_purchase_price, 4) as product_purchase_price, product_unit, has_expiry_date",
            "is_trash = 0 and product_id"   => $product_id
            "fields"    => "bg_item_product_id, product_name, round(product_purchase_price, 2) as product_purchase_price, product_unit, bg_product_qnt, 0 as product_discount",
                "left join {$table_prefix}products as products on products.product_id = bg_item_product_id"
                        "pid"  => $pvalue["bg_item_product_id"], // pid = Product id
                        "pn"   => $pvalue["product_name"], // pn = product Name
                        "pd"   => $pvalue["product_discount"], // pd = product Discount 
                        "iq"   => $pvalue["bg_product_qnt"], // iq = Item Qunatity
                        "pu"   => $pvalue["product_unit"], // iq = Item Qunatity
                        "pp"   => $pvalue["product_purchase_price"], // sp = purchase price
            "pid"  => $product["product_id"], // pid = Product id
            "pn"   => $product["product_name"], // pn = product Name
            "pd"   => $product["product_discount"], // pd = product Discount
            "sp"   => $product["product_sale_price"], // sp = sale price
            "pp"   => $product["product_purchase_price"], // sp = purchase price
            "pu"   => $product["product_unit"], // pu = Product Unit
            "hed"  => $product["has_expiry_date"] // hed = Has Expiry Date
        $selectProductId = easySelecta(array(
                        if(stock_in is null, 0, round(stock_in, 2) ) as stock_in, product_type, product_{$customerType}_discount as product_discount, round(product_sale_price, 2) as product_sale_price, 
                        round(product_purchase_price, 2) as product_purchase_price, product_packet_qnt, product_unit, maintain_stock, has_expiry_date, has_batch",
                    ) as stock on stock.vp_id =  product.product_id"
                            round(sub_product.product_sale_price, 2) as product_sale_price,
                            round(sub_product.product_purchase_price, 2) as product_purchase_price,
                    ) as stock on stock.vp_id = bg_item_product_id"
        $selectProductId = easySelecta(array(
        "fields"    => "product_id, product_name, product_type, product_{$customerType}_discount as product_discount, round(product_sale_price, 2) as product_sale_price, round(product_purchase_price, 2) as product_purchase_price, product_unit, has_expiry_date",
                    product_id, product_name, product_{$customerType}_discount as product_discount, product_unit, has_expiry_date, round(product_sale_price, 2) as product_sale_price,
                    where stock_product_id = {$product_id} and sales_customer_id = {$customer_id} and product_stock.is_trash = 0
    $productEditionFilter = ( isset($_GET["edition"]) and !empty($_GET["edition"]) ) ? " product_edition = '{$_GET["edition"]}' " : " product_type != 'Child' ";
// Product Comparision Details
if(isset($_GET['page']) and $_GET['page'] == "getProductComparision") {
    $product = implode(",",$_POST["productsId"]);
            "msg": "An unknow error occured. Please contact with administrator."
            "msg": "An unknow error occured. Please contact with administrator.d"


    if( $type === "weekly" ) {
                if(sales_quantity is null, 0, sum(sales_quantity)) as sales_quantity
                    sum(sales_quantity) as sales_quantity 
                WHERE is_trash = 0
		$weeklySalesOverviewData = array();
                array_push($weeklySalesOverviewData, $sales["sales_quantity"] );
                    "label" => __("Weekly Sales"),
                    "data"  => $weeklySalesOverviewData
                if(sales_quantity is null, 0, sales_quantity) as sales_quantity
                    sum(sales_quantity) as sales_quantity 
                WHERE is_trash = 0
		$dailySalesOverviewData = array();
                array_push($dailySalesOverviewData, $sales["sales_quantity"] );
                    "label" => __("Daily Sales"),
                    "data"  => $dailySalesOverviewData

Fixed code:
        $selectProductId = easySelectA(array(
        "table"     => "products as product",
        "fields"    => "product.product_id, concat(product_name, ' ', if(product_group is null, '', left(product_group, 3))) as product_name, product_type, 0 as product_discount, 
                        round( COALESCE(purchase_price, product_purchase_price), 2) as product_purchase_price, 
                        round( COALESCE(sale_price, product_sale_price), 2) as product_sale_price, 
                        product_unit, has_expiry_date, if(stock_in is null, 0, round(stock_in, 2)) as stock_in,
                        if(total_sold is null, 0, round(total_sold, 2)) as total_sold_count, round(product_alert_qnt, 2) as product_alert_qnt",
        "join"      => array(
            "left join (select
                            vp_id,
                            sum(base_stock_in/base_qty) as stock_in
                        from product_base_stock
                        where warehouse = '{$_SESSION['wid']}'
                        group by vp_id
            ) as pbs on pbs.vp_id = product.product_id",
            "left join (
                SELECT
                    stock_product_id,
                    sum(stock_item_qty) as total_sold
                FROM {$table_prefix}product_stock
                WHERE is_trash = 0 and stock_type = 'sale' and stock_warehouse_id = '{$_SESSION['wid']}'
            ) as sold on sold.stock_product_id = product.product_id",
            // Because Of we have different price based on shop
            "left join (SELECT
                            product_id,
                            purchase_price,
                            sale_price
                FROM {$table_prefix}product_price    
                WHERE shop_id = '{$_SESSION['sid']}'
            ) as product_price on product_price.product_id = product.product_id"
        ),
            "product.is_trash = 0 and product.product_id"   => $product_id
            "fields"    => "bg_item_product_id, concat(product_name, ' ', if(product_group is null, '', left(product_group, 3))) as product_name, product_type,
                            round( COALESCE(purchase_price, product_purchase_price)  , 2) as product_purchase_price, 
                            product_unit, bg_product_qnt, 0 as product_discount, if(stock_in is null, 0, round(stock_in, 2)) as stock_in,
                            if(total_sold is null, 0, round(total_sold, 2)) as total_sold_count, round(product_alert_qnt, 2) as product_alert_qnt",
                "left join {$table_prefix}products as products on products.product_id = bg_item_product_id",
                "left join (SELECT
                                product_id,
                                purchase_price
                    FROM {$table_prefix}product_price 
                    WHERE shop_id = '{$_SESSION['sid']}'
                ) as product_price on product_price.product_id = bg_item_product_id",
                "left join (
                    SELECT
                        stock_product_id,
                        sum(stock_item_qty) as total_sold
                    FROM {$table_prefix}product_stock
                    WHERE is_trash = 0 and stock_type = 'sale' and stock_warehouse_id = '{$_SESSION['wid']}'
                ) as sold on sold.stock_product_id = bg_item_product_id",
                "left join (select
                                vp_id,
                                sum(base_stock_in/base_qty) as stock_in
                            from product_base_stock
                            where warehouse = '{$_SESSION['wid']}'
                            group by vp_id
                ) as pbs on pbs.vp_id = bg_item_product_id",
                        "pid"       => $pvalue["bg_item_product_id"], // pid = Product id
                        "pn"        => $pvalue["product_name"], // pn = product Name
                        "pd"        => $pvalue["product_discount"], // pd = product Discount 
                        "iq"        => $pvalue["bg_product_qnt"], // iq = Item Quantity
                        "pu"        => $pvalue["product_unit"], // iq = Item Quantity
                        "alertq"    => $product["product_alert_qnt"], // aq = alert quantity
                        "soldq"     => $product["total_sold_count"], // soldq = stock quantity
                        "stockq"    => $product["stock_in"], // stockq = stock quantity
                        "pp"        => $pvalue["product_purchase_price"], // sp = purchase price
            "pid"       => $product["product_id"], // pid = Product id
            "pn"        => $product["product_name"], // pn = product Name
            "pd"        => $product["product_discount"], // pd = product Discount
            "sp"        => $product["product_sale_price"], // sp = sale price
            "pp"        => $product["product_purchase_price"], // sp = purchase price
            "pu"        => $product["product_unit"], // pu = Product Unit
            "alertq"    => $product["product_alert_qnt"], // aq = alert quantity
            "soldq"     => $product["total_sold_count"], // soldq = stock quantity
            "stockq"    => $product["stock_in"], // stockq = stock quantity
            "hed"       => $product["has_expiry_date"] // hed = Has Expiry Date
        $selectProductId = easySelectA(array(
                        if(stock_in is null, 0, round(stock_in, 2) ) as stock_in, product_type, product_{$customerType}_discount as product_discount, 
                        round( COALESCE(product_price.purchase_price, product_purchase_price), 2) as product_purchase_price, 
                        round( COALESCE(product_price.sale_price, product_sale_price), 2) as product_sale_price, 
                        product_packet_qnt, product_unit, maintain_stock, has_expiry_date, has_batch",
                    ) as stock on stock.vp_id =  product.product_id",
            // Because Of we have different price based on shop
            "left join (SELECT
                            product_id,
                            purchase_price,
                            sale_price
                FROM {$table_prefix}product_price    
                WHERE shop_id = '{$_SESSION['sid']}'
            ) as product_price on product_price.product_id = product.product_id"

                            round( COALESCE(product_price.purchase_price, sub_product.product_purchase_price), 2) as product_purchase_price, 
                            round( COALESCE(product_price.sale_price, sub_product.product_sale_price), 2) as product_sale_price, 

                    ) as stock on stock.vp_id = bg_item_product_id",
                // Because Of we have different price based on shop
                "left join (SELECT
                                product_id,
                                purchase_price,
                                sale_price
                    FROM {$table_prefix}product_price    
                    WHERE shop_id = '{$_SESSION['sid']}'
                ) as product_price on product_price.product_id = sub_product.product_id"
        $selectProductId = easySelectA(array(
        "fields"    => "product_id, product_name, product_type, product_{$customerType}_discount as product_discount, 

                        round( COALESCE(purchase_price, product_purchase_price), 2) as product_purchase_price, 
                        round( COALESCE(sale_price, product_sale_price), 2) as product_sale_price, 
                        
                        product_unit, has_expiry_date",
        "join"      => array(
            // Because Of we have different price based on shop
            "left join (SELECT
                            product_id,
                            purchase_price,
                            sale_price
                FROM {$table_prefix}product_price    
                WHERE shop_id = '{$_SESSION['sid']}'
            ) as product_price using(product_id) "
        ),
                    product_id, product_name, product_{$customerType}_discount as product_discount, product_unit, has_expiry_date, 

                    round( COALESCE(sale_price, product_sale_price), 2) as product_sale_price, 

                    where stock_product_id = {$product_id} and sales_customer_id = '{$customer_id}' and product_stock.is_trash = 0
                "left join (SELECT
                                product_id,
                                sale_price
                    FROM {$table_prefix}product_price    
                    WHERE shop_id = '{$_SESSION['sid']}'
                ) as product_price using(product_id) "
    $productEditionFilter = ( isset($_GET["edition"]) and !empty($_GET["edition"]) ) ? " product_edition = '". safe_input($_GET["edition"]) ."' " : " product_type != 'Child' ";
// Product Comparison Details
if(isset($_GET['page']) and $_GET['page'] == "getProductComparison") {
    $product =  safe_input(implode(",",$_POST["productsId"]));
        // var_dump($sales);

            "msg": "An unknown error occurred. Please contact with administrator."
            "msg": "An unknown error occurred. Please contact with administrator.d"
    if( $type === "monthly" ) {

        $monthlySalesData = easySelectD("
            SELECT
                concat(date_format(db_date, '%M %Y')) AS label,
                sum(if(sales_quantity is null, 0, sales_quantity)) as sales_quantity,
                sum(if(sales_grand_total is null, 0, sales_grand_total)) as sales_grand_total
            FROM time_dimension
            LEFT JOIN (
                SELECT 
                    sales_delivery_date, 
                    sum(sales_quantity) as sales_quantity,
                    sum(sales_grand_total) as sales_grand_total
                FROM {$table_prefix}sales 
                WHERE is_trash = 0 and is_return = 0
                GROUP BY sales_delivery_date
            ) AS sales on sales_delivery_date = db_date
            WHERE db_date BETWEEN NOW() - INTERVAL 20 MONTH AND NOW()
            group by year(db_date), month(db_date)
            order by year(db_date), month(db_date) ASC
		");

        $monthlySalesOverviewLabel = array();
		$monthlySalesOverviewQtyData = array();
		$monthlySalesOverviewAmountData = array();
        
        if( $monthlySalesData !== false ) {

            foreach($monthlySalesData["data"] as $sales ) {
                array_push($monthlySalesOverviewLabel, $sales["label"] );
                array_push($monthlySalesOverviewQtyData, $sales["sales_quantity"] );
                array_push($monthlySalesOverviewAmountData, $sales["sales_grand_total"] );
            }

        }


        $monthlySalesData = array(
            "labels" => $monthlySalesOverviewLabel,
            "datasets" => array(
                array(
                    "label" => __("Monthly Sold Qty"),
                    "borderColor" => "orange",
                    "borderWidth"   => 2,
                    "data"  => $monthlySalesOverviewQtyData
                ),
                array(
                    "label" => __("Monthly Sold Amount"),
                    "borderColor" => "green",
                    "borderWidth"   => 2,
                    "data"  => $monthlySalesOverviewAmountData
                )
            )
        );

        echo json_encode($monthlySalesData);


    } else if( $type === "weekly" ) {
                sum(if(sales_quantity is null, 0, sales_quantity)) as sales_quantity,
                sum(if(sales_grand_total is null, 0, sales_grand_total)) as sales_grand_total
                    sum(sales_quantity) as sales_quantity,
                    sum(sales_grand_total) as sales_grand_total
                WHERE is_trash = 0 and is_return = 0
            order by year(db_date), week(db_date) ASC
		$weeklySalesOverviewQtyData = array();
		$weeklySalesOverviewAmountData = array();
                array_push($weeklySalesOverviewQtyData, $sales["sales_quantity"] );
                array_push($weeklySalesOverviewAmountData, $sales["sales_grand_total"] );
                    "label" => __("Weekly Sold Qty"),
                    "borderColor" => "orange",
                    "borderWidth"   => 2,
                    "data"  => $weeklySalesOverviewQtyData
                ),
                array(
                    "label" => __("Weekly Sold Amount"),
                    "data"  => $weeklySalesOverviewAmountData
                if(sales_quantity is null, 0, sales_quantity) as sales_quantity,
                if(sales_grand_total is null, 0, sales_grand_total) as sales_grand_total
                    sum(sales_quantity) as sales_quantity,
                    sum(sales_grand_total) as sales_grand_total
                WHERE is_trash = 0 and is_return = 0
		$dailySalesOverviewQtyData = array();
		$dailySalesOverviewAmountData = array();
                array_push($dailySalesOverviewQtyData, $sales["sales_quantity"] );
                array_push($dailySalesOverviewAmountData, $sales["sales_grand_total"] );
                    "label" => __("Daily Sold Qty"),
                    "borderColor" => "orange",
                    "borderWidth"   => 2,
                    "data"  => $dailySalesOverviewQtyData
                ),
                array(
                    "label" => __("Daily Sold Amount"),
                    "data"  => $dailySalesOverviewAmountData
}


if(isset($_GET['page']) and $_GET['page'] == "customerPurchaseIncreasedList") {

    /**
     * See here to know how to caculat parcentage
     * https://www.bbc.co.uk/bitesize/guides/zpjmjty/revision/2
     * 
     * 
     * percentage increase = increased number ÷ original number × 100
     * percentage decrease = decreased number ÷ original number × 100
     * 
     */

    $type = isset($_GET["type"]) ? $_GET["type"] : "daily";

    if( $type === "fullYear" ) {

        $getPurchaseIncreasedCustomer = easySelectA(array(
            "table"     => "customers as customer",
            "fields"    => "customer_id, customer_name, upazila_name, district_name,
                            round(previous_year_total_sales, 2) as previous_year_total_purchase, 
                            round(coalesce(current_year_total_sales, 0), 2) as current_year_total_purchase,
                            round( ( coalesce(current_year_total_sales, 0) - coalesce(previous_year_total_sales, 0) ) / previous_year_total_sales * 100, 2) as increased_rate
                            ",
            "join"      => array(
                "left join {$table_prefix}upazilas on customer_upazila = upazila_id",
                "left join {$table_prefix}districts on customer_district = district_id",
                "left join (
                    select
                        sales_customer_id,
                        sum( CASE WHEN YEAR(sales_delivery_date) = YEAR( CURDATE() - INTERVAL 1 YEAR ) THEN sales_grand_total END ) as previous_year_total_sales,
                        sum( CASE WHEN YEAR(sales_delivery_date) = YEAR( CURDATE() ) THEN sales_grand_total END ) as current_year_total_sales
                    from ro_sales
                    where is_trash = 0 and is_return = 0
                    group by sales_customer_id
                ) as customer_sale on sales_customer_id = customer_id"
            ),
            "where" => array(
                "customer.is_trash = 0 and previous_year_total_sales is not null and coalesce(previous_year_total_sales, 0) < current_year_total_sales and current_year_total_sales > 100000"
            ),
            "orderby"   => array(
                "increased_rate"    => "DESC"
            ),
            "limit" => array(
                "start"     => 0,
                "length"    => 15
            )
        ));

    } else {

        $getPurchaseIncreasedCustomer = easySelectA(array(
            "table"     => "customers as customer",
            "fields"    => "customer_id, customer_name, upazila_name, district_name,
                            round(previous_year_total_sales, 2) as previous_year_total_purchase, 
                            round(coalesce(current_year_total_sales, 0), 2) as current_year_total_purchase,
                            round( ( coalesce(current_year_total_sales, 0) - coalesce(previous_year_total_sales, 0) ) / previous_year_total_sales * 100, 2) as increased_rate
                            ",
            "join"      => array(
                "left join {$table_prefix}upazilas on customer_upazila = upazila_id",
                "left join {$table_prefix}districts on customer_district = district_id",
                "left join (
                    select
                        sales_customer_id,
                        sum( CASE WHEN sales_delivery_date between CONCAT( YEAR(CURDATE() - INTERVAL 1 YEAR),'-',01,'-',01) AND CURDATE() - INTERVAL 1 YEAR THEN sales_grand_total END ) as previous_year_total_sales,
                        sum( CASE WHEN YEAR(sales_delivery_date) = YEAR( CURDATE() ) THEN sales_grand_total END ) as current_year_total_sales
                    from ro_sales
                    where is_trash = 0 and is_return = 0
                    group by sales_customer_id
                ) as customer_sale on sales_customer_id = customer_id"
            ),
            "where" => array(
                "customer.is_trash = 0 and previous_year_total_sales is not null and coalesce(previous_year_total_sales, 0) < current_year_total_sales and current_year_total_sales > 100000"
            ),
            "orderby"   => array(
                "increased_rate"    => "DESC"
            ),
            "limit" => array(
                "start"     => 0,
                "length"    => 15
            )
        ));

    }
    

    if($getPurchaseIncreasedCustomer !== false) {

        echo json_encode( $getPurchaseIncreasedCustomer["data"] );

    }


}


if(isset($_GET['page']) and $_GET['page'] == "customerPurchaseDecreasedList") {


    /**
     * See here to know how to caculat parcentage
     * https://www.bbc.co.uk/bitesize/guides/zpjmjty/revision/2
     * 
     * 
     * percentage increase = increased number ÷ original number × 100
     * percentage decrease = decreased number ÷ original number × 100
     * 
     */

    $type = isset($_GET["type"]) ? $_GET["type"] : "daily";

    if( $type === "fullYear" ) {

        $getPurchaseDecreasedCustomer = easySelectA(array(
            "table"     => "customers as customer",
            "fields"    => "customer_id, customer_name, upazila_name, district_name,
                            round(previous_year_total_sales, 2) as previous_year_total_purchase, 
                            round(coalesce(current_year_total_sales, 0), 2) as current_year_total_purchase,
                            round( ( coalesce(previous_year_total_sales, 0) - coalesce(current_year_total_sales, 0) ) / previous_year_total_sales * 100, 2) as decreased_rate
                            ",
            "join"      => array(
                "left join {$table_prefix}upazilas on customer_upazila = upazila_id",
                "left join {$table_prefix}districts on customer_district = district_id",
                "left join (
                    select
                        sales_customer_id,
                        sum( CASE WHEN YEAR(sales_delivery_date) = YEAR( CURDATE() - INTERVAL 1 YEAR ) THEN sales_grand_total END ) as previous_year_total_sales,
                        sum( CASE WHEN YEAR(sales_delivery_date) = YEAR( CURDATE() ) THEN sales_grand_total END ) as current_year_total_sales
                    from ro_sales
                    where is_trash = 0 and is_return = 0
                    group by sales_customer_id
                ) as customer_sale on sales_customer_id = customer_id"
            ),
            "where" => array(
                "customer.is_trash = 0 and previous_year_total_sales is not null and previous_year_total_sales > coalesce(current_year_total_sales, 0) and previous_year_total_sales > 100000"
            ),
            "orderby"   => array(
                "decreased_rate"    => "DESC",
                "previous_year_total_sales"    => "DESC"
            ),
            "limit" => array(
                "start"     => 0,
                "length"    => 15
            )
        
        ));
    
    } else {
        
        $getPurchaseDecreasedCustomer = easySelectA(array(
            "table"     => "customers as customer",
            "fields"    => "customer_id, customer_name, upazila_name, district_name,
                            round(previous_year_total_sales, 2) as previous_year_total_purchase, 
                            round(coalesce(current_year_total_sales, 0), 2) as current_year_total_purchase,
                            round( ( coalesce(previous_year_total_sales, 0) - coalesce(current_year_total_sales, 0) ) / previous_year_total_sales * 100, 2) as decreased_rate
                            ",
            "join"      => array(
                "left join {$table_prefix}upazilas on customer_upazila = upazila_id",
                "left join {$table_prefix}districts on customer_district = district_id",
                "left join (
                    select
                        sales_customer_id,
                        sum( CASE WHEN sales_delivery_date between CONCAT( YEAR(CURDATE() - INTERVAL 1 YEAR),'-',01,'-',01) AND CURDATE() - INTERVAL 1 YEAR THEN sales_grand_total END ) as previous_year_total_sales,
                        sum( CASE WHEN YEAR(sales_delivery_date) = YEAR( CURDATE() ) THEN sales_grand_total END ) as current_year_total_sales
                    from ro_sales
                    where is_trash = 0 and is_return = 0
                    group by sales_customer_id
                ) as customer_sale on sales_customer_id = customer_id"
            ),
            "where" => array(
                "customer.is_trash = 0 and previous_year_total_sales is not null and previous_year_total_sales > coalesce(current_year_total_sales, 0) and previous_year_total_sales > 100000"
            ),
            "orderby"   => array(
                "decreased_rate"    => "DESC",
                "previous_year_total_sales"    => "DESC"
            ),
            "limit" => array(
                "start"     => 0,
                "length"    => 15
            )
        
        ));

    }


    if($getPurchaseDecreasedCustomer !== false) {

        echo json_encode( $getPurchaseDecreasedCustomer["data"] );

    }


}



if(isset($_GET['page']) and $_GET['page'] == "searchContact") {

    $search = safe_input($_GET['s']);

    $getContact = easySelectD("

        SELECT
            name,
            number,
            type
        FROM
        (
            SELECT 
                customer_name as name,
                customer_phone as number,
                'Customer' as type
            FROM {$table_prefix}customers
            WHERE is_trash = 0
            UNION ALL
            SELECT 
                person_full_name as name,
                person_phone as number,
                'Leads' as type
            FROM {$table_prefix}persons
            WHERE is_trash = 0
        ) as contactData 
        WHERE name LIKE '%{$search}%' or number like '{$search}%'
        limit 0,10
    ");

    if($getContact !== false) {
        echo json_encode($getContact["data"]);
    } else {
        echo "";
    }

}


if(isset($_GET['page']) and $_GET['page'] == "productListByBrand") {


    $filter_reorder_qty = "";
    if( isset( $_GET["frq"] ) and $_GET["frq"] === "Yes" ) {
        $filter_reorder_qty = " AND stock_in <= product_alert_qnt";
    }

    $products = easySelectA(array(
        "table"     => "products as product",
        "fields"    => "product.product_id as pid, concat(product_name, ' ', if(product_group is null, '', left(product_group, 3))) as pn, product_type, 0 as product_discount, 
                        round( COALESCE(purchase_price, product_purchase_price), 2) as pp, 
                        round( COALESCE(sale_price, product_sale_price), 2) as product_sale_price, 
                        product_unit as pu, has_expiry_date, if(stock_in is null, 0, round(stock_in, 2)) as stockq,
                        if(total_sold is null, 0, round(total_sold, 2)) as soldq, round(product_alert_qnt, 2) as alertq
                        ",
        "join"      => array(
            "left join (
                SELECT
                    stock_product_id,
                    sum(stock_item_qty) as total_sold
                FROM {$table_prefix}product_stock
                WHERE is_trash = 0 and stock_type = 'sale'
                group by stock_product_id
            ) as sold on sold.stock_product_id = product.product_id",
            "left join (select
                            vp_id,
                            sum(base_stock_in/base_qty) as stock_in
                        from product_base_stock
                        where warehouse = '{$_SESSION['wid']}'
                        group by vp_id
            ) as pbs on pbs.vp_id = product.product_id",
            // Because Of we have different price based on shop
            "left join (SELECT
                            product_id,
                            purchase_price,
                            sale_price
                FROM {$table_prefix}product_price    
                WHERE shop_id = '{$_SESSION['sid']}'
            ) as product_price on product_price.product_id = product.product_id"
        ),
        "where"     => array(
            "product.is_trash = 0 {$filter_reorder_qty}",
            " AND product.product_brand_id" => $_GET["brand_id"],
        ),
        "groupby"   => "product.product_id"
    ));

    if($products !== false) {
        echo json_encode( $products["data"] );
    } else {
        echo "";
    }

}


if(isset($_GET['page']) and $_GET['page'] == "getIncomeReportData") {


    $dateFilter = empty($_POST["incomeReportDate"]) ? "1970-01-01 - ".date("Y")."-12-31" : safe_input($_POST["incomeReportDate"]);
    $dateRange = explode(" - ", $dateFilter);

    if( !empty($_POST["incomeReportDate"]) ) {
        
    }
    

    $incomeData = easySelectA(array(
        "table"     => "product_stock",
        "fields"    => "sum(case when stock_type = 'sale' then stock_item_subtotal else 0 end) as total_sale,
                        sum(case when stock_type = 'purchase' then stock_item_subtotal else 0 end) as total_purchase
                        ",
        "where"     => array(
            "is_trash = 0 AND stock_entry_date BETWEEN '{$dateRange[0]}' AND '{$dateRange[1]}'",
            " AND stock_shop_id"    => $_POST["incomeReportShop"]
        )
    ));


    $openingStock = easySelectA(array(
        "table"     => "purchase",
        "fields"    => "sum(CASE WHEN purchase_date > '{$dateRange[0]}' THEN purchase_grand_total ELSE 0 END ) AS opening_stock,
                        sum(CASE WHEN purchase_date > '{$dateRange[0]}' THEN purchase_grand_total ELSE 0 END ) AS opening_stock,
        ",
        "where"     => array(
            "is_trash = 0"
        )
    ));

    print_r($incomeData);