public frmMSVariationReport(List <Model.VariationByBank> variationByBanks, DateTime dtPrevious, DateTime dtCurrent)
{
InitializeComponent();
try
{
List <Model.VariationByBank> lst = new List <Model.VariationByBank>();
Model.VariationByBank data = new Model.VariationByBank();
foreach (var d in variationByBanks)
{
if (d.BankName.ToLower() == "total")
{
continue;
}
foreach (var nric in d.UnpaidNRIC.Split(','))
{
data = new Model.VariationByBank();
data.BankName = d.BankName;
data.NoOfMemberCurrent = d.NoOfMemberCurrent;
data.NoOfMemberPrevious = d.NoOfMemberPrevious;
data.TotalAmountPrevious = d.TotalAmountPrevious;
data.TotalAmountCurrent = d.TotalAmountCurrent;
data.Different = d.Different;
data.Unpaid = d.Unpaid;
data.NewPaid = d.NewPaid;
data.NRIC = nric;
var dPre = db.MonthlySubscriptionMembers.FirstOrDefault(x => x.MonthlySubscriptionBankId == d.MSBankIdPrevious && x.NRIC == nric);
if (dPre != null)
{
data.VarStatus = dPre.MASTERMEMBER?.RESIGNED == 1?"Resigned Members": "Previous Subscription Paid - Current Subscription Unpaid";
data.Membername = dPre.MemberName;
data.Amount = 0;
data.AmountPrevious = dPre.Amount;
data.AmountDifferent = data.Amount - data.AmountPrevious;
data.MSStatus = dPre.MonthlySubscriptionMemberStatu.Status;
lst.Add(data);
}
}
foreach (var nric in d.NewPaidNRIC.Split(','))
{
data = new Model.VariationByBank();
data.BankName = d.BankName;
data.NoOfMemberCurrent = d.NoOfMemberCurrent;
data.NoOfMemberPrevious = d.NoOfMemberPrevious;
data.TotalAmountPrevious = d.TotalAmountPrevious;
data.TotalAmountCurrent = d.TotalAmountCurrent;
data.Different = d.Different;
data.Unpaid = d.Unpaid;
data.NewPaid = d.NewPaid;
data.NRIC = nric;
var dPre = db.MonthlySubscriptionMembers.FirstOrDefault(x => x.MonthlySubscriptionBankId == d.MSBankIdCureent && x.NRIC == nric);
if (dPre != null)
{
var doj = dPre.MASTERMEMBER?.DATEOFJOINING ?? DateTime.Now;
data.VarStatus = doj.Year == dtPrevious.Year && doj.Month == dtPrevious.Month?$"New Join on {dtPrevious:MMM yyyy}" : "Previous Subscription Unpaid - Current Subscription Paid";
data.Membername = dPre.MemberName;
data.Amount = dPre.Amount;
data.AmountPrevious = 0;
data.AmountDifferent = data.Amount - data.AmountPrevious;
data.MSStatus = dPre.MonthlySubscriptionMemberStatu.Status;
lst.Add(data);
}
}
try
{
var lstPrevious = db.MonthlySubscriptionMembers.Where(x => x.MonthlySubscriptionBankId == d.MSBankIdPrevious);
var lstCurrent = db.MonthlySubscriptionMembers.Where(x => x.MonthlySubscriptionBankId == d.MSBankIdCureent);
var lstMismatch = lstPrevious.Join(lstCurrent, x => x.NRIC, x => x.NRIC, (x, y) => new { NRIC = x.NRIC, MemberName = x.MemberName, AmountPrevious = x.Amount, AmountCurrent = y.Amount }).Where(x => x.AmountPrevious != x.AmountCurrent).ToList();
foreach (var mm in lstMismatch)
{
data = new Model.VariationByBank();
data.BankName = d.BankName;
data.NoOfMemberCurrent = d.NoOfMemberCurrent;
data.NoOfMemberPrevious = d.NoOfMemberPrevious;
data.TotalAmountPrevious = d.TotalAmountPrevious;
data.TotalAmountCurrent = d.TotalAmountCurrent;
data.Different = d.Different;
data.Unpaid = d.Unpaid;
data.NewPaid = d.NewPaid;
data.NRIC = mm.NRIC;
data.VarStatus = mm.AmountCurrent < mm.AmountPrevious ? $"Subscription Decrement" : "Subscription Increment";
data.Membername = mm.MemberName;
data.Amount = mm.AmountCurrent;
data.AmountPrevious = mm.AmountPrevious;
data.AmountDifferent = data.Amount - data.AmountPrevious;
lst.Add(data);
}
}
catch (Exception ex) { }
}
rptViewer.SetDisplayMode(Microsoft.Reporting.WinForms.DisplayMode.PrintLayout);
rptViewer.Reset();
rptViewer.LocalReport.DataSources.Add(new ReportDataSource("VariationByBank", lst));
rptViewer.LocalReport.ReportEmbeddedResource = "Nube.Reports.rptMSVariationReport.rdlc";
List <ReportParameter> reportParameters = new List <ReportParameter>();
reportParameters.Add(new ReportParameter("Title", $"NUBE Monthly Subscription {dtCurrent:MMM yyyy} - Variation Report"));
reportParameters.Add(new ReportParameter("dtPrevious", $"{dtPrevious:MMM yyyy}"));
reportParameters.Add(new ReportParameter("dtCurrent", $"{dtCurrent:MMM yyyy}"));
rptViewer.LocalReport.SetParameters(reportParameters);
rptViewer.RefreshReport();
}
catch (Exception ex)
{
}
}
void LoadMonthlySubsciption(DateTime dt)
{
db = new nubebfsEntity();
this.Title = $"Monthly Subscription [ {dt.ToString("MMMM - yyyy")} ]";
dataMS = db.MonthlySubscriptions.FirstOrDefault(x => x.date == dt);
if (dataMS?.MonthEndClosed == true)
{
stpFileSelect.Visibility = Visibility.Collapsed;
}
else
{
stpFileSelect.Visibility = Visibility.Visible;
}
if (dataMS != null)
{
try
{
var lstBank = dataMS.MonthlySubscriptionBanks.Select(x => new
Model.MonthlySubsBank {
Id = x.Id,
BankName = x.MASTERBANK.BANK_NAME,
NoOfMember = x.MonthlySubscriptionMembers.Count(),
TotalAmount = x.MonthlySubscriptionMembers.Sum(y => y.Amount),
ActiveAmount = x.MonthlySubscriptionMembers.Where(y => y.MonthlySubscriptionMemberStatu?.Id == (int)AppLib.MonthlySubscriptionMemberStatus.Active).Sum(z => z.Amount),
DefaulterAmount = x.MonthlySubscriptionMembers.Where(y => y.MonthlySubscriptionMemberStatu?.Id == (int)AppLib.MonthlySubscriptionMemberStatus.Defaulter).Sum(z => z.Amount),
StruckOffAmount = x.MonthlySubscriptionMembers.Where(y => y.MonthlySubscriptionMemberStatu?.Id == (int)AppLib.MonthlySubscriptionMemberStatus.StruckOff).Sum(z => z.Amount),
ResignedAmount = x.MonthlySubscriptionMembers.Where(y => y.MonthlySubscriptionMemberStatu?.Id == (int)AppLib.MonthlySubscriptionMemberStatus.Resigned).Sum(z => z.Amount),
SundryCreditorAmount = x.MonthlySubscriptionMembers.Where(y => y.MonthlySubscriptionMemberStatu?.Id == (int)AppLib.MonthlySubscriptionMemberStatus.SundryCreditor).Sum(z => z.Amount)
}).ToList();
var b = new Model.MonthlySubsBank
{
BankName = "Total",
NoOfMember = lstBank.Sum(x => x.NoOfMember),
TotalAmount = lstBank.Sum(x => x.TotalAmount),
ActiveAmount = lstBank.Sum(x => x.ActiveAmount),
DefaulterAmount = lstBank.Sum(x => x.DefaulterAmount),
StruckOffAmount = lstBank.Sum(x => x.StruckOffAmount),
ResignedAmount = lstBank.Sum(x => x.ResignedAmount),
SundryCreditorAmount = lstBank.Sum(x => x.SundryCreditorAmount)
};
lstBank.Add(b);
dgvBank.ItemsSource = lstBank;
lblPaidMembers.Text = dataMS.MonthlySubscriptionBanks.Sum(x => x.MonthlySubscriptionMembers.Count()).ToString();
lblPaidAmount.Text = dataMS.MonthlySubscriptionBanks.Sum(x => x.MonthlySubscriptionMembers.Sum(y => y.Amount)).ToString("N2");
}
catch (Exception ex)
{
}
try
{
var lstMemberStatus = db.MonthlySubscriptionMemberStatus.ToList().Select(x => new Model.MonthlySubsMemberStatus()
{
Description = x.Status,
NoOfMember = x.MonthlySubscriptionMembers.Count(y => y.MonthlySubscriptionBank.MonthlySubscriptionId == dataMS.Id),
Amount = x.MonthlySubscriptionMembers.Where(y => y.MonthlySubscriptionBank.MonthlySubscriptionId == dataMS.Id).Sum(z => z.Amount),
}).ToList();
var mStatus = new Model.MonthlySubsMemberStatus()
{
Description = "Total",
NoOfMember = lstMemberStatus.Sum(x => x.NoOfMember),
Amount = lstMemberStatus.Sum(x => x.Amount),
};
lstMemberStatus.Add(mStatus);
dgvMemberStatus.ItemsSource = lstMemberStatus;
}
catch (Exception ex)
{
}
try
{
var lstMemberMatching = db.MonthlySubscriptionMatchingTypes.ToList().Select(x => new Model.MonthlySubsApprovalStatus()
{
Description = x.Name,
NoOfMember = x.MonthlySubscriptionMemberMatchingResults.Count(y => y.MonthlySubscriptionMember?.MonthlySubscriptionBank?.MonthlySubscriptionId == dataMS.Id),
Approved = x.MonthlySubscriptionMemberMatchingResults.Count(y => y.MonthlySubscriptionMember?.MonthlySubscriptionBank?.MonthlySubscriptionId == dataMS.Id && y.ApprovedBy != null),
Pending = x.MonthlySubscriptionMemberMatchingResults.Count(y => y.MonthlySubscriptionMember?.MonthlySubscriptionBank?.MonthlySubscriptionId == dataMS.Id && y.ApprovedBy == null),
}).ToList();
var aStatus = new Model.MonthlySubsApprovalStatus()
{
Description = "Total",
NoOfMember = lstMemberMatching.Sum(x => x.NoOfMember),
Approved = lstMemberMatching.Sum(x => x.Approved),
Pending = lstMemberMatching.Sum(x => x.Pending),
};
lstMemberMatching.Add(aStatus);
dgvMemberMatching.ItemsSource = lstMemberMatching;
btnMonthEndClose.IsEnabled = lstMemberMatching.Sum(x => x.Pending) == 0 && lstMemberMatching.Sum(x => x.NoOfMember) != 0;
var lstBankCurrent = dataMS.MonthlySubscriptionBanks.Select(x => new
Model.MonthlySubsBank
{
Id = x.Id,
BankName = x.MASTERBANK.BANK_NAME,
NoOfMember = x.MonthlySubscriptionMembers.Count(),
TotalAmount = x.MonthlySubscriptionMembers.Sum(y => y.Amount)
});
var dtPre = dataMS.date.AddMonths(-1);
var lstBankPrevious = db.MonthlySubscriptionBanks.Where(x => x.MonthlySubscription.date == dtPre).Select(x => new
Model.MonthlySubsBank
{
Id = x.Id,
BankName = x.MASTERBANK.BANK_NAME,
NoOfMember = x.MonthlySubscriptionMembers.Count(),
TotalAmount = x.MonthlySubscriptionMembers.Sum(y => y.Amount)
});
List <Model.VariationByBank> variationByBanks = new List <Model.VariationByBank>();
Model.VariationByBank vb;
foreach (var d in lstBankCurrent)
{
var dPre = lstBankPrevious.FirstOrDefault(x => x.BankName == d.BankName);
vb = new Model.VariationByBank();
vb.MSBankIdPrevious = dPre?.Id ?? 0;
vb.MSBankIdCureent = d.Id;
var lstNRIC_Current = db.MonthlySubscriptionMembers.Where(x => x.MonthlySubscriptionBankId == vb.MSBankIdCureent).Select(x => x.NRIC).ToList();
var lstNRIC_Previous = db.MonthlySubscriptionMembers.Where(x => x.MonthlySubscriptionBankId == vb.MSBankIdPrevious).Select(x => x.NRIC).ToList();
var lstNewPaidNRIC = lstNRIC_Current.Except(lstNRIC_Previous);
var lstUnpaidNRIC = lstNRIC_Previous.Except(lstNRIC_Current);
vb.BankName = d.BankName;
vb.NoOfMemberCurrent = d.NoOfMember;
vb.NoOfMemberPrevious = dPre?.NoOfMember ?? 0;
vb.TotalAmountCurrent = d.TotalAmount;
vb.TotalAmountPrevious = dPre?.TotalAmount ?? 0;
vb.Different = vb.NoOfMemberCurrent - vb.NoOfMemberPrevious;
vb.NewPaid = lstNewPaidNRIC.Count(); // getNotContainCount( lstNRIC_Previous, lstNRIC_Current);
vb.Unpaid = lstUnpaidNRIC.Count(); // getNotContainCount(lstNRIC_Current, lstNRIC_Previous);
vb.NewPaidNRIC = string.Join(",", lstNewPaidNRIC);
vb.UnpaidNRIC = string.Join(",", lstUnpaidNRIC);
if (vb.Different != 0 || vb.Unpaid != 0 || vb.NewPaid != 0)
{
variationByBanks.Add(vb);
}
}
vb = new Model.VariationByBank();
vb.BankName = "Total";
vb.NoOfMemberCurrent = variationByBanks.Sum(x => x.NoOfMemberCurrent);
vb.NoOfMemberPrevious = variationByBanks.Sum(x => x.NoOfMemberPrevious);
vb.Different = variationByBanks.Sum(x => x.Different);
vb.Unpaid = variationByBanks.Sum(x => x.Unpaid);
vb.NewPaid = variationByBanks.Sum(x => x.NewPaid);
variationByBanks.Add(vb);
dgvBankVar.ItemsSource = variationByBanks;
}
catch (Exception ex)
{
}
}
else
{
dgvBank.ItemsSource = null;
dgvMemberStatus.ItemsSource = null;
dgvMemberMatching.ItemsSource = null;
dgvBankVar.ItemsSource = null;
lblPaidAmount.Text = "";
lblPaidMembers.Text = "";
}
}
