using AipDatabase.API.Infrastructures; using AipDatabase.API.Interfaces; using AipDatabase.API.Models; using Dapper; using Microsoft.Data.SqlClient; using Microsoft.IdentityModel.Tokens; using System.Data; namespace AipDatabase.API.Repositories { public class FileJobLogRepository : IFileJobLogRepository { private readonly ILogger _log; private readonly IDatabaseFactory _databaseFactory; private readonly SqlHelper _sqlHelper; public FileJobLogRepository(ILogger log, IDatabaseFactory databaseFactory) { _log = log; _databaseFactory = databaseFactory; _sqlHelper = new SqlHelper(); } public async Task> FindGatewayLogs(int pagePerCount, int pageSize, int page, string startDate, string endDate, string? searchType, string? searchText) { try { string whereSql = ""; if (!searchType.IsNullOrEmpty() && !searchText.IsNullOrEmpty()) { whereSql = $" AND {searchType} Like '%{searchText}%'"; if ("FileName".Equals(searchType) || "FileOwner".Equals(searchType)) { whereSql += $" OR New{searchType} LIKE '%{searchText}%'"; } } using var connection = _databaseFactory.GetDbConnection(); var parameters1 = new { startDate, endDate, }; string sql = string.Empty; string tmpSql = _sqlHelper.GetSqlFromEmbeddedResource("FileJobLog.FindGatewayLogsTatal"); sql = tmpSql + whereSql; _log.LogInformation(sql); Int64 totalCount = await connection.QueryFirstAsync( sql, parameters1, commandType: CommandType.Text ); _log.LogInformation($"FileJobLog.FindGatewayLogsTatal: {totalCount} EA."); Pagination result = new Pagination(page, pageSize, pagePerCount, (int)totalCount); var parameters2 = new { startDate, endDate, startRow = result.startRow, endRow = result.endRow }; tmpSql = _sqlHelper.GetSqlFromEmbeddedResource("FileJobLog.FindGatewayLogs"); sql = "SELECT A.* FROM (" + tmpSql + whereSql + " ) A WHERE RowNum BETWEEN @startRow AND @endRow"; _log.LogInformation(sql); IEnumerable logResult = await connection.QueryAsync( sql, parameters2, commandType: CommandType.Text ); result.lists = logResult.ToList(); return result; } catch (Exception) { throw; } } } }