private void returnedIDTestTableLoad() { DateTime dateTime = new DateTime { }; returnedIDTestTable = FormModel.SearchQuery("select * from appointment"); }
//Populates the combobox with the names from the sql server private void comboBoxSelectTableLoad() { comboboxSelectTable = FormModel.SearchQuery("select distinct customerName" + " Customer from customer"); comboBox1.DataSource = comboboxSelectTable; comboBox1.DisplayMember = "Customer"; comboBox1.SelectedItem = null; comboBox1.Text = "-customer select-"; }
//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-"; } combobox2SelectTable = FormModel.SearchQuery("select distinct type from appointment"); comboBox2.DataSource = combobox2SelectTable; comboBox2.DisplayMember = "Type"; comboBox2.SelectedItem = null; comboBox2.Text = "-type select-"; }
//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 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); } }