k_online_backend/service/ocean_order.class.php

<?php

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

/**
 * Description of ocean
 *
 * @author Administrator
 */
class ocean_order {

    private static $_ocean;

    function __construct() {  
       
    }

    public static function getInstance() {
        global $memory_limit;
        $memory_limit = ini_get("memory_limit");
        ini_set("memory_limit", '2048M');
        if (!self::$_ocean) {
            $c = __CLASS__;
            self::$_ocean = new $c;
        }
        return self::$_ocean;
    }

    public function ocean_order() {
        $operate = utils::_get('operate');
        $operate = strtolower($operate);

        if (empty($operate)) {
            //search
            //栏位信息
            $column = column::getInstance()->getDisplayColumn('Ocean_Search');
            $BookingTableColumns = column::getInstance()->tableColumns('Ocean_Search',$column);
            $data['TrackingTableColumns'] = $BookingTableColumns;
            common::echo_json_encode(200,$data);
            exit();
        }

        if ($operate == "search") {
            $this->_ocean_search();
        }

        if ($operate == "detail") {
            $serial_no = common::deCode($_GET['a'], 'D');
            $transport_mode = common::excuteOneSql("SELECT transport_mode FROM public.kln_ocean ob WHERE  ob.serial_no = '$serial_no'");
            if ($transport_mode == "sea"){
                $this->_ocean_detail();
            } elseif ($transport_mode == "air"){
                $this->_air_ocean_detail();
            } else {
                $this->_ocean_detail();
            }
        }

        if ($operate == "ams_isf_log"){
            $this->_ams_isf_log();
        }

        /*
         * export ocean order to excel
        */
        if ($operate == "excel") {
            $this->_ocean_excel();
        }

        /*
         * download Revenue
        */
        if ($operate == "revenue") {
            $this->_revenue_download();
        }
       
        if ($operate == 'setting_ocean_order_display') {
            column::getInstance()->settingDisplay('Ocean_Search', 'main_new_version.php?action=ocean_order');
        }

        /*
        * download document
        */
        if ($operate == "download") {
            $this->_download();
        }

        /*
        * document upload
        */
        if ($operate == "document_upload"){
            //只查询public,两边数据一样，不用去同步更新新加的Client_Upload ，Notify Station，Auto-sync to Station
            $transport_mode = $_POST['transport_mode'];
            if($transport_mode == 'sea'){
                $document = _getViewDocType($_REQUEST["_schemas"]);
            }else{
                $document = _getAirViewDocType($_REQUEST["_schemas"]);
            }
            $file_serial_nos = array();
            foreach ($document as $v) {
                $types = $v['serial_no'];
                $types = strtolower($types);
                $types = explode(";", $types);
                foreach ($types as $k => $type) {
                    $file_serial_nos[] = $type;
                }
            }
            $file_serial_nos = utils::implode(';',$file_serial_nos);
            if($transport_mode == 'sea'){
                $sql = "select display_name,format_name as file_type,serial_no,m_h from public.ra_online_file_format where lower(serial_no) " . common::getInNotInSql($file_serial_nos) . " 
                        and active = true and client_upload = true";
            }else{
                $sql = "select display_name,format_name as file_type,serial_no,m_h from public.air_file_format where lower(serial_no) " . common::getInNotInSql($file_serial_nos) . " 
                    and active = true and client_upload = true";
            }
            $display_name = common::excuteListSql($sql);
            $data['File Type'] = $display_name;
            common::echo_json_encode(200,$data);
            exit();
        }

        /*
        * document upload do
        */
        if ($operate == "document_upload_do"){
            //checked from 
            if(empty($_POST['file_type']) || empty($_FILES['file']["name"])){
                $data = array(
                    'msg' => 'File Type or File must select.'
                );
                common::echo_json_encode(500,$data);
                exit();
            }
            $_schemas = $_REQUEST["_schemas"];
            $transport_mode = $_POST['transport_mode'];
            $bol = $_POST['h_bol'];
            if ($transport_mode == "sea") {
                $count = common::excuteObjectSql("select * from ocean where lower(h_bol) = '" . strtolower($bol) . "'");
            } elseif ($transport_mode == "air") {
                $count = common::excuteObjectSql("select * from air where lower(hawb) = '" . strtolower($bol) . "'");
            }
            if (empty($count) || utils::count($count) <= 0){
                $data = array(
                    'msg' => 'HBOL does not exist.'
                );
                common::echo_json_encode(500,$data);
                exit();
            }

            $file_type = $_POST['file_type'];
            $_table =  $transport_mode == "sea" ? "ra_online_doc_upload" : "air_doc_upload";
            $_table_format =  $transport_mode == "sea" ? "ra_online_file_format" : "air_file_format";
            //格式配置只查询public
            $format = common::excuteObjectSql("select serial_no,standard,client_upload,notify_station,auto_sync_to_station from public.$_table_format where format_name = '$file_type'");       
            //查询存在的文件类型最大序号
            $num = common::excuteOneSql("select count(*) from $_table where lower(bol) = '" . strtolower($bol) . "' and  format_serailno = '".$format['serial_no']."'");
            try {
                //$allow_extend = "|.pdf|.xlsx|.docx|";
                $allow_extend = "|.pdf|";
                $file_name = $_FILES['file']["name"];
                $errorMsg = "";
                $sql = "";
                
                foreach ($file_name as $i => $v) {
                    if (empty($v)) {
                        continue;
                    }
                    $size = floor($_FILES ["file"]['size'][$i] / 1024 / 1024);
                    if ($size > 5) {
                        $errorMsg = $_FILES['file']["name"][$i] ." is larger than 5M.";
                        continue;
                    }
                    if ($_FILES["file"]["error"][$i] > 0) {
                        $errorMsg = " File Upload Error(". $_FILES ["file"]["error"][$i] .")";
                        continue;
                    }
                    $path_parts = pathinfo($file_name[$i]);
                    $exten = $path_parts['extension'];
                    if (stristr($allow_extend, "|." . $exten . "|") === FALSE) {
                        $errorMsg = $_FILES['file']["name"][$i].', File type error.';
                        //continue;
                    }

                    $filename = $bol."_".$format['standard']."_V".($i+1+$num). "." . $exten;
                    $file_size = floor($_FILES ["file"]['size'][$i] / 1024) . "KB";
                    $order_type =  $transport_mode == "sea" ? "ocean" : "air";
                   
                    $origin_station = $count["origin_station"];
                    $destination_station = $count["destination_station"];

                    $format_serialno = $format['serial_no'];
                    $serial_no = utils::uuid();

                    $from_station = $count["from_station"];
                    if (strtolower($_schemas) == "public") {
                        if ($transport_mode == "sea")
                            $folder = DOCUMENT_OCEAN_UPLOAD_PATH . $from_station;
                        elseif ($transport_mode == "air")
                            $folder = DOCUMENT_AIR_UPLOAD_PATH . $from_station;
                    }else {
                        if ($transport_mode == "sea")
                            $folder = DOCUMENT_SFSOCEAN_UPLOAD_PATH . $from_station;
                        elseif ($transport_mode == "air")
                            $folder = DOCUMENT_SFSAIR_UPLOAD_PATH . $from_station;
                    }
    
                    $save_path = $folder . DS . date("Ymd") . DS;
                    if (common::mkdirs($save_path) === FALSE){
                        $errorMsg = "Save Folder create error.";
                        continue;
                    }
                    //pdf文件不需要处理宏，直接保存
                    if($exten == "pdf"){
                        if (move_uploaded_file($_FILES['file']['tmp_name'][$i], $save_path . $filename)) {
                            $sql .= "insert into $_schemas.$_table (file_name, file_path, upload_date, upload_ip, upload_by, bol, file_type, file_size, online_upload, 
                                origin, destination, from_station, serial_no, 
                                online_format,format_serailno,source_filename) 
                            values ('" . common::check_input($filename) . "','" . common::check_input($save_path) . "', now(), '" . common::ip() . "', 
                                    '" . common::check_input(_getLoginName()) . "','" . common::check_input(strtoupper($bol)) . "', '$order_type', '" . common::check_input($file_size) . "',
                                    't', '" . common::check_input($origin_station) . "', '" .common::check_input($destination_station) . "',
                                    '" . common::check_input($from_station) . "', '" . $serial_no . "',
                                    '" . common::check_input($file_type) . "','" . common::check_input($format_serialno) . "','" . common::check_input($filename) . "');";        
                        } else {
                            $errorMsg = "Upload Filed Failed.";
                            continue;
                        }
                    } else {
                        $outputFileName = $save_path . $filename;
                        if(move_uploaded_file($_FILES['file']['tmp_name'][$i],$outputFileName)){
                            //判断文件时候待宏，如果则这删除
                            if(utils::hasMacros($outputFileName)){
                                unlink($outputFileName);
                                $errorMsg = "Upload Filed Has Macros,Please remove and re-upload.";
                                continue;
                            }
                            $sql .= "insert into $_schemas.$_table (file_name, file_path, upload_date, upload_ip, upload_by, bol, file_type, file_size, online_upload, 
                                    origin, destination, from_station, serial_no, 
                                    online_format,format_serailno,source_filename) 
                                values ('" . common::check_input($filename) . "','" . common::check_input($save_path) . "', now(), '" . common::ip() . "', 
                                        '" . common::check_input(_getLoginName()) . "','" . common::check_input(strtoupper($bol)) . "', '$order_type', '" . common::check_input($file_size) . "',
                                        't', '" . common::check_input($origin_station) . "', '" .common::check_input($destination_station) . "',
                                        '" . common::check_input($from_station) . "', '" . $serial_no . "',
                                        '" . common::check_input($file_type) . "','" . common::check_input($format_serialno) . "','" . common::check_input($filename) . "');";
                        } else {
                            $errorMsg = "Upload Filed Failed.";
                            continue;
                        }
                    } 
                    //根据配置，看需要同步文件,
                    $doc_upload_log_table =  $transport_mode == "sea" ? "ra_online_doc_upload_log" : "air_doc_upload_log";
                    if(!empty($format['auto_sync_to_station'])){
                        if($format['auto_sync_to_station'] == "Origin Station"){
                            $sql .= "INSERT INTO public.$doc_upload_log_table(serial_no, station, download_time)
                                VALUES ('".$serial_no."', '" .common::check_input($destination_station) . "', now());";
                        }
                        if($format['auto_sync_to_station'] == "Destination Station"){
                            $sql .= "INSERT INTO public.$doc_upload_log_table(serial_no, station, download_time)
                                VALUES ('".$serial_no."', '" .common::check_input($origin_station) . "', now());";
                        }
                        if($format['auto_sync_to_station'] == "Origin and Destination Stations"){
                            //这个都同步，不需要手动补充记录去阻止文件的同步
                        }
                    } else {
                        //如果没有配置。这代表不同步文件
                        $sql .= "INSERT INTO public.$doc_upload_log_table(serial_no, station, download_time)
                                VALUES ('".$serial_no."', '" .common::check_input($destination_station) . "', now());";
                        $sql .= "INSERT INTO public.$doc_upload_log_table(serial_no, station, download_time)
                                VALUES ('".$serial_no."', '" .common::check_input($origin_station) . "', now());";       
                    }
                }
                //根据配置发送通知邮件 相同文件类型只提醒一次
                if(!empty($format['notify_station'])  && empty($errorMsg)){
                    $origin_op_email = common::excuteOneSql("select email from employee where employee_id = '".$count["origin_op_id"]."' limit 1");
                    $dest_op_email = common::excuteOneSql("select email from employee where employee_id = '".$count["dest_op"]."' limit 1");
                    $ks_from = common::excuteOneSql("select ksmart_station from public.contract_region_mapping where overeas = '".$count["from_station"]."' limit 1");
                    $ks_to = common::excuteOneSql("select ksmart_station from public.contract_region_mapping where overeas = '".$count["destination_station"]."' limit 1");
                    $ks_from = empty($ks_from)? 'f' : $ks_from;
                    $ks_to = empty($ks_to)? 'f' : $ks_to;
                    if($ks_from == "f" && $ks_to == "f"){
                        $errorMsg = "Both Non Ksmart,Please checked.";
                    }
                    
                    $curr_date_config = common::excuteObjectSql("SELECT to_char(now()::date,'Mon_DD_YYYY') as d1_day,to_char(now()::date,'Mon_DD_YYYY HH:MM') as d2_day");
                    $subject = "External Documentation Upload Notification"."_".$curr_date_config['d1_day'];
                    $email_from = "US.KApex.Online@kerryapex.com";
                    $upload_user_name = _getLoginName();
                    $upload_user_email = _getLoginEamil();
                    //如果有多個就留空，給Email信息就行
                    if ($transport_mode == "sea") {
                        $company =utils::getConpanyForNotify($_schemas,'ocean');  
                    }else{
                        $company =utils::getConpanyForNotify($_schemas,'air');
                    }

                    //Ksmart--> Ksmart
                    if ($ks_from == "t" && $ks_to == "t"){
                        if($format['notify_station'] == "Origin Station" || $format['notify_station'] == "Origin and Destination Stations"){
                            if(!empty($origin_op_email)){
                                //立即发送
                                $content = utils::getKlnDocNotifyContent($bol,$file_type,$upload_user_name,$upload_user_email,$company,$curr_date_config['d2_day']);
                                $sql .= "INSERT INTO email_record(type, title, from_email, to_email, content, insert_date, cc_email, attachment_path)
                                    VALUES ('KLN_DOC_Notify', '" . pg_escape_string($subject) . "', '" . pg_escape_string($email_from) . "', '" .
                                    pg_escape_string($origin_op_email) . "', '" . pg_escape_string($content) . "', now(), '', '');";
                            }else{
                                //加入队列状态
                                $sql .=  "INSERT INTO public.kln_online_upload_file_notify(h_bol, file_type, upload_by, upload_time, email, company, 
                                            is_send, checked_time, checked_type,origin_op,dest_op)
                                    VALUES ('$bol','$file_type','$upload_user_name', now(),'$upload_user_email', '$company', 
                                            false, now(), 'origin_op_email','".$count["origin_op_id"]."','".$count["dest_op"]."');";
                                //做好加入队列时的日志记录
                                $user_type = _isApexLogin() ? "Employee" : "Customer";
                                $detail = $bol.": $file_type File upload,origin_op_email is null, Add Notification wait";       
                                utils::single_operation_log_save($user_type,$upload_user_name,"Tracking","upload_file_pending",$detail);            
                            }
                        }
                        if($format['notify_station'] == "Destination Station" || $format['notify_station'] == "Origin and Destination Stations"){
                            if(!empty($dest_op_email)){
                                //立即发送
                                $content = utils::getKlnDocNotifyContent($bol,$file_type,$upload_user_name,$upload_user_email,$company,$curr_date_config['d2_day']);
                                $sql .= "INSERT INTO email_record(type, title, from_email, to_email, content, insert_date, cc_email, attachment_path)
                                    VALUES ('KLN_DOC_Notify', '" . pg_escape_string($subject) . "', '" . pg_escape_string($email_from) . "', '" .
                                    pg_escape_string($dest_op_email) . "', '" . pg_escape_string($content) . "', now(), '', '');";
                            }else{
                                //加入队列状态
                                $sql .=  "INSERT INTO public.kln_online_upload_file_notify(h_bol, file_type, upload_by, upload_time, email, company, 
                                            is_send, checked_time, checked_type,origin_op,dest_op)
                                    VALUES ('$bol','$file_type','$upload_user_name', now(),'$upload_user_email', '$company', 
                                            false, now(), 'dest_op_email','".$count["origin_op_id"]."','".$count["dest_op"]."');";
                                //做好加入队列时的日志记录
                                $user_type = _isApexLogin() ? "Employee" : "Customer";
                                $detail = $bol.": $file_type File upload,dest_op_email is null, Add Notification wait";       
                                utils::single_operation_log_save($user_type,$upload_user_name,"Tracking","upload_file_pending",$detail);            
                            }
                        }
                    }

                    //Ksmart-->non Ksmart
                    if ($ks_from == "t" && $ks_to == "f"){
                        if($format['notify_station'] == "Destination Station" || $format['notify_station'] == "Origin and Destination Stations"){
                            //避免重复邮件发给origin_op，  dest_op信息缺失的话，会发送给origin_op
                            $dest_op_email_temp = empty($dest_op_email)? $origin_op_email:$dest_op_email;
                            if(!empty($dest_op_email_temp)){
                                //立即发送
                                $content = utils::getKlnDocNotifyContent($bol,$file_type,$upload_user_name,$upload_user_email,$company,$curr_date_config['d2_day']);
                                $sql .= "INSERT INTO email_record(type, title, from_email, to_email, content, insert_date, cc_email, attachment_path)
                                    VALUES ('KLN_DOC_Notify', '" . pg_escape_string($subject) . "', '" . pg_escape_string($email_from) . "', '" .
                                    pg_escape_string($dest_op_email_temp) . "', '" . pg_escape_string($content) . "', now(), '', '');";
                            }else{
                                //加入队列状态 如果缺失，用origin_op_email
                                $sql .=  "INSERT INTO public.kln_online_upload_file_notify(h_bol, file_type, upload_by, upload_time, email, company, 
                                            is_send, checked_time, checked_type,origin_op,dest_op)
                                    VALUES ('$bol','$file_type','$upload_user_name', now(),'$upload_user_email', '$company', 
                                            false, now(), 'origin_op_email','".$count["origin_op_id"]."','".$count["dest_op"]."');";
                                //做好加入队列时的日志记录
                                $user_type = _isApexLogin() ? "Employee" : "Customer";
                                $detail = $bol.": $file_type File upload,origin_op_email is null, Add Notification wait";       
                                utils::single_operation_log_save($user_type,$upload_user_name,"Tracking","upload_file_pending",$detail);             
                            }
                        }

                        if($format['notify_station'] == "Origin Station" || $format['notify_station'] == "Origin and Destination Stations"){
                            if(empty($dest_op_email) && $format['notify_station'] == "Origin and Destination Stations"){

                            }else{
                            if(!empty($origin_op_email)){
                                //立即发送
                                $content = utils::getKlnDocNotifyContent($bol,$file_type,$upload_user_name,$upload_user_email,$company,$curr_date_config['d2_day']);
                                $sql .= "INSERT INTO email_record(type, title, from_email, to_email, content, insert_date, cc_email, attachment_path)
                                    VALUES ('KLN_DOC_Notify', '" . pg_escape_string($subject) . "', '" . pg_escape_string($email_from) . "', '" .
                                    pg_escape_string($origin_op_email) . "', '" . pg_escape_string($content) . "', now(), '', '');";
                            }else{
                                //加入队列状态
                                $sql .=  "INSERT INTO public.kln_online_upload_file_notify(h_bol, file_type, upload_by, upload_time, email, company, 
                                            is_send, checked_time, checked_type,origin_op,dest_op)
                                    VALUES ('$bol','$file_type','$upload_user_name', now(),'$upload_user_email', '$company', 
                                            false, now(), 'origin_op_email','".$count["origin_op_id"]."','".$count["dest_op"]."');";
                                //做好加入队列时的日志记录
                                $user_type = _isApexLogin() ? "Employee" : "Customer";
                                $detail = $bol.": $file_type File upload,origin_op_email is null, Add Notification wait";       
                                utils::single_operation_log_save($user_type,$upload_user_name,"Tracking","upload_file_pending",$detail);            
                            }
                            }
                        }
                    }

                    //Non Ksmart-->Ksmart
                    if ($ks_from == "f" && $ks_to == "t"){
                        if($format['notify_station'] == "Origin Station" || $format['notify_station'] == "Origin and Destination Stations"){
                            $origin_op_email_temp = empty($origin_op_email)? $dest_op_email:$origin_op_email;
                            if(!empty($origin_op_email_temp)){
                                //立即发送
                                $content = utils::getKlnDocNotifyContent($bol,$file_type,$upload_user_name,$upload_user_email,$company,$curr_date_config['d2_day']);
                                $sql .= "INSERT INTO email_record(type, title, from_email, to_email, content, insert_date, cc_email, attachment_path)
                                    VALUES ('KLN_DOC_Notify', '" . pg_escape_string($subject) . "', '" . pg_escape_string($email_from) . "', '" .
                                    pg_escape_string($origin_op_email_temp) . "', '" . pg_escape_string($content) . "', now(), '', '');";
                            }else{
                                //加入队列状态
                                $sql .=  "INSERT INTO public.kln_online_upload_file_notify(h_bol, file_type, upload_by, upload_time, email, company, 
                                            is_send, checked_time, checked_type,origin_op,dest_op)
                                    VALUES ('$bol','$file_type','$upload_user_name', now(),'$upload_user_email', '$company', 
                                            false, now(), 'dest_op_email','".$count["origin_op_id"]."','".$count["dest_op"]."');";
                                //做好加入队列时的日志记录
                                $user_type = _isApexLogin() ? "Employee" : "Customer";
                                $detail = $bol.": $file_type File upload,dest_op_email is null, Add Notification wait";       
                                utils::single_operation_log_save($user_type,$upload_user_name,"Tracking","upload_file_pending",$detail);             

                            }
                        }
                        if($format['notify_station'] == "Destination Station" || $format['notify_station'] == "Origin and Destination Stations"){
                            //避免重复邮件发给dest_op，  origin_op信息缺失的话，会发送给dest_op
                            if(empty($origin_op_email) && $format['notify_station'] == "Origin and Destination Stations"){

                            }else{
                            if(!empty($dest_op_email)){
                                //立即发送
                                $content = utils::getKlnDocNotifyContent($bol,$file_type,$upload_user_name,$upload_user_email,$company,$curr_date_config['d2_day']);
                                $sql .= "INSERT INTO email_record(type, title, from_email, to_email, content, insert_date, cc_email, attachment_path)
                                    VALUES ('KLN_DOC_Notify', '" . pg_escape_string($subject) . "', '" . pg_escape_string($email_from) . "', '" .
                                    pg_escape_string($dest_op_email) . "', '" . pg_escape_string($content) . "', now(), '', '');";
                            }else{
                                //加入队列状态
                                $sql .=  "INSERT INTO public.kln_online_upload_file_notify(h_bol, file_type, upload_by, upload_time, email, company, 
                                            is_send, checked_time, checked_type,origin_op,dest_op)
                                    VALUES ('$bol','$file_type','$upload_user_name', now(),'$upload_user_email', '$company', 
                                            false, now(), 'dest_op_email','".$count["origin_op_id"]."','".$count["dest_op"]."');";
                                //做好加入队列时的日志记录
                                $user_type = _isApexLogin() ? "Employee" : "Customer";
                                $detail = $bol.": $file_type File upload,dest_op_email is null, Add Notification wait";       
                                utils::single_operation_log_save($user_type,$upload_user_name,"Tracking","upload_file_pending",$detail);           
                            }
                            }
                        }
                    }
                }

                //代表前面有错误发生，终止SQL的执行
                if (!empty($errorMsg)) {
                    $data = array(
                        'msg' => $errorMsg
                    );
                    common::echo_json_encode(500,$data);
                    exit();
                }
                common::excuteUpdateSql($sql);
                $data = array(
                    'msg' => "success"
                );
                common::echo_json_encode(200,$data);
                exit();
            } catch (Exception $e) {
                error_log($e->getTraceAsString());
                $data = array(
                    'msg' => "Upload Filed Failed."
                );
                common::echo_json_encode(500,$data);
            }
        }

        if ($operate == "save_communication") {
            try {
                $content = $_POST["content"];
                $content = common::check_input($content);
                $text = $_POST["text"];
                $content = urldecode($content);
                $web_content = urldecode($text);

                $communication_cc = $_POST["communication_cc"];
                $serial_no = common::uuid();
                $email_uuid = $_POST["serial_no"];
                $add_by = _getLoginName();
                $refer_id = 0;
               
                $email = $this->getEmail($email_uuid);
                $from_email = "US.KApex.Online@kerryapex.com";
                $to_email = $email["email"];
                $cc_email = $_SESSION['ONLINE_USER']['email'] . ";ApexOnlineCommunication@apexshipping.com";
                $communication_cc = trim($communication_cc);
                if (!empty($communication_cc)) {
                    $communication_cc = common::check_input($communication_cc);
                    $cc_email .= ";" . $communication_cc;
                }


                $user_from = _getLoginName();
                $user_to = $email["name"];
                $user_cc = $_SESSION['ONLINE_USER']['first_name'];
                if (empty($user_cc)) {
                    $user_cc = _getLoginName();
                }
                $user_cc .= ";ApexOnlineCommunication";

                //邮件发送
                $poKey = "<br><br>Important note: when you reply this email, do not remove ApexOnlineCommunication@apexshipping.com<br>Below is system code, do not delete.<br>@@";
                $emailSql = "select encode(public.pgp_sym_encrypt('" . $serial_no . "','pom_key'), 'base64')::text;";
                $key = common::excuteOneSql($emailSql);

                $historyEmails = "";

                $title = "Communication from " . _getLoginName() . ", HBOL: " . $email["h_bol"] . ", Consignee: " . $email["consignee"];
                $emailContent = $content . $poKey . $key . ";" . "@@<br>For your convenience weblink: https://ra.kerryapex.com/<br><br>" . $historyEmails;

                common::excuteUpdateSql("insert into public.email_record (type,title,from_email,to_email,cc_email,content,insert_date)values('Communication','$title','$from_email','$to_email','$cc_email','$emailContent',now());");

                $rs = common::excuteUpdateSql("INSERT INTO public.online_ocean_communication(serial_no, email_uuid, content,web_content,user_from, user_to, user_cc, refer_id, add_by, add_time, cc_email) 
                VALUES ('$serial_no', '$email_uuid', '$emailContent','$web_content', '$user_from', '$to_email', '$cc_email', $refer_id, '$add_by', now(), '$communication_cc');");

                $emailRecords = $this->getCommunicationNew($email_uuid);
                common::echo_json_encode(200,array("msg" => "Sent Successfully", "emailRecords" => $emailRecords));
                exit();
            } catch (Exception $e) {
                common::echo_json_encode(500,array("msg" => "Sent Error."));
                exit();
            }
        }

        if ($operate == "ocean_vgm") {
            if (!_canEdiVgm()) {
                $data = array("msg" =>"Vgm Permission Error");
                common::echo_json_encode(400, $data);
                exit();
            }
            $serial_no = common::deCode(($_GET["a"]), 'D');
            $sqlWhere = " and o.serial_no='" . $serial_no . "'";
            $column = "*";
            $schemas = $_GET["_schemas"];
            if (empty($schemas)) {
                $schemas = "public";
            }
            $ocean_info_sql = "select o.from_station as o_from_station, o.h_bol as o_h_bol, o.carrier_booking, o.m_vessel, o.m_voyage, to_char(o.f_etd, 'MM/DD/YYYY') as f_etd, to_char(o.m_eta, 'MM/DD/YYYY') as m_eta, v.*, o.master_base_number from "
                    . "$schemas.ocean o left join $schemas.ocean_vgm v on o.serial_no=v.serial_no or o.master_base_number=v.master_base_number where 1=1  and o.status::text <> 'Cancelled'::text AND o.bol_type::text <> 'BOOKING'::text AND o.bol_type::text <> 'QUOTE'::text" . $sqlWhere;
            $ocean_info = common::excuteObjectSql($ocean_info_sql);

            $rs_sql = "select o.service, o.ctnr, v.carrier_booking_no, o.ctnr, o.size, v.vgm_weight, v.unit, v.vgm_time, v.vgm_method, o.grs_kgs, o.grs_lbs, ov.id as ocean_vgm_id, v.id as ocean_vgm_container_id from "
                    . "$schemas.oc_container o left join $schemas.ocean oc on o.serial_no=oc.serial_no "
                    . " left join $schemas.ocean_vgm ov on oc.serial_no=ov.serial_no or oc.master_base_number=ov.master_base_number "
                    . " left join $schemas.ocean_vgm_container v on ov.serial_no=v.serial_no and o.ctnr=v.ctnr "
                    . " where 1=1 and oc.status::text <> 'Cancelled'::text AND oc.bol_type::text <> 'BOOKING'::text AND oc.bol_type::text <> 'QUOTE'::text and oc.master_base_number='" . $ocean_info["master_base_number"] . "' and oc.from_station='" . $ocean_info["o_from_station"] . "'";
            $rs = common::excuteListSql($rs_sql);
            $ocean_info["is_send"] = (empty($ocean_info["is_send"]) ? "f" : $ocean_info["is_send"]);
            if (empty($ocean_info["id"])) {
                $ocean_info["is_send"] = "t";
            }
            $err_msg = "";
            if (empty($ocean_info["carrier_booking"])) {
                $err_msg = "Carrier Booking is empty.";
            }
            if ($schemas <> "public") {
                $err_msg .= "This hbol do not support.";
            }
            $err_msg_ctnr = "";
            $err_msg_CFS = "";
            foreach ($rs as $cv) {
                if (empty($cv["ctnr"])) {
                    $err_msg_ctnr = "Container# is empty.";
                }
                if (substr($cv["service"], 0, 3) == "CFS") {
                    $err_msg_CFS = "CFS cannot support update.";
                }
            }
            if (!empty($err_msg)) {
                $err_msg = "You cant't add this VGM, (" . $err_msg ." ". $err_msg_ctnr." ".$err_msg_CFS.")";
            }
            $serial_no = common::deCode($serial_no, "E");

            //default
            if (empty($rs["id"])) {
                $default = common::excuteObjectSql("select * from ocean_vgm_setting where login_name ilike '" . _getLoginName() . "'");
            }

            $log = common::excuteListSql("select * from ocean_vermas where from_serial_no ilike '" . $ocean_info["master_base_number"] . "'");

            //detail  information 
            $detail_information_column = array();
            $detail_information_column[] = array("title" =>"SN","field" =>"sn","formatter" =>"","edit_type" =>"normal",);
            $detail_information_column[] = array("title" =>"Carrier Booking No.","field" =>"carrier_booking_no","formatter" =>"","edit_type" =>"normal");
            $detail_information_column[] = array("title" =>"Container No","field" =>"container_no","formatter" =>"","edit_type" =>"normal");
            $detail_information_column[] = array("title" =>"VGM Weight","field" =>"vgm_weight","formatter" =>"","edit_type" =>"number");
            $detail_information_column[] = array("title" =>"VGM Unit","field" =>"vgm_kg_lg","formatter" =>"","edit_type" =>"select");
            $detail_information_column[] = array("title" =>"VGM Time","field" =>"vgm_date","formatter" =>"","edit_type" =>"dateTime");
            $detail_information_column[] = array("title" =>"VGM Method","field" =>"vgm_method","formatter" =>"","edit_type" =>"input");

            foreach($rs as $rk => $rv){
                $rs[$rk]['sn'] = $rk +1;
                $rs[$rk]['container_no'] = $rv['ctnr'];
                $rs[$rk]['vgm_kg_lg'] = $rv['unit'];
                $rs[$rk]['vgm_date'] = $rv['vgm_time'];
                //这个是前端逻辑,carrier_booking_no为空的话，取carrier_booking
                if(empty($rs[$rk]['carrier_booking_no'])){
                    $rs[$rk]['carrier_booking_no'] = $ocean_info["carrier_booking"];
                }
            }
            $detail_information =array("detail_information_column" =>$detail_information_column,
                "detail_information_data" =>$rs);

            $data = array("msg" =>$err_msg);

            $Submitter = $ocean_info["authorized_company"];
            if(empty($ocean_info["id"])){
                $Submitter = $default["submitter"];
            }

            $signature = $ocean_info["signature"];
            if(empty($ocean_info["id"])){
                $signature = $default["signature"];
            }

            $authorized_email = $ocean_info["authorized_email"];
            if(empty($ocean_info["id"])){
                $authorized_email = $default["authorized_email"];
            }

            $authorized_tel = $ocean_info["authorized_tel"];
            if(empty($ocean_info["id"])){
                $authorized_tel = $default["authorized_tel"];
            }
            
            $data["general_information"] = array("HBL No."=>$ocean_info["o_h_bol"],
                "Carrier Booking No."=>$ocean_info["carrier_booking"],
                "Vessel"=>$ocean_info["m_vessel"],"Voyage"=>$ocean_info["m_voyage"],
                "ETD"=>$ocean_info["f_etd"],"ETA"=>$ocean_info["m_eta"],
                "Last updated User"=>$ocean_info["modify_user"],"Last updated Time"=>$ocean_info["modify_time"],
                "Submitter" =>$Submitter,"signature" =>$signature,"authorized_email" =>$authorized_email,"authorized_tel" =>$authorized_tel,"is_send" =>$ocean_info['is_send']);

            $data["detail_information"] = $detail_information;
            $data["schemas"] = $schemas;
            $data["serial_no"] = $serial_no;

            common::echo_json_encode(200, $data);
            exit();
        }

        if ($operate == "save_ocean_vgm") {
            if (!_canEdiVgm()) {
                $data = array("msg" =>"Vgm Permission Error");
                common::echo_json_encode(400, $data);
                exit();
            }
            $msg = $this->save_vgm();
            if ($msg == "success"){
                $data = array("msg" =>$msg);
                common::echo_json_encode(200, $data);
            } else {
                $data = array("msg" =>$msg);
                common::echo_json_encode(500, $data);
            }
            exit();
        }
        
        /*
         * share_shipment 这个提交的意义，是为了留下记录用户信息
        */
        if ($operate == "share_shipment") {
            $data = array("msg" =>"share_shipment");
            common::echo_json_encode(200,$data);
            exit();
        }
    }

    /*
    * ocean order search
    */
    private function _ocean_search() {
        $cp = common::check_input($_POST ['cp']); //current_page
        $ps = common::check_input($_POST ['ps']); //ps
        if (empty($ps))
            $ps = 100;
        if (empty($cp))
            $cp = 1;

        $sqlWhere = ' where ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
        $sqlWhere .= search::getInstance()->getSearchSQL("Ocean_Search");

        if (!empty($_REQUEST["_reportRef"])) {
            $_reportType = $_REQUEST["_reportType"];
            $_reportRef = $_REQUEST["_reportRef"];
            if ($_reportType == "r3") {
                if ($_reportRef == "Previous 7 Days") {
                    $sqlWhere .= " and eta>(current_date-interval '7 days') and eta<current_date";
                } else if ($_reportRef == "≤1 Day") {
                    $sqlWhere .= " and eta=current_date";
                } else if ($_reportRef == "1-2 Days") {
                    $sqlWhere .= " and eta>current_date and eta<=(current_date+interval '2 days')";
                } else if ($_reportRef == "3-6 Days") {
                    $sqlWhere .= " and eta>(current_date+interval '2 days') and eta<=(current_date+interval '6 days')";
                } else if ($_reportRef == "≥7 Days") {
                    $sqlWhere .= " and eta>(current_date+interval '6 days')";
                } else {
                    $sqlWhere .= " and 1<>1";
                }
            } else if ($_reportType == "r4") {
                if ($_reportRef == "Previous 7 Days") {
                    $sqlWhere .= " and etd>(current_date-interval '7 days') and etd<current_date";
                } else if ($_reportRef == "≤1 Day") {
                    $sqlWhere .= " and etd=current_date";
                } else if ($_reportRef == "1-2 Days") {
                    $sqlWhere .= " and etd>current_date and etd<=(current_date+interval '2 days')";
                }  else if ($_reportRef == "3-6 Days") {
                    $sqlWhere .= " and etd>(current_date+interval '2 days') and etd<=(current_date+interval '6 days')";
                } else if ($_reportRef == "≥7 Days") {
                    $sqlWhere .= " and etd>(current_date+interval '6 days')";
                } else {
                    $sqlWhere .= " and 1<>1";
                }
            } else if ($_reportType == "ata_r3") {
                if ($_reportRef == "≤1 Day") {
                    $sqlWhere .= " and ata >= etd and (ata-etd) >= 0 and (ata-etd) < 1";
                } else if ($_reportRef == "1-2 Days") {
                    $sqlWhere .= " and ata >= etd and (ata-etd) >= 1 and (ata-etd) < 2";
                }  else if ($_reportRef == "3-6 Days") {
                    $sqlWhere .= " and ata >= etd and (ata-etd) >= 2 and (ata-etd) < 6";
                } else if ($_reportRef == "≥7 Days") {
                    $sqlWhere .= " and ata >= etd and (ata-etd) >= 6";
                } else {
                    $sqlWhere .= " and 1<>1";
                }
            } else if ($_reportType == "atd_r4") {
                if ($_reportRef == "≤1 Day") {
                    $sqlWhere .= " and atd >= etd and (atd-etd) >= 0 and (atd-etd) < 1";
                } else if ($_reportRef == "1-2 Days") {
                    $sqlWhere .= " and atd >= etd and (atd-etd) >= 1 and (atd-etd) < 2";
                }  else if ($_reportRef == "3-6 Days") {
                    $sqlWhere .= " and atd >= etd and (atd-etd) >= 2 and (atd-etd) < 6";
                } else if ($_reportRef == "≥7 Days") {
                    $sqlWhere .= " and atd >= etd and (atd-etd) >= 6 ";
                } else {
                    $sqlWhere .= " and 1<>1";
                }
            } else if ($_reportType == "top") {
                if (!empty($_reportRef)) {
                    $_reportStationType = $_REQUEST["_reportStationType"];
                    
                    if($_reportStationType == 'shippr_uncode'){
                        $sqlWhere .= " and shippr_uncode  = '$_reportRef'";
                    }
                    if($_reportStationType == 'fport_of_loading_un'){
                        $sqlWhere .= " and fport_of_loading_un  = '$_reportRef'";
                    }
                    if($_reportStationType == 'consignee_uncode'){
                        $sqlWhere .= " and  consignee_uncode = '$_reportRef'";
                    }
                    if($_reportStationType == 'mport_of_discharge_un'){
                        $sqlWhere .= " and  mport_of_discharge_un = '$_reportRef'";
                    }
                    
                } else {
                    $sqlWhere .= " and 1<>1";
                }
            } else if ($_reportType == "co2e") {
                $_reportDataType = $_REQUEST["_reportDataType"];
                //查询sea 其他的为空
                if (!empty($_reportRef) && $_reportDataType == "sea") {
                    $_reportStationType = $_REQUEST["_reportStationType"];
                    if($_reportStationType == 'origin'){
                        $sqlWhere .= " and  shippr_uncode = '$_reportRef'";
                    }else{
                        $sqlWhere .= " and  consignee_uncode = '$_reportRef'";
                    }
                    
                } else {
                    $sqlWhere .= " and 1<>1";
                }
            } else {
                $_reportRef = explode("-", $_REQUEST["_reportRef"]);
                $_reportRefb_date = $_REQUEST["_reportRefb_date"];
                $bdate = common::excuteOneSql("select to_date('$_reportRefb_date','mm/YYYY')");
                $_reportRefe_date = $_REQUEST["_reportRefe_date"];
                $edate = common::excuteOneSql("select to_date('$_reportRefe_date','mm/YYYY') + interval '1 month' - interval '1 day'");
                //$sqlWhere .= " and eta>='$bdate' and eta<='$edate'";
                if ($_REQUEST["_reportRef"] == "Over 80 Days") {
                    $sqlWhere .= " and (eta-etd)>80";
                } else {
                    $sqlWhere .= " and (eta-etd)>" . $_reportRef[0] . " and (eta-etd)<=" . substr($_reportRef[1], 0, 2);
                }
            }
        }
        //取消全文检索
        // if (!empty($_POST["_textSearch"])) {
        //     $sqlWhere .= " and text_search @@ (str_to_tsquery('" . common::check_input($_POST["_textSearch"]) . "'))";
        // }
        //处理reference类型的组合查询  Search booking No./HBL No./PO No./Carrier Booking No.
        if (!empty($_POST["_textSearch"])) {
            $textSearch_arr = $_POST['_textSearch'];
            if(!is_array($textSearch_arr)){
                $textSearch_arr = str_replace(",", ";", $textSearch_arr);
                $textSearch_arr = array($textSearch_arr);
            }
            $more_param = common::getInNotInSqlForSearch(strtolower(utils::implode(';',$textSearch_arr)));
            //$sqlWhere .= " and (ARRAY[$more_param] && array_append(array[lower(booking_no)::text,lower(h_bol)::text, lower(po_no),lower(carrier_booking),lower(tracking_no)],''))";
            $sqlWhere .= " and ((ARRAY[$more_param] && array_append(ARRAY[lower(booking_no::text), lower(h_bol::text), lower(m_bol), lower(carrier_booking), lower(quote_no), lower(tracking_no)]||string_to_array(lower(ctnrs),','), ''::text))
                or lower(po_no) like '%" . strtolower(common::check_input($_POST["_textSearch"])) . "%'
                or lower(invoice_no) like '%" . strtolower(common::check_input($_POST["_textSearch"])) . "%')";
        }
        //移除filterTag
        $sqlWhere_befrom_filterTag  = $sqlWhere;

        $mode_param = "";
        $transport_mode = empty($_POST["transport_mode"]) ? "all" : $_POST["transport_mode"];
        if(!is_array($transport_mode)){
            $transport_mode = array($transport_mode);
        }
        if((utils::count($transport_mode) == 1 && strtolower($transport_mode[0]) == 'all')){
            $transport_mode = array("sea","air","road");         
        }
        $transport_mode = utils::implode(";",$transport_mode);

        $mode_param = common::getInNotInSqlForSearch($transport_mode);
        $sqlWhere .= " and transport_mode in ($mode_param)";
        $transport_mode_search = " and transport_mode in ($mode_param)";
        
        //查询tag
        // IFFBCF  Booking Confirmation                      Created
        // IFFCPU  Cargo Pickup                              Cargo Received
        // IFFREC  Cargo Arrived at Origin                   Cargo Received
        // IFFDEP  Departure                                 Departed
        // IFFARR  Arrived at Final Destination Port         Arrived
        // IFFAFD  Arrived at Final Destination              Completed (remove)

        // IFFDEL  document turnover/delivered              Completed
        $filterTag_param = "";
        if (!empty($_POST["filterTag"])) {
            if (utils::count($_POST['filterTag']) == 1){
                $filterTag = $_POST['filterTag'][0];
            }else{
                $filterTag = utils::implode(",", $_POST['filterTag']);
            }

            $_sqlwhere = "1<>1";
            $filterTag_param = "1<>1";
            if(strtolower($filterTag) == "all"){
                $filterTag_param = "1=1";
            }
            if (stripos($filterTag, "Created") !== FALSE) {
                $_sqlwhere .= " or ((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)";
                $filterTag_param .= " or ((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)";
            }
            if (stripos($filterTag, "Cargo Received") !== FALSE) {
                $_sqlwhere .= " or ((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)";
                $filterTag_param .= " or ((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)";
            }
            if (stripos($filterTag, "Departure") !== FALSE) {
                $_sqlwhere .= "or (m_iffdep is not null and m_iffarr is null and m_iffdel is null)";
                $filterTag_param .= "or (m_iffdep is not null and m_iffarr is null and m_iffdel is null)";
            }
            if (stripos($filterTag, "Arrived") !== FALSE) {
                $_sqlwhere .= " or (m_iffarr is not null and m_iffdel is null)";
                $filterTag_param .= " or (m_iffarr is not null and m_iffdel is null)";
            }
            if (stripos($filterTag, "Completed") !== FALSE) {
                $_sqlwhere .= " or (m_iffdel is not null)";
                $filterTag_param .= " or (m_iffdel is not null)";
            }
            if(strtolower($filterTag) <> "all" && !empty($filterTag)){
                $sqlWhere .= " and ($_sqlwhere)";
                $filterTag_param = " ($filterTag_param)";
            }
        }
        if(empty($filterTag_param)){
            $filterTag_param = "1=1";
        }
        $tag_and_mode_param = $filterTag_param.$transport_mode_search;

        $rc = $_POST ['rc'];
        //这里都要查询，除非多传几个参数回来
        if ($rc == - 1 || true) {
            $sql = "select count(1) as rc,
                sum(case when (1<>1 or (transport_mode='sea')) then 1 else 0 end) as sea_rc, 
                sum(case when (1<>1 or (transport_mode='air')) then 1 else 0 end) as air_rc, 
                sum(case when $tag_and_mode_param then 1 else 0 end) as seach_rc, 
                sum(case when (m_iffcpu is null and m_iffrec is null and m_iffdep is null and m_iffarr is null and m_iffdel is null)
                     then 1 else 0 end) 
                as created, 
                sum(case 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 1 else 0 end) 
                as cargo_received,
                sum(case when (m_iffdep is not null and m_iffarr is null and m_iffdel is null) then 1 else 0 end) as departed,
                sum(case when (m_iffarr is not null and m_iffdel is null) then 1 else 0 end) as arrived,
                sum(case when (m_iffdel is not null) then 1 else 0 end) as completed,
                string_agg(distinct incoterms, '<$>') as incoterm_str,
                string_agg(distinct service, '<$>') as service_str 
            from public.kln_ocean". $sqlWhere_befrom_filterTag;
            error_log("online_ocean_search_All_Status: ".$sql);

            $sql_all_status_data = common::excuteObjectSql($sql);
            $incoterms = explode("<$>", $sql_all_status_data['incoterm_str']);
            $IncotermsList = array();
            foreach($incoterms as $terms){
                if(!empty($terms)){
                    $IncotermsList[] = array("name"=>$terms,"checked"=>false);
                }
            }
            $service = explode("<$>", $sql_all_status_data['service_str']);
            $ServiceList = array();
            foreach($service as $_service){
                if(!empty($_service)){
                    $ServiceList[] = array("name"=>$_service,"checked"=>false);
                }
            }
            $rc = $sql_all_status_data['rc'];
            $search_rc = $sql_all_status_data['seach_rc'];
            $sea_rc = $sql_all_status_data['sea_rc'];
            $air_rc = $sql_all_status_data['air_rc'];
            $Created = $sql_all_status_data['created'];
            $Cargo_Received = $sql_all_status_data['cargo_received'];
            $Departed = $sql_all_status_data['departed'];
            $Arrived = $sql_all_status_data['arrived'];
            $Completed = $sql_all_status_data['completed'];
            
            if (!empty($_POST["filterTag"])) {
                $tagsList = array(array("name"=>"All","number"=>intval($rc),"type"=>"all","checked"=>utils::in_array('All', $_POST["filterTag"])? true : false),
                array("name"=>"Created","number"=>intval($Created),"type"=>"created","checked"=>utils::in_array('Created', $_POST["filterTag"])? true : false),
                array("name"=>"Cargo Received","number"=>intval($Cargo_Received),"type"=>"cargo_received","checked"=>utils::in_array('Cargo Received', $_POST["filterTag"])? true : false),
                array("name"=>"Departure","number"=>intval($Departed),"type"=>"departure","checked"=>utils::in_array('Departure', $_POST["filterTag"])? true : false),
                array("name"=>"Arrived","number"=>intval($Arrived),"type"=>"arrived","checked"=>utils::in_array('Arrived', $_POST["filterTag"])? true : false),
                array("name"=>"Completed","number"=>intval($Completed),"type"=>"completed","checked"=>utils::in_array('Completed', $_POST["filterTag"])? true : false));
            }else{
                //初始，前端有选择着带入选择
                $tagsList = array(array("name"=>"All","number"=>intval($rc),"type"=>"all","checked"=> true ),
                array("name"=>"Created","number"=>intval($Created),"type"=>"created","checked"=> false),
                array("name"=>"Cargo Received","number"=>intval($Cargo_Received),"type"=>"cargo_received","checked"=> false),
                array("name"=>"Departure","number"=>intval($Departed),"type"=>"departure","checked"=> false),
                array("name"=>"Arrived","number"=>intval($Arrived),"type"=>"arrived","checked"=> false),
                array("name"=>"Completed","number"=>intval($Completed),"type"=>"completed","checked"=>false));
            }

            $TransportList = array(
                array("name"=>"Ocean Freight","sname"=>"Sea","number"=>intval($sea_rc),"checked"=>false,"icon"=>"#icon-icon_ocean_b"),
                array("name"=>"Air Freight","sname"=>"Air","number"=>intval($air_rc),"checked"=>false,"icon"=>"#icon-icon_airplane_b"));
            
            $transport_mode_arr = explode(";", $transport_mode);
            foreach($transport_mode_arr as $mode){
                foreach($TransportList as $tkey => $transport){
                    if(strtolower(trim($mode)) == strtolower($TransportList[$tkey]["sname"])){
                        $TransportList[$tkey]["checked"] = true;
                    }
                }
            }
            //现在下载交给前端，后台预先只返回全部字段的列,
            $allColumn = column::getInstance()->getDisplayColumnAllReomveDefault('Ocean_Search');
            $allBookingColumns = column::getInstance()->tableColumns('Ocean_Search',$allColumn);
        }
        $tp = ceil($rc / $ps);
        $order_by = " etd desc";
        if (_isCustomerLogin()) {
            $order_by = " eta desc";
        }
        if ($rc > 0) {
            // $ocean_ref_sql = " LEFT JOIN LATERAL ( SELECT string_agg(ref_code ||ref_value, ', ') AS other_refenrence_no
            //                 FROM public.ocean_reference temp WHERE temp.serial_no = oo.serial_no) aa ON true ";

            $sql = "with oo  as (SELECT order_from as _schemas,serial_no as __serial_no, serial_no,transport_mode,
                    h_bol as _hbol, isf_bol as _isfbol, consignee_city as _consignee_city,
                    (select user_login from kln_user_subscribed us  
                        where lower(us.user_login) = '".strtolower(_getLoginName())."' and us.subscribed_serial_no = kln_ocean.serial_no) as is_subscribe,
                    CASE
	    	            WHEN (m_iffcpu is null and m_iffrec is null and m_iffdep is null and m_iffarr is null and m_iffdel is null) THEN 'Created'::text
                        WHEN ((m_iffcpu is not null or m_iffrec is not null) and m_iffdep is null and m_iffarr is null and m_iffdel is null) THEN 'Cargo Received'::text
                        WHEN (m_iffdep is not null and m_iffarr is null and m_iffdel is null) THEN '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'
		            END AS new_status, ".
                    column::getInstance()->getSearchSqlForDisplay('Ocean_Search') . " from public.kln_ocean " . $sqlWhere . 
                " order by $order_by limit " . $ps . " offset " . ($cp - 1) * $ps.") 
            select oo.* ,
                case when oo.transport_mode ='sea' 
                        then ( SELECT string_agg(ref_code ||ref_value, ', ') AS other_refenrence_no FROM public.ocean_reference temp WHERE temp.serial_no = oo.serial_no)
                    when oo.transport_mode ='air' 
                        then ''
                    else '' 
                end as \"Other refenrence No.\"
            from oo";

            $tmp_search_without_limit = "with oo  as (SELECT order_from as _schemas,serial_no as __serial_no, serial_no,transport_mode,
                    h_bol as _hbol, isf_bol as _isfbol, consignee_city as _consignee_city,
                    (select user_login from kln_user_subscribed us  
                        where lower(us.user_login) = '".strtolower(_getLoginName())."' and us.subscribed_serial_no = kln_ocean.serial_no) as is_subscribe,
                    CASE
	    	            WHEN (m_iffcpu is null and m_iffrec is null and m_iffdep is null and m_iffarr is null and m_iffdel is null) THEN 'Created'::text
                        WHEN ((m_iffcpu is not null or m_iffrec is not null) and m_iffdep is null and m_iffarr is null and m_iffdel is null) THEN 'Cargo Received'::text
                        WHEN (m_iffdep is not null and m_iffarr is null and m_iffdel is null) THEN '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'
		            END AS new_status, ".
                    column::getInstance()->getSearchSqlForDisplay('Ocean_Search') . " from public.kln_ocean " . $sqlWhere . 
                " order by $order_by) 
           select oo.* ,
                case when oo.transport_mode ='sea' 
                        then ( SELECT string_agg(ref_code ||ref_value, ', ') AS other_refenrence_no FROM public.ocean_reference temp WHERE temp.serial_no = oo.serial_no)
                    when oo.transport_mode ='air' 
                        then ''
                    else '' 
                end as \"Other refenrence No.\"
            from oo";
            
            $rs = common::excuteListSql($sql);
            error_log("online_ocean_search_SQL: ".$sql);
            
            //对查询的结果做特殊处理，比如要拼接某个值，合并值等
            foreach($rs as $index => $val) {
                //返回加密serial_no
                $rs[$index]["__serial_no"] = common::deCode($val['__serial_no'], 'E');
                //$rs[$index]["mode"] = "Ocean Freight";
                $rs[$index]["Mode"] = $val['transport_mode'] == 'sea' ? "Ocean Freight" : ($val['transport_mode'] == 'air' ? "Air Freight": "");
                //status 改为ocean_milestone里的信息
                $rs[$index]["Status"] = $rs[$index]["new_status"];
                //订阅的参数
                if(empty($rs[$index]["is_subscribe"])){
                    $rs[$index]['is_subscribe'] = false;
                }else{
                    $rs[$index]['is_subscribe'] = true;
                }
                $rs[$index]["a"] = common::deCode($val['__serial_no'], 'E');
            }
        
            $arrTmp = array('searchData' => $rs, 
               'tagsList' => $tagsList,
               'TransportList' => $TransportList,
               'allColums' => $allBookingColumns,
               'IncotermsList' =>$IncotermsList,
               'ServiceList' =>$ServiceList,
               'canEdiVgm' => _canEdiVgm(),
               'rc' => $search_rc,
               'ps' => $ps,
               'cp' => $cp,
               'tp' => $tp,
               'tmp_search' => common::deCode($tmp_search_without_limit, 'E'),
               'type' => common::check_input($_POST ['_ntype']));
        } else {
            $arrTmp = array('searchData' => array(), 
                'tagsList' => $tagsList,
                'TransportList' => $TransportList,
                'IncotermsList' =>$IncotermsList,
                'ServiceList' =>$ServiceList,
                'allColums' => $allBookingColumns,
                'canEdiVgm' => _canEdiVgm(),
                'rc' => $search_rc,
                'ps' => $ps,
                'cp' => $cp,
                'tp' => $tp,
                'tmp_search' => "",
                'type' => common::check_input($_POST ['_ntype']));
        }
        common::echo_json_encode(200,$arrTmp);
        exit();
    }

    private function _ocean_detail() {
        $serial_no = common::deCode($_GET['a'], 'D');
        $sql = $this->getOceanDetail($serial_no);
        $ocean = common::excuteObjectSql($sql);
        
        if (!empty($ocean)) {
            $ocean['_isf_bol'] = $ocean['isf_bol'];
            if (empty($ocean['_isf_bol'])) {
                $ocean['_isf_bol'] = "AMAW" . $ocean['_h_bol'];
            }
            $isf_ss = md5(md5($ocean['_isf_bol']));
            $isf_ss = substr($isf_ss, 7) . substr($isf_ss, 0, 7);
            //ETA Dest的计算逻辑移除
            // most update status
            $_schemas = $_REQUEST["_schemas"];
            $mostStatusSql = "select last_status_loc, last_status_city, to_char(last_status_315_date, 'MM/DD/YYYY') as last_status_315_date,last_status_315_code "
                    . ", fport_of_loading_un, mport_of_discharge_un, place_of_receipt_un, place_of_delivery_un FROM public.kln_ocean "
                    . "where serial_no= '" . $ocean["serial_no"] . "'";
            if (!empty($_schemas)) {
                $mostStatusSql .= " and order_from='" . $_schemas . "'";
            }
            $mostStatus = common::excuteObjectSql($mostStatusSql);
            $state = -1;
            if (!empty($mostStatus["last_status_315_code"])) {
                //desc 逻辑处理取消
                $state = common::getStatusStage($mostStatus["last_status_315_code"]);
            }
            //请求地图是需要的参数
            $uncode = "'";
            if ($mostStatus["place_of_receipt_un"] != $mostStatus["fport_of_loading_un"]) {
                $uncode .= $mostStatus["place_of_receipt_un"];
            }
            $uncode .= "'|'" . $mostStatus["fport_of_loading_un"] . "'|'" . $mostStatus["mport_of_discharge_un"] . "'|'";
            if ($mostStatus["mport_of_discharge_un"] != $mostStatus["place_of_delivery_un"]) {
                $uncode .= $mostStatus["place_of_delivery_un"];
            }
            $uncode .= "'";
        }

        $vueData = $this->returnOceanDetailData($ocean,$state);

        //处理shipment data暂时写死
        $simplexData = $vueData['shipmentData'];
        //处理transportInfo信息数据
        $transportInfo = $vueData['transportInfo'];
        //处理basicInfo信息数据
        $basicInfo = $vueData['basicInfo'];
        //处理 拼接地址 ocean表单exp 字段无法精准分割电话和地址信息，只能从contacts表里查询
        $businessPartners = $vueData['businessPartners'];
        //处理routes 转船可能有多个情况
        $routes = $vueData['routes']; 
        //处理marksAndDescription
        $marksAndDescription = $vueData['marksAndDescription'];
        
        //处理containerStatusData
        $containerStatusData = $vueData['containerStatusData'];


        //处理ocean_reference
        $ref_no = array();
        $ref_arr = common::excuteListSql("select * from ocean_reference where lower(serial_no) = '" . strtolower($serial_no) . "'");
        if(!empty($ref_arr)){
            foreach($ref_arr as  $ref) {
                $ref_no[] = array("label"=>$ref["ref_code"],"value"=>$ref["ref_value"]);
            }
        }
        //处理Container ：配置Ocean_Container字段 UI 需要的字段有：Container.no Packing,quantity,Gross weight(kg),Gross weight(LB)，CBM，seal#，size.service
        $ocean_container_column = array();
        $container_column = column::getInstance()->getDisplayColumnAll('Ocean_Container');
        foreach($container_column as $cc){
            $ocean_container_column[] =array("field" =>$cc['database_column_name'],"title" =>$cc['name'],"formatter" =>"","type" =>"normal");
        }
        $sql = "SELECT " . column::getInstance()->getSearchSql('Ocean_Container') . ",net_lbs from oc_container where lower(serial_no) = '" . strtolower($serial_no) . "'";
        $rss = common::excuteListSql($sql);

        $quantity_unit = array();
        $g_weight_tolal = 0;
        $ch_weight_tolal = 0;
        $ch_weight_tolal_grs_lbs = 0;
        $cbm_tolal = 0;
        foreach ($rss as $key => $rs) {
            $unit = $rs['unit'];
            if (array_key_exists($unit, $quantity_unit)) {
                $quantity_unit[$unit] = $quantity_unit[$unit] + $rs['qty'];
            } else {
                $quantity_unit[$unit] =  $rs['qty'];
            }
            $g_weight_tolal += $rs['grs_kgs'];
            $ch_weight_tolal += $rs['net_lbs'];
            $ch_weight_tolal_grs_lbs += $rs['grs_lbs'];
            $cbm_tolal += $rs['cbm'];
            //特殊处理weight
            if(!empty($rss[$key]['grs_kgs'])){
                $rss[$key]['grs_kgs'] = $rs['grs_kgs']."KGS";
            }
            if(!empty($rss[$key]['grs_lbs'])){
                $rss[$key]['grs_lbs'] = $rs['grs_lbs']."LBS";
            }
        }
        $quantity_tolal = "";
        foreach($quantity_unit as $uk => $uv){
            // 使用substr()截取第一次出现之前的字符串
            $uk = substr($uk, 0, strpos($uk, "-"));
            $quantity_tolal.=$uv." ".$uk." ";
        }
        $ch_weight_tolal = empty($ch_weight_tolal) ? $ch_weight_tolal_grs_lbs : $ch_weight_tolal;

        $g_weight_tolal = sprintf("%.3f", $g_weight_tolal);
        $ch_weight_tolal = sprintf("%.3f", $ch_weight_tolal);
        $cbm_tolal = sprintf("%.4f", $cbm_tolal);
        //Containers信息
        $containers = array("container_column"=>$ocean_container_column,"container_data" =>$rss);
        //Packing信息
        $packing = array("Quantity/Unit"=>$quantity_tolal,"G. Weight" => $g_weight_tolal." KGS","Ch. Weight" => $ch_weight_tolal." LBS","Volume" => $cbm_tolal." CBM");
        
        //Milestones info  列名固定
        $Milestones = common::getMilestonesInfo($ocean,"sea",$_schemas,$vueData['EDI315TimeAndLocation']);

        //页面固定写死的信息
        $document_column = array();
        $document_column[] = array("title" =>"File Type","field" =>"file_type","formatter" =>"","type" =>"normal");
        $document_column[] = array("title" =>"File","field" =>"file","formatter" =>"","type" =>"normal");

        $document_data = array();
        $document = _getViewDocType($_REQUEST["_schemas"]);
        $OutFileURL = common::excuteOneSql("select item_value from config where item='OutFileURL'");

        $ocean_can_view_file = common::excuteOneSql("select ocean_can_view_file from ra_online_user where lower(user_login)='".strtolower($_SESSION['ONLINE_USER']['user_login'])."'");
        if (!empty($document)) {
            foreach ($document as $v) {
                $file_type = $v['display_name'];
                $file_arr =  $this->getOceanFileByHbol($ocean['_m_bol'], $ocean['_h_bol'], $v['serial_no'], $v['m_h'], $v['display_name'], TRUE,$ocean['_job_bol']) ;

                $tar = json_decode($ocean_can_view_file,true);

                if(!empty($tar[$v['display_name']])){
                    foreach ($tar[$v['display_name']] as $dkey => $dvalue) {
                        switch ($v['display_name']) {
                            case 'HBL':
                                $sql = "SELECT (SELECT format_name  from $_schemas.ra_online_file_format where serial_no = ra_online_doc_upload.format_serailno) as format_name,
                                        from_system,serial_no,file_path, file_name, upload_by, to_char(upload_date, 'MM/DD/YYYY') as upload_date
                                        from ra_online_doc_upload
                                        where lower(bol) = lower('".$ocean['_h_bol']."') and lower(format_serailno) in ('".strtolower(str_replace(";","','",$dvalue['value']))."')  and upper((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2]) = '".$dvalue['type']."'
                                        order by id desc";
                                break;
                            case 'MBL':
                                $sql = "SELECT (SELECT format_name  from $_schemas.ra_online_file_format where serial_no = ra_online_doc_upload.format_serailno) as format_name,
                                        from_system,serial_no,file_path, file_name, upload_by, to_char(upload_date, 'MM/DD/YYYY') as upload_date
                                        from ra_online_doc_upload
                                        where ( lower(bol) = lower('".strtolower($ocean['_m_bol'])."') or lower(bol) = lower('".strtolower($ocean['_job_bol'])."')) and lower(format_serailno) in ('".strtolower(str_replace(";","','",$dvalue['value']))."')  and upper((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2]) = '".$dvalue['type']."'
                                        order by id desc";
                                break;
                            case 'Others':
                                $sql = "SELECT (SELECT format_name  from $_schemas.ra_online_file_format where serial_no = ra_online_doc_upload.format_serailno) as format_name,
                                        from_system,serial_no,file_path, file_name, upload_by, to_char(upload_date, 'MM/DD/YYYY') as upload_date
                                        from ra_online_doc_upload
                                        where (lower(bol) = lower('".strtolower($ocean['_h_bol'])."') or lower(bol) = lower('".strtolower($ocean['_m_bol'])."') or lower(bol) = lower('".strtolower($ocean['_job_bol'])."')) and lower(format_serailno) in ('".strtolower(str_replace(";","','",$dvalue['value']))."')  and upper((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2]) = '".$dvalue['type']."'
                                        order by id desc";
                                break;
                            default:
                                $sql = "";
                                break;
                        }

                        if (!empty($sql)) {
                            $rss = common::excuteListSql($sql);
                            foreach ($rss as $rs) {
                                if ($rs['from_system']=="TOPOCEAN"||$rs['from_system']=="KSMART") {
                                    $file_arr[] = array("url"=>$OutFileURL."/download.php?_schemas=&a=".base64_encode(base64_encode($rs['serial_no'])),
                                        "file_name" => $rs['file_name'],
                                        "format_name" => $rs['format_name'],
                                        "detail"=>$rs['upload_date'],
                                        "can_delete"=>_getLoginName() == $rs['upload_by'],
                                        "from_system" =>'TOPOCEAN_KSMART');
                                }else{
                                    $file_arr[] = array("url"=>"main_new_version.php?action=ocean_order&operate=download&url=". (common::deCode($rs['file_path'] . DS . $rs['file_name'], 'E')),
                                        "file_name" => $rs['file_name'],
                                        "format_name" => $rs['format_name'],
                                        "detail"=>$rs['upload_date'],
                                        "can_delete"=>_getLoginName() == $rs['upload_by'],
                                        "from_system" =>'');
                                }
                            }
                        }
                    }

                }
                //$document_data[] = array("file_type"=>$file_type,"file"=>$file_arr);
                //按现在的逻辑，有值才会显示
                foreach($file_arr as $f){
                    $document_data[] = array("file_type"=>$f["format_name"],"file"=>$f);
                }
            }
        }
        $document_info = array("document_column"=>$document_column,"document_data" =>$document_data);

        $emailRecords = $this->getCommunicationNew($ocean["serial_no"]);
        $cc_email = common::excuteOneSql("select cc_email from public.online_ocean_communication where email_uuid='" . $ocean["serial_no"] . "' order by id desc limit 1");
        $email = $this->getEmail($ocean["serial_no"]);
        $email['cc_email'] = $cc_email;
        $email['emailRecords'] = $emailRecords;

        global $_COPYRIGHT;
        $data = array('transportInfo' => $transportInfo, 
                        'basicInfo' => $basicInfo,
                        'businessPartners' => $businessPartners,
                        'ref_no' => $ref_no,
                        'packing' => $packing,
                        'marksAndDescription' => $marksAndDescription,
                        'containers' => $containers,
                        'simplexData' =>$simplexData,
                        'containerStatusData' => $containerStatusData,
                        'document_info' => $document_info,
                        'Milestones' => $Milestones,
                        'routes' => $routes,
                        'email' => $email,
                        'ams_ss' =>  md5(md5($ocean['serial_no'])),
                        'isf_ss' => $isf_ss,
                        'transport_mode' =>"sea",
                        '_schemas' =>$_schemas,
                        'uncode' =>$uncode,
                        'serial_no' =>$serial_no,
                        'is_subscribe' =>common::checkedSubscribe($serial_no),
                        'a' =>common::deCode($serial_no, 'E'),
                        'can_upload_doc' => strtolower($_SESSION['ONLINE_USER']['can_upload_doc']) == 't' || strtolower($_SESSION['ONLINE_USER']['can_view_doc']) == 't',
                        'canEdiVgm' => _canEdiVgm(),
                        'canViewAMSLog'=>_canViewAMSLog(),
                        'canViewISFLog'=>_canViewISFLog(),
                        'copyright' =>$_COPYRIGHT);
        common::echo_json_encode(200,$data);                
        exit(); 
    }

    private function _air_ocean_detail(){
        $serial_no = common::deCode($_GET['a'], 'D');
        $sql = $this->getAirDetail($serial_no);
        $air = common::excuteObjectSql($sql);
        
        //根据air_milestone 判断 shipment status的state
        $_schemas = $_REQUEST["_schemas"];
        $air_milestone_status = common::excuteListSql("select code,
                act_date,est_date,timezone 
            from  air_milestone a 
		 		where a.serial_no='$serial_no' and code in('IFFCPU','IFFREC','IFFONB','IFFARR','IFFDEL')
            order by id");
        $state = -1;
        if (!empty($air_milestone_status)) {
            $state_arr = array();
            foreach($air_milestone_status as $ms){
                if($ms['code'] == "IFFCPU"){
                    $state_arr[] = 0;
                }
                if($ms['code'] == "IFFREC" || $ms['code'] == "IFFONB"){
                    $state_arr[] = 1;
                }
                if($ms['code'] == "IFFARR"){
                    $state_arr[] = 2;
                }
                if($ms['code'] == "IFFDEL"){
                    $state_arr[] = 3;
                }
            }
            $state = max($state_arr);
        }        
        $vueData = $this->returnAirDetailData($air,$air_milestone_status,$state);

        //处理shipment data暂时写死
        $simplexData = $vueData['shipmentData'];
        //处理transportInfo信息数据
        $transportInfo = $vueData['transportInfo'];
        //处理basicInfo信息数据
        $basicInfo = $vueData['basicInfo'];
        //处理 拼接地址 ocean表单exp 字段无法精准分割电话和地址信息，只能从contacts表里查询
        $businessPartners = $vueData['businessPartners'];
        //处理routes 转船可能有多个情况
        $routes = $vueData['routes']; 
        //处理marksAndDescription
        $marksAndDescription = $vueData['marksAndDescription'];
        
        $quantity_tolal = empty($air['qty']) ? "" : $air['qty'].$air['qty_uom'];
        $g_weight_tolal = empty($air['piece_count']) ? "" : sprintf("%.3f", $air['piece_count'])." KGS";
        $ch_weight_tolal = empty($air['piece_count']) ? "": sprintf("%.3f", $air['weight'])." KGS";
        $cbm_tolal = empty($air['cbm']) ? "" : sprintf("%.4f", $air['cbm'])." CBM";
        //Packing信息
        $packing = array("Quantity/Unit"=>$quantity_tolal,"G. Weight" => $g_weight_tolal,"Ch. Weight" => $ch_weight_tolal,"Volume" => $cbm_tolal);
        
        //Milestones info  列名固定
        $Milestones = common::getMilestonesInfo($air,"air",$_schemas);

        //页面固定写死的信息
        $document_column = array();
        $document_column[] = array("title" =>"File Type","field" =>"file_type","formatter" =>"","type" =>"normal");
        $document_column[] = array("title" =>"File","field" =>"file","formatter" =>"","type" =>"normal");

        $document_data = array();

        $document = _getAirViewDocType($_REQUEST["_schemas"]);
        $air_can_view_file = common::excuteOneSql("select air_can_view_file from ra_online_user where lower(user_login)='".strtolower($_SESSION['ONLINE_USER']['user_login'])."'");
        if (!empty($document)) {
            foreach ($document as $v) {
                $file_type = $v['display_name'];
                $file_arr =  $this->getAirFileByHbol($air['_mawb'] . '-' . $air['_mawb_name'], $air['_h_bol'], $v['serial_no'], $v['m_h'], $v['display_name'], TRUE);

                $tar = json_decode($air_can_view_file,true);
                if(!empty($tar[$v['display_name']])){
                    foreach ($tar[$v['display_name']] as $dkey => $dvalue) {
                        switch ($v['display_name']) {
                            case 'HBL':
                                $sql = "SELECT (SELECT format_name  from $_schemas.air_file_format where serial_no = air_doc_upload.format_serailno) as format_name,
                                        file_path, file_name, upload_by, to_char(upload_date, 'MM/DD/YYYY') as upload_date
                                        from air_doc_upload
                                        where lower(bol) = lower('".$air['_h_bol']."') and lower(format_serailno) in ('".strtolower(str_replace(";","','",$dvalue['value']))."')  and upper((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2]) = '".$dvalue['type']."'
                                        order by id desc";
                                break;
                            case 'MBL':
                                $sql = "SELECT (SELECT format_name  from $_schemas.air_file_format where serial_no = air_doc_upload.format_serailno) as format_name,
                                        file_path, file_name, upload_by, to_char(upload_date, 'MM/DD/YYYY') as upload_date
                                        from air_doc_upload
                                        where lower(bol) = lower('".strtolower($air['_mawb'] . '-' . $air['_mawb_name'])."') and lower(format_serailno) in ('".strtolower(str_replace(";","','",$dvalue['value']))."')  and upper((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2]) = '".$dvalue['type']."'
                                        order by id desc";
                                break;
                            case 'Others':
                                $sql = "SELECT 
                                        (SELECT format_name  from $_schemas.air_file_format where serial_no = air_doc_upload.format_serailno) as format_name,
                                        file_path, file_name, upload_by, to_char(upload_date, 'MM/DD/YYYY') as upload_date
                                        from air_doc_upload
                                        where (lower(bol) = lower('".strtolower($air['_h_bol'])."') or lower(bol) = lower('".strtolower($air['_mawb'] . '-' . $air['_mawb_name'])."')) and lower(format_serailno) in ('".strtolower(str_replace(";","','",$dvalue['value']))."')  and upper((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2]) = '".$dvalue['type']."'
                                        order by id desc";
                                break;
                            default:
                                $sql = "";
                                break;
                        }
                        if (!empty($sql)) {
                            $rss = common::excuteListSql($sql);
                            foreach ($rss as $rs) {
                                $file_arr[] = array("url"=>"main_new_version.php?action=ocean_order&operate=download&url=". (common::deCode($rs['file_path'] . DS . $rs['file_name'], 'E')),
                                        "file_name" => $rs['file_name'],
                                        "detail"=>$rs['upload_date'],
                                        "can_delete"=>_getLoginName() == $rs['upload_by'],
                                        "from_system" =>'');
                            }
                        }
                    }
                }
                //$document_data[] = array("file_type"=>$file_type,"file"=>$file_arr);
                //按现在的逻辑，有值才会显示
                foreach($file_arr as $f){
                    $document_data[] = array("file_type"=>$f["format_name"],"file"=>$f);
                }
            }
        }
        $document_info = array("document_column"=>$document_column,"document_data" =>$document_data);

        $emailRecords = $this->getCommunicationNew($air["serial_no"]);
        $cc_email = common::excuteOneSql("select cc_email from public.online_ocean_communication where email_uuid='" . $air["serial_no"] . "' order by id desc limit 1");
        $email = $this->getEmail($air["serial_no"]);
        $email['cc_email'] = $cc_email;
        $email['emailRecords'] = $emailRecords;

        global $_COPYRIGHT;
        $data = array('transportInfo' => $transportInfo, 
                        'basicInfo' => $basicInfo,
                        'businessPartners' => $businessPartners,
                        'ref_no' => array(),
                        'packing' => $packing,
                        'marksAndDescription' => $marksAndDescription,
                        'containers' => array(),
                        'simplexData' =>$simplexData,
                        'containerStatusData' => array(),
                        'document_info' => $document_info,
                        'containers' => array(),
                        'Milestones' => $Milestones,
                        'routes' => $routes,
                        'email' => $email,
                        'ams_ss' =>"",
                        'isf_ss' => "",
                        '_schemas' =>$_schemas,
                        'uncode' =>"",
                        'transport_mode' =>"air",
                        'serial_no' =>$serial_no,
                        'is_subscribe' =>common::checkedSubscribe($serial_no),
                        'a' =>common::deCode($serial_no, 'E'),
                        'can_upload_doc' => strtolower($_SESSION['ONLINE_USER']['can_upload_doc']) == 't' || strtolower($_SESSION['ONLINE_USER']['can_view_doc']) == 't',
                        'canEdiVgm' => _canEdiVgm(),
                        'canViewAMSLog'=>_canViewAMSLog(),
                        'canViewISFLog'=>_canViewISFLog(),
                        'copyright' =>$_COPYRIGHT);
        common::echo_json_encode(200,$data);                
        exit(); 
    }

    private function _ocean_excel() {
        $sql = common::deCode($_POST ['tmp_search'], 'D');
        //$sql = substr($sql, 0, strripos($sql, " limit"));
        if(!empty($sql)){
            $rs = common::excuteListSql($sql);
        }
        //去除null
        foreach($rs as $index => $val) {
            foreach($val as $index_2 => $_val) {
                if(empty($rs[$index][$index_2]) || $rs[$index][$index_2] == null){
                    $rs[$index][$index_2] = "";
                }
            }
        }
        //对查询的结果做特殊处理，比如要拼接某个值，合并值等
        foreach($rs as $index => $val) {
            //返回加密serial_no
            $rs[$index]["__serial_no"] = common::deCode($val['__serial_no'], 'E');
            //$rs[$index]["mode"] = "Ocean Freight";
            $rs[$index]["Mode"] = $val['transport_mode'] == 'sea' ? "Ocean Freight" : ($val['transport_mode'] == 'air' ? "Air Freight": "");
            //status 改为ocean_milestone里的信息
            $rs[$index]["Status"] = $rs[$index]["new_status"];
        }
        common::echo_json_encode(200,array("msg"=>"success","Data" => $rs));
        exit;
    }

    private function _revenue_download(){
        $r2_cloumn = array();
        $r2_cloumn[] = array("title" => "Month","dataIndex"=>"month");
        $r2_cloumn[] = array("title" => "Currency","dataIndex"=>"currency");
        $r2_cloumn[] = array("title" => "Total Amount","dataIndex"=>"invoice_amount");

        $r3_cloumn = array();
        $r3_cloumn[] = array("title" => "Invoice Issue Date","dataIndex"=>"invoice_date");
        $r3_cloumn[] = array("title" => "Invoice Number","dataIndex"=>"invoice_no");
        $r3_cloumn[] = array("title" => "HBL Number","dataIndex"=>"h_bol");
        $r3_cloumn[] = array("title" => "Amount","dataIndex"=>"ivnoice_amount");
        $r3_cloumn[] = array("title" => "Currency","dataIndex"=>"currency");

        $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;";
        //$sql = "select * from _test_data_table";
        //$r2_data = common::excuteListSql($sql);
        //只取r3  代码去计总r2 ，避免二次请求数据库
        $sql = "SELECT * FROM get_customer_revenue_report('$param');FETCH ALL FROM r3;";
        //$sql = "select * from _test_data_table_two";
        $r3_data = common::excuteListSql($sql);

        $group_data = array();
        foreach($r3_data as $r3){
            $r3['invoice_date'] = strtoupper(date('M,Y', strtotime($r3['invoice_date'])));
            $group_data[] = $r3;
        }
        //按货币和日期分组
        $groupedItems = array();
        foreach ($group_data as $item) {
            $key = $item['currency']."_".$item['invoice_date'];
            if(array_key_exists($key, $groupedItems)){
                $temp = $groupedItems[$key];
                $temp['invoice_amount'] = $temp['invoice_amount'] + $item['ivnoice_amount'];
                $groupedItems[$key] = $temp;
            }else{
                $groupedItems[$key] = array("month"=>$item['invoice_date'],"currency"=>$item['currency'],"invoice_amount"=>$item['ivnoice_amount']);
            }
        }
        $r2_data = array();
        foreach($groupedItems as $gt){
            $gt['invoice_amount'] = number_format($gt['invoice_amount'], 4, '.', '');
            $r2_data[] = $gt;
        }
        common::echo_json_encode(200,array("msg"=>"success","r2" => $r2_data,"r3"=>$r3_data,"r2_cloumn" => $r2_cloumn,"r3_cloumn" => $r3_cloumn,
           "r2_title"=>"Monthly Summary Data","r3_title"=>"Invoice Detailed Data"));
        exit;
    }
    
    private function _ams_isf_log(){
        global $cbpdb;
        $ams_ss = $_POST['ams_ss'];
        $isf_ss = $_POST['isf_ss'];

        $amsLog = array();
        //页面固定写死的信息
        $amsLog_column = array();
        $amsLog_column[] = array("title" =>"Date Time","field" =>"atime","formatter" =>"","type" =>"dateTime");
        $amsLog_column[] = array("title" =>"Code","field" =>"cbperrorcode","formatter" =>"","type" =>"normal");
        $amsLog_column[] = array("title" =>"Name","field" =>"isams_submit","formatter" =>"","type" =>"normal");
        $amsLog_column[] = array("title" =>"Description","field" =>"amslog","formatter" =>"","type" =>"normal");
        $amsLog["amsLog_column"] = $amsLog_column;
        $amsLog["msg"] = "Origin have not filed ACE-M1, no data found";
        $amsLog["data"] = array();
        if (_canViewAMSLog()) {
            $s = $ams_ss;
            //$s = "PDLA200127H";
            if (!empty($s)) {
                //$cc = $cbpdb->GetAll("select distinct h_bol from ams_information where md5(md5(from_serial_no)) = '" . pg_escape_string($s) . "'");
                $cc = $cbpdb->GetAll("select distinct h_bol from ams_information where h_bol = '" . pg_escape_string($s) . "'");
                foreach ($cc as $v){
                    $amses[] = $cbpdb->GetAll("select * from ams_info where h_bol = '" . pg_escape_string($v['h_bol']) . "'");
                }
                //考虑一个情况
                if (utils::count($amses) > 0) {
                    $ams = $amses[0];
                    $carrier_hbol = substr($ams[0]['m_bol'], 4);
                    $carrier_scac = substr($ams[0]['m_bol'], 0, 4);
                    if (empty($ams[0]['h_bol'])){
                        $amsLog["msg"] = "Origin have not filed ACE-M1, no data found";
                        $amsLog["data"] = array();
                    }else{
                        $log = $cbpdb->GetAll("select cbpreplytime as atime, amslog, cbperrorcode, isams_submit 
                            from v_e_amslog where hbol = '" . pg_escape_string($ams[0]['h_bol']) . "' 
                            and split_part(stationname, ',', 1) = split_part('" . pg_escape_string($ams[0]['station_name']) . "', ',', 1) 
                            order by cbpreplytime desc, substr(cbperrorcode, 2, 1) desc");

                        $amsLog["msg"] = "";
                        $amsLog["data"] = $log;
                    }
                }
            }
        }

        $isfLog = array();
        //页面固定写死的信息
        $isfLog_column = array();
        $isfLog_column[] = array("title" =>"Date Time","field" =>"atime","formatter" =>"","type" =>"dateTime");
        $isfLog_column[] = array("title" =>"Code","field" =>"cbperrorcode","formatter" =>"","type" =>"normal");
        $isfLog_column[] = array("title" =>"Name","field" =>"isfsubmit","formatter" =>"","type" =>"normal");
        $isfLog_column[] = array("title" =>"Description","field" =>"isflog","formatter" =>"","type" =>"normal");
        $isfLog["isfLog_column"] = $isfLog_column;
        $isfLog["msg"] = "Origin have not filed ISF, no data found";
        $isfLog["data"] = array();
        if (_canViewISFLog()) {
            if (!empty($isf_ss)) {
                $ss = $isf_ss;
                $len = strlen($ss);
                $b = substr($ss, $len - 7) . substr($ss, 0, $len - 7);

                if (empty($b)){
				    $isfLog["msg"] = "Origin have not filed ISF, no data found";
                    $isfLog["data"] = array();
                }else{
			        $isf = $cbpdb->GetRow("select * from ocean_isf where md5(md5(bol)) = '" . pg_escape_string($b) . "' or md5(md5(substr(bol, 5))) = '" . pg_escape_string($b) . "' order by id desc limit 1");
                }

                if (!empty($isf)) {
                    if (!empty($isf['dob'])){
                        $isf['dob'] = date("m/d/Y", strtotime($isf['dob']));
                    }       
                    $msg = $cbpdb->GetAll("select cbpreplytime as atime, isflog, cbperrorcode, isfsubmit, hbol 
                            from v_e_isflog where (case when coalesce(isf_no, '')<>'' 
                            then isf_no = '" . pg_escape_string($isf["isf_no"]) . "' else hbol = '" . pg_escape_string($isf["bol"]) . "'  
                            and stationname = '" . pg_escape_string($isf["station_name"]) . "' end) 
                            order by cbpreplytime desc, rowid desc");
                    $isfLog["msg"] = "";
                    $isfLog["data"] = $msg;
                }else{
                    $isfLog["msg"] = "Origin have not filed ISF, no data found";
                    $isfLog["data"] = array();
                }
                    
            }
        }

        $data =array("msg"=>"success",
            "canViewAMSLog" => _canViewAMSLog(),
            "canViewISFLog" => _canViewISFLog(),
            "amsLog" =>$amsLog, 
            "isfLog" =>$isfLog);
        common::echo_json_encode(200,$data);
        exit;
    }
    

    private function getEmail($serial_no) {
        return utils::getEmail($serial_no);
    }

    private function getCommunicationNew($serial_no) {
        $list = common::excuteListSql("select to_char(add_time, 'MM/dd/yyyy hh24:MI:ss') as add_times, * from public.online_ocean_communication where email_uuid='$serial_no' and refer_id = 0  order by id");
        $emialRecords =array();
        foreach ($list as $k => $v) {
            $msg =array();
            $msg["name"] = $v["add_by"];
            $msg["creatTime"] = $v["add_times"];
            $msg["content"] =  urldecode($v["web_content"]);
            $emialRecords[] = $msg;
        }
        return $emialRecords;
    }

    /*
     * get file by hbol/destination
     * H：HBL
     * M: MBL
     * F: FL
     * I: C/I & Packing list
     */
    private function getOceanFileByHbol($m_bol, $h_bol, $types, $m_bs, $display, $detail = FALSE,$job_no="") {
        $_schemas = $_REQUEST['_schemas'];
        $types = strtolower($types);
        $types = explode(";", $types);
        $m_bs = explode(";", $m_bs);
        $otheres = NULL;

        $filefilter = " ('TELEX') ";//过滤掉类型，单独配置 3537
        $OutFileURL = common::excuteOneSql("select item_value from config where item='OutFileURL'");
        $file = array();
        foreach ($types as $k => $type) {
            $m_b = $m_bs[$k];
            if (strtolower($m_b) == 'm')
                $sql = "SELECT (SELECT format_name  from $_schemas.ra_online_file_format where serial_no = ra_online_doc_upload.format_serailno) as format_name,
                    from_system,serial_no,file_path, file_name, upload_by, to_char(upload_date, 'MM/DD/YYYY') as upload_date from ra_online_doc_upload where (lower(bol) = '" . strtolower($m_bol) . "' or lower(bol) ='".strtolower($job_no)."' ) and lower(format_serailno) = '$type' and upper(coalesce((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2],'')) not in ".$filefilter." order by id desc";
            if (strtolower($m_b) == 'h')
                $sql = "SELECT (SELECT format_name  from $_schemas.ra_online_file_format where serial_no = ra_online_doc_upload.format_serailno) as format_name,
                    from_system,serial_no,file_path, file_name, upload_by, to_char(upload_date, 'MM/DD/YYYY') as upload_date from ra_online_doc_upload where lower(bol) =  '" . strtolower($h_bol) . "' and lower(format_serailno) = '$type' and upper(coalesce((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2],'')) not in ".$filefilter." order by id desc";
            if (strtolower($m_b) == 'a')
                $sql = "SELECT (SELECT format_name  from $_schemas.ra_online_file_format where serial_no = ra_online_doc_upload.format_serailno) as format_name,
                    from_system,serial_no,file_path, file_name, upload_by, to_char(upload_date, 'MM/DD/YYYY') as upload_date from ra_online_doc_upload where (lower(bol) = '" . strtolower($m_bol) . "' or lower(bol) = '" . strtolower($h_bol) . "' or lower(bol) ='".strtolower($job_no)."') and lower(format_serailno) = '$type' and upper(coalesce((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2],'')) not in ".$filefilter." order by id desc";
            if (!empty($sql)) {
                $rss = common::excuteListSql($sql);
                foreach ($rss as $rs) {
                    if ($rs['from_system']=="TOPOCEAN"||$rs['from_system']=="KSMART") {
                        $file[] = array("url"=>$OutFileURL.'/download.php?_schemas=&a='.base64_encode(base64_encode($rs['serial_no'])),
                            "file_name" => $rs['file_name'],
                            "format_name" => $rs['format_name'],
                            "detail"=>$rs['upload_date'],
                            "can_delete"=>_getLoginName() == $rs['upload_by'],
                            "from_system" =>'TOPOCEAN_KSMART');
                    }else {
                        $file[] = array("url"=>"main_new_version.php?action=ocean_order&operate=download&url=". (common::deCode($rs['file_path'] . DS . $rs['file_name'], 'E')),
                        "file_name" => $rs['file_name'],
                        "format_name" => $rs['format_name'],
                        "detail"=>$rs['upload_date'],
                        "can_delete"=>_getLoginName() == $rs['upload_by'],
                        "from_system" =>'');
                    }
                }
            }
        }
        return $file;
    }

    /*
     * get file by hbol/destination
     * H：HBL
     * M: MBL
     * F: FL
     * I: C/I & Packing list
     */
    private function getAirFileByHbol($m_bol, $h_bol, $types, $m_bs, $display, $detail = FALSE) {
        $_schemas = $_REQUEST['_schemas'];
        $types = explode(";", $types);
        $m_bs = explode(";", $m_bs);
        $filefilter = " ('TELEX') ";//过滤掉类型，单独配置 3537

        $file = array();
        foreach ($types as $k => $type) {
            $m_b = $m_bs[$k];
            if (strtolower($m_b) == 'm')
                $sql = "SELECT (SELECT format_name  from $_schemas.air_file_format where serial_no = air_doc_upload.format_serailno) as format_name,
                    file_path, file_name, upload_by, to_char(upload_date, 'MM/DD/YYYY') as upload_date from air_doc_upload where lower(bol) = '" . strtolower($m_bol) . "' and lower(format_serailno) " . common::getInNotInSql($type) . " and upper(coalesce((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2],''))  not in ".$filefilter." order by id desc";
            if (strtolower($m_b) == 'h')
                $sql = "SELECT (SELECT format_name  from $_schemas.air_file_format where serial_no = air_doc_upload.format_serailno) as format_name,
                    file_path, file_name, upload_by, to_char(upload_date, 'MM/DD/YYYY') as upload_date from air_doc_upload where lower(bol) =  '" . strtolower($h_bol) . "' and lower(format_serailno) " . common::getInNotInSql($type) . " and upper(coalesce((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2],'')) not in ".$filefilter." order by id desc";
            if (strtolower($m_b) == 'a')
                $sql = "SELECT (SELECT format_name  from $_schemas.air_file_format where serial_no = air_doc_upload.format_serailno) as format_name,
                    file_path, file_name, upload_by, to_char(upload_date, 'MM/DD/YYYY') as upload_date from air_doc_upload where (lower(bol) = '" . strtolower($m_bol) . "' or lower(bol) = '" . strtolower($h_bol) . "') and lower(format_serailno) " . common::getInNotInSql($type) . "and upper(coalesce((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2],'')) not in ".$filefilter." order by id desc";
            if (!empty($sql)) {
                $rss = common::excuteListSql($sql);
                foreach ($rss as $rs) {
                    $file[] = array("url"=>"main_new_version.php?action=ocean_order&operate=download&url=". (common::deCode($rs['file_path'] . DS . $rs['file_name'], 'E')),
                        "file_name" => $rs['file_name'],
                        "format_name" => $rs['format_name'],
                        "detail"=>$rs['upload_date'],
                        "can_delete"=>_getLoginName() == $rs['upload_by'],
                        "from_system" =>'');
                }
            }
        }
        return $file;
    }

    /*
     * download file
    */
    private function _download() {
        $url = common::deCode($_GET['url'], 'D');
        common::download_file($url);
    }
    
    private function save_vgm() {
        $schemas = $_POST["schemas"];
        $serial_no = common::deCode($_POST["serial_no"], 'D');
        $ocean = common::excuteObjectSql("select m_bol, master_base_number, from_station, h_bol from $schemas.ocean where serial_no='" . common::check_input($serial_no) . "'");
        $contact = common::excuteObjectSql("select company, city, state, zipcode, phone_1, fax, email_1, contactname_1, address_1, address_2, address_3, address_4 from ocean.contacts where contact_id='" . $ocean["from_station"] . "'");
        $address = $contact["address_1"];
        if (!empty($address)) {
            $address .= "\r\n";
        }
        $address .= $contact["address_2"];
        if (!empty($address)) {
            $address .= "\r\n";
        }
        $address .= $contact["address_3"];
        if (!empty($address)) {
            $address .= "\r\n";
        }
        $address .= $contact["address_4"];

        $exists = common::excuteOneSql("select serial_no from $schemas.ocean_vgm_masterbasenumber where master_base_number ilike '" . $ocean["master_base_number"] . "'");

        global $db;
        $db->StartTrans();
        if (empty($_POST["is_send"])) {
            $_POST["is_send"] = 'f';
        }
        if (empty($exists)) {
            $db->Execute("insert into ocean_vgm_masterbasenumber (master_base_number, serial_no) values ('" . $ocean["master_base_number"] . "', '$serial_no');");
            $value["serial_no"] = $serial_no;
            $value["carrier_booking_no"] = $_POST["all_carrier_booking"];
            $value["modify_user"] = _getLoginName();
            $value["modify_time"] = "now()";
            $value["authorized_email"] = !empty($_POST["authorized_email"]) ? $_POST["authorized_email"] : "";
            $value["authorized_tel"] = !empty($_POST["authorized_tel"]) ? $_POST["authorized_tel"] : "";
            $value["authorized_company"] = !empty($_POST["submitter"]) ? $_POST["submitter"] : "";
            $value["signature"] = !empty($_POST["signature"]) ? $_POST["signature"] : "";
            $value["is_send"] = $_POST["is_send"];
            $value["action"] = "New";
            $value["type"] = "Shipper";

            $value["m_bol"] = $ocean["m_bol"];
            $value["master_base_number"] = $ocean["master_base_number"];
            $value["shipper_id"] = $ocean["from_station"];
            $value["shipper"] = $contact["company"];
            $value["shipper_address"] = $address;
            $value["shipper_contactname"] = $contact["contactname_1"];
            $value["shipper_email"] = $contact["email_1"];
            $value["shipper_fax"] = $contact["fax"];
            $value["shipper_telephone"] = $contact["phone_1"];
            $value["update_uuid"] = utils::uuid();
            $value["h_bol"] = $ocean["h_bol"];
            $value["from_station"] = $ocean["from_station"];

            $sql = common::getInsertSqlNull("$schemas .ocean_vgm", $value);
            $db->Execute($sql);
        } else {
            $serial_no = $exists;
            $db->Execute("update ocean_vgm_masterbasenumber set id=id where master_base_number ilike '" . $ocean["master_base_number"] . "';");

            $db->Execute("update $schemas.ocean_vgm set is_send='" . $_POST["is_send"] . "', carrier_booking_no='" . $_POST["all_carrier_booking"] . "', modify_user='" . _getLoginName() . "', modify_time=now(),"
                    . "authorized_email='" . $_POST["authorized_email"] . "', authorized_tel='" . $_POST["authorized_tel"] . "', authorized_company='" . $_POST["submitter"] . "', signature='" . $_POST["signature"] . "', send_time=null "
                    . ", update_uuid='" . utils::uuid() . "' where master_base_number ilike '" . $ocean["master_base_number"] . "';");
        }
        $db->Execute("delete from ocean_vgm_container where serial_no ilike '" . $serial_no . "';");

        foreach ($_POST["container_no"] as $i => $cv) {
            $value1["serial_no"] = $serial_no;
            $value1["carrier_booking_no"] = !empty($_POST["carrier_booking_no"][$i]) ? $_POST["carrier_booking_no"][$i] : "";
            $value1["ctnr"] = !empty($_POST["container_no"][$i]) ? $_POST["container_no"][$i] : "";
            $value1["size"] = !empty($_POST["size"][$i]) ? $_POST["size"][$i] : "";
            $value1["vgm_weight"] = !empty($_POST["vgm_weight"][$i]) ? $_POST["vgm_weight"][$i] : "";
            $value1["unit"] = !empty($_POST["vgm_kg_lg"][$i]) ? $_POST["vgm_kg_lg"][$i] : "";
            $value1["vgm_time"] = !empty($_POST["vgm_date"][$i]) ? $_POST["vgm_date"][$i] : "";
            $value1["cargo_weight_kgs"] = !empty($_POST["cargo_weight_kg"][$i]) ? $_POST["cargo_weight_kg"][$i] : "";
            $value1["cargo_weight_lbs"] = !empty($_POST["cargo_weight_lb"][$i]) ? $_POST["cargo_weight_lb"][$i] : "";
            $value1["vgm_method"] = !empty($_POST["vgm_method"][$i]) ? $_POST["vgm_method"][$i] : "";
            $value1["master_base_number"] = $ocean["master_base_number"];

            $sql = common::getInsertSqlNull("$schemas.ocean_vgm_container", $value1);
            $db->Execute($sql);
        }

        if ($db->CompleteTrans() === FALSE) {
            return "error";
        } else {
            return "success";
        }
    }

    private function getOceanDetail($serial_no) {
        $order_from = $_REQUEST['_schemas'];
        $_schemas = $_REQUEST['_schemas'];
        if($_schemas == 'public'){
            $_schemas = "ocean";
        }
        $sql = "with o as(
            SELECT  o.* from ocean o where serial_no = '" . $serial_no . "'
        )
        SELECT m_eta as _m_eta,  h_bol as _h_bol, m_bol as _m_bol,job_no as _job_bol,
                o.* ,sh.*, cn.* ,aa.*,dd.*,fd.*,oo.*,
                (select uncode from $order_from.ports where  code = o.port_of_transshipment) as port_of_transshipment_un
        from o 
        LEFT JOIN LATERAL ( SELECT tracking_no as _tracking_no,shippr_uncode,shipper_city,
                    consignee_uncode,consignee_city,incoterms,
                    fport_of_loading_un,
                    mport_of_discharge_un,
                    place_of_receipt_un,
                    place_of_delivery_un,
                    (select time_zone from public.city_timezone where uncode = oo.fport_of_loading_un limit 1) as pol_timezone,
                    (select uncity from $order_from.ports where uncode = oo.fport_of_loading_un limit 1) as pol_uncity,
                    (select time_zone from public.city_timezone where uncode = oo.mport_of_discharge_un limit 1) as mpod_timezone,
                    (select uncity from $order_from.ports where uncode = oo.mport_of_discharge_un limit 1) as mpod_uncity,
                    (select time_zone from public.city_timezone where uncode = oo.place_of_receipt_un limit 1) as por_timezone,
                    (select uncity from $order_from.ports where uncode = oo.place_of_receipt_un limit 1) as por_uncity,
                    (select time_zone from public.city_timezone where uncode = oo.place_of_delivery_un limit 1) as pod_timezone,
                    (select uncity from $order_from.ports where uncode = oo.place_of_delivery_un limit 1) as pod_uncity,
                    (select time_zone from public.city_timezone where uncode = oo.final_desination_uncode limit 1) as _fd_timezone,
                    (select uncity from $order_from.ports where uncode = oo.final_desination_uncode limit 1) as _pd_uncity,
                    po_no as _po_no,
                    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::text = o.serial_no::text and order_from = '$order_from') oo ON true
            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::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::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_station::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.destination_station::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";
        //error_log($sql);
        return $sql;
    }

    private function getAirDetail($serial_no) {
        $order_from = $_REQUEST['_schemas'];
        $_schemas = $_REQUEST['_schemas'];
        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,
                    (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.fport_of_loading_un limit 1) as pol_uncity,
                    (select time_zone from public.city_timezone where uncode = oo.mport_of_discharge_un limit 1) as mpod_timezone,
                    (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.mport_of_discharge_un limit 1) as mpod_uncity,
                    (select time_zone from public.city_timezone where uncode = oo.place_of_receipt_un limit 1) as por_timezone,
                    (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.place_of_receipt_un limit 1)  as por_uncity,
                    (select time_zone from public.city_timezone where uncode = oo.place_of_delivery_un limit 1) as pod_timezone,
                    (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.place_of_delivery_un limit 1) as pod_uncity,
                    (select time_zone from public.city_timezone where uncode = oo.final_desination_uncode limit 1) as _fd_timezone,
                    (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.final_desination_uncode limit 1) 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.*,air.*
        from o 
            LEFT JOIN LATERAL ( SELECT  departure_airport_name as fport_of_loading_exp,
                destination_airport_name as mport_of_discharge_exp,
                mawb as _mawb, mawb_name as _mawb_name, hawb as _hawb
            FROM $order_from.air a WHERE a.serial_no = o.serial_no) air ON true
            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";
        //error_log($sql);
        return $sql;
    }

    private function returnOceanDetailData($ocean,$state){
        $data = array();
        //由于这些基础数据还待完善，而且现在提单样式改版也没有显示客户自身的地址数据，所以我们这边的这个取值也要麻烦调整一下：
        //1.Shipment detail顶部的Origin和Destination的取值换成Place of Receipt、Place of Delivery的UNCODE
        //2.Tracking列表页里面字段Origin和Destination先隐藏（客户地址），不做展示
        $ocean['shippr_uncode'] = $ocean['place_of_receipt_un'];
        $ocean['consignee_uncode'] = $ocean['place_of_delivery_un'];

        /* Container Status */
        //Container_Status　新版只显示几个状态， CTNR# EE Ｉ AE VD VA
        //记录所有的信息 EDI315TimeAndLocation
        $EDIDate = common::getEDI315StatusForDetail($ocean["serial_no"]);
        $data['containerStatusData'] = $EDIDate['containerStatusData'];
        $data['EDI315TimeAndLocation'] = $EDIDate['EDI315TimeAndLocation'];
        $VD = $EDIDate['VD'];
        $VA = $EDIDate['VA'];

        //处理shipment data
        $ocean_milestone_status = common::excuteListSql("select code,
                act_date,est_date,timezone 
            from  ocean_milestone a 
		 		where a.serial_no='".$ocean["serial_no"]."' and code in('IFFREC','IFFONB','IFFDEP','IFFARR','IFFDEL')
            order by id");
        $ocean_milestone_status_code = array();
        foreach($ocean_milestone_status as $oms){
            $ocean_milestone_status_code[$oms['code']] = $oms;
        }
        
        if ($state < 1){
            //未发生前
            if(!empty($ocean_milestone_status_code['IFFDEP']['est_date'])){
                //如果milestone有值，或者与milestone不等，以milestone的优先度最高
                $ocean['f_etd'] = $ocean_milestone_status_code['IFFDEP']['est_date'];
            } 
        } else{
            if(!empty($ocean_milestone_status_code['IFFDEP']['act_date'])){
                //如果milestone有值，或者与milestone不等，以milestone的优先度最高
                $ocean['atd'] = $ocean_milestone_status_code['IFFDEP']['act_date'];
            }
        }    
        //先处理一下 ATD 和 ATA,在进行后面的取值
        if(empty($ocean['atd']) && !empty($VD)){
            $ocean['atd'] = $VD;
        }
        if(empty($ocean['ata']) && !empty($VA)){
            $ocean['ata'] = $VA;
        } 

        $simplexData = array();
        //place_of_Receipt
        $place_of_Receipt_stauts =  array("index" =>1,"label" =>"Place of Receipt",
            "path" =>$ocean['place_of_receipt_exp']."/".$ocean['place_of_receipt_un'],"isArrival" =>$state >= 0 ? true : false);
        $children = array();  
        if($state >= 1 && !empty($ocean_milestone_status_code['IFFREC']['act_date'])){
            //发生后，未发生前没有
            $children[] = array("label" =>"Cargo Received","date" =>$ocean_milestone_status_code['IFFREC']['act_date']);
        }
        $place_of_Receipt_stauts["children"] = $children;
        $simplexData[] = $place_of_Receipt_stauts;

        //port_of_Loading
        $port_of_Loading_stauts = array("index" =>2,"label" =>"Port of Loading",
            "path" =>$ocean['fport_of_loading_exp']."/".$ocean['fport_of_loading_un'],"isArrival" =>$state >= 1 ? true : false);
        $children = array();  
        if ($state < 1){
            $children[] = array("label" =>"Departure(ETD)","date" =>$ocean['f_etd']);
        } else{
            if(!empty($ocean_milestone_status_code['IFFONB']['act_date'])){
                $children[] = array("label" =>"On board","date" =>$ocean_milestone_status_code['IFFONB']['act_date']);
            }
            $children[] = array("label" =>"Departure(ATD)","date" =>$ocean['atd']);
        }                 
        $port_of_Loading_stauts["children"] = $children;        
        $simplexData[] = $port_of_Loading_stauts;

        //Port of Discharge
        $port_of_Discharge_status = array("index" =>3,"label" =>"Port of Discharge",
            "path" =>$ocean['mport_of_discharge_exp']."/".$ocean['mport_of_discharge_un'],"isArrival" =>$state >= 2 ? true : false);
        $children = array(); 
        // if ($state < 2){
        //     //未发生前
        //     if(!empty($ocean_milestone_status_code['IFFARR']['est_date'])){
        //         $children[] = array("label" =>"Arrival(ETA)","date" =>$ocean_milestone_status_code['IFFARR']['est_date']);
        //     }else{
        //         //没有,取eta
        //         $children[] = array("label" =>"Arrival(ETA)","date" =>$ocean['m_eta']);
        //     }
        // } else{
        //     if(!empty($ocean_milestone_status_code['IFFARR']['act_date'])){
        //         $children[] = array("label" =>"Arrival(ATA)","date" =>$ocean_milestone_status_code['IFFARR']['act_date']);
        //     }
        // } 
        //不需要判断是否发送，根据 milestone 和m_eta 的同步关系，这里m_eta 优先度最高
        if ($state < 2){
            $children[] = array("label" =>"Arrival(ETA)","date" =>$ocean['m_eta']);
        }else{
            $children[] = array("label" =>"Arrival(ATA)","date" =>$ocean['ata']);
        }             
        $port_of_Discharge_status["children"] = $children;     
        $simplexData[] = $port_of_Discharge_status;

        //Place of Delivery
        $place_of_Delivery_status = array("index" =>4,"label" =>"Place of Delivery",
            "path" =>$ocean['place_of_delivery_exp']."/".$ocean['place_of_delivery_un'],"isArrival" =>$state == 3 ? true : false);
        $children = array(); 
        if ($state < 3){
            //未发生前
        } else{
            if(!empty($ocean_milestone_status_code['IFFDEL']['act_date'])){
                $children[] = array("label" =>"Cargo handover/Delivered","date" =>$ocean_milestone_status_code['IFFDEL']['act_date']);
            }
        }                 
        $simplexData[] = $place_of_Delivery_status;
        $data['shipmentData'] = $simplexData;

        //处理transportInfo信息数据(air改过，从KLN_Ocean主表信息 etd 和eta 的取值跟ocean的f_etd,m_eta)
        $transportInfo = array("Tracking No." =>$ocean['_tracking_no'],"status"=>$ocean['new_status'],"mode" => "Ocean Freight",
            "origin" =>$ocean['shippr_uncode'],"destination" =>$ocean['consignee_uncode'],
            "etd" =>$ocean['f_etd'],"atd" =>$ocean['atd'],
            "etd_timezone" =>$ocean['pol_timezone'],
            "atd_timezone" =>$ocean['pol_timezone'],
            "eta" =>$ocean['m_eta'],"ata" =>$ocean['ata'],
            "eta_timezone" =>$ocean['mpod_timezone'],
            "ata_timezone" =>$ocean['mpod_timezone']);
        $data['transportInfo'] = $transportInfo;

        //处理basicInfo信息数据
        $vessel = utils::outDisplayForMerge($ocean['f_vessel'],$ocean['m_vessel']);
        $voyage = utils::outDisplayForMerge($ocean['f_voyage'],$ocean['m_voyage']);
        $basicInfo = array("MAWB/MBL No." =>$ocean['m_bol'],"HAWB/HBOL" => $ocean['h_bol'],"Carrier_Booking_No" =>$ocean['booking_no'],
            "PO_NO" =>$ocean['_po_no'],"Vessel/Airline" =>$vessel,"Voyage/Filght" =>$voyage,
            "Incoterm" =>$ocean['incoterms'],"Service_Type" =>$ocean['service']);
        $data['basicInfo'] = $basicInfo;    

        //处理 拼接地址 ocean表单exp 字段无法精准分割电话和地址信息，只能从contacts表里查询
        $shipper_address = common::retStationInfo($ocean['sh_address_1'], $ocean['sh_address_2'], $ocean['sh_address_3'], $ocean['sh_address_4'], 
            $ocean['sh_city'], $ocean['sh_state'], $ocean['sh_zipcode'], $ocean['sh_country']);
        $consignee_address = common::retStationInfo($ocean['cn_address_1'], $ocean['cn_address_2'], $ocean['cn_address_3'], $ocean['cn_address_4'], 
            $ocean['cn_city'], $ocean['cn_state'], $ocean['cn_zipcode'], $ocean['cn_country']);
        $origin_address = common::retStationInfo($ocean['aa_address_1'], $ocean['aa_address_2'], $ocean['aa_address_3'], $ocean['aa_address_4'], 
            $ocean['aa_city'], $ocean['aa_state'], $ocean['aa_zipcode'], $ocean['aa_country']); 
        $destination_address = common::retStationInfo($ocean['dd_address_1'], $ocean['dd_address_2'], $ocean['dd_address_3'], $ocean['dd_address_4'], 
            $ocean['dd_city'], $ocean['dd_state'], $ocean['dd_zipcode'], $ocean['dd_country']);    

        $shipperPartners = array("company" =>$ocean['sh_company'],"address"=>$shipper_address,"phone"=>$ocean['sh_phone']);
        $consigneePartners = array("company" =>$ocean['cn_company'],"address"=>$consignee_address,"phone"=>$ocean['cn_phone']);
        $originPartners = array("company" =>$ocean['aa_company'],"address"=>$origin_address,"phone"=>$ocean['aa_phone']);
        $destinationPartners = array("company" =>$ocean['dd_company'],"address"=>$destination_address,"phone"=>$ocean['dd_phone']);
        $businessPartners = array("shipper"=>$shipperPartners,"consignee" => $consigneePartners,"origin" => $originPartners,"destination" => $destinationPartners);
        $data['businessPartners'] = $businessPartners;  
    
        //处理routes 转船可能有多个情况
        if ($ocean['is_vessel_direct'] <> "t"){
            //不是直航
            $routes = array(array("mode" =>"Ocean Freight","mode_label" =>"Sea", "origin" =>$ocean['shippr_uncode'],
                "destination" => $ocean['port_of_transshipment_un'],
                "etd" => $ocean['f_etd'],"atd" => $ocean['atd'],
                "eta" =>$ocean['f_eta'],"ata" =>$EDIDate['VA_RELAY'],
                "vessel" =>$ocean['f_vessel'],"voyageNo" =>$ocean['f_voyage']));
            $routes[] = array("mode" =>"Ocean Freight", "mode_label" =>"Sea","origin" =>$ocean['port_of_transshipment_un'],
                "destination" => $ocean['consignee_uncode'],
                "etd" => $ocean['m_etd'],"atd" => $EDIDate['VD_RELAY'],
                "eta" =>$ocean['m_eta'],"ata" =>$ocean['ata'],
                "vessel" =>$ocean['m_vessel'],"voyageNo" =>$ocean['m_voyage']);
        }else{
            $routes = array(array("mode" =>"Ocean Freight","mode_label" =>"Sea", "origin" =>$ocean['shippr_uncode'],
                "destination" => $ocean['consignee_uncode'],
                "etd" => $ocean['f_etd'],"atd" => $ocean['atd'],
                "eta" =>$ocean['m_eta'],"ata" => $ocean['ata'],
                "vessel" =>$ocean['f_vessel'],"voyageNo" =>$ocean['f_voyage']));
        }
        $data['routes'] = $routes;
        
        //处理marksAndDescription
        $notes = common::excuteListSql("select notes from station_data.ocean_notes on2 where serial_no='".$ocean["serial_no"]."' and type_code = 'AO'");
        $remark = "";
        foreach($notes as $note){
            if(!empty($note['notes'])){
                $remark.=$note['notes']."\r\n";
            }
        }
        $marksAndDescription = array("marks"=>$ocean['marks'],"description"=>$ocean['description'],"remark"=>$remark);
        $data['marksAndDescription'] = $marksAndDescription;

        return $data;    
    }

    private function returnAirDetailData($ocean,$ocean_milestone_status,$state){
        $data = array();
        //由于这些基础数据还待完善，而且现在提单样式改版也没有显示客户自身的地址数据，所以我们这边的这个取值也要麻烦调整一下：
        //1.Shipment detail顶部的Origin和Destination的取值换成Place of Receipt、Place of Delivery的UNCODE
        //2.Tracking列表页里面字段Origin和Destination先隐藏（客户地址），不做展示
        //Origin和Destination的展示，能不能Origin of Cargo有值就用Origin of Cargo，Origin of Cargo就放Departure Airport? Destination也一樣？
        $ocean['shippr_uncode'] = !empty($ocean['place_of_receipt_un'])? $ocean['place_of_receipt_un'] : $ocean['fport_of_loading_un'];
        $ocean['consignee_uncode'] = !empty($ocean['place_of_delivery_un']) ? $ocean['place_of_delivery_un'] : $ocean['mport_of_discharge_un'];

        //处理shipment data
        $ocean_milestone_status_code = array();
        foreach($ocean_milestone_status as $oms){
            $ocean_milestone_status_code[$oms['code']] = $oms;
        }
        if ($state < 1){
            //未发生前
            if(!empty($ocean_milestone_status_code['IFFONB']['est_date'])){
                //如果milestone有值，或者与milestone不等，以milestone的优先度最高
                $ocean['etd'] = $ocean_milestone_status_code['IFFONB']['est_date'];
            } 
        } else{
            if(!empty($ocean_milestone_status_code['IFFONB']['act_date'])){
                //如果milestone有值，或者与milestone不等，以milestone的优先度最高
                //ATD 取值为IFFONB milestone实际发生的时间
                $ocean['atd'] = $ocean_milestone_status_code['IFFONB']['act_date'];
            }
        }

        $simplexData = array();
        //place_of_Receipt
        $place_of_Receipt_stauts =  array("index" =>1,"label" =>"Place of Receipt",
            "path" =>$ocean['place_of_receipt_exp']."/".$ocean['place_of_receipt_un'],"isArrival" =>$state >= 0 ? true : false);
        $children = array();  
        if($state >= 1 && !empty($ocean_milestone_status_code['IFFCPU']['act_date'])){
            //发生后，未发生前没有
            $children[] = array("label" =>"Cargo Received","date" =>$ocean_milestone_status_code['IFFCPU']['act_date']);
        }
        $place_of_Receipt_stauts["children"] = $children;
        $simplexData[] = $place_of_Receipt_stauts;

        //port_of_Loading
        $port_of_Loading_stauts = array("index" =>2,"label" =>"Port of Loading",
            "path" =>$ocean['fport_of_loading_exp']."/".$ocean['fport_of_loading_un'],"isArrival" =>$state >= 1 ? true : false);
        $children = array();  
        if ($state < 1){
            $children[] = array("label" =>"Departure(ETD)","date" =>$ocean['etd']);
        } else{
            if(!empty($ocean_milestone_status_code['IFFREC']['act_date'])){
                $children[] = array("label" =>"Cargo Received","date" =>$ocean_milestone_status_code['IFFREC']['act_date']);
            }
            if(!empty($ocean_milestone_status_code['IFFONB']['act_date'])){
                $children[] = array("label" =>"Departure(ATD)","date" =>$ocean_milestone_status_code['IFFONB']['act_date']);
            }
        }                 
        $port_of_Loading_stauts["children"] = $children;        
        $simplexData[] = $port_of_Loading_stauts;

        //Port of Discharge
        $port_of_Discharge_status = array("index" =>3,"label" =>"Port of Discharge",
            "path" =>$ocean['mport_of_discharge_exp']."/".$ocean['mport_of_discharge_un'],"isArrival" =>$state >= 2 ? true : false);
        $children = array(); 
        if ($state < 2){
            $children[] = array("label" =>"Arrival(ETA)","date" =>$ocean['eta']);
        }else{
            if(!empty($ocean_milestone_status_code['IFFARR']['act_date'])){
                $children[] = array("label" =>"Arrival(ATA)","date" =>$ocean_milestone_status_code['IFFARR']['act_date']);
                $ocean['ata'] = $ocean_milestone_status_code['IFFARR']['act_date'];
            }else{
                //IFFARR 如果同步异常没有值时，去表里的 ata_date AS ata
                $children[] = array("label" =>"Arrival(ATA)","date" =>$ocean['ata']);
            }
        }             
        $port_of_Discharge_status["children"] = $children;     
        $simplexData[] = $port_of_Discharge_status;

        //Place of Delivery
        $place_of_Delivery_status = array("index" =>4,"label" =>"Place of Delivery",
            "path" =>$ocean['place_of_delivery_exp']."/".$ocean['place_of_delivery_un'],"isArrival" =>$state == 3 ? true : false);
        $children = array(); 
        if ($state < 3){
            //未发生前
        } else{
            if(!empty($ocean_milestone_status_code['IFFDEL']['act_date'])){
                $children[] = array("label" =>"Cargo handover/Delivered","date" =>$ocean_milestone_status_code['IFFDEL']['act_date']);
            }
        }                 
        $simplexData[] = $place_of_Delivery_status;
        $data['shipmentData'] = $simplexData;

        //处理transportInfo信息数据(air改过，从KLN_Ocean主表信息 etd 和eta 的取值跟ocean的f_etd,m_eta)
        $transportInfo = array("Tracking No." =>$ocean['tracking_no'],"status"=>$ocean['new_status'],"mode" => "Air Freight",
            "origin" =>$ocean['shippr_uncode'],"destination" =>$ocean['consignee_uncode'],
            "etd" =>$ocean['etd'],"atd" =>$ocean['atd'],
            "etd_timezone" =>$ocean['pol_timezone'],
            "atd_timezone" =>$ocean['pol_timezone'],
            "eta" =>$ocean['eta'],"ata" =>$ocean['ata'],
            "eta_timezone" =>$ocean['mpod_timezone'],
            "ata_timezone" =>$ocean['mpod_timezone']);
        $data['transportInfo'] = $transportInfo;

        //处理basicInfo信息数据
        $vessel = $ocean['vessel'];
        $voyage = $ocean['voyage'];
        $basicInfo = array("MAWB/MBL No." =>$ocean['m_bol'],"HAWB/HBOL" => $ocean['h_bol'],"Carrier_Booking_No" =>$ocean['booking_no'],
            "PO_NO" =>$ocean['po_no'],"Vessel/Airline" =>$vessel,"Voyage/Filght" =>$voyage,
            "Incoterm" =>$ocean['incoterms'],"Service_Type" =>$ocean['service']);
        $data['basicInfo'] = $basicInfo;    

        //处理 拼接地址 ocean表单exp 字段无法精准分割电话和地址信息，只能从contacts表里查询
        $shipper_address = common::retStationInfo($ocean['sh_address_1'], $ocean['sh_address_2'], $ocean['sh_address_3'], $ocean['sh_address_4'], 
            $ocean['sh_city'], $ocean['sh_state'], $ocean['sh_zipcode'], $ocean['sh_country']);
        $consignee_address = common::retStationInfo($ocean['cn_address_1'], $ocean['cn_address_2'], $ocean['cn_address_3'], $ocean['cn_address_4'], 
            $ocean['cn_city'], $ocean['cn_state'], $ocean['cn_zipcode'], $ocean['cn_country']);
        $origin_address = common::retStationInfo($ocean['aa_address_1'], $ocean['aa_address_2'], $ocean['aa_address_3'], $ocean['aa_address_4'], 
            $ocean['aa_city'], $ocean['aa_state'], $ocean['aa_zipcode'], $ocean['aa_country']); 
        $destination_address = common::retStationInfo($ocean['dd_address_1'], $ocean['dd_address_2'], $ocean['dd_address_3'], $ocean['dd_address_4'], 
            $ocean['dd_city'], $ocean['dd_state'], $ocean['dd_zipcode'], $ocean['dd_country']);    

        $shipperPartners = array("company" =>$ocean['sh_company'],"address"=>$shipper_address,"phone"=>$ocean['sh_phone']);
        $consigneePartners = array("company" =>$ocean['cn_company'],"address"=>$consignee_address,"phone"=>$ocean['cn_phone']);
        $originPartners = array("company" =>$ocean['aa_company'],"address"=>$origin_address,"phone"=>$ocean['aa_phone']);
        $destinationPartners = array("company" =>$ocean['dd_company'],"address"=>$destination_address,"phone"=>$ocean['dd_phone']);
        $businessPartners = array("shipper"=>$shipperPartners,"consignee" => $consigneePartners,"origin" => $originPartners,"destination" => $destinationPartners);
        $data['businessPartners'] = $businessPartners;  
    
        //处理routes air不存在中转的
        $routes = array(array("mode" =>"Air Freight","mode_label" =>"Air", "origin" =>$ocean['shippr_uncode'],
                "destination" => $ocean['consignee_uncode'],
                "etd" => $ocean['etd'],"atd" => $ocean['atd'],
                "eta" => $ocean['eta'],"ata" => $ocean['ata'],
                "vessel" =>$ocean['vessel'],"voyageNo" =>$ocean['voyage']));
        $data['routes'] = $routes;
        
        //处理marksAndDescription
        $notes = common::excuteListSql("select notes from station_data.air_notes on2 where serial_no='".$ocean["serial_no"]."' and type_code = 'AO'");
        $remark = "";
        foreach($notes as $note){
            if(!empty($note['notes'])){
                $remark.=$note['notes']."\r\n";
            }
        }
        $marksAndDescription = array("marks"=>$ocean['marks'],"description"=>$ocean['description'],"remark"=>$remark);
        $data['marksAndDescription'] = $marksAndDescription;
        return $data;    
    }

    private function getUploadFolder() {
        $sql = "SELECT ra_value from ra_online_config where lower(ra_name) = 'upload_document'";
        return common::excuteOneSql($sql);
    }
}
?>