invisio v2.0

<?php
session_start();
include('../../../config/main_function.php');
$secure = "cAh3DrJACzw4RbU";
$connection = connectDB($secure);

require('../../../PHPExcel-1.8/Classes/PHPExcel.php');
require('../../../PHPExcel-1.8/Classes/PHPExcel/IOFactory.php');

$objPHPExcel = new PHPExcel();
// กำหนดค่าต่างๆ
$objPHPExcel->getProperties()->setCreator("Company Co., Ltd.");
$objPHPExcel->getProperties()->setLastModifiedBy("Company Co., Ltd.");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX ReportQuery Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX ReportQuery Document");
$objPHPExcel->getProperties()->setDescription("ReportQuery from Company Co., Ltd.");

$sheet = $objPHPExcel->getActiveSheet();
$pageMargins = $sheet->getPageMargins();

// margin is set in inches (0.5cm)
$margin = 0.5 / 2.54;
$pageMargins->setTop($margin);
$pageMargins->setBottom($margin);
$pageMargins->setLeft($margin);
$pageMargins->setRight(0);

$styleHeader = array(
    //'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ffff00')),
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '#000000')
        )
    ),
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => array('rgb' => 'FFFF00'),
    ),
    'font'  => array(
        'bold'  => true,
        'size'  => 10,
        'name'  => 'Arial',
    ),
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    )
);

$styleHeaderLeft = array(
    //'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ffff00')),
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '#000000')
        )
    ),
    'font'  => array(
        'bold'  => true,
        'size'  => 10,
        'name'  => 'Arial',
    ),
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    )
);

$styleHeaderRight = array(
    //'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ffff00')),
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '#000000')
        )
    ),
    'font'  => array(
        'bold'  => true,
        'size'  => 10,
        'name'  => 'Arial',
    ),
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    )
);

$styleHeaderNoBorder = array(
    //'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ffff00')),
    'font'  => array(
        'bold'  => true,
        'size'  => 10,
        'name'  => 'Arial',
    ),
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    )
);

$styleNumber = array(
    //'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ffff00')),
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '#000000')
        )
    ),
    'font'  => array(
        'size'  => 9,
        'name'  => 'Arial',
    ),
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    )
);

$styleText = array(
    //'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ffff00')),
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '#000000')
        )
    ),
    'font'  => array(
        'size'  => 9,
        'name'  => 'Arial',
    ),
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    )
);

$styleTextNoBorder = array(
    //'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ffff00')),

'font'  => array(
        'size'  => 9,
        'name'  => 'Arial',
    ),
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    )
);

$styleText_green = array(
    //'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ffff00')),
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '#000000')
        )
    ),
    'font'  => array(
        'size'  => 9,
        'name'  => 'Arial',
        'color' => array('rgb' => '21BA21'),
    ),
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    )
);

$styleText_red = array(
    //'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ffff00')),
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '#000000')
        )
    ),
    'font'  => array(
        'size'  => 9,
        'name'  => 'Arial',
        'color' => array('rgb' => 'FF0000'),
    ),
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    )
);

$styleText_blue = array(
    //'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ffff00')),
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '#000000')
        )
    ),
    'font'  => array(
        'size'  => 9,
        'name'  => 'Arial',
        'color' => array('rgb' => '0000FF'),
    ),
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    )
);

// 'color' => array('rgb' => '32CD32'),
$styleTextCenter = array(
    //'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ffff00')),
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '#000000')
        )
    ),
    'font'  => array(
        'size'  => 9,
        'name'  => 'Arial',
    ),
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    )
);

$styleTextLeft = array(
    //'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ffff00')),
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '#000000')
        )
    ),
    'font'  => array(
        'size'  => 9,
        'name'  => 'Arial',
    ),
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    )
);

$styleTextRight = array(
    //'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ffff00')),
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '#000000')
        )
    ),
    'font'  => array(
        'size'  => 9,
        'name'  => 'Arial',
    ),
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    )
);

$rowCell = 1;
$columnCharacter = array(
    'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T',
    'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP',
    'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ', 'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK',
    'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ'
);

$columnHeaderName = array(
    "หมายเลขใสมัคร", "ชื่อภาษาไทย","ชื่อภาษาอังกฤษ", "วัน/เดือน/ปี เกิด", "สัญชาติ", "ระดับ", "ประเภท", "หมายเลขบัตร / passport", "วันหมดอายุ", "Visa", "Visa Exp.", "WP", "WP Exp.", "บริษัท", "เบอร์โทร"
);

$num_header = count($columnHeaderName);
$c = 0;
for ($i = 0; $i < $num_header; $i++) {
 $objPHPExcel->getActiveSheet()->setCellValue($columnCharacter[$c] . $rowCell, $columnHeaderName[$c]);
 $objPHPExcel->getActiveSheet()->getColumnDimension($columnCharacter[$c])->setAutoSize(true);
 $objPHPExcel->getActiveSheet()->getStyle($columnCharacter[$c] . $rowCell)->applyFromArray($styleTextCenter);

$c++;
}
//////////// body //////////////////// 
$rowCell = 2;

$sql_system = "SELECT * FROM tbl_system_setting WHERE setting_id = '1';";
$rs_system = mysqli_query($connection, $sql_system);
$row_system = mysqli_fetch_array($rs_system);

$key = $row_system['secure_text'];
$pointer = $row_system['secure_pointer'];

$type = mysqli_real_escape_string($connection, $_POST['type']);
$level = mysqli_real_escape_string($connection, $_POST['level']);
$search = mysqli_real_escape_string($connection, $_POST['search']);
$payment_status = mysqli_real_escape_string($connection, $_POST['payment_status']);
$approve_status = mysqli_real_escape_string($connection, $_POST['approve_status']);

$condition_type = "";
if ($type == 'x') {
    $condition_type .= "AND NOT a.register_type = '0' ";
} else {
    $condition_type .= "AND a.register_type = '$type'";
}

$condition_level = "";
if ($level != 'x') {
    $condition_level .= "AND a.register_level = '$level'";
}

$condition_search = "";
if ($search != "" && $search != "undefined") {
    $condition_search  .= "AND c.citizen_no LIKE '%$search%'";
}

$condition_status = "";
if ($payment_status != 'x') {
    $condition_status  .= "AND g.approve_result = '$payment_status'";
}

$condition_approve = "";
if ($approve_status == 9) {
    $condition_approve  .= "AND a.approve_status IS NULL";
} else if ($approve_status != 'x') {
    $condition_approve  .= "AND a.approve_status = '$approve_status'";
}

$sql_member = "SELECT a.*,
c.*,
g.transfer_date,
g.approve_result,
g.payment_id,
g.slip_image
FROM tbl_member a 
LEFT JOIN tbl_member_detail c ON c.member_id = a.member_id
LEFT JOIN tbl_member_payment g ON g.member_id = a.member_id
WHERE a.register_status = '1' $condition_type $condition_level $condition_search $condition_status $condition_approve
ORDER BY register_no ASC";
$rs_member = mysqli_query($connection, $sql_member) or die($connection->error);

while ($row_member = mysqli_fetch_assoc($rs_member)) {

$member_id_temp = bigsara_encode($row_member['member_id'], $row_member['secure_text'], $row_member['secure_pointer']);
    $member_id = $member_id_temp . "." . $row_member['secure_text'];

$register_type = $row_member['register_type'];

if ($register_type == 1) {
        $register_type = 'นายจ้าง';
    } elseif ($register_type == 2) {
        $register_type = 'บนจ.';
    } elseif ($register_type == 3) {
        $register_type = 'Agency';
    } elseif ($register_type == 4) {
        $register_type = 'แรงงาน';
    }

$register_level = $row_member['register_level'];

if ($register_level == 'g9k1s') {
        $register_level = 'ทั่วไป';
    } elseif ($register_level == 'kki22') {
        $register_level = 'วิสามัญ';
    } elseif ($register_level == 'x9fkq') {
        $register_level = 'สามัญ.';
    }

$citizen_no = $row_member['citizen_no'];
    $work_permit = $row_member['work_permit'];
    $visa_no = $row_member['visa_no'];

$date_now = date('Y-m-d');
 ///// วันที่หมดอายุบัตรประจำตัว
 if (!empty($row_member['card_expire_date'])) {
 $card_exp_date = $row_member['card_expire_date'];
 $cal_card_date = strtotime($card_exp_date) - strtotime($date_now);
 $card_exp_day = floor($cal_card_date / 86400);
 if ($card_exp_day < 0) {
 $card_exp_day = "[ขาด " . substr($card_exp_day, 1) . "วัน]";
 } else {
 $card_exp_day = "[อีก $card_exp_day วัน]";
 }
 } else {
 $card_exp_day = "";
 }

///// วันที่หมดอายุ visa
 if (!empty($row_member['visa_expire_date'])) {
 $visa_exp_date = $row_member['visa_expire_date'];
 $cal_visa_date = strtotime($visa_exp_date) - strtotime($date_now);
 $visa_exp_day = floor($cal_visa_date / 86400);
 if ($visa_exp_day < 0) {
 $visa_exp_day = "[ขาด " . substr($visa_exp_day, 1) . "วัน]";
 } else {
 $visa_exp_day = "[อีก $visa_exp_day วัน]";
 }
 } else {
 $visa_exp_day = "";
 }

///// วันที่หมดดอายุ WP
 if (!empty($row_member['work_expire_date'])) {
 $work_exp_date = $row_member['work_expire_date'];
 $cal_work_date = strtotime($work_exp_date) - strtotime($date_now);
 $work_exp_day = floor($cal_work_date / 86400);
 if ($work_exp_day < 0) {
 $work_exp_day = "[ขาด " . substr($work_exp_day, 1) . "วัน]";
 } else {
 $work_exp_day = "[อีก $work_exp_day วัน]";
 }
 } else {
 $work_exp_day = "";
 }

$temp_title = explode(" ", getTitle_name($row_member['title_name']));
    $title_th = $temp_title[0];
    $title_en = $temp_title[2];

$register_no = "";

if (empty($row_member['member_code'])) {
        $register_no .= $row_member['register_no'];
    } else {
        $register_no .= $row_member['member_code'];
    }

$member_name = ($row_member['member_name'] != "" ? $title_th . " " . $row_member['member_name'] : "");
    $member_name_en = ($row_member['member_name_en'] != "" ? $title_en . " " . $row_member['member_name_en'] : "");
    $birth_date = (!empty($row_member['birth_date']) ? date('d M Y', strtotime($row_member['birth_date'] + 543)) : "-");

$sql_race = "SELECT * FROM tbl_country WHERE ct_code = '{$row_member['race']}'";
    $res_race = mysqli_query($connection, $sql_race);
    $row_race = mysqli_fetch_assoc($res_race);
    $ct_name = $row_race['ct_nameTHA'];

$objPHPExcel->getActiveSheet()->setCellValue("A" . $rowCell, $register_no);
    $objPHPExcel->getActiveSheet()->setCellValue("B" . $rowCell, $member_name);
    $objPHPExcel->getActiveSheet()->setCellValue("C" . $rowCell, $member_name_en);
    $objPHPExcel->getActiveSheet()->setCellValue("D" . $rowCell, $birth_date);
    $objPHPExcel->getActiveSheet()->setCellValue("E" . $rowCell, $ct_name);
    $objPHPExcel->getActiveSheet()->setCellValue("F" . $rowCell, $register_level);
    $objPHPExcel->getActiveSheet()->setCellValue("G" . $rowCell, $register_type);
    $objPHPExcel->getActiveSheet()->setCellValue("H" . $rowCell, $citizen_no);
    $objPHPExcel->getActiveSheet()->setCellValue("I" . $rowCell, date('d M', strtotime($row_member['card_expire_date'])) . ' ' . date('Y', strtotime($row_member['card_expire_date'] + 543)));
    $objPHPExcel->getActiveSheet()->setCellValueExplicit("J" . $rowCell, $visa_no,  PHPExcel_Cell_DataType::TYPE_STRING);
    $objPHPExcel->getActiveSheet()->setCellValue("K" . $rowCell, date('d M', strtotime($row_member['visa_expire_date'])) . ' ' . date('Y', strtotime($row_member['visa_expire_date'] + 543)));
    $objPHPExcel->getActiveSheet()->setCellValueExplicit("L" . $rowCell, $work_permit,  PHPExcel_Cell_DataType::TYPE_STRING);
    $objPHPExcel->getActiveSheet()->setCellValue("M" . $rowCell, date('d M', strtotime($row_member['work_expire_date'])) . ' ' . date('Y', strtotime($row_member['work_expire_date'] + 543)));
    $objPHPExcel->getActiveSheet()->setCellValue("N" . $rowCell, $row_member['company_name']);
    $objPHPExcel->getActiveSheet()->setCellValue("O" . $rowCell, $row_member['company_phone']);

$objPHPExcel->getActiveSheet()->getStyle("A" . $rowCell . ':' . "O" . $rowCell)->applyFromArray($styleTextCenter);

$rowCell++;
}

/*-------------------------------------------------------------------------------------------------------------------------------*/

$objPHPExcel->setActiveSheetIndex(0);
//ตั้งชื่อไฟล์
$file_name = "รายชื่อสมาชิกรอรับรอง รอบวันที่ " . date("d-m-Y", strtotime($row_cer_lot['certification_date']));
//
// Save Excel 2007 file
#echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
ob_end_clean();
// We'll be outputting an excel file
header('Content-type: application/vnd.ms-excel');
// It will be called file.xls
header('Content-Disposition: attachment;filename="' . $file_name . '.xlsx');

$objWriter->save('php://output');

exit();