255 lines
18 KiB
C#
255 lines
18 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data.SqlClient;
|
|
using System.Linq;
|
|
using System.Threading.Tasks;
|
|
using LCU.Shared.Collections.RepoApiLib.Extensions;
|
|
using LCU.Shared.Collections.RepoApiLib.Models;
|
|
using LCU.Shared.Collections.RepoApiLib.Models.Reporting;
|
|
using LCU.Shared.Collections.RepoApiLib.Persistence;
|
|
using Microsoft.EntityFrameworkCore;
|
|
|
|
namespace LCU.Shared.Collections.RepoApiLib.Repositories
|
|
{
|
|
public class ReportingRepository : BaseRepository, IReportingRepository
|
|
{
|
|
public ReportingRepository(AppDbContext context) : base(context)
|
|
{
|
|
}
|
|
|
|
// Maybe return a condensed object specifically meant for the frontend?
|
|
public async Task<IReadOnlyCollection<RepoAccount>> GetAllAccountsInO4R()
|
|
{
|
|
return await _context.RepoAccounts.Where(repoAccount => repoAccount.Status == "O4R")
|
|
.IncludeAll()
|
|
.ToListAsync();
|
|
}
|
|
|
|
public async Task<IReadOnlyCollection<LandmarkReportAccountEntry>> GetActiveAccounts()
|
|
{
|
|
return await _context.ActiveAccountEntries.FromSql(
|
|
@"select repos.repoId as RepoId, repos.DaysDelinquent as DaysDelinquent, repos.LastPaymentDate as LastPaymentDate, repos.LstPmtAmt as LastPaymentAmount,
|
|
Repos.Balance as Balance, Repos.AccountTypeCode as AccountTypeCode, Repos.Bankruptcy as Bankruptcy, Repos.DateClosed as DateClosed,
|
|
Repos.DelinquentAmount as DelinquentAmount, Repos.Collector as Collector, Borrowers.IndividualName, repos.AccountNo as AccountNo,
|
|
Collaterals.Year as Year, Collaterals.Make as Make, Collaterals.Model as Model, RepoCompany.Name as RepoCompany,
|
|
RepoCompanySelections.DateRepoed as DateRepoed, RepoCompanySelections.RepoType,
|
|
RepoCompanySelections.RepoCompanyAssignmentDate as DateAssigned,
|
|
Repos.Status as Status, DATEDIFF(day, RepoStatusChanges.ChangedDate, CURRENT_TIMESTAMP) as DaysInStatus, IMSAutoFileData.SoldDate as SoldDate,
|
|
IMSAutoFileData.DateEnteredAutoIMS as DateEnteredAutoIMS, IMSAutoFileData.FloorPriceAccepted as FloorPrice, IMSAutoFileData.VehicleGrade as Grade,
|
|
IMSAutoFileData.ScheduledSaleDate as ScheduledSaleDate, IMSAutoFileData.TotalCharges as TotalCharges,
|
|
IMSAutoFileData.GrossPercentFloor as GrossPercentFloor, IMSAutoFileData.NetPercentFloor as NetPercentFloor,
|
|
IMSAutoFileData.SalePrice as GrossSalePrice,
|
|
Auctions.Name as AuctionHouse, AuctionAssignments.DateSubmitted as AuctionDate
|
|
From repos
|
|
left join RepoStatusChanges on RepoStatusChanges.RepoId = Repos.RepoId
|
|
and RepoStatusChanges.ChangedDate = (select max(ChangedDate) from RepoStatusChanges where RepoId = repos.RepoId)
|
|
join Borrowers on Borrowers.RepoId = Repos.RepoId and Borrowers.OwnershipType = 6
|
|
join Collaterals on Collaterals.RepoId = Repos.RepoId
|
|
join RepoCompanySelections on Collaterals.CollateralId = RepoCompanySelections.CollateralId and RepoCompanySelections.DateTimeCreated = (select max(DateTimeCreated) from RepoCompanySelections where CollateralId = Collaterals.CollateralId)
|
|
left join RepoCompany on RepoCompanySelections.RepoCompanyId = RepoCompany.ID
|
|
left join AuctionAssignmentsToCollaterals on Collaterals.CollateralId = AuctionAssignmentsToCollaterals.CollateralId
|
|
left join AuctionAssignments on AuctionAssignmentsToCollaterals.AuctionAssignmentId = AuctionAssignments.ID
|
|
left join IMSAutoFileData on Repos.RepoId = IMSAutoFileData.RepoId
|
|
left join Auctions on Auctions.AuctionID = AuctionAssignments.AuctionId
|
|
where repos.status != 'REDEEMED' and repos.dateclosed is null
|
|
order by DateAssigned desc;
|
|
").ToListAsync();
|
|
}
|
|
|
|
|
|
public async Task<IReadOnlyCollection<LandmarkReportAccountEntry>> GetClosedAccounts(DateTime lowDateTime, DateTime highDateTime)
|
|
{
|
|
var lowDate = new SqlParameter("lowDate", lowDateTime);
|
|
var highDate = new SqlParameter("highDate", highDateTime);
|
|
return await _context.ActiveAccountEntries.FromSql(
|
|
@"select repos.repoId as RepoId, repos.DaysDelinquent as DaysDelinquent, repos.LastPaymentDate as LastPaymentDate, repos.LstPmtAmt as LastPaymentAmount,
|
|
Repos.Balance as Balance, Repos.AccountTypeCode as AccountTypeCode, Repos.Bankruptcy as Bankruptcy, Repos.DateClosed as DateClosed,
|
|
Repos.DelinquentAmount as DelinquentAmount, Repos.Collector as Collector, Borrowers.IndividualName, repos.AccountNo as AccountNo,
|
|
Collaterals.Year as Year, Collaterals.Make as Make, Collaterals.Model as Model, RepoCompany.Name as RepoCompany,
|
|
RepoCompanySelections.DateRepoed as DateRepoed, RepoCompanySelections.RepoType,
|
|
RepoCompanySelections.RepoCompanyAssignmentDate as DateAssigned,
|
|
Repos.Status as Status, DATEDIFF(day, RepoStatusChanges.ChangedDate, CURRENT_TIMESTAMP) as DaysInStatus, IMSAutoFileData.SoldDate as SoldDate,
|
|
IMSAutoFileData.DateEnteredAutoIMS as DateEnteredAutoIMS, IMSAutoFileData.FloorPriceAccepted as FloorPrice, IMSAutoFileData.VehicleGrade as Grade,
|
|
IMSAutoFileData.ScheduledSaleDate as ScheduledSaleDate, IMSAutoFileData.TotalCharges as TotalCharges,
|
|
IMSAutoFileData.GrossPercentFloor as GrossPercentFloor, IMSAutoFileData.NetPercentFloor as NetPercentFloor,
|
|
IMSAutoFileData.SalePrice as GrossSalePrice,
|
|
Auctions.Name as AuctionHouse, AuctionAssignments.DateSubmitted as AuctionDate
|
|
From repos
|
|
left join RepoStatusChanges on RepoStatusChanges.RepoId = Repos.RepoId
|
|
and RepoStatusChanges.ChangedDate = (select max(ChangedDate) from RepoStatusChanges where RepoId = repos.RepoId)
|
|
join Borrowers on Borrowers.RepoId = Repos.RepoId and Borrowers.OwnershipType = 6
|
|
join Collaterals on Collaterals.RepoId = Repos.RepoId
|
|
join RepoCompanySelections on Collaterals.CollateralId = RepoCompanySelections.CollateralId and RepoCompanySelections.DateTimeCreated = (select max(DateTimeCreated) from RepoCompanySelections where CollateralId = Collaterals.CollateralId)
|
|
left join RepoCompany on RepoCompanySelections.RepoCompanyId = RepoCompany.ID
|
|
left join AuctionAssignmentsToCollaterals on Collaterals.CollateralId = AuctionAssignmentsToCollaterals.CollateralId
|
|
left join AuctionAssignments on AuctionAssignmentsToCollaterals.AuctionAssignmentId = AuctionAssignments.ID
|
|
left join IMSAutoFileData on Repos.RepoId = IMSAutoFileData.RepoId
|
|
left join Auctions on Auctions.AuctionID = AuctionAssignments.AuctionId
|
|
where repos.status in ('PSKP', 'REDEEMED') or (repos.DateClosed is not null and repos.DateClosed >= @lowDate and repos.DateClosed <= @highDate)
|
|
order by DateAssigned desc;
|
|
", lowDate, highDate).ToListAsync();
|
|
}
|
|
|
|
public async Task<IReadOnlyCollection<StdReportEntry>> GetAccountsByDateSoldInAutoIMS(DateTime lowDateTime, DateTime highDateTime)
|
|
{
|
|
return await GetAccountsWithinDateRange(lowDateTime, highDateTime, "SoldDate");
|
|
}
|
|
|
|
public async Task<IReadOnlyCollection<StdReportEntry>> GetAccountsByDateRepoed(DateTime lowDateTime, DateTime highDateTime)
|
|
{
|
|
return await GetAccountsWithinDateRange(lowDateTime, highDateTime, "DateRepoed");
|
|
}
|
|
|
|
public async Task<IReadOnlyCollection<StdReportEntry>> GetAccountsByDateAssigned(DateTime lowDateTime, DateTime highDateTime)
|
|
{
|
|
return await GetAccountsWithinDateRange(lowDateTime, highDateTime, "RepoCompanyAssignmentDate");
|
|
}
|
|
|
|
public async Task<IReadOnlyCollection<StdReportEntry>> GetAccountsByStatusChangeInPeriod(string status, DateTime lowDateTime, DateTime highDateTime)
|
|
{
|
|
return await GetAccountsWithLatestStatusChangeInPeriod(lowDateTime, highDateTime, status);
|
|
}
|
|
|
|
public async Task<IReadOnlyCollection<StdReportEntry>> GetAccountsByO4RDate(DateTime lowDateTime, DateTime highDateTime)
|
|
{
|
|
return await GetAccountsWithinDateRange(lowDateTime, highDateTime, "O4RDate");
|
|
}
|
|
|
|
private async Task<IReadOnlyCollection<StdReportEntry>> GetAccountsWithinDateRange(DateTime lowDateTime, DateTime highDateTime, string dateFieldToFilter)
|
|
{
|
|
var lowDate = new SqlParameter("lowDate", lowDateTime);
|
|
var highDate = new SqlParameter("highDate", highDateTime);
|
|
return await _context.StandardReportEntries.FromSql(
|
|
$@"
|
|
select
|
|
repos.repoId as RepoId,
|
|
repos.Balance as Balance,
|
|
repos.AccountNo as AccountNo,
|
|
repos.AccountTypeCode as AccountTypeCode,
|
|
repos.Bankruptcy as Bankruptcy,
|
|
repos.Collector as Collector,
|
|
repos.O4RDate as O4RDate,
|
|
DATEDIFF(day, Repos.DueToPayDate, CURRENT_TIMESTAMP) as DaysCurrentDelq,
|
|
Repos.DaysDelinquent as DaysDelqWhenAdded,
|
|
Repos.Status as Status,
|
|
(select max(ChangedDate) from RepoStatusChanges where RepoId = repos.RepoId) as MostRecentStatusChangeDate,
|
|
DATEDIFF(day, RepoStatusChanges.ChangedDate, CURRENT_TIMESTAMP) as DaysInStatus,
|
|
RepoStatusChanges.ChangedDate as StatusChangeDate,
|
|
Repos.LstPmtAmt as LastPaymentAmount,
|
|
Repos.LastPaymentDate as LastPaymentDate,
|
|
Repos.DelinquentAmount as DelinquentAmount,
|
|
Collaterals.CollateralId as CollateralId,
|
|
RepoCompanySelections.RepoCompanyId as RepoCompanyId,
|
|
Collaterals.Year as Year,
|
|
Collaterals.Make as Make,
|
|
Collaterals.Model as Model,
|
|
RepoCompany.Name as RepoCompany,
|
|
RepoCompanySelections.DateRepoed as DateRepoed,
|
|
RepoCompanySelections.RepoCompanyAssignmentDate as RepoCompanyAssignmentDate,
|
|
RepoCompanySelections.RepoType as RepoType,
|
|
DATEDIFF(day, RepoCompanySelections.RepoCompanyAssignmentDate, RepoCompanySelections.DateRepoed) as RepoDuration,
|
|
AuctionAssignments.AuctionId as AuctionId,
|
|
Auctions.Name as AuctionName,
|
|
IMSAutoFileData.Status as AutoImsStatus,
|
|
IMSAutoFileData.SoldDate as SoldDate,
|
|
IMSAutoFileData.DateEnteredAutoIMS as DateEnteredAutoIMS,
|
|
IMSAutoFileData.ScheduledSaleDate as ScheduledSaleDate,
|
|
IMSAutoFileData.VehicleGrade as Grade, IMSAutoFileData.FloorPriceAccepted as FloorPrice,
|
|
IMSAutoFileData.SalePrice as GrossSalePrice,
|
|
IMSAutoFileData.GrossPercentFloor as GrossPercentFloor,
|
|
IMSAutoFileData.NetPercentFloor as NetPercentFloor,
|
|
IMSAutoFileData.TotalCharges as TotalCharges,
|
|
Borrowers.IndividualName as IndividualName,
|
|
null as FilteredStatus,
|
|
null as FilteredStatusChangeDate
|
|
From repos
|
|
left join RepoStatusChanges on RepoStatusChanges.RepoId = Repos.RepoId
|
|
and RepoStatusChanges.ChangedDate = (select max(ChangedDate) from RepoStatusChanges where RepoId = repos.RepoId)
|
|
left join Collaterals on Collaterals.RepoId = Repos.RepoId
|
|
left join RepoCompanySelections on Collaterals.CollateralId = RepoCompanySelections.CollateralId
|
|
left join RepoCompany on RepoCompanySelections.RepoCompanyId = RepoCompany.ID
|
|
left join AuctionAssignmentsToCollaterals on Collaterals.CollateralId = AuctionAssignmentsToCollaterals.CollateralId
|
|
left join AuctionAssignments on AuctionAssignmentsToCollaterals.AuctionAssignmentId = AuctionAssignments.ID
|
|
left join Auctions on Auctions.AuctionID = AuctionAssignments.AuctionId
|
|
left join IMSAutoFileData on Repos.RepoId = IMSAutoFileData.RepoId
|
|
left join Borrowers on Repos.RepoId = Borrowers.RepoId and Borrowers.OwnershipType = 6
|
|
where RepoCompanySelections.DateTimeCreated = (
|
|
select max(RepoCompanySelections.DateTimeCreated)
|
|
from RepoCompanySelections
|
|
where RepoCompanySelections.CollateralId = Collaterals.CollateralId
|
|
) and {dateFieldToFilter} >= @lowDate and {dateFieldToFilter} <= @highDate
|
|
", lowDate, highDate).Distinct().ToListAsync();
|
|
}
|
|
|
|
private async Task<IReadOnlyCollection<StdReportEntry>> GetAccountsWithLatestStatusChangeInPeriod(DateTime lowDateTime, DateTime highDateTime, string status)
|
|
{
|
|
var lowDate = new SqlParameter("lowDate", lowDateTime);
|
|
var highDate = new SqlParameter("highDate", highDateTime);
|
|
var repoStatus = new SqlParameter("repoStatus", status);
|
|
return await _context.StandardReportEntries.FromSql(
|
|
$@"
|
|
select
|
|
repos.repoId as RepoId,
|
|
repos.Balance as Balance,
|
|
repos.AccountNo as AccountNo,
|
|
repos.AccountTypeCode as AccountTypeCode,
|
|
repos.Bankruptcy as Bankruptcy,
|
|
repos.Collector as Collector,
|
|
repos.O4RDate as O4RDate,
|
|
DATEDIFF(day, Repos.DueToPayDate, CURRENT_TIMESTAMP) as DaysCurrentDelq,
|
|
Repos.DaysDelinquent as DaysDelqWhenAdded,
|
|
Repos.Status as Status,
|
|
(select max(ChangedDate) from RepoStatusChanges where RepoId = repos.RepoId) as MostRecentStatusChangeDate,
|
|
DATEDIFF(day, RepoStatusChanges.ChangedDate, CURRENT_TIMESTAMP) as DaysInStatus,
|
|
RepoStatusChanges.ChangedDate as StatusChangeDate,
|
|
Repos.LstPmtAmt as LastPaymentAmount,
|
|
Repos.LastPaymentDate as LastPaymentDate,
|
|
Repos.DelinquentAmount as DelinquentAmount,
|
|
Collaterals.CollateralId as CollateralId,
|
|
RepoCompanySelections.RepoCompanyId as RepoCompanyId,
|
|
Collaterals.Year as Year,
|
|
Collaterals.Make as Make,
|
|
Collaterals.Model as Model,
|
|
RepoCompany.Name as RepoCompany,
|
|
RepoCompanySelections.DateRepoed as DateRepoed,
|
|
RepoCompanySelections.RepoCompanyAssignmentDate as RepoCompanyAssignmentDate,
|
|
RepoCompanySelections.RepoType as RepoType,
|
|
DATEDIFF(day, RepoCompanySelections.RepoCompanyAssignmentDate, RepoCompanySelections.DateRepoed) as RepoDuration,
|
|
AuctionAssignments.AuctionId as AuctionId,
|
|
Auctions.Name as AuctionName,
|
|
IMSAutoFileData.Status as AutoImsStatus,
|
|
IMSAutoFileData.SoldDate as SoldDate,
|
|
IMSAutoFileData.DateEnteredAutoIMS as DateEnteredAutoIMS,
|
|
IMSAutoFileData.ScheduledSaleDate as ScheduledSaleDate,
|
|
IMSAutoFileData.VehicleGrade as Grade, IMSAutoFileData.FloorPriceAccepted as FloorPrice,
|
|
IMSAutoFileData.SalePrice as GrossSalePrice,
|
|
IMSAutoFileData.GrossPercentFloor as GrossPercentFloor,
|
|
IMSAutoFileData.NetPercentFloor as NetPercentFloor,
|
|
IMSAutoFileData.TotalCharges as TotalCharges,
|
|
Borrowers.IndividualName as IndividualName,
|
|
DesiredStatusChange.StatusTo as FilteredStatus,
|
|
DesiredStatusChange.ChangedDate as FilteredStatusChangeDate
|
|
From repos
|
|
left join RepoStatusChanges on RepoStatusChanges.RepoId = Repos.RepoId
|
|
and RepoStatusChanges.ChangedDate = (select max(ChangedDate) from RepoStatusChanges where RepoId = repos.RepoId)
|
|
left join Collaterals on Collaterals.RepoId = Repos.RepoId
|
|
left join RepoCompanySelections on Collaterals.CollateralId = RepoCompanySelections.CollateralId
|
|
left join RepoCompany on RepoCompanySelections.RepoCompanyId = RepoCompany.ID
|
|
left join AuctionAssignmentsToCollaterals on Collaterals.CollateralId = AuctionAssignmentsToCollaterals.CollateralId
|
|
left join AuctionAssignments on AuctionAssignmentsToCollaterals.AuctionAssignmentId = AuctionAssignments.ID
|
|
left join Auctions on Auctions.AuctionID = AuctionAssignments.AuctionId
|
|
left join IMSAutoFileData on Repos.RepoId = IMSAutoFileData.RepoId
|
|
left join Borrowers on Repos.RepoId = Borrowers.RepoId and Borrowers.OwnershipType = 6
|
|
inner join RepoStatusChanges DesiredStatusChange on DesiredStatusChange.RepoId = Repos.RepoId
|
|
and DesiredStatusChange.StatusTo = @repoStatus
|
|
and DesiredStatusChange.ChangedDate >= @lowDate
|
|
and DesiredStatusChange.ChangedDate <= @highDate
|
|
where RepoCompanySelections.DateTimeCreated = (
|
|
select max(RepoCompanySelections.DateTimeCreated)
|
|
from RepoCompanySelections
|
|
where RepoCompanySelections.CollateralId = Collaterals.CollateralId
|
|
)
|
|
", lowDate, highDate, repoStatus).Distinct().ToListAsync();
|
|
}
|
|
}
|
|
} |