98 lines
4.1 KiB
C#
98 lines
4.1 KiB
C#
namespace TaxBaik.Infrastructure.Repositories;
|
|
|
|
using Dapper;
|
|
using TaxBaik.Domain.Entities;
|
|
using TaxBaik.Domain.Interfaces;
|
|
|
|
public class ClientRepository(IDbConnectionFactory connectionFactory) : BaseRepository(connectionFactory), IClientRepository
|
|
{
|
|
private const string SelectColumns =
|
|
"id, name, company_name, phone, email, service_type, tax_type, status, source, memo, created_at, updated_at";
|
|
|
|
public async Task<(IEnumerable<Client> Items, int Total)> GetPagedAsync(
|
|
int page, int pageSize, string? status = null, string? search = null, CancellationToken ct = default)
|
|
{
|
|
using var conn = Conn();
|
|
var offset = (page - 1) * pageSize;
|
|
|
|
using var reader = await conn.QueryMultipleAsync(
|
|
$@"SELECT {SelectColumns} FROM clients
|
|
WHERE (@Status::text IS NULL OR status = @Status)
|
|
AND (@Search::text IS NULL OR name ILIKE @SearchLike OR phone ILIKE @SearchLike OR company_name ILIKE @SearchLike)
|
|
ORDER BY created_at DESC
|
|
LIMIT @PageSize OFFSET @Offset;
|
|
|
|
SELECT COUNT(*) FROM clients
|
|
WHERE (@Status::text IS NULL OR status = @Status)
|
|
AND (@Search::text IS NULL OR name ILIKE @SearchLike OR phone ILIKE @SearchLike OR company_name ILIKE @SearchLike);",
|
|
new { Status = status, Search = search, SearchLike = string.IsNullOrEmpty(search) ? null : $"%{search}%", PageSize = pageSize, Offset = offset });
|
|
|
|
var items = (await reader.ReadAsync<Client>()).ToList();
|
|
var total = await reader.ReadFirstAsync<int>();
|
|
return (items, total);
|
|
}
|
|
|
|
public async Task<Client?> GetByIdAsync(int id, CancellationToken ct = default)
|
|
{
|
|
using var conn = Conn();
|
|
return await conn.QueryFirstOrDefaultAsync<Client>(
|
|
$"SELECT {SelectColumns} FROM clients WHERE id = @Id",
|
|
new { Id = id });
|
|
}
|
|
|
|
public async Task<Client?> GetByEmailAsync(string email, CancellationToken ct = default)
|
|
{
|
|
using var conn = Conn();
|
|
return await conn.QueryFirstOrDefaultAsync<Client>(
|
|
$"SELECT {SelectColumns} FROM clients WHERE email = @Email",
|
|
new { Email = email });
|
|
}
|
|
|
|
public async Task<Client?> GetByPhoneAsync(string phone, CancellationToken ct = default)
|
|
{
|
|
using var conn = Conn();
|
|
return await conn.QueryFirstOrDefaultAsync<Client>(
|
|
$"SELECT {SelectColumns} FROM clients WHERE phone = @Phone",
|
|
new { Phone = phone });
|
|
}
|
|
|
|
public async Task<int> CountByCreatedAtRangeAsync(DateTime startDateUtc, DateTime endDateUtc, CancellationToken ct = default)
|
|
{
|
|
using var conn = Conn();
|
|
return await conn.ExecuteScalarAsync<int>(
|
|
@"SELECT COUNT(*)
|
|
FROM clients
|
|
WHERE created_at >= @StartDateUtc
|
|
AND created_at <= @EndDateUtc",
|
|
new { StartDateUtc = startDateUtc, EndDateUtc = endDateUtc });
|
|
}
|
|
|
|
public async Task<int> CreateAsync(Client client, CancellationToken ct = default)
|
|
{
|
|
using var conn = Conn();
|
|
return await conn.QueryFirstAsync<int>(
|
|
@"INSERT INTO clients (name, company_name, phone, email, service_type, tax_type, status, source, memo, created_at, updated_at)
|
|
VALUES (@Name, @CompanyName, @Phone, @Email, @ServiceType, @TaxType, @Status, @Source, @Memo, NOW(), NOW())
|
|
RETURNING id",
|
|
client);
|
|
}
|
|
|
|
public async Task UpdateAsync(Client client, CancellationToken ct = default)
|
|
{
|
|
using var conn = Conn();
|
|
await conn.ExecuteAsync(
|
|
@"UPDATE clients
|
|
SET name = @Name, company_name = @CompanyName, phone = @Phone, email = @Email,
|
|
service_type = @ServiceType, tax_type = @TaxType, status = @Status,
|
|
source = @Source, memo = @Memo, updated_at = NOW()
|
|
WHERE id = @Id",
|
|
client);
|
|
}
|
|
|
|
public async Task DeleteAsync(int id, CancellationToken ct = default)
|
|
{
|
|
using var conn = Conn();
|
|
await conn.ExecuteAsync("DELETE FROM clients WHERE id = @Id", new { Id = id });
|
|
}
|
|
}
|