private void checkDue()
{
try
{
using (var ctx = new finalContext())
{
var lon = from lo in ctx.FPaymentInfo
where lo.PaymentDate <= DateTime.Today.Date && (lo.PaymentStatus == "Pending" || lo.PaymentStatus == "On Hold")
select lo;
foreach (var item in lon)
{
var ctr = ctx.FPaymentInfo.Where(x => (x.PaymentDate <= DateTime.Today.Date && x.LoanID == item.LoanID) && (x.PaymentStatus == "Due" || x.PaymentStatus == "Returned" || x.PaymentStatus == "Due/Pending" || x.PaymentStatus == "Deposited")).Count();
if (ctr == 0)
{
item.PaymentStatus = "Due";
}
else
{
item.PaymentStatus = "Due/Pending";
}
}
var dep = from d in ctx.FPaymentInfo
where d.PaymentStatus == "Due"
select d;
foreach (var item in dep)
{
var ctr = ctx.FPaymentInfo.Where(x => x.LoanID == item.LoanID && x.PaymentStatus == "Deposited").Count();
if (ctr != 0)
{
item.PaymentStatus = "Due/Pending";
}
}
var lons = from lo in ctx.Loans
where lo.Status == "Released" && lo.Service.Department == "Financing"
select lo;
foreach (var item in lons)
{
var ctr1 = ctx.FPaymentInfo.Where(x => x.LoanID == item.LoanID && x.PaymentStatus == "Cleared").Count();
var ctr2 = ctx.FPaymentInfo.Where(x => x.LoanID == item.LoanID).Count();
if (ctr1 == ctr2)
{
item.Status = "Paid";
PaidLoan pl = new PaidLoan { LoanID = item.LoanID, DateFinished = DateTime.Today.Date };
ctx.PaidLoans.Add(pl);
}
}
//MICRO
var mLoans = from m in ctx.MPaymentInfoes
where m.DueDate < DateTime.Today.Date && m.PaymentStatus == "Pending"
select m;
DateTime dt;
DateTime dt2;
int Interval = 0;
DateInterval dInt = DateInterval.Day;
foreach (var itm in mLoans)
{
dt = itm.DueDate;
itm.PaymentStatus = "Unpaid";
itm.TotalPayment = 0;
var ser = ctx.Services.Find(itm.Loan.ServiceID);
var iAmt = itm.TotalAmount;
//var ln = ctx.Loans.Find(itm.LoanID);
double cRem = itm.RemainingLoanBalance;
/*var rc = ctx.MPaymentInfoes.Where(x => x.LoanID == itm.LoanID && x.PaymentStatus == "Paid").Count();
if (rc > 0)
{
var re = from x in ctx.MPaymentInfoes
where x.LoanID == itm.LoanID && x.PaymentStatus == "Paid"
select x;
foreach (var item in re)
{
cRem = cRem - itm.TotalPayment;
}
}*/
double ciRate = ser.LatePaymentPenalty / 100;
double ctRate = itm.TotalAmount * ciRate;
double ctBalance = itm.TotalAmount;
//System.Windows.MessageBox.Show(ciRate.ToString());
//System.Windows.MessageBox.Show(ctRate.ToString());
int n = itm.PaymentNumber;
while (dt < DateTime.Today.Date)
{
String value = itm.Loan.Mode;
if (value == "Semi-Monthly")
{
Interval = 15;
dInt = DateInterval.Day;
}
else if (value == "Weekly")
{
Interval = 7;
dInt = DateInterval.Day;
}
else if (value == "Daily")
{
Interval = 1;
dInt = DateInterval.Day;
}
dt = DateAndTime.DateAdd(dInt, Interval, dt);
bool isHoliday = true;
while (isHoliday == true || dt.Date.DayOfWeek.ToString() == "Saturday" || dt.Date.DayOfWeek.ToString() == "Sunday")
{
if (dt.Date.DayOfWeek.ToString() == "Saturday")
{
dt = DateAndTime.DateAdd(DateInterval.Day, 2, dt);
}
else if (dt.Date.DayOfWeek.ToString() == "Sunday")
{
dt = DateAndTime.DateAdd(DateInterval.Day, 1, dt);
}
var myC = ctx.Holidays.Where(x => x.Date.Month == dt.Date.Month && x.Date.Day == dt.Date.Day && x.isYearly == true).Count();
if (myC > 0)
{
dt = DateAndTime.DateAdd(DateInterval.Day, 1, dt);
isHoliday = true;
}
else
{
myC = ctx.Holidays.Where(x => x.Date.Month == dt.Date.Month && x.Date.Day == dt.Date.Day && x.Date.Year == dt.Date.Year && x.isYearly == !true).Count();
if (myC > 0)
{
dt = DateAndTime.DateAdd(DateInterval.Day, 1, dt);
isHoliday = true;
}
else
{
isHoliday = false;
}
}
}
String str = "";
double tRate = ctRate;
str = tRate.ToString("N2");
tRate = Convert.ToDouble(str);
//System.Windows.MessageBox.Show(tRate.ToString());
double tBalance = ctBalance + tRate;
str = tBalance.ToString("N2");
tBalance = Convert.ToDouble(str);
double tAmount = itm.Amount + tBalance;
str = tAmount.ToString("N2");
tAmount = Convert.ToDouble(str);
ctBalance = tAmount;
ctRate = ctBalance * ciRate;
double tRem = cRem + tRate;
str = tRem.ToString("N2");
tRem = Convert.ToDouble(str);
cRem = tRem;
dt2 = DateAndTime.DateAdd(dInt, Interval, dt);
String st = "Unpaid";
if (dt2 > DateTime.Today.Date)
st = "Pending";
MPaymentInfo mpi = null;
if (tAmount <= tRem)
{
mpi = new MPaymentInfo { PaymentNumber = n + 1, Amount = itm.Amount, TotalBalance = tBalance, BalanceInterest = tRate, DueDate = dt, ExcessBalance = 0, LoanID = itm.LoanID, PaymentStatus = st, TotalAmount = tAmount, RemainingLoanBalance = tRem, PreviousBalance = iAmt };
ctx.MPaymentInfoes.Add(mpi);
}
else
{
if (itm.PaymentStatus == "Unpaid")
{
double tPaid = 0;
var m1 = from m in ctx.MPaymentInfoes
where m.LoanID == itm.LoanID && m.PaymentStatus == "Paid"
select m;
foreach (var i in m1)
{
tPaid = tPaid + i.TotalPayment;
}
var c = ctx.PassedToCollectors.Where(x => x.LoanID == itm.LoanID).Count();
if (c < 1)
{
using (var ctx2 = new finalContext())
{
PassedToCollector pc = new PassedToCollector { DatePassed = DateTime.Today.Date, LoanID = itm.LoanID, RemainingBalance = tRem, TotalPassedBalance = tRem, TotalPaidBeforePassing = tPaid };
var l1 = ctx2.Loans.Find(itm.LoanID);
l1.Status = "Under Collection";
ctx2.PassedToCollectors.Add(pc);
ctx2.SaveChanges();
}
}
}
}
iAmt = tAmount;
n++;
}
}
ctx.SaveChanges();
}
}
catch (Exception ex)
{
System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
}
private void btnRelease_Click(object sender, RoutedEventArgs e)
{
try
{
if (status == "Releasing")
{
double max = 0;
double min = 0;
using (var ctx = new finalContext())
{
var lon = ctx.Loans.Find(lId);
var ser = ctx.Services.Find(lon.ServiceID);
max = ser.MaxTerm;
min = ser.MinTerm;
if (Convert.ToDouble(txtTerm.Text) > max || Convert.ToDouble(txtTerm.Text) < min)
{
System.Windows.MessageBox.Show("Term must not be greater than the maximum term(" + ser.MaxTerm + " mo.) OR less than the minimum term(" + ser.MinTerm + " mo.)", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
max = ser.MaxValue;
min = ser.MinValue;
if (Convert.ToDouble(txtAmt.Text) > max || Convert.ToDouble(txtAmt.Text) < min)
{
System.Windows.MessageBox.Show("Principal amount must not be greater than the maximum loanable amount OR less than the minimum loanable amount", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
}
if (Convert.ToDouble(txtAmt.Text) > Convert.ToDouble(lblPrincipal.Content))
{
MessageBox.Show("Principal amount must not be greater than the maximum loanable amount", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
if(ciId == 0)
{
MessageBox.Show("Please assign a collector for this loan", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
MessageBoxResult mr = MessageBox.Show("Are you sure you want to process this transaction?", "Question", MessageBoxButton.YesNo, MessageBoxImage.Question);
if (mr == MessageBoxResult.Yes)
{
using (var ctx = new finalContext())
{
int bId = 0;
var lon = ctx.Loans.Find(lId);
lon.Status = "Released";
lon.Mode = cmbMode.Text;
lon.Term = Convert.ToInt32(txtTerm.Text);
lon.CollectortID = ciId;
var cn = ctx.Services.Find(lon.ServiceID);
double co = cn.AgentCommission / 100;
double cm = Convert.ToDouble(txtAmt.Text) * co;
//MessageBox.Show(cm.ToString());
ReleasedLoan rl = new ReleasedLoan { AgentsCommission = cm, DateReleased = DateTime.Today.Date, LoanID = lId, MonthlyPayment = Convert.ToDouble(lblMonthly.Content), NetProceed = Convert.ToDouble(lblProceed.Content), Principal = Convert.ToDouble(txtAmt.Text), TotalLoan = Convert.ToDouble(lblInt.Content) };
lon.ReleasedLoan = rl;
var lo = ctx.GenSOA.Where(x => x.PaymentNumber == 1).First();
int y = 0;
MPaymentInfo py = new MPaymentInfo { Amount = Convert.ToDouble(lo.Amount), BalanceInterest = 0, DueDate = lo.PaymentDate, LoanID = lId, PaymentNumber = 1, PaymentStatus = "Pending", PreviousBalance = 0, RemainingLoanBalance = Convert.ToDouble(lo.RemainingBalance), TotalAmount = Convert.ToDouble(lo.Amount), TotalBalance = 0 };
ctx.MPaymentInfoes.Add(py);
AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Released loan (" + lon.Service.Name + ") for client " + lon.Client.FirstName + " " + lon.Client.MiddleName + " " + lon.Client.LastName + " " + lon.Client.Suffix };
ctx.AuditTrails.Add(at);
ctx.SaveChanges();
printSOA();
MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
this.Close();
}
}
}
else if (status == "UReleasing")
{
using (var ctx = new finalContext())
{
var lon = ctx.Loans.Find(lId);
lon.CollectortID = ciId;
AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Updated Released loan (" + lon.Service.Name + ") for client " + lon.Client.FirstName + " " + lon.Client.MiddleName + " " + lon.Client.LastName + " " + lon.Client.Suffix };
ctx.AuditTrails.Add(at);
ctx.SaveChanges();
MessageBox.Show("Transaction has been successfully updated", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
this.Close();
}
}
}
catch (Exception ex)
{
System.Windows.MessageBox.Show("Incorrect Format on some Fields / Incomplete Input(s)", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
}
private void btnRecord_Click(object sender, RoutedEventArgs e)
{
//try
//{
double amt = 0;
try
{
amt = Convert.ToDouble(txtAmt.Text);
amt = Convert.ToDouble(amt.ToString("N2"));
}
catch(Exception)
{
amt = 0;
}
if (amt > Convert.ToDouble(lblTotalLoan.Content))
{
System.Windows.MessageBox.Show("Amount must not be greater that the remaining amount", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
if (amt < 1)
{
System.Windows.MessageBox.Show("Amount must be greater that 1", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
double cPayment = (Convert.ToDouble(lblTotalLoan.Content) - Convert.ToDouble(lblExcessive.Content));
if (cPayment < 0 || cPayment < Convert.ToDouble(lblTotalLoan.Content))
{
cPayment = Convert.ToDouble(lblTotalLoan.Content);
}
if (amt > cPayment )
{
System.Windows.MessageBox.Show("Amount must not be greater that the remaining amount", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
if (amt <= Convert.ToDouble(lblTotal.Content))
{
//System.Windows.MessageBox.Show("Dito");
using (var ctx = new finalContext())
{
int lID = Convert.ToInt32(txtID.Text);
var py = ctx.MPaymentInfoes.Where(x => x.LoanID == lID && x.PaymentStatus == "Pending").First();
DateInterval dInt = new DateInterval();
int Interval = 0;
String value = py.Loan.Mode;
if (value == "Semi-Monthly")
{
Interval = 15;
dInt = DateInterval.Day;
}
else if (value == "Weekly")
{
Interval = 7;
dInt = DateInterval.Day;
}
else if (value == "Daily")
{
Interval = 1;
dInt = DateInterval.Day;
}
DateTime dt = py.DueDate;
dt = DateAndTime.DateAdd(dInt, Interval, dt);
bool isHoliday = true;
while (isHoliday == true || dt.Date.DayOfWeek.ToString() == "Saturday" || dt.Date.DayOfWeek.ToString() == "Sunday")
{
if (dt.Date.DayOfWeek.ToString() == "Saturday")
{
dt = DateAndTime.DateAdd(DateInterval.Day, 2, dt);
}
else if (dt.Date.DayOfWeek.ToString() == "Sunday")
{
dt = DateAndTime.DateAdd(DateInterval.Day, 1, dt);
}
var myC = ctx.Holidays.Where(x => x.Date.Month == dt.Date.Month && x.Date.Day == dt.Date.Day && x.isYearly == true).Count();
if (myC > 0)
{
dt = DateAndTime.DateAdd(DateInterval.Day, 1, dt);
isHoliday = true;
}
else
{
myC = ctx.Holidays.Where(x => x.Date.Month == dt.Date.Month && x.Date.Day == dt.Date.Day && x.Date.Year == dt.Date.Year && x.isYearly == !true).Count();
if (myC > 0)
{
dt = DateAndTime.DateAdd(DateInterval.Day, 1, dt);
isHoliday = true;
}
else
{
isHoliday = false;
}
}
}
var ser = ctx.Services.Find(py.Loan.ServiceID);
double interest = ser.LatePaymentPenalty / 100;
double payment = Convert.ToDouble(amt.ToString("N2"));
double pbal = Convert.ToDouble((py.TotalAmount - payment).ToString("N2"));
double balInt = Convert.ToDouble((pbal * interest).ToString("N2"));
double totalBal = Convert.ToDouble((pbal + balInt).ToString("N2"));
double total = Convert.ToDouble((totalBal + py.Amount).ToString("N2"));
double re = Convert.ToDouble(lblTotalLoan.Content);
double rem = Convert.ToDouble((re - payment).ToString("N2"));
if (rem < total)
{
rem = 0;
total = totalBal;
}
py.TotalPayment = amt;
py.PaymentDate = DateTime.Now.Date;
py.PaymentStatus = "Paid";
int n = py.PaymentNumber + 1;
if ((amt == Convert.ToDouble(lblTotal.Content) && rem <= py.Amount) || rem < 0)
{
System.Windows.MessageBox.Show("The Loan has been successfully finished!", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
var lon = ctx.Loans.Find(lID);
lon.Status = "Paid";
py.TotalPayment = amt;
py.PaymentDate = DateTime.Now.Date;
py.PaymentStatus = "Paid";
py.RemainingLoanBalance = 0;
PaidLoan pl = new PaidLoan { LoanID = lID, DateFinished = DateTime.Today.Date };
ctx.PaidLoans.Add(pl);
goto here;
}
MPaymentInfo mp = new MPaymentInfo { Amount = py.Amount, BalanceInterest = balInt, DueDate = dt, ExcessBalance = 0, LoanID = lID, PaymentNumber = n, PaymentStatus = "Pending", PreviousBalance = pbal, RemainingLoanBalance = rem, TotalAmount = total, TotalBalance = totalBal };
ctx.MPaymentInfoes.Add(mp);
here:
ctx.SaveChanges();
txtID.Text = "";
txtID.Focus();
}
}
else
{
using (var ctx = new finalContext())
{
int lID = Convert.ToInt32(txtID.Text);
var py = ctx.MPaymentInfoes.Where(x => x.LoanID == lID && x.PaymentStatus == "Pending").First();
DateInterval dInt = new DateInterval();
double ex = amt - py.TotalAmount;
while (ex > 0)
{
int Interval = 0;
String value = py.Loan.Mode;
if (value == "Semi-Monthly")
{
Interval = 15;
dInt = DateInterval.Day;
}
else if (value == "Weekly")
{
Interval = 7;
dInt = DateInterval.Day;
}
else if (value == "Daily")
{
Interval = 1;
dInt = DateInterval.Day;
}
DateTime dt = py.DueDate;
dt = DateAndTime.DateAdd(dInt, Interval, dt);
bool isHoliday = true;
while (isHoliday == true || dt.Date.DayOfWeek.ToString() == "Saturday" || dt.Date.DayOfWeek.ToString() == "Sunday")
{
if (dt.Date.DayOfWeek.ToString() == "Saturday")
{
dt = DateAndTime.DateAdd(DateInterval.Day, 2, dt);
}
else if (dt.Date.DayOfWeek.ToString() == "Sunday")
{
dt = DateAndTime.DateAdd(DateInterval.Day, 1, dt);
}
var myC = ctx.Holidays.Where(x => x.Date.Month == dt.Date.Month && x.Date.Day == dt.Date.Day && x.isYearly == true).Count();
if (myC > 0)
{
dt = DateAndTime.DateAdd(DateInterval.Day, 1, dt);
isHoliday = true;
}
else
{
myC = ctx.Holidays.Where(x => x.Date.Month == dt.Date.Month && x.Date.Day == dt.Date.Day && x.Date.Year == dt.Date.Year && x.isYearly == !true).Count();
if (myC > 0)
{
dt = DateAndTime.DateAdd(DateInterval.Day, 1, dt);
isHoliday = true;
}
else
{
isHoliday = false;
}
}
}
double payment = Convert.ToDouble(py.Amount.ToString("N2"));
double rem = Convert.ToDouble((py.RemainingLoanBalance - py.TotalAmount).ToString("N2"));
double total = Convert.ToDouble((py.Amount - ex).ToString("N2"));
ex = Convert.ToDouble(ex.ToString("N2"));
double ex2 = ex - py.Amount;
ex2 = Convert.ToDouble(ex2.ToString("N2"));
string st = "";
int n = py.PaymentNumber + 1;
//System.Windows.MessageBox.Show(rem.ToString());
MPaymentInfo mp = new MPaymentInfo();
if (total < 0)
{
total = py.Amount;
st = "Pending";
n = py.PaymentNumber;
mp = new MPaymentInfo { Amount = py.Amount, BalanceInterest = 0, DueDate = dt, ExcessBalance = Convert.ToDouble(ex.ToString("N2")), LoanID = lID, PaymentNumber = n, PaymentStatus = st, PreviousBalance = 0, RemainingLoanBalance = rem, TotalAmount = total, TotalBalance = 0, PaymentDate = DateTime.Now.Date };
}
else
{
st = "Pending";
mp = new MPaymentInfo { Amount = py.Amount, BalanceInterest = 0, DueDate = dt, ExcessBalance = Convert.ToDouble(ex.ToString("N2")), LoanID = lID, PaymentNumber = n, PaymentStatus = st, PreviousBalance = 0, RemainingLoanBalance = rem, TotalAmount = total, TotalBalance = 0 };
}
ex = Convert.ToDouble(ex.ToString("N2"));
if ((rem <= py.Amount && ex >= py.Amount) || rem < 0)
{
System.Windows.MessageBox.Show("The Loan has been successfully finished!", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
var lon = ctx.Loans.Find(lID);
lon.Status = "Paid";
py.TotalPayment = amt;
py.PaymentDate = DateTime.Now.Date;
py.PaymentStatus = "Paid";
PaidLoan pl = new PaidLoan { LoanID = lID, DateFinished = DateTime.Today.Date };
ctx.PaidLoans.Add(pl);
mp.PaymentStatus = "Paid";
mp.TotalAmount = py.Amount;
mp.ExcessBalance = py.Amount;
mp.RemainingLoanBalance = 0;
mp.PaymentDate = DateTime.Now.Date;
mp.TotalPayment = py.Amount;
goto here;
}
//System.Windows.MessageBox.Show(ex.ToString("N2"));
py.TotalPayment = amt;
py.PaymentDate = DateTime.Now.Date;
py.PaymentStatus = "Paid";
amt = Convert.ToDouble(ex.ToString("N2"));
here:
ctx.MPaymentInfoes.Add(mp);
ctx.SaveChanges();
txtID.Text = "";
txtID.Focus();
try
{
py = ctx.MPaymentInfoes.Where(x => x.LoanID == lID && x.PaymentStatus == "Pending").First();
}
catch (Exception)
{
return;
}
ex = ex2;
}
}
}
/*}
catch (Exception ex)
{
System.Windows.MessageBox.Show("Runtime Error:" + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}*/
}
