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); }
}
