CVE-2023-1361: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: SQL Injection in GitHub repository unilogies/bumsys prior to v2.0.2.
CWE description: 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.
CVSS v3 base score: 6.5
Published: 2023-03-13T05:15Z
Repository: https://github.com/unilogies/bumsys
Language: PHP
Files changed: core/ajax/ajax_data.php
Commit date: 2023-03-04 16:57:47 +0600
Commit message: Fix SQL Injection

Diff: diff --git a/core/ajax/ajax_data.php b/core/ajax/ajax_data.php
index 57b874c7..be0cce4f 100644
--- a/core/ajax/ajax_data.php
+++ b/core/ajax/ajax_data.php
@@ -4,11 +4,11 @@
 // Product Details for purchase
 if(isset($_GET['page']) and $_GET['page'] == "productDetails") {
 
     $product_id = "";
     if(isset($_GET["product_id"])) {
-        $selectProductId = easySelecta(array(
+        $selectProductId = easySelectA(array(
             "table"     => "products",
             "fields"    => "product_id",
             "where"     => array(
                 "product_id" => $_GET["product_id"],
                 " or product_code" => $_GET["product_id"],
@@ -25,15 +25,42 @@ if(isset($_GET['page']) and $_GET['page'] == "productDetails") {
         die("Sorry no product found!");
     }
 
     //$this_product = product_type($product_id);
     $select_product = 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",
+        "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"
+        ),
         "where"     => array(
-            "is_trash = 0 and product_id"   => $product_id
+            "product.is_trash = 0 and product.product_id"   => $product_id
         )
     ));
 
     // If there is no product againts given ID
     if(!$select_product) {
@@ -87,13 +114,36 @@ if(isset($_GET['page']) and $_GET['page'] == "productDetails") {
 
     } else if( $product_is("Grouped") ) {
 
         $selectGroupedProducts = easySelectA(array(
             "table"     => "bg_product_items as bg_product_items",
-            "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",
+            "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",
             "join"      => array(
-                "left join {$table_prefix}products as products on products.product_id = bg_item_product_id"
+                "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",
             ),
             "where"     => array(
                 "is_raw_materials = 0 and bg_product_id" => $product_id
             )
         ));
@@ -103,16 +153,19 @@ if(isset($_GET['page']) and $_GET['page'] == "productDetails") {
             foreach($selectGroupedProducts["data"] as $pkey => $pvalue) {
 
                 // Store the product data
                 array_push($returnData, 
                     array(
-                        "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"       => $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
                     )
                 );
 
             }
 
@@ -120,17 +173,20 @@ if(isset($_GET['page']) and $_GET['page'] == "productDetails") {
         
     } else {
 
         // For normal product
         array_push($returnData, array(
-            "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
+            "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
         ));
 
     }
 
     echo json_encode($returnData);
@@ -141,11 +197,11 @@ if(isset($_GET['page']) and $_GET['page'] == "productDetails") {
 // Product details for POS
 if(isset($_GET['page']) and $_GET['page'] == "productDetailsForPos") {
 
     $product_id = "";
     if(isset($_GET["product_id"])) {
-        $selectProductId = easySelecta(array(
+        $selectProductId = easySelectA(array(
             "table"     => "products",
             "fields"    => "product_id",
             "where"     => array(
                 "product_id" => $_GET["product_id"],
                 " or product_code" => $_GET["product_id"],
@@ -194,22 +250,32 @@ if(isset($_GET['page']) and $_GET['page'] == "productDetailsForPos") {
     $returnData = array();
 
     $select_product = easySelectA(array(
         "table"     => "products as product",
         "fields"    => "product.product_id as product_id, concat(product_name, ' ', if(product_group is null, '', left(product_group, 3))) as product_name, product_generic, 
-                        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",
+                        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",
         "join"      => array(
             "left join ( select 
                             vp_id,
                             if(batch_id is not null, 1, 0) as has_batch, /** Checking the product if set has expriy date and there also have batch entry*/
                             warehouse, 
                             sum(base_stock_in/base_qty) as stock_in 
                         FROM product_base_stock
                         where vp_id= '{$product_id}' and ( batch_id is null or date(batch_expiry_date) > curdate() ) and warehouse = '{$warehouse_id}' 
                         group by vp_id
-                    ) as stock on stock.vp_id =  product.product_id"
+                    ) 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"
         ),
         "where"     => array(
             "product.is_trash = 0 and product.product_id"   => $product_id
         )
     ));
@@ -277,12 +343,14 @@ if(isset($_GET['page']) and $_GET['page'] == "productDetailsForPos") {
         
         $subProductsStockCheck = easySelectA(array(
             "table"     => "bg_product_items",
             "fields"    => "bg_item_product_id, product_name, product_{$customerType}_discount as product_discount, 
                             bg_item_product_id, 
-                            round(sub_product.product_sale_price, 2) as product_sale_price,
-                            round(sub_product.product_purchase_price, 2) as product_purchase_price,
+
+                            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, 
+
                             sub_product.product_generic as product_generic,
                             sub_product.product_unit as product_unit,
                             sub_product.product_packet_qnt as product_packet_qnt,
                             sub_product.has_expiry_date as has_expiry_date,
                             sub_product.maintain_stock as maintain_stock,
@@ -297,11 +365,19 @@ if(isset($_GET['page']) and $_GET['page'] == "productDetailsForPos") {
                                 if(batch_id is not null, 1, 0) as has_batch, /** Checking the product if set has expriy date and there also have batch entry*/
                                 sum(base_stock_in/base_qty) as stock_in 
                         FROM product_base_stock
                         where ( batch_id is null or date(batch_expiry_date) > curdate() ) and warehouse = '{$warehouse_id}' 
                         group by vp_id
-                    ) as stock on stock.vp_id = bg_item_product_id"
+                    ) 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"
             ),
             "where"     => array(
                 "is_raw_materials = 0 and bg_product_id = {$product_id}"
             )
 
@@ -422,11 +498,11 @@ if(isset($_GET['page']) and $_GET['page'] == "productDetailsForPos") {
 // Product for return
 if(isset($_GET['page']) and $_GET['page'] == "productDetailsForReturn") {
 
     $product_id = "";
     if(isset($_GET["product_id"])) {
-        $selectProductId = easySelecta(array(
+        $selectProductId = easySelectA(array(
             "table"     => "products",
             "fields"    => "product_id",
             "where"     => array(
                 "product_id" => $_GET["product_id"],
                 " or product_code" => $_GET["product_id"],
@@ -458,11 +534,26 @@ if(isset($_GET['page']) and $_GET['page'] == "productDetailsForReturn") {
         $customerType = strtolower($selectCustomerType["data"][0]["customer_type"]);
     }
 
     $select_product = easySelectA(array(
         "table"     => "products",
-        "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",
+        "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) "
+        ),
         "where"     => array(
             "is_trash = 0 and product_id"   => $product_id
         )
     ));
 
@@ -520,11 +611,14 @@ if(isset($_GET['page']) and $_GET['page'] == "productDetailsForReturn") {
 
         // For normal product
         $normal_product = easySelectA(array(
             "table"     => "products as products",
             "fields"    => "
-                    product_id, product_name, product_{$customerType}_discount as product_discount, product_unit, has_expiry_date, round(product_sale_price, 2) as product_sale_price,
+                    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, 
+
                     if(sale_item_quantity is null, 0, round(sale_item_quantity, 2)) as purchasedQnt,
                     if(returns_products_quantity is null, 0, round(returns_products_quantity, 2)) as returnedQnt
             ",
             "join"      => array(
                 "left join (
@@ -532,13 +626,19 @@ if(isset($_GET['page']) and $_GET['page'] == "productDetailsForReturn") {
                         stock_product_id,
                         sum(case when stock_type = 'sale' then stock_item_qty end ) as sale_item_quantity,
                         sum(case when stock_type = 'sale-return' then stock_item_qty end ) as returns_products_quantity
                     from {$table_prefix}product_stock as product_stock
                     left join {$table_prefix}sales on stock_sales_id = sales_id
-                    where stock_product_id = {$product_id} and sales_customer_id = {$customer_id} and product_stock.is_trash = 0
+                    where stock_product_id = {$product_id} and sales_customer_id = '{$customer_id}' and product_stock.is_trash = 0
                     group by stock_product_id
                 ) as stock on stock_product_id = product_id",
+                "left join (SELECT
+                                product_id,
+                                sale_price
+                    FROM {$table_prefix}product_price    
+                    WHERE shop_id = '{$_SESSION['sid']}'
+                ) as product_price using(product_id) "
             ),
             "where" => array(
                 "product_id"    => $product_id
             )
         ))["data"][0];
@@ -568,11 +668,11 @@ if(isset($_GET['page']) and $_GET['page'] == "productList") {
     $productBrandFilter = ( isset($_GET["brand"]) and !empty($_GET["brand"]) ) ? $_GET["brand"] : "";
     $productGenericFilter = ( isset($_GET["generic"]) and !empty($_GET["generic"]) ) ? $_GET["generic"] : "";
     $productAuthorFilter = ( isset($_GET["author"]) and !empty($_GET["author"]) ) ? $_GET["author"] : "";
 
     // If there are any edition to filter, we do not need product_type != 'Child' filter
-    $productEditionFilter = ( isset($_GET["edition"]) and !empty($_GET["edition"]) ) ? " product_edition = '{$_GET["edition"]}' " : " product_type != 'Child' ";
+    $productEditionFilter = ( isset($_GET["edition"]) and !empty($_GET["edition"]) ) ? " product_edition = '". safe_input($_GET["edition"]) ."' " : " product_type != 'Child' ";
 
     $oderBy = array();
     
     if( !empty(get_options("defaultProductOrder")) ) {
 
@@ -995,16 +1095,16 @@ if(isset($_GET['page']) and $_GET['page'] == "getCompanyDueBillDetails") {
     echo json_encode($getEmpAdvancePaymentData);
 
 }
 
 
-// Product Comparision Details
+// Product Comparison Details
 
-if(isset($_GET['page']) and $_GET['page'] == "getProductComparision") {
+if(isset($_GET['page']) and $_GET['page'] == "getProductComparison") {
 
     $dateRange = explode(" - ", safe_input($_POST["dateRange"]));
-    $product = implode(",",$_POST["productsId"]);
+    $product =  safe_input(implode(",",$_POST["productsId"]));
 
 
     // Check if the given products are variable or not
     $selectAllProduct = easySelectA(array(
         "table"     => "products",
@@ -1198,10 +1298,12 @@ if(isset($_GET['page']) and $_GET['page'] == "getProductComparision") {
                 product_id IN($product) and db_date between '{$dateRange[0]}' and '{$dateRange[1]}'
             GROUP BY {$relationOnDate}, product_id
             ORDER BY `time_dimension`.`db_date` ASC, product_id DESC
         ")["data"];
 
+        // var_dump($sales);
+
         foreach($sales as $key => $data) {
 
             // Collect Sold qunatity
             if( !isset($chart_data[$data["product_id"]]) ) {
     
@@ -1350,18 +1452,18 @@ if(isset($_GET['page']) and $_GET['page'] == "updateInLine") {
 
     if( !isset($_GET["t"]) or !isset($_GET["f"]) or !isset($_GET["p"]) or !isset($_GET["tab"])  ) {
 
         echo '{
             "error": "true",
-            "msg": "An unknow error occured. Please contact with administrator."
+            "msg": "An unknown error occurred. Please contact with administrator."
         }';
 
     } else if( empty($_GET["t"]) or empty($_GET["f"]) or empty($_GET["p"]) or empty($_GET["tab"])  ) {
         
         echo '{
             "error": "true",
-            "msg": "An unknow error occured. Please contact with administrator.d"
+            "msg": "An unknown error occurred. Please contact with administrator.d"
         }';
 
     } else {
 
         // Update the
@@ -1710,56 +1812,123 @@ if(isset($_GET['page']) and $_GET['page'] == "getChildProductData") {
     }
 
 }
 
 
-
-
 if(isset($_GET['page']) and $_GET['page'] == "salesOverviewChartData") {
 
     $type = isset($_GET["type"]) ? $_GET["type"] : "daily";
 
-    if( $type === "weekly" ) {
+    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" ) {
 
         $weeklySalesData = easySelectD("
             SELECT
                 concat(date_format(db_date, '%D %M')) AS label,
-                if(sales_quantity is null, 0, sum(sales_quantity)) as sales_quantity
+                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_quantity) as sales_quantity,
+                    sum(sales_grand_total) as sales_grand_total
                 FROM {$table_prefix}sales 
-                WHERE is_trash = 0
+                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 30 WEEK AND NOW()
             group by week(db_date)
+            order by year(db_date), week(db_date) ASC
 		");
 
         $weeklySalesOverviewLabel = array();
-		$weeklySalesOverviewData = array();
+		$weeklySalesOverviewQtyData = array();
+		$weeklySalesOverviewAmountData = array();
         
         if( $weeklySalesData !== false ) {
 
             foreach($weeklySalesData["data"] as $sales ) {
                 array_push($weeklySalesOverviewLabel, $sales["label"] );
-                array_push($weeklySalesOverviewData, $sales["sales_quantity"] );
+                array_push($weeklySalesOverviewQtyData, $sales["sales_quantity"] );
+                array_push($weeklySalesOverviewAmountData, $sales["sales_grand_total"] );
             }
 
         }
 
 
         $weeklySalesData = array(
             "labels" => $weeklySalesOverviewLabel,
             "datasets" => array(
                 array(
-                    "label" => __("Weekly Sales"),
+                    "label" => __("Weekly Sold Qty"),
+                    "borderColor" => "orange",
+                    "borderWidth"   => 2,
+                    "data"  => $weeklySalesOverviewQtyData
+                ),
+                array(
+                    "label" => __("Weekly Sold Amount"),
                     "borderColor" => "green",
                     "borderWidth"   => 2,
-                    "data"  => $weeklySalesOverviewData
+                    "data"  => $weeklySalesOverviewAmountData
                 )
             )
         );
 
         echo json_encode($weeklySalesData);
@@ -1771,51 +1940,392 @@ if(isset($_GET['page']) and $_GET['page'] == "salesOverviewChartData") {
         /** Daily Sales Calculatin */
 
         $dailySalesData = easySelectD("
             SELECT
                 db_date AS label,
-                if(sales_quantity is null, 0, sales_quantity) as sales_quantity
+                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
             FROM time_dimension
             LEFT JOIN (
                 SELECT 
                     sales_delivery_date, 
-                    sum(sales_quantity) as sales_quantity 
+                    sum(sales_quantity) as sales_quantity,
+                    sum(sales_grand_total) as sales_grand_total
                 FROM {$table_prefix}sales 
-                WHERE is_trash = 0
+                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 30 DAY AND NOW()
 		");
 
         $dailySalesOverviewLabel = array();
-		$dailySalesOverviewData = array();
+		$dailySalesOverviewQtyData = array();
+		$dailySalesOverviewAmountData = array();
         
         if( $dailySalesData !== false ) {
 
             foreach($dailySalesData["data"] as $sales ) {
                 array_push($dailySalesOverviewLabel, $sales["label"] );
-                array_push($dailySalesOverviewData, $sales["sales_quantity"] );
+                array_push($dailySalesOverviewQtyData, $sales["sales_quantity"] );
+                array_push($dailySalesOverviewAmountData, $sales["sales_grand_total"] );
             }
 
         }
 
 
         $dailySalesData = array(
             "labels" => $dailySalesOverviewLabel,
             "datasets" => array(
                 array(
-                    "label" => __("Daily Sales"),
+                    "label" => __("Daily Sold Qty"),
+                    "borderColor" => "orange",
+                    "borderWidth"   => 2,
+                    "data"  => $dailySalesOverviewQtyData
+                ),
+                array(
+                    "label" => __("Daily Sold Amount"),
                     "borderColor" => "green",
                     "borderWidth"   => 2,
-                    "data"  => $dailySalesOverviewData
+                    "data"  => $dailySalesOverviewAmountData
                 )
             )
         );
 
         echo json_encode($dailySalesData);
 
     }
 
 
+}
+
+
+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);
+
+
 }
 
 ?>
\ No newline at end of file

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