private void DgvBank_MouseDoubleClick(object sender, MouseButtonEventArgs e) { try { Model.MonthlySubsBank d = dgvBank.SelectedItem as Model.MonthlySubsBank; if (data != null) { if (data.MonthlySubscription.Id != 0) { frmMonthlySubscriptionMembers f = new frmMonthlySubscriptionMembers(data.MonthlySubscription.Id); f.cbxBank.Text = d.BankName; f.Search(); f.ShowDialog(); } } } 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 = ""; } }