// 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();
}
}
}