public function GetRoomTotalForDay($room, $date = null) {
if(!isset($date)) {
$date = date("Y-m-d");
}
// This function is going to return the number of shoes processed that day
// First of all work out which scanner number is required for the room
$scanner = $this->GetScannerNumber($room);
// Next generate the SQL
$sql = "SELECT `scanners.KordNo`, `scanners.BundleNumber`
FROM `scanners`
WHERE `scanners.Date` = '" . $date . "'
AND `scanners.Scanner` IN (";
foreach($scanner as $x) {
$sql .= $x . ",";
}
$sql .= "0);";
// And query the database
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
$return[] = $row;
}
// It is more complicated for Kettering, Closing & Rushden, we need to filter the list
if(in_array($room, array(3,4,5))) {
foreach($return as $x) {
$sql = "SELECT `scanners.Scanner`
FROM `scanners`
WHERE `scanners.KordNo` = " . $x['scanners.KordNo'] . "
AND `scanners.BundleNumber` = " . $x['scanner.BundleNumber'] . "
ORDER BY `scanners.Date` DESC
LIMIT 1,1;";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);
// If scanner 7, it's been through bottom stock so need to find previous
if($row[0] == 7) {
$sql = "SELECT `scanners.Scanner`
FROM `scanners`
WHERE `scanners.KordNo` = " . $x['scanners.KordNo'] . "
AND `scanners.BundleNumber` = " . $x['scanners.BundleNumber'] . "
ORDER BY `scanners.Date` DESC
LIMIT 2,1;";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);
}
if($row[0] == 10 && $room == 3) {
$finalReturn[] = $x;
} elseif($row[0] == 11 && $room == 4) {
$finalReturn[] = $x;
} elseif($row[0] == 15 && $room == 5) {
$finalReturn[] = $x;
}
}
$return = $finalReturn;
}
// Now we have a list of tickets, we need to query how many pairs are in each ticket
$total = 0;
foreach($return as $x) {
$sql = "SELECT `QtyIssued`
FROM `ArchiveBundle`
WHERE `ArchiveBundle.KordNo` = '" . $x['scanners.KordNo'] . "'
AND `ArchiveBundle.BundleNumber` = '" . $x['scanners.BundleNumber'] . "';";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);
$total += $row[0];
}
return $total;
}
I have edited the class above which pulls no results. However the original class below pulls results out. Please can someone help.
public function GetRoomTotalForDay($room, $date = null) {
if(!isset($date)) {
$date = date("Y-m-d");
}
// This function is going to return the number of shoes processed that day
// First of all work out which scanner number is required for the room
$scanner = $this->GetScannerNumber($room);
// Next generate the SQL
$sql = "SELECT `KordNo`, `BundleNumber`
FROM `scanners`
WHERE `Date` = '" . $date . "'
AND `Scanner` IN (";
foreach($scanner as $x) {
$sql .= $x . ",";
}
$sql .= "0);";
// And query the database
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
$return[] = $row;
}
// It is more complicated for Kettering, Closing & Rushden, we need to filter the list
if(in_array($room, array(3,4,5))) {
foreach($return as $x) {
$sql = "SELECT `Scanner`
FROM `scanners`
WHERE `KordNo` = " . $x['KordNo'] . "
AND `BundleNumber` = " . $x['BundleNumber'] . "
ORDER BY `Date` DESC
LIMIT 1,1;";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);
// If scanner 7, it's been through bottom stock so need to find previous
if($row[0] == 7) {
$sql = "SELECT `Scanner`
FROM `scanners`
WHERE `KordNo` = " . $x['KordNo'] . "
AND `BundleNumber` = " . $x['BundleNumber'] . "
ORDER BY `Date` DESC
LIMIT 2,1;";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);
}
if($row[0] == 10 && $room == 3) {
$finalReturn[] = $x;
} elseif($row[0] == 11 && $room == 4) {
$finalReturn[] = $x;
} elseif($row[0] == 15 && $room == 5) {
$finalReturn[] = $x;
}
}
$return = $finalReturn;
}
// Now we have a list of tickets, we need to query how many pairs are in each ticket
$total = 0;
foreach($return as $x) {
$sql = "SELECT `QtyIssued`
FROM `ArchiveBundle`
WHERE `KordNo` = '" . $x['KordNo'] . "'
AND `BundleNumber` = '" . $x['BundleNumber'] . "';";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);
$total += $row[0];
}
return $total;
}
The class above counts the amount of shoes produced. I have had to edit this class so it can exclude certain types of shoes but it does not seem to pull any results for some reason.
UPDATE.
This is the class scanners. This is what its currently at the moment. I'm fairly new to php and this code was writted by my predecessor.
<?php
class CHScanners {
var $conn;
// Constructor, connect to the database
public function __construct() {
require_once "/var/www/reporting/settings.php";
define("DAY", 86400);
if(!$this->conn = mysql_connect(DB_HOST, DB_USERNAME, DB_PASSWORD)) die(mysql_error());
if(!mysql_select_db(DB_DATABASE_NAME, $this->conn)) die(mysql_error());
}
public function ListRoomBundles($room, $date, $dateTo = null) {
// If dateTo hasn't been set, make it now
if(!isset($dateTo) or $dateTo == "") {
$dateTo = $date;
}
// Return an array with each bundle number and the quantity for each day
$scanner = $this->GetScannerNumber($room);
$sql = "SELECT * FROM `scanners` WHERE `Scanner` IN (";
foreach($scanner as $x) {
$sql .= $x . ",";
}
$sql .= "0)
AND `Date` BETWEEN '" . $date . "' AND '" . $dateTo . "'
GROUP BY `KordNo`, `BundleNumber`;";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
$sql = "SELECT `BundleReference`, `QtyIssued`, `WorksOrder`
FROM `ArchiveBundle`
WHERE `KordNo` = '" . $row['KordNo'] . "'
AND `BundleNumber` = '" . $row['BundleNumber'] . "';";
$result2 = mysql_query($sql);
while($row = mysql_fetch_array($result2)) {
if($row[0] != "") {
$final[] = $row;
} else {
$final[] = array("Can't find bundle number", "N/A");
}
}
}
return $final;
}
public function GetRoomTotalForDay($room, $date = null) {
if(!isset($date)) {
$date = date("Y-m-d");
}
// This function is going to return the number of shoes processed that day
// First of all work out which scanner number is required for the room
$scanner = $this->GetScannerNumber($room);
// Next generate the SQL
$sql = "SELECT `scanners.KordNo`, `scanners.BundleNumber`
FROM `scanners,TWOrder,Stock`
INNER JOIN TWORDER ON `scanners.KordNo` = `TWOrder.KOrdNo`
AND `scanners.Date` = '" . $date . "'
INNER JOIN Stock ON `TWOrder.Product` = `Stock.ProductCode`
AND `Stock.ProductGroup` NOT BETWEEN 400 AND 650
AND `scanners.Scanner` IN (
ORDER BY `scanners.KordNo' ASC";
foreach($scanner as $x) {
$sql .= $x . ",";
}
$sql .= "0);";
// And query the database
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
$return[] = $row;
}
// It is more complicated for Kettering, Closing & Rushden, we need to filter the list
if(in_array($room, array(3,4,5))) {
foreach($return as $x) {
$sql = "SELECT `scanners.Scanner`
FROM `scanners`
WHERE `scanners.KordNo` = " . $x['scanners.KordNo'] . "
AND `scanners.BundleNumber` = " . $x['scanners.BundleNumber'] . "
ORDER BY `scanners.Date` DESC
LIMIT 1,1;";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);
// If scanner 7, it's been through bottom stock so need to find previous
if($row[0] == 7) {
$sql = "SELECT `scanners.Scanner`
FROM `scanners`
WHERE `scanners.KordNo` = " . $x['scanners.KordNo'] . "
AND `scanners.BundleNumber` = " . $x['scanners.BundleNumber'] . "
ORDER BY `Date` DESC
LIMIT 2,1;";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);
}
if($row[0] == 10 && $room == 3) {
$finalReturn[] = $x;
} elseif($row[0] == 11 && $room == 4) {
$finalReturn[] = $x;
} elseif($row[0] == 15 && $room == 5) {
$finalReturn[] = $x;
}
}
$return = $finalReturn;
}
// Now we have a list of tickets, we need to query how many pairs are in each ticket
$total = 0;
foreach($return as $x) {
$sql = "SELECT `QtyIssued`
FROM `ArchiveBundle`
WHERE `KordNo` = '" . $x['scanners.KordNo'] . "'
AND `BundleNumber` = '" . $x['scanners.BundleNumber'] . "';";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);
$total += $row[0];
}
return $total;
}
// We need a function to select the previous Monday from a given date
public function GetPreviousMonday($timestamp) {
if(date("N", $timestamp) == 1) {
return $timestamp;
} elseif(in_array(date("N", $timestamp), array(2, 3, 4, 5))) {
return $timestamp - (date("N", $timestamp)-1)*DAY;
} elseif(in_array(date("N", $timestamp), array(6, 7))) {
return $timestamp + (date("N", $timestamp)*(-1)+8)*DAY;
} else {
return false;
}
}
public function GetRoomName($room) {
// Return the room name from the room number
switch($room) {
case 1:
return "Skin Room";
case 2:
return "Clicking Room";
case 3:
return "Kettering";
case 4:
return "Closing Room";
case 5:
return "Rushden";
case 6:
return "Assembly Room";
case 7:
return "Lasting Room";
case 8:
return "Making Room";
case 9:
return "Finishing Room";
case 10:
return "Shoe Room";
}
}
public function GetDueDateForWorksOrder($worksOrderNumber) {
$sql = "SELECT `DueDate`
FROM `TWOrder`
WHERE `WorksOrderNumber` = '" . $worksOrderNumber . "';";
mysql_select_db(DB_DATABASE_NAME, $this->conn);
$result = mysql_query($sql, $this->conn);
$row = mysql_fetch_row($result);
return $row[0];
}
private function GetScannerNumber($room) {
// Get the room number from the scanner number
switch($room) {
case 1:
$scanner = array(3);
break;
case 2:
$scanner = array(10,11,15);
break;
case 3:
$scanner = array(5);
break;
case 4:
$scanner = array(5);
break;
case 5:
$scanner = array(5);
break;
case 6:
$scanner = array(6);
break;
case 7:
$scanner = array(9);
break;
case 8:
$scanner = array(8);
break;
case 9:
$scanner = array(12);
break;
case 10:
$scanner = array(14);
break;
default:
$scanner = array(0);
break;
}
return $scanner;
}
}
?>
You have a typo - a letter is missing in the last line of this block of code:
if(in_array($room, array(3,4,5))) {
foreach($return as $x) {
$sql = "SELECT `scanners.Scanner`
FROM `scanners`
WHERE `scanners.KordNo` = " . $x['scanners.KordNo'] . "
AND `scanners.BundleNumber` = " . $x['scanner.BundleNumber'] .
Here the array item should be $x['scanners.BundleNumber'].