<?php
include "../../Classes/PHPExcel.php";
include('../../includes/function_db.php');

$title = "ข้อมูลการปฏิบัติราชการพิเศษ";
$tbname = "disciplinary";
$colhead = array("ที่", "พ.ศ.", "สถานโทษ", "ลักษณะความผิด");
$cdb = new control_db();


$objPHPExcel = new PHPExcel();
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());

$objPHPExcel->getProperties()->setCreator("NEEC-Team")
    ->setLastModifiedBy("NEEC-Team")
    ->setTitle("exam_field")
    ->setSubject("exam_field")
    ->setDescription("exam_field")
    ->setKeywords("exam excel php nfe onie download")
    ->setCategory("exam_field");

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);

$sql = "select person.FirstName,person.LastName ";
$sql .= ",prefix.title ";
$sql .= " from person ";
$sql .= " inner join prefix on prefix.code=person.PrefixCode ";
$sql .= " where person.id='$_GET[s_person_id]' ";
$query = $cdb->conn->prepare($sql);
try {
    $query->execute();
    $person = $query->fetch(PDO::FETCH_ASSOC);
    $nm = $person['title'] . $person['FirstName'] . ' ' . $person['LastName'];
} catch (PDOException $er) {
    echo "Error :" . $er->getMessage();
}

$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A1', $title)
    ->setCellValue('A2', $nm)
    ->setCellValue('A3', 'ข้อมูล ณ วันที่ ' . date('d-m-') . (date('Y') + 543) . date(' h:i:s'));
$col = 'A';
foreach ($colhead as $key => $value) {
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($col . '4', $value);
    ++$col;
}
//ดึงข้อมูลโทษ
$sql = "select id, title from penalty_type";
$query = $cdb->conn->prepare($sql);
try {
    $query->execute();
    $count = $query->rowCount();
    while ($data = $query->fetch(PDO::FETCH_ASSOC)) {
        $sjt[$data['id']] = $data['title'];
    }
} catch (PDOException $er) {
    echo 'Error :' . $er->getMessage();
}
//print_r($sjt);
//จบข้อมูลโทษ
$sql = "select year,penalty,title ";
$sql .= " from $tbname where 1=1 ";
foreach ($_GET as $key => $value) {
    $key = str_replace('pdf_', '', $key);
    if (!in_array($key, $no_field)) {
        if ($value <> '') {
            $field = str_replace('s_', '', $key);
            $param[$field] = "%$value%";
            $sql .= " and $field like :$field ";
        }
    }
}

$sql .= " order by id ";
//echo $sql;
$query = $cdb->conn->prepare($sql);
try {
    $query->execute($param);
    $count = $query->rowCount();
    $row = 5;
    $i = 1;
    while ($data = $query->fetch(PDO::FETCH_ASSOC)) {
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $row, $i);

        $col = 'B';
        foreach ($data as $key => $value) {
            if ($key == 'penalty') {
                $value = $sjt[$value];
            }
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($col . $row, $value);
            ++$col;
        }
        ++$i;
        ++$row;
    }
} catch (PDOException $er) {
    echo 'Error :' . $er->getMessage();
}


$objPHPExcel->getActiveSheet()->setTitle($title);

$objPHPExcel->setActiveSheetIndex(0);

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;