潘钊 / bsth_control · Files

package com.bsth.service.excep.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.data.BasicData;
import com.bsth.entity.excep.Offline;
import com.bsth.entity.oil.Ylb;
import com.bsth.entity.realcontrol.ScheduleRealInfo;
import com.bsth.service.excep.OfflineService;
import com.bsth.util.EchartConver;
import com.bsth.util.PageHelper;
import com.bsth.util.PageObject;
import com.bsth.util.db.DBUtils_MS;
import com.github.abel533.echarts.Option;
import com.google.gson.Gson;

@Service
public class OfflineServiceImpl implements OfflineService {

	@Autowired
	JdbcTemplate jdbcTemplate;
	public List<Offline> findGPS(Map<String, Object> map) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Offline> list=new ArrayList<Offline>();
		String days_year=map.get("days_year").toString();
		String line_id=map.get("line_id").toString();
		String device_id=map.get("device_id").toString();
		String ts=map.get("ts").toString();
		String sql="SELECT t.line_id,t.device_id,MIN(ts) as ts,days_year FROM bsth_c_gps_info t "
				+ "  WHERE t.line_id='"+line_id+"' and t.device_id='"+device_id+"' and t.days_year='"+days_year+"' and t.ts>='"+ts+"' "
				+ "  GROUP BY  t.device_id,t.line_id ";
		sql +=" order by ts ";
		try {
			conn = DBUtils_MS.getConnection();
			ps = conn.prepareStatement(sql);
//			ps.setInt(1,0*10); // O-最大条数  -- M-起始条数
//			ps.setInt(2, 1000000); // O-最小条数 -- M-显示条数
			rs = ps.executeQuery();
			list = resultSet(rs);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtils_MS.close(rs, ps, conn);
		}
		
		return list;
	}
	
 //	@Override
	static List<Offline> findAll(Map<String, Object> map) {
		// TODO Auto-generated method stub
		
		//查询条件
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int page=Integer.parseInt(map.get("page").toString());
		List<Offline> list=new ArrayList<Offline>();
		/*
		String sql="SELECT * FROM (SELECT A.*,ROWNUM R FROM (  "
				+ "select * from bsth_c_offline) A "
				+ "WHERE ROWNUM <= ? ) B WHERE R >?";*/
		String sql="select * from bsth_c_offline where 1=1 ";
		Object line=map.get("line");
		Object nbbm=map.get("nbbm");
		Object updown=map.get("updown");
		Object date=map.get("date");

		if(line!=null){
			sql +=" and line like'%"+line.toString().trim()+"%'";
		}
		
		if(nbbm!=null){
			 nbbm=BasicData.deviceId2NbbmMap.inverse().get(nbbm);
			 if(nbbm!=null)
			sql +=" and vehicle like '%"+nbbm+"%'";
		}
		
		if(updown!=null){
			sql +="and up_down like '%"+updown.toString()+"%'";
		}
		if(date!=null){
			if (date.toString().length()>0) {
				SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				try {
					Long t1=sdf.parse(date.toString()+" 00:00:00").getTime();
					Long t2=sdf.parse(date.toString()+" 23:59:59").getTime();
					sql += " and timestamp >="+t1 +" and timestamp <="+t2;
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			
		}
		sql +=" order by id limit ?,?";

		try {
			conn = DBUtils_MS.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, page*10); // O-最大条数  -- M-起始条数
			ps.setInt(2, 10); // O-最小条数 -- M-显示条数
			rs = ps.executeQuery();
			list = resultSet2Set(rs);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtils_MS.close(rs, ps, conn);
		}
		
		return list;
	}
 public  List<Offline> resultSet(ResultSet rs) throws SQLException{
	// TODO Auto-generated method stub
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			List<Offline> list=new ArrayList<Offline>();
			Offline offline;
			while(rs.next()){
				offline=new Offline();
				offline.setTs(Long.parseLong(rs.getObject("ts").toString()));
				list.add(offline);
			}
			return list;
 }
	/**
	 * 数据转换
	 */
//	@Override
	static List<Offline> resultSet2Set(ResultSet rs) throws SQLException{
		// TODO Auto-generated method stub
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		List<Offline> list=new ArrayList<Offline>();
		Offline offline;
		while(rs.next()){
			offline=new Offline();
			offline.setId(Integer.valueOf(rs.getObject("id").toString()));
			offline.setLat(Float.valueOf(rs.getObject("lat").toString()));
			offline.setLine(Integer.valueOf(rs.getObject("line").toString()));
			//run 时注解
			offline.setLineName(BasicData.lineCode2NameMap.get(offline.getLine().toString()));
			offline.setLon(Float.valueOf(rs.getObject("lon").toString()));
			offline.setTimestamp(Long.valueOf(rs.getObject("timestamp").toString()));
			offline.setTimestampDate(sdf.format(new Date(offline.getTimestamp())));
			offline.setUpDown(Integer.valueOf(rs.getObject("up_down").toString()));
			offline.setVehicle(BasicData.deviceId2NbbmMap.get(rs.getObject("vehicle").toString()));
			offline.setDeviceid(rs.getObject("vehicle").toString());
			list.add(offline);
		}
		return list;
	}
	
	
	@SuppressWarnings("unchecked")
	public PageObject <Offline> Pagequery(Map<String, Object> map) {
		// TODO Auto-generated method stub

		String sql="select count(*) record  from bsth_c_offline where 1=1 ";
		
		Object line=map.get("line");
		Object nbbm=map.get("nbbm");
		Object updown=map.get("updown");
		
		Object date=map.get("date");

		if(line!=null){
			sql +=" and line like'%"+line.toString().trim()+"%'";
		}
		
		if(nbbm!=null){
			nbbm=BasicData.deviceId2NbbmMap.inverse().get(nbbm);
			if(nbbm!=null)
			sql +=" and vehicle like '%"+nbbm+"%'";
		}
		
		if(updown!=null){
			sql +="and up_down like '%"+updown.toString()+"%'";
		}
		if(date!=null){
			if (date.toString().length()>0) {
				SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				try {
					Long t1=sdf.parse(date.toString()+" 00:00:00").getTime();
					Long t2=sdf.parse(date.toString()+" 23:59:59").getTime();
					sql += " and timestamp >="+t1 +" and timestamp <="+t2;
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			
		}
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int totalData = 0;

		try {
			conn = DBUtils_MS.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			 if(rs.next()){
				 totalData=rs.getInt("record");
			 }		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtils_MS.close(rs, ps, conn);
		}
		PageHelper pageHelper = new PageHelper(totalData, map);
//		List<T> list = this.baseDao.queryByObject(pageHelper.getMap());
		List<Offline> list=findAll(pageHelper.getMap());
		for (int i = 0; i < list.size(); i++) {
			int lineid=	list.get(i).getLine();
			String nbbm2=list.get(i).getVehicle();
			String deviceid=list.get(i).getDeviceid();
			Long d1=list.get(i).getTimestamp();
			Date datess = new Date(d1);
			String dates=new SimpleDateFormat("yyyy-MM-dd").format(datess);
			String sk=new SimpleDateFormat("HH:mm").format(datess);
			String sqlPbb="SELECT * FROM ( select lp_name,cl_zbh,j_gh,j_name,MIN(fcsj) as fcsj,MAX(zdsj) as ddsj"
					+ "  from bsth_c_s_sp_info_real where cl_zbh='"+nbbm2+"'  and"
					+ "   real_exec_date='"+dates+"' GROUP BY cl_zbh,lp_name,j_gh,j_name ) t WHERE t.fcsj<='"+sk+"' AND t.ddsj>='"+sk+"' " ;
			List<ScheduleRealInfo> pbb =jdbcTemplate.query(sqlPbb,  
			        new RowMapper<ScheduleRealInfo>(){  
			            @Override  
			            public ScheduleRealInfo  mapRow(ResultSet rs, int rowNum) throws SQLException {  
			            	ScheduleRealInfo s  = new ScheduleRealInfo(); 
			            	s.setjGh(rs.getString("j_gh"));
			            	s.setjName(rs.getString("j_name"));
			            	s.setLpName(rs.getString("lp_name"));
			                return s;  
			            }  
					});
			Map<String, Object> gpsmap=new HashMap<>();
			String str = String.format("%tj",datess);
			gpsmap.put("device_id",deviceid );
			gpsmap.put("line_id", lineid);
			gpsmap.put("days_year", str);
			gpsmap.put("ts", d1);
		 List<Offline> gps=findGPS(gpsmap);
	   if(gps.size()>0){
		  Long ts= gps.get(0).getTs();
		String ts1 =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date(ts));
		   list.get(0).setTsdate(ts1);
	   }
		if(pbb.size()>0){
					list.get(i).setJsy(pbb.get(0).getjGh()+"/"+pbb.get(0).getjName());
					list.get(i).setLpname(pbb.get(0).getLpName());
				}
		
		}
         
		PageObject<Offline> pageObject = pageHelper.getPageObject();
				pageObject.setDataList(list);
	
		return pageObject;
	}
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Map<String, Object> map=new HashMap<String,Object>();
		map.put("pageData","20");
		map.put("curPage", "1");
				String sql="select count(*) record  from bsth_c_offline";
				Connection conn = null;
				PreparedStatement ps = null;
				ResultSet rs = null;
				int totalData = 0;

				try {
					conn = DBUtils_MS.getConnection();
					ps = conn.prepareStatement(sql);
					rs = ps.executeQuery();
					 if(rs.next()){
						 totalData=rs.getInt("record");
					 }		
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally {
					DBUtils_MS.close(rs, ps, conn);
				}
				PageHelper pageHelper = new PageHelper(totalData, map);
//				List<T> list = this.baseDao.queryByObject(pageHelper.getMap());
				List<Offline> list=findAll(pageHelper.getMap());
				PageObject<Offline> pageObject = pageHelper.getPageObject();
				pageObject.setDataList(list);
		System.out.println("");
	}

	@Override
	public Map<String, Object> getReport(Map<String, Object> map) {
		// TODO Auto-generated method stub
		String chartStr = "";
		Map<String, Object> mapValue = new HashMap<String, Object>();
		try {
			String xAxisName = map.get("xAxisName").toString();
			String legendName = map.get("legendName").toString();
			String xAxisItem = "";
			String xAxisOrderBy = "";
			String legendItem = "";
			String legendOrderBy = "";
			if(xAxisName!=null){
				String [] xaxis = xAxisName.split(":");
				if(xaxis.length>1 && xaxis[1].toLowerCase().contains("date")){					
					//本来还要判断下日期格式是否标准
					xAxisItem = "to_char("+xaxis[0]+",'"+xaxis[2]+"') ";
					xAxisOrderBy = (xaxis.length>3?xaxis[3]:"");
				}else{
					xAxisItem = xaxis[0];
				}
			}
			if(legendName!=null){
				String [] legend = legendName.split(":");
				if(legend.length>1 && legend[1].toLowerCase().contains("date")){					
					//本来还要判断下日期格式是否标准					
					legendItem = "to_char("+legend[0]+",'"+legend[2]+"') ";
					legendOrderBy = (legend[3].length()>3?legend[3]:"");
				}else{
					legendItem = legend[0];
				}
			}			
			map.put("xAxisItem", xAxisItem);
			map.put("xAxisOrderBy", xAxisOrderBy);
			map.put("legendItem", legendItem);
			map.put("legendOrderBy", legendOrderBy);
			List dataList = new ArrayList();
			dataList =getReportList(map);
			EchartConver echart = new EchartConver(map);
			Option option = echart.getOption(dataList);
			Gson gson = new Gson();
			chartStr = gson.toJsonTree(option).toString();
			mapValue.put("dataList", dataList);
			mapValue.put("option", chartStr);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return mapValue;
	}
	
	static List getReportList(Map<String, Object> map) {
		List dataList = new ArrayList();
		String times="";
		Object dataTime=map.get("dataTime");
		Object line=map.get("line");
		String addSql="";
		if(line !=null){
			if(line.toString()!=""){
				addSql =" and line =" +line;
			}
		}
		if(dataTime!=null){
			times=dataTime.toString();
		}
		if(times.equals("")){
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			times=sdf.format(new Date());
		}
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String times1=times+" 00:00:01";
		String times2=times+" 23:59:59";
		
		Connection conn = null;	
		PreparedStatement ps = null;
		ResultSet rs = null;
	try {
		Long t1=simpleDateFormat.parse(times1).getTime();
		Long t2=simpleDateFormat.parse(times2).getTime();
		String sql="SELECT up_down xAxis , line legend,count(*) series"+
				 " FROM  bsth_c_offline "+
				"where timestamp>? and timestamp<? "+addSql+" group by xAxis ,legend";
		conn = DBUtils_MS.getConnection();
		ps = conn.prepareStatement(sql);
		ps.setLong(1, t1);
		ps.setLong(2, t2);
		rs = ps.executeQuery();
		while(rs.next()){
			Map<String, Object> newMap=new HashMap<String,Object>();
			if(rs.getObject("xAxis").toString().equals("0"))
				newMap.put("xAxis", "上行");
			else if(rs.getObject("xAxis").toString().equals("1"))
				newMap.put("xAxis", "下行");
			else
				newMap.put("xAxis", "无效");
			
			if(BasicData.lineCode2NameMap.get(rs.getObject("legend").toString())!=null && BasicData.lineCode2NameMap.get(rs.getObject("legend").toString())!="" )
				newMap.put("legend", BasicData.lineCode2NameMap.get(rs.getObject("legend").toString()));
			else
				newMap.put("legend", "编码"+rs.getObject("legend").toString());
			
			
			
			newMap.put("series", Integer.parseInt(rs.getObject("series").toString()));
			dataList.add(newMap);
 		}
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}catch (ParseException e1) {
		// TODO Auto-generated catch block
		e1.printStackTrace();
	}finally {
		DBUtils_MS.close(rs, ps, conn);
	}
	
		return dataList;
	}

}