package cn.com.countrygarden.bi.main.repository.erp;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.com.countrygarden.bi.main.exception.ServiceException;
import cn.com.countrygarden.bi.main.model.erp.FeesSerial;
import cn.com.countrygarden.bi.main.model.erp.JdbcConstants;
import cn.com.countrygarden.bi.main.model.erp.RealAmountMsg;

/**
 * @Author christ.lai
 * @Date 2018-05-31
 * @Descripe 实收dao
 */

public class JdbcRealRepository {

	private final static Logger logger = LoggerFactory.getLogger(JdbcRealRepository.class);

	/**
	 * @param jdbcConstants
	 *            前台传输的参数，包含数据库连接的参数
	 * @param stringList
	 *            读取excel解析出来的list
	 */
	public static void jdbcConnect(JdbcConstants jdbcConstants, List<RealAmountMsg> stringList) {

		Connection conn = null;

		Statement stmt = null;

		ResultSet rs = null;

		Set<Integer> commidSet = new HashSet<Integer>(); // 项目编号
		
		List<FeesSerial> fslist = null; //feesSerial 费用流水数据

		CallableStatement cs = null;// 调用存储过程使用的接口
		
		JdbcPublicRepository jpr = new JdbcPublicRepository(jdbcConstants); //工具类
		try {

			// 加载驱动类
			logger.info("驱动" + jdbcConstants.getDriver());
			logger.info("URL" + jdbcConstants.getUrl());
			logger.info("用户名" + jdbcConstants.getUsername());
			logger.info("密码" + jdbcConstants.getPassword());
			Class.forName(jdbcConstants.getDriver());

			conn = DriverManager.getConnection(jdbcConstants.getUrl(), jdbcConstants.getUsername(),
					jdbcConstants.getPassword());

			conn.setAutoCommit(false); // 设为手动提交

			stmt = conn.createStatement();
			
			Long timeStamp = System.currentTimeMillis(); //时间戳
            String templeTable = "##t_real_amount_msg_"+timeStamp;
            System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"#########################新建临时表名："+templeTable+"......");
			
            String checktable = "select * from tempdb..sysobjects where id=object_id('tempdb.."+templeTable+"');\n";
			rs = stmt.executeQuery(checktable);
			// 不存在就创建
			if (!rs.next()) {
				// 临时表
				String createTempTable ="CREATE TABLE "+templeTable+" ( \n"
						+ "id bigint NOT NULL PRIMARY KEY IDENTITY (1, 1) , \n" 
						+ "roomid bigint DEFAULT NULL , \n"
						+ "custid bigint DEFAULT NULL , \n" 
						+ "costid bigint DEFAULT NULL , \n"
						+ "parkid bigint DEFAULT NULL , \n" 
						+ "handid bigint DEFAULT NULL , \n"
						+ "commid INT DEFAULT NULL , \n" 
						+ "cwbh VARCHAR (255) DEFAULT NULL , \n"
						+ "fwbh VARCHAR (255) DEFAULT NULL , \n" 
						+ "fwmc VARCHAR (255) DEFAULT NULL , \n"
						+ "fymc VARCHAR (255) DEFAULT NULL , \n" 
						+ "fyrq datetime DEFAULT NULL , \n"
						+ "jssj datetime DEFAULT NULL , \n" 
						+ "khlb VARCHAR (255) DEFAULT NULL , \n"
						+ "khmc VARCHAR (255) DEFAULT NULL , \n" 
						+ "kssj VARCHAR (255) DEFAULT NULL , \n"
						+ "sjh VARCHAR (255) DEFAULT NULL , \n" 
						+ "skfs VARCHAR (255) DEFAULT NULL , \n"
						+ "skr VARCHAR (255) DEFAULT NULL , \n" 
						+ "sksj VARCHAR (255) DEFAULT NULL , \n"
						+ "je NUMERIC(18,2) DEFAULT NULL , \n" 
						+ "yjrq VARCHAR (255) DEFAULT NULL , \n"
						+ "bz VARCHAR (255) DEFAULT NULL ); \n";

				// /创建临时表
				stmt.addBatch(createTempTable);
				logger.info("临时表" + createTempTable);
			}
			
			String insert = "insert into "+templeTable+" (commid,khmc,fwbh,cwbh,fymc,fyrq,yjrq,kssj,je,skfs,skr,sksj,sjh,bz,khlb) values ";
            int count = 0;
            StringBuffer values = new StringBuffer();
            String insert_sql = ""; //最后拼接出来的insert语句
			
			for (RealAmountMsg ra : stringList) {
				values.append("(");
				if (ra.getCommid() != null) {
					values.append(ra.getCommid()+",");
				}else{
					values.append(null+",");
				}
				if(ra.getKhmc() != null){
					values.append("'"+ra.getKhmc()+"' ,");
				}else{
					values.append(null+",");
				}
				if(ra.getFwbh() != null){
					values.append("'"+ra.getFwbh()+"' ,");
				}else{
					values.append(null+",");
				}
				if(ra.getCwbh() != null){
					values.append("'"+ra.getCwbh()+"' ,");
				}else{
					values.append(null+",");
				}
				if(ra.getFymc() != null){
					values.append("'"+ra.getFymc()+"' ,");
				}else{
					values.append(null+",");
				}
				if(ra.getFyrq() != null){
					values.append("'"+ra.getFyrq()+"' ,");
				}else{
					values.append(null+",");
				}
				if(ra.getYjrq() != null){
					values.append("'"+ra.getYjrq()+"' ,");
				}else{
					values.append(null+",");
				}
				if(ra.getKssj() != null){
					values.append("'"+ra.getKssj()+"' ,");
				}else{
					values.append(null+",");
				}
				if(ra.getJe() != null){
					values.append("'"+ra.getJe()+"' ,");
				}else{
					values.append(null+",");
				}
				if(ra.getSkfs() != null){
					values.append("'"+ra.getSkfs()+"' ,");
				}else{
					values.append(null+",");
				}
				if(ra.getSkr() != null){
					values.append("'"+ra.getSkr()+"' ,");
				}else{
					values.append(null+",");
				}
				if(ra.getSksj() != null){
					values.append("'"+ra.getSksj()+"' ,");
				}else{
					values.append(null+",");
				}
				if(ra.getSjh() != null){
					values.append("'"+ra.getSjh()+"' ,");
				}else{
					values.append(null+",");
				}
				if(ra.getBz() != null){
					values.append("'"+ra.getBz()+"' ,");
				}else{
					values.append(null+",");
				}
				if(ra.getKhlb() != null){
					values.append("'"+ra.getKhlb()+"' ),");
				}else{
					values.append(null+") ,");
				}
				
				count ++; //循环一次,计数器加一
				if(count % 1000 == 0){
					insert_sql = insert + values.substring(0,values.lastIndexOf(",")).toString();//去掉末尾的逗号,拼接成完整的insert语句
	                stmt.addBatch(insert_sql);//添加到批处理
	                count = 0 ; //还原计数器
					values = new StringBuffer(); //还原StringBuffer
	                logger.info("待插入数据" + insert_sql);
				}
				commidSet.add(ra.getCommid());
			}
			//将1000取余剩下的部分拼接成insert,添加到批处理
            if(count > 0){
				insert_sql = insert + values.substring(0,values.lastIndexOf(",")).toString();//去掉末尾的逗号,拼接成完整的insert语句
                stmt.addBatch(insert_sql);//添加到批处理
                count = 0 ; //还原计数器
				values = new StringBuffer(); //还原StringBuffer
            }
            stmt.executeBatch();
            System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+" :临时表插入结束.......");

			// 从临时表中拿到数据
			for (Integer commid : commidSet) {
				String checkProcedure = "select * from dbo.sysobjects where id = object_id(N'[dbo].[UPDATE_REAL_PROCE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1 \n";
				rs = stmt.executeQuery(checkProcedure);
				// 不存在就创建
				if (!rs.next()) {
					// 创建存储过程SQL语句 ： 目的是为了取别的表上的数据补充完整临时表的数据
					String UPDATE_REAL_PROCE = "create procedure UPDATE_REAL_PROCE(@ID bigint,@tableName varchar(255)) \n" + "AS \n"
							+ "declare @commid bigint \n" + "set @commid=@ID \n"
							+ "declare @templeTable varchar(255) \n"
                    		+ "set @templeTable = @tableName \n"
							+ "exec('update a set a.roomid=b.roomid from '+@templeTable+' a,Tb_HSPR_Room b where b.commid='+@CommID+' and a.fwbh=b.roomsign and IsDelete=0') \n"
							+ "exec('update a set a.custid=b.custid from '+@templeTable+' a,tb_hspr_customerlive b where b.commid='+@CommID+' and a.roomid=b.roomid and isactive=1 and khlb like ''%业主%''')\n"
							+ "exec('update a set a.costid=b.costid from '+@templeTable+' a,Tb_HSPR_CostItem b where b.commid='+@CommID+' and a.fymc=b.costname and IsDelete=0') \n"
							+ "exec('update a set a.custid=b.custid from '+@templeTable+' a,Tb_HSPR_customer b where b.commid='+@CommID+' and a.khmc=b.custname and IsDelete=0 and isnull(a.custid,'''')=''''') \n"
							+ "exec('update a set a.parkid=b.parkid from '+@templeTable+' a,tb_hspr_parking b where b.commid='+@CommID+' and a.cwbh=b.parkname and IsDelete=0 ')\n"
							+ "exec('update a set a.handid=b.handid from '+@templeTable+' a,tb_hspr_parkinghand b where b.commid='+@CommID+' and a.parkid=b.parkid and IsDelete=0;') \n";
					logger.info("补充完整临时表" + UPDATE_REAL_PROCE);
					// /创建存储过程
					stmt.executeUpdate(UPDATE_REAL_PROCE);
				}
				// 调用存储过程
				cs = conn.prepareCall("{call UPDATE_REAL_PROCE(" + commid + ","+templeTable+")}");
				// 调用的结果集
				cs.executeUpdate();
				System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"#########################调用存储过程："+"UPDATE_REAL_PROCE(" + commid + ")"+"......");
				//取得执行Proc_INSERT_REAL 之前，最大feesId
				long startId = new JdbcPublicRepository(jdbcConstants).getMaxFeesId(conn, commid);
				
				//查询Proc_INSERT_REAL是否已经创建
				String checkProcedure2 = "select * from dbo.sysobjects where id = object_id(N'[dbo].[Proc_INSERT_REAL]') and OBJECTPROPERTY(id, N'IsProcedure') = 1; \n";
				rs = stmt.executeQuery(checkProcedure2);
				// 不存在就创建
				if (!rs.next()) {
					// 各实体表在临时表里抽取数据
					String Proc_INSERT_REAL = "create procedure Proc_INSERT_REAL(@ID bigint,@tableName varchar(255)) \n" + "as \n"
							+ "Declare @ReceID bigint \n" 
							+ "Declare @TemReceID bigint \n" 
							+ "Declare @FeesID bigint \n"
							+ "Declare @RecdID bigint \n" 
							+ "Declare @CommID int \n" 
							+ "Declare @CustID bigint \n"
							+ "Declare @RoomID bigint \n" 
							+ "Declare @CostID bigint \n" 
							+ "Declare @parkid bigint \n"
							+ "Declare @handid bigint \n" 
							+ "Declare @CustName nvarchar(20) \n"
							+ "Declare @RoomSign nvarchar(50) \n" 
							+ "Declare @UserCode nvarchar(20) \n"
							+ "Declare @UserName nvarchar(50) \n" 
							+ "Declare @ReceMemo nvarchar(50) \n"
							+ "Declare @FeesMemo nvarchar(50) \n" 
							+ "Declare @BillsSign nvarchar(50) \n"
							+ "Declare @ChargeMode nvarchar(50) \n" 
							+ "Declare @MeterSign nvarchar(50) \n"
							+ "Declare @FeesDueDate datetime \n" 
							+ "Declare @FeesStateDate datetime \n"
							+ "Declare @FeesEndDate datetime \n" 
							+ "Declare @ChargeDate Datetime \n"
							+ "Declare @ChargeAmount numeric(18,2) \n" 
							+ "Declare @Skid bigint  \n" 
							+ "declare @templeTable varchar(255) \n"
                    		+ "set @templeTable = @tableName \n"
							+"Set @CommID = @ID \n"
							+"SELECT @FeesID = isnull(max(FeesID),(@CommID* CAST(1000000000000 AS bigint)) ) FROM Tb_HSPR_Fees \n"
							+"where CommID = @CommID \n"
							+"if(@FeesID<	(SELECT isnull(max(FeesID),(@CommID* CAST(1000000000000 AS bigint)) ) \n"
							+"FROM Tb_HSPR_Feeshis \n"
							+"where CommID = @CommID)) \n"
							+"begin \n"
							+"SELECT @FeesID=isnull(max(FeesID),(@CommID* CAST(1000000000000 AS bigint)) ) \n"
							+"FROM Tb_HSPR_Feeshis \n"
							+"where CommID = @CommID \n"
							+"end \n"
							+"select @RecdID= isnull(max(recdid),(@CommID* CAST(1000000000000 AS bigint)))  from tb_hspr_feesdetail where commid=@CommID \n"
							+"select @TemReceID= isnull(max(receid),(@CommID* CAST(1000000000000 AS bigint)))  from tb_hspr_feesreceipts where commid=@CommID \n"
							+"exec('DECLARE t_RoomSign CURSOR FOR " 
							+"select custid,isnull(RoomID,0) as RoomID, costid, isnull(parkid,0), isnull(handid,0)"
							+",NULL,fyrq, kssj,jssj, sjh, skr,je, skfs,sksj, ''批量导入'',''批量导入'',0 "
							+"from '+@templeTable+' "
							+"where custid is not null and CostID is not null and isnull(je,0)<>0 "
							+"order by custid,roomid,sjh,costid ') \n"
							
							+"OPEN t_RoomSign \n" 
							+ "FETCH NEXT FROM t_RoomSign INTO \n"
							+"@CustID,@RoomID,@CostID,@parkid,@handid, @metersign,@FeesDueDate, @FeesStateDate,@FeesEndDate \n"
							+",@BillsSign, @UserName,@ChargeAmount, @ChargeMode,@ChargeDate, @ReceMemo,@FeesMemo,@Skid \n"
							+"WHILE @@FETCH_STATUS = 0 \n" 
							+"BEGIN \n" 

							// --select * From Tb_Sys_User \n"+
							// --插入收款人
							+"if exists(select * From Tb_Sys_User \n" + "	where UserName= @UserName) \n"
								+ "begin \n"
								+ "SELECT @UserCode = UserCode From Tb_Sys_User \n"
								+ "Where UserName = @UserName \n" 
								+ "end \n" 
							+ "else \n" 
								+ "begin \n"
								+ "Set @UserCode = '' \n" 
								+ "Set @UserName = '' \n" 
								+ "end \n" 
							// 插入费用日期
							+"if(isnull(@FeesEndDate,'')='') \n" 
							+"begin \n"
							+"Set @FeesStateDate = @FeesDueDate \n"
							+"Set @FeesEndDate = dateadd(d,-day(@FeesDueDate),dateadd(m,1,@FeesDueDate)) \n"
							+"end \n"
							+"--收据 \n"
							+"if exists(select * from Tb_HSPR_FeesReceipts \n"
							+"where CommID = @CommID and CustID = @CustID and RoomID = @RoomID \n"
							+"and BillsSign = @BillsSign and datediff(day,BillsDate,@ChargeDate)=0) \n"
							+"begin \n"		
							+"SELECT @ReceID = ReceID \n"
							+"FROM Tb_HSPR_FeesReceipts \n"
							+"where CommID = @CommID and CustID = @CustID and RoomID = @RoomID \n"
							+"and BillsSign = @BillsSign and datediff(day,BillsDate,@ChargeDate)=0 \n"
							+"and IsDelete = 0 \n"
							+"update Tb_HSPR_FeesReceipts \n"
							+"set PaidAmount = PaidAmount + @ChargeAmount \n"
							+"where ReceID = @ReceID \n"
							+"and IsDelete = 0 \n"
							+"end \n"
							+"else \n" 
							+"begin \n"
							+"Select @ReceID = isnull(max(ReceID),@TemReceID) \n"
							+"from Tb_HSPR_FeesReceipts \n" 
							+"Where CommID = @CommID \n"
							// 收据Tb_HSPR_FeesReceipts
							+"Set @ReceID = @ReceID + 1 \n" 
							+"INSERT INTO Tb_HSPR_FeesReceipts \n"
							+"(ReceID,CommID,CustID,RoomID,BillsSign,PrintTimes \n"
							+",BillsDate,UserCode,ChargeMode,AccountWay,ReceMemo,PerSurplus \n"
							+",SurplusAmount,PrecAmount,PaidAmount,IsDelete,UseRepID,BillTypeID) \n"
							+"VALUES \n"
							+"(@ReceID,@CommID,@CustID,@RoomID,@BillsSign,1 \n"
							+",@ChargeDate,@UserCode,@ChargeMode,15,@ReceMemo,0 \n"
							+",0,0,@ChargeAmount,0,0,0) \n" 
							+"end \n" 
							// 明细Tb_HSPR_Fees
							+"Set @FeesID = @FeesID + 1 \n" 
							+"INSERT INTO Tb_HSPR_Fees \n"
							+"(FeesID,CommID,CostID,CustID,RoomID,parkid,FeesDueDate \n"
							+",FeesStateDate,FeesEndDate,DueAmount,PaidAmount,DebtsAmount \n"
							+",IsAudit,AccountFlag,IsBank,IsCharge,FeesMemo,AccountsDueDate,MeterSign \n"
							+",StanMemo,HandID,IsFreeze,IsProPerty,IsPrec) \n" + "	VALUES \n"
							+"(@FeesID,@CommID,@CostID,@CustID,@RoomID,@parkid,@FeesDueDate \n"
							+",@FeesStateDate,@FeesEndDate,@ChargeAmount,@ChargeAmount,0,0 \n"
							+",15,0,1,@FeesMemo,@FeesDueDate,@MeterSign,'',@handid,0,0,0) \n"
							// 入库Tb_HSPR_FeesDetail
							+"Set @RecdID = @RecdID + 1 \n" + "	INSERT INTO Tb_HSPR_FeesDetail \n"
							+"(RecdID,CommID,CostID,CustID,RoomID,FeesID,ReceID \n"
							+",ChargeMode,AccountWay,ChargeAmount,LateFeeAmount \n"
							+",ChargeDate,FeesMemo,IsDelete,OldCostID) \n" + "	VALUES \n"
							+"(@RecdID,@CommID,@CostID,@CustID,@RoomID,@FeesID,@ReceID \n"
							+",@ChargeMode,15,@ChargeAmount,0 \n" 
							+",@ChargeDate,@FeesMemo,0,@Skid) \n" 
							// -- 游标赋值
							+"FETCH NEXT FROM t_RoomSign INTO @CustID,@RoomID,@CostID,@parkid,@handid,@MeterSign,@FeesDueDate,@FeesStateDate,@FeesEndDate,@BillsSign,@UserName,@ChargeAmount,@ChargeMode \n"
							+",@ChargeDate,@ReceMemo,@FeesMemo,@Skid \n" + "END \n"
							// -- 关闭游标
							+"CLOSE t_RoomSign \n"
							// -- 释放游标
							+"DEALLOCATE t_RoomSign; \n";
					logger.info("回填数据存储过程" + Proc_INSERT_REAL);
					// /创建存储过程
					stmt.executeUpdate(Proc_INSERT_REAL);
				}
				cs = conn.prepareCall("{call Proc_INSERT_REAL(" + commid + ","+templeTable+")}");
				cs.execute();
				System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"#########################调用存储过程："+"Proc_INSERT_REAL(" + commid + ")"+"......");
				//取得执行Proc_INSERT_REAL 之后，最大feesId
				long endId = jpr.getMaxFeesId(conn, commid);
				//获得FeesSerial数据集合
				fslist =  jpr.getFeesSerial(conn, commid, startId, endId);
				//调用 importFeesSerial 接口
				importFeesSerial(jdbcConstants, fslist);
			}
			conn.commit(); // 提交事务
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new ServiceException("数据库异常：" + e.getMessage());
		} catch (SQLException e) {
			e.printStackTrace();
			throw new ServiceException("数据库异常：" + e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * @author Austin
	 * @param jdbcConstants
	 * @param stringList
	 * @description 创建10.10.171.177服务器的Rts_feesSerial数据库连接，创建临时表，插入数据，执行存储过程
	 */
	public static void importFeesSerial(JdbcConstants jdbcConstants, List<FeesSerial> stringList) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		CallableStatement cs = null;// 调用存储过程使用的接口
		Set<Integer> commidSet = new HashSet<Integer>(); // 项目编号
		SimpleDateFormat timeFormat = new SimpleDateFormat("yyyy/MM/dd"); //Date 转 datetime
		try{
			// 加载驱动类
			logger.info("驱动" + jdbcConstants.getDriver());
			logger.info("URL" + jdbcConstants.getUrl());
			logger.info("用户名" + jdbcConstants.getUsername());
			logger.info("密码" + jdbcConstants.getPassword());
			Class.forName(jdbcConstants.getDriver());
			conn = DriverManager.getConnection(jdbcConstants.getRealUrl(), jdbcConstants.getUsername(),
					jdbcConstants.getPassword()); //注意：这边的URL 是连接到10.10.171.177 服务器
			conn.setAutoCommit(false); 
			stmt = conn.createStatement();
			Long timeStamp = System.currentTimeMillis(); //时间戳
            String templeTable = "##tb_ccc_feesSerial_"+timeStamp;
            System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"#########################新建临时表名："+templeTable+"......");
			/*
			 * 创建临时表，将jdbcConnection方法
			 * 查询所得结果,导入Rts_feesSerial库
			 * 的临时表 ##tb_ccc_feesSerial 中
			 */
			String checktable = "select * from tempdb..sysobjects where id=object_id('tempdb.."+templeTable+"');\n";
			rs = stmt.executeQuery(checktable);
			// 不存在就创建
			if (!rs.next()) {
				// 临时表
				String createTempTable = "CREATE TABLE "+templeTable+"( \n"
						+"Id varchar(255) DEFAULT NULL , \n"
						+"CommID int  DEFAULT NULL, \n"
						+"FeesID bigint  DEFAULT NULL, \n"
						+"CustID bigint  DEFAULT NULL, \n"
						+"RoomID bigint  DEFAULT NULL, \n"
						+"ParkID bigint  DEFAULT NULL, \n"						
						+"FeesDueDate datetime  DEFAULT NULL, \n"						
						+"BussType varchar(255)  DEFAULT NULL, \n"
						+"DealAmount decimal(18,2) DEFAULT NULL, \n"
						+"DealDate datetime  DEFAULT NULL, \n"					
						+"ChargesModeFeesDueDate varchar(255) DEFAULT NULL, \n"
						+"CostID bigint DEFAULT NULL, \n"
						+"CorpCostID bigint DEFAULT NULL, \n"
						+"StanID bigint DEFAULT NULL, \n"
						+"StanAmount numeric(18,2) DEFAULT NULL, \n"
						+"CorpStanID bigint DEFAULT NULL, \n"
						+"RoomPropertyUses varchar(255) DEFAULT NULL, \n"
						+"RoomPropertyRights varchar(255) DEFAULT NULL, \n"
						+"ParkPropertyRights varchar(255) DEFAULT NULL, \n"					
						+"RoomActualSubDate datetime DEFAULT NULL, \n"
						+"ParkActualSubDate datetime DEFAULT NULL, \n"						
						+"CustLiveType int DEFAULT NULL, \n"
						+"BuildType varchar(255) DEFAULT NULL, \n"
						+"BuildArea numeric(18,2) DEFAULT NULL, \n"
						+"RoomParkCount int DEFAULT NULL, \n"
						+"IsHelpPay varchar(255) DEFAULT NULL, \n"
						+"RoomUseState varchar(255) DEFAULT NULL, \n"
						+"ParkUseState varchar(255) DEFAULT NULL, \n"
						+"LateAmount decimal(18,2) DEFAULT NULL, \n"
						+"TaxRate decimal(18,2) DEFAULT NULL, \n" 
						+"TaxAmount decimal(18,2) DEFAULT NULL, \n" 
						+"Tax decimal(18,2) DEFAULT NULL, \n" 
						+"CustTypeName varchar(255) DEFAULT NULL, \n"					
						+"LateStartDate datetime DEFAULT NULL, \n"						
						+"LatePrice decimal(18,2) DEFAULT NULL, \n"
						+"LateRate decimal(18,2) DEFAULT NULL, \n"
						+"ParkArea numeric(18,2) DEFAULT NULL, \n"
						+"StanFormula int DEFAULT NULL, \n"
						+"RoomState int DEFAULT NULL, \n"
						+"GardenArea numeric(18,2) DEFAULT NULL, \n"
						+"YardArea numeric(18,2) DEFAULT NULL, \n"
						+"ChargeMode varchar(255) DEFAULT NULL, \n"
						+"CommisionAmount numeric(18,2) DEFAULT NULL, \n"
						+"IsWriteOff int DEFAULT NULL, \n"
						+"WriteOffType varchar(255) DEFAULT NULL, \n"
						+"WriteOffUserName varchar(255) DEFAULT NULL, \n"
						+"WriteOffAuditUserName varchar(255) DEFAULT NULL, \n"
						+"WriteOffMeme varchar(255)DEFAULT NULL )";
				//创建临时表
				stmt.addBatch(createTempTable);
				logger.info("临时表" + createTempTable);
			}
			String insert = "insert into "+templeTable+" (Id,commid,FeesID,CustID,RoomID,ParkID,FeesDueDate,BussType,DealAmount,DealDate,"
					+ "ChargesModeFeesDueDate,CostID,CorpCostID,StanID,StanAmount,CorpStanID,RoomPropertyUses,RoomPropertyRights,ParkPropertyRights,"
					+ "RoomActualSubDate,ParkActualSubDate,CustLiveType,BuildType,BuildArea,RoomParkCount,IsHelpPay,RoomUseState,ParkUseState,LateAmount,"
					+ "CustTypeName,TaxRate,TaxAmount,Tax,LateStartDate,LatePrice,LateRate,ParkArea,StanFormula,RoomState,GardenArea,YardArea,ChargeMode,CommisionAmount) values ";
            int count = 0;
            StringBuffer values = new StringBuffer();
            String insert_sql = ""; //最后拼接出来的insert语句
			
            for (FeesSerial fs : stringList) {
            	values.append("(");
				if( fs.getId() != null ){
					values.append("'"+fs.getId()+"' ,");
				}else{
					values.append(null+",");
				}
				if( fs.getCommID() != null){
					values.append(fs.getCommID()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getFeesID() != null ){
					values.append(fs.getFeesID()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getCustID() != null ){
					values.append(fs.getCustID()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getRoomID() != null ){
					values.append(fs.getRoomID()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getParkID() != null ){
					values.append(fs.getParkID()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getFeesDueDate() != null){
					values.append("'"+fs.getFeesDueDate()+"' ,");
				}else{
					values.append(null+",");
				}
				if( fs.getBussType() != null ){
					values.append("'"+fs.getBussType()+"' ,");
				}else{
					values.append(null+",");
				}
				if( fs.getDealAmount() != null ){
					values.append(fs.getDealAmount()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getDealDate() != null ){
					values.append("'"+fs.getDealDate()+"' ,");
				}else{
					values.append(null+",");
				}
				if( fs.getChargesModeFeesDueDate() != null){
					values.append("'"+fs.getChargesModeFeesDueDate()+"' ,");
				}else{
					values.append(null+",");
				} 
				if( fs.getCostID() != null ){
					values.append(fs.getCostID()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getCorpCostID() != null){
					values.append(fs.getCorpCostID()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getStanID() != null){
					values.append(fs.getStanID()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getStanAmount() != null){
					values.append(fs.getStanAmount()+",");	
				}else{
					values.append(null+",");
				}
				if( fs.getCorpStanID() != null){
					values.append(fs.getCorpStanID()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getRoomPropertyUses() != null){
					values.append("'"+fs.getRoomPropertyUses()+"' ,");
				}else{
					values.append(null+",");
				}
				if( fs.getRoomPropertyRights() != null){
					values.append("'"+fs.getRoomPropertyRights()+"' ,");
				}else{
					values.append(null+",");
				}
				if( fs.getParkPropertyRights() != null){
					values.append("'"+fs.getParkPropertyRights()+"' ,");
				}else{
					values.append(null+",");
				} 
				if( fs.getRoomActualSubDate() != null){
					values.append("'"+fs.getRoomActualSubDate()+"' ,");
				}else{
					values.append(null+",");
				} 
				if( fs.getParkActualSubDate() != null){
					values.append("'"+fs.getParkActualSubDate()+"' ,");
				}else{
					values.append(null+",");
				} 
				if( fs.getCustLiveType() != null ){
					values.append(fs.getCustLiveType()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getBuildType() != null){
					values.append("'"+fs.getBuildType()+"' ,");
				}else{
					values.append(null+",");
				}
				if( fs.getBuildArea() != null ){
					values.append(fs.getBuildArea()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getRoomParkCount() != null ){
					values.append(fs.getRoomParkCount()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getIsHelpPay() != null){
					values.append("'"+fs.getIsHelpPay()+"' ,");
				}else{
					values.append(null+",");
				} 
				if( fs.getRoomUseState() != null){
					values.append("'"+fs.getRoomUseState()+"' ,");
				}else{
					values.append(null+",");
				}
				if( fs.getParkUseState() != null){
					values.append("'"+fs.getParkUseState()+"' ,");
				}else{
					values.append(null+",");
				}
				if( fs.getLateAmount() != null){
					values.append(fs.getLateAmount()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getCustTypeName() != null){
					values.append("'"+fs.getCustTypeName()+"' ,");
				}else{
					values.append(null+",");
				} 
				if( fs.getTaxRate() != null ){
					values.append(fs.getTaxRate()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getTaxAmount() != null ){
					values.append(fs.getTaxAmount()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getTax() != null){
					values.append(fs.getTax()+",");
				}else{
					values.append(null+",");
				} 
				if( fs.getLateStartDate() != null ){
					values.append("'"+fs.getLateStartDate()+"' ,");
				}else{
					values.append(null+",");
				}
				if( fs.getLatePrice() != null){
					values.append(fs.getLatePrice()+",");
				}else{
					values.append(null+",");
				} 
				if( fs.getLateRate() != null){
					values.append(fs.getLateRate()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getParkArea() != null){
					values.append(fs.getParkArea()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getStanFormula() != null){
					values.append(fs.getStanFormula()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getRoomState() != null){
					values.append(fs.getRoomState()+",");
				}else{
					values.append(null+",");
				} 
				if( fs.getGardenArea() != null){
					values.append(fs.getGardenArea()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getYardArea() != null ){
					values.append(fs.getYardArea()+",");
				}else{
					values.append(null+",");
				}
				if( fs.getChargeMode() != null){
					values.append("'"+fs.getChargeMode()+"' ,");
				}else{
					values.append(null+",");
				}
				if( fs.getCommisionAmount() != null ){
					values.append(fs.getCommisionAmount()+"),");
				}else{
					values.append(null+"),");
				}
				count ++; //循环一次,计数器加一
				if(count % 1000 == 0){
					insert_sql = insert + values.substring(0,values.lastIndexOf(",")).toString();//去掉末尾的逗号,拼接成完整的insert语句
	                stmt.addBatch(insert_sql);//添加到批处理
	                count = 0 ; //还原计数器
					values = new StringBuffer(); //还原StringBuffer
	                logger.info("待插入数据" + insert_sql);
				}
				commidSet.add(fs.getCommID());
			}
            //将1000取余剩下的部分拼接成insert,添加到批处理
            if(count > 0){
				insert_sql = insert + values.substring(0,values.lastIndexOf(",")).toString();//去掉末尾的逗号,拼接成完整的insert语句
                stmt.addBatch(insert_sql);//添加到批处理
                count = 0 ; //还原计数器
				values = new StringBuffer(); //还原StringBuffer
            }
            stmt.executeBatch();
            System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+" :临时表插入结束.......");
			
			/*
			 * 遍历commidSet
			 * 执行存储过程Proc_INSERT_REAL_ACCOUNT（或者先创建 后执行）
			 */
			for(int commid : commidSet){
				String checkProcedure3 = "select * from dbo.sysobjects where id = object_id(N'[dbo].[PROC_CCC_INSERT_REAL_ACCOUNT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1 \n";
				rs = stmt.executeQuery(checkProcedure3);
				// 不存在就创建
				if (!rs.next()) {
					// 从实体表在临时表里抽取数据
					String PROC_CCC_INSERT_REAL_ACCOUNT = "create procedure PROC_CCC_INSERT_REAL_ACCOUNT(@ID bigint,@tableName varchar(255)) \n"
							+ "as \n" 
							+ "declare @templeTable varchar(255)"
							+ "set @templeTable = @tableName"
							+" insert into Tb_HSPR_FeesSerial(Id,CommID,FeesID,CustID,RoomID,ParkID,FeesDueDate,BussType,DealAmount,DealDate \n"
							+" ,ChargesModeFeesDueDate,CostID,CorpCostID,StanID,StanAmount,CorpStanID,RoomPropertyUses,RoomPropertyRights,ParkPropertyRights \n"
							+" ,RoomActualSubDate,ParkActualSubDate,CustLiveType,BuildType,BuildArea,RoomParkCount \n"
							+" ,IsHelpPay,RoomUseState,ParkUseState,LateAmount,CustTypeName \n"
							+" ,TaxRate,TaxAmount,Tax,LateStartDate,LatePrice,LateRate \n"
							+" ,ParkArea,StanFormula,RoomState,GardenArea,YardArea,ChargeMode,CommisionAmount) \n"
							+" exec('select Id,CommID,FeesID,CustID,RoomID,ParkID,FeesDueDate,BussType,DealAmount,DealDate"
							+" ,ChargesModeFeesDueDate,CostID,CorpCostID,StanID,StanAmount,CorpStanID,RoomPropertyUses,RoomPropertyRights,ParkPropertyRights"
							+" ,RoomActualSubDate,ParkActualSubDate,CustLiveType,BuildType,BuildArea,RoomParkCount"
							+" ,IsHelpPay,RoomUseState,ParkUseState,LateAmount,CustTypeName"
							+" ,TaxRate,TaxAmount,Tax,LateStartDate,LatePrice,LateRate"
							+" ,ParkArea,StanFormula,RoomState,GardenArea,YardArea,ChargeMode,CommisionAmount "
							+" from '+@templeTable+'')";

					logger.info("回填数据存储过程" + PROC_CCC_INSERT_REAL_ACCOUNT);
					// /创建存储过程
					stmt.executeUpdate(PROC_CCC_INSERT_REAL_ACCOUNT);
				}
				// 调用存储过程
				cs = conn.prepareCall("{call PROC_CCC_INSERT_REAL_ACCOUNT("+commid+", "+templeTable+")}");
				// 调用的结果集
				cs.execute();
				System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"#########################调用存储过程："+"{call PROC_CCC_INSERT_REAL_ACCOUNT("+commid+", "+templeTable+")}"+"......");
			}
			conn.commit(); // 提交事务
			
			
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new ServiceException("数据库异常：" + e.getMessage());
		} catch (SQLException e) {
			e.printStackTrace();
			throw new ServiceException("数据库异常：" + e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}
	
}