public ActionResult GetBranchDetails(int branchid )
{
CommonDAL cd = new CommonDAL();
BranchDetailsViewModel objbranch = new BranchDetailsViewModel();
// MessageBox.Show(branchid);
objbranch = cd.GetBranchDetails(BranchCode[branchid-1]);
return Json(objbranch);
}
public ActionResult Index()
{
CommonDAL syncobj = new CommonDAL();
//syncobj.Sync();
BankCountryModel objbankcountrymodel = new BankCountryModel();
objbankcountrymodel.CityModel = new List<City>();
objbankcountrymodel.BranchModel = new List<BranchL>();
objbankcountrymodel.CityModel = GetAllCities();
return View(objbankcountrymodel);
}
public List<City> GetAllCities()
{
List<City> objcity = new List<City>();
CommonDAL cd = new CommonDAL();
List<string> listcity = cd.GetCityList();
int id = 1;
foreach (string v in listcity)
{
objcity.Add(new City { Id = id++, CityName = v });
}
return objcity;
}
public bool AddBranch(BranchDetails bd)
{
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString());
CommonDAL commonDALObj = new CommonDAL();
SqlCommand cmd = new SqlCommand("SELECT MAX(BranchCode) FROM Branch ", connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
if (!reader.IsDBNull(0))
{
bd.BranchCode = Convert.ToInt64(reader[0]) + 1;
}
else
bd.BranchCode = 1;
reader.Close();
cmd.CommandText = String.Format("SELECT MAX(Id) FROM UserRoles ");
reader = cmd.ExecuteReader();
reader.Read();
int id = Convert.ToInt16(reader[0]) + 1;
connection.Close();
SqlConnection cn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString());
string command = String.Format("INSERT INTO Branch VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')", bd.BranchCode, bd.BranchName, bd.CityName, bd.Address, bd.ContactNumber, bd.BankerName, bd.BranchLogInID,commonDALObj.GetHashedText(bd.BranchLogInPassword), bd.Email);
cn2.Open();
SqlCommand cmd2 = new SqlCommand(command,cn2);
int res = cmd2.ExecuteNonQuery();
SqlCommand cmd3 = new SqlCommand(String.Format("INSERT INTO UserRoles VALUES('{0}','{1}', '{2}', '{3}','{4}','{5}', 'A')", id, bd.BranchLogInID, commonDALObj.GetHashedText(bd.BranchLogInPassword), "Banker",DateTime.Now.ToString(),"0"), cn2);
cmd3.ExecuteNonQuery();
cn2.Close();
if (res == 0)
return false;
else
return true;
}
public ActionResult ApplyForLoan()
{
CommonDAL objCommonDal = new CommonDAL();
ViewBag.cityList = objCommonDal.GetCityList();
long customerID = (Session["User"] as UserRole).customerID;
CustomerDAL objCustomerDal = new CustomerDAL();
LoanRequestViewModel objLoanRequest = new LoanRequestViewModel();
objLoanRequest.customerID = customerID;
objLoanRequest.age = objCustomerDal.GetAgeByCustomerID(customerID);
objLoanRequest.salaryProof = new byte[1024];
objLoanRequest.addressProof = new byte[1024];
@ViewBag.loanTypeList = new List<SelectListItem>()
{
new SelectListItem(){Text = "Personal",Value="P"},
new SelectListItem(){Text = "Home",Value="H"},
new SelectListItem(){Text = "Vehicle",Value="V"},
};
return View(objLoanRequest);
}
public List<BranchL> GetAllBranch(int cityid)
{
List<BranchL> objbranch = new List<BranchL>();
BranchCode = new List<long>();
CommonDAL cd = new CommonDAL();
List<string> listcity = cd.GetCityList();
string city = listcity[cityid-1];
List<BranchMiniViewModel> bmvm = new List<BranchMiniViewModel>();
int id =1;
bmvm = cd.GetBranchesInCity(city);
foreach (BranchMiniViewModel b in bmvm)
{
objbranch.Add(new BranchL {Id = id++, BranchName = b.BranchName });
BranchCode.Add(b.BranchCode);
}
return objbranch;
}
public bool FinishReg(Customer c)
{
bool res;
CommonDAL commonDALObj = new CommonDAL();
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString()))
{
string cmdtxt = String.Format("UPDATE Customer SET UserID = '{0}', Password = '******', CommunicationAddress = '{2}', TransactionPassword = '******', PhotoIDProof = '{4}' WHERE CustomerID = {5}", c.UserID, commonDALObj.GetHashedText(c.Password), c.CommunicationAddress, commonDALObj.GetHashedText(c.TransactionPassword), c.PhotoIDProof, c.CustomerID);
SqlCommand command = new SqlCommand(cmdtxt, connection);
connection.Open();
int rowaff = command.ExecuteNonQuery();
// Call Read before accessing data.
if (rowaff == 0)
res = false;
else
res = true;
command.CommandText = String.Format("SELECT MAX(Id) FROM UserRoles ");
SqlDataReader rd = command.ExecuteReader();
rd.Read();
int id = Convert.ToInt16(rd[0]) + 1;
rd.Close();
command.CommandText = String.Format("INSERT INTO UserRoles VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', 'A') ", id, c.UserID, commonDALObj.GetHashedText(c.Password), "Customer", DateTime.Now.ToString(), "0");
command.ExecuteNonQuery();
}
return res;
}
public string ValidateAndSetPassword(long customerID, string oldPassword, string newPassword, string passwordType)
{
CommonDAL commonDALObj = new CommonDAL();
if (oldPassword.Equals(newPassword))
return "Old and New Passwords cannot be same !";
if(passwordType.Equals("userPassword"))
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString()))
{
SqlCommand command = new SqlCommand(String.Format("SELECT Password, UserID FROM Customer WHERE CustomerID = {0} ", customerID), connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
reader.Read();
var storedPassword = reader[0].ToString();
var userID = reader[1].ToString();
reader.Close();
if (!storedPassword.Equals(commonDALObj.GetHashedText(oldPassword)))
return "Old Password entered is not correct !";
// update password in Customer and User Profile tables
command.CommandText = String.Format("UPDATE Customer SET Password = '******' WHERE CustomerID = {1}", commonDALObj.GetHashedText(newPassword), customerID.ToString());
command.ExecuteNonQuery();
command.CommandText = String.Format("UPDATE UserRoles SET Password = '******' WHERE UserID = '{1}' ", commonDALObj.GetHashedText(newPassword), userID);
command.ExecuteNonQuery();
}
}
else
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString()))
{
SqlCommand command = new SqlCommand(String.Format("SELECT TransactionPassword FROM Customer WHERE CustomerID = {0} ", customerID), connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
reader.Read();
var storedPassword = reader[0].ToString();
reader.Close();
if (!storedPassword.Equals(commonDALObj.GetHashedText(oldPassword)))
return "Password entered is not correct !";
// update password in Customer and User Profile tables
command.CommandText = String.Format("UPDATE Customer SET TransactionPassword = '******' WHERE CustomerID = {1}", commonDALObj.GetHashedText(newPassword), customerID.ToString());
command.ExecuteNonQuery();
}
}
return "Success";
}
public ActionResult ApplyForLoan(LoanRequestViewModel model)
{
CommonDAL objCommonDal = new CommonDAL();
CustomerDAL objCustomerDal = new CustomerDAL();
ViewBag.cityList = objCommonDal.GetCityList();
@ViewBag.loanTypeList = new List<SelectListItem>()
{
new SelectListItem(){Text = "Personal",Value="P"},
new SelectListItem(){Text = "Home",Value="H"},
new SelectListItem(){Text = "Vehicle",Value="V"},
};
if(objCustomerDal.AddLoanAccountRequest(model) )
{
ModelState.AddModelError("", "You request for loan was successful. You may apply for yet another loan");
}
else
{
ModelState.AddModelError("", "Operation failed");
}
return View(model);
}
public JsonResult GetBranchesInCity(string cityName)
{
List<BranchMiniViewModel> bmvm = new List<BranchMiniViewModel>();
CommonDAL cd = new CommonDAL();
bmvm = cd.GetBranchesInCity(cityName);
//MessageBox.Show(bmvm[0].BranchCode.ToString()+" "+bmvm[0].BranchName);
SelectList obj = new SelectList(bmvm, "BranchCode", "BranchName");
return Json(obj);
}
public ActionResult CreateNewAccount(NewAccountRequestView model, HttpPostedFileBase Image)
{
CustomerDAL customerDALobject = new CustomerDAL();
// ModelState.Clear();
CommonDAL obj = new CommonDAL();
ViewBag.cityList = obj.GetCityList();
if (Image == null)
{
ViewBag.message = "Please upload image first ! ";
return View(model);
}
if (customerDALobject.AddNewAccountRequest(model))
{
ViewBag.message = "Request Submitted. Thank You !";
return View(model);
}
else
{
ViewBag.message = "Oops... Error in submitting request.";
return View(model);
}
}
public ActionResult CreateNewAccount()
{
string result = (new CommonDAL()).CheckValidation("Customer", this.Session);
if (result.Equals("LogIn"))
return RedirectToAction("Login", "CommonBiz");
else if (result.Equals("Unauthorised"))
return RedirectToAction("Unauthorised", "CommonBiz");
CommonDAL obj = new CommonDAL();
NewAccountRequestView model = new NewAccountRequestView();
var customerID = (Session["User"] as UserRole).customerID;
ViewBag.cityList = obj.GetCityList();
model.CustomerID = customerID;
return View(model);
}
public ActionResult Login(LoginCredentials model)
{
CommonDAL obj = new CommonDAL();
string role = obj.CheckRole(model.UserName, model.Password);
if (!ModelState.IsValid)
return View(model);
if (role.Equals("DoesNotExist"))
{
ModelState.AddModelError("", "The user name does not exist !");
return View(model);
//return RedirectToAction("Login");
}
else if (role.Equals("Locked"))
{
ModelState.AddModelError("", "Your account is currently locked. Please contact your banker to unlock !");
return View(model);
//return RedirectToAction("Login");
}
else if (role.Equals("Account has been locked. Contact your banker to unlock !"))
{
ModelState.AddModelError("", "Account has been locked. Contact your banker to unlock !");
return View(model);
//return RedirectToAction("Login");
}
else if (role.Contains("more attempts remaining"))
{
ModelState.AddModelError("", role);
return View(model);
//return RedirectToAction("Login");
}
else if (role.Contains("Invalid"))
{
ModelState.AddModelError("", "Incorrect Password !");
return View(model);
//return RedirectToAction("Login");
}
UserRole ur = new UserRole {userID = model.UserName, role = role};
if(role.Equals("Customer"))
ur.customerID = (new CustomerDAL()).GetCustomerIDbyUserID(model.UserName);
else if(role.Equals("Banker"))
ur.branchCode = (new BankerDAL()).GetBranchCodeByBankerID(model.UserName);
this.Session["User"] = ur;
if (role.Equals("Admin"))
{
return RedirectToAction("DashBoard", "Admin");
}
else if (role.Equals("Banker"))
{
return RedirectToAction("DashBoard", "Banker");
}
else
{
return RedirectToAction("DashBoard", "Customer");
}
}
public string CheckRole(string UserID, string Password)
{
string res;
CommonDAL commonDALObj = new CommonDAL();
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString());
string query = "SELECT Password, Role,FailCount,Status FROM UserRoles WHERE UserID = @userID ";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add(new SqlParameter("@userID", UserID));
connection.Open();
SqlDataReader reader = command.ExecuteReader();
int failCount;
if (!reader.HasRows)
{
res = String.Copy("DoesNotExist");
}
else
{ // account exists
reader.Read();
if (reader[3].ToString().Equals("L")) // Account is locked
{
res = String.Copy("Locked");
}
else
{
//Account is active
if (reader[0].ToString().Equals(commonDALObj.GetHashedText(Password)))
{
//correct password
res = String.Copy(reader[1].ToString());
//update last log in in UserRoles and set FailCount = 0
reader.Close();
command.CommandText = "UPDATE UserRoles SET FailCount = 0, LastLogInDate = '" + DateTime.Now.ToString() + "' WHERE UserID = '" + UserID + "'";
command.ExecuteNonQuery();
}
else
{
//Account is active but wrong password !
if (reader[1].ToString().Equals("Customer"))
{
failCount = Convert.ToInt16(reader[2]) + 1;
if (failCount == 3)
{
// lock customer account and report
reader.Close();
command.CommandText = "UPDATE UserRoles SET FailCount = 3, Status = 'L' WHERE UserID = '" + UserID + "'";
command.ExecuteNonQuery();
res = String.Copy("Account has been locked. Contact your banker to unlock !");
}
else
{
// update failcount in UserRoles and warn
reader.Close();
command.CommandText = "UPDATE UserRoles SET FailCount = " + failCount.ToString() + " WHERE UserID = '" + UserID + "'";
command.ExecuteNonQuery();
res = String.Copy("Incorrect Password ! " + (3 - failCount).ToString() + " more attempts remaining.");
}
}
else
{ // Admin or banker entered incorrect password
res = String.Copy("Invalid");
}
}
}
}
connection.Close();
return res;
}
public void Sync()
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString()))
{
string cmdtxt = "DELETE FROM UserRoles";
SqlCommand command = new SqlCommand(cmdtxt, connection);
connection.Open();
CommonDAL commonDALObj = new CommonDAL();
int rowaff = command.ExecuteNonQuery();
command.CommandText = String.Copy("INSERT INTO UserRoles VALUES('1','Adminnnn', '"+commonDALObj.GetHashedText("A1@nnnnn")+"','Admin','2015/2/2','0','A')");
rowaff = command.ExecuteNonQuery();
command.CommandText = String.Copy("INSERT INTO UserRoles VALUES('2','Banker11','" + commonDALObj.GetHashedText("B1@nnnnn") + "','Banker','2015/2/2','0','A')");
rowaff = command.ExecuteNonQuery();
command.CommandText = String.Copy("INSERT INTO UserRoles VALUES('3','Customer1','" + commonDALObj.GetHashedText("C1@nnnnn") + "','Customer','2015/2/2','0','A')");
rowaff = command.ExecuteNonQuery();
command.CommandText = String.Copy("INSERT INTO UserRoles VALUES('4','Banker22','" + commonDALObj.GetHashedText("B1@nnnnn") + "','Banker','2015/2/2','0','A')");
rowaff = command.ExecuteNonQuery();
command.CommandText = String.Copy("DELETE FROM BranchTransferRequest");
rowaff = command.ExecuteNonQuery();
command.CommandText = String.Copy("DELETE FROM ClosingRequest");
rowaff = command.ExecuteNonQuery();
command.CommandText = String.Copy("DELETE FROM LoanRequest");
rowaff = command.ExecuteNonQuery();
command.CommandText = String.Copy("DELETE FROM NewAccountRequest");
rowaff = command.ExecuteNonQuery();
command.CommandText = String.Copy("DELETE FROM Branch");
rowaff = command.ExecuteNonQuery();
command.CommandText = String.Copy("INSERT INTO Branch VALUES('1','Branch1','City1','Address1','11','Banker11','Banker11','"+commonDALObj.GetHashedText("B1@nnnnn")+"','[email protected]')");
rowaff = command.ExecuteNonQuery();
command.CommandText = String.Copy("INSERT INTO Branch VALUES('2','Branch2','City2','Address1','11','Banker22','Banker22','" + commonDALObj.GetHashedText("B1@nnnnn") + "','[email protected]')");
rowaff = command.ExecuteNonQuery();
command.CommandText = String.Copy("DELETE FROM Customer");
rowaff = command.ExecuteNonQuery();
command.CommandText = String.Copy("INSERT INTO Customer (CustomerID, CustomerName,DOB, UserID, Password, PermanentAddress, CommunicationAddress, ContactNumber, Email, TransactionPassword, Token) VALUES('1001','Customer','2015/12/12','Customer1','"+commonDALObj.GetHashedText("customer1") + "','PAddress','CAddress','11','[email protected]','"+commonDALObj.GetHashedText("tpassword")+"','IBI1234')");
rowaff = command.ExecuteNonQuery();
command.CommandText = String.Copy("DELETE FROM BranchTransferRequest");
rowaff = command.ExecuteNonQuery();
//insert into user profile too !!
}
}
