<?php

require_once("conf.php");
require_once("method.php");
date_default_timezone_set('Europe/Istanbul');

$method = $_SERVER["REQUEST_METHOD"];
method($method);

$data = json_decode(file_get_contents('php://input'), true);

if ($method == "GET" and isset($_GET['pdf_id']) and !isset($_GET['excel_id'])) {

	function base64_to_jpeg($base64_string, $output_file)
	{
		// open the output file for writing
		$ifp = fopen('img/' . $output_file, 'wb');

		// split the string on commas
		// $data[ 0 ] == "data:image/png;base64"
		// $data[ 1 ] == <actual base64 string>
		//$data = explode( ',', $base64_string );

		// we could add validation here with ensuring count( $data ) > 1
		//fwrite( $ifp, base64_decode( $data[ 1 ] ) );
		fwrite($ifp, base64_decode($base64_string));
		// clean up the file resource
		fclose($ifp);

		return "img/" . $output_file;
	}

	$query = $db->query("SELECT * FROM report WHERE rpId='" . $_GET['pdf_id'] . "'", PDO::FETCH_ASSOC);
	if ($query->rowCount() > 0) {
		foreach ($query as $q) {

			$rpId = $q["rpId"];
			$rpDate = $q["rpDate"];
			$rpSubject = $q["rpSubject"];
			$rpOrder = $q["rpOrder"];
			$rpCustomerId = $q["rpCustomerId"];
			$rpExporterId = $q["rpExporterId"];
			$rpProductId = $q["rpProductId"];
			$rpQuantity = $q["rpQuantity"];
			$rpSampling = $q["rpSampling"];
			$rpAnalysisNote = $q["rpAnalysisNote"];
			$rpNotes = $q["rpNotes"];
			$rpPersonelId = $q["rpPersonelId"];
			$rpCreatedAt = $q["rpCreatedAt"];
			$rpUpdatedAt = $q["rpUpdatedAt"];
		}

		$sampling_query = $db->prepare("SELECT * FROM samplingsheet WHERE smReportId=:id ORDER BY smCreatedAt ASC");
		$sampling_query->execute(array("id" => $rpId));

		$company_query = $db->prepare("SELECT * FROM company WHERE cyId=:cy_id limit 1");
		$company_query->execute(array("cy_id" => $rpCustomerId));
		$company = $company_query->fetch(PDO::FETCH_ASSOC);

		//echo $company['cyLogo'];

		if ($company['cyLogo'] != "") {
			$cmp_logo = base64_to_jpeg($company['cyLogo'], $company['cyId'] . $company['cyName'] . ".jpeg");
		} else {
			$cmp_logo = "";
		}


		/*$exporter_query=$db->prepare("SELECT * FROM company WHERE cyId=:cy_id limit 1");
		$exporter_query->execute(array("cy_id"=>$rpExporterId));
		$exporter=$exporter_query->fetch(PDO::FETCH_ASSOC);*/
	} else {
		echo "";
	}



	function h_size($url)
	{
		if (pozition($url) == 100) {
			list($width, $height, $type, $attr) = getimagesize($url);
			$aspect_ratio = $height / $width;
			$new_height = 100 * $aspect_ratio;
		} else {
			list($width, $height, $type, $attr) = getimagesize($url);
			$aspect_ratio = $height / $width;
			$new_height = 130 * $aspect_ratio;
		}


		return floor($new_height) + 7;
	}

	function pozition($url)
	{
		list($width, $height, $type, $attr) = getimagesize($url);
		if ($width / $height < 1) {
			return 100;
		} else {
			return 130;
		}
	}


	$size = 55;
	require('fpdf/fpdf.php');


	class PDF extends FPDF
	{
		function Header()
		{
			global $cmp_logo;

			$date = date('d/m/Y');
			$this->Setfont('courier', 'B', 16);
			$this->Image('img/doraintrade_logo.png', 5, 5, 55);
			$this->Cell(190, 10, 'Samplingsheet Doraintrade', 0, 0, 'C');
			//$this->Image('img/doraintrade_logo.png',155,5,55);
			if ($cmp_logo == "") {
			} else {
				$this->Image($cmp_logo, 155, 5, 50);
			}
			//$this->Cell(190,10,$cmp_logo,0,0,'C');

			$this->Ln(30);
		}

		function MainTable($rpSubject, $rpOrder)
		{
			$this->Setfont('courier', 'i', 13);
			$this->Cell(50, 6, 'Subject', 1, 0, 'C');
			$this->Cell(140, 6, $rpSubject, 1, 1, 'C');

			$this->Cell(50, 6, 'Order', 1, 0, 'C');
			$this->Cell(140, 6, $rpOrder, 1, 1, 'C');

			/*$this->Cell(50,6,'Label',1,0,'C');
		$this->Cell(140,6,'asd',1,1,'C');

		$this->Cell(50,60,'Label',1,0,'C');
		$this->MultiCell(140,60,$this->Image('img/doraintrade_logo.png',65,60),1,1);*/
		}

		function TableColumn($title, $img, $size)
		{
			if ($img == "") {
				$this->Cell(50, 6, $title, 1, 0, 'C');
				$this->Cell(140, 6, "", 1, 1, 'C');
			} else {
				$this->Cell(50, h_size($img), $title, 1, 0, 'C');
				//$this->Cell(140,h_size($img),$this->Image("img/doraintrade_logo.png",65,55,100),1,1);
				//$this->Image($img,65,$size,100);
				$this->Cell(140, h_size($img), $this->Image($img, 65, $size, pozition($img)), 1, 1);
			}
		}

		function Footer()
		{
			global $rpDate;
			$this->SetY(-15);
			//$date = date('d/m/Y');
			$this->Setfont('arial', 'i', 12);
			$this->Cell(190, 10, $rpDate, 0, 0, 'R');

			$this->Ln(30);
		}
	}

	$pdf = new PDF();
	$pdf->AddPage('P', 'A4');
	$pdf->MainTable($rpSubject, $rpOrder);

	while ($sampling = $sampling_query->fetch(PDO::FETCH_ASSOC)) {
		if ($sampling['smImg'] == "") {
			$pdf->TableColumn($sampling['smTitle'], "", "");
		} else {
			$pdf->TableColumn($sampling['smTitle'], base64_to_jpeg($sampling['smImg'], $sampling['smTitle'] . ".jpeg"), $size);

			$size = $size + h_size("img/" . $sampling['smTitle'] . ".jpeg");

			if ($size > 180) {
				$size = 43;
			}
		}
		//sleep(1.25);

		//$pdf->Cell(50,6,h_size($sampling['smTitle'].".png"),0,0,"R");
	}

	$pdf->Output("doc/pdf/" . $rpOrder . "-" . $rpSubject . ".pdf", "F");


	echo json_encode("http://localhost/doraintrade/doc/pdf/" . $rpOrder . "-" . $rpSubject . ".pdf");
} else if ($method == "GET" and isset($_GET['excel_id']) and !isset($_GET['pdf_id'])) {

	$query = $db->query("SELECT * FROM report WHERE rpId='" . $_GET['excel_id'] . "'", PDO::FETCH_ASSOC);
	if ($query->rowCount() > 0) {
		foreach ($query as $q) {
			$rpId = $q["rpId"];
			$rpDate = $q["rpDate"];
			$rpSubject = $q["rpSubject"];
			$rpOrder = $q["rpOrder"];
			$rpCustomerId = $q["rpCustomerId"];
			$rpExporterId = $q["rpExporterId"];
			$rpProductId = $q["rpProductId"];
			$rpQuantity = $q["rpQuantity"];
			$rpSampling = $q["rpSampling"];
			$rpAnalysisNote = $q["rpAnalysisNote"];
			$rpNotes = $q["rpNotes"];
			$rpPersonelId = $q["rpPersonelId"];
			$rpCreatedAt = $q["rpCreatedAt"];
			$rpUpdatedAt = $q["rpUpdatedAt"];
		}

		$analysis_query = $db->prepare("SELECT * FROM analysis WHERE asReportId='" . $rpId . "'");
		$analysis_query->execute();

		$customer_query = $db->prepare("SELECT * FROM company WHERE cyId=:cy_id limit 1");
		$customer_query->execute(array("cy_id" => $rpCustomerId));
		$customer = $customer_query->fetch(PDO::FETCH_ASSOC);

		$product_query = $db->prepare("SELECT * FROM product WHERE ptId=:ptId limit 1");
		$product_query->execute(array("ptId" => $rpProductId));
		$product = $product_query->fetch(PDO::FETCH_ASSOC);

		$exporter_query = $db->prepare("SELECT * FROM company WHERE cyId=:cy_id limit 1");
		$exporter_query->execute(array("cy_id" => $rpExporterId));
		$exporter = $exporter_query->fetch(PDO::FETCH_ASSOC);
	} else {
		echo "";
	}

	$counter = 12;
	require_once 'Classes/PHPExcel.php';

	$excel = PHPExcel_IOFactory::createReader('Excel2007');
	$excel = $excel->load('doc/excel/excel.xlsx');
	$excel->getActiveSheet()->setTitle('sayfa1');
	$excel->getActiveSheet()->setCellValue('A1', $customer['cyName'] . "-" . $product['ptName']);
	$excel->getActiveSheet()->setCellValue('F2', $rpDate);

	$excel->getActiveSheet()->setCellValue('A3', 'Date:');
	$excel->getActiveSheet()->setCellValue('B3', $rpDate);
	$excel->getActiveSheet()->setCellValue('A4', 'Order No:');
	$excel->getActiveSheet()->setCellValue('B4', $rpOrder);
	$excel->getActiveSheet()->setCellValue('A5', 'Exporter:');
	$excel->getActiveSheet()->setCellValue('B5', $exporter['cyName']);
	$excel->getActiveSheet()->setCellValue('A6', 'Lot No:');
	$excel->getActiveSheet()->setCellValue('B6', $rpSubject);
	$excel->getActiveSheet()->setCellValue('A7', 'Quantity');
	$excel->getActiveSheet()->setCellValue('B7', $rpQuantity);
	$excel->getActiveSheet()->setCellValue('A8', 'Sampling');
	$excel->getActiveSheet()->setCellValue('B8', $rpSampling);

	$excel->getActiveSheet()->setCellValue('B11', 'Value');
	$excel->getActiveSheet()->setCellValue('C11', 'Actuel 1');
	$excel->getActiveSheet()->setCellValue('D11', 'Actuel 2');
	$excel->getActiveSheet()->setCellValue('E11', 'Average');

	while ($analysis = $analysis_query->fetch(PDO::FETCH_ASSOC)) {
		$excel->getActiveSheet()->setCellValue('A' . $counter, $analysis['asName'])->getStyle('A' . $counter)->applyFromArray(
			array('borders' => array(
				'allborders'    => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
			))
		);

		$excel->getActiveSheet()->setCellValue('B' . $counter, $analysis['asValue'])->getStyle('B' . $counter)->applyFromArray(
			array('borders' => array(
				'allborders'    => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
			))
		);
		$excel->getActiveSheet()->setCellValue('C' . $counter, $analysis['asActuel1'])->getStyle('C' . $counter)->applyFromArray(
			array('borders' => array(
				'allborders'    => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
			))
		);
		$excel->getActiveSheet()->setCellValue('D' . $counter, $analysis['asActuel2'])->getStyle('D' . $counter)->applyFromArray(
			array('borders' => array(
				'allborders'    => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
			))
		);
		$excel->getActiveSheet()->setCellValue('E' . $counter, $analysis['asAverage'])->getStyle('E' . $counter)->applyFromArray(
			array('borders' => array(
				'allborders'    => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
			))
		);
		$counter = $counter + 1;
	}


	$counter = $counter + 2;
	$excel->getActiveSheet()->setCellValue('A' . $counter, "NOTES: " . $rpAnalysisNote)->getStyle('A' . $counter)->applyFromArray(
		array('borders' => array(
			'allborders'    => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
		))
	);


	$save = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
	$save->save('doc/excel/' . $rpOrder . '-' . $rpSubject . '.xlsx');

	error_reporting(0);
	echo json_encode("http://localhost/doraintrade/doc/excel/" . $rpOrder . "-" . $rpSubject . ".xlsx");
	exit;
} else if ($method == "GET" and isset($_GET['excel_id_list'])) {

	require_once 'Classes/PHPExcel.php';

	$excel = PHPExcel_IOFactory::createReader('Excel2007');
	$excel = $excel->load('doc/excel-list/excel.xlsx');
	$excel->getActiveSheet()->setTitle('sayfa1');
	/*$excel->getActiveSheet()->setCellValue('A1', "Date");
	$excel->getActiveSheet()->setCellValue('B1', "Exporter");
	$excel->getActiveSheet()->setCellValue('C1', "Customer");
	$excel->getActiveSheet()->setCellValue('D1', "Order No");
	$excel->getActiveSheet()->setCellValue('E1', "Lot No");
	$excel->getActiveSheet()->setCellValue('F1', "Product");
	$excel->getActiveSheet()->setCellValue('G1', "Personel");*/

	$excel_id_list = json_decode($_GET['excel_id_list']);
	//echo "ok" . $excel_id_list;
	$id_counter = 2;
	foreach ($excel_id_list as $id) {
		//echo "id".$id."\n";

		$query = $db->query("SELECT * FROM report WHERE rpId='" . $id . "'", PDO::FETCH_ASSOC);
		if ($query->rowCount() > 0) {
			foreach ($query as $q) {
				$rpId = $q["rpId"];
				$rpDate = $q["rpDate"];
				$rpSubject = $q["rpSubject"];
				$rpOrder = $q["rpOrder"];
				$rpCustomerId = $q["rpCustomerId"];
				$rpExporterId = $q["rpExporterId"];
				$rpProductId = $q["rpProductId"];
				$rpQuantity = $q["rpQuantity"];
				$rpSampling = $q["rpSampling"];
				$rpAnalysisNote = $q["rpAnalysisNote"];
				$rpNotes = $q["rpNotes"];
				$rpPersonelId = $q["rpPersonelId"];
				$rpCreatedAt = $q["rpCreatedAt"];
				$rpUpdatedAt = $q["rpUpdatedAt"];
			}

			$personel_query = $db->prepare("SELECT * FROM personel WHERE prlId=:id limit 1");
			$personel_query->execute(array("id" => $rpPersonelId));
			$personel = $personel_query->fetch(PDO::FETCH_ASSOC);

			$customer_query = $db->prepare("SELECT * FROM company WHERE cyId=:cy_id and cyCompanyType=:cType limit 1");
			$customer_query->execute(array("cy_id" => $rpCustomerId, "cType" => "customer"));
			$customer = $customer_query->fetch(PDO::FETCH_ASSOC);

			$product_query = $db->prepare("SELECT * FROM product WHERE ptId=:ptId limit 1");
			$product_query->execute(array("ptId" => $rpProductId));
			$product = $product_query->fetch(PDO::FETCH_ASSOC);

			$exporter_query = $db->prepare("SELECT * FROM company WHERE cyId=:cy_id and cyCompanyType=:cType limit 1");
			$exporter_query->execute(array("cy_id" => $rpExporterId, "cType" => "exporter"));
			$exporter = $exporter_query->fetch(PDO::FETCH_ASSOC);
		} else {
			echo "";
		}
		$excel->getActiveSheet()->setCellValue('A' . $id_counter, ($id_counter-1));
		$excel->getActiveSheet()->setCellValue('B' . $id_counter, $rpDate);
		$excel->getActiveSheet()->setCellValue('C' . $id_counter, $exporter['cyName']);
		$excel->getActiveSheet()->setCellValue('D' . $id_counter, $customer['cyName']);
		$excel->getActiveSheet()->setCellValue('E' . $id_counter, $rpOrder);
		$excel->getActiveSheet()->setCellValue('F' . $id_counter, $rpSubject);
		$excel->getActiveSheet()->setCellValue('G' . $id_counter, $product['ptName']);
		$excel->getActiveSheet()->setCellValue('H' . $id_counter, $personel['prlName'] . " " . $personel['prlLastName']);

		$id_counter++;
	}
	//$excel->getActiveSheet()->setCellValue('H1',"Counter: ".($id_counter-2));
	$save = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
	$save->save('doc/excel-list/'.$rpId.'-excel.xlsx');
	error_reporting(0);
	echo json_encode("http://localhost/doraintrade/doc/excel-list/".$rpId."-excel.xlsx");
	exit;

	
}
