873 lines
48 KiB
PHP
873 lines
48 KiB
PHP
<?php
|
|
/*=======================================================================
|
|
*
|
|
* inc_scan_events.inc.php
|
|
*
|
|
* Autor: Carsten Annacker
|
|
*
|
|
=======================================================================*/
|
|
|
|
include_once("../include/inc_job.inc.php");
|
|
$test = false;
|
|
|
|
$jb_statusArr[0] = "UN-Dispo";
|
|
$jb_statusArr[1] = "angenommen";
|
|
$jb_statusArr[2] = "erledigt";
|
|
$jb_statusArr[9] = "Disposition";
|
|
$eventMsgArray[0]["102"] = "Handed over to TSP"; // Übernahme IKEA
|
|
$eventMsgArray[0]["201"] = "Received at HUB"; // Einlagern LSC
|
|
$eventMsgArray[0]["202"] = "Loaded on delivery truck"; // Auslagern LSC
|
|
$eventMsgArray[0]["301"] = "Order delivered"; // Erledigt // Lieferung
|
|
$eventMsgArray[0]["304"] = "Failed delivery"; // keine Anlieferung möglich
|
|
$eventMsgArray[0]["351"] = "Picked up at customer"; // Abholung erfolgte
|
|
$eventMsgArray[0]["354"] = "Failed Pick-up"; // keine Abholung möglich
|
|
$eventMsgArray[0]["359"] = "Returned to Store Pick-Up"; // Übergabe an IKEA nach Abholung
|
|
$eventMsgArray[0]["408"] = "Returned to Store"; // Übergabe an IKEA
|
|
$eventMsgArray[1]["501"] = "Planned"; // "Order is planned";
|
|
$eventMsgArray[1]["502"] = "LockedForChange"; // "Order is locked for Change";
|
|
$eventMsgArray[1]["301"] = "Completed"; // "Order completed" // Erledigt, Montage
|
|
$eventMsgArray[1]["304"] = "Failed"; // "Failed" // Keine Montage möglich, Montage
|
|
$bo_stateArr["-1"] = "wartend";
|
|
$bo_stateArr["0"] = "gesendet";
|
|
$bo_stateArr["1"] = "zu senden";
|
|
$bo_stateArr["2"] = "ungültig";
|
|
$bo_stateArr["3"] = "Ohne Auftrag";
|
|
$bo_stateArr["4"] = "Auftrag nicht gefunden";
|
|
$bo_stateArr["5"] = "nicht gesendet";
|
|
$bo_stateArr["6"] = "falsch";
|
|
$bo_stateArr["7"] = "geändert";
|
|
$bo_stateArr["8"] = "doppelt";
|
|
$bo_stateArr["9"] = "gesendet";
|
|
$bo_stateArr["10"] = "unvollständig";
|
|
|
|
// LCD - indirekte Lieferung (nicht taggleich)
|
|
// Erster Eintrag ist Default
|
|
$eventsExpected[0][] = array("102", "201", "202", "301");
|
|
$eventsExpected[0][] = array("102", "201", "202", "304", "408");
|
|
$eventsExpected[0][] = array("102", "201", "202", "304", "201", "408");
|
|
|
|
$eventsExpected[0][] = array("102", "201", "202", "304", "201", "202", "301");
|
|
$eventsExpected[0][] = array("102", "201", "202", "304", "201", "202", "304", "201", "408");
|
|
|
|
$eventsExpected[0][] = array("102", "201", "202", "304", "201", "202", "304", "201", "202", "301");
|
|
$eventsExpected[0][] = array("102", "201", "202", "304", "201", "202", "304", "201", "202", "304", "201", "408");
|
|
|
|
$eventsExpected[0][] = array("102", "201", "202", "304", "201", "202", "304", "201", "202", "304", "201", "202", "301");
|
|
$eventsExpected[0][] = array("102", "201", "202", "304", "201", "202", "304", "201", "202", "304", "201", "202", "304", "201", "408");
|
|
|
|
$eventsExpected[0][] = array("102", "201", "202", "304", "201", "202", "304", "201", "202", "304", "201", "202", "304", "201", "202", "301");
|
|
$eventsExpected[0][] = array("102", "201", "202", "304", "201", "202", "304", "201", "202", "304", "201", "202", "304", "201", "202", "304", "201", "408");
|
|
|
|
$eventsExpected[0][] = array("102", "201", "408");
|
|
|
|
// LCD - direkte Lieferung (taggleich)
|
|
// Erster Eintrag ist Default
|
|
$eventsExpected[2][] = array("102", "301");
|
|
$eventsExpected[2][] = array("102", "304", "408");
|
|
$eventsExpected[2][] = array("102", "304", "201", "408");
|
|
|
|
$eventsExpected[2][] = array("102", "304", "201", "202", "301");
|
|
$eventsExpected[2][] = array("102", "304", "201", "202", "304", "408");
|
|
$eventsExpected[2][] = array("102", "304", "201", "202", "304", "201", "408");
|
|
|
|
$eventsExpected[2][] = array("102", "304", "201", "202", "304", "201", "202", "301");
|
|
$eventsExpected[2][] = array("102", "304", "201", "202", "304", "201", "202", "304", "408");
|
|
$eventsExpected[2][] = array("102", "304", "201", "202", "304", "201", "202", "304", "201", "408");
|
|
|
|
$eventsExpected[2][] = array("102", "304", "201", "202", "304", "201", "202", "304", "201", "202", "301");
|
|
$eventsExpected[2][] = array("102", "304", "201", "202", "304", "201", "202", "304", "201", "202", "304", "408");
|
|
$eventsExpected[2][] = array("102", "304", "201", "202", "304", "201", "202", "304", "201", "202", "304", "201", "408");
|
|
|
|
$eventsExpected[2][] = array("102", "304", "201", "202", "304", "201", "202", "304", "201", "202", "304", "201", "202", "301");
|
|
$eventsExpected[2][] = array("102", "304", "201", "202", "304", "201", "202", "304", "201", "202", "304", "201", "202", "304", "408");
|
|
$eventsExpected[2][] = array("102", "304", "201", "202", "304", "201", "202", "304", "201", "202", "304", "201", "202", "304", "201", "408");
|
|
|
|
// LCD - Abholung
|
|
// Erster Eintrag ist Default
|
|
$eventsExpected[3][] = array("102", "351", "201", "359");
|
|
$eventsExpected[3][] = array("102", "351", "359");
|
|
$eventsExpected[3][] = array("102", "354");
|
|
|
|
// OS
|
|
// Erster Eintrag ist Default
|
|
$eventsExpected[1][] = array("501", "502", "301");
|
|
$eventsExpected[1][] = array("501", "502", "304");
|
|
|
|
//$eventFollower[0]["0"] = array("102");
|
|
//$eventFollower[0]["102"] = array("201");
|
|
//$eventFollower[0]["201"] = array("408", "202");
|
|
//$eventFollower[0]["408"] = array();
|
|
//$eventFollower[0]["202"] = array("301", "304");
|
|
//$eventFollower[0]["301"] = array();
|
|
//$eventFollower[0]["304"] = array("408", "201");
|
|
//$eventFollower[0]["201"] = array("408");
|
|
//
|
|
//$eventFollower[2]["0"] = array("102");
|
|
//$eventFollower[2]["102"] = array("301", "304");
|
|
//$eventFollower[2]["301"] = array();
|
|
//$eventFollower[2]["304"] = array("408", "201");
|
|
//$eventFollower[2]["408"] = array();
|
|
//$eventFollower[2]["201"] = array("408");
|
|
//
|
|
//$eventFollower[3]["0"] = array("351", "359");
|
|
//$eventFollower[3]["351"] = array("201", "359");
|
|
//$eventFollower[3]["359"] = array();
|
|
//$eventFollower[3]["201"] = array("359");
|
|
//
|
|
//$eventFollower[1]["0"] = array("501");
|
|
//$eventFollower[1]["501"] = array("502");
|
|
//$eventFollower[1]["502"] = array("301", "304");
|
|
//$eventFollower[1]["301"] = array();
|
|
//$eventFollower[1]["304"] = array();
|
|
|
|
$log_file_name = "inc_scan_events";
|
|
|
|
// MC 2020-04-19
|
|
$phpVersion = substr(phpversion(), 0, 3);
|
|
|
|
if ($phpVersion >= "8.0") :
|
|
if (!is_object($db)) :
|
|
$dbCreds = array("dbHost" => $dbhostOnly, "dbUser" => $dblogin, "dbPassword" => $dbpassword, "dbName" => $dbname, "dbPort" => $dbport, "dbTable" => "user");
|
|
$mysql_db_default = new DB($dbCreds);
|
|
else :
|
|
$mysql_db_default = $db;
|
|
endif;
|
|
$mysql_db_default->dbQ('SET NAMES latin1');
|
|
$mysql_db_default->dbQ('USE phoenix');
|
|
elseif ($phpVersion >= "7.0") :
|
|
if (!is_object($db)) :
|
|
require "../PEAR/DB.php"; // DB.php is wrapper for MDB2.php (!!!!)
|
|
$mysql_db_default =& MDB2::connect($dsn, $dbOptions);
|
|
if (DB::isError($db)) :
|
|
die ("$PHP_SELF: " . $db->getMessage());
|
|
endif;
|
|
else :
|
|
$mysql_db_default = $db;
|
|
endif;
|
|
$mysql_db_default->setFetchMode(MDB2_FETCHMODE_ASSOC);
|
|
$mysql_db_default->query('SET NAMES latin1');
|
|
$mysql_db_default->query('USE phoenix');
|
|
// $mysql_db_default->loadModule('Extended', null, false);
|
|
else :
|
|
$mysql_db_default = mysql_connect($dbhost, $dblogin, $dbpassword) or die (mysql_error());
|
|
mysql_select_db("phoenix", $mysql_db_default) or die (mysql_error($mysql_db_default));
|
|
mysql_query('SET NAMES latin1', $mysql_db_default) or die ('SET NAMES latin1' . ": " . mysql_error($mysql_db_default));
|
|
endif;
|
|
|
|
//writeLog_order_status("[" . date("Y-m-d H:i:s") . "] [\$mysql_db_default = '" . $mysql_db_default . "']");
|
|
|
|
//$mysql_db_b2b = mysql_connect("172.16.0.123:3651", $dblogin, $dbpassword) or die (mysql_error());
|
|
//mysql_select_db("phoenix", $mysql_db_b2b) or die (mysql_error($mysql_db_b2b));
|
|
//mysql_query('SET NAMES latin1', $mysql_db_b2b) or die ('SET NAMES latin1' . ": " . mysql_error($mysql_db_b2b));
|
|
//writeLog_order_status("[" . date("Y-m-d H:i:s") . "] [\$mysql_db_b2b = '" . $mysql_db_b2b . "']");
|
|
$mysql_db_b2b = $mysql_db_default;
|
|
|
|
$cs_id_relatedArr = array(
|
|
"1" => "1",
|
|
"2" => "2",
|
|
"17051" => "17093",
|
|
"3506" => "3534",
|
|
"3512" => "3540"
|
|
);
|
|
|
|
$csc_id_relatedArr = array(
|
|
"1" => "Schnelsen",
|
|
"2" => "Moorfleet",
|
|
"17051" => "Altona",
|
|
"3506" => "LuBu",
|
|
"3512" => "SiFi"
|
|
);
|
|
|
|
//$row = get_first_row("SELECT usr_firstname, usr_name, usr_account FROM phoenix.user WHERE usr_id = " . $usr_id);
|
|
//$javascript_usr_name = $row["usr_firstname"] . " " . $row["usr_name"] . " (" . $row["usr_account"] . ")";
|
|
|
|
// getScanEventsHtml() is the interface for the usage of this include:
|
|
//admin/jb_detail.php: $scanEventsHtml = getScanEventsHtml($job_id);
|
|
//include/ajaxReqScanEvent.php: getScanEventsHtml("$jb_id,$jb_id", array(), true)
|
|
//tools/scan_events.php: $content = getScanEventsHtml($jb_id, $dates, false, $f_mode, $f_no_OS);
|
|
function getScanEventsHtml($jb_id, $dates = array(), $bodyonly = false, $mode = "0", $no_OS = "")
|
|
{
|
|
global $usr_id;
|
|
|
|
$internal_txt = "";
|
|
//// 21.06.2016: Die internen Events sollen (nur in den Auftragsdetails) ebenfalls dargestellt werden
|
|
// if (strpos($jb_id, ",") === false) {
|
|
if (!$bodyonly)
|
|
$internal_txt = get_scan_events_internal($jb_id, strpos($jb_id, ",") === false);
|
|
// }
|
|
|
|
$csv_txt = get_scan_events($jb_id, true, $dates, $mode, $no_OS);
|
|
if (!$bodyonly) {
|
|
$csv_txt = "<table border=0 cellspacing=0 cellpadding=4 vspace=0 hspace=0 name=\"eventTable\">\n<tr><td>" .
|
|
str_replace(";", "</td><td align=left>", $csv_txt) .
|
|
"\n</table>\n";
|
|
} else {
|
|
$csv_txt = "<tr><td>" .
|
|
str_replace("\n", "", str_replace(";", "</td><td align=left>", $csv_txt));
|
|
}
|
|
$content .= str_replace(">#ee", " bgcolor=\"#FFD822\">", str_replace(">#ef", " bgcolor=\"#EEC722\">", str_replace(">#fe", " bgcolor=\"#99FF99\">", str_replace(">#ff", " bgcolor=\"#88EE88\">",
|
|
str_replace(">#aa", " bgcolor=\"#DDDDDD\">", str_replace(">#ab", " bgcolor=\"#EEEEEE\">", str_replace(">#ba", " bgcolor=\"#BBBBFF\">", str_replace(">#bb", " bgcolor=\"#CCCCFF\">",
|
|
str_replace(">#cc", " bgcolor=\"#DDBBBB\">", str_replace(">#cd", " bgcolor=\"#DDCCCC\">", str_replace(">#dc", " bgcolor=\"#FF9999\">", str_replace(">#dd", " bgcolor=\"#FFAAAA\">",
|
|
$csv_txt))))))))))));
|
|
return $internal_txt . $content;
|
|
}
|
|
|
|
$edit_cnt = 0;
|
|
function get_scan_events($jb_ids, $out = true, $dates = array(), $mode = "0", $no_OS = "") {
|
|
global $jb_statusArr, $eventMsgArray, $bo_stateArr, $csc_id_relatedArr, $cs_id_relatedArr, $eventsExpected,
|
|
$f_open, $f_disp, $f_taken, $f_finished, $edit_cnt, $test, $hq_id, $mysql_db_b2b, $dbhost_history, $dblogin_history, $dbpassword_history;
|
|
|
|
$job_details = false;
|
|
$selection_date_field = "jb_ordertime";
|
|
$bo_state_clause = "";
|
|
if ($jb_ids != "" && $jb_ids != 0) {
|
|
$where_clause = " AND jb.jb_id IN (" . $jb_ids . ")";
|
|
if (strpos($jb_ids, ",") === false) {
|
|
$job_details = true;
|
|
} else {
|
|
$bo_state_clause = " AND bo_state != 9";
|
|
}
|
|
} else {
|
|
$bo_state_clause = " AND bo_state != 9";
|
|
if ($mode == "0") // $mode == 0 => nach Eingangsdatum selektieren
|
|
$selection_date_field = "jb_createtime";
|
|
$where_clause = " AND (" . $selection_date_field . " BETWEEN '" . $dates[0] . " 00:00:00' AND '" . $dates[1] . " 23:59:59')";
|
|
// bo_state = 9 sind zwar gesendet, aber für die Bearbeitung ausgeblendet
|
|
|
|
$jb_status_clause = "";
|
|
if ($f_open == "1") {
|
|
$jb_status_clause = ",0";
|
|
}
|
|
if ($f_disp == "1") {
|
|
$jb_status_clause .= ",9";
|
|
}
|
|
if ($f_taken == "1") {
|
|
$jb_status_clause .= ",1";
|
|
}
|
|
if ($f_finished == "1") {
|
|
$jb_status_clause .= ",2";
|
|
}
|
|
if ($jb_status_clause == "") {
|
|
$f_open = $f_disp = $f_taken = $f_finished = "1";
|
|
$where_clause .= " AND jb_status IN (0,1,2,9)";
|
|
} else {
|
|
$where_clause .= " AND jb_status IN (" . substr($jb_status_clause, 1) . ")";
|
|
}
|
|
}
|
|
|
|
//echo "|\$no_OS = $no_OS|";
|
|
if ($no_OS == "1")
|
|
$where_clause .= " AND NOT (jb.jb_service IN (6, 18))";
|
|
if (isset($hq_id) && $hq_id != "")
|
|
$where_clause .= " AND jb.hq_id = " . $hq_id;
|
|
|
|
$ret_val = "";
|
|
if($out) {
|
|
// $cC = "#dc"; $cC2 = "#dd";
|
|
// $cC = "#aa"; $cC2 = "#ab";
|
|
$cC = "#ba"; $cC2 = "#bb";
|
|
$ret_val = (!$job_details
|
|
? $cC . "Auftragsnr." . ";$cC2" . "Barcode" . ";$cC" . "Markt" . ";$cC2" . "Eingang" . ";$cC" . "Ausführung" . ";$cC2" . "Erledigung" . ";$cC" . "Fahrer" . ";$cC2" . "Status" . ";" . ";$cC" . "Scan-Event" . ";$cC2" . "Event-Zeit" . ";$cC" . "Vorname Name (Login)" . ";$cC2" . "Event-Status" . "</td></tr>\n<tr><td></td></tr>\n"
|
|
: ";" . $cC . "Scan-Event" . ";$cC2" . "Event-Zeit" . ";$cC" . "Vorname Name (Login)" . ";$cC2" . "Event-Status" . "</td></tr>\n");
|
|
}
|
|
|
|
writeLog_order_status("[" . date("Y-m-d H:i:s") . "] collecting barcodes and other data for jb_ids = " . $jb_ids);
|
|
$sql_query =
|
|
// "SELECT bo_obj_data, csc_id_related, jb.jb_id, gdc_content, jb_ordertime, jb_finishtime, jb_status, cr_sid, bo_ext_id2, usr_account, usr_firstname, usr_name, bo_createtime, bo_state, jb_service, bo_id, jb_freetext_1" .
|
|
// " FROM phoenix.job AS jb, phoenix.tour AS tr, phoenix.genericdatacontainer AS gdc, phoenix_log.b2b_objects" .
|
|
// " LEFT JOIN phoenix.user AS usr ON usr.usr_id = bo_ext_id2" .
|
|
// " WHERE bo_type = 300 AND bo_obj_data LIKE CONCAT(BINARY tr_commission_no, '|%') AND tr_commission_no != '' AND jb.jb_id = tr.jb_id AND tr_sort = 2 AND gdc_obj_id = jb.jb_id AND gdc_gen_fieldname = 'acquisitiontime'" .
|
|
// $where_clause . // "AND tr_commission_no = '629154988'" .
|
|
// " ORDER BY " . $selection_date_field . ", jb_id, bo_createtime, bo_id";
|
|
|
|
// "SELECT bo_obj_data, csc_id_related, jb.jb_id, gdc_content, jb_ordertime, jb_finishtime, jb_status, cr_sid, bo_ext_id2, usr_account, usr_firstname, usr_name, bo_createtime, bo_state, jb_service, bo_id, jb_freetext_1, tr_commission_no" .
|
|
// " FROM phoenix.job AS jb, phoenix.genericdatacontainer AS gdc, phoenix.tour AS tr" .
|
|
// " LEFT JOIN phoenix_log.b2b_objects ON bo_type = 300 AND bo_obj_data LIKE CONCAT(BINARY tr_commission_no, '|%') AND tr_commission_no != '' " . $bo_state_clause .
|
|
// " LEFT JOIN phoenix.user AS usr ON usr.usr_id = bo_ext_id2" .
|
|
// " WHERE jb_storno IS NULL " . /* AND NOT (jb_service IN (6, 18)) */ " AND jb.jb_id = tr.jb_id AND tr_sort = 2 AND gdc_obj_id = jb.jb_id AND gdc_gen_fieldname = 'acquisitiontime'" .
|
|
// $where_clause . // " AND tr_commission_no IN ('HA000085544', 'HA000078898') " .
|
|
// " ORDER BY " . $selection_date_field . ", jb_id, bo_createtime, bo_id";
|
|
|
|
"SELECT csc_id_related, jb.jb_id, gdc_content, jb_ordertime, jb_finishtime, jb_status, cr_sid, jb_service, jb_freetext_1, tr_commission_no" .
|
|
" FROM phoenix.job AS jb, phoenix.genericdatacontainer AS gdc, phoenix.tour AS tr" .
|
|
" WHERE jb_storno IS NULL " . /* AND NOT (jb_service IN (6, 18)) */ " AND jb.jb_id = tr.jb_id AND tr_sort = 2 AND gdc_obj_id = jb.jb_id AND gdc_gen_fieldname = 'acquisitiontime'" .
|
|
$where_clause . // " AND tr_commission_no IN ('HA000085544', 'HA000078898') " .
|
|
" ORDER BY " . $selection_date_field . ", jb_id";
|
|
|
|
//echo $sql_query;
|
|
//writeLog_order_status($sql_query);
|
|
$res = exec_query($sql_query, $mysql_db_b2b, false);
|
|
$prev_jb_id = "";
|
|
$prev_event = "";
|
|
$event_cnt = 0;
|
|
$event_501 = false;
|
|
$event_502 = false;
|
|
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
|
|
// erst das "echte" Eingangsdatum finden
|
|
$jb_storno = $row["jb_id"];
|
|
do {
|
|
$last_jb_storno = $jb_storno;
|
|
$jb_storno = get_first("SELECT gdc_content FROM genericdatacontainer WHERE gdc_gen_fieldname = 'jb_storno' AND gdc_obj_id = " . $jb_storno);
|
|
} while ($jb_storno != "");
|
|
$jb_incoming_date = $row["gdc_content"];
|
|
if ($last_jb_storno != $row["jb_id"])
|
|
$jb_incoming_date = get_first("SELECT gdc_content FROM genericdatacontainer WHERE gdc_gen_fieldname = 'acquisitiontime' AND gdc_obj_id = " . $last_jb_storno);
|
|
|
|
writeLog_order_status("[" . date("Y-m-d H:i:s") . "] Barcode '" . $row["tr_commission_no"] . ": checking events for jb_id = " . $row["jb_id"]);
|
|
$sql_query =
|
|
"SELECT bo_obj_data, bo_ext_id2, bo_createtime, bo_state, bo_id" .
|
|
" FROM phoenix_log.b2b_objects WHERE bo_type = 300 AND bo_obj_data LIKE '" . $row["tr_commission_no"] . "|%' " . $bo_state_clause .
|
|
" ORDER BY bo_createtime, bo_id";
|
|
//echo $sql_query;
|
|
$res1 = exec_query($sql_query, $mysql_db_b2b, true);
|
|
// Wenn nichts gefunden, dann in der Historie suchen (nur wenn job_details)
|
|
if (mysql_num_rows($res1) == 0 && $job_details) {
|
|
$mysql_db_b2b_history = mysql_connect($dbhost_history, $dblogin_history, $dbpassword_history) or die (mysql_error());
|
|
mysql_select_db("phoenix", $mysql_db_b2b_history) or die (mysql_error($mysql_db_b2b_history));
|
|
mysql_query('SET NAMES latin1', $mysql_db_b2b_history) or die ('SET NAMES latin1' . ": " . mysql_error($mysql_db_b2b_history));
|
|
writeLog_order_status("[" . date("Y-m-d H:i:s") . "] [\$mysql_db_b2b_history = '" . $mysql_db_b2b_history . "']");
|
|
$res1 = exec_query(str_replace("b2b_objects", "b2b_objects_2015", $sql_query), $mysql_db_b2b_history, true);
|
|
}
|
|
// Wenn immer noch nichts gefunden, dann einen leeren Eintrag generieren, damit die nachfolgende Schleife einmal durchlaufen wird
|
|
if (mysql_num_rows($res1) == 0) {
|
|
$sql_query =
|
|
"SELECT NULL AS bo_obj_data, NULL AS bo_ext_id2, NULL AS bo_createtime, NULL AS bo_state, NULL AS bo_id";
|
|
$res1 = exec_query($sql_query, $mysql_db_b2b);
|
|
}
|
|
|
|
while ($row1 = mysql_fetch_array($res1, MYSQL_ASSOC)):
|
|
$row["bo_obj_data"] = $row1["bo_obj_data"];
|
|
$row["bo_ext_id2"] = $row1["bo_ext_id2"];
|
|
$row["bo_createtime"] = $row1["bo_createtime"];
|
|
$row["bo_state"] = $row1["bo_state"];
|
|
$row["bo_id"] = $row1["bo_id"];
|
|
if (!is_null($row1["bo_ext_id2"])):
|
|
$tmp_row = get_first_row("SELECT usr_account, usr_firstname, usr_name FROM user WHERE usr_id = " . $row1["bo_ext_id2"]);
|
|
$row["usr_account"] = $tmp_row[0];
|
|
$row["usr_firstname"] = $tmp_row[1];
|
|
$row["usr_name"] = $tmp_row[2];
|
|
endif;
|
|
|
|
list ($sequence_no, $event_no) = explode("|", $row["bo_obj_data"]);
|
|
// wenn Auftrag ohne jegliche Events (durch LEFT JOIN hier vorhanden), dann die Nummer aus der tr_commission_no nehmen
|
|
if ($sequence_no == "")
|
|
$sequence_no = $row["tr_commission_no"];
|
|
|
|
// Ereignis nur dann zu dem aktuellen Auftrag zählen, wenn es leer ist oder mindestens vom selben Tage stammt;
|
|
// anders ist es nicht möglich, Ereignisse zu Nachfolgeaufträge mit der selben Shipment-/Ordernummer von vorherigen zu unterscheiden;
|
|
// Es wird nur der Tag genommen, weil theroetisch ein "Handed over to TSP" kurz vor dem Auftragseingang sein könnte, nicht aber einen Tag vorher
|
|
// if (strtotime(substr($row["bo_createtime"], 0, 10)) >= strtotime(substr($jb_incoming_date, 0, 10))) {
|
|
if ($prev_jb_id != $row["jb_id"]) {
|
|
// Restliche Events des vorherigen Auftrages
|
|
if ($out && !$job_details && $prev_jb_id != "")
|
|
$ret_val .= missing_events($event_cnt, $jb_service_flag, $jb_event_seq_flag, $event_seqno, $cC, $cC2, $prev_sequence_no, $prev_event, $prev_jb_id, $jb_incoming_date);
|
|
// Neuer Auftrag - Auftragsdetails zeigen
|
|
if ($out && !$job_details && $row["jb_status"] == 2) {
|
|
$cC = "#ba"; $cC2 = "#bb";
|
|
} elseif($out && !$job_details) {
|
|
$cC = "#dc"; $cC2 = "#dd";
|
|
}
|
|
if($out && !$job_details) {
|
|
$ret_val .= /* "<div id=\"" . $sequence_no . "\" name=\"" . $sequence_no . "\">" . */ "<tr><td>";
|
|
if ($row["jb_finishtime"] == "0000-00-00 00:00:00")
|
|
$row["jb_finishtime"] = "";
|
|
$ret_val .= $cC .
|
|
"<a href=\"javascript:popupWindow('../admin/jb_detail.php?job_id=" . $row["jb_id"] . "','Auftrag','scrollbars=yes')\">" . $row["jb_id"]. "</a>" .
|
|
";$cC2" . $sequence_no .
|
|
";$cC" . $csc_id_relatedArr[$row["csc_id_related"]] .
|
|
";$cC2" . formDateTime($jb_incoming_date) . ";$cC" . $row["jb_freetext_1"] . ";$cC2" . formDateTime($row["jb_finishtime"]) . ";$cC" . $row["cr_sid"] . ";$cC2 " . $jb_statusArr[$row["jb_status"]] . ";";
|
|
$colCnt = 0;
|
|
} elseif ($job_details) {
|
|
$ret_val .= "<tr><td>";
|
|
}
|
|
$prev_jb_id = $row["jb_id"];
|
|
$prev_sequence_no = $sequence_no;
|
|
$prev_event = "";
|
|
$event_cnt = 0;
|
|
$event_seqno = 0;
|
|
$event_posno = 0;
|
|
$edit_cnt = 0;
|
|
$tmp_chain = array();
|
|
$event_501 = false;
|
|
$event_502 = false;
|
|
$new_ok_state = 1;
|
|
} else {
|
|
// Keine Auftragsdetails zeigen => Leerzeile
|
|
if($out && !$job_details) {
|
|
$ret_val .= ";;;;;;;;";
|
|
}
|
|
}
|
|
|
|
// Details des Ereignisses
|
|
if (($event_no != "501" || !$event_501) && ($event_no != "502" || !$event_502)) {
|
|
|
|
// $jb_service_flag für $eventMsgArray (0 = LCD, 1 = OS)
|
|
$jb_service_flag = ($row["jb_service"] != 6 && $row["jb_service"] != 18 ? 0 : 1);
|
|
|
|
// $jb_event_seq_flag für $eventsExpected (0 = Indirekt, 1 = OS, 2 = Direkt, 3 = Abholung)
|
|
$jb_event_seq_flag = $jb_service_flag;
|
|
|
|
if ($jb_event_seq_flag == 0 && formDate(substr($jb_incoming_date, 0, 10)) == substr($row["jb_freetext_1"], 0, 10))
|
|
$jb_event_seq_flag = 2; // Direkt (taggleich)
|
|
if ($row["jb_service"] == 128 || $row["jb_service"] == 384)
|
|
$jb_event_seq_flag = 3; // Abholung
|
|
|
|
//writeLog_order_status("\$jb_event_seq_flag = $jb_event_seq_flag");
|
|
// In den zulässigen Event-Ketten schauen, wo es ab der aktuellen Stelle passt
|
|
$event_ok = false;
|
|
for($i = 0; $i < count($eventsExpected[$jb_event_seq_flag]); $i++) {
|
|
for ($j = $event_cnt; $j < count($eventsExpected[$jb_event_seq_flag][$i]); $j++) {
|
|
//writeLog_order_status($sequence_no . ": " . "$event_cnt" . ": " . $event_no . ": " . $eventsExpected[$jb_event_seq_flag][$i][$j]);
|
|
if ($eventsExpected[$jb_event_seq_flag][$i][$j] == $event_no) {
|
|
// checken, ob das mit der bisherigen Kette passt
|
|
$chain_ok = true;
|
|
for ($k = 0; $k < count($tmp_chain); $k++) {
|
|
//writeLog_order_status("aktuelle Kettennummer: " . $event_seqno);
|
|
//writeLog_order_status( $tmp_chain[$k] . " != " . $eventsExpected[$jb_event_seq_flag][$i][$k] . "?");
|
|
if ($tmp_chain[$k] != $eventsExpected[$jb_event_seq_flag][$i][$k]) {
|
|
$chain_ok = false;
|
|
break;
|
|
}
|
|
}
|
|
if ($chain_ok) {
|
|
//writeLog_order_status(" [$i][$j] passt");
|
|
$event_seqno = $i;
|
|
$event_posno = $j;
|
|
$event_ok = true;
|
|
$tmp_chain[] = $event_no;
|
|
break 2;
|
|
}
|
|
}
|
|
//writeLog_order_status(" passt nicht");
|
|
}
|
|
}
|
|
//echo "$event_no, $event_ok, \$event_posno=|$event_posno|<br>\n";
|
|
//writeLog_order_status("\$tmp_chain = " . var_export($tmp_chain, true));
|
|
//writeLog_order_status("\$sequence_no = $sequence_no, \$bo_id = " . $row["bo_id"] . ", \$event_no = $event_no, \$event_ok = $event_ok, \$event_posno=|$event_posno|");
|
|
|
|
//echo "<br>\n$sequence_no:" . print_r($eventsExpected[$jb_event_seq_flag][$event_seqno]);
|
|
|
|
if ($row["bo_state"] == 3) {
|
|
// Status wieder auf "zu senden" setzen, wenn vorher "ohne Auftrag" (Auftrag ist ja jetzt da)
|
|
exec_query("UPDATE phoenix_log.b2b_objects SET bo_state = 1 WHERE bo_id = " . $row["bo_id"] . " AND bo_state = 3");
|
|
$row["bo_state"] = get_first("SELECT bo_state FROM phoenix_log.b2b_objects WHERE bo_id = " . $row["bo_id"]);
|
|
}
|
|
|
|
// Event ist erlaubt
|
|
if ($event_ok) {
|
|
// Auffüllen der erwarteten Events
|
|
if ($out && !$job_details) {
|
|
$cCtmp = "#cc";
|
|
$cC2tmp = "#cd";
|
|
}
|
|
for ($i = $event_cnt; $i < $event_posno; $i++) {
|
|
$tmp_event_no = $eventsExpected[$jb_event_seq_flag][$event_seqno][$i];
|
|
if ($out && !$job_details) {
|
|
$ret_val .=
|
|
";" . $cCtmp . $eventMsgArray[$jb_service_flag][$tmp_event_no] . " (" . $tmp_event_no . ")" . ";$cC2tmp" .
|
|
"<input name=\"" . $sequence_no . "_" . $edit_cnt . "\" type=\"text\" size=\"19\" maxlength=\"19\" class=\"input\" value=\"\" " .
|
|
"onchange=\"edit_event('" . $sequence_no . "','" . $edit_cnt++ . "','" . 0 . "','" . $tmp_event_no . "', 0, " . $row["jb_id"] . ", '" . $jb_incoming_date . "')\"" .
|
|
">" .
|
|
";$cCtmp" . " " . " " . " " . " " . " " . "" . ";$cC2tmp" . " " . "</td></tr>\n<tr><td>;;;;;;;;";
|
|
// TOBEDONE: ACHTUNG: der array_splice muss auch dann ausgeführt werden, wenn $out == false ist
|
|
}
|
|
array_splice($tmp_chain, -1, 0, $tmp_event_no);
|
|
//writeLog_order_status("\$tmp_chain = " . var_export($tmp_chain, true));
|
|
// wenn mind. ein Event aufgefüllt werden muss, dann ist die Kette nicht vollständig und dann darf das Event nicht gesendet werden
|
|
// $new_ok_state = 10;
|
|
// "unvollständig" fliegt erst einmal wieder raus
|
|
$new_ok_state = 1;
|
|
}
|
|
//echo $row["bo_state"] . "," . $new_ok_state . "<br>\n";
|
|
if (!($event_no == "301" || $event_no == "304") && $row["bo_state"] != $new_ok_state && $row["bo_state"] != 0 && !$job_details) {
|
|
// Status auf "zu senden" bzw. "unvollständig" setzen, wenn aktueller Status etwas anderes ist
|
|
exec_query("UPDATE phoenix_log.b2b_objects SET bo_state = " . $new_ok_state . " WHERE bo_id = " . $row["bo_id"]);
|
|
$row["bo_state"] = get_first("SELECT bo_state FROM phoenix_log.b2b_objects WHERE bo_id = " . $row["bo_id"]);
|
|
}
|
|
$event_cnt = $event_posno + 1;
|
|
//echo "\$event_cnt=|$event_posno + 1|<br>\n";
|
|
}
|
|
//echo "\$event_cnt=|$event_cnt|<br>\n";
|
|
|
|
if (in_array($row["bo_state"], array(0, 1, 5, -1, 7, 10)) /*&& $prev_event != $event_no*/ && $event_ok) {
|
|
// Gültige Statusse für gültige Events: "-1" = "gestoppt" (Planned/LockedForChange), "0" = "gesendet", "1" = "zu senden", "5" = "nicht gesendet" (OS failed), "7" = "geändert", "10" = unvollständig
|
|
if ($out) {
|
|
$cC = "#aa"; $cC2 = "#ab";
|
|
}
|
|
if ($out && !$job_details && $event_no == "501") {
|
|
$tmp_row = get_first_row("SELECT bo_id, bo_createtime FROM phoenix_log.b2b_objects WHERE bo_type = 300 AND bo_obj_data = '" . $sequence_no . "|501' AND bo_state = 0");
|
|
if ($tmp_row[0] != "") {
|
|
$row["bo_id"] = $tmp_row[0];
|
|
$row["bo_createtime"] = $tmp_row[1];
|
|
$row["bo_state"] = 0;
|
|
}
|
|
}
|
|
} else {
|
|
// ungültige Events: "2" = "ungültig", "3" = "Ohne Auftrag", "4" = "Auftrag nicht gefunden", "6" = "falsch" (wird hier gesetzt, wenn vorher = "1" oder "7");
|
|
if($out) {
|
|
$cC = "#cc"; $cC2 = "#cd";
|
|
}
|
|
// } else {
|
|
if (!($event_no == "301" || $event_no == "304") || $prev_event == $event_no) {
|
|
// wenn Event falsch, dann Status auf "falsch" (nur wenn vorher 1 oder 7, d.h. noch nicht gesendet)
|
|
// dies nur, wenn Event doppelt oder kein Erledigungsevent (diese haben immer Vorrang, wenn sie nicht genau doppelt nacheinander kommen)
|
|
if ($row["bo_id"] != "" && !$job_details) {
|
|
//writeLog_order_status("[" . date("Y-m-d H:i:s") . "] " . "UPDATE phoenix_log.b2b_objects SET bo_state = 6 WHERE bo_id = " . $row["bo_id"] . " AND bo_state IN (1, 7, 8, 10)");
|
|
if (!$test) {
|
|
exec_query("UPDATE phoenix_log.b2b_objects SET bo_state = 6 WHERE bo_id = " . $row["bo_id"] . " AND bo_state IN (1, 7, 8, 10)");
|
|
$row["bo_state"] = get_first("SELECT bo_state FROM phoenix_log.b2b_objects WHERE bo_id = " . $row["bo_id"]);
|
|
} else {
|
|
$row["bo_state"] = 6;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
if ($out && $event_no != "") {
|
|
if ($row["bo_ext_id2"] == 0)
|
|
$row["usr_account"] = "#Sendeprozess#";
|
|
$cCtmp = $cC;
|
|
$cC2tmp = $cC2;
|
|
$outoftime = false;
|
|
if (($event_no == "301" || $event_no == "304") && $row["bo_state"] != 6) {
|
|
$outoftime = true;
|
|
$cCtmp = "#ef";
|
|
$cC2tmp = "#ee";
|
|
if (substr($row["jb_freetext_1"], 0, 10) == formDate(substr($row["bo_createtime"], 0, 10))) {
|
|
$daytimeTimeInterval = getDaytimeTimeInterval($row["bo_createtime"], $cs_id_relatedArr[$row["csc_id_related"]]);
|
|
//echo $row["jb_id"] . ": " . substr($row["jb_freetext_1"], 12) . ", " . $daytimeTimeInterval . "<br>\n";
|
|
if (substr($row["jb_freetext_1"], 12) == $daytimeTimeInterval || (substr($row["jb_freetext_1"], 12, 9) == "ganztägig" && $daytimeTimeInterval != "")) {
|
|
$cCtmp = "#ff";
|
|
$cC2tmp = "#fe";
|
|
$outoftime = false;
|
|
}
|
|
}
|
|
}
|
|
$eventText = $eventMsgArray[$jb_service_flag][$event_no] . " (" . $event_no . ")";
|
|
$select_list = false;
|
|
if (!$job_details && $row["bo_state"] != 0 && $row["bo_state"] != 6 && ($event_no == "301" || $event_no == "304" || $event_no == "351" || $event_no == "354")) {
|
|
$eventText =
|
|
"<select name=\"" . $sequence_no . "_" . $edit_cnt . "_select\" class=\"input\" onchange=\"edit_event('" . $sequence_no . "','" . $edit_cnt++ . "','" . $row['bo_id'] . "','" . $event_no . "', 1, " . $row["jb_id"] . ", '" . $jb_incoming_date . "')\">\n";
|
|
if ($event_no == "301" || $event_no == "304") {
|
|
$eventText .= mk_option("301", $eventMsgArray[$jb_service_flag]["301"] . " (301)", $event_no);
|
|
$eventText .= mk_option("304", $eventMsgArray[$jb_service_flag]["304"] . " (304)", $event_no);
|
|
} elseif ($event_no == "351" || $event_no == "354") {
|
|
$eventText .= mk_option("351", $eventMsgArray[$jb_service_flag]["351"] . " (351)", $event_no);
|
|
$eventText .= mk_option("354", $eventMsgArray[$jb_service_flag]["354"] . " (354)", $event_no);
|
|
}
|
|
$eventText .= "</select>\n";
|
|
$select_list = true;
|
|
}
|
|
$ret_val .=
|
|
";" . $cCtmp .
|
|
(/*$row["bo_state"] != 0 */ $event_no != 501 && $event_no != 502 && !$job_details
|
|
? "<a href=\"javascript:edit_event('" . $sequence_no . "','" . $edit_cnt++ . "','" . $row['bo_id'] . "','" . $event_no . "', 2, " . $row["jb_id"] . ", '" . $jb_incoming_date . "')\">" .
|
|
"<img src=\"../images/trash.jpg\" border=\"0\" height=\"13\" width=\"8\" alt=\"löschen\"></a> "
|
|
: "") .
|
|
$eventText . ";$cC2tmp" .
|
|
($job_details || ((!$outoftime && $row["bo_state"] != 7) || $row["bo_state"] == 0 || $row["bo_state"] == 6)
|
|
? formDateTime($row["bo_createtime"])
|
|
: "<input name=\"" . $sequence_no . "_" . $edit_cnt . "\" type=\"text\" size=\"19\" maxlength=\"19\" class=\"input\" value=\"" . formDateTime($row["bo_createtime"]) . "\" " .
|
|
"onchange=\"edit_event('" . $sequence_no . "','" . $edit_cnt++ . "','" . $row['bo_id'] . "','" . $event_no . "', 0, " . $row["jb_id"] . ", '" . $jb_incoming_date . "')\">") .
|
|
";$cCtmp" . $row["usr_firstname"] . " " . $row["usr_name"] . " (" . $row["usr_account"] . ")" . ";$cC2tmp" . $bo_stateArr[$row["bo_state"]] . "</td></tr>\n<tr><td>";
|
|
// if (!$job_details && ((($outoftime || $row["bo_state"] == 7) && $row["bo_state"] != 0 && $row["bo_state"] != 6) || $select_list))
|
|
// } elseif($out) {
|
|
// $ret_val .=
|
|
// ";;;honk;</td></tr>\n<tr><td>";
|
|
}
|
|
$prev_event = $event_no;
|
|
if ($event_no == "501")
|
|
$event_501 = true;
|
|
if ($event_no == "502")
|
|
$event_502 = true;
|
|
} elseif($out && !$job_details) {
|
|
$ret_val = substr($ret_val, 0, -8);
|
|
}
|
|
//}
|
|
endwhile;
|
|
endwhile;
|
|
mysql_free_result($res);
|
|
if ($out && !$job_details && $prev_jb_id != "") {
|
|
$ret_val .= missing_events($event_cnt, $jb_service_flag, $jb_event_seq_flag, $event_seqno, $cC, $cC2, $sequence_no, $prev_event, $prev_jb_id, $jb_incoming_date);
|
|
}
|
|
return $ret_val;
|
|
}
|
|
|
|
function missing_events($event_cnt, $jb_service_flag, $jb_event_seq_flag, $event_seqno, $cC, $cC2, $sequence_no, $prev_event, $jb_id, $jb_incoming_date) {
|
|
global $eventsExpected, $eventMsgArray, $edit_cnt;
|
|
|
|
$retval = "";
|
|
// Restliche Events des vorherigen Auftrages
|
|
$cCtmp = "#cc";
|
|
$cC2tmp = "#cd";
|
|
//echo "|$event_cnt|" . count($eventsExpected[$jb_event_seq_flag][$event_seqno]) . "|<br>\n";
|
|
for ($i = $event_cnt; $i < count($eventsExpected[$jb_event_seq_flag][$event_seqno]); $i++) {
|
|
$tmp_event_no = $eventsExpected[$jb_event_seq_flag][$event_seqno][$i];
|
|
$retval .=
|
|
($prev_event == "" && $i == $event_cnt ? ";" : ";;;;;;;;;") .
|
|
$cCtmp . $eventMsgArray[$jb_service_flag][$tmp_event_no] . " (" . $tmp_event_no . ")" . ";$cC2tmp" .
|
|
"<input name=\"" . $sequence_no . "_" . $edit_cnt . "\" type=\"text\" size=\"19\" maxlength=\"19\" class=\"input\" value=\"\" " .
|
|
"onchange=\"edit_event('" . $sequence_no . "','" . $edit_cnt++ . "','" . 0 . "','" . $tmp_event_no . "', 0, " . $jb_id . ", '" . $jb_incoming_date . "')\"" .
|
|
">;$cCtmp" . " " . " " . " " . " " . " " . " " . ";$cC2tmp" . " " . "</td></tr>\n<tr><td>";
|
|
}
|
|
return $retval . "</td></tr>\n"; //</div>\n";
|
|
}
|
|
|
|
function mk_option($optval, $opttext, $optsel) {
|
|
return "<option value=\"" . $optval . "\" " . ($optval == $optsel ? "selected" : "") . ">" . $opttext . "</option>";
|
|
}
|
|
|
|
function check_scan_events($jb_ids) {
|
|
return get_scan_events($jb_ids, false);
|
|
}
|
|
|
|
function exec_query($sqlquery, $mysql_db_id = NULL, $do_log = true) {
|
|
global $mysql_db_default;
|
|
|
|
if (is_null($mysql_db_id))
|
|
$mysql_db_id = $mysql_db_default;
|
|
if ($do_log)
|
|
writeLog_order_status("[" . date("Y-m-d H:i:s") . "] [" . $mysql_db_id . "] " . $sqlquery);
|
|
$res = mysql_query($sqlquery, $mysql_db_id) or die ($sqlquery . ": " . mysql_error($mysql_db_id));
|
|
return $res;
|
|
}
|
|
|
|
function get_first_row($sqlquery, $mysql_db_id = NULL) {
|
|
global $mysql_db_default;
|
|
|
|
if (is_null($mysql_db_id))
|
|
$mysql_db_id = $mysql_db_default;
|
|
$res = mysql_query($sqlquery, $mysql_db_id) or die ($sqlquery . ": " . mysql_error($mysql_db_id));
|
|
$row = mysql_fetch_array($res);
|
|
mysql_free_result($res);
|
|
return $row;
|
|
}
|
|
|
|
function get_first($sqlquery, $mysql_db_id = NULL) {
|
|
|
|
if ($row = get_first_row($sqlquery, $mysql_db_id))
|
|
return $row[0];
|
|
return "";
|
|
}
|
|
|
|
function formDate($sqlDate) {
|
|
if (trim($sqlDate) == "")
|
|
return "";
|
|
return substr($sqlDate, 8, 2) . "." . substr($sqlDate, 5, 2) . "." . substr($sqlDate, 0, 4);
|
|
}
|
|
|
|
function formDateTime($sqlDate) {
|
|
if (trim($sqlDate) == "")
|
|
return "";
|
|
return substr($sqlDate, 8, 2) . "." . substr($sqlDate, 5, 2) . "." . substr($sqlDate, 0, 4) . " " . substr($sqlDate, 11, 5);
|
|
}
|
|
|
|
function sqlDate($germanDate) {
|
|
if (trim($germanDate) == "")
|
|
return "";
|
|
return substr($germanDate, 6, 4) . "-" . substr($germanDate, 3, 2) . "-" . substr($germanDate, 0, 2);
|
|
}
|
|
|
|
function writeLog_order_status($log_text) {
|
|
global $test, $log_file_name;
|
|
|
|
if ($test)
|
|
$fileHandle = @fopen("../log/" . $log_file_name . "_log_test_" . date("Ym") . ".log", 'a');
|
|
else
|
|
$fileHandle = @fopen("../log/" . $log_file_name . "_log_" . date("Ym") . ".log", 'a');
|
|
@fwrite($fileHandle, $log_text . "\n");
|
|
@fclose($fileHandle);
|
|
return;
|
|
}
|
|
|
|
function findEarliestJb_createtime($jb_id) {
|
|
$jb_storno = get_first("SELECT gdc_content FROM genericdatacontainer WHERE gdc_obj_id = " . $jb_id . " AND gdc_gen_fieldname = 'jb_storno'");
|
|
if ($jb_storno == "")
|
|
return get_first("SELECT jb_createtime FROM job WHERE jb_id = " . $jb_id);
|
|
return findEarliestJb_createtime($jb_storno);
|
|
}
|
|
|
|
function check_pickup_and_arr_tour($jb_id = 0) {
|
|
global $mysql_db_b2b;
|
|
//echo "'$jb_id'<br>\n";
|
|
$sequence_no_clause = "";
|
|
$tr_commission_no_clause = "AND tr_commission_no != ''";
|
|
$tr_commission_nos = "";
|
|
if ($jb_id != 0) {
|
|
$sql_query = "SELECT TRIM(tr_commission_no) AS sequence_no FROM phoenix.tour WHERE tr_sort = 2 AND TRIM(tr_commission_no) != '' AND jb_id IN (" . $jb_id . ")";
|
|
writeLog_order_status("[" . date("Y-m-d H:i:s") . "] (DEBUG_check_pickup_and_arr_tour) " . $sql_query);
|
|
$res = exec_query($sql_query, NULL, false);
|
|
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
|
|
$sequence_no_clause .= "bo_obj_data LIKE '" . $row["sequence_no"] . "|301%' OR bo_obj_data LIKE '" . $row["sequence_no"] . "|304%' OR bo_obj_data LIKE '" . $row["sequence_no"] . "|408%' OR ";
|
|
$tr_commission_nos .= "'" . $row["sequence_no"] . "',";
|
|
endwhile;
|
|
}
|
|
if ($sequence_no_clause == "")
|
|
$sequence_no_clause = "bo_obj_data LIKE '%|301%' OR bo_obj_data LIKE '%|304%' OR bo_obj_data LIKE '%|408%'";
|
|
else
|
|
$sequence_no_clause = substr($sequence_no_clause, 0, -4);
|
|
if ($tr_commission_nos != "")
|
|
$tr_commission_no_clause = "AND TRIM(tr_commission_no) IN (" . substr($tr_commission_nos, 0, -1) . ")";
|
|
|
|
//echo "'$sequence_no_clause'<br>\n";
|
|
//echo "'$tr_commission_no_clause'<br>\n";
|
|
//die();
|
|
|
|
//24.11.2016: Dir Reihenfolge des Umoperierens der Delivery- zu den Pickup Events und des Schreibens der Ankommenszeit wurde umgedreht, damit nicht mehr versehntlich ein Pickup-Event als
|
|
// Delivery versandt werden kann (wenn nämlich genau zwischen den beiden Operationen ein Delivery-Scan hereinkommt, also nicht mehr umoperiert wird, aber die Ankommenszeit bekommt)
|
|
writeLog_order_status("-----------------------------------------------------------------------------------");
|
|
writeLog_order_status("[" . date("Y-m-d H:i:s") . "] " . "*** write time from the arrival-button in events '301', '304' and '351' ***");
|
|
$sql_query =
|
|
// "SELECT CONCAT(LEFT(gdc_content, 4), '-', MID(gdc_content, 5, 2), '-', MID(gdc_content, 7, 2), ' ', MID(gdc_content, 9, 2), ':', MID(gdc_content, 11, 2), ':', MID(gdc_content, 13, 2)) AS gdc_content_time," .
|
|
// " tr_id, tr_commission_no, bo_id" .
|
|
// " FROM phoenix.genericdatacontainer, phoenix.tour, phoenix_log.b2b_objects" .
|
|
// " WHERE gdc_obj_type = 'tr' AND gdc_gen_fieldname = 'arr_tour' AND gdc_obj_id = tr_id AND gdc_context = ''" .
|
|
// " AND bo_type = 300 AND bo_state = 1 AND tr_commission_no != ''" .
|
|
// " AND (bo_obj_data LIKE CONCAT(BINARY tr_commission_no, '|301%')" .
|
|
// " OR bo_obj_data LIKE CONCAT(BINARY tr_commission_no, '|304%')" .
|
|
// " OR bo_obj_data LIKE CONCAT(BINARY tr_commission_no, '|351%'))";
|
|
"SELECT CONCAT(LEFT(gdc_content, 4), '-', MID(gdc_content, 5, 2), '-', MID(gdc_content, 7, 2), ' ', MID(gdc_content, 9, 2), ':', MID(gdc_content, 11, 2), ':', MID(gdc_content, 13, 2)) AS gdc_content_time," .
|
|
" tr_id, tr_commission_no" .
|
|
" FROM phoenix.genericdatacontainer, phoenix.tour" .
|
|
" WHERE gdc_obj_type = 'tr' AND gdc_gen_fieldname = 'arr_tour' AND gdc_obj_id = tr_id " . $tr_commission_no_clause . " AND gdc_context = ''";
|
|
writeLog_order_status("[" . date("Y-m-d H:i:s") . "] (DEBUG_check_pickup_and_arr_tour) " . $sql_query);
|
|
$res = exec_query($sql_query, $mysql_db_b2b, false);
|
|
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
|
|
$bo_id = get_first("SELECT bo_id FROM phoenix_log.b2b_objects WHERE bo_type = 300 AND bo_state = 1" .
|
|
" AND (bo_obj_data LIKE '" . $row["tr_commission_no"] . "|301%'" .
|
|
" OR bo_obj_data LIKE '" . $row["tr_commission_no"] . "|304%'" .
|
|
" OR bo_obj_data LIKE '" . $row["tr_commission_no"] . "|351%')");
|
|
if ($bo_id != "") {
|
|
writeLog_order_status("[" . date("Y-m-d H:i:s") . "] Barcode '" . $row["tr_commission_no"] . "': Ankommenszeit '" . $row["gdc_content_time"] . "' wird geschrieben");
|
|
exec_query("UPDATE phoenix_log.b2b_objects SET bo_createtime = '" . $row["gdc_content_time"] . "' WHERE bo_id = " . $bo_id . " AND bo_createtime > '" . $row["gdc_content_time"] . "'");
|
|
exec_query("UPDATE genericdatacontainer SET gdc_context = '" . $bo_id . "' WHERE gdc_obj_type = 'tr' AND gdc_gen_fieldname = 'arr_tour' AND gdc_obj_id = " . $row["tr_id"] . " AND gdc_context = ''");
|
|
}
|
|
endwhile;
|
|
writeLog_order_status("-----------------------------------------------------------------------------------");
|
|
|
|
writeLog_order_status("[" . date("Y-m-d H:i:s") . "] " . "*** in case of a return-job, change 'Order delivered', 'Failed delivery' and 'Returned to Store' to 'Picked up at customer', 'Failed Pick-up' and 'Returned to Store Pick-Up' ***");
|
|
// "Order delivered" und "Failed delivery" in "Picked up at customer" und "Failed Pick-up" umoperieren wenn Abholung
|
|
$serviceAbhol = pow(2, get_first("SELECT mt_sort FROM metatype WHERE mt_type = 'service' AND mt_mapped_value = 'AB'"));
|
|
$sql_query = "SELECT bo_id, bo_obj_data FROM phoenix_log.b2b_objects WHERE bo_type = 300 AND bo_state IN (1) AND (" . $sequence_no_clause . ")";
|
|
writeLog_order_status("[" . date("Y-m-d H:i:s") . "] (DEBUG_check_pickup_and_arr_tour) " . $sql_query);
|
|
$res = exec_query($sql_query, $mysql_db_b2b, false);
|
|
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
|
|
list ($sequence_no, $dummy) = explode("|", $row["bo_obj_data"]);
|
|
$jb_service = get_first("SELECT jb_service FROM job, tour, genericdatacontainer WHERE tr_commission_no = '" . $sequence_no . "' AND job.jb_id = tour.jb_id AND job.jb_id = gdc_obj_id AND gdc_gen_fieldname = 'acquisitiontime'");
|
|
if (($jb_service & $serviceAbhol) == $serviceAbhol) {
|
|
writeLog_order_status("[" . date("Y-m-d H:i:s") . "] Barcode '" . $sequence_no . "': Event '" . $dummy . "' wird ersetzt");
|
|
exec_query("UPDATE phoenix_log.b2b_objects SET bo_obj_data = '" . str_replace("|408", "|359", str_replace("|301", "|351", str_replace("|304", "|354", $row["bo_obj_data"])) . "' WHERE bo_id = " . $row["bo_id"]));
|
|
}
|
|
endwhile;
|
|
writeLog_order_status("-----------------------------------------------------------------------------------");
|
|
}
|
|
|
|
function check_duplicate_deliveries() {
|
|
global $mysql_db_b2b;
|
|
|
|
writeLog_order_status("-----------------------------------------------------------------------------------");
|
|
writeLog_order_status("[" . date("Y-m-d H:i:s") . "] " . "*** avoid sending of duplicate 'Order delivered' events (possible because events can come from both mobile device and manual editing) ***");
|
|
// find duplicate "Order delivered" to be sent (the first one may already be sent before)
|
|
// Attention: This works for LCD shipments only (11 digits-barcode like 'HA000749027') and OS orders (9 digits-barcodes like '716078828')
|
|
$sql_query = "SELECT LEFT(bo_obj_data, 11) AS ship_no FROM phoenix_log.b2b_objects WHERE bo_type = 300 AND bo_state IN (0, 1, 9) AND bo_obj_data LIKE '%|301%' GROUP BY LEFT (bo_obj_data, 11) HAVING COUNT(*) > 1";
|
|
$res = exec_query($sql_query, $mysql_db_b2b, false);
|
|
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
|
|
// list ($sequence_no, $dummy) = explode("|", $row["bo_obj_data"]);
|
|
$sql_query = "SELECT bo_id, bo_state FROM phoenix_log.b2b_objects WHERE bo_type = 300 AND bo_state IN (0, 1, 9) AND (LEFT(bo_obj_data, 15) = '" . $row['ship_no'] . "|301'" .
|
|
" OR LEFT(bo_obj_data, 13) = '" . substr($row['ship_no'], 0, -2) . "|301') ORDER BY bo_createtime";
|
|
//writeLog_order_status("[" . date("Y-m-d H:i:s") . "] (DEBUG_check_duplicate_deliveries) " . $sql_query);
|
|
$res1 = exec_query($sql_query, $mysql_db_b2b, false);
|
|
$is_sent = false;
|
|
$youngest_unsent_bo_id = "";
|
|
while ($row1 = mysql_fetch_array($res1, MYSQL_ASSOC)):
|
|
// make sure that the youngest event will be sent if no event isn't sent yet
|
|
//writeLog_order_status("[" . date("Y-m-d H:i:s") . "] (DEBUG_check_duplicate_deliveries) \$row1['bo_id'] = " . $row1["bo_id"] . ", \$row1['bo_state'] = " . $row1['bo_state']);
|
|
if ($row1['bo_state'] == 0 || $row1['bo_state'] == 9) {
|
|
$is_sent = true;
|
|
} else {
|
|
if ($youngest_unsent_bo_id == "") {
|
|
$youngest_unsent_bo_id = $row1['bo_id'];
|
|
} else {
|
|
writeLog_order_status("[" . date("Y-m-d H:i:s") . "] (DEBUG_check_duplicate_deliveries) UPDATE phoenix_log.b2b_objects SET bo_state = 8 WHERE bo_id = " . $row1["bo_id"] . " AND bo_state = 1");
|
|
exec_query("UPDATE phoenix_log.b2b_objects SET bo_state = 8 WHERE bo_id = " . $row1["bo_id"] . " AND bo_state = 1");
|
|
}
|
|
}
|
|
endwhile;
|
|
//writeLog_order_status("[" . date("Y-m-d H:i:s") . "] (DEBUG_check_duplicate_deliveries) \$is_sent = " . $is_sent . ", \$youngest_unsent_bo_id = " . $$youngest_unsent_bo_id);
|
|
// at least one of the events is sent, so the unsent youngest event must not be sent
|
|
if ($is_sent && $youngest_unsent_bo_id != "") {
|
|
writeLog_order_status("[" . date("Y-m-d H:i:s") . "] (DEBUG_check_duplicate_deliveries) UPDATE phoenix_log.b2b_objects SET bo_state = 8 WHERE bo_id = " . $youngest_unsent_bo_id . " AND bo_state = 1");
|
|
exec_query("UPDATE phoenix_log.b2b_objects SET bo_state = 8 WHERE bo_id = " . $youngest_unsent_bo_id . " AND bo_state = 1");
|
|
}
|
|
|
|
endwhile;
|
|
}
|
|
|
|
function get_scan_events_internal($jb_ids, $job_details, $bodyonly = false) {
|
|
global $usr_id, $dblogin, $dbpassword;
|
|
|
|
$constGlobalDbInstNo = getParameterValue("0", "GLOBAL_UNIQUE_DB_INSTANCE_NO", "0", "0");
|
|
|
|
//writeLog_order_status("[" . date("Y-m-d H:i:s") . "] (DEBUG_get_scan_events_internal) " . $jb_ids);
|
|
foreach (explode(",", $jb_ids) as $jb_id) { // alle durchhühnern, könnten stornierte dabei sein, die die Shipmentnummer nicht mehr haben
|
|
//writeLog_order_status("[" . date("Y-m-d H:i:s") . "] (DEBUG_get_scan_events_internal) " . $jb_id);
|
|
$sequence_no = get_first("SELECT tr_commission_no FROM phoenix.tour WHERE tr_sort = 2 AND jb_id = " . $jb_id . " AND tr_commission_no LIKE 'HA%'");
|
|
//writeLog_order_status("[" . date("Y-m-d H:i:s") . "] (DEBUG_get_scan_events_internal) " . $sequence_no);
|
|
if ($sequence_no != "")
|
|
break;
|
|
}
|
|
|
|
$internal_events_html = "";
|
|
if (trim($sequence_no) != "") {
|
|
$sql_query = "SELECT log_createtime, usr_id, logo_description FROM phoenix_log.log WHERE logo_id = 138 AND logo_description like '" . $sequence_no . "%' ORDER BY log_createtime";
|
|
writeLog_order_status("[" . date("Y-m-d H:i:s") . "] (DEBUG_get_scan_events_internal) " . $sql_query);
|
|
$res = exec_query($sql_query, $mysql_db_b2b, false);
|
|
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
|
|
if (!is_null($row["usr_id"]) && $row["usr_id"] != 0):
|
|
$tmp_row = get_first_row("SELECT usr_account, usr_firstname, usr_name FROM user WHERE usr_id = " . $row["usr_id"]);
|
|
$row["usr_account"] = $tmp_row[0];
|
|
$row["usr_firstname"] = $tmp_row[1];
|
|
$row["usr_name"] = $tmp_row[2];
|
|
endif;
|
|
$logo_descriptionArr = explode("|", $row["logo_description"]);
|
|
$internal_events_html .= "<tr><td></td><td>" . $logo_descriptionArr[2] . "</td><td align=left>" . formDateTime($row["log_createtime"]) . "</td><td align=left>" . $row["usr_firstname"] . " " . $row["usr_name"] . " (" . $row["usr_account"] . ")</td></tr>";
|
|
endwhile;
|
|
|
|
if (!$job_details) {
|
|
$mysql_db_intern = mysql_connect("172.16.0.103:3711", $dblogin, $dbpassword) or die (mysql_error());
|
|
mysql_select_db("sysadmin", $mysql_db_intern) or die (mysql_error($mysql_db_intern));
|
|
mysql_query('SET NAMES latin1', $mysql_db_intern) or die ('SET NAMES latin1' . ": " . mysql_error($mysql_db_intern));
|
|
$imei = get_first("SELECT TRIM(cp_val) FROM conf_prop WHERE cust_id = " . $constGlobalDbInstNo . " AND prop_id = 21 AND usr_id = " . $usr_id . " AND cp_val LIKE 'WU%'", $mysql_db_intern);
|
|
if ($imei != "") {
|
|
$internal_events_html .=
|
|
"<tr><td></td><td colspan='3'><b>Shipment Status hinzufügen:</b> " .
|
|
"<select name=\"" . $sequence_no . "_" . "set_status" . "_select\" class=\"input\" onchange=\"edit_event('" . $sequence_no . "','set_status','" . $imei . "','set_status', 0, " . $jb_id . ", '')\">";
|
|
foreach (array(
|
|
0 => '---',
|
|
1 => 'HOTT',
|
|
2 => 'LODT',
|
|
3 => 'LOS',
|
|
4 => 'RAH',
|
|
// 5 => 'ANK',
|
|
6 => 'PUAC',
|
|
7 => 'DF',
|
|
8 => 'RTS',
|
|
9 => 'OD',
|
|
10 => 'ABBR',
|
|
11 => 'RTSPU',
|
|
12 => 'PUF',
|
|
13 => 'RAS')
|
|
as $val => $code)
|
|
$internal_events_html .= mk_option($val, $code, 0);
|
|
$internal_events_html .= "</select></td><tr>";
|
|
}
|
|
}
|
|
if ($internal_events_html != "" && !$bodyonly)
|
|
$internal_events_html =
|
|
"<table border=0 cellspacing=0 cellpadding=4 vspace=0 hspace=0 name=\"eventTableInternal\">" .
|
|
"<tr><td></td><td><b>Shipment Status</b>" . "</td><td align=left>" . "<b>Zeitpunkt</b>" . "</td><td align=left>" . "<b>Vorname Name (Login)</b>" . "</td></tr>" .
|
|
$internal_events_html .
|
|
"</table><br>";
|
|
}
|
|
return $internal_events_html;
|
|
}
|