<?php 
require_once '../../vendor/autoload.php';
include "../koneksi.php";

$inputFileName = 'p_list.xlsx';
$phpExcel = PHPExcel_IOFactory::load($inputFileName);

$id = $_GET['id'];
$data = mysqli_query ($koneksi, " select 
                              deliverytbl.Del_ID,
                              deliverytbl.DateKirim,
                              deliverytbl.DetailPO_ID,
                              detail_po.PO_ID,
                              po_tbl.No_PO,
                              po_tbl.CustomerID,
                              customer.CustomerName,
                              detail_po.ProductID,
                              product.ItemDescription,
                              product.SLoc,
                              product.QtyUnit,
                              deliverytbl.DetailEkspedisi_ID,
                              detailekspedisi.EkspedisiID,
                              ekspedisi.Ekspedisi,
                              detailekspedisi.Sopir,
                              detailekspedisi.NoPolisi,
                              detailekspedisi.NoHP,
                              deliverytbl.NoDN,   
                              deliverytbl.QtyKirim    
                      from 
                      deliverytbl
                      INNER JOIN detail_po
                      ON deliverytbl.DetailPO_ID=detail_po.DetailPO_ID
                      INNER JOIN po_tbl
                      ON detail_po.PO_ID=po_tbl.PO_ID
                      INNER JOIN customer
                      ON po_tbl.CustomerID=customer.CustomerID
                      INNER JOIN product
                      ON detail_po.ProductID=product.ProductID
                      INNER JOIN detailekspedisi
                      ON deliverytbl.DetailEkspedisi_ID=detailekspedisi.DetailEkspedisi_ID
                      INNER JOIN ekspedisi
                      ON detailekspedisi.EkspedisiID=ekspedisi.EkspedisiID
                      WHERE Del_ID ='$id'");


$rinc = mysqli_query ($koneksi, " SELECT 
					  stpj.STPJ_ID,
					  plan.PlanID,
					  plan.DatePlan,
					  plan.POOR,
					  product.ProductID,
					  product.SLoc,
					  product.QtyUnit,
					  stpj.Del_ID,
					  stpj.Kode,
					  stpj.NoLot,
					  stpj.Nick,
					  stpj.Qty
			  FROM stpj 
			  INNER JOIN plan
			  ON stpj.PlanID=plan.PlanID
			  INNER JOIN product
			  ON plan.ProductID=product.ProductID
			  where stpj.Del_ID='$id'");

$count = mysqli_num_rows($rinc);


while ($row = mysqli_fetch_assoc($data)){
		$tgl_kirim = date ("d-m-Y", strtotime($row['DateKirim']));
		$dn = $row['NoDN'];
		$customer = $row['CustomerName'];
		$product = $row['ItemDescription'];
		$ekspedisi = $row['Ekspedisi'];
		$nopol = $row['NoPolisi'];
		$sopir = $row['Sopir'];
		$hp = $row['NoHP'];
		$qty_kirim = $row['QtyKirim'];
	}

$phpExcel->getProperties()->setCreator('Muhammad Wildan')
		->setTitle('phpSpreadSheet for ProgWeb Nicktainer')
		->setSubject('Subject Nicktainer')
		->setDescription('make PHPSpreadsheet');

$phpExcel->getActiveSheet()->setTitle('Form Pengecekan');
$phpExcel->setActiveSheetIndex(0)
		->setCellValue('B2', $tgl_kirim)
		->setCellValue('B3', $dn)
		->setCellValue('B4', $customer)
		->setCellValue('B5', $product)
		->setCellValue('E2', $ekspedisi)
		->setCellValue('E3', $nopol)
		->setCellValue('E4', $sopir)
		->setCellValue('E5', $hp);

		$no = array("A7","A8","A9","A10","A11","A12","A13","A14","A15","A16","A17","A18","A19","A20","A21","A22","A23","A24","A25","A26","A27","A28","A29","A30","A31","A32","A33","A34","A35","A36","A37","A38","A39","A40","A41","A42","A43","A44","A45","A46","A47","A48");
		$kode = array("B7","B8","B9","B10","B11","B12","B13","B14","B15","B16","B17","B18","B19","B20","B21","B22","B23","B24","B25","B26","B27","B28","B29","B30","B31","B32","B33","B34","B35","B36","B37","B38","B39","B40","B41","B42","B43","B44","B45","B46","B47","B48");
		$nolot = array("C7","C8","C9","C10","C11","C12","C13","C14","C15","C16","C17","C18","C19","C20","C21","C22","C23","C24","C25","C26","C27","C28","C29","C30","C31","C32","C33","C34","C35","C36","C37","C38","C39","C40","C41","C42","C43","C44","C45","C46","C47","C48");
		$tgl = array("D7","D8","D9","D10","D11","D12","D13","D14","D15","D16","D17","D18","D19","D20","D21","D22","D23","D24","D25","D26","D27","D28","D29","D30","D31","D32","D33","D34","D35","D36","D37","D38","D39","D40","D41","D42","D43","D44","D45","D46","D47","D48");
		$qty = array("E7","E8","E9","E10","E11","E12","E13","E14","E15","E16","E17","E18","E19","E20","E21","E22","E23","E24","E25","E26","E27","E28","E29","E30","E31","E32","E33","E34","E35","E36","E37","E38","E39","E40","E41","E42","E43","E44","E45","E46","E47","E48");
		$nick = array("F7","F8","F9","F10","F11","F12","F13","F14","F15","F16","F17","F18","F19","F20","F21","F22","F23","F24","F25","F26","F27","F28","F29","F30","F31","F32","F33","F34","F35","F36","F37","F38","F39","F40","F41","F42","F43","F44","F45","F46","F47","F48");
		
		
		for ($i=0; $i < $count; $i++) : 
			$row = mysqli_fetch_assoc($rinc);
			$phpExcel->setActiveSheetIndex(0)
					->setCellValue($no[$i], $i+1)
					->setCellValue($kode[$i], $row['POOR'].'-'.$row['Kode'].'-'.substr(preg_replace('/[^A-Za-z0-9]/', '', $row['DatePlan']), 2, 4))
					->setCellValue($nolot[$i], $row['NoLot'])
					->setCellValue($tgl[$i], date ("d-m-Y", strtotime($row['DatePlan'])))
					->setCellValue($qty[$i], $row['QtyUnit']*$row['Qty'])
					->setCellValue($nick[$i], $row['Nick']);

		endfor;

$writer = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel2007');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="PL-'.$tgl_kirim.'-'.$dn.'.xlsx"');
$writer->save('php://output');

 ?>
