/*
Frontend page: Fee Page
Title: Getting Fees by due date
Designed: Nadeeka
User story:
Developed: Nadeeka
Date created: 4/21/2016
*/
public List<Fees> GetFeesByDueDate(int loanId, DateTime dueDate, string type)
{
try
{
List<Fees> lstFee = new List<Fees>();
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@loan_id", loanId });
paramertList.Add(new object[] { "@bill_due_date", dueDate });
paramertList.Add(new object[] { "@type", type });
DataSet dataSet = dataHandler.GetDataSet("spGetFeesByDueDate", paramertList);
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
Fees fee = new Fees();
fee.FeeId = int.Parse(dataRow["fee_id"].ToString());
fee.UnitId = dataRow["unit_id"].ToString();
fee.LoanId = int.Parse(dataRow["loan_id"].ToString());
fee.Type = dataRow["type"].ToString();
fee.Amount = Convert.ToDecimal(dataRow["amount"].ToString());
fee.Description = dataRow["description"].ToString();
fee.BillDueDate = Convert.ToDateTime(dataRow["bill_due_date"].ToString());
fee.FeeDueDate = Convert.ToDateTime(dataRow["due_date"].ToString());
if (type == "advanceFee")
{
string[] info = fee.Description.Split(',');
if (info != null && info.Length > 0)
{
if (info[1] != "")
{
fee.IdentificationNumber = info[1];
}
if (info.Length > 2 && info[2] != "")
{
fee.Year = Convert.ToInt32(info[2]);
}
if (info.Length > 3 && info[3] != "")
{
fee.Make = info[3];
}
if (info.Length > 4 && info[4] != "")
{
fee.Model = info[4];
}
if (info.Length > 5 && info[5] != "")
{
fee.AdvanceDate = Convert.ToDateTime(info[5]);
}
else
{
fee.AdvanceDate = Convert.ToDateTime(dataRow["due_date"].ToString());
}
}
}
lstFee.Add(fee);
}
return lstFee;
}
else
{
return null;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Created By: kasun Smarawickrama
/// Date : 10/2/2012
///
/// check the loan inserted values to advance,lot,monthly loan fee tables.
/// </summary>
/// <param name="loanId">loan id</param>
/// <returns>fees Details</returns>
///
public Fees checkLoanIsInFeesTables(int loanId)
{
Fees fees = new Fees();
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@loan_id", loanId });
try
{
DataSet dataSet = dataHandler.GetDataSet("spCheckLoanIsInAdvanceFeeTable", paramertList);
if (dataSet != null && dataSet.Tables.Count != 0 && dataSet.Tables[0].Rows.Count != 0)
{
DataRow dataRow = dataSet.Tables[0].Rows[0];
fees.AdvanceAmount = decimal.Parse(dataRow["advance_fee_amount"].ToString());
fees.AdvanceFeeCalculateType = dataRow["advance_fee_calculate_type"].ToString();
fees.AdvanceNeedReceipt = bool.Parse(dataRow["receipt"].ToString());
fees.AdvanceDue = dataRow["payment_due_method"].ToString();
fees.AdvanceDueDate = dataRow["payment_due_date"].ToString();
fees.AdvanceFeeDealerEmail = dataRow["auto_remind_dealer_email"].ToString();
if (dataRow["delaer_remind_period"] != System.DBNull.Value)
{
fees.AdvanceFeeDealerEmailRemindPeriod = int.Parse(dataRow["delaer_remind_period"].ToString());
}
fees.AdvanceDueEmail = dataRow["due_auto_remind_email"].ToString();
if (dataRow["due_auto_remind_period"] != System.DBNull.Value)
{
fees.AdvanceDueEmailRemindPeriod = int.Parse(dataRow["due_auto_remind_period"].ToString());
}
}
DataSet dataSet1 = dataHandler.GetDataSet("spCheckLoanIsInMonthlyLoanFeeTable", paramertList);
if (dataSet1 != null && dataSet1.Tables.Count != 0 && dataSet1.Tables[0].Rows.Count != 0)
{
DataRow dataRow1 = dataSet1.Tables[0].Rows[0];
fees.MonthlyLoanAmount = decimal.Parse(dataRow1["monthly_loan_fee_amount"].ToString());
fees.MonthlyLoanNeedReceipt = bool.Parse(dataRow1["receipt"].ToString());
fees.MonthlyLoanDue = dataRow1["payment_due_method"].ToString();
fees.MonthlyLoanDueDate = dataRow1["payment_due_date"].ToString();
fees.MonthlyLoanFeeDealerEmail = dataRow1["auto_remind_dealer_email"].ToString();
if (dataRow1["delaer_remind_period"] != System.DBNull.Value)
{
fees.MonthlyLoanFeeDealerEmailRemindPeriod = int.Parse(dataRow1["delaer_remind_period"].ToString());
}
fees.MonthlyLoanDueEmail = dataRow1["due_auto_remind_email"].ToString();
if (dataRow1["due_auto_remind_period"] != System.DBNull.Value)
{
fees.MonthlyLoanDueEmailRemindPeriod = int.Parse(dataRow1["due_auto_remind_period"].ToString());
}
}
DataSet dataSet2 = dataHandler.GetDataSet("spCheckLoanIsInLotInspectionFeeTable", paramertList);
if (dataSet2 != null && dataSet2.Tables.Count != 0 && dataSet2.Tables[0].Rows.Count != 0)
{
DataRow dataRow2 = dataSet2.Tables[0].Rows[0];
fees.LotInspectionAmount = decimal.Parse(dataRow2["lot_inspection_amount"].ToString());
fees.LotInspectionNeedReceipt = bool.Parse(dataRow2["receipt"].ToString());
fees.LotInspectionDue = dataRow2["payment_due_method"].ToString();
fees.LotInspectionDueDate = dataRow2["payment_due_date"].ToString();
fees.LotInspectionFeeDealerEmail = dataRow2["auto_remind_dealer_email"].ToString();
if (dataRow2["delaer_remind_period"] != System.DBNull.Value)
{
fees.LotInspectionFeeDealerEmailRemindPeriod = int.Parse(dataRow2["delaer_remind_period"].ToString());
}
fees.LotInspectionDueEmail = dataRow2["due_auto_remind_email"].ToString();
if (dataRow2["due_auto_remind_period"] != System.DBNull.Value)
{
fees.LotInspectionDueEmailRemindPeriod = int.Parse(dataRow2["due_auto_remind_period"].ToString());
}
}
return fees;
}
catch (Exception ex)
{
throw ex;
}
}
public ActionResult Step8()
{
var userId = userData.UserId;
BranchAccess branch = new BranchAccess();
//check user is user or dealer user
if (userData.RoleId >= 3)
{
return RedirectToAction("UserLogin", "Login", new { lbl = "You are not Allowed." });
}
//get company type
int companyType = branch.getCompanyTypeByUserId(userId);
//check lender company
if (companyType == 1)
{
ViewBag.isLender = true;
}
//check dealer company
else
{
ViewBag.isLender = false;
}
Fees fee = new Fees();
LoanSetupAccess loan = new LoanSetupAccess();
fee.LoanId = loanData.loanId;
//check insert or update
if (loanData.stepId > 3)
{
ViewBag.isEdit = "editable";
//get fee details
var hasLoan = loan.checkLoanIsInFeesTables(fee.LoanId);
//check advance amount
if (hasLoan.AdvanceAmount == 0)
{
hasLoan.AdvanceId = "2";
}
else {
hasLoan.AdvanceId = "1";
}
if (hasLoan.MonthlyLoanAmount == 0)
{
hasLoan.MonthlyLoanId = "2";
}
else {
hasLoan.MonthlyLoanId = "1";
}
if (hasLoan.LotInspectionAmount == 0)
{
hasLoan.LotInspectionId = "2";
}
else {
hasLoan.LotInspectionId = "1";
}
if (hasLoan.AdvanceAmount > 0 || hasLoan.MonthlyLoanAmount > 0 || hasLoan.LotInspectionAmount > 0)
{
hasLoan.LoanId = fee.LoanId;
hasLoan.isEdit = true;
//hasLoan.IsAdvanceFeeCompleteEmailReminder = false;
//hasLoan.IsLoanFeeCompleteEmailReminder = false;
//hasLoan.IsLotFeeCompleteEmailReminder = false;
hasLoan.IsAdvanceFeeDueEmailReminder = false;
hasLoan.IsLoanFeeDueEmailReminder = false;
hasLoan.IsLotFeeDueEmailReminder = false;
//if (hasLoan.AdvanceFeeDealerEmail != "")
//{
// hasLoan.IsAdvanceFeeCompleteEmailReminder = true;
//}
//if (hasLoan.MonthlyLoanFeeDealerEmail != "")
//{
// hasLoan.IsLoanFeeCompleteEmailReminder = true;
//}
//if (hasLoan.LotInspectionFeeDealerEmail != "")
//{
// hasLoan.IsLotFeeCompleteEmailReminder = true;
//}
if (hasLoan.AdvanceDueEmail != "")
{
hasLoan.IsAdvanceFeeDueEmailReminder = true;
}
if (hasLoan.MonthlyLoanDueEmail != "")
{
hasLoan.IsLoanFeeDueEmailReminder = true;
}
if (hasLoan.LotInspectionDueEmail != "")
{
hasLoan.IsLotFeeDueEmailReminder = true;
}
}
if (HttpContext.Request.IsAjaxRequest())
{
ViewBag.AjaxRequest = 1;
return PartialView(hasLoan);
}
else
{
return View(hasLoan);
}
}
else
{
ViewBag.isEdit = "notEditable";
Fees feeNew = new Fees();
feeNew.LoanId = fee.LoanId;
if (feeNew.LoanId > 0)
{
feeNew.isEdit = false;
var email = loan.getAutoRemindEmailByLoanId(feeNew.LoanId);
if (email != null)
{
feeNew.AdvanceDueEmail = email;
feeNew.MonthlyLoanDueEmail = email;
feeNew.LotInspectionDueEmail = email;
}
//feeNew.IsAdvanceFeeCompleteEmailReminder = false;
//feeNew.IsLotFeeCompleteEmailReminder = false;
//feeNew.IsLoanFeeCompleteEmailReminder = false;
feeNew.IsAdvanceFeeDueEmailReminder = false;
feeNew.IsLotFeeDueEmailReminder = false;
feeNew.IsLoanFeeDueEmailReminder = false;
if (HttpContext.Request.IsAjaxRequest())
{
ViewBag.AjaxRequest = 1;
return PartialView(feeNew);
}
else
{
return View(feeNew);
}
}
else
{
return RedirectToAction("Step7");
}
}
}
public ActionResult Step8(Fees fees)
{
if (userData.RoleId >= 3)
{
return RedirectToAction("UserLogin", "Login", new { lbl = "You are not Allowed." });
}
StepAccess step = new StepAccess();
if (fees.AdvanceDue == "Vehicle Payoff")
{
fees.AdvanceDueDate = "VP";
}
if (fees.AdvanceDue == "Time of Advance")
{
fees.AdvanceDueDate = "ToA";
}
//if (fees.IsAdvanceFeeCompleteEmailReminder == false)
//{
// fees.AdvanceFeeDealerEmail = "";
// fees.AdvanceFeeDealerEmailRemindPeriod = 0;
//}
//if (fees.IsLoanFeeCompleteEmailReminder == false)
//{
// fees.MonthlyLoanFeeDealerEmail = "";
// fees.MonthlyLoanFeeDealerEmailRemindPeriod = 0;
//}
//if (fees.IsLotFeeCompleteEmailReminder == false)
//{
// fees.LotInspectionFeeDealerEmail = "";
// fees.LotInspectionFeeDealerEmailRemindPeriod = 0;
//}
if (fees.IsAdvanceFeeDueEmailReminder == false)
{
fees.AdvanceDueEmail = "";
fees.AdvanceDueEmailRemindPeriod = 0;
}
if (fees.IsLoanFeeDueEmailReminder == false)
{
fees.MonthlyLoanDueEmail = "";
fees.MonthlyLoanDueEmailRemindPeriod = 0;
}
if (fees.IsLotFeeDueEmailReminder == false)
{
fees.LotInspectionDueEmail = "";
fees.LotInspectionDueEmailRemindPeriod = 0;
}
fees.LoanId = loanData.loanId;
// after hide the due methods
fees.MonthlyLoanDue = "Once a Month";
fees.LotInspectionDue = "Monthly";
if (step.InsertFeesDetails(fees))
{
var userId = userData.UserId;
var branchId = loanData.loanId;
if (fees.isEdit == true)
{
return RedirectToAction("Step9");
}
else if (step.UpdateLoanSetupStep(userData.UserId, loanData.CompanyId, loanData.BranchId, loanData.nonRegisteredBranchId, loanData.loanId, 4))
{
if (loanData.stepId < 4)
{
loanData.stepId = 4;
Log log = new Log(userData.UserId, userData.Company_Id, loanData.BranchId, fees.LoanId, "Fees", "Inserted fees details of loan : " + fees.LoanId, DateTime.Now);
int islog = (new LogAccess()).InsertLog(log);
}
else
{
Log log = new Log(userData.UserId, userData.Company_Id, loanData.BranchId, fees.LoanId, "Fees", "Edited fees details of loan : " + fees.LoanId, DateTime.Now);
int islog = (new LogAccess()).InsertLog(log);
}
Session["loanStep"] = loanData;
return RedirectToAction("Step9");
}
else
{
return RedirectToAction("Step8");
}
}
else
{
return RedirectToAction("Step8");
}
}
public bool InsertFeesDetails(Fees fees)
{
using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["AutoDealersConnection"].ToString()))
{
try
{
using (SqlCommand command = new SqlCommand("spInsertFeesDetails", con))
{
command.CommandType = CommandType.StoredProcedure;
if (fees.AdvanceId == "1")
{
command.Parameters.Add("@advance_id", SqlDbType.Bit).Value = true;
}
else {
command.Parameters.Add("@advance_id", SqlDbType.Bit).Value = false;
}
command.Parameters.Add("@advance_fee_amount", SqlDbType.Decimal).Value = fees.AdvanceAmount;
command.Parameters.Add("@advance_fee_calculate_type", SqlDbType.NVarChar).Value = fees.AdvanceFeeCalculateType;
command.Parameters.Add("@advance_receipt", SqlDbType.Bit).Value = fees.AdvanceNeedReceipt;
command.Parameters.Add("@advance_payment_due_method", SqlDbType.NVarChar).Value = fees.AdvanceDue;
command.Parameters.Add("@advance_payment_due_date", SqlDbType.NVarChar).Value = fees.AdvanceDueDate;
command.Parameters.Add("@advance_auto_remind_dealer_email", SqlDbType.NVarChar).Value = fees.AdvanceFeeDealerEmail;
command.Parameters.Add("@advance_delaer_remind_period", SqlDbType.NVarChar).Value = fees.AdvanceFeeDealerEmailRemindPeriod;
command.Parameters.Add("@advance_due_auto_remind_email", SqlDbType.NVarChar).Value = fees.AdvanceDueEmail;
command.Parameters.Add("@advance_due_auto_remind_period", SqlDbType.NVarChar).Value = fees.AdvanceDueEmailRemindPeriod;
if (fees.MonthlyLoanId == "1")
{
command.Parameters.Add("@monthly_loan_id", SqlDbType.Bit).Value = true;
}
else {
command.Parameters.Add("@monthly_loan_id", SqlDbType.Bit).Value = false;
}
command.Parameters.Add("@monthly_loan_fee_amount", SqlDbType.Decimal).Value = fees.MonthlyLoanAmount;
command.Parameters.Add("@monthly_loan_receipt", SqlDbType.Bit).Value = fees.MonthlyLoanNeedReceipt;
command.Parameters.Add("@monthly_loan_payment_due_method", SqlDbType.NVarChar).Value = fees.MonthlyLoanDue;
command.Parameters.Add("@monthly_loan_payment_due_date", SqlDbType.NVarChar).Value = fees.MonthlyLoanDueDate;
command.Parameters.Add("@monthly_loan_auto_remind_dealer_email", SqlDbType.NVarChar).Value = fees.MonthlyLoanFeeDealerEmail;
command.Parameters.Add("@monthly_loan_delaer_remind_period", SqlDbType.NVarChar).Value = fees.MonthlyLoanFeeDealerEmailRemindPeriod;
command.Parameters.Add("@monthly_loan_due_auto_remind_email", SqlDbType.NVarChar).Value = fees.MonthlyLoanDueEmail;
command.Parameters.Add("@monthly_loan_due_auto_remind_period", SqlDbType.NVarChar).Value = fees.MonthlyLoanDueEmailRemindPeriod;
if (fees.LotInspectionId == "1")
{
command.Parameters.Add("@lot_inspection_id", SqlDbType.Bit).Value = true;
}
else {
command.Parameters.Add("@lot_inspection_id", SqlDbType.Bit).Value = false;
}
command.Parameters.Add("@lot_inspection_amount", SqlDbType.Decimal).Value = fees.LotInspectionAmount;
command.Parameters.Add("@lot_inspection_receipt", SqlDbType.Bit).Value = fees.LotInspectionNeedReceipt;
command.Parameters.Add("@lot_payment_due_method", SqlDbType.NVarChar).Value = fees.LotInspectionDue;
command.Parameters.Add("@lot_payment_due_date", SqlDbType.NVarChar).Value = fees.LotInspectionDueDate;
command.Parameters.Add("@lot_inspection_auto_remind_dealer_email", SqlDbType.NVarChar).Value = fees.LotInspectionFeeDealerEmail;
command.Parameters.Add("@lot_inspection_delaer_remind_period", SqlDbType.NVarChar).Value = fees.LotInspectionFeeDealerEmailRemindPeriod;
command.Parameters.Add("@lot_inspection_due_auto_remind_email", SqlDbType.NVarChar).Value = fees.LotInspectionDueEmail;
command.Parameters.Add("@lot_inspection_due_auto_remind_period", SqlDbType.NVarChar).Value = fees.LotInspectionDueEmailRemindPeriod;
command.Parameters.Add("@loan_id", SqlDbType.Int).Value = fees.LoanId;
SqlParameter returnParameter = command.Parameters.Add("@ReturnValue", SqlDbType.Bit);
returnParameter.Direction = ParameterDirection.ReturnValue;
con.Open();
command.ExecuteNonQuery();
if ((int)returnParameter.Value == 1)
{
return true;
}
else {
return false;
}
}
}
catch (Exception ex)
{
throw ex;
}
}
}