<?php
if (!defined('BASEPATH'))
    exit('No direct script access allowed');
class Marketing_Loading_Model extends CI_Model
{
    private $tableName = 'marketing_loading';
    function __construct()
    {
        /* Call the Model constructor */
        parent::__construct();
    }
    public function get_all_vehicle()
    {
        $this->db->select('*');
        $this->db->order_by("vehicle_name", "desc");
        $this->db->where("vehicle_id IS NOT NULL");
        $this->db->where("vehicle_status", 1);
        $query = $this->db->get('vehicles');
        return $query->result_array();
    }
    function get_all_unloading_by_route_id($rote_id)
    {
        $this->db->select('id,loading_ref_no,loading_date_time');
        $this->db->from('marketing_loading_unloading');
        $this->db->where('status', "UNLOADING");
		$this->db->or_where('status', "LOADED_TO_LORRY");
        //$this->db->where('route_id', $rote_id);
        $query = $this->db->get();
        $data  = array();
        //$data[0] = 'SELECT'; 
        foreach ($query->result() as $row) {
            $data[$row->id] = $row->loading_ref_no . " / " . $row->loading_date_time;
        }
        return ($data);
    }
    function get_all_sample_issue_by_unloading_id($loading_id)
    {
        $this->db->select('srii.sales_rep_issue_id');
        $this->db->select('sales_rep_issues.sales_rep_issue_ref_no');
        $this->db->from('sales_rep_issue_items srii');
        $this->db->join('sales_rep_issues', 'srii.sales_rep_issue_id=sales_rep_issues.sales_rep_issue_id', 'inner');
        $this->db->where('srii.delivery_id', $loading_id);
		$this->db->where('srii.delivery_status',1);		
        $query = $this->db->get();
        $data  = array();
        foreach ($query->result() as $row) {
            $data[$row->sales_rep_issue_id] = $row->sales_rep_issue_ref_no;
        }
        return ($data);
    }
    function get_loading_details_by_loading_id($loading_id)
    {
        $this->db->select('marketing_loading_details.* ,');
        $this->db->select('sales_rep_issues.sales_rep_issue_ref_no,sales_rep_issues.document_ref_number');
        $this->db->from('marketing_loading_details');
        $this->db->join('sales_rep_issues ', 'marketing_loading_details.order_id = sales_rep_issues.sales_rep_issue_id ', 'left');
        $this->db->where('marketing_loading_details.loading_id', $loading_id);
        $this->db->group_by('marketing_loading_details.order_id');
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_loading_details_by_loading_id_print($loading_id)
    {
        $this->db->select('sales_rep_issue_items.product_id,sales_rep_issue_items.product_price,sales_rep_issue_items.salse_rep_issue_qty');
        $this->db->select('product.product_name,product.product_name,product.book_year');
        $this->db->from('marketing_loading_details');
        $this->db->join('sales_rep_issue_items ', 'marketing_loading_details.order_id = sales_rep_issue_items.sales_rep_issue_id ', 'left');
        $this->db->join('product', 'sales_rep_issue_items.product_id = product.product_id ', 'left');
        $this->db->where('marketing_loading_details.loading_id', $loading_id);
        //$this->db->group_by('sales_rep_issue_items.product_id');
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_loading_details($loading_id)
    {
        $this->db->select('marketing_loading.*');
        $this->db->select('vehicles.vehicle_code');
        $this->db->from('marketing_loading');
        $this->db->join('vehicles', 'marketing_loading.vehicle_id=vehicles.vehicle_id', 'left');
        $this->db->where('marketing_loading.id', $loading_id);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_all_routes()
    {
        $this->db->select('*');
        $this->db->order_by("route_name", "desc");
        $this->db->where("route_id IS NOT NULL"); //("id !=",$id);
        $query = $this->db->get('routes');
        return $query->result();
    }
    function get_all_areas()
    {
        $this->db->select('*');
        $this->db->order_by("area_name", "asc");
        $this->db->where("status", 1); //("id !=",$id);
        $query = $this->db->get('master_area');
        return $query->result();
    }
    public function get_next_ref_no()
    {
        $this->db->select_max('id');
        return $this->db->get('marketing_loading');
    }
    public function get_sales_suggestion($term)
    {
        $this->db->select('sales.*');
        $this->db->where("sale_reference_no LIKE '%$term' ");
        $this->db->order_by('sale_reference_no', 'asc');
        $this->db->limit(10, 0);
        $query = $this->db->get('sales');
        return $query->result_array();
    }
    public function get_return_sales_count_by_sales_id($sale_id)
    {
        $this->db->where('sale_id', $sale_id);
        $query = $this->db->get('sales_return');
        return $query->num_rows();
    }
    function finish_loading($loading_id)
    {
        $data = array(
            'status' => "LOADED"
        );
        $this->db->where('id', $loading_id);
        return $this->db->update('marketing_loading', $data);
    }
    function unloading($loading_id, $data)
    {
        $this->db->where('id', $loading_id);
        return $this->db->update('marketing_loading', $data);
    }
    function complete_unloading($loading_id, $data)
    {
        $this->db->where('id', $loading_id);
        return $this->db->update('marketing_loading', $data);
    }
    public function get_total_return_qty_in_sales_rep_issue_id($SRI_id)
    {
        $this->db->select_sum('sales_rep_retuned_qty');
        $this->db->from('sales_rep_issues_return_item');
        $this->db->where('sales_rep_id', $SRI_id);
        $query  = $this->db->get();
        $result = $query->row_array();
        return $result;
    }
    public function get_total_issue_qty_in_sales_rep_issue_id($SRI_id)
    {
        $this->db->select_sum('salse_rep_issue_qty');
        $this->db->from('sales_rep_issue_items');
        $this->db->where('sales_rep_issue_id', $SRI_id);
        $query  = $this->db->get();
        $result = $query->row_array();
        return $result;
    }
    public function get_total_cus_issue_qty_in_sales_rep_issue_id($SRI_id)
    {
        $this->db->select_sum('product_qty');
        $this->db->from('marketing_issue_customer');
        $this->db->where('sample_issue_id', $SRI_id);
        $query  = $this->db->get();
        $result = $query->row_array();
        return $result;
    }
    public function check_unloading($issue_card_id)
    {
        $this->db->join('sales_rep_issues', 'sales_rep_issues.sales_rep_issue_id=marketing_loading_details.order_id', 'left');
        $this->db->where('marketing_loading_details.loading_id', $issue_card_id);
        $this->db->where('sales_rep_issues.status', "UNLOADING");
        $query = $this->db->get('marketing_loading_details');
        return $query->num_rows();
    }
    public function check_loading($issue_card_id)
    {
        //$this->db->join('sales_rep_issues','sales_rep_issues.sales_rep_issue_id=marketing_loading_details.order_id','left');
        $this->db->where('marketing_loading_details.loading_id', $issue_card_id);
        //$this->db->where('sales_rep_issues.status',"UNLOADING");
        $query = $this->db->get('marketing_loading_details');
        return $query->num_rows();
    }
    public function get_total_return_qty_in_sales_rep_issue_id_product_id($SRI_id, $product_id)
    {
        $this->db->select_sum('sales_rep_retuned_qty');
        $this->db->from('sales_rep_issues_return_item');
        $this->db->where('sales_rep_id', $SRI_id);
        $this->db->where('product_id', $product_id);
        $query  = $this->db->get();
        $result = $query->row_array();
        return $result;
    }
    public function get_total_issue_qty_in_sales_rep_issue_id_product_id($SRI_id, $product_id)
    {
        $this->db->select_sum('salse_rep_issue_qty');
        $this->db->from('sales_rep_issue_items');
        $this->db->where('sales_rep_issue_id', $SRI_id);
        $this->db->where('product_id', $product_id);
        $query  = $this->db->get();
        $result = $query->row_array();
        return $result;
    }
    public function get_total_cus_issue_qty_in_sales_rep_issue_id_product_id($SRI_id, $product_id)
    {
        $this->db->select_sum('product_qty');
        $this->db->from('marketing_issue_customer');
        $this->db->where('sample_issue_id', $SRI_id);
        $this->db->where('product_id', $product_id);
        $query  = $this->db->get();
        $result = $query->row_array();
        return $result;
    }
    public function get_all_sales_rep_issue($loading_id)
    {
        $this->db->select('marketing_loading_details.*');
        $this->db->select('marketing_loading.status');
        $this->db->select('sales_rep_issues.sales_rep_issue_ref_no,sales_rep_issues.total_amount,sales_rep_issues.document_ref_number,sales_rep_issues.sales_rep_issue_date,sales_rep_issues.status AS SRI_status');
        $this->db->select('routes.route_name');
        $this->db->select('user.user_first_name');
        /*,user.user_first_name*/
        $this->db->from('marketing_loading_details');
        $this->db->join('marketing_loading', 'marketing_loading_details.loading_id=marketing_loading.id', 'left');
        $this->db->join('sales_rep_issues', 'marketing_loading_details.order_id=sales_rep_issues.sales_rep_issue_id', 'left');
        $this->db->join('routes', 'routes.route_id=marketing_loading.route_id', 'left');
        $this->db->join('user', 'user.user_id=sales_rep_issues.sales_rep_id', 'left');
        //$this->db->join('routes','routes.route_id=sales_rep_issues.route_id','left');
        $this->db->where('marketing_loading_details.loading_id', $loading_id);
        $this->db->where('marketing_loading.status', "UNLOADING");
        //$this->db->where('sales_rep_issues.status',"APPROVED");
        //$this->db->order_by('sales_rep_issues.sales_rep_issue_id','desc');
        $query = $this->db->get();
        return $query->result_array();
    }
    public function get_all_approved_issues_by_route_id($route_id)
    {
        $this->db->select('sales_rep_issues.sales_rep_issue_id,sales_rep_issues.sales_rep_issue_ref_no,sales_rep_issues.total_amount,comment,document_ref_number ');
        $this->db->from('sales_rep_issues');
        $this->db->where('sales_rep_issues.route_id', $route_id);
        $this->db->where('sales_rep_issues.status', "APPROVED");
        $this->db->where('sales_rep_issues.sales_rep_issue_id NOT IN (select order_id from marketing_loading_details)');
        $this->db->group_by('sales_rep_issues.sales_rep_issue_id');
        $query = $this->db->get();
        return $query->result_array();
    }
    public function get_all_active_loadings()
    {
        $this->db->select('marketing_loading.*,vehicles.vehicle_name');
        $this->db->from('marketing_loading');
        $this->db->join('vehicles', 'vehicles.vehicle_id=marketing_loading.vehicle_id', 'left');
        $this->db->where('marketing_loading.status != "PROCESS_DONE"');
        $this->db->order_by('marketing_loading.id', 'desc');
        $query = $this->db->get();
        return $query->result_array();
    }
    function save_issue_card($data, $issue_card_id = false)
    {
        if (!$issue_card_id) {
            $this->db->insert($this->tableName, $data);
        } else {
            $this->db->where('marketing_loading', $issue_card_id);
            return $this->db->update($this->tableName, $data);
        }
    }
    //Sales item save
    function save_issue_card_item($data_item)
    {
        $this->db->insert('marketing_loading_details', $data_item);
    }
    function get_issue_card_details_by_issue_card_id($issue_card_id)
    {
        $this->db->select('*');
        $this->db->from('issue_card');
        $this->db->where('issue_card_id', $issue_card_id);
        $query = $this->db->get();
        return $query->row_array();

    }
    function get_issue_card_item_list_by_issue_card_id($issue_card_id)
    {
        $this->db->select('icd.* ,SUM(sp.sale_pymnt_amount) sale_pymnt_amount');
        $this->db->select('s.sale_reference_no ,s.card_ref_number');
        $this->db->from('issue_card_details icd');
        $this->db->join('sale_payments sp', 'icd.sale_id = sp.sale_id ', 'left');
        $this->db->join('sales s', 'icd.sale_id = s.sale_id ', 'left');
        //$this->db->where('sp.sale_id = icd.sale_id');
        $this->db->where('icd.issue_card_id', $issue_card_id);
        $this->db->group_by('icd.sale_id');
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_invoice_item_list_by_route_id($route_id)
    {
        $this->db->select('sales.sale_id,sales.sale_reference_no,sales.sale_total,sum(sale_payments.sale_pymnt_amount)');
        $this->db->from('sale_payments');
        $this->db->join('sales', 'sales.sale_id = sale_payments.sale_id ', 'left');
        $this->db->where('route_id', $route_id);
        $this->db->group_by('sales.sale_id');
        $query = $this->db->get();
        return $query->result_array();
    }
}