<?php
/**
 * Copyright (C) DairyWindow 2019
 * admin@dairywindow.nz
 */

	/* (C) DairyWindow 2012-2017 */
	
	function blue(){
		global $pdf;
		$pdf->SetTextColor(0,0,128); //Blue
		$pdf->SetFont('Arial','',10);
	}
	function black(){
		global $pdf;
		$pdf->SetTextColor(0,0,0); //Black
		$pdf->SetFont('Arial','B',10);
	}
	
	function showhead(){
		global $link;
		global $output;
		global $tickbox;
		global $notickbox;
		global $pdf;
		global $filename;
		
		$sql="
			# get cypher/spec
			Select
				dw_cypher.cypherText,
				dw_spec.specText,
				dw_user.userFirst,
				dw_user.userLast,
				dw_cypher.cypherManuDate
			From
				(dw_spec Right Join
				dw_cypher
					On dw_spec.specID = dw_cypher.cypherSpec) Left Join
				dw_user
					On dw_cypher.cypherSentBy = dw_user.userID
			Where
				dw_cypher.cypherID = ?
		";
		if(!$cypherquery = mysqli_prepared_query($link, $sql, "i", array($_GET['cypher']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
		$cypherarray = $cypherquery[0];
		
		$sql="
			# get form
			Select
				dw_form.formText
			From
				dw_form
			Where
				dw_form.formID = ?
		";
		if(!$formquery = mysqli_prepared_query($link, $sql, "i", array($_GET['form']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
		$formarray = $formquery[0];
		//$pdf->CellFitScale(20,10,'Title',1,1,'C');
		$pdf->SetTextColor(153,51,0); //Brown
		$pdf->SetFont('Arial','B',14);
		$pdf->CellFitScale(277,5,'SAMPLE SUBMISSION FORM',0,1);
		$pdf->Image('images/logo_pdf.jpg',250,7,null,8,null,'https://www.dairywindow.nz');
		//           file                , x ,y, w  ,h,type, link
		$pdf->SetFillColor(217); //Grey
		
		black(); $pdf->CellFitScale(30,5,'Business name:',1,0,'L',1);
		blue();  $pdf->CellFitScale(50,5, $_SESSION['comp_name_long'],'LTR');
		black(); $pdf->CellFitScale(41,5,'Report to:',1,0,'L',1);
		blue();  $pdf->CellFitScale(60,5, $_SESSION['report_to'],1);
		black(); $pdf->CellFitScale(41,5,'Submission reference:',1,0,'L',1);
		blue();  $pdf->CellFitScale(55,5, str_replace('.pdf', '', $filename),1,0);
		$pdf->SetFillColor(255); //White
		$pdf->CellFitScale(10,5,' ','L',1,'L',1); //this is to blank out filename overfill
		$pdf->SetFillColor(217); //Grey
		
		black(); $pdf->CellFitScale(30,5,'Postal address:','LTR',0,'L',1);
		blue();  $pdf->CellFitScale(50,5, $_SESSION['address_1'],'LTR');
		black(); $pdf->CellFitScale(41,5,'Copies to:',1,0,'L',1);
		blue();  $pdf->CellFitScale(60,5, $_SESSION['copies_to'],1);
		black(); $pdf->CellFitScale(41,5,'Date submitted:',1,0,'L',1);
		blue();  $pdf->CellFitScale(55,5,date('j F Y'),1,1);
		
		black(); $pdf->CellFitScale(30,5,'','LR',0,'L',1);
		blue();  $pdf->CellFitScale(50,5, $_SESSION['address_2'],'LR');
		black(); $pdf->CellFitScale(41,5,'Order number:',1,0,'L',1);
		blue();  if(isset($_REQUEST['order']) && $_REQUEST['order']!="" && $_REQUEST['order']!=null) $pdf->CellFitScale(60,5, $_REQUEST['order'],1);
						 else $pdf->CellFitScale(60,5, $_SESSION['order_number'],1);
		black(); $pdf->CellFitScale(41,5,'Submitted by:',1,0,'L',1);
		blue();  $pdf->CellFitScale(55,5, $_SESSION['userFirst']." ".$_SESSION['userLast'],1,1);
		
		black(); $pdf->CellFitScale(30,5,'','LBR',0,'L',1);
		blue();  $pdf->CellFitScale(50,5, $_SESSION['address_3'],'LBR');
		black(); $pdf->CellFitScale(41,5,'Quote number:',1,0,'L',1);
		blue();  $pdf->CellFitScale(60,5, $_SESSION['quote_number'],1);
		black(); $pdf->CellFitScale(41,5,'Results required by:',1,0,'L',1);
		blue();  $pdf->CellFitScale(55,5,'As per contract',1,1);
		
		black(); $pdf->CellFitScale(30,5,'Contact person:',1,0,'L',1);
		blue();  $pdf->CellFitScale(50,5, $_SESSION['contact_person'],1);
		black(); $pdf->CellFitScale(41,5,'Market access:',1,0,'L',1);
		blue();  $pdf->CellFitScale(60,5, $_SESSION['market_access'],1);
		black(); $pdf->CellFitScale(41,5,'Sampled by:',1,0,'L',1);
		blue();  $pdf->CellFitScale(55,5,sampled_by(),1,0);
		$pdf->SetFillColor(255); //White
		$pdf->CellFitScale(10,5,' ','L',1,'L',1); //this is to blank out sampled_by name overfill
		$pdf->SetFillColor(217); //Grey
		
		black(); $pdf->CellFitScale(30,5,'Phone:',1,0,'L',1);
		blue();  $pdf->CellFitScale(50,5, $_SESSION['phone'],1);
		black(); $pdf->CellFitScale(41,5,'Report by:','LTR',0,'L',1);
		black(); $pdf->CellFitScale(20,5,'Email','LTR',0,'C',0);
		black(); $pdf->CellFitScale(20,5,'Fax','LTR',0,'C',0);
		black(); $pdf->CellFitScale(20,5,'Mail','LTR',0,'C',0);
		black(); $pdf->CellFitScale(41,5,'Sample storage','LTR',0,'L',1);
		black(); $pdf->CellFitScale(18.33,5,'Ambient','LTR',0,'C',0);
		black(); $pdf->CellFitScale(18,   5,'Chill','LTR',0,'C',0);
		black(); $pdf->CellFitScale(18.66,5,'Frozen','LTR',1,'C',0);
		
		black(); $pdf->CellFitScale(30,5,'Mobile:',1,0,'L',1);
		blue();  $pdf->CellFitScale(50,5, $_SESSION['mobile'],1);
		black(); $pdf->CellFitScale(41,5,'','LBR',0,'L',1);
		blue();  $pdf->SetFont('ZapfDingbats');
		$pdf->CellFitScale(20,5,'4','LBR',0,'C',0);
		$pdf->CellFitScale(20,5,'','LBR',0,'C',0);
		$pdf->CellFitScale(20,5,'','LBR',0,'C',0);
		$pdf->SetFont('Arial');
		black(); $pdf->CellFitScale(41,5,'instructions','LR',0,'L',1);
		black(); $pdf->CellFitScale(18.33,5,utf8_decode('18 to 25 °C'),'LR',0,'C',0);
		black(); $pdf->CellFitScale(18.33,5,utf8_decode('2 to 8 °C'),'LR',0,'C',0);
		black(); $pdf->CellFitScale(18.33,5,utf8_decode('-15 to -25°C'),'LR',1,'C',0);
		
		black(); $pdf->CellFitScale(30,5,'Fax:',1,0,'L',1);
		blue();  $pdf->CellFitScale(50,5, $_SESSION['fax'],1);
		black(); $pdf->CellFitScale(41,5,'Sampling date:',1,0,'L',1);
		blue();  $pdf->CellFitScale(60,5,date('d F Y',strtotime($cypherarray['cypherManuDate'])),1,0,'C',0);
		black(); $pdf->CellFitScale(41,5,'','LBR',0,'L',1);
		blue();  $pdf->SetFont('ZapfDingbats');
		if($_GET['temp']==1) $pdf->CellFitScale(18.33,5,'4','LBR',0,'C',0);
		else $pdf->CellFitScale(18.33,5,'','LBR',0,'C',0);
		if($_GET['temp']==2) $pdf->CellFitScale(18.33,5,'4','LBR',0,'C',0);
		else $pdf->CellFitScale(18.33,5,'','LBR',0,'C',0);
		if($_GET['temp']==3) $pdf->CellFitScale(18.33,5,'4','LBR',1,'C',0);
		else $pdf->CellFitScale(18.33,5,'','LBR',1,'C',0);
		$pdf->SetFont('Arial');
		
		if(isset($_GET['notes']) && $_GET['notes']!=''){
			black(); $pdf->CellFitScale(30,5,'Notes for lab:',1,0,'L',1);
			blue();  $pdf->CellFitScale(247,5,clean($_GET['notes']),1,1,'L',0);
		}
		$pdf->SetTextColor(0,0,0); //Black
	}
	
	function sampled_by(){
		global $link;
		$sampled_by = "";
		
		$sql="
			Select
				dw_spec.specStyle
			From
				dw_cypher Inner Join
				dw_spec
					On dw_cypher.cypherSpec = dw_spec.specID
			Where
				dw_cypher.cypherID = ?
		";
		if(!$qry = mysqli_prepared_query($link, $sql, "i", array($_GET['cypher']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
		$array = $qry[0];
		$specstyle = $array['specStyle'];
		if($specstyle==1){ //final product
			$sql="
				Select
					dw_sn_value.valueValue
				From
					dw_sn_field Inner Join
					dw_sn_value
						On dw_sn_value.valueField = dw_sn_field.fieldID Inner Join
					dw_settings
						On dw_settings.subform_sampled_by1 = dw_sn_field.fieldID Inner Join
					dw_cypher
						On dw_sn_value.valueDay = dw_cypher.cypherManuDate
				Where
					dw_settings.settingsID = ? And
					dw_cypher.cypherID = ?
			";
			if(!$qry = mysqli_prepared_query($link, $sql, "ii", array($_SESSION['userSite'], $_GET['cypher']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
			if(mysqli_prepared_num_rows($qry)>0){
				$array = $qry[0];
				$sampled_by .= $array['valueValue'];
			}
			
			$sql="
				Select
					dw_sn_value.valueValue
				From
					dw_sn_field Inner Join
					dw_sn_value
						On dw_sn_value.valueField = dw_sn_field.fieldID Inner Join
					dw_settings
						On dw_settings.subform_sampled_by2 = dw_sn_field.fieldID Inner Join
					dw_cypher
						On dw_sn_value.valueDay = dw_cypher.cypherManuDate
				Where
					dw_settings.settingsID = ? And
					dw_cypher.cypherID = ?
			";
			if(!$qry = mysqli_prepared_query($link, $sql, "ii", array($_SESSION['userSite'], $_GET['cypher']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
			if(mysqli_prepared_num_rows($qry)>0){
				$array = $qry[0];
				if($sampled_by!="") $sampled_by .= " & ";
				$sampled_by .= $array['valueValue'];
			}
		}else{ //in process
			$sampled_by = $_SESSION['userFirst']." ".$_SESSION['userLast'];
		}
		return $sampled_by;
	}
	
	function rejig_unit_indexes($units, $new_order){
		global $debug;
		
		if($debug){
			echo "<pre>Was:";
			print_r($units);
			echo "</pre>";
		}
		
		if(count($units) != count($new_order)){
			if($debug) echo "Error: Count of units (".count($units).") is NOT the same as count of orders (".count($new_order).")<br>\n";
			return $units;
		}
		
		$new_units = [];
		for($i=0; $i<count($units); $i++){
			$new_units[$i] = $units[$new_order[$i]];
		}
		
		if($debug){
			echo "<pre>Now:";
			print_r($new_units);
			echo "</pre><hr>";
		}
		
		return $new_units;
	}
	
	function showdata($format){
		$max_units_per_page = 9;
		global $link;
		global $form;
		global $debug;
		
		global $tickbox;
		global $notickbox;
		
		global $pdf;
		global $filename;
		
		global $message; 
		
		$tier2array = array();
		
		$output = '';
		$number_of_ticks = 0;
		
		switch($format){
			case 'preview':
				echo $output;
				break;
			case 'print':
				//echo $output;
				require_once('inc/pdf/fpdf.php');
				require_once('inc/pdf/color.inc.php');
				require_once('inc/pdf/htmlparser.inc.php');
				require_once('inc/pdf/pdftable.php');
				
				class PDF extends PDFTable{
					function Footer(){
						$this->SetFont('Arial','',8);
						$this->SetXY(10,-10);
						$this->CellFitScale(0,10,'Printed '.date('d/m/y G:i'),0,0,'L');
						$this->SetXY(10,-10);
						$this->CellFitScale(0,10,'Cypher:'.$_GET['cypher'].' Form:'.$_GET['form'],0,0,'C');
						$this->SetXY(200,-10);
						$this->CellFitScale(0,10,'Page '.$this->PageNo().' of {nb}',0,0,'R');
					}

					//Cell with horizontal scaling if text is too wide
					function CellFit($w, $h=0, $txt='', $border=0, $ln=0, $align='', $fill=false, $link='', $scale=false, $force=true){
						//Get string width
						$str_width= $this->GetStringWidth($txt);

						//Calculate ratio to fit cell
						if($w==0) $w = $this->w-$this->rMargin-$this->x;
						if($str_width==0) $ratio = ($w-$this->cMargin*2);
						else $ratio = ($w-$this->cMargin*2)/$str_width;

						$fit = ($ratio < 1 || ($ratio > 1 && $force));
						if ($fit){
							if ($scale)	{
								//Calculate horizontal scaling
								$horiz_scale= $ratio*100.0;
								//Set horizontal scaling
								$this->_out(sprintf('BT %.2F Tz ET', $horiz_scale));
							}else{
								//Calculate character spacing in points
								$char_space=($w-$this->cMargin*2-$str_width)/max($this->MBGetStringLength($txt)-1, 1)*$this->k;
								//Set character spacing
								$this->_out(sprintf('BT %.2F Tc ET', $char_space));
							}
							//Override user alignment (since text will fill up cell)
							$align='';
						}

						//Pass on to Cell method
						$this->Cell($w, $h, $txt, $border, $ln, $align, $fill, $link);

						//Reset character spacing/horizontal scaling
						if($fit) $this->_out('BT '.($scale ? '100 Tz' : '0 Tc').' ET');
					}

					//Cell with horizontal scaling only if necessary
					function CellFitScale($w, $h=0, $txt='', $border=0, $ln=0, $align='', $fill=false, $link=''){
						$this->CellFit($w, $h, $txt, $border, $ln, $align, $fill, $link, true, false);
					}

					//Cell with horizontal scaling always
					function CellFitScaleForce($w, $h=0, $txt='', $border=0, $ln=0, $align='', $fill=false, $link=''){
						$this->CellFit($w, $h, $txt, $border, $ln, $align, $fill, $link, true, true);
					}

					//Cell with character spacing only if necessary
					function CellFitSpace($w, $h=0, $txt='', $border=0, $ln=0, $align='', $fill=false, $link=''){
						$this->CellFit($w, $h, $txt, $border, $ln, $align, $fill, $link, false, false);
					}

					//Cell with character spacing always
					function CellFitSpaceForce($w, $h=0, $txt='', $border=0, $ln=0, $align='', $fill=false, $link=''){
						//Same as calling CellFit directly
						$this->CellFit($w, $h, $txt, $border, $ln, $align, $fill, $link, false, true);
					}

					//Patch to also work with CJK double-byte text
					function MBGetStringLength($s){
						if($this->CurrentFont['type']=='Type0'){
							$len = 0;
							$nbbytes = strlen($s);
							for ($i = 0; $i < $nbbytes; $i++){
								if (ord($s[$i])<128)
									$len++;
								else{
									$len++;
									$i++;
								}
							}
							return $len;
						}
						else return strlen($s);
					}

				}
				
				$pdf = new PDF();
				$pdf->SetMargins(10,10,10);
				$pdf->defaultFontFamily = 'Arial';
				$pdf->defaultFontStyle = '';
				$pdf->defaultFontSize = 10;
				$pdf->SetFont($pdf->defaultFontFamily, $pdf->defaultFontStyle, $pdf->defaultFontSize);
				$pdf->AliasNbPages(); // Total number of pages
				$pdf->SetDisplayMode('fullpage');
				$pdf->SetAuthor($_SESSION['userFirst']." ".$_SESSION['userLast'],1);
				$pdf->SetCreator('DairyWindow',1);
				
				$sql="
					Select
						dw_spec.specID,
						dw_spec.specText,
						dw_cypher.cypherText,
						dw_cypher.cypherOrderID
					From
						dw_cypher Inner Join
						dw_spec
							On dw_cypher.cypherSpec = dw_spec.specID
					Where
						dw_cypher.cypherID = ?
				";
				if(!$qry = mysqli_prepared_query($link, $sql, "i", array($_GET['cypher']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
				$cypherarray = $qry[0];
				
				$filename = $_SESSION['filename_prefix']."-";
				switch($_SESSION['cypherorbatch']){
					case 0: case '0': //cyphers only
						$filename .= $cypherarray['cypherText'];
						break;
					case 1: case '1': //batches only
						$filename .= $cypherarray['cypherOrderID'];
						break;
					default: //both
						$filename .= $cypherarray['cypherText'].'-'.$cypherarray['cypherOrderID'];
						break;
				}
				
				$sql="
					Select
						dw_form.formText
					From
						dw_form
					Where
						dw_form.formID = ?
				";
				if(!$qry = mysqli_prepared_query($link, $sql, "i", array($_GET['form']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
				$formarray = $qry[0];
				
				$filename .= "-".$cypherarray['specText']."-".$formarray['formText'];
				
				$pdf->SetTitle($filename,1);
				$pdf->AddPage('L'); //Landscape
				break;
			case 'csv':
				//echo $output;
				break;
		}
		
		$sql="
			# get cypher/spec
			Select
				dw_cypher.cypherText,
				dw_cypher.cypherOrderID,
				dw_cypher.cypherManuDate,
				dw_cypher.cypherSent,
				dw_spec.specID,
				dw_spec.specText,
				dw_spec.specCode,
				dw_spec.specVersion,
				dw_po.poShortDesc As specPO
			From
				dw_spec Right Join
				dw_cypher
					On dw_spec.specID = dw_cypher.cypherSpec Left Join
				dw_po
					On dw_spec.specPO = dw_po.poID
			Where
				dw_cypher.cypherID = ?
		";
		if(!$cypherquery = mysqli_prepared_query($link, $sql, "i", array($_GET['cypher']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
		$cypherarray = $cypherquery[0];
		
		$sql="
			# get units/locations
			Select
				dw_unit.unitID,
				dw_unit.unitText,
				dw_unit.unitCompText,
				dw_location.locationText
			From
				dw_cypher Right Join
				(dw_location Right Join
				dw_unit
					On dw_location.locationID = dw_unit.unitLocation)
					On dw_cypher.cypherID = dw_unit.unitCypher
			Where
				dw_unit.unitActive = 1 And
				dw_cypher.cypherID = ?
			Group By
				dw_unit.unitID
			Order By
				Case dw_unit.unitText
					When 'F' Then 1
					When 'M' Then 2
					When 'L' Then 3
					Else 4
				End,
				Field(dw_unit.unitText, 'F', 'M', 'L', 'Comp', 'Comp1', 'Comp 1', 'Comp2', 'Comp 2', 'Composite'),
				LPad(dw_unit.unitText,20,0),
				dw_location.locationOrder,
				dw_location.locationText
		";
		if(!$unitquery = mysqli_prepared_query($link, $sql, "i", array($_GET['cypher']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
		$num_units = mysqli_prepared_num_rows($unitquery);
		$unitids = array();
		$units = array();
		$locations = array();
		$hascomp = false;
		foreach($unitquery as $unitarray){
			$unitids[] = $unitarray['unitID'];
			$units[]   = $unitarray['unitText'];
			$comps[]   = $unitarray['unitCompText'];
			if(in_array($unitarray['unitText'], array('Comp', 'comp', 'Composite', 'composite', 'Comp1', 'comp1', 'Comp 1', 'comp 1', 'Comp2', 'comp2', 'Comp 2', 'comp 2'))) $hascomp = true;
			$locations[] = $unitarray['locationText'];
		}

		//sort array of units
		//if(is_array($units)) sort($units);
		//$units[0] will usually be "Comp" for FP at this point

		$comp_names = array(
			'comp',
			'Comp',
			'composite',
			'Composite',
			'comp1',
			'Comp1',
			'comp2',
			'Comp2',
			'comp 1',
			'Comp 1',
			'comp 2',
			'Comp 2'
		);

		//move comp to end of array
		//get the key of the comp
		$compkey = 0;
		foreach($comp_names as $comp_name){
			$counter = 0;
			foreach($units as $unit){
				if($unit==$comp_name){
					$compkey = $counter;
					break;
				}
				$counter++;
			}
		}

		//check if the key we got is in fact a comp
		if(in_array($units[$compkey], $comp_names)){
			//get the value of comp (for adding later)
			$comp_value = $units[$compkey];
			//delete comp
			array_splice($units, $compkey, 1);
			//reindex keys
			//$units = array_values($units); //dont need this due to array_splice
			//add comp to end
			$units[] = $comp_value;
		}

		//rearrange units for Yashili
		if($_SESSION['userSite']==5 && in_array($num_units, [10,11,12,19,20,21])){
			
			if($debug){
				echo "site: ".$_SESSION['userSite']."<br>\nhascomp: ";
				if($hascomp) echo "true";
				else echo "false";
				echo "<br>\n";
			}

			$new_order = [];
			for($i=0; $i<$num_units; $i++){
				$new_order[] = $i;
			}

			if(!$hascomp && $num_units==10){
				if($debug) echo 'no tier 3, no comp<br>';
				$new_order = array(0,2,4,6,8, 1,3,5,7,9);
				//                 [ Tier 1 ] [ Tier 2 ]
			}
			elseif( $hascomp && $num_units==11){
				if($debug) echo 'no tier 3, has comp<br>';
				$new_order = array(0,2,4,6,8, 1,3,5,7,9, 10);
				//                 [ Tier 1 ] [ Tier 2 ] [Comp]
			}
			elseif( $hascomp && $num_units==12){
				if($debug) echo 'no tier 3, has 2 comps<br>';
				$new_order = array(0,2,4,6,8, 1,3,5,7,9, 10,11);
				//                 [ Tier 1 ] [ Tier 2 ] [Comps]
			}
			elseif(!$hascomp && $num_units==19){
				if($debug) echo 'has tier 3, no comp<br>';
				$new_order = array(0,4,8,12,16, 2,3,6,7,10,11,14,15,18, 1,5,9,13,17);
				//                 [ Tier 1  ]  [       Tier 3       ]  [  Tier 2  ]
			}
			elseif( $hascomp && $num_units==20){
				if($debug) echo 'has tier 3, has comp<br>';
				$new_order = array(0,4,8,12,16, 2,3,6,7,10,11,14,15,18, 1,5,9,13,17, 19);
				//                 [ Tier 1  ]  [       Tier 3       ]  [  Tier 2  ] [Comp]
			}
			elseif( $hascomp && $num_units==21){
				if($debug) echo 'has tier 3, has 2 comps<br>';
				$new_order = array(0,4,8,12,16, 2,3,6,7,10,11,14,15,18, 1,5,9,13,17, 19,20);
				//                 [ Tier 1  ]  [       Tier 3       ]  [  Tier 2  ] [Comps]
			}
			else{
				if($debug) echo 'else<br>';
			}

			if($debug) echo 'Rejigging unitids...<br>';
			$unitids   = rejig_unit_indexes($unitids,   $new_order);
			if($debug) echo 'Rejigging units...<br>';
			$units     = rejig_unit_indexes($units,     $new_order);
			if($debug) echo 'Rejigging comps...<br>';
			$comps     = rejig_unit_indexes($comps,     $new_order);
			if($debug) echo 'Rejigging locations...<br>';
			$locations = rejig_unit_indexes($locations, $new_order);

		}//otherwise order is simply 1..n
		
		$sql="
			# get tests
			Select
				dw_sub.subFreq,
				dw_test.testID,
				dw_test.testTextLab,
				dw_test.testCode
			From
				dw_sub Inner Join
				dw_test On dw_test.testID = dw_sub.subTest Inner Join
				dw_group On dw_test.testGroup = dw_group.groupID Inner Join
				dw_cypher On dw_cypher.cypherSpec = dw_sub.subSpec
			Where
				dw_sub.subForm = ? And
				dw_cypher.cypherID = ?
			Order By
				dw_group.groupOrder,
				dw_test.testOrder
		";
		if(!$testquery = mysqli_prepared_query($link, $sql, "ii", array($_GET['form'], $_GET['cypher']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
		
		$num_tests = mysqli_prepared_num_rows($testquery);
		$tests = array();
		$codes = array();
		$freqs = array();
		foreach($testquery as $testarray){
			if($debug) $tests[] = clean($testarray['testTextLab'])." [".$testarray['testID']."]";
			else $tests[] = clean($testarray['testTextLab']);
			$codes[] = $testarray['testCode'];
			$freqs[] = $testarray['subFreq'];
		}
		
		$pages = ceil($num_units/$max_units_per_page);
		
		if($debug) $output .= "num_units: ".$num_units."<br>pages: ".$pages."<br>hascomp: ".($hascomp ? 'Yes' : 'No')."<br>";
		
		$sql="
			# get form
			Select
				dw_form.formText,
				dw_form.formCSVprefix
			From
				dw_form
			Where
				dw_form.formID = ?
		";
		if(!$formquery = mysqli_prepared_query($link, $sql, "i", array(filter_input(INPUT_GET, 'form')), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
		$formarray = $formquery[0];
		
		$filename = $_SESSION['filename_prefix']."-";
		switch($_SESSION['cypherorbatch']){
			case 0: case '0': //cyphers only
				$filename .= $cypherarray['cypherText'];
				break;
			case 1: case '1': //batches only
				$filename .= $cypherarray['cypherOrderID'];
				break;
			default: //both
				$filename .= $cypherarray['cypherText'].'-'.$cypherarray['cypherOrderID'];
				break;
		}
		
		//Issue #9, ensure filename is unique
		$filename .= "-".$cypherarray['specText']."-".$formarray['formText'];
		if($_SESSION['csv_unique']==1){
			$value = 1;
			while(true){
				$new_filename = $filename."-".str_pad($value,2,"0",STR_PAD_LEFT);
				if(!file_exists($new_filename)) break;
				$value++;
			}
			$filename = $new_filename;
		}
		
		if($format=='csv'){
			
			$filename .= ".csv";
			
			$fullfilename = "subforms/".$_SESSION['userSite']."/".$filename;
			if(!$fh = fopen($fullfilename, 'w')) reporterror('Could not open '.$fullfilename.' for writing, check path exists','None',__FILE__,__LINE__);
			//CSV header
			$output = "\"PreReg_Ref\",\"Barcode\",\"Client\",\"JobType\",\"SubRef\",\"Order\",\"Urgent\",\"SampledBy\",\"CommentsReport\",\"CommentsLab\",\"SampleType\",\"Description\",\"QualityUnit\",\"ProductCode\",\"SpecCode\",\"SpecVersion\",\"SampledDate\",\"TestCode\",\"Cypher\",\"Batch\",\"CypherManuDate\",\"Comp\",\"PO\"\n";
			fwrite($fh, $output);
		}else{
			$filename .= ".pdf";
		}
		
		for($page=1; $page<= $pages; $page++){
			$start_unit = ($page-1)*$max_units_per_page;
			if($page==$pages) $end_unit = $num_units-1; //for last page
			else $end_unit = (($page*$max_units_per_page)-1);
			if($debug){
				$output .= "page: ".$page." of ".$pages."<br>
					start_unit: ".$start_unit."<br>
					end_unit: ".$end_unit."<br>";
			}
			
			if($page==1 && $format=='print') $output .= showhead();
			
			if($format!='csv'){
				$output .= "<style type='text/css'>
				.center{ text-align:center; }
				</style><table class='table' width='100%' border='1'><thead><tr><td bgcolor='#d9d9d9' colspan='".(($end_unit-$start_unit)+2)."'><b><center>SAMPLE DETAILS</center></b></td></tr><tr>";
			}
			
			if($format=='print'){
				
				$output .= "<td><b>Sample number (lab use only)</b></td>";
				for($u=$start_unit; $u<=$end_unit; $u++){
					$output .= "<td>&nbsp;</td>";
				}
				$output .= "</tr><tr><td>Product or Spec:</td>";
				for($u=$start_unit; $u<=$end_unit; $u++){
					$output .= "<td><center>".$cypherarray['specText']."</center></td>";
				}
				$output .= "</tr><tr><td>Cypher or Batch:</td>";
				for($u=$start_unit; $u<=$end_unit; $u++){
					$output .= "<td><center>";
					if($_SESSION['cypherorbatch']==0 || $_SESSION['cypherorbatch']=='0' || $_SESSION['cypherorbatch']==2 || $_SESSION['cypherorbatch']=='2') $output .= $cypherarray['cypherText']." ";
					if($_SESSION['cypherorbatch']==1 || $_SESSION['cypherorbatch']=='1' || $_SESSION['cypherorbatch']==2 || $_SESSION['cypherorbatch']=='2') $output .= $cypherarray['cypherOrderID'];
					$output .= "</center></td>";
				}
				$output .= "</tr><tr><td>Manufacture date:</td>";
				for($u=$start_unit; $u<=$end_unit; $u++){
					$output .= "<td><center>".date('d M y',strtotime($cypherarray['cypherManuDate']))."</center></td>";
				}
				$output .= "</tr><tr><td>Location or Description:</td>";
				for($u=$start_unit; $u<=$end_unit; $u++){
					$output .= "<td><center>".$locations[$u]."</center></td>";
				}
				$output .= "</tr><tr><td>Sample reference:</td>";
				for($u=$start_unit; $u<=$end_unit; $u++){
					$output .= "<td class='center'><center>".$units[$u];
					
					if($comps[$u]!="" && $comps[$u]!=null && $comps[$u]!=0 && $comps[$u]!="0"){
						$sql="
							Select dw_unit.unitText
							From dw_unit
							Where dw_unit.unitID = ?
						";
						if(!$comp_query2 = mysqli_prepared_query($link, $sql, "i", array($comps[$u]), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
						$comp_row2 = $comp_query2[0];
						$output .= " (".$comp_row2['unitText'].")";
					}
					$output .= "</center></td>";
				}
				$output .= "</tr><tr><td bgcolor='#d9d9d9'>Test</td><td colspan='".(($end_unit-$start_unit)+1)."' bgcolor='#d9d9d9'><center>Analytical requirements</center></td></tr><tr>";
			}

			if($format=='preview'){
				$output .= "<th>&nbsp;</th>";
				//if($debug) echo print_r($locations, true);
				if(count($locations>0) && count($units>0)){
					for($u=$start_unit; $u<=$end_unit; $u++){
						$output .= "<th>".$locations[$u]."<br>".$units[$u];
						if($debug) $output .= " <small>(".$u.")</small>";
						if($comps[$u]!="" && $comps[$u]!=null){
							$comp = explode('_', $comps[$u]);
							$sql="
								Select dw_unit.unitText
								From dw_unit
								Where dw_unit.unitID = ?
							";
							if(!$comp_query2 = mysqli_prepared_query($link, $sql, "i", array($comp[0]), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
							$comp_row2 = $comp_query2[0];
							$output .= "<br><small>(".$comp_row2['unitText'].")</small>";
						}
						$output .= "</th>";
					}
				}
			}
			if($format!='csv') $output .= "</tr></thead>";
			for($t=0; $t<$num_tests; $t++){ //for each test
				if($format!='csv'){
					$output .= "<tr><td title='freq:".$freqs[$t]."'>".$tests[$t];
					if($codes[$t]!='') $output .= " <small>(".$codes[$t].")</small>";
					$output .= "</td>";
				}
				for($u=$start_unit; $u<=$end_unit; $u++){
					if($format=='csv'){
						//start forming line of text for CSV
						$colE = substr($filename,strlen($_SESSION['filename_prefix'])+1,-4);
						
						$sampled_by = sampled_by();

						if(!empty($_REQUEST['order'])) $specPO = clean($_REQUEST['order']);
						else $specPO = $_SESSION['order_number'];

						if($comps[$u]!="" && $comps[$u]!=null && $comps[$u]!=0){
							//part of a comp
							if(!in_array($unitids[$u], $tier2array)){
								$sql="
									Select dw_unit.unitText
									From dw_unit
									Where dw_unit.unitID = ?
								";
								if(!$comp_query2 = mysqli_prepared_query($link, $sql, "i", array($comps[$u]), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
								$comp_row2 = $comp_query2[0];

								switch($_SESSION['cypherorbatch']){
									case 0: //cypher only
										$data = "\"".$colE."\",,\"".$_SESSION['subform_client']."\",\"Routine\",\"".$colE."\",\"".$specPO."\",,\"".$sampled_by."\",,\"".clean($_GET['notes'])."\",\"Dairy\",\"".$locations[$u]."\",\"".$unitids[$u]."_".$units[$u]."\",\"".$cypherarray['specCode']."\",\"".$cypherarray['specText']."\",\"".$cypherarray['specVersion']."\",".date('d/m/Y H:i').",\"InformationOnly\",\"".$cypherarray['cypherText'   ]."\",\"".$cypherarray['cypherText'   ]."\",".date('d/m/Y H:i',strtotime($cypherarray['cypherManuDate'])).",\"".$comps[$u]."_".$comp_row2['unitText']."\",\"".$specPO."\"\n";
										break;
									case 1: //batch only
										$data = "\"".$colE."\",,\"".$_SESSION['subform_client']."\",\"Routine\",\"".$colE."\",\"".$specPO."\",,\"".$sampled_by."\",,\"".clean($_GET['notes'])."\",\"Dairy\",\"".$locations[$u]."\",\"".$unitids[$u]."_".$units[$u]."\",\"".$cypherarray['specCode']."\",\"".$cypherarray['specText']."\",\"".$cypherarray['specVersion']."\",".date('d/m/Y H:i').",\"InformationOnly\",\"".$cypherarray['cypherOrderID']."\",\"".$cypherarray['cypherOrderID']."\",".date('d/m/Y H:i',strtotime($cypherarray['cypherManuDate'])).",\"".$comps[$u]."_".$comp_row2['unitText']."\",\"".$specPO."\"\n";
										break;
									default: //both
										$data = "\"".$colE."\",,\"".$_SESSION['subform_client']."\",\"Routine\",\"".$colE."\",\"".$specPO."\",,\"".$sampled_by."\",,\"".clean($_GET['notes'])."\",\"Dairy\",\"".$locations[$u]."\",\"".$unitids[$u]."_".$units[$u]."\",\"".$cypherarray['specCode']."\",\"".$cypherarray['specText']."\",\"".$cypherarray['specVersion']."\",".date('d/m/Y H:i').",\"InformationOnly\",\"".$cypherarray['cypherText'   ]."\",\"".$cypherarray['cypherOrderID']."\",".date('d/m/Y H:i',strtotime($cypherarray['cypherManuDate'])).",\"".$comps[$u]."_".$comp_row2['unitText']."\",\"".$specPO."\"\n";
										//         PreReg_Ref Barcode                  Client         JobType      SubRef          Order   Urgent   Sampled By               CommentsReport      CommentsLab       SampleType           Description     QualityUnit                   ProductCode                         SpecCode                         SpecVersion                     SampledDate          TestCode                        Cypher                                Batch                                                                 CypherManuDate                           Comp                             PO
								}
								$tier2array[] = $unitids[$u];
							}
						}else{
							//not part of a comp
							$data = "\"".$colE."\",,\"".$_SESSION['subform_client']."\",\"Routine\",\"".$colE."\",\"".$specPO."\",,\"".$sampled_by."\",,\"".clean($_GET['notes'])."\",\"Dairy\",\"".$locations[$u]."\",\"".$unitids[$u]."_".$units[$u]."\",\"".$cypherarray['specCode']."\",\"".$cypherarray['specText']."\",\"".$cypherarray['specVersion']."\",".date('d/m/Y H:i').",\"".$codes[$t]."\",\"".$cypherarray['cypherText']."\",\"".$cypherarray['cypherOrderID']."\",".date('d/m/Y H:i',strtotime($cypherarray['cypherManuDate'])).",\"\",\"".$specPO."\"\n";
							//         PreReg_Ref Barcode                  Client         JobType       SubRef         Order   Urgent   SampledBy                 CommentsReport     CommentsLab      SampleType            Description    QualityUnit                    ProductCode                         SpecCode                         SpecVersion                 SampledDate             TestCode                        Cypher                             Batch                                                                 CypherManuDate       Comp          PO
						}
						//$csv_data[$u][$t] = $data;
					}else{
						$output .= "<td ><font family='ZapfDingbats'>";
						//if($debug) $output .= 'u:'.$u.' t:'.$t.' freqs[t]:'.$freqs[$t];
					}
					if($hascomp==true){
						$thecomp = $num_units - 1; //0 index
						$last_unit = $num_units - 2; //not comp
						//if($debug) $output .= "u: ".$u."<br>thecomp: " . $thecomp . "<br>last_unit: ".$last_unit."<br>";
						switch($freqs[$t]){
							case 1: //comp only
								if($u==$thecomp){
									$output .= $tickbox; // Comp
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else $output .= $notickbox;
								break;
							case 2: //even
								if($u%2==0 && $u!= $thecomp){
									$output .= $tickbox; // First
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else $output .= $notickbox;
								break;
							case 3: // first, mid, last
								if($u==0){
									$output .= $tickbox; // First
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else if($u==floor(($num_units-1)/2)){
									$output .= $tickbox; // Mid
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else if($u==$last_unit){
									$output .= $tickbox; // Last
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else $output .= $notickbox;
								break;
							case 4: // first, mid, last, comp
								if($u==0){
									$output .= $tickbox; // First
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else if($u==floor(($num_units-1)/2)){
									$output .= $tickbox; // Mid
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else if($u==$last_unit){
									$output .= $tickbox; // Last
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else if($u==$thecomp){
									$output .= $tickbox; // Comp
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else $output .= $notickbox;
								break;
							case 5: // all units, not comp
								if($u==$thecomp) $output .= $notickbox; // Comp
								else{
									$output .= $tickbox;
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								break;
							case 6: // all units, plus comp
								$output .= $tickbox;
								$number_of_ticks++;
								if($format=='csv') $csv_data[$u][$t] = $data;
								break;
							case 7: // first only
								if($u==0){
									$output .= $tickbox; // First
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}else{
									$output .= $notickbox;
								}
								break;
							case 8: // last only
								if($u==$last_unit){
									$output .= $tickbox; // Last
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}else{
									$output .= $notickbox;
								}
								break;
							case 9: // first, last
								if($u==0){
									$output .= $tickbox; // First
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else if($u==$last_unit){
									$output .= $tickbox; // Last
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else $output .= $notickbox;
								break;
							case 10: // Tier 1
								if($u>=0 && $u<=4){
									$output .= $tickbox; // Last
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}else{
									$output .= $notickbox;
								}
								break;
							case 11: // first, comp
								if($u==0){
									$output .= $tickbox; // First
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else if($u==$thecomp){
									$output .= $tickbox; // Last (comp)
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else $output .= $notickbox;
								break;
							case 12: // Tier 2 (composites)
								if(stristr($units[$u],"Comp")){
									$output .= $tickbox;
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}else{
									$output .= $notickbox;
								}
								break;
							case 13: // Tier 3
								if($u>=5 && $u<=13){
									$output .= $tickbox;
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else $output .= $notickbox;
								break;
							case 14: //odd
								if($u%2!=0 && $u!= $thecomp){
									$output .= $tickbox;
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else $output .= $notickbox;
								break;
							default:
								//reporterror('Error with frequency. Comp is true, Frequency is '.$freqs[$t], $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
								$sql="
									Select
										dw_frequency.freqAlgorithm
									From
										dw_frequency
									Where
										dw_frequency.freqID = ?
								";
								if(!$freq_query = mysqli_prepared_query($link, $sql, "i", array($freqs[$t]), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
								$freq_row = $freq_query[0];
								if(!is_array($freq_row['freqAlgorithm'])){
									$freq_row['freqAlgorithm'] = explode(',', $freq_row['freqAlgorithm']);
								}
								if($debug) $output.= '&nbsp;<br>&nbsp;'; //to allow room for the sql buttons

								if(isset($freq_row['freqAlgorithm']) && in_array($u, $freq_row['freqAlgorithm'])){
									$output .= $tickbox;
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
						}
					}else{
						//no composite sample
						$last_unit = $num_units - 1;
						//if($debug) $output .= "u: ".$u."<br>last_unit: ".$last_unit."<br>";
						switch($freqs[$t]){
							case 1: //comp only
								$output .= $notickbox;
								break;
							case 2: //even
								if($u%2==0){
									$output .= $tickbox;
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else $output .= $notickbox;
								break;
							case 3: // first, mid, last
								if($u==0){
									$output .= $tickbox; // First
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else if($u==floor($num_units/2)){
									$output .= $tickbox; // Mid
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else if($u==$last_unit){
									$output .= $tickbox; // Last
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else $output .= $notickbox;
								break;
							case 4: // first, mid, last, comp
								if($u==0){
									$output .= $tickbox; // First
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else if($u==floor($num_units/2)){
									$output .= $tickbox; // Mid
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else if($u==$last_unit){
									$output .= $tickbox; // Last
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else $output .= $notickbox;
								break;
							case 5: // all units, not comp
								//or
							case 6: // all units, plus comp
								$output .= $tickbox;
								$number_of_ticks++;
								if($format=='csv') $csv_data[$u][$t] = $data;
								break;
							case 7: // first only
								if($u==0){
									$output .= $tickbox; // First
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}else{
									$output .= $notickbox;
								}
								break;
							case 8: // last only
								if($u==$last_unit){
									$output .= $tickbox; // Last
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}else{
									$output .= $notickbox;
								}
								break;
							case 9: // first, last
								if($u==0){
									$output .= $tickbox; // First
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else if($u==$last_unit){
									$output .= $tickbox; // Last
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else $output .= $notickbox;
								break;
							case 10: // Tier 1
								if($u>=0 && $u<=4){
									$output .= $tickbox;
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}else{
									$output .= $notickbox;
								}
								break;
							case 11: // first, comp
								if($u==0){
									$output .= $tickbox; // First
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else $output .= $notickbox;
								break;
							case 12: // Tier 2 (composites)
								$output .= $notickbox;
								break;
							case 13: // Tier 3
								if($u>=5 && $u<=13){
									$output .= $tickbox;
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}else{
									$output .= $notickbox;
								}
								break;
							case 14: //odd
								if($u%2!=0){
									$output .= $tickbox;
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
								else $output .= $notickbox;
								break;
							default:
								//reporterror('Error with frequency. Comp is false, Frequency is '.$freqs[$t], $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
								$sql="
									Select
										dw_frequency.freqAlgorithm
									From
										dw_frequency
									Where
										dw_frequency.freqID = ?
								";
								if(!$freq_query = mysqli_prepared_query($link, $sql, "i", array($freqs[$t]), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
								$freq_row = $freq_query[0];
								//$output.=$freq_row['freqAlgorithm'];
								
								if(is_string($freq_row['freqAlgorithm'])) $frequencies = explode(',', $freq_row['freqAlgorithm']);
								else $frequencies = $freq_row['freqAlgorithm'];
								
								if(in_array($u, $frequencies)){
									$output .= $tickbox;
									$number_of_ticks++;
									if($format=='csv') $csv_data[$u][$t] = $data;
								}
						}
					}
					$output .= "</font></td>";
				}
				$output .= "</tr>";
			}
			$output .= "</table><pagebreak/>";
		}
		
		$output = substr($output, 0, -12); //remove <pagebreak/>
		if($number_of_ticks<1 && isset($_GET['form'])){
			$message['type']="error";
			$message['text']="There are no tests selected on this submission form.<br>Click <a href='submod.php?";
			if(isset($cypherarray['specID'])) $message['text'].="product=".$cypherarray['specID']."&amp;";
			$message['text'].="form=".$_GET['form']."'>Edit tests on form</a> to add some or OK to continue";
		}
		
		switch($format){
			case 'preview':
				echo $output;
				//echo "<br>page:".$page."<br>pages:".$pages."<br>";
				break;
			case 'print':
				//echo $output;
				$outputs = explode('<pagebreak/>', $output);
				$page=1;
				foreach($outputs as $thispage){
					if($thispage!='' && $thispage!=null){
						$pdf->htmltable($thispage);
						if($pages!=1 && $page<$pages) $pdf->Addpage('L');
					}
					$page++;
				}
				$pdf->output($filename,'D',true);
				break;
			case 'csv':
				if(isset($csv_data) && $csv_data!="" && $csv_data!=null){
					if(isset($csv_data) && $csv_data!="" && $csv_data!=null){
						//print_r($csv_data);
						foreach($csv_data as $units){
							//print_r($units);
							foreach($units as $tests){
								//print_r($tests);
								fwrite($fh, $tests);
							}
						}
					}
				}
				fclose($fh);

				if(!$debug){
					//email csv
					require_once('inc/PHPMailer/class.phpmailer.php');
					$mail = new PHPMailer();

					$mail->setFrom('admin@dairywindow.nz', $_SESSION['email_name']);
					$mail->addAddress($_SESSION['email_to'], $_SESSION['ext_lab']);     // Add a recipient
					$mail->addReplyTo($_SESSION['userEmail'], $_SESSION['userFirst']." ".$_SESSION['userLast']);
					$mail->addCC($_SESSION['email_cc']);
					$mail->addCC($_SESSION['email2_cc']);

					$mail->addAttachment($fullfilename); // Add attachments

					$mail->Subject = $filename;
					$body = 'Please find attached submission form '.$filename.' from '.$_SESSION['comp_name_long'].' via DairyWindow';
					$mail->Body = $body;
					$mail->AltBody = $body;

					if($mail->send()){
						$sql = "
						update dw_cypher
						set cypherSent = 1
						where cypherID = ?
					";
						if(!savesql($link, $sql, "i", array($_GET['cypher']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);

						$message['type'] = "ok";
						$message['text'] = "Email with <a href='".$fullfilename."'>".$filename."</a> attached has been sent to lab";

						//if($debug) echo $message['text'];
					//}else{
						//if($debug) die(print_r(error_get_last(), true)."\r\n".$mail->ErrorInfo);else reporterror($mail->ErrorInfo, 'None', $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
					}
				}else{
					echo '<a href="'.$fullfilename.'">'.$fullfilename.'</a>'.":<br>\r\n";
					echo file_get_contents($fullfilename);
					die();
				}
				break;
			default:
				reporterror("Error: Unknown format (".$format.")", 'None', $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
		}
		
	}
	
	require "inc/connect.php";
	require "inc/fade.php";

	if(isset($_POST['btnPrint'])){
		$tickbox = '4';
		$notickbox = '';
		if(isset($_GET['cypher']) && $_GET['cypher']!='' && $_GET['cypher']!=0 && isset($_GET['form']) && $_GET['form']!='' && $_GET['form']!=0) showdata('print');
	}else{
		$tickbox = '&#10004;';
		$notickbox = '&nbsp;';
		if(isset($_POST['btnExport']) || isset($_POST['confirm'])){ //CSV
			if(isset($_GET['cypher']) && $_GET['cypher']!='' && $_GET['cypher']!=0 && isset($_GET['form']) && $_GET['form']!='' && $_GET['form']!=0) showdata('csv');
		}
		if(isset($_POST['btnExportAsk'])){ //CSV
			$message['type'] = "warning";
			$message['text'] = "This sub form has already been sent to the lab. Are you sure you want to send it again? Usually the lab only accepts the first submisson";
		}
		
		$title = "Submission form";
		require "inc/head.php";
		$menu = "subform";
		require "inc/menu.php";
		
		if(isset($_GET['cypher']) && $_GET['cypher']!='') $cypher = $_GET['cypher'];
		else $cypher=0;
		if(isset($_GET['form']) && $_GET['form']!='') $form = $_GET['form'];
		else $form=0;
		
		if($debug) showdebug();
		
	?>

<div id='loading' ><img src='images/loading-small.gif' alt='Please wait. Loading...' width='111' height='156' style='margin-top:150px;' class='center-block'><br><div class='text-center'><img src='images/carregando.gif' width='16' height='16'>&nbsp;Please wait while loading...</div></div>

<h1 class='text-center'><?php echo $title; ?></h1>

<div class='col-md-3' role='complementary'>
	<form class="form-horizontal" method="get" id='mainform' >
		<div class="form-group"><!-- Cypher -->
			<label for="cypher" class="col-sm-2 control-label"><?php
				switch($_SESSION['cypherorbatch']){
					case 0: case '0': //cyphers only
						echo 'Cypher';
						break;
					case 1: case '1': //batches only
						echo 'Batch';
						break;
					default: //both
						echo 'Cypher/batch';
						break;
				}
			?></label>
			<div class="col-sm-10">
				<?php
					$sql="
						Select
							dw_cypher.cypherID,
							dw_cypher.cypherText,
							dw_cypher.cypherOrderID,
							dw_cypher.cypherManuDate,
							dw_spec.specText,
							dw_spec.specStyle,
							dw_cypher.cypherSent,
							dw_po.poShortDesc As specPO
						From
							(dw_spec Right Join
							dw_cypher On dw_spec.specID = dw_cypher.cypherSpec) Right Join
							dw_unit On dw_cypher.cypherID = dw_unit.unitCypher Left Join
							dw_po On dw_spec.specPO = dw_po.poID
						Where
							dw_cypher.cypherSite = ? And
							dw_spec.specActive = 1 And
							dw_unit.unitID Is Not Null And
							dw_spec.specSend = 1 And
							dw_cypher.cypherManuDate >= ?
						Group By
							dw_cypher.cypherID, dw_po.poShortDesc
						Order By
							dw_cypher.cypherManuDate Desc,
							dw_spec.specOrder
					";
					if(!$cypherquery = mysqli_prepared_query($link, $sql, "is", array($_SESSION['userSite'], $startDate), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
					echo "<select id='cypher' name='cypher' class='form-control' onchange='submit();'>";
					if(mysqli_prepared_num_rows($cypherquery)<1){
						echo '<option value="0">No cyphers/batches!</option>';
					}else{
						echo '<option value="0">&nbsp;</option>';
						foreach($cypherquery as $cypherarray){
							echo "<option value='".$cypherarray['cypherID']."'";
							if(isset($_GET['cypher']) && $_GET['cypher']==$cypherarray['cypherID']) echo " selected";
							echo ">";
							if($cypherarray['specStyle']==1){ //FP
								if($_SESSION['cypherorbatch']==0) echo $cypherarray['cypherText'];else if($_SESSION['cypherorbatch']==1) echo $cypherarray['cypherOrderID'];else echo $cypherarray['cypherText']." ".$cypherarray['cypherOrderID'];
							}else echo date('d/m/y', strtotime($cypherarray['cypherManuDate'])); //IP
							echo " ".$cypherarray['specText'];
							if($debug) echo " [".$cypherarray['cypherID']."]";
							if($cypherarray['cypherSent']==1) echo " &#10004;";
							echo "</option>";
						}
					}
					echo "</select>";
				?>
			</div>
		</div>
		<div class="form-group"><!-- Form -->
			<label for="form" class="col-sm-2 control-label">Form</label>
			<div class="col-sm-10">
				<?php
					if(isset($_GET['cypher']) && $_GET['cypher']!='' && $_GET['cypher']!=null){
						$sql="
							Select
								dw_form.formID,
								dw_form.formText
							From
								dw_cypher Left Join
								(dw_form Right Join
								dw_sub
									On dw_form.formID = dw_sub.subForm)
									On dw_cypher.cypherSpec = dw_sub.subSpec
							Where
								dw_cypher.cypherID = ? And
								dw_form.formSite = ?
							Group By
								dw_form.formID
							Order By
								dw_form.formOrder
						";
						if(!$formquery = mysqli_prepared_query($link, $sql, "ii", array($_GET['cypher'], $_SESSION['userSite']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
					}else{
						$sql="
							Select
								dw_form.formID,
								dw_form.formText
							From
								dw_form
							Where
								dw_form.formSite = ?
							Order By
								dw_form.formOrder
						";
						if(!$formquery = mysqli_prepared_query($link, $sql, "i", array($_SESSION['userSite']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
					}
					echo "<select id='form' name='form' class='form-control' onchange='submit();'>
						<option value='0'>&nbsp;</option>";
					foreach($formquery as $formarray){
						echo "<option value='".$formarray['formID']."'";
							if(isset($_GET['form']) && $_GET['form']==$formarray['formID']) echo " selected";
							echo ">".$formarray['formText'];
							if($debug) echo " [".$formarray['formID']."]";
						echo "</option>";
					}
					echo "</select>";
				?>
			</div>
		</div>
		<div class="form-group"><!-- Temp -->
			<label for="temp" class="col-sm-2 control-label">Temperature</label>
			<div class="col-sm-10">
				<select id='temp' name='temp' class='form-control'>
					<option value="1"<?php if(isset($_GET['temp']) && ($_GET['temp']==1 || $_GET['temp']=='1')) echo " selected"; ?> >Ambient (18°C to 25°C)</option>
					<option value="2"<?php if(isset($_GET['temp']) && ($_GET['temp']==2 || $_GET['temp']=='2')) echo " selected"; ?> >Chilled (2°C to 8°C)</option>
					<option value="3"<?php if(isset($_GET['temp']) && ($_GET['temp']==3 || $_GET['temp']=='3')) echo " selected"; ?> >Frozen (-15°C to -25°C)</option>
				</select>
			</div>
		</div>
		<div class="form-group"><!-- Notes -->
			<label for="notes" class="col-sm-2 control-label">Notes for <?php echo $_SESSION['ext_lab']; ?></label>
			<div class="col-sm-10">
				<input type="text" name='notes' class="form-control" id="notes" <?php if(isset($_GET['notes']) && $_GET['notes']!='') echo "value='".$_GET['notes']."'"; ?> >
			</div>
		</div>
		<div class="form-group"><!-- Order -->
			<label for="order" class="col-sm-2 control-label">Order number</label>
			<div class="col-sm-10">
				<?php
					$order = "";
					if(isset($_GET['cypher'])){
						$sql="
							# get po from spec
							Select
								dw_po.poShortDesc,
								dw_spec.specPO
							From
								dw_spec Right Join
								dw_cypher
									On dw_cypher.cypherSpec = dw_spec.specID Left Join
								dw_po
									On dw_spec.specPO = dw_po.poID
							Where
								dw_cypher.cypherID = ?
						";
						if(!$poquery = mysqli_prepared_query($link, $sql, "i", array($_GET['cypher']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
						if(mysqli_prepared_num_rows($poquery)>0){
							//got PO
							if($poquery[0]['specPO']=="" || $poquery[0]['specPO']==null){
								$order = $poquery[0]['poShortDesc'];
							}else{
								$order = $poquery[0]['specPO'];
							}
						}else{
							//no PO
							if(isset($_SESSION['order_number']) && $_SESSION['order_number']!=""){
								$order = $_SESSION['order_number'];
							}else{
								if(isset($_GET['order']) && $_GET['order']!='') $order = $_GET['order'];
							}
						}
					}else{
						if(isset($_SESSION['order_number']) && $_SESSION['order_number']!=""){
							$order = $_SESSION['order_number'];
						}else{
							if(isset($_GET['order']) && $_GET['order']!='') $order = $_GET['order'];
						}
					}
				?>
				<input type="text" name='order' class="form-control" id="order" value="<?php echo $order; ?>" >
			</div>
		</div>
		<div class="form-group"><!-- Button -->
			<div class="col-sm-offset-2 col-sm-10">
				<?php
					if($debug){
						echo "<input type='hidden' name='debug' value='true'>";
					}
				?>
				<button type="submit" class="btn btn-primary"><span class='glyphicon glyphicon-search' aria-hidden='true'></span>&nbsp;Preview</button>
			</div>
		</div>
	</form>
	<?php
		if(isset($_GET['cypher']) && $_GET['cypher']!=0 && isset($_GET['form']) && $_GET['form']!=0){
			/* DO NOT put the form as get! */
			echo "
				<form method='post'>
					<div class='form-group'>
						<div class='col-sm-offset-2 col-sm-10' style='padding-left:0'>
							<a class='btn btn-default' href='print_labels.php?cypher=".$_GET['cypher']."' role='button'><span class='glyphicon glyphicon-barcode' aria-hidden='true'></span>&nbsp;Print labels</a>
							<button type='submit' name='btnPrint' id='btnPrint' class='btn btn-default'><img src='images/pdf.png'>&nbsp;Get PDF</button>
							";
							if(isset($_GET['cypher'])){
								$sql="
									Select cypherSent
									From dw_cypher
									Where cypherID = ?
								";
								if(!$query = mysqli_prepared_query($link, $sql, "i", array($_GET['cypher']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
								if(mysqli_prepared_num_rows($query)>0){
									$cypherSent = $query[0]['cypherSent'];
									if($cypherSent==1){
										//cypher has alredy been sent
										echo "<button type='submit' name='btnExportAsk' id='btnExport' class='btn btn-default'><img src='images/csv.png'>&nbsp;Email CSV</button>";
									}else{
										//cypher has not been sent yet
										echo "<button type='submit' name='btnExport' id='btnExport' class='btn btn-default'><img src='images/csv.png'>&nbsp;Email CSV</button>";
									}
								}else{
									//no records found
									echo "<button type='submit' name='btnExport' id='btnExport' class='btn btn-default'><img src='images/csv.png'>&nbsp;Email CSV</button>";
								}
							}else{
								//cypher not set
							}
							echo "
						</div>
					</div>
					<input type='hidden' name='order' value='".$order."'>
				</form>
			";
		}
	?>
</div>

<div id='main' class='container col-md-9' style='display:none;' role='main'>
	<?php
		if(isset($_GET['cypher']) && $_GET['cypher']!='' && $_GET['cypher']!=0 && isset($_GET['form']) && $_GET['form']!='' && $_GET['form']!=0) showdata('preview');
	?>
</div>
<script src="js/subforms.js"></script>
<?php require "inc/footer.php"; } ?>