<?php
class TranModel extends CI_Model
{
	function __construct() {
		parent::__construct();
		$this->load->database();
	}
       
	public function add_tran($data)
	{
		$this->db->insert('transactions',$data);
		return true;
	}

	public function update_tran($id, $ownerid, $data)
	{
		$this->db->where(array('id'=>$id, 'ownerid'=>$ownerid));
		$this->db->update('transactions', $data);
		return true;
	}
	public function get_tran($id)
	{
		$this->db->where('id', $id);
		$query = $this->db->get('transactions');
		return $query->result();
	}

	public function delete_trans($ids){
		$this->db->where_in('id', $ids);
		$this->db->delete('transactions');
		return true;
	}

	

	function all_trans_count()
    {   
        $sql ='SELECT a.* ,(select sum(t.actual_amount-t.discount_amount+t.tax_amount) from transactions as t where t.types="income" and t.tid=a.id) as income,(select sum(i.actual_amount-i.discount_amount+i.tax_amount) from lkqou_vbizz_transaction as i where i.types="expense" and i.tid=a.id) as expense FROM lkqou_vbizz_tran as a';
        //echo $sql;exit;
        $query = $this
                ->db
                ->query($sql);
    
        return $query->num_rows();

    }
    
    function all_trans($limit,$start,$col,$dir){   

		$sql ='SELECT a.* ,(select sum(t.actual_amount-t.discount_amount+t.tax_amount) from lkqou_vbizz_transaction as t where t.types="income" and t.tid=a.id) as income,(select sum(i.actual_amount-i.discount_amount+i.tax_amount) from lkqou_vbizz_transaction as i where i.types="expense" and i.tid=a.id) as expense FROM lkqou_vbizz_tran as a'.
              ' Order by '.$col.' '.$dir.
			  ' Limit '.$limit.' Offset '.$start;
        
        $query = $this
                ->db
                ->query($sql);
    
        
        if($query->num_rows()>0)
        {
            return $query->result(); 
        }
        else
        {
            return null;
        }
        
    }
   
    function trans_search($limit,$start,$search_array,$col,$dir, $ownerid)
    {
        // $where = ' Where ownerid='.$ownerid;
        $where = '';

        if(isset($search_array['search'])){
            $where .= ' Where LOWER(a.title) like "%'.$search_array['search'].'%"';
        }
        
		$sql ='SELECT a.* ,(select sum(t.actual_amount-t.discount_amount+t.tax_amount) from lkqou_vbizz_transaction as t where t.types="income" and t.tid=a.id) as income,(select sum(i.actual_amount-i.discount_amount+i.tax_amount) from lkqou_vbizz_transaction as i where i.types="expense" and i.tid=a.id) as expense FROM lkqou_vbizz_tran as a'.
              $where.' Order by '.$col.' '.$dir.
			  ' Limit '.$limit.' Offset '.$start;
        
        $query = $this
                ->db
                ->query($sql);
    
        
        if($query->num_rows()>0)
        {
            return $query->result(); 
        }
        else
        {
            return null;
        }
    }

    function trans_search_count($search, $ownerid)
    {
		// $where = ' Where ownerid='.$ownerid;
        $where = '';

        if(isset($search_array['search'])){
            $where .= ' Where LOWER(a.title) like "%'.$search_array['search'].'%"';
        }
        
		$sql ='SELECT a.* ,(select sum(t.actual_amount-t.discount_amount+t.tax_amount) from lkqou_vbizz_transaction as t where t.types="income" and t.tid=a.id) as income,(select sum(i.actual_amount-i.discount_amount+i.tax_amount) from lkqou_vbizz_transaction as i where i.types="expense" and i.tid=a.id) as expense FROM lkqou_vbizz_tran as a'.
              $where;
        
        $query = $this
                ->db
                ->query($sql);
    
        return $query->num_rows();
    } 

	function dropdown_ttypes($id)
	{
		$sql ='select * from lkqou_vbizz_tran where id <> '.$id.' order by title asc';
        
        $query = $this
                ->db
                ->query($sql);
    
        
        if($query->num_rows()>0)
        {
            return $query->result(); 
        }
        else
        {
            return null;
        }

	}

	
	
}