<?php
class TrialModel extends CI_Model
{
	function __construct() {
		parent::__construct();
		$this->load->database();
	}
       

	// function all_trials_count($ownerid)
    // {   
    //     $where = 'Where i.ownerid='.$ownerid;
    //     $invoicewhere = 'Where ownerid='.$ownerid;
    //     $sql = 'SELECT id, title, tdate, account_id, types, tranid, final_amount, mode, action from (SELECT i.id, i.title, i.tdate, i.account_id, i.types, i.tranid , (i.actual_amount-i.discount_amount+i.tax_amount) as final_amount, m.title as mode, 1 as action FROM lkqou_vbizz_transaction as i left join lkqou_vbizz_tmode as m on i.mid=m.id '.$where.' UNION ALL select id, invoice_number as title, invoice_date as tdate, 0 as account_id, invoice_for as types,transaction_id as tranid, (select(amount-discount_amount+tax_amount)) as final_amount, "invoice" as mode, 2 as action from lkqou_vbizz_invoices as i '.$invoicewhere.') as a ';
    //     $query = $this
    //             ->db
    //             ->query($sql);
    
    //     return $query->num_rows();  

    // }

    // function all_trials($limit,$start,$col,$dir,$ownerid)
    // {   
    //     $where = 'Where i.ownerid='.$ownerid;
    //     $invoicewhere = 'Where ownerid='.$ownerid;
    //     if ($col=='date'){
    //         $col='tdate';
    //     }
    //     $sql = 'SELECT id, title, tdate, account_id, types, tranid, final_amount, mode, action from (SELECT i.id, i.title, i.tdate, i.account_id, i.types, i.tranid , (i.actual_amount-i.discount_amount+i.tax_amount) as final_amount, m.title as mode, 1 as action FROM lkqou_vbizz_transaction as i left join lkqou_vbizz_tmode as m on i.mid=m.id '.$where.' UNION ALL select id, invoice_number as title, invoice_date as tdate, 0 as account_id, invoice_for as types,transaction_id as tranid, (select(amount-discount_amount+tax_amount)) as final_amount, "invoice" as mode, 2 as action from lkqou_vbizz_invoices as i '.$invoicewhere.') 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 trials_search($limit,$start,$year,$month,$account)
    {
        
        $sql = "SELECT i.*,(select(actual_amount-discount_amount+tax_amount)) as final_amount, m.title as mode FROM lkqou_vbizz_transaction as i left join lkqou_vbizz_tmode as m on i.mid=m.id WHERE year(i.tdate)=".$year." AND month(i.tdate)='".$month."' AND i.account_id= '".$account."' group by id order by i.tdate asc ".
               " limit {$limit} offset {$start}";	
        $query = $this
                ->db
                ->query($sql);

        
       
        if($query->num_rows()>0)
        {
            return $query->result();  
        }
        else
        {
            return null;
        }
    }

	function trials_search_count($year, $month, $account)
    {
        $sql = 'SELECT i.*,(select(actual_amount-discount_amount+tax_amount)) as final_amount, m.title as mode FROM lkqou_vbizz_transaction as i left join lkqou_vbizz_tmode as m on i.mid=m.id WHERE year(i.tdate)=".$year." AND month(i.tdate)="'.$month.'" AND i.account_id= "'.$account.'" group by id order by i.tdate asc ';
        $query = $this
                ->db
                ->query($sql);
    
        return $query->num_rows();
    } 

    function get_years(){
        $sql = ' SELECT DISTINCT YEAR (tdate) as tdate FROM lkqou_vbizz_transaction order by tdate';
        $query = $this
                ->db
                ->query($sql);
        
        if($query->num_rows()>0)
        {
            return $query->result();  
        }
        else
        {
            return null;
        }
    }

    function get_opening_balance($filter_account, $filter_year, $filter_month)
	{
		$month_start = date($filter_year.'-'.$filter_month.'-01');
		
		$sql='select initial_balance FROM lkqou_vbizz_accounts where id='.$filter_account ;
		$query = $this->db->query($sql);
        if($query->num_rows()>0)
        {
            $initial_balance = $query->row()->initial_balance; 
        }
        else
        {
            $initial_balance = 0;
        }
		
		$sql='select sum(actual_amount-discount_amount+tax_amount) as income FROM lkqou_vbizz_transaction where types = "income" and account_id='.$filter_account.' and tdate<'.$month_start ;
		$query = $this->db->query($sql);
		$income = $query->row()->income;
		//echo'<pre>';print_r($income);
		
		$sql='select sum(amount) as bank_income FROM lkqou_vbizz_banking where to_account='.$filter_account.' and created <'.$month_start ;
		$query = $this->db->query($sql);
		$bank_income = $query->row()->bank_income;
		//echo'<pre>';print_r($bank_income);
		
		$total_income_bal = $initial_balance + $income + $bank_income;
		
		$sql='select sum(actual_amount-discount_amount+tax_amount) as expense FROM lkqou_vbizz_transaction where types = "expense" and account_id='.$filter_account.' and tdate<'.$month_start ;
		$query = $this->db->query($sql);
		$expense = $query->row()->expense;
		
		$sql='select sum(amount) as bank_transfer FROM lkqou_vbizz_banking where from_account='.$filter_account.' and created <'.$month_start ;
		$query = $this->db->query($sql);
		$bank_transfer = $query->row()->bank_transfer;
		
		$available_balance = $total_income_bal-$expense-$bank_transfer;
		
		return $available_balance;
	}
	
	//get closing balance at the end of month
	function get_closing_balance($filter_account, $filter_year, $filter_month)
	{
		$month_end = date($filter_year.'-'.$filter_month.'-31');
		
		$sql='select initial_balance FROM lkqou_vbizz_accounts where id='.$filter_account ;
		$query = $this->db->query($sql);
		
        if($query->num_rows()>0)
        {
            $initial_balance = $query->row()->initial_balance; 
        }
        else
        {
            $initial_balance = 0;
        }
		
		$sql='select sum(actual_amount-discount_amount+tax_amount) as income FROM lkqou_vbizz_transaction where types = "income" and account_id='.$filter_account.' and tdate<'.$month_end ;
		$query = $this->db->query($sql);
		$income = $query->row()->income;
		//echo'<pre>';print_r($income); 
		
		$sql='select sum(amount) as bank_income FROM lkqou_vbizz_banking where to_account='.$filter_account.' and created <'.$month_end ;
		$query = $this->db->query($sql);
		$bank_income = $query->row()->bank_income;
		//echo'<pre>';print_r($bank_income);
		
		$total_income_bal = $initial_balance + $income + $bank_income;
		
		$sql='select sum(actual_amount-discount_amount+tax_amount) as expense FROM lkqou_vbizz_transaction where types = "expense" and account_id='.$filter_account.' and tdate<'.$month_end ;
		$query = $this->db->query($sql);
		$expense = $query->row()->expense;
		//echo'<pre>';print_r($expense);
		
		$sql='select sum(amount) as bank_transfer FROM lkqou_vbizz_banking where from_account='.$filter_account.' and created <'.$month_end ;
		$query = $this->db->query($sql);
		$bank_transfer = $query->row()->bank_transfer;
		//echo'<pre>';print_r($bank_transfer);
		
		$available_balance = $total_income_bal-$expense-$bank_transfer;
		
		return $available_balance;
	}

	

	
}