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



import cn.com.countrygarden.bi.main.exception.ServiceException;
import cn.com.countrygarden.bi.main.model.erp.FamilyMemberMsg;
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 家庭资料
 */
public class JdbcMemberRepository {

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

    /**
     * @param jdbcConstants 前台传输的参数，包含数据库连接的参数
     * @param stringList    读取excel解析出来的list
     */
    public static void jdbcConnect(JdbcConstants jdbcConstants, List<FamilyMemberMsg> 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_family_member_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 IDENTITY(1,1) PRIMARY KEY,\n" +
                      "  commid bigint DEFAULT 0,\n" +
                      "  csrq varchar(255) DEFAULT NULL,\n" +
                      "  cymc varchar(255) DEFAULT NULL,\n" +
                      "  fwbh varchar(255) DEFAULT NULL,\n" +
                      "  gddh varchar(255) DEFAULT NULL,\n" +
                      "  gj varchar(255) DEFAULT NULL,\n" +
                      "  gzdw varchar(255) DEFAULT NULL,\n" +
                      "  lymc varchar(255) DEFAULT NULL,\n" +
                      "  xb varchar(255) DEFAULT NULL,\n" +
                      "  yddh varchar(255) DEFAULT NULL,\n" +
                      "  yyzgx varchar(255) DEFAULT NULL,\n" +
                      "  yzmc varchar(255) DEFAULT NULL,\n" +
                      "  zjhm varchar(255) DEFAULT NULL,\n" +
                      "  zjmc varchar(255) DEFAULT NULL,\n" +
                      "  zy varchar(255) DEFAULT NULL,\n" +
                      " roomid bigint DEFAULT 0,\n" +
                      " custid bigint DEFAULT 0\n" +
                      ");";
                // 创建临时表
                stmt.addBatch(createTempTable);
                logger.info("临时表" + createTempTable);
            }
            String insert = "insert into "+templeTable+" (commid,csrq,cymc,fwbh,gddh,gj,gzdw,lymc,xb,yddh,yyzgx,yzmc,zjmc,zjhm,zy,roomid,custid) values ";
            int count = 0;
            StringBuffer values = new StringBuffer();
            String insert_sql = ""; //最后拼接出来的insert语句
            
            //循环拼接insert语句,插入临时表
            for(FamilyMemberMsg fm : stringList){
            	values.append("(");
            	if(fm.getCommid() != null){
            		values.append(fm.getCommid()+",");
				}else{
					values.append(null+",");
				}
            	if(fm.getCsrq() != null){
            		values.append("'"+fm.getCsrq()+"' ,");
				}else{
					values.append(null+",");
				}
            	if(fm.getCymc() != null){
            		values.append("'"+fm.getCymc()+"' ,");
				}else{
					values.append(null+",");
				}
            	if(fm.getFwbh() != null){
            		values.append("'"+fm.getFwbh()+"' ,");
				}else{
					values.append(null+",");
				}
            	if(fm.getGddh() != null){
            		values.append("'"+fm.getGddh()+"' ,");
				}else{
					values.append(null+",");
				}
            	if(fm.getGj() != null){
            		values.append("'"+fm.getGj()+"' ,");
				}else{
					values.append(null+",");
				}
            	if(fm.getGzdw() != null){
            		values.append("'"+fm.getGzdw()+"' ,");
				}else{
					values.append(null+",");
				}
            	if(fm.getLymc() != null){
            		values.append("'"+fm.getLymc()+"' ,");
				}else{
					values.append(null+",");
				}
            	if(fm.getXb() != null){
            		values.append("'"+fm.getXb()+"' ,");
				}else{
					values.append(null+",");
				}
            	if(fm.getYddh() != null){
            		values.append("'"+fm.getYddh()+"' ,");
				}else{
					values.append(null+",");
				}
            	if(fm.getYyzgx() != null){
            		values.append("'"+fm.getYyzgx()+"' ,");
				}else{
					values.append(null+",");
				}
            	if(fm.getYzmc() != null){
            		values.append("'"+fm.getYzmc()+"' ,");
				}else{
					values.append(null+",");
				}
            	if(fm.getZjmc() != null){
            		values.append("'"+fm.getZjmc()+"' ,");
				}else{
					values.append(null+",");
				}
            	if(fm.getZjhm() != null){
            		values.append("'"+fm.getZjhm()+"' ,");
				}else{
					values.append(null+",");
				}
            	if(fm.getZy() != null){
            		values.append("'"+fm.getZy()+"' ,");
				}else{
					values.append(null+",");
				}
            	if(fm.getRoomid() != null){
            		values.append(fm.getRoomid()+",");
				}else{
					values.append(null+",");
				}
            	if(fm.getCustid() != null){
            		values.append(fm.getCustid()+"),");
				}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(fm.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].[PROC_UPDATE_MEMBER]') and OBJECTPROPERTY(id, N'IsProcedure') = 1\n";
                rs = stmt.executeQuery(checkProcedure);
                //不存在就创建
                if (!rs.next()) {
                    //创建存储过程SQL语句  ： 目的是为了取别的表上的数据补充完整临时表的数据
                    String PROC_UPDATE_MEMBER = "create procedure PROC_UPDATE_MEMBER(@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 b.IsActive=1;')\n";
                    logger.info("补充完整临时表" + PROC_UPDATE_MEMBER);
                    // /创建存储过程
                    stmt.executeUpdate(PROC_UPDATE_MEMBER);
                }
                //调用存储过程
                cs = conn.prepareCall("{call PROC_UPDATE_MEMBER(" + commid + ","+templeTable+")}");//创建一个 CallableStatement 对象来调用数据库存储过程

                //调用的结果集
                cs.executeUpdate();

                //查询存储过程是否存在
                String checkProcedure2 = "select * from dbo.sysobjects where id = object_id(N'[dbo].[PROC_INSERT_MEMBER]') and OBJECTPROPERTY(id, N'IsProcedure') = 1\n";
                rs = stmt.executeQuery(checkProcedure2);
                //不存在就创建
                if (!rs.next()) {
                    /*
                     * 从临时表  ##t_family_member_msg 中抽取数据，插入  tb_hspr_household
                     */
                	String PROC_INSERT_MEMBER = "create procedure PROC_INSERT_MEMBER(@ID int,@tableName varchar(255)) AS\n"
							+ "declare @commid int\n" 
                			+ "set @commid = @ID \n"
                			+ "declare @templeTable varchar(255) \n"
                    		+  "set @templeTable = @tableName \n"
							+ "exec('insert into tb_hspr_household(HoldID,CommID,CustID,RoomID,isdelete,membername,paperName,papercode,MobilePhone,Birthday,Surname,Name,Relationship) "
							+ "select"
							+ "(select isnull(max(holdid),('+@commid+'* CAST(100000000 AS bigint))) from tb_hspr_household where commid='+@commid+')+row_number() OVER (ORDER BY commid DESC) as holdid,"
							+ "CommID,custid,roomid,0,cymc,zjmc,zjhm,yddh,csrq,null,null,isnull((select dictionarycode from Tb_Dictionary_Relation where dictionaryname=yyzgx),''0014'') from '+@templeTable+' \n"
							+ "where cymc is not null  and custid is not null ')\n";
                    logger.info("回填数据存储过程" + PROC_INSERT_MEMBER);
                    // /创建存储过程
                    stmt.executeUpdate(PROC_INSERT_MEMBER);
                }

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

        }

    }
}

