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


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

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

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 JdbcParkingRepository {
	private final static Logger logger = LoggerFactory.getLogger(JdbcParkingRepository.class);
	/**
	 * @param jdbcConstants 前台传输的参数，包含数据库连接的参数
	 * @param stringList 读取excel解析出来的list
	 */
	public static void jdbcConnect(JdbcConstants jdbcConstants,List<ParkingMsg> 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_parking_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" +
						"  roomid bigint DEFAULT 0 ,\n"+
						"  stanid bigint DEFAULT 0 ,\n"+
						"  custid bigint DEFAULT 0 ,\n"+
						"  costid bigint DEFAULT 0,\n" +
						"  commid bigint DEFAULT 0,\n" +
						"  cphm varchar(255) DEFAULT NULL,\n" +
						"  cqxz varchar(255) DEFAULT NULL,\n" +
						"  cwbh varchar(255) DEFAULT NULL,\n" +
						"  cwlx varchar(255) DEFAULT NULL,\n" +
						"  cwqy varchar(255) DEFAULT NULL,\n" +
						"  yzfwbh varchar(255) DEFAULT NULL,\n" +
						"  khfwbh varchar(255) DEFAULT NULL,\n" +
						"  jssj datetime DEFAULT NULL,\n" +
						"  jzmj decimal(18,2) DEFAULT NULL,\n" +
						"  khmc varchar(255) DEFAULT NULL,\n" +
						"  kssj datetime DEFAULT NULL,\n" +
						
						"  htjfsj datetime DEFAULT NULL,\n" +
						"  sjjfsj datetime DEFAULT NULL,\n" +
						"  jgsj datetime DEFAULT NULL,\n" +
						
						"  sfbz varchar(255) DEFAULT NULL,\n" +
						"  syzt varchar(255) DEFAULT NULL,\n" +
						"  yzmc varchar(255) DEFAULT NULL\n" +
						");\n";
				// /创建临时表
				stmt.addBatch(createTempTable);
				logger.info("临时表" + createTempTable);
			}
			
			String insert = "insert into "+templeTable+" (commid,cwqy,cwlx,cwbh,cqxz,yzmc,yzfwbh,syzt,khmc,khfwbh,kssj,jssj,htjfsj,sjjfsj,jgsj,cphm,jzmj,sfbz) values ";
            int count = 0;
            StringBuffer values = new StringBuffer();
            String insert_sql = ""; //最后拼接出来的insert语句
			
			for (ParkingMsg pm : stringList) {
				values.append("(");
				if (pm.getCommid() != null) {
					values.append(pm.getCommid()+",");
				}else{
					values.append(null+",");
				}
				if (pm.getCwqy() != null) {
					values.append("'"+pm.getCwqy()+"' ,");
				}else{
					values.append(null+",");
				}
				if (pm.getCwlx() != null) {
					values.append("'"+pm.getCwlx()+"' ,");
				}else{
					values.append(null+",");
				}
				if (pm.getCwbh() != null) {
					values.append("'"+pm.getCwbh()+"' ,");
				}else{
					values.append(null+",");
				}
				if (pm.getCqxz() != null) {
					values.append("'"+pm.getCqxz()+"' ,");
				}else{
					values.append(null+",");
				}
				if (pm.getYzmc() != null) {
					values.append("'"+pm.getYzmc()+"' ,");
				}else{
					values.append(null+",");
				}
				if (pm.getYzFwbh() != null) {
					values.append("'"+pm.getYzFwbh()+"' ,");
				}else{
					values.append(null+",");
				}
				if (pm.getSyzt() != null) {
					values.append("'"+pm.getSyzt()+"' ,");
				}else{
					values.append(null+",");
				}
				if (pm.getKhmc() != null) {
					values.append("'"+pm.getKhmc()+"' ,");
				}else{
					values.append(null+",");
				}
				if (pm.getKhFwbh() != null) {
					values.append("'"+pm.getKhFwbh()+"' ,");
				}else{
					values.append(null+",");
				}
				if (pm.getKssj() != null) {
					values.append("'"+new SimpleDateFormat("yyyy/MM/dd").format(pm.getKssj())+"' ,");
				}else{
					values.append(null+",");
				}
				if (pm.getJssj() != null) {
					values.append("'"+new SimpleDateFormat("yyyy/MM/dd").format(pm.getJssj())+"' ,");
				}else{
					values.append(null+",");
				}

				
				if (pm.getHtjfsj() != null) {
					values.append("'"+new SimpleDateFormat("yyyy/MM/dd").format(pm.getHtjfsj())+"' ,");
				}else{
					values.append(null+",");
				}
				if (pm.getSjjfsj() != null) {
					values.append("'"+new SimpleDateFormat("yyyy/MM/dd").format(pm.getSjjfsj())+"' ,");
				}else{
					values.append(null+",");
				}
				if (pm.getJgsj() != null) {
					values.append("'"+new SimpleDateFormat("yyyy/MM/dd").format(pm.getJgsj())+"' ,");
				}else{
					values.append(null+",");
				}
				
				
				if (pm.getCphm() != null) {
					values.append("'"+pm.getCphm()+"' ,");
				}else{
					values.append(null+",");
				}
				if (pm.getJzmj() != null) {
					values.append("'"+pm.getJzmj()+"' ,");
				}else{
					values.append(null+",");
				}
				if (pm.getSfbz() != null) {
					values.append("'"+pm.getSfbz()+"' ),");
				}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(pm.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) {
				//检查是否已经创建了UPDATE_PARKING_PROCE
				String checkProcedure = "select * from dbo.sysobjects where id = object_id(N'[dbo].[UPDATE_PARKING_PROCE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1;\n";
				rs = stmt.executeQuery(checkProcedure);
				//不存在就创建
				if (!rs.next()) {
					//创建存储过程SQL语句  ： 目的是为了取别的表上的数据补充完整临时表的数据
					String UPDATE_PARKING_PROCE = "create procedure UPDATE_PARKING_PROCE(@ID bigint ,@tableName varchar(255)) \n" +
							"AS \n" +
							"declare  @commid bigint \n" +
							"declare @templeTable varchar(255) \n"+
                    		"set @templeTable = @tableName \n"+
							"set @commid= @ID \n" +
							"exec('update a set a.stanid=b.stanid,a.costid=b.costid from '+@templeTable+' a,Tb_HSPR_CostStandard b where b.commid='+@commid+' and a.sfbz=b.stanname and b.IsDelete=0;')\n";
					logger.info("更新存储过程" + UPDATE_PARKING_PROCE);
					// /创建存储过程
					stmt.executeUpdate(UPDATE_PARKING_PROCE);
				}
				//调用存储过程
				cs = conn.prepareCall("{call UPDATE_PARKING_PROCE(" + commid + ","+templeTable+")}");//创建一个 CallableStatement 对象来调用数据库存储过程
				//调用的结果集
				cs.executeUpdate();
				System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"#########################调用存储过程："+"UPDATE_PARKING_PROCE(" + commid + ")"+"......");

				//检查是否已经创建了INSERT_PARKING_PROCE
				String checkProcedure2 = "select * from dbo.sysobjects where id = object_id(N'[dbo].[INSERT_PARKING_PROCE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1\n";
				rs = stmt.executeQuery(checkProcedure2);
				//不存在就创建
				if (!rs.next()) {
					//各实体表在临时表里抽取数据
					String INSERT_PARKING_PROCE = 
							"create procedure INSERT_PARKING_PROCE(@ID int, @tableName varchar(255)) AS \n" +
							"declare @ChargeCycle int \n" +
							"declare @CommID int \n" +
							"declare @CustName nvarchar(30) \n" +
							"declare @CustName2 nvarchar(30) \n" +
							"declare @RoomSign2 nvarchar(30) \n" +
							"declare @RoomSign nvarchar(30) \n" +
							"declare @ParkType nvarchar(20) \n" +
							"declare @ParkArea numeric(18,2) \n" +
							"declare @CarparkName nvarchar(20) \n" +
							"declare @CarparkSign nvarchar(20) \n" +
							"declare @ParkName nvarchar(30) \n" +
							"declare @ParkingNum int \n" +
							"declare @PropertyRight nvarchar(30) \n" +
							"declare @PropertyUses nvarchar(20) \n" +
							"declare @carsign nvarchar(50) \n" +
							"declare @cartype nvarchar(50) \n" +	
							"declare @HandID bigint \n" +
							"declare @CarparkID bigint \n" +
							"declare @ParkID bigint \n" +
							"declare @CustID bigint \n" +
							"declare @RoomID bigint \n" +
							"declare @CustID2 bigint \n" +
							"declare @RoomID2 bigint \n" +
							"declare @CostID bigint \n" +
							"declare @StanID bigint \n" +
							"declare @CarEmission nvarchar(20) \n" +
							"declare @sfdj nvarchar(50) \n" +
							"declare @dqrq datetime \n" +
							"declare @kssj datetime \n" +
							"declare @NewCustID bigint \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 @PaperCode nvarchar(20) \n" +
							"declare @FixedTel nvarchar(50) \n" +
							"declare @MobilePhone nvarchar(50) \n" +		
							"declare @CustTypeID bigint \n" +
							"declare @ContractSign nvarchar(20) \n" +
							"declare @Surname nvarchar(20) \n" +
							"declare @Name nvarchar(20) \n" +
							"declare @Email nvarchar(100) \n" +
							"declare @sex nvarchar(100) \n" +
							"declare @csny nvarchar(100) \n" +
							"declare @LiveID bigint \n" +
							"declare @date datetime \n" +
							"declare @i int \n" +
							"declare @templeTable varchar(255) \n"+
							"declare @htjfsj datetime\n"+
							"declare @sjjfsj datetime\n"+
							"declare @jgsj datetime\n"+
							"set @templeTable = @tableName \n"+
							"set @i=0 \n" +
							"set @CommID = @ID \n" +
							//"--车位区域，车位编号，建筑面积，车位类型，排量，租户名称，房间编号，标准，开始时间， \n" +
							//"--结束时间，停车卡号，车牌号码，产权性质，使用状态，业主名称，房屋名称 \n" +
							"exec('DECLARE t_BuildName scroll CURSOR FOR " +
							"select cwqy,cwbh,jzmj,cwlx,null,khmc,khfwbh,sfbz,kssj,jssj,null,cphm" +
							",cqxz,syzt,yzmc,yzfwbh,stanid,costid,htjfsj,sjjfsj,jgsj " +
							"from '+@templeTable+' " +
							"order by cwqy ,cwbh') " +
							
							"OPEN t_BuildName \n" +
							"FETCH NEXT FROM t_BuildName INTO @CarparkName,@ParkName \n" +
							",@ParkArea,@CarType,@CarEmission,@CustName,@RoomSign,@sfdj,@kssj,@dqrq,@CarparkSign, \n" +
							"@CarSign,@PropertyRight,@PropertyUses,@CustName2,@roomsign2,@StanID,@CostID,@htjfsj,@sjjfsj,@jgsj  \n" +  
						            
							"WHILE @@FETCH_STATUS = 0 \n" +
							"BEGIN \n" +
								"set @i=@i+1 \n" +
								"if not exists(select CarparkID from  Tb_HSPR_Carpark \n" +
								"where CommID = @CommID and CarparkName = @CarparkName) \n" +
								"begin  \n" +
									"SELECT @CarparkID=isnull(max(CarparkID)+1,(@CommID* CAST((case when @CommID>=214748 then 1000 else 10000 end) AS bigint) +1))"+
									"FROM Tb_HSPR_Carpark \n" +
									"where CommID = @CommID \n" +

									"Insert Into Tb_HSPR_Carpark(CarparkID,CommID,CarparkName) \n" +
									"Values (@CarparkID,@CommID,@CarparkName) \n" +
								"end \n" +
							
							
							
								"select @CarparkID =CarparkID from Tb_HSPR_Carpark \n" +
								"where CommID = @CommID and CarparkName = @CarparkName \n" +
								

								"SELECT @ParkID=isnull(max(ParkID)+1,(@CommID* CAST(100000000 AS bigint) +1) ) \n" +
								"FROM Tb_HSPR_Parking \n" +
								"where CommID = @CommID \n" +
								"set @RoomID2=0 \n" +
								"set @CustID2=0 \n" +
								"if(isnull(@RoomSign2,'')<>'') --如果房间编号不为空值 \n" +
								"begin \n" +
									"select @RoomID2=RoomID from Tb_HSPR_Room where CommID=@CommID and RoomSign=@RoomSign2 and isdelete=0 \n" +
									"if(@RoomID2!=0) \n" +
									"begin \n" +
										"select @CustID2=CustID from Tb_HSPR_CustomerLive where RoomID=@RoomID2 and isactive=1 \n" +
									"end \n" +
								"end \n" +
								"if(isnull(@RoomSign2,'')='' and isnull(@CustName2,'') <> '')--房间编号为空，业主名称不为空 \n" +
								"begin \n" +
									"if not exists(select * from Tb_HSPR_Customer \n" +
									"where CommID = @CommID  \n" +
									"and CustName = @CustName2 and isdelete=0 \n" +
									")   --同时在系统里面没有同名业主存在，插入业主信息为临时业主 \n" +
										"begin \n" +
										"select @NewCustID = MAX(custid)+1 from tb_hspr_customer where CommID=@CommID \n" +
										"--新的客户 \n" +
										"INSERT INTO Tb_HSPR_Customer \n" +
											"(CustID,CommID,CustName,IsDelete \n" +
										",PaperName,PaperCode,FixedTel,MobilePhone,Address,email,sex,Birthday,LinkmanTel, \n" +
										"PostCode,Recipient,Memo,BankName,BankIDs,BankAccount,CustTypeID,Surname,[Name],LiveType3) \n" +
										"VALUES \n" +
											"(@NewCustID,@CommID,@CustName2,0 \n" +
										",'0001',@PaperCode,@FixedTel,@MobilePhone,@Address,@Email,@sex,@csny, \n" +
										"@LinkmanTel,@PostCode,@Recipient,@Memo,@BankName,@BankIDs,@BankAccount, \n" +
										"@CustTypeID,@Surname,@Name,3) \n" +

										"set @CustID2 = @NewCustID \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,0,@CustID2,3,1,0,0,0,GETDATE(),null,null,null,@ContractSign) \n" +
										"end \n" +
									"else --查找业主ID \n" +
										"begin \n" +
											"select @CustID2 = CustID  \n" +
											"from Tb_HSPR_Customer \n" +
											"where CommID = @CommID and CustName = @CustName2 and isdelete=0 \n" +
										
										"end \n" +
							
									"--select @CustName,@RoomSign,@RoomID,@CustID \n" +
									
									"set @RoomID2=0 \n" +
								"end \n" +
								
								"set @RoomID=0 \n" +
								"set @CustID=0 \n" +
								
								"if(isnull(@RoomSign,'') <>'') \n" +
								"begin \n" +
									"select @RoomID=RoomID from Tb_HSPR_Room where CommID=@CommID and RoomSign=@RoomSign and isdelete=0 \n" +
									"if(@RoomID!=0) \n" +
									"begin \n" +
										"select @CustID=CustID from Tb_HSPR_CustomerLive where RoomID=@RoomID and isactive=1 \n" +
									"end	 \n" +
								"end \n" +
								"if(isnull(@RoomSign,'')='' and isnull(@CustName,'')<>'') \n" +
								"begin \n" +
									"if not exists(select * from Tb_HSPR_Customer \n" +
									"where CommID = @CommID  \n" +
									"and CustName = @CustName \n" +
									") \n" +
									"begin \n" +
										"select @NewCustID = MAX(custid)+1 from tb_hspr_customer where CommID=@CommID \n" +
										"--新的客户 \n" +
										"INSERT INTO Tb_HSPR_Customer \n" +
											"(CustID,CommID,CustName,IsDelete \n" +
										",PaperName,PaperCode,FixedTel,MobilePhone,Address,email,sex,Birthday,LinkmanTel, \n" +
										"PostCode,Recipient,Memo,BankName,BankIDs,BankAccount,CustTypeID,Surname,[Name],LiveType3) \n" +
										"VALUES \n" +
											"(@NewCustID,@CommID,@CustName,0 \n" +
										",'0001',@PaperCode,@FixedTel,@MobilePhone,@Address,@Email,@sex,@csny, \n" +
										"@LinkmanTel,@PostCode,@Recipient,@Memo,@BankName,@BankIDs,@BankAccount, \n" +
										"@CustTypeID,@Surname,@Name,3) \n" +

										"set @CustID = @NewCustID \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"+
							
										"INSERT INTO Tb_HSPR_CustomerLive \n" +
										"(LiveID,RoomID,CustID,LiveType,IsActive,IsDelLive,IsDebts,IsSale,ChargeTime \n" +
										",SubmitTime,FittingTime,StayTime,ContractSign) \n" +
										"VALUES \n" +
										"(@LiveID,0,@CustID,3,1,0,0,0,GETDATE(),null,null,null,@ContractSign) \n" +
									"end \n" +
									"else \n" +
									"begin \n" +
										"select @CustID = CustID  \n" +
										"from Tb_HSPR_Customer \n" +
										"where CommID = @CommID  \n" +
										"and CustName = @CustName and isdelete=0 \n" +
									"end \n" +
							
									"--select @CustName,@RoomSign,@RoomID,@CustID \n" +
									
									"set @RoomID=0 \n" +
								"end \n" +
								"INSERT INTO Tb_HSPR_Parking \n" +
										"(ParkID,CommID,CustID,RoomID,StanID,ParkType,ParkArea,CarparkID,ParkName,ParkingNum, \n" +
										"PropertyRight,PropertyUses,UseState,IsDelete,ParkCategory,contsubdate,actualsubdate,takeoverdate) \n" +
								"VALUES \n" +
										"(@ParkID,@CommID,@CustID2,@RoomID2,@StanID,@CarType,@ParkArea,@CarparkID, \n" +
										"@parkname,@ParkingNum,@PropertyRight,@PropertyUses, \n" +
										"case when @roomID=0 and @CustID=0 then '闲置' else '使用' end,0,0,@htjfsj,@sjjfsj,@jgsj) \n" +

								"if(@RoomID<>0 or @CustID<>0 ) --房号或者业主名字其中任意不为空 \n" +
								"begin \n" +
									"SELECT @HandID=isnull(max(HandID)+1,(@CommID* CAST(100000000 AS bigint) +1) ) \n" +
									"FROM Tb_HSPR_ParkingHand \n" +
									"where CommID = @CommID \n" +

									"INSERT INTO Tb_HSPR_ParkingHand \n" +
										"(HandID,CommID,ParkID,CustID,RoomID,ParkStartDate,ParkEndDate,handling,HandDate, \n" +
										"ParkingCarSign,CarSign,CarType,FacBrands,CarColor,CarEmission,Phone,IsDelete,isuse) \n" +
									"VALUES \n" +
									"(@HandID,@CommID,@ParkID,@CustID,@RoomID,@kssj,@dqrq,'批量导入',getdate(),@CarparkSign, \n" +
									"@CarSign,@CarType,'','',@CarEmission,'',0,1) \n" +

									"if(@StanID<>0 and @kssj is not null and @StanID is not null ) --如果找到相应标准，且结束日期不为空 \n" +
									"begin \n" +
									"INSERT INTO Tb_HSPR_CostStanSetting(CommID,CustID,RoomID,StanID,IsInherit \n" +
									",CostID,HandID,CalcBeginDate,IsDelete,ChargeCycle,ChangeDate,PayType) \n" +
									"Values (@CommID,@CustID,@RoomID,@StanID,1,@CostID,@HandID,@kssj,0,case when @sfdj like '%物业服务费%' then 1 else 0 end,getdate(),1) \n" +
									"end \n" +
								"end \n" +
								
								"FETCH NEXT FROM t_BuildName INTO @CarparkName,@ParkName \n" +
							",@ParkArea,@CarType,@CarEmission,@CustName,@RoomSign,@sfdj,@kssj,@dqrq, \n" +
							"@CarparkSign,@CarSign,@PropertyRight,@PropertyUses,@CustName2,@roomsign2,@StanID,@CostID,@htjfsj,@sjjfsj,@jgsj \n" +
							"END \n" +
							
							"CLOSE t_BuildName \n" +
							"DEALLOCATE t_BuildName";

					logger.info("回填车位数据存储过程" + INSERT_PARKING_PROCE);
					// /创建存储过程
					stmt.executeUpdate(INSERT_PARKING_PROCE);
				}
				//调用存储过程
				cs = conn.prepareCall("{call INSERT_PARKING_PROCE(" + commid + ","+templeTable+")}");//创建一个 CallableStatement 对象来调用数据库存储过程
				//调用的结果集
				cs.execute();
				System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"#########################调用存储过程："+"INSERT_PARKING_PROCE(" + commid + ")"+"......");
			}
			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();
			}
		}

	}
}
