package flow;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;
import org.codehaus.jettison.json.JSONObject;

public class GetVND {
private static Logger log = Logger.getLogger(GetVND.class);
	
	/**
	 * 获取前三位手机号码将获取到的VDN返回
	 * @ClassName: GetVND 
	 * @Description: seven
	 * @author 获取前三位手机号码将获取到的VDN返回
	 * @date 2021年7月23日 下午2:23
	 *  
	 */
	//transDnis,UUI
	public void getVDN(com.avaya.sce.runtimecommon.SCESession mySession) {
		//进线获取进线电话前3位
		String ani = mySession.getVariableField(IProjectVariables.SESSION,IProjectVariables.SESSION_FIELD_ANI).getStringValue();
		String substring = ani.substring(0, 3);
		String transferNO = mySession.getVariableField(IProjectVariables.TRANS_DNIS).getStringValue();
		Pattern pattern = Pattern.compile("[0-9]*");
        Matcher isNum = pattern.matcher(transferNO);
        if( !isNum.matches() ){
        	//非全数字
        	setTRANSDNIS(mySession);
        }else  {
        	//全数字
        	setTRANSDNISnumber(mySession);
		}
       
	}
	
	/**
	 * 全数字情况下
	 * @ClassName: setTRANSDNISnumber 
	 * @Description: seven
	 * @author 全数字情况下将判断是否是白名单，若是白名单则赋值白名单，若非白名单则直接赋值
	 * @date 2021年7月23日 下午2:23
	 *  
	 */
	public void setTRANSDNISnumber(com.avaya.sce.runtimecommon.SCESession mySession){
		
		String white = mySession.getVariableField(IProjectVariables.WHITE).getStringValue();
		if(white.equals("1")){
			String whiteVDN = mySession.getVariableField(IProjectVariables.WHITE_VDN).getStringValue();
			System.out.println("GetVND该用户是白名单");
			mySession.getVariableField(IProjectVariables.TRANS_DNIS).setValue(whiteVDN);
		}
		String viptype = mySession.getVariableField(IProjectVariables.VIPTYPE).getStringValue();
		String ani = mySession.getVariableField(IProjectVariables.SESSION,IProjectVariables.SESSION_FIELD_ANI).getStringValue();
		String dnis = mySession.getVariableField(IProjectVariables.SESSION,IProjectVariables.SESSION_FIELD_DNIS).getStringValue();
		String trans_dnis = mySession.getVariableField(IProjectVariables.TRANS_DNIS).getStringValue();
		String uui=viptype+"&"+ani+"&"+dnis+"&"+trans_dnis;
		System.out.println("GetVND全数字情况下进线UUI："+uui);
		mySession.getVariableField(IProjectVariables.UUI).setValue(uui);
	}
	
	/**
	 * 非全数字情况下
	 * @ClassName: setTRANSDNIS 
	 * @Description: seven
	 * @author 非全数字情况下将判断是否是白名单，若是白名单则赋值白名单，若非白名单则从数据库中获取TRANS_DNIS对应的
	 * @date 2021年7月23日 下午2:23
	 *  
	 */
	public void setTRANSDNIS(com.avaya.sce.runtimecommon.SCESession mySession){
		
		String white = mySession.getVariableField(IProjectVariables.WHITE).getStringValue();
		String viptype = mySession.getVariableField(IProjectVariables.VIPTYPE).getStringValue();
		String ani = mySession.getVariableField(IProjectVariables.SESSION,IProjectVariables.SESSION_FIELD_ANI).getStringValue();
		String dnis = mySession.getVariableField(IProjectVariables.SESSION,IProjectVariables.SESSION_FIELD_DNIS).getStringValue();
		String trans_dnis = mySession.getVariableField(IProjectVariables.TRANS_DNIS).getStringValue();
		if(white.equals("1")){
			String whiteVDN = mySession.getVariableField(IProjectVariables.WHITE_VDN).getStringValue();
			System.out.println("GetVND该用户是白名单");
			mySession.getVariableField(IProjectVariables.TRANS_DNIS).setValue(whiteVDN);
		}else {
			getVNDsql(trans_dnis,viptype,white,mySession);
		}
		trans_dnis = mySession.getVariableField(IProjectVariables.TRANS_DNIS).getStringValue();
		String uui=viptype+"&"+ani+"&"+dnis+"&"+trans_dnis;
		System.out.println("GetVND全数字情况下进线UUI："+uui);
		mySession.getVariableField(IProjectVariables.UUI).setValue(uui);
	}
	
	/**
	 * 数据库搜索VDN_ROUTER表  获取对应的VDN
	 * @ClassName: GetVND 
	 * @Description: seven
	 * @author transDnis 是存储的技能组的值， VipType 是用户的VIP等级0，1是普通用户，黑名单     1后都是VIP
	 * @date 2021年7月23日 下午2:23
	 *  
	 */
	public String getVNDsql (String transDnis,String VipType,String white,com.avaya.sce.runtimecommon.SCESession mySession){
		String SkillLevel=null;
		String SkillType=transDnis;
		String dbType = mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_TYPE).getStringValue();
		String user=null;
		String password=null;
		String url=null;
		String driver=null;
		if(dbType.equals("1")){
			user=mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_USER_1).getStringValue();
			password=mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_PASSWORD_1).getStringValue();
			url=mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_URL_1).getStringValue();
			driver=mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_DRIVER_1).getStringValue();
		}else {
			user=mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_USER_2).getStringValue();
			password=mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_PASSWORD_2).getStringValue();
			url=mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_URL_2).getStringValue();
			driver=mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_DRIVER_2).getStringValue();
		}
		String sql=null;
		if(white.equals("1")){
			sql ="SELECT * from VDN_ROUTER v where v.BusinessType='Heytap' and v.SkillLevel='SVIP'  ORDER BY Phone_number";
		
		}else if(VipType.equals("0")||VipType.equals("1")||VipType.equals("")){
			sql ="SELECT * from VDN_ROUTER v where v.BusinessType='Heytap' and v.SkillLevel='VIP' and v.SkillType='"+SkillType+"' ORDER BY Phone_number";
			
		}else {
			sql ="SELECT * from VDN_ROUTER v where v.BusinessType='Heytap' and v.SkillLevel='PT' and v.SkillType='"+SkillType+"' ORDER BY Phone_number";
			
		}
		try {
			ResultSet rs=null;
			Connection conn = null;
			PreparedStatement pre =null;
			System.out.println(url + "+url+" + user + "+username+"
					+ password + "+password+" + driver + "+driver");

			conn = null;
			Class.forName(driver);
			//System.out.println(mySession.getVariableField("创建数据库连接成功");
			log.info("创建数据库连接成功");
			DriverManager.setLoginTimeout(2);
			conn = DriverManager.getConnection(url, user, password);
			log.info("创建数据库连接成功----并成功登入登入账户为"+user);
			pre = conn.prepareStatement(sql);
			log.info("运行的getVDN——sql语句是："+sql);
	        rs=pre.executeQuery();
	        List<Vdn> list = new ArrayList<Vdn>();
	        List<Vdn> list2 = new ArrayList<Vdn>();
	        while(rs.next()){
            	Vdn md = new Vdn();
            	if(rs.getString("Phone_number")==null ||rs.getString("Phone_number").length()==0){
            		md.setBusinessType(rs.getString("BusinessType"));
            		md.setSkillType(rs.getString("SkillType"));
            		md.setSkillName(rs.getString("SkillName"));
            		md.setPhone_number(rs.getString("Phone_number"));
            		md.setSkillVdn(rs.getString("SkillVdn"));    	
            		list.add(md);
            	}
            	else {
            		md.setBusinessType(rs.getString("BusinessType"));
            		md.setSkillType(rs.getString("SkillType"));
            		md.setSkillName(rs.getString("SkillName"));
            		md.setPhone_number(rs.getString("Phone_number"));
            		md.setSkillVdn(rs.getString("SkillVdn"));        	
            		list2.add(md);
				}
            }
	        for(Vdn v : list){
				System.out.println(v.getSkillVdn());
				mySession.getVariableField(IProjectVariables.TRANS_DNIS).setValue(v.getSkillVdn());
			}
	        String ani = mySession.getVariableField(IProjectVariables.SESSION,IProjectVariables.SESSION_FIELD_ANI).getStringValue();
			String substring = ani.substring(0, 3);
			for(Vdn v : list2){
        		String[] split = v.getPhone_number().split("-");
        		if(split.length==2){
        			int min = Integer.parseInt(split[0]);
        			int max = Integer.parseInt(split[1]);
        			int a = Integer.parseInt(substring);
        			if(min<=a && max>=a){
        				System.out.println(v.getSkillType());
        				mySession.getVariableField(IProjectVariables.TRANS_DNIS).setValue(v.getSkillVdn());
        				
        			}
        		}
        	}
		} catch (Exception e) {
			log.info("运行的getVDN——数据库查询出现错误sql语句是："+sql+"   错误信息是："+e.getMessage());
		}
		
		
		
		return null;
	}
	
}
