public MonthlySubs()
{
DB = new nubebfsEntity();
selectedDate = DateTime.Now.Date;
}
public MonthlySubs(DateTime dt)
{
DB = new nubebfsEntity();
selectedDate = dt;
}
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 = "";
}
}