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.PrepaidAmountMsg;

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 预收
 */
public class JdbcPrepaidRepository {

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

    /**
     * @param jdbcConstants 前台传输的参数，包含数据库连接的参数
     * @param stringList    读取excel解析出来的list
     */
    public static void jdbcConnect(JdbcConstants jdbcConstants, List<PrepaidAmountMsg> 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_prepaid_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" +
                        "  costid bigint DEFAULT 0,\n" +
                        "  custid bigint DEFAULT 0,\n" +
                        "  commid bigint DEFAULT 0,\n" +
                        "  cwbh varchar(255) DEFAULT NULL,\n" +
                        "  fwbh varchar(255) DEFAULT NULL,\n" +
                        "  fyxm varchar(255) DEFAULT NULL,\n" +
                        "  handid bigint DEFAULT 0,\n" +
                        "  handids bigint DEFAULT 0,\n" +
                        "  khlb varchar(255) DEFAULT NULL,\n" +
                        "  khmc varchar(255) DEFAULT NULL,\n" +
                        "  parkid bigint DEFAULT 0,\n" +
                        "  roomid bigint DEFAULT 0,\n" +
                        "  sfsj varchar(255) DEFAULT NULL,\n" +
                        "  yjye numeric(18,2) DEFAULT NULL); \n";
                // /创建临时表
                stmt.addBatch(createTempTable);
                logger.info("临时表" + createTempTable);
            }
            
            String insert = "insert into "+templeTable+" (commid,khlb,khmc,fwbh,cwbh,sfsj,fyxm,yjye) values ";
            int count = 0;
            StringBuffer values = new StringBuffer();
            String insert_sql = ""; //最后拼接出来的insert语句
            for (PrepaidAmountMsg pa : stringList) {
            	values.append("(");
				if (pa.getCommid() != null) {
					values.append(pa.getCommid()+",");
				}else{
					values.append(null+",");
				}
				if(pa.getKhlb() != null){
					values.append("'"+pa.getKhlb()+"' ,");
				}else{
					values.append(null+",");
				}
				if(pa.getKhmc() != null){
					values.append("'"+pa.getKhmc()+"' ,");
				}else{
					values.append(null+",");
				}
				if(pa.getFwbh() != null){
					values.append("'"+pa.getFwbh()+"' ,");
				}else{
					values.append(null+",");
				}
				if(pa.getCwbh() != null){
					values.append("'"+pa.getCwbh()+"' ,");
				}else{
					values.append(null+",");
				}
				if(pa.getSfsj() != null){
					values.append("'"+pa.getSfsj()+"' ,");
				}else{
					values.append(null+",");
				}
				if(pa.getFyxm() != null){
					values.append("'"+pa.getFyxm()+"' ,");
				}else{
					values.append(null+",");
				}
				if(pa.getYjye() != null){
					values.append("'"+pa.getYjye()+"'),");
				}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(pa.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) {

                String checkProcedure = "select * from dbo.sysobjects where id = object_id(N'[dbo].[UPDATE_PREPAID_PROCE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1\n";
                rs = stmt.executeQuery(checkProcedure);
                //不存在就创建
                if (!rs.next()) {
                    //创建存储过程SQL语句  ： 目的是为了取别的表上的数据补充完整临时表的数据
                    String UPDATE_PREPAID_PROCE = "create procedure UPDATE_PREPAID_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 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 '+@templeTable+'  set handids=handid where isnull(handid,'''')<>''''') ";
                    logger.info("补充完整临时表" + UPDATE_PREPAID_PROCE);
                    // /创建存储过程
                    stmt.executeUpdate(UPDATE_PREPAID_PROCE);
                }
                //调用存储过程
                cs = conn.prepareCall("{call UPDATE_PREPAID_PROCE("+commid+","+templeTable+")}");//创建一个 CallableStatement 对象来调用数据库存储过程
                //调用的结果集
                cs.executeUpdate();
                System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"#########################调用存储过程："+"UPDATE_PREPAID_PROCE(" + commid + ")"+"......");

                String checkProcedure2 = "select * from dbo.sysobjects where id = object_id(N'[dbo].[PROCE_INSERT_PREPAID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1\n";
                rs = stmt.executeQuery(checkProcedure2);
                //不存在就创建
                if (!rs.next()) {
                    //各实体表在临时表里抽取数据
                    String PROCE_INSERT_PREPAID = "create procedure PROCE_INSERT_PREPAID(@ID bigint,@tableName varchar(255)) \n"
							+ "as \n" 
							+"declare @CustID bigint \n"
							+"declare @RoomID bigint \n"
							+"declare @parkid bigint \n"
							+"declare @handid bigint \n"
							+"declare @CustName varchar(30) \n"
							+"declare @RoomSign varchar(30) \n"
							+"declare @PrecDate datetime \n"
							+"declare @PrecAmount numeric(18,2) \n"
							+"declare @CostID bigint \n"
							+"declare @i int \n"
							+"declare @FeesID bigint \n"
							+"declare @PrecID bigint \n"
							+"declare @RecdID bigint \n"
							+"declare @CostIDs nvarchar(100) \n"
							+"declare @CostNames nvarchar(100) \n"
							+"declare @handids nvarchar(100) \n"
							+"declare @parknames nvarchar(100) \n"
							+"declare @CommID int \n"
							+"set @CommID = @ID \n"
							+"declare @templeTable varchar(255) \n"
							+"set @templeTable = @tableName \n"
							+"declare @temp nvarchar(40) \n"
							+"set @temp=concat('##insert_prepaid_',convert(nvarchar,getdate(),112),replace(convert(nvarchar,getdate(),114),':','')) \n"
							+"exec('create table '+@temp+' (id int, costid bigint, costname nvarchar(20), costids nvarchar(80), costnames nvarchar(80) )') \n"
							 
							+"exec('declare cu_getDistinctCostnames scroll CURSOR FOR select distinct fyxm from '+@templeTable) \n"
							+"open cu_getDistinctCostnames fetch next from cu_getDistinctCostnames into @costnames \n"
							+"while @@fetch_status=0  \n"
							+"begin \n"
							+"exec('insert into '+@temp+' (id,costname,costnames) select t.id,t.items,t.original from (select ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as ID, value as Items,'''+@costnames+'''  as Original from funSplit( '''+@costnames+''' ,'','')) t ') \n"
							+"fetch next from cu_getDistinctCostnames into @costnames \n"
							+"end \n"
							+"close cu_getDistinctCostnames; \n"
							+"DEALLOCATE cu_getDistinctCostnames; \n"

							+"exec( 'with tt as ( select ci.costid,t.costname,t.costnames from '+@temp+'  t left join tb_hspr_costitem ci on t.costname=ci.costname and ci.CommID='+@CommID+' ) update a set a.costids=b.costids,a.costid=b.costid from '+@temp+'  a,( select tt.costid,tt.costname,tt.costnames,p3.costids from tt left join ( select p2.costnames,stuff((select '',''+convert(nvarchar,p1.costid)  from tt p1 where p1.costnames=p2.costnames for xml path('''')),1,1,'''') costids from tt p2 group by costnames ) p3 on tt.costnames=p3.costnames )b where a.costnames=b.costnames and a.costname=b.costname ') \n"

							+"exec('DECLARE cu_preCosts scroll CURSOR FOR select CustID,isnull(RoomID,0) as RoomID,isnull(yjye,0) PrecAmount,sfsj PrecDate,parkid parkid,handid handid,handids handids,cwbh parknames,t2.costid,t2.costids,t2.costnames from '+@templeTable+' t1,(select distinct costnames,costids,convert(bigint,left(costids,case (charindex('','',costids)) when 0 then len(costids) else charindex('','',costids)-1 end)) as costid from '+@temp+' ) t2 where isnull(yjye,0)<>0 and CustID is not null and t1.fyxm=t2.costnames ') \n"
							+"OPEN cu_preCosts \n"
							+"FETCH NEXT FROM cu_preCosts INTO @CustID,@RoomID,@PrecAmount,@PrecDate,@parkid,@handid,@handids,@parknames,@CostID,@CostIDs,@CostNames \n"
							+"WHILE @@FETCH_STATUS = 0 \n"
							+"BEGIN \n"
							+"declare @BillsSign nvarchar(20) \n"
							+"declare @ChargeMode nvarchar(20) \n"
							+"declare @AccountWay smallint \n"
							+"declare @IsAudit smallint \n"
							+"declare @IsDelete smallint \n"
							+"declare @BuildSNum int \n"
							+"declare @PrecMemo nvarchar(60) \n"
							+"declare @PerFeesamount numeric(18,2) \n"
							+"set @ChargeMode = '现金' \n"
							+"set @AccountWay = 3 \n"
							+"set @IsDelete = 0 \n"
							+"set @IsAudit = 0 \n"
							+"set @PrecMemo = convert(nvarchar,getdate(),120) \n"
							+"SELECT @PrecID = isnull(max(precid)+1,(@CommID* CAST(1000000000000 AS bigint)+1)) \n"
							+"FROM Tb_HSPR_PreCosts \n"
							+"where CommID = @CommID \n"
							+"SELECT @RecdID = isnull(max(recdid)+1,(@CommID* CAST(1000000000000 AS bigint)+1)) \n"
							+"FROM Tb_HSPR_PreCostsdetail \n"
							+"where CommID = @CommID \n"
							+"INSERT INTO Tb_HSPR_PreCosts \n"
							+"(PrecID,CommID,CustID,RoomID,CostID,PrecAmount,IsPrec,PrecMemo,BillsSign,CostIDs,CostNames,HandIDs,ParkNames) \n"
							+"VALUES \n"
							+"(@PrecID,@CommID,@CustID,@RoomID,@CostID,@PrecAmount,1,@PrecMemo,@BillsSign,@CostIDs,@CostNames,@handids,@parknames) \n"
							+"INSERT INTO Tb_HSPR_PreCostsDetail \n"
							+"(RecdID,PrecID,CommID,CustID,RoomID,CostID,PrecAmount,NewPrecAmount,PrecDate,PrecMemo,BillsSign,ChargeMode,AccountWay,IsAudit,IsDelete,FeesID,SourceType,CostIDs,CostNames,ParkID,ParkNames,HandID,HandIDs) \n"
							+"VALUES \n"
							+"(@RecdID,@PrecID,@CommID,@CustID,@RoomID,@CostID,@PrecAmount,@PrecAmount,@PrecDate,@PrecMemo,@BillsSign,@ChargeMode,@AccountWay,@IsAudit,@IsDelete,@FeesID,0,@CostIDs,@CostNames,@parkid,@parknames,@handid,@handids) \n"
							+"exec('insert into Tb_HSPR_PreCostsCan(PrecID,CanCostID) select '+@PrecID+',Costid from '+@temp+' where costnames='''+@costnames+'''') \n"
							+"FETCH NEXT FROM cu_preCosts INTO @CustID,@RoomID,@PrecAmount,@PrecDate,@parkid,@handid,@handids,@parknames,@CostID,@CostIDs,@CostNames \n"
							+"END \n"
							+"CLOSE cu_preCosts \n"
							+"DEALLOCATE cu_preCosts";

                    logger.info("回填数据存储过程" + PROCE_INSERT_PREPAID);

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

            }

        }

    }
}
