<?php
function __negara_kel_produk(&$total_transaksi, &$negara) {
	negara_kel_produk($total_transaksi, $negara);
	/** 
	global $date_start, $date_end, $txt_remark, $txt_pic, $jn_trans, $jn_trans2;
	$to_negara = $product_category = '';
	$sql3 = 
	" SELECT DISTINCT to_negara ". 
	" FROM kontak_dagang_header, kontak_dagang_transaksi ".
	" WHERE header_id = to_kontak_dagang_header ".
	" AND tgl_transaksi >= '".$date_start."' " .
	" AND tgl_transaksi <= '".$date_end."' " . $txt_remark . $txt_pic . $jn_trans .
	" ORDER BY to_negara ";
	//echo "<br>sql3: ".$sql3;
	$ctr = 0;
	if ($hasil3 = mysql_query($sql3)) {
		while ($data3 = mysql_fetch_array ($hasil3)) {
			$ctr++;
			if ($to_negara)	$to_negara .= ',';
			$to_negara .= $data3["to_negara"];
		}
	}
	//echo "<br>to_negara: ".$ctr;
	$sql3 = 
	" SELECT DISTINCT product_category ".
	" FROM kontak_dagang_header, kontak_dagang_transaksi, form_element_value " .
	" WHERE header_id = to_kontak_dagang_header " .
	" AND to_negara = valueID " .
	" AND to_negara in ($to_negara) " .
	" AND tgl_transaksi >= '".$date_start."' " .
	" AND tgl_transaksi <= '".$date_end."'" . $txt_remark . $txt_pic . $jn_trans .
	" ORDER BY product_category ";
	//echo "<br>sql3: ".$sql3;
	if ($hasil3 = mysql_query($sql3)) {
		while ($data3 = mysql_fetch_array ($hasil3)) {
			if ($product_category)	$product_category .= ',';
			$product_category .= $data3["product_category"];
		}
	}
	$sql3 = 
	" SELECT jn_trans, to_negara, valueName, product_category, elementSubDescription, estimasi_kontrak " .
	" FROM kontak_dagang_header, kontak_dagang_transaksi, form_element_sub, form_element_value " .
	" WHERE header_id = to_kontak_dagang_header " .
	" AND valueID = to_negara AND product_category = elementSubID " .
	" AND product_category in ($product_category) " .
	" AND tgl_transaksi >= '".$date_start."' " .
	" AND tgl_transaksi <= '".$date_end."'" . $txt_remark . $txt_pic . $jn_trans .
	" ORDER BY to_negara, product_category ";
	//echo $sql3;
	if ($hasil3 = mysql_query($sql3)) {
		$nama_produk = $nama_negara = ''; $produk = array();
		$total_transaksi = $total_produk = $total_negara = $ctr = 0;
		$totalRecords = mysql_num_rows($hasil3); //echo "<br>totalRecords: ".$totalRecords;		
		while ($data3 = mysql_fetch_array ($hasil3)) {
			
			if ($data3["jn_trans"]!=1220 && $data3["jn_trans"]!=1214 && $data3["jn_trans"]!=1219) {
				//echo "<br>jn_trans: ".$data3["jn_trans"];
				$ctr++;
				$total_transaksi = $total_transaksi + $data3["estimasi_kontrak"];
				if ($nama_negara != $data3["valueName"] || $nama_produk!=$data3["elementSubDescription"] || $ctr==$totalRecords) {
					if($nama_produk) {
						$total_negara = $total_negara + $total_produk;
						$produk[] = array(
							"namaProduk" 	=> $nama_produk,	
							"total_produk" 	=> $total_produk
						);
						$total_produk = 0;
					}
					//$nama_produk = $data3["elementSubDescription"];
				} 
				if ($nama_negara != $data3["valueName"] && $nama_produk != $data3["elementSubDescription"]) {
					if($produk) {
						foreach ($produk as $key => $value) {
							$negara[] = array(
								"nama_negara"  => $nama_negara,
								"total_negara" => $total_negara,
								"nama_produk"  => $value["namaProduk"],
								"total_produk" => $value["total_produk"]
							);
						}
						$produk = array();
						$total_negara = 0;
						$total_produk = 0;	
					}
					$nama_negara = $data3["valueName"];
					$nama_produk = $data3["elementSubDescription"];
				
				} else if ($nama_negara==$data3["valueName"] && $nama_produk!=$data3["elementSubDescription"]) {
					$total_negara = $total_negara + $total_produk;
					$nama_produk  = $data3["elementSubDescription"];
					$total_produk = 0;
					
				} else if ($nama_negara!=$data3["valueName"] && $nama_produk==$data3["elementSubDescription"]) {
					if($produk) {
						foreach ($produk as $key => $value) {
							$negara[] = array(
								"nama_negara"  => $nama_negara,
								"total_negara" => $total_negara,
								"nama_produk"  => $value["namaProduk"],
								"total_produk" => $value["total_produk"]
							);
						}
						$produk = array();
						$total_negara = 0;
						$total_produk = 0;	
					}
					$nama_negara = $data3["valueName"];
				} else {} 
	
				if ($ctr==$totalRecords) {
					$total_produk = $total_produk + $data3["estimasi_kontrak"];
					$total_negara = $total_negara + $total_produk;
					$produk[] = array(
						"namaProduk" 	=> $nama_produk,	
						"total_produk" 	=> $total_produk
					);
					foreach ($produk as $key => $value) {
						$negara[] = array(
							"nama_negara"  => $nama_negara,
							"total_negara" => $total_negara,
							"nama_produk"  => $value["namaProduk"],
							"total_produk" => $value["total_produk"]
						);
					}
					
				} 
				//echo "<br>jn_trans: ".$data3["jn_trans"];
				$total_produk = $total_produk + $data3["estimasi_kontrak"];
			} else {
				//echo "<br>jn_trans: ".$data3["jn_trans"];
			}//else {echo "<br>jn_trans: ".$data3["jn_trans"];}
		}
	}
	//echo $total_transaksi;	
	**/
	return array($negara, $total_transaksi);
}

function __negara_kel_produk_full(&$total_transaksi, &$negara) {
	global $date_start, $date_end, $txt_remark, $txt_pic, $jn_trans;
	$to_negara = $product_category = '';
	negara_kel_produk($total_transaksi, $negara);
}

function __negara_kel_produk_listIT(&$total_transaksi, &$negara, &$row1, &$row2, &$row3, &$jn_trans_name) {
	global $activeSheet, $total_persen, $counter;
	
	$rowx = 0; $styleArray = array();
	__showHeader($rowx, $styleArray, $row1, $row2, $row3, $jn_trans_name);
	
	$rowx++; 
	$activeSheet->setCellValue("A".$rowx, "No"); 
	$activeSheet->setCellValue("B".$rowx, "Negara / Produk");
	$activeSheet->setCellValue("C".$rowx, "Nilai (US $)");
	$activeSheet->setCellValue("D".$rowx, "Persentase (%)");
	$activeSheet->getStyle('A'.$rowx.':D'.$rowx)->applyFromArray($styleArray);  
	$activeSheet->getStyle("A".$rowx)->getAlignment()->setHorizontal('center');
	$activeSheet->getStyle("C".$rowx.":D".$rowx)->getAlignment()->setHorizontal('right');
	if ($negara) {
		foreach ($negara as $key => $row) {
			$s_negara[$key]  	  = $row["nama_negara"];
			$s_total_negara[$key] = $row["total_negara"];
			$s_nama_produk[$key]  = $row["nama_produk"];
			$s_total_produk[$key] = $row["total_produk"];
		}
		if ($total_transaksi > 0)
		array_multisort($s_total_negara,SORT_DESC, $s_total_produk,SORT_DESC,  $negara);
	}
	$rowx++; 
	$activeSheet->  mergeCells("A".$rowx.":B".$rowx);
	$activeSheet->setCellValue("A".$rowx, "Total");
	$activeSheet->setCellValue("C".$rowx, $total_transaksi);
	$activeSheet->setCellValue("D".$rowx, $total_persen);
	$activeSheet->getStyle("C".$rowx)
	  ->getNumberFormat()
	  ->setFormatCode(PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
	$activeSheet->getStyle("D".$rowx)
	  ->getNumberFormat()
	  ->setFormatCode('00.00');
	$activeSheet->getStyle("A".$rowx.":D".$rowx)->getAlignment()->setHorizontal('right');
	$activeSheet->getStyle('A'.$rowx.':D'.$rowx)->applyFromArray($styleArray);  

	$cek = '';
	$counter2 = $total_lainnya = 0;
	foreach ($negara as $key => $value) {
		if ($cek != $value["nama_negara"]) {
			$cek  = $value["nama_negara"];
			$counter++;
			$counter2 = 0;
			//if (isset($total_lainnya) && $total_lainnya > 0) {
			if ($total_lainnya > 0) {	
				$rowx++;
				$activeSheet->setCellValue("B".$rowx, "Lainnya");
				$activeSheet->setCellValue("C".$rowx, $total_lainnya);
				$activeSheet->setCellValue("D".$rowx, "");
				$activeSheet->getStyle("C".$rowx)
				  ->getNumberFormat()
				  ->setFormatCode(PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
				$activeSheet->getStyle("C".$rowx.":D".$rowx)->getAlignment()->setHorizontal('right');
				$activeSheet->getStyle('A'.$rowx.':D'.$rowx)->applyFromArray($styleArray);  
			}
			$total_lainnya = 0;
			$percenPernegara = round(($value["total_negara"]/$total_transaksi)*100, 2);
			
			$rowx++;
			$activeSheet->setCellValue("A".$rowx, $counter);
			$activeSheet->setCellValue("B".$rowx, $value["nama_negara"]);
			$activeSheet->setCellValue("C".$rowx, $value["total_negara"]);
			$activeSheet->setCellValue("D".$rowx, $percenPernegara);
			$activeSheet->getStyle("A".$rowx)->getAlignment()->setHorizontal('center');
			$activeSheet->getStyle("C".$rowx.":C".$rowx)->getAlignment()->setHorizontal('right');
			$activeSheet->getStyle('A'.$rowx.':D'.$rowx)->applyFromArray($styleArray);  
			$activeSheet->getStyle("C".$rowx)
			  ->getNumberFormat()
			  ->setFormatCode(PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
			$activeSheet->getStyle("D".$rowx)
			  ->getNumberFormat()
			  ->setFormatCode('00.00');
			
			$counter2++;
			if ($counter2 <= 5) {
				$rowx++;
				$activeSheet->setCellValue("B$rowx", $value["nama_produk"]);
				$activeSheet->setCellValue("C$rowx", $value["total_produk"]);
				$activeSheet->getStyle("C".$rowx)
				  ->getNumberFormat()
				  ->setFormatCode(PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
				$activeSheet->getStyle("C".$rowx.":D".$rowx)->getAlignment()->setHorizontal('right');
	
			} else {
				$total_lainnya = $total_lainnya + $value["total_produk"];
			}
		  } else {
			$counter2++;
			if ($counter2 <= 5) {
				$rowx++;
				$activeSheet->setCellValue("B".$rowx, $value["nama_produk"]);
				$activeSheet->setCellValue("C".$rowx, $value["total_produk"]);
				$activeSheet->getStyle("C".$rowx)
				  ->getNumberFormat()
				  ->setFormatCode(PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
				$activeSheet->getStyle("C".$rowx.":D".$rowx)->getAlignment()->setHorizontal('right');
				
			  } else {
				$total_lainnya = $total_lainnya + $value["total_produk"];
			}
		}
	}
}

function __negara_kel_produk_full_listIT(&$total_transaksi, &$negara, &$row1, &$row2, &$row3, &$jn_trans_name) {
	global $activeSheet, $total_persen, $counter;
	
	$rowx = 0; $styleArray = array();
	__showHeader($rowx, $styleArray, $row1, $row2, $row3, $jn_trans_name);
	
	$rowx++; 
	$activeSheet->setCellValue("A".$rowx, "No"); 
	$activeSheet->setCellValue("B".$rowx, "Negara / Produk");
	$activeSheet->setCellValue("C".$rowx, "Nilai (US $)");
	$activeSheet->setCellValue("D".$rowx, "Persentase (%)");
	$activeSheet->getStyle('A'.$rowx.':D'.$rowx)->applyFromArray($styleArray);  
	$activeSheet->getStyle("A".$rowx)->getAlignment()->setHorizontal('center');
	$activeSheet->getStyle("C".$rowx.":D".$rowx)->getAlignment()->setHorizontal('right');
	if ($negara) {
		foreach ($negara as $key => $row) {
			$s_negara[$key]  	  = $row["nama_negara"];
			$s_total_negara[$key] = $row["total_negara"];
			$s_nama_produk[$key]  = $row["nama_produk"];
			$s_total_produk[$key] = $row["total_produk"];
		}
		if ($total_transaksi > 0)
		array_multisort($s_total_negara,SORT_DESC, $s_total_produk,SORT_DESC,  $negara);
	}
	$rowx++; 
	$activeSheet->  mergeCells("A".$rowx.":B".$rowx);
	$activeSheet->setCellValue("A".$rowx, "Total");
	$activeSheet->setCellValue("C".$rowx, $total_transaksi);
	$activeSheet->setCellValue("D".$rowx, $total_persen);
	$activeSheet->getStyle("C".$rowx)
	  ->getNumberFormat()
	  ->setFormatCode(PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
	$activeSheet->getStyle("D".$rowx)
	  ->getNumberFormat()
	  ->setFormatCode('00.00');
	$activeSheet->getStyle("A".$rowx.":D".$rowx)->getAlignment()->setHorizontal('right');
	$activeSheet->getStyle('A'.$rowx.':D'.$rowx)->applyFromArray($styleArray);  

	$cek = '';
	$counter2 = 0;
	foreach ($negara as $key => $value) {
		if ($cek != $value["nama_negara"]) {
			$counter++;
			$cek  = $value["nama_negara"];
			$percenPernegara = round(($value["total_negara"]/$total_transaksi)*100, 2);
			
			$rowx++;
			$activeSheet->setCellValue("A".$rowx, $counter);
			$activeSheet->setCellValue("B".$rowx, $value["nama_negara"]);
			$activeSheet->setCellValue("C".$rowx, $value["total_negara"]);
			$activeSheet->setCellValue("D".$rowx, $percenPernegara);
			$activeSheet->getStyle("A".$rowx)->getAlignment()->setHorizontal('center');
			$activeSheet->getStyle("C".$rowx.":C".$rowx)->getAlignment()->setHorizontal('right');
			$activeSheet->getStyle('A'.$rowx.':D'.$rowx)->applyFromArray($styleArray);  
			$activeSheet->getStyle("C".$rowx)
			  ->getNumberFormat()
			  ->setFormatCode(PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
			$activeSheet->getStyle("D".$rowx)
			  ->getNumberFormat()
			  ->setFormatCode('00.00');
		}
		$rowx++;
		$activeSheet->setCellValue("B$rowx", $value["nama_produk"]);
		$activeSheet->setCellValue("C$rowx", $value["total_produk"]);
		$activeSheet->getStyle("C".$rowx)
		  ->getNumberFormat()
		  ->setFormatCode(PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
		$activeSheet->getStyle("C".$rowx.":D".$rowx)->getAlignment()->setHorizontal('right');
	}
}

function negara_kel_produk_full(&$total_transaksi, &$produk) {
	global $date_start, $date_end, $txt_remark, $txt_pic, $jn_trans;
	$to_negara = $product_category = '';
	negara_kel_produk($total_transaksi, $produk);
}

function negara_kel_produk(&$total_transaksi, &$negara) {
	global $date_start, $date_end, $txt_remark, $txt_pic, $jn_trans;
	global $conn;
	/** 
	$sql3 = 
	" SELECT estimasi_kontrak ". 
	" FROM kontak_dagang_header, kontak_dagang_transaksi ".
	" WHERE header_id = to_kontak_dagang_header ".
	" AND tgl_transaksi >= '".$date_start."' " .
	" AND tgl_transaksi <= '".$date_end."' " . $txt_remark . $txt_pic .
	" ORDER BY estimasi_kontrak ";	
	$total_transaksi = 0;
	if ($hasil3 = mysql_query($sql3)) {
		while ($data3 = mysql_fetch_array ($hasil3)) {
			$total_transaksi = $total_transaksi + $data3["estimasi_kontrak"];
		}
	}
	echo "<p>ini Total: ".$total_transaksi."</p>";
	**/
	$total_transaksi = 0;
	$sql3 = 
	" SELECT DISTINCT to_negara ". 
	" FROM kontak_dagang_header, kontak_dagang_transaksi ".
	" WHERE header_id = to_kontak_dagang_header ".
	" AND  jn_trans <> 1214 ".
	" AND  jn_trans <> 1215 ".
	" AND tgl_transaksi >= '".$date_start."' " .
	" AND tgl_transaksi <= '".$date_end."' " . $txt_remark . $txt_pic .
	" ORDER BY to_negara ";
	//echo "<p>".$sql3."</p>";
	if ($hasil3 = mysqli_query($conn, $sql3)) {
		while ($data3 = mysqli_fetch_array ($hasil3)) {
			$sql4 = 
			" SELECT DISTINCT product_category ".
			" FROM kontak_dagang_header, kontak_dagang_transaksi, form_element_value " .
			" WHERE header_id = to_kontak_dagang_header " .
			" AND to_negara = valueID ".
			" AND to_negara = ".$data3["to_negara"].
			" AND  jn_trans <> 1214 ".
			" AND  jn_trans <> 1215 ".		
			" AND tgl_transaksi >= '".$date_start."' " .
			" AND tgl_transaksi <= '".$date_end."'" . $txt_remark . $txt_pic .
			" ORDER BY product_category ";
			//echo $sql4;
			if ($hasil4 = mysqli_query($conn, $sql4)) {
				$total_negara = 0;
				$produk = array();
				$namaNegara = '';
				while ($data4 = mysqli_fetch_array ($hasil4)) {
					$sql5 = 
					" SELECT * FROM". 
					" kontak_dagang_header, kontak_dagang_transaksi, form_element_sub, form_element_value ".
					" WHERE header_id = to_kontak_dagang_header " .
					" AND valueID = to_negara AND product_category = elementSubID ".
					" AND  jn_trans <> 1214 ".
					" AND  jn_trans <> 1215 ".				
					" AND valueID = ".$data3["to_negara"].
					" AND product_category = ".$data4["product_category"].
					" AND tgl_transaksi >= '".$date_start."' " .
					" AND tgl_transaksi <= '".$date_end."'" . $txt_remark . $txt_pic . $jn_trans .
					" ORDER BY product_category ";
					//echo '<p>sql5: '.$sql5.'</p>';
					if ($hasil5 = mysqli_query($conn, $sql5)) {
						$total_produk = 0; $namaProduk = '';
						while ($data5 = mysqli_fetch_array ($hasil5)) {
							$namaNegara   = $data5["valueName"];
							$namaProduk   = $data5["elementSubDescription"];
							$total_produk = $total_produk + $data5["estimasi_kontrak"];
							
						}
						if ($total_produk) {
							$total_negara = $total_negara + $total_produk; 
							$produk[] = array(
								"namaProduk" 	=> $namaProduk,	
								"total_produk" 	=> $total_produk
							);
						}
					}
				}
				if ($total_negara) {
					foreach ($produk as $key => $value) {
						$negara[] = array(
							"nama_negara"  => $namaNegara,
							"total_negara" => $total_negara,
							"nama_produk"  => $value["namaProduk"],
							"total_produk" => $value["total_produk"]
						);
					}
					$total_transaksi = $total_transaksi + $total_negara;
				}
			}
		}
	}
	//echo "Total: ".$total_transaksi;
}
?>