public async Task <List <DailyStatisticDto> > GetDailyStatisticsAsync(DateTime date) { using (var connection = await _connectionProvider.GetDbConnectionAsync()) { var dtos = await connection.QueryAsync <DailyStatisticDto>("select Id,CreationTime,Date,Platform,Description from DailyStatistics where Date=@Date", new { Date = date }); return(dtos.ToList()); } }
public async Task <PaginationOutput <ResumeDto> > GetListAsync(QueryResumeInput input) { using (var connection = await _provider.GetDbConnectionAsync()) { string columnSql = "a.Id,a.Name,a.JobId,c.Title,a.PhoneNumber," + "a.CreationTime,a.CreatorUserId,CONCAT(d.Surname,d.Name) CreatorUserName ,a.OwnerUserId,CONCAT(e.Surname,e.Name) OwnerUserName," + "b.Id as InvestigationId,a.AuditStatus,a.PlatformName,a.PlatformId," + "a.Enable,a.EnableReason"; var fromSql = new StringBuilder(); fromSql.Append("from resumes a "); fromSql.Append("left join investigations b on a.Id = b.ResumeId "); fromSql.Append("inner join jobs c on a.JobId = c.Id "); fromSql.Append("inner join users d on a.CreatorUserId = d.Id "); fromSql.Append("inner join users e on a.OwnerUserId = e.id "); fromSql.Append(" where 1 = 1"); if (!string.IsNullOrEmpty(input.Keyword)) { fromSql.Append(" and (a.Name like '%@Keyword%' or a.PhoneNumber like '%@Keyword%' or a.PlatformId like '%@Keyword%') "); } if (input.JobId.HasValue) { fromSql.Append(" and a.JobId = @JobId "); } if (input.CreatorUserId.HasValue) { fromSql.Append(" and a.CreatorUserId = @CreatorUserId "); } if (input.OwnerUserId.HasValue && input.OwnerUserId != Guid.Empty) { fromSql.Append(" and a.OwnerUserId = @OwnerUserId "); } if (input.StartTime.HasValue && input.EndTime.HasValue) { fromSql.Append(" and a.CreationTime >= @StartTime and a.CreationTime<= @EndTime "); } if (input.AuditStatus.HasValue) { fromSql.Append(" and a.AuditStatus = @AuditStatus "); } return(await connection.GetPaginationAsync <ResumeDto>(columnSql, fromSql.ToString(), input, input.PageSize, input.PageIndex)); } }