//internal void getSelectedUnitTypes(int loanId, LoanSetupStep1 loanSetupStep1)
//{
// DataHandler dataHandler = new DataHandler();
// List<object[]> paramertList = new List<object[]>();
// paramertList.Add(new object[] { "@loan_id", loanId });
// try
// {
// }
// catch
// {
// }
// using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AutoDealersConnection"].ConnectionString))
// {
// try
// {
// using (SqlCommand cmd = new SqlCommand("spGetLoanUnitTypesByLoanId", con))
// {
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.Add("@loan_id", SqlDbType.Int).Value = loanId;
// con.Open();
// SqlDataReader reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// UnitType unitType = new UnitType();
// unitType.unitTypeId = int.Parse(reader["unit_type_id"].ToString());
// //loanSetupStep1.selectedUnitTypes.Add(unitType);
// }
// reader.Close();
// }
// }
// catch (Exception ex)
// {
// throw ex;
// }
// finally
// {
// con.Close();
// }
// }
//}
internal int UpdateLoanCurtailment(CurtailmentModel curtailmentModel, int loanId)
{
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
bool loanStatus = curtailmentModel.LoanStatus == "Yes" ? true : false;
char calMode = curtailmentModel.CalculationBase == "Full payment" ? 'f' : 'a';
char timeBase = curtailmentModel.TimeBase == "Months" ? 'm' : 'd';
paramertList.Add(new object[] { "@loan_id", loanId });
paramertList.Add(new object[] { "@loan_status", loanStatus });
paramertList.Add(new object[] { "@curtailment_due_date", curtailmentModel.DueDate });
paramertList.Add(new object[] { "@curtailment_auto_remind_email", curtailmentModel.AutoRemindEmail });
paramertList.Add(new object[] { "@curtailment_remind_period", curtailmentModel.EmailRemindPeriod });
paramertList.Add(new object[] { "@curtailment_calculation_type", calMode });
paramertList.Add(new object[] { "@pay_off_type", timeBase });
try
{
return(dataHandler.ExecuteSQLReturn("spUpdateLoanCurtailmentd", paramertList));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy : kasun
/// CreatedDate: 2016/04/04
///
/// Update user right details in user permission table
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public int updateUserRightDetails(User user, int loggedUser)
{
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@user_Id", user.UserId });
paramertList.Add(new object[] { "@logged_user_Id", loggedUser });
paramertList.Add(new object[] { "@loan_id", user.LoanId });
paramertList.Add(new object[] { "@right_id", user.UserRights });
if (user.ReportRights != null && user.ReportRights != "")
{
paramertList.Add(new object[] { "@report_right_id", user.ReportRights });
}
else
{
paramertList.Add(new object[] { "@report_right_id", "" });
}
paramertList.Add(new object[] { "@modify_date", DateTime.Now });
try
{
return(dataHandler.ExecuteSQLReturn("spUpdateUserRightDetails", paramertList));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:Piyumi
/// CreatedDate: 4/21/2016
/// Update Loan Status
/// </summary>
/// <param name="slctdLoanId"></param>
/// <param name="slctdLoanCode"></param>
/// <returns></returns>
public int UpdateLoanStatus(int slctdLoanId, string slctdLoanCode, string slctdActiveDate)
{
if ((slctdLoanId > 0) && (!string.IsNullOrEmpty(slctdLoanCode)) && (!string.IsNullOrEmpty(slctdActiveDate)))
{
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@loan_id", slctdLoanId });
paramertList.Add(new object[] { "@loan_code", slctdLoanCode });
paramertList.Add(new object[] { "@loan_status", 1 });
paramertList.Add(new object[] { "@active_date", DateTime.Parse(slctdActiveDate) });
paramertList.Add(new object[] { "@modified_date", DateTime.Now });
try
{
return(dataHandler.ExecuteSQLReturn("spUpdateLoanStatus", paramertList));
}
catch (Exception ex)
{
throw ex;
}
}
else
{
return(0);
}
}
/// <summary>
/// CreatedBy : Kanishka SHM
/// CreatedDate: 2016/01/16
///
/// Insert User details
///
/// argument : user (User)
///
///
/// UpdatedBy : nadeeka
/// UpdatedDate: 2016/03/03
/// removed existing connection open method and set parameter's to object list and pass stored procedure name to
/// call DataHandler class to save user object
/// </summary>
/// <returns>1</returns>
public int InsertUser(User user)
{
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@user_Id", user.UserId });
paramertList.Add(new object[] { "@user_name", user.UserName });
paramertList.Add(new object[] { "@password", user.Password });
paramertList.Add(new object[] { "@first_name", user.FirstName });
paramertList.Add(new object[] { "@last_name", user.LastName });
paramertList.Add(new object[] { "@email", user.Email });
paramertList.Add(new object[] { "@phone_no", user.PhoneNumber });
paramertList.Add(new object[] { "@status", user.Status });
paramertList.Add(new object[] { "@is_delete", user.IsDelete });
paramertList.Add(new object[] { "@created_by", user.CreatedBy });
paramertList.Add(new object[] { "@create_Date", DateTime.Now });
paramertList.Add(new object[] { "@branch_id", user.BranchId });
paramertList.Add(new object[] { "@role_id", user.RoleId });
paramertList.Add(new object[] { "@Company_id", user.Company_Id });
paramertList.Add(new object[] { "@step_status", user.step_status });
try
{
return(dataHandler.ExecuteSQLReturn("spInsertUser", paramertList));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:Asanka Senarathna
/// CreatedDate: 6/27/2016
/// Update Loan Status for Active loan to inactive
/// </summary>
/// <param name="slctdLoanId"></param>
/// <param name="slctdLoanCode"></param>
/// <returns></returns>
public int UpdateLoanStatus_ActiveInactive(int slctdLoanId, string slctdLoanCode)
{
if ((slctdLoanId > 0) && (!string.IsNullOrEmpty(slctdLoanCode)))
{
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@loan_id", slctdLoanId });
paramertList.Add(new object[] { "@loan_code", slctdLoanCode });
paramertList.Add(new object[] { "@loan_status", 0 });
try
{
return(dataHandler.ExecuteSQLReturn("spUpdateLoanStatus_ActivetoInactive", paramertList));
}
catch (Exception ex)
{
throw ex;
}
}
else
{
return(0);
}
}
/// <summary>
/// Frontend page: dashboard page
/// title: checking is atleast one permission for report access
/// designed: irfan mam
/// User story: DFP 476
/// developed: irfan mam
/// date creaed: 6/23/2016
///
/// </summary>
///
/// <returns>
/// if there is no loan has user rights -> false
/// if there is atleast one user right for any loan -> true
/// </returns>
public bool IsAtleastOnePermissionForReport(int userId)
{
bool ret = false; // set ret value false as default
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>(); // argument list
// add user id to argument list
paramertList.Add(new object[] { "@user_id", userId });
try
{
// if stored proceture return 1
if (dataHandler.ExecuteSQLReturn("isAtleastOnePermissionForReport", paramertList) == 1)
{
// set return value to true
ret = true;
}
}
catch (Exception ex)
{
throw ex;
}
// return the ret value
return(ret);
}
/// <summary>
/// CreatedBy : Kanishka SHM
/// CreatedDate: 2016/01/16
///
/// Insert User details
///
/// argument : user (User)
///
///
/// UpdatedBy : nadeeka
/// UpdatedDate: 2016/03/03
/// removed existing connection open method and set parameter's to object list and pass stored procedure name to
/// call DataHandler class to save user object
/// </summary>
/// <returns>1</returns>
public int InsertUser(User user)
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@user_Id", user.UserId });
paramertList.Add(new object[] { "@user_name", user.UserName });
paramertList.Add(new object[] { "@password", user.Password });
paramertList.Add(new object[] { "@first_name", user.FirstName });
paramertList.Add(new object[] { "@last_name", user.LastName });
paramertList.Add(new object[] { "@email", user.Email });
paramertList.Add(new object[] { "@phone_no", user.PhoneNumber });
paramertList.Add(new object[] { "@status", user.Status });
paramertList.Add(new object[] { "@is_delete", user.IsDelete });
paramertList.Add(new object[] { "@created_by", user.CreatedBy });
paramertList.Add(new object[] { "@create_Date", DateTime.Now });
paramertList.Add(new object[] { "@branch_id", user.BranchId });
paramertList.Add(new object[] { "@role_id", user.RoleId });
paramertList.Add(new object[] { "@Company_id", user.Company_Id });
paramertList.Add(new object[] { "@step_status", user.step_status });
try
{
return dataHandler.ExecuteSQLReturn("spInsertUser", paramertList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:Piyumi
/// CreatedDate:2016/1/27
/// Insert non registered branch
/// </summary>
/// <param name="nonRegBranch"></param>
/// <param name="userId"></param>
/// <returns></returns>
public int insertNonRegBranchDetails(CompanyBranchModel nonRegBranch, int userId, string companyCode)
{
nonRegBranch.MainBranch.BranchCreatedDate = DateTime.Now;
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@user_id", userId });
paramertList.Add(new object[] { "@company_code", companyCode });
if (!string.IsNullOrEmpty(nonRegBranch.MainBranch.BranchCode))
{
paramertList.Add(new object[] { "@branch_code", nonRegBranch.MainBranch.BranchCode.Trim() });
}
else
{
paramertList.Add(new object[] { "@branch_code", nonRegBranch.MainBranch.BranchCode });
}
if (!string.IsNullOrEmpty(nonRegBranch.MainBranch.BranchName))
{
paramertList.Add(new object[] { "@branch_name", nonRegBranch.MainBranch.BranchName.Trim() });
}
paramertList.Add(new object[] { "@branch_address_1", nonRegBranch.MainBranch.BranchAddress1.Trim() });
paramertList.Add(new object[] { "@branch_address_2", nonRegBranch.MainBranch.BranchAddress2 ?? "" });
paramertList.Add(new object[] { "@state_id", nonRegBranch.MainBranch.StateId });
paramertList.Add(new object[] { "@city", nonRegBranch.MainBranch.BranchCity ?? "" });
if ((nonRegBranch.MainBranch.Extention != null) && (nonRegBranch.MainBranch.Extention.ToString() != ""))
{
nonRegBranch.MainBranch.BranchZip = nonRegBranch.MainBranch.ZipPre + "-" + nonRegBranch.MainBranch.Extention;
}
else
{
nonRegBranch.MainBranch.BranchZip = nonRegBranch.MainBranch.ZipPre;
}
paramertList.Add(new object[] { "@zip", nonRegBranch.MainBranch.BranchZip.Trim() });
paramertList.Add(new object[] { "@email", nonRegBranch.MainBranch.BranchEmail ?? "" });
paramertList.Add(new object[] { "@phone_num_1", nonRegBranch.MainBranch.BranchPhoneNum1.Trim() });
paramertList.Add(new object[] { "@phone_num_2", nonRegBranch.MainBranch.BranchPhoneNum2 ?? "" });
paramertList.Add(new object[] { "@phone_num_3", nonRegBranch.MainBranch.BranchPhoneNum3 ?? "" });
paramertList.Add(new object[] { "@fax", nonRegBranch.MainBranch.BranchFax ?? "" });
paramertList.Add(new object[] { "@created_date", DateTime.Now });
paramertList.Add(new object[] { "@company_id", nonRegBranch.MainBranch.BranchCompany });
paramertList.Add(new object[] { "@branch_id", nonRegBranch.MainBranch.BranchCreatedBy });
try
{
return(dataHandler.ExecuteSQLReturn("spInsertNonRegisteredBranch", paramertList));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy : Kasun Smarawickrama
/// CreatedDate: 2016/01/14
///
/// Retrive User Level by userid
/// </summary>
/// <param name="userId">userid</param>
/// <returns></returns>
/// UpdatedBy : Asanka Senarathna
public int GetUserLevelByUserId(int userId)
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@userId", userId });
try
{
return dataHandler.ExecuteSQLReturn("spGetUserLevelByUserId", paramertList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:Piyumi
/// CreatedDate:2016/2/9
/// Insert details of title related to a loanId
/// </summary>
/// <param name="loanId"></param>
/// <returns>countVal</returns>
public int insertTitleDetails(Title title)
{
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@is_title_tracked", title.IsTitleTrack });
if (title.IsTitleTrack)
{
//paramertList.Add(new object[] { "@title_accept_method", title.TitleAcceptMethod });
paramertList.Add(new object[] { "@title_received_time_period", title.ReceivedTimeLimit });
paramertList.Add(new object[] { "@auto_remind_period", title.RemindPeriod });
paramertList.Add(new object[] { "@auto_remind_email", title.RemindEmail });
}
else
{
// paramertList.Add(new object[] { "@title_accept_method", null });
paramertList.Add(new object[] { "@title_received_time_period", null });
paramertList.Add(new object[] { "@auto_remind_period", null });
paramertList.Add(new object[] { "@auto_remind_email", null });
//paramertList.Add(new object[] { "@need_scan_copy", 0 });
}
paramertList.Add(new object[] { "@is_receipt_required", title.IsReceipRequired });
if (title.IsReceipRequired)
{
paramertList.Add(new object[] { "receipt_required_method", title.ReceiptRequiredMethod });
}
else
{
paramertList.Add(new object[] { "receipt_required_method", null });
}
if (title.NeedScanCopy)
{
paramertList.Add(new object[] { "@need_scan_copy", 1 });
}
else
{
paramertList.Add(new object[] { "@need_scan_copy", 0 });
}
paramertList.Add(new object[] { "@loan_id", title.LoanId });
try
{
return(dataHandler.ExecuteSQLReturn("spInsertTitleDetails", paramertList));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:Piyumi
/// CreatedDate:4/23/2016
/// edit user at dashboard
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public int UpdateUser(User user, int userId)
{
if (user != null)
{
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@user_id", user.UserId });
paramertList.Add(new object[] { "@current_password", user.CurrentPassword });
paramertList.Add(new object[] { "@password", user.Password });
paramertList.Add(new object[] { "@first_name", user.FirstName });
paramertList.Add(new object[] { "@last_name", user.LastName });
paramertList.Add(new object[] { "@email", user.Email });
if (user.RoleId == 1)
{
paramertList.Add(new object[] { "@phone_no", user.PhoneNumber });
}
else
{
paramertList.Add(new object[] { "@phone_no", user.PhoneNumber2 });
}
if (user.Status)
{
paramertList.Add(new object[] { "@status", 1 });
}
else
{
paramertList.Add(new object[] { "@status", 0 });
}
paramertList.Add(new object[] { "@modified_date", DateTime.Now });
paramertList.Add(new object[] { "@modified_by", userId });
try
{
return(dataHandler.ExecuteSQLReturn("spUpdateUser", paramertList));
}
catch (Exception ex)
{
throw ex;
}
}
else
{
return(0);
}
}
/// <summary>
/// CreatedBy : Kasun Smarawickrama
/// CreatedDate: 2016/01/14
///
/// Retrive User Level by userid
/// </summary>
/// <param name="userId">userid</param>
/// <returns></returns>
/// UpdatedBy : Asanka Senarathna
public int GetUserLevelByUserId(int userId)
{
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@userId", userId });
try
{
return(dataHandler.ExecuteSQLReturn("spGetUserLevelByUserId", paramertList));
}
catch (Exception ex)
{
throw ex;
}
}
public int PayOffUnits(string unitIdList, DateTime payOffDate, int titleStatus)
{
try {
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList1 = new List <object[]>();
paramertList1.Add(new object[] { "@Input", unitIdList });
paramertList1.Add(new object[] { "@pay_date", payOffDate });
paramertList1.Add(new object[] { "@title_status", titleStatus });
return(dataHandler.ExecuteSQLReturn("spCurtailmentsBackup", paramertList1));
}
catch (Exception ex)
{
throw ex;
}
}
public int InsertUserInDashboard(User user)
{
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
if (user != null)
{
paramertList.Add(new object[] { "@user_name", user.UserName });
paramertList.Add(new object[] { "@password", user.Password });
paramertList.Add(new object[] { "@first_name", user.FirstName });
paramertList.Add(new object[] { "@last_name", user.LastName });
paramertList.Add(new object[] { "@email", user.Email });
paramertList.Add(new object[] { "@phone_no", user.PhoneNumber2 });
paramertList.Add(new object[] { "@created_date", DateTime.Now });
paramertList.Add(new object[] { "@created_by", user.CreatedBy });
paramertList.Add(new object[] { "@company_id", user.Company_Id });
paramertList.Add(new object[] { "@branch_id", user.BranchId });
paramertList.Add(new object[] { "@role_id", user.RoleId });
if (user.RoleId == 3)
{
paramertList.Add(new object[] { "@rights", user.UserRights });
paramertList.Add(new object[] { "@report_rights", user.ReportRights });
paramertList.Add(new object[] { "@loan_id", user.LoanId });
}
else
{
paramertList.Add(new object[] { "@rights", "" });
paramertList.Add(new object[] { "@report_rights", "" });
paramertList.Add(new object[] { "@loan_id", 0 });
}
paramertList.Add(new object[] { "@step_status", user.step_status });
paramertList.Add(new object[] { "@status", user.Status });
}
try
{
return(dataHandler.ExecuteSQLReturn("spInsertDashboardUserDetails", paramertList));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy: Piyumi
/// CreatedDate: 3/30/2016
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public int GetLoanCount(int para, int roleId)
{
//int loanCount = 0;
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@para", para });
paramertList.Add(new object[] { "@role", roleId });
try
{
return(dataHandler.ExecuteSQLReturn("spGetLoanCount", paramertList));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy : nadeeka
/// UpdatedDate: 2016/03/04
/// set parameter's to object list and pass stored procedure name to DataHandler class to save branch object
///
/// </summary>
/// <param name="branch">branch object</param>
/// <param name="id"> user id</param>
/// <returns></returns>
public int insertBranch(Branch branch, int id, string companyCode)
{
//if (string.IsNullOrEmpty(branch.BranchCode))
//{
// branch.BranchCode = createBranchCode(getCompanyCodeByUserId(id));
//}
//branch.BranchCompany = getCompanyIdByUserId(id);
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@user_id", id });
paramertList.Add(new object[] { "@branch_code", branch.BranchCode });
paramertList.Add(new object[] { "@company_code", companyCode });
paramertList.Add(new object[] { "@branch_name", branch.BranchName });
paramertList.Add(new object[] { "@branch_address_1", branch.BranchAddress1 });
paramertList.Add(new object[] { "@branch_address_2", branch.BranchAddress2 ?? "" });
paramertList.Add(new object[] { "@state_id", branch.StateId });
paramertList.Add(new object[] { "@city", branch.BranchCity });
if ((branch.Extention != null) && (branch.Extention.ToString() != ""))
{
branch.BranchZip = branch.ZipPre + "-" + branch.Extention;
}
else
{
branch.BranchZip = branch.ZipPre;
}
paramertList.Add(new object[] { "@zip", branch.BranchZip });
paramertList.Add(new object[] { "@email", branch.BranchEmail });
paramertList.Add(new object[] { "@phone_num_1", branch.BranchPhoneNum1 });
paramertList.Add(new object[] { "@phone_num_2", branch.BranchPhoneNum2 ?? "" });
paramertList.Add(new object[] { "@phone_num_3", branch.BranchPhoneNum3 ?? "" });
paramertList.Add(new object[] { "@fax", branch.BranchFax ?? "" });
paramertList.Add(new object[] { "@created_date", DateTime.Now });
paramertList.Add(new object[] { "@company_id", branch.BranchCompany });
try
{
return(dataHandler.ExecuteSQLReturn("spInsertBranch", paramertList));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy: Piyumi
/// CreatedDate: 3/30/2016
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public int GetLoanCount(int para, int roleId)
{
//int loanCount = 0;
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@para", para });
paramertList.Add(new object[] { "@role", roleId });
try
{
return dataHandler.ExecuteSQLReturn("spGetLoanCount", paramertList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:Piyumi
/// CreatedDate:2016/2/5
/// insert details of interest which related to a loan
/// </summary>
/// <returns>countval</returns>
public int insertInterestDetails(Interest interest)
{
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@interest_rate", interest.InterestRate });
paramertList.Add(new object[] { "@paid_date", interest.PaidDate });
paramertList.Add(new object[] { "@payment_period", interest.PaymentPeriod });
paramertList.Add(new object[] { "@auto_remind_email", interest.AutoRemindEmail });
paramertList.Add(new object[] { "@auto_remind_period", interest.RemindPeriod });
paramertList.Add(new object[] { "@loan_id", interest.LoanId });
paramertList.Add(new object[] { "@accrual_method_id", interest.AccrualMethodId });
try
{
return(dataHandler.ExecuteSQLReturn("spInsertInterestDetails", paramertList));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:
/// CreatedDate:4/24/2016
///insert log in every transaction
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public int InsertLog(Log log)
{
try {
if (log != null)
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@date_time", log.DateTime });
paramertList.Add(new object[] { "@user_id", log.UserId });
paramertList.Add(new object[] { "@company_id", log.CompanyId });
paramertList.Add(new object[] { "@branch_id", log.BranchId });
paramertList.Add(new object[] { "@loan_id", log.LoanId });
paramertList.Add(new object[] { "@description", log.Description });
paramertList.Add(new object[] { "@page", log.Page });
try
{
return dataHandler.ExecuteSQLReturn("spInsertLog", paramertList);
}
catch
{
return 0;
}
// return 1;
}
else
{
return 0;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:
/// CreatedDate:4/24/2016
///insert log in every transaction
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public int InsertLog(Log log)
{
try {
if (log != null)
{
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@date_time", log.DateTime });
paramertList.Add(new object[] { "@user_id", log.UserId });
paramertList.Add(new object[] { "@company_id", log.CompanyId });
paramertList.Add(new object[] { "@branch_id", log.BranchId });
paramertList.Add(new object[] { "@loan_id", log.LoanId });
paramertList.Add(new object[] { "@description", log.Description });
paramertList.Add(new object[] { "@page", log.Page });
try
{
return(dataHandler.ExecuteSQLReturn("spInsertLog", paramertList));
}
catch
{
return(0);
}
// return 1;
}
else
{
return(0);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:Asanka Senarathna
/// CreatedDate: 6/27/2016
/// Update Loan Status for Active loan to inactive
/// </summary>
/// <param name="slctdLoanId"></param>
/// <param name="slctdLoanCode"></param>
/// <returns></returns>
public int UpdateLoanStatus_ActiveInactive(int slctdLoanId, string slctdLoanCode)
{
if ((slctdLoanId > 0) && (!string.IsNullOrEmpty(slctdLoanCode)) )
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@loan_id", slctdLoanId });
paramertList.Add(new object[] { "@loan_code", slctdLoanCode });
paramertList.Add(new object[] { "@loan_status", 0 });
try
{
return dataHandler.ExecuteSQLReturn("spUpdateLoanStatus_ActivetoInactive", paramertList);
}
catch (Exception ex)
{
throw ex;
}
}
else
{
return 0;
}
}
/// <summary>
/// CreatedBy : nadeeka
/// UpdatedDate: 2016/03/04
/// set parameter's to object list and pass stored procedure name to DataHandler class to save branch object
///
/// </summary>
/// <param name="branch">branch object</param>
/// <param name="id"> user id</param>
/// <returns></returns>
public int insertBranch(Branch branch, int id,string companyCode)
{
//if (string.IsNullOrEmpty(branch.BranchCode))
//{
// branch.BranchCode = createBranchCode(getCompanyCodeByUserId(id));
//}
//branch.BranchCompany = getCompanyIdByUserId(id);
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@user_id", id });
paramertList.Add(new object[] { "@branch_code", branch.BranchCode });
paramertList.Add(new object[] { "@company_code", companyCode });
paramertList.Add(new object[] { "@branch_name", branch.BranchName });
paramertList.Add(new object[] { "@branch_address_1", branch.BranchAddress1 });
paramertList.Add(new object[] { "@branch_address_2", branch.BranchAddress2 ?? "" });
paramertList.Add(new object[] { "@state_id", branch.StateId });
paramertList.Add(new object[] { "@city", branch.BranchCity });
if ((branch.Extention != null) && (branch.Extention.ToString() != ""))
{
branch.BranchZip = branch.ZipPre + "-" + branch.Extention;
}
else
{
branch.BranchZip = branch.ZipPre;
}
paramertList.Add(new object[] { "@zip", branch.BranchZip });
paramertList.Add(new object[] { "@email", branch.BranchEmail });
paramertList.Add(new object[] { "@phone_num_1", branch.BranchPhoneNum1 });
paramertList.Add(new object[] { "@phone_num_2", branch.BranchPhoneNum2 ?? "" });
paramertList.Add(new object[] { "@phone_num_3", branch.BranchPhoneNum3 ?? "" });
paramertList.Add(new object[] { "@fax", branch.BranchFax ?? "" });
paramertList.Add(new object[] { "@created_date", DateTime.Now });
paramertList.Add(new object[] { "@company_id", branch.BranchCompany });
try
{
return dataHandler.ExecuteSQLReturn("spInsertBranch", paramertList);
}
catch (Exception ex)
{
throw ex;
}
}
public int insertNonRegBranchDetails(CompanyBranchModel nonRegBranch, int userId)
{
nonRegBranch.MainBranch.BranchCreatedDate = DateTime.Now;
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@user_id", userId });
//paramertList.Add(new object[] { "@company_code", companyCode });
paramertList.Add(new object[] { "@branch_code", nonRegBranch.MainBranch.BranchCode.Trim() });
if (!string.IsNullOrEmpty(nonRegBranch.MainBranch.BranchName))
{
paramertList.Add(new object[] { "@branch_name", nonRegBranch.MainBranch.BranchName.Trim() });
}
paramertList.Add(new object[] { "@branch_address_1", nonRegBranch.MainBranch.BranchAddress1.Trim() });
paramertList.Add(new object[] { "@branch_address_2", nonRegBranch.MainBranch.BranchAddress2 ?? "" });
paramertList.Add(new object[] { "@state_id", nonRegBranch.MainBranch.StateId });
paramertList.Add(new object[] { "@city", nonRegBranch.MainBranch.BranchCity ?? "" });
if ((nonRegBranch.MainBranch.Extention != null) && (nonRegBranch.MainBranch.Extention.ToString() != ""))
{
nonRegBranch.MainBranch.BranchZip = nonRegBranch.MainBranch.ZipPre + "-" + nonRegBranch.MainBranch.Extention;
}
else
{
nonRegBranch.MainBranch.BranchZip = nonRegBranch.MainBranch.ZipPre;
}
paramertList.Add(new object[] { "@zip", nonRegBranch.MainBranch.BranchZip.Trim() });
paramertList.Add(new object[] { "@email", nonRegBranch.MainBranch.BranchEmail ?? "" });
paramertList.Add(new object[] { "@phone_num_1", nonRegBranch.MainBranch.BranchPhoneNum1.Trim() });
paramertList.Add(new object[] { "@phone_num_2", nonRegBranch.MainBranch.BranchPhoneNum2 ?? "" });
paramertList.Add(new object[] { "@phone_num_3", nonRegBranch.MainBranch.BranchPhoneNum3 ?? "" });
paramertList.Add(new object[] { "@fax", nonRegBranch.MainBranch.BranchFax ?? "" });
paramertList.Add(new object[] { "@created_date", DateTime.Now });
paramertList.Add(new object[] { "@company_id", nonRegBranch.MainBranch.BranchCompany });
paramertList.Add(new object[] { "@branch_id", nonRegBranch.MainBranch.BranchCreatedBy });
try
{
return dataHandler.ExecuteSQLReturn("spInsertNonRegisteredBranch", paramertList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:Piyumi
/// CreatedDate:5/3/2016
/// Update Company details
/// </summary>
/// <param name="company"></param>
/// <returns></returns>
public int UpdateCompany(Company company,int userId)
{
if (company != null)
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@company_id", company.CompanyId });
paramertList.Add(new object[] { "@company_name", company.CompanyName });
paramertList.Add(new object[] { "@address1", company.CompanyAddress1 });
if (!string.IsNullOrEmpty(company.CompanyAddress2)){
paramertList.Add(new object[] { "@address2", company.CompanyAddress2 });
}
else
{
paramertList.Add(new object[] { "@address2", null });
}
paramertList.Add(new object[] { "@city",company.City });
paramertList.Add(new object[] { "@state", company.StateId });
paramertList.Add(new object[] { "@zip", company.Zip });
paramertList.Add(new object[] { "@phone_no", company.PhoneNum1 });
if (!string.IsNullOrEmpty(company.PhoneNum2))
{
paramertList.Add(new object[] { "@phone_no2", company.PhoneNum2 });
}
else
{
paramertList.Add(new object[] { "@phone_no2", null });
}
if (!string.IsNullOrEmpty(company.PhoneNum3))
{
paramertList.Add(new object[] { "@phone_no3", company.PhoneNum3 });
}
else
{
paramertList.Add(new object[] { "@phone_no3", null });
}
if (!string.IsNullOrEmpty(company.Fax))
{
paramertList.Add(new object[] { "@fax", company.Fax });
}
else
{
paramertList.Add(new object[] { "@fax", null });
}
if (!string.IsNullOrEmpty(company.Email))
{
paramertList.Add(new object[] { "@email", company.Email });
}
else
{
paramertList.Add(new object[] { "@email", null });
}
if (!string.IsNullOrEmpty(company.WebsiteUrl))
{
paramertList.Add(new object[] { "@web", company.WebsiteUrl });
}
else
{
paramertList.Add(new object[] { "@web", null });
}
paramertList.Add(new object[] { "@modified_date", DateTime.Now });
paramertList.Add(new object[] { "@modified_by", userId });
try
{
return dataHandler.ExecuteSQLReturn("spUpdateCompany", paramertList);
}
catch (Exception ex)
{
throw ex;
}
}
else
{
return 0;
}
}
/// <summary>
/// CreatedBy:Piyumi
/// CreatedDate:2016/2/5
/// insert details of interest which related to a loan
/// </summary>
/// <returns>countval</returns>
public int insertInterestDetails(Interest interest)
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@interest_rate", interest.InterestRate });
paramertList.Add(new object[] { "@paid_date", interest.PaidDate });
paramertList.Add(new object[] { "@payment_period", interest.PaymentPeriod });
paramertList.Add(new object[] { "@auto_remind_email", interest.AutoRemindEmail });
paramertList.Add(new object[] { "@auto_remind_period", interest.RemindPeriod });
paramertList.Add(new object[] { "@loan_id", interest.LoanId });
paramertList.Add(new object[] { "@accrual_method_id", interest.AccrualMethodId });
try
{
return dataHandler.ExecuteSQLReturn("spInsertInterestDetails", paramertList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:Piyumi
/// CreatedDate:2016/2/9
/// Insert details of title related to a loanId
/// </summary>
/// <param name="loanId"></param>
/// <returns>countVal</returns>
public int insertTitleDetails(Title title)
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@is_title_tracked", title.IsTitleTrack });
if (title.IsTitleTrack)
{
//paramertList.Add(new object[] { "@title_accept_method", title.TitleAcceptMethod });
paramertList.Add(new object[] { "@title_received_time_period", title.ReceivedTimeLimit });
paramertList.Add(new object[] { "@auto_remind_period", title.RemindPeriod });
paramertList.Add(new object[] { "@auto_remind_email", title.RemindEmail });
}
else
{
// paramertList.Add(new object[] { "@title_accept_method", null });
paramertList.Add(new object[] { "@title_received_time_period", null });
paramertList.Add(new object[] { "@auto_remind_period", null });
paramertList.Add(new object[] { "@auto_remind_email", null});
//paramertList.Add(new object[] { "@need_scan_copy", 0 });
}
paramertList.Add(new object[] { "@is_receipt_required", title.IsReceipRequired });
if (title.IsReceipRequired)
{
paramertList.Add(new object[] { "receipt_required_method", title.ReceiptRequiredMethod });
}
else
{
paramertList.Add(new object[] { "receipt_required_method",null});
}
if (title.NeedScanCopy)
{
paramertList.Add(new object[] { "@need_scan_copy", 1 });
}
else
{
paramertList.Add(new object[] { "@need_scan_copy", 0 });
}
paramertList.Add(new object[] { "@loan_id", title.LoanId });
try
{
return dataHandler.ExecuteSQLReturn("spInsertTitleDetails", paramertList);
}
catch(Exception ex)
{
throw ex;
}
}
public int PayOffUnits(string unitIdList, DateTime payOffDate, int titleStatus)
{
try {
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList1 = new List<object[]>();
paramertList1.Add(new object[] { "@Input", unitIdList });
paramertList1.Add(new object[] { "@pay_date", payOffDate });
paramertList1.Add(new object[] { "@title_status", titleStatus });
return dataHandler.ExecuteSQLReturn("spCurtailmentsBackup", paramertList1);
}
catch (Exception ex)
{
throw ex;
}
}
//internal void getSelectedUnitTypes(int loanId, LoanSetupStep1 loanSetupStep1)
//{
// DataHandler dataHandler = new DataHandler();
// List<object[]> paramertList = new List<object[]>();
// paramertList.Add(new object[] { "@loan_id", loanId });
// try
// {
// }
// catch
// {
// }
// using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AutoDealersConnection"].ConnectionString))
// {
// try
// {
// using (SqlCommand cmd = new SqlCommand("spGetLoanUnitTypesByLoanId", con))
// {
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.Add("@loan_id", SqlDbType.Int).Value = loanId;
// con.Open();
// SqlDataReader reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// UnitType unitType = new UnitType();
// unitType.unitTypeId = int.Parse(reader["unit_type_id"].ToString());
// //loanSetupStep1.selectedUnitTypes.Add(unitType);
// }
// reader.Close();
// }
// }
// catch (Exception ex)
// {
// throw ex;
// }
// finally
// {
// con.Close();
// }
// }
//}
internal int UpdateLoanCurtailment(CurtailmentModel curtailmentModel, int loanId)
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
bool loanStatus = curtailmentModel.LoanStatus == "Yes" ? true : false;
char calMode = curtailmentModel.CalculationBase == "Full payment" ? 'f' : 'a';
char timeBase = curtailmentModel.TimeBase == "Months" ? 'm' : 'd';
paramertList.Add(new object[] { "@loan_id", loanId });
paramertList.Add(new object[] { "@loan_status", loanStatus });
paramertList.Add(new object[] { "@curtailment_due_date", curtailmentModel.DueDate });
paramertList.Add(new object[] { "@curtailment_auto_remind_email", curtailmentModel.AutoRemindEmail});
paramertList.Add(new object[] { "@curtailment_remind_period", curtailmentModel.EmailRemindPeriod });
paramertList.Add(new object[] { "@curtailment_calculation_type", calMode });
paramertList.Add(new object[] { "@pay_off_type", timeBase });
try
{
return dataHandler.ExecuteSQLReturn("spUpdateLoanCurtailmentd", paramertList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy : kasun
/// CreatedDate: 2016/04/04
///
/// Update user right details in user permission table
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public int updateUserRightDetails(User user, int loggedUser)
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@user_Id", user.UserId });
paramertList.Add(new object[] { "@logged_user_Id", loggedUser });
paramertList.Add(new object[] { "@loan_id", user.LoanId });
paramertList.Add(new object[] { "@right_id", user.UserRights });
if (user.ReportRights != null && user.ReportRights != "")
{
paramertList.Add(new object[] { "@report_right_id", user.ReportRights });
}
else {
paramertList.Add(new object[] { "@report_right_id", "" });
}
paramertList.Add(new object[] { "@modify_date", DateTime.Now });
try
{
return dataHandler.ExecuteSQLReturn("spUpdateUserRightDetails", paramertList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Frontend page: dashboard page
/// title: checking is atleast one permission for report access
/// designed: irfan mam
/// User story: DFP 476
/// developed: irfan mam
/// date creaed: 6/23/2016
///
/// </summary>
///
/// <returns>
/// if there is no loan has user rights -> false
/// if there is atleast one user right for any loan -> true
/// </returns>
public bool IsAtleastOnePermissionForReport( int userId)
{
bool ret = false; // set ret value false as default
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>(); // argument list
// add user id to argument list
paramertList.Add(new object[] { "@user_id", userId });
try
{
// if stored proceture return 1
if( dataHandler.ExecuteSQLReturn("isAtleastOnePermissionForReport", paramertList)== 1)
{
// set return value to true
ret = true;
}
}
catch (Exception ex)
{
throw ex;
}
// return the ret value
return ret;
}
public int InsertUserInDashboard(User user)
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
if (user != null)
{
paramertList.Add(new object[] { "@user_name", user.UserName });
paramertList.Add(new object[] { "@password", user.Password });
paramertList.Add(new object[] { "@first_name", user.FirstName });
paramertList.Add(new object[] { "@last_name", user.LastName });
paramertList.Add(new object[] { "@email", user.Email });
paramertList.Add(new object[] { "@phone_no", user.PhoneNumber2 });
paramertList.Add(new object[] { "@created_date", DateTime.Now });
paramertList.Add(new object[] { "@created_by", user.CreatedBy});
paramertList.Add(new object[] { "@company_id", user.Company_Id });
paramertList.Add(new object[] { "@branch_id", user.BranchId });
paramertList.Add(new object[] { "@role_id", user.RoleId});
if (user.RoleId == 3)
{
paramertList.Add(new object[] { "@rights", user.UserRights });
paramertList.Add(new object[] { "@report_rights", user.ReportRights });
paramertList.Add(new object[] { "@loan_id", user.LoanId });
}
else
{
paramertList.Add(new object[] { "@rights",""});
paramertList.Add(new object[] { "@report_rights", "" });
paramertList.Add(new object[] { "@loan_id", 0 });
}
paramertList.Add(new object[] { "@step_status", user.step_status });
paramertList.Add(new object[] { "@status", user.Status });
}
try
{
return dataHandler.ExecuteSQLReturn("spInsertDashboardUserDetails", paramertList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy : Piyumi
/// CreatedDate: 05/04/2016
///
/// Insert company in setup process
/// </summary>
/// <param name="company"></param>
/// <returns></returns>
public int InsertNonRegisteredCompanyAtDashboard(PartnerCompany company)
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@company_name", company.CompanyName.Trim() });
if (!string.IsNullOrEmpty(company.CompanyCode))
{
paramertList.Add(new object[] { "@company_code", company.CompanyCode.Trim() });
}
else
{
paramertList.Add(new object[] { "@company_code", company.CompanyCode});
}
paramertList.Add(new object[] { "@company_address_1", company.CompanyAddress1.Trim() });
if (!string.IsNullOrEmpty(company.CompanyAddress2))
{
company.CompanyAddress2.Trim();
paramertList.Add(new object[] { "@company_address_2", company.CompanyAddress2.Trim() });
}
paramertList.Add(new object[] { "@stateId", company.StateId });
paramertList.Add(new object[] { "@city", company.City.Trim() });
paramertList.Add(new object[] { "@zip", company.Zip.Trim() });
if (!string.IsNullOrEmpty(company.Email))
{
company.Email.Trim();
}
if (!string.IsNullOrEmpty(company.PhoneNum2))
{
company.PhoneNum2.Trim();
}
if (!string.IsNullOrEmpty(company.PhoneNum3))
{
company.PhoneNum3.Trim();
}
paramertList.Add(new object[] { "@email", company.Email });
paramertList.Add(new object[] { "@phone_num_1", company.PhoneNum1 });
paramertList.Add(new object[] { "@phone_num_2", company.PhoneNum2 });
paramertList.Add(new object[] { "@phone_num_3", company.PhoneNum3 });
paramertList.Add(new object[] { "@fax", company.Fax });
paramertList.Add(new object[] { "@website_url", company.WebsiteUrl });
paramertList.Add(new object[] { "@created_by", company.CreatedBy });
paramertList.Add(new object[] { "@created_date", DateTime.Now });
paramertList.Add(new object[] { "@company_type", company.TypeId });
paramertList.Add(new object[] { "@reg_company_id", company.RegCompanyId });
try
{
return dataHandler.ExecuteSQLReturn("spInsertNonRegisteredCompanyAtDashboard", paramertList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:Piyumi
/// CreatedDate:4/23/2016
/// edit user at dashboard
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public int UpdateUser(User user, int userId)
{
if (user != null)
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@user_id", user.UserId });
paramertList.Add(new object[] { "@current_password", user.CurrentPassword });
paramertList.Add(new object[] { "@password", user.Password });
paramertList.Add(new object[] { "@first_name", user.FirstName });
paramertList.Add(new object[] { "@last_name", user.LastName });
paramertList.Add(new object[] { "@email", user.Email });
if (user.RoleId == 1)
{
paramertList.Add(new object[] { "@phone_no", user.PhoneNumber });
}
else
{
paramertList.Add(new object[] { "@phone_no", user.PhoneNumber2 });
}
if (user.Status)
{
paramertList.Add(new object[] { "@status", 1 });
}
else {
paramertList.Add(new object[] { "@status", 0 });
}
paramertList.Add(new object[] { "@modified_date", DateTime.Now });
paramertList.Add(new object[] { "@modified_by", userId });
try
{
return dataHandler.ExecuteSQLReturn("spUpdateUser", paramertList);
}
catch (Exception ex)
{
throw ex;
}
}
else
{
return 0;
}
}
/// <summary>
/// CreatedBy:Piyumi
/// CreatedDate: 4/21/2016
/// Update Loan Status
/// </summary>
/// <param name="slctdLoanId"></param>
/// <param name="slctdLoanCode"></param>
/// <returns></returns>
public int UpdateLoanStatus(int slctdLoanId, string slctdLoanCode,string slctdActiveDate)
{
if ((slctdLoanId > 0) && (!string.IsNullOrEmpty(slctdLoanCode)) && (!string.IsNullOrEmpty(slctdActiveDate)))
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@loan_id", slctdLoanId });
paramertList.Add(new object[] { "@loan_code", slctdLoanCode });
paramertList.Add(new object[] { "@loan_status", 1 });
paramertList.Add(new object[] { "@active_date", DateTime.Parse(slctdActiveDate) });
paramertList.Add(new object[] { "@modified_date", DateTime.Now });
try
{
return dataHandler.ExecuteSQLReturn("spUpdateLoanStatus", paramertList);
}
catch (Exception ex)
{
throw ex;
}
}
else
{
return 0;
}
}