Dhananjaya99
331 lines
15 KiB
C#
331 lines
15 KiB
C#
using Fitness_core_BE.DTOs.Stats;
|
|
using Fitness_core_BE.Interfaces;
|
|
using Fitness_core_BE.Models;
|
|
using Microsoft.EntityFrameworkCore;
|
|
|
|
namespace Fitness_core_BE.Repos
|
|
{
|
|
public class ClientStatRepository : IClientStatRepository
|
|
{
|
|
private readonly TenantDbContext _dbContext;
|
|
|
|
public ClientStatRepository(TenantDbContext dbContext)
|
|
{
|
|
_dbContext = dbContext;
|
|
}
|
|
|
|
// Member counts are driven by SubscriptionMembers so group secondaries
|
|
// (couple/family) are counted, not just subscription primaries.
|
|
private IQueryable<SubscriptionMember> ActiveMembersForClient(Guid clientId)
|
|
{
|
|
return _dbContext.SubscriptionMembers
|
|
.Where(m => m.Subscription.CustomerPlan.FKClientId == clientId
|
|
&& m.Subscription.IsActive
|
|
&& m.IsActive
|
|
&& m.TenantCustomerLink.IsActiveAtThisClient);
|
|
}
|
|
|
|
public async Task<int> GetTotalCustomerCountAsync(Guid clientId, CancellationToken ct = default)
|
|
{
|
|
return await ActiveMembersForClient(clientId)
|
|
.Select(m => m.FKCustomerId)
|
|
.Distinct()
|
|
.CountAsync(ct);
|
|
}
|
|
|
|
public async Task<int> GetMonthlyCustomerCountAsync(Guid clientId, CancellationToken ct = default)
|
|
{
|
|
var startOfMonth = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, 1, 0, 0, 0, DateTimeKind.Utc);
|
|
|
|
return await ActiveMembersForClient(clientId)
|
|
.Where(m => m.JoinedAt >= startOfMonth)
|
|
.Select(m => m.FKCustomerId)
|
|
.Distinct()
|
|
.CountAsync(ct);
|
|
}
|
|
|
|
public async Task<int> GetTotalWorkoutPlanCountAsync(Guid clientId, CancellationToken ct = default)
|
|
{
|
|
return await _dbContext.WorkoutPlans
|
|
.Where(w => w.FKClientId == clientId && w.IsActive)
|
|
.CountAsync(ct);
|
|
}
|
|
|
|
public async Task<int> GetTotalMealPlanCountAsync(Guid clientId, CancellationToken ct = default)
|
|
{
|
|
return await _dbContext.MealPlans
|
|
.Where(m => m.FKClientId == clientId && m.IsActive)
|
|
.CountAsync(ct);
|
|
}
|
|
|
|
public async Task<int> GetLastMonthCustomerCountAsync(Guid clientId, CancellationToken ct = default)
|
|
{
|
|
var now = DateTime.UtcNow;
|
|
var startOfLastMonth = new DateTime(now.Year, now.Month, 1, 0, 0, 0, DateTimeKind.Utc).AddMonths(-1);
|
|
var startOfThisMonth = new DateTime(now.Year, now.Month, 1, 0, 0, 0, DateTimeKind.Utc);
|
|
|
|
return await ActiveMembersForClient(clientId)
|
|
.Where(m => m.JoinedAt >= startOfLastMonth && m.JoinedAt < startOfThisMonth)
|
|
.Select(m => m.FKCustomerId)
|
|
.Distinct()
|
|
.CountAsync(ct);
|
|
}
|
|
|
|
public async Task<List<Attendance>> GetLastFiveAttendancesAsync(Guid clientId, CancellationToken ct = default)
|
|
{
|
|
return await _dbContext.Attendances
|
|
.Where(a => a.FKClientId == clientId)
|
|
.Include(a => a.TenantCustomerLink)
|
|
.OrderByDescending(a => a.EventTime)
|
|
.Take(5)
|
|
.AsNoTracking()
|
|
.ToListAsync(ct);
|
|
}
|
|
|
|
public async Task<List<PaymentTransaction>> GetLastFivePaymentsAsync(Guid clientId, CancellationToken ct = default)
|
|
{
|
|
var payments = await _dbContext.PaymentTransactions
|
|
.Where(p => p.FK_ClientId == clientId && p.IsActive == true)
|
|
.Include(p => p.CustomerInvoice)
|
|
.ThenInclude(ci => ci.CustomerSubscription)
|
|
.ThenInclude(cs => cs.TenantCustomerLink)
|
|
.OrderByDescending(p => p.PayDate)
|
|
.Take(5)
|
|
.AsNoTracking()
|
|
.ToListAsync(ct);
|
|
|
|
// PaymentTransaction.Customer isn't a real relationship post-split (Customer
|
|
// identity lives in the central DB, this table is tenant-scoped) -- backfill a
|
|
// display-only Customer shape from the cached TenantCustomerLink so this response's
|
|
// existing customer.cusName shape keeps working for API consumers.
|
|
foreach (var payment in payments)
|
|
{
|
|
var link = payment.CustomerInvoice?.CustomerSubscription?.TenantCustomerLink;
|
|
if (link != null)
|
|
{
|
|
payment.Customer = new Customer
|
|
{
|
|
CusId = link.TenantCustomerLinkId,
|
|
CusName = link.DisplayName,
|
|
PrimaryPH = link.PrimaryPhoneCached,
|
|
Email = link.EmailCached
|
|
};
|
|
}
|
|
}
|
|
|
|
return payments;
|
|
}
|
|
|
|
// Revenue counts only approved payments (owner-recorded payments are approved
|
|
// on creation; pending online attempts are excluded).
|
|
public async Task<decimal> GetLastMonthRevenueAsync(Guid clientId, CancellationToken ct = default)
|
|
{
|
|
var now = DateTime.UtcNow;
|
|
var startOfLastMonth = new DateTime(now.Year, now.Month, 1, 0, 0, 0, DateTimeKind.Utc).AddMonths(-1);
|
|
var startOfThisMonth = new DateTime(now.Year, now.Month, 1, 0, 0, 0, DateTimeKind.Utc);
|
|
|
|
return await _dbContext.PaymentTransactions
|
|
.Where(p => p.FK_ClientId == clientId
|
|
&& p.IsActive == true
|
|
&& p.IsApproved == true
|
|
&& p.PayDate >= startOfLastMonth
|
|
&& p.PayDate < startOfThisMonth)
|
|
.SumAsync(p => p.PayAmount, ct);
|
|
}
|
|
|
|
public async Task<List<MonthlyRevenueStat>> GetMonthlyRevenueVariationAsync(
|
|
Guid clientId, DateTime? from = null, DateTime? to = null, CancellationToken ct = default)
|
|
{
|
|
DateTime rangeStart;
|
|
DateTime rangeEndExclusive;
|
|
bool byHour;
|
|
bool byDay;
|
|
|
|
if (from.HasValue && to.HasValue)
|
|
{
|
|
rangeStart = DateTime.SpecifyKind(from.Value.Date, DateTimeKind.Utc);
|
|
rangeEndExclusive = DateTime.SpecifyKind(to.Value.Date.AddDays(1), DateTimeKind.Utc);
|
|
var totalDays = (rangeEndExclusive - rangeStart).TotalDays;
|
|
// A single-day range (Today/Yesterday) would otherwise collapse to one bucket,
|
|
// so break it down by hour instead so the chart still shows a trend.
|
|
byHour = totalDays <= 1;
|
|
byDay = !byHour && totalDays <= 31;
|
|
}
|
|
else
|
|
{
|
|
// Default (no explicit range): trailing 10 calendar months, unchanged from before.
|
|
var now = DateTime.UtcNow;
|
|
rangeStart = new DateTime(now.Year, now.Month, 1, 0, 0, 0, DateTimeKind.Utc).AddMonths(-9);
|
|
rangeEndExclusive = new DateTime(now.Year, now.Month, 1, 0, 0, 0, DateTimeKind.Utc).AddMonths(1);
|
|
byHour = false;
|
|
byDay = false;
|
|
}
|
|
|
|
DateTime BucketKey(DateTime d) =>
|
|
byHour ? new DateTime(d.Year, d.Month, d.Day, d.Hour, 0, 0, DateTimeKind.Utc)
|
|
: byDay ? d.Date
|
|
: new DateTime(d.Year, d.Month, 1, 0, 0, 0, DateTimeKind.Utc);
|
|
|
|
var payments = await _dbContext.PaymentTransactions
|
|
.Where(p => p.FK_ClientId == clientId
|
|
&& p.IsActive == true
|
|
&& p.IsApproved == true
|
|
&& p.PayDate >= rangeStart
|
|
&& p.PayDate < rangeEndExclusive)
|
|
.Select(p => new { p.PayDate, p.PayAmount })
|
|
.AsNoTracking()
|
|
.ToListAsync(ct);
|
|
|
|
var newMembers = await ActiveMembersForClient(clientId)
|
|
.Where(m => m.JoinedAt >= rangeStart && m.JoinedAt < rangeEndExclusive)
|
|
.Select(m => new { m.JoinedAt, m.FKCustomerId })
|
|
.AsNoTracking()
|
|
.ToListAsync(ct);
|
|
|
|
var revenueByBucket = payments
|
|
.GroupBy(p => BucketKey(p.PayDate!.Value))
|
|
.ToDictionary(g => g.Key, g => g.Sum(p => p.PayAmount));
|
|
|
|
var newMembersByBucket = newMembers
|
|
.GroupBy(m => BucketKey(m.JoinedAt))
|
|
.ToDictionary(g => g.Key, g => g.Select(m => m.FKCustomerId).Distinct().Count());
|
|
|
|
var buckets = new List<DateTime>();
|
|
for (var d = rangeStart; d < rangeEndExclusive; d = byHour ? d.AddHours(1) : byDay ? d.AddDays(1) : d.AddMonths(1))
|
|
buckets.Add(d);
|
|
|
|
return buckets
|
|
.Select(bucket => new MonthlyRevenueStat
|
|
{
|
|
Year = bucket.Year,
|
|
Month = bucket.Month,
|
|
MonthLabel = byHour ? bucket.ToString("h tt") : byDay ? bucket.ToString("MMM d") : bucket.ToString("MMM yyyy"),
|
|
Revenue = revenueByBucket.TryGetValue(bucket, out var rev) ? rev : 0m,
|
|
NewMemberCount = newMembersByBucket.TryGetValue(bucket, out var cnt) ? cnt : 0
|
|
})
|
|
.ToList();
|
|
}
|
|
|
|
public async Task<int> GetExpiringSoonCountAsync(Guid clientId, DateTime from, DateTime to, CancellationToken ct = default)
|
|
{
|
|
return await ActiveMembersForClient(clientId)
|
|
.Where(m => m.Subscription.RenewalDate != null
|
|
&& m.Subscription.RenewalDate >= from
|
|
&& m.Subscription.RenewalDate <= to)
|
|
.Select(m => m.FKCustomerId)
|
|
.Distinct()
|
|
.CountAsync(ct);
|
|
}
|
|
public async Task<decimal> GetTotalRevenueAsync(Guid clientId, CancellationToken ct = default)
|
|
{
|
|
return await _dbContext.PaymentTransactions
|
|
.Where(p => p.FK_ClientId == clientId && p.IsActive == true && p.IsApproved == true)
|
|
.SumAsync(p => p.PayAmount, ct);
|
|
}
|
|
|
|
public async Task<ClientStatSummaryDto> GetClientStatSummaryAsync(
|
|
Guid clientId, DateTime? from = null, DateTime? to = null, CancellationToken ct = default)
|
|
{
|
|
DateTime periodStart;
|
|
DateTime periodEndExclusive;
|
|
DateTime comparisonStart;
|
|
|
|
if (from.HasValue && to.HasValue)
|
|
{
|
|
periodStart = DateTime.SpecifyKind(from.Value.Date, DateTimeKind.Utc);
|
|
periodEndExclusive = DateTime.SpecifyKind(to.Value.Date.AddDays(1), DateTimeKind.Utc);
|
|
// Equal-length window immediately preceding the selected period, for growth %.
|
|
comparisonStart = periodStart - (periodEndExclusive - periodStart);
|
|
}
|
|
else
|
|
{
|
|
var now = DateTime.UtcNow;
|
|
periodStart = new DateTime(now.Year, now.Month, 1, 0, 0, 0, DateTimeKind.Utc);
|
|
periodEndExclusive = periodStart.AddMonths(1);
|
|
comparisonStart = periodStart.AddMonths(-1);
|
|
}
|
|
|
|
int totalCustomers = await ActiveMembersForClient(clientId)
|
|
.Select(m => m.FKCustomerId)
|
|
.Distinct()
|
|
.CountAsync(ct);
|
|
|
|
int thisPeriodCustomers = await ActiveMembersForClient(clientId)
|
|
.Where(m => m.JoinedAt >= periodStart && m.JoinedAt < periodEndExclusive)
|
|
.Select(m => m.FKCustomerId)
|
|
.Distinct()
|
|
.CountAsync(ct);
|
|
|
|
int lastPeriodCustomers = await ActiveMembersForClient(clientId)
|
|
.Where(m => m.JoinedAt >= comparisonStart && m.JoinedAt < periodStart)
|
|
.Select(m => m.FKCustomerId)
|
|
.Distinct()
|
|
.CountAsync(ct);
|
|
|
|
decimal thisPeriodRevenue = await _dbContext.PaymentTransactions
|
|
.Where(p => p.FK_ClientId == clientId &&
|
|
p.IsActive == true &&
|
|
p.IsApproved == true &&
|
|
p.PayDate >= periodStart &&
|
|
p.PayDate < periodEndExclusive)
|
|
.SumAsync(p => p.PayAmount, ct);
|
|
|
|
decimal lastPeriodRevenue = await _dbContext.PaymentTransactions
|
|
.Where(p => p.FK_ClientId == clientId &&
|
|
p.IsActive == true &&
|
|
p.IsApproved == true &&
|
|
p.PayDate >= comparisonStart &&
|
|
p.PayDate < periodStart)
|
|
.SumAsync(p => p.PayAmount, ct);
|
|
|
|
decimal totalRevenue = await _dbContext.PaymentTransactions
|
|
.Where(p => p.FK_ClientId == clientId &&
|
|
p.IsActive == true &&
|
|
p.IsApproved == true)
|
|
.SumAsync(p => p.PayAmount, ct);
|
|
|
|
decimal customerGrowthPct = lastPeriodCustomers == 0
|
|
? (thisPeriodCustomers > 0 ? 100m : 0m)
|
|
: Math.Round((decimal)(thisPeriodCustomers - lastPeriodCustomers)
|
|
/ lastPeriodCustomers * 100, 2);
|
|
|
|
decimal revenueGrowthPct = lastPeriodRevenue == 0
|
|
? (thisPeriodRevenue > 0 ? 100m : 0m)
|
|
: Math.Round((thisPeriodRevenue - lastPeriodRevenue)
|
|
/ lastPeriodRevenue * 100, 2);
|
|
|
|
// OverdueCount is filled in by ClientStatService (needs IOverdueLockRepository).
|
|
var expiryWindowStart = DateTime.UtcNow;
|
|
int expiringSoon = await GetExpiringSoonCountAsync(clientId, expiryWindowStart, expiryWindowStart.AddDays(7), ct);
|
|
|
|
// "Today" always means today regardless of the from/to period filter above --
|
|
// matches the AttendanceRepository convention (DateTime.UtcNow.Date), not the
|
|
// selected reporting window.
|
|
var todayStart = DateTime.UtcNow.Date;
|
|
var todayEndExclusive = todayStart.AddDays(1);
|
|
var todayPayments = await _dbContext.PaymentTransactions
|
|
.Where(p => p.FK_ClientId == clientId &&
|
|
p.IsActive == true &&
|
|
p.IsApproved == true &&
|
|
p.PayDate >= todayStart &&
|
|
p.PayDate < todayEndExclusive)
|
|
.ToListAsync(ct);
|
|
|
|
return new ClientStatSummaryDto
|
|
{
|
|
TotalCustomerCount = totalCustomers,
|
|
ThisMonthCustomerCount = thisPeriodCustomers,
|
|
LastMonthCustomerCount = lastPeriodCustomers,
|
|
CustomerGrowthPercentage = customerGrowthPct,
|
|
ThisMonthRevenue = thisPeriodRevenue,
|
|
LastMonthRevenue = lastPeriodRevenue,
|
|
RevenueGrowthPercentage = revenueGrowthPct,
|
|
TotalRevenue = totalRevenue,
|
|
ExpiringWithin7DaysCount = expiringSoon,
|
|
TodayPaymentsCount = todayPayments.Count,
|
|
TodayPaymentsTotal = todayPayments.Sum(p => p.PayAmount)
|
|
};
|
|
}
|
|
}
|
|
}
|