using AutoCheckSMS.Common;
|
using Microsoft.Practices.EnterpriseLibrary.Data;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace AutoCheckSMS
|
{
|
/// <summary>
|
/// 数据库操作类
|
/// </summary>
|
public class SQLHelper
|
{
|
Database da = DatabaseFactory.CreateDatabase("ConnectionString");
|
private static SQLHelper _instance;
|
public static SQLHelper Instance
|
{
|
get
|
{
|
if (_instance == null)
|
{
|
_instance = new SQLHelper();
|
}
|
return _instance;
|
}
|
}
|
|
#region 审核操作
|
/// <summary>
|
/// 获取待审核内容
|
/// </summary>
|
/// <returns></returns>
|
public List<tbl_sms_task> GetSmsConent()
|
{
|
List<tbl_sms_task> list = new List<tbl_sms_task>();
|
string[] Out = System.Configuration.ConfigurationManager.AppSettings["OutUserid"].Trim().Split(',');
|
|
string U_sql = "select userid,parentuserid from tbl_user where isuse=1 and isdelete=0";
|
DataSet U_ds1 = da.ExecuteDataSet(CommandType.Text, U_sql);
|
DataTable U_dt = U_ds1.Tables[0];
|
List<GetUserID> IGetID = new List<GetUserID>();
|
foreach (DataRow item in U_dt.Rows)
|
{
|
IGetID.Add(new GetUserID{
|
UserID = item[0].ToString(),
|
ParentuserID = item[1].ToString()
|
});
|
}
|
List<GetUserID> GetID = GetSonUsserID(Out, IGetID);
|
|
string[] I_Out = new string[GetID.Count];
|
int i = 0;
|
foreach (var item in GetID)
|
{
|
I_Out[i] = item.UserID;
|
i++;
|
}
|
Log4netService.Debug($"过滤ID个数:{GetID.Count}");
|
try
|
{
|
string time = DateTime.Now.AddHours(-1).ToString("yyyy-MM-dd HH:mm");
|
string sql = $"select TaskID,UserID,MsgContent,ReadError,IsAudit from tbl_sms_task where CreateTime>= '{time}'and IsAudit=0 and ReadError like '%分包完成%' and msgContent not like '%退' and MsgContent not like '%回_退%' and MsgContent not like '%退订%' and MsgContent not like '%退_T%' and MsgContent not like '%.c%' and MsgContent not like '%点对点%'";
|
DataSet ds = da.ExecuteDataSet(CommandType.Text, sql);
|
DataTable dt = ds.Tables[0];
|
if (dt.Rows.Count > 0)
|
{
|
foreach (DataRow dr in dt.Rows)
|
{
|
if (!I_Out.Contains(dr["UserID"].ToString().Trim()))
|
{
|
int taskid = int.Parse(dr["TaskID"].ToString());
|
int userid = int.Parse(dr["UserID"].ToString());
|
string content = dr["MsgContent"].ToString();
|
int isaudit = int.Parse(dr["IsAudit"].ToString());
|
list.Add(new tbl_sms_task() { TaskID = taskid, UserID = userid, MsgContent = content, IsAudit = isaudit });
|
}
|
else
|
{
|
Console.WriteLine($"【审核操作】存在过滤ID：{dr["UserID"].ToString().Trim()},跳过该ID内容");
|
Log4netService.Debug($"【审核操作】存在过滤ID：{dr["UserID"].ToString().Trim()},跳过该ID内容：{dr["MsgContent"]}");
|
}
|
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
Log4netService.Error($"获取待审核内容异常：{ex.Message}");
|
}
|
return list;
|
}
|
|
/// <summary>
|
/// 递归获取获取子ID
|
/// </summary>
|
/// <param name="UserID">传入一级ID</param>
|
/// <param name="GetID">传入账号表</param>
|
/// <returns></returns>
|
public static List<GetUserID> GetSonUsserID(string[] UserID, List<GetUserID> GetID)
|
{
|
List<GetUserID> LGetID = new List<GetUserID>();
|
List<GetUserID> LID = new List<GetUserID>();
|
for (int i = 0; i < UserID.Length; i++)
|
{
|
LID.AddRange(GetID.Where(a => a.ParentuserID == UserID[i]).ToList());//将GetID中符合一级ID的所有子ID传入LID
|
|
}
|
if (LID.Count != 0)//递归条件
|
{
|
string[] SonID = new string[LID.Count];
|
int i = 0;
|
foreach (var item in LID)
|
{
|
//Console.WriteLine($"递归 ID：{item.ParentuserID} 子ID：{item.UserID}");
|
SonID[i] = item.UserID;
|
i++;
|
}//将子ID转成数组
|
//for (int l = 0; l < SonID.Length; l++)
|
//{
|
// Console.WriteLine($"需要递归的ID：{SonID[l]}");
|
//}
|
|
for (int h = 0; h < UserID.Length; h++)
|
{
|
LGetID.Add(new GetUserID
|
{
|
UserID = UserID[h].ToString(),
|
ParentuserID = 0.ToString()
|
});
|
}
|
LGetID.AddRange(GetSonUsserID(SonID, GetID));
|
}
|
else
|
{
|
for (int i = 0; i < UserID.Length; i++)
|
{
|
LGetID.Add(new GetUserID
|
{
|
UserID = UserID[i].ToString(),
|
ParentuserID = 0.ToString()
|
});
|
}
|
return LGetID;
|
}
|
return LGetID;
|
}
|
|
public class GetUserID
|
{
|
public string UserID { get; set; }
|
public string ParentuserID { get; set; }
|
}
|
|
|
/// <summary>
|
/// 更新审核状态
|
/// </summary>
|
/// <param name="audit"></param>
|
/// <returns></returns>
|
public int UpdateSmsTask(int audit,int taskids)
|
{
|
try
|
{
|
string sql = "update tbl_sms_task set IsAudit=@isAudit where taskid in (@taskids)";
|
SqlCommand cmd = new SqlCommand(sql);
|
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@isAudit", Value = audit });
|
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@taskids", Value = taskids });
|
return da.ExecuteNonQuery(cmd);
|
}
|
catch (Exception ex)
|
{
|
Log4netService.Error($"更新状态异常：{ex.Message}");
|
}
|
return 0;
|
}
|
|
|
#endregion
|
|
|
#region 模板操作
|
|
/// <summary>
|
/// 获取手动审核通过的内容
|
/// </summary>
|
/// <returns></returns>
|
public List<tbl_sms_task> GetSmsLogContent()
|
{
|
List<tbl_sms_task> list = new List<tbl_sms_task>();
|
try
|
{
|
string[] OutUserid = System.Configuration.ConfigurationManager.AppSettings["OutUserid"].Trim().Split(',');
|
string time = DateTime.Now.AddHours(-1).ToString("yyyy-MM-dd HH:mm");
|
string sql = $"select TaskID,UserID,MsgContent,ReadError,IsAudit from tbl_sms_task_log where CreateTime>= '{time}' and issend=2 and (MsgContent not like '%退订%' and MsgContent not like '%.c%' and MsgContent not like '%点对点%') and IsAudit=2";
|
DataSet ds = da.ExecuteDataSet(CommandType.Text, sql);
|
DataTable dt = ds.Tables[0];
|
if (dt.Rows.Count > 0)
|
{
|
foreach (DataRow dr in dt.Rows)
|
{
|
foreach (var item in OutUserid)
|
{
|
if (dr["UserID"].ToString() != item)
|
{
|
int taskid = int.Parse(dr["TaskID"].ToString());
|
int userid = int.Parse(dr["UserID"].ToString());
|
string content = dr["MsgContent"].ToString();
|
int isaudit = int.Parse(dr["IsAudit"].ToString());
|
list.Add(new tbl_sms_task() { TaskID = taskid, UserID = userid, MsgContent = content, IsAudit = isaudit });
|
}
|
else
|
{
|
Console.WriteLine($"存在过滤ID：{item},跳过该ID内容");
|
Log4netService.Debug($"【模板操作】存在过滤ID：{item},跳过该ID内容：{dr["MsgContent"]}");
|
}
|
}
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
Log4netService.Error($"获取已审核的内容异常：{ex.Message}");
|
}
|
|
return list;
|
}
|
|
|
/// <summary>
|
/// 获取模板
|
/// </summary>
|
/// <returns></returns>
|
public List<A_template> GetTemplates()
|
{
|
List<A_template> list = new List<A_template>();
|
string sql = $"select id,template,matchType from A_template ";
|
DataSet ds = da.ExecuteDataSet(CommandType.Text, sql);
|
DataTable dt = ds.Tables[0];
|
if (dt.Rows.Count > 0)
|
{
|
foreach (DataRow dr in dt.Rows)
|
{
|
int id = int.Parse(dr["id"].ToString());
|
string template = dr["template"].ToString();
|
int matchType = int.Parse(dr["matchType"].ToString());
|
list.Add(new A_template() { Id=id,Template=template,MatchType= matchType });
|
}
|
}
|
return list;
|
}
|
|
/// <summary>
|
/// 添加模板
|
/// </summary>
|
/// <param name="template"></param>
|
/// <param name="matchType"></param>
|
/// <returns></returns>
|
public int AddTemplate(string template,int matchType)
|
{
|
string sql = "insert into A_template (template,matchType) values (@template,@matchType)";
|
SqlCommand cmd = new SqlCommand(sql);
|
cmd.Parameters.Add(new SqlParameter() {ParameterName="@template",Value=template });
|
cmd.Parameters.Add(new SqlParameter() { ParameterName= "@matchType", Value=matchType});
|
return da.ExecuteNonQuery(cmd);
|
}
|
|
#endregion
|
|
}
|
}
|
