<?php
defined('BASEPATH') OR exit('No direct script access allowed');
    class Plstatement extends MY_Controller {
	function __construct(){
            parent::__construct();
            if($this->session->user == ""){
                redirect('login');
            }
            $this->load->model('settings/companyinfo_model');
            $this->load->library('Formatslist');
	}
//	public function index(){
//            redirect(base_url("finance/Profitandlossstatement/viewpandlstatement"));
//	}
	public function index() {
            
            $query = $this->db->get('PAYMENTHEADS');
            $data["headlist"] = $query->result();
            $data["headlist"] = json_decode(json_encode($data["headlist"]), True);
            $nxi    =    count($data["headlist"]);
//            $data["headlist"][$nxi]['ID'] = 'PURCHASE';
//            $data["headlist"][$nxi]['PAYMENTHEAD'] = 'PURCHASE';
//            $data["headlist"][$nxi+1]['ID'] = 'PURCHASERETURN';
//            $data["headlist"][$nxi+1]['PAYMENTHEAD'] = 'PURCHASE RETURN';
//            $data["headlist"][$nxi+2]['ID'] = 'SALES';
//            $data["headlist"][$nxi+2]['PAYMENTHEAD'] = 'SALES';
//            $data["headlist"][$nxi+3]['ID'] = 'EXPENSECLAIM';
//            $data["headlist"][$nxi+3]['PAYMENTHEAD'] = 'EXPENSE CLAIM';
//            $data["headlist"][$nxi+4]['ID'] = 'SALESRETURN';
//            $data["headlist"][$nxi+4]['PAYMENTHEAD'] = 'SALESRETURN';
            if(isset($_POST['choosehead'])) {
                $data['choosehead'] =   $_POST['choosehead'];
            } else {
                $data['choosehead'] =   array();
            }
            if(isset($_POST['filter_begin'])) {
                $data['filter_begin'] =   $_POST['filter_begin'];
            } else {
                $data['filter_begin'] =   '';
            }
            if(isset($_POST['filter_end'])) {
                $data['filter_end'] =   $_POST['filter_end'];
            } else {
                $data['filter_end'] =   '';
            }
            
            $data['template'] = 'finance/view_profitandlossstatement';
            $this->load->view('template',$data);
	}
        
        public function searchpandltransactions() {
            if($_POST) {
                $where = "R.AMOUNT>0 AND R.STATUS=1 ";
                $filterdate    =   explode(" - ",$_POST['filter_date']);
                $filterstart    =   $filterdate[0];
                $filterend    =   $filterdate[1];
                if($filterstart) {
                    $fromdate   =   date("Y-m-d",strtotime($filterstart));
                }
                if($filterend) {
                    $todate     =   date("Y-m-d",strtotime($filterend));
                }
                
                
                if($fromdate){
                    $where .= ' AND R.TRANSACTION_DATE >="'.date("Y-m-d",strtotime($fromdate)).'"';
                }
                if($todate){
                    $where .= ' AND R.TRANSACTION_DATE <="'.date("Y-m-d",strtotime($todate)).'"';
                }

                if($_POST['choosehead']){
                    $tmphead = "'".implode("','",$_POST['choosehead'])."'";
                    $where .= ' AND R.PAYMENTHEAD_ID IN (SELECT ID FROM PAYMENTHEADS WHERE PAYMENTHEAD IN ('.$tmphead.'))';
                }
                $paymentwhere = "R.AMOUNT<=0 AND R.STATUS=1 ";
//                if($_POST['filter_begin']){
//                    $paymentwhere .= ' and PY.PAYMENT_DATE >="'.date("Y-m-d",strtotime($_POST['filter_begin'])).'"';
//                }
//                if($_POST['filter_end']){
//                    $paymentwhere .= ' and PY.PAYMENT_DATE <="'.date("Y-m-d",strtotime($_POST['filter_end'])).'"';
//                }
                if($fromdate){
                    $paymentwhere .= ' AND R.TRANSACTION_DATE >="'.date("Y-m-d",strtotime($fromdate)).'"';
                }
                if($todate){
                    $paymentwhere .= ' AND R.TRANSACTION_DATE <="'.date("Y-m-d",strtotime($todate)).'"';
                }
                if($_POST['choosehead']){
                    $tmphead = "'".implode("','",$_POST['choosehead'])."'";
                    $paymentwhere .= ' AND R.PAYMENTHEAD_ID IN (SELECT ID FROM PAYMENTHEADS WHERE PAYMENTHEAD IN ('.$tmphead.'))';
                }
//                $sql = "(SELECT P.PAYMENTHEAD,0 AS DEBITAMOUNT,SUM(R.AMOUNT) AS CREDITAMOUNT"
//                    . " FROM RECEIPTS R LEFT JOIN PAYMENTHEADS P ON R.HEAD_ID=P.ID WHERE R.HEAD_ID!='DEPOSIT' AND R.STATUS=1 AND R.CID='".$this->session->companyid."'".$where
//                    ." GROUP BY R.HEAD_ID ORDER BY P.PAYMENTHEAD) UNION ALL 
//                    (SELECT P.PAYMENTHEAD,SUM(PY.AMOUNT) AS DEBITAMOUNT,0 AS CREDITAMOUNT
//                    FROM PAYMENTS PY LEFT JOIN PAYMENTHEADS P ON PY.HEAD_ID=P.ID WHERE PY.HEAD_ID!='WITHDRAW' AND PY.CID='".$this->session->companyid."' AND PY.STATUS=1".$paymentwhere
//                    ." GROUP BY PY.HEAD_ID ORDER BY P.PAYMENTHEAD)";
                
               
                
                $sql ='(SELECT AC.PAYMENTHEAD ,0 AS DEBITAMOUNT, SUM(R.AMOUNT) AS CREDITAMOUNT 
FROM TRANSACTIONS R  
JOIN PAYMENTHEADS AC ON AC.ID=R.PAYMENTHEAD_ID 
WHERE '.$where.' GROUP BY R.PAYMENTHEAD_ID ORDER BY AC.PAYMENTHEAD)
UNION ALL
(SELECT AC.PAYMENTHEAD,SUM(R.AMOUNT) AS DEBITAMOUNT,0 AS CREDITAMOUNT
FROM TRANSACTIONS R 
JOIN PAYMENTHEADS AC ON AC.ID=R.PAYMENTHEAD_ID 
WHERE '.$paymentwhere.' GROUP BY R.PAYMENTHEAD_ID ORDER BY AC.PAYMENTHEAD)';

//                echo $sql;exit;

                $profitandlossdata = $this->db->query($sql)->result();
                ?>
                    <table class="table table-striped table-hover table-bordered flip-content">
                        <thead class="flip-content bordered-palegreen">
                            <tr role="row">
                                <th>
                                    <?php echo $this->lang->line("HEADS"); ?>
                                </th>
                                <th style="text-align: right;">
                                    <?php echo $this->lang->line("INCOME"); ?>
                                </th>
                                <th style="text-align: right;">
                                    <?php echo $this->lang->line("EXPENCE"); ?>
                                </th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php 
                            $CREDITAMOUNT = 0;
                            $DEBITAMOUNT = 0;
                            if(isset($profitandlossdata)) {
                                if($profitandlossdata) {
                                    foreach($profitandlossdata as $profitandlossrec) {
                                        $CREDITAMOUNT = $CREDITAMOUNT+$profitandlossrec->CREDITAMOUNT;
                                        $DEBITAMOUNT = $DEBITAMOUNT+$profitandlossrec->DEBITAMOUNT;
                            ?>
                                <tr>
                                    <td>
                                        <?php echo $profitandlossrec->PAYMENTHEAD; ?>
                                    </td>
                                    <td style="text-align: right;">
                                        <?php
                                        if($profitandlossrec->CREDITAMOUNT!=0) {
                                            echo $this->formatslist->formatCurrencywithHash($profitandlossrec->CREDITAMOUNT);
                                        } else {
                                            echo $this->formatslist->formatCurrencywithHash(0);
                                        }
                                        ?>
                                    </td>
                                    <td style="text-align: right;">
                                        <?php
                                        if($profitandlossrec->DEBITAMOUNT!=0) {
                                            echo $this->formatslist->formatCurrencywithHash($profitandlossrec->DEBITAMOUNT);
                                        } else {
                                            echo $this->formatslist->formatCurrencywithHash(0);
                                        }
                                        ?>
                                    </td>
                                </tr>
                            <?php
                                    }
                                }
                            }
                            ?>
                                <tr>
                                    <td></td>
                                    <td style="text-align: right;font-weight: bold;">
                                        <?php
                                            echo $this->formatslist->formatCurrencywithHash($CREDITAMOUNT);
                                        ?>
                                    </td>
                                    <td style="text-align: right;font-weight: bold;">
                                        <?php
                                            echo $this->formatslist->formatCurrencywithHash($DEBITAMOUNT);
                                        ?>
                                    </td>
                                </tr>
                        </tbody>
                        <tfoot>
                            <tr>
                                <td style="text-align: right;font-weight: bold;" colspan="3">
                                    <?php
                                    $balance = $CREDITAMOUNT+$DEBITAMOUNT;
                                    if($balance>=0) {
                                        echo 'Profit/Loss: <span style="color: #11a711;font-size: 16px;">'.$this->formatslist->formatCurrencywithHash($balance).'</span>';
                                    } else {
                                        echo 'Profit/Loss: <span style="color: #e2380f;font-size: 16px;">'.$this->formatslist->formatCurrencywithHash($balance).'</span>';
                                    }
                                    ?>
                                </td>
                            </tr>
                        </tfoot>
                    </table>
                <?php
            }
        }
        
        public function exportPDFData($fromdate='',$todate='',$headtype='',$exporttype=''){ 
		
            if(!$_POST) { 
                echo "Submission Error";exit;
            }
//            $fromdate   =   $_POST["filter_begin"];
//            $todate     =   $_POST["filter_end"];
            $filterdate    =   explode(" - ",$_POST['filter_date']);
            $filterstart    =   $filterdate[0];
            $filterend    =   $filterdate[1];
            if($filterstart) {
                $fromdate   =   date("Y-m-d",strtotime($filterstart));
            }
            if($filterend) {
                $todate     =   date("Y-m-d",strtotime($filterend));
            }
            $headtype   =   $_POST["choosehead"];
            $exporttype =   $_POST["exporttype"];
            $content = $this->fetch_data_for_PDF($fromdate,$todate,$headtype);
            
            $companyinfo = $this->companyinfo_model->getcompanyinfo($this->session->companyid);
            if($companyinfo->DISPLAYLOGO) {
                $DISPLAYLOGO   =   $companyinfo->DISPLAYLOGO;
            } else {
                $DISPLAYLOGO   =   base_url().'assets/img/logo.png';
            }
            if($exporttype=="PDF") {
				
                $path = FCPATH.'application/libraries/vendor/autoload.php';
                require_once $path;
                $mpdf =  new \Mpdf\Mpdf(['tempDir' => '/tmp']);
                $mpdf->setAutoTopMargin='stretch';
                $mpdf->setAutoBottomMargin='stretch';
                $mpdf->SetTitle('P&L Statement');
				
                $headerdata =   '<div>
                    <div style="width:50%;float:left;">
                        <span style="font-size:16px;font-weight:bold;">'.$companyinfo->COMPANYNAME.'</span><br/>
                            '.$companyinfo->ADDRESS1.', '.$companyinfo->ADDRESS2.'<br/>
                            Tel: '.$companyinfo->PHONE1.', '.$companyinfo->PHONE2.'<br/>
                            Email: '.$companyinfo->EMAIL.'<br/>
                            Web: '.$companyinfo->WEBSITE.'<br/>
                            Tax ID: '.$companyinfo->TAXID.'
                    </div>
                    <div style="width:49%;float:right;text-align:right;">
                        <img src="'.$DISPLAYLOGO.'" style="float:right;" />
                    </div>
                </div>';
				
                $mpdf->SetHTMLHeader("<div style='width:100%;'>".$headerdata."</div>");
				$mpdf->WriteHTML($content);
                $mpdf->SetHTMLFooter("<div style='width:100%;text-align:center;'>{PAGENO} / {nbpg}</div>"); 
                $mpdf->Output("P&LStatement.pdf","I");
            } else if($exporttype=="EXCEL") {
                $headerdata =   '<table><tr><td colspan="4">
                    <div style="width:50%;float:left;">
                        <span style="font-size:16px;font-weight:bold;">'.$companyinfo->COMPANYNAME.'</span><br/>
                            '.$companyinfo->ADDRESS1.', '.$companyinfo->ADDRESS2.'<br/>
                            Tel: '.$companyinfo->PHONE1.', '.$companyinfo->PHONE2.'<br/>
                            Email: '.$companyinfo->EMAIL.'<br/>
                            Web: '.$companyinfo->WEBSITE.'<br/>
                            Tax ID: '.$companyinfo->TAXID.'
                    </div></td><td>
                    <div style="width:49%;float:right;text-align:right;">
                        <img src="'.$DISPLAYLOGO.'" style="float:right;" />
                    </div></td>
                </table>';
                $file = "statement.xls";
                header("Content-type: application/vnd.ms-excel");
                header("Content-Disposition: attachment; filename=$file");
                echo $headerdata.$content;
            }
        }

    public function fetch_data_for_PDF($fromdate='',$todate='',$headtype='') {
        $where = "";
        if($fromdate) {
            $where .= ' and R.RECEIPT_DATE >="'.date("Y-m-d",strtotime($fromdate)).'"';
        }
        if($todate) {
            $where .= ' and R.RECEIPT_DATE <="'.date("Y-m-d",strtotime($todate)).'"';
        }
        if($headtype) {
            $tmphead = "'".implode("','",$headtype)."'";
            $where .= ' and (R.HEAD_ID IN ('.$tmphead.'))';
        }
        $paymentwhere = "";
        if($fromdate){
            $paymentwhere .= ' and PY.PAYMENT_DATE >="'.date("Y-m-d",strtotime($fromdate)).'"';
        }
        if($todate){
            $paymentwhere .= ' and PY.PAYMENT_DATE <="'.date("Y-m-d",strtotime($todate)).'"';
        }
        if($headtype){
            $tmphead = "'".implode("','",$headtype)."'";
            $paymentwhere .= ' and (PY.HEAD_ID IN ('.$tmphead.'))';
        }
        $sql = "(SELECT P.PAYMENTHEAD,0 AS DEBITAMOUNT,SUM(R.AMOUNT) AS CREDITAMOUNT"
            . " FROM RECEIPTS R LEFT JOIN PAYMENTHEADS P ON R.HEAD_ID=P.ID WHERE R.HEAD_ID!='DEPOSIT' AND R.STATUS=1 AND R.CID='".$this->session->companyid."' ".$where
            ." GROUP BY R.HEAD_ID ORDER BY P.PAYMENTHEAD) UNION ALL 
            (SELECT P.PAYMENTHEAD,SUM(PY.AMOUNT) AS DEBITAMOUNT,0 AS CREDITAMOUNT
            FROM PAYMENTS PY LEFT JOIN PAYMENTHEADS P ON PY.HEAD_ID=P.ID WHERE PY.HEAD_ID!='WITHDRAW' AND PY.STATUS=1 AND PY.CID='".$this->session->companyid."' ".$paymentwhere
            ." GROUP BY PY.HEAD_ID ORDER BY P.PAYMENTHEAD)";
        
//        echo $sql;exit;
        
        $data = $this->common->customquery($sql);
        $output = '';
        
//                 echo $output;exit;
        $CREDITAMOUNT = 0;
        $DEBITAMOUNT = 0;
        if($data) {
			$style='<style>
				body { font-size:12px; font-family: Arial, Helvetica, sans-serif; }
				#accountdetailtable table,#detailtable table { border-top:1px solid #aaa;border-left:1px solid #aaa;  } 
				#accountdetailtable table td,#detailtable table td,table th { 
				padding-top:10px;
				padding-bottom:10px;
				border-right:1px solid #aaa;border-bottom:1px solid #aaa;
				} 
				</style><div style="width:100%;font-size:18px;text-align:center;padding-bottom:12px;">P&L STATEMENT</div><hr />';
			$accountdetailsdiv = $style;
			$accountdetailsdiv = $accountdetailsdiv.'<br/><div style="width:100%;float:right;text-align:right;">';
			if($fromdate!='-') {
				$accountdetailsdiv = $accountdetailsdiv.' From : <b>'.$this->formatslist->dateFormat($fromdate).'</b>';
			}
			if($todate!='-') {
				$accountdetailsdiv = $accountdetailsdiv.'&nbsp;&nbsp; To : <b>'.$this->formatslist->dateFormat($todate).'</b>';
			} 
			$accountdetailsdiv = $accountdetailsdiv.'&nbsp;&nbsp; Printed On: <b>'.$this->formatslist->datetimeFormat(date('d-m-Y H:i')).'</b>';
			$accountdetailsdiv = $accountdetailsdiv.'</div><br/><div style="width: 100%;" id="detailtable">';
			$output = $accountdetailsdiv.'<table border="0" cellspacing="0" cellpadding="3" style="width: 100%;">  
				<tr>  
					 <th style="text-align:left;" width="110" colspan="4">Heads</th>';  
					 $output.='<th style="text-align:right;" width="100">Income</th>  
					 <th style="text-align:right;" width="100">Expense</th>
				</tr>';
            foreach($data as $profitandlossrec) {
                $CREDITAMOUNT = $CREDITAMOUNT+$profitandlossrec->CREDITAMOUNT;
                $DEBITAMOUNT = $DEBITAMOUNT+$profitandlossrec->DEBITAMOUNT;
                $output .= '<tr>
                <td colspan="4">'.$profitandlossrec->PAYMENTHEAD.'</td>';
                $output .= '<td style="text-align:right;">';
                if($profitandlossrec->CREDITAMOUNT!=0) {
                    $output .= $this->formatslist->formatCurrencywithHash($profitandlossrec->CREDITAMOUNT);
                } else {
                    $output .= $this->formatslist->formatCurrencywithHash(0);
                }
                $output .= '</td>';
                $output .= '<td style="text-align:right;">';
                if($profitandlossrec->DEBITAMOUNT!=0) {
                    $output .= $this->formatslist->formatCurrencywithHash($profitandlossrec->DEBITAMOUNT);
                } else {
                    $output .= $this->formatslist->formatCurrencywithHash(0);
                }
                $output .= '</td>
                </tr>';
            }
            $output .= '<tr><td colspan="4"></td><td style="text-align: right;font-weight: bold;"><b>'
                .$this->formatslist->formatCurrencywithHash($CREDITAMOUNT).'</b></td>'
                .'<td style="text-align:right;"><b>'.$this->formatslist->formatCurrencywithHash($DEBITAMOUNT).'</b></td></tr>';
            $output .= '
                <tr>
                    <td style="text-align: right;font-weight: bold;" colspan="6">';
                        $balance = $CREDITAMOUNT+$DEBITAMOUNT;
                        if($balance>=0) {
                            $output .= 'Profit/Loss: <span style="color: #11a711;font-size: 16px;">'.$this->formatslist->formatCurrencywithHash($balance).'</span>';
                        } else {
                            $output .= 'Profit/Loss: <span style="color: #e2380f;font-size: 16px;">'.$this->formatslist->formatCurrencywithHash($balance).'</span>';
                        }
            $output .= '</td>
                </tr>';
				$output .= '</table></div>';
            return $output;
        }else{
			$output = '<div> No Data </div>';
            return $output;
        }
        
        
    }
}
