Backend-Api/CompanyManagment.EFCore/Repository/EmployeeBankInformationRepository.cs

using _0_Framework.InfraStructure;
using AccountMangement.Infrastructure.EFCore;
using Company.Domain.EmployeeBankInformationAgg;
using CompanyManagment.App.Contracts.EmployeeBankInformation;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;

namespace CompanyManagment.EFCore.Repository;

public class EmployeeBankInformationRepository : RepositoryBase<long, EmployeeBankInformation>, IEmployeeBankInformationRepository
{

    private readonly CompanyContext _companyContext;
    private readonly AccountContext _accountContext;
    public EmployeeBankInformationRepository(CompanyContext context, CompanyContext companyContext, AccountContext accountContext) : base(context)
    {
        _companyContext = companyContext;
        _accountContext = accountContext;
    }

    public List<GroupedEmployeeBankInformationViewModel> Search(long workshopId, EmployeeBankInformationSearchModel searchParams)
    {
        var bankInfoQuery = _companyContext.EmployeeBankInformationSet.Where(x => x.WorkshopId == workshopId)
            .Include(x => x.Employee).Include(x => x.Bank)
            .Select(x => new
            {
                x.Bank,
                x.BankId,
                FullName = (x.Employee.FName + " " + x.Employee.LName),
                x.EmployeeId,
                x.WorkshopId,
            })


            .AsQueryable();

        if (searchParams.BankId > 0)
            bankInfoQuery = bankInfoQuery.Where(x => x.BankId == searchParams.BankId);

        if (searchParams.EmployeeId > 0)
            bankInfoQuery = bankInfoQuery.Where(x => x.EmployeeId == searchParams.EmployeeId);

        var personnelCodes =
            _companyContext.PersonnelCodeSet.Where(x => bankInfoQuery.Any(y => y.EmployeeId == x.EmployeeId)).ToList();

        var bankInfoList = bankInfoQuery.ToList();


        var groupedBanks = bankInfoList.GroupBy(x =>  x.EmployeeId)
            .Select(x => new { EmployeeId=x.Key,Banks= x.Select(y=>y.Bank).ToList() }).ToList();

        //Get bank logos from account context
        var mediaIds = groupedBanks.SelectMany(x => x.Banks.Select(y=>y.BankLogoMediaId)).ToList();

        var banksLogos = _accountContext.Medias.Where(y => mediaIds.Contains(y.id))
            .Select(media => new { media.Path, MediaId = media.id }).ToList();


        return bankInfoList.GroupBy(x => x.EmployeeId).Select(x =>
        {
            var banks = groupedBanks.First(y => y.EmployeeId == x.Key).Banks;
            return new GroupedEmployeeBankInformationViewModel()
            {
                BankPicturesList = banksLogos.Where(y => banks.Any(z => y.MediaId == z.BankLogoMediaId))
                    .Select(y => y.Path).ToList(),
                EmployeeId = x.Key,
                WorkshopId = workshopId,
                EmployeeName = x.FirstOrDefault()?.FullName ?? "",
                TotalBankAccountsCount = x.Count(),
                PersonnelCode =
                    personnelCodes.FirstOrDefault(y => y.EmployeeId == x.Key)?.PersonnelCode.ToString() ?? "",
                BankNamesList = banks.Select(y => y.BankName).ToList()
            };
        }).ToList();
    }


    public void RemoveByEmployeeId(IEnumerable<EmployeeBankInformation> entities)
    {
        _companyContext.EmployeeBankInformationSet.RemoveRange(entities);
    }

    public GroupedEmployeeBankInformationViewModel GetByEmployeeId(long workshopId, long employeeId)
    {
        //todo: optimize
        var entities = _companyContext.EmployeeBankInformationSet
            .Include(x => x.Bank).Include(x => x.Employee)
            .Where(x => x.EmployeeId == employeeId && x.WorkshopId == workshopId);
        var personnelCode =
            _companyContext.PersonnelCodeSet.FirstOrDefault(x => x.EmployeeId == employeeId && x.WorkshopId == workshopId)?
                .PersonnelCode.ToString() ?? "";
        var bankInfoList = entities.ToList();
        var groupedBanks = bankInfoList.GroupBy(x => x.Bank).Select(x => x.Key).ToList();

        var mediaIds = groupedBanks.Select(x => x.BankLogoMediaId).ToList();

        var banksLogos = _accountContext.Medias.Where(y => mediaIds.Contains(y.id))
            .Select(media => new { media.Path, MediaId = media.id });

        var banksLogosList = banksLogos.ToList();

        var result = new GroupedEmployeeBankInformationViewModel()
        {
            BankPicturesList = banksLogosList.Select(y => y.Path).ToList(),
            EmployeeId = employeeId,
            WorkshopId = workshopId,
            EmployeeName = entities.FirstOrDefault()?.Employee.FullName ?? "",
            TotalBankAccountsCount = entities.Count(),
            PersonnelCode = personnelCode,
            BankInformation = entities.Select(y => new EmployeeBankInformationViewModel()
            {
                Id = y.id,
                BankId = y.BankId,
                BankLogoMediaId=y.Bank.BankLogoMediaId,
                BankAccountNumber = y.BankAccountNumber,
                BankName = y.Bank.BankName,
                CardNumber = y.CardNumber,
                ShebaNumber = y.ShebaNumber,
                IsDefault = y.IsDefault
            }).OrderByDescending(y=>y.IsDefault).ToList()
        };
        result.BankInformation.ForEach(x =>
        {
            x.BankLogoPath = banksLogos.FirstOrDefault(z => z.MediaId == x.BankLogoMediaId)?.Path ?? "";
        });

        return result;
    }

    public List<EmployeeBankInformation> GetRangeByEmployeeId(long workshopId, long employeeId)
    {
        return _companyContext.EmployeeBankInformationSet.Where(x =>
            x.WorkshopId == workshopId && x.EmployeeId == employeeId).ToList();
    }



    public void RemoveRange(List<EmployeeBankInformation> entities)
    {
        _companyContext.EmployeeBankInformationSet.RemoveRange(entities);
    }

    public EmployeeBankInformationViewModel GetDetails(long id)
    {
        var entity = _companyContext.EmployeeBankInformationSet.Include(x => x.Bank)
            .Include(x => x.Employee).FirstOrDefault(x => x.id == id);
        if (entity == null)
            return new();
        var mediaId = entity.Bank.BankLogoMediaId;
        var mediaPath = _accountContext.Medias.FirstOrDefault(x => x.id == mediaId)?.Path ?? "";

        return new EmployeeBankInformationViewModel()
        {
            EmployeeId = entity.EmployeeId,
            BankAccountNumber = entity.BankAccountNumber,
            BankLogoPath = mediaPath,
            BankName = entity.Bank.BankName,
            CardNumber = entity.CardNumber,
            EmployeeName = entity.Employee.FullName,
            Id = entity.id,
            ShebaNumber = entity.ShebaNumber,
            IsDefault = entity.IsDefault
        };

    }

    public List<GroupedEmployeeBankInformationViewModel> GetAllByWorkshopId(long workshopId)
    {

        //todo: optimize
        var bankInfoQuery = _companyContext.EmployeeBankInformationSet.Where(x => x.WorkshopId == workshopId)
            .Include(x => x.Employee).Include(x => x.Bank).AsQueryable();


        var personnelCodes =
            _companyContext.PersonnelCodeSet.Where(x => bankInfoQuery.Any(y => y.EmployeeId == x.EmployeeId));

        var bankInfoList = bankInfoQuery.ToList();


        var groupedBanks = bankInfoList.GroupBy(x => x.EmployeeId).Select(x =>new{EmployeeId=x.Key,Banks=x.Select(y=>y.Bank).ToList()}).ToList();
        //Get bank logos from account context
        var mediaIds = groupedBanks.SelectMany(x => x.Banks.Select(y=>y.BankLogoMediaId)).ToList();
        var banksLogos = _accountContext.Medias.Where(y => mediaIds.Contains(y.id))
            .Select(media => new { media.Path, MediaId = media.id }).ToList();


        return bankInfoList.GroupBy(x => x.Employee).Select(x =>
        {
            var banks = groupedBanks.First(y => y.EmployeeId == x.Key.id).Banks;
            return new GroupedEmployeeBankInformationViewModel()
            {
                BankPicturesList = banksLogos.Where(y => banks.Any(z => y.MediaId == z.BankLogoMediaId))
                    .Select(y => y.Path).ToList(),
                BankNamesList = banks.Select(y => y.BankName).ToList(),
                EmployeeId = x.Key.id,
                WorkshopId = workshopId,
                EmployeeName = x.Key.FullName,
                TotalBankAccountsCount = x.Count(),
                PersonnelCode = personnelCodes.FirstOrDefault(y => y.EmployeeId == x.Key.id).PersonnelCode
                    .ToString(),
                BankInformation = x.OrderByDescending(y => y.IsDefault).Select(y =>
                    new EmployeeBankInformationViewModel()
                    {
                        Id = y.id,
                        BankAccountNumber = y.BankAccountNumber,
                        BankName = y.Bank.BankName,
                        CardNumber = y.CardNumber,
                        ShebaNumber = y.ShebaNumber,
                        IsDefault = y.IsDefault,
                    }).ToList()
            };
        }).ToList();


    }

    public List<EmployeeBankInformationViewModelForExcel> SearchForExcel(long workshopId, EmployeeBankInformationSearchModel searchParams)
    {
        var bankInfoQuery = _companyContext.EmployeeBankInformationSet.Where(x => x.WorkshopId == workshopId)
            .Include(x => x.Employee).Include(x => x.Bank)
            .Select(x => new
            {
                x.Bank.BankName,
                x.BankId,
                FullName = (x.Employee.FName + " " + x.Employee.LName),
                x.EmployeeId,
                x.WorkshopId,
                Id = x.id,
                x.CardNumber,
                x.ShebaNumber,
                x.BankAccountNumber,
                x.IsDefault
            });

        if (searchParams.BankId > 0)
            bankInfoQuery = bankInfoQuery.Where(x => x.BankId == searchParams.BankId);

        if (searchParams.EmployeeId > 0)
            bankInfoQuery = bankInfoQuery.Where(x => x.EmployeeId == searchParams.EmployeeId);

        if (searchParams.EmployeeBankInfoIds.Any())
            bankInfoQuery = bankInfoQuery.Where(x => searchParams.EmployeeBankInfoIds.Contains(x.Id));

        var bankInfoList = bankInfoQuery.ToList();




        return bankInfoList.GroupBy(x => x.EmployeeId).Select(x =>
        {
            return new EmployeeBankInformationViewModelForExcel()
            {
                EmployeeName = x.FirstOrDefault()?.FullName ?? "",
                BankInformationList = x.Select(y => new BankInformationDtoForExcel()
                {
                    BankName = y.BankName,
                    CardNumber = y.CardNumber,
                    BankAccountNumber = y.BankAccountNumber,
                    ShebaNumber = y.ShebaNumber,
                    IsDefault = y.IsDefault
                }).ToList()
            };
        }).ToList();
    }
}