<?php
 ini_set('memory_limit', '2G');
$fulloutput = "";

$tab = isset($_GET['tab']) ? $_GET['tab'] : '';
if ($tab !== '') {
	include("../../operainfo/getq.php");
} else {
	include("../../operainfo/getq3.php");
}

$search = safetext2(isset($_GET['search']) ? $_GET['search'] : '');
$searchstkref = safetext2(isset($_GET['stkref']) ? $_GET['stkref'] : '');

if ($searchstkref !== '') {
	$search = $searchstkref;
}

$hostcompanyid = safetext2(isset($_GET['hostcompanyid']) ? $_GET['hostcompanyid'] : '');
$text = safetext2(isset($_GET['text']) ? $_GET['text'] : '');
if ($text == 'undefined') {
	$text = "";
}

$sql = "SELECT stock_ref,label_description FROM stocklist_tarriffs WHERE hostcompany_id = ? and Stock_ref = ?";
$stmt = pdoquery($sql, array($hostcompanyid,$searchstkref));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
	$stkref2 = $row['stock_ref'];
	$lbldesc[$stkref2] = $row['label_description'];
}
			
$sql = "SELECT stock_ref,ordernumber FROM serialnumberpicking GROUP BY ordernumber, stock_ref";
$stmt = pdoquery($sql);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
	$usestk = $row['stock_ref'];
	$useord = $row['ordernumber'];
	$link = $usestk . "ASA" . $useord;
	$md5link2 = md5($link);
	$isactive[$md5link2] = 1;
}
			
$sql = "SELECT Stock_ref,Qty,Qty_Invoiced,Order_number FROM backorders2";
$stmt = pdoquery($sql);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
	$usestk = $row['Stock_ref'];
	$useqty = $row['Qty'];
	if ($row['Qty_Invoiced'] == 0) {
		$link = $usestk . "AXA" . $useqty;
		$md5link = md5($link);
		$link = $usestk . "ASA" . $row['Order_number'];
		$md5link2 = md5($link);
		if (!isset($isactive[$md5link2]) || $isactive[$md5link2] == '') {
			$posord[$md5link] = $row['Order_number'];
		}
	}
}

		
		
$sql = "SELECT stock_ref FROM ISO_employee_product_training WHERE trainee = ? AND type = 'QA' AND signoffby > 0";
$stmt = pdoquery($sql, array($vui));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
	$stkr = $row['stock_ref'];
	$istrained[$stkr] = 1;
}
	
	
if ($searchstkref !== '') {
	$sql = "SELECT Serialnumbers.id, Serialnumbers.Porref, Serialnumbers.Datebookedin FROM supplier_returns_details, Serialnumbers WHERE supplier_returns_details.serialid = Serialnumbers.id AND Serialnumbers.Stock_ref = ? AND supplier_returns_details.stock_ref = ? GROUP BY Serialnumbers.id";
	$params = array($search, $searchstkref);
} else {
	$sql = "SELECT Serialnumbers.id, Serialnumbers.Porref, Serialnumbers.Datebookedin FROM supplier_returns_details, Serialnumbers WHERE supplier_returns_details.serialid = Serialnumbers.id AND Serialnumbers.Stock_ref = ? AND supplier_returns_details.stock_ref = ? GROUP BY Serialnumbers.id";
	$params = array($search, $search);
}

$stmt = pdoquery($sql, $params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
	$barcode = $row['id'];
	$Datebookedin = $row['Datebookedin'];
	$barcodelist[$barcode] = $Datebookedin;
}
		
		
		
		
$sql = "SELECT viamed_customer_repairs_details.barcode, viamed_customer_repairs_details.warranty_new, Serialnumbers.Porref, Serialnumbers.Datebookedin FROM viamed_customer_repairs_details, Serialnumbers WHERE viamed_customer_repairs_details.barcode = Serialnumbers.id AND Serialnumbers.Stock_ref = ? AND viamed_customer_repairs_details.stock_ref = ? GROUP BY Serialnumbers.id";
$stmt = pdoquery($sql, array($searchstkref, $searchstkref));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
	$barcode = $row['barcode'];
	$Datebookedin = $row['Datebookedin'];
	$barcodelist[$barcode] = $Datebookedin;
	$barcodewarr[$barcode] = $row['warranty_new'];
}
		
if (!is_array($barcodelist)) {
    $barcodelist = array(); // Ensures it's always an array – PHP 5.6 & 8 compatible
}
foreach ($barcodelist as $barcode => $Datebookedin) {
	$Datebookedincountreturn[$Datebookedin]=isset($Datebookedincountreturn[$Datebookedin]) ? $Datebookedincountreturn[$Datebookedin] : 0;
	$Datebookedincountreturnwarr[$Datebookedin]=isset($Datebookedincountreturnwarr[$Datebookedin]) ? $Datebookedincountreturnwarr[$Datebookedin]: 0;
	$barcodewarr[$barcode]=isset($barcodewarr[$barcode]) ? $barcodewarr[$barcode]: '';
	if ($barcodewarr[$barcode]=='') {
		$Datebookedincountreturn[$Datebookedin]++;
	} else {
		$Datebookedincountreturnwarr[$Datebookedin]++;
	}
}

if (count($barcodelist) > 0) {
    $sql = "SELECT d.barcode, d.repair_header_id, s.Datebookedin
            FROM viamed_customer_repairs_details d
            JOIN Serialnumbers s ON d.barcode = s.id
            WHERE d.barcode IN (" . implode(',', array_fill(0, count($barcodelist), '?')) . ")
            AND d.repair_header_id > 0
            GROUP BY d.barcode, s.Datebookedin";
    $params = array_keys($barcodelist);
    $stmt = pdoquery($sql, $params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($rows as $row) {
        $Datebookedin = $row['Datebookedin'];
        $Datebookedincountsrs[$Datebookedin] = isset($Datebookedincountsrs[$Datebookedin]) ? $Datebookedincountsrs[$Datebookedin] + 1 : 1;
    }
}

if ($searchstkref !== '') {
	$sql = "SELECT COUNT(Datebookedin) AS cnt, Datebookedin FROM Serialnumbers WHERE Ordernumber = '' AND Invoicenumber = '' AND Stock_ref = ? AND Serialnumber NOT LIKE '%(r)%' AND Serialnumber <> '-' GROUP BY Datebookedin";
	$params = array($searchstkref);
} else {
	$sql = "SELECT COUNT(Datebookedin) AS cnt, Datebookedin FROM Serialnumbers WHERE Ordernumber = '' AND Invoicenumber = '' AND (Datebookedin LIKE ? OR Stock_ref LIKE ?) AND Serialnumber NOT LIKE '%(r)%' AND Serialnumber <> '-' GROUP BY Datebookedin";
	$params = array('%' . $search . '%', '%' . $search . '%');
}
$stmt = pdoquery($sql, $params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
	$datebookedin = $row['Datebookedin'];
	$notfullysold[$datebookedin] = $row['cnt'];
}


	
$fulloutput .= "<table class=CSSTable_n2 border=1><tr><td>Host</td><td>Barcode</td><Td>Stock Reference &nbsp &nbsp </td><td>Stock Description &nbsp</td><td>Quantity Added &nbsp &nbsp </td><td><a TITLE=\"Number Barcodes in Returns or Supplier Returns\">Customer Fails</a></td><td>Other / QA or Non Warranty Fail</td><td>SRS / SRN</td><td>Batchnumber</td><Td>DateCode</td><td>POR</td><td>Date</td><td>Advanced Reference &nbsp &nbsp </td><td>Booked in by</td></tr>";

if ($searchstkref !== '') {
	$sql = "SELECT COUNT(id) AS total, companyid, Stock_ref, Datebookedin, Porref, manufacture_date_code, Bookedinby, Ordernumber, batchnumber FROM Serialnumbers WHERE Serialnumber <> '-' AND Serialnumber NOT LIKE '%(r)%' AND Stock_ref = ? AND Ordernumber <> 'o' AND (companyid = '0' OR companyid = ?) GROUP BY Stock_ref, Datebookedin, Porref, batchnumber ORDER BY Datebookedin DESC";
	$params = array($searchstkref, $hostcompanyid);
} else {
	$sql = "SELECT COUNT(id) AS total, companyid, Stock_ref, Datebookedin, Porref, manufacture_date_code, Bookedinby, Ordernumber, batchnumber FROM Serialnumbers WHERE Serialnumber <> '-' AND Serialnumber NOT LIKE '%(r)%' AND (Datebookedin LIKE ? OR Stock_ref LIKE ?) AND Ordernumber <> 'o' AND (companyid = '0' OR companyid = ?) GROUP BY Stock_ref, Datebookedin, Porref, batchnumber ORDER BY Datebookedin DESC";
	$params = array('%' . $search . '%', '%' . $search . '%', $hostcompanyid);
}
$stmt = pdoquery($sql, $params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$num_rows3 = count($rows);
foreach ($rows as $row) {
	$stkref2 = $row['Stock_ref'];
	$Datebookedin = $row['Datebookedin'];
	$prndate = date("d/m/y", $row['Datebookedin']);
	$link = $row['Stock_ref'] . "axa" . $row['Datebookedin'];
	$md5outlink = md5($link);
	$hostid = $row['companyid'];
	if ($row['Ordernumber'] == 'o') {
		$fulloutput .= "<TR bgcolor=\"#994444\" $useurgent onMouseOver=\"this.bgColor='#d4deea';\" onMouseOut=\"this.bgColor='#994444';\">";
	} else {
		$fulloutput .= "<TR bgcolor=\"#92adc8\" $useurgent onMouseOver=\"this.bgColor='#d4deea';\" onMouseOut=\"this.bgColor='#92adc8';\">";
	}

	$fulloutput .= "<td>$global_company_logo24[$hostid]</td>";

	if ($text == '') {
		$fulloutput .= "<td><button onclick=\"getbarcodesadvref('$stkref2','" . $row['Porref'] . "','" . $row['Datebookedin'] . "','$hostid')\">Barcodes</button></td>";
	} else {
		$fulloutput .= "<td></td>";
	}
	$fulloutput .= "<Td>";

	$fulloutput .= $row['Stock_ref'] . "</td><td>$lbldesc[$stkref2]</td><Td>" . $row['total'] . "</td>";
	$Datebookedincountreturnwarr[$Datebookedin]=isset($Datebookedincountreturnwarr[$Datebookedin]) ? $Datebookedincountreturnwarr[$Datebookedin]: '';
	$Datebookedincountreturn[$Datebookedin] = isset( $Datebookedincountreturn[$Datebookedin] ) ? $Datebookedincountreturn[$Datebookedin] : '';
	$Datebookedincountsrs[$Datebookedin] = isset($Datebookedincountsrs[$Datebookedin]) ? $Datebookedincountsrs[$Datebookedin] : '';
	if ($Datebookedincountreturnwarr[$Datebookedin] > 0) {
		$fulloutput .= "<td><button onclick=\"get_warranty_failed_barcodes('" . $stkref2 . "','" . $Datebookedin . "','" . $hostcompanyid . "')\" title='Click to view warranty repair barcodes'>" . $Datebookedincountreturnwarr[$Datebookedin] . " Fails</button></td>";
	} else {
		$fulloutput .= "<td></td>";
	}
	if ($Datebookedincountreturn[$Datebookedin] > 0) {
		$fulloutput .= "<td><button onclick=\"get_qa_failed_barcodes('" . $stkref2 . "','" . $Datebookedin . "','" . $hostcompanyid . "')\" title='Click to view QA / non-warranty failed barcodes'>" . $Datebookedincountreturn[$Datebookedin] . " Fails</button></td>";
	} else {
		$fulloutput .= "<td></td>";
	}
	if ($Datebookedincountsrs[$Datebookedin] > 0) {
		$fulloutput .= "<td><button onclick=\"get_srs_barcodes('" . $stkref2 . "','" . $Datebookedin . "','" . $hostcompanyid . "')\" title='Click to view SRS/SRN barcodes'>" . $Datebookedincountsrs[$Datebookedin] . " SRS</button></td>";
	} else {
		$fulloutput .= "<td></td>";
	}

	$fulloutput .= "<td>" . $row['batchnumber'] . " ";
	

	
	if ($text == '') {
		//print "<button onclick=\"tracebatch('$stkref2','" . $row['batchnumber'] . "','" . $row['Datebookedin'] . "','$hostid')\">Trace</button>";
	}
	if ($text == '') {
		$fulloutput .= "<button onclick=\"tracebatchnewtab('$stkref2','" . $row['batchnumber'] . "','" . $row['Datebookedin'] . "','$hostid')\">Trace</button>";
	}
	$fulloutput .= "</td><td>";
	$link = $row['Stock_ref'] . "AXA" . $row['total'];
	$md5link = md5($link);


	$fulloutput .= $row['manufacture_date_code'] . "</a></td><td>";
	if ($text == '') {
		$fulloutput .= linkpurchaseorder($x, $row['Porref'], '', 24);
	} else {
		$fulloutput .= "";
	}
	$fulloutput .= $row['Porref'] . "</td><td>$prndate</td><td>";

	$fulloutput .= $row['Datebookedin'] . " ";
	$istrained[$stkref2]  = isset( $istrained[$stkref2]  ) ? $istrained[$stkref2]  : '';
	if ($istrained[$stkref2] > 0 || $vui == 2 || $vui == 1) {
		$link = "$baseurl/intranet/databases/serial_extra/viewbatchqa.php$x&aref=" . $row['Datebookedin'] . "&stkref=$stkref2";
		$fulloutput .= "<a href=\"$link\"> &nbsp";
$notfullysold[$Datebookedin]= isset( $notfullysold[$Datebookedin]) ? $notfullysold[$Datebookedin] : '';
		if ($notfullysold[$Datebookedin] > 0) {
			$fulloutput .= "<br>$notfullysold[$Datebookedin] Left";
		} else {
			$fulloutput .= "<br>All Gone";
		}
		if ($text == '') {
			$fulloutput .= "<br><button>View QA</button></a>";
		} else {
			$fulloutput .= "";
		}
	}
	$fulloutput .= "</td><td>" . $row['Bookedinby'] . "</td><td><a href=\"/intranet/databases/stock_lables/lable_menu.php$x&stk=$stkref2&advref=" . $row['Datebookedin'] . "\"><button>Labels</button></a></td>";
	if ($localname=='Derek Lamb' and $text=='')
		{
		//$fulloutput .= "<a href=\"stock_list_recent_additions_removestock.php$x&aref=$row[Datebookedin]&stkref=$row[Stock_ref]\">Remove Batch</a>";
		}
	// 1425473266 < - remove batch
	
	$fulloutput .= "</tr>";

}
$fulloutput .=  "</table></centeR>";


$groupreport = isset( $groupreport ) ? $groupreport  : '';
if ($groupreport == '') {
	print "$fulloutput";
}

// Checked for PHP5.6 / 8.x compatibility

?>