package com.foc.flightdynamics.service;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

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.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.foc.conf.Constants;
import com.foc.conf.GConstants;
import com.foc.flightdynamics.bean.FlightDynamicsBean;
import com.foc.flightdynamics.entity.FlightDynamicsEntity;
import com.foc.push.entity.FlightCrewListEntity;
import com.foc.push.entity.UserNotifyEntity;
import com.foc.push.service.PushService;
import com.foc.util.DBHandler;
import com.foc.util.DBHelper;
import com.foc.util.DateUtil;
import com.foc.util.IdWorker;

public class FlightDynamicsService {

    private QueryRunner run = null;

    private PushService service = new PushService();
    
    public FlightDynamicsService() {
        try {
            this.run = new QueryRunner(DBHelper.getDataSource());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    public void finish(final Map<String, String> flightDates, final String modified){
        long startTime = System.nanoTime();
        if(flightDates != null){
            for(String flightDate:flightDates.keySet()){
                
                deleteLastDynamicsData(flightDate, modified);
            }
        }
        long endTime  = System.nanoTime();
        long duration = endTime - startTime;
        System.out.println("【FlightDynamicsService】【finish】处理时间：" + DateUtil.getDurationTime(duration));
    }
    public boolean deleteLastDynamicsData(final String flightDate, final String modified){
        try {
            if(StringUtils.isNotEmpty(flightDate)){
                
                String insert = "INSERT INTO "+Constants.FOC_FLIGHT_DYNAMICS_HISTORY+" SELECT * FROM "+Constants.FOC_FLIGHT_DYNAMICS+" WHERE datop_chn=? AND modified !=?";
                System.out.println(insert+"["+flightDate+","+modified+"]");
                run.update(insert, flightDate, modified);
                
                String sql = "DELETE FROM "+Constants.FOC_FLIGHT_DYNAMICS+" WHERE datop_chn=? AND modified !=?";
                System.out.println(sql+"["+flightDate+","+modified+"]");
                int count = run.update(sql, flightDate, modified);
                if(count>0)
                    return true;
            }
        } catch (Exception e) {
            // TODO: handle exception
        }
        return false;
    }
    public String getStringValue(Object object, String want){
        
        if(object == null || StringUtils.isEmpty(String.valueOf(object)))
            return want;
        return String.valueOf(object);
    }
    public void insertFlightDynamicsData(JSONObject result, final String modified, boolean dailyFlag, Map<String, String> flightDates) {
//        Date curDate = new Date();
//        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
//        String curDateStr = dateFormat.format(curDate);
//        dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//        boolean dailyFlag = false; //是否同步每日5时信息
//        try {
//            Date startDateTime = dateFormat.parse(curDateStr + " 05:00:00");
//            Date endDateTime = dateFormat.parse(curDateStr + " 05:04:00");
//            if (startDateTime.getTime() <= curDate.getTime() && endDateTime.getTime() >= curDate.getTime()) {
//                dailyFlag = true;
//            }
//        } catch (ParseException e) {
//            e.printStackTrace();
//        }
//        dailyFlag = true;
        JSONObject dataJson = (JSONObject) result.get("data");
        JSONArray  jsonArray = (JSONArray) dataJson.get("apiResp");
        
        List<FlightDynamicsBean> flightDynamicsBeans = JSONObject.parseArray(jsonArray.toJSONString(),
                FlightDynamicsBean.class);
        if (flightDynamicsBeans != null) {
//            int index=0;
            try {
                int size = flightDynamicsBeans.size();
                for (int index=0;index<size;index++) {

//                    index++;
                    try {
                        FlightDynamicsBean flightDynamicsBean = flightDynamicsBeans.get(index);
                        
                        System.out.print("【DYNAMIC】第【"+(index+1)+"/"+size+"】条【"+flightDynamicsBean.getFlightNo()+"】【"+flightDynamicsBean.getDatopChn()+"】【"+flightDynamicsBean.getStatus()+"】");
                        
                        mergeFlightDynamics(flightDynamicsBean, dailyFlag, modified);
                        
                        if(flightDates.get(flightDynamicsBean.getDatopChn()) == null){
                            
                            flightDates.put(flightDynamicsBean.getDatopChn(), flightDynamicsBean.getDatopChn());
                        }
                        System.out.println("【正常】");
                    } catch (Exception e) {
                        // TODO: handle exception
                        
                        System.out.println("【异常】");
                    }
                }
            } catch (Exception e) {
                // TODO: handle exception
                e.printStackTrace();
            }
        }
    }

    /**
     * 同步数据到每日5点数据
     * @param flightDynamicsBean
     * @param dailyFlag
     */
//    private void mergeFlightDailyDynamics(FlightDynamicsBean flightDynamicsBean, boolean dailyFlag) {
//        // 是5点时间，有数据直接更新，没有数据直接插入
//        if (existFlightDynamics(flightDynamicsBean.getFlightNo(),
//                flightDynamicsBean.getDatop(), flightDynamicsBean.getDepStn(), flightDynamicsBean.getArrStn(), Constants.FOC_FLIGHT_DYNAMICS_DAILY)) {
//            if (dailyFlag) {
//                updateFlightDynamics(flightDynamicsBean, Constants.FOC_FLIGHT_DYNAMICS_DAILY);
//            }
//        } else {
//           insertFlightDynamics(flightDynamicsBean, Constants.FOC_FLIGHT_DYNAMICS_DAILY);
//        }
//    }

    private void mergeFlightDynamics(FlightDynamicsBean flightDynamicsBean, boolean dailyFlag, final String modified) {

//        Map<MapType, FlightDynamicsEntity> flightDynamicsEntityMap = new HashMap<MapType, FlightDynamicsEntity>();
        FlightDynamicsEntity flight = existFlightDynamics(flightDynamicsBean.getFlightNo(),
                flightDynamicsBean.getDatopChn(), flightDynamicsBean.getDepStn(), flightDynamicsBean.getArrStn(), Constants.FOC_FLIGHT_DYNAMICS);
        if (flight != null) {
            
            String estStatus = this.getFlightEstStatus(flightDynamicsBean.getFlightNo(),
                    flightDynamicsBean.getDatopChn(), flightDynamicsBean.getDepStn(), flightDynamicsBean.getArrStn());
            if(StringUtils.isNotBlank(flight.getStatus())){
                if(!"CNL".equals(estStatus) && "CNL".equals(flightDynamicsBean.getStatus())){// 航班取消了
                    if(flight.getStatus().equals(flightDynamicsBean.getStatus()) == false){// 上一个航班状态为非CNL
                        
                        List<FlightCrewListEntity> crewList = this.getFlightCrewList(flightDynamicsBean.getFlightNo(),
                                flightDynamicsBean.getDatopChn(), flightDynamicsBean.getDepStn(), flightDynamicsBean.getArrStn());
                        
                        if(crewList != null){
                            
                            Map<String, String> payload = new HashMap<String, String>();
                            payload.put("flightNo",   flightDynamicsBean.getFlightNo());
                            payload.put("depIata",    flightDynamicsBean.getDepStn());
                            payload.put("arrIata",    flightDynamicsBean.getArrStn());
                            payload.put("flightDate", flightDynamicsBean.getDatopChn());
                            payload.put("lastStatus", flight.getStatus());
                            
                            for(FlightCrewListEntity crew:crewList){
                                
                                UserNotifyEntity notify1 = new UserNotifyEntity();
                                notify1.setId(IdWorker.getNextId());
                                notify1.setUserId(crew.getUserId());// 消息接受者用户ID(*必填)
                                notify1.setType("MS1003");// 消息类型(*必填)
                                notify1.setTitle(flightDynamicsBean.getFlightNo()+"（"+flight.getDepCityName()+"－"+flight.getArrCityName()+"）航班取消了，请小伙伴们关注！");// 提示标题(*必填)
                                
                                service.sendPush(notify1, payload);
                                
                                String[] userList = GConstants.getValue("send.push.user.list", "").split(";");
                                for (String userId:userList) {
                                    
                                    if(StringUtils.isNotEmpty(userId)){
                                        
                                        UserNotifyEntity notify2 = new UserNotifyEntity();
                                        notify2.setId(IdWorker.getNextId());
                                        notify2.setUserId(crew.getUserId());// 消息接受者用户ID(*必填)
                                        notify2.setType("MS1003");// 消息类型(*必填)
                                        notify2.setTitle(flightDynamicsBean.getFlightNo()+"（"+flight.getDepCityName()+"－"+flight.getArrCityName()+"）航班取消了，请小伙伴们关注！");// 提示标题(*必填)
                                        
                                        service.sendPush(notify2, payload);
                                    }
                                }
                            }
                        }
                    }
                }
                if("DEL".equals(flightDynamicsBean.getStatus())){// 航班延误了
                    if(flight.getStatus().equals(flightDynamicsBean.getStatus()) == false){// 上一个航班状态为非DEL
                        
                        List<FlightCrewListEntity> crewList = this.getFlightCrewList(flightDynamicsBean.getFlightNo(),
                                flightDynamicsBean.getDatopChn(), flightDynamicsBean.getDepStn(), flightDynamicsBean.getArrStn());
                        
                        if(crewList != null){
                            Map<String, String> payload = new HashMap<String, String>();
                            payload.put("flightNo",   flightDynamicsBean.getFlightNo());
                            payload.put("depIata",    flightDynamicsBean.getDepStn());
                            payload.put("arrIata",    flightDynamicsBean.getArrStn());
                            payload.put("flightDate", flightDynamicsBean.getDatopChn());
                            payload.put("lastStatus", flight.getStatus());
                            
                            for(FlightCrewListEntity crew:crewList){
                                
                                UserNotifyEntity notify = new UserNotifyEntity();
                                notify.setId(IdWorker.getNextId());
                                notify.setUserId(crew.getUserId());// 消息接受者用户ID(*必填)
                                notify.setType("MS1004");// 消息类型(*必填)
                                notify.setTitle(flightDynamicsBean.getFlightNo()+"（"+flight.getDepCityName()+"－"+flight.getArrCityName()+"）航班延误了，请小伙伴们关注！");// 提示标题(*必填)
                                
                                service.sendPush(notify, payload);
                                
                                String[] userList = GConstants.getValue("send.push.user.list", "").split(";");
                                for (String userId:userList) {
                                    
                                    if(StringUtils.isNotEmpty(userId)){
                                        
                                        notify = new UserNotifyEntity();
                                        notify.setId(IdWorker.getNextId());
                                        notify.setUserId(crew.getUserId());// 消息接受者用户ID(*必填)
                                        notify.setType("MS1003");// 消息类型(*必填)
                                        notify.setTitle(flightDynamicsBean.getFlightNo()+"（"+flight.getDepCityName()+"－"+flight.getArrCityName()+"）航班取消了，请小伙伴们关注！");// 提示标题(*必填)
                                        
                                        service.sendPush(notify, payload);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            System.out.print("【updateFlightDynamics】");
            updateFlightDynamics(flightDynamicsBean, estStatus, modified, Constants.FOC_FLIGHT_DYNAMICS);
        } else{
            System.out.print("【insertFlightDynamics】");
            insertFlightDynamics(flightDynamicsBean, modified, Constants.FOC_FLIGHT_DYNAMICS);
        }
//        String today = DateUtil.dateToStr(new Date(), DateUtil.DATE_FORMAT);
        if (!dailyFlag) {
            return;
        }
        Calendar calendar = new GregorianCalendar();
        calendar.add(Calendar.DATE, 1);
        String tomorrow = DateUtil.dateToStr(calendar.getTime(), DateUtil.DATE_FORMAT);
        if(tomorrow.equals(flightDynamicsBean.getDatopChn())){// 如果是明天的航班则需要插入数据
            
            flight = existFlightDynamics(flightDynamicsBean.getFlightNo(),
                    flightDynamicsBean.getDatopChn(), flightDynamicsBean.getDepStn(), flightDynamicsBean.getArrStn(), Constants.FOC_EST_FLIGHT_DYNAMICS);
            // 如果数据不存在则添加一条数据
            if (flight == null) {
                
                insertFlightDynamics(flightDynamicsBean, modified, Constants.FOC_EST_FLIGHT_DYNAMICS);
            }
        }
    }

    public void insertFlightDynamics(FlightDynamicsBean flightDynamicsBean, final String modified, String tableName) {
        List<Map<String, Object>> columnMapList = new ArrayList<Map<String, Object>>();
        Map<String, Object> columnMap = new TreeMap<String, Object>();
        // 字段和值绑定
        columnMap.put("t_dwn_chn", getStringValue(flightDynamicsBean.getTDwnChn(), null));
        columnMap.put("eta", getStringValue(flightDynamicsBean.getEta(), null));
        columnMap.put("dep_stn", getStringValue(flightDynamicsBean.getDepStn(), ""));
        columnMap.put("update_time", getStringValue(flightDynamicsBean.getUpdateTime(), null));
        columnMap.put("ata", getStringValue(flightDynamicsBean.getAta(), null));
        columnMap.put("status", flightDynamicsBean.getStatus());
        
        if(Constants.FOC_FLIGHT_DYNAMICS.equals(tableName)){// 实际航班添加计划航班状态
            columnMap.put("est_status", this.getFlightEstStatus(flightDynamicsBean.getFlightNo(),
                    flightDynamicsBean.getDatopChn(), flightDynamicsBean.getDepStn(), flightDynamicsBean.getArrStn()));// 航班计划状态
        }
        columnMap.put("etd", getStringValue(flightDynamicsBean.getEtd(), null));
        columnMap.put("arr_stn", flightDynamicsBean.getArrStn());
        columnMap.put("datop_chn", getStringValue(flightDynamicsBean.getDatopChn(), null));
        columnMap.put("t_off_chn", getStringValue(flightDynamicsBean.getTOffChn(), null));
        columnMap.put("t_dwn", getStringValue(flightDynamicsBean.getTDwn(), null));
        columnMap.put("ata_chn", getStringValue(flightDynamicsBean.getAtaChn(), null));
        columnMap.put("gate", getStringValue(flightDynamicsBean.getGate(), ""));
        columnMap.put("t_off", getStringValue(flightDynamicsBean.getTOff(), null));
        columnMap.put("flight_no", getStringValue(flightDynamicsBean.getFlightNo(), ""));
        columnMap.put("atd_chn", getStringValue(flightDynamicsBean.getAtdChn(), null));
        columnMap.put("ac_long_no", getStringValue(flightDynamicsBean.getAcLongNo(), ""));
        columnMap.put("etd_chn", getStringValue(flightDynamicsBean.getEtdChn(), null));
        columnMap.put("datop", getStringValue(flightDynamicsBean.getDatop(), null));
        columnMap.put("is_stop_over", getStringValue(flightDynamicsBean.getIsStopOver(), null));
        columnMap.put("eta_chn", getStringValue(flightDynamicsBean.getEtaChn(), null));
        columnMap.put("atd", getStringValue(flightDynamicsBean.getAtd(), null));
        
        columnMap.put("std", getStringValue(flightDynamicsBean.getStd(), null));//std 计划起飞时间
        columnMap.put("sta", getStringValue(flightDynamicsBean.getSta(), null));//sta 计划到达时间
        columnMap.put("std_chn", getStringValue(flightDynamicsBean.getStdChn(), null));//std_chn 计划起飞时间（北京时间）
        columnMap.put("sta_chn", getStringValue(flightDynamicsBean.getStaChn(), null));//sta_chn 计划到达时间（北京时间）
        
        columnMap.put("ac_type", getStringValue(flightDynamicsBean.getAcType(), ""));
        columnMap.put("orig_ac_type", getStringValue(flightDynamicsBean.getOrigAcType(), ""));
        
        columnMap.put("div_recode", getStringValue(flightDynamicsBean.getDivRecode(), ""));// 备降代码
        columnMap.put("div_flag", getStringValue(flightDynamicsBean.getDivFlag(), ""));// 备降标识
        columnMap.put("leg_no", getStringValue(flightDynamicsBean.getLegNo(), ""));// 航段编号 "legNo": "100"    数据150表示备降200表示经停

        columnMap.put("delay1", getStringValue(flightDynamicsBean.getDelay1(), ""));// 第一次延误代码
        columnMap.put("dur1", getStringValue(flightDynamicsBean.getDur1(), ""));// 第一次延误时间
        columnMap.put("modified", modified);
        
        columnMap.put("stc", getStringValue(flightDynamicsBean.getStc(), ""));

        columnMapList.add(columnMap);

        /**
         * 单条插入，保证一条语句插入报错，还能往下执行
         */
        
//        System.out.print("【DYNAMIC】【insertFlightDynamics】【"+flightDynamicsBean.getFlightNo()+"】【"+flightDynamicsBean.getDatopChn()+"】");
        DBHandler dBHandler = new DBHandler();
        dBHandler.batchInsert(tableName, columnMapList);
    }

    /**
     * 获取航班的计划状态
     * 
     * @author renmb
     * @time  2016年10月31日
     * @param flightNo
     * @param datopChn
     * @param depStn
     * @param arrStn
     * @return
     */
    public String getFlightEstStatus(String flightNo,String datopChn,String depStn, String arrStn){
        
        ResultSetHandler<List<FlightDynamicsEntity>> handler = new BeanListHandler<FlightDynamicsEntity>(
                FlightDynamicsEntity.class);
        String query = "SELECT * FROM " + Constants.FOC_EST_FLIGHT_DYNAMICS + " WHERE flight_no=? AND datop_chn=? AND dep_stn=? AND arr_stn=?";
        List<FlightDynamicsEntity> list = null;
        try {
            list = run.query(query, handler, flightNo, datopChn, depStn, arrStn);
            if (list != null && list.size() > 0) {

                FlightDynamicsEntity flight = list.get(0);
                if(StringUtils.isNotBlank(flight.getStatus()))
                    return flight.getStatus();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return "";
    }
    private void updateFlightDynamics(FlightDynamicsBean flightDynamicsBean, String estStatus, final String modified, String tableName) {

        DBHandler dBHandler = new DBHandler();
        Map<String, Object> columnMap = new TreeMap<String, Object>();
        Map<String, Object> whereMap = new TreeMap<String, Object>();

        columnMap.put("t_dwn_chn", getStringValue(flightDynamicsBean.getTDwnChn(), null));
        columnMap.put("eta", getStringValue(flightDynamicsBean.getEta(), null));
//        columnMap.put("dep_stn", getStringValue(flightDynamicsBean.getDepStn(), ""));
        columnMap.put("update_time", getStringValue(flightDynamicsBean.getUpdateTime(), null));
        columnMap.put("ata", getStringValue(flightDynamicsBean.getAta(), null));
        columnMap.put("status", getStringValue(flightDynamicsBean.getStatus(), ""));
        if(Constants.FOC_FLIGHT_DYNAMICS.equals(tableName)){// 实际航班添加计划航班状态
            columnMap.put("est_status", this.getFlightEstStatus(flightDynamicsBean.getFlightNo(),
                    flightDynamicsBean.getDatopChn(), flightDynamicsBean.getDepStn(), flightDynamicsBean.getArrStn()));// 航班计划状态
        }
        columnMap.put("etd", getStringValue(flightDynamicsBean.getEtd(), null));
//        columnMap.put("arr_stn", flightDynamicsBean.getArrStn());
//        columnMap.put("datop_chn", getStringValue(flightDynamicsBean.getDatopChn(), null));
        columnMap.put("t_off_chn", getStringValue(flightDynamicsBean.getTOffChn(), null));
        columnMap.put("t_dwn", getStringValue(flightDynamicsBean.getTDwn(), null));
        columnMap.put("ata_chn", getStringValue(flightDynamicsBean.getAtaChn(), null));
        columnMap.put("gate", getStringValue(flightDynamicsBean.getGate(), ""));
        columnMap.put("t_off", getStringValue(flightDynamicsBean.getTOff(), null));
//        columnMap.put("flight_no", flightDynamicsBean.getFlightNo());
        columnMap.put("atd_chn", getStringValue(flightDynamicsBean.getAtdChn(), null));
        columnMap.put("ac_long_no", getStringValue(flightDynamicsBean.getAcLongNo(), ""));
        columnMap.put("etd_chn", getStringValue(flightDynamicsBean.getEtdChn(), null));
        columnMap.put("datop", getStringValue(flightDynamicsBean.getDatop(), null));
        columnMap.put("is_stop_over", getStringValue(flightDynamicsBean.getIsStopOver(), ""));
        columnMap.put("eta_chn", getStringValue(flightDynamicsBean.getEtaChn(), null));
        columnMap.put("atd", getStringValue(flightDynamicsBean.getAtd(), null));
        
        columnMap.put("std", getStringValue(flightDynamicsBean.getStd(), null));//std 计划起飞时间
        columnMap.put("sta", getStringValue(flightDynamicsBean.getSta(), null));//sta 计划到达时间
        columnMap.put("std_chn", getStringValue(flightDynamicsBean.getStdChn(), null));//std_chn 计划起飞时间（北京时间）
        columnMap.put("sta_chn", getStringValue(flightDynamicsBean.getStaChn(), null));//sta_chn 计划到达时间（北京时间）
        
        columnMap.put("ac_type", getStringValue(flightDynamicsBean.getAcType(), ""));
        columnMap.put("orig_ac_type", getStringValue(flightDynamicsBean.getOrigAcType(), ""));
        
        columnMap.put("div_recode", getStringValue(flightDynamicsBean.getDivRecode(), ""));// 备降代码
        columnMap.put("div_flag", getStringValue(flightDynamicsBean.getDivFlag(), ""));// 备降标识
        columnMap.put("leg_no", getStringValue(flightDynamicsBean.getLegNo(), ""));// 航段编号 "legNo": "100"    数据150表示备降200表示经停

        columnMap.put("delay1", getStringValue(flightDynamicsBean.getDelay1(), ""));// 第一次延误代码
        columnMap.put("dur1", getStringValue(flightDynamicsBean.getDur1(), ""));// 第一次延误时间
        columnMap.put("modified", modified);
        
        columnMap.put("stc", getStringValue(flightDynamicsBean.getStc(), ""));
        
        whereMap.put("flight_no", getStringValue(flightDynamicsBean.getFlightNo(), ""));
        whereMap.put("datop_chn", getStringValue(flightDynamicsBean.getDatopChn(), null));
        whereMap.put("dep_stn", getStringValue(flightDynamicsBean.getDepStn(), ""));
        whereMap.put("arr_stn", getStringValue(flightDynamicsBean.getArrStn(), ""));

//        System.out.print("【DYNAMIC】【updateFlightDynamics】【"+flightDynamicsBean.getFlightNo()+"】【"+flightDynamicsBean.getDatopChn()+"】");
        dBHandler.update(tableName, columnMap, whereMap);
    }

    public FlightDynamicsEntity existFlightDynamics(String flightNo,
            String datopChn, String depStn, String arrStn, String tableName) {
        ResultSetHandler<List<FlightDynamicsEntity>> handler = new BeanListHandler<FlightDynamicsEntity>(
                FlightDynamicsEntity.class);
//        String query = "SELECT * FROM " + tableName + " WHERE flight_no=? AND datop_chn=? AND dep_stn=? AND arr_stn=?";
        List<FlightDynamicsEntity> list = null;
        try {
            StringBuilder builder = new StringBuilder();
            builder.append("SELECT  a.`datop_chn` AS datopChn, ");
            builder.append("        a.`datop`, ");
            builder.append("        a.`flight_no` AS flightNo, ");
            builder.append("        a.`status`, ");
            builder.append("        a.dep_stn AS depStn, ");
            builder.append("        b.airport_name AS depAirportName, ");
            builder.append("        b.city_name AS depCityName, ");
            builder.append("        a.arr_stn, ");
            builder.append("        c.airport_name AS arrAirportName, ");
            builder.append("        c.city_name AS arrCityName ");
            builder.append("FROM `"+tableName+"` a ");
            builder.append("LEFT JOIN `foc_airports` b ");
            builder.append("  ON a.`dep_stn` = b.`airport_iata` ");
            builder.append("LEFT JOIN `foc_airports` c ");
            builder.append("  ON a.`arr_stn` = c.`airport_iata` ");
            builder.append("WHERE flight_no=? AND datop_chn=? AND dep_stn=? AND arr_stn=? ");
//            System.out.println(builder.toString()+"["+flightNo+"],["+datopChn+"],["+depStn+"],["+arrStn+"]");
            list = run.query(builder.toString(), handler, flightNo, datopChn, depStn, arrStn);
            if (list != null && list.size() > 0) {

//                flightDynamicsEntityMap.put(MapType.flightDynamicsEntityType, list.get(0));
                return list.get(0);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }
    
    public List<FlightCrewListEntity> getFlightCrewList(String flightNo,
            String datop, String depStn, String arrStn) {
        ResultSetHandler<List<FlightCrewListEntity>> handler = new BeanListHandler<FlightCrewListEntity>(
                FlightCrewListEntity.class);
        
        StringBuffer buf = new StringBuffer();
        buf.append("  SELECT o.rank_sd AS rankSd, " );
        buf.append("         o.staff_id AS userId, " );
        buf.append("         o.staff_name AS username, " );
        buf.append("         u.photo, " );
        buf.append("         u.phone, " );
        buf.append("         u.mobile " );
        buf.append("  FROM foc_ods o " );
        buf.append("  LEFT JOIN sys_user u " );
        buf.append("    ON o.staff_id=u.id " );
        buf.append("  LEFT JOIN sys_office c  " );
        buf.append("    ON c.id = u.company_id " );
        buf.append("  LEFT JOIN sys_office f  " );
        buf.append("    ON f.id = u.office_id " );
        buf.append("  LEFT JOIN sys_user_profiles p  " );
        buf.append("    ON o.staff_id=p.user_id " );
        buf.append("  WHERE o.flight_no=? " );
        buf.append("    AND o.flight_date=? " );
        buf.append("    AND o.dep_stn=? " );
        buf.append("    AND o.arr_stn=? " );
        
        List<FlightCrewListEntity> list = null;
        try {
            list = run.query(buf.toString(), handler, flightNo, datop, depStn, arrStn);
            if (list != null && list.size() > 0) {

                return list;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }
    
    enum MapType {
        flightDynamicsEntityType;
    }
}
