<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Bank_model extends CI_Model {
    
    public function __construct() {
        parent::__construct();
        $this->db = $this->load->database('default', TRUE);
        $CI =& get_instance();
        $CI->db =& $this->db;
    }

    public function get_bank_list(){
        $query = 'select 
                    account_id,
                    account_name,
                    account_code,
                    account_number,
                    bank_name,
                    routing_number,
                    description,
                    is_primary,
                    status,
                    date_added,
                    sum(credit) credit,
                    sum(debit) debit,
                    sum(credit_bank_charges) credit_bank_charges 
                FROM
                (   
                    ( SELECT `bank_accounts`.*,0 as credit, sum(payment_received.amount) as debit, sum(payment_received.bank_charges) as credit_bank_charges from gst_bank_accounts bank_accounts left join gst_payment_received payment_received on bank_accounts.account_id = payment_received.deposit_to group by bank_accounts.account_id ) 
                UNION 
                    ( SELECT `bank_accounts`.*,sum(payment_made.amount) as credit,0 as debit,0 as credit_bank_charges from gst_bank_accounts bank_accounts left join gst_payment_made payment_made on bank_accounts.account_id = payment_made.paid_through group by bank_accounts.account_id)
                ) as tbl group by account_id';
    	return $this->db->query($query)->result();
        // echo "<pre>";print_r($data);
        // echo $this->db->last_query();die();
    }
	
	public function get_bank_deposit_details(){
        $query = 'select 
            gst_bank_accounts.account_id,if(gst_bank_accounts.account_id=ac1.from_acc_id,sum(ac1.amount),0) as debit,if(gst_bank_accounts.account_id=ac2.to_acc_id,sum(ac2.amount),0) as credit
        from 
            gst_bank_accounts
        left join
            gst_bank_diposit ac1
        on
            gst_bank_accounts.account_id = ac1.from_acc_id
        left join
            gst_bank_diposit ac2
        on
            gst_bank_accounts.account_id = ac2.to_acc_id
        group by
            gst_bank_accounts.account_id';
        return $this->db->query($query)->result();
    }
    
    
    public function get_bank_expance_details(){
        $query = 'select 
            gst_bank_accounts.account_id,if(gst_bank_accounts.account_id=ac1.paid_through_acc,sum(ac1.amount),0) as debit
        from 
            gst_bank_accounts
        left join
            gst_expences ac1
        on
            gst_bank_accounts.account_id = ac1.paid_through_acc        
        group by
            gst_bank_accounts.account_id';
        return $this->db->query($query)->result();
    }
    
    

    public function get_bank_details($bank_id){
    	return $this->db->select('*')
    					->from('bank_accounts')
    					->where('account_id',$bank_id)
    					->get()
    					->row();
    }

    public function insert_bank($bank_data){
    	$this->db->insert('bank_accounts',$bank_data);
    }
    
    public function import_product($product_data){
    	$this->db->insert('rugs_tmp',$product_data);
    }
	
	public function insert_bank_deposit($bank_data){
    	$this->db->insert('bank_diposit',$bank_data);
    }

    public function update_bank($bank_data,$bank_id){
    	$this->db->where('account_id',$bank_id);
    	$this->db->update('bank_accounts',$bank_data);
    }
}