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)));
}