k_online_backend/utils/common.class.php

<?php

if (!defined('IN_ONLINE')) {
    exit('Access Denied');
}

/**
 * Description of common
 *
 * @author Administrator
 */
class common {

    private static $data = [];
    
    public static function set($key, $value)
    {
        self::$data[$key] = $value;
    }
    
    public static function get($key)
    {
        $data = self::$data[$key];
        return !empty($data)? $data:"";
    }

    /*
     * mysql check input
     */
    public static function check_input($value, $boolean = FALSE, $toupper = FALSE) {
        if ($boolean !== FALSE) {
            if ($value == "t")
                return "'t'";
            else
                return "'f'";
        }
        $value = trim($value);
        if ($toupper !== FALSE)
            $value = mb_strtoupper($value);
        if (!is_numeric($value)) {
            $value = pg_escape_string($value);
        }
        return $value;
    }
    
    public static function excuteOneSql($sql, $exception = FALSE) {
        if ($exception) {
            global $db;
            $result = $db->GetOne($sql);
            return $result === FALSE ? null : $result;
        } else {
            if (empty($sql))
                exit(json_encode("Program encountered an error."));
            global $db;
            $result = $db->GetOne($sql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $sql), 0));
            return $result === FALSE ? null : $result;
        }
    }

    public static function excuteObjectSql($sql) {
        if (empty($sql))
            exit(json_encode("Program encountered an error."));
        global $db;
        $result = $db->GetRow($sql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $sql), 0));
        return $result === FALSE ? null : $result;
    }

    public static function excuteUpdateSql($sql, $req_id = FALSE) {
        if (empty($sql))
            exit(json_encode("Program encountered an error."));
        global $db;
        $rs = $db->Execute($sql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $sql), 0));
        if ($rs && $req_id !== FALSE)
            return $db->PO_Insert_ID();
        else
            return $rs;
    }
    
    public static function excuteListSql($sql, $exception = FALSE) {
        if ($exception) {
            global $db;
            $result = $db->GetAll($sql);
            return $result === FALSE ? null : $result;
        } else {
            if (empty($sql))
                exit(json_encode("Program encountered an error."));
            global $db;
            $result = $db->GetAll($sql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $sql), 0));
            return $result === FALSE ? null : $result;
        }
    }

    /*
     * Database log output
    */
    public static function dbLog($db, $sql) {
        $backMsg = $db->errorMsg() . ' sql=' . $sql;
        return $backMsg;
    }

    /*
     * get IP
    */
    public static function ip() {
        if (getenv("HTTP_X_FORWARDED_FOR"))
            return getenv("HTTP_X_FORWARDED_FOR");
        if (getenv("HTTP_CLIENT_IP"))
            return getenv("HTTP_CLIENT_IP");
        if (getenv("REMOTE_ADDR"))
            return getenv("REMOTE_ADDR");
        if ($HTTP_SERVER_VARS["HTTP_X_FORWARDED_FOR"])
            return $HTTP_SERVER_VARS["HTTP_X_FORWARDED_FOR"];
        if ($HTTP_SERVER_VARS["HTTP_CLIENT_IP"])
            return $HTTP_SERVER_VARS["HTTP_CLIENT_IP"];
        if ($HTTP_SERVER_VARS["REMOTE_ADDR"])
            return $HTTP_SERVER_VARS["REMOTE_ADDR"];
        return "Unknown";
    }

    public static function getDBUuid() {
        global $db;
        $sql = "select uuid_generate_v1()";
        $uuid = $db->GetOne($sql);
        return $uuid;
    }

    public static function securityCheckHandNew($action) {
        if (strpos($action, "handset") === 0) {
            
        } elseif (strpos($action, "hand") === 0) {
            
        } else {
            common::sessionVerify();
            $httpAccept = $_SERVER['HTTP_ACCEPT']; // ajax request,is json or html
            $ajax = isset($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER
                            ['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest'; // is ajax request?
            //暂时这么写，放开action = main的查询是为了在没有登录时获取信息。
            //tools是为了避免查询的用户，没有配置tools权限，但是新版mark_save需要进入
            if($action == "main" || $action == "tools" || $action == "save_layout"){
                return;
            }
            //tools是为了避免查询的用户，没有配置tools权限，但是新版mark_save需要进入
            if($action == "robot_chat" ){
                $operate = strtolower(utils::_get('operate'));
                if ($operate == "ai_chat_fixed_init"|| $operate == "ai_chat_prompt"){
                    return;
                }
            }

            //tools是为了避免查询的用户，没有配置tools权限，但是新版mark_save需要进入
            if($action == "multilingual" ){
                $operate = strtolower(utils::_get('operate'));
                if ($operate == "load_all_pages_by_lang"){
                    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 开头的都匹配上了
                            && !(stripos($action, "shipment_status_report") === 0)
                            && !(stripos($action, "report_config") === 0)
                            && !(stripos($action, "kam_customer_auto_login") === 0)
                        ) {
                            $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 = '<input type="hidden" name="_apex_or_sfs" value=""/>';
        } else {
            $msg = '<div class="modules">Apex/SFS/Kerry :&nbsp;<select name="_apex_or_sfs" class="sl"><option value="">All</option>';
            if ($_SESSION['ONLINE_USER']['main_schemas'] == "public") {
                $msg .= '<option value="public">Apex</option>';
            } else {
                $msg .= '<option value="public">Apex</option>';
            }
            if ($_SESSION['ONLINE_USER']['main_schemas'] == "sfs") {
                $msg .= '<option value="sfs">SFS</option>';
            } else {
                $msg .= '<option value="sfs">SFS</option>';
            }
            if ($_SESSION['ONLINE_USER']['main_schemas'] == "kyiff") {
                $msg .= '<option value="kyiff">Kerry</option>';
            } else {
                $msg .= '<option value="kyiff">Kerry</option>';
            }
            $msg .= '</select></div>';
        }
        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($type,$schames);
            }
            
            if (strtolower($type) == "air") {
                $sqlWhere .= self::_air($type,$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($type,"public");
                $sqlWhere .= ") or (order_from='sfs' ";
                if (empty($user["sfs_ONLINE_USER"])) {
                    $user = $_SESSION["sfs_ONLINE_USER"];
                }
                $sqlWhere .= self::_air($type,"sfs");
                $sqlWhere .= "))";
            }
            
            if (strtolower($type) == "air") {
                $sqlWhere .= " and ((order_from='public' ";
                $sqlWhere .= self::_air($type, "public");
                $sqlWhere .= ") or (order_from='sfs' ";

                if (empty($user["sfs_ONLINE_USER"])) {
                    $user = $_SESSION["sfs_ONLINE_USER"];
                }
                $sqlWhere .= self::_air($type, "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) . "'";
            }
        }
    }

    /**
     * 不转小写
    */
    public static function getInNotInSqlForSearchWithoutLower($contact_id, $type = 'in') {
        if (empty($contact_id))
            return "'__NONE__'";
        $contact_id = trim($contact_id);
        $contact_id = trim($contact_id, ";");
        $contact_id = trim($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'];

        $tableName = common::get("tableName");
        if(empty($tableName)){
            $tableName = "kln_ocean";
        }
        
        $sqlWhere = "";
        if (_isCustomerLoginHandNew($user)) {
            $sqlWhere .= " and " . _customerFilerSearchHandNew($tableName,$user, $schemas);
        } else {
            //employee_search_type 的设置
            $sqlStr = _employeeFilerSearch($tableName,'ocean','employee_contact_id_user','employee_company_name',$schemas);

            if (empty($o) && empty($d) && empty($sales) && empty($op) && empty($follow)&& $sqlStr == "1<>1") {
                return " and 1<>1";
            }
            if (strtolower($o_or) == "all" || strtolower($d_or) == "all" || strtolower($sales_or) == "all" || strtolower($op_or) == "all") {
                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') {
                    if ($tableName == 'online_container'){
                        $sql .= " and lower(agent)";
                        $sql .= utils::getInSql($d);
                    } else {
                        $inClause = common::getInNotInSqlForSearchWithoutLower($d); 
                        $arrayLiteral = 'ARRAY[' . $inClause . ']::text[]';
                        $sql .= " and edi_agent && $arrayLiteral";
                    }
                }
                $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)) {
                if ($tableName == 'online_container'){
                    $sqlWhere .= " or lower(agent)";
                    $sqlWhere .= utils::getInSql($d_or);
                } else {
                    $inClause = common::getInNotInSqlForSearchWithoutLower($d_or); 
                    $arrayLiteral = 'ARRAY[' . $inClause . ']::text[]';
                    $sqlWhere .= " or edi_agent && $arrayLiteral";

                    // $inClause = utils::getInSql($d_or); 
                    // $sqlWhere .= " OR (";
                    // $sqlWhere .= " LOWER(agent) " . $inClause;
                    // $sqlWhere .= " OR EXISTS(SELECT 1 FROM ".$schemas.".ocean  where ocean.serial_no = ".$tableName.".serial_no AND LOWER(ocean.to_other_stations) " . $inClause.")" ;
                    // $sqlWhere .= " OR EXISTS (";
                    // $sqlWhere .= "     SELECT 1 FROM ".$schemas.".ocean_agent ";
                    // $sqlWhere .= "     WHERE ocean_agent.serial_no = ".$tableName.".serial_no ";
                    // $sqlWhere .= "       AND LOWER(ocean_agent.edi_agent) " . $inClause;
                    // $sqlWhere .= " )";
                    // $sqlWhere .= " )";
                }
            }
            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 . "'";
                }
            }
            if( $sqlStr != "1<>1"){
                $sqlWhere .= " or " .$sqlStr;
            }

            $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'];
        
                $tableName = "kln_booking";
                $sqlWhere = "";
                if (_isCustomerLoginHandNew($user)) {
                    $sqlWhere .= " and " . _customerFilerSearchHandNew($tableName,$user, $schames);
                } else {
                    //employee_search_type 的设置
                    $sqlStr = _employeeFilerSearch($tableName,'ocean','employee_contact_id_user','employee_company_name',$schames);
                    
                    if (empty($o) && empty($d) && empty($sales) && empty($op) && empty($follow)&& $sqlStr == "1<>1") {
                        return " and 1<>1";
                    }
                    if (strtolower($o_or) == "all" || strtolower($d_or) == "all" || strtolower($sales_or) == "all") {
                        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);
                            $inClause = common::getInNotInSqlForSearchWithoutLower($d); 
                            $arrayLiteral = 'ARRAY[' . $inClause . ']::text[]';
                            $sql .= " and edi_agent && $arrayLiteral";
                        }
                        $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);
                        
                        $inClause = utils::getInSql($d_or); 
                        $sqlWhere .= " OR (";
                        $sqlWhere .= " LOWER(agent) " . $inClause;
                        //$sqlWhere .= " OR LOWER(to_other_stations) " . $inClause;
                        $sqlWhere .= " OR EXISTS(SELECT 1 FROM ".$schames.".ocean  where ocean.serial_no = ".$tableName.".serial_no AND LOWER(ocean.to_other_stations) " . $inClause.")" ;
                        $sqlWhere .= " OR EXISTS (";
                        $sqlWhere .= "     SELECT 1 FROM ".$schames.".ocean_agent ";
                        $sqlWhere .= "     WHERE ocean_agent.serial_no = ".$tableName.".serial_no ";
                        $sqlWhere .= "       AND LOWER(ocean_agent.edi_agent) " . $inClause;
                        $sqlWhere .= " )";
                        $sqlWhere .= " )";
                    }
                    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 . "%'";
                    }
                    if( $sqlStr != "1<>1"){
                        $sqlWhere .= " or " .$sqlStr;
                    }
        
                    $sqlWhere = " and ($sqlWhere)";
                }
                return $sqlWhere;
    }

    /**
     * online air
     */
    private static function _air($type,$schemas = "public") {
        $station = _getAirStation($schemas);
        $station_or = _getAirStationOr($schemas);
        $sales = _getAirSales($schemas);
        $sales_or = _getAirSalesOr($schemas);

        if (strtolower($type) == "air_booking") {
            $tableName = "kln_booking";
        } else {
            $tableName = "kln_ocean";
        }
        
        $sqlWhere = " 1=1 ";
        if (_isCustomerLogin()) {
            $sqlWhere .= " and " . _customerAirFilerSearch($tableName,$schemas);
        } else {
            //employee_search_type 的设置
            $sqlStr = _employeeFilerSearch($tableName,'air','employee_air_contact_id_user','employee_air_company_name',$schemas);

            if (empty($station) && empty($sales) && $sqlStr == "1<>1") {
                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);
            if( $sqlStr != "1<>1"){
               $sqlWhere = $sqlWhere. " or " .$sqlStr;
            }
        }
        return " and ($sqlWhere)";
    }

    public static function isNewVersion() {
        if (utils::checkExist($_SERVER['PHP_SELF'], "main_new_version.php")) {
            return "_new";
        }
        return "";
    }

    public static function removeTopOceanOldVersionSpecialField($rss){
        $rss_bk = $rss;
        $new_arr = array();
        foreach ($rss_bk as $k => $v) {
            if (utils::startWith($v['database_column_name'], "__") || utils::startWith($v['database_column_name'], "___")){
                //unset($rss[$k]);
            }else{
                $new_arr[] = $v;
            }
        }
        return $new_arr;
    }

    public static function echo_json_encode($code,$data){
        if(_isDesensitizationKln()){
            common::maskShipperName($data);
        }
        if((_isCustomerLogin())){
            common::maskOceanMbol($data);
        }
        $resData = array();
        $resData["code"] = $code;
        $resData["data"] =$data;
        //echo utils::jsonFiltration("null", "\"\"", json_encode($resData));
        echo json_encode($resData);
    }

    public static function maskOceanMbol(&$data) {
        if (is_array($data)) {
            foreach ($data as $key => &$value) {
                if ($key === 'MBOL/MAWB No.') {
                    if(is_array($value)){
                        $temp = array();
                        foreach($value as $vk =>$vv){
                            if ($value['transport_mode_extend'] == 'sea'){
                                $temp[$vk] = '**********';
                            }
                        }
                        $value = $temp;
                    } else {
                        if ($data['transport_mode_extend'] == 'sea'){
                            $value = '**********'; // 替换为脱敏值
                        }
                    }
                } else {
                    // 递归处理子数组
                    common::maskOceanMbol($value);
                }
            }
            unset($value); // 解除引用
        }
        // 如果是标量（string, int 等），不做处理
    }

    public static function maskShipperName(&$data) {
        if (is_array($data)) {
            foreach ($data as $key => &$value) {
                if ($key === 'shipperName' || $key === 'consigneeName' 
                    || $key === 'Shipper'  || $key === 'Consignee'
                    || $key === 'shipper'  || $key === 'consignee'
                    || $key === 'Notify Party'  || $key === 'Bill To') {
                    if(is_array($value)){
                        $temp = array();
                        foreach($value as $vk =>$vv){
                            $temp[$vk] = '**********';
                        }
                        $value = $temp;
                    } else {
                        $value = '**********'; // 替换为脱敏值
                    }
                } else {
                    // 递归处理子数组
                    common::maskShipperName($value);
                }
            }
            unset($value); // 解除引用
        }
        // 如果是标量（string, int 等），不做处理
    }

    /*
     * MM/DD/YYYY To YYYY-MM-DD
     */

     public static function usDate2sqlDate($timestr) {
        if (empty($timestr))
            return '';
        $datearray = explode("/", $timestr);

        $m = $datearray [0];
        $d = $datearray [1];
        $y = $datearray [2];

        return $y . "-" . $m . "-" . $d;
    }

    public static function uuid() {
        return md5(uniqid("", TRUE) . mt_rand());
    }

    /*
    * YYYYMMDD To MM/DD/YYYY
    */
    public static function date2usdate($datestr) {
        if (empty($datestr))
            return '';

        $y = substr($datestr, 0, 4);
        $m = substr($datestr, 4, 2);
        $d = substr($datestr, 6, 2);

        return $m . "/" . $d . "/" . $y;
    }

    /*
     * date add some days
    */
    public static function addDays($date, $days) {
        $time = strtotime($date) + $days * 24 * 3600;
        return date('m/d/Y', $time);
    }

    /*
     * download file from file system
     */

     public static function download_file($filename, $display_name = null, $delete = FALSE, $files = NULL) {
        $filename = str_replace("/", DIRECTORY_SEPARATOR, $filename);
        $filename = str_replace("\\", DIRECTORY_SEPARATOR, $filename);
        if (!file_exists($filename)){
            exit("File Not Exist");
        }
        if (empty($display_name))
            $display_name = basename($filename);
        //$file = fopen($filename, "r");
        header_remove("Content-type");
        header("Content-type:" . self::getContentType($filename));
        header("Expires: 0");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: can-cache");
        header("Accept-ranges:bytes");
        header("Accept-length:" . filesize($filename));
        header('Access-Control-Expose-Headers: Content-Disposition');
        header("Content-Disposition:attachment;filename=\"" . $display_name . "\"");
        //echo fread($file, filesize($filename));
        //fclose($file);
        //针对大文件，规定每次读取文件的字节数为4096字节，直接输出数据
        $read_buffer = 4096;
        $handle = fopen($filename, 'rb');
        //总的缓冲的字节数
        $sum_buffer = 0;
        $filesize = filesize($filename);

        //只要没到文件尾，就一直读取
        while (!feof($handle) && $sum_buffer < $filesize) { 
            echo fread($handle, $read_buffer);
            $sum_buffer += $read_buffer;
        }
        //关闭句柄
        fclose($handle);
        if ($delete !== FALSE) {
            unlink($filename);
        }
        if (!empty($files)) {
            foreach ($files as $f) {
                unlink($f);
            }
        }
    }

    public static function getContentType($filename) {
        $extend = self::getExtendFilename($filename);
        $filetype = array(
            'xls' => 'application/vnd.ms-excel',
            'doc' => 'application/msword',
            'gif' => 'image/gif',
            'jpg' => 'image/jpeg',
            'jpeg' => 'image/jpeg',
            'jpe' => 'image/jpeg',
            'bmp' => 'image/bmp',
            'png' => 'image/png',
            'tif' => 'image/tiff',
            'pdf' => 'application/pdf',
            'zip' => 'application/zip'
        );
        return $filetype[$extend];
    }

    /**
     * 生成嵌套 ZIP 文件（按 format_name 分组）
     *
     * @param array $files 文件列表
     */
    public static  function downloadFilesAsZip($files)
    {
        // 清除缓冲区，防止输出干扰 ZIP 文件
        if (ob_get_level()) {
            ob_end_clean();
        }

        $f = ONLINE_ROOT . DS . "upload" . DS . "Attachment_Summary_" . date('Y-m-dH\hi\ms\s', time()) . ".zip";
        $zip = new ZipArchive();

        if ($zip->open($f, ZipArchive::OVERWRITE) === TRUE ||
            $zip->open($f, ZipArchive::CREATE) === TRUE) {
            
        } else {
            die("create ZIP faild。");
        }

        // 按 format_name 分组
        $grouped = [];
        foreach ($files as $file) {
            $format = $file['format_name'] ?: 'Other';
            if (!isset($grouped[$format])) {
                $grouped[$format] = [];
            }
            $grouped[$format][] = $file;
        }

        // 遍历每个分组（类型），创建子目录
        foreach ($grouped as $formatName => $fileList) {
            // 生成子文件夹名：FormatName_YYYYMMDD_HHMMSS
            $subFolderName = $formatName . '_' . date('Ymd') . '_' . date('His');
            $subFolderName = common::sanitizeFilename($subFolderName);

            // 添加子文件夹（空目录）
            $zip->addEmptyDir($subFolderName);

            // 添加每个文件到对应子文件夹
            $h_bol_num = array();
            foreach ($fileList as $file) {
                if(array_key_exists($file['h_bol'], $h_bol_num)){
                    $h_bol_num[$file['h_bol']] += 1; 
                }else{
                    $h_bol_num[$file['h_bol']] = 1;
                }

                $realPath = $file['file_path'];
                $extend = self::getExtendFilename($realPath);

                $fileName = $file['h_bol']."_".$file['standard']."_V".$h_bol_num[$file['h_bol']].".".$extend;
                $fileName = common::sanitizeFilename($fileName);

                if (file_exists($realPath) && is_file($realPath)) {
                    $targetPath = $subFolderName . '/' . $fileName;
                    $zip->addFile($realPath, $targetPath);
                }
            }
        }
        $zip->close();
        // 输出下载 headers
        common::download_file($f, null, TRUE, null);
    }

    /**
     * 生成嵌套 ZIP 文件 
     *
     * @param array $files 文件列表
     */
    public static  function downloadFilesAsSimapleZip($files,$zipName)
    {
        // 清除缓冲区，防止输出干扰 ZIP 文件
        if (ob_get_level()) {
            ob_end_clean();
        }

        $f = ONLINE_ROOT . DS . "upload" . DS . $zipName."_" . date('Y-m-dH\hi\ms\s', time()) . ".zip";
        $zip = new ZipArchive();

        if ($zip->open($f, ZipArchive::OVERWRITE) === TRUE ||
            $zip->open($f, ZipArchive::CREATE) === TRUE) {
            
        } else {
            die("create ZIP faild。");
        }
        foreach ($files as $file) {
            if (file_exists($file) && is_file($file)) {
                $zip->addFile($file, basename($file));
            }
        }
        $zip->close();
        // 读取 ZIP 文件内容并 Base64 编码
        if (!file_exists($f)) {
            $data = array('msg' => 'ZIP file was not created.','data' => '');
            common::echo_json_encode(500, $data);
            exit();
        }
        $zipContent = file_get_contents($f);
        if ($zipContent === false) {
            $data = array('msg' => 'Failed to read ZIP file.','data' => '');
            common::echo_json_encode(500, $data);
            exit();
        }
        $base64Zip = base64_encode($zipContent);
        //删除临时 ZIP 文件
        unlink($f);
        
        $data = array("msg"=>"success","filename" =>basename($f),"data"=>$base64Zip);
        common::echo_json_encode(200, $data);
        exit();
    }


    /*
     * Get the file extension
    */
    public static function getExtendFilename($file_name) {
        $extend = pathinfo($file_name);
        $extend = strtolower($extend["extension"]);

        return $extend;
    }

    public static function getManagement(){
        //Management 自己配置需要创建表保存
        $user_management_data = common::excuteObjectSql("select management,dashboard_filter from ra_online_user where user_login = '"._getLoginName()."'");
        //为空,配置使用默认值
        $user_management = $user_management_data['management'];
        if(empty($user_management)){
            $Management = common::getdefaultManagement();
        }else{
            $Management = json_decode($user_management,true);
            foreach($Management as $key =>$val){
                $Management[$key]['id'] = ($key +1);
                $Management[$key]['switchValue'] = $val['switchValue'] == "true" ? true : false;

                if ($val['title'] == "Revenue Spent"){
                    $Management[$key]['isRevenueDisplay'] = _isRevenueDisplay();
                    if(!_isRevenueDisplay()){
                        $Management[$key]['switchValue'] = false;
                    }
                }
            }
            
        }
        $user_dashboard_filter = $user_management_data['dashboard_filter'];
        $dashboard_filter  =array();
        if(!empty($user_dashboard_filter)){
            $dashboard_filter = json_decode($user_dashboard_filter,true);
        }else{
            $threeMonthsAgoTimestamp = strtotime('-3 months');
            $date_start = date('Y-m-d', $threeMonthsAgoTimestamp);
            $date_end = date('Y-m-d');
            $date_start_two = date('m/d/Y', $threeMonthsAgoTimestamp);
            $date_end_two = date('m/d/Y');

            //ETD
            $_12MonthsAgoTimestamp = strtotime('-11 months');
            $etd_date_start = date('Y-m', $_12MonthsAgoTimestamp);
            $etd_date_end = date('Y-m');
            $etd_date_start_two = date('m/Y', $_12MonthsAgoTimestamp);
            $etd_date_end_two = date('m/Y');

            //柜子
            $ctnr_date_start = date('Y-m', $_12MonthsAgoTimestamp);
            $ctnr_date_end = date('Y-m');
            $ctnr_date_start_two = date('m/Y', $_12MonthsAgoTimestamp);
            $ctnr_date_end_two = date('m/Y');

            //Top
            $top_date_start = date('Y-m-d', $_12MonthsAgoTimestamp);
            $top_date_end = date('Y-m-d');
            $top_date_start_two = date('m/d/Y', $_12MonthsAgoTimestamp);
            $top_date_end_two = date('m/d/Y');

            //KPIDefaulteData
            $dashboard_filter['KPIDefaulteData'] = array("transportation" =>array('All'),"date_type"=>"ETD","date_start"=>$date_start_two,"date_start_two"=>$date_start,
            "date_end"=>$date_end_two,"date_end_two"=>$date_end);

            //PendingDefaultData
            $dashboard_filter['PendingDefaultData'] = array("transportation" =>array('All'),"date_type"=>"ETD","date_start"=>"","date_start_two"=>"",
                "date_end"=>"","date_end_two"=>"");

            //RecentDefaultData
            $dashboard_filter['RecentDefaultData'] = array("transportation" =>array('All'),"date_type"=>"ETD","date_start"=>$date_start_two,"date_start_two"=>$date_start,
                "date_end"=>$date_end_two,"date_end_two"=>$date_end);
            
            //ETDDefaultData
            $dashboard_filter['ETDDefaultData'] = array("transportation" =>array('All'),"date_type"=>"ETA","date_start"=>$etd_date_start_two,"date_start_two"=>$etd_date_start,
            "date_end"=>$etd_date_end_two,"date_end_two"=>$etd_date_end);

            //ContainerefaultData
            $dashboard_filter['ContainerefaultData'] = array("transportation" =>array('Sea'),"date_type"=>"ETA","date_start"=>$ctnr_date_start_two,"date_start_two"=>$ctnr_date_start,
             "date_end"=>$ctnr_date_end_two,"date_end_two"=>$ctnr_date_end);

            //RevenueDefaultData
            if(_isRevenueDisplay()){ 
                $dashboard_filter['RevenueDefaultData'] = array("transportation" =>array('All'),"date_type"=>"Invoice Issue Date","date_start"=>$ctnr_date_start_two,"date_start_two"=>$ctnr_date_start,
                "date_end"=>$ctnr_date_end_two,"date_end_two"=>$ctnr_date_end); 
            }

            //Top10faultData
            $dashboard_filter['Top10faultData'] = array("transportation" =>array('All'),"date_type"=>"ETA","date_start"=>$top_date_start_two,"date_start_two"=>$top_date_start,
            "date_end"=>$top_date_end_two,"date_end_two"=>$top_date_end);

            //OriginCo2Top10faultData
            $dashboard_filter['OriginCo2Top10faultData'] = array("transportation" =>array('All'),"date_type"=>"ETA","date_start"=>$top_date_start_two,"date_start_two"=>$top_date_start,
            "date_end"=>$top_date_end_two,"date_end_two"=>$top_date_end);

            //DestinationCo2Top10faultData
            $dashboard_filter['DestinationCo2Top10faultData'] = array("transportation" =>array('All'),"date_type"=>"ETA","date_start"=>$top_date_start_two,"date_start_two"=>$top_date_start,
            "date_end"=>$top_date_end_two,"date_end_two"=>$top_date_end);
        }
        return  array("Management" => $Management,"dashboard_filter" =>$dashboard_filter);
    }

    public static function getItemStyle($type,$code){
        if($type == 'r1'){
            $data =  array("0-20 Days" =>"#ffc594",
            "20-40 Days" =>"#ff9e4c",
            "40-60 Days" =>"#ff7500",
            "60-80 Days" =>"#ff3d00",
            "Over 80 Days" =>"#d50000");
            return $data[$code];
        }
        if($type == 'r4' || $type == 'r3' || $type == 'atd_r4' || $type == 'ata_r3'){
            $data =  array("0 Day" =>"#ffc594",
            "Today" =>"#ffc594",
            "1-2 Days" =>"#ff9e4c",
            "3-6 Days" =>"#ff7500",
            "7 Days" =>"#ff3d00");
            return $data[$code];
        }

        if($type == 'r2' || utils::startWith($type,"co2e")){
            $data =  array("45" =>"#FFE3CC",
            "40" =>"#FFAC66",
            "20" =>"#FF7500",
            "air" =>"#FFE3CC",
            "sea" =>"#FFAC66",
            "road" =>"#FF7500");
            return $data[$code];
        }
        if($type == "top"){
            $data =  array("1" =>"#FF7500",
            "2" =>"#ff9033",
            "3" =>"#ff9e4d",
            "4" =>"#ffac66",
            "5" =>"#ffba80",
            "6" =>"#ffc899",
            "7" =>"#ffd6b3",
            "8" =>"#ffe3cc",
            "9" =>"#fff1e6",
            "10" =>"#fff1e6");
            return $data[$code];
        }
    }

    //处理返回原表数据格式
    public static function mian_repot_do($value,$type,$totalValue){
        $data = array();
        $value_arr = json_decode($value,true);
        //r1 是 ETD to ETA (Days)圆形图表数据结构返回
        if($type == 'r1'){
            $ETDList = array();
            foreach($value_arr as $arr){
                $color = common::getItemStyle($type,$arr['name']);
                $ETDList[] = array("value" =>intval($arr['value']),"name" =>$arr['name'],"itemStyle" =>array("color" =>$color));
            }

            // 排序依据的键数组（这里我们用一个数字数组来表示）
            $order = ["0-20 Days", "20-40 Days", "40-60 Days","60-80 Days","Over 80 Days"]; 
            $ETDList = utils::arrayOrderBykeys($order,$ETDList,"name");

            //$ETDList = array_reverse($ETDList);
            $ETD_Title = "Total: $totalValue";
            $data = array("ETDList" =>$ETDList,"ETD_Radius"=>array('50%','80%'),"ETD_Title" =>$ETD_Title,"download_name" => "ETD to ETA (Days)");
        }
        if($type == 'r4' || $type == 'r3'){
            //重新整理一下数据，给UI一致
            $data_kd = array();
            foreach($value_arr as $arr){
                if(stripos("Today", $arr['name']) !== false){
                    $color = common::getItemStyle($type,"Today");
                    if(empty($data_kd["0"])){
                        $data_kd["0"] = array("value" =>intval($arr['value']),"name" =>"≤1 Day","itemStyle" =>array("color" =>$color));
                    }else{
                        $data_kd["0"]["value"] = $data_kd["0"]["value"] + intval($arr['value']);
                    }
                }
                if(stripos("+1 Days/+2 Days", $arr['name']) !== false){
                    $color = common::getItemStyle($type,"1-2 Days");
                    if(empty($data_kd["1"])){
                        $data_kd["1"] = array("value" =>intval($arr['value']),"name" =>"1-2 Days","itemStyle" =>array("color" =>$color));
                    }else{
                        $data_kd["1"]["value"] = $data_kd["1"]["value"] + intval($arr['value']);
                    }
                }
                if(stripos("+3 Days/+4 Days/+5 Days/+6 Days", $arr['name']) !== false){
                    $color = common::getItemStyle($type,"3-6 Days");
                    if(empty($data_kd["2"])){
                        $data_kd["2"] = array("value" =>intval($arr['value']),"name" =>"3-6 Days","itemStyle" =>array("color" =>$color));
                    }else{
                        $data_kd["2"]["value"] = $data_kd["2"]["value"] + intval($arr['value']);
                    }
                }
                if(stripos("+7 Days/Over 7 Days", $arr['name']) !== false){
                    $color = common::getItemStyle($type,"7 Days");
                    if(empty($data_kd["3"])){
                        $data_kd["3"] = array("value" =>intval($arr['value']),"name" =>"≥7 Days","itemStyle" =>array("color" =>$color));
                    }else{
                        $data_kd["3"]["value"] = $data_kd["3"]["value"] + intval($arr['value']);
                    }
                }
            }

            // 排序依据的键数组（这里我们用一个数字数组来表示）
            $order = ["≤1 Day", "1-2 Days", "3-6 Days","≥7 Days"]; 
            $data_kd = utils::arrayOrderBykeys($order,$data_kd,"name");

            if($type == 'r4'){
                $data = array("ETDList" =>$data_kd,"ETD_Radius"=>array('30%','50%'),"title1" =>"Departure","title2" =>"","download_name" => "Pending Departure");
            }
            if($type == 'r3'){
                $data = array("ETDList" =>$data_kd,"ETD_Radius"=>array('30%','50%'),"title1" =>"Arrival","title2" =>"","download_name" => "Pending Arrival");
            }
            
        }
        return $data;
    }

    //单独处理co2e bar
    public static function getCo2eBar(){
        //分担查询
        include ONLINE_ROOT . 'libs' . DS . 'map_config.ini.php';

        //新UI air sea road 目前只有sea
        $type = $_REQUEST["r_type"];

        $sqlWhere = ' and ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
        $sqlWhere = " " . $sqlWhere;

        $date_type = strtolower($_REQUEST["date_type"]);
        if (isset($_REQUEST['date_start']) && !empty($_REQUEST['date_start']))
            $sqlWhere .= " and $date_type >= '" . common::usDate2sqlDate($_REQUEST['date_start']) . " 00:00:00'";
        if (isset($_REQUEST['date_end']) && !empty($_REQUEST['date_end']))
            $sqlWhere .= " and $date_type <= '" . common::usDate2sqlDate($_REQUEST['date_end']) . " 23:59:59'";

        $transportation = $_REQUEST["transportation"];
        if(!is_array($transportation)){
            $transportation = array($transportation);
        }
        $mode_param = "";
        if(utils::count($transportation) == 1 && strtolower($transportation[0]) == 'all' ){
            $mode_param = "'sea','air','road','rail'";         
        }else{
            $transport_mode = utils::implode(';', $transportation);
            $mode_param = common::getInNotInSqlForSearch($transport_mode);
        }
        $sqlWhere .= " and transport_mode_extend in ($mode_param)";

        $sqlWhere .= common::getDashboardContactIDSqlWhere();
         
        $ContainerCount_Title = array();
        //先查询总的排放量sea air road，确定排名后，在分别查询对应的sea air road 
        //这里的问题跟 top 一样 。总的排放量查询有问题。目前是总的排放量 暂时等于 sea的排放量
        if ($type == "co2e_orgin"){
            $co2e_shippr_sql = "select SUM(COALESCE(carbon_emission,0)) as catnum ,
                sum(case when (1<>1 or (transport_mode_extend='air')) then COALESCE(carbon_emission,0) else 0 end) as air_rc,
                sum(case when (1<>1 or (transport_mode_extend='sea')) then COALESCE(carbon_emission,0) else 0 end) as sea_rc, 
                sum(case when (1<>1 or (transport_mode_extend='road')) then COALESCE(carbon_emission,0) else 0 end) as road_rc,
                sum(case when (1<>1 or (transport_mode_extend='rail')) then COALESCE(carbon_emission,0) else 0 end) as rail_rc, 
                shippr_uncode as station from public.kln_ocean  where 1=1 and COALESCE(shippr_uncode,'')<>'' and carbon_emission is not null  $sqlWhere group by shippr_uncode order by catnum desc limit 10";
            //$co2e_aLL = common::excuteListSql($co2e_shippr_sql);
            $co2e_aLL = $mapdb->GetAll($co2e_shippr_sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $co2e_shippr_sql), 0));
        }
        if ($type == "co2e_destination"){
            $co2e_consignee_sql = "select SUM(COALESCE(carbon_emission,0)) as catnum ,
                sum(case when (1<>1 or (transport_mode_extend='air')) then COALESCE(carbon_emission,0) else 0 end) as air_rc,
                sum(case when (1<>1 or (transport_mode_extend='sea')) then COALESCE(carbon_emission,0) else 0 end) as sea_rc, 
                sum(case when (1<>1 or (transport_mode_extend='road')) then COALESCE(carbon_emission,0) else 0 end) as road_rc,
                sum(case when (1<>1 or (transport_mode_extend='rail')) then COALESCE(carbon_emission,0) else 0 end) as rail_rc,  
                consignee_uncode as station from public.kln_ocean  where 1=1 and COALESCE(consignee_uncode,'')<>'' and carbon_emission is not null $sqlWhere group by consignee_uncode order by catnum desc limit 10";
            //$co2e_aLL = common::excuteListSql($co2e_consignee_sql);
            $co2e_aLL = $mapdb->GetAll($co2e_consignee_sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $co2e_consignee_sql), 0));
        }
        //最大Y值
        $maxY = 0;
        $stations = array();
        foreach($co2e_aLL as $val){
            $ContainerCount_Title[] = $val['station'];
            $maxY = $maxY > $val['catnum'] ? $maxY : $val['catnum'];
            if(!empty($val['station'])){
                $stations[] = $val['station'];
            }
        }
        // $station_str = utils::implode(',',$stations);

        $ContainerCounSeries = array();
        $shipment_mode_arr = array("air","sea","road","rail");
        foreach($shipment_mode_arr as $_shipment_mode){
            //类型有 sea air road，目前之类只查询sea的，其他为空
            $shipment_mode = $_shipment_mode;
            // $co2e = array();
            if ($type == "co2e_orgin"){
                // $co2e_shippr_sql_union = utils::_getSql($station_str,$type,$shipment_mode,$sqlWhere);
                // if(!empty($co2e_shippr_sql_union)){
                //     $co2e = common::excuteListSql($co2e_shippr_sql_union);
                // }
                $download_name = "CO2e Emission by Origin (Top 10)";
            }
            if ($type == "co2e_destination"){
                // $co2e_consignee_union = utils::_getSql($station_str,$type,$shipment_mode,$sqlWhere);
                // if(!empty($co2e_consignee_union)){
                //     $co2e = common::excuteListSql($co2e_consignee_union);
                // }
                $download_name = "CO2e Emission by Destination (Top 10)";
            }

            $total = 0;
            $max = 0;
            $data = array();
            foreach($co2e_aLL as $val){
                $catnum = $shipment_mode."_rc";
                $data[] = empty($val[$catnum])? 0 : round($val[$catnum], 4);
                $total = $total + $val[$catnum];
                $max = $max < $val[$catnum] ? $val[$catnum] : $max;
            }
            $ContainerCounSeries[$shipment_mode] = array("data"=>$data,"total"=>$total,"max"=>$max);
        }
        
        //处理返回时数据格式 其实不太需要计算最大值，在最开始总量查询的时候以及查询出来
        $ContainerCounSeries_return = array();
        foreach($ContainerCounSeries as $k =>$v){
            $color = common::getItemStyle($type,$k);
            $ContainerCounSeries_return[] = array("name"=>$k,"type"=>"bar","emphasis" => array("focus" =>"none"),
                "stack" =>"总计","data" =>$v['data'],"itemStyle" =>array("color" =>$color),'barWidth'=>'40');  
        }
        //处理返回原表数据格式
        //计算刻度值 最小值是0，最大值是3000，刻度是500  interval
        $interval = utils::calculateTicks(0,$maxY,10);
        if($interval == 0){
            //处理返回默认值
            $interval = 1;
        }
        
        $returnData = array("ContainerCount_Title"=>"","ContainerCountList" =>$ContainerCount_Title,"ContainerCounSeries" =>$ContainerCounSeries_return,
            "min" => 0,"Max" =>$interval*10,"interval" =>$interval,"download_name" => $download_name);
        return $returnData;
    }

    public static function getTopBar(){
        $sqlWhere = ' and ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
        $sqlWhere = " " . $sqlWhere;

        $date_type = strtolower($_REQUEST["date_type"]);
        if (isset($_REQUEST['date_start']) && !empty($_REQUEST['date_start']))
            $sqlWhere .= " and $date_type >= '" . common::usDate2sqlDate($_REQUEST['date_start']) . " 00:00:00'";
        if (isset($_REQUEST['date_end']) && !empty($_REQUEST['date_end']))
            $sqlWhere .= " and $date_type <= '" . common::usDate2sqlDate($_REQUEST['date_end']) . " 23:59:59'";

        $transportation = $_REQUEST["transportation"];
        if(!is_array($transportation)){
            $transportation = array($transportation);
        }
        $mode_param = "";
        if(utils::count($transportation) == 1 && strtolower($transportation[0]) == 'all' ){
            $mode_param = "'sea','air','road','rail'";         
        }else{
            $transport_mode = utils::implode(';', $transportation);
            $mode_param = common::getInNotInSqlForSearch($transport_mode);
        }
        $sqlWhere .= " and transport_mode_extend in ($mode_param)";    
         
        $toporiginType = "shippr_uncode";
        $shippr_uncode_10_sql ="with aa as  (select count(shippr_uncode) as num,shippr_uncode,
                                (array_agg(shipper_city))[1] AS shipper_city from public.kln_ocean  
                        where 1=1 and COALESCE(shippr_uncode,'')<>'' $sqlWhere group by shippr_uncode order by num desc limit 10)
            select   aa.*,dd.* from  aa  
            left join LATERAL ( select lon as lng, lat as lat,
                '' as label, '' as infor, 3 as sort,
                null::timestamp without time zone as stime,''::text as ptype 
            from vessel.vt_unlocode where (lat<=90 and lat>=-90) and (lon<=180 and lon>=-180) and lon is not null and lat is not null and uncode=aa.shippr_uncode ) dd on true";
   
        $shippr_uncode_10 = common::excuteListSql($shippr_uncode_10_sql);
        //如果值没有：客户地址-->站点地址-->Port地址（POL/POD） 
        if(utils::count($shippr_uncode_10) == 1 && empty($shippr_uncode_10[0]["shippr_uncode"])){
            $toporiginType = "fport_of_loading_un";
            $shippr_uncode_10_sql = "with aa as  (select count(fport_of_loading_un) as num,fport_of_loading_un as shippr_uncode 
                    from public.kln_ocean where 1=1 and COALESCE(fport_of_loading_un,'')<>'' $sqlWhere group by fport_of_loading_un order by num desc limit 10)
                    select   aa.*,dd.* from  aa  
                        left join LATERAL ( select lon as lng, lat as lat,
                            '' as label, '' as infor, 3 as sort,
                            null::timestamp without time zone as stime,''::text as ptype 
                        from vessel.vt_unlocode where (lat<=90 and lat>=-90) and (lon<=180 and lon>=-180) and lon is not null and lat is not null and uncode=aa.fport_of_loading_un ) dd on true";
            $shippr_uncode_10 = common::excuteListSql($shippr_uncode_10_sql);
        }
        //$toporiginType = "fport_of_loading_un";

        $topdestinationinType = "consignee_uncode";
        $consignee_uncode_10_sql = "with aa as  (select count(consignee_uncode) as num,consignee_uncode,
                    (array_agg(consignee_city))[1] AS consignee_city 
                from public.kln_ocean  where 1=1  and COALESCE(consignee_uncode,'')<>''  $sqlWhere group by consignee_uncode order by num desc limit 10)
        select   aa.*,dd.* from  aa  
        left join LATERAL ( select lon as lng, lat as lat,
            '' as label, '' as infor, 3 as sort,
            null::timestamp without time zone as stime,''::text as ptype 
        from vessel.vt_unlocode where (lat<=90 and lat>=-90) and (lon<=180 and lon>=-180) and lon is not null and lat is not null and uncode=aa.consignee_uncode ) dd on true";

        $consignee_uncode_10 = common::excuteListSql($consignee_uncode_10_sql);
        //如果值没有：客户地址-->站点地址-->Port地址（POL/POD） 
        if(utils::count($consignee_uncode_10) == 1 && empty($consignee_uncode_10[0]["consignee_uncode"])){
            $topdestinationinType = "mport_of_discharge_un";
            $consignee_uncode_10_sql = "with aa as  (select count(mport_of_discharge_un) as num,mport_of_discharge_un as consignee_uncode from public.kln_ocean where 1=1 and COALESCE(mport_of_discharge_un,'')<>'' $sqlWhere group by mport_of_discharge_un order by num desc limit 10)
                select   aa.*,dd.* from  aa  
                left join LATERAL ( select lon as lng, lat as lat,
                    '' as label, '' as infor, 3 as sort,
                    null::timestamp without time zone as stime,''::text as ptype 
                from vessel.vt_unlocode where (lat<=90 and lat>=-90) and (lon<=180 and lon>=-180) and lon is not null and lat is not null and uncode=aa.mport_of_discharge_un ) dd on true";
            $consignee_uncode_10 = common::excuteListSql($consignee_uncode_10_sql);
        }

        $toporigin = array();
        $toporiginMap = array();
        $i = 0;
        $origiNunMax = 0;
        foreach($shippr_uncode_10 as $orgin){
            $i = $i + 1;
            $map = array();
            if(!empty($orgin['lat'])&&!empty($orgin['lng'])){
                $map = array("lat" =>$orgin['lat'],"lng" =>$orgin['lng']);
            }   
            //$json = '{"lng":121.8525,"lat":29.926545,"label":"'.$orgin['origin'].'","infor":"LAT KRABANG, THAILAND","sort":"0","stime":null,"ptype":"por"}';
            //$map = json_decode($json,true);
            if(!empty($map)){
                $toporiginMap[] = array("qandl"=>array(floatval($map['lat']),floatval($map['lng'])),
                    "divIcon" => array("iconSize"=>0),
                    "name" =>$orgin['shippr_uncode'],
                    "color" =>common::getItemStyle("top",$i),
                    "value" =>$orgin['num'],
                    "textcolor" =>"#FFF");
            }
            //查询uncode,对应的city
            $city = "";
            if($toporiginType == "shippr_uncode"){
                $city = $orgin['shipper_city'];
            }
            //$city 做假数据
            $toporigin[] = array("name"=>$orgin['shippr_uncode'],"city_name"=>$city,"value"=>$orgin['num'],"color"=>common::getItemStyle("top",$i));
            $origiNunMax = $origiNunMax < $orgin['num'] ? $orgin['num'] : $origiNunMax;
        }

        $topdestination = array();
        $topdestinationinMap = array();
        $i = 0;
        $agentiNunMax = 0;
        foreach($consignee_uncode_10 as $agent){
            $i = $i + 1;
            $map = array();
            if(!empty($agent['lat'])&&!empty($agent['lng'])){
                $map = array("lat" =>$agent['lat'],"lng" =>$agent['lng']); 
            }  
            // $json = '{"lng":"100.78594000","lat":"13.68521000","label":"'.$agent['agent'].'","infor":"LAT KRABANG, THAILAND","sort":"0","stime":null,"ptype":"por"}';
            // $map = json_decode($json,true);
            if(!empty($map)){
                $topdestinationinMap[] = array("qandl"=>array(floatval($map['lat']),floatval($map['lng'])),
                    "divIcon" => array("iconSize"=>0),
                    "name" =>$agent['consignee_uncode'],
                    "color" =>common::getItemStyle("top",$i),
                    "value" =>$agent['num'],
                    "textcolor" =>"#FFF");
            }

            //查询uncode,对应的city
            $city = "";
            if($topdestinationinType == "consignee_uncode"){
                $city = $agent['consignee_city'];
            }
            //$city 做假数据
            $topdestination[] = array("name"=>$agent['consignee_uncode'],"city_name"=>$city,"value"=>$agent['num'],"color"=>common::getItemStyle("top",$i));
            $agentiNunMax = $agentiNunMax < $agent['num'] ? $agent['num'] : $agentiNunMax;
        }

        //处理返回原表数据格式
        $interval = utils::calculateTicks(0,$origiNunMax,10);
        if($interval == 0){
            //处理返回默认值
            $interval = 1;
        }

        $dest_interval = utils::calculateTicks(0,$agentiNunMax,10);
        if($dest_interval == 0){
            //处理返回默认值
            $dest_interval = 1;
        }

        $returnData = array("seller_data_list_origin"=>$toporigin,"toporiginMap"=>array_reverse($toporiginMap),"toporiginType"=>$toporiginType,
            "seller_data_list_destination"=>$topdestination,"topdestinationinMap"=>array_reverse($topdestinationinMap),"topdestinationinType"=>$topdestinationinType,
            "min" => 0,"Max" =>$interval*10,"interval" =>$interval,
            "dest_min" => 0,"dest_Max" =>$dest_interval*10,"dest_interval" =>$dest_interval);
        return $returnData;
    }


    public static function getTopBarNew(){
        //分担查询
        include ONLINE_ROOT . 'libs' . DS . 'map_config.ini.php';

        $sqlWhere = ' and ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
        $sqlWhere = " " . $sqlWhere;

        $date_type = strtolower($_REQUEST["date_type"]);
        if (isset($_REQUEST['date_start']) && !empty($_REQUEST['date_start']))
            $sqlWhere .= " and $date_type >= '" . common::usDate2sqlDate($_REQUEST['date_start']) . " 00:00:00'";
        if (isset($_REQUEST['date_end']) && !empty($_REQUEST['date_end']))
            $sqlWhere .= " and $date_type <= '" . common::usDate2sqlDate($_REQUEST['date_end']) . " 23:59:59'";

        $transportation = $_REQUEST["transportation"];
        if(!is_array($transportation)){
            $transportation = array($transportation);
        }
        $mode_param = "";
        if(utils::count($transportation) == 1 && strtolower($transportation[0]) == 'all' ){
            $mode_param = "'sea','air','road','rail'";         
        }else{
            $transport_mode = utils::implode(';', $transportation);
            $mode_param = common::getInNotInSqlForSearch($transport_mode);
        }
        $sqlWhere .= " and transport_mode_extend in ($mode_param)"; 
        $sqlWhere .= common::getDashboardContactIDSqlWhere();   
         
        $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($page,$ocean,$transport_mode,$_schemas,$EDI315TimeAndLocation=array()){
        //Milestones info列名固定
        $Milestones_column = array();
        $Milestones_column[] = array("title" =>"Milestones","cleaned_field_name"=>$page.".milestones","field" =>"milestones","formatter" =>"normal","type" =>"normal");
        $Milestones_column[] = array("title" =>"Date Time","cleaned_field_name"=>$page.".date_time","field" =>"date_time","formatter" =>"dateTime","type" =>"normal");
        $Milestones_column[] = array("title" =>"Locations","cleaned_field_name"=>$page.".locations","field" =>"locations","formatter" =>"normal","type" =>"normal");
        $Milestones_column[] = array("title" =>"Remarks","cleaned_field_name"=>$page.".remarks","field" =>"remarks","formatter" =>"normal","type" =>"normal");

        //EDI315 有关联查询，以那个为准。但是如果是手工录入或者不是EDI 进去的Milestone的情况，这以这个为准
        $Milestones_NO_Mapping = common::getMilestones_NO_Mapping_Data($ocean);
    
        //Milestones 数据信息待定
        $Milestones_data = array();
        if($transport_mode == 'sea'){
            $Milestones_data_arr = common::excuteListSql("select  sn.description,act_date||' '||COALESCE(act_time,'') as date_time,
                    remark,timezone,a.code
                from  public.ocean_milestone a  
                    inner join public.customer_service_milestone_sno sn on sn.code=a.code and sn.type = 'sea'
                where a.serial_no='".$ocean["serial_no"]."' and act_date is not null  order by sn.sno asc");  
        }else{
            $Milestones_data_arr = common::excuteListSql("select  sn.description,act_date||' '||COALESCE(act_time,'') as date_time,
                    remark,timezone,a.code
                from  $_schemas.air_milestone a  
                    inner join public.customer_service_milestone_sno sn on sn.code=a.code and sn.type = 'air'
                where a.serial_no='".$ocean["serial_no"]."' and act_date is not null  order by sn.sno asc");  
        }         
        foreach($Milestones_data_arr as $mda){
            $timezone = "";
            $IFFICC_locations ="";
            if(!empty($EDI315TimeAndLocation[$mda['code']])){
                $edi315Info = $EDI315TimeAndLocation[$mda['code']];
                if(!empty($edi315Info)){
                    $timezone = $edi315Info['timezone'];
                }
                //IFFICC 比较特殊用EDI的locations
                if($mda['code'] == "IFFICC"){
                    $IFFICC_locations = $edi315Info['location'];
                }
            }else{
                $edi315Info =  $Milestones_NO_Mapping[$mda['code']];
                if(!empty($edi315Info)){
                    $timezone = $edi315Info['timezone'];
                }
            }
            //locations 以配置的信息为准
            $locations = "";
            $edi315Info =  $Milestones_NO_Mapping[$mda['code']];
            if(!empty($edi315Info)){
                $locations = $edi315Info['location'];
                if($mda['code'] == "IFFICC" && !empty($IFFICC_locations)){
                    $locations = $IFFICC_locations;
                }
            }
            $Milestones_data[] = array("milestones"=>$mda['description'],"date_time"=>$mda['date_time'],"timezone" =>$timezone,
                "locations" => $locations, "remarks" =>$mda['remark']);
        }  
        $Milestones = array("Milestones_column"=>$Milestones_column,"Milestones_data" =>$Milestones_data);

        return $Milestones;
    }

    /**
     * 查询uncode 对应的时区
     */
    public static function getCityPortsInfo($uncodes){
        $data =array();
        $sql = "select time_zone as timezonecode,uncode as ctrycitycode from public.city_timezone where lower(uncode) ". common::getInNotInSql($uncodes);
        $uncode_rs = common::excuteListSql($sql);
        foreach($uncode_rs as $value){
            $data[$value['ctrycitycode']] = $value['timezonecode'];
        }
        return $data;
    }

    /**
     * EDI315 有关联查询，以那个为准。但是如果是手工录入或者没有对应的情况，这以这个为准
     */
    public static function getMilestones_NO_Mapping_Data($ocean){
        $Milestones_NO_Mapping = array();
        //取origin  $ocean['aa_city'] 不是必需,对应文件里未提location来源
        $Milestones_NO_Mapping['IFFBCF'] = array("code"=>"origin","timezone"=>$ocean['aa_timezone'],"location"=>"");
        $Milestones_NO_Mapping['IFFBCR'] = array("code"=>"origin","timezone"=>$ocean['aa_timezone'],"location"=>"");
        $Milestones_NO_Mapping['IFFCPU'] = array("code"=>"origin","timezone"=>$ocean['aa_timezone'],"location"=>"");
        $Milestones_NO_Mapping['IFFSIL'] = array("code"=>"origin","timezone"=>$ocean['aa_timezone'],"location"=>"");
        $Milestones_NO_Mapping['IFFEDO'] = array("code"=>"origin","timezone"=>$ocean['aa_timezone'],"location"=>"");
        $Milestones_NO_Mapping['IFFECC'] = array("code"=>"origin","timezone"=>$ocean['aa_timezone'],"location"=>"");

        //Place of Receipt
        $Milestones_NO_Mapping['IFFREC'] = array("code"=>"por/pol","timezone"=>$ocean['por_timezone'],"location"=>$ocean['por_uncity']);
        $Milestones_NO_Mapping['IFFECP'] = array("code"=>"por/pol","timezone"=>$ocean['por_timezone'],"location"=>$ocean['por_uncity']);
      
        //Port of loading
        $Milestones_NO_Mapping['IFFONB'] = array("code"=>"por/pol","timezone"=>$ocean['pol_timezone'],"location"=>$ocean['pol_uncity']);
        $Milestones_NO_Mapping['IFFDEP'] = array("code"=>"por/pol","timezone"=>$ocean['pol_timezone'],"location"=>$ocean['pol_uncity']);
        
        //取destination $ocean['dd_city']不是必需
        $Milestones_NO_Mapping['IFFCSN'] = array("code"=>"destination","timezone"=>$ocean['dd_timezone'],"location"=>"");
        $Milestones_NO_Mapping['IFFIDO'] = array("code"=>"destination","timezone"=>$ocean['dd_timezone'],"location"=>"");

        //取Port of discharge
        $Milestones_NO_Mapping['IFFARR'] = array("code"=>"mpod","timezone"=>$ocean['mpod_timezone'],"location"=>$ocean['mpod_uncity']);
        $Milestones_NO_Mapping['IFFUND'] = array("code"=>"mpod/pod/fd","timezone"=>$ocean['mpod_timezone'],"location"=>$ocean['mpod_uncity']);
        $Milestones_NO_Mapping['IFFICC'] = array("code"=>"mpod","timezone"=>$ocean['mpod_timezone'],"location"=>$ocean['mpod_uncity']);

        //Place of Delivery
        $Milestones_NO_Mapping['IFFAFD'] = array("code"=>"pod","timezone"=>$ocean['pod_timezone'],"location"=>$ocean['pod_uncity']);
        $Milestones_NO_Mapping['IFFCTA'] = array("code"=>"pod/mpod","timezone"=>$ocean['pod_timezone'],"location"=>$ocean['pod_uncity']);
        $Milestones_NO_Mapping['IFFADW'] = array("code"=>"pod","timezone"=>$ocean['pod_timezone'],"location"=>$ocean['pod_uncity']);
        $Milestones_NO_Mapping['IFFDDW'] = array("code"=>"pod","timezone"=>$ocean['pod_timezone'],"location"=>$ocean['pod_uncity']);
        $Milestones_NO_Mapping['IFFPPD'] = array("code"=>"mpod/pod/fd","timezone"=>$ocean['pod_timezone'],"location"=>$ocean['pod_uncity']);
        $Milestones_NO_Mapping['IFFECR'] = array("code"=>"mpod/pod/fd","timezone"=>$ocean['pod_timezone'],"location"=>$ocean['pod_uncity']);
        

        //final_destination $ocean['fd_city']不是必需
        $Milestones_NO_Mapping['IFFDEL'] = array("code"=>"fd","timezone"=>$ocean['_fd_timezone'],"location"=>"");
        $Milestones_NO_Mapping['IFFHBL'] = array("code"=>"fd","timezone"=>$ocean['_fd_timezone'],"location"=>"");

        return $Milestones_NO_Mapping;
    }

    /**
     * 这个详细查询已经带模式了
     */
    public static function getEDI315StatusForDetail($serial_no){
        $data = array();
        $ctnr_sql = "SELECT oc.ctnr, oc.serial_no,oc.size FROM oc_container oc LEFT JOIN ocean o ON oc.serial_no = o.serial_no 
                WHERE o.serial_no='$serial_no'";
        $ctnr_data = common::excuteListSql($ctnr_sql); 
        $containerStatusData = array();
        $VD = "";
        $VA = "";
        $VD_RELAY  = "";
        $VA_RELAY  = "";    
        foreach ($ctnr_data as $cd){
            //存在柜号为空的数据情况
            if(empty($cd['ctnr'])){
                continue;
            }
            //(select uncity from public.ports where uncode = s.event_code) as uncity, 按新版的逻辑这个先取消掉
            $ctnr_status_sql = "select s.source_id, s.event_base as event,
                    to_char(to_timestamp(s.event_date, 'YYYYMMDD'), 'YYYY-MM-DD') as eventdate,
                    to_char(to_timestamp(s.event_time, 'HH24MI'), 'HH24:MI') as eventtime,
                    e.description,s.event_type as eventtype, 
                    s.event_code as eventcode, s.event_city as eventcity,
                    (select time_zone from public.city_timezone where uncode = s.event_code) as timezone,
                    s.event_city as uncity,
                    case when s.event_base ='I' or s.event_base ='TR'  then 'IFFREC'::text
                        when s.event_base ='AE'  then 'IFFONB'::text
                        when s.event_base ='VD'  then 'IFFDEP'::text
                        when s.event_base ='EB' or s.event_base ='VA' then 'IFFARR'::text
                        when s.event_base ='UV'  then 'IFFUND'::text
                        when s.event_base ='VA'  then 'IFFAFD'::text
                        when s.event_base ='AV'  then 'IFFCTA'::text
                        when s.event_base ='CT'  then 'IFFICC'::text
                        when s.event_base ='OA' or s.event_base ='D'  then 'IFFPPD'::text
                        when s.event_base ='RD'  then 'IFFECR'::text
                        when s.event_base ='EE'  then 'IFFECP'::text
                        else '' ::text
                    end as milestone_code  
                from public.ra_online_container_status_v s 
                    left join public.ra_online_edi_event e on s.event_base = e.ra_name 
                where s.serial_no = '" . pg_escape_string($cd['serial_no']) . "' 
                    and s.container_no = '" . pg_escape_string($cd['ctnr']) . "' and is_display = true 
                order by to_timestamp(s.event_date, 'YYYYMMDD') desc,
                    to_timestamp(s.event_time, 'HH24MI') desc,e.ra_order desc";
            $ctnr_status = common::excuteListSql($ctnr_status_sql);       

            //Container_Status　新版只显示几个状态， CTNR# EE Ｉ AE VD VA
            $container_status_column = array("EE","I","AE","VD","VA");
            $containerStatusDataTemp = array();
            $content = array();
            //按顺序处理 新版只显示几个状态
            //foreach($container_status_column as $status){
                foreach($ctnr_status as $event){
                    //if(strtolower($event['event']) == strtolower($status)){
                        $eventdate = $event["eventdate"];
                        if (empty($event["source_id"]) || $event["source_id"]==0) {
                            $eventdate.= " " . $event["eventtime"];
                        }
                        $location_code = $event["eventcode"];
                        $content[] = array("code" =>$event['eventtype']."-".$location_code,"title" =>$event['description'],"date"=>$eventdate,"country"=>$location_code,"timezone"=>$event['timezone'],
                            "uncity"=>$event['uncity']);
                        if(strtolower($event['event']) == "vd" && !empty($eventdate)){
                            $VD = $eventdate;
                        }
                        if(strtolower($event['event']) == "va" && !empty($eventdate)){
                            $VA = $eventdate;
                        }
                        if(strtolower($event['event']) == strtolower("VD_RELAY") && !empty($eventdate)){
                            $VD_RELAY = $eventdate;
                        }
                        if(strtolower($event['event']) == strtolower("VA_RELAY") && !empty($eventdate)){
                            $VA_RELAY = $eventdate;
                        }
                    //}
                }
            //}
            //记录所有的信息
            $EDI315TimeAndLocation = array();
            foreach($ctnr_status as $event){
                if(!empty($EDI315TimeAndLocation['IFFARR']) && $EDI315TimeAndLocation['IFFARR']['code'] == "EB"){
                    //如果存在EB 的EB 的优先级最高
                    continue;
                }
                if(!empty($EDI315TimeAndLocation['IFFPPD']) && $EDI315TimeAndLocation['IFFARR']['code'] == "OA"){
                    //如果存在OA 的OA 的优先级最高
                    continue;
                }
                $EDI315TimeAndLocation[$event['milestone_code']] = array("code"=>$event['event'],"timezone"=>$event['timezone'],"location"=>$event['uncity']);
            }

            $containerStatusDataTemp['label'] = $cd['ctnr']." / ".$cd['size'];
            $containerStatusDataTemp['ctnr_label'] = $cd['ctnr'];
            $containerStatusDataTemp['content'] = $content;
            $containerStatusData[] = $containerStatusDataTemp;
        }
        $data['containerStatusData'] = $containerStatusData;
        $data['EDI315TimeAndLocation'] = $EDI315TimeAndLocation;
        $data['VD'] = $VD;
        $data['VA'] = $VA;
        $data['VD_RELAY'] = $VD_RELAY;
        $data['VA_RELAY'] = $VA_RELAY;
        return $data;
    }

    public static function getEDI315Time($serial_no,$_schemas){
        //Timezone From 来自于EDI315
        $ctnr_sql = "SELECT oc.ctnr, oc.serial_no,oc.size FROM $_schemas.oc_container oc LEFT JOIN $_schemas.ocean o ON oc.serial_no = o.serial_no 
                WHERE o.serial_no='$serial_no'";
        $ctnr_data = common::excuteListSql($ctnr_sql); 
        $EDI315TimeAndLocation = array();
        foreach ($ctnr_data as $cd){
            //存在柜号为空的数据情况
            if(empty($cd['ctnr'])){
                continue;
            }
            $ctnr_status_sql = "select 
                    (select time_zone from public.city_timezone where uncode = s.event_code) as timezone,
                    case when s.event_base ='I' or s.event_base ='TR' then 'IFFREC'::text
                        when s.event_base ='AE'  then 'IFFONB'::text
                        when s.event_base ='VD'  then 'IFFDEP'::text
                        when s.event_base ='EB' or s.event_base ='VA' then 'IFFARR'::text
                        when s.event_base ='UV'  then 'IFFUND'::text
                        when s.event_base ='VA'  then 'IFFAFD'::text
                        when s.event_base ='AV'  then 'IFFCTA'::text
                        when s.event_base ='CT'  then 'IFFICC'::text
                        when s.event_base ='OA' or s.event_base ='D'  then 'IFFPPD'::text
                        when s.event_base ='RD'  then 'IFFECR'::text
                        when s.event_base ='EE'  then 'IFFECP'::text
                        else '' ::text
                    end as milestone_code  
                from public.ra_online_container_status_v s 
                    left join public.ra_online_edi_event e on s.event_base = e.ra_name 
                where s.serial_no = '" . pg_escape_string($cd['serial_no']) . "' 
                    and s.container_no = '" . pg_escape_string($cd['ctnr']) . "' and is_display = true 
                order by to_timestamp(s.event_date, 'YYYYMMDD') asc,
                    to_timestamp(s.event_time, 'HH24MI') asc,e.ra_order asc";
            $ctnr_status = common::excuteListSql($ctnr_status_sql);       
            //记录所有的信息、替换最后一个柜子
            $EDI315TimeAndLocation = array();
            foreach($ctnr_status as $event){
                if(!empty($EDI315TimeAndLocation['IFFARR']) && $EDI315TimeAndLocation['IFFARR']['code'] == "EB"){
                    //如果存在EB 的EB 的优先级最高
                    continue;
                }
                if(!empty($EDI315TimeAndLocation['IFFPPD']) && $EDI315TimeAndLocation['IFFARR']['code'] == "OA"){
                    //如果存在OA 的OA 的优先级最高
                    continue;
                }
                $EDI315TimeAndLocation[$event['milestone_code']] = array("code"=>$event['event'],"timezone"=>$event['timezone']);
            }
        }
        return $EDI315TimeAndLocation;
    }

    public static function getMilestoneTimeAndlocations($milestone_code,$transport_mode,$serial_no,$order_from){
        $EDI315TimeAndLocation = array();
        if($transport_mode == "sea"){
            $ctnr_sql = "SELECT oc.ctnr, oc.serial_no,oc.size FROM $order_from.oc_container oc LEFT JOIN ocean o ON oc.serial_no = o.serial_no 
                    WHERE o.serial_no='$serial_no'";
            $ctnr_data = common::excuteListSql($ctnr_sql); 
            foreach ($ctnr_data as $cd){
                //存在柜号为空的数据情况
                if(empty($cd['ctnr'])){
                    continue;
                }
                $ctnr_status_sql = "select s.source_id, s.event_base as event,
                        to_char(to_timestamp(s.event_date, 'YYYYMMDD'), 'YYYY-MM-DD') as eventdate,
                        to_char(to_timestamp(s.event_time, 'HH24MI'), 'HH24:MI') as eventtime,
                        e.description,s.event_type as eventtype, 
                        s.event_code as eventcode, s.event_city as eventcity,
                        (select time_zone from public.city_timezone where uncode = s.event_code) as timezone,
                        (select uncity from public.ports where uncode = s.event_code) as uncity,
                        case when s.event_base ='I'  then 'IFFREC'::text
                            when s.event_base ='AE'  then 'IFFONB'::text
                            when s.event_base ='VD'  then 'IFFDEP'::text
                            when s.event_base ='EB' or s.event_base ='VA' then 'IFFARR'::text
                            when s.event_base ='UV'  then 'IFFUND'::text
                            when s.event_base ='VA'  then 'IFFAFD'::text
                            when s.event_base ='AV'  then 'IFFCTA'::text
                            when s.event_base ='CT'  then 'IFFICC'::text
                            when s.event_base ='OA' or s.event_base ='D'  then 'IFFPPD'::text
                            when s.event_base ='EE'  then 'IFFECP'::text
                            else '' ::text
                        end as milestone_code  
                    from public.ra_online_container_status_v s 
                        left join ra_online_edi_event e on s.event_base = e.ra_name 
                    where s.serial_no = '" . pg_escape_string($cd['serial_no']) . "' 
                        and s.container_no = '" . pg_escape_string($cd['ctnr']) . "' and is_display = true 
                    order by to_timestamp(s.event_date, 'YYYYMMDD') asc,
                        to_timestamp(s.event_time, 'HH24MI') asc,e.ra_order asc";
                $ctnr_status = common::excuteListSql($ctnr_status_sql);       
                //记录所有的信息
                $EDI315TimeAndLocation = array();
                foreach($ctnr_status as $event){
                    if(!empty($EDI315TimeAndLocation['IFFARR']) && $EDI315TimeAndLocation['IFFARR']['code'] == "EB"){
                        //如果存在EB 的EB 的优先级最高
                        continue;
                    }
                    if(!empty($EDI315TimeAndLocation['IFFPPD']) && $EDI315TimeAndLocation['IFFARR']['code'] == "OA"){
                        //如果存在OA 的OA 的优先级最高
                        continue;
                    }
                    $EDI315TimeAndLocation[$event['milestone_code']] = array("code"=>$event['event'],"timezone"=>$event['timezone'],"location"=>$event['uncity']);
                }
            }
        }

        //当前数据的所有time city 查询
        $sql = common::trackingSql($serial_no,$order_from);
        $data_arr = common::excuteListSql($sql);
        $data = $data_arr[0];

        //EDI315 有关联查询，以那个为准。但是如果是手工录入或者不是EDI 进去的Milestone的情况，这以这个为准
        $Milestones_NO_Mapping = common::getMilestones_NO_Mapping_Data($data);

        $timezone = "";
        $IFFICC_locations ="";
        if(!empty($EDI315TimeAndLocation[$milestone_code])){
            $edi315Info = $EDI315TimeAndLocation[$milestone_code];
            if(!empty($edi315Info)){
                $timezone = $edi315Info['timezone'];
            }
            //IFFICC 比较特殊用EDI的locations
            if($milestone_code == "IFFICC"){
                $IFFICC_locations = $edi315Info['location'];
            }
        }else{
            $edi315Info =  $Milestones_NO_Mapping[$milestone_code];
            if(!empty($edi315Info)){
                $timezone = $edi315Info['timezone'];
            }
        }
        //locations 以配置的信息为准
        $locations = "";
        $edi315Info =  $Milestones_NO_Mapping[$milestone_code];
        if(!empty($edi315Info)){
            $locations = $edi315Info['location'];
            if($milestone_code == "IFFICC" && !empty($IFFICC_locations)){
                $locations = $IFFICC_locations;
            }
        }
        return  array("timezone" =>$timezone,"locations" =>$locations);
    }

    public static function trackingSql($serial_no,$order_from){
        $_schemas = $order_from;
        if($_schemas == 'public'){
            $_schemas = "ocean";
        }
        $sql = "with o as(
            SELECT  oo.*,m_bol as _m_bol, h_bol as _h_bol,
                    (select time_zone from public.city_timezone where uncode = oo.fport_of_loading_un limit 1) as pol_timezone,
                    case when oo.transport_mode ='sea' 
                            then (select uncity from $order_from.ports where uncode = oo.fport_of_loading_un limit 1)
                        else (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.fport_of_loading_un limit 1)
                    end as pol_uncity,
                    (select time_zone from public.city_timezone where uncode = oo.mport_of_discharge_un limit 1) as mpod_timezone,
                    case when oo.transport_mode ='sea' 
                            then (select uncity from $order_from.ports where uncode = oo.mport_of_discharge_un limit 1)
                        else (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.mport_of_discharge_un limit 1)
                    end as mpod_uncity,
                    (select time_zone from public.city_timezone where uncode = oo.place_of_receipt_un limit 1) as por_timezone,
                    case when oo.transport_mode ='sea' 
                            then (select uncity from $order_from.ports where uncode = oo.place_of_receipt_un limit 1)
                        else (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.place_of_receipt_un limit 1)
                    end as por_uncity,
                    (select time_zone from public.city_timezone where uncode = oo.place_of_delivery_un limit 1) as pod_timezone,
                    case when oo.transport_mode ='sea' 
                            then (select uncity from $order_from.ports where uncode = oo.place_of_delivery_un limit 1)
                        else (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.place_of_delivery_un limit 1)
                    end as pod_uncity,
                    (select time_zone from public.city_timezone where uncode = oo.final_desination_uncode limit 1) as _fd_timezone,
                    case when oo.transport_mode ='sea' 
                            then (select uncity from $order_from.ports where uncode = oo.final_desination_uncode limit 1)
                        else (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.final_desination_uncode limit 1)
                    end as _pd_uncity,
                    CASE
                        WHEN ((m_iffbcf is not null or m_iffbcf is null) and m_iffcpu is null and m_iffrec is null and m_iffdep is null and m_iffarr is null and m_iffdel is null) THEN 'Created'::text
                        WHEN ((m_iffcpu is not null or m_iffrec is not null) and m_iffdep is null and m_iffarr is null and m_iffdel is null) THEN 'Cargo Received'::text
                        WHEN (m_iffdep is not null and m_iffarr is null and m_iffdel is null) THEN 'Departure'::text
                        WHEN (m_iffarr is not null and m_iffdel is null) THEN 'Arrived'::text
                        WHEN (m_iffdel is not null) THEN 'Completed'::text
                        ELSE 'Created'::text
                    END AS new_status
            from public.kln_ocean oo where oo.serial_no = '" . $serial_no . "' and oo.order_from = '$order_from'
        )
        SELECT  o.* ,sh.*, cn.*,aa.*,dd.*,fd.*
        from o
            LEFT JOIN LATERAL ( SELECT company as cn_company,
                address_1 as cn_address_1,
                address_2 as cn_address_2,
                address_3 as cn_address_3,
                address_4 as cn_address_4,
                city as cn_city, state as cn_state, zipcode as cn_zipcode, country as cn_country,
                phone_1 as cn_phone
            FROM $_schemas.contacts c WHERE o.consignee_id::text = c.contact_id::text) cn ON true
            LEFT JOIN LATERAL ( SELECT company as sh_company,
                address_1 as sh_address_1,
                address_2 as sh_address_2,
                address_3 as sh_address_3,
                address_4 as sh_address_4,
                city as sh_city, state as sh_state, zipcode as sh_zipcode, country as sh_country,
                phone_1 as sh_phone
            FROM $_schemas.contacts c WHERE o.shipper_id::text = c.contact_id::text) sh ON true
            LEFT JOIN LATERAL ( SELECT company as aa_company,
                address_1 as aa_address_1,
                address_2 as aa_address_2,
                address_3 as aa_address_3,
                address_4 as aa_address_4,
                city as aa_city, state as aa_state, zipcode as aa_zipcode, country as aa_country,
                phone_1 as aa_phone,
                (select time_zone from public.city_timezone where uncode = LEFT(c.country, 2) || COALESCE(c.city_code,'') limit 1) as aa_timezone
            FROM $_schemas.contacts c WHERE o.origin::text = c.contact_id::text) aa ON true
            LEFT JOIN LATERAL ( SELECT company as dd_company,
                address_1 as dd_address_1,
                address_2 as dd_address_2,
                address_3 as dd_address_3,
                address_4 as dd_address_4,
                city as dd_city, state as dd_state, zipcode as dd_zipcode, country as dd_country,
                phone_1 as dd_phone,
                (select time_zone from public.city_timezone where uncode = LEFT(c.country, 2) || COALESCE(c.city_code,'') limit 1) as dd_timezone
            FROM $_schemas.contacts c WHERE o.agent::text = c.contact_id::text) dd ON true
            LEFT JOIN LATERAL ( SELECT 
                city as fd_city,
                (select time_zone from public.city_timezone where uncode = LEFT(c.country, 2) || COALESCE(c.city_code,'') limit 1) as fd_timezone
            FROM $_schemas.contacts c WHERE o.final_desination::text = c.contact_id::text) fd ON true";
        return $sql;
    }

    public static function getDashboardTransportationSqlWhere(){
        $transportation = $_REQUEST["transportation"];
        if(!is_array($transportation)){
            $transportation = array($transportation);
        }
        $mode_param = "";
        if(utils::count($transportation) == 1 && strtolower($transportation[0]) == 'all' ){
            $mode_param = "'sea','air','road','rail'";         
        }else{
            $transport_mode = utils::implode(';', $transportation);
            $mode_param = common::getInNotInSqlForSearch($transport_mode);
        }
        return " and transport_mode_extend in ($mode_param)";
    }

    public static function getDashboardContactIDSqlWhere($specialType = ""){
        //$_REQUEST["customerCode"] = array("Al121212121","Al12121551");
        //$_REQUEST["customerType"] = array("customer_code","consignee_id");
        if(empty($_REQUEST["customerCode"]) 
            || empty($_REQUEST["customerType"])
            || common::isArrayAllEmpty($_REQUEST["customerCode"]) 
            || common::isArrayAllEmpty($_REQUEST["customerType"])){
            return "";
        }
        $contactId = $_REQUEST["customerCode"];
        if(!is_array($contactId)){
            $contactId = array($contactId);
        }
        $contactId = utils::implode(';', $contactId);

        //shipper_id  consignee_id   customer_code billto_id  notify_party_id
        $customerType = $_REQUEST["customerType"];
        if(!is_array($customerType)){
            $customerType = array($customerType);
        }

        $sqlWhere = " 1<>1 ";
        foreach($customerType as $value){
            //controlling_customer_code
            if ($value == 'customer_code' && $specialType == "container_bar") {
                $tempSql = " case when  order_from = 'public' 
                                then (exists(select 1 from public.ocean_extend ex where ex.serial_no = online_container.serial_no and lower(ex.customer_code) " . common::getInNotInSql($contactId)." limit 1))
                            when order_from = 'sfs' 
                                then (exists(select 1 from sfs.ocean_extend ex where ex.serial_no = online_container.serial_no and lower(ex.customer_code) " . common::getInNotInSql($contactId)." limit 1))
                            else 1<>1 end ";
                $sqlWhere .= " or ($tempSql)";
            } else {
                $sqlWhere .= " or lower(".$value.") ". common::getInNotInSql($contactId);
            }
        }
        return " and ($sqlWhere)";
    }


    /*
     *  Create Directory
    */
    public static function mkdirs($path, $mode = 0777) { //creates directory tree recursively
        if (!file_exists($path)) {
            if (!mkdir($path, $mode, TRUE))
                return FALSE;
        }
        return TRUE;
    }

    /**
     * checked is_subscribe
    */
    public static function checkedSubscribe($serial_no) { //creates directory tree recursively
        $is_subscribe_exist = common::excuteOneSql("select user_login from public.kln_user_subscribed us  
            where lower(us.user_login) = '".strtolower(_getLoginName())."' and us.subscribed_serial_no = '".$serial_no."'");
        if(empty($is_subscribe_exist)){
            $is_subscribe = false;
        }else{
            $is_subscribe = true;
        }
        return $is_subscribe;
    }

    /**
     * get webiste
    */
    public static function getWebiste($carrier) {
        $website = "";
        if (!empty($carrier)){
            $website = common::excuteOneSql("select website from vessel_company where scac = '$carrier'");
        }
        if (strpos($website, ";") === FALSE){
            
        }else{
            $site = explode(";", $website);
            $website = $site[0];
        } 
        return  $website; 
    }

    public static  function getWeek($week){
        if ($week == "0"){
            $week = "Sunday";
        } elseif ($week == "1"){
            $week = "Monday";
        } elseif ($week == "2"){
            $week = "Tuesday";
        } elseif ($week == "3"){
            $week = "Wednesday";
        } elseif ($week == "4"){
            $week = "Thursday";
        } elseif ($week == "5"){
            $week = "Friday";
        } elseif ($week == "6"){
            $week = "Sunday";
        }
        return $week;
    }

    /**
     * 这里基准event 写死, 根据online查询页面的通用的来, 这里需提问确定
    */
    public  static function getEDICtnrEvent(){
        $event =array(array("event_name"=>"EE","description"=>"Empty Equipment Dispatched"),
                array("event_name"=>"I","description"=>"Gate in full for a booking"),
                array("event_name"=>"AE","description"=>"Container loaded on vessel"),
                array("event_name"=>"VD","description"=>"Vessel Departure"),
                array("event_name"=>"VA_RELAY","description"=>"Arrive Relay Port"),
                array("event_name"=>"UV_RELAY","description"=>"Unloaded at Relay Port"),
                array("event_name"=>"AE_RELAY","description"=>"Loaded at Relay Port"),
                array("event_name"=>"VD_RELAY","description"=>"Depart Relay Port"),
                array("event_name"=>"CU","description"=>"Carrier and Customs Release"),
                array("event_name"=>"CT","description"=>"Customs release"),
                array("event_name"=>"CR","description"=>"Carrier release"),
                array("event_name"=>"VA","description"=>"Vessel Arrival"),
                array("event_name"=>"UV","description"=>"Unloaded From Vessel"),
                array("event_name"=>"AG","description"=>"Estimated Delivery"),
                array("event_name"=>"OA","description"=>"Gate out full from final discharge port"),
                array("event_name"=>"FT","description"=>"Free Time Expired"),
                array("event_name"=>"AL","description"=>"Container loaded on Rail"),
                array("event_name"=>"AR","description"=>"Container unloaded from Rail"),
                array("event_name"=>"AV","description"=>"Shipment available for pickup or delivery"),
                array("event_name"=>"D","description"=>"Gate out for delivery to customer"),
                array("event_name"=>"RD","description"=>"Container returned empty"),
                array("event_name"=>"C","description"=>"Vessel Estimated Time of Departure"),
                array("event_name"=>"C_RELAY","description"=>"Estimated Time of Departure from Tranship Port"),
                array("event_name"=>"AG_DES","description"=>"Estimated Delivery Destination"),
                array("event_name"=>"IFFADW","description"=>"Shipment in CFS warehouse"),
                array("event_name"=>"IFFDDW","description"=>"Shipment Out from CFS House"));
        return  $event;   
    }

    /**
     * 处理daily 和 week 不同周或者天的数据分组
    */
    public  static function handleDailyWeekedData($notificationsArr){
        $uniqe = array();
        $ret = array();
        foreach($notificationsArr as $info){
            $uniqe_group_str = $info['insert_date_format'];
            if(utils::in_array($uniqe_group_str,$uniqe)){
                $tempArr = $ret[$uniqe_group_str];
                $tempArr[] = $info;
                $ret[$uniqe_group_str] = $tempArr;
            } else {
                $ret[$uniqe_group_str] = array($info);
                $uniqe[] = $uniqe_group_str;
            }
        }
        return $ret;
    }
    /**
     *  /unit=="Day(s)" ? "days":"hours";
    */
    public static function convertoVue($unit){
        if(empty($unit))
            return $unit;
        if ($unit == "days"){
            return "Day(s)";
        }else{
            return "Hour(s)";
        }
    }

    /**
     * vue to int
     */
    public static function convertoint($num){
        if($num == "0"){
            return intval($num);
        }
        return !empty($num) ? intval($num): $num;
    }

    //检查user name length
    public static function checkUserNameLength($input) {
        if(!empty($input) && strlen($input) > 50){
            $data = array(
                'code' => 'no_exist',
                'login_version' => '',
                'msg' => 'The username or password you entered is incorrect'
            );
            common::echo_json_encode(500, $data);
            exit();
        }
    }

    public static function checkInputInval($input) { 
        $data = true;       
        // 定义危险 SQL 关键字列表
        $dangerousKeywords = [
           'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'DROP', 'ALTER', 'CREATE', 
           'UNION', 'EXEC', 'EXECUTE', 'TRUNCATE', 'GRANT', 'REVOKE', 
           'WAITFOR', 'DELAY', 'PG_SLEEP', '--', ';'
        ];
       
        // 检查是否包含危险关键字（不区分大小写）
        foreach ($dangerousKeywords as $keyword) {
            if (stripos($input, $keyword) !== false) {
                $data = false;
            }
        }
       
        // 检查是否包含 SQL 注入常见模式
        if (preg_match('/\b(OR|AND)\s+1\s*=\s*1\b/i', $input) || 
           preg_match('/\bUNION\s+SELECT\b/i', $input)) {
            $data = false;
        }
        if(!empty($input) && strlen($input) > 50){
            $data = false;
        }
        return $data;
    }

    public static function checkSafeSql($input) { 
        $data = true;       
        // 定义危险 SQL 关键字列表
        $dangerousKeywords = [
           'INSERT', 'UPDATE', 'DELETE', 'DROP', 'ALTER', 'CREATE', 
           'UNION', 'EXEC', 'EXECUTE', 'TRUNCATE', 'GRANT', 'REVOKE', 
           'WAITFOR', 'DELAY', 'PG_SLEEP'
        ];
       
        // 检查是否包含危险关键字（不区分大小写）
        foreach ($dangerousKeywords as $keyword) {
            $pattern = '/\b' . preg_quote($keyword, '/') . '\b/i'; // \b 表示单词边界
            if (preg_match($pattern, $input)) {
                $data = false;
                break; // 提高性能，发现一个就返回
            }
        }
       
        // 可选：进一步确认是否为 SELECT 查询
        if (!preg_match('/^\s*SELECT\b/i', trim($input))) {
           $data = false; // 不是以 SELECT 开头
        }

        // 转为小写统一处理
        $sqlLower = strtolower($input);
        // 查找 'from' 出现的位置
        $fromPos = stripos($sqlLower, 'from');
        if ($fromPos === false) {
            return false; // 没有 from，不可能有 where，直接返回 false
        }

        // 从 from 之后的部分中查找 where
        $afterFrom = substr($sqlLower, $fromPos + 4); // +4 是跳过 'from'
        if (stripos($afterFrom, 'WHERE') === false && stripos($afterFrom, 'limit') === false){
            $data = false; // 简单的判断是否带条件查询
        }

        return $data;
    }

    //预加载写法
    public static function excuteObjectPrepareSql($sql,$param) {
        if (empty($sql))
            exit(json_encode("Program encountered an error."));
        global $db;
        $stmt = $db->Prepare($sql);
        $result = $db->Execute($stmt, $param);
        if ($result && $result->RecordCount() > 0) { 
            $row = $result->fields;
            return $row;
        }
        return  null;
    }

    //拼接换行
    public static function splicedLlineBreaks() {
       return "
       ";
    }

    /**
     * chat获取自然序列号
    */
    public static function getChatAiSequence(){
        $sequence = common::excuteOneSql("select count(*) from kln_robot_chat_log where request_time >= CURRENT_DATE");
        $sequence = empty($sequence)? "1" : $sequence + 1;
        if($sequence >99999){
            return $sequence;
        }
        $sequence = sprintf("%05d", $sequence);
        return $sequence;
    }

    /**
     * Delivery Booking No获取自然序列号
    */
    public static function getDeliveryBookingSequence(){
        $sequence = common::excuteOneSql("select public.get_booking_no()");
        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'] = '<a href="'.$httpUrl.'" target="_blank">Details</a> <a href="'.SERVER_Vue_PAHT.'SystemSettings" target="_blank">Notify</a>';
                }
            }
            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'] = '<a href="'.$httpUrl.'" target="_blank">Details</a> <a href="'.SERVER_Vue_PAHT.'SystemSettings" target="_blank">Notify</a>';
                    $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 <a href="'.$httpUrl.'" target="_blank">Download Complete</a> 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'] = '<a href="'.$httpUrl.'" target="_blank">Details</a> <a href="'.SERVER_Vue_PAHT.'SystemSettings" target="_blank">Notify</a>';
                $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 <a href="'.$httpUrl.'" target="_blank">Download Complete</a> 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'] = '<a href="'.$httpUrl.'" target="_blank">Details</a> <a href="'.SERVER_Vue_PAHT.'SystemSettings" target="_blank">Notify</a>';
                $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 <a href="'.$httpUrl.'" target="_blank">Download Complete</a> 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('<h_bol>', 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'] = '<a href="'.$httpUrl.'" target="_blank">Track Shipment</a>';
            $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'] = '<a href="'.$httpUrl.'" target="_blank">Details</a> <a href="'.SERVER_Vue_PAHT.'SystemSettings" target="_blank">Notify</a>';
                $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 <a href="'.$httpUrl.'" target="_blank">Download Complete</a> 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'] = '<a href="'.$httpUrl.'" target="_blank">Details</a> <a href="'.SERVER_Vue_PAHT.'SystemSettings" target="_blank">Notify</a>';
                $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 <a href="'.$httpUrl.'" target="_blank">Download Complete</a> 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'] = '<a href="'.$httpUrl.'" target="_blank">Shipment Detail Page Link</a>';
            }
            $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'] = '<a href="'.$httpUrl.'" target="_blank">Details</a> <a href="'.SERVER_Vue_PAHT.'SystemSettings" target="_blank">Notify</a>';
                $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 <a href="'.$httpUrl.'" target="_blank">Download Complete</a> button.';
                $total = array("Download_Complete" =>$download_complete);
            }
            $reference = utils::replacementsFixed($total,$reference,array("Download_Complete"));
        }

        if($fixedChat["fixed_faq"] == "Set up automatic notifications for shipment updates." 
            || $fixedChat["fixed_faq"] == "How can I view the detailed shipping route of my package?" ){
            $reference = $fixedChat["answer_style"];
        }

        error_log($reference);
        return $reference; 
    }

    public static function dealDateTime($date,$time,$zone,$format){
        if(empty($date)){
            return "";
        }
        //处理掉多余空格
        if(!empty($time)){
            $time_str = " ".$time;
        } else {
            $time_str = "";
        }
        if(!empty($date) && !empty($zone)){
            $utc = common::getUTCOffsetFormatted($zone);
            return date($format, strtotime($date)).$time_str." UTC".$utc;
        }
        return date($format, strtotime($date)).$time_str;
    }
    
    public static function getUTCOffsetFormatted($timezone) {
        $timezone = new DateTimeZone($timezone);
        $now = new DateTime('now', $timezone);
        $offset = $timezone->getOffset($now);
        $hours =  floor($offset / 3600 * 10) / 10;
        if( $hours > 0 ){
           $hours = "+".$hours; 
        }
        return $hours;
    }

    /**
     * 获取固定问题的查询sql
    */
    public static function getFaqSql($faq){
        $sqlObj = common::excuteObjectSql("select fixed_sql from public.kln_robot_chat_fixed where fixed_faq = '$faq'");
        $fixed_sql = $sqlObj['fixed_sql'];

        $sqlDetail = '';
        $sqlArr = explode(";", $fixed_sql);
        if(count($sqlArr) > 1){
            $sqlDetail = $sqlArr[1];
            $search = 'limit 10';
            $sqlDetail = str_ireplace($search, '', $sqlDetail);
        }
        return $sqlDetail;
    }

    /**
     * 获取固定问题列
    */
    public static function getFaqColumnSql($faq){
         //返回格式相同的放到一起处理
        if($faq == "Shipments arriving in the next 7 days."){
            // | HBL/HAWB No. | Origin           | Destination      | Latest Milestone | ETA       | Cargo Type    | Actions          |
            // |--------------|------------------|------------------|------------------|-----------|---------------|------------------|
            // | {{h_bol}} | {{place_of_receipt_exp}} | {{place_of_delivery_exp}} | {{description}} | {{eta}} | {{cargo_type}} | {{action}} |
            $columns = array();
            $columns[] = array("display_name" => "HBL/HAWB No.","database_column_name"=>"h_bol");
            $columns[] = array("display_name" => "Origin","database_column_name"=>"place_of_receipt_exp");
            $columns[] = array("display_name" => "Destination","database_column_name"=>"place_of_delivery_exp");
            $columns[] = array("display_name" => "Latest Milestone","database_column_name"=>"description");
            $columns[] = array("display_name" => "ETA","database_column_name"=>"eta");
            $columns[] = array("display_name" => "Cargo Type","database_column_name"=>"cargo_type");
        }

        if($faq == "Show shipments delayed in the last 30 days."){
            // | HBL/HAWB No. | Origin           | Destination      | Latest Milestone | ETA       | Cargo Type    | Actions          |
            // |--------------|------------------|------------------|------------------|-----------|---------------|------------------|
            // | {{h_bol}} | {{place_of_receipt_exp}} | {{place_of_delivery_exp}} | {{description}} | {{eta}} | {{cargo_type}} | {{action}} |
            $columns = array();
            $columns[] = array("display_name" => "HBL/HAWB No.","database_column_name"=>"h_bol");
            $columns[] = array("display_name" => "Origin","database_column_name"=>"place_of_receipt_exp");
            $columns[] = array("display_name" => "Destination","database_column_name"=>"place_of_delivery_exp");
            $columns[] = array("display_name" => "Delayed Type","database_column_name"=>"log_type");
            $columns[] = array("display_name" => "Planned Date","database_column_name"=>"planned_time");
            $columns[] = array("display_name" => "Actual/Revised Date","database_column_name"=>"actual_time");
            $columns[] = array("display_name" => "Delay Duration","database_column_name"=>"duration");
        }

        if($faq == "List shipments with milestone updates in the last 7 days."){
            // | HBL/HAWB No. | Latest Milestone           | Time                         | Location                  | Actions          |
            // |--------------|----------------------------|------------------------------|---------------------------|------------------|
            // | {{h_bol}} | {{description}} | {{update_date}} | {{locations}} | {{action}} |
            $columns = array();
            $columns[] = array("display_name" => "HBL/HAWB No.","database_column_name"=>"h_bol");
            $columns[] = array("display_name" => "Latest Milestone","database_column_name"=>"description");
            $columns[] = array("display_name" => "Time","database_column_name"=>"update_date");
            $columns[] = array("display_name" => "Location","database_column_name"=>"locations");
        }

        if($faq == "What is the current status of my active shipments?"){
            // | HBL/HAWB No. | Origin           | Destination      | Latest Milestone           | Time                         | Location                  | Cargo Type    | Actions          |
            // |--------------|------------------|------------------|----------------------------|------------------------------|---------------------------|---------------|------------------|
            // | {{h_bol}} | {{place_of_receipt_exp}} | {{place_of_delivery_exp}} | {{description}} | {{time}} | {{locations}} | {{cargo_type}} | {{action}} |
            $columns = array();
            $columns[] = array("display_name" => "HBL/HAWB No.","database_column_name"=>"h_bol");
            $columns[] = array("display_name" => "Origin","database_column_name"=>"place_of_receipt_exp");
            $columns[] = array("display_name" => "Destination","database_column_name"=>"place_of_delivery_exp");
            $columns[] = array("display_name" => "Latest Milestone","database_column_name"=>"description");
            $columns[] = array("display_name" => "Time","database_column_name"=>"time");
            $columns[] = array("display_name" => "Location","database_column_name"=>"locations");
            $columns[] = array("display_name" => "Cargo Type","database_column_name"=>"cargo_type");
        }

        if($faq == "List shipments with container status updates in the last 7 days."){
            // | Container No. | Container Status           | Time                         | Location                  | Actions          |
            // |---------------|----------------------------|------------------------------|---------------------------|------------------|
            // | {{container_no}} | {{description}} | {{time}} | {{uncity}} | {{action}} | 
            $columns = array();
            $columns[] = array("display_name" => "Container No.","database_column_name"=>"container_no");
            $columns[] = array("display_name" => "Container Status","database_column_name"=>"description");
            $columns[] = array("display_name" => "Time","database_column_name"=>"time");
            $columns[] = array("display_name" => "Location","database_column_name"=>"uncity");
        }

        if($faq == "Today's shipments summary."){
            // | HBL/HAWB No. | Mode | Origin           | Destination      | Action Type | Action Time             | Action Location | Actions          |
            // |--------------|------|------------------|------------------|-------------|-------------------------|-----------------|------------------|
            // | {{h_bol}} | {{transport_mode}} | {{place_of_receipt_exp}} | {{place_of_delivery_exp}} | {{action_type}} | {{time}} | {{locations}} | {{action}} |
            $columns = array();
            $columns[] = array("display_name" => "HBL/HAWB No.","database_column_name"=>"h_bol");
            $columns[] = array("display_name" => "Mode","database_column_name"=>"transport_mode");
            $columns[] = array("display_name" => "Origin","database_column_name"=>"place_of_receipt_exp");
            $columns[] = array("display_name" => "Destination","database_column_name"=>"place_of_delivery_exp");
            $columns[] = array("display_name" => "Action Type","database_column_name"=>"action_type");
            $columns[] = array("display_name" => "Action Time","database_column_name"=>"time");
            $columns[] = array("display_name" => "Action Location","database_column_name"=>"locations");
        }

        if($faq == "Sort my active shipments by earliest arrival date."){
            // | ETA    | HBOL/HAWB No. | Mode  | Origin           | Destination      | Days to Arrival | Actions          |
            // |--------|---------------|-------|------------------|------------------|-----------------|------------------|
            // | {{eta}} | {{h_bol}} | {{transport_mode}} | {{place_of_receipt_exp}} | {{place_of_delivery_exp}} | {{day_to_arr}} | {{action}} |
            $columns = array();
            $columns[] = array("display_name" => "ETA","database_column_name"=>"eta");
            $columns[] = array("display_name" => "HBL/HAWB No.","database_column_name"=>"h_bol");
            $columns[] = array("display_name" => "Mode","database_column_name"=>"transport_mode");
            $columns[] = array("display_name" => "Origin","database_column_name"=>"place_of_receipt_exp");
            $columns[] = array("display_name" => "Destination","database_column_name"=>"place_of_delivery_exp");
            $columns[] = array("display_name" => "Days to Arrival","database_column_name"=>"day_to_arr");
        }
        return $columns;
    }

    /**
     * 处理数据问题
    */
    public static function dealDataWithFaq($faq,$data){
        $refer_data  = array();
        //返回格式相同的放到一起处理
        if($faq == "Shipments arriving in the next 7 days."){
            $refer_data = $data;
        }

        if($faq == "Show shipments delayed in the last 30 days."){
            foreach($data as $key =>$d){
                $temp = array();
                $temp['h_bol'] = $d['h_bol'];
                $temp['place_of_receipt_exp'] = $d['place_of_receipt_exp'];
                $temp['place_of_delivery_exp'] = $d['place_of_delivery_exp'];
                $temp['log_type'] = $d['log_type'];
                $temp['planned_time'] = common::dealDateTime($d['event_old_date'],$d['event_old_time'],'',"m/d/Y");
                $temp['actual_time'] = common::dealDateTime($d['event_date'],$d['event_time'],'',"m/d/Y");
                $temp['duration'] = $d['duration'] <= 0 ? '< 1 Days' : $d['duration'].' Days';
                $refer_data[] = $temp;
            }
        }

        if($faq == "List shipments with milestone updates in the last 7 days."){
           foreach($data as $key =>$d){
                $temp = array();
                $temp['h_bol'] = $d['h_bol'];
                $temp['description'] = $d['description'];
                $temp['update_date'] = common::dealDateTime($d['update_date'],"",$d['timezone'],"m/d/Y H:i:s");
                $temp['locations'] = $d['locations'];
                $refer_data[] = $temp;
            }
        }

        if($faq == "What is the current status of my active shipments?"){
           foreach($data as $key => $d){
                $temp = array();
                $temp['h_bol'] = $d['h_bol'];
                $temp['place_of_receipt_exp'] = $d['place_of_receipt_exp'];
                $temp['place_of_delivery_exp'] = $d['place_of_delivery_exp'];
                $temp['description'] = $d['description'];
                $temp['time'] = common::dealDateTime($d['act_date'],$d['act_time'],$d['timezone'],"m/d/Y");
                $temp['locations'] = $d['locations'];
                $temp['cargo_type'] = $d['cargo_type'];
                $refer_data[] = $temp;
            }
        }

        if($faq == "List shipments with container status updates in the last 7 days."){
            foreach($data as $key =>$d){
                $temp = array();
                $temp['container_no'] = $d['container_no'];
                $temp['description'] = $d['description'];
                $temp['time'] = common::dealDateTime($d['eventdate'],$d['eventtime'],$d['timezone'],"m/d/Y");
                $temp['uncity'] = $d['uncity'];
                $refer_data[] = $temp;
            }
        }

        if($faq == "Today's shipments summary."){
            foreach($data as $key =>$d){
                $temp = array();
                $temp['h_bol'] = $d['h_bol'];
                $temp['transport_mode'] = $d['transport_mode'];
                $temp['place_of_receipt_exp'] = $d['place_of_receipt_exp'];
                $temp['place_of_delivery_exp'] = $d['place_of_delivery_exp'];
                $temp['action_type'] = $d['action_type'];
                $temp['time'] = common::dealDateTime($d['act_date'],$d['act_time'],$d['timezone'],"m/d/Y");
                $temp['locations'] = $d['locations'];
                $refer_data[] = $temp;
            }
        }

        if($faq == "Sort my active shipments by earliest arrival date."){
            foreach($data as $key =>$d){
                $temp = array();
                $temp['eta'] = $d['eta'];
                $temp['h_bol'] = $d['h_bol'];
                $temp['transport_mode'] = $d['transport_mode'];
                $temp['place_of_receipt_exp'] = $d['place_of_receipt_exp'];
                $temp['place_of_delivery_exp'] = $d['place_of_delivery_exp'];
                $temp['day_to_arr'] = $d['day_to_arr'];
                $refer_data[] = $temp;
            }
        }
        return $refer_data;
    }

    public static function destination_delivery_window_radio($radio){
        if($radio == "No_Restrictions"){
            return 1;
        }
        if($radio == "Restrictions_ETD_ATD"){
            return 2;
        }
        if($radio == "Restrictions_ETA_ATA"){
            return 3;
        }
    }

    public static function destination_delivery_recommended($radio){
        if($radio == "No_Recommended"){
            return 1;
        }
        if($radio == "Delivery_ETA_ATA"){
            return 2;
        }
    }

    public static function checkOverlap($post_arr,$name,$startIndex = 0) {
        $errors = "";
        $num = count($post_arr);
        //$_POST['rule_type'];
        for ($i = 0; $i < $num; $i++) {
            for ($j = $i + 1; $j < $num; $j++) {
                $set1 = $post_arr[$i] ?? [];
                $set2 = $post_arr[$j] ?? [];
                
                $intersection = common::array_intersect_own($set1, $set2,$_POST['rule_type'][$i+$startIndex]);
                $intersectionC = "";
                //这个特殊。如果ports 存在一样的，那检查carrier 是否存在一样的。
                if ($name == "Sea Rule") {
                    $setC1 = explode(",",$_POST['carrier'][$i+$startIndex]) ?? [];
                    $setC2 = explode(",",$_POST['carrier'][$j+$startIndex]) ?? [];
                    
                    $intersectionC = array_intersect($setC1, $setC2);
                    if (!empty($intersection) && !empty($intersectionC)) {
                        $errors = "$name " . ($i + 1) . " and $name " . ($j + 1) . " have overlapping";
                        if (!empty($intersection)) {
                            $errors .= " ports: " . implode(', ', $intersection);
                        }
                        if (!empty($intersectionC)) {
                            $errors .= " carrier: " . implode(', ', $intersectionC);
                        }
                    }
                    
                } else {
                    if (!empty($intersection)) {
                        $errors = "$name " . ($i + 1) . " and $name " . ($j + 1) . " have overlapping ports: " . implode(', ', $intersection);
                    }
                }

                
            }
        }
        return $errors;
    }

    public static function array_intersect_own($set1, $set2){
        $intersection = array_intersect($set1, $set2);

        //这个是无效的$set1是数组，不是字符串。
        // if ($rule_type <> '*Default Rule' && ($set1 == 'ALL' || $set2 == 'ALL')){
        //     $intersection = "ALL";
        // }
        return $intersection;
    }

    public static function sendDestinationDeliveryReminder($data,$shipmentsData,$status){
        $html_tr = "";
        $dest_op_arr = array();
        foreach($shipmentsData['data'] as $shipment){
            // $html_tr .="<tr style=\"color: #ED6D00;\">
            //     <td align=\"center\" style=\" width: 162px;padding: 10px;word-wrap: break-word; word-break: break-all; white-space: normal;\">".$shipment['h_bol']."</td>
            //     <td align=\"center\" style=\" width: 162px;padding: 10px;word-wrap: break-word; word-break: break-all; white-space: normal;\">".$shipment['ctnr']."</td>
            //     <td align=\"center\" style=\" width: 162px;padding: 10px;word-wrap: break-word; word-break: break-all; white-space: normal;\">".$shipment['package_type']."</td>
            //     <td align=\"center\" style=\" width: 162px;padding: 10px;word-wrap: break-word; word-break: break-all; white-space: normal;\">".$shipment['kgw']."</td>
            // </tr>";
            $html_tr .='<tr style="color: #ED6D00;">
                <td align="center"
                    style=" width: 162px;padding: 10px 6px;word-wrap: break-word; word-break: break-all; white-space: normal;">'.$shipment['h_bol'].'</td>
                <td align="center"
                    style=" width: 162px;padding: 10px 6px;word-wrap: break-word; word-break: break-all; white-space: normal;">'.$shipment['ctnr'].'</td>
                <td align="center"
                    style=" width: 162px;padding: 10px 6px;word-wrap: break-word; word-break: break-all; white-space: normal;">'.$shipment['po_no'].'</td>
                <td align="center"
                    style=" width: 162px;padding: 10px 6px;word-wrap: break-word; word-break: break-all; white-space: normal;">'.$shipment['service'].'</td>
                <td align="center"
                    style=" width: 162px;padding: 10px 6px;word-wrap: break-word; word-break: break-all; white-space: normal;">'.$shipment['package_type'].'</td>
                <td align="center"
                    style=" width: 162px;padding: 10px 6px;word-wrap: break-word; word-break: break-all; white-space: normal;">'.$shipment['kgw'].'</td>
             </tr>';
            if(!empty($shipment['dest_op'])){
                $dest_op_arr[] = $shipment['dest_op'];
            }
        }

        //{{consignee_name}}
        
        $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 = '<a href="https://online-beta.kln.com/" style="color: #ED6D00; font-size: 14px;">https://online-beta.kln.com/</a>';
        }
        if ($status == "Approve") {
            $links = '<a href="https://online-beta.kln.com/" style="color: #ED6D00; font-size: 14px;">https://online-beta.kln.com/</a>';
        }

        //邮件模板填充字段准备
        //empty($address['contact_person']) ? "" : "Contact: ".$address['contact_person']."(".$address['contact_number'].")"
        $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"=>$address['address_1'],
            "address_1"=>$data['delivery_address'],
            "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,
            "consignee_name" =>$data['consignee'],
            "special_requirements" =>$data['special_requirements'],
            "html_tr" =>$html_tr);
    
        $contents = common::excuteObjectSql("select subject, ra_content as content from ra_online_email_tpl where ra_type = 'Delivery_".$status."'");
        //检查type长度
        $report_type = "delivery_email";
        $report_type = strlen($report_type) > 20 ?  substr($report_type, 0, 20) : $report_type;
        $subject = common::check_input($contents['subject']);
        $email_from = "US.KApex.Online@kerryapex.com";
        $to_email = $customer_email;
        $cc_email = $kln_pic_email;
        if(!empty($dest_op_arr)){
            $dest_op_arr = array_values(array_unique($dest_op_arr));
            $more_param = common::getInNotInSqlForSearch(strtolower(utils::implode(';',$dest_op_arr)));
            $destOPEmail = common::excuteObjectSql("select string_agg(email,';') as email from employee where COALESCE(email,'') <> '' and  lower(employee_id) in (".$more_param.")");
            if(!empty($destOPEmail['email'])){
                $cc_email = $cc_email.";".$destOPEmail['email'];
            }
        }
        
        error_log($contents['content']);
        
        // 动态构建替换数组（格式：[key] => value）
        $replacements = [];
        foreach ($tplData as $key => $value) {
            $replacements["{{$key}}"] = $value; 
            $replacements["{{{$key}}}"] = $value; 
        }
        $content = strtr($contents['content'], $replacements);
        $content = common::check_input($content);
        //error_log($content);

        $sql = "INSERT INTO email_record(type, title, from_email, to_email, content, insert_date, cc_email, attachment_path)
            VALUES ('" . $report_type . "', '" . $subject . "', '" . $email_from . "', '".$to_email . "', '" . $content . "', now(), '" . $cc_email . "', '');";
        if(empty($contents) || empty($to_email)){
            return "";
        }    
        return $sql;
    }

    public static function deliveryStatusConvert($status){
        if ($status == 'Approve'){
            return 'Approved';
        }
        if ($status == 'Reject'){
            return 'Rejected';
        }
        if ($status == 'Cancel'){
            return 'Cancelled';
        }
        return $status;
    }

    public static function customizeParsing ($rawString){
        // 您提供的字符串
        // $rawString = '{ "can_query": false, "query_type": "", "reason": "用户询问是否有其他联系邮箱，因为support@kln.com邮箱返回无法送达。这个问题无法通过数据表查询得到答案，因为表中不包含KLN公司的联系邮箱信息。", "sql": "", "reference": "", "response": "I understand that you\'re having trouble with the email address support@kln.com returning as undeliverable. For customer support inquiries, please use our official customer service email: customer.service@kln.com or alternatively, you can contact our global service desk at global.service@kln.com.
        
        // You can also reach us through our customer portal at https://www.kln.com/contact-us or call our customer service hotline at +852 2796 6666.
        
        // We apologize for any inconvenience and are happy to assist you with your logistics needs." }';

        // 要提取的键
        $keysToExtract = ['can_query', 'sql', 'reference', 'response'];

        // 存储结果的数组
        $extractedValues = [];

        foreach ($keysToExtract as $key) {
            $fullKey = '"' . $key . '"'; // 构建完整的键，如 "can_query"
            $keyPos = strpos($rawString, $fullKey);
            
            if ($keyPos === false) {
                $extractedValues[$key] = null; // 键未找到
                continue;
            }
            
            // 找到键后，定位冒号 :
            $colonPos = strpos($rawString, ':', $keyPos + strlen($fullKey));
            if ($colonPos === false) {
                $extractedValues[$key] = null; // 冒号未找到
                continue;
            }
            
            // 跳过冒号和可能的空白，定位值的开始
            $valueStart = $colonPos + 1;
            while ($valueStart < strlen($rawString) && ctype_space($rawString[$valueStart])) {
                $valueStart++;
            }
            
            if ($valueStart >= strlen($rawString)) {
                $extractedValues[$key] = null; // 值开始位置越界
                continue;
            }
            
            $value = '';
            $char = $rawString[$valueStart];
            
            // 情况1: 值以双引号开头 (字符串)
            if ($char === '"') {
                // 从 valueStart+1 开始寻找结束的双引号，需要处理转义
                $pos = $valueStart + 1;
                $strValue = '';
                $escaped = false;
                
                while ($pos < strlen($rawString)) {
                    $currentChar = $rawString[$pos];
                    if ($escaped) {
                        // 处理转义字符，简单处理常见情况
                        switch ($currentChar) {
                            case 'n': $strValue .= "\n"; break;
                            case 't': $strValue .= "\t"; break;
                            case 'r': $strValue .= "\r"; break;
                            case '"': $strValue .= '"'; break;
                            case '\\': $strValue .= '\\'; break;
                            default: $strValue .= $currentChar; break; // 其他转义，原样保留？
                        }
                        $escaped = false;
                    } else {
                        if ($currentChar === '\\') {
                            $escaped = true;
                        } elseif ($currentChar === '"') {
                            // 找到了未转义的结束引号
                            break;
                        } else {
                            $strValue .= $currentChar;
                        }
                    }
                    $pos++;
                }
                
                // 如果找到了结束引号，$strValue 就是解码后的字符串
                // 如果没找到（pos >= strlen），说明字符串没闭合，这里按找到的处理
                $value = trim($strValue);
                
            } 
            // 情况2: 值不是以双引号开头 (false, true, null, "", 数字等)
            else {
                // 寻找值的结束位置：遇到逗号,、右花括号}、右括号] 或空白序列（如果后面是分隔符）
                $pos = $valueStart;
                $literalValue = '';
                
                while ($pos < strlen($rawString)) {
                    $currentChar = $rawString[$pos];
                    // 检查是否到了值的边界
                    if ($currentChar === ',' || $currentChar === '}' || $currentChar === ']') {
                        break; // 遇到分隔符，值结束
                    }
                    // 如果遇到空白，检查后面的字符是否是分隔符或空白，如果是，则值可能结束
                    if (ctype_space($currentChar)) {
                        // 查看下一个非空白字符
                        $nextPos = $pos + 1;
                        while ($nextPos < strlen($rawString) && ctype_space($rawString[$nextPos])) {
                            $nextPos++;
                        }
                        if ($nextPos >= strlen($rawString) || $rawString[$nextPos] === ',' || $rawString[$nextPos] === '}' || $rawString[$nextPos] === ']') {
                            break; // 后面是分隔符或结束，当前空白是值的结束
                        }
                        // 否则，空白是值的一部分？(通常字面量不含内部空白)
                    }
                    // 对于字面量，我们通常认为它不包含内部空白，所以遇到空白且后面不是分隔符时，可能也该停止？
                    // 但为了简单，我们主要依赖 , } ] 分隔符。
                    // 追加当前字符
                    $literalValue .= $currentChar;
                    $pos++;
                }
                
                // $literalValue 现在包含从 valueStart 到分隔符前的所有字符
                // 但可能包含尾部空白，trim 一下
                $value = trim($literalValue);
                
                // 特别处理空字符串字面量 ""
                // 如果原始字符串在 valueStart 位置是 "，但我们上面的 else 分支没进，所以不会到这里
                // 如果原始是 "", 它会被上面的 else 分支捕获为字符串 '""'，但我们希望它的值是空字符串 ''
                // 所以需要检查 $value 是否等于 '""'
                if ($value === '""') {
                    $value = '';
                }
                // 注意：在您的例子中，"sql": "" 和 "reference": "" 在 JSON 中是空字符串，但在 else 分支会被识别为 '""'
                // 我们在这里统一处理。
            }
            
            $extractedValues[$key] = $value;
        }
        return $extractedValues;
    }

    public static function secondaryReplacement($brokenJson){
        // 用 \n 替换实际换行符 '/"\w+"\s*:\s*"\K[^"]*(?=")/s'  这种如果你字段名不是简单的 \w+（比如有 -、中文等），可以用：
        $fixedJson = preg_replace_callback(
            '/"\s*:\s*"\K[^"]*(?=")/s',
            function ($matches) {
                $text =$matches[0];
                return str_replace(["\r\n", "\n", "\r"], "\\n", $text); 
                //return str_replace("\n", "\\n", $matches[0])
                //return str_replace(["\r\n", "\n", "\r"], "\\n", $text);
            },
            $brokenJson
        );
        return $fixedJson;
    }

    /**
     * 客户和 内部员工在不同status下。运行的操作限制
     */
    public static function checkedActionLegal($serial_no,$operate){
        $is_employee = _isApexLogin();
        $status = common::excuteOneSql("select status from public.kln_destination_delivery where serial_no = '$serial_no'");

        $legal = false;
        if ($status == 'Pending Approval'){
            if ($is_employee){
                //Pending Approval 状态下 内部  能审核，拒绝，和看详细
                if ($operate == "review" && ($_POST['status'] == "Approve" || $_POST['status'] == "Reject")){
                    $legal = true;
                }
                if ($operate == "view_detail"){
                    $legal = true;
                }
            } else {
                //Pending Approval 状态下 客户的  能取消，看详细，和编辑
                if ($operate == "review" && $_POST['status'] == "Cancel"){
                    $legal = true;
                }
                if ($operate == "view_detail"){
                    $legal = true;
                }   
                if ($operate == "add"){
                    $legal = true;
                }
            }
        } else if ($status == 'Approve'){
            if ($is_employee){
                //Approve 下 内部的能看详细
                if ($operate == "view_detail"){
                    $legal = true;
                }
            } else {
                //Approve 下 客户的 能发邮件留言，能看详细
                if ($operate == "email_message_board"){
                    $legal = true;
                }
                if ($operate == "view_detail"){
                    $legal = true;
                }
            }
        } else if ($status == 'Reject'){
            if ($is_employee){
                //Reject 下 员工 看详细
                if ($operate == "view_detail"){
                    $legal = true;
                }
            } else {
                //Reject 下 客户 能编辑 看详细
                if ($operate == "add"){
                    $legal = true;
                }
                if ($operate == "view_detail"){
                    $legal = true;
                }
            }
        } else if ($status == 'Cancel'){
            //不分账户类型，只能看详细
            if ($operate == "view_detail"){
                $legal = true;
            }
        }

        //固定
        //$legal = true;
        if(!$legal){
            $data = array("msg" =>"Illegal access");
            common::echo_json_encode(200,$data); 
            exit();
        }
    }

    public static function saveDAddressTempTable($delivery_serial_no,$consignee_id){
        //deliver address is new
        $addressSql = "";
        $_sync_key_add = "";
        $address_1 = $_POST['address_1'];
        foreach($address_1 as $key => $_address_1){
            $_address_1 = common::check_input($_address_1); 
            $_address_2 = common::check_input($_POST['address_2'][$key]);
            $_address_3 = common::check_input($_POST['address_3'][$key]);
            $_address_4 = common::check_input($_POST['address_4'][$key]); 
            $_country = common::check_input($_POST['country'][$key]); 
            $_city = common::check_input($_POST['city'][$key]); 
            $_postal_code = common::check_input($_POST['postal_code'][$key]); 
            $_contact_person = common::check_input($_POST['contact_person'][$key]); 
            $_contact_number = common::check_input($_POST['contact_number'][$key]); 

            $_contact_id = common::check_input($_POST['contact_id'][$key]);
            $_sync_key = common::check_input($_POST['sync_key'][$key]);
            $_from_station = common::check_input($_POST['from_station'][$key]);
            $_contact_type = common::check_input($_POST['contact_type'][$key]);
            $_create_user = common::check_input($_POST['create_user'][$key]);

            //为空的代表新加  _contact_type在非Unedit 和  Delete时，判断新加前端有bug,  先添加，立即修改。状态会从add  变成 moidfy,这个是有问题。
            if (empty($_sync_key)){
                $uniqueAC =array();
                foreach($consignee_id as $ck => $_consignee_id){
                    $_add_create_user = "Online_D_Address";
                    if(utils::in_array($_consignee_id,$uniqueAC)){
                        continue;
                    }
                    $uniqueAC[] = $_consignee_id;

                    $_sync_key = common::uuid();
                    $_from_station = "";
                    $addressSql .= "INSERT INTO public.contacts_address_online(
                            delivery_serial_no,action,
                            addr_type,contact_id, addr1, addr2,addr3, addr4, 
                            ctry_code, city_code, postal_code,  
                            create_user, create_date, modify_user, modify_date,
                            is_sync_country, sync_key, from_station,attn,tel)
                    VALUES ('$delivery_serial_no','$_contact_type',
                            'D','$_consignee_id','$_address_1','$_address_2','$_address_3','$_address_4',
                            '$_country','$_city','$_postal_code',
                            '$_add_create_user',now(),'$_add_create_user',now(),
                            true,'$_sync_key','$_from_station','$_contact_person','$_contact_number');";
                    $_sync_key_add = $_sync_key;        
                }
            } else {
                if(!empty($_sync_key)){
                    $_add_create_user = "Online_D_Address";
                    if($_contact_type == "Unedit"){
                        continue;
                    }
                    $_is_delete = 'f';
                    if ($_contact_type ==  "Delete"){
                        $_is_delete = 't';
                    }

                    $exist = common::excuteObjectSql("select sync_key,action from public.contacts_address_online where sync_key = '$_sync_key'");
                    if(empty($exist)){
                        $addressSql .= "INSERT INTO public.contacts_address_online(
                                delivery_serial_no,action,
                                addr_type,contact_id, addr1, addr2,addr3, addr4, 
                                ctry_code, city_code, postal_code,  
                                create_user, create_date, modify_user, modify_date,
                                is_sync_country, sync_key, from_station,attn,tel,is_delete)
                        VALUES ('$delivery_serial_no','$_contact_type',
                                'D','$_contact_id','$_address_1','$_address_2','$_address_3','$_address_4',
                                '$_country','$_city','$_postal_code',
                                '$_add_create_user',now(),'$_add_create_user',now(),
                                true,'$_sync_key','$_from_station','$_contact_person','$_contact_number','$_is_delete');";
                    } else {
                        //Modify  Unedit :如果是用户完全新加的,这里不做action的修改，保留记录的Add
                        $temp_action = $_contact_type;
                        if ($exist['action'] == "Add"){
                            $temp_action = "Add";
                        }

                        $addressSql .="UPDATE public.contacts_address_online
                            SET delivery_serial_no = '$delivery_serial_no',addr1='$_address_1', addr2='$_address_2', addr3='$_address_3', addr4='$_address_4',
                                ctry_code='$_country', city_code='$_city', postal_code='$_postal_code', 
                                modify_user='$_add_create_user', modify_date=now(),
                                attn='$_contact_person', tel='$_contact_number',
                                action='$temp_action', is_delete = '$_is_delete'
                            WHERE sync_key = '$_sync_key';";
                    }
                }
            }
        }
        if(!empty($addressSql)){
            common::excuteUpdateSql($addressSql);
        }
        return $_sync_key_add;
    }

    /**
     * 审核通过时，才取下放当时应用修改的地址
     */
    public static function returnDAddressRecord($address_country,$onine_address,$delivery_address_detail){
        //deliver address is new
        $addressSql = "";
        $address_country = json_decode($address_country,true);
        //onine_address 这个是查询出来的attn 不变
        foreach($onine_address as $key => $_onine_address){
            $_address_1 = common::check_input($_onine_address['addr1']); 
            $_address_2 = common::check_input($_onine_address['addr2']);
            $_address_3 = common::check_input($_onine_address['addr3']);
            $_address_4 = common::check_input($_onine_address['addr4']); 
            $_ctry_code = common::check_input($_onine_address['ctry_code']); 
            $_city_code = common::check_input($_onine_address['city_code']); 
            $_postal_code = common::check_input($_onine_address['postal_code']); 
            $_attn = common::check_input($_onine_address['attn']); 
            $_tel = common::check_input($_onine_address['tel']); 

            $_contact_id = common::check_input($_onine_address['contact_id']);
            $_sync_key = common::check_input($_onine_address['sync_key']);
            $_from_station = strtoupper(common::check_input($_onine_address['from_station']));
            $_contact_type = common::check_input($_onine_address['action']);
            $_is_delete = common::check_input($_onine_address['is_delete']);

            $addressSql .=  "delete from public.contacts_address_online WHERE sync_key = '$_sync_key';";
            $uniqueAC =array();
            foreach($address_country as $ck => $_country){
                if(utils::in_array($_country,$uniqueAC)){
                    continue;
                }
                $uniqueAC[] = $_country;

                $_add_create_user = "Online_D_Address";
                $from_station = "ONLINE_".strtoupper($_country);

                $_sync_key_new = $_sync_key;
                if($ck > 0){
                    $_sync_key_new = common::uuid();
                }

                $existData = common::excuteObjectSql("select sync_key FROM public.contacts_address WHERE sync_key = '$_sync_key' and from_station = '$from_station'");
                if ($delivery_address_detail['sync_key'] == $_sync_key) {
                    if(!empty($existData) && !empty($existData['sync_key'])){
                        $addressSql .= "UPDATE public.contacts_address
                            SET addr1='$_address_1', addr2='$_address_2', 
                                ctry_code='$_ctry_code', city_code='$_city_code', postal_code='$_postal_code',  
                                attn = '$_attn',tel = '$_tel',
                                modify_user='"._getLoginName()."', modify_date=now()
                            WHERE sync_key = '$_sync_key';";
                    } else {
                        $addressSql .= "INSERT INTO public.contacts_address(
                                addr_type,contact_id, addr1, addr2,addr3, addr4, 
                                ctry_code, city_code, postal_code,  
                                create_user, create_date, modify_user, modify_date,
                                is_sync_country, sync_key, from_station,attn,tel)
                        VALUES ('D','$_contact_id','$_address_1','$_address_2','$_address_3','$_address_4',
                                '$_ctry_code','$_city_code','$_postal_code',
                                '$_add_create_user',now(),'$_add_create_user',now(),
                                true,'$_sync_key_new','$from_station','$_attn','$_tel');";
                    }
                } else {
                    //除开当前编辑的提交的，就只有用户自己删除要处理，其他的修改最后未选的情况留在本地管理
                    if ($_is_delete  == 't' && !empty($existData) && !empty($existData['sync_key'])){
                        $addressSql .=  "DELETE FROM public.contacts_address WHERE sync_key = '$_sync_key';";
                    }
                }
            }
        }
        return $addressSql;
    }


    /**
     * 当前save的地址信息
     */
    public static function returnDAddress($_sync_key_add=""){
        //deliver address is new
        $$delivery_address = "";
        $address_1 = $_POST['address_1'];
        foreach($address_1 as $key => $_address_1){
            $_address_1 = $_address_1; 
            $_address_2 = $_POST['address_2'][$key];
            $_address_3 = $_POST['address_3'][$key];
            $_address_4 = $_POST['address_4'][$key]; 
            $_country = $_POST['country'][$key]; 
            $_city = $_POST['city'][$key]; 
            $_postal_code = $_POST['postal_code'][$key]; 
            $_contact_person = $_POST['contact_person'][$key]; 
            $_contact_number = $_POST['contact_number'][$key]; 

            $_contact_id = $_POST['contact_id'][$key];
            //如果是新加的为空，这取当前consignee_id  WILSON80001
            if(empty($_contact_id)){
                $_contact_id = $_POST['consignee_id'][0];
            }
            $_sync_key = $_POST['sync_key'][$key];
            $_from_station = $_POST['from_station'][$key];
            $_contact_type = $_POST['contact_type'][$key];

            //if($_contact_type == "Add"){
            if(!empty($_sync_key_add)){
                //add 这么生成的唯一key
                $_sync_key = $_sync_key_add;
            }

            if ($_contact_type <> "Delete"){
                $delivery_address = array("address_1"=>$_address_1,
                    "address_2"=>$_address_2,
                    "address_3"=>$_address_3,
                    "address_4"=>$_address_4,
                    "country"=>$_country,
                    "city"=>$_city,
                    "postal_code"=>$_postal_code,
                    "contact_person"=>$_contact_person,
                    "contact_number"=>$_contact_number,
                    "contact_id"=>$_contact_id,
                    "sync_key"=>$_sync_key,
                    "from_station"=>$_from_station);
            }
        }
        return $delivery_address;
    }

    /**
     * Booking自動同步到Ksmart WO 的逻辑   保存在do_header和do_details表里
     */
    public static function saveWorkOrder($delivery_booking){
        $h_serial_no_json  = $delivery_booking['h_serial_no_json'];
        $h_serial_no_arr  =json_decode($h_serial_no_json,true);

        $ctnr_db  = $delivery_booking['ctnr'];
        //从UI 设计的case来讲，一个hbol就生成成一个wo ID 
        $sql = "";
        $do_serial_no_arr = array();
        foreach($h_serial_no_arr as $serial_no){
            $oceanInfo = common::excuteObjectSql("select *,
                case when order_from = 'public' and transport_mode ='sea' then (select manifest_type from public.ocean o where o.serial_no = oo.serial_no  limit 1)
                     when order_from = 'sfs' and transport_mode ='sea' then (select manifest_type from sfs.ocean o where o.serial_no = oo.serial_no  limit 1)
                else ''::text end as _manifest_type
            from public.kln_ocean oo where oo.serial_no ='".$serial_no."'" );
            //空運我們先不管，不做同步，就只做創建到審批，後面的讓他們自己錄入系統處理吧  
            if ($oceanInfo['transport_mode'] == "air" || strtoupper($oceanInfo['_manifest_type']) == "BCN"){
                continue;
            }
            $biztype = $oceanInfo['ex_im'] == "Export" && $oceanInfo['transport_mode'] == "sea" ? "SE" :(
                       $oceanInfo['ex_im'] == "Import" && $oceanInfo['transport_mode'] == "sea" ? "SI" :(
                       $oceanInfo['ex_im'] == "Export" && $oceanInfo['transport_mode'] == "air" ? "AE" :(
                       $oceanInfo['ex_im'] == "Import" && $oceanInfo['transport_mode'] == "air" ? "AI" : "" )));
            $mode_type = $delivery_booking['delivery_mode'] == "Truck" ? "Road" : "Rail";
            
            $consignee = $oceanInfo['consignee_id'];
            $_schema = $oceanInfo['order_from'];
            $_schema_contacts = $oceanInfo['order_from'];
            if($oceanInfo['order_from'] == "public"){
                $_schema_contacts = "ocean";
            }
        
            $delivery_address  =json_decode($delivery_booking['delivery_address_detail'],true);
            $consignee_name = common::excuteOneSql("select company from $_schema_contacts.contacts where contact_id = '$consignee' limit 1");
            $consignee_exp = common::combineContactInfoString($delivery_address);
            $consignee_sync_key = $delivery_address['sync_key'];
            $consignee_country_code = $delivery_address['country'];
            $consignee_city_code = $delivery_address['city'];
            $consignee_pic = $delivery_address['contact_person'];
            $consignee_tel = $delivery_address['contact_number'];
            $consignee_address1 = $delivery_address['address_1'];
            $consignee_address2 = $delivery_address['address_2'];
            $consignee_address3 = $delivery_address['address_3'];
            $consignee_address4 = $delivery_address['address_4'];

            $ctnrs = common::excuteListSql("select * FROM $_schema.oc_container oc where oc.serial_no::text = '".$serial_no."' 
                    and oc.ctnr in (select regexp_split_to_table('".$ctnr_db."', ','))" );
            $total_pcs = 0;
            $total_pcs_unit = "";
            $total_weight = 0;
            $total_weight_unit = "KGS";
            $total_volume = 0;
            $total_volume_unit = "CBM";

            $detail_row_count = 0;
            foreach($ctnrs as $_ctnr_info){
                $total_pcs_unit = $_ctnr_info['unit'];
                $total_pcs += $_ctnr_info['qty'];
                $total_weight += $_ctnr_info['grs_kgs'];
                $total_volume += $_ctnr_info['cbm'];
                $detail_row_count +=1;
            } 
             
            $total_pcs = empty($total_pcs) ? "null" : "'".$total_pcs."'";
            $total_weight = empty($total_weight) ? "null" : "'".$total_weight."'";
            $total_volume = empty($total_volume) ? "null" : "'".$total_volume."'";

            $do_serial_no = common::uuid();
            $sql .="INSERT INTO public.do_header(
                do_serial_no,detail_row_count,
                air_ocean, station_code, biztype,delivery_type,mode_type,
                serial_no, lot_no, awbbl_no, job_no, do_date,
                total_pcs, total_pcs_unit,
                total_weight,total_weight_unit,
                total_volume, total_volume_unit,
                est_delivery_date, est_delivery_time,instructions,
                create_user, create_time,delivery_vehicle_type,delivery_fuel_type,
                consignee, consignee_name, consignee_exp,
                consignee_address1,consignee_address2,consignee_address3,consignee_address4,
                consignee_country_code,consignee_city_code,consignee_pic,consignee_tel,consignee_addr_type_id,
                delivery_to_address_sync_key,refno)
            VALUES ('".$do_serial_no."','".$detail_row_count."','Ocean', '".$oceanInfo['agent']."','".$biztype."','Awaiting Confirmation','".$mode_type."',
                '".$serial_no."','".$oceanInfo['m_bol']."','".$oceanInfo['h_bol']."','".$oceanInfo['job_no']."',now(),
                ".$total_pcs.",'".$total_pcs_unit."',".$total_weight.",'".$total_weight_unit."',".$total_volume.",'".$total_volume_unit."',
                '".$delivery_booking['_delivery_date']."','".$delivery_booking['_delivery_time']."',
                '".$delivery_booking['special_requirements']."',
                'KLN_Online',now(),'Subcontractor','Diesel','".$consignee."','".$consignee_name."','".$consignee_exp."',
                '".$consignee_address1."','".$consignee_address2."','".$consignee_address3."','".$consignee_address4."',
                '".$consignee_country_code."','".$consignee_city_code."','".$consignee_pic."','".$consignee_tel."',0,
                '".$consignee_sync_key."','".$oceanInfo['delivery_reference']."');"; 
            
            foreach($ctnrs as $ctnr){
                $qty = empty($ctnr['qty']) ? "null" : "'".$ctnr['qty']."'";
                $grs_kgs = empty($ctnr['grs_kgs']) ? "null" : "'".$ctnr['grs_kgs']."'";
                $cbm = empty($ctnr['cbm']) ? "null" : "'".$ctnr['cbm']."'";
                $l4_length = empty($ctnr['l4_length']) ? "null" : "'".$ctnr['l4_length']."'";
                $l4_width = empty($ctnr['l4_width']) ? "null" : "'".$ctnr['l4_width']."'";
                $l4_height = empty($ctnr['l4_height']) ? "null" : "'".$ctnr['l4_height']."'";

                $serial_no_sync_key = common::uuid();
                $sql .="INSERT INTO public.do_detail(
                    do_serial_no, pcs, pcs_unit, weight, weight_unit, volume, 
                    marks, description, create_user, create_time, 
                    ctnr, size, seal_no, is_lcl,
                    length,width,height,serial_no_sync_key)
                VALUES ('".$do_serial_no."',$qty, '".$ctnr['unit']."',$grs_kgs,'KGS',$cbm,
                    '".$ctnr['marks']."','".$ctnr['description']."','KLN_Online',now(),
                    '".$ctnr['ctnr']."','".$ctnr['size']."','".$ctnr['seal_no']."','".$ctnr['is_lcl']."',
                    $l4_length,$l4_width,$l4_height,'$serial_no_sync_key');";
            }
            $do_serial_no_arr[] = $do_serial_no;
        }
        //do_serial_no_arr 只允许HBOL的层级创建,这里理论上只有一个
        return array("sql"=>$sql,"do_serial_no_arr"=>$do_serial_no_arr);
    }

    public static function combineContactInfoString($contact){
        $backString = "";
        if ($contact !== null) {
            $address1 = trim($contact['address_1'] ?? '');
            $address2 = trim($contact['address_2'] ?? '');
            $address3 = trim($contact['address_3'] ?? '');
            $address4 = trim($contact['address_4'] ?? '');
            $city     = trim($contact['city'] ?? '');
            $zipcode  = trim($contact['postal_code'] ?? '');
            $country  = trim($contact['country'] ?? '');

            // 拼接非空的地址行
            if ($address1 !== '') {
                $backString .= $address1;
            }

            if ($address2 !== '') {
                $backString .= ($backString !== '' ? "\n" : '') . $address2;
            }

            if ($address3 !== '') {
                $backString .= ($backString !== '' ? "\n" : '') . $address3;
            }

            if ($address4 !== '') {
                $backString .= ($backString !== '' ? "\n" : '') . $address4;
            }

            // 添加城市、邮编和国家
            $location = trim("$city $zipcode $country");
            if ($location !== '') {
                $backString .= ($backString !== '' ? "\n" : '') . $location;
            }
        }

        return trim($backString);
    }

    /**
     * 过滤文件名中的非法字符（适用于 Windows / Linux / macOS）
     *
     * @param string $filename 原始文件名
     * @param string $replacement 替换非法字符的符号（默认用下划线）
     * @return string 安全的文件名
     */
    public static function sanitizeFilename($filename, $replacement = '_')
    {
        // 定义非法字符（Windows 系统最严格）
        $illegalChars = ['\\', '/', ':', '*', '?', '"', '<', '>', '|', "\0"];

        // 更严格的：也替换制表符、换行符、回车
        $illegalChars = array_merge($illegalChars, ["\t", "\n", "\r"]);

        // 替换所有非法字符
        $safeName = str_replace($illegalChars, $replacement, $filename);

        // 额外清理：多个连续的替换符 → 变成一个（如 ___ → _）
        $safeName = preg_replace('/' . preg_quote($replacement, '/') . '+/', $replacement, $safeName);

        // 去除首尾空白和替换符
        $safeName = trim($safeName, " \t\n\r\0\x0B" . $replacement);

        // 防止以点或横线开头/结尾（某些系统敏感）
        $safeName = trim($safeName, '.-');

        // 如果清理后为空，返回默认名
        if ($safeName === '' || $safeName === '.' || $safeName === '..') {
            $safeName = 'file_' . time();
        }

        return $safeName;
    }
    /**
     * Packing List 和 Commercial Invoice  顺序处理,以后自定义了可以移除这个函数
    */
    public static function destination_delivery_shipment_search_sort($columns){
        // 找出目标元素的位置和需要移动的元素
        $moveItems = [];
        $targetIndex = null;

        foreach ($columns as $index => $item) {
            if ($item['field'] === 'package_type') {
                $targetIndex = $index;
            } elseif (in_array($item['title'], ['Packing List', 'Commercial Invoice'])) {
                $moveItems[] = $item;
                unset($columns[$index]); // 移除这些元素
            }
        }

        // 如果找到了目标位置并且有需要移动的元素
        if ($targetIndex !== null && count($moveItems) > 0) {
            // 在目标位置后插入需要移动的元素
            array_splice($columns, $targetIndex + 1, 0, $moveItems);
            
            // 重置数组索引
            $columns = array_values($columns);
        }
        return $columns;
    }

    public static function getReportRealDBFiled(){
        $kln_ocean = [
            "kln_ocean.tracking_no" => "tracking_no",
            "kln_ocean.m_bol" => "mbol_mawb_no",
            "kln_ocean.h_bol" => "hbol_hawb_no",
            "kln_ocean.invoice_no" => "shipment_invoice_no",
            "kln_ocean.booking_no" => "booking_no",
            "COALESCE(oe.import_po_no,kln_ocean.po_no)"=>"shipment_po_no",    //代表oo里的组合字段，需要特殊处理
            "kln_ocean.quote_no" => "quote_no",
            "kln_ocean.carrier_booking" => "carrier_booking_no",
            "kln_ocean.contract" => "contract_no",
            "kln_ocean.manifest_hbol" => "manifest_hbol",
            "kln_ocean.transport_mode" => "transportation_mode",
            "kln_ocean.service" => "service_type",
            "oe.manifest_type"=>"shipment_type",
            "kln_ocean.ex_im" => "ex_im",
            "kln_ocean.incoterms" => "incoterms",
            "oe.loadterm"=>"load_terms",
            "(CASE
                WHEN (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 'Departed'::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'
            END)" => "status",
            "kln_ocean.carbon_emission" => "total_co2_emission",
            "kln_ocean.qty" => "shipment_qty",
            "kln_ocean.piece_count" => "shipment_gross_weight",
            "kln_ocean.weight" => "chargeable_weight",
            "oe.volume"=>"shipment_volume",
            "kln_ocean.shipper" => "shipper",
            "kln_ocean.shipper_id" => "shipper_id",
            "kln_ocean.consignee" => "consignee",
            "kln_ocean.consignee_id" => "consignee_id",
            "kln_ocean.notify_party" => "notify_party",
            "kln_ocean.notify_party_id" => "notify_party_id",
            "kln_ocean.billto" => "bill_to",
            "kln_ocean.group_name" => "group_name",
            "kln_ocean.origin" => "origin_agent",
            "kln_ocean.agent" => "destination_agent",
            "kln_ocean.dest_op" => "destination_operator",
            "kln_ocean.sales_rep" => "sales",
            "kln_ocean.etd" => "etd",
            "kln_ocean.eta" => "eta",
            "kln_ocean.created_time" => "creation_time",
            "kln_ocean.atd" => "atd",
            "kln_ocean.ata" => "ata",
            "kln_ocean.shipper_city" => "shipper_city",
            "kln_ocean.consignee_city" => "consignee_city",
            "kln_ocean.place_of_receipt_exp" => "place_of_receipt",
            "kln_ocean.port_of_loading" => "port_of_loading",
            "kln_ocean.port_of_discharge" => "port_of_discharge",
            "kln_ocean.fport_of_loading_un" => "pol_locode",
            "kln_ocean.mport_of_discharge_un" => "pod_locode",
            "kln_ocean.place_of_delivery_exp" => "place_of_delivery",
            "kln_ocean.port_of_transshipment_name" => "port_of_transhipment",
            "kln_ocean.carrier" => "carrier",
            "(CASE
                WHEN kln_ocean.voyage IS NOT DISTINCT FROM kln_ocean.f_voyage THEN kln_ocean.voyage
                WHEN NULLIF(TRIM(kln_ocean.voyage), '') IS NOT NULL AND NULLIF(TRIM(kln_ocean.f_voyage), '') IS NOT NULL
                    THEN kln_ocean.f_voyage || '/' || kln_ocean.voyage
                WHEN NULLIF(TRIM(kln_ocean.voyage), '') IS NOT NULL
                    THEN kln_ocean.voyage
                ELSE kln_ocean.f_voyage
            END)"=>"voyage_flight",    
            "(CASE
                WHEN kln_ocean.vessel IS NOT DISTINCT FROM kln_ocean.f_vessel THEN kln_ocean.vessel
                WHEN NULLIF(TRIM(kln_ocean.vessel), '') IS NOT NULL AND NULLIF(TRIM(kln_ocean.f_vessel), '') IS NOT NULL
                    THEN kln_ocean.f_vessel || '/' || kln_ocean.vessel
                WHEN NULLIF(TRIM(kln_ocean.vessel), '') IS NOT NULL
                    THEN kln_ocean.vessel
                ELSE kln_ocean.f_vessel
            END)"=>"vessel_airline",
            "CONCAT_WS('/', 
                CASE
                    WHEN kln_ocean.vessel IS NOT DISTINCT FROM kln_ocean.f_vessel THEN kln_ocean.vessel
                    WHEN NULLIF(TRIM(kln_ocean.vessel), '') IS NOT NULL AND NULLIF(TRIM(kln_ocean.f_vessel), '') IS NOT NULL
                        THEN kln_ocean.f_vessel || '/' || kln_ocean.vessel
                    WHEN NULLIF(TRIM(kln_ocean.vessel), '') IS NOT NULL
                        THEN kln_ocean.vessel
                    ELSE kln_ocean.f_vessel
                END, 
                CASE
                    WHEN kln_ocean.voyage IS NOT DISTINCT FROM kln_ocean.f_voyage THEN kln_ocean.voyage
                    WHEN NULLIF(TRIM(kln_ocean.voyage), '') IS NOT NULL AND NULLIF(TRIM(kln_ocean.f_voyage), '') IS NOT NULL
                        THEN kln_ocean.f_voyage || '/' || kln_ocean.voyage
                    WHEN NULLIF(TRIM(kln_ocean.voyage), '') IS NOT NULL
                        THEN kln_ocean.voyage
                    ELSE kln_ocean.f_voyage
                END
            )"=>"vslvoy_flight",
            "kln_ocean.ams_status" => "ace_m1_status",
            "kln_ocean.isisf" => "is_isf",
            "kln_ocean.obl_set" => "obl_set",
            "oc.ctnr" => "container_no",
            "oc.size" => "container_size",
            "oc.qty" => "container_qty",
            "oc.unit" => "container_unit",
            "oc.grs_kgs" => "container_weight",
            "oc.cbm" => "container_volume",
            "oc.po_no" => "container_po_no",
            "oc.item_no" => "item_no",
            "oc.invoice_no" => "container_invoice_no",
            "oc.seal_no" => "seal_no",
            "oi.po_no" => "item_po_no",
            "oi.sku_no" => "sku_no",
            "oi.quantity" => "item_qty",
            "oi.unit" => "item_unit",
            "oi.grs_kgs" => "item_weight",
            "oi.vol_cbm" => "item_volume",
            "oi.description" => "description",
            "oi.inner_pcs" => "inner_pcs",
            "(COALESCE(co2_r.pickup_distances, 0) +COALESCE(co2_r.routes_distances, 0) +COALESCE(co2_r.delivery_distances, 0))" => "total_distance",
		    "co2_r.pickup_carbon_emission" => "pickup_co2_emission",
		    "co2_r.pickup_distances" => "pickup_distance",
		    "co2_r.routes_carbon_emission" => "main_route_co2_emission",
		    "co2_r.routes_distances" => "main_route_distance",
		    "co2_r.delivery_carbon_emission" => "delivery_co2_emission",
		    "co2_r.delivery_distances" => "delivery_distance",

            "COALESCE(wo_pickup.postal_code,routing_pickup.postal_code)" => "pickup_postal_code",
            "COALESCE(wo_pickup.mode_type,routing_pickup.mode_type)" => "pickup_mode_type",
            "COALESCE(wo_delivery.postal_code,routing_delivery.postal_code)" => "last_mile_delivery_postal_code",
            "COALESCE(wo_delivery.mode_type,routing_delivery.mode_type)" => "last_mile_delivery_mode_type",

            "wo_delivery.est_delivery_date" => "last_mile_delivery_date",
            "wo_delivery.est_delivery_time" => "last_mile_delivery_time",
            "wo_delivery.city" => "last_mile_delivery_city",
            "wo_delivery.full_address" => "last_mile_delivery _address",
            "(CASE WHEN COALESCE(NULLIF(trim(oo.piece_count), ''), '0')::numeric > 0 
                        AND (COALESCE(co2_r.pickup_distances, 0) +COALESCE(co2_r.routes_distances, 0) +COALESCE(co2_r.delivery_distances, 0)) > 0 
                        AND COALESCE(oo.carbon_emission, 0) > 0 
                    THEN ROUND(COALESCE(oo.carbon_emission, 0) / 1000.0 
                        / (1.0 / 1000.0)
                        / (1.0 / 1000.0)
                        / (COALESCE(NULLIF(trim(oo.piece_count), ''), '0')::numeric / (1.0 / (COALESCE(co2_r.pickup_distances, 0) +COALESCE(co2_r.routes_distances, 0) +COALESCE(co2_r.delivery_distances, 0))) / 1000.0),4)		
            END)" => "co2e_intensity",
            "(case when kln_ocean.order_from  ='public' and kln_ocean.transport_mode  ='sea' then (select customer_code from public.ocean_extend ex where ex.serial_no = kln_ocean.serial_no limit 1)
                when kln_ocean.order_from  ='sfs' and kln_ocean.transport_mode  ='sea' then (select customer_code from sfs.ocean_extend ex where ex.serial_no = kln_ocean.serial_no limit 1)
                when kln_ocean.order_from  ='public' and kln_ocean.transport_mode  ='air' then (select customer_code from public.air_extend ex where ex.serial_no = kln_ocean.serial_no limit 1)
                when kln_ocean.order_from  ='sfs' and kln_ocean.transport_mode  ='air' then (select customer_code from sfs.air_extend ex where ex.serial_no = kln_ocean.serial_no limit 1)
            else ''::text
            end)" => "controlling_customer"
        ];
        return  $kln_ocean; 
    }

    public static function toPgTextArrayLiteral(array $arr): string {
        if (empty($arr)) {
            return "'{}'";
        }
        $quoted = array_map(fn($v) => '"' . addcslashes((string)$v, '"\\') . '"', $arr);
        return "'{" . implode(',', $quoted) . "}'";
    }

    /**
     * 根据用户配置计算下一次执行时间（返回 UTC 时间戳，无时区）
     *
     * @param array $config 来自数据库的一行配置
     * @return \DateTimeImmutable 返回 UTC 时间（无时区），可用于存储到 next_run_time
     */
    public static function calculateNextRunTime(array $config): \DateTimeImmutable
    {
        // ────────────────────────────────────────────────
        // 第一步：解析用户时区（支持 'UTC+08', 'Asia/Shanghai' 等）
        // ────────────────────────────────────────────────
        $userTz = common::parseUserTimezone($config['timezone'] ?? 'UTC');

        // 获取当前时间（在用户时区中）
        $nowInUserTz = new \DateTimeImmutable('now', $userTz);

        // ────────────────────────────────────────────────
        // 第二步：解析时间字符串（如 '09:00:00' → [9,0,0]）
        // ────────────────────────────────────────────────
        $parseTime = function (?string $timeStr): array {
            if (!$timeStr || !preg_match('/^(\d{1,2}):(\d{2})(?::(\d{2}))?$/', $timeStr, $m)) {
                return [9, 0, 0]; // 默认 09:00
            }
            return [(int)$m[1], (int)$m[2], isset($m[3]) ? (int)$m[3] : 0];
        };

        // ────────────────────────────────────────────────
        // 第三步：根据频率计算下一个执行时间（在用户时区中）
        // ────────────────────────────────────────────────
        $next = null;
        $freq = $config['delivery_frequency'] ?? 'daily';

        switch ($freq) {
            case 'daily':
                [$h, $m, $s] = $parseTime($config['daily_time'] ?? '09:00:00');
                $next = $nowInUserTz->setTime($h, $m, $s);
                if ($next <= $nowInUserTz) {
                    $next = $next->modify('+1 day');
                }
                break;

            case 'weekly':
                [$h, $m, $s] = $parseTime($config['weekly_time'] ?? '09:00:00');
                $scheduledDays = array_map('intval', (array)($config['weekly_day'] ?? []));
                if (empty($scheduledDays)) {
                    $scheduledDays = [1]; // 默认周一
                }

                $now = $nowInUserTz;
                $next = null;

                //搜索 8 天：确保包含“下周同一天”
                for ($i = 0; $i <= 7; $i++) {
                    $candidate = (clone $now)->modify("+$i days")->setTime($h, $m, $s);
                    if (in_array((int)$candidate->format('N'), $scheduledDays)) {
                        if ($candidate >= $now) {
                            $next = $candidate;
                            break;
                        }
                    }
                }
                // fallback：仅当 $scheduledDays 为空或极端错误时触发
                if (!$next) {
                    // 安全兜底：取最小周几，安排到下周
                    $dow = min($scheduledDays ?: [1]);
                    $next = (clone $now)
                        ->modify('next monday')
                        ->modify('+' . ($dow - 1) . ' days')
                        ->setTime($h, $m, $s);
                }
                break;

            case 'monthly':
                [$h, $m, $s] = $parseTime($config['monthly_time'] ?? '09:00:00');
                $days = array_map('intval', (array)($config['monthly_day'] ?? []));
                if (empty($days)) {
                    $days = [1]; // 默认每月1号
                }

                $targetDay = null;
                // 尝试在当前月找一个 >= 今天的日期
                foreach ($days as $day) {
                    $temp = $nowInUserTz->setDate(
                        (int)$nowInUserTz->format('Y'),
                        (int)$nowInUserTz->format('m'),
                        $day
                    )->setTime($h, $m, $s);
                    if ($temp >= $nowInUserTz) {
                        $targetDay = $day;
                        break;
                    }
                }

                if ($targetDay !== null) {
                    $next = $nowInUserTz->setDate(
                        (int)$nowInUserTz->format('Y'),
                        (int)$nowInUserTz->format('m'),
                        $targetDay
                    )->setTime($h, $m, $s);

                    // 处理无效日期（如 2月31日 → 自动变为月末）
                    if ((int)$next->format('d') !== $targetDay) {
                        $next = $next->modify('last day of this month')->setTime($h, $m, $s);
                    }
                } else {
                    // 当前月没有合适日期，跳到下个月
                    $nextMonth = $nowInUserTz->modify('first day of next month');
                    $targetDay = $days[0]; // 取第一个
                    $next = $nextMonth->setDate(
                        (int)$nextMonth->format('Y'),
                        (int)$nextMonth->format('m'),
                        $targetDay
                    )->setTime($h, $m, $s);

                    if ((int)$next->format('d') !== $targetDay) {
                        $next = $next->modify('last day of this month')->setTime($h, $m, $s);
                    }
                }
                break;

            case 'quarterly':
                [$h, $m, $s] = $parseTime($config['quarterly_time'] ?? '09:00:00');
                $monthInQuarter = max(1, min(3, (int)($config['quarterly_month'] ?? 1))); // 1~3
                $day = max(1, min(31, (int)($config['quarterly_day'] ?? 1)));

                $currentYear = (int)$nowInUserTz->format('Y');
                $currentMonth = (int)$nowInUserTz->format('m');
                $currentQuarter = ceil($currentMonth / 3);
                $targetMonthThisQuarter = ($currentQuarter - 1) * 3 + $monthInQuarter;

                // 构造本季度目标日期
                $next = (new \DateTimeImmutable("{$currentYear}-01-01", $userTz))
                    ->setDate($currentYear, $targetMonthThisQuarter, $day)
                    ->setTime($h, $m, $s);

                // 修正无效日期
                if ((int)$next->format('d') !== $day) {
                    $next = $next->modify('last day of this month')->setTime($h, $m, $s);
                }

                if ($next <= $nowInUserTz) {
                    // 跳到下一季度
                    $nextQuarter = $currentQuarter + 1;
                    if ($nextQuarter > 4) {
                        $nextQuarter = 1;
                        $currentYear++;
                    }
                    $targetMonthNextQuarter = ($nextQuarter - 1) * 3 + $monthInQuarter;
                    $next = (new \DateTimeImmutable("{$currentYear}-01-01", $userTz))
                        ->setDate($currentYear, $targetMonthNextQuarter, $day)
                        ->setTime($h, $m, $s);

                    if ((int)$next->format('d') !== $day) {
                        $next = $next->modify('last day of this month')->setTime($h, $m, $s);
                    }
                }
                break;

            case 'yearly':
                [$h, $m, $s] = $parseTime($config['yearly_time'] ?? '09:00:00');
                $months = array_map('intval', (array)($config['yearly_month'] ?? []));
                $day = max(1, (int)($config['yearly_day'] ?? 1));

                if (empty($months)) {
                    $months = [1];
                }

                $targetMonth = null;
                foreach ($months as $month) {
                    $temp = $nowInUserTz->setDate((int)$nowInUserTz->format('Y'), $month, $day);
                    if ($temp >= $nowInUserTz) {
                        $targetMonth = $month;
                        break;
                    }
                }

                if ($targetMonth !== null) {
                    $next = $nowInUserTz->setDate((int)$nowInUserTz->format('Y'), $targetMonth, $day)->setTime($h, $m, $s);
                    if ((int)$next->format('m') !== $targetMonth) {
                        $next = $next->modify('last day of this month')->setTime($h, $m, $s);
                    }
                } else {
                    // 明年
                    $targetMonth = $months[0];
                    $next = $nowInUserTz->modify('+1 year')
                        ->setDate((int)$nowInUserTz->modify('+1 year')->format('Y'), $targetMonth, $day)
                        ->setTime($h, $m, $s);
                    if ((int)$next->format('m') !== $targetMonth) {
                        $next = $next->modify('last day of this month')->setTime($h, $m, $s);
                    }
                }
                break;

            default:
                $next = $nowInUserTz->setTime(9, 0, 0);
                if ($next <= $nowInUserTz) {
                    $next = $next->modify('+1 day');
                }
                break;
        }

        // ────────────────────────────────────────────────
        // 第四步：转成 UTC 时间（无时区），用于存入数据库
        // ────────────────────────────────────────────────
        return $next->setTimezone(new \DateTimeZone('UTC'));
    }

    /**
     * 解析用户输入的时区（如 'UTC+08' 或 'Asia/Shanghai'）为 DateTimeZone
     */
    public static function parseUserTimezone(string $tzStr): \DateTimeZone
    { 
        $tzStr = trim($tzStr);

        // 1. 尝试标准 IANA 时区
        if (@in_array($tzStr, timezone_identifiers_list())) {
            return new \DateTimeZone($tzStr);
        }

        // 2. 处理 UTC±H[H] 或 UTC±H[H]:MM
        if (preg_match('/^UTC([+-])(\d{1,2})(?::(\d{2}))?$/', $tzStr, $matches)) {
            $sign = $matches[1];
            $hours = (int)$matches[2];
            $minutes = isset($matches[3]) ? (int)$matches[3] : 0;

            if ($hours >= 0 && $hours <= 14 && $minutes < 60) {
                // 构造 +HH:MM 或 -HH:MM 格式（PHP 原生支持）
                $offsetStr = sprintf('%s%02d:%02d', $sign, $hours, $minutes);
                try {
                    return new \DateTimeZone($offsetStr);
                } catch (\Exception $e) {
                    // fallback
                }
            }
        }

        // 3. 默认
        return new \DateTimeZone('UTC');
    }

    // 判断某个字段是否属于特定逻辑表
    // $filterFields = [
    //     'Description',     // 来自 oi
    //     'Status',          // 来自 oo
    //     'Load Terms'       // 来自 oe
    // ];
    public static function getRequiredTables(array $filterFields)
    {
        $FIELD_TABLE_MAP = [
        // 主表 oo (kln_ocean)
        'Status'                     => ['oo','new_status'],
        'Transportation Mode'        => ['oo'],
        'Voyage/Flight'              => ['oo','_voyage'],
        'Vessel/Airline'             => ['oo','_vessel'],
        'VSLVOY/FLIGHT'              => ['oo','VSLVOY/FLIGHT'],
        'Container No. (House)'      => ['oo'],
        'Shipment PO No.'            => ['oo', 'oe'], // 可能来自 oe.import_po_no

        // oc_container
        'Container No.'              => ['oc'],
        'Container Size'             => ['oc'],
        'Container Qty'              => ['oc'],
        'Container Unit'             => ['oc'],
        'Container Weight'           => ['oc'],
        'Container Volume'           => ['oc'],
        'Container PO No.'           => ['oc'],
        'Item No.'                   => ['oc'],
        'Invoice No.'                => ['oc'],

        // oc_container_item (oi)
        'Item PO No.'                => ['oi'],
        'SKU NO.'                    => ['oi'],
        'Item Qty'                   => ['oi'],
        'Item Unit'                  => ['oi'],
        'Item Weight'                => ['oi'],
        'Item Volume'                => ['oi'],
        'Description'                => ['oi'],
        'Inner PCS'                  => ['oi'],

        // kln_ocean_extend (oe)
        'Shipment Type'              => ['oe'],
        'Container Size (House)'     => ['oe'],
        'Load Terms'                 => ['oe'],

        // calculate_co2_result (co2_r)
        'Total Distance'             => ['co2_r'],
        'Pickup CO2 Emission'        => ['co2_r'],
        'Pickup Distance'            => ['co2_r'],
        'Main Route CO2 Emission'    => ['co2_r'],
        'Main Route Distance'        => ['co2_r'],
        'Delivery CO2 Emission'      => ['co2_r'],
        'Delivery Distance'          => ['co2_r'],
        'CO2e Intensity'             => ['co2_r'],

        // 特殊字段：依赖 oc + oe
        //'Controlling Customer'       => ['oo','Controlling Customer'],
        'CARRIER TEU'                => ['oe','CARRIER TEU'],

        'Pickup-Postal Code'              => ['pickup'],
        'Pickup-Mode Type'                => ['pickup'],
        'Last mile delivery-Postal Code'  => ['delivery'],
        'Last mile delivery-Mode Type'    => ['delivery'],

        'Last Mile Delivery Date'    => ['delivery'],
        'Last Mile Delivery Time'    => ['delivery'],
        'Last Mile Delivery City'    => ['delivery'],
        'Last Mile Delivery Address' => ['delivery']
        ];

        $tables = [];

        foreach ($filterFields as $fieldAlias) {
            if (isset($FIELD_TABLE_MAP[$fieldAlias])) {
                foreach ($FIELD_TABLE_MAP[$fieldAlias] as $table) {
                    $tables[$table] = true;
                }
            }
        }

        return array_keys($tables); // e.g., ['oo', 'oi']
    }

    public static function buildCountSql(array $filterFieldAliases,$level)
    {
        $requiredTables = common::getRequiredTables($filterFieldAliases);

        // 是否需要容器/项数据（即区分 public / sfs）
        $needContainerData = in_array('oc', $requiredTables);
        $needItemData = in_array('oi', $requiredTables);
        $needOceanExtend   = in_array('oe', $requiredTables) || in_array('Last Mile Delivery', $filterFieldAliases);
        $needCO2           = in_array('co2_r', $requiredTables);
        $need_pickup       = in_array('pickup', $requiredTables);
        $need_delivery     = in_array('delivery', $requiredTables);

        $needteu     = in_array('CARRIER TEU', $requiredTables);
        $needCustomer     = in_array('Controlling Customer', $requiredTables);

        //在根据层级分配是否需要字段
        if($level == 'Container Level'){
            $needContainerData = true;
        }elseif($level == 'Item Level'){
            $needItemData = true;
            $needContainerData = true;
        }
        
        $needCustomerSQL = "";
        // if($needCustomer){
        //     $needCustomerSQL = " case when oo.order_from  ='public' and oo.transport_mode  ='sea' then (select customer_code from public.ocean_extend ex where ex.serial_no = oo.serial_no limit 1)
        //         when oo.order_from  ='sfs' and oo.transport_mode  ='sea' then (select customer_code from sfs.ocean_extend ex where ex.serial_no = oo.serial_no limit 1)
        //         when oo.order_from  ='public' and oo.transport_mode  ='air' then (select customer_code from public.air_extend ex where ex.serial_no = oo.serial_no limit 1)
        //         when oo.order_from  ='sfs' and oo.transport_mode  ='air' then (select customer_code from sfs.air_extend ex where ex.serial_no = oo.serial_no limit 1)
        //     else ''::text
        //     end as \"Controlling Customer\", ";
        // }

        $needteuSQL = "";
        if($needteu){
            $needteuSQL = " CASE
                WHEN oe.manifest_type in ('LCL','GPE','CIL','COL') THEN 0::numeric
                WHEN oe.manifest_type in ('FCL','BCN','CIF','COF','BCL','BCF') and  \"left\"(oc.size::text, 1) = '1'::text THEN 0.5::numeric
                WHEN oe.manifest_type in ('FCL','BCN','CIF','COF','BCL','BCF') and  \"left\"(oc.size::text, 1) = '2'::text THEN 1::numeric
                WHEN oe.manifest_type in ('FCL','BCN','CIF','COF','BCL','BCF') and  \"left\"(oc.size::text, 2) = '40'::text THEN 2::numeric
                WHEN oe.manifest_type in ('FCL','BCN','CIF','COF','BCL','BCF') and  \"left\"(oc.size::text, 2) = '45'::text THEN 2.5::numeric
                WHEN oe.manifest_type in ('FCL','BCN','CIF','COF','BCL','BCF') and  \"left\"(oc.size::text, 1) = '5'::text THEN 2.65::numeric
                WHEN oe.manifest_type in ('RORO','BULK','ROR','BLK','CON')  THEN ROUND(COALESCE(oc.grs_kgs,0)/15000.0, 2)::numeric
            ELSE (CASE
                    WHEN \"left\"(oc.size::text, 1) = '2'::text THEN 1
                    ELSE 2::numeric
                END)
            END	 AS \"CARRIER TEU\", ";
        }

        // 构建 LATERAL 子查询（按需）
        $oeLateral = '';
        if ($needOceanExtend) {
            $oeLateral = "
                LEFT JOIN LATERAL (
                    SELECT 
                        loadterm,
                        manifest_type,
                        container_size,
                        import_po_no,
                        (SELECT jsonb_agg(to_jsonb(de.*)) FROM (
                            SELECT 
                                split_part(lmd, '/', 1) AS ctnr,
                                split_part(lmd, '/', 2) AS delivery_datetime
                            FROM regexp_split_to_table(COALESCE(oe.last_mile_delivery_date, ''), ';') AS lmd
                            WHERE lmd <> ''
                        ) de) AS lmdds
                    FROM public.kln_ocean_extend oe 
                    WHERE oe.serial_no::text = oo.serial_no::text 
                    AND oe.order_from::text = oo.order_from::text 
                    LIMIT 1
                ) oe ON true";
        }

        $co2Lateral = '';
        if ($needCO2) {
            $co2Lateral = "
                LEFT JOIN LATERAL (
                    SELECT 
                        pickup_carbon_emission,
                        pickup_distances,
                        routes_carbon_emission,
                        routes_distances,
                        delivery_carbon_emission,
                        delivery_distances
                    FROM public.calculate_co2_result ccr
                    WHERE ccr.serial_no::text = oo.serial_no::text
                    LIMIT 1
                ) co2_r ON true";
        }

        $pickupLateral = '';
        if ($need_pickup) {
            $pickupLateral = "
               LEFT JOIN LATERAL (
                    SELECT 
                        cap.postal_code,
                        h.mode_type
                    FROM station_data.do_header h
                        LEFT JOIN station_data.do_detail d on h.id = d.do_header_id and h.from_station = d.from_station 
                        LEFT JOIN public.contacts_address cap ON cap.sync_key = h.collect_addr_key
                    WHERE (h.serial_no = oo.serial_no  
                            or h.serial_no = (case when oo.order_from  ='public' and oo.transport_mode  ='sea' 
                                                        then (select master_base_number from public.ocean ex where ex.serial_no = oo.serial_no limit 1)
                                                when oo.order_from  ='sfs' and oo.transport_mode  ='sea'
                                                        then (select master_base_number from sfs.ocean ex where ex.serial_no = oo.serial_no limit 1)
                                                else ''::text end))  
                        AND h.from_station = oo.origin
                        AND COALESCE(h.serial_no) <> ''				
                        AND COALESCE(h.void_by,'') = ''	
                        AND (d.ctnr = oc.ctnr or coalesce(oc.ctnr,''::text)=''::text)				
                        AND h.collect_addr_key is not null  ORDER BY h.id DESC  LIMIT 1
                ) AS wo_pickup ON true
                LEFT JOIN LATERAL (
                    SELECT 
                        pickup_postal_code AS postal_code,
                        pickup_mode_type AS mode_type
                    FROM (
                        SELECT pickup_postal_code, pickup_mode_type
                            FROM station_data.ocean_pickup_delivery
                        WHERE serial_no = oo.serial_no AND from_station = oo.origin AND oo.transport_mode = 'sea' 
                        UNION ALL
                        SELECT pickup_postal_code, pickup_mode_type
                            FROM station_data.air_pickup_delivery 
                        WHERE serial_no = oo.serial_no AND from_station = oo.origin AND oo.transport_mode = 'air' 
                    ) u
                    LIMIT 1
                ) AS routing_pickup ON true ";
        }

        $deliveryLateral = '';
        if ($need_delivery) {
            $deliveryLateral = "
                LEFT JOIN LATERAL (
                    SELECT 
                        cad.postal_code,
                        h.mode_type,
                        h.est_delivery_date,
                        h.est_delivery_time,
                        CONCAT_WS('',cad.ctry_code,cad.city_code) as city,
                        COALESCE(cad.addr1, '') || ' ' || COALESCE(cad.addr2, '') || ' ' || COALESCE(cad.addr3, '') || ' ' || COALESCE(cad.addr4, '') AS full_address
                    FROM station_data.do_header h
                        LEFT JOIN station_data.do_detail d on h.id = d.do_header_id and h.from_station = d.from_station 
                        LEFT JOIN public.contacts_address cad ON cad.sync_key = h.consignee_addr_key 
                    WHERE (h.serial_no = oo.serial_no  
                            or h.serial_no = (case when oo.order_from  ='public' and oo.transport_mode  ='sea' 
                                                        then (select master_base_number from public.ocean ex where ex.serial_no = oo.serial_no limit 1)
                                                when oo.order_from  ='sfs' and oo.transport_mode  ='sea'
                                                        then (select master_base_number from sfs.ocean ex where ex.serial_no = oo.serial_no limit 1)
                                                else ''::text end)) 
                        AND h.from_station = oo.agent
                        AND COALESCE(h.serial_no) <> '' 				
                        AND COALESCE(h.void_by,'') = ''	
                        AND (d.ctnr = oc.ctnr or coalesce(oc.ctnr,''::text)=''::text)  
                        AND h.consignee_addr_key is not null ORDER BY h.id DESC LIMIT 1
                ) AS wo_delivery ON true
                LEFT JOIN LATERAL (
                    SELECT 
                        delivery_postal_code AS postal_code,
                        delivery_mode_type AS mode_type
                    FROM (
                        SELECT delivery_postal_code, delivery_mode_type
                            FROM station_data.ocean_pickup_delivery
                        WHERE serial_no = oo.serial_no AND from_station = oo.agent AND oo.transport_mode = 'sea'
                        UNION ALL
                        SELECT delivery_postal_code, delivery_mode_type
                            FROM station_data.air_pickup_delivery
                        WHERE serial_no = oo.serial_no AND from_station = oo.agent AND oo.transport_mode = 'air' 
                    ) u
                    LIMIT 1
                ) AS routing_delivery ON true ";
        }


        // --- CTE: oo (主表) ---
        $sql = "
        with oo as (
            SELECT  * FROM public.kln_ocean  <{klnOceanSearchKLN}>
        )";

        // 如果不需要容器/项数据，直接 COUNT
        if (!$needContainerData) {
            if(!empty($needCustomerSQL)){
                return $sql . "\n ,vv as (select {$needCustomerSQL} 1 from oo {$oeLateral} {$co2Lateral} {$pickupLateral} {$deliveryLateral} <{ocItemSearchKLN}>) 
                    select count(*)  from vv <{vvSearchKLN}>;";
            }
            return $sql . "\n select count(*) from oo {$oeLateral} {$co2Lateral} {$pickupLateral} {$deliveryLateral} <{ocItemSearchKLN}>;";
        }

        // --- 需要容器数据：构建 vv_public 和 vv_sfs ---
        $joinPublic = "
            LEFT JOIN public.oc_container oc ON oo.serial_no::text = oc.serial_no::text AND oo.order_from = 'public'";

        $joinSfs = "
            LEFT JOIN sfs.oc_container oc ON oo.serial_no::text = oc.serial_no::text AND oo.order_from = 'sfs'";

        // 公共部分：JOIN 容器和 item
        if ($needContainerData && $needItemData) {
            $joinPublic = "
            LEFT JOIN public.oc_container oc ON oo.serial_no::text = oc.serial_no::text AND oo.order_from = 'public'
            LEFT JOIN public.oc_container_item oi ON oc.edi_serial_no = oi.edi_container_serial_no";

            $joinSfs = "
                LEFT JOIN sfs.oc_container oc ON oo.serial_no::text = oc.serial_no::text AND oo.order_from = 'sfs'
                LEFT JOIN sfs.oc_container_item oi ON oc.edi_serial_no = oi.edi_container_serial_no";
        }

        $sql .= ",
        vv_public AS (
            SELECT {$needCustomerSQL} {$needteuSQL}  1
            FROM (select * from oo where oo.order_from = 'public') as oo 
            {$joinPublic}
            {$oeLateral}
            {$co2Lateral}
            {$pickupLateral}
            {$deliveryLateral}
            <{ocItemSearchKLN}>
        ),
        vv_sfs AS (
            SELECT {$needCustomerSQL} {$needteuSQL} 1
            FROM (select * from oo where oo.order_from = 'sfs') as oo 
            {$joinSfs}
            {$oeLateral}
            {$co2Lateral}
            <{ocItemSearchKLNSFS}>
        )
        SELECT COUNT(*) FROM (
            SELECT * FROM vv_public
            UNION ALL
            SELECT * FROM vv_sfs
        ) vv  <{vvSearchKLN}>;";

    return $sql;
    }

    public static function getSelectedArray($field_display_name)
    {
        if ($field_display_name == 'Transportation Mode'){
            return [['label'=>'Sea','value'=>'sea'],['label'=>'Air','value'=>'air'],['label'=>'Road','value'=>'road'],['label'=>'Rail','value'=>'rail']];
        }elseif($field_display_name == 'EX/IM'){
            return [['label'=>'Export','value'=>'Export'],['label'=>'Import','value'=>'Import']];
        }elseif($field_display_name == 'Status'){
            return [['label'=>'Created','value'=>'Created'],['label'=>'Cargo Received','value'=>'Cargo Received'],
                ['label'=>'Departed','value'=>'Departed'],['label'=>'Arrived','value'=>'Arrived'],['label'=>'Completed','value'=>'Completed']];
        }else{
            return [];
        }
    }

    /**
     * KLN Report Control: Contact_id and Group Name
    */
    public static function getKlnReportControl()
    {
        $data =["contactIdSqlWhere" => " OR 1<>1 ", "groupNameSqlWhere" => " OR 1<>1 "];
        if (_isCustomerLogin()){
            //contact_id判断
            $ocean_contact_id = _getCompanyContactHandNew($_SESSION["ONLINE_USER"]);
            $air_contact_id = _getAirContactID('public');
            $all_id = $ocean_contact_id;
            if (utils::endWith($ocean_contact_id,";")){
                $all_id .=$air_contact_id;
            } else {
                $all_id .=";".$air_contact_id;
            }
            $unique_arr = explode(';', $all_id);
            //构建安全的数组字面量用于 PostgreSQL 的 && 交集判断
            // 转义每个 ID 防注入
            $escaped_ids = array();
            foreach($unique_arr as $id){
                $id = trim($id);
                if (empty($id))
                    continue;
                $escaped_ids[] =  "'" .common::check_input($id) . "'";
            }
            if(empty($escaped_ids)){
                $escaped_ids[] =  "'" .common::check_input('default_value_that_does_not_exist') . "'";
            }
            $contactIdSqlWhereStr = 'ARRAY[' . implode(',', $escaped_ids) . ']';
            $data['contactIdSqlWhere'] = " OR party_ids && $contactIdSqlWhereStr ";

            //Group Name 判断
            $company = _getCompanyHandNew($_SESSION["ONLINE_USER"],'public');
            $aa = explode(";", $company);

            $escaped_company = array();
            foreach ($aa as $value) {
                $value = trim($value);
                if (empty($value))
                    continue;
                $escaped_company[] = "'" . common::check_input($value) . "'";
            }
            if(empty($escaped_company)){
                $escaped_company[] = "'" . common::check_input('default_value_that_does_not_exist') . "'";
            }
            $groupNameSqlWhereStr = 'ARRAY[' . implode(',', $escaped_company) . ']';
            $data['groupNameSqlWhere'] = "  OR group_names && $groupNameSqlWhereStr";
        } else {
            //contact_id判断
            $ocean_contact_id = $_SESSION["ONLINE_USER"]['employee_contact_id_user'];
            $air_contact_id = $_SESSION["ONLINE_USER"]['employee_air_contact_id_user'];
            $all_id = $ocean_contact_id;
            if (utils::endWith($ocean_contact_id,";")){
                $all_id .=$air_contact_id;
            } else {
                $all_id .=";".$air_contact_id;
            }
            $unique_arr = explode(';', $all_id);
            //构建安全的数组字面量用于 PostgreSQL 的 && 交集判断
            // 转义每个 ID 防注入
            $escaped_ids = array();
            foreach($unique_arr as $id){
                $id = trim($id);
                if (empty($id))
                    continue;
                $escaped_ids[] =  "'" .common::check_input($id) . "'";
            }
            if(empty($escaped_ids)){
                $escaped_ids[] =  "'" .common::check_input('default_value_that_does_not_exist') . "'";
            }
            $contactIdSqlWhereStr = 'ARRAY[' . implode(',', $escaped_ids) . ']';
            $data['contactIdSqlWhere'] = " OR party_ids && $contactIdSqlWhereStr ";

            //Group Name 判断
            $oceanCompany = $_SESSION["ONLINE_USER"]['employee_company_name'];
            $airCompany = $_SESSION["ONLINE_USER"]['employee_air_company_name'];
            $company = $oceanCompany;
            if (utils::endWith($oceanCompany,";")){
                $company .=$airCompany;
            } else {
                $company .=";".$airCompany;
            }
            $aa = explode(";", $company);

            $escaped_company = array();
            foreach ($aa as $value) {
                $value = trim($value);
                if (empty($value))
                    continue;
                $escaped_company[] = "'" . common::check_input($value) . "'";
            }
            if(empty($escaped_company)){
                $escaped_company[] = "'" . common::check_input('default_value_that_does_not_exist') . "'";
            }
            $groupNameSqlWhereStr = 'ARRAY[' . implode(',', $escaped_company) . ']';
            $data['groupNameSqlWhere'] = "  OR group_names && $groupNameSqlWhereStr";
        }

        return $data;
    }

    /**
     * 判断数组值为空
     */
    public static function isArrayAllEmpty($arr) {
        if(!is_array($arr)){
            $arr = array($arr);
        }
        foreach ($arr as $value) {
            if (trim((string)$value) !== '') {
                return false; // 只要有一个非空，就返回 false
            }
        }
        return true; // 全部为空
    }

    /**
     *  获取页面mapping
    */
    public static function getPageMapping($key){
        $pagemapping = ['Booking_Search'=>'booking','Booking_Container'=>'booking',
            'Ocean_Search'=>'tracking','Ocean_Container'=>'tracking',
            'shipment_status_report'=>'report',
            'Destination_Delivery_Search'=>'destinationDelivery',
            'destination_delivery_shipment_search'=>'destinationDelivery'];
        return $pagemapping[$key];
    }
}
?>