//public bool IsValidName(string name)
//{
// using (LoanPriceEntities context = new LoanPriceEntities())
// {
// int count = context.CreditRatings.Where(s => s. == name).Count();
// if (count == 0)
// return true;
// else
// return false;
// }
//}
public string SaveAgency(CreditAgency agency)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
if (agency.ID <= 0)
{
//if (context.CreditRatings.Where(s => s.CreditAgency == rating.CreditAgency).Count() == 0)
//{
context.AddToCreditAgencies(agency);
context.SaveChanges();
return "Credit Agency is added successfully";
//}
//else
// return "Entry of the same Currency is already exists.";
}
else
{
//if (context.Currencies.Where(s => s.Currancy == currency.Currancy && s.ID != currency.ID).Count() == 0)
//{
context.CreditAgencies.Attach(agency);
context.ObjectStateManager.ChangeObjectState(agency, System.Data.EntityState.Modified);
context.SaveChanges();
return "Credit Agency is Updated successfully";
//}
//else
// return "Entry of the same Currency is already exists.";
}
}
}
/// <summary>
/// Authenticate the user and log the failed attempts and locked the account if failed attemtps reach to 3
/// </summary>
/// <param name="name"></param>
/// <param name="password"></param>
/// <returns></returns>
public Login Authenticate(string name, string password)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
Login login = context.Logins.FirstOrDefault(c => c.Name == name && c.Password == password);
if (login == null)
{
// Log failed attempt
Login log = context.Logins.FirstOrDefault(c => c.Name == name);
if (log != null)
{
log.FailedAttempts = log.FailedAttempts + 1;
// if there are 3 failed attempts then Lock the account
if (log.FailedAttempts == 3)
{
log.IsLocked = true;
}
}
}
else
{
// reset failed attempt if login successfully
login.FailedAttempts = 0;
}
context.SaveChanges();
return login;
}
}
public void InsertUpdateSettings(Setting data)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
Setting setting = context.Settings.SingleOrDefault(s => s.Name == data.Name);
if (setting != null)
{
setting.Value = data.Value;
context.SaveChanges();
}
else
{
context.Settings.AddObject(data);
context.SaveChanges();
}
}
}
public tblActivityLog AddActivityLog(tblActivityLog model)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
context.tblActivityLogs.AddObject(model);
context.SaveChanges();
return model;
}
}
public string SaveHistory(LoanHistory history)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
context.AddToLoanHistories(history);
context.SaveChanges();
return "History is added successfully";
}
}
public void Delete(int id)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
tblCountry group = context.tblCountry.FirstOrDefault(c => c.ID == id);
if (group != null)
{
context.tblCountry.DeleteObject(group);
context.SaveChanges();
}
}
}
public void AddEmailQue(tblEmailQue model)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
tblEmailQue que = context.tblEmailQues.FirstOrDefault(c => EntityFunctions.TruncateTime(c.SendTime) == EntityFunctions.TruncateTime(model.SendTime));
if (que == null)
{
context.tblEmailQues.AddObject(model);
context.SaveChanges();
}
}
}
public void Delete(int id)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
CreditAgency group = context.CreditAgencies.FirstOrDefault(c => c.ID == id);
if (group != null)
{
context.CreditAgencies.DeleteObject(group);
context.SaveChanges();
}
}
}
public void SaveLoanSchedule(LoanSchedule model)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
if (model.ID > 0)
{
context.AddObject("LoanSchedule", model);
}
else
{
context.AddToLoanSchedules(model);
}
context.SaveChanges();
}
}
public void SetEmailReceiver(int id)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
// uncheck all
List<Group> groups = context.Groups.ToList();
for (int i = 0; i < groups.Count; i++)
{
groups[i].IsEmailReceiver = false;
}
groups.FirstOrDefault(c => c.ID == id).IsEmailReceiver = true;
context.SaveChanges();
}
}
public bool Delete(int id)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
EmailGroup group = context.EmailGroups.FirstOrDefault(c => c.ID == id);
if (group != null)
{
context.EmailGroups.DeleteObject(group);
context.SaveChanges();
return true;
}
else
{
return false;
}
}
}
public void ImportUSDCurves(List<USDCurve> lst)
{
try
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
foreach (var item in lst)
{
context.AddToUSDCurves(item);
}
context.SaveChanges();
}
}
catch (Exception ex)
{
throw ex;
}
}
public void AddImportedQuotesAndTrades(List<QuotesAndTrades> lst)
{
try
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
foreach (var item in lst)
{
context.AddToQuotesAndTrades(item);
}
context.SaveChanges();
}
}
catch (Exception ex)
{
throw ex;
}
}
public bool RemoveLast60DayLogs()
{
try
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
var oldRecords = context.tblActivityLogs.Where(c => c.ActivityDate > DateTime.Now.AddDays(-60));
foreach (var item in oldRecords)
{
context.tblActivityLogs.DeleteObject(item);
}
context.SaveChanges();
return true;
}
}
catch (Exception ex)
{
return false;
}
}
public Login ChangePassword(int id, string newPassword)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
Login model = context.Logins.FirstOrDefault(c => c.ID == id);
// Save password history
PasswordHistory history = new PasswordHistory();
history.ChangedDate = DateTime.Now;
history.ChangedPassword = newPassword;
history.Password = model.Password;
history.UserId = model.ID;
context.PasswordHistories.AddObject(history);
model.Password = newPassword;
context.SaveChanges();
return model;
}
}
public bool RemoveLoanSchedule(int loanID)
{
try
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
List<LoanSchedule> loans = context.LoanSchedules.Where(s => s.LoanID == loanID).ToList();
for (int i = 0; i < loans.Count; i++)
{
context.DeleteObject(loans[i]);
}
context.SaveChanges();
return true;
}
}
catch (Exception ex)
{
return false;
}
}
public void UpdateCurve()
{
try
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
var result = context.EURCurves.ToList();
if (result != null)
{
foreach (var item in result)
{
item.IsNew = false;
}
context.SaveChanges();
}
}
}
catch (Exception)
{
}
}
public string SaveCountry(tblCountry country)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
if (country.ID <= 0)
{
if (context.tblCountry.Where(s => s.Name == country.Name).Count() == 0)
{
context.AddTotblCountry(country);
context.SaveChanges();
return "Country is added successfully";
}
else
return "Entry of the same Name is already exists.";
}
else
{
context.tblCountry.Attach(country);
context.ObjectStateManager.ChangeObjectState(country, System.Data.EntityState.Modified);
context.SaveChanges();
return "Country is Updates successfully";
}
}
}
///
/// Insert Duplicate Records
///
public void SaveDuplicateLoan(DuplicateLoan model)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
if (model.ID > 0)
{
context.AddObject("DuplicateLoans", model);
}
else
{
context.AddToDuplicateLoans(model);
}
context.SaveChanges();
}
}
public bool RemoveLoan(int id)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
Loans model = context.Loans.SingleOrDefault(c => c.ID == id);
if (model != null)
{
// Delete the Password policy of this account
LoanScheduleBL loanScheduleBL = new LoanScheduleBL();
loanScheduleBL.RemoveLoanSchedule(id);
context.Loans.DeleteObject(model);
context.SaveChanges();
return true;
}
else
{
return false;
}
}
}
public bool RemoveAllLoans()
{
try
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
List<Loans> loans = context.Loans.ToList();
for (int i = 0; i < loans.Count; i++)
{
LoanScheduleBL loanScheduleBL = new LoanScheduleBL();
loanScheduleBL.RemoveLoanSchedule(loans[i].ID);
context.DeleteObject(loans[i]);
}
context.SaveChanges();
return true;
}
}
catch (Exception ex)
{
return false;
}
}
public void EditLoan(Loans loan, int loanID, int type)
{
try
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
Loans oldLoan = context.Loans.FirstOrDefault(c => c.ID == loanID);
oldLoan.CodeName = loan.CodeName;
oldLoan.Borrower = loan.Borrower;
oldLoan.Sector = loan.Sector;
oldLoan.Signing_Date = loan.Signing_Date;
oldLoan.Maturity_Date = loan.Maturity_Date;
oldLoan.FixedOrFloating = loan.FixedOrFloating;
oldLoan.Margin = loan.Margin;
oldLoan.Currency = loan.Currency;
oldLoan.PP = loan.PP;
oldLoan.CreditRating = loan.CreditRating;
oldLoan.Country = loan.Country;
oldLoan.CouponFrequency = loan.CouponFrequency;
oldLoan.FacilitySize = loan.FacilitySize;
oldLoan.Bilateral = loan.Bilateral;
oldLoan.Amortizing = loan.Amortizing;
oldLoan.AmortisationsStartPoint = loan.AmortisationsStartPoint;
oldLoan.CouponDate = loan.CouponDate;
oldLoan.Notional = loan.Notional;
oldLoan.NoOfAmortisationPoint = loan.NoOfAmortisationPoint;
oldLoan.CreditRatingFitch = loan.CreditRatingFitch;
oldLoan.CreditRatingING = loan.CreditRatingING;
oldLoan.CreditRatingModys = loan.CreditRatingModys;
oldLoan.CreditRatingSPs = loan.CreditRatingSPs;
oldLoan.StructureID = loan.StructureID;
oldLoan.LastEdited = loan.LastEdited;
oldLoan.CreatedBy = loan.CreatedBy;
oldLoan.SummitCreditEntity = loan.SummitCreditEntity;
oldLoan.Grid = loan.Grid;
oldLoan.Gurantor = loan.Gurantor;
context.SaveChanges();
//if type == 1 then do not add to DuplicateLoan
if (CheckForLoanCode(loan.CodeName, loanID) && type != 1)
{
DuplicateLoan duplicateLoan = new DuplicateLoan();
duplicateLoan.CodeName = loan.CodeName;
duplicateLoan.Borrower = loan.Borrower;
duplicateLoan.Sector = loan.Sector;
duplicateLoan.Signing_Date = loan.Signing_Date;
duplicateLoan.Maturity_Date = loan.Maturity_Date;
duplicateLoan.FixedOrFloating = loan.FixedOrFloating;
duplicateLoan.Margin = loan.Margin;
duplicateLoan.Currency = loan.Currency;
duplicateLoan.Country = loan.Country;
duplicateLoan.CouponFrequency = loan.CouponFrequency;
duplicateLoan.FacilitySize = loan.FacilitySize;
duplicateLoan.Bilateral = loan.Bilateral;
duplicateLoan.Amortizing = loan.Amortizing;
duplicateLoan.AmortisationsStartPoint = loan.AmortisationsStartPoint;
duplicateLoan.CouponDate = loan.CouponDate;
duplicateLoan.Notional = loan.Notional;
duplicateLoan.NoOfAmortisationPoint = loan.NoOfAmortisationPoint;
context.AddToDuplicateLoans(duplicateLoan);
context.SaveChanges();
}
}
}
catch (Exception)
{
}
}
public Login Unlock(int id)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
Login login = context.Logins.FirstOrDefault(c => c.ID == id);
if (login != null)
{
login.IsLocked = false;
login.FailedAttempts = 0;
}
context.SaveChanges();
return login;
}
}
public EmailGroup Save(EmailGroup model)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
if (model.ID <= 0)
{
context.EmailGroups.AddObject(model);
}
else
{
context.EmailGroups.Attach(model);
context.ObjectStateManager.ChangeObjectState(model, System.Data.EntityState.Modified);
}
context.SaveChanges();
return model;
}
}
public void UpdateSpread(int loanID, Decimal spread)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
List<LoanSchedule> scheduleList = context.LoanSchedules.Where(s => s.LoanID == loanID).ToList();
foreach (var item in scheduleList)
{
item.Spread = spread;
}
context.SaveChanges();
}
}
public void SetLogin(Login model, int type)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
Login login = context.Logins.Where(c => c.ID == model.ID).FirstOrDefault();
if (login != null)
{
switch (type)
{
case 1:
login.IsLogin = true;
context.SaveChanges();
break;
case 2:
login.IsLogin = false;
context.SaveChanges();
break;
default:
break;
}
}
}
}
public void EditSchedule(int loanID, DataTable dt)
{
try
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
List<LoanSchedule> oldScheduleList = context.LoanSchedules.Where(s => s.LoanID == loanID).ToList();
if (oldScheduleList.Count > 0)
{
LoanScheduleBL loanScheduleBL = new LoanScheduleBL();
loanScheduleBL.RemoveLoanSchedule(loanID);
for (int i = 0; i < dt.Rows.Count; i++)
{
// oldScheduleList[i].ID = Convert.ToInt16(dt.Rows[i][0]);
//if (i >= oldScheduleList.Count)
//{
LoanSchedule loanSchedule = new LoanSchedule();
loanSchedule.LoanID = loanID;
loanSchedule.StartDate = Convert.ToDateTime(dt.Rows[i][1].ToString());
loanSchedule.EndDate = Convert.ToDateTime(dt.Rows[i][2].ToString());
loanSchedule.CoupFrac = Convert.ToDecimal(Convert.ToDecimal(dt.Rows[i][3].ToString()).ToString("0.00"));
loanSchedule.Notation = Convert.ToDecimal(dt.Rows[i][4].ToString());
loanSchedule.Amortisation = Convert.ToDecimal(Convert.ToDecimal(dt.Rows[i][5].ToString()).ToString("0.00"));
loanSchedule.Factor = Convert.ToDecimal(Convert.ToDecimal(dt.Rows[i][6].ToString()).ToString("0.00000"));
if (dt.Rows[i][7] != null)
loanSchedule.Spread = Convert.ToDecimal(Convert.ToDecimal(dt.Rows[i][7].ToString()).ToString("0.00000"));
if (dt.Rows[i][8] != null)
loanSchedule.CouponPaymentDate = Convert.ToDateTime(dt.Rows[i][8].ToString());
if (dt.Rows[i][9] != null)
loanSchedule.RiskFreeDP1 = Convert.ToDecimal(Convert.ToDecimal(dt.Rows[i][9].ToString()).ToString("0.0000000"));
if (dt.Rows[i][10] != null)
loanSchedule.RiskFreeDP2 = Convert.ToDecimal(Convert.ToDecimal(dt.Rows[i][10].ToString()).ToString("0.0000000"));
if (dt.Rows[i][11] != null)
loanSchedule.FloatingRate = Convert.ToDecimal(Convert.ToDecimal(dt.Rows[i][11].ToString()).ToString("0.0000000"));
if (dt.Rows[i][12] != null)
loanSchedule.AllInRate = Convert.ToDecimal(Convert.ToDecimal(dt.Rows[i][12].ToString()).ToString("0.00000"));
if (dt.Rows[i][13] != null)
loanSchedule.Interest = Convert.ToDecimal(Convert.ToDecimal(dt.Rows[i][13].ToString()).ToString("0.00000"));
if (dt.Rows[i][14] != null)
loanSchedule.Days = Convert.ToInt16(dt.Rows[i][14].ToString());
if (dt.Rows[i][15] != null)
loanSchedule.AmortisationInt = Convert.ToDecimal(Convert.ToDecimal(dt.Rows[i][15].ToString()).ToString("0.0000000"));
SaveLoanSchedule(loanSchedule);
// }
//else
//{
// oldScheduleList[i].StartDate = Convert.ToDateTime(dt.Rows[i][1].ToString());
// oldScheduleList[i].EndDate = Convert.ToDateTime(dt.Rows[i][2].ToString());
// oldScheduleList[i].CoupFrac = Convert.ToDecimal(Convert.ToDecimal(dt.Rows[i][3].ToString()).ToString("0.00"));
// oldScheduleList[i].Notation = Convert.ToDecimal(dt.Rows[i][4].ToString());
// oldScheduleList[i].Amortisation = Convert.ToDecimal(Convert.ToDecimal(dt.Rows[i][5].ToString()).ToString("0.00"));
// oldScheduleList[i].Factor = Convert.ToDecimal(Convert.ToDecimal(dt.Rows[i][6].ToString()).ToString("0.00000"));
//}
}
}
else
{
foreach (DataRow dr in dt.Rows)
{
LoanSchedule loanSchedule = new LoanSchedule();
loanSchedule.LoanID = loanID;
loanSchedule.StartDate = Convert.ToDateTime(dr["StartDate"]);
loanSchedule.EndDate = Convert.ToDateTime(dr["EndDate"]);
loanSchedule.CoupFrac = Convert.ToDecimal(dr["CoupFrac"]);
loanSchedule.Notation = Convert.ToDecimal(dr["Notation"]);
loanSchedule.Amortisation = Convert.ToDecimal(dr["Amortisation"]);
loanSchedule.Factor = Convert.ToDecimal(Convert.ToDecimal(dr["Factor"]).ToString("0.00000"));
loanSchedule.Spread = Convert.ToDecimal(dr["Spread"]);
loanSchedule.AllInRate = Convert.ToDecimal(dr["AllInRate"]);
loanSchedule.CouponPaymentDate = Convert.ToDateTime(dr["CouponPaymentDate"]);
loanSchedule.RiskFreeDP1 = Convert.ToDecimal(dr["RiskFreeDP1"]);
loanSchedule.RiskFreeDP2 = Convert.ToDecimal(dr["RiskFreeDP2"]);
loanSchedule.FloatingRate = Convert.ToDecimal(dr["FloatingRate"]);
loanSchedule.Interest = Convert.ToDecimal(dr["Interest"]);
loanSchedule.Days = Convert.ToInt16(dr["Days"]);
loanSchedule.AmortisationInt = Convert.ToDecimal(dr["AmortisationInt"]);
SaveLoanSchedule(loanSchedule);
}
}
context.SaveChanges();
}
}
catch (Exception)
{
}
}
public Login SetRole(int id, string role)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
Login login = context.Logins.FirstOrDefault(c => c.ID == id);
if (login != null)
{
login.Role = role;
}
context.SaveChanges();
return login;
}
}
public string AddImportedLoans(Loans item)
{
LogsBLL logBL = new LogsBLL();
string str = "";
try
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
string codeName = item.CodeName;
if (CheckForLoanCode(item.CodeName))
{
context.AddToLoans(item);
context.SaveChanges();
//item = GetLoanByCode(codeName);
LoanScheduleBL loanScheduleBL = new LoanScheduleBL();
string couponDT = item.CouponDate.ToString();
DateTime cpnDT;
if (couponDT == string.Empty)
cpnDT = AddBusinessDays(DateTime.Now, 10);
else
cpnDT = Convert.ToDateTime(item.CouponDate);
DateTime tradeDate = DateTime.Now;
DataTable dtSchedule = loanScheduleBL.GenerateTable(15, Convert.ToInt16(item.NoOfAmortisationPoint), Convert.ToDateTime(item.AmortisationsStartPoint), item.CouponFrequency.ToString(), item.Notional.ToString(), Convert.ToDateTime(item.Maturity_Date), Convert.ToDateTime(item.CouponDate), Convert.ToDecimal(item.Margin), Convert.ToString(item.Currency), Convert.ToDateTime(tradeDate), AddBusinessDays(Convert.ToDateTime(tradeDate), 10));
if (dtSchedule != null)
{
try
{
foreach (DataRow dr in dtSchedule.Rows)
{
LoanSchedule loanSchedule = new LoanSchedule();
loanSchedule.LoanID = item.ID;
loanSchedule.StartDate = Convert.ToDateTime(dr["StartDate"]);
loanSchedule.EndDate = Convert.ToDateTime(dr["EndDate"]);
loanSchedule.Notation = Convert.ToDecimal(dr["Notation"]);
loanSchedule.CoupFrac = Convert.ToDecimal(dr["CoupFrac"]);
loanSchedule.Amortisation = Convert.ToDecimal(dr["Amortisation"]);
loanSchedule.Factor = Convert.ToDecimal(dr["Factor"]);
loanSchedule.CouponPaymentDate = Convert.ToDateTime(dr["CouponPaymentDate"]);
loanSchedule.Spread = Convert.ToDecimal(dr["Spread"]);
loanSchedule.RiskFreeDP1 = Convert.ToDecimal(dr["RiskFreeDP1"]);
loanSchedule.RiskFreeDP2 = Convert.ToDecimal(dr["RiskFreeDP2"]);
loanSchedule.FloatingRate = Convert.ToDecimal(dr["FloatingRate"]);
loanSchedule.AllInRate = Convert.ToDecimal(dr["AllInRate"]);
loanSchedule.Interest = Convert.ToDecimal(dr["Interest"]);
loanSchedule.Days = Convert.ToInt16(dr["Days"]);
loanSchedule.AmortisationInt = Convert.ToDecimal(dr["AmortisationInt"]);
loanScheduleBL.SaveLoanSchedule(loanSchedule);
}
}
catch (Exception ex)
{
str = ex.Message;
// str = ex.Message;
}
}
}
else
{
DuplicateLoan loan = new DuplicateLoan();
loan.CodeName = item.CodeName;
loan.Borrower = item.Borrower;
loan.Country = item.Country;
loan.Sector = item.Sector;
loan.Maturity_Date = item.Maturity_Date;
loan.Signing_Date = item.Signing_Date;
loan.FixedOrFloating = item.FixedOrFloating;
loan.Margin = item.Margin;
loan.Currency = item.Currency;
loan.CouponFrequency = item.CouponFrequency;
loan.FacilitySize = item.FacilitySize;
loan.Bilateral = item.Bilateral;
loan.Amortizing = item.Amortizing;
loan.AmortisationsStartPoint = loan.AmortisationsStartPoint;
loan.CouponDate = loan.CouponDate;
loan.Notional = loan.Notional;
loan.NoOfAmortisationPoint = loan.NoOfAmortisationPoint;
context.AddToDuplicateLoans(loan); context.SaveChanges();
}
}
}
catch (Exception ex)
{
str = ex.Message;
if (ex.InnerException != null)
{
str = str + " :: " + ex.InnerException.Message;
}
}
return str;
}
public Login ResetPasswordPublic(string userName, string newPassword, out string message)
{
using (LoanPriceEntities context = new LoanPriceEntities())
{
Login model = context.Logins.FirstOrDefault(c => c.Name == userName);
if (model == null)
{
message = "User with this name doesn't exist";
return model;
}
else if (model.IsLocked)
{
message = "User account is locked. We cann't reset password.";
return model;
}
// Save password history
PasswordHistory history = new PasswordHistory();
history.ChangedDate = DateTime.Now;
history.ChangedPassword = newPassword;
history.Password = model.Password;
history.UserId = model.ID;
context.PasswordHistories.AddObject(history);
model.Password = newPassword;
model.LastPasswordReset = DateTime.Now;
context.SaveChanges();
message = "Password reseted successfully";
return model;
}
}
