f9-web-ai-test/backend/F9WebAiTest.Infrastructure/FreeSqls/Repositories/SYS/Employees/EmployeeRepositories.cs

using FreeSql;
using F9WebAiTest.Application.Common;
using F9WebAiTest.Application.Interfaces.SnowflakeID;
using F9WebAiTest.Application.Dtos.SYS.Employees;
using F9WebAiTest.Application.Interfaces.Repositories.SYS.Employees;
using F9WebAiTest.Domain.Entities.SYS.Departments;
using F9WebAiTest.Domain.Entities.SYS.Employees;
using F9WebAiTest.Domain.Entities.SYS.WorkTypes;
using F9WebAiTest.Domain.Enums.SYS.Employees;
using F9WebAiTest.Domain.Interfaces.SYS.Employees;

namespace F9WebAiTest.Infrastructure.FreeSqls.Repositories.SYS.Employees;

internal class EmployeeWriteRepository : F9RepositoryBase, IEmployeeWriteRepository
{
    public EmployeeWriteRepository(IFreeSql fsql) : base(fsql) { }

    public async Task<Employee> GetByIdAsync(long id, CancellationToken cancellationToken)
        => await Orm.Select<Employee>().Where(x => x.Id == id && x.DeletedAt == null).FirstAsync(cancellationToken);

    public async Task<bool> AddAsync(Employee entity)
    {
        entity.CreatedAt = DateTime.Now;
        return await Orm.Insert(entity).ExecuteAffrowsAsync() > 0;
    }

    public async Task<bool> UpdateAsync(Employee entity)
    {
        entity.UpdatedAt = DateTime.Now;
        return await Orm.Update<Employee>().SetSource(entity).ExecuteAffrowsAsync() > 0;
    }

    public async Task<bool> SoftDeleteAsync(long id)
        => await Orm.Update<Employee>()
            .Set(x => x.IsDeleted, true)
            .Set(x => x.DeletedAt, DateTime.Now)
            .Where(x => x.Id == id)
            .ExecuteAffrowsAsync() > 0;
}

internal class EmployeeReadRepository : F9RepositoryBase, IEmployeeReadRepository
{
    private readonly ISnowflakeIdGenerator _snowflakeIdGenerator;

    public EmployeeReadRepository(IFreeSql fsql, ISnowflakeIdGenerator snowflakeIdGenerator) : base(fsql)
        => _snowflakeIdGenerator = snowflakeIdGenerator;

    public async Task<PageResult<EmployeeReadDto_View>> GetPagedAsync(long depId, long worktypeId,
        Employee_WorkStatus_Enum? workStatus, Prod_Banci_Enum? banci, string keyword, int pageIndex, int pageSize)
    {
        if (pageIndex <= 0) pageIndex = 1;
        if (pageSize <= 0) pageSize = 20;

        var query = Orm.Select<Employee>().Where(x => x.DeletedAt == null);

        if (depId > 0)
        {
            var depIds = await GetDepIdsWithChildrenAsync(depId);
            query = query.Where(x => depIds.Contains(x.DepId));
        }
        if (worktypeId > 0) query = query.Where(x => x.WorktypeId == worktypeId);
        if (workStatus.HasValue) query = query.Where(x => x.WorkStatus == workStatus.Value);
        if (banci.HasValue) query = query.Where(x => x.Banci == banci.Value);

        if (!string.IsNullOrWhiteSpace(keyword))
        {
            var kw = keyword.Trim();
            query = query.Where(x => x.Name.Contains(kw) || x.Code.Contains(kw) || x.Phone.Contains(kw)
                || x.IdCard.Contains(kw) || x.Gangwei.Contains(kw) || x.NamePinyin.Contains(kw));
        }

        var total = await query.CountAsync();
        var items = await query
            .OrderBy(x => x.DepId)
            .OrderBy(x => x.Sort)
            .OrderBy(x => x.Name)
            .Page(pageIndex, pageSize)
            .ToListAsync(x => new EmployeeReadDto_View(x.Id, x.DepId, "", x.WorktypeId, "", x.Banci, "", x.Gangwei,
                x.WorkStatus, x.IsZhuanzheng, x.Code, x.Name, x.Gender, 0, x.IdCard, x.Phone, x.BirthDate, x.RuzhiDate,
                x.ZhuanzhengDate, x.LizhiDate, x.LizhiYuanyin, x.WorkGroup, x.Sort, x.BankcardCode, x.HetongType,
                x.HetongStartDate, x.HetongEndDate, x.TuixiuDate, x.ShebaoDate, x.Minzu, x.HunyinStatus, x.Xueli, x.Huji,
                x.Address, x.JinjiLianxiren, x.JinjiLianxirenPhone, x.BiyeYuanxiao, x.Zhuanye, x.NamePinyin, x.Memo));

        if (items.Count == 0)
        {
            return new PageResult<EmployeeReadDto_View> { Items = items, Total = total, PageIndex = pageIndex, PageSize = pageSize };
        }

        var depNameMap = await GetDepNameMapAsync();
        var worktypeNameMap = await GetWorktypeNameMapAsync();

        var list = items
            .Select(x => x with
            {
                DepName = depNameMap.TryGetValue(x.DepId, out var dn) ? dn : string.Empty,
                WorktypeName = worktypeNameMap.TryGetValue(x.WorktypeId, out var wn) ? wn : string.Empty,
                BanciName = x.Banci.HasValue ? x.Banci.Value.ToString() : string.Empty,
                Nianling = CalcAge(x.BirthDate, x.IdCard)
            })
            .ToList();

        return new PageResult<EmployeeReadDto_View> { Items = list, Total = total, PageIndex = pageIndex, PageSize = pageSize };
    }

    public async Task<EmployeeReadDto_Edit> GetByIdForEditAsync(long id)
        => await Orm.Select<Employee>()
            .Where(x => x.Id == id && x.DeletedAt == null)
            .FirstAsync(x => new EmployeeReadDto_Edit(x.Id, x.DepId, x.WorktypeId, x.WorkStatus, x.Banci, x.Code,
                x.Name, x.Gangwei, x.Phone, x.IdCard, x.BirthDate, x.Address, x.Gender, x.Minzu, x.HunyinStatus, x.Huji,
                x.RuzhiDate, x.LizhiDate, x.ZhuanzhengDate, x.IsZhuanzheng, x.JinjiLianxiren, x.JinjiLianxirenPhone,
                x.HetongType, x.HetongStartDate, x.HetongEndDate, x.TuixiuDate, x.ShebaoDate, x.ShebaoDesc,
                x.BankcardCode, x.BiyeYuanxiao, x.Zhuanye, x.Xueli, x.WorkGroup, x.Sort, x.NamePinyin, x.Memo,
                x.LizhiYuanyin));

    public async Task<List<EmployeeChangeRecordDto>> GetChangeRecordsAsync(long employeeId)
    {
        var rows = await Orm.Select<EmployeeChangeRecord>()
            .Where(x => x.EmployeeId == employeeId)
            .OrderByDescending(x => x.Date)
            .ToListAsync(x => new EmployeeChangeRecordDto(x.Id, x.EmployeeId, x.Date, x.ChangeStatus, "",
                x.ChangeShuoming, x.ChangeYuanyin));

        return rows.Select(x => x with { ChangeStatusName = x.ChangeStatus.ToString() }).ToList();
    }

    public async Task<List<EmployeeTiaogangRecordDto>> GetTiaogangRecordsAsync(long employeeId)
    {
        var rows = await Orm.Select<EmployeeTiaogangRecord>()
            .Where(x => x.EmployeeId == employeeId)
            .OrderByDescending(x => x.TiaogangDate)
            .ToListAsync(x => new EmployeeTiaogangRecordDto(x.Id, x.EmployeeId, x.OldDepId, "", x.OldWorktypeId, "",
                x.NewDepId, "", x.NewWorktypeId, "", x.TiaogangDate));

        if (rows.Count == 0) return rows;

        var depNameMap = await GetDepNameMapAsync();
        var worktypeNameMap = await GetWorktypeNameMapAsync();

        return rows
            .Select(x => x with
            {
                OldDepName = depNameMap.TryGetValue(x.OldDepId, out var odn) ? odn : string.Empty,
                NewDepName = depNameMap.TryGetValue(x.NewDepId, out var ndn) ? ndn : string.Empty,
                OldWorktypeName = worktypeNameMap.TryGetValue(x.OldWorktypeId, out var own) ? own : string.Empty,
                NewWorktypeName = worktypeNameMap.TryGetValue(x.NewWorktypeId, out var nwn) ? nwn : string.Empty
            })
            .ToList();
    }

    public async Task<bool> AddChangeRecordAsync(EmployeeChangeRecordDto record)
    {
        var entity = new EmployeeChangeRecord(_snowflakeIdGenerator.CreateId(), record.EmployeeId,
            record.Date, record.ChangeStatus, record.ChangeShuoming ?? string.Empty, record.ChangeYuanyin ?? string.Empty)
        {
            CreatedAt = DateTime.Now
        };
        return await Orm.Insert(entity).ExecuteAffrowsAsync() > 0;
    }

    public async Task<bool> AddTiaogangRecordAsync(long employeeId, long oldDepId, long oldWorktypeId, long newDepId,
        long newWorktypeId, DateTime date)
    {
        var entity = new EmployeeTiaogangRecord(_snowflakeIdGenerator.CreateId(), employeeId, oldDepId,
            oldWorktypeId, newDepId, newWorktypeId, date)
        {
            CreatedAt = DateTime.Now
        };
        return await Orm.Insert(entity).ExecuteAffrowsAsync() > 0;
    }

    public async Task<string> GetDepNameAsync(long depId)
    {
        if (depId <= 0) return string.Empty;
        var dep = await Orm.Select<Department>().Where(x => x.Id == depId).FirstAsync();
        return dep?.Name ?? string.Empty;
    }

    public async Task<string> GetWorktypeNameAsync(long worktypeId)
    {
        if (worktypeId <= 0) return string.Empty;
        var worktype = await Orm.Select<WorkType>().Where(x => x.Id == worktypeId).FirstAsync();
        return worktype?.WorkName ?? string.Empty;
    }

    /// <summary>取部门及其全部下级部门 Id</summary>
    private async Task<List<long>> GetDepIdsWithChildrenAsync(long rootDepId)
    {
        var all = await Orm.Select<Department>().Where(x => x.DeletedAt == null).ToListAsync();
        var result = new HashSet<long> { rootDepId };

        var changed = true;
        while (changed)
        {
            changed = false;
            foreach (var dep in all)
            {
                if (dep.ParentId != 0 && result.Contains(dep.ParentId) && result.Add(dep.Id)) changed = true;
            }
        }

        return result.ToList();
    }

    private async Task<Dictionary<long, string>> GetDepNameMapAsync()
        => (await Orm.Select<Department>().Where(x => x.DeletedAt == null).ToListAsync())
            .ToDictionary(x => x.Id, x => x.Name);

    private async Task<Dictionary<long, string>> GetWorktypeNameMapAsync()
        => (await Orm.Select<WorkType>().Where(x => x.DeletedAt == null).ToListAsync())
            .ToDictionary(x => x.Id, x => x.WorkName);

    /// <summary>年龄：优先出生日期，其次从身份证号推算</summary>
    private static int CalcAge(DateTime? birthDate, string idCard)
    {
        var birth = birthDate;

        if (birth == null && !string.IsNullOrWhiteSpace(idCard))
        {
            var card = idCard.Trim();
            string? datePart = card.Length switch
            {
                18 => card.Substring(6, 8),
                15 => "19" + card.Substring(6, 6),
                _ => null
            };
            if (datePart != null
                && DateTime.TryParseExact(datePart, "yyyyMMdd", null, System.Globalization.DateTimeStyles.None, out var parsed))
            {
                birth = parsed;
            }
        }

        if (birth == null) return 0;

        var today = DateTime.Today;
        var age = today.Year - birth.Value.Year;
        if (birth.Value.Date > today.AddYears(-age)) age--;
        return age < 0 ? 0 : age;
    }
}