/*
Frontend page: Fee Page
Title: Getting Curtailment Shedule
Designed: Irfan Mam
User story:
Developed: Irfan MAM
Date created: 4/22/2016
*/
public bool GetFeesDueDates(int loanId, out string advPayDueDate, out string monPayDueDate, out string lotPayDueDate)
{
try
{
advPayDueDate = "";
monPayDueDate = "";
lotPayDueDate = "";
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@loan_id", loanId });
DataSet dataSet = dataHandler.GetDataSet("spGetFeesDueDates", paramertList);
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
advPayDueDate = dataRow["adv_payment_due_date"].ToString();
monPayDueDate = dataRow["mon_payment_due_date"].ToString();
lotPayDueDate = dataRow["lot_payment_due_date"].ToString();
}
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy: Kasun
/// CreatedDate:05/04/2016
///
/// get loan details by loan code
/// </summary>
/// <param name="loanCode"></param>
/// <returns></returns>
public Loan GetLoanByLoanCode(int loanId,string loanCode)
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@loan_code", loanCode });
paramertList.Add(new object[] { "@loan_id", loanId });
Loan loan = new Loan();
try
{
DataSet dataSet = dataHandler.GetDataSet("spGetLoanByLoanCode", paramertList);
if (dataSet != null && dataSet.Tables.Count != 0)
{
DataRow dataRow = dataSet.Tables[0].Rows[0];
loan.LoanId = Convert.ToInt32(dataRow["loan_id"]);
loan.LoanAmount = Convert.ToDecimal(dataRow["loan_amount"]);
loan.LoanCode = dataRow["loan_code"].ToString();
loan.MaturityDate = Convert.ToDateTime(dataRow["maturity_date"]);
loan.StartDate = Convert.ToDateTime(dataRow["start_date"]);
loan.LoanStatus = Convert.ToBoolean(dataRow["loan_status"]);
loan.LoanNumber = dataRow["loan_number"].ToString();
return loan;
}
else
{
return null;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:Piyumi
/// CreatedDate:2016/1/13
/// Retrieve user_name and created person of users
/// </summary>
/// <param name="userType"></param>
/// <returns>userLogin object</returns>
public List<UserLogin> getUserByType(int levelId, int userId)
{
List<UserLogin> UserList = new List<UserLogin>();
DataHandler dataHandler = new DataHandler();
int userRole = getUserRole(userId);
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@level_id", levelId });
paramertList.Add(new object[] { "@user_id", userId });
try
{
DataSet dataSet = dataHandler.GetDataSet("spGetUserLoginDetailsByType");
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
UserLogin user = new UserLogin();
user.loginId = userId;
user.userId = int.Parse(dataRow["user_id"].ToString());
user.userName = dataRow["user_name"].ToString();
user.createdBy = int.Parse(dataRow["created_by"].ToString());
user.createdByRole = getUserRole(user.createdBy);
user.createdName = getUserNameById(user.createdBy);
user.roleId = int.Parse(dataRow["role_id"].ToString());
if (userRole == 1)
{
user.isEdit = true;
}
else if ((userRole == 2) && (levelId == 1))
{
user.isEdit = false;
}
else if ((userRole == 2) && (levelId == 2))
{
user.isEdit = true;
}
else if ((userRole == 2) && (levelId == 3))
{
user.isEdit = true;
}
UserList.Add(user);
}
return UserList;
}
else
{
return null;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy: MAM. IRFAN
/// CreatedDate: 2016/01/16
///
/// Getting all branches
///
///
/// UpdatedBy : nadeeka
/// UpdatedDate: 2016/03/04
/// removed existing connection open method and set parameter to object list and pass stored procedure name to
/// call DataHandler class method and getting dataset object,
/// create and return branche object list using that dataset
///
/// </summary>
/// <returns> a list contain all branches</returns>
///
public List<Branch> getBranches(int companyId)
{
List<Branch> branchesLists = new List<Branch>();
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@companyId", companyId });
try
{
DataSet dataSet = dataHandler.GetDataSet("spGetBranchesByCompanyId", paramertList);
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
Branch branch = new Branch();
branch.BranchId = Convert.ToInt32(dataRow["branch_id"].ToString());
branch.BranchName = dataRow["branch_name"].ToString();
branch.BranchCode = dataRow["branch_code"].ToString();
branch.BranchAddress1 = dataRow["branch_address_1"].ToString();
branchesLists.Add(branch);
}
return branchesLists;
}
else
{
return null;
}
}
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;
}
/// <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>
/// Frontend Page : Join Dealer
/// Title: Get all users for given company id
/// Designed: Piyumi Perera
/// User story:
/// Developed: Piyumi Perera
/// Date Created: 05/26/2016
/// </summary>
/// <param name="companyId"></param>
/// <returns></returns>
public List<User> GetUserListByCompany(int companyId)
{
List<User> users = new List<User>();
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@company_id", companyId });
DataSet dataSet = dataHandler.GetDataSet("spGetUsersbyCompany2", paramertList);
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
User user = new User();
user.UserId = Convert.ToInt32(dataRow["user_id"].ToString());
user.UserName = dataRow["user_name"].ToString();
user.Password = dataRow["password"].ToString();
user.FirstName = dataRow["first_name"].ToString();
user.LastName = dataRow["last_name"].ToString();
user.NewEmail = dataRow["email"].ToString();
user.PhoneNumber = dataRow["phone_no"].ToString();
user.BranchId = Convert.ToInt32(dataRow["branch_id"].ToString());
user.RoleId = Convert.ToInt32(dataRow["role_id"].ToString());
users.Add(user);
}
return users;
}
else
{
return null;
}
}
/// <summary>
/// CreatedBy:Piyumi
/// CreatedDate:4/27/2016
/// </summary>
/// <param name="company_Id"></param>
/// <returns></returns>
public List<UserRole> GetAllUserRoles(int company_Id)
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@company_id", company_Id });
List<UserRole> userRoleList = new List<UserRole>();
try
{
DataSet dataSet = dataHandler.GetDataSet("spGetAllUserRolsByCompany", paramertList);
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
UserRole role = new UserRole
{
RoleId = Convert.ToInt32(dataRow["role_id"]),
RoleName = dataRow["role_name"].ToString()
};
userRoleList.Add(role);
}
return userRoleList;
}
else
{
return null;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy : Nadeeka
/// CreatedDate: 2016/02/09
///
/// retreive Curtailment Detail By CurtailmentId
///
/// argument : curtailment_id (int)
///
/// </summary>
/// <returns>curtailment list</returns>
public List<Curtailment> retreiveCurtailmentByLoanId(int loanId)
{
try
{
List<Curtailment> lstCurtailment = new List<Curtailment>();
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@loan_id", loanId });
DataSet dataSet = dataHandler.GetDataSet("spRetrieveCurtailmentByLoanId", paramertList);
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
Curtailment curtailment = new Curtailment();
curtailment.CurtailmentId = int.Parse(dataRow["curtailment_id"].ToString());
curtailment.LoanId = int.Parse(dataRow["loan_id"].ToString());
curtailment.TimePeriod = Convert.ToInt32(dataRow["time_period"]);
curtailment.Percentage = Convert.ToInt32(dataRow["percentage"].ToString());
lstCurtailment.Add(curtailment);
}
return lstCurtailment;
}
else
{
return null;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:Irfan MAM
/// CreatedDate:2016/2/9
/// getting all unit types
/// </summary>
/// <returns>IList<UnitType></returns>
/// UpdatedBy:Asanka Senarathna
///
internal IList<UnitType> getAllUnitTypes()
{
List<UnitType> unitTypes = new List<UnitType>();
DataHandler dataHandler = new DataHandler();
try
{
DataSet dataSet = dataHandler.GetDataSet("spGetAllUnitTypes", null);
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
UnitType unitType = new UnitType();
unitType.unitTypeName = dataRow["unit_type_name"].ToString();
unitType.unitTypeId = int.Parse(dataRow["unit_type_id"].ToString());
unitType.isSelected = false;
unitTypes.Add(unitType);
}
return unitTypes;
}
else
{
return null;
}
}
catch (Exception ex)
{
throw ex;
}
}
internal string updateCurtailmets(SelectedCurtailmentList curtailmentScheduleModel, int loanId, string dealerEmail)
{
try
{
int i = 1;
XElement xEle = new XElement("Curtailments",
from curtailmentShedule in curtailmentScheduleModel.SelectedCurtailmentSchedules
select new XElement("CurtailmentShedule",
new XElement("CurtNo", curtailmentShedule.CurtNumber),
new XElement("UnitId", curtailmentShedule.UnitId),
new XElement("CurtAmount", curtailmentShedule.CurtAmount),
new XElement("PayDate", curtailmentShedule.PayDate),
new XElement("id", i++)
));
string xmlDoc = xEle.ToString();
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList2 = new List<object[]>();
paramertList2.Add(new object[] { "@loan_id", loanId });
paramertList2.Add(new object[] { "@dealer_email", dealerEmail ?? ""});
paramertList2.Add(new object[] { "@Input", xmlDoc });
return dataHandler.ExecuteSQLWithStringReturnVal("spUpdateCurtailmentSchedule", paramertList2);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
///
///
///
/// UpdatedBy : nadeeka
/// UpdatedDate: 2016/03/06
/// removed existing connection open method
/// call DataHandler class method and getting dataset object,
/// create and return state object list using that dataset
/// </summary>
/// <returns></returns>
public List<State> GetAllStates()
{
List<State> stateList = new List<State>();
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
try
{
DataSet dataSet = dataHandler.GetDataSet("spGetState");
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
State state = new State();
state.StateId = Convert.ToInt32(dataRow["state_id"]);
state.StateName = dataRow["state_name"].ToString();
stateList.Add(state);
}
return stateList;
}
else
{
return null;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:Irfan MAM
/// CreatedDate:2016/2/9
/// check the loan number is unique for a branch
/// </summary>
/// <returns>true or false</returns>
public bool IsUniqueLoanNumberForBranch(string loanNumber, int RegisteredBranchId, User user,int loanId)
{
try
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@loan_number", loanNumber });
paramertList.Add(new object[] { "@branch_id", RegisteredBranchId });
paramertList.Add(new object[] { "@loan_id", loanId });
DataSet dataSet = dataHandler.GetDataSet("spIsUniqueLoanNumberForBranch", paramertList);
if (dataSet != null && dataSet.Tables.Count != 0 && dataSet.Tables[0].Rows.Count != 0)
{
return false;
}
else
{
return true;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy : Kanishka SHM
/// CreatedDate: 2016/01/17
///
/// Get all company types
///
/// argument : None
///
/// UpdatedBy : nadeeka
/// UpdatedDate: 2016/03/06
/// removed existing connection open method
/// call DataHandler class method and getting dataset object,
/// create and return company type object list using that dataset
///
/// </summary>
/// <returns>List<CompanyType></returns>
public List<CompanyType> GetAllCompanyType()
{
List<CompanyType> ctList = new List<CompanyType>();
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
try
{
DataSet dataSet = dataHandler.GetDataSet("spGetAllCompanyType");
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
CompanyType ct = new CompanyType();
ct.TypeId = Convert.ToInt32(dataRow["company_type_id"]);
ct.TypeName = dataRow["company_type_name"].ToString();
ctList.Add(ct);
}
return ctList;
}
else
{
return null;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy : Kasun Samarawickrama
/// CreatedDate: 2016/01/17
///
/// Get user permission permission string which contain rightId's
/// </summary>
/// <param name="userId"> Profile edit users id</param>
/// <returns>Right List, but first Right contain the string, If List have more than 1 value it is going to be an unAuthorize one</returns>
///
public List<Right> getRightsString(int userId, int loanId)
{
List<Right> RightsLists = new List<Right>();
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@userId", userId });
paramertList.Add(new object[] { "@loanId", loanId });
try
{
DataSet dataSet = dataHandler.GetDataSet("spGetRightsStringByUserId", paramertList);
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
Right right = new Right();
right.userId = int.Parse(dataRow["user_id"].ToString());
right.rightsPermissionString = dataRow["right_id"].ToString();
right.reportRightsPermissionString = dataRow["report_rights"].ToString();
RightsLists.Add(right);
}
return RightsLists;
}
else
{
return null;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy : Kasun Samarawickrama
/// CreatedDate: 2016/01/16
///
/// Get all rights in database
/// </summary>
/// <param name="userId"></param>
/// <returns>Right list</returns>
///
public List<Right> getRights()
{
List<Right> RightsLists = new List<Right>();
DataHandler dataHandler = new DataHandler();
try
{
DataSet dataSet = dataHandler.GetDataSet("spGetRights");
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
Right right = new Right();
right.rightId = dataRow["right_id"].ToString();
right.active = false;
right.description = dataRow["description"].ToString();
RightsLists.Add(right);
}
return RightsLists;
}
else
{
return null;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:kasun
/// CreatedDate:2016/4/22
///
/// Get all titles for a loan
/// </summary>
/// <param name="loanId"></param>
/// <returns></returns>
public List<TitleUpload> GetUploadTitlesByLoanId(string unitId)
{
DataHandler dataHandler = new DataHandler();
List<object[]> parameterList = new List<object[]>();
List<TitleUpload> titleList = new List<TitleUpload>();
parameterList.Add(new object[] { "@unit_id", unitId });
try
{
DataSet dataSet = dataHandler.GetDataSet("spGetUploadTitlesByUnitId", parameterList);
if (dataSet != null && dataSet.Tables.Count != 0 && dataSet.Tables[0].Rows.Count != 0)
{
foreach (DataRow reader in dataSet.Tables[0].Rows)
{
TitleUpload title = new TitleUpload();
title.FilePath = reader["file_path"].ToString();
title.UnitId = reader["unit_id"].ToString();
title.OriginalFileName = reader["original_file_name"].ToString();
titleList.Add(title);
}
return titleList;
}
else {
return null;
}
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet SelectRequestAns(User user)
{
try
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@user_id", user.UserId });
return dataHandler.GetDataSet("spGetUserReque", 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 : Kasun Samarawickrama
/// CreatedDate: 2016/01/14
///
/// user login authentication
///
/// UpdatedBy : Asanka
/// UpdatedDate: 2016/03/04
/// 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>
/// <param name="username"></param>
/// <param name="password"></param>
///
/// <returns>userid</returns>
public DataSet CheckUserLogin(User user)
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@userName", user.UserName });
try
{
return dataHandler.GetDataSet("spUserLogin", paramertList);
}
catch (Exception ex)
{
throw ex;
}
}
/*
Frontend page: Reset Password page
Title: Verifying the user account using usedId and token
Designed: Irfan Mam
User story:
Developed: Irfan MAM
Date created: 1/17/2016
*/
public bool verifyAccount(int userId, string token)
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@user_id", userId });
paramertList.Add(new object[] { "@token", token });
paramertList.Add(new object[] { "@expired_date", DateTime.Now });
try
{
return dataHandler.ExecuteSQLWithReturnVal("spverifyAccountBytoken", paramertList) > 0 ? true : false;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Created by :Asanka Senarathna
/// Createddate : 2016/03/30
/// Update User Request (Add Answer to User Question)
/// </summary>
/// <param name="userRequest"></param>
/// <returns></returns>
public int UpdateUserRequestAnswer(UserRequest userRequest)
{
try
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@request_id", userRequest.request_id });
paramertList.Add(new object[] { "@answer", userRequest.answer });
paramertList.Add(new object[] { "@answer_user_id", userRequest.answer_user_id });
return dataHandler.ExecuteSQL("spUpdateUserRequestAnswer", paramertList) ? 1 : 0;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:Piyumi
/// CreatedDate:2016/2/5
/// get all accrual methods to a list
/// </summary>
/// <returns>methodList</returns>
public List<AccrualMethods> GetAllAccrualMethods()
{
List<AccrualMethods> methodList = new List<AccrualMethods>();
DataHandler dataHandler = new DataHandler();
DataSet dataSet = dataHandler.GetDataSetBySQL("spGetAllAccrualMethods");
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
AccrualMethods methods = new AccrualMethods()
{
MethodId = Convert.ToInt32(dataRow["accrual_method_id"]),
MethodName = dataRow["accrual_method_name"].ToString()
};
methodList.Add(methods);
}
return methodList;
}
else
{
return null;
}
//using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["AutoDealersConnection"].ToString()))
//{
// var command = new SqlCommand("spGetAllAccrualMethods", con) { CommandType = CommandType.StoredProcedure };
// con.Open();
// using (var reader = command.ExecuteReader())
// {
// while (reader.Read())
// {
// AccrualMethods methods = new AccrualMethods()
// {
// MethodId = Convert.ToInt32(reader["accrual_method_id"]),
// MethodName = reader["accrual_method_name"].ToString()
// };
// methodList.Add(methods);
// }
// }
//}
//return methodList;
}
/// <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;
}
}
/*
Frontend page: Report Page
Title: Get active account details of (Super Admin/ Admin)
Designed: Irfan Mam
User story: DFP-446
Developed: Irfan MAM
Date created: 6/23/2016
*/
public List<Account> GetAccountDetails(int companyIdORBranchId, int userRole)
{
List<Account> Accounts = new List<Account>();
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
// if user is super admin pass company id for companyIdORBranchId
// if user is a admin pass branch id for companyIdORBranchId
paramertList.Add(new object[] { "@comp_or_branch_id", companyIdORBranchId });
paramertList.Add(new object[] { "@user_role", userRole });
// calling stored procedure
DataSet dataSet = dataHandler.GetDataSet("GetAccountDetailsByUserRole", paramertList);
// biding data to list
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
Account account = new Account();
account.LoanId = Convert.ToInt32(dataRow["loan_id"]);
account.LoanCode = dataRow["loan_code"].ToString();
account.BranchId = Convert.ToInt32(dataRow["branch_id"]);
account.LoanNumber = dataRow["loan_number"].ToString();
account.BranchName = dataRow["branch_name"].ToString();
account.PatBranchName = dataRow["nr_branch_name"].ToString();
account.LoanAmount = Convert.ToDecimal(dataRow["loan_amount"].ToString());
account.UsedAmount = Convert.ToDecimal(dataRow["used_amount"].ToString());
account.ActiveUnits = Convert.ToInt32(dataRow["active_units"]);
account.PatBranchAddress1 = dataRow["branch_address_1"].ToString();
account.PatBranchAddress2 = dataRow["branch_address_2"].ToString();
account.PatCity = dataRow["city"].ToString();
Accounts.Add(account);
}
}
// returning the List of account details of relevant user (Super Admin/ Admin)
return Accounts;
}
/// <summary>
/// CreatedBy:Piyumi
/// CreatedDate:2016/2/23
/// Get all not advanced unit details by loan id
/// </summary>
/// <param name="loanId"></param>
/// <returns>unitList</returns>
public List<Unit> GetNotAdvancedUnitDetailsByLoanId(int loanId)
{
DataHandler dataHandler = new DataHandler();
List<object[]> parameterList = new List<object[]>();
List<Unit> unitList = new List<Unit>();
parameterList.Add(new object[] { "@loan_id", loanId });
try
{
DataSet dataSet = dataHandler.GetDataSet("spGetNotAdvancedUnitDetailsByLoanId", parameterList);
if (dataSet != null && dataSet.Tables.Count != 0 && dataSet.Tables[0].Rows.Count != 0)
{
foreach (DataRow reader in dataSet.Tables[0].Rows)
{
Unit unit = new Unit();
unit.UnitId = reader["unit_id"].ToString();
unit.CreatedDate = Convert.ToDateTime(reader["created_date"].ToString());
unit.IdentificationNumber = reader["identification_number"].ToString();
unit.Year = Convert.ToInt32(reader["year"].ToString());
unit.Make = reader["make"].ToString();
unit.Model = reader["model"].ToString();
unit.Cost = Convert.ToDecimal(reader["cost"].ToString());
unit.AdvanceAmount = Convert.ToDecimal(reader["advance_amount"].ToString());
unit.IsTitleUploaded = Convert.ToBoolean(reader["IsTitleUploaded"]) ? 1 : 0;
unitList.Add(unit);
}
return unitList;
}
else {
return unitList;
}
}
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>
/// Created by : Asanka Senarathna
/// Created date :2016/03/30
/// Insert User request message
/// </summary>
/// <param name="userRequest"></param>
/// <returns></returns>
public int InsertUserRequest(UserRequest userRequest)
{
try
{
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@company_id", userRequest.company_id });
paramertList.Add(new object[] { "@branch_id", userRequest.branch_id });
paramertList.Add(new object[] { "@user_id", userRequest.user_id });
paramertList.Add(new object[] { "@role_id", userRequest.role_id });
paramertList.Add(new object[] { "@loan_code", userRequest.loan_code });
paramertList.Add(new object[] { "@page_name", userRequest.page_name });
paramertList.Add(new object[] { "@topic", userRequest.topic });
paramertList.Add(new object[] { "@message", userRequest.message });
paramertList.Add(new object[] { "@priority_level", userRequest.priority_level });
return dataHandler.ExecuteSQL("spInsertUserRequest", paramertList) ? 1 : 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;
}
}
public List<LoanIdNumber> GetLoanNumbersWithBranch(int companyId)
{
List<LoanIdNumber> loanNumbers = new List<LoanIdNumber>();
DataHandler dataHandler = new DataHandler();
List<object[]> paramertList = new List<object[]>();
paramertList.Add(new object[] { "@company_id", companyId });
DataSet dataSet = dataHandler.GetDataSet("spGetLoanNumbersWithBranch", paramertList);
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
LoanIdNumber loanNumber = new LoanIdNumber();
loanNumber.LoanId = Convert.ToInt32(dataRow["loan_id"]);
loanNumber.LoanNumberB = dataRow["LoanDisplay"].ToString();
loanNumber.BranchId = Convert.ToInt32(dataRow["branch_id"]);
loanNumbers.Add(loanNumber);
}
}
return loanNumbers;
}
/*
*
* Frontend page: Fee Page
* Title: Getting Fees by due date
* Designed: Nadeeka
* User story:
* Developed: Nadeeka
* Date created: 4/21/2016
*
*/
public List <Fees> GetFeesByDueDate(int loanId, DateTime dueDate, string type)
{
try
{
List <Fees> lstFee = new List <Fees>();
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@loan_id", loanId });
paramertList.Add(new object[] { "@bill_due_date", dueDate });
paramertList.Add(new object[] { "@type", type });
DataSet dataSet = dataHandler.GetDataSet("spGetFeesByDueDate", paramertList);
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
Fees fee = new Fees();
fee.FeeId = int.Parse(dataRow["fee_id"].ToString());
fee.UnitId = dataRow["unit_id"].ToString();
fee.LoanId = int.Parse(dataRow["loan_id"].ToString());
fee.Type = dataRow["type"].ToString();
fee.Amount = Convert.ToDecimal(dataRow["amount"].ToString());
fee.Description = dataRow["description"].ToString();
fee.BillDueDate = Convert.ToDateTime(dataRow["bill_due_date"].ToString());
fee.FeeDueDate = Convert.ToDateTime(dataRow["due_date"].ToString());
if (type == "advanceFee")
{
string[] info = fee.Description.Split(',');
if (info != null && info.Length > 0)
{
if (info[1] != "")
{
fee.IdentificationNumber = info[1];
}
if (info.Length > 2 && info[2] != "")
{
fee.Year = Convert.ToInt32(info[2]);
}
if (info.Length > 3 && info[3] != "")
{
fee.Make = info[3];
}
if (info.Length > 4 && info[4] != "")
{
fee.Model = info[4];
}
if (info.Length > 5 && info[5] != "")
{
fee.AdvanceDate = Convert.ToDateTime(info[5]);
}
else
{
fee.AdvanceDate = Convert.ToDateTime(dataRow["due_date"].ToString());
}
}
}
lstFee.Add(fee);
}
return(lstFee);
}
else
{
return(null);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:Piyumi
/// CreatedDate:2016/1/13
///
/// Get user details of a given user
///
/// EditedBy: Kanishka
/// EditedDate:2016/2/26
///
/// Add user company code and branch code
///
/// </summary>
/// <param name="user_id"></param>
/// <returns>GetDetails Object</returns>
public GetDetails getUserById(int user_id)
{
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@user_id", user_id });
//Company company = new Company();
try
{
DataSet dsCompany = dataHandler.GetDataSet("spGetUserDetailsById", paramertList);
if (dsCompany != null && dsCompany.Tables.Count != 0)
{
GetDetails dtl = new GetDetails();
//company.CompanyId = int.Parse(dsCompany.Tables[0].Rows[0]["company_Id"].ToString());
//company.CompanyName = dsCompany.Tables[0].Rows[0]["company_name"].ToString();
//company.CompanyCode = dsCompany.Tables[0].Rows[0]["company_code"].ToString();
//company.CompanyAddress1 = dsCompany.Tables[0].Rows[0]["company_address_1"].ToString();
//company.CompanyAddress2 = dsCompany.Tables[0].Rows[0]["company_address_2"].ToString();
//company.StateId = int.Parse(dsCompany.Tables[0].Rows[0]["stateId"].ToString());
//company.City = dsCompany.Tables[0].Rows[0]["city"].ToString();
//company.Zip = dsCompany.Tables[0].Rows[0]["zip"].ToString();
//string[] zipWithExtention = company.Zip.Split('-');
//if (zipWithExtention[0] != null) company.ZipPre = zipWithExtention[0];
//if (zipWithExtention.Count() >= 2 && zipWithExtention[1] != null) company.Extension = zipWithExtention[1];
//company.Email = dsCompany.Tables[0].Rows[0]["email"].ToString();
//company.PhoneNum1 = dsCompany.Tables[0].Rows[0]["phone_num_1"].ToString();
//company.PhoneNum2 = dsCompany.Tables[0].Rows[0]["phone_num_2"].ToString();
//company.PhoneNum3 = dsCompany.Tables[0].Rows[0]["phone_num_3"].ToString();
//company.Fax = dsCompany.Tables[0].Rows[0]["fax"].ToString();
//company.WebsiteUrl = dsCompany.Tables[0].Rows[0]["website_url"].ToString();
//company.TypeId = int.Parse(dsCompany.Tables[0].Rows[0]["company_type"].ToString());
dtl.userId = int.Parse(dsCompany.Tables[0].Rows[0]["user_id"].ToString());
dtl.firstName = dsCompany.Tables[0].Rows[0]["first_name"].ToString();
dtl.lastName = dsCompany.Tables[0].Rows[0]["last_name"].ToString();
dtl.email = dsCompany.Tables[0].Rows[0]["email"].ToString();
dtl.phoneNo = dsCompany.Tables[0].Rows[0]["phone_no"].ToString();
if (bool.Parse(dsCompany.Tables[0].Rows[0]["status"].ToString()))
{
dtl.status = "Active";
}
else
{
dtl.status = "Not Active";
}
DateTime day = DateTime.Parse(dsCompany.Tables[0].Rows[0]["created_date"].ToString());
dtl.createdDate = day.ToShortDateString();
dtl.roleName = dsCompany.Tables[0].Rows[0]["role_name"].ToString();
if (dsCompany.Tables[0].Rows[0]["branch_name"].ToString() != "")
{
dtl.branchName = dsCompany.Tables[0].Rows[0]["branch_name"].ToString();
dtl.BranchId = Convert.ToInt32(dsCompany.Tables[0].Rows[0]["branch_id"]);
dtl.BranchCode = dsCompany.Tables[0].Rows[0]["BranchCode"].ToString();
}
else
{
dtl.branchName = "";
dtl.BranchId = 0;
dtl.BranchCode = "";
}
dtl.CompanyCode = dsCompany.Tables[0].Rows[0]["CompanyCode"].ToString();
return(dtl);
}
else
{
return(null);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:Piyumi
/// CreatedDate:2016/1/13
/// Retrieve user_name and created person of users
/// </summary>
/// <param name="userType"></param>
/// <returns>userLogin object</returns>
public List <UserLogin> getUserByType(int levelId, int userId)
{
List <UserLogin> UserList = new List <UserLogin>();
DataHandler dataHandler = new DataHandler();
int userRole = getUserRole(userId);
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@level_id", levelId });
paramertList.Add(new object[] { "@user_id", userId });
try
{
DataSet dataSet = dataHandler.GetDataSet("spGetUserLoginDetailsByType");
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
UserLogin user = new UserLogin();
user.loginId = userId;
user.userId = int.Parse(dataRow["user_id"].ToString());
user.userName = dataRow["user_name"].ToString();
user.createdBy = int.Parse(dataRow["created_by"].ToString());
user.createdByRole = getUserRole(user.createdBy);
user.createdName = getUserNameById(user.createdBy);
user.roleId = int.Parse(dataRow["role_id"].ToString());
if (userRole == 1)
{
user.isEdit = true;
}
else if ((userRole == 2) && (levelId == 1))
{
user.isEdit = false;
}
else if ((userRole == 2) && (levelId == 2))
{
user.isEdit = true;
}
else if ((userRole == 2) && (levelId == 3))
{
user.isEdit = true;
}
UserList.Add(user);
}
return(UserList);
}
else
{
return(null);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy: MAM. IRFAN
/// CreatedDate: 2016/02/05
///
/// Getting all non reistered company branches by Registered company id
///
///
/// UpdatedBy : nadeeka
/// UpdatedDate: 2016/03/04
/// removed existing connection open method and set parameter to object list and pass stored procedure name to
/// call DataHandler class method and getting dataset object,
/// create and return partner branch object list using that dataset
///
/// </summary>
/// <returns> a list contain all branches</returns>
///
public List <NonRegBranch> getNonRegBranches(int companyId)
{
List <NonRegBranch> branchesLists = new List <NonRegBranch>();
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@companyId", companyId });
try
{
DataSet dataSet = dataHandler.GetDataSet("spGetNonRegBranchesByCompanyId", paramertList);
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
NonRegBranch branch = new NonRegBranch();
branch.NonRegBranchId = int.Parse(dataRow["non_reg_branch_id"].ToString());
branch.BranchName = dataRow["branch_name"].ToString();
branch.BranchCode = dataRow["branch_code"].ToString();
branch.BranchId = int.Parse(dataRow["branch_id"].ToString());
branch.BranchAddress1 = dataRow["branch_address_1"].ToString();
branch.BranchAddress2 = dataRow["branch_address_2"].ToString();
branch.StateId = Convert.ToInt32(dataRow["state_id"].ToString());
branch.BranchCity = dataRow["city"].ToString();
branch.BranchZip = dataRow["zip"].ToString();
string[] zipWithExtention = branch.BranchZip.Split('-');
if (zipWithExtention[0] != null)
{
branch.ZipPre = zipWithExtention[0];
}
if (zipWithExtention.Count() >= 2 && zipWithExtention[1] != null)
{
branch.Extention = zipWithExtention[1];
}
else if (zipWithExtention.Count() == 1)
{
branch.Extention = "";
}
branch.NonRegCompanyName = dataRow["company_name"].ToString();
branch.CompanyNameBranchName = dataRow["company_name"].ToString() + " - " + dataRow["branch_name"].ToString();
branch.BranchEmail = dataRow["email"].ToString();
branch.BranchPhoneNum1 = dataRow["phone_num_1"].ToString();
branch.BranchPhoneNum2 = dataRow["phone_num_2"].ToString();
branch.BranchPhoneNum3 = dataRow["phone_num_3"].ToString();
branch.BranchFax = dataRow["fax"].ToString();
branch.BranchCompany = Convert.ToInt32(dataRow["company_id"]);
branchesLists.Add(branch);
}
return(branchesLists);
}
else
{
return(null);
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <Branch> GetLoansByBranches(int branchIdL)
{
List <Branch> branchesLists = new List <Branch>();
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@branch_id", branchIdL });
try
{
DataSet dataSet = dataHandler.GetDataSet("spGetLoanByBranchId", paramertList);
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
Branch branch = new Branch();
branch.BranchId = Convert.ToInt32(dataRow["branch_id"].ToString());
branch.BranchName = dataRow["branch_name"].ToString();
branch.LoanId = int.Parse(dataRow["loan_id"].ToString());
branch.LoanNumber = dataRow["loan_number"].ToString();
if (!string.IsNullOrEmpty(dataRow["is_title_tracked"].ToString()))
{
branch.IsTitleTrack = bool.Parse(dataRow["is_title_tracked"].ToString());
}
else
{
branch.IsTitleTrack = false;
}
if (!string.IsNullOrEmpty(dataRow["has_advance_fee"].ToString()) || !string.IsNullOrEmpty(dataRow["has_monthly_loan_fee"].ToString()) || !string.IsNullOrEmpty(dataRow["has_lot_inspection_fee"].ToString()))
{
branch.HasAdvanceFee = (dataRow["has_advance_fee"]) != DBNull.Value ? (bool)dataRow["has_advance_fee"] : false;
branch.HasMonthlyFee = (dataRow["has_monthly_loan_fee"]) != DBNull.Value ? (bool)dataRow["has_monthly_loan_fee"] : false;
branch.HasLotFee = (dataRow["has_lot_inspection_fee"]) != DBNull.Value ? (bool)dataRow["has_lot_inspection_fee"] : false;
if (branch.HasAdvanceFee || branch.HasMonthlyFee || branch.HasLotFee)
{
branch.HasFee = true;
}
else
{
branch.HasFee = false;
}
}
else
{
branch.HasFee = false;
}
branchesLists.Add(branch);
}
return(branchesLists);
}
else
{
return(null);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy: Piyumi
/// CreatedDate: 3/30/2016
/// get loan details
/// </summary>
/// <param name="id"></param>
/// <param name="role"></param>
/// <returns>Loan</returns>
public Loan GetLoanDetails(int id, int role)
{
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@para", id });
paramertList.Add(new object[] { "@role", role });
try
{
DataSet dataSet = dataHandler.GetDataSet("spGetLoan", paramertList);
if (dataSet != null && dataSet.Tables.Count != 0 && dataSet.Tables[0].Rows.Count != 0)
{
Loan loanObj = new Loan();
loanObj.LoanNumber = dataSet.Tables[0].Rows[0]["loan_number"].ToString();
//loanObj.PartnerName = dataSet.Tables[0].Rows[0]["company_name"].ToString()+" - " + dataSet.Tables[0].Rows[0]["non_reg_branch_name"].ToString(); comment by asanka
loanObj.PartnerName = dataSet.Tables[0].Rows[0]["non_reg_branch_name"].ToString();
loanObj.LoanCode = dataSet.Tables[0].Rows[0]["loan_code"].ToString();
loanObj.PartnerType = int.Parse(dataSet.Tables[0].Rows[0]["company_type"].ToString());
loanObj.BranchName = dataSet.Tables[0].Rows[0]["branch_name"].ToString();
if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["is_title_tracked"].ToString()))
{
if (bool.Parse(dataSet.Tables[0].Rows[0]["is_title_tracked"].ToString()))
{
loanObj.IsTitleTrack = 1;
}
else
{
loanObj.IsTitleTrack = 0;
}
}
else
{
loanObj.IsTitleTrack = 0;
}
if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["has_lot_inspection_fee"].ToString()))
{
if (bool.Parse(dataSet.Tables[0].Rows[0]["has_lot_inspection_fee"].ToString()))
{
loanObj.LotInspectionFee = 1;
}
else
{
loanObj.LotInspectionFee = 0;
}
}
else
{
loanObj.LotInspectionFee = 0;
}
if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["has_monthly_loan_fee"].ToString()))
{
if (bool.Parse(dataSet.Tables[0].Rows[0]["has_monthly_loan_fee"].ToString()))
{
loanObj.MonthlyLoanFee = 1;
}
else
{
loanObj.MonthlyLoanFee = 0;
}
}
else
{
loanObj.MonthlyLoanFee = 0;
}
if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["has_advance_fee"].ToString()))
{
if (bool.Parse(dataSet.Tables[0].Rows[0]["has_advance_fee"].ToString()))
{
loanObj.AdvanceFee = 1;
}
else
{
loanObj.AdvanceFee = 0;
}
if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["payment_due_method"].ToString()))
{
if (dataSet.Tables[0].Rows[0]["payment_due_method"].ToString().Contains("Vehicle Payoff"))
{
loanObj.AdvanceFeePayAtPayoff = true;
}
else
{
loanObj.AdvanceFeePayAtPayoff = false;
}
}
}
else
{
loanObj.AdvanceFee = 0;
}
if (role == 3)
{
if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["right_id"].ToString()))
{
loanObj.Rights = dataSet.Tables[0].Rows[0]["right_id"].ToString();
}
}
loanObj.NonRegBranchId = int.Parse(dataSet.Tables[0].Rows[0]["non_reg_branch_id"].ToString());
loanObj.LoanId = int.Parse(dataSet.Tables[0].Rows[0]["loan_id"].ToString());
return(loanObj);
}
return(null);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Frontend page: Title(loan setup)
/// Title: Get details of title related to given loanId
/// Designed: Piyumi
/// User story:
/// Date created: 2016/2/9
/// </summary>
/// <param name="loanId"></param>
/// <returns>TitleObject</returns>
public Title getTitleDetails(int loanId)
{
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
paramertList.Add(new object[] { "@loan_id", loanId });
try
{
DataSet dataSet = dataHandler.GetDataSet("spGetTitleDetailsByLoanId", paramertList);
Title obj1 = new Title();
//Check dataset is not null and table count >0
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
obj1.LoanId = int.Parse(dataRow["loan_id"].ToString());
obj1.IsTitleTrack = bool.Parse(dataRow["is_title_tracked"].ToString());
//Check whether title need to be tracked
if (obj1.IsTitleTrack)
{
obj1.ReceivedTimeLimit = dataRow["title_received_time_period"].ToString();
//Check auto remind period is null or empty
if (!string.IsNullOrEmpty(dataRow["auto_remind_period"].ToString()))
{
obj1.RemindPeriod = int.Parse(dataRow["auto_remind_period"].ToString());
}
//Check auto remind email is null or empty
if (!string.IsNullOrEmpty(dataRow["auto_remind_email"].ToString()))
{
obj1.RemindEmail = dataRow["auto_remind_email"].ToString();
}
else
{
//get auto remind email given in loan details page
LoanSetupAccess st = new LoanSetupAccess();
obj1.RemindEmail = st.getAutoRemindEmailByLoanId(obj1.LoanId);
}
}
else
{
//get auto remind email given in loan details page
LoanSetupAccess st = new LoanSetupAccess();
obj1.RemindEmail = st.getAutoRemindEmailByLoanId(obj1.LoanId);
}
obj1.IsReceipRequired = bool.Parse(dataRow["is_receipt_required"].ToString());
//Check whether receipts are required
if (obj1.IsReceipRequired)
{
obj1.ReceiptRequiredMethod = dataRow["receipt_required_method"].ToString();
}
//Check need scan copy value is null or empty
if (!string.IsNullOrEmpty(dataRow["need_scan_copy"].ToString()))
{
obj1.NeedScanCopy = bool.Parse(dataRow["need_scan_copy"].ToString());
//Check need scan copy value is true
if (obj1.NeedScanCopy)
{
obj1.TitleAcceptMethod = "Scanned Title Adequate";
}
}
}
return(obj1);
}
else
{
return(obj1);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// CreatedBy:Piyumi
/// CreatedDate: 03/16/2016
/// get titles list by identification number
/// </summary>
/// <param name="identificationNumber"></param>
/// <returns>resultList</returns>
public List <Unit> SearchTitle(string loanCode, string identificationNumber)
{
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
List <Unit> resultList = new List <Unit>();
if (!string.IsNullOrEmpty(identificationNumber))
{
paramertList.Add(new object[] { "@loan_code", loanCode });
paramertList.Add(new object[] { "@identification_number", identificationNumber });
try
{
DataSet dataSet = dataHandler.GetDataSet("spGetTitlesByIdentificationNumber", paramertList);
if (dataSet != null && dataSet.Tables.Count != 0)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
Unit title = new Unit();
if (!string.IsNullOrEmpty(dataRow["unit_status"].ToString()) && int.Parse(dataRow["unit_status"].ToString()) == 0)
{
title.AdvanceDateStr = "";
}
else
{
if (!string.IsNullOrEmpty(dataRow["advance_date"].ToString()))
{
title.AdvanceDateStr = Convert.ToDateTime(dataRow["advance_date"]).ToString("MM/dd/yyy");
}
}
//else
//{
// title.AdvanceDate = "";
//}
title.UnitId = dataRow["unit_id"].ToString();
title.IdentificationNumber = dataRow["identification_number"].ToString();
title.Year = int.Parse(dataRow["year"].ToString());
title.Make = dataRow["make"].ToString();
title.Model = dataRow["model"].ToString();
if (!string.IsNullOrEmpty(dataRow["title_status"].ToString()))
{
if (int.Parse(dataRow["title_status"].ToString()) == 0)
{
title.CurrentTitleStatus = "Not Received";
}
else if (int.Parse(dataRow["title_status"].ToString()) == 1)
{
title.CurrentTitleStatus = "Received";
}
else if (int.Parse(dataRow["title_status"].ToString()) == 2)
{
title.CurrentTitleStatus = "Returned to Dealer";
}
else if (int.Parse(dataRow["title_status"].ToString()) == 3)
{
title.CurrentTitleStatus = "Sent to Bank";
}
}
else
{
title.CurrentTitleStatus = "";
}
if (!string.IsNullOrEmpty(dataRow["unit_status"].ToString()))
{
title.UnitStatus = int.Parse(dataRow["unit_status"].ToString());
if (int.Parse(dataRow["unit_status"].ToString()) == 0)
{
title.CurrentUnitStatus = "InActive";
}
else if (int.Parse(dataRow["unit_status"].ToString()) == 1)
{
title.CurrentUnitStatus = "Active";
}
else if (int.Parse(dataRow["unit_status"].ToString()) == 2)
{
title.CurrentUnitStatus = "Paid";
}
}
else
{
title.CurrentUnitStatus = "";
}
resultList.Add(title);
}
return(resultList);
}
else
{
return(null);
}
}
catch (Exception ex)
{
throw ex;
}
}
return(null);
}
/*
*
* Frontend page: DashBoard Page
* Title: Get a inactive loan by loan code
* Designed: Irfan Mam
* User story: DFP-484
* Developed: Irfan MAM
* Date created: 7/3/2016
*
*/
public Loan GetAInActiveLoanDetailsbyLoanCode(string loancode, int role)
{
DataHandler dataHandler = new DataHandler();
List <object[]> paramertList = new List <object[]>();
// add a parameters to a list
paramertList.Add(new object[] { "@role", role });
paramertList.Add(new object[] { "@loan_code", loancode });
try
{
// excecute sp and return the datas
DataSet dataSet = dataHandler.GetDataSet("spGetAInActiveLoanByLoanCode", paramertList);
// if data exists bind it to loan object
if (dataSet != null && dataSet.Tables.Count != 0 && dataSet.Tables[0].Rows.Count != 0)
{
Loan loanObj = new Loan();
loanObj.LoanNumber = dataSet.Tables[0].Rows[0]["loan_number"].ToString();
loanObj.PartnerName = dataSet.Tables[0].Rows[0]["non_reg_branch_name"].ToString();
loanObj.LoanCode = dataSet.Tables[0].Rows[0]["loan_code"].ToString();
loanObj.PartnerType = int.Parse(dataSet.Tables[0].Rows[0]["company_type"].ToString());
loanObj.BranchName = dataSet.Tables[0].Rows[0]["branch_name"].ToString();
// if is_title_tracked has a value
if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["is_title_tracked"].ToString()))
{
// check that value
if (bool.Parse(dataSet.Tables[0].Rows[0]["is_title_tracked"].ToString()))
{
loanObj.IsTitleTrack = 1;
}
else
{
loanObj.IsTitleTrack = 0;
}
}
// if is_title_tracked has not a value
else
{
loanObj.IsTitleTrack = 0;
}
// if has_lot_inspection_fee has a value
if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["has_lot_inspection_fee"].ToString()))
{
if (bool.Parse(dataSet.Tables[0].Rows[0]["has_lot_inspection_fee"].ToString()))
{
loanObj.LotInspectionFee = 1;
}
else
{
loanObj.LotInspectionFee = 0;
}
}
// if has_lot_inspection_fee has not a value
else
{
loanObj.LotInspectionFee = 0;
}
// if has_monthly_loan_fee has a value
if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["has_monthly_loan_fee"].ToString()))
{
if (bool.Parse(dataSet.Tables[0].Rows[0]["has_monthly_loan_fee"].ToString()))
{
loanObj.MonthlyLoanFee = 1;
}
else
{
loanObj.MonthlyLoanFee = 0;
}
}
// if has_monthly_loan_fee not a value
else
{
loanObj.MonthlyLoanFee = 0;
}
// if has_advance_fee has a value
if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["has_advance_fee"].ToString()))
{
if (bool.Parse(dataSet.Tables[0].Rows[0]["has_advance_fee"].ToString()))
{
loanObj.AdvanceFee = 1;
}
else
{
loanObj.AdvanceFee = 0;
}
if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["payment_due_method"].ToString()))
{
if (dataSet.Tables[0].Rows[0]["payment_due_method"].ToString().Contains("Vehicle Payoff"))
{
loanObj.AdvanceFeePayAtPayoff = true;
}
else
{
loanObj.AdvanceFeePayAtPayoff = false;
}
}
}
// if has_advance_fee has not a value
else
{
loanObj.AdvanceFee = 0;
}
loanObj.StartDate = Convert.ToDateTime(dataSet.Tables[0].Rows[0]["start_date"].ToString());
loanObj.LoanAmount = Convert.ToDecimal(dataSet.Tables[0].Rows[0]["loan_amount"].ToString());
loanObj.MaturityDate = Convert.ToDateTime(dataSet.Tables[0].Rows[0]["maturity_date"].ToString());
loanObj.NonRegBranchId = int.Parse(dataSet.Tables[0].Rows[0]["non_reg_branch_id"].ToString());
loanObj.LoanId = int.Parse(dataSet.Tables[0].Rows[0]["loan_id"].ToString());
// return the loan object
return(loanObj);
}
// if there is no data return null
return(null);
}
catch (Exception ex)
{
throw ex;
}
}
