<?php
function __kel_produk_negara_full_listIT(&$total_transaksi, &$produk, &$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, "Produk / Negara");
	$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 ($produk) {
		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"];
		}
		if ($total_transaksi > 0)
		array_multisort($s_total_produk,SORT_DESC, $s_total_negara,SORT_DESC,  $produk);
	}
	$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 ($produk as $key => $value) {
		if ($cek != $value["nama_produk"]) {
			$cek = $value["nama_produk"];
			$percenPernegara = round(($value["total_produk"]/$total_transaksi)*100, 2);
			$counter++;
			$rowx++;
			$activeSheet->setCellValue("A".$rowx, $counter);
			$activeSheet->setCellValue("B".$rowx, $value["nama_produk"]);
			$activeSheet->setCellValue("C".$rowx, $value["total_produk"]);
			$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_negara"]);
		$activeSheet->setCellValue("C$rowx", $value["total_negara"]);
		$activeSheet->getStyle("C".$rowx)
		  ->getNumberFormat()
		  ->setFormatCode(PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
		$activeSheet->getStyle("C".$rowx.":D".$rowx)->getAlignment()->setHorizontal('right');
	}
}

function __kel_produk_negara_listIT(&$total_transaksi, &$produk, &$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, "Produk / Negara");
	$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 ($produk) {
		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"];
		}
		if ($total_transaksi > 0)
		array_multisort($s_total_produk,SORT_DESC, $s_total_negara,SORT_DESC,  $produk);
	}
	$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 ($produk as $key => $value) {
		if ($cek != $value["nama_produk"]) {
			$cek  = $value["nama_produk"];
			$counter++;
			$counter2 = 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_produk"]/$total_transaksi)*100, 2);
			
			$rowx++;
			$activeSheet->setCellValue("A".$rowx, $counter);
			$activeSheet->setCellValue("B".$rowx, $value["nama_produk"]);
			$activeSheet->setCellValue("C".$rowx, $value["total_produk"]);
			$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_negara"]);
				$activeSheet->setCellValue("C$rowx", $value["total_negara"]);
				$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_negara"];
			}
		  } else {
			$counter2++;
			if ($counter2 <= 5) {
				$rowx++;
				$activeSheet->setCellValue("B".$rowx, $value["nama_negara"]);
				$activeSheet->setCellValue("C".$rowx, $value["total_negara"]);
				$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_negara"];
			}
		}
	}
}

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

function __kel_produk_negara(&$total_transaksi, &$produk) {
	global $date_start, $date_end, $txt_remark, $txt_pic, $jn_trans;
	$to_negara = $product_category = '';
	kel_produk_negara($total_transaksi, $produk);
	/** 
	$sql3 = 
	" SELECT DISTINCT product_category ". 
	" 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 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"];
		}
	}
	//echo "".$product_category;
	$sql3 = 
	" SELECT DISTINCT to_negara ".
	" FROM kontak_dagang_header, kontak_dagang_transaksi, form_element_sub ".
	" WHERE header_id = to_kontak_dagang_header " .
	" 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 ";
	if ($hasil3 = mysql_query($sql3)) {
		while ($data3 = mysql_fetch_array ($hasil3)) {
			if ($to_negara)	$to_negara .= ',';
			$to_negara .= $data3["to_negara"];
		}
	}
	$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 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, to_negara ";
	//echo $sql3;
	if ($hasil3 = mysql_query($sql3)) {
		$nama_produk = $nama_negara = ''; $negara = 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) {
				$ctr++;
				$total_transaksi = $total_transaksi + $data3["estimasi_kontrak"];
				if ($nama_negara != $data3["valueName"] || $nama_produk!=$data3["elementSubDescription"] || $ctr==$totalRecords) {
					if($nama_negara) {
						$total_produk = $total_produk + $total_negara;
						$negara[] = array(
							"nama_negara" 	=> $nama_negara,	
							"total_negara" 	=> $total_negara
						);
						$total_negara = 0;
					}
					//$nama_produk = $data3["elementSubDescription"];
				} 
				if ($nama_negara != $data3["valueName"] && $nama_produk != $data3["elementSubDescription"]) {
					if($negara) {
						foreach ($negara as $key => $value) {
							$produk[] = array(
								"nama_produk"  => $nama_produk,
								"total_produk" => $total_produk,
								"nama_negara"  => $value["nama_negara"],
								"total_negara" => $value["total_negara"]
							);
						}
						$negara = array();
						$total_produk = 0;	
						$total_negara = 0;
					}
					$nama_negara = $data3["valueName"];
					$nama_produk = $data3["elementSubDescription"];
				
				} else if ($nama_negara!=$data3["valueName"] && $nama_produk==$data3["elementSubDescription"]) {
					$total_produk = $total_produk + $total_negara;
					$nama_negara  = $data3["valueName"];
					$total_negara = 0;
	
				} else if ($nama_negara==$data3["valueName"] && $nama_produk!=$data3["elementSubDescription"]) {
					if($negara) {
						foreach ($negara as $key => $value) {
							$produk[] = array(
								"nama_produk"  => $nama_produk,
								"total_produk" => $total_produk,
								"nama_negara"  => $value["nama_negara"],
								"total_negara" => $value["total_negara"]
							);
						}
						$negara = array();
						$total_negara = 0;
						$total_produk = 0;	
					}
					$nama_produk = $data3["elementSubDescription"];
				} else {} 
				if ($ctr==$totalRecords) {
					$total_negara = $total_negara + $data3["estimasi_kontrak"];
					$total_produk = $total_produk + $total_negara;
					$negara[] = array(
						"nama_negara" 	=> $nama_negara,	
						"total_negara" 	=> $total_negara
					);
					foreach ($negara as $key => $value) {
						$produk[] = array(
							"nama_produk"  => $nama_produk,
							"total_produk" => $total_produk,
							"nama_negara"  => $value["nama_negara"],
							"total_negara" => $value["total_negara"]
						);
					}
				} 
				$total_negara = $total_negara + $data3["estimasi_kontrak"];
			}
		}
	}
	**/
	return array($produk, $total_transaksi);
}

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

					$total_transaksi = $total_transaksi + $total_produk;
				}
			}
		}
	}
}
?>