潘钊 / bsth_control · Files

package com.bsth.service.oil.impl;

import java.io.File;
import java.io.FileInputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.transaction.Transactional;

import com.bsth.entity.oil.Jdl;
import com.bsth.util.ReportUtils;
import org.apache.commons.lang3.StringEscapeUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.bsth.common.ResponseCode;
import com.bsth.entity.excep.Offline;
import com.bsth.entity.oil.Ylb;
import com.bsth.entity.oil.Ylxxb;
import com.bsth.entity.oil.YlxxbUpdate;
import com.bsth.entity.search.CustomerSpecs;
import com.bsth.repository.oil.YlbRepository;
import com.bsth.repository.oil.YlxxbRepository;
import com.bsth.repository.oil.YlxxbUpdateRepository;
import com.bsth.service.impl.BaseServiceImpl;
import com.bsth.service.oil.YlxxbService;
import com.bsth.util.PageHelper;
import com.bsth.util.PageObject;

@Service
public class YlxxbServiceImpl extends BaseServiceImpl<Ylxxb,Integer> implements YlxxbService
{
	Logger logger = LoggerFactory.getLogger(this.getClass());
	@Autowired
	YlxxbRepository repository;
	@Autowired
	YlxxbUpdateRepository updateRepository;
	@Autowired
	YlbRepository ylbRepository;
	@Autowired
	JdbcTemplate jdbcTemplate;

	@Autowired
	YlxxbRepository ylxxbRepository;

	@Override
	public PageObject<Ylxxb> Pagequery(Map<String, Object> map) {

		String rq=map.get("yyrq").toString();
		String gsdm=map.get("gsdm_like").toString();
		String fgsdm=map.get("fgsdm_like").toString();

		String sql="	select * from ("
				+ "select *,CONCAT(nbbm,jsy)  as nj, CONCAT(nbbm,'_',jsy,'_',jzl) as njj "
				+ " from bsth_c_ylxxb where yyrq='"+rq+"' "
				+ " and gsdm ='"+gsdm+"' and jylx !='1') x where x.nj not in ("
				+ " select CONCAT(nbbm,jsy)  from bsth_c_ylb "
				+ " where rq='"+rq+"'"
				+ " and ssgsdm='"+gsdm+"' and fgsdm ='"+fgsdm+"')"
				+ " and x.nbbm in (select nbbm  from bsth_c_ylb "
				+ " where rq='"+rq+"'"
				+ " and ssgsdm='"+gsdm+"' and fgsdm ='"+fgsdm+"') "
				+ " and x.nj not in (select CONCAT(nbbm,jsy) from bsth_c_ylxxb"
				+ " where yyrq='"+rq+"' and gsdm ='"+gsdm+"' AND jylx in (1,3))"
				+ " and x.njj not in (SELECT tj FROM bsth_c_ylxxb_update  "
				+ " where yyrq='"+rq+"' and zt='3')";

		//根具条件查询指定日期Ylb的数据
//		List<Ylb> ylbIterator=ylbRepository.checkYl(rq,gsdm,fgsdm);
		List<Ylxxb> list=jdbcTemplate.query(sql,
				new RowMapper<Ylxxb>(){
					@Override
					public Ylxxb  mapRow(ResultSet rs, int rowNum) throws SQLException {
						Ylxxb s  = new Ylxxb();
						s.setId(rs.getInt("id"));
						s.setYyrq(rs.getDate("yyrq"));
						s.setNbbm(rs.getString("nbbm"));
						s.setGsdm(rs.getString("gsdm"));
						s.setFgsdm(rs.getString("fgsdm"));
						s.setJsy(rs.getString("jsy"));
						s.setJzl(rs.getDouble("jzl"));
						s.setStationid(rs.getString("stationid"));
						s.setNylx(rs.getInt("nylx"));
						s.setJyggh(rs.getString("jyggh"));
						s.setYj(rs.getDouble("yj"));
//            	s.setLdgh(rs.getString("ldgh"));
						s.setBz(rs.getString("bz"));
						return s;
					}
				});


		List<Ylb> listylb=ylbRepository.obtainYl(rq, gsdm, fgsdm, "", "", "nbbm");
		for (int i = 0; i < list.size(); i++) {
			String ldgh="";
			for (int j = 0; j < listylb.size(); j++) {
				Ylb ylb =listylb.get(j);
				if(list.get(i).getNbbm().equals(ylb.getNbbm())){
					if(ldgh.equals(""))
						ldgh +=ylb.getJsy();
					else
						ldgh += ","+ylb.getJsy();
				}
			}
			list.get(i).setLdgh(ldgh);

		}



		PageHelper pageHelper = new PageHelper(list.size(), map);
		pageHelper.getMap();
		PageObject<Ylxxb> pageObject = pageHelper.getPageObject();
		pageObject.setDataList(list);
		return pageObject;
	}
	@Transactional
	@Override
	public Map<String, Object> checkJsy(Map<String, Object> map) throws Exception{
		Map<String, Object> newMap=new HashMap<String,Object>();
		// TODO Auto-generated method stub
		try {
//		int id=Integer.parseInt(map.get("id").toString());
//		String jsy=map.get("jsy").toString();
//		Ylxxb ylxxb=repository.findOne(id);
//		ylxxb.setJsy(jsy);
//		repository.save(ylxxb);
	        DecimalFormat decimalFormat = new DecimalFormat("###################.###########");  

			String json =StringEscapeUtils.unescapeHtml4(map.get("ylbList").toString());
			JSONArray jsonArray=JSONArray.parseArray(json);
			JSONObject jsonObject;
			for (int x = 0; x < jsonArray.size(); x++) {
				jsonObject=jsonArray.getJSONObject(x);
				Integer id =jsonObject.getInteger("id");
				String jsy =jsonObject.getString("jsy");
				String lsjsy =jsonObject.getString("lsjsy");
				String ldgh =jsonObject.getString("ldgh");
				if(!lsjsy.equals(jsy)){
					if(ldgh.indexOf(jsy)>-1){
						Ylxxb ylxxb=repository.findOne(id);
						YlxxbUpdate ylxxbUpdate=new YlxxbUpdate();
						ylxxbUpdate.setGsdm(ylxxb.getGsdm());
						ylxxbUpdate.setJsy(ylxxb.getJsy());
						ylxxbUpdate.setJzl(ylxxb.getJzl());
						ylxxbUpdate.setJlrq(ylxxb.getJlrq());
						ylxxbUpdate.setNbbm(ylxxb.getNbbm());
						ylxxbUpdate.setYyrq(ylxxb.getYyrq());
						ylxxbUpdate.setCreatetime(new Date());
						ylxxbUpdate.setTj(ylxxb.getNbbm()+"_"+ylxxb.getJsy()+"_"+decimalFormat.format(ylxxb.getJzl()));
						ylxxbUpdate.setZt(3);
						updateRepository.save(ylxxbUpdate);
						ylxxb.setJsy(jsy);
						ylxxb.setJylx(3);
						repository.save(ylxxb);
					}
				}
			}
			newMap.put("status", ResponseCode.SUCCESS);
		}catch(Exception e){
			newMap.put("status", ResponseCode.ERROR);
			logger.error("save erro.", e);
			throw e;
		}
		return newMap;
	}
	@Override
	public List<Ylxxb> Pagequery_nbbm(Map<String, Object> map) {
		// TODO Auto-generated method stub
		String rq=map.get("yyrq").toString();
		String gsdm=map.get("gsdm_like").toString();
		String fgsdm=map.get("fgsdm_like").toString();

		String nbbm=map.get("nbbm").toString();
		
	String sql="select * from ("
			+ " select t.*,CONCAT(t.nbbm,'_',t.jsy,'_',t.jzl) as tj from bsth_c_ylxxb t  where "
			+ " t.yyrq='"+rq+"' and t.gsdm='"+gsdm+"' and  t.nylx=0 and t.jylx in(0,2)  "
			+ " and t.nbbm  in (select c.inside_code from bsth_c_cars c "
			+ " where c.business_code ='"+gsdm+"' and c.sfdc=0 and c.branche_company_code ='"+fgsdm+"')"
			+ " and t.nbbm not in (select nbbm from bsth_c_ylb y "
			+ " where y.rq= '"+rq+"' and ssgsdm='"+gsdm+"' and fgsdm='"+fgsdm+"') "
			+ " union "
			+ " select t.*,CONCAT(t.nbbm,'_',t.jsy,'_',t.jzl) as tj from bsth_c_ylxxb t  where "
			+ " t.yyrq ='"+rq+"' and t.gsdm='"+gsdm+"' and t.nylx=0 and t.jylx in(0,2) "
			+ " and t.nbbm not in (	select c.inside_code from bsth_c_cars c  "
			+ " where c.business_code ='"+gsdm+"' and c.sfdc=0 )"
			+ " ) z"
			+ " where z.tj not in ("
			+ " SELECT x.tj FROM bsth_c_ylxxb_update x where x.yyrq='"+rq+"' and zt=2"
			+ " ) and z.nbbm like '%"+nbbm+"%'";
		
		//根具条件查询指定日期Ylb的数据
//		List<Ylb> ylbIterator=ylbRepository.checkYl(rq,gsdm,fgsdm);
		List<Ylxxb> list=jdbcTemplate.query(sql,  
		        new RowMapper<Ylxxb>(){  
            @Override  
            public Ylxxb  mapRow(ResultSet rs, int rowNum) throws SQLException {  
            	Ylxxb s  = new Ylxxb(); 
            	s.setId(rs.getInt("id"));
            	s.setYyrq(rs.getDate("yyrq"));
            	s.setNbbm(rs.getString("nbbm"));
            	s.setGsdm(rs.getString("gsdm"));
            	s.setJsy(rs.getString("jsy"));
            	s.setJzl(rs.getDouble("jzl"));
            	s.setStationid(rs.getString("stationid"));
            	s.setNylx(rs.getInt("nylx"));
            	s.setJyggh(rs.getString("jyggh"));
            	s.setYj(rs.getDouble("yj"));
//            	s.setLdgh(rs.getString("ldgh"));
            	s.setBz(rs.getString("bz"));
                return s;  
            }  
		});  
		return list;
	}


	@Transactional  
	@Override
	public Map<String, Object> checkNbbm(Map<String, Object> map) throws Exception{
		Map<String, Object> newMap=new HashMap<String,Object>();
		// TODO Auto-generated method stub
		try {
//		int id=Integer.parseInt(map.get("id").toString());
//		String jsy=map.get("jsy").toString();
//		Ylxxb ylxxb=repository.findOne(id);
//		ylxxb.setJsy(jsy);
//		repository.save(ylxxb);
			String json =StringEscapeUtils.unescapeHtml4(map.get("ylbList").toString());
			JSONArray jsonArray=JSONArray.parseArray(json);
	        DecimalFormat decimalFormat = new DecimalFormat("###################.###########");  

			JSONObject jsonObject;
			for (int x = 0; x < jsonArray.size(); x++) {
				jsonObject=jsonArray.getJSONObject(x);
				Integer id =jsonObject.getInteger("id");
				String nbbm =jsonObject.getString("nbbm").trim();
				Ylxxb ylxxb=repository.findOne(id);
				if(!ylxxb.getNbbm().equals(nbbm)){
					ylxxb.setNbbm(nbbm);
					ylxxb.setJylx(2);
					repository.save(ylxxb);
					YlxxbUpdate ylxxbUpdate=new YlxxbUpdate();
					ylxxbUpdate.setGsdm(ylxxb.getGsdm());
					ylxxbUpdate.setJsy(ylxxb.getJsy());
					ylxxbUpdate.setJzl(ylxxb.getJzl());
					ylxxbUpdate.setJlrq(ylxxb.getJlrq());
					ylxxbUpdate.setNbbm(nbbm);
					ylxxbUpdate.setYyrq(ylxxb.getYyrq());
					ylxxbUpdate.setCreatetime(new Date());
					ylxxbUpdate.setTj(nbbm+"_"+ylxxb.getJsy()+"_"+decimalFormat.format(ylxxb.getJzl()));
					ylxxbUpdate.setZt(2);
					updateRepository.save(ylxxbUpdate);
				}
			}
		newMap.put("status", ResponseCode.SUCCESS);
		}catch(Exception e){
			newMap.put("status", ResponseCode.ERROR);
			logger.error("save erro.", e);
			throw e;
		}
		return newMap;
	}
	@Override
	public Map<String, Object> saveYlxxb(Map<String, Object> map) {
		// TODO Auto-generated method stub
		Map<String, Object> newMap=new HashMap<String,Object>();
		try{
			Integer id=Integer.parseInt(map.get("id").toString());
			Ylxxb t=repository.findOne(id);
			t.setJzl(Double.parseDouble(map.get("jzl").toString().trim()));
			t.setNbbm(map.get("nbbm").toString().trim());
			t.setJsy(map.get("jsy").toString().trim());
			repository.save(t);
			newMap.put("status", ResponseCode.SUCCESS);
		}catch(Exception e){
			newMap.put("status", ResponseCode.ERROR);
			logger.error("save erro.", e);
			throw e;
		}
		return newMap;
	}

	@Override
	public String importExcel(File file) {
		String gsbm="100";
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		DecimalFormat df = new DecimalFormat("######0.00");
		List<String> textList = new ArrayList<String>();
		try {
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			HSSFSheet sheet = wb.getSheetAt(0);
			// 取得总行数
			int rowNum = sheet.getLastRowNum() + 1;
			// 取得总列数
			int cellNum = sheet.getRow(0).getLastCellNum();
			HSSFRow row = null;
			HSSFCell cell = null;
			for(int i = 2; i < rowNum; i++){
				row = sheet.getRow(i);
				if (row == null){
					continue;
				}
				String text = "";
				for(int j = 0; j < cellNum; j++){
					cell = row.getCell(j);
					if(cell == null){
						text += ",";
						continue;
					}
					text += String.valueOf(cell) + ",";
				}
				String[] split = (text+";").split(",");
				String str = "";
				for(int j = 0; j < split.length && j < 5; j++){
					str += split[j];
				}
				if(str.trim().length() == 0){
					continue;
				}
				textList.add(text + ";");
			}
			for(int i = 0; i < textList.size(); i++){
				String text = textList.get(i);
				System.out.println(text);
				String[] split = text.split(",");
				String rq = split[0].trim();//日期
				String nbbm = split[1].trim();//内部编号
				/*String jsy = split[2].trim();//驾驶员工号*/
				String jsy ="";//
				double jzl = Double.valueOf(split[2].trim().length()!=0?split[2]:"0");//加注量
				String jyz = split[3].trim();//能源站
				String plate = split[4].trim();//车牌号
				String remarks = split[5].trim();//备注
				String yh = split[6].trim();//油号
				if(rq.trim().length() == 0){
					rq = sdf.format(new Date());
				}
			/*	System.out.println(jsy);
				if(jsy != null && jsy.length() > 0 && !(jsy.equals(jsy.replaceAll("\\p{P}", "")))){
					return "加油：驾驶员工号错误，请检查是否导入错误excel";
				}*/

				List<Double> jzl_ = ylxxbRepository.import_queryBySame(gsbm, rq, nbbm);
//
				if(jzl_.size() == 0){
					ylxxbRepository.import_insertData(gsbm, rq, nbbm, jsy,
							df.format(jzl), jyz, remarks, sd.format(new Date()),plate,yh);
				}else{
					ylxxbRepository.import_UpdateJzl(df.format(jzl), jsy, jyz, remarks, gsbm, rq, nbbm,plate,yh);
				}
			}

			wb.close();
			fs.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "文件导入失败";
		} finally {
			file.delete();
		}
		return "文件导入成功";
	}

	@Override
	public Map<String, Object> query(Map<String, Object> map) {
		Map<String, Object> modelMap = new HashMap<String, Object>();
		Integer page = Integer.valueOf(map.containsKey("page")?map.get("page").toString():"0");
		StringBuffer sql=new StringBuffer("SELECT * FROM bsth_c_ylxxb where 1=1 ");
		if(map.get("rq")!=null && !"".equals(map.get("rq").toString())){
			sql.append("and yyrq>='"+map.get("rq")+"'");
		}
		if(map.get("nbbm")!=null && !"".equals(map.get("nbbm").toString())){
			sql.append("and nbbm='"+map.get("nbbm")+"'");
		}
		List<Ylxxb> query = jdbcTemplate.query(sql.toString(), BeanPropertyRowMapper.newInstance(Ylxxb.class));

		if(!map.containsKey("type")){

			int end = (page+1)*10>query.size()?query.size():(page+1)*10;
			modelMap.put("dataList", query.subList(page*10, end));
			modelMap.put("totalPages", query.size()%10>0?query.size()/10+1:query.size()/10);

		} else if(map.get("type").toString().equals("export")){
			SimpleDateFormat sdfMonth = new SimpleDateFormat("yyyy-MM-dd"),
					sdfSimple = new SimpleDateFormat("yyyyMMdd");

			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
			for(Ylxxb jdl : query){
				Map<String, Object> m = new HashMap<String, Object>();
				m.put("rq", sdfMonth.format(jdl.getYyrq()));
				m.put("nbbm", jdl.getNbbm());
				m.put("jyl", jdl.getJzl());
				m.put("jyz", jdl.getStationid());
				m.put("remarks", jdl.getBz());
				m.put("yh", jdl.getYh());
				list.add(m);
			}
/*
			List<Iterator<?>> listI = new ArrayList<Iterator<?>>();
			Map<String, Object> m = new HashMap<String, Object>();
			ReportUtils ee = new ReportUtils();
			try {
				listI.add(list.iterator());
				String path = this.getClass().getResource("/").getPath()+"static/pages/forms/";
				ee.excelReplace(listI, new Object[] { m }, path+"mould/export_Jdl.xls",
						path+"export/车辆充电量" + sdfSimple.format(sdfMonth.parse(rq)) + ".xls");
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}*/
		}
		return modelMap;
	}
}