/**
 *
 */
package com.foc.plan.push.service;

import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang3.StringUtils;

import com.foc.plan.push.entity.FlightListEntity;
import com.foc.plan.push.entity.OdsUserListEntity;
import com.foc.plan.push.entity.PlanPushBaseListEntity;
import com.foc.push.service.PushService;
import com.foc.util.DBHelper;
import com.foc.util.DateUtil;

/**
 * 
 * @team BBDTEK IT Team
 * @author renmb
 * @version 1.0
 * @time  2016年12月17日
 */
public class PlanPushService {
    
    private QueryRunner run = null;
    private PushService pushService = new PushService();
    
    public PlanPushService() {
        try {
            this.run = new QueryRunner(DBHelper.getDataSource());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    public void finish(final String flightDate, final String modified) {
        
        
    }
    public boolean handle(final String flightDate, final String modified){
        long startTime = System.nanoTime();
        planTimePush();
        long endTime  = System.nanoTime();
        long duration = endTime - startTime;
        System.out.println("【航班计划确认签到】处理时间：" + DateUtil.getDurationTime(duration));
        
        startTime = System.nanoTime();
        odsUserPush();
        endTime  = System.nanoTime();
        duration = endTime - startTime;
        System.out.println("【准备会前确认签到】处理时间：" + DateUtil.getDurationTime(duration));
        
        return true;
    }
    /**
     * 准备会前确认签到
     * 推送：
     * 航班计划确认签到推送   ：航班计划确认签到结束时间－后台设定的航班计划确认签到提醒时间
     * 如用户已经进行航班计划确认签到则不需要进行推送提醒。
     * 例：今天此用户有三个航班的飞行任务、一个乘务长、只需要签一次航班计划确认签到。（以第一个航班为计算依据）同时需要把剩下今天所剩下的飞行任务航班全部在此推送中体现。
     *     今天此用户所有飞行任务有两个乘务长、则需要签两次航班计划确认签到。（分别以此乘务长的第一个航班为计算依据）同时需要把今天所剩下的飞行任务航班全部在此推送中体现。
     * 解释：今日所有航班飞行任务如果只有一个乘务长的情况则只需要进行一次航班计划确认签到，如果今日所有航班飞行任务有两个乘务长则航班计划确认签到则需要分别给两个乘务长进行签到。
     * 内容例子：文案：2016-12-03 JD7458、JD8473 向白百何乘务长航班计划确认签到还有60分钟截止，别忘记签到哦！

     * 签到b推送时间   ：签到b开始时间－后台设定的航班准备会前40分钟提醒时间
     * 如果用户已经进行准备会确认签到则不需要进行推送提醒。
     * 注释：
     * 签到a＝准备会签到
     * 签到b＝准备会确认签到
     * 
     * @author renmb
     * @time  2016年12月18日
     */
    public void odsUserPush(){
        
        Map<String, PlanPushBaseListEntity> map = this.getBaseMap();
        List<OdsUserListEntity> odsUserList = this.getOdsUserList();
        if(odsUserList != null && odsUserList.size()>0){// 向机组排班用户推送数据
            
            for(OdsUserListEntity user:odsUserList){// 循环遍历机组成员
                try {
                    PlanPushBaseListEntity base = map.get(user.getDepStn());// 获取基地设置信息
                    if(base != null){
                        long timeMillis = 0;//System.currentTimeMillis()+base.getReadyRemindHour()*60*60*1000+base.getReadyRemindMinute()*60*1000;
                        long startTime = 0;
                        if(base.getReadyHour() == 0 && base.getReadyMinute() == 0){// 后台没有设置准备会时间
                            if(user.getSignUpStartTime() != null){// 没有默认签到时间
                                timeMillis = System.currentTimeMillis()+base.getReadyRemindHour()*60*60*1000+base.getReadyRemindMinute()*60*1000;
                                startTime = user.getSignUpStartTime().getTime();
                            }
                        } else{
                            if(user.getStdChn() != null){
                                
                                timeMillis = System.currentTimeMillis()+base.getReadyHour()*60*60*1000+base.getReadyMinute()*60*1000
                                        +base.getReadyRemindHour()*60*60*1000+base.getReadyRemindMinute()*60*1000;;
                                startTime = user.getStdChn().getTime();// 航班计划起飞时间
                            }
                        }
                        if(startTime > 0 
                             && startTime<=timeMillis
                               && "2".equals(user.getCrewTypeId()) 
                                 && user.getPrepareBefore40SignTime() == null){// 推送给乘务组的所有用户
                            
//                        Date startTime = user.getSignUpStartTime() != null ? user.getSignUpStartTime() : user.getStdChn();
//                        if(user.getSignUpStartTime() != null 
//                              && user.getSignUpStartTime().getTime()<=timeMillis
//                                  && "2".equals(user.getCrewTypeId()) 
//                                      && user.getPrepareBefore40SignTime() == null
//                                        && user.getFlightPlanConfirmSignTime() == null){// 推送给乘务组的所有用户
                        
//                        if(user.getStdChn() != null 
//                                && user.getStdChn().getTime()<=timeMillis
//                                    && "2".equals(user.getCrewTypeId()) 
//                                        && user.getPrepareBefore40SignTime() == null
//                                          && user.getFlightPlanConfirmSignTime() == null){// 推送给乘务组的所有用户
                            
                            this.updateOdsPushStatus(user.getOdsId(), "YES");
                            String flightDate = DateUtil.dateToStr(user.getFlightDate(), DateUtil.DATE_FORMAT);
                            Map<String, String> extras = new HashMap<String, String>();
                            extras.put("flightDate", flightDate);
                            extras.put("flightNo",   user.getFlightNo());
                            extras.put("depStn",     user.getDepStn());
                            extras.put("arrStn",     user.getArrStn());
                            
                            String title = "";
                            if(base.getReadyRemindHour()>0){
//                                String hour = base.getReadyRemindHour() > 9 ? String.valueOf(base.getReadyRemindHour()):"0"+base.getReadyRemindHour();
//                                String minute = base.getReadyRemindMinute() > 9 ? String.valueOf(base.getReadyRemindMinute()):"0"+base.getReadyRemindMinute();
                                String hour = String.format("%02d", base.getReadyRemindHour());
                                String minute = String.format("%02d", base.getReadyRemindMinute());
                                title = flightDate+" "+user.getFlightNo()+" 准备会前确认签到还有"+hour+"小时"+minute+"分钟截止打卡，别忘记打卡哦。 ";
                            } else{
                                
//                                String minute = base.getReadyRemindMinute() > 9 ? String.valueOf(base.getReadyRemindMinute()):"0"+base.getReadyRemindMinute();
                                String minute = String.format("%02d", base.getReadyRemindMinute());
                                title = flightDate+" "+user.getFlightNo()+" 准备会前确认签到还有"+minute+"分钟截止打卡，别忘记打卡哦。 ";
                            }
                            System.out.println("TITLE:"+title);
                            pushService.sendPush(user.getStaffId(), "MS1002", title, extras);
                        }
                    }
                } catch (Exception e) {
                    // TODO: handle exception
                    e.printStackTrace();
                }
            }
        }
    }
    /**
     * 航班计划确认签到
     * 
     * @author renmb
     * @time  2016年12月18日
     */
    public void planTimePush(){
        
        Map<String, PlanPushBaseListEntity> map = this.getBaseMap();
        List<FlightListEntity> flightList = this.getFlightList();
        if(flightList != null){
            
            for (FlightListEntity flight:flightList) {
                
                PlanPushBaseListEntity base = map.get(flight.getDepStn());// 获取基地设置信息
                if(base != null){
                    long timeMillis = System.currentTimeMillis()+base.getPlanRemindHour()*60*60*1000+base.getPlanRemindMinute()*60*1000;
                    if(flight.getStdChn() != null && flight.getStdChn().getTime()<=timeMillis){
                        
                        List<OdsUserListEntity> odsUserList = this.getOdsUserList(flight.getFlightDate(), flight.getFlightNo(), 
                                flight.getDepStn(), flight.getArrStn());
                        
                        if(odsUserList != null && odsUserList.size()>0){// 向机组排班用户推送数据
                            
                            this.updateFlightStdPushStatus(flight.getFlightDate(), flight.getFlightNo(), 
                                    flight.getDepStn(), flight.getArrStn(), "YES");
                            
                            Map<String, String> extras = new HashMap<String, String>();
                            extras.put("flightDate", DateUtil.dateToStr(flight.getFlightDate(), DateUtil.DATE_FORMAT));
                            extras.put("flightNo",   flight.getFlightNo());
                            extras.put("depStn",     flight.getDepStn());
                            extras.put("arrStn",     flight.getArrStn());
                            extras.put("stdChn",     DateUtil.dateToStr(flight.getStdChn(), DateUtil.TIMEF_FORMAT));
                            
                            String chiefName = null;
                            for(OdsUserListEntity user:odsUserList){// 循环遍历机组成员
                                if("乘务长".equals(user.getRankSd())){
                                    
                                    chiefName = user.getStaffName();
                                    break;// 乘务长不推送
                                }
                            }
                            if(StringUtils.isNotEmpty(chiefName)){
                                String title = "";
//                                long minute = (timeMillis - flight.getStdChn().getTime())/60000;
                                if(base.getPlanRemindHour()>0){
//                                    String hour = base.getPlanRemindHour() > 9 ? String.valueOf(base.getPlanRemindHour()):"0"+base.getPlanRemindHour();
//                                    String minute = base.getPlanRemindMinute() > 9 ? String.valueOf(base.getPlanRemindMinute()):"0"+base.getPlanRemindMinute();
                                    
                                    String hour = String.format("%02d", base.getPlanRemindHour());
                                    String minute = String.format("%02d", base.getPlanRemindMinute());
                                    title = flight.getDepStn()+" "+" "+flight.getFlightNo()+" 向"+chiefName+"乘务长的航班计划确认签到还有"+hour+"小时"+minute+"分钟截止打卡，别忘记打卡哦。 ";
                                } else{
                                    
//                                    String minute = base.getPlanRemindMinute() > 9 ? String.valueOf(base.getPlanRemindMinute()):"0"+base.getPlanRemindMinute();
                                    String minute = String.format("%02d", base.getPlanRemindMinute());
                                    title = flight.getDepStn()+" "+" "+flight.getFlightNo()+" 向"+chiefName+"乘务长的航班计划确认签到还有"+minute+"分钟截止打卡，别忘记打卡哦。 ";
                                }
                                System.out.println("乘务长:"+chiefName);
                                System.out.println("TITLE:"+title);
                                if(StringUtils.isNotEmpty(chiefName)){
                                    for(OdsUserListEntity user:odsUserList){// 循环遍历机组成员
                                        if("2".equals(user.getCrewTypeId()) 
                                                && "乘务长".equals(user.getRankSd()) == false
                                                    && user.getFlightPlanConfirmSignTime() == null){// 推送给乘务组非乘务长的用户
                                            
                                            pushService.sendPush(user.getStaffId(), "MS1001", title, extras);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
    public Map<String, PlanPushBaseListEntity> getBaseMap(){
        
        Map<String, PlanPushBaseListEntity> map = new HashMap<String, PlanPushBaseListEntity>();
        List<PlanPushBaseListEntity> list = this.getBaseList();
        if(list != null){
            
            for (PlanPushBaseListEntity plan:list) {
                
                map.put(plan.getAirportIata(), plan);
            }
        }
        return map;
    }
    public List<PlanPushBaseListEntity> getBaseList(){
        ResultSetHandler<List<PlanPushBaseListEntity>> handler = new BeanListHandler<PlanPushBaseListEntity>(
                PlanPushBaseListEntity.class);
        List<PlanPushBaseListEntity> list = null;
        try {
            StringBuilder builder = new StringBuilder();
            builder.append("SELECT c.airport_iata AS airportIata, ");
            builder.append("       c.airport_name AS airportName, ");
            builder.append("       a.id AS baseId, ");
            builder.append("       a.name AS baseName, ");
            builder.append("       IFNULL(a.plan_sign_up_hour, 0) AS planSignUpHour, ");
            builder.append("       IFNULL(a.plan_sign_up_minute, 0) AS planSignUpMinute, ");
            builder.append("       IFNULL(a.plan_remind_hour, 0) AS planRemindHour, ");
            builder.append("       IFNULL(a.plan_remind_minute, 0) AS planRemindMinute, ");
            builder.append("       IFNULL(a.ready_sign_up_hour, 0) AS readySignUpHour, ");
            builder.append("       IFNULL(a.ready_sign_up_minute, 0) AS readySignUpMinute, ");
            builder.append("       IFNULL(a.ready_remind_hour, 0) AS readyRemindHour, ");
            builder.append("       IFNULL(a.ready_remind_minute, 0) AS readyRemindMinute, ");
            builder.append("       IFNULL(a.plan_sign_up_end_hour, 0) AS planSignUpEndHour, ");
            builder.append("       IFNULL(a.plan_sign_up_end_minute, 0) AS planSignUpEndMinute, ");
            
            builder.append("       IFNULL(a.ready_hour, 0) AS readyHour, ");
            builder.append("       IFNULL(a.ready_minute, 0) AS readyMinute ");
            
            builder.append("FROM foc_bases a ");
            builder.append("INNER JOIN foc_airport2base b ");
            builder.append("  ON a.id=b.base_id ");
            builder.append("INNER JOIN foc_airports c ");
            builder.append("  ON b.airport_id=c.id ");
            builder.append("WHERE a.`status`='1' ");
//            System.out.println(builder);
            list = run.query(builder.toString(), handler);
            if (list != null && list.size() > 0) {
//                odsChangesEntityMap.put(MapType.odsChangesEntityType, list.get(0));
                return list;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 修改航班“航班计划确认签到”推送状态
     * 
     * @author renmb
     * @time  2016年12月18日
     * @param flightDate
     * @param flightNo
     * @param depStn
     * @param arrStn
     * @param planPushed
     * @return
     */
    public boolean updateFlightStdPushStatus(Date flightDate, String flightNo, String depStn, String arrStn, String planPushed){
        
        try {
            if(StringUtils.isNotEmpty(flightNo)){
                String sql = "UPDATE `foc_flight_dynamics` SET plan_pushed=? WHERE datop_chn=? AND flight_no=? AND dep_stn=? AND arr_stn=?";
                System.out.println(sql+"["+planPushed+","+flightDate+","+flightNo+","+depStn+","+arrStn+"]");
                int count = run.update(sql, planPushed, flightDate, flightNo, depStn, arrStn);
                if(count>0)
                    return true;
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return false;
    }
    
    
    public boolean updateOdsPushStatus(String odsId, String planPushed){
        
        try {
            if(StringUtils.isNotEmpty(odsId)){
                String sql = "UPDATE `foc_ods` SET ods_pushed=? WHERE id=?";
                System.out.println(sql+"["+planPushed+","+odsId+"]");
                int count = run.update(sql, planPushed, odsId);
                if(count>0)
                    return true;
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return false;
    }
    /**
     * 获取航班列表
     * 
     * @author renmb
     * @time  2016年12月18日
     * @return
     */
    public List<FlightListEntity> getFlightList(){
        ResultSetHandler<List<FlightListEntity>> handler = new BeanListHandler<FlightListEntity>(
                FlightListEntity.class);
        
        
        List<FlightListEntity> list = null;
        try {
            Date d = new Date();
//            String current = DateUtil.getDateTime(d);
            String dateFrom = DateUtil.dateToStr(DateUtil.offsetDate(d, 0), DateUtil.DATE_FORMAT);
            String dateTo = DateUtil.dateToStr(DateUtil.offsetDate(d, 1), DateUtil.DATE_FORMAT);
            
            StringBuilder builder = new StringBuilder();
            builder.append("SELECT `datop` AS utcFlightDate, ");
            builder.append("       `datop_chn` AS flightDate, ");
            builder.append("       `flight_no` AS flightNo, ");
            builder.append("       `dep_stn` AS depStn, ");
            builder.append("       `arr_stn` AS arrStn, ");
            builder.append("       `status` AS flightStatus, ");
            builder.append("       `std_chn` AS stdChn, ");
            builder.append("       `plan_pushed` AS planPushed ");
            builder.append("FROM `foc_flight_dynamics` ");
            builder.append("WHERE `datop_chn` BETWEEN ? AND ? ");
//            builder.append("  AND `std_chn`>=?");
            builder.append("  AND `plan_pushed`=?");
//            System.out.println(builder.toString()+"["+dateFrom+","+dateTo+","+current+",NO]");
            list = run.query(builder.toString(), handler, dateFrom, dateTo, "NO");
            if (list != null && list.size() > 0) {
                return list;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 获取排班数据
     * 
     * @author renmb
     * @time  2016年12月18日
     * @param flightDate
     * @param flightNo
     * @param depStn
     * @param arrStn
     * @return
     */
    public List<OdsUserListEntity> getOdsUserList(Date flightDate, String flightNo, String depStn, String arrStn){
        ResultSetHandler<List<OdsUserListEntity>> handler = new BeanListHandler<OdsUserListEntity>(
                OdsUserListEntity.class);
        
        List<OdsUserListEntity> list = null;
        try {
            StringBuilder builder = new StringBuilder();
            builder.append("SELECT o.`id` AS odsId,  ");
            builder.append("       o.`crew_type_id` AS crewTypeId,  ");
            builder.append("       o.`flight_date` AS flightDate,  ");
            builder.append("       o.`flight_no` AS flightNo,  ");
            builder.append("       o.`ac_no` AS acNo,  ");
            builder.append("       o.`staff_id` AS staffId,  ");
            builder.append("       o.`staff_name` AS staffName,  ");
            builder.append("       o.`rank_sd` AS rankSd,  ");
            builder.append("       o.`sign_up` AS signUp,  ");
            builder.append("       o.`sign_up_time` AS signUpTime,  ");
            builder.append("       o.`min_sign_up_start_time` AS minSignUpStartTime,  ");
            builder.append("       o.`max_sign_up_start_time` AS maxSignUpStartTime,  ");
            builder.append("       o.`sign_up_start_time` AS signUpStartTime,  ");
            builder.append("       o.`sign_up_end_time` AS signUpEndTime,  ");
            builder.append("       os.`flight_plan_confirm_sign_time` AS flightPlanConfirmSignTime,  ");
            builder.append("       os.`prepare_before40_sign_time` AS prepareBefore40SignTime,  ");
            builder.append("       o.`dep_stn` AS depStn,  ");
            builder.append("       o.`arr_stn` AS arrStn, ");
            builder.append("       d.std_chn AS stdChn  ");
            builder.append("FROM `foc_ods` o ");
            
            builder.append("LEFT JOIN foc_flight_dynamics d ");
            builder.append("  ON o.flight_date = d.datop_chn ");
            builder.append("    AND o.flight_no = d.flight_no ");
            builder.append("      AND o.dep_stn = d.dep_stn ");
            builder.append("        AND o.arr_stn = d.arr_stn ");
            
            builder.append("LEFT JOIN foc_ods2sign os  ");
            builder.append("  ON o.flight_date = os.flight_date  ");
            builder.append("    AND o.flight_no = os.flight_no  ");
            builder.append("      AND o.dep_stn = os.dep_stn  ");
            builder.append("        AND o.arr_stn = os.arr_stn  ");
            builder.append("          AND o.staff_id = os.staff_id ");
            
            builder.append("WHERE o.`flight_date`=? ");
            builder.append("  AND o.`flight_no`=? ");
            builder.append("  AND o.`dep_stn`=? ");
            builder.append("  AND o.`arr_stn`=? ");
            
//            System.out.println(builder.toString()+"["+flightDate+","+flightNo+","+depStn+","+arrStn+"]");
            list = run.query(builder.toString(), handler, flightDate, flightNo, depStn, arrStn);
            if (list != null && list.size() > 0) {
                return list;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 获取“准备会前确认签到”
     * 
     * @author renmb
     * @time  2016年12月18日
     * @return
     */
    public List<OdsUserListEntity> getOdsUserList(){
        ResultSetHandler<List<OdsUserListEntity>> handler = new BeanListHandler<OdsUserListEntity>(
                OdsUserListEntity.class);
        
        List<OdsUserListEntity> list = null;
        try {
            StringBuilder builder = new StringBuilder();
//            builder.append("SELECT `id` AS odsId, ");
//            builder.append("       `crew_type_id` AS crewTypeId, ");
//            builder.append("       `flight_date` AS flightDate, ");
//            builder.append("       `flight_no` AS flightNo, ");
//            builder.append("       `ac_no` AS acNo, ");
//            builder.append("       `staff_id` AS staffId, ");
//            builder.append("       `staff_name` AS staffName, ");
//            builder.append("       `rank_sd` AS rankSd, ");
//            builder.append("       `sign_up` AS signUp, ");
//            builder.append("       `sign_up_time` AS signUpTime, ");
//            builder.append("       `min_sign_up_start_time` AS minSignUpStartTime, ");
//            builder.append("       `max_sign_up_start_time` AS maxSignUpStartTime, ");
//            builder.append("       `sign_up_start_time` AS signUpStartTime, ");
//            builder.append("       `sign_up_end_time` AS signUpEndTime, ");
//            
//            builder.append("       `flight_plan_confirm_sign_time` AS flightPlanConfirmSignTime, ");
//            builder.append("       `prepare_before40_sign_time` AS prepareBefore40SignTime, ");
//            
//            builder.append("       `dep_stn` AS depStn, ");
//            builder.append("       `arr_stn` AS arrStn ");
//            builder.append("FROM `foc_ods` ");
            
            builder.append("SELECT o.`id` AS odsId,  ");
            builder.append("       o.`crew_type_id` AS crewTypeId,  ");
            builder.append("       o.`flight_date` AS flightDate,  ");
            builder.append("       o.`flight_no` AS flightNo,  ");
            builder.append("       o.`ac_no` AS acNo,  ");
            builder.append("       o.`staff_id` AS staffId,  ");
            builder.append("       o.`staff_name` AS staffName,  ");
            builder.append("       o.`rank_sd` AS rankSd,  ");
            builder.append("       o.`sign_up` AS signUp,  ");
            builder.append("       o.`sign_up_time` AS signUpTime,  ");
            builder.append("       o.`min_sign_up_start_time` AS minSignUpStartTime,  ");
            builder.append("       o.`max_sign_up_start_time` AS maxSignUpStartTime,  ");
            builder.append("       o.`sign_up_start_time` AS signUpStartTime,  ");
            builder.append("       o.`sign_up_end_time` AS signUpEndTime,  ");
            builder.append("       os.`flight_plan_confirm_sign_time` AS flightPlanConfirmSignTime,  ");
            builder.append("       os.`prepare_before40_sign_time` AS prepareBefore40SignTime,  ");
            builder.append("       o.`dep_stn` AS depStn,  ");
            builder.append("       o.`arr_stn` AS arrStn, ");
            builder.append("       d.std_chn AS stdChn  ");
            builder.append("FROM `foc_ods` o ");
            builder.append("LEFT JOIN foc_flight_dynamics d ");
            builder.append("  ON o.flight_date = d.datop_chn ");
            builder.append("    AND o.flight_no = d.flight_no ");
            builder.append("      AND o.dep_stn = d.dep_stn ");
            builder.append("        AND o.arr_stn = d.arr_stn ");
            
            builder.append("LEFT JOIN foc_ods2sign os  ");
            builder.append("  ON o.flight_date = os.flight_date  ");
            builder.append("    AND o.flight_no = os.flight_no  ");
            builder.append("      AND o.dep_stn = os.dep_stn  ");
            builder.append("        AND o.arr_stn = os.arr_stn  ");
            builder.append("          AND o.staff_id = os.staff_id ");
            
            builder.append("WHERE o.`flight_date` BETWEEN ? AND ?  ");
            builder.append("  AND o.`sign_up`='1' ");
            builder.append("  AND o.`ods_pushed`=?");
            
            Date d = new Date();
            String current = DateUtil.getDateTime(d);
            String dateFrom = DateUtil.dateToStr(DateUtil.offsetDate(d, 0), DateUtil.DATE_FORMAT);
            String dateTo = DateUtil.dateToStr(DateUtil.offsetDate(d, 1), DateUtil.DATE_FORMAT);
            
            System.out.println(builder.toString()+"["+dateFrom+","+dateTo+","+current+",NO]");
            list = run.query(builder.toString(), handler, dateFrom, dateTo, "NO");
            if (list != null && list.size() > 0) {
                return list;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 
     * 
     * @author renmb
     * @time  2017年3月9日
     * @param flightDate
     * @param flightNo
     * @param depStn
     * @param arrStn
     * @param userId
     * @return
     */
    public List<OdsUserListEntity> getOdsUserSigned(Date flightDate, String flightNo, String depStn, String arrStn, String userId){
        ResultSetHandler<List<OdsUserListEntity>> handler = new BeanListHandler<OdsUserListEntity>(
                OdsUserListEntity.class);
        
        List<OdsUserListEntity> list = null;
        try {
            StringBuilder builder = new StringBuilder();
            
            builder.append("SELECT os.`flight_plan_confirm_sign_time` AS flightPlanConfirmSignTime,  ");
            builder.append("       os.`prepare_before40_sign_time` AS prepareBefore40SignTime,  ");
            builder.append("FROM JOIN foc_ods2sign os  ");
            
            builder.append("WHERE os.flight_date = ?  ");
            builder.append("  AND os.flight_no = ? ");
            builder.append("  AND os.dep_stn = ? ");
            builder.append("  AND os.arr_stn = ? ");
            builder.append("  AND os.staff_id = ? ");
            
            System.out.println(builder.toString()+"["+flightDate+","+flightNo+","+depStn+","+arrStn+","+userId+"]");
            list = run.query(builder.toString(), handler, flightDate, flightNo, depStn, arrStn, userId);
            if (list != null && list.size() > 0) {
                return list;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }
}
