1771 lines
89 KiB
PHP
1771 lines
89 KiB
PHP
<?php
|
|
/*=======================================================================
|
|
*
|
|
* inc_stock.inc.php
|
|
*
|
|
* Autor: Marc Vollmann
|
|
*
|
|
*
|
|
=======================================================================*/
|
|
|
|
|
|
// Inserts a new stock
|
|
// Returns the generated stock ID or "0" if failed
|
|
// Take care global parameter $empIdRootAdmin is set for setting admin parameters in table "parameter"
|
|
function insertStock ($stkName, $stkPreId = "0", $stkObjType = "", $stkObjId = "", $stkBarcode = "", $adId = "0",$csId = "", $hqId = "", $stkVisible = "1", $stkReadonly = "0", $dbConnection = "") {
|
|
global $db, $PHP_SELF;
|
|
global $hq_id, $usr_id, $emp_id, $empIdRootAdmin;
|
|
$retValue = "0";
|
|
$stkName = trim($stkName);
|
|
if ($stkName != "") :
|
|
if (!is_object($dbConnection) || $dbConnection == "") : $dbConnection = $db; endif;
|
|
$stkPath = "";
|
|
if ($stkPreId != "" && is_numeric($stkPreId) && $stkPreId > "0") :
|
|
$stkPath = getFieldValueFromId("stock", "stk_id", $stkPreId, "stk_path",$dbConnection);
|
|
if ($stkPath == ""): $stkPath = "//"; endif;
|
|
$stkPath = $stkPath . $stkPreId . "//";
|
|
else :
|
|
$stkPreId = "0";
|
|
endif;
|
|
if ($adId == "" || !is_numeric($adId)) :
|
|
$adId = "0";
|
|
endif;
|
|
if ($csId == "" || !is_numeric($csId)) :
|
|
$csId = "0";
|
|
endif;
|
|
if ($hqId == "" || !is_numeric($hqId) || $hqId == "0") :
|
|
$hqId = $hq_id;
|
|
endif;
|
|
if ($stkObjId == "" || !is_numeric($stkObjId) || $stkObjId == "0") :
|
|
// $stkObjType = "";
|
|
$stkObjId = "0";
|
|
endif;
|
|
$stkBarcode = trim($stkBarcode);
|
|
if ($stkBarcode == "") :
|
|
$stkBarcode = $stkName;
|
|
endif;
|
|
if ($stkVisible == "" || !is_numeric($stkVisible)) :
|
|
$stkVisible = "1";
|
|
endif;
|
|
if ($stkReadonly == "" || !is_numeric($stkReadonly)) :
|
|
$stkReadonly = "0";
|
|
endif;
|
|
|
|
// Check for double stock names. They have to be unique for each customer and headquarters
|
|
// if (!existsEntry("stock",array("hq_id",$hqId,"cs_id",$customerId,"stk_name",$stkName),$dbConnection)) :
|
|
|
|
TA("B");
|
|
|
|
$currentTime = getDateTime("0");
|
|
|
|
// The new path is the concatenation of the path of the current stock and id of the current stock
|
|
insertStmt("stock",array("hq_id",$hqId,"stk_pre_id",$stkPreId,"stk_obj_type",$stkObjType,"stk_obj_id",$stkObjId,"stk_name",$stkName,"stk_path",$stkPath,"cs_id",$csId,"stk_visible",$stkVisible,"stk_readonly",$stkReadonly,"stk_barcode",$stkBarcode,"ad_id",$adId,"stk_modifytime",$currentTime), $dbConnection);
|
|
$stkIdNew = getLastInsertId($dbConnection);
|
|
|
|
// Only execute if new stock is a root stock
|
|
if ($stkPreId == "0") :
|
|
|
|
// Extend inividual parameter of the current employee (only not being admin)
|
|
// $empIdRootAdmin = getEmpIdOfRootAdmin($userTypeName); // Done before ...!!!
|
|
if ($empIdRootAdmin != "" && $emp_id != "" && $empIdRootAdmin != $emp_id) :
|
|
$stkRootAccessIDsEmpId = getParameterValue($emp_id, "MASK_STK_ROOT_ACCESS");
|
|
$stkRootAccessIDsEmpId .= "," . $stkIdNew;
|
|
updateStmt("parameter","emp_id",$emp_id,array("par_value",$stkRootAccessIDsEmpId),"par_key = 'MASK_STK_ROOT_ACCESS' AND hq_id = '". $hq_id . "'");
|
|
endif;
|
|
|
|
// Generate a new parameter for the headlines of the extra data fields
|
|
insertStmt("parameter",array("emp_id","0","hq_id","0","par_key","MASK_STK_DATAFIELDHEADLINES_" . $stkIdNew,"par_value","01,02,03,04,05,06,07,08,09,10,11,12,13,14,15"));
|
|
|
|
// Generate a new parameter for the extra fields have to be displayed WITHOUT existing serial number
|
|
insertStmt("parameter",array("emp_id","0","hq_id","0","par_key","MASK_STK_DATAFIELDS_" . $stkIdNew,"par_value","0"));
|
|
endif;
|
|
$retValue = $stkIdNew;
|
|
|
|
// Write logdata into log database
|
|
writeToLogDB("87",$hq_id,"",$usr_id,"","",$customerId,"HQ_ID=".$hqId."|STK_ID_NEW=".$stkIdNew."|STK_ID_PARENT=".$stkPreId."|STK_NAME_NEW=".$stkName."|STK_BARCODE_NEW=".$stkBarcode."|STK_OBJ_TYPE=".$stkObjType."|STK_OBJ_ID=".$stkObjId);
|
|
|
|
TA("C");
|
|
TA("E");
|
|
// else :
|
|
// $statusMessage = getLngt("Ein Lagerort mit demselben Namen") . " " . $stkName . " " . getLngt("existiert schon. Bitte umbenennen!");
|
|
// endif;
|
|
endif;
|
|
return $retValue;
|
|
}
|
|
|
|
// Removes a stock
|
|
// Returns the generated stock ID or "0" if failed
|
|
function deleteStock ($stkId, $dbConnection = "") {
|
|
global $db, $PHP_SELF;
|
|
global $hq_id, $usr_id, $emp_id;
|
|
$retValue = "";
|
|
if ($stkId != "" && is_numeric($stkId) && $stkId > "0") :
|
|
if (!is_object($dbConnection) || $dbConnection == "") : $dbConnection = $db; endif;
|
|
|
|
if (!existsEntry("stock",array("stk_pre_id",$stkId),$dbConnection)) :
|
|
|
|
// Check assigned articles to the stock to be deleted
|
|
if (!existsEntry("stockarticle",array("stk_id",$stkId),$dbConnection)) :
|
|
|
|
TA("B");
|
|
|
|
// Get the name of the stock (ONLY for logging!)
|
|
$stkNameToBeDeleted = getFieldValueFromId("stock","stk_id",$stkId,"stk_name");
|
|
$stkCsIdToBeDeleted = getFieldValueFromId("stock","stk_id",$stkId,"cs_id");
|
|
$stkObjTypeToBeDeleted = getFieldValueFromId("stock","stk_id",$stkId,"stk_obj_type");
|
|
$stkObjIdToBeDeleted = getFieldValueFromId("stock","stk_id",$stkId,"stk_obj_id");
|
|
|
|
deleteStmt("parameter","par_key = 'MASK_STK_DATAFIELDHEADLINES_" . $stkId . "'", $dbConnection);
|
|
deleteStmt("parameter","par_key = 'MASK_STK_DATAFIELDS_" . $stkId . "'", $dbConnection);
|
|
|
|
// Because of entries in "stockmove" (journal) the stock will be NOT removed but renamed and set to invisible
|
|
// deleteStmt("stock","stk_id = '" . $stkId . "'", $dbConnection);
|
|
updateStmt("stock","stk_id",$stkId,array("stk_name", "[DEL] " . $stkNameToBeDeleted,"stk_obj_type","","stk_obj_id","0","stk_visible","0","stk_readonly","1"));
|
|
|
|
TA("C");
|
|
TA("E");
|
|
|
|
$retValue = $stkNameToBeDeleted;
|
|
|
|
// Write logdata into log database
|
|
writeToLogDB("88",$hq_id,"",$usr_id,"","",$stkCsIdToBeDeleted,"HQ_ID=".$hq_id."|STK_ID=".$stkId."|STK_NAME=".$stkNameToBeDeleted."|STK_OBJ_TYPE=".$stkObjTypeToBeDeleted."|STK_OBJ_ID=".$stkObjIdToBeDeleted);
|
|
else:
|
|
$statusMessage = getLngt("Eintrag kann nicht entfernt werden, da mindestens ein Artikel darauf gebucht ist!");
|
|
endif;
|
|
else:
|
|
$statusMessage = getLngt("Eintrag kann nicht entfernt werden, da mindestens ein Unterlagerort existiert!");
|
|
endif;
|
|
endif;
|
|
return $retValue;
|
|
}
|
|
|
|
// Checks a stock having at least one stock article
|
|
function checkStockExistingStockarticle ($stkId, $dbConnection = "") {
|
|
global $db, $PHP_SELF;
|
|
$retBool = false;
|
|
if ($stkId != "" && is_numeric($stkId) && $stkId > "0") :
|
|
if (!is_object($dbConnection) || $dbConnection == "") : $dbConnection = $db; endif;
|
|
if (existsEntry("stockarticle",array("stk_id",$stkId),$dbConnection)) :
|
|
$retBool = true;
|
|
endif;
|
|
endif;
|
|
return $retBool;
|
|
}
|
|
|
|
// Inserts stocks for "service units" being individual objects with special types (vehicles, article items, ....)
|
|
// There could be defined a special stock in specialObjectStockIdParent being parent stock for all object stocks.
|
|
// Otherwise every object will be associated to a special root stock (!!!!)
|
|
function objectInsertStock ($stkObjType, $stkObjId, $specialObjectStockIdParent, $stkParentName, $stkName, $dbConnection = "") {
|
|
global $db, $PHP_SELF;
|
|
$retValue = "0";
|
|
|
|
$stkObjType = trim($stkObjType);
|
|
if ($stkObjType != "" && $stkObjId != "" && is_numeric($stkObjId) && $stkObjId > 0) :
|
|
if (!is_object($dbConnection) || $dbConnection == "") : $dbConnection = $db; endif;
|
|
|
|
if ($specialObjectStockIdParent == "" || !is_numeric($specialObjectStockIdParent)) :
|
|
$specialObjectStockIdParent = "0";
|
|
endif;
|
|
$stkParentName = trim($stkParentName);
|
|
if ($stkParentName == "") :
|
|
$stkParentName = "SYSTEM-OBJECT-ROOT";
|
|
endif;
|
|
$stkName = trim($stkName);
|
|
if ($stkName == "") :
|
|
$stkName = $stkObjType . "_" . $stkObjId;
|
|
endif;
|
|
|
|
// Check existence of a PARENT or ROOT stock
|
|
if ($specialObjectStockIdParent == "0") :
|
|
// Check existence of vehicle ROOT stock
|
|
if (!existsEntry("stock",array("stk_pre_id","0","stk_obj_type",$stkObjType,"stk_obj_id","0","stk_name",$stkParentName))) :
|
|
$objectStkIdParent = insertStock($stkParentName, "0", $stkObjType, "0", $stkParentName, "0", "0", $hq_id, "1", "1",$dbConnection);
|
|
else :
|
|
$objectStkIdParent = getFieldValueFromClause("stock","stk_id","stk_name = '" . $stkParentName . "' AND stk_pre_id = '0' AND stk_obj_type = '" . $stkObjType . "' AND stk_obj_id = '0'",$dbConnection);
|
|
endif;
|
|
else :
|
|
// Check existence of vehicle PARENT stock defined by parameter
|
|
$objectStkIdParent = getFieldValueFromClause("stock","stk_id","stk_id = '" . $specialObjectStockIdParent . "'",$dbConnection);
|
|
endif;
|
|
|
|
if ($objectStkIdParent != "" && is_numeric($objectStkIdParent) && $objectStkIdParent > 0) :
|
|
if (!existsEntry("stock",array("stk_obj_type",$stkObjType,"stk_obj_id",$stkObjId),$dbConnection)) :
|
|
// Insert new vehicle stock with parent $objectStkIdParent
|
|
$opInsStk = insertStock($stkName, $objectStkIdParent, $stkObjType, $stkObjId, $stkName, "", "", "", "1", "",$dbConnection);
|
|
if ($opInsStk == "" || $opInsStk == "0"):
|
|
$retValue = "-3";
|
|
// $statusMessage = getLngt("ACHTUNG! Die Anlage des Lagerortes konnte nicht durchgeführt werden! Bitte wiederholen bzw. den Administrator kontaktieren!");
|
|
else :
|
|
$retValue = $opInsStk;
|
|
endif;
|
|
endif;
|
|
else :
|
|
$retValue = "-2";
|
|
// $statusMessage = getLngt("ACHTUNG! Es existiert kein notwendiges fahrzeugbezogenes Hauptlager! Bitte den Administrator kontaktieren!");
|
|
endif;
|
|
else :
|
|
$retValue = "-1";
|
|
// $statusMessage = getLngt("Es wurde kein spezielles Objekt bzw. dessen Typ spezifiziert!");
|
|
endif;
|
|
|
|
return $retValue;
|
|
}
|
|
|
|
// Get all children stocks of the requestet stock (next sublevel)
|
|
// $stkId : Stock id to be requested
|
|
function getStockChildren($stkId, $withIdentity = "") {
|
|
global $db, $PHP_SELF;
|
|
$retArray = array();
|
|
if ($stkId != "" && is_numeric($stkId)) :
|
|
if ($withIdentity == "1") :
|
|
$tmpStkName = getFieldValueFromId("stock", "stk_id", $stkId, "stk_name");
|
|
$retArray[$stkId] = $tmpStkName;
|
|
endif;
|
|
$sqlStmt = "SELECT stk.stk_id, stk.stk_name FROM stock AS stk WHERE stk.stk_pre_id = '" . $stkId . "' ";
|
|
$result = $db->query($sqlStmt);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
while ($row = $result->fetch_assoc()):
|
|
$retArray[$row["stk_id"]] = $row["stk_name"];
|
|
endwhile;
|
|
$result->free();
|
|
endif;
|
|
return $retArray;
|
|
}
|
|
|
|
// Checks two stocks being child from the other
|
|
// $stkId : Stock id (parent)
|
|
// $stkIdChild : Stock id (child)
|
|
function isStkChild($stkId, $stkIdChild, $checkForIdentity = "") {
|
|
$retBool = false;
|
|
if ($stkId != "" && is_numeric($stkId) && $stkIdChild != "" && is_numeric($stkIdChild)) :
|
|
if ($checkForIdentity == "1") :
|
|
if ($stkId == $stkIdChild) :
|
|
$retBool = true;
|
|
endif;
|
|
endif;
|
|
if (!$retBool) :
|
|
$tmpStkChildPath = getFieldValueFromId("stock", "stk_id", $stkIdChild, "stk_path");
|
|
if (!(strpos($tmpStkChildPath, "//" . $stkId . "//") === FALSE)) :
|
|
$retBool = true;
|
|
endif;
|
|
endif;
|
|
endif;
|
|
return $retBool;
|
|
}
|
|
|
|
// Get the id of the root stock of the requested stock
|
|
// $stkId : Stock id to be requested
|
|
// $level : Level of the tree (default = "0" returns the ROOT id !!!)
|
|
function getStkPathId($stkId, $level = "0") {
|
|
global $db, $PHP_SELF;
|
|
$retVal = "0";
|
|
// Get path field of the requested stock
|
|
$tmpStkPath = getFieldValueFromId("stock", "stk_id", $stkId, "stk_path");
|
|
if ($tmpStkPath != "") :
|
|
// Remove path delimiter at the beginning and at the end
|
|
$tmpStkPath = substr($tmpStkPath, 2, -2);
|
|
// Split path to array of ids
|
|
$tmpPathArray = spliti("//", $tmpStkPath);
|
|
// Get the first element being the root stock id
|
|
$retVal = $tmpPathArray[$level];
|
|
else :
|
|
// The root of the requested stock is the stock itself
|
|
$retVal = $stkId;
|
|
endif;
|
|
return $retVal;
|
|
}
|
|
|
|
// Get the sum of the whole subtree of a specified numeric field
|
|
// $sumField : Name of the specified field to be accumulated
|
|
// $idFieldVal : Value of the id field
|
|
// $table : Name of the table containing the tree structure
|
|
// $pathField : Name of the field containing the path of nodes to the root item
|
|
// $whereClause : Special filter clause (e.g.: $whereClause = "AND csc_id = '"$costcenterId." '";)
|
|
function getFieldSumOfSubtree ($sumField, $idFieldVal, $table, $pathField, $whereClause) {
|
|
global $db, $PHP_SELF;
|
|
$retVal = -1;
|
|
if ($sumField != "" && $idFieldVal != "" && $table != "" && $pathField != "") :
|
|
if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;
|
|
$sqlStmt = "SELECT SUM(" . $sumField . ") AS result FROM " . $table . " WHERE " . $pathField . " LIKE '%//" . $idFieldVal . "//%' " . $whereClause;
|
|
$result = $db->query($sqlStmt);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
|
|
while ($row = $result->fetch_assoc()):
|
|
$retVal = $row["result"];
|
|
endwhile;
|
|
$result->free();
|
|
if ($retVal == "") : $retVal = 0; endif;
|
|
endif;
|
|
return $retVal;
|
|
}
|
|
|
|
// Get the sum of all items of the tree having the same parent
|
|
// $sumField : Name of the specified field to be accumulated
|
|
// $idField : Name of the id field
|
|
// $idFieldVal : Value of the id field
|
|
// $table : Name of the table containing the tree structure
|
|
// $idParentField : Name of the field containing the parent id
|
|
// $whereClause : Special filter clause (e.g.: $whereClause = "AND csc_id = '"$costcenterId." '";)
|
|
function getFieldSumOfSameLevelOfTree ($sumField, $idField, $idFieldVal, $table, $idParentField, $whereClause) {
|
|
global $db, $PHP_SELF;
|
|
$retVal = -1;
|
|
if ($sumField != "" && $idFieldVal != "" && $table != "" && $idParentField != "") :
|
|
// Get parent id
|
|
$idParentFieldVal = getFieldValueFromId($table, $idField, $idFieldVal, $idParentField);
|
|
|
|
if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;
|
|
$sqlStmt = "SELECT SUM(" . $sumField . ") AS result FROM " . $table . " WHERE " . $idParentField . " = '" . $idParentFieldVal . "' " . $whereClause;
|
|
$result = $db->query($sqlStmt);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
|
|
while ($row = $result->fetch_assoc()):
|
|
$retVal = $row["result"];
|
|
endwhile;
|
|
$result->free();
|
|
if ($retVal == "") : $retVal = 0; endif;
|
|
endif;
|
|
return $retVal;
|
|
}
|
|
|
|
// Get the sum of the next sublevel of the subtree of a specified numeric field
|
|
// $sumField : Name of the specified field to be accumulated
|
|
// $idFieldVal : Value of the id field
|
|
// $table : Name of the table containing the tree structure
|
|
// $parentField : Name of the field containing the parent id, here used to check the next sublevel of children
|
|
// $whereClause : Special filter clause (e.g.: $whereClause = "AND csc_id = '"$costcenterId." '";)
|
|
function getFieldSumOfNextSubLevelOfTree ($sumField, $idFieldVal, $table, $parentField, $whereClause) {
|
|
global $db, $PHP_SELF;
|
|
$retVal = -1;
|
|
if ($sumField != "" && $idFieldVal != "" && $table != "" && $parentField != "") :
|
|
if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;
|
|
$sqlStmt = "SELECT SUM(" . $sumField . ") AS result FROM " . $table . " WHERE " . $parentField . " = '" . $idFieldVal . "' " . $whereClause;
|
|
$result = $db->query($sqlStmt);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
|
|
while ($row = $result->fetch_assoc()):
|
|
$retVal = $row["result"];
|
|
endwhile;
|
|
$result->free();
|
|
if ($retVal == "") : $retVal = 0; endif;
|
|
endif;
|
|
return $retVal;
|
|
}
|
|
|
|
// ------------------------------------------------------------------------------------------------------
|
|
|
|
// Gets all stocks of a specified (root) stock (top down recursion)
|
|
// $stkId : Stock id
|
|
function getTreeStockData($stkId) {
|
|
global $stkIdTreeArray;
|
|
|
|
// Get stock children
|
|
$tmpArray = getStockChildren($stkId);
|
|
$keys = array_keys($tmpArray);
|
|
$keysLen = count($keys);
|
|
|
|
if ($keysLen > 0) :
|
|
// Iterate current children
|
|
for ($i = 0; $i < $keysLen; $i++) :
|
|
$tmpStkId = $keys[$i];
|
|
|
|
// Get data of the current stock
|
|
$stkIdTreeArray[$tmpStkId] = $tmpArray[$tmpStkId];
|
|
|
|
// Recursion ...
|
|
getTreeStockData($tmpStkId);
|
|
endfor;
|
|
endif;
|
|
}
|
|
|
|
// ------------------------------------------------------------------------------------------------------
|
|
|
|
// Checks sum of sub tree quantities smaller or equal than current stock item
|
|
// $stkId : Id of the stock
|
|
// $quantityToBeChecked : Ouantity to be checked
|
|
// $whereClause : Special filter clause (e.g.: $whereClause = "AND csc_id = '"$costcenterId." '";)
|
|
function checkStockQuantitySubtree ($stkId, $quantityToBeChecked = "", $whereClause = "") {
|
|
$retBool = false;
|
|
if ($stkId != "") :
|
|
if ($quantityToBeChecked == "") :
|
|
// Get max quantity of the current stock
|
|
$stkMaxquantity = getFieldValueFromId("stock", "stk_id", $stkId, "stk_maxquantity");
|
|
else :
|
|
$stkMaxquantity = $quantityToBeChecked;
|
|
endif;
|
|
|
|
if (!is_numeric($stkMaxquantity)) :
|
|
$stkMaxquantity = 0;
|
|
endif;
|
|
|
|
// Get max quantity of the whole subtree of the current stock
|
|
$stkSubtreeMaxQuantity = getFieldSumOfSubtree("stk_maxquantity", $stkId, "stock", "stk_path", $whereClause);
|
|
|
|
if ($stkSubtreeMaxQuantity <= $stkMaxquantity) :
|
|
$retBool = true;
|
|
endif;
|
|
endif;
|
|
return $retBool;
|
|
}
|
|
|
|
// Checks sum of all items being in the same tree level (including the current item) quantities smaller or equal than the parent stock item
|
|
// $stkId : Id of the stock
|
|
// $whereClause : Special filter clause (e.g.: $whereClause = "AND csc_id = '"$costcenterId." '";)
|
|
function checkStockQuantitySameLevel ($stkId, $quantityToBeChecked = "", $whereClause = "") {
|
|
$retBool = false;
|
|
if ($stkId != "") :
|
|
// Get parent id of the current stock
|
|
$idParentFieldVal = getFieldValueFromId("stock", "stk_id", $stkId, "stk_pre_id");
|
|
|
|
$whereClause = "";
|
|
if ($quantityToBeChecked == "") :
|
|
$quantityToBeChecked = 0;
|
|
// $quantityToBeChecked = getFieldValueFromId("stock", "stk_id", $stkId, "stk_maxquantity");
|
|
else :
|
|
// Get max quantity of the current stock
|
|
$whereClause = "stk_id != '" . $stkId . "' " . $whereClause;
|
|
endif;
|
|
|
|
if (!is_numeric($quantityToBeChecked)) :
|
|
$quantityToBeChecked = 0;
|
|
endif;
|
|
|
|
// Get max quantity of the parent stock
|
|
$stkMaxquantityParent = getFieldValueFromId("stock", "stk_id", $idParentFieldVal, "stk_maxquantity");
|
|
|
|
// Get max quantity of the whole subtree of the current stock
|
|
$stkSameLevelMaxQuantity = getFieldSumOfSameLevelOfTree("stk_maxquantity", "stk_id", $stkId, "stock", "stk_pre_id", $whereClause);
|
|
|
|
if (($stkSameLevelMaxQuantity + $quantityToBeChecked) <= $stkMaxquantityParent) :
|
|
$retBool = true;
|
|
endif;
|
|
endif;
|
|
return $retBool;
|
|
}
|
|
|
|
// Checks sum of sub tree quantities smaller or equal than current stock item
|
|
// $stkId : Id of the stock
|
|
// $whereClause : Special filter clause (e.g.: $whereClause = "AND csc_id = '"$costcenterId." '";)
|
|
function checkStockQuantityNextSubLevel ($stkId, $quantityToBeChecked = "", $whereClause = "") {
|
|
$retBool = false;
|
|
if ($stkId != "") :
|
|
if ($quantityToBeChecked == "") :
|
|
// Get max quantity of the current stock
|
|
$stkMaxquantity = getFieldValueFromId("stock", "stk_id", $stkId, "stk_maxquantity");
|
|
else :
|
|
$stkMaxquantity = $quantityToBeChecked;
|
|
endif;
|
|
|
|
if (!is_numeric($stkMaxquantity)) :
|
|
$stkMaxquantity = 0;
|
|
endif;
|
|
|
|
// Get max quantity of the whole subtree of the current stock
|
|
$stkNextSublevelMaxQuantity = getFieldSumOfNextSubLevelOfTree("stk_maxquantity", $stkId, "stock", "stk_pre_id", $whereClause);
|
|
|
|
if ($stkNextSublevelMaxQuantity <= $stkMaxquantity) :
|
|
$retBool = true;
|
|
endif;
|
|
endif;
|
|
return $retBool;
|
|
}
|
|
|
|
// Gets sum of stock ARTICLE quantities of the subtree
|
|
// $stkId : Id of the stock
|
|
// $whereClause : Special filter clause (e.g.: $whereClause = "AND csc_id = '"$costcenterId." '";)
|
|
function getStockArticleQuantitySubtree ($stkId, $treeMode = "0", $sumField = "", $whereClause = "") {
|
|
global $db, $PHP_SELF;
|
|
$retVal = "";
|
|
if ($stkId != "") :
|
|
if ($sumField == "") : $sumField = "stkat.stkat_quantity"; endif;
|
|
|
|
if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;
|
|
|
|
$whereClauseTreeMode = " (stk.stk_id = '" . $stkId . "' OR stk.stk_path LIKE '%//" . $stkId . "//%' ) ";
|
|
if ($treeMode == "1") :
|
|
$whereClauseTreeMode = " stk.stk_id = '" . $stkId . "' ";
|
|
elseif ($treeMode == "2") :
|
|
$whereClauseTreeMode = " stk.stk_path LIKE '%//" . $stkId . "//%' ";
|
|
endif;
|
|
|
|
$sqlStmt = "SELECT SUM(" . $sumField . ") AS result" .
|
|
" FROM stock AS stk, stockarticle AS stkat, article AS at" .
|
|
" WHERE stk.stk_id = stkat.stk_id AND stkat.at_id = at.at_id AND " . $whereClauseTreeMode . " " . $whereClause;
|
|
$result = $db->query($sqlStmt);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
|
|
while ($row = $result->fetch_assoc()):
|
|
$retVal = $row["result"];
|
|
endwhile;
|
|
$result->free();
|
|
if ($retVal == "") : $retVal = 0; endif;
|
|
endif;
|
|
return $retVal;
|
|
}
|
|
|
|
// Checks sum of sub tree stock ARTICLE quantities smaller or equal than current stock item quantity
|
|
// $stkId : Id of the stock
|
|
// $whereClause : Special filter clause (e.g.: $whereClause = "AND csc_id = '"$costcenterId." '";)
|
|
// E.g. the node is at the end of tree (it is a leaf) then it is a MUST to check for the quantity of the
|
|
// stock ARTICLES associated to this node, to avoid stock max. quantity value becomes smaller then the stock ARTICLE quantity value!!!
|
|
function checkStockArticleQuantitySubtree ($stkId, $quantityToBeChecked = "", $whereClause = "") {
|
|
$retBool = false;
|
|
if ($stkId != "") :
|
|
if ($quantityToBeChecked == "") :
|
|
// Get max quantity of the current stock
|
|
$quantityToBeChecked = getFieldValueFromId("stock", "stk_id", $stkId, "stk_maxquantity");
|
|
endif;
|
|
|
|
if (!is_numeric($quantityToBeChecked)) :
|
|
$quantityToBeChecked = 0;
|
|
endif;
|
|
|
|
// Get stock ARTICLE quantity of the whole subtree of the current stock
|
|
$stkatSubtreeQuantity = getStockArticleQuantitySubtree($stkId);
|
|
|
|
if ($stkatSubtreeQuantity <= $quantityToBeChecked) :
|
|
$retBool = true;
|
|
endif;
|
|
endif;
|
|
return $retBool;
|
|
}
|
|
|
|
// Get the sum of the quantity of a special article on any (root) stock (whole system)
|
|
// $atId : Article Id
|
|
// $whereClause : Special filter clause
|
|
function quantityOfSpecialStockArticleOnAnyStock ($atId, $whereClause = "") {
|
|
global $db, $PHP_SELF;
|
|
$retVal = -1;
|
|
if ($atId != "" && is_numeric($atId)) :
|
|
if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;
|
|
$sqlStmt = "SELECT SUM(stkat.stkat_itemquantity) AS result" .
|
|
" FROM stockarticle AS stkat" .
|
|
" WHERE stkat.at_id = '" . $atId . "'" . $whereClause;
|
|
$result = $db->query($sqlStmt);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
|
|
while ($row = $result->fetch_assoc()):
|
|
$retVal = $row["result"];
|
|
endwhile;
|
|
$result->free();
|
|
|
|
if ($retVal == "") : $retVal = 0; endif;
|
|
endif;
|
|
return $retVal;
|
|
}
|
|
|
|
// ------------------------------------------------------------------------------------------------------
|
|
|
|
function stockFilesOutputCSV($sqlStmt, $titleArray, $fieldArray, $aligns, $filename, $postParserField, $postParserFunction) {
|
|
global $db, $PHP_SELF;
|
|
global $f_stkat_export, $f_secretFileName, $statusMessage, $stkIdRoot;
|
|
|
|
// Remove old files
|
|
foreach (glob("../temp/download/*.stk.csv") as $fileNameToBoRemoved) {
|
|
if ((time() - filemtime($fileNameToBoRemoved)) > 60) :
|
|
if (file_exists($fileNameToBoRemoved)) :
|
|
unlink($fileNameToBoRemoved);
|
|
endif;
|
|
endif;
|
|
}
|
|
|
|
$f_secretFileName = "";
|
|
|
|
$result = $db->query($sqlStmt);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
|
|
// Define output parameters
|
|
$delimiter = ';';
|
|
$writeHeader = "YES";
|
|
$mode = "1";
|
|
if ($stkIdRoot != "" && $stkIdRoot > "0") :
|
|
$tmpStkIdRootOfStkIdFrom = getStkPathId($stkIdRoot);
|
|
$stkExportStkNamePrefixIfNum = getParameterValue("0", "STOCK_EXPORT_STK_NAME_PREFIX_IF_NUM_" . $tmpStkIdRootOfStkIdFrom, "0");
|
|
if ($stkExportStkNamePrefixIfNum != "") :
|
|
$fieldsToSetPrefix = array("stk_name",$stkExportStkNamePrefixIfNum);
|
|
endif;
|
|
endif;
|
|
|
|
include_once ("../include/inc_list_defineoutput2file.inc.php");
|
|
|
|
$outputLinesArrayLen = count($outputLinesArray);
|
|
if ($outputLinesArrayLen > 0) :
|
|
|
|
if ($filename == "") : $filename = "LAGER"; endif;
|
|
$f_fileName = $filename . "_" . date("YmdHis");
|
|
|
|
// $f_secretFileName = md5($f_fileName) . ".stk.csv";
|
|
$f_secretFileName = $f_fileName . ".stk.csv";
|
|
|
|
if ($outputHeader != "") :
|
|
writeToFile("../temp/download/" . $f_secretFileName, $outputHeader); // write header if requested
|
|
endif;
|
|
|
|
for ($i = 0; $i < $outputLinesArrayLen; $i++) :
|
|
// Post parsing if necessary
|
|
if ($postParserField != "") :
|
|
if ($postParserFunction == "postParseStockArticle") :
|
|
$outputLinesArray[$i] = postParseStockArticle($outputLinesArray[$i]);
|
|
elseif ($postParserFunction == "postParseArticleSum") :
|
|
$outputLinesArray[$i] = postParseArticleSum($outputLinesArray[$i]);
|
|
elseif ($postParserFunction == "postParseStockSum") :
|
|
$outputLinesArray[$i] = postParseStockSum($outputLinesArray[$i]);
|
|
elseif ($postParserFunction == "postParseStockJournal") :
|
|
$outputLinesArray[$i] = postParseStockJournal($outputLinesArray[$i]);
|
|
elseif ($postParserFunction == "postParseStockArticleItems") :
|
|
$outputLinesArray[$i] = postParseStockArticleItems($outputLinesArray[$i]);
|
|
endif;
|
|
endif;
|
|
|
|
writeToFile("../temp/download/" . $f_secretFileName, $outputLinesArray[$i]); // write statistic data
|
|
endfor;
|
|
else :
|
|
$statusMessage = "Es wurden keine Daten gefunden! Eine Datei wurde deshalb nicht generiert!";
|
|
$f_stkat_export = false;
|
|
endif;
|
|
}
|
|
|
|
function postParseStockArticle ($textToParse) {
|
|
global $f_stkat_export, $f_stkat_search_at_misc;
|
|
$textToParse = trim($textToParse);
|
|
|
|
if ($textToParse != "") :
|
|
|
|
// Iterate ALL occurrences of "<postparser>...</postparser>"
|
|
while (!(strpos($textToParse, "<postparser>") === FALSE)) {
|
|
|
|
$beginTagPosBegin = strpos($textToParse, "<postparser>");
|
|
$beginTagPosEnd = $beginTagPosBegin + 12;
|
|
$endTagPosBegin = strpos($textToParse, "</postparser>");
|
|
$endTagPosEnd = $endTagPosBegin + 13;
|
|
|
|
if ($beginTagPosEnd < $endTagPosBegin) :
|
|
$tagContent = substr($textToParse, $beginTagPosEnd, $endTagPosBegin - $beginTagPosEnd);
|
|
|
|
if ($tagContent != "") :
|
|
$tagContent = str_replace("|", "-,-", $tagContent);
|
|
$parseArray = spliti("-,-", $tagContent); // (at_id, at_eid, at_serialno, stk_id)
|
|
|
|
// Special treatment
|
|
if ($f_stkat_export == "1") :
|
|
$tagContent = $parseArray[1];
|
|
else :
|
|
if ($parseArray[2] == "1") :
|
|
$tagContent = "<a href=\"../stock/article_item.php?atId=" . $parseArray[0] . "&stkId=" . $parseArray[3] . "&searchField=" . $f_stkat_search_at_misc . "\" target=\"_blank\">" . $parseArray[1] . "</a>";
|
|
else :
|
|
$tagContent = "<a href=\"javascript:finishPageJournalAction('1', '', '" . $parseArray[3] . "', '" . $parseArray[0] . "', '', '', '0', '', '', '', '')\">" . $parseArray[1] . "</a>";
|
|
endif;
|
|
endif;
|
|
|
|
// Remove last "<br>"
|
|
// $tagContent = substr($tagContent, 0, -4);
|
|
endif;
|
|
|
|
// Substitute text fragment
|
|
$textToParse = substr_replace($textToParse, $tagContent, $beginTagPosBegin, $endTagPosEnd - $beginTagPosBegin);
|
|
endif;
|
|
}
|
|
endif;
|
|
return $textToParse;
|
|
}
|
|
|
|
// Displays all listed stock articles of a specified stock
|
|
// $stkId : Id of the stock
|
|
// $whereClause : Special filter clause (e.g.: $whereClause = "AND csc_id = '"$costcenterId." '";)
|
|
function showStockArticles ($stkId, $treeMode = "0", $whereClause = "", $orderByClause = "") {
|
|
global $db, $PHP_SELF;
|
|
global $f_stk_planning, $f_stkat_export, $numOfArticles, $numOfPlaces, $statusMessage;
|
|
$retOut = "";
|
|
if ($stkId != "") :
|
|
$sqlStmt = getStmtStockArticles ($stkId, $treeMode, $whereClause, $orderByClause);
|
|
$result = $db->query($sqlStmt);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
|
|
if ($f_stk_planning == "1") :
|
|
$titleArray = array(getLngt("Lagerort "), getLngt("Artikelnr. "), getLngt("Bezeichnung "), getLngt("Zusatz "), getLngt("Belegte Plätze "), getLngt("Stückzahl "), getLngt("Artikelverbund "));
|
|
$aligns = "l,l,l,l,r,r,l";
|
|
$fieldArray = array("stk_name", "at_x", "at_name", "at_match", "stkat_quantity", "stkat_itemquantity", "atg_name");
|
|
$postParserField = "at_x";
|
|
else :
|
|
$titleArray = array(getLngt("Lagerort "), getLngt("Artikelnr. "), getLngt("Bezeichnung "), getLngt("Zusatz "), getLngt("Stückzahl "), getLngt("Artikelverbund "));
|
|
$aligns = "l,l,l,l,r,l";
|
|
$fieldArray = array("stk_name", "at_x", "at_name", "at_match", "stkat_itemquantity", "atg_name");
|
|
$postParserField = "at_x";
|
|
endif;
|
|
$alignArray = spliti(",",$aligns);
|
|
$alignTitles = "left";
|
|
$widths = "100";
|
|
$widthArray = spliti(",",$widths);
|
|
$summationField = "stkat_quantity";
|
|
$mode = "1"; // Output from DB-RESULT
|
|
$javaScriptFunctionNameForSort = "finishPageSortStockArticle";
|
|
$sortDBField = "YES"; // Used in following include-file for sorting per column;
|
|
$fieldSortArray = array("YES","YES","YES","YES","YES","YES","YES");
|
|
include ("../include/inc_list_defineoutput.inc.php");
|
|
// Post parsing if necessary
|
|
if ($postParserField != "") :
|
|
$tableBody = postParseStockArticle($tableBody);
|
|
endif;
|
|
|
|
$retOut .= "<table>\n";
|
|
$retOut .= $tableHeader . $tableBody;
|
|
$retOut .= "</table>\n";
|
|
|
|
$result->free();
|
|
|
|
$numOfArticles = $rowCounter;
|
|
$numOfPlaces = $sumOfSummationField;
|
|
|
|
// Optional output to file
|
|
if ($f_stkat_export) :
|
|
stockFilesOutputCSV($sqlStmt, $titleArray, $fieldArray, $aligns, "LAGERBESTAND", $postParserField, "postParseStockArticle");
|
|
endif;
|
|
endif;
|
|
return $retOut;
|
|
}
|
|
|
|
function postParseArticleSum ($textToParse) {
|
|
global $f_stkat_export, $f_stkat_search_at_misc;
|
|
$textToParse = trim($textToParse);
|
|
|
|
if ($textToParse != "") :
|
|
|
|
// Iterate ALL occurrences of "<postparser>...</postparser>"
|
|
while (!(strpos($textToParse, "<postparser>") === FALSE)) {
|
|
|
|
$beginTagPosBegin = strpos($textToParse, "<postparser>");
|
|
$beginTagPosEnd = $beginTagPosBegin + 12;
|
|
$endTagPosBegin = strpos($textToParse, "</postparser>");
|
|
$endTagPosEnd = $endTagPosBegin + 13;
|
|
|
|
if ($beginTagPosEnd < $endTagPosBegin) :
|
|
$tagContent = substr($textToParse, $beginTagPosEnd, $endTagPosBegin - $beginTagPosEnd);
|
|
|
|
if ($tagContent != "") :
|
|
$tagContent = str_replace("|", "-,-", $tagContent);
|
|
$parseArray = spliti("-,-", $tagContent); // (at_id, at_eid, at_serialno, stk_id)
|
|
|
|
// Special treatment
|
|
if ($parseArray[2] == "1" && $f_stkat_export != "1") :
|
|
$tagContent = "<a href=\"../stock/article_item.php?atId=" . $parseArray[0] . "&stkId=0" . "&searchField=" . $f_stkat_search_at_misc . "\" target=\"_blank\">" . $parseArray[1] . "</a>";
|
|
else :
|
|
$tagContent = $parseArray[1];
|
|
endif;
|
|
|
|
// Remove last "<br>"
|
|
// $tagContent = substr($tagContent, 0, -4);
|
|
endif;
|
|
|
|
// Substitute text fragment
|
|
$textToParse = substr_replace($textToParse, $tagContent, $beginTagPosBegin, $endTagPosEnd - $beginTagPosBegin);
|
|
endif;
|
|
}
|
|
endif;
|
|
return $textToParse;
|
|
}
|
|
|
|
// Displays all listed articles of a specified stock (tree)
|
|
// $stkId : Id of the stock
|
|
// $whereClause : Special filter clause (e.g.: $whereClause = "AND csc_id = '"$costcenterId." '";)
|
|
function showArticleSum ($stkId, $whereClause = "", $orderByClause = "") {
|
|
global $db, $PHP_SELF;
|
|
global $f_stkat_export, $numOfStocks, $numOfPlaces, $statusMessage;
|
|
$retOut = "";
|
|
if ($stkId != "") :
|
|
$sqlStmt = getStmtArticleSum($stkId, $whereClause, $orderByClause);
|
|
$result = $db->query($sqlStmt);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
|
|
$titleArray = array(getLngt("Artikelnr. "),getLngt("Bezeichnung "), getLngt("Zusatz "), getLngt("Stückzahl "), getLngt("Artikelverbund "));
|
|
$aligns = "l,l,l,r,l";
|
|
$fieldArray = array("stkat_x", "at_name", "at_match", "itemquantity", "atg_name");
|
|
$postParserField = "stkat_x";
|
|
$alignArray = spliti(",",$aligns);
|
|
$alignTitles = "left";
|
|
$widths = "100";
|
|
$widthArray = spliti(",",$widths);
|
|
$summationField = "itemquantity";
|
|
$mode = "1"; // Output from DB-RESULT
|
|
$javaScriptFunctionNameForSort = "finishPageSortArticleSum";
|
|
$sortDBField = "YES"; // Used in following include-file for sorting per column;
|
|
$fieldSortArray = array("YES","YES","YES","YES","YES","YES");
|
|
include ("../include/inc_list_defineoutput.inc.php");
|
|
// Post parsing if necessary
|
|
if ($postParserField != "") :
|
|
$tableBody = postParseArticleSum($tableBody);
|
|
endif;
|
|
|
|
$retOut .= "<table>\n";
|
|
$retOut .= $tableHeader . $tableBody;
|
|
$retOut .= "</table>\n";
|
|
|
|
$result->free();
|
|
|
|
$numOfStocks = $rowCounter;
|
|
$numOfPlaces = $sumOfSummationField;
|
|
|
|
// Optional output to file
|
|
if ($f_stkat_export) :
|
|
stockFilesOutputCSV($sqlStmt, $titleArray, $fieldArray, $aligns, "LAGERBESTAND", $postParserField, "postParseArticleSum");
|
|
endif;
|
|
endif;
|
|
return $retOut;
|
|
}
|
|
|
|
function postParseStockSum ($textToParse) {
|
|
global $f_stkat_export;
|
|
$textToParse = trim($textToParse);
|
|
|
|
if ($textToParse != "") :
|
|
|
|
// Iterate ALL occurrences of "<postparser>...</postparser>"
|
|
while (!(strpos($textToParse, "<postparser>") === FALSE)) {
|
|
|
|
$beginTagPosBegin = strpos($textToParse, "<postparser>");
|
|
$beginTagPosEnd = $beginTagPosBegin + 12;
|
|
$endTagPosBegin = strpos($textToParse, "</postparser>");
|
|
$endTagPosEnd = $endTagPosBegin + 13;
|
|
|
|
if ($beginTagPosEnd < $endTagPosBegin) :
|
|
$tagContent = substr($textToParse, $beginTagPosEnd, $endTagPosBegin - $beginTagPosEnd);
|
|
|
|
if ($tagContent != "") :
|
|
// $tagContent contains the stock id
|
|
$stkId = $tagContent;
|
|
|
|
// Get max quantity of the stock
|
|
$stkMaxquantity = getFieldValueFromId("stock", "stk_id", $stkId, "stk_maxquantity");
|
|
|
|
// Get quantity of places occupied by articles
|
|
$articlePlaceQuantityOfCurrentStock = getStockArticleQuantitySubtree($stkId, "1", "", "");
|
|
|
|
// Get max quantity of the whole subtree of the current stock
|
|
$stkNextSublevelMaxQuantity = getFieldSumOfNextSubLevelOfTree("stk_maxquantity", $stkId, "stock", "stk_pre_id", "");
|
|
|
|
// Compute the value for free stocks
|
|
$stkFreeAreas = ($stkMaxquantity - $articlePlaceQuantityOfCurrentStock - $stkNextSublevelMaxQuantity);
|
|
|
|
// Special treatment
|
|
$tagContent = $stkFreeAreas . " = " . $stkMaxquantity . " - " . "(" . $articlePlaceQuantityOfCurrentStock . " + " . $stkNextSublevelMaxQuantity . ")";
|
|
|
|
// Remove last "<br>"
|
|
// $tagContent = substr($tagContent, 0, -4);
|
|
endif;
|
|
|
|
// Substitute text fragment
|
|
$textToParse = substr_replace($textToParse, $tagContent, $beginTagPosBegin, $endTagPosEnd - $beginTagPosBegin);
|
|
endif;
|
|
}
|
|
endif;
|
|
return $textToParse;
|
|
}
|
|
|
|
|
|
// Displays all listed stocks with sum of articles
|
|
// $stkId : Id of the stock
|
|
// $whereClause : Special filter clause (e.g.: $whereClause = "AND csc_id = '"$costcenterId." '";)
|
|
function showStockSum ($stkId, $whereClause = "") {
|
|
global $db, $PHP_SELF;
|
|
global $md_id, $hq_id, $f_stkat_export, $numOfStocks, $numOfPlaces, $statusMessage;
|
|
$retOut = "";
|
|
if ($stkId != "") :
|
|
if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;
|
|
$sqlStmt = "SELECT stk.stk_id, stk.stk_name, SUM(stkat.stkat_maxquantity) AS maxquantity, SUM(stkat.stkat_quantity) AS quantity, stk.stk_id AS stk_x" .
|
|
" FROM stock AS stk, stockarticle AS stkat" .
|
|
" LEFT JOIN article AS at ON stkat.at_id = at.at_id" .
|
|
" LEFT JOIN articlegroupitem AS atgi ON stkat.at_id = atgi.at_id AND atgi.md_id = '" . $md_id . "' AND atgi.hq_id = '" . $hq_id . "'" .
|
|
" WHERE stk.stk_id = stkat.stk_id AND (stk.stk_id = '" . $stkId . "' OR stk.stk_path LIKE '%//" . $stkId . "//%' ) " . $whereClause .
|
|
" GROUP BY stk.stk_name" .
|
|
" ORDER BY stk.stk_path";
|
|
|
|
$result = $db->query($sqlStmt);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
|
|
$titleArray = array(getLngt("Lagerort "), getLngt("Belegte Plätze "), getLngt("Frei Plätze = Gesamt - (Belegt + Reserviert)"));
|
|
$aligns = "l,r,r";
|
|
$fieldArray = array("stk_name", "quantity", "stk_x");
|
|
$postParserField = "stk_x";
|
|
$alignArray = spliti(",",$aligns);
|
|
$alignTitles = "left";
|
|
$widths = "100";
|
|
$widthArray = spliti(",",$widths);
|
|
$summationField = "quantity";
|
|
$mode = "1"; // Output from DB-RESULT
|
|
$sortDBField = ""; // Used in following include-file for sorting per column;
|
|
include ("../include/inc_list_defineoutput.inc.php");
|
|
// Post parsing if necessary
|
|
if ($postParserField != "") :
|
|
$tableBody = postParseStockSum($tableBody);
|
|
endif;
|
|
|
|
$retOut .= "<table>\n";
|
|
$retOut .= $tableHeader . $tableBody;
|
|
$retOut .= "</table>\n";
|
|
|
|
$result->free();
|
|
|
|
$numOfStocks = $rowCounter;
|
|
$numOfPlaces = $sumOfSummationField;
|
|
|
|
// Optional output to file
|
|
if ($f_stkat_export) :
|
|
stockFilesOutputCSV($sqlStmt, $titleArray, $fieldArray, $aligns, "LAGERBESTAND", $postParserField, "postParseStockSum");
|
|
endif;
|
|
endif;
|
|
return $retOut;
|
|
}
|
|
|
|
// Displays inventory list
|
|
// $stkId : Id of the stock
|
|
// $whereClause : Special filter clause (e.g.: $whereClause = "AND csc_id = '"$costcenterId." '";)
|
|
function showStockInventory ($stkId, $treeMode = "0", $whereClause = "", $orderByClause = "") {
|
|
global $db, $PHP_SELF;
|
|
global $f_stk_planning, $f_stkat_export, $numOfArticles, $numOfPlaces, $statusMessage;
|
|
$retOut = "";
|
|
if ($stkId != "") :
|
|
$sqlStmt = getStmtStockInventory ($stkId, $treeMode, $whereClause, $orderByClause);
|
|
$result = $db->query($sqlStmt);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
|
|
$titleArray = array(getLngt("Lagerort "), getLngt("Artikelnr. "), getLngt("Bezeichnung "), getLngt("Zusatz "), getLngt("Stückzahl "), getLngt("Seriennr. "), getLngt("Artikelverbund "),
|
|
getLngt("01 "), getLngt("02 "), getLngt("03 "), getLngt("04 "), getLngt("05 "),
|
|
getLngt("06 "), getLngt("07 "), getLngt("08 "), getLngt("09 "), getLngt("10 "),
|
|
getLngt("11 "), getLngt("12 "), getLngt("13 "), getLngt("14 "), getLngt("15 "));
|
|
$aligns = "l,l,l,l,r,l,l,l,l,l,l,l,l,l,l,l,l,l,l,l,l,l";
|
|
$fieldArray = array("stk_name", "at_eid", "at_name", "at_match", "stkat_itemquantity", "ati_serialno", "atg_name",
|
|
"ati_data_01", "ati_data_02", "ati_data_03", "ati_data_04", "ati_data_05",
|
|
"ati_data_06", "ati_data_07", "ati_data_08", "ati_data_09", "ati_data_10",
|
|
"ati_data_11", "ati_data_12", "ati_data_13", "ati_data_14", "ati_data_15",
|
|
"ati_data_16", "ati_data_17", "ati_data_18", "ati_data_19", "ati_data_20",
|
|
"ati_data_21", "ati_data_22", "ati_data_23", "ati_data_24", "ati_data_25");
|
|
$postParserField = "";
|
|
|
|
$alignArray = spliti(",",$aligns);
|
|
$alignTitles = "left";
|
|
$widths = "100";
|
|
$widthArray = spliti(",",$widths);
|
|
$summationField = "stkat_quantity";
|
|
$mode = "1"; // Output from DB-RESULT
|
|
$javaScriptFunctionNameForSort = "finishPageSortInventory";
|
|
$sortDBField = "YES"; // Used in following include-file for sorting per column;
|
|
$fieldSortArray = array("YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES");
|
|
include ("../include/inc_list_defineoutput.inc.php");
|
|
// Post parsing if necessary
|
|
if ($postParserField != "") :
|
|
$tableBody = postParseStockArticle($tableBody);
|
|
endif;
|
|
|
|
$retOut .= "<table>\n";
|
|
$retOut .= $tableHeader . $tableBody;
|
|
$retOut .= "</table>\n";
|
|
|
|
$result->free();
|
|
|
|
$numOfArticles = $rowCounter;
|
|
$numOfPlaces = $sumOfSummationField;
|
|
|
|
// Optional output to file
|
|
if ($f_stkat_export) :
|
|
stockFilesOutputCSV($sqlStmt, $titleArray, $fieldArray, $aligns, "LAGERBESTAND", $postParserField, "postParseInventory");
|
|
endif;
|
|
endif;
|
|
return $retOut;
|
|
}
|
|
|
|
// ------------------------------------------------------------------------------------------------------
|
|
|
|
// Get the free places of special stock according the article-stock-factor of the requested article
|
|
function getFreeStkQuantity ($stkId, $atId, $itemQuantity, $mode = "") {
|
|
global $db, $PHP_SELF;
|
|
|
|
$retBool = false;
|
|
$stkQuantityFree = 0;
|
|
$stkItemQuantityFree = 0;
|
|
|
|
if ($stkId != "" && is_numeric($stkId) && $atId != "" && is_numeric($atId) && is_numeric($itemQuantity)) :
|
|
|
|
// *** Get article data ***
|
|
// Number of articles matching number of stock areas
|
|
$atStkItemQuantity = getFieldValueFromId("article", "at_id", $atId, "at_stk_itemquantity");
|
|
$atStkAreaQuantity = getFieldValueFromId("article", "at_id", $atId, "at_stk_areaquantity");
|
|
|
|
// AREA QUANTITIES of the current stock
|
|
// Get the max area quantity of the stock
|
|
$stkMaxquantity = getFieldValueFromId("stock", "stk_id", $stkId, "stk_maxquantity");
|
|
// Get quantity of areas occupied by articles
|
|
$articlePlaceQuantityOfCurrentStock = getStockArticleQuantitySubtree($stkId, "1", "", "");
|
|
// Get max area quantity of the whole subtree of the current stock (reservations)
|
|
$stkNextSublevelMaxQuantity = getFieldSumOfNextSubLevelOfTree("stk_maxquantity", $stkId, "stock", "stk_pre_id", "");
|
|
// Compute free places of the target stock
|
|
$stkQuantityFree = ($stkMaxquantity - $articlePlaceQuantityOfCurrentStock - $stkNextSublevelMaxQuantity);
|
|
|
|
// Convert the free area quantity to the quantity or free items according to the specified article
|
|
$stkItemQuantityFree = ceil(($stkQuantityFree * $atStkItemQuantity) / $atStkAreaQuantity);
|
|
|
|
if ($stkItemQuantityFree >= $itemQuantity) :
|
|
$retBool = true;
|
|
endif;
|
|
endif;
|
|
|
|
if ($mode == "0") :
|
|
return $retBool;
|
|
elseif ($mode == "1") :
|
|
return $stkQuantityFree;
|
|
elseif ($mode == "2") :
|
|
return $stkItemQuantityFree;
|
|
else :
|
|
return array($stkQuantityFree, $stkItemQuantityFree);
|
|
endif;
|
|
}
|
|
|
|
// Gets the substocks to have access to
|
|
// Returned values according to $retMode:
|
|
// [= "0"] : Plain array of all IDs of substocks of all listed root stocks [e.g.: "1,12,15,23|16,27,32,48|20,25,36,49,64" => (12,15,23,27,32,48,25,36,49,64)]
|
|
// [= "1"] : WHERE clause with OR logic for stocks
|
|
function getAccessibleSubstocks ($retMode = "0", $empId = "") {
|
|
global $hq_id, $emp_id, $userTypeName;
|
|
$retVal = "";
|
|
if ($retMode == "0" || $retMode == "1") : $retVal = array(); endif;
|
|
if ($empId == "") : $empId = $emp_id; endif;
|
|
$parMaskStkSubstockEmpIdAccess = getParameterValue($empId, "MASK_STK_SUBSTOCK_ACCESS");
|
|
if ($parMaskStkSubstockEmpIdAccess != "") :
|
|
$parMaskStkSubstockEmpIdAccess = str_replace("|", "--", $parMaskStkSubstockEmpIdAccess);
|
|
$tmpSubstockRightsEmpIdArray = spliti("--", $parMaskStkSubstockEmpIdAccess);
|
|
$tmpSubstockRightsEmpIdArrayLen = count($tmpSubstockRightsEmpIdArray);
|
|
for ($j = 0; $j < $tmpSubstockRightsEmpIdArrayLen; $j++) :
|
|
$tmpSubStockTupel = spliti(",", $tmpSubstockRightsEmpIdArray[$j]);
|
|
$tmpSubStockTupelLen = count($tmpSubStockTupel);
|
|
for ($k = 1; $k < $tmpSubStockTupelLen; $k++) :
|
|
// if (isStkChild($tmpSubStockTupel[$k], $tmpSubStockTupel[0], "1")) :
|
|
if ($retMode == "0") :
|
|
$retVal[] = $tmpSubStockTupel[$k];
|
|
endif;
|
|
if ($retMode == "1") :
|
|
$retVal[] = "(stk.stk_id = '" . $tmpSubStockTupel[$k] . "' OR stk.stk_path LIKE '%//" . $tmpSubStockTupel[$k] . "//%' )";
|
|
endif;
|
|
// endif;
|
|
endfor;
|
|
endfor;
|
|
endif;
|
|
if ($retMode == "1") :
|
|
if (count($retVal) > 0) :
|
|
$retVal = implode(" OR ", $retVal);
|
|
else :
|
|
$retVal = "";
|
|
endif;
|
|
endif;
|
|
return $retVal;
|
|
}
|
|
|
|
|
|
function stockMoveTA ($stkIdFrom, $stkIdTo, $atId, $itemQuantity, $tan = "", $remark = "", $serialno = "", $data = array(), $jobId = "0", $usrId = "0", $atIdBundle = "0", $uniqueBookingTimestamp = "", $stkmvSpecialDatetime2, $serialnoOld = "") {
|
|
global $db, $PHP_SELF;
|
|
global $hq_id, $emp_id, $userTypeName;
|
|
$retBool = false;
|
|
$retMsg = "";
|
|
|
|
|
|
// *** Get article data ***
|
|
// Number of articles matching number of stock areas
|
|
$atStkItemQuantity = getFieldValueFromId("article", "at_id", $atId, "at_stk_itemquantity");
|
|
$atStkAreaQuantity = getFieldValueFromId("article", "at_id", $atId, "at_stk_areaquantity");
|
|
// Switch for serial number
|
|
$atSerialNo = getFieldValueFromId("article", "at_id", $atId, "at_serialno");
|
|
// Bundle factor and code
|
|
$atBundleQuantity = getFieldValueFromId("article", "at_id", $atId, "at_bundlequantity");
|
|
$atBundleCode = getFieldValueFromId("article", "at_id", $atId, "at_bundlecode");
|
|
|
|
$serialno = trim($serialno);
|
|
$serialnoOld = trim($serialnoOld);
|
|
|
|
// Check planning status enabled or disabled for the stock $stkIdTo
|
|
// (Here it is a LOCAL parameter !!!)
|
|
$f_stk_planning = 1; // Default enabled
|
|
// Get root stock of $stkIdTo
|
|
$tmpStkIdRootOfStkIdTo = getStkPathId($stkIdTo);
|
|
if ($stkIdTo > 0) :
|
|
// Get the current planning status
|
|
$f_stk_planning = getFieldValueFromId("stock","stk_id",$tmpStkIdRootOfStkIdTo,"stk_planning");
|
|
endif;
|
|
|
|
// Get parameter
|
|
$stArticlemoveStkFromEqualsStkTo = getParameterValue("0", "STOCK_ARTICLEMOVE_STKFROM_EQ_STKTO", "0");
|
|
$tmpStkIdRootOfStkIdFrom = getStkPathId($stkIdFrom);
|
|
$stArticlemoveSerialnumberNoCheck = getParameterValue("0", "STOCK_ARTICLEMOVE_SERNO_NO_CHECK_" . $tmpStkIdRootOfStkIdFrom, "0");
|
|
|
|
|
|
// Get the emp_id of the root admin because he/she is allowed to change a password of an employee
|
|
$empIdRootAdmin = getEmpIdOfRootAdmin($userTypeName);
|
|
|
|
// *** Substock rights (BEGIN) ***
|
|
// Check for substock rights to execute the disposition
|
|
if ($emp_id == $empIdRootAdmin) :
|
|
$substockRightsFromOK = true;
|
|
$substockRightsToOK = true;
|
|
else :
|
|
$parMaskStkSubstockEmpIdAccess = getParameterValue($emp_id, "MASK_STK_SUBSTOCK_ACCESS");
|
|
if ($parMaskStkSubstockEmpIdAccess == "") :
|
|
$substockRightsFromOK = true;
|
|
$substockRightsToOK = true;
|
|
else :
|
|
$parMaskStkSubstockEmpIdAccess = str_replace("|", "--", $parMaskStkSubstockEmpIdAccess);
|
|
$tmpSubstockRightsEmpIdArray = spliti("--", $parMaskStkSubstockEmpIdAccess);
|
|
$tmpSubstockRightsEmpIdArrayLen = count($tmpSubstockRightsEmpIdArray);
|
|
$rootStockFoundInParameterStkFrom = false;
|
|
$rootStockFoundInParameterStkTo = false;
|
|
$substockRightsFromOK = false;
|
|
$substockRightsToOK = false;
|
|
for ($j = 0; $j < $tmpSubstockRightsEmpIdArrayLen; $j++) :
|
|
$tmpSubStockTupel = spliti(",", $tmpSubstockRightsEmpIdArray[$j]);
|
|
$tmpSubStockTupelLen = count($tmpSubStockTupel);
|
|
if ($tmpSubStockTupel[0] == $tmpStkIdRootOfStkIdFrom) :
|
|
$rootStockFoundInParameterStkFrom = true;
|
|
for ($k = 1; $k < $tmpSubStockTupelLen; $k++) :
|
|
if (isStkChild($tmpSubStockTupel[$k], $stkIdFrom, "1")) :
|
|
$substockRightsFromOK = true;
|
|
endif;
|
|
endfor;
|
|
endif;
|
|
if ($tmpSubStockTupel[0] == $tmpStkIdRootOfStkIdTo) :
|
|
$rootStockFoundInParameterStkTo = true;
|
|
for ($k = 1; $k < $tmpSubStockTupelLen; $k++) :
|
|
if (isStkChild($tmpSubStockTupel[$k], $stkIdTo, "1")) :
|
|
$substockRightsToOK = true;
|
|
endif;
|
|
endfor;
|
|
endif;
|
|
endfor;
|
|
endif;
|
|
if ($stkIdFrom == "0" || !$rootStockFoundInParameterStkFrom) : $substockRightsFromOK = true; endif;
|
|
if ($stkIdTo == "0" || !$rootStockFoundInParameterStkTo) : $substockRightsToOK = true; endif;
|
|
endif;
|
|
// *** Substock rights (END) ***
|
|
|
|
|
|
if ($stkIdFrom == "" || !is_numeric($stkIdFrom) || ($stkIdFrom != "0" && !existsEntry("stock",array("stk_id",$stkIdFrom)))) :
|
|
$retMsg = getLngt("Der Abgangslagerort ist nicht bekannt!");
|
|
elseif ($stkIdTo == "" || !is_numeric($stkIdTo) || ($stkIdTo != "0" && !existsEntry("stock",array("stk_id",$stkIdTo)))) :
|
|
$retMsg = getLngt("Der Ziellagerort ist nicht bekannt!");
|
|
elseif (!$substockRightsFromOK && $stkIdFrom != "0") :
|
|
$retMsg = getLngt("Für die Buchung vom Quelllagerort besteht keine Berechtigung!");
|
|
elseif (!$substockRightsToOK && $stkIdTo != "0") :
|
|
$retMsg = getLngt("Für die Buchung auf den Ziellagerort besteht keine Berechtigung!");
|
|
elseif ($atSerialNo && $serialno == "") :
|
|
$retMsg = getLngt("Dieser Artikel benötgt eine Seriennummer!");
|
|
elseif ($atSerialNo && $itemQuantity != 1) :
|
|
$retMsg = getLngt("Die Stückzahl darf bei erforderlicher Seriennummer die Menge 1 nicht überschreiten!");
|
|
elseif ($stkIdFrom == "0" && $stkIdFrom == $stkIdTo) :
|
|
$retMsg = getLngt("Durchgangsbuchungen vom Eingangs- zum Ausgangslager sind nicht zulässig!");
|
|
elseif ($stArticlemoveStkFromEqualsStkTo != "1" && $stkIdFrom == $stkIdTo) :
|
|
$retMsg = getLngt("Quelle und Ziel sind identisch!");
|
|
elseif ($atId == "" || !is_numeric($atId) || !existsEntry("article",array("at_id",$atId))) :
|
|
$retMsg = getLngt("Der Artikel ist nicht bekannt!");
|
|
elseif (!($atStkItemQuantity > 0 && $atStkAreaQuantity > 0)) :
|
|
$retMsg = getLngt("Zum Artikel existiert keine Angabe zur Lagereinheit im Artikelstamm (Verhältnis Anzahl Artikel zur Anzahl Lagerplätze)!");
|
|
elseif ($itemQuantity == "" || !is_numeric($itemQuantity) || $itemQuantity <= 0) :
|
|
$retMsg = getLngt("Die angeforderte Bewegungsmenge muss größer als 0 sein!");
|
|
elseif ($stkIdFrom > "0" && $atId != "" && $serialno != "" && $stArticlemoveSerialnumberNoCheck != "1" && !existsEntry("articleitem",array("stk_id",$stkIdFrom,"at_id",$atId,"ati_serialno",$serialno))) :
|
|
$retMsg = getLngt("Diese Seriennummer befindet sich nicht im Bestand des Quelllagers! Evtl. fehlerhafte manuelle Eingabe der Seriennummer?");
|
|
elseif ($stkIdTo > "0" && $atId != "" && $serialno != "" && $stArticlemoveSerialnumberNoCheck != "1" && existsEntry("articleitem",array("at_id",$atId,"ati_serialno",$serialno))) :
|
|
$tmpStkId = getFieldValueFromClause("articleitem", "stk_id", "at_id = '" . $atId . "' AND ati_serialno = '" . $serialno . "'");
|
|
$tmpStkName = getFieldValueFromId("stock", "stk_id", $tmpStkId, "stk_name");
|
|
$retMsg = getLngt("Diese angegebene Seriennummer befindet sich schon im Bestand (Lagerort: " . $tmpStkName . ")! Evtl. fehlerhafte manuelle Eingabe der Seriennummer?");
|
|
else :
|
|
$retBool = true;
|
|
|
|
// *** Check article item quantity of the SOURCE STOCK >= quantity to be moved ***
|
|
// Attention: Exception is $stkIdFrom == "0" because TA is ok, because this special source target has an endless quantity for each article !!!
|
|
if ($stkIdFrom > "0") :
|
|
// Get article item quantity of the source stock
|
|
$stockArticleItemQuantityStkFrom = getStockArticleQuantitySubtree($stkIdFrom, "1", "stkat.stkat_itemquantity", "stkat.at_id = '" . $atId . "'");
|
|
if ($stockArticleItemQuantityStkFrom < $itemQuantity) :
|
|
$retBool = false;
|
|
$retMsg = getLngt("Die angeforderte Bewegungsmenge " . $itemQuantity . " ist nicht in der Höhe auf diesem Abgangslager! (Bestand: " . $stockArticleItemQuantityStkFrom . ")");
|
|
endif;
|
|
endif;
|
|
|
|
// *** Check free (item) quantity of the TARGET STOCK >= quantity to be moved ***
|
|
// Attention: Exception is $stkIdTo == "0" because TA is ok, because this special source target has a virtual quantity of 0 for each article !!!
|
|
if ($retBool && $stkIdTo > "0") :
|
|
|
|
// ITEM QUANTITIES of the current article
|
|
// Get article item quantity of the source stock
|
|
// $stockArticleItemQuantityStkFrom // Already done above ...
|
|
// Get article item quantity of the target stock
|
|
$stockArticleItemQuantityStkTo = getStockArticleQuantitySubtree($stkIdTo, "1", "stkat.stkat_itemquantity", "stkat.at_id = '" . $atId . "'");
|
|
// Sum up article item quantities of source stock and target stock to check whether item movement cuts down on one stock area
|
|
// (E.g.: IF 100 items of article A need 1 stock area AND e.g. on stock S are 30 items (need 1 stock area) AND on stock T are 70 pieces (also need 1 stock area)
|
|
// THEN only 1 stock area is needed after item movement either the 30 items from S to T or 70 items from T to S)
|
|
$sumStockArticleItemQuantity = $stockArticleItemQuantityStkFrom + $stockArticleItemQuantityStkTo;
|
|
// Get the rest per modulo
|
|
$tmpRest = ($sumStockArticleItemQuantity % intval($atStkItemQuantity / $atStkAreaQuantity));
|
|
if ($tmpRest == 0) :
|
|
// ...
|
|
endif;
|
|
/*
|
|
echo "Summe: " . $sumStockArticleItemQuantity . " = " . $stockArticleItemQuantityStkFrom . " + " . $stockArticleItemQuantityStkTo . "<br>";
|
|
echo "Faktor: " . intval($atStkItemQuantity / $atStkAreaQuantity) . " --- " . $atStkItemQuantity . " " . $atStkAreaQuantity . "<br>";
|
|
echo "Rest: " . $tmpRest . "<br>";
|
|
*/
|
|
|
|
// AREA QUANTITIES of the current stock
|
|
// Get the max area quantity of the stock
|
|
$stkMaxquantity = getFieldValueFromId("stock", "stk_id", $stkIdTo, "stk_maxquantity");
|
|
// Get quantity of areas occupied by articles
|
|
$articlePlaceQuantityOfCurrentStock = getStockArticleQuantitySubtree($stkIdTo, "1", "", "");
|
|
// Get max area quantity of the whole subtree of the current stock
|
|
$stkNextSublevelMaxQuantity = getFieldSumOfNextSubLevelOfTree("stk_maxquantity", $stkIdTo, "stock", "stk_pre_id", "");
|
|
// Compute free places of the target stock
|
|
$stkQuantityFree = ($stkMaxquantity - $articlePlaceQuantityOfCurrentStock - $stkNextSublevelMaxQuantity);
|
|
|
|
// Convert the free area quantity to the quantity or free items according to the specified article
|
|
$stkItemQuantityFree = ceil(($stkQuantityFree * $atStkItemQuantity) / $atStkAreaQuantity);
|
|
|
|
if ($retBool && $f_stk_planning == "1" && $stkItemQuantityFree < $itemQuantity) :
|
|
$retBool = false; // Currently no restriction according to the booked quantity!!!
|
|
$retMsg = getLngt("Die angeforderte Bewegungsmenge " . $itemQuantity . " passt kapazitativ nicht auf dieses Ziellager! (Frei: " . $stkItemQuantityFree . ")");
|
|
endif;
|
|
endif;
|
|
|
|
if ($retBool) :
|
|
TA("B");
|
|
|
|
// *** Insert stock article movement ***
|
|
$dataString = "";
|
|
$dataLen = count($data);
|
|
$tmpIsEmpty = true;
|
|
for ($i = 0; $i < $dataLen; $i++) :
|
|
if ($data[$i] != "") :
|
|
$tmpIsEmpty = false;
|
|
break;
|
|
endif;
|
|
endfor;
|
|
if (!$tmpIsEmpty) :
|
|
$dataString = implode("<br>", $data);
|
|
endif;
|
|
$currentTime = getDateTime("0");
|
|
insertStmt("stockmove",array("stkmv_timestamp", $currentTime, "stk_id_from",$stkIdFrom,"stk_id_to",$stkIdTo,"at_id",$atId,"stkmv_itemquantity",$itemQuantity,"stkmv_bundlequantity",$atBundleQuantity,"stkmv_bundlecode",$atBundleCode,"stkmv_tan",$tan,"stkmv_remark",$remark,"stkmv_serialno",$serialno,"stkmv_data",$dataString,"at_id_bundle",$atIdBundle,"stkmv_datetime",$uniqueBookingTimestamp,"stkmv_datetime2",$stkmvSpecialDatetime2,"jb_id",$jobId,"usr_id",$usrId));
|
|
|
|
// *** Update stock quantity from source stock ***
|
|
if ($stkIdFrom > "0") :
|
|
// Get current item quantity of the specified article and source stock
|
|
$stkatItemQuantity = getFieldValueFromClause("stockarticle", "stkat_itemquantity", "stk_id = '" . $stkIdFrom . "' AND at_id = '" . $atId . "'");
|
|
// Subtract the $itemQuantity from $stkatItemQuantity
|
|
$stkatItemQuantity = $stkatItemQuantity - $itemQuantity;
|
|
if ($stkatItemQuantity > 0) :
|
|
// Convert to the new used stock areas
|
|
$stkatQuantity = ceil(($stkatItemQuantity * $atStkAreaQuantity) / $atStkItemQuantity);
|
|
updateStmt("stockarticle","stk_id",$stkIdFrom,array("stkat_quantity",$stkatQuantity,"stkat_itemquantity",$stkatItemQuantity),"at_id = '".$atId."'");
|
|
else :
|
|
deleteStmt("stockarticle"," stk_id = '".$stkIdFrom."' AND at_id = '".$atId."' ");
|
|
endif;
|
|
|
|
if ($atId != "" && $serialno != "") :
|
|
// ONLY in case $stkIdFrom == $stkIdTo !!!
|
|
if ($stkIdFrom == $stkIdTo && $serialnoOld != "" && $serialnoOld != $serialno) :
|
|
if (existsEntry("articleitem",array("stk_id",$stkIdFrom,"at_id",$atId,"ati_serialno",$serialnoOld))) :
|
|
deleteStmt("articleitem"," stk_id = '".$stkIdFrom."' AND at_id = '".$atId."' AND ati_serialno = '".$serialnoOld."' ");
|
|
endif;
|
|
endif;
|
|
if (existsEntry("articleitem",array("stk_id",$stkIdFrom,"at_id",$atId,"ati_serialno",$serialno))) :
|
|
deleteStmt("articleitem"," stk_id = '".$stkIdFrom."' AND at_id = '".$atId."' AND ati_serialno = '".$serialno."' ");
|
|
endif;
|
|
endif;
|
|
endif;
|
|
|
|
// *** Update stock quantity from target stock ***
|
|
if ($stkIdTo > "0") :
|
|
// Get current item quantity of the specified article and target stock
|
|
$stkatItemQuantity = getFieldValueFromClause("stockarticle", "stkat_itemquantity", "stk_id = '" . $stkIdTo . "' AND at_id = '" . $atId . "'");
|
|
if ($stkatItemQuantity == "") : $stkatItemQuantity = 0; endif;
|
|
// Add to the $itemQuantity
|
|
$stkatItemQuantity = $stkatItemQuantity + $itemQuantity;
|
|
// Convert to the new used stock areas
|
|
$stkatQuantity = ceil(($stkatItemQuantity * $atStkAreaQuantity) / $atStkItemQuantity);
|
|
if (existsEntry("stockarticle",array("stk_id",$stkIdTo,"at_id",$atId))) :
|
|
updateStmt("stockarticle","stk_id",$stkIdTo,array("stkat_quantity",$stkatQuantity,"stkat_itemquantity",$stkatItemQuantity),"at_id = '".$atId."'");
|
|
else :
|
|
insertStmt("stockarticle",array("stk_id",$stkIdTo,"at_id",$atId,"stkat_quantity",$stkatQuantity,"stkat_itemquantity",$stkatItemQuantity));
|
|
endif;
|
|
// Insert serial number
|
|
if ($atId != "" && $serialno != "") :
|
|
insertStmt("articleitem",array("at_id",$atId,"stk_id",$stkIdTo,"ati_serialno",$serialno,
|
|
"ati_data_01",$data[0],"ati_data_02",$data[1],"ati_data_03",$data[2],"ati_data_04",$data[3],"ati_data_05",$data[4],
|
|
"ati_data_06",$data[5],"ati_data_07",$data[6],"ati_data_08",$data[7],"ati_data_09",$data[8],"ati_data_10",$data[9],
|
|
"ati_data_11",$data[10],"ati_data_12",$data[11],"ati_data_13",$data[12],"ati_data_14",$data[13],"ati_data_15",$data[14],
|
|
"ati_data_16",$data[15],"ati_data_17",$data[16],"ati_data_18",$data[17],"ati_data_19",$data[18],"ati_data_20",$data[19],
|
|
"ati_data_21",$data[20],"ati_data_22",$data[21],"ati_data_23",$data[22],"ati_data_24",$data[23],"ati_data_25",$data[24]));
|
|
endif;
|
|
endif;
|
|
|
|
TA("C");
|
|
TA("E");
|
|
endif;
|
|
endif;
|
|
return array($retBool, $retMsg);
|
|
}
|
|
|
|
// ------------------------------------------------------------------------------------------------------
|
|
|
|
function postParseStockJournal ($textToParse) {
|
|
global $f_stkmv_export;
|
|
$textToParse = trim($textToParse);
|
|
|
|
if ($textToParse != "") :
|
|
|
|
// Iterate ALL occurrences of "<postparser>...</postparser>"
|
|
while (!(strpos($textToParse, "<postparser>") === FALSE)) {
|
|
|
|
$beginTagPosBegin = strpos($textToParse, "<postparser>");
|
|
$beginTagPosEnd = $beginTagPosBegin + 12;
|
|
$endTagPosBegin = strpos($textToParse, "</postparser>");
|
|
$endTagPosEnd = $endTagPosBegin + 13;
|
|
|
|
if ($beginTagPosEnd < $endTagPosBegin) :
|
|
$tagContent = substr($textToParse, $beginTagPosEnd, $endTagPosBegin - $beginTagPosEnd);
|
|
$tagContent = trim($tagContent);
|
|
|
|
if ($tagContent != "" && !(strpos($tagContent, "|") === FALSE)) :
|
|
$tagContent = str_replace("|", "-,-", $tagContent);
|
|
$parseArray = spliti("-,-", $tagContent); // (stk.stk_id, stk2.stk_id, at.at_id, stkmv.stkmv_tan, stkmv.stkmv_serialno, stkmv.stkmv_itemquantity, stkmv.stkmv_id, stkmv.at_id_bundle, stkmv.stkmv_datetime, stkmv.stkmv_data)
|
|
|
|
// Special treatment
|
|
if ($f_stkmv_export != "1") :
|
|
$tagContent = "<a href=\"javascript:finishPageJournalAction('0', '" . $parseArray[0] . "', '" . $parseArray[1] . "', '" . $parseArray[2] . "', '" . $parseArray[3] . "', '" . $parseArray[4] . "', '" . $parseArray[5] . "', '" . $parseArray[6] . "', '" . $parseArray[7] . "', '" . $parseArray[8] . "', '" . $parseArray[9] . "')\">Rückbuchung</a>";
|
|
if ($parseArray[1] != "" && $parseArray[1] != "0") :
|
|
// Only display if target stock is not null (incoming disposition)
|
|
$tagContent .= "<br><a href=\"javascript:finishPageJournalAction('1', '" . $parseArray[0] . "', '" . $parseArray[1] . "', '" . $parseArray[2] . "', '" . $parseArray[3] . "', '" . $parseArray[4] . "', '" . $parseArray[5] . "', '" . $parseArray[6] . "', '" . $parseArray[7] . "', '" . $parseArray[8] . "', '" . $parseArray[9] . "')\">Disponieren</a>";
|
|
endif;
|
|
else :
|
|
$tagContent = "";
|
|
endif;
|
|
|
|
// Remove last "<br>"
|
|
// $tagContent = substr($tagContent, 0, -4);
|
|
endif;
|
|
|
|
// Substitute text fragment
|
|
$textToParse = substr_replace($textToParse, $tagContent, $beginTagPosBegin, $endTagPosEnd - $beginTagPosBegin);
|
|
endif;
|
|
}
|
|
endif;
|
|
return $textToParse;
|
|
}
|
|
|
|
// Displays the stock journal according to the parameter
|
|
function getStockJournal ($whereClause = "", $orderByClause = "", $limit = "", $orderByDirection = "") {
|
|
global $db, $PHP_SELF;
|
|
global $f_stkmv_export, $numOfJournalEntries, $stkIdRoot;
|
|
global $stkJournalTitleArray, $stkJournalTitleDisplayedArray;
|
|
$retOut = "";
|
|
if (true) :
|
|
$sqlStmt = getStmtStockJournal($whereClause, $orderByClause, $limit, $orderByDirection);
|
|
$result = $db->query($sqlStmt);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
|
|
$tmpStkIdRoot = getStkPathId($stkIdRoot); // Makes it safe to have a real root stock
|
|
$maskStkJournalHeadlines = getParameterValue("0", "MASK_STK_JOURNALHEADLINES_" . $tmpStkIdRoot, "0");
|
|
if ($maskStkJournalHeadlines == "") :
|
|
$maskStkJournalHeadlines = $stkJournalTitleArray;
|
|
else :
|
|
$maskStkJournalHeadlines = spliti(",",$maskStkJournalHeadlines);
|
|
endif;
|
|
$maskStkJournalHeadlinesDisplayed = getParameterValue("0", "MASK_STK_JOURNALHEADLINES_DISPLAYED_" . $tmpStkIdRoot, "0");
|
|
if ($maskStkJournalHeadlinesDisplayed == "") :
|
|
$maskStkJournalHeadlinesDisplayed = $stkJournalTitleDisplayedArray;
|
|
else :
|
|
$maskStkJournalHeadlinesDisplayed = spliti(",",$maskStkJournalHeadlinesDisplayed);
|
|
endif;
|
|
$maskStkJournalHeadlinesLen = count($maskStkJournalHeadlines);
|
|
$tmpFieldArray = array("stkmv_id", "stkmv_timestamp", "stk_name_from", "stk_name_to", "at_eid", "at_name", "at_match", "stkmv_itemquantity", "stkmv_tan", "stkmv_remark", "stkmv_serialno", "stkmv_data", "at_eid_bundle", "jb_id", "stkmv_datetime2", "usr_data", "stkmv_x");
|
|
// $titleArray = array(getLngt("Lfd. "), getLngt("Zeitstempel "), getLngt("Quelle "), getLngt("Ziel "), getLngt("Artikelnr. "), getLngt("Artikelbez. "), getLngt("Artikelzusatz "), getLngt("Stückzahl "), getLngt("Transaktionsnr. "), getLngt("Bemerkung "), getLngt("Seriennummer "), getLngt("Zusatzdaten "), getLngt("Gebindeartikel "), getLngt("Auftrag "), getLngt("Zusatzdatum "), getLngt("Mitarbeiter "), getLngt("Aktion "));
|
|
$titleArray = array();
|
|
$alignArray = array();
|
|
$fieldArray = array();
|
|
$aligns = "rllllllrlllllrllc";
|
|
$colSortOffsetArray = array();
|
|
$postParserField = "";
|
|
for ($i = 0; $i < $maskStkJournalHeadlinesLen; $i++) :
|
|
if ($maskStkJournalHeadlinesDisplayed[$i] == "1" && $maskStkJournalHeadlines[$i] != "") :
|
|
$titleArray[] = $maskStkJournalHeadlines[$i];
|
|
$alignArray[] = substr($aligns, $i, 1);
|
|
$fieldArray[] = $tmpFieldArray[$i];
|
|
$colSortOffsetArray[] = $i;
|
|
if ($tmpFieldArray[$i] == "stkmv_x") :
|
|
$postParserField = "stkmv_x";
|
|
endif;
|
|
endif;
|
|
endfor;
|
|
$alignTitles = "left";
|
|
// $widths = "130";
|
|
// $widthArray = spliti(",",$widths);
|
|
$summationField = "stkmv_itemquantity";
|
|
$mode = "1"; // Output from DB-RESULT
|
|
$javaScriptFunctionNameForSort = "finishPageSortJournal";
|
|
$sortDBField = "YES"; // Used in following include-file for sorting per column;
|
|
$fieldSortArray = array("YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES","YES");
|
|
include ("../include/inc_list_defineoutput.inc.php");
|
|
// Post parsing if necessary
|
|
if ($postParserField != "") :
|
|
$tableBody = postParseStockJournal($tableBody);
|
|
endif;
|
|
|
|
// $retOut .= "<table>\n";
|
|
$retOut .= $tableHeader . $tableBody;
|
|
// $retOut .= "</table>\n";
|
|
|
|
$result->free();
|
|
|
|
$numOfJournalEntries = $rowCounter;
|
|
|
|
// Optional output to file
|
|
if ($f_stkmv_export) :
|
|
stockFilesOutputCSV($sqlStmt, $titleArray, $fieldArray, $aligns, "LAGERJOURNAL", $postParserField, "postParseStockJournal");
|
|
endif;
|
|
endif;
|
|
return $retOut;
|
|
}
|
|
|
|
// ------------------------------------------------------------------------------------------------------
|
|
|
|
function postParseStockJobs ($textToParse) {
|
|
$textToParse = trim($textToParse);
|
|
|
|
if ($textToParse != "") :
|
|
|
|
// Iterate ALL occurrences of "<postparser>...</postparser>"
|
|
while (!(strpos($textToParse, "<postparser>") === FALSE)) {
|
|
|
|
$beginTagPosBegin = strpos($textToParse, "<postparser>");
|
|
$beginTagPosEnd = $beginTagPosBegin + 12;
|
|
$endTagPosBegin = strpos($textToParse, "</postparser>");
|
|
$endTagPosEnd = $endTagPosBegin + 13;
|
|
|
|
if ($beginTagPosEnd < $endTagPosBegin) :
|
|
$tagContent = substr($textToParse, $beginTagPosEnd, $endTagPosBegin - $beginTagPosEnd);
|
|
$tagContent = trim($tagContent);
|
|
|
|
// if ($tagContent != "" && !(strpos($tagContent, "|") === FALSE)) :
|
|
if ($tagContent != "") :
|
|
// $tagContent = str_replace("|", "-,-", $tagContent);
|
|
// $parseArray = spliti("-,-", $tagContent); // (jb_id)
|
|
|
|
// Special treatment
|
|
// $tagContent = "<a href=\"javascript:finishPageJobAction('" . $parseArray[0] . "')\">" . $parseArray[0] . "</a>";
|
|
$tagContent = "<a href=\"javascript:finishPageJobAction('" . $tagContent . "')\">" . $tagContent . "</a>";
|
|
|
|
// Remove last "<br>"
|
|
// $tagContent = substr($tagContent, 0, -4);
|
|
endif;
|
|
|
|
// Substitute text fragment
|
|
$textToParse = substr_replace($textToParse, $tagContent, $beginTagPosBegin, $endTagPosEnd - $beginTagPosBegin);
|
|
endif;
|
|
}
|
|
endif;
|
|
return $textToParse;
|
|
}
|
|
|
|
// Displays all current jobs matching one of the addresses of the stock tree
|
|
function getStockJobs ($whereClause = "", $orderByClause = "", $limit = "") {
|
|
global $db, $PHP_SELF;
|
|
global $numOfJobEntries;
|
|
$retOut = "";
|
|
if (true) :
|
|
$sqlStmt = getStmtStockJobs($whereClause, $orderByClause, $limit);
|
|
|
|
$result = $db->query($sqlStmt);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
|
|
$titleArray = array(getLngt("Auftragsnr. "), getLngt("Auftragszeit "), getLngt("Preis "), getLngt("Firma Abholung "), getLngt("Abholadresse "), getLngt("Firma. 1. Anlieferung. "), getLngt("1. Anlieferadr. "));
|
|
$aligns = "r,c,r,l,l,l,l";
|
|
$fieldArray = array("jb_id", "jb_ordertime", "jb_totalprice", "comp_1", "address_1", "comp_2", "address_2");
|
|
$postParserField = "jb_id";
|
|
$alignArray = spliti(",",$aligns);
|
|
$alignTitles = "left";
|
|
// $widths = "130";
|
|
// $widthArray = spliti(",",$widths);
|
|
$summationField = "";
|
|
$mode = "1"; // Output from DB-RESULT
|
|
$javaScriptFunctionNameForSort = "finishPageSortStockJobs";
|
|
$sortDBField = "YES"; // Used in following include-file for sorting per column;
|
|
$fieldSortArray = array("YES","YES","YES","YES","YES","YES","YES");
|
|
include ("../include/inc_list_defineoutput.inc.php");
|
|
// Post parsing if necessary
|
|
if ($postParserField != "") :
|
|
$tableBody = postParseStockJobs($tableBody);
|
|
endif;
|
|
|
|
// $retOut .= "<table>\n";
|
|
$retOut .= $tableHeader . $tableBody;
|
|
// $retOut .= "</table>\n";
|
|
|
|
$result->free();
|
|
|
|
$numOfJobEntries = $rowCounter;
|
|
endif;
|
|
return $retOut;
|
|
}
|
|
|
|
// ------------------------------------------------------------------------------------------------------
|
|
|
|
// Returns the statement to get all stocks of the whole subtree of a specified stock
|
|
function getStmtAllStocksByStkId ($stkId, $whereClause = "") {
|
|
$retStmt = "";
|
|
if ($stkId != "" && is_numeric($stkId)) :
|
|
if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;
|
|
$retStmt = "SELECT stk.stk_id, stk.stk_name, stk.stk_barcode, stk.ad_id, stk.stk_hsno"
|
|
. " FROM stock AS stk"
|
|
. " WHERE (stk.stk_id = '" . $stkId . "' OR stk.stk_path LIKE '%//" . $stkId . "//%') " . $whereClause
|
|
. " ORDER BY stk.stk_name";
|
|
endif;
|
|
return $retStmt;
|
|
}
|
|
|
|
// Returns the statement to get the journal according to the specified WHERE clause
|
|
function getStmtStockJournal ($whereClause = "", $orderByClause = "", $limit = "", $orderByDirection = "") {
|
|
global $md_id, $hq_id;
|
|
$retStmt = "";
|
|
if ($orderByClause == "") : $orderByClause = "stkmv.stkmv_timestamp"; endif;
|
|
if ($limit == "") : $limit = "1000"; endif;
|
|
if ($orderByDirection != "") : $orderByDirection = " DESC "; endif;
|
|
if ($whereClause != "") : $whereClause = " WHERE " . $whereClause; endif;
|
|
$retStmt = "SELECT stkmv.stkmv_id, stkmv.stkmv_timestamp, stk.stk_name AS stk_name_from, stk2.stk_name AS stk_name_to,"
|
|
. " at.at_id, at.at_eid, at.at_name, at.at_match, stkmv.stkmv_itemquantity, CEILING(stkmv.stkmv_itemquantity / stkmv.stkmv_bundlequantity) AS bundlequantity,"
|
|
. " stkmv.stkmv_tan, stkmv.stkmv_remark, stkmv.stkmv_serialno, stkmv.stkmv_data, stkmv.jb_id, stk.stk_id AS stk_id_from, stk2.stk_id AS stk_id_to,"
|
|
. " stkmv.at_id_bundle, stkmv.stkmv_datetime, LEFT(stkmv.stkmv_datetime2,10) AS stkmv_datetime2, at2.at_eid AS at_eid_bundle, atgi.atg_id,"
|
|
. " CONCAT(usr.usr_firstname,'<br>',usr.usr_name) AS usr_data,"
|
|
. " CONCAT(IFNULL(stk.stk_id,''),'|',IFNULL(stk2.stk_id,''),'|',at.at_id,'|',stkmv.stkmv_tan,'|',stkmv.stkmv_serialno,'|',stkmv.stkmv_itemquantity,'|',stkmv.stkmv_id,'|',stkmv.at_id_bundle,'|',stkmv.stkmv_datetime,'|',stkmv.stkmv_data) AS stkmv_x"
|
|
. " FROM stockmove AS stkmv LEFT JOIN stock AS stk ON stkmv.stk_id_from = stk.stk_id"
|
|
. " LEFT JOIN stock AS stk2 ON stkmv.stk_id_to = stk2.stk_id"
|
|
. " LEFT JOIN article AS at ON stkmv.at_id = at.at_id"
|
|
. " LEFT JOIN articlegroupitem AS atgi ON stkmv.at_id = atgi.at_id AND atgi.md_id = '" . $md_id . "' AND atgi.hq_id = '" . $hq_id . "'"
|
|
. " LEFT JOIN article AS at2 ON stkmv.at_id_bundle = at2.at_id"
|
|
. " LEFT JOIN user AS usr ON stkmv.usr_id = usr.usr_id"
|
|
. $whereClause
|
|
. " ORDER BY " . $orderByClause . $orderByDirection
|
|
. " LIMIT 0," . $limit;
|
|
return $retStmt;
|
|
}
|
|
|
|
// Returns the statement to get articles
|
|
function getStmtArticle ($whereClause = "", $orderByClause = "", $limit = "") {
|
|
$retStmt = "";
|
|
if ($orderByClause == "") : $orderByClause = "at.at_name"; endif;
|
|
if ($limit == "") : $limit = "1000"; endif;
|
|
if ($whereClause != "") : $whereClause = " WHERE " . $whereClause; endif;
|
|
$retStmt = "SELECT at.at_id, at.at_eid, at.at_name, at.at_match, at.at_description, at.at_barcode, at.at_group,"
|
|
. " at.at_bundlequantity, at.at_stk_itemquantity, at.at_stk_areaquantity, at.at_bundlecode"
|
|
. " FROM article AS at"
|
|
. $whereClause
|
|
. " ORDER BY " . $orderByClause
|
|
. " LIMIT 0," . $limit;
|
|
return $retStmt;
|
|
}
|
|
|
|
// Returns the statement to get all current jobs matching one of the addresses of the stock tree
|
|
function getStmtStockJobs ($whereClause = "", $orderByClause = "", $limit = "") {
|
|
$retStmt = "";
|
|
if ($orderByClause == "") : $orderByClause = "jb.jb_ordertime"; endif;
|
|
if ($limit == "") : $limit = "1000"; endif;
|
|
if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;
|
|
$retStmt = "SELECT jb.jb_id, jb.jb_totalprice, jb.jb_ordertime,"
|
|
. " tr.tr_comp, tr.tr_comp2, tr.tr_phone, tr.tr_person, tr.tr_remark,"
|
|
. " ad.ad_street, tr.tr_hsno, ad.ad_zipcode, ad.ad_city,"
|
|
. " tr2.tr_comp, tr2.tr_comp2, tr2.tr_phone, tr2.tr_person, tr2.tr_remark,"
|
|
. " ad2.ad_street, tr2.tr_hsno, ad2.ad_zipcode, ad2.ad_city,"
|
|
. " CONCAT(IFNULL(tr.tr_comp,''),'<br>',IFNULL(tr.tr_comp2,''),'<br>',IFNULL(tr.tr_person,''),'<br><br>',IFNULL(tr.tr_phone,''),'<br><br>',IFNULL(tr.tr_remark,'')) AS comp_1,"
|
|
. " CONCAT(IFNULL(tr2.tr_comp,''),'<br>',IFNULL(tr2.tr_comp2,''),'<br>',IFNULL(tr2.tr_person,''),'<br><br>',IFNULL(tr2.tr_phone,''),'<br><br>',IFNULL(tr2.tr_remark,'')) AS comp_2,"
|
|
. " CONCAT(ad.ad_street,' ',tr.tr_hsno,'<br>',ad.ad_zipcode,' ',ad.ad_city) AS address_1,"
|
|
. " CONCAT(ad2.ad_street,' ',tr2.tr_hsno,'<br>',ad2.ad_zipcode,' ',ad2.ad_city) AS address_2"
|
|
. " FROM job AS jb LEFT JOIN tour AS tr2 ON jb.jb_id = tr2.jb_id AND tr2.tr_sort = '2' LEFT JOIN address AS ad2 ON tr2.ad_id = ad2.ad_id, "
|
|
. " tour AS tr, address AS ad, costcenter AS csc, customer AS cs, company AS cmp"
|
|
. " WHERE jb.jb_status != '2' AND jb.jb_id = tr.jb_id AND tr.tr_sort = '1' AND tr.ad_id = ad.ad_id AND"
|
|
. " jb.csc_id_payer = csc.csc_id AND csc.cs_id = cs.cs_id AND cs.cmp_id = cmp.cmp_id"
|
|
. " " . $whereClause
|
|
. " ORDER BY " . $orderByClause
|
|
. " LIMIT 0," . $limit;
|
|
return $retStmt;
|
|
}
|
|
|
|
// Returns the statement to get articles
|
|
function getStmtStockArticles ($stkId, $treeMode = "0", $whereClause = "", $orderByClause = "") {
|
|
global $md_id, $hq_id;
|
|
$retStmt = "";
|
|
if ($stkId != "") :
|
|
if ($orderByClause == "") : $orderByClause = "stk.stk_name, at.at_name"; endif;
|
|
if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;
|
|
$whereClauseTreeMode = " stk.stk_id = '" . $stkId . "' ";
|
|
if ($treeMode == "1") :
|
|
$whereClauseTreeMode = " (stk.stk_id = '" . $stkId . "' OR stk.stk_path LIKE '%//" . $stkId . "//%' ) ";
|
|
endif;
|
|
$retStmt = "SELECT stk.stk_id, stk.stk_name, CONCAT(stkat.at_id, '|', at.at_eid, '|', at.at_serialno, '|', stk.stk_id) AS at_x," .
|
|
" at.at_eid, at.at_name, at.at_match, at.at_serialno, stkat.stkat_maxquantity, stkat.stkat_quantity, stkat.stkat_itemquantity," .
|
|
" CEILING(stkat.stkat_itemquantity / at.at_bundlequantity) AS bundlequantity, atg.atg_name" .
|
|
" FROM stock AS stk, article AS at, stockarticle AS stkat" .
|
|
" LEFT JOIN articlegroupitem AS atgi ON stkat.at_id = atgi.at_id AND atgi.md_id = '" . $md_id . "' AND atgi.hq_id = '" . $hq_id . "'" .
|
|
" LEFT JOIN articlegroup AS atg ON atg.md_id = '" . $md_id . "' AND atg.atg_id = atgi.atg_id" .
|
|
" WHERE stk.stk_id = stkat.stk_id AND stkat.at_id = at.at_id AND " . $whereClauseTreeMode . " " . $whereClause .
|
|
" ORDER BY " . $orderByClause;
|
|
endif;
|
|
return $retStmt;
|
|
}
|
|
|
|
// Returns the statement to get article sums
|
|
function getStmtArticleSum ($stkId, $whereClause = "", $orderByClause = "") {
|
|
global $md_id, $hq_id;
|
|
$retStmt = "";
|
|
if ($stkId != "") :
|
|
if ($orderByClause == "") : $orderByClause = "at.at_name"; endif;
|
|
if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;
|
|
$retStmt = "SELECT at.at_eid, at.at_name, at.at_match, SUM(stkat.stkat_maxquantity) AS maxquantity, SUM(stkat.stkat_itemquantity) AS itemquantity, CEILING(SUM(stkat.stkat_itemquantity / at.at_bundlequantity)) AS bundlequantity, atg.atg_name," .
|
|
" CONCAT(stkat.at_id, '|', at.at_eid, '|', at.at_serialno, '|', stkat.stk_id) AS stkat_x" .
|
|
" FROM article AS at, stock AS stk, stockarticle AS stkat" .
|
|
" LEFT JOIN articlegroupitem AS atgi ON stkat.at_id = atgi.at_id AND atgi.md_id = '" . $md_id . "' AND atgi.hq_id = '" . $hq_id . "'" .
|
|
" LEFT JOIN articlegroup AS atg ON atg.md_id = '" . $md_id . "' AND atg.atg_id = atgi.atg_id" .
|
|
" WHERE at.at_id = stkat.at_id AND stk.stk_id = stkat.stk_id AND (stk.stk_id = '" . $stkId . "' OR stk.stk_path LIKE '%//" . $stkId . "//%' ) " . $whereClause .
|
|
" GROUP BY at.at_id" .
|
|
" ORDER BY " . $orderByClause;
|
|
endif;
|
|
return $retStmt;
|
|
}
|
|
|
|
// Returns the statement to get article items with serial numbers
|
|
function getStmtStockArticleItems ($atId, $stkId = "0", $whereClause = "", $fieldClause = "", $orderByClause = "", $limit = "") {
|
|
$retStmt = "";
|
|
if ($atId != "") :
|
|
if ($orderByClause == "") : $orderByClause = "stk.stk_name, ati.ati_serialno"; endif;
|
|
if ($limit == "") : $limit = "1000"; endif;
|
|
if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;
|
|
if ($stkId > "0") : $whereClause .= " AND stk.stk_id = '" . $stkId . "' "; endif;
|
|
$retStmt = "SELECT at.at_eid, at.at_name, ati.at_id, ati.ati_serialno, ati.stk_id, stk.stk_name,"
|
|
. " ati.ati_data_01, ati.ati_data_02, ati.ati_data_03, ati.ati_data_04, ati.ati_data_05,"
|
|
. " ati.ati_data_06, ati.ati_data_07, ati.ati_data_08, ati.ati_data_09, ati.ati_data_10,"
|
|
. " ati.ati_data_11, ati.ati_data_12, ati.ati_data_13, ati.ati_data_14, ati.ati_data_15,"
|
|
. " ati.ati_data_16, ati.ati_data_17, ati.ati_data_18, ati.ati_data_19, ati.ati_data_20,"
|
|
. " ati.ati_data_21, ati.ati_data_22, ati.ati_data_23, ati.ati_data_24, ati.ati_data_25"
|
|
. $fieldClause
|
|
. " FROM article AS at, articleitem AS ati, stock AS stk"
|
|
. " WHERE ati.at_id = '" . $atId . "' AND ati.at_id = at.at_id AND ati.stk_id = stk.stk_id" . $whereClause
|
|
. " ORDER BY " . $orderByClause
|
|
. " LIMIT 0," . $limit;
|
|
endif;
|
|
return $retStmt;
|
|
}
|
|
|
|
// Returns the statement to get article items with serial numbers, article data and stock data
|
|
function getStmtGenericStockArticleItems ($atId = "0", $stkId = "0", $serialNo = "", $stkTreeMode = "0", $whereClause = "", $fieldClause = "", $orderByClause = "", $limit = "") {
|
|
$retStmt = "";
|
|
if ($fieldClause == "") :
|
|
$fieldClause = "at.at_eid, at.at_name, at.at_group, ati.at_id, ati.ati_serialno, ati.stk_id, stk.stk_name,"
|
|
. "ati.ati_data_01, ati.ati_data_02, ati.ati_data_03, ati.ati_data_04, ati.ati_data_05,"
|
|
. "ati.ati_data_06, ati.ati_data_07, ati.ati_data_08, ati.ati_data_09, ati.ati_data_10,"
|
|
. "ati.ati_data_11, ati.ati_data_12, ati.ati_data_13, ati.ati_data_14, ati.ati_data_15,"
|
|
. "ati.ati_data_16, ati.ati_data_17, ati.ati_data_18, ati.ati_data_19, ati.ati_data_20,"
|
|
. "ati.ati_data_21, ati.ati_data_22, ati.ati_data_23, ati.ati_data_24, ati.ati_data_25,"
|
|
. "stk.stk_name, stk.stk_hsno, stk.ad_id, ad.ad_street, ad.ad_zipcode, ad.ad_city, ad.ad_country";
|
|
endif;
|
|
$whereClauseArray = array();
|
|
if ($atId != "" && $atId > "0") :
|
|
$whereClauseArray[] = " ati.at_id = '" . $atId . "' ";
|
|
endif;
|
|
if ($stkId != "" && $stkId > "0") :
|
|
if ($stkTreeMode == "1") :
|
|
$whereClauseArray[] = " (stk.stk_id = '" . $stkId . "' OR stk.stk_path LIKE '%//" . $stkId . "//%' ) ";
|
|
else :
|
|
$whereClauseArray[] = " ati.stk_id = '" . $stkId . "' ";
|
|
endif;
|
|
endif;
|
|
if ($serialNo != "") : $whereClauseArray[] = " ati.ati_serialno = '" . $serialNo . "' "; endif;
|
|
if ($whereClause != "") : $whereClauseArray[] = $whereClause; endif;
|
|
$whereClause = "";
|
|
if (count($whereClauseArray) > 0) : $whereClause = " AND " . implode(" AND ", $whereClauseArray) . " "; endif;
|
|
if ($orderByClause != "") : $orderByClause = " ORDER BY " . $orderByClause; endif;
|
|
if ($limit != "") : $limit = " LIMIT 0," . $limit; endif;
|
|
$retStmt = "SELECT " . $fieldClause
|
|
. " FROM articleitem AS ati, article AS at, stock AS stk LEFT JOIN address AS ad ON stk.ad_id = ad.ad_id"
|
|
. " WHERE ati.at_id = at.at_id AND ati.stk_id = stk.stk_id" . $whereClause . $orderByClause . $limit;
|
|
return $retStmt;
|
|
}
|
|
|
|
// Returns the statement to get article items according to a (sub)string to search for a serial number or a special data fields
|
|
function getStmtStockArticleItemSearch ($whereClause = "", $orderByClause = "", $limit = "", $fieldClause = "") {
|
|
$retStmt = "";
|
|
if (true) :
|
|
if ($fieldClause == "") : $fieldClause = "ati.at_id, ati.stk_id"; endif;
|
|
if ($orderByClause == "") : $orderByClause = "stk.stk_name, ati.ati_serialno"; endif;
|
|
if ($limit == "") : $limit = "1000"; endif;
|
|
if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;
|
|
$retStmt = "SELECT " . $fieldClause
|
|
. " FROM article AS at, articleitem AS ati, stock AS stk"
|
|
. " WHERE ati.at_id = at.at_id AND ati.stk_id = stk.stk_id" . $whereClause
|
|
. " ORDER BY " . $orderByClause
|
|
. " LIMIT 0," . $limit;
|
|
endif;
|
|
return $retStmt;
|
|
}
|
|
|
|
function getStmtStockArticleItemSearch_NEW ($stkId, $whereClause = "", $orderByClause = "") {
|
|
global $md_id, $hq_id;
|
|
$retStmt = "";
|
|
if ($orderByClause == "") : $orderByClause = "stk.stk_name, at.at_name"; endif;
|
|
if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;
|
|
$whereClauseTreeMode = " (stk.stk_id = '" . $stkId . "' OR stk.stk_path LIKE '%//" . $stkId . "//%' ) ";
|
|
$retStmt = "SELECT stk.stk_id, stk.stk_name, at.at_id, at.at_eid, at.at_name, at.at_serialno, stkat.stkat_maxquantity, stkat.stkat_quantity, stkat.stkat_itemquantity," .
|
|
" ati.ati_serialno, ati.stk_id, stk.stk_name," .
|
|
" ati.ati_data_01, ati.ati_data_02, ati.ati_data_03, ati.ati_data_04, ati.ati_data_05," .
|
|
" ati.ati_data_06, ati.ati_data_07, ati.ati_data_08, ati.ati_data_09, ati.ati_data_10," .
|
|
" ati.ati_data_11, ati.ati_data_12, ati.ati_data_13, ati.ati_data_14, ati.ati_data_15," .
|
|
" ati.ati_data_16, ati.ati_data_17, ati.ati_data_18, ati.ati_data_19, ati.ati_data_20," .
|
|
" ati.ati_data_21, ati.ati_data_22, ati.ati_data_23, ati.ati_data_24, ati.ati_data_25" .
|
|
" FROM stock AS stk, article AS at, stockarticle AS stkat" .
|
|
" LEFT JOIN articlegroupitem AS atgi ON stkat.at_id = atgi.at_id AND atgi.md_id = '" . $md_id . "' AND atgi.hq_id = '" . $hq_id . "'" .
|
|
" LEFT JOIN articleitem AS ati ON ati.at_id = at.at_id AND ati.stk_id = stk.stk_id" .
|
|
" WHERE stk.stk_id = stkat.stk_id AND stkat.at_id = at.at_id AND " . $whereClauseTreeMode . " " . $whereClause .
|
|
" ORDER BY " . $orderByClause;
|
|
return $retStmt;
|
|
}
|
|
|
|
// Returns the statement to get articles WITH item data (inventory)
|
|
function getStmtStockInventory ($stkId, $treeMode = "0", $whereClause = "", $orderByClause = "") {
|
|
global $md_id, $hq_id;
|
|
$retStmt = "";
|
|
if ($stkId != "") :
|
|
if ($orderByClause == "") : $orderByClause = "stk.stk_name, at.at_name"; endif;
|
|
if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;
|
|
$whereClauseTreeMode = " stk.stk_id = '" . $stkId . "' ";
|
|
if ($treeMode == "1") :
|
|
$whereClauseTreeMode = " (stk.stk_id = '" . $stkId . "' OR stk.stk_path LIKE '%//" . $stkId . "//%' ) ";
|
|
endif;
|
|
|
|
$sqlquery = "SELECT stk.stk_id, stk.stk_name, CONCAT(stkat.at_id, '|', at.at_eid, '|', at.at_serialno, '|', stk.stk_id) AS at_x, at.at_eid, at.at_name, at.at_match, at.at_serialno, stkat.stkat_maxquantity, stkat.stkat_quantity, stkat.stkat_itemquantity, CEILING(stkat.stkat_itemquantity / at.at_bundlequantity) AS bundlequantity, atg.atg_name," .
|
|
" ' ' AS ati_serialno," .
|
|
" ' ' AS ati_data_01, ' ' AS ati_data_02, ' ' AS ati_data_03, ' ' AS ati_data_04, ' ' AS ati_data_05," .
|
|
" ' ' AS ati_data_06, ' ' AS ati_data_07, ' ' AS ati_data_08, ' ' AS ati_data_09, ' ' AS ati_data_10," .
|
|
" ' ' AS ati_data_11, ' ' AS ati_data_12, ' ' AS ati_data_13, ' ' AS ati_data_14, ' ' AS ati_data_15," .
|
|
" ' ' AS ati_data_16, ' ' AS ati_data_17, ' ' AS ati_data_18, ' ' AS ati_data_19, ' ' AS ati_data_20," .
|
|
" ' ' AS ati_data_21, ' ' AS ati_data_22, ' ' AS ati_data_23, ' ' AS ati_data_24, ' ' AS ati_data_25" .
|
|
" FROM stock AS stk, article AS at, stockarticle AS stkat" .
|
|
" LEFT JOIN articlegroupitem AS atgi ON stkat.at_id = atgi.at_id AND atgi.md_id = '" . $md_id . "' AND atgi.hq_id = '" . $hq_id . "'" .
|
|
" LEFT JOIN articlegroup AS atg ON atg.md_id = '" . $md_id . "' AND atg.atg_id = atgi.atg_id" .
|
|
" WHERE stk.stk_id = stkat.stk_id AND stkat.at_id = at.at_id AND at.at_serialno = '0' AND " . $whereClauseTreeMode . " " . $whereClause .
|
|
" ORDER BY " . $orderByClause;
|
|
|
|
$sqlquery2 = "SELECT stk.stk_id, stk.stk_name, CONCAT(stkat.at_id, '|', at.at_eid, '|', at.at_serialno, '|', stk.stk_id) AS at_x, at.at_eid, at.at_name, at.at_match, at.at_serialno, stkat.stkat_maxquantity, stkat.stkat_quantity, '1' AS stkat_itemquantity, '1' AS bundlequantity, atg.atg_name," .
|
|
" ati.ati_serialno," .
|
|
" ati.ati_data_01, ati.ati_data_02, ati.ati_data_03, ati.ati_data_04, ati.ati_data_05," .
|
|
" ati.ati_data_06, ati.ati_data_07, ati.ati_data_08, ati.ati_data_09, ati.ati_data_10," .
|
|
" ati.ati_data_11, ati.ati_data_12, ati.ati_data_13, ati.ati_data_14, ati.ati_data_15," .
|
|
" ati.ati_data_16, ati.ati_data_17, ati.ati_data_18, ati.ati_data_19, ati.ati_data_20," .
|
|
" ati.ati_data_21, ati.ati_data_22, ati.ati_data_23, ati.ati_data_24, ati.ati_data_25" .
|
|
" FROM stock AS stk, article AS at, articleitem AS ati, stockarticle AS stkat" .
|
|
" LEFT JOIN articlegroupitem AS atgi ON stkat.at_id = atgi.at_id AND atgi.md_id = '" . $md_id . "' AND atgi.hq_id = '" . $hq_id . "'" .
|
|
" LEFT JOIN articlegroup AS atg ON atg.md_id = '" . $md_id . "' AND atg.atg_id = atgi.atg_id" .
|
|
" WHERE stk.stk_id = stkat.stk_id AND stkat.at_id = at.at_id AND at.at_serialno = '1' AND ati.at_id = at.at_id AND ati.stk_id = stk.stk_id AND " . $whereClauseTreeMode . " " . $whereClause .
|
|
" ORDER BY " . $orderByClause;
|
|
|
|
$retStmt = "(" . $sqlquery . ") UNION (" . $sqlquery2 . ")";
|
|
endif;
|
|
return $retStmt;
|
|
}
|
|
?>
|