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>();
|
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 '%.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)
|
{
|
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 });
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
Log4netService.Error($"获取待审核内容异常：{ex.Message}");
|
}
|
return list;
|
}
|
|
/// <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 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)
|
{
|
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 });
|
}
|
}
|
}
|
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
|
|
}
|
}
|
