/// <summary>
/// 根据搜索条件,推送计划Id列表,查询出模板变更记录列表【2】
/// </summary>
/// <param name="pushPlanIds"></param>
/// <param name="query"></param>
/// <returns></returns>
public static async Task <List <TemplateModifyLogInfo> > GetTemplateModifyLogInfoWithIdsAndQueryInfo(List <int> pushPlanIds, TemplateLogQueryInfo query)
{
var stringBuilder = new StringBuilder();
if (query == null)
{
query = new TemplateLogQueryInfo()
{
PageIndex = 1, PageSize = 15
};
}
var parameters = new List <SqlParameter>
{
new SqlParameter("@pagesize", query.PageSize == 0 ? 50 : query.PageSize),
new SqlParameter("@pageindex", query.PageIndex == 0 ? 1 : query.PageIndex),
};
stringBuilder.Append("SELECT Pkid, [User] as ModifyUser,OriginTemplate,NewTemplate,TemplateId as PushPlanId,LastUpdateDateTime from Tuhu_log..PushTemplateModifyLog WITH(NOLOCK) where templateId>0 ");
if (!pushPlanIds.Any())
{
pushPlanIds.Add(0);
}
stringBuilder.Append($" and TemplateId in({string.Join(",", pushPlanIds)})");
if (!string.IsNullOrEmpty(query.ModifyUser))
{
parameters.Add(new SqlParameter("@ModifyUser", query.ModifyUser));
stringBuilder.Append($" and [user] like '%' + @ModifyUser + '%'");
}
if (query.ModifyStartTime.HasValue)
{
parameters.Add(new SqlParameter("@ModifyStartTime", query.ModifyStartTime.Value));
stringBuilder.Append($" and lastupdateDateTime>=@ModifyStartTime ");
}
if (query.ModifyEndTime.HasValue)
{
parameters.Add(new SqlParameter("@ModifyEndTime", query.ModifyEndTime.Value));
stringBuilder.Append($" and lastupdateDateTime<=@ModifyEndTime ");
}
stringBuilder.Append($"order by Pkid desc OFFSET ( @pagesize * (@pageindex - 1 ) ) ROWS FETCH NEXT @pagesize ROWS ONLY;");
var conn = ConfigurationManager.ConnectionStrings["Tuhu_log"].ConnectionString;
if (SecurityHelp.IsBase64Formatted(conn))
{
conn = SecurityHelp.DecryptAES(conn);
}
using (var dbHelper = new Tuhu.Component.Common.SqlDbHelper(conn))
{
return((await dbHelper.ExecuteDataTableAsync(stringBuilder.ToString(), CommandType.Text, parameters.ToArray())).ConvertTo <TemplateModifyLogInfo>()?.ToList());
}
}
/// <summary>
/// 根据搜索条件,推送计划Id列表,查询出模板变更记录列表【2】
/// </summary>
/// <param name="pushPlanIds"></param>
/// <param name="query"></param>
/// <returns></returns>
public static async Task <int> GetTemplateModifyLogCountWithIdsAndQueryInfo(List <int> pushPlanIds, TemplateLogQueryInfo query)
{
var stringBuilder = new StringBuilder();
var parameters = new List <SqlParameter>();
if (query == null)
{
query = new TemplateLogQueryInfo()
{
};
}
stringBuilder.Append("SELECT count(1) from Tuhu_log..PushTemplateModifyLog WITH(NOLOCK) where 1=1 ");
if (!pushPlanIds.Any())
{
pushPlanIds.Add(0);
}
stringBuilder.Append($" and TemplateId in({string.Join(",", pushPlanIds)})");
if (!string.IsNullOrEmpty(query.ModifyUser))
{
parameters.Add(new SqlParameter("@ModifyUser", query.ModifyUser));
stringBuilder.Append($" and [user] like '%' + @ModifyUser + '%'");
}
if (query.ModifyStartTime.HasValue)
{
parameters.Add(new SqlParameter("@ModifyStartTime", query.ModifyStartTime.Value));
stringBuilder.Append($" and lastupdateDateTime>=@ModifyStartTime ");
}
if (query.ModifyEndTime.HasValue)
{
parameters.Add(new SqlParameter("@ModifyEndTime", query.ModifyEndTime.Value));
stringBuilder.Append($" and lastupdateDateTime<=@ModifyEndTime ");
}
var conn = ConfigurationManager.ConnectionStrings["Tuhu_log"].ConnectionString;
if (SecurityHelp.IsBase64Formatted(conn))
{
conn = SecurityHelp.DecryptAES(conn);
}
using (var dbHelper = new Tuhu.Component.Common.SqlDbHelper(conn))
{
var result = await dbHelper.ExecuteScalarAsync(stringBuilder.ToString(), CommandType.Text, parameters.ToArray());
int count = 1;
int.TryParse(result?.ToString(), out count);
return(count);
}
}
/// <summary>
/// 搜索满足条件的数据，最多抓取500条记录
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public static async Task <List <TemplateModifyLogInfo> > GetExportTemplateModifyLogDatas(TemplateLogQueryInfo query)
{
try
{
query.PageIndex = 1;
query.PageSize = 500;
var pushIds = await GetPushPlanIdsWithQueryInfo(query);
var logList = await GetTemplateModifyLogInfoWithIdsAndQueryInfo(pushIds, query);
var countresult = await GetTemplateModifyLogCountWithIdsAndQueryInfo(pushIds, query);
var templateList = await GetTemplateInfoFromIds(logList?.Select(o => o.PushPlanId).Distinct().ToList());
foreach (var item in logList)
{
var templateInfo = templateList.FirstOrDefault(o => o.PushPlanId == item.PushPlanId);
if (templateInfo == null)
{
continue;
}
item.TemplateId = templateInfo.TemplateId;
item.TemplateTitle = templateInfo.TemplateTitle;
item.PushPlanTitle = templateInfo.PushPlanTitle;
item.DeviceType = templateInfo.DeviceType;
item.NewTemplate = item.NewTemplate.Replace('\"', '\'');
item.OriginTemplate = item.OriginTemplate.Replace('\"', '\'');
}
return(logList);
}
catch (Exception ex)
{
return(new List <TemplateModifyLogInfo>());
}
}
/// <summary>
/// 分页获取操作历史列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public static async Task <TemplateLogInfoQueryResult> SelectPushTemplateModifyLogsAsync(TemplateLogQueryInfo query)
{
try
{
var pushIds = await GetPushPlanIdsWithQueryInfo(query);
var logList = await GetTemplateModifyLogInfoWithIdsAndQueryInfo(pushIds, query);
var countresult = await GetTemplateModifyLogCountWithIdsAndQueryInfo(pushIds, query);
var templateList = await GetTemplateInfoFromIds(logList?.Select(o => o.PushPlanId).Distinct().ToList());
foreach (var item in logList)
{
var templateInfo = templateList.FirstOrDefault(o => o.PushPlanId == item.PushPlanId);
if (templateInfo == null)
{
continue;
}
item.TemplateId = templateInfo.TemplateId;
item.TemplateTitle = templateInfo.TemplateTitle;
item.PushPlanTitle = templateInfo.PushPlanTitle;
item.DeviceType = templateInfo.DeviceType;
}
var result = new TemplateLogInfoQueryResult()
{
TotalCount = countresult,
PageIndex = query.PageIndex,
Result = logList.ToList(),
PageSize = query.PageSize
};
return(result);
}
catch (Exception ex)
{
return(new TemplateLogInfoQueryResult()
{
});
}
}
/// <summary>
/// 根据搜索条件查询出推送计划Id列表【1】
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public static async Task <List <int> > GetPushPlanIdsWithQueryInfo(TemplateLogQueryInfo query)
{
var wheresb = new StringBuilder();
var parameters = new List <SqlParameter>()
{
};
wheresb.Append("SELECT pkid FROM Tuhu_notification..tbl_PushTemplate WITH(NOLOCK) where 1=1 ");
if (query.TemplateId > 0)
{
parameters.Add(new SqlParameter("@BatchId", query.TemplateId));
wheresb.Append($" and batchId=@BatchId ");
}
if (query.PushPlanId > 0)
{
parameters.Add(new SqlParameter("@PushPlanId", query.PushPlanId));
wheresb.Append($" and pkid=@PushPlanId ");
}
if (!string.IsNullOrEmpty(query.PushPlanTitle))
{
parameters.Add(new SqlParameter("@PushPlanTitle", query.PushPlanTitle));
wheresb.Append($" and title like '%' + @PushPlanTitle + '%' ");
}
if (query.DeviceType.HasValue)
{
parameters.Add(new SqlParameter("@DeviceType", (int)query.DeviceType.Value));
wheresb.Append($" and DeviceType=@DeviceType ");
}
wheresb.Append("order by pkid desc OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY;");
var conn = ConfigurationManager.ConnectionStrings["Tuhu_log"].ConnectionString;
if (SecurityHelp.IsBase64Formatted(conn))
{
conn = SecurityHelp.DecryptAES(conn);
}
try
{
using (var dbHelper = new Tuhu.Component.Common.SqlDbHelper(conn))
{
var result = await dbHelper.ExecuteDataTableAsync(wheresb.ToString(), CommandType.Text, parameters.ToArray());
if (result != null && result.Rows.Count > 0)
{
var data = result.AsEnumerable().Select(x => int.Parse(x[0].ToString())).ToList();
return(data);
}
return(new List <int>());
}
}
catch (Exception ex)
{
throw;
}
}