<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');
class Customerwarning extends MY_Controller {
    function __construct() {
        parent::__construct();
        $this->load->library('Formatslist');
        $this->load->model('partner/customers_model');
    }
    
    function index() {
        if($_POST) {
            $sql = "SELECT C.ID AS CUSTID,MAX(R.DATENEW) AS MAXDATENEW FROM RECEIPTS R JOIN TICKETS T"
                ." JOIN CUSTOMERS C ON T.ID=R.ID AND C.ID=T.CUSTOMER AND ROUND(C.CURDEBT,1)>0"
                ." JOIN PAYMENTS P ON P.RECEIPT=R.ID AND (P.PAYMENT='debtpaid')";
            if($_POST["custcategory"]) 
                $sql = $sql." WHERE C.TAXCATEGORY='".$_POST["custcategory"]."'";
            $sql = $sql." GROUP BY C.ID";
            $data["resultdata"] = $this->common->customquery($sql);
            $data["sqlquery"] = $sql;
        }
        
        $data['custArr']    =   $this->customers_model->getCustomerCategories();
        $data['template'] = 'reports/customerwarninglist';
        $this->load->view('template', $data);
    }
    
    function exportexceldata() {
        if (isset($_POST)) {
            if (isset($_POST["excelexport"])) {
                $file = "Customerwarning-001.xls";
                header("Content-type: application/vnd.ms-excel");
                header("Content-Disposition: attachment; filename=$file");
            }
            $resultdata =   $this->common->customquery($_POST["hiddensqlquery"]);
            $tabledata  =   '<table class="table table-striped table-hover table-bordered" id="reportdatatable">
                    <thead>
                        <tr role="row" style="border-bottom:1px solid #000;">
                            <th style="text-align:left;padding:3px;">Search Key</th>
                            <th style="text-align:left;padding:3px;">Category</th>
                            <th style="text-align:left;padding:3px;">Name</th>
                            <th style="text-align:right;padding:3px;">Card</th>
                            <th style="text-align:right;padding:3px;">Max.Debt</th>
                            <th style="text-align:right;padding:3px;">Last Trn. Date</th>
                            <th style="text-align:right;padding:3px;">Current Debt</th>
                        </tr>
                    </thead>
                    <tfoot></tfoot>
                <tbody>';
            if(isset($resultdata)) {
$totcurdebt	=	0;
                if($resultdata) {
                    foreach($resultdata as $result) {
                        $sql1 = "SELECT C.ID AS CUSTID,C.SEARCHKEY,C.NAME,R.DATENEW AS MAXDATENEW ,P.PAYMENT, C.CARD, C.MAXDEBT, C.CURDATE ,"
                            . "C.CURDEBT,P.PAYMENT,C.TAXCATEGORY,TC.NAME AS TAXCATEGORYNAME FROM RECEIPTS R JOIN TICKETS T"
                            ." JOIN CUSTOMERS C ON T.ID=R.ID AND C.ID=T.CUSTOMER "
                            ." JOIN PAYMENTS P ON P.RECEIPT=R.ID AND (P.PAYMENT='debt') LEFT JOIN TAXCUSTCATEGORIES TC ON TC.ID=C.TAXCATEGORY"
                            . " WHERE C.ID='".$result->CUSTID."' AND R.DATENEW>'".$result->MAXDATENEW."'"
                            . " ORDER BY R.DATENEW ASC LIMIT 0,1";
                        $custrec = $this->common->customquery($sql1);
                        if($custrec) {
                            $now = time();
                            $your_date = strtotime($custrec[0]->MAXDATENEW);
                            $datediff = $now - $your_date;
                            $daysdif    =   round($datediff / (60 * 60 * 24));
                            if($daysdif>=$_POST["hiddendayssearch"]) {
				if (isset($_POST["excelexport"])) {
					//
				} else {
                                	$tabledata  =   $tabledata.'<tr role="row"><td colspan="7"><hr/></td></tr>';
				}
                                $tabledata  =   $tabledata.'<tr role="row">
                                    <td style="text-align:left;padding:3px;">'.$custrec[0]->SEARCHKEY.'</td>
                                    <td style="text-align:left;padding:3px;">'.$custrec[0]->TAXCATEGORYNAME.'</td>
                                    <td style="text-align:left;padding:3px;">'.$custrec[0]->NAME.'</td>
                                    <td style="text-align:right;padding:3px;">'.$custrec[0]->CARD.'</td>
                                    <td style="text-align:right;padding:3px;">'.$this->formatslist->formatCurrency($custrec[0]->MAXDEBT).'</td>
                                    <td style="text-align:right;padding:3px;">'.$this->formatslist->datetimeFormat($custrec[0]->CURDATE).'</td>
                                    <td style="text-align:right;padding:3px;">'.$this->formatslist->formatCurrency($custrec[0]->CURDEBT).'</td>
                                </tr>';
$totcurdebt	=	$custrec[0]->CURDEBT+$totcurdebt;
                            }
                        }
                    }
		$tabledata  =   $tabledata.' <tr><td></td><td></td><td></td><td></td><td></td><td></td><td><b>'.$this->formatslist->formatCurrency($totcurdebt).'</b></td></tr></tbody></table>';
                }
            }
            if (isset($_POST["excelexport"])) {
                echo $tabledata;
            } else if (isset($_POST["pdfxport"])) { 
                $this->load->model('settings/companyinfo_model');
                $data['companyinfo'] = $this->companyinfo_model->getcompanyinfo('0');
                $data['tabledata'] = $tabledata;
                $tabledata = $this->load->view('reports/report_print', $data, true);
                $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('John Mathew');
                $pdf->SetTitle('Customer Warning List');
                $pdf->SetSubject('Customer Warning List');
                $pdf->SetHeaderData(K_BLANK_IMAGE, PDF_HEADER_LOGO_WIDTH, $data['companyinfo']->COMPANYNAME."#Customer Warning List", $data['companyinfo']->ADDRESS1."\nPhone: ".$data['companyinfo']->PHONE1."\n".$data['companyinfo']->EMAIL.', '.$data['companyinfo']->WEBSITE);
                // set header and footer fonts
                $pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
                $pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
                // set default monospaced font
                $pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
                //set margins
                $pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
                $pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
                $pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
                //set auto page breaks
                $pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
                //set image scale factor
                $pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
                // ---------------------------------------------------------
                // set font
                $pdf->SetFont('helvetica', 'B', 11);
                // add a page
                //$pdf->AddPage();
                $pdf->AddPage();
                $pdf->writeHTML($tabledata, true, false, true, false, '');
                // Print some HTML Cells
                // reset pointer to the last page
                $pdf->lastPage();
                // ---------------------------------------------------------
                //Close and output PDF document
                $pdf->Output('customerwarninglist-001.pdf', 'D');
            }

        }
    }
}