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


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

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 JdbcArrearageRepository {

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

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

        Connection conn = null;

        Statement stmt = null;

        ResultSet rs = null;

        CallableStatement cs = null;//调用存储过程使用的接口
       
        Set<Integer> commidSet = new HashSet<Integer>();

        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_arrearage_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 IDENTITY(1,1) PRIMARY KEY,\n" +
                        "  cwbh varchar(255) DEFAULT NULL,\n" +
                        "  fwbh varchar(255) DEFAULT NULL,\n" +
                        "  fyrq varchar(255) DEFAULT NULL,\n" +
                        "  fyxm varchar(255) DEFAULT NULL,\n" +
                        "  je numeric(18,2) DEFAULT NULL,\n" +
                        "  jssj varchar(255) DEFAULT NULL,\n" +
                        "  khmc varchar(255) DEFAULT NULL,\n" +
                        "  kssj varchar(255) DEFAULT NULL,\n" +
                        "  ysrq varchar(255) DEFAULT NULL,\n" +
                        "  bz varchar(255) DEFAULT NULL,\n" +
                        "  khlb varchar(255) DEFAULT NULL,\n" +
                        "  costid bigint DEFAULT 0,\n" +
                        "  custid bigint DEFAULT 0,\n" +
                        "  handid bigint DEFAULT 0,\n" +
                        "  parkid bigint DEFAULT 0,\n" +
                        "  roomid bigint DEFAULT 0,\n" +
                        "  commid bigint DEFAULT 0);\n";
                stmt.addBatch(createTempTable);
                logger.info("临时表" + createTempTable);
            }
            
            String insert = "insert into "+templeTable+" (commid,cwbh,fwbh,fyrq,fyxm,je,jssj,khmc,kssj,ysrq,bz,khlb) values ";
            int count = 0;
            StringBuffer values = new StringBuffer();
            String insert_sql = ""; //最后拼接出来的insert语句
            //解析ecxl数据，循环插入到临时表
            for (ArrearageMsg am : stringList) {
            	values.append("(");
				if (am.getCommid() != null) {
					values.append(am.getCommid()+",");
				}else{
					values.append(null+",");
				}
				if(am.getCwbh() != null){
					values.append("'"+am.getCwbh()+"' ,");
				}else{
					values.append(null+",");
				}
				if(am.getFwbh() != null){
					values.append("'"+am.getFwbh()+"' ,");
				}else{
					values.append(null+",");
				}
				if(am.getFyrq() != null){
					values.append("'"+am.getFyrq()+"' ,");
				}else{
					values.append(null+",");
				}
				if(am.getFyxm() != null){
					values.append("'"+am.getFyxm()+"' ,");
				}else{
					values.append(null+",");
				}
				if(am.getJe() != null){
					values.append("'"+am.getJe()+"' ,");
				}else{
					values.append(null+",");
				}
				if(am.getJssj() != null){
					values.append("'"+am.getJssj()+"' ,");
				}else{
					values.append(null+",");
				}
				if(am.getKhmc() != null){
					values.append("'"+am.getKhmc()+"' ,");
				}else{
					values.append(null+",");
				}
				if(am.getKssj() != null){
					values.append("'"+am.getKssj()+"' ,");
				}else{
					values.append(null+",");
				}
				if(am.getYsrq() != null){
					values.append("'"+am.getYsrq()+"' ,");
				}else{
					values.append(null+",");
				}
				if(am.getBz() != null){
					values.append("'"+am.getBz()+"' ,");
				}else{
					values.append(null+",");
				}
				if(am.getKhlb() != null){
					values.append("'"+am.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(am.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_ARREARAGE_PROCE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1;\n";
                //执行存储过程
                rs = stmt.executeQuery(checkProcedure);
                //不存在就创建
                if (!rs.next()) {
                    //创建存储过程SQL语句  ： 目的是为了取别的表上的数据补充完整临时表的数据
                    String UPDATE_ARRE_PROCE = "create procedure UPDATE_ARREARAGE_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.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.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" +
                            "exec('update a set a.costid=b.costid from '+@templeTable+' a,Tb_HSPR_CostItem b where b.commid='+@commid+'  and a.fyxm=b.costname and IsDelete=0') \n";
                            logger.info("更新存储过程" + UPDATE_ARRE_PROCE);
                    // /创建存储过程
                    stmt.executeUpdate(UPDATE_ARRE_PROCE);
                }
                //调用存储过程
                cs = conn.prepareCall("{call UPDATE_ARREARAGE_PROCE(" + commid + ","+templeTable+")}");//创建一个 CallableStatement 对象来调用数据库存储过程
                //调用的结果集
                cs.executeUpdate();
                System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"#########################调用存储过程："+"UPDATE_ARREARAGE_PROCE(" + commid + ")"+"......");
                //查询存储过程是否存在
                String checkProcedure2 = "select * from dbo.sysobjects where id = object_id(N'[dbo].[PROCE_INSERT_ARRE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1\n";
                rs = stmt.executeQuery(checkProcedure2);
                //不存在就创建
                if (!rs.next()) {
                    //各实体表在临时表里抽取数据
                    String PROCE_INSERT_ARRE = "create procedure PROCE_INSERT_ARRE(@ID bigint,@tableName varchar(255)) \n" +
							"as \n" 
                    		+"declare @FeesID bigint \n"
                    		+ "declare @RoomID bigint \n"
							+ "declare @CostID bigint \n" 
							+ "declare @CustID bigint \n" 
							+ "declare @CommID	BIGINT \n"
							+ "declare @handid	BIGINT \n" 
							+ "declare @parkid	BIGINT \n" 
							+ "declare	@IsAudit smallint \n" 
							+ "declare	@IsBank smallint \n"
							+ "declare	@IsCharge smallint \n" 
							+ "declare	@AccountFlag int \n"
							+ "declare @BuildSNum int \n" 
							+ "declare @stanmemo nvarchar(500) \n" 
							+" declare @FeesDueDate datetime \n" 
							+ "declare @FeesStateDate datetime \n"
							+ "declare @FeesEndDate datetime \n" 
							+ "declare @AccountsDueDate datetime \n" 
							+" declare @DueAmount numeric(18,2) \n" 
							+ "declare @DebtsAmount numeric(18,2) \n" 
							+ "declare @yqr nvarchar(30) \n"
							+ "declare @khid int \n" 
							+ "declare @dybh nvarchar(50) \n" 
							+ "declare @mc nvarchar(100) \n"
							+ "declare @ysrq datetime \n" 
							+ "declare @qsje numeric(18,2) \n"
							+ "declare @FeesMemo nvarchar(50) \n" 
							+ "declare @ksrq datetime \n" 
							+ "declare @jsrq datetime \n"
							+ "declare @metersign nvarchar(10) \n"
							+ "declare @templeTable varchar(255) \n"
                    		+ "set @templeTable = @tableName \n"
							+ "set @CommID = @ID \n"
							
							+ "exec('DECLARE t_BuildName scroll CURSOR FOR "
							+ "SELECT fyxm,fyrq,ysrq,kssj,jssj,je,custid,isnull(roomid,0),costid,''批量导入'',NULL,isnull(parkid,0),isnull(handid,0) from '+@templeTable+' "
							+ "WHERE custid IS NOT null ')"
							
							+ "OPEN t_BuildName \n"
							+ "FETCH NEXT FROM t_BuildName INTO @mc,@FeesDueDate,@AccountsDueDate,@FeesStateDate,@FeesEndDate \n"
							+ ",@qsje,@CustID,@RoomID,@CostID,@FeesMemo,@metersign,@parkid,@handid \n"
							+ "WHILE @@FETCH_STATUS = 0 \n" +

							"BEGIN \n" 
							+ "set @IsCharge = 0 \n" 
							+ "set @IsBank = 0 \n" 
							+ "set @IsAudit = 0 \n"
							+ "set @AccountFlag = 99 \n" +

							"set @DueAmount = @qsje \n" + "set @DebtsAmount = @qsje \n"
							+ "if isnull(@custid,0)=0  or isnull(@costid,0)=0 or @FeesDueDate is null \n" 
							+ "begin \n" +
							// 设值
							"select @custid,@costid,@FeesDueDate,'CUSTID或者costid等于0 或@FeesDueDate 为空，退出导入' \n"
							+ "CLOSE t_BuildName \n" + "DEALLOCATE t_BuildName \n" + "return \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" +
							// @FeesID设值
							"SELECT @FeesID = isnull(max(FeesID)+1,(@CommID* CAST(1000000000000 AS bigint) +1) ) \n"
							+ "FROM Tb_HSPR_Fees \n" + "where CommID = @CommID \n" +

							"if(@FeesID<	(SELECT isnull(max(FeesID)+1,(@CommID* CAST(1000000000000 AS bigint) +1) ) \n"
							+ "FROM Tb_HSPR_Feeshis \n" + "where CommID = @CommID)) \n" 
							+ "begin \n"
							+ "SELECT @FeesID=isnull(max(FeesID)+1,(@CommID* CAST(1000000000000 AS bigint) +1) ) \n"
							+ "FROM Tb_HSPR_Feeshis \n" + "where CommID = @CommID \n" 
							+ "end \n" +
							// @feesid或者@custid或者@RoomID或者@costid前6位不等于commid，退出导入
							"if(left(@feesid,6)!=@commid or left(@custid,6)!=@commid or (left(@RoomID,6)!=@commid and isnull(@Roomid,0)!=0)or left(@costid,6)!=@commid  ) \n"
							+ "begin \n" + "select '@feesid或者@custid或者@RoomID或者@costid前6位不等于commid，退出导入' \n"
							+ "CLOSE t_BuildName \n" + "DEALLOCATE t_BuildName \n" + "return \n" + "end \n" +

							// 入库
							"INSERT INTO Tb_HSPR_Fees \n"
							+ "(FeesID,CommID,CostID,CustID,RoomID,FeesDueDate,FeesStateDate,FeesEndDate,DueAmount,DebtsAmount,IsAudit \n"
							+ ",AccountFlag,IsBank,IsCharge,IsFreeze,accountsduedate,IsProperty,handid \n"
							+ ",IsPrec,metersign,FeesMemo,parkid) \n" 
							+ "VALUES \n"
							+ "(@FeesID,@CommID,@CostID,@CustID,@RoomID,@FeesDueDate,@FeesStateDate,@FeesEndDate,@DueAmount, \n"
							+ "@DebtsAmount,@IsAudit,@AccountFlag,@IsBank,@IsCharge,0,@AccountsDueDate,0,@handid \n"
							+ ",0,@metersign,@FeesMemo,@parkid) \n" +

							"FETCH NEXT FROM t_BuildName INTO  @mc,@FeesDueDate,@AccountsDueDate,@FeesStateDate,@FeesEndDate \n"
							+ ",@qsje,@CustID,@RoomID,@CostID,@FeesMemo,@metersign,@parkid,@handid \n" 
							+ "END \n" +
							// 关闭游标
							"CLOSE t_BuildName \n" + "DEALLOCATE t_BuildName \n";
                    logger.info("回填数据存储过程" + PROCE_INSERT_ARRE);
					// /创建存储过程
					stmt.executeUpdate(PROCE_INSERT_ARRE);
				}
				// 执行存储过程
				cs = conn.prepareCall("{call PROCE_INSERT_ARRE(" + commid + ","+templeTable+")}");
				// 调用的结果集
				cs.execute();
				System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"#########################调用存储过程："+"PROCE_INSERT_ARRE(" + commid + ")"+"......");
			}
                // 提交事务
			    conn.commit(); 
			    
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new ServiceException("数据库异常：" + e.getMessage());
		} catch (SQLException 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();
			}

		}

	}
}
