<?php
 
set_time_limit(0);
header('Content-Type: text/html; charset=utf-8');
/** PHPExcel */
require_once ("../Classes/PHPExcel.php");
include("../connect.php");
include("../function.php");

// สร้าง object ของ Class  PHPExcel  ขึ้นมาใหม่
$objPHPExcel = new PHPExcel();
 
// กำหนดค่าต่างๆ
$objPHPExcel->getProperties()->setCreator("F-PLUS Co., Ltd.");
$objPHPExcel->getProperties()->setLastModifiedBy("F-PLUS Co., Ltd.");
$objPHPExcel->getProperties()->setTitle("REPORT F-PLUS");
$objPHPExcel->getProperties()->setSubject("REPORT F-PLUS");
$objPHPExcel->getProperties()->setDescription("Data from WebServices");
 
$sheet = $objPHPExcel->getActiveSheet();
$pageMargins = $sheet->getPageMargins();
 
// margin is set in inches (0.5cm)
$margin = 0.5 / 2.54;
//Set Margin
$pageMargins->setTop($margin);
$pageMargins->setBottom($margin);
$pageMargins->setLeft($margin);
$pageMargins->setRight(0);
 
//กำหนดความกว้างในแต่ละ Colum
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(50);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(13);
    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(13);
    $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(12);
    $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(10);
//  
 
//สร้าง Style เพื่อกำหนดฟอร์แมตของหัว Colum
$styleHeader = array(
        'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ffff00')),
        'borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),
        'font'  => array(
        'bold'  => true,
        'size'  => 9,
        'name'  => 'Arial'
    ));
 
//กำหนด Style และข้อมูล ให้กับ Row ที่ 1 หรือหัวของ Colum
    $objPHPExcel->setActiveSheetIndex(0)

                ->setCellValue('A1', 'วันที่เอกสาร')
                ->setCellValue('B1', 'รหัสพนักงานขาย')
                ->setCellValue('C1', 'ชื่อพนักงานขาย')
                ->setCellValue('D1', 'คลัง')
                ->setCellValue('E1', 'เลขที่ Invoice')
                ->setCellValue('F1', 'เลขที่ Co')
                ->setCellValue('G1', 'otp')
                ->setCellValue('H1', 'รหัสสินค้า')
                ->setCellValue('I1', 'ชื่อสินค้า')
				->setCellValue('J1','Lot Number')
                ->setCellValue('K1', 'จำนวน(ชิ้น)')
                ->setCellValue('L1', 'ราคา')
				->setCellValue('M1','ราคารวม(ก่อน Vat)')
                ->setCellValue('N1', 'Vat')
                ->setCellValue('O1', 'NET_AMOUNT');
                //$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->applyFromArray($styleHeader);
//
 
//กำหนดแถวเริ่มต้นในการเริ่มใส่ข้อมูล
$rowCell=2;

$Dstart = $_GET["Dstart"];
$Dend = $_GET["Dend"];

$sql = <<<SQL
SELECT oh.UAIVDT,OBSMCD,us.JUTX40,OBWHLO,oh.UAEXIN,oh.UAORTP,
OBORNO,OBITNO,OBITDS,OBBANO,OBORQT as QTY,
OBNEPR as PRICE,
(ol.OBLNA2)*1.07 AS 'BF_VAT',
(ol.OBLNA2)/1.07 as 'VAT',
(ol.OBLNA2) AS 'NETAMOUNT'
FROM MVXJDTA.OOLINE ol
LEFT JOIN MVXJDTA.ODHEAD oh ON ol.OBORNO = oh.UAORNO
LEFT JOIN MVXJDTA.CMNUSR us ON ol.OBSMCD = us.JUUSID 
WHERE oh.UAIVDT BETWEEN '$Dstart' and '$Dend'
AND oh.UAORTP IN ('012','013','014','015','042','043','044','045','922','923','924','925','022','023','024','025','912','913','914','915','942','943','944','945') 
ORDER BY OBORNO
SQL;

$rs=odbc_exec($con,$sql);

while(odbc_fetch_row($rs)){
	$th1 = odbc_result($rs,"UAIVDT");
	$th2 = odbc_result($rs,"OBSMCD");
	$th3 = iconv("TIS-620","UTF-8",odbc_result($rs,"JUTX40"));
    $th4 = odbc_result($rs,"OBWHLO");
	$th5 = odbc_result($rs,"UAEXIN");
	$th6 = odbc_result($rs,"OBORNO");
	$th7 = odbc_result($rs,"UAORTP");
	$th8 = odbc_result($rs,"OBITNO");
	$th9 =  iconv("TIS-620","UTF-8",odbc_result($rs,"OBITDS"));
	$th10 = odbc_result($rs,"OBBANO");
	$th11 = odbc_result($rs,"QTY");
	$th12 = odbc_result($rs,"PRICE");
	$th13 = odbc_result($rs,"BF_VAT");
	$th14 = odbc_result($rs,"VAT");
	$th15 = odbc_result($rs,"NETAMOUNT");

    $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A'.$rowCell, $th1)
                ->setCellValue('B'.$rowCell, $th2)
                ->setCellValue('C'.$rowCell, $th3)
                ->setCellValue('D'.$rowCell, $th4)
                ->setCellValue('E'.$rowCell, $th5)
                ->setCellValue('F'.$rowCell, $th6)
                ->setCellValue('G'.$rowCell, $th7)
                ->setCellValue('H'.$rowCell, $th8)
                ->setCellValue('I'.$rowCell, $th9)
                ->setCellValue('J'.$rowCell, $th10)
                ->setCellValue('K'.$rowCell, $th11)
                ->setCellValue('L'.$rowCell, $th12)        
                ->setCellValue('M'.$rowCell, $th13)
                ->setCellValue('N'.$rowCell, $th14)
                ->setCellValue('O'.$rowCell, $th15);
    $rowCell++;

}

// My File Name
$Fp = Date(dmyhs);
$FileNameXLS = "CNwithLOT".$Fp.".xlsx";
//echo $FileNameXLS;

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('CNwithLOT');
 
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// 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='.$FileNameXLS.'');
$objWriter->save('php://output');
 
exit();
 
?>