<?php
    defined('BASEPATH') OR exit('No direct script access allowed');
    class Statement extends MY_Controller {
	function __construct(){
            parent::__construct();
            if($this->session->user == ""){
                redirect('login');
            }
            $this->load->model('finance/AccountModel');
            $this->load->model('finance/StatementModel');
            $this->load->library('Formatslist');
	}
	public function index(){
            $data['accounts'] = $this->AccountModel->dropdown_acounts();
            $data['template'] = 'finance/view_statement';
            $this->load->view('template',$data);
	}
	public function view_statement(){
            $ownerid=$this->session->userid;
            $columns = array( 
                0 =>'date', 
                1 =>'transaction',
//                2 =>'mode',
//                3=> 'tranid',
                2=> 'debit',
                3=> 'credit',
                4=> 'balance'
            );
            $limit = $this->input->post('length'); 
            $start = $this->input->post('start');
            if($this->input->post('order')[0]['column'])
                $order = $columns[$this->input->post('order')[0]['column']];
            else
                $order= '';
            $dir = 'DESC';
            $totalData = $this->StatementModel->all_statements_count($ownerid);
            $totalFiltered = $totalData;
            $search_array = array();  
            if(!empty($this->input->post('search')['value'])&&!empty($this->input->post('search_btn'))){
                $search_array['search'] = $this->input->post('search')['value'];
            }
            if(!empty($this->input->post('filter_begin'))){
                $search_array['filter_begin'] = date("Y-m-d",strtotime($this->input->post('filter_begin')));
            }
            if(!empty($this->input->post('filter_end'))){
                $search_array['filter_end'] = date("Y-m-d",strtotime($this->input->post('filter_end')));
            }
            if(!empty($this->input->post('account'))){
                $search_array['account'] = $this->input->post('account');
            }
            if(!empty($this->input->post('type'))){
                $search_array['type'] = $this->input->post('type');
            }
            if(count($search_array)==0) {
                $order  =   'date';
                $statements = $this->StatementModel->all_statements($limit,$start,$order,$dir, $ownerid);
            } else {
		$order  =   'date';
                $statements =  $this->StatementModel->statements_search($limit,$start,$search_array,$order,$dir,$ownerid);
                $totalFiltered = $this->StatementModel->statements_search_count($search_array,$ownerid);
            }
        $data = array();
        if(!empty($statements)) {
            $account_id = 0;
            $initial_balance = 0;
            foreach ($statements as $row){
                //date
                $format = "d M Y";
                $saved_date = $row->TRANSACTION_DATE;
                $datetime = strtotime($saved_date);
                if($format) {
                    $date = date($format, $datetime );
                } else {
                    $date = $saved_date;
                }
                //transaction
                $transaction = $row->ACCOUNT_NAME;
                //link
                if($row->transactiontype=="Income") {
                    $link = $row->STATUS==1? site_url('order'):site_url('invoice');
                }
                if($row->transactiontype=="Expence") {
                    $link = $row->STATUS==1? site_url('purchase'):site_url('invoiceexpense');
                }
                //mode
                $mode = $row->transactiontype;
                //tranid
                $tranid = $row->COMMENTS;
                //debit
                if(empty($row->transactiontype))
                    continue;
                if($row->transactiontype=="Income") {
                    $debit = "0.00";
                    $d_val = 0;
                    $credit = $row->AMOUNT;
                    $c_val = $row->AMOUNT;
                } else if($row->transactiontype=="Expence") {
                    $debit = $row->AMOUNT;
                    $d_val = $row->AMOUNT;
                    $credit = "0.00";
                    $c_val = 0;
                }
                //account_id
                // $account_id = $row->account_id;
                //balance
                $initial_balance = $initial_balance + $c_val + $d_val;
                $nestedData['date'] = $this->formatslist->dateFormat($date);
                $nestedData['transaction'] = "<a href='javascript:void(0)' onclick='transactionsDetailsView(\"".$row->ID."\")'>".$transaction.'</a>';
//                    $nestedData['mode'] = $mode;
//                    $nestedData['tranid'] = $tranid;
                $nestedData['debit'] = $this->formatslist->formatCurrencywithHash($debit);
                $nestedData['credit'] = $this->formatslist->formatCurrencywithHash($credit);
                $nestedData['balance'] = $this->formatslist->formatCurrencywithHash($initial_balance);
                $data[] = $nestedData;
            }
        }
        $json_data = array(
            "draw"            => intval($this->input->post('draw')),  
            "recordsTotal"    => intval($totalData),  
            "recordsFiltered" => intval($totalFiltered), 
            "data"            => $data   
        );
        echo json_encode($json_data);
    }
    
    public function exportPDFData($searchtext='',$fromdate='',$todate='',$account='',$transtype='',$exporttype=''){
        
        if($exporttype=="PDF") {
            $this->load->model('settings/companyinfo_model');
            $this->load->library('Pdf');
            $pdf = new Pdf('P', 'mm', 'A4', true, 'UTF-8', false);
            $pdf->SetMargins(10, 10, 10, true);
            $pdf->SetCreator('Saleculator');
            $pdf->SetAuthor('Shameer C K');  
            $pdf->SetTitle("Account Statement");
            $pdf->SetSubject('Account Statement');
            $data['companyinfo'] = $this->companyinfo_model->getcompanyinfo('0');
            $pdf->SetHeaderData(K_BLANK_IMAGE, PDF_HEADER_LOGO_WIDTH, $data['companyinfo']->COMPANYNAME."#Account Statement",
                    $data['companyinfo']->ADDRESS1."\nPhone: ".$data['companyinfo']->PHONE1."\n".$data['companyinfo']->EMAIL.','
                    . ' '.$data['companyinfo']->WEBSITE."\nTRN: ".$data['companyinfo']->TAXID);
            $pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
            $pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
            $pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
            $pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
            $pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
            $pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
            $pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
            $pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
            $pdf->SetFont('helvetica', '', 11);
        }
        $content = '<style>table td,table th { border:1px solid #aaa; } </style><table cellspacing="0" cellpadding="3" style="width:100%;font-size:12px;">  
            <tr>  
                <th style="width:14%;">Date</th>';
                $content.= '<th style="width:25%;">Transaction</th>';
                $content.='<th style="width:25%;text-align:right;">Income</th>
                <th style="width:18%;text-align:right;">Expense</th>  
                <th style="width:18%;text-align:right;">Balance</th>
            </tr>'; 
        $content .= $this->fetch_data_for_PDF($searchtext,$fromdate,$todate,$account,$transtype);  
        $content .= '</table>'; 
        if($exporttype=="PDF") { 
            $pdf->AddPage();
            $pdf->writeHTML($content, true, false, true, false, '');
            $pdf->lastPage();
            $pdf->Output('Statement.pdf', 'I');
        } else if($exporttype=="EXCEL") {
            $file = "demo.xls";
            header("Content-type: application/vnd.ms-excel");
            header("Content-Disposition: attachment; filename=$file");
            echo $content;
        }
    }


    public function fetch_data_for_PDF($searchtext='',$fromdate='',$todate='',$account='',$transtype='') {
        if($searchtext!='-'){               
            $where=' and (LOWER(transactiontype) like "%'.$searchtext.'%" || AMOUNT like "%'.$searchtext.'%"'
                    . ' || COMMENTS like "%'.$searchtext.'%" ||  statustext like "%'.$searchtext.'%" ||  ACCOUNT_NAME like "%'.$searchtext.'%"'
                    . ' || SUPPLIERNAME like "%'.$searchtext.'%")';
        }else{
            $where='';
        }
        if($fromdate!='-'){
            $where .= ' and TRANSACTION_DATE >="'.date("Y-m-d",strtotime($fromdate)).'"';
        }
        if($todate!='-'){
            $where .= ' and TRANSACTION_DATE <="'.date("Y-m-d",strtotime($todate)).'"';
        }
        if($account!='-'){
            $where .= ' and ACCOUNT_ID ="'.$account.'"';
        }
        if($transtype!='-'){
            if($transtype=='Income')
                $searchtype=0;
            else if($transtype=='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 STATUS=1 ".$where." ORDER BY TRANSACTION_DATE ASC";
        // echo $sql;exit;
        $data = $this->common->customquery($sql);
        $output = '';
        $initial_balance = 0;
        if($account!='-') {
            $openingbal  =   $this->AccountModel->initial_balance('',$account);
            if($fromdate!='-') {
                $currentbalance = $this->AccountModel->searchdatebalanceaccount(date("Y-m-d",strtotime($fromdate)),$account);
                $initial_balance    =   $openingbal+$currentbalance;
            } else {
                $initial_balance    =   $openingbal;
            }
            $accountRec  =   $this->AccountModel->get_account($account);
            $output .= '<tr><td colspan="4"> Account Statement of '.$accountRec[0]->ACCOUNT_NAME.' </td></tr>';
            if($fromdate=='-')
                $output .= '<tr><td colspan="4"> Opening Balance </td>';
            else
                $output .= '<tr><td colspan="4"> Opening Balance As On '.date("d-M-Y",strtotime($fromdate)).'</td>';
            $output .= '<td align="right">'.$this->formatslist->formatCurrencywithHash($initial_balance).'</td></tr>';
        }
        if($data){
            $debttotal  =   0;
            $credittotal  =   0;
            foreach ($data as $key => $value) {
//                    if($value->transactiontype=="Income"){
//                        $debit = "0.00";
//                        $d_val = 0;
//                        $credit = $value->AMOUNT;
//                        $c_val = $value->AMOUNT;

//                    } else if($value->transactiontype=="Expence") {
//                        $debit = $value->AMOUNT;
//                        $d_val = $value->AMOUNT;
//                        $credit = "0.00";
//                        $c_val = 0;
//                    }

                if($value->AMOUNT<0){
                    $debit = "0.00";
                    $d_val = 0;
                    $credit = $value->AMOUNT;
                    $c_val = $value->AMOUNT;
                    $debttotal=$debttotal+$value->AMOUNT;
                } else {
                    $debit = $value->AMOUNT;
                    $d_val = $value->AMOUNT;
                    $credit = "0.00";
                    $c_val = 0;
                    $credittotal=$credittotal+$value->AMOUNT;
                }
                $initial_balance = $initial_balance + $c_val + $d_val;
                $output .= '<tr>
                <td>'.$this->formatslist->dateFormat($value->TRANSACTION_DATE).'</td>';  
                if($value->PAYMENTHEAD=='PURCHASE' || $value->PAYMENTHEAD=='PURCHASERETURN')
                    $output .= '<td>'.$value->PAYMENTHEAD.'<br/>[ Supplier : <span style="color:blue;">'.$value->SUPPLIERNAME.'</span> ]</td>';
                else
                    $output .= '<td>'.$value->PAYMENTHEAD.'<br/>'.$value->COMMENTS.'</td>';
                $output .= '<td style="text-align:right;">'.$this->formatslist->formatCurrencywithHash($debit).'</td>  
                <td style="text-align:right;">'.$this->formatslist->formatCurrencywithHash($credit).'</td>
                <td align="right" style="text-align:right;">'.$this->formatslist->formatCurrencywithHash($initial_balance).'</td>
                </tr>';
            }
            $output .= '<tr><td colspan="2" align="right">Total (Income and Expense)</td><td style="text-align:right;">'
                .$this->formatslist->formatCurrencywithHash($credittotal).'</td>'
                . '<td style="text-align:right;">'.$this->formatslist->formatCurrencywithHash($debttotal).'</td><td></td></tr>';
            return $output;
        }else{
            $output='';
            return $output;
        }
    }
}
