public void MainForm_Load(bool week) { DateTime filter = week ? CalcDateFilter("week") : CalcDateFilter("month"); DataTable dtRecord = SqlUpdater.FirstCal(SqlUpdater.DateSQLFormat(filter), week); AppointmentCalendar.DataSource = dtRecord; }
private void ReportButton_Click(object sender, EventArgs e) { DataRowView drv = TypeCombo.SelectedItem as DataRowView; try { string item = TypeCombo.SelectedItem.ToString(); IDictionary <string, object> dictionary = SqlUpdater.ReportAppoint(item); janResult.Text = dictionary["Jan"].ToString(); febResult.Text = dictionary["Feb"].ToString(); marResult.Text = dictionary["Mar"].ToString(); aprResult.Text = dictionary["Apr"].ToString(); mayResult.Text = dictionary["May"].ToString(); junResult.Text = dictionary["Jun"].ToString(); julResult.Text = dictionary["Jul"].ToString(); augResult.Text = dictionary["Aug"].ToString(); sepResult.Text = dictionary["Sep"].ToString(); octResult.Text = dictionary["Oct"].ToString(); novResult.Text = dictionary["Nov"].ToString(); decResult.Text = dictionary["Dec"].ToString(); } catch (Exception ex) { Console.WriteLine(ex); } }
private void customerCreateButton_Click(object sender, EventArgs e) { string timestamp = SqlUpdater.createTimestamp(); string userName = SqlUpdater.getCurrentUserName(); if (string.IsNullOrEmpty(customerNameText.Text) || string.IsNullOrEmpty(customerPhoneText.Text) || string.IsNullOrEmpty(customerCityText.Text) || string.IsNullOrEmpty(customerCountryText.Text) || string.IsNullOrEmpty(customerZipText.Text) || string.IsNullOrEmpty(customerAddressText.Text) || (activeYes.Checked == false && activeNo.Checked == false)) { MessageBox.Show("Please complete all fields"); } else { int countryId = SqlUpdater.createRecord(timestamp, userName, "country", $"'{customerCountryText.Text}'"); int cityId = SqlUpdater.createRecord(timestamp, userName, "city", $"'{customerCityText.Text}', '{countryId}'"); int addressId = SqlUpdater.createRecord(timestamp, userName, "address", $"'{customerAddressText.Text}', '', '{cityId}', '{customerZipText.Text}', '{customerPhoneText.Text}'"); SqlUpdater.createRecord(timestamp, userName, "customer", $"'{ customerNameText.Text}', '{addressId}', '{(activeYes.Checked ? 1 : 0)}'"); Close(); } }
public static void writeUserLoginLog(int userId) { string path = "log.text"; string log = $"User with ID {userId} logged in at {SqlUpdater.createTimestamp()}" + Environment.NewLine; File.AppendAllText(path, log); }
private void searchButton_Click(object sender, EventArgs e) { int customerId = SqlUpdater.findCustomer(searchBar.Text); if (customerId != 0) { cForm = SqlUpdater.getCustomerDetails(customerId); customerNameBox.Text = cForm["customerName"]; customerPhoneBox.Text = cForm["phone"]; customerCityBox.Text = cForm["city"]; customerAddessBox.Text = cForm["address"]; customerZipBox.Text = cForm["zip"]; customerCountryBox.Text = cForm["country"]; if (cForm["active"] == "true") { activeYes.Checked = true; } else { activeNo.Checked = true; } } else { MessageBox.Show("Unable to find customer"); } }
private void searchButton_Click(object sender, EventArgs e) { int customerId = SqlUpdater.findCustomer(searchBar.Text); if (customerId != 0) { customerDetails = SqlUpdater.getCustomerDetails(customerId); nameLabel.Text = customerDetails["customerName"]; phoneLabel.Text = customerDetails["phone"]; addressLabel.Text = customerDetails["address"]; cityLabel.Text = customerDetails["city"]; zipLabel.Text = customerDetails["zip"]; countryLabel.Text = customerDetails["country"]; if (customerDetails["active"] == "true") { activeLabel.Text = "Active"; } else { activeLabel.Text = "Inactive"; } } else { MessageBox.Show("Unable to find customer"); } }
private void ReportButton_Click(object sender, EventArgs e) { DataRowView drv = comboBox1.SelectedItem as DataRowView; int id = Convert.ToInt32(comboBox1.SelectedValue); DataTable dtRecord = SqlUpdater.Schedule(id.ToString()); userReportView.DataSource = dtRecord; }
private void SearchButton_Click_1(object sender, EventArgs e) { string appointmentId = SearchBar.Text; form = SqlUpdater.GetAppointmentDetails(appointmentId); CustomerIdBox.Text = form["customerId"]; TypeBox.Text = form["type"]; StartTimeBox.Text = SqlUpdater.ConvertToTimezone(form["start"]); EndTimeBox.Text = (SqlUpdater.ConvertToTimezone(form["end"])); }
private void searchButton_Click(object sender, EventArgs e) { string appointmentId = searchBar.Text; aForm = SqlUpdater.getAppointmentDetails(appointmentId); customerIdBox.Text = aForm["customerId"]; typeBox.Text = aForm["type"]; startTimeBox.Value = DateTime.Parse(SqlUpdater.convertToTimezone(aForm["start"])); endTimeBox.Value = DateTime.Parse(SqlUpdater.convertToTimezone(aForm["end"])); }
private void searchButton_Click(object sender, EventArgs e) { string appointmentId = appointmentIdBox.Text; appointmentDetails = SqlUpdater.getAppointmentDetails(appointmentId); customerIdLabel.Text = appointmentDetails["customerId"]; typeLabel.Text = appointmentDetails["type"]; startLabel.Text = appointmentDetails["start"]; endLabel.Text = appointmentDetails["end"]; }
public static bool AppHasConflict(DateTime startTime, DateTime endTime) { foreach (var app in SqlUpdater.GetAppointments().Values) { if (startTime < DateTime.Parse(app["end"].ToString()) && DateTime.Parse(app["start"].ToString()) < endTime) { return(true); } } return(false); }
public static Array getReport() { List <AppReport> appReports = new List <AppReport>(); List <Hashtable> appTypes = new List <Hashtable>(); SortedList months = new SortedList(); months.Add(1, "Janurary"); months.Add(2, "Feburary"); months.Add(3, "March"); months.Add(4, "April"); months.Add(5, "May"); months.Add(6, "June"); months.Add(7, "July"); months.Add(8, "August"); months.Add(9, "September"); months.Add(10, "October"); months.Add(11, "November"); months.Add(12, "December"); foreach (var app in SqlUpdater.getAppointments().Values) { int appMonth = DateTime.Parse(app["start"].ToString()).Month; bool duplicate = false; foreach (AppReport r in appReports) { if (r.month == months[appMonth].ToString() && r.appType == app["type"].ToString()) { duplicate = true; } } if (!duplicate) { AppReport appReport = new AppReport(); appReport.month = months[appMonth].ToString(); appReport.appType = app["type"].ToString(); appReport.quantity = SqlUpdater.getAppointments().Where(i => i.Value["type"].ToString() == app["type"].ToString() && DateTime.Parse(i.Value["start"].ToString()).Month == appMonth).Count(); appReports.Add(appReport); } } var appointmentArray = from row in appReports select new { Month = row.month, Type = row.appType, Quantity = row.quantity }; return(appointmentArray.ToArray()); }
private void UpdateButton_Click_1(object sender, EventArgs e) { string timestamp = SqlUpdater.CreateTimestamp(); int userId = SqlUpdater.GetCurrentUserID(); string username = SqlUpdater.GetCurrentUserName(); int appointmentId = Convert.ToInt32(SearchBar.Text); int customerId = Convert.ToInt32(CustomerIdBox.Text); string type = TypeBox.Text; DateTime startTime = DateTime.Parse(StartTimeBox.Text).ToUniversalTime(); DateTime endTime = DateTime.Parse(EndTimeBox.Text).ToUniversalTime(); String st = DateTime.Parse(StartTimeBox.Text).ToUniversalTime().ToString("u"); String et = DateTime.Parse(EndTimeBox.Text).ToUniversalTime().ToString("u"); bool pass = Validator(); if (pass) { try { if (AppHasConflict(startTime, endTime)) { throw new AppointmentException(); } else { try { if (OutsideBusinessHours(startTime, endTime)) { throw new AppointmentException(); } else { SqlUpdater.UpdateAppt(customerId, userId, st, et, type, timestamp, username, appointmentId); mainFormObject.UpdateCalendar(); MessageBox.Show("Update Sucessfull."); Close(); } } catch (AppointmentException ex) { ex.BusinessHours(); } } } catch (AppointmentException ex) { ex.AppOverlap(); } } else { MessageBox.Show("Add Appointment Error."); } }
public static Array getReport() { Dictionary <int, Hashtable> userReport = SqlUpdater.getAppointments(); var appointmentArray = from row in userReport select new { UserName = row.Value["username"], Type = row.Value["type"], StartTime = SqlUpdater.convertToTimezone(row.Value["start"].ToString()), EndTime = SqlUpdater.convertToTimezone(row.Value["start"].ToString()), Customer = row.Value["customerName"] }; return(appointmentArray.ToArray()); }
private void loginButton_Click(object sender, EventArgs e) { if (FindUser(username.Text, password.Text) != 0) { this.Hide(); MainForm MainForm = new MainForm(); MainForm.loginForm = this; Logger.writeUserLoginLog(SqlUpdater.getCurrentUserID()); MainForm.Show(); } else { MessageBox.Show(errorMessage); password.Text = ""; } }
public bool updateCustomer(Dictionary <string, string> updatedForm) { MySqlConnection c = new MySqlConnection(SqlUpdater.conString); c.Open(); //Customer table string recUpdate = $"UPDATE customer" + $" SET customerName = '{updatedForm["customerName"]}', active = '{updatedForm["active"]}', lastUpdate = '{SqlUpdater.createTimestamp()}', lastUpdateBy = '{SqlUpdater.getCurrentUserName()}'" + $" WHERE customerName = '{cForm["customerName"]}'"; MySqlCommand cmd = new MySqlCommand(recUpdate, c); int customerUpdated = cmd.ExecuteNonQuery(); //Address table recUpdate = $"UPDATE address" + $" SET address = '{updatedForm["address"]}', postalCode = '{updatedForm["zip"]}', phone = '{updatedForm["phone"]}', lastUpdate = '{SqlUpdater.createTimestamp()}', lastUpdateBy = '{SqlUpdater.getCurrentUserName()}'" + $" WHERE address = '{cForm["address"]}'"; cmd = new MySqlCommand(recUpdate, c); int addressUpdated = cmd.ExecuteNonQuery(); //City Table recUpdate = $"UPDATE city" + $" SET city = '{updatedForm["city"]}', lastUpdate = '{SqlUpdater.createTimestamp()}', lastUpdateBy = '{SqlUpdater.getCurrentUserName()}'" + $" WHERE city = '{cForm["city"]}'"; cmd = new MySqlCommand(recUpdate, c); int cityUpdated = cmd.ExecuteNonQuery(); //Country Table recUpdate = $"UPDATE country" + $" SET country = '{updatedForm["country"]}', lastUpdate = '{SqlUpdater.createTimestamp()}', lastUpdateBy = '{SqlUpdater.getCurrentUserName()}'" + $" WHERE country = '{cForm["country"]}'"; cmd = new MySqlCommand(recUpdate, c); int countryUpdated = cmd.ExecuteNonQuery(); c.Close(); if (customerUpdated != 0 && addressUpdated != 0 && cityUpdated != 0 && countryUpdated != 0) { return(true); } else { return(false); } }
static public int FindUser(string userName, string password) { MySqlConnection C = new MySqlConnection(SqlUpdater.conString); C.Open(); MySqlCommand cmd = new MySqlCommand($"SELECT userId FROM user WHERE userName = '******' AND password = '******'", C); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.HasRows) { rdr.Read(); SqlUpdater.setCurrentUserID(Convert.ToInt32(rdr[0])); SqlUpdater.setCurrentUserName(userName); rdr.Close(); return(SqlUpdater.getCurrentUserID()); } return(0); }
private void LoginButton_Click(object sender, EventArgs e) { if (FindUser(Username.Text, Password.Text) != 0) { this.Hide(); MainForm MainForm = new MainForm { loginForm = this }; Logger.WriteUserLoginLog(SqlUpdater.GetCurrentUserName()); MainForm.Show(); } else { MessageBox.Show(errorMessage); Password.Text = ""; } }
private void SearchButton_Click_1(object sender, EventArgs e) { { int customerId = SqlUpdater.FindCustomer(SearchBar.Text); if (customerId != 0) { cForm = SqlUpdater.GetCustomerDetails(customerId); CustomerNameBox.Text = cForm["customerName"]; CustomerPhoneBox.Text = cForm["phone"]; CustomerCityBox.Text = cForm["city"]; CustomerAddressBox.Text = cForm["address"]; CustomerZipBox.Text = cForm["zip"]; CustomerCountryBox.Text = cForm["country"]; } else { MessageBox.Show("Unable to find customer"); } } }
public static DataTable getReport() { Dictionary <int, Hashtable> appointments = SqlUpdater.getAppointments(); DataTable dt = new DataTable(); dt.Clear(); dt.Columns.Add("customer"); dt.Columns.Add("appointments"); IEnumerable <string> customers = appointments.Select(i => i.Value["customerName"].ToString()).Distinct(); foreach (string customer in customers) { DataRow row = dt.NewRow(); row["customer"] = customer; row["appointments"] = appointments.Where(i => i.Value["customerName"].ToString() == customer.ToString()).Count().ToString(); dt.Rows.Add(row); } return(dt); }
private void AddButton_Click(object sender, EventArgs e) { string timestamp = SqlUpdater.CreateTimestamp(); int userId = SqlUpdater.GetCurrentUserID(); string username = SqlUpdater.GetCurrentUserName(); DateTime startTime = DateTime.Parse(StartTimeBox.Text).ToUniversalTime(); DateTime endTime = DateTime.Parse(EndTimeBox.Text).ToUniversalTime(); bool pass = Validator(); if (pass) { try { if (AppHasConflict(startTime, endTime)) { throw new AppointmentException(); } else { try { if (OutsideBusinessHours(startTime, endTime)) { throw new AppointmentException(); } else { SqlUpdater.CreateRecord(timestamp, username, "appointment", $"'{CustomerIDBox.Text}', '{DateTime.Parse(StartTimeBox.Text).ToUniversalTime():u}', '{DateTime.Parse(EndTimeBox.Text).ToUniversalTime():u}', '{TypeBox.Text}'", userId); mainFormObject.UpdateCalendar(); Close(); } } catch (AppointmentException ex) { ex.BusinessHours(); } } } catch (AppointmentException ex) { ex.AppOverlap(); } }
private void AllButton_Click(object sender, EventArgs e) { try { IDictionary <string, object> dictionary = SqlUpdater.ReportAll(); janResult.Text = dictionary["Jan"].ToString(); febResult.Text = dictionary["Feb"].ToString(); marResult.Text = dictionary["Mar"].ToString(); aprResult.Text = dictionary["Apr"].ToString(); mayResult.Text = dictionary["May"].ToString(); junResult.Text = dictionary["Jun"].ToString(); julResult.Text = dictionary["Jul"].ToString(); augResult.Text = dictionary["Aug"].ToString(); sepResult.Text = dictionary["Sep"].ToString(); octResult.Text = dictionary["Oct"].ToString(); novResult.Text = dictionary["Nov"].ToString(); decResult.Text = dictionary["Dec"].ToString(); } catch (Exception ex) { Console.WriteLine(ex); } }
private void addButton_Click(object sender, EventArgs e) { string timestamp = SqlUpdater.createTimestamp(); int userId = SqlUpdater.getCurrentUserID(); string username = SqlUpdater.getCurrentUserName(); DateTime startTime = startTimeBox.Value.ToUniversalTime(); DateTime endTime = endTimeBox.Value.ToUniversalTime(); try { if (appHasConflict(startTime, endTime)) { throw new appointmentException(); } else { try { if (outsideBusinessHours(startTime, endTime)) { throw new appointmentException(); } else { SqlUpdater.createRecord(timestamp, username, "appointment", $"'{customerIdBox.Text}', '{startTimeBox.Value.ToUniversalTime().ToString("u")}', '{endTimeBox.Value.ToUniversalTime().ToString("u")}', '{typeBox.Text}'", userId); mainFormObject.updateCalendar(); Close(); } } catch (appointmentException ex) { ex.businessHours(); } } } catch (appointmentException ex) { ex.appOverlap(); } }
public bool updateAppointment(Dictionary <string, string> updatedForm) { MySqlConnection c = new MySqlConnection(SqlUpdater.conString); c.Open(); string recUpdate = $"UPDATE appointment" + $" SET customerId = '{updatedForm["customerId"]}', start = '{updatedForm["start"]}', end = '{updatedForm["end"]}', type = '{updatedForm["type"]}', lastUpdate = '{SqlUpdater.createTimestamp()}', lastUpdateBy = '{SqlUpdater.getCurrentUserName()}'" + $" WHERE appointmentId = '{aForm["appointmentId"]}'"; MySqlCommand cmd = new MySqlCommand(recUpdate, c); int appointmentUpdated = cmd.ExecuteNonQuery(); c.Close(); if (appointmentUpdated != 0) { return(true); } else { return(false); } }
static public Array getCalendar(bool weekView) { MySqlConnection c = new MySqlConnection(SqlUpdater.conString); c.Open(); string query = $"SELECT customerId, type, start, end, appointmentId, userId FROM appointment WHERE userid = '{SqlUpdater.getCurrentUserID()}'"; MySqlCommand cmd = new MySqlCommand(query, c); MySqlDataReader rdr = cmd.ExecuteReader(); Dictionary <int, Hashtable> appointments = SqlUpdater.getAppointments();//new Dictionary<int, Hashtable>(); while (rdr.Read()) { Hashtable appointment = new Hashtable(); appointment.Add("customerId", rdr[0]); appointment.Add("type", rdr[1]); appointment.Add("start", rdr[2]); appointment.Add("end", rdr[3]); appointment.Add("userId", rdr[5]); appointment.Add(Convert.ToInt32(rdr[4]), appointment); } rdr.Close(); foreach (var app in appointments.Values) { query = $"SELECT userName FROM user WHERE userId = '{app["userId"]}'"; cmd = new MySqlCommand(query, c); rdr = cmd.ExecuteReader(); rdr.Read(); app.Add("userName", rdr[0]); rdr.Close(); } foreach (var app in appointments.Values) { query = $"SELECT customerName FROM customer WHERE customerId = '{app["customerId"]}'"; cmd = new MySqlCommand(query, c); rdr = cmd.ExecuteReader(); rdr.Read(); app.Add("customerName", rdr[0]); rdr.Close(); } Dictionary <int, Hashtable> parsedAppointments = new Dictionary <int, Hashtable>(); foreach (var app in appointments) { DateTime startTime = DateTime.Parse(app.Value["start"].ToString()); DateTime endTime = DateTime.Parse(app.Value["end"].ToString()); DateTime today = DateTime.UtcNow; if (weekView) { DateTime sunday = today.AddDays(-(int)today.DayOfWeek); DateTime saturday = today.AddDays(-(int)today.DayOfWeek + (int)DayOfWeek.Saturday); if (startTime >= sunday && endTime < saturday) { parsedAppointments.Add(app.Key, app.Value); } } else { DateTime firstDayOfMonth = new DateTime(today.Year, today.Month, 1); DateTime lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1); if (startTime >= firstDayOfMonth && endTime < lastDayOfMonth) { parsedAppointments.Add(app.Key, app.Value); } } } SqlUpdater.setAppointments(appointments); var appointmentArray = from row in parsedAppointments select new { ID = row.Key, Type = row.Value["type"], StartTime = SqlUpdater.convertToTimezone(row.Value["start"].ToString()), EndTime = SqlUpdater.convertToTimezone(row.Value["end"].ToString()), Customer = row.Value["customerName"] }; c.Close(); return(appointmentArray.ToArray()); }