k_online_backend/utils/utils.class.php

<?php

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

/**
 * Description of utilsclass
 *
 * @author Administrator
 */
class utils {

    public static function checkPassword($password,$rule="",$user_login="") {
        $str ="";
        if (!empty($rule)) {
            //是否校验大小写
            if (!empty($rule["hasOneUpperChar"])&&$rule["hasOneUpperChar"]) {
                if (!preg_match('/[A-Z]/',$password)) {
                    $str ="Password must contain uppercase letters";
                }
            }
            //是否校验小写
            if (!empty($rule["hasOneLowerChar"])&&$rule["hasOneLowerChar"]) {
                if (!preg_match('/[a-z]/',$password)) {
                    $str ="Password must contain lowercase letters";
                }
            }
            //是否存在数字
            if (!empty($rule["hasOneNumberChar"])&&$rule["hasOneNumberChar"]) {
                if (!preg_match('/[0-9]/',$password)) {
                    $str ="Password must contain numbers";
                }
            }
            $sql = "select user_type from ra_online_user_roles_rel where upper(user_login)=upper('".$user_login."') and exists(select count(0) from ra_online_user where upper(user_login)=upper('".$user_login."') and is_desktop=true) order by id desc limit 1;";
            $user_type = common::excuteOneSql($sql);
            if (!empty($user_type)&&$user_type=="Super User") {
                if (strlen($password)<$rule["SuperMinLen"]||strlen($password)>$rule["SuperMaxLen"]) {
                    $str ="Super user password length between ".$rule["SuperMinLen"]." and ".$rule["SuperMaxLen"];
                }
            }else{
                //校验密码长度
                if (strlen($password)<$rule["MinLen"]||strlen($password)>$rule["MaxLen"]) {
                    $str ="Password length between ".$rule["MinLen"]." and  ".$rule["MaxLen"];
                }
            }
            return $str;
        }else{
            if (preg_match('/^\d*$/', $password) || preg_match('/^[a-zA-Z]+$/', $password)) {
                $str ="Must include letters and numbers";
            }
            $len = strlen($password);
            $t = substr($password, 0, 1);
            for ($i = 1; $i < $len; $i++) {
                $t1 = substr($password, $i, 1);
                if ($t != $t1) {
                    return "";
                }
            }
            return "error";
        }
    }

    //隐藏邮箱地址
    public static function maskEmail($email) {
        $idex = strlen($email) - strrpos($email, ".");
        $mask = substr($email, 0, 1) . str_repeat('*', 6) . "@" . str_repeat('*', 3) . substr($email, -$idex);
        return $mask;
    }

    public static function getInSql($str, $not = false, $sep = ";") {
        $str = trim($str);
        $str = trim($str, $sep);
        $str = trim($str);
        if (empty($str) && $str !== "0" && $str !== 0)
            return "1<>1";
        $str = strtolower($str);
        if (utils::checkExist($str, $sep)) {
            $aa = explode($sep, $str);
            $msg = "";
            foreach ($aa as $value) {
                $value = trim($value);
                if (empty($value))
                    continue;
                if (empty($msg))
                    $msg = "'" . common::check_input($value) . "'";
                else
                    $msg .= ",'" . common::check_input($value) . "'";
            }
            if ($not !== FALSE)
                return " not in (" . $msg . ")";
            else
                return " in (" . $msg . ")";
        } else {
            if ($not !== FALSE)
                return " != '" . common::check_input(trim($str)) . "'";
            else
                return " = '" . common::check_input(trim($str)) . "'";
        }
    }

    public static function checkExist($string, $search, $u = TRUE) {
        if ($u === TRUE) {
            if (stripos($string, $search) !== false)
                return TRUE;
        }else {
            if (strpos($string, $search) !== false)
                return TRUE;
        }
        return FALSE;
    }

    public static function endWith($string, $end, $u = TRUE) {
        if ($u === TRUE) {
            $string = strtolower($string);
            $end = strtolower($end);
            return strrchr($string, $end) == $end;
        }
        return strrchr($string, $end) == $end;
    }

    public static function _get($str) {
        $rs = isset($_POST[$str]) ? $_POST[$str] : null;
        if (empty($rs))
            $rs = isset($_GET[$str]) ? $_GET[$str] : null;
        return $rs;
    }

    public static function startWith($string, $start, $u = TRUE) {
        if ($u === TRUE)
            return stripos($string, $start) === 0;
        return strpos($string, $start) === 0;
    }

    public static function outDisplay($content, $is_time = 'f', $is_first = 'f', $is_boolean = 'f', $excel_export = FALSE) {
        if (empty($content) && $content !== 0 && $content !== "0")
            return "";
        if (strtolower($is_time) == 't')
            return utils::dealTimeDisplay($content);
        if (strtolower($is_first) == 't') {
            if ($excel_export !== FALSE)
                return utils::getCompanyName($content);
            else
                return '<span title="' . $content . '">' . utils::getCompanyName($content) . '</span>';
        }
        if (strtolower($is_boolean) == 't')
            return utils::outTrue($content);
        return nl2br($content);
    }

    public static function _output($value) {
        if (empty($value))
            return "&nbsp;";
        else
            return $value;
    }

    public static function dealTimeDisplay($date) {
        if (empty($date))
            return "";
        if (strlen($date) > 10)
            return date("m/d/Y H:i:s", strtotime($date));
        return date("m/d/Y", strtotime($date));
    }

    public static function outDisplayForMerge($frist,$last,$split = "/") {
        if($frist == $last){
            return $frist;
        }
        if (!empty($frist)){
            if(!empty($last)){
                return $frist.$split.$last;
            }else{
                return $frist;
            }
        }else{
            return $last;
        }
    }

    public static function outTrue($r) {
        if (empty($r))
            return "No";
        $r = strtolower($r);
        if ($r == "t")
            return "Yes";
        elseif ($r == "f")
            return "No";
        else
            return $r;
    }

    public static function getCompanyName($detail) {
        $detail = nl2br($detail);
        if (strpos($detail, '<br />') === FALSE)
            return $detail;
        return substr($detail, 0, strpos($detail, '<br />'));
    }

    public static function getEmail($serial_no) {
        $ocean = common::excuteObjectSql("select sales_rep, last_user, created_by, order_from, h_bol, consignee, dest_op, agent from public.kln_ocean where md5(serial_no)=md5('$serial_no') "
                        . "order by schem_not_display nulls last limit 1");
        $schema = $ocean["order_from"] . ".";
        $dest_op_from_agent = common::excuteOneSql("select dest_op_from_agent from " . $schema . "ocean where md5(serial_no)=md5('$serial_no')");
        if ($ocean["agent"] == "KYMTL" || $ocean["agent"] == "KYYYZ") {
            $email = array();
            $email["email"] = "";
            if (!empty($dest_op_from_agent)) {
                $so_email = common::excuteOneSql("select email from " . $schema . "employee where employee_id='" . $dest_op_from_agent . "' and active=true");
            }
            if (!empty($so_email)) {
                if (empty($email["email"])) {
                    $email["email"] = $so_email;
                } else {
                    $email["email"] .= ";" . $so_email;
                }
            }
            if (!empty($ocean["sales_rep"])) {
                $rep_email = common::excuteOneSql("select email from " . $schema . "employee where lower(salesopcode)='" . strtolower($ocean["sales_rep"]) . "' and active=true");
                if (!empty($rep_email)) {
                    if (empty($email["email"])) {
                        $email["email"] = $rep_email;
                    } else {
                        $email["email"] .= ";" . $rep_email;
                    }
                }
            }
        } else {
            $email = common::excuteObjectSql("select string_agg(e.email, ';') as email, string_agg(e.first_name, ';') as name from " . $schema . "ra_online_user u, " . $schema . "employee e WHERE u.employee_id = e.employee_id and "
                            . "lower(u.user_login) in ('" . strtolower($ocean["created_by"]) . "', '" . strtolower($ocean["last_user"]) . "')");
            if (empty($dest_op_from_agent)) {
                if (!empty($ocean["dest_op"])) {
                    $so_email = common::excuteOneSql("select email from " . $schema . "employee where employee_id='" . $ocean["dest_op"] . "' and active=true");
                }
            } else {
                $so_email = common::excuteOneSql("select email from " . $schema . "employee where employee_id='" . $dest_op_from_agent . "' and active=true");
            }
            if (empty($so_email)) {
                if ($ocean["agent"] == "APEXSFO") {
                    $so_email = "oid2@apexshipping.com";
                }
                if ($ocean["agent"] == "APEXLAX") {
                    $so_email = "laxoid@apexshipping.com";
                }
                if ($ocean["agent"] == "APEXNYC") {
                    $so_email = "NYCOID@APEXSHIPPING.COM";
                }
                if ($ocean["agent"] == "APEXPNW") {
                    $so_email = "pnwoid@apexshipping.com";
                }
                if ($ocean["agent"] == "STLUTA") {
                    $so_email = "starlinkOID@apexshipping.com ";
                }
                if ($ocean["agent"] == "APEXORD") {
                    $so_email = "ordoid@apexshipping.com";
                }
            }
            if (!empty($so_email)) {
                if (empty($email["email"])) {
                    $email["email"] = $so_email;
                } else {
                    $email["email"] .= ";" . $so_email;
                }
            }
            if (!empty($ocean["sales_rep"])) {
                $rep_email = common::excuteOneSql("select email from " . $schema . "employee where lower(salesopcode)='" . strtolower($ocean["sales_rep"]) . "' and active=true");
                if (!empty($rep_email)) {
                    if (empty($email["email"])) {
                        $email["email"] = $rep_email;
                    } else {
                        $email["email"] .= ";" . $rep_email;
                    }
                }
            }
        }
        $email["h_bol"] = $ocean["h_bol"];
        $email["consignee"] = $ocean["consignee"];
        return $email;
    }

    /***
     * 过滤json中的某个数据
     * @param unknown $json
     * @param unknown $search
     * @param unknown $replace
     * @return mixed
     */
    public static function jsonFiltration($search,$replace,$json){
        //处理json中将斜杠转义问题
        $json = str_replace("\\/", "/", $json);
        return str_replace($search, $replace, $json);
    }

    /*
     * calculate eta destination by etd port
    */
    public static function calculate_ETA_Des($serial_no) {
        $sql = "SELECT m_eta as eat, mport_of_discharge as poul, place_of_delivery as pod,service from ocean where lower(serial_no) = '" . strtolower($serial_no) . "'";
        $rs = common::excuteObjectSql($sql);
        $date = "";
        if (!empty($rs['eat'])) {
            $date = utils::calculate_ETA_Dest($rs['eat'], $rs['poul'], $rs['pod'], $rs['service']);
        }
        return $date;
    }

    public static function calculate_ETA_Dest($eta, $poul, $pod, $service) {
        if (empty($poul) || empty($pod))
            return $eta;
        $sql = "SELECT door_days, cy_days
               FROM eta_dest
              WHERE eta_dest.state::text = ((( SELECT unlocode.state
                       FROM ports, unlocode
                      WHERE ports.uncode::text = unlocode.uncode::text AND ports.code::text = '" . common::check_input($pod) . "'
                     LIMIT 1))::text) AND (','::text || eta_dest.pod::text) ~~* (('%,'::text || '" . common::check_input($poul) . "') || '%'::text)
             LIMIT 1";
        //$sql = "select door_days, cy_days from eta_dest where state = (select state from ports where code = '" . common::check_input($poul) . "' limit 1) and ','||pod ilike '%," . common::check_input($pod) . "%'";
        $rs = common::excuteObjectSql($sql);
        if (empty($rs))
            return $eta;
        if (utils::endWith($service, "cy"))
            return common::addDays($eta, $rs['cy_days']);
        else
            return common::addDays($eta, $rs['door_days']);
    }

    /*
     * password change, email alert
    */
    public static function sendEmailByPassword($username, $password, $email, $companyname='') {
        $sql = "select subject, ra_content as content from ra_online_email_tpl where lower(ra_type) = 'forgotpw'";
        $rs = common::excuteObjectSql($sql);
        if (!empty($rs)) {
            $subject = $rs['subject'];
            $content = $rs['content'];
        }
        if (!empty($subject) && !empty($content)) {
            $content = str_replace('<{username}>', $username, $content);
            $content = str_replace('<{password}>', $password, $content);
            $content = str_replace('<{companyname}>', $companyname, $content);

            global $db;
            common::excuteUpdateSql("INSERT INTO public.email_record_forgotpassword(type, title, from_email, to_email, content, insert_date, 
            cc_email) VALUES ('forgot_password', '" . common::check_input($subject) . "', 'US.KApex.Online@kerryapex.com', '" .
                    common::check_input($email) . "', '" . common::check_input($content) . "', now(), '');");
            return "success";
            //return Mail::sendMail($email, $subject, $content);
        } else
            return null;
    }

    public static function operation_log_records(){
        //排除opreation_log操作
        if( empty($_REQUEST["operate"])
            || ($_REQUEST["action"] == "login" && $_REQUEST["operate"] == "verifcation_code")
            || ($_REQUEST["action"] == "login" && $_REQUEST["operate"] == "check_uname")
            || ($_REQUEST["action"] == "ocean_order" && $_REQUEST["operate"] == "setting_ocean_order_display")
            || ($_REQUEST["action"] == "ocean_booking" && $_REQUEST["operate"] == "setting_display")){
            return;
        }
        //排除robot的相关的记录
        if((stripos($_REQUEST["action"], "robot") === 0)){
            return;
        }

        if($_REQUEST["action"] == "login" && $_REQUEST["operate"] == "tracking_checked"){
            //public tracking_checked 的user name 记录对应IP 地址
            $user_type = "Customer";
            $user_name = common::ip();
        } elseif($_REQUEST["action"] == "login" && $_REQUEST["operate"] == "do_login"){
            //移除do_login 因为在登录的过程中，是没有用户信息的
            $user_name = $_REQUEST["uname"];
        } else{
            $user_name = _getLoginName();
        }

        $user_type = _isApexLogin() ? "Employee" : "Customer";
        //如果在没有登录前，没有登录信息，指定用户
        if(!isset($_SESSION['ONLINE_USER'])){
            $user_type = common::excuteOneSql("select user_type from public.ra_online_user u where lower(user_login) = '" . strtolower($user_name) . "'");
        }
        
        $operateInfo = utils::getPageByAction($_REQUEST["action"],$_REQUEST["operate"]);
        $page = $operateInfo["page"];
        $operation = $operateInfo["operate"];
        $operation_detail = utils::analyzeOperationDetail($_REQUEST["action"],$_REQUEST["operate"]);
        if(empty($operation_detail)){
            $operation_detail = common::check_input(utils::jsonFiltration("null", "\"\"", json_encode($_REQUEST)));
        }
        //过滤一分钟以内，相同用户的重复请求
        $exist_sql = "select count(1) from public.customer_service_operation_log 
            where user_name = '$user_name' 
                and page = '$page' and operation = '$operation' and operation_detail = '$operation_detail' 
                and operation_time > NOW() - INTERVAL '1 minute' limit 1;";
        $exist_obj = common::excuteOneSql($exist_sql); 
        if(empty($exist_obj)){     
            $sql = "INSERT INTO public.customer_service_operation_log(user_type, user_name, page, operation, operation_detail, 
                operation_time) 
                VALUES ('$user_type', '$user_name', '$page', '$operation', '$operation_detail', now())";
            common::excuteUpdateSql($sql);
        }
    }

    public static function getPageByAction($action,$operate){
        //取消
        $operationConvertName = array(
            "login=do_login" => array("page" =>"Login","operate"=>"Login"),
            "login=forgot_password" => array("page" =>"Login","operate"=>"Forgot_PPassword"),
            "login=logout" => array("page" =>"logout","operate"=>"logout"),
            "login=update_pwd_expires" => array("page" =>"Login","operate"=>"Reset password"),
            "ocean_booking=search" => array("page" =>"Booking","operate"=>"Search"),
            "Booking_Search=save_setting_display" => array("page" =>"Booking","operate"=>"Customize Coulumns"),
            "booking=autody" => array("page" =>"Booking","operate"=>"More Filter"),
            "booking=autoport" => array("page" =>"Booking","operate"=>"More Filter"),
            "ocean_booking=detail" => array("page" =>"Booking","operate"=>"Open Detailed Page"),
            "ocean_booking=excel" => array("page" =>"Booking","operate"=>"Download"),
            "ocean_booking=save_communication" => array("page" =>"Booking","operate"=>"Send Email"),
            "opreation_log=search" => array("page" =>"Opreation_log","operate"=>"Search"),

            "login=tracking_checked" => array("page" =>"Tracking","operate"=>"Public tracking"),
            "ocean_order=search" => array("page" =>"Tracking","operate"=>"Search"),
            "Ocean_Search=save_setting_display" => array("page" =>"Tracking","operate"=>"Customize Coulumns"),
            "tracking=autody" => array("page" =>"Tracking","operate"=>"More Filter"),
            "tracking=autoport" => array("page" =>"Tracking","operate"=>"More Filter"),
            "ocean_order=detail" => array("page" =>"Tracking","operate"=>"Open Detailed Page"),
            "ocean_order=excel" => array("page" =>"Tracking","operate"=>"Download"),
            "ocean_order=download" => array("page" =>"Tracking","operate"=>"Download"),
            "ocean_order=save_communication" => array("page" =>"Booking","operate"=>"Send Email"),
            "ocean_order=ams_isf_log" => array("page" =>"Tracking","operate"=>"AMS/ISF"),
            "ocean_order=ocean_vgm" => array("page" =>"Tracking","operate"=>"Enter VGM"),
            "ocean_order=save_ocean_vgm" => array("page" =>"Tracking","operate"=>"Save VGM"),
            "ocean_order=share_shipment" => array("page" =>"Tracking","operate"=>"Share shipment"),
            "ocean_order=document_upload" => array("page" =>"Tracking","operate"=>"document_upload"),
            "ocean_order=document_upload_do" => array("page" =>"Tracking","operate"=>"document_upload_do"),

            "tools=mark_save" => array("page" =>"Tools","operate"=>"Mark_Save"),
            "password=" => array("page" =>"Profile","operate"=>"Change password"));
        if($action == "ajax" && $operate == "save_setting_display"){
            $model_name = $_REQUEST['model_name'];
            return $operationConvertName[$model_name."=".$operate];
        }
        if($action == "ajax" && ($operate == "autody" || $operate == "autoport")){
            $model_name = $_REQUEST['search_mode'];
            return $operationConvertName[$model_name."=".$operate];
        }    
        return $operationConvertName[$action."=".$operate];
    }

    public static function analyzeOperationDetail($action,$operate){
        $detail = "";
        if($action == "login" && $operate == "do_login"){
            $detail = 'System Account';
            if($_REQUEST['token']){
                $detail = 'From Apex Online';
            }
        }
        if($action == "login" && $operate == "logout"){
            $detail = 'User Logout';
        }
        if($action == "login" && $operate == "tracking_checked"){
            $detail = 'Join public tracking action,Public tracking number:'.$_POST['reference_number'];
        }
        if($action == "password"){
            $detail = 'User Change password';
        }
        if(($action == "ocean_booking" || $action == "ocean_order") && $operate == "search"){
            $detail = "";
            //还有一个同以分钟内，不记录相同的查询  这个需要建表查询
            //{"action":"ocean_booking","operate":"search","_ntype":"ocean_booking","cp":"1","ps":"100","rc":"-1","other_filed":"","uname":"ra.admin","psw":"abc123456789"}
            $filter_common_field = array("action","operate","_ntype","cp","ps","rc","other_filed","uname","psw");
            foreach($_REQUEST as $selected_key => $selected){
                if(!utils::in_array($selected_key, $filter_common_field)){
                    if(is_array($selected)){
                        $selected = utils::implode(",",$selected);
                    }
                    $detail .="$selected_key:".$selected."; ";
                }
            }
            if(empty($detail)){
                $detail .="No search condition";
            }
        }
        if($action == "ajax" && $operate == "save_setting_display"){
            $detail = "";
            $type = $_REQUEST['model_name'] == "Booking_Search" ? "Booking_Search" : "Ocean_Search";
            //记录最终save 和 default 字段相比的结果
            // $default_ids = common::excuteListSql("select id,display_name from public.ra_online_search_display_cso where model_name = '$type' 
            //     and display_name in('Booking No.','MBL No.','HBL No.','Mode','Status',
            //         'Shipper','Consignee','Origin Agent','Destination Agent','Creation Time','ETD','ETA',
            //         'Voyage','Vessel','Week','Created by') order by default_order");
            $default_ids = common::excuteListSql("select id,display_name from public.ra_online_search_display_cso where model_name = '$type' 
                    and default_display = true  order by default_order");        

            $ids = utils::implode(";", $_POST['ids']);
            $save_ids = common::excuteListSql("select id,display_name from public.ra_online_search_display_cso where model_name = '$type' 
                    and id::text = any(regexp_split_to_array('$ids', ';')) order by default_order"); 

            $detele_detail = "";
            foreach($default_ids as $did){
                if(!utils::exist_array($did['id'],$save_ids)){
                    $detele_detail .=$did['display_name']."/";
                }
            }
            $add_detail = "";
            foreach($save_ids as $sid){
                if(!utils::exist_array($sid['id'],$default_ids)){
                    $add_detail .=$sid['display_name']."/";
                }
            }
            if(!empty($detele_detail)){
                $detail.="Detele fields: (".$detele_detail."). ";
            }
            if(!empty($add_detail)){
                $detail.="Add fields: (".$add_detail."). ";
            }
            if(empty($detail)){
                $detail = "The default field has not changed";
            }
        }

        if(($action == "ocean_booking" || $action == "ocean_order") && $operate == "detail"){
            $tabel = $action == "ocean_booking" ? "kln_booking" : "kln_ocean";
            $serial_no = common::deCode($_GET['a'], 'D');
            $sql = "SELECT booking_no,h_bol from public.$tabel where serial_no = '$serial_no' limit 1";
            $data = common::excuteObjectSql($sql);
            if(!empty($data['booking_no'])){
                $detail = 'Booking No.: '.$data['booking_no'];
            }else{
                $detail = 'HBOL: '.$data['h_bol'];
            }
        }

        if(($action == "ocean_booking" || $action == "ocean_order") && $operate == "save_communication"){
            $text = $_POST["text"];
            $detail = urldecode($text);  
        }
        if(($action == "ocean_order") && $operate == "ams_isf_log"){
            $detail = "Enter AMS/ISF Page";  
        }
        if(($action == "ocean_booking" || $action == "ocean_order") && $operate == "excel"){
            $detail = "Filter_condition:" . $_REQUEST['excel_filter_condition']." Selected Fields:". $_REQUEST['selected_fields'];
        }
        //Tracking詳情頁download的file（顯示file名稱）
        if(($action == "ocean_order") && $operate == "download"){
            $filename = common::deCode($_GET['url'], 'D');
            $filename = str_replace("/", DIRECTORY_SEPARATOR, $filename);
            $filename = str_replace("\\", DIRECTORY_SEPARATOR, $filename);
            $display_name = basename($filename);
            if (!file_exists($filename)){
                $detail = "Tracking Detail Attachment Download But File Not Exist : $display_name";
            }else{
                $detail = "Tracking Detail Attachment Download: $display_name";
            }
        }

        //Tracking詳情頁Upload Files（顯示file名稱）
        if(($action == "ocean_order") && $operate == "document_upload"){
            $detail = "Enter Upload Files page";
        }
        
        return $detail;

    }

    public static function calculateTicks($minValue, $maxValue, $targetTickCount = 10) {
        $tickSpacing = ($maxValue - $minValue);
        $tickSpacing = intval($tickSpacing);
		$interval = ceil($tickSpacing / $targetTickCount);
        $len = strlen($interval);
        if ($len >1){
            $interval = ceil($interval/pow(10,$len-1)) *pow(10,$len-1);
        }
        return $interval;
    }

    //只记录Public tracking
    public static function single_operation_log_save($user_type,$user_name,$page,$operation,$operation_detail){
        $sql = "INSERT INTO public.customer_service_operation_log(user_type, user_name, page, operation, operation_detail, 
        operation_time) 
        VALUES ('$user_type', '$user_name', '$page', '$operation', '$operation_detail', now())";
        common::excuteUpdateSql($sql);
    }

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

    public static function count($variable){
        if (is_array($variable)) {
            $count = count($variable);
        } else {
            $count = 0;
        }
        return $count;
    }

    public static function implode($sp,$variable){
        $variable = isset($variable) && is_array($variable) ? $variable : array();
        return implode($sp, $variable);
    }

    public static function in_array($str, $arr){
        if (is_array($arr)) {
            return in_array($str, $arr);
        } else {
            return false;
        }
    }

    public static function exist_array($key,$arr){
        $flag = false;
        foreach($arr as $v){
            if($v['id'] == $key ){
                $flag = true;
            }
        }
        return $flag;
    }

    public static function getConpanyForNotify($_schemas,$type){
        //如果有多個就留空
        $company = "";
        if($type == 'ocean'){
            $company = $_SESSION['ONLINE_USER']['company_name'];
            $company_arr =  explode(";",$company);
            $temp = array();
            foreach($company_arr as $v){
                if(!empty($v)){
                    $temp[] = $v;
                }
            }
            if(utils::count($temp) <= 1){
                return $company;
            }
        }
        if($type == 'air'){
            $company_id =  $_SESSION['ONLINE_USER']['air_customers']; 
            $company_id_arr =  explode(";",$company_id);
            $temp = array();
            foreach($company_id_arr as $v){
                if(!empty($v)){
                    $temp[] = $v;
                }
            }
            if(utils::count($temp) == 1){
                if ($_schemas == "public") {//apex ocean和air 分开
                    $sql = "SELECT company  from ocean.contacts where contact_id = '" . common::check_input($temp[0]) . "' ";
                } else {
                    $sql = "SELECT company  from $_schemas.contacts where contact_id = '" . common::check_input($temp[0]) . "' ";
                }
                $company = common::excuteOneSql($sql);
            }
            return $company;
        }
    }


    public static function getKlnDocNotifyContent($bol,$file_type,$upload_user_name,$upload_user_email,$company,$date_time){
        $report_setting = common::excuteObjectSql("select * from ra_online_auto_report_config where report_type = 'KLN_DOC_Notify' ");
        $report_content = $report_setting['report_content'];
        $columns = common::excuteListSql("select display_name,model_name,database_column_name,excel_width,order_by from public.ra_online_search_display_cso where model_name = 'KLN_DOC_Notify'");
        $missing_packing_th = "<tr>";
        foreach ($columns as $colk => $colvalue) {
                $missing_packing_th.='<td style="width:'.$colvalue['excel_width'].'pt;border:solid black 1.0pt;background:#1F4E78;padding:0cm 5.4pt 0cm 5.4pt;height:14.25pt">
                                        <p class="MsoNormal" align="left" style="text-align:left"><b><span lang="EN-US" style="font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:white">'.$colvalue['display_name'].'</span></b></p>
                            </td>';      
        }
        $missing_packing_th .= "</tr>";
        $report_content = str_replace('<{missing_packing_th}>', $missing_packing_th, $report_content);
        
        $data = array(array("h_bol"=>$bol,"file_type"=>$file_type,"upload_by"=>$upload_user_name,"email"=>$upload_user_email,"company"=>$company,"upload_time"=>$date_time."(US/Pacific)"));
        $missing_packing_tr = "";
        foreach ($data as $rk => $rv) {
            $missing_packing_tr .= "<tr>";
            foreach ($columns as $ck => $cv) {
                if(utils::endWith($cv['display_name'], "email")){
                    $missing_packing_tr.='<td style="font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:black;border-top:none;
                        border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt;padding:0cm 5.4pt 0cm 5.4pt;height:14.25pt">
                            <a href="mailto:'.$rv[$cv['database_column_name']].'">'.$rv[$cv['database_column_name']].'</a>
                    </td>';
                }else{
                    $missing_packing_tr.='<td  style="font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:black;'
                        . 'border-top:none;border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt;padding:0cm 5.4pt 0cm 5.4pt;height:14.25pt">'.$rv[$cv['database_column_name']].'</td>';
                }
            }
            $missing_packing_tr .= "</tr>";
        }
        $report_content = str_replace('<{missing_packing_tr}>', $missing_packing_tr, $report_content);
        return $report_content;
    }
    

    public static function _getSql($ids, $type,$shipment_mode,$sqlWhere) {
        $ids_arr = explode(',', $ids);
        $sql = "";
        if($type == "co2e_orgin"){
            $str = "SUM(COALESCE(carbon_emission,0)) as catnum ";
            $filed = "shippr_uncode";
        } else {
            $str = "SUM(COALESCE(carbon_emission,0)) as catnum ";
            $filed = "consignee_uncode";
        }

        $shipment_mode_where = " 1=1 ";
        //这里处理为空，目前没有数据
        $shipment_mode_where = " transport_mode = '$shipment_mode' ";
        $shipment_mode_where .= $sqlWhere;
        foreach ($ids_arr as $value) {
            if (!empty($value)) {
                if (empty($sql)) {
                    $sql .= "SELECT $str FROM public.kln_ocean  where $shipment_mode_where  and $filed = '$value'";
                } else {
                    $sql .= " union all SELECT $str from public.kln_ocean  where $shipment_mode_where and $filed = '$value'";
                }
            }
        }
        return $sql;
    }

    public static function removeDuplicateArray($array){
        $result = array();
        foreach ($array as $value) {
            //过滤可能的空值，会引起前端页面有数据但无法显示
            if(!empty($value)){
                if (!utils::in_array($value, $result)) {
                    $result[] = $value;
                }
            }
        }
        return $result;
    }

    public static function hasMacros($filePath) {
        $extension = pathinfo($filePath, PATHINFO_EXTENSION);
        if ($extension === 'xlsx') {
            // 检查 .xlsx 文件
            $zip = new ZipArchive();
            if ($zip->open($filePath) === TRUE) {
                // 检查是否存在 vbaProject.bin 文件
                $hasMacros = $zip->locateName('xl/vbaProject.bin') !== false;
                $zip->close();
                return $hasMacros;
            } else {
                return false; // 无法打开文件
            }
        } elseif ($extension === 'docx') {
            // 检查 .docx 文件
            $zip = new ZipArchive();
            if ($zip->open($filePath) === TRUE) {
                // 检查是否存在 vbaProject.bin 文件
                $hasMacros = $zip->locateName('word/vbaProject.bin') !== false;
                $zip->close();
                return $hasMacros;
            } else {
                return false; // 无法打开文件
            }
        } else {
            return false; // 不是支持的文件类型
        }
    } 

    public static function arrayKeyToInt($arr){
        $ret = array();
        foreach($arr as $key => $val){
            $ret[] = $val;
        }
        return $ret;
    }

    public static function getDailyAndweeklyFrist($arr){
        $numericRecords = count($arr);
        $ret = array();

        $numericRecords_one = 0;
        $numericRecords_two = 0;
        foreach($arr as $key => $val){
            if($key == 0){
                $val["numericRecords"] = $numericRecords;
                $ret = $val;
            }
            if($val["notifiation_type"] == "Departure/Arrival_Delay"){
                if(utils::checkExist($val["delay_name"],"Departure_Delay")){
                    $numericRecords_one +=1;
                }
                if(utils::checkExist($val["delay_name"],"Arrival_Delay")){
                    $numericRecords_two +=1;
                }
            }
            if($val["notifiation_type"] == "ETD/ETA_Change"){
                if(utils::checkExist($val["date_change_name"],"ETD Change")){
                    $numericRecords_one +=1;
                }
                if(utils::checkExist($val["date_change_name"],"ETA Change")){
                    $numericRecords_two +=1;
                }
            }
        }
        //对Delay and change 特殊处理
        $ret["numericRecords_one"]= $numericRecords_one;
        $ret["numericRecords_two"]= $numericRecords_two;
        return $ret;
    }

    /**
     * ocean ->sea
     */
    public static function converModeToDB($shipment_transport_mode_arr){
        $converMode = array();
        foreach($shipment_transport_mode_arr as $model){
            if (strtolower($model) == "ocean"){
                $converMode[] = 'sea';
            }else{
                $converMode[] = strtolower($model);
            }
        }
        return utils::implode(";",$converMode);
    }

    /**
     * sea ->ocean
    */
    public static function converModeToDisplay($shipment_transport_mode){
        //数据转换前端需要的
        $converMode = array();
        $shipment_transport_mode_arr = explode(";", $shipment_transport_mode);
        foreach($shipment_transport_mode_arr as $model){
            if (strtolower($model) == "sea"){
                $converMode[] = 'Ocean';
            }else{
                $converMode[] = ucfirst($model);
            }
        }
        return  utils::implode(";",$converMode);
    }

    public static function compareArrayEq($array1,$array2){ 
        $array1 = empty($array1) ? array():$array1;
        $array2 = empty($array2) ? array():$array2;
        // 使用array_diff比较并重置键名
        $diff1 = array_diff($array1, $array2);
        $diff2 = array_diff($array2, $array1);
         
        // 如果结果为空，说明两个数组一样
        if (empty($diff1) && empty($diff2)) {
            return true;
        } else {
            return false;
        }
    }

    public static function comvertutcinfo($time_zone){
        if(!empty($time_zone)){
            if(utils::checkExist($time_zone,"+")){
                $time_zone = str_replace('+', '-', $time_zone);
            }else{
                $time_zone = str_replace('-', '+', $time_zone);
            }
        }
        return  $time_zone;
    }

    public static function arrayOrderBykeys($order,$data_kd,$key){
        $orderedData = []; // 用于存储按$order排序后的数据
        foreach ($order as $name) {
            foreach ($data_kd as $item) {
                if ($item[$key] == $name) {
                    $orderedData[] = $item; // 将找到的元素添加到新数组中
                    break; // 找到后跳出内层循环，继续外层循环处理下一个id
                }
            }
        }
        return $orderedData;
    }

    /**
     * 数组去掉null 
     */
    public static function arrayRemoveNull($array){
        foreach ($array as &$value) {
            if (is_null($value)) {
                $value = '';
            }
        }
        unset($value); // 断开 $value 的引用

        return $array;
    }
    /**
     * public.kln_ocean 和 WHERE 的位置关系，进行条件拼接
    */
    public static function modifyString($input,$sqlWhere) {
        $search = 'public.kln_ocean';
        $pos = strpos($input, $search);
        
        if ($pos !== false) {
            $afterSearch = substr($input, $pos + strlen($search));
            $hasWhere = (stripos($afterSearch, 'WHERE') !== false);
            
            if ($hasWhere) {
                // 在 WHERE 后插入 1=1
                $wherePos = stripos($input, 'WHERE', $pos);
                $insertPos = $wherePos + strlen('WHERE');
                $sqlWhere =' ' .$sqlWhere.' and  ';
                return substr_replace($input, $sqlWhere, $insertPos, 0);
            } else {
                // 在 public.kln_ocean 后插入 1=1
                $insertPos = $pos + strlen($search);
                $sqlWhere =' where ' .$sqlWhere.' ';
                return substr_replace($input, $sqlWhere, $insertPos, 0);
            }
        }
        return $input;
    }

    /**
     * LIMIT的处理，或者超过100
     * 如果有limit 给最后一个limit修改 
     * 这个处理不了 写在字段里的子查询（select 1 from aa limit 1） as aa，sql会原样返回
     */
    public static function processLimitClause($sql,$limit = 100) {
        $is_limit = false;
        // 去除前后空白
        $sql = trim($sql);

        // 临时保存字符串内容，防止被误匹配
        $placeholder = '__SQL_STRING_PLACEHOLDER__';
        $strings = [];

        // 匹配字符串（包括单引号和 $$ 符号）
        $pattern = "/('(?:[^']|'')*')|(\$(?:.*?)\$)/is";

        // 替换所有字符串为占位符，并保存原内容
        $cleanSql = preg_replace_callback($pattern, function ($match) use (&$strings, $placeholder) {
            $str = $match[1] ?? $match[2];
            $index = count($strings);
            $strings[$index] = $str;
            return $placeholder . $index;
        }, $sql);

        // 正则匹配所有 LIMIT 子句（支持 LIMIT n 和 LIMIT n OFFSET m）
        $pattern = '/\s+LIMIT\s+\d+(\s+OFFSET\s+\d+)?/i';

        preg_match_all($pattern, $cleanSql, $matches, PREG_OFFSET_CAPTURE);

        if (!empty($matches[0])) {
            // 获取最后一个 LIMIT 的位置和内容
            $lastMatch = end($matches[0]);
            $limitStr  = $lastMatch[0]; // 完整的 LIMIT 子句，如 " LIMIT 50" 或 " LIMIT 200 OFFSET 10"
            $pos       = $lastMatch[1]; // 起始位置

            // 提取 LIMIT 的数字部分
            preg_match('/\d+/', $limitStr, $numMatch);
            $currentLimit = intval($numMatch[0]);

            if ($currentLimit < $limit) {
                // 当前 LIMIT 值更小，不做修改
                $is_limit = true;
            }else{
                // 构造新的 LIMIT 子句，保留可能存在的 OFFSET
                $offsetPart = '';
                if (preg_match('/(.*?)(\s+OFFSET\s+\d+)/i', $limitStr, $offsetMatches)) {
                    $offsetPart = $offsetMatches[2]; // 例如 " OFFSET 10"
                }
                $newLimitClause = " LIMIT {$limit}{$offsetPart}";

                // 替换最后一个 LIMIT
                $before = substr($cleanSql, 0, $pos);
                $after  = substr($cleanSql, $pos + strlen($limitStr));

                $cleanSql = $before . $newLimitClause . $after;
            }
        } else {
            // 没有 LIMIT，直接加上
            $cleanSql .= " LIMIT {$limit}";
        }

        // 最后恢复原来的字符串内容
        $finalSql = preg_replace_callback("/{$placeholder}(\d+)/", function ($match) use ($strings) {
            return $strings[(int)$match[1]];
        }, $cleanSql);

        return array("sql"=>$finalSql, "is_limit"=>$is_limit);
    }

    /**
     * 替换 single reference
    */
    public static function replacements($data, $template,$new_sql) {
        // 动态构建替换数组（格式：[key] => value）
        $replacements = [];
        foreach ($data as $key => $value) {
            $replacements["{{$key}}"] = $value; 
            $replacements["{{{$key}}}"] = $value; 
        }
        if(empty($data)){
            $fileds = common::extractSelectFields($new_sql);
            foreach($fileds as $key){
                //如果遇到data没数据，但是有 total_count ,则处理成0
                if($key == "total_count"){
                    $replacements["{{$key}}"] = "0"; 
                    $replacements["{{{$key}}}"] = "0";
                } else {
                    $replacements["{{$key}}"] = ""; 
                    $replacements["{{{$key}}}"] = "";
                }
            }
        }
        // 执行替换
        error_log("replacements_single:".json_encode($replacements));
        error_log("template_single:".$template);
        $result = strtr($template, $replacements);
        // 输出结果
        return $result;
    }

     /**
     * 替换复杂的reference
     * 
    */
    public static function replacementsMultiline($data, $template,$new_sql) {
        //行中所有 {{xxx}} 模板变量都必须在 SQL 查询字段中存在
        //SQL 中可以有比 reference 多的字段，不影响匹配
        //一旦找到第一个符合条件的 
        //不依赖任何 {{#EACH ROW}} 或其他模板语法
        //| {{h_bol}} | {{m_bol}} | {{transport_mode}} | {{service}} | 格式
        $explode_data = utils::findFirstTemplateRow($template,$new_sql);
        $explode_str  = $explode_data['line'];

        $replaceTemplate = "";
        //检查模板 是否已经带有特定表格的序列
        if(!empty($explode_str) && strpos($template, $explode_str) !== false){
            $spacing = utils::getMarkDownTableSpacing($template,$explode_str);
            $parts = explode($explode_str, $template,2); 

            // 生成所有行
            $generatedRows = [];
            foreach ($data as $row) {
                $replacements = [];
                foreach ($row as $key => $value) {
                    $replacements["{{{$key}}}"] = $value;
                }
                $generatedRows[] = strtr($explode_str, $replacements);
            }
            if(empty($generatedRows) && empty($data)){
                $generatedRows[] = "No Data";
            }

            //如果 SQL字段是有多余未匹配的字段 调用一次个体替换
            $mapping  = $explode_data['mapping'];
            if(!$mapping){
                $parts[0] = utils::replacements($data[0],$parts[0],$new_sql);
                $parts[1] = utils::replacements($data[0],$parts[1],$new_sql);
            }

            $replaceTemplate = $parts[0] . implode($spacing, $generatedRows) . $parts[1];
        }else{
            //全文替换 上面统一有excuteListSql 这里的结果要变一下
            $replaceTemplate = utils::replacements($data[0],$template,$new_sql);
        }
        return $replaceTemplate;
    }

    // 在 reference 中查找第一个符合要求的 | ... | 行
    public static function findFirstTemplateRow($reference, $sql) {
        $sqlFields = common::extractSelectFields($sql);
        if (empty($sqlFields)) return null;

        $lines = preg_split('/\r\n|\r|\n/', $reference);

        foreach ($lines as $line) {
            $line = trim($line);

            // 检查是否是以 | 开头和结尾的表格行
            if (strpos($line, '|') !== 0 || substr($line, -1) !== '|') continue;

            // 提取该行中的所有 {{xxx}} 模板变量
            preg_match_all('/\{\{(\w+)\}\}/', $line, $matches);
            $templateVars = $matches[1];

           
            preg_match_all('/(?<=\|)([^|]+)(?=\|)/', $line, $matchesTwo);
            // 去除每个匹配项前后的空白字符
            $cells = $matchesTwo[1];


            if (empty($templateVars) || count($templateVars) <> count($cells)) continue;

            // 检查每个变量是否都在 SQL 字段中
            foreach ($templateVars as $var) {
                if (!utils::in_array(strtolower($var), array_map('strtolower', $sqlFields))) {
                    continue; // 跳出当前循环，继续检查下一行
                }
            }

            $mapping = true; 
            //检查 SQL 字段是否有未匹配的字段
            if(count($sqlFields) <> count($templateVars)){
                $mapping = false;
            }

            // 所有变量都匹配成功，返回这一行
            return array("line"=>$line,"mapping" => $mapping);
        }

        return array("line"=>null,"mapping" =>true);; // 没有找到匹配行
    }

    /**
     * 替换复杂的reference 固定问题 分开
    */
    public static function replacementsFixedMultilineForFixed($data, $template,$explode_str) {
        //| {{h_bol}} | {{m_bol}} | {{transport_mode}} | {{service}} | 格式
        $replaceTemplate = "";
        //检查模板 是否已经带有特定表格的序列
        if(strpos($template, $explode_str) !== false){
            $spacing = utils::getMarkDownTableSpacing($template,$explode_str);
            $parts = explode("$explode_str", $template,2); 

            // 生成所有行
            $generatedRows = [];
            foreach ($data as $row) {
                $replacements = [];
                foreach ($row as $key => $value) {
                    $replacements["{{{$key}}}"] = $value;
                }
                $generatedRows[] = strtr($explode_str, $replacements);
            }
            $replaceTemplate = $parts[0] . implode($spacing, $generatedRows) . $parts[1];
        }else{
            //有异常,模板原样返回
            $replaceTemplate = $template;
        }
        return $replaceTemplate;
    }

     /**
     * 替换 single reference Fixed
    */
    public static function replacementsFixed($data, $template,$fileds) {
        // 动态构建替换数组（格式：[key] => value）
        $replacements = [];
        foreach ($data as $key => $value) {
            $replacements["{{$key}}"] = $value; 
            $replacements["{{{$key}}}"] = $value; 
        }
        if(empty($data)){
            foreach($fileds as $key){
                $replacements["{{$key}}"] = ""; 
                $replacements["{{{$key}}}"] = "";
            }
        }
        // 执行替换
        $result = strtr($template, $replacements);
        // 输出结果
        return $result;
    }

    public static function getMarkDownTableSpacing($str,$search){
        //$str = "|--------------|\n sdsds| E1205546127 | ";
        //$search = "| E1205546127 |";
        // 查找搜索字符串的位置
        $pos = strpos($str, $search);
        if ($pos !== false) {
            // 从搜索字符串前面开始向前查找 "|" 的位置
            for ($i = $pos - 1; $i >= 0; $i--) {
                if ($str[$i] === '|') {
                    // 提取两个位置之间的内容
                    $result = substr($str, $i + 1, $pos - $i - 1);
                    return $result;
                }
            }
        } else {
            return "\n";
        }
    }

    public static function uniqueGroupbyData($unique_filed,$groups_filed,$rows){
        $uniqueData = [];
        $hBolSeen = [];
        //需要去重
        if(!empty($unique_filed)){
            foreach ($rows as $row) {
                $hBol = $row[$unique_filed]."_".$row[$groups_filed];
                if (!isset($hBolSeen[$hBol])) {
                    $uniqueData[] = $row;
                    $hBolSeen[$hBol] = true;
                }
            }
        } else {
            //不需要去重
            $uniqueData = $rows;
        }

        //分组计数
        $dateGroups = [];
        if(!empty($groups_filed)){
            foreach ($uniqueData as $item) {
                $date = $item[$groups_filed];
            
                if (!isset($dateGroups[$date])) {
                    $dateGroups[$date] = 0;
                }
                $dateGroups[$date]++;
            }
        } 
        return $dateGroups;
    }

    public static function getDmoeSqlForAi($type){
        $data= array();
        $data["Show shipments delayed in the last 30 days."] = "select count(*)
            from public.kln_record kr
                inner join LATERAL (select h_bol from public.kln_ocean oo where oo.serial_no = kr.serial_no and <{ExtendHand_KLN}>  ) m on true
            where kr.log_type like '%Delay' 
            and kr.log_time >= CURRENT_DATE - INTERVAL '30 day'	
            and kr.event_date is not null and kr.event_old_date is not null
            and (kr.event_date||' '||COALESCE(kr.event_time,'00:00'))::timestamp >= (kr.event_old_date||' '||COALESCE(kr.event_old_time,'00:00'))::timestamp;select oo.h_bol,oo.place_of_receipt_exp,oo.place_of_delivery_exp,oo.serial_no,oo.order_from,
            kr.log_type,kr.event_old_date, kr.event_old_time,kr.event_date, kr.event_time,
                (EXTRACT(DAY FROM ((event_date||' '||COALESCE(event_time,'00:00'))::timestamp - (event_old_date||' '||COALESCE(event_old_time,'00:00'))::timestamp))) as duration
            from public.kln_record kr
                inner join LATERAL (select h_bol, place_of_receipt_exp, place_of_delivery_exp,serial_no,order_from
                            from public.kln_ocean oo where oo.serial_no = kr.serial_no and <{ExtendHand_KLN}>  ) oo on true
            where kr.log_type like '%Delay' 
            and kr.log_time >= CURRENT_DATE - INTERVAL '30 day'	
            and kr.event_date is not null and kr.event_old_date is not null
            and (kr.event_date||' '||COALESCE(kr.event_time,'00:00'))::timestamp >= (kr.event_old_date||' '||COALESCE(kr.event_old_time,'00:00'))::timestamp
            order by kr.log_time  desc limit 10";
        $data["Shipments arriving in the next 7 days."] =  "select count(*)
         from (
         	SELECT  oo.serial_no,h_bol, place_of_receipt_exp, place_of_delivery_exp,m.description,eta,order_from, o.cargo_type
         		FROM public.kln_ocean oo
                inner join LATERAL (select case when is_hazardous = 't' then 'Dangerous Goods'::text else 'General'::text end as cargo_type 
			        from public.ocean o where o.serial_no = oo.serial_no) o on true
         		left join LATERAL (select a.code,a.description 
         				from public.ocean_milestone a 
         					inner join public.customer_service_milestone_sno s 
         				on a.code = s.code 
         					and s.type = 'sea' 
         					and a.serial_no = oo.serial_no
         					and a.act_date is not null 
         				order by s.sno desc limit 1) m on true
         	WHERE  <{ExtendHand_KLN}> and  oo.transport_mode = 'sea' and order_from = 'public' and m.code <> ''  limit 10
         ) t;select serial_no,h_bol,place_of_receipt_exp,place_of_delivery_exp,description,eta,order_from,cargo_type 
         from (
         	SELECT  oo.serial_no,h_bol, place_of_receipt_exp, place_of_delivery_exp,m.description,eta,order_from, o.cargo_type
         		FROM public.kln_ocean oo
                inner join LATERAL (select case when is_hazardous = 't' then 'Dangerous Goods'::text else 'General'::text end as cargo_type 
			        from public.ocean o where o.serial_no = oo.serial_no) o on true
         		left join LATERAL (select a.code,a.description 
         				from public.ocean_milestone a 
         					inner join public.customer_service_milestone_sno s 
         				on a.code = s.code 
         					and s.type = 'sea' 
         					and a.serial_no = oo.serial_no
         					and a.act_date is not null 
         				order by s.sno desc limit 1) m on true
         	WHERE  <{ExtendHand_KLN}> and  oo.transport_mode = 'sea' and order_from = 'public' and m.code <> ''  limit 20
         ) t order by eta";

        $data["List shipments with milestone updates in the last 7 days."] =  "select count(*)
            from (
            select serial_no
            from (SELECT oo.serial_no from public.ocean_milestone a 
                        inner join public.customer_service_milestone_sno s on a.code = s.code 
                        inner join public.kln_ocean oo on oo.serial_no = a.serial_no 
                    where s.type = 'sea' 
                        and a.act_date is not null
                        ) po
            
           
            )t;


            select serial_no,order_from,h_bol, description,update_date_format,update_date,
                    COALESCE(m.jsonb_data->>'milestone','')::jsonb->>'timezone' as timezone,
                    COALESCE(m.jsonb_data->>'milestone','')::jsonb->>'locations' as locations 
            from (
            select serial_no,order_from,h_bol,description,to_char(update_date,'Mon DD') as update_date_format,update_date,code
            from (SELECT oo.serial_no,oo.order_from,oo.h_bol,s.description,COALESCE(a.update_date, a.create_date) as update_date,a.code
                    from public.ocean_milestone a 
                        inner join public.customer_service_milestone_sno s on a.code = s.code 
                        inner join public.kln_ocean oo on oo.serial_no = a.serial_no 
                    where s.type = 'sea' 
                        and a.act_date is not null
                        ) po
            )t  left join LATERAL (select public.getTimeAndLocationForKln(serial_no,code,''::text)::jsonb as jsonb_data) m on true 
            order by update_date limit 10;

            select aa.update_date_format, COUNT(*) AS total_count
            from (
                select DISTINCT ON (h_bol) h_bol, update_date_format
                from (
                select h_bol,to_char(update_date,'Mon DD') as update_date_format
                from (SELECT oo.h_bol,COALESCE(a.update_date, a.create_date) as update_date
                        from public.ocean_milestone a 
                            inner join public.kln_ocean oo on oo.serial_no = a.serial_no 
                        where a.act_date is not null
                            ) po
              
                )t order by h_bol
            ) aa
            group by aa.update_date_format order by aa.update_date_format ";
        $data["What is the current status of my active shipments?"] ="SELECT count(*)
                FROM public.kln_ocean oo
                WHERE  <{ExtendHand_KLN}> and ((oo.ata is not null and oo.ata >= CURRENT_DATE - INTERVAL '3 months' AND oo.ata < CURRENT_DATE) 
                    or not exists( select 1 from public.ocean_milestone a where a.serial_no = oo.serial_no and a.act_date is not null  and a.code = 'IFFDEL')) and oo.transport_mode = 'sea' and order_from = 'public';with oo as(
                SELECT h_bol, place_of_receipt_exp, place_of_delivery_exp,serial_no,transport_mode,order_from
                FROM public.kln_ocean oo
                WHERE  <{ExtendHand_KLN}> and ((oo.ata is not null and oo.ata >= CURRENT_DATE - INTERVAL '3 months' AND oo.ata < CURRENT_DATE) 
                    or not exists( select 1 from public.ocean_milestone a where a.serial_no = oo.serial_no and a.act_date is not null  and a.code = 'IFFDEL')) and oo.transport_mode = 'sea' and order_from = 'public' order by id limit 10 
            )

            SELECT oo.*,mil.description,mil.act_date,mil.act_time,o.cargo_type,
                COALESCE(jsonb_data->>'milestone','')::jsonb->>'timezone' as timezone,
                COALESCE(jsonb_data->>'milestone','')::jsonb->>'locations' as locations
            from oo  
            inner join LATERAL (select case when is_hazardous = 't' then 'Dangerous Goods'::text else 'General'::text end as cargo_type 
                from public.ocean o where o.serial_no = oo.serial_no) o on true
            left join LATERAL (select a.code,s.description,to_char(a.act_date, 'YYYY-MM-DD') as act_date ,a.act_time
                from public.ocean_milestone a 
                    left join public.customer_service_milestone_sno s on a.code = s.code 
                    and s.type = 'sea' 
                    and a.serial_no = oo.serial_no
                    and a.act_date is not null 
                    order by s.sno desc limit 1) mil on true
            left join LATERAL (select public.getTimeAndLocationForKln(oo.serial_no,mil.code,''::text)::jsonb as jsonb_data) lt on true
            where oo.transport_mode = 'sea' and order_from = 'public'
            union all 
            SELECT oo.*,mil.description,mil.act_date,mil.act_time,o.cargo_type,
                COALESCE(jsonb_data->>'milestone','')::jsonb->>'timezone' as timezone,
                COALESCE(jsonb_data->>'milestone','')::jsonb->>'locations' as locations
            from oo  
            inner join LATERAL (select case when is_hazardous = 't' then 'Dangerous Goods'::text else 'General'::text end as cargo_type 
                from sfs.ocean o where o.serial_no = oo.serial_no) o on true
            left join LATERAL (select a.code,s.description,to_char(a.act_date, 'YYYY-MM-DD') as act_date ,a.act_time
                from public.ocean_milestone a 
                    left join public.customer_service_milestone_sno s on a.code = s.code 
                    and s.type = 'air' 
                    and a.serial_no = oo.serial_no
                    and a.act_date is not null 
                    order by s.sno desc limit 1) mil on true
                left join LATERAL (select public.getTimeAndLocationForKln(oo.serial_no,mil.code,''::text)::jsonb as jsonb_data) lt on true		
            where oo.transport_mode = 'sea' and order_from = 'sfs'
            union all 
            SELECT oo.*,mil.description,mil.act_date,mil.act_time,o.cargo_type,
                COALESCE(jsonb_data->>'milestone','')::jsonb->>'timezone' as timezone,
                COALESCE(jsonb_data->>'milestone','')::jsonb->>'locations' as locations
            from oo  
            inner join LATERAL (select case when is_hazardous = 't' then 'Dangerous Goods'::text else 'General'::text end as cargo_type 
                from public.ocean o where o.serial_no = oo.serial_no) o on true
            left join LATERAL (select a.code,s.description,to_char(a.act_date, 'YYYY-MM-DD') as act_date ,a.act_time
                from public.air_milestone a 
                    left join public.customer_service_milestone_sno s on a.code = s.code 
                    and s.type = 'air' 
                    and a.serial_no = oo.serial_no
                    and a.act_date is not null 
                    order by s.sno desc limit 1) mil on true
            left join LATERAL (select public.getTimeAndLocationForKln(oo.serial_no,mil.code,''::text)::jsonb as jsonb_data) lt on true		
            where oo.transport_mode = 'air' and order_from = 'public'
            union all 
            SELECT oo.*,mil.description,mil.act_date,mil.act_time,o.cargo_type,
                COALESCE(jsonb_data->>'milestone','')::jsonb->>'timezone' as timezone,
                COALESCE(jsonb_data->>'milestone','')::jsonb->>'locations' as locations
            from oo  
            inner join LATERAL (select case when is_hazardous = 't' then 'Dangerous Goods'::text else 'General'::text end as cargo_type 
                from sfs.ocean o where o.serial_no = oo.serial_no) o on true
            left join LATERAL (select a.code,s.description,to_char(a.act_date, 'YYYY-MM-DD') as act_date ,a.act_time
                from sfs.air_milestone a 
                    left join public.customer_service_milestone_sno s on a.code = s.code 
                    and s.type = 'air' 
                    and a.serial_no = oo.serial_no
                    and a.act_date is not null 
                    order by s.sno desc limit 1) mil on true
            left join LATERAL (select public.getTimeAndLocationForKln(oo.serial_no,mil.code,''::text)::jsonb as jsonb_data) lt on true		
            where oo.transport_mode = 'air' and order_from = 'sfs'";

        $data["List shipments with container status updates in the last 7 days."] = "select count(*)			
            FROM ra_online_container_status s
                LEFT JOIN oc_container oc ON s.status_id = oc.status_id
                LEFT JOIN ocean o ON o.serial_no::text = oc.serial_no::text
                LEFT JOIN public.ra_online_edi_event e on s.event_base = e.ra_name 
            WHERE o.status::text <> 'Cancelled'::text 
                and is_display = true  
                and s.insert_date <= CURRENT_DATE AND s.insert_date >='2023-02-23'
                and exists(select 1 from kln_ocean oo where <{ExtendHand_KLN}> and oo.serial_no = o.serial_no);select oo.serial_no,oo.order_from,s.event_base as event,s.container_no,
                to_char(to_timestamp(s.event_date, 'YYYYMMDD'), 'YYYY-MM-DD') as eventdate,
                to_char(to_timestamp(s.event_date, 'YYYYMMDD'),'Mon DD') as _eventdate,
                to_char(to_timestamp(s.event_time, 'HH24MI'), 'HH24:MI') as eventtime,
                (select time_zone from public.city_timezone where uncode = s.event_code) as timezone,
                e.description,
                s.event_city as uncity				
            FROM ra_online_container_status s
                LEFT JOIN oc_container oc ON s.status_id = oc.status_id
                LEFT JOIN ocean o ON o.serial_no::text = oc.serial_no::text
                LEFT JOIN public.ra_online_edi_event e on s.event_base = e.ra_name 
                LEFT JOIN public.kln_ocean oo ON oo.serial_no::text = o.serial_no::text
            WHERE o.status::text <> 'Cancelled'::text 
                and is_display = true  
                and s.insert_date <= CURRENT_DATE AND s.insert_date >='2023-02-23'
                and exists(select 1 from kln_ocean oo where  <{ExtendHand_KLN}> and oo.serial_no = o.serial_no) limit 10;select aa._eventdate, COUNT(*) AS total_count
            from (select DISTINCT ON (s.container_no) s.container_no,
                to_char(to_timestamp(s.event_date, 'YYYYMMDD'),'Mon DD') as _eventdate
                FROM ra_online_container_status s
                    LEFT JOIN oc_container oc ON s.status_id = oc.status_id
                    LEFT JOIN ocean o ON o.serial_no::text = oc.serial_no::text
                    LEFT JOIN public.ra_online_edi_event e on s.event_base = e.ra_name 
                WHERE o.status::text <> 'Cancelled'::text 
                    and is_display = true  
                    and s.insert_date <= CURRENT_DATE AND s.insert_date >='2023-02-23'
                    and exists(select 1 from kln_ocean oo where <{ExtendHand_KLN}> and oo.serial_no = o.serial_no)
                    order by s.container_no
            )aa group by _eventdate order by _eventdate";

        $data["Today's shipments summary."] = "select count(*)
            from (
            select oo.serial_no
                from (
                    select t.serial_no from (
                        SELECT 
                            a.serial_no,
                            ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
                            from public.ocean_milestone a 
                                left join public.customer_service_milestone_sno s on a.code = s.code 
                            where s.type = 'sea' 
                                and a.act_date is not null
                                --and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
                        )t WHERE rn = 1
                    ) po inner join public.kln_ocean oo on oo.serial_no = po.serial_no and (<{ExtendHand_KLN}>) 
            union all 
            select oo.serial_no
                from (
                    select t.serial_no from (
                        SELECT 
                            a.code,a.serial_no,
                            ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
                            from public.air_milestone a 
                                left join public.customer_service_milestone_sno s on a.code = s.code 
                            where s.type = 'sea' 
                                and a.act_date is not null
                                --and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
                        )t WHERE rn = 1
                    ) pa inner join public.kln_ocean oo on oo.serial_no = pa.serial_no and (<{ExtendHand_KLN}>)
            union all 
            select oo.serial_no
                from (
                    select t.serial_no from (
                        SELECT 
                            a.serial_no,
                            ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
                            from sfs.air_milestone a 
                                left join public.customer_service_milestone_sno s on a.code = s.code 
                            where s.type = 'sea' 
                                and a.act_date is not null
                                --and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
                        )t WHERE rn = 1
                    ) sa inner join public.kln_ocean oo on oo.serial_no = sa.serial_no and (<{ExtendHand_KLN}>)
            )t;select *
            from (
            select *,oo.serial_no,oo.order_from,oo.h_bol,oo.transport_mode,oo.place_of_receipt_exp, oo.place_of_delivery_exp,
                    COALESCE(jsonb_data->>'milestone','')::jsonb->>'timezone' as timezone,
                    COALESCE(jsonb_data->>'milestone','')::jsonb->>'locations' as locations 
                from (
                    select *,public.getTimeAndLocationForKln(t.serial_no,t.code,''::text)::jsonb as jsonb_data 
                        from (
                        SELECT 
                            case when a.code = 'IFFDEP' then 'Departure'
                                when a.code = 'IFFARR' then 'Arrived'
                                when a.code = 'IFFDEL' then 'Delivered'
                            else  s.description end as action_type, 	
                            a.update_date,a.code,a.serial_no,
                        to_char(a.update_date, 'Mon_DD_YYYY') as _update_date,
                            to_char(a.act_date, 'YYYY-MM-DD') as act_date ,
                            a.act_time,
                            ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
                            from public.ocean_milestone a 
                                left join public.customer_service_milestone_sno s on a.code = s.code 
                            where s.type = 'sea' 
                                and a.act_date is not null
                                --and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
                        )t WHERE rn = 1
                    ) po inner join public.kln_ocean oo on oo.serial_no = po.serial_no 
            union all 
            select *,oo.serial_no,oo.order_from,oo.h_bol,oo.transport_mode,oo.place_of_receipt_exp, oo.place_of_delivery_exp,
                    COALESCE(jsonb_data->>'milestone','')::jsonb->>'timezone' as timezone,
                    COALESCE(jsonb_data->>'milestone','')::jsonb->>'locations' as locations 
                from (
                    select *,public.getTimeAndLocationForKln(t.serial_no,t.code,''::text)::jsonb as jsonb_data 
                        from (
                        SELECT 
                            case when a.code = 'IFFDEP' then 'Departure'
                                when a.code = 'IFFARR' then 'Arrived'
                                when a.code = 'IFFDEL' then 'Delivered'
                            else  s.description end as action_type, 	
                            a.update_date,a.code,a.serial_no,
                            to_char(a.update_date, 'Mon_DD_YYYY') as _update_date,
                            to_char(a.act_date, 'YYYY-MM-DD') as act_date ,
                            a.act_time,
                            ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
                            from public.air_milestone a 
                                left join public.customer_service_milestone_sno s on a.code = s.code 
                            where s.type = 'sea' 
                                and a.act_date is not null
                                --and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
                        )t WHERE rn = 1
                    ) pa inner join public.kln_ocean oo on oo.serial_no = pa.serial_no 
            union all 
            select *,oo.serial_no,oo.order_from,oo.h_bol,oo.transport_mode,oo.place_of_receipt_exp, oo.place_of_delivery_exp,
                    COALESCE(jsonb_data->>'milestone','')::jsonb->>'timezone' as timezone,
                    COALESCE(jsonb_data->>'milestone','')::jsonb->>'locations' as locations 
                from (
                    select *,public.getTimeAndLocationForKln(t.serial_no,t.code,''::text)::jsonb as jsonb_data 
                        from (
                        SELECT 
                            case when a.code = 'IFFDEP' then 'Departure'
                                when a.code = 'IFFARR' then 'Arrived'
                                when a.code = 'IFFDEL' then 'Delivered'
                            else  s.description end as action_type, 	
                            a.update_date,a.code,a.serial_no,
                            to_char(a.update_date, 'Mon_DD_YYYY') as _update_date,
                            to_char(a.act_date, 'YYYY-MM-DD') as act_date ,
                            a.act_time,
                            ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
                            from sfs.air_milestone a 
                                left join public.customer_service_milestone_sno s on a.code = s.code 
                            where s.type = 'sea' 
                                and a.act_date is not null
                                --and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
                        )t WHERE rn = 1
                    ) sa inner join public.kln_ocean oo on oo.serial_no = sa.serial_no 
            )t  limit 10;select sum(case when (action_type='Departure') then 1 else 0 end) as dep, 
                sum(case when (action_type='Arrived') then 1 else 0 end) as arr,
                sum(case when (action_type='Delivered') then 1 else 0 end) as del
            from (
            select action_type
                from (
                    select t.action_type,t.serial_no from (
                        SELECT 
                            case when a.code = 'IFFDEP' then 'Departure'
                                when a.code = 'IFFARR' then 'Arrived'
                                when a.code = 'IFFDEL' then 'Delivered'
                            else  s.description end as action_type,
                            a.serial_no,				
                            ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
                            from public.ocean_milestone a 
                                left join public.customer_service_milestone_sno s on a.code = s.code 
                            where s.type = 'sea' 
                                and a.act_date is not null
                                --and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
                        )t WHERE rn = 1
                    ) po inner join public.kln_ocean oo on oo.serial_no = po.serial_no and (<{ExtendHand_KLN}>) 
            union all 
            select action_type
                from (
                    select t.action_type,t.serial_no from (
                        SELECT 
                            case when a.code = 'IFFDEP' then 'Departure'
                                when a.code = 'IFFARR' then 'Arrived'
                                when a.code = 'IFFDEL' then 'Delivered'
                            else  s.description end as action_type,
                            a.serial_no,
                            ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
                            from public.air_milestone a 
                                left join public.customer_service_milestone_sno s on a.code = s.code 
                            where s.type = 'sea' 
                                and a.act_date is not null
                                --and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
                        )t WHERE rn = 1
                    ) pa inner join public.kln_ocean oo on oo.serial_no = pa.serial_no and (<{ExtendHand_KLN}>)
            union all 
            select action_type
                from (
                    select t.action_type,t.serial_no from (
                        SELECT 
                            case when a.code = 'IFFDEP' then 'Departure'
                                when a.code = 'IFFARR' then 'Arrived'
                                when a.code = 'IFFDEL' then 'Delivered'
                            else  s.description end as action_type,
                            a.serial_no,
                            ROW_NUMBER() OVER(PARTITION BY a.serial_no ORDER BY s.sno DESC) AS rn
                            from sfs.air_milestone a 
                                left join public.customer_service_milestone_sno s on a.code = s.code 
                            where s.type = 'sea' 
                                and a.act_date is not null
                                --and a.update_date >= CURRENT_DATE AND a.update_date < CURRENT_DATE + INTERVAL '1 day'
                        )t WHERE rn = 1
                    ) sa inner join public.kln_ocean oo on oo.serial_no = sa.serial_no and (<{ExtendHand_KLN}>)
            )t";

        $data["Sort my active shipments by earliest arrival date."] = "select to_char(oo.eta,'DD-Mon') as eta, oo.h_bol,oo.transport_mode, oo.place_of_receipt_exp, oo.place_of_delivery_exp,oo.serial_no,oo.order_from,
                    case when oo.eta - CURRENT_DATE <= 0 then '< 1 days'::text
                        else (oo.eta - CURRENT_DATE)||' days'::text end as day_to_arr
                from  public.kln_ocean oo where 1=1   order by eta  limit 10";

        return $data[$type];
    }
}
?>