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

class ResetPasswordHistoryReport extends ActivityBase {
	private $type = "" ;
	
	function __construct() {
		$this->db = $_SESSION[SE_DB] ;
		$this->dbsun = $_SESSION[SE_DB_SUN] ;
		$this->orgid = $_SESSION[SE_ORGID] ;
		$this->fldorg = RequestHeaderTable::C_ORG_ID ;
	}
	function __destruct() {
		unset($this->db) ;
	}
	public function processRequest($params) {
		$this->type = REQ_VIEW ;
		
		try {
			$this->db->open() ;
			$this->dbsun->open() ;
			if (isset($params) && count($params) > 0) {
				if (isset($params['type']))
					$this->type = $params['type'] ;
			}
			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() ;
			$this->dbsun->close() ;
			return true ;
		} catch (Exception $e) {
			$this->db->close() ;
			$this->dbsun->close() ;
			die ($e->getMessage()) ;
		}
	}
    
    private function getResetTypeOption(){
		$vls        = array() ;
        $vls[] 		= array ('code'=>-1,'desc'=>'All') ;
        $vls[] 		= array ('code'=>0,'desc'=>'Backend Manual') ;
        $vls[] 		= array ('code'=>1,'desc'=>'Self Email Sending') ;
		
		return Util::createOptionValue($vls, "") ;
    }
    private function getUserOption(){
		$vls = $this->getValueList(UserTable::C_TABLE, UserTable::C_ID, UserTable::C_FULL_NAME,array('code'=>'0','desc'=>'All User')) ;
		return Util::createOptionValue($vls) ;
    }
    
    private function viewDoc($params){
    
        $user = $this->getParamInt($params, 'coy', 0);
        $target = $this->getParamInt($params, 'dept', 0);
        $reset_type = $this->getParam($params, 'tn', -1);
        $start_date = $this->getParamDate($params, 'date', '');
        $end_date = $this->getParamDate($params, 'dateend', '');
        
        $rows = $this->getRows($user, $target, $reset_type, $start_date, $end_date);

        if(!is_null($rows) && count($rows) > 0){
            $style_header = array(
                'font' 			=> array('bold' => true),
                'alignment' 	=> array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,),
                'fill'          => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'C8C8C8'))
            );
            $style_title = array(
                'font' 			=> array('bold' => true, 'size'=>20),
                'alignment' 	=> array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,)
            );

            $excel = new PHPExcel();
            
            $columnHeader = array ("Resetter","Target","Type","Date"
            );
            $idx = 2;
            $title = "Reset Password History Report";
            $filename = PATH_CLAIM_EXPORT . $title . ".xlsx";
            $excel->setActiveSheetIndex(0)->setCellValue(chr(65) . $idx, $title);
            $excel->getActiveSheet()->mergeCells(chr(65).$idx. ':' . chr(65+count($columnHeader)-1) . $idx);
            $excel->getActiveSheet()->getStyle(chr(65) . $idx . ":" . chr(65+count($columnHeader)-1) . $idx)->applyFromArray($style_title);
            
            $title = "From " . $params['date'] . " to " . $params['dateend'];
            $idx += 1;
            $excel->setActiveSheetIndex(0)->setCellValue(chr(65) . $idx, $title);
            $excel->getActiveSheet()->mergeCells(chr(65).$idx. ':' . chr(65+count($columnHeader)-1) . $idx);
            $excel->getActiveSheet()->getStyle(chr(65) . $idx . ":" . chr(65+count($columnHeader)-1) . $idx)->applyFromArray($style_title);
            $excel->getActiveSheet()->getStyle(chr(65) . $idx . ":" . chr(65+count($columnHeader)-1) . $idx)->getFont()->setSize(14);
            
            $idx += 2;
            for($i=0;$i<count($columnHeader);$i++){
                $excel->setActiveSheetIndex(0)->setCellValue(chr(65+$i) . $idx, $columnHeader[$i]);
            }
            $excel->setActiveSheetIndex(0)->getStyle(chr(65) . $idx . ":" . chr(65+count($columnHeader)-1) . $idx)->applyFromArray($style_header);
            $excel->getActiveSheet()->getStyle(chr(65) . $idx . ":" . chr(65+count($columnHeader)-1) . $idx)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
            
            $idx += 1;
            $startIdx = $idx;
            
            foreach($rows as $row){
                
                $columnContent = array (
                    $row["RESETTER_NAME"],
                    $row["TARGET_NAME"],
                    $row[UserResetPasswordHistoryTable::C_TYPE] == 0 ? "Backend Manual" : "Self Email Sending",
                    (new DateTime($row[UserResetPasswordHistoryTable::C_DATE]))->format('d/m/Y H:i:s')
                );
                
                for($i=0;$i<count($columnContent);$i++){
                    $excel->setActiveSheetIndex(0)->setCellValue(chr(65 + $i) . $idx, $columnContent[$i]);
                }
                
                $idx += 1;
            }
            
            $excel->getActiveSheet()->getStyle(chr(65) . $startIdx . ":" . chr(65+count($columnHeader)-1) . ($idx-1))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

            $objWriter = new PHPExcel_Writer_Excel2007($excel);
            $objWriter->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.";
        }
        
    }
    
    private function getList($params){
        
        $user = $this->getParamInt($params, 'user', 0);
        $target = $this->getParamInt($params, 'user_target', 0);
        $reset_type = $this->getParamInt($params, 'reset_type', -1);
        $start_date = $this->getParamDate($params, 'start', '');
        $end_date = $this->getParamDate($params, 'end', '');
        
        $result = "";
        
        $rows = $this->getRows($user, $target, $reset_type, $start_date, $end_date);
        $reqCode = "";
        if(!is_null($rows) && count($rows) > 0){
            foreach($rows as $row){
                
                $columnContent = array (
                    $row["RESETTER_NAME"],
                    $row["TARGET_NAME"],
                    $row[UserResetPasswordHistoryTable::C_TYPE] == 0 ? "Backend Manual" : "Self Email Sending",
                    (new DateTime($row[UserResetPasswordHistoryTable::C_DATE]))->format('d/m/Y H:i:s')
                );
                
                $result .= "<tr>"; 
                
                for($i=0;$i<count($columnContent);$i++){
                    $result .= "<td>" . $columnContent[$i] . "</td>";
                }
                $result .= "</tr>";
                
                $showHeader = false;
            }
        } else {
            $result = "<tr><td colspan=8> NO RESULT FOUND.</td></tr>";
        }
        
        return $result;
    }
	
	private function getView() {
		ob_start() ;
		include (PATH_VIEWS . "admin/ResetPasswordHistoryReportView.php") ;
		echo Util::minifyHtml(ob_get_clean()) ;
	}
	
	public function generateOptions($tableName, $keyField, $descField, $textHeading, $default="", $isDesciption = true, $orderby=null, $isnumber=false) {
		$filter 	= array();
		$filter[] 	= array('field'=>$this->fldorg,'value'=>$this->orgid) ;
		$vls 		= $this->getValueList($tableName, $keyField, $descField,null,$filter, $orderby) ;
		if($isDesciption)
			$vls 	= $this->getValueList($tableName, $keyField, $descField,array('code'=>'-1','desc'=>$textHeading),$filter, $orderby) ;
		return Util::createOptionValue($vls, $default, $isnumber) ;
	}
	
	public function getClaimGroup($default="", $isDesciption = true, $isChooseOne = true){
		$textChooseHead = "--- Select Claim Group ---";
		if($isChooseOne == false)
			$textChooseHead = "All";
		return $this->generateOptions(ClaimGroupTable::C_TABLE, ClaimGroupTable::C_ID, ClaimGroupTable::C_DESC, 
										"$textChooseHead", $default, $isDesciption);
	}
    
    private function createHeaderPdf($pdf, $columnSize){
        //Column Header
        $pdf->SetFillColor(200, 200, 200);
        $pdf->SetFont('times', 'b', 12);
        $border = "TBLR";
        $pdf->MultiCell($columnSize[0], 8, "Resetter", $border, 'C'           , 1, 0, '', '', true);
        $pdf->MultiCell($columnSize[1], 8, "Target", $border, 'C'           , 1, 0, '', '', true);
        $pdf->MultiCell($columnSize[2], 8, "Type", $border, 'C'           , 1, 0, '', '', true);
        $pdf->MultiCell($columnSize[3], 8, "Date", $border, 'C'           , 1, 1, '', '', true);
        
        return $pdf;
    }
	
	private function getReport($params=null) {
        
        $user = $this->getParamInt($params, 'coy', 0);
        $target = $this->getParamInt($params, 'dept', 0);
        $reset_type = $this->getParam($params, 'tn', -1);
        $start_date = $this->getParamDate($params, 'date', '');
        $end_date = $this->getParamDate($params, 'dateend', '');
        
        $rows = $this->getRows($user, $target, $reset_type, $start_date, $end_date);

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

            // create new PDF document
            $pdf = new TCPDF('P', 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('times', '', 10);

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

            // set cell padding
            $pdf->setCellPaddings(1, 1, 1, 1);
            $pdf->SetFillColor(255, 255, 255);
            $columnSize = array(55, 55, 40, 30);
            
            
            //Header
            
            $pdf->SetFont('times', 'b', 20);
            $pdf->MultiCell(0, 5, "Reset Password History Report", '', 'C'           , 1, 1, '', '', true);
            $pdf->SetFont('times', '', 15);
            $pdf->MultiCell(0, 5, "From " . $params['date'] . " to " . $params['dateend'], '', 'C'           , 1, 1, '', '', true);
            
            $pdf->Ln(5);
            //$pdf->SetY(60);
            
            //Column Header
            $pdf->SetFillColor(200, 200, 200);
            $pdf->SetFont('times', 'b', 12);
            $border = "TBLR";
            $pdf->MultiCell($columnSize[0], 8, "Resetter", $border, 'C'           , 1, 0, '', '', true);
            $pdf->MultiCell($columnSize[1], 8, "Target", $border, 'C'           , 1, 0, '', '', true);
            $pdf->MultiCell($columnSize[2], 8, "Type", $border, 'C'           , 1, 0, '', '', true);
            $pdf->MultiCell($columnSize[3], 8, "Date", $border, 'C'           , 1, 1, '', '', true);
            
            $pdf->SetFillColor(255, 255, 255);
            $pdf->SetFont('times', '', 8);
            
            $count = 1;
            $page = 0;
            $rowHeight = 6;
            foreach($rows as $row){
                
                $border = "LRTB";
                //if ($count == count($rows)) $border .= "B";
                
                $columnContent = array (
                    $row["RESETTER_NAME"],
                    $row["TARGET_NAME"],
                    $row[UserResetPasswordHistoryTable::C_TYPE] == 0 ? "Backend Manual" : "Self Email Sending",
                    (new DateTime($row[UserResetPasswordHistoryTable::C_DATE]))->format('d/m/Y H:i:s')
                );
                
                $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('times', '', 8);
                
                $pdf->MultiCell($columnSize[0], $numlines * $rowHeight, $columnContent[0], $border, 'L'           , 1, 0, '', '', true);
                $pdf->MultiCell($columnSize[1], $numlines * $rowHeight, $columnContent[1], $border, 'L'           , 1, 0, '', '', true);
                $pdf->MultiCell($columnSize[2], $numlines * $rowHeight, $columnContent[2], $border, 'C'           , 1, 0, '', '', true);
                $pdf->MultiCell($columnSize[3], $numlines * $rowHeight, $columnContent[3], $border, 'C'           , 1, 1, '', '', true);
                
                $count += 1;
            }
            
            $pdf->lastPage();
            $pdf->Output('preview.pdf', 'I');
        } else {
            echo "No result found.";
        }
        
        unset($clsCompany);
        unset($clsRequestDetail);
        
        
	}
    
    private function getRows($user, $target, $reset_type, $start_date, $end_date){
        $result = null;
        
        $sql = " SELECT M.*, R." . UserTable::C_FULL_NAME . " AS [RESETTER_NAME], T." . UserTable::C_FULL_NAME . " AS [TARGET_NAME] " . 
                " FROM " . UserResetPasswordHistoryTable::C_TABLE . " M " . 
                " LEFT OUTER JOIN " . UserTable::C_TABLE . " R ON (R." . UserTable::C_ID . " = M." . UserResetPasswordHistoryTable::C_ID . ") " . 
                " LEFT OUTER JOIN " . UserTable::C_TABLE . " T ON (T." . UserTable::C_ID . " = M." . UserResetPasswordHistoryTable::C_ID_TARGET . ") ";
        $sql .= " WHERE M." . UserResetPasswordHistoryTable::C_DATE . " >= '" . trim($start_date) . " 00:00:00.000' ";
        $sql .= " AND M." . UserResetPasswordHistoryTable::C_DATE . " <= '" . trim($end_date) . " 23:59:59.000' ";
        
        if ($user != 0){
            $sql .= " AND M." . UserResetPasswordHistoryTable::C_ID . " = " . $user . " ";
        }
        if ($target != 0){
            $sql .= " AND M." . UserResetPasswordHistoryTable::C_ID_TARGET . " = " . $target . " ";
        }
        if ($reset_type != -1){
            $sql .= " AND M." . UserResetPasswordHistoryTable::C_TYPE . " = " . $reset_type . " ";
        }
        
        $sql .= " ORDER BY M." . UserResetPasswordHistoryTable::C_DATE . ", R." . UserTable::C_FULL_NAME . ", T." . UserTable::C_FULL_NAME . " ";
        
        try{
            $result = $this->db->getTable($sql);
        } catch (Exception $e){
            Log::write("[ResetPasswordHistoryReportGetData]" . $e->getMessage());
            return null;
        }
        
        return $result;
    }
}
?>