k_online_backend/service/ocean_booking.class.php

<?php

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

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

    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_booking() {
        $operate = utils::_get('operate');
        $operate = strtolower($operate);
        /*
         * index page
         */
        if (empty($operate)) {
            $data = array();
            if (_isCustomerLogin())
                $data['is_customer'] = "yes";
            else
                $data['is_customer'] = "no";
            //栏位信息
            $column = column::getInstance()->getDisplayColumn('Booking_Search');
            $BookingTableColumns = column::getInstance()->tableColumns('Booking_Search',$column);
            $data['BookingTableColumns'] = $BookingTableColumns;
            common::echo_json_encode(200,$data);
            exit();
        }
        /*
         * ocean order search
         */
        if ($operate == "search") {
            $this->_booking_search();
        }
        if ($operate == "setting_display") {
            column::getInstance()->settingDisplay('Booking_Search', 'main_new_version.php?action=ocean_booking');
        }
        if ($operate == "detail") {
            $serial_no = common::deCode($_GET['a'], 'D');
            $transport_mode = common::excuteOneSql("SELECT transport_mode FROM public.kln_booking ob WHERE  ob.serial_no = '$serial_no'");
            if ($transport_mode == "sea"){
                $this->_booking_detail();
            } elseif ($transport_mode == "air"){
                $this->_air_booking_detail();
            } else {
                $this->_booking_detail();
            }
        }
        if ($operate == "excel") {
            $this->_booking_excel();
        }
        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();
            }
        }
    }

    private function _booking_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;
        }
        $sqlWhere = ' where ' . common::searchExtendHand_KLN("booking", $_SESSION["ONLINE_USER"]);
        $sqlWhere .= search::getInstance()->getSearchSQL("Booking_Search");

        //移除掉全文检索 但保留代码
        // 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)],''))
                or lower(po_no) like '%" . strtolower(common::check_input($_POST["_textSearch"])) . "%')"; 
        } 
        //为了移除filterTag条件
        $sqlWhere_befrom_filterTag  = $sqlWhere;

        $filterTag_param = "";
        $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)";
        //前端提交是数组
        if (!empty($_POST['filterTag']) && utils::count($_POST['filterTag']) < 4) {
            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, "Confirmed") !== FALSE) {
                $_sqlwhere .= " or (bol_type != 'BOOKING')";
                $filterTag_param .= " or (bol_type != 'BOOKING')";
            }
            if (stripos($filterTag, "Cancelled") !== FALSE) {
                $_sqlwhere .= " or (status='Cancelled')";
                $filterTag_param .= " or (status='Cancelled')";
            }
            if (stripos($filterTag, "Created") !== FALSE) {
                $_sqlwhere .= " or (bol_type = 'BOOKING' and status!='Cancelled')";
                $filterTag_param .= " or (bol_type = 'BOOKING' and status!='Cancelled')";
            }
            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_all_status = "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 (1<>1 or ($mode_param_search and bol_type = 'BOOKING' and status!='Cancelled')) then 1 else 0 end) as creatd,
                sum(case when (1<>1 or ($mode_param_search and bol_type != 'BOOKING')) then 1 else 0 end) as confirmed,
                sum(case when (1<>1 or ($mode_param_search and status='Cancelled')) then 1 else 0 end) as concelled
            from public.kln_booking" . $sqlWhere_befrom_filterTag;

            error_log($sql_all_status );

            //$sql_all_status_data = common::excuteObjectSql($sql_all_status);
            $sql_all_status_data =  $mapdb->GetRow($sql_all_status) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $sql_all_status), 0));

            $seach_rc = $sql_all_status_data['seach_rc'];
            $rc = $sql_all_status_data['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'];
            $Creatd = $sql_all_status_data['creatd'];
            $Confirmed = $sql_all_status_data['confirmed'];
            $Concelled = $sql_all_status_data['concelled'];

            //前端数据返回，不管有无数据
            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($Creatd),"type"=>"created","checked"=>utils::in_array('Created', $_POST["filterTag"])? true : false),
                array("name"=>"Confirmed","number"=>intval($Confirmed),"type"=>"confirmed","checked"=>utils::in_array('Confirmed', $_POST["filterTag"])? true : false),
                array("name"=>"Cancelled","number"=>intval($Concelled),"type"=>"cancelled","checked"=>utils::in_array('Cancelled', $_POST["filterTag"])? true : false));
            }else{
                //初始，前端有选择着带入选择
                $tagsList = array(array("name"=>"All","number"=>intval($rc),"type"=>"all","checked"=>true),
                array("name"=>"Created","number"=>intval($Creatd),"type"=>"created","checked"=>false),
                array("name"=>"Confirmed","number"=>intval($Confirmed),"type"=>"confirmed","checked"=>false),
                array("name"=>"Cancelled","number"=>intval($Concelled),"type"=>"cancelled","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('Booking_Search');
            $allBookingColumns = column::getInstance()->tableColumns('Booking_Search',$allColumn);
        }
        $tp = ceil($rc / $ps);
        if ($rc > 0 ) {
            $order_by = " f_etd desc";
            //TopOcean的不用考虑
            $ocean_dest_sql = "";
            //合并显示两个特殊字段 voyage_m_voyage/vessel_m_vessel
            $sql = "SELECT order_from as _schemas, serial_no as __serial_no, 
                        color,transport_mode,transport_mode_extend, bol_type, m_voyage as _m_voyage, m_vessel as _m_vessel,f_eta as _f_eta," .
                        column::getInstance()->getSearchSqlForDisplay('Booking_Search') . " from public.kln_booking $ocean_dest_sql " . $sqlWhere . " order by $order_by limit " . $ps . " offset " . ($cp - 1) * $ps;
            
            //$rs = common::excuteListSql($sql);
            $rs =  $mapdb->GetAll($sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $sql), 0));
            error_log("online_booking_search_SQL: ".$sql);

            //对查询的结果做特殊处理，比如要拼接某个值，合并值等
            foreach($rs as $index => $val) {
                //合并显示 vessel
                if(array_key_exists("f_vessel", $val)){
                    $rs[$index]["f_vessel"] = utils::outDisplayForMerge($val['f_vessel'],$val['_m_vessel']);
                }
                //合并显示 voyage
                if(array_key_exists("f_voyage", $val)){
                    $rs[$index]["f_voyage"] = utils::outDisplayForMerge($val['f_voyage'],$val['_m_voyage']);
                }

                //显示 eta
                if(array_key_exists("m_eta", $val)){
                    $rs[$index]["m_eta"] = !empty($val['m_eta']) ? $val['m_eta'] : $val['_f_eta'];
                }

                //返回加密serial_no
                $rs[$index]["__serial_no"] = common::deCode($val['__serial_no'], 'E');

                //按新逻辑处理status,现在是管理员权限，客户权限少了一种状态，先设置默认值防止出错
                // $status = "Created";
                // if ($val['color'] == "FF00FF00" || $val['color'] == "FF0000FF"){
                //     $status = "Confirmed";
                // }
                // if ($val['color'] == "FFFF0000"){
                //     $status = "Cancelled";
                // }
                // $rs[$index]["Status"] = $status;
                if($val['bol_type'] == 'BOOKING' and $val['Status']!='Cancelled'){
                    $status = "Created";
                }
                if ($val['bol_type'] != 'BOOKING'){
                    $status = "Confirmed";
                }
                if ($val['Status']=='Cancelled'){
                    $status = "Cancelled";
                }
                $rs[$index]["Status"] = $status;

                $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": "")));;
            }
            
            $arrTmp = array('searchData' => $rs, 
                'tagsList' => $tagsList,
                'TransportList' => $TransportList,
                'allColums' => $allBookingColumns,
                'rc' => $seach_rc,
                'ps' => $ps,
                'cp' => $cp,
                'tp' => $tp,
                'tmp_search' => common::deCode($sql, 'E'),
                'type' => common::check_input($_POST ['_ntype']));
        } else {
            $arrTmp = array('searchData' => array(), 
                'tagsList' => $tagsList,
                'TransportList' => $TransportList,
                'allColums' => $allBookingColumns,
                'rc' => $seach_rc,
                'ps' => $ps,
                'cp' => $cp,
                'tp' => $tp,
                'tmp_search' => "",
                'type' => common::check_input($_POST ['_ntype']));
        }

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

    private function _booking_detail() {
        $serial_no = common::deCode($_GET['a'], 'D');
        $status = $_GET['status'];
        $_schemas = $_GET['_schemas'];
        if($_schemas == 'public'){
            $_schemas = "ocean";
        }
        $sql = "with o as(
            SELECT  o.*,'' as incoterms from ocean o where serial_no = '" .$serial_no . "'
        )
        SELECT o.booking_no as _booking_no,
            o.* ,sh.*, cn.* ,aa.*,dd.*,ob.*
        from o
            LEFT JOIN LATERAL ( SELECT shippr_uncode,shipper_city,
                consignee_uncode,consignee_city,
                place_of_receipt_uncode,place_of_delivery_uncode,
                fport_of_loading_uncode,mport_of_discharge_uncode,po_no as _po_no,transport_mode,transport_mode_extend
            FROM public.kln_booking ob WHERE o.serial_no::text = ob.serial_no::text) ob 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
            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
            FROM $_schemas.contacts c WHERE o.destination_station::text = c.contact_id::text) dd ON true";
        $ocean = common::excuteObjectSql($sql);

        error_log($sql);

        $vueData = $this->returnBookingDetailData($ocean,$status);

        //处理transportInfo信息数据
        $transportInfo = $vueData["transportInfo"];
        //处理basicInfo信息数据
        $basicInfo = $vueData["basicInfo"];
        //处理 拼接地址 ocean表单exp 字段无法精准分割电话和地址信息，只能从contacts表里查询
        $businessPartners = $vueData["businessPartners"];
        $marksAndDescription = $vueData["marksAndDescription"];
        //处理ocean_reference
        $ref_no = array();
        $ref_arr = common::excuteListSql("select * from ocean_reference where lower(serial_no) = '" . strtolower($serial_no) . "'");
        if(!empty($ref_arr)){
            foreach($ref_arr as  $ref) {
                $ref_no[] = array("label"=>$ref["ref_code"],"value"=>$ref["ref_value"]);
            }
        }
      
        //处理返回booking的 Container 信息数据
        $booking_container_column = array();
        $container_column = column::getInstance()->getDisplayColumnAll('Booking_Container');
        $booking_Container_config = array("QTY" => array("formatter"=>"number","digits"=>0),
                "KGS" => array("formatter"=>"number","digits"=>3),
                "CBM" => array("formatter"=>"number","digits"=>3));
        foreach($container_column as $cc){
            if(!empty($booking_Container_config[$cc['name']])){
                $booking_container_column[] =array("field" =>$cc['database_column_name'],"title" =>$cc['name'],
                "formatter" =>$booking_Container_config[$cc['name']]["formatter"],
                "digits" =>$booking_Container_config[$cc['name']]["digits"],
                "type" =>"normal");
            }else{
                $booking_container_column[] =array("field" =>$cc['database_column_name'],"title" =>$cc['name'],
                "formatter" =>"",
                "digits" =>"",
                "type" =>"normal");
            }
            
        }
        if (!empty($ocean['_booking_no'])) {
            $sql = "SELECT " . column::getInstance()->getSearchSql('Booking_Container') . " from ocean_booking_container where lower(serial_no) = '" . strtolower($serial_no) . "'";
            $rss = common::excuteListSql($sql);
        } else {
            $sql = "SELECT " . column::getInstance()->getSearchSql('Booking_Container') . " from oc_container_booking_v where lower(serial_no) = '" . strtolower($serial_no) . "'";
            $rss = common::excuteListSql($sql);
        }
        $containers = array("container_column"=>$booking_container_column,"container_data" =>$rss);

        //packing
        $quantity_unit = array();
        $packages = "CTN";
        $g_weight_tolal = 0;
        $ch_weight_tolal = 0;
        $cbm_tolal = 0;
        foreach($rss as $pk => $pv){
            //相同的unit 的合并成一个
            $unit = $pv['unit'];
            if (array_key_exists($unit, $quantity_unit)) {
                $quantity_unit[$unit] = $quantity_unit[$unit] + $pv['qty'];
            } else {
                $quantity_unit[$unit] =  $pv['qty'];
            }
            $g_weight_tolal += $pv['kgs'];
            $ch_weight_tolal += $pv['kgs'];
            $cbm_tolal += $pv['cbm'];
        }
        $quantity_tolal = "";
        foreach($quantity_unit as $uk => $uv){
            $quantity_tolal.=$uv." ".$uk." ";
        }

        //$packing = array("Quantity/Unit"=>"47 CTN","G. Weight" => "480.25 KGS","Ch. Weight" => "689.26 KGS","Volume" => "3.801 CBM");
        $g_weight_tolal = sprintf("%.3f", $g_weight_tolal);
        if (!empty($ocean['_booking_no'])) {
            $cbm_tolal = sprintf("%.3f", $cbm_tolal);
        }else{
            $cbm_tolal = sprintf("%.4f", $cbm_tolal);
        }
        $packing = array("Quantity/Unit"=>$quantity_tolal,"G. Weight" => $g_weight_tolal." KGS","Ch. Weight" => $g_weight_tolal." KGS","Volume" => $cbm_tolal." CBM");
        
        //处理邮件信息返回
        $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,
                        'email' => $email,
                        'serial_no' => $serial_no,
                        '_schemas' => $_GET['_schemas'],
                        '__serial_no' => common::deCode($ocean['serial_no'], 'E'),
                        'copyright' =>$_COPYRIGHT);
        common::echo_json_encode(200,$data);                
        exit(); 
    }

    /**
     * 信息从kln_booking表里获取（查询全部数据），信息不够在从对应模式里的air_booking取部分数据。 
    */
    private function _air_booking_detail(){
        $serial_no = common::deCode($_GET['a'], 'D');
        $status = $_GET['status'];
        $_schemas = $_GET['_schemas'];
        if($_schemas == 'public'){
            $_schemas = "ocean";
        }
        //air要关联ata_date as ata, atd没有对应字段
        $sql = "with o as(
            SELECT  o.* from public.kln_booking o where serial_no = '" . $serial_no . "'
        )
        SELECT *, po_no as _po_no,'' as incoterms,null as atd,
            sh.*, cn.*, aa.*, dd.*
        from o
            LEFT JOIN LATERAL ( SELECT company as cn_company,
                address_1 as cn_address_1,
                address_2 as cn_address_2,
                address_3 as cn_address_3,
                address_4 as cn_address_4,
                city as cn_city, state as cn_state, zipcode as cn_zipcode, country as cn_country,
                phone_1 as cn_phone
            FROM $_schemas.contacts c WHERE o.consignee_id::text = c.contact_id::text) cn ON true
            LEFT JOIN LATERAL ( SELECT company as sh_company,
                address_1 as sh_address_1,
                address_2 as sh_address_2,
                address_3 as sh_address_3,
                address_4 as sh_address_4,
                city as sh_city, state as sh_state, zipcode as sh_zipcode, country as sh_country,
                phone_1 as sh_phone
            FROM $_schemas.contacts c WHERE o.shipper_id::text = c.contact_id::text) sh ON true
            LEFT JOIN LATERAL ( SELECT company as aa_company,
                address_1 as aa_address_1,
                address_2 as aa_address_2,
                address_3 as aa_address_3,
                address_4 as aa_address_4,
                city as aa_city, state as aa_state, zipcode as aa_zipcode, country as aa_country,
                phone_1 as aa_phone
            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
            FROM $_schemas.contacts c WHERE o.agent::text = c.contact_id::text) dd ON true";
        $ocean = common::excuteObjectSql($sql);

        $vueData = $this->returnBookingDetailData($ocean,$status);

        //处理transportInfo信息数据
        $transportInfo = $vueData["transportInfo"];
        //处理basicInfo信息数据
        $basicInfo = $vueData["basicInfo"];
        //处理 拼接地址 ocean表单exp 字段无法精准分割电话和地址信息，只能从contacts表里查询
        $businessPartners = $vueData["businessPartners"];
        $marksAndDescription = $vueData["marksAndDescription"];
        //处理ocean_reference： air没有这个信息
        $ref_no = array();
        
        //处理返回booking的 Container 信息数据
        $containers = array();
        //packing
        //$packing = array("Quantity/Unit"=>"47 CTN","G. Weight" => "480.25 KGS","Ch. Weight" => "689.26 KGS","Volume" => "3.801 CBM");
        $quantity_tolal = empty($ocean['qty']) ? "" : $ocean['qty'].$ocean['qty_uom'];
        $g_weight_tolal = empty($ocean['piece_count']) ? "" : sprintf("%.3f", $ocean['piece_count'])." KGS";
        $ch_weight_tolal = empty($ocean['piece_count']) ? "": sprintf("%.3f", $ocean['weight'])." KGS";
        $cbm_tolal = empty($ocean['cbm']) ? "" : sprintf("%.4f", $ocean['cbm'])." CBM";
        $packing = array("Quantity/Unit"=>$quantity_tolal,"G. Weight" => $g_weight_tolal,"Ch. Weight" => $ch_weight_tolal,"Volume" => $cbm_tolal);
        
        //处理邮件信息返回
        $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,
                        'email' => $email,
                        'serial_no' => $serial_no,
                        '_schemas' => $_GET['_schemas'],
                        '__serial_no' => common::deCode($ocean['serial_no'], 'E'),
                        'copyright' =>$_COPYRIGHT);
        common::echo_json_encode(200,$data);                
        exit(); 
    }

    private function _booking_excel() {
        $sql = common::deCode($_POST['tmp_search'], 'D');
        $sql = substr($sql, 0, strripos($sql, " limit"));
        if(!empty($sql)){
            $rs = common::excuteListSql($sql);
        }
        //去除null
        foreach($rs as $index => $val) {
            foreach($val as $index_2 => $_val) {
                if(empty($rs[$index][$index_2]) || $rs[$index][$index_2] == null){
                    $rs[$index][$index_2] = "";
                }
            }
        }
        foreach($rs as $index => $val) {
            //合并显示 vessel
            if(array_key_exists("f_vessel", $val)){
                $rs[$index]["f_vessel"] = utils::outDisplayForMerge($val['f_vessel'],$val['_m_vessel']);
            }
            //合并显示 voyage
            if(array_key_exists("f_voyage", $val)){
                $rs[$index]["f_voyage"] = utils::outDisplayForMerge($val['f_voyage'],$val['_m_voyage']);
            }
            //显示 eta
            if(array_key_exists("m_eta", $val)){
                $rs[$index]["m_eta"] = !empty($val['m_eta']) ? $val['m_eta'] : $val['_f_eta'];
            }
            //返回加密serial_no
            $rs[$index]["__serial_no"] = common::deCode($val['__serial_no'], 'E');

            //按新逻辑处理status,现在是管理员权限，客户权限少了一种状态，先设置默认值防止出错
            if($val['bol_type'] == 'BOOKING' and $val['Status']!='Cancelled'){
                $status = "Created";
            }
            if ($val['bol_type'] != 'BOOKING'){
                $status = "Confirmed";
            }
            if ($val['Status']=='Cancelled'){
                $status = "Cancelled";
            }
            $rs[$index]["Status"] = $status;
            $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": "")));
        }
        common::echo_json_encode(200,array("msg"=>"success","Data" => $rs));
        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;
    }


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

    private function returnBookingDetailData($ocean,$status){
        $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_uncode'];
        $ocean['consignee_uncode'] = $ocean['place_of_delivery_uncode'];

        //booking对 eta的处理
        $ocean['m_eta'] = !empty($ocean['m_eta']) ? $ocean['m_eta'] :  $ocean['_f_eta'];

        //获取对应uncode 对应的时间
        $uncodes = $ocean['fport_of_loading_uncode'].";".$ocean['mport_of_discharge_uncode'];
        $codeinfo = common::getCityPortsInfo($uncodes);
        //处理transportInfo信息数据
        $_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("bookingNo." =>$ocean['booking_no'],"status"=>$status,"mode" => $_mode,
            "origin" =>$ocean['shippr_uncode'],"destination" =>$ocean['consignee_uncode'],
            "etd" =>$ocean['f_etd'],"atd" =>$ocean['atd'],
            "etd_timezone" =>$codeinfo[$ocean['fport_of_loading_uncode']],
            "atd_timezone" =>$codeinfo[$ocean['fport_of_loading_uncode']],
            "eta" =>$ocean['m_eta'],"ata" =>$ocean['ata'],
            "eta_timezone" =>$codeinfo[$ocean['mport_of_discharge_uncode']],
            "ata_timezone" =>$codeinfo[$ocean['mport_of_discharge_uncode']]);
        $data["transportInfo"] = $transportInfo;

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

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

        $shipperPartners = array("company" =>$ocean['sh_company'],"address"=>$shipper_address,"phone"=>$ocean['sh_phone']);
        $consigneePartners = array("company" =>$ocean['cn_company'],"address"=>$consignee_address,"phone"=>$ocean['cn_phone']);
        $originPartners = array("company" =>$ocean['aa_company'],"address"=>$origin_address,"phone"=>$ocean['aa_phone']);
        $destinationPartners = array("company" =>$ocean['dd_company'],"address"=>$destination_address,"phone"=>$ocean['dd_phone']);
        $businessPartners = array("shipper"=>$shipperPartners,"consignee" => $consigneePartners,"origin" => $originPartners,"destination" => $destinationPartners);
        $data["businessPartners"] = $businessPartners;

        $marksAndDescription = array("marks"=>$ocean['marks'],"description"=>$ocean['description']);
        $data["marksAndDescription"] = $marksAndDescription;

        return $data;    
    }
}

?>