<?php session_start();
	$domain_name = "";
	$uri = $_SERVER["REQUEST_URI"]; 
	$exploded_uri = explode("/", $uri); 
	$domain_name = $exploded_uri[1];
	
	if ($domain_name && $domain_name == "tei") $domain_name .= "/";
	else $domain_name = "";

	$date_start = $_POST["data_start"];
	$date_end = $_POST["data_end"];

	$dataRecord = '';
	$dataRecord2 = '';	
	
	$thisPage = substr($_SERVER["SERVER_NAME"],strrpos($_SERVER["SERVER_NAME"],"/")+0);
	if ($thisPage=="localhost") {
		$thisPage = "http://".$thisPage."/tei/";
		$thisUrl = $_SERVER['DOCUMENT_ROOT'].'tei/'; //"/inetpub/wwwroot/ibex/";
	} else {
		$thisPage = "http://".$thisPage."/".$domain_name;
		$thisUrl = $_SERVER['DOCUMENT_ROOT'].'/'.$domain_name; //"/home/sloki/user/t87309/sites/www/ibex/2017/";
	} 
	
	//echo "<br> thisUrl : ".$thisUrl;
	include_once ($thisUrl . '/koneksi.php');
	include_once ($thisUrl . '/functions/indonesian_date.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';
	//echo "<br>test : ".$thisUrl . '/adm1n/php-excell/Classes/PHPExcel.php';
	require_once $thisUrl . '/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
	));
	
	
	
	if (isset($_POST['remark'])) {
		$remark = " ";
	} else {
		$remark = "AND remark = '0' ";
	}
	

	$sql3 = 
	"select * from kontak_dagang_header, " .
	"kontak_dagang_transaksi, form_element_sub " . 
	"where elementSubID = product_category and " .
	"header_id = to_kontak_dagang_header and ".
	"tgl_transaksi >= '".$date_start."' " .
	$remark." AND tgl_transaksi <= '".$date_end."' order by product_category ";
	echo $sql3;
	
	if ($hasil3 = mysql_query($sql3)) {
		$cek = 0;
		$total_produk = 0;
		$total_transaksi = 0;
		$produk = array();
		while ($data3 = mysql_fetch_array($hasil3)) {
			if ($cek != $data3["product_category"]) {
				if ($cek != 0) {
					$sql4 = 
					"SELECT * FROM kontak_dagang_transaksi, kontak_dagang_header " .
					"WHERE header_id = to_kontak_dagang_header " .
					$remark." AND product_category = '".$cek."' ORDER BY to_negara ";
					if ($hasil4 = mysql_query($sql4)) {
						$cek2 = 0;	
						$total_negara = 0;
						while ($data4 = mysql_fetch_array($hasil4)) {
							if ($cek2 != $data4["to_negara"]) {
								if ($cek2 != 0) {
									FindData($cek2, "form_element_value", $dataTitle, $dataRecord2);		
									$produk[] = array(
										"nama_produk"  => $dataRecord,
										"total_produk" => $total_produk,
										"nama_negara"  => $dataRecord2,
										"total_negara" => $total_negara
									);
									$total_negara = 0;
								}
								$total_negara = $total_negara + $data4["estimasi_kontrak"];
								$cek2 = $data4["to_negara"];
							} else {
								$total_negara = $total_negara + $data4["estimasi_kontrak"];
							}
						}
						FindData($cek2, "form_element_value", $dataTitle, $dataRecord2);		
						$produk[] = array(
							"nama_produk"  => $dataRecord,
							"total_produk" => $total_produk,
							"nama_negara"  => $dataRecord2,
							"total_negara" => $total_negara
						);
						$total_produk = 0;	
					}
				}
				$total_transaksi = $total_transaksi + $data3["estimasi_kontrak"];
				$total_produk = $total_produk + $data3["estimasi_kontrak"];
				$cek = $data3["product_category"];
				$dataRecord = FindDataSingle("form_element_sub", "elementSubID", $cek, "elementSubDescription"); 
			} else {
				$total_transaksi = $total_transaksi + $data3["estimasi_kontrak"];
				$total_produk = $total_produk + $data3["estimasi_kontrak"];
			}
		}
		
		//echo $cek;
		$sql4 = 
		"select * from kontak_dagang_header, kontak_dagang_transaksi " . 
		"WHERE header_id = to_kontak_dagang_header " .
		"and product_category = '".$cek."' " .
		$remark." order by to_negara ";
		if ($hasil4 = mysql_query($sql4)) {
			$cek2 = 0;	
			$total_negara = 0;
			while ($data4 = mysql_fetch_array($hasil4)) {
				if ($cek2 != $data4["to_negara"]) {
					if ($cek2 != 0) {
						FindData($cek2, "form_element_value", $dataTitle, $dataRecord2);		
						$produk[] = array(
							"nama_produk"  => $dataRecord,
							"total_produk" => $total_produk,
							"nama_negara"  => $dataRecord2,
							"total_negara" => $total_negara
						);
						$total_negara = 0;
					}
					$total_negara = $total_negara + $data4["estimasi_kontrak"];
					$cek2 = $data4["to_negara"];
				} else {
					$total_negara = $total_negara + $data4["estimasi_kontrak"];					
				}
			}
		}
		FindData($cek2, "form_element_value", $dataTitle, $dataRecord2);		
		$produk[] = array(
			"nama_produk"  => $dataRecord,
			"total_produk" => $total_produk,
			"nama_negara"  => $dataRecord2,
			"total_negara" => $total_negara
		);

		///11111111111echo $sql4;

		/** 
		
		**/
		//$dataRecord2 = FindDataSingle("form_element_sub", "elementSubID", $cek2, "elementSubDescription"); 
		//$negara[] = array($dataRecord =>$total_negara, $dataRecord2 => $total_produk);  
		//FindData($cek, "form_element_value", $dataTitle, $dataRecord);
		//$negara [$dataRecord] = $total_negara;
		
		/**  
		**/
		foreach ($produk as $key => $row) {

			$s_produk[$key] = $row["nama_produk"];
			$s_total_produk[$key]  = $row["total_produk"];
			$s_nama_negara[$key]  	  = $row["nama_negara"];
			$s_total_negara[$key] = $row["total_negara"];
		}
		
		//arsort($s_total_negara);											
		//var_dump ($s_total_negara);
		//array_multisort($total_negara,SORT_DESC, $nama_negara,SORT_ASC, $total_produk,SORT_DESC, $nama_produk,SORT_ASC, $negara);
		array_multisort($s_total_produk,SORT_DESC, $s_total_negara,SORT_DESC,  $produk);
		var_dump ($produk);
		
		$total_persen = 100;
		$counter = 0;
		//echo "<br>";
		//
	}
	
	
	
			
	$objPHPExcel = new PHPExcel();
	$sheet = $objPHPExcel->getActiveSheet();
	
	$objPHPExcel->getDefaultStyle()->applyFromArray($styleWrapText);

	//$objPHPExcel->setActiveSheetIndex(0)->mergeCells('R1:S1');

	$sheet->getStyle("A")->applyFromArray($styleAlignCenter);
	$sheet->getStyle("C")->applyFromArray($styleAlignCenter);
	$sheet->getStyle("D")->applyFromArray($styleAlignCenter);
	$sheet->getStyle("M")->applyFromArray($styleAlignCenter);
	
	$sheet->getStyle("A1:N1")->applyFromArray($styleAlignMiddle);	

	// Set document properties
	$objPHPExcel->getProperties()->setCreator("Trade Expo Indonesia 2017")
								 ->setLastModifiedBy("Trade Expo Indonesia 2017")
								 ->setTitle("Rekap Transaksi")
								 ->setSubject("Trade Expo Indonesia 2017")
								 ->setDescription("Trade Expo Indonesia 2017")
								 ->setKeywords("Trade Expo Indonesia 2017")
								 ->setCategory("Trade Expo Indonesia 2017");
	
	// Add some data
	$objPHPExcel->setActiveSheetIndex(0)
				->setCellValue('A1', 'No')
				->setCellValue('B1', 'Produk/Negara')
				->setCellValue('C1', 'NIlai (US $)')
				->setCellValue('D1', 'Persentase (%)');
				
				$sheet->getStyle("A1:D1")->applyFromArray($styleBorderOurline);	
				$sheet->getStyle("A1:D1")->applyFromArray($styleBorder);	
				$sheet->getStyle("A1:D1")->applyFromArray($styleBackgroundColor);
				$sheet->getStyle("A1:D1")->applyFromArray($styleFontBold);		
	
	$nomor = 2;
	$objPHPExcel->setActiveSheetIndex(0)
				->setCellValue('A'.$nomor, $nomor-2)
				->setCellValue('B'.$nomor, 'TOTAL')
				->setCellValue('C'.$nomor, $total_transaksi)
				->setCellValue('C'.$nomor, '100');
	$cek = '';
	$counter2 = 0;
	foreach ($produk as $key => $value) {
		$nomor++;
		//$percenPernegara = round(($value["total_produk"]/$total_transaksi)*100, 2);
		$objPHPExcel->setActiveSheetIndex(0)
					->setCellValue('A'.$nomor, $counter)
					->setCellValue('B'.$nomor, $value["nama_produk"])
					->setCellValue('C'.$nomor, $value["total_produk"])
					->setCellValue('C'.$nomor, $percenPernegara);
		$counter2++;
	}
			
	
	
	
	// Rename worksheet
	$objPHPExcel->getActiveSheet()->setTitle('Rekap Kelompok Product & Negara');
	
	
	// Set active sheet index to the first sheet, so Excel opens this as the first sheet
	
	/** 
	$objPHPExcel->setActiveSheetIndex(0);
	
	$fileName = "rekap_by_group_and_counry.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;

	**/
?>