gxwss/src/java/com/data/dao/impl/ImportDataFileDaoImpl.java

package com.data.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import com.core.util.DateUtil;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.StatelessSession;
import org.hibernate.Transaction;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;

import com.base.model.BaseWaterProperties;
import com.base.service.IBaseWaterPropertiesService;
import com.bus.model.BusMeterReadInfo;
import com.bus.model.enumType.OrgImportStatus;
import com.bus.service.IBusCustomerMeterService;
import com.bus.service.IBusOrgService;
import com.core.dao.impl.GenericDaoImpl;
import com.core.util.DBUtils;
import com.core.web.security.ContextUtil;
import com.data.dao.IImportDataFileDao;
import com.data.model.ImportDataFile;
import com.data.model.enumType.ImportStatus;
import com.data.service.IImportDataFileService;
//import org.slf4j.Logger;
//import org.slf4j.LoggerFactory;
/**
 * 数据导入文件持久层接口类
 * 
 * @author lha
 * 
 */
public class ImportDataFileDaoImpl extends GenericDaoImpl<ImportDataFile, Long>
		implements IImportDataFileDao {
	Logger log = LoggerFactory.getLogger(ImportDataFileDaoImpl.class);
	@Autowired
	private org.springframework.jdbc.core.JdbcTemplate jdbcTemplate;
	
	@Autowired
	protected org.hibernate.SessionFactory  sessionFactory;
	
	@Autowired
	private IBusOrgService busOrgService;
	
	@Autowired
	private IBaseWaterPropertiesService baseWaterPropertiesService;
	
	@Autowired
	private IBusCustomerMeterService busCustomerMeter;
	
	@Autowired
	private IImportDataFileService importDataFileService;
	
	private int value = 5000;
	
	enum CheckType{
		customerNumber,
		meterNumber
	}

	public void insertOracleData(String year,ImportDataFile importDataFile,String tablename) {	
		StatelessSession session = sessionFactory.openStatelessSession();
		Transaction tx = session.beginTransaction();
		try {		
			SyncOracleData(year,importDataFile,tablename);
			SQLQuery query = session.createSQLQuery("{CALL bus_org_procedure(?,?)}");
			query.setString(0, year);
			query.setLong(1, importDataFile.getImportId());

			List list = query.list();
			tx.commit();
			session.close();

			importDataFile.setDataStatus(ImportStatus.complete.name());
			importDataFile.setStatusDescription("同步oracle单位信息成功！");
			importDataFileService.update(importDataFile);

		} catch (Exception e) {
			StringBuffer bs = new StringBuffer();
	        bs.append("\n 失败原因: " + e.fillInStackTrace() + "");
			importDataFile.setDataStatus(ImportStatus.fail.name());
			importDataFile.setStatusDescription(bs.toString());
			importDataFileService.update(importDataFile);
			e.printStackTrace();
			tx.rollback();
		}		
	}
	
	private void SyncOracleData(String year,ImportDataFile importDataFile,String tablename)  throws Exception{		
		Statement stmt = null;
		ResultSet rs = null;
		String strsql ="";		
		
		Connection mysqlconn = null;
		
		
		Connection oracleconn = null;
		
		Statement sm = null;			
		
		
		try {
			mysqlconn = DBUtils.getMysqlConn();
			sm = mysqlconn.createStatement();			
			oracleconn = DBUtils.getOracleConn();
			
			stmt = (Statement) oracleconn.createStatement();

			//rs = stmt.executeQuery("SELECT COUNT(*) as count FROM "+tablename);
			//查询数据总数
//			int count = 0;
//			
////			while (rs.next()) 
////			{
////				count = rs.getInt("count");
////			}
//			int last = count % value;
//			int sum = (count - last) / value;
//			int numUp = value;
//			int numQuery = value;

//			for (int j = 0; j < sum + 1; j++)
//			{
//				if (j == sum)
//				{
//					numQuery = last;
//					numUp += last;
//				} else 
//				{
//					if(j==0){
//						numUp = value;
//					}else{
//						numUp += value;
//					}
//				}
//				rs = stmt
//						.executeQuery("	SELECT TOP "
//								+ numQuery
//								+ " *                                                                              "
//								+ "	FROM                                                                           "
//								+ "	(                                                                              "
//								+ "		SELECT TOP "
//								+ numUp
//								+ " a.*                                                                            "
//								+ "			FROM "+tablename+" a                                                          "
//								+ "         ORDER BY a.`HH` ASC                                              "
//								+ "	) ORDER BY `HH` DESC                                                     ");
				
				rs = stmt.executeQuery("SELECT * from "+tablename+" ORDER BY HH ASC ");
				strsql="";
				while (rs.next()) 
				{
						//取access字段值
						String KHH = String.valueOf(rs.getInt("KHH"));
						String HH = String.valueOf(rs.getInt("HH"));
						
						String BZMC = rs.getString("BZMC");
						if(BZMC==null){
						BZMC = "";
						}
						String ZBWZ = rs.getString("ZBWZ");
						if(ZBWZ==null){
							ZBWZ = "";
						}		
						String YYQY = rs.getString("QQYQ");
						if(YYQY==null){
							YYQY = "";
						}
						String LXDH = rs.getString("LXDH");
						if(LXDH==null){
							LXDH = "";
						}
						String ZH = rs.getString("ZH");
						if(ZH==null){
							ZH = "";
						}
						String YSSX = rs.getString("YSSX");
						if(YSSX==null){
							YSSX = "";
						}
//						if(strsql==""){
							strsql = "('"+KHH+"', '"+HH+"', '"+BZMC+"', '"+ZBWZ+"', '"+YYQY+"', '"+LXDH+"', '"+ZH+"', '"+YSSX+"')";
//						}
//						else{
//							strsql += ",('"+KHH+"', '"+HH+"', '"+BZMC+"', '"+ZBWZ+"', '"+YYQY+"', '"+LXDH+"', '"+ZH+"')";
//						}	
						sm.executeUpdate("insert into yhjbxx (KHH, HH, BZMC, ZBWZ, YYQY, LXDH, ZH, YSSX) values "+ strsql);
				}
				//sm.executeUpdate("insert into yhjbxx (KHH, HH, BZMC, ZBWZ, YYQY, LXDH, ZH, YSSX) values "+ strsql);
//			}
			sm.executeUpdate("DELETE Y FROM  yhjbxx Y WHERE EXISTS ( SELECT * FROM `bus_org` bo JOIN `bus_customer_meter` bcm ON bo.`org_id`=bcm.`org_id` WHERE y.bzmc=bo.`org_name` AND y.yssx=(select base_org_type.org_name from base_org_type where base_org_type.`org_type_id` = bo.`org_type_id`) AND y.zbwz=bcm.`address` AND y.khh=bcm.`customer_Number` AND y.hh = bcm.`meter_Number` AND bcm.`year`='"+year+"')");
			sm.close();
			stmt.close();
			oracleconn.close();
			mysqlconn.close();
			DBUtils.closeConnection();
		} 
		catch (Exception e) {
			StringBuffer bs = new StringBuffer();
			//获取当前抛出异常的字符串
	        bs.append("\n" + e.fillInStackTrace() + "");
	        //向上层抛出异常
	        System.out.println("err oracle=============="+e.toString());
		}
		
	}	
	private void insertYhjbxx(String mdbPath,ImportDataFile importDataFile,String year) throws Exception{		
		Statement stmt = null;
		ResultSet rs = null;
		String strsql ="";		
		
		Connection mysqlconn = null;
		Statement sm = null;			
		
		
		try {
		
			mysqlconn = DBUtils.getMysqlConn();
			sm = mysqlconn.createStatement();	
			sm.executeUpdate("truncate table yhjbxx");
			
			//打开对应数据库连接
			Connection conn = DBUtils
					.getConnection(
							DBUtils.DataBaseType.access,
							//"jdbc:odbc:driver={Microsoft Access Driver (*.mdb)}; DBQ="+mdbPath+";charSet=GBK");
							"jdbc:odbc:driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ="+mdbPath+";charSet=GBK");	//64位
			stmt = (Statement) conn.createStatement();
			stmt.executeUpdate("DELETE FROM yhjbxx WHERE ZBWZ like 'YN%'");
			stmt.executeUpdate("alter table yhjbxx add `_AutoID_` AUTOINCREMENT(1,1);");
			rs = stmt
					.executeQuery("SELECT COUNT(*) as count FROM YHJBXX");
			//查询数据总数
			int count = 0;
			while (rs.next()) 
			{
				count = rs.getInt("count");
			}
			int last = count % value;
			int sum = (count - last) / value;
			int numUp = value;
			int numQuery = value;

			for (int j = 0; j < sum + 1; j++)
			{
				if (j == sum)
				{
					numQuery = last;
					numUp += last;
				} else 
				{
					if(j==0){
						numUp = value;
					}else{
						numUp += value;
					}
				}
				rs = stmt
						.executeQuery("	SELECT TOP "
								+ numQuery
								+ " *                                                                              "
								+ "	FROM                                                                           "
								+ "	(                                                                              "
								+ "		SELECT TOP "
								+ numUp
								+ " a.*                                                                            "
								+ "			FROM YHJBXX a                                                          "
								+ "         ORDER BY a.`_AutoID_` ASC                                              "
								+ "	) ORDER BY `_AutoID_` DESC                                                     ");
				strsql="";
				while (rs.next()) 
				{
						//取access字段值
						String KHH = String.valueOf(rs.getInt("KHH"));
						String HH = String.valueOf(rs.getInt("HH"));
						
						String BZMC = rs.getString("BZMC");
						if(BZMC==null){
						BZMC = "";
						}
						String ZBWZ = rs.getString("ZBWZ");
						if(ZBWZ==null){
							ZBWZ = "";
						}		
						String YYQY = rs.getString("YYQY");
						if(YYQY==null){
							YYQY = "";
						}
						String LXDH = rs.getString("LXDH");
						if(LXDH==null){
							LXDH = "";
						}
						String ZH = rs.getString("ZH");
						if(ZH==null){
							ZH = "";
						}
						String YSSX = rs.getString("YSSX");
						if(YSSX==null){
							YSSX = "";
						}
						if(strsql==""){
							strsql = "('"+KHH+"', '"+HH+"', '"+BZMC+"', '"+ZBWZ+"', '"+YYQY+"', '"+LXDH+"', '"+ZH+"', '"+YSSX+"')";
						}
						else{
							strsql += ",('"+KHH+"', '"+HH+"', '"+BZMC+"', '"+ZBWZ+"', '"+YYQY+"', '"+LXDH+"', '"+ZH+"', '"+YSSX+"')";
						}				
				}
				sm.executeUpdate("insert into yhjbxx (KHH, HH, BZMC, ZBWZ, YYQY, LXDH, ZH, YSSX) values "+ strsql);
			}
			sm.executeUpdate("DELETE Y FROM  yhjbxx Y WHERE EXISTS ( SELECT * FROM `bus_org` bo JOIN `bus_customer_meter` bcm ON bo.`org_id`=bcm.`org_id` WHERE y.bzmc=bo.`org_name` AND y.yssx=(select base_org_type.org_name from base_org_type where base_org_type.`org_type_id` = bo.`org_type_id`) AND y.zbwz=bcm.`address` AND y.khh=bcm.`customer_Number` AND y.hh = bcm.`meter_Number` AND bcm.`year`='"+year+"')");
			sm.close();
			stmt.close();
			mysqlconn.close();
			conn.close();			
			DBUtils.closeConnection();
		} 
		catch (Exception e) {
			StringBuffer bs = new StringBuffer();
			//获取当前抛出异常的字符串
	        bs.append("\n" + e.fillInStackTrace() + "");
	        //向上层抛出异常
			throw new Exception(bs.toString());
		}
		
	}		
//	@SuppressWarnings("rawtypes")
	@Override
	public void insertOrgInfoData(String mdbPath,String year,ImportDataFile importDataFile) {
		StatelessSession session = sessionFactory.openStatelessSession();
		Transaction tx = session.beginTransaction();
		try {		
			insertYhjbxx(mdbPath,importDataFile,year);
			SQLQuery query = session.createSQLQuery("{CALL bus_org_procedure(?,?)}");
			query.setString(0, year);
			query.setLong(1, importDataFile.getImportId());
//			query.setInteger(2, 0);
			List list = query.list();
			tx.commit();
			session.close();
//			if("1".equals(list.get(0).toString())){
//				importDataFile.setDataStatus(ImportStatus.fail.name());
//				importDataFile.setStatusDescription("单位基本信息导入失败！");
//				session.update(importDataFile);
//			}else if("2".equals(list.get(0).toString())){
			importDataFile.setDataStatus(ImportStatus.complete.name());
			importDataFile.setStatusDescription("单位基本信息导入成功！");
			importDataFileService.update(importDataFile);
//			}

		} catch (Exception e) {
			StringBuffer bs = new StringBuffer();
	        bs.append("\n 失败原因: " + e.fillInStackTrace() + "");
			importDataFile.setDataStatus(ImportStatus.fail.name());
			importDataFile.setStatusDescription(bs.toString());
			importDataFileService.update(importDataFile);
			e.printStackTrace();
			tx.rollback();
		}
	}
	//**
	
	@Override
	public void insertOracleCbxx(String year,ImportDataFile importDataFile,String month) {
		Statement stmt = null;
		ResultSet rs = null;
		StatelessSession session = sessionFactory.openStatelessSession();
		Transaction tx = session.beginTransaction();
		Set<Integer> setMonth = new HashSet<>();
		try {

			
			Connection conn = DBUtils.getOracleConn();
			stmt = (Statement) conn.createStatement();

//			rs = stmt.executeQuery("SELECT COUNT(0) as count FROM V_CBXX where to_number(to_char(SFYF,'yyyy'))='2020' and to_number(to_char(SFYF,'mm'))='1'");
//			int count = 0; 
//			while (rs.next()) {
//				count = rs.getInt("count");
//			}
//			int last = count % value;
//			int sum = (count - last) / value;
//			int numUp = value;
//			int numQuery = value;

//			for (int j = 0; j < sum + 1; j++) {
//				if (j == sum) {
//					numQuery = last;
//					numUp += last;
//				} else {
//					if(j==0){
//						numUp = value;
//					}else{
//						numUp += value;
//					}
//				}
			rs = stmt.executeQuery("SELECT *  FROM V_CBXX c where to_number(to_char(SFYF,'yyyy'))='2019' and to_number(to_char(SFYF,'mm'))<'5' ORDER BY c.HH,c.SL ASC");
				//rs = stmt.executeQuery("select * from V_CBXX where sfyf>to_date('2019-12-31','yyyy-mm-dd') ");
				 
//								+ numQuery
//								+ " * FROM (SELECT TOP                                               "
//								+ numUp
//								+ " * FROM V_CBXX c where to_number(to_char(SFYF,'yyyy'))='2020' and to_number(to_char(SFYF,'mm'))='1' ORDER BY c.HH,c.SL ASC) ORDER BY c.HH,c.SL DESC");
				while (rs.next()) {
					Integer HH = rs.getInt("HH");
					Date SFYF = rs.getDate("SFYF");
					String XZMC = rs.getString("XZMC");
					Float SL = rs.getFloat("SL");
					Float JBSFDJ = rs.getFloat("JBSFDJ");
					Float PWFDJ = rs.getFloat("PWFDJ");
					SimpleDateFormat formatter = new SimpleDateFormat("yyyy");
					String dateString = formatter.format(SFYF);

					Float zyfdj = rs.getFloat("ZYFDJ");
					Float ysbl = rs.getFloat("YSBL");
					
					BusMeterReadInfo busMeterReadInfo = new BusMeterReadInfo();
					//根据水表查询对应单位
//					BusOrg org = getOrgForUserWaterInfo(session,String.valueOf(HH),year);
//					if(org==null){//没有对应单位的水表标识为待处理pending状态
					busMeterReadInfo.setImportStatus(OrgImportStatus.pending.name());
//					}else{//有对应单位的水表则标识为normal正常状态
//						busMeterReadInfo.setImportStatus(ImportStatus.normal.name());
//					}
					busMeterReadInfo.setPriceMonth(SFYF);
					//抄表月份
					Integer cb_month = Integer.valueOf(DateUtil.getFormatDateTime(SFYF,"MM"));
					//用set记录月份信息
					setMonth.add(cb_month);
					Float baseWaterPrice = null;
					if(XZMC!=null && !"".equals(XZMC)){
						//查找用水性质
						BaseWaterProperties baseWaterProperties = findBaseWaterProperties(session,XZMC);
						//如果用水性质不存在则新增用水性质
						if(baseWaterProperties==null){
							baseWaterPrice = JBSFDJ;
							BaseWaterProperties tmp = new BaseWaterProperties(XZMC,JBSFDJ);
							//tmp.setPropertiesName(XZMC);
							tmp.setWaterPrices(busMeterReadInfo.getMonth(),JBSFDJ);
							tmp.setCreater(ContextUtil.getCurrentUser());
							tmp.setCreateTime(new Date());
							session.insert(tmp);
							busMeterReadInfo.setBaseWaterProperties(findBaseWaterProperties(session,XZMC));
						}else{
							//根据月份拿到水价
							baseWaterPrice = baseWaterProperties.getWaterPrices(busMeterReadInfo.getMonth());
							if (baseWaterPrice == null){
								baseWaterPrice = JBSFDJ;
								baseWaterProperties.setWaterPrices(busMeterReadInfo.getMonth(),JBSFDJ);
								session.update(baseWaterProperties);
							}
							busMeterReadInfo.setBaseWaterProperties(baseWaterProperties);
						}
					}

					Integer quarter = null;
					if(cb_month==1 || cb_month==2 || cb_month==3){
						quarter = 1;
					}
					if(cb_month==4 || cb_month==5 || cb_month==6){
						quarter = 2;
					}
					if(cb_month==7 || cb_month==8 || cb_month==9){
						quarter = 3;
					}
					if(cb_month==10 || cb_month==11 || cb_month==12){
						quarter = 4;
					}
					busMeterReadInfo.setMonth(cb_month);
					busMeterReadInfo.setQuarter(quarter);
					busMeterReadInfo.setWater(SL);
					busMeterReadInfo.setMeterNumber(HH);
					//月份的水价
					busMeterReadInfo.setBaseWaterPrice(baseWaterPrice);
					busMeterReadInfo.setSewagePrice(PWFDJ);
					busMeterReadInfo.setImportId(importDataFile.getImportId());
					busMeterReadInfo.setZyfdj(zyfdj);
					busMeterReadInfo.setYsbl(ysbl);
					
//					//如果有匹配的水表，则该用水信息需要关联单位对象
//					if(org!=null){
//						busMeterReadInfo.setOrg(org);
//					}
					
					
					busMeterReadInfo.setYear(year);
					session.insert(busMeterReadInfo);
				}
//			}
//			tx.commit();
			SQLQuery query = session.createSQLQuery("{CALL update_user_water_info(?)}");
			query.setString(0, year);
			query.list();
			SQLQuery query2 = session.createSQLQuery("{CALL update_quarterly_assessment(?)}");
			query2.setString(0, year);
			query2.list();
			if (setMonth.size()>0){
				SQLQuery query3 = session.createSQLQuery("{CALL update_month_assessment(?,?)}");
				query3.setString(0, year);
				query3.setString(1, StringUtils.join(setMonth.toArray(),","));
				query3.list();
			}
			tx.commit();
			session.close();
//			updateWaterReadInfo(year);
			importDataFile.setDataStatus(ImportStatus.complete.name());
			importDataFile.setStatusDescription("单位月用水量信息导入成功！");
			importDataFileService.update(importDataFile);
		} catch (Exception e) {
			StringBuffer bs = new StringBuffer();
	        bs.append("\n 失败原因: " + e.fillInStackTrace() + "");
			importDataFile.setDataStatus(ImportStatus.fail.name());
			importDataFile.setStatusDescription(bs.toString());
			importDataFileService.update(importDataFile);
			e.printStackTrace();
			tx.rollback();
		}
	}
	
	
	
	@Override
	public void insertMeterReadInfoData(String mdbPath,String year,ImportDataFile importDataFile) {
		Statement stmt = null;
		ResultSet rs = null;
		StatelessSession session = sessionFactory.openStatelessSession();
		Transaction tx = session.beginTransaction();
		Set<Integer> setMonth = new HashSet<>();
		try {
			Connection conn = DBUtils
					.getConnection(
							DBUtils.DataBaseType.access,
							//"jdbc:odbc:driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ="+mdbPath+";charSet=GBK");			
						  //"jdbc:odbc:driver={Microsoft Access Driver (*.mdb)}; DBQ="+mdbPath+";charSet=GBK");	//32位
						  "jdbc:odbc:driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ="+mdbPath+";charSet=GBK");	//64位
			stmt = (Statement) conn.createStatement();
			stmt.executeUpdate("alter table CBXX add `_AutoID_` AUTOINCREMENT(1,1);");
			rs = stmt.executeQuery("SELECT COUNT(*) as count FROM CBXX");
			int count = 0;
			while (rs.next()) {
				count = rs.getInt("count");
			}
			int last = count % value;
			int sum = (count - last) / value;
			int numUp = value;
			int numQuery = value;

			for (int j = 0; j < sum + 1; j++) {
				if (j == sum) {
					numQuery = last;
					numUp += last;
				} else {
					if(j==0){
						numUp = value;
					}else{
						numUp += value;
					}
				}
				rs = stmt
						.executeQuery("SELECT TOP                                                    "
								+ numQuery
								+ " * FROM (SELECT TOP                                               "
								+ numUp
								+ " * FROM CBXX c ORDER BY c.`_AutoID_` ASC) ORDER BY `_AutoID_` DESC");
				while (rs.next()) {
					Integer HH = rs.getInt("HH");
					Date SFYF = rs.getDate("SFYF");
					String XZMC = rs.getString("XZMC");
					Float SL = rs.getFloat("SL");
					Float JBSFDJ = rs.getFloat("JBSFDJ");
					Float PWFDJ = rs.getFloat("PWFDJ");
					BusMeterReadInfo busMeterReadInfo = new BusMeterReadInfo();
					//根据水表查询对应单位
//					BusOrg org = getOrgForUserWaterInfo(session,String.valueOf(HH),year);
//					if(org==null){//没有对应单位的水表标识为待处理pending状态
					busMeterReadInfo.setImportStatus(OrgImportStatus.pending.name());
//					}else{//有对应单位的水表则标识为normal正常状态
//						busMeterReadInfo.setImportStatus(ImportStatus.normal.name());
//					}
					busMeterReadInfo.setPriceMonth(SFYF);
					//抄表月份
					Integer cb_month = Integer.valueOf(DateUtil.getFormatDateTime(SFYF,"MM"));

					//用set记录月份信息
					setMonth.add(cb_month);

					busMeterReadInfo.setMonth(cb_month);
					Float baseWaterPrice = null;
					if(XZMC!=null && !"".equals(XZMC)){
						//查找用水性质
						BaseWaterProperties baseWaterProperties = findBaseWaterProperties(session,XZMC);
						//如果用水性质不存在则新增用水性质
						if(baseWaterProperties==null){
							baseWaterPrice = JBSFDJ;
							BaseWaterProperties tmp = new BaseWaterProperties(XZMC,JBSFDJ);
							tmp.setPropertiesName(XZMC);
							tmp.setWaterPrices(busMeterReadInfo.getMonth(),JBSFDJ);
							tmp.setCreater(ContextUtil.getCurrentUser());
							tmp.setCreateTime(new Date());
							session.insert(tmp);
							busMeterReadInfo.setBaseWaterProperties(findBaseWaterProperties(session,XZMC));
						}else{
							//根据月份拿到水价
							baseWaterPrice = baseWaterProperties.getWaterPrices(busMeterReadInfo.getMonth());
							if (baseWaterPrice == null){
								baseWaterPrice = JBSFDJ;
								baseWaterProperties.setWaterPrices(busMeterReadInfo.getMonth(),JBSFDJ);
								session.update(baseWaterProperties);
							}
							busMeterReadInfo.setBaseWaterProperties(baseWaterProperties);
						}
					}
					Integer quarter = null;
					if(cb_month==1 || cb_month==2 || cb_month==3){
						quarter = 1;
					}
					if(cb_month==4 || cb_month==5 || cb_month==6){
						quarter = 2;
					}
					if(cb_month==7 || cb_month==8 || cb_month==9){
						quarter = 3;
					}
					if(cb_month==10 || cb_month==11 || cb_month ==12){
						quarter = 4;
					}
					busMeterReadInfo.setQuarter(quarter);
					busMeterReadInfo.setWater(SL);
					busMeterReadInfo.setMeterNumber(HH);
					//月份的水价
					busMeterReadInfo.setBaseWaterPrice(baseWaterPrice);
					busMeterReadInfo.setSewagePrice(PWFDJ);
					busMeterReadInfo.setImportId(importDataFile.getImportId());
//					//如果有匹配的水表，则该用水信息需要关联单位对象
//					if(org!=null){
//						busMeterReadInfo.setOrg(org);
//					}
					busMeterReadInfo.setYear(year);
					session.insert(busMeterReadInfo);
				}
			}
//			tx.commit();
			SQLQuery query = session.createSQLQuery("{CALL update_user_water_info(?)}");
			query.setString(0, year);
			query.list();
			SQLQuery query2 = session.createSQLQuery("{CALL update_quarterly_assessment(?)}");
			query2.setString(0, year);
			query2.list();
			if (setMonth.size()>0){
				SQLQuery query3 = session.createSQLQuery("{CALL update_month_assessment(?,?)}");
				query3.setString(0, year);
				query3.setString(1, StringUtils.join(setMonth.toArray(),","));
				query3.list();
			}
			tx.commit();
			session.close();
//			updateWaterReadInfo(year);
			importDataFile.setDataStatus(ImportStatus.complete.name());
			importDataFile.setStatusDescription("单位月用水量信息导入成功！");
			importDataFileService.update(importDataFile);
		} catch (Exception e) {
			StringBuffer bs = new StringBuffer();
	        bs.append("\n 失败原因: " + e.fillInStackTrace() + "");
			importDataFile.setDataStatus(ImportStatus.fail.name());
			importDataFile.setStatusDescription(bs.toString());
			importDataFileService.update(importDataFile);
			e.printStackTrace();
			tx.rollback();
		}
	}


	@Override
	public void insertMeterReadInfoDataByExce(String year, List<String[]> dataList) {

		if (dataList.size() <= 0) {
			return ;
		}
		StatelessSession session = sessionFactory.openStatelessSession();
		Set<Integer> setMonth = new HashSet<>();
		Transaction tx = session.beginTransaction();
		for (int i = 1; i < dataList.size(); i++) {
			//跳过标题头
			String[] item = dataList.get(i);
			Integer HH = Integer.parseInt(item[0].toString());

			String str_SFYF = item[1];
			str_SFYF = str_SFYF.replaceAll("/","-");
			Date SFYF = null;
			try {
				SFYF = DateUtil.parseDate(str_SFYF,new String[]{"yyyy-MM-dd"});
			} catch (ParseException e) {
				e.printStackTrace();
			}
			String XZMC = item[2];
			Float SL = Float.valueOf(item[3]);
			Float JBSFDJ = Float.valueOf(item[4]);
			Float PWFDJ = Float.valueOf(item[5]);
			BusMeterReadInfo busMeterReadInfo = new BusMeterReadInfo();
			//根据水表查询对应单位
//					BusOrg org = getOrgForUserWaterInfo(session,String.valueOf(HH),year);
//					if(org==null){//没有对应单位的水表标识为待处理pending状态
			busMeterReadInfo.setImportStatus(OrgImportStatus.pending.name());
//					}else{//有对应单位的水表则标识为normal正常状态
//						busMeterReadInfo.setImportStatus(ImportStatus.normal.name());
//					}
			busMeterReadInfo.setPriceMonth(SFYF);
			//抄表月份
			Integer cb_month = Integer.valueOf(DateUtil.getFormatDateTime(SFYF,"MM"));
			//用set记录月份信息
			setMonth.add(cb_month);
			busMeterReadInfo.setMonth(cb_month);
			Float baseWaterPrice = null;
			if(XZMC!=null && !"".equals(XZMC)){
				//查找用水性质
				BaseWaterProperties baseWaterProperties = findBaseWaterProperties(session,XZMC);
				//如果用水性质不存在则新增用水性质
				if(baseWaterProperties==null){
					baseWaterPrice = JBSFDJ;
					BaseWaterProperties tmp = new BaseWaterProperties();
					tmp.setPropertiesName(XZMC);
					tmp.setWaterPrices(busMeterReadInfo.getMonth(),JBSFDJ);
					tmp.setCreater(ContextUtil.getCurrentUser());
					tmp.setCreateTime(new Date());
					session.insert(tmp);
					busMeterReadInfo.setBaseWaterProperties(findBaseWaterProperties(session,XZMC));
				}else{
					//根据月份拿到水价
					baseWaterPrice = baseWaterProperties.getWaterPrices(busMeterReadInfo.getMonth());
					if (baseWaterPrice == null){
						baseWaterPrice = JBSFDJ;
						baseWaterProperties.setWaterPrices(busMeterReadInfo.getMonth(),JBSFDJ);
						session.update(baseWaterProperties);
					}
					busMeterReadInfo.setBaseWaterProperties(baseWaterProperties);
				}
			}
			Integer quarter = null;
			if(cb_month==1 || cb_month==2 || cb_month==3){
				quarter = 1;
			}
			if(cb_month==4 || cb_month==5 || cb_month==6){
				quarter = 2;
			}
			if(cb_month==7 || cb_month==8 || cb_month==9){
				quarter = 3;
			}
			if(cb_month==10 || cb_month==11 || cb_month ==12){
				quarter = 4;
			}
			busMeterReadInfo.setQuarter(quarter);
			busMeterReadInfo.setWater(SL);
			busMeterReadInfo.setMeterNumber(HH);
			//月份的水价
			busMeterReadInfo.setBaseWaterPrice(baseWaterPrice);
			busMeterReadInfo.setSewagePrice(PWFDJ);
			busMeterReadInfo.setImportId(0L);
			busMeterReadInfo.setYear(year);
			session.insert(busMeterReadInfo);
		}
		SQLQuery query = session.createSQLQuery("{CALL update_user_water_info(?)}");
		query.setString(0, year);
		query.list();
		SQLQuery query2 = session.createSQLQuery("{CALL update_quarterly_assessment(?)}");
		query2.setString(0, year);
		query2.list();
		if (setMonth.size()>0){
			String monthStr = StringUtils.join(setMonth.toArray(),",");
			log.info(monthStr);
			SQLQuery query3 = session.createSQLQuery("{CALL update_month_assessment(?,?)}");
			query3.setString(0, year);
			query3.setString(1, monthStr);
			query3.list();
		}

		tx.commit();
		session.close();
	}

	/**
	 * 查找用水性质
	 * @param session
	 * @param XZMC
	 * @return
	 */
	@SuppressWarnings("unchecked")
	private BaseWaterProperties findBaseWaterProperties(StatelessSession session,String XZMC) {
		List<BaseWaterProperties> list = new ArrayList<BaseWaterProperties>();
		try {
			Query dataItor = session.createQuery("SELECT b FROM BaseWaterProperties b WHERE propertiesName='"+XZMC+"'");
			list = (List<BaseWaterProperties>)dataItor.list();
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(list.size()>0){
			return list.get(0);
		}else{
			return null;
		}
	}

//	private void updateWaterReadInfo(String year) {
//		RowMapper<Object[]> rowMapper = new RowMapper<Object[]>() {
//
//			@Override
//			public Object[] mapRow(ResultSet rs, int index) throws SQLException {
//				Object[] obj = new Object[3];
//				obj[0] = rs.getInt("org_number");
//				obj[1] = rs.getLong("org_id");
//				obj[2] = rs.getInt("meter_Number");
//				return obj;
//			}
//
//		};
//		
//		String sql = "SELECT bo.`org_number`,bo.`org_id`,bcm.`meter_Number` FROM `bus_org` bo JOIN `bus_customer_meter` bcm ON bo.`org_id`=bcm.`org_id` WHERE bo.`year`=?";
//		List<Object[]> list = jdbcTemplate.query(sql, rowMapper,year);
//		for (int i = 0; i < list.size(); i++) {
//				Object[] object = list.get(i);
//				String orgNumber = "";
//				if(object[0]!=null){
//					orgNumber = object[0].toString();
//				}
//				String s = 
//						"UPDATE `bus_meter_read_info` SET `import_status`='normal',`org_id`=?,`org_number`=? WHERE `import_status`='pending' AND `meter_number`=? AND year=?" ;
//				jdbcTemplate.update(s, new Object[]{Long.valueOf(object[1].toString()),orgNumber,object[2].toString(),year});
//		}
//		
//		jdbcTemplate.update("DELETE FROM `bus_meter_read_info` WHERE `import_status`='pending'");
//	}
}