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 ra_online_user u
                    left join  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"){
                $user_name = common::check_input($_POST['user_name']);
                $email = common::check_input($_POST['email']);
                $sql = "update public.ra_online_user set user_login = '$user_name',email = '$email'  where lower(user_login) = '".strtolower(_getLoginName())."'";
            }else{
                $date_fromat = common::check_input($_POST['date_fromat']);
                $numbers_format = common::check_input($_POST['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_fromat = '$date_fromat',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_fromat', '$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";
            $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())."'");
            $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_search") {
            $cp = common::check_input($_POST ['cp']); //current_page
            $ps = common::check_input($_POST ['ps']); //ps
            if (empty($ps))
                $ps = 50;

            $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 count(1) 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::excuteObjectSql($sql);
                $arrTmp = array('monitoringRules' => $monitoringRules, 
                    'rc' => intval($rc),
                    'ps' => $ps,
                    'cp' => $cp,
                    'tp' => $tp
                );
            } else {
                $arrTmp = array('searchData' => array(), 
                    'rc' => $rc,
                    'ps' => $ps,
                    'cp' => $cp,
                    'tp' => $tp,
                );
            }
            common::echo_json_encode(200,$arrTmp);
            exit();
        }
        if ($operate == "monitoring_rules_do") {
            $rules_type = common::check_input($_POST["rules_type"]);
            //检查编辑提交的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']);
            $rules_type = common::check_input($_POST['rules_type']);
            $sql = "delete from notifications_rules where notifications_type = 'Monitoring' 
                and  rules_type = '$rules_type' 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"){
            $rules_type = common::check_input($_POST ['rules_type']); 
            if ($rules_type == "all"){
                $sql = "select *,
                        (select o.h_bol from public.kln_ocean o where o.serial_no = ni.serial_no limit 1) 
                    from public.kln_notifiation_info ni
                    where lower(ni.user_login) = '"._getLoginName()."' 
                        and (ni.frequency_type = 'Instant' 
                            or (ni.frequency_type = 'Daily'  and timezone(ni.daily_time_zone, NOW()::time) > ni.daily_time::time)
                            or (ni.frequency_type = 'Weekly' and timezone(ni.weekly_time_zone, NOW()::time) > ni.weekly_time::time 
                                and  ni.weekly_week ilike '%'|| EXTRACT(dow FROM timezone(ni.weekly_time_zone, NOW())) ||'%')) 
                        and ni.notifications_method = true and ni.is_send_message = false order by ni.insert_date desc";   
            }else{
                $sql = "select case when COALESCE(ni.milestone_code,'') <> ''  and ni.milestone_transport_mode = 'sea'
                                    then  (select description from  public.customer_service_milestone_sno where type = 'sea' and code = ni.milestone_code)
                                else (select description from  public.customer_service_milestone_sno where type = 'air' and code = ni.milestone_code) end as milestone_desc,
                        ni.milestone_code,*
                    (select o.h_bol from public.kln_ocean o where o.serial_no = ni.serial_no limit 1)
                from public.kln_notifiation_info ni
                    where lower(ni.user_login) = '"._getLoginName()."' 
                        and ni.rules_type = '".$rules_type."'
                        and (ni.frequency_type = 'Instant' 
                            or (ni.frequency_type = 'Daily'  and timezone(ni.daily_time_zone, NOW()::time) > ni.daily_time::time)
                            or (ni.frequency_type = 'Weekly' and timezone(ni.weekly_time_zone, NOW()::time) > ni.weekly_time::time 
                                and  ni.weekly_week ilike '%'|| EXTRACT(dow FROM timezone(ni.weekly_time_zone, NOW())) ||'%')) 
                        and ni.notifications_method = true and ni.is_send_message = false order by ni.insert_date desc";
            }
            $notifications = common::excuteListSql($sql);

            $all_rules = array("Milestone_Update","Container_Status_Update","Departure/Arrival_Delay","ETD/ETA_Change");
            
            foreach($notifications as $notification){
                if($notification["notifiation_type"] == "Milestone_Update"){
                    if ($notification["frequency_type"] == "Instant"){
                        $milestoneInstantMessage[] = array("mode"=>$notification["milestone_transport_mode"],"HBOL"=>$notification["milestone_transport_mode"],
                            "HBOL"=>$notification["milestone_transport_mode"]);
                    }

                }
            }

        }
    }

    /**
     * 遍历查找对应的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"]);
                $ret = $rules;
            }
        }
        //Milestone Update的结构处理,处理init page load
        if($rule_name == "Milestone_Update"){
            //Milestone Update的页面配置数据
            $milestones = common::excuteListSql("select * from 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['description'],
                    "code"=>$milestone['code'],"description"=>$milestone['description']);
                } 
                if($milestone['type'] == "ocean"){
                    $oceanMilestone[] = array("label"=>$milestone['description'],"value"=>$milestone['description'],
                    "code"=>$milestone['code'],"description"=>$milestone['description']);
                }
            }
            $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();
            //转换描述，因为前端支持description，没有code的对应
            $oceanMilestoneDescription = $this->convertDescriptionForMilestoneAndCtnr("Milestone_Update",$oceanMilestoneSetting,$oceanMilestone);
            //转换描述，因为前端支持description，没有code的对应
            $airMilestoneDescription = $this->convertDescriptionForMilestoneAndCtnr("Milestone_Update",$airMilestoneSetting,$airMilestone);
            $ret["OceanCheckedList"] = $oceanMilestoneDescription;
            $ret["AirCheckedList"] = $airMilestoneDescription;
        }
        //Milestone Update的结构处理
        if($rule_name == "Container_Status_Update"){
            //这里基准event 写死, 根据online查询页面的通用的来, 这里需提问确定
            $event =$this->getEDICtnrEvent();
            $ctnrStatus = array();
            foreach($event as $e){
                $ctnrStatus[] = array("label"=>$e['description'],"value"=>$e['description']);
            }
            $ret["CtnrCheckBoxList"] = $ctnrStatus;
           
            $ctnrStatusSetting = !empty($ret['ocean_ctnr_status']) ? explode(";",$ret['ocean_ctnr_status']) : array();
            //转换描述，因为前端支持description，没有code的对应
            $ctnrStatusDescription = $this->convertDescriptionForMilestoneAndCtnr("Container_Status_Update",$ctnrStatusSetting,$event);
            $ret["CtnrCheckedList"] = $ctnrStatusDescription;
            
        }
        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.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);
            $arrTmp = array('tableData' => $subscribeShipment, 
                'rc' => intval($rc),
                'ps' => $ps,
                'cp' => $cp,
                'tp' => $tp
            );
        } else {
            $arrTmp = array('tableData' => array(), 
                'rc' => $rc,
                'ps' => $ps,
                'cp' => $cp,
                'tp' => $tp,
            );
        }
        return $arrTmp;
    }

    /**
     * 转换描述，因为前端支持description，没有code的对应
     */
    public function convertDescriptionForMilestoneAndCtnr($type,$codeArr,$mappingData){
        $descriptions = array();
        $key = $type == "Milestone_Update" ? "code" : "event_name";
        foreach($codeArr as $code){
            foreach($mappingData as $md){
                if($md[$key] == $code){
                    $descriptions[] = common::check_input($md['description']);
                }
            }
        }
        return $descriptions;
    }

    /**
     * 转换code，因为前端提交的是description，没有code的
     */
    public function convertCodeForMilestoneAndCtnr($type,$descriptionArr,$mappingData){
        $codes = array();
        $key = $type == "Milestone_Update" ? "code" : "event_name";
        foreach($descriptionArr as $description){
            foreach($mappingData as $md){
                if($md['description'] == $description){
                    $codes[] = common::check_input($md[$key]);
                }
            }
        }
        return $codes;
    }

    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 = common::check_input($_POST['method_by_email']);
        $method_by_message = common::check_input($_POST['method_by_message']);
        $event_details = common::check_input($_POST['event_details']);
        $frequency_display = common::check_input($_POST['frequency_display']);
        $method_display = common::check_input($_POST['method_display']);

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

        if ($rules_type == "Milestone_Update"){
            //提交的description 的转换code
            $milestones = common::excuteListSql("select * from customer_service_milestone_sno order by type, sno");
            $oceanMilestone = array();
            $airMilestone = array();
            foreach($milestones as $milestone){
                if($milestone['type'] == "air"){
                    $airMilestone[] = $milestone;
                } 
                if($milestone['type'] == "ocean"){
                    $oceanMilestone[] = $milestone;
                }
            }
            $ocean_milestone = $this->convertCodeForMilestoneAndCtnr("Milestone_Update",$_POST['ocean_milestone'],$oceanMilestone);
            $air_milestone = $this->convertCodeForMilestoneAndCtnr("Milestone_Update",$_POST['air_milestone'],$airMilestone);
            $ocean_milestone = utils::implode(";",$ocean_milestone);
            $air_milestone =  utils::implode(";",$air_milestone);

            $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_transport_mode,shipment_etd_limit,shipment_eta_limit)
            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_transport_mode','$shipment_etd_limit','$shipment_eta_limit');";
        }

        if ($rules_type == "Container_Status_Update"){
            $event = $this->getEDICtnrEvent();
            $ocean_ctnr_status = $this->convertCodeForMilestoneAndCtnr("Container_Status_Update",$_POST['ocean_ctnr_status'],$event);
            $ocean_ctnr_status = utils::implode(";",$ocean_ctnr_status);

            $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_transport_mode,shipment_etd_limit,shipment_eta_limit)
            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_transport_mode','$shipment_etd_limit','$shipment_eta_limit');";
        }

        if ($rules_type == "Departure/Arrival_Delay"){
            $ocean_atd_sub_etd = $_POST['ocean_atd_sub_etd'];
            $ocean_atd_sub_etd_unit = $_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 = $_POST['ocean_ata_sub_eta'];
            $ocean_ata_sub_eta_unit = $_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 = $_POST['air_atd_sub_etd'];
            $air_atd_sub_etd_unit = $_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 = $_POST['air_ata_sub_eta'];
            $air_ata_sub_eta_unit = $_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";
            }
            
            $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_transport_mode,shipment_etd_limit,shipment_eta_limit)
            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_transport_mode','$shipment_etd_limit','$shipment_eta_limit');";
        }

        if ($rules_type == "ETD/ETA_Change"){
            $ocean_etd_change = $_POST['ocean_etd_change'];
            $ocean_etd_old_sub_new = $_POST['ocean_etd_old_sub_new'];
            $ocean_etd_old_sub_new_unit = $_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 = $_POST['ocean_eta_change'];
            $ocean_eta_old_sub_new = $_POST['ocean_eta_old_sub_new'];
            $ocean_eta_old_sub_new_unit = $_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 = $_POST['air_etd_change'];
            $air_etd_old_sub_new = $_POST['air_etd_old_sub_new'];
            $air_etd_old_sub_new_unit = $_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 = $_POST['air_eta_change'];
            $air_eta_old_sub_new = $_POST['air_eta_old_sub_new'];
            $air_eta_old_sub_new_unit = $_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";
            }
            
            $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_transport_mode,shipment_etd_limit,shipment_eta_limit)
            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_transport_mode','$shipment_etd_limit','$shipment_eta_limit');";
        }
        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::excuteOneSql("select * 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");
            $range_flag = true;   
            foreach($checkRangeFiled as $filed){
                if($filed == "shipment_transport_mode"){
                    $postValue = utils::implode(";",$_POST[$filed]);
                }else{
                    $postValue = !empty($_POST[$filed]) ? $_POST[$filed] : "";
                }
                if($postValue != $rule[$filed]){
                    $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"){
                    $postValue = utils::implode(";",$_POST[$filed]);
                }else{
                    $postValue = !empty($_POST[$filed]) ? $_POST[$filed] : "";
                }
                if($postValue != $rule[$filed]){
                    $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){
                $postValue = !empty($_POST[$filed]) ? $_POST[$filed] : "";
                if($postValue != $rule[$filed]){
                    $frequency_flag = false;
                }
            }
            
            //判断通知方式是否一样
            $checkMethodFiled = array("method_by_email","method_by_message");
            $method_flag = true; 
            foreach($checkMethodFiled as $filed){
                $postValue = !empty($_POST[$filed]) ? $_POST[$filed] : "";
                if($postValue != $rule[$filed]){
                    $method_flag = false;
                }
            }

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

    /**
     * 这里基准event 写死, 根据online查询页面的通用的来, 这里需提问确定
     */
    public function getEDICtnrEvent(){
        $event =array(array("event_name"=>"EE","description"=>"Empty Equipment Dispatched"),array("event_name"=>"I","description"=>"Gate in full for a booking"),array("event_name"=>"AE","description"=>"Container loaded on vessel"),
                array("event_name"=>"VD","description"=>"Vessel Departure"),array("event_name"=>"VA_RELAY","description"=>"Arrive Relay Port"),array("event_name"=>"UV_RELAY","description"=>"Unloaded at Relay Port"),
                array("event_name"=>"AE_RELAY","description"=>"Loaded at Relay Port"),array("event_name"=>"VD_RELAY","description"=>"Depart Relay Port"),array("event_name"=>"CU","description"=>"Carrier and Customs Release"),
                array("event_name"=>"CT","description"=>"Customs release"),array("event_name"=>"CR","description"=>"Carrier release"),array("event_name"=>"VA","description"=>"Vessel Arrival"),
                array("event_name"=>"UV","description"=>"Unloaded From Vessel"),array("event_name"=>"AG","description"=>"Estimated Delivery"),array("event_name"=>"OA","description"=>"Gate out full from final discharge port"),
                array("event_name"=>"FT","description"=>"Free Time Expired"),array("event_name"=>"AL","description"=>"Container loaded on Rail"),array("event_name"=>"AR","description"=>"Container unloaded from Rail"),
                array("event_name"=>"AV","description"=>"Shipment available for pickup or delivery"),array("event_name"=>"D","description"=>"Gate out for delivery to customer"),array("event_name"=>"RD","description"=>"Container returned empty"),
                array("event_name"=>"C","description"=>"Vessel Estimated Time of Departure"),array("event_name"=>"C_RELAY","description"=>"Estimated Time of Departure from Tranship Port"),array("event_name"=>"AG_DES","description"=>"Estimated Delivery Destination"),
                array("event_name"=>"IFFADW","description"=>"Shipment in CFS warehouse"),array("event_name"=>"IFFDDW","description"=>"Shipment Out from CFS House"));
        return  $event;   
    }


    public function getNotifications($rules_type){

        //$all_rules = array("Milestone_Update","Container_Status_Update","Departure/Arrival_Delay","ETD/ETA_Change");
        if($rules_type == "Milestone_Update"){
            $sql = "select case when COALESCE(ni.milestone_code,'') <> '' and ni.milestone_transport_mode = 'sea'
                                then  (select description from  public.customer_service_milestone_sno where type = 'sea' and code = ni.milestone_code)
                            else (select description from  public.customer_service_milestone_sno where type = 'air' and code = ni.milestone_code)
                            end as milestone_desc,
                        case when ni.milestone_code in ('IFFBCF','IFFBCR','IFFSIL','IFFEDO','IFFECC') 
                                then '' 
                            when ni.milestone_code in ('IFFREC','IFFECP')
                                then ''
                        ni.milestone_date,ni.milestone_time,
                        (select o.h_bol from public.kln_ocean o where o.serial_no = ni.serial_no limit 1)
                    from public.kln_notifiation_info ni
                        where lower(ni.user_login) = '"._getLoginName()."' 
                        and ni.rules_type = '".$rules_type."'
                        and (ni.frequency_type = 'Instant' 
                            or (ni.frequency_type = 'Daily'  and timezone(ni.daily_time_zone, NOW()::time) > ni.daily_time::time)
                            or (ni.frequency_type = 'Weekly' and timezone(ni.weekly_time_zone, NOW()::time) > ni.weekly_time::time 
                                    and  ni.weekly_week ilike '%'|| EXTRACT(dow FROM timezone(ni.weekly_time_zone, NOW())) ||'%')) 
                        and ni.notifications_method = true and ni.is_send_message = false order by ni.insert_date desc";
        }

    }
}

?>