GetOne($sql); return $result === FALSE ? null : $result; } else { if (empty($sql)) exit(json_encode("Program encountered an error.")); global $db; $result = $db->GetOne($sql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $sql), 0)); return $result === FALSE ? null : $result; } } public static function excuteObjectSql($sql) { if (empty($sql)) exit(json_encode("Program encountered an error.")); global $db; $result = $db->GetRow($sql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $sql), 0)); return $result === FALSE ? null : $result; } public static function excuteUpdateSql($sql, $req_id = FALSE) { if (empty($sql)) exit(json_encode("Program encountered an error.")); global $db; $rs = $db->Execute($sql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $sql), 0)); if ($rs && $req_id !== FALSE) return $db->PO_Insert_ID(); else return $rs; } public static function excuteListSql($sql, $exception = FALSE) { if ($exception) { global $db; $result = $db->GetAll($sql); return $result === FALSE ? null : $result; } else { if (empty($sql)) exit(json_encode("Program encountered an error.")); global $db; $result = $db->GetAll($sql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $sql), 0)); return $result === FALSE ? null : $result; } } /* * Database log output */ public static function dbLog($db, $sql) { $backMsg = $db->errorMsg() . ' sql=' . $sql; return $backMsg; } /* * get IP */ public static function ip() { if (getenv("HTTP_X_FORWARDED_FOR")) return getenv("HTTP_X_FORWARDED_FOR"); if (getenv("HTTP_CLIENT_IP")) return getenv("HTTP_CLIENT_IP"); if (getenv("REMOTE_ADDR")) return getenv("REMOTE_ADDR"); if ($HTTP_SERVER_VARS["HTTP_X_FORWARDED_FOR"]) return $HTTP_SERVER_VARS["HTTP_X_FORWARDED_FOR"]; if ($HTTP_SERVER_VARS["HTTP_CLIENT_IP"]) return $HTTP_SERVER_VARS["HTTP_CLIENT_IP"]; if ($HTTP_SERVER_VARS["REMOTE_ADDR"]) return $HTTP_SERVER_VARS["REMOTE_ADDR"]; return "Unknown"; } public static function getDBUuid() { global $db; $sql = "select uuid_generate_v1()"; $uuid = $db->GetOne($sql); return $uuid; } public static function securityCheckHandNew($action) { if (strpos($action, "handset") === 0) { } elseif (strpos($action, "hand") === 0) { } else { common::sessionVerify(); $httpAccept = $_SERVER['HTTP_ACCEPT']; // ajax request,is json or html $ajax = isset($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER ['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest'; // is ajax request? //暂时这么写，放开action = main的查询是为了在没有登录时获取信息。 //tools是为了避免查询的用户，没有配置tools权限，但是新版mark_save需要进入 if($action == "main" || $action == "tools" || $action == "save_layout"){ return; } //tools是为了避免查询的用户，没有配置tools权限，但是新版mark_save需要进入 if($action == "robot_chat" ){ $operate = strtolower(utils::_get('operate')); if ($operate == "ai_chat_fixed_init"|| $operate == "ai_chat_prompt"){ return; } } if (!isset($_SESSION['ONLINE_USER'])) { self::timeoutPrintInfor($httpAccept, $ajax, 'no'); } else { $time = time() - $_SESSION["LAST_OPERATE_TIME"]; $set_session_timeout = $_SESSION['SESSION_TIMEOUT']; if (_getLoginName() == "ra.admin") { $set_session_timeout = 4 * 3600; } if ($time > $set_session_timeout) { self::timeoutPrintInfor($httpAccept, $ajax, 'yes'); } else { $_SESSION["LAST_OPERATE_TIME"] = time(); if (!_isAdmin() && $_GET["action"] != "linkcrm") { if (!utils::checkExist($_SESSION['ONLINE_USER']['permission'], $action) && !(stripos($action, "main") === 0) && !(stripos($action, "user_guide") === 0) && !(stripos($action, "feature_update") === 0) && !(stripos($action, "ajax") === 0) && !(stripos($action, "opreation_log") === 0) && !(stripos($action, "robot") === 0) // robot 开头的都匹配上了 && !(stripos($action, "system_setting") === 0) && !(stripos($action, "monitoring_setting") === 0) && !(stripos($action, "notifications_rules") === 0) && !(stripos($action, "destination_delivery") === 0) // destination_delivery 开头的都匹配上了 ) { $data = array("msg"=>"Permission Denied"); common::echo_json_encode(500, $data); exit(); } } } } session_write_close(); } } public static function getCompanySearch() { $sc_list = _get_schemas(); if (utils::count($sc_list) == 1) { return ""; } if (_isCustomerLogin()) { $msg = ''; } else { $msg = '
Apex/SFS/Kerry : 
'; } return $msg; } public static function sessionVerify() { if (!isset($_SESSION['user_agent'])) { $_SESSION['user_agent'] = MD5($_SERVER['REMOTE_ADDR'] . $_SERVER['HTTP_USER_AGENT']); } elseif ($_SESSION['user_agent'] != MD5($_SERVER['REMOTE_ADDR'] . $_SERVER['HTTP_USER_AGENT'])) { session_regenerate_id(); } } public static function searchExtendHand_KLN($type, $user, $company_name = "station_name") { $sqlWhere = " 1=1"; if($type == "booking"){ $ocean_booking_sqlWhere = self::searchExtendHandNew("booking",$user, $company_name); $air_booking_sqlWhere = self::searchExtendHandNew("air_booking",$user, $company_name); $sqlWhere = "((transport_mode ='sea' and ".$ocean_booking_sqlWhere.") or (transport_mode ='air' and ".$air_booking_sqlWhere."))"; } if($type == "ocean"){ $ocean_booking_sqlWhere = self::searchExtendHandNew("ocean",$user, $company_name); $air_booking_sqlWhere = self::searchExtendHandNew("air",$user, $company_name); $sqlWhere = "((transport_mode ='sea' and ".$ocean_booking_sqlWhere.") or (transport_mode ='air' and ".$air_booking_sqlWhere."))"; } return $sqlWhere; } public static function searchExtendHandNew($type, $user, $company_name = "station_name") { if (_isAdminHandNew($user)) { if(strtolower($type) == "air_booking" ) { return "1=1"; } return " (COALESCE(schem_not_display,false) = false)"; } if (_isDocAdmin($user["user_login"])) { if(strtolower($type) == "air_booking" ) { return "1=1"; } return ' (COALESCE(schem_not_display,false) = false)'; } if (strtolower($type) != "ocean" && strtolower($type) != "booking" && strtolower($type) != "air_booking"&& strtolower($type) != "air") { return " 1<>1"; } if (empty($user["schemas_list"])) { $user["schemas_list"] = $_SESSION["schemas_list"]; } $schemas_list = $user["schemas_list"]; if ($user["is_kerry_shipment"] == "t") { $sqlWhere = " is_kerry_shipment is not null and is_kerry_shipment=true"; } else if ($user["is_kerry_shipment"] == "f") { $sqlWhere = " (COALESCE(is_kerry_shipment,false) = false)"; } else { $sqlWhere = " 1=1 "; } if (!empty($_POST["_apex_or_sfs"])) { $sqlWhere .= " and order_from='" . $_POST["_apex_or_sfs"] . "'"; } if (utils::count($schemas_list) == 1) { $schames = $schemas_list[0]["schemas_name"]; if(strtolower($type) == "air_booking" ||strtolower($type) == "air") { } else $sqlWhere .= " and order_from='$schames'"; if ($schames == "sfs" && empty($user["sfs_ONLINE_USER"])) { $user = $_SESSION["sfs_ONLINE_USER"]; } if (strtolower($type) == "ocean") { $sqlWhere .= self::_oceanHandNew($user, $schames); if ($company_name == "doc") { if (empty($user["view_file_format"])) { if (strtolower($user["user_type"]) == "customer") { $sqlWhere .= " and serial_no in (SELECT serial_no from $schames.ra_online_file_format where active = true and client_display = true)"; } else { $sqlWhere .= " and serial_no in (SELECT serial_no from $schames.ra_online_file_format where active = true)"; } } else { $sqlWhere .= " and serial_no in (SELECT regexp_split_to_table('" . $user["view_file_format"] . "',';'))"; } } } if (strtolower($type) == "booking") { $sqlWhere .= self::_bookingHandNew($user, $schames); } if (strtolower($type) == "air_booking") { $sqlWhere .= self::_air($schames); } if (strtolower($type) == "air") { $sqlWhere .= self::_air($schames); } } elseif (utils::count($schemas_list) == 2) { if(strtolower($type) == "air_booking" ){ }else{ $sqlWhere .= " and (COALESCE(schem_not_display,false) = false)"; } if (strtolower($type) == "ocean") { $sqlWhere .= " and ((order_from='public' "; $sqlWhere .= self::_oceanHandNew($user, "public"); if ($company_name == "doc") { if (empty($user["view_file_format"])) { if (strtolower($user["user_type"]) == "customer") { $sqlWhere .= " and serial_no in (SELECT serial_no from public.ra_online_file_format where active = true and client_display = true)"; } else { $sqlWhere .= " and serial_no in (SELECT serial_no from public.ra_online_file_format where active = true)"; } } else { $sqlWhere .= " and serial_no in (SELECT regexp_split_to_table('" . $user["view_file_format"] . "',';'))"; } } $sqlWhere .= ") or (order_from='sfs' "; if (empty($user["sfs_ONLINE_USER"])) { $user = $_SESSION["sfs_ONLINE_USER"]; } $sqlWhere .= self::_oceanHandNew($user, "sfs"); if ($company_name == "doc") { if (empty($user["view_file_format"])) { if (strtolower($user["user_type"]) == "customer") { $sqlWhere .= " and serial_no in (SELECT serial_no from sfs.ra_online_file_format where active = true and client_display = true)"; } else { $sqlWhere .= " and serial_no in (SELECT serial_no from sfs.ra_online_file_format where active = true)"; } } else { $sqlWhere .= " and serial_no in (SELECT regexp_split_to_table('" . $user["view_file_format"] . "',';'))"; } } $sqlWhere .= ") )"; } if (strtolower($type) == "booking") { $sqlWhere .= " and ((order_from='public' "; $sqlWhere .= self::_bookingHandNew($user, "public"); $sqlWhere .= ") or (order_from='sfs' "; if (empty($user["sfs_ONLINE_USER"])) { $user = $_SESSION["sfs_ONLINE_USER"]; } $sqlWhere .= self::_bookingHandNew($user, "sfs"); $sqlWhere .= "))"; } if (strtolower($type) == "air_booking") { $sqlWhere .= " and ((order_from='public' "; $sqlWhere .= self::_air("public"); $sqlWhere .= ") or (order_from='sfs' "; if (empty($user["sfs_ONLINE_USER"])) { $user = $_SESSION["sfs_ONLINE_USER"]; } $sqlWhere .= self::_air("sfs"); $sqlWhere .= "))"; } if (strtolower($type) == "air") { $sqlWhere .= " and ((order_from='public' "; $sqlWhere .= self::_air($user, "public"); $sqlWhere .= ") or (order_from='sfs' "; if (empty($user["sfs_ONLINE_USER"])) { $user = $_SESSION["sfs_ONLINE_USER"]; } $sqlWhere .= self::_air($user, "sfs"); $sqlWhere .= ") )"; } } else { $sqlWhere = " 1<>1"; } return $sqlWhere; } public static function getStatusStage($status) { if ($status == "EE" || $status == "I") { return 0; } if ($status == "AE" || $status == "VD" || $status == "VA_RELAY" || $status == "UV_RELAY" || $status == "AE_RELAY" || $status == "VD_RELAY") { return 1; } if ($status == "VA" || $status == "UV" || $status == "AL" || $status == "AR" || $status == "CU" || $status == "CT" || $status == "CR" || $status == "OA") { return 2; } if ($status == "AV" || $status == "D" || $status == "RD") { return 3; } return -1; } public static function getInNotInSql($contact_id, $type = 'in') { if (empty($contact_id)) return " =''"; $contact_id = trim($contact_id); $contact_id = trim($contact_id, ";"); $contact_id = trim($contact_id); $contact_id = strtolower($contact_id); if ($type == 'in') { if (utils::checkExist($contact_id, ";")) { $ss = ""; $aa = explode(";", $contact_id); foreach ($aa as $k => $v) { $v = trim($v); if (empty($ss)) $ss = "'" . common::check_input($v) . "'"; else $ss .= ",'" . common::check_input($v) . "'"; } return " in (" . $ss . ")"; } else { return " = '" . common::check_input($contact_id) . "'"; } } else { if (utils::checkExist($contact_id, ";")) { $ss = ""; $aa = explode(";", $contact_id); foreach ($aa as $k => $v) { $v = trim($v); if (empty($ss)) $ss = "'" . common::check_input($v) . "'"; else $ss .= ",'" . common::check_input($v) . "'"; } return " in (" . $ss . ")"; } else { return " != '" . common::check_input($contact_id) . "'"; } } } public static function getInNotInSqlForSearch($contact_id, $type = 'in') { if (empty($contact_id)) return " =''"; $contact_id = trim($contact_id); $contact_id = trim($contact_id, ";"); $contact_id = trim($contact_id); $contact_id = strtolower($contact_id); if ($type == 'in') { if (utils::checkExist($contact_id, ";")) { $ss = ""; $aa = explode(";", $contact_id); foreach ($aa as $k => $v) { $v = trim($v); if (empty($ss)) $ss = "'" . common::check_input($v) . "'"; else $ss .= ",'" . common::check_input($v) . "'"; } return $ss; } else { return "'" . common::check_input($contact_id) . "'"; } } } /* * Encrypt a SQL query statement used to be passed as a parameter to get excel output encode :DeCode('str','E'); decode :DeCode('enstr','D'); */ public static function deCode($string, $operation = "E") { $key = md5("uls_webwms"); $key_length = strlen($key); if ($operation == "D") $string = rawurldecode($string); $string = $operation == 'D' ? base64_decode($string) : substr(md5($string . $key), 0, 8) . $string; $string_length = strlen($string); $rndkey = $box = array(); $result = ''; for ($i = 0; $i <= 255; $i++) { $rndkey [$i] = ord($key [$i % $key_length]); $box [$i] = $i; } for ($j = $i = 0; $i < 256; $i++) { $j = ($j + $box [$i] + $rndkey [$i]) % 256; $tmp = $box [$i]; $box [$i] = $box [$j]; $box [$j] = $tmp; } for ($a = $j = $i = 0; $i < $string_length; $i++) { $a = ($a + 1) % 256; $j = ($j + $box [$a]) % 256; $tmp = $box [$a]; $box [$a] = $box [$j]; $box [$j] = $tmp; $result .= chr(ord($string [$i]) ^ ($box [($box [$a] + $box [$j]) % 256])); } if ($operation == 'D') { if (substr($result, 0, 8) == substr(md5(substr($result, 8) . $key), 0, 8)) { return substr($result, 8); } else { return ''; } } else { return rawurlencode(str_replace('=', '', base64_encode($result))); } } public static function getStatusDesc($code) { if (strtoupper($code) == "EE") { return "Empty Equipment Dispatched"; } if (strtoupper($code) == "I") { return "Gate in full for a booking"; } if (strtoupper($code) == "AE") { return "Container loaded on vessel"; } if (strtoupper($code) == "VD") { return "Vessel Departure"; } if (strtoupper($code) == "VA_RELAY") { return "Arrive Relay Port"; } if (strtoupper($code) == "VD_RELAY") { return "Depart Relay Port"; } if (strtoupper($code) == "AE_RELAY") { return "Loaded at Relay Port"; } if (strtoupper($code) == "UV_RELAY") { return "Unloaded at Relay Port"; } if (strtoupper($code) == "VA") { return "Vessel Arrival"; } if (strtoupper($code) == "UV") { return "Unloaded From Vessel"; } if (strtoupper($code) == "AL") { return "Container loaded on Rail"; } if (strtoupper($code) == "AR") { return "Container unloaded from Rail"; } if (strtoupper($code) == "CU") { return "Carrier and Customs Release"; } if (strtoupper($code) == "CT") { return "Customs release"; } if (strtoupper($code) == "CR") { return "Carrier release"; } if (strtoupper($code) == "OA") { return "Gate out full from final discharge port"; } if (strtoupper($code) == "AV") { return "Shipment available for pickup or delivery"; } if (strtoupper($code) == "RD") { return "Container returned empty"; } if (strtoupper($code) == "D") { return "Gate out for delivery to customer"; } } public static function _toString($msg) { if ($msg == "" || $msg == NULL) { return ""; } return $msg . ""; } /* * timeout output */ public static function timeoutPrintInfor($httpAccept, $ajax, $login) { //记录系统因session过期 退出登录 $data = array("msg"=>"session_time_out"); self::echo_json_encode(403,$data); exit(); } /** * _bookingHandNew 和 _oceanHandNew逻辑完全一样，只是ocean 多一个dest_op字段，在booking里没有的 */ private static function _oceanHandNew($user, $schemas = "public") { $o = $user['ocean_station']; $o_or = $user['ocean_station_or']; $d = $user['ocean_agent']; $d_or = $user['ocean_agent_or']; $sales = $user['ocean_sales']; $sales_or = $user['ocean_sales_or']; $op = $user['ocean_dest_op']; $op_or = $user['ocean_dest_op_or']; $follow = $user['ocean_following_sales']; $follow_or = $user['ocean_following_sales_or']; if (strtolower($o_or) == "all" || strtolower($d_or) == "all" || strtolower($sales_or) == "all" || strtolower($op_or) == "all") { return " and 1=1"; } $sqlWhere = ""; if (_isCustomerLoginHandNew($user)) { $sqlWhere .= " and " . _customerFilerSearchHandNew($user, $schemas); } else { if (empty($o) && empty($d) && empty($sales) && empty($op) && empty($follow)) { return " and 1<>1"; } if ((strtolower($o) == 'all' || empty($o)) && (strtolower($d) == "all" || empty($d))) { } else { $sql = "1=1"; if (!empty($o) && strtolower($o) != 'all') { $sql .= " and lower(origin)"; $sql .= utils::getInSql($o); } if (!empty($d) && strtolower($d) != 'all') { $sql .= " and lower(agent)"; $sql .= utils::getInSql($d); } $sqlWhere .= " and (" . $sql . ")"; } if (strtolower($sales) == 'all' || empty($sales)) { } else { if (utils::checkExist($sales, ";")) { $sql = "1!=1"; $tt = explode(";", $sales); foreach ($tt as $t) { $t = trim($t); if (!empty($t)) $sql .= " or lower(sales_rep)='" . strtolower($t) . "'"; } $sqlWhere .= " and ($sql)"; } else $sqlWhere .= " and lower(sales_rep)='" . strtolower($sales) . "'"; } if (strtolower($op) == 'all' || empty($op)) { } else { $sqlWhere .= " and lower(dest_op) " . utils::getInSql($op); } if (strtolower($follow) == 'all' || empty($follow)) { } else { if (utils::checkExist($follow, ";")) { $sql = "1!=1"; $tt = explode(";", $follow); foreach ($tt as $t) { $t = trim($t); if (!empty($t)) $sql .= " or following_sales ilike '" . $t . "'"; } $sqlWhere .= " and ($sql)"; } else $sqlWhere .= " and following_sales ilike '" . $follow . "'"; } $sqlWhere = " (1=1 $sqlWhere)"; if (!empty($o_or)) { $sqlWhere .= " or lower(origin)"; $sqlWhere .= utils::getInSql($o_or); } if (!empty($d_or)) { $sqlWhere .= " or lower(agent)"; $sqlWhere .= utils::getInSql($d_or); } if (!empty($sales_or)) { if (utils::checkExist($sales_or, ";")) { $sql = "1!=1"; $tt = explode(";", $sales_or); foreach ($tt as $t) { $t = trim($t); if (!empty($t)) $sql .= " or lower(sales_rep)='" . strtolower($t) . "'"; } $sqlWhere .= " or ($sql)"; } else $sqlWhere .= " or lower(sales_rep)='" . strtolower($sales_or) . "'"; } if (!empty($op_or)) { $sqlWhere .= " or lower(dest_op) " . utils::getInSql($op_or); } if (!empty($follow_or)) { if (utils::checkExist($follow_or, ";")) { $sql = "1!=1"; $tt = explode(";", $follow_or); foreach ($tt as $t) { $t = trim($t); if (!empty($t)) $sql .= " or following_sales ilike '" . $t . "'"; } $sqlWhere .= " or ($sql)"; }else { $sqlWhere .= " or following_sales ilike '" . $follow_or . "'"; } } $sqlWhere = " and ($sqlWhere)"; } return $sqlWhere; } /** * _bookingHandNew 和 _oceanHandNew逻辑完全一样，只是ocean 多一个dest_op字段，在booking里没有的 */ private static function _bookingHandNew($user, $schames) { $o = $user['ocean_station']; $o_or = $user['ocean_station_or']; $d = $user['ocean_agent']; $d_or = $user['ocean_agent_or']; $sales = $user['ocean_sales']; $sales_or = $user['ocean_sales_or']; $op = $user['ocean_dest_op']; $op_or = $user['ocean_dest_op_or']; $follow = $user['ocean_following_sales']; $follow_or = $user['ocean_following_sales_or']; if (strtolower($o_or) == "all" || strtolower($d_or) == "all" || strtolower($sales_or) == "all") { return " and 1=1"; } $sqlWhere = ""; if (_isCustomerLoginHandNew($user)) { $sqlWhere .= " and " . _customerFilerSearchHandNew($user, $schames); } else { if (empty($o) && empty($d) && empty($sales) && empty($op) && empty($follow)) { return " and 1<>1"; } if ((strtolower($o) == 'all' || empty($o)) && (strtolower($d) == "all" || empty($d))) { } else { $sql = "1=1"; if (!empty($o) && strtolower($o) != 'all') { $sql .= " and lower(origin)"; $sql .= utils::getInSql($o); } if (!empty($d) && strtolower($d) != 'all') { $sql .= " and lower(agent)"; $sql .= utils::getInSql($d); } $sqlWhere .= " and (" . $sql . ")"; } if (strtolower($sales) == 'all' || empty($sales)) { } else { if (utils::checkExist($sales, ";")) { $sql = "1!=1"; $tt = explode(";", $sales); foreach ($tt as $t) { $t = trim($t); if (!empty($t)) $sql .= " or lower(sales_rep)='" . strtolower($t) . "'"; } $sqlWhere .= " and ($sql)"; } else $sqlWhere .= " and lower(sales_rep)='" . strtolower($sales) . "'"; } if (strtolower($follow) == 'all' || empty($follow)) { } else { if (utils::checkExist($follow, ";")) { $sql = "1!=1"; $tt = explode(";", $follow); foreach ($tt as $t) { $t = trim($t); if (!empty($t)) $sql .= " or following_sales ilike '" . $t . "%'"; } $sqlWhere .= " and ($sql)"; } else $sqlWhere .= " and following_sales ilike '" . $follow . "%'"; } $sqlWhere = " (1=1 $sqlWhere)"; if (!empty($o_or)) { $sqlWhere .= " or lower(origin)"; $sqlWhere .= utils::getInSql($o_or); } if (!empty($d_or)) { $sqlWhere .= " or lower(agent)"; $sqlWhere .= utils::getInSql($d_or); } if (!empty($sales_or)) { if (utils::checkExist($sales_or, ";")) { $sql = "1!=1"; $tt = explode(";", $sales_or); foreach ($tt as $t) { $t = trim($t); if (!empty($t)) $sql .= " or lower(sales_rep)='" . strtolower($t) . "'"; } $sqlWhere .= " or ($sql)"; } else $sqlWhere .= " or lower(sales_rep)='" . strtolower($sales_or) . "%'"; } if (!empty($follow_or)) { if (utils::checkExist($follow_or, ";")) { $sql = "1!=1"; $tt = explode(";", $follow_or); foreach ($tt as $t) { $t = trim($t); if (!empty($t)) $sql .= " or following_sales ilike '" . $t . "%'"; } $sqlWhere .= " or ($sql)"; } else $sqlWhere .= " or following_sales ilike '" . $follow_or . "%'"; } $sqlWhere = " and ($sqlWhere)"; } return $sqlWhere; } /** * online air */ private static function _air($schemas = "public") { $station = _getAirStation($schemas); $station_or = _getAirStationOr($schemas); $sales = _getAirSales($schemas); $sales_or = _getAirSalesOr($schemas); $sqlWhere = " 1=1 "; if (_isCustomerLogin()) { $sqlWhere .= " and " . _customerAirFilerSearch($schemas); } else { if (empty($station) && empty($sales)) { return "and 1<>1"; } if (strtolower($station_or) == "all" || strtolower($sales_or) == "all") return " and ($sqlWhere)"; if (strtolower($station) == 'all' || empty($station)) { } else { $sqlWhere .= " and (lower(origin)"; $sqlWhere .= utils::getInSql($station); $sqlWhere .= " or lower(agent)"; $sqlWhere .= utils::getInSql($station); $sqlWhere .= ")"; } if (strtolower($sales) == 'all' || empty($sales)) { } else { $sqlWhere .= " and (lower(sales_rep) " . utils::getInSql($sales) . " or lower(following_sales) " . utils::getInSql($sales) . ")"; } $sqlWhere = " ($sqlWhere)"; if (!empty($station_or)) $sqlWhere = "$sqlWhere or lower(origin)" . utils::getInSql($station_or) . " or lower(agent)" . utils::getInSql($station_or); if (!empty($sales_or)) $sqlWhere = "$sqlWhere or lower(sales_rep)" . utils::getInSql($sales_or) . " or lower(following_sales)" . utils::getInSql($sales_or); } return " and ($sqlWhere)"; } public static function isNewVersion() { if (utils::checkExist($_SERVER['PHP_SELF'], "main_new_version.php")) { return "_new"; } return ""; } public static function removeTopOceanOldVersionSpecialField($rss){ $rss_bk = $rss; $new_arr = array(); foreach ($rss_bk as $k => $v) { if (utils::startWith($v['database_column_name'], "__") || utils::startWith($v['database_column_name'], "___")){ //unset($rss[$k]); }else{ $new_arr[] = $v; } } return $new_arr; } public static function echo_json_encode($code,$data){ if(_isDesensitizationKln()){ common::maskShipperName($data); } if((_isCustomerLogin())){ common::maskOceanMbol($data); } $resData = array(); $resData["code"] = $code; $resData["data"] =$data; //echo utils::jsonFiltration("null", "\"\"", json_encode($resData)); echo json_encode($resData); } public static function maskOceanMbol(&$data) { if (is_array($data)) { foreach ($data as $key => &$value) { if ($key === 'MBOL/MAWB No.') { if(is_array($value)){ $temp = array(); foreach($value as $vk =>$vv){ if ($value['transport_mode_extend'] == 'sea'){ $temp[$vk] = '**********'; } } $value = $temp; } else { if ($data['transport_mode_extend'] == 'sea'){ $value = '**********'; // 替换为脱敏值 } } } else { // 递归处理子数组 common::maskOceanMbol($value); } } unset($value); // 解除引用 } // 如果是标量（string, int 等），不做处理 } public static function maskShipperName(&$data) { if (is_array($data)) { foreach ($data as $key => &$value) { if ($key === 'shipperName' || $key === 'consigneeName' || $key === 'Shipper' || $key === 'Consignee' || $key === 'shipper' || $key === 'consignee' || $key === 'Notify Party' || $key === 'Bill To') { if(is_array($value)){ $temp = array(); foreach($value as $vk =>$vv){ $temp[$vk] = '**********'; } $value = $temp; } else { $value = '**********'; // 替换为脱敏值 } } else { // 递归处理子数组 common::maskShipperName($value); } } unset($value); // 解除引用 } // 如果是标量（string, int 等），不做处理 } /* * MM/DD/YYYY To YYYY-MM-DD */ public static function usDate2sqlDate($timestr) { if (empty($timestr)) return ''; $datearray = explode("/", $timestr); $m = $datearray [0]; $d = $datearray [1]; $y = $datearray [2]; return $y . "-" . $m . "-" . $d; } public static function uuid() { return md5(uniqid("", TRUE) . mt_rand()); } /* * YYYYMMDD To MM/DD/YYYY */ public static function date2usdate($datestr) { if (empty($datestr)) return ''; $y = substr($datestr, 0, 4); $m = substr($datestr, 4, 2); $d = substr($datestr, 6, 2); return $m . "/" . $d . "/" . $y; } /* * date add some days */ public static function addDays($date, $days) { $time = strtotime($date) + $days * 24 * 3600; return date('m/d/Y', $time); } /* * download file from file system */ public static function download_file($filename, $display_name = null, $delete = FALSE, $files = NULL) { $filename = str_replace("/", DIRECTORY_SEPARATOR, $filename); $filename = str_replace("\\", DIRECTORY_SEPARATOR, $filename); if (!file_exists($filename)){ exit("File Not Exist"); } if (empty($display_name)) $display_name = basename($filename); //$file = fopen($filename, "r"); header_remove("Content-type"); header("Content-type:" . self::getContentType($filename)); header("Expires: 0"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Pragma: can-cache"); header("Accept-ranges:bytes"); header("Accept-length:" . filesize($filename)); header('Access-Control-Expose-Headers: Content-Disposition'); header("Content-Disposition:attachment;filename=\"" . $display_name . "\""); //echo fread($file, filesize($filename)); //fclose($file); //针对大文件，规定每次读取文件的字节数为4096字节，直接输出数据 $read_buffer = 4096; $handle = fopen($filename, 'rb'); //总的缓冲的字节数 $sum_buffer = 0; $filesize = filesize($filename); //只要没到文件尾，就一直读取 while (!feof($handle) && $sum_buffer < $filesize) { echo fread($handle, $read_buffer); $sum_buffer += $read_buffer; } //关闭句柄 fclose($handle); if ($delete !== FALSE) { unlink($filename); } if (!empty($files)) { foreach ($files as $f) { unlink($f); } } } public static function getContentType($filename) { $extend = self::getExtendFilename($filename); $filetype = array( 'xls' => 'application/vnd.ms-excel', 'doc' => 'application/msword', 'gif' => 'image/gif', 'jpg' => 'image/jpeg', 'jpeg' => 'image/jpeg', 'jpe' => 'image/jpeg', 'bmp' => 'image/bmp', 'png' => 'image/png', 'tif' => 'image/tiff', 'pdf' => 'application/pdf', 'zip' => 'application/zip' ); return $filetype[$extend]; } /** * 生成嵌套 ZIP 文件（按 format_name 分组） * * @param array $files 文件列表 */ public static function downloadFilesAsZip($files) { // 清除缓冲区，防止输出干扰 ZIP 文件 if (ob_get_level()) { ob_end_clean(); } $f = ONLINE_ROOT . DS . "upload" . DS . "Attachment_Summary_" . date('Y-m-dH\hi\ms\s', time()) . ".zip"; $zip = new ZipArchive(); if ($zip->open($f, ZipArchive::OVERWRITE) === TRUE || $zip->open($f, ZipArchive::CREATE) === TRUE) { } else { die("create ZIP faild。"); } // 按 format_name 分组 $grouped = []; foreach ($files as $file) { $format = $file['format_name'] ?: 'Other'; if (!isset($grouped[$format])) { $grouped[$format] = []; } $grouped[$format][] = $file; } // 遍历每个分组（类型），创建子目录 foreach ($grouped as $formatName => $fileList) { // 生成子文件夹名：FormatName_YYYYMMDD_HHMMSS $subFolderName = $formatName . '_' . date('Ymd') . '_' . date('His'); $subFolderName = common::sanitizeFilename($subFolderName); // 添加子文件夹（空目录） $zip->addEmptyDir($subFolderName); // 添加每个文件到对应子文件夹 $h_bol_num = array(); foreach ($fileList as $file) { if(array_key_exists($file['h_bol'], $h_bol_num)){ $h_bol_num[$file['h_bol']] += 1; }else{ $h_bol_num[$file['h_bol']] = 1; } $realPath = $file['file_path']; $extend = self::getExtendFilename($realPath); $fileName = $file['h_bol']."_".$file['standard']."_V".$h_bol_num[$file['h_bol']].".".$extend; $fileName = common::sanitizeFilename($fileName); if (file_exists($realPath) && is_file($realPath)) { $targetPath = $subFolderName . '/' . $fileName; $zip->addFile($realPath, $targetPath); } } } $zip->close(); // 输出下载 headers common::download_file($f, null, TRUE, null); } /** * 生成嵌套 ZIP 文件 * * @param array $files 文件列表 */ public static function downloadFilesAsSimapleZip($files,$zipName) { // 清除缓冲区，防止输出干扰 ZIP 文件 if (ob_get_level()) { ob_end_clean(); } $f = ONLINE_ROOT . DS . "upload" . DS . $zipName."_" . date('Y-m-dH\hi\ms\s', time()) . ".zip"; $zip = new ZipArchive(); if ($zip->open($f, ZipArchive::OVERWRITE) === TRUE || $zip->open($f, ZipArchive::CREATE) === TRUE) { } else { die("create ZIP faild。"); } foreach ($files as $file) { if (file_exists($file) && is_file($file)) { $zip->addFile($file, basename($file)); } } $zip->close(); // 读取 ZIP 文件内容并 Base64 编码 if (!file_exists($f)) { $data = array('msg' => 'ZIP file was not created.','data' => ''); common::echo_json_encode(500, $data); exit(); } $zipContent = file_get_contents($f); if ($zipContent === false) { $data = array('msg' => 'Failed to read ZIP file.','data' => ''); common::echo_json_encode(500, $data); exit(); } $base64Zip = base64_encode($zipContent); //删除临时 ZIP 文件 unlink($f); $data = array("msg"=>"success","filename" =>basename($f),"data"=>$base64Zip); common::echo_json_encode(200, $data); exit(); } /* * Get the file extension */ public static function getExtendFilename($file_name) { $extend = pathinfo($file_name); $extend = strtolower($extend["extension"]); return $extend; } public static function getManagement(){ //Management 自己配置需要创建表保存 $user_management_data = common::excuteObjectSql("select management,dashboard_filter from ra_online_user where user_login = '"._getLoginName()."'"); //为空,配置使用默认值 $user_management = $user_management_data['management']; if(empty($user_management)){ $Management = common::getdefaultManagement(); }else{ $Management = json_decode($user_management,true); foreach($Management as $key =>$val){ $Management[$key]['id'] = ($key +1); $Management[$key]['switchValue'] = $val['switchValue'] == "true" ? true : false; if ($val['title'] == "Revenue Spent"){ $Management[$key]['isRevenueDisplay'] = _isRevenueDisplay(); if(!_isRevenueDisplay()){ $Management[$key]['switchValue'] = false; } } } } $user_dashboard_filter = $user_management_data['dashboard_filter']; $dashboard_filter =array(); if(!empty($user_dashboard_filter)){ $dashboard_filter = json_decode($user_dashboard_filter,true); }else{ $threeMonthsAgoTimestamp = strtotime('-3 months'); $date_start = date('Y-m-d', $threeMonthsAgoTimestamp); $date_end = date('Y-m-d'); $date_start_two = date('m/d/Y', $threeMonthsAgoTimestamp); $date_end_two = date('m/d/Y'); //ETD $_12MonthsAgoTimestamp = strtotime('-11 months'); $etd_date_start = date('Y-m', $_12MonthsAgoTimestamp); $etd_date_end = date('Y-m'); $etd_date_start_two = date('m/Y', $_12MonthsAgoTimestamp); $etd_date_end_two = date('m/Y'); //柜子 $ctnr_date_start = date('Y-m', $_12MonthsAgoTimestamp); $ctnr_date_end = date('Y-m'); $ctnr_date_start_two = date('m/Y', $_12MonthsAgoTimestamp); $ctnr_date_end_two = date('m/Y'); //Top $top_date_start = date('Y-m-d', $_12MonthsAgoTimestamp); $top_date_end = date('Y-m-d'); $top_date_start_two = date('m/d/Y', $_12MonthsAgoTimestamp); $top_date_end_two = date('m/d/Y'); //KPIDefaulteData $dashboard_filter['KPIDefaulteData'] = array("transportation" =>array('All'),"date_type"=>"ETD","date_start"=>$date_start_two,"date_start_two"=>$date_start, "date_end"=>$date_end_two,"date_end_two"=>$date_end); //PendingDefaultData $dashboard_filter['PendingDefaultData'] = array("transportation" =>array('All'),"date_type"=>"ETD","date_start"=>"","date_start_two"=>"", "date_end"=>"","date_end_two"=>""); //RecentDefaultData $dashboard_filter['RecentDefaultData'] = array("transportation" =>array('All'),"date_type"=>"ETD","date_start"=>$date_start_two,"date_start_two"=>$date_start, "date_end"=>$date_end_two,"date_end_two"=>$date_end); //ETDDefaultData $dashboard_filter['ETDDefaultData'] = array("transportation" =>array('All'),"date_type"=>"ETA","date_start"=>$etd_date_start_two,"date_start_two"=>$etd_date_start, "date_end"=>$etd_date_end_two,"date_end_two"=>$etd_date_end); //ContainerefaultData $dashboard_filter['ContainerefaultData'] = array("transportation" =>array('Sea'),"date_type"=>"ETA","date_start"=>$ctnr_date_start_two,"date_start_two"=>$ctnr_date_start, "date_end"=>$ctnr_date_end_two,"date_end_two"=>$ctnr_date_end); //RevenueDefaultData if(_isRevenueDisplay()){ $dashboard_filter['RevenueDefaultData'] = array("transportation" =>array('All'),"date_type"=>"Invoice Issue Date","date_start"=>$ctnr_date_start_two,"date_start_two"=>$ctnr_date_start, "date_end"=>$ctnr_date_end_two,"date_end_two"=>$ctnr_date_end); } //Top10faultData $dashboard_filter['Top10faultData'] = array("transportation" =>array('All'),"date_type"=>"ETA","date_start"=>$top_date_start_two,"date_start_two"=>$top_date_start, "date_end"=>$top_date_end_two,"date_end_two"=>$top_date_end); //OriginCo2Top10faultData $dashboard_filter['OriginCo2Top10faultData'] = array("transportation" =>array('All'),"date_type"=>"ETA","date_start"=>$top_date_start_two,"date_start_two"=>$top_date_start, "date_end"=>$top_date_end_two,"date_end_two"=>$top_date_end); //DestinationCo2Top10faultData $dashboard_filter['DestinationCo2Top10faultData'] = array("transportation" =>array('All'),"date_type"=>"ETA","date_start"=>$top_date_start_two,"date_start_two"=>$top_date_start, "date_end"=>$top_date_end_two,"date_end_two"=>$top_date_end); } return array("Management" => $Management,"dashboard_filter" =>$dashboard_filter); } public static function getItemStyle($type,$code){ if($type == 'r1'){ $data = array("0-20 Days" =>"#ffc594", "20-40 Days" =>"#ff9e4c", "40-60 Days" =>"#ff7500", "60-80 Days" =>"#ff3d00", "Over 80 Days" =>"#d50000"); return $data[$code]; } if($type == 'r4' || $type == 'r3' || $type == 'atd_r4' || $type == 'ata_r3'){ $data = array("0 Day" =>"#ffc594", "Today" =>"#ffc594", "1-2 Days" =>"#ff9e4c", "3-6 Days" =>"#ff7500", "7 Days" =>"#ff3d00"); return $data[$code]; } if($type == 'r2' || utils::startWith($type,"co2e")){ $data = array("45" =>"#FFE3CC", "40" =>"#FFAC66", "20" =>"#FF7500", "air" =>"#FFE3CC", "sea" =>"#FFAC66", "road" =>"#FF7500"); return $data[$code]; } if($type == "top"){ $data = array("1" =>"#FF7500", "2" =>"#ff9033", "3" =>"#ff9e4d", "4" =>"#ffac66", "5" =>"#ffba80", "6" =>"#ffc899", "7" =>"#ffd6b3", "8" =>"#ffe3cc", "9" =>"#fff1e6", "10" =>"#fff1e6"); return $data[$code]; } } //处理返回原表数据格式 public static function mian_repot_do($value,$type,$totalValue){ $data = array(); $value_arr = json_decode($value,true); //r1 是 ETD to ETA (Days)圆形图表数据结构返回 if($type == 'r1'){ $ETDList = array(); foreach($value_arr as $arr){ $color = common::getItemStyle($type,$arr['name']); $ETDList[] = array("value" =>intval($arr['value']),"name" =>$arr['name'],"itemStyle" =>array("color" =>$color)); } // 排序依据的键数组（这里我们用一个数字数组来表示） $order = ["0-20 Days", "20-40 Days", "40-60 Days","60-80 Days","Over 80 Days"]; $ETDList = utils::arrayOrderBykeys($order,$ETDList,"name"); //$ETDList = array_reverse($ETDList); $ETD_Title = "Total: $totalValue"; $data = array("ETDList" =>$ETDList,"ETD_Radius"=>array('50%','80%'),"ETD_Title" =>$ETD_Title,"download_name" => "ETD to ETA (Days)"); } if($type == 'r4' || $type == 'r3'){ //重新整理一下数据，给UI一致 $data_kd = array(); foreach($value_arr as $arr){ if(stripos("Today", $arr['name']) !== false){ $color = common::getItemStyle($type,"Today"); if(empty($data_kd["0"])){ $data_kd["0"] = array("value" =>intval($arr['value']),"name" =>"≤1 Day","itemStyle" =>array("color" =>$color)); }else{ $data_kd["0"]["value"] = $data_kd["0"]["value"] + intval($arr['value']); } } if(stripos("+1 Days/+2 Days", $arr['name']) !== false){ $color = common::getItemStyle($type,"1-2 Days"); if(empty($data_kd["1"])){ $data_kd["1"] = array("value" =>intval($arr['value']),"name" =>"1-2 Days","itemStyle" =>array("color" =>$color)); }else{ $data_kd["1"]["value"] = $data_kd["1"]["value"] + intval($arr['value']); } } if(stripos("+3 Days/+4 Days/+5 Days/+6 Days", $arr['name']) !== false){ $color = common::getItemStyle($type,"3-6 Days"); if(empty($data_kd["2"])){ $data_kd["2"] = array("value" =>intval($arr['value']),"name" =>"3-6 Days","itemStyle" =>array("color" =>$color)); }else{ $data_kd["2"]["value"] = $data_kd["2"]["value"] + intval($arr['value']); } } if(stripos("+7 Days/Over 7 Days", $arr['name']) !== false){ $color = common::getItemStyle($type,"7 Days"); if(empty($data_kd["3"])){ $data_kd["3"] = array("value" =>intval($arr['value']),"name" =>"≥7 Days","itemStyle" =>array("color" =>$color)); }else{ $data_kd["3"]["value"] = $data_kd["3"]["value"] + intval($arr['value']); } } } // 排序依据的键数组（这里我们用一个数字数组来表示） $order = ["≤1 Day", "1-2 Days", "3-6 Days","≥7 Days"]; $data_kd = utils::arrayOrderBykeys($order,$data_kd,"name"); if($type == 'r4'){ $data = array("ETDList" =>$data_kd,"ETD_Radius"=>array('30%','50%'),"title1" =>"Departure","title2" =>"","download_name" => "Pending Departure"); } if($type == 'r3'){ $data = array("ETDList" =>$data_kd,"ETD_Radius"=>array('30%','50%'),"title1" =>"Arrival","title2" =>"","download_name" => "Pending Arrival"); } } return $data; } //单独处理co2e bar public static function getCo2eBar(){ //分担查询 include ONLINE_ROOT . 'libs' . DS . 'map_config.ini.php'; //新UI air sea road 目前只有sea $type = $_REQUEST["r_type"]; $sqlWhere = ' and ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]); $sqlWhere = " " . $sqlWhere; $date_type = strtolower($_REQUEST["date_type"]); if (isset($_REQUEST['date_start']) && !empty($_REQUEST['date_start'])) $sqlWhere .= " and $date_type >= '" . common::usDate2sqlDate($_REQUEST['date_start']) . " 00:00:00'"; if (isset($_REQUEST['date_end']) && !empty($_REQUEST['date_end'])) $sqlWhere .= " and $date_type <= '" . common::usDate2sqlDate($_REQUEST['date_end']) . " 23:59:59'"; $transportation = $_REQUEST["transportation"]; if(!is_array($transportation)){ $transportation = array($transportation); } $mode_param = ""; if(utils::count($transportation) == 1 && strtolower($transportation[0]) == 'all' ){ $mode_param = "'sea','air','road','rail'"; }else{ $transport_mode = utils::implode(';', $transportation); $mode_param = common::getInNotInSqlForSearch($transport_mode); } $sqlWhere .= " and transport_mode_extend in ($mode_param)"; $ContainerCount_Title = array(); //先查询总的排放量sea air road，确定排名后，在分别查询对应的sea air road //这里的问题跟 top 一样 。总的排放量查询有问题。目前是总的排放量 暂时等于 sea的排放量 if ($type == "co2e_orgin"){ $co2e_shippr_sql = "select SUM(COALESCE(carbon_emission,0)) as catnum , sum(case when (1<>1 or (transport_mode_extend='air')) then COALESCE(carbon_emission,0) else 0 end) as air_rc, sum(case when (1<>1 or (transport_mode_extend='sea')) then COALESCE(carbon_emission,0) else 0 end) as sea_rc, sum(case when (1<>1 or (transport_mode_extend='road')) then COALESCE(carbon_emission,0) else 0 end) as road_rc, sum(case when (1<>1 or (transport_mode_extend='rail')) then COALESCE(carbon_emission,0) else 0 end) as rail_rc, shippr_uncode as station from public.kln_ocean where 1=1 and COALESCE(shippr_uncode,'')<>'' and carbon_emission is not null $sqlWhere group by shippr_uncode order by catnum desc limit 10"; //$co2e_aLL = common::excuteListSql($co2e_shippr_sql); $co2e_aLL = $mapdb->GetAll($co2e_shippr_sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $co2e_shippr_sql), 0)); } if ($type == "co2e_destination"){ $co2e_consignee_sql = "select SUM(COALESCE(carbon_emission,0)) as catnum , sum(case when (1<>1 or (transport_mode_extend='air')) then COALESCE(carbon_emission,0) else 0 end) as air_rc, sum(case when (1<>1 or (transport_mode_extend='sea')) then COALESCE(carbon_emission,0) else 0 end) as sea_rc, sum(case when (1<>1 or (transport_mode_extend='road')) then COALESCE(carbon_emission,0) else 0 end) as road_rc, sum(case when (1<>1 or (transport_mode_extend='rail')) then COALESCE(carbon_emission,0) else 0 end) as rail_rc, consignee_uncode as station from public.kln_ocean where 1=1 and COALESCE(consignee_uncode,'')<>'' and carbon_emission is not null $sqlWhere group by consignee_uncode order by catnum desc limit 10"; //$co2e_aLL = common::excuteListSql($co2e_consignee_sql); $co2e_aLL = $mapdb->GetAll($co2e_consignee_sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $co2e_consignee_sql), 0)); } //最大Y值 $maxY = 0; $stations = array(); foreach($co2e_aLL as $val){ $ContainerCount_Title[] = $val['station']; $maxY = $maxY > $val['catnum'] ? $maxY : $val['catnum']; if(!empty($val['station'])){ $stations[] = $val['station']; } } // $station_str = utils::implode(',',$stations); $ContainerCounSeries = array(); $shipment_mode_arr = array("air","sea","road","rail"); foreach($shipment_mode_arr as $_shipment_mode){ //类型有 sea air road，目前之类只查询sea的，其他为空 $shipment_mode = $_shipment_mode; // $co2e = array(); if ($type == "co2e_orgin"){ // $co2e_shippr_sql_union = utils::_getSql($station_str,$type,$shipment_mode,$sqlWhere); // if(!empty($co2e_shippr_sql_union)){ // $co2e = common::excuteListSql($co2e_shippr_sql_union); // } $download_name = "CO2e Emission by Origin (Top 10)"; } if ($type == "co2e_destination"){ // $co2e_consignee_union = utils::_getSql($station_str,$type,$shipment_mode,$sqlWhere); // if(!empty($co2e_consignee_union)){ // $co2e = common::excuteListSql($co2e_consignee_union); // } $download_name = "CO2e Emission by Destination (Top 10)"; } $total = 0; $max = 0; $data = array(); foreach($co2e_aLL as $val){ $catnum = $shipment_mode."_rc"; $data[] = empty($val[$catnum])? 0 : round($val[$catnum], 4); $total = $total + $val[$catnum]; $max = $max < $val[$catnum] ? $val[$catnum] : $max; } $ContainerCounSeries[$shipment_mode] = array("data"=>$data,"total"=>$total,"max"=>$max); } //处理返回时数据格式 其实不太需要计算最大值，在最开始总量查询的时候以及查询出来 $ContainerCounSeries_return = array(); foreach($ContainerCounSeries as $k =>$v){ $color = common::getItemStyle($type,$k); $ContainerCounSeries_return[] = array("name"=>$k,"type"=>"bar","emphasis" => array("focus" =>"none"), "stack" =>"总计","data" =>$v['data'],"itemStyle" =>array("color" =>$color),'barWidth'=>'40'); } //处理返回原表数据格式 //计算刻度值 最小值是0，最大值是3000，刻度是500 interval $interval = utils::calculateTicks(0,$maxY,10); if($interval == 0){ //处理返回默认值 $interval = 1; } $returnData = array("ContainerCount_Title"=>"","ContainerCountList" =>$ContainerCount_Title,"ContainerCounSeries" =>$ContainerCounSeries_return, "min" => 0,"Max" =>$interval*10,"interval" =>$interval,"download_name" => $download_name); return $returnData; } public static function getTopBar(){ $sqlWhere = ' and ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]); $sqlWhere = " " . $sqlWhere; $date_type = strtolower($_REQUEST["date_type"]); if (isset($_REQUEST['date_start']) && !empty($_REQUEST['date_start'])) $sqlWhere .= " and $date_type >= '" . common::usDate2sqlDate($_REQUEST['date_start']) . " 00:00:00'"; if (isset($_REQUEST['date_end']) && !empty($_REQUEST['date_end'])) $sqlWhere .= " and $date_type <= '" . common::usDate2sqlDate($_REQUEST['date_end']) . " 23:59:59'"; $transportation = $_REQUEST["transportation"]; if(!is_array($transportation)){ $transportation = array($transportation); } $mode_param = ""; if(utils::count($transportation) == 1 && strtolower($transportation[0]) == 'all' ){ $mode_param = "'sea','air','road','rail'"; }else{ $transport_mode = utils::implode(';', $transportation); $mode_param = common::getInNotInSqlForSearch($transport_mode); } $sqlWhere .= " and transport_mode_extend in ($mode_param)"; $toporiginType = "shippr_uncode"; $shippr_uncode_10_sql ="with aa as (select count(shippr_uncode) as num,shippr_uncode, (array_agg(shipper_city))[1] AS shipper_city from public.kln_ocean where 1=1 and COALESCE(shippr_uncode,'')<>'' $sqlWhere group by shippr_uncode order by num desc limit 10) select aa.*,dd.* from aa left join LATERAL ( select lon as lng, lat as lat, '' as label, '' as infor, 3 as sort, null::timestamp without time zone as stime,''::text as ptype from vessel.vt_unlocode where (lat<=90 and lat>=-90) and (lon<=180 and lon>=-180) and lon is not null and lat is not null and uncode=aa.shippr_uncode ) dd on true"; $shippr_uncode_10 = common::excuteListSql($shippr_uncode_10_sql); //如果值没有：客户地址-->站点地址-->Port地址（POL/POD） if(utils::count($shippr_uncode_10) == 1 && empty($shippr_uncode_10[0]["shippr_uncode"])){ $toporiginType = "fport_of_loading_un"; $shippr_uncode_10_sql = "with aa as (select count(fport_of_loading_un) as num,fport_of_loading_un as shippr_uncode from public.kln_ocean where 1=1 and COALESCE(fport_of_loading_un,'')<>'' $sqlWhere group by fport_of_loading_un order by num desc limit 10) select aa.*,dd.* from aa left join LATERAL ( select lon as lng, lat as lat, '' as label, '' as infor, 3 as sort, null::timestamp without time zone as stime,''::text as ptype from vessel.vt_unlocode where (lat<=90 and lat>=-90) and (lon<=180 and lon>=-180) and lon is not null and lat is not null and uncode=aa.fport_of_loading_un ) dd on true"; $shippr_uncode_10 = common::excuteListSql($shippr_uncode_10_sql); } //$toporiginType = "fport_of_loading_un"; $topdestinationinType = "consignee_uncode"; $consignee_uncode_10_sql = "with aa as (select count(consignee_uncode) as num,consignee_uncode, (array_agg(consignee_city))[1] AS consignee_city from public.kln_ocean where 1=1 and COALESCE(consignee_uncode,'')<>'' $sqlWhere group by consignee_uncode order by num desc limit 10) select aa.*,dd.* from aa left join LATERAL ( select lon as lng, lat as lat, '' as label, '' as infor, 3 as sort, null::timestamp without time zone as stime,''::text as ptype from vessel.vt_unlocode where (lat<=90 and lat>=-90) and (lon<=180 and lon>=-180) and lon is not null and lat is not null and uncode=aa.consignee_uncode ) dd on true"; $consignee_uncode_10 = common::excuteListSql($consignee_uncode_10_sql); //如果值没有：客户地址-->站点地址-->Port地址（POL/POD） if(utils::count($consignee_uncode_10) == 1 && empty($consignee_uncode_10[0]["consignee_uncode"])){ $topdestinationinType = "mport_of_discharge_un"; $consignee_uncode_10_sql = "with aa as (select count(mport_of_discharge_un) as num,mport_of_discharge_un as consignee_uncode from public.kln_ocean where 1=1 and COALESCE(mport_of_discharge_un,'')<>'' $sqlWhere group by mport_of_discharge_un order by num desc limit 10) select aa.*,dd.* from aa left join LATERAL ( select lon as lng, lat as lat, '' as label, '' as infor, 3 as sort, null::timestamp without time zone as stime,''::text as ptype from vessel.vt_unlocode where (lat<=90 and lat>=-90) and (lon<=180 and lon>=-180) and lon is not null and lat is not null and uncode=aa.mport_of_discharge_un ) dd on true"; $consignee_uncode_10 = common::excuteListSql($consignee_uncode_10_sql); } $toporigin = array(); $toporiginMap = array(); $i = 0; $origiNunMax = 0; foreach($shippr_uncode_10 as $orgin){ $i = $i + 1; $map = array(); if(!empty($orgin['lat'])&&!empty($orgin['lng'])){ $map = array("lat" =>$orgin['lat'],"lng" =>$orgin['lng']); } //$json = '{"lng":121.8525,"lat":29.926545,"label":"'.$orgin['origin'].'","infor":"LAT KRABANG, THAILAND","sort":"0","stime":null,"ptype":"por"}'; //$map = json_decode($json,true); if(!empty($map)){ $toporiginMap[] = array("qandl"=>array(floatval($map['lat']),floatval($map['lng'])), "divIcon" => array("iconSize"=>0), "name" =>$orgin['shippr_uncode'], "color" =>common::getItemStyle("top",$i), "value" =>$orgin['num'], "textcolor" =>"#FFF"); } //查询uncode,对应的city $city = ""; if($toporiginType == "shippr_uncode"){ $city = $orgin['shipper_city']; } //$city 做假数据 $toporigin[] = array("name"=>$orgin['shippr_uncode'],"city_name"=>$city,"value"=>$orgin['num'],"color"=>common::getItemStyle("top",$i)); $origiNunMax = $origiNunMax < $orgin['num'] ? $orgin['num'] : $origiNunMax; } $topdestination = array(); $topdestinationinMap = array(); $i = 0; $agentiNunMax = 0; foreach($consignee_uncode_10 as $agent){ $i = $i + 1; $map = array(); if(!empty($agent['lat'])&&!empty($agent['lng'])){ $map = array("lat" =>$agent['lat'],"lng" =>$agent['lng']); } // $json = '{"lng":"100.78594000","lat":"13.68521000","label":"'.$agent['agent'].'","infor":"LAT KRABANG, THAILAND","sort":"0","stime":null,"ptype":"por"}'; // $map = json_decode($json,true); if(!empty($map)){ $topdestinationinMap[] = array("qandl"=>array(floatval($map['lat']),floatval($map['lng'])), "divIcon" => array("iconSize"=>0), "name" =>$agent['consignee_uncode'], "color" =>common::getItemStyle("top",$i), "value" =>$agent['num'], "textcolor" =>"#FFF"); } //查询uncode,对应的city $city = ""; if($topdestinationinType == "consignee_uncode"){ $city = $agent['consignee_city']; } //$city 做假数据 $topdestination[] = array("name"=>$agent['consignee_uncode'],"city_name"=>$city,"value"=>$agent['num'],"color"=>common::getItemStyle("top",$i)); $agentiNunMax = $agentiNunMax < $agent['num'] ? $agent['num'] : $agentiNunMax; } //处理返回原表数据格式 $interval = utils::calculateTicks(0,$origiNunMax,10); if($interval == 0){ //处理返回默认值 $interval = 1; } $dest_interval = utils::calculateTicks(0,$agentiNunMax,10); if($dest_interval == 0){ //处理返回默认值 $dest_interval = 1; } $returnData = array("seller_data_list_origin"=>$toporigin,"toporiginMap"=>array_reverse($toporiginMap),"toporiginType"=>$toporiginType, "seller_data_list_destination"=>$topdestination,"topdestinationinMap"=>array_reverse($topdestinationinMap),"topdestinationinType"=>$topdestinationinType, "min" => 0,"Max" =>$interval*10,"interval" =>$interval, "dest_min" => 0,"dest_Max" =>$dest_interval*10,"dest_interval" =>$dest_interval); return $returnData; } public static function getTopBarNew(){ //分担查询 include ONLINE_ROOT . 'libs' . DS . 'map_config.ini.php'; $sqlWhere = ' and ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]); $sqlWhere = " " . $sqlWhere; $date_type = strtolower($_REQUEST["date_type"]); if (isset($_REQUEST['date_start']) && !empty($_REQUEST['date_start'])) $sqlWhere .= " and $date_type >= '" . common::usDate2sqlDate($_REQUEST['date_start']) . " 00:00:00'"; if (isset($_REQUEST['date_end']) && !empty($_REQUEST['date_end'])) $sqlWhere .= " and $date_type <= '" . common::usDate2sqlDate($_REQUEST['date_end']) . " 23:59:59'"; $transportation = $_REQUEST["transportation"]; if(!is_array($transportation)){ $transportation = array($transportation); } $mode_param = ""; if(utils::count($transportation) == 1 && strtolower($transportation[0]) == 'all' ){ $mode_param = "'sea','air','road','rail'"; }else{ $transport_mode = utils::implode(';', $transportation); $mode_param = common::getInNotInSqlForSearch($transport_mode); } $sqlWhere .= " and transport_mode_extend in ($mode_param)"; $toporiginType = "shippr_uncode"; $shippr_uncode_10_sql ="with aa as (select count(shippr_uncode) as num,shippr_uncode, (array_agg(shipper_city))[1] AS shipper_city from public.kln_ocean where 1=1 and COALESCE(shippr_uncode,'')<>'' $sqlWhere group by shippr_uncode order by num desc limit 10) select aa.* from aa "; $shippr_uncode_10 = $mapdb->GetAll($shippr_uncode_10_sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $shippr_uncode_10_sql), 0)); //如果值没有：客户地址-->站点地址-->Port地址（POL/POD） if(utils::count($shippr_uncode_10) == 1 && empty($shippr_uncode_10[0]["shippr_uncode"])){ $toporiginType = "fport_of_loading_un"; $shippr_uncode_10_sql = "with aa as (select count(fport_of_loading_un) as num,fport_of_loading_un as shippr_uncode from public.kln_ocean where 1=1 and COALESCE(fport_of_loading_un,'')<>'' $sqlWhere group by fport_of_loading_un order by num desc limit 10) select aa.* from aa "; $shippr_uncode_10 = $mapdb->GetAll($shippr_uncode_10_sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $shippr_uncode_10_sql), 0)); } $topdestinationinType = "consignee_uncode"; $consignee_uncode_10_sql = "with aa as (select count(consignee_uncode) as num,consignee_uncode, (array_agg(consignee_city))[1] AS consignee_city from public.kln_ocean where 1=1 and COALESCE(consignee_uncode,'')<>'' $sqlWhere group by consignee_uncode order by num desc limit 10) select aa.* from aa"; $consignee_uncode_10 = $mapdb->GetAll($consignee_uncode_10_sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $consignee_uncode_10_sql), 0)); //如果值没有：客户地址-->站点地址-->Port地址（POL/POD） if(utils::count($consignee_uncode_10) == 1 && empty($consignee_uncode_10[0]["consignee_uncode"])){ $topdestinationinType = "mport_of_discharge_un"; $consignee_uncode_10_sql = "with aa as (select count(mport_of_discharge_un) as num,mport_of_discharge_un as consignee_uncode from public.kln_ocean where 1=1 and COALESCE(mport_of_discharge_un,'')<>'' $sqlWhere group by mport_of_discharge_un order by num desc limit 10) select aa.* from aa"; $consignee_uncode_10 = $mapdb->GetAll($consignee_uncode_10_sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $consignee_uncode_10_sql), 0)); } $toporigin = array(); $toporiginMap = array(); $i = 0; $origiNunMax = 0; foreach($shippr_uncode_10 as $orgin){ $i = $i + 1; $map =common::excuteObjectSql("select lon as lng, lat as lat, '' as label, '' as infor, 3 as sort, null::timestamp without time zone as stime,''::text as ptype from vessel.vt_unlocode where (lat<=90 and lat>=-90) and (lon<=180 and lon>=-180) and lon is not null and lat is not null and uncode='".$orgin['shippr_uncode']."'"); //$json = '{"lng":121.8525,"lat":29.926545,"label":"'.$orgin['origin'].'","infor":"LAT KRABANG, THAILAND","sort":"0","stime":null,"ptype":"por"}'; //$map = json_decode($json,true); if(!empty($map)){ $toporiginMap[] = array("qandl"=>array(floatval($map['lat']),floatval($map['lng'])), "divIcon" => array("iconSize"=>0), "name" =>$orgin['shippr_uncode'], "color" =>common::getItemStyle("top",$i), "value" =>$orgin['num'], "textcolor" =>"#FFF"); } //查询uncode,对应的city $city = ""; if($toporiginType == "shippr_uncode"){ $city = $orgin['shipper_city']; } //$city 做假数据 $toporigin[] = array("name"=>$orgin['shippr_uncode'],"city_name"=>$city,"value"=>$orgin['num'],"color"=>common::getItemStyle("top",$i)); $origiNunMax = $origiNunMax < $orgin['num'] ? $orgin['num'] : $origiNunMax; } $topdestination = array(); $topdestinationinMap = array(); $i = 0; $agentiNunMax = 0; foreach($consignee_uncode_10 as $agent){ $i = $i + 1; $map =common::excuteObjectSql("select lon as lng, lat as lat, '' as label, '' as infor, 3 as sort, null::timestamp without time zone as stime,''::text as ptype from vessel.vt_unlocode where (lat<=90 and lat>=-90) and (lon<=180 and lon>=-180) and lon is not null and lat is not null and uncode='".$agent['consignee_uncode']."' "); // $json = '{"lng":"100.78594000","lat":"13.68521000","label":"'.$agent['agent'].'","infor":"LAT KRABANG, THAILAND","sort":"0","stime":null,"ptype":"por"}'; // $map = json_decode($json,true); if(!empty($map)){ $topdestinationinMap[] = array("qandl"=>array(floatval($map['lat']),floatval($map['lng'])), "divIcon" => array("iconSize"=>0), "name" =>$agent['consignee_uncode'], "color" =>common::getItemStyle("top",$i), "value" =>$agent['num'], "textcolor" =>"#FFF"); } //查询uncode,对应的city $city = ""; if($topdestinationinType == "consignee_uncode"){ $city = $agent['consignee_city']; } //$city 做假数据 $topdestination[] = array("name"=>$agent['consignee_uncode'],"city_name"=>$city,"value"=>$agent['num'],"color"=>common::getItemStyle("top",$i)); $agentiNunMax = $agentiNunMax < $agent['num'] ? $agent['num'] : $agentiNunMax; } //处理返回原表数据格式 $interval = utils::calculateTicks(0,$origiNunMax,10); if($interval == 0){ //处理返回默认值 $interval = 1; } $dest_interval = utils::calculateTicks(0,$agentiNunMax,10); if($dest_interval == 0){ //处理返回默认值 $dest_interval = 1; } $returnData = array("seller_data_list_origin"=>$toporigin,"toporiginMap"=>array_reverse($toporiginMap),"toporiginType"=>$toporiginType, "seller_data_list_destination"=>$topdestination,"topdestinationinMap"=>array_reverse($topdestinationinMap),"topdestinationinType"=>$topdestinationinType, "min" => 0,"Max" =>$interval*10,"interval" =>$interval, "dest_min" => 0,"dest_Max" =>$dest_interval*10,"dest_interval" =>$dest_interval); return $returnData; } public static function getRevenue(){ //$data = '{"bar_title":"Total: 0","barList":["DEC,23","JAN,24","FEB,24","MAR,24","APR,24","MAY,24","JUN,24","JUL,24","AUG,24","SEP,24","OCT,24","NOV,24"],"barSeries":[{"name":"USD","type":"bar","data":[1,1,1,1,1,1,1,1,1,1,1,1],"emphasis":{"disabled":true,"focus":"none"},"itemStyle":{"color":"#FF7500","borderRadius":6},"label":{"show":false,"color":"#646A73","position":"top","fontFamily":"Lato-Light","hideWhenMouseLeave":false}},{"name":"THB","type":"bar","data":[2,2,2,2,2,2,2,2,2,2,2,2],"emphasis":{"disabled":true,"focus":"none"},"itemStyle":{"color":"#FFAC66","borderRadius":6},"label":{"show":false,"color":"#646A73","position":"top","fontFamily":"Lato-Light","hideWhenMouseLeave":false}},{"name":"SGD","type":"bar","data":[3,3,3,3,3,3,3,3,3,3,3,3],"emphasis":{"disabled":true,"focus":"none"},"itemStyle":{"color":"#FFE3CC","borderRadius":6},"label":{"show":false,"color":"#646A73","position":"top","fontFamily":"Lato-Light","hideWhenMouseLeave":false}}],"Max":5,"interval":1,"download_name":"Rvenue","isShowTooltips":true}'; //return json_decode($data,true); $date_from = null; $date_to = null; if (isset($_REQUEST['date_start']) && !empty($_REQUEST['date_start'])){ $date_start = common::dateFormatToYM($_REQUEST['date_start']); $date_from = $date_start."-01"; } if (isset($_REQUEST['date_end']) && !empty($_REQUEST['date_end'])){ $date_end = common::dateFormatToYM($_REQUEST['date_end']); $date_to = date('Y-m-t', strtotime($date_end."-01")); } $param = '{"user_login":"'._getLoginName().'","report_type":"ALL","date_from":"'.$date_from.'","date_to":"'.$date_to.'"}'; $sql = "SELECT * FROM get_customer_revenue_report('$param');FETCH ALL FROM r2;"; error_log("Revenue_sql:".$sql); $r2_data = common::excuteListSql($sql); //$r2_data = common::excuteListSql("select * from _test_data_table"); $barList = array(); $groupedItems = array(); foreach ($r2_data as $item) { if(!utils::in_array($item['month'],$barList)){ $barList[] = $item['month']; } $groupedItems[$item['currency']][] = $item; } //补齐所有货币的月份 foreach($groupedItems as $ckey =>$cvalueArr){ $currency = array(); foreach($barList as $month){ $currency[] = common::findCurrencyByMonth($month,$ckey,$cvalueArr); } $groupedItems[$ckey] = $currency; } //处理barSeries $barSeries = array(); //先准备4种颜色 $color = array("#FF7500","#FFAC66","#FFE3CC"); $index = 0; $max = 0; foreach($groupedItems as $currency => $series){ $series_max = 0; $num = $index % 3; $index = $index +1; $tem_series = array(); $tem_series["name"] = $currency; $tem_series["type"] = "bar"; $tem_series["emphasis"] = array("disabled"=>true,"focus"=>"none"); $tem_series["itemStyle"] = array("color"=>$color[$num],"borderRadius"=>6); $tem_series["label"] = array("show"=>false,"color"=>"#646A73","position"=>"top","fontFamily"=>"Lato-Light","hideWhenMouseLeave"=>false); //准备series data $data = array(); $total = 0; foreach($series as $_series){ $invoice_amount = empty($_series['invoice_amount'])? 0 : round($_series['invoice_amount'], 4); $data[]=$invoice_amount; $total = $total + $invoice_amount; $max = $max<$_series['invoice_amount'] ? $_series['invoice_amount'] : $max; $series_max = $series_max<$_series['invoice_amount'] ? $_series['invoice_amount'] : $series_max; } //计算Y坐标的间隔 每种货币的 $interval = utils::calculateTicks(0,$series_max,5); if($interval == 0){ //处理返回默认值 $interval = 2; } $tem_series["Max"] = $interval*5; $tem_series["interval"] = $interval; $tem_series["data"] = $data; $tem_series["total"] = floatval(number_format($total, 4, '.', '')); $barSeries[] = $tem_series; } //计算Y坐标的间隔 $interval = utils::calculateTicks(0,$max,5); if($interval == 0){ //处理返回默认值 $interval = 2; } $revenueBarData = array("bar_title" =>"Total:","barList" => $barList, "barSeries" =>$barSeries,"download_name"=>"Rvenue","isShowTooltips"=>true,"Max" =>$interval*5,"interval" =>$interval); return $revenueBarData; } public static function findCurrencyByMonth($month,$ckey,$cvalueArr){ $data = array("month" =>$month,"currency"=>$ckey,"invoice_amount"=>0,"sort_month"=>""); foreach($cvalueArr as $realmonth){ if($realmonth['month'] == $month){ $data = $realmonth; } } return $data; } public static function getdefaultManagement(){ $Management = array(); $Management[] = array("id"=>1 , "title"=>"KPI", "switchValue"=>true, "text"=>"Pie chart showing figures of shipments KPI of Departure and Arrival."); $Management[] = array("id"=>2 , "title"=>"Pending", "switchValue"=>true, "text"=>"Pie chart showing figures of shipments which are soon to depart/arrive (Calculated from ETD/ETA)."); $Management[] = array("id"=>3 , "title"=>"ETD to ETA (Days)", "switchValue"=>true, "text"=>"Pie chart showing figures of shipments which are soon to depart/arrive (Calculated from ETD/ETA)."); $Management[] = array("id"=>4 , "title"=>"Container Count", "switchValue"=>true, "text"=>"Pie chart showing figures of shipments which are soon to depart/arrive (Calculated from ETD/ETA)."); //if(_isRevenueDisplay()){ $Management[] = array("id"=>5 , "title"=>"Revenue Spent", "switchValue"=>_isRevenueDisplay(), "text"=>"Revenue data for the past 12 months.", "isRevenueDisplay"=>_isRevenueDisplay()); //} $Management[] = array("id"=>6 , "title"=>"Top 10 Origin/Destination", "switchValue"=>true, "text"=>"Figure of the top 10 origin/destination.", "title1"=>"Top 10 Origin", "title2"=>"Top 10 Destination"); $Management[] = array("id"=>7 , "title"=>"CO2e Emission by Origin (Top 10)", "switchValue"=>true, "text"=>"Figure of the CO2e Emission by origin."); $Management[] = array("id"=>8 , "title"=>"CO2e Emission by Destination (Top 10)", "switchValue"=>true, "text"=>"Figure of the CO2e Emission by destination."); $Management[] = array("id"=>9 , "title"=>"Recent Status", "switchValue"=>true, "text"=>"A shipment list with latest status update on top."); return $Management; } /** * Destroy Session */ public static function sessionDestroy() { session_destroy(); setcookie(session_name(), '', time() - 3600); $_SESSION = array(); } public static function dateFormatToYM($dateString){ $date = DateTime::createFromFormat('m/Y', $dateString); $formattedDate = $date->format('Y-m'); // YYYY-MM return $formattedDate; } public static function retStationInfo($address_1,$address_2,$address_3,$address_4,$city,$state,$country,$zipcode){ $stationInfo = ""; if(!empty($address_1)){ $stationInfo .= $address_1; } if(!empty($address_2)){ $stationInfo .= " ".$address_2; } if(!empty($address_3)){ $stationInfo .= " ".$address_3; } if(!empty($address_4)){ $stationInfo .= " ".$address_4; } $temp_str = ""; if(!empty($city)){ $temp_str .= $city." "; } if(!empty($state)){ $temp_str .= $state." "; } if(!empty($zipcode)){ $temp_str .= $zipcode." "; } if(!empty($country)){ $temp_str .= $country." "; } if(!empty($temp_str)){ return $stationInfo." ".trim($temp_str); } return $stationInfo; } public static function getInsertSqlNull($table_name, $values) { $field = ""; $value = ""; foreach ($values as $k => $v) { if ($k == 'tmp' || $k == 'action' || $k == 'operate' || $k == 'x' || $k == 'y') { continue; } if (is_array($v)) { $v = utils::implode(",", $v); } if (empty($field)) { $field = $k; if (utils::checkExist($v, 'now()')) { $value = $v; } elseif ($v == null) { $value .= 'null'; } elseif ($v == 'TRUE' || $v == 'FALSE') { $value .= $v; } else { $value = '\'' . common::check_input($v) . '\''; } } else { $field .= ',' . $k; if (utils::checkExist($v, 'now()')) $value .= ', now()'; elseif ($v == null) { $value .= ',null'; } else if ($v == 'TRUE' || $v == 'FALSE') { $value .= ',' . $v; } else { $value .= ', \'' . common::check_input($v) . '\''; } } } return 'insert into ' . $table_name . '(' . $field . ') values (' . $value . ')'; } //密码规则验证 public static function checkPasswordRule($login, $new_password){ $sql="select item_value from config where item='passwordCheckRules'"; $rs = common::excuteObjectSql($sql); $str = ""; if (!empty($rs)) { $passwordCheckRules = json_decode($rs["item_value"],true); //校验使用次数 if (!empty($passwordCheckRules["pastPasswordCheckNum"])) { $sql = "select password from public.ra_online_user_password_history where lower(user_login)='".common::check_input(strtolower($login))."' order by id desc limit ".$passwordCheckRules["pastPasswordCheckNum"]; $passwords = common::excuteListSql($sql); foreach ($passwords as $pwd) { if ($pwd['password'] == $new_password) { $str = "This password has been recently used"; } } } if(empty($str)){ $str = utils::checkPassword($new_password,$passwordCheckRules,$login); } }else{ $str = utils::checkPassword($new_password); } return $str; } public static function getMilestonesInfo($ocean,$transport_mode,$_schemas,$EDI315TimeAndLocation=array()){ //Milestones info列名固定 $Milestones_column = array(); $Milestones_column[] = array("title" =>"Milestones","field" =>"milestones","formatter" =>"normal","type" =>"normal"); $Milestones_column[] = array("title" =>"Date Time","field" =>"date_time","formatter" =>"dateTime","type" =>"normal"); $Milestones_column[] = array("title" =>"Locations","field" =>"locations","formatter" =>"normal","type" =>"normal"); $Milestones_column[] = array("title" =>"Remarks","field" =>"remarks","formatter" =>"normal","type" =>"normal"); //EDI315 有关联查询，以那个为准。但是如果是手工录入或者不是EDI 进去的Milestone的情况，这以这个为准 $Milestones_NO_Mapping = common::getMilestones_NO_Mapping_Data($ocean); //Milestones 数据信息待定 $Milestones_data = array(); if($transport_mode == 'sea'){ $Milestones_data_arr = common::excuteListSql("select sn.description,act_date||' '||COALESCE(act_time,'') as date_time, remark,timezone,a.code from public.ocean_milestone a inner join public.customer_service_milestone_sno sn on sn.code=a.code and sn.type = 'sea' where a.serial_no='".$ocean["serial_no"]."' and act_date is not null order by sn.sno asc"); }else{ $Milestones_data_arr = common::excuteListSql("select sn.description,act_date||' '||COALESCE(act_time,'') as date_time, remark,timezone,a.code from $_schemas.air_milestone a inner join public.customer_service_milestone_sno sn on sn.code=a.code and sn.type = 'air' where a.serial_no='".$ocean["serial_no"]."' and act_date is not null order by sn.sno asc"); } foreach($Milestones_data_arr as $mda){ $timezone = ""; $IFFICC_locations =""; if(!empty($EDI315TimeAndLocation[$mda['code']])){ $edi315Info = $EDI315TimeAndLocation[$mda['code']]; if(!empty($edi315Info)){ $timezone = $edi315Info['timezone']; } //IFFICC 比较特殊用EDI的locations if($mda['code'] == "IFFICC"){ $IFFICC_locations = $edi315Info['location']; } }else{ $edi315Info = $Milestones_NO_Mapping[$mda['code']]; if(!empty($edi315Info)){ $timezone = $edi315Info['timezone']; } } //locations 以配置的信息为准 $locations = ""; $edi315Info = $Milestones_NO_Mapping[$mda['code']]; if(!empty($edi315Info)){ $locations = $edi315Info['location']; if($mda['code'] == "IFFICC" && !empty($IFFICC_locations)){ $locations = $IFFICC_locations; } } $Milestones_data[] = array("milestones"=>$mda['description'],"date_time"=>$mda['date_time'],"timezone" =>$timezone, "locations" => $locations, "remarks" =>$mda['remark']); } $Milestones = array("Milestones_column"=>$Milestones_column,"Milestones_data" =>$Milestones_data); return $Milestones; } /** * 查询uncode 对应的时区 */ public static function getCityPortsInfo($uncodes){ $data =array(); $sql = "select time_zone as timezonecode,uncode as ctrycitycode from public.city_timezone where lower(uncode) ". common::getInNotInSql($uncodes); $uncode_rs = common::excuteListSql($sql); foreach($uncode_rs as $value){ $data[$value['ctrycitycode']] = $value['timezonecode']; } return $data; } /** * EDI315 有关联查询，以那个为准。但是如果是手工录入或者没有对应的情况，这以这个为准 */ public static function getMilestones_NO_Mapping_Data($ocean){ $Milestones_NO_Mapping = array(); //取origin $ocean['aa_city'] 不是必需,对应文件里未提location来源 $Milestones_NO_Mapping['IFFBCF'] = array("code"=>"origin","timezone"=>$ocean['aa_timezone'],"location"=>""); $Milestones_NO_Mapping['IFFBCR'] = array("code"=>"origin","timezone"=>$ocean['aa_timezone'],"location"=>""); $Milestones_NO_Mapping['IFFCPU'] = array("code"=>"origin","timezone"=>$ocean['aa_timezone'],"location"=>""); $Milestones_NO_Mapping['IFFSIL'] = array("code"=>"origin","timezone"=>$ocean['aa_timezone'],"location"=>""); $Milestones_NO_Mapping['IFFEDO'] = array("code"=>"origin","timezone"=>$ocean['aa_timezone'],"location"=>""); $Milestones_NO_Mapping['IFFECC'] = array("code"=>"origin","timezone"=>$ocean['aa_timezone'],"location"=>""); //Place of Receipt $Milestones_NO_Mapping['IFFREC'] = array("code"=>"por/pol","timezone"=>$ocean['por_timezone'],"location"=>$ocean['por_uncity']); $Milestones_NO_Mapping['IFFECP'] = array("code"=>"por/pol","timezone"=>$ocean['por_timezone'],"location"=>$ocean['por_uncity']); //Port of loading $Milestones_NO_Mapping['IFFONB'] = array("code"=>"por/pol","timezone"=>$ocean['pol_timezone'],"location"=>$ocean['pol_uncity']); $Milestones_NO_Mapping['IFFDEP'] = array("code"=>"por/pol","timezone"=>$ocean['pol_timezone'],"location"=>$ocean['pol_uncity']); //取destination $ocean['dd_city']不是必需 $Milestones_NO_Mapping['IFFCSN'] = array("code"=>"destination","timezone"=>$ocean['dd_timezone'],"location"=>""); $Milestones_NO_Mapping['IFFIDO'] = array("code"=>"destination","timezone"=>$ocean['dd_timezone'],"location"=>""); //取Port of discharge $Milestones_NO_Mapping['IFFARR'] = array("code"=>"mpod","timezone"=>$ocean['mpod_timezone'],"location"=>$ocean['mpod_uncity']); $Milestones_NO_Mapping['IFFUND'] = array("code"=>"mpod/pod/fd","timezone"=>$ocean['mpod_timezone'],"location"=>$ocean['mpod_uncity']); $Milestones_NO_Mapping['IFFICC'] = array("code"=>"mpod","timezone"=>$ocean['mpod_timezone'],"location"=>$ocean['mpod_uncity']); //Place of Delivery $Milestones_NO_Mapping['IFFAFD'] = array("code"=>"pod","timezone"=>$ocean['pod_timezone'],"location"=>$ocean['pod_uncity']); $Milestones_NO_Mapping['IFFCTA'] = array("code"=>"pod/mpod","timezone"=>$ocean['pod_timezone'],"location"=>$ocean['pod_uncity']); $Milestones_NO_Mapping['IFFADW'] = array("code"=>"pod","timezone"=>$ocean['pod_timezone'],"location"=>$ocean['pod_uncity']); $Milestones_NO_Mapping['IFFDDW'] = array("code"=>"pod","timezone"=>$ocean['pod_timezone'],"location"=>$ocean['pod_uncity']); $Milestones_NO_Mapping['IFFPPD'] = array("code"=>"mpod/pod/fd","timezone"=>$ocean['pod_timezone'],"location"=>$ocean['pod_uncity']); $Milestones_NO_Mapping['IFFECR'] = array("code"=>"mpod/pod/fd","timezone"=>$ocean['pod_timezone'],"location"=>$ocean['pod_uncity']); //final_destination $ocean['fd_city']不是必需 $Milestones_NO_Mapping['IFFDEL'] = array("code"=>"fd","timezone"=>$ocean['_fd_timezone'],"location"=>""); $Milestones_NO_Mapping['IFFHBL'] = array("code"=>"fd","timezone"=>$ocean['_fd_timezone'],"location"=>""); return $Milestones_NO_Mapping; } /** * 这个详细查询已经带模式了 */ public static function getEDI315StatusForDetail($serial_no){ $data = array(); $ctnr_sql = "SELECT oc.ctnr, oc.serial_no,oc.size FROM oc_container oc LEFT JOIN ocean o ON oc.serial_no = o.serial_no WHERE o.serial_no='$serial_no'"; $ctnr_data = common::excuteListSql($ctnr_sql); $containerStatusData = array(); $VD = ""; $VA = ""; $VD_RELAY = ""; $VA_RELAY = ""; foreach ($ctnr_data as $cd){ //存在柜号为空的数据情况 if(empty($cd['ctnr'])){ continue; } //(select uncity from public.ports where uncode = s.event_code) as uncity, 按新版的逻辑这个先取消掉 $ctnr_status_sql = "select s.source_id, s.event_base as event, to_char(to_timestamp(s.event_date, 'YYYYMMDD'), 'YYYY-MM-DD') as eventdate, to_char(to_timestamp(s.event_time, 'HH24MI'), 'HH24:MI') as eventtime, e.description,s.event_type as eventtype, s.event_code as eventcode, s.event_city as eventcity, (select time_zone from public.city_timezone where uncode = s.event_code) as timezone, s.event_city as uncity, case when s.event_base ='I' or s.event_base ='TR' then 'IFFREC'::text when s.event_base ='AE' then 'IFFONB'::text when s.event_base ='VD' then 'IFFDEP'::text when s.event_base ='EB' or s.event_base ='VA' then 'IFFARR'::text when s.event_base ='UV' then 'IFFUND'::text when s.event_base ='VA' then 'IFFAFD'::text when s.event_base ='AV' then 'IFFCTA'::text when s.event_base ='CT' then 'IFFICC'::text when s.event_base ='OA' or s.event_base ='D' then 'IFFPPD'::text when s.event_base ='RD' then 'IFFECR'::text when s.event_base ='EE' then 'IFFECP'::text else '' ::text end as milestone_code from public.ra_online_container_status_v s left join public.ra_online_edi_event e on s.event_base = e.ra_name where s.serial_no = '" . pg_escape_string($cd['serial_no']) . "' and s.container_no = '" . pg_escape_string($cd['ctnr']) . "' and is_display = true order by to_timestamp(s.event_date, 'YYYYMMDD') desc, to_timestamp(s.event_time, 'HH24MI') desc,e.ra_order desc"; $ctnr_status = common::excuteListSql($ctnr_status_sql); //Container_Status　新版只显示几个状态， CTNR# EE Ｉ AE VD VA $container_status_column = array("EE","I","AE","VD","VA"); $containerStatusDataTemp = array(); $content = array(); //按顺序处理 新版只显示几个状态 //foreach($container_status_column as $status){ foreach($ctnr_status as $event){ //if(strtolower($event['event']) == strtolower($status)){ $eventdate = $event["eventdate"]; if (empty($event["source_id"]) || $event["source_id"]==0) { $eventdate.= " " . $event["eventtime"]; } $location_code = $event["eventcode"]; $content[] = array("code" =>$event['eventtype']."-".$location_code,"title" =>$event['description'],"date"=>$eventdate,"country"=>$location_code,"timezone"=>$event['timezone'], "uncity"=>$event['uncity']); if(strtolower($event['event']) == "vd" && !empty($eventdate)){ $VD = $eventdate; } if(strtolower($event['event']) == "va" && !empty($eventdate)){ $VA = $eventdate; } if(strtolower($event['event']) == strtolower("VD_RELAY") && !empty($eventdate)){ $VD_RELAY = $eventdate; } if(strtolower($event['event']) == strtolower("VA_RELAY") && !empty($eventdate)){ $VA_RELAY = $eventdate; } //} } //} //记录所有的信息 $EDI315TimeAndLocation = array(); foreach($ctnr_status as $event){ if(!empty($EDI315TimeAndLocation['IFFARR']) && $EDI315TimeAndLocation['IFFARR']['code'] == "EB"){ //如果存在EB 的EB 的优先级最高 continue; } if(!empty($EDI315TimeAndLocation['IFFPPD']) && $EDI315TimeAndLocation['IFFARR']['code'] == "OA"){ //如果存在OA 的OA 的优先级最高 continue; } $EDI315TimeAndLocation[$event['milestone_code']] = array("code"=>$event['event'],"timezone"=>$event['timezone'],"location"=>$event['uncity']); } $containerStatusDataTemp['label'] = $cd['ctnr']." / ".$cd['size']; $containerStatusDataTemp['ctnr_label'] = $cd['ctnr']; $containerStatusDataTemp['content'] = $content; $containerStatusData[] = $containerStatusDataTemp; } $data['containerStatusData'] = $containerStatusData; $data['EDI315TimeAndLocation'] = $EDI315TimeAndLocation; $data['VD'] = $VD; $data['VA'] = $VA; $data['VD_RELAY'] = $VD_RELAY; $data['VA_RELAY'] = $VA_RELAY; return $data; } public static function getEDI315Time($serial_no,$_schemas){ //Timezone From 来自于EDI315 $ctnr_sql = "SELECT oc.ctnr, oc.serial_no,oc.size FROM $_schemas.oc_container oc LEFT JOIN $_schemas.ocean o ON oc.serial_no = o.serial_no WHERE o.serial_no='$serial_no'"; $ctnr_data = common::excuteListSql($ctnr_sql); $EDI315TimeAndLocation = array(); foreach ($ctnr_data as $cd){ //存在柜号为空的数据情况 if(empty($cd['ctnr'])){ continue; } $ctnr_status_sql = "select (select time_zone from public.city_timezone where uncode = s.event_code) as timezone, case when s.event_base ='I' or s.event_base ='TR' then 'IFFREC'::text when s.event_base ='AE' then 'IFFONB'::text when s.event_base ='VD' then 'IFFDEP'::text when s.event_base ='EB' or s.event_base ='VA' then 'IFFARR'::text when s.event_base ='UV' then 'IFFUND'::text when s.event_base ='VA' then 'IFFAFD'::text when s.event_base ='AV' then 'IFFCTA'::text when s.event_base ='CT' then 'IFFICC'::text when s.event_base ='OA' or s.event_base ='D' then 'IFFPPD'::text when s.event_base ='RD' then 'IFFECR'::text when s.event_base ='EE' then 'IFFECP'::text else '' ::text end as milestone_code from public.ra_online_container_status_v s left join public.ra_online_edi_event e on s.event_base = e.ra_name where s.serial_no = '" . pg_escape_string($cd['serial_no']) . "' and s.container_no = '" . pg_escape_string($cd['ctnr']) . "' and is_display = true order by to_timestamp(s.event_date, 'YYYYMMDD') asc, to_timestamp(s.event_time, 'HH24MI') asc,e.ra_order asc"; $ctnr_status = common::excuteListSql($ctnr_status_sql); //记录所有的信息、替换最后一个柜子 $EDI315TimeAndLocation = array(); foreach($ctnr_status as $event){ if(!empty($EDI315TimeAndLocation['IFFARR']) && $EDI315TimeAndLocation['IFFARR']['code'] == "EB"){ //如果存在EB 的EB 的优先级最高 continue; } if(!empty($EDI315TimeAndLocation['IFFPPD']) && $EDI315TimeAndLocation['IFFARR']['code'] == "OA"){ //如果存在OA 的OA 的优先级最高 continue; } $EDI315TimeAndLocation[$event['milestone_code']] = array("code"=>$event['event'],"timezone"=>$event['timezone']); } } return $EDI315TimeAndLocation; } public static function getMilestoneTimeAndlocations($milestone_code,$transport_mode,$serial_no,$order_from){ $EDI315TimeAndLocation = array(); if($transport_mode == "sea"){ $ctnr_sql = "SELECT oc.ctnr, oc.serial_no,oc.size FROM $order_from.oc_container oc LEFT JOIN ocean o ON oc.serial_no = o.serial_no WHERE o.serial_no='$serial_no'"; $ctnr_data = common::excuteListSql($ctnr_sql); foreach ($ctnr_data as $cd){ //存在柜号为空的数据情况 if(empty($cd['ctnr'])){ continue; } $ctnr_status_sql = "select s.source_id, s.event_base as event, to_char(to_timestamp(s.event_date, 'YYYYMMDD'), 'YYYY-MM-DD') as eventdate, to_char(to_timestamp(s.event_time, 'HH24MI'), 'HH24:MI') as eventtime, e.description,s.event_type as eventtype, s.event_code as eventcode, s.event_city as eventcity, (select time_zone from public.city_timezone where uncode = s.event_code) as timezone, (select uncity from public.ports where uncode = s.event_code) as uncity, case when s.event_base ='I' then 'IFFREC'::text when s.event_base ='AE' then 'IFFONB'::text when s.event_base ='VD' then 'IFFDEP'::text when s.event_base ='EB' or s.event_base ='VA' then 'IFFARR'::text when s.event_base ='UV' then 'IFFUND'::text when s.event_base ='VA' then 'IFFAFD'::text when s.event_base ='AV' then 'IFFCTA'::text when s.event_base ='CT' then 'IFFICC'::text when s.event_base ='OA' or s.event_base ='D' then 'IFFPPD'::text when s.event_base ='EE' then 'IFFECP'::text else '' ::text end as milestone_code from public.ra_online_container_status_v s left join ra_online_edi_event e on s.event_base = e.ra_name where s.serial_no = '" . pg_escape_string($cd['serial_no']) . "' and s.container_no = '" . pg_escape_string($cd['ctnr']) . "' and is_display = true order by to_timestamp(s.event_date, 'YYYYMMDD') asc, to_timestamp(s.event_time, 'HH24MI') asc,e.ra_order asc"; $ctnr_status = common::excuteListSql($ctnr_status_sql); //记录所有的信息 $EDI315TimeAndLocation = array(); foreach($ctnr_status as $event){ if(!empty($EDI315TimeAndLocation['IFFARR']) && $EDI315TimeAndLocation['IFFARR']['code'] == "EB"){ //如果存在EB 的EB 的优先级最高 continue; } if(!empty($EDI315TimeAndLocation['IFFPPD']) && $EDI315TimeAndLocation['IFFARR']['code'] == "OA"){ //如果存在OA 的OA 的优先级最高 continue; } $EDI315TimeAndLocation[$event['milestone_code']] = array("code"=>$event['event'],"timezone"=>$event['timezone'],"location"=>$event['uncity']); } } } //当前数据的所有time city 查询 $sql = common::trackingSql($serial_no,$order_from); $data_arr = common::excuteListSql($sql); $data = $data_arr[0]; //EDI315 有关联查询，以那个为准。但是如果是手工录入或者不是EDI 进去的Milestone的情况，这以这个为准 $Milestones_NO_Mapping = common::getMilestones_NO_Mapping_Data($data); $timezone = ""; $IFFICC_locations =""; if(!empty($EDI315TimeAndLocation[$milestone_code])){ $edi315Info = $EDI315TimeAndLocation[$milestone_code]; if(!empty($edi315Info)){ $timezone = $edi315Info['timezone']; } //IFFICC 比较特殊用EDI的locations if($milestone_code == "IFFICC"){ $IFFICC_locations = $edi315Info['location']; } }else{ $edi315Info = $Milestones_NO_Mapping[$milestone_code]; if(!empty($edi315Info)){ $timezone = $edi315Info['timezone']; } } //locations 以配置的信息为准 $locations = ""; $edi315Info = $Milestones_NO_Mapping[$milestone_code]; if(!empty($edi315Info)){ $locations = $edi315Info['location']; if($milestone_code == "IFFICC" && !empty($IFFICC_locations)){ $locations = $IFFICC_locations; } } return array("timezone" =>$timezone,"locations" =>$locations); } public static function trackingSql($serial_no,$order_from){ $_schemas = $order_from; if($_schemas == 'public'){ $_schemas = "ocean"; } $sql = "with o as( SELECT oo.*,m_bol as _m_bol, h_bol as _h_bol, (select time_zone from public.city_timezone where uncode = oo.fport_of_loading_un limit 1) as pol_timezone, case when oo.transport_mode ='sea' then (select uncity from $order_from.ports where uncode = oo.fport_of_loading_un limit 1) else (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.fport_of_loading_un limit 1) end as pol_uncity, (select time_zone from public.city_timezone where uncode = oo.mport_of_discharge_un limit 1) as mpod_timezone, case when oo.transport_mode ='sea' then (select uncity from $order_from.ports where uncode = oo.mport_of_discharge_un limit 1) else (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.mport_of_discharge_un limit 1) end as mpod_uncity, (select time_zone from public.city_timezone where uncode = oo.place_of_receipt_un limit 1) as por_timezone, case when oo.transport_mode ='sea' then (select uncity from $order_from.ports where uncode = oo.place_of_receipt_un limit 1) else (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.place_of_receipt_un limit 1) end as por_uncity, (select time_zone from public.city_timezone where uncode = oo.place_of_delivery_un limit 1) as pod_timezone, case when oo.transport_mode ='sea' then (select uncity from $order_from.ports where uncode = oo.place_of_delivery_un limit 1) else (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.place_of_delivery_un limit 1) end as pod_uncity, (select time_zone from public.city_timezone where uncode = oo.final_desination_uncode limit 1) as _fd_timezone, case when oo.transport_mode ='sea' then (select uncity from $order_from.ports where uncode = oo.final_desination_uncode limit 1) else (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.final_desination_uncode limit 1) end as _pd_uncity, CASE WHEN ((m_iffbcf is not null or m_iffbcf is null) and m_iffcpu is null and m_iffrec is null and m_iffdep is null and m_iffarr is null and m_iffdel is null) THEN 'Created'::text WHEN ((m_iffcpu is not null or m_iffrec is not null) and m_iffdep is null and m_iffarr is null and m_iffdel is null) THEN 'Cargo Received'::text WHEN (m_iffdep is not null and m_iffarr is null and m_iffdel is null) THEN 'Departure'::text WHEN (m_iffarr is not null and m_iffdel is null) THEN 'Arrived'::text WHEN (m_iffdel is not null) THEN 'Completed'::text ELSE 'Created'::text END AS new_status from public.kln_ocean oo where oo.serial_no = '" . $serial_no . "' and oo.order_from = '$order_from' ) SELECT o.* ,sh.*, cn.*,aa.*,dd.*,fd.* from o LEFT JOIN LATERAL ( SELECT company as cn_company, address_1 as cn_address_1, address_2 as cn_address_2, address_3 as cn_address_3, address_4 as cn_address_4, city as cn_city, state as cn_state, zipcode as cn_zipcode, country as cn_country, phone_1 as cn_phone FROM $_schemas.contacts c WHERE o.consignee_id::text = c.contact_id::text) cn ON true LEFT JOIN LATERAL ( SELECT company as sh_company, address_1 as sh_address_1, address_2 as sh_address_2, address_3 as sh_address_3, address_4 as sh_address_4, city as sh_city, state as sh_state, zipcode as sh_zipcode, country as sh_country, phone_1 as sh_phone FROM $_schemas.contacts c WHERE o.shipper_id::text = c.contact_id::text) sh ON true LEFT JOIN LATERAL ( SELECT company as aa_company, address_1 as aa_address_1, address_2 as aa_address_2, address_3 as aa_address_3, address_4 as aa_address_4, city as aa_city, state as aa_state, zipcode as aa_zipcode, country as aa_country, phone_1 as aa_phone, (select time_zone from public.city_timezone where uncode = LEFT(c.country, 2) || COALESCE(c.city_code,'') limit 1) as aa_timezone FROM $_schemas.contacts c WHERE o.origin::text = c.contact_id::text) aa ON true LEFT JOIN LATERAL ( SELECT company as dd_company, address_1 as dd_address_1, address_2 as dd_address_2, address_3 as dd_address_3, address_4 as dd_address_4, city as dd_city, state as dd_state, zipcode as dd_zipcode, country as dd_country, phone_1 as dd_phone, (select time_zone from public.city_timezone where uncode = LEFT(c.country, 2) || COALESCE(c.city_code,'') limit 1) as dd_timezone FROM $_schemas.contacts c WHERE o.agent::text = c.contact_id::text) dd ON true LEFT JOIN LATERAL ( SELECT city as fd_city, (select time_zone from public.city_timezone where uncode = LEFT(c.country, 2) || COALESCE(c.city_code,'') limit 1) as fd_timezone FROM $_schemas.contacts c WHERE o.final_desination::text = c.contact_id::text) fd ON true"; return $sql; } public static function getDashboardTransportationSqlWhere(){ $transportation = $_REQUEST["transportation"]; if(!is_array($transportation)){ $transportation = array($transportation); } $mode_param = ""; if(utils::count($transportation) == 1 && strtolower($transportation[0]) == 'all' ){ $mode_param = "'sea','air','road','rail'"; }else{ $transport_mode = utils::implode(';', $transportation); $mode_param = common::getInNotInSqlForSearch($transport_mode); } return " and transport_mode_extend in ($mode_param)"; } /* * Create Directory */ public static function mkdirs($path, $mode = 0777) { //creates directory tree recursively if (!file_exists($path)) { if (!mkdir($path, $mode, TRUE)) return FALSE; } return TRUE; } /** * checked is_subscribe */ public static function checkedSubscribe($serial_no) { //creates directory tree recursively $is_subscribe_exist = common::excuteOneSql("select user_login from public.kln_user_subscribed us where lower(us.user_login) = '".strtolower(_getLoginName())."' and us.subscribed_serial_no = '".$serial_no."'"); if(empty($is_subscribe_exist)){ $is_subscribe = false; }else{ $is_subscribe = true; } return $is_subscribe; } /** * get webiste */ public static function getWebiste($carrier) { $website = ""; if (!empty($carrier)){ $website = common::excuteOneSql("select website from vessel_company where scac = '$carrier'"); } if (strpos($website, ";") === FALSE){ }else{ $site = explode(";", $website); $website = $site[0]; } return $website; } public static function getWeek($week){ if ($week == "0"){ $week = "Sunday"; } elseif ($week == "1"){ $week = "Monday"; } elseif ($week == "2"){ $week = "Tuesday"; } elseif ($week == "3"){ $week = "Wednesday"; } elseif ($week == "4"){ $week = "Thursday"; } elseif ($week == "5"){ $week = "Friday"; } elseif ($week == "6"){ $week = "Sunday"; } return $week; } /** * 这里基准event 写死, 根据online查询页面的通用的来, 这里需提问确定 */ public static function getEDICtnrEvent(){ $event =array(array("event_name"=>"EE","description"=>"Empty Equipment Dispatched"), array("event_name"=>"I","description"=>"Gate in full for a booking"), array("event_name"=>"AE","description"=>"Container loaded on vessel"), array("event_name"=>"VD","description"=>"Vessel Departure"), array("event_name"=>"VA_RELAY","description"=>"Arrive Relay Port"), array("event_name"=>"UV_RELAY","description"=>"Unloaded at Relay Port"), array("event_name"=>"AE_RELAY","description"=>"Loaded at Relay Port"), array("event_name"=>"VD_RELAY","description"=>"Depart Relay Port"), array("event_name"=>"CU","description"=>"Carrier and Customs Release"), array("event_name"=>"CT","description"=>"Customs release"), array("event_name"=>"CR","description"=>"Carrier release"), array("event_name"=>"VA","description"=>"Vessel Arrival"), array("event_name"=>"UV","description"=>"Unloaded From Vessel"), array("event_name"=>"AG","description"=>"Estimated Delivery"), array("event_name"=>"OA","description"=>"Gate out full from final discharge port"), array("event_name"=>"FT","description"=>"Free Time Expired"), array("event_name"=>"AL","description"=>"Container loaded on Rail"), array("event_name"=>"AR","description"=>"Container unloaded from Rail"), array("event_name"=>"AV","description"=>"Shipment available for pickup or delivery"), array("event_name"=>"D","description"=>"Gate out for delivery to customer"), array("event_name"=>"RD","description"=>"Container returned empty"), array("event_name"=>"C","description"=>"Vessel Estimated Time of Departure"), array("event_name"=>"C_RELAY","description"=>"Estimated Time of Departure from Tranship Port"), array("event_name"=>"AG_DES","description"=>"Estimated Delivery Destination"), array("event_name"=>"IFFADW","description"=>"Shipment in CFS warehouse"), array("event_name"=>"IFFDDW","description"=>"Shipment Out from CFS House")); return $event; } /** * 处理daily 和 week 不同周或者天的数据分组 */ public static function handleDailyWeekedData($notificationsArr){ $uniqe = array(); $ret = array(); foreach($notificationsArr as $info){ $uniqe_group_str = $info['insert_date_format']; if(utils::in_array($uniqe_group_str,$uniqe)){ $tempArr = $ret[$uniqe_group_str]; $tempArr[] = $info; $ret[$uniqe_group_str] = $tempArr; } else { $ret[$uniqe_group_str] = array($info); $uniqe[] = $uniqe_group_str; } } return $ret; } /** * /unit=="Day(s)" ? "days":"hours"; */ public static function convertoVue($unit){ if(empty($unit)) return $unit; if ($unit == "days"){ return "Day(s)"; }else{ return "Hour(s)"; } } /** * vue to int */ public static function convertoint($num){ if($num == "0"){ return intval($num); } return !empty($num) ? intval($num): $num; } //检查user name length public static function checkUserNameLength($input) { if(!empty($input) && strlen($input) > 50){ $data = array( 'code' => 'no_exist', 'login_version' => '', 'msg' => 'The username or password you entered is incorrect' ); common::echo_json_encode(500, $data); exit(); } } public static function checkInputInval($input) { $data = true; // 定义危险 SQL 关键字列表 $dangerousKeywords = [ 'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'DROP', 'ALTER', 'CREATE', 'UNION', 'EXEC', 'EXECUTE', 'TRUNCATE', 'GRANT', 'REVOKE', 'WAITFOR', 'DELAY', 'PG_SLEEP', '--', ';' ]; // 检查是否包含危险关键字（不区分大小写） foreach ($dangerousKeywords as $keyword) { if (stripos($input, $keyword) !== false) { $data = false; } } // 检查是否包含 SQL 注入常见模式 if (preg_match('/\b(OR|AND)\s+1\s*=\s*1\b/i', $input) || preg_match('/\bUNION\s+SELECT\b/i', $input)) { $data = false; } if(!empty($input) && strlen($input) > 50){ $data = false; } return $data; } public static function checkSafeSql($input) { $data = true; // 定义危险 SQL 关键字列表 $dangerousKeywords = [ 'INSERT', 'UPDATE', 'DELETE', 'DROP', 'ALTER', 'CREATE', 'UNION', 'EXEC', 'EXECUTE', 'TRUNCATE', 'GRANT', 'REVOKE', 'WAITFOR', 'DELAY', 'PG_SLEEP' ]; // 检查是否包含危险关键字（不区分大小写） foreach ($dangerousKeywords as $keyword) { $pattern = '/\b' . preg_quote($keyword, '/') . '\b/i'; // \b 表示单词边界 if (preg_match($pattern, $input)) { $data = false; break; // 提高性能，发现一个就返回 } } // 可选：进一步确认是否为 SELECT 查询 if (!preg_match('/^\s*SELECT\b/i', trim($input))) { $data = false; // 不是以 SELECT 开头 } // 转为小写统一处理 $sqlLower = strtolower($input); // 查找 'from' 出现的位置 $fromPos = stripos($sqlLower, 'from'); if ($fromPos === false) { return false; // 没有 from，不可能有 where，直接返回 false } // 从 from 之后的部分中查找 where $afterFrom = substr($sqlLower, $fromPos + 4); // +4 是跳过 'from' if (stripos($afterFrom, 'WHERE') === false && stripos($afterFrom, 'limit') === false){ $data = false; // 简单的判断是否带条件查询 } return $data; } //预加载写法 public static function excuteObjectPrepareSql($sql,$param) { if (empty($sql)) exit(json_encode("Program encountered an error.")); global $db; $stmt = $db->Prepare($sql); $result = $db->Execute($stmt, $param); if ($result && $result->RecordCount() > 0) { $row = $result->fields; return $row; } return null; } //拼接换行 public static function splicedLlineBreaks() { return " "; } /** * chat获取自然序列号 */ public static function getChatAiSequence(){ $sequence = common::excuteOneSql("select count(*) from kln_robot_chat_log where request_time >= CURRENT_DATE"); $sequence = empty($sequence)? "1" : $sequence + 1; if($sequence >99999){ return $sequence; } $sequence = sprintf("%05d", $sequence); return $sequence; } /** * Delivery Booking No获取自然序列号 */ public static function getDeliveryBookingSequence(){ $sequence = common::excuteOneSql("select count(*) from public.kln_destination_delivery where created_time >= CURRENT_DATE"); $sequence = empty($sequence)? "1" : $sequence + 1; if($sequence >9999){ return $sequence; } $sequence = sprintf("%04d", $sequence); return $sequence; } /** * 处理chat 返回的json字符串 */ public static function getChatAimessage($message){ //格式:"```json ```" return str_replace(["```json", "```"], "", $message); } public static function extractSelectFields($sql){ // 正则表达式匹配 SELECT 和 FROM 之间的内容 if (preg_match('/SELECT\s+(.*?)\s+FROM/si', $sql, $matches)) { $selectPart = $matches[1]; // 去除注释和换行 $selectPart = preg_replace('/--.*$/m', '', $selectPart); // 去除单行注释 $selectPart = preg_replace('/\/\*.*?\*\//s', '', $selectPart); // 去除多行注释 $selectPart = str_replace(["\r", "\n"], ' ', $selectPart); // 换行替换成空格 // 分割字段 $fields = []; foreach (explode(',', $selectPart) as $field) { $field = trim($field); if (empty($field)) continue; // 匹配 AS 别名 if (preg_match('/(?:AS\s+)?(\w+)$/i', $field, $aliasMatch)) { // 如果有别名，则使用别名 $fields[] = strtolower($aliasMatch[1]); } else { // 否则直接添加字段名 $fields[] = strtolower(trim($field)); } } return array_unique($fields); } return []; // 如果没有找到 SELECT 字段 } public static function getUserCountry(){ $contact_id = $_SESSION['ONLINE_USER']['contact_id']; if(empty($contact_id)){ return "claude"; } $model = "deepseek"; $belong_schemas = $_SESSION['ONLINE_USER']['belong_schemas']; if(empty($belong_schemas)){ $belong_schemas = "public"; } if(stripos($belong_schemas, "public") !== false){ $data = common::excuteListSql("select LEFT(country, 2) as country from ocean.contacts where contact_id = '$contact_id'"); foreach($data as $country){ if($country['country'] != "CN"){ $model = "claude"; } } } if(stripos($belong_schemas, "sfs") !== false){ $data = common::excuteListSql("select LEFT(country, 2) as country from sfs.contacts where contact_id = '$contact_id'"); foreach($data as $country){ if($country['country'] != "CN"){ $model = "claude"; } } } return $model; } public static function FixedAnswerAndLogData($fixedChat,$question_content){ $reference = ""; //返回格式相同的放到一起处理 if($fixedChat["fixed_faq"] == "Shipments arriving in the next 7 days." || $fixedChat["fixed_faq"] == "What is the current status of my active shipments?"){ $reference = $fixedChat["answer_style"]; $sql = $fixedChat["fixed_sql"]; if($_POST['is_demo'] == 't'){ $sql = utils::getDmoeSqlForAi($fixedChat["fixed_faq"]); } $sqlArr = explode(";", $sql); $sqlCount = $sqlArr[0]; //拼接用户权限 $sqlWhere = ' ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]); $sqlCount = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlCount); error_log("countSql:".$sqlCount); $count = common::excuteOneSql($sqlCount); //替换总数 $total = array("total" =>$count); $reference = utils::replacementsFixed($total,$reference,array("total")); $sqlDetail = $sqlArr[1]; //拼接用户权限 $sqlDetail = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlDetail); error_log("detail:".$sqlDetail); $data = common::excuteListSql($sqlDetail); if($fixedChat["fixed_faq"] == "Shipments arriving in the next 7 days."){ foreach($data as $key =>$d){ $serial_no = common::deCode($d['serial_no'], 'E'); $httpUrl = SERVER_Vue_PAHT."tracking/detail?a=".$serial_no."&_schemas=".$d["order_from"]; $data[$key]['action'] = 'Details Notify'; } } if($fixedChat["fixed_faq"] == "What is the current status of my active shipments?"){ $refer_data =array(); foreach($data as $key =>$d){ $temp = array(); $temp['h_bol'] = $d['h_bol']; $temp['place_of_receipt_exp'] = $d['place_of_receipt_exp']; $temp['place_of_delivery_exp'] = $d['place_of_delivery_exp']; $temp['description'] = $d['description']; $temp['time'] = common::dealDateTime($d['act_date'],$d['act_time'],$d['timezone'],"m/d/Y"); $temp['locations'] = $d['locations']; $temp['cargo_type'] = $d['cargo_type']; $serial_no = common::deCode($d['serial_no'], 'E'); $httpUrl = SERVER_Vue_PAHT."tracking/detail?a=".$serial_no."&_schemas=".$d["order_from"]; $temp['action'] = 'Details Notify'; $refer_data[] = $temp; } $data = $refer_data; } $reference = utils::replacementsFixedMultilineForFixed($data,$reference,$fixedChat['table_format_tr']); $total = array("Download_Complete" =>""); if ($count > 10){ $httpUrl = SERVER_PAHT."main_new_version.php?action=robot_chat&operate=download&faq=". common::deCode($fixedChat["fixed_faq"]); $download_complete = '- Due to page limitations, only the first 10 records are currently displayed. If you need the complete data, please click the Download Complete button.'; $total = array("Download_Complete" =>$download_complete); } $reference = utils::replacementsFixed($total,$reference,array("Download_Complete")); } if($fixedChat["fixed_faq"] == "Show shipments delayed in the last 30 days."){ $reference = $fixedChat["answer_style"]; $sql = $fixedChat["fixed_sql"]; if($_POST['is_demo'] == 't'){ $sql = utils::getDmoeSqlForAi($fixedChat["fixed_faq"]); } $sqlArr = explode(";", $sql); $sqlCount = $sqlArr[0]; //拼接用户权限 $sqlWhere = ' ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]); $sqlCount = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlCount); error_log("countSql:".$sqlCount); $count = common::excuteOneSql($sqlCount); //替换总数 $total = array("total" =>$count); $reference = utils::replacementsFixed($total,$reference,array("total")); $sqlDetail = $sqlArr[1]; //拼接用户权限 $sqlDetail = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlDetail); error_log("detail:".$sqlDetail); $data = common::excuteListSql($sqlDetail); $refer_data =array(); foreach($data as $key =>$d){ $temp = array(); $temp['h_bol'] = $d['h_bol']; $temp['place_of_receipt_exp'] = $d['place_of_receipt_exp']; $temp['place_of_delivery_exp'] = $d['place_of_delivery_exp']; $temp['log_type'] = $d['log_type']; $temp['planned_time'] = common::dealDateTime($d['event_old_date'],$d['event_old_time'],'',"m/d/Y"); $temp['actual_time'] = common::dealDateTime($d['event_date'],$d['event_time'],'',"m/d/Y"); $temp['duration'] = $d['duration'] <= 0 ? '< 1 Days' : $d['duration'].' Days'; $serial_no = common::deCode($d['serial_no'], 'E'); $httpUrl = SERVER_Vue_PAHT."tracking/detail?a=".$serial_no."&_schemas=".$d["order_from"]; $temp['action'] = 'Details Notify'; $refer_data[] = $temp; } $reference = utils::replacementsFixedMultilineForFixed($refer_data,$reference,$fixedChat['table_format_tr']); $total = array("Download_Complete" =>""); if ($count > 10){ $httpUrl = SERVER_PAHT."main_new_version.php?action=robot_chat&operate=download&faq=". common::deCode($fixedChat["fixed_faq"]); $download_complete = '- Due to page limitations, only the first 10 records are currently displayed. If you need the complete data, please click the Download Complete button.'; $total = array("Download_Complete" =>$download_complete); } $reference = utils::replacementsFixed($total,$reference,array("Download_Complete")); } if($fixedChat["fixed_faq"] == "List shipments with milestone updates in the last 7 days."){ $reference = $fixedChat["answer_style"]; $sql = $fixedChat["fixed_sql"]; if($_POST['is_demo'] == 't'){ $sql = utils::getDmoeSqlForAi($fixedChat["fixed_faq"]); } $sqlArr = explode(";", $sql); $sqlCount = $sqlArr[0]; //拼接用户权限 $sqlWhere = ' ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]); $sqlCount = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlCount); error_log("countSql:".$sqlCount); $count = common::excuteOneSql($sqlCount); //替换总数 $total = array("total" =>$count); $reference = utils::replacementsFixed($total,$reference,array("total")); $sqlDetail = $sqlArr[1]; //拼接用户权限 $sqlDetail = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlDetail); error_log("detail:".$sqlDetail); $data = common::excuteListSql($sqlDetail); $refer_data =array(); foreach($data as $key =>$d){ $temp = array(); $temp['h_bol'] = $d['h_bol']; $temp['description'] = $d['description']; $temp['update_date'] = common::dealDateTime($d['update_date'],"",$d['timezone'],"m/d/Y H:i:s"); $temp['locations'] = $d['locations']; $serial_no = common::deCode($d['serial_no'], 'E'); $httpUrl = SERVER_Vue_PAHT."tracking/detail?a=".$serial_no."&_schemas=".$d["order_from"]; $temp['action'] = 'Details Notify'; $refer_data[] = $temp; } $reference = utils::replacementsFixedMultilineForFixed($refer_data,$reference,$fixedChat['table_format_tr']); //Timeline View $sqlview = $sqlArr[2]; //拼接用户权限 $sqlview = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlview); error_log($sqlview); $dateGroups = common::excuteListSql($sqlview); $timeline_view = ""; if(!empty($dateGroups)){ $timeline_view = "## Timeline View: \n"; } foreach($dateGroups as $key => $tv_count){ $timeline_view.="- ".$tv_count['update_date_format'].": ".$tv_count['total_count']." shipments reached milestones \n"; } $total = array("Timeline View" =>$timeline_view); $reference = utils::replacementsFixed($total,$reference,array("Timeline View")); $total = array("Download_Complete" =>""); if ($count > 10){ $httpUrl = SERVER_PAHT."main_new_version.php?action=robot_chat&operate=download&faq=". common::deCode($fixedChat["fixed_faq"]); $download_complete = '- Due to page limitations, only the first 10 records are currently displayed. If you need the complete data, please click the Download Complete button.'; $total = array("Download_Complete" =>$download_complete); } $reference = utils::replacementsFixed($total,$reference,array("Download_Complete")); error_log($reference); } if($fixedChat["fixed_faq"] == "Show me the full history of my container."){ $reference = $fixedChat["answer_style"]; $sql = $fixedChat["fixed_sql"]; if($_POST['is_demo'] == 't'){ $question_content = 'DRYU9375994'; } $sqlArr = explode(";", $sql); $sqlOne = $sqlArr[0]; //拼接用户权限 $sqlWhere = ' ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]); $sqlOne = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlOne); $sqlOne = str_replace('<{ctnr}>', strtolower($question_content), $sqlOne); error_log($sqlOne); $data = common::excuteListSql($sqlOne); if(empty($data)){ return "No valid Container number detected. Please try clicking on other FAQ questions or input your own question. Thank you."; } //如果数据为空，用这个fileds配置的 把模板里值逐个替换为空 $fileds = array("ctnr","size","h_bol","carrier","vessel","voyage","grs_kgs","ams_commodity","seal_no", "place_of_receipt_exp","place_of_delivery_exp","last_status_315_code","last_status_city","last_status_315_date"); //Container Information替换 $reference = utils::replacementsFixed($data[0],$reference,$fileds); //Related Shipments替换，如果有的话 if (!empty($data[0]['cleaned_ctnrs'])){ $related_Shipments = "## Related Shipments \n"; $related_Shipments .= "- **Other Containers on same Bill of Lading**: ".$data[0]['cleaned_ctnrs']; $rsdata = array("Related Shipments" =>$related_Shipments); $reference = utils::replacementsFixed($rsdata,$reference,[]); }else{ $rsdata = array("Related Shipments" =>""); $reference = utils::replacementsFixed($rsdata,$reference,[]); } //Complete Container Status //根据第一个sql 查出来的serial_no，和 container_no. $complete_container_status = ""; if (!empty($data)) { $sqltwo = $sqlArr[1]; $sqltwo = str_replace('<{serial_no}>', $data[0]['serial_no'], $sqltwo); $sqltwo = str_replace('<{container_no}>', $data[0]['ctnr'], $sqltwo); $csdata = common::excuteListSql($sqltwo); foreach($csdata as $csd){ $complete_container_status .= "- **".$csd['eventdate']." ".$csd['eventtime']."** ".$csd['description']." | ".$csd['uncity']." \n"; } } $rsdata = array("complete_container_status" =>$complete_container_status); $reference = utils::replacementsFixed($rsdata,$reference,[]); } if($fixedChat["fixed_faq"] == "Show me the full history of my shipment."){ $reference = $fixedChat["answer_style"]; $sql = $fixedChat["fixed_sql"]; if($_POST['is_demo'] == 't'){ $question_content = 'XSTSNA003195'; } $sqlArr = explode(";", $sql); $sqlOne = $sqlArr[0]; //拼接用户权限 $sqlWhere = ' ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]); $sqlOne = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlOne); $sqlOne = str_replace('', strtolower($question_content), $sqlOne); error_log($sqlOne); $data = common::excuteListSql($sqlOne); if(empty($data)){ return "No valid Shipment number detected. Please try clicking on other FAQ questions or input your own question. Thank you."; } //如果数据为空，用这个fileds配置的 把模板里值逐个替换为空 $fileds = array("h_bol","carrier_booking","po_no","service","incoterms","shipper_city","consignee_city","etd","eta", "shipper","consignee","notify_party","origin_agent","destination_agent","carrier"); //Container Information替换 $reference = utils::replacementsFixed($data[0],$reference,$fileds); $ref_data = array(); $serial_no = common::deCode($data[0]['serial_no'], 'E'); $httpUrl = SERVER_PAHT."tracking/detail?a=".$serial_no."&_schemas=".$data[0]['order_from']; $ref_data['Track Shipment'] = 'Track Shipment'; $reference = utils::replacementsFixed($ref_data,$reference,$fileds); //Complete Container Status //根据第一个sql 查出来的serial_no，和 container_no. $complete_container_status = ""; if (!empty($data)) { $sqltwo = $sqlArr[1]; $sqltwo = str_replace('<{serial_no}>', $data[0]['serial_no'], $sqltwo); $csdata = common::excuteListSql($sqltwo); foreach($csdata as $csd){ $complete_container_status .= "- **".$csd['eventdate']." ".$csd['eventtime']."** ".$csd['description']." | ".$csd['uncity']." \n"; } } $rsdata = array("complete_container_status" =>$complete_container_status); $reference = utils::replacementsFixed($rsdata,$reference,[]); } if($fixedChat["fixed_faq"] == "List shipments with container status updates in the last 7 days."){ $reference = $fixedChat["answer_style"]; $sql = $fixedChat["fixed_sql"]; if($_POST['is_demo'] == 't'){ $sql = utils::getDmoeSqlForAi($fixedChat["fixed_faq"]); } $sqlArr = explode(";", $sql); $sqlCount = $sqlArr[0]; //拼接用户权限 $sqlWhere = ' ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]); $sqlCount = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlCount); error_log("countSql:".$sqlCount); $count = common::excuteOneSql($sqlCount); //替换总数 $total = array("total" =>$count); $reference = utils::replacementsFixed($total,$reference,array("total")); $sqlDetail = $sqlArr[1]; //拼接用户权限 $sqlDetail = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlDetail); error_log("detail:".$sqlDetail); $data = common::excuteListSql($sqlDetail); $refer_data =array(); foreach($data as $key =>$d){ $temp = array(); $temp['container_no'] = $d['container_no']; $temp['description'] = $d['description']; $temp['time'] = common::dealDateTime($d['eventdate'],$d['eventtime'],$d['timezone'],"m/d/Y"); $temp['uncity'] = $d['uncity']; $serial_no = common::deCode($d['serial_no'], 'E'); $httpUrl = SERVER_Vue_PAHT."tracking/detail?a=".$serial_no."&_schemas=".$d["order_from"]; $temp['action'] = 'Details Notify'; $refer_data[] = $temp; } $reference = utils::replacementsFixedMultilineForFixed($refer_data,$reference,$fixedChat['table_format_tr']); //Timeline View $sqlview = $sqlArr[2]; //拼接用户权限 $sqlview = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlview); error_log("viewSql:".$sqlview); $dateGroups = common::excuteListSql($sqlview); $timeline_view = ""; if(!empty($dateGroups)){ $timeline_view = "## Timeline View: \n"; } foreach($dateGroups as $key => $tv_count){ $timeline_view.="- ".$tv_count['_insert_date'].": ".$tv_count['total_count']." containers have been updated \n"; } $total = array("Timeline View" =>$timeline_view); $reference = utils::replacementsFixed($total,$reference,array("Timeline View")); $total = array("Download_Complete" =>""); if ($count > 10){ $httpUrl = SERVER_PAHT."main_new_version.php?action=robot_chat&operate=download&faq=". common::deCode($fixedChat["fixed_faq"]); $download_complete = '- Due to page limitations, only the first 10 records are currently displayed. If you need the complete data, please click the Download Complete button.'; $total = array("Download_Complete" =>$download_complete); } $reference = utils::replacementsFixed($total,$reference,array("Download_Complete")); } if($fixedChat["fixed_faq"] == "Today's shipments summary."){ $reference = $fixedChat["answer_style"]; $sql = $fixedChat["fixed_sql"]; if($_POST['is_demo'] == 't'){ $sql = utils::getDmoeSqlForAi($fixedChat["fixed_faq"]); } $sqlArr = explode(";", $sql); $sqlCount = $sqlArr[0]; //拼接用户权限 $sqlWhere = ' ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]); $sqlCount = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlCount); error_log("countSql:".$sqlCount); $count = common::excuteOneSql($sqlCount); //替换总数 $total = array("total" =>$count); $reference = utils::replacementsFixed($total,$reference,array("total")); $sqlDetail = $sqlArr[1]; //拼接用户权限 $sqlDetail = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlDetail); error_log("detail:".$sqlDetail); $data = common::excuteListSql($sqlDetail); $refer_data =array(); foreach($data as $key =>$d){ $temp = array(); $temp['h_bol'] = $d['h_bol']; $temp['transport_mode'] = $d['transport_mode']; $temp['place_of_receipt_exp'] = $d['place_of_receipt_exp']; $temp['place_of_delivery_exp'] = $d['place_of_delivery_exp']; $temp['action_type'] = $d['action_type']; $temp['time'] = common::dealDateTime($d['act_date'],$d['act_time'],$d['timezone'],"m/d/Y"); $temp['locations'] = $d['locations']; $serial_no = common::deCode($d['serial_no'], 'E'); $httpUrl = SERVER_Vue_PAHT."tracking/detail?a=".$serial_no."&_schemas=".$d["order_from"]; $temp['action'] = 'Details Notify'; $refer_data[] = $temp; } $reference = utils::replacementsFixedMultilineForFixed($refer_data,$reference,$fixedChat['table_format_tr']); //替换开头日期 $d1_day = empty($data) ? "" : $data[0]['_update_date']; $dateData = array("date" => $d1_day); $reference = utils::replacementsFixed($dateData,$reference,[]); //dep arr del total $sqlview = $sqlArr[2]; //拼接用户权限 $sqlview = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlview); error_log("depArrDelSql:".$sqlview); $dateGroups = common::excuteListSql($sqlview); $total = array("dep" =>$dateGroups[0]['dep'],"arr" => $dateGroups[0]['arr'],"del" => $dateGroups[0]['del']); $reference = utils::replacementsFixed($total,$reference,[]); $total = array("Download_Complete" =>""); if ($count > 10){ $httpUrl = SERVER_PAHT."main_new_version.php?action=robot_chat&operate=download&faq=". common::deCode($fixedChat["fixed_faq"]); $download_complete = '- Due to page limitations, only the first 10 records are currently displayed. If you need the complete data, please click the Download Complete button.'; $total = array("Download_Complete" =>$download_complete); } $reference = utils::replacementsFixed($total,$reference,array("Download_Complete")); } if($fixedChat["fixed_faq"] == "Show me the current location of my shipment."){ $reference = $fixedChat["answer_style"]; $sql = $fixedChat["fixed_sql"]; if($_POST['is_demo'] == 't'){ $question_content = 'DRYU9375994'; } $sqlArr = explode(";", $sql); $sqlOne = $sqlArr[0]; //拼接用户权限 $sqlWhere = ' ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]); $sqlOne = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlOne); $sqlOne = str_replace('<{ctnr}>', strtolower($question_content), $sqlOne); error_log($sqlOne); $data = common::excuteListSql($sqlOne); if(empty($data)){ return "No valid Container/BOL number detected. Please try clicking on other FAQ questions or input your own question. Thank you."; } //如果数据为空，用这个fileds配置的 把模板里值逐个替换为空 $fileds = array("tracking_no","h_bol","question_content","transport_mode","place_of_receipt_exp","place_of_delivery_exp","carrier","vessel","voyage","Shipment Detail Page Link"); $ref_data = array(); if(!empty($data)){ $ref_data = $data[0]; $ref_data['question_content'] = $question_content; $serial_no = common::deCode($data[0]['serial_no'], 'E'); $httpUrl = SERVER_PAHT."tracking/detail?a=".$serial_no."&_schemas=".$data[0]['order_from']; $ref_data['Shipment Detail Page Link'] = 'Shipment Detail Page Link'; } $reference = utils::replacementsFixed($ref_data,$reference,$fileds); } if($fixedChat["fixed_faq"] == "Sort my active shipments by earliest arrival date."){ $reference = $fixedChat["answer_style"]; $sql = $fixedChat["fixed_sql"]; if($_POST['is_demo'] == 't'){ $sql = utils::getDmoeSqlForAi($fixedChat["fixed_faq"]); } $sqlArr = explode(";", $sql); $sqlCount = $sqlArr[0]; //拼接用户权限 $sqlWhere = ' ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]); $sqlCount = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlCount); error_log("countSql:".$sqlCount); $count = common::excuteOneSql($sqlCount); $sqlDetail = $sqlArr[1]; //拼接用户权限 $sqlWhere = ' ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]); $sqlDetail = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sqlDetail); error_log("detail:".$sqlDetail); $data = common::excuteListSql($sqlDetail); $refer_data =array(); foreach($data as $key =>$d){ $temp = array(); $temp['eta'] = $d['eta']; $temp['h_bol'] = $d['h_bol']; $temp['transport_mode'] = $d['transport_mode']; $temp['place_of_receipt_exp'] = $d['place_of_receipt_exp']; $temp['place_of_delivery_exp'] = $d['place_of_delivery_exp']; $temp['day_to_arr'] = $d['day_to_arr']; $serial_no = common::deCode($d['serial_no'], 'E'); $httpUrl = SERVER_Vue_PAHT."tracking/detail?a=".$serial_no."&_schemas=".$d["order_from"]; $temp['action'] = 'Details Notify'; $refer_data[] = $temp; } $data = $refer_data; $reference = utils::replacementsFixedMultilineForFixed($data,$fixedChat['answer_style'],$fixedChat['table_format_tr']); $total = array("Download_Complete" =>""); if ($count > 10){ $httpUrl = SERVER_PAHT."main_new_version.php?action=robot_chat&operate=download&faq=". common::deCode($fixedChat["fixed_faq"]); $download_complete = '- Due to page limitations, only the first 10 records are currently displayed. If you need the complete data, please click the Download Complete button.'; $total = array("Download_Complete" =>$download_complete); } $reference = utils::replacementsFixed($total,$reference,array("Download_Complete")); } if($fixedChat["fixed_faq"] == "Set up automatic notifications for shipment updates." || $fixedChat["fixed_faq"] == "How can I view the detailed shipping route of my package?" ){ $reference = $fixedChat["answer_style"]; } error_log($reference); return $reference; } public static function dealDateTime($date,$time,$zone,$format){ if(empty($date)){ return ""; } //处理掉多余空格 if(!empty($time)){ $time_str = " ".$time; } else { $time_str = ""; } if(!empty($date) && !empty($zone)){ $utc = common::getUTCOffsetFormatted($zone); return date($format, strtotime($date)).$time_str." UTC".$utc; } return date($format, strtotime($date)).$time_str; } public static function getUTCOffsetFormatted($timezone) { $timezone = new DateTimeZone($timezone); $now = new DateTime('now', $timezone); $offset = $timezone->getOffset($now); $hours = floor($offset / 3600 * 10) / 10; if( $hours > 0 ){ $hours = "+".$hours; } return $hours; } /** * 获取固定问题的查询sql */ public static function getFaqSql($faq){ $sqlObj = common::excuteObjectSql("select fixed_sql from public.kln_robot_chat_fixed where fixed_faq = '$faq'"); $fixed_sql = $sqlObj['fixed_sql']; $sqlDetail = ''; $sqlArr = explode(";", $fixed_sql); if(count($sqlArr) > 1){ $sqlDetail = $sqlArr[1]; $search = 'limit 10'; $sqlDetail = str_ireplace($search, '', $sqlDetail); } return $sqlDetail; } /** * 获取固定问题列 */ public static function getFaqColumnSql($faq){ //返回格式相同的放到一起处理 if($faq == "Shipments arriving in the next 7 days."){ // | HBL/HAWB No. | Origin | Destination | Latest Milestone | ETA | Cargo Type | Actions | // |--------------|------------------|------------------|------------------|-----------|---------------|------------------| // | {{h_bol}} | {{place_of_receipt_exp}} | {{place_of_delivery_exp}} | {{description}} | {{eta}} | {{cargo_type}} | {{action}} | $columns = array(); $columns[] = array("display_name" => "HBL/HAWB No.","database_column_name"=>"h_bol"); $columns[] = array("display_name" => "Origin","database_column_name"=>"place_of_receipt_exp"); $columns[] = array("display_name" => "Destination","database_column_name"=>"place_of_delivery_exp"); $columns[] = array("display_name" => "Latest Milestone","database_column_name"=>"description"); $columns[] = array("display_name" => "ETA","database_column_name"=>"eta"); $columns[] = array("display_name" => "Cargo Type","database_column_name"=>"cargo_type"); } if($faq == "Show shipments delayed in the last 30 days."){ // | HBL/HAWB No. | Origin | Destination | Latest Milestone | ETA | Cargo Type | Actions | // |--------------|------------------|------------------|------------------|-----------|---------------|------------------| // | {{h_bol}} | {{place_of_receipt_exp}} | {{place_of_delivery_exp}} | {{description}} | {{eta}} | {{cargo_type}} | {{action}} | $columns = array(); $columns[] = array("display_name" => "HBL/HAWB No.","database_column_name"=>"h_bol"); $columns[] = array("display_name" => "Origin","database_column_name"=>"place_of_receipt_exp"); $columns[] = array("display_name" => "Destination","database_column_name"=>"place_of_delivery_exp"); $columns[] = array("display_name" => "Delayed Type","database_column_name"=>"log_type"); $columns[] = array("display_name" => "Planned Date","database_column_name"=>"planned_time"); $columns[] = array("display_name" => "Actual/Revised Date","database_column_name"=>"actual_time"); $columns[] = array("display_name" => "Delay Duration","database_column_name"=>"duration"); } if($faq == "List shipments with milestone updates in the last 7 days."){ // | HBL/HAWB No. | Latest Milestone | Time | Location | Actions | // |--------------|----------------------------|------------------------------|---------------------------|------------------| // | {{h_bol}} | {{description}} | {{update_date}} | {{locations}} | {{action}} | $columns = array(); $columns[] = array("display_name" => "HBL/HAWB No.","database_column_name"=>"h_bol"); $columns[] = array("display_name" => "Latest Milestone","database_column_name"=>"description"); $columns[] = array("display_name" => "Time","database_column_name"=>"update_date"); $columns[] = array("display_name" => "Location","database_column_name"=>"locations"); } if($faq == "What is the current status of my active shipments?"){ // | HBL/HAWB No. | Origin | Destination | Latest Milestone | Time | Location | Cargo Type | Actions | // |--------------|------------------|------------------|----------------------------|------------------------------|---------------------------|---------------|------------------| // | {{h_bol}} | {{place_of_receipt_exp}} | {{place_of_delivery_exp}} | {{description}} | {{time}} | {{locations}} | {{cargo_type}} | {{action}} | $columns = array(); $columns[] = array("display_name" => "HBL/HAWB No.","database_column_name"=>"h_bol"); $columns[] = array("display_name" => "Origin","database_column_name"=>"place_of_receipt_exp"); $columns[] = array("display_name" => "Destination","database_column_name"=>"place_of_delivery_exp"); $columns[] = array("display_name" => "Latest Milestone","database_column_name"=>"description"); $columns[] = array("display_name" => "Time","database_column_name"=>"time"); $columns[] = array("display_name" => "Location","database_column_name"=>"locations"); $columns[] = array("display_name" => "Cargo Type","database_column_name"=>"cargo_type"); } if($faq == "List shipments with container status updates in the last 7 days."){ // | Container No. | Container Status | Time | Location | Actions | // |---------------|----------------------------|------------------------------|---------------------------|------------------| // | {{container_no}} | {{description}} | {{time}} | {{uncity}} | {{action}} | $columns = array(); $columns[] = array("display_name" => "Container No.","database_column_name"=>"container_no"); $columns[] = array("display_name" => "Container Status","database_column_name"=>"description"); $columns[] = array("display_name" => "Time","database_column_name"=>"time"); $columns[] = array("display_name" => "Location","database_column_name"=>"uncity"); } if($faq == "Today's shipments summary."){ // | HBL/HAWB No. | Mode | Origin | Destination | Action Type | Action Time | Action Location | Actions | // |--------------|------|------------------|------------------|-------------|-------------------------|-----------------|------------------| // | {{h_bol}} | {{transport_mode}} | {{place_of_receipt_exp}} | {{place_of_delivery_exp}} | {{action_type}} | {{time}} | {{locations}} | {{action}} | $columns = array(); $columns[] = array("display_name" => "HBL/HAWB No.","database_column_name"=>"h_bol"); $columns[] = array("display_name" => "Mode","database_column_name"=>"transport_mode"); $columns[] = array("display_name" => "Origin","database_column_name"=>"place_of_receipt_exp"); $columns[] = array("display_name" => "Destination","database_column_name"=>"place_of_delivery_exp"); $columns[] = array("display_name" => "Action Type","database_column_name"=>"action_type"); $columns[] = array("display_name" => "Action Time","database_column_name"=>"time"); $columns[] = array("display_name" => "Action Location","database_column_name"=>"locations"); } if($faq == "Sort my active shipments by earliest arrival date."){ // | ETA | HBOL/HAWB No. | Mode | Origin | Destination | Days to Arrival | Actions | // |--------|---------------|-------|------------------|------------------|-----------------|------------------| // | {{eta}} | {{h_bol}} | {{transport_mode}} | {{place_of_receipt_exp}} | {{place_of_delivery_exp}} | {{day_to_arr}} | {{action}} | $columns = array(); $columns[] = array("display_name" => "ETA","database_column_name"=>"eta"); $columns[] = array("display_name" => "HBL/HAWB No.","database_column_name"=>"h_bol"); $columns[] = array("display_name" => "Mode","database_column_name"=>"transport_mode"); $columns[] = array("display_name" => "Origin","database_column_name"=>"place_of_receipt_exp"); $columns[] = array("display_name" => "Destination","database_column_name"=>"place_of_delivery_exp"); $columns[] = array("display_name" => "Days to Arrival","database_column_name"=>"day_to_arr"); } return $columns; } /** * 处理数据问题 */ public static function dealDataWithFaq($faq,$data){ $refer_data = array(); //返回格式相同的放到一起处理 if($faq == "Shipments arriving in the next 7 days."){ $refer_data = $data; } if($faq == "Show shipments delayed in the last 30 days."){ foreach($data as $key =>$d){ $temp = array(); $temp['h_bol'] = $d['h_bol']; $temp['place_of_receipt_exp'] = $d['place_of_receipt_exp']; $temp['place_of_delivery_exp'] = $d['place_of_delivery_exp']; $temp['log_type'] = $d['log_type']; $temp['planned_time'] = common::dealDateTime($d['event_old_date'],$d['event_old_time'],'',"m/d/Y"); $temp['actual_time'] = common::dealDateTime($d['event_date'],$d['event_time'],'',"m/d/Y"); $temp['duration'] = $d['duration'] <= 0 ? '< 1 Days' : $d['duration'].' Days'; $refer_data[] = $temp; } } if($faq == "List shipments with milestone updates in the last 7 days."){ foreach($data as $key =>$d){ $temp = array(); $temp['h_bol'] = $d['h_bol']; $temp['description'] = $d['description']; $temp['update_date'] = common::dealDateTime($d['update_date'],"",$d['timezone'],"m/d/Y H:i:s"); $temp['locations'] = $d['locations']; $refer_data[] = $temp; } } if($faq == "What is the current status of my active shipments?"){ foreach($data as $key => $d){ $temp = array(); $temp['h_bol'] = $d['h_bol']; $temp['place_of_receipt_exp'] = $d['place_of_receipt_exp']; $temp['place_of_delivery_exp'] = $d['place_of_delivery_exp']; $temp['description'] = $d['description']; $temp['time'] = common::dealDateTime($d['act_date'],$d['act_time'],$d['timezone'],"m/d/Y"); $temp['locations'] = $d['locations']; $temp['cargo_type'] = $d['cargo_type']; $refer_data[] = $temp; } } if($faq == "List shipments with container status updates in the last 7 days."){ foreach($data as $key =>$d){ $temp = array(); $temp['container_no'] = $d['container_no']; $temp['description'] = $d['description']; $temp['time'] = common::dealDateTime($d['eventdate'],$d['eventtime'],$d['timezone'],"m/d/Y"); $temp['uncity'] = $d['uncity']; $refer_data[] = $temp; } } if($faq == "Today's shipments summary."){ foreach($data as $key =>$d){ $temp = array(); $temp['h_bol'] = $d['h_bol']; $temp['transport_mode'] = $d['transport_mode']; $temp['place_of_receipt_exp'] = $d['place_of_receipt_exp']; $temp['place_of_delivery_exp'] = $d['place_of_delivery_exp']; $temp['action_type'] = $d['action_type']; $temp['time'] = common::dealDateTime($d['act_date'],$d['act_time'],$d['timezone'],"m/d/Y"); $temp['locations'] = $d['locations']; $refer_data[] = $temp; } } if($faq == "Sort my active shipments by earliest arrival date."){ foreach($data as $key =>$d){ $temp = array(); $temp['eta'] = $d['eta']; $temp['h_bol'] = $d['h_bol']; $temp['transport_mode'] = $d['transport_mode']; $temp['place_of_receipt_exp'] = $d['place_of_receipt_exp']; $temp['place_of_delivery_exp'] = $d['place_of_delivery_exp']; $temp['day_to_arr'] = $d['day_to_arr']; $refer_data[] = $temp; } } return $refer_data; } public static function destination_delivery_window_radio($radio){ if($radio == "No_Restrictions"){ return 1; } if($radio == "Restrictions_ETD_ATD"){ return 2; } if($radio == "Restrictions_ETA_ATA"){ return 3; } } public static function destination_delivery_recommended($radio){ if($radio == "No_Recommended"){ return 1; } if($radio == "Delivery_ETA_ATA"){ return 2; } } public static function checkOverlap($post_arr,$name,$startIndex = 0) { $errors = ""; $num = count($post_arr); //$_POST['rule_type']; for ($i = 0; $i < $num; $i++) { for ($j = $i + 1; $j < $num; $j++) { $set1 = $post_arr[$i] ?? []; $set2 = $post_arr[$j] ?? []; $intersection = common::array_intersect_own($set1, $set2,$_POST['rule_type'][$i+$startIndex]); $intersectionC = ""; //这个特殊。如果ports 存在一样的，那检查carrier 是否存在一样的。 if ($name == "Sea Rule") { $setC1 = explode(",",$_POST['carrier'][$i+$startIndex]) ?? []; $setC2 = explode(",",$_POST['carrier'][$j+$startIndex]) ?? []; $intersectionC = array_intersect($setC1, $setC2); if (!empty($intersection) && !empty($intersectionC)) { $errors = "$name " . ($i + 1) . " and $name " . ($j + 1) . " have overlapping"; if (!empty($intersection)) { $errors .= " ports: " . implode(', ', $intersection); } if (!empty($intersectionC)) { $errors .= " carrier: " . implode(', ', $intersectionC); } } } else { if (!empty($intersection)) { $errors = "$name " . ($i + 1) . " and $name " . ($j + 1) . " have overlapping ports: " . implode(', ', $intersection); } } } } return $errors; } public static function array_intersect_own($set1, $set2){ $intersection = array_intersect($set1, $set2); //这个是无效的$set1是数组，不是字符串。 // if ($rule_type <> '*Default Rule' && ($set1 == 'ALL' || $set2 == 'ALL')){ // $intersection = "ALL"; // } return $intersection; } public static function sendDestinationDeliveryReminder($data,$shipmentsData,$status){ $html_tr = ""; $dest_op_arr = array(); foreach($shipmentsData['data'] as $shipment){ $html_tr .=" ".$shipment['h_bol']." ".$shipment['ctnr']." ".$shipment['package_type']." ".$shipment['kgw']." "; if(!empty($shipment['dest_op'])){ $dest_op_arr[] = $shipment['dest_op']; } } $kln_pic_email = $data['kln_pic']; //这票的创建者email $customer_email = common::excuteOneSql("select email from public.ra_online_user where user_login = '".$data['create_by']."'"); if ($status == "Reject" || $status == "Cancel") { $log = common::excuteObjectSql("select *, TO_CHAR(created_time, 'Mon-DD-YYYY') as _created_time from public.kln_destination_delivery_operation_log where serial_no = '".$data['serial_no']."' order by id desc limit 1"); } $links = ""; if ($status == "Pending Approval" || $status == "Modify") { $links = 'https://online-beta.kln.com/'; } if ($status == "Approve") { $links = 'https://online-beta.kln.com/'; } //邮件模板填充字段准备 $address = json_decode($data["delivery_address_detail"],true); $tplData = array("customer_email"=>$customer_email, "customer_name"=>$data['create_by'], "delivery_mode"=>$data['delivery_mode'], "delivery_date"=>$data['_delivery_date'], "location_name"=>empty($address['contact_person']) ? "" : $address['contact_person'].":".$address['contact_number'], "address_1"=>$address['address_1'], "contact"=>"", "kln_pic_email"=>$kln_pic_email, "booking_no" =>$data['booking_no'], "status" =>$status, "action_time" =>$log['_created_time'], "created_time" =>$data['_created_time'], "update_time" =>$data['_update_time'], "action_by" =>$log['create_by'], "action_reason" =>"", "action_comments" =>$log['notes'], "link" =>$links, "html_tr" =>$html_tr); $contents = common::excuteObjectSql("select subject, ra_content as content from ra_online_email_tpl where ra_type = 'Delivery_".$status."'"); //检查type长度 $report_type = "delivery_email"; $report_type = strlen($report_type) > 20 ? substr($report_type, 0, 20) : $report_type; $subject = common::check_input($contents['subject']); $email_from = "US.KApex.Online@kerryapex.com"; $to_email = $customer_email; $cc_email = $kln_pic_email; if(!empty($dest_op_arr)){ $dest_op_arr = array_values(array_unique($dest_op_arr)); $more_param = common::getInNotInSqlForSearch(strtolower(utils::implode(';',$dest_op_arr))); $destOPEmail = common::excuteObjectSql("select string_agg(email,';') as email from employee where COALESCE(email,'') <> '' and lower(employee_id) in (".$more_param.")"); if(!empty($destOPEmail['email'])){ $cc_email = $cc_email.";".$destOPEmail['email']; } } error_log($contents['content']); // 动态构建替换数组（格式：[key] => value） $replacements = []; foreach ($tplData as $key => $value) { $replacements["{{$key}}"] = $value; $replacements["{{{$key}}}"] = $value; } $content = strtr($contents['content'], $replacements); $content = common::check_input($content); //error_log($content); $sql = "INSERT INTO email_record(type, title, from_email, to_email, content, insert_date, cc_email, attachment_path) VALUES ('" . $report_type . "', '" . $subject . "', '" . $email_from . "', '".$to_email . "', '" . $content . "', now(), '" . $cc_email . "', '');"; if(empty($contents) || empty($to_email)){ return ""; } return $sql; } public static function deliveryStatusConvert($status){ if ($status == 'Approve'){ return 'Approved'; } if ($status == 'Reject'){ return 'Rejected'; } if ($status == 'Cancel'){ return 'Cancelled'; } return $status; } public static function customizeParsing ($rawString){ // 您提供的字符串 // $rawString = '{ "can_query": false, "query_type": "", "reason": "用户询问是否有其他联系邮箱，因为support@kln.com邮箱返回无法送达。这个问题无法通过数据表查询得到答案，因为表中不包含KLN公司的联系邮箱信息。", "sql": "", "reference": "", "response": "I understand that you\'re having trouble with the email address support@kln.com returning as undeliverable. For customer support inquiries, please use our official customer service email: customer.service@kln.com or alternatively, you can contact our global service desk at global.service@kln.com. // You can also reach us through our customer portal at https://www.kln.com/contact-us or call our customer service hotline at +852 2796 6666. // We apologize for any inconvenience and are happy to assist you with your logistics needs." }'; // 要提取的键 $keysToExtract = ['can_query', 'sql', 'reference', 'response']; // 存储结果的数组 $extractedValues = []; foreach ($keysToExtract as $key) { $fullKey = '"' . $key . '"'; // 构建完整的键，如 "can_query" $keyPos = strpos($rawString, $fullKey); if ($keyPos === false) { $extractedValues[$key] = null; // 键未找到 continue; } // 找到键后，定位冒号 : $colonPos = strpos($rawString, ':', $keyPos + strlen($fullKey)); if ($colonPos === false) { $extractedValues[$key] = null; // 冒号未找到 continue; } // 跳过冒号和可能的空白，定位值的开始 $valueStart = $colonPos + 1; while ($valueStart < strlen($rawString) && ctype_space($rawString[$valueStart])) { $valueStart++; } if ($valueStart >= strlen($rawString)) { $extractedValues[$key] = null; // 值开始位置越界 continue; } $value = ''; $char = $rawString[$valueStart]; // 情况1: 值以双引号开头 (字符串) if ($char === '"') { // 从 valueStart+1 开始寻找结束的双引号，需要处理转义 $pos = $valueStart + 1; $strValue = ''; $escaped = false; while ($pos < strlen($rawString)) { $currentChar = $rawString[$pos]; if ($escaped) { // 处理转义字符，简单处理常见情况 switch ($currentChar) { case 'n': $strValue .= "\n"; break; case 't': $strValue .= "\t"; break; case 'r': $strValue .= "\r"; break; case '"': $strValue .= '"'; break; case '\\': $strValue .= '\\'; break; default: $strValue .= $currentChar; break; // 其他转义，原样保留？ } $escaped = false; } else { if ($currentChar === '\\') { $escaped = true; } elseif ($currentChar === '"') { // 找到了未转义的结束引号 break; } else { $strValue .= $currentChar; } } $pos++; } // 如果找到了结束引号，$strValue 就是解码后的字符串 // 如果没找到（pos >= strlen），说明字符串没闭合，这里按找到的处理 $value = trim($strValue); } // 情况2: 值不是以双引号开头 (false, true, null, "", 数字等) else { // 寻找值的结束位置：遇到逗号,、右花括号}、右括号] 或空白序列（如果后面是分隔符） $pos = $valueStart; $literalValue = ''; while ($pos < strlen($rawString)) { $currentChar = $rawString[$pos]; // 检查是否到了值的边界 if ($currentChar === ',' || $currentChar === '}' || $currentChar === ']') { break; // 遇到分隔符，值结束 } // 如果遇到空白，检查后面的字符是否是分隔符或空白，如果是，则值可能结束 if (ctype_space($currentChar)) { // 查看下一个非空白字符 $nextPos = $pos + 1; while ($nextPos < strlen($rawString) && ctype_space($rawString[$nextPos])) { $nextPos++; } if ($nextPos >= strlen($rawString) || $rawString[$nextPos] === ',' || $rawString[$nextPos] === '}' || $rawString[$nextPos] === ']') { break; // 后面是分隔符或结束，当前空白是值的结束 } // 否则，空白是值的一部分？(通常字面量不含内部空白) } // 对于字面量，我们通常认为它不包含内部空白，所以遇到空白且后面不是分隔符时，可能也该停止？ // 但为了简单，我们主要依赖 , } ] 分隔符。 // 追加当前字符 $literalValue .= $currentChar; $pos++; } // $literalValue 现在包含从 valueStart 到分隔符前的所有字符 // 但可能包含尾部空白，trim 一下 $value = trim($literalValue); // 特别处理空字符串字面量 "" // 如果原始字符串在 valueStart 位置是 "，但我们上面的 else 分支没进，所以不会到这里 // 如果原始是 "", 它会被上面的 else 分支捕获为字符串 '""'，但我们希望它的值是空字符串 '' // 所以需要检查 $value 是否等于 '""' if ($value === '""') { $value = ''; } // 注意：在您的例子中，"sql": "" 和 "reference": "" 在 JSON 中是空字符串，但在 else 分支会被识别为 '""' // 我们在这里统一处理。 } $extractedValues[$key] = $value; } return $extractedValues; } public static function secondaryReplacement($brokenJson){ // 用 \n 替换实际换行符 '/"\w+"\s*:\s*"\K[^"]*(?=")/s' 这种如果你字段名不是简单的 \w+（比如有 -、中文等），可以用： $fixedJson = preg_replace_callback( '/"\s*:\s*"\K[^"]*(?=")/s', function ($matches) { $text =$matches[0]; return str_replace(["\r\n", "\n", "\r"], "\\n", $text); //return str_replace("\n", "\\n", $matches[0]) //return str_replace(["\r\n", "\n", "\r"], "\\n", $text); }, $brokenJson ); return $fixedJson; } /** * 客户和 内部员工在不同status下。运行的操作限制 */ public static function checkedActionLegal($serial_no,$operate){ $is_employee = _isApexLogin(); $status = common::excuteOneSql("select status from public.kln_destination_delivery where serial_no = '$serial_no'"); $legal = false; if ($status == 'Pending Approval'){ if ($is_employee){ //Pending Approval 状态下 内部 能审核，拒绝，和看详细 if ($operate == "review" && ($_POST['status'] == "Approve" || $_POST['status'] == "Reject")){ $legal = true; } if ($operate == "view_detail"){ $legal = true; } } else { //Pending Approval 状态下 客户的 能取消，看详细，和编辑 if ($operate == "review" && $_POST['status'] == "Cancel"){ $legal = true; } if ($operate == "view_detail"){ $legal = true; } if ($operate == "add"){ $legal = true; } } } else if ($status == 'Approve'){ if ($is_employee){ //Approve 下 内部的能看详细 if ($operate == "view_detail"){ $legal = true; } } else { //Approve 下 客户的 能发邮件留言，能看详细 if ($operate == "email_message_board"){ $legal = true; } if ($operate == "view_detail"){ $legal = true; } } } else if ($status == 'Reject'){ if ($is_employee){ //Reject 下 员工 看详细 if ($operate == "view_detail"){ $legal = true; } } else { //Reject 下 客户 能编辑 看详细 if ($operate == "add"){ $legal = true; } if ($operate == "view_detail"){ $legal = true; } } } else if ($status == 'Cancel'){ //不分账户类型，只能看详细 if ($operate == "view_detail"){ $legal = true; } } //固定 //$legal = true; if(!$legal){ $data = array("msg" =>"Illegal access"); common::echo_json_encode(200,$data); exit(); } } public static function saveDAddressTempTable($delivery_serial_no,$consignee_id){ //deliver address is new $addressSql = ""; $_sync_key_add = ""; $address_1 = $_POST['address_1']; foreach($address_1 as $key => $_address_1){ $_address_1 = common::check_input($_address_1); $_address_2 = common::check_input($_POST['address_2'][$key]); $_address_3 = common::check_input($_POST['address_3'][$key]); $_address_4 = common::check_input($_POST['address_4'][$key]); $_country = common::check_input($_POST['country'][$key]); $_city = common::check_input($_POST['city'][$key]); $_postal_code = common::check_input($_POST['postal_code'][$key]); $_contact_person = common::check_input($_POST['contact_person'][$key]); $_contact_number = common::check_input($_POST['contact_number'][$key]); $_contact_id = common::check_input($_POST['contact_id'][$key]); $_sync_key = common::check_input($_POST['sync_key'][$key]); $_from_station = common::check_input($_POST['from_station'][$key]); $_contact_type = common::check_input($_POST['contact_type'][$key]); $_create_user = common::check_input($_POST['create_user'][$key]); //为空的代表新加 _contact_type在非Unedit 和 Delete时，判断新加前端有bug, 先添加，立即修改。状态会从add 变成 moidfy,这个是有问题。 if (empty($_sync_key)){ $uniqueAC =array(); foreach($consignee_id as $ck => $_consignee_id){ $_add_create_user = "Online_D_Address"; if(utils::in_array($_consignee_id,$uniqueAC)){ continue; } $uniqueAC[] = $_consignee_id; $_sync_key = common::uuid(); $_from_station = ""; $addressSql .= "INSERT INTO public.contacts_address_online( delivery_serial_no,action, addr_type,contact_id, addr1, addr2,addr3, addr4, ctry_code, city_code, postal_code, create_user, create_date, modify_user, modify_date, is_sync_country, sync_key, from_station,attn,tel) VALUES ('$delivery_serial_no','$_contact_type', 'D','$_consignee_id','$_address_1','$_address_2','$_address_3','$_address_4', '$_country','$_city','$_postal_code', '$_add_create_user',now(),'$_add_create_user',now(), true,'$_sync_key','$_from_station','$_contact_person','$_contact_number');"; $_sync_key_add = $_sync_key; } } else { if(!empty($_sync_key)){ $_add_create_user = "Online_D_Address"; if($_contact_type == "Unedit"){ continue; } $_is_delete = 'f'; if ($_contact_type == "Delete"){ $_is_delete = 't'; } $exist = common::excuteObjectSql("select sync_key,action from public.contacts_address_online where sync_key = '$_sync_key'"); if(empty($exist)){ $addressSql .= "INSERT INTO public.contacts_address_online( delivery_serial_no,action, addr_type,contact_id, addr1, addr2,addr3, addr4, ctry_code, city_code, postal_code, create_user, create_date, modify_user, modify_date, is_sync_country, sync_key, from_station,attn,tel,is_delete) VALUES ('$delivery_serial_no','$_contact_type', 'D','$_contact_id','$_address_1','$_address_2','$_address_3','$_address_4', '$_country','$_city','$_postal_code', '$_add_create_user',now(),'$_add_create_user',now(), true,'$_sync_key','$_from_station','$_contact_person','$_contact_number','$_is_delete');"; } else { //Modify Unedit :如果是用户完全新加的,这里不做action的修改，保留记录的Add $temp_action = $_contact_type; if ($exist['action'] == "Add"){ $temp_action = "Add"; } $addressSql .="UPDATE public.contacts_address_online SET delivery_serial_no = '$delivery_serial_no',addr1='$_address_1', addr2='$_address_2', addr3='$_address_3', addr4='$_address_4', ctry_code='$_country', city_code='$_city', postal_code='$_postal_code', modify_user='$_add_create_user', modify_date=now(), attn='$_contact_person', tel='$_contact_number', action='$temp_action', is_delete = '$_is_delete' WHERE sync_key = '$_sync_key';"; } } } } if(!empty($addressSql)){ common::excuteUpdateSql($addressSql); } return $_sync_key_add; } /** * 审核通过时，才取下放当时应用修改的地址 */ public static function returnDAddressRecord($address_country,$onine_address,$delivery_address_detail){ //deliver address is new $addressSql = ""; $address_country = json_decode($address_country,true); //onine_address 这个是查询出来的attn 不变 foreach($onine_address as $key => $_onine_address){ $_address_1 = common::check_input($_onine_address['addr1']); $_address_2 = common::check_input($_onine_address['addr2']); $_address_3 = common::check_input($_onine_address['addr3']); $_address_4 = common::check_input($_onine_address['addr4']); $_ctry_code = common::check_input($_onine_address['ctry_code']); $_city_code = common::check_input($_onine_address['city_code']); $_postal_code = common::check_input($_onine_address['postal_code']); $_attn = common::check_input($_onine_address['attn']); $_tel = common::check_input($_onine_address['tel']); $_contact_id = common::check_input($_onine_address['contact_id']); $_sync_key = common::check_input($_onine_address['sync_key']); $_from_station = strtoupper(common::check_input($_onine_address['from_station'])); $_contact_type = common::check_input($_onine_address['action']); $_is_delete = common::check_input($_onine_address['is_delete']); $addressSql .= "delete from public.contacts_address_online WHERE sync_key = '$_sync_key';"; $uniqueAC =array(); foreach($address_country as $ck => $_country){ if(utils::in_array($_country,$uniqueAC)){ continue; } $uniqueAC[] = $_country; $_add_create_user = "Online_D_Address"; $from_station = "ONLINE_".strtoupper($_country); $_sync_key_new = $_sync_key; if($ck > 0){ $_sync_key_new = common::uuid(); } $existData = common::excuteObjectSql("select sync_key FROM public.contacts_address WHERE sync_key = '$_sync_key' and from_station = '$from_station'"); if ($delivery_address_detail['sync_key'] == $_sync_key) { if(!empty($existData) && !empty($existData['sync_key'])){ $addressSql .= "UPDATE public.contacts_address SET addr1='$_address_1', addr2='$_address_2', ctry_code='$_ctry_code', city_code='$_city_code', postal_code='$_postal_code', attn = '$_attn',tel = '$_tel', modify_user='"._getLoginName()."', modify_date=now() WHERE sync_key = '$_sync_key';"; } else { $addressSql .= "INSERT INTO public.contacts_address( addr_type,contact_id, addr1, addr2,addr3, addr4, ctry_code, city_code, postal_code, create_user, create_date, modify_user, modify_date, is_sync_country, sync_key, from_station,attn,tel) VALUES ('D','$_contact_id','$_address_1','$_address_2','$_address_3','$_address_4', '$_ctry_code','$_city_code','$_postal_code', '$_add_create_user',now(),'$_add_create_user',now(), true,'$_sync_key_new','$from_station','$_attn','$_tel');"; } } else { //除开当前编辑的提交的，就只有用户自己删除要处理，其他的修改最后未选的情况留在本地管理 if ($_is_delete == 't' && !empty($existData) && !empty($existData['sync_key'])){ $addressSql .= "DELETE FROM public.contacts_address WHERE sync_key = '$_sync_key';"; } } } } return $addressSql; } /** * 当前save的地址信息 */ public static function returnDAddress($_sync_key_add=""){ //deliver address is new $$delivery_address = ""; $address_1 = $_POST['address_1']; foreach($address_1 as $key => $_address_1){ $_address_1 = $_address_1; $_address_2 = $_POST['address_2'][$key]; $_address_3 = $_POST['address_3'][$key]; $_address_4 = $_POST['address_4'][$key]; $_country = $_POST['country'][$key]; $_city = $_POST['city'][$key]; $_postal_code = $_POST['postal_code'][$key]; $_contact_person = $_POST['contact_person'][$key]; $_contact_number = $_POST['contact_number'][$key]; $_contact_id = $_POST['contact_id'][$key]; //如果是新加的为空，这取当前consignee_id WILSON80001 if(empty($_contact_id)){ $_contact_id = $_POST['consignee_id'][0]; } $_sync_key = $_POST['sync_key'][$key]; $_from_station = $_POST['from_station'][$key]; $_contact_type = $_POST['contact_type'][$key]; //if($_contact_type == "Add"){ if(!empty($_sync_key_add)){ //add 这么生成的唯一key $_sync_key = $_sync_key_add; } if ($_contact_type <> "Delete"){ $delivery_address = array("address_1"=>$_address_1, "address_2"=>$_address_2, "address_3"=>$_address_3, "address_4"=>$_address_4, "country"=>$_country, "city"=>$_city, "postal_code"=>$_postal_code, "contact_person"=>$_contact_person, "contact_number"=>$_contact_number, "contact_id"=>$_contact_id, "sync_key"=>$_sync_key, "from_station"=>$_from_station); } } return $delivery_address; } /** * Booking自動同步到Ksmart WO 的逻辑 保存在do_header和do_details表里 */ public static function saveWorkOrder($delivery_booking){ global $db; $h_serial_no_json = $delivery_booking['h_serial_no_json']; $h_serial_no_arr =json_decode($h_serial_no_json,true); $ctnr_db = $delivery_booking['ctnr']; //从UI 设计的case来讲，一个hbol就生成成一个wo ID foreach($h_serial_no_arr as $serial_no){ $sql = ""; $oceanInfo = common::excuteObjectSql("select *, case when order_from = 'public' and transport_mode ='sea' then (select manifest_type from public.ocean o where o.serial_no = oo.serial_no limit 1) when order_from = 'sfs' and transport_mode ='sea' then (select manifest_type from sfs.ocean o where o.serial_no = oo.serial_no limit 1) else ''::text end as _manifest_type from public.kln_ocean oo where oo.serial_no ='".$serial_no."'" ); //空運我們先不管，不做同步，就只做創建到審批，後面的讓他們自己錄入系統處理吧 if ($oceanInfo['transport_mode'] == "air" || strtoupper($oceanInfo['_manifest_type']) == "BCN"){ continue; } $biztype = $oceanInfo['ex_im'] == "Export" && $oceanInfo['transport_mode'] == "sea" ? "SE" :( $oceanInfo['ex_im'] == "Import" && $oceanInfo['transport_mode'] == "sea" ? "SI" :( $oceanInfo['ex_im'] == "Export" && $oceanInfo['transport_mode'] == "air" ? "AE" :( $oceanInfo['ex_im'] == "Import" && $oceanInfo['transport_mode'] == "air" ? "AI" : "" ))); $mode_type = $delivery_booking['delivery_mode'] == "Truck" ? "Road" : "Rail"; $consignee = $oceanInfo['consignee_id']; $_schema = $oceanInfo['order_from']; $_schema_contacts = $oceanInfo['order_from']; if($oceanInfo['order_from'] == "public"){ $_schema_contacts = "ocean"; } $delivery_address =json_decode($delivery_booking['delivery_address_detail'],true); $consignee_name = common::excuteOneSql("select company from $_schema_contacts.contacts where contact_id = '$consignee' limit 1"); $consignee_exp = common::combineContactInfoString($delivery_address); $consignee_sync_key = $delivery_address['sync_key']; $consignee_country_code = $delivery_address['country']; $consignee_city_code = $delivery_address['city']; $consignee_pic = $delivery_address['contact_person']; $consignee_tel = $delivery_address['contact_number']; $consignee_address1 = $delivery_address['address_1']; $consignee_address2 = $delivery_address['address_2']; $consignee_address3 = $delivery_address['address_3']; $consignee_address4 = $delivery_address['address_4']; $ctnrs = common::excuteListSql("select * FROM $_schema.oc_container oc where oc.serial_no::text = '".$serial_no."' and oc.ctnr in (select regexp_split_to_table('".$ctnr_db."', ','))" ); $total_pcs = ""; $total_pcs_unit = ""; $total_weight = ""; $total_weight_unit = "KGS"; $total_volume = ""; $total_volume_unit = "CBM"; $detail_row_count = 0; foreach($ctnrs as $_ctnr_info){ $total_pcs_unit = $_ctnr_info['unit']; $total_pcs += $_ctnr_info['qty']; $total_weight += $_ctnr_info['grs_kgs']; $total_volume += $_ctnr_info['cbm']; $detail_row_count +=1; } $total_pcs = empty($total_pcs) ? "null" : "'".$total_pcs."'"; $total_weight = empty($total_weight) ? "null" : "'".$total_weight."'"; $total_volume = empty($total_volume) ? "null" : "'".$total_volume."'"; $do_serial_no = common::uuid(); $sql .="INSERT INTO public.do_header( do_serial_no,detail_row_count, air_ocean, station_code, biztype,delivery_type,mode_type, serial_no, lot_no, awbbl_no, job_no, do_date, total_pcs, total_pcs_unit, total_weight,total_weight_unit, total_volume, total_volume_unit, est_delivery_date, est_delivery_time,instructions, create_user, create_time,delivery_vehicle_type,delivery_fuel_type, consignee, consignee_name, consignee_exp, consignee_address1,consignee_address2,consignee_address3,consignee_address4, consignee_country_code,consignee_city_code,consignee_pic,consignee_tel,consignee_addr_type_id, delivery_to_address_sync_key,refno) VALUES ('".$do_serial_no."','".$detail_row_count."','Ocean', '".$oceanInfo['agent']."','".$biztype."','Awaiting Confirmation','".$mode_type."', '".$serial_no."','".$oceanInfo['m_bol']."','".$oceanInfo['h_bol']."','".$oceanInfo['job_no']."',now(), ".$total_pcs.",'".$total_pcs_unit."',".$total_weight.",'".$total_weight_unit."',".$total_volume.",'".$total_volume_unit."', '".$delivery_booking['_delivery_date']."','".$delivery_booking['_delivery_time']."', '".$delivery_booking['special_requirements']."', 'KLN_Online',now(),'Subcontractor','Diesel','".$consignee."','".$consignee_name."','".$consignee_exp."', '".$consignee_address1."','".$consignee_address2."','".$consignee_address3."','".$consignee_address4."', '".$consignee_country_code."','".$consignee_city_code."','".$consignee_pic."','".$consignee_tel."',0, '".$consignee_sync_key."','".$oceanInfo['delivery_reference']."');"; foreach($ctnrs as $ctnr){ $qty = empty($ctnr['qty']) ? "null" : "'".$ctnr['qty']."'"; $grs_kgs = empty($ctnr['grs_kgs']) ? "null" : "'".$ctnr['grs_kgs']."'"; $cbm = empty($ctnr['cbm']) ? "null" : "'".$ctnr['cbm']."'"; $l4_length = empty($ctnr['l4_length']) ? "null" : "'".$ctnr['l4_length']."'"; $l4_width = empty($ctnr['l4_width']) ? "null" : "'".$ctnr['l4_width']."'"; $l4_height = empty($ctnr['l4_height']) ? "null" : "'".$ctnr['l4_height']."'"; $serial_no_sync_key = common::uuid(); $sql .="INSERT INTO public.do_detail( do_serial_no, pcs, pcs_unit, weight, weight_unit, volume, marks, description, create_user, create_time, ctnr, size, seal_no, is_lcl, length,width,height,serial_no_sync_key) VALUES ('".$do_serial_no."',$qty, '".$ctnr['unit']."',$grs_kgs,'KGS',$cbm, '".$ctnr['marks']."','".$ctnr['description']."','KLN_Online',now(), '".$ctnr['ctnr']."','".$ctnr['size']."','".$ctnr['seal_no']."','".$ctnr['is_lcl']."', $l4_length,$l4_width,$l4_height,'$serial_no_sync_key');"; } $db->Execute($sql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $sql), 0)); } } public static function combineContactInfoString($contact){ $backString = ""; if ($contact !== null) { $address1 = trim($contact['address_1'] ?? ''); $address2 = trim($contact['address_2'] ?? ''); $address3 = trim($contact['address_3'] ?? ''); $address4 = trim($contact['address_4'] ?? ''); $city = trim($contact['city'] ?? ''); $zipcode = trim($contact['postal_code'] ?? ''); $country = trim($contact['country'] ?? ''); // 拼接非空的地址行 if ($address1 !== '') { $backString .= $address1; } if ($address2 !== '') { $backString .= ($backString !== '' ? "\n" : '') . $address2; } if ($address3 !== '') { $backString .= ($backString !== '' ? "\n" : '') . $address3; } if ($address4 !== '') { $backString .= ($backString !== '' ? "\n" : '') . $address4; } // 添加城市、邮编和国家 $location = trim("$city $zipcode $country"); if ($location !== '') { $backString .= ($backString !== '' ? "\n" : '') . $location; } } return trim($backString); } /** * 过滤文件名中的非法字符（适用于 Windows / Linux / macOS） * * @param string $filename 原始文件名 * @param string $replacement 替换非法字符的符号（默认用下划线） * @return string 安全的文件名 */ public static function sanitizeFilename($filename, $replacement = '_') { // 定义非法字符（Windows 系统最严格） $illegalChars = ['\\', '/', ':', '*', '?', '"', '<', '>', '|', "\0"]; // 更严格的：也替换制表符、换行符、回车 $illegalChars = array_merge($illegalChars, ["\t", "\n", "\r"]); // 替换所有非法字符 $safeName = str_replace($illegalChars, $replacement, $filename); // 额外清理：多个连续的替换符 → 变成一个（如 ___ → _） $safeName = preg_replace('/' . preg_quote($replacement, '/') . '+/', $replacement, $safeName); // 去除首尾空白和替换符 $safeName = trim($safeName, " \t\n\r\0\x0B" . $replacement); // 防止以点或横线开头/结尾（某些系统敏感） $safeName = trim($safeName, '.-'); // 如果清理后为空，返回默认名 if ($safeName === '' || $safeName === '.' || $safeName === '..') { $safeName = 'file_' . time(); } return $safeName; } /** * Packing List 和 Commercial Invoice 顺序处理,以后自定义了可以移除这个函数 */ public static function destination_delivery_shipment_search_sort($columns){ // 找出目标元素的位置和需要移动的元素 $moveItems = []; $targetIndex = null; foreach ($columns as $index => $item) { if ($item['field'] === 'package_type') { $targetIndex = $index; } elseif (in_array($item['title'], ['Packing List', 'Commercial Invoice'])) { $moveItems[] = $item; unset($columns[$index]); // 移除这些元素 } } // 如果找到了目标位置并且有需要移动的元素 if ($targetIndex !== null && count($moveItems) > 0) { // 在目标位置后插入需要移动的元素 array_splice($columns, $targetIndex + 1, 0, $moveItems); // 重置数组索引 $columns = array_values($columns); } return $columns; } } ?>