k_online_backend/service/destination_delivery.class.php

<?php

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

/**
 * Description of operation_log
 *
 * @author Administrator
 */
class destination_delivery {

    private static $_destination_delivery;

    public static function getInstance() {
        if (!self::$_destination_delivery) {
            $c = __CLASS__;
            self::$_destination_delivery = new $c;
        }
        return self::$_destination_delivery;
    }

    /**
     * 关于destination_delivery 页面的所有的静态加载数据查询
     */
    public function destination_delivery_load(){
        $operate = utils::_get('operate');
        $operate = strtolower($operate);

        /**
         * select country
         */
        if ($operate == "country") {
            $term = $_POST['term'];
            $term = trim($term);
            $sql = "select DISTINCT category AS country_code from public.kerry_system_code
                        where description='COUNTRY-STATION' 
                              and category ilike '" . common::check_input($term) . "%'";
            $sql .= " order by category";

            $rs = common::excuteListSql($sql);
            //前端要加上id 从1开始
            $retData = array();
            foreach($rs as $key => $val){
                if(!empty($val['country_code'])){
                    $retData[] = array("value" =>$val['country_code'] ,"label"=>$val['country_code']);
                }
            }
            common::echo_json_encode(200,$retData);
            exit();

        }

        /**
         * select station  选择国家后再展示 station list数据
        */
        if ($operate == "station") {
            $country = common::check_input($_REQUEST['country']);
            $sql  = "select code from public.kerry_system_code 
                    where description='COUNTRY-STATION'  
                        and category = '".$country."'   
                        and code NOT LIKE 'ONLINE_%' order by code";
            $rs = common::excuteListSql($sql);
            foreach($rs as $key => $val){
                if(!empty($val['code'])){
                    $retData[] = array("value" =>$val['code'] ,"label"=>$val['code']);
                }
            }
            common::echo_json_encode(200,$retData);
            exit();
        }

        /**
         * select KLN PIC   下拉可供选择的employee帐号，且帐号权限站点包括选中的station ？
         */
        if ($operate == "employee_account") {
            $term = $_POST['term'];
            $term = trim($term);
            $station = $_REQUEST['station'];
            $sqlwhere = '';
            if(!empty($station)){
                //more_param = common::getInNotInSqlForSearch(utils::implode(';',$station));
                //$sqlwhere = " and lower(r.allow_login_remote) in ($more_param) ";
                $safeStation = [];
                foreach ($station as $s) {
                    $safeStation[] = common::check_input($s);
                }
                $arrayLiteral = '{' . implode(',', $safeStation) . '}';
                $sqlwhere = " AND coalesce(r.allow_login_remote,'') <>'' 
                              AND '$arrayLiteral'::text[] && string_to_array(r.allow_login_remote, ';')";
            }

            $sql = "select e.email from public.ra_online_user r, employee e 
                    where r.employee_id=e.employee_id 
                        and r.online_active = true
                        and r.user_type = 'Employee' 
                        and e.active = true
                        ".$sqlwhere."
                        and coalesce(e.email,'') <>''
                        and e.email ilike '" . common::check_input($term) . "%' order by e.email  limit 50";

            $rs = common::excuteListSql($sql);
            $retData = array();
            foreach($rs as $key => $val){
                if(!empty($val['email'])){
                    $retData[] = array("value" =>$val['email'] ,"label"=>$val['email']);
                }
            }
            common::echo_json_encode(200,$retData);
            exit();
        }

        /**
         * 下拉可供选择的ports
         */
        if ($operate == "ports") {
            
            $mode = $_POST['mode'];
            $term = $_POST['term'];
            $term = trim($term);

            if ($mode == 'sea'){
                $sql  = "select DISTINCT uncode from public.ports 
                    where  coalesce(uncode,'')<> '' and  uncode ilike '" . common::check_input($term) . "%'";
            } else {
                    $sql = "select DISTINCT coalesce(airport.country_abb,'')||airport.airport_code as uncode from sfs.airport 
                            where  coalesce(country_abb,'')<>'' 
                                and  coalesce(airport.country_abb,'')||airport.airport_code ilike '" . common::check_input($term) . "%'";
            }
            $sql .= " order by uncode limit 50";

            $rs = common::excuteListSql($sql);
            //前端要加上id 从1开始
            $retData = array();
            foreach($rs as $key => $val){
                if(!empty($val['uncode'])){
                    $retData[] = array("value" =>$val['uncode'] ,"label"=>$val['uncode'],"checked"=>false);
                }
            }
            common::echo_json_encode(200,$retData);
            exit();
            
        }

        /**
         * 下拉可供选择的carrier
         */
        if ($operate == "carrier") {
            $term = $_POST['term'];
            $term = trim($term);

            $sql = "select DISTINCT scac from public.vessel_company
                            where coalesce(scac,'')<> '' and  scac ilike '" . common::check_input($term) . "%'";
            $sql .= " order by scac limit 50";

            $rs = common::excuteListSql($sql);
            //前端要加上id 从1开始
            $retData = array();
            foreach($rs as $key => $val){
                if(!empty($val['scac'])){
                    $retData[] = array("value" =>$val['scac'] ,"label"=>$val['scac'],"checked"=>false);
                }
            }
            common::echo_json_encode(200,$retData);
            exit();
        }

        /**
         *  Manage Address load
        */
        if ($operate == "manage_address"){
            $consignee = $_REQUEST['consignee_id'];

            //$consignee =array('COMGEN38002','FGB050132','ATOZTI35005','WILSON80001');
            $more_param = common::getInNotInSqlForSearch(utils::implode(';',$consignee));

            $country = $_REQUEST['country'];
            //$country =array('GB','DE','US');
            $more_param_country = common::getInNotInSqlForSearch(utils::implode(';',$country));

            $delivery_serial_no = common::deCode($_REQUEST['delivery_serial_no'], 'D');
            //contact_person,contact_number,  现在系统还没有把这两个字段接入
            //if(!empty($delivery_serial_no)){
                $sql = "SELECT addr1,addr2,addr3,addr4,
                            ctry_code,city_code,postal_code,
                            attn as contact_person,tel as contact_number,
                            create_user,contact_id,sync_key,
                            from_station,
                            'from Ksmart' as op_action
                        FROM public.contacts_address ca 
                            WHERE addr_type = 'D' 
                                and lower(ca.contact_id) in (".$more_param.") 
                                and ca.from_station in (select code from public.kerry_system_code where lower(category) in (".$more_param_country.")) 
                                and ca.sync_key not in (select sync_key from public.contacts_address_online where lower(contact_id) in (".$more_param."))
                        union all
                        SELECT addr1,addr2,addr3,addr4,
                                ctry_code,city_code,postal_code,
                                attn as contact_person,
                                tel as contact_number,
                                create_user,contact_id,sync_key,
                                from_station,
                                action as op_action
                        FROM public.contacts_address_online 
                            where is_delete = false  
                                and lower(contact_id) in (".$more_param.")";
            // }else{
            //     $sql = "SELECT addr1,addr2,addr3,addr4,
            //                 ctry_code,city_code,postal_code,
            //                 '' as contact_person,'' as contact_number,
            //                 create_user,contact_id,sync_key,
            //                 from_station,
            //                 'from Ksmart' as op_action
            //             FROM public.contacts_address ca 
            //             WHERE addr_type = 'D' 
            //                 and lower(ca.contact_id) in (".$more_param.") 
            //                 and ca.from_station in (select code from public.kerry_system_code where lower(category) in (".$more_param_country."))";
            // }

            $data = common::excuteListSql($sql);
            error_log($sql);
            $manageAddressList = array();
            //Online_D_Address
            foreach($data as $d){
                if ($d['op_action'] == "from Ksmart" && $d['create_user'] <> "Online_D_Address"){
                    $address_remark = "read-only";
                } elseif ($d['op_action'] == "from Ksmart" && $d['create_user'] == "Online_D_Address"){
                    $address_remark = "reviewed";
                } elseif ($d['op_action'] == "Add" && $d['create_user'] == "Online_D_Address"){
                    $address_remark = "unreviewed";
                } elseif ($d['op_action'] == "Modify" && $d['create_user'] == "Online_D_Address"){
                    $address_remark = "unreviewed";
                } else {
                    $address_remark = "deletion";
                }
                $manageAddressList[] = array(
                    "address_1"=>$d['addr1'],
                    "address_2"=>$d['addr2'],
                    "address_3"=>$d['addr3'],
                    "address_4"=>$d['addr4'],
                    "country"=>$d['ctry_code'],
                    "city"=>$d['city_code'],
                    "postal_code"=>$d['postal_code'],
                    "contact_person"=>$d['contact_person'] === null ? '' : $d['contact_person'],
                    "contact_number" =>$d['contact_number'] === null ? '' : $d['contact_number'],
                    "create_user" => $d['create_user'],   //create_user = Online_D_Address 时，代表對客戶賬號類型自己創建的D類地址
                    "contact_id" => $d['contact_id'],
                    "sync_key" => $d['sync_key'],         //唯一key 
                    "from_station" => $d['from_station'],  //只有是客户新键的才会特殊处理station Online_GE.  好判断是那个国家的
                    "contact_type" => "Unedit",           //这个前端控制的
                    "op_action" => $d['op_action'],
                    "address_remark" => $address_remark
                );
            }
            common::echo_json_encode(200,$manageAddressList);
            exit();

        }

        /**
         *  country and city load
         */
        if ($operate == "country_city_load") {
            $term_type = $_POST['term_type'];
            
            $limit = $_POST['limit'];
            $term = $_POST['term'];
            $term = trim($term);

            $extend_sql_where = "";
            if ($term_type == "country" && !empty($limit)){
                $extend_sql_where = " and location_code = '$limit'";
            }
            if ($term_type == "city" && !empty($limit)){
                $extend_sql_where = " and country_code = '$limit'";
            }

            if ($term_type == "country"){
                $sql = "select DISTINCT country_code AS code from public.unlocode 
                            where country_code ilike '" . common::check_input($term) . "%'".$extend_sql_where;
                $sql .= " order by country_code  limit 50";
            }
            if ($term_type == "city"){
                $sql = "select DISTINCT location_code AS code from public.unlocode 
                            where location_code ilike '" . common::check_input($term) . "%'".$extend_sql_where;
                $sql .= " order by location_code limit 50";
            }    
            $rs = common::excuteListSql($sql);
            //前端要加上id 从1开始
            $retData = array();
            foreach($rs as $key => $val){
                if(!empty($val['code'])){
                    $retData[] = array("value" =>$val['code'] ,"label"=>$val['code']);
                }
            }
            common::echo_json_encode(200,$retData);
            exit();

        }

        if ($operate == "destination_delivery_calendar_consignee") {
            $month = common::check_input($_POST ['month']);
            $month = common::excuteObjectSql("SELECT CURRENT_DATE AS access_date,  
                                        (to_date('".$month."', 'MM/YYYY'))::date AS month_start,
                                (date_trunc('month', to_date('".$month."', 'MM/YYYY')) + interval '1 month - 1 day')::date AS month_end");

            $sqlWhere = ' where ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
            //兜底规则
            $sqlKNDDWhere = "and exists(select 1 from public.kln_ocean ". $sqlWhere."  and kln_ocean.serial_no = any(kln_destination_delivery.h_serial_no) limit 1)";  

            $consignee = common::check_input($_POST ['consignee']); 
            if(!empty($consignee)){
                $sqlKNDDWhere .= " and consignee ilike '%".$consignee."%'";
            }                 

            $sql  = "select DISTINCT consignee AS code from public.kln_destination_delivery
                    where 
                        status NOT IN ('Cancel', 'Reject')
                        AND delivery_date >= '".$month['month_start']."'
                        AND delivery_date <= '".$month['month_end']."' ".$sqlKNDDWhere;
            $rs = common::excuteListSql($sql);

            foreach($rs as $key => $val){
                if(!empty($val['code'])){
                    $retData[] = array("value" =>$val['code'] ,"label"=>$val['code']);
                }
            }
            common::echo_json_encode(200,$retData);
            exit();
        }
    }

    /**
     * destination_delivery 配置
    */
    public function destination_delivery_config(){
        $operate = utils::_get('operate');
        $operate = strtolower($operate);

        /**
         * 这个是嵌套在destination_delivery的配置查询，
         */
        if ($operate == "search"){
            $cp = common::check_input($_POST ['cp']); //current_page
            $ps = common::check_input($_POST ['ps']); //ps
            if (empty($ps))
                $ps = 100;
            if (empty($cp))
                $cp = 1;

            $rc = $_POST ['rc'];
            if ($rc == - 1) {
                $sql = "select count(*) from public.kln_destination_delivery_config";
                $rc = common::excuteOneSql($sql);
            }
            $tp = ceil($rc / $ps);
            if ($rc > 0) {
                $sql = "select * from public.kln_destination_delivery_config";
                $sql .= " order by id desc limit " . $ps . " offset " . ($cp - 1) * $ps;

                $rs = common::excuteListSql($sql);

                foreach($rs as $key => $val){
                    $rs[$key]["_serial_no"] = common::deCode($val['serial_no'], 'E');
                }
                $arrTmp = array('searchData' => $rs, 
                        'rc' => intval($rc),
                        'ps' => intval($ps),
                        'cp' => intval($cp),
                        'tp' => intval($tp));
                common::echo_json_encode(200,$arrTmp);
                exit();
            }else{
                $arrTmp = array('searchData' => array());
                common::echo_json_encode(200, $arrTmp);
                exit();
            }
        }

        if ($operate == "add"){
            $serial_no = common::deCode($_REQUEST['a'], 'D');
            $data = array();
            $rule = array();

            if (!empty($serial_no)){
                $sql = "select * from public.kln_destination_delivery_config where serial_no = '$serial_no'";
                $data = common::excuteObjectSql($sql);

                if ($data['recommended_delivery'] == 'Delivery_ETA_ATA') {
                    $sql = "select * from public.kln_destination_delivery_rule where recommended_delivery_serial_no = '$serial_no'";
                    $rule = common::excuteListSql($sql);
                }
            } else{
                //代表新加，页面初始
                $data['booking_window'] = "No_Restrictions";
                $data['booking_window_date_start'] ="";
                $data['booking_window_date_end'] ="";
                $data['recommended_delivery'] ="No_Recommended";
            }

            //处理组装返回格式
            //RcommendDeliveryDate
            $RecommendCheckedAirList = array();
            $RecommendCheckedSeaList = array();
            $RecommendCheckedList = array();
            foreach($rule as $item){
                if ($item['mode_type'] == 'sea'){
                    $RecommendCheckedSeaList[] = array("priority"=>$item['priority'],"rule_type"=>$item['rule_type'],
                        "ports"=>explode(",", $item['ports']),
                        "carrier"=>explode(",", $item['carrier']),
                        "recommended_delivery_from"=>$item['recommended_delivery_from'],
                        "recommended_delivery_to"=>$item['recommended_delivery_to'],
                        "mode_type"=>$item['mode_type']);
                }
                if ($item['mode_type'] == 'air'){
                    $RecommendCheckedAirList[] = array("priority"=>$item['priority'],"rule_type"=>$item['rule_type'],
                        "ports"=>explode(",", $item['ports']),
                        "carrier"=>explode(",", $item['carrier']),
                        "recommended_delivery_from"=>$item['recommended_delivery_from'],
                        "recommended_delivery_to"=>$item['recommended_delivery_to'],
                        "mode_type"=>$item['mode_type']);
                }
                if (!utils::in_array(ucfirst($item['mode_type']), $RecommendCheckedList)) {
                    $RecommendCheckedList[] = ucfirst($item['mode_type']);
                }
            }

            $returnData = $data;
            $returnData["SetBookingWindow"] = array("windowradio" =>common::destination_delivery_window_radio($data['booking_window']),
                    "windowBeforeDays"=>$data['booking_window_date_start'], 
                    "windowAfterDays"=>$data['booking_window_date_end']);

            $returnData["RcommendDeliveryDate"] = array("Recommendradio" =>common::destination_delivery_recommended($data['recommended_delivery']),
                    "RecommendCheckedList"=>$RecommendCheckedList, 
                    "RecommendCheckedAirList"=>$RecommendCheckedAirList,
                    "RecommendCheckedSeaList"=>$RecommendCheckedSeaList);

            $returnData["KLNPLCvalue"] = empty($data['kln_pic']) ? array() : explode(";", $data['kln_pic']);

            $CountryCheckedList = array();
            $sql = "select code from public.kerry_system_code 
                    where description='COUNTRY-STATION'  
                        and category = '".$data['country']."' 
                        and code NOT LIKE 'Online_%'";
            $rs = common::excuteListSql($sql);
            foreach($rs as $key => $val){
                if(!empty($val['code'])){
                    $CountryCheckedList[] = array("value" =>$val['code'] ,"label"=>$val['code']);
                }
            }
            $returnData["CountryCheckedList"] = $CountryCheckedList;
            $returnData["station"] = explode(",", $returnData["station"]);
            $data = array('returnData' =>$returnData);
            common::echo_json_encode(200,$data);                
            exit(); 
        }

        if ($operate == "save"){
            $serial_no = common::deCode($_POST['serial_no'], 'D');

            $country = common::check_input($_POST['country']);
            $station = empty($_POST['station']) ? "" : utils::implode(",",$_POST['station']);
            $booking_window = common::check_input($_POST['booking_window']);
            $booking_window_date_start = common::check_input($_POST['booking_window_date_start']);
            $booking_window_date_end = common::check_input($_POST['booking_window_date_end']);
            $recommended_delivery = common::check_input($_POST['recommended_delivery']);
            $kln_pic = $_POST['kln_pic'];
            $kln_pic = utils::implode(";",$kln_pic);
            $booking_window_desc = common::check_input($_POST['booking_window_desc']);
            $recommended_delivery_date_desc = common::check_input($_POST['recommended_delivery_date_desc']);

            //rule
            $_mode_type = $_POST['mode_type'];
            $_priority = $_POST['priority'];
            $_rule_type = $_POST['rule_type'];
            $_ports = $_POST['ports'];
            $_carrier = $_POST['carrier'];
            $_recommended_delivery_from = $_POST['recommended_delivery_from'];
            $_recommended_delivery_to = $_POST['recommended_delivery_to'];

            $sql = "";
            if (!empty($serial_no)){
                $updateSqlSet = " modify_by = '"._getLoginName()."',update_time = now()";
                if (!empty($country)) {
                    $updateSqlSet.= ", country = '$country' ";
                }
                if (!empty($station)) {
                    $updateSqlSet.= ", station = '$station' ";
                }
                if (!empty($booking_window)) {
                    $updateSqlSet.= ", booking_window = '$booking_window' ";
                }
                if (!empty($booking_window_date_start)) {
                    $updateSqlSet.= ", booking_window_date_start = '$booking_window_date_start' ";
                }
                if (!empty($booking_window_date_end)) {
                    $updateSqlSet.= ", booking_window_date_end = '$booking_window_date_end' ";
                }
                if (!empty($recommended_delivery)) {
                    $updateSqlSet.= ", recommended_delivery = '$recommended_delivery' ";
                }
                if (!empty($kln_pic)) {
                    $updateSqlSet.= ", kln_pic = '$kln_pic' ";
                }
                if (!empty($booking_window_desc)) {
                    $updateSqlSet.= ", booking_window_desc = '$booking_window_desc' ";
                }
                if (!empty($recommended_delivery_date_desc)) {
                    $updateSqlSet.= ", recommended_delivery_date_desc = '$recommended_delivery_date_desc' ";
                }
                //代表update 
                $sql .= "update public.kln_destination_delivery_config set ".$updateSqlSet."
                    where serial_no = '$serial_no';";
            } else {
                //检查配置的站点是否于以前的配置的，是否重合
                $exist = common::excuteListSql("select country,station from public.kln_destination_delivery_config WHERE string_to_array('$station', ',') && string_to_array(station, ',')");
                if(!empty($exist)){
                    $data = array("msg" =>"The station has been reconfigured. Please check ".$exist[0]['country'] ." - " .$exist[0]['station']);
                    common::echo_json_encode(200,$data);                
                    exit(); 
                }
                //代表更新
                $serial_no = common::uuid();
                $sql .="INSERT INTO public.kln_destination_delivery_config(
                        serial_no, country, station, booking_window, booking_window_date_start, 
                        booking_window_date_end, recommended_delivery, kln_pic, booking_window_desc,recommended_delivery_date_desc,
                        create_by, created_time, modify_by, update_time)
                VALUES ('$serial_no', '$country', '$station', '$booking_window', '$booking_window_date_start', 
                        '$booking_window_date_end', '$recommended_delivery', '$kln_pic','$booking_window_desc','$recommended_delivery_date_desc',
                        '"._getLoginName()."', now(), '"._getLoginName()."', now());";
            }

            $errmsg = "";
            //柜子是先删除，后添加
            $sql .= "delete from  public.kln_destination_delivery_rule where recommended_delivery_serial_no = '$serial_no';";
            if ($recommended_delivery == "Delivery_ETA_ATA"){
                // 用于统计每种类型是否存在，以及是否满足 rule_type = 'all'
                $typeExists = ['air' => false, 'sea' => false];
                $typeHasAllRule = ['air' => false, 'sea' => false];

                //检查判断规则不能重合
                $seaRule = [];
                $airRule = [];
                $startIndex = null;

                foreach($_mode_type as $key => $v){
                    $mode_type = $v;
                    $priority = $_priority[$key];
                    $rule_type = $_rule_type[$key];
                    $ports = $_ports[$key];
                    $carrier = $_carrier[$key];
                    if( $mode_type == 'sea'){
                        $seaRule[] = explode(",",$_ports[$key]);
                        if(empty($startIndex)){
                              $startIndex = $key;
                        }
                    } else {
                        $airRule[] = explode(",",$_ports[$key]);  
                    }
                    $recommended_delivery_from = $_recommended_delivery_from[$key];
                    $recommended_delivery_to = $_recommended_delivery_to[$key];

                    $sql .= "INSERT INTO public.kln_destination_delivery_rule(
                            recommended_delivery_serial_no, mode_type, priority, 
                            rule_type, ports, carrier, recommended_delivery_from, recommended_delivery_to, 
                            create_by, created_time, modify_by, update_time)
                    VALUES ('$serial_no', '$mode_type', '$priority', 
                            '$rule_type', '$ports', '$carrier', '$recommended_delivery_from', '$recommended_delivery_to', 
                            '"._getLoginName()."', now(), '"._getLoginName()."', now());";

                    $typeExists[$mode_type] = true;
                    if ($rule_type == '*Default Rule') {
                        $typeHasAllRule[$mode_type] = true;
                    }
                }
                // 检查缺失的 rule_type = 'all'
                $missingTypes = [];
                foreach (['air', 'sea'] as $type) {
                    if ($typeExists[$type] && !$typeHasAllRule[$type]) {
                        $missingTypes[] = $type;
                    }
                }
                if (!empty($missingTypes)) {
                    $errmsg =  "The Default Rule must exist.";
                }

                //检查规则不能重复
                $errmsg .= common::checkOverlap($seaRule,"Sea Rule",$startIndex);
                $errmsg .= common::checkOverlap($airRule,"Air Rule");
            }

            if (empty($errmsg) && !empty($sql)){
                common::excuteUpdateSql($sql);
                $data = array("msg" =>"success");
            } else {
                $data = array("msg" =>$errmsg);
            }  
            common::echo_json_encode(200,$data);                
            exit(); 
        }

        if ($operate == "delete"){
            $serial_no = common::deCode($_POST['a'], 'D');
            $sql = "delete from public.kln_destination_delivery_config where serial_no = '$serial_no';";
            $sql .= "delete from public.kln_destination_delivery_rule where recommended_delivery_serial_no = '$serial_no';";
            error_log($sql);
            common::excuteUpdateSql($sql);
            $data = array("msg" =>"success");
            common::echo_json_encode(200,$data);                
            exit(); 
        }

    }

    /**
     *  booking
     */
    public function destination_delivery_booking(){
        $operate = utils::_get('operate');
        $operate = strtolower($operate);

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

        /**
         * customize columns的按鈕
        */
        if ($operate == 'destination_delivery_shipment_display') {
            column::getInstance()->settingDisplay('destination_delivery_shipment_search', '');
        }

        if ($operate == "destination_delivery_shipment_search") {
            //search
            //栏位信息
            $column = column::getInstance()->getDisplayColumn('destination_delivery_shipment_search');
            $BookingTableColumns = column::getInstance()->tableColumns('destination_delivery_shipment_search',$column);

            //Packing List 和 Commercial Invoice  顺序处理,以后自定义了可以移除这个函数
            $BookingTableColumns = common::destination_delivery_shipment_search_sort($BookingTableColumns);
            $data['TrackingTableColumns'] = $BookingTableColumns;
            common::echo_json_encode(200,$data);
            exit();
        }

        /**
         * select shipment，
         *  Pending Approval Approve Reject Cancel  Modify  Submit
         */
        if ($operate == "search"){
            $cp = common::check_input($_POST ['cp']); //current_page
            $ps = common::check_input($_POST ['ps']); //ps
            if (empty($ps))
                $ps = 100;
            if (empty($cp))
                $cp = 1;

            $sqlWhere = ' where ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
            //兜底规则
            $sqlWhere .= " and eta >= now() - INTERVAL '6 months' and eta <= now() + INTERVAL '6 months'";
            //代表employee
            $sqlDeliveryWhere = " 1=1"; 
            if(_isApexLogin()){
                //$sqlDeliveryWhere .=  " and '".strtolower("CATHY.LEE@APEXSHIPPING.COM")."' =  ANY(regexp_split_to_array(LOWER(kd.kln_pic), ','))";
                //$sqlDeliveryWhere .= " and '".strtolower(_getLoginEmployeeEamil())."' =  ANY(regexp_split_to_array(LOWER(kd.kln_pic), ','))";
            }    

            //条件带入
            $text_search = $_REQUEST['text_search'];
            if (!empty($text_search)){
                $sqlDeliveryWhere .= " and (lower(booking_no) like '%".strtolower($text_search)."%'
                    or lower(h_bol) like '%".strtolower($text_search)."%'
                    or lower(m_bol) like '%".strtolower($text_search)."%' 
                    or lower(ctnr) like '%".strtolower($text_search)."%'
                    or lower(consignee) like '%".strtolower($text_search)."%')";
            }

            if (isset($_REQUEST['delivery_date_start']) && !empty($_REQUEST['delivery_date_start']))
                $sqlDeliveryWhere .= " and kd.delivery_date >= '" . common::usDate2sqlDate($_REQUEST['delivery_date_start']) . " 00:00:00'";
            if (isset($_REQUEST['delivery_date_end']) && !empty($_REQUEST['delivery_date_end']))
                $sqlDeliveryWhere .= " and kd.delivery_date <= '" . common::usDate2sqlDate($_REQUEST['delivery_date_end']) . " 23:59:59'";

            if(!empty($_POST['delivery_mode'])){
                $sqlDeliveryWhere .= " and kd.delivery_mode ='". common::check_input($_REQUEST['delivery_mode'])."'";
            }
            if (isset($_REQUEST['created_time_start']) && !empty($_REQUEST['created_time_start']))
                $sqlDeliveryWhere .= " and kd.created_time >= '" . common::usDate2sqlDate($_REQUEST['created_time_start']) . " 00:00:00'";
            if (isset($_REQUEST['created_time_end']) && !empty($_REQUEST['created_time_end']))
                $sqlDeliveryWhere .= " and kd.created_time <= '" . common::usDate2sqlDate($_REQUEST['created_time_end']) . " 23:59:59'";

            //移除filterTag
            //$sqlWhere_befrom_filterTag  = $sqlWhere;

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

                $filterTag_param = "1<>1";
                if(strtolower($filterTag) == "all"){
                    $filterTag_param = "1=1";
                }
                if (stripos($filterTag, "Pending Approval") !== FALSE) {
                    $filterTag_param .= " or (kd.status = 'Pending Approval')";
                }
                if (stripos($filterTag, "Approve") !== FALSE) {
                    $filterTag_param .= " or (kd.status = 'Approve')";
                }
                if (stripos($filterTag, "Reject") !== FALSE) {
                    $filterTag_param .= " or (kd.status = 'Reject')";
                }
                if (stripos($filterTag, "Cancel") !== FALSE) {
                    $filterTag_param .= " or (kd.status = 'Cancel')";
                }
                if(strtolower($filterTag) <> "all" && !empty($filterTag)){
                    $filterTag_param = " ($filterTag_param)";
                }
            }
            if(empty($filterTag_param)){
                $filterTag_param = "1=1";
            }
            $tag_and_mode_param = $filterTag_param;    
            

            $rc = $_POST ['rc'];
            if ($rc == - 1 || true) {
                $sql = "select count(1) as rc,
                        sum(case when $tag_and_mode_param then 1 else 0 end) as seach_rc, 
                        sum(case when kd.status ='Pending Approval' then 1 else 0 end) as pending_approval_rc, 
                        sum(case when kd.status ='Approve' then 1 else 0 end) as approved_rc,
                        sum(case when kd.status ='Reject' then 1 else 0 end) as rejected_rc,
                        sum(case when kd.status ='Cancel' then 1 else 0 end) as cancelled_rc
                    from public.kln_destination_delivery kd
                        where ".$sqlDeliveryWhere." 
                            and exists(select 1 from public.kln_ocean ". $sqlWhere."  and kln_ocean.serial_no = any(kd.h_serial_no) limit 1)";
                error_log("kln_destination_delivery_count:".$sql);        
                $statusRc = common::excuteObjectSql($sql);
                $rc = $statusRc['rc'];
                $search_rc = $statusRc['seach_rc'];
                $pending_approval_rc = $statusRc['pending_approval_rc'];
                $approved_rc = $statusRc['approved_rc'];
                $rejected_rc = $statusRc['rejected_rc'];
                $cancelled_rc = $statusRc['cancelled_rc'];
            }
            $tp = ceil($rc / $ps);
            if ($rc > 0) {
                $sql = "select *,to_char((recommended_delivery_window_date_from)::date, 'YYYY-MM-DD') 
                                    || ';' ||
                                    to_char((recommended_delivery_window_date_to)::date, 'YYYY-MM-DD') AS date_range
                         from public.kln_destination_delivery kd
                    where ".$tag_and_mode_param." 
                        and ".$sqlDeliveryWhere."
                        and exists(select 1 from public.kln_ocean  ". $sqlWhere."  and kln_ocean.serial_no = any(h_serial_no) limit 1)";
                $sql .= " order by id desc limit " . $ps . " offset " . ($cp - 1) * $ps;

                error_log("kln_destination_delivery_search:".$sql);
                $rs = common::excuteListSql($sql);

                foreach($rs as $key => $val){
                    $rs[$key]["_serial_no"] = common::deCode($val['serial_no'], 'E');
                    $rs[$key]["status"] = common::deliveryStatusConvert($val['status']);
                    //转换一下
                    $h_bol_multiple_link = json_decode($val['h_bol_multiple_link'],true);
                    foreach($h_bol_multiple_link as $linkkey => $link){
                        $h_bol_multiple_link[ $linkkey]['value'] = common::deCode($link['value'], 'E');
                    }
                    $rs[$key]["h_bol"] = $h_bol_multiple_link;
                    $rs[$key]["kln_pic"] = empty($val['kln_pic']) ? array() : explode(";", $val['kln_pic']);
                }
                $arrTmp = array('searchData' => $rs,
                        'is_employee' => _isApexLogin(), 
                        'All' =>$rc,
                        'pending_approval_rc' =>$pending_approval_rc,
                        'approved_rc' =>$approved_rc,
                        'rejected_rc' =>$rejected_rc,
                        'cancelled_rc' =>$cancelled_rc,
                        'rc' => $search_rc,
                        'ps' => $ps,
                        'cp' => $cp,
                        'tp' => $tp);
                common::echo_json_encode(200,$arrTmp);
                exit();
            }else{
                $arrTmp = array('searchData' => array(),
                        'is_employee' => _isApexLogin(), 
                        'All' =>$rc,
                        'pending_approval_rc' =>$pending_approval_rc,
                        'approved_rc' =>$approved_rc,
                        'rejected_rc' =>$rejected_rc,
                        'cancelled_rc' =>$cancelled_rc,
                        'rc' => $search_rc,
                        'ps' => $ps,
                        'cp' => $cp,
                        'tp' => $tp);
                common::echo_json_encode(200,$arrTmp);
                exit();
            }
        }

        if ($operate == "search_calendar"){ 

            $month = common::check_input($_POST ['month']); //current_page
            $consignee = common::check_input($_POST ['consignee']);
            $sqlWhere = ' where ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);

            $sqlWhere_bk= $sqlWhere;
            $sqlKNDDWhere = "and exists(select 1 from public.kln_ocean ". $sqlWhere_bk."  and kln_ocean.serial_no = any(kln_destination_delivery.h_serial_no) limit 1)";
            if(!empty($consignee)){
                $sqlKNDDWhere .= " and consignee ilike '%".$consignee."%'";
            }

            //兜底规则
            $sqlWhere .= " and eta >= now() - INTERVAL '3 months' and eta <= now() + INTERVAL '1 months' and 1<>1 ";
            if(!empty($consignee)){
                $sqlWhere .= " and consignee ilike '%".$consignee."%'";
            }
            $search_calendar_sql = common::excuteOneSql("select item_value from public.config where item = 'Destination_Bookings_Calendar_Sql'");

            //日期计算
            $month = common::excuteObjectSql("SELECT CURRENT_DATE AS access_date,  
                                        (to_date('".$month."', 'MM/YYYY'))::date AS month_start,
                                (date_trunc('month', to_date('".$month."', 'MM/YYYY')) + interval '1 month - 1 day')::date AS month_end");

            $search_calendar_sql = str_replace('<{access_date}>', $month['access_date'], $search_calendar_sql);
            $search_calendar_sql = str_replace('<{month_start}>', $month['month_start'], $search_calendar_sql);
            $search_calendar_sql = str_replace('<{month_end}>', $month['month_end'], $search_calendar_sql);
            $search_calendar_sql = str_replace('<{sqlWhere}>', $sqlWhere, $search_calendar_sql);
            $search_calendar_sql = str_replace('<{sqlKNDDWhere}>', $sqlKNDDWhere, $search_calendar_sql);
            
            //error_log($search_calendar_sql);
            $result = common::excuteOneSql($search_calendar_sql);
            $result = json_decode($result,true);

            foreach($result as $index => $_result){
                $size_arr = array();
                foreach($_result['bookingDetail'] as $bookingDetail){
                    $oc_size_arr = explode(',', $bookingDetail['oc_size']);
                    foreach($oc_size_arr as $size){
                        if (!empty($size)) {
                            // 如果已经存在，加1；否则初始化为1
                            if (isset($size_arr[$size])) {
                                $size_arr[$size]++;
                            } else {
                                $size_arr[$size] = 1;
                            }
                        }
                    }
                }
                $output = [];
                foreach ($size_arr as $size => $count) {
                    $output[] = $size . '*' . $count;
                }
                $result[$index]['ctnrSize'] = $output;
            }
            $result['tmp_search'] = common::deCode($search_calendar_sql, 'E');
            common::echo_json_encode(200,$result);
            exit();
        }

        if ($operate == "search_shipment"){
            $data = $this->search_shipment();
            if(!empty($data['msg'])){
                $arrTmp = array("msg" =>$data['msg']);
            }else{
                $arrTmp =  array("msg" =>"success","data"=>$data['data']); 
            }
            common::echo_json_encode(200, $arrTmp);
            exit();
        }

        if ($operate == "add"){
            $serial_no = common::deCode($_POST['serial_no'], 'D');

            //前端按钮 后台权限拦截
            if(!empty($serial_no)){
                common::checkedActionLegal($serial_no,$operate);
            }else{
                if(_isApexLogin()){
                    $data = array("msg" =>"Employees cannot create booking");
                    common::echo_json_encode(200,$data); 
                    exit(); 
                }
            }

            $booking_data = array();
            if(!empty($serial_no)){
                //to_char(delivery_date, 'MM/DD/YYYY') AS _delivery_date, 
                $sql = "select *,
                    to_char(delivery_date, 'HH24:MI') AS _delivery_time 
                from public.kln_destination_delivery where serial_no = '$serial_no'";
                $booking_data  = common::excuteObjectSql($sql);
                $booking_data["delivery_time"] = $booking_data["_delivery_time"];
                $booking_data["delivery_address_detail"] = json_decode($booking_data["delivery_address_detail"],true);
                $shipments = $this->search_shipment_with_booking($booking_data['serial_no'],$booking_data['h_serial_no'],$booking_data['ctnr']);
            } else {
                $shipments = $this->search_shipment();
            }
            if(!empty($shipments['msg'])){
                $data = array("msg" =>$shipments['msg']);
                common::echo_json_encode(200,$data);                
                exit(); 
            }
            $booking_data['tableData'] = $shipments['data'];
            $data = array("msg" =>"success","data"=>$booking_data);
            common::echo_json_encode(200,$data);                
            exit(); 
        }

        if ($operate == "save"){
            $serial_no = common::deCode($_POST['serial_no'], 'D');
            
            $booking_no = $_POST['booking_no'];  
            $manifest_type = $_POST['manifest_type'];  
            $h_bol = $_POST['h_bol']; 
            $h_serial_no  = $_POST['h_serial_no'];
            $order_from  = $_POST['order_from']; 
            $m_bol = $_POST['m_bol'];  
            $ctnr = $_POST['ctnr'];
            $kln_pic = $_POST['dc_kln_pic'];       
            $consignee = $_POST['consignee'];
            $consignee_id = $_POST['consignee_id'];
            $dc_country = $_POST['dc_country'];
            $pakages = $_POST['pakages'];
            $oc_size = $_POST['oc_size'];

            $recommended_delivery_window_date_from = $_POST['recommended_delivery_from_date'];
            $recommended_delivery_window_date_to = $_POST['recommended_delivery_to_date'];
            $recommended_delivery_from = $_POST['recommended_delivery_from'];
            $recommended_delivery_to = $_POST['recommended_delivery_to'];
            $modify_reason = common::check_input($_POST['modify_reason']);
            

            $delivery_date = $_POST['delivery_date'];
            $delivery_time = $_POST['delivery_time'];
            $delivery_mode = common::check_input($_POST['delivery_mode']);
            //这status 编辑只有两种情况，新加，和在拒绝后 用户重新编辑 状态都是Pending Approval
            $status = "Pending Approval";
            $is_new = false;
            if(empty($serial_no)){
                $is_new = true;
            }
        
            $delivery_address = common::check_input($_POST['delivery_address']);
            $special_requirements = common::check_input($_POST['special_requirements']);
            $delivery_reference = common::check_input($_POST['delivery_reference']);


            //检查不能重复添加booking  这里空运没问题，但是会遇到相同hbol 不同的柜子情况，本质上来说就是一条
            //$sqlWhere = ' where ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
            //兜底规则
            //$sqlWhere .= " and  eta >= now() - INTERVAL '6 months' and eta <= now() + INTERVAL '6 months'";
            //and exists(select 1 from public.kln_ocean oo ". $sqlWhere."  and oo.serial_no = any(kde.h_serial_no) limit 1)

            $ckeckedSql = "with save_table as (
                    select regexp_split_to_table('".utils::implode(',',$h_serial_no)."', ',') as serial_no
                ),
                save_table_ctnr as (
                    select regexp_split_to_table('".utils::implode(',',$ctnr)."', ',') as ctnr
                )
                select h_bol,ctnr from public.kln_destination_delivery kde
                    where status not in ('Cancel','Reject')
                        and exists(select 1 from save_table where save_table.serial_no = any(kde.h_serial_no))
                        and (exists(select 1 from save_table_ctnr where save_table_ctnr.ctnr = ANY(regexp_split_to_array(kde.ctnr, ',')) 
                            or COALESCE(kde.ctnr,'') = ''))";
            //代表编辑
            if(!empty($serial_no)){
                $ckeckedSql .=" and kde.serial_no <> '$serial_no'";
            }            
            
            $checkeData = common::excuteObjectSql($ckeckedSql);
            if(!empty($checkeData['h_bol'])){
                $data = array("msg" =>"The HBOL NO: ".$checkeData['h_bol'] .", Destination Delivery cannot be created repeatedly.");
                common::echo_json_encode(200,$data); 
                exit();
            }

            $sql = "";
            //代表修改
            if(!empty($serial_no)){
                $updateSqlSet = " modify_by = '"._getLoginName()."',update_time = now()";

                if (!empty($status)) {
                    $updateSqlSet.= ", status = '$status' ";
                }
                if (!empty($delivery_address)) {
                    $delivery_address_detail = common::check_input(json_encode(common::returnDAddress(),JSON_UNESCAPED_UNICODE));
                    $updateSqlSet.= ", delivery_address = '$delivery_address' ";
                    $updateSqlSet.= ", delivery_address_detail = '$delivery_address_detail' ";
                }
                if (!empty($special_requirements)) {
                    $updateSqlSet.= ", special_requirements = '$special_requirements' ";
                }
                if (!empty($delivery_reference)) {
                    $updateSqlSet.= ", delivery_reference = '$delivery_reference' ";
                }
                if (!empty($modify_reason)) {
                    $updateSqlSet.= ", modify_reason = '$modify_reason' ";
                }
                if (!empty($delivery_date)) {
                    $delivery_date = empty($_POST['delivery_date']) ? "null": "'".common::usDate2sqlDate($_POST['delivery_date'])." ".$delivery_time."'";
                    $updateSqlSet.= ", delivery_date =  $delivery_date";
                }

                //修改记录临时表
                common::saveDAddressTempTable($serial_no,$consignee_id);
                //代表update 
                $sql .= "update public.kln_destination_delivery set ".$updateSqlSet."
                    where serial_no = '$serial_no';"; 

                //记录log
                $sql .="INSERT INTO public.kln_destination_delivery_operation_log(
                                serial_no, action,notes, create_by, created_time, created_zone)
                        VALUES ('$serial_no', 'Submit','".$modify_reason."', '"._getLoginName()."', now(), ''); ";        
            } else {  
                //array("msg"=>"","result"=>$result);
                $saveDataArr = $this->groupShipments($booking_no,$manifest_type,$h_bol,$h_serial_no,$order_from,$m_bol,$ctnr,$kln_pic,$consignee,$consignee_id,
                    $recommended_delivery_window_date_from, $recommended_delivery_window_date_to,
                    $recommended_delivery_from,$recommended_delivery_to,$dc_country,$pakages,$oc_size);
                
                if(!empty($saveDataArr['msg'])){
                    $data = array("msg" =>$saveDataArr['msg']);
                    common::echo_json_encode(200,$data); 
                    exit();
                }

                $saveData = $saveDataArr['result'];
                foreach($saveData as $sData){
                    //DB2510210001-->DB+年+月+日+自增序號四位
                    //获取自然序列
                    $sequence = common::getDeliveryBookingSequence();  
                    $booking_no_new = $sequence;

                    $serial_no = common::uuid();

                    // 使用 PostgreSQL 的 ARRAY 语法
                    $tags_sql = "ARRAY['" . implode("','", array_map('pg_escape_string', $sData['h_serial_no'])) . "']";
                    // 使用 PostgreSQL 的 ARRAY 语法
                    $country_sql = "ARRAY['" . implode("','", array_map('pg_escape_string', $sData['dc_country'])) . "']";

                    $recommended_delivery_window_date_from = "";
                    if(empty($sData['recommended_delivery_window_date_from'])){
                        $recommended_delivery_window_date_from = "null";
                    } else {
                        $recommended_delivery_window_date_from = "'".$sData['recommended_delivery_window_date_from']."'";
                    }

                    $recommended_delivery_window_date_to = "";
                    if(empty($sData['recommended_delivery_window_date_to'])){
                        $recommended_delivery_window_date_to = "null";
                    } else {
                        $recommended_delivery_window_date_to = "'".$sData['recommended_delivery_window_date_to']."'";
                    }

                    $delivery_date = empty($_POST['delivery_date']) ? "null": "'".common::usDate2sqlDate($_POST['delivery_date'])." ".$delivery_time."'";

                    //修改记录临时表,有值代表选择的add
                    $_sync_key_add = common::saveDAddressTempTable($serial_no,$sData['consignee_id']);
                    $delivery_address_detail = common::check_input(json_encode(common::returnDAddress($_sync_key_add),JSON_UNESCAPED_UNICODE));

                    $sql .= "INSERT INTO public.kln_destination_delivery(
                            serial_no, h_serial_no,address_country,
                            booking_no, h_bol,h_bol_multiple_link,
                            m_bol, ctnr,
                            consignee, delivery_date, delivery_mode, status, delivery_address,delivery_address_detail, special_requirements,delivery_reference, 
                            recommended_delivery_window_date_from, recommended_delivery_window_date_to, d_address_change_log,
                            recommended_delivery_from, recommended_delivery_to,kln_pic,
                            create_by, created_time, modify_by, update_time,total_qty,oc_size)
                    VALUES ('$serial_no', ".$tags_sql.",".$country_sql.",
                            '".$booking_no_new."', '".utils::implode(',',$sData['h_bol'])."','".common::check_input(json_encode($sData['h_bol_multiple_link']))."',
                            '".utils::implode(',',$sData['m_bol'])."', '".utils::implode(',',$sData['ctnr'])."',
                            '".$sData['consignee']."', $delivery_date,'$delivery_mode','$status', '$delivery_address','$delivery_address_detail', '$special_requirements','$delivery_reference',
                            $recommended_delivery_window_date_from, $recommended_delivery_window_date_to,'',
                            '".$sData['recommended_delivery_from']."', '".$sData['recommended_delivery_to']."','".$sData['kln_pic']."',
                            '"._getLoginName()."', now(), '"._getLoginName()."', now(),'".$sData['pakages']."','".utils::implode(',',$sData['oc_size'])."');";
                    //记录log
                    $sql .="INSERT INTO public.kln_destination_delivery_operation_log(
                                    serial_no, action,notes, create_by, created_time, created_zone)
                            VALUES ('$serial_no', 'Submit','', '"._getLoginName()."', now(), ''); ";   
                }
            }

            if(!empty($sql)){
                $rs = common::excuteUpdateSql($sql);
                if ($rs === FALSE){
                    $data = array("msg" =>"error");
                    common::echo_json_encode(500,$data); 
                    exit();
                }
            }

            if(!$is_new){
                //状态变更时，发送邮件提醒 
                $status = "Modify";
                $data = common::excuteObjectSql("select *, TO_CHAR(delivery_date, 'Mon-DD-YYYY HH24:MI') as _delivery_date,
                        TO_CHAR(created_time, 'Mon-DD-YYYY') as _created_time,
                        TO_CHAR(update_time, 'Mon-DD-YYYY') as _update_time
                    from public.kln_destination_delivery where serial_no = '$serial_no'");
                $shipmentsData = $this->search_shipment_with_booking($data['serial_no'],$data['h_serial_no'],$data['ctnr']);
                $email_sql = common::sendDestinationDeliveryReminder($data,$shipmentsData,$status);       
                if(!empty($email_sql)){
                    common::excuteUpdateSql($email_sql);
                }     
            } else {
                $status = "Submit";
                $data = common::excuteObjectSql("select *, TO_CHAR(delivery_date, 'Mon-DD-YYYY HH24:MI') as _delivery_date,
                        TO_CHAR(created_time, 'Mon-DD-YYYY') as _created_time,
                        TO_CHAR(update_time, 'Mon-DD-YYYY') as _update_time
                    from public.kln_destination_delivery where serial_no = '$serial_no'");
                $shipmentsData = $this->search_shipment_with_booking($data['serial_no'],$data['h_serial_no'],$data['ctnr']);
                $email_sql = common::sendDestinationDeliveryReminder($data,$shipmentsData,$status);       
                if(!empty($email_sql)){
                    common::excuteUpdateSql($email_sql);
                }     
            }
            $data = array("msg" =>"success");
            common::echo_json_encode(200,$data); 
            exit();
        }

        /**
         * 审核 Approve  or Reject  Cancel  Pending Approval
        */
        if ($operate == "review"){
            $serial_no = common::deCode($_POST['serial_no'], 'D');
            $status = common::check_input($_POST['status']);
            $notes = common::check_input($_POST['notes']);
            $action_user = _getLoginName();

            //前端按钮 后台权限拦截
            common::checkedActionLegal($serial_no,$operate);
            global $db;
            $db->StartTrans();
            try {
                $sql = "";
                $do_serial_nos = "";
                //审核通过时，才取下放当时应用修改的地址
                if ($status == "Approve") {
                    $delivery_booking = common::excuteObjectSql("select *,array_to_json(address_country) as address_country,
                        array_to_json(h_serial_no) as h_serial_no_json,
                        TO_CHAR( delivery_date, 'HH24:MI') AS _delivery_time,
                        delivery_date::date AS _delivery_date
                        from public.kln_destination_delivery where serial_no = '$serial_no'"); 

                    $delivery_address_detail = json_decode($delivery_booking["delivery_address_detail"],true);
                    $onine_address = common::excuteListSql("select * from public.contacts_address_online 
                        where (delivery_serial_no = '$serial_no' or sync_key = '".$delivery_address_detail['sync_key']."')");

                    //$sql .= "delete from public.contacts_address_online where delivery_serial_no = '$serial_no';";
                    $sql .=common::returnDAddressRecord($delivery_booking["address_country"],$onine_address,$delivery_address_detail);

                    //Booking自動同步到Ksmart WO 的逻辑   保存在do_header和do_details表里
                    $sqlData =common::saveWorkOrder($delivery_booking);
                    $sql .= $sqlData['sql'];
                    $do_serial_nos = utils::implode(';',$sqlData['do_serial_no_arr']);
                }
                if ($status == "Reject" || $status == "Cancel") {
                    //这种情况，清除当前用户对这一票的修改和添加的信息
                    $sql .= "delete from public.contacts_address_online where delivery_serial_no = '$serial_no';";
                }

                $sql .= "update public.kln_destination_delivery set do_serial_no = '$do_serial_nos', status = '$status', modify_by = '"._getLoginName()."',update_time = now() where serial_no = '$serial_no';";
                $sql .= "INSERT INTO public.kln_destination_delivery_operation_log(
                                serial_no, action,notes, create_by, created_time, created_zone)
                        VALUES ('$serial_no', '$status','$notes', '$action_user', now(), ''); ";
 
                $db->Execute($sql) or ((!$db->ErrorMsg()) or error_log(common::dbLog($db, $sql), 0));
                $rs = $db->CompleteTrans();
            } catch (Exception $e) {
                error_log("review:".$e->getMessage());
                $db->RollbackTrans();
            } 

            if ($rs === FALSE){
                $data = array("msg" => "Update Error");
                common::echo_json_encode(200,$data);
            } else{
                //状态变更时，发送邮件提醒 
                $data = common::excuteObjectSql("select *, 
                            TO_CHAR(delivery_date, 'Mon-DD-YYYY HH24:MI') as _delivery_date,
                            TO_CHAR(created_time, 'Mon-DD-YYYY') as _created_time,
                            TO_CHAR(update_time, 'Mon-DD-YYYY') as _update_time 
                        from public.kln_destination_delivery where serial_no = '$serial_no'");
                $shipmentsData = $this->search_shipment_with_booking($data['serial_no'],$data['h_serial_no'],$data['ctnr']);
                $email_sql = common::sendDestinationDeliveryReminder($data,$shipmentsData,$status);    
                if(!empty($email_sql)){
                    common::excuteUpdateSql($email_sql);
                }     
                $data = array("msg" =>"success");
                common::echo_json_encode(200,$data);
            }       
        }

        /**
         * 邮件留言初始
        */
        if ($operate == "email_message_init"){
            try {
                $email_uuid = common::deCode($_POST['serial_no'], 'D');
                $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 == "email_message_board"){
            //前端按钮 后台权限拦截
            $email_uuid = common::deCode($_POST['serial_no'], 'D');
            common::checkedActionLegal($email_uuid,$operate);

            try {
                $content = $_POST["content"];
                $content = common::check_input($content);
                $content = urldecode($content);

                $text = $_POST["text"];
                $web_content = urldecode($text);

                $serial_no = common::uuid();
                $email_uuid = common::deCode($_POST['serial_no'], 'D');
                $add_by = _getLoginName();
                $refer_id = 0;
               
                $from_email = "US.KApex.Online@kerryapex.com";
                $to_email = $_POST["kln_pic"];

                $cc_email = "";
                $communication_cc = $_POST["communication_cc"];
                $communication_cc = trim($communication_cc);
                if (!empty($communication_cc)) {
                    $communication_cc = common::check_input($communication_cc);
                    $cc_email = $communication_cc;
                }
                $user_from = _getLoginName();

                $h_bol_str = array();
                foreach($_POST["h_bol"] as $obj){
                    $h_bol_str[] = $obj['key'];
                }
                
                //邮件发送
                $title = "Destination Delivery from " . _getLoginName() . ", Shipment No: " . utils::implode(',',$h_bol_str);
                $emailContent = $content;

                common::excuteUpdateSql("insert into public.email_record (type,title,from_email,to_email,cc_email,content,insert_date)values('Delivery_Comm','$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();
            }
        }

        /**
         * view detail and log
        */
        if ($operate == "view_detail"){
            $serial_no = common::deCode($_POST['serial_no'], 'D');
            //前端按钮 后台权限拦截
            common::checkedActionLegal($serial_no,$operate);

            $data = common::excuteObjectSql("select * from public.kln_destination_delivery where serial_no = '$serial_no'");

            //$_shipmentsData = array();
            $shipmentsData = $this->search_shipment_with_booking($data['serial_no'],$data['h_serial_no'],$data['ctnr']);
            // foreach($shipmentsData["data"] as $shipment){
            //     $_shipmentsData[] = array("HBOL No." => $shipment['h_bol'],"Container No." => $shipment['ctnr'],
            //         "Service Type" => $shipment['service'],"ETA" => $shipment['eta'],
            //         "Recommended Delivery Date" => $shipment['date_range']);
            // }

            $_operation_log = array();
            $operation_log = common::excuteListSql("select * from public.kln_destination_delivery_operation_log where serial_no = '$serial_no' order by id desc");
            foreach($operation_log as $log){
                $action = $log['action'] == "Pending Approval" ? "Submit" : $log['action'];
                $_operation_log[] = array("time" => $log['created_time'],"timezone" => $log['created_zone'],
                    "label" => $action,"createdBy" => $log['create_by'],
                    "tips" => $log['notes']);
            }

            $retData = $data;
            $retData["status"] = common::deliveryStatusConvert($retData["status"]);
            $retData['shipmentsData'] = $shipmentsData["data"];
            $retData['operation_log'] = $_operation_log;

            $data = array("msg" =>"success","data"=>$retData);
            common::echo_json_encode(200,$data);                
            exit(); 
        }

        /**
         * Delivery date
        */
        if ($operate == "delivery_date_load"){
            $sqlWhere = ' where ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
            //兜底规则
            $sqlWhere .= " and eta >= now() - INTERVAL '6 months' and eta <= now() + INTERVAL '6 months'";

            $sqlDeliveryWhere = " 1=1"; 
            //$sqlWhere_befrom_filterTag = $sqlWhere;
            $sql = "select delivery_date::date as delivery_date,
                        sum(case when status ='Pending Approval' then 1 else 0 end) as pending_approval_rc,
                        sum(case when status ='Approve' then 1 else 0 end) as approved_rc
                    from public.kln_destination_delivery kd 
                        where ".$sqlDeliveryWhere."
                            and exists(select 1 from public.kln_ocean ". $sqlWhere."  and kln_ocean.serial_no = any(kd.h_serial_no) limit 1)
                    group by delivery_date::date  order by delivery_date::date desc";
            $rs  = common::excuteListSql($sql);
            error_log("delivery_date_load:".$sql);        
            $dateArr = new stdClass();
            foreach($rs as $val){
                //$dateArr[] = array($val['delivery_date'] => array("pending"=>$val['pending_approval_rc'],"approved"=>$val['approved_rc']));
                $dateArr->{$val['delivery_date']} = new stdClass();
                $dateArr->{$val['delivery_date']}->pending = $val['pending_approval_rc'];
                $dateArr->{$val['delivery_date']}->approved = $val['approved_rc'];
            }
            $data =  array("msg" =>"success","data"=>$dateArr); 
            common::echo_json_encode(200,$data); 
        }

        /*
        * download  batch P AND CI document 
        */
        if ($operate == "batch_download_ci_p_file") {
            $this->_batch_download_ci_p_file();
        }

    }

    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;
    }

    function search_shipment(){
        $sqlWhere_common = ' where ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
        //根據用戶的賬號權限所能看到的全部shipment數據的目的地站點，去匹配是否有開通destination delivery的服務，如果未開通，則展示此提示頁面
        //兜底规则
        $sqlWhere = $sqlWhere_common;
        $sqlWhere .= " and  eta >= now() - INTERVAL '3 months' and eta <= now() + INTERVAL '1 months'";
        $checkOpenSql = "select station from public.kln_destination_delivery_config dc
                            where exists(select 1 from  
                                    public.kln_ocean oo ". $sqlWhere." 
                                        and oo.agent in (select regexp_split_to_table(dc.station, E',')) limit 1
                                )";
        $config = common::excuteListSql($checkOpenSql);
        if(empty($config)){
            return array("msg"=>"Destination Delivery Service Not Available","data"=>"");
        }
        
        //根據用戶的賬號權限所能看到的全部shipment數據的目的地站點，去匹配是否有開通destination delivery的服務，如果有開通，但沒有符合條件的shipment，則展示此提示頁面
        $sqlWhere = $sqlWhere_common;
        $sqlWhere .= " and  eta >= now() - INTERVAL '3 months' and eta <= now() + INTERVAL '1 months'";

        $text_search = $_REQUEST['text_search'];
        if (!empty($text_search)){
            $sqlWhere .= " and (lower(oo.h_bol) like '%".strtolower($text_search)."%'
                or lower(oo.po_no) like '%".strtolower($text_search)."%'
                or lower(oo.ctnrs) like '%".strtolower($text_search)."%')";
        }

        //vessel name
        $vessel = $_REQUEST['vessel'];
        if (!empty($vessel)){
            $sqlWhere .= " and lower(vessel) like '%".strtolower($vessel)."%'";
        }

        $consignee = $_REQUEST['consignee'];
        if (!empty($consignee)){
            $sqlWhere .= " and lower(consignee) like '%".strtolower($consignee)."%'";
        }

        $shipper = $_REQUEST['shipper'];
        if (!empty($shipper)){
            $sqlWhere .= " and lower(shipper) like '%".strtolower($shipper)."%'";
        }

        if (isset($_REQUEST['eta_start']) && !empty($_REQUEST['eta_start']))
            $sqlWhere .= " and eta >= '" . common::usDate2sqlDate($_REQUEST['eta_start']) . " 00:00:00'";
        if (isset($_REQUEST['eta_end']) && !empty($_REQUEST['eta_end']))
            $sqlWhere .= " and eta <= '" . common::usDate2sqlDate($_REQUEST['eta_end']) . " 23:59:59'";

        if (isset($_REQUEST['ata_start']) && !empty($_REQUEST['ata_start']))
            $sqlWhere .= " and ata >= '" . common::usDate2sqlDate($_REQUEST['ata_start']) . " 00:00:00'";
        if (isset($_REQUEST['ata_end']) && !empty($_REQUEST['ata_end']))
            $sqlWhere .= " and ata <= '" . common::usDate2sqlDate($_REQUEST['ata_end']) . " 23:59:59'";

        $recommended_delivery_sql = ' 1=1 ';
        if (isset($_REQUEST['recommended_delivery_date']) && !empty($_REQUEST['recommended_delivery_date']))
            $recommended_delivery_sql .= " and ((recommended_delivery_from_date is not null and recommended_delivery_from_date <= '" . common::usDate2sqlDate($_REQUEST['recommended_delivery_date']) . " 00:00:00')
                                                OR 
                                                recommended_delivery_from_date is null)";
        if (isset($_REQUEST['recommended_delivery_date']) && !empty($_REQUEST['recommended_delivery_date']))
            $recommended_delivery_sql .= " and ((recommended_delivery_to_date is not null and recommended_delivery_to_date >= '" . common::usDate2sqlDate($_REQUEST['recommended_delivery_date']) . " 00:00:00')
                                                OR 
                                                recommended_delivery_to_date is null)";

        $sql = "with 
            ooc as (
                select oo.*,
                    dc.serial_no as dc_serial_no,
                    dc.country as dc_country,
                    dc.kln_pic as dc_kln_pic,
                    dc.recommended_delivery as dc_recommended_delivery
                from (select oo.serial_no,
                            oo.h_bol,
                            oo.m_bol,
                            oo.service,
                            oo.po_no,
                            oo.transport_mode,
                            oo.qty,
                            oo.qty_uom,
                            oo.piece_count,
                            oo.cbm,
                            oo.vessel,
                            oo.voyage,
                            oo.carrier,
                            oo.fport_of_loading_un,
                            oo.mport_of_discharge_un,
                            oo.eta,
                            oo.ata,
                            oo.etd,
                            oo.atd,
                            oo.shipper,
                            oo.consignee_id,
                            oo.consignee,
                            oo.booking_no,
                            oo.agent,
                            oo.order_from 
                        from public.kln_ocean oo ". $sqlWhere ."
                    ) oo
                    INNER JOIN (
                        SELECT 
                            serial_no, 
                            country, 
                            kln_pic,
                            booking_window,
                            booking_window_date_start,
                            booking_window_date_end,
                            recommended_delivery,
                            unnest(string_to_array(station, ',')) as station_code
                        FROM public.kln_destination_delivery_config 
                    )dc on oo.agent = dc.station_code
                    where case when dc.booking_window = 'Restrictions_ETD_ATD' and COALESCE(dc.booking_window_date_start,'')<>'' and 	COALESCE(dc.booking_window_date_end,'')<>''
                                then COALESCE(atd, etd) >= (NOW() - (dc.booking_window_date_start ||' days')::INTERVAL)::date and  COALESCE(atd, etd) <= (NOW() + (dc.booking_window_date_end ||' days')::INTERVAL)::date
                            when dc.booking_window = 'Restrictions_ETA_ATA' and COALESCE(dc.booking_window_date_start,'')<>'' and 	COALESCE(dc.booking_window_date_end,'')<>''
                                then COALESCE(ata, eta) >= (NOW() - (dc.booking_window_date_start ||' days')::INTERVAL)::date and  COALESCE(ata, eta) <= (NOW() + (dc.booking_window_date_end ||' days')::INTERVAL)::date
                        else 1=1 end 
            ),
            matched_data as (
                select
                    ooc.serial_no as h_serial_no,
                    ooc.h_bol,
                    ooc.m_bol,
                    oc.ctnr,
                    ooc.service,
                    ooc.po_no,
                    (select reference_no from public.ocean o where o.serial_no = ooc.serial_no  limit 1) as reference_no,
                    ooc.transport_mode,
                    oc.qty::text as pakages, 
                    oc.unit as package_type ,
                    oc.grs_kgs::text as kgw,
                    oc.cbm as volume,
                    oc.size as oc_size,
                    ooc.vessel,
                    ooc.voyage,
                    ooc.carrier,
                    ooc.fport_of_loading_un,
                    ooc.mport_of_discharge_un,
                    ooc.eta,
                    ooc.ata,
                    ooc.shipper,
                    ooc.consignee_id,
                    ooc.consignee,
                    ooc.booking_no,
                    ''::text as manifest_type,
                    dc_serial_no,
                    dc_country,
                    dc_kln_pic,
                    ooc.agent,
                    ooc.order_from,
                    dc_recommended_delivery
                from ooc  
                    LEFT JOIN public.oc_container oc ON oc.serial_no::text = ooc.serial_no::text  
                where  ooc.order_from = 'public' and transport_mode = 'sea'
                union all
                select 
                    ooc.serial_no as h_serial_no,
                    ooc.h_bol,
                    ooc.m_bol,
                    oc.ctnr,
                    ooc.service,
                    ooc.po_no,
                    (select reference_no from sfs.ocean o where o.serial_no = ooc.serial_no  limit 1) as reference_no,
                    ooc.transport_mode,
                    oc.qty::text as pakages, 
                    oc.unit as package_type ,
                    oc.grs_kgs::text as kgw,
                    oc.cbm as volume,
                    oc.size as oc_size,
                    ooc.vessel,
                    ooc.voyage,
                    ooc.carrier,
                    ooc.fport_of_loading_un,
                    ooc.mport_of_discharge_un,
                    ooc.eta,
                    ooc.ata,
                    ooc.shipper,
                    ooc.consignee_id,
                    ooc.consignee,
                    ooc.booking_no,
                    ''::text as manifest_type,
                    dc_serial_no,
                    dc_country,
                    dc_kln_pic,
                    ooc.agent,
                    ooc.order_from,
                    dc_recommended_delivery
                from ooc  
                    LEFT JOIN sfs.oc_container oc ON oc.serial_no::text = ooc.serial_no::text  
                where  ooc.order_from = 'sfs' and transport_mode = 'sea'
                union all
                select 
                    ooc.serial_no as h_serial_no,
                    ooc.h_bol,
                    ooc.m_bol,
                    '' as ctnr,
                    ooc.service,
                    ooc.po_no,
                    '' as reference_no,
                    ooc.transport_mode,
                    ooc.qty as pakages, 
                    ooc.qty_uom as package_type ,
                    ooc.piece_count as kgw,
                    ooc.cbm as volume,
                    '' as oc_size,
                    ooc.vessel,
                    ooc.voyage,
                    ooc.carrier,
                    ooc.fport_of_loading_un,
                    ooc.mport_of_discharge_un,
                    ooc.eta,
                    ooc.ata,
                    ooc.shipper,
                    ooc.consignee_id,
                    ooc.consignee,
                    ooc.booking_no,
                    ''::text as manifest_type,
                    dc_serial_no,
                    dc_country,
                    dc_kln_pic,
                    ooc.agent,
                    ooc.order_from,
                    dc_recommended_delivery
                from ooc
                where ooc.transport_mode = 'air'
            ),
            shipment_table as(
                select md.*,
                    CASE 
                        WHEN md.manifest_type = 'BCN' THEN (select string_agg(trim(COALESCE(h_bol,'')), ';'::text ORDER BY h_bol) from public.kln_ocean where m_bol = md.m_bol)
                        ELSE '' END
                    As same_mbol,    
                    CASE 
                        WHEN  r.recommended_delivery_from IS NOT NULL AND r.recommended_delivery_to IS NOT NULL THEN
                            to_char((COALESCE(ata, eta) + (r.recommended_delivery_from ||' days')::INTERVAL)::date, 'YYYY.MM.DD') 
                            || '-' || 
                            to_char((COALESCE(ata, eta) + (r.recommended_delivery_to ||' days')::INTERVAL)::date, 'YYYY.MM.DD') 
                        WHEN  r.recommended_delivery_from IS NULL AND r.recommended_delivery_to IS NOT NULL THEN
                            '-' || 
                            to_char((COALESCE(ata, eta) + (r.recommended_delivery_to || ' days')::INTERVAL)::date, 'YYYY.MM.DD')
                        WHEN r.recommended_delivery_from IS NOT NULL AND r.recommended_delivery_to IS NULL THEN   
                            to_char((COALESCE(ata, eta) + (r.recommended_delivery_from || ' days')::INTERVAL)::date, 'YYYY.MM.DD')
                            || '-' 
                        ELSE '' END 
                    AS date_range,
                    r.recommended_delivery_from, 
                    r.recommended_delivery_to,
                    CASE 
                        WHEN r.recommended_delivery_from IS NOT NULL THEN
                            (COALESCE(ata, eta) + (r.recommended_delivery_from ||' days')::INTERVAL)::date
                        ELSE null END
                    AS recommended_delivery_from_date,

                    CASE 
                        WHEN r.recommended_delivery_to IS NOT NULL THEN
                            (COALESCE(ata, eta) + (r.recommended_delivery_to ||' days')::INTERVAL)::date
                        ELSE null END
                    AS recommended_delivery_to_date
                from matched_data md
                LEFT JOIN LATERAL (
                    SELECT 
                        r.*,
                        CASE 
                          WHEN md.transport_mode = 'sea' THEN
                              CASE 
                                  WHEN r.ports != 'ALL' AND r.carrier != 'ALL' THEN 4
                                  WHEN r.ports != 'ALL' AND r.carrier = 'ALL'  THEN 3
                                  WHEN r.ports = 'ALL'  AND r.carrier != 'ALL' THEN 2
                                  ELSE 1
                              END
                          WHEN md.transport_mode = 'air' THEN
                              CASE 
                                  WHEN r.ports != 'ALL' THEN 2   
                                  ELSE 1                          
                              END
                          ELSE 0
                      END AS match_score
                    FROM public.kln_destination_delivery_rule r 
                    WHERE lower(md.dc_recommended_delivery) <> 'no_recommended' 
                        and r.recommended_delivery_serial_no = md.dc_serial_no
                        AND r.mode_type = md.transport_mode
                        AND (COALESCE(r.ports,'') = 'ALL' OR COALESCE(r.ports,'') ilike '%'|| md.mport_of_discharge_un ||'%')
                        AND (COALESCE(r.carrier,'') = 'ALL' OR COALESCE(r.carrier,'') ilike '%'|| md.carrier ||'%' )
                        ORDER BY match_score DESC,priority asc
                        LIMIT 1
                    ) r ON true
                order by md.eta desc,md.h_bol asc
            )
            select * from shipment_table 
                where not exists(select 1 from  public.kln_destination_delivery kde 
                            where status not in ('Cancel','Reject') 
                                    and shipment_table.h_serial_no = any(kde.h_serial_no)
                                    and (shipment_table.ctnr = ANY(regexp_split_to_array(kde.ctnr, ',')) or COALESCE(kde.ctnr,'') = '' )) 
                and  ".$recommended_delivery_sql;

        $rs = common::excuteListSql($sql);
        error_log($sql);
        if(empty($rs)){
            return array("msg"=>"No Eligible Shipments for Booking","data"=>"");
        }
        return array("msg"=>"","data"=>$rs);    
    }

    /**
     * 不依赖配置的规则查询,如果规则已经移除或者修改不在查询范围内，開通期間創建的booking數據正常保留和展示
    */
    function search_shipment_with_booking($serial_no,$h_serial_no,$ctnr){

        $sqlCtnr = "";
        if(!empty($ctnr)){
            $sqlCtnr = "and oc.ctnr in (select regexp_split_to_table('".$ctnr."', ','))";
        }

        $sqlWhere =  "where oo.serial_no = ANY('".$h_serial_no."'::TEXT[])";
        $sql = "with ooc as(
                select * from public.kln_ocean oo ". $sqlWhere ."
            ),
            matched_data as (
                select
                    ooc.serial_no as h_serial_no,
                    ooc.h_bol,
                    ooc.m_bol,
                    oc.ctnr,
                    ooc.service,
                    ooc.po_no,
                    (select reference_no from public.ocean o where o.serial_no = ooc.serial_no  limit 1) as reference_no,
                    ooc.transport_mode,
                    oc.qty::text as pakages, 
                    oc.unit as package_type ,
                    oc.grs_kgs::text as kgw,
                    oc.cbm as volume,
                    oc.size as oc_size,
                    ooc.vessel,
                    ooc.voyage,
                    ooc.carrier,
                    ooc.fport_of_loading_un,
                    ooc.mport_of_discharge_un,
                    ooc.eta,
                    ooc.ata,
                    ooc.shipper,
                    ooc.consignee_id,
                    ooc.consignee,
                    '' as controlling_customer,
                    ooc.booking_no,
                    '' as manifest_type,
                    ooc.agent,
                    ooc.order_from,
                    ooc.dest_op
                from ooc  
                    LEFT JOIN public.oc_container oc ON oc.serial_no::text = ooc.serial_no::text  
                where  ooc.order_from = 'public' and transport_mode = 'sea' ".$sqlCtnr."
                union all
                select 
                    ooc.serial_no as h_serial_no,
                    ooc.h_bol,
                    ooc.m_bol,
                    oc.ctnr,
                    ooc.service,
                    ooc.po_no,
                    (select reference_no from sfs.ocean o where o.serial_no = ooc.serial_no  limit 1) as reference_no,
                    ooc.transport_mode,
                    oc.qty::text as pakages, 
                    oc.unit as package_type ,
                    oc.grs_kgs::text as kgw,
                    oc.cbm as volume,
                    oc.size as oc_size,
                    ooc.vessel,
                    ooc.voyage,
                    ooc.carrier,
                    ooc.fport_of_loading_un,
                    ooc.mport_of_discharge_un,
                    ooc.eta,
                    ooc.ata,
                    ooc.shipper,
                    ooc.consignee_id,
                    ooc.consignee,
                    '' as controlling_customer,
                    ooc.booking_no,
                    '' as manifest_type,
                    ooc.agent,
                    ooc.order_from,
                    ooc.dest_op
                from ooc  
                    LEFT JOIN sfs.oc_container oc ON oc.serial_no::text = ooc.serial_no::text  
                where  ooc.order_from = 'sfs' and transport_mode = 'sea' ".$sqlCtnr."
                union all
                select 
                    ooc.serial_no as h_serial_no,
                    ooc.h_bol,
                    ooc.m_bol,
                    '' as ctnr,
                    ooc.service,
                    ooc.po_no,
                    '' as reference_no,
                    ooc.transport_mode,
                    ooc.qty as pakages, 
                    ooc.qty_uom as package_type ,
                    ooc.piece_count as kgw,
                    ooc.cbm as volume,
                    '' as oc_size,
                    ooc.vessel,
                    ooc.voyage,
                    ooc.carrier,
                    ooc.fport_of_loading_un,
                    ooc.mport_of_discharge_un,
                    ooc.eta,
                    ooc.ata,
                    ooc.shipper,
                    ooc.consignee_id,
                    ooc.consignee,
                    '' as controlling_customer,
                    ooc.booking_no,
                    '' as manifest_type,
                    ooc.agent,
                    ooc.order_from,
                    ooc.dest_op
                from ooc
                where ooc.transport_mode = 'air'
            )
            select md.*,
                CASE 
                    WHEN kd.recommended_delivery_window_date_from IS NOT NULL AND kd.recommended_delivery_window_date_to IS NOT NULL THEN
                        to_char((recommended_delivery_window_date_from)::date, 'YYYY.MM.DD') 
                        || '-' ||
                        to_char((recommended_delivery_window_date_to)::date, 'YYYY.MM.DD')
                    WHEN kd.recommended_delivery_window_date_from IS NULL AND kd.recommended_delivery_window_date_to IS NOT NULL THEN
                        '-' || 
                        to_char((recommended_delivery_window_date_to)::date, 'YYYY.MM.DD')
                    WHEN kd.recommended_delivery_window_date_from IS NOT NULL AND kd.recommended_delivery_window_date_to IS NULL THEN
                        to_char((recommended_delivery_window_date_from)::date, 'YYYY.MM.DD') 
                        || '-'
                    ELSE  '' 
                END AS date_range,
                kd.recommended_delivery_from, 
                kd.recommended_delivery_to,
                kd.recommended_delivery_window_date_from as recommended_delivery_from_date,
                kd.recommended_delivery_window_date_to as recommended_delivery_to_date,
                (select dc.country from public.kln_destination_delivery_config dc where md.agent in (select regexp_split_to_table(dc.station, ','))) as dc_country
            from matched_data md  
                LEFT join public.kln_destination_delivery kd on kd.serial_no  = '$serial_no'
            order by md.eta desc";
        //error_log($sql);    
        $rs = common::excuteListSql($sql);
        return array("msg"=>"","data"=>$rs); 
    }

    function groupShipments($booking_no, $manifest_type, $h_bol,$h_serial_no, $order_from,$m_bol, $ctnr,$kln_pic,$consignee,$consignee_id,
        $recommended_delivery_window_date_from, $recommended_delivery_window_date_to,
        $recommended_delivery_from,$recommended_delivery_to,$dc_country,$pakages,$oc_size){
        // Combine the data into an array of shipments
        $shipments = [];
        foreach ($h_bol as $index => $_h_bol) {
            $manifest_type = common::excuteOneSql("select manifest_type from ".$order_from[$index].".ocean o where o.serial_no = '".$h_serial_no[$index]."'  limit 1");
            //$manifest_type[$index],
            //为了优化总sql 改为在这里单个查询
            $shipments[] = [
                'booking_no' =>$booking_no[$index],
                'manifest_type' => $manifest_type,
                'h_bol' => $h_bol[$index],
                'h_serial_no' => $h_serial_no[$index],
                'order_from' => $order_from[$index],
                'm_bol' => $m_bol[$index],
                'ctnr' => $ctnr[$index],
                'kln_pic' => $kln_pic[$index],
                'consignee' => $consignee[$index],
                'consignee_id' => $consignee_id[$index],
                'dc_country' => $dc_country[$index],
                'pakages' => $pakages[$index],
                'oc_size' => $oc_size[$index],
                'recommended_delivery_window_date_from' => $recommended_delivery_window_date_from[$index],
                'recommended_delivery_window_date_to' => $recommended_delivery_window_date_to[$index],
                'recommended_delivery_from' => $recommended_delivery_from[$index],
                'recommended_delivery_to' => $recommended_delivery_to[$index]
            ];
        }

        // 按 MBOL 分组
        $groupedByMbol = [];
        // 按 ShipmentID 分组
        $groupedByShipmentId = [];

        foreach ($shipments as $item) {
            $m_bol = $item['m_bol'];
            $h_serial_no = $item['h_serial_no'];
            $h_bol = $item['h_bol'];
            $order_from = $item['order_from'];
            $ctnr = $item['ctnr'];
            $dc_country = $item['dc_country'];
            $consignee_id = $item['consignee_id'];
            $pakages = $item['pakages'];
            $oc_size = $item['oc_size'];

            if(strtoupper($item['manifest_type']) == 'BCN'){
                if (!isset($groupedByMbol[$m_bol])) {
                    $item['h_bol'] = array();
                    $item['h_bol_multiple_link'] = array();
                    $item['h_serial_no'] = array();
                    $item['ctnr'] = array();
                    $item['dc_country'] = array();
                    $item['consignee_id'] = array();
                    $item['oc_size'] = array();
                    $item['pakages'] = 0;
                    $item['m_bol'] = array($item['m_bol']);
                    $groupedByMbol[$m_bol] = $item;
                }

                if (!in_array($h_serial_no, $groupedByMbol[$m_bol]['h_serial_no'])) {
                    $groupedByMbol[$m_bol]['h_bol'][] = $h_bol;
                    $groupedByMbol[$m_bol]['h_bol_multiple_link'][] = array("key"=>$h_bol,"value" =>$h_serial_no,"order_from"=>$order_from);
                    $groupedByMbol[$m_bol]['h_serial_no'][] = $h_serial_no;
                    $groupedByMbol[$m_bol]['dc_country'][] = $dc_country;
                    $groupedByMbol[$m_bol]['consignee_id'][] = $consignee_id;
                }
                if (!in_array($ctnr, $groupedByMbol[$m_bol]['ctnr'])) {
                    $groupedByMbol[$m_bol]['ctnr'][] = $ctnr;
                    $groupedByMbol[$m_bol]['oc_size'][] = $oc_size;
                    $groupedByMbol[$m_bol]['pakages'] += intval($pakages);
                }
            } else {
                if (!isset($groupedByShipmentId[$h_serial_no])) {
                    $item['m_bol'] = array();
                    $item['ctnr'] = array();
                    $item['dc_country'] = array($item['dc_country']);
                    $item['consignee_id'] = array($item['consignee_id']);
                    $item['h_serial_no'] = array($item['h_serial_no']);
                    $item['h_bol'] = array($item['h_bol']);
                    $item['pakages'] = 0;
                    $item['oc_size'] = array();
                    $item['h_bol_multiple_link'] = array(array("key"=>$h_bol,"value" =>$h_serial_no,"order_from"=>$order_from));
                    $groupedByShipmentId[$h_serial_no] = $item;
                }

                if (!in_array($m_bol, $groupedByShipmentId[$h_serial_no]['m_bol'])) {
                    $groupedByShipmentId[$h_serial_no]['m_bol'][] = $m_bol;
                }

                if (!in_array($ctnr, $groupedByShipmentId[$h_serial_no]['ctnr'])) {
                    $groupedByShipmentId[$h_serial_no]['ctnr'][] = $ctnr;
                    $groupedByShipmentId[$h_serial_no]['oc_size'][] = $oc_size;
                    $groupedByShipmentId[$h_serial_no]['pakages'] += intval($pakages);
                }
            }
        }

        //检查是否选中所有的BCN
        foreach($groupedByMbol as $mkey => $mval){
            $mcount = common::excuteOneSql("select count(*) from kln_ocean where m_bol = '".common::check_input($mkey)."'");
            $hcount = count($mval['h_serial_no']);
            if($mcount <> $hcount){
                return array("msg"=>"MBL No.: ".$mkey.",The shipment of BCN has not been fully selected");
            }
        }

        $result = array();
        foreach($groupedByMbol as $mb){
            $result[] = $mb;
        }
        foreach($groupedByShipmentId as $hb){
            $result[] = $hb;
        }
        return array("msg"=>"","result"=>$result);
    }

    /**
     * 批量选着文件下载 CI & P
     */
    private function _batch_download_ci_p_file(){
        //ocean的相关配置参数    
        $OutFileURL = common::excuteOneSql("select item_value from config where item='OutFileURL'");
        $ocean_can_view_file = common::excuteOneSql("select ocean_can_view_file from ra_online_user where user_login='".$_SESSION['ONLINE_USER']['user_login']."'");
        //$ocean_can_view_file = '{"HBL":[{"type":"TELEX","value":""}],"MBL":[{"type":"TELEX","value":""}]}';
        $tar = json_decode($ocean_can_view_file,true);
        $sqlFiterWhere = "1<>1 ";
        foreach($tar as $_tar){
            foreach($_tar as $dkey => $dvalue){
                $sqlFiterWhere.= " or (lower(format_serailno) in ('".strtolower(str_replace(";","','",$dvalue['value']))."')  
                    and upper((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2]) = '".$dvalue['type']."')";
            }
        }

        //air的相关配置参数  
        $air_can_view_file = common::excuteOneSql("select air_can_view_file from ra_online_user where lower(user_login)='".strtolower($_SESSION['ONLINE_USER']['user_login'])."'");
        $air_tar = json_decode($air_can_view_file,true);
        $air_sqlFiterWhere = "1<>1 ";
        foreach($air_tar as $_air_tar){
            foreach($_air_tar as $air_dkey => $air_dvalue){
                $air_sqlFiterWhere.= " or (lower(format_serailno) in ('".strtolower(str_replace(";","','",$air_dvalue['value']))."')  
                    and upper((string_to_array((string_to_array(file_name,'.'))[1], '_'))[2]) = '".$air_dvalue['type']."')";
            }
        }

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

            $_schemas = $ocean["order_from"];
            if ($ocean["transport_mode"] == 'sea'){
                //ocean 能看的文件类型
                $document = _getViewDocType($_schemas);
                $file_serial_nos = array();
                foreach ($document as $v) {
                    $types = $v['serial_no'];
                    $types = strtolower($types);
                    $types = explode(";", $types);
                    foreach ($types as $k => $type) {
                        $file_serial_nos[] = $type;
                    }
                }
                $file_serial_nos = utils::implode(';',$file_serial_nos);
                $formatSerailnoWhere = "";
                if($file_type == 'Packing List'){
                    $formatSerailnoWhere = " and lower(format_serailno) = '".strtolower('abfee0ffac630983976b336b37624c73')."'";
                } else if ($file_type == 'C/I'){
                    $formatSerailnoWhere = " and lower(format_serailno) = '".strtolower('4f0a5400c6829')."'";
                }

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

                $rss = common::excuteListSql($fileSql);
                foreach ($rss as $rs) {
                    if ($rs['from_system']=="TOPOCEAN"||$rs['from_system']=="KSMART") {
                        //这种类型现在没有了
                    }else {
                        $file_arr[] = $rs['file_path'] . DS . $rs['file_name'];
                    }
                } 
               
            }
            if ($ocean["transport_mode"] == 'air'){
                //air能看的文件类型
                $document = _getAirViewDocType($_schemas);
                $file_serial_nos = array();
                foreach ($document as $v) {
                    $types = $v['serial_no'];
                    $types = strtolower($types);
                    $types = explode(";", $types);
                    foreach ($types as $k => $type) {
                        $file_serial_nos[] = $type;
                    }
                }
                $file_serial_nos = utils::implode(';',$file_serial_nos);

                $formatSerailnoWhere = "";
                if($file_type == 'Packing List'){
                    $formatSerailnoWhere = " and lower(format_serailno) = '".strtolower('abfee0ffac630983976b336b37624c73')."'";
                } else if ($file_type == 'C/I'){
                    $formatSerailnoWhere = " and lower(format_serailno) = '".strtolower('4f0a5400c6829')."'";
                }

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

                $rss = common::excuteListSql($fileSql);
                foreach ($rss as $rs) {
                    $file_arr[] = $rs['file_path'] . DS . $rs['file_name'];
                } 
            }
        }

        if($file_type == 'Packing List'){
            $zipName = "Packing_List";
        } else if ($file_type == 'C/I'){
            $zipName = "C/I";     
        }

        if(empty($file_arr)){
            $data = array('msg' => 'No file to download','data' => '');
            common::echo_json_encode(500, $data);
            exit();
        }
        common::downloadFilesAsSimapleZip($file_arr,$zipName);
    }
}

?>