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


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

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

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * @Author christ.lai
 * @Date 2018-05-31
 * @Descripe 租户dao
 */
public class JdbcOwnerRepository {

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

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

        Connection conn = null;

        Statement stmt = null;

        ResultSet rs = null;

        Set<Integer> commidSet = new HashSet<Integer>(); // 项目编号

        CallableStatement cs = null;//调用存储过程使用的接口
        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_house_owner_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 PRIMARY KEY IDENTITY(1,1),\n" +
                        "  cqxz varchar(255) DEFAULT NULL,\n" +
                        "  csrq datetime DEFAULT NULL,\n" +
                        "  czdh varchar(255) DEFAULT NULL,\n" +
                        "  dy int DEFAULT NULL,\n" +
                        "  dzyj varchar(255) DEFAULT NULL,\n" +
                        "  fwbh varchar(255) DEFAULT NULL,\n" +
                        "  fwlx varchar(255) DEFAULT NULL,\n" +
                        "  fwmc varchar(255) DEFAULT NULL,\n" +
                        "  fwzt varchar(255) DEFAULT NULL,\n" +
                        "  gddh varchar(255) DEFAULT NULL,\n" +
                        "  gj varchar(255) DEFAULT NULL,\n" +
                        "  gtmj decimal(18,2) DEFAULT NULL,\n" +
                        "  htjfsj datetime DEFAULT NULL,\n" +
                        "  hymj decimal(18,2) DEFAULT NULL,\n" +
                        "  jzmj decimal(18,2) DEFAULT NULL,\n" +
                        "  ksjfsj datetime DEFAULT NULL,\n" +
                        "  lc int DEFAULT NULL,\n" +
                        "  lxdz varchar(255) DEFAULT NULL,\n" +
                        "  lymc varchar(255) DEFAULT NULL,\n" +
                        "  mjlx varchar(255) DEFAULT NULL,\n" +
                        "  sjjfsj datetime DEFAULT NULL,\n" +
                        "  sjr varchar(255) DEFAULT NULL,\n" +
                        "  syxz varchar(255) DEFAULT NULL,\n" +
                        "  syzt varchar(255) DEFAULT NULL,\n" +
                        "  tnmj decimal(18,2) DEFAULT NULL,\n" +
                        "  wyjgsj datetime DEFAULT NULL,\n" +
                        "  xb varchar(255) DEFAULT NULL,\n" +
                        "  yddh varchar(255) DEFAULT NULL,\n" +
                        "  yzbm varchar(255) DEFAULT NULL,\n" +
                        "  yzmc varchar(255) DEFAULT NULL,\n" +
                        "  zjhm varchar(255) DEFAULT NULL,\n" +
                        "  zjmc varchar(255) DEFAULT NULL,\n" +
                        "  ztqy varchar(255) DEFAULT NULL,\n" +
                        "  commid bigint DEFAULT 0,\n" +
                        "  xh int DEFAULT 0,\n" +
                        "  lb varchar(255) DEFAULT NULL,\n" +
                        ");\n";
                //创建临时表
                stmt.addBatch(createTempTable);
                logger.info("临时表" + createTempTable);
            }
            
            //计数器
            int count = 0;
            
            String insert = "insert into "+templeTable+" (commid,ztqy,lymc,fwbh,fwmc,dy,lc,xh,"
            +"mjlx,jzmj,tnmj,gtmj,hymj,cqxz,syxz,syzt,fwzt,fwlx,htjfsj,sjjfsj,wyjgsj,ksjfsj,yzmc,"
            + "xb,gj,csrq,zjmc,zjhm,gddh,yddh,czdh,lxdz,yzbm,sjr,dzyj,lb)  values ";
            
            StringBuffer values = new StringBuffer();
            
            String insert_sql = ""; //最后拼接出来的insert语句
            
            for (HouseOwnerMsg ho : stringList) {
            	values.append("("); 
            	if (ho.getCommid() != null) {
					values.append(ho.getCommid()+",");
				}else{
					values.append(null+",");
				}
				if (ho.getZtqy() != null) {
					values.append("'"+ho.getZtqy()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getLymc() != null) {
					values.append("'"+ho.getLymc()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getFwbh() != null) {
					values.append("'"+ho.getFwbh()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getFwmc() != null) {
					values.append("'"+ho.getFwmc()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getDy() != null) {
					values.append(ho.getDy()+",");
				}else{
					values.append(null+",");
				}
				if (ho.getLc() != null) {
					values.append(ho.getLc()+",");
				}else{
					values.append(null+",");
				}
				if (ho.getXh() != null) {
					values.append(ho.getXh()+",");
				}else{
					values.append(null+",");
				}
				if (ho.getMjlx() != null) {
					values.append("'"+ho.getMjlx()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getJzmj() != null) {
					values.append(ho.getJzmj()+",");
				}else{
					values.append(null+",");
				}
				if (ho.getTnmj() != null) {
					values.append(ho.getTnmj()+",");
				}else{
					values.append(null+",");
				}
				if (ho.getGtmj() != null) {
					values.append(ho.getGtmj()+",");
				}else{
					values.append(null+",");
				}
				if (ho.getHymj() != null) {
					values.append(ho.getHymj()+",");
				}else{
					values.append(null+",");
				}
				if (ho.getCqxz() != null) {
					values.append("'"+ho.getCqxz()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getSyxz() != null) {
					values.append("'"+ho.getSyxz()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getSyzt() != null) {
					values.append("'"+ho.getSyzt()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getFwzt() != null) {
					values.append("'"+ho.getFwzt()+"' ,");
				}else{
					values.append(null+",");
				}
            	if(ho.getFwlx() != null){
            		values.append("'"+ho.getFwlx()+"' ,");
            	}else{
					values.append(null+",");
				}
				if (ho.getHtjfsj() != null) {
					values.append("'"+new SimpleDateFormat("yyyy/MM/dd").format(ho.getHtjfsj())+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getSjjfsj() != null) {
					values.append("'"+new SimpleDateFormat("yyyy/MM/dd").format(ho.getSjjfsj())+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getWyjgsj() != null) {
					values.append("'"+new SimpleDateFormat("yyyy/MM/dd").format(ho.getWyjgsj())+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getKsjfsj() != null) {
					values.append("'"+new SimpleDateFormat("yyyy/MM/dd").format(ho.getKsjfsj())+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getYzmc() != null) {
					values.append("'"+ho.getYzmc()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getXb() != null) {
					values.append("'"+ho.getXb()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getGj() != null) {
					values.append("'"+ho.getGj()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getCsrq() != null) {
					values.append("'"+new SimpleDateFormat("yyyy/MM/dd").format(ho.getCsrq())+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getZjmc() != null) {
					values.append("'"+ho.getZjmc()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getZjhm() != null) {
					values.append("'"+ho.getZjhm()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getGddh() != null) {
					values.append("'"+ho.getGddh()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getYddh() != null) {
					values.append("'"+ho.getYddh()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getCzdh() != null) {
					values.append("'"+ho.getCzdh()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getLxdz() != null) {
					values.append("'"+ho.getLxdz()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getYzbm() != null) {
					values.append("'"+ho.getYzbm()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getSjr() != null) {
					values.append("'"+ho.getSjr()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getDzyj() != null) {
					values.append("'"+ho.getDzyj()+"' ,");
				}else{
					values.append(null+",");
				}
				if (ho.getLymc() != null) {
					values.append("'"+ho.getLymc()+"' ),");
				}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(ho.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 (int commid : commidSet) {
				/*
				 *   1.导入组团存储过程
				 */
                String checkProcedure = "select * from dbo.sysobjects where id = object_id(N'[dbo].[PROCE_INSERT_REGION]') and OBJECTPROPERTY(id, N'IsProcedure') = 1\n";
                rs = stmt.executeQuery(checkProcedure);
                if (!rs.next()) {
                    /*
                     *1. 导入组团临时表里抽取数据
                     */
                    String PROCE_INSERT_REGION = 
                    		"create procedure PROCE_INSERT_REGION(@ID int, @tableName varchar(255)) AS \n"+
                    		"declare @CommID int \n"+
                    		"declare @RegionID bigint \n"+
                    		"declare @i int \n"+
                    		"declare @RegionName nvarchar(20) \n"+
                    		"declare @RegionSNum int \n"+
                    		"declare @templeTable varchar(255) \n"+
                    		"set @templeTable = @tableName \n"+
                    		"set @CommID = @ID \n"+
                    		"select @i= isnull(max(RegionSNum),0) from Tb_HSPR_Region where commid=@CommID \n"+
                    		//动态sql
                    		"exec('DECLARE t_BuildName CURSOR FOR select ztqy from '+@templeTable+' where ztqy not in (select RegionName from Tb_HSPR_Region where commid='+@CommID+' and IsDelete=0) group by ztqy order by ztqy ')"+
                    		"\n"+
                    		
                    		"OPEN t_BuildName \n"+
                    		"FETCH NEXT FROM t_BuildName INTO @RegionName \n"+
                    		
                    		"WHILE @@FETCH_STATUS = 0 \n"+
                    		"BEGIN \n"+
                    			"set @RegionName = RTRIM(LTRIM(@RegionName)) \n"+
                    			"IF(@RegionName<>N'''') \n"+
                    			"begin \n"+
                    				"SELECT @RegionID = isnull(max(RegionID)+1,(@CommID* CAST(1000000 AS bigint) +1) ) \n"+
                    				"FROM Tb_HSPR_Region \n"+
                    				"where CommID = @CommID \n"+

                    				//select @BuildingID \n"+
                    		
                    				"set @i = @i + 1 \n"+
                    				"set @RegionSNum = @i \n"+
                    		
                    				"INSERT INTO Tb_HSPR_Region \n"+
                    					"(RegionID,CommID,RegionName,RegionSNum,IsDelete) \n"+
                    				"VALUES \n"+
                    					"(@RegionID,@CommID,@RegionName,@RegionSNum,0) \n"+
                    			"end \n"+
                    			
                    			"FETCH NEXT FROM t_BuildName INTO @RegionName \n"+
                    		"END \n"+
                    		
                    		"CLOSE t_BuildName \n"+
                    		"DEALLOCATE t_BuildName ";

                    logger.info("回填数据存储过程" + PROCE_INSERT_REGION);
                    // /创建存储过程
                    stmt.executeUpdate(PROCE_INSERT_REGION);
                }
                System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"#########################调用存储过程："+"PROCE_INSERT_REGION(" + commid + ","+templeTable+")"+"......");
                //调用存储过程
                cs = conn.prepareCall("{call PROCE_INSERT_REGION(" + commid + ","+templeTable+")}");
                //调用的结果集
                cs.execute();
                /*
                 * 2.导入楼宇存储过程
                 */
                String checkProcedure2 = "select * from dbo.sysobjects where id = object_id(N'[dbo].[INSERT_BUILDING_PROCE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1\n";
                rs = stmt.executeQuery(checkProcedure2);
                if (!rs.next()) {
                    //导入楼宇临时表里抽取数据
                    String INSERT_BUILDING_PROCE = 
                    		"create procedure INSERT_BUILDING_PROCE(@ID int,@tableName varchar(255)) AS \n"+
                    		"declare @CommID int \n"+
                    		"declare @BuildID bigint \n"+
                    		"declare @i int \n"+
                    		"declare @lb nvarchar(20) \n"+
                    		"declare @BuildSign nvarchar(10) \n"+
                    		"declare @BuildName nvarchar(20) \n"+
                    		"declare @BuildSNum int \n"+
                    		"declare @RegionSNum int \n"+
                    		"declare @RegionName nvarchar(20) \n"+
                    		"declare @templeTable varchar(255) \n"+
                    		"set @templeTable = @tableName \n"+
                    		"set @CommID = @ID \n"+
                    		"select @i= isnull(max(buildSNum),0) from Tb_HSPR_Building where commid=@CommID \n"+

                    		"exec('DECLARE t_BuildName CURSOR FOR select ztqy,lb,lymc from '+ @templeTable +' where lymc not in (select buildname from Tb_HSPR_Building where commid='+@CommID+' and IsDelete=0) group by ztqy,lb,lymc order by ztqy,lb')\n"+
                    		
                    		"OPEN t_BuildName \n"+
                    		"FETCH NEXT FROM t_BuildName INTO @RegionName,@lb,@BuildName \n"+
                    		
                    		"WHILE @@FETCH_STATUS = 0 \n"+
                    		"BEGIN \n"+
                    			"set @BuildName = RTRIM(LTRIM(@BuildName)) \n"+
                    			"IF(@BuildName<>N'''') \n"+
                    			"begin \n"+
                    				"SELECT @BuildID = isnull(max(BuildID)+1,(@CommID* CAST(1000000 AS bigint) +1) ) \n"+
                    				"FROM Tb_HSPR_Building \n"+
                    				"where CommID = @CommID \n"+

                    				//select @BuildingID \n"+
                    		
                    				"set @i = @i + 1 \n"+
                    				"set @BuildSNum = @i \n"+
                    		
                    				"set @BuildSign = substring('0000',1,4-len(@i))+Convert(nvarchar(10),@i) \n"+
                    				//select @BuildSign \n"+

                    				"select @RegionSNum = RegionSNum from Tb_HSPR_Region \n"+
                    				"where RegionName = @RegionName \n"+
                    				"and CommID = @CommID \n"+

                    				"INSERT INTO Tb_HSPR_Building \n"+
                    					"(BuildID,CommID,BuildSign,BuildName,BuildSNum,RegionSNum,IsDelete) \n"+
                    				"VALUES \n"+
                    					"(@BuildID,@CommID,@BuildSign,@BuildName,@BuildSNum,@RegionSNum,0) \n"+
                    			"end \n"+
                    			
                    			
                    			"FETCH NEXT FROM t_BuildName INTO @RegionName,@lb,@BuildName \n"+
                    		"END \n"+
                    		
                    		"CLOSE t_BuildName \n"+
                    		"DEALLOCATE t_BuildName";

                    logger.info("回填楼宇数据存储过程" + INSERT_BUILDING_PROCE);
                    // /创建存储过程
                    stmt.executeUpdate(INSERT_BUILDING_PROCE);
                }
                System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"#########################调用存储过程："+"INSERT_BUILDING_PROCE(" + commid + ","+templeTable+")"+"......");
                //调用存储过程
                cs = conn.prepareCall("{call INSERT_BUILDING_PROCE(" + commid + ","+templeTable+")}");
                //调用的结果集
                cs.execute();
               
                /*
                 * 3.导入房屋存储过程
                 */
                String checkProcedure3 = "select * from dbo.sysobjects where id = object_id(N'[dbo].[PROCE_INSERT_HOUSE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1\n";
                rs = stmt.executeQuery(checkProcedure3);
                if (!rs.next()) {
                    //导入房屋临时表里抽取数据
                    String PROCE_INSERT_HOUSE = 
                    		"create procedure PROCE_INSERT_HOUSE(@ID int, @tableName varchar(255)) AS \n"+
                    		"declare @CommID int \n"+
                    		"declare @RoomSign nvarchar(30) \n"+
                    		"declare @RoomName nvarchar(50) \n"+
                    		"declare @RoomID bigint \n"+
                    		"declare @MaxRoomID bigint \n"+
                    		"declare @AreaChangeID bigint \n"+
                    		"declare @BuildSNum int \n"+
                    		"declare @RegionSNum int \n"+
                    		"declare @UnitSNum int \n"+
                    		"declare @FloorSNum int \n"+
                    		"declare @RoomSNum int \n"+
                    		"declare @BuildArea numeric(18,2) \n"+

                    		"declare @InteriorArea numeric(18,2) \n"+
                    		"declare @ContSubDate date \n"+
                    		"declare @takeoverdate date \n"+
                    		"declare @actualsubdate date \n"+
                    		"declare @paybegindate date \n"+

                    		"declare @CommonArea numeric(18,2) \n"+
                    		"declare @GardenArea  numeric(18,2) \n"+

                    		"declare @oldBuildArea numeric(18,2) \n"+
                    		"declare @oldInteriorArea numeric(18,2) \n"+
                    		"declare @oldCommonArea numeric(18,2) \n"+
                    		"declare @oldGardenArea numeric(18,2) \n"+

                    		"declare @RoomModel nvarchar(20) \n"+
                    		"declare @PropertyRights nvarchar(20) \n"+
                    		"declare @PropertyUses nvarchar(20) \n"+
                    		"declare @UsesState smallint \n"+
                    		"declare @Advertising nvarchar(30) \n"+
                    		"declare @lymc nvarchar(20) \n"+
                    		"declare @RegionName nvarchar(20) \n"+
                    		"declare @RoomStateName nvarchar(20)\n"+
                    		"declare @RoomState int \n"+
                    		"declare @RoomType int \n"+
                    		"declare @templeTable varchar(255) \n"+
                    		
                    		"set @templeTable = @tableName \n"+
                    		"set @RoomState=0 \n"+
                    		"set @CommID = @ID \n"+

                    		"SELECT @MaxRoomID = isnull(max(roomid),(@CommID* CAST(100000000 AS bigint))) \n"+
                    				"FROM Tb_HSPR_room \n"+
                    				"where CommID = @CommID \n"+

                    		"exec('DECLARE t_HSPR_Room CURSOR FOR select ztqy,lymc,fwbh,jzmj,dy,lc,xh,tnmj,gtmj,0,hymj,cqxz,syxz,1,0,0,'''',isnull(0,0),'''', "+
                    		"isnull((select roomstate from tb_hspr_roomstate where statename=fwzt and isdelete=0),null) roomstate,fwmc,htjfsj,wyjgsj,sjjfsj,ksjfsj, "+
                    		"case when fwlx=''销售房屋'' then 1 "+
                    		"when fwlx=''自持房屋'' then 2 "+
                    		"when fwlx=''物业用房'' then 3 "+
                    		"when fwlx=''公共区域'' then 4 "+
                    		"else null end RoomType from '+@templeTable+' "+
                    		"order by lb,dy,lc,xh ')\n"+
                    		
                    		"OPEN t_HSPR_Room \n"+
                    		"FETCH NEXT FROM t_HSPR_Room INTO @RegionName,@lymc,@RoomSign,@BuildArea,@UnitSNum,@FloorSNum,@RoomSNum,@InteriorArea,@CommonArea,@oldBuildArea,@GardenArea,@PropertyRights,@PropertyUses,@UsesState,@oldInteriorArea,@oldCommonArea,@RoomStateName \n"+
                    	",@oldGardenArea,@Advertising,@RoomState,@RoomName,@ContSubDate,@takeoverdate,@actualsubdate,@paybegindate,@RoomType \n"+
                    		
                    		"WHILE @@FETCH_STATUS = 0 \n"+
                    		"BEGIN \n"+
                    			"set @MaxRoomID = @MaxRoomID+1 \n"+
                    			"set @AreaChangeID = @AreaChangeID+1 \n"+
                    			
                    			
                    			"select @BuildSNum = BuildSNum from Tb_HSPR_Building \n"+
                    			"where BuildName = @lymc  \n"+
                    			"and CommID = @CommID \n"+

                    			"select @RegionSNum = RegionSNum from Tb_HSPR_Region \n"+
                    			"where RegionName = @RegionName \n"+
                    			"and CommID = @CommID \n"+

                    			"INSERT [Tb_HSPR_Room]  \n"+
                    			"( [RoomID],[CommID],[RoomSign],[RoomName],[BuildSNum],[UnitSNum],[FloorSNum],[RoomSNum],[BuildArea] \n"+
                    			",[RoomState] , [ChargeTypeID] , [IsDelete] \n"+ 
                    			",InteriorArea,CommonArea,GardenArea,RoomModel,PropertyRights,PropertyUses,UsesState,areatype,IsSplitUnite,Advertising,ContSubDate,takeoverdate,RegionSNum,actualsubdate,paybegindate,RoomType) \n"+ 
                    			"VALUES ( @MaxRoomID , @CommID , @RoomSign ,@RoomName , @BuildSNum , @UnitSNum, @FloorSNum , @RoomSNum , @BuildArea \n"+
                    			",@RoomState , 0 , 0,@InteriorArea,@CommonArea,@GardenArea,@RoomModel,@PropertyRights,@PropertyUses,@UsesState,0,0,@Advertising,@ContSubDate,@takeoverdate,@RegionSNum,@actualsubdate,@paybegindate,@RoomType) \n"+

                    			"insert into Tb_HSPR_RoomStateHis(CommID,CustID,RoomID,NewRoomState,RoomState,UserCode,ChangeDate) \n"+
                    			"values(@CommID,0,@MaxRoomID,@RoomState,0,'',getdate()) \n"+

                    			"FETCH NEXT FROM t_HSPR_Room INTO @RegionName,@lymc,@RoomSign,@BuildArea,@UnitSNum,@FloorSNum,@RoomSNum,@InteriorArea,@CommonArea,@oldBuildArea,@GardenArea,@PropertyRights,@PropertyUses,@UsesState,@oldInteriorArea,@oldCommonArea,@RoomStateName \n"+
                    							",@oldGardenArea,@Advertising,@RoomState,@RoomName,@ContSubDate,@takeoverdate,@actualsubdate,@paybegindate,@RoomType \n"+
                    		"END \n"+
                    		
                    		"CLOSE t_HSPR_Room \n"+
                    		"DEALLOCATE t_HSPR_Room";


                    logger.info("回填房屋数据存储过程" + PROCE_INSERT_HOUSE);
                    // /创建存储过程
                    stmt.executeUpdate(PROCE_INSERT_HOUSE);
                }
                System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"#########################调用存储过程："+"PROCE_INSERT_HOUSE(" + commid + ","+templeTable+")"+"......");
                //调用存储过程
                cs = conn.prepareCall("{call PROCE_INSERT_HOUSE(" + commid + ","+templeTable+")}");
                cs.execute();
                
                /*
                 * 4.导入 业主信息存储过程
                 */
                String checkProcedure4 = "select * from dbo.sysobjects where id = object_id(N'[dbo].[INSERT_OWNER_PROCE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1\n";
                rs = stmt.executeQuery(checkProcedure4);
                //不存在就创建
                if (!rs.next()) {
                    //导入业主临时表里抽取数据
                    String INSERT_OWNER_PROCE = 
                    		"create procedure INSERT_OWNER_PROCE(@ID int, @tableName varchar(255)) AS \n"+
                    		"declare @CommID int \n"+
                    		"declare @LiveID bigint \n"+
                    		"declare @NewCustID bigint \n"+
                    		"declare @CustName nvarchar(30) \n"+
                    		"declare @RoomID bigint \n"+
                    		"declare @RoomSign nvarchar(30) \n"+
                    		"declare @CustID bigint \n"+

                    		"declare @LiveType smallint \n"+
                    		"declare @ChargeTime datetime \n"+

                    		"declare @SubmitTime datetime \n"+
                    		"declare @FittingTime datetime \n"+
                    		"declare @StayTime datetime \n"+
                    	
                    		"declare @PaperName nvarchar(20) \n"+
                    		"declare @PaperCode nvarchar(20) \n"+
                    		"declare @FixedTel nvarchar(50) \n"+
                    		"declare @MobilePhone nvarchar(50) \n"+
                    		
                    		
                    		"declare @Email nvarchar(100) \n"+
                    		"declare @sex nvarchar(100) \n"+
                    		"declare @csny nvarchar(100) \n"+

                    		"declare @date datetime \n"+
                    		"declare @LinkmanTel nvarchar(20) \n"+
                    		"declare @Address nvarchar(1000) \n"+
                    		"declare @PostCode nvarchar(20) \n"+
                    		"declare @Recipient nvarchar(100) \n"+
                    		"declare @Memo nvarchar(1000) \n"+
                    		"declare @BankName nvarchar(20) \n"+
                    		"declare @BankIDs nvarchar(20) \n"+
                    		"declare @BankAccount nvarchar(20) \n"+
                    		"declare @WorkUnit nvarchar(200) \n"+
                    		"declare @Random int \n"+

                    		"declare @CustTypeID bigint \n"+
                    		"declare @ContractSign nvarchar(20) \n"+
                    		"declare @Surname nvarchar(20) \n"+
                    		"declare @Name nvarchar(20) \n"+
                    		
                    		"declare @i int \n"+
                    		"set @i = 1 \n"+
                    		
                    		"declare @templeTable varchar(255) \n"+
                    		"set @templeTable = @tableName \n"+
                    		
                    		"set @CommID = @ID \n"+
                    		//业主 \n"+
                    		"set @LiveType = 1 \n"+
                    		"set @ChargeTime = getdate() \n"+
                    		"set @date = getdate() \n"+
                    		"set @Random = Convert(int,DATEPART(millisecond,@date)) \n"+
                    		
                    		"SELECT @NewCustID = isnull(max(custid),(@CommID* CAST(100000000 AS bigint))) \n"+
                    		"FROM Tb_HSPR_Customer \n"+
                    		"where CommID = @CommID \n"+
                    		
                    		"exec('DECLARE t_HSPR_Customer CURSOR FOR "+ 		
                    		"select yzmc,fwbh,zjhm,gddh,yddh,'''','''','''',lxdz,'''',xb,csrq,'''',yzbm,'''','''','''','''','''','''','''','''','''','''', "+
                    		"isnull((select DictionaryCode from Tb_Dictionary_PaperName where DictionaryName=zjmc),''0001'') from '+@templeTable) \n"+
                    		
                    		"OPEN t_HSPR_Customer \n"+
                    		"FETCH NEXT FROM t_HSPR_Customer INTO @CustName,@RoomSign,@PaperCode,@FixedTel,@MobilePhone,@SubmitTime,@FittingTime,@StayTime,@Address \n"+
                    		",@Email,@sex,@csny,@LinkmanTel,@PostCode,@Recipient,@Memo,@BankName,@BankIDs,@BankAccount,@WorkUnit,@CustTypeID,@ContractSign,@Surname,@Name,@PaperName \n"+
                    		
                    		"WHILE @@FETCH_STATUS = 0 \n"+
                    		"BEGIN \n"+
                    			
                    			"set @i = @i + 1 \n"+
                    	
                    			"select @RoomID = RoomID \n"+
                    			"from Tb_HSPR_Room \n"+
                    			"where CommID = @CommID \n"+
                    			"and RoomSign = @RoomSign \n"+
                    	
                    			"if not exists(select * from Tb_HSPR_Customer \n"+
                    			"where CommID = @CommID \n"+ 
                    			"and CustName = @CustName \n"+
                    			"and PaperCode=@PaperCode) \n"+
                    			"begin \n"+
                    				"set @NewCustID = @NewCustID+1 \n"+
                    				//新的客户 \n"+
                    				"INSERT INTO Tb_HSPR_Customer \n"+
                    					"(CustID,CommID,CustName,IsDelete \n"+
                    				",PaperName,PaperCode,FixedTel,MobilePhone,Address,email,sex,Birthday,LinkmanTel,PostCode,Recipient,Memo,BankName,BankIDs,BankAccount,CustTypeID,Surname,[Name]) \n"+
                    				"VALUES \n"+
                    					"(@NewCustID,@CommID,@CustName,0 \n"+
                    				",@PaperName,@PaperCode,@FixedTel,@MobilePhone,@Address,@Email,@sex,@csny,@LinkmanTel,@PostCode,@Recipient,@Memo,@BankName,@BankIDs,@BankAccount,@CustTypeID,@Surname,@Name) \n"+

                    				"set @CustID = @NewCustID \n"+
                    			"end \n"+
                    			"else \n"+
                    			"begin \n"+
                    				"select @CustID = CustID  \n"+
                    				"from Tb_HSPR_Customer \n"+
                    				"where CommID = @CommID  \n"+
                    				"and CustName = @CustName \n"+
                    				"and PaperCode=@PaperCode \n"+
                    			"end \n"+
                    	
                    			"set @date = getdate() \n"+			
                    			"set @Random = @i + 1000 \n"+

                    			"set @LiveID = Convert(nvarchar(20),@date,112) \n"+
                    			"+Convert(nvarchar(10),@CommID)\n"+
                    			"+Convert(nvarchar(10),@Random) \n"+
                    	
                    			//新的使用信息 \n"+
                    			"INSERT INTO Tb_HSPR_CustomerLive \n"+
                    				"(LiveID,RoomID,CustID,LiveType,IsActive,IsDelLive,IsDebts,IsSale,ChargeTime \n"+
                    			",SubmitTime,FittingTime,StayTime,ContractSign) \n"+
                    			"VALUES \n"+
                    				"(@LiveID,@RoomID,@CustID,@LiveType,1,0,0,0,@ChargeTime \n"+
                    			",@SubmitTime,@FittingTime,@StayTime,@ContractSign) \n"+
                    				
                    			"FETCH NEXT FROM t_HSPR_Customer INTO @CustName,@RoomSign,@PaperCode,@FixedTel,@MobilePhone,@SubmitTime,@FittingTime,@StayTime,@Address,@Email,@sex,@csny \n"+
                    							",@LinkmanTel,@PostCode,@Recipient,@Memo,@BankName,@BankIDs,@BankAccount,@WorkUnit,@CustTypeID,@ContractSign,@Surname,@Name,@PaperName \n"+
                    		"END \n"+
                    		
                    		"CLOSE t_HSPR_Customer \n"+
                    		"DEALLOCATE t_HSPR_Customer; ";

                    logger.info("回填业主数据存储过程" + INSERT_OWNER_PROCE);
                    // /创建存储过程
                    stmt.executeUpdate(INSERT_OWNER_PROCE);
                }
                System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"#########################调用存储过程："+"INSERT_OWNER_PROCE(" + commid + ","+templeTable+")"+"......");
                //调用存储过程
                cs = conn.prepareCall("{call INSERT_OWNER_PROCE(" + commid + ","+templeTable+")}");
                cs.execute();
                conn.commit(); // 插入事务分开提交
                /*
                 * 5.更新楼宇信息存储过程
                 */
                String checkProcedure5 = "select * from dbo.sysobjects where id = object_id(N'[dbo].[UPDATE_BUILDING_PROC]') and OBJECTPROPERTY(id, N'IsProcedure') = 1\n";
                rs = stmt.executeQuery(checkProcedure5);
                //不存在就创建
                if (!rs.next()) {
                	//导入临时表里抽取数据
                    String UPDATE_BUILDING_PROC = 
                    		"create procedure UPDATE_BUILDING_PROC(@ID int) AS \n"+
                    		"declare @CommID int \n"+
                    		"declare @BuildID bigint \n"+
                    		"declare @BuildSNum int \n"+
                    		"declare @UnitNum int \n"+
                    		"declare @FloorsNum int \n"+
                    		"declare @PerFloorNum int \n"+
                    		"declare @HouseholdsNum int \n"+

                    		"set @CommID = @ID \n"+

                    		"DECLARE t_HSPR_Building CURSOR FOR \n"+
                    		"select BuildID,BuildSNum from Tb_HSPR_Building \n"+
                    		"where CommID = @CommID \n"+
                    		
                    		"OPEN t_HSPR_Building \n"+
                    		"FETCH NEXT FROM t_HSPR_Building INTO @BuildID,@BuildSNum \n"+
                    		
                    		
                    		"WHILE @@FETCH_STATUS = 0 \n"+
                    		"BEGIN \n"+
                    			"select @UnitNum = Count(*)  from ( \n"+
                    				"select UnitSNum from Tb_HSPR_Room \n"+
                    				"where CommID = @CommID \n"+
                    				"and BuildSNum = @BuildSNum \n"+
                    				"Group by UnitSNum) as tt \n"+

                    			"select @FloorsNum = Count(*)  from ( \n"+
                    				"select FloorSNum from Tb_HSPR_Room \n"+
                    				"where CommID = @CommID \n"+
                    				"and BuildSNum = @BuildSNum \n"+
                    				"Group by FloorSNum) as tt \n"+
                    			"select @PerFloorNum = max(tt.PerFloorNum) from ( \n"+
                    				"select UnitSNum,FloorSNum,max(RoomSNum) as PerFloorNum from Tb_HSPR_Room \n"+
                    				"where CommID = @CommID \n"+
                    				"and BuildSNum = @BuildSNum \n"+
                    				"Group by UnitSNum,FloorSNum) as tt \n"+

                    			"set @HouseholdsNum = @UnitNum * @FloorsNum * @PerFloorNum \n"+

                    			"update Tb_HSPR_Building \n"+
                    			"set UnitNum = @UnitNum \n"+
                    			",FloorsNum = @FloorsNum \n"+
                    			",PerFloorNum = @PerFloorNum \n"+
                    			",HouseholdsNum = @HouseholdsNum \n"+
                    			"where BuildID = @BuildID \n"+
                    			
                    			"FETCH NEXT FROM t_HSPR_Building INTO @BuildID,@BuildSNum \n"+
                    		"END \n"+
                    		
                    		"CLOSE t_HSPR_Building \n"+
                    		"DEALLOCATE t_HSPR_Building \n"+

                    	"exec Proc_HSPR_Customer_UpdateLive @CommID,0 \n"+

                    	"update Tb_HSPR_Customer \n"+
                    			"set RoomSigns = dbo.funGetRoomSigns(CustID,0), \n"+
                    			"RoomCount = dbo.funGetRoomSignsCount(CustID,0) where commid=@CommID \n"+
                    	"truncate table tb_hspr_roomtemptree ";


                    logger.info("回填数据存储过程" + UPDATE_BUILDING_PROC);
                    //创建存储过程
                    stmt.executeUpdate(UPDATE_BUILDING_PROC);
                }
                System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"#########################调用存储过程："+"UPDATE_BUILDING_PROC(" + commid + ")"+"......");
                //调用存储过程
				cs = conn.prepareCall("{call UPDATE_BUILDING_PROC(" + commid + ")}");
				cs.execute();
            }
            conn.commit(); // 提交事务
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new ServiceException("数据库异常："+e.getMessage());
        } catch (SQLException e) {
            e.printStackTrace();
            throw new ServiceException("数据库异常："+e.getMessage());
        } catch(Exception e){
        	e.printStackTrace();
            throw new ServiceException("数据库异常："+e.getMessage());
    	}finally {
            // 遵循：resultset-->statment-->connection这样的关闭顺序！一定要将三个trycatch块，分开写！
            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();
            }
        }
    }
}
