<?php
if (!defined('BASEPATH'))
    exit('No direct script access allowed');
class Sales_Model extends CI_Model
{
    private $tableName = 'sales';
    function __construct()
    {
        /* Call the Model constructor */
        parent::__construct();
    }
    function get_all_sales_return_for_balance_report($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '', $srh_customer_id = '')
    {
        $this->db->select('sr.* , c.cus_name,SUM(p.sale_pymnt_amount) AS total_paid_amount');
        $this->db->from('sales_return sr');
        $this->db->join('customer c', 'sr.customer_id = c.cus_id', 'left');
        $this->db->join('sale_payments p', 'sr.sl_rtn_id = p.sale_id', 'left');
        if ($srh_customer_id) {
            $this->db->where("sr.customer_id", $srh_customer_id);
        }
        $this->db->where('sale_pymnt_paying_by !=', 'Cash');
        if ($srh_warehouse_id) {
            $this->db->where("sr.warehouse_id", $srh_warehouse_id);
        }
        if ($srh_from_date) {
            $this->db->where("sr.sl_rtn_datetime >=", $srh_from_date);
        }
        if ($srh_to_date) {
            $this->db->where("sr.sl_rtn_datetime <=", $srh_to_date);
        }
        $this->db->order_by("sr.sl_rtn_id", "desc");
        $this->db->group_by('sr.sl_rtn_id');
        $query = $this->db->get();
        return $query->result_array();
    }
    public function get_products_suggestions_get_by_delivery_id($term, $delivery_id, $werehouse_id)
    {
        $this->db->select('p.*');
        $this->db->from('product p');
        $this->db->join('lot_order_items li', 'li.product_id=p.product_id', 'inner');
        if ($term) {
            $this->db->where("li.delivery_id = '$delivery_id' AND li.delivery_status = '1' AND li.item_status = '1' AND li.is_delete_item = '0' AND p.product_status = '1' AND p.product_code LIKE '%$term%'");
            $this->db->or_where("li.delivery_id = '$delivery_id' AND li.delivery_status = '1' AND li.item_status = '1' AND li.is_delete_item = '0' AND p.product_status = '1' AND p.product_name LIKE '%$term%'");
        }
        $this->db->group_by('p.product_id');
        $this->db->limit(10, 0);
        $query = $this->db->get();
        return $query->result_array();
    }
    //get Sold Qty By WarehouseId
    public function getSoldQtyByWarehouseId($warehouse_id, $product_id, $srh_from_date = '', $srh_to_date = '')
    {
        $this->db->select_sum('si.quantity');
        $this->db->from('sale_items si');
        $this->db->join('sales s', 's.sale_id = si.sale_id', 'left');
        $this->db->where('s.warehouse_id', $warehouse_id);
        $this->db->where('si.product_id', $product_id);
        if ($srh_to_date) {
            $srh_to_date = date('Y-m-d', strtotime($srh_to_date . "+1 days"));
            $this->db->where("s.sale_datetime <=", $srh_to_date); //("id !=",$id);
        }
        if ($srh_from_date) {
            $this->db->where("s.sale_datetime >=", $srh_from_date); //("id !=",$id);
        }
        $query = $this->db->get();
        return $data['quantity'] = $query->row()->quantity;
    }
    function get_all_sales_for_sales_report($srh_warehouse_id, $srh_to_date, $srh_from_date, $srh_customer_id, $srh_payment_status)
    {
        $this->db->select('s.* , c.cus_name ,SUM(p.sale_pymnt_amount) AS total_paid_amount');
        $this->db->from('sales s');
        $this->db->join('customer c', 's.customer_id = c.cus_id', 'left');
        $this->db->join('sale_payments p', 's.sale_id = p.sale_id', 'left');
        $this->db->order_by("s.sale_id", "desc");
        $this->db->group_by('s.sale_id');
        if ($srh_warehouse_id) {
            $this->db->where("s.warehouse_id", $srh_warehouse_id);
        }
        if ($srh_to_date) {
            $srh_to_date = date('Y-m-d', strtotime($srh_to_date . "+1 days"));
            $this->db->where("s.sale_datetime <=", $srh_to_date);
        }
        if ($srh_from_date) {
            $this->db->where("s.sale_datetime >=", $srh_from_date);
        }
        if ($srh_customer_id) {
            $this->db->where("s.customer_id", $srh_customer_id);
        }
        if ($srh_from_date) {
            $this->db->limit($srh_from_date, $srh_to_date);
        }
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_all_sales_return_for_report($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '', $sale_id = '', $from = '', $to = '')
    {
        $this->db->select('sr.* , c.cus_name ,SUM(p.sale_pymnt_amount) AS total_paid_amount');
        $this->db->from('sales_return sr');
        $this->db->join('customer c', 'sr.customer_id = c.cus_id', 'left');
        $this->db->join('sale_payments p', 'sr.sl_rtn_id = p.sale_id', 'left');
        $this->db->where("p.sale_payment_type", 'sales_return');
        $this->db->order_by("sr.sl_rtn_id", "desc");
        $this->db->group_by('sr.sl_rtn_id');
        if ($srh_warehouse_id) {
            $this->db->where("sr.warehouse_id", $srh_warehouse_id);
        }
        if ($srh_to_date) {
            $srh_to_date = date('Y-m-d', strtotime($srh_to_date . "+1 days"));
            $this->db->where("sr.sl_rtn_datetime <=", $srh_to_date); //("id !=",$id);
        }
        if ($srh_from_date) {
            $this->db->where("sr.sl_rtn_datetime >=", $srh_from_date); //("id !=",$id);
        }
        if ($to) {
            $this->db->limit($to, $from);
        }
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_all_sales_return_for_report_hw($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '', $sale_id = '', $from = '', $to = '')
    {
        $this->db->select('sr.* , c.cus_name ,SUM(p.sale_pymnt_amount) AS total_paid_amount');
        $this->db->from('hw_sales_return sr');
        $this->db->join('customer c', 'sr.customer_id = c.cus_id', 'left');
        $this->db->join('sale_payments p', 'sr.sl_rtn_id = p.sale_id', 'left');
        $this->db->where("p.sale_payment_type", 'hw_sales_return');
        $this->db->order_by("sr.sl_rtn_id", "desc");
        $this->db->group_by('sr.sl_rtn_id');
        if ($srh_warehouse_id) {
            $this->db->where("sr.warehouse_id", $srh_warehouse_id);
        }
        if ($srh_to_date) {
            $srh_to_date = date('Y-m-d', strtotime($srh_to_date . "+1 days"));
            $this->db->where("sr.sl_rtn_datetime <=", $srh_to_date); //("id !=",$id);
        }
        if ($srh_from_date) {
            $this->db->where("sr.sl_rtn_datetime >=", $srh_from_date); //("id !=",$id);
        }
        if ($to) {
            $this->db->limit($to, $from);
        }
        $query = $this->db->get();
        return $query->result_array();
    }
    //Sales get information
    public function get_sale_info_by_customer_id($id)
    {
        $this->db->select('*');
        $this->db->from('sales');
        $this->db->where("customer_id", $id);
        $this->db->order_by("sale_id", "desc");
        $query = $this->db->get();
        return $query->result();
    }
    function get_all_sales_bu_cus_id($cus_id)
    {
        $this->db->select('sales.*, customer.cus_name');
        $this->db->from('customer');
        $this->db->join('sales', 'sales.customer_id = customer.cus_id', 'left');
        $this->db->order_by("sales.sale_id", "desc");
        $this->db->where("sales.customer_id", $cus_id);
        $query = $this->db->get();
        return $query->result();
    }
    function getPaymentsForPrint($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '', $srh_type = '', $srh_payment_term = '', $ss_user_id = '')
    {
        $sel = 'p.*,c.cus_name,b.*';
        if ($ss_user_id)
            $sel .= ',u.user_first_name';
        $this->db->select($sel);
        $this->db->from('sale_payments p');
        $this->db->join('hw_sales b', 'b.sale_id = p.sale_id', 'left');
        $this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
        $this->db->join('customer c', 'c.cus_id = b.customer_id', 'left');
        if ($ss_user_id)
            $this->db->join('user u', 'u.user_id = p.user_id', 'left');
        if ($srh_type) {
            $this->db->where("p.sale_payment_type", $srh_type); //
        }
        if ($srh_payment_term) {
            $this->db->where("p.sale_pymnt_paying_by", $srh_payment_term); //
        }
        if ($ss_user_id) {
            $this->db->where("p.user_id", $ss_user_id); //
        }
        if ($srh_warehouse_id) {
            $this->db->where("b.warehouse_id", $srh_warehouse_id); //
        }
        if ($srh_to_date) {
            $this->db->where("date(p.sale_pymnt_date_time) <=", $srh_to_date); //
        }
        if ($srh_from_date) {
            $this->db->where("date(p.sale_pymnt_date_time) >=", $srh_from_date); //
        }
        $this->db->order_by("b.sale_id", "desc");
        $query = $this->db->get();
        //echo $this->db->last_query();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    function getChequeForPrint($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '', $srh_type = '', $srh_payment_term = '', $ss_user_id = '')
    {
        $sel = 'p.*,c.cus_name,b.*';
        if ($ss_user_id)
            $sel .= ',u.user_first_name';
        $this->db->select($sel);
        $this->db->select('mb.bank_name');
        $this->db->from('sale_payments p');
        $this->db->join('sales b', 'b.sale_id = p.sale_id', 'left');
        $this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
        $this->db->join('customer c', 'c.cus_id = b.customer_id', 'left');
        $this->db->join('mstr_bank mb', 'mb.bank_id = p.bank_id', 'left');
        $this->db->where("p.sale_pymnt_paying_by", 'Cheque');
        if ($ss_user_id)
            $this->db->join('user u', 'u.user_id = p.user_id', 'left');
        if ($srh_type) {
            $this->db->where("p.sale_payment_type", $srh_type); //
        }
        if ($srh_payment_term) {
            $this->db->where("p.sale_pymnt_paying_by", $srh_payment_term); //
        }
        if ($ss_user_id) {
            $this->db->where("p.user_id", $ss_user_id); //
        }
        if ($srh_warehouse_id) {
            $this->db->where("b.warehouse_id", $srh_warehouse_id); //
        }
        if ($srh_to_date) {
            $this->db->where("p.sale_pymnt_added_date_time <=", $srh_to_date); //("id !=",$id);
        }
        if ($srh_from_date) {
            $this->db->where("p.sale_pymnt_added_date_time >=", $srh_from_date); //("id !=",$id);
        }
        $this->db->order_by("b.sale_id", "desc");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    function getHwChequeForPrint($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '', $cheque_date='', $cheque_no = '')
    {
        $this->db->select('p.*,c.cus_name,b.*');
        $this->db->select('mb.bank_name');
        $this->db->from('sale_payments p');
        $this->db->join('hw_sales b', 'b.sale_id = p.sale_id', 'left');
        $this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
        $this->db->join('customer c', 'c.cus_id = b.customer_id', 'left');
        $this->db->join('mstr_bank mb', 'mb.bank_id = p.bank_id', 'left');
        $this->db->where("p.sale_pymnt_paying_by", 'Cheque');
        $this->db->where("p.sale_payment_type", "hw_sale"); //
        
        if ($srh_warehouse_id) {
            $this->db->where("b.warehouse_id", $srh_warehouse_id); //
        }
        if ($srh_to_date) {
            $this->db->where("date(p.sale_pymnt_added_date_time) <=", $srh_to_date); //("id !=",$id);
        }
        if ($srh_from_date) {
            $this->db->where("date(p.sale_pymnt_added_date_time) >=", $srh_from_date); //("id !=",$id);
        }
        if ($cheque_date) {
            $this->db->where("date(p.sale_pymnt_cheque_date) ", $cheque_date); //("id !=",$id);
        }
        if ($cheque_no) {
            $this->db->where("p.sale_pymnt_cheque_no", $cheque_no); //("id !=",$id);
        }
        $this->db->order_by("b.sale_id", "desc");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    //Sales best for dashboard
    function getBestSales($year = null, $month = 0, $from = 0, $to = 0)
    {
        $this->db->select('SUM(ft.fi_qty)AS fi_qty_tot,p.product_name,p.product_code,p.product_part_no,p.product_oem_part_number');
        $this->db->from('fi_table ft');
        $this->db->join('product p', 'ft.fi_item_id = p.product_id', 'left');
        $this->db->where('ft.fi_type_id', 'sale');
        if ($month) {
            $this->db->where('MONTH(ft.fi_date_time)', $month, FALSE);
        }
        if ($year) {
            $this->db->where('YEAR(ft.fi_date_time)', $year, FALSE);
        }
        if ($to) {
            $this->db->limit($to, $from);
        }
        $this->db->order_by("fi_qty_tot", "desc");
        $this->db->group_by('ft.fi_item_id');
        $query = $this->db->get();
        return $query->result();
    }
    //Sales genarate referance number
    function get_next_ref_no()
    {
        $this->db->select_max('sale_id');
        return $this->db->get('sales');
    }
    //Sales get avalable product qty
    function get_avalable_product_qty($product_id, $warehouse_id)
    {
        $this->db->select_sum('fi_qty');
        $query = $this->db->get('fi_table');
        return $query->row()->fi_qty;
    }
    //Sales get toatal paid 
    function get_total_paid_by_sale_id($sale_id)
    {
        $this->db->select_sum('sale_pymnt_amount');
        $this->db->from('sale_payments');
        $this->db->where("sale_id", $sale_id);
        $this->db->where("sale_pymnt_paying_by != 'Cheque_Return'");
        $this->db->where("sale_payment_type", "sale");
        $query = $this->db->get();
        if ($query->row()->sale_pymnt_amount) {
            return $query->row()->sale_pymnt_amount;
        } else {
            return 0;
        }
    }
    function get_total_paid_by_sale_id_hw($sale_id)
    {
        $this->db->select_sum('sale_pymnt_amount');
        $this->db->from('sale_payments');
        $this->db->where("sale_id", $sale_id);
        $this->db->where("sale_pymnt_paying_by != 'Cheque_Return'");
        $this->db->where("sale_payment_type", "sale");
        $query = $this->db->get();
        if ($query->row()->sale_pymnt_amount) {
            return $query->row()->sale_pymnt_amount;
        } else {
            return 0;
        }
    }
    //Sales get information
    public function get_sale_info($id)
    {
        $this->db->select('s.*');
        $this->db->select('it.invoice_type_name');
        $this->db->select('v.vehicle_code,v.vehicle_name');
        $this->db->select('du.user_first_name as driver_first_name,du.user_last_name as driver_last_name   ,du.invoice_name as driver_tpno');
        $this->db->select('u.user_first_name,u.user_last_name,u.invoice_name');
        $this->db->from('sales s');
        $this->db->join('user u', 's.sales_rep_id=u.user_id', 'left');
        $this->db->join('marketing_loading_unloading ml', 's.delivery_id=ml.id', 'left');
        $this->db->join('vehicles v', 'ml.lorry_id=v.vehicle_id', 'left');
        $this->db->join('user du', 'ml.rep_id=du.user_id', 'left');
        $this->db->join('master_invoice_type it', 's.invoice_type=it.invoice_type_id', 'left');
        $this->db->where("s.sale_id", $id);
        $query = $this->db->get();
        return $query->row_array();
    }
    public function get_add_sale_info($id)
    {
        $this->db->select('*');
        $this->db->from('sales_additional_details');
        $this->db->where("sale_id", $id);
        $query = $this->db->get();
        return $query->row_array();
    }
    //Sales item list get by id
    public function get_sale_item_list_by_sale_id($sale_id)
    {
        $this->db->select('sale_items.*, product.product_name, product.product_code,product.product_part_no,product.product_oem_part_number');
        $this->db->from('sale_items');
        $this->db->join('product', 'sale_items.product_id = product.product_id', 'left');
        $this->db->order_by("sale_items.id", "desc");
        $this->db->where("sale_items.sale_id", $sale_id); //("id !=",$id);
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_count($CRN)
    {
        $this->db->select('COUNT(*) as counts');
        $this->db->from('sales');
        $this->db->where("card_ref_number", $CRN);
        $query = $this->db->get();
        return $query;
    }
    //Sales save
    function save_sales(&$supplier_data, $sale_id = false)
    {
        if (!$sale_id) {
            $this->db->insert($this->tableName, $supplier_data);
            return $this->db->_error_message();
        } else {
            $this->db->where('sale_id', $sale_id);
            return $this->db->update($this->tableName, $supplier_data);
        }
    }
    //Sales item save
    function save_sales_item(&$data_item)
    {
        $this->db->insert('sale_items', $data_item);
    }
    //Sales get for report
    function get_all_sales_for_report($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '', $sale_id = '', $from = '', $to = '', $srh_customer_id = '')
    {
        $this->db->select('s.* , c.cus_name ,SUM(p.sale_pymnt_amount) AS total_paid_amount');
        $this->db->from('sales s');
        $this->db->join('customer c', 's.customer_id = c.cus_id', 'left');
        $this->db->join('sale_payments p', 's.sale_id = p.sale_id', 'left');
        $this->db->order_by("s.sale_id", "desc");
        $this->db->group_by('s.sale_id');
        //$this->db->where("p.sale_payment_type",'sale');
        if ($srh_warehouse_id) {
            $this->db->where("s.warehouse_id", $srh_warehouse_id); //("id !=",$id);
        }
        if ($srh_to_date) {
            $srh_to_date = date('Y-m-d', strtotime($srh_to_date . "+1 days"));
            $this->db->where("s.sale_datetime <=", $srh_to_date);
        }
        if ($srh_from_date) {
            $this->db->where("s.sale_datetime >=", $srh_from_date); //("id !=",$id);
        }
        if ($sale_id) {
            $this->db->where("s.sale_id =", $sale_id); //("id !=",$id);
        }
        if ($srh_customer_id) {
            $this->db->where("s.customer_id", $srh_customer_id); //("id !=",$id);
        }
        if ($to) {
            $this->db->limit($to, $from);
        }
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_all_sales_for_report_hw($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '', $sale_id = '', $from = '', $to = '', $srh_customer_id = '')
    {
        $this->db->select('s.* , c.cus_name ,SUM(p.sale_pymnt_amount) AS total_paid_amount');
        $this->db->from('hw_sales s');
        $this->db->join('customer c', 's.customer_id = c.cus_id', 'left');
        $this->db->join('sale_payments p', 's.sale_id = p.sale_id', 'left');
        $this->db->order_by("s.sale_id", "desc");
        $this->db->group_by('s.sale_id');
        $this->db->where("p.sale_payment_type", 'hw_sale');
        if ($srh_warehouse_id) {
            $this->db->where("s.warehouse_id", $srh_warehouse_id); //("id !=",$id);
        }
        if ($srh_to_date) {
            $srh_to_date = date('Y-m-d', strtotime($srh_to_date . "+1 days"));
            $this->db->where("s.sale_datetime <=", $srh_to_date);
        }
        if ($srh_from_date) {
            $this->db->where("s.sale_datetime >=", $srh_from_date); //("id !=",$id);
        }
        if ($sale_id) {
            $this->db->where("s.sale_id =", $sale_id); //("id !=",$id);
        }
        if ($srh_customer_id) {
            $this->db->where("s.customer_id", $srh_customer_id); //("id !=",$id);
        }
        if ($to) {
            $this->db->limit($to, $from);
        }
        $query = $this->db->get();
        return $query->result_array();
    }
    //Sales all get
    public function get_all_sales($start, $length, $search, $direct_sale = "")
    {
        $this->db->select('sales.*, customer.cus_name');
        $this->db->from('sales');
        $this->db->join('customer', 'sales.customer_id = customer.cus_id', 'left');
        if (isset($search['value']))
            if ($search['value']) {
                $this->db->like('sales.card_ref_number', $search['value']);
                $this->db->or_like('sales.sale_reference_no', $search['value']);
                $this->db->or_like('customer.cus_name', $search['value']);
            }
        //echo $direct_sale;
        if ($direct_sale == 1) {
            $this->db->where("sales.direct_sale", "1");
        } else
            $this->db->where("sales.direct_sale", "0");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
        }
        $this->db->order_by("sales.sale_datetime", "desc");
        //echo $this->db->last_query();echo "|";
        if ($start != '' && $length != '') {
            $query = $this->db->get();
            return $query->result_array();
        } else {
            $query = $this->db->get();
            return $query->num_rows();
        }
    }
    //Sales get for print
    function get_all_sales_for_print_sales()
    {
        $this->db->select('s.* , c.cus_name ,SUM(p.sale_pymnt_amount) AS total_paid_amount');
        $this->db->from('sales s');
        $this->db->join('customer c', 's.customer_id = c.cus_id', 'left');
        $this->db->join('sale_payments p', 's.sale_id = p.sale_id', 'left');
        $this->db->order_by("s.sale_id", "desc");
        $this->db->group_by('s.sale_id');
        $this->db->where("s.sale_id IS NOT NULL"); //("id !=",$id);
        $query = $this->db->get();
        return $query->result_array();
    }
    //Sales payment get 
    function get_sale_payments_by_sale_id($sale_id)
    {
        $this->db->select('sale_payments.*,user.user_first_name,user_group.user_group_name');
        $this->db->from('sale_payments');
        $this->db->join('user', 'sale_payments.user_id = user.user_id', 'left');
        $this->db->join('user_group', 'user.group_id = user_group.user_group_id', 'left');
        $this->db->order_by("sale_payments.sale_pymnt_id", "desc");
        $this->db->where("sale_payments.sale_id", $sale_id);
        $this->db->where("sale_payments.sale_payment_type", 'sale');
        $query = $this->db->get();
        return $query->result();
    }
    //Get product sujetions
    function get_products_suggestions($term)
    {
        $this->db->select('product' . '.*');
        $this->db->order_by("product_name", "asc");
        $this->db->where("product_name LIKE '%$term%' OR product_code LIKE '%$term%' OR product_oem_part_number LIKE '%$term%' OR product_part_no LIKE '%$term%'");
        $this->db->limit(10, 0);
        $query = $this->db->get('product');
        return $query->result_array();
    }
    function get_all_products()
    {
        $this->db->select('product' . '.*');
        $this->db->order_by("product_name", "asc");
        $this->db->where("product_id IS NOT NULL");
        $query = $this->db->get('product');
        return $query->result_array();
    }
    function get_products_suggestions_get_by_sales_id_for_return($term, $sales_id)
    {
        $this->db->select('sale_items.*');
        $this->db->select('product.product_name,product.product_code,product.product_oem_part_number,product.product_part_no');
        $this->db->from('sale_items');
        $this->db->join('product', 'sale_items.product_id = product.product_id', 'left');
        $this->db->where("sale_items.sale_id", $sales_id);
        $this->db->where("product.product_name LIKE '%$term%'");
        $this->db->or_where("sale_items.sale_id", $sales_id);
        $this->db->where("product.product_code LIKE '%$term%'");
        $this->db->or_where("sale_items.sale_id", $sales_id);
        $this->db->where("product.product_oem_part_number LIKE '%$term%'");
        $this->db->or_where("sale_items.sale_id", $sales_id);
        $this->db->where("product.product_part_no LIKE '%$term%'");
        $this->db->limit(10, 0);
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_products_suggestions_get_by_sales_id_for_return_hw($term, $sales_id)
    {
        $this->db->select('hw_sale_items.*');
        $this->db->select('product.product_name,product.product_code,product.product_oem_part_number,product.product_part_no');
        $this->db->from('hw_sale_items');
        $this->db->join('product', 'hw_sale_items.product_id = product.product_id', 'left');
        $this->db->where("hw_sale_items.sale_id", $sales_id);
        $this->db->where("product.product_name LIKE '%$term%'");
        $this->db->or_where("hw_sale_items.sale_id", $sales_id);
        $this->db->where("product.product_code LIKE '%$term%'");
        $this->db->or_where("hw_sale_items.sale_id", $sales_id);
        $this->db->where("product.product_oem_part_number LIKE '%$term%'");
        $this->db->or_where("hw_sale_items.sale_id", $sales_id);
        $this->db->where("product.product_part_no LIKE '%$term%'");
        $this->db->limit(10, 0);
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_sale_paynment_total_info($sale_id)
    {
        $sale_payment_type = "sale";
        $this->db->select_sum('sale_pymnt_amount');
        $this->db->from('sale_payments');
        $this->db->where("sale_payment_type", $sale_payment_type);
        $this->db->where("sale_id", $sale_id);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_sum_of_return_received_item_qty($product_id, $sale_id)
    {
        $this->db->select_sum('quantity');
        $this->db->from('sales_return_items');
        $this->db->join('sales_return', 'sales_return_items.sl_rtn_id = sales_return.sl_rtn_id', 'left');
        $this->db->where("sales_return.sale_id", $sale_id);
        $this->db->where("sales_return_items.product_id", $product_id);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_sum_of_return_received_item_qty_hw($product_id, $sale_id)
    {
        $this->db->select_sum('quantity');
        $this->db->from('hw_sales_return_items');
        $this->db->join('hw_sales_return', 'hw_sales_return_items.sl_rtn_id = hw_sales_return.sl_rtn_id', 'left');
        $this->db->where("hw_sales_return.sale_id", $sale_id);
        $this->db->where("hw_sales_return_items.product_id", $product_id);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_sum_of_invoiced_item_qty($product_id, $rep_issue_id, $werehouse_id)
    {
        $this->db->select_sum('quantity');
        $this->db->from('sale_items');
        $this->db->join('sales', 'sale_items.sale_id = sales.sale_id ', 'left');
        $this->db->where("sales.warehouse_id", $werehouse_id);
        $this->db->where("sale_items.sales_rep_issue_id", $rep_issue_id);
        $this->db->where("sale_items.product_id", $product_id);
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_sum_of_invoiced_item_qty_old($product_id, $rep_issue_id, $werehouse_id)
    {
        $this->db->select_sum('quantity');
        $this->db->from('sale_items');
        $this->db->join('sales', 'sale_items.sale_id = sales.sale_id ', 'left');
        $this->db->where("sales.warehouse_id", $werehouse_id);
        $this->db->where("sales.sales_rep_issue_id", $rep_issue_id);
        $this->db->where("sale_items.product_id", $product_id);
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_sum_of_invoiced_item_qty_by_sale_id($product_id, $sale_id)
    {
        $this->db->select_sum('quantity');
        $this->db->from('sale_items');
        $this->db->join('sales', 'sale_items.sale_id = sales.sale_id ', 'left');
        $this->db->where("sales.sales_rep_id", $sale_id);
        $this->db->where("sale_items.product_id", $product_id);
        $query = $this->db->get();
        return $query->result_array();
    }
    //Sales payment save
    function save_sale_payments(&$data, $sale_pymnt_id = false)
    {
        if (!$sale_pymnt_id) {
            return $this->db->insert('sale_payments', $data);
        } else {
            $this->db->where('supp_id', $sale_pymnt_id);
            return $this->db->update('sale_payments', $data);
        }
    }
    public function get_all_sales_item_by_sale_id($sales_id)
    {
        $this->db->select('sale_items.id, sale_items.product_id , product.product_code , product.product_name ,sale_items.unit_price ,product.product_oem_part_number ,product.product_part_no ,sale_items.item_cost ,sale_items.quantity');
        $this->db->from('sale_items');
        $this->db->join('product', 'sale_items.product_id = product.product_id ', 'left');
        $this->db->join('sales', 'sales.sale_id = sale_items.sale_id ', 'left');
        $this->db->where('sales.sale_id', $sales_id);
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_lorry_details_by_delivery_id($delivery_id)
    {
        $this->db->select('vehicle_name');
        $this->db->from('vehicles');
        $this->db->join('marketing_loading_unloading', 'vehicles.vehicle_id = marketing_loading_unloading.lorry_id ', 'left');
        $this->db->where('marketing_loading_unloading.id', $delivery_id);
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_rep_name_by_rep_id($id)
    {
        $this->db->select('user_first_name,user_last_name');
        $this->db->from("user");
        $this->db->where("user_id", $id);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_cus_tot_credit($cus_id)
    {
        $this->db->select_sum('s.sale_total');
        $this->db->from('sales s');
        //$this->db->join('sales s', 's.sale_id = si.sale_id', 'left');
        $this->db->where('s.customer_id', $cus_id);
        $query = $this->db->get();
        return $query->row()->sale_total;
    }
    function save_sales_additional($data)
    {
        return $this->db->insert('sales_additional_details', $data);
    }
    function get_sale_master_details($sale_id)
    {
        $this->db->select('s.*');
        $this->db->from('sales s');
        $this->db->where('s.sale_id', $sale_id);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_sale_item_details($sale_id)
    {
        $this->db->select('si.*');
        $this->db->from('sale_items si');
        $this->db->where('si.sale_id', $sale_id);
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_product_cash_price($product_id)
    {
        $this->db->select('p.product_price');
        $this->db->from('product p');
        $this->db->where('p.product_id', $product_id);
        $query = $this->db->get();
        $data  = $query->row_array();
        if ($data['product_price']) {
            return $data['product_price'];
        } else {
            return 1;
        }
    }
    function get_product_credit_price($product_id)
    {
        $this->db->select('p.credit_salling_price');
        $this->db->from('product p');
        $this->db->where('p.product_id', $product_id);
        $query = $this->db->get();
        $data  = $query->row_array();
        if ($data['credit_salling_price']) {
            return $data['credit_salling_price'];
        } else {
            return 1;
        }
    }
    function update_sale_item_by_sale_item_id($id = '', $data)
    {
        if ($id > 0) {
            $this->db->where('id', $id);
            return $this->db->update('sale_items', $data);
        } else {
            return 0;
        }
    }
    function return_cheque($id = '', $data)
    {
        if ($id > 0) {
            $this->db->where('sale_pymnt_id', $id);
            return $this->db->update('sale_payments', $data);
        } else {
            return 0;
        }
    }
    function get_all_sum_sales_for_summery_report($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '', $sale_id = '', $from = '', $to = '', $srh_customer_id = '')
    {
        $this->db->select('SUM(`sale_total`) AS sale_total');
        $this->db->from('sales');
        //                    $this->db->join('customer','customer.cus_id = sales.customer_id');
        //                    $this->db->join('sales_return','customer.cus_id = sales_return.customer_id');
        $this->db->where('sales.customer_id', $srh_customer_id);
        if ($srh_warehouse_id) {
            $this->db->where("sales.warehouse_id", $srh_warehouse_id); //("id !=",$id);
        }
        if ($srh_to_date) {
            $srh_to_date = date('Y-m-d', strtotime($srh_to_date . ""));
            $this->db->where("sales.sale_datetime <=", $srh_to_date);
        }
        if ($srh_from_date) {
            $srh_from_date = date('Y-m-d', strtotime($srh_from_date));
            $this->db->where("sales.sale_datetime >=", $srh_from_date); //("id !=",$id);
        }
        $query  = $this->db->get();
        //print_r($this->db->last_query());
        $result = $query->result_array();
        return $result;
    }
    function get_sum_sales_return($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '', $sale_id = '', $from = '', $to = '', $srh_customer_id = '')
    {
        $this->db->select('SUM(`sl_rtn_total`) AS sale_return_total');
        $this->db->from('sales_return');
        $this->db->where('customer_id', $srh_customer_id);
        if ($srh_warehouse_id) {
            $this->db->where("sales_return.warehouse_id", $srh_warehouse_id); //("id !=",$id);
        }
        if ($srh_to_date) {
            $srh_to_date = date('Y-m-d', strtotime($srh_to_date . ""));
            $this->db->where("sales_return.sl_rtn_datetime <=", $srh_to_date);
        }
        if ($srh_from_date) {
            $srh_from_date = date('Y-m-d', strtotime($srh_from_date));
            $this->db->where("sales_return.sl_rtn_datetime >=", $srh_from_date); //("id !=",$id);
        }
        $query  = $this->db->get();
        $result = $query->row_array();
        return $result;
    }
    function getSumPaymentsForBalanceRep($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '', $srh_type = '', $srh_payment_term = '', $ss_user_id = '', $srh_customer_id = '', $return_name = '')
    {
        $warehouse_id = '';
        $sel          = 'SUM(p.sale_pymnt_amount) AS ' . $return_name . '';
        if ($ss_user_id)
            $sel .= ',u.user_first_name';
        $this->db->select($sel);
        $this->db->from('sale_payments p');
        $this->db->join('sales b', 'b.sale_id = p.sale_id', 'left');
        //       $this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
        //       $this->db->join('customer c', 'c.cus_id = b.customer_id', 'left');
        //       $this->db->where('sale_pymnt_paying_by !=','Cheque_Return');
        if ($ss_user_id)
            $this->db->join('user u', 'u.user_id = p.user_id', 'left');
        if ($srh_from_date) {
            $this->db->where("p.sale_pymnt_added_date_time >=", $srh_from_date); //("id !=",$id);
        }
        if ($return_name == 'tot_sp') {
            $this->db->where('sale_pymnt_paying_by !=', 'Cheque_Return');
        }
        if ($return_name == 'unrealized_chq') {
            $srh_to_date = date('Y-m-d', strtotime($srh_to_date . ""));
            $this->db->where("p.sale_pymnt_date_time >=", $srh_to_date); //("id !=",$id);            
        } else if ($srh_to_date) {
            $srh_to_date = date('Y-m-d', strtotime($srh_to_date . ""));
            $this->db->where("p.sale_pymnt_added_date_time <=", $srh_to_date); //("id !=",$id);
        }
        if ($srh_type) {
            $this->db->where("p.sale_payment_type", $srh_type); //
        }
        if ($srh_payment_term) {
            $this->db->where("p.sale_pymnt_paying_by", $srh_payment_term); //
        }
        if ($ss_user_id) {
            $this->db->where("p.user_id", $ss_user_id); //
        }
        if ($srh_customer_id) {
            $this->db->where("b.warehouse_id =" . $srh_warehouse_id . " AND b.customer_id=" . $srh_customer_id . ""); //      
        } else if ($srh_warehouse_id) {
            $this->db->where("b.warehouse_id", $srh_warehouse_id); //      
        } else {
            $this->session->userdata('ss_warehouse_id');
            $warehouse_id = $this->session->userdata('ss_warehouse_id');
            //        echo $warehouse_id;
            $this->db->where("b.warehouse_id", $warehouse_id);
            //        echo "INN";
        }
        /*
        if($srh_to_date){
        $this->db->where("p.sale_pymnt_added_date_time <=",$srh_to_date);//("id !=",$id);
        }
        if($srh_from_date){
        $this->db->where("p.sale_pymnt_added_date_time >=",$srh_from_date);//("id !=",$id);
        }*/
        //       $this->db->order_by("b.sale_id", "desc");
        $query = $this->db->get();
        // echo $this->db->last_query();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            //return false;
        }
    }
    //-----------------------------------------------------------------------------
    function get_sale_payment_cus_id($py_id)
    {
        $this->db->select('s.customer_id');
        $this->db->from('sale_payments p');
        $this->db->join('sales s', 's.sale_id = p.sale_id', 'left');
        $this->db->where("p.sale_pymnt_id", $py_id);
        $query  = $this->db->get();
        $result = $query->row_array();
        return $result['customer_id'];
    }
    function get_direct_sale_qty($warehouse_id = '', $product_id, $srh_to_date = '', $srh_from_date = '')
    {
        $this->db->select_sum('si.quantity');
        $this->db->from('sale_items si');
        $this->db->join('sales s', 's.sale_id = si.sale_id', 'left');
        $this->db->where('s.direct_sale', 1);
        if ($warehouse_id)
            $this->db->where('s.warehouse_id', $warehouse_id);
        $this->db->where('si.product_id', $product_id);
        if ($srh_to_date) {
            $srh_to_date = date('Y-m-d', strtotime($srh_to_date . "+1 days"));
            $this->db->where("s.sale_datetime <=", $srh_to_date); //("id !=",$id);
        }
        $date          = date_create("2020-05-29");
        $srh_from_date = date_format($date, "Y-m-d");
        $this->db->where("s.sale_datetime >=", $srh_from_date); //("id !=",$id);
        $query = $this->db->get();
        return $data['quantity'] = $query->row()->quantity;
    }
}