k_online_backend/utils/common.class.php

<?php

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

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

    /*
     * 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;
            }
            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)
                            && !(stripos($action, "system_setting") === 0)
                            && !(stripos($action, "monitoring_setting") === 0)
                            && !(stripos($action, "notifications_rules") === 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($schames);
            }
            
            if (strtolower($type) == "air") {
                $sqlWhere .= self::_air($schames);
            }

        } elseif (utils::count($schemas_list) == 2) {
            if(strtolower($type) == "air_booking" ){
                
            }else{
                $sqlWhere .= " and (COALESCE(schem_not_display,false) = false)";
            }
            if (strtolower($type) == "ocean") {
                $sqlWhere .= " and ((order_from='public' ";
                $sqlWhere .= self::_oceanHandNew($user, "public");
                if ($company_name == "doc") {
                    if (empty($user["view_file_format"])) {
                        if (strtolower($user["user_type"]) == "customer") {
                            $sqlWhere .= " and serial_no in (SELECT serial_no from public.ra_online_file_format where active = true and client_display = true)";
                        } else {
                            $sqlWhere .= " and serial_no in (SELECT serial_no from public.ra_online_file_format where active = true)";
                        }
                    } else {
                        $sqlWhere .= " and serial_no in (SELECT regexp_split_to_table('" . $user["view_file_format"] . "',';'))";
                    }
                }
                $sqlWhere .= ") or (order_from='sfs' ";

                if (empty($user["sfs_ONLINE_USER"])) {
                    $user = $_SESSION["sfs_ONLINE_USER"];
                }
                $sqlWhere .= self::_oceanHandNew($user, "sfs");
                if ($company_name == "doc") {
                    if (empty($user["view_file_format"])) {
                        if (strtolower($user["user_type"]) == "customer") {
                            $sqlWhere .= " and serial_no in (SELECT serial_no from sfs.ra_online_file_format where active = true and client_display = true)";
                        } else {
                            $sqlWhere .= " and serial_no in (SELECT serial_no from sfs.ra_online_file_format where active = true)";
                        }
                    } else {
                        $sqlWhere .= " and serial_no in (SELECT regexp_split_to_table('" . $user["view_file_format"] . "',';'))";
                    }
                }
                $sqlWhere .= ") )";
            }
            if (strtolower($type) == "booking") {
                $sqlWhere .= " and ((order_from='public' ";
                $sqlWhere .= self::_bookingHandNew($user, "public");
                $sqlWhere .= ") or (order_from='sfs' ";
                if (empty($user["sfs_ONLINE_USER"])) {
                    $user = $_SESSION["sfs_ONLINE_USER"];
                }
                $sqlWhere .= self::_bookingHandNew($user, "sfs");
                $sqlWhere .= "))";
            }
            if (strtolower($type) == "air_booking") {
                $sqlWhere .= " and ((order_from='public' ";
                $sqlWhere .= self::_air("public");
                $sqlWhere .= ") or (order_from='sfs' ";
                if (empty($user["sfs_ONLINE_USER"])) {
                    $user = $_SESSION["sfs_ONLINE_USER"];
                }
                $sqlWhere .= self::_air("sfs");
                $sqlWhere .= "))";
            }
            
            if (strtolower($type) == "air") {
                $sqlWhere .= " and ((order_from='public' ";
                $sqlWhere .= self::_air($user, "public");
                $sqlWhere .= ") or (order_from='sfs' ";

                if (empty($user["sfs_ONLINE_USER"])) {
                    $user = $_SESSION["sfs_ONLINE_USER"];
                }
                $sqlWhere .= self::_air($user, "sfs");
                $sqlWhere .= ") )";
            }
        } else {
            $sqlWhere = " 1<>1";
        }
        return $sqlWhere;
    }
    

    public static function getStatusStage($status) {
        if ($status == "EE" || $status == "I") {
            return 0;
        }
        if ($status == "AE" || $status == "VD" || $status == "VA_RELAY" || $status == "UV_RELAY" || $status == "AE_RELAY" || $status == "VD_RELAY") {
            return 1;
        }
        if ($status == "VA" || $status == "UV" || $status == "AL" || $status == "AR" || $status == "CU" || $status == "CT" || $status == "CR" || $status == "OA") {
            return 2;
        }
        if ($status == "AV" || $status == "D" || $status == "RD") {
            return 3;
        }
        return -1;
    }

    public static function getInNotInSql($contact_id, $type = 'in') {
        if (empty($contact_id))
            return " =''";
        $contact_id = trim($contact_id);
        $contact_id = trim($contact_id, ";");
        $contact_id = trim($contact_id);
        $contact_id = strtolower($contact_id);
        if ($type == 'in') {
            if (utils::checkExist($contact_id, ";")) {
                $ss = "";
                $aa = explode(";", $contact_id);
                foreach ($aa as $k => $v) {
                    $v = trim($v);
                    if (empty($ss))
                        $ss = "'" . common::check_input($v) . "'";
                    else
                        $ss .= ",'" . common::check_input($v) . "'";
                }
                return " in (" . $ss . ")";
            } else {
                return " = '" . common::check_input($contact_id) . "'";
            }
        } else {
            if (utils::checkExist($contact_id, ";")) {
                $ss = "";
                $aa = explode(";", $contact_id);
                foreach ($aa as $k => $v) {
                    $v = trim($v);
                    if (empty($ss))
                        $ss = "'" . common::check_input($v) . "'";
                    else
                        $ss .= ",'" . common::check_input($v) . "'";
                }
                return " in (" . $ss . ")";
            } else {
                return " != '" . common::check_input($contact_id) . "'";
            }
        }
    }

    public static function getInNotInSqlForSearch($contact_id, $type = 'in') {
        if (empty($contact_id))
            return " =''";
        $contact_id = trim($contact_id);
        $contact_id = trim($contact_id, ";");
        $contact_id = trim($contact_id);
        $contact_id = strtolower($contact_id);
        if ($type == 'in') {
            if (utils::checkExist($contact_id, ";")) {
                $ss = "";
                $aa = explode(";", $contact_id);
                foreach ($aa as $k => $v) {
                    $v = trim($v);
                    if (empty($ss))
                        $ss = "'" . common::check_input($v) . "'";
                    else
                        $ss .= ",'" . common::check_input($v) . "'";
                }
                return  $ss;
            } else {
                return "'" . common::check_input($contact_id) . "'";
            }
        }
    }

     /*
     * Encrypt a SQL query statement used to be passed as a parameter to get excel output
      encode  :DeCode('str','E');
      decode  :DeCode('enstr','D');
     */

     public static function deCode($string, $operation = "E") {
        $key = md5("uls_webwms");
        $key_length = strlen($key);

        if ($operation == "D")
            $string = rawurldecode($string);
        $string = $operation == 'D' ? base64_decode($string) : substr(md5($string . $key), 0, 8) . $string;
        $string_length = strlen($string);
        $rndkey = $box = array();
        $result = '';
        for ($i = 0; $i <= 255; $i++) {
            $rndkey [$i] = ord($key [$i % $key_length]);
            $box [$i] = $i;
        }
        for ($j = $i = 0; $i < 256; $i++) {
            $j = ($j + $box [$i] + $rndkey [$i]) % 256;
            $tmp = $box [$i];
            $box [$i] = $box [$j];
            $box [$j] = $tmp;
        }
        for ($a = $j = $i = 0; $i < $string_length; $i++) {
            $a = ($a + 1) % 256;
            $j = ($j + $box [$a]) % 256;
            $tmp = $box [$a];
            $box [$a] = $box [$j];
            $box [$j] = $tmp;
            $result .= chr(ord($string [$i]) ^ ($box [($box [$a] + $box [$j]) % 256]));
        }

        if ($operation == 'D') {
            if (substr($result, 0, 8) == substr(md5(substr($result, 8) . $key), 0, 8)) {
                return substr($result, 8);
            } else {
                return '';
            }
        } else {
            return rawurlencode(str_replace('=', '', base64_encode($result)));
        }
    }
    
    public static function getStatusDesc($code) {
        if (strtoupper($code) == "EE") {
            return "Empty Equipment Dispatched";
        }
        if (strtoupper($code) == "I") {
            return "Gate in full for a booking";
        }
        if (strtoupper($code) == "AE") {
            return "Container loaded on vessel";
        }
        if (strtoupper($code) == "VD") {
            return "Vessel Departure";
        }
        if (strtoupper($code) == "VA_RELAY") {
            return "Arrive Relay Port";
        }
        if (strtoupper($code) == "VD_RELAY") {
            return "Depart Relay Port";
        }
        if (strtoupper($code) == "AE_RELAY") {
            return "Loaded at Relay Port";
        }
        if (strtoupper($code) == "UV_RELAY") {
            return "Unloaded at Relay Port";
        }
        if (strtoupper($code) == "VA") {
            return "Vessel Arrival";
        }
        if (strtoupper($code) == "UV") {
            return "Unloaded From Vessel";
        }
        if (strtoupper($code) == "AL") {
            return "Container loaded on Rail";
        }
        if (strtoupper($code) == "AR") {
            return "Container unloaded from Rail";
        }
        if (strtoupper($code) == "CU") {
            return "Carrier and Customs Release";
        }
        if (strtoupper($code) == "CT") {
            return "Customs release";
        }
        if (strtoupper($code) == "CR") {
            return "Carrier release";
        }
        if (strtoupper($code) == "OA") {
            return "Gate out full from final discharge port";
        }
        if (strtoupper($code) == "AV") {
            return "Shipment available for pickup or delivery";
        }
        if (strtoupper($code) == "RD") {
            return "Container returned empty";
        }
        if (strtoupper($code) == "D") {
            return "Gate out for delivery to customer";
        }
    }

    public static function _toString($msg) {
        if ($msg == "" || $msg == NULL) {
            return "";
        }
        return $msg . "";
    }

    /*
     *  timeout output
     */

     public static function timeoutPrintInfor($httpAccept, $ajax, $login) {
        //记录系统因session过期 退出登录
        $data = array("msg"=>"session_time_out");
        self::echo_json_encode(403,$data);
        exit();
    }

    /**
     * _bookingHandNew 和 _oceanHandNew逻辑完全一样，只是ocean 多一个dest_op字段，在booking里没有的
    */
    private static function _oceanHandNew($user, $schemas = "public") {
        $o = $user['ocean_station'];
        $o_or = $user['ocean_station_or'];
        $d = $user['ocean_agent'];
        $d_or = $user['ocean_agent_or'];
        $sales = $user['ocean_sales'];
        $sales_or = $user['ocean_sales_or'];
        $op = $user['ocean_dest_op'];
        $op_or = $user['ocean_dest_op_or'];
        $follow = $user['ocean_following_sales'];
        $follow_or = $user['ocean_following_sales_or'];

        if (strtolower($o_or) == "all" || strtolower($d_or) == "all" || strtolower($sales_or) == "all" || strtolower($op_or) == "all") {
            return " and 1=1";
        }
        $sqlWhere = "";
        if (_isCustomerLoginHandNew($user)) {
            $sqlWhere .= " and " . _customerFilerSearchHandNew($user, $schemas);
        } else {
            if (empty($o) && empty($d) && empty($sales) && empty($op) && empty($follow)) {
                return " and 1<>1";
            }
            if ((strtolower($o) == 'all' || empty($o)) && (strtolower($d) == "all" || empty($d))) {
                
            } else {
                $sql = "1=1";
                if (!empty($o) && strtolower($o) != 'all') {
                    $sql .= " and lower(origin)";
                    $sql .= utils::getInSql($o);
                }
                if (!empty($d) && strtolower($d) != 'all') {
                    $sql .= " and lower(agent)";
                    $sql .= utils::getInSql($d);
                }
                $sqlWhere .= " and (" . $sql . ")";
            }
            if (strtolower($sales) == 'all' || empty($sales)) {
                
            } else {
                if (utils::checkExist($sales, ";")) {
                    $sql = "1!=1";
                    $tt = explode(";", $sales);
                    foreach ($tt as $t) {
                        $t = trim($t);
                        if (!empty($t))
                            $sql .= " or lower(sales_rep)='" . strtolower($t) . "'";
                    }
                    $sqlWhere .= " and ($sql)";
                } else
                    $sqlWhere .= " and lower(sales_rep)='" . strtolower($sales) . "'";
            }

            if (strtolower($op) == 'all' || empty($op)) {
                
            } else {
                $sqlWhere .= " and lower(dest_op) " . utils::getInSql($op);
            }

            if (strtolower($follow) == 'all' || empty($follow)) {
                
            } else {
                if (utils::checkExist($follow, ";")) {
                    $sql = "1!=1";
                    $tt = explode(";", $follow);
                    foreach ($tt as $t) {
                        $t = trim($t);
                        if (!empty($t))
                            $sql .= " or following_sales ilike '" . $t . "'";
                    }
                    $sqlWhere .= " and ($sql)";
                } else
                    $sqlWhere .= " and following_sales ilike '" . $follow . "'";
            }
            $sqlWhere = " (1=1 $sqlWhere)";
            if (!empty($o_or)) {
                $sqlWhere .= " or lower(origin)";
                $sqlWhere .= utils::getInSql($o_or);
            }
            if (!empty($d_or)) {
                $sqlWhere .= " or lower(agent)";
                $sqlWhere .= utils::getInSql($d_or);
            }
            if (!empty($sales_or)) {
                if (utils::checkExist($sales_or, ";")) {
                    $sql = "1!=1";
                    $tt = explode(";", $sales_or);
                    foreach ($tt as $t) {
                        $t = trim($t);
                        if (!empty($t))
                            $sql .= " or lower(sales_rep)='" . strtolower($t) . "'";
                    }
                    $sqlWhere .= " or ($sql)";
                } else
                    $sqlWhere .= " or lower(sales_rep)='" . strtolower($sales_or) . "'";
            }
            if (!empty($op_or)) {
                $sqlWhere .= " or lower(dest_op) " . utils::getInSql($op_or);
            }
            if (!empty($follow_or)) {
                if (utils::checkExist($follow_or, ";")) {
                    $sql = "1!=1";
                    $tt = explode(";", $follow_or);
                    foreach ($tt as $t) {
                        $t = trim($t);
                        if (!empty($t))
                            $sql .= " or following_sales ilike '" . $t . "'";
                    }
                    $sqlWhere .= " or ($sql)";
                }else {
                    $sqlWhere .= " or following_sales ilike '" . $follow_or . "'";
                }
            }

            $sqlWhere = " and ($sqlWhere)";
        }

        return $sqlWhere;
    }

    /**
     * _bookingHandNew 和 _oceanHandNew逻辑完全一样，只是ocean 多一个dest_op字段，在booking里没有的
    */
    private static function _bookingHandNew($user, $schames) {
                $o = $user['ocean_station'];
                $o_or = $user['ocean_station_or'];
                $d = $user['ocean_agent'];
                $d_or = $user['ocean_agent_or'];
                $sales = $user['ocean_sales'];
                $sales_or = $user['ocean_sales_or'];
                $op = $user['ocean_dest_op'];
                $op_or = $user['ocean_dest_op_or'];
                $follow = $user['ocean_following_sales'];
                $follow_or = $user['ocean_following_sales_or'];
        
                if (strtolower($o_or) == "all" || strtolower($d_or) == "all" || strtolower($sales_or) == "all") {
                    return " and 1=1";
                }

                $sqlWhere = "";
                if (_isCustomerLoginHandNew($user)) {
                    $sqlWhere .= " and " . _customerFilerSearchHandNew($user, $schames);
                } else {
                    if (empty($o) && empty($d) && empty($sales) && empty($op) && empty($follow)) {
                        return " and 1<>1";
                    }
                    if ((strtolower($o) == 'all' || empty($o)) && (strtolower($d) == "all" || empty($d))) {
                        
                    } else {
                        $sql = "1=1";
                        if (!empty($o) && strtolower($o) != 'all') {
                            $sql .= " and lower(origin)";
                            $sql .= utils::getInSql($o);
                        }
                        if (!empty($d) && strtolower($d) != 'all') {
                            $sql .= " and lower(agent)";
                            $sql .= utils::getInSql($d);
                        }
                        $sqlWhere .= " and (" . $sql . ")";
                    }
                    if (strtolower($sales) == 'all' || empty($sales)) {
                        
                    } else {
                        if (utils::checkExist($sales, ";")) {
                            $sql = "1!=1";
                            $tt = explode(";", $sales);
                            foreach ($tt as $t) {
                                $t = trim($t);
                                if (!empty($t))
                                    $sql .= " or lower(sales_rep)='" . strtolower($t) . "'";
                            }
                            $sqlWhere .= " and ($sql)";
                        } else
                            $sqlWhere .= " and lower(sales_rep)='" . strtolower($sales) . "'";
                    }
                    if (strtolower($follow) == 'all' || empty($follow)) {
                        
                    } else {
                        if (utils::checkExist($follow, ";")) {
                            $sql = "1!=1";
                            $tt = explode(";", $follow);
                            foreach ($tt as $t) {
                                $t = trim($t);
                                if (!empty($t))
                                    $sql .= " or following_sales ilike '" . $t . "%'";
                            }
                            $sqlWhere .= " and ($sql)";
                        } else
                            $sqlWhere .= " and following_sales ilike '" . $follow . "%'";
                    }
        
                    $sqlWhere = " (1=1 $sqlWhere)";
                    if (!empty($o_or)) {
                        $sqlWhere .= " or lower(origin)";
                        $sqlWhere .= utils::getInSql($o_or);
                    }
                    if (!empty($d_or)) {
                        $sqlWhere .= " or lower(agent)";
                        $sqlWhere .= utils::getInSql($d_or);
                    }
                    if (!empty($sales_or)) {
                        if (utils::checkExist($sales_or, ";")) {
                            $sql = "1!=1";
                            $tt = explode(";", $sales_or);
                            foreach ($tt as $t) {
                                $t = trim($t);
                                if (!empty($t))
                                    $sql .= " or lower(sales_rep)='" . strtolower($t) . "'";
                            }
                            $sqlWhere .= " or ($sql)";
                        } else
                            $sqlWhere .= " or lower(sales_rep)='" . strtolower($sales_or) . "%'";
                    }
                    if (!empty($follow_or)) {
                        if (utils::checkExist($follow_or, ";")) {
                            $sql = "1!=1";
                            $tt = explode(";", $follow_or);
                            foreach ($tt as $t) {
                                $t = trim($t);
                                if (!empty($t))
                                    $sql .= " or following_sales ilike '" . $t . "%'";
                            }
                            $sqlWhere .= " or ($sql)";
                        } else
                            $sqlWhere .= " or following_sales ilike '" . $follow_or . "%'";
                    }
        
                    $sqlWhere = " and ($sqlWhere)";
                }
                return $sqlWhere;
    }

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

        $sqlWhere = " 1=1 ";
        if (_isCustomerLogin()) {
            $sqlWhere .= " and " . _customerAirFilerSearch($schemas);
        } else {
            if (empty($station) && empty($sales)) {
                return "and 1<>1";
            }
            if (strtolower($station_or) == "all" || strtolower($sales_or) == "all")
                        return " and ($sqlWhere)";
        
            if (strtolower($station) == 'all' || empty($station)) {
                        
            } else {
                $sqlWhere .= " and (lower(origin)";
                $sqlWhere .= utils::getInSql($station);
                $sqlWhere .= " or lower(agent)";
                $sqlWhere .= utils::getInSql($station);
                $sqlWhere .= ")";
            }
            if (strtolower($sales) == 'all' || empty($sales)) {
                        
            } else {
                $sqlWhere .= " and (lower(sales_rep) " . utils::getInSql($sales) . " or lower(following_sales) " . utils::getInSql($sales) . ")";
            }
            $sqlWhere = " ($sqlWhere)";
            if (!empty($station_or))
                $sqlWhere = "$sqlWhere or lower(origin)" . utils::getInSql($station_or) . " or lower(agent)" . utils::getInSql($station_or);
            if (!empty($sales_or))
                $sqlWhere = "$sqlWhere or lower(sales_rep)" . utils::getInSql($sales_or) . " or lower(following_sales)" . utils::getInSql($sales_or);
        }
        return " and ($sqlWhere)";
    }

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

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

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

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

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

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

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

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

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

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

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

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

    /*
     * download file from file system
     */

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

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

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

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

        return $extend;
    }

    public static function getManagement(){
        //Management 自己配置需要创建表保存
        $user_management_data = common::excuteObjectSql("select management,dashboard_filter from ra_online_user where user_login = '"._getLoginName()."'");
        //为空,配置使用默认值
        $user_management = $user_management_data['management'];
        if(empty($user_management)){
            $Management = common::getdefaultManagement();
        }else{
            $Management = json_decode($user_management,true);
            foreach($Management as $key =>$val){
                $Management[$key]['id'] = intval($val['id']);
                $Management[$key]['switchValue'] = $val['switchValue'] == "true" ? true : false;
            }
        }
        $user_dashboard_filter = $user_management_data['dashboard_filter'];
        $dashboard_filter  =array();
        if(!empty($user_dashboard_filter)){
            $dashboard_filter = json_decode($user_dashboard_filter,true);
        }else{
            $threeMonthsAgoTimestamp = strtotime('-3 months');
            $date_start = date('Y-m-d', $threeMonthsAgoTimestamp);
            $date_end = date('Y-m-d');
            $date_start_two = date('m/d/Y', $threeMonthsAgoTimestamp);
            $date_end_two = date('m/d/Y');

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

        $transportation = $_REQUEST["transportation"];
        if(!is_array($transportation)){
            $transportation = array($transportation);
        }
        $mode_param = "";
        if(utils::count($transportation) == 1 && strtolower($transportation[0]) == 'all' ){
            $mode_param = "'sea','air','road'";         
        }else{
            $transport_mode = utils::implode(';', $transportation);
            $mode_param = common::getInNotInSqlForSearch($transport_mode);
        }
        $sqlWhere .= " and transport_mode in ($mode_param)";    
         
        $toporiginType = "shippr_uncode";
        $shippr_uncode_10_sql ="with aa as  (select count(shippr_uncode) as num,shippr_uncode,
                                (array_agg(shipper_city))[1] AS shipper_city from public.kln_ocean  
                        where 1=1 and COALESCE(shippr_uncode,'')<>'' $sqlWhere group by shippr_uncode order by num desc limit 10)
            select   aa.* from  aa ";
   
        $shippr_uncode_10 = $mapdb->GetAll($shippr_uncode_10_sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $shippr_uncode_10_sql), 0));
        //如果值没有：客户地址-->站点地址-->Port地址（POL/POD） 
        if(utils::count($shippr_uncode_10) == 1 && empty($shippr_uncode_10[0]["shippr_uncode"])){
            $toporiginType = "fport_of_loading_un";
            $shippr_uncode_10_sql = "with aa as  (select count(fport_of_loading_un) as num,fport_of_loading_un as shippr_uncode 
                    from public.kln_ocean where 1=1 and COALESCE(fport_of_loading_un,'')<>'' $sqlWhere group by fport_of_loading_un order by num desc limit 10)
                    select   aa.* from  aa ";
            $shippr_uncode_10 = $mapdb->GetAll($shippr_uncode_10_sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $shippr_uncode_10_sql), 0));
        }
       
        $topdestinationinType = "consignee_uncode";
        $consignee_uncode_10_sql = "with aa as  (select count(consignee_uncode) as num,consignee_uncode,
                    (array_agg(consignee_city))[1] AS consignee_city 
                from public.kln_ocean  where 1=1  and COALESCE(consignee_uncode,'')<>''  $sqlWhere group by consignee_uncode order by num desc limit 10)
        select   aa.* from  aa";

        $consignee_uncode_10 = $mapdb->GetAll($consignee_uncode_10_sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $consignee_uncode_10_sql), 0));
        //如果值没有：客户地址-->站点地址-->Port地址（POL/POD） 
        if(utils::count($consignee_uncode_10) == 1 && empty($consignee_uncode_10[0]["consignee_uncode"])){
            $topdestinationinType = "mport_of_discharge_un";
            $consignee_uncode_10_sql = "with aa as  (select count(mport_of_discharge_un) as num,mport_of_discharge_un as consignee_uncode from public.kln_ocean where 1=1 and COALESCE(mport_of_discharge_un,'')<>'' $sqlWhere group by mport_of_discharge_un order by num desc limit 10)
                select   aa.* from  aa";
            $consignee_uncode_10 = $mapdb->GetAll($consignee_uncode_10_sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $consignee_uncode_10_sql), 0));
        }

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

        $topdestination = array();
        $topdestinationinMap = array();
        $i = 0;
        $agentiNunMax = 0;
        foreach($consignee_uncode_10 as $agent){
            $i = $i + 1;
            $map =common::excuteObjectSql("select lon as lng, lat as lat,
                    '' as label, '' as infor, 3 as sort,
                    null::timestamp without time zone as stime,''::text as ptype 
                from vessel.vt_unlocode where (lat<=90 and lat>=-90) and (lon<=180 and lon>=-180) and lon is not null and lat is not null and uncode='".$agent['consignee_uncode']."' ");
            
            // $json = '{"lng":"100.78594000","lat":"13.68521000","label":"'.$agent['agent'].'","infor":"LAT KRABANG, THAILAND","sort":"0","stime":null,"ptype":"por"}';
            // $map = json_decode($json,true);
            if(!empty($map)){
                $topdestinationinMap[] = array("qandl"=>array(floatval($map['lat']),floatval($map['lng'])),
                    "divIcon" => array("iconSize"=>0),
                    "name" =>$agent['consignee_uncode'],
                    "color" =>common::getItemStyle("top",$i),
                    "value" =>$agent['num'],
                    "textcolor" =>"#FFF");
            }

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

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

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

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

    public static function getRevenue(){
        //$data = '{"bar_title":"Total: 0","barList":["DEC,23","JAN,24","FEB,24","MAR,24","APR,24","MAY,24","JUN,24","JUL,24","AUG,24","SEP,24","OCT,24","NOV,24"],"barSeries":[{"name":"USD","type":"bar","data":[1,1,1,1,1,1,1,1,1,1,1,1],"emphasis":{"disabled":true,"focus":"none"},"itemStyle":{"color":"#FF7500","borderRadius":6},"label":{"show":false,"color":"#646A73","position":"top","fontFamily":"Lato-Light","hideWhenMouseLeave":false}},{"name":"THB","type":"bar","data":[2,2,2,2,2,2,2,2,2,2,2,2],"emphasis":{"disabled":true,"focus":"none"},"itemStyle":{"color":"#FFAC66","borderRadius":6},"label":{"show":false,"color":"#646A73","position":"top","fontFamily":"Lato-Light","hideWhenMouseLeave":false}},{"name":"SGD","type":"bar","data":[3,3,3,3,3,3,3,3,3,3,3,3],"emphasis":{"disabled":true,"focus":"none"},"itemStyle":{"color":"#FFE3CC","borderRadius":6},"label":{"show":false,"color":"#646A73","position":"top","fontFamily":"Lato-Light","hideWhenMouseLeave":false}}],"Max":5,"interval":1,"download_name":"Rvenue","isShowTooltips":true}';   
        //return json_decode($data,true);
        $date_from = null;
        $date_to = null;
        if (isset($_REQUEST['date_start']) && !empty($_REQUEST['date_start'])){
            $date_start = common::dateFormatToYM($_REQUEST['date_start']);
            $date_from = $date_start."-01";
        }
        if (isset($_REQUEST['date_end']) && !empty($_REQUEST['date_end'])){
            $date_end = common::dateFormatToYM($_REQUEST['date_end']);
            $date_to = date('Y-m-t', strtotime($date_end."-01"));
        }
        $param = '{"user_login":"'._getLoginName().'","report_type":"ALL","date_from":"'.$date_from.'","date_to":"'.$date_to.'"}';
        $sql = "SELECT * FROM get_customer_revenue_report('$param');FETCH ALL FROM r2;";
        error_log("Revenue_sql:".$sql);
        $r2_data = common::excuteListSql($sql);
        //$r2_data = common::excuteListSql("select * from _test_data_table");

        $barList = array();
        $groupedItems = array();
        foreach ($r2_data as $item) {
            if(!utils::in_array($item['month'],$barList)){
                $barList[] = $item['month'];
            }
            $groupedItems[$item['currency']][] = $item;
        }

        //补齐所有货币的月份
        foreach($groupedItems as $ckey =>$cvalueArr){
            $currency = array();
            foreach($barList as $month){
                $currency[] = common::findCurrencyByMonth($month,$ckey,$cvalueArr);
            }
            $groupedItems[$ckey] = $currency;
        }

        //处理barSeries
        $barSeries = array();
        //先准备4种颜色
        $color = array("#FF7500","#FFAC66","#FFE3CC");
        $index = 0;
        $max = 0;
        foreach($groupedItems as $currency => $series){
            $series_max = 0;
            $num = $index % 3;
            $index = $index +1; 
            $tem_series = array();
            $tem_series["name"] = $currency;
            $tem_series["type"] = "bar";
            $tem_series["emphasis"] = array("disabled"=>true,"focus"=>"none");
            $tem_series["itemStyle"] = array("color"=>$color[$num],"borderRadius"=>6);
            $tem_series["label"] = array("show"=>false,"color"=>"#646A73","position"=>"top","fontFamily"=>"Lato-Light","hideWhenMouseLeave"=>false);
            //准备series data
            $data = array();
            $total = 0;
            foreach($series as $_series){
                $invoice_amount = empty($_series['invoice_amount'])? 0 : round($_series['invoice_amount'], 4);
                $data[]=$invoice_amount;
                $total = $total + $invoice_amount;
                $max = $max<$_series['invoice_amount'] ? $_series['invoice_amount'] : $max;
                $series_max = $series_max<$_series['invoice_amount'] ? $_series['invoice_amount'] : $series_max;
            }

            //计算Y坐标的间隔 每种货币的
            $interval = utils::calculateTicks(0,$series_max,5);
            if($interval == 0){
                //处理返回默认值
                $interval = 2;
            }
            $tem_series["Max"] = $interval*5;
            $tem_series["interval"] = $interval;
            $tem_series["data"] = $data;
            $tem_series["total"] = floatval(number_format($total, 4, '.', ''));
            $barSeries[] = $tem_series;
        }

        //计算Y坐标的间隔
        $interval = utils::calculateTicks(0,$max,5);
        if($interval == 0){
            //处理返回默认值
            $interval = 2;
        }

        $revenueBarData = array("bar_title" =>"Total:","barList" => $barList,
            "barSeries" =>$barSeries,"download_name"=>"Rvenue","isShowTooltips"=>true,"Max" =>$interval*5,"interval" =>$interval);
        return $revenueBarData;  
    }

    public static function findCurrencyByMonth($month,$ckey,$cvalueArr){
        $data = array("month" =>$month,"currency"=>$ckey,"invoice_amount"=>0,"sort_month"=>"");
        foreach($cvalueArr as $realmonth){
            if($realmonth['month'] == $month){
                $data = $realmonth;
            }
        }
        return $data;
    }

    public static function getdefaultManagement(){
        $Management = array();
        $Management[] = array("id"=>1 ,
            "title"=>"KPI",
            "switchValue"=>true,
            "text"=>"Pie chart showing figures of shipments KPI of Departure and Arrival.");
        $Management[] = array("id"=>2 ,
            "title"=>"Pending",
            "switchValue"=>true,
            "text"=>"Pie chart showing figures of shipments which are soon to depart/arrive (Calculated from ETD/ETA).");
        $Management[] = array("id"=>3 ,
            "title"=>"ETD to ETA (Days)",
            "switchValue"=>true,
            "text"=>"Pie chart showing figures of shipments which are soon to depart/arrive (Calculated from ETD/ETA).");
        $Management[] = array("id"=>4 ,
            "title"=>"Container Count",
            "switchValue"=>true,
            "text"=>"Pie chart showing figures of shipments which are soon to depart/arrive (Calculated from ETD/ETA).");
        if(_isRevenueDisplay()){    
            $Management[] = array("id"=>5 ,
                "title"=>"Revenue Spent",
                "switchValue"=>true,
                "text"=>"Revenue data for the past 12 months."); 
        }   
        $Management[] = array("id"=>6 ,
            "title"=>"Top 10 Origin/Destination",
            "switchValue"=>true,
            "text"=>"Figure of the top 10 origin/destination.",
            "title1"=>"Top 10 Origin",
            "title2"=>"Top 10 Destination");
        $Management[] = array("id"=>7 ,
            "title"=>"CO2e Emission by Origin (Top 10)",
            "switchValue"=>true,
            "text"=>"Figure of the CO2e Emission by origin.");
        $Management[] = array("id"=>8 ,
            "title"=>"CO2e Emission by Destination (Top 10)",
            "switchValue"=>true,
            "text"=>"Figure of the CO2e Emission by destination."); 
        $Management[] = array("id"=>9 ,
            "title"=>"Recent Status",
            "switchValue"=>true,
            "text"=>"A shipment list with latest status update on top."); 
        return  $Management;   
    }

    /**
     * Destroy Session
     */
    public static function sessionDestroy() {
        session_destroy();
        setcookie(session_name(), '', time() - 3600);
        $_SESSION = array();
    }


    public static function dateFormatToYM($dateString){
        $date = DateTime::createFromFormat('m/Y', $dateString);
        $formattedDate = $date->format('Y-m'); // YYYY-MM
        return $formattedDate;
    }

    public static function retStationInfo($address_1,$address_2,$address_3,$address_4,$city,$state,$country,$zipcode){
        $stationInfo = "";
        if(!empty($address_1)){
            $stationInfo .= $address_1;
        }
        if(!empty($address_2)){
            $stationInfo .= " ".$address_2;
        }
        if(!empty($address_3)){
            $stationInfo .= " ".$address_3;
        }
        if(!empty($address_4)){
            $stationInfo .= " ".$address_4;
        }
        $temp_str  = "";
        if(!empty($city)){
            $temp_str .= $city." ";
        }
        if(!empty($state)){
            $temp_str .= $state." ";
        }
        if(!empty($zipcode)){
            $temp_str .= $zipcode." ";
        }
        if(!empty($country)){
            $temp_str .= $country." ";
        }
        if(!empty($temp_str)){
            return $stationInfo." ".trim($temp_str);
        }
        return $stationInfo;
    }

    public static function getInsertSqlNull($table_name, $values) {
        $field = "";
        $value = "";
        foreach ($values as $k => $v) {
            if ($k == 'tmp' || $k == 'action' || $k == 'operate' || $k == 'x' || $k == 'y') {
                continue;
            }
            if (is_array($v)) {
                $v = utils::implode(",", $v);
            }
            if (empty($field)) {
                $field = $k;
                if (utils::checkExist($v, 'now()')) {
                    $value = $v;
                } elseif ($v == null) {
                    $value .= 'null';
                } elseif ($v == 'TRUE' || $v == 'FALSE') {
                    $value .= $v;
                } else {
                    $value = '\'' . common::check_input($v) . '\'';
                }
            } else {
                $field .= ',' . $k;
                if (utils::checkExist($v, 'now()'))
                    $value .= ', now()';
                elseif ($v == null) {
                    $value .= ',null';
                } else if ($v == 'TRUE' || $v == 'FALSE') {
                    $value .= ',' . $v;
                } else {
                    $value .= ', \'' . common::check_input($v) . '\'';
                }
            }
        }
        return 'insert into ' . $table_name . '(' . $field . ') values (' . $value . ')';
    }

    //密码规则验证
    public static function checkPasswordRule($login, $new_password){
        $sql="select item_value from config where item='passwordCheckRules'";
        $rs = common::excuteObjectSql($sql);

        $str =  "";
        if (!empty($rs)) {
            $passwordCheckRules = json_decode($rs["item_value"],true);
            //校验使用次数
            if (!empty($passwordCheckRules["pastPasswordCheckNum"])) {
                $sql = "select password from public.ra_online_user_password_history where lower(user_login)='".common::check_input(strtolower($login))."' order by id desc limit ".$passwordCheckRules["pastPasswordCheckNum"];
                $passwords = common::excuteListSql($sql);
                foreach ($passwords as $pwd) {
                    if ($pwd['password'] == $new_password) {
                        $str = "This password has been recently used";
                    }
                }
            }
            if(empty($str)){
                $str = utils::checkPassword($new_password,$passwordCheckRules,$login);
            }
        }else{
            $str = utils::checkPassword($new_password);
        }

        return $str;
    }

    public static function getMilestonesInfo($ocean,$transport_mode,$_schemas,$EDI315TimeAndLocation=array()){
        //Milestones info列名固定
        $Milestones_column = array();
        $Milestones_column[] = array("title" =>"Milestones","field" =>"milestones","formatter" =>"normal","type" =>"normal");
        $Milestones_column[] = array("title" =>"Date Time","field" =>"date_time","formatter" =>"dateTime","type" =>"normal");
        $Milestones_column[] = array("title" =>"Locations","field" =>"locations","formatter" =>"normal","type" =>"normal");
        $Milestones_column[] = array("title" =>"Remarks","field" =>"remarks","formatter" =>"normal","type" =>"normal");

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

        return $Milestones;
    }

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

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

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

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

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

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

        return $Milestones_NO_Mapping;
    }

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

            //Container_Status　新版只显示几个状态， CTNR# EE Ｉ AE VD VA
            $container_status_column = array("EE","I","AE","VD","VA");
            $containerStatusDataTemp = array();
            $content = array();
            //按顺序处理 新版只显示几个状态
            //foreach($container_status_column as $status){
                foreach($ctnr_status as $event){
                    //if(strtolower($event['event']) == strtolower($status)){
                        $eventdate = $event["eventdate"];
                        if (empty($event["source_id"]) || $event["source_id"]==0) {
                            $eventdate.= " " . $event["eventtime"];
                        }
                        $location_code = $event["eventcode"];
                        $content[] = array("code" =>$event['event'],"title" =>$event['description'],"date"=>$eventdate,"country"=>$location_code,"timezone"=>$event['timezone']);
                        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['content'] = $content;
            $containerStatusData[] = $containerStatusDataTemp;
        }
        $data['containerStatusData'] = $containerStatusData;
        $data['EDI315TimeAndLocation'] = $EDI315TimeAndLocation;
        $data['VD'] = $VD;
        $data['VA'] = $VA;
        $data['VD_RELAY'] = $VD_RELAY;
        $data['VA_RELAY'] = $VA_RELAY;
        return $data;
    }

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

    //预加载写法
    public static function 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;
        $sequence = sprintf("%05d", $sequence);
        return $sequence;
    }

    /**
     * 处理chat 返回的json字符串
    */
    public static function getChatAimessage($message){
       //格式:"```json ```"
       return  str_replace(["```json", "```"], "", $message);
    }

    public static function extractSelectFields($sql){
        // 正则表达式匹配 SELECT 和 FROM 之间的内容
        if (preg_match('/SELECT\s+(.*?)\s+FROM/si', $sql, $matches)) {
            $selectPart = $matches[1];

            // 去除注释和换行
            $selectPart = preg_replace('/--.*$/m', '', $selectPart); // 去除单行注释
            $selectPart = preg_replace('/\/\*.*?\*\//s', '', $selectPart); // 去除多行注释
            $selectPart = str_replace(["\r", "\n"], ' ', $selectPart); // 换行替换成空格

            // 分割字段
            $fields = [];
            foreach (explode(',', $selectPart) as $field) {
                $field = trim($field);
                if (empty($field)) continue;

                // 匹配 AS 别名
                if (preg_match('/(?:AS\s+)?(\w+)$/i', $field, $aliasMatch)) {
                    // 如果有别名，则使用别名
                    $fields[] = strtolower($aliasMatch[1]);
                } else {
                    // 否则直接添加字段名
                    $fields[] = strtolower(trim($field));
                }
            }

            return $fields;
        }

        return []; // 如果没有找到 SELECT 字段
    }

    public static function getUserCountry(){
        $contact_id = $_SESSION['ONLINE_USER']['contact_id'];
        if(empty($contact_id)){
            return "claude";
        }

        $model = "deepseek";
        $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";
            }
        }
        $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_PAHT."tracking/detail?a=".$serial_no."&_schemas=".$d["order_from"];
                    $data[$key]['action'] = '<a href="'.$httpUrl.'" target="_blank">Details</a> <a href="'.SERVER_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_PAHT."tracking/detail?a=".$serial_no."&_schemas=".$d["order_from"];
                    $temp['action'] = '<a href="'.$httpUrl.'" target="_blank">Details</a> <a href="SystemSettings" target="_blank">Notify</a>';
                    $refer_data[] = $temp;
                }
                $data = $refer_data;
            }
            $reference = utils::replacementsFixedMultilineForFixed($data,$reference,$fixedChat['table_format_tr']);
            //$data_download = "main_new_version.php?action=ocean_order&operate=download&temp=". common::deCode($fixedChat["fixed_faq"]);
        }

        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_PAHT."tracking/detail?a=".$serial_no."&_schemas=".$d["order_from"];
                $temp['action'] = '<a href="'.$httpUrl.'" target="_blank">Details</a> <a href="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 => $count){
                $timeline_view.="- ".$count['update_date_format'].": ".$count['total_count']." shipments reached milestones \n";
            }
            $total = array("Timeline View" =>$timeline_view);
            $reference = utils::replacementsFixed($total,$reference,array("Timeline View"));

            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,[]);
            }

            //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 H:i:s");
                $temp['uncity'] = $d['uncity'];

                $serial_no = common::deCode($d['serial_no'], 'E');
                $httpUrl = SERVER_PAHT."tracking/detail?a=".$serial_no."&_schemas=".$d["order_from"];
                $temp['action'] = '<a href="'.$httpUrl.'" target="_blank">Details</a> <a href="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 => $count){
                $timeline_view.="- ".$count['_eventdate'].": ".$count['total_count']." containers have been updated \n";
            }
            $total = array("Timeline View" =>$timeline_view);
            $reference = utils::replacementsFixed($total,$reference,array("Timeline View"));
        }

        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 H:i:s");
                $temp['locations'] = $d['locations'];

                $serial_no = common::deCode($d['serial_no'], 'E');
                $httpUrl = SERVER_PAHT."tracking/detail?a=".$serial_no."&_schemas=".$d["order_from"];
                $temp['action'] = '<a href="'.$httpUrl.'" target="_blank">Details</a> <a href="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,[]);
        }

        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"]);
            }

            $sqlDetail = $sql;
            //拼接用户权限
            $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_PAHT."tracking/detail?a=".$serial_no."&_schemas=".$d["order_from"];
                $temp['action'] = '<a href="'.$httpUrl.'" target="_blank">Details</a> <a href="SystemSettings" target="_blank">Notify</a>';
                $refer_data[] = $temp;
            }
            $data = $refer_data;
            $reference = utils::replacementsFixedMultilineForFixed($data,$fixedChat['answer_style'],$fixedChat['table_format_tr']);
        }

        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;
    }
    
    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;
    }
}
?>