<?php
require_once (PATH_CONTROLLERS . "Common.php") ;
require_once (PATH_MODELS . "admin/UserClass.php") ;
require_once (PATH_MODELS . "admin/AuditLogClass.php") ;

require '../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;

class AuditLogReport extends GLBase {
	private $type = "" ;
	
	function __construct() {
		$this->db = $_SESSION[SE_DB] ;
		$this->orgid = $_SESSION[SE_ORGID] ;
	}
	function __destruct() {
		unset($this->db) ;
	}
	public function processRequest($params) {
		$this->type = REQ_VIEW ;
		
		try {
			$this->db->open() ;
			if (isset($params) && count($params) > 0) {
				if (isset($params['type']))
					$this->type = $params['type'] ;
			}
			if (!$this->checkSessionTimeout($this->type)) {
				$this->db->close();
				return false;
			}
			switch ($this->type) {
				case REQ_LIST:
					echo $this->getList($params) ;
					break ;
				case REQ_REPORT:
					$this->getReport($params) ;
					break ;
				case REQ_VIEW:
					$this->getView() ;
					break ;
				case REQ_EXPORT:
					$this->viewDoc($params) ;
					break ;
				default:
					$this->sendJsonResponse(Status::Error,"invalid request.","",$this->type) ;
					break ;
			}
			$this->db->close() ;
			return true ;
		} catch (Exception $e) {
			$this->db->close() ;
			die ($e->getMessage()) ;
		}
	}
    
    private function getUserOption(){
        $cls    = new UserClass($this->db);
        $rows   = $cls->getTable();
        
		$vls 			= array() ;
		
		if(is_null($rows) || count($rows) == 0)
			return null;
		
		foreach ($rows as $row) {
			$vls[] 		= array ('code'=>$row[UserTable::C_ID],'desc'=>$row[UserTable::C_FULL_NAME] . " (" . $row[UserTable::C_NAME] . ")") ;
		}
		return Util::createOptionValue($vls, "") ;
    }
    
    private function viewDoc($params){
		/** Create a new Spreadsheet Object **/
		$spreadsheet = new Spreadsheet();
		$worksheet = $spreadsheet->getActiveSheet();

        $clsUser = new UserClass($this->db);
        
        $userid = $this->getParam($params, 'code', '');
        $start_date = $this->getParam($params, 'date', '');
        $end_date = $this->getParam($params, 'dateend', '');

        $result = "";
        
        $rows = $this->getRows($userid, $start_date, $end_date);
        
        if(!is_null($rows) && count($rows) > 0){
            $style_header = array(
                'font' 			=> array('bold' => true),
                'alignment' 	=> array('horizontal' => Alignment::HORIZONTAL_CENTER,),
                'fill'          => array('fillType' => Fill::FILL_SOLID,'color' => array('rgb' => 'C8C8C8'))
            );
            $style_title = array(
                'font' 			=> array('bold' => true, 'size'=>20),
                'alignment' 	=> array('horizontal' => Alignment::HORIZONTAL_CENTER,)
            );
            
            $columnHeader = array ("Datetime","User","Table","Description");
            
            $title = "Audit Log Report";
            
            $filename = PATH_DOC . $title . ".xlsx";
            
            $idx = 2;
            $worksheet->setCellValue(chr(65) . $idx, $title);
            $worksheet = $spreadsheet->getActiveSheet();
            $worksheet->mergeCells(chr(65).$idx. ':' . chr(65+count($columnHeader)-1) . $idx);
            $worksheet->getStyle(chr(65) . $idx . ":" . chr(65+count($columnHeader)-1) . $idx)->applyFromArray($style_title);
            
            $title = "From " . $params['date'] . " to " . $params['dateend'];
            $idx += 1;
            $worksheet->setCellValue(chr(65) . $idx, $title);
            $worksheet->mergeCells(chr(65).$idx. ':' . chr(65+count($columnHeader)-1) . $idx);
            $worksheet->getStyle(chr(65) . $idx . ":" . chr(65+count($columnHeader)-1) . $idx)->applyFromArray($style_title);
            $worksheet->getStyle(chr(65) . $idx . ":" . chr(65+count($columnHeader)-1) . $idx)->getFont()->setSize(14);
            
            
            $idx += 2;
            for($i=0;$i<count($columnHeader);$i++){
                $worksheet->setCellValue(chr(65+$i) . $idx, $columnHeader[$i]);
            }
            $worksheet->getStyle(chr(65) . $idx . ":" . chr(65+count($columnHeader)-1) . $idx)->applyFromArray($style_header);
            $worksheet->getStyle(chr(65) . $idx . ":" . chr(65+count($columnHeader)-1) . $idx)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THICK);
            
            $idx += 1;
            $startIdx = $idx;
            
            foreach($rows as $row){
                $columnContent = array();
                
                $columnContent[] = (new DateTime($row[AuditLogTable::C_DATE]))->format('d/m/Y');
                $columnContent[] = $row[UserTable::C_FULL_NAME];
                $columnContent[] = $row[AuditLogTable::C_LOG_TABLE];
                $columnContent[] = $row[AuditLogTable::C_DESC];
                
                for($i=0;$i<count($columnContent);$i++){
                    $worksheet->setCellValue(chr(65 + $i) . $idx, $columnContent[$i]);
                }
                
                $idx += 1;
            }
            
            $worksheet->getStyle(chr(65) . $startIdx . ":" . chr(65+count($columnHeader)-1) . ($idx-1))->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
            
            // Write a new .xlsx file
            $writer = new Xlsx($spreadsheet);
            // Save the new .xlsx file
            $writer->save($filename);

            header('Content-Description: File Transfer');
            header('Content-Type: application/force-download');
            header('Content-Disposition: attachement; filename="' . basename($filename) . '"');
            header('Content-Transfer-Encoding: binary');
            header('Expires: 0');
            header('Cache-Control: must-revalidate');
            header('Pragma: public');
            header('Content-Length: ' . filesize($filename));
            ob_clean();
            flush();
            readfile($filename);
        } else {
            echo "NO RESULT FOUND.";
        }
        
        unset($clsUser);
        
        
        return $result;
    }
    
    private function getList($params){
        $clsUser = new UserClass($this->db);
        
        $userid = $this->getParam($params, 'code', '');
        $start_date = $this->getParam($params, 'start', '');
        $end_date = $this->getParam($params, 'end', '');
        
        $result = "";
        $rows = $this->getRows($userid, $start_date, $end_date);
        $columnContent = [];
        if(!is_null($rows) && count($rows) > 0){
            foreach ($rows as $i => $row) {

                $columnContent[$i]['Datetime'] = (new DateTime($row[AuditLogTable::C_DATE]))->format('d/m/Y');
                $columnContent[$i]['User'] = $row[UserTable::C_FULL_NAME];
                $columnContent[$i]['Table'] = $row[AuditLogTable::C_LOG_TABLE];
                $columnContent[$i]['Description'] = $row[AuditLogTable::C_DESC];
            }
        }
        
        unset($clsUser);
        
        return json_encode($columnContent) ;
    }
	
	private function getView() {
		ob_start() ;
		include (PATH_VIEWS . "admin/AuditLogReportView.php") ;
		echo Util::minifyHtml(ob_get_clean()) ;
	}
    
    private function createHeaderPdf($pdf, $columnSize){
            //Column Header
        $pdf->SetFillColor(200, 200, 200);
        $pdf->SetFont('freeserif', 'b', 11);
        $border = "TBLR";
        
        $pdf->MultiCell($columnSize[0], 5, "Preparer", $border, 'C'           , 1, 0, '', '', true);
        $pdf->MultiCell($columnSize[1], 5, "Preparer", $border, 'C'           , 1, 0, '', '', true);
        $pdf->MultiCell($columnSize[2], 5, "Preparer", $border, 'C'           , 1, 0, '', '', true);
        $pdf->MultiCell($columnSize[3], 5, "Preparer", $border, 'C'           , 1, 1, '', '', true);
        
        return $pdf;
    }
    
	private function getReport($params) {
        $clsUser = new UserClass($this->db);
        
        $userid = $this->getParam($params, 'code', '');
        $start_date = $this->getParam($params, 'date', '');
        $end_date = $this->getParam($params, 'dateend', '');
        
       $result = "";
        $rows = $this->getRows($userid, $start_date, $end_date);

        if(!is_null($rows) && count($rows) > 0){
            
            require_once(PATH_PDF . 'tcpdf.php');

            // create new PDF document
            $pdf = new TCPDF('L', PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
            $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 font
            $pdf->SetFont($this->fontname, '', 10);

            // add a page
            $pdf->AddPage();

            // set cell padding
            $pdf->setCellPaddings(1, 1, 1, 1);
            $pdf->SetFillColor(255, 255, 255);
            
            $columnSize = array(40, 40, 40, 150);
            
            //Header
            
            $title = "Audit Log Report";
            $pdf->SetFont('freeserif', 'b', 25);
            $pdf->MultiCell(265, 5, $title, '', 'C'           , 1, 1, '', '', true);
            $pdf->SetFont($this->fontname, '', 15);
            $end_date = $params['dateend'] != "" ? $params['dateend']:date('m/d/Y');
            $pdf->MultiCell(265, 5, "From " . $params['date'] . " to " . $end_date, '', 'C'           , 1, 1, '', '', true);
            
            
            $pdf->Ln(5);
            
            $pdf->SetFillColor(255, 255, 255);
            $pdf->SetFont($this->fontname, '', 9);
            $rowHeight = 4.5;
            
            $count = 1;
            $page = 0;
            foreach($rows as $row){
                
                $border = "LR";
                
                if ($count == count($rows)) $border .= "B";
                
                
                $columnContent = array();
                
                $columnContent[] = (new DateTime($row[AuditLogTable::C_DATE]))->format('d/m/Y');
                $columnContent[] = $row[UserTable::C_FULL_NAME];
                $columnContent[] = $row[AuditLogTable::C_LOG_TABLE];
                $columnContent[] = $row[AuditLogTable::C_DESC];
                $numlines = 0;
                for ($i=0;$i<count($columnSize);$i++){
                    $temp = $pdf->getNumLines($columnContent[$i], $columnSize[$i]);
                    if ($numlines < $temp) $numlines = $temp;
                } 
                
                if ($pdf->GetY() + $numlines * $rowHeight > $pdf->getPageHeight() - $pdf->getMargins()['bottom']){
                    $pdf->AddPage();
                }
                
                
                if ($page != $pdf->getPage()){
                    $page = $pdf->getPage();
            
                    $pdf = $this->createHeaderPdf($pdf, $columnSize);
                }
                
                $pdf->SetFillColor(255, 255, 255);
                $pdf->SetFont($this->fontname, '', 8);
                
                
                for ($i=0;$i<count($columnSize);$i++){
                    $ln = ($i ==  count($columnSize) - 1 ? 1 : 0);
                    
                    $pdf->MultiCell($columnSize[$i], $numlines * $rowHeight, $columnContent[$i], $border, 'L'           , 1, $ln, '', '', true);
                }
                $count += 1;
            }
            
            $pdf->lastPage();
            ob_end_clean();
            $pdf->Output('preview.pdf', 'I');
        } else {
            echo "<p style='font-family:freeserif;'>No result found.</p>";
        }
        Log::write("[AuditLogReportGetData]" . $e->getMessage());
        unset($clsUser);
        
	}
    
    private function getRows($userid, $start_date, $end_date){
        $result = null;
        
        $sql = " SELECT A.*, U." . UserTable::C_FULL_NAME . ", U." . UserTable::C_NAME . " " .
                " FROM " . AuditLogTable::C_TABLE . " A " . 
                " JOIN " . UserTable::C_TABLE . " U ON (A." . AuditLogTable::C_USER . " = U." . UserTable::C_ID . ") ";
        
        if (trim($userid) != ''){
            $sql .= " AND A." . AuditLogTable::C_USER . " = " . $userid . " ";
        }
        if (trim($start_date) != ''){
            $start_date = (new DateTime($start_date))->format('Y-m-d');
            $sql .= " AND A." . AuditLogTable::C_DATE . " >= '" . trim($start_date) . " 00:00:00.000' ";
        }
        if (trim($end_date) != ''){
            $end_date = (new DateTime($end_date))->format('Y-m-d');
            $sql .= " AND A." . AuditLogTable::C_DATE . " <= '" . trim($end_date) . " 23:59:59.000' ";
        }
        $sql .= " ORDER BY A." . AuditLogTable::C_DATE . " ";
        try{
            $result = $this->db->getTable($sql);
        } catch (Exception $e){
            Log::write("[AuditLogReportGetData]" . $e->getMessage());
            return null;
        }
        
        return $result;
    }
}
?>