//Populates the combobox with the names from the sql server
private void comboBoxSelectTableLoad()
{
comboboxSelectTable = FormModel.SearchQuery("select distinct customerName" +
" Customer, customerID from customer");
comboBox1.DataSource = comboboxSelectTable;
comboBox1.DisplayMember = "Customer";
if (preCreatedForm == false)
{
comboBox1.SelectedItem = null;
comboBox1.Text = "-customer select-";
}
}
//deletes row if found
private void DeleteButton_Click(object sender, EventArgs e)
{
var confirmResult = MessageBox.Show("Are you sure to delete this Appointment?",
"Confirm Delete?", MessageBoxButtons.YesNo);
if (confirmResult == DialogResult.Yes)
{
if (AppIndx >= 0)
{
FormModel.DeleteNonQuery($"delete from appointment where appointmentID = '{AppointmentsDGV.Rows[(int)AppIndx].Cells[0].Value.ToString()}';");
AppointmentSelectTableLoad();
}
}
}
private void MainscreenSelectTableLoad(string custName)
{
returnedSelectTable = FormModel.DgvBuild("SELECT date_format(start, '%Y-%m-%d') Day," +
" date_format(start, '%H %i') Time, appointmentID, userName User," +
" customerName Customer FROM appointment a inner join customer c on" +
" a.customerID=c.customerID inner join user u on a.userID=u.userID where " +
$" c.customerName = \"{custName}\"",
CalenderDGV);
if (CalenderDGV.Rows.Count == 0)
{
pictureBox1.Visible = true;
CalenderDGV.Visible = false;
}
}
//sends select statements to populate DGV
private void CustomerSelectTableLoad()
{
returnedSelectTable = FormModel.DgvBuild("SELECT customerID ID, " + //0
"customerName Name, phone Phone, active Active, " + //123
"Address Address, city City, postalCode Zip, " + //456
"country Country, c.customerID, c.addressID, ci.cityID, " + //7 8 9 10
"co.countryID FROM customer c inner join address a on " + //11
"c.addressID=a.addressID inner join city ci on a.cityID=ci.cityID " +
"inner join country co on ci.countryID=co.countryID",
customerDGV);
customerDGV.Columns[8].Visible = false;
customerDGV.Columns[9].Visible = false;
customerDGV.Columns[10].Visible = false;
customerDGV.Columns[11].Visible = false;
}
private void MainscreenSelectTableLoad(DateTime startDate, DateTime endDate)
{
FormModel.DgvBuild("SELECT date_format(start, '%Y-%m-%d') Day," +
" date_format(start, '%H %i') Time, appointmentID, userName User," +
" customerName Customer FROM appointment a inner join customer c on" +
" a.customerID=c.customerID inner join user u on a.userID=u.userID where " +
$"a.start>=STR_TO_DATE('{startDate}', '%c/%e/%Y') and " +
$"a.end<= STR_TO_DATE('{endDate}', '%c/%e/%Y')",
CalenderDGV);
if (CalenderDGV.Rows.Count == 0)
{
pictureBox1.Visible = true;
CalenderDGV.Visible = false;
}
}
private void DeleteButton_Click(object sender, EventArgs e)
{
var confirmResult = MessageBox.Show("Are you sure to delete this Customer?",
"Confirm Delete!", MessageBoxButtons.YesNo);
if (confirmResult == DialogResult.Yes)
{
foreach (DataRow row in returnedSelectTable.Rows)
{
if (IDTextBox.Text == row.ItemArray[0].ToString())
{
row.Delete();
CustIndx = null;
FormModel.DeleteNonQuery($"delete from customer where customerID = '{IDTextBox.Text}'; Commit;");
}
}
}
}
//sends select statements and any arguements to populate DGV
private void AppointmentSelectTableLoad()
{
returnedSelectTable = FormModel.DgvBuild("SELECT appointmentID ID, " + // 0
"c.customerName Customer, title Title, location Location, " + // 123
"contact Contact, type Type, url URL, " + // 456
"date_format(start, '%Y-%m-%d') Day, " + // 7
"date_format(start, '%H %i') \"Start Time\", " + // 8
"date_format(end, '%H %i') \"End Time\", " + // 9
"description Description, a.customerID, date_format(end, '%Y-%m-%d') \"End Day\", " + // 10 11 12
"ad.phone FROM appointment a " + // 13
"INNER JOIN customer c ON a.customerID=c.customerID INNER JOIN " +
"address ad ON c.addressID=ad.addressID",
AppointmentsDGV);
AppointmentsDGV.Columns[0].Visible = false;
AppointmentsDGV.Columns[12].Visible = false;
AppointmentsDGV.Columns[11].Visible = false;
AppointmentsDGV.Columns[2].Visible = false;
AppointmentsDGV.Columns[3].Visible = false;
AppointmentsDGV.Columns[10].Visible = false;
AppointmentsDGV.Columns[4].Visible = false;
AppointmentsDGV.Columns[6].Visible = false;
returnedIDTestTableLoad();
}
//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 AppointmentSelectTableLoad(string typeName, int?emptyIndicator)
{
returnedSelectTable = FormModel.DgvBuild("SELECT appointmentID ID, " + // 0
"c.customerName Customer, title Title, location Location, " + // 123
"contact Contact, type Type, url URL, " + // 456
"date_format(start, '%Y-%m-%d %H %i') \"Start Time\", " + // 7
"date_format(end, '%Y-%m-%d %H %i') \"End Time\", " + // 8
"description Description, a.customerID, " + // 9 10
"ad.phone FROM appointment a " + // 11
"INNER JOIN customer c ON a.customerID=c.customerID INNER JOIN " +
"address ad ON c.addressID=ad.addressID where " +
$" a.type = \"{typeName}\"",
AppointmentsDGV);
foreach (DataRow row in returnedSelectTable.Rows)
{
DateTime dateTime = new DateTime {
};
dateTime = DateTime.ParseExact(row.ItemArray[7].ToString(), "yyyy-MM-dd HH mm",
System.Globalization.CultureInfo.InstalledUICulture).ToLocalTime();
row.ItemArray[7] = dateTime.ToString();
}
foreach (DataRow row in returnedSelectTable.Rows)
{
DateTime dateTime = new DateTime {
};
dateTime = DateTime.ParseExact(row.ItemArray[8].ToString(), "yyyy-MM-dd HH mm",
System.Globalization.CultureInfo.InstalledUICulture).ToLocalTime();
row.ItemArray[8] = dateTime.ToString();
}
foreach (DataGridViewRow row in AppointmentsDGV.Rows)
{
if (row.Index >= 0)
{
DateTime dateTime = new DateTime {
};
dateTime = DateTime.ParseExact(row.Cells[7].Value.ToString(), "yyyy-MM-dd HH mm",
System.Globalization.CultureInfo.InstalledUICulture).ToLocalTime();
row.Cells[7].Value = dateTime.ToString();
}
}
foreach (DataGridViewRow row in AppointmentsDGV.Rows)
{
if (row.Index >= 0)
{
DateTime dateTime = new DateTime {
};
dateTime = DateTime.ParseExact(row.Cells[8].Value.ToString(), "yyyy-MM-dd HH mm",
System.Globalization.CultureInfo.InstalledUICulture).ToLocalTime();
row.Cells[8].Value = dateTime.ToString();
}
}
AppointmentsDGV.Columns[0].Visible = false;
AppointmentsDGV.Columns[2].Visible = false;
AppointmentsDGV.Columns[3].Visible = false;
AppointmentsDGV.Columns[4].Visible = false;
AppointmentsDGV.Columns[6].Visible = false;
AppointmentsDGV.Columns[9].Visible = false;
AppointmentsDGV.Columns[10].Visible = false;
AppointmentsDGV.Columns[11].Visible = false;
if (AppointmentsDGV.Rows.Count == 0)
{
pictureBox1.Visible = true;
AppointmentsDGV.Visible = false;
}
}
private void returnedIDTestTableLoad()
{
returnedIDTestTable = FormModel.SearchQuery("select * from appointment");
}
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);
}
}
//Form Switch buttons
private void Loginbutton_Click(object sender, EventArgs e)
{
string userName = UsertextBox.Text.ToString();
string userPass = PasswordtextBox.Text.ToString();
List <string> lines = new List <string> {
" "
};
var userCreds = FormModel.SearchQuery("select * from user");
//called 2x in code wasnt enough to make a full method when inline lambda was faster
Action <List <String> > logging = writeToLog =>
{
foreach (string line in lines)
{
using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"loginlogs.txt", true))
{
file.WriteLine(line);
}
}
};
bool nameTrue = false;
bool passTrue = false;
foreach (DataRow row in userCreds.Rows)
{
if (userName == row.ItemArray[1].ToString())
{
nameTrue = true;
}
if (userPass == row.ItemArray[2].ToString())
{
passTrue = true;
}
if (nameTrue && passTrue)
{
FormModel.CurrentUserID = row.ItemArray[0].ToString();
FormModel.CurrentUser = row.ItemArray[1].ToString();
lines.Add($"UserID = \"{row.ItemArray[0].ToString()}\"");
lines.Add($"UserName = \"{row.ItemArray[1].ToString()}\"");
lines.Add($"Time = \"{DateTime.UtcNow}\"");
lines.Add($"Success = \"True\"");
lines.Add($"");
logging(lines);
var tempTable = FormModel.SearchQuery("select c.customerName, a.start, a.type from appointment a inner join customer c on a.customerID = c.customerID;");
foreach (DataRow nextAppt in tempTable.Rows)
{
if (DateTime.UtcNow.AddHours(2) >= DateTime.Parse(nextAppt.ItemArray[1].ToString()) &&
DateTime.UtcNow <= DateTime.Parse(nextAppt.ItemArray[1].ToString()))
{
MessageBox.Show($"Urgent! There is an upcoming appointment with " +
$"{nextAppt.ItemArray[0].ToString()} at " +
$"{nextAppt.ItemArray[1].ToString()} for " +
$"{nextAppt.ItemArray[2].ToString()}.");
}
}
this.Hide();
Mainscreen mainscreen = new Mainscreen();
mainscreen.Show();
break;
}
}
if (!(nameTrue && passTrue))
{
lines.Add($"UserName = \"{UsertextBox.Text}\"");
lines.Add($"UserPass = \"{PasswordtextBox.Text}\"");
lines.Add($"Time = \"{DateTime.UtcNow}\"");
lines.Add($"Success = \"Failed\"");
lines.Add($"");
MessageBox.Show($"{Properties.Resources.BadLogin}");
logging(lines);
}
}