k_online_backend/service/tools.class.php

<?php

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

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

    private static $_tools;

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

    /*
     * update password when login success
    */
    public function updatePassword() {
        if ($_SESSION['ONLINE_USER']['is_demo'] == "t") {
            $str = "DEMO cannot update password.";
        } else {
            $opsw = common::check_input($_POST ['opsw']);
            $npsw = common::check_input($_POST ['npsw']);
            $username = _getLoginName();

            $msg = common::checkPasswordRule($username, $npsw);
            //为空代表验证通过
            if (empty($msg)) {
                $sql = "select ra_password as password from ra_online_user where lower(user_login) = '" . strtolower($username) . "'";
                $rs = common::excuteObjectSql($sql);
                $str = '';
                if (!empty($rs)) {
                    if ($rs['password'] == $opsw) {
                        $sql = "UPDATE ra_online_user SET ra_password = '" . $npsw . "', last_pwd_change = now() WHERE lower(user_login) = '" . strtolower($username) . "'";
                        $rls = common::excuteUpdateSql($sql);
                        if (!$rls) {
                            $str = "Password modification failed!";
                        } else {
                            $str = " Your password has been modified!";
                        }
                    } else {
                        $str = "Old password is incorrect!";
                    }
                } else {
                    $str = "Old password is incorrect!";
                }
            } else {
                $str = $msg;
            }
        }
        $returnData = array("msg" => $str);
        common::echo_json_encode(200, $returnData);
        exit();
    }

    public function markSystem(){
        $operate = utils::_get('operate');
        $operate = strtolower($operate);

        if ($operate == "mark_save") {
            $suggestion = utils::implode(",",$_POST['suggestion']); 
            $proposal = common::check_input($_POST['proposal']); 
            $expression = common::check_input($_POST['expression']);
            $complete_funtionality = common::check_input($_POST['Complete_funtionality']);
            $accurate_data = common::check_input($_POST['Accurate_data']);
            $clear_information = common::check_input($_POST['Clear_information']);
            $easy_to_use = common::check_input($_POST['Easy_to_use']);
            $system_Performance = common::check_input($_POST['System_Performance']);
            $username = common::check_input($_POST['username']);

            $user_type = _isApexLogin() ? "employee" : "customer";
            if(!isset($_SESSION['ONLINE_USER'])){
                $user_type = "other";
            }
            $loginName = _getLoginName();
            $loginEamil = _getLoginEamil();
            //如果在没有登录前，没有登录信息，指定用户-- 这里逻辑取消，没有登录相当于匿名用户的评价，无法获取用户名
            // if(!isset($_SESSION['ONLINE_USER'])){
            //     $user_type = "Customer";
            //     if(!empty($username)){
            //         $loginName = $username;
            //         $loginEamil = common::excuteOneSql("select email from public.ra_online_user u where lower(user_login) = '" . strtolower($username) . "'");
            //     }
            // }
            $sql = "INSERT INTO public.customer_service_user_mark(user_type, user_name, suggestion, proposal, expression, complete_funtionality, 
                    accurate_data, clear_information, easy_to_use, system_performance, 
                    created_time,email)
            VALUES ('$user_type', '$loginName', '$suggestion', '$proposal', '$expression', '$complete_funtionality', 
                    '$accurate_data', '$clear_information', '$easy_to_use', '$system_Performance', now(),'$loginEamil')";
            common::excuteUpdateSql($sql);

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

    public function user_system_setting(){
        $operate = utils::_get('operate');
        $operate = strtolower($operate);

        if ($operate == "personal_profile_init") {
            // get system config
            $sql = "SELECT lower(ra_name) as ra_name, ra_value from ra_online_config where lower(ra_name) in ('employee_password_change_cycle', 'customer_password_change_cycle')";
            $rs1s = common::excuteListSql($sql);
            foreach ($rs1s as $rs1) {
                if ($rs1['ra_name'] == 'employee_password_change_cycle')
                    $EMPLOYEE_PASSWORD_CHANGE_CYCLE = $rs1['ra_value'];
                if ($rs1['ra_name'] == 'customer_password_change_cycle')
                    $CUSTOMER_PASSWORD_CHANGE_CYCLE = $rs1['ra_value'];
            }
            $sql="select item_value from config where item='passwordChangePeriod'";
            $pcp = common::excuteObjectSql($sql);
            $passwordChangePeriod = json_decode($pcp["item_value"],true);

            if (_isApexLogin()) {
                $PASSWORD_CHANGE_CYCLE = $EMPLOYEE_PASSWORD_CHANGE_CYCLE;
                //如果有新配置，则采用新配置
                if (!empty($pcp)) {
                    $PASSWORD_CHANGE_CYCLE = $passwordChangePeriod["Employee"]["days"];
                }
            } else {
                $PASSWORD_CHANGE_CYCLE = $CUSTOMER_PASSWORD_CHANGE_CYCLE;
                //如果有新配置，则采用新配置
                if (!empty($pcp)) {
                    $PASSWORD_CHANGE_CYCLE = $passwordChangePeriod["Customer"]["days"];
                }
            }
            
            $sql = "select u.first_name,u.last_name,u.user_login,u.email,EXTRACT(DAY from (now() - u.last_pwd_change)) as last_pwd_change_date,
                    ue.date_format,ue.numbers_format
                from public.ra_online_user u
                    left join  public.kln_user_extend ue on u.user_login = ue.user_login
                where lower(u.user_login) = '".strtolower(_getLoginName())."' ";

            $data = common::excuteObjectSql($sql);
            $data["expire_day"] = $PASSWORD_CHANGE_CYCLE - $data['last_pwd_change_date'];
            common::echo_json_encode(200,$data);
            exit();
        }

        if ($operate == "personal_profile_save") {
            $save_model = common::check_input($_POST['save_model']);
            if ($save_model == "profile"){
                $first_name = common::check_input($_POST['first_name']);
                $last_name = common::check_input($_POST['last_name']);
                $sql = "update public.ra_online_user set first_name = '$first_name',last_name = '$last_name'  where lower(user_login) = '".strtolower(_getLoginName())."'";
            }else{
                $date_format = common::check_input($_REQUEST['date_format']);
                $numbers_format = common::check_input($_REQUEST['numbers_format']);
                $exist_kln_user = common::excuteObjectSql("select user_login from  public.kln_user_extend where  lower(user_login) = '".strtolower(_getLoginName())."'");
                if (!empty($exist_kln_user['user_login'])){
                    $sql = "update public.kln_user_extend set date_format = '$date_format',numbers_format = '$numbers_format'  where lower(user_login) = '".strtolower(_getLoginName())."'";
                } else {
                    $sql = "INSERT INTO public.kln_user_extend(user_login, date_format, numbers_format, subscribe_hbol)
                        VALUES ('"._getLoginName()."', '$date_format', '$numbers_format', null);";
                }
            }
            common::excuteUpdateSql($sql);
            $data = array("msg" => "save Successful");
            common::echo_json_encode(200,$data);
            exit();
        }

        if ($operate == "subscribe_notification_init") {
            $subscribur_data =array();
            //查询用户对应的Rule
            $subscribe_rule_sql = "select *,TO_CHAR(daily_time, 'HH24:MI') as _daily_time,
                    TO_CHAR(weekly_time, 'HH24:MI') as _weekly_time
                from public.notifications_rules where notifications_type = 'Subscribe' and lower(user_login) = '".strtolower(_getLoginName())."' order by id desc";
            $subscribe_rules = common::excuteListSql($subscribe_rule_sql);
            $all_rules = array("Milestone_Update","Container_Status_Update","Departure/Arrival_Delay","ETD/ETA_Change");
            foreach($all_rules as $rule_name){
                $rules = $this->getSubscribeRules($rule_name,$subscribe_rules);
                $subscribur_data[$rule_name] = $rules;
            }
            //整合拼接addedRules
            $addedRules = array();
            foreach($subscribe_rules as $addedRule){
                $addedRules[] = array(
                    "visible" => false,
                    "id" =>$addedRule['id'],
                    "Event" =>$addedRule['rules_type'],
                    "Event Details" =>$addedRule['event_details'],
                    "Frequency" =>$addedRule['frequency_display'],
                    "Methods" =>$addedRule['method_display']);
            }
            $subscribur_data['addedRules'] = array("tableData"=>$addedRules);

            //获取subscribe shipment 当前页数cp，每页ps
            $subscribeShipmentWithPage =  $this->getSubscribeShipment(1,15);
            $subscribur_data['subscribeShipmentWithPage'] = $subscribeShipmentWithPage;

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

        if ($operate == "subscribe_notification_event_update"){
            $rules_type = common::check_input($_POST["rules_type"]);
            //判断该规则是否存在
            $exist = common::excuteObjectSql("select user_login,id from public.notifications_rules where notifications_type = 'Subscribe' and rules_type = '".$rules_type."'
                    and  lower(user_login) = '".strtolower(_getLoginName())."'");

            //检查参数是否正常
            $err = $this->checkedNotificationParam(); 
            if(!empty($err)){
                $data = array("msg" =>"Parameter error: ".$err);
                common::echo_json_encode(500,$data);
                exit();
            }      
            $updateOrInsert = empty($exist) ? "insert" : "update";        
            $sql = $this->getNotificationsRulesUpdateSql($updateOrInsert,$rules_type,"Subscribe",$exist['id']);
            $rs = common::excuteUpdateSql($sql);
            if ($rs === FALSE){
                $data = array("msg" => "Update Error");
            } else{
                $data = array("msg" => "Update Successful");
                //返回addedRules 全部列表
                $subscribe_rule_sql = "select * from public.notifications_rules where notifications_type = 'Subscribe' and lower(user_login) = '".strtolower(_getLoginName())."' order by id";
                $subscribe_rules = common::excuteListSql($subscribe_rule_sql);
                //整合拼接addedRules
                $addedRules = array();
                foreach($subscribe_rules as $addedRule){
                    $addedRules[] = array(
                        "id" =>$addedRule['id'],
                        "Event" =>$addedRule['rules_type'],
                        "Event Details" =>$addedRule['event_details'],
                        "Frequency" =>$addedRule['frequency_display'],
                        "Methods" =>$addedRule['method_display']);
                }
                $data['addedRules'] = array("tableData"=>$addedRules);
            }
            common::echo_json_encode(200,$data);
            exit();

        }

        if ($operate == "subscribe_notification_rules_delete"){
            $rules_type = common::check_input($_POST['rules_type']);
            $sql = "delete from notifications_rules where notifications_type = 'Subscribe' 
                and rules_type = '$rules_type' and lower(user_login) = '".strtolower(_getLoginName())."'";
            common::excuteUpdateSql($sql);
            $data = array("msg" => "Delete Successful");
            common::echo_json_encode(200,$data);
            exit();
        }

        if ($operate == "subscribe_shipment"){
            $serial_no = common::deCode($_POST['serial_no'], 'D');
            $is_subscribe = common::check_input($_POST['is_subscribe']);
            if($is_subscribe == "true"){
                $exist = common::excuteOneSql("select user_login from public.kln_user_subscribed where lower(user_login) = '".strtolower(_getLoginName())."' and subscribed_serial_no = '$serial_no'");
                if(!empty($exist)){
                    $data = array("msg" => "Subscribe exist,Please check");
                    common::echo_json_encode(200,$data);
                    exit();
                }
    
                $sql = "INSERT INTO public.kln_user_subscribed(user_login, subscribed_serial_no, create_user, create_time)
                    VALUES ('"._getLoginName()."', '$serial_no', '"._getLoginName()."', now());";
                common::excuteUpdateSql($sql);
                $data = array("msg" => "Subscribe Successful");
                common::echo_json_encode(200,$data);
                exit();
            }else{
                //取消订阅
                $sql = "delete from public.kln_user_subscribed where lower(user_login) = '".strtolower(_getLoginName())."' and subscribed_serial_no = '$serial_no';";
                common::excuteUpdateSql($sql);
                $data = array("msg" => "Cancel Subscribe successfully");
                common::echo_json_encode(200,$data);
                exit();
            }
        }

        if ($operate == "subscribe_shipment_search"){
            $cp = common::check_input($_POST ['cp']); //current_page
            $ps = common::check_input($_POST ['ps']); //ps
            $arrTmp = $this->getSubscribeShipment($cp,$ps);
            common::echo_json_encode(200,$arrTmp);
            exit();
        }
    }

    public function user_monitoring_setting(){
        $operate = utils::_get('operate');
        $operate = strtolower($operate);

        if ($operate == "monitoring_rules_init"){
            $ret = array();
            //Milestone Update的页面配置数据
            $milestones = common::excuteListSql("select * from public.customer_service_milestone_sno order by type, sno");
            $oceanMilestone = array();
            $airMilestone = array();
            foreach($milestones as $milestone){
                if($milestone['type'] == "air"){
                    $airMilestone[] = array("label"=>$milestone['description'],"value"=>$milestone['code']);
                } 
                if($milestone['type'] == "sea"){
                    $oceanMilestone[] = array("label"=>$milestone['description'],"value"=>$milestone['code']);
                }
            }
            $ret["OceanCheckBoxList"] = $oceanMilestone;
            $ret["AirCheckBoxList"] = $airMilestone;

            //Milestone Update的结构处理
            //这里基准event 写死, 根据online查询页面的通用的来, 这里需提问确定
            //$event =common::getEDICtnrEvent();
            $event = common::excuteListSql("select ra_name as event_name,ra_order,description 
                from public.ra_online_edi_event e 
                    where e.ra_name in('I','VD','VA','UV','AL','AR','OA','RD') order by e.ra_order desc");
            $ctnrStatus = array();
            foreach($event as $e){
                $ctnrStatus[] = array("label"=>$e['description'],"value"=>$e['event_name']);
            }
            $ret["CtnrCheckBoxList"] = $ctnrStatus; 
            common::echo_json_encode(200,$ret);
            exit();
        }

        if ($operate == "monitoring_rules_search") {
            $cp = common::check_input($_POST ['cp']); //current_page
            $ps = common::check_input($_POST ['ps']); //ps
            if (empty($ps))
                $ps = 15;

            $sql = "select count(1) from public.notifications_rules where lower(user_login) = '".strtolower(_getLoginName())."' and notifications_type = 'Monitoring'";
            $rc = common::excuteOneSql($sql);

            $tp = ceil($rc / $ps);
            if ($rc > 0) {
                $sql = "select *,replace(rules_type, '_', ' ') AS _rules_type_display,
                        case when rules_type = 'Milestone_Update' then 'Milestone'
                            when rules_type = 'Container_Status_Update' then 'Container'
                            when rules_type = 'Departure/Arrival_Delay' then 'Departure'
                            when rules_type = 'ETD/ETA_Change' then 'ETDChange'
                        else '' end as notifications_option 
                     from public.notifications_rules 
                        where lower(user_login) = '".strtolower(_getLoginName())."' 
                            and notifications_type = 'Monitoring' order by id desc limit " . $ps . " offset " . ($cp - 1) * $ps;
                $monitoringRules = common::excuteListSql($sql);
                $arrTmp = array('monitoringRules' => $monitoringRules, 
                    'rc' => intval($rc),
                    'ps' => intval($ps),
                    'cp' => intval($cp),
                    'tp' => intval($tp)
                );
            } else {
                $arrTmp = array('monitoringRules' => array(), 
                    'rc' => intval($rc),
                    'ps' => intval($ps),
                    'cp' => intval($cp),
                    'tp' => intval($tp)
                );
            }
            common::echo_json_encode(200,$arrTmp);
            exit();
        }

        if ($operate == "monitoring_rules_edit"){
            $id = $_POST['id'];
            $rules_type = common::check_input($_POST['rules_type']);
            $subscribe_rule_sql = "select *,
                    TO_CHAR(daily_time, 'HH24:MI') as _daily_time,
                    TO_CHAR(weekly_time, 'HH24:MI') as _weekly_time,
                    case when rules_type = 'Milestone_Update' then 'Milestone'
                         when rules_type = 'Container_Status_Update' then 'Container'
                         when rules_type = 'Departure/Arrival_Delay' then 'Departure'
                         when rules_type = 'ETD/ETA_Change' then 'ETDChange'
                    else '' end as notifications_option
                from public.notifications_rules where notifications_type = 'Monitoring' and lower(user_login) = '".strtolower(_getLoginName())."'
                    and id = '$id' order by id";
            $subscribe_rules = common::excuteListSql($subscribe_rule_sql);

            $rules = $this->getSubscribeRules($rules_type,$subscribe_rules);
            
            //数据转换前端需要的显示的格式
            $rules["shipment_transport_mode"] = utils::converModeToDisplay($rules["shipment_transport_mode"]);

            $monitoring_data[$rules_type] = $rules;
            common::echo_json_encode(200,$monitoring_data);
            exit();
        }

        if ($operate == "monitoring_rules_do") {
            $rules_type = common::check_input($_POST["rules_type"]);
            //检查参数是否正常
            $err = $this->checkedNotificationParam(); 
            if(!empty($err)){
                 $data = array("msg" =>"Parameter error: ".$err);
                 common::echo_json_encode(500,$data);
                 exit();
            } 

            //检查编辑提交的Monitoring规则，是否允许保存
            $msg = $this->checkedMonitoringRulesSave($rules_type);
            if(!empty($msg)){
                $data = array("msg" =>$msg);
                common::echo_json_encode(200,$data);
                exit();
            }
            $updateOrInsert = "insert";
            if(isset($_POST['id']) && !empty($_POST['id'])){
                $updateOrInsert = "update";
            }       
            $sql = $this->getNotificationsRulesUpdateSql($updateOrInsert,$rules_type,"Monitoring",$_POST['id']);
            $rs = common::excuteUpdateSql($sql);
            if ($rs === FALSE){
                $data = array("msg" => "Update Error");
            } else{
                $data = array("msg" => "Update Successful");
            }
            common::echo_json_encode(200,$data);
            exit();
        }
        if ($operate == "monitoring_rules_delete"){
            $id = common::check_input($_POST['id']);
            $sql = "delete from notifications_rules where notifications_type = 'Monitoring' 
                 and lower(user_login) = '".strtolower(_getLoginName())."' and  id = '$id'";
            common::excuteUpdateSql($sql);
            $data = array("msg" => "Delete Successful");
            common::echo_json_encode(200,$data);
            exit();
        }
    }

    public function notifications_rules(){
        $operate = utils::_get('operate');
        $operate = strtolower($operate);

        if ($operate == "notifications_init"){
            $rules_type = common::check_input($_REQUEST['rules_type']);
            $milestoneData = array();
            $containerData = array();
            $delayData = array();
            $changeData = array(); 
            if ($rules_type == "all"){
                $rules_type = "Milestone_Update;Container_Status_Update;Departure/Arrival_Delay;ETD/ETA_Change;Feature_Update;Passwond_Notifcations";
                $allData = $this->getNotifications($rules_type,"all");

                $milestoneData = $allData['Milestone_Update'];
                $containerData = $allData['Container_Status_Update'];
                $delayData = $allData['Departure/Arrival_Delay'];
                $changeData = $allData['ETD/ETA_Change'];
                $featureUpdate = $allData['Feature_Update'];
                $passwond_Notifcations = $allData['Passwond_Notifcations'];
            } else {
                $data = $this->getNotifications($rules_type,"all");
                if($rules_type == "Milestone_Update"){
                    $milestoneData = $data['Milestone_Update'];
                }elseif($rules_type == "Container_Status_Update"){
                    $containerData = $data['Container_Status_Update'];
                }elseif($rules_type == "Departure/Arrival_Delay"){
                    $delayData = $data['Departure/Arrival_Delay'];
                }elseif($rules_type == "ETD/ETA_Change"){
                    $changeData = $data['ETD/ETA_Change'];
                }elseif($rules_type == "Feature_Update"){
                    $featureUpdate = $data['Feature_Update'];
                }elseif($rules_type == "Passwond_Notifcations"){
                    $passwond_Notifcations = $data['Passwond_Notifcations'];
                }
            }

            $data = array("milestoneData"=>$milestoneData,"containerData"=>$containerData,"delayData"=>$delayData,
                "changeData"=>$changeData,"featureUpdate"=>$featureUpdate,"passwond_Notifcations"=>$passwond_Notifcations);
            $instant_sum = array();
            foreach($data as $v){
                if(!empty($v['instant'])){
                    foreach($v['instant'] as $instant){
                        $instant_sum[] = $instant;
                    }
                }
                if(!empty($v['daily'])){
                    $dailys = common::handleDailyWeekedData($v['daily']);
                    foreach($dailys as $dailyArr){
                        //取第一组的第一个显示
                        $dailyFristAndFrist = utils::getDailyAndweeklyFrist($dailyArr);
                        $instant_sum[]= $dailyFristAndFrist;
                    }
                }
                if(!empty($v['weekly'])){
                    $weeklys = common::handleDailyWeekedData($v['weekly']);
                    foreach($weeklys as $weeklyArr){
                        $weeklyFristAndFrist = utils::getDailyAndweeklyFrist($weeklyArr);
                        $instant_sum[]= $weeklyFristAndFrist;
                    }
                }
            }
            //根据时间顺序排序
            $insert_dates = array_column($instant_sum, 'insert_date');
            array_multisort($insert_dates, SORT_DESC, $instant_sum);

            $info = array();
            foreach($instant_sum as $mInfo){
                $eventCard = $this->getEventCard($mInfo);
                if(!empty($mInfo['other_type']) && $mInfo['other_type'] == "password"){
                    $info[] = array("notificationType"=>"password","info" =>$eventCard);
                }elseif(!empty($mInfo['other_type']) && $mInfo['other_type'] == "feature"){
                    $info[] = array("notificationType"=>"feature","info" =>$eventCard);
                }else{
                    $info[] = array("notificationType"=>"event","info" =>$eventCard);
                }
            }

            $returnData = $info;
            common::echo_json_encode(200,$returnData);
            exit();

        }

        if($operate == "notifications_see_all"){
            $rules_type = common::check_input($_REQUEST['rules_type']);
            $frequency_type = common::check_input($_REQUEST['frequency_type']);  //这个只会传daily 和weekly
            $insert_date_format = common::check_input($_REQUEST['insert_date_format']);

            $notificationsData = $this->getNotifications($rules_type,$frequency_type,$insert_date_format);
            $moreData = $notificationsData[$rules_type][strtolower($frequency_type)];

            //这个函数里面带有分开计数的信息
            $dataInfo =utils::getDailyAndweeklyFrist($moreData);

            $returnData = array();
            $notificationList = array();
            $ids = array();
            foreach($moreData as $key => $data){
                $eventCard = $this->getEventCard($data);
                //sea all的数据格式和查询全部的格式有区别
                if($key == 0){
                    $returnData["title"] = $eventCard["title"];
                    if($eventCard["type"] == "change" || $eventCard["type"] == "delay"){
                        $returnData["etdOrdeparturNum"] = $dataInfo["numericRecords_one"];
                        $returnData["etaOrarrivalNum"] =$dataInfo["numericRecords_two"];
                        $returnData["type"] =$eventCard["type"];
                    }else{
                        $returnData["numericRecords"] = $dataInfo["numericRecords"];
                    }
                }
                //移除不需要的字段
                unset($eventCard["title"]);
                $notificationList[] = $eventCard;
                $ids[] =  $data['id'];
            }
            if(!empty($notificationList)){
                $returnData["notificationList"] = $notificationList;
            }

            //点击seall会默认全部标记为已读
            if(!empty($ids)){
                $more_param = common::getInNotInSqlForSearch(strtolower(utils::implode(';',$ids)));
                $markReadSql = "update public.kln_notifiation_info set is_send_message = now(),readed_date = now() where id in ($more_param)";
                common::excuteUpdateSql($markReadSql);
            }
            common::echo_json_encode(200,$returnData);
            exit();
        }

        if($operate == "notifications_read"){
            $read_type = common::check_input($_POST["read_type"]);
            $id = $_POST["id"];
            //代表改用户下的所有信息全部标记为已读
            if ($read_type == "true"){
                $rs = common::excuteUpdateSql("update public.kln_notifiation_info set is_send_message = now(),readed_date = now() where lower(user_login) = '".strtolower(_getLoginName())."'");
            }else{
                $more_param = common::getInNotInSqlForSearch(strtolower(utils::implode(';',$id)));
                $markReadSql = "update public.kln_notifiation_info set is_send_message = now(),readed_date = now() where id in ($more_param)";
                $rs = common::excuteUpdateSql($markReadSql);
            }
            if ($rs === FALSE){
                $returnData = array("msg" =>"Error");
                common::echo_json_encode(500,$returnData);
            }else{
                $returnData = array("msg" =>"Success");
                common::echo_json_encode(200,$returnData);
            }
            exit();
        }

        if ($operate == "notifications_message_init"){
            //查询所有情况得未读情况 查询最近一年的情况
            $unreadSql = "with countTbale as (
                select ni.notifiation_type,
                    case when COALESCE(ni.frequency_type,'') = 'Daily'
                            then to_char(timezone(ni.daily_time_zone, ni.insert_date),'Mon DD, YYYY')
                    when COALESCE(ni.frequency_type,'') = 'Weekly' 
                            then to_char(timezone(ni.weekly_time_zone,ni.insert_date)::date - (((EXTRACT(dow FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer - ni.weekly_week::integer + 7) % 7) ||' days')::INTERVAL,'Mon DD, YYYY')
                            || ' - ' ||
                            to_char(timezone(ni.weekly_time_zone,ni.insert_date)::date + (((ni.weekly_week::integer - EXTRACT(dow FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer + 7) % 7)-1 + CASE WHEN EXTRACT(DOW FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer=ni.weekly_week::integer THEN 7 ELSE 0 END ||' days')::INTERVAL,'Mon DD, YYYY')
                    else '' 
                    end  as insert_date_format  
                from  public.kln_notifiation_info ni
                where ni.insert_date > NOW() - INTERVAL '1 year' 
                    and ni.notifications_method = 'true' and ni.is_send_message is null
                    and lower(ni.user_login) = '".strtolower(_getLoginName())."'
                    and ((ni.frequency_type = 'Daily'  
                                and (case  when (timezone(ni.daily_time_zone, NOW())::time > ni.daily_time::time)
		                                then timezone(ni.daily_time_zone,ni.insert_date)::date < timezone(ni.daily_time_zone, NOW())::date
	                                else
		                                timezone(ni.daily_time_zone,ni.insert_date)::date < timezone(ni.daily_time_zone, NOW())::date - '1 days'::INTERVAL
	                                end))
                            or (ni.frequency_type = 'Weekly' 
                                and (case when (timezone(ni.weekly_time_zone, NOW())::time > ni.weekly_time::time)
                                        then timezone(ni.weekly_time_zone,ni.insert_date)::date <= (timezone(ni.weekly_time_zone,ni.insert_date)::date + (((ni.weekly_week::integer - EXTRACT(dow FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer + 7) % 7)-1 + CASE WHEN EXTRACT(DOW FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer=ni.weekly_week::integer THEN 7 ELSE 0 END ||' days')::INTERVAL)::date
                                    else 
                                        timezone(ni.weekly_time_zone,ni.insert_date)::date < (timezone(ni.weekly_time_zone,ni.insert_date)::date - (((EXTRACT(dow FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer - ni.weekly_week::integer + 7) % 7) ||' days')::INTERVAL)::date
                                    end))) 
                      group by ni.notifiation_type,insert_date_format 
            union all
                select ni.notifiation_type, '' as insert_date_format  
                    from  public.kln_notifiation_info ni
                    where ni.insert_date > NOW() - INTERVAL '1 year' 
                        and lower(ni.user_login) in ('".strtolower(_getLoginName())."','all_user')
                        and ni.notifications_method = 'true' and ni.is_send_message is null
                        and frequency_type = 'Instant'  
            )
            select 
                    sum(case when (1<>1 or (notifiation_type='Milestone_Update')) then 1 else 0 end) as m_rc,
                    sum(case when (1<>1 or (notifiation_type='Container_Status_Update')) then 1 else 0 end) as cs_rc,
                    sum(case when (1<>1 or (notifiation_type='Departure/Arrival_Delay')) then 1 else 0 end) as da_rc,
                    sum(case when (1<>1 or (notifiation_type='ETD/ETA_Change')) then 1 else 0 end) as ec_rc,
                    sum(case when (1<>1 or (notifiation_type='Feature_Update')) then 1 else 0 end) as f_rc
                from  countTbale ";

            $count = common::excuteObjectSql($unreadSql);

            //单独的选中的数据
            $rules_type = common::check_input($_REQUEST['rules_type']);
            $data = $this->getNotifications($rules_type,"all");

            $unreadCount = 0;
            $readCount = 0;
            $instant_sum = array();
            if(!empty($data[$rules_type]['instant'])){
                foreach($data[$rules_type]['instant'] as $instant){
                    $instant_sum[] = $instant;
                    if(!empty($instant["is_send_message"])){
                        $readCount +=1;
                    }else{
                        $unreadCount +=1;
                    }
                }
            }
            if(!empty($data[$rules_type]['daily'])){
                $dailys = common::handleDailyWeekedData($data[$rules_type]['daily']);
                foreach($dailys as $dailyArr){
                    //取第一组的第一个显示
                    $dailyFristAndFrist = utils::getDailyAndweeklyFrist($dailyArr);
                    $instant_sum[]= $dailyFristAndFrist;
                    if(!empty($dailyFristAndFrist["is_send_message"])){
                        $readCount +=1;
                    }else{
                        $unreadCount +=1;
                    }
                }
            }
            if(!empty($data[$rules_type]['weekly'])){
                $weeklys = common::handleDailyWeekedData($data[$rules_type]['weekly']);
                foreach($weeklys as $weeklyArr){
                    $weeklyFristAndFrist = utils::getDailyAndweeklyFrist($weeklyArr);
                    $instant_sum[]= $weeklyFristAndFrist;
                    if(!empty($weeklyFristAndFrist["is_send_message"])){
                        $readCount +=1;
                    }else{
                        $unreadCount +=1;
                    }
                }
            }

            //根据时间顺序排序
            $insert_dates = array_column($instant_sum, 'insert_date');
            array_multisort($insert_dates, SORT_DESC, $instant_sum);

            $info = array();
            foreach($instant_sum as $mInfo){
                $eventCard = $this->getEventCard($mInfo);
                if(!empty($mInfo['other_type']) && $mInfo['other_type'] == "password"){
                    $info[] = array("notificationType"=>"password","info" =>$eventCard);
                }elseif(!empty($mInfo['other_type']) && $mInfo['other_type'] == "feature"){
                    $info[] = array("notificationType"=>"feature","info" =>$eventCard);
                }else{
                    $info[] = array("notificationType"=>"event","info" =>$eventCard);
                }
            }

            //返回数据结构
            $returnData = array();
            $m_rc = empty($count['m_rc']) ? 0 : intval($count['m_rc']);
            $cs_rc = empty($count['cs_rc']) ? 0 : intval($count['cs_rc']);
            $da_rc = empty($count['da_rc']) ? 0 : intval($count['da_rc']);
            $ec_rc = empty($count['ec_rc']) ? 0 : intval($count['ec_rc']);
            $f_rc = empty($count['f_rc']) ? 0 : intval($count['f_rc']);
            $returnData['countList'] = array($m_rc,$cs_rc,$da_rc,$ec_rc,$f_rc);
            $returnData['allCount'] =count($instant_sum);
            $returnData['unreadCount'] =$unreadCount;
            $returnData['readCount'] =$readCount;
            $returnData['cardList'] =$info;

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

        if ($operate == "check_notifications_message"){
            $checkUnread = "select id
                from public.kln_notifiation_info ni  
                    where lower(ni.user_login) in ('".strtolower(_getLoginName())."','all_user')
                        and (ni.frequency_type = 'Instant' 
                            or (ni.frequency_type = 'Daily'  
                                and (case  when (timezone(ni.daily_time_zone, NOW())::time > ni.daily_time::time)
		                                then timezone(ni.daily_time_zone,ni.insert_date)::date < timezone(ni.daily_time_zone, NOW())::date
	                                else
		                                timezone(ni.daily_time_zone,ni.insert_date)::date < timezone(ni.daily_time_zone, NOW())::date - '1 days'::INTERVAL
	                                end))
                            or (ni.frequency_type = 'Weekly' 
                                and (case when (timezone(ni.weekly_time_zone, NOW())::time > ni.weekly_time::time)
                                        then timezone(ni.weekly_time_zone,ni.insert_date)::date <= (timezone(ni.weekly_time_zone,ni.insert_date)::date + (((ni.weekly_week::integer - EXTRACT(dow FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer + 7) % 7)-1 + CASE WHEN EXTRACT(DOW FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer=ni.weekly_week::integer THEN 7 ELSE 0 END ||' days')::INTERVAL)::date
                                    else 
                                        timezone(ni.weekly_time_zone,ni.insert_date)::date < (timezone(ni.weekly_time_zone,ni.insert_date)::date - (((EXTRACT(dow FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer - ni.weekly_week::integer + 7) % 7) ||' days')::INTERVAL)::date
                                    end))) 
                        and ni.notifications_method = true and is_send_message is null limit 1";
            $unread = common::excuteObjectSql($checkUnread); 
            $returnData = array("has_message" =>!empty($unread));
            common::echo_json_encode(200,$returnData);      
        }
    }

    /**
     * 遍历查找对应的rule。
     */
    public function getSubscribeRules($rule_name,$subscribe_rules){
        //初始是不显示，没有值的情况
        $ret = array("is_display" => false);
        foreach($subscribe_rules as $rules){
            if($rules['rules_type'] == $rule_name){
                $rules["is_display"] = true;
                $rules["daily_time"] = $rules["_daily_time"];
                $rules["weekly_time"] = $rules["_weekly_time"];
                $rules["weekly_week"] = common::getWeek($rules["weekly_week"]);

                //字符串转数字
                $rules["ocean_atd_sub_etd"] = common::convertoint($rules["ocean_atd_sub_etd"]);
                $rules["ocean_ata_sub_eta"] = common::convertoint($rules["ocean_ata_sub_eta"]);
                $rules["air_atd_sub_etd"] = common::convertoint($rules["air_atd_sub_etd"]);
                $rules["air_ata_sub_eta"] = common::convertoint($rules["air_ata_sub_eta"]);

                $rules["ocean_etd_old_sub_new"] = common::convertoint($rules["ocean_etd_old_sub_new"]);
                $rules["ocean_eta_old_sub_new"] = common::convertoint($rules["ocean_eta_old_sub_new"]);
                $rules["air_etd_old_sub_new"] = common::convertoint($rules["air_etd_old_sub_new"]);
                $rules["air_eta_old_sub_new"] = common::convertoint($rules["air_eta_old_sub_new"]);

                $rules["shipment_etd_limit"] = common::convertoint($rules["shipment_etd_limit"]);
                $rules["shipment_eta_limit"] = common::convertoint($rules["shipment_eta_limit"]);
                $rules["shipment_etd_limit_from"] = common::convertoint($rules["shipment_etd_limit_from"]);
                $rules["shipment_eta_limit_from"] = common::convertoint($rules["shipment_eta_limit_from"]);

                // $air_etd_old_sub_new_unit=="Day(s)" ? "days":"hours";
                $rules["ocean_atd_sub_etd_unit"] = common::convertoVue($rules["ocean_atd_sub_etd_unit"]);
                $rules["ocean_ata_sub_eta_unit"] = common::convertoVue($rules["ocean_ata_sub_eta_unit"]);
                $rules["air_atd_sub_etd_unit"] = common::convertoVue($rules["air_atd_sub_etd_unit"]);
                $rules["air_ata_sub_eta_unit"] = common::convertoVue($rules["air_ata_sub_eta_unit"]);

                $rules["ocean_etd_old_sub_new_unit"] = common::convertoVue($rules["ocean_etd_old_sub_new_unit"]);
                $rules["ocean_eta_old_sub_new_unit"] = common::convertoVue($rules["ocean_eta_old_sub_new_unit"]);
                $rules["air_etd_old_sub_new_unit"] = common::convertoVue($rules["air_etd_old_sub_new_unit"]);
                $rules["air_eta_old_sub_new_unit"] = common::convertoVue($rules["air_eta_old_sub_new_unit"]);
                
                $ret = $rules;
            }
        }
        //Milestone Update的结构处理,处理init page load
        if($rule_name == "Milestone_Update"){
            //Milestone Update的页面配置数据
            $milestones = common::excuteListSql("select * from public.customer_service_milestone_sno order by type, sno");
            $oceanMilestone = array();
            $airMilestone = array();
            foreach($milestones as $milestone){
                if($milestone['type'] == "air"){
                    $airMilestone[] = array("label"=>$milestone['description'],"value"=>$milestone['code']);
                } 
                if($milestone['type'] == "sea"){
                    $oceanMilestone[] = array("label"=>$milestone['description'],"value"=>$milestone['code']);
                }
            }
            $ret["OceanCheckBoxList"] = $oceanMilestone;
            $ret["AirCheckBoxList"] = $airMilestone;

            $oceanMilestoneSetting = !empty($ret['ocean_milestone']) ? explode(";",$ret['ocean_milestone']) : array();
            $airMilestoneSetting = !empty($ret['air_milestone']) ? explode(";",$ret['air_milestone']): array();
            $ret["OceanCheckedList"] = $oceanMilestoneSetting;
            $ret["AirCheckedList"] = $airMilestoneSetting;
        }
        //Milestone Update的结构处理
        if($rule_name == "Container_Status_Update"){
            //这里基准event 写死, 根据online查询页面的通用的来, 这里需提问确定
            //$event =common::getEDICtnrEvent();
            $event = common::excuteListSql("select ra_name as event_name,ra_order,description 
                from public.ra_online_edi_event e 
                 where e.ra_name in('I','VD','VA','UV','AL','AR','OA','RD') order by e.ra_order desc");
            $ctnrStatus = array();
            foreach($event as $e){
                $ctnrStatus[] = array("label"=>$e['description'],"value"=>$e['event_name']);
            }
            $ret["CtnrCheckBoxList"] = $ctnrStatus;
           
            $ctnrStatusSetting = !empty($ret['ocean_ctnr_status']) ? explode(";",$ret['ocean_ctnr_status']) : array();
            $ret["CtnrCheckedList"] = $ctnrStatusSetting;
            
        }
        return $ret;
    }

    /**
     * 查询对应用户订阅的shipment信息.可能存在分页查询，如果有需要就改正
     * cp current_page
     */
    public function getSubscribeShipment($cp,$ps){
        if (empty($cp)){
            $cp = 1;
        }
        if (empty($ps)){
            $ps = 15;
        }
        $sql = "select count(1) from  public.kln_user_subscribed u
                left join public.kln_ocean o on  o.serial_no  = u.subscribed_serial_no 
            where  lower(user_login) = '".strtolower(_getLoginName())."'";
        $rc = common::excuteOneSql($sql);
        $tp = ceil($rc / $ps);
        if ($rc > 0) {
            $sql = "select o.serial_no,order_from as _schemas,o.h_bol,
                        o.shipper,o.consignee,o.etd,o.eta,
                        case when transport_mode = 'sea'  
                                then (select  sn.description
                                    from  public.ocean_milestone a  
                                        inner join public.customer_service_milestone_sno sn on sn.code=a.code and sn.type = 'sea'
                                    where a.serial_no=o.serial_no and act_date is not null  order by sn.sno desc limit 1)
                            when transport_mode = 'air' and  order_from = 'public'
                                then (select  sn.description
                                    from  public.air_milestone a  
                                        inner join public.customer_service_milestone_sno sn on sn.code=a.code and sn.type = 'air'
                                    where a.serial_no=o.serial_no and act_date is not null  order by sn.sno desc limit 1)
                            when transport_mode = 'air' and  order_from = 'sfs'
                                then (select  sn.description
                                    from  sfs.air_milestone a  
                                        inner join public.customer_service_milestone_sno sn on sn.code=a.code and sn.type = 'air'
                                    where a.serial_no=o.serial_no and act_date is not null  order by sn.sno desc limit 1)
                            else '' end as recent_milestone				
                    from  public.kln_user_subscribed u 
                        left join public.kln_ocean o on  o.serial_no  = u.subscribed_serial_no
                    where  lower(user_login) = '".strtolower(_getLoginName())."' order by u.id desc limit " . $ps . " offset " . ($cp - 1) * $ps;
            $subscribeShipment = common::excuteListSql($sql);
            foreach($subscribeShipment as $key => $val){
                $subscribeShipment[$key]["__serial_no"] = common::deCode($val['serial_no'], 'E');
            }
            $arrTmp = array('tableData' => $subscribeShipment, 
                'rc' => intval($rc),
                'ps' => intval($ps),
                'cp' => intval($cp),
                'tp' => intval($tp)
            );
        } else {
            $arrTmp = array('tableData' => array(), 
                'rc' => intval($rc),
                'ps' => intval($ps),
                'cp' => intval($cp),
                'tp' => intval($tp),
            );
        }
        return $arrTmp;
    }

    public function checkedNotificationParam(){
        $rules_type = common::check_input($_POST["rules_type"]);
        //先判断异常数据
        $msg = "";
        if ($rules_type == "Departure/Arrival_Delay"){
            $ocean_atd_sub_etd = common::check_input($_POST['ocean_atd_sub_etd']);
            if (!empty($ocean_atd_sub_etd) && !ctype_digit($ocean_atd_sub_etd)) {
                $msg = "Ocean Delayed(ATD-ETD) is not Number";
            }
           
            $ocean_ata_sub_eta = common::check_input($_POST['ocean_ata_sub_eta']);
            if (!empty($ocean_ata_sub_eta) && !ctype_digit($ocean_ata_sub_eta)) {
                $msg = "Ocean Delayed(ATA-ETA) is not Number";
            }

            $air_atd_sub_etd = common::check_input($_POST['air_atd_sub_etd']);
            if (!empty($air_atd_sub_etd) && !ctype_digit($air_atd_sub_etd)) {
                $msg = "Air Delayed(ATD-ETD) is not Number";
            }

            $air_ata_sub_eta = common::check_input($_POST['air_ata_sub_eta']);
            if (!empty($air_ata_sub_eta) && !ctype_digit($air_ata_sub_eta)) {
                $msg = "Air Delayed(ATA-ETA) is not Number";
            }
        }

        if ($rules_type == "ETD/ETA_Change"){
            $ocean_etd_old_sub_new = common::check_input($_POST['ocean_etd_old_sub_new']);
            if (!empty($ocean_etd_old_sub_new) && !ctype_digit($ocean_etd_old_sub_new)) {
                $msg = "Ocean Notify(ETD) is not Number";
            }
           
            $ocean_eta_old_sub_new = common::check_input($_POST['ocean_eta_old_sub_new']);
            if (!empty($ocean_eta_old_sub_new) && !ctype_digit($ocean_eta_old_sub_new)) {
                $msg = "Ocean Notify(ETA) is not Number";
            }
            

            $air_etd_old_sub_new = common::check_input($_POST['air_etd_old_sub_new']);
            if (!empty($air_etd_old_sub_new) && !ctype_digit($air_etd_old_sub_new)) {
                $msg = "Air Notify(ETD) is not Number";
            }
            
            $air_eta_old_sub_new = common::check_input($_POST['air_eta_old_sub_new']);
            if (!empty($air_eta_old_sub_new) && !ctype_digit($air_eta_old_sub_new)) {
                $msg = "Air Notify(ETA) is not Number";
            }
            
        }

        return $msg;
    }

    public function getNotificationsRulesUpdateSql($updateOrInsert,$rules_type,$notifications_type,$id){
        $sql = "";
        //先删后加 这个逻辑有移除
        // if($updateOrInsert == "update"){
        //     $sql.="delete from public.notifications_rules where rules_type = '$rules_type' 
        //             and notifications_type = '$notifications_type' and  lower(user_login) = '".strtolower(_getLoginName())."'
        //             and id = '$id';";
        // }
        //这个几个参数是所有规则都有的参数
        $frequency_type = common::check_input($_POST['frequency_type']);
        $daily_time  = "null";
        $daily_time_zone = "";
        $weekly_week =  "";
        $weekly_time =  "null";
        $weekly_time_zone =  "";
        if(strtolower($frequency_type) == "daily"){
            $daily_time = "'".common::check_input($_POST['daily_time'])."'";
            $daily_time_zone = common::check_input($_POST['daily_time_zone']);
        } elseif (strtolower($frequency_type) == "weekly"){
            $weekly_week = common::check_input($_POST['weekly_week']);
            $weekly_time = "'".common::check_input($_POST['weekly_time'])."'";
            $weekly_time_zone = common::check_input($_POST['weekly_time_zone']);
        }

        
        $method_by_email = !empty($_POST['method_by_email']) ? common::check_input($_POST['method_by_email']) : 'false';
        $method_by_message = !empty($_POST['method_by_message']) ? common::check_input($_POST['method_by_message']) : 'false';
        $event_details = common::check_input($_POST['event_details']);
        $frequency_display = common::check_input($_POST['frequency_display']);
        $method_display = common::check_input($_POST['method_display']);
        $shipment_detail = common::check_input($_POST['shipment_details']);

        //当规则是 Monitoring类型是,需要配置的range
        $shipment_transport_mode = "";
        $shipment_etd_limit = "";
        $shipment_eta_limit = "";
        $shipment_etd_limit_from = "";
        $shipment_eta_limit_from = "";
        if($notifications_type == "Monitoring"){
            $shipment_transport_mode = utils::converModeToDB($_POST['shipment_transport_mode']);
            $shipment_etd_limit = common::check_input($_POST['shipment_etd_limit']);
            $shipment_eta_limit = common::check_input($_POST['shipment_eta_limit']);

            $shipment_etd_limit_from = common::check_input($_POST['shipment_etd_limit_from']);
            $shipment_eta_limit_from = common::check_input($_POST['shipment_eta_limit_from']);
        }

        if ($rules_type == "Milestone_Update"){
            $ocean_milestone = utils::implode(";",$_POST['ocean_milestone']);
            $air_milestone =  utils::implode(";",$_POST['air_milestone']);

            //改为update  不在是删除后添加，为了保留原始顺序
            if ($updateOrInsert == "update"){ 
                $sql.="update public.notifications_rules set ocean_milestone = '$ocean_milestone',air_milestone = '$air_milestone',
                    frequency_type = '$frequency_type',daily_time = $daily_time, daily_time_zone = '$daily_time_zone',
                    weekly_week = '$weekly_week', weekly_time = $weekly_time, weekly_time_zone = '$weekly_time_zone', method_by_email = '$method_by_email',method_by_message = '$method_by_message',
                    event_details = '$event_details', frequency_display = '$frequency_display', method_display = '$method_display', shipment_details = '$shipment_detail',
                    shipment_transport_mode = '$shipment_transport_mode', shipment_etd_limit = '$shipment_etd_limit', shipment_eta_limit = '$shipment_eta_limit',
                    shipment_etd_limit_from = '$shipment_etd_limit_from', shipment_eta_limit_from = '$shipment_eta_limit_from'
                    where rules_type = '$rules_type' 
                        and notifications_type = '$notifications_type' and  lower(user_login) = '".strtolower(_getLoginName())."'
                        and id = '$id';";
            } else {
            $sql.="INSERT INTO public.notifications_rules(
                    user_login, notifications_type, rules_type, ocean_milestone, 
                    air_milestone, frequency_type, daily_time, daily_time_zone,
                    weekly_week, weekly_time, weekly_time_zone, method_by_email, method_by_message,
                    event_details, frequency_display, method_display,shipment_details,
                    shipment_transport_mode,shipment_etd_limit,shipment_eta_limit,shipment_etd_limit_from,shipment_eta_limit_from)
            VALUES ('".strtolower(_getLoginName())."', '$notifications_type', '$rules_type', '$ocean_milestone',
                    '$air_milestone', '$frequency_type', $daily_time, '$daily_time_zone', 
                    '$weekly_week', $weekly_time, '$weekly_time_zone', '$method_by_email', '$method_by_message',
                    '$event_details', '$frequency_display', '$method_display','$shipment_detail',
                    '$shipment_transport_mode','$shipment_etd_limit','$shipment_eta_limit','$shipment_etd_limit_from','$shipment_eta_limit_from');";
            }
        }

        if ($rules_type == "Container_Status_Update"){
            $ocean_ctnr_status = utils::implode(";",$_POST['ocean_ctnr_status']);

            //改为update  不在是删除后添加，为了保留原始顺序
            if ($updateOrInsert == "update"){ 
                $sql.="update public.notifications_rules set ocean_ctnr_status = '$ocean_ctnr_status',
                    frequency_type = '$frequency_type',daily_time = $daily_time, daily_time_zone = '$daily_time_zone',
                    weekly_week = '$weekly_week', weekly_time = $weekly_time, weekly_time_zone = '$weekly_time_zone', method_by_email = '$method_by_email',method_by_message = '$method_by_message',
                    event_details = '$event_details', frequency_display = '$frequency_display', method_display = '$method_display', shipment_details = '$shipment_detail',
                    shipment_transport_mode = '$shipment_transport_mode', shipment_etd_limit = '$shipment_etd_limit', shipment_eta_limit = '$shipment_eta_limit',
                    shipment_etd_limit_from = '$shipment_etd_limit_from', shipment_eta_limit_from = '$shipment_eta_limit_from' 
                    where rules_type = '$rules_type' 
                        and notifications_type = '$notifications_type' and  lower(user_login) = '".strtolower(_getLoginName())."'
                        and id = '$id';";
            }else{
            $sql.="INSERT INTO public.notifications_rules(
                    user_login, notifications_type, rules_type, ocean_ctnr_status, 
                    frequency_type, daily_time, daily_time_zone,
                    weekly_week, weekly_time, weekly_time_zone, method_by_email, method_by_message,
                    event_details, frequency_display, method_display,shipment_details,
                    shipment_transport_mode,shipment_etd_limit,shipment_eta_limit,shipment_etd_limit_from,shipment_eta_limit_from)
            VALUES ('".strtolower(_getLoginName())."', '$notifications_type', '$rules_type', '$ocean_ctnr_status',
                    '$frequency_type', $daily_time, '$daily_time_zone', 
                    '$weekly_week', $weekly_time, '$weekly_time_zone', '$method_by_email', '$method_by_message',
                    '$event_details', '$frequency_display', '$method_display','$shipment_detail',
                    '$shipment_transport_mode','$shipment_etd_limit','$shipment_eta_limit','$shipment_etd_limit_from','$shipment_eta_limit_from');";
            }
        }

        if ($rules_type == "Departure/Arrival_Delay"){
            $ocean_atd_sub_etd = common::check_input($_POST['ocean_atd_sub_etd']);
            $ocean_atd_sub_etd_unit = common::check_input($_POST['ocean_atd_sub_etd_unit']);
            if(!empty($ocean_atd_sub_etd_unit)){
                $ocean_atd_sub_etd_unit = $ocean_atd_sub_etd_unit=="Day(s)" ? "days":"hours";
            }
            $ocean_ata_sub_eta = common::check_input($_POST['ocean_ata_sub_eta']);
            $ocean_ata_sub_eta_unit = common::check_input($_POST['ocean_ata_sub_eta_unit']);
            if(!empty($ocean_ata_sub_eta_unit)){
                $ocean_ata_sub_eta_unit = $ocean_ata_sub_eta_unit=="Day(s)" ? "days":"hours";
            }

            $air_atd_sub_etd = common::check_input($_POST['air_atd_sub_etd']);
            $air_atd_sub_etd_unit = common::check_input($_POST['air_atd_sub_etd_unit']);
            if(!empty($air_atd_sub_etd_unit)){
                $air_atd_sub_etd_unit = $air_atd_sub_etd_unit=="Day(s)" ? "days":"hours";
            }
            $air_ata_sub_eta = common::check_input($_POST['air_ata_sub_eta']);
            $air_ata_sub_eta_unit = common::check_input($_POST['air_ata_sub_eta_unit']);
            if(!empty($air_ata_sub_eta_unit)){
                $air_ata_sub_eta_unit = $air_ata_sub_eta_unit=="Day(s)" ? "days":"hours";
            }

            //改为update  不在是删除后添加，为了保留原始顺序
            if ($updateOrInsert == "update"){
                $sql.="update public.notifications_rules set 
                    ocean_atd_sub_etd = '$ocean_atd_sub_etd',ocean_atd_sub_etd_unit = '$ocean_atd_sub_etd_unit',
                    ocean_ata_sub_eta = '$ocean_ata_sub_eta',ocean_ata_sub_eta_unit = '$ocean_ata_sub_eta_unit',
                    air_atd_sub_etd = '$air_atd_sub_etd',air_atd_sub_etd_unit = '$air_atd_sub_etd_unit',
                    air_ata_sub_eta = '$air_ata_sub_eta',air_ata_sub_eta_unit = '$air_ata_sub_eta_unit',
                    frequency_type = '$frequency_type',daily_time = $daily_time, daily_time_zone = '$daily_time_zone',
                    weekly_week = '$weekly_week', weekly_time = $weekly_time, weekly_time_zone = '$weekly_time_zone', method_by_email = '$method_by_email',method_by_message = '$method_by_message',
                    event_details = '$event_details', frequency_display = '$frequency_display', method_display = '$method_display', shipment_details = '$shipment_detail',
                    shipment_transport_mode = '$shipment_transport_mode', shipment_etd_limit = '$shipment_etd_limit', shipment_eta_limit = '$shipment_eta_limit',
                    shipment_etd_limit_from = '$shipment_etd_limit_from', shipment_eta_limit_from = '$shipment_eta_limit_from' 
                    where rules_type = '$rules_type' 
                        and notifications_type = '$notifications_type' and  lower(user_login) = '".strtolower(_getLoginName())."'
                        and id = '$id';";
            }else{
            $sql.="INSERT INTO public.notifications_rules(
                    user_login, notifications_type, rules_type,
                    ocean_atd_sub_etd, ocean_atd_sub_etd_unit,ocean_ata_sub_eta,ocean_ata_sub_eta_unit,
                    air_atd_sub_etd, air_atd_sub_etd_unit,air_ata_sub_eta,air_ata_sub_eta_unit,
                    frequency_type, daily_time, daily_time_zone,
                    weekly_week, weekly_time, weekly_time_zone, method_by_email, method_by_message,
                    event_details, frequency_display, method_display,shipment_details,
                    shipment_transport_mode,shipment_etd_limit,shipment_eta_limit,shipment_etd_limit_from,shipment_eta_limit_from)
            VALUES ('".strtolower(_getLoginName())."', '$notifications_type', '$rules_type', 
                    '$ocean_atd_sub_etd','$ocean_atd_sub_etd_unit','$ocean_ata_sub_eta','$ocean_ata_sub_eta_unit',
                    '$air_atd_sub_etd','$air_atd_sub_etd_unit','$air_ata_sub_eta','$air_ata_sub_eta_unit',
                    '$frequency_type', $daily_time, '$daily_time_zone', 
                    '$weekly_week', $weekly_time, '$weekly_time_zone', '$method_by_email', '$method_by_message',
                    '$event_details', '$frequency_display', '$method_display','$shipment_detail',
                    '$shipment_transport_mode','$shipment_etd_limit','$shipment_eta_limit','$shipment_etd_limit_from','$shipment_eta_limit_from');";
            }
        }

        if ($rules_type == "ETD/ETA_Change"){
            $ocean_etd_change = !empty($_POST['ocean_etd_change']) ? common::check_input($_POST['ocean_etd_change']) : 'false';
            $ocean_etd_old_sub_new = common::check_input($_POST['ocean_etd_old_sub_new']);
            $ocean_etd_old_sub_new_unit = common::check_input($_POST['ocean_etd_old_sub_new_unit']);
            if(!empty($ocean_etd_old_sub_new_unit)){
                $ocean_etd_old_sub_new_unit = $ocean_etd_old_sub_new_unit=="Day(s)" ? "days":"hours";
            }
            $ocean_eta_change = !empty($_POST['ocean_eta_change']) ? common::check_input($_POST['ocean_eta_change']) : 'false';
            $ocean_eta_old_sub_new = common::check_input($_POST['ocean_eta_old_sub_new']);
            $ocean_eta_old_sub_new_unit = common::check_input($_POST['ocean_eta_old_sub_new_unit']);
            if(!empty($ocean_eta_old_sub_new_unit)){
                $ocean_eta_old_sub_new_unit = $ocean_eta_old_sub_new_unit=="Day(s)" ? "days":"hours";
            }

            $air_etd_change = !empty($_POST['air_etd_change']) ? common::check_input($_POST['air_etd_change']) : 'false';
            $air_etd_old_sub_new = common::check_input($_POST['air_etd_old_sub_new']);
            $air_etd_old_sub_new_unit = common::check_input($_POST['air_etd_old_sub_new_unit']);
            if(!empty($air_etd_old_sub_new_unit)){
                $air_etd_old_sub_new_unit = $air_etd_old_sub_new_unit=="Day(s)" ? "days":"hours";
            }
            $air_eta_change = !empty($_POST['air_eta_change']) ? common::check_input($_POST['air_eta_change']): 'false';
            $air_eta_old_sub_new = common::check_input($_POST['air_eta_old_sub_new']);
            $air_eta_old_sub_new_unit = common::check_input($_POST['air_eta_old_sub_new_unit']);
            if(!empty($air_eta_old_sub_new_unit)){
                $air_eta_old_sub_new_unit = $air_eta_old_sub_new_unit=="Day(s)" ? "days":"hours";
            }
            //改为update  不在是删除后添加，为了保留原始顺序
            if ($updateOrInsert == "update"){
                $sql.="update public.notifications_rules set 
                    ocean_etd_change = '$ocean_etd_change',ocean_etd_old_sub_new = '$ocean_etd_old_sub_new',ocean_etd_old_sub_new_unit = '$ocean_etd_old_sub_new_unit',
                    ocean_eta_change = '$ocean_eta_change',ocean_eta_old_sub_new = '$ocean_eta_old_sub_new',ocean_eta_old_sub_new_unit = '$ocean_eta_old_sub_new_unit',

                    air_etd_change = '$air_etd_change',air_etd_old_sub_new = '$air_etd_old_sub_new',air_etd_old_sub_new_unit = '$air_etd_old_sub_new_unit',
                    air_eta_change = '$air_eta_change',air_eta_old_sub_new = '$air_eta_old_sub_new',air_eta_old_sub_new_unit = '$air_eta_old_sub_new_unit',

                    frequency_type = '$frequency_type',daily_time = $daily_time, daily_time_zone = '$daily_time_zone',
                    weekly_week = '$weekly_week', weekly_time = $weekly_time, weekly_time_zone = '$weekly_time_zone', method_by_email = '$method_by_email',method_by_message = '$method_by_message',
                    event_details = '$event_details', frequency_display = '$frequency_display', method_display = '$method_display', shipment_details = '$shipment_detail',
                    shipment_transport_mode = '$shipment_transport_mode', shipment_etd_limit = '$shipment_etd_limit', shipment_eta_limit = '$shipment_eta_limit',
                    shipment_etd_limit_from = '$shipment_etd_limit_from', shipment_eta_limit_from = '$shipment_eta_limit_from'  
                    where rules_type = '$rules_type' 
                        and notifications_type = '$notifications_type' and  lower(user_login) = '".strtolower(_getLoginName())."'
                        and id = '$id';";
            }else{
            $sql.="INSERT INTO public.notifications_rules(
                    user_login, notifications_type, rules_type,
                    ocean_etd_change, ocean_etd_old_sub_new,ocean_etd_old_sub_new_unit,ocean_eta_change,ocean_eta_old_sub_new,ocean_eta_old_sub_new_unit,
                    air_etd_change, air_etd_old_sub_new,air_etd_old_sub_new_unit,air_eta_change,air_eta_old_sub_new,air_eta_old_sub_new_unit,
                    frequency_type, daily_time, daily_time_zone,
                    weekly_week, weekly_time, weekly_time_zone, method_by_email, method_by_message,
                    event_details, frequency_display, method_display,shipment_details,
                    shipment_transport_mode,shipment_etd_limit,shipment_eta_limit,shipment_etd_limit_from,shipment_eta_limit_from)
            VALUES ('".strtolower(_getLoginName())."', '$notifications_type', '$rules_type', 
                    '$ocean_etd_change','$ocean_etd_old_sub_new','$ocean_etd_old_sub_new_unit','$ocean_eta_change','$ocean_eta_old_sub_new','$ocean_eta_old_sub_new_unit',
                    '$air_etd_change','$air_etd_old_sub_new','$air_etd_old_sub_new_unit','$air_eta_change','$air_eta_old_sub_new','$air_eta_old_sub_new_unit',
                    '$frequency_type', $daily_time, '$daily_time_zone', 
                    '$weekly_week', $weekly_time, '$weekly_time_zone', '$method_by_email', '$method_by_message',
                    '$event_details', '$frequency_display', '$method_display','$shipment_detail',
                    '$shipment_transport_mode','$shipment_etd_limit','$shipment_eta_limit','$shipment_etd_limit_from','$shipment_eta_limit_from');";
            }
        }
        return $sql;
    }

    /**
     * 检查编辑提交的Monitoring规则，是否允许保存
     */
    public function checkedMonitoringRulesSave($rules_type){
        $sql_where = "";
        if(isset($_POST['id']) && !empty($_POST['id'])){
            $sql_where = " and id <> '".common::check_input($_POST['id'])."'";
        } 
        $rules = common::excuteListSql("select *,
                    TO_CHAR(daily_time, 'HH24:MI') as _daily_time,
                    TO_CHAR(weekly_time, 'HH24:MI') as _weekly_time
                from public.notifications_rules where notifications_type = 'Monitoring' and rules_type = '".$rules_type."'
                    and  lower(user_login) = '".strtolower(_getLoginName())."' $sql_where");
        foreach($rules as $rule){
            //判断range 是否一样
            $checkRangeFiled = array("shipment_transport_mode","shipment_etd_limit","shipment_eta_limit","shipment_etd_limit_from","shipment_eta_limit_from");
            $range_flag = true;   
            foreach($checkRangeFiled as $filed){
                if($filed == "shipment_transport_mode"){
                    $postValue = utils::converModeToDB($_POST[$filed]);
                    $rule_mode_arr = explode(";", $rule[$filed]);
                    $post_mode_arr = explode(";", $postValue);
                    if(!utils::compareArrayEq($post_mode_arr,$rule_mode_arr)){
                        $range_flag = false;
                    }
                }else{
                    //正常字段直接比较就行
                    $postValue = !empty($_POST[$filed]) ? $_POST[$filed] : "";
                    $dbValue = !empty($rule[$filed]) ? $rule[$filed] : "";
                    if($postValue != $dbValue){
                        $range_flag = false;
                    }
                }
            }

            //判断details 是否一样
            $checkDetailsFiled = array("ocean_milestone","air_milestone","ocean_ctnr_status",
                "ocean_atd_sub_etd","ocean_atd_sub_etd_unit","ocean_ata_sub_eta","ocean_ata_sub_eta_unit",
                "air_atd_sub_etd","air_atd_sub_etd_unit","air_ata_sub_eta","air_ata_sub_eta_unit",
                "ocean_etd_change","ocean_etd_old_sub_new","ocean_etd_old_sub_new_unit","ocean_eta_change","ocean_eta_old_sub_new","ocean_eta_old_sub_new_unit",
                "air_etd_change","air_etd_old_sub_new","air_etd_old_sub_new_unit","air_eta_change","air_eta_old_sub_new","air_eta_old_sub_new_unit"); 
            $details_flag = true;     
            foreach($checkDetailsFiled as $filed){
                if($filed == "ocean_milestone" || $filed == "air_milestone" || $filed == "ocean_ctnr_status"){
                    $rule_mode_arr = empty($rule[$filed])? array() :explode(";", $rule[$filed]);
                    $post_mode_arr = $_POST[$filed];
                    if(!utils::compareArrayEq($post_mode_arr,$rule_mode_arr)){
                        $details_flag = false;
                    }
                } elseif ($filed == "ocean_etd_change" || $filed == "ocean_eta_change" || $filed == "air_etd_change" || $filed == "air_eta_change"){
                    $post_boolean = (empty($_POST[$filed]) || $_POST[$filed] == "false") ? "f":"t";
                    if($post_boolean != $rule[$filed]){
                        $details_flag = false;
                    }
                } elseif ($filed == "ocean_atd_sub_etd_unit" || $filed == "ocean_ata_sub_eta_unit"
                    || $filed == "air_atd_sub_etd_unit" || $filed == "air_ata_sub_eta_unit"
                    || $filed == "ocean_etd_old_sub_new_unit" || $filed == "ocean_eta_old_sub_new_unit"
                    || $filed == "air_etd_old_sub_new_unit" || $filed == "air_eta_old_sub_new_unit"){

                    $postValue =  !empty($_POST[$filed]) ? $_POST[$filed] : "";
                    if(!empty($postValue)){
                        $postValue = $postValue=="Day(s)" ? "days":"hours";
                    }
                    $dbValue = !empty($rule[$filed]) ? $rule[$filed] : "";
                    if($postValue != $dbValue){
                        $details_flag = false;
                    }
                } else {
                    $postValue = !empty($_POST[$filed]) ? $_POST[$filed] : "";
                    $dbValue = !empty($rule[$filed]) ? $rule[$filed] : "";
                    if($postValue != $dbValue){
                        $details_flag = false;
                    }
                }
            }    
           
            //判断frequency 是否一样
            $checkFrequencyFiled = array("frequency_type","daily_time","daily_time_zone",
                "weekly_week","weekly_time","weekly_time_zone","daily_time_zone");
            $frequency_flag = true; 
            foreach($checkFrequencyFiled as $filed){
                if($filed == "daily_time" || $filed == "weekly_time"){
                    $postValue = !empty($_POST[$filed]) ? $_POST[$filed] : "";
                    //_daily_time _weekly_time
                    $dbValue = !empty($rule["_".$filed]) ? $rule["_".$filed] : "";
                    if($postValue != $dbValue){
                        $frequency_flag = false;
                    }
                } else {
                    $postValue = !empty($_POST[$filed]) ? $_POST[$filed] : "";
                    $dbValue = !empty($rule[$filed]) ? $rule[$filed] : "";
                    if($postValue != $dbValue){
                        $frequency_flag = false;
                    }
                }
            }
            
            //判断通知方式是否一样
            $checkMethodFiled = array("method_by_email","method_by_message");
            $method_flag = true; 
            foreach($checkMethodFiled as $filed){
                $postValue = (empty($_POST[$filed]) || $_POST[$filed] == "false") ? "f" : "t";
                if($postValue != $rule[$filed]){
                    $method_flag = false;
                }
            }

            //五个条件一样，不允许保存
            if($range_flag && $details_flag && $frequency_flag && $method_flag){
                $msg = "Unable to Save";
                break;
            }
            //前三个重回，后面不重合，提示但允许保存
            if($range_flag && $details_flag && $_POST['is_similar_rule'] <> 'true'){
                $msg = "Similar Rule Detected";
                break;
            }
        }
        return $msg;
    }


    public function getNotifications($notifiation_type,$frequency_type,$insert_date_format = null){
        if ($frequency_type == "all"){
            //Daily 频率：超过昨天的数据，不在限制提醒的daily_time，全部应该查出来
            //比如今天周四 17号, 上第一周截止是10号， 10号以后的数据，不在做weekly_time和weekly_week 的限制，直接查出来
            $sql_where = " and (ni.frequency_type = 'Instant' 
                            or (ni.frequency_type = 'Daily'  
                                and (case  when (timezone(ni.daily_time_zone, NOW())::time > ni.daily_time::time)
		                                then timezone(ni.daily_time_zone,ni.insert_date)::date < timezone(ni.daily_time_zone, NOW())::date
	                                else
		                                timezone(ni.daily_time_zone,ni.insert_date)::date < timezone(ni.daily_time_zone, NOW())::date - '1 days'::INTERVAL
	                                end))
                                    
                            or (ni.frequency_type = 'Weekly' 
                                and (case when (timezone(ni.weekly_time_zone, NOW())::time > ni.weekly_time::time)
                                        then timezone(ni.weekly_time_zone,ni.insert_date)::date <= (timezone(ni.weekly_time_zone,ni.insert_date)::date + (((ni.weekly_week::integer - EXTRACT(dow FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer + 7) % 7)-1 + CASE WHEN EXTRACT(DOW FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer=ni.weekly_week::integer THEN 7 ELSE 0 END ||' days')::INTERVAL)::date
                                    else 
                                        timezone(ni.weekly_time_zone,ni.insert_date)::date < (timezone(ni.weekly_time_zone,ni.insert_date)::date - (((EXTRACT(dow FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer - ni.weekly_week::integer + 7) % 7) ||' days')::INTERVAL)::date
                                    end)))";
        } elseif($frequency_type == "Daily"){
            $sql_where = " and (ni.frequency_type = 'Daily'  
                                and (case  when (timezone(ni.daily_time_zone, NOW())::time > ni.daily_time::time)
		                                then timezone(ni.daily_time_zone,ni.insert_date)::date < timezone(ni.daily_time_zone, NOW())::date
	                                else
		                                timezone(ni.daily_time_zone,ni.insert_date)::date < timezone(ni.daily_time_zone, NOW())::date - '1 days'::INTERVAL
	                                end))";

        } elseif($frequency_type == "Weekly"){
            $sql_where = " and (ni.frequency_type = 'Weekly' 
                                and (case when (timezone(ni.weekly_time_zone, NOW())::time > ni.weekly_time::time)
                                        then timezone(ni.weekly_time_zone,ni.insert_date)::date <= (timezone(ni.weekly_time_zone,ni.insert_date)::date + (((ni.weekly_week::integer - EXTRACT(dow FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer + 7) % 7)-1 + CASE WHEN EXTRACT(DOW FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer=ni.weekly_week::integer THEN 7 ELSE 0 END ||' days')::INTERVAL)::date
                                    else 
                                        timezone(ni.weekly_time_zone,ni.insert_date)::date < (timezone(ni.weekly_time_zone,ni.insert_date)::date - (((EXTRACT(dow FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer - ni.weekly_week::integer + 7) % 7) ||' days')::INTERVAL)::date
                                    end))";
        }
        if (!empty($_REQUEST['current_time'])){
            // $sql_where .= " and ni.insert_date >= TO_TIMESTAMP('".$_REQUEST['current_time']."', 'MM/DD/YYYY HH24:MI:SS') - interval '5 minutes'
            //     and ni.is_send_message is null";
            //这里都以服务器时间，检查标准
            $sql_where .= " and ni.is_send_message is null";
        }
        if (!empty($_REQUEST['info_type']) && $_REQUEST['info_type'] == 'true'){
            $sql_where .= " and ni.is_send_message is null";
        }

        //这里的查询会把不同日期的但hbol相同的信息，过滤掉只剩下最新的那一条。所以移除
        $aa_where = "";
        if (!empty($insert_date_format)){
            $aa_where = " where insert_date_format = '$insert_date_format'";
        }
        
        $more_param = common::getInNotInSqlForSearch($notifiation_type);
        $sql = "select *
                 from  (select ni.*,
                    case when ni.notifiation_type = 'Departure/Arrival_Delay' and ni.delay_unit = 'days'
                            then (EXTRACT(DAY FROM ((delay_act_date||' '||delay_act_time)::timestamp - (delay_est_date||' '||delay_est_time)::timestamp)))
                        when ni.notifiation_type = 'Departure/Arrival_Delay' and ni.delay_unit = 'hours'
                            then (FLOOR(EXTRACT(epoch FROM ((delay_act_date||' '||delay_act_time)::timestamp - (delay_est_date||' '||delay_est_time)::timestamp))/3600))
                        else 0 
                    end  as _delay_diff,

                    case when COALESCE(ni.frequency_type,'') = 'Daily'
                            then to_char(timezone(ni.daily_time_zone, ni.insert_date),'Mon DD, YYYY')
                        when COALESCE(ni.frequency_type,'') = 'Weekly' 
                            then to_char(timezone(ni.weekly_time_zone,ni.insert_date)::date - (((EXTRACT(dow FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer - ni.weekly_week::integer + 7) % 7) ||' days')::INTERVAL,'Mon DD, YYYY')
                            || ' - ' ||
                            to_char(timezone(ni.weekly_time_zone,ni.insert_date)::date + (((ni.weekly_week::integer - EXTRACT(dow FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer + 7) % 7)-1 + CASE WHEN EXTRACT(DOW FROM timezone(ni.weekly_time_zone,ni.insert_date)::date)::integer=ni.weekly_week::integer THEN 7 ELSE 0 END ||' days')::INTERVAL,'Mon DD, YYYY')
                        else '' 
                    end  as insert_date_format,

                    case when ni.notifiation_type ='Milestone_Update' 
                        then public.getPreviousMilestone(ni.serial_no,ni.milestone_code,ccc.transport_mode,ccc.order_from)
                        else ''
                    end as milestone_previous_json,

                    case when ni.notifiation_type ='Container_Status_Update' 
                        then public.getPreviousCtnrStatus(ni.serial_no,ni.ctnr,ni.ctnr_status_code)
                        else ''
                    end as ctnr_previous_json,

                    case when ni.notifiation_type ='Container_Status_Update' 
                        then (select description 
                                    from public.ra_online_edi_event e where e.ra_name = ni.ctnr_status_code limit 1)
                        else ''
                    end as ctnr_desc,

                    ccc.order_from,ccc.h_bol,ccc.transport_mode
                from public.kln_notifiation_info ni
                        left join LATERAL (select oo.h_bol,oo.transport_mode,oo.order_from 
                            from public.kln_ocean oo 
                            where oo.serial_no = ni.serial_no limit 1) ccc on true
                where lower(ni.user_login) in ('".strtolower(_getLoginName())."','all_user')
                        and ni.insert_date > NOW() - INTERVAL '1 year' 
                        and lower(ni.notifiation_type) in ($more_param)
                    ".$sql_where." and ni.notifications_method = true order by ni.insert_date desc) aa $aa_where";
        error_log($sql);

        $data_all_type = common::excuteListSql($sql);
        $data_group = array();
        $data_group_uniqe = array();
        foreach($data_all_type as $dat){
            $uniqe_group_str = $dat['notifiation_type'];
            if(utils::in_array($uniqe_group_str,$data_group_uniqe)){
                $tempArr = $data_group[$uniqe_group_str];
                $tempArr[] = $dat;
                $data_group[$uniqe_group_str] = $tempArr;
            } else {
                $data_group[$uniqe_group_str] = array($dat);
                $data_group_uniqe[] = $uniqe_group_str;
            }
        }

        $retData = array();
        foreach($data_group as $key => $data){
            $notifiation_type_db = $key;
            //统一处理数据Instant Daily weekly_week 先分开在处理
            $instant = array();
            $daily = array();
            $daily_uniqe = array();

            $weekly  = array();
            $weekly_uniqe = array();
            foreach($data as $d){
                if ($d['frequency_type'] == "Instant"){
                    $instant[] = $d;
                }
                //Daily 或者 Weekly类型为这个时才用这个去重，否则要加上描述（转船的情况，会让相同的HBOL显示）
                $uniqe_str = $d['serial_no'];
                if ($notifiation_type_db == "Milestone_Update"){
                    $uniqe_str = $d['serial_no']."_".$d['insert_date_format']."_".$d['milestone_code'];
                }else if ($notifiation_type_db == "Container_Status_Update"){
                    $uniqe_str = $d['ctnr']."_".$d['insert_date_format']."_".$d['ctnr_status_code'];
                }else if($notifiation_type_db == "Departure/Arrival_Delay"){
                    $uniqe_str = $d['serial_no']."_".$d['insert_date_format']."_".$d['delay_name'];
                }else if($notifiation_type_db == "ETD/ETA_Change"){
                    $uniqe_str = $d['serial_no']."_".$d['insert_date_format']."_".$d['date_change_name'];
                }

                if ($d['frequency_type'] == "Daily"){
                    if(!utils::in_array($uniqe_str,$daily_uniqe)){
                        $daily[$uniqe_str] = $d;
                        $daily_uniqe[] = $uniqe_str;
                    } 
                }
                if ($d['frequency_type'] == "Weekly"){
                    if(!utils::in_array($uniqe_str,$weekly_uniqe)){
                        $weekly[$uniqe_str] = $d;
                        $weekly_uniqe[] = $uniqe_str;
                    }
                }
            }
            $retData[$key]=  array("instant" =>$instant,"daily" =>utils::arrayKeyToInt($daily),"weekly"=>utils::arrayKeyToInt($weekly));
        }
        return $retData;
    }

    public function getEventCard($mInfo){
        $eventCard = array();
        $notifiation_type = $mInfo['notifiation_type'];
        if($notifiation_type == "Milestone_Update"){
            $eventCard = array("type" =>'milestone',
                    "numericRecords"=>0,
                    "isRead"=>!empty($mInfo["is_send_message"]) ? true : false,
                    "title"=>"Milestone Update",
                    "mode"=>$mInfo["transport_mode"] == 'sea' ? "Ocean Freight": "Air Freight",
                    "no"=>$mInfo["h_bol"],
                    "tag"=>$mInfo["milestone_description"],
                    "location"=>$mInfo["milestone_locations"],
                    "timezone"=>$mInfo["milestone_timezone"],
                    "time"=>$mInfo["milestone_date"]." ".$mInfo["milestone_time"],
                    "timeLabel"=>"",
                    "previous"=>"",
                    "frequency_type"=>$mInfo["frequency_type"],
                    "serial_no"=>common::deCode($mInfo["serial_no"], 'E'),
                    "order_from"=>$mInfo["order_from"], 
                    "id"=>$mInfo["id"],
                    "insert_date_format"=>'',
                    "rules_type"=>$mInfo["notifiation_type"],
                    "info"=>new stdClass());

            if ($mInfo["frequency_type"] == "Daily"){
                $eventCard["numericRecords"] = !empty($mInfo["numericRecords"]) ? $mInfo["numericRecords"] : 0;
                $eventCard["title"] = "Milestone Update Daily Summary(".$mInfo["insert_date_format"].")";
                $eventCard["insert_date_format"] = $mInfo["insert_date_format"];
                //seeall 的时候，前端不需要id
                $eventCard["id"] = '';

                $milestone_previous = json_decode($mInfo["milestone_previous_json"],true);
                if(!empty($milestone_previous['milestone']['milestone_date'])){
                    $eventCard["previous"] = array("tag" =>"Previous:".$milestone_previous['milestone']['milestone_description']." from ".$milestone_previous['milestone']['locations'],
                        "date" => $milestone_previous['milestone']['milestone_date'],
                        "time" => $milestone_previous['milestone']['milestone_time'],
                        "timezone" =>$milestone_previous['milestone']['timezone']);    
                }
            } else if($mInfo["frequency_type"] == "Weekly"){
                $eventCard["numericRecords"] = !empty($mInfo["numericRecords"]) ? $mInfo["numericRecords"] : 0;
                $eventCard["title"] = "Milestone Update Weekly Summary(".$mInfo["insert_date_format"].")";
                $eventCard["insert_date_format"] = $mInfo["insert_date_format"];
                //seeall 的时候，前端不需要id
                $eventCard["id"] = '';

                $milestone_previous = json_decode($mInfo["milestone_previous_json"],true);
                if(!empty($milestone_previous['milestone']['milestone_date'])){
                    $eventCard["previous"] = array("tag" =>"Previous:".$milestone_previous['milestone']['milestone_description']." from ".$milestone_previous['milestone']['locations'],
                        "date" => $milestone_previous['milestone']['milestone_date'],
                        "time" => $milestone_previous['milestone']['milestone_time'],
                        "timezone" =>$milestone_previous['milestone']['timezone']);    
                }
                
            }
        }
        if($notifiation_type == "Container_Status_Update"){
            //当前状态的描述ctnr_desc
            //$ctnrStatusdesc = $this->getContainerStatusDesc($mInfo["ctnr_status_code"]);
            $eventCard = array("type" =>'container',
                    "numericRecords"=>0,
                    "isRead"=>!empty($mInfo["is_send_message"]) ? true : false,
                    "title"=>"Container_Status_Update",
                    "mode"=>"Ocean Freight",
                    "no"=>$mInfo["ctnr"],
                    "tag"=>$mInfo["ctnr_desc"],
                    "location"=>$mInfo["ctnr_status_locations"],
                    "timezone"=>$mInfo["ctnr_status_timezone"],
                    "time"=>$mInfo["ctnr_status_date"]." ".$mInfo["ctnr_status_time"],
                    "timeLabel"=>"",
                    "previous"=>"",
                    "frequency_type"=>$mInfo["frequency_type"],
                    "serial_no"=>common::deCode($mInfo["serial_no"], 'E'),
                    "order_from"=>$mInfo["order_from"],
                    "id"=>$mInfo["id"],
                    "insert_date_format"=>'',
                    "rules_type"=>$mInfo["notifiation_type"],
                    "info"=>new stdClass());

            if ($mInfo["frequency_type"] == "Daily"){
                $eventCard["numericRecords"] = !empty($mInfo["numericRecords"]) ? $mInfo["numericRecords"] : 0;
                $eventCard["title"] = "Container Status Update Daily Summary(".$mInfo["insert_date_format"].")";
                $eventCard["insert_date_format"] = $mInfo["insert_date_format"];
                //seeall 的时候，前端不需要id
                $eventCard["id"] = '';

                $ctnr_previous = json_decode($mInfo["ctnr_previous_json"],true);
                if(!empty($ctnr_previous["ctnrStatus"]["date"])){
                   //当前状态 前一个的描述
                   $eventCard["previous"] = array("tag" =>"Previous:" .$ctnr_previous["ctnrStatus"]["description"]. " from " .$ctnr_previous["ctnrStatus"]["locations"],
                       "date" => $ctnr_previous["ctnrStatus"]["date"],
                       "time" => $ctnr_previous["ctnrStatus"]["time"],
                       "timezone" =>$ctnr_previous["ctnrStatus"]["timezone"]);  
                }
            } else if($mInfo["frequency_type"] == "Weekly"){
                $eventCard["numericRecords"] = !empty($mInfo["numericRecords"]) ? $mInfo["numericRecords"] : 0;
                $eventCard["title"] = "Container Status Update Weekly Summary(".$mInfo["insert_date_format"].")";
                $eventCard["insert_date_format"] = $mInfo["insert_date_format"];
                //seeall 的时候，前端不需要id
                $eventCard["id"] = '';

                $ctnr_previous = json_decode($mInfo["ctnr_previous_json"],true);
                if(!empty($ctnr_previous["ctnrStatus"]["date"])){
                   //当前状态 前一个的描述
                   $eventCard["previous"] = array("tag" =>"Previous:" .$ctnr_previous["ctnrStatus"]["description"]. " from " .$ctnr_previous["ctnrStatus"]["locations"],
                       "date" => $ctnr_previous["ctnrStatus"]["date"],
                       "time" => $ctnr_previous["ctnrStatus"]["time"],
                       "timezone" =>$ctnr_previous["ctnrStatus"]["timezone"]);  
                }
            }
        }

        if($notifiation_type == "Departure/Arrival_Delay"){
            //代表信息为转船信息，title要处理一下： leg 2/3 Departure_Delay => Departure_Delay
            $title = $mInfo["delay_name"];
            $outsideLocation = "";
            $outsideTimezone = "";
            $outsideTimeLabel= "";
            $insideTimeLabel= "";
            if(utils::checkExist($mInfo["delay_name"],"Departure_Delay")){
                $outsideTimeLabel = "ETD";
                $insideTimeLabel = "ATD";
            }
            if(utils::checkExist($mInfo["delay_name"],"Arrival_Delay")){
                $outsideTimeLabel = "ETA";
                $insideTimeLabel = "ATA";
            }
            //直航的的
            if(utils::checkExist($mInfo["delay_name"],"Departure_Delay") and $mInfo["delay_is_direct"] == 't'){
                $outsideLocation = $mInfo["delay_locations_from"];
            }
            if(utils::checkExist($mInfo["delay_name"],"Arrival_Delay") and $mInfo["delay_is_direct"] == 't'){
                $outsideLocation = $mInfo["delay_locations_to"];
            }

            $route = array();
            $leg = array();
            if($mInfo["delay_is_direct"] <>'t'){
                $title = substr($mInfo["delay_name"],8);
                $route = array($mInfo["delay_locations_from"],$mInfo["delay_locations_transshipment"],$mInfo["delay_locations_to"]);
                //当前current Leg
                $leg = array($mInfo["delay_locations_from"],$mInfo["delay_locations_transshipment"]);
                if($mInfo["delay_current"] == "2"){
                    $leg = array($mInfo["delay_locations_transshipment"],$mInfo["delay_locations_to"]);
                }
            }
            $act_date = $mInfo["delay_act_date"]." ".$mInfo["delay_act_time"];
            $est_date = $mInfo["delay_est_date"]." ".$mInfo["delay_est_time"];
            $delay_diff = $mInfo["_delay_diff"];
            $delay_unit = $mInfo["delay_unit"];
            $eventCard = array("type" =>'delay',
                    "numericRecords"=>0,
                    "isRead"=>!empty($mInfo["is_send_message"]) ? true : false,
                    "title"=>$title,
                    "mode"=>$mInfo["transport_mode"] == 'sea' ? "Ocean Freight": "Air Freight",
                    "no"=>$mInfo["h_bol"],
                    "tag"=>$mInfo["delay_name"],
                    "location"=>$outsideLocation,
                    "timezone"=>$mInfo["delay_timezone"],
                    "time"=>$est_date,
                    "timeLabel"=>$outsideTimeLabel,
                    "previous"=>"",
                    "frequency_type"=>$mInfo["frequency_type"],
                    "serial_no"=>common::deCode($mInfo["serial_no"], 'E'),
                    "order_from"=>$mInfo["order_from"],
                    "id"=>$mInfo["id"],
                    "insert_date_format"=>'',
                    "rules_type"=>$mInfo["notifiation_type"],
                    "info"=>array("route"=>$route,
                        "leg"=>$leg,
                        "etdOrdeparturNum"=>0,
                        "etaOrarrivalNum"=>0,
                        "time"=>$act_date,
                        "timeLabel"=>$insideTimeLabel,
                        "delayTimeTip"=>"+".$delay_diff." ".$delay_unit." delay",
                        "timezone"=>$mInfo["delay_timezone"]
                    ));

            if ($mInfo["frequency_type"] == "Daily"){
                $eventCard["numericRecords"] = !empty($mInfo["numericRecords"]) ? $mInfo["numericRecords"] : 0;
                $eventCard["info"]["etdOrdeparturNum"] = !empty($mInfo["numericRecords_one"]) ? $mInfo["numericRecords_one"] : 0;
                $eventCard["info"]["etaOrarrivalNum"] = !empty($mInfo["numericRecords_two"]) ? $mInfo["numericRecords_two"] : 0;
                $eventCard["title"] = "Container Status Update Daily Summary(".$mInfo["insert_date_format"].")";
                $eventCard["insert_date_format"] = $mInfo["insert_date_format"];
                //seeall 的时候，前端不需要id
                $eventCard["id"] = '';
            } else if($mInfo["frequency_type"] == "Weekly"){
                $eventCard["numericRecords"] = !empty($mInfo["numericRecords"]) ? $mInfo["numericRecords"] : 0;
                $eventCard["info"]["etdOrdeparturNum"] = !empty($mInfo["numericRecords_one"]) ? $mInfo["numericRecords_one"] : 0;
                $eventCard["info"]["etaOrarrivalNum"] = !empty($mInfo["numericRecords_two"]) ? $mInfo["numericRecords_two"] : 0;
                $eventCard["title"] = "Container Status Update Weekly Summary(".$mInfo["insert_date_format"].")";
                $eventCard["insert_date_format"] = $mInfo["insert_date_format"];
                //seeall 的时候，前端不需要id
                $eventCard["id"] = '';
            }
        }
        if($notifiation_type == "ETD/ETA_Change"){
            $title = $mInfo["date_change_name"];
            if(utils::checkExist($mInfo["date_change_name"],"ETD Change")){
                $outsideTimeLabel = "Original ETD";
                $insideTimeLabel = "Updated ETD";
            }
            if(utils::checkExist($mInfo["date_change_name"],"ETA Change")){
                $outsideTimeLabel = "Original ETA";
                $insideTimeLabel = "Updated ETA";
            }
             
            if($mInfo["date_change_is_direct"] <>'t'){
                //代表信息为转船信息，title要处理一下： leg 1/3 ETD Change
                $title = substr($mInfo["date_change_name"],8);
                $route = array($mInfo["date_change_locations_from"],$mInfo["date_change_locations_transshipment"],$mInfo["date_change_locations_to"]);
                $leg = array($mInfo["date_change_locations_from"],$mInfo["date_change_locations_transshipment"]);
                if($mInfo["date_change_current"] == "2"){
                    $leg = array($mInfo["date_change_locations_transshipment"],$mInfo["date_change_locations_to"]);
                }
            }
            $updated_date = $mInfo["date_change_updated_date"]." ".$mInfo["date_change_updated_time"];
            $original_date = $mInfo["date_change_original_date"]." ".$mInfo["date_change_original_time"];
            $eventCard = array("type" =>'change',
                    "numericRecords"=>0,
                    "isRead"=>!empty($mInfo["is_send_message"]) ? true : false,
                    "title"=>$title,
                    "mode"=>$mInfo["transport_mode"] == 'sea' ? "Ocean Freight": "Air Freight",
                    "no"=>$mInfo["h_bol"],
                    "tag"=>$mInfo["date_change_name"],
                    "location"=>"",
                    "timezone"=>$mInfo["date_change_timezone"],
                    "time"=>$original_date,
                    "timeLabel"=>$outsideTimeLabel,
                    "previous"=>"",
                    "frequency_type"=>$mInfo["frequency_type"],
                    "serial_no"=>common::deCode($mInfo["serial_no"], 'E'),
                    "order_from"=>$mInfo["order_from"],
                    "id"=>$mInfo["id"],
                    "insert_date_format"=>'',
                    "rules_type"=>$mInfo["notifiation_type"],
                    "info"=>array("route"=>$route,
                        "leg"=>$leg,
                        "etdOrdeparturNum"=>0,
                        "etaOrarrivalNum"=>0,
                        "time"=>$updated_date,
                        "timeLabel"=>$insideTimeLabel,
                        "delayTimeTip"=>"",
                        "timezone"=>$mInfo["date_change_timezone"]
                    ));

            if ($mInfo["frequency_type"] == "Daily"){
                $eventCard["numericRecords"] = !empty($mInfo["numericRecords"]) ? $mInfo["numericRecords"] : 0;
                $eventCard["info"]["etdOrdeparturNum"] = !empty($mInfo["numericRecords_one"]) ? $mInfo["numericRecords_one"] : 0;
                $eventCard["info"]["etaOrarrivalNum"] = !empty($mInfo["numericRecords_two"]) ? $mInfo["numericRecords_two"] : 0;
                $eventCard["title"] = "ETD/ETA Change Daily Summary(".$mInfo["insert_date_format"].")";
                $eventCard["insert_date_format"] = $mInfo["insert_date_format"];
                $eventCard["id"] = "";
            } else if($mInfo["frequency_type"] == "Weekly"){
                $eventCard["numericRecords"] = !empty($mInfo["numericRecords"]) ? $mInfo["numericRecords"] : 0;
                $eventCard["info"]["etdOrdeparturNum"] = !empty($mInfo["numericRecords_one"]) ? $mInfo["numericRecords_one"] : 0;
                $eventCard["info"]["etaOrarrivalNum"] = !empty($mInfo["numericRecords_two"]) ? $mInfo["numericRecords_two"] : 0;
                $eventCard["title"] = "ETD/ETA Change Weekly Summary(".$mInfo["insert_date_format"].")";
                $eventCard["insert_date_format"] = $mInfo["insert_date_format"];
                $eventCard["id"] = "";
            }
        }
        if($notifiation_type == "Feature_Update" || $notifiation_type == "Passwond_Notifcations"){
            $title = $mInfo["other_type"] == "password" ? "Password Notifications" : "Feature Update";
            $isExpiration = false;
            if($title == "Password Notifications" and $mInfo["other_pnum"] <= 3){
                $isExpiration = true;
            }
            $eventCard = array(
                "title" => $title,
                "id"=> $mInfo["id"],
                "header"=> $mInfo["other_name"],
                "content"=>$mInfo["other_desc"],
                "isRead"=>!empty($mInfo["is_send_message"]) ? true : false,
                "isExpiration"=> $isExpiration,
                "rules_type"=>$mInfo["notifiation_type"],
                "imgSrc"=>SERVER_PAHT.FILE_UPLOAD_PAHT."feature_update_".$mInfo["id"].".jpg",
                "view_more_link" =>"main_new_version.php?action=feature_update&id=".$mInfo["id"]
            );
        }
        return $eventCard;
    }



    /**
     * 返回当前柜子的status信息描述,目前作废
     */
    public static function getContainerStatusDesc($ctnr_status_code){
        $event =common::getEDICtnrEvent();
        $ctnrStatusdesc = "";
        foreach($event as $e){
            if($e['event_name'] == $ctnr_status_code){
                $ctnrStatusdesc = $e['description'];
            }
        }
        return $ctnrStatusdesc;
    }
}

?>