潘钊 / bsth_control · Files

package com.bsth.service.oil.impl;

import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

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.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.bsth.common.ResponseCode;
import com.bsth.entity.oil.Jdl;
import com.bsth.entity.oil.JdlReception;
import com.bsth.entity.sys.SysUser;
import com.bsth.repository.oil.JdlReceptionRepository;
import com.bsth.repository.oil.JdlRepository;
import com.bsth.security.util.SecurityUtils;
import com.bsth.service.impl.BaseServiceImpl;
import com.bsth.service.oil.JdlService;
import com.bsth.util.ReportUtils;

@Service
public class JdlServiceImpl extends BaseServiceImpl<Jdl, Integer> implements JdlService {
	
	@Autowired
	JdlRepository repository;
	
	@Autowired
	JdlReceptionRepository jdlReceptionRepository;

	@Override
	public String importExcel(File file, String gsbm, String gsName, String fgsbm, String fgsName) {
		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);
				String[] split = text.split(",");
				String rq = split[0].trim();
				String nbbm = split[1].trim();
//				String jsy= split[2].trim();
				double jdl = Double.valueOf(split[2].trim().length()!=0?split[2]:"0");
				String jdz = split[3].trim();
				String remarks = split[4].trim();
				if(rq.trim().length() == 0){
					rq = sdf.format(new Date());
				}
				
				List<Double> jdl_ = repository.queryBySame(gsbm, fgsbm, rq, nbbm);
				
				if(jdl_.size() == 0){
					repository.insertData(gsbm, gsName, fgsbm, fgsName, rq, nbbm,
							df.format(jdl), jdz, remarks, sd.format(new Date()));
				}else{
//					jdl += jdl_.get(0);
					repository.UpdateJdl(df.format(jdl), sd.format(new Date()), gsbm, fgsbm, rq, nbbm);
				}
			}
			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>();
		String gsbm = map.get("gsbm").toString();
		String fgsbm = map.get("fgsbm").toString();
		String rq = map.get("rq").toString();
		String nbbm = map.get("nbbm").toString();
//		String jsy =map.get("jsy").toString();
		Integer page = Integer.valueOf(map.containsKey("page")?map.get("page").toString():"0");
		List<Jdl> query = repository.query(gsbm, fgsbm, rq, nbbm);
		
		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(Jdl jdl : query){
				Map<String, Object> m = new HashMap<String, Object>();
				m.put("rq", sdfMonth.format(jdl.getRq()));
				m.put("nbbm", jdl.getNbbm());
				m.put("jdl", jdl.getJdl());
				m.put("jdz", jdl.getJdz());
				m.put("remarks", jdl.getRemarks());
				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;
	}

	@Override
	public String importExcel_2412(File file, String gsbm, String gsName, String fgsbm, String fgsName) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		SimpleDateFormat ddFormat = new SimpleDateFormat("dd");
		DecimalFormat df = new DecimalFormat("######0.00");
		SysUser user = SecurityUtils.getCurrentUser();
		Date currDate = new Date();
		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 = 3; 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 < 12; j++){
					str += split[j];
				}
				if(str.trim().length() == 0)
					continue;
				textList.add(text + ";");
			}
			List<JdlReception> list = new ArrayList<JdlReception>();
			for(int i = 0; i < textList.size(); i++){
				String text = textList.get(i);
				String[] split = text.split(",");
				if(split[1] != null && split[1].trim().length() > 0){
					Integer dd = Integer.valueOf(split[1].trim().split("\\.")[0]);	// 日期
					String zh = split[2].trim();	// 桩号
					String nbbm = split[3].trim();	// 车号
					String startTime = split[4].trim();	// 开始时间
					String endTime = split[5].trim();	// 结束时间
					String sumTime = split[6].trim();	// 总计（分钟）
					String startSoc = split[7].trim();	// 起始电量SOC%
					String endSoc = split[8].trim();	// 结束电量SOC%
					String jdl = split[9].trim();	// 充电度数
					String lc = split[10].trim();	// 里程度数
					String stopReason = split[11].trim();	// 一次不能正常充电记录
					JdlReception jdlRe = new JdlReception();
					Date date = sd.parse(startTime);
					if(!(dd == Integer.valueOf(ddFormat.format(date)))){
						date.setTime(date.getTime() - 1l*1000*60*60*24);
					}
					jdlRe.setDateStr(sdf.format(date));
					jdlRe.setConnectorId(zh);
					jdlRe.setCarCode(nbbm);
					jdlRe.setStartTime(startTime);
					jdlRe.setEndTime(endTime);
					jdlRe.setSumTime(Integer.valueOf(sumTime.split("\\.")[0]));
					jdlRe.setStartSoc(Double.valueOf(startSoc));
					jdlRe.setEndSoc(Double.valueOf(endSoc));
					jdlRe.setChargeCapacity(Double.valueOf(jdl));
					jdlRe.setStopReason(stopReason);
					jdlRe.setCreateBy(user.getUserName());
					jdlRe.setCreateDate(currDate);
					jdlRe.setOrigin(1);
					list.add(jdlRe);
				}
			}
			jdlReceptionRepository.saveAll(list);
			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_2412(Map<String, Object> map) {
		SimpleDateFormat sdfMonth = new SimpleDateFormat("yyyy-MM-dd"),
				sdfSimple = new SimpleDateFormat("yyyyMMdd");
		Map<String, Object> modelMap = new HashMap<String, Object>();
		String rq = map.get("rq").toString();
		String nbbm = map.get("nbbm").toString();
		Integer page = Integer.valueOf(map.containsKey("page")?map.get("page").toString():"0");
		List<Jdl> query = new ArrayList<Jdl>();
		
		List<Object[]> querySum = jdlReceptionRepository.querySum(rq, nbbm);
		
		try {
			for(Object[] j : querySum){
				Jdl jdl = new Jdl();
				jdl.setRq(sdfMonth.parse(j[0].toString()));
				jdl.setNbbm(j[1].toString());
				jdl.setJdl(Double.valueOf(j[2].toString()));
				jdl.setJdz("");
				jdl.setRemarks("");
				query.add(jdl);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		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")){
			
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
			for(Jdl jdl : query){
				Map<String, Object> m = new HashMap<String, Object>();
				m.put("rq", sdfMonth.format(jdl.getRq()));
				m.put("nbbm", jdl.getNbbm());
				m.put("jdl", jdl.getJdl());
				m.put("jdz", jdl.getJdz());
				m.put("remarks", jdl.getRemarks());
				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;
	}

	@Override
	public List<JdlReception> queryJdlReception(Map<String, Object> map) {
		String rq = map.get("rq").toString();
		String nbbm = map.get("nbbm").toString();
		List<JdlReception> query = jdlReceptionRepository.query(rq, nbbm);
		return query;
	}

	@Override
	public Map<String, Object> queryJdlReceptionBatch(Map<String, Object> map) {
		SimpleDateFormat sdfMonth = new SimpleDateFormat("yyyy-MM-dd");
		Map<String, Object> modelMap = new HashMap<String, Object>();
		String rq = map.get("rq").toString();
		Integer page = Integer.valueOf(map.containsKey("page")?map.get("page").toString():"0");
		List<Map<String, Object>> query = new ArrayList<Map<String, Object>>();
		
		List<Object[]> queryBatch = jdlReceptionRepository.queryBatch(rq);
		
		try {
			for(Object[] j : queryBatch){
				Map<String, Object> m = new HashMap<String, Object>();
				m.put("createBy", j[0]);
				m.put("createDate", j[1]);
				query.add(m);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		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);
			
		return modelMap;
	}

	@Override
	public List<JdlReception> queryJdlReceptionBatchData(Map<String, Object> map) {
		String createBy = map.get("createBy").toString();
		String createDate = map.get("createDate").toString();
		List<JdlReception> query = jdlReceptionRepository.queryBatchData(createBy, createDate);
		return query;
	}

	@Override
	public Map<String, Object> deleteJdlReceptionBatch(Map<String, Object> map) {
		Map<String, Object> resMap = new HashMap<>();
		try{
			String createBy = map.get("createBy").toString();
			String createDate = map.get("createDate").toString();
			jdlReceptionRepository.deleteBatch(createBy, createDate);
			resMap.put("status", ResponseCode.SUCCESS);
		}catch (Exception e){
			resMap.put("status", ResponseCode.ERROR);
			resMap.put("msg", e.getMessage());
		}
		return resMap;
	}

	@Override
	public List<Jdl> queryJdlByJdlReception(String rq, String nbbm) {
		List<Jdl> list = new ArrayList<Jdl>();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		List<JdlReception> query = jdlReceptionRepository.query(rq, nbbm);
		for(JdlReception jr : query){
			try {
				Jdl jdl = new Jdl();
				jdl.setFgsBm("-1");
				jdl.setRq(sdf.parse(jr.getDateStr()));
				jdl.setJdl(jr.getChargeCapacity());
				jdl.setNbbm(jr.getCarCode());
				list.add(jdl);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	
}