<?php
/**
 * Copyright (C) DairyWindow 2018
 * admin@dairywindow.nz
 */

	/* (C) DairyWindow 2012-2017 */
	
	require "inc/connect.php";

	/**
	 * @param string $text text to search for
	 * @param string $onclick url
	 * @param int    $id unique id
	 * @return string
	 */
	function td($text, $onclick="", $id=0){
		$search = clean($_GET['search']);
		$return = "<td class='text-center' ";
		if(isset($id) && $id!=0) {
		  $return .= "title='".$id."' ";
		}
		if(isset($onclick) && $onclick!=""){
			$return .= "onclick=\"document.location.href='".$onclick."';\" style='cursor:pointer;vertical-align:middle;' ";
		}else{
			$return .= "style='vertical-align:middle;' ";
		}
		$return .= ">";
		if(stristr($text, $search)){
			if(strlen(stristr($text, $search, true))>100) $return .= "&hellip;";
			$return .= substr(stristr($text, $search, true),-100); //show text before search string
			$return .= "<span class='bg-success'>".substr($text, stripos($text, $search), strlen($search))."</span>"; //show search string
			$return .= substr(stristr($text, $search),strlen($search),100); //show text after search string
			if(strlen(stristr($text, $search))>100) $return .= "&hellip;";
		}else{
			if($text == 'View'){
				$return .= '<button class="btn btn-sm btn-default">'.$text.'</button>';
			}else{
				$return .= $text;
			}
		}
		$return .= "</td>";
		return $return;
	}
	
	$title = "Search";
	require "inc/head.php";
	$menu = "search";
	require "inc/menu.php";
	
	if($debug) showdebug();
	
	$season_start = date('Y-m-d',strtotime(date('Y',strtotime('last year')).'-'.$_SESSION['season_start'].'-01'));
	
?>
	
	<div id="topDIV" ><h1 class="text-center"><?php echo $title; ?></h1></div>
	
	<div id="main" role="main">
	<?php
		
		if(isset($_GET['search']) && $_GET['search']!=""){
			
			$search = clean($_GET['search']);
			
			/*
				cypher/batch
				equipment
				location
				machine
				manufacturer
				damages
				evap downtime
				damages
				release main
				release sub
				result
				spec/product
				test
				type
				unit
				user
			*/
			
			$sql="
				Insert into dw_search      ( sText ,        sUser      , sDate)
				Values                     (  ?    ,         ?         , now())
			";
			if(!savesql($link, $sql, "si", array($search, $_SESSION['userID']      ), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
			
			$sql="
				# cypher
				Select
					dw_cypher.cypherID,
					dw_cypher.cypherText,
					dw_cypher.cypherManuDate,
					dw_cypher.cypherOrderID,
					dw_cypher.cypherNotesFP,
					dw_cypher.cypherFirstUnit,
					dw_cypher.cypherLastUnit,
					dw_spec.specStyle,
					dw_spec.specText
				From
					dw_spec Right Join
					dw_cypher
						On dw_spec.specID = dw_cypher.cypherSpec
				Where
					(dw_cypher.cypherText Like ? Or
						dw_cypher.cypherManuDate Like ? Or # default
						dw_cypher.cypherManuDate Like ? Or # d/m/y
						dw_cypher.cypherManuDate Like ? Or # y-m-d
						dw_cypher.cypherNotesFP Like ? Or
						dw_cypher.cypherOrderID Like ? Or
						dw_cypher.cypherFirstUnit Like ? Or
						dw_cypher.cypherLastUnit Like ?) And
					dw_cypher.cypherSite = ?
				Order By
					dw_cypher.cypherManuDate Desc
				#Limit 20
			";
			//searching date for 'blah' will convert it to 01-01-1970
			if(!$cypher_query = mysqli_prepared_query($link, $sql, "ssssssssi", array('%'.$search.'%', '%'.$search.'%', '%'.date('d/m/y', strtotime($search)).'%', '%'.date('Y-m-d', strtotime($search)).'%', '%'.$search.'%', '%'.$search.'%', '%'.$search.'%', '%'.$search.'%', $_SESSION['userSite']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
			$cypher_num = mysqli_prepared_num_rows($cypher_query);
			if($cypher_num > 0){ ?>
				<hr>
				<table class="table table-hover sorttable" >
					<thead>
						<tr >
							<th class="text-center" >Results</th>
							<?php if($_SESSION['cypherorbatch']==0 || $_SESSION['cypherorbatch']=='0' || $_SESSION['cypherorbatch']==2 || $_SESSION['cypherorbatch']=='2'){ ?><th class="text-center" >Cypher</th><?php } ?>
							<th class="text-center" >Date</th>
							<?php if($_SESSION['cypherorbatch']=='1' || $_SESSION['cypherorbatch']=='2' || $_SESSION['cypherorbatch']>=1){ ?><th class="text-center" >Batch</th><?php } ?>
							<th class="text-center" >Product</th>
							<th class="text-center" >First unit</th>
							<th class="text-center" >Last unit</th>
							<th class="text-center" >Notes</th>
						</tr>
					</thead>
					<tbody>
						<?php foreach($cypher_query as $array){
							echo "<tr>";
								echo td("View", "results.php?r=".$array['cypherID']);
								if($_SESSION['cypherorbatch']==0 || $_SESSION['cypherorbatch']=='0' || $_SESSION['cypherorbatch']==2 || $_SESSION['cypherorbatch']=='2'){
									echo td($array['cypherText'], "cypher.php?c=".$array['cypherID']);
								}
								echo "<td class='text-center'>".date('d/m/y',strtotime($array['cypherManuDate']))."</td>";
								if($_SESSION['cypherorbatch']=='1' || $_SESSION['cypherorbatch']=='2' || $_SESSION['cypherorbatch']>=1){
									echo td($array['cypherOrderID'], "cypher.php?c=".$array['cypherID']);
								}
								echo td($array['specText'], "cypher.php?c=".$array['cypherID']);
								
								if(!function_exists('getunits')) require "inc/getunits.php";
								$unitlist = getunits($array['cypherID'], $array['specStyle']);
								$xfirstunit = $unitlist[0];
								$xlastunit = $unitlist[1];
								
								if($array['cypherFirstUnit']=="" || $array['cypherFirstUnit']==null) echo td($xfirstunit, "cypher.php?c=".$array['cypherID']);
								else echo td($array['cypherFirstUnit']);
								
								if($array['cypherLastUnit' ]=="" || $array['cypherLastUnit' ]==null) echo td($xlastunit, "cypher.php?c=".$array['cypherID']);
								else echo td($array['cypherLastUnit'], "cypher.php?c=".$array['cypherID']);
								
								echo td($array['cypherNotesFP'], "cypher.php?c=".$array['cypherID']);
								
							echo "</tr>";
						}
						?>
					</tbody>
				</table>
				<?php
			}
			
			$sql="
				# equipment
				Select
					dw_equipment.eqID,
					dw_equipment.eqUniqueID,
					dw_manufacturer.manuText,
					dw_equipment.eqText,
					dw_status.statusText,
					dw_equipment.eqSerial,
					dw_location.locationText,
					dw_equipment.eqNotes
				From
					dw_manufacturer Right Join
					(dw_status Right Join
					(dw_location Right Join
					dw_equipment
						On dw_location.locationID = dw_equipment.eqLocation)
						On dw_status.statusID = dw_equipment.eqStatus)
						On dw_manufacturer.manuID = dw_equipment.eqManufacturer
				Where
					(dw_equipment.eqText Like ?) Or
					(dw_equipment.eqSerial Like ?) Or
					(dw_equipment.eqNotes Like ?)
				#Limit 20
			";
			if(!$equipment_query = mysqli_prepared_query($link, $sql, "sss", array('%'.$search.'%', '%'.$search.'%', '%'.$search.'%'), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
			$equipment_num = mysqli_prepared_num_rows($equipment_query);
			if($equipment_num > 0){ ?>
				<hr>
				<table class="table table-hover sorttable" >
					<thead>
						<tr >
							<th class="text-center" >ID</th>
							<th class="text-center" >Manufacturer</th>
							<th class="text-center" >Equipment</th>
							<th class="text-center" >Status</th>
							<th class="text-center" >Serial</th>
							<th class="text-center" >Location</th>
							<th class="text-center" >Notes</th>
						</tr>
					</thead>
					<tbody>
						<?php
							foreach($equipment_query as $array){
								echo "<tr onclick=\"document.location.href='equipment.php?e=".$array['eqID']."';\" style='cursor:pointer;vertical-align:middle;' >";
									echo td($array['eqUniqueID']);
									echo td($array['manuText'], "", $array['eqID']);
									echo td($array['eqText']);
									echo td($array['statusText']);
									echo td($array['eqSerial']);
									echo td($array['locationText']);
									echo td($array['eqNotes']);
								echo "</tr>";
							}
						?>
					</tbody>
				</table>
				<?php
			}
			
			$sql="
				# location
				Select
					dw_location.locationID,
					dw_locgroup.locGroupText,
					dw_location.locationText
				From
					dw_locgroup Right Join
					dw_location
						On dw_locgroup.locGroupID = dw_location.locationGroup
				Where
					(dw_locgroup.locGroupText Like ?) Or
					(dw_locgroup.locGroupText Like ? And
					dw_location.locationActive = 1 And
					dw_location.locationSite = ?)
				Order By
					dw_locgroup.locGroupOrder,
					dw_locgroup.locGroupText,
					dw_location.locationOrder,
					dw_location.locationText
				#Limit 20
			";
			if(!$location_query = mysqli_prepared_query($link, $sql, "ssi", array('%'.$search.'%', '%'.$search.'%', $_SESSION['userSite']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
			$location_num = mysqli_prepared_num_rows($location_query);
			if($location_num > 0){ ?>
				<hr>
				<table class="table table-hover sorttable" >
					<thead>
						<tr >
							<th class="text-center" >Group</th>
							<th class="text-center" >Location</th>
						</tr>
					</thead>
					<tbody>
						<?php 
							foreach($location_query as $array){
								echo "<tr onclick=\"document.location.href='location.php?e=".$array['locationID']."';\" style='cursor:pointer;vertical-align:middle;' >";
									echo td($array['locGroupText']);
									echo td($array['locationText']);
								echo "</tr>";
							}
						?>
					</tbody>
				</table>
				<?php
			}
			
			$sql="
				# machine
				Select
					dw_machine.machineID,
					dw_machine.machineText
				From dw_machine
				Where dw_machine.machineText Like ?
				#Limit 20
			";
			if(!$machine_query = mysqli_prepared_query($link, $sql, "s", array('%'.$search.'%'), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
			$machine_num = mysqli_prepared_num_rows($machine_query);
			if($machine_num > 0){ ?>
				<hr>
				<table class="table table-hover sorttable" >
					<thead>
						<tr >
							<th class="text-center" >Location</th>
						</tr>
					</thead>
					<tbody>
						<?php
							foreach($machine_query as $array){
								echo "<tr onclick=\"document.location.href='machine.php?m=".$array['machineID']."';\" style='cursor:pointer;vertical-align:middle;' >";
									echo td($array['machineText']);
								echo "</tr>";
							}
						?>
					</tbody>
				</table>
				<?php
			}
			
			$sql="
				# shift notes
				Select
					dw_sn_value.valueValue,
					dw_sn_value.valueDay,
					dw_sn_field.fieldTitle,
					dw_sn_page.pageID,
					dw_sn_page.pageText
				From
					dw_sn_value Inner Join
					dw_sn_field
						On dw_sn_value.valueField = dw_sn_field.fieldID Inner Join
					dw_sn_page
						On dw_sn_field.fieldPage = dw_sn_page.pageID
				Where
				  dw_sn_value.valueValue Like ? And
					dw_sn_page.pageSite = ? And
					dw_sn_page.pageActive = 1 And
					dw_sn_field.fieldActive = 1
				Order By
					dw_sn_value.valueDay Desc,
					dw_sn_page.pageOrder,
					dw_sn_page.pageText,
					dw_sn_field.fieldOrder,
					dw_sn_field.fieldTitle
				#Limit 20
			";
			if(!$snotes_query = mysqli_prepared_query($link, $sql, "si", array('%'.$search.'%', $_SESSION['userSite']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
			$snotes_num = mysqli_prepared_num_rows($snotes_query);
			if($snotes_num > 0){ ?>
				<hr>
				<table class="table table-hover sorttable" >
					<thead>
						<tr >
							<th class="text-center" >Date</th>
							<th class="text-center" >Tab</th>
							<th class="text-center" >Field</th>
							<th class="text-center" >Text</th>
						</tr>
					</thead>
					<tbody>
						<?php
							foreach($snotes_query as $array){
								echo "<tr onclick=\"document.location.href='shiftnotes.php?c=".$array['valueDay']."&p=".$array['pageID']."';\" style='cursor:pointer;vertical-align:middle;' >";
									echo td(date('d/m/Y',strtotime($array['valueDay'])));
									echo td($array['pageText'], "", $array['valueDay']);
									echo td($array['fieldTitle']);
									echo td($array['valueValue']);
								echo "</tr>";
							}
						?>
					</tbody>
				</table>
				<?php
			}
			
			$sql="
				# test
				Select
					dw_test.testID,
					dw_test.testText,
					dw_test.testTextLab,
					dw_test.testTextCOA,
					dw_test.testCode,
					dw_test.testMethod,
					dw_test.testLabName,
					dw_test.testUnits,
					dw_group.groupText
				From
					dw_test Left Join
					dw_group
						On dw_test.testGroup = dw_group.groupID
				Where
					(dw_test.testText Like ? Or
						dw_test.testTextLab Like ? Or
						dw_test.testTextCOA Like ? Or
						dw_test.testCode Like ? Or
						dw_test.testMethod Like ? Or
						dw_test.testLabName Like ? Or
						dw_test.testUnits Like ?) And
					dw_test.testSite = ? And
					dw_test.testActive = 1
				Order By
					dw_group.groupOrder,
					dw_test.testOrder
				#Limit 20
			";
			if(!$test_query = mysqli_prepared_query($link, $sql, "sssssssi", array('%'.$search.'%', '%'.$search.'%', '%'.$search.'%', '%'.$search.'%', '%'.$search.'%', '%'.$search.'%', '%'.$search.'%', $_SESSION['userSite']), __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
			$test_num = mysqli_prepared_num_rows($test_query);
			if($test_num > 0){ ?>
				<hr>
				<table class="table table-hover sorttable" >
					<thead>
						<tr >
							<th class="text-center" >Group</th>
							<th class="text-center" >Test (local)</th>
							<th class="text-center" >Test (lab)</th>
							<th class="text-center" >Test (reports)</th>
							<th class="text-center" >Code</th>
							<th class="text-center" >Method</th>
							<th class="text-center" >Units</th>
							<th class="text-center" >Lab name</th>
						</tr>
					</thead>
					<tbody>
						<?php
							foreach($test_query as $array){
								echo "<tr onclick=\"document.location.href='test.php?t=".$array['testID']."';\" style='cursor:pointer;vertical-align:middle;' >";
									echo td($array['groupText']);
									echo td($array['testText'],"", $array['testID']);
									echo td($array['testTextLab']);
									echo td($array['testTextCOA']);
									echo td($array['testCode']);
									echo td($array['testMethod']);
									echo td($array['testUnits']);
									echo td($array['testLabName']);
								echo "</tr>";
							}
						?>
					</tbody>
				</table>
				<?php
			}


			$sql="
				# result
				SELECT
				  dw_cypher.cypherID,
				  dw_cypher.cypherManuDate,
				  dw_cypher.cypherText,
				  dw_cypher.cypherOrderID,
				  dw_spec.specText,
				  dw_unit.unitText,
				  dw_location.locationText,
				  dw_result.resultFriendly,
				  dw_result.resultReference
				FROM
				  dw_result
				  LEFT JOIN dw_unit ON dw_unit.unitID = dw_result.resultUnit
				  LEFT JOIN dw_cypher ON dw_cypher.cypherID = dw_unit.unitCypher
				  LEFT JOIN dw_spec ON dw_spec.specID = dw_cypher.cypherSpec
				  LEFT JOIN dw_location ON dw_location.locationID = dw_unit.unitLocation
				WHERE
				  dw_result.resultReference like ?
			";
			$types = 's';
			$params = array('%'.$search.'%');
			if(!$result_query = mysqli_prepared_query($link, $sql, $types, $params, __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
			$result_num = mysqli_prepared_num_rows($result_query);
			if($result_num > 0){ ?>
				<hr>
				<table class="table table-hover sorttable" >
					<thead>
					<tr >
						<th class="text-center" >Cypher</th>
						<th class="text-center" >Date</th>
						<th class="text-center" >Batch/Order</th>
						<th class="text-center" >Product</th>
						<th class="text-center" >Sample</th>
						<th class="text-center" >Location</th>
						<th class="text-center" >Result</th>
						<th class="text-center" >Reference</th>
					</tr>
					</thead>
					<tbody>
					<?php
						foreach($result_query as $array){
							echo "<tr onclick=\"document.location.href='results.php?r=".$array['cypherID']."';\" style='cursor:pointer;vertical-align:middle;' >";
							echo td($array['cypherText']);
							echo td(date('d/m/y', strtotime($array['cypherManuDate'])));
							echo td($array['cypherOrderID']);
							echo td($array['specText']);
							echo td($array['unitText']);
							echo td($array['locationText']);
							echo td($array['resultFriendly']);
							echo td($array['resultReference']);
							echo "</tr>";
						}
					?>
					</tbody>
				</table>
				<?php
			}


			$sql="
				# user
				Select
					dw_user.userID,
					dw_user.userFirst,
					dw_user.userLast,
					dw_user.userTitle
				From
					dw_user
				Where
					dw_user.userFirst Like ? Or
					dw_user.userLast  Like ?
				Order By
					dw_user.userFirst,
					dw_user.userLast
				#Limit 20
			";
			$types = 'ss';
			$params = array('%'.$search.'%', '%'.$search.'%');
			if(!$user_query = mysqli_prepared_query($link, $sql, $types, $params, __FILE__, __LINE__)) reporterror(get_error(), $sql, $_SERVER['REQUEST_URI'], __FILE__, __LINE__);
			$user_num = mysqli_prepared_num_rows($user_query);
			if($user_num > 0){ ?>
				<hr>
				<table class="table table-hover sorttable" >
					<thead>
					<tr >
						<th class="text-center" >First name</th>
						<th class="text-center" >Last name</th>
						<th class="text-center" >Title</th>
					</tr>
					</thead>
					<tbody>
					<?php
						foreach($user_query as $array){
							echo "<tr onclick=\"document.location.href='user.php?u=".$array['userID']."';\" style='cursor:pointer;vertical-align:middle;' >";
							echo td($array['userFirst'],"", $array['userID']);
							echo td($array['userLast']);
							echo td($array['userTitle']);
							echo "</tr>";
						}
					?>
					</tbody>
				</table>
				<?php
			}


			if(
				$cypher_num   ==0 &&
				$equipment_num==0 &&
				$location_num ==0 &&
				$machine_num  ==0 &&
				$snotes_num   ==0 &&
				$test_num     ==0 &&
				$result_num   ==0 &&
				$user_num     ==0
			) echo "<p class='bg-warning text-center'>DairyWindow has finished searching text fields for ".$search.", but could not find it. Maybe try changing what you are searching for?</p>";
		}
		
		?>
	</div>

	<?php require "inc/footer.php"; ?>