public async Task <BaseResponse> CronJobList(CronJobListRequest request)
{
var filterBuilder = Builders <CronJob> .Filter;
var filter = filterBuilder.Empty;
if (!string.IsNullOrEmpty(request.Name))
{
filter = filter & filterBuilder.Eq("Name", request.Name);
}
if (request.JobState != 0)
{
filter = filter & filterBuilder.Eq("JobState", request.JobState);
}
if (request.CreateTime != null && request.CreateTime.Count == 2)
{
filter = filter & filterBuilder.Gte("CreateTime", request.CreateTime.First()) & filterBuilder.Lte("CreateTime", request.CreateTime.Last());
}
var sort = request.OrderBy == 0 ? Builders <CronJob> .Sort.Ascending(request.OrderByField) : Builders <CronJob> .Sort.Descending(request.OrderByField);
var list = await _cronJobRepository.GetListAsync(filter, request.Skip, request.PageSize, sort);
//最多数到300就不往下继续数了。
long count = await _cronJobRepository.Context.CountDocumentsAsync(filter, new CountOptions { Limit = 300 });
BasePageResponse <CronJob> res = new BasePageResponse <CronJob>();
res.DataList = list;
res.TotalCount = count;
return(BaseResponse <List <CronJob> > .Ok(list));
}
public async Task <BasePageResponse <CronJob> > CronJobList(CronJobListRequest request)
{
//MySqlDbType同时存在于“MySql.Data, Version=8.0.19.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d”和“MySqlConnector, Version=0.61.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92”中
//List<MySqlParameter> ps = new List<MySqlParameter>();
//选取完所有数据，再对数据进行排序。
StringBuilder whereSql = new StringBuilder(" 1=1 ");
if (!string.IsNullOrEmpty(request.Name))
{
whereSql.Append($" and cron_job.Name='{request.Name}'");
//ps.Add(new MySqlParameter
//{
// Value = request.Name,
// ParameterName = "Name",
// MySqlDbType = MySqlDbType.VarChar
//});
}
if (request.JobState != 0)
{
whereSql.Append($" and cron_job.JobState={request.JobState}");
//ps.Add(new MySqlParameter
//{
// Value = request.JobState,
// ParameterName = "JobState",
// MySqlDbType = MySqlDbType.Int32
//});
}
StringBuilder orderSql = new StringBuilder(" ");
orderSql.Append(request.OrderByField);
orderSql.Append(request.OrderBy == 0 ?" desc ":" asc ");
#region 开始和结束时间筛选
if (request.CreateTime != null && request.CreateTime.Count == 2)
{
whereSql.Append($" AND cron_job.CreateTime BETWEEN '{request.CreateTime.First()}' AND '{request.CreateTime.Last()}' ");
}
if (request.UpdateTime != null && request.UpdateTime.Count == 2)
{
whereSql.Append($" AND cron_job.UpdateTime BETWEEN '{request.UpdateTime.First()}' AND '{request.UpdateTime.Last()}' ");
}
#endregion
#region 查总数
string sqlcount = $@"
SELECT
count(1) as Count
FROM
cron_job
WHERE {whereSql} ";
_logger.LogInformation(sqlcount);
var count = await Context.SqlQueryFirstAsync <int>(sqlcount);
#endregion
StringBuilder limit = new StringBuilder();
limit.Append(request.LimitSql());
var sqlList = string.Format(@"SELECT * FROM cron_job WHERE {0} ORDER BY {1} ", whereSql, orderSql);
string sql = string.Concat(sqlList, limit);
_logger.LogInformation(sql);
var data = await Context.SqlQueryAsync <CronJob>(sql);
return(new BasePageResponse <CronJob>
{
TotalCount = count,
DataList = data
});
}
public async Task <BaseResponse> CronJobList([FromBody] CronJobListRequest request)
{
return(await _cronJobService.CronJobList(request));
}
public async Task <BaseResponse> CronJobList(CronJobListRequest request)
{
var res = await _cronJobRepository.CronJobList(request);
return(BaseResponse <BasePageResponse <CronJob> > .Ok(res));
}
