public decimal?GetAdjustment(string id)
{
db = new db_lendingEntities();
{
decimal?adjustment = 0;
var result = from d in db.tbl_adjustment where d.LoanNo.Equals(id) orderby d.Autonum select d;
foreach (var data in result)
{
if (data.TransType == "Debit memo")
{
adjustment = decimal.Round((decimal)(adjustment + data.Amount), 2, MidpointRounding.AwayFromZero);
}
else
{
adjustment = decimal.Round((decimal)(adjustment - data.Amount), 2, MidpointRounding.AwayFromZero);
}
}
return(adjustment);
}
}
public ActionResult Create()
{
db_lendingEntities db = new db_lendingEntities();
ViewBag.InterestTypeID = "";
if (Session["UserId"] != null)
{
ViewBag.Form = "Loan Processing";
ViewBag.Controller = "Loan Processing";
ViewBag.Action = "Create";
LoadCustomer();
LoadLoanType();
return(View());
}
else
{
return(RedirectToAction("Login", "Account"));
}
}
public ActionResult LoadList()
{
try
{
var dateNow = DateTime.Now;
dateNow = dateNow.AddDays(-7);
using (db_lendingEntities db = new db_lendingEntities())
{
//var data = db.tbl_loan_processing.Where(a => a.loan_date >= DateTime.Now && a.loan_date <= DateTime.Now).ToList();
var data = db.tbl_loan_processing.Where(a => a.loan_date >= dateNow).OrderByDescending(a => a.autonum).ToList();
//var data = from d in db.tbl_loan_processing select new { d.autonum, d.loan_date, d.loan_no, d.loan_name, d.loan_granted };
return(Json(new { data = data }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception)
{
throw;
}
}
public async Task <JsonResult> Save(tbl_cash_in model)
{
try
{
db_lendingEntities db = new db_lendingEntities();
tbl_cash_in tbl = new tbl_cash_in();
bool success = false;
string message = "";
tbl.CashInDate = model.CashInDate;
tbl.UserName = model.UserName;
tbl.DateFrom = model.DateFrom;
tbl.DateTo = model.DateTo;
tbl.Amount = model.Amount;
tbl.CreatedBy = Session["UserName"].ToString();
tbl.CreatedAt = DateTime.Now;
db.tbl_cash_in.Add(tbl);
await db.SaveChangesAsync();
var result = true;
success = result;
if (result)
{
message = "Successfully saved.";
}
else
{
message = "Error saving data. Duplicate entry.";
}
return(Json(new { success = success, message = message }));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public JsonResult ViewInterestDues(String id)
{
db_lendingEntities db = new db_lendingEntities();
try
{
if (Session["UserId"] != null)
{
var result = from d in db.tbl_payment_details where d.reference_no == id && d.payment_type == "OR Payment Interest" orderby d.loan_no, d.payment_type ascending select d;
return(Json(new { data = result }, JsonRequestBehavior.AllowGet));
}
else
{
return(Json("Failed", JsonRequestBehavior.AllowGet));
}
}
catch (Exception)
{
throw;
}
}
public JsonResult GetReceivablesForTheDay()
{
try
{
decimal?totalBalance = 0;
using (db = new db_lendingEntities())
{
DateTime dateVar = _serverDateTime;
var result = from d in db.tbl_loan_processing where (d.due_date <= dateVar) && d.status == "Released" orderby d.customer_name ascending select d;
foreach (var dt in result)
{
decimal?principal = decimal.Round((decimal)dt.loan_granted, 2, MidpointRounding.AwayFromZero);
decimal?principalInterest = dt.loan_granted * (dt.loan_interest_rate / 100);
decimal?interest = GetInterest(dt.loan_no);
decimal?additionalInterest = GetAdditionalInterest(dt.loan_no);
interest = decimal.Round((decimal)(principalInterest + interest + additionalInterest), 2, MidpointRounding.AwayFromZero);
decimal?payment = decimal.Round((decimal)GetPayments(dt.loan_no), 2, MidpointRounding.AwayFromZero);
decimal?balance = decimal.Round((decimal)(principal + interest - payment), 2, MidpointRounding.AwayFromZero);
if (balance > 0)
{
totalBalance = totalBalance + balance;
}
}
ViewBag.totalBalance = decimal.Round((decimal)totalBalance, 2, MidpointRounding.AwayFromZero);
ViewBag.totalBalance = String.Format("{0:0.00}", ViewBag.totalBalance);
}
return(Json(ViewBag.totalBalance, JsonRequestBehavior.AllowGet));
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
//Functions
public void LoadCustomer()
{
try
{
using (db = new db_lendingEntities())
{
var customerList = new List <SelectListItem>();
var dbQuery = from d in db.tbl_customer select d;
foreach (var d in dbQuery)
{
if ((d.firstname != null) && (d.lastname != null) && (d.middlename != null))
{
customerList.Add(new SelectListItem {
Value = d.autonum.ToString(), Text = d.lastname.ToUpper() + ", " + d.firstname.ToUpper() + " " + d.middlename.ToUpper()
});
}
if ((d.firstname != null) && (d.lastname != null) && (d.middlename == null))
{
customerList.Add(new SelectListItem {
Value = d.autonum.ToString(), Text = d.lastname.ToUpper() + ", " + d.firstname.ToUpper()
});
}
if ((d.firstname != null) && (d.lastname == null) && (d.middlename == null))
{
customerList.Add(new SelectListItem {
Value = d.autonum.ToString(), Text = d.firstname.ToUpper()
});
}
}
ViewBag.Customer = new SelectList(customerList, "Value", "Text");
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public JsonResult AddFee(tbl_fees_validation model)
{
try
{
db_lendingEntities db = new db_lendingEntities();
tbl_fees tbl = new tbl_fees();
tbl.fees_description = model.fees_description;
db.tbl_fees.Add(tbl);
db.SaveChanges();
return(Json("Success", JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json("Failed", JsonRequestBehavior.DenyGet));
throw ex;
}
}
public ActionResult Print(int?id)
{
try
{
using (db_lendingEntities db = new db_lendingEntities())
{
tbl_end_of_day_transactions tbl_end_of_day_transactions = db.tbl_end_of_day_transactions.Find(id);
if (Session["UserId"] != null)
{
return(View(tbl_end_of_day_transactions));
}
else
{
return(RedirectToAction("Login", "Account"));
}
}
}
catch (Exception)
{
throw;
}
}
//VIEWING
public ActionResult Details(int?id)
{
try
{
using (db_lendingEntities db = new db_lendingEntities())
{
tbl_payment tbl_payment = db.tbl_payment.Find(id);
if (Session["UserId"] != null)
{
return(View(tbl_payment));
}
else
{
return(RedirectToAction("Login", "Account"));
}
}
}
catch (Exception)
{
throw;
}
}
public JsonResult SaveLedger(tbl_loan_ledger model)
{
try
{
db_lendingEntities db = new db_lendingEntities();
tbl_loan_ledger tbl = new tbl_loan_ledger();
tbl.date_trans = model.date_trans;
tbl.trans_type = model.trans_type;
tbl.reference_no = model.reference_no;
tbl.loan_no = model.loan_no;
tbl.loan_type_name = model.loan_type_name;
tbl.customer_id = model.customer_id;
tbl.customer_name = model.customer_name;
tbl.interest_type = model.interest_type;
tbl.interest_rate = model.interest_rate;
tbl.interest = model.interest;
tbl.amount_paid = model.amount_paid;
tbl.principal = model.principal;
tbl.balance = model.balance;
tbl.date_created = DateTime.Now;
tbl.created_by = Session["UserName"].ToString();
db.tbl_loan_ledger.Add(tbl);
db.SaveChanges();
return(Json(tbl.reference_no, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json("Failed", JsonRequestBehavior.DenyGet));
throw ex;
}
}
public decimal?GetLedgerBalance(string id)
{
using (db = new db_lendingEntities())
{
decimal?balance = 0;
var result =
from d in db.tbl_loan_ledger
where d.loan_no.Equals(id)
select d;
foreach (var data in result)
{
switch (data.trans_type)
{
case "Beginning Balance":
balance = data.balance;
break;
case "Late Payment Interest":
balance = balance + data.interest;
break;
case "OR Payment":
balance = balance - data.amount_paid;
break;
case "OR Payment Interest":
balance = balance - data.interest;
break;
default:
break;
}
}
return(balance);
}
}
public DateTime?GetInterestStartDate(string id)
{
using (db = new db_lendingEntities())
{
DateTime?dateStart = null;
var result =
from d in db.tbl_loan_ledger
where d.loan_no.Equals(id)
orderby d.autonum
select d;
foreach (var data in result)
{
switch (data.trans_type)
{
case "Beginning Balance":
if (data.interest_type == "1")
{
dateStart = data.date_trans.Value.AddDays(1);
}
else if (data.interest_type == "2")
{
dateStart = data.date_trans.Value.AddDays(30);
}
break;
case "Late Payment Interest":
dateStart = data.date_trans;
break;
default:
break;
}
}
return(dateStart);
}
}
public JsonResult CheckIfForRestructure(string id)
{
try
{
using (db = new db_lendingEntities())
{
var message = "";
if (isRestructuredDone(id) == false)
{
message = "true";
}
else
{
message = "false";
}
return(Json(new { success = true, message = message }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public decimal?GetLedgerInterestBalance(string id, decimal?initialInterest)
{
using (db = new db_lendingEntities())
{
decimal?interest = initialInterest;
var result =
from d in db.tbl_loan_ledger
where d.loan_no.Equals(id)
select d;
foreach (var data in result)
{
switch (data.trans_type)
{
case "Late Payment Interest":
interest = interest + decimal.Round((decimal)data.interest, 2, MidpointRounding.AwayFromZero);
break;
case "OR Payment":
interest = interest - decimal.Round((decimal)data.interest, 2, MidpointRounding.AwayFromZero);
break;
case "Debit memo":
interest = interest + decimal.Round((decimal)data.interest, 2, MidpointRounding.AwayFromZero);
break;
case "Credit memo":
interest = interest - decimal.Round((decimal)data.interest, 2, MidpointRounding.AwayFromZero);
break;
default:
break;
}
}
return(interest);
}
}
public ActionResult Print(string date)
{
if (Session["UserId"] != null)
{
try
{
using (db = new db_lendingEntities())
{
DateTime dateVar = DateTime.Parse(date);
decimal? totalBalance = 0;
decimal? Balance1 = 0;
decimal? Balance2 = 0;
decimal? Balance3 = 0;
List <receivables> receivablesList = new List <receivables>();
List <receivables> receivablesList1 = new List <receivables>();
List <receivables> receivablesList2 = new List <receivables>();
var count = 0;
var count1 = 0;
var count2 = 0;
var result = from d in db.tbl_loan_processing where (d.due_date <= dateVar) && d.status == "Released" orderby d.customer_name ascending select d;
foreach (var dt in result)
{
decimal?principal = decimal.Round((decimal)dt.loan_granted, 2, MidpointRounding.AwayFromZero);
decimal?principalInterest = dt.loan_granted * (dt.loan_interest_rate / 100);
decimal?interest = GetInterest(dt.loan_no);
decimal?additionalInterest = GetAdditionalInterest(dt.loan_no);
interest = decimal.Round((decimal)(principalInterest + interest + additionalInterest), 2, MidpointRounding.AwayFromZero);
decimal?payment = decimal.Round((decimal)GetPayments(dt.loan_no), 2, MidpointRounding.AwayFromZero);
decimal?balance = decimal.Round((decimal)(principal + interest - payment), 2, MidpointRounding.AwayFromZero);
if (balance > 0)
{
if (GetInterestType(dt.loan_name) == "1")
{
receivablesList.Add(new receivables {
loanNo = dt.loan_no, customerName = dt.customer_name.ToString().ToUpperInvariant(), dueDate = String.Format("{0:MM/dd/yyyy}", dt.due_date), principal = String.Format("{0:n}", principal), interest = String.Format("{0:n}", interest), payment = String.Format("{0:n}", payment), balance = String.Format("{0:n}", balance)
});
Balance1 = Balance1 + balance;
count += 1;
}
else if (GetInterestType(dt.loan_name) == "2")
{
receivablesList1.Add(new receivables {
loanNo = dt.loan_no, customerName = dt.customer_name.ToString().ToUpperInvariant(), dueDate = String.Format("{0:MM/dd/yyyy}", dt.due_date), principal = String.Format("{0:n}", principal), interest = String.Format("{0:n}", interest), payment = String.Format("{0:n}", payment), balance = String.Format("{0:n}", balance)
});
Balance2 = Balance2 + balance;
count1 += 1;
}
else if (GetInterestType(dt.loan_name) != "1" && GetInterestType(dt.loan_name) != "2")
{
receivablesList2.Add(new receivables {
loanNo = dt.loan_no, customerName = dt.customer_name.ToString().ToUpperInvariant(), dueDate = String.Format("{0:MM/dd/yyyy}", dt.due_date), principal = String.Format("{0:n}", principal), interest = String.Format("{0:n}", interest), payment = String.Format("{0:n}", payment), balance = String.Format("{0:n}", balance)
});
Balance3 = Balance3 + balance;
count2 += 1;
}
totalBalance = totalBalance + balance;
}
}
ViewBag.dateString = String.Format("{0:MMMM dd, yyyy}", date);
ViewBag.receivableList = receivablesList;
ViewBag.receivableList1 = receivablesList1;
ViewBag.receivableList2 = receivablesList2;
ViewBag.count = count;
ViewBag.count1 = count1;
ViewBag.count2 = count2;
ViewBag.Balance1 = decimal.Round((decimal)Balance1, 2, MidpointRounding.AwayFromZero);
ViewBag.Balance1 = String.Format("{0:n}", ViewBag.Balance1);
ViewBag.Balance2 = decimal.Round((decimal)Balance2, 2, MidpointRounding.AwayFromZero);
ViewBag.Balance2 = String.Format("{0:n}", ViewBag.Balance2);
ViewBag.Balance3 = decimal.Round((decimal)Balance3, 2, MidpointRounding.AwayFromZero);
ViewBag.Balance3 = String.Format("{0:n}", ViewBag.Balance3);
ViewBag.totalBalance = decimal.Round((decimal)totalBalance, 2, MidpointRounding.AwayFromZero);
ViewBag.totalBalance = String.Format("{0:n}", ViewBag.totalBalance);
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
return(View());
}
else
{
return(RedirectToAction("Login", "Account"));
}
}
public JsonResult GenerateInterest(string loanNo)
{
using (db = new db_lendingEntities())
{
var result =
from d in db.tbl_loan_processing
where d.loan_no == loanNo &&
d.status == "Released" &&
d.due_date < _serverDateTime
orderby d.loantype_id ascending
select d;
foreach (var dt in result)
{
var balance = (decimal)GetLedgerBalance(dt.loan_no);
if (balance > 0)
{
var interestRate = (decimal)dt.loan_interest_rate;
decimal noOfDays = 0;
var dateStart = GetInterestStartDate(dt.loan_no);
var skipIinterest = false;
if (GetInterestType(dt.loan_name) == "1")
{
noOfDays = decimal.ToInt32((_serverDateTime - dateStart).Value.Days);
}
else
{
skipIinterest = true;
//if ((decimal.ToInt32((_serverDateTime - dateStart).Value.Days)) >= 1)
//{
// if ((decimal.ToInt32((_serverDateTime - dateStart).Value.Days)) == 1)
// {
// if (NoOfInterest(dt.loan_no) >=
// (decimal.ToInt32((_serverDateTime - dateStart).Value.Days)))
// {
// skipIinterest = true;
// }
// noOfDays = 1;
// }
// else
// {
// noOfDays = (_serverDateTime - dateStart).Value.Days;
// noOfDays = (noOfDays / 30);
// noOfDays = decimal.Ceiling(noOfDays);
// if (NoOfInterest(dt.loan_no) >= noOfDays)
// {
// skipIinterest = true;
// }
// }
//}
}
decimal totalInterest = 0;
for (var c = 0; c < noOfDays; c++)
{
var interest = (balance * (interestRate / 100));
balance = balance + interest;
totalInterest = totalInterest + interest;
}
if (skipIinterest == false)
{
db_lendingEntities dbSave = new db_lendingEntities();
tbl_loan_ledger tbl = new tbl_loan_ledger();
tbl.date_trans = _serverDateTime;
tbl.trans_type = "Late Payment Interest";
tbl.reference_no = "";
tbl.loan_no = dt.loan_no;
tbl.loan_type_name = dt.loan_name;
tbl.customer_id = dt.customer_id;
tbl.customer_name = dt.customer_name;
tbl.interest_type = GetInterestType(dt.loan_name);
tbl.interest_rate = dt.loan_interest_rate;
tbl.interest = totalInterest;
tbl.amount_paid = 0;
tbl.principal = 0;
tbl.balance = 0;
tbl.date_created = DateTime.Now;
tbl.created_by = Session["UserName"].ToString();
dbSave.tbl_loan_ledger.Add(tbl);
dbSave.SaveChanges();
}
}
}
}
return(Json("Success", JsonRequestBehavior.AllowGet));
}
public ActionResult LoadReprintDetails(int id)
{
try
{
using (db = new db_lendingEntities())
{
var ReceiptNo = "";
var Date = "";
var Borrower = "";
var IdNo = "";
var principalReference = "";
var principalParticulars = "";
var principalAmount = "";
var interestReference = "";
var interestParticulars = "";
var interestAmount = "";
var balancLoanNo = "";
var balanceAmount = "";
var result = from d in db.tbl_payment where d.autonum == id select d;
foreach (var dt in result)
{
ReceiptNo = dt.reference_no;
Date = dt.date_trans.ToString();
Borrower = dt.payor_name;
IdNo = dt.payor_id.ToString();
}
var result1 = from d in db.tbl_payment_details where d.reference_no == ReceiptNo && d.payment_type == "OR Payment" select d;
foreach (var dt in result1)
{
principalReference = dt.reference_no.ToString();
principalParticulars = "Principal payment";
principalAmount = String.Format("{0:n}", dt.amount);
balancLoanNo = dt.loan_no;
}
var result2 = from d in db.tbl_payment_details where d.reference_no == ReceiptNo && d.payment_type == "OR Payment Interest" select d;
foreach (var dt in result2)
{
interestReference = dt.reference_no.ToString();
interestParticulars = "Interest payment";
interestAmount = String.Format("{0:n}", dt.amount);
balancLoanNo = dt.loan_no;
}
balanceAmount = String.Format("{0:n}", DisplayLedgerBalance(balancLoanNo, ReceiptNo));
List <DetailsForReprintModel> list = new List <DetailsForReprintModel>();
list.Add(new DetailsForReprintModel
{
ReceiptNo = "Receipt No: " + ReceiptNo,
Date = "Date: " + Date,
Borrower = "Borrower: " + Borrower,
IdNo = "ID No: " + IdNo,
principalReference = principalReference,
principalParticulars = principalParticulars,
principalAmount = principalAmount,
interestReference = interestReference,
interestParticulars = interestParticulars,
interestAmount = interestAmount,
balancLoanNo = balancLoanNo,
balanceAmount = balanceAmount
});
return(Json(list, JsonRequestBehavior.AllowGet));
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public decimal?GetLedgerBalance(string id)
{
using (db = new db_lendingEntities())
{
var found = false;
decimal?balance = 0;
var result =
from d in db.tbl_loan_ledger
where d.loan_no.Equals(id)
orderby(d.autonum)
select d;
foreach (var data in result)
{
switch (data.trans_type)
{
case "Beginning Balance":
balance = data.balance;
break;
case "Late Payment Interest":
balance = balance + decimal.Round((decimal)data.interest, 2, MidpointRounding.AwayFromZero);
break;
case "OR Payment":
balance = balance - decimal.Round((decimal)data.amount_paid, 2, MidpointRounding.AwayFromZero);
break;
case "OR Payment Interest":
balance = balance - decimal.Round((decimal)data.interest, 2, MidpointRounding.AwayFromZero);
break;
case "Debit memo":
balance = balance + decimal.Round((decimal)data.interest, 2, MidpointRounding.AwayFromZero);
break;
case "Credit memo":
balance = balance - decimal.Round((decimal)data.interest, 2, MidpointRounding.AwayFromZero);
break;
default:
break;
}
if (data.interest_type == "1")
{
if (data.date_trans.Value.Day == _serverDateTime.Day && data.date_trans.Value.DayOfYear == _serverDateTime.DayOfYear)
{
found = true;
}
}
else
{
if (data.date_trans.Value.Day == _serverDateTime.Day && data.date_trans.Value.DayOfYear == _serverDateTime.DayOfYear)
{
found = true;
}
}
}
if (found)
{
balance = 0;
return(balance);
}
else
{
return(balance);
}
}
}
public async Task <JsonResult> Save(collectionSaveModel model)
{
try
{
tbl_payment payment = new tbl_payment();
bool success = false;
string message = "";
db = new db_lendingEntities();
var isDuplicate = await db.tbl_payment.AllAsync(u => u.reference_no == model.PaymentNo);
if (isDuplicate == false)
{
payment.reference_no = model.PaymentNo;
payment.date_trans = DateTime.Parse(model.PaymentDate);
payment.payor_id = int.Parse(model.CustomerId);
payment.payor_name = model.CustomerName;
payment.total_amount = decimal.Round(decimal.Parse(model.Payment), 2,
MidpointRounding.AwayFromZero);
payment.created_by = Session["UserName"].ToString();
payment.date_created = DateTime.Now;
db.tbl_payment.Add(payment);
await db.SaveChangesAsync();
var result = true;
success = result;
if (result)
{
decimal totalPayment = 0;
decimal amountDuePrincipal = 0;
decimal amountDueInterest = 0;
try
{
totalPayment = decimal.Round(decimal.Parse(model.Payment), 2,
MidpointRounding.AwayFromZero);
amountDuePrincipal = decimal.Round(decimal.Parse(model.AmountDuePrincipal), 2,
MidpointRounding.AwayFromZero);
amountDueInterest = decimal.Round(decimal.Parse(model.AmountDueInterest), 2,
MidpointRounding.AwayFromZero);
}
catch (Exception ex)
{
}
decimal paymentInterest = 0;
decimal paymentPrincipal = 0;
if (amountDueInterest > 0)
{
if (totalPayment >= amountDueInterest)
{
paymentInterest = decimal.Round(amountDueInterest, 2, MidpointRounding.AwayFromZero);
totalPayment = totalPayment - paymentInterest;
}
else if (totalPayment < amountDueInterest)
{
paymentInterest = totalPayment;
totalPayment = 0;
}
}
if (amountDuePrincipal > 0)
{
paymentPrincipal = totalPayment;
totalPayment = 0;
}
if (paymentPrincipal > 0)
{
tbl_payment_details paymentDetailsPrincipal = new tbl_payment_details();
paymentDetailsPrincipal.reference_no = model.PaymentNo;
paymentDetailsPrincipal.payment_type = "OR Payment";
paymentDetailsPrincipal.loan_no = model.LoanNo;
paymentDetailsPrincipal.loan_name = model.LoanName;
paymentDetailsPrincipal.due_date = DateTime.Parse(model.LoanDueDate);
paymentDetailsPrincipal.amount =
decimal.Round(paymentPrincipal, 2, MidpointRounding.AwayFromZero);
paymentDetailsPrincipal.created_by = Session["UserName"].ToString();
paymentDetailsPrincipal.date_created = DateTime.Now;
db.tbl_payment_details.Add(paymentDetailsPrincipal);
await db.SaveChangesAsync();
}
if (paymentInterest > 0)
{
tbl_payment_details paymentDetailsInterest = new tbl_payment_details();
paymentDetailsInterest.reference_no = model.PaymentNo;
paymentDetailsInterest.payment_type = "OR Payment Interest";
paymentDetailsInterest.loan_no = model.LoanNo;
paymentDetailsInterest.loan_name = model.LoanName;
paymentDetailsInterest.due_date = DateTime.Parse(model.LoanDueDate);
paymentDetailsInterest.amount =
decimal.Round(paymentInterest, 2, MidpointRounding.AwayFromZero);
paymentDetailsInterest.created_by = Session["UserName"].ToString();
paymentDetailsInterest.date_created = DateTime.Now;
db.tbl_payment_details.Add(paymentDetailsInterest);
await db.SaveChangesAsync();
}
tbl_loan_ledger loanLedger = new tbl_loan_ledger();
loanLedger.date_trans = DateTime.Parse(model.PaymentDate);
loanLedger.trans_type = "OR Payment";
loanLedger.reference_no = model.PaymentNo;
loanLedger.loan_no = model.LoanNo;
loanLedger.loan_type_name = model.LoanName;
loanLedger.customer_id = int.Parse(model.CustomerId);
loanLedger.customer_name = model.CustomerName;
loanLedger.interest_type = GetInterestType(model.LoanName);
loanLedger.interest_rate = GetInterestRate(model.LoanName);
loanLedger.interest = decimal.Round(paymentInterest, 2, MidpointRounding.AwayFromZero);
loanLedger.amount_paid =
decimal.Round(decimal.Parse(model.Payment), 2, MidpointRounding.AwayFromZero);
loanLedger.principal = decimal.Round(paymentPrincipal, 2, MidpointRounding.AwayFromZero);
loanLedger.balance = 0;
loanLedger.date_created = DateTime.Now;
loanLedger.created_by = Session["UserName"].ToString();
db.tbl_loan_ledger.Add(loanLedger);
await db.SaveChangesAsync();
message = "Successfully saved.";
}
else
{
message = "Error saving data. Duplicate entry.";
}
}
else
{
message = "Error saving data. Duplicate entry.";
}
return(Json(new { success = success, message = message }));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
return(Json(new { success = false, message = "Failed to save." }));
}
}
public JsonResult AddCustomer(tbl_customer_validation model)
{
try
{
db_lendingEntities db = new db_lendingEntities();
tbl_customer tbl = new tbl_customer();
if (model.date_registered != null)
{
tbl.customer_no = model.customer_no;
}
else
{
tbl.date_registered = model.date_registered;
}
//
if (model.lastname != null)
{
tbl.lastname = model.lastname.ToUpper();
}
else
{
}
if (model.firstname != null)
{
tbl.firstname = model.firstname.ToUpper();
}
else
{
}
//
if (model.middlename != null)
{
tbl.middlename = model.middlename.ToUpper();
}
else
{
}
if (model.civil_status != null)
{
tbl.civil_status = model.civil_status;
}
else
{
}
if (model.address != null)
{
tbl.address = model.address.ToUpper();
}
else
{
}
if (model.contact_no != null)
{
tbl.contact_no = model.contact_no;
}
else
{
}
if (model.email != null)
{
tbl.email = model.email;
}
else
{
}
if (model.date_of_birth != null)
{
tbl.date_of_birth = model.date_of_birth;
}
else
{
}
if (model.birth_place != null)
{
tbl.birth_place = model.birth_place.ToUpper();
}
else
{
}
if (model.occupation != null)
{
tbl.occupation = model.occupation.ToUpper();
}
else
{
}
if (model.credit_limit != null)
{
tbl.credit_limit = model.credit_limit;
}
else
{
}
if (model.annual_income != null)
{
tbl.annual_income = model.annual_income;
}
else
{
}
db.tbl_customer.Add(tbl);
db.SaveChanges();
return(Json("Success", JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json("Failed", JsonRequestBehavior.DenyGet));
throw ex;
}
}
public ActionResult Print(String id)
{
try
{
if (Session["UserId"] != null)
{
db = new db_lendingEntities();
List <receiptlist> list = new List <receiptlist>();
ViewBag.receiptno = id.ToString().PadLeft(5, '0');;
ViewBag.receiptdate = DateTime.Now.ToString("MM/dd/yyyy");
decimal total_amount_paid = 0;
var result = from d in db.tbl_payment_details where d.reference_no == id orderby d.loan_no, d.payment_type ascending select d;
foreach (var dt in result)
{
if (ViewBag.borrower == "" || ViewBag.borrower == null)
{
ViewBag.borrower = GetBorrower(id);
}
if (ViewBag.borrower_id == "" || ViewBag.borrower_id == null)
{
ViewBag.borrower_id = GetBorrowerid(id);
}
if (dt.payment_type == "OR Payment")
{
list.Add(new receiptlist {
reference_no = dt.loan_no, particulars = "Principal", amount = String.Format("{0:0.00}", dt.amount)
});
}
else
{
list.Add(new receiptlist {
reference_no = dt.loan_no, particulars = "Interest", amount = String.Format("{0:0.00}", dt.amount)
});
}
total_amount_paid = total_amount_paid + (decimal)dt.amount;
}
ViewBag.total_amount_paid = String.Format("{0:0.00}", total_amount_paid);
ViewBag.receiptdetailslist = list;
db = new db_lendingEntities();
List <receiptbalancelist> list2 = new List <receiptbalancelist>();
int customerid = Convert.ToInt32(GetBorrowerid(id));
var result2 = from d in db.tbl_loan_processing where d.customer_id == customerid select d.loan_no;
foreach (var dt2 in result2)
{
if (DisplayLedgerBalance(dt2, id) > 0)
{
list2.Add(new receiptbalancelist {
loan_no = dt2, balance = String.Format("{0:0.00}", DisplayLedgerBalance(dt2, id))
});
}
}
ViewBag.receiptdetailsbalancelist = list2;
return(PartialView("Print"));
}
else
{
return(RedirectToAction("Login", "Account"));
}
}
catch (Exception)
{
throw;
}
}
public int interestLoop(string id)
{
int result = 0;
try
{
using (db = new db_lendingEntities())
{
DateTime?DueDate = DateTime.Now;
DateTime?latePaymentInterestDate = DateTime.Now;
DateTime?newPaymentInterestDate = DateTime.Now;
Boolean hasLatePaymentInterest = false;
var result1 =
from d in db.tbl_loan_ledger
where d.loan_no.Equals(id)
orderby(d.autonum)
select d;
foreach (var data in result1)
{
switch (data.trans_type)
{
case "Beginning Balance":
DueDate = (DateTime)data.date_trans.Value.AddDays(0);
break;
case "Late Payment Interest":
newPaymentInterestDate = (DateTime)data.date_trans;
latePaymentInterestDate = (DateTime)data.date_trans;
hasLatePaymentInterest = true;
break;
default:
break;
}
}
decimal loopCounter = decimal.ToInt32((_serverDateTime - DueDate).Value.Days);
loopCounter = Convert.ToInt32(Math.Floor(loopCounter / 30));
if (hasLatePaymentInterest == true)
{
latePaymentInterestDate = DueDate.Value.AddDays((double)loopCounter * 30);
}
else
{
latePaymentInterestDate = DueDate.Value.AddDays(0);
}
decimal difference = 0;
difference = (decimal.ToInt32((_serverDateTime - newPaymentInterestDate).Value.Days));
if (difference >= 30)
{
result = Convert.ToInt32(Math.Floor(difference / 30));
}
}
return(result);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public decimal?GetReceivables()
{
try
{
using (db = new db_lendingEntities())
{
var dateVar = DateTime.Now;
decimal?totalBalance = 0;
var result = from d in db.tbl_loan_processing where (d.due_date <= dateVar) && d.status == "Released" orderby d.customer_name ascending select d;
foreach (var dt in result)
{
decimal ledgerBalance = decimal.Round((decimal)GetLedgerBalance(dt.loan_no), 2, MidpointRounding.AwayFromZero);
if (ledgerBalance > 0)
{
decimal adjustment = decimal.Round((decimal)GetAdjustment(dt.loan_no), 2, MidpointRounding.AwayFromZero);
decimal?principal = decimal.Round((decimal)dt.loan_granted, 2, MidpointRounding.AwayFromZero);
decimal?principalInterest = decimal.Round((decimal)(dt.loan_granted * (dt.loan_interest_rate / 100)), 2, MidpointRounding.AwayFromZero);
decimal?interest = decimal.Round((decimal)GetInterest(dt.loan_no), 2, MidpointRounding.AwayFromZero);
decimal?additionalInterest = decimal.Round((decimal)ComputeInterest(dt.loan_no, (decimal)dt.loan_interest_rate, dateVar), 2, MidpointRounding.AwayFromZero);
interest = decimal.Round((decimal)(principalInterest + interest + additionalInterest), 2, MidpointRounding.AwayFromZero);
decimal?payment = decimal.Round((decimal)GetPayments(dt.loan_no), 2, MidpointRounding.AwayFromZero);
if (adjustment < 0)
{
adjustment = adjustment * -1;
if (adjustment >= interest)
{
interest = 0;
adjustment = adjustment - (decimal)interest;
if (adjustment > 0 && principal > adjustment)
{
principal = principal - adjustment;
adjustment = 0;
}
}
else
{
interest = interest - adjustment;
}
}
else
{
interest = interest + adjustment;
}
decimal?balance = decimal.Round((decimal)(principal + interest - payment), 2, MidpointRounding.AwayFromZero);
if (balance > 0)
{
if (GetInterestType(dt.loan_name) == "1")
{
}
else if (GetInterestType(dt.loan_name) == "2")
{
balance = balance - principalInterest;
}
else if (GetInterestType(dt.loan_name) != "1" && GetInterestType(dt.loan_name) != "2")
{
balance = balance - principalInterest;
}
totalBalance = totalBalance + balance;
}
}
}
return(totalBalance);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public ActionResult Save(string id)
{
try
{
using (db = new db_lendingEntities())
{
string message = "";
var result = from d in db.tbl_loan_processing where d.loan_no.Equals(id) select d;
foreach (var dt in result)
{
DateTime newDueDate = (DateTime)dt.due_date;
var dueDate = newDueDate.ToString("MM/dd/yyyy");
DateTime newLoanDate = (DateTime)dt.loan_date;
var loanDate = newLoanDate.ToString("MM/dd/yyyy");
decimal loanBalance = decimal.Round((decimal)GetLedgerBalance(dt.loan_no), 2, MidpointRounding.AwayFromZero);
decimal restructuredInterest = 0;
decimal restructuredInterestTotal = 0;
decimal newLoanBalance = loanBalance;
int loop = interestLoop(dt.loan_no);
for (int i = 0; i < loop; i++)
{
restructuredInterest = decimal.Round(newLoanBalance * ((decimal)dt.loan_interest_rate / 100), 2, MidpointRounding.AwayFromZero);
newLoanBalance = newLoanBalance + restructuredInterest;
restructuredInterestTotal = restructuredInterestTotal + restructuredInterest;
}
if (loanBalance > 0)
{
db_lendingEntities dbSave = new db_lendingEntities();
tbl_loan_ledger tbl = new tbl_loan_ledger();
tbl.date_trans = _serverDateTime;
tbl.trans_type = "Late Payment Interest";
tbl.reference_no = "";
tbl.loan_no = dt.loan_no;
tbl.loan_type_name = dt.loan_name;
tbl.customer_id = dt.customer_id;
tbl.customer_name = dt.customer_name.ToUpper();
tbl.interest_type = GetInterestType(dt.loan_name);
tbl.interest_rate = dt.loan_interest_rate;
tbl.interest = restructuredInterestTotal;
tbl.amount_paid = 0;
tbl.principal = 0;
tbl.balance = 0;
tbl.date_created = DateTime.Now;
tbl.created_by = Session["UserName"].ToString();
dbSave.tbl_loan_ledger.Add(tbl);
dbSave.SaveChanges();
message = "Success!";
}
else
{
message = "Failed!";
}
}
return(Json(new { success = true, message = message }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public async Task <JsonResult> Save(tbl_adjustment model)
{
try
{
tbl_adjustment adjustment = new tbl_adjustment();
var adjustmentNo = 0;
using (db = new db_lendingEntities())
{
var count = db.tbl_adjustment.ToList();
if (count.Count != 0)
{
adjustmentNo = db.tbl_adjustment.Max(a => a.Autonum) + 1;
}
else
{
adjustmentNo = 1;
}
}
bool success = false;
string message = "";
using (db_lendingEntities dbSave = new db_lendingEntities())
{
adjustment.TransType = model.TransType;
adjustment.DateTrans = model.DateTrans;
adjustment.LoanNo = model.LoanNo;
adjustment.Remarks = model.Remarks;
adjustment.Amount = decimal.Round((decimal)model.Amount, 2, MidpointRounding.AwayFromZero);
adjustment.CreatedBy = Session["UserName"].ToString();
adjustment.CreatedAt = DateTime.Now;
dbSave.tbl_adjustment.Add(adjustment);
await dbSave.SaveChangesAsync();
var result = true;
success = result;
if (result)
{
var customerId = 0;
var customerName = "";
var loanName = "";
using (db = new db_lendingEntities())
{
var loan = from d in db.tbl_loan_processing where d.loan_no == model.LoanNo && d.status == "Released" select d;
foreach (var dt in loan)
{
customerId = (int)dt.customer_id;
customerName = dt.customer_name;
loanName = dt.loan_name;
}
}
tbl_loan_ledger loanLedger = new tbl_loan_ledger();
loanLedger.date_trans = model.DateTrans;
loanLedger.trans_type = model.TransType;
loanLedger.reference_no = adjustmentNo.ToString();
loanLedger.loan_no = model.LoanNo;
loanLedger.loan_type_name = loanName.ToString();
loanLedger.customer_id = customerId;
loanLedger.customer_name = customerName;
loanLedger.interest_type = GetInterestType(loanName);
loanLedger.interest_rate = GetInterestRate(loanName);
loanLedger.interest = decimal.Round((decimal)model.Amount, 2, MidpointRounding.AwayFromZero);
loanLedger.amount_paid = decimal.Round(0, 2, MidpointRounding.AwayFromZero);
loanLedger.principal = decimal.Round(0, 2, MidpointRounding.AwayFromZero);
loanLedger.balance = 0;
loanLedger.date_created = DateTime.Now;
loanLedger.created_by = Session["UserName"].ToString();
dbSave.tbl_loan_ledger.Add(loanLedger);
await dbSave.SaveChangesAsync();
message = "Successfully saved.";
}
else
{
message = "Error saving data. Duplicate entry.";
}
}
return(Json(new { success = success, message = message }));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}