<?php
namespace App\Model;
use App\Model\BaseModel;
use App\Core\Service\Logger;
use App\Config\Main;
use App\Library\Fx;
class OrderModel extends BaseModel
{
    private $fx;
    public function __construct()
    {
        parent::__construct();
        $this->fx = new Fx();
    }
    
    function generateBranchOrderId($comp_id, $order_id, $branch_id, $state_id) {
        try {
            $sql  = "SELECT branch_prefix, order_prefix_id";
            $sql .= " FROM " . $this->client_db_prefix.$comp_id . "." . $this->tables['branch_master'] . " ";
            $sql .= " WHERE branch_id= " . $branch_id;
            $branchrs = $this->fetchSingleData($sql);
            if(empty($branchrs['status'])) {
                throw new \Exception($branchrs['error']);
            }
            if(empty($branchrs['data'])) {
                throw new \Exception("Branch detail not found to generate order number");
            }
            if (empty($branchrs['data']['branch_prefix'])) {
                throw new \Exception("Branch prefix not found to generate order number");
            }
            $state_code = 'HR';
            if (!empty($state_id)) {
                $sql2  = "SELECT state_code FROM `" . $this->tables['admin_state_master'] . "` as s  WHERE state_id= " . $state_id;
                $staters = $this->fetchSingleData($sql2);
                if(empty($staters['status'])) {
                    throw new \Exception($staters['error']);
                }
                if(!empty($staters['data'])) {
                    $state_code = $staters['data']['state_code'];
                }
            }
            $prefix = $this->fx->generateOrderPrefix($branchrs['data']['branch_prefix']. str_pad($order_id, 4, "0", STR_PAD_LEFT), $state_code);
            return ["status" => true, "data" => $prefix, 'order_prefix_id'=>$branchrs['data']['order_prefix_id']];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function prepareOrderData(Array $data, $comp_id) {
        try {
            $order = array(
                'customer_id' => $data['customer_id'], 
                'lead_id' => (isset($data['lead_id']) && $data['lead_id'] != '') ? $data['lead_id'] : NULL, 
                'branch_id' => $data['branch_id'], 
                'address_id' => $data['address_id'], 
                'customer_address' => json_encode($data['address_detail']), 
                'payment_type' => !empty($data['payment_type'])?$data['payment_type']:NULL, 
                'notes' => !empty($data['notes'])? \addslashes($data['notes']) : NULL, 
                'total_no_item' => count($data['item_list']), 
                'gross_amt' => $data['gross_amt'], 
                'discount_amt' => $data['discount_amt'], 
                'tax_amt' => $data['tax_amt'], 
                'round_off_amt' => $data['round_off_amt'], 
                'net_amt' => ($data['net_amount']) ? $data['net_amount'] : 0.00, 
                'vpp_disc_percentage' => (isset($data['vpp_disc_percentage']) && $data['vpp_disc_percentage'] > 0) ? $data['vpp_disc_percentage'] : 0.00, 
                'vpp_disc_amt' => (isset($data['vpp_disc_amt']) && $data['vpp_disc_amt'] > 0) ? $data['vpp_disc_amt'] : 0.00, 
                'updated_time' => date('Y-m-d H:i:s'), 
                'ip_address' => NULL, 
                'customer_detail' => json_encode($data['customer_detail']), 
                'customer_type' => $data['customer_type'], 
                'current_order_status' => $data['current_order_status'], 
                'package_id' => !empty($data['package_id']) ? implode(',', $data['package_id']) : NULL, 
                'coupon_code' => !empty($data['coupon_code']) ? $data['coupon_code'] : NULL,
                'order_coupon_id' => !empty($data['order_coupon_id']) ? $data['order_coupon_id'] : NULL, 
                'transaction_id' => !empty($data['transaction_id']) ? $data['transaction_id'] : NULL,
                'advance_payment' => !empty($data['advance_payment']) ? $data['advance_payment'] : 0.00,
                'partial_payment' => !empty($data['partial_payment']) ? $data['partial_payment'] : 0.00,
                'additional_discount' => empty($data['additional_discount']) ? $data['additional_discount'] : 0.00,
                'courier_charges' => !empty($data['courier_charges']) ? $data['courier_charges'] : 0.00
            );
            $order['advance_payment'] = !empty($data['advance_payment']) ? $data['advance_payment'] : 0.00;
            $order['partial_payment'] = !empty($data['partial_payment']) ? $data['partial_payment'] : 0.00;
            $order['additional_discount'] = !empty($data['additional_discount']) ? $data['additional_discount'] : 0.00;
            $order['courier_charges'] = !empty($data['courier_charges']) ? $data['courier_charges'] : 0.00;
            $order['is_paid'] = (isset($data['is_paid'])) ? 1 : 0;
            if (isset($data['same_as_shipping'])) {
                $order['same_as_shipping'] = 1;
                $order['customer_billing_address'] = $order['customer_address'];
            } else {
                $order['same_as_shipping'] = 0;
                $order['customer_billing_address'] = json_encode(array(
                    'customer_id' => $data['customer_id'], 
                    'billing_pincode' => $data['billing_pincode'], 
                    'address_state' => $data['billing_state'], 
                    'address_city' => $data['billing_city'], 
                    'billing_address' => $data['billing_address']
                ));
            }
            if (!empty($data['payment_confirm_id'])) {
                $order['payment_confirm_id'] = $data['payment_confirm_id'];
                $order['payment_date'] 	=	$data['payment_date'];
            }
            if (!empty($data['booking_time'])) {
                $order['booking_time'] = $data['booking_time'];
            }
            if (!empty($data['shopify_id'])) {
                $order['shopify_id'] = $data['shopify_id'];
                $order['shopify_number'] 	=	$data['shopify_number'];
            }
            if (!empty($data['delivery_attempt'])) {
                $order['delivery_attempt'] = $data['delivery_attempt'];
                $order['delivery_date'] 	=	$data['delivery_date'];
            }

            $order['expected_carrier_id'] = (isset($data['expected_carrier_id']) && $data['expected_carrier_id'] > 0) ? $data['expected_carrier_id'] : null;
            $order['order_created_by_id'] = !empty($data['order_created_by_id']) ? $data['order_created_by_id'] : 0;
            $order['order_booked_by_id'] = !empty($data['order_booked_by_id']) ? $data['order_booked_by_id'] : 0;
            $order['order_booked_by_name'] = !empty($data['order_booked_by_name']) ? $data['order_booked_by_name'] : NULL;
            $order['call_type'] = !empty($data['call_type']) ? $data['call_type'] : NULL;
            if (empty($data['order_id'])) {
                $order['o_tl_id'] = @$data['o_tl_id'];
                $order['o_tl_name'] = @$data['o_tl_name'];
                $order['o_manager_id'] = @$data['o_manager_id'];
                $order['o_manager_name'] = @$data['o_manager_name'];
                $order['o_group_id'] = @$data['o_group_id'];
                $order['o_group_name'] = @$data['o_group_name'];
            }
            if (isset($data['order_platform_origin'])) {
                $order['order_platform_origin'] = $data['order_platform_origin'];
            }
            $order['order_assign_type'] = 0;
            $order['dealer_id'] = Null;
            $custypers = $this->getCustomerTypeList("id=".$data['customer_type']." AND customer_type=2", $comp_id);
            if(empty($custypers['status'])) {
                throw new \Exception($custypers['error']);
            }
            if(!empty($custypers['data'])) {
                $dealers = $this->checkPincodeDetailWithDealer("pincode='".$data['address_pincode']."'", $comp_id);
                if (empty($dealers['status'])) {
                    throw new \Exception($dealers['error']);
                }
                if(!empty($dealers['data'])) {
                    $order['order_assign_type'] = 1;
                    $order['dealer_id'] = $dealers['data']['dealer_id'];
                }
            }
            if(!empty($data['order_id'])) {
                if (!empty($data['bookchangeRights'])) {
                    unset($order['order_booked_by_id'], $order['order_booked_by_name']);
                }
                unset($order['current_order_status'], $order['order_created_by_id']);
            }
            return ["status" => true, "data" => $order];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }
    
    function prepareOrderItemData(Array $data, $order_id) {
        try {
            $item_arr = [];
            if(!empty($data['item_list'])) {
                foreach ($data['item_list'] as $key => $item) {
                    $one_item = array(
                        'order_id' => $order_id, 
                        'category_id' => !empty($item['category_id'])?$item['category_id']:NULL, 
                        'item_package_id' => !empty($item['item_package_id'])?$item['item_package_id']:NULL,
                        'discount_type' => !empty($item['discount_type'])?$item['discount_type']:NULL, 
                        'discount_amount' => !empty($item['discount_amount'])?$item['discount_amount']:NULL,
                        'discount' => !empty($item['discount'])?$item['discount']:NULL,
                        'item_name' => !empty($item['item_name'])?$item['item_name']:NULL,
                        'item_id' => !empty($item['item_id'])?$item['item_id']:NULL,
                        'rate' => !empty($item['rate'])?$item['rate']:0.00,
                        'qty' => !empty($item['qty'])?$item['qty']:0.00,
                        'amount' => !empty($item['amount'])?$item['amount']:0.00,
                        'tax_percentage' => !empty($item['tax_percentage'])?$item['tax_percentage']:0.00,
                        'tax_amount' => !empty($item['tax_amount'])?$item['tax_amount']:0.00,
                        'total_amount' => !empty($item['total_amount'])? $item['total_amount']:NULL,
                        'mrp' => !empty($item['mrp']) ? $item['mrp'] : 0.00, 
                        'including_tax' => !empty($item['including_tax']) ? $item['including_tax'] : 0, 
                        'hsn_code' => !empty($item['hsn_code'])?$item['hsn_code']:NULL,
                        'ip_address' => NULL
                    );
                    $one_item['item_detail'] = json_encode([
                        "item_code"=>$item['item_code'],
                        "category_id"=>$item['category_id'],
                        "group_id"=>$item['group_id'],
                        "unit"=>$item['unit'],
                        "rate"=>$one_item['rate'],
                        "qty" => $one_item['qty'],
                        "discount" => $one_item['discount'],
                        "discount_type" => $one_item['discount_type'],
                        "total_rate"=>$item['total_rate'],
                        "including_tax"=>$one_item['including_tax'],
                        "image"=>$item['image'],
                        "variant_id"=>$item['variant_id']
                    ]);
                    $item_arr[] = $one_item;
				}
            }
            return ["status"=>true, "data"=>$item_arr];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function prepareOrderTaxData(Array $data, $order_id) {
        try {
            $tax_arr = [];
            if(!empty($data['item_tax_list'])) {
                foreach ($data['item_tax_list'] as $key => $item_taxes) {
                    foreach($item_taxes as $item_id=>$item) {
                        $tax_arr[] = array(
                            'order_id' => $order_id, 
                            'tax_id' => !empty($item['tax_id'])?$item['tax_id']:NULL, 
                            'tax_name' => !empty($item['tax_name'])?$item['tax_name']:NULL, 
                            'item_id' => !empty($item['item_id'])?$item['item_id']:NULL,
                            'item_name' => !empty($item['item_name'])?$item['item_name']:NULL,
                            'tax_percentage' => !empty($item['tax_percentage'])?$item['tax_percentage']:0.00,
                            'taxable_amount' => !empty($item['taxable_amount'])? $item['taxable_amount']:NULL,
                            'tax_amount' => !empty($item['tax_amount'])?$item['tax_amount']:0.00
                        );
                    }
				}
            }
            return ["status"=>true, "data"=>$tax_arr];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getAllTaxType($comp_id, $branch_id = 0, $state_id = 0)
	{
		try {
            $sql1  = "SELECT b.branch_id, s.is_union_territory, s.state_name, s.state_id FROM `" . $this->client_db_prefix.$comp_id . "`.`" . $this->tables['branch_master'] . "` as b ";
            $sql1 .= " JOIN ".$this->tables['admin_state_master']." as s ON(s.state_id=b.state) ";
            $sql1 .= " WHERE branch_id= " . $branch_id;
            $branchrs = $this->fetchSingleData($sql1);
            if(empty($branchrs['status'])) {
                throw new \Exception($branchrs['error']);
            }
            if(empty($branchrs['data'])) {
                throw new \Exception("Branch detail not found to get tax types");
            }
            $sql2  = "SELECT state_id, is_union_territory FROM `" . $this->tables['admin_state_master'] . "` as s  WHERE state_id= " . $state_id;
            $staters = $this->fetchSingleData($sql2);
            if(empty($staters['status'])) {
                throw new \Exception($staters['error']);
            }
            if(empty($staters['data'])) {
                throw new \Exception("State detail not found to get tax types");
            }
            $taxArray[] = 'All';
            if (($branchrs['data']['state_id'] == $staters['data']['state_id']) && $staters['data']['is_union_territory'] == 1) {
                $taxArray[] = 'Intra';
                $taxArray[] = 'Union';
            } else if (($branchrs['data']['state_id'] == $staters['data']['state_id']) && $staters['data']['is_union_territory'] == 0) {
                $taxArray[] = 'Intra';
                $taxArray[] = 'State';
            } else {
                $taxArray[] = 'Inter';
            }
            return ["status" => true, "data" => $taxArray];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
	}

    function getItemTaxDetail($comp_id, $item_id, $tax_types = [])
	{
        try {
            $sql  = "SELECT i.item_id,i.item_name,i.item_desc,t.tax_name as tax_name,t.tax_id as tax_id,tm.tax_percentage";
            $sql .= " FROM ".$this->client_db_prefix.$comp_id . "." . $this->tables['item_master'] . " as i ";
            $sql .= " LEFT JOIN ".$this->client_db_prefix.$comp_id . "." . $this->tables['itemcategorytax_master'] . " as tm ON(tm.cat_id=i.category_id) ";
            $sql .= " LEFT JOIN ".$this->client_db_prefix.$comp_id . "." . $this->tables['tax_master'] . " as t ON(t.tax_id=tm.tax_id) ";
            $sql .= " WHERE i.item_id=".$item_id;
            if(!empty($tax_types)) {
                $sql .= " AND t.tax_type IN('".implode("','", $tax_types)."')";
            }
            $sql .= " ORDER BY i.item_id";
            $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 getStockDetail($comp_id, string | Array $item_id,  $branch_id, $where = '', $order = array()) {
        try {
            $order['order_id'] = !empty($order['order_id']) ? $order['order_id'] : NULL;
            $qry = "IFNULL((stock),0) as stock_qty";
            if (!empty($order['stock_status']) && $order['stock_status'] == 2) {
                $qry = "IFNULL((stock),0)+IFNULL(t3.qty,0) as stock_qty";
            }
            $sql  = "SELECT t1.*, $qry, t3.qty as ordered_qty";
            $sql .= " FROM ".$this->client_db_prefix.$comp_id . "." . $this->tables['item_master']." t1 ";
            $sql .= " LEFT JOIN ".$this->client_db_prefix.$comp_id . "." . $this->tables['item_stock']." t2 ON(t2.item_id=t1.item_id and t2.branch_id=$branch_id)";
            $sql .= " LEFT JOIN ".$this->client_db_prefix.$comp_id . "." . $this->tables['order_item_list']." t3 ON(t1.item_id=t3.item_id and order_id='$order[order_id]')";
            $sql .= " WHERE t2.branch_id=$branch_id "; 
            if(!empty($where)) {
                $sql .= " AND " . $where;
            }
            if(!empty($item_id)) {
                if(is_array($item_id)) {
                    $sql .= " AND t1.item_id IN('" . implode("','", $item_id) . "')";
                }
            }
            
            $rsp = $this->fetchAllData($sql);
            if(empty($rsp['status'])) {
                throw new \Exception($rsp['error']);
            }
            $data = array();
            if(!empty($rsp['data'])) {
                foreach ($rsp['data'] as $key => $value) {
                    $data[$value['item_id']] = $value;
                }
            }
            return ["status" => true, "data" => $data];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()]; 
        }
    }

    function validateAndAddNewCustomer(Array $data, $comp_id) {
        try {
            // echo "<pre>";
            //         print_r($data);
            $sql  = "SELECT id, customer_type";
            $sql .= " FROM ".$this->client_db_prefix.$comp_id . "." . $this->tables['customer_master'];
            $sql .= " WHERE mobile_number=:mobno AND country_code=:cncode";
            $rsp = $this->fetchAllData($sql, [':mobno'=>trim($data['mobile_number']), ':cncode'=>trim($data['phone_code'])]);
            if(empty($rsp['status'])) {
                throw new \Exception($rsp['error']);
            }
            $dbdata = [
                "customer_name" => ucfirst($data['customer_name']),
                "mobile_number" => trim($data['mobile_number']),
                "country_code" => trim($data['phone_code'])
            ];
            
            if(!empty($data['email_id'])) {
                $dbdata['email_id'] = trim($data['email_id']);
            }
            if(!empty($data['alt_mobile1'])) {
                $dbdata['alt_mobile1'] = trim($data['alt_mobile1']);
            }
            if(!empty($data['branch_id'])) {
                $dbdata['branch_id'] = trim($data['branch_id']);
            }
            if(!empty($data['customer_type'])) {
                $dbdata['customer_type'] = trim($data['customer_type']);
            }
            if (empty($rsp['data'])) { 
                $rspins = $this->insert($this->client_db_prefix.$comp_id . "." . $this->tables['customer_master'], $dbdata);
                if(empty($rspins['status'])) {
                    throw new \Exception($rspins['error']);
                }
                $customer_id = $rspins['insert_id'];
            } else {
                $updData = [];
                if (empty($rsp['data'][0]['customer_name'])) {
                    $updData['customer_name'] = ucfirst($data['customer_name']);
                }
                if (empty($rsp['data'][0]['mobile_number'])) {
                    $updData['mobile_number'] = trim($data['mobile_number']);
                }
                if (empty($rsp['data'][0]['country_code'])) {
                    $updData['country_code'] = trim($data['phone_code']);
                }
                if (empty($rsp['data'][0]['email_id'])) {
                    $updData['email_id'] = trim($data['email_id']);
                }
                if (empty($rsp['data'][0]['alt_mobile1'])) {
                    $updData['alt_mobile1'] = !empty($data['alt_mobile1']) ? trim($data['alt_mobile1']) : '';
                }
                if (empty($rsp['data'][0]['branch_id'])) {
                    $updData['branch_id'] = trim($data['branch_id']);
                }
                if (empty($rsp['data'][0]['customer_type'])) {
                    $updData['customer_type'] = trim($data['customer_type']);
                }
                if (!empty($updData)) {
                    $rspins = $this->update($this->client_db_prefix . $comp_id . "." . $this->tables['customer_master'], $updData, ['id' => $rsp['data'][0]['id']]);
                    if (empty($rspins['status'])) {
                        throw new \Exception($rspins['error']);
                    }
                }
                $customer_id = $rsp['data'][0]['id'];
            }

            $address_db_data = array(
                'customer_id' => $customer_id,
                'address_pincode' => $data['address_pincode'],
                'address_state_id' => $data['address_state_id'],
                'address_city_id' => $data['address_city_id'],
                'address_mobile' => $data['address_mobile'],
                'address' => $data['address'],
                'gst' => !empty($data['gst']) ? $data['gst'] : ""
            );
            if (empty($data['address_id'])) {
                $rspaddr = $this->insert($this->client_db_prefix . $comp_id . "." . $this->tables['customer_address_master'], $address_db_data);
                if (empty($rspaddr['status'])) {
                    throw new \Exception($rspaddr['error']);
                }
                $address_id = $rspaddr['insert_id'];
            } else {
                $address_id = $data['address_id'];  
                $addSql  = "SELECT *";
                $addSql .= " FROM " . $this->client_db_prefix . $comp_id . "." . $this->tables['customer_address_master'];
                $addSql .= " WHERE address_pincode=:address_pincode AND customer_id=:customer_id";
                $fetchExistingAdd = $this->fetchAllData($addSql, [':address_pincode' => trim($data['address_pincode']), ':customer_id' => trim($customer_id)]);
                if (empty($fetchExistingAdd['status'])) {
                    throw new \Exception($fetchExistingAdd['error']);
                }
                if (empty($fetchExistingAdd['data'])) {
                    $rspaddr = $this->insert($this->client_db_prefix . $comp_id . "." . $this->tables['customer_address_master'], $address_db_data);
                    if (empty($rspaddr['status'])) {
                        throw new \Exception($rspaddr['error']);
                    }
                    $address_id = $rspaddr['insert_id'];
                }
                // $rspaddr = $this->update($this->client_db_prefix . $comp_id . "." . $this->tables['customer_address_master'], $address_db_data, ['id' => $data['address_id']]);
                // if (empty($rspaddr['status'])) {
                //     throw new \Exception($rspaddr['error']);
                // }
            }
            $address_db_data['address_id'] = $address_id;

            return ["status" => true, "data" => ["customer_id" => $customer_id, "address_id" => $address_id, "customer" => $dbdata, "address" => $address_db_data]];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getManagerAndTLById($comp_id, $user_id) {
        try {
            $sql  = "SELECT agent.profile, grp.group_name, agent.user_name, tl.user_id AS tl_id, tl.user_name AS tl_name, manager.user_id AS manager_id, manager.user_name AS manager_name, grp.group_name";
            $sql .= " FROM " . $this->client_db_prefix . $comp_id . "." . $this->tables['users_master'] . " as agent ";
            $sql .= " LEFT JOIN " . $this->client_db_prefix . $comp_id . "." . $this->tables['users_master'] . " as tl ON(agent.manager_id = tl.user_id) ";
            $sql .= " LEFT JOIN " . $this->client_db_prefix . $comp_id . "." . $this->tables['users_master'] . " as manager ON(tl.manager_id = manager.user_id) ";
            $sql .= " LEFT JOIN " . $this->client_db_prefix . $comp_id . "." . $this->tables['users_group_master'] . " as grp ON(agent.profile = grp.id) ";
            $sql .= " WHERE agent.user_id=".$user_id;
            $rsp = $this->fetchAllData($sql);
            if(empty($rsp['status'])) {
                throw new \Exception($rsp['error']);
            }
            if(!empty($rsp['data'])) {
                return ["status" => true, "data" => $rsp['data'][0]];
            } else {
                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 getCustomerTypeList($where, $comp_id) {
        try {
            $sql  = "SELECT id,category_name,customer_type,status";
            $sql .= " FROM ".$this->client_db_prefix.$comp_id . "." . $this->tables['customer_category_master'];
            if(!empty($where)) {
                $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 checkPincodeDetailWithDealer($where, $comp_id) {
        try {
            $sql  = "SELECT * ";
            $sql .= " FROM ".$this->client_db_prefix.$comp_id . "." . $this->tables['location_pincode'];
            $sql .= " WHERE ".$where;
            $sql .= " LIMIT 1 ";
            $rsp = $this->fetchAllData($sql);
            if(empty($rsp['status'])) {
                throw new \Exception($rsp['error']);
            }
            if(!empty($rsp['data'])){
                return ["status" => true, "data" => $rsp['data'][0]];
            } else {
                return ["status" => true, "data" => NULL];
            }
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function saveOrderData($comp_id, $data, $order_status_detail) {
        try {
            $ord = $this->prepareOrderData($data, $comp_id);
            if(empty($ord['status'])) {
                throw new \Exception($ord['error']);
            }            
            $conn = $this->conn();   
            $conn->beginTransaction();
            if(empty($data['id'])) {
                $rspord = $this->insert($this->client_db_prefix.$comp_id . "." . $this->tables['order'], $ord['data'], $conn);
                if(empty($rspord['status'])) {
                    if($conn->inTransaction()) $conn->rollBack();
                    throw new \Exception($rspord['error']);
                }
                $order_id = $rspord['insert_id'];
                $branch_order_id = $this->generateBranchOrderId($comp_id, $order_id, $data['branch_id'], $data['address_state_id']);
                if(empty($branch_order_id['status'])) {
                    if($conn->inTransaction()) $conn->rollBack();       
                    throw new \Exception($branch_order_id['error']);
                }
                $updord = $this->update($this->client_db_prefix.$comp_id . "." . $this->tables['order'], [
                    'customer_order_id'=>$branch_order_id['data'],
                    'order_series'=>$order_id,
                    'order_prefix_id'=>!empty($branch_order_id['order_prefix_id'])?$branch_order_id['order_prefix_id']:NULL
                ], ['id'=>$order_id], [], $conn);
                if(empty($updord['status'])) {          
                    if($conn->inTransaction()) $conn->rollBack();
                    throw new \Exception($updord['error']);
                }  
            } else {
                $order_id = $data['id'];
                $branch_order_id = $this->generateBranchOrderId($comp_id, $order_id, $data['branch_id'], $data['address_state_id']);
                if(empty($branch_order_id['status'])) {
                    if($conn->inTransaction()) $conn->rollBack();       
                    throw new \Exception($branch_order_id['error']);
                }
                $ord['data']['customer_order_id'] = $branch_order_id['data'];
                $ord['data']['order_series'] = $order_id;
                $ord['data']['order_prefix_id'] = !empty($branch_order_id['order_prefix_id'])?$branch_order_id['order_prefix_id']:NULL;
                 
                $rspord = $this->update($this->client_db_prefix.$comp_id . "." . $this->tables['order'], $ord['data'], ['id'=>$order_id], [], $conn);
                if(empty($rspord['status'])) {
                    if($conn->inTransaction()) $conn->rollBack();
                    throw new \Exception($rspord['error']);
                }
            }
            if(!empty($ord['data']['lead_id'])) {
                $updleadprev = $this->update($this->client_db_prefix.$comp_id . "." . $this->tables['leads'], ['order_id'=>null], ['order_id'=>$order_id], [], $conn);
                if(empty($updleadprev['status'])) {          
                    if($conn->inTransaction()) $conn->rollBack();
                    throw new \Exception($updleadprev['error']);
                }  

                $updlead = $this->update($this->client_db_prefix.$comp_id . "." . $this->tables['leads'], [
                    'order_id'=>$order_id
                ], ['id'=>$ord['data']['lead_id']], [], $conn);
                if(empty($updlead['status'])) {          
                    if($conn->inTransaction()) $conn->rollBack();
                    throw new \Exception($updlead['error']);
                }                  
            }
            if(!empty($data['id'])) { //IN CASE OF UPDATE
                if($data['stock_status'] != 0) {
                    if ($data['stock_status'] == 2) {
                        $st = $this->fx->stockOut($comp_id, $order_id, $conn, 1, 'IO');
                        if(empty($st['status'])) {
                            throw new \Exception($st['error']);
                        }
                        $stup = $this->updateOrder($comp_id, ['stock_status'=>0], ['id'=>':order_id'], [':order_id'=>$order_id], $conn);
                        if(empty($stup['status'])) {
                            throw new \Exception($stup['error']);
                        }
                    } else {
                        $st = $this->fx->stockOut($comp_id, $order_id, $conn, 0, 'IO');
                        if(empty($st['status'])) {
                            throw new \Exception($st['error']);
                        }
                        $stup = $this->updateOrder($comp_id, ['stock_status'=>1], ['id'=>':order_id'], [':order_id'=>$order_id], $conn);
                        if(empty($stup['status'])) {
                            throw new \Exception($stup['error']);
                        }
                    }
                }
                $this->delete($this->client_db_prefix.$comp_id . "." . $this->tables['order_item_list'], ['order_id' => $order_id], [], $conn);
				$this->delete($this->client_db_prefix.$comp_id . "." . $this->tables['order_tax_list'], ['order_id' => $order_id], [], $conn);

                $coupon_used = $this->client_db_prefix.$comp_id . "." . $this->tables['coupon_used'];
                if(!empty($data['delete_coupon_used_where'])) {
                    $this->delete($coupon_used, $data['delete_coupon_where'], [], $conn);    
                }

                if(!empty($data['update_coupon_used_where'])) {
                    $cpnd = $this->fetchAllData("SELECT * FROM $coupon_used WHERE coupon_name='".$data['update_coupon_used_where']['coupon_code']."' LIMIT 1", []);
                    if(empty($cpnd['status'])) {
                        if($conn->inTransaction()) $conn->rollBack();
                        throw new \Exception($cpnd['error']);
                    }
                    if(empty($cpnd['data'])) {
                        $upd = $this->update($this->client_db_prefix.$comp_id . "." . $this->tables['coupon_list'], ['is_used'=>0], ['id'=>$data['delete_coupon_used_where']['coupon_id']], [],$conn);    
                        if(empty($upd['status'])) {
                            if($conn->inTransaction()) $conn->rollBack();
                            throw new \Exception($upd['error']);
                        }
                    }
                }
            }
            $ord_item = $this->prepareOrderItemData($data, $order_id);
            if(empty($ord_item['status'])) {
                if($conn->inTransaction()) $conn->rollBack();
                throw new \Exception($ord_item['error']);
            }
            $ord_tax = $this->prepareOrderTaxData($data, $order_id);
            if(empty($ord_tax['status'])) {
                if($conn->inTransaction()) $conn->rollBack();
                throw new \Exception($ord_tax['error']);
            }
            if(!empty($ord_item['data'])) {
                $rspitem = $this->batchInsert($this->client_db_prefix.$comp_id . "." . $this->tables['order_item_list'], $ord_item['data'], $conn);
                if(empty($rspitem['status'])) {
                    if($conn->inTransaction()) $conn->rollBack();
                    throw new \Exception($rspitem['error']);
                }
            }
            if(!empty($ord_tax['data'])) {
                $rsptax = $this->batchInsert($this->client_db_prefix.$comp_id . "." . $this->tables['order_tax_list'], $ord_tax['data'], $conn);
                if(empty($rsptax['status'])) {
                    if($conn->inTransaction()) $conn->rollBack();
                    throw new \Exception($rsptax['error']);
                }
            }
            if($order_status_detail['stock_in_out'] != 0) {
                $rollback = 0;
                $stock_status = 2;
                if ($order_status_detail['stock_in_out'] == 1){
                    $rollback = $stock_status = 1;
                }
                if ($ord['data']['order_assign_type'] == 0) {
                    $st = $this->fx->stockOut($comp_id, $order_id, $conn, $rollback, 'IO');
                    if(empty($st['status'])) {
                        if($conn->inTransaction()) $conn->rollBack();
                        throw new \Exception($st['error']);
                    }
                }
                $stup = $this->updateOrder($comp_id, ['stock_status'=>$stock_status], ['id'=>':order_id'], [':order_id'=>$order_id], $conn);
                if(empty($stup['status'])) {
                    if($conn->inTransaction()) $conn->rollBack();
                    throw new \Exception($stup['error']);
                }
            }
            if(!empty($data['save_coupon_data'])) {
                $data['save_coupon_data']['order_id'] = $order_id;
                $cpnins = $this->insert($this->client_db_prefix.$comp_id . "." . $this->tables['coupon_used'], $data['save_coupon_data'], $conn);
                if(empty($cpnins['status'])) {
                    if($conn->inTransaction()) $conn->rollBack();
                    throw new \Exception($cpnins['error']);
                }
                $upd = $this->update($this->client_db_prefix.$comp_id . "." . $this->tables['coupon_list'], ['is_used'=>1], ['id'=>$data['order_coupon_id']], [],$conn);    
                if(empty($upd['status'])) {
                    if($conn->inTransaction()) $conn->rollBack();
                    throw new \Exception($upd['error']);
                }
            }
            if($conn->inTransaction()) $conn->commit();
            return ['status' => true, 'data' => $order_id];
        } 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 updateOrder($comp_id, Array $data, String|Array $condition, Array $params, $conn=NULL) {
        try {
            $rspupd = $this->update($this->client_db_prefix.$comp_id . "." . $this->tables['order'], $data, $condition, $params, $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 getOrderList($comp_id, $where, $page_no, $limit, $sortby) {
        try {
            $countrs = $this->callProcedure($this->procedures['GetOrderList'], ['comp_id'=>$comp_id, 'recpageno'=>$page_no, 'reclimit'=>$limit, 'sort_by'=>$sortby, 'searchstr'=>$where, 'all_fields'=>0, 'count'=>1], true);
            if (empty($countrs['status'])) {
                throw new \Exception($countrs['error']);
            }
            $count  = $countrs['data']['count'];
            if($count > 0) {
                $result = $this->callProcedure($this->procedures['GetOrderList'], ['comp_id'=>$comp_id, 'recpageno'=>$page_no, 'reclimit'=>$limit, 'sort_by'=>$sortby, 'searchstr'=>$where, 'all_fields'=>0, 'count'=>0]);
                if(empty($result['status'])) {
                    throw new \Exception($result['error']);
                }
                return ["status"=>true, "data"=>$result['data'], "count"=>$count, "page"=>$page_no, "page_limit"=>$limit];
            } else {
                return ["status"=>true, "data"=>[], "count"=>$count, "page"=>$page_no, "page_limit"=>$limit];
            }
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function getOrderDetail($comp_id, $where, $all_fields = 1, $single = 1) {
        try {
            return $this->callProcedure($this->procedures['GetOrderDetail'], ['comp_id'=>$comp_id,'searchstr'=>$where, 'all_fields'=>$all_fields, '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 getOrderItemList($comp_id, $where, $branch_id, $stock_status, $all_fields = 1) {
        try {
            return $this->callProcedure($this->procedures['GetOrderItemList'], ['comp_id'=>$comp_id, 'searchstr'=>$where, 'all_fields'=>$all_fields, 'branch_id'=>$branch_id, 'stock_status'=>$stock_status]);
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

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

    function getOrderLogList($comp_id, $where, Bool $single=false) {
        try {
            return $this->callProcedure($this->procedures['GetOrderLogList'], ['comp_id'=>$comp_id,'searchstr'=>$where, 'sort_by'=>'id', 'sort_type'=>'DESC'], $single);
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

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

    function getStatusDetails($comp_id, $where) {
        try {
            $sql  = "SELECT * ";
            $sql .= " FROM " . $this->client_db_prefix.$comp_id . "." . $this->tables['order_status'];
            $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 getNextOrderList($comp_id, $status_id, $for_invoce=false) {
        try {
            $where = "l.order_status_id=:status_id AND l.status=1";
            if($for_invoce) {
                $where .= " AND s.status_type IN(1,2,3,4,8,9) ";
            }
            $sql  = "SELECT l.*,s.status_name,s.color_code,s.status_type,s.stock_in_out ";
            $sql .= " FROM " . $this->client_db_prefix.$comp_id . "." . $this->tables['order_logic'] . " l ";
            $sql .= " LEFT JOIN " . $this->client_db_prefix.$comp_id . "." . $this->tables['order_status'] . " s ON(s.id=l.status_action_id) ";
            $sql .= " WHERE $where";
            $rsp = $this->fetchAllData($sql, [':status_id'=>$status_id]);
            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 leadList($comp_id, $order_id, $country_code, $mobile_no) {
        try {
            $where = "";
            $sql  = "SELECT t1.id, CONCAT('#',t1.id, ' on ', DATE_FORMAt(t1.created_at, '%d-%m-%Y')) AS lead_title ";
            $sql .= " FROM " . $this->client_db_prefix.$comp_id . "." . $this->tables['leads'] . " t1 ";
            $sql .= " WHERE (t1.contact_code=:country_code AND contact_no=:mobile_no) " ;
            $params = [':country_code'=>$country_code, ':mobile_no'=>$mobile_no];
            if(!empty($order_id)) {
                $sql .= " AND (t1.order_id=:order_id OR t1.order_id IS NULL OR t1.order_id = 0)";    
                $params = [':country_code'=>$country_code, ':mobile_no'=>$mobile_no, ':order_id'=>$order_id];
            }
            $rsp = $this->fetchAllData($sql, $params);
            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 addOrderCourierDetail($comp_id, $order, $data) {
        try {
            $conn = $this->conn();   
            $conn->beginTransaction();
            $rspupd = $this->update($this->client_db_prefix.$comp_id . "." . $this->tables['order'], $order, ['id'=>$data['order_id']], [], $conn);
            if(empty($rspupd['status'])) {
                if($conn->inTransaction()) $conn->rollBack();
                throw new \Exception($rspupd['error']);
            }
            $delrs = $this->delete($this->client_db_prefix.$comp_id . "." . $this->tables['order_courier'], ['order_id' => $data['order_id']], [], $conn);
            if(empty($delrs['status'])) {
                if($conn->inTransaction()) $conn->rollBack();
                throw new \Exception($delrs['error']);
            }
            $rspord = $this->insert($this->client_db_prefix.$comp_id . "." . $this->tables['order_courier'], $data, $conn);
            if(empty($rspord['status'])) {
                if($conn->inTransaction()) $conn->rollBack();
                throw new \Exception($rspord['error']);
            }
            if($conn->inTransaction()) $conn->commit();
            return ["status" => true, "data" => $rspord['insert_id']];
        } 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 assignUnassignOrder($comp_id, $order_data, $where, $order_assign_logs, $order_logs) {
        try {
            $conn = $this->conn();   
            $conn->beginTransaction();
            $rspupd = $this->update($this->client_db_prefix.$comp_id . "." . $this->tables['order'], $order_data, $where, [], $conn);
            if(empty($rspupd['status'])) {
                if($conn->inTransaction()) $conn->rollBack();
                throw new \Exception($rspupd['error']);
            }
            $assign_logs_ins = $this->batchInsert($this->client_db_prefix.$comp_id . "." . $this->tables['order_assign_logs'], $order_assign_logs, $conn);
            if(empty($assign_logs_ins['status'])) {
                if($conn->inTransaction()) $conn->rollBack();
                throw new \Exception($assign_logs_ins['error']);
            }
            $order_logs_ins = $this->batchInsert($this->client_db_prefix.$comp_id . "." . $this->tables['order_logs'], $order_logs, $conn);
            if(empty($order_logs_ins['status'])) {
                if($conn->inTransaction()) $conn->rollBack();
                throw new \Exception($order_logs_ins['error']);
            }
            if($conn->inTransaction()) $conn->commit();
            return ["status" => true, "data" => 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()];
        }
    }

    
}