<?php
if (!defined('BASEPATH'))
    exit('No direct script access allowed');
class Supplier_Multiple_Payment_Model extends CI_Model
{
    private $tableName = 'supplier_multiple_payment';
    function __construct()
    {
        parent::__construct();
    }
    
    	function get_all_bank_accounts($b_id='') {
		$this->db->select('bank_accounts'.'.*');
		$this->db->order_by("b_name", "asc");
	//	$this->db->where("id IS NOT NULL");//("id !=",$id);
	if($b_id){
	    $this->db->where('b_id', $b_id);
	}
			$this->db->where('b_status', 1);
		$query = $this->db->get('bank_accounts');
		return $query->result();
	}
	
    function get_all_list_supplier_multiple_payment()
    {
        $this->db->select('mp.*,c.*');
        $this->db->from('supplier_multiple_payment mp');
        $this->db->join('supplier c', 'c.supp_id = mp.supplier_id');
        $this->db->order_by("mp.smp_id", "desc");
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_supplier_multiple_payment_details_by_id($smp_id)
    {
        //$this->db->select('mp.*,c.supp_company_name,ba.*');
        $this->db->select('mp.*,c.supp_company_name');
        $this->db->from('supplier_multiple_payment mp');
        $this->db->join('supplier c', 'c.supp_id = mp.supplier_id');
       // $this->db->join('bank_accounts ba', 'mp.b_id = ba.b_id');
        $this->db->where("mp.smp_id", $smp_id);
        $this->db->order_by("mp.smp_id", "desc");
        $query = $this->db->get();
        //echo $this->db->last_query();
        return $query->row();
    }
    function get_supplier_multiple_payment_items_by_id($smp_id)
    {
        $this->db->select('p.*, s.*');
        $this->db->from('sale_payments p');
        $this->db->join('purchases s', 's.id = p.sale_id', 'left');
        $this->db->where("p.smp_id", $smp_id);
		$this->db->where("p.sale_payment_type", 'grn');
        $this->db->order_by("p.sale_pymnt_id", "asc");
        $query = $this->db->get();
        return $query->result();
    }
    
    function get_supplier_multiple_payment_grn_return_items_by_id($smp_id)
    {
        $this->db->select('sp.*, pr.*');
        $this->db->from('sale_payments sp');
        $this->db->join('purchase_return pr', 'sp.sale_id = pr.pur_rtn_id', 'left');
        $this->db->where("sp.smp_id", $smp_id);
		$this->db->where("sp.sale_payment_type", 'grn_return');
        $this->db->order_by("sp.sale_pymnt_id", "asc");
        $query = $this->db->get();
        return $query->result();
    }
    function get_next_ref_no()
    {
        $this->db->select_max('smp_last_id');
        $query = $this->db->get('supplier_multiple_payment');
        return $query->row();
    }
    function save_supplier_multiple_payment_header(&$supplier_data, $exp_id = false)
    {
        if (!$exp_id) {
            $smp_id = $this->db->insert('supplier_multiple_payment', $supplier_data);
            return $smp_id;
        } else {
            $this->db->where('smp_id', $exp_id);
            return $this->db->update($this->tableName, $supplier_data);
        }
    }
    function get_purchase_complete_info_by_customer_id($id)
    {
        $this->db->select('s.*,sum(sp.sale_pymnt_amount) as paid_amount,c.supp_company_name');
        $this->db->from('purchases s');
        $this->db->join('sale_payments sp', 's.id=sp.sale_id', 'left');
        $this->db->join('supplier c', 'c.supp_id=s.supplier_id', 'left');
        $this->db->where("s.supplier_id", $id);
        $this->db->order_by("s.id", "desc");
        $this->db->group_by("s.id");
        $query = $this->db->get();
        return $query->result();
    }
    function save_sale_multiple_payment(&$data_item)
    {
        if ($this->db->insert('sale_payments', $data_item)) {
            return true;
        } else {
            return false;
        }
    }
	function delete_supplier_multiple_payment_by_id($smp_id)
    {
		if($this->db->query('DELETE FROM sale_payments WHERE smp_id = '.$smp_id.' AND sale_payment_type=\'grn\''))
		if($this->db->query('DELETE FROM supplier_multiple_payment WHERE smp_id = '.$smp_id))
			return 1;
    }
	function get_purchase_complete_info_by_supplier_id($supplier_id,$srh_warehouse_id)
    {
        $this->db->select('s.*,sum(sp.sale_pymnt_amount) as paid_amount,c.supp_company_name');
        $this->db->from('purchases s');
        $this->db->join('sale_payments sp', 's.id=sp.sale_id', 'left');
        $this->db->join('supplier c', 'c.supp_id=s.supplier_id', 'left');
        $this->db->where("s.supplier_id", $supplier_id);
		if ($srh_warehouse_id) {
            $this->db->where("s.warehouse_id", $srh_warehouse_id);
        }
        $this->db->order_by("s.id", "asc");
        $this->db->group_by("s.id");
        $query = $this->db->get();
        return $query->result();
    }
    
    	function get_purchase_rtn_complete_info_by_supplier_id($supplier_id,$srh_warehouse_id)
    {
        $this->db->select('pr.*,sum(sp.sale_pymnt_amount) as paid_amount,s.supp_company_name');
        $this->db->from('purchase_return pr');
        $this->db->join('sale_payments sp', 'pr.pur_rtn_id=sp.sale_id', 'left');
        $this->db->join('supplier s', 'pr.supp_id=s.supp_id', 'left');
        $this->db->where("pr.supp_id", $supplier_id);
		if ($srh_warehouse_id) {
            $this->db->where("pr.warehouse_id", $srh_warehouse_id);
        }
        $this->db->order_by("pr.pur_rtn_id", "asc");
        $this->db->group_by("pr.pur_rtn_id");
        $query = $this->db->get();
        return $query->result();
    }
	function get_supplier_balance_by_supplier_id($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '',  $srh_supplier_id = '' , $srh_payment_term = '' , $ss_user_id = '' , $return_name = ''){
		
		$srh_type = 'grn';
		$return_name = 'total_payments';
		
		$total_purchases = $this->sum_of_purchases_by_supplier_id				($srh_warehouse_id , $srh_to_date , $srh_from_date , $srh_supplier_id );
			//echo '<pre>';print_r($total_purchases['grand_total']);echo '</pre>';
		$total_purchases_return = $this->sum_of_purchases_return_by_supplier_id	($srh_warehouse_id , $srh_to_date , $srh_from_date , $srh_supplier_id );
			//echo '<pre>';print_r($total_purchases_return['grand_total']);echo '</pre>';
		$total_payments	 = $this->sum_of_payments_by_supplier_id			($srh_warehouse_id , $srh_to_date , $srh_from_date , $srh_type 	  , $srh_supplier_id, $srh_payment_term , $ss_user_id  , $return_name );
			//echo '<pre>';print_r($total_payments['total_payments']);echo '</pre>';
			
			return $total_purchases['grand_total'] - $total_purchases_return['grand_total'] - $total_payments['total_payments'];
		}
	function sum_of_purchases_by_supplier_id($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '', $srh_supplier_id = '')
    {
        $this->db->select('SUM(`grand_total`) AS grand_total');
        $this->db->from('purchases');
        $this->db->where('purchases.supplier_id', $srh_supplier_id);
        if ($srh_warehouse_id) {
            $this->db->where("purchases.warehouse_id", $srh_warehouse_id);
        }
        if ($srh_to_date) {//echo $srh_to_date.'';
            $srh_to_date = date('Y-m-d', strtotime($srh_to_date . ""));
            $this->db->where("DATE( purchases.date ) <=", $srh_to_date);
        }
        if ($srh_from_date) {
            $srh_from_date = date('Y-m-d', strtotime($srh_from_date));
            $this->db->where("DATE( purchases.date ) >=", $srh_from_date);
        }
        $query  = $this->db->get();
		//echo $this->db->last_query();
        $result = $query->row_array();
        return $result;
    }
	function sum_of_purchases_return_by_supplier_id($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '', $srh_supplier_id = '')
    {
        /*
        $this->db->select('SUM(`grand_total`) AS grand_total');
        $this->db->from('purchase_return');
        $this->db->where('supplier_id', $srh_supplier_id);
        if ($srh_warehouse_id) {
            $this->db->where("purchase_return.warehouse_id", $srh_warehouse_id);
        }
        if ($srh_to_date) {//echo $srh_to_date.'';
            $srh_to_date = date('Y-m-d', strtotime($srh_to_date . ""));
            $this->db->where("DATE( purchases_return.date ) <=", $srh_to_date);
        }
        if ($srh_from_date) {
            $srh_from_date = date('Y-m-d', strtotime($srh_from_date));
            $this->db->where("DATE( purchases_return.date ) >=", $srh_from_date);
        }
        $query  = $this->db->get();
		//echo $this->db->last_query();
        $result = $query->row_array();
        return $result;
        */
    }
	//CRA001491
	function sum_of_payments_by_supplier_id($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '', $srh_type = '' , $srh_supplier_id = '', $srh_payment_term = '', $ss_user_id = '', $return_name = '')
    {
        $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('purchases b', 'b.id = p.sale_id', 'left');
        if ($ss_user_id)
            $this->db->join('user u', 'u.user_id = p.user_id', 'left');
        if ($srh_from_date) {
            $this->db->where("date( p.sale_pymnt_added_date_time ) >=", $srh_from_date);
        }
        /*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);
        } else*/ if ($srh_to_date) {
            $srh_to_date = date('Y-m-d', strtotime($srh_to_date . ""));
            $this->db->where("date( p.sale_pymnt_added_date_time ) <=", $srh_to_date);
        }
        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);
        }
		$this->db->where("p.sale_pymnt_paying_by != 'Return_Cash'");
        if ($ss_user_id) {
            $this->db->where("p.user_id", $ss_user_id);
        }
        if ($srh_supplier_id) {
           // $this->db->where(" b.supplier_id=" . $srh_supplier_id );
        } else if ($srh_warehouse_id) {
            $this->db->where("b.warehouse_id", $srh_warehouse_id);
        } else {
            //$this->session->userdata('ss_warehouse_id');
            //$srh_warehouse_id = $this->session->userdata('ss_warehouse_id');
            //$this->db->where("b.warehouse_id", $srh_warehouse_id);
        }
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row_array();
        } else {
        }
    }
    
    
     function get_supplier_multiple_payment_sup_adj_items_by_id($smp_id)
    {
        $this->db->select('sp.*, spa.*');
        $this->db->from('sale_payments sp');
        $this->db->join('supplier_payment_adjustment spa', 'sp.sale_id = spa.spa_id', 'left');
        $this->db->where("sp.smp_id", $smp_id);
		$this->db->where("sp.sale_payment_type", 'supplier_payment_adjustment');
        $this->db->order_by("sp.sale_pymnt_id", "asc");
        $query = $this->db->get();
        return $query->result();
    }
    
      public function get_sale_complete_info_by_customer_id($id)
    {
        $this->db->select('s.*,sum(sp.sale_pymnt_amount) as paid_amount,c.cus_name');
        $this->db->from('sales s');
        $this->db->join('sale_payments sp', 's.sale_id=sp.sale_id', 'left');
        $this->db->join('customer c', 'c.cus_id=s.customer_id', 'left');
        $this->db->where("s.customer_id", $id);
        $this->db->order_by("s.sale_id", "desc");
        $this->db->group_by("s.sale_id");
        $query = $this->db->get();
        return $query->result();
    }
}