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  batch document 
        */
        if ($operate == "batch_download_load") {
            $this->_batch_download_load();
        }


        /*
        * download  batch document 
        */
        if ($operate == "batch_download") {
            $data = $_POST['data'];
            $fileMap = array();
            foreach($data as $key => $_data){
                $url_param = $_data['url_param'];
                $url = $_data['url'];
                $h_bol = $_data['h_bol'];
                $label = $_data['label'];
                $standard = $_data['standard'];
                $is_topocean = $_data['is_topocean'];
                if($is_topocean[$key] == 't'){
                    //这个不会进来,现在文件全是从online 这边查询的
                    continue;
                }else{
                    $filename = common::deCode($url_param, 'D');
                    $filename = str_replace("/", DIRECTORY_SEPARATOR, $filename);
                    $filename = str_replace("\\", DIRECTORY_SEPARATOR, $filename);
                    if (!file_exists($filename)){
                        $display_name = basename($filename);
                        $data = array(
                            'msg' => $display_name.": File Not Exist."
                        );
                        common::echo_json_encode(500, $data);
                    }

                    $fileMap[] = [
                            'file_path' => $filename,
                            'h_bol' => $h_bol,
                            'format_name' => $label,
                            'standard' => $standard
                        ];
                }
            }
            common::downloadFilesAsZip($fileMap);
        }

        /*
        * 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'];
            $_bol = "";
            if ($transport_mode == "sea") {
                //m_bol,h_bol,job_no
                $count = common::excuteObjectSql("select * from ocean where lower(h_bol) = '" . strtolower($bol) . "'");
                $_bol = $count['m_bol'];
            } elseif ($transport_mode == "air") {
                //mawb,mawb_name,hawb
                $count = common::excuteObjectSql("select * from air where lower(hawb) = '" . strtolower($bol) . "'");
                $_bol = $count['mawb'] . '-' . $count['mawb_name'];
            }
            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,m_h,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']."'");
            $num = 0;
            if ($transport_mode == "sea") {
                if (strtolower($format["m_h"]) == 'm'){
                    $num = common::excuteOneSql("select count(*) from $_schemas.$_table where (lower(bol) = '" . strtolower($count['m_bol']) . "' or lower(bol) = '" . strtolower($count['job_no']) . "') and  format_serailno = '".$format['serial_no']."'");
                }
                if (strtolower($format["m_h"]) == 'h'){
                   $num = common::excuteOneSql("select count(*) from $_schemas.$_table where lower(bol) = '" . strtolower($count['h_bol']) . "' and  format_serailno = '".$format['serial_no']."'");
                }
                if (strtolower($format["m_h"]) == 'a'){
                    $num = common::excuteOneSql("select count(*) from $_schemas.$_table where(lower(bol) = '" . strtolower($count['m_bol']) . "' or lower(bol) = '" . strtolower($count['h_bol']) . "' or lower(bol) = '" . strtolower($count['job_no']) . "') and  format_serailno = '".$format['serial_no']."'");
                }
            } elseif ($transport_mode == "air") {
                if (strtolower($format["m_h"]) == 'm'){
                    $num = common::excuteOneSql("select count(*) from $_schemas.$_table where lower(bol) = '" . strtolower($count['mawb'] . '-' . $count['mawb_name']) . "' and  format_serailno = '".$format['serial_no']."'");
                }
                if (strtolower($format["m_h"]) == 'h'){
                    $num = common::excuteOneSql("select count(*) from $_schemas.$_table where lower(bol) = '" . strtolower($count['hawb']) . "' and  format_serailno = '".$format['serial_no']."'");
                }
                if (strtolower($format["m_h"]) == 'a'){
                    $num = common::excuteOneSql("select count(*) from $_schemas.$_table where ( lower(bol) = '" . strtolower($count['hawb']) . "'  OR  lower(bol) = '" . strtolower($count['mawb'] . '-' . $count['mawb_name']) . "') and  format_serailno = '".$format['serial_no']."'");
                }
            }

            try {
                $allow_extend = "|.pdf|.xlsx|.docx|.xls|.doc|";
                $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;
                    }
                    
                    //检查xlsx,docx 文档的是否异常,  doc 和xls放过
                    if($exten == 'xlsx' || $exten == 'docx'){
                        //MIME 类型校验（关键！防伪装）
                        $tmpName = $_FILES['file']['tmp_name'][$i];
                        $finfo = finfo_open(FILEINFO_MIME_TYPE);
                        $mimeType = finfo_file($finfo, $tmpName);
                        finfo_close($finfo);

                        $allowedMimes = [
                            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',     // .xlsx
                            'application/vnd.openxmlformats-officedocument.wordprocessingml.document', // .docx
                            'application/pdf'                                                       // .pdf
                        ];

                        if (!in_array($mimeType, $allowedMimes)) {
                            $errorMsg = "Invalid file format. Please upload a genuine .xlsx, .docx, or .pdf file.";
                            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文件不需要处理宏，直接保存
                    $_bol_filed =  (strtolower($format["m_h"]) == 'm') ? $_bol : $bol;
                    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_filed)) . "', '$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 {
                            error_log("Upload Filed Failed: move_uploaded_file error,".$save_path . $filename);
                            error_log("Upload Filed Info:".json_encode($_FILES));
                            $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_filed)) . "', '$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("Upload Filed Failed:".$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'];
                $rs[$rk]['cargo_weight_kg'] = $rv['grs_kgs'];
                $rs[$rk]['cargo_weight_lb'] = $rv['grs_lbs'];
                //这个是前端逻辑,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();
        }

        if ($operate == "default_vgm"){
            $vgm = array();
            $isvgm = false;
            if (_canEdiVgm()) {
                $isvgm = true;
            }
            if ($isvgm) {
                $vgm = common::excuteObjectSql("select * from ocean_vgm_setting where login_name ilike '" . _getLoginName() . "'");
            }

            $data = array("isvgm" =>$isvgm,"vgm" => $vgm);
            common::echo_json_encode(200,$data);
            exit();
        }
        
        if ($operate == "default_vgm_update"){
            $vgm = array();
            if (_canEdiVgm()) {
                $submitter = $_POST['submitter'];
                $signature = $_POST['signature'];
                $authorized_email = $_POST['authorized_email'];
                $authorized_tel = $_POST['authorized_tel'];
                $count = common::excuteOneSql("select count(*) from ocean_vgm_setting where  login_name ilike '" . _getLoginName() . "'");

                if($count > 0){
                    $rs = common::excuteUpdateSql("update ocean_vgm_setting set submitter = '" . common::check_input($submitter) . "', "
                        . "signature = '" . common::check_input($signature) . "', authorized_email = '" . common::check_input($authorized_email) . "', "
                        . "authorized_tel = '" . common::check_input($authorized_tel) . "' where login_name ilike '" . _getLoginName() . "'");
                }else{
                    $rs =common::excuteUpdateSql("INSERT INTO ocean_vgm_setting(login_name, submitter, signature, authorized_email, authorized_tel) "
                        . "VALUES ('" . _getLoginName() . "', '" . common::check_input($submitter) . "', "
                        . "'" . common::check_input($signature) . "', '" . common::check_input($authorized_email) . "', "
                        . "'" . common::check_input($authorized_tel) . "');");
                }
                $vgm["submitter"] = $submitter;
                $vgm["signature"] = $signature;
                $vgm["authorized_email"] = $authorized_email;
                $vgm["authorized_tel"] = $authorized_tel;
            }

            $data = array("msg" =>"Save Successful","vgm" => $vgm);
            common::echo_json_encode(200,$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() {
        //分担查询
        include ONLINE_ROOT . 'libs' . DS . 'map_config.ini.php';

        $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","rail");         
        }
        $transport_mode = utils::implode(";",$transport_mode);

        $mode_param = common::getInNotInSqlForSearch($transport_mode);
        $sqlWhere .= " and transport_mode_extend in ($mode_param)";
        $transport_mode_search = " and transport_mode_extend 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, "Departed") !== 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;
        $mode_param_search = "1=1 ".$transport_mode_search;

        $rc = $_POST ['rc'];
        //这里都要查询，除非多传几个参数回来
        if ($rc == - 1 || true) {
            $sql = "select count(1) as rc,
                sum(case when (1<>1 or (transport_mode_extend='sea')) then 1 else 0 end) as sea_rc, 
                sum(case when (1<>1 or (transport_mode_extend='air')) then 1 else 0 end) as air_rc, 
                sum(case when (1<>1 or (transport_mode_extend='road')) then 1 else 0 end) as road_rc, 
                sum(case when (1<>1 or (transport_mode_extend='rail')) then 1 else 0 end) as rail_rc, 
                sum(case when $tag_and_mode_param then 1 else 0 end) as seach_rc, 
                sum(case when ($mode_param_search 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 1 else 0 end) 
                as created, 
                sum(case when ($mode_param_search and (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 ($mode_param_search and 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 ($mode_param_search and m_iffarr is not null and m_iffdel is null) then 1 else 0 end) as arrived,
                sum(case when ($mode_param_search and 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);
            $sql_all_status_data =  $mapdb->GetRow($sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $sql), 0));
            $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'];
            $road_rc = $sql_all_status_data['road_rc'];
            $rail_rc = $sql_all_status_data['rail_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"=>"Departed","number"=>intval($Departed),"type"=>"departed","checked"=>utils::in_array('Departed', $_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"=>"Departed","number"=>intval($Departed),"type"=>"departed","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"),
                array("name"=>"Rail Freight","sname"=>"Rail","number"=>intval($rail_rc),"checked"=>false,"icon"=>"#icon-icon_railway_b"),
                array("name"=>"Road Freight","sname"=>"Road","number"=>intval($road_rc),"checked"=>false,"icon"=>"#icon-icon_truck_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 oe.*, order_from as _schemas,serial_no as __serial_no, serial_no,transport_mode,transport_mode_extend,
                    h_bol as _hbol, isf_bol as _isfbol, consignee_city as _consignee_city,piece_count as _piece_count,
                    f_vessel as _f_vessel, f_voyage as _f_voyage,
                    COALESCE(oe.import_po_no,po_no) as _po_no,
                    (select user_login from public.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 'Departed'::text
                        WHEN (m_iffarr is not null and m_iffdel is null) THEN 'Arrived'::text
                        WHEN (m_iffdel is not null) THEN 'Completed'::text
	    	            ELSE 'Created'
		            END AS new_status, ".
                    column::getInstance()->getSearchSqlForDisplay('Ocean_Search') . " 
                from public.kln_ocean
                    LEFT JOIN LATERAL ( SELECT loadterm,
                        manifest_type,
                        volume,
                        last_mile_delivery_date,
                        container_size,
                        import_po_no
                    FROM public.kln_ocean_extend oe WHERE oe.serial_no::text = kln_ocean.serial_no::text and oe.order_from::text = kln_ocean.order_from::text) oe ON true " 
                . $sqlWhere . " order by $order_by limit " . $ps . " offset " . ($cp - 1) * $ps.") 
            select oo.* ,
                case when oo.transport_mode ='sea'
                        then ( SELECT string_agg(DISTINCT code || value,', ' ORDER BY code || value) AS other_refenrence_no FROM public.reference_all temp 
                                    WHERE temp.code <> 'PO' 
                                            AND COALESCE(temp.value,'')<>''
                                            AND temp.value IS NOT NULL  
                                            AND LENGTH(TRIM(BOTH FROM temp.value)) > 0  
                                            AND 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 oe.*, order_from as _schemas,serial_no as __serial_no, serial_no,transport_mode,transport_mode_extend,
                    h_bol as _hbol, isf_bol as _isfbol, consignee_city as _consignee_city,piece_count as _piece_count,
                    f_vessel as _f_vessel, f_voyage as _f_voyage,
                    COALESCE(oe.import_po_no,po_no) as _po_no,
                    (select user_login from public.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 'Departed'::text
                        WHEN (m_iffarr is not null and m_iffdel is null) THEN 'Arrived'::text
                        WHEN (m_iffdel is not null) THEN 'Completed'::text
	    	            ELSE 'Created'
		            END AS new_status, ".
                    column::getInstance()->getSearchSqlForDisplay('Ocean_Search') . " 
                from public.kln_ocean
                    LEFT JOIN LATERAL ( SELECT loadterm,
                        manifest_type,
                        volume,
                        last_mile_delivery_date,
                        container_size,
                        import_po_no
                    FROM public.kln_ocean_extend oe WHERE oe.serial_no::text = kln_ocean.serial_no::text and oe.order_from::text = kln_ocean.order_from::text) oe ON true " 
                . $sqlWhere . " order by $order_by) 
           select oo.* ,
                 case when oo.transport_mode ='sea'
                        then ( SELECT string_agg(DISTINCT code || value,', ' ORDER BY code || value) AS other_refenrence_no FROM public.reference_all temp 
                                    WHERE temp.code <> 'PO' 
                                            AND COALESCE(temp.value,'')<>''
                                            AND temp.value IS NOT NULL  
                                            AND LENGTH(TRIM(BOTH FROM temp.value)) > 0  
                                            AND temp.serial_no = oo.serial_no)
                    when oo.transport_mode ='air' 
                        then ''
                    else '' 
                end as \"Other refenrence No.\"
            from oo";
            
            //$rs = common::excuteListSql($sql);
            $rs =  $mapdb->GetAll($sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $sql), 0));
            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_extend'] == 'sea' ? "Ocean Freight" : ($val['transport_mode_extend'] == 'air' ? "Air Freight": 
                                                ($val['transport_mode_extend'] == 'rail' ? "Rail Freight": 
                                                ($val['transport_mode_extend'] == 'road' ? "Road Freight": "")));

                $rs[$index]["Vessel/Airline"] = utils::outDisplayForMerge($val['_f_vessel'],$val['Vessel/Airline']);
                $rs[$index]["Voyage/Flight"] = utils::outDisplayForMerge($val['_f_voyage'],$val['Voyage/Flight']);
                
                //po_no 处理 有import 显示import
                $rs[$index]["PO No."] = $rs[$index]["_po_no"];

                //status 改为ocean_milestone里的信息
                $rs[$index]["Status"] = $rs[$index]["new_status"];
                if($val['transport_mode'] == 'sea'){
                    $rs[$index]["Chargeable Weight"] = $rs[$index]["_piece_count"];
                }
                $rs[$index]["Shipment Type"] = $rs[$index]["Shipment Type"] == 'GPE' ? 'LCL' : $rs[$index]["Shipment Type"];
                //订阅的参数
                if(empty($rs[$index]["is_subscribe"])){
                    $rs[$index]['is_subscribe'] = false;
                }else{
                    $rs[$index]['is_subscribe'] = true;
                }
                //这个不影响，不会deCode两次，取得是临时变量的__serial_no，加这个是为了订阅
                $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);
        //(本质是是底表ocean 和 kln_oceans数据没有及时同步，详细页面有查询的ocean的时间照成的时间不一致),
        //单独处理一些，用kln_ocean里面的字段，与查询页面保持一致： etd as oo_etd, eta as oo_eta,atd as oo_atd,ata as oo_ata,
        //这样改动最小
        //这个是同步问题，同步是实时的
        //$ocean["f_etd"] = $ocean["oo_etd"];
        //$ocean["m_eta"] = $ocean["oo_eta"];
        //$ocean["atd"] = $ocean["oo_atd"];
        //$ocean["ata"] = $ocean["oo_ata"];
        
        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();
        if(!empty($ocean['_invoice_no'])){
            $ref_no[] = array("label"=>"Invoice No.","value"=>$ocean['_invoice_no']);
        }
        $ref_arr = common::excuteListSql("select code,value from public.reference_all where  code <> 'PO' 
            and COALESCE(value,'')<>''
            AND value IS NOT NULL  
            AND LENGTH(TRIM(BOTH FROM value)) > 0  
            and lower(serial_no) = '" . strtolower($serial_no) . "' group by code,value");
        if(!empty($ref_arr)){
            foreach($ref_arr as  $ref) {
                $ref_no[] = array("label"=>$ref["code"],"value"=>$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');
        //特殊处理po_no,配置column在查询的时候，不好
        $container_column[] = array("database_column_name" => "po_no","name"=>"PO No.");
        $container_column_config = array("Quantity" => array("formatter"=>"number","digits"=>0),
                "Gross Weight" => array("formatter"=>"number","digits"=>3),
                "Gross Weight" => array("formatter"=>"number","digits"=>3),
                "CBM" => array("formatter"=>"number","digits"=>3),
                "CFT" => array("formatter"=>"number","digits"=>3));
        foreach($container_column as $cc){
            $cc['name'] = $cc['database_column_name'] == "grs_kgs" ? "Gross Weight(KGS)" : ($cc['database_column_name'] == "grs_lbs" ? "Gross Weight(LBS)" : $cc['name']);
            if(!empty($container_column_config[$cc['name']])){
                $ocean_container_column[] =array("field" =>$cc['database_column_name'],"title" =>$cc['name'],
                "formatter" =>$container_column_config[$cc['name']]["formatter"],
                "digits" =>$container_column_config[$cc['name']]["digits"],
                "type" =>"normal");
            }else{
                $ocean_container_column[] =array("field" =>$cc['database_column_name'],"title" =>$cc['name'],"formatter" =>"","type" =>"normal");
            }

        }
        $sql = "SELECT " . column::getInstance()->getSearchSql('Ocean_Container') . ",
                net_lbs,po_no as oc_po_no,
                (select po_no from oc_container_item where oc_container_id = oc_container.id) as item_po_no 
            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) {
            //特殊处理po_no
            $rss[$key]['po_no'] = utils::outDisplayForMergeForDISTINCT($rs['oc_po_no'],$rs['item_po_no']);

            $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'];
            }
            if(!empty($rss[$key]['grs_lbs'])){
                $rss[$key]['grs_lbs'] = $rs['grs_lbs'];
            }

            //为每个柜子下面单独加状态信息 ctnr_label  content
            $rss[$key]['containerStatusData'] = array();
            foreach($containerStatusData as $_containerStatusData){
                if(trim($_containerStatusData["ctnr_label"]) == trim($rs["ctnr"])){
                    $rss[$key]['containerStatusData'] = $_containerStatusData["content"];
                }
            }
        }
        $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信息  ocean G和 Ch Weight 改成一样
        $packing = array("Quantity/Unit"=>$quantity_tolal,"G. Weight" => $g_weight_tolal." KGS","Ch. Weight" => $g_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,
                        'website' =>common::getWebiste($ocean['m_carrier']));
        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,
                        'website' =>"");
        common::echo_json_encode(200,$data);                
        exit(); 
    }

    private function _ocean_excel() {
        $ss = "UD5Qek0pR%2FL3aJmJClY%2F7RHy5As3znfG0XuXRBGpoO2xEBNmaWm93auayD9mk%2BU0tjXP%2Ft%2Bwl9OnDpTMHdrEg0rI%2BvR8G130D5qn%2FM4%2B%2Fwp28n51MxTJqP21yJQCZiuXexWBTv0xafTZGxKzLdyMFGufEGdgBV0FyS3DOVYmlUFMfI%2B6qMwTA6ZK8KG703X3t%2Fa2WYpyTW%2BXz7aKIoZzvKVcjnrNuwUY6OmeAfAxK17dryxvO8KtkQ0OTTIaP18KU3YNkAltEC%2FbMiLj3GpsoGPYQjuAdJ9n770AKUreC%2BY%2BSbsHiq%2BDqTtJJAX0Os4vD1gzNnH2s4LhYH7t1LlHeMKOyp8v6QZpTMRJNBAf7DrLJ%2FjLAptgjYmmiTkitZwcy17okN39Fa2A8UJUmzkLV8Cr%2FmRBUGbZtGgDXBzsAcRPqPzTOC8IIfDU2enhQgMacRhAJ%2BIlbe3XKTyeaQ%2BLLmFruj3VKTiXe6DylTwgzEL3gV0HnK1YZG1jAlHglZgOEHFivJkTNsHFEMBJWeNmF3mIflLO0QfhKLtUvv7pnutrDM3U3UD3Z%2BPXSyuEs0ndgsQJDHFP1XvwU1LXZTfZLsERUwzm4RNbiAGssM8BXZ1YmctAtHbo5JEp8%2BzMluBDh5E%2BGH%2BEDaSLO6ifWpcXCYHw%2BlMPMamSAPQ8RFQlZ7L2oE%2Bm134pzqsy227uw63EHYiucRmhdwuQiqxUoKazlKD%2BgXzlAoqGEatqRfQKlzz681Q61xwluzSU0TCCv3p7kpZBrx%2BT8wnvyX3Tb8l8lmJVayCNjCt8xFPOhFkiRFaOcq0WcGn13RWRlUdIgZ2LxWU2%2Bg24AR3iQqbFRkxXifcuMmpCgCqNyy1xsdf6GikS88wVa2x18rSlic3LqZSPWXsaSoOw7BIq1rm8zZO7x45mHpSji1CZu4clhSwptA4VBT2b7tqn0P57SbLhOLdm4ElpUenmRr12LOm%2BdWmZPIhL0CgpkCYuCw7JjJNTKBjqjxIKro8sgDIC871Y0wY3yIto%2F2j70jAnvQ9EZNlOkXWhWzsOw85yyAMvs2l%2BFlVCjsJ%2FwFYU7xCcYoBJ16JfTUk%2F279XvXQpvnTQpHpz7faz08WEZ3Oi3zhwdVxmYfd%2BgtnBfWDpntLRIe5ebMbaeYInZ7RMQVTc6rI50OmQaHs2lNCIx%2Bmwh6npAe2fPmQzygphgcJ5FNxt%2FOLgbtQVg4hmNvXmt7PJwAs7t446fw3cxMJa4kzTDtM56BYfJyita%2BYRSqOzERX8j44ZCYhU6SnE7yNg9tVA1C0lQJez6y3UQNcPGRign8ZaT25id5JwplokTRxmVLt4J69kRcivo%2BwUllxzgF0VzAf6PdVteNIdCera6d91TNMVvNo1f48JDKlX7MULVw5%2B%2FywaXoFSLz4uyV1nDoVfK0oacILO1Hv2VJ%2BnwYk4dWmmgvtHSGz0XT7xgfsmgUZSrlaU%2BAxN28z16JxhkBxasXbcG0BLpXSwITMXvQtoR8ahknjabNGuDSVEECPtSyRSjdBE%2BVdkuiQlGe62mkoOlNhflDRimbZpW0723FIyuunZ%2FDjMbycS%2BW%2FfulqmUvranpeow7I93yiYcWVrPCF%2FMcwjgaV%2BjNPP%2BCMkktdoqFYFi%2F%2Fi2pJW91EruvqdnBI3OboZkxRpmKSa6IU3cAOJ%2B3D1PvOzkSGesmapTyU57IPyo4kLypFD7xpeRgqUNax2%2FigYj%2BLrem3IbHgkhjdcy4cT8O4Ca2oheYZHydUBhjZYciyk6mTrFw%2Br9Kgy%2FD84Sr%2BJqplwmJWyD%2FHHJfVBnecsqU2ULrQl8Dg1ohQFQAkBBRSCo9zyiL6p1IPhL84iveU1thcjG2WZh8TG7EE0RpgPDAPpSrgWy2SQHzVpMwes2Gg%2BpcNI043LPJzGa%2B9cOFNQpu6w%2FXnXb%2B5JsozxgbQEiKcWn5Lz5g2%2BONJJfNS%2FFC9Ku%2B9ahPUpRN2GMdqO4q8UIhYfnFBrrNGU43b5ijQHXjlP%2F%2BweCxbbD%2BaV5RBXar9%2FLv%2F4KqQjLj00fHKlmTn3rD%2FgcbHGTJHsrhT%2B6b4pNzPqns1Otj3ocXfjSEievXtBPl2rQIuAIvoBGu%2Bw15WestKwibtkeK3mFX0mxkdntqLssehNd5Tydue6n3SA88vOu3Laq6gfZ%2FURiiFRBX1GrpqnnTu%2FpDVCobdDVbClmPbaZ3P%2FKXrZSvmD08IRqxey04AwS%2BD%2Bi8FVFiMA80oEA8ulBAQKU7%2BSoZ4XlS3re7hlnTyo6bxamz%2Bg0d3k8R68dwAiKQO%2FvOUW%2FkodlOPhh2BvR5VLLOVja1zWdH96sXIPf5mZV9AU%2FURDZTyX4CBVMCGKLPF6duyGR4rMKigiNixdEb1WDEOaFPizNHSIwDHAdxeMUeD8sEIf0oMeWDBwwCRNLw7%2F2kPlmAMXr1J2r0lpvYWA0dGHj4%2FC420AjfoO6xrJaJ%2BDk9mqak1AxL5%2Fd00FNfXk5GLiRwGIOcGsAGgdhDFRJ9bHfJQnvrtOwbSqxOEN52lJ9%2FPEvxtXc049Rn6y3sB5ARNjha6qNIKDzzCZvmJ2OXRvbt0dg9CILjLxEDaT%2B53cf7mYFZ6%2BUxtqz1hmg%2FGWOkFY5r9oCcq5lZy%2BR3xZkyjvgqEcYwAqzDmZ24Wd3X2z67oFabM6UThJ2cMbzOKzmgGkknyAxt%2BFLb5n0izMn3xWD5bC8lKMuwgvZQMe%2B55eitm7sNxaeBIG5HW4cM2J4m99rP%2FoQop7TcSrKks5Rr30GFG%2BdEqlJXhWR9sVUoBcG%2BBy6pHxIuoVvOTlSFwPGfg9PcJ2ezAgxP0k84%2FNZBkVAKmRgsd%2FIKdtY%2B%2BSdonYFf9EWk8VFEEUU3vvbPhHLA%2Fh9FWS7eiGXjMum0%2Fip5yPHFkCHR5%2F%2B0%2Fo88wCj24BF7RW%2B5kRTZesMiFb5BhgIOuoMItq5auSR412y5I1v%2FNxB86WCIc%2BOnfHhKRrEOfgreTJEoPrUhEg7w23YlV2nS%2FRNHEKjLW%2FJDptVCZILD5%2BeDnZ4V2DKIUfxBBsCw4XibSZnxIVcleQ%2B2wdrXgnzo8Ub0YlNbgziUPQz0hlMjKKFFuASzfNZEVSFXk%2BH18TIm0NpxEqBu1blquDINwbSEpehv4gwS9ipxINn7siSRbWFMdCqCcVLY1B%2BPqOgDurnC921hxKGMSddPOuPd%2Bz4zb8YO6nZjVqlpI89LJrIKlpVD9Plq9ALOt%2FD%2F6yLXHZ0h0dsYv3LGvcZHUcLUYaphHRCfUgJ4YhHHvyl5hjMCMmCP%2BsXqh70wNbpqiNPfR2Gy33cDWLw1%2FxnZWQO7tkUBbGqygV93Hjqz9QZguvDID5BoAIQqm12n0Xf7sV9xCDThcpJQfmCfgtKe8zI4a%2FsmSx2shB9ZBvHGOiOHSdWsdhdcMRRdARLgo%2FoBk8Wavc4rHRH3axFPcn8%2F8kuR9iA74Gpq2K2hD77SKzl%2FFZ8y9uU%2Baedfw2Q6f3P3R0ivYRwUzfo28uD%2FTvk%2Fs%2F12%2Bwwum4%2BRaWWgODMQTPvIh1xrtkqGp956o7s8x2oRLAiZRPxjnheSLhDE3R2abUmRUtldFDNXmIA%2FuFsxUefP69QJvvI8d5RN7idUP6QHLlsRPqRm0j9lMVPXhIUR29SyzHvAr5hv9RI2vHgVi734VZ%2BPDGQ5tXdlj3vJ28qzEiY0lHkkVK8%2F7JUCs6reot5kXIo%2FAX5XzH1Hdkudv0sFSbvdms72mHmf080FXXQl7%2B8GcZu93fsI613rztMxBza%2BYNEXwLShJOmKNqZwhjrevtsbWTJcUOLAp%2FrUjB1n6dvqARQI6VnBTK4KCsNCoEbW%2FM5iPrq4fhrTv6ovWuQUv4gRmt6EQyOH%2FSN%2BNKSLFlkUJtYM%2B%2Fk8sSZDzHNWa3kWSQd3fF31YZUv4TleZCfwCu6b049aRhFJ%2BSRJLUHgXcVcsAeib3qkn2MVDE60zJHMlB90OBSgqeeDdVn1RS%2BNs%2B8Ecu%2Bm7pvxJVubEYT%2BcdLKzIkwn5BxAd8PoEOfrSwjt1jVD5SyJVo1b7%2FflOYkVZO0yy4Kz5chGHB4PYNZRUqc3EuNsN%2BQgkhODqX6hccXbjTAp8q3XfTP8SskVwn3idV5tvHW42LCPdF942wJlDrH4sboPBHJbjXZU6gZnSTWDNk9hgAwW24EtoDGIZSp3Zu5bZfDCI7OY2FBX9yTC9gMmdhi1yTKslY6XCHJ3P8dCa0o7aUnKV2cLbsT5EhS72My0uD2q%2Fw8prHm2AdkChcqFrXGx2I6rzBqeDfU35zoN%2BNDZTT9v3TpAVVTu%2B1TPSQeDoY4iuVJaUmi8e5ijZ3aQUEdqX%2F92YfN0QEAxNx%2F%2FDx7eSJxvYcG8GAK%2Bs8iB5rCSmIcc8xT9yOFF5UVomVYNiN%2FJAU1ZTBpQ4a%2F0fSi9HW1TC580zE0kTLiEMCIR3ZraIA5g2RC6Jfb%2B3EVe%2FZQ9m5K5v%2B%2B5J68MXylqsIv4QPvcmP2g0GNcJNvVM7g8ZBZJ2ODAnEjFTy2qOyLiRjeiTGG%2B2t5bXEyZJnxx88sY0EkunZhTPObIqpR93sequO7WV7Lgf39W9d5BLseKH%2FYr6DBjxkXh5ZsNsWPRFDiq6vadqVFVJvBtg%2FuzT5Y%2BdLeh32WzfyWli%2FeafFQjvu6b6M2fLk8VILLzzuwxwu8SO0PbUK8TPcE%2FE1A39m83sWhlkR1QybBSpVvA2uOCFLqCl6CSWJkO4ZibA3xQ2U714QOOtYNXRzDOjeJHzgznB7LSKYJlcGsPUuUVhFW9gSKB%2FslTzWlXWT6H5hxEvfvK8%2FvxsE3nsYo1agg3SQuCTfLKuw%2FoUCOv3mQUK62sKtmdrgAvEaMABrDZib%2BUmRzjzaIUjzwayEmGBCpuy9Ag7EUypcU4hBNS6GeMANiUZUJfXbVORe84RqPBxaB4Pva3ATDnc87IvGsQZ6hezlsCVg9Axx9UQP8%2FBfAhLHNW4%2FJ3Ip2pXPbkG1uN1%2FWQPlP8hGByQba4L9PRgkkLz8HO1kVsvCG5wppdhlit0AQK%2BYKfjE%2BSmL87uBBGPLaqqTo1tC5p86w3zzcP6UwC7Zwwq0NtKf3BxGJKuQeVWLwV1lssjNB5a%2BjZxrgAOvqg1rguqPaHDPP0yJhrY%2FWVyxA5cd9MGwXWS5y1ElN0r1MPG5PVHWe6xAIkRK9N8uGAaDrSDeo2q8hQ0GlOQBNYBnC7yorcSLmLyUvyWcwKd7nZqtIpUzCbQpyIhUMWe58xn9hFSBYj79V2qHYZzQ%2FazEH0bcgmkAdkHr2QYi6X9RIKp%2FQpEndNfiHCesOK5dcqClrTJMUV5IOITq77XnucWHi%2F%2FXrTggy0yeOTBHT7FyKNR5rztaWUZiQtglU66rwCk45qQG8PXsjExkdC26Lksk61DGjpWwfNhV2ZT16NBNdqtclgER7M5NqpCAPK4sfrRgugSU18JS30afjXpbJOJRQgGeDvMgWic5UHMZ3F%2Fk1yQbWsyB7r%2FZYYlOnsHD2iuMnai%2B%2Bx%2BWM1lXirQIb0ghEtDqB%2FFwHk%2BaUjD5t2bxXbNl8FgUu%2Br6Lx2uvMOsdkvCQTkmHQ4fnvBfTkuM7Y4qhX0rZN2u%2FsdhESOWHhlROqTJ8UNWNfiaHKnpWxAjiXLzPfMISsafQdGDPksD018KPdiTTu%2FWTWpGBTJsAWAonyt%2F1fhf8Ys50nwPqum32nCBccUXl%2Bf0TqCDu7S0qpmtQn%2FvWOUBeizX%2FIkZ%2B5hoKKv1IgL4A3ve2CGRmokYJ7HGllQ8BrxqM42OSmlWAP4kIWu23hcbC4XWFsmnyEXkTQPuU%2FVbddr7BzT%2F2zvaOZXhBw78hz8Jg%2BxcQ%2FJ7aDqGNlCvHxcJYIIrPUWxzMxql20DPfk1t6TDeUhlJNs4m6FWHPAd3oAsRCO4oSExt6%2F9xtebUinqxABblDx8qgF52%2BmRUnQU4T9GtZomcJkp%2FuyHpdMCD7XOHXjOinPGx3aroYr1XTlKtPDRqTtd7CA30CkPgM8Mu595Vyi6G04Qx0%2B03UuREsLlRp%2FRGUDNDuTVKlnvHNgVe2APkpcfX6OHjlGACNmMFyihC3SDEcaIl86ltAlRX6zI31f2Ml%2BhNPOxM38d59ZmMjFejkR07oCNU5kscl2ts%2FGDm3ZD0yAZy9dFgaQbysnagitgXeG%2Fuqd6E5%2FjXbjWMeZSxJFl9UkgJOp2GgzRbFMIlFwfO4gTYgfJbmCyZbibTqKbodslyHAuRu4VwKCC4Ug31wgEAok2pI5rjzaGaXPzgXT8JVMDCdPa83VqBqHgRoLgSKe3D85Zdh4o20prFaKgvzRqhTiv%2BA9aToqqrNcH3zefIXZ5ACJvOuKrQiFP%2FvCPtP%2BvUeiiAPeIVYbDJE%2BPB9arHOV0ssNMlt5FaOaK%2BtCBw5AbkSeGFFP4PsVx%2FriZXhmDiUiw9nWq5WdnPXmNfRh3b%2FeN0p71KE8LOGHruveDfPpIglVyAQOcWJZDNyVRnRS%2FG7ojNHpq%2B28Z1GDiGn15mw3fnVDKVu9Zc%2Fx48gJtJD83m24qGHnrqgy0uQ%2FFa8cCFsE7cNLv70xtdP4ChL6key2jC7OHqh2Jb0au7gz6Fwmow6DVaTJgxNL6Mk1tDl8%2BYnNfPIuI2GuepV2%2FBdpgbMgo%2BaCSQRfXYuRjSmXPWSL0w6XJBaNki58NfwSvOCq4oKoE6lqa6JindjKmWqjmebMOmvi9QkBqStbGWI2bHU6%2BE2rCupklPXqd09esrUvrGANfldvErWlN%2FXnXUWbnmVY75V7Np%2BKdkMC4%2B20qd5Oitv2NV3grJPzxfd56v4ZfSoxJXBCAvLlSxfNfm5qd9%2BtYIlgGQmsLbO2bKvnuOxdWxPfh6n7jPGGTfpHtVYXtMs3QdZg3OKjsa2i6rT1mC33L91SvnF7JHaFK0qQA%2FRh3040El8bRP7Tl3BLwXzkG3xmSHmGJm1ZSv9kGRxSAANm6eFbhvvjZlHbP3eqzr2pAD49WihurlJICnaq%2FiwTUZ2cdeFDSvp5A1NPj6Th5R%2FpBVdHu7VYlBuOeJIM7cFIcxvyfuZU0oF8DlSxMKYvYTneVdQ90RPTjSave41ut%2BzcUJTSJ8jq8aAmeOeVyznHt23r7qTVyoi8OFkanMoNVzqDBTv6waDrpM7GQGJkcl8b%2BkIItstSK9UNkdqM%2BCoUFTUJWEMc2NwOqiT3iCct%2BuZSQ9%2BGaCvEXhZ6Y9kO1vYReaBMaKMn6gulunDb0PCrEpaT7zCYtMqQ9h%2B0PLh5XNZl%2FcqSBfJd695Al7qLFUetrArM89TJ%2BAwsG%2BPD8cvuTRQH3%2BNQInBzUAA%2FjQIFRtngy7JLmm25uzoeKbeC%2FfH1f1fMz%2B7WJElv0n4muyVUsHzhwgZJKTHUdz%2BV4BotmOWsObEDkT9Ea2a%2FgCeoPGL1f8QwOvQIJCaCr66qwfdXOMTizfD6tfT6PWeyjgT9IsCMDw0tSlPtcCgZlhITi6PSBpXhyalBM0o1UDkGXri9UQbQxddGDI4ID62hbMhIQw%2BdskTBnGr4Kjad6bmDMKjgYqQzJiOFnREzOrSCs8%2Fhrn4QkuIgqM%2Bj0j%2F%2BGRIzKg%2FTpAvHiOUcOKOl%2FqeUAvb5MJEIfVdZURVd0CFKx%2BDCJQQZw1VtwR%2FrieFr3ewCmZgw%2FYGAI8V0TPToBwMubM6fbPqgbOBZsnmp3QbAMbd2ZAcrvmMLeyMiBzMzwR69DDhUCVW8mKfN0XShSO%2BLGDKj23B%2BkeKTKmyLS7irhbDCCP6izisy1yJIZqezj3ZmxCased7EjKF7bHf1KRByrtvgEP1s%2F%2FlltoAZPI3pr4U3fvsSht7%2BE52jY5rRlGiHHdeUetvd1gX%2FRwW82vRilRxCW%2FfDqFoUsQ1mR2sTVqjdvg%2F98MbAxuBnRqXyJg%2FmrdZJtOjbM8zf9PuLmGvNhpyR8PLzgB3%2BE%2F53OoMwOjAzOdrUPIyMsbmhY8eICENEHWQDV%2BiiybWxD9%2BsojIGj6y8Tz2k5w6BoigCtnGxbJDPhbtalaohCbtCBCclUFx%2FlX%2B6rP9fkpAHQaBWQGX8ol0uidXY7Rt3NgS7uzjY6yUeZwJUxO%2BgDi8OWdcRfIkULdGl7uu8Wnap6DKGTEKzBozSuEqZ%2BL7YZpHj36UiQLUvKCrjPp13Qx5gK70WKMO4J4E8UajjNsR%2FYZuouJQ8R0tXjvlckVrxlTQybn1D0Cj%2ByD%2Bp%2FDJRUni1pDly5ba8wIa4bCsjKqeft%2FdF%2FrB9n7sdrfxfGyAXlwDoTPlzU8ChiMcr01NKc%2Bmn1kUdaVHYXy46o5dHzkVAN8Y6PfZvAgbm%2BjAE7dtFPptqYza4ajXNeOp2KAj8iJj%2Bcnf40dlvXdJiueH%2FgoS4BNtNwnqpoo4VW9YbRv4WNdVLBiCbu9x9gfGStcvoCgqifSq1LWG8kbGzlv%2BLnqN6EYUHJVv9QviohwQYIKqAdtsLWknkdckOuxm6Xo0ovKeGJmBW8cZzqtwjQD0ChcKTO7EZx2b653xYo%2BixuAu%2Fy0SRCbHVOeLrhmyewQNpdsYAO6HQzuKTL8oONfiofUPT2feQczrCHgGI1%2FdNqL7peDgC5yg9qGMpozuwnxqbJoLlM0ZQSvkTa3hKNKBwuFZ0CrjhR7QAGdTJYVBzDlAg0GsBZ2q%2By45CAl7l3FHlXzWikbbAI6tPiy6rGpgidXu82Qd0yZXa0aHi2Uc8GShLTpx579D9W21b%2FAolsbzMpC3EF%2BwlJP1ThlJWf6BRMJuMdMmULn2EBnoVKpMB5taKcibNHVbR3eHegEvRlnZtpk6lhwIhiGVCEwI7JyoNAickW%2FV%2F4PzQzoY9R2yM3qsMpBS6PsD5WhjT9TAt%2B9lbbLk7xE3T1rn7reKv8yHIwkt9p7rpDl1IoXPx3LBZNR3Ikdi%2Bke3ezocTsbknC%2BWegYr1OO%2Frz9Dtrs6SCYLA5otRF6ad%2BaSeq%2B%2BK67rhE1gPJToESZuBBiBovlDU1GU5otd5WM0APuBCV3XsoD9oo8JPMcgYNMu9s4QXaDFQy3g9L9GRNNnMOaHDb4OsQ1s5r7%2Fpl%2FWTPHdinSL7JgIiHjbSTge%2FhpZfOoAGxPEqK6GNgPoEjjLAotuXxcTQUiFrOVOqKAK4q9hp%2BRx1O113aUfSPKxnmJv4N8vFMJd2G%2Bw%2BeN7PRNJv3uf2gIgKto1ixCymwAwm6NRNHmXsjQ9y3QnR65jehoOfyIGh0Yd6XHPP%2B6Xa7Fm%2FkNW0FP3ynN6Fwhymt1NNsRp0IG0I3I%2FjJPVzTpD7VtiUGUjItdRpN38%2F%2BH1XpLa6oIKLI%2BolmLLUZcfFU5ZSmmv9y66B4qnK9rQnY3%2FI6bRsDFik41PKG99r241Bi01kFCN7dBq3j7yy6%2Bb%2FjtzrqMJZ5spjZuDU0AAbiDg2RtgSeP8ovj8SDGGmQZrV53qRZf%2Fw5xh0AjcfAYBtmFitpIhJK0DO5I4mFlLxn1vXdNPf6tF52SGrPzCu%2Foy9qYd4Qg91d%2F8NO1EidUXFnRnW%2BRatPlqkbGjIO3h1eEl3P4nlL%2BzhlM9UbU3AZpY3BrC2gCDf2mnK6tAYSLDyXh%2BUD63VE35xtbW8a%2BD0yPiHMGpyo8l0Qr1XPqBwriteRAFR5qxOKVa092XkFCZcmXIs5nqZMk3NI6KdXzj2RXpRLWy4y1F0P2fY5ZNZP6aR9spTiHzvAUevJDYudJf9My0x2KEFGVTw2xREwC4H7FGzUY0O4TrQ3dORqGwsRM3hJkHXxVACH6EUdxsrvIABW1pKSRApMs7ITmB5ZKeQKouTo6eLyQmA7iPcbyBRL7me2SpCIPsOadwYlhYe2QVQGlgamsoOcfgjIwwN6ZXy%2Fzh81mh6vcdy0J%2FPmiDN%2Fff2CMr%2BIG8at06pZ1cUJXp039WpsbfHVrhp%2BUKWdHtx94Sld8zOKqAIcXS3XI1BA0l56w%2FlRKl6YNl7i24c7qeCV%2BKRHDrdzw3FJX3zczItg1re%2B1hFvNx7LXcWNJRRxMJUOp0E6X9CAImONUmSbDyISEPoxIRa01qjkYEImjzLisKSsO%2FpAKl75rLNFkqslKkdZYK6qvlH%2BwbluaAtWScUrqxIV2xGLIIsMtZBMOt0Yfy4ePYFIld1gxzF1NAmc9N%2FLGvm%2BHIYV0OZRiL2nXL58yTNU7mvAmh4K8P2IB7sHZr4DdZdVjYlDk%2FC%2BUgVqAAFzGgbXHVFoBQlmWuvEWHjOLHQSp8ojXM80WgOmwr%2FAD7jrQrZrDMXUSFa9IhvNAMY3KFCNTt7LhlkDCFAQMFY25tZdDrO1d9ST4TJ%2Bu8UvuLQQyxwW60CTSt2WYiix%2Bq7Cr5UrFXskMf%2BTx3sOoWEi1%2BKurc7sxJTQbB%2BEQWd4Seqdym9DwmRVPTN%2FWr2d95K2tJhNmyc30dXYeLooIQYzNpMDb%2FjB14I9u6FA9wvdewu9HRO9Mt0u3Eak%2BoN6POCZMOQuiPWuAFq1ZBN7jNaNoV09nMHm1C5I2smFqpRPTp44B7mQVAKmctQxF0FvVTsYp%2F6%2FQeuCmoDzgYNigy38xzqlFYGj%2Bg1U4wv2joRXeyl6%2FsDMNL348MLzjz42AnN9lOUuEy5YXWQDIxsvyAAsfhE1P9QaTINM%2FhOogrTodGHZqot0v4lvQSLuFQ1d8UXYfxGxUIH%2FucO6ie1WMxstdI7orppqw5K5hq13zmq2eZrDpXyJB5wIpYaH5p2DCRyYqDJFWwBsC4gnyok73nzNwkVihj3DLZM5I4U3aKrq15gt2tlmP8EBdGVEvhAB180uZ2QhEZSiVmFt1oDVE%2B7NQdYH7eXlfpPdH67oi%2BR48jPPovBptuHPO%2BQtQufhJ870FrZ4TnwZCK27sbmOu9FgCRshQf1xwEg4ZdbBP9dt4cSrKzQ2BOuoJUIYOsbmpve7djqYRujtBK3qdp%2BZX%2FbLlUelMfE8nmK2870OsVd7LwDTynyXF79wmZBlKoc0C5cl48HkXxsAlVoUyQfilWcnDP5Rszt6ATUGd3iTIzF%2FM4HbK%2FLVsxYkjn3LYdHu4AX4zbvu5fOv3OdN5KxQZEG7Cp1IEIPZ3JuFP387MWzrfLpgvEqEEtnCqonZgUh2NwTQ3VrGlqcQyy%2B3HGKk0cfL6nQoeBTbq0rzWZWtoa%2Bwv79GgTkpB%2F4ONyk2a3dSQRn2B3%2FIUMTOHCNy4ejbPfHIj0Ft5Tmk%2FS%2Fl31SfwamLUFwJGcooDktrA8oCehBJ9Jy4OQ7uIgn37uK9pyJLN1oQlYsHwDasg5AvlGrCzdBEGcqVIpSeIN87nOuRXqZZaDPr3ZcddCce0ZdCocwFcxBuf1wUbyOHUlUAMsnwzcZSXHQf1%2BCVyLBgHurZvxoeLG4ouvRMQChJjrxW6XOV6fyBpDCOPnlFsMSyrF3%2BCvXT1B8VZitYVzqbIlH70buMpd25jhG%2FNqCDwNrnZ3a9O%2B5lZf2kTqOe1PI1j0Oy6Yq2nzf2N8Eeno7OUej3BpzoPzj43JpvOdAaZNBoJI5ByUhxJT0nID1d63GcPOS5Gy%2B3za0vGW7R%2FAe%2FSOsmE%2BtHMDXM9jnVFbZ7W%2FwdRMihTAUts7e0096tWf2SW4y0SnLwrde2FAHzQZr8a7CKIgWgOWwIqCxpt1wwfh%2FbN%2FaZJS%2BqA7hsd0EHzpoxrslb%2FHS14GWtgArJckkuP6fDVskZdEcuUtna4udJWvVyiJvGLICYqxXUAQUMfungh%2BaPZJJAviBMs%2BHIhBD1%2B2vxoOaZiTBk9pcYWWsdsN%2Fny4%2BDbfiFqzGORr196JeKsFTKf6sWcKk07%2ByvyGwHzf%2Bcnrv0XEsX%2BAW8LNOaBsSPcpO%2FITeFOr3g5Ud86x5GNduqcYwt7mWNy97ERIkPTEBeRq8WsRJmCZzAD9f3d7tGwCTZoSuOn4%2BelcJlznpnGNkci1TqIFaaXRcU7GAOW3thVD%2Bq3mQOe35pMAuLS%2BrwOca56Ykzxqj6dWDssmx1jfZPXG6%2FqTmCsUY95bZlHhmYk1%2F3Ox1dBFomuy9hEI04C1NZ1wEpOwkgrJea2mMXxsM7MIamIW4ViMsph91Di%2BK8Di5JFkK%2BD%2FOzfm5SAOwR8XDad6w6Be1goGJCDNV2PhJwxbtqHVvoRiM6n6Y%2FxGsTBwHH%2F1Gxrbe8QKuqUZphrnSgegC4mc1KTbsdqXOTxZzSHMcJbBF%2FlzF4oSKec9fzBALUpAjFTsFnFtRjMAGtdprf9VEf4RVTYGXn0GFVadhsqCpp%2B%2FqS5T2b0Ut0eitMYqrlYm4UKojyZtnCyAhICUtPVnAjyEsfZkPoJtya%2FNd%2Fl28xBMWO%2BsvcDr2k03TDJjVbfeYFiaqNvE8v6ZGzyKHXQiRvieA%2FoBxgGOpvULbAW4CN4uNVM1nqwutLuvota%2B6Dr30ZKC9Q0cPQrOXgqobLz1%2BBOufAsOhwnGb5TuyqR1ukPuzNGKLnYMtUU%2Fse%2BNuJ%2FEOVGRdnshceBIPVhqoJ%2Bz0G2tkv3TpZb%2BjNhLS48m7p3Ip%2B9PYPW248z8LEIzn0YobccqTOjMoncK6PYI6YOs0%2Ftq8%2FwRep2Zq2ZjMsCxPdLKHtfosBOMnn6U9Wy5BRwVcb74CDMQkPemW1o3Ce4Q2xoA8NJaEMMuB4arA8pqy7A6ervapyLQ3FxjutJeE6GP3SMhPZts%2Bv0LoB5Pr3d0caaHPYOjCQ%2Fzq1VYziaxoYgMZKyMlIGqWckEpncoJOP5XySEc0OKooqM%2BDx07Mj4CDQ%2B3p%2FwALjc78vNKdtrxCiN9L4OJsahp9ifUcmcE%2FZonoF370QIGmXba8EERE6XTXAYYl53%2FmQoTQ%2FFE7PUj%2FXDAr9%2Fcaxhyn5jX%2F%2BlfprumE3QVMcXnNiqp8Qq4u13p5qdgmDghvSOzMvsuYmU054mn2S977sv6ev3gkv1v4H2YHFJe37rjsEwuyvsJh3ouBodFHfB%2FudRmtc%2BKqK0CFxd2su7%2FFv7%2B681aoCK34KG776ROCwl08ufBEgxHqJdL95H%2BftwNDyZu4OydzlNKIGCIIGTpom3MaEsLoo9X5zPmszIDZpj2L4cyM%2BrC4te6jzgTouFg%2BLQA3meKDC3WzFPDXxq0nUYJEzgcLTyXxMbbaEgxxRHT8DeA3JEI1mSJl46jibSy9mRPDoRTroEsK%2Fb9l0uc4hSFRbZACU6vETXEBhZNxFKc%2B4zPCnr2PAufZr7KPWMSSMIZMJLheaIIYgLEBhpDbf2RUdkfaC7sugDBIEn5PZ%2FtEEwZV0WFZq9V8pwkYCg5gn8QvtD%2F5Kj%2FEcC3HH5f2xjY8IfYhiP84GHuI43UxLo20XMvXzT%2B6so3XCTtkOuGw8jebVDXHgLGOv%2B5P7ignooabbbxg2TBOOrc6a5aN9cqKTSGW9kg8w7OFl%2FLKNoVC9QiOoLr9qFEbIgjspVTMB%2B5XSTRg3Fs23UDiqKugeCHJ1%2Bftl1tnQ4Yo8c0S44wrcVDvtxoi8EfrOuMCbwQY%2BFAgQHDUUzvbawL0JxPeoZgTcWsDIPDtexKqSsTIsroltiy8kO%2B9QEKjv8Xk3uxSU%2Bi3YCCaHwZ29P5eGukGv%2BxLlggPxxjyDU0SO3wWlVqecOP2cQZ55XF5wQK9SunLCMqlIGQzt6zSV7KnjI7MlTcY7jA8P60aoEVtTdItbcbbhOBnoOw1Ce90LnlezsKvV18p3qyWYWhSfA9A99A2jsvqIpTYsWjabcSbjl0bAtgS%2FU9UG%2FnRW12%2Fv9JyK4E2XVpwLaZupBZ8sMMxuCb1Umu8hqUbi7qeelthe%2FYw8dLzloejeLZPjnfiz1FUrEbHGQMzC41sjKcDnVgZKxdXOu0lXI36rzWuIl3fEbQCbiHOkJUeZGr%2BUFealEw1blZn3uvUQE1xq2RTn9DKPnJQX5mYoe8jmRN1L%2FN0Qcd1McFxzucQmSn9odocy7y56XCQN814rTw%2FwOEYyMxm4tByvK6LpuUxzb3IIRdVKaY%2FyuyV3LPghJYdvj2WrYPTEmFfDakYRQpDfPuB7EABwbxpsRv9kqz9%2FORUUR%2Fipw%2FSTyX4awH2KXZgtYjYEXcA0sSYDYzjdKvoaZ56R0fO8Wi3LbRxDTYjLFkvQY70btX2Nek9b%2Fuq6AFqhlkM6g0%2FjAaI%2B28d%2BQAZ5qUidKhAK5iYLlbn50F%2BW91bbmwcfYWAJ7x9Q0i7pXXuqmTmsrceOscbIZkfOS1iR2WBOmhTbBrc8rQmBTykRraYgvSNR9LvSCReH3yMyIrSeEFHtOhz5w4CbiA9GX75fRGtIy4teNcI23OvI3l6dcsggYd02GXwnGoIiFrFo%2F08%2Fkj8JVpyQjC0N%2F5imhxldu6Foz9hllU8SdMKdsMU1Qm88QbRt0QZZetkT72XBLuhZQXSYkgnY4abv9yC6cn86V%2BfE2McFSMIL0rbjZnM4YWpStB12OzYvwPkI6Ga8QfImIgGzV6tfKKnudXjP325Xnst1fhsmxTkrZ9r%2Bqnp9%2FiWTTOC%2F9EuEeUj%2Fp80iShB3bBoHl8BbdfrgnJ97DHlIGTxRpt2gdToz736W7fD%2BAOXObnSfBZ8m0caev2e%2FH9y9CKk8FU0sQJSboUC1nnysaaj9295taJ%2Fh2lhLsyIGrhnMKjSUhlGLA4Iuvz304Q8ywvtFDMuDLW9lRqhyLnBQofewP6EYRl0ffVvJ%2B4jISvtrLHfY%2B%2BBfJgFe2y7wRU%2BYmVCNZo%2BY5wojxplf5sIdMM113XiPFknYmzbICIA1ln2xFMROXzdmtYzXJSwoMMHsfcWP8cVhPzkAQ3OjiTUum3g732LQ1gdyU6Afg%2Bc%2FqJzMpZmoggHtZStFLJSzkbDDsqvx89RjaUFCv1I2B8cOXD1WvhzqzMmJoqlIDA4GlXyi%2FQ3wieFDMJRHyRePisHTbcYCoGH9v89RnBzNq08KXpeuYDqwcICuElhwdmeeoB0do%2F%2BkXO2OFyQP8J%2FCGAv%2Fccf9Bg1GsiCY3rfmXId%2FwXcEzudCxVT2UnAjSG7kEk3auwTe2dhn9GZGv1%2FkAOWNgRtfs6aRFsiVJr8Ni5q0QVjt77FF69JJMznywrn%2Bv2BzMA5C14%2BllU6mG58GCBT0YpX0mHMj2cLYoT61M0tzOxRIi6WlkTZ5s1YSeZRlWzartQBXUimioDxCcnDGHHu4BOVd4Mnz0LhX7hK14CJdWptr8%2FtZ6xUiIwheP7%2BnAhpgrkD8e3dhsDrMDnJawgojdVFMy55pd9m9eWOyuPRzUTMnevdNrucvoD03PAQsW2ddX%2Bga6gr%2FORbSuQtnaq91AD08QvWJqrxn5Ydm4xmwzAYJZNn2HpgaQX33DYX4M5yGBpeqGfBf%2BL6%2FSC57P3eaIoH0fvaSDL%2FbopiSQjQzgvayqAZseNkr8gShKfzrfLORvZF4cqOcR%2BQrKdFcwIt%2FLX0IZCliqeSA%2F65OlbWTV3jeQkr49tD8LyTzFjiRct3%2Fhm5Bz821QAvKrsE%2BweK54wQKuFhM0IVYRY5Ovl01XXvNj0l84VqO7axrvN8IKbVtS3ia23VcF2tOy%2Fs6NdKVTJDYvIYivw6iEtCRsZWX0IGwTY%2BwrcWWnvQJvJZxhETnyTgzNfZ1Ubkp1vNPp14aC3rFJ8yuNuDSHl3UWbWToRB%2B9y%2Fbs%2FjOAXXkyqxxqiHGz5Q%2B5%2BhVf3LT0XLEUd7e0RFEgmH63o4zuTnMu3QF%2FJHSFrTyTbYVTCqJi%2BU3DfR96LPgMWCLcJumnTVOhnACpYkTjZ76qCoTOMueiv1TQESso33sxZPyfeydJhWt3IdihB%2BynbHQfkK9N8%2F5tt6aA0lZfBchfEfp9%2FX3CC9GSe7I0olMUmrehty4bloybSRm6FPAMhwnCv8RNh99NFt2phdzJvkC2rzXoqVeLOyLzY7cMJyxwFnIY0YOGqaRrFkmW%2FGja%2BT8FIP9HZBuh2ytRZZLdnhtl3VB8exR0OtC3LuplEWYhy5IlrwZ92wndH%2Fxkjhbgui5fGXYct5Au15Kw5zk2uisq7nqZqHq%2BdCi%2B%2FKYDxitAc87l3qaXfZLKaBi%2FU9UM6AvUQPxRBH%2Fsg9ya27kNIsB%2FGAZkZcHUbMW%2BAhyJdb0BIbhNQRjbZGpQIp4k4QgJk7DjeHY6%2BgzOdVqr9iMdW1zUThL6yGRxPzjd6xNMumI0GGA8RM2Ih7rXWe0%2BOQPR%2BDC2a4bbkXUfeQcthC93VIR2ssbZ4XWpvhvWj1nVN6UG4Gr%2BorKQnpqppPZhPRrUFqeJP47geO419WCxxdUgproFdNEu8X9KhoroZia9Cz0FhYE3dcS3co3BqH4IIQPGnIj6vPh1PKCrrKLwuUWSyXWGcwx4GNEeSUjLJXcmEzBaFAS6pXXdCjz9BRUgne0BeQavBA7KCM2PKpMx7vaNlTTZfpOpw8tZ9lPzMs1unqVGrSTcGG4y63wkbBDLzvGj5EDO7HgUu9SV9kVeCnk9tr2LS%2FwjWS1qlqMygnJPOzkNml0hsL%2Bu9De5bznYgPMTiUB7DH%2Bn4gzU8OTWxUxtMkHSEB3RKdSBMDEogM4xN%2BWij9t7fVFpOEGtIkHYaP8wib%2FAnrv2TEPb07IX6LfwT9M9fI%2FYKPE34YasJtIckJxvTzUzyGoiRudsTg2m79KjZYEQ1kFFbcg1CIwl75fKJ1LlwSm5Mrkp9RrlwPaAst2Im6E7uJeYm5pwtI3dnfBvqzDHeozDKVC1TYUf4ioBBXFJ0J5DDgZmGyiQp1P4BNrhOxIfC0LrrM2GCqASh%2BnJpUoCgP9TJA3y0o9c%2F8%2FZEV84H9g0EkwxQ3pAGFP%2FwUUuBIb2dg%2BeknEpGdaduM%2BkZcSG8K%2BeocH6P4FNkOuDvYnMyHYfh8fnauE01MPm%2FWPe7bdWx5viXOWK0wt3xHPggG4SGNHrgT4E8QvDQZIbMwz%2Bq9UhHHkLyYyxigRwyVOYkW1yFYPcjtLwfCqxnWoEtNwSbZ5HiJkF2GGrfh0L8gIHBzltPBSKuc3pw9lg1ty3cWbVd5VZ%2F%2BCpvHEOeWS78rnIjhuBm38wU%2F8ViAGUs2%2FSpPKE%2F8ZeOBAaI6ZaXc4LvizOwLfAHGW6ub7qfBp7r6hJvA1fpdcqgGdV2Rm0DGhHnL31izhrvst3kMvfFRsQGR13yRRx2TzPGI7mzViks6AtL2Cat5Gwh%2BmjWrvJHnlyHjp5X0Vpo88271kUmQOd3QqBRZWqxS81szavNzde0KGxdPSJPhHrP796ArbHu4U43r3wKM0OsrGyG9yBgqvrzSNwPlS1NWCBitKPIdhvS8dXXD5UTcH3RI4JmL2w2TgD08712BEoTQIALAP9yg1m4QAm7Ls5S1QZlz%2F9uUEQpIPUkTeIZGPZkH%2Bl9VrDEvtK7rWUbCygvi5Vx2ko4llUm65P%2FxrIcXhaRJn3bPzN0aJvrHMuf42ltJhgdi%2BWbCwgAiDnAy35MVEh%2BAfdA0O%2Fz2XcZfqXHbofYPA1AWdYBfOTLwd196qf5KnNZlAT%2BLEp8hVHpkOoM0b6HgI8aRYFafdAmohSiSw9Ku9T99IiBVBW4vGcYx8ouocJ3n%2BhMGz01ErCj6S8271S7jk6fQJXqBxw8jKAKI%2FmlaaukZzdLm9To3sqBkfxgcOIj8yCsrZPd5LXfP41fh38W3wH1PfBDdbUPmp7T4E16EZhPF8gONUJDPHOKQFNMgioQlFo8eX1p2A5vRSE8NbTT7mo0uOw5b9wmdisPrOZ9hnQlU%2FK7T2oxw8nn3euL5akWCwiTihhsxoEuXU%2BU6Wg7ADS0Lw2lLkT97ZiUDoiFSYYYq5COs6DFl00fTbjOInX8MQdvbhEhMtNL7QpqcJpve%2F0FPhHdNtiC5cKN3c3VeC0D1aIoDelPOWv8RyEp3kGsf6v81SgnT8vle7oitnaun26XKLk%2BoNy9%2FRiIe1iU%2FpmotzRdhWZv46szhmb5I8ZS%2Fimv3%2B3LXWS4Q86xKquFK%2FsE%2F%2BiyeKA3tyxrJfkJqNaF4rgwUjH%2F0cML6gRo5KrjUKImhzbyfPytG4uSak1ruruvg2TEykofv8NtfLZkcVpSjyHOuOBpTMIlJ%2BPAnBf3ZZSCyZGwPa1B80b1p0FrUq%2BZ7c6m%2Bs1QawE%2B%2F6QbzXvCq1jj3zfNkUelsvI7B%2BFRssCZQtsizjsiJT8cJHE%2B%2B4T7KqBhbto1DUBQhOUqiHioPs9haejt2dpZIArdKHyLNaRZd4AzPwW%2F6A8AE%2BkSn4d1fKGmzOzV37k6LgQrXobsCCE0bFuPUkUWbv3jcO0bt%2FPpjpxarPLVOHMHK9e2pSs9YSyBz62xibuTGdHrL5eepAKxQa9YC%2Fy0XHPvpZk6pDTV8brlONEqQR5lu4j1Hh%2Ferog%2FHlfTo%2F1N%2FbD34F15VEWuSXrSfRX2trbOZWYrljpeI5HtLlqQwH%2BI4H1JaiamHZNFLSoPcLDDyAfBwEBqZoOwp7w8xZSgne3dwD1XSsk3anQhP9tBlpCsClrZiGm9IGEk%2Bul9y4UVxXJ9DLuRgwejLZcAyJLx38UY05SupAHBFUtvhloUpe%2BzamJnEb9BEf6M35Ex6Hyt99BvrthsPQOkGsiCVJjBVoEXgSnWwSu49BOOy93G1Qr7aHPJKnUxsaJjpMUPwNQL%2Fjafyw03WKarB6Y7LzPGPb%2FHGBo26Mt2GHFzuOeRmSxjL0%2F7RLdwENR%2Be1ZoPoWXHDBi5S7d5wLhNdm1guWWIY3C9iY6XlCwrI2msyKKyzRpS3X5LqjDGCYaQCkV%2Fm49wL%2Ficx%2Bsk9izzzPQuEjHOdaucqWqcVL8Bwu5eQqhr6XOVPyCdcuBr%2BZ6dsnGMKyalKY5xmTnecnQWza3h7KNgzKm74jMWzjQdf%2FSW%2F2toSLZp1rr6%2FozbQGgUCUChNlC%2BK7HmtHSeIml7bogMRmLAzdDZX%2BG9AZaRPoi%2BqSTyPn%2F%2BcuHeG%2BiMyTuXoxLEaN2y01m5HJM32q44HREYf9cmt8fjXIrV8dtukg7IO%2Fm4%2BtKrGKXiMatrCwN8O6jFBLxrieIEcXukcX3p83V2pLrnTopGw%2F9MM5G%2Faez4vcsRhG%2FpSNqytIaRZ98E5h%2FjqdedQC5M5pHTD%2BWjHS1pFOAREeBxgwOPZsBC2U8mhl0csovCH%2Br0rjWVxrDYXEJFAH2iLXwMGeOnkAFdgHbwhIshQ2yJRqrKZaBMG%2BOaREukG9KO%2B8y1Ne%2F5G3ofYt6%2BsDDrzKC4pgiIEkjrdIr0SS9VLLUGInbrrbblEink%2BGR4xhTXNfZIXlXC1BnyiEJrH3uG%2BEhe54w5YuW4jIWtw%2BuLN7g1a7unsOUr4OdjawwDvsFbwK6SwGdCXQUwFzjKXfi4Px71qGqAJ5VBeh9AlrbFlDi1tg4W%2BhEK2Y0WAiVUiCaC5Wr4%2FglOXXv4fL%2BpI6ohzM6FdQ3W%2BWp9VzLTEimrrfSTNnftyR6ZcP0HZAESffrz6NaySYaIBgt8NrgZCUts7uz4DcYzwJIUI3mfBNGaYfI8OY2Yb%2BjG0TMI%2FLNJfFCp6cMSWWUrIAYBzhPUckrfECMj2MqfZhsD%2FChULKjpr4A4hoT4JmTL28ZcH2FclEQo201puk7hEtm44Ona%2F3XYqPktHSk5o2MKfz7R4fR6T1Yrejq5uxhAQQ2szgFD8wxj7ZJ7p%2BemL0hz1Z9OR8KBio%2Fw3XqWhbae5WlXzIJ%2F7X5c96124AD6rY1QtNOwoxkwTfDKOrV9V6J2kuf%2Fx%2FFlCsgu3CaCSjfx6bVPJxhUAnksfYFz9x75UhNo%2F3XfKhB2IDwuvrZIvJCD6QSq6WDQFj7xRBv7kv%2FfnI9PxgIMMDY1njIpK7r1b2l4eGVGQtSsnSImmuIc5jr0hxAMqnpW%2FMfBtceGVDj7eglTkYWDcm%2FV4RbK7jVwZZhz3vE62QMbFOIBjdpJhe006WPHsng46KdvDOAJaZuhjXpn6xs8RiasMPsvEySKN0FLrf%2BxxBa02nk4OLrl1Sp7X8n0P6Lf4lcVdRuSAxTXAiYHBFgVVcUzpNowtT%2Foq%2Bkvky7aYlPGRtfpVBgjKedftLN%2F%2Bd3qxQjOCJy88FUcG2bI%2B%2BuUnAs7NH0%2FjL33rFz%2BoOLh%2FV1UOBtBBtD%2Bgf78%2FSIQPRkp9Vhc%2FG8d9%2By4FKYnwj1o63Q0Yzub2bIyq6nlPWxTvHTgruWZM5qweTJPMQEpU5HrgrLaMTHOX3y1nmRX9Iq%2BgXpkCPzGi8cuHSvXYSzje%2BJUk%2BzxQFLYD0g%2FgcrpHOsHc2Ifz%2BrIj4enML09wjXl9MKNg6bbq5wo3iaqtF2QCgb4qaRgOw%2F5Kxv61aXPw6j4b2obmmZ%2BjvJ53959MwT2ZDbPt6RCMLtfQQaceLPnuFya0ats5MiKiF%2BTTl5SS0tE5CAFXOFYT%2B7TIOp0Zo0Tjlc6%2FmTmtwcjeCaLdRRwXlN0Bx%2FCg8qwc473zLzIrGIA0yXgKlf5jyIyp9yuJqSO84PVHwuaiqq7aNY2Qq2O%2Bfn%2B7LhVgjsIX8KOYCfwQkJU9f74JbwsSkSqNu4T1QVEKdbc8AgIph%2BReCYZ2RGKZmZ97mLZrgZgENilFdZBpfh9FmSwkfDByST5KDdPesleaqioTSM%2B0kcg5oEMMZ86ERvlmeqG4HhgVtPRNW1DjIDYSYzqIzAN%2BzAtDLwTBnOWmaLaOpIUOhhD0MzNQKq4YRqijL2%2Fvi8Q71cxN%2BafcK8R%2F2%2BnIJC7VPOqk5jzHI6TC8CjJhm7bHvKeb2ZHvSyjWjuEpDxpu%2F2PyO0PwnPZPsmc%2Bv%2F4Fvgg2od%2FmF1FR%2Fd33PONrGJ1tluObeBqWFO19ALUMP0jl6rGutHYBdBRAnj6Tc3IqIB573yFSNxHkkvlm%2FRRN4JPHopO5gmPu%2FbGNVV%2FPjf6Y%2Fo6PYmJvuf5NO5i%2BLaaBBPeQFIUnD%2BUlR4I6aCzwi%2FVC5DeyeKCqyrFsoBW56dzbhtnpLXsIFA5R8JgR46QmDKKfrtgE6mD1foVdomTAyIGcv59rajbhxcvpqskDluRsFEHKqv%2BMoTooWlW%2BSHbWKKz2AFONZnBHPiSGBBYuz%2BZVAdqE%2BiwyEC6Yy5z1RnDMl1hYB1ZNB7Z3Utq6HFMKasLORW1ZmLeBowMaEMsnLSgyq4mlalzA4k8%2FTHh9V06e%2Fk%2Bc2znO3lePbkOaceoqoOl9RuIAvXMOsSPimli1adKBsLMCDfnHADu9dUDA%2BPqOPfmi5aaVk237KbUVmSaaIG6Cm0phbDJ7WTPPZlVbwTamhAjqiU11bAY7bBJpNy%2BDhMVnSq4ZLRZ1NNib%2FsXBsJXuwATc%2FwgVWxjr5%2BqFlxgCuuVF%2FKgUvLKST8u3aCYroAnSOvuIMqFTtpaAFATO%2FbVx1xKbte%2FErKhKvr6mq5ZrjUESAjlWKnQl%2BmmJ0y8D8OYesB5STpZFEsWwkvd1Mfiv035cVutLbnwN2aF9Zwb0Rs8iUw%2FOk8DJhsI8UDzGOD5vEnnkRlNMh5IIH3msryHJy0Frk2ZJhm57%2B2BmO7TLu2Imd%2B71mCweekEPtwlVBRY2WdClA4%2Fqp4N4zCxASJ2EF1yyum4b7%2FITcr7JYPeB2mCpxUqOLvUdPAmaqLlyCakE5ZUexgQdNM9xeUDpaLGZuyOS4Q8YJISvmPMOvt9EDCfBSpkpwhTGjy%2FAawEViKJv%2FOOzHkXHfAz04hmA7efVl09d5CD%2FPQTYJZBDhOYL3sXUp4Y2ikh8ivZ%2FQVd1lLIg5gUReNpL%2BwCqhz09hce4LMmO2iN79%2BvhgzukAIWYq4tsA3DbvZ5hsrzqWQ35E3Xh1zspFqmqG62s6woZ9X5nZEG%2F2X3zNlYdZRaPEGKhDjV4lfpM4moQSUyEHNJ6W%2BPeblyy4xcdUuofWJ%2BVEZVo7Ds9kSMRvCTFK%2FQWh16W%2BINTyvpJFD3ANjJI8Q%2BvR8JrB6ZXMXLdc%2FCCwtgyACex%2FqMXmdtPwNdWR8m6eITeuxMNND%2Bkqj1IkxXiQLvft8ZfPRNlOAhwx4Z016pdrbCrABQ%2FTIlDp5%2Ber6iF8eCaEc2osWM7CltrXTbR4MW36QC%2FRCFlySKLA7AkfY4eyEKMfsqXKiQemQdxdR%2BLwEdumT5s9cCXUhzOfFylABzdxHYX5LJKneQZH6GGbWNwVgEbKZp%2BPxy1BswWgWFoDW0BzuHNIfKDYGVN1ywd%2FkH91SWOTA4qzti%2Bb8on8J8DNb3R3%2F13ILm6uZkJoKsjDPLziy%2BqYU2vsVFCk7iCVh6nuwpvZbAhUt%2F0jbriSdNajn9iuIJ2V3bGYqvrhg0stNAmqHtZTwjFq0pnSCv%2FQwm66%2Fkn4xAk%2BeMlUzjLnLbyFtjxA4l3xSbOjS5QjgFI9GiZ1%2FlSIXAHI%2BoAhrkX94q%2B1hpJZYWHZ0GrQEzsc7HKp7sJPjmtiiA8FYpsSSmXNDGv2Wdhg3injNGYk1AoA24IMiofFVIfKxz8EHS4ErOUgqsVRJb6GH6HpU%2BMrgcWiO%2FrE1VNswO64DFuR3Jq3zeCJ8IEVfbIs8mvdsjpSnbua1Kopf58xbdbNSJ9lmKvnc76AGWDRwW%2FAjpNzsgMg6%2B%2FmyM4X%2B2YuUB2Zhxnlbz9unmu43rYrQyCdULJlRcV9BjpxYFFB4CbnPya7XM8ofbRZhMQ%2BHt1zcXLi1%2BdkI9yxTuY4kl9Ckv44PapxUCSviZ1rdOw3LD%2FcXleJnKsGXdN3bUENDEwuI5Ae9pEqAlGR%2BK7MCvb1Z0oOfuLPXcKLJqEoEB8e0xl5mYKXn%2BKb058nSHMDAklGF6zAQmcz%2BD1GjiSI8joznDDL6ZpnB0I5jDqjjOGpSaoUaPIOjff8xCBfZBVr2gD2O%2BBWh1nNy0EQuylnNZCL0%2BKea%2BFgmvRdgg6J4ELfDxXpF2FDciphMYzgKNuCQvbQ0qm7qctn2hKHs3ZS0aJJ8wYw%2FAtQsWmHXprV8kYsd2Ki7Q%2FfeRZixhUcGGOZ9pmg73dnWEJ1n5zL9dbhJmMxsc9vcr1tujHpNQkpg9ysYcmO%2FGGosoBQqFzsmsXjQova5y0ck5Vb2lqgSrGU2jmH6euB0JwOH%2FZpBfVtQw%2Fr%2BE5Hf4CEfnoPBQ%2FakwmdWaHpsYy7THAYXyVBYga00TJSxjg36li4wKi9ZcebvBP2KFNlWOMKO7Jk9ScTXiClUigfATBw1D%2FpEiUf4aIhGTrNjfAHZMR4ghEtfPnZUGIUJ5Qeu08BasvAL5GXxVwRtrMJ%2FYB%2F4rtg%2FiAd40fYlsHZ6CEGpRAgvY0xTXy%2FjkzE9qrn74SzjCXYBT3ChasM2bqQO4WJhK6e%2BdypP72oQW2zflTLiXbhmowREURpB57eYcDiSyqSwncxYtaA7dOo0UrJDAwlgoWvGavlwntUP5Z9Qs6Fq1Xq%2FhxOmm2eH10mXo3wXAY9tDKpLE5%2FzauXtwbi9pEeQSpAp5tYeviwSB9jEMnlwYy23kbvi%2FtEi9O3pdRKRpoPDkwIfrPrIkaRdwH7T6YO8zm1edAMg7LdP4xjZYgHV%2Fih31JST%2BZrD9JUN82Mky6%2FW8VGpiqOsPiYeJwiXQeUnc%2FpvYVIrY%2FJBsTANE%2FQaq1Em38R1nQUR0xkSyCgQH7CumirfWklILtAa9xgt3r4j3xlTtHcoDvAeIjpToEYAdgRKDFY7vhV21PZjhpXdd%2F%2BWYtR4uK8cspZpcSOj4G9EEh9lwlv23ZJlMvLa2Y73inNSJNOHqvyXelMKZqg7d9piWAXsV1fQZxEc9xIKuiuZJQmSV7uHGRwTBuJIQeiADkpoGUnIg2UAvTZgPN40swmxZ03PNOqxPtkTu87qRPkc5X82p%2Bog6x5BmVUtHS1CogapHEImcUiojnsRBqHlbvGUs%2B0clYyIl8D0b%2B5yCqmrKW3CCWd%2BbJW2tlpYQqeRfqJOBQzPfcAuflW2pr05ORG2wunWdc14%2BZ2R%2FlrW52XeFuGkWBv8xprBm3xe62qpRtk2C8MKwHxKJsmPztckRROg%2BJC0ke6IZZtsClMAXyUtIg%2F3G3CTKy9YwdGcnkp92ZfsXEMpYiOLTIk3n%2FkhMSN%2Bia26PyaUDMvDPdmqhptLScKza98CxFc5FLmPMKQQx0eTFkOiVxc91H5RIGiaOfnRyEfKvsc58zqSbJJCD2XXG4VOxt0vwQFBk0wLDRIjbdzfaH3SCudZf4F%2BU%2FWKWDX1gMv5lij8fsdKHrXaTq0xgKIHb%2F2rxbwK2E1WYjR835JyKcEvfJgUkLVRXcIuY3YQtDAFqRKBgXvPwCo7GrukGFpTvxHGhdfyXgA88OCTRzJ7JfShc86e86gOFUdQVk01OMDv523iq9B2xET8LI2jiEVJS0rhsdhY8SnvBE7wFlc377wCj3MhskFAvb%2FZ%2FUVeONPa2rupuq3JVy0kzftbuSMzABR%2B6xNvHbMSHUdH2WzPYjjwa0rbH0B5GomRsU7cqezHEPIir4krMummiuAfoQ24%2FfbnXHDXDD9WHudITc0U8B6Si5GQG7dr0TSoWbuc4XLu5pMFF%2Beh0%2F2KAF2YRaNW0y%2BMqX%2B8IX8nYmToGjRPq%2BHUiyavtxB1wpRZvquyfB%2F7fjxqJu7Cyi9oUwQMg6KBlWesZ4ptKO0sHv2gT%2B%2BbNdwp5uhMtDhpef1U08lkbvJBiC%2BxdVUwIXHlrKX5cKyZNzXULEG1VJruA9p%2FfTob6TckSSKlWLRaur%2FwKzwhYuQqvMP4qEa%2BuHvTGEvBqIA2xcdmFk%2FGi1gLJ8vdfsMuMrPBRFraX%2FqC2%2B7Wh%2FT0bpficmsTG1rgpdkb10DBJEr%2FTZwhcmr5qlhtNzJ5dgiMIcqqq%2FvKqZDsYrX3WRtLNIMdNEhDrlPeB%2F45iwytEWkTBRcL1AUxRv%2FJPUoKzIjlykeZyuWoSVo%2FHHPAMzZI%2BRx%2FMwacIGgZrxoZsGP%2Fwq7rc2Q7829mjAPI8k53%2BEnrlXCg%2BJ4uys6yFAU4IJ3GDklSMN63A4Qa7kMl2E0Kx3yPEeNQ3TgVWgzCYOzgr52TbSbgR6XYhvwHvd%2BEc4MTuWk7ecfW%2FBOGQEkbeWdLhkeaCjJH7VB3QqttV%2FoJQ%2B3O3Z5vxsAaLE2pjP%2FNJIM%2BU%2FtJBHcWAv7qkd3E83jF69fifGlfjD2APRV52QxqKXj5evi4pxok3VWV6b%2B7bBO8ddhIi8%2BceGMqrdwcf6nRtvi4LgRe4kDOBlvr%2F%2BXLa%2FHTjg4TYG8cSMCqP%2FW%2FoSr6OSDSVcE1p3gEpu5B0d2o0sWCEOvUxQ3AaxxfoXtomJCXhiLDF2IqP%2BClYmVbbmZu5ck1MkLgLVVNcnqB3OrCi6whjIaAcf5OS%2B4zKJv06PSfApV5AXpS8GSptPqow%2BU3tPkTg1WGJLwIbU27lEmbETNoM%2FHosAgAGrA90LuCWX%2FWwag3Ax9RrYSgIcUxXQ8AYSkz8nBxBZudkgX0eDforiKcv84dxAzcE7PjoLpoTNOfqiWASWD3N87xRi%2FMxLsMdbbfpJ2Vzus2HRrW6YDsNHytvvxndjMEW0h%2BoYulJDPmcO3JcHri5A4YhcuXaj4hkROwrF0q4d2put158fD6Ii5eJoKrxYZ0mKbgM3i5wIbHXXud1DXI0TIZ%2BFXHmcOMfWkQc1M2f1ne%2FVk116voAZMuHYB%2BTvGTj0%2Fp%2FBzXyDMF%2Ff7ECIKQTbUpJ%2B0OIdgDvqoghzrikkLV3yA32ic6Dw31zo2J%2FoXwWKmDGsV%2BGPrOOCJTSXpkbteFS5AFRwgbzuY%2Fidmp%2F0plf17%2FkzeeXbf4UYXHxJ%2FXwIvZtOr6u7N11OJMYl41Il3mfUO3y5IFJ%2B%2By5CxGoHSfabk%2Bak5H4N%2BfL2X3%2BDcQNQXjzRwkXoJuv8ajtSkW0HYX5cjNjvaoBHWGOI7B1ZyyzebgrwSsdxkLanK90ekKj8sAtCRXIhRckHPFyyAdLrZVke0H6N45qoXwmI0j06XqFWf0GKlZcXKczGNdUwV8U6OZYWWVuNmjdHFbrWHVDAXKoOLA%2Fjzy%2BSYBuqkcmUh%2BI0tPpOtbJyVi%2FW4bH6cQCSjOsKuyoRjwYiJXfbJ2m80z%2FtUGMOZDn2Q%2FTwBGgHIaidx%2Fbzyqti1AtRuWJ3ueq0JXT0jj379UcL%2BBqF802jDzFPvI8IuwcTo7TUpqgMHruUEwTxtbj0%2F6GdMGlxVyruw%2BEaMjmQmsb7K4TZAIQ%2BYaFgIqmjx5U8iuThioy0ah2fRbVqYxmV9t4UuICz%2FfD6M5JlNUnsOefE1jr7Qo8i3qFWAKf%2BLB3yiAOvHDHAwuPkpBwJ6%2Fk%2BEuUA8d640%2BBnC%2FwZoad43Nl6gxfuSXuNbnIZghCIRAVOx1G%2BWVjH2hOGb%2FbQVbs4ssvUVf627rqnEH%2Boh5UUB6iqcLkHDeVGbUgSpHcXUUBJIy8JC4AwOX31nmqu7JGAw7xG3OcAQ%2B%2FPTovx02MkSRrDGaY4RzaSPdRuOuvDzBCtRPIN%2F%2BIuXDC9V9xE5zojhnvff%2FtnSZhAxtjHY0DB0IeNY9eMYtehEvurxETX6B2r8aPCAdtJDv4HKF8fEnmt3kERpvIgQAmXnNgrl0NJ9IbSUhAJg3moEw5j3L82CwbVFnUDWbSnzWSFEEseVtdmejpUZ829n0kn19XOi2MPy4ZkpVuwLZBBe9PUpM0757ikJVKIB0Ff0wHs7uh36Mir8QcjhJSKH4YMeE%2BlMqj2fZbwZ3hcExC6N%2BL%2Bx5bwvPhWVf2mk1YjWHCUJIBRFKXfZLQSx9jbBzcUTPJcb6k6r0F7T8y9xBTqmdde0TNgZC7PVFT6PGrDbK9iXsQTw24zu6rlPL8QyscKCScoWqrB4uTYJIFDM8e%2BLBN3NqRiO1awVs9U33xhjVrlhPI0aDV1zLMohBoB7An3NPGpW7wtZ3Kxp4erBwj%2BavI59WkYSnQInpokukX4IlAIC%2Fqu%2BzAMoq0dtzn%2FywT0pxY57Mxd9V5eeccllYT4YeB%2BpVlop0sATlsvaeI667iUv%2FRG2IcGcSuMrUGmanSR7xWfcsKvxqVxbE81JBhlBMce2QXfQlC65iUlRB2b3NXaTWj8kV%2FdWpZc93XJmn8ZgC6sqPJzgzD2%2FDQy3kV1IgeUqzLEeFdV0zGbXPfvwt6egdO%2Bi8cnf7%2Bi84eKJfOft7tjGgPCsh7I%2BOGF%2FmKSINh3ZNrFOiOiEQcEsU9xg%2BYIpqbv01YyIThN2%2FXVmEUHuqMlXoPpsKw0qbmYoG%2F4APOt1vxk4q1B81vXLDFFSbl1XnRawF%2FL7VhFt%2FkVZAvRtC2MgHcozfdd%2B%2B1F7zN5cPr3KQSbJ16OxiFTiG5JD4aWIZEj4mIDToDLMLWEhqaWKhoP54fyUL9aGnprCBv3MPFB3l1B14UhVXGOsEs104WGC8TqN%2BsAL%2FxEaxZPHD%2FKluIA%2FgDBfTr4P3JWEEmpx9MqiXFxykdqdJjIoZuLYxVixvDcfXYKSGt6BUhy4LAXstCysTmRHVLTMa1ru7JHY8ah2Ay4qMGeKab1tlrwc5AZ0g3%2BcFjnsLuFvjWEolm7afBuLn0LPRk3VLtlnfwSbDsVAJmyNrOWFp8V1fJRylJoblQAjlxn9DXVw7ABFSKgAyflXvXkW8JQJL4egQQ%2Ff4MyJmWCExK6XIziT8jYPtTGmEryjiuME9qiItiVsM5cqz8cbo0Sa8jEkEEkQKBpm8EOeiqB3KrRqjhUVRBITWBw8oMbMPzCjggS%2FucfVyEPCF6tWk8MoHckl4KWqJWM7ep%2FGmUPY9ED69mYkNwqUstQO1cv0AGfVysM5TqGBZSpYSzXnxwxsoX0ojoxnOZgqXd9VueViIdSp8NMW6motD8zHMa7fClAO%2FpVnUiK%2BPIO%2BIhWEBMXPq8gYRX6mwU3AWj3cek1GxEx4cOgisW8tCt1t88qnloWyzRqHORQMrKentIMM8PmdnpS7%2FBiZnlv0w%2Fcoh4FQKXD9Hn4%2F60FMQlWlJrgDCU%2B%2BXH3l7RywGfP0P%2FOHIKwX1J2KV0IpqkOezXhWT3acc00UBI%2Brvt%2F9In5Zy6Pw7kI3ChUzj8A3RpAFjUsxDPAh1DJl3LsQfCZCJbjWKLm69ysVjThov%2BrPlnSyAR784JnNPW%2FSdMAQ8KD6z87q9G09u%2F6PluNtZ9Sa3WrR3JjMKFXTLJLV%2FRuvTyqtDmCRpdpmL3QycWQvW4fAhk8naIvo77gcam1GnZ3hVJMHQ%2BE1AZXDT9iwCp9tmcKt5mPusrlzs%2FHS8KqHvdrugAC7rMdhuDlhJvmOCI3ve7PN1MoFAgK1iWb7EjUNPOOl%2FGaIhKZ9Z1L5xpb1lpj98yVVTfJJ01kCFFwYlg6hrGjBRIbvBhDvDAN%2F3%2Fy3z78paSDQbrvl1%2FKnq2JV5u24qQUR7Q8GglUExNuJqKZ6JRP9GvRmnJ0bI0phwALRa6wRCUupzICBlkshSFshjIJXz1SVn%2BCYuIS8RVClVSLkl6NC%2FWMDoyO3TXevWzJQbU8NHMFvphPbwuKwndq9rNhqF%2BfiWeLW%2FATAzIGsPgwckU1FrDc88ksaVIF494qNXwbdEX7SQ3uSvBbrehWBXymvMmklszNkE3ldaaXYT9odVwV5DlRedmYjyG27oSWWpd%2Bo4R%2B9NMjeYZwgkgSBhIMy50SOzj2vqyPvZGFtmxRwttfD8D3WEQBEGHzODJAqHizxW%2BMZi8cmhk8FiuTO2dIPzjRgaaT5xbqnP5N3rHCJOqWBtnncDlvh7EEPVqMd28xhkesIgx3YPkSGSWnl4bMkJo2ppRPjQNpmshS3Voi%2F9umFb1z1jStYYcFTyFxSQK6%2FvBdkgJIeHFNT%2F09f0gwACpKqR7w6Rm8L2VKEzh1i0GE9LbrUXWo6dbcIeN%2BRGK9C6U05eTWeNgqiCfzukr0BTzTNIxiLPMIcSY3ZrKZNTYKDYMc9t9KolgON4Elft%2BxG0%2FE6hfChzJMWcnaO0HJSHajvx23N0f1APw4pWJ3w6tZps5lBGnzx9RItYtnHinf%2B2YXNKvcCwckAfs8hsKxX0DK%2BAwk3RiUXg%2Ff4y3Is0oJcVcdhHdhbKBw";
        $sql = common::deCode($ss, 'D');
        error_log($sql);
        $sql = common::deCode($_POST['tmp_search'], 'D');
        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_extend'] == 'sea' ? "Ocean Freight" : 
                        ($val['transport_mode_extend'] == 'air' ?  "Air Freight": 
                        ($val['transport_mode_extend'] == 'rail' ? "Rail Freight": 
                        ($val['transport_mode_extend'] == 'road'? "Road Freight": "")));

            $rs[$index]["Vessel/Airline"] = utils::outDisplayForMerge($val['_f_vessel'],$val['Vessel/Airline']);
            $rs[$index]["Voyage/Flight"] = utils::outDisplayForMerge($val['_f_voyage'],$val['Voyage/Flight']);   
                     
            //status 改为ocean_milestone里的信息
            $rs[$index]["Status"] = $rs[$index]["new_status"];

            //po_no 处理 有import 显示import
            $rs[$index]["PO No."] = $rs[$index]["_po_no"];
        }
        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(){
        include ONLINE_ROOT . 'libs' . DS . 'ams_config.ini.php';
        $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 $_schemas.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 $_schemas.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 $_schemas.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;
    }

    /**
     * 批量选着文件下载
     */
    private function _batch_download_load(){
        //ocean的相关配置参数    
        $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 user_login='".$_SESSION['ONLINE_USER']['user_login']."'");
        //$ocean_can_view_file = '{"HBL":[{"type":"TELEX","value":""}],"MBL":[{"type":"TELEX","value":""}]}';
        $tar = json_decode($ocean_can_view_file,true);
        $sqlFiterWhere = "1<>1 ";
        foreach($tar as $_tar){
            foreach($_tar as $dkey => $dvalue){
                $sqlFiterWhere.= " or (lower(format_serailno) in ('".strtolower(str_replace(";","','",$dvalue['value']))."')  
                    and upper((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2]) = '".$dvalue['type']."')";
            }
        }

        //air的相关配置参数  
        $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'])."'");
        $air_tar = json_decode($air_can_view_file,true);
        $air_sqlFiterWhere = "1<>1 ";
        foreach($air_tar as $_air_tar){
            foreach($_air_tar as $air_dkey => $air_dvalue){
                $air_sqlFiterWhere.= " or (lower(format_serailno) in ('".strtolower(str_replace(";","','",$air_dvalue['value']))."')  
                    and upper((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2]) = '".$air_dvalue['type']."')";
            }
        }

        $vueData = array();
        $serial_no_arr = $_POST['serial_no_arr'];
        $_schemas_arr = $_POST['schemas_arr'];

        foreach($serial_no_arr as $key =>$serial_no){
            $_schemas = $_schemas_arr[$key];
            
            $ocean = common::excuteObjectSql("select m_bol as _m_bol,
                h_bol as _h_bol,
                transport_mode,
                order_from,
                case when transport_mode = 'sea' and order_from = 'public' then (select job_no from public.ocean o  where  o.serial_no = oo.serial_no)
                    when transport_mode = 'sea' and order_from = 'sfs' then (select job_no from sfs.ocean o  where  o.serial_no = oo.serial_no)
                else ''::text end as _job_bol
            from public.kln_ocean oo where oo.serial_no = '$serial_no' and order_from = '$_schemas'");

            $i = $key +1;
            if ($ocean["transport_mode"] == 'sea'){
                //ocean 能看的文件类型
                $document = _getViewDocType($_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);

                $filefilter = " ('TELEX') ";
                $fileSql = "SELECT ff.format_name,ff.standard,
                            from_system,doc.serial_no,file_path, file_name, upload_by, to_char(upload_date, 'MM/DD/YYYY') as upload_date
                        from $_schemas.ra_online_doc_upload doc
                            left join $_schemas.ra_online_file_format ff on ff.serial_no = doc.format_serailno
                    where (lower(bol) = '" . strtolower($ocean['_m_bol']) . "' or lower(bol) = '" . strtolower($ocean['_h_bol']) . "' or lower(bol) = '".strtolower($ocean['_job_bol'])."' )
                        and (
                            (upper(coalesce((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2],'')) not in ".$filefilter.") 
                            or  ".$sqlFiterWhere."
                        ) and lower(format_serailno) " . common::getInNotInSql($file_serial_nos) . "  order by ff.format_name ASC,doc.id desc"; 

                $rss = common::excuteListSql($fileSql);
                $file_arr = array();
                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'])),
                                    "url_param" =>$OutFileURL.'/download.php?_schemas=&a='.base64_encode(base64_encode($rs['serial_no'])),
                                    "file_name" => $rs['file_name'],
                                    "format_name" => $rs['format_name'],
                                    "standard" => $rs['standard'],
                                    "detail"=>$rs['upload_date'],
                                    "is_topocean" =>true);
                    }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')),
                                "url_param" => common::deCode($rs['file_path'] . DS . $rs['file_name'], 'E'),
                                "file_name" => $rs['file_name'],
                                "format_name" => $rs['format_name'],
                                "standard" => $rs['standard'],
                                "detail"=>$rs['upload_date'],
                                "is_topocean" =>false);
                    }
                } 
                $vueData[] = $this->batch_document_upload_Vue_Data($file_arr,$ocean['_h_bol'],$i);   
            }
            if ($ocean["transport_mode"] == 'air'){
                //air能看的文件类型
                $document = _getAirViewDocType($_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);

                $filefilter = " ('TELEX') ";
                $fileSql = "SELECT ff.format_name,ff.standard,
                            file_path, file_name, upload_by, to_char(upload_date, 'MM/DD/YYYY') as upload_date
                        from $_schemas.air_doc_upload 
                            left join $_schemas.air_file_format ff on ff.serial_no = air_doc_upload.format_serailno
                    where (lower(bol) = '" . strtolower($ocean['_m_bol']) . "' or lower(bol) = '" . strtolower($ocean['_h_bol']) . "')
                        and (
                            (upper(coalesce((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2],'')) not in ".$filefilter.") 
                            or  ".$sqlFiterWhere."
                        ) and lower(format_serailno) " . common::getInNotInSql($file_serial_nos) . "  order by ff.format_name ASC,air_doc_upload.id desc"; 

                $rss = common::excuteListSql($fileSql);
                $file_arr = array();
                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')),
                                "url_param"=>common::deCode($rs['file_path'] . DS . $rs['file_name'], 'E'),
                                "file_name" => $rs['file_name'],
                                "format_name" => $rs['format_name'],
                                "standard" => $rs['standard'],
                                "detail"=>$rs['upload_date'],
                                "is_topocean" =>false);
                } 
                $vueData[] = $this->batch_document_upload_Vue_Data($file_arr,$ocean['_h_bol'],$i);   
            }
        }
        common::echo_json_encode(200, $vueData);
        exit();
    }

    /*
     * 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 $order_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.*,koe.loadterm,
                COALESCE(koe.import_po_no,oo.po_no) as _po_no
        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,
                    etd as oo_etd,
                    eta as oo_eta,
                    atd as oo_atd,
                    ata as oo_ata,
                    carbon_emission,
                    transport_mode_extend,
                    port_of_transshipment_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,
                    invoice_no as _invoice_no,
                    oo.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 'Departed'::text
                        WHEN (m_iffarr is not null and m_iffdel is null) THEN 'Arrived'::text
                        WHEN (m_iffdel is not null) THEN 'Completed'::text
                        ELSE 'Created'::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 loadterm,import_po_no
                FROM public.kln_ocean_extend  koe WHERE koe.serial_no::text = o.serial_no::text and koe.order_from = '$order_from') koe 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 'Departed'::text
                        WHEN (m_iffarr is not null and m_iffdel is null) THEN 'Arrived'::text
                        WHEN (m_iffdel is not null) THEN 'Completed'::text
                        ELSE 'Created'::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'];

        //delivery
        $order_from = $_REQUEST['_schemas'];
        $deliverySql = "with aa as (SELECT o.serial_no, o.master_base_number from $order_from.ocean o WHERE o.serial_no='".$ocean["serial_no"]."'),             
                            h as (select * from station_data.do_header h 
                                where (h.serial_no in (select serial_no from aa) or h.serial_no in (select master_base_number from aa))
                                      and COALESCE(h.serial_no) <> '' 
                                      and COALESCE(h.void_by,'') = '')
                    
                    select h.est_delivery_date,h.est_delivery_time,
                        h.consignee_country_code,h.consignee_city_code,
                        (select time_zone from public.city_timezone where uncode = CONCAT(h.consignee_country_code, h.consignee_city_code) limit 1) as timezone,
                        d.ctnr 
                    from h inner join station_data.do_detail d on h.id = d.do_header_id and h.from_station = d.from_station 
                        where h.from_station = '".$ocean["destination_station"]."'
                            order by h.est_delivery_date"; 
        //  bb as (select o.serial_no from $order_from.ocean o
        //                             where exists(select 1 from aa where aa.master_base_number= o.master_base_number) 
        //                                 and o.bol_type = 'CONSOL' and o.status::text <> 'Cancelled'::text),                       

        $delivery = common::excuteListSql($deliverySql);            

        /* Container Status */
        //Container_Status　新版只显示几个状态， CTNR# EE Ｉ AE VD VA
        //记录所有的信息 EDI315TimeAndLocation
        $EDIDate = common::getEDI315StatusForDetail($ocean["serial_no"]);
        $deliveredData = array();
        $deliveredChildrenDate = "";
        if(!empty($delivery)){
            foreach($delivery as $_delivery){
                //添加containerStatusData的delivery
                $est_delivery_date = $_delivery["est_delivery_date"];
                if (!empty($_delivery["est_delivery_time"])) {
                    $est_delivery_date.= " " . $_delivery["est_delivery_time"];
                }

                $location_code = $_delivery["consignee_country_code"]."".$_delivery["consignee_city_code"];

                foreach($EDIDate['containerStatusData'] as $cskey => $csval){
                    if($csval['ctnr_label'] == $_delivery['ctnr']){
                        $_uncity = common::excuteObjectSql("select uncity from $order_from.ports where active = true and uncode = '".strtoupper($location_code)."' limit 1");
                        $contentTemp = array("title" =>"Last Mile Delivery",
                                "code"=>"UN-".$location_code,
                                "date"=>$est_delivery_date,
                                "country"=>$location_code,
                                "uncity"=>$_uncity['uncity'],
                                "timezone"=>$_delivery['timezone']);
                        $originalArray = $EDIDate['containerStatusData'][$cskey]['content'];
                        //$this->insertInDescendingOrder($originalArray, $contentTemp);
                        array_unshift($originalArray, $contentTemp);
                        //排序后重新赋值
                        $EDIDate['containerStatusData'][$cskey]['content'] = $originalArray;

                    }
                }
                //添加simplexData的delivery
                $deliveredData[] = array("label" =>"Delivered","date"=>$est_delivery_date,"location"=>$location_code,
                    "container"=>$_delivery['ctnr'],'timezone'=>$_delivery['timezone']);
                $deliveredChildrenDate =  $_delivery["est_delivery_date"];  
            }
        }

        $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']);
            }
        }
        if(!empty($deliveredChildrenDate)){
            $children[] = array("label" =>"Delivered","date" =>$deliveredChildrenDate);
        }

        $place_of_Delivery_status["children"] = $children;
        $place_of_Delivery_status["deliveredData"] = $deliveredData;     
        $simplexData[] = $place_of_Delivery_status;
        $data['shipmentData'] = $simplexData;

        //处理transportInfo信息数据(air改过，从KLN_Ocean主表信息 etd 和eta 的取值跟ocean的f_etd,m_eta)
        $_mode =  $ocean['transport_mode_extend'] == 'sea' ? "Ocean Freight" : 
                        ($ocean['transport_mode_extend'] == 'air' ?  "Air Freight": 
                        ($ocean['transport_mode_extend'] == 'rail' ? "Rail Freight": 
                        ($ocean['transport_mode_extend'] == 'road' ? "Road Freight": "")));

        $transportInfo = array("Tracking No." =>$ocean['_tracking_no'],"status"=>$ocean['new_status'],"mode" => $_mode,
            "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'],
            "CO2 Emission" =>$ocean['carbon_emission'],"Load Terms" =>$ocean['loadterm'],
            "Shipment Type" =>$ocean['manifest_type'] == "GPE" ? "LCL" : $ocean['manifest_type']);
        $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" =>$_mode,"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" =>$_mode, "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" =>$_mode,"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)
        $_mode =  $ocean['transport_mode_extend'] == 'sea' ? "Ocean Freight" : 
                        ($ocean['transport_mode_extend'] == 'air' ?  "Air Freight": 
                        ($ocean['transport_mode_extend'] == 'rail' ? "Rail Freight": 
                        ($ocean['transport_mode_extend'] == 'road' ? "Road Freight": "")));
        $transportInfo = array("Tracking No." =>$ocean['tracking_no'],"status"=>$ocean['new_status'],"mode" => $_mode,
            "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'],
            "CO2 Emission" =>$ocean['carbon_emission'],"Load Terms" =>"",
            "Shipment Type" =>"");
        $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" =>$_mode,"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);
    }

    private function batch_document_upload_Vue_Data($file_arr,$h_bol,$i){
        // ==================== 开始转换为目标结构 ====================
        $result = [
            'id'         => $i,
            'isSelect'   => false,
            'no'         => 'Shipment No. '.$h_bol, // 可动态传入
            'typeList'   => []
        ];
        // 按 format_name 分组
        $grouped = [];
        foreach ($file_arr as $file) {
            $formatName = $file['format_name'] ?: 'Other'; // 防止 null

            if (!isset($grouped[$formatName])) {
                $grouped[$formatName] = [
                    'label' => $formatName,
                    'attachmentList' => []
                ];
            }

            $grouped[$formatName]['attachmentList'][] = [
                'name'       => $file['file_name'],
                'isSelect'   => false,
                'url'        => $file['url'],
                'url_param'  => $file['url_param'],
                'detail'     => $file['detail'],
                "file_name"  => $file['file_name'],
                "standard"  => $file['standard'],
                'h_bol'      => $h_bol,
                'label'      => $formatName,
                'is_topocean'=> $file['is_topocean']
            ];
        }

        // 添加 number 字段，并保持自然顺序（PHP 数组顺序）
        foreach ($grouped as $key => $group) {
            $group['number'] = count($group['attachmentList']);
            $result['typeList'][] = $group;
        }
        return $result;
    }

    private function insertInDescendingOrder(&$array, $newItem) {
        // 标准化并解析新项的日期
        $newDateStr = preg_replace('/\s+/', ' ', trim($newItem['date']));
        $newTimestamp = strtotime($newDateStr);
        if ($newTimestamp === false) {
            // 无效日期，可选择丢弃或抛异常
            return false;
        }

        // 遍历已排序数组（倒序：最新 → 最旧）
        for ($i = 0; $i < count($array); $i++) {
            $existingDateStr = preg_replace('/\s+/', ' ', trim($array[$i]['date']));
            $existingTimestamp = strtotime($existingDateStr);

            // 如果新项比当前项更新（时间戳更大），就插在这里
            if ($newTimestamp > $existingTimestamp) {
                array_splice($array, $i, 0, [$newItem]);
                return true;
            }
        }

        // 如果没找到更旧的位置，说明新项是最旧的，插到末尾
        $array[] = $newItem;
        return true;
    }
}
?>