//save button sends mySQLupdate or mySQLinsert commands with validation and UTC
private void SaveButton_Click(object sender, EventArgs e)
{
int entryReady = 0;
bool existsAlready = false;
foreach (DataRow row in returnedIDTestTable.Rows)
{
if (AppIDTextBox != null && AppIDTextBox.Text != "" && row.ItemArray[0].ToString() == AppIDTextBox.Text)
{
existsAlready = true;
break;
}
}
if (TypeTextbox.Text.ToString() != "" && TypeTextbox.Text.Length > 3)
{
entryReady++;
}
else
{
MessageBox.Show("Please enter valid type. Must be 3 characters or more.");
}
if (dateTimePicker1.Value > DateTime.Now &&
dateTimePicker2.Value > DateTime.Now &&
dateTimePicker1.Value < dateTimePicker2.Value)
{
entryReady++;
}
else
{
MessageBox.Show("Dates must be in the future. And end date must be after start date.");
}
if (dateTimePicker1.Value.DayOfWeek != DayOfWeek.Saturday &&
dateTimePicker1.Value.DayOfWeek != DayOfWeek.Sunday &&
dateTimePicker2.Value.DayOfWeek != DayOfWeek.Saturday &&
dateTimePicker2.Value.DayOfWeek != DayOfWeek.Sunday &&
dateTimePicker1.Value.Hour >= 9 &&
dateTimePicker2.Value.Hour <= 17)
{
entryReady++;
}
else
{
MessageBox.Show("Appointment times must be within business hours.");
}
bool overlapAppts = false;
foreach (DataRow row in returnedIDTestTable.Rows)
{
if ((DateTime.Parse(row.ItemArray[9].ToString()).ToLocalTime() <= dateTimePicker1.Value &&
dateTimePicker1.Value <= DateTime.Parse(row.ItemArray[10].ToString()).ToLocalTime()) ||
(DateTime.Parse(row.ItemArray[9].ToString()).ToLocalTime() <= dateTimePicker2.Value &&
dateTimePicker2.Value <= DateTime.Parse(row.ItemArray[10].ToString()).ToLocalTime()) ||
(DateTime.Parse(row.ItemArray[9].ToString()).ToLocalTime() >= dateTimePicker1.Value &&
dateTimePicker2.Value >= DateTime.Parse(row.ItemArray[10].ToString()).ToLocalTime()))
{
overlapAppts = true;
break;
}
}
if (overlapAppts)
{
MessageBox.Show("Appointments cannot overlap.");
}
else
{
entryReady++;
}
if (existsAlready && entryReady == 4)
{
FormModel.UpdateNonQuery($"UPDATE appointment SET " +
$"customerID = '{comboboxSelectTable.Rows[comboBox1.SelectedIndex].ItemArray[1].ToString()}', " +
$"userID = '{FormModel.CurrentUserID.ToString()}', " +
$"type = '{TypeTextbox.Text}', " +
$"start = '{DateTime.Parse(dateTimePicker1.Value.ToString()).ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " +
$"end = '{DateTime.Parse(dateTimePicker2.Value.ToString()).ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " +
$"lastUpdate = '{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " +
$"lastupdateby = \"{FormModel.CurrentUser}\" " +
$"WHERE (appointmentID = '{AppIDTextBox.Text}');");
AppointmentSelectTableLoad(comboboxSelectTable.Rows[comboBox1.SelectedIndex].ItemArray[0].ToString());
}
else if (entryReady == 4)
{
FormModel.InsertNonQuery("insert into appointment " +
"(customerID, userID, type, start, end, createDate, createdBy) VALUES " +
$"('{comboboxSelectTable.Rows[comboBox1.SelectedIndex].ItemArray[1].ToString()}', " +
$"'{FormModel.CurrentUserID}', '{TypeTextbox.Text}', " +
$"'{DateTime.Parse(dateTimePicker1.Value.ToString()).ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " +
$"'{DateTime.Parse(dateTimePicker2.Value.ToString()).ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " +
$"now(), '{FormModel.CurrentUser}');");
AppointmentSelectTableLoad(comboboxSelectTable.Rows[comboBox1.SelectedIndex].ItemArray[0].ToString());
}
else
{
MessageBox.Show("Entry Failed");
}
comboBox1_SelectionChangeCommitted(new object(), new EventArgs());
returnedIDTestTableLoad();
combobox2SelectTable = FormModel.SearchQuery("select distinct type from appointment");
comboBox2.DataSource = combobox2SelectTable;
comboBox2.DisplayMember = "Type";
comboBox2.SelectedItem = null;
comboBox2.Text = "-type select-";
}
private void SaveButton_Click(object sender, EventArgs e)
{
DataTable tempTable = new DataTable {
};
string isfoundCustomerID = null;
string isfoundCountry = null;
string isfoundCity = null;
string isfoundAddressPhoneID = null;
DialogResult confirmResult;
int readyToEnter = 0;
if (nameTextBox.Text == "" || countryTextBox.Text == "" ||
address1TextBox.Text == "" || phoneTextBox.Text == "" ||
zipTextBox.Text == "" || cityTextBox.Text == "")
{
MessageBox.Show("Please be sure to fill all fields.");
}
else
{
readyToEnter++;
}
if (zipTextBox.Text.Length > 10)
{
MessageBox.Show("Zip Code is too long.");
}
else
{
readyToEnter++;
}
if (phoneTextBox.Text.Length > 20)
{
MessageBox.Show("Phone Number is too long.");
}
else
{
readyToEnter++;
}
if (readyToEnter == 3)
{
foreach (DataRow row in returnedSelectTable.Rows)
{
if (IDTextBox.Text == row.ItemArray[0].ToString())
{
isfoundCustomerID = row.ItemArray[0].ToString();
}
if (countryTextBox.Text == row.ItemArray[7].ToString())
{
isfoundCountry = row.ItemArray[11].ToString();
}
if (cityTextBox.Text == row.ItemArray[5].ToString())
{
isfoundCity = row.ItemArray[10].ToString();
}
if (isfoundCustomerID != null && address1TextBox.Text == row.ItemArray[5].ToString() &&
phoneTextBox.Text == row.ItemArray[2].ToString())
{
isfoundAddressPhoneID = row.ItemArray[9].ToString();
}
}
if (isfoundCustomerID != null)
{
confirmResult = MessageBox.Show("Are you sure to update this Customer?",
"Confirm update?", MessageBoxButtons.YesNo);
}
else
{
confirmResult = MessageBox.Show("Are you sure to create this Customer?",
"Confirm creation?", MessageBoxButtons.YesNo);
}
if (confirmResult == DialogResult.Yes)
{
if (isfoundCountry == null)
{
FormModel.InsertNonQuery("Insert into country " +
$"(country, createdate, createdby, lastupdate, lastupdateby) " +
$"values (\"{countryTextBox.Text}\", " +
$"'{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " +
$"\"{FormModel.CurrentUser}\", " +
$"'{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " +
$"\"{FormModel.CurrentUser}\");");
tempTable = FormModel.SearchQuery("select * from country;");
foreach (DataRow row in tempTable.Rows)
{
if (row.ItemArray[1].ToString() == countryTextBox.Text)
{
isfoundCountry = row.ItemArray[0].ToString();
break;
}
}
}
if (isfoundCity == null)
{
FormModel.InsertNonQuery("Insert into city " +
$"(city, countryID, createdate, createdby, lastupdate, lastupdateby) " +
$"values (\"{cityTextBox.Text}\", {isfoundCountry}, " +
$"'{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " +
$"\"{FormModel.CurrentUser}\", " +
$"'{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " +
$"\"{FormModel.CurrentUser}\");");
tempTable = FormModel.SearchQuery("select * from city;");
foreach (DataRow row in tempTable.Rows)
{
if (row.ItemArray[1].ToString() == cityTextBox.Text)
{
isfoundCity = row.ItemArray[0].ToString();
break;
}
}
}
if (isfoundAddressPhoneID == null)
{
FormModel.InsertNonQuery("Insert into address (address, " +
"cityID, postalCode, Phone, createdate, createdby, lastupdate, lastupdateby) " +
$"values (\"{address1TextBox.Text}\", {isfoundCity}, {zipTextBox.Text}, \"{phoneTextBox.Text}\", " +
$"'{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " +
$"\"{FormModel.CurrentUser}\", " +
$"'{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " +
$"\"{FormModel.CurrentUser}\");");
tempTable = FormModel.SearchQuery("select * from address;");
foreach (DataRow row in tempTable.Rows)
{
if (address1TextBox.Text == row.ItemArray[1].ToString() &&
phoneTextBox.Text == row.ItemArray[5].ToString() &&
DateTime.ParseExact(row.ItemArray[6].ToString(), "M/d/yyyy h:mm:ss tt",
System.Globalization.CultureInfo.InstalledUICulture).Hour == DateTime.UtcNow.Hour)
{
isfoundAddressPhoneID = row.ItemArray[0].ToString();
break;
}
}
}
if (isfoundCustomerID == null)
{
FormModel.InsertNonQuery("Insert into customer " +
"(customerName, addressID, active, createdate, createdby, lastupdate, lastupdateby) " +
$"values (\"{nameTextBox.Text}\", {isfoundAddressPhoneID}, {yesRadioButton.Checked}, " +
$"'{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " +
$"\"{FormModel.CurrentUser}\", " +
$"'{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " +
$"\"{FormModel.CurrentUser}\");");
}
else
{
FormModel.UpdateNonQuery("update customer " +
$"set customerName = \"{nameTextBox.Text}\", " +
$"addressID = {isfoundAddressPhoneID}, " +
$"active = {yesRadioButton.Checked}, " +
$"lastUpdate = '{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " +
$"lastupdateby = \"{FormModel.CurrentUser}\" " +
$"where customerID = {isfoundCustomerID};");
}
}
CustomerSelectTableLoad();
//called 2x in code wasnt enough to make a full method when inline lambda was faster
Action <string> indicator = customerName =>
{
foreach (DataGridViewRow rowSearch in customerDGV.Rows)
{
if (rowSearch.Cells[1].Value.ToString().Equals(customerName))
{
rowSearch.Selected = true;
CustIndx = rowSearch.Index;
break;
}
}
};
indicator(CustomerNamePassed);
}
}