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_%'";
            $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(e.contact_id) in ($more_param) ";
            }

            $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) . "%' limit 20";

            // $sql  = "select email from public.ra_online_user where user_type = 'Employee' and lower(contact_id) in ($more_param)
            //     and email ilike '" . common::check_input($term) . "%' limit 20";
            //error_log($sql);
            $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 20";

            $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 20";

            $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');
            //$delivery_serial_no = 'c4d2951f438a4c9fccde37f26614740a';
            if(!empty($delivery_serial_no)){
                $sql = "SELECT addr1,addr2,addr3,addr4,
                            ctry_code,city_code,postal_code,
                            contact_person,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 delivery_serial_no = '".$delivery_serial_no."')
                        union all
                        SELECT addr1,addr2,addr3,addr4,
                                ctry_code,city_code,postal_code,
                                contact_person,contact_number,
                                create_user,contact_id,sync_key,
                                from_station,
                                action as op_action
                        FROM public.contacts_address_online 
                            where delivery_serial_no = '$delivery_serial_no'";
            }else{
                $sql = "SELECT addr1,addr2,addr3,addr4,
                            ctry_code,city_code,postal_code,
                            contact_person,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 20";
            }
            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 20";
            }    
            $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();

        }
    }

    /**
     * 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 crm.overeas from public.contract_region_mapping crm
                            left join ocean.contacts c on  c.contact_id = crm.overeas and crm.ksmart_station_new ilike 'ksmart%'
                    where SUBSTRING(c.country FROM 1 FOR 2) = '".$data['country']."'";
            $rs = common::excuteListSql($sql);
            foreach($rs as $key => $val){
                if(!empty($val['overeas'])){
                    $CountryCheckedList[] = array("value" =>$val['overeas'] ,"label"=>$val['overeas']);
                }
            }
            $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 = common::check_input($_POST['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 = [];

                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]);
                        ///$sea_carrier_index += 1;
                    } 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");
                $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();
        }

        if ($operate == "destination_delivery_shipment_search") {
            //search
            //栏位信息
            $column = column::getInstance()->getDisplayColumn('destination_delivery_shipment_search');
            $BookingTableColumns = column::getInstance()->tableColumns('destination_delivery_shipment_search',$column);
            $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()){
                //$sqlWhere .= " and kln_pic ilike '"._getLoginEamil()."'";
                $sqlDeliveryWhere .= " and '".strtolower(_getLoginEmployeeEamil())."' in (select regexp_split_to_table(lower(kd.kln_pic), ','))";
            }    

            //条件带入
            $text_search = $_REQUEST['text_search'];
            if (!empty($text_search)){
                $sqlWhere .= " and (booking_no ilike '%".strtolower($text_search)."%'
                    or h_bol ilike '%".strtolower($text_search)."%'
                    or m_bol ilike '%".strtolower($text_search)."%' 
                    or ctnr ilike '%".strtolower($text_search)."%'
                    or consignee ilike '%".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 oo ". $sqlWhere."  and oo.serial_no = any(kd.h_serial_no))";
                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 oo ". $sqlWhere."  and oo.serial_no = any(h_serial_no))";
                $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']);
                    $rs[$key]["h_bol"] = json_decode($val['h_bol_multiple_link']);
                }
                $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_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['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'];

            $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']);

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

            // $ckeckedSql = "with save_table as (select regexp_split_to_table('".utils::implode(',',$h_serial_no)."', ',') as serial_no)
            // select string_agg(h_bol, ';') as h_bol from public.kln_destination_delivery kde
            //         where exists(select 1 from save_table where save_table.serial_no = any(kde.h_serial_no))
            //             and exists(select 1 from public.kln_ocean oo ". $sqlWhere."  and oo.serial_no = any(kde.h_serial_no))";
            // //代表编辑
            // if(!empty($serial_no)){
            //     $ckeckedSql .=" and kde.serial_no <> '$serial_no'";
            // }            

            // $checkeData = common::excuteObjectSql($ckeckedSql);
            // if(!empty($checkeData['h_bol'])){
            //     $data = array("msg" =>"The ".$checkeData['h_bol'] ." already exists");
            //     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($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 {  
                $saveData = $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);
                
                foreach($saveData as $sData){
                    $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, 
                            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)
                    VALUES ('$serial_no', ".$tags_sql.",".$country_sql.",
                            '".$sData['booking_no']."', '".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',
                            $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());";
                    //记录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(200,$data); 
                    exit();
                }
            }

            if(!$is_new){
                //状态变更时，发送邮件提醒 
                $status = "Modify";
                $data = common::excuteObjectSql("select *, TO_CHAR(delivery_date, 'Mon-DD-YYYY') 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') 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);
            $sql = "";
            //审核通过时，才取下放当时应用修改的地址
            if ($status == "Approve") {
                $address_country = common::excuteOneSql("select array_to_json(address_country) as address_country from public.kln_destination_delivery where serial_no = '$serial_no'");
                $onine_address = common::excuteListSql("select * from public.contacts_address_online where delivery_serial_no = '$serial_no'");
                $sql .= "delete from public.contacts_address_online where delivery_serial_no = '$serial_no';";
                $sql .=common::returnDAddressRecord($address_country,$onine_address);
            }
            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 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(), ''); ";

            $rs = common::excuteUpdateSql($sql);  
            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') 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;
            $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"; 
            if(_isApexLogin()){
                //$sqlWhere .= " and kln_pic ilike '"._getLoginEamil()."'";
                $sqlDeliveryWhere .= " and '".strtolower(_getLoginEmployeeEamil())."' in (select regexp_split_to_table(lower(kd.kln_pic), ','))";
            }      
            //$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 oo ". $sqlWhere."  and oo.serial_no = any(h_serial_no))
                    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); 
        }

    }

    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 '6 months' and eta <= now() + INTERVAL '6 months'";
        // $checkOpenSql = "with oo as ( select DISTINCT agent from public.kln_ocean oo ". $sqlWhere.")
        //         select agent from  oo 
        //             where exists(select 1 from public.kln_destination_delivery_config where oo.agent in (select regexp_split_to_table(station, E','))) 
        //         limit 1";
        $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 '6 months' and eta <= now() + INTERVAL '6 months'";

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

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

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

        $shipper = $_REQUEST['shipper'];
        if (!empty($vessel)){
            $sqlWhere .= " and shipper ilike '%".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'";

        $sql = "with oo as(
                select * from public.kln_ocean oo ". $sqlWhere ."
            ),
            ooc as (
                select oo.*,
                    dc.serial_no as dc_serial_no,
                    dc.country as dc_country,
                    dc.kln_pic as dc_kln_pic
                from public.kln_destination_delivery_config dc
                    Inner Join oo
                        on oo.agent in (select regexp_split_to_table(dc.station, ','))
                    where  1=1 and 
                        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,
                    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,
                    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,
                    (select customer_name from public.ocean_extend ex where ex.serial_no = ooc.serial_no  limit 1) as controlling_customer,
                    ooc.booking_no,
                    (select manifest_type from public.ocean o where o.serial_no = ooc.serial_no  limit 1) as manifest_type,
                    dc_serial_no,
                    dc_country,
                    dc_kln_pic,
                    ooc.agent,
                    ooc.order_from
                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,
                    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,
                    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,
                    (select customer_name from ocean_extend ex where ex.serial_no = ooc.serial_no  limit 1) as controlling_customer,
                    ooc.booking_no,
                    (select manifest_type from sfs.ocean o where o.serial_no = ooc.serial_no  limit 1) as manifest_type,
                    dc_serial_no,
                    dc_country,
                    dc_kln_pic,
                    ooc.agent,
                    ooc.order_from
                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,
                    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,
                    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,
                    dc_serial_no,
                    dc_country,
                    dc_kln_pic,
                    ooc.agent,
                    ooc.order_from
                from ooc
                where ooc.transport_mode = 'air'
            )
            select md.*,
                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.*
                FROM public.kln_destination_delivery_rule r 
                WHERE md.transport_mode = r.mode_type 
                    and r.recommended_delivery_serial_no = md.dc_serial_no
                    AND ((md.transport_mode = 'sea' AND (
                            (r.ports ilike '%'|| md.mport_of_discharge_un ||'%' AND r.carrier ilike '%'|| md.carrier ||'%') 
                            OR (r.ports ilike '%'|| md.mport_of_discharge_un ||'%' AND r.carrier = 'ALL')
                            OR (r.ports = 'ALL' AND r.carrier ilike '%'|| md.carrier ||'%')
                            OR (r.ports = 'ALL' AND r.carrier = 'ALL'))
                        ) 
                        OR (md.transport_mode = 'air' AND (
                            r.ports ilike '%'|| md.mport_of_discharge_un ||'%'
                            OR r.ports = 'ALL')
                        )
                    )
                    ORDER BY priority asc
                    LIMIT 1
                ) r ON true
            order by md.eta desc";
         
        $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,
                    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,
                    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,
                    (select customer_name from public.ocean_extend ex where ex.serial_no = ooc.serial_no  limit 1) as controlling_customer,
                    ooc.booking_no,
                    (select manifest_type from public.ocean o where o.serial_no = ooc.serial_no  limit 1) as manifest_type,
                    ooc.agent,
                    ooc.order_from
                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,
                    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,
                    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,
                    (select customer_name from ocean_extend ex where ex.serial_no = ooc.serial_no  limit 1) as controlling_customer,
                    ooc.booking_no,
                    (select manifest_type from sfs.ocean o where o.serial_no = ooc.serial_no  limit 1) as manifest_type,
                    ooc.agent,
                    ooc.order_from
                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,
                    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,
                    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
                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){
        // Combine the data into an array of shipments
        $shipments = [];
        foreach ($h_bol as $index => $_h_bol) {
            $shipments[] = [
                'booking_no' =>$booking_no[$index],
                'manifest_type' => $manifest_type[$index],
                '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],
                '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'];

            if($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['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;
                }
            } 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['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;
                }
            }
        }

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

?>