//Copy Constructor
public Component(Component copyComponent)
{
actualLoanInformation_ = new LoanInformation();
ComponentID = copyComponent.ComponentID;
ComponentName = copyComponent.ComponentName;
Category = copyComponent.Category;
Datasheet = copyComponent.Datasheet;
UserComment = copyComponent.UserComment;
AdminComment = copyComponent.AdminComment;
ComponentNumber = copyComponent.ComponentNumber;
Image = copyComponent.Image;
ComponentInfo = copyComponent.ComponentInfo;
SerieNr = copyComponent.SerieNr;
ManufacturerLink = copyComponent.ManufacturerLink;
ActualLoanInformation.LoanID = copyComponent.ActualLoanInformation.LoanID;
ActualLoanInformation.LoanDate = copyComponent.ActualLoanInformation.LoanDate;
ActualLoanInformation.OwnerID = copyComponent.ActualLoanInformation.OwnerID;
ActualLoanInformation.ReservationID = copyComponent.ActualLoanInformation.ReservationID;
ActualLoanInformation.ReturnDate = copyComponent.ActualLoanInformation.ReturnDate;
ActualLoanInformation.Component = copyComponent.ActualLoanInformation.Component;
ActualLoanInformation.IsEmailSend = copyComponent.ActualLoanInformation.IsEmailSend;
ActualLoanInformation.ReservationDate = copyComponent.ActualLoanInformation.ReservationDate;
ActualLoanInformation.MobilNr = copyComponent.ActualLoanInformation.MobilNr;
}
// GET: CategoryComponent/Loan/5
public ActionResult Loan(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
// Check if the specific component is not already loaned.
var loanInformation = db.LoanInformations.SingleOrDefault(r => r.SpecificComponentId == id);
if (loanInformation == null)
{
ViewBag.IsComponentAvailable = true;
loanInformation = new LoanInformation();
var specificComponent = db.SpecificComponents.Find(id);
loanInformation.SpecificComponentId = specificComponent.Id;
// To display the data about the component, the student wants to loan.
loanInformation.SpecificComponent = specificComponent;
}
else
{
ViewBag.IsComponentAvailable = false;
var specificComponent = loanInformation.SpecificComponent;
var component = loanInformation.SpecificComponent.Component1;
ViewBag.ComponentName = component.Name;
ViewBag.ComponentNumber = specificComponent.Number;
ViewBag.ComponentSerieNr = specificComponent.SerieNr;
}
ViewBag.ReservationId = new SelectList(db.Students, "Id", "StudentId");
return(View(loanInformation));
}
public ActionResult Edit(string listingID, bool Internal = false)
{
var serialization = new Serialization();
var listingsBA = new Listings();
var HashCriteria = new Hashtable();
var listingsModel = new ListingModel();
var listing_ID = Convert.ToInt64(CipherTool.DecryptString(listingID));
listingsModel.loanInformation = new LoanInformation();
var dropdownValues = new LoanInformation();
HashCriteria.Add("UserID", userID);
HashCriteria.Add("ID", listing_ID);
HashCriteria.Add("CurrentTab", "LoanInformation");
var actualCriteria = serialization.SerializeBinary((object)HashCriteria);
var result = listingsBA.EditCurrentListing(actualCriteria);
var listingEditModel = (LoanInformation)(serialization.DeSerializeBinary(Convert.ToString(result)));
var defaultValues = listingsBA.GetListingDefaultValuesForLoanInformation();
dropdownValues = (LoanInformation)(serialization.DeSerializeBinary(Convert.ToString(defaultValues)));
listingEditModel.ListingType = GetListingLoanType();
listingEditModel.RateType = dropdownValues.RateType;
listingEditModel.AmortizationType = dropdownValues.AmortizationType;
listingEditModel.PaymentsFrequency = dropdownValues.PaymentsFrequency;
listingEditModel.LienPositionType = dropdownValues.LienPositionType;
listingEditModel.JuniorTrustDeedORMortgageList = dropdownValues.JuniorTrustDeedORMortgageList;
listingsModel.loanInformation = listingEditModel;
if (Internal)
{
return(PartialView("_LoanInformation", listingsModel.loanInformation));
}
return(View("AddListing", listingsModel));
}
public void PrintLoanInformation(LoanInformation loanInformation)
{
Console.WriteLine();
Console.WriteLine("RESULTS:");
Console.WriteLine($"Current loan: {Math.Round(loanInformation.LoanSum, 2)}");
Console.WriteLine($"Amount to be returned from client: {Math.Round(loanInformation.LoanSumToReturn, 2)}");
Console.WriteLine($"Monthly payment for the loan: {Math.Round(loanInformation.MonthlyPayment, 2)}");
Console.WriteLine($"Interest rate: {Math.Round(loanInformation.InterestRate, 2) * 100}%");
Console.WriteLine($"Effective annual interest rate (lt. BVKKMN): {Math.Round(loanInformation.EffectiveAnnualInterestRate, 2) * 100}%");
Console.WriteLine();
Console.WriteLine("LOAN PAYMENT TABLE:");
Console.WriteLine("{0,8} {1,12} {2,18} {3,14} {4,14}", "Month", "Date",
"Interest payment", "Loan payment", "Loan residue");
for (var paymentId = 0; paymentId < loanInformation.Payments.Count; paymentId++)
{
Console.WriteLine("{0,8} {1,12} {2,18} {3,14} {4,14}", paymentId + 1,
loanInformation.Payments[paymentId].PaymentDate.ToString("yyyy-MM-dd"),
Math.Round(loanInformation.Payments[paymentId].CurrentInterestPaymentAmount, 2),
Math.Round(loanInformation.Payments[paymentId].CurrentLoanPaymentAmount, 2),
Math.Round(loanInformation.Payments[paymentId].LoanLeftToPay, 2));
}
Console.WriteLine();
}
public RepaymentSchedule GetRepaymentSchedule(LoanInformation loanInformation)
{
var repaymentSchedule = new RepaymentSchedule()
{
Installments = new List <Installment>()
};
var payment = PaymentHelpers.GetPayment(loanInformation.Amount, loanInformation.NumberOfPayments, loanInformation.APR);
var amountDue = loanInformation.Amount;
for (var i = 0; i < loanInformation.NumberOfPayments; i++)
{
var installment = new Installment
{
InstallmentNumber = i + 1
};
installment.AmountDue = amountDue;
installment.Interest = PaymentHelpers.GetInterest(amountDue, loanInformation.NumberOfPayments, loanInformation.APR);
installment.Principal = payment - installment.Interest;
amountDue -= installment.Principal;
repaymentSchedule.Installments.Add(installment);
}
return(repaymentSchedule);
}
public ActionResult DeleteConfirmed(int id)
{
LoanInformation loanInformation = db.LoanInformation.Find(id);
db.LoanInformation.Remove(loanInformation);
db.SaveChanges();
return(RedirectToAction("Index"));
}
public bool SaveLoanInformation([FromBody] LoanInformation information)
{
if (information.Id == Guid.Empty)
{
information.Id = Guid.NewGuid();
}
return(_loanRepository.SaveLoan(information));
}
public LoanSummary GetLoanSummary(LoanInformation loanInformation)
{
var loanSummary = new LoanSummary
{
WeeklyRepayment = PaymentHelpers.GetPayment(loanInformation.Amount, loanInformation.NumberOfPayments, loanInformation.APR)
};
loanSummary.TotalRepaid = loanSummary.WeeklyRepayment * loanInformation.NumberOfPayments;
loanSummary.TotalInterest = loanSummary.TotalRepaid - loanInformation.Amount;
return(loanSummary);
}
public ActionResult Edit([Bind(Include = "LoanId,ComponentNr,LoanDate,ReturnDate,IsEmailSent,ReservationDate,OwnerId,ReservationId,StudentId")] LoanInformation loanInformation)
{
if (ModelState.IsValid)
{
db.Entry(loanInformation).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
ViewBag.ComponentNr = new SelectList(db.Components, "ID", "SerieNr", loanInformation.ComponentNr);
ViewBag.StudentId = new SelectList(db.UserInformation, "StudentId", "FullName", loanInformation.StudentId);
return(View(loanInformation));
}
// GET: LoanInformations/Details/5
public ActionResult Details(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
LoanInformation loanInformation = db.LoanInformation.Find(id);
if (loanInformation == null)
{
return(HttpNotFound());
}
return(View(loanInformation));
}
public void LoanSummaryTest_With_Default_NumberOfPayments()
{
var loanInformation = new LoanInformation()
{
Amount = 40000,
APR = 10,
};
var loanService = new LoanService();
var loanSummary = loanService.GetLoanSummary(loanInformation);
Assert.Equal(809.07m, loanSummary.WeeklyRepayment);
Assert.Equal(42071.64m, loanSummary.TotalRepaid);
Assert.Equal(2071.64m, loanSummary.TotalInterest);
}
public ActionResult SaveLoanInformation(LoanInformation model)
{
var serialization = new Serialization();
var listingsBA = new Listings();
long listingID = 0;
if (Request.Form["ginfo-hdn"] != null && Request.Form["ginfo-hdn"] != "")
{
JavaScriptSerializer js = new JavaScriptSerializer();
model.GuarantorInformation = js.Deserialize <List <GuarantorInformation> >(Request.Form["ginfo-hdn"]);
}
if (model.IsCompany)
{
ModelState.Remove("PrimaryBorrowerFirstName");
ModelState.Remove("LastName");
}
else
{
ModelState.Remove("CompanyName");
}
if (ModelState.IsValid)
{
var HashCriteria = SetHashCriteriaForLoanInformation(model);
var actualCriteria = serialization.SerializeBinary((object)HashCriteria);
var result = listingsBA.SaveLoanInformation(actualCriteria);
listingID = Convert.ToInt64(serialization.DeSerializeBinary(Convert.ToString(result)));
}
else
{
LoanInformation loanInformation = new LoanInformation();
var result = listingsBA.GetListingDefaultValuesForLoanInformation();
loanInformation = (LoanInformation)(serialization.DeSerializeBinary(Convert.ToString(result)));
loanInformation.ListingType = GetListingLoanType();
loanInformation.IsGuaranteed = model.IsGuaranteed;
loanInformation.GuarantorInformation = model.GuarantorInformation;
loanInformation.IsCompany = model.IsCompany;
//loanInformation.PrimaryBorrowerFirstName = model.PrimaryBorrowerFirstName;
//loanInformation.LastName = model.LastName;
return(PartialView("_LoanInformation", loanInformation));
}
string ListingID = CipherTool.EncryptString(Convert.ToString(listingID), true);
var jsonResult = new[] {
new { ListingID = ListingID, ID = listingID },
};
return(Json(jsonResult, JsonRequestBehavior.AllowGet));
}
public ActionResult Loan([Bind(Include = "Id,LoanDate,ReturnDate,IsEmailSend,ReservationDate,ReservationId,SpecificComponentId")] LoanInformation loanInformation)
{
if (ModelState.IsValid)
{
loanInformation.LoanDate = DateTime.Now;
db.LoanInformations.Add(loanInformation);
db.SaveChanges();
return(RedirectToAction("Index"));
}
ViewBag.ReservationId = new SelectList(db.Students, "Id", "StudentId");
return(View(loanInformation));
}
public void LoanSummaryTest()
{
var loanInformation = new LoanInformation()
{
Amount = 50000,
APR = 19,
NumberOfPayments = 52,
};
var loanService = new LoanService();
var loanSummary = loanService.GetLoanSummary(loanInformation);
Assert.Equal(1057.53m, loanSummary.WeeklyRepayment);
Assert.Equal(54991.56m, loanSummary.TotalRepaid);
Assert.Equal(4991.56m, loanSummary.TotalInterest);
}
public bool SaveLoan(LoanInformation loan)
{
var conn = RedisConnection.Connection;
var db = conn.GetDatabase(2);
db.StringSet(loan.Id.ToString(), JsonConvert.SerializeObject(loan));
db.ListRemove("SendEmail", loan.Id.ToString());
db.ListRemove(loan.Email, loan.Id.ToString());
if (loan.SendEmail)
{
db.ListLeftPush("SendEmail", loan.Id.ToString());
}
db.ListLeftPush(loan.Email, loan.Id.ToString());
return(true);
}
public LoanInformation GetListingDefaultValuesForLoanInformation()
{
LoanInformation lstListingModel = new LoanInformation();
DBUtility objUtility = new DBUtility();
_cmd = new SqlCommand();
var _ds = new DataSet();
_cmd.CommandType = CommandType.StoredProcedure;
_cmd.CommandText = "GP_SP_GetListingDefaultValuesForLoanInformation";
_ds = objUtility.FillDataSet(_cmd, _ds);
lstListingModel.RateType = new List <CommonList>();
lstListingModel.AmortizationType = new List <CommonList>();
lstListingModel.PaymentsFrequency = new List <CommonList>();
lstListingModel.LienPositionType = new List <CommonList>();
lstListingModel.JuniorTrustDeedORMortgageList = new List <CommonList>();
for (int i = 0; i < _ds.Tables.Count; i++)
{
foreach (DataRow dr in _ds.Tables[i].Rows)
{
CommonList temp = new CommonList();
temp.ID = Convert.ToInt32(dr["ID"]);
temp.Name = Convert.ToString(dr["Name"]);
if (i == 0)
{
lstListingModel.RateType.Add(temp);
}
if (i == 1)
{
lstListingModel.AmortizationType.Add(temp);
}
if (i == 2)
{
lstListingModel.PaymentsFrequency.Add(temp);
}
if (i == 3)
{
lstListingModel.LienPositionType.Add(temp);
}
if (i == 4)
{
lstListingModel.JuniorTrustDeedORMortgageList.Add(temp);
}
}
}
return(lstListingModel);
}
// GET: LoanInformations/Edit/5
public ActionResult Edit(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
LoanInformation loanInformation = db.LoanInformation.Find(id);
if (loanInformation == null)
{
return(HttpNotFound());
}
ViewBag.ComponentNr = new SelectList(db.Components, "ID", "SerieNr", loanInformation.ComponentNr);
ViewBag.StudentId = new SelectList(db.UserInformation, "StudentId", "FullName", loanInformation.StudentId);
return(View(loanInformation));
}
public ActionResult AddListing()
{
Session["ListingDocuments"] = null;
Session["ListingImages"] = null;
var serialization = new Serialization();
var listingsBA = new Listings();
var listingModel = new ListingModel();
listingModel.loanInformation = new LoanInformation();
var loanInformation = new LoanInformation();
var result = listingsBA.GetListingDefaultValuesForLoanInformation();
loanInformation = (LoanInformation)(serialization.DeSerializeBinary(Convert.ToString(result)));
loanInformation.ListingType = GetListingLoanType();
listingModel.loanInformation = loanInformation;
return(View(listingModel));
}
public LoanInformation CalculateLoanInformation(decimal loanSum, int loanDuration, decimal interestRate)
{
var decimalInterestRate = interestRate / 100;
var monthlyInterestRate = (double)decimalInterestRate / 12;
var loanMonthlyPayment = CalculateMonthlyPayment(loanSum, loanDuration, monthlyInterestRate);
var effectiveAnnualInterestRate = CalculateEffectiveAnnualInterestRate(monthlyInterestRate);
var loanInformation = new LoanInformation()
{
LoanSum = loanSum,
InterestRate = decimalInterestRate,
MonthlyPayment = loanMonthlyPayment,
EffectiveAnnualInterestRate = effectiveAnnualInterestRate,
LoanSumToReturn = loanMonthlyPayment * loanDuration,
Payments = CalculateLoanPayments(loanSum, loanDuration, monthlyInterestRate, loanMonthlyPayment)
};
return(loanInformation);
}
public void RepaymentScheduleTest_With_Default_NumberOfPayments()
{
var loanInformation = new LoanInformation()
{
Amount = 30000,
APR = 10,
};
var loanService = new LoanService();
var repaymentSchedule = loanService.GetRepaymentSchedule(loanInformation);
Assert.Equal(loanInformation.NumberOfPayments, repaymentSchedule.Installments.Count);
Assert.Equal(30000m, repaymentSchedule.Installments[0].AmountDue);
Assert.Equal(549.11m, repaymentSchedule.Installments[0].Principal);
Assert.Equal(57.69m, repaymentSchedule.Installments[0].Interest);
Assert.Equal(15950.82m, repaymentSchedule.Installments[25].AmountDue);
Assert.Equal(576.13m, repaymentSchedule.Installments[25].Principal);
Assert.Equal(30.67m, repaymentSchedule.Installments[25].Interest);
Assert.Equal(605.89m, repaymentSchedule.Installments[51].AmountDue);
Assert.Equal(605.63m, repaymentSchedule.Installments[51].Principal);
Assert.Equal(1.17m, repaymentSchedule.Installments[51].Interest);
}
public void RepaymentScheduleTest()
{
var loanInformation = new LoanInformation()
{
Amount = 50000,
APR = 19,
NumberOfPayments = 52,
};
var loanService = new LoanService();
var repaymentSchedule = loanService.GetRepaymentSchedule(loanInformation);
Assert.Equal(loanInformation.NumberOfPayments, repaymentSchedule.Installments.Count);
Assert.Equal(50000m, repaymentSchedule.Installments[0].AmountDue);
Assert.Equal(874.84m, repaymentSchedule.Installments[0].Principal);
Assert.Equal(182.69m, repaymentSchedule.Installments[0].Interest);
Assert.Equal(27142.67m, repaymentSchedule.Installments[25].AmountDue);
Assert.Equal(958.35m, repaymentSchedule.Installments[25].Principal);
Assert.Equal(99.18m, repaymentSchedule.Installments[25].Interest);
Assert.Equal(1053.45m, repaymentSchedule.Installments[51].AmountDue);
Assert.Equal(1053.68m, repaymentSchedule.Installments[51].Principal);
Assert.Equal(3.85m, repaymentSchedule.Installments[51].Interest);
}
public ActionResult AddLoan(LoanViewModel model)
{
var loaner = AutoMapper.Mapper.Map <Loaner>(model);
var component = _componentRepo.GetByID(model.ComponentId);
component.Loaner = loaner;
_componentRepo.Update(component);
var loanInformation = new LoanInformation
{
ComponentId = model.ComponentId,
IsEmailSent = false,
LoanDate = DateTime.UtcNow,
ReturnDate = model.ReturnDate,
// Not sure what to do with this property, so for now it will be set to UtcNow
ReservationDate = DateTime.UtcNow,
LoanOwnerId = loaner.Id
};
_loanInformationRepo.Insert(loanInformation);
return(RedirectToAction("ShowComponents", "Component"));
}
public void RepaymentScheduleTest_With_MonthlyPayment()
{
var loanInformation = new LoanInformation()
{
Amount = 20000,
APR = 10,
NumberOfPayments = 12,
};
var loanService = new LoanService();
var repaymentSchedule = loanService.GetRepaymentSchedule(loanInformation);
Assert.Equal(loanInformation.NumberOfPayments, repaymentSchedule.Installments.Count);
Assert.Equal(20000m, repaymentSchedule.Installments[0].AmountDue);
Assert.Equal(1591.65m, repaymentSchedule.Installments[0].Principal);
Assert.Equal(166.67m, repaymentSchedule.Installments[0].Interest);
Assert.Equal(11907.98m, repaymentSchedule.Installments[5].AmountDue);
Assert.Equal(1659.09m, repaymentSchedule.Installments[5].Principal);
Assert.Equal(99.23m, repaymentSchedule.Installments[5].Interest);
Assert.Equal(1743.76m, repaymentSchedule.Installments[11].AmountDue);
Assert.Equal(1743.79m, repaymentSchedule.Installments[11].Principal);
Assert.Equal(14.53m, repaymentSchedule.Installments[11].Interest);
}
public ActionResult EditLoanInformation(int componentId, int specificId, LoanInformation loanInformation)
{
if (ModelState.IsValid)
{
Component component = db.Components.Include(x => x.SpecificComponent).Include("SpecificComponent.LoanInformation.User").First(x => x.Id == componentId);
SpecificComponent spComp = component.SpecificComponent.First(x => x.Id == specificId);
if (loanInformation.User == null)
{
int UserId = spComp.LoanInformation.User.Id;
db.LoanInformations.Remove(spComp.LoanInformation);
db.Users.Remove(db.Users.First(x => x.Id == UserId));
}
else
{
spComp.LoanInformation = loanInformation;
}
db.Entry(component).State = EntityState.Modified;
db.SaveChanges();
}
return(RedirectToAction("Lend", new { id = componentId }));
}
public IActionResult LoanSummary([FromQuery] LoanInformation loanInformation)
{
var result = _loanService.GetLoanSummary(loanInformation);
return(new JsonResult(result));
}
public bool CreateLoanInformation(LoanInformation loan, Component c)
{
try
{
// Open the connection
_conn.Open();
// prepare command string using paramters in string and returning the given identity
string insertStringParam =
@"INSERT INTO [LoanInformation] (Component, LoanDate, ReturnDate, IsEmailSend, ReservationDate, MobilNr, OwnerID, ReservationID)
OUTPUT INSERTED.LoanID
VALUES (@Data1, @Data2,@Data3,@Data4,@Data5,@Data6,@Data7,@Data8)";
object loanDateParam = DBNull.Value;
object returnDateParam = DBNull.Value;
object reservationDateParam = DBNull.Value;
object isEmailsendParam = DBNull.Value;
object mobilNrParam = DBNull.Value;
object ownerIdParam = DBNull.Value;
object resIdParam = DBNull.Value;
loan.Component = c.ComponentID;
using (SqlCommand cmd = new SqlCommand(insertStringParam, _conn))
{
// Get your parameters ready
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data1";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data2";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data3";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data4";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data5";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data6";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data7";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data8";
cmd.Parameters["@Data1"].Value = loan.Component;
cmd.Parameters["@Data2"].Value = loan.LoanDate ?? loanDateParam;
cmd.Parameters["@Data3"].Value = loan.ReturnDate ?? returnDateParam;
cmd.Parameters["@Data4"].Value = loan.IsEmailSend ?? isEmailsendParam;
cmd.Parameters["@Data5"].Value = loan.ReservationDate ?? reservationDateParam;
cmd.Parameters["@Data6"].Value = loan.MobilNr ?? mobilNrParam;
cmd.Parameters["@Data7"].Value = loan.OwnerID ?? ownerIdParam;
cmd.Parameters["@Data8"].Value = loan.ReservationID ?? resIdParam;
//var id
loan.LoanID = (int)cmd.ExecuteScalar(); //Returns the identity of the new tuple/record
//hv.HID = (int)cmd.ExecuteNonQuery(); //Does not workReturns row affected and not the identity of the new tuple/record
CurrentLoanInformation = loan;
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return false;
}
finally
{
// Close the connection
if (_conn != null)
{
_conn.Close();
}
}
return true;
}
public LoanInformation GetLoanInformationDetails(Hashtable listingCriteria)
{
var _db = new DBUtility();
var temp = new LoanInformation();
var _dt = new DataTable();
var _ds = new DataSet();
_cmd = new SqlCommand();
_cmd.CommandType = CommandType.StoredProcedure;
_cmd.CommandText = "GP_SP_GetLoanInformationDetails";
_cmd.Parameters.AddWithValue("@UserId", Convert.ToString(listingCriteria["UserID"]));
_cmd.Parameters.AddWithValue("@ListingDetailsID", Convert.ToInt64(listingCriteria["ID"]));
_ds = _db.FillDataSet(_cmd, _ds);
if (_ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in _ds.Tables[0].Rows)
{
temp.ID = Convert.ToInt64(dr["ID"]);
temp.Name = Convert.ToString(dr["Name"]);
temp.ListingTypeID = Convert.ToInt32(dr["ListingTypeID"]);
temp.RateTypeID = Convert.ToInt32(dr["RateTypeID"]);
temp.AmortizationTypeID = Convert.ToInt32(dr["AmortizationTypeID"]);
temp.PrePayPenalty = Convert.ToBoolean(dr["PrePayPenalty"]);
temp.InDefault = Convert.ToBoolean(dr["InDefault"]);
temp.BalloonPayment = Convert.ToBoolean(dr["BalloonPayment"]);
temp.LoanTermsModified = Convert.ToBoolean(dr["LoanTermsModified"]);
temp.OnForbearance = Convert.ToBoolean(dr["OnForbearance"]);
temp.Registered_wMERS = Convert.ToBoolean(dr["Registered_wMERS"]);
temp.InForeclosureID = Convert.ToString(dr["InForeclosure"]);
temp.InBankruptcyID = Convert.ToString(dr["InBankruptcy"]);
temp.OriginationDate = Convert.ToString(dr["OriginationDate"]);
temp.PaidToDate = Convert.ToString(dr["PaidToDate"]);
temp.NextPaymentDate = Convert.ToString(dr["NextPaymentDate"]);
temp.PayersLastPaymentMadeDate = Convert.ToString(dr["PayersLastPaymentMadeDate"]);
temp.OriginalLoanAmount = Convert.ToDouble(dr["OriginalLoanAmount"]);
temp.UnpaidPrincipalBalance = Convert.ToDouble(dr["UnpaidPrincipalBalance"]);
temp.NoteMaturityDate = Convert.ToString(dr["NoteMaturityDate"]);
temp.AccruedLateCharges = Convert.ToDouble(dr["AccruedLateCharges"]);
temp.LoanCharges = Convert.ToDouble(dr["LoanCharges"]);
temp.NoOfPaymentsInLast12 = Convert.ToInt32(dr["NoOfPaymentsInLast12"]);
temp.FirstPaymentDate = Convert.ToString(dr["FirstPaymentDate"]);
temp.EscrowImpounds = Convert.ToString(dr["EscrowImpounds"]);
temp.NoteInterestRate = Convert.ToDouble(dr["NoteInterestRate"]);
temp.SoldInterestRate = Convert.ToDouble(dr["SoldInterestRate"]);
temp.PaymentsFrequencyID = Convert.ToInt32(dr["PaymentsFrequency"]);
temp.LateCharges = Convert.ToDouble(dr["LateCharges"]);
temp.LateChargesType = Convert.ToInt32(dr["LateChargesType"]);
temp.LateChargesAfterXDays = Convert.ToInt32(dr["LateChargesAfterXDays"]);
temp.UnpaidInterest = Convert.ToDouble(dr["UnpaidInterest"]);
temp.PropertyTaxesDue = Convert.ToDouble(dr["PropertyTaxesDue"]);
temp.PaymentTrust = Convert.ToDouble(dr["PaymentTrust"]);
temp.pAndL = Convert.ToDouble(dr["pAndL"]);
temp.TotalMonthlyLoanPayment = Convert.ToInt32(dr["TotalMonthlyLoanPayment"]);
temp.CheckAllThatApplyID = Convert.ToInt32(dr["CheckAllThatApply"]);
temp.JuniorTrustDeedORMortgageID = Convert.ToInt32(dr["JuniorTrustDeedORMortgage"]);
temp.LienPositionID = Convert.ToInt32(dr["LienPosition"]);
temp.PrincipalBalance = Convert.ToDouble(dr["PrincipalBalance"]);
temp.MonthlyPayment = Convert.ToDouble(dr["MonthlyPayment"]);
temp.InformationAsOf = Convert.ToString(dr["InformationAsOf"]);
temp.Modified = Convert.ToString(dr["Modified"]);
temp.Current = Convert.ToString(dr["CurrentStatus"]);
temp.DrawPeriodStartDate = Convert.ToString(dr["DrawPeriodStartDate"]);
temp.RepaymentPeriodStartDate = Convert.ToString(dr["RepaymentPeriodStartDate"]);
temp.ArmInfo_NextAdjustment = Convert.ToString(dr["ArmInfo_NextAdjustment"]);
temp.ArmInfo_AdjPaymentChangeDate = Convert.ToString(dr["ArmInfo_AdjPaymentChangeDate"]);
temp.ArmInfo_AdjustmentFrequency = Convert.ToDouble(dr["ArmInfo_AdjustmentFrequency"]);
temp.ArmInfo_Floor = Convert.ToDouble(dr["ArmInfo_Floor"]);
temp.ArmInfo_IndexName = Convert.ToString(dr["ArmInfo_IndexName"]);
temp.ArmInfo_Margin = Convert.ToDouble(dr["ArmInfo_Margin"]);
temp.ArmInfo_Ceiling = Convert.ToDouble(dr["ArmInfo_Ceiling"]);
temp.IsCompany = Convert.ToBoolean(dr["IsCompany"]);
temp.PrimaryBorrowerFirstName = dr["PrimaryBorrowerFirstName"].ToString();
temp.LastName = dr["LastName"].ToString();
temp.MailingAddress = dr["MailingAddress"].ToString();
temp.BorrowerCity = dr["BorrowerCity"].ToString();
temp.BorrowerState = dr["BorrowerState"].ToString();
temp.BorrowerZip = Convert.ToInt32(dr["BorrowerZip"]);
temp.BorrowerHomePh = Convert.ToInt64(dr["BorrowerHomePh"]);
temp.BorrowerWorkPh = Convert.ToInt64(dr["BorrowerWorkPh"]);
temp.BorrowerMobilePh = Convert.ToInt64(dr["BorrowerMobilePh"]);
temp.BorrowerEmail = dr["BorrowerEmail"].ToString();
temp.BorrowerFax = Convert.ToInt64(dr["BorrowerFax"]);
temp.BorrowerSSTaxIDNoPrimary = Convert.ToInt64(dr["BorrowerSSTaxIDNoPrimary"]);
temp.CompanyName = dr["PrimaryBorrowerFirstName"].ToString();
temp.ListingStatusID = Convert.ToInt32(dr["ListingStatusID"]);
}
}
if (_ds.Tables.Count > 1)
{
temp.GuarantorInformation = (from DataRow dr in _ds.Tables[1].AsEnumerable()
select new GuarantorInformation()
{
Name = dr["Name"].ToString(),
Address = dr["Address"].ToString(),
Phone = dr["Phone"].ToString()
}).ToList();
temp.IsGuaranteed = temp.GuarantorInformation.Count > 0 ? "Yes" : "No";
//(List<GuarantorInformation>)_ds.Tables[1].AsEnumerable().ToList();
}
return(temp);
}
private void CurrentLoanInformationHelper(int compId)
{
SqlDataReader rdr = null;
try
{
// Open the connection
_conn.Open();
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("SELECT * FROM LoanInformation WHERE (Component ='" + compId + "')", _conn);
// 2. Call Execute reader to get query results
rdr = cmd.ExecuteReader();
// transfer data from result set to local model
while (rdr.Read())
{
//Console.WriteLine(rdr[0]);
CurrentLoanInformation = new LoanInformation();
CurrentLoanInformation.LoanID = (int)rdr["LoanID"];
CurrentLoanInformation.Component = (int)rdr["Component"];
if (rdr["LoanDate"] != DBNull.Value)
CurrentLoanInformation.LoanDate = (DateTime)rdr["LoanDate"];
else
{
CurrentLoanInformation.LoanDate = SqlDateTime.Null;
}
if (rdr["ReturnDate"] != DBNull.Value)
CurrentLoanInformation.ReturnDate = (DateTime)rdr["ReturnDate"];
else
{
CurrentLoanInformation.ReturnDate = SqlDateTime.Null;
}
if (rdr["IsEmailSend"] != DBNull.Value)
CurrentLoanInformation.IsEmailSend = (string)rdr["IsEmailSend"];
else
{
CurrentLoanInformation.IsEmailSend = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
if (rdr["ReservationDate"] != DBNull.Value)
CurrentLoanInformation.ReservationDate = (DateTime)rdr["ReservationDate"];
else
{
CurrentLoanInformation.ReservationDate = SqlDateTime.Null;
}
if (rdr["MobilNr"] != DBNull.Value)
CurrentLoanInformation.MobilNr = (string)rdr["MobilNr"];
else
{
CurrentLoanInformation.MobilNr = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
if (rdr["OwnerID"] != DBNull.Value)
CurrentLoanInformation.OwnerID = (string)rdr["OwnerID"];
else
{
CurrentLoanInformation.OwnerID = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
if (rdr["ReservationID"] != DBNull.Value)
CurrentLoanInformation.ReservationID = (string)rdr["ReservationID"];
else
{
CurrentLoanInformation.ReservationID = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
// close the reader
if (rdr != null)
{
rdr.Close();
}
// Close the connection
if (_conn != null)
{
_conn.Close();
}
}
}
private Hashtable SetHashCriteriaForLoanInformation(LoanInformation loanInformation)
{
var HashCriteria = new Hashtable();
HashCriteria.Add("ListingID", loanInformation.ID);
HashCriteria.Add("Name", loanInformation.Name);
HashCriteria.Add("ListingTypeID", loanInformation.ListingTypeID);
HashCriteria.Add("RateTypeID", loanInformation.RateTypeID);
HashCriteria.Add("AmortizationTypeID", loanInformation.AmortizationTypeID);
HashCriteria.Add("PrePayPenalty", loanInformation.PrePayPenalty);
HashCriteria.Add("InDefault", loanInformation.InDefault);
HashCriteria.Add("BalloonPayment", loanInformation.BalloonPayment);
HashCriteria.Add("LoanTermsModified", loanInformation.LoanTermsModified);
HashCriteria.Add("OnForbearance", loanInformation.OnForbearance);
HashCriteria.Add("Registered_wMERS", loanInformation.Registered_wMERS);
HashCriteria.Add("InForeclosure", loanInformation.InForeclosureID);
HashCriteria.Add("InBankruptcy", loanInformation.InBankruptcyID);
HashCriteria.Add("OriginationDate", loanInformation.OriginationDate);
HashCriteria.Add("PaidToDate", loanInformation.PaidToDate);
HashCriteria.Add("NextPaymentDate", loanInformation.NextPaymentDate);
HashCriteria.Add("PayersLastPaymentMadeDate", loanInformation.PayersLastPaymentMadeDate);
HashCriteria.Add("OriginalLoanAmount", loanInformation.OriginalLoanAmount);
HashCriteria.Add("UnpaidPrincipalBalance", loanInformation.UnpaidPrincipalBalance);
HashCriteria.Add("NoteMaturityDate", loanInformation.NoteMaturityDate);
HashCriteria.Add("AccruedLateCharges", loanInformation.AccruedLateCharges);
HashCriteria.Add("LoanCharges", loanInformation.LoanCharges);
HashCriteria.Add("NoOfPaymentsInLast12", loanInformation.NoOfPaymentsInLast12);
HashCriteria.Add("FirstPaymentDate", loanInformation.FirstPaymentDate);
HashCriteria.Add("EscrowImpounds", loanInformation.EscrowImpounds);
HashCriteria.Add("NoteInterestRate", loanInformation.NoteInterestRate);
HashCriteria.Add("SoldInterestRate", loanInformation.SoldInterestRate);
HashCriteria.Add("PaymentsFrequency", loanInformation.PaymentsFrequencyID);
HashCriteria.Add("LateCharges", loanInformation.LateCharges);
HashCriteria.Add("LateChargesType", loanInformation.LateChargesType);
HashCriteria.Add("LateChargesAfterXDays", loanInformation.LateChargesAfterXDays);
HashCriteria.Add("UnpaidInterest", loanInformation.UnpaidInterest);
HashCriteria.Add("PropertyTaxesDue", loanInformation.PropertyTaxesDue);
HashCriteria.Add("PaymentTrust", loanInformation.PaymentTrust);
HashCriteria.Add("pAndL", loanInformation.pAndL);
HashCriteria.Add("TotalMonthlyLoanPayment", loanInformation.TotalMonthlyLoanPayment);
HashCriteria.Add("CheckAllThatApply", loanInformation.CheckAllThatApplyID);
HashCriteria.Add("JuniorTrustDeedORMortgage", loanInformation.JuniorTrustDeedORMortgageID);
HashCriteria.Add("LienPosition", loanInformation.LienPositionID);
HashCriteria.Add("PrincipalBalance", loanInformation.PrincipalBalance);
HashCriteria.Add("MonthlyPayment", loanInformation.MonthlyPayment);
HashCriteria.Add("Modified", loanInformation.Modified);
HashCriteria.Add("Current", loanInformation.Current);
HashCriteria.Add("DrawPeriodStartDate", loanInformation.DrawPeriodStartDate);
HashCriteria.Add("RepaymentPeriodStartDate", loanInformation.RepaymentPeriodStartDate);
HashCriteria.Add("InformationAsOf", loanInformation.InformationAsOf);
HashCriteria.Add("ArmInfo_NextAdjustment", loanInformation.ArmInfo_NextAdjustment);
HashCriteria.Add("ArmInfo_AdjPaymentChangeDate", loanInformation.ArmInfo_AdjPaymentChangeDate);
HashCriteria.Add("ArmInfo_AdjustmentFrequency", loanInformation.ArmInfo_AdjustmentFrequency);
HashCriteria.Add("ArmInfo_Floor", loanInformation.ArmInfo_Floor);
HashCriteria.Add("ArmInfo_IndexName", loanInformation.ArmInfo_IndexName);
HashCriteria.Add("ArmInfo_Margin", loanInformation.ArmInfo_Margin);
HashCriteria.Add("ArmInfo_Ceiling", loanInformation.ArmInfo_Ceiling);
HashCriteria.Add("ListingStatusID", loanInformation.ListingStatusID);
HashCriteria.Add("UserID", userID);
HashCriteria.Add("GuarantorInformation", loanInformation.GuarantorInformation);
//Brower information
HashCriteria.Add("IsCompany", loanInformation.IsCompany);
HashCriteria.Add("PrimaryBorrowerFirstName", loanInformation.PrimaryBorrowerFirstName);
HashCriteria.Add("LastName", loanInformation.LastName);
HashCriteria.Add("MailingAddress", loanInformation.MailingAddress);
HashCriteria.Add("BorrowerCity", loanInformation.BorrowerCity);
HashCriteria.Add("BorrowerState", loanInformation.BorrowerState);
HashCriteria.Add("BorrowerZip", loanInformation.BorrowerZip);
HashCriteria.Add("BorrowerHomePh", loanInformation.BorrowerHomePh);
HashCriteria.Add("BorrowerWorkPh", loanInformation.BorrowerWorkPh);
HashCriteria.Add("BorrowerMobilePh", loanInformation.BorrowerMobilePh);
HashCriteria.Add("BorrowerEmail", loanInformation.BorrowerEmail);
HashCriteria.Add("BorrowerFax", loanInformation.BorrowerFax);
HashCriteria.Add("BorrowerSSTaxIDNoPrimary", loanInformation.BorrowerSSTaxIDNoPrimary);
HashCriteria.Add("CompanyName", loanInformation.CompanyName);
return(HashCriteria);
}
public void RemoveReservation(int compId)
{
try
{
var loan = new LoanInformation { ReservationID = "", ReservationDate = new SqlDateTime() };
UpdateLoanInformation(compId, loan);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
public List<LoanInformation> GetAllLoanInformation()
{
var loanList = new List<LoanInformation>();
SqlDataReader rdr = null;
try
{
// Open the connection
_conn.Open();
string insertStringParam = @"SELECT * FROM LoanInformation";
SqlCommand cmd = new SqlCommand(insertStringParam, _conn);
cmd.ExecuteNonQuery();
using (rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var loanInfo = new LoanInformation();
loanInfo.LoanID = (int) rdr["LoanID"];
loanInfo.Component = (int) rdr["Component"];
if (rdr["LoanDate"] != DBNull.Value)
loanInfo.LoanDate = (DateTime) rdr["LoanDate"];
else
{
loanInfo.LoanDate = null;
}
if (rdr["ReturnDate"] != DBNull.Value)
loanInfo.ReturnDate = (DateTime) rdr["ReturnDate"];
else
{
loanInfo.ReturnDate = null;
}
if (rdr["IsEmailSend"] != DBNull.Value)
loanInfo.IsEmailSend = (string) rdr["IsEmailSend"];
else
{
loanInfo.IsEmailSend = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
if (rdr["ReservationDate"] != DBNull.Value)
loanInfo.ReservationDate = (DateTime) rdr["ReservationDate"];
else
{
loanInfo.ReservationDate = null;
}
if (rdr["MobilNr"] != DBNull.Value)
loanInfo.MobilNr = (string) rdr["MobilNr"];
else
{
loanInfo.MobilNr = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
if (rdr["OwnerID"] != DBNull.Value)
loanInfo.OwnerID = (string) rdr["OwnerID"];
else
{
loanInfo.OwnerID = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
if (rdr["ReservationID"] != DBNull.Value)
loanInfo.ReservationID = (string) rdr["ReservationID"];
else
{
loanInfo.ReservationID = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
loanList.Add(loanInfo);
CurrentLoanInformation = loanInfo;
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
// Close the connection
if (_conn != null)
{
_conn.Close();
}
}
return loanList;
}
public bool UpdateLoanInformation(int compId, LoanInformation loan)
{
try
{
CurrentLoanInformationHelper(compId);
// Open the connection
_conn.Open();
// prepare command string
string updateString =
@"UPDATE LoanInformation
SET Component= @Data2, LoanDate = @Data3, ReturnDate = @Data4, IsEmailSend = @Data5, ReservationDate = @Data6, MobilNr = @Data7, OwnerID = @Data8, ReservationID = @Data9
WHERE LoanID = @Data1";
using (SqlCommand cmd = new SqlCommand(updateString, _conn))
{
// Get your parameters ready
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data1";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data2";
cmd.Parameters.Add("@Data3", SqlDbType.DateTime);
cmd.Parameters.Add("@Data4", SqlDbType.DateTime);
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data5";
cmd.Parameters.Add("@Data6", SqlDbType.DateTime);
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data7";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data8";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data9";
// Hvis man ikke ?dre p?en attribut, s?s?tes den lig med CurrentInformation attribut.
cmd.Parameters["@Data1"].Value = loan.LoanID != 0 ? loan.LoanID : CurrentLoanInformation.LoanID;
cmd.Parameters["@Data2"].Value = loan.Component != 0 ? loan.Component : CurrentLoanInformation.Component;
cmd.Parameters["@Data3"].Value = loan.LoanDate ?? CurrentLoanInformation.LoanDate;
cmd.Parameters["@Data4"].Value = loan.ReturnDate ?? CurrentLoanInformation.ReturnDate;
cmd.Parameters["@Data5"].Value = loan.IsEmailSend ?? CurrentLoanInformation.IsEmailSend;
cmd.Parameters["@Data6"].Value = loan.ReservationDate ?? CurrentLoanInformation.ReservationDate;
cmd.Parameters["@Data7"].Value = loan.MobilNr ?? CurrentLoanInformation.MobilNr;
cmd.Parameters["@Data8"].Value = loan.OwnerID ?? CurrentLoanInformation.OwnerID;
cmd.Parameters["@Data9"].Value = loan.ReservationID ?? CurrentLoanInformation.ReservationID;
cmd.ExecuteNonQuery();
}
//// 1. Instantiate a new command with command text only
//SqlCommand cmd = new SqlCommand(updateString);
//// 2. Set the Connection property
//cmd.Connection = conn;
//// 3. Call ExecuteNonQuery to send command
//cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return false;
}
finally
{
// Close the connection
if (_conn != null)
{
_conn.Close();
}
}
return true;
}
public LoanInformation GetLoanInformation(Component cp)
{
try
{
// Open the connection
_conn.Open();
// prepare command string using paramters in string and returning the given identity
string insertStringParam =
@"SELECT * FROM LoanInformation WHERE (Component = @Data1)";
SqlCommand cmd = new SqlCommand(insertStringParam, _conn);
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data1";
cmd.Parameters["@Data1"].Value = cp.ComponentID;
cmd.ExecuteNonQuery();
SqlDataReader rdr;
using (rdr = cmd.ExecuteReader())
{
var loanInfo = new LoanInformation();
while (rdr.Read())
{
loanInfo.LoanID = (int)rdr["LoanID"];
loanInfo.Component = (int)rdr["Component"];
if (rdr["LoanDate"] != DBNull.Value)
loanInfo.LoanDate = (DateTime)rdr["LoanDate"];
else
{
loanInfo.LoanDate = null;
}
if (rdr["ReturnDate"] != DBNull.Value)
loanInfo.ReturnDate = (DateTime)rdr["ReturnDate"];
else
{
loanInfo.ReturnDate = null;
}
if (rdr["IsEmailSend"] != DBNull.Value)
loanInfo.IsEmailSend = (string)rdr["IsEmailSend"];
else
{
loanInfo.IsEmailSend = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
if (rdr["ReservationDate"] != DBNull.Value)
loanInfo.ReservationDate = (DateTime)rdr["ReservationDate"];
else
{
loanInfo.ReservationDate = null;
}
if (rdr["MobilNr"] != DBNull.Value)
loanInfo.MobilNr = (string)rdr["MobilNr"];
else
{
loanInfo.MobilNr = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
if (rdr["OwnerID"] != DBNull.Value)
loanInfo.OwnerID = (string)rdr["OwnerID"];
else
{
loanInfo.OwnerID = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
if (rdr["ReservationID"] != DBNull.Value)
loanInfo.ReservationID = (string)rdr["ReservationID"];
else
{
loanInfo.ReservationID = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
}
CurrentLoanInformation = loanInfo;
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
// Close the connection
if (_conn != null)
{
_conn.Close();
}
}
return CurrentLoanInformation;
}
public OutLoanInformation GetLoanInformationByCustomer(double customerID)
{
string connectionString = DataBaseHelper.GetConnectionString("DLG");
OutLoanInformation response = new OutLoanInformation();
LoanInformation loan;
List <LoanInformation> lst = new List <LoanInformation>();
var ora = new OracleServer(connectionString);
//LoanDetail detail;
string command = string.Empty;
try
{
command = "SELECT NUMERO_CARPETA, NUMERO_IDENTIFICACION, NOMBRE_PERSONA, CODIGO_ESTADO_CARPETA, NOMBRE_ESTADO_CARPETA, ";
command = command + "LINEA_CREDITO, MONTO_SOLICITUD, MONTO_APROBADO, PLAZO_APROBADO, TASA_APROBADA, CODIGO_ASESOR, ";
command = command + "FECHA_CREACION, CODIGO_ZONA, CODIGO_SUCURSAL, NOMBRE_SUCURSAL, CODIGO_CONVENIO, NOMBRE_CONVENIO, ";
command = command + "CODIGO_PAGADURIA, NOMRE_CONVENIO, ANALISTA_APROBACION, CODIGO_NOVEDAD, NOMBRE_NOVEDAD, CODIGO_JEFE_CCIAL, ";
command = command + "NOMBRE_JEFE_CCIAL, CEDULA_EJECUTIVO, NOMBRE_EJECUTIVO ";
command = command + string.Format("FROM BBS_LIQCOM_V_CARPETAS WHERE NUMERO_IDENTIFICACION = '{0}'", customerID);
var rdr = ora.ExecuteCommand(command);
while (rdr.Read())
{
loan = new LoanInformation();
loan.folderNumber = DBNull.Value.Equals(rdr["NUMERO_CARPETA"]) ? 0 : double.Parse(rdr["NUMERO_CARPETA"].ToString());
loan.numberID = DBNull.Value.Equals(rdr["NUMERO_IDENTIFICACION"]) ? string.Empty : rdr["NUMERO_IDENTIFICACION"].ToString();
loan.personName = DBNull.Value.Equals(rdr["NOMBRE_PERSONA"]) ? string.Empty : rdr["NOMBRE_PERSONA"].ToString();
loan.folderStateCode = DBNull.Value.Equals(rdr["CODIGO_ESTADO_CARPETA"]) ? 0 : double.Parse(rdr["CODIGO_ESTADO_CARPETA"].ToString());
loan.folderStateName = DBNull.Value.Equals(rdr["NOMBRE_ESTADO_CARPETA"]) ? string.Empty : rdr["NOMBRE_ESTADO_CARPETA"].ToString();
loan.loanLine = DBNull.Value.Equals(rdr["LINEA_CREDITO"]) ? 0 : double.Parse(rdr["LINEA_CREDITO"].ToString());
loan.requisitionAmount = DBNull.Value.Equals(rdr["MONTO_SOLICITUD"]) ? 0 : double.Parse(rdr["MONTO_SOLICITUD"].ToString());
loan.amountApproved = DBNull.Value.Equals(rdr["MONTO_APROBADO"]) ? 0 : double.Parse(rdr["MONTO_APROBADO"].ToString());
loan.termApproved = DBNull.Value.Equals(rdr["PLAZO_APROBADO"]) ? 0 : int.Parse(rdr["PLAZO_APROBADO"].ToString());
loan.rateApproved = DBNull.Value.Equals(rdr["TASA_APROBADA"]) ? 0 : decimal.Parse(rdr["TASA_APROBADA"].ToString());
loan.loanOfficerCode = DBNull.Value.Equals(rdr["CODIGO_ASESOR"]) ? 0 : double.Parse(rdr["CODIGO_ASESOR"].ToString());
loan.creationDate = DBNull.Value.Equals(rdr["FECHA_CREACION"]) ? DateTime.Today.ToString() : rdr["FECHA_CREACION"].ToString();
loan.zoneCode = DBNull.Value.Equals(rdr["CODIGO_ZONA"]) ? 0 : double.Parse(rdr["CODIGO_ZONA"].ToString());
loan.branchCode = DBNull.Value.Equals(rdr["CODIGO_SUCURSAL"]) ? 0 : double.Parse(rdr["CODIGO_SUCURSAL"].ToString());
loan.branchName = DBNull.Value.Equals(rdr["NOMBRE_SUCURSAL"]) ? string.Empty : rdr["NOMBRE_SUCURSAL"].ToString();
loan.agreementCode = DBNull.Value.Equals(rdr["CODIGO_CONVENIO"]) ? 0 : double.Parse(rdr["CODIGO_CONVENIO"].ToString());
loan.agreementName = DBNull.Value.Equals(rdr["NOMBRE_CONVENIO"]) ? string.Empty : rdr["NOMBRE_CONVENIO"].ToString();
loan.payamentCode = DBNull.Value.Equals(rdr["CODIGO_PAGADURIA"]) ? 0 : double.Parse(rdr["CODIGO_PAGADURIA"].ToString());
loan.payamentName = DBNull.Value.Equals(rdr["NOMRE_CONVENIO"]) ? string.Empty : rdr["NOMRE_CONVENIO"].ToString();
loan.analystsApproved = DBNull.Value.Equals(rdr["ANALISTA_APROBACION"]) ? "-" : rdr["ANALISTA_APROBACION"].ToString();
loan.noveltyCode = DBNull.Value.Equals(rdr["CODIGO_NOVEDAD"]) ? 0 : double.Parse(rdr["CODIGO_NOVEDAD"].ToString());
loan.noveltyName = DBNull.Value.Equals(rdr["NOMBRE_NOVEDAD"]) ? string.Empty : rdr["NOMBRE_NOVEDAD"].ToString();
loan.commercialBossCode = DBNull.Value.Equals(rdr["CODIGO_JEFE_CCIAL"]) ? 0 : double.Parse(rdr["CODIGO_JEFE_CCIAL"].ToString());
loan.commercialBossName = DBNull.Value.Equals(rdr["NOMBRE_JEFE_CCIAL"]) ? string.Empty : rdr["NOMBRE_JEFE_CCIAL"].ToString();
loan.executiveID = DBNull.Value.Equals(rdr["CEDULA_EJECUTIVO"]) ? string.Empty : rdr["CEDULA_EJECUTIVO"].ToString();
loan.executiveName = DBNull.Value.Equals(rdr["NOMBRE_EJECUTIVO"]) ? string.Empty : rdr["NOMBRE_EJECUTIVO"].ToString();
lst.Add(loan);
}
rdr.Close();
response.loanInformationList = lst;
response.msg = new Response();
response.msg.errorCode = "200";
response.msg.errorMessage = "OK";
}
catch (Exception ex)
{
throw new Exception("RequisitionDAO.GetLoanInformationByCustomer", ex);
}
finally
{
ora.Dispose();
}
return(response);
}
public IEnumerable <LoanInformation> Read(string file)
{
using (var doc = SpreadsheetDocument.Open(file, false))
{
var workbookPart = doc.WorkbookPart;
var worksheetPart = workbookPart.WorksheetParts.First();
var reader = OpenXmlReader.Create(worksheetPart);
bool isFirst = true;
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
if (reader.IsEndElement)
{
continue;
}
if (isFirst)
{
isFirst = false;
continue;
}
var children = reader.LoadCurrentElement().ChildElements;
var loan = new LoanInformation();
foreach (Cell child in children.Where(x => x is Cell))
{
var columnReference = Regex.Replace(child.CellReference.ToString(), @"[\d]", string.Empty);
var cellContent = child.InnerText;
switch (columnReference)
{
case "A":
loan.Id = cellContent;
continue;
case "B":
loan.IssueDate = StringToDateTimeConverter.Convert(cellContent);
continue;
case "C":
loan.ClosingDate = StringToDateTimeConverter.Convert(cellContent);
continue;
case "D":
loan.ListingDate = StringToDateTimeConverter.Convert(cellContent);
continue;
case "E":
loan.Country = cellContent;
continue;
case "F":
loan.LoanOriginator = cellContent;
continue;
case "G":
loan.MintosRating = cellContent;
continue;
case "H":
loan.LoanType = cellContent;
continue;
case "I":
loan.LoanRate = Convert.ToDecimal(cellContent);
continue;
case "J":
loan.Term = Convert.ToUInt16(cellContent);
continue;
case "K":
loan.Collateral = YesNoToBooleanConverter.Convert(cellContent);
continue;
case "L":
loan.InitialLtv = Convert.ToDecimal(cellContent);
continue;
case "M":
loan.Ltv = Convert.ToDecimal(cellContent);
continue;
case "N":
loan.LoanStatus = cellContent;
continue;
case "O":
loan.BuybackReason = cellContent;
continue;
case "P":
loan.InitialLoanAmount = Convert.ToDecimal(cellContent);
continue;
case "Q":
loan.RemainingLoanAmount = Convert.ToDecimal(cellContent);
continue;
case "R":
loan.Currency = cellContent;
continue;
case "S":
loan.Buyback = YesNoToBooleanConverter.Convert(cellContent);
continue;
default:
throw new ArgumentOutOfRangeException(nameof(columnReference));
}
}
yield return(loan);
}
}
}
}
public IActionResult RepaymentSchedule([FromQuery] LoanInformation loanInformation)
{
var result = _loanService.GetRepaymentSchedule(loanInformation);
return(new JsonResult(result));
}
