package com.foc.flightdynamics.service;

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;
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 java.sql.SQLException;
import java.util.*;

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();
            }
        }
    }


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

        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())) {
                    // 上一个航班状态为非CNL
                    if (flight.getStatus().equals(flightDynamicsBean.getStatus()) == false) {

                        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());
                                // 消息接受者用户ID(*必填)
                                notify1.setUserId(crew.getUserId());
                                // 消息类型(*必填)
                                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());
                                        // 消息接受者用户ID(*必填)
                                        notify2.setUserId(crew.getUserId());
                                        // 消息类型(*必填)
                                        notify2.setType("MS1003");
                                        // 提示标题(*必填)
                                        notify2.setTitle(flightDynamicsBean.getFlightNo() + "（" + flight.getDepCityName() + "－" + flight.getArrCityName() + "）航班取消了，请小伙伴们关注！");
                                        service.sendPush(notify2, payload);
                                    }
                                }
                            }
                        }
                    }
                }
                // 航班延误了
                if ("DEL".equals(flightDynamicsBean.getStatus())) {
                    // 上一个航班状态为非DEL
                    if (flight.getStatus().equals(flightDynamicsBean.getStatus()) == false) {

                        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());
                                // 消息接受者用户ID(*必填)
                                notify.setUserId(crew.getUserId());
                                // 消息类型(*必填)
                                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());
                                        // 消息接受者用户ID(*必填)
                                        notify.setUserId(crew.getUserId());
                                        // 消息类型(*必填)
                                        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);
        }
        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));
        //std 计划起飞时间
        columnMap.put("std", getStringValue(flightDynamicsBean.getStd(), null));
        //sta 计划到达时间
        columnMap.put("sta", getStringValue(flightDynamicsBean.getSta(), null));
        //std_chn 计划起飞时间（北京时间）
        columnMap.put("std_chn", getStringValue(flightDynamicsBean.getStdChn(), null));
        //sta_chn 计划到达时间（北京时间）
        columnMap.put("sta_chn", getStringValue(flightDynamicsBean.getStaChn(), null));

        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(), ""));
        // 航段编号 "legNo": "100"    数据150表示备降200表示经停
        columnMap.put("leg_no", getStringValue(flightDynamicsBean.getLegNo(), ""));
        // 第一次延误代码
        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);
    }

    /**
     * 获取航班的计划状态
     *
     * @param flightNo
     * @param datopChn
     * @param depStn
     * @param arrStn
     * @return
     * @author renmb
     * @time 2016年10月31日
     */
    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;
    }
}
