void MemberStatusUpdate(MonthlySubscriptionMember msMember) { try { var mm = db.MASTERMEMBERs.Where(x => x.ICNO_OLD == msMember.NRIC || x.ICNO_NEW == msMember.NRIC || x.NRIC_ByBank == msMember.NRIC).OrderByDescending(x => x.MEMBER_CODE).Select(x => new { x.MEMBER_CODE, x.STATUS_CODE }).FirstOrDefault(); if (mm == null) { msMember.MonthlySubcriptionMemberStatusId = (int)AppLib.MonthlySubscriptionMemberStatus.SundryCreditor; msMember.MemberCode = null; } else { msMember.MonthlySubcriptionMemberStatusId = (int)mm.STATUS_CODE.Value; msMember.MemberCode = mm.MEMBER_CODE; } } catch (Exception ex) { } }
MonthlySubscriptionMemberMatchingResult getMonthlySubscriptionMemberMatchingResult(MonthlySubscriptionMember msMember, AppLib.MonthlySubscriptionMatchingType type) { return(db.MonthlySubscriptionMemberMatchingResults.FirstOrDefault(x => x.MonthlySubscriptionMemberId == msMember.Id && x.MonthlySubscriptionMatchingTypeId == (int)type)); }
void ApprovalStatusUpdate(MonthlySubscriptionMember msMember) { try { var mm = db.MASTERMEMBERs.Select(x => new { x.MEMBER_CODE, x.STATUS_CODE, x.BANK_CODE, x.MASTERBANK.BANK_NAME, x.MEMBER_NAME, x.ICNO_NEW, x.ICNO_OLD, x.NRIC_ByBank }).OrderByDescending(x => x.MEMBER_CODE).FirstOrDefault(x => x.ICNO_OLD == msMember.NRIC || x.ICNO_NEW == msMember.NRIC || x.NRIC_ByBank == msMember.NRIC); if (mm == null) { try { var msmmr = getMonthlySubscriptionMemberMatchingResult(msMember, AppLib.MonthlySubscriptionMatchingType.NRICNotMatched); if (msmmr == null) { msmmr = new MonthlySubscriptionMemberMatchingResult() { MonthlySubscriptionMemberId = msMember.Id, MonthlySubscriptionMatchingTypeId = (int)AppLib.MonthlySubscriptionMatchingType.NRICNotMatched, }; db.MonthlySubscriptionMemberMatchingResults.Add(msmmr); } msmmr.Description = $""; db.MonthlySubscriptionMemberMatchingResults.RemoveRange(db.MonthlySubscriptionMemberMatchingResults.Where(x => x.MonthlySubscriptionMemberId == msMember.Id && x.MonthlySubscriptionMatchingTypeId != (int)AppLib.MonthlySubscriptionMatchingType.NRICNotMatched)); db.SaveChanges(); } catch (Exception ex) { } } else { try { var msmmr = getMonthlySubscriptionMemberMatchingResult(msMember, AppLib.MonthlySubscriptionMatchingType.NRICNotMatched); if (msmmr != null) { db.MonthlySubscriptionMemberMatchingResults.Remove(msmmr); } db.SaveChanges(); msmmr = getMonthlySubscriptionMemberMatchingResult(msMember, AppLib.MonthlySubscriptionMatchingType.NRICMatched); if (msMember.NRIC == mm.ICNO_NEW) { if (msmmr == null) { msmmr = new MonthlySubscriptionMemberMatchingResult() { MonthlySubscriptionMemberId = msMember.Id, MonthlySubscriptionMatchingTypeId = (int)AppLib.MonthlySubscriptionMatchingType.NRICMatched, }; db.MonthlySubscriptionMemberMatchingResults.Add(msmmr); } msmmr.Description = $""; msmmr.ApprovedBy = AppLib.iUserCode; } else if (msmmr != null) { db.MonthlySubscriptionMemberMatchingResults.Remove(msmmr); } db.SaveChanges(); msmmr = getMonthlySubscriptionMemberMatchingResult(msMember, AppLib.MonthlySubscriptionMatchingType.NRICOldMatched); if (msMember.NRIC != mm.ICNO_NEW && msMember.NRIC == mm.ICNO_OLD) { if (msmmr == null) { msmmr = new MonthlySubscriptionMemberMatchingResult() { MonthlySubscriptionMemberId = msMember.Id, MonthlySubscriptionMatchingTypeId = (int)AppLib.MonthlySubscriptionMatchingType.NRICOldMatched, }; db.MonthlySubscriptionMemberMatchingResults.Add(msmmr); } msmmr.Description = $""; msmmr.ApprovedBy = AppLib.iUserCode; } else if (msmmr != null) { db.MonthlySubscriptionMemberMatchingResults.Remove(msmmr); } db.SaveChanges(); msmmr = getMonthlySubscriptionMemberMatchingResult(msMember, AppLib.MonthlySubscriptionMatchingType.NRICByBankMatched); if (msMember.NRIC != mm.ICNO_NEW && msMember.NRIC != mm.ICNO_OLD && msMember.NRIC == mm.NRIC_ByBank) { if (msmmr == null) { msmmr = new MonthlySubscriptionMemberMatchingResult() { MonthlySubscriptionMemberId = msMember.Id, MonthlySubscriptionMatchingTypeId = (int)AppLib.MonthlySubscriptionMatchingType.NRICByBankMatched, }; db.MonthlySubscriptionMemberMatchingResults.Add(msmmr); } msmmr.Description = $""; msmmr.ApprovedBy = AppLib.iUserCode; } else if (msmmr != null) { db.MonthlySubscriptionMemberMatchingResults.Remove(msmmr); } db.SaveChanges(); msmmr = getMonthlySubscriptionMemberMatchingResult(msMember, AppLib.MonthlySubscriptionMatchingType.StruckOffMembers); if (msMember.MonthlySubcriptionMemberStatusId == (int)AppLib.MonthlySubscriptionMemberStatus.StruckOff) { if (msmmr == null) { msmmr = new MonthlySubscriptionMemberMatchingResult() { MonthlySubscriptionMemberId = msMember.Id, MonthlySubscriptionMatchingTypeId = (int)AppLib.MonthlySubscriptionMatchingType.StruckOffMembers, }; db.MonthlySubscriptionMemberMatchingResults.Add(msmmr); } msmmr.Description = $""; } else if (msmmr != null) { db.MonthlySubscriptionMemberMatchingResults.Remove(msmmr); } db.SaveChanges(); msmmr = getMonthlySubscriptionMemberMatchingResult(msMember, AppLib.MonthlySubscriptionMatchingType.ResignedMembers); if (msMember.MonthlySubcriptionMemberStatusId == (int)AppLib.MonthlySubscriptionMemberStatus.Resigned) { if (msmmr == null) { msmmr = new MonthlySubscriptionMemberMatchingResult() { MonthlySubscriptionMemberId = msMember.Id, MonthlySubscriptionMatchingTypeId = (int)AppLib.MonthlySubscriptionMatchingType.ResignedMembers, }; db.MonthlySubscriptionMemberMatchingResults.Add(msmmr); } msmmr.Description = $""; } else if (msmmr != null) { db.MonthlySubscriptionMemberMatchingResults.Remove(msmmr); } db.SaveChanges(); msmmr = getMonthlySubscriptionMemberMatchingResult(msMember, AppLib.MonthlySubscriptionMatchingType.MismatchedMemberName); if (msMember.MemberName.ToLower() != mm.MEMBER_NAME.ToLower()) { if (msmmr == null) { msmmr = new MonthlySubscriptionMemberMatchingResult() { MonthlySubscriptionMemberId = msMember.Id, MonthlySubscriptionMatchingTypeId = (int)AppLib.MonthlySubscriptionMatchingType.MismatchedMemberName, }; db.MonthlySubscriptionMemberMatchingResults.Add(msmmr); } msmmr.Description = $"From Bank : {msMember.MemberName}\r\nFrom NUBE : {mm.MEMBER_NAME}"; } else if (msmmr != null) { db.MonthlySubscriptionMemberMatchingResults.Remove(msmmr); } db.SaveChanges(); msmmr = getMonthlySubscriptionMemberMatchingResult(msMember, AppLib.MonthlySubscriptionMatchingType.MismatchedBank); if (msMember.MonthlySubscriptionBank.BankCode != mm.BANK_CODE) { var mb = db.MASTERBANKs.FirstOrDefault(x => x.BANK_CODE == mm.BANK_CODE); if (msMember.MonthlySubscriptionBank.BankCode != mb.HEADER_BANK_CODE) { if (msmmr == null) { msmmr = new MonthlySubscriptionMemberMatchingResult() { MonthlySubscriptionMemberId = msMember.Id, MonthlySubscriptionMatchingTypeId = (int)AppLib.MonthlySubscriptionMatchingType.MismatchedBank, }; db.MonthlySubscriptionMemberMatchingResults.Add(msmmr); } msmmr.Description = $"From Bank : {msMember.MonthlySubscriptionBank.MASTERBANK.BANK_NAME}\r\nFrom NUBE : {mm.BANK_NAME}"; } else if (msmmr != null) { db.MonthlySubscriptionMemberMatchingResults.Remove(msmmr); } } else if (msmmr != null) { db.MonthlySubscriptionMemberMatchingResults.Remove(msmmr); } db.SaveChanges(); var dt = MonthlySubsDate.AddMonths(-1); var msMemberPre = db.MonthlySubscriptionMembers.FirstOrDefault(x => x.NRIC == msMember.NRIC && x.MonthlySubscriptionBank.MonthlySubscription.date == dt); msmmr = getMonthlySubscriptionMemberMatchingResult(msMember, AppLib.MonthlySubscriptionMatchingType.PreviousSubscriptionUnpaid); if (msMemberPre == null) { if (msmmr == null) { msmmr = new MonthlySubscriptionMemberMatchingResult() { MonthlySubscriptionMemberId = msMember.Id, MonthlySubscriptionMatchingTypeId = (int)AppLib.MonthlySubscriptionMatchingType.PreviousSubscriptionUnpaid, }; db.MonthlySubscriptionMemberMatchingResults.Add(msmmr); } msmmr.Description = $""; } else if (msmmr != null) { db.MonthlySubscriptionMemberMatchingResults.Remove(msmmr); } msmmr = getMonthlySubscriptionMemberMatchingResult(msMember, AppLib.MonthlySubscriptionMatchingType.MismatchedPreviousSubscription); if (msMemberPre?.Amount != null && msMember.Amount != msMemberPre.Amount) { if (msmmr == null) { msmmr = new MonthlySubscriptionMemberMatchingResult() { MonthlySubscriptionMemberId = msMember.Id, MonthlySubscriptionMatchingTypeId = (int)AppLib.MonthlySubscriptionMatchingType.MismatchedPreviousSubscription, }; db.MonthlySubscriptionMemberMatchingResults.Add(msmmr); } msmmr.Description = $"Previous Subscription: {msMemberPre.Amount}\r\nCurrent Subscription : {msMember.Amount}"; } else if (msmmr != null) { db.MonthlySubscriptionMemberMatchingResults.Remove(msmmr); } } catch (Exception ex) { } } } catch (Exception ex) { } }
private void BtnBrowse_Click(object sender, RoutedEventArgs e) { try { var bank = cbxBank.SelectedItem as MASTERBANK; var fileType = cbxFileType.SelectedItem as MonthlySubscriptonFileType; if (bank == null) { MessageBox.Show("Select Bank"); cbxBank.Focus(); } else if (fileType == null) { MessageBox.Show("Select file type"); cbxFileType.Focus(); } else if (cdrMonthlySubscription.SelectedDate == null) { MessageBox.Show("Select Month"); cdrMonthlySubscription.Focus(); } else { OpenFileDialog ofd = new OpenFileDialog(); if (ofd.ShowDialog() == true) { var dt = cdrMonthlySubscription.SelectedDate.Value; var ms = db.MonthlySubscriptions.FirstOrDefault(x => x.date == dt); if (ms == null) { ms = new MonthlySubscription() { date = dt, MonthEndClosed = false }; db.MonthlySubscriptions.Add(ms); } var msBank = ms.MonthlySubscriptionBanks.FirstOrDefault(x => x.BankCode == bank.BANK_CODE); if (msBank == null) { msBank = new MonthlySubscriptionBank() { BankCode = bank.BANK_CODE }; ms.MonthlySubscriptionBanks.Add(msBank); } else { MessageBox.Show($"{cbxBank.Text} is already done.\r\n delete old entry and retry If you need."); return; } var msAttachment = msBank.MonthlySubscriptionBankAttachments.FirstOrDefault(x => x.MonthlySubscriptionFileTypeId == fileType.Id); if (msAttachment == null) { msAttachment = new MonthlySubscriptionBankAttachment() { MonthlySubscriptionFileTypeId = fileType.Id }; msBank.MonthlySubscriptionBankAttachments.Add(msAttachment); } msAttachment.FilePath = ofd.FileName; msAttachment.FileContent = File.ReadAllBytes(ofd.FileName); if (fileType.Id == (int)AppLib.MonthlySubscriptionFileType.FromNUBE) { DataTable dataTable = new DataTable(); using (var con = new OleDbConnection("")) { string Import_FileName = ofd.FileName; string sheetName = "Sheet1"; string fileExtension = System.IO.Path.GetExtension(Import_FileName); if (fileExtension == ".xls") { con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;'"; } if (fileExtension == ".xlsx") { con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'"; } using (OleDbCommand comm = new OleDbCommand()) { comm.CommandText = "Select * from [" + sheetName + "$]"; comm.Connection = con; using (OleDbDataAdapter da = new OleDbDataAdapter()) { da.SelectCommand = comm; da.Fill(dataTable); for (var i = 0; i < dataTable.Rows.Count; i++) { var NRIC = dataTable.Rows[i]["NRIC"].ToString(); var MemberName = dataTable.Rows[i]["MemberName"].ToString(); var Amount = dataTable.Rows[i]["Amount"].ToString(); if (String.IsNullOrWhiteSpace(NRIC)) { continue; } var msMember = msBank.MonthlySubscriptionMembers.FirstOrDefault(x => x.NRIC == NRIC); if (msMember == null) { msMember = new MonthlySubscriptionMember() { NRIC = NRIC, MonthlySubcriptionMemberStatusId = (int)AppLib.MonthlySubscriptionMemberStatus.SundryCreditor }; msBank.MonthlySubscriptionMembers.Add(msMember); } msMember.MemberName = MemberName; msMember.Amount = Convert.ToDecimal(Amount); } } } } } db.SaveChanges(); MessageBox.Show("Saved"); LoadMonthlySubsciption(cdrMonthlySubscription.SelectedDate.Value); } } }catch (Exception ex) { MessageBox.Show(ex.Message); } }