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 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 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 !!
}
}
