潘钊 / 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.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.transaction.Transactional;

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.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.data.BasicData;
import com.bsth.entity.oil.Cwjy;
import com.bsth.entity.oil.Cyl;
import com.bsth.entity.oil.Ylb;
import com.bsth.entity.oil.Ylxxb;
import com.bsth.repository.oil.CwjyRepository;
import com.bsth.repository.oil.YlxxbRepository;
import com.bsth.service.impl.BaseServiceImpl;
import com.bsth.service.oil.CwjyService;
import com.bsth.util.Arith;
import com.bsth.util.PageHelper;
import com.bsth.util.PageObject;

@Service
public class CwjyServiceImpl extends BaseServiceImpl<Cwjy,Integer> implements CwjyService
{
	@Autowired
	CwjyRepository repository;
	@Autowired
	YlxxbRepository ylxxbRepository;
	
	@Autowired
	JdbcTemplate jdbcTemplate;
	Logger logger = LoggerFactory.getLogger(this.getClass());
	/*@SuppressWarnings("unchecked")
	public PageObject<Ylxxb> Pagequery(Map<String, Object> map) {
		int page=Integer.parseInt(map.get("page").toString());
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		
		String rq=map.get("rq").toString();
		String nbbm="";
		if(map.get("nbbh")!=null){
			nbbm=map.get("nbbh").toString();
		}
		
		Object gsdmIn=map.get("gsdm_in");
		Object fgsdmIn=map.get("fgsdm_in");
		String addSql="";
		String  gsdmLike="";
		String  fgsdmLike="";
		
		//选择了公司 
		if(gsdmIn==null){
			gsdmLike=map.get("gsdm_like").toString();
			addSql += " and a.gsdm = '"+gsdmLike+ "' ";
			
			//选择了分公司
			if(fgsdmIn==null){
				fgsdmLike=map.get("fgsdm_like").toString();;
				addSql += " and a.fgsdm = '"+fgsdmLike+ "' ";
			}else{
				String fgsdmIns[]= fgsdmIn.toString().split(",");
				addSql +=" and a.fgsdm in (";
				for(int i=0;i<fgsdmIns.length;i++){
					addSql +="'"+fgsdmIns[i]+"'";
					if(i<fgsdmIns.length-1){
						addSql +=",";
					}
				}
				addSql +=")";
			}
		}else{
			//没有选择公司 （分公司也没有选择）
			String gsdmIns[]=gsdmIn.toString().split(",");
			addSql += " and a.gsdm in (";
			for(int i=0;i<gsdmIns.length;i++){
				addSql +="'" +gsdmIns[i]+"'";
				if(i<gsdmIns.length-1){
					addSql+=",";
				}
			}
			addSql +=")";
			String fgsdmIns[]= fgsdmIn.toString().split(",");
			addSql +=" and a.fgsdm in (";
			for(int i=0;i<fgsdmIns.length;i++){
				addSql +="'"+fgsdmIns[i]+"'";
				if(i<fgsdmIns.length-1){
					addSql +=",";
				}
			}
			addSql +=")";
			
			
		}
		String countSql="SELECT ifnull(count(*),0) as countTs FROM  bsth_c_cwjy  a "+ 
				   " left  join ( select * from bsth_c_ylxxb b where to_days(b.yyrq)=to_days('"+rq+"') and jylx=1) b " +
				  " on a.nbbm=b.nbbm left join (select nbbm,group_concat(jsy) as jsy  "
				  + "from bsth_c_ylb where  to_days(rq)= to_days('"+rq+"' ) group by nbbm "+
				   " ) c on a.nbbm=c.nbbm  where  a.nbbm like '%"+nbbm+"%' " +addSql ;
		int listsize=jdbcTemplate.queryForObject(countSql, Integer.class);
		// TODO Auto-generated method stub
		String sql="SELECT a.gsdm as gsdm,a.fgsdm as fgsdm,a.nbbm as nbbm,b.jsy as jsy,b.jzl as jzl ,b.stationid as stationid,"
				+ "b.nylx as nylx,b.yj as yj,b.bz as bz,c.jsy as ldgh  FROM  bsth_c_cwjy  a "+ 
				   " left  join ( select * from bsth_c_ylxxb b where to_days(b.yyrq)=to_days('"+rq+"') and jylx=1) b " +
				  " on a.nbbm=b.nbbm left join (select nbbm,group_concat(jsy) as jsy  "
				  + "from bsth_c_ylb where  to_days(rq)= to_days('"+rq+"' ) group by nbbm "+
				   " ) c on a.nbbm=c.nbbm  where  a.nbbm like '%"+nbbm+"%' " +addSql+ "  limit "+page*10+","+10;
		
		
		List<Ylxxb> yList= jdbcTemplate.query(sql,
				 new RowMapper<Ylxxb>(){
			 @Override
			 public Ylxxb  mapRow(ResultSet rs, int rowNum) throws SQLException {  
				 Ylxxb t=new Ylxxb();
				 t.setGsdm(rs.getString("gsdm"));
				 t.setFgsdm(rs.getString("fgsdm"));
				 t.setNbbm(rs.getString("nbbm"));
				 t.setJsy(rs.getString("jsy"));
				 t.setJzl(rs.getDouble("jzl"));
				 t.setStationid(rs.getString("stationid"));
				 t.setNylx(rs.getInt("nylx"));
				 t.setYj(rs.getDouble("yj"));
				 t.setBz(rs.getString("bz"));
				 t.setLdgh(rs.getString("ldgh"));
				 return t;
			 }
		});
		List<Object[]> list=repository.obtainCwjycl(rq,nbbm);
		for (int i = 0; i < list.size(); i++) {
			Ylxxb y=new Ylxxb();
			y.setGsdm(list.get(i)[0]==null?"":list.get(i)[0].toString());
			y.setNbbm(list.get(i)[1]==null?"":list.get(i)[1].toString());
			y.setJsy(list.get(i)[2]==null?"":list.get(i)[2].toString());
			y.setJzl(list.get(i)[3]==null?0.0:Double.parseDouble(list.get(i)[3].toString()));
			y.setStationid(list.get(i)[4]==null?"":list.get(i)[4].toString());
			y.setNylx(list.get(i)[5]==null?0:Integer.parseInt(list.get(i)[5].toString()));
			y.setYj(list.get(i)[6]==null?0.0:Double.parseDouble(list.get(i)[6].toString()));
			y.setBz(list.get(i)[7]==null?"":list.get(i)[7].toString());
			y.setLdgh(list.get(i)[8]==null?"":list.get(i)[8].toString());
			try {
				y.setYyrq(sdf.parse(rq));
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			yList.add(y);
		}
		PageHelper pageHelper = new PageHelper(listsize, map);
		pageHelper.getMap();
		PageObject<Ylxxb> pageObject=pageHelper.getPageObject();
		pageObject.setDataList(yList);
		return pageObject;
	}*/

	@Override
	public Ylxxb bynbbm(Map<String, Object> map) {
		// TODO Auto-generated method stub
		// TODO Auto-generated method stub
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String rq = map.get("rq").toString();
		String nbbm = "";
		if (map.get("nbbm") != null) {
			nbbm = map.get("nbbm").toString();
		}
		List<Object[]> list = repository.obtainCwjycl(rq, nbbm);
		Ylxxb y = new Ylxxb();
		if (list.size() > 0) {
			y.setGsdm(list.get(0)[0] == null ? "" : list.get(0)[0].toString());
			y.setNbbm(list.get(0)[1] == null ? "" : list.get(0)[1].toString());
			y.setJsy(list.get(0)[2] == null ? "" : list.get(0)[2].toString());
			y.setJzl(list.get(0)[3] == null ? 0.0 : Double.parseDouble(list.get(0)[3].toString()));
			y.setStationid(list.get(0)[4] == null ? "" : list.get(0)[4].toString());
			y.setNylx(list.get(0)[5] == null ? 0 : Integer.parseInt(list.get(0)[5].toString()));
			y.setYj(list.get(0)[6] == null ? 0.0 : Double.parseDouble(list.get(0)[6].toString()));
			y.setBz(list.get(0)[7] == null ? "" : list.get(0)[7].toString());
			y.setLdgh(list.get(0)[8] == null ? "" : list.get(0)[8].toString());
			try {
				y.setYyrq(sdf.parse(rq));
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return y;
	}

	@Transactional 
	@Override
	public Map<String, Object> savejzl(Map<String, Object> maps) throws Exception {
		// TODO Auto-generated method stub
		Map<String, Object> map=new HashMap<String,Object>();
		try {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//			String rq = sdf.format(t.getYyrq());
//			String nbbm=t.getNbbm();
//			List<Ylxxb> yList=ylxxbRepository.obtainYlxx2(rq,nbbm);
//			if(yList.size()>0){
//				t.setId(yList.get(0).getId());
//				ylxxbRepository.save(t);
//			}else{
//				ylxxbRepository.save(t);
//			}
			String gsbm=maps.get("gsbm").toString();
			String fgsbm=maps.get("fgsbm").toString();
			String rq=maps.get("rq").toString();
			Date date=sdf.parse(rq);
			String json =StringEscapeUtils.unescapeHtml4(maps.get("ylbList").toString());
			JSONArray jsonArray=JSONArray.parseArray(json);
			JSONObject jsonObject;
			// 获取车辆存油信息
			for (int i = 0; i < jsonArray.size(); i++) {
				jsonObject=jsonArray.getJSONObject(i);
				Integer id =jsonObject.getInteger("id");
				String jsy=jsonObject.getString("jsy");
				double jzl=jsonObject.getDoubleValue("jzl");
				String stationid=jsonObject.getString("stationid");
				String nbbm=jsonObject.getString("nbbm");
				int nylx=jsonObject.getIntValue("nylx");
				String jyggh=jsonObject.getString("jyggh");
				String bz=jsonObject.getString("bz");
				Ylxxb y;
				if(id>0){
					y=ylxxbRepository.findOne(id);
					y.setJsy(jsy);
					y.setJzl(jzl);
					y.setStationid(stationid);
					y.setJyggh(jyggh);
					y.setBz(bz);
					y.setCreatetime(new Date());
					y.setYyrq(date);
					ylxxbRepository.save(y);
				}else{
					if(jsy!="" && jzl>0){
						y=new Ylxxb();
						y.setGsdm(gsbm);
						y.setJsy(jsy);
						y.setNbbm(nbbm);
						y.setJzl(jzl);
						y.setStationid(stationid);
						y.setJyggh(jyggh);
						y.setBz(bz);
						y.setCreatetime(new Date());
						y.setYyrq(date);
						y.setJylx(1);
						y.setNylx(0);
						ylxxbRepository.save(y);
					}
				}
			}
			map.put("status", ResponseCode.SUCCESS);
			
		} catch (Exception e) {
			map.put("status", ResponseCode.ERROR);
			logger.error("save erro.", e);
			throw e;
			// TODO: handle exception
		}
		return map;
	}

	
	
	@Override
	public List<Ylxxb> Pagequery(Map<String, Object> map) {
		// TODO Auto-generated method stub
		String rq=map.get("rq").toString();
		String nbbm=map.get("nbbh").toString();
		String gsdm=map.get("gsdm_like").toString();
		
		String fgsdm=map.get("fgsdm_like").toString();
		String line =map.get("line_like").toString().trim();
		/*String sql="SELECT a.gsdm as gsdm,a.fgsdm as fgsdm,a.nbbm as nbbm,"
				+ "b.jyggh as jyggh,b.jzl as jzl ,b.stationid as stationid,b.jsy as jsy,"
				+ "b.nylx as nylx,b.yj as yj,b.bz as bz,b.id as id ,c.jsy as ldgh  FROM  bsth_c_cwjy  a "+ 
				   " left  join ("
				   + " select * from bsth_c_ylxxb b "
				   + "where to_days(b.yyrq)=to_days('"+rq+"') and jylx=1  "
				   		+ " and gsdm = '"+gsdm+"') b " +
				  " on a.nbbm=b.nbbm "
				  + "left join (select nbbm,group_concat(jsy) as jsy  "
				  + "from bsth_c_ylb where  to_days(rq)= to_days('"+rq+"' ) "
				  		+ " and ssgsdm='"+gsdm+"' and fgsdm ='"+fgsdm+"'  group by nbbm "+
				   " ) c on a.nbbm=c.nbbm  "
				   + "where  a.nbbm like '%"+nbbm+"%' and a.gsdm='"+gsdm+"' and a.fgsdm ='"+fgsdm+"'"
				   		+ " order by a.nbbm";*/
		String sql= " select * from bsth_c_ylxxb b "
				   + "where to_days(b.yyrq)=to_days('"+rq+"') and jylx=1  "
				   		+ " and gsdm = '"+gsdm+"'";
		
		List<Ylxxb> yList= jdbcTemplate.query(sql,
				 new RowMapper<Ylxxb>(){
			 @Override
			 public Ylxxb  mapRow(ResultSet rs, int rowNum) throws SQLException {  
				 Ylxxb t=new Ylxxb();
				 t.setId(rs.getInt("id"));
				 t.setGsdm(rs.getString("gsdm"));
				 t.setFgsdm(rs.getString("fgsdm"));
				 t.setNbbm(rs.getString("nbbm"));
				 t.setJyggh(rs.getString("jyggh"));
				 t.setJsy(rs.getString("jsy"));
				 t.setJzl(rs.getDouble("jzl"));
				 t.setStationid(rs.getString("stationid"));
				 t.setNylx(rs.getInt("nylx"));
				 t.setYj(rs.getDouble("yj"));
				 t.setBz(rs.getString("bz"));
				 return t;
			 }
		});
		List<Cwjy> cwList=null;
		if(line.equals("")){
			cwList = repository.selectAll(nbbm, gsdm, fgsdm);
		}else{
			cwList = repository.selectAll_s(nbbm, gsdm, fgsdm, line);
		}
				
		String sql_ylb="SELECT nbbm,group_concat(jsy) AS jsy FROM bsth_c_ylb WHERE "
				+ " rq = '"+rq+"' AND ssgsdm = '"+gsdm+"' AND "
				+ " fgsdm = '"+fgsdm+"' GROUP BY nbbm";
		
		List<Map<String, String>> ylbList= jdbcTemplate.query(sql_ylb,
				 new RowMapper<Map<String, String>>(){
			 @Override
			 public Map<String, String>  mapRow(ResultSet rs, int rowNum) throws SQLException {  
				 Map<String, String> maps =new HashMap<String, String>();
				 maps.put("nbbm", rs.getString("nbbm"));
				 maps.put("ldgh", rs.getString("jsy"));
				 return maps;
			 }
		});
		
		List<Ylxxb> ylxxbList= new ArrayList<Ylxxb>();
		Ylxxb y=null;
		for (int i = 0; i < cwList.size(); i++) {
			Cwjy cwjy=cwList.get(i);
			y=new Ylxxb();
			y.setNylx(0);
			y.setId(0);
			y.setJzl(0.0);
			y.setGsdm(cwjy.getGsdm());
			y.setFgsdm(cwjy.getFgsdm());
			y.setNbbm(cwjy.getNbbm());
			y.setLinename(cwjy.getLinename());
			for (int j = 0; j < yList.size(); j++) {
				Ylxxb ylxxb=yList.get(j);
				if(y.getNbbm().equals(ylxxb.getNbbm())){
					y.setId(ylxxb.getId());
					y.setJyggh(ylxxb.getJyggh());
				    y.setJsy(ylxxb.getJsy());
				    y.setJzl(ylxxb.getJzl());
				    y.setStationid(ylxxb.getStationid());
				    y.setNylx(ylxxb.getNylx());
				    y.setYj(ylxxb.getYj());
				    y.setBz(ylxxb.getBz());
				    break;
				}
			}
			for (int j = 0; j < ylbList.size(); j++) {
				Map<String, String> m=ylbList.get(j);
				if(m.get("nbbm").toString().equals(cwjy.getNbbm())){
					y.setLdgh(m.get("ldgh").toString());
					break;
				}
				
			}
			
			ylxxbList.add(y);
			
		}
		
		return ylxxbList;
	}

	@Override
	public int checkNbbm(Cwjy t) {
		// TODO Auto-generated method stub
		String sql="select count(*) from bsth_c_cwjy where nbbm ='"+t.getNbbm()+"'"
				+ " and gsdm ='"+t.getFgsdm()+"' and fgsdm ='"+t.getFgsdm()+"'";
		int cs=jdbcTemplate.queryForObject(sql, Integer.class);
		
		return cs;
	}

	@Override
	public List<Ylxxb> cwjyList(Map<String, Object> map) {
		String rq=map.get("rq").toString();
		String nbbm=map.get("nbbh").toString();
		String gsdm=map.get("gsdm_like").toString();
		
		String fgsdm=map.get("fgsdm_like").toString();
		String line =map.get("line_like").toString().trim();
		boolean type=false;
		if(map.get("type")!=null){
			type=true;
		}
		String sql="select a.nbbm,a.gsdm,a.fgsdm,IFNULL(b.id,0) as id,IFNULL(b.jzl,0) as jzl,"
		+ " IFNULL(b.jyggh,0) as jyggh,IFNULL(b.jsy,0) as jsy,IFNULL(b.stationid,0) as stationid ,"
		+ " IFNULL(b.yj,0) as yj,IFNULL(b.nylx,0) as nylx,IFNULL(b.bz,'') as bz,IFNULL(c.jsy,'') as ldgh ,"
		+ " IFNULL(c.xlname,c.linename) as xlname  from "
		+ " (select * from bsth_c_cwjy where  gsdm='"+gsdm+"' and fgsdm='"+fgsdm+"') a "
		+ " left join (select * from bsth_c_ylxxb where yyrq='"+rq+"' and gsdm='"+gsdm+"' and jylx=1) b"
		+ " on a.nbbm=b.nbbm LEFT JOIN (select u.nbbm,u.jsy,v.linename,v.xlname,v.xlbm from(select a.nbbm,GROUP_CONCAT(a.jsy) as jsy "
		+ " from (select nbbm,jsy from bsth_c_ylb x where x.rq='"+rq+"' "
		+ " and x.ssgsdm='"+gsdm+"' and x.fgsdm='"+fgsdm+"' group by nbbm,jsy ) a  group by a.nbbm) u"
		+ " LEFT JOIN(select o.nbbm,GROUP_CONCAT(o.xlbm) xlbm ,GROUP_CONCAT(o.linename) as xlname,"
		+ " GROUP_CONCAT(p.`name`)  as linename from (select nbbm,xlbm,linename from bsth_c_ylb "
		+ " where rq='"+rq+"' and ssgsdm='"+gsdm+"' and fgsdm='"+fgsdm+"' group by nbbm,xlbm,linename) o "
		+ " LEFT JOIN bsth_c_line p on o.xlbm=p.line_code group by o.nbbm ) v on u.nbbm=v.nbbm) c "
		+ " on a.nbbm=c.nbbm where 1=1 ";
		if(!nbbm.equals("")){
			sql +=" and a.nbbm like '%"+nbbm+"%'";
		}
		if(!line.equals("")){
			sql += " and c.xlbm ='"+line+"'";
		}
		if(type){
			sql +=" and c.xlbm is not null";
		}
		sql +=" order by xlname desc";
		List<Ylxxb> yList= jdbcTemplate.query(sql,
				 new RowMapper<Ylxxb>(){
			 @Override
			 public Ylxxb  mapRow(ResultSet rs, int rowNum) throws SQLException {  
				 Ylxxb t=new Ylxxb();
				 t.setId(rs.getInt("id"));
				 t.setGsdm(rs.getString("gsdm"));
				 t.setFgsdm(rs.getString("fgsdm"));
				 t.setNbbm(rs.getString("nbbm"));
				 t.setJyggh(rs.getString("jyggh"));
				 t.setJsy(rs.getString("jsy"));
				 t.setJzl(rs.getDouble("jzl"));
				 t.setStationid(rs.getString("stationid"));
				 t.setNylx(rs.getInt("nylx"));
				 t.setYj(rs.getDouble("yj"));
				 t.setBz(rs.getString("bz"));
				 t.setLdgh(rs.getString("ldgh"));
				 t.setLinename(rs.getString("xlname"));
				 return t;
			 }
		});
		return yList;
	}
	
	
	@Override
	public String importExcel(File file, String gsbm, String gsName) {
		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();
				double jzl = Double.valueOf(split[3].trim().length()!=0?split[3]:"0");
				String jyz = split[4].trim();
				String remarks = split[5].trim();
				if(rq.trim().length() == 0){
					rq = sdf.format(new Date());
				}
				
				List<Double> jzl_ = repository.import_queryBySame(gsbm, rq, nbbm);
//				
				if(jzl_.size() == 0){
					repository.import_insertData(gsbm, rq, nbbm, jsy, 
							df.format(jzl), jyz, remarks, sd.format(new Date()));
				}else{
					repository.import_UpdateJzl(df.format(jzl), jsy, jyz, remarks, gsbm, rq, nbbm);
				}
			}
			wb.close();
			fs.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "文件导入失败";
		} finally {
			file.delete();
		}
		return "文件导入成功";
	}
	
	
}