private void btnLogIn_Click(object sender, EventArgs e)
{
try
{
// Validate
if (ValidateChildren(ValidationConstraints.Enabled))
{
// Connect to db and check username and password entered
con.Open();
cmd.Connection = con;
cmd.CommandText = "Select * FROM user Where userName = '******'";
MySqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
if (txtUsername.Text.ToUpper().Equals(dr["userName"].ToString().ToUpper()) && txtPassword.Text.Equals(dr["password"]))
{
MessageBox.Show($"{Properties.Resources.logInSuccess}", "Login", MessageBoxButtons.OK, MessageBoxIcon.Information);
currentUserID = Convert.ToInt32(dr["userID"]);
currentUser = dr["userName"].ToString();
// Open Scheduling screen
Scheduling scheduling = new Scheduling();
this.Hide();
scheduling.Show();
// Log successful login
using (StreamWriter w = File.AppendText("log.txt"))
{
LogEntry($"{currentUser.ToString()} Logged in successfully", w);
}
}
else // Throw exception
{
throw (new UserPasswordException(string.Format($"{Properties.Resources.loginError}")));
}
}
else
{
MessageBox.Show($"{Properties.Resources.loginError2}");
}
}
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message); // print error message
}
finally //Close dr and connection
{
if (dr != null)
{
dr.Close();
}
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
public AppointmentForm()
{
InitializeComponent();
dtpEnd.Value = dTPStart.Value;
this.btnDone.Click += (object sender, EventArgs e) =>
{
this.Hide();
Scheduling schedulingScreen = new Scheduling();
schedulingScreen.Show();
}; // Lambda expression to create Done button click event
}
public CustomerForm()
{
InitializeComponent();
this.btnClose.Click += (object sender, EventArgs e) =>
{
this.Hide();
Scheduling schedulingScreen = new Scheduling();
schedulingScreen.Show();
addressID = AddressID;
};// Lambda expression to create Close button click event
}
// Save appointment
private void btnSaveApt_Click(object sender, EventArgs e)
{
// Convert times for validations
string custName = cbCustName.Text.ToString();
lowerLimit = Convert.ToDateTime((dTPStart.Value.ToString("yyyy-MM-dd") + (" 09:00")));
upperLimit = Convert.ToDateTime((dTPStart.Value.ToString("yyyy-MM-dd") + (" 17:00")));
apptStartTime = Convert.ToDateTime(dTPStart.Value.ToString("yyyy-MM-dd") + dTPStartTime.Value.ToString(" HH:mm"));
apptEndTime = Convert.ToDateTime(dTPStart.Value.ToString("yyyy-MM-dd") + dTPEndTime.Value.ToString(" HH:mm"));
try
{
//Validate times
if ((BusinessHrsValidation()) && (FutureAppointmentValidation()) && (MeetingTimeValidation()))
{
using (con)
{
int custID;
UsrID = usrID;
// Insert Appointment
string sp = "sp_insertAppt";
using (MySqlCommand cmd2 = new MySqlCommand(sp, con))
{
apptStartTime = TimeZoneInfo.ConvertTimeToUtc(dTPStartTime.Value, TimeZoneInfo.Local);
apptEndTime = TimeZoneInfo.ConvertTimeToUtc(dTPEndTime.Value, TimeZoneInfo.Local);
string apptStart = dTPStart.Value.ToString("yyyy-MM-dd") + apptStartTime.ToString(" HH:mm");
string apptEnd = dTPStart.Value.ToString("yyyy-MM-dd") + apptEndTime.ToString(" HH:mm");
DateTime t1 = Convert.ToDateTime(apptStart);
DateTime t2 = Convert.ToDateTime(apptEnd);
string apptStartTimeTemp = dTPStartTime.Value.ToString(" HH:mm");
string apptEndTimeTemp = dTPEndTime.Value.ToString(" HH:mm");
con.Open();
// Get userID by name
MySqlCommand cmd4 = new MySqlCommand("sp_getUserIDbyName", con);
cmd4.CommandType = CommandType.StoredProcedure;
cmd4.Parameters.AddWithValue("@usrname", cbUsr.Text.ToString());
MySqlDataReader dr = cmd4.ExecuteReader();
if (dr.Read())
{
usrID = Convert.ToInt32(dr["userId"]);
}
dr.Close();
// Insert or update Appointment
start = Convert.ToDateTime(dTPStart.Value.ToString("yyyy-MM-dd") + dTPStartTime.Value.ToString(" HH:mm"));
end = Convert.ToDateTime(dTPStart.Value.ToString("yyyy-MM-dd") + dTPEndTime.Value.ToString(" HH:mm"));
MySqlCommand cmd3 = new MySqlCommand("sp_getUsrApptTimes", con);
cmd3.CommandType = CommandType.StoredProcedure;
cmd3.Parameters.AddWithValue("@usrID", usrID);
dr = cmd3.ExecuteReader();
while (dr.Read())
{
ChangeAID();
int AID = Convert.ToInt32(dr["AID"]);
if ((Scheduling.GetCorrectedDate(Convert.ToDateTime(dr["Start"])) <= start) &&
(start <= Scheduling.GetCorrectedDate(Convert.ToDateTime(dr["End"]))))
{
if (AID != Convert.ToInt32(lblApptID.Text))
{
string mtgStart = Scheduling.GetCorrectedDate(Convert.ToDateTime(dr["Start"])).ToString("hh:mm tt");
string mtgEnd = Scheduling.GetCorrectedDate(Convert.ToDateTime(dr["End"])).ToString("hh:mm tt");
string mtgDay = Scheduling.GetCorrectedDate(Convert.ToDateTime(dr["Start"])).ToString("MMMM dd, yyyy");
char.ToUpper(cbUsr.Text[0]);
MessageBox.Show($"Consultant {cbUsr.Text} already has a meeting from {mtgStart} to {mtgEnd} on {mtgDay}!");
goto done;
}
}
}
dr.Close();
//Get customerID by name
MySqlCommand cmd = new MySqlCommand("sp_getIDByName", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@custName", custName.ToString());
dr = cmd.ExecuteReader();
if (dr.Read())
{
{
custID = Convert.ToInt32(dr["customerId"].ToString());
cmd2.Connection = con;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.AddWithValue("@apptID", lblApptID.Text.ToString());
cmd2.Parameters.AddWithValue("@custID", custID);
cmd2.Parameters.AddWithValue("@usrId", usrID);
cmd2.Parameters.AddWithValue("@typeof", cbType.Text.ToString());
cmd2.Parameters.AddWithValue("@startDate", t1.ToString("yyyy-MM-dd HH:mm"));//dTPStart.Value.ToString("yyyy-MM-dd"));
cmd2.Parameters.AddWithValue("@endDate", t2.ToString("yyyy-MM-dd HH:mm"));
DateTime lastUpdatedtimeUTC = DateTime.UtcNow;
DateTime lastUpdatedtime;
lastUpdatedtime = DateTime.SpecifyKind(lastUpdatedtimeUTC, DateTimeKind.Utc).ToLocalTime();
cmd2.Parameters.AddWithValue("@lastUpdated", lastUpdatedtimeUTC.ToString("yyyy-MM-dd HH:mm"));
dr.Close();
cmd2.ExecuteNonQuery();
MessageBox.Show("Appointment Saved!");
}
}
done :;
con.Close();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);// print error message
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
this.Hide();
Scheduling schedulingScreen = new Scheduling();
schedulingScreen.Show();
}
// View/Edit customer record
private void btnCustViewEdit_Click(object sender, EventArgs e)
{
try
{
if (indexOfSelectedCustomer >= 0)
{
int currentCustomerID = (int)dgvCustomer.Rows[indexOfSelectedCustomer].Cells[0].Value;
this.Hide();
CustomerForm viewCustomer = new CustomerForm();
if (currentCustomerID > 0)
{
con.Open();
MySqlCommand cmd4 = new MySqlCommand("sp_distinctZip");
cmd4.Connection = con;
MySqlDataReader dr = cmd4.ExecuteReader();
while (dr.Read())
{
viewCustomer.cbCustZip.Items.Add(dr["postalCode"].ToString());
}
dr.Close();
cmd.Connection = con;
cmd.CommandText = "sp_viewEditCustomerByID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@custID", currentCustomerID);
dr = cmd.ExecuteReader();
if (dr.Read())
{
viewCustomer.lblCustID.Text = dr["customerId"].ToString();
viewCustomer.tbCustName.Text = dr["customerName"].ToString();
viewCustomer.tbCustPhone.Text = dr["phone"].ToString();
viewCustomer.tbCustAdd.Text = dr["address"].ToString();
viewCustomer.tbCustCity.Text = dr["city"].ToString();
viewCustomer.tbCustCountry.Text = dr["country"].ToString();
viewCustomer.cbCustZip.Text = dr["postalCode"].ToString();
CustomerForm.AddressID = Convert.ToInt32(dr["addressId"]);
viewCustomer.lblLastUpdated.Text = Scheduling.GetCorrectedDate(Convert.ToDateTime(dr["lastUpdate"])).ToString();
}
con.Close();
}
viewCustomer.gbCustomer.Text = "Edit Customer";
viewCustomer.ShowDialog();
}
else
{
MessageBox.Show("Please select and Appointment to Edit the Customer!");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);// print error message
}
finally
{
if (dr != null)
{
dr.Close();
}
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
// Save Customer and address
private void btnSaveCustomer_Click(object sender, EventArgs e)
{
custID = Convert.ToInt32(lblCustID.Text);
try
{
using (con)
{
con.Open();
MySqlCommand cmd_GetCityID = new MySqlCommand("sp_getCityID", con);
MySqlCommand cmd_GetAddressID = new MySqlCommand("sp_getAddressID", con);
// Get City ID with zip
cmd_GetCityID.CommandType = CommandType.StoredProcedure;
cmd_GetCityID.Parameters.AddWithValue("@zip", cbCustZip.Text);
MySqlDataReader dr = cmd_GetCityID.ExecuteReader();
if (dr.Read())
{
cityID = Convert.ToInt32(dr["cityId"]);
}
dr.Close();
// Insert or update address Info
MySqlCommand cmd_addUpdateAddress = new MySqlCommand("sp_addUpdateAddress", con);
cmd_addUpdateAddress.CommandType = CommandType.StoredProcedure;
cmd_addUpdateAddress.Parameters.AddWithValue("@address_ID", addressID);
cmd_addUpdateAddress.Parameters.AddWithValue("@address1", tbCustAdd.Text);
cmd_addUpdateAddress.Parameters.AddWithValue("@city_ID", cityID);
cmd_addUpdateAddress.Parameters.AddWithValue("@zip", cbCustZip.Text);
cmd_addUpdateAddress.Parameters.AddWithValue("@phoneNumber", tbCustPhone.Text);
cmd_addUpdateAddress.ExecuteNonQuery();
// Get addressID with cityID and zip
cmd_GetAddressID.CommandType = CommandType.StoredProcedure;
cmd_GetAddressID.Parameters.AddWithValue("@city_ID", cityID);
cmd_GetAddressID.Parameters.AddWithValue("@zip", cbCustZip.Text);
cmd_GetAddressID.Parameters.AddWithValue("@custAddress", tbCustAdd.Text);
cmd_GetAddressID.Parameters.AddWithValue("@custPhone", tbCustPhone.Text);
dr = cmd_GetAddressID.ExecuteReader();
if (dr.Read())
{
addressID = Convert.ToInt32(dr["addressId"]);
}
dr.Close();
// Insert or update customr Info
MySqlCommand cmd_addUpdateCustomer = new MySqlCommand("sp_addUpdateCustomer", con);
cmd_addUpdateCustomer.Parameters.Clear();
cmd_addUpdateCustomer.CommandType = CommandType.StoredProcedure;
cmd_addUpdateCustomer.Parameters.AddWithValue("@custID", custID);
cmd_addUpdateCustomer.Parameters.AddWithValue("@custName", tbCustName.Text);
cmd_addUpdateCustomer.Parameters.AddWithValue("@addressID", addressID);
cmd_addUpdateCustomer.ExecuteNonQuery();
MessageBox.Show("Customer Added/Updated");// Feedback
}
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);// print error message
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
this.Hide();
Scheduling schedulingScreen = new Scheduling();
schedulingScreen.Show();
}
