<?php
    class TransactionModel extends CI_Model {
        function __construct() {
            parent::__construct();
            $this->load->database();
        }
        public function add_transaction($data) {
            $this->db->insert('TRANSACTIONS',$data);
            return true;
        }
        public function update_transaction($data,$id) {
            $this->db->where(array('ID'=>$id));
            $this->db->limit(1, 0);
            $this->db->update('TRANSACTIONS', $data);
            return true;
        }
        function all_transaction_count($trnstype=''){
            if($trnstype==0 || $trnstype==1){
                $this->db->where('TRANSACTION_TYPE_ID', $trnstype);
                $query = $this->db->get('TRANSACTIONS');
                return $query->num_rows();
            }else{
                $query = $this->db->get('TRANSACTIONS');
                return $query->num_rows();
                return 0;
            }
        }
        function listTransByType($limit,$start,$col,$dir,$trnstype=''){
            $sql = " Select a.ID, b.ACCOUNT_NAME as account,b.ID as accountid, a.AMOUNT, c.NAME as created_by, a.CREATED as created,a.STATUS, ".
                " CASE WHEN a.TRANSACTION_TYPE_ID=0 THEN 'Deposit' ELSE 'Payment' END as Transactiontype,d.PAYMENTHEAD".
                " from TRANSACTIONS as a left join ACCOUNTS as b on a.ACCOUNT_ID=b.ID".
                " left join PEOPLE as c on a.CREATED_BY=c.ID".
                " left join PAYMENTHEADS as d on a.PAYMENTHEAD_ID=d.ID";
            if($trnstype!='')
                $sql.= " WHERE TRANSACTION_TYPE_ID='".$trnstype."'";
            if($col)
                $sql.= " Order by a.{$col} {$dir}";
            else
                $sql.= " Order by a.CREATED DESC";
            if($limit!='' && $start!='')
                $sql.= " Limit {$limit} Offset {$start}";
            $query = $this->db->query($sql);
            if($query->num_rows()>0){
                return $query->result(); 
            }else{
                return null;
            }
        }
        function searchTransByType($limit,$start,$search,$col,$dir,$trnstype=''){
            $sql = " Select a.ID, b.ACCOUNT_NAME as account,b.ID as accountid, a.AMOUNT, c.NAME as created_by, a.CREATED as created,a.STATUS,".
                " CASE WHEN a.TRANSACTION_TYPE_ID=0 THEN 'Deposit' ELSE 'Payment' END as Transactiontype,d.PAYMENTHEAD".
                " from TRANSACTIONS as a".
                " left join ACCOUNTS as b on a.ACCOUNT_ID=b.ID".
                " left join PEOPLE as c on a.CREATED_BY=c.ID".
                " left join PAYMENTHEADS as d on a.PAYMENTHEAD_ID=d.ID".
                " where b.ACCOUNT_NAME Like '%{$search}%' or ".
                " b.ACCOUNT_NAME Like '%{$search}%' ";
            if($trnstype!='')
                $sql.= " AND TRANSACTION_TYPE_ID='".$trnstype."'";
            if($col)
                $sql.= " Order by a.{$col} {$dir}";
            if($limit && $start)
                $sql.= " Limit {$limit} Offset {$start}";	
            $query = $this->db->query($sql);
            if($query->num_rows()>0){
                return $query->result();  
            }else{
                return null;
            }
        }
        function countSearchTransByType($search,$trnstype=''){
            $sql = " Select a.ID, b.ACCOUNT_NAME as account,b.ID as accountid, a.AMOUNT, c.NAME as created_by, a.CREATED as created,a.STATUS,".
                " CASE WHEN a.TRANSACTION_TYPE_ID=0 THEN 'Deposit' ELSE 'Payment' END as Transactiontype,d.PAYMENTHEAD".
                " from TRANSACTIONS as a".
                " left join ACCOUNTS as b on a.ACCOUNT_ID=b.ID".
                " left join PEOPLE as c on a.CREATED_BY=c.ID".
                " left join PAYMENTHEADS as d on a.PAYMENTHEAD_ID=d.ID".
                " where b.ACCOUNT_NAME Like '%{$search}%' or ".
                " b.ACCOUNT_NAME Like '%{$search}%' ";
            if($trnstype!='')
                $sql.= " AND TRANSACTION_TYPE_ID='".$trnstype."'";
            $query = $this->db->query($sql);
            return $query->num_rows();
        }
        function addPurchasePayment($data){
            $this->db->insert('PURCHASEINVOICE_PAYMENTS',$data);
            return true;
        }
        
        public function deletePurchasePayment($id){
            $this->db->where('TRANSACTION_ID', $id);
            $this->db->delete('PURCHASEINVOICE_PAYMENTS');
            return true;
        }
        
        public function getPurchasePayment($transid=''){
            $sql = "SELECT * FROM PURCHASEINVOICE_PAYMENTS";
            if($transid!='')
                $sql.= " WHERE TRANSACTION_ID='".$transid."'";
            $query = $this->db->query($sql);
            return $query->result();
        }
        
        public function getPurchaseInvoice($id='') {
            $sql = " Select a.ID, b.ACCOUNT_NAME as account,b.ID as accountid, a.AMOUNT, c.NAME as created_by, a.CREATED as created,a.STATUS,a.COMMENTS, CASE WHEN a.TRANSACTION_TYPE_ID=0 THEN 'Deposit' ELSE 'Payment' END as Transactiontype,d.PAYMENTHEAD,a.PAYMENTHEAD_ID,a.TRANSACTION_TYPE_ID,e.PURCHASEINVOICE_ID".
                " from TRANSACTIONS as a".
                " left join ACCOUNTS as b on a.ACCOUNT_ID=b.ID".
                " left join PEOPLE as c on a.CREATED_BY=c.ID".
                " left join PAYMENTHEADS as d on a.PAYMENTHEAD_ID=d.ID".
                " left join PURCHASEINVOICE_PAYMENTS as e on a.ID=e.TRANSACTION_ID";
            if($id!='')
                $sql.= " WHERE a.ID='".$id."'";
            $query = $this->db->query($sql);
            return $query->result_array();
        }
        public function delete_transaction($id){
            $this->db->where('id', $id);
            $this->db->delete('TRANSACTIONS');
            return true;
        }
        public function get_transactiondetails($id){
            $this->db->where('id', $id);
            $query = $this->db->get('TRANSACTIONS');
            return $query->result();
        }
        public function purchaseInvoicePaymentList() {
            $sql="SELECT * FROM (select (COALESCE(FirstSet.TOTAL,0)+COALESCE(ThirdSet.TAXAMOUNT,0)+COALESCE(SecondSet.FREIGHTAMOUNT,0)+COALESCE(SecondSet.DISCOUNT,0)+COALESCE(SecondSet.ADJUSTMENT,0)) AS TOTALAMOUNT, COALESCE(FourthSet.PAIDAMOUNT,0) as PAIDAMOUNT, ((COALESCE(FirstSet.TOTAL,0)+COALESCE(ThirdSet.TAXAMOUNT,0)+COALESCE(SecondSet.FREIGHTAMOUNT,0)+COALESCE(SecondSet.DISCOUNT,0)+COALESCE(SecondSet.ADJUSTMENT,0))+COALESCE(FourthSet.PAIDAMOUNT,0)) AS BALANCE, FirstSet.PLID, SecondSet.* from ( SELECT P.*,S.NAME as name FROM PURCHASEINVOICE P LEFT JOIN SUPPLIERS S ON P.SUPPLIERID=S.ID ) as SecondSet LEFT JOIN ( SELECT SUM((COALESCE(PL.QUANTITY,0) * COALESCE(PL.UNITPRICE,0))+COALESCE(PL.DISCOUNT,0)) AS TOTAL,PL.PIID AS PLID FROM PURCHASEINVOICELINES PL GROUP BY PL.PIID ) as FirstSet ON FirstSet.PLID=SecondSet.ID LEFT JOIN ( SELECT SUM(TAXAMOUNT) AS TAXAMOUNT,PIID AS TPIID FROM PURCHASEINVOICETAXES GROUP BY PIID ) as ThirdSet ON FirstSet.PLID=ThirdSet.TPIID LEFT JOIN ( SELECT SUM(T.AMOUNT) AS PAIDAMOUNT,PP.PURCHASEINVOICE_ID FROM TRANSACTIONS T LEFT JOIN PURCHASEINVOICE_PAYMENTS PP ON PP.TRANSACTION_ID=T.ID WHERE T.PAYMENTHEAD_ID='PURCHASE' GROUP BY PP.PURCHASEINVOICE_ID ) as FourthSet ON ThirdSet.TPIID=FourthSet.PURCHASEINVOICE_ID) MYTABLE WHERE ( STATUS=2 || STATUS=-2) AND BALANCE!=0";
            //echo $sql;exit;
            $arr = $this->common->customquery($sql);
            return $arr;
        }
        
        function all_transactions_count($ownerid){   
//            $where = ' Where a.CREATED_BY='.$ownerid;
            $sql = " Select * ".
                " from TRANSACTIONS as a".
                " left join ACCOUNTS as b on a.ACCOUNT_ID=b.ID".
                " left join PEOPLE as c on a.CREATED_BY=c.ID".
                " left join PAYMENTHEADS as d on a.PAYMENTHEAD_ID=d.ID";
            $query = $this->db->query($sql);
            return $query->num_rows();  
        }
        
        function all_transactions($limit,$start,$dir,$ownerid,$col='date') {

            if ($col=='date'){
                $col='created,TRANSACTION_DATE';
            }
//            $where = ' Where created='.$ownerid;
            $sql = "select * from (Select a.ID, b.ACCOUNT_NAME as account,b.ID as accountid, a.AMOUNT, c.NAME as created_by,
                a.CREATED as created,a.STATUS, CONCAT(S.NAME, ' - ', IF(S.TAXID IS NULL OR S.TAXID='','',S.TAXID)) AS SUPPLIERNAME,CASE a.status 
                WHEN 0 THEN 'Created'
                WHEN 1 THEN 'Approved'
                WHEN 2 THEN 'Rejected'
                WHEN 3 THEN 'On Hold'
                WHEN 4 THEN 'Updated'
                ELSE 'Created'
                END as statustext,a.TRANSACTION_DATE,".
                " CASE WHEN a.TRANSACTION_TYPE_ID=0 THEN 'Deposit' ELSE 'Payment' END as Transactiontype,d.PAYMENTHEAD".
                " from TRANSACTIONS as a".
                " left join ACCOUNTS as b on a.ACCOUNT_ID=b.ID".
                " left join PEOPLE as c on a.CREATED_BY=c.ID".
                " left join PAYMENTHEADS as d on a.PAYMENTHEAD_ID=d.ID".
                " left join PURCHASEINVOICE_PAYMENTS as PP on PP.TRANSACTION_ID=a.ID".
                " left join PURCHASEINVOICE as P on P.ID=PP.PURCHASEINVOICE_ID".
                " left join SUPPLIERS as S on S.ID=P.SUPPLIERID";
           $sql.= ") mytable Order by created DESC";
//            if($col)
//                $sql.= " ,{$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 transactions_search($limit,$start,$search_array,$col,$dir, $ownerid) {
            $where = ' HAVING 1';
            if(isset($search_array['search'])){
                $where .= ' and (account like "%'.$search_array['search'].'%" || Transactiontype like "%'.$search_array['search'].'%"'
                    . ' || PAYMENTHEAD like "%'.$search_array['search'].'%" ||  AMOUNT like "%'.$search_array['search'].'%"'
                    . ' || created_by like "%'.$search_array['search'].'%" || statustext like "%'.$search_array['search'].'%"'
                    . ' || SUPPLIERNAME like "%'.$search_array['search'].'%")';
            }
            if(isset($search_array['filter_begin'])){
                $where .= ' and TRANSACTION_DATE >="'.date("Y-m-d",strtotime($search_array['filter_begin'])).'"';
            }
            if(isset($search_array['filter_end'])){
                $where .= ' and TRANSACTION_DATE <="'.date("Y-m-d",strtotime($search_array['filter_end'])).'"';
            }
            $sql = "Select a.ID, b.ACCOUNT_NAME as account,b.ID as accountid, a.AMOUNT, c.NAME as created_by,
                a.CREATED as created,a.STATUS,CONCAT(S.NAME, ' - ', IF(S.TAXID IS NULL OR S.TAXID='','',S.TAXID)) AS SUPPLIERNAME,CASE a.status 
                WHEN 0 THEN 'Created'
                WHEN 1 THEN 'Approved'
                WHEN 2 THEN 'Rejected'
                WHEN 3 THEN 'On Hold'
                WHEN 4 THEN 'Updated'
                ELSE 'Created'
                END as statustext,a.TRANSACTION_DATE,".
                " CASE WHEN a.TRANSACTION_TYPE_ID=0 THEN 'Deposit' ELSE 'Payment' END as Transactiontype,d.PAYMENTHEAD".
                " from TRANSACTIONS as a".
                " left join ACCOUNTS as b on a.ACCOUNT_ID=b.ID".
                " left join PEOPLE as c on a.CREATED_BY=c.ID".
                " left join PAYMENTHEADS as d on a.PAYMENTHEAD_ID=d.ID".
                " left join PURCHASEINVOICE_PAYMENTS as PP on PP.TRANSACTION_ID=a.ID".
                " left join PURCHASEINVOICE as P on P.ID=PP.PURCHASEINVOICE_ID".
                " left join SUPPLIERS as S on S.ID=P.SUPPLIERID";
           $sql.= " ".$where." Order by created DESC";
//            if($col)
//                $sql.= " ,{$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 transactions_search_count($search_array, $ownerid) {
            $where = ' Where 1';
            if(isset($search_array['search'])){
                $where .= ' and (b.ACCOUNT_NAME like "%'.$search_array['search'].'%"'
                    . ' || PAYMENTHEAD like "%'.$search_array['search'].'%" ||  AMOUNT like "%'.$search_array['search'].'%"'
                    . ' || c.NAME like "%'.$search_array['search'].'%")';
            }
            if(isset($search_array['filter_begin'])){
                $where .= ' and TRANSACTION_DATE >="'.date("Y-m-d",strtotime($search_array['filter_begin'])).'"';
            }
            if(isset($search_array['filter_end'])){
                $where .= ' and TRANSACTION_DATE <="'.date("Y-m-d",strtotime($search_array['filter_end'])).'"';
            }
            $sql = "Select a.ID, b.ACCOUNT_NAME as account,b.ID as accountid, a.AMOUNT, c.NAME as created_by, a.CREATED as created,a.STATUS,CASE a.status 
                WHEN 0 THEN 'Created'
                WHEN 1 THEN 'Approved'
                WHEN 2 THEN 'Rejected'
                WHEN 3 THEN 'On Hold'
                WHEN 4 THEN 'Updated'
                ELSE 'Created'
                END as statustext,a.TRANSACTION_DATE,".
                " CASE WHEN a.TRANSACTION_TYPE_ID=0 THEN 'Deposit' ELSE 'Payment' END as Transactiontype,d.PAYMENTHEAD".
                " from TRANSACTIONS as a".
                " left join ACCOUNTS as b on a.ACCOUNT_ID=b.ID".
                " left join PEOPLE as c on a.CREATED_BY=c.ID".
                " left join PAYMENTHEADS as d on a.PAYMENTHEAD_ID=d.ID";
            $sql.= " ".$where;
            $query = $this->db->query($sql);
            return $query->num_rows();
        }
        
//        public function getPurchasePaymentDetails($transid='',$piid=''){
//            $sql = "SELECT * FROM PURCHASEINVOICE_PAYMENTS WHERE 1";
//            if($transid!='')
//                $sql.= " AND TRANSACTION_ID='".$transid."'";
//            if($piid!='')
//                $sql.= " AND PURCHASEINVOICE_ID='".$piid."'";
//            $query = $this->db->query($sql);
//            return $query->result();
//        }
        
    }
?>
