<?php
require '../vendor/autoload.php';
require_once('../Db/dbconfig.php');

$dptId=$_GET['dptId'];
$frmdtl=$_GET['frmdt'];
$todtl=$_GET['todt'];
$itemcat=$_GET['itemcat'];

$frmdt = new DateTime($frmdtl, new DateTimeZone('Asia/Kolkata'));
$frmdt = $frmdt->format('Y-m-d 00:00:00');
$frmdt = new DateTime($frmdt, new DateTimeZone('Asia/Kolkata'));


$frmdt->setTimezone(new DateTimeZone('MST'));
$frmdt = $frmdt->format('Y-m-d H:i:s');

$todt = new DateTime($todtl, new DateTimeZone('Asia/Kolkata'));
$todt = $todt->format('Y-m-d 23:59:59');
$todt = new DateTime($todt, new DateTimeZone('Asia/Kolkata'));

$todt->setTimezone(new DateTimeZone('MST'));
$todt = $todt->format('Y-m-d H:i:s');

$mpdf = new \Mpdf\Mpdf();

$table = '<style>table { font-family: arial, sans-serif; border-collapse: collapse; width: 100%;}td, th { border: 1px solid #dddddd; text-align: left; padding: 8px;}</style>';

if($dptId == '0'){

	$sqlCat = "select * from product p where id =".$itemcat;
	$resultCat = $con->query($sqlCat);
	if ($resultCat->num_rows > 0) 
	{
	    // output data of each row
		while($rowCat = $resultCat->fetch_assoc()) 
		{
			$table .="<h4>".$rowCat['item'].":- ".$frmdt." To ".$todt."</h4>";
		}
	}

	$sql = "select ad.productItem_id ,ad.id,DATE(ad.created_at) as senddttime,p2.description,d.depart,ad.send_to_quantity ,ad.send_to_qtny_type,qt.q_name 
		from admin_dpts ad,productitem p2,product p4,department d,quantity_type qt where p2.item = p4.id and ad.productItem_id = p2.id and ad.send_to = d.id and ad.send_to_qtny_type = qt.id and p4.id =".$itemcat." and ad.send_to_quantity !=0 and DATE(ad.created_at) between '".$frmdt."' and '".$todt."'";

	$table .='<table><thead><tr><th>SrNo.</th><th>Date</th><th>Item Name</th><th>Name of Dept</th><th>Quantity</th><th>Quantity Type</th></tr></thead><tbody>';

	$result = $con->query($sql);

	if ($result->num_rows > 0) 
	{
	    // output data of each row
		while($row = $result->fetch_assoc()) 
		{
			if($row['send_to_qtny_type'] == '1' or $row['send_to_qtny_type'] == '3'){
				if($row['send_to_quantity'] != 0){
					$sendQty= ($row['send_to_quantity']/1000);
				}else{
					$sendQty= $row['send_to_quantity'];
				}
			}else{
				$sendQty= $row['send_to_quantity'];
			}

			$table.= "<tr><td><p>".$row['id']."</p></td><td><p>".$row['senddttime']."</p></td><td><p>".$row['description']."</p></td><td><p>".$row['depart']."</p></td><td><p>".$sendQty."</p></td><td><p>".$row['q_name']."</p></td></tr>";
		}
	
	}

}else{
	if($dptId == '1'){
		if($dptId == '1'){
			$table .="<h4>Process Report:- ".$frmdtl." To ".$todtl."</h4>";
		}
		$table .='<table><thead><tr><th>Date</th><th>Lot Number</th><th>Raw Material</th><th>Raw Material Quantity</th><th>Raw Material Quantity Type</th><th>Soaken Weight</th><th>Final Product</th><th>Quantity</th><th>Type</th><th>Scrap</th><th>Scrap Quantity</th></tr></thead><tbody>';

		$mlsql = "select '1' as is_single,s.lotnumber,s.inputQty, s.admindpt_parent_id,'' as admindptml_parentId,s.wtype,s.wasteqty,s.initial_qnty_type,s.send_to_quantity,s.finalproductId,
		s.qtytypeid,s.water_wt,s.waste,s.id,s.productItem_id,s.senddttime,s.finalprodct,s.q_name from (select ad.lotnumber,ad.waste,ad.admindpt_parent_id,ad.wtype,ad.wasteqty,ad.initial_qnty_type,
		ad.inputQty ,fp.id as finalproductId,ad.id ,fp.item as finalprodct,ad.send_to_quantity,sendqt.q_name,sendqt.id as qtytypeid,ad.created_at as senddttime ,
		ad.water_wt,ad.productItem_id
		from admin_dpts_history ad ,final_product fp ,quantity_type sendqt where ad.final_product = fp.id and ad.send_to_qtny_type = sendqt.id and ad.send_from = '".$dptId."' 
		and ad.send_to_quantity !=0 and ad.created_at between '".$frmdt."' and '".$todt."' and ad.updated_by is null and ad.deleted_by is null order by ad.created_at asc) as s union 
		select '0' as is_single,m.lotnumber,m.inputQty,m.admindpt_parent_id,m.admindptml_parentId,m.wtype,m.wasteqty,m.initial_qnty_type,m.send_to_quantity,m.finalproductId,m.qtytypeid,m.water_wt,
		m.waste,m.id,m.productItem_id,m.senddttime,m.finalprodct,m.q_name from (select adm.lotnumber,admrch.admindpt_parent_id,admrch.admindptml_parentId,adm.wtype,adm.wasteqty,admrch.initial_qnty_type,admrch.inputQty,adm.send_to_quantity,adm.final_product as finalproductId,
		adm.send_to_qtny_type as qtytypeid,admrch.water_wt ,adm.waste, adm.id,admrch.productItem_id, adm.created_at as senddttime ,fp.item as finalprodct,qt.q_name from admin_dpts_mul_raw adm,admin_dpts_mul_raw_child_history admrch ,
		final_product fp ,quantity_type qt where adm.lotnumber = admrch.lotnumber and adm.final_product = fp.id and 
		adm.send_to_qtny_type = qt.id and adm.send_from = '".$dptId."' and adm.send_to_quantity !=0 and adm.created_at between '".$frmdt."' and '".$todt."' and adm.updated_by is null 
		and adm.deleted_by is null order by adm.created_at asc) as m";

		
		$result = $con->query($mlsql);
		
		if ($result->num_rows > 0) 
		{
		    // output data of each row
			while($row = $result->fetch_assoc()) 
			{
				if($row['qtytypeid'] == '1' or $row['qtytypeid'] == '3'){
					if($row['send_to_quantity'] != 0){
						$sendQty= ($row['send_to_quantity']/1000);
					}else{
						$sendQty= $row['send_to_quantity'];
					}
				}else{
					$sendQty= $row['send_to_quantity'];
				}

				$totalinputqtys = '';
				$totalinputqtystypes = '';

				if($row['admindpt_parent_id'] !=null and $row['admindpt_parent_id'] != '0'){
					$admindpts = explode(',',$row['admindpt_parent_id']);
					$inputqtys = explode(',',$row['inputQty']);

					for($i=0;$i< count($admindpts);$i++){
						$adminArys = explode('-',$admindpts[$i]);
						$sql1="select ad.send_to_qtny_type,qt.q_name from admin_dpts ad,quantity_type qt where ad.send_to_qtny_type = qt.id and ad.id =".$adminArys[0].";";
					    $res=mysqli_query($con,$sql1);
						while($row1 = mysqli_fetch_array($res))
						{
							$inputqtytype= $row1['send_to_qtny_type'];
							$totalinputqtystypes.= $row1['q_name'].",";
							if($inputqtytype == '1' or $inputqtytype == '3'){
								if($inputqtys[$i] != 0){
									$inputQty= ($inputqtys[$i]/1000);
								}else{
									$inputQty= $inputqtys[$i];
								}
							}else{
								$inputQty= $inputqtys[$i];
							}
						}
						
						$totalinputqtys .=$inputQty.",";

					}
				}else{
					$admindpts = explode(',',$row['admindptml_parentId']);
					$inputqtys = explode(',',$row['inputQty']);

					for($i=0;$i< count($admindpts);$i++){
						$adminArys = explode('-',$admindpts[$i]);
						$sql1="select ad.send_to_qtny_type,qt.q_name from admin_dpts_mul ad,quantity_type qt where ad.send_to_qtny_type = qt.id and ad.id =".$adminArys[0].";";
						
						$res=mysqli_query($con,$sql1);
						while($row1 = mysqli_fetch_array($res))
						{
							$inputqtytype= $row1['send_to_qtny_type'];
							$totalinputqtystypes.= $row1['q_name'].",";
							if($inputqtytype == '1' or $inputqtytype == '3'){
								if($inputqtys[$i] != 0){
									$inputQty= ($row['inputQty']/1000);
								}else{
									$inputQty= $row['inputQty'];
								}
							}else{
								$inputQty= $row['inputQty'];
							}
						}
						$totalinputqtys .=$inputQty.",";
					}
					
				}
								

				if($row['water_wt'] != 0){
					$waterwt= ($row['water_wt']/1000);
				}else{
					$waterwt= $row['water_wt'];
				}

				$wasteqty =0;
				$wasteType='';
				if($row['waste'] !=null or !empty($row['waste'])){
					if($row['wtype'] == '1' or $row['wtype'] == '3'){
						if($row['wasteqty'] != 0){
							$wasteqty= ($row['wasteqty']/1000);
						}else{
							$wasteqty= $row['wasteqty'];
						}
					}else{
						$wasteqty= $row['wasteqty'];
					}

					$sql1="SELECT name FROM waster_type where id=".$row['waste'].";";
				    $res=mysqli_query($con,$sql1);
					while($row1 = mysqli_fetch_array($res))
					{
						$wasteType= $row1['name'];
					} 
				}
				

				$prodnames = '';
				$inptqtyName = '';
				$sql1="select group_concat(description) as items from productitem p where id in (".$row['productItem_id'].");";
			    $res=mysqli_query($con,$sql1);
				while($row1 = mysqli_fetch_array($res))
				{
					$prodnames= $row1['items'];
				} 

				$date = new DateTime($row['senddttime'], new DateTimeZone('MST'));
				$date->setTimezone(new DateTimeZone('Asia/Kolkata'));

				$table.= "<tr><td><p>".$date->format('Y-m-d H:i:s')."</p></td><td><p>".$row['lotnumber']."</p></td><td><p>".$prodnames."</p></td><td><p>".substr($totalinputqtys, 0,strlen($totalinputqtys)-1)."</p></td><td><p>".substr($totalinputqtystypes, 0,strlen($totalinputqtystypes)-1)."</p></td><td><p>".$waterwt."</p></td><td><p>".$row['finalprodct']."</p></td><td><p>".$sendQty."</p></td><td><p>".$row['q_name']."</p></td><td><p>".$wasteType."</p></td><td><p>".$wasteqty."</p></td></tr>";
			}
	
		}
	}else if($dptId == '5'){
		$table .="<h4>F-39:- ".$frmdtl." To ".$todtl."</h4>";
		$table .='<table><thead><tr><th>Date</th><th>Raw Material</th><th>Raw Material Quantity</th><th>Raw Material Quantity Type</th><th>Final Product</th><th>Quantity</th>
		<th>Quantity Type</th><th>No. of Bags</th></tr></thead><tbody>';

		$sql="select * from (
			select * from (select adh.id,adh.productItem_id,adh.initial_quantity,adh.initial_qnty_type,adh.inputQty,adh.wasteqty,adh.wtype as wasteqtytype,adh.water_wt,adh.totalBoxs,adh.send_to ,fp.item as finalprodct , adh.waste,adh.send_to_qtny_type ,adh.send_to_quantity,adh.created_at,d.depart as todpt,qt.q_name 
			from admin_dpts_history adh,department d,final_product fp,quantity_type qt 
			where adh.send_to = d.id and adh.final_product = fp.id and adh.send_to_qtny_type = qt.id and send_from =5 and adh.created_at between '".$frmdt."' and '".$todt."' and 
			adh.updated_by is null and adh.deleted_by is null ) as s
			union
			select * from (select admrh.id,admrch.productItem_id,admrch.initial_quantity,admrch.initial_qnty_type,admrch.inputQty,admrh.wasteqty,admrh.wtype as wasteqtytype,admrch.water_wt,admrh.totalBoxs,admrh.send_to, fp.item as finalprodct, admrh.waste, admrh.send_to_qtny_type, admrh.send_to_quantity,admrh.created_at,d.depart as todpt,qt.q_name
			from admin_dpts_mul_raw_history admrh,admin_dpts_mul_raw_child_history admrch ,department d,final_product fp ,quantity_type qt where admrh.send_to_qtny_type = qt.id and 
			admrh.final_product = fp.id  and admrh.send_to = d.id and admrh.id = admrch.admr_parent_id and send_from =5 and admrh.created_at between '".$frmdt."' and '".$todt."' and 
			admrh.updated_by is null and admrh.deleted_by is null) as m ) as t order by created_at asc";

		$result = $con->query($sql);

		//echo $sql;

		if ($result->num_rows > 0) 
		{
			// output data of each row
			while($row = $result->fetch_assoc()) 
			{
				$q_name ='';
				$sql1="select q_name from quantity_type where id =".$row['send_to_qtny_type'].";";
				$res=mysqli_query($con,$sql1);
				while($row1 = mysqli_fetch_array($res))
				{
					$q_name= $row1['q_name'];
				} 

				if($row['send_to_qtny_type'] == '1' or $row['send_to_qtny_type'] == '3'){
					if($row['send_to_quantity'] != 0){
						$sendQty= ($row['send_to_quantity']/1000);
					}else{
						$sendQty= $row['send_to_quantity'];
					}
				}else{
					$sendQty= $row['send_to_quantity'];
				}

				if($row['initial_qnty_type'] == '1' or $row['initial_qnty_type'] == '3'){
					if($row['initial_quantity'] != 0){
						$inputQty= ($row['initial_quantity']/1000);
					}else{
						$inputQty= $row['initial_quantity'];
					}
				}else{
					$inputQty= $row['initial_quantity'];
				}

				if($row['water_wt'] != 0){
					$waterwt= ($row['water_wt']/1000);
				}else{
					$waterwt= $row['water_wt'];
				}

				/*
				if($row['wasteqtytype'] == '1' or $row['wasteqtytype'] == '3'){
					if($row['wasteqty'] != 0){
						$wasteqty= ($row['wasteqty']/1000);
					}else{
						$wasteqty= $row['wasteqty'];
					}
				}else{
					$wasteqty= $row['wasteqty'];
				}
				*/

				$prodname='';
				$sql2="select description as items from productitem p where id in (".$row['productItem_id'].");";
				$res2=mysqli_query($con,$sql2);
				while($row2 = mysqli_fetch_array($res2))
				{
					$prodname = $row2['items'];
				}

				$inptqtyName='';
				$sql3="select q_name from quantity_type where id =".$row['initial_qnty_type'].";";
				$res3=mysqli_query($con,$sql3);
				while($row3 = mysqli_fetch_array($res3))
				{
					$inptqtyName = $row3['q_name'];
				}

				$date = new DateTime($row['created_at'], new DateTimeZone('MST'));
				$date->setTimezone(new DateTimeZone('Asia/Kolkata'));

				$table.= "<tr><td><p>".$date->format('Y-m-d H:i:s')."</p></td><td><p>".$prodname."</p></td><td><p>".$inputQty."</p></td>
				<td><p>".$inptqtyName."</p></td><td><p>".$row['finalprodct']."</p></td><td><p>".$sendQty."</p></td><td><p>".$q_name."</p></td>
				<td><p>".$row['totalBoxs']."</p></td></tr>";
				
			}

		}


	}else if($dptId == '2'){
		$table .="<h4>Packaging:- ".$frmdtl." To ".$todtl."</h4>";
		
		$table .='<table><thead><tr><th>Date</th><th>Final Product</th><th>Final Product Quantity(pieces)</th><th>Final Product Quantity(weight))</th><th>Piece/Box</th><th>Weight/Box</th><th>Total Boxes</th><th>Rejection Weight</th></tr></thead><tbody>';

		$sql = "select ptsh.storageId,ptsh.storageID_ml,ptsh.admindpt_parent_details ,ptsh.created_at as senddttime,fp.item as finalprodct,sum(if(ptsh.totalBoxs > 1,ptsh.send_to_quantity * ptsh.totalBoxs,ptsh.send_to_quantity)) as totsend_to_quantity,ptsh.send_to_quantity ,ptsh.send_to_qtny_type as qtytypeid ,qt.q_name,sum(ptsh.totalBoxs) as totalBoxs 
			from package_to_stock_history ptsh,final_product fp,quantity_type qt where ptsh.final_product = fp.id and ptsh.send_to_qtny_type = qt.id  and ptsh.created_at between '".$frmdt."' and '".$todt."' group by ptsh.final_product ,ptsh.send_to_quantity ,ptsh.send_to_qtny_type order by ptsh.created_at asc;";

		$result = $con->query($sql);

		//echo $sql;

		if ($result->num_rows > 0) 
		{
		    // output data of each row
			while($row = $result->fetch_assoc()) 
			{
				if($row['qtytypeid'] == '1' or $row['qtytypeid'] == '3'){
					if($row['totsend_to_quantity'] != 0){
						$totsendQty= ($row['totsend_to_quantity']/1000);
					}else{
						$totsendQty= $row['totsend_to_quantity'];
					}

					if($row['send_to_quantity'] != 0){
						$sendQty= ($row['send_to_quantity']/1000);
					}else{
						$sendQty= $row['send_to_quantity'];
					}
				}else{
					$totsendQty= $row['totsend_to_quantity'];
					$sendQty= $row['send_to_quantity'];
				}

				$tweightqty = 0;
				$tpieceqty = 0;

				$sweightqty = 0;
				$spieceqty = 0;
				if($row['qtytypeid'] == '1'){
					$tweightqty = $totsendQty;
					$sweightqty = $sendQty;
				}else if($row['qtytypeid'] == '2'){
					$tpieceqty = $totsendQty;
					$spieceqty = $sendQty;
				}

				$sendQtyrej = 0;

				$adId = explode(",",$row['admindpt_parent_details']);

				if($row['storageId'] != '0'){
					$sqlreject = "select send_to_qtny_type,send_to_quantity from admin_dpts_history admh where send_from = 2 and waste = 4 and admindpt_parent_id =".$adId[0];
				}else{
					$sqlreject = "select send_to_qtny_type,send_to_quantity from admin_dpts_mul_raw_history admh,admin_dpts_mul_raw_child_history admrch where admh.id = admrch.admr_parent_id 
					and send_from = 2 and waste = 4 and admrch.admindptml_parentId =".$adId[0];
				}
					
				$resultj = $con->query($sqlreject);

				if ($resultj->num_rows > 0) 
				{
				    // output data of each row
					while($rowj = $resultj->fetch_assoc()) 
					{
						if($rowj['send_to_qtny_type'] == '1' or $rowj['send_to_qtny_type'] == '3'){
							if($rowj['send_to_quantity'] != 0){
								$sendQtyrej= ($rowj['send_to_quantity']/1000);
							}else{
								$sendQtyrej= $rowj['send_to_quantity'];
							}
						}else{
							$sendQtyrej= $rowj['send_to_quantity'];
						}
					}
				}

				$date = new DateTime($row['senddttime'], new DateTimeZone('MST'));
				$date->setTimezone(new DateTimeZone('Asia/Kolkata'));

				$table.= "<tr><td><p>".$date->format('Y-m-d H:i:s')."</p></td><td><p>".$row['finalprodct']."</p></td><td><p>".$tpieceqty."</p></td><td><p>".$tweightqty."</p></td><td><p>".$spieceqty."</p></td><td><p>".$sweightqty."</p></td><td><p>".$row['totalBoxs']."</p></td><td><p>".$sendQtyrej."</p></td></tr>";
				
			}
		
		}

	}else if($dptId == 'pur'){
		$table .="<h4>Purchase:- ".$frmdtl." To ".$todtl."</h4>";
		
		$table .='<table><thead><tr><th>Sr.No</th><th>Date</th><th>Challan No.</th><th>Supplier</th><th>Item Category</th><th>Item</th><th>Quantity</th><th>Quantity Type</th></tr></thead><tbody>';

		$sql = "select p.quantity_type,DATE(p.created_at) as senddttime ,p2.item as raw_material ,p3.description as item ,s.name as supplier_name ,p.cha_no ,p.p_id,p.quantity,qt.q_name from purchase p,product p2 ,productitem p3,supplier s,quantity_type qt 
			where p.item = p3.id and p.item_category = p2.id and p.supplier_name = s.id and p.quantity_type = qt.id and p.created_at between '".$frmdt."' and '".$todt."';";
		$result = $con->query($sql);

		//echo $sql;

		if ($result->num_rows > 0) 
		{
		    // output data of each row
			while($row = $result->fetch_assoc()) 
			{
				if($row['quantity_type'] == '1' or $row['quantity_type'] == '3'){
					if($row['quantity'] != 0){
						$sendQty= ($row['quantity']/1000);
					}else{
						$sendQty= $row['quantity'];
					}
				}else{
					$sendQty= $row['quantity'];
				}

				$table.= "<tr><td><p>".$row['p_id']."</p></td><td><p>".$row['senddttime']."</p></td><td><p>".$row['cha_no']."</p></td><td><p>".$row['supplier_name']."</p></td><td><p>".$row['raw_material']."</p></td><td><p>".$row['item']."</p></td><td><p>".$sendQty."</p></td><td><p>".$row['q_name']."</p></td></tr>";
				
			}
		
		}

	}else if($dptId == 'export'){
		$table .="<h4>Export:- ".$frmdtl." To ".$todtl."</h4>";
		
		$table .='<table><thead><tr><th>Sr.No</th><th>Invoice No.</th><th>Date</th><th>Lot Numbers</th><th>Buyer Name</th><th>Product</th><th>Quantity(piece)</th><th>Quantity(weight)</th><th>Carton</th><th>Exported To</th></tr></thead><tbody>';

		$sql = "select group_concat(distinct ste.lotnumber) as lotnumbers,ste.quantity_type,ste.id,ste.invoice_no ,DATE(invoice_dt) as invoice_dt,pm.name ,fp.item,sum(if(ste.no_of_boxes > 1,ste.quantity * ste.no_of_boxes,ste.quantity)) as quantity,qt.q_name,sum(ste.no_of_boxes) no_of_boxes,ste.is_international 
			from stock_to_export ste ,purchaser_master pm,quantity_type qt ,final_product fp 
			where ste.purchaser_name = pm.id  and ste.quantity_type = qt.id and ste.final_productId = fp.id and DATE(ste.invoice_dt) between '".$frmdtl."' and '".$todtl."' group by ste.final_productId ,ste.quantity_type,ste.invoice_no order by ste.created_at asc;";
		$result = $con->query($sql);

		//echo $sql;

		if ($result->num_rows > 0) 
		{
			$totalweight = 0;
			$totalpiece = 0;
			$totalBoxes = 0;
		    // output data of each row
			while($row = $result->fetch_assoc()) 
			{


				if($row['quantity_type'] == '1' or $row['quantity_type'] == '3'){
					if($row['quantity'] != 0){
						$sendQty= ($row['quantity']/1000);
					}else{
						$sendQty= $row['quantity'];
					}
				}else{
					$sendQty= $row['quantity'];
				}

				if($row['is_international'] == '1'){
					$exported = 'International';
				}else{
					$exported = 'Domestic';
				}

				$weightqty = 0;
				$pieceqty = 0;
				if($row['quantity_type'] == '1'){
					$weightqty = $sendQty;
				}else if($row['quantity_type'] == '2'){
					$pieceqty = $sendQty;
				}

				$totalpiece +=$pieceqty;
				$totalweight +=$weightqty;
				$totalBoxes+=$row['no_of_boxes'];

				$table.= "<tr><td><p>".$row['id']."</p></td><td><p>".$row['invoice_no']."</p></td><td><p>".$row['invoice_dt']."</p></td><td><p>".$row['lotnumbers']."</p></td><td><p>".$row['name']."</p></td><td><p>".$row['item']."</p></td><td><p>".$pieceqty."</p></td><td><p>".$weightqty."</p></td><td><p>".$row['no_of_boxes']."</p></td><td><p>".$exported."</p></td></tr>";
				
			}

			$table.= "<tr><td></td><td></td><td></td><td></td><td><p>Total</p></td><td><p>".$totalpiece."</p></td><td><p>".$totalweight."</p></td><td><p>".$totalBoxes."</p></td><td></td></tr>";
		
		}

	}
}

$table.="</tbody></table>";

$mpdf->WriteHTML($table);
$mpdf->Output();
?>