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