public void Delete(int id)
{
MLFSReportingPeriod period = _db.MLFSReportingPeriods.Find(id);
_db.MLFSReportingPeriods.Remove(period);
_db.SaveChanges();
}
public void CreateNTUTest()
{
//arrange
MLFSSale sale = new MLFSSale()
{
Id = 1,
ReportingPeriodId = 6,
Investment = 100,
NetAmount = 4,
OnGoingPercentage = (decimal)0.005,
RelevantDate = DateTime.Parse("01/02/2020")
};
MLFSReportingPeriod period = new MLFSReportingPeriod(5, 2020)
{
Id = 2
};
//act
MLFSDebtorAdjustment adj = sale.CreateNTU(period);
//assert
Assert.AreEqual(-4, adj.Amount, "Amount is not the inverse of the debtor");
Assert.AreEqual(2, adj.ReportingPeriodId, "Correct reporting period not given.");
Assert.AreEqual(true, adj.NotTakenUp, "Not reporting as an NTU adjustment");
}
public async Task <List <MLFSIncomeReport> > GetIncome(int currentMonthId, bool currentFY = false, bool last12periods = false)
{
MLFSReportingPeriod period = await _periodData.GetPeriodById(currentMonthId);
List <MLFSIncome> incomeLines = new List <MLFSIncome>();
List <MLFSIncomeReport> reportLines = new List <MLFSIncomeReport>();
if (!currentFY && !last12periods)
{
incomeLines = period.Receipts;
}
else
{
List <MLFSReportingPeriod> periods = new List <MLFSReportingPeriod>();
if (currentFY)
{
periods = await _periodData.GetFinancialYear(period);
}
else if (last12periods)
{
periods = await _periodData.GetLast12Months(period);
}
incomeLines = periods.SelectMany(x => x.Receipts).ToList();
}
foreach (MLFSIncome income in incomeLines)
{
reportLines.Add(new MLFSIncomeReport(income));
}
return(reportLines);
}
public async Task <int> InsertPeriod(MLFSReportingPeriod period)
{
_db.MLFSReportingPeriods.Add(period);
await _db.SaveChangesAsync();
return(period.Id);
}
public async Task <List <IncomeReport> > IncomeReport(int[] periodId, bool byAdvisor = false, bool byOrganisation = false)
{
List <MLFSReportingPeriod> periods = new List <MLFSReportingPeriod>();
List <IncomeReport> report = new List <IncomeReport>();
for (int i = 0; i < periodId.Length; i++)
{
MLFSReportingPeriod period = await _periodData.GetPeriodById(periodId[i]);
periods.Add(period);
}
if (byAdvisor && !byOrganisation)
{
report = ViewModels.IncomeReport.CreateReportByAdvisor(periods);
}
else if (byOrganisation && !byAdvisor)
{
report = ViewModels.IncomeReport.CreateReportByOrganisation(periods);
}
else
{
report = ViewModels.IncomeReport.CreateFromList(periods.SelectMany(x => x.Receipts).ToList());
}
return(report);
}
public async Task <List <MLFSReportingPeriod> > GetCurrentPeriods()
{
MLFSReportingPeriod period = await _periodData.GetCurrent();
List <MLFSReportingPeriod> currentPeriods = await _periodData.GetLast12Months(period);
return(currentPeriods);
}
public async Task <int> Put([FromBody] MLFSReportingPeriod period)
{
if (period != null)
{
int periodId = await _periodData.InsertPeriod(period);
return(periodId);
}
return(0);
}
public async Task <List <MLFSIncome> > GetIncome(MLFSReportingPeriod period, int?advisorId = null)
{
if (advisorId == null)
{
return(await _db.MLFSIncome.Where(x => x.ReportingPeriodId == period.Id).Include(z => z.MLFSDebtorAdjustment).Include(y => y.Advisor).ToListAsync());
}
else
{
return(await _db.MLFSIncome.Where(x => x.ReportingPeriodId == period.Id && x.AdvisorId == advisorId).Include(z => z.MLFSDebtorAdjustment).Include(y => y.Advisor).ToListAsync());
}
}
public async Task <IActionResult> Create([Bind("Id,Month,Year")] MLFSReportingPeriod mLFSReportingPeriod)
{
if (ModelState.IsValid)
{
MLFSReportingPeriod period = new MLFSReportingPeriod(mLFSReportingPeriod.Month, mLFSReportingPeriod.Year);
await _periodData.InsertPeriod(period);
return(RedirectToAction(nameof(Index)));
}
return(View(mLFSReportingPeriod));
}
public async Task <MLFSReportingPeriod> GetCurrent()
{
DateTime lastMonth = DateTime.Now.AddMonths(-1);
MLFSReportingPeriod period = await _db.MLFSReportingPeriods.Where(x => x.Year == lastMonth.Year && x.Month == lastMonth.Month).FirstOrDefaultAsync();
if (period == null)
{
period = await _db.MLFSReportingPeriods.OrderByDescending(x => x.Year).ThenByDescending(y => y.ReportOrder).FirstOrDefaultAsync();
}
return(period);
}
// GET: Index
public async Task <IActionResult> Index(int?periodId)
{
if (periodId == null)
{
MLFSReportingPeriod current = await _periodData.GetCurrent();
periodId = current.Id;
}
ViewBag.ReportingPeriodId = await _periodData.SelectList(periodId);
return(View());
}
public async Task <MLFSReportingPeriod> Get(string id)
{
if (String.IsNullOrEmpty(id))
{
return(null);
}
if (int.TryParse(id, out int periodId))
{
MLFSReportingPeriod period = await _periodData.GetPeriodById(periodId);
return(period);
}
return(null);
}
public async Task <IActionResult> CheckForMatches()
{
MLFSReportingPeriod period = await _periodData.GetCurrent();
List <MLFSSale> debtors = await _debtorData.GetDebtors(period);
List <MLFSIncome> income = await _incomeData.GetIncome();
income = income.Where(x => x.MLFSDebtorAdjustment == null && x.IncomeType.Contains("Initial")).ToList();
List <MLFSDebtorAdjustment> adjs = MLFSSale.CheckForReceipts(debtors, income);
_adjustmentData.InsertList(adjs);
return(RedirectToAction("Index"));
}
public async Task <SelectList> SelectList(int?periodId = null)
{
List <MLFSReportingPeriod> periods = await GetPeriods();
if (periodId == null)
{
MLFSReportingPeriod period = await GetCurrent();
periodId = period.Id;
}
SelectList sList = new SelectList(periods.OrderByDescending(x => x.Year).ThenByDescending(y => y.ReportOrder), "Id", "Description", periodId);
return(sList);
}
public async Task <IActionResult> DirectorsReport(int?periodId)
{
if (periodId == null)
{
return(NotFound());
}
MLFSReportingPeriod period = await _periodData.GetPeriodById((int)periodId);
List <MLFSSale> sales = await _salesData.GetSales(period);
List <DirectorsReport> report = ViewModels.DirectorsReport.Create(sales.OrderBy(x => x.ClientName).ToList());
return(PartialView("_DirectorsReport", report.OrderBy(x => x.ClientId)));
}
public async Task <List <MLFSSale> > GetDebtors(MLFSReportingPeriod period)
{
List <MLFSSale> debtors = await _db.MLFSSales.Where(x => (x.ReportingPeriod.Year == period.Year && x.ReportingPeriod.Month <= period.Month) || x.ReportingPeriod.Year < period.Year)
.Include(y => y.Advisor)
.Include(x => x.Adjustments)
.ThenInclude(x => x.ReportingPeriod)
.Select(d => new MLFSSale()
{
Id = d.Id,
IOId = d.IOId,
IOReference = d.IOReference,
ReportingPeriodId = d.ReportingPeriodId,
ReportingPeriod = d.ReportingPeriod,
Organisation = d.Organisation,
ClientName = d.ClientName,
ClientId = d.ClientId,
JointClientId = d.JointClientId,
JointClientName = d.JointClientName,
AdvisorId = d.AdvisorId,
Advisor = d.Advisor,
ProviderName = d.ProviderName,
PlanType = d.PlanType,
IsNew = d.IsNew,
RelevantDate = d.RelevantDate,
NetAmount = d.NetAmount,
VAT = d.VAT,
Investment = d.Investment,
OnGoingPercentage = d.OnGoingPercentage,
PlanReference = d.PlanReference,
EstimatedOtherIncome = d.EstimatedOtherIncome,
Adjustments = d.Adjustments.Where(y => (y.ReportingPeriod.Year == period.Year && y.ReportingPeriod.Month <= period.Month) || y.ReportingPeriod.Year < period.Year).ToList()
})
.ToListAsync();
//foreach (MLFSSale debtor in debtors)
//{
// List<MLFSDebtorAdjustment> adjs = debtor.Adjustments.ToList();
// for(int i = 0; i < adjs.Count; i++)
// {
// MLFSDebtorAdjustment adj = adjs[i];
// if ((adj.ReportingPeriod.Year == period.Year && adj.ReportingPeriod.Month > period.Month) || adj.ReportingPeriod.Year > period.Year)
// {
// debtor.Adjustments.Remove(adj);
// }
// }
//}
debtors = debtors.Where(x => x.Outstanding != 0).ToList();
return(debtors);
}
// GET: MLFSSales/VAT
public async Task <IActionResult> VAT(int?periodId)
{
if (periodId == null)
{
return(NotFound());
}
MLFSReportingPeriod period = await _periodData.GetPeriodById((int)periodId);
if (period == null)
{
return(NotFound());
}
List <MLFSSale> sales = await _salesData.GetSales(period);
return(View("Index", sales.Where(x => x.VAT != 0)));
}
public static List <NewBusiness> CreateList(List <MLFSSale> sales, MLFSReportingPeriod period)
{
List <NewBusiness> reports = sales.GroupBy(x => x.Advisor).Select(y => new NewBusiness()
{
Period = period.Description,
PeriodId = period.Id,
Quarter = period.Quarter,
Advisor = y.Key.Fullname,
AdvisorId = y.Key.Id,
Organisation = y.Key.Department,
NewClients = y.Where(a => a.IsNew).Sum(z => z.NetAmount),
ExistingClients = y.Where(a => !a.IsNew).Sum(z => z.NetAmount),
Total = y.Sum(z => z.NetAmount)
}).ToList();
return(reports);
}
public async Task <IActionResult> IncomeOnly(Upload upload)
{
if (upload.ReportingPeriodId == null)
{
return(NotFound());
}
MLFSReportingPeriod period = await _periodData.GetPeriodById((int)upload.ReportingPeriodId);
if (period == null)
{
return(NotFound());
}
upload.ReportingPeriod = period;
upload.ReportingPeriodId = period.Id;
if (upload.Files == null || upload.Files.Count != 4)
{
return(NotFound());
}
List <MLFSAdvisor> advisors = await _advisorData.GetAdvisors();
DataTable incomeTable = new DataTable();
foreach (IFormFile file in upload.Files)
{
if (file.Length > 0)
{
string newFilePath = Path.GetTempFileName();
using (var fileStream = new FileStream(newFilePath, FileMode.Create))
{
await file.CopyToAsync(fileStream);
}
if (file.FileName.Contains("AdviserMonthlyFCI"))
{
incomeTable = Tools.ConvertCSVToDataTable(newFilePath);
}
}
}
upload.Income = MLFSIncome.CreateFromDataTable(incomeTable, advisors, period);
await _incomeData.InsertList(upload.Income);
List <MLFSDebtorAdjustment> adjs = MLFSSale.CheckForReceipts(upload.Sales, upload.Income);
_adjustmentData.InsertList(adjs);
ViewBag.Result = "Uploaded Successfully";
return(RedirectToAction("Index"));
}
public void AddPlanDataTest()
{
//arrange
MLFSReportingPeriod period = new MLFSReportingPeriod(1, 2020)
{
Id = 2
};
MLFSSale sale = new MLFSSale()
{
Id = 1,
Investment = 100,
ReportingPeriodId = period.Id,
ReportingPeriod = period,
NetAmount = 4,
OnGoingPercentage = (decimal)0.005,
RelevantDate = DateTime.Parse("01/02/2020")
};
DataTable table = new DataTable();
table.Columns.Add("Id", typeof(int));
table.Columns.Add("Provider.Name", typeof(string));
table.Columns.Add("Owner 1.Creation Date", typeof(string));
table.Columns.Add("Total Premiums to Date", typeof(string));
table.Columns.Add("On-going Fee Percentage", typeof(string));
table.Columns.Add("Selling Adviser.Group.Name", typeof(string));
DataRow row = table.NewRow();
row["Id"] = "123465";
row["Provider.Name"] = "Elevate";
row["Owner 1.Creation Date"] = "01/01/2020";
row["Total Premiums to Date"] = "10000";
row["On-going Fee Percentage"] = "0.01";
row["Selling Adviser.Group.Name"] = "Milsted Langdon Financial Services";
table.Rows.Add(row);
//act
sale.AddPlanData(row);
//assert
Assert.AreEqual(true, sale.IsNew, "Client is not reporting as new");
Assert.AreEqual((decimal)10000, sale.Investment, "Investment not updated");
}
public static List <SalesReport> CreateByCampaign(List <MLFSIncome> income, MLFSReportingPeriod period)
{
List <SalesReport> salesReports = income.Where(w => w.ReportingPeriodId == period.Id).GroupBy(x => x.Campaign).Select(y => new SalesReport()
{
Period = period.Description,
Advisor = y.Key,
Budget = 0,
New_Business = y.Where(a => a.IsNewBusiness && a.MLFSDebtorAdjustment != null).Sum(b => b.Amount),
Renewals = y.Where(a => !a.IsNewBusiness).Sum(b => b.Amount),
Unallocated = y.Where(a => a.IsNewBusiness && a.MLFSDebtorAdjustment == null).Sum(b => b.Amount),
Clawback = y.Where(a => a.IsClawBack).Sum(b => b.Amount),
NotTakenUp = 0,
Adjustment = 0,
Debtors_Adjustment = 0,
Debtors_Variance = 0
}).ToList();
return(salesReports);
}
public async Task <IActionResult> Update(int?advisorId, int?reportingPeriodId, string value)
{
if (reportingPeriodId == null)
{
return(NotFound());
}
if (advisorId == null)
{
return(NotFound());
}
if (decimal.TryParse(value, out decimal budget))
{
MLFSReportingPeriod period = await _periodData.GetPeriodById((int)reportingPeriodId);
if (period == null)
{
return(NotFound());
}
List <MLFSBudget> budgets = await _budgetData.GetBudgets(period);
MLFSBudget b = budgets.Where(x => x.AdvisorId == advisorId).FirstOrDefault();
if (b == null)
{
b = new MLFSBudget()
{
AdvisorId = (int)advisorId,
Budget = budget,
ReportingPeriodId = period.Id
};
_budgetData.Insert(b);
}
else
{
b.Budget = budget;
_budgetData.Update(b);
}
return(Ok());
}
else
{
return(NotFound());
}
}
public async Task <IActionResult> UpdateData(int?periodId, int?saleId = null)
{
List <MLFSSale> sales = new List <MLFSSale>();
if (saleId == null)
{
if (periodId == null)
{
return(NotFound());
}
MLFSReportingPeriod period = await _periodData.GetPeriodById((int)periodId);
if (period == null)
{
return(NotFound());
}
sales = await _salesData.GetSales(period);
}
else
{
MLFSSale soleSale = await _salesData.GetSaleById((int)saleId);
sales.Add(soleSale);
}
for (int i = 0; i < sales.Count; i++)
{
MLFSSale sale = sales[i];
//only interested in clients which haven't been updated already or where we are forcing a particular line
//if (saleId != null || (sale.EstimatedOtherIncome == 0))
//{
Console.WriteLine("Client: " + sale.ClientId + ":" + sale.Client + ":" + i.ToString());
MLFSClient client = await _clientData.GetClient(sale.ClientId);
List <MLFSIncome> income = await _incomeData.GetIncome(client);
sale.AddClientData(client, income);
_salesData.Update(sale);
//}
}
return(Ok());
}
// GET: MLFSSales
public async Task <IActionResult> Index(int?periodId, int?advisorId = null)
{
if (periodId == null)
{
return(NotFound());
}
MLFSReportingPeriod period = await _periodData.GetPeriodById((int)periodId);
if (period == null)
{
return(NotFound());
}
List <MLFSSale> sales = await _salesData.GetSales(period);
if (advisorId != null)
{
sales = sales.Where(x => x.AdvisorId == advisorId).ToList();
}
return(View(sales));
}
public async Task <IActionResult> NotTakenUp(int?debtorId)
{
if (debtorId == null)
{
return(NotFound());
}
MLFSSale mLFSSale = await _salesData.GetSaleById((int)debtorId);
if (mLFSSale == null)
{
return(NotFound());
}
MLFSReportingPeriod period = await _periodData.GetCurrent();
MLFSDebtorAdjustment adj = mLFSSale.CreateNTU(period);
_adjustmentData.Insert(adj);
return(RedirectToAction("Index", "Debtor"));
}
public IActionResult Edit(int id, [Bind("Id,Description,Month,Year,FinancialYear,ReportOrder")] MLFSReportingPeriod mLFSReportingPeriod)
{
if (id != mLFSReportingPeriod.Id)
{
return(NotFound());
}
if (ModelState.IsValid)
{
try
{
_periodData.Update(mLFSReportingPeriod);
}
catch (DbUpdateConcurrencyException)
{
throw;
}
return(RedirectToAction(nameof(Index)));
}
return(View(mLFSReportingPeriod));
}
public async Task UpdateClientOnboardDate(MLFSReportingPeriod period)
{
List <MLFSIncome> incomeLines = await _db.MLFSIncome.Where(x => x.ReportingPeriodId == period.Id && x.ClientOnBoardDate == null).ToListAsync();
incomeLines = incomeLines.Where(x => x.IsNewBusiness).ToList();
if (incomeLines.Count > 0)
{
string[] ids = incomeLines.Select(x => x.ClientId).ToArray();
while (ids.Length != 0)
{
string[] idsForSubmission;
string idString = "";
if (ids.Length > 100)
{
idsForSubmission = ids.Take(100).ToArray();
}
else
{
idsForSubmission = ids;
}
foreach (string id in idsForSubmission)
{
if (!String.IsNullOrEmpty(id))
{
idString += id + ",";
}
}
idString = idString.TrimEnd(',');
idString = "(" + idString + ")";
List <MLFSClient> clients = await _clientData.GetClients(idString);
if (clients != null)
{
MLFSIncome.UpdateFromIO(incomeLines, clients);
}
ids = ids.Except(idsForSubmission).ToArray();
}
}
}
public async Task <IActionResult> Index(Upload upload)
{
if (upload.ReportingPeriodId == null)
{
return(NotFound());
}
MLFSReportingPeriod period = await _periodData.GetPeriodById((int)upload.ReportingPeriodId);
if (period == null)
{
return(NotFound());
}
upload.ReportingPeriod = period;
upload.ReportingPeriodId = period.Id;
if (upload.Files == null || upload.Files.Count != 4)
{
return(NotFound());
}
List <MLFSAdvisor> advisors = await _advisorData.GetAdvisors();
string response = await upload.CreateEntities(advisors);
if (response != "Success")
{
return(NotFound());
}
await _salesData.InsertList(upload.Sales);
await _incomeData.InsertList(upload.Income);
List <MLFSDebtorAdjustment> adjs = MLFSSale.CheckForReceipts(upload.Sales, upload.Income);
_adjustmentData.InsertList(adjs);
ViewBag.Result = "Uploaded Successfully";
return(RedirectToAction("Index"));
}
public async Task <ActionResult> PostMonthlyData(IFormFile salesCSV, IFormFile planCSV, IFormFile fciCSV, string periodId)
{
//check we have a valid period
if (periodId == null || !int.TryParse(periodId, out int pId))
{
return(BadRequest());
}
//Get the period we are using
MLFSReportingPeriod period = await _periodData.GetPeriodById(pId);
if (period == null)
{
return(BadRequest());
}
//convert our csvs into datatables
string newSalesFilePath = Path.GetTempFileName();
string newPlanFilePath = Path.GetTempFileName();
string newFCIFilePath = Path.GetTempFileName();
if (salesCSV.Length > 0)
{
using (var fileStream = new FileStream(newSalesFilePath, FileMode.Create))
{
await salesCSV.CopyToAsync(fileStream);
}
}
else
{
return(NotFound());
}
if (planCSV.Length > 0)
{
using (var fileStream = new FileStream(newPlanFilePath, FileMode.Create))
{
await planCSV.CopyToAsync(fileStream);
}
}
else
{
return(NotFound());
}
if (fciCSV.Length > 0)
{
using (var fileStream = new FileStream(newFCIFilePath, FileMode.Create))
{
await fciCSV.CopyToAsync(fileStream);
}
}
else
{
return(NotFound());
}
DataTable feeDt = Tools.ConvertCSVToDataTable(newSalesFilePath);
DataTable planDt = Tools.ConvertCSVToDataTable(newPlanFilePath);
DataTable fciDt = Tools.ConvertCSVToDataTable(newFCIFilePath);
//load data to database and get response
await _salesData.UploadSalesForPeriod(period, feeDt, planDt);
List <MLFSIncome> receipts = await _incomeData.UploadIncomeForPeriod(period, fciDt);
await _incomeData.UpdateClientOnboardDate(period);
//Allocate Receipts
List <MLFSSale> debtors = await _salesData.GetDebtors();
List <MLFSDebtorAdjustment> adjs = MLFSSale.CheckForReceipts(debtors, receipts.Where(x => x.IncomeType.Contains("Initial")).ToList());
_adjData.InsertList(adjs);
return(Ok());
}
private List <MLFSReportingPeriod> MockEntries()
{
List <MLFSReportingPeriod> periods = new List <MLFSReportingPeriod>();
for (int i = 1; i < 4; i++)
{
MLFSReportingPeriod period = new MLFSReportingPeriod(i, 2020)
{
Id = i
};
periods.Add(period);
}
MLFSAdvisor advisor = new MLFSAdvisor()
{
Id = 1,
FirstName = "Geoff",
LastName = "Smith"
};
List <MLFSIncome> income = new List <MLFSIncome>();
MLFSIncome inc = new MLFSIncome()
{
Id = 1,
IOReference = "123456",
Advisor = advisor,
AdvisorId = advisor.Id,
Organisation = "FPP",
ReportingPeriodId = periods[0].Id,
ReportingPeriod = periods[0],
RelevantDate = DateTime.Now,
ClientOnBoardDate = DateTime.Now.AddMonths(-2),
ClientId = "234",
Amount = 100,
PlanNumber = "9876"
};
income.Add(inc);
MLFSIncome inc2 = new MLFSIncome()
{
Id = 2,
IOReference = "1234567",
Advisor = advisor,
AdvisorId = advisor.Id,
Organisation = "MLFS",
ReportingPeriodId = periods[0].Id,
ReportingPeriod = periods[0],
RelevantDate = DateTime.Now,
ClientOnBoardDate = DateTime.Now.AddMonths(-2),
ClientId = "345",
Amount = 100,
PlanNumber = "9877"
};
income.Add(inc2);
List <MLFSBudget> budgets = new List <MLFSBudget>();
MLFSBudget budget = new MLFSBudget()
{
Id = 9,
Advisor = advisor,
AdvisorId = advisor.Id,
Budget = (decimal)20000,
ReportingPeriodId = periods[0].Id,
ReportingPeriod = periods[0]
};
budgets.Add(budget);
periods[0].Receipts = income;
periods[0].Budgets = budgets;
return(periods);
}
