潘钊 / bsth_control · Files

package com.bsth.service.schedule;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.bsth.entity.realcontrol.ScheduleRealInfo;
import com.bsth.entity.schedule.SchedulePlanInfo;
import com.bsth.entity.schedule.TTInfoDetail;
import com.bsth.repository.realcontrol.ScheduleRealInfoRepository;
import com.bsth.util.ReportUtils;

@Service
public class PeopleCarPlanServiceImpl implements PeopleCarPlanService {

	@Autowired
	private ScheduleRealInfoRepository scheduleRealInfoRepository;
	
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public List<Map<String, Object>> queryPeopleCar(String line, String date, String type) {
		Map<String, List<SchedulePlanInfo>> keyMap = new HashMap<String, List<SchedulePlanInfo>>();
		Map<String, List<Map<String, Object>>> temp = new HashMap<String, List<Map<String, Object>>>();
		List<SchedulePlanInfo> list = new ArrayList<SchedulePlanInfo>();
		List<Map<String, Object>> resList = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
		
		if(date.length() == 0){
			date = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		}

		try {

			String sql = "select * from bsth_c_s_sp_info where DATE_FORMAT(schedule_date,'%Y-%m-%d') = '"+date+"'";
			if(line.length() != 0){
				sql += " and xl_bm = '"+line+"'";
			}
			
			list =jdbcTemplate.query(sql,
					new RowMapper<SchedulePlanInfo>(){
				@Override
				public SchedulePlanInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
					
					SchedulePlanInfo schedule = new SchedulePlanInfo();
					
					schedule.setXlName(rs.getString("xl_name"));
					schedule.setLpName(rs.getString("lp_name"));
					schedule.setClZbh(rs.getString("cl_zbh"));
					schedule.setjName(rs.getString("j_name"));
					schedule.setsName(rs.getString("s_name"));
					schedule.setJhlc(rs.getDouble("jhlc"));
					return schedule;
				}
			});
	

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//分组计算路程公里
		for(SchedulePlanInfo schedule : list){
			String key = schedule.getXlName() + "/" + schedule.getLpName() + "/" +
					schedule.getClZbh() + "/" + schedule.getjName() + "/" + schedule.getsName();
			if(!keyMap.containsKey(key))
				keyMap.put(key, new ArrayList<SchedulePlanInfo>());
			keyMap.get(key).add(schedule);
		}
		for(String key : keyMap.keySet()){
			Map<String, Object> tempMap = new HashMap<String, Object>();
			BigDecimal jhlc = new BigDecimal(0);
			for(SchedulePlanInfo schedule : keyMap.get(key)){
				if(schedule.getJhlc() != null)
					jhlc = jhlc.add(new BigDecimal(schedule.getJhlc()));
			}
			String[] split = key.split("/");
			tempMap.put("date", date);
			tempMap.put("line", split[0]);
			tempMap.put("lp", split[1]);
			tempMap.put("clzbh", split[2]);
			tempMap.put("jName", split[3]);
			tempMap.put("sName", split[4]);
			if(split[4].equals("null"))
				tempMap.put("sName", "/");
			tempMap.put("jhlc", jhlc.setScale(2, BigDecimal.ROUND_UP));
			mapList.add(tempMap);
		}
		
		for(Map<String, Object> m : mapList){
			String key = m.get("line").toString();
			if(!temp.containsKey(key))
				temp.put(key, new ArrayList<Map<String, Object>>());
			temp.get(key).add(m);
		}
		for(String key : temp.keySet()){
			Map<Integer, List<Map<String, Object>>> tempList = new HashMap<Integer, List<Map<String,Object>>>();
			List<Integer> keyList = new ArrayList<Integer>();
			for(Map<String, Object> m : temp.get(key)){
				int i = Integer.valueOf(m.get("lp").toString());
				if(!tempList.containsKey(i))
					tempList.put(i, new ArrayList<Map<String, Object>>());
				tempList.get(i).add(m);
				keyList.add(i);
			}
			Collections.sort(keyList);
			for(Integer i : keyList){
				for(Map<String, Object> m : tempList.get(i)){
					resList.add(m);
				}
			}
		}
		
		if(type.equals("export")){
			SimpleDateFormat sdfMonth = new SimpleDateFormat("yyyy-MM-dd"),
					sdfSimple = new SimpleDateFormat("yyyyMMdd");
			List<Iterator<?>> listI = new ArrayList<Iterator<?>>();
			Map<String,Object> map = new HashMap<String, Object>();
			ReportUtils ee = new ReportUtils();
			try {
				listI.add(resList.iterator());
				String path = this.getClass().getResource("/").getPath()+"static\\pages\\forms\\";
				ee.excelReplace(listI, new Object[] { map }, path+"mould\\peoCarPlan.xls",
						path+"export\\计划车辆班次人员" + sdfSimple.format(sdfMonth.parse(date)) + ".xls");
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		
		return resList;
	}

	@Override
	public List<Map<String, Object>> workDaily(String line, String date, String type) {
		List<Map<String, Object>> resList = new ArrayList<Map<String, Object>>();
		DecimalFormat df = new DecimalFormat("###0.##");
		if(date.length() == 0){
			date = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		}
		List<ScheduleRealInfo> list = new ArrayList<ScheduleRealInfo>();
		if(line.length() != 0)
			list = scheduleRealInfoRepository.scheduleDaily(line, date);
		else 
			list = scheduleRealInfoRepository.findByDate(date);
		Map<String, List<ScheduleRealInfo>> keyMap = new HashMap<String, List<ScheduleRealInfo>>();
		for(ScheduleRealInfo schedule : list){
			String key = schedule.getXlName();
			if(!keyMap.containsKey(key)){
				keyMap.put(key, new ArrayList<ScheduleRealInfo>());
			}
			keyMap.get(key).add(schedule);
		}
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-ddHH:mm");
		Long zgfBegin = 0l;
		Long zgfEnd = 0l;
		Long wgfBegin = 0l;
		Long wgfEnd = 0l;
		try {
			//早晚高峰时段
			zgfBegin = sdf.parse(date + "07:30").getTime();
			zgfEnd = sdf.parse(date + "09:30").getTime();
			wgfBegin = sdf.parse(date + "16:30").getTime();
			wgfEnd = sdf.parse(date + "18:30").getTime();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		for(String key : keyMap.keySet()){
			Map<String, Object> tempMap = new HashMap<String, Object>();
			Map<String, List <ScheduleRealInfo>> listMap = new HashMap<String, List <ScheduleRealInfo>>();
			int jhbc = 0;
			int dftz = 0;
			int jhcc = 0;
			int sjcc = 0;
			int upfk = 0;
			int updk = 0;
			int dnfk = 0;
			int dndk = 0;
			int upfm = 0;
			int updm = 0;
			int dnfm = 0;
			int dndm = 0;
			int jhsb = 0;
			int sjsb = 0;
			int jhmb = 0;
			int sjmb = 0;
			int jhzgf = 0;
			int sjzgf = 0;
			int jhwgf = 0;
			int sjwgf = 0;
			for(ScheduleRealInfo schedule : keyMap.get(key)){
				schedule.setFcsjAll(schedule.getFcsj());
				
				//早晚高峰时段执行率
				if(schedule.getFcsjT() >= zgfBegin && schedule.getFcsjT() <= zgfEnd){
					jhzgf++;
					if(schedule.getFcsjActual() != null)
						sjzgf++;
				}
				if(schedule.getFcsjT() >= wgfBegin && schedule.getFcsjT() <= wgfEnd){
					jhwgf++;
					if(schedule.getFcsjActual() != null)
						sjwgf++;
				}
				
				//班次数和出场数
				jhbc++;
				if(schedule.getBcType().equals("out"))
					jhcc++;

				//发出时间快慢
				if(schedule.getFcsjActual() != null){
					if(schedule.getBcType().equals("out"))
						sjcc++;
					schedule.setFcsjActualAll(schedule.getFcsjActual());
					if(schedule.getFcsjActualTime() - schedule.getFcsjT() < -60000){
						if(schedule.getXlDir().equals("0"))
							upfk++;
						else if (schedule.getXlDir().equals("1"))
							dnfk++;
					} else if(schedule.getFcsjActualTime() - schedule.getFcsjT() > 180000){
						if(schedule.getXlDir().equals("0"))
							upfm++;
						else if (schedule.getXlDir().equals("1"))
							dnfm++;
					}
				}
				//到站时间快慢
				if(schedule.getZdsjActual() != null && schedule.getBcsj() != null){
					schedule.setZdsjActualAll(schedule.getZdsjActual());
					schedule.setDfsjAll(schedule.getDfsj());
					schedule.calcEndTime();
					if(schedule.getZdsjActualTime() - schedule.getZdsjT() < -60000){
						if(schedule.getXlDir().equals("0"))
							updk++;
						else if (schedule.getXlDir().equals("1"))
							dndk++;
					} else if(schedule.getZdsjActualTime() - schedule.getZdsjT() > 180000){
						if(schedule.getXlDir().equals("0"))
							updm++;
						else if (schedule.getXlDir().equals("1"))
							dndm++;
					}
				}
				
				//待发调整
				if(schedule.getDfsj() != null){
					if(!schedule.getDfsj().equals(schedule.getFcsj())){
						dftz++;
					}
				}
				
				if(schedule.getBcType().equals("normal")){
					String clZbh = schedule.getClZbh();
					if(!listMap.containsKey(clZbh))
						listMap.put(clZbh, new ArrayList<ScheduleRealInfo>());
					listMap.get(clZbh).add(schedule);
				}
			}

			//求首末班准点率
			for(String clZbh : listMap.keySet()){
				Map<Integer, ScheduleRealInfo> temp = new HashMap<Integer, ScheduleRealInfo>();
				List <ScheduleRealInfo> tempList = new ArrayList<ScheduleRealInfo>();
				List<Integer> sort = new ArrayList<Integer>();
				for(ScheduleRealInfo schedule : listMap.get(clZbh)){
					String[] split = schedule.getFcsj().split(":");
					int min = Integer.valueOf(split[0])*60 + Integer.valueOf(split[1]);
					temp.put(min, schedule);
					sort.add(min);
				}
				Collections.sort(sort);
				for(Integer i : sort){
					tempList.add(temp.get(i));
				}
				ScheduleRealInfo shouban = tempList.get(0);
				ScheduleRealInfo moban = tempList.get(tempList.size() - 1);
				if(shouban.getFcsjActual() != null){
					jhsb++;
					if(shouban.getFcsjActualTime() - shouban.getFcsjT() >= -60000 && shouban.getFcsjActualTime() - shouban.getFcsjT() <= 180000)
						sjsb++;
				}
				if(moban.getFcsjActual() != null){
					jhmb++;
					if(moban.getFcsjActualTime() - moban.getFcsjT() >= -60000 && moban.getFcsjActualTime() - moban.getFcsjT() <= 180000)
						sjmb++;
				}
				
			}
			
			tempMap.put("date", date);
			tempMap.put("line", key);
			tempMap.put("jhbc", jhbc);
			tempMap.put("dftz", dftz);
			tempMap.put("dftzl", df.format(((float)dftz/jhbc)*100) + "%");
			tempMap.put("ccl", df.format(((float)sjcc/jhcc)*100) + "%");
			tempMap.put("upfk", upfk);
			tempMap.put("updk", updk);
			tempMap.put("dnfk", dnfk);
			tempMap.put("dndk", dndk);
			tempMap.put("upfm", upfm);
			tempMap.put("updm", updm);
			tempMap.put("dnfm", dnfm);
			tempMap.put("dndm", dndm);
			tempMap.put("wdzs", (upfk + updk + dnfk + dndk) + "/" + (upfm + updm + dnfm + dndm));
			tempMap.put("smbzdl", (jhsb==0?"100%":df.format(((float)sjsb/jhsb)*100)+"%")+"/"+(jhmb==0?"100%":df.format(((float)sjmb/jhmb)*100)+"%"));
			tempMap.put("gfbczxl", (jhzgf==0?"0%":df.format(((float)sjzgf/jhzgf)*100)+"%")+"/"+(jhwgf==0?"0%":df.format(((float)sjwgf/jhwgf)*100)+"%"));
			resList.add(tempMap);
		}
		
		if(type.equals("export")){
			SimpleDateFormat sdfMonth = new SimpleDateFormat("yyyy-MM-dd"),
					sdfSimple = new SimpleDateFormat("yyyyMMdd");
			List<Iterator<?>> listI = new ArrayList<Iterator<?>>();
			Map<String,Object> map = new HashMap<String, Object>();
			ReportUtils ee = new ReportUtils();
			try {
				listI.add(resList.iterator());
				String path = this.getClass().getResource("/").getPath()+"static\\pages\\forms\\";
				ee.excelReplace(listI, new Object[] { map }, path+"mould\\workDaily.xls",
						path+"export\\营运服务日报表" + sdfSimple.format(sdfMonth.parse(date)) + ".xls");
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		
		return resList;
	}

	@Override
	public Map<String, Object> scheduleAnaly(String page, String line, String startDate, String endDate, String model, String type) {
		DecimalFormat df = new DecimalFormat("00");
		List<TTInfoDetail> ttList = new ArrayList<TTInfoDetail>();
		List<ScheduleRealInfo> list = new ArrayList<ScheduleRealInfo>();
		List<Map<String, Object>> resList = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> tempList = new ArrayList<Map<String, Object>>();
		Map<String, List<ScheduleRealInfo>> keyMap = new HashMap<String, List<ScheduleRealInfo>>();
		Map<String, Object> modelMap = new HashMap<String, Object>();
		
		if(startDate.length() == 0){
			startDate = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		}
		if(endDate.length() == 0){
			endDate = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		}
		try {

			String sql = "select * from bsth_c_s_sp_info_real where DATE_FORMAT(schedule_date,'%Y-%m-%d') >= '"+startDate+"' and DATE_FORMAT(schedule_date,'%Y-%m-%d') <= '"+endDate+"'";
			if(line.length() != 0){
				sql += " and xl_bm = '"+line+"'";
			}
			sql += " and bc_type = 'normal'";
			
			list =jdbcTemplate.query(sql,
					new RowMapper<ScheduleRealInfo>(){
				@Override
				public ScheduleRealInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
					ScheduleRealInfo schedule = new ScheduleRealInfo();
					schedule.setXlName(rs.getString("xl_name"));
					schedule.setBcType(rs.getString("bc_type"));
					schedule.setBcs(rs.getInt("bcs"));
					schedule.setFcsj(rs.getString("fcsj"));
					schedule.setFcsjActual(rs.getString("fcsj_actual"));
					schedule.setZdsj(rs.getString("zdsj"));
					schedule.setZdsjActual(rs.getString("zdsj_actual"));
					schedule.setBcsj(rs.getInt("bcsj"));
					schedule.setQdzName(rs.getString("qdz_name"));
					return schedule;
				}
			});
			
			if(model.length() != 0){
				sql = "select * from bsth_c_s_ttinfo_detail where ttinfo = '"+model+"' and bc_type = 'normal'";
			}
			
			ttList =jdbcTemplate.query(sql,
					new RowMapper<TTInfoDetail>(){
				@Override
				public TTInfoDetail mapRow(ResultSet rs, int rowNum) throws SQLException {
					TTInfoDetail ttInfo = new TTInfoDetail();
					ttInfo.setBcType(rs.getString("bc_type"));
					ttInfo.setBcs(rs.getInt("bcs"));
					ttInfo.setFcsj(rs.getString("fcsj"));
					ttInfo.setBcsj(rs.getInt("bcsj"));
					return ttInfo;
				}
			});
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//分组计算 同时判断是否所选时刻信息
		for(ScheduleRealInfo schedule : list){
			if(model.length() != 0){
				for(TTInfoDetail tt : ttList){
					if(tt.getBcs() == schedule.getBcs() && tt.getFcsj().equals(schedule.getFcsj())
							&& tt.getBcsj() == schedule.getBcsj()){
						String key = schedule.getXlName()+"/"+schedule.getQdzName()+"/"+schedule.getFcsj();
						if(!keyMap.containsKey(key))
							keyMap.put(key, new ArrayList<ScheduleRealInfo>());
						keyMap.get(key).add(schedule);
						continue;
					}
				}
			} else {
				String key = schedule.getXlName()+"/"+schedule.getQdzName()+"/"+schedule.getFcsj();
				if(!keyMap.containsKey(key))
					keyMap.put(key, new ArrayList<ScheduleRealInfo>());
				keyMap.get(key).add(schedule);
			}
		}
		for(String key : keyMap.keySet()){
			Map<String, Object> tempMap = new HashMap<String, Object>();
			List<Integer> fcsj = new ArrayList<Integer>();
			List<Integer> yssj = new ArrayList<Integer>();
			int jhbc = 0;
			int sjbc = 0;
			for(ScheduleRealInfo schedule : keyMap.get(key)){
				tempMap.put("bcsj", schedule.getBcsj());
				jhbc++;
				if(schedule.getZdsjActual() != null && schedule.getFcsjActual() != null){
					sjbc++;
					String[] fcsj_ = schedule.getFcsjActual().split(":");
					String[] zdsj_ = schedule.getZdsjActual().split(":");
					int fcsj_m = (Integer.valueOf(fcsj_[0]) * 60) + Integer.valueOf(fcsj_[1]);
					int zdsj_m = (Integer.valueOf(zdsj_[0]) * 60) + Integer.valueOf(zdsj_[1]);
					fcsj.add(fcsj_m);
					yssj.add(zdsj_m - fcsj_m);
				}
			}
			int zzfc = 0;
			int zwfc = 0;
			int pjfc = 0;
			int fcSum = 0;
			int zkys = 0;
			int zmys = 0;
			int pjys = 0;
			int ysSum = 0;
			for(int i : fcsj){
				fcSum += i;
				if(zzfc == 0 || zzfc > i)
					zzfc = i;
				if(zwfc == 0 || zwfc < i)
					zwfc = i;
			}
			for(int i : yssj){
				ysSum += i;
				if(zkys == 0 || zkys > i)
					zkys = i;
				if(zmys == 0 || zmys < i)
					zmys = i;
			}
			if(fcsj.size() != 0){
				pjfc = fcSum / fcsj.size();
				tempMap.put("zzfc", df.format(zzfc/60)+":"+df.format(zzfc%60));
				tempMap.put("zwfc", df.format(zwfc/60)+":"+df.format(zwfc%60));
				tempMap.put("pjfc", df.format(pjfc/60)+":"+df.format(pjfc%60));
			} else {
				tempMap.put("zzfc", "/");
				tempMap.put("zwfc", "/");
				tempMap.put("pjfc", "/");
			}
			if(yssj.size() != 0){
				pjys = ysSum / yssj.size();
				tempMap.put("zkys", zkys);
				tempMap.put("zmys", zmys);
				tempMap.put("pjys", pjys);
			} else {
				tempMap.put("zkys", "/");
				tempMap.put("zmys", "/");
				tempMap.put("pjys", "/");
			}
			String[] split = key.split("/");
			tempMap.put("line", split[0]);
			tempMap.put("qdz", split[1]);
			tempMap.put("jhfc", split[2]);
			tempMap.put("jhbc", jhbc);
			tempMap.put("sjbc", sjbc);
			tempList.add(tempMap);
		}
		
		//排序
		Map<String, List<Map<String, Object>>> listMap = new HashMap<String, List<Map<String, Object>>>();
		Map<String, List<Map<String, Object>>> listMap2 = new HashMap<String, List<Map<String, Object>>>();
		for(Map<String, Object> m : tempList){
			String key = m.get("line").toString() + m.get("qdz");
			if(!listMap.containsKey(key))
				listMap.put(key, new ArrayList<Map<String, Object>>());
			listMap.get(key).add(m);
		}
		for(String key : listMap.keySet()){
			Map<Integer, Map<String, Object>> tempMap = new HashMap<Integer, Map<String,Object>>();
			List<Integer> keyList = new ArrayList<Integer>();
			for(Map<String, Object> m : listMap.get(key)){
				String[] split = m.get("jhfc").toString().split(":");
				int jhfc = Integer.valueOf(split[0])*60 + Integer.valueOf(split[1]);
				tempMap.put(jhfc, m);
				keyList.add(jhfc);
			}
			Collections.sort(keyList);
			for(Integer Int : keyList){
				Map<String, Object> map = tempMap.get(Int);
				String str = map.get("line").toString();
				if(!listMap2.containsKey(str))
					listMap2.put(str, new ArrayList<Map<String, Object>>());
				listMap2.get(str).add(map);
			}
		}
		for(String str : listMap2.keySet()){
			for(Map<String, Object> map : listMap2.get(str)){
				resList.add(map);
			}
		}
		
		//分页
		if(page.length() != 0 && type.equals("query")){
			int currPage = Integer.valueOf(page);
			modelMap.put("totalPage", resList.size()%10>0?(resList.size()/10 + 1):resList.size()/10);
			modelMap.put("dataList", resList.subList(currPage*10,((currPage+1)*10)>=resList.size()?resList.size():(currPage+1)*10));
		}
		
		//导出
		if(type.equals("export")){
			SimpleDateFormat sdfMonth = new SimpleDateFormat("yyyy-MM-dd"),
					sdfSimple = new SimpleDateFormat("yyyyMMdd");
			List<Iterator<?>> listI = new ArrayList<Iterator<?>>();
			Map<String,Object> map = new HashMap<String, Object>();
			ReportUtils ee = new ReportUtils();
			try {
				listI.add(resList.iterator());
				String path = this.getClass().getResource("/").getPath()+"static\\pages\\forms\\";
				ee.excelReplace(listI, new Object[] { map }, path+"mould\\scheduleAnaly.xls",
						path+"export\\时刻表分析" + sdfSimple.format(sdfMonth.parse(startDate)) + "-" + sdfSimple.format(sdfMonth.parse(endDate)) + ".xls");
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		
		return modelMap;
	}

	@Override
	public List<Map<String, Object>> getModel(String line, String startDate, String endDate) {
		List<Map<String, Object>> resList = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat sdfEE = new SimpleDateFormat("EEEE");
		
		if(startDate.length() == 0){
			startDate = sdf.format(new Date());
		}
		if(endDate.length() == 0){
			endDate = sdf.format(new Date());
		}
		try {
			
			String sql = "select tt.id, tt.name, qyrq, tt.rule_days, tt.special_days from bsth_c_s_ttinfo tt left join bsth_c_line cl on cl.id = tt.xl where tt.is_cancel = 0 and tt.is_enable_dis_template = 1";
			if(line.length() != 0){
				sql += " and cl.line_code = '"+line+"'";
			}
			sql += " order by tt.create_date desc";
			
			list = jdbcTemplate.query(sql,
					new RowMapper<Map<String, Object>>(){
				@Override
				public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
					Map<String, Object> map = new HashMap<String, Object>();
					map.put("id", rs.getString("id"));
					map.put("name", rs.getString("name"));
					map.put("qyrq", rs.getString("qyrq"));
					map.put("ruleDays", rs.getString("rule_days"));
					map.put("specialDays", rs.getString("special_days"));
					return map;
				}
			});
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		Date date1 = null;
		Date date2 = null;
		try {
			date1 = sdf.parse(startDate);
			date2 = sdf.parse(endDate);
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		Set<Integer> ruleDays1 = new HashSet<Integer>();
		List<String> specialDays1 = new ArrayList<String>();
		for(int i = 0; ; i++){
			Date tempDate = new Date(date1.getTime() + i*1000*60*60*24);
			if(tempDate.getTime() > date2.getTime())
				break;
			String week = sdfEE.format(tempDate);
			if(week.equals("星期一"))
				ruleDays1.add(0);
			else if(week.equals("星期二"))
				ruleDays1.add(1);
			else if(week.equals("星期三"))
				ruleDays1.add(2);
			else if(week.equals("星期四"))
				ruleDays1.add(3);
			else if(week.equals("星期五"))
				ruleDays1.add(4);
			else if(week.equals("星期六"))
				ruleDays1.add(5);
			else if(week.equals("星期日"))
				ruleDays1.add(6);
			specialDays1.add(sdf.format(tempDate));
		}
		for(Map<String, Object> map : list){
			String[] ruleDays = map.get("ruleDays").toString().split(",");
			String[] specialDays = map.get("specialDays").toString().split(",");
			boolean flag = false;
			DO:{
				try {
					long qyrq = sdf.parse(map.get("qyrq").toString()).getTime();
					if(qyrq > date2.getTime())
						break;
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				for(int i = 0; i < ruleDays.length; i++){
					if(ruleDays[i].equals("1")){
						if(ruleDays1.contains(i)){
							flag = true;
							break DO;
						}
					}
				}
				for(int i = 0; i < specialDays.length; i++){
					for(int j = 0; j < specialDays1.size(); j++){
						if(specialDays[i].equals(specialDays1.get(j))){
							flag = true;
							break DO;
						}
					}
				}
			}
			if(flag)
				resList.add(map);
		}
		
		return resList;
	}

	@Override
	public List<Map<String, Object>> firstAndLastBus(String line, String date, String type) {
		List<ScheduleRealInfo> list = new ArrayList<ScheduleRealInfo>();
		List<Map<String, Object>> resList = new ArrayList<Map<String, Object>>();
		Map<String, List<ScheduleRealInfo>> keyMap = new HashMap<String, List<ScheduleRealInfo>>();
		
		if(date.length() == 0)
			date = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		if(line.length() != 0)
			list = scheduleRealInfoRepository.scheduleDaily(line, date);
		else 
			list = scheduleRealInfoRepository.findByDate(date);
		
		for(ScheduleRealInfo schedule : list){
			if(!schedule.getBcType().equals("normal"))
				continue;
			String key = schedule.getXlName();
			if(!keyMap.containsKey(key))
				keyMap.put(key, new ArrayList<ScheduleRealInfo>());
			keyMap.get(key).add(schedule);
		}
		for(String key : keyMap.keySet()){
			Map<String, Object> tempMap = new HashMap<String, Object>();
			Map<Long, ScheduleRealInfo> temp0 = new HashMap<Long, ScheduleRealInfo>();
			List<Long> longList0 = new ArrayList<Long>();
			Map<Long, ScheduleRealInfo> temp1 = new HashMap<Long, ScheduleRealInfo>();
			List<Long> longList1 = new ArrayList<Long>();
			for(ScheduleRealInfo schedule : keyMap.get(key)){
				String[] split = schedule.getFcsj().split(":");
				long min = Integer.valueOf(split[0])*60 + Integer.valueOf(split[1]);
				schedule.setFcsjT(min);
				if(schedule.getXlDir().equals("0")){
					temp0.put(min, schedule);
					longList0.add(min);
				} else {
					temp1.put(min, schedule);
					longList1.add(min);
				}
			}
			Collections.sort(longList0);
			Collections.sort(longList1);
			ScheduleRealInfo shouban0 = temp0.get(longList0.get(0));
			ScheduleRealInfo moban0 = temp0.get(longList0.get(longList0.size() - 1));
			ScheduleRealInfo shouban1 = temp1.get(longList1.get(0));
			ScheduleRealInfo moban1 = temp1.get(longList1.get(longList1.size() - 1));
			tempMap.put("date", date);
			tempMap.put("line", key);
			tempMap.put("qdzFirst0", shouban0.getQdzName());
			tempMap.put("jhfcFirst0", shouban0.getFcsj());
			tempMap.put("qdzLast0", moban0.getQdzName());
			tempMap.put("jhfcLast0", moban0.getFcsj());
			tempMap.put("qdzFirst1", shouban1.getQdzName());
			tempMap.put("jhfcFirst1", shouban1.getFcsj());
			tempMap.put("qdzLast1", moban1.getQdzName());
			tempMap.put("jhfcLast1", moban1.getFcsj());
			if(shouban0.getFcsjActual() != null){
				String[] split = shouban0.getFcsjActual().split(":");
				long min = Integer.valueOf(split[0])*60 + Integer.valueOf(split[1]); 
				long delay = shouban0.getFcsjT() - min;
				tempMap.put("sjfcFirst0", shouban0.getFcsjActual());
				if(delay > 0)
					tempMap.put("delayFirst0", "+" + delay);
				else
					tempMap.put("delayFirst0", delay);
			} else {
				tempMap.put("sjfcFirst0", "/");
				tempMap.put("delayFirst0", "/");
			}
			if(moban0.getZdsjActual() != null){
				String[] split = moban0.getFcsjActual().split(":");
				long min = Integer.valueOf(split[0])*60 + Integer.valueOf(split[1]);
				long delay = moban0.getFcsjT() - min;
				tempMap.put("sjfcLast0", moban0.getFcsjActual());
				if(delay > 0)
					tempMap.put("delayLast0", "+" + delay);
				else
					tempMap.put("delayLast0", delay);
			} else {
				tempMap.put("sjfcLast0", "/");
				tempMap.put("delayLast0", "/");
			}
			if(shouban1.getFcsjActual() != null){
				String[] split = shouban1.getFcsjActual().split(":");
				long min = Integer.valueOf(split[0])*60 + Integer.valueOf(split[1]); 
				long delay = shouban1.getFcsjT() - min;
				tempMap.put("sjfcFirst1", shouban1.getFcsjActual());
				if(delay > 0)
					tempMap.put("delayFirst1", "+" + delay);
				else
					tempMap.put("delayFirst1", delay);
			} else {
				tempMap.put("sjfcFirst1", "/");
				tempMap.put("delayFirst1", "/");
			}
			if(moban1.getZdsjActual() != null){
				String[] split = moban1.getFcsjActual().split(":");
				long min = Integer.valueOf(split[0])*60 + Integer.valueOf(split[1]);
				long delay = moban1.getFcsjT() - min;
				tempMap.put("sjfcLast1", moban1.getFcsjActual());
				if(delay > 0)
					tempMap.put("delayLast1", "+" + delay);
				else
					tempMap.put("delayLast1", delay);
			} else {
				tempMap.put("sjfcLast1", "/");
				tempMap.put("delayLast1", "/");
			}
			resList.add(tempMap);
		}
		
		if(type.equals("export")){
			SimpleDateFormat sdfMonth = new SimpleDateFormat("yyyy-MM-dd"),
					sdfSimple = new SimpleDateFormat("yyyyMMdd");
			List<Iterator<?>> listI = new ArrayList<Iterator<?>>();
			Map<String,Object> map = new HashMap<String, Object>();
			ReportUtils ee = new ReportUtils();
			try {
				listI.add(resList.iterator());
				String path = this.getClass().getResource("/").getPath()+"static\\pages\\forms\\";
				ee.excelReplace(listI, new Object[] { map }, path+"mould\\firstAndLastBus.xls",
						path+"export\\线路首末班" + sdfSimple.format(sdfMonth.parse(date)) + ".xls");
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		
		return resList;
	}

	@Override
	public List<Map<String, Object>> commandState(String line, String date, String code) {
		List<Map<String, Object>> resList = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Map<String, List<Map<String, Object>>> keyMap = new HashMap<String, List<Map<String, Object>>>();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");
		
		if(date.length() == 0)
			date = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		
		try {
			
			String sql = "select r.id, r.schedule_date_str, r.xl_name, r.cl_zbh, r.j_gh, r.j_name, r.fcsj, d.timestamp, d.reply46, d.reply47, d.reply46time, d.reply47time " +
					"FROM bsth_c_s_sp_info_real as r left join bsth_v_directive_60 as d on r.id = d.sch and d.is_dispatch = 1 where DATE_FORMAT(schedule_date,'%Y-%m-%d') = '"+date+"'";
			if(line.length() != 0){
				sql += " and xl_bm = '"+line+"'";
			}
			if(code.length() != 0){
				sql += " and cl_zbh = '"+code+"'";
			}
			sql += " union " +
					"select r.id, r.schedule_date_str, r.xl_name, r.cl_zbh, r.j_gh, r.j_name, r.fcsj, d.timestamp, d.reply46, d.reply47, d.reply46time, d.reply47time " +
					"FROM bsth_c_s_sp_info_real as r right join bsth_v_directive_60 as d on r.id = d.sch where d.is_dispatch = 1 and DATE_FORMAT(schedule_date,'%Y-%m-%d') = '"+date+"'";
			if(line.length() != 0){
				sql += " and xl_bm = '"+line+"'";
			}
			if(code.length() != 0){
				sql += " and cl_zbh = '"+code+"'";
			}
			sql += " order by fcsj";
			
			list = jdbcTemplate.query(sql,
					new RowMapper<Map<String, Object>>(){
				@Override
				public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
					Map<String, Object> map = new HashMap<String, Object>();
					map.put("id", rs.getString("id"));
					map.put("date", rs.getString("schedule_date_str"));
					map.put("line", rs.getString("xl_name"));
					map.put("clZbh", rs.getString("cl_zbh"));
					map.put("jGh", rs.getString("j_gh"));
					map.put("jName", rs.getString("j_name"));
					map.put("fcsj", rs.getString("fcsj"));
					map.put("timestamp", rs.getString("timestamp"));
					map.put("reply46", rs.getString("reply46"));
					map.put("reply47", rs.getString("reply47"));
					map.put("reply46time", rs.getString("reply46time"));
					map.put("reply47time", rs.getString("reply47time"));
					return map;
				}
			});
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		for(Map<String, Object> map : list){
			String key = map.get("line") + "/" + map.get("clZbh") + "/" + map.get("jGh") + "/" + map.get("jName");
			if(!keyMap.containsKey(key))
				keyMap.put(key, new ArrayList<Map<String, Object>>());
			keyMap.get(key).add(map);
		}
		for(String key : keyMap.keySet()){
			Map<String, Object> tempMap = new HashMap<String, Object>();
			Set<String> tempSet = new HashSet<String>();
			int sjf = 0;
			int wqr = 0;
			for(Map<String, Object> map : keyMap.get(key)){
				tempSet.add(map.get("id").toString());
				if(map.get("timestamp") != null){
					sjf++;
					if(map.get("reply47").toString().equals("-1"))
						wqr++;
					map.put("time", sdf.format(new Date(Long.valueOf(map.get("timestamp").toString()))));
				} else 
					map.put("time", "/");
				
				if(map.get("reply46time") != null)
					map.put("time46", sdf.format(new Date(Long.valueOf(map.get("reply46time").toString()))));
				else 
					map.put("time46", "/");
				
				if(map.get("reply47time") != null)
					map.put("time47", sdf.format(new Date(Long.valueOf(map.get("reply47time").toString()))));
				else 
					map.put("time47", "/");
				
			}
			String[] split = key.split("/");
			tempMap.put("date", date);
			tempMap.put("line", split[0]);
			tempMap.put("clZbh", split[1]);
			tempMap.put("jsy", split[2] + "/" + split[3]);
			tempMap.put("jhf", tempSet.size());
			tempMap.put("sjf", sjf);
			tempMap.put("wqr", wqr);
			tempMap.put("workList", keyMap.get(key));
			resList.add(tempMap);
		}
		
		return resList;
	}

}