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") {
            $this->_ocean_detail();
        }

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

        /*
         * export ocean order to excel
        */
        if ($operate == "excel") {
            $this->_ocean_excel();
        }
       
        if ($operate == 'setting_ocean_order_display') {
            column::getInstance()->settingDisplay('Ocean_Search', 'main_new_version.php?action=ocean_order');
        }

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

        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.";
            }
            foreach ($rs as $cv) {
                if (empty($cv["ctnr"])) {
                    $err_msg .= "Container# is empty.";
                }
                if (substr($cv["service"], 0, 3) == "CFS") {
                    $err_msg .= "CFS cannot support update.";
                }
            }
            if (!empty($err_msg)) {
                $err_msg = "You cant't add this VGM, (" . $err_msg . ")";
            }
            $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" =>"input");
            $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'];
            }
            $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();
        }
    }

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

        $sqlWhere = ' where ' . common::searchExtendHandNew("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 == "0 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 == "0 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 == "0 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 == "0 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 == 'origin'){
                        $sqlWhere .= " and  origin = '$_reportRef'";
                    }else{
                        $sqlWhere .= " and  agent = '$_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  origin = '$_reportRef'";
                    }else{
                        $sqlWhere .= " and  agent = '$_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);
                }
            }
        }

        //查询tag
        // IFFBCF;Booking Confirmation
        // IFFREC;Cargo Arrived at Origin
        // IFFDEP;Departure
        // IFFARR;Arrived at Final Destination Port
        // IFFAFD;Arrived at Final Destination
        if (!empty($_POST["filterTag"])) {
            if (utils::count($_POST['filterTag']) == 1){
                $filterTag = $_POST['filterTag'][0];
            }else{
                $filterTag = utils::implode(",", $_POST['filterTag']);
            }

            $_sqlwhere = "1<>1";
            if (stripos($filterTag, "Booked") !== FALSE) {
                $_sqlwhere .= " or (dd.status = 'Booked')";
            }
            if (stripos($filterTag, "Cargo Received") !== FALSE) {
                $_sqlwhere .= " or (dd.status = 'Cargo Received')";
            }
            if (stripos($filterTag, "Departed") !== FALSE) {
                $_sqlwhere .= " or (dd.status = 'Departed')";
            }
            if (stripos($filterTag, "Arrived") !== FALSE) {
                $_sqlwhere .= " or (dd.status = 'Arrived')";
            }
            if (stripos($filterTag, "Completed") !== FALSE) {
                $_sqlwhere .= " or (dd.status = 'Completed')";
            }
            if(strtolower($filterTag) <> "all" && !empty($filterTag)){
                $sqlWhere .= " and ($_sqlwhere)";
            }
        }
        //取消全文检索
        // 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 = array($textSearch_arr);
            }
            $more_sql = "1<>1";
            foreach($textSearch_arr as $tsv){
                $more_sql .= " or  booking_no ilike '%" . common::check_input(trim($tsv)) . "%'";
                $more_sql .= " or  h_bol ilike '%" . common::check_input(trim($tsv)) . "%'";
                $more_sql .= " or  po_no ilike '%" . common::check_input(trim($tsv)) . "%'";
                $more_sql .= " or  carrier_booking ilike '%" . common::check_input(trim($tsv)) . "%'";
            }
            if ($more_sql <> "1<>1"){
                $sqlWhere .= " and ($more_sql)";
            } 
        }

        $rc = $_POST ['rc'];
        //这里都要查询，除非多传几个参数回来
        if ($rc == - 1 || true) {
            $sql = "select count(1) from online_ocean
	            left join LATERAL (select case when a.description='Booking Confirmation' then 'Booked'
							when a.description='Cargo Pickup' then 'Cargo Received'
							when a.description='Cargo Arrived at Origin' then 'Cargo Received'
							when a.description='Departure' then 'Departed'
							when a.description='Arrived at Final Destination Port Arrived' then 'Arrived'
							when a.description='Arrived at Final Destination' then 'Completed'
							else 'Booked' END as status
				from  ocean_milestone a 
					where a.serial_no=online_ocean.serial_no 
					and a.description in (
						select regexp_split_to_table('Booking Confirmation,Cargo Pickup,Cargo Arrived at Origin,Departure,Arrived at Final Destination Port,Arrived at Final Destination', ','))
				 order by id desc limit 1) dd on true" . $sqlWhere;
            $rc = common::excuteOneSql($sql);
            error_log("online_ocean_count_ALL_SQL:".$sql);

            //Booked
            $sql = $this->getOcanOrderSearchSql("Booked",$sqlWhere);
            $Booked = common::excuteOneSql($sql);
            error_log("online_ocean_count_Booked_SQL: ".$sql);

            //Cargo Received
            $sql = $this->getOcanOrderSearchSql("Cargo Received",$sqlWhere);
            $Cargo_Received = common::excuteOneSql($sql);
            error_log("online_ocean_count_Cargo_Received_SQL: ".$sql);

            //Departed
            $sql = $this->getOcanOrderSearchSql("Departed",$sqlWhere);
            $Departed = common::excuteOneSql($sql);
            error_log("online_ocean_count_Departed_SQL: ".$sql);

            //Arrived
            $sql = $this->getOcanOrderSearchSql("Arrived",$sqlWhere);
            $Arrived = common::excuteOneSql($sql);
            error_log("online_ocean_count_Arrived_SQL: ".$sql);

            //Completed
            $sql = $this->getOcanOrderSearchSql("Completed",$sqlWhere);
            $Completed = common::excuteOneSql($sql);
            error_log("online_ocean_count_Completed_SQL: ".$sql);
            
            if (!empty($_POST["filterTag"])) {
                $tagsList = array(array("name"=>"All","number"=>$rc,"type"=>"all","checked"=>utils::in_array('All', $_POST["filterTag"])? true : false),
                array("name"=>"Booked","number"=>$Booked,"type"=>"booked","checked"=>utils::in_array('Booked', $_POST["filterTag"])? true : false),
                array("name"=>"Cargo Received","number"=>$Cargo_Received,"type"=>"cargo_received","checked"=>utils::in_array('Cargo Received', $_POST["filterTag"])? true : false),
                array("name"=>"Departure","number"=>$Departed,"type"=>"departure","checked"=>utils::in_array('Departure', $_POST["filterTag"])? true : false),
                array("name"=>"Arrived","number"=>$Arrived,"type"=>"arrived","checked"=>utils::in_array('Arrived', $_POST["filterTag"])? true : false),
                array("name"=>"Completed","number"=>$Completed,"type"=>"completed","checked"=>utils::in_array('Completed', $_POST["filterTag"])? true : false));
            }else{
                //初始，前端有选择着带入选择
                $tagsList = array(array("name"=>"All","number"=>$rc,"type"=>"all","checked"=> true ),
                array("name"=>"Booked","number"=>$Booked,"type"=>"booked","checked"=> false),
                array("name"=>"Cargo Received","number"=>$Cargo_Received,"type"=>"cargo_received","checked"=> false),
                array("name"=>"Departure","number"=>$Departed,"type"=>"departure","checked"=> false),
                array("name"=>"Arrived","number"=>$Arrived,"type"=>"arrived","checked"=> false),
                array("name"=>"Completed","number"=>$Completed,"type"=>"completed","checked"=>false));
            }

            $TransportList = array(
                array("name"=>"Ocean Freight","number"=>$rc,"checked"=>false,"icon"=>"#icon-icon_ocean_b"));

            //现在下载交给前端，后台预先只返回全部字段的列,
            $allColumn = column::getInstance()->getDisplayColumnAllReomveDefault('Ocean_Search');
            $allBookingColumns = column::getInstance()->tableColumns('Ocean_Search',$allColumn);
        }
        $tp = ceil($rc / $ps);
        $order_by = " etd desc NULLS LAST, id";
        if (_isCustomerLogin()) {
            $order_by = " eta desc NULLS LAST, id";
        }
        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 = online_ocean.serial_no) aa ON true ";

            $ocean_status_sql = " LEFT JOIN LATERAL ( select case when a.description='Booking Confirmation' then 'Booked'
							when a.description='Cargo Pickup' then 'Cargo Received'
							when a.description='Cargo Arrived at Origin' then 'Cargo Received'
							when a.description='Departure' then 'Departed'
							when a.description='Arrived at Final Destination Port Arrived' then 'Arrived'
							when a.description='Arrived at Final Destination' then 'Completed'
							else 'Booked' END as new_status
				from  ocean_milestone a 
					where a.serial_no=online_ocean.serial_no 
					and a.description in (
						select regexp_split_to_table('Booking Confirmation,Cargo Pickup,Cargo Arrived at Origin,Departure,Arrived at Final Destination Port,Arrived at Final Destination', ','))
				 order by id desc limit 1) dd ON true ";

            $sql = "SELECT order_from as _schemas,serial_no as __serial_no, serial_no,
                    h_bol as _hbol, isf_bol as _isfbol, 
                    CASE
	    	            WHEN dd.new_status is null THEN 'Booked'::text
	    	            ELSE dd.new_status::text
		            END AS new_status, ".
                    column::getInstance()->getSearchSqlForDisplay('Ocean_Search') . " from public.online_ocean $ocean_ref_sql $ocean_status_sql" . $sqlWhere . 
                " order by $order_by limit " . $ps . " offset " . ($cp - 1) * $ps;
            $rs = common::excuteListSql($sql);
            error_log("online_ocean_search_SQL: ".$sql);
            
            //对查询的结果做特殊处理，比如要拼接某个值，合并值等
            foreach($rs as $index => $val) {
                //返回加密serial_no
                $rs[$index]["__serial_no"] = common::deCode($val['__serial_no'], 'E');
                $rs[$index]["mode"] = "Ocean Freight";
                $rs[$index]["Status"] = $rs[$index]["new_status"];
            }
        
            $arrTmp = array('searchData' => $rs, 
               'tagsList' => $tagsList,
               'TransportList' => $TransportList,
               'allColums' => $allBookingColumns,
               'canEdiVgm' => _canEdiVgm(),
               'rc' => $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,
                'canEdiVgm' => _canEdiVgm(),
                'rc' => $rc,
                'ps' => $ps,
                'cp' => $cp,
                'tp' => $tp,
                'tmp_search' => common::deCode($sql, 'E'),
                'type' => common::check_input($_POST ['_ntype']));
        }
        common::echo_json_encode(200,$arrTmp);
        exit();
    }

    private function _ocean_detail() {
        $serial_no = common::deCode($_GET['a'], 'D');
        $sql = $this->getOceanDetail($serial_no);
        $ocean = common::excuteObjectSql($sql);
        
        if (!empty($ocean)) {
            $ocean['_isf_bol'] = $ocean['isf_bol'];
            if (empty($ocean['_isf_bol'])) {
                $ocean['_isf_bol'] = "AMAW" . $ocean['_h_bol'];
            }
            $isf_ss = md5(md5($ocean['_isf_bol']));
            $isf_ss = substr($isf_ss, 7) . substr($isf_ss, 0, 7);
            //ETA Dest的计算逻辑移除
            // most update status
            $_schemas = $_REQUEST["_schemas"];
            $mostStatusSql = "select last_status_loc, last_status_city, to_char(last_status_315_date, 'MM/DD/YYYY') as last_status_315_date,last_status_315_code "
                    . ", fport_of_loading_un, mport_of_discharge_un, place_of_receipt_un, place_of_delivery_un FROM public.online_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']; 

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

        //Packing信息
        $packing = array("Quantity/Unit"=>$quantity_tolal.$packages,"G. Weight" => $g_weight_tolal." KGS","Ch. Weight" => $ch_weight_tolal." LBS","Volume" => $cbm_tolal." CBM");
       
        /* Container Status */
        //数据库里配置好
        //$sql = "SELECT " . column::getInstance()->getSearchSql('Ocean_Container_Status') . " from oc_container_v where lower(serial_no) = '" . strtolower($serial_no) . "'";
        $ctnr_sql = "SELECT oc.ctnr, oc.serial_no,oc.size FROM oc_container oc LEFT JOIN ocean o ON oc.serial_no = o.serial_no 
                WHERE o.serial_no='$serial_no'";
        $ctnr_data = common::excuteListSql($ctnr_sql); 
        $containerStatusData = array();   
        foreach ($ctnr_data as $cd){
            $ctnr_status_sql = "select s.source_id, s.event_base as event,
                    to_char(to_timestamp(s.event_date, 'YYYYMMDD'), 'YYYY-MM-DD') as eventdate,
                    to_char(to_timestamp(s.event_time, 'HH24MI'), 'HH24:MI') as eventtime,
                    e.description,s.event_type as eventtype, 
                    s.event_code as eventcode, s.event_city as eventcity 
                from public.ra_online_container_status_v s 
                    left join ra_online_edi_event e on s.event_base = e.ra_name 
                where s.serial_no = '" . pg_escape_string($cd['serial_no']) . "' 
                    and s.container_no = '" . pg_escape_string($cd['ctnr']) . "' and is_display = true 
                order by to_timestamp(s.event_date, 'YYYYMMDD') asc,
                    to_timestamp(s.event_time, 'HH24MI') asc,
                    e.ra_order asc";
            $ctnr_status = common::excuteListSql($ctnr_status_sql);       

            //Container_Status　新版只显示几个状态， CTNR# EE Ｉ AE VD VA
            $container_status_column = array("EE","I","AE","VD","VA");
            $containerStatusDataTemp = array();
            $content = array();
            foreach($container_status_column as $status){
                foreach($ctnr_status as $event){
                    if(strtolower($event['event']) == strtolower($status)){
                        $eventdate = $event["eventdate"];
                        if (empty($event["source_id"]) || $event["source_id"]==0) {
                            $eventdate.= " " . $event["eventtime"];
                        }
                        $location_code = $event["eventtype"] . "," . $event["eventcode"];
                        $content[] = array("title" =>$event['description'],"date"=>$eventdate,"country"=>$location_code);
                    }
                }
            }

            $containerStatusDataTemp['label'] = $cd['ctnr']." / ".$cd['size'];
            $containerStatusDataTemp['content'] = $content;
            $containerStatusData[] = $containerStatusDataTemp;
        } 

        //Milestones info  列名固定
        $Milestones = common::getMilestonesInfo($ocean);

        //页面固定写死的信息
        $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->getFileByHbol($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 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 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 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'],
                                        "detail"=>$rs['upload_date'],
                                        "can_delete"=>_getLoginName() == $rs['upload_by']);
                                }else{
                                    $file_arr[] = array("url"=>"main_new_version?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']);
                                }
                            }
                        }
                    }

                }
                //$document_data[] = array("file_type"=>$file_type,"file"=>$file_arr);
                //按现在的逻辑，有值才会显示
                foreach($file_arr as $f){
                    $document_data[] = array("file_type"=>$file_type,"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,
                        'containers' => $containers,
                        'Milestones' => $Milestones,
                        'routes' => $routes,
                        'email' => $email,
                        'ams_ss' =>  md5(md5($ocean['serial_no'])),
                        'isf_ss' => $isf_ss,
                        '_schemas' =>$_schemas,
                        'uncode' =>$uncode,
                        'serial_no' =>$serial_no,
                        'can_upload_doc' => strtolower($_SESSION['ONLINE_USER']['can_upload_doc']) == 't' || strtolower($_SESSION['ONLINE_USER']['can_view_doc']) == 't',
                        'canEdiVgm' => _canEdiVgm(),
                        'copyright' =>$_COPYRIGHT);
        common::echo_json_encode(200,$data);                
        exit(); 
    }

    private function _ocean_excel() {
        //现在下载交给前端，后台最后只返回全部字段的列,
        $allColumn = column::getInstance()->getDisplayColumnAll('Ocean_Search');
        $allBookingColumns = column::getInstance()->tableColumns('Ocean_Search',$allColumn);
        common::echo_json_encode(200,array("msg"=>"success","allColums" => $allBookingColumns));
        exit;
    }
    
    private function _ams_isf_log(){
        global $cbpdb;
        $ams_ss = $_POST['ams_ss'];
        $isf_ss = $_POST['isf_ss'];

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

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

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

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

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

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

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

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

    /*
     * get file by hbol/destination
     * H：HBL
     * M: MBL
     * F: FL
     * I: C/I & Packing list
     */
    private function getFileByHbol($m_bol, $h_bol, $types, $m_bs, $display, $detail = FALSE,$job_no="") {
        $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 from_system,serial_no,file_path, file_name, upload_by, to_char(upload_date, 'MM/DD/YYYY') as upload_date from ra_online_doc_upload where (lower(bol) = '" . strtolower($m_bol) . "' or lower(bol) ='".strtolower($job_no)."' ) and lower(format_serailno) = '$type' and upper(coalesce((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2],'')) not in ".$filefilter." order by id desc";
            if (strtolower($m_b) == 'h')
                $sql = "SELECT from_system,serial_no,file_path, file_name, upload_by, to_char(upload_date, 'MM/DD/YYYY') as upload_date from ra_online_doc_upload where lower(bol) =  '" . strtolower($h_bol) . "' and lower(format_serailno) = '$type' and upper(coalesce((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2],'')) not in ".$filefilter." order by id desc";
            if (strtolower($m_b) == 'a')
                $sql = "SELECT from_system,serial_no,file_path, file_name, upload_by, to_char(upload_date, 'MM/DD/YYYY') as upload_date from ra_online_doc_upload where (lower(bol) = '" . strtolower($m_bol) . "' or lower(bol) = '" . strtolower($h_bol) . "' or lower(bol) ='".strtolower($job_no)."') and lower(format_serailno) = '$type' and upper(coalesce((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2],'')) not in ".$filefilter." order by id desc";
            if (!empty($sql)) {
                $rss = common::excuteListSql($sql);
                foreach ($rss as $rs) {
                    if ($rs['from_system']=="TOPOCEAN"||$rs['from_system']=="KSMART") {
                        $file[] = array("url"=>$OutFileURL.'/download.php?_schemas=&a='.base64_encode(base64_encode($rs['serial_no'])),
                            "file_name" => $rs['file_name'],
                            "detail"=>$rs['upload_date'],
                            "can_delete"=>_getLoginName() == $rs['upload_by']);
                    }else {
                        $file[] = array("url"=>"main_new_version?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']);
                    }
                }
            }
        }
        return $file;
    }

    /*
     * download file
    */
    private function _download() {
        $url = common::deCode($_GET['url'], 'D');
        common::download_file($url);
    }
    
    private function getOcanOrderSearchSql($type,$sqlWhere) {
        if($type == "Booked"){
            $Booked_sqlWhere = " and (1<>1 or dd.status ='Booked')";
            return "select count(1) from online_ocean
	            left join LATERAL (select case when a.description='Booking Confirmation' then 'Booked'
							when a.description='Cargo Pickup' then 'Cargo Received'
							when a.description='Cargo Arrived at Origin' then 'Cargo Received'
							when a.description='Departure' then 'Departed'
							when a.description='Arrived at Final Destination Port Arrived' then 'Arrived'
							when a.description='Arrived at Final Destination' then 'Completed'
							else 'Booked' END as status
				from  ocean_milestone a 
					where a.serial_no=online_ocean.serial_no 
					and a.description in (
						select regexp_split_to_table('Booking Confirmation,Cargo Pickup,Cargo Arrived at Origin,Departure,Arrived at Final Destination Port,Arrived at Final Destination', ','))
				 order by id desc limit 1) dd on true" . $sqlWhere.$Booked_sqlWhere;
        }
        if($type == "Cargo Received"){
            $Cargo_Received_sqlWhere = " and (1<>1 or dd.status ='Cargo Received')";
            return "select count(1) from online_ocean
	            left join LATERAL (select case when a.description='Booking Confirmation' then 'Booked'
							when a.description='Cargo Pickup' then 'Cargo Received'
							when a.description='Cargo Arrived at Origin' then 'Cargo Received'
							when a.description='Departure' then 'Departed'
							when a.description='Arrived at Final Destination Port Arrived' then 'Arrived'
							when a.description='Arrived at Final Destination' then 'Completed'
							else 'Booked' END as status
				from  ocean_milestone a 
					where a.serial_no=online_ocean.serial_no 
					and a.description in (
						select regexp_split_to_table('Booking Confirmation,Cargo Pickup,Cargo Arrived at Origin,Departure,Arrived at Final Destination Port,Arrived at Final Destination', ','))
				 order by id desc limit 1) dd on true" . $sqlWhere.$Cargo_Received_sqlWhere;
        }
        if($type == "Departed"){
            $Departed_sqlWhere = " and (1<>1 or dd.status ='Departed')";
            return "select count(1) from online_ocean
	            left join LATERAL (select case when a.description='Booking Confirmation' then 'Booked'
							when a.description='Cargo Pickup' then 'Cargo Received'
							when a.description='Cargo Arrived at Origin' then 'Cargo Received'
							when a.description='Departure' then 'Departed'
							when a.description='Arrived at Final Destination Port Arrived' then 'Arrived'
							when a.description='Arrived at Final Destination' then 'Completed'
							else 'Booked' END as status
				from  ocean_milestone a 
					where a.serial_no=online_ocean.serial_no 
					and a.description in (
						select regexp_split_to_table('Booking Confirmation,Cargo Pickup,Cargo Arrived at Origin,Departure,Arrived at Final Destination Port,Arrived at Final Destination', ','))
				 order by id desc limit 1) dd on true" . $sqlWhere.$Departed_sqlWhere;
        }
        if($type == "Arrived"){
            $Arrived_sqlWhere = " and (1<>1 or dd.status ='Arrived')";
            return "select count(1) from online_ocean
	            left join LATERAL (select case when a.description='Booking Confirmation' then 'Booked'
							when a.description='Cargo Pickup' then 'Cargo Received'
							when a.description='Cargo Arrived at Origin' then 'Cargo Received'
							when a.description='Departure' then 'Departed'
							when a.description='Arrived at Final Destination Port Arrived' then 'Arrived'
							when a.description='Arrived at Final Destination' then 'Completed'
							else 'Booked' END as status
				from  ocean_milestone a 
					where a.serial_no=online_ocean.serial_no 
					and a.description in (
						select regexp_split_to_table('Booking Confirmation,Cargo Pickup,Cargo Arrived at Origin,Departure,Arrived at Final Destination Port,Arrived at Final Destination', ','))
				 order by id desc limit 1) dd on true" . $sqlWhere.$Arrived_sqlWhere;
        }
        if($type == "Completed"){
            $Completed_sqlWhere = " and (1<>1 or dd.status ='Completed')";
            return "select count(1) from online_ocean
	            left join LATERAL (select case when a.description='Booking Confirmation' then 'Booked'
							when a.description='Cargo Pickup' then 'Cargo Received'
							when a.description='Cargo Arrived at Origin' then 'Cargo Received'
							when a.description='Departure' then 'Departed'
							when a.description='Arrived at Final Destination Port Arrived' then 'Arrived'
							when a.description='Arrived at Final Destination' then 'Completed'
							else 'Booked' END as status
				from  ocean_milestone a 
					where a.serial_no=online_ocean.serial_no 
					and a.description in (
						select regexp_split_to_table('Booking Confirmation,Cargo Pickup,Cargo Arrived at Origin,Departure,Arrived at Final Destination Port,Arrived at Final Destination', ','))
				 order by id desc limit 1) dd on true" . $sqlWhere.$Completed_sqlWhere;
        }
    }

    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) {
        $sql = "SELECT m_eta as _m_eta,  h_bol as _h_bol, m_bol as _m_bol,job_no as _job_bol,
                public.z_get_eta_dest(o.final_eta::date, o.eta_dest::text, o.service::text, o.m_eta::date, o.place_of_delivery::text, o.mport_of_discharge::text) as _eta_dest, 
                to_char(final_eta, 'MM/DD/YYYY') as _final_eta,
                o.* ,sh.*, cn.* ,aa.*,dd.*,ee.status as new_status
            from ocean o 
            left join LATERAL (select case when a.description='Booking Confirmation' then 'Booked'
                        when a.description='Cargo Pickup' then 'Cargo Received'
                        when a.description='Cargo Arrived at Origin' then 'Cargo Received'
                        when a.description='Departure' then 'Departed'
                        when a.description='Arrived at Final Destination Port Arrived' then 'Arrived'
                        when a.description='Arrived at Final Destination' then 'Completed'
                        else 'Booked' END as status
                from  ocean_milestone a 
                    where a.serial_no=o.serial_no 
                    and a.description in (
                        select regexp_split_to_table('Booking Confirmation,Cargo Pickup,Cargo Arrived at Origin,Departure,Arrived at Final Destination Port,Arrived at Final Destination', ','))
                order by id desc limit 1) ee 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 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 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 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 contacts c WHERE o.destination_station::text = c.contact_id::text) dd ON true
        where lower(serial_no) = '" . strtolower($serial_no) . "'";
        return $sql;
    }

    private function returnOceanDetailData($ocean,$state){
        $data = array();

        //处理shipment data暂时写死
        $simplexData = array();
        $simplexData[] = array("index" =>1,"label" =>"Place of Receipt",
            "path" =>$ocean['place_of_receipt_exp']."/".$ocean['place_of_receipt'],"isArrival" =>$state == 1 ? true : false);

        $simplexData[] = array("index" =>2,"label" =>"Port of Loading",
            "path" =>$ocean['fport_of_loading_exp_exp']."/".$ocean['fport_of_loading_exp'],"isArrival" =>$state == 2 ? true : false,
            "children" =>array(array("label" =>"Cargo arrival at Origin","date" =>"2024-02-01"),
                            array("label" =>"'Departure","date" =>"2024-02-01")));

        $simplexData[] = array("index" =>3,"label" =>"Port of Discharge",
            "path" =>$ocean['mport_of_discharge_exp']."/".$ocean['mport_of_discharge'],"isArrival" =>$state == 3 ? true : false,
            "children" =>array(array("label" =>"ETA","date" =>"2024-02-01")));

        $simplexData[] = array("index" =>4,"label" =>"Place of Delivery",
            "path" =>$ocean['place_of_delivery_exp']."/".$ocean['place_of_delivery'],"isArrival" =>$state == 4 ? true : false);
        $data['shipmentData'] = $simplexData;

        //处理transportInfo信息数据
        $transportInfo = array("Tracking No." =>$ocean['tracking_no'],"status"=>$ocean['new_status'],"mode" => "Ocean Freight",
            "origin" =>$ocean['origin_station'],"destination" =>$ocean['destination_station'],
            "etd" =>$ocean['f_etd'],"atd" =>"","eta" =>$ocean['m_eta'],"ata" =>"");
        $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['carrier_booking'],
            "PO_NO" =>$ocean['po_no'],"Vessel/Airline" =>$vessel,"Voyage/Filght" =>$voyage,
            "Incoterm" =>$ocean['terms'],"Service_Type" =>$ocean['service']);
        $data['basicInfo'] = $basicInfo;    

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

        $shipperPartners = array("company" =>$ocean['sh_company'],"address"=>$shipper_address,"phone"=>$ocean['sh_phone']);
        $consigneePartners = array("company" =>$ocean['cn_company'],"address"=>$consignee_address,"phone"=>$ocean['cn_phone']);
        $originPartners = array("company" =>$ocean['aa_company'],"address"=>$origin_address,"phone"=>$ocean['aa_phone']);
        $destinationPartners = array("company" =>$ocean['dd_company'],"address"=>$destination_address,"phone"=>$ocean['dd_phone']);
        $businessPartners = array("shipper"=>$shipperPartners,"consignee" => $consigneePartners,"origin" => $originPartners,"destination" => $destinationPartners);
        $data['businessPartners'] = $businessPartners;  
    
        //处理routes 转船可能有多个情况
        $routes = array(array("mode" =>"Sea", "origin" =>$ocean['fport_of_loading'],
            "destination" => $ocean['fport_of_discharge'],
            "etd" => $ocean['f_etd'],"atd" => "",
            "eta" =>$ocean['f_eta'],"ata" =>"",
            "vessel" =>$ocean['f_vessel'],"voyageNo" =>$ocean['f_voyage']));
        //不是直航
        if ($ocean['is_vessel_direct'] <> "t"){
            $routes[] = array("mode" =>"Sea", "origin" =>$ocean['mport_of_loading'],
                "destination" => $ocean['mport_of_discharge'],
                "etd" => $ocean['m_etd'],"atd" => "",
                "eta" =>$ocean['m_eta'],"ata" =>"",
                "vessel" =>$ocean['m_vessel'],"voyageNo" =>$ocean['m_voyage']);
        }
        $data['routes'] = $routes;
        
        //处理marksAndDescription
        $marksAndDescription = array("marks"=>$ocean['marks'],"description"=>$ocean['description']);
        $data['marksAndDescription'] = $marksAndDescription;

        return $data;    
    }
}
?>