<?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 getimportdata($token){
    	$query = 'select 
            *
        from 
            import_products
        left join
            vendors
        on
            import_products.vendor = vendors.id
        where
            import_products.id = '.$token ;
        
        return $this->db->query($query)->row();
    }

    public function update_product($product_update_data,$photo_id){
        $this->db->where('photo_id',$photo_id);
    	$this->db->update('rugs_tmp',$product_update_data);
    }  


    public function updateimportdata($token){
    	$query = "update
            import_products
        set
            status = '2'
        where
            id = ".$token ;
        
        return $this->db->query($query);
    }
    
    
    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 update_product_surya($product_update_data,$photo_id,$vendor){
        $this->db->where('vendor', $vendor);
        $this->db->like('photo_id', $photo_id, 'after');
    	$this->db->update('rugs_tmp',$product_update_data);

        //print_r($this->db->last_query());
        //exit();
    }

    public function comm_product_active(){
        return $this->db->select('photo_id,sector,variations')
    					->from('rugs_tmp')
    					->where('isResidential',1)
                        ->where('new_arrival !=',2)
                        ->where('vendor','Christopher Fareed Design Studios')
                        ->where('variations !=','')
    					->get()
    					->result_array();
    }
    
    public function product_is_available($photo_id,$vendor){
        $this->db->select('*')->from('rugs_tmp');
        $this->db->where('vendor', $vendor);
        $this->db->like('photo_id', $photo_id, 'after');
        return $this->db->get()->row();
    }
    
    public function import_product($product_data){
    	$this->db->insert('rugs_tmp',$product_data);
    }

    public function updatemap($sizesku,$vendorsku,$map,$msrp,$upc){

        $query = "SELECT count(*) as rowcount FROM `rugs_tmp` WHERE JSON_SEARCH(`variations`,'one', '".$upc."') IS NOT NULL AND vendor_sku = '".$vendorsku."'";
        $count_row = $this->db->query($query)->row();;

        if($count_row->rowcount > 0)
        {
            $query = "UPDATE rugs_tmp SET `variations` = JSON_REPLACE(`variations`, CONCAT(REPLACE(SUBSTRING_INDEX(JSON_SEARCH(`variations`, 'one', '".$upc."'),'.',1),'\"',''),'.price'), ".$map.") WHERE vendor_sku = '".$vendorsku."'";    	
            $this->db->query($query);
            $query = "UPDATE rugs_tmp SET `variations` = JSON_REPLACE(`variations`, CONCAT(REPLACE(SUBSTRING_INDEX(JSON_SEARCH(`variations`, 'one', '".$upc."'),'.',1),'\"',''),'.MSRP'), ".$msrp.") WHERE vendor_sku = '".$vendorsku."'";    	
            return $this->db->query($query);
        }
        
    }

    public function updatestock($sizesku,$vendorsku,$sizeactive,$upc){

        $query = "SELECT count(*) as rowcount FROM `rugs_tmp` WHERE JSON_SEARCH(`variations`,'one', '".$sizesku."') IS NOT NULL AND vendor_sku LIKE '".$vendorsku."%'";
        $count_row = $this->db->query($query)->row();

        if($count_row->rowcount > 0)
        {
           // $query = "UPDATE rugs_tmp SET `variations` = JSON_REPLACE(`variations`, CONCAT(REPLACE(SUBSTRING_INDEX(JSON_SEARCH(`variations`, 'one', '".$sizesku."'),'.',1),'\"',''),'.price'), ".$map.") WHERE vendor_sku = '".$vendorsku."'";    	
           // return $this->db->query($query);
            
            // $query = "UPDATE rugs_tmp SET `variations` = JSON_REPLACE(`variations`, CONCAT(REPLACE(SUBSTRING_INDEX(JSON_SEARCH(`variations`, 'one', '".$sizesku."'),'.',1),'\"',''),'.MSRP'), ".$msrp.") WHERE vendor_sku = '".$vendorsku."'";    	
            // return $this->db->query($query);   
            
             $query = "UPDATE rugs_tmp SET `variations` = JSON_REPLACE(`variations`, CONCAT(REPLACE(SUBSTRING_INDEX(JSON_SEARCH(`variations`, 'one', '".$sizesku."'),'.',1),'\"',''),'.active'), ".$sizeactive.") WHERE vendor_sku LIKE '".$vendorsku."%'";    	
             return $this->db->query($query); 

        }
        
    }
    
    public function updatevsku($vskuold,$status){
    	$query = "UPDATE rugs_tmp SET new_arrival = '".$status."' WHERE vendor_sku LIKE '".$vskuold."%'";
        //exit();
        return $this->db->query($query);
    }

    public function getactivesku(){
    	$query = "SELECT photo_id,variations FROM `rugs_tmp` WHERE new_arrival = 0 order by id desc LIMIT 10";
        return $this->db->query($query)->result_array();
    }
    
    public function checkstatusactive($vendorsku){
    	$query = "SELECT photo_id,new_arrival FROM `rugs_tmp` WHERE vendor_sku like '".$vendorsku."%'";
        return $this->db->query($query)->result_array();
    }

    public function getactivesuryasku(){
    	$query = "SELECT photo_id,vendor_sku FROM `rugs_tmp` WHERE vendor = 'Surya' AND new_arrival = 0";
        return $this->db->query($query)->result_array();
    }

    public function getallsuryasku(){
    	$query = "SELECT photo_id,vendor_sku FROM `rugs_tmp` WHERE vendor = 'Surya'";
        return $this->db->query($query)->result_array();
    }
	
	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);
    }
}