<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');
class Suppliersdiary extends MY_Controller {
    function __construct() {
        parent::__construct();
        $this->load->library('Formatslist');
    }
    
    function index() {
        if($_POST) {
            $sql = "SELECT A.ID, A.AMOUNT, A.CREATED AS CREATED,A.STATUS,S.ID as SUPPLIERID,S.NAME AS SUPPLIERNAME , A.TRANSACTION_DATE,P.REFERENCE
                FROM TRANSACTIONS AS A
                JOIN PURCHASEINVOICE_PAYMENTS AS PP ON PP.TRANSACTION_ID=A.ID
                JOIN PURCHASEINVOICE AS P ON P.ID=PP.PURCHASEINVOICE_ID
                JOIN SUPPLIERS AS S ON S.ID=P.SUPPLIERID
                WHERE A.STATUS=1";
            if($_POST["searchkey"])
                $sql = $sql." AND S.SEARCHKEY='".$_POST["searchkey"]."'";
            if($_POST["suplrname"])
                $sql = $sql." AND S.NAME like'%".$_POST["suplrname"]."%'";
            if($_POST["fromdate"])
                $sql = $sql." AND A.TRANSACTION_DATE>='".date("Y-m-d H:i:s",strtotime($_POST["fromdate"]." 00:00:00"))."'";
            if($_POST["todate"])
                $sql = $sql." AND A.TRANSACTION_DATE<='".date("Y-m-d H:i:s",strtotime($_POST["todate"]." 23:59:59"))."'";
            $sql = $sql." ORDER BY S.ID , A.TRANSACTION_DATE";
            $data["resultdata"] = $this->common->customquery($sql);
            $data["sqlquery"] = $sql;
        }
        $data['template'] = 'reports/supplierdiary';
        $this->load->view('template', $data);
    }
    
    function exportexceldata() {
        if (isset($_POST)) {
            if (isset($_POST["excelexport"])) {
                $file = "Customerdiary-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">
                        <th style="text-align:left;">Date</th>
                        <th style="text-align:left;">Invoice Ref.</th>
                        <th style="text-align:left;">Invoice Type</th>
                        <th style="text-align:right;">Amount</th>
                        <th style="text-align:right;">Balance</th>
                    </tr>
                </thead>
                <tfoot>
                </tfoot>
                <tbody>';
            if(isset($resultdata)) {
                if($resultdata) {
                    $supplierid   =   '';
                    foreach($resultdata as $result) {
                        if($supplierid!=$result->SUPPLIERID) {
                            $tabledata      =   $tabledata.'<tr role="row"><td colspan="5"><hr/><b>'.$result->SUPPLIERNAME.'</b></td></tr>';
                            $supplierid   =   $result->SUPPLIERID;
                            $sql = "SELECT SUM(A.AMOUNT) AS TOTALAMOUNT FROM TRANSACTIONS AS A JOIN PURCHASEINVOICE_PAYMENTS AS PP ON PP.TRANSACTION_ID=A.ID
                        JOIN PURCHASEINVOICE AS P ON P.ID=PP.PURCHASEINVOICE_ID JOIN SUPPLIERS AS S ON S.ID=P.SUPPLIERID WHERE A.STATUS=1"
                        . " AND S.ID='".$result->SUPPLIERID."'";
                            if($_POST["hiddenfromdate"])
                                $sql = $sql." AND A.TRANSACTION_DATE<'".date("Y-m-d",strtotime($_POST["hiddenfromdate"]." 00:00:00"))."'";
                            $totalamount = $this->common->customquery($sql);
                            $tabledata      =   $tabledata.'<tr role="row"><td colspan="4"> <b>'
                                . 'Opening Balance as on '.date("d-M-Y",strtotime($_POST["hiddenfromdate"]." -1 day")).'</b></td>'
                                . '<td style="text-align:right;">'.$this->formatslist->formatCurrencywithHash($totalamount[0]->TOTALAMOUNT).'</td></tr>';
                            $balancedeflect =   $totalamount[0]->TOTALAMOUNT;
                        }
                        $balancedeflect =   $balancedeflect+$result->AMOUNT;
                        $tabledata      =   $tabledata.'<tr role="row">
                            <td>'.$this->formatslist->dateFormat($result->TRANSACTION_DATE).'</td>
                            <td>'.$result->REFERENCE.'</td>
                            <td>';
                            if($result->AMOUNT>0)
                                $tabledata      =   $tabledata.'Purchase Invoice';
                            else if($result->AMOUNT<0)
                                $tabledata      =   $tabledata."Purchase Invoice Return";
                        $tabledata      =   $tabledata.'</td>
                            <td style="text-align:right;">'.$this->formatslist->formatCurrency($result->AMOUNT).'</td>
                            <td style="text-align:right;">'.$this->formatslist->formatCurrency($balancedeflect).'</td>
                        </tr>';
                    }
                }
            }
            $tabledata      =   $tabledata.'</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 Diary');
                $pdf->SetSubject('Customer Diary');
                $pdf->SetHeaderData(K_BLANK_IMAGE, PDF_HEADER_LOGO_WIDTH, $data['companyinfo']->COMPANYNAME."#Customer Diary", $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('customerdiary-001.pdf', 'D');
            }
        }
    }
    
}