<?php
/**
 * PHPExcel
 *
 * Copyright (C) 2006 - 2013 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version    1.7.9, 2013-06-02
 */

/** Error reporting */
//require_once('libconfig.php');
//require_once('libhtml.php');
//require_once('libmsginfo.php');
//require_once('libdefine.php');
//require_once('libdbconn.php');

$keyword=$_REQUEST['keyword'];
$cl=new MY_HTML();
//if($idfakultas){$nmfakultas=$cl->fn_get_fieldvalue("namafakultas","select namafakultas from ".DB_UNDIAN.".tbl_fakultas where id='$idfakultas'");}


error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser');

/** Include PHPExcel */

require_once 'Classes/PHPExcel.php';
if(preg_match("/^windows/i",strtolower(php_uname()))){PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);}

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// My data
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1','TELKOMCEL 4GLTE')
            ->setCellValue('A2','Registrant List')
            ->setCellValue('A3','Reported By : '.$_SESSION['nik']);

//  set format cell
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);


// Print Header
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A6','No')
            ->setCellValue('B6','Site ID')
            ->setCellValue('C6','Site Name')
            ->setCellValue('D6','Site Type')
            ->setCellValue('E6','Band')
            ->setCellValue('F6','Lng')
            ->setCellValue('G6','Lat')
            ->setCellValue('H6','2G Frekuensi')
            ->setCellValue('I6','3G Frekuensi')
            ->setCellValue('J6','4G Frekuensi')
            ->setCellValue('K6','Position')
            ->setCellValue('L6','District')
            ->setCellValue('M6','Subdistrict')
            ->setCellValue('N6','Suco')
            ->setCellValue('O6','Time On')
            ->setCellValue('P6','Cluster Area')
            ->setCellValue('Q6','Tower Height')
            ->setCellValue('R6','Owner')
            ;

// === Merge Cells
//$objPHPExcel->getActiveSheet()->mergeCells('G5:H5');
//$objPHPExcel->getActiveSheet()->mergeCells('I5:J5');

$objPHPExcel->getActiveSheet()->getStyle('A6:R6')->getFont()->getColor()->setRGB('ffffff');
// Worksheet Name
$objPHPExcel->getActiveSheet()->setTitle('SITEINFO_'.date("Ym"));

// === Set Background Cell
$objPHPExcel->cellColor("A6:R6","938e8e");

// === set Column Width A
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(6);
// === Set auto COlumn width
foreach(range('B','R') as $columnID)
{
    $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}

$qry="select id,siteid,sitename,idsitetypes,sitetype,band,lng,lat,2G,3G,4G,position,district,subdistrict,suco,onair,clusterarea,towerheight,owner
      from ".DB_APORTIL.".tbl_nits_siteslacci
      where (siteid like '%$keyword%' or sitename like '%$keyword%' or band like '%$keyword%' or sitetype like '%$keyword%') 
            and isdeleted=0
      order by id asc";
      
$rsrpt=$cl->selectquery($qry);
if($rsrpt)
{
    $i=7;$j=1;
    while($rw=$cl->data_row_array($rsrpt))
    {
        $timeon="";
        if($rw["onair"]){$timeon=date("Y-m-d",strtotime($row["onair"]));}

        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$i,$j)
                                            ->setCellValue('B'.$i,$rw["siteid"])
                                            ->setCellValue('C'.$i,$rw["sitename"])
                                            ->setCellValue('D'.$i,$rw["sitetype"])
                                            ->setCellValue('E'.$i,$rw["band"])
                                            ->setCellValue('F'.$i,$rw["lng"])
                                            ->setCellValue('G'.$i,$rw["lat"])
                                            ->setCellValue('H'.$i,$rw["2G"])
                                            ->setCellValue('I'.$i,$rw["3G"])
                                            ->setCellValue('J'.$i,$rw["4G"])
                                            ->setCellValue('K'.$i,$rw["position"])
                                            ->setCellValue('L'.$i,$rw["district"])
                                            ->setCellValue('M'.$i,$rw["subdistrict"])
                                            ->setCellValue('N'.$i,$rw["suco"])
                                            ->setCellValue('O'.$i,$timeon)
                                            ->setCellValue('P'.$i,$rw["clusterarea"])
                                            ->setCellValue('Q'.$i,$rw["towerheight"])
                                            ->setCellValue('R'.$i,$rw["owner"])
                                            ;
        $i+=1;
    }
}
$cl->clearrecordset($rsrpt);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="SITEINFO.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
?>