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

	function all_statements_count($ownerid){   
            $where = ' Where T.STATUS=1 ';
            $sql = "SELECT T.ID,T.TRANSACTION_DATE,
                CASE T.TRANSACTION_TYPE_ID
                  WHEN 0 THEN 'Income' 
                  WHEN 1 THEN 'Expence'  
                  ELSE '' END as transactiontype, T.TRANSACTION_TYPE_ID ,T.ACCOUNT_ID,T.PAYMENTHEAD_ID,T.AMOUNT,
                  T.COMMENTS,T.CREATED,T.CREATED_BY,T.APPROVED,T.APPROVED_BY,T.COMMENTS,
                CASE T.STATUS
                  WHEN 0 THEN 'Created' 
                  WHEN 1 THEN 'Approved'
                  WHEN 2 THEN 'Rejected'
                  WHEN 3 THEN 'On Hold'
                  WHEN 4 THEN 'Updated'
                  ELSE '' END as statustext,T.STATUS,A.ACCOUNT_NAME,A.AVAILABLE_BALANCE
                from TRANSACTIONS T JOIN ACCOUNTS A ON T.ACCOUNT_ID=A.ID".$where;
            $query = $this->db->query($sql);
            return $query->num_rows();  
        }

        function all_statements($limit,$start,$col,$ownerid,$dir="ASC") {
            if ($col=='date'){
                $col='T.TRANSACTION_DATE';
            }
            $where = ' Where T.STATUS=1';
            $sql = "SELECT T.ID,T.TRANSACTION_DATE,
            CASE T.TRANSACTION_TYPE_ID
                WHEN 0 THEN 'Income' 
                WHEN 1 THEN 'Expence' 
                ELSE '' END as transactiontype, T.TRANSACTION_TYPE_ID ,T.ACCOUNT_ID,T.PAYMENTHEAD_ID,T.AMOUNT,
                T.COMMENTS,T.CREATED,T.CREATED_BY,T.APPROVED,T.APPROVED_BY,
                CONCAT(S.NAME, ' - ', IF(S.TAXID IS NULL OR S.TAXID='','',S.TAXID)) AS SUPPLIERNAME,
            CASE T.STATUS
                WHEN 0 THEN 'Created' 
                WHEN 1 THEN 'Approved'
                WHEN 2 THEN 'Rejected'
                ELSE ''
            END as statustext,
            T.STATUS,A.ACCOUNT_NAME,A.AVAILABLE_BALANCE,
                 CASE WHEN T.TRANSACTION_TYPE_ID=0 THEN 'Deposit' ELSE 'Payment' END as Transactiontype,d.PAYMENTHEAD
            from TRANSACTIONS T JOIN ACCOUNTS A ON T.ACCOUNT_ID=A.ID 
            left join PEOPLE as c on T.CREATED_BY=c.ID
            left join PAYMENTHEADS as d on T.PAYMENTHEAD_ID=d.ID
            left join PURCHASEINVOICE_PAYMENTS as PP on PP.TRANSACTION_ID=T.ID
            left join PURCHASEINVOICE as P on P.ID=PP.PURCHASEINVOICE_ID
            left join SUPPLIERS as S on S.ID=P.SUPPLIERID"
            .$where;
            if($col)
                $sql.= " Order by {$col} {$dir}";
            if($limit!='' && $start!='')
                $sql.= " Limit {$start}, {$limit}";
//              echo $sql;exit;
            $query = $this->db->query($sql);
            if($query->num_rows()>0) {
                return $query->result(); 
            } else {
                return null;
            }
        }
    
	function statements_search($limit,$start,$search_array, $ownerid,$col='date',$dir='ASC') {
            if ($col=='date'){
                $col='TRANSACTION_DATE';
            }
            $where = ' Where STATUS=1';
            if(isset($search_array['search'])){
                $where .= ' and (LOWER(transactiontype) like "%'.$search_array['search'].'%" || '
                    . 'AMOUNT like "%'.$search_array['search'].'%" || COMMENTS like "%'.$search_array['search'].'%" || '
                    . ' statustext like "%'.$search_array['search'].'%" || ACCOUNT_NAME like "%'.$search_array['search'].'%"'
                    . ' || SUPPLIERNAME like "%'.$search_array['search'].'%")';
            }
            if(isset($search_array['filter_begin'])){
                $where .= ' and TRANSACTION_DATE >="'.$search_array['filter_begin'].'"';
            }
            if(isset($search_array['filter_end'])){
                $where .= ' and TRANSACTION_DATE <="'.$search_array['filter_end'].'"';
            }
            if(isset($search_array['account'])){
                $where .= ' and ACCOUNT_ID ="'.$search_array['account'].'"';
            }
            if(isset($search_array['type'])){
                if($search_array['type']=='Income')
                    $searchtype=0;
                else if($search_array['type']=='Expense')
                    $searchtype=1;
                $where .= ' and TRANSACTION_TYPE_ID ="'.$searchtype.'"';
            }
            $sql = "SELECT * from (SELECT T.ID,T.TRANSACTION_DATE,
            CASE T.TRANSACTION_TYPE_ID
                WHEN 0 THEN 'Income' 
                WHEN 1 THEN 'Expence' 
                ELSE '' END as transactiontype, T.TRANSACTION_TYPE_ID ,T.ACCOUNT_ID,T.PAYMENTHEAD_ID,T.AMOUNT,
                T.COMMENTS,T.CREATED,T.CREATED_BY,T.APPROVED,T.APPROVED_BY,
                CONCAT(S.NAME, ' - ', IF(S.TAXID IS NULL OR S.TAXID='','',S.TAXID)) AS SUPPLIERNAME,
            CASE T.STATUS
                WHEN 0 THEN 'Created' 
                WHEN 1 THEN 'Approved'
                WHEN 2 THEN 'Rejected'
                ELSE '' END as statustext,T.STATUS,A.ACCOUNT_NAME,A.AVAILABLE_BALANCE,d.PAYMENTHEAD
            from TRANSACTIONS T JOIN ACCOUNTS A ON T.ACCOUNT_ID=A.ID
                left join PEOPLE as c on T.CREATED_BY=c.ID
                left join PAYMENTHEADS as d on T.PAYMENTHEAD_ID=d.ID
                left join PURCHASEINVOICE_PAYMENTS as PP on PP.TRANSACTION_ID=T.ID
                left join PURCHASEINVOICE as P on P.ID=PP.PURCHASEINVOICE_ID
                left join SUPPLIERS as S on S.ID=P.SUPPLIERID) mytable".$where;
//            if($col)
                $sql.= " Order by TRANSACTION_DATE ASC";
            if($limit!='' && $start!='')
                $sql.= " Limit {$start}, {$limit}";
            
//            echo $sql;exit;    
            
            $query = $this->db->query($sql);

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

	function statements_search_count($search_array, $ownerid)
    {
            $where = ' Where STATUS=1';
            
            if(isset($search_array['search'])){
                $where .= ' and (LOWER(transactiontype) like "%'.$search_array['search'].'%"'
                    . ' || AMOUNT like "%'.$search_array['search'].'%" || COMMENTS like "%'.$search_array['search'].'%"'
                    . ' ||  statustext like "%'.$search_array['search'].'%" ||  ACCOUNT_NAME like "%'.$search_array['search'].'%"'
                    . ' || SUPPLIERNAME like "%'.$search_array['search'].'%")';
            }

            if(isset($search_array['filter_begin'])){
                $where .= ' and TRANSACTION_DATE >="'.$search_array['filter_begin'].'"';
            }

            if(isset($search_array['filter_end'])){
                $where .= ' and TRANSACTION_DATE <="'.$search_array['filter_end'].'"';
            }
            if(isset($search_array['account'])){
                $where .= ' and ACCOUNT_ID ="'.$search_array['account'].'"';
            }
            if(isset($search_array['type'])){
                if($search_array['type']=='Income')
                    $searchtype=0;
                else if($search_array['type']=='Expense')
                    $searchtype=1;
                $where .= ' and TRANSACTION_TYPE_ID ="'.$searchtype.'"';
            }
            
            $sql = "SELECT * from (SELECT T.ID,T.TRANSACTION_DATE,
            CASE T.TRANSACTION_TYPE_ID
                WHEN 0 THEN 'Income' 
                WHEN 1 THEN 'Expence' 
              ELSE '' END as transactiontype, T.TRANSACTION_TYPE_ID ,T.ACCOUNT_ID,T.PAYMENTHEAD_ID,T.AMOUNT,
              T.COMMENTS,T.CREATED,T.CREATED_BY,T.APPROVED,T.APPROVED_BY,
              CONCAT(S.NAME, ' - ', IF(S.TAXID IS NULL OR S.TAXID='','',S.TAXID)) AS SUPPLIERNAME,
            CASE T.STATUS
              WHEN 0 THEN 'Created' 
              WHEN 1 THEN 'Approved'
              WHEN 2 THEN 'Rejected'
              ELSE '' END as statustext,T.STATUS,A.ACCOUNT_NAME,A.AVAILABLE_BALANCE, d.PAYMENTHEAD
            from TRANSACTIONS T JOIN ACCOUNTS A ON T.ACCOUNT_ID=A.ID
            left join PEOPLE as c on T.CREATED_BY=c.ID
            left join PAYMENTHEADS as d on T.PAYMENTHEAD_ID=d.ID
            left join PURCHASEINVOICE_PAYMENTS as PP on PP.TRANSACTION_ID=T.ID
            left join PURCHASEINVOICE as P on P.ID=PP.PURCHASEINVOICE_ID
            left join SUPPLIERS as S on S.ID=P.SUPPLIERID
            ) mytable".$where;
            
            
        $query = $this
                ->db
                ->query($sql);
    
        return $query->num_rows();
    } 


	

	
}