public bool CheckPhoneAndTelIsInBlackList(string Verifycode, BlackListCheckType blackListCheckType, string phone) { string msg = ""; if (BLL.CallRecord_ORIG_Authorizer.Instance.Verify(Verifycode, 0, ref msg, "校验电话号码是否黑名单,授权失败。")) { return(BLL.BlackWhiteList.Instance.CheckPhoneAndTelIsInBlackList(blackListCheckType, phone)); } //没有权限,返回是黑名单 return(true); }
/// 验证是否黑名单 /// <summary> /// 验证是否黑名单 /// </summary> /// <param name="projectID">项目ID</param> /// <param name="phone">号码</param> /// <returns>true 是黑名单 false 不是</returns> public bool CheckPhoneAndTelIsInBlackList(long projectID, string phone) { Entities.ProjectInfo pinfo = BLL.ProjectInfo.Instance.GetProjectInfo(projectID); //是否启用黑名单校验 1启用 0禁用 if (pinfo.IsBlacklistCheck.GetValueOrDefault(-2) == 1) { if (pinfo.BlacklistCheckType.HasValue) { BlackListCheckType blackListCheckType = (BlackListCheckType)pinfo.BlacklistCheckType.Value; return(CheckPhoneAndTelIsInBlackList(blackListCheckType, phone)); } } //不进行验证,不是黑名单 return(false); }
/// 验证是否黑名单 /// <summary> /// 验证是否黑名单 /// </summary> /// <param name="blackListCheckType"></param> /// <param name="phone"></param> /// <returns></returns> public bool CheckPhoneAndTelIsInBlackList(BlackListCheckType blackListCheckType, string phone) { phone = SqlFilter(phone); using (SqlConnection conn = new SqlConnection(CONNECTIONSTRINGS)) { conn.Open(); //先验证CC端 if ((blackListCheckType & BlackListCheckType.BT2_CC) == BlackListCheckType.BT2_CC) { string sql = "SELECT COUNT(*) FROM dbo.BlackWhiteList WHERE Status=0 AND Type=0 AND PhoneNum='" + phone + "'"; int num = (int)SqlHelper.ExecuteScalar(conn, CommandType.Text, sql); if (num > 0) { //是CC的黑名单 return(true); } } //再验证CRM端 if ((blackListCheckType & BlackListCheckType.BT1_CRM) == BlackListCheckType.BT1_CRM) { //验证联系人表 会员表 客户表 string sql = @"SELECT COUNT(*) FROM ( SELECT 0 AS typeid FROM CRM2009.dbo.ContactInfo WHERE Status=0 AND (REPLACE(OfficeTel,'-','')='" + phone + @"' OR REPLACE(Phone,'-','')='" + phone + @"') UNION SELECT 1 AS typeid FROM CRM2009.dbo.DMSMember WHERE Status=0 AND REPLACE(Phone,'-','')='" + phone + @"' UNION SELECT 2 AS typeid FROM CRM2009.dbo.CustInfo WHERE Status=0 AND REPLACE(OfficeTel,'-','')='" + phone + @"' ) tmp"; int num = (int)SqlHelper.ExecuteScalar(conn, CommandType.Text, sql); if (num > 0) { //是CRM的黑名单 return(true); } } } //不是黑名单 return(false); }
/// 验证是否黑名单 /// <summary> /// 验证是否黑名单 /// </summary> /// <param name="blackListCheckType">验证类型</param> /// <param name="phone">号码</param> /// <returns>true 是黑名单 false 不是</returns> public bool CheckPhoneAndTelIsInBlackList(BlackListCheckType blackListCheckType, string phone) { return(Dal.BlackWhiteList.Instance.CheckPhoneAndTelIsInBlackList(blackListCheckType, phone)); }
/// 删除重复的电话号码-其他任务导入 /// <summary> /// 删除重复的电话号码-其他任务导入 /// </summary> /// <param name="projectid"></param> /// <param name="tableName"></param> /// <param name="phoneCol"></param> /// <returns></returns> public Dictionary <string, List <string> > DelSameTelForImportOtherData(long projectid, string tableName, string phoneCol, string[] rangeid, BlackListCheckType blacklistchecktype, Action <string> logFunc) { logFunc("数据校验:tableName=" + tableName + " phoneCol=" + phoneCol + " rangeid[0]=" + rangeid[0] + " rangeid[1]=" + rangeid[1] + " projectid=" + projectid + " blacklistchecktype=" + blacklistchecktype); Dictionary <string, List <string> > dic = new Dictionary <string, List <string> >(); using (SqlConnection conn = new SqlConnection(CONNECTIONSTRINGS)) { Stopwatch sw = new Stopwatch(); sw.Start(); conn.Open(); //查找重复的需要删除的数据 string s_sql = @"SELECT * FROM ( SELECT RecID," + phoneCol + @",row_number() OVER (PARTITION BY " + phoneCol + @" ORDER BY RecID ASC) AS rowid FROM ( SELECT * FROM " + tableName + @" WHERE (RecID IN (SELECT RelationID FROM dbo.ProjectDataSoure WHERE ProjectID=" + projectid + @")) --已有的项目下的数据 UNION ALL SELECT * FROM " + tableName + @" WHERE (RecID>=" + rangeid[0] + @" AND RecID<=" + rangeid[1] + @")--刚刚导入的数据 ) t ) tmp WHERE tmp.rowid<>1 And ISNULL(tmp." + phoneCol + @",'')<>'' ORDER BY tmp.RecID"; DataTable dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, s_sql).Tables[0]; List <string> delids = CommonFunction.DataTableToList <string>(dt, "RecID", "RecID", ""); dic.Add("重复数据", delids); logFunc("重复数据校验耗时:" + sw.Elapsed.ToString()); sw.Reset(); sw.Start(); //校验项目验证方式 if ((blacklistchecktype & BlackListCheckType.BT2_CC) == BlackListCheckType.BT2_CC) { s_sql = @"SELECT RecID FROM " + tableName + @" WHERE " + phoneCol + @" IN ( SELECT PhoneNum FROM dbo.BlackWhiteList WHERE Status=0 AND Type=0) AND RecID>=" + rangeid[0] + @" AND RecID<=" + rangeid[1]; dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, s_sql).Tables[0]; List <string> ccdel = CommonFunction.DataTableToList <string>(dt, "RecID", "RecID", ""); dic.Add("CC黑名单", ccdel); logFunc("CC黑名单校验耗时:" + sw.Elapsed.ToString()); sw.Reset(); sw.Start(); } if ((blacklistchecktype & BlackListCheckType.BT1_CRM) == BlackListCheckType.BT1_CRM) { string s_tmp = @"SELECT OfficeTel as phone INTO #tmp1 FROM ( SELECT REPLACE(OfficeTel,'-','') AS OfficeTel,1 AS typeid FROM CRM2009.dbo.ContactInfo WHERE Status=0 AND ISNULL(OfficeTel,'')<>'' AND ISNUMERIC(REPLACE(OfficeTel,'-',''))=1 UNION SELECT REPLACE(Phone,'-','') AS Phone,2 AS typeid FROM CRM2009.dbo.ContactInfo WHERE Status=0 AND ISNULL(Phone,'')<>'' AND ISNUMERIC(REPLACE(Phone,'-',''))=1 UNION SELECT REPLACE(Phone,'-','') AS Phone,3 AS typeid FROM CRM2009.dbo.DMSMember WHERE Status=0 AND ISNULL(Phone,'')<>'' AND ISNUMERIC(REPLACE(Phone,'-',''))=1 UNION SELECT REPLACE(OfficeTel,'-','') AS OfficeTel,4 AS typeid FROM CRM2009.dbo.CustInfo WHERE Status=0 AND ISNULL(Officetel,'')<>'' AND ISNUMERIC(REPLACE(OfficeTel,'-',''))=1 ) tmp"; SqlHelper.ExecuteNonQuery(conn, CommandType.Text, s_tmp); s_sql = @"SELECT RecID FROM " + tableName + @" INNER JOIN #tmp1 ON " + phoneCol + @"=#tmp1.phone WHERE RecID>=" + rangeid[0] + " AND RecID<=" + rangeid[1]; dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, s_sql).Tables[0]; List <string> crmdel = CommonFunction.DataTableToList <string>(dt, "RecID", "RecID", ""); dic.Add("CRM黑名单", crmdel); logFunc("CRM黑名单校验耗时:" + sw.Elapsed.ToString()); sw.Reset(); sw.Start(); } sw.Stop(); } //返回结果 return(dic); }
/// 删除重复的电话号码-其他任务导入 /// <summary> /// 删除重复的电话号码-其他任务导入 /// </summary> /// <param name="projectid"></param> /// <param name="ttname"></param> /// <param name="tfname"></param> /// <returns></returns> public Dictionary <string, List <string> > DelSameTelForImportOtherData(long projectid, string ttname, string tfname, string[] rangeid, BlackListCheckType blacklistchecktype) { return(Dal.OtherTaskInfo.Instance.DelSameTelForImportOtherData(projectid, ttname, tfname, rangeid, blacklistchecktype, new Action <string>(BLL.Loger.Log4Net.Info))); }