<?php

namespace App\Model;

use App\Model\BaseModel;
use App\Core\Service\Logger;
use App\Config\Main;

class FxModel extends BaseModel
{
    function __construct()
    {
        parent::__construct();
    }

    public function getFromTable($table, $where, $params = [])
    {
        try {
            return $this->fetchAllData("SELECT * FROM $table WHERE $where LIMIT 1", $params);
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function getRowFromTable($table, $where, $params = [])
    {
        try {
            return $this->fetchSingleData("SELECT * FROM $table WHERE $where LIMIT 1", $params);
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getCouponDetail($comp_id, $where, $single = 0)
    {
        try {
            return $this->callProcedure($this->procedures['GetCouponDetail'], ['comp_id' => $comp_id, 'searchstr' => $where, 'single' => $single], $single ? true : false);
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getCustomerAddress($comp_id, $where, $single = 0, $sortby = '', $sorttype = '')
    {
        try {
            return $this->callProcedure($this->procedures['GetCustomerAddress'], ['comp_id' => $comp_id, 'searchstr' => $where, 'single' => $single, 'sortby' => $sortby, 'sorttype' => $sorttype], $single ? true : false);
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function getMaxInvoiceSeries($comp_id, $branch_id)
    {
        try {
            return $this->callProcedure($this->procedures['GetMaxInvoiceSeries'], ['comp_id' => $comp_id, 'branch_id' => $branch_id], true);
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getCourierDetail($comp_id, $searchstr)
    {
        try {
            Logger::error($comp_id, ['File' => __FILE__, 'Line' => __LINE__]);
            return $this->callProcedure($this->procedures['GetCourierDetail'], ['comp_id' => $comp_id, 'searchstr' => $searchstr, 'single' => 1], true);
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getOrderDetailForCourier($comp_id, $where, $single = 1)
    {
        try {
            return $this->callProcedure($this->procedures['GetOrderDetailForCourier'], ['comp_id' => $comp_id, 'searchstr' => $where, 'single' => $single], $single ? true : false);
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function stockCheck($comp_id, $item_id, $branch_id)
    {
        try {
            $sql  = "SELECT t1.* ";
            $sql .= " FROM " . $this->client_db_prefix . $comp_id . "." . $this->tables['item_stock'] . " as t1 ";
            $sql .= " WHERE item_id=$item_id AND branch_id=$branch_id";
            $rsp = $this->fetchAllData($sql);
            if (empty($rsp['status'])) {
                throw new \Exception($rsp['error']);
            }
            return ["status" => true, "exist" => count($rsp['data']) > 0 ? 1 : 0];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getOrderItemListForStock($comp_id, $where)
    {
        try {
            $sql  = "SELECT o.id as order_id,l.item_id,l.qty,o.branch_id";
            $sql .= " FROM " . $this->client_db_prefix . $comp_id . "." . $this->tables['order'] . " as o ";
            $sql .= " LEFT JOIN " . $this->client_db_prefix . $comp_id . "." . $this->tables['order_item_list'] . " as l ON(o.id=l.order_id) ";
            $sql .= " WHERE $where";
            $rsp = $this->fetchAllData($sql);
            if (empty($rsp['status'])) {
                throw new \Exception($rsp['error']);
            }
            return ["status" => true, "data" => $rsp['data']];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getOuterStockInvenory($comp_id, $where, $type)
    {
        try {
            $columns = ($type == 'qty') ? "t2.qty,t2.rto_qty" : "t2.qty as stout_qty,t2.rto_qty as qty";
            $sql  = "SELECT $columns";
            $sql .= " FROM " . $this->client_db_prefix . $comp_id . "." . $this->tables['stockout_inventory'] . " as t1 ";
            $sql .= " LEFT JOIN " . $this->client_db_prefix . $comp_id . "." . $this->tables['stockout_inventory_item'] . " as t2 ON(t1.inv_id=t2.inv_id) ";
            $sql .= " WHERE $where";
            $rsp = $this->fetchAllData($sql);
            if (empty($rsp['status'])) {
                throw new \Exception($rsp['error']);
            }
            return ["status" => true, "data" => $rsp['data']];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function stockOutUpdate($comp_id, $item_id, $branch_id, $qty, $rollback = 0, $conn = NULL)
    {
        try {
            $chk = $this->stockCheck($comp_id, $item_id, $branch_id);
            if (empty($chk['status'])) {
                throw new \Exception($chk['error']);
            }

            if ($chk['exist'] == 1) {
                $data = ["item_id" => $item_id, "branch_id" => $branch_id];
                if ($rollback == 0) {
                    $data["stock_out"] = "`stock_out`+ $qty";
                    $data["stock"] = "`stock`- $qty";
                } else {
                    $data["stock_out"] = "`stock_out`- $qty";
                    $data["stock"] = "`stock`+ $qty";
                }
                $rsp = $this->update($this->client_db_prefix . $comp_id . "." . $this->tables['item_stock'], $data, ['item_id' => $item_id, "branch_id" => $branch_id], [], $conn);
                return ["status" => true];
            } else {
                $data = array("item_id" => $item_id, "branch_id" => $branch_id, "stock_out" => $qty, "stock" => -$qty);
                $rsp = $this->insert($this->client_db_prefix . $comp_id . "." . $this->tables['item_stock'], $data, $conn);
                if (empty($rsp['status'])) {
                    throw new \Exception($rsp['error']);
                }
                return ["status" => true, "data" => $rsp['insert_id']];
            }
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function searchCustomerByMobile($comp_id, array $queryArray = [], array $queryOrArray = [], Bool $detail = false)
    {
        try {
            $condition = $this->whereString($queryArray, [], [], []);
            $orCondition = $this->whereString($queryOrArray, [], [], []);
            $orCondition = str_replace(" AND ", " OR ", $orCondition);
            if ($condition == "1=1") $condition = "";
            if ($orCondition == "1=1") $orCondition = "";

            $sql  = "SELECT c.*, ";
            if ($detail) {
                $sql .= "c.id as customer_id,a.id as address_id,a.address_title,a.address_pincode,a.address_state_id,a.address_city_id,a.address_mobile,a.address,a.gst,s.state_name as address_state_name,ci.city_name as address_city_name";
            }
            $db_name = $this->client_db_prefix . $comp_id;
            $sql .= " FROM $db_name." . $this->tables['customer_master'] . " as c ";
            if ($detail) {
                $sql .= " LEFT JOIN $db_name." . $this->tables['customer_address_master'] . " as a ON(a.customer_id=c.id) ";
                $sql .= " LEFT JOIN " . $this->tables['admin_state_master'] . " as s ON(s.state_id=a.address_state_id) ";
                $sql .= " LEFT JOIN $db_name." . $this->tables['location_city'] . " as ci ON(ci.id=a.address_city_id) ";
            }
            if (!empty($condition)) {
                $sql .= " WHERE $condition ";
                if (!empty($orCondition)) {
                    $sql .= " AND ($orCondition) ";
                }
            } else if (!empty($orCondition)) {
                $sql .= " WHERE $orCondition ";
            }
            $sql .= " ORDER BY c.id DESC LIMIT 1";
            $rsp = $this->fetchAllData($sql);
            if (empty($rsp['status'])) {
                throw new \Exception($rsp['error']);
            }
            return ["status" => true, "data" => $rsp['data']];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function addOrderLog($comp_id, $data, $conn = NULL)
    {
        try {
            $rsp = $this->insert($this->client_db_prefix . $comp_id . "." . $this->tables['order_logs'], $data, $conn);
            if (empty($rsp['status'])) {
                throw new \Exception($rsp['error']);
            }
            return ["status" => true, "data" => $rsp['insert_id']];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function addOrderShopifyLog($comp_id, $user_id, $order_list)
    {
        try {
            $conn = $this->conn();
            $conn->beginTransaction();
            foreach ($order_list as $order) {
                if (empty($order['shopify_log_id'])) {
                    $rsp = $this->insert($this->client_db_prefix . $comp_id . "." . $this->tables['order_shopify_process_log'], ["order_id" => $order['id'], 'cr_usr' => $user_id], $conn);
                    if (empty($rsp['status'])) {
                        if ($conn->inTransaction()) $conn->rollBack();
                        throw new \Exception($rsp['error']);
                    }
                    $ursp = $this->update($this->client_db_prefix . $comp_id . "." . $this->tables['order'], ['shopify_log_id' => $rsp['insert_id']], ['id' => $order['id']], [], $conn);
                    if (empty($ursp['status'])) {
                        if ($conn->inTransaction()) $conn->rollBack();
                        throw new \Exception($ursp['error']);
                    }
                }
            }
            if ($conn->inTransaction()) $conn->commit();
            return ["status" => true];
        } catch (\Exception $e) {
            if (!empty($conn)) {
                if ($conn->inTransaction()) $conn->rollBack();
            }
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function getBranchList($comp_id, $where, $single = 0, $sortby = '', $sorttype = '')
    {
        try {
            return $this->callProcedure($this->procedures['GetBranchDetail'], ['comp_id' => $comp_id, 'searchstr' => $where, 'single' => $single, 'sortby' => $sortby, 'sorttype' => $sorttype], $single ? true : false);
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getOrderItemsTotalQty($comp_id, $order_id)
    {
        try {
            $sql  = "SELECT SUM(t1.qty) as total_qty";
            $sql .= " FROM " . $this->client_db_prefix . $comp_id . "." . $this->tables['order_item_list'] . " as t1 ";
            $sql .= " WHERE t1.order_id=:orderid";
            $rsp = $this->fetchAllData($sql, [':orderid' => $order_id]);
            if (empty($rsp['status'])) {
                throw new \Exception($rsp['error']);
            }
            return ["status" => true, "data" => $rsp['data'][0]['total_qty'] ? $rsp['data'][0]['total_qty'] : 0];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getCouponUsedCount($comp_id, $customer_id, $coupon_code)
    {
        try {
            $sql  = "SELECT count(t1.id) as total_count";
            $sql .= " FROM " . $this->client_db_prefix . $comp_id . "." . $this->tables['coupon_used'] . " as t1 ";
            $sql .= " WHERE t1.customer_id=:customerid AND coupon_name=:couponcode";
            $rsp = $this->fetchAllData($sql, [':customerid' => $customer_id, ':couponcode' => $coupon_code]);
            if (empty($rsp['status'])) {
                throw new \Exception($rsp['error']);
            }
            return ["status" => true, "count" => $rsp['data'][0]['total_count'] ? $rsp['data'][0]['total_count'] : 0];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getOrderLogs($comp_id, $order_id)
    {
        try {
            $sql  = "SELECT SUM(t1.qty) as total_qty";
            $sql .= " FROM " . $this->client_db_prefix . $comp_id . "." . $this->tables['order_item_list'] . " as t1 ";
            $sql .= " WHERE t1.order_id=:orderid";
            $rsp = $this->fetchAllData($sql, [':orderid' => $order_id]);
            if (empty($rsp['status'])) {
                throw new \Exception($rsp['error']);
            }
            return ["status" => true, "data" => $rsp['data'][0]['total_qty'] ? $rsp['data'][0]['total_qty'] : 0];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function addOrderCourier(Int $comp_id, array $data, $conn = NULL)
    {
        try {
            $rsp = $this->insert($this->client_db_prefix . $comp_id . "." . $this->tables['order_courier'], $data, $conn);
            if (empty($rsp['status'])) {
                throw new \Exception($rsp['error']);
            }
            return ["status" => true, "data" => $rsp['insert_id']];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function updateOrderCourier(Int $comp_id, array $data, array $where, $conn = NULL)
    {
        try {
            $rspupd = $this->update($this->client_db_prefix . $comp_id . "." . $this->tables['order_courier'], $data, $where, [], $conn);
            if (empty($rspupd['status'])) {
                throw new \Exception($rspupd['error']);
            }
            return ["status" => true, "data" => NULL];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }
    private function getCityStateNamesFromPincode($comp_id, $pincode)
    {
        try {
            if (empty($pincode)) {
                return ["status" => false, "error" => "Pincode is required"];
            }
            
            $pincode_table = $this->client_db_prefix . $comp_id . "." . $this->tables['location_pincode'];
            $city_table = $this->client_db_prefix . $comp_id . "." . $this->tables['location_city'];
            
            $sql = "SELECT 
                        c.city_name AS address_city_name,
                        s.state_name AS address_state_name,
                        p.city_id AS address_city_id,
                        p.state_id AS address_state_id ,
                        n.country_name 
                    FROM {$pincode_table} p
                    LEFT JOIN {$city_table} c ON p.city_id = c.id
                    LEFT JOIN {$this->tables['admin_state_master']} s ON p.state_id = s.state_id
                    LEFT JOIN {$this->tables['admin_country_master']} n ON n.country_id = s.country_id 
                    WHERE p.pincode = :pincode
                    LIMIT 1";
            
            $result = $this->fetchSingleData($sql, [
                ':pincode' => $pincode
            ]);
            
            if (!empty($result['status']) && !empty($result['data'])) {
                return ["status" => true, "data" => $result['data']];
            }
            return ["status" => false, "error" => $result['error'] ?? "No data found for pincode: {$pincode}"];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getOrderFullAddressDetail($comp_id, $order_data)
    {
        try {
            
            $address = json_decode($order_data['customer_address'], true);
            if (!empty($address) && !empty($address['address_pincode'])) {
                $cityStateResult = $this->getCityStateNamesFromPincode($comp_id, $address['address_pincode']);
                if (!empty($cityStateResult['status']) && !empty($cityStateResult['data'])) {
                    $address['address_city_name'] = $cityStateResult['data']['address_city_name'];
                    $address['address_state_name'] = $cityStateResult['data']['address_state_name'];
                    $address['address_city_id'] = $cityStateResult['data']['address_city_id'];
                    $address['address_state_id'] = $cityStateResult['data']['address_state_id'];
                    $address['address_country_name'] = $cityStateResult['data']['country_name'];
                } else if (!empty($cityStateResult['error'])) {
                    Logger::error($cityStateResult['error'], ['File' => __FILE__, 'Line' => __LINE__]);
                }
            }
            $order_data['customer_address_detail'] = $address;
            // Handle billing address - using pincode only
            $billing_address = json_decode($order_data['customer_billing_address'], true);
            if (!empty($billing_address) && !empty($billing_address['address_pincode'])) {
                $cityStateResult = $this->getCityStateNamesFromPincode($comp_id, $billing_address['address_pincode']);
                if (!empty($cityStateResult['status']) && !empty($cityStateResult['data'])) {
                    $billing_address['address_city_name'] = $cityStateResult['data']['address_city_name'];
                    $billing_address['address_state_name'] = $cityStateResult['data']['address_state_name'];
                    $billing_address['address_city_id'] = $cityStateResult['data']['address_city_id'];
                    $billing_address['address_state_id'] = $cityStateResult['data']['address_state_id'];
                    $billing_address['address_country_name'] = $cityStateResult['data']['country_name'];
                } else if (!empty($cityStateResult['error'])) {
                    Logger::error($cityStateResult['error'], ['File' => __FILE__, 'Line' => __LINE__]);
                }
            }
            $order_data['customer_billing_address'] = $billing_address;
            return ["status" => true, "data" => $order_data];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getOrderFullAddressDetailOld($comp_id, $order_data)
    {
        try {
            $address = json_decode($order_data['customer_address'], true);
            if (!empty($address['address_pincode'])) {
                
                $state_rs = $this->getFromTable($this->tables['admin_state_master'], "state_id=:id", [':id' => $address['address_state_id']]);
                if (empty($state_rs['status'])) {
                    throw new \Exception($state_rs['error']);
                }
                if (!empty($state_rs['data'])) {
                    $address['address_state_name'] = $state_rs['data'][0]['state_name'];
                }
                $city_table = $this->client_db_prefix . $comp_id . "." . $this->tables['location_city'];
                $city_rs = $this->getFromTable($city_table, "id=:id", [':id' => $address['address_city_id']]);
                if (empty($city_rs['status'])) {
                    throw new \Exception($city_rs['error']);
                }
                if (!empty($city_rs['data'])) {
                    $address['address_city_name'] = $city_rs['data'][0]['city_name'];
                }
            }
            $order_data['customer_address_detail'] = $address;
            $billing_address = json_decode($order_data['customer_billing_address'], true);
            if (!empty($billing_address)) {
                if (!empty($billing_address['address_state_id'])) {
                    $state_rs = $this->getFromTable($this->tables['admin_state_master'], "state_id=:id", [':id' => $billing_address['address_state_id']]);
                    if (empty($state_rs['status'])) {
                        throw new \Exception($state_rs['error']);
                    }
                    if (!empty($state_rs['data'])) {
                        $billing_address['address_state_name'] = $state_rs['data'][0]['state_name'];
                    }
                }
                if (!empty($billing_address['address_city_id'])) {
                    $city_table = $this->client_db_prefix . $comp_id . "." . $this->tables['location_city'];
                    $city_rs = $this->getFromTable($city_table, "id=:id", [':id' => $billing_address['address_city_id']]);
                    if (empty($city_rs['status'])) {
                        throw new \Exception($city_rs['error']);
                    }
                    if (!empty($city_rs['data'])) {
                        $billing_address['address_city_name'] = $city_rs['data'][0]['city_name'];
                    }
                }
            }
            $order_data['customer_billing_address'] = $billing_address;
            return ["status" => true, "data" => $order_data];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function stockOut($comp_id, $id, $rollback = 0, $type = 'IO', $conn = NULL)
    {
        try {
            $itemList = array();
            if ($type == 'IO') {
                $itemList = $this->getOrderItemListForStock($comp_id, 'o.id=' . $id);
            } else if ($type == 'IV') {
                $itemList = $this->getOuterStockInvenory($comp_id, 't1.inv_id=' . $id, 'qty');
            }
            if (!empty($itemList['status'])) {
                foreach ($itemList['data'] as $item) {
                    if ($item['item_id'] > 0) {
                        $updrs = $this->stockOutUpdate($comp_id, $item['item_id'], $item['branch_id'], $item['qty'], $rollback, $conn);
                        if (empty($updrs['status'])) {
                            throw new \Exception($updrs['error']);
                        }
                    }
                }
            } else if (isset($itemList['error'])) {
                throw new \Exception($itemList['error']);
            } else {
                throw new \Exception("Unable to get Item List for stock");
            }
            Logger::debug("stockOut done", ['File' => __FILE__, 'Line' => __LINE__]);
            return ["status" => true];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function updateDocketNumberUsage($comp_id, $docket_number, $data, $conn = NULL)
    {
        try {
            $rspupd = $this->update($this->client_db_prefix . $comp_id . "." . $this->tables['courier_docketno'], $data, ['docket_no' => $docket_number], [], $conn);
            if (empty($rspupd['status'])) {
                throw new \Exception($rspupd['error']);
            }
            return ["status" => true, "data" => NULL];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function updateOrderStatus($comp_id, $user_id, $order, $data, $next_status, $message)
    {
        try {
            $conn = $this->conn();
            $conn->beginTransaction();

            $rspupd = $this->update($this->client_db_prefix . $comp_id . "." . $this->tables['order'], $data, ['id' => $order['order_id']], [], $conn);
            if (empty($rspupd['status'])) {
                if ($conn->inTransaction()) $conn->rollBack();
                throw new \Exception($rspupd['error']);
            }
            $stdata = [
                "order_id" => $order['order_id'],
                "last_status_id" => $order['current_order_status'],
                "current_status_id" => $data['current_order_status'],
                "log_message" => $message
            ];
            if (!empty($user_id)) {
                $stdata['user_id'] = $user_id;
            }
            if (!empty($data['remarks'])) {
                $stdata['remarks'] = $data['remarks'];
            }
            $logrs = $this->addOrderLog($comp_id, $stdata, $conn);
            if (empty($logrs['status'])) {
                if ($conn->inTransaction()) $conn->rollBack();
                throw new \Exception($logrs['error']);
            }
            if ($next_status['status_type'] == 2) {
                $upd = ['used_time' => date('Y-m-d H:i:s'), 'order_id' => $order['order_id']];
                $uprs = $this->updateDocketNumberUsage($comp_id, $data['docket_number'], $upd, $conn);
                if (empty($uprs['status'])) {
                    if ($conn->inTransaction()) $conn->rollBack();
                    throw new \Exception($uprs['error']);
                }
            }
            if ($next_status['stock_in_out'] != 0) {
                if ($order['stock_status'] == 2 && $next_status['stock_in_out'] == 1) {
                    if ($order['order_assign_type'] == 0) {
                        $this->stockOut($comp_id, $order['order_id'], 1, 'IO');
                    }
                    $rspupd = $this->update($this->client_db_prefix . $comp_id . "." . $this->tables['order'], ['stock_status' => 1], ['id' => $order['order_id']], [], $conn);
                    if (empty($rspupd['status'])) {
                        if ($conn->inTransaction()) $conn->rollBack();
                        throw new \Exception($rspupd['error']);
                    }
                }
            }
            if ($conn->inTransaction()) $conn->commit();
            return ["status" => true, "data" => NULL];
        } catch (\Exception $e) {
            if (!empty($conn)) {
                if ($conn->inTransaction()) $conn->rollBack();
            }
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }

    function getOrderStatusListOwned($comp_id, $sess_user)
    {
        try {
            $table = $this->client_db_prefix . $comp_id . "." . $this->tables['order_status'];
            $status_arr_owned = !empty($sess_user->order_status_id) ? implode(",", $sess_user->order_status_id) : [];
            $whr = "mark_as_default_complete=:num";
            if ($sess_user->master_type != 1) {
                if (!empty($sess_user->order_status_id)) {
                    $whr .= " AND id IN(" . $status_arr_owned. ")";
                }
            }
            $rsp = $this->fetchSingleData("SELECT GROUP_CONCAT(id) as status_list FROM $table WHERE $whr LIMIT 1", [':num' => 2]);
            if (empty($rsp['status'])) {
                throw new \Exception($rsp['error']);
            }
            return [
                "status" => true,
                "data" => [
                    "owned" => $status_arr_owned,
                    "completed" => $rsp['data']['status_list']
                ]
            ];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getBranchRightsOwned($comp_id, $sess_user)
    {
        try {
            $table = $this->client_db_prefix . $comp_id . "." . $this->tables['branch_user_rights'];
            $whr = "user_id=:usr AND status=:num";
            $rsp = $this->fetchSingleData("SELECT GROUP_CONCAT(branch_id) as branch_list FROM $table WHERE $whr LIMIT 1", [':usr' => $sess_user->id, ':num' => 1]);
            if (empty($rsp['status'])) {
                throw new \Exception($rsp['error']);
            }
            return ["status" => true, "data" => $rsp['data']['branch_list']];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getChildUsers($user_id)
    {
        try {
            return $this->callProcedure($this->procedures['GetChildUsers'], ['user_id' => $user_id], true);
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getOrderListConditions($user_data, $input)
    {
        try {
            $branch_list = $this->getBranchRightsOwned($user_data->comp_id, $user_data);
            if (empty($branch_list['status'])) {
                throw new \Exception($branch_list['error']);
            }
            $branches = !empty($branch_list['data']) ? explode(",", $branch_list['data']) : [];
            $status_list = $this->getOrderStatusListOwned($user_data->comp_id, $user_data);
            if (empty($status_list['status'])) {
                throw new \Exception($status_list['error']);
            }

            if (!empty($input['search'])) {
                foreach ($input['search'] as $field => $value) {
                    if ($field == 'order_status') {
                        if (!empty($value)) {
                            if ($value == 1) { //ALL
                                if ($user_data->master_type != 1) {
                                    if (!empty($status_list['data']['owned'])) {
                                        $wharr['order_status_in'] = $status_list['data']['owned'];
                                    }
                                }
                            } else if ($value == 2) { //Pending
                                if (!empty($status_list['data']['completed'])) {
                                    $wharr['order_status_not_in'] = $status_list['data']['completed'];
                                }
                            } else if ($value == 3) { //Completed
                                if (!empty($status_list['data']['completed'])) {
                                    $wharr['order_status_in'] = $status_list['data']['completed'];
                                }
                            }
                        }
                    } else if ($field == 'assign_type') {
                        if (!empty($value)) {
                            if ($value == 2) {
                                $wharr['last_allocate_uid'] = 0;
                            } else if ($value == 3) {
                                $wharr['last_allocate_uid_null'] = NULL;
                            }
                        }
                    } else if ($field == 'archive') {
                        if ($value == 1 || $value == 0) {
                            $wharr['archive'] = $value;
                        }
                    } else if ($field == 'payment_status') {
                        if ($value == 1) {
                            $wharr['payment_confirm_id_notnull'] = $value;
                        } else if ($value == 0) {
                            $wharr['payment_confirm_id_null'] = $value;
                        }
                    } else if ($field == 'branch_id') {
                        if (!empty($value)) {
                            if ($user_data->master_type != 1 && in_array($value, $branches)) {
                                $wharr['branch_id'] = $value;
                            } else {
                                $wharr['branch_id'] = $value;
                            }
                        } else if ($user_data->master_type != 1 && !empty($branch_list['data'])) {
                            $wharr['branch_id_in'] = $branch_list['data'];
                        }
                    } else if ($field == "order_booked_by_id") {
                        $wharr['order_booked_by_id'] = is_array($value) ? implode(",", $value) : $value;
                    } else if ($field == "tl_id") {
                        $wharr['tl_id'] = is_array($value) ? implode(",", $value) : $value;
                    } else if ($field == "manager_id") {
                        $wharr['manager_id'] = is_array($value) ? implode(",", $value) : $value;
                    } elseif ($field == "lead_id") {
                        $wharr['lead_id'] = is_array($value) ? implode(",", $value) : $value;
                    } elseif ($field == "conected_lead") {
                        if ($value == 0) {
                            $wharr['lead_id_null'] = $value;
                        } else if ($value == 1) {
                            $wharr['lead_id_notnull'] = $value;
                        }
                    } elseif ($field == "is_moved_shopify") {
                        if ($value == 0) {
                            $wharr['shopify_number_null'] = $value;
                        } else if ($value == 1) {
                            $wharr['shopify_number_notnull'] = $value;
                        }
                    } elseif ($field == "confirm_by") {
                        if ($value == 1 || $value == 2) {
                            $wharr['confirm_by'] = intval($value);
                            $wharr['current_order_status_not'] = 11;
                        } else if ($value == 3) {
                            $wharr['confirm_by_null'] = NULL;
                            $wharr['current_order_status_not'] = 11;
                        } else if ($value == 4) {
                            $wharr['current_order_status_not'] = 11;
                        }
                    } else if ($field == "booking_time") {
                        if ($value) {
                            $wharr['from_date'] = $value[0];
                            $wharr['to_date'] = $value[1];
                        }
                    } else if ($field == "invoice_date") {
                        if ($value) {
                            $wharr['inv_from_date'] = $value[0];
                            $wharr['inv_to_date'] = $value[1];
                        }
                    } else if ($field == "rto_date") {
                        if ($value) {
                            $wharr['rto_from_date'] = $value[0];
                            $wharr['rto_to_date'] = $value[1];
                        }
                    } else if ($field == "payment_date") {
                        if ($value) {
                            $wharr['payment_from_date'] = $value[0];
                            $wharr['payment_to_date'] = $value[1];
                        }
                    } else if ($field == "delivery_date") {
                        if ($value) {
                            $wharr['delivery_from_date'] = $value[0];
                            $wharr['delivery_to_date'] = $value[1];
                        }
                    } else if ($field == "hold_till") {
                        if ($value) {
                            $wharr['hold_from_date'] = $value[0];
                            $wharr['hold_to_date'] = $value[1];
                        }
                    } else if ($field == "shopify_created_at") {
                        if ($value) {
                            $wharr['shopify_created_from_date'] = $value[0];
                            $wharr['shopify_created_to_date'] = $value[1];
                        }
                    } else if ($field == "lead_date") {
                        if ($value) {
                            $wharr['lead_date_from'] = $value[0];
                            $wharr['lead_date_to'] = $value[1];
                        }
                    } else if ($field == "invoice_return") {
                        if ($value == 0) {
                            $wharr['invoice_return_null'] = $value;
                        } else if ($value == 1) {
                            $wharr['invoice_return_notnull'] = $value;
                        }
                    } else {
                        $wharr[$field] = $value;
                    }
                }
            }

            $whstring = $this->whereString(
                $wharr,
                array('*' => 't.', 'mobile_number' => 'c.', 'alt_mobile1' => 'c.', 'address_city_id' => 'a.', 'address_pincode' => 'a.', 'address_state_id' => 'a.', 'current_order_status_not'=>'s.'),
                array(
                    'confirm_by_null' => 'ISNULL',
                    'current_order_status_not' => 'NOT IN',
                    'order_booked_by_id' => 'IN',
                    'tl_id' => 'IN',
                    'manager_id' => 'IN',
                    'lead_id' => 'IN',
                    'payment_confirm_id_notnull' => 'ISNOTNULL',
                    'payment_confirm_id_null' => 'ISNULL',
                    'lead_id_null' => 'ISNULL',
                    'lead_id_notnull' => 'ISNOTNULL',
                    'shopify_number_null' => 'ISNULL',
                    'shopify_number_notnull' => 'ISNOTNULL',
                    'order_status_not_in' => 'NOT IN',
                    'order_status_in' => 'IN',
                    'last_allocate_uid_null' => 'ISNULL',
                    'last_allocate_uid' => 'GT',
                    'branch_id_in' => 'IN',
                    'mobile_number' => 'WHERE',
                    'alt_mobile1' => 'LIKE',
                    'from_date' => 'DGTE_FF',
                    'to_date' => 'DLTE_LL',
                    'dis_from_date' => 'DGTE_F',
                    'dis_to_date' => 'DLTE_L',
                    'hold_from_date' => 'DGTE_F',
                    'hold_to_date' => 'DLTE_L',
                    'inv_from_date' => 'DGTE_F',
                    'inv_to_date' => 'DLTE_L',
                    'rto_from_date' => 'DGTE_F',
                    'rto_to_date' => 'DLTE_L',
                    'delivery_from_date' => 'DGTE_F',
                    'delivery_to_date' => 'DLTE_L',
                    'payment_from_date' => 'DGTE_F',
                    'payment_to_date' => 'DLTE_L',
                    'shopify_created_from_date' => 'DGTE_F',
                    'shopify_created_to_date' => 'DLTE_L',
                    'lead_date_to' => 'DLTE_LL',
                    'lead_date_from' => 'DGTE_FF',
                    'invoice_return_null' => 'ISNULL',
                    'invoice_return_notnull' => 'ISNOTNULL'
                ),
                array(
                    'confirm_by_null' => 'confirm_by',
                    'current_order_status_not' => 'status_type',
                    'shopify_number_null' => 'shopify_number',
                    'shopify_number_notnull' => 'shopify_number',
                    'lead_id_null' => 'lead_id',
                    'lead_id_notnull' => 'lead_id',
                    'payment_confirm_id_null' => 'payment_confirm_id',
                    'payment_confirm_id_notnull' => 'payment_confirm_id',
                    'order_status_in' => 'current_order_status',
                    'order_status_not_in' => 'current_order_status',
                    'last_allocate_uid_null' => 'last_allocate_uid',
                    'branch_id_in' => 'branch_id',
                    'from_date' => 'booking_time',
                    'to_date' => 'booking_time',
                    'dis_from_date' => 'dispatch_date',
                    'dis_to_date' => 'dispatch_date',
                    'hold_from_date' => 'hold_till',
                    'hold_to_date' => 'hold_till',
                    'inv_from_date' => 'invoice_date',
                    'inv_to_date' => 'invoice_date',
                    'rto_from_date' => 'rto_date',
                    'rto_to_date' => 'rto_date',
                    'payment_from_date' => 'payment_date',
                    'payment_to_date' => 'payment_date',
                    'delivery_from_date' => 'delivery_date',
                    'delivery_to_date' => 'delivery_date',
                    'shopify_created_from_date' => 'shopify_created_at',
                    'shopify_created_to_date' => 'shopify_created_at',
                    'lead_date_to' => 'lead_date',
                    'lead_date_from' => 'lead_date',
                    'invoice_return_null' => 'invoice_return_date',
                    'invoice_return_notnull' => 'invoice_return_date',
                    'manager_id' => 'o_manager_id',
                    'tl_id' => 'o_tl_id',
                )
            );
            return ['status' => true, 'data' => $whstring];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }

    function getEmailSmsWhatsAppLog($comp_id, $req_id) {
        try {
            $tbl  = $this->client_db_prefix.$comp_id . "." . $this->tables['email_sms_log'];
            $tbl2 = $this->client_db_prefix.$comp_id . "." . $this->tables['order_message_template'];
            $tbl3 = $this->tables['whatsapp_solution_providers'];
            return $this->fetchAllData("SELECT t1.*, t2.template_name, t2.sender_id, t2.dlt_template_id, t2.whatsapp_provider, t2.is_whatsapp_business_template, t3.api_method AS whatsapp_api_method, t3.api_data_type AS whatsapp_api_data_type, t3.response_type AS whatsapp_response_type, JSON_UNQUOTE(t3.template_message) AS whatsapp_template_message, JSON_UNQUOTE(t3.text_message) AS whatsapp_text_message, JSON_UNQUOTE(t3.image_message) AS whatsapp_image_message, JSON_UNQUOTE(t3.audio_message) AS whatsapp_audio_message, JSON_UNQUOTE(t3.video_message) AS whatsapp_video_message, JSON_UNQUOTE(t3.attachment_message) AS whatsapp_attachment_message FROM $tbl t1 LEFT JOIN $tbl2 t2 ON(t2.id=t1.template_id)  LEFT JOIN $tbl3 t3 ON(t3.provider=t2.whatsapp_provider) WHERE t1.status=:st AND t1.sent=0 AND t1.request_id=:req_id ORDER BY created_at", [":st"=>0, ':req_id'=>$req_id]);
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getBranchMasterForMailer($comp_id) {
        try {
            $tbl  = $this->client_db_prefix.$comp_id . "." . $this->tables['branch_master'];
            $tbl3 = $this->tables['whatsapp_solution_providers'];
            $tbl4 = $this->tables['sms_solution_providers'];
            return $this->fetchAllData("SELECT t1.*, JSON_UNQUOTE(t1.whatsapp_api_headers) AS whatsapp_api_headers, t3.api_method AS whatsapp_api_method, t3.api_data_type AS whatsapp_api_data_type, t3.response_type AS whatsapp_response_type, JSON_UNQUOTE(t3.template_message) AS whatsapp_template_message, JSON_UNQUOTE(t3.text_message) AS whatsapp_text_message, JSON_UNQUOTE(t3.image_message) AS whatsapp_image_message, JSON_UNQUOTE(t3.audio_message) AS whatsapp_audio_message, JSON_UNQUOTE(t3.video_message) AS whatsapp_video_message, JSON_UNQUOTE(t3.attachment_message) AS whatsapp_attachment_message, t4.api_method AS sms_api_method, t4.api_data_type AS sms_api_data_type, t4.response_type AS sms_api_response_type FROM $tbl t1 LEFT JOIN $tbl3 t3 ON(t3.provider=t1.whatsapp_provider) LEFT JOIN $tbl4 t4 ON(t4.provider=t1.sms_provider) ORDER BY created_at");
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getBranchMasterForDialer($comp_id)
    {
        try {
            $tbl  = $this->client_db_prefix . $comp_id . "." . $this->tables['branch_master'];
            $tbl2 = $this->tables['dial_solution_providers'];
            return $this->fetchAllData("SELECT t1.*, t2.api_method AS dial_api_method, t2.api_data_type AS dial_api_data_type, t2.response_type AS dial_response_type FROM $tbl t1 LEFT JOIN $tbl2 t2 ON(t2.provider=t1.dialer_provider) ORDER BY created_at");
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }
}