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;
}
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){
$resData = array();
$resData["code"] = $code;
$resData["data"] =$data;
//echo utils::jsonFiltration("null", "\"\"", json_encode($resData));
echo json_encode($resData);
}
/*
* 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("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];
}
/*
* 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'";
}else{
$transport_mode = utils::implode(';', $transportation);
$mode_param = common::getInNotInSqlForSearch($transport_mode);
}
$sqlWhere .= " and transport_mode 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='air')) then COALESCE(carbon_emission,0) else 0 end) as air_rc,
sum(case when (1<>1 or (transport_mode='sea')) then COALESCE(carbon_emission,0) else 0 end) as sea_rc,
sum(case when (1<>1 or (transport_mode='road')) then COALESCE(carbon_emission,0) else 0 end) as road_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='air')) then COALESCE(carbon_emission,0) else 0 end) as air_rc,
sum(case when (1<>1 or (transport_mode='sea')) then COALESCE(carbon_emission,0) else 0 end) as sea_rc,
sum(case when (1<>1 or (transport_mode='road')) then COALESCE(carbon_emission,0) else 0 end) as road_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");
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'";
}else{
$transport_mode = utils::implode(';', $transportation);
$mode_param = common::getInNotInSqlForSearch($transport_mode);
}
$sqlWhere .= " and transport_mode 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'";
}else{
$transport_mode = utils::implode(';', $transportation);
$mode_param = common::getInNotInSqlForSearch($transport_mode);
}
$sqlWhere .= " and transport_mode 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'";
}else{
$transport_mode = utils::implode(';', $transportation);
$mode_param = common::getInNotInSqlForSearch($transport_mode);
}
return " and transport_mode 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;
}
/**
* 处理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) {
$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]);
$intersectionC = "";
//这个特殊。如果ports 存在一样的，那检查carrier 是否存在一样的。
if ($name == "Sea Rule") {
$setC1 = explode(",",$_POST['carrier'][$i]) ?? [];
$setC2 = explode(",",$_POST['carrier'][$j]) ?? [];
$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,$rule_type){
$intersection = array_intersect($set1, $set2);
if ($rule_type <> '*Default Rule' && ($set1 == 'ALL' || $set2 == 'ALL')){
$intersection = "ALL";
}
return $intersection;
}
public static function sendDestinationDeliveryReminder($data,$shipmentsData,$status){
$html_tr = "";
foreach($shipmentsData['data'] as $shipment){
$html_tr .="
| ".$shipment['h_bol']." |
".$shipment['ctnr']." |
".$shipment['package_type']." |
".$shipment['kgw']." |
";
}
$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;
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]);
if ($_contact_type == "Add"){
$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,contact_person,contact_number)
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"){
$_add_create_user = $_create_user;
}
$exist = common::excuteObjectSql("select sync_key,action from public.contacts_address_online where sync_key = '$_sync_key' and delivery_serial_no = '$delivery_serial_no'");
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,contact_person,contact_number)
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');";
} else {
if ($_contact_type == "Delete" && $exist['action'] == "Add"){
//代表未审核新加的，这次提交为删除
$addressSql .= "delete from public.contacts_address_online where sync_key = '$_sync_key' and delivery_serial_no = '$delivery_serial_no';";
}
// elseif ($_contact_type == "Delete" && ($exist['action'] == "Modify" || $exist['action'] == "Unedit")){
// //代表未审核编辑的，这次提交为删除，修改action固定为Detele
// $addressSql .="UPDATE public.contacts_address_online
// SET 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(),
// contact_person='$_contact_person', contact_number='$_contact_number',
// action='Delete'
// WHERE sync_key = '$_sync_key' and delivery_serial_no = '$delivery_serial_no';";
// }
else {
//Modify Unedit :如果是用户完全新加的,这里不做action的修改，保留记录的Add
$temp_action = $_contact_type;
if ($exist['action'] == "Add"){
$temp_action = "Add";
}
$addressSql .="UPDATE public.contacts_address_online
SET 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(),
contact_person='$_contact_person', contact_number='$_contact_number',
action='$temp_action'
WHERE sync_key = '$_sync_key' and delivery_serial_no = '$delivery_serial_no';";
}
}
}
}
}
common::excuteUpdateSql($addressSql);
return $_sync_key_add;
}
public static function returnDAddressRecord($address_country,$onine_address){
//deliver address is new
$addressSql = "";
$address_country = json_decode($address_country,true);
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']);
$_country = common::check_input($_onine_address['ctry_code']);
$_city = common::check_input($_onine_address['city_code']);
$_postal_code = common::check_input($_onine_address['postal_code']);
$_contact_person = common::check_input($_onine_address['contact_person']);
$_contact_number = common::check_input($_onine_address['contact_number']);
$_contact_id = common::check_input($_onine_address['contact_id']);
$_sync_key = common::check_input($_onine_address['sync_key']);
$_from_station = common::check_input($_onine_address['from_station']);
$_contact_type = common::check_input($_onine_address['action']);
//适应前端逻辑，如果遇到Unedit 代表该地址未变动,移除变动记录里
if ($_contact_type == "Unedit"){
continue;
}
$uniqueAC =array();
foreach($address_country as $ck => $_country){
if(utils::in_array($_country,$uniqueAC)){
continue;
}
$uniqueAC[] = $_country;
//如果需要新加的时候 用户固定名称，用作标记 //contact_person,contact_number '$_contact_person','$_contact_number'
$_add_create_user = "Online_D_Address";
if ($_contact_type == "Add"){
$sync_key = common::uuid();
$from_station = "Online_".strtoupper($_country);
$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)
VALUES ('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');";
}
if ($_contact_type == "Modify"){
//代表在一个国家内的编辑 //contact_person='$_contact_person', contact_number='$_contact_number'
$from_station_country = "";
if (strpos($_from_station, "Online_") === 0) { // 确保字符串以 Online_ 开头
$from_station_country = substr($_from_station, strlen("Online_"));
}
if($from_station_country == $_country){
$addressSql .= "UPDATE public.contacts_address
SET addr1='$_address_1', addr2='$_address_2',
ctry_code='$_country', city_code='$_city', postal_code='$_postal_code',
modify_user='"._getLoginName()."', modify_date=now()
WHERE sync_key = '$_sync_key';";
} else {
$sync_key = common::uuid();
$from_station = "Online_".strtoupper($_country);
$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)
VALUES ('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');";
}
}
if ($_contact_type == "Delete"){
$from_station_country = "";
if (strpos($_from_station, "Online_") === 0) { // 确保字符串以 Online_ 开头
$from_station_country = substr($_from_station, strlen("Online_"));
}
if($from_station_country == $_country){
$addressSql .= "DELETE FROM public.contacts_address WHERE sync_key = '$_sync_key';";
}
}
}
}
return $addressSql;
}
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];
$_sync_key = $_POST['sync_key'][$key];
$_from_station = $_POST['from_station'][$key];
$_contact_type = $_POST['contact_type'][$key];
if($_contact_type == "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,
"contact_type"=>"Unedit");
}
}
return $delivery_address;
}
}
?>