public GetExhibitorFinancials GetExhibitorFinancials(int exhibitorId)
{
IEnumerable <ExhibitorMoneyReceived> data = null;
GetExhibitorFinancials getExhibitorFinancials = new GetExhibitorFinancials();
// var yearlyMaintainence = _context.YearlyMaintainence.Where(x => x.Years == DateTime.Now.Year && x.IsActive == true &&
// x.IsDeleted == false).FirstOrDefault();
var yearlyMaintainence = _context.YearlyMaintainence.Where(x => x.IsActive == true &&
x.IsDeleted == false).FirstOrDefault();
if (yearlyMaintainence != null)
{
var stallCodes = (from gcc in _context.GlobalCodeCategories
join gc in _context.GlobalCodes on gcc.GlobalCodeCategoryId equals gc.CategoryId
where gcc.CategoryName == "StallType" && gc.IsDeleted == false && gc.IsActive == true
select new
{
gc.GlobalCodeId,
gc.CodeName,
gc.IsDeleted
}).ToList();
int horseStallTypeId = stallCodes != null?stallCodes.Where(x => x.CodeName == "HorseStall" && x.IsDeleted == false).
Select(x => x.GlobalCodeId).FirstOrDefault() : 0;
int tackStallTypeId = stallCodes != null?stallCodes.Where(x => x.CodeName == "TackStall" && x.IsDeleted == false).
Select(x => x.GlobalCodeId).FirstOrDefault() : 0;
var preHorseStall = _context.StallAssignment.Where(x => x.ExhibitorId == exhibitorId && x.StallAssignmentTypeId == horseStallTypeId &&
x.Date.Date <= yearlyMaintainence.PreEntryCutOffDate.Date &&
x.IsActive == true && x.IsDeleted == false).ToList();
var preTackStall = _context.StallAssignment.Where(x => x.ExhibitorId == exhibitorId && x.StallAssignmentTypeId == tackStallTypeId &&
x.Date.Date <= yearlyMaintainence.PreEntryCutOffDate.Date &&
x.IsActive == true && x.IsDeleted == false).ToList();
var preClasses = _context.ExhibitorClass.Where(x => x.ExhibitorId == exhibitorId &&
x.Date.Date <= yearlyMaintainence.PreEntryCutOffDate.Date &&
x.IsActive == true && x.IsDeleted == false).ToList();
int additionalPrograme = _context.Exhibitors.Where(x => x.ExhibitorId == exhibitorId && x.IsActive == true && x.IsDeleted == false
).Select(x => x.QTYProgram).FirstOrDefault();
var postHorseStall = _context.StallAssignment.Where(x => x.ExhibitorId == exhibitorId && x.StallAssignmentTypeId == horseStallTypeId &&
x.Date.Date > yearlyMaintainence.PreEntryCutOffDate.Date &&
x.IsActive == true && x.IsDeleted == false).ToList();
var postTackStall = _context.StallAssignment.Where(x => x.ExhibitorId == exhibitorId && x.StallAssignmentTypeId == tackStallTypeId &&
x.Date.Date > yearlyMaintainence.PreEntryCutOffDate.Date &&
x.IsActive == true && x.IsDeleted == false).ToList();
var postClasses = _context.ExhibitorClass.Where(x => x.ExhibitorId == exhibitorId &&
x.Date.Date > yearlyMaintainence.PreEntryCutOffDate.Date &&
x.IsActive == true && x.IsDeleted == false).ToList();
var allFees = _context.YearlyMaintainenceFee.Where(x => x.FeeType == "GeneralFee" && x.IsDeleted == false).ToList();
var horseStallFee = allFees.Where(x => x.FeeName == "Stall" &&
x.YearlyMaintainenceId == yearlyMaintainence.YearlyMaintainenceId && x.IsDeleted == false).ToList();
var tackStallFee = allFees.Where(x => x.FeeName == "Tack" &&
x.YearlyMaintainenceId == yearlyMaintainence.YearlyMaintainenceId && x.IsDeleted == false).ToList();
decimal additionalProgramsFee = allFees.Where(x => x.FeeName == "Additional Programs" &&
x.YearlyMaintainenceId == yearlyMaintainence.YearlyMaintainenceId && x.IsDeleted == false).Select
(x => x.Amount).FirstOrDefault();
var classEntryFee = allFees.Where(x => x.FeeName == "Class Entry" &&
x.YearlyMaintainenceId == yearlyMaintainence.YearlyMaintainenceId && x.IsDeleted == false).ToList();
var nsbaClassEntryFee = allFees.Where(x => x.FeeName == "NSBA Entry" && x.YearlyMaintainenceId == yearlyMaintainence.YearlyMaintainenceId &&
x.IsDeleted == false).ToList();
var administrationFee = allFees.Where(x => x.FeeName == "Administration" && x.YearlyMaintainenceId == yearlyMaintainence.YearlyMaintainenceId &&
x.IsDeleted == false).ToList();
var checkNSBAExhibitor = _context.Exhibitors.Where(x => x.ExhibitorId == exhibitorId).FirstOrDefault();
var allNSBAClasses = _context.Classes.Where(x => x.IsNSBAMember == true && x.IsDeleted == false).ToList();
decimal preClassEntryAmount = 0;
decimal postClassEntryAmount = 0;
int preNSBAClasses = 0;
int postNSBAClasses = 0;
if (allNSBAClasses.Count() != 0)
{
if (checkNSBAExhibitor.IsNSBAMember == true)
{
if (preClasses.Count != 0)
{
var preNSBA = (from all in allNSBAClasses
where preClasses.Any(x => x.ClassId == all.ClassId
)
select new { all.ClassId });
preNSBAClasses = preNSBA.Count();
}
if (postClasses.Count != 0)
{
var postNSBA = (from all in allNSBAClasses
where postClasses.Any(x => x.ClassId == all.ClassId
)
select new { all.ClassId });
postNSBAClasses = postNSBA.Count();
}
if (nsbaClassEntryFee != null)
{
decimal preNSBAClassFee = nsbaClassEntryFee.Where(x => x.TimeFrame == "Pre").Select(x => x.Amount).FirstOrDefault();
decimal postNSBAClassFee = nsbaClassEntryFee.Where(x => x.TimeFrame == "Post").Select(x => x.Amount).FirstOrDefault();
preClassEntryAmount = preNSBAClassFee * preNSBAClasses;
postClassEntryAmount = postNSBAClassFee * postNSBAClasses;
}
}
}
decimal preHorseStallAmount = 0;
if (horseStallFee.Count != 0)
{
decimal preHoseStallFee = horseStallFee.Where(x => x.TimeFrame == "Pre").Select(x => x.Amount).FirstOrDefault();
preHorseStallAmount = preHoseStallFee * preHorseStall.Count();
}
decimal preTackStallAmount = 0;
if (tackStallFee.Count != 0)
{
decimal preTackStallFee = tackStallFee.Where(x => x.TimeFrame == "Pre").Select(x => x.Amount).FirstOrDefault();
preTackStallAmount = preTackStallFee * preTackStall.Count();
}
decimal preClassAmount = 0;
if (classEntryFee.Count != 0)
{
decimal preClassFee = classEntryFee.Where(x => x.TimeFrame == "Pre").Select(x => x.Amount).FirstOrDefault();
preClassAmount = preClassFee * preClasses.Count();
}
decimal preAdministrationAmount = 0;
int numberOfHorses = _context.ExhibitorHorse.Where(x => x.ExhibitorId == exhibitorId && x.IsDeleted == false).Count();
if (administrationFee.Count != 0)
{
decimal preAdministrationFee = administrationFee.Where(x => x.TimeFrame == "Pre").Select(x => x.Amount).FirstOrDefault();
preAdministrationAmount = numberOfHorses * preAdministrationFee;
}
decimal additionalAmount = 0;
additionalAmount = additionalProgramsFee * additionalPrograme;
decimal postHorseStallAmount = 0;
if (horseStallFee.Count != 0)
{
decimal postHoseStallFee = horseStallFee.Where(x => x.TimeFrame == "Post").Select(x => x.Amount).FirstOrDefault();
postHorseStallAmount = postHoseStallFee * postHorseStall.Count();
}
decimal postTackStallAmount = 0;
if (tackStallFee.Count != 0)
{
decimal postTackStallFee = tackStallFee.Where(x => x.TimeFrame == "Post").Select(x => x.Amount).FirstOrDefault();
postTackStallAmount = postTackStallFee * postTackStall.Count();
}
decimal postClassAmount = 0;
if (classEntryFee.Count != 0)
{
decimal postClassStallFee = classEntryFee.Where(x => x.TimeFrame == "Post").Select(x => x.Amount).FirstOrDefault();
postClassAmount = postClassStallFee * postClasses.Count();
}
decimal horseStallAmount = preHorseStallAmount + postHorseStallAmount;
decimal tackStallAmount = preTackStallAmount + postTackStallAmount;
decimal classAmount = preClassAmount + postClassAmount;
decimal nsbaClassesAmount = preClassEntryAmount + postClassEntryAmount;
int horseStall = preHorseStall.Count() + postHorseStall.Count();
int tackStall = preTackStall.Count() + postTackStall.Count();
int classes = preClasses.Count() + postClasses.Count();
int nsbaClasses = preNSBAClasses + postNSBAClasses;
string[] feetype = { "Stall", "Tack", "Additional Programs", "Class Entry", "NSBA Entry", "Administration" };
decimal[] amount = { horseStallAmount, tackStallAmount, additionalAmount, classAmount, nsbaClassesAmount, preAdministrationAmount };
int[] qty = { horseStall, tackStall, additionalPrograme, classes, nsbaClasses, numberOfHorses };
List <ExhibitorFeesBilled> exhibitorFeesBilleds = new List <ExhibitorFeesBilled>();
for (int i = 0; i < feetype.Count(); i++)
{
if (qty[i] != 0)
{
ExhibitorFeesBilled exhibitorFeesBilled = new ExhibitorFeesBilled();
exhibitorFeesBilled.FeeTypeId = allFees.Where(x => x.FeeName == feetype[i] && x.YearlyMaintainenceId ==
yearlyMaintainence.YearlyMaintainenceId && (x.TimeFrame == "Pre" || x.TimeFrame == "")).
Select(x => x.YearlyMaintainenceFeeId).FirstOrDefault();
exhibitorFeesBilled.Qty = qty[i];
exhibitorFeesBilled.FeeType = feetype[i];
exhibitorFeesBilled.Amount = amount[i];
exhibitorFeesBilleds.Add(exhibitorFeesBilled);
}
}
getExhibitorFinancials.exhibitorFeesBilled = exhibitorFeesBilleds;
getExhibitorFinancials.FeeBilledTotal = horseStallAmount + tackStallAmount + additionalAmount + classAmount + nsbaClassesAmount + preAdministrationAmount;
data = (from exhibitorpayment in _context.ExhibitorPaymentDetails
where exhibitorpayment.IsActive == true && exhibitorpayment.IsDeleted == false &&
exhibitorpayment.ExhibitorId == exhibitorId
select new ExhibitorMoneyReceived
{
Date = exhibitorpayment.PayDate,
Amount = exhibitorpayment.AmountPaid,
});
getExhibitorFinancials.exhibitorMoneyReceived = data.ToList();
getExhibitorFinancials.MoneyReceivedTotal = _context.ExhibitorPaymentDetails.Where(x => x.ExhibitorId == exhibitorId && x.IsDeleted == false).Select(x => x.AmountPaid).Sum();
getExhibitorFinancials.Outstanding = (horseStallAmount + tackStallAmount + additionalAmount + classAmount + nsbaClassesAmount + preAdministrationAmount) - (getExhibitorFinancials.MoneyReceivedTotal);
decimal overPayment = (getExhibitorFinancials.MoneyReceivedTotal) - (horseStallAmount + tackStallAmount + additionalAmount + classAmount + nsbaClassesAmount + preAdministrationAmount);
if (overPayment < 0)
{
overPayment = 0;
}
if (getExhibitorFinancials.Outstanding < 0)
{
getExhibitorFinancials.Outstanding = 0;
}
getExhibitorFinancials.OverPayment = overPayment;
getExhibitorFinancials.Refunds = _context.ExhibitorPaymentDetail.Where(x => x.ExhibitorId == exhibitorId && x.IsDeleted == false).Select(x => x.RefundAmount).Sum();
}
return(getExhibitorFinancials);
}
public GetExhibitorFinancials GetExhibitorFinancials(int exhibitorId)
{
IEnumerable <ExhibitorMoneyReceived> data = null;
GetExhibitorFinancials getExhibitorFinancials = new GetExhibitorFinancials();
var yearlyMaintainence = _context.YearlyMaintainence.Where(x => x.Years == DateTime.Now.Year && x.IsActive == true &&
x.IsDeleted == false).FirstOrDefault();
var stallCodes = (from gcc in _context.GlobalCodeCategories
join gc in _context.GlobalCodes on gcc.GlobalCodeCategoryId equals gc.CategoryId
where gcc.CategoryName == "StallType" && gc.IsDeleted == false && gc.IsActive == true
select new
{
gc.GlobalCodeId,
gc.CodeName,
gc.IsDeleted
}).ToList();
int horseStallTypeId = stallCodes.Where(x => x.CodeName == "HorseStall" && x.IsDeleted == false).Select(x => x.GlobalCodeId).FirstOrDefault();
int tackStallTypeId = stallCodes.Where(x => x.CodeName == "TackStall" && x.IsDeleted == false).Select(x => x.GlobalCodeId).FirstOrDefault();
var preHorseStall = _context.StallAssignment.Where(x => x.ExhibitorId == exhibitorId && x.StallAssignmentTypeId == horseStallTypeId &&
x.Date.Date < yearlyMaintainence.PreEntryCutOffDate.Date &&
x.IsActive == true && x.IsDeleted == false).ToList();
var preTackStall = _context.StallAssignment.Where(x => x.ExhibitorId == exhibitorId && x.StallAssignmentTypeId == tackStallTypeId &&
x.Date.Date < yearlyMaintainence.PreEntryCutOffDate.Date &&
x.IsActive == true && x.IsDeleted == false).ToList();
var preClasses = _context.ExhibitorClass.Where(x => x.ExhibitorId == exhibitorId &&
x.Date.Date < yearlyMaintainence.PreEntryCutOffDate.Date &&
x.IsActive == true && x.IsDeleted == false).ToList();
int additionalPrograme = _context.Exhibitors.Where(x => x.ExhibitorId == exhibitorId && x.IsActive == true && x.IsDeleted == false
).Select(x => x.QTYProgram).FirstOrDefault();
var postHorseStall = _context.StallAssignment.Where(x => x.ExhibitorId == exhibitorId && x.StallAssignmentTypeId == horseStallTypeId &&
x.Date.Date > yearlyMaintainence.PreEntryCutOffDate.Date &&
x.IsActive == true && x.IsDeleted == false).ToList();
var postTackStall = _context.StallAssignment.Where(x => x.ExhibitorId == exhibitorId && x.StallAssignmentTypeId == tackStallTypeId &&
x.Date.Date > yearlyMaintainence.PreEntryCutOffDate.Date &&
x.IsActive == true && x.IsDeleted == false).ToList();
var postClasses = _context.ExhibitorClass.Where(x => x.ExhibitorId == exhibitorId &&
x.Date.Date > yearlyMaintainence.PreEntryCutOffDate.Date &&
x.IsActive == true && x.IsDeleted == false).ToList();
var feeCodes = (from gcc in _context.GlobalCodeCategories
join gc in _context.GlobalCodes on gcc.GlobalCodeCategoryId equals gc.CategoryId
where gcc.CategoryName == "FeeType" && gc.IsDeleted == false && gc.IsActive == true
select new
{
gc.GlobalCodeId,
gc.CodeName,
gc.IsDeleted
}).ToList();
int horseStallFeeId = feeCodes.Where(x => x.CodeName == "Stall" && x.IsDeleted == false).Select(x => x.GlobalCodeId).FirstOrDefault();
int tackStallFeeId = feeCodes.Where(x => x.CodeName == "Tack" && x.IsDeleted == false).Select(x => x.GlobalCodeId).FirstOrDefault();
int additionalProgramsFeeId = feeCodes.Where(x => x.CodeName == "Additional Program" && x.IsDeleted == false).Select(x => x.GlobalCodeId).FirstOrDefault();
int classEntryId = feeCodes.Where(x => x.CodeName == "Class Entry" && x.IsDeleted == false).Select(x => x.GlobalCodeId).FirstOrDefault();
int sponsorRefundId = feeCodes.Where(x => x.CodeName == "Ad Sponsor Refund" && x.IsDeleted == false).Select(x => x.GlobalCodeId).FirstOrDefault();
var horseStallFee = _context.YearlyMaintainenceFee.Where(x => x.FeeTypeId == horseStallFeeId && x.YearlyMaintainenceId == yearlyMaintainence.YearlyMaintainenceId).FirstOrDefault();
var tackStallFee = _context.YearlyMaintainenceFee.Where(x => x.FeeTypeId == tackStallFeeId && x.YearlyMaintainenceId == yearlyMaintainence.YearlyMaintainenceId).FirstOrDefault();
decimal additionalProgramsFee = _context.YearlyMaintainenceFee.Where(x => x.FeeTypeId == additionalProgramsFeeId && x.YearlyMaintainenceId == yearlyMaintainence.YearlyMaintainenceId).Select
(x => x.Amount).FirstOrDefault();
var classEntryFee = _context.YearlyMaintainenceFee.Where(x => x.FeeTypeId == classEntryId && x.YearlyMaintainenceId == yearlyMaintainence.YearlyMaintainenceId).FirstOrDefault();
decimal preHorseStallAmount = 0;
if (horseStallFee != null)
{
preHorseStallAmount = horseStallFee.PreEntryFee * preHorseStall.Count();
}
decimal preTackStallAmount = 0;
if (tackStallFee != null)
{
preTackStallAmount = tackStallFee.PreEntryFee * preTackStall.Count();
}
decimal preClassAmount = 0;
if (classEntryFee != null)
{
preClassAmount = classEntryFee.PreEntryFee * preClasses.Count();
}
decimal additionalAmount = 0;
if (additionalProgramsFee != null && additionalPrograme != null)
{
additionalAmount = additionalProgramsFee * additionalPrograme;
}
decimal postHorseStallAmount = 0;
if (horseStallFee != null)
{
postHorseStallAmount = horseStallFee.PostEntryFee * postHorseStall.Count();
}
decimal postTackStallAmount = 0;
if (tackStallFee != null)
{
postTackStallAmount = tackStallFee.PostEntryFee * postTackStall.Count();
}
decimal postClassAmount = 0;
if (classEntryFee != null)
{
postClassAmount = classEntryFee.PostEntryFee * postClasses.Count();
}
decimal horseStallAmount = preHorseStallAmount + postHorseStallAmount;
decimal tackStallAmount = preTackStallAmount + postTackStallAmount;
decimal classAmount = preClassAmount + postClassAmount;
int horseStall = preHorseStall.Count() + postHorseStall.Count();
int tackStall = preTackStall.Count() + postTackStall.Count();
int classes = preClasses.Count() + postClasses.Count();
int[] FeeTypeId = { horseStallFeeId, tackStallFeeId, additionalProgramsFeeId, classEntryId };
string[] feetype = { "Stall", "Tack", "Additional Programs", "Class Entry" };
decimal[] amount = { horseStallAmount, tackStallAmount, additionalAmount, classAmount };
int[] qty = { horseStall, tackStall, additionalPrograme, classes };
List <ExhibitorFeesBilled> exhibitorFeesBilleds = new List <ExhibitorFeesBilled>();
for (int i = 0; i < feetype.Count(); i++)
{
if (qty[i] != 0)
{
ExhibitorFeesBilled exhibitorFeesBilled = new ExhibitorFeesBilled();
exhibitorFeesBilled.FeeTypeId = FeeTypeId[i];
exhibitorFeesBilled.Qty = qty[i];
exhibitorFeesBilled.FeeType = feetype[i];
exhibitorFeesBilled.Amount = amount[i];
exhibitorFeesBilleds.Add(exhibitorFeesBilled);
}
}
getExhibitorFinancials.exhibitorFeesBilled = exhibitorFeesBilleds;
getExhibitorFinancials.FeeBilledTotal = horseStallAmount + tackStallAmount + additionalAmount + classAmount;
data = (from exhibitorpayment in _context.ExhibitorPaymentDetails
where exhibitorpayment.IsActive == true && exhibitorpayment.IsDeleted == false &&
exhibitorpayment.ExhibitorId == exhibitorId && exhibitorpayment.FeeTypeId != sponsorRefundId
select new ExhibitorMoneyReceived
{
Date = exhibitorpayment.PayDate,
Amount = exhibitorpayment.AmountPaid,
});
getExhibitorFinancials.exhibitorMoneyReceived = data.ToList();
getExhibitorFinancials.MoneyReceivedTotal = _context.ExhibitorPaymentDetails.Where(x => x.ExhibitorId == exhibitorId && x.IsActive == true && x.IsDeleted == false).Select(x => x.AmountPaid).Sum();
getExhibitorFinancials.Outstanding = (horseStallAmount + tackStallAmount + additionalAmount + classAmount) - (getExhibitorFinancials.MoneyReceivedTotal);
decimal overPayment = (getExhibitorFinancials.MoneyReceivedTotal) - (horseStallAmount + tackStallAmount + additionalAmount + classAmount);
if (overPayment < 0)
{
overPayment = 0;
}
if (getExhibitorFinancials.Outstanding < 0)
{
getExhibitorFinancials.Outstanding = 0;
}
getExhibitorFinancials.OverPayment = overPayment;
getExhibitorFinancials.Refunds = _context.ExhibitorPaymentDetail.Where(x => x.ExhibitorId == exhibitorId && x.IsActive == true && x.IsDeleted == false).Select(x => x.RefundAmount).Sum();
return(getExhibitorFinancials);
}
