<?php session_start();
	$includePage = dirname(__FILE__);
	//echo "Ori : ".$includePage;
	$arr = explode("\\",$includePage);
	$includePage = '';
	foreach ($arr as $value) {
		if ($value != "adm1n" && $value != "pages" && $value != "laporan-transaksi") {
			if ($includePage)
				$includePage .= '\\';
			$includePage .= $value;
		}
	}
	//echo "<br> Trim : ".$includePage;
	include_once ($includePage . '/koneksi.php');

	function FindData($tabelID, $tabelName, &$dataTitle, &$dataRecord) {
		$sql7 = "SELECT * FROM $tabelName, form_element_sub WHERE elementSubID = elementSubParentID and valueID = '$tabelID'";
		//echo $sql7;
		if ($hasil7 = mysql_query($sql7)) {
			while ($data7 = mysql_fetch_array($hasil7)) {
				$dataTitle = $data7["elementSubDescription"]; 
				$dataRecord = $data7["valueName"]; 
				return $dataRecord;
			}
		}
	}

	function FindDataList($tabelName1, $tabelName2, $tabelField1, $tabelField2, $searchID, $Key, $dataRecord='') {
		$sql7 = "SELECT valueName FROM $tabelName1, $tabelName2 WHERE $tabelField1=$tabelField2 AND $searchID = '$Key'";
		//echo $sql7;
		if ($hasil7 = mysql_query($sql7)) {
			while ($data7 = mysql_fetch_array($hasil7)) {
				if ($dataRecord) {
					$dataRecord .= ', ';
				}
				$dataRecord .= $data7["valueName"]; 
			}
		}
		return $dataRecord;
	}
	
	function FindDataListWithHeader($Key, $dataRecord='') {
		$sql7 = "SELECT elementSubDescription, valueName FROM buyer_register_product, 
				 form_element_sub, form_element_value WHERE elementSubParentID = elementSubID 
				 AND link_to_buyer = '$Key' AND link_to_product_value = valueID";
		//echo $sql7;
		$elementSubDescription = '';
		if ($hasil7 = mysql_query($sql7)) {
			while ($data7 = mysql_fetch_array($hasil7)) {
				if ($elementSubDescription != $data7["elementSubDescription"]) {
					if ($dataRecord) {
						$dataRecord .= '), ';
					}
					$dataRecord .= $data7["elementSubDescription"].'('; 
					$elementSubDescription = $data7["elementSubDescription"];
				} else {
					if ($dataRecord) {
						$dataRecord .= ', ';
					}
				}
				$dataRecord .= $data7["valueName"]; 
			}
		}
		$dataRecord .= ')';
		return $dataRecord;
	}
		
	function FindDataSingle($tabelName, $tabelField, $key, $dataRecord) {
		$sql7 = "SELECT * FROM $tabelName WHERE $tabelField = '$key' ";
		//echo $sql7;
		if ($hasil7 = mysql_query($sql7)) {
			while ($data7 = mysql_fetch_array($hasil7)) {
				${$dataRecord} = $data7[$dataRecord]; 
				return ${$dataRecord};
			}
		}
	}
	
	/**
	 * PHPExcel
	 *
	 * Copyright (C) 2006 - 2014 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 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
	 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
	 * @version    ##VERSION##, ##DATE##
	 */
	
	/** Error reporting */
	error_reporting(E_ALL);
	ini_set('display_errors', TRUE);
	ini_set('display_startup_errors', TRUE);
	date_default_timezone_set('Asia/Jakarta');
	
	if (PHP_SAPI == 'cli')
		die('This page should only be run from a Web Browser');
	
	/** Include PHPExcel */
	//require_once dirname(__FILE__) . '/Classes/PHPExcel.php';
	require_once $includePage . '/adm1n/php-excell/Classes/PHPExcel.php';
	
	// Create new PHPExcel object
	$styleAlignCenter = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )
    );
	
 	$styleAlignMiddle = array(
        'alignment' => array(
			'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
        )
    );

	$styleBorder = array(
		'borders' => array(
			'allborders' => array(
				'style' => PHPExcel_Style_Border::BORDER_THIN
			)
		)
  	);
		
	$styleBorderOurline = array(
		'borders' => array(
			'outline' => array(
			  	'style' => PHPExcel_Style_Border::BORDER_THIN
			)
		)
	);		

	$styleBackgroundColor = array(
        'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            'color' => array('rgb' => 'EEEEEE')
        )
	);		
	
	$styleFontBold = array(
    	'font'  => array(
        	'bold'  => true
    ));	
		
	$styleWrapText = array(
		'alignment'	 => array(
			'wrap' => true
	));
	
			
	$objPHPExcel = new PHPExcel();
	$sheet = $objPHPExcel->getActiveSheet();
	
	$objPHPExcel->getDefaultStyle()->applyFromArray($styleWrapText);

	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:A2');
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('B1:B2');
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('C1:C2');
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('D1:D2');
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('E1:E2');
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('F1:F2');
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('G1:G2');
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('H1:H2');
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('I1:I2');
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('J1:J2');
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('K1:K2');
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('L1:L2');
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('M1:M2');
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('N1:N2');
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('O1:Q1');
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('R1:S1');

	$sheet->getStyle("A:C")->applyFromArray($styleAlignCenter);
	$sheet->getStyle("K")->applyFromArray($styleAlignCenter);
	$sheet->getStyle("N")->applyFromArray($styleAlignCenter);
	
	$sheet->getStyle("O1:Q1")->applyFromArray($styleAlignCenter);
	$sheet->getStyle("R1:S1")->applyFromArray($styleAlignCenter);

	$sheet->getStyle("A1:N2")->applyFromArray($styleAlignMiddle);	

	// Set document properties
	$objPHPExcel->getProperties()->setCreator("Trade Expo Indonesia")
								 ->setLastModifiedBy("Trade Expo Indonesia")
								 ->setTitle("Trade Expo Indonesia 2017")
								 ->setSubject("Trade Expo Indonesia 2017")
								 ->setDescription("Trade Expo Indonesia 2017.")
								 ->setKeywords("Trade Expo Indonesia 2017")
								 ->setCategory("Trade Expo Indonesia");
	
	// Add some data
	$objPHPExcel->setActiveSheetIndex(0)
				->setCellValue('A1', 'No')
				->setCellValue('B1', 'Date Register')
				->setCellValue('C1', 'Buyer ID')
				->setCellValue('D1', 'Full Name')
				->setCellValue('E1', 'Gender')
				->setCellValue('F1', 'Country')
				->setCellValue('G1', 'City / Province')
				->setCellValue('H1', 'Company Name')
				->setCellValue('I1', 'Company Phone')
				->setCellValue('J1', 'Company Address')
				->setCellValue('K1', 'Zip Code')
				->setCellValue('L1', 'Mobile Number')
				->setCellValue('M1', 'Email Address')
				->setCellValue('N1', 'Attend TEI Last Year')
				->setCellValue('O1', 'Info About TEI 2017')
				->setCellValue('O2', 'Know TEI From')
				->setCellValue('P2', 'ITPC/ATDAG')
				->setCellValue('Q2', 'Others')
				->setCellValue('R1', 'Products')
				->setCellValue('R2', 'Interested Products')
				->setCellValue('S2', 'Others');
				
				$sheet->getStyle("A1:S2")->applyFromArray($styleBorderOurline);	
				$sheet->getStyle("A1:S2")->applyFromArray($styleBorder);	
				$sheet->getStyle("A1:S2")->applyFromArray($styleBackgroundColor);
				$sheet->getStyle("A1:S2")->applyFromArray($styleFontBold);		
	
	$sql  = "select * from buyer_register order by date_register";
	$nomor = 3;
	if ($hasil = mysql_query($sql)) {
		while ($data = mysql_fetch_array($hasil)) {
			$objPHPExcel->setActiveSheetIndex(0)
						->setCellValue('A'.$nomor, $nomor-2)
						->setCellValue('B'.$nomor, date("F d, Y",strtotime($data["date_register"])))
						->setCellValue('C'.$nomor, $data["buyerRegID"])
						->setCellValue('D'.$nomor, $data["fullName"])
						->setCellValue('E'.$nomor, FindData($data["gender"], "form_element_value", $dataTitle, $dataRecord))
						->setCellValue('F'.$nomor, FindData($data["country"], "form_element_value", $dataTitle, $dataRecord))
						->setCellValue('G'.$nomor, FindData($data["kota_propinsi"], "form_element_value", $dataTitle, $dataRecord).' '.$data["kota_propinsi_lain"])
						->setCellValue('H'.$nomor, $data["company_name"])
						->setCellValue('I'.$nomor, $data["telpon"])
						->setCellValue('J'.$nomor, $data["alamat_perusahaan"])
						->setCellValue('K'.$nomor, $data["kdpos"])
						->setCellValue('L'.$nomor, $data["hp"])
						->setCellValue('M'.$nomor, $data["email"])
						->setCellValue('N'.$nomor, FindData($data["attend_last_year"], "form_element_value", $dataTitle, $dataRecord))
						->setCellValue('O'.$nomor, FindDataList("buyer_register_knowfrom", "form_element_value", "link_to_table_value", "valueID", "link_to_buyer", $data["buyerID"], ""))
						->setCellValue('P'.$nomor, FindData($data["know_from_office_rep"], "form_element_value", $dataTitle, $dataRecord))
						->setCellValue('Q'.$nomor, FindDataSingle("buyer_register_knowfrom_lain", "link_to_buyer", $data["buyerID"], "knowfromlainDesc"))
						->setCellValue('R'.$nomor, FindDataListWithHeader($data["buyerID"], ''))
						->setCellValue('S'.$nomor, FindDataSingle("buyer_register_product_lain", "link_to_buyer", $data["buyerID"], "byProdLainName"));
						$sheet->getStyle("A".$nomor.":"."S".$nomor)->applyFromArray($styleBorderOurline);	
						$sheet->getStyle("A".$nomor.":"."S".$nomor)->applyFromArray($styleBorder);	
			$nomor++;
		}
	}
	
	// Rename worksheet
	$objPHPExcel->getActiveSheet()->setTitle('Buyer Detailed Report');
	
	
	// Set active sheet index to the first sheet, so Excel opens this as the first sheet
	$objPHPExcel->setActiveSheetIndex(0);
	
	$fileName = "buyers-details.xlsx";
	// Redirect output to a client’s web browser (Excel2007)
	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header('Content-Disposition: attachment;filename="'.$fileName.'"');
	header('Cache-Control: max-age=0');
	// If you're serving to IE 9, then the following may be needed
	header('Cache-Control: max-age=1');
	
	// If you're serving to IE over SSL, then the following may be needed
	header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
	header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
	header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
	header ('Pragma: public'); // HTTP/1.0
	
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter->save('php://output');
	exit;
?>