protected void btnLogin_Click(object sender, EventArgs e)
{
//If either entry is blank, stop checks
if (txtUsername.Text == "" || txtPassword.Text == "")
{
Session["loginErrorMessage"] = "Please enter your username and password.";
lblError.Text = Session["loginErrorMessage"].ToString();
}
else
{
//If entered, get name and password for querying db
string userName = GlobalFunctions.escapeSqlString(txtUsername.Text);
string userHash = GlobalFunctions.CalculateSHAHash(txtPassword.Text);
//Get the customers assosciated with this dealer. status=1 requires it to be an active account.
sdsLogin.SelectCommand = "SELECT login, password, user_type, user_group, reference_id, user_id FROM users WHERE login='******' AND password='******' AND status=1";
//assign the table names to the dataview object
DataView dvUsers = (DataView)sdsLogin.Select(System.Web.UI.DataSourceSelectArguments.Empty);
//If nothing was found, let them know there was an error
if (dvUsers.Count == 0)
{
Session["loginErrorMessage"] = "Username or password invalid.";
lblError.Text = Session["loginErrorMessage"].ToString();
}
else
{
Session["loginErrorMessage"] = "";
//Sunspace
if (dvUsers[0][2].ToString() == "S")
{
//-1 is not a valid dealer ID, so on later checks, if -1, the user will need to spoof, which changes this
Session.Add("dealer_id", "-1");
Session.Add("user_id", dvUsers[0][5].ToString());
Session.Add("user_type", dvUsers[0][2].ToString());
Session.Add("user_group", dvUsers[0][3].ToString());
Session.Add("loggedIn", dvUsers[0][0].ToString());
}
//If dealer
else if (dvUsers[0][2].ToString() == "D")
{
Session.Add("dealer_id", dvUsers[0][4].ToString());
Session.Add("user_id", dvUsers[0][5].ToString());
Session.Add("user_type", dvUsers[0][2].ToString());
Session.Add("user_group", dvUsers[0][3].ToString());
Session.Add("loggedIn", dvUsers[0][0].ToString());
}
//Login means we need to update the last_access date
//get current date right now
DateTime aDate = DateTime.Now;
sdsLogin.UpdateCommand = "UPDATE users SET last_access='" + aDate.ToString("yyyy/MM/dd") + "' "
+ "WHERE login='******'";
sdsLogin.Update();
//Finally, we check what kind of user they are. Send sunspace users to spoof page by default, otherwise to home
//if (dvUsers[0][2].ToString() == "S")
//{
// Session["dealer_id"] = 1; //changeme to sunspace internal dealer default
//}
Response.Redirect("Home.aspx");
}
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
//If any of the textboxes required for all users are empty stop immediately
if (txtLogin.Text == "" ||
txtPassword.Text == "" ||
txtEmail.Text == "" ||
txtFirstName.Text == "" ||
txtLastName.Text == "")
{
lblError.Text = "Please enter data into all fields.";
}
else
{
#region Dealer Sales Rep
//adding a dealer sales rep
//Need to check hidden for usergroup as the ddl is built/cleared client side on change of ddlusertype
if (ddlUserType.SelectedValue == "Dealer" && hidUserGroup.Value == "Sales Rep")
{
DateTime aDate = DateTime.Now;
sdsUsers.InsertCommand = "INSERT INTO users (login, password, email_address, enrol_date, last_access, user_type, user_group, reference_id, first_name, last_name, status)"
+ "VALUES('"
+ GlobalFunctions.escapeSqlString(txtLogin.Text) + "', '"
+ GlobalFunctions.CalculateSHAHash(txtPassword.Text) + "', '"
+ GlobalFunctions.escapeSqlString(txtEmail.Text) + "', '"
+ aDate.ToString("yyyy/MM/dd") + "', '"
+ aDate.ToString("yyyy/MM/dd") + "', '" //default to same-day
+ "D" + "', '" //Must be D-S because a dealer can only add users of his dealership
+ "S" + "', "
+ Convert.ToInt32(Session["dealer_id"].ToString()) + ", '" //reference ID is the dealer id in the dealer table they belong to
+ GlobalFunctions.escapeSqlString(txtFirstName.Text) + "', '"
+ GlobalFunctions.escapeSqlString(txtLastName.Text) + "', "
+ 1 + ")";
sdsUsers.Insert();
lblError.Text = "Successfully Added";
}
#endregion
#region Dealer Admin
//adding a head dealer
else if (ddlUserType.SelectedValue == "Dealer" && hidUserGroup.Value == "Admin")
{
//Requires additional checks if adding a dealer
if (txtDealershipName.Text == "" ||
txtMultiplier.Text == "")
{
lblError.Text = "Please enter data into all fields.";
}
else
{
//open SQL connection for use with transaction
using (SqlConnection aConnection = new SqlConnection(sdsUsers.ConnectionString))
{
//Open connection, then create a command and a transaction that are linked to it
aConnection.Open();
SqlCommand aCommand = aConnection.CreateCommand();
SqlTransaction aTransaction;
// Start a local transaction.
aTransaction = aConnection.BeginTransaction("SampleTransaction");
// Must assign both transaction object and connection
// to Command object for a pending local transaction
aCommand.Connection = aConnection;
aCommand.Transaction = aTransaction;
try
{
//Add to dealer table
aCommand.CommandText = "INSERT INTO dealers (dealer_name, first_name, last_name, country, multiplier)"
+ "VALUES('"
+ GlobalFunctions.escapeSqlString(txtDealershipName.Text) + "', '"
+ GlobalFunctions.escapeSqlString(txtFirstName.Text) + "', '"
+ GlobalFunctions.escapeSqlString(txtLastName.Text) + "', '"
+ ddlCountry.SelectedValue + "', "
+ Convert.ToDecimal(txtMultiplier.Text) + ")"; //user enters %, so 80% will become 1.8 as a multiplier
aCommand.ExecuteNonQuery(); //Execute a command that does not return anything
aCommand.CommandText = "SELECT dealer_id FROM dealers WHERE dealer_name='" + txtDealershipName.Text + "'";
int newDealerId = Convert.ToInt32(aCommand.ExecuteScalar()); //ExecuteScalar returns the value in the first field of the first row of a query. Good for getting one piece of data immediately
//Now add user
DateTime aDate = DateTime.Now;
aCommand.CommandText = "INSERT INTO users (login, password, email_address, enrol_date, last_access, user_type, user_group, reference_id, first_name, last_name, status)"
+ "VALUES('"
+ txtLogin.Text + "', '"
+ GlobalFunctions.CalculateSHAHash(txtPassword.Text) + "', '"
+ GlobalFunctions.escapeSqlString(txtEmail.Text) + "', '"
+ aDate.ToString("yyyy/MM/dd") + "', '"
+ aDate.ToString("yyyy/MM/dd") + "', '" //default to same-day
+ "D" + "', '" //Must be D-A within this block of logic
+ "A" + "', "
+ newDealerId + ", '" //reference ID is the dealer id in the dealer table they belong to
+ GlobalFunctions.escapeSqlString(txtFirstName.Text) + "', '"
+ GlobalFunctions.escapeSqlString(txtLastName.Text) + "', "
+ 1 + ")";
aCommand.ExecuteNonQuery(); //Execute a command that does not return anything
//An entrance into the model preferences table, one entry for each model type
//These have hardcoded default values that any added dealer will have as their preferences.
//They can be edited here.
#region Model 100 preferences entry
aCommand.CommandText = "INSERT INTO model_preferences (dealer_id, model_type, default_filler, interior_panel_skin, exterior_panel_skin, frame_colour, door_type, door_style, door_swing, door_hinge, door_hardware, door_colour, door_glass_tint, door_vinyl_tint, door_screen_type, window_type, window_colour, window_glass_tint, window_vinyl_tint, window_screen_type, sunshade_valance_colour, sunshade_fabric_colour, sunshade_openness, roof_type, roof_interior_skin, roof_exterior_skin, roof_thickness, floor_thickness, floor_metal_barrier, kneewall_height, kneewall_type, kneewall_glass_tint, transom_height, transom_style, transom_glass_tint, transom_vinyl_tint, transom_screen_type, markup)"
+ "VALUES("
+ newDealerId + ", "
+ "'M100',"
+ "10,"
+ "'White Aluminum Stucco',"
+ "'White Aluminum Stucco',"
+ "'White',"
//door
+ "'Cabana',"
+ "'Full Screen',"
+ "'Out',"
+ "'R',"
+ "'Satin Silver',"
+ "'White',"
+ "'Clear',"
+ "'Clear',"
+ "'No Screen',"
//window
+ "'Fixed Vinyl',"
+ "'White',"
+ "'Clear',"
+ "'Clear',"
+ "'No Screen',"
//sunshade
+ "'White',"
+ "'Chalk',"
+ "'3%',"
//roof
+ "'Studio',"
+ "'White Aluminum Stucco',"
+ "'White Aluminum Stucco',"
+ "'3',"
//floor
+ "'4.5',"
+ "0,"
//kneewall
+ 20d + ","
+ "'Glass',"
+ "'Clear',"
//transom
+ 20d + ","
+ "'Glass',"
+ "'Clear',"
+ "'Clear',"
+ "'No Screen',"
+ 0.25d
+ ")";
aCommand.ExecuteNonQuery(); //Execute a command that does not return anything
#endregion
#region Model 200 preferences entry
aCommand.CommandText = "INSERT INTO model_preferences (dealer_id, model_type, default_filler, interior_panel_skin, exterior_panel_skin, frame_colour, door_type, door_style, door_swing, door_hinge, door_hardware, door_colour, door_glass_tint, door_vinyl_tint, door_screen_type, window_type, window_colour, window_glass_tint, window_vinyl_tint, window_screen_type, sunshade_valance_colour, sunshade_fabric_colour, sunshade_openness, roof_type, roof_interior_skin, roof_exterior_skin, roof_thickness, floor_thickness, floor_metal_barrier, kneewall_height, kneewall_type, kneewall_glass_tint, transom_height, transom_style, transom_glass_tint, transom_vinyl_tint, transom_screen_type, markup)"
+ "VALUES("
+ newDealerId + ", "
+ "'M200',"
+ "10,"
+ "'White Aluminum Stucco',"
+ "'White Aluminum Stucco',"
+ "'White',"
//door
+ "'Cabana',"
+ "'Full Screen',"
+ "'Out',"
+ "'R',"
+ "'Satin Silver',"
+ "'White',"
+ "'Clear',"
+ "'Clear',"
+ "'No Screen',"
//window
+ "'Vertical 4 Track',"
+ "'White',"
+ "'Clear',"
+ "'Clear',"
+ "'No Screen',"
//sunshade
+ "'White',"
+ "'Chalk',"
+ "'3%',"
//roof
+ "'Studio',"
+ "'White Aluminum Stucco',"
+ "'White Aluminum Stucco',"
+ "'3',"
//floor
+ "'4.5',"
+ "0,"
//kneewall
+ 20d + ","
+ "'Glass',"
+ "'Clear',"
//transom
+ 20d + ","
+ "'Glass',"
+ "'Clear',"
+ "'Clear',"
+ "'No Screen',"
+ 0.25d
+ ")";
aCommand.ExecuteNonQuery(); //Execute a command that does not return anything
#endregion
#region Model 300 preferences entry
aCommand.CommandText = "INSERT INTO model_preferences (dealer_id, model_type, default_filler, interior_panel_skin, exterior_panel_skin, frame_colour, door_type, door_style, door_swing, door_hinge, door_hardware, door_colour, door_glass_tint, door_vinyl_tint, door_screen_type, window_type, window_colour, window_glass_tint, window_vinyl_tint, window_screen_type, sunshade_valance_colour, sunshade_fabric_colour, sunshade_openness, roof_type, roof_interior_skin, roof_exterior_skin, roof_thickness, floor_thickness, floor_metal_barrier, kneewall_height, kneewall_type, kneewall_glass_tint, transom_height, transom_style, transom_glass_tint, transom_vinyl_tint, transom_screen_type, markup)"
+ "VALUES("
+ newDealerId + ", "
+ "'M300',"
+ "10,"
+ "'White Aluminum Stucco',"
+ "'White Aluminum Stucco',"
+ "'White',"
//door
+ "'Cabana',"
+ "'Full Screen',"
+ "'Out',"
+ "'R',"
+ "'Satin Silver',"
+ "'White',"
+ "'Clear',"
+ "'Clear',"
+ "'No Screen',"
//window
+ "'Horizontal Roller',"
+ "'White',"
+ "'Clear',"
+ "'Clear',"
+ "'No Screen',"
//sunshade
+ "'White',"
+ "'Chalk',"
+ "'3%',"
//roof
+ "'Studio',"
+ "'White Aluminum Stucco',"
+ "'White Aluminum Stucco',"
+ "'3',"
//floor
+ "'4.5',"
+ "0,"
//kneewall
+ 20d + ","
+ "'Glass',"
+ "'Clear',"
//transom
+ 20d + ","
+ "'Glass',"
+ "'Clear',"
+ "'Clear',"
+ "'No Screen',"
+ 0.25d
+ ")";
aCommand.ExecuteNonQuery(); //Execute a command that does not return anything
#endregion
#region Model 400 preferences entry
aCommand.CommandText = "INSERT INTO model_preferences (dealer_id, model_type, default_filler, interior_panel_skin, exterior_panel_skin, frame_colour, door_type, door_style, door_swing, door_hinge, door_hardware, door_colour, door_glass_tint, door_vinyl_tint, door_screen_type, window_type, window_colour, window_glass_tint, window_vinyl_tint, window_screen_type, sunshade_valance_colour, sunshade_fabric_colour, sunshade_openness, roof_type, roof_interior_skin, roof_exterior_skin, roof_thickness, floor_thickness, floor_metal_barrier, kneewall_height, kneewall_type, kneewall_glass_tint, transom_height, transom_style, transom_glass_tint, transom_vinyl_tint, transom_screen_type, markup)"
+ "VALUES("
+ newDealerId + ", "
+ "'M400',"
+ "10,"
+ "'White Aluminum Stucco',"
+ "'White Aluminum Stucco',"
+ "'White',"
//door
+ "'Cabana',"
+ "'Full Screen',"
+ "'Out',"
+ "'R',"
+ "'Satin Silver',"
+ "'White',"
+ "'Clear',"
+ "'Clear',"
+ "'No Screen',"
//window
+ "'Horizontal Roller',"
+ "'White',"
+ "'Clear',"
+ "'Clear',"
+ "'No Screen',"
//sunshade
+ "'White',"
+ "'Chalk',"
+ "'3%',"
//roof
+ "'Studio',"
+ "'White Aluminum Stucco',"
+ "'White Aluminum Stucco',"
+ "'3',"
//floor
+ "'4.5',"
+ "0,"
//kneewall
+ 20d + ","
+ "'Glass',"
+ "'Clear',"
//transom
+ 20d + ","
+ "'Glass',"
+ "'Clear',"
+ "'Clear',"
+ "'No Screen',"
+ 0.25d
+ ")";
aCommand.ExecuteNonQuery(); //Execute a command that does not return anything
#endregion
//Lastly, a preferences table entry, with defaults
aCommand.CommandText = "INSERT INTO preferences (dealer_id, installation_type, model_type, layout, cut_pitch)"
+ "VALUES("
+ newDealerId + ", "
+ "'House',"
+ "'M200',"
+ "'preset 1',"
+ "1"
+ ")";
aCommand.ExecuteNonQuery(); //Execute a command that does not return anything
lblError.Text = "Successfully Added";
// Attempt to commit the transaction.
aTransaction.Commit();
}
catch (Exception ex)
{
lblError.Text = "Commit Exception Type: " + ex.GetType();
lblError.Text += " Message: " + ex.Message;
// Attempt to roll back the transaction.
try
{
aTransaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
}
}
}
}
#endregion
#region Sunspace CSR
//Sunspace CSR
else if (ddlUserType.SelectedValue == "Sunspace" && ddlUserGroup.SelectedValue == "Customer Service Rep")
{
using (SqlConnection aConnection = new SqlConnection(sdsUsers.ConnectionString))
{
aConnection.Open();
SqlCommand aCommand = aConnection.CreateCommand();
SqlTransaction aTransaction;
// Start a local transaction.
aTransaction = aConnection.BeginTransaction("SampleTransaction");
// Must assign both transaction object and connection
// to Command object for a pending local transaction
aCommand.Connection = aConnection;
aCommand.Transaction = aTransaction;
try
{
//Add to dealer table
aCommand.CommandText = "INSERT INTO sunspace (position, first_name, last_name)"
+ "VALUES('"
+ "CSR" + "', '" //can only be CSR at this point, can be changed to a variable later
+ GlobalFunctions.escapeSqlString(txtFirstName.Text) + "', '"
+ GlobalFunctions.escapeSqlString(txtLastName.Text) + "'"
+ ")";
aCommand.ExecuteNonQuery(); //Execute a command that does not return anything
aCommand.CommandText = "SELECT sunspace_id FROM sunspace WHERE position='" + "CSR" + "' AND first_name='" + GlobalFunctions.escapeSqlString(txtFirstName.Text) + "' AND last_name='" + GlobalFunctions.escapeSqlString(txtLastName.Text) + "'";
int newSunspaceId = Convert.ToInt32(aCommand.ExecuteScalar()); //ExecuteScalar returns the value in the first field of the first row of a query. Good for getting one piece of data immediately
//Now add user
DateTime aDate = DateTime.Now;
aCommand.CommandText = "INSERT INTO users (login, password, email_address, enrol_date, last_access, user_type, user_group, reference_id, first_name, last_name, status)"
+ "VALUES('"
+ GlobalFunctions.escapeSqlString(txtLogin.Text) + "', '"
+ GlobalFunctions.CalculateSHAHash(txtPassword.Text) + "', '"
+ GlobalFunctions.escapeSqlString(txtEmail.Text) + "', '"
+ aDate.ToString("yyyy/MM/dd") + "', '"
+ aDate.ToString("yyyy/MM/dd") + "', '" //default to same-day
+ "S" + "', '" //Must be S-C within this block of logic
+ "C" + "', "
+ newSunspaceId + ", '" //reference ID is the dealer id in the dealer table they belong to
+ GlobalFunctions.escapeSqlString(txtFirstName.Text) + "', '"
+ GlobalFunctions.escapeSqlString(txtLastName.Text) + "', "
+ 1 + ")";
aCommand.ExecuteNonQuery(); //Execute a command that does not return anything
lblError.Text = "Successfully Added";
// Attempt to commit the transaction.
aTransaction.Commit();
}
catch (Exception ex)
{
lblError.Text = "Commit Exception Type: " + ex.GetType();
lblError.Text += " Message: " + ex.Message;
// Attempt to roll back the transaction.
try
{
aTransaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
}
}
}
#endregion
#region Sunspace Admin
//Sunspace Admin
else
{
//You currently may not add an admin in such a way. Such a decision should come from high up and be done directly through a database query.
}
#endregion
}
}