static function getLevels($locationId)
{
$pdo = PDOBuilder::getPDO();
$lvls = array();
/* Start from LOCATIONS table to return a line with null everywhere
* if there is no stocklevel and no line at all
* if the location does not exist. */
// Get security and max levels
$sqlLvl = "SELECT STOCKLEVEL.ID, PRODUCT, STOCKSECURITY, STOCKMAXIMUM " . "FROM LOCATIONS " . "LEFT JOIN STOCKLEVEL ON STOCKLEVEL.LOCATION = LOCATIONS.ID " . "WHERE LOCATIONS.ID = :loc";
$stmtLvl = $pdo->prepare($sqlLvl);
$stmtLvl->bindParam(":loc", $locationId);
$stmtLvl->execute();
$locationExists = false;
while ($row = $stmtLvl->fetch()) {
$locationExists = true;
if ($row['PRODUCT'] !== null) {
$lvls[$row['PRODUCT']] = array($row['ID'], $row['STOCKSECURITY'], $row['STOCKMAXIMUM']);
}
}
if (!$locationExists) {
return null;
}
// Get quantities
$qties = array();
$sqlQty = "SELECT PRODUCT, ATTRIBUTESETINSTANCE_ID AS ATTR, UNITS " . "FROM STOCKCURRENT " . "WHERE LOCATION = :loc";
$stmtQty = $pdo->prepare($sqlQty);
$stmtQty->bindParam(':loc', $locationId);
$stmtQty->execute();
while ($row = $stmtQty->fetch()) {
$prdId = $row['PRODUCT'];
if (!isset($qties[$prdId])) {
$qties[$prdId] = array();
}
$qties[$prdId][$row['ATTR']] = $row['UNITS'];
}
// Merge both ids
$prdIds = array();
foreach (array_keys($lvls) as $id) {
$prdIds[] = $id;
}
foreach (array_keys($qties) as $id) {
if (!in_array($id, $prdIds)) {
$prdIds[] = $id;
}
}
// Merge all data
$levels = array();
foreach ($prdIds as $id) {
$row = array("PRODUCT" => $id, "LOCATION" => $locationId);
if (isset($lvls[$id])) {
$row['ID'] = $lvls[$id][0];
$row['STOCKSECURITY'] = $lvls[$id][1];
$row['STOCKMAXIMUM'] = $lvls[$id][2];
} else {
$row['ID'] = null;
$row['STOCKSECURITY'] = null;
$row['STOCKMAXIMUM'] = null;
}
if (isset($qties[$id])) {
foreach ($qties[$id] as $attr => $qty) {
$row['ATTRIBUTESETINSTANCE_ID'] = $attr;
$row['UNITS'] = $qty;
$levels[] = StocksService::buildDBLevel($row);
}
} else {
$row['ATTRIBUTESETINSTANCE_ID'] = null;
$row['UNITS'] = null;
$levels[] = StocksService::buildDBLevel($row);
}
}
return $levels;
}
