AipGateway/AipDatabase.API/Repositories/FileJobLogRepository.cs

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<FileJobLogRepository> _log;
        private readonly IDatabaseFactory _databaseFactory;
        private readonly SqlHelper<FileJobLogRepository> _sqlHelper;
        
        public FileJobLogRepository(ILogger<FileJobLogRepository> log, IDatabaseFactory databaseFactory)
        {
            _log = log;
            _databaseFactory = databaseFactory;
            _sqlHelper = new SqlHelper<FileJobLogRepository>();
        }

        public async Task<Pagination<GatewayLog>> 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<Int64>(
                    sql,
                    parameters1,
                    commandType: CommandType.Text
                    );
                _log.LogInformation($"FileJobLog.FindGatewayLogsTatal: {totalCount} EA.");

                Pagination<GatewayLog> result = new Pagination<GatewayLog>(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<GatewayLog> logResult = await connection.QueryAsync<GatewayLog>(
                                    sql,
                                     parameters2,
                                    commandType: CommandType.Text
                                    );

                result.lists = logResult.ToList();
                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }

    }
}