$rule["SuperMaxLen"]) {
$str ="Super user password length between ".$rule["SuperMinLen"]." and ".$rule["SuperMaxLen"];
}
}else{
//校验密码长度
if (strlen($password)<$rule["MinLen"]||strlen($password)>$rule["MaxLen"]) {
$str ="Password length between ".$rule["MinLen"]." and ".$rule["MaxLen"];
}
}
return $str;
}else{
if (preg_match('/^\d*$/', $password) || preg_match('/^[a-zA-Z]+$/', $password)) {
$str ="Must include letters and numbers";
}
$len = strlen($password);
$t = substr($password, 0, 1);
for ($i = 1; $i < $len; $i++) {
$t1 = substr($password, $i, 1);
if ($t != $t1) {
return "";
}
}
return "error";
}
}
//隐藏邮箱地址
public static function maskEmail($email) {
$idex = strlen($email) - strrpos($email, ".");
$mask = substr($email, 0, 1) . str_repeat('*', 6) . "@" . str_repeat('*', 3) . substr($email, -$idex);
return $mask;
}
public static function getInSql($str, $not = false, $sep = ";") {
$str = trim($str);
$str = trim($str, $sep);
$str = trim($str);
if (empty($str) && $str !== "0" && $str !== 0)
return "1<>1";
$str = strtolower($str);
if (utils::checkExist($str, $sep)) {
$aa = explode($sep, $str);
$msg = "";
foreach ($aa as $value) {
$value = trim($value);
if (empty($value))
continue;
if (empty($msg))
$msg = "'" . common::check_input($value) . "'";
else
$msg .= ",'" . common::check_input($value) . "'";
}
if ($not !== FALSE)
return " not in (" . $msg . ")";
else
return " in (" . $msg . ")";
} else {
if ($not !== FALSE)
return " != '" . common::check_input(trim($str)) . "'";
else
return " = '" . common::check_input(trim($str)) . "'";
}
}
public static function checkExist($string, $search, $u = TRUE) {
if ($u === TRUE) {
if (stripos($string, $search) !== false)
return TRUE;
}else {
if (strpos($string, $search) !== false)
return TRUE;
}
return FALSE;
}
public static function endWith($string, $end, $u = TRUE) {
if ($u === TRUE) {
$string = strtolower($string);
$end = strtolower($end);
return strrchr($string, $end) == $end;
}
return strrchr($string, $end) == $end;
}
public static function _get($str) {
$rs = isset($_POST[$str]) ? $_POST[$str] : null;
if (empty($rs))
$rs = isset($_GET[$str]) ? $_GET[$str] : null;
return $rs;
}
public static function startWith($string, $start, $u = TRUE) {
if ($u === TRUE)
return stripos($string, $start) === 0;
return strpos($string, $start) === 0;
}
public static function outDisplay($content, $is_time = 'f', $is_first = 'f', $is_boolean = 'f', $excel_export = FALSE) {
if (empty($content) && $content !== 0 && $content !== "0")
return "";
if (strtolower($is_time) == 't')
return utils::dealTimeDisplay($content);
if (strtolower($is_first) == 't') {
if ($excel_export !== FALSE)
return utils::getCompanyName($content);
else
return '' . utils::getCompanyName($content) . '';
}
if (strtolower($is_boolean) == 't')
return utils::outTrue($content);
return nl2br($content);
}
public static function _output($value) {
if (empty($value))
return " ";
else
return $value;
}
public static function dealTimeDisplay($date) {
if (empty($date))
return "";
if (strlen($date) > 10)
return date("m/d/Y H:i:s", strtotime($date));
return date("m/d/Y", strtotime($date));
}
public static function outDisplayForMerge($frist,$last,$split = "/") {
if($frist == $last){
return $frist;
}
if (!empty($frist)){
if(!empty($last)){
return $frist.$split.$last;
}else{
return $frist;
}
}else{
return $last;
}
}
public static function outTrue($r) {
if (empty($r))
return "No";
$r = strtolower($r);
if ($r == "t")
return "Yes";
elseif ($r == "f")
return "No";
else
return $r;
}
public static function getCompanyName($detail) {
$detail = nl2br($detail);
if (strpos($detail, '
') === FALSE)
return $detail;
return substr($detail, 0, strpos($detail, '
'));
}
public static function getEmail($serial_no) {
$ocean = common::excuteObjectSql("select sales_rep, last_user, created_by, order_from, h_bol, consignee, dest_op, agent from public.kln_ocean where md5(serial_no)=md5('$serial_no') "
. "order by schem_not_display nulls last limit 1");
$schema = $ocean["order_from"] . ".";
$dest_op_from_agent = common::excuteOneSql("select dest_op_from_agent from " . $schema . "ocean where md5(serial_no)=md5('$serial_no')");
if ($ocean["agent"] == "KYMTL" || $ocean["agent"] == "KYYYZ") {
$email = array();
$email["email"] = "";
if (!empty($dest_op_from_agent)) {
$so_email = common::excuteOneSql("select email from " . $schema . "employee where employee_id='" . $dest_op_from_agent . "' and active=true");
}
if (!empty($so_email)) {
if (empty($email["email"])) {
$email["email"] = $so_email;
} else {
$email["email"] .= ";" . $so_email;
}
}
if (!empty($ocean["sales_rep"])) {
$rep_email = common::excuteOneSql("select email from " . $schema . "employee where lower(salesopcode)='" . strtolower($ocean["sales_rep"]) . "' and active=true");
if (!empty($rep_email)) {
if (empty($email["email"])) {
$email["email"] = $rep_email;
} else {
$email["email"] .= ";" . $rep_email;
}
}
}
} else {
$email = common::excuteObjectSql("select string_agg(e.email, ';') as email, string_agg(e.first_name, ';') as name from " . $schema . "ra_online_user u, " . $schema . "employee e WHERE u.employee_id = e.employee_id and "
. "lower(u.user_login) in ('" . strtolower($ocean["created_by"]) . "', '" . strtolower($ocean["last_user"]) . "')");
if (empty($dest_op_from_agent)) {
if (!empty($ocean["dest_op"])) {
$so_email = common::excuteOneSql("select email from " . $schema . "employee where employee_id='" . $ocean["dest_op"] . "' and active=true");
}
} else {
$so_email = common::excuteOneSql("select email from " . $schema . "employee where employee_id='" . $dest_op_from_agent . "' and active=true");
}
if (empty($so_email)) {
if ($ocean["agent"] == "APEXSFO") {
$so_email = "oid2@apexshipping.com";
}
if ($ocean["agent"] == "APEXLAX") {
$so_email = "laxoid@apexshipping.com";
}
if ($ocean["agent"] == "APEXNYC") {
$so_email = "NYCOID@APEXSHIPPING.COM";
}
if ($ocean["agent"] == "APEXPNW") {
$so_email = "pnwoid@apexshipping.com";
}
if ($ocean["agent"] == "STLUTA") {
$so_email = "starlinkOID@apexshipping.com ";
}
if ($ocean["agent"] == "APEXORD") {
$so_email = "ordoid@apexshipping.com";
}
}
if (!empty($so_email)) {
if (empty($email["email"])) {
$email["email"] = $so_email;
} else {
$email["email"] .= ";" . $so_email;
}
}
if (!empty($ocean["sales_rep"])) {
$rep_email = common::excuteOneSql("select email from " . $schema . "employee where lower(salesopcode)='" . strtolower($ocean["sales_rep"]) . "' and active=true");
if (!empty($rep_email)) {
if (empty($email["email"])) {
$email["email"] = $rep_email;
} else {
$email["email"] .= ";" . $rep_email;
}
}
}
}
$email["h_bol"] = $ocean["h_bol"];
$email["consignee"] = $ocean["consignee"];
return $email;
}
/***
* 过滤json中的某个数据
* @param unknown $json
* @param unknown $search
* @param unknown $replace
* @return mixed
*/
public static function jsonFiltration($search,$replace,$json){
//处理json中将斜杠转义问题
$json = str_replace("\\/", "/", $json);
return str_replace($search, $replace, $json);
}
/*
* calculate eta destination by etd port
*/
public static function calculate_ETA_Des($serial_no) {
$sql = "SELECT m_eta as eat, mport_of_discharge as poul, place_of_delivery as pod,service from ocean where lower(serial_no) = '" . strtolower($serial_no) . "'";
$rs = common::excuteObjectSql($sql);
$date = "";
if (!empty($rs['eat'])) {
$date = utils::calculate_ETA_Dest($rs['eat'], $rs['poul'], $rs['pod'], $rs['service']);
}
return $date;
}
public static function calculate_ETA_Dest($eta, $poul, $pod, $service) {
if (empty($poul) || empty($pod))
return $eta;
$sql = "SELECT door_days, cy_days
FROM eta_dest
WHERE eta_dest.state::text = ((( SELECT unlocode.state
FROM ports, unlocode
WHERE ports.uncode::text = unlocode.uncode::text AND ports.code::text = '" . common::check_input($pod) . "'
LIMIT 1))::text) AND (','::text || eta_dest.pod::text) ~~* (('%,'::text || '" . common::check_input($poul) . "') || '%'::text)
LIMIT 1";
//$sql = "select door_days, cy_days from eta_dest where state = (select state from ports where code = '" . common::check_input($poul) . "' limit 1) and ','||pod ilike '%," . common::check_input($pod) . "%'";
$rs = common::excuteObjectSql($sql);
if (empty($rs))
return $eta;
if (utils::endWith($service, "cy"))
return common::addDays($eta, $rs['cy_days']);
else
return common::addDays($eta, $rs['door_days']);
}
/*
* password change, email alert
*/
public static function sendEmailByPassword($username, $password, $email, $companyname='') {
$sql = "select subject, ra_content as content from ra_online_email_tpl where lower(ra_type) = 'forgotpw'";
$rs = common::excuteObjectSql($sql);
if (!empty($rs)) {
$subject = $rs['subject'];
$content = $rs['content'];
}
if (!empty($subject) && !empty($content)) {
$content = str_replace('<{username}>', $username, $content);
$content = str_replace('<{password}>', $password, $content);
$content = str_replace('<{companyname}>', $companyname, $content);
global $db;
common::excuteUpdateSql("INSERT INTO public.email_record_forgotpassword(type, title, from_email, to_email, content, insert_date,
cc_email) VALUES ('forgot_password', '" . common::check_input($subject) . "', 'US.KApex.Online@kerryapex.com', '" .
common::check_input($email) . "', '" . common::check_input($content) . "', now(), '');");
return "success";
//return Mail::sendMail($email, $subject, $content);
} else
return null;
}
/**
* reset password link email sendEmailByResetPassword
*/
public static function sendEmailByResetPassword($data, $email) {
if(strtolower($data["user_type"]) == "employee"){
$sql = "select subject, ra_content as content from ra_online_email_tpl where lower(ra_type) = 'ra_reset'";
} else {
$sql = "select subject, ra_content as content from ra_online_email_tpl where lower(ra_type) = 'kln_reset'";
}
$rs = common::excuteObjectSql($sql);
if (!empty($rs)) {
$subject = $rs['subject'];
$content = $rs['content'];
}
if (!empty($subject) && !empty($content)) {
$verifcation_code = utils::AES_128_CBC_Encrypt($data['user_login'],"USAIandy20244Q9X","0123456123456789");
//http://localhost:8080/k_new_online/login?state=reset&verifcation_code=XXX
$encoded = rawurlencode($verifcation_code);
$link = SERVER_Vue_PAHT."login?state=reset&name=".$data['user_login']."&verifcation_code=".$encoded;
$tplData = array("username"=>$data['user_login'],
"creation_date"=>$data['current_time'],
"link" =>$link);
// 动态构建替换数组（格式：[key] => value）
$replacements = [];
foreach ($tplData as $key => $value) {
$replacements["{{$key}}"] = $value;
$replacements["{{{$key}}}"] = $value;
}
$content = strtr($content, $replacements);
$content = common::check_input($content);
common::excuteUpdateSql("INSERT INTO public.email_record_forgotpassword(type, title, from_email, to_email, content, insert_date,
cc_email) VALUES ('forgot_password', '" . common::check_input($subject) . "', 'US.KApex.Online@kerryapex.com', '" .
common::check_input($email) . "', '" . $content . "', now(), '');
INSERT INTO public.customer_service_secret_key(secret_key, create_time) VALUES('".$verifcation_code."',now())");
return "success";
//return Mail::sendMail($email, $subject, $content);
} else
return null;
}
/**
* reset password link email sendEmailByResetPassword
*/
public static function sendEmailWithPasswordUpdate($user_login, $user_type) {
if (strtolower($user_type) == "employee"){
$sql = "select subject, ra_content as content from ra_online_email_tpl where lower(ra_type) = 'ra_passwordchange'";
} else {
$sql = "select subject, ra_content as content from ra_online_email_tpl where lower(ra_type) = 'kln_passwordchange'";
}
$rs = common::excuteObjectSql($sql);
if (!empty($rs)) {
$subject = $rs['subject'];
$content = $rs['content'];
}
if (!empty($subject) && !empty($content)) {
if(strtolower($user_type) == "employee"){
$sql = "SELECT lower(ra_name) as ra_name, ra_value from ra_online_config where lower(ra_name) in ('employee_password_change_cycle')";
} else {
$sql = "SELECT lower(ra_name) as ra_name, ra_value from ra_online_config where lower(ra_name) in ('customer_password_change_cycle')";
}
$PASSWORD_CHANGE_CYCLE = 0;
$rs1s = common::excuteListSql($sql);
foreach ($rs1s as $rs1) {
$PASSWORD_CHANGE_CYCLE = $rs1['ra_value'];
}
$dateInfo = common::excuteObjectSql("select
to_char(now(), 'Mon-DD-YYYY') as current_date,
to_char(now(), 'Mon-DD-YYYY HH24:MI:SS') as current_time,
to_char((current_date + INTERVAL '".$PASSWORD_CHANGE_CYCLE." days'),'Mon-DD-YYYY') as expiry_date ;");
$tplData = array("username"=>$user_login,
"creation_date"=>$dateInfo['current_time'],
"update_date"=>$dateInfo['current_date'],
"expiry_date"=>$dateInfo['expiry_date']);
// 动态构建替换数组（格式：[key] => value）
$replacements = [];
foreach ($tplData as $key => $value) {
$replacements["{{$key}}"] = $value;
$replacements["{{{$key}}}"] = $value;
}
$content = strtr($content, $replacements);
$content = common::check_input($content);
$email = _getLoginEamil();
$sql = "INSERT INTO email_record(type, title, from_email, to_email, content, insert_date, cc_email, attachment_path)
VALUES ('kln_passwordChange', '" . common::check_input($subject) . "', 'US.KApex.Online@kerryapex.com', '" .
common::check_input($email) . "', '" . common::check_input($content) . "', now(), '', '');";
common::excuteUpdateSql($sql);
return "success";
} else
return null;
}
public static function operation_log_records(){
//排除opreation_log操作
if( empty($_REQUEST["operate"])
|| ($_REQUEST["action"] == "login" && $_REQUEST["operate"] == "verifcation_code")
|| ($_REQUEST["action"] == "login" && $_REQUEST["operate"] == "check_uname")
|| ($_REQUEST["action"] == "ocean_order" && $_REQUEST["operate"] == "setting_ocean_order_display")
|| ($_REQUEST["action"] == "ocean_booking" && $_REQUEST["operate"] == "setting_display")){
return;
}
//排除robot的相关的记录
if((stripos($_REQUEST["action"], "robot") === 0)){
return;
}
if($_REQUEST["action"] == "login" && $_REQUEST["operate"] == "tracking_checked"){
//public tracking_checked 的user name 记录对应IP 地址
$user_type = "Customer";
$user_name = common::ip();
} elseif($_REQUEST["action"] == "login" && $_REQUEST["operate"] == "do_login"){
//移除do_login 因为在登录的过程中，是没有用户信息的
$user_name = $_REQUEST["uname"];
} else{
$user_name = _getLoginName();
}
$user_type = _isApexLogin() ? "Employee" : "Customer";
//如果在没有登录前，没有登录信息，指定用户
if(!isset($_SESSION['ONLINE_USER'])){
$user_type = common::excuteOneSql("select user_type from public.ra_online_user u where lower(user_login) = '" . strtolower($user_name) . "'");
}
$operateInfo = utils::getPageByAction($_REQUEST["action"],$_REQUEST["operate"]);
$page = $operateInfo["page"];
$operation = $operateInfo["operate"];
$operation_detail = utils::analyzeOperationDetail($_REQUEST["action"],$_REQUEST["operate"]);
if(empty($operation_detail)){
$operation_detail = common::check_input(utils::jsonFiltration("null", "\"\"", json_encode($_REQUEST)));
}
//过滤一分钟以内，相同用户的重复请求
$exist_sql = "select count(1) from public.customer_service_operation_log
where user_name = '$user_name'
and page = '$page' and operation = '$operation' and operation_detail = '$operation_detail'
and operation_time > NOW() - INTERVAL '1 minute' limit 1;";
$exist_obj = common::excuteOneSql($exist_sql);
if(empty($exist_obj)){
$sql = "INSERT INTO public.customer_service_operation_log(user_type, user_name, page, operation, operation_detail,
operation_time)
VALUES ('$user_type', '$user_name', '$page', '$operation', '$operation_detail', now())";
common::excuteUpdateSql($sql);
}
}
public static function getPageByAction($action,$operate){
//取消
$operationConvertName = array(
"login=do_login" => array("page" =>"Login","operate"=>"Login"),
"login=forgot_password" => array("page" =>"Login","operate"=>"Forgot_PPassword"),
"login=logout" => array("page" =>"logout","operate"=>"logout"),
"login=update_pwd_expires" => array("page" =>"Login","operate"=>"Reset password"),
"ocean_booking=search" => array("page" =>"Booking","operate"=>"Search"),
"Booking_Search=save_setting_display" => array("page" =>"Booking","operate"=>"Customize Coulumns"),
"booking=autody" => array("page" =>"Booking","operate"=>"More Filter"),
"booking=autoport" => array("page" =>"Booking","operate"=>"More Filter"),
"ocean_booking=detail" => array("page" =>"Booking","operate"=>"Open Detailed Page"),
"ocean_booking=excel" => array("page" =>"Booking","operate"=>"Download"),
"ocean_booking=save_communication" => array("page" =>"Booking","operate"=>"Send Email"),
"opreation_log=search" => array("page" =>"Opreation_log","operate"=>"Search"),
"login=tracking_checked" => array("page" =>"Tracking","operate"=>"Public tracking"),
"ocean_order=search" => array("page" =>"Tracking","operate"=>"Search"),
"Ocean_Search=save_setting_display" => array("page" =>"Tracking","operate"=>"Customize Coulumns"),
"tracking=autody" => array("page" =>"Tracking","operate"=>"More Filter"),
"tracking=autoport" => array("page" =>"Tracking","operate"=>"More Filter"),
"ocean_order=detail" => array("page" =>"Tracking","operate"=>"Open Detailed Page"),
"ocean_order=excel" => array("page" =>"Tracking","operate"=>"Download"),
"ocean_order=download" => array("page" =>"Tracking","operate"=>"Download"),
"ocean_order=save_communication" => array("page" =>"Booking","operate"=>"Send Email"),
"ocean_order=ams_isf_log" => array("page" =>"Tracking","operate"=>"AMS/ISF"),
"ocean_order=ocean_vgm" => array("page" =>"Tracking","operate"=>"Enter VGM"),
"ocean_order=save_ocean_vgm" => array("page" =>"Tracking","operate"=>"Save VGM"),
"ocean_order=share_shipment" => array("page" =>"Tracking","operate"=>"Share shipment"),
"ocean_order=document_upload" => array("page" =>"Tracking","operate"=>"document_upload"),
"ocean_order=document_upload_do" => array("page" =>"Tracking","operate"=>"document_upload_do"),
"tools=mark_save" => array("page" =>"Tools","operate"=>"Mark_Save"),
"password=" => array("page" =>"Profile","operate"=>"Change password"));
if($action == "ajax" && $operate == "save_setting_display"){
$model_name = $_REQUEST['model_name'];
return $operationConvertName[$model_name."=".$operate];
}
if($action == "ajax" && ($operate == "autody" || $operate == "autoport")){
$model_name = $_REQUEST['search_mode'];
return $operationConvertName[$model_name."=".$operate];
}
return $operationConvertName[$action."=".$operate];
}
public static function analyzeOperationDetail($action,$operate){
$detail = "";
if($action == "login" && $operate == "do_login"){
$detail = 'System Account';
if($_REQUEST['token']){
$detail = 'From Apex Online';
}
}
if($action == "login" && $operate == "logout"){
$detail = 'User Logout';
}
if($action == "login" && $operate == "tracking_checked"){
$detail = 'Join public tracking action,Public tracking number:'.$_POST['reference_number'];
}
if($action == "password"){
$detail = 'User Change password';
}
if(($action == "ocean_booking" || $action == "ocean_order") && $operate == "search"){
$detail = "";
//还有一个同以分钟内，不记录相同的查询 这个需要建表查询
//{"action":"ocean_booking","operate":"search","_ntype":"ocean_booking","cp":"1","ps":"100","rc":"-1","other_filed":"","uname":"ra.admin","psw":"abc123456789"}
$filter_common_field = array("action","operate","_ntype","cp","ps","rc","other_filed","uname","psw");
foreach($_REQUEST as $selected_key => $selected){
if(!utils::in_array($selected_key, $filter_common_field)){
if(is_array($selected)){
$selected = utils::implode(",",$selected);
}
$detail .="$selected_key:".$selected."; ";
}
}
if(empty($detail)){
$detail .="No search condition";
}
}
if($action == "ajax" && $operate == "save_setting_display"){
$detail = "";
$type = $_REQUEST['model_name'] == "Booking_Search" ? "Booking_Search" : "Ocean_Search";
//记录最终save 和 default 字段相比的结果
// $default_ids = common::excuteListSql("select id,display_name from public.ra_online_search_display_cso where model_name = '$type'
// and display_name in('Booking No.','MBL No.','HBL No.','Mode','Status',
// 'Shipper','Consignee','Origin Agent','Destination Agent','Creation Time','ETD','ETA',
// 'Voyage','Vessel','Week','Created by') order by default_order");
$default_ids = common::excuteListSql("select id,display_name from public.ra_online_search_display_cso where model_name = '$type'
and default_display = true order by default_order");
$ids = utils::implode(";", $_POST['ids']);
$save_ids = common::excuteListSql("select id,display_name from public.ra_online_search_display_cso where model_name = '$type'
and id::text = any(regexp_split_to_array('$ids', ';')) order by default_order");
$detele_detail = "";
foreach($default_ids as $did){
if(!utils::exist_array($did['id'],$save_ids)){
$detele_detail .=$did['display_name']."/";
}
}
$add_detail = "";
foreach($save_ids as $sid){
if(!utils::exist_array($sid['id'],$default_ids)){
$add_detail .=$sid['display_name']."/";
}
}
if(!empty($detele_detail)){
$detail.="Detele fields: (".$detele_detail."). ";
}
if(!empty($add_detail)){
$detail.="Add fields: (".$add_detail."). ";
}
if(empty($detail)){
$detail = "The default field has not changed";
}
}
if(($action == "ocean_booking" || $action == "ocean_order") && $operate == "detail"){
$tabel = $action == "ocean_booking" ? "kln_booking" : "kln_ocean";
$serial_no = common::deCode($_GET['a'], 'D');
$sql = "SELECT booking_no,h_bol from public.$tabel where serial_no = '$serial_no' limit 1";
$data = common::excuteObjectSql($sql);
if(!empty($data['booking_no'])){
$detail = 'Booking No.: '.$data['booking_no'];
}else{
$detail = 'HBOL: '.$data['h_bol'];
}
}
if(($action == "ocean_booking" || $action == "ocean_order") && $operate == "save_communication"){
$text = $_POST["text"];
$detail = urldecode($text);
}
if(($action == "ocean_order") && $operate == "ams_isf_log"){
$detail = "Enter AMS/ISF Page";
}
if(($action == "ocean_booking" || $action == "ocean_order") && $operate == "excel"){
$detail = "Filter_condition:" . $_REQUEST['excel_filter_condition']." Selected Fields:". $_REQUEST['selected_fields'];
}
//Tracking詳情頁download的file（顯示file名稱）
if(($action == "ocean_order") && $operate == "download"){
$filename = common::deCode($_GET['url'], 'D');
$filename = str_replace("/", DIRECTORY_SEPARATOR, $filename);
$filename = str_replace("\\", DIRECTORY_SEPARATOR, $filename);
$display_name = basename($filename);
if (!file_exists($filename)){
$detail = "Tracking Detail Attachment Download But File Not Exist : $display_name";
}else{
$detail = "Tracking Detail Attachment Download: $display_name";
}
}
//Tracking詳情頁Upload Files（顯示file名稱）
if(($action == "ocean_order") && $operate == "document_upload"){
$detail = "Enter Upload Files page";
}
return $detail;
}
public static function calculateTicks($minValue, $maxValue, $targetTickCount = 10) {
$tickSpacing = ($maxValue - $minValue);
$tickSpacing = intval($tickSpacing);
$interval = ceil($tickSpacing / $targetTickCount);
$len = strlen($interval);
if ($len >1){
$interval = ceil($interval/pow(10,$len-1)) *pow(10,$len-1);
}
return $interval;
}
//只记录Public tracking
public static function single_operation_log_save($user_type,$user_name,$page,$operation,$operation_detail){
$sql = "INSERT INTO public.customer_service_operation_log(user_type, user_name, page, operation, operation_detail,
operation_time)
VALUES ('$user_type', '$user_name', '$page', '$operation', '$operation_detail', now())";
common::excuteUpdateSql($sql);
}
public static function uuid() {
return strtoupper(md5(uniqid("", TRUE) . mt_rand()));
}
public static function count($variable){
if (is_array($variable)) {
$count = count($variable);
} else {
$count = 0;
}
return $count;
}
public static function implode($sp,$variable){
$variable = isset($variable) && is_array($variable) ? $variable : array();
return implode($sp, $variable);
}
public static function in_array($str, $arr){
if (is_array($arr)) {
return in_array($str, $arr);
} else {
return false;
}
}
public static function exist_array($key,$arr){
$flag = false;
foreach($arr as $v){
if($v['id'] == $key ){
$flag = true;
}
}
return $flag;
}
public static function getConpanyForNotify($_schemas,$type){
//如果有多個就留空
$company = "";
if($type == 'ocean'){
$company = $_SESSION['ONLINE_USER']['company_name'];
$company_arr = explode(";",$company);
$temp = array();
foreach($company_arr as $v){
if(!empty($v)){
$temp[] = $v;
}
}
if(utils::count($temp) <= 1){
return $company;
}
}
if($type == 'air'){
$company_id = $_SESSION['ONLINE_USER']['air_customers'];
$company_id_arr = explode(";",$company_id);
$temp = array();
foreach($company_id_arr as $v){
if(!empty($v)){
$temp[] = $v;
}
}
if(utils::count($temp) == 1){
if ($_schemas == "public") {//apex ocean和air 分开
$sql = "SELECT company from ocean.contacts where contact_id = '" . common::check_input($temp[0]) . "' ";
} else {
$sql = "SELECT company from $_schemas.contacts where contact_id = '" . common::check_input($temp[0]) . "' ";
}
$company = common::excuteOneSql($sql);
}
return $company;
}
}
public static function getKlnDocNotifyContent($bol,$file_type,$upload_user_name,$upload_user_email,$company,$date_time){
$report_setting = common::excuteObjectSql("select * from ra_online_auto_report_config where report_type = 'KLN_DOC_Notify' ");
$report_content = $report_setting['report_content'];
$columns = common::excuteListSql("select display_name,model_name,database_column_name,excel_width,order_by from public.ra_online_search_display_cso where model_name = 'KLN_DOC_Notify'");
$missing_packing_th = "";
foreach ($columns as $colk => $colvalue) {
$missing_packing_th.='|
'.$colvalue['display_name'].'
| ';
}
$missing_packing_th .= "
";
$report_content = str_replace('<{missing_packing_th}>', $missing_packing_th, $report_content);
$data = array(array("h_bol"=>$bol,"file_type"=>$file_type,"upload_by"=>$upload_user_name,"email"=>$upload_user_email,"company"=>$company,"upload_time"=>$date_time."(US/Pacific)"));
$missing_packing_tr = "";
foreach ($data as $rk => $rv) {
$missing_packing_tr .= "";
foreach ($columns as $ck => $cv) {
if(utils::endWith($cv['display_name'], "email")){
$missing_packing_tr.='|
'.$rv[$cv['database_column_name']].'
| ';
}else{
$missing_packing_tr.=''.$rv[$cv['database_column_name']].' | ';
}
}
$missing_packing_tr .= "
";
}
$report_content = str_replace('<{missing_packing_tr}>', $missing_packing_tr, $report_content);
return $report_content;
}
public static function _getSql($ids, $type,$shipment_mode,$sqlWhere) {
$ids_arr = explode(',', $ids);
$sql = "";
if($type == "co2e_orgin"){
$str = "SUM(COALESCE(carbon_emission,0)) as catnum ";
$filed = "shippr_uncode";
} else {
$str = "SUM(COALESCE(carbon_emission,0)) as catnum ";
$filed = "consignee_uncode";
}
$shipment_mode_where = " 1=1 ";
//这里处理为空，目前没有数据
$shipment_mode_where = " transport_mode = '$shipment_mode' ";
$shipment_mode_where .= $sqlWhere;
foreach ($ids_arr as $value) {
if (!empty($value)) {
if (empty($sql)) {
$sql .= "SELECT $str FROM public.kln_ocean where $shipment_mode_where and $filed = '$value'";
} else {
$sql .= " union all SELECT $str from public.kln_ocean where $shipment_mode_where and $filed = '$value'";
}
}
}
return $sql;
}
public static function removeDuplicateArray($array){
$result = array();
foreach ($array as $value) {
//过滤可能的空值，会引起前端页面有数据但无法显示
if(!empty($value)){
if (!utils::in_array($value, $result)) {
$result[] = $value;
}
}
}
return $result;
}
public static function hasMacros($filePath) {
$extension = pathinfo($filePath, PATHINFO_EXTENSION);
if ($extension === 'xlsx') {
// 检查 .xlsx 文件
$zip = new ZipArchive();
if ($zip->open($filePath) === TRUE) {
// 检查是否存在 vbaProject.bin 文件
$hasMacros = $zip->locateName('xl/vbaProject.bin') !== false;
$zip->close();
return $hasMacros;
} else {
return false; // 无法打开文件
}
} elseif ($extension === 'docx') {
// 检查 .docx 文件
$zip = new ZipArchive();
if ($zip->open($filePath) === TRUE) {
// 检查是否存在 vbaProject.bin 文件
$hasMacros = $zip->locateName('word/vbaProject.bin') !== false;
$zip->close();
return $hasMacros;
} else {
return false; // 无法打开文件
}
} else {
return false; // 不是支持的文件类型
}
}
public static function arrayKeyToInt($arr){
$ret = array();
foreach($arr as $key => $val){
$ret[] = $val;
}
return $ret;
}
public static function getDailyAndweeklyFrist($arr){
$numericRecords = count($arr);
$ret = array();
$numericRecords_one = 0;
$numericRecords_two = 0;
foreach($arr as $key => $val){
if($key == 0){
$val["numericRecords"] = $numericRecords;
$ret = $val;
}
if($val["notifiation_type"] == "Departure/Arrival_Delay"){
if(utils::checkExist($val["delay_name"],"Departure_Delay")){
$numericRecords_one +=1;
}
if(utils::checkExist($val["delay_name"],"Arrival_Delay")){
$numericRecords_two +=1;
}
}
if($val["notifiation_type"] == "ETD/ETA_Change"){
if(utils::checkExist($val["date_change_name"],"ETD Change")){
$numericRecords_one +=1;
}
if(utils::checkExist($val["date_change_name"],"ETA Change")){
$numericRecords_two +=1;
}
}
}
//对Delay and change 特殊处理
$ret["numericRecords_one"]= $numericRecords_one;
$ret["numericRecords_two"]= $numericRecords_two;
return $ret;
}
/**
* ocean ->sea
*/
public static function converModeToDB($shipment_transport_mode_arr){
$converMode = array();
foreach($shipment_transport_mode_arr as $model){
if (strtolower($model) == "ocean"){
$converMode[] = 'sea';
}else{
$converMode[] = strtolower($model);
}
}
return utils::implode(";",$converMode);
}
/**
* sea ->ocean
*/
public static function converModeToDisplay($shipment_transport_mode){
//数据转换前端需要的
$converMode = array();
$shipment_transport_mode_arr = explode(";", $shipment_transport_mode);
foreach($shipment_transport_mode_arr as $model){
if (strtolower($model) == "sea"){
$converMode[] = 'Ocean';
}else{
$converMode[] = ucfirst($model);
}
}
return utils::implode(";",$converMode);
}
public static function compareArrayEq($array1,$array2){
$array1 = empty($array1) ? array():$array1;
$array2 = empty($array2) ? array():$array2;
// 使用array_diff比较并重置键名
$diff1 = array_diff($array1, $array2);
$diff2 = array_diff($array2, $array1);
// 如果结果为空，说明两个数组一样
if (empty($diff1) && empty($diff2)) {
return true;
} else {
return false;
}
}
public static function comvertutcinfo($time_zone){
if(!empty($time_zone)){
if(utils::checkExist($time_zone,"+")){
$time_zone = str_replace('+', '-', $time_zone);
}else{
$time_zone = str_replace('-', '+', $time_zone);
}
}
return $time_zone;
}
public static function arrayOrderBykeys($order,$data_kd,$key){
$orderedData = []; // 用于存储按$order排序后的数据
foreach ($order as $name) {
foreach ($data_kd as $item) {
if ($item[$key] == $name) {
$orderedData[] = $item; // 将找到的元素添加到新数组中
break; // 找到后跳出内层循环，继续外层循环处理下一个id
}
}
}
return $orderedData;
}
/**
* 数组去掉null
*/
public static function arrayRemoveNull($array){
foreach ($array as &$value) {
if (is_null($value)) {
$value = '';
}
}
unset($value); // 断开 $value 的引用
return $array;
}
/**
* public.kln_ocean 和 WHERE 的位置关系，进行条件拼接
*/
public static function modifyString($input,$sqlWhere) {
$search = 'public.kln_ocean';
$pos = strpos($input, $search);
if ($pos !== false) {
$afterSearch = substr($input, $pos + strlen($search));
$hasWhere = (stripos($afterSearch, 'WHERE') !== false);
if ($hasWhere) {
// 在 WHERE 后插入 1=1
$wherePos = stripos($input, 'WHERE', $pos);
$insertPos = $wherePos + strlen('WHERE');
$sqlWhere =' ' .$sqlWhere.' and ';
return substr_replace($input, $sqlWhere, $insertPos, 0);
} else {
// 在 public.kln_ocean 后插入 1=1
$insertPos = $pos + strlen($search);
$sqlWhere =' where ' .$sqlWhere.' ';
return substr_replace($input, $sqlWhere, $insertPos, 0);
}
}
return $input;
}
/**
* LIMIT的处理，或者超过100
* 如果有limit 给最后一个limit修改
* 这个处理不了 写在字段里的子查询（select 1 from aa limit 1） as aa，sql会原样返回
*/
public static function processLimitClause($sql,$limit = 100) {
$is_limit = false;
// 去除前后空白
$sql = trim($sql);
// 临时保存字符串内容，防止被误匹配
$placeholder = '__SQL_STRING_PLACEHOLDER__';
$strings = [];
// 匹配字符串（包括单引号和 $$ 符号）
$pattern = "/('(?:[^']|'')*')|(\$(?:.*?)\$)/is";
// 替换所有字符串为占位符，并保存原内容
$cleanSql = preg_replace_callback($pattern, function ($match) use (&$strings, $placeholder) {
$str = $match[1] ?? $match[2];
$index = count($strings);
$strings[$index] = $str;
return $placeholder . $index;
}, $sql);
// 正则匹配所有 LIMIT 子句（支持 LIMIT n 和 LIMIT n OFFSET m）
$pattern = '/\s+LIMIT\s+\d+(\s+OFFSET\s+\d+)?/i';
preg_match_all($pattern, $cleanSql, $matches, PREG_OFFSET_CAPTURE);
if (!empty($matches[0])) {
// 获取最后一个 LIMIT 的位置和内容
$lastMatch = end($matches[0]);
$limitStr = $lastMatch[0]; // 完整的 LIMIT 子句，如 " LIMIT 50" 或 " LIMIT 200 OFFSET 10"
$pos = $lastMatch[1]; // 起始位置
// 提取 LIMIT 的数字部分
preg_match('/\d+/', $limitStr, $numMatch);
$currentLimit = intval($numMatch[0]);
if ($currentLimit < $limit) {
// 当前 LIMIT 值更小，不做修改
$is_limit = true;
}else{
// 构造新的 LIMIT 子句，保留可能存在的 OFFSET
$offsetPart = '';
if (preg_match('/(.*?)(\s+OFFSET\s+\d+)/i', $limitStr, $offsetMatches)) {
$offsetPart = $offsetMatches[2]; // 例如 " OFFSET 10"
}
$newLimitClause = " LIMIT {$limit}{$offsetPart}";
// 替换最后一个 LIMIT
$before = substr($cleanSql, 0, $pos);
$after = substr($cleanSql, $pos + strlen($limitStr));
$cleanSql = $before . $newLimitClause . $after;
}
} else {
// 没有 LIMIT，直接加上
$cleanSql .= " LIMIT {$limit}";
}
// 最后恢复原来的字符串内容
$finalSql = preg_replace_callback("/{$placeholder}(\d+)/", function ($match) use ($strings) {
return $strings[(int)$match[1]];
}, $cleanSql);
return array("sql"=>$finalSql, "is_limit"=>$is_limit);
}
/**
* 替换 single reference
*/
public static function replacements($data, $template,$new_sql) {
// 动态构建替换数组（格式：[key] => value）
$replacements = [];
foreach ($data as $key => $value) {
$replacements["{{$key}}"] = $value;
$replacements["{{{$key}}}"] = $value;
}
if(empty($data)){
$fileds = common::extractSelectFields($new_sql);
foreach($fileds as $key){
//如果遇到data没数据，但是有 total_count ,则处理成0
if($key == "total_count"){
$replacements["{{$key}}"] = "0";
$replacements["{{{$key}}}"] = "0";
} else {
$replacements["{{$key}}"] = "";
$replacements["{{{$key}}}"] = "";
}
}
}
// 执行替换
error_log("replacements_single:".json_encode($replacements));
error_log("template_single:".$template);
$result = strtr($template, $replacements);
// 输出结果
return $result;
}
/**
* 替换复杂的reference
*
*/
public static function replacementsMultiline($data, $template,$new_sql) {
//行中所有 {{xxx}} 模板变量都必须在 SQL 查询字段中存在
//SQL 中可以有比 reference 多的字段，不影响匹配
//一旦找到第一个符合条件的
//不依赖任何 {{#EACH ROW}} 或其他模板语法
//| {{h_bol}} | {{m_bol}} | {{transport_mode}} | {{service}} | 格式
$explode_data = utils::findFirstTemplateRow($template,$new_sql);
$explode_str = $explode_data['line'];
$replaceTemplate = "";
//检查模板 是否已经带有特定表格的序列
if(!empty($explode_str) && strpos($template, $explode_str) !== false){
$spacing = utils::getMarkDownTableSpacing($template,$explode_str);
$parts = explode($explode_str, $template,2);
// 生成所有行
$generatedRows = [];
foreach ($data as $row) {
$replacements = [];
foreach ($row as $key => $value) {
$replacements["{{{$key}}}"] = $value;
}
$generatedRows[] = strtr($explode_str, $replacements);
}
if(empty($generatedRows) && empty($data)){
$generatedRows[] = "No Data";
}
//如果 SQL字段是有多余未匹配的字段 调用一次个体替换
$mapping = $explode_data['mapping'];
if(!$mapping){
$parts[0] = utils::replacements($data[0],$parts[0],$new_sql);
$parts[1] = utils::replacements($data[0],$parts[1],$new_sql);
}
$replaceTemplate = $parts[0] . implode($spacing, $generatedRows) . $parts[1];
}else{
//全文替换 上面统一有excuteListSql 这里的结果要变一下
$replaceTemplate = utils::replacements($data[0],$template,$new_sql);
}
return $replaceTemplate;
}
// 在 reference 中查找第一个符合要求的 | ... | 行
public static function findFirstTemplateRow($reference, $sql) {
$sqlFields = common::extractSelectFields($sql);
if (empty($sqlFields)) return null;
$lines = preg_split('/\r\n|\r|\n/', $reference);
foreach ($lines as $line) {
$line = trim($line);
// 检查是否是以 | 开头和结尾的表格行
if (strpos($line, '|') !== 0 || substr($line, -1) !== '|') continue;
// 提取该行中的所有 {{xxx}} 模板变量
preg_match_all('/\{\{(\w+)\}\}/', $line, $matches);
$templateVars = $matches[1];
preg_match_all('/(?<=\|)([^|]+)(?=\|)/', $line, $matchesTwo);
// 去除每个匹配项前后的空白字符
$cells = $matchesTwo[1];
if (empty($templateVars) || count($templateVars) <> count($cells)) continue;
// 检查每个变量是否都在 SQL 字段中：这里逻辑是无效的，这里是按模板取的,sql查询有多余字段不影响，也不影响单独去渲染
foreach ($templateVars as $var) {
if (!utils::in_array(strtolower($var), array_map('strtolower', $sqlFields))) {
continue; // 跳出当前循环，继续检查下一行
}
}
$mapping = true;
//检查 SQL 字段是否有未匹配的字段
if(count($sqlFields) <> count($templateVars)){
$mapping = false;
}
// 所有变量都匹配成功，返回这一行
return array("line"=>$line,"mapping" => $mapping);
}
return array("line"=>null,"mapping" =>true);; // 没有找到匹配行
}
/**
* 替换复杂的reference 固定问题 分开
*/
public static function replacementsFixedMultilineForFixed($data, $template,$explode_str) {
//| {{h_bol}} | {{m_bol}} | {{transport_mode}} | {{service}} | 格式
$replaceTemplate = "";
//检查模板 是否已经带有特定表格的序列
if(strpos($template, $explode_str) !== false){
$spacing = utils::getMarkDownTableSpacing($template,$explode_str);
$parts = explode("$explode_str", $template,2);
// 生成所有行
$generatedRows = [];
foreach ($data as $row) {
$replacements = [];
foreach ($row as $key => $value) {
$replacements["{{{$key}}}"] = $value;
}
$generatedRows[] = strtr($explode_str, $replacements);
}
$replaceTemplate = $parts[0] . implode($spacing, $generatedRows) . $parts[1];
}else{
//有异常,模板原样返回
$replaceTemplate = $template;
}
return $replaceTemplate;
}
/**
* 替换 single reference Fixed
*/
public static function replacementsFixed($data, $template,$fileds) {
// 动态构建替换数组（格式：[key] => value）
$replacements = [];
foreach ($data as $key => $value) {
$replacements["{{$key}}"] = $value;
$replacements["{{{$key}}}"] = $value;
}
if(empty($data)){
foreach($fileds as $key){
$replacements["{{$key}}"] = "";
$replacements["{{{$key}}}"] = "";
}
}
// 执行替换
$result = strtr($template, $replacements);
// 输出结果
return $result;
}
public static function getMarkDownTableSpacing($str,$search){
//$str = "|--------------|\n sdsds| E1205546127 | ";
//$search = "| E1205546127 |";
// 查找搜索字符串的位置
$pos = strpos($str, $search);
if ($pos !== false) {
// 从搜索字符串前面开始向前查找 "|" 的位置
for ($i = $pos - 1; $i >= 0; $i--) {
if ($str[$i] === '|') {
// 提取两个位置之间的内容
$result = substr($str, $i + 1, $pos - $i - 1);
return $result;
}
}
} else {
return "\n";
}
}
public static function uniqueGroupbyData($unique_filed,$groups_filed,$rows){
$uniqueData = [];
$hBolSeen = [];
//需要去重
if(!empty($unique_filed)){
foreach ($rows as $row) {
$hBol = $row[$unique_filed]."_".$row[$groups_filed];
if (!isset($hBolSeen[$hBol])) {
$uniqueData[] = $row;
$hBolSeen[$hBol] = true;
}
}
} else {
//不需要去重
$uniqueData = $rows;
}
//分组计数
$dateGroups = [];
if(!empty($groups_filed)){
foreach ($uniqueData as $item) {
$date = $item[$groups_filed];
if (!isset($dateGroups[$date])) {
$dateGroups[$date] = 0;
}
$dateGroups[$date]++;
}
}
return $dateGroups;
}
public static function getDmoeSqlForAi($type){
$data= array();
$data["Show shipments delayed in the last 30 days."] = "select count(*)
from public.kln_record kr
inner join LATERAL (select h_bol from public.kln_ocean oo where oo.serial_no = kr.serial_no and <{ExtendHand_KLN}> ) m on true
where kr.log_type like '%Delay'
and kr.log_time >= CURRENT_DATE - INTERVAL '30 day'
and kr.event_date is not null and kr.event_old_date is not null
and (kr.event_date||' '||COALESCE(kr.event_time,'00:00'))::timestamp >= (kr.event_old_date||' '||COALESCE(kr.event_old_time,'00:00'))::timestamp;select oo.h_bol,oo.place_of_receipt_exp,oo.place_of_delivery_exp,oo.serial_no,oo.order_from,
kr.log_type,kr.event_old_date, kr.event_old_time,kr.event_date, kr.event_time,
(EXTRACT(DAY FROM ((event_date||' '||COALESCE(event_time,'00:00'))::timestamp - (event_old_date||' '||COALESCE(event_old_time,'00:00'))::timestamp))) as duration
from public.kln_record kr
inner join LATERAL (select h_bol, place_of_receipt_exp, place_of_delivery_exp,serial_no,order_from
from public.kln_ocean oo where oo.serial_no = kr.serial_no and <{ExtendHand_KLN}> ) oo on true
where kr.log_type like '%Delay'
and kr.log_time >= CURRENT_DATE - INTERVAL '30 day'
and kr.event_date is not null and kr.event_old_date is not null
and (kr.event_date||' '||COALESCE(kr.event_time,'00:00'))::timestamp >= (kr.event_old_date||' '||COALESCE(kr.event_old_time,'00:00'))::timestamp
order by kr.log_time desc limit 10";
$data["Shipments arriving in the next 7 days."] = "select count(*)
from (
SELECT oo.serial_no,h_bol, place_of_receipt_exp, place_of_delivery_exp,m.description,eta,order_from, o.cargo_type
FROM public.kln_ocean oo
inner join LATERAL (select case when is_hazardous = 't' then 'Dangerous Goods'::text else 'General'::text end as cargo_type
from public.ocean o where o.serial_no = oo.serial_no) o on true
left join LATERAL (select a.code,a.description
from public.ocean_milestone a
inner join public.customer_service_milestone_sno s
on a.code = s.code
and s.type = 'sea'
and a.serial_no = oo.serial_no
and a.act_date is not null
order by s.sno desc limit 1) m on true
WHERE <{ExtendHand_KLN}> and oo.transport_mode = 'sea' and order_from = 'public' and m.code <> '' limit 10
) t;select serial_no,h_bol,place_of_receipt_exp,place_of_delivery_exp,description,eta,order_from,cargo_type
from (
SELECT oo.serial_no,h_bol, place_of_receipt_exp, place_of_delivery_exp,m.description,eta,order_from, o.cargo_type
FROM public.kln_ocean oo
inner join LATERAL (select case when is_hazardous = 't' then 'Dangerous Goods'::text else 'General'::text end as cargo_type
from public.ocean o where o.serial_no = oo.serial_no) o on true
left join LATERAL (select a.code,a.description
from public.ocean_milestone a
inner join public.customer_service_milestone_sno s
on a.code = s.code
and s.type = 'sea'
and a.serial_no = oo.serial_no
and a.act_date is not null
order by s.sno desc limit 1) m on true
WHERE <{ExtendHand_KLN}> and oo.transport_mode = 'sea' and order_from = 'public' and m.code <> '' limit 20
) t order by eta";
$data["List shipments with milestone updates in the last 7 days."] = "select count(*)
from (
select serial_no
from (SELECT oo.serial_no from public.ocean_milestone a
inner join public.customer_service_milestone_sno s on a.code = s.code
inner join public.kln_ocean oo on oo.serial_no = a.serial_no
where s.type = 'sea'
and a.act_date is not null
) po
)t;
select serial_no,order_from,h_bol, description,update_date_format,update_date,
COALESCE(m.jsonb_data->>'milestone','')::jsonb->>'timezone' as timezone,
COALESCE(m.jsonb_data->>'milestone','')::jsonb->>'locations' as locations
from (
select serial_no,order_from,h_bol,description,to_char(update_date,'Mon DD') as update_date_format,update_date,code
from (SELECT oo.serial_no,oo.order_from,oo.h_bol,s.description,COALESCE(a.update_date, a.create_date) as update_date,a.code
from public.ocean_milestone a
inner join public.customer_service_milestone_sno s on a.code = s.code
inner join public.kln_ocean oo on oo.serial_no = a.serial_no
where s.type = 'sea'
and a.act_date is not null
) po
)t left join LATERAL (select public.getTimeAndLocationForKln(serial_no,code,''::text)::jsonb as jsonb_data) m on true
order by update_date limit 10;
select aa.update_date_format, COUNT(*) AS total_count
from (
select DISTINCT ON (h_bol) h_bol, update_date_format
from (
select h_bol,to_char(update_date,'Mon DD') as update_date_format
from (SELECT oo.h_bol,COALESCE(a.update_date, a.create_date) as update_date
from public.ocean_milestone a
inner join public.kln_ocean oo on oo.serial_no = a.serial_no
where a.act_date is not null
) po
)t order by h_bol
) aa
group by aa.update_date_format order by aa.update_date_format ";
$data["What is the current status of my active shipments?"] ="SELECT count(*)
FROM public.kln_ocean oo
WHERE <{ExtendHand_KLN}> and ((oo.ata is not null and oo.ata >= CURRENT_DATE - INTERVAL '3 months' AND oo.ata < CURRENT_DATE)
or not exists( select 1 from public.ocean_milestone a where a.serial_no = oo.serial_no and a.act_date is not null and a.code = 'IFFDEL')) and oo.transport_mode = 'sea' and order_from = 'public';with oo as(
SELECT h_bol, place_of_receipt_exp, place_of_delivery_exp,serial_no,transport_mode,order_from
FROM public.kln_ocean oo
WHERE <{ExtendHand_KLN}> and ((oo.ata is not null and oo.ata >= CURRENT_DATE - INTERVAL '3 months' AND oo.ata < CURRENT_DATE)
or not exists( select 1 from public.ocean_milestone a where a.serial_no = oo.serial_no and a.act_date is not null and a.code = 'IFFDEL')) and oo.transport_mode = 'sea' and order_from = 'public' order by id limit 10
)
SELECT oo.*,mil.description,mil.act_date,mil.act_time,o.cargo_type,
COALESCE(jsonb_data->>'milestone','')::jsonb->>'timezone' as timezone,
COALESCE(jsonb_data->>'milestone','')::jsonb->>'locations' as locations
from oo
inner join LATERAL (select case when is_hazardous = 't' then 'Dangerous Goods'::text else 'General'::text end as cargo_type
from public.ocean o where o.serial_no = oo.serial_no) o on true
left join LATERAL (select a.code,s.description,to_char(a.act_date, 'YYYY-MM-DD') as act_date ,a.act_time
from public.ocean_milestone a
left join public.customer_service_milestone_sno s on a.code = s.code
and s.type = 'sea'
and a.serial_no = oo.serial_no
and a.act_date is not null
order by s.sno desc limit 1) mil on true
left join LATERAL (select public.getTimeAndLocationForKln(oo.serial_no,mil.code,''::text)::jsonb as jsonb_data) lt on true
where oo.transport_mode = 'sea' and order_from = 'public'
union all
SELECT oo.*,mil.description,mil.act_date,mil.act_time,o.cargo_type,
COALESCE(jsonb_data->>'milestone','')::jsonb->>'timezone' as timezone,
COALESCE(jsonb_data->>'milestone','')::jsonb->>'locations' as locations
from oo
inner join LATERAL (select case when is_hazardous = 't' then 'Dangerous Goods'::text else 'General'::text end as cargo_type
from sfs.ocean o where o.serial_no = oo.serial_no) o on true
left join LATERAL (select a.code,s.description,to_char(a.act_date, 'YYYY-MM-DD') as act_date ,a.act_time
from public.ocean_milestone a
left join public.customer_service_milestone_sno s on a.code = s.code
and s.type = 'air'
and a.serial_no = oo.serial_no
and a.act_date is not null
order by s.sno desc limit 1) mil on true
left join LATERAL (select public.getTimeAndLocationForKln(oo.serial_no,mil.code,''::text)::jsonb as jsonb_data) lt on true
where oo.transport_mode = 'sea' and order_from = 'sfs'
union all
SELECT oo.*,mil.description,mil.act_date,mil.act_time,o.cargo_type,
COALESCE(jsonb_data->>'milestone','')::jsonb->>'timezone' as timezone,
COALESCE(jsonb_data->>'milestone','')::jsonb->>'locations' as locations
from oo
inner join LATERAL (select case when is_hazardous = 't' then 'Dangerous Goods'::text else 'General'::text end as cargo_type
from public.ocean o where o.serial_no = oo.serial_no) o on true
left join LATERAL (select a.code,s.description,to_char(a.act_date, 'YYYY-MM-DD') as act_date ,a.act_time
from public.air_milestone a
left join public.customer_service_milestone_sno s on a.code = s.code
and s.type = 'air'
and a.serial_no = oo.serial_no
and a.act_date is not null
order by s.sno desc limit 1) mil on true
left join LATERAL (select public.getTimeAndLocationForKln(oo.serial_no,mil.code,''::text)::jsonb as jsonb_data) lt on true
where oo.transport_mode = 'air' and order_from = 'public'
union all
SELECT oo.*,mil.description,mil.act_date,mil.act_time,o.cargo_type,
COALESCE(jsonb_data->>'milestone','')::jsonb->>'timezone' as timezone,
COALESCE(jsonb_data->>'milestone','')::jsonb->>'locations' as locations
from oo
inner join LATERAL (select case when is_hazardous = 't' then 'Dangerous Goods'::text else 'General'::text end as cargo_type
from sfs.ocean o where o.serial_no = oo.serial_no) o on true
left join LATERAL (select a.code,s.description,to_char(a.act_date, 'YYYY-MM-DD') as act_date ,a.act_time
from sfs.air_milestone a
left join public.customer_service_milestone_sno s on a.code = s.code
and s.type = 'air'
and a.serial_no = oo.serial_no
and a.act_date is not null
order by s.sno desc limit 1) mil on true
left join LATERAL (select public.getTimeAndLocationForKln(oo.serial_no,mil.code,''::text)::jsonb as jsonb_data) lt on true
where oo.transport_mode = 'air' and order_from = 'sfs'";
$data["List shipments with container status updates in the last 7 days."] = "select count(*)
FROM ra_online_container_status s
LEFT JOIN oc_container oc ON s.status_id = oc.status_id
LEFT JOIN ocean o ON o.serial_no::text = oc.serial_no::text
LEFT JOIN public.ra_online_edi_event e on s.event_base = e.ra_name
WHERE o.status::text <> 'Cancelled'::text
and is_display = true
and s.insert_date <= CURRENT_DATE AND s.insert_date >='2023-02-23'
and exists(select 1 from kln_ocean oo where <{ExtendHand_KLN}> and oo.serial_no = o.serial_no);select oo.serial_no,oo.order_from,s.event_base as event,s.container_no,
to_char(to_timestamp(s.event_date, 'YYYYMMDD'), 'YYYY-MM-DD') as eventdate,
to_char(to_timestamp(s.event_date, 'YYYYMMDD'),'Mon DD') as _eventdate,
to_char(to_timestamp(s.event_time, 'HH24MI'), 'HH24:MI') as eventtime,
(select time_zone from public.city_timezone where uncode = s.event_code) as timezone,
e.description,
s.event_city as uncity
FROM ra_online_container_status s
LEFT JOIN oc_container oc ON s.status_id = oc.status_id
LEFT JOIN ocean o ON o.serial_no::text = oc.serial_no::text
LEFT JOIN public.ra_online_edi_event e on s.event_base = e.ra_name
LEFT JOIN public.kln_ocean oo ON oo.serial_no::text = o.serial_no::text
WHERE o.status::text <> 'Cancelled'::text
and is_display = true
and s.insert_date <= CURRENT_DATE AND s.insert_date >='2023-02-23'
and exists(select 1 from kln_ocean oo where <{ExtendHand_KLN}> and oo.serial_no = o.serial_no) limit 10;select aa._eventdate, COUNT(*) AS total_count
from (select DISTINCT ON (s.container_no) s.container_no,
to_char(to_timestamp(s.event_date, 'YYYYMMDD'),'Mon DD') as _eventdate
FROM ra_online_container_status s
LEFT JOIN oc_container oc ON s.status_id = oc.status_id
LEFT JOIN ocean o ON o.serial_no::text = oc.serial_no::text
LEFT JOIN public.ra_online_edi_event e on s.event_base = e.ra_name
WHERE o.status::text <> 'Cancelled'::text
and is_display = true
and s.insert_date <= CURRENT_DATE AND s.insert_date >='2023-02-23'
and exists(select 1 from kln_ocean oo where <{ExtendHand_KLN}> and oo.serial_no = o.serial_no)
order by s.container_no
)aa group by _eventdate order by _eventdate";
$data["Today's shipments summary."] = "select count(*)
from (
select oo.serial_no
from (
select t.serial_no from (
SELECT
a.serial_no,
ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
from public.ocean_milestone a
left join public.customer_service_milestone_sno s on a.code = s.code
where s.type = 'sea'
and a.act_date is not null
--and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
)t WHERE rn = 1
) po inner join public.kln_ocean oo on oo.serial_no = po.serial_no and (<{ExtendHand_KLN}>)
union all
select oo.serial_no
from (
select t.serial_no from (
SELECT
a.code,a.serial_no,
ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
from public.air_milestone a
left join public.customer_service_milestone_sno s on a.code = s.code
where s.type = 'sea'
and a.act_date is not null
--and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
)t WHERE rn = 1
) pa inner join public.kln_ocean oo on oo.serial_no = pa.serial_no and (<{ExtendHand_KLN}>)
union all
select oo.serial_no
from (
select t.serial_no from (
SELECT
a.serial_no,
ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
from sfs.air_milestone a
left join public.customer_service_milestone_sno s on a.code = s.code
where s.type = 'sea'
and a.act_date is not null
--and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
)t WHERE rn = 1
) sa inner join public.kln_ocean oo on oo.serial_no = sa.serial_no and (<{ExtendHand_KLN}>)
)t;select *
from (
select *,oo.serial_no,oo.order_from,oo.h_bol,oo.transport_mode,oo.place_of_receipt_exp, oo.place_of_delivery_exp,
COALESCE(jsonb_data->>'milestone','')::jsonb->>'timezone' as timezone,
COALESCE(jsonb_data->>'milestone','')::jsonb->>'locations' as locations
from (
select *,public.getTimeAndLocationForKln(t.serial_no,t.code,''::text)::jsonb as jsonb_data
from (
SELECT
case when a.code = 'IFFDEP' then 'Departure'
when a.code = 'IFFARR' then 'Arrived'
when a.code = 'IFFDEL' then 'Delivered'
else s.description end as action_type,
a.update_date,a.code,a.serial_no,
to_char(a.update_date, 'Mon_DD_YYYY') as _update_date,
to_char(a.act_date, 'YYYY-MM-DD') as act_date ,
a.act_time,
ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
from public.ocean_milestone a
left join public.customer_service_milestone_sno s on a.code = s.code
where s.type = 'sea'
and a.act_date is not null
--and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
)t WHERE rn = 1
) po inner join public.kln_ocean oo on oo.serial_no = po.serial_no
union all
select *,oo.serial_no,oo.order_from,oo.h_bol,oo.transport_mode,oo.place_of_receipt_exp, oo.place_of_delivery_exp,
COALESCE(jsonb_data->>'milestone','')::jsonb->>'timezone' as timezone,
COALESCE(jsonb_data->>'milestone','')::jsonb->>'locations' as locations
from (
select *,public.getTimeAndLocationForKln(t.serial_no,t.code,''::text)::jsonb as jsonb_data
from (
SELECT
case when a.code = 'IFFDEP' then 'Departure'
when a.code = 'IFFARR' then 'Arrived'
when a.code = 'IFFDEL' then 'Delivered'
else s.description end as action_type,
a.update_date,a.code,a.serial_no,
to_char(a.update_date, 'Mon_DD_YYYY') as _update_date,
to_char(a.act_date, 'YYYY-MM-DD') as act_date ,
a.act_time,
ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
from public.air_milestone a
left join public.customer_service_milestone_sno s on a.code = s.code
where s.type = 'sea'
and a.act_date is not null
--and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
)t WHERE rn = 1
) pa inner join public.kln_ocean oo on oo.serial_no = pa.serial_no
union all
select *,oo.serial_no,oo.order_from,oo.h_bol,oo.transport_mode,oo.place_of_receipt_exp, oo.place_of_delivery_exp,
COALESCE(jsonb_data->>'milestone','')::jsonb->>'timezone' as timezone,
COALESCE(jsonb_data->>'milestone','')::jsonb->>'locations' as locations
from (
select *,public.getTimeAndLocationForKln(t.serial_no,t.code,''::text)::jsonb as jsonb_data
from (
SELECT
case when a.code = 'IFFDEP' then 'Departure'
when a.code = 'IFFARR' then 'Arrived'
when a.code = 'IFFDEL' then 'Delivered'
else s.description end as action_type,
a.update_date,a.code,a.serial_no,
to_char(a.update_date, 'Mon_DD_YYYY') as _update_date,
to_char(a.act_date, 'YYYY-MM-DD') as act_date ,
a.act_time,
ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
from sfs.air_milestone a
left join public.customer_service_milestone_sno s on a.code = s.code
where s.type = 'sea'
and a.act_date is not null
--and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
)t WHERE rn = 1
) sa inner join public.kln_ocean oo on oo.serial_no = sa.serial_no
)t limit 10;select sum(case when (action_type='Departure') then 1 else 0 end) as dep,
sum(case when (action_type='Arrived') then 1 else 0 end) as arr,
sum(case when (action_type='Delivered') then 1 else 0 end) as del
from (
select action_type
from (
select t.action_type,t.serial_no from (
SELECT
case when a.code = 'IFFDEP' then 'Departure'
when a.code = 'IFFARR' then 'Arrived'
when a.code = 'IFFDEL' then 'Delivered'
else s.description end as action_type,
a.serial_no,
ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
from public.ocean_milestone a
left join public.customer_service_milestone_sno s on a.code = s.code
where s.type = 'sea'
and a.act_date is not null
--and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
)t WHERE rn = 1
) po inner join public.kln_ocean oo on oo.serial_no = po.serial_no and (<{ExtendHand_KLN}>)
union all
select action_type
from (
select t.action_type,t.serial_no from (
SELECT
case when a.code = 'IFFDEP' then 'Departure'
when a.code = 'IFFARR' then 'Arrived'
when a.code = 'IFFDEL' then 'Delivered'
else s.description end as action_type,
a.serial_no,
ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
from public.air_milestone a
left join public.customer_service_milestone_sno s on a.code = s.code
where s.type = 'sea'
and a.act_date is not null
--and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
)t WHERE rn = 1
) pa inner join public.kln_ocean oo on oo.serial_no = pa.serial_no and (<{ExtendHand_KLN}>)
union all
select action_type
from (
select t.action_type,t.serial_no from (
SELECT
case when a.code = 'IFFDEP' then 'Departure'
when a.code = 'IFFARR' then 'Arrived'
when a.code = 'IFFDEL' then 'Delivered'
else s.description end as action_type,
a.serial_no,
ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
from sfs.air_milestone a
left join public.customer_service_milestone_sno s on a.code = s.code
where s.type = 'sea'
and a.act_date is not null
--and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
)t WHERE rn = 1
) sa inner join public.kln_ocean oo on oo.serial_no = sa.serial_no and (<{ExtendHand_KLN}>)
)t";
$data["Sort my active shipments by earliest arrival date."] = "select to_char(oo.eta,'DD-Mon') as eta, oo.h_bol,oo.transport_mode, oo.place_of_receipt_exp, oo.place_of_delivery_exp,oo.serial_no,oo.order_from,
case when oo.eta - CURRENT_DATE <= 0 then '< 1 days'::text
else (oo.eta - CURRENT_DATE)||' days'::text end as day_to_arr
from public.kln_ocean oo where 1=1 order by eta limit 10";
return $data[$type];
}
//AES 加密
public static function AES_128_CBC_Encrypt($data,$key = "fT5!R1k$7Mv@4Q9X",$iv = '1234567890123456'){
//$key = 'fT5!R1k$7Mv@4Q9X'; // 密钥应该是16字节（128位），24字节（192位）或32字节（256位）
$method = 'AES-128-CBC';
//$iv = '1234567890123456';
// 加密
$encrypted = openssl_encrypt($data, $method, $key, OPENSSL_RAW_DATA, $iv);
// 编码为可打印的字符串，如Base64
$encrypted = base64_encode($encrypted);
return $encrypted;
}
/**
* 解密decrypt
*/
public static function AES_encrypted($encrypted_string,$isbase64_encode = true,$key = "fT5!R1k$7Mv@4Q9X",$iv = '1234567890123456'){
//$key = 'fT5!R1k$7Mv@4Q9X'; // 16 bytes key
//$iv = '1234567890123456'; // 16 bytes IV
if($isbase64_encode){
$decrypted = openssl_decrypt(base64_decode($encrypted_string), 'AES-128-CBC', $key, OPENSSL_RAW_DATA, $iv);
}else{
$decrypted = openssl_decrypt($encrypted_string, 'AES-128-CBC', $key, OPENSSL_RAW_DATA, $iv);
}
return $decrypted;
}
}
?>