= "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 = "\n
" . str_replace(";", "", $csv_txt) . "\n
\n"; } else { $csv_txt = "" . str_replace("\n", "", str_replace(";", "", $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" . "\n\n" : ";" . $cC . "Scan-Event" . ";$cC2" . "Event-Zeit" . ";$cC" . "Vorname Name (Login)" . ";$cC2" . "Event-Status" . "\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 .= /* "
" . */ ""; if ($row["jb_finishtime"] == "0000-00-00 00:00:00") $row["jb_finishtime"] = ""; $ret_val .= $cC . "" . $row["jb_id"]. "" . ";$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 .= ""; } $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|
\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 "
\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" . "" . ";$cCtmp" . " " . " " . " " . " " . " " . "" . ";$cC2tmp" . " " . "\n;;;;;;;;"; // 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 . "
\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|
\n"; } //echo "\$event_cnt=|$event_cnt|
\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 . "
\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 = "\n"; $select_list = true; } $ret_val .= ";" . $cCtmp . (/*$row["bo_state"] != 0 */ $event_no != 501 && $event_no != 502 && !$job_details ? "" . "\"löschen\" " : "") . $eventText . ";$cC2tmp" . ($job_details || ((!$outoftime && $row["bo_state"] != 7) || $row["bo_state"] == 0 || $row["bo_state"] == 6) ? formDateTime($row["bo_createtime"]) : "") . ";$cCtmp" . $row["usr_firstname"] . " " . $row["usr_name"] . " (" . $row["usr_account"] . ")" . ";$cC2tmp" . $bo_stateArr[$row["bo_state"]] . "\n"; // if (!$job_details && ((($outoftime || $row["bo_state"] == 7) && $row["bo_state"] != 0 && $row["bo_state"] != 6) || $select_list)) // } elseif($out) { // $ret_val .= // ";;;honk;\n"; } $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]) . "|
\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" . ";$cCtmp" . " " . " " . " " . " " . " " . " " . ";$cC2tmp" . " " . "\n"; } return $retval . "\n"; //
\n"; } function mk_option($optval, $opttext, $optsel) { return ""; } 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'
\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'
\n"; //echo "'$tr_commission_no_clause'
\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 .= "" . $logo_descriptionArr[2] . "" . formDateTime($row["log_createtime"]) . "" . $row["usr_firstname"] . " " . $row["usr_name"] . " (" . $row["usr_account"] . ")"; 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 .= "Shipment Status hinzufügen: " . ""; } } if ($internal_events_html != "" && !$bodyonly) $internal_events_html = "" . "" . $internal_events_html . "
Shipment Status" . "" . "Zeitpunkt" . "" . "Vorname Name (Login)" . "

"; } return $internal_events_html; }