/// <summary>
/// 查询分页
/// </summary>
/// <param name="pageIndex">页码，从0开始</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="filter">过滤器</param>
/// <param name="connectionId">连接ID</param>
/// <returns>分页信息</returns>
public PagingInfo <JobTaskInfo> QueryPage(int pageIndex, int pageSize, JobTaskFilterInfo filter = null, string connectionId = null)
{
var q = QueryForFilter(filter);
if (q == null)
{
return(null);
}
var page = new PagingInfo <JobTaskInfo>()
{
PageIndex = pageIndex,
PageSize = pageSize,
Records = q.Count()
};
if (page.Records == 0)
{
return(page);
}
page.Rows = q.Skip(pageIndex.GetSkipRecordIndex(pageSize)).Take(pageSize).ToList();
return(page);
}
/// <summary>
/// 为过滤器获取查询表达式
/// </summary>
/// <param name="filter">过滤器</param>
/// <returns>查询表达式</returns>
private IQueryable <JobTaskInfo> QueryForFilter(JobTaskFilterInfo filter = null)
{
if (list.IsNullOrCount0())
{
return(null);
}
var query = list.AsQueryable();
if (filter == null)
{
return(query);
}
if (!filter.Ids.IsNullOrLength0())
{
query = query.Where(p => filter.Ids.Contains(p.Id));
}
if (!filter.JtNames.IsNullOrLength0())
{
query = query.Where(p => filter.JtNames.Contains(p.JtName, true));
}
if (!filter.JtGroups.IsNullOrLength0())
{
query = query.Where(p => filter.JtGroups.Contains(p.JtGroup, true));
}
if (!filter.JobFullClasses.IsNullOrLength0())
{
query = query.Where(p => filter.JobFullClasses.Contains(p.JobFullClass, true));
}
if (!filter.TriggerCrons.IsNullOrLength0())
{
query = query.Where(p => filter.TriggerCrons.Contains(p.TriggerCron, true));
}
if (filter.SuccessedRemove != null)
{
query = query.Where(p => p.SuccessedRemove == filter.SuccessedRemove.Value);
}
if (!string.IsNullOrWhiteSpace(filter.Keyword))
{
query = query.Where(p => p.JtName.Contains(filter.Keyword) || p.JtGroup.Contains(filter.Keyword));
}
if (filter.StartCreateTime != null)
{
query = query.Where(p => p.CreateTime >= filter.StartCreateTime);
}
if (filter.EndCreateTime != null)
{
query = query.Where(p => p.CreateTime <= filter.EndCreateTime);
}
if (!string.IsNullOrWhiteSpace(filter.SortName))
{
query = query.OrderBy(new Dictionary <string, SortType>(1)
{
{ filter.SortName, filter.Sort }
});
}
return(query);
}
/// <summary>
/// 获取条件SQL
/// </summary>
/// <param name="filter">过滤器</param>
/// <returns>条件SQL</returns>
private string GetWhereSql(ref JobTaskFilterInfo filter)
{
var sql = CreateWhereSql();
if (filter == null)
{
filter = new JobTaskFilterInfo();
return(sql.ToString());
}
var nameField = GetFieldByProp("JtName");
var groupField = GetFieldByProp("JtGroup");
if (!filter.Ids.IsNullOrLength0())
{
sql.AppendFormat(" AND {0}{1}{2} IN({3})", PfxEscapeChar, GetFieldByProp("Id"), SufxEscapeChar, filter.Ids.ToMergeString(","));
}
if (!filter.JtNames.IsNullOrLength0())
{
sql.AppendFormat(" AND {0}{1}{2} IN({3})", PfxEscapeChar, nameField, SufxEscapeChar, filter.JtNames.ToMergeString(",", "'"));
}
if (!filter.JtGroups.IsNullOrLength0())
{
sql.AppendFormat(" AND {0}{1}{2} IN({3})", PfxEscapeChar, groupField, SufxEscapeChar, filter.JtGroups.ToMergeString(",", "'"));
}
if (!filter.JobFullClasses.IsNullOrLength0())
{
sql.AppendFormat(" AND {0}{1}{2} IN({3})", PfxEscapeChar, GetFieldByProp("JobFullClasse"), SufxEscapeChar, filter.JobFullClasses.ToMergeString(",", "'"));
}
if (!filter.TriggerCrons.IsNullOrLength0())
{
sql.AppendFormat(" AND {0}{1}{2} IN({3})", PfxEscapeChar, GetFieldByProp("TriggerCron"), SufxEscapeChar, filter.TriggerCrons.ToMergeString(",", "'"));
}
if (filter.SuccessedRemove != null)
{
sql.AppendFormat(" AND {0}{1}{2}=@SuccessedRemove", PfxEscapeChar, GetFieldByProp("SuccessedRemove"), SufxEscapeChar);
}
if (!string.IsNullOrWhiteSpace(filter.Keyword))
{
sql.AppendFormat(" AND ({0}{1}{2} LIKE '%{3}%' OR {0}{4}{2} LIKE '%{3}%')",
PfxEscapeChar, nameField, SufxEscapeChar, filter.Keyword.FillSqlValue(), groupField);
}
if (filter.StartCreateTime != null)
{
sql.AppendFormat(" AND {0}{1}{2}>=@StartCreateTime", PfxEscapeChar, GetFieldByProp("CreateTime"), SufxEscapeChar);
}
if (filter.EndCreateTime != null)
{
sql.AppendFormat(" AND {0}{1}{2}>=@EndCreateTime", PfxEscapeChar, GetFieldByProp("CreateTime"), SufxEscapeChar);
}
return(sql.ToString());
}
/// <summary>
/// 查询
/// </summary>
/// <param name="filter">过滤器</param>
/// <param name="connectionId">连接ID</param>
/// <returns>作业任务信息列表</returns>
public IList <JobTaskInfo> Query(JobTaskFilterInfo filter = null, string connectionId = null)
{
var q = QueryForFilter(filter);
if (q == null)
{
return(null);
}
return(q.ToList());
}
/// <summary>
/// 查询分页
/// </summary>
/// <param name="pageIndex">页码，从0开始</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="filter">过滤器</param>
/// <param name="connectionId">连接ID</param>
/// <returns>分页信息</returns>
public PagingInfo <JobTaskInfo> QueryPage(int pageIndex, int pageSize, JobTaskFilterInfo filter = null, string connectionId = null)
{
var whereSql = GetWhereSql(ref filter);
if (string.IsNullOrWhiteSpace(filter.SortName))
{
filter.SortName = "CreateTime";
filter.Sort = SortType.ASC;
}
var orderSql = new StringBuilder($"ORDER BY {PfxEscapeChar}{GetFieldByProp(filter.SortName)}{SufxEscapeChar} {filter.Sort.ToString()}");
if (!"Id".Equals(filter.SortName))
{
orderSql.AppendFormat(",{0}", GetFieldByProp("Id"));
}
var whereSqlStr = whereSql.ToString();
var countSql = $"{BasicCountSql()} {whereSqlStr}";
var selectSql = $"{BasicSelectSql()} {whereSqlStr} {orderSql.ToString()} {GetPartPageSql(pageIndex, pageSize)}";
PagingInfo <JobTaskInfo> result = null;
DbConnectionManager.BrainpowerExecute(connectionId, this, (connId, dbConn) =>
{
var trans = GetDbTransaction(connId, AccessMode.SLAVE);
result = PagingUtil.ExecPage <JobTaskInfo>(pageIndex, pageSize, () =>
{
return(ExecRecordSqlLog <int>(countSql, () =>
{
return dbConn.ExecuteScalar <int>(countSql, filter, transaction: trans);
}, tag: "QueryPage"));
}, () =>
{
return(ExecRecordSqlLog <IList <JobTaskInfo> >(selectSql, () =>
{
return dbConn.Query <JobTaskInfo>(selectSql, filter, transaction: trans).AsList();
}, tag: "QueryPage"));
});
}, AccessMode.SLAVE);
DbToModelFullOtherProps(result.Rows);
return(result);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="filter">过滤器</param>
/// <param name="connectionId">连接ID</param>
/// <returns>作业任务信息列表</returns>
public IList <JobTaskInfo> Query(JobTaskFilterInfo filter = null, string connectionId = null)
{
var sql = $"{BasicSelectSql()} {GetWhereSql(ref filter)}";
if (!string.IsNullOrWhiteSpace(filter.SortName))
{
sql += $" ORDER BY {PfxEscapeChar}{GetFieldByProp(filter.SortName)}{SufxEscapeChar} {filter.Sort.ToString()}";
}
IList <JobTaskInfo> result = null;
DbConnectionManager.BrainpowerExecute(connectionId, this, (connId, dbConn) =>
{
result = ExecRecordSqlLog <IList <JobTaskInfo> >(sql, () =>
{
return(dbConn.Query <JobTaskInfo>(sql, filter, GetDbTransaction(connId, AccessMode.SLAVE)).ToList());
}, tag: "Query");
}, AccessMode.SLAVE);
DbToModelFullOtherProps(result);
return(result);
}