public string FormatAddressString(int addressId) { string addressString; using (var ctx = new DataLayer.ScheduleEntities()) { var addressJoin = (from a in dbcontext.addresses join c in dbcontext.cities on a.cityId equals c.cityId join cn in dbcontext.countries on c.countryId equals cn.countryId where a.addressId == addressId //orderby appt.start select new { a.address1, a.address2, postCode = a.postalCode, cityName = c.city1, countryName = cn.country1 }).ToList().First(); addressString = $"{addressJoin.address1} {addressJoin.address2} {addressJoin.cityName}, {addressJoin.countryName}"; } return(addressString); }
private void ValidateAppt(DataLayer.appointment appt) { using (var ctx = new DataLayer.ScheduleEntities()) { string CurrentUserName = dbcontext.users.Find(mainForm.CurrentUser).userName; // Lambda with LINQ query for concise and efficient queries bool overlapping = ctx.appointments .Where(a => a.createdBy == CurrentUserName && (a.start > appt.start && a.start < appt.end) || (a.end <appt.end && a.start> appt.start) ) .Any(); if (overlapping) { throw new WorkingTooHardException("Appointment overlaps with existing appointment"); } if ( appt.start.ToLocalTime().Hour < 9 || appt.start.ToLocalTime().Hour > 17 || appt.start.ToLocalTime().Day != appt.end.ToLocalTime().Day ) { throw new WorkingTooHardException("Appointments must be within business hours"); } if (appt.start.ToLocalTime() > appt.end.ToLocalTime()) { throw new TimeTravelImpossibleException("Appointment end cannot be before appointment start"); } } }
public void CheckForNotify() { using (var ctx = new DataLayer.ScheduleEntities()) { DateTime notifyStart = DateTime.Now.ToUniversalTime(); DateTime notifyEnd = DateTime.Now.ToUniversalTime().AddMinutes(15); string notificationMessage = string.Empty; var upcomingAppts = (from a in ctx.appointments join c in ctx.customers on a.customerId equals c.customerId where a.start >= notifyStart && a.start <= notifyEnd select new { a, c }).ToList(); if (upcomingAppts.Count() > 0) { // lambda function justitified effeciently produces formatted message output upcomingAppts.ForEach( appt => notificationMessage += $"{appt.a.start.ToLocalTime()}: " + $"{appt.a.title} - " + $"{appt.c.customerName}" + $"{Environment.NewLine}" ); MessageBox.Show("Meetings beginning in less than 15 minutes:" + Environment.NewLine + notificationMessage); } } }
private void RefreshConsultantSchedule() { textBoxConsultantSchedules.Clear(); using (var ctx = new DataLayer.ScheduleEntities()) { var appointments = from user in ctx.users from appointment in ctx.appointments orderby user.userName, appointment.start select new { user.userName, user.userId, apptUserId = appointment.userId, appointment.start, appointment.end, appointment.title }; foreach (var appt in appointments) { if (appt.apptUserId == appt.userId) { textBoxConsultantSchedules.AppendText($"{appt.userName}: {appt.start.ToLocalTime()} - {appt.end.ToLocalTime()} {appt.title}{Environment.NewLine}"); } } } }
private int createNewAddress() { using (var ctx = new DataLayer.ScheduleEntities()) { DataLayer.address newAddress = new DataLayer.address(); newAddress.address1 = textBoxAddress1.Text; newAddress.address2 = textBoxAddress2.Text; newAddress.cityId = comboBoxCity.SelectedIndex + 1; newAddress.createDate = DateTime.Now.ToUniversalTime(); newAddress.createdBy = CurrentUserName; newAddress.postalCode = textBoxPostCode.Text; newAddress.lastUpdate = DateTime.Now.ToUniversalTime(); newAddress.lastUpdateBy = CurrentUserName; newAddress.phone = textBoxPhone.Text; Validate(); // validate the input fields //addressBindingSource.EndEdit(); // try to save changes try { ctx.addresses.Add(newAddress); ctx.SaveChanges(); // write changes to database file } catch (DbEntityValidationException) { MessageBox.Show("Something has gone wrong saving this data", "Entity Validation Exception"); } return(newAddress.addressId); } }
private void createNewCustomer() { using (var ctx = new DataLayer.ScheduleEntities()) { DataLayer.customer newCust = new DataLayer.customer(); newCust.customerName = textBoxName.Text; newCust.active = checkBoxActive.Checked; newCust.addressId = createNewAddress(); newCust.lastUpdate = DateTime.Now.ToUniversalTime(); newCust.lastUpdateBy = CurrentUserName; newCust.createdBy = CurrentUserName; newCust.createDate = DateTime.Now.ToUniversalTime(); customerBindingSource.EndEdit(); // try to save changes try { ctx.customers.Add(newCust); ctx.SaveChanges(); // write changes to database file this.Close(); DialogResult = DialogResult.OK; } catch (DbEntityValidationException) { MessageBox.Show("Something has gone wrong saving this data", "Entity Validation Exception"); } catch (Exception e) { MessageBox.Show("Unable to update the db", e.Message); } } }
private void updateExistingCustomer(DataLayer.customer cust) { using (var ctx = new DataLayer.ScheduleEntities()) { var customerUpdate = (from cs in ctx.customers join a in ctx.addresses on cs.addressId equals a.addressId join c in ctx.cities on a.cityId equals c.cityId join cn in ctx.countries on c.countryId equals cn.countryId where cs.customerId == cust.customerId //orderby appt.start select new { cs, a, c, cn }).Single(); //`customerId`, `customerName`, `addressId`, `active`, `createDate`, `createdBy`, `lastUpdate`, `lastUpdateBy` customerUpdate.cs.customerName = textBoxName.Text; customerUpdate.a.address1 = textBoxAddress1.Text; customerUpdate.a.address2 = textBoxAddress2.Text; customerUpdate.a.postalCode = textBoxPostCode.Text; customerUpdate.a.cityId = comboBoxCity.SelectedIndex + 1; customerUpdate.cs.active = checkBoxActive.Checked; customerUpdate.a.phone = textBoxPhone.Text; customerUpdate.cs.lastUpdate = DateTime.Now.ToUniversalTime(); customerUpdate.cs.lastUpdateBy = CurrentUserName; if (customerUpdate.cs.createdBy is null) { customerUpdate.cs.createdBy = CurrentUserName; } Validate(); // validate the input fields //appointmentBindingSource.EndEdit(); // try to save changes try { ctx.SaveChanges(); // write changes to database file this.Close(); DialogResult = DialogResult.OK; } catch (DbEntityValidationException) { MessageBox.Show("Something has gone wrong saving this data", "Entity Validation Exception"); } } }
public MakeCustomer(MainScreen mainForm, int custId) : this(mainForm) { try { this.cust = dbcontext.customers .Where(c => c.customerId == custId) .FirstOrDefault(); } catch (NullReferenceException) { MessageBox.Show("An appointment to modify could not be loaded"); this.Hide(); } using (var ctx = new DataLayer.ScheduleEntities()) { var addressJoin = (from a in dbcontext.addresses join c in dbcontext.cities on a.cityId equals c.cityId join cn in dbcontext.countries on c.countryId equals cn.countryId where a.addressId == cust.addressId //orderby appt.start select new { a.addressId, a.address1, a.address2, a.phone, postCode = a.postalCode, cityIndex = c.cityId, countryIndex = cn.countryId }).ToList().First(); //`customerId`, `customerName`, `addressId`, `active`, `createDate`, `createdBy`, `lastUpdate`, `lastUpdateBy` textBoxName.Text = cust.customerName; textBoxAddress1.Text = addressJoin.address1; textBoxAddress2.Text = addressJoin.address2; textBoxPostCode.Text = addressJoin.postCode; comboBoxCity.SelectedIndex = addressJoin.cityIndex - 1; comboBoxCountry.SelectedIndex = addressJoin.countryIndex - 1; textBoxPhone.Text = addressJoin.phone; addr.addressId = addressJoin.addressId; checkBoxActive.Checked = cust.active; } }
private void refreshData() { // super reliable refresh... dbcontext.Dispose(); dbcontext = new DataLayer.ScheduleEntities(); int range = radioButtonWeekView.Checked ? 7 : 30; DateTime rangeStart = DateTime.Now.ToUniversalTime(); DateTime rangeEnd = DateTime.Now.ToUniversalTime().AddDays(range); using (var ctx = new DataLayer.ScheduleEntities()) { string currentUserName = GetUsernameByUserID(CurrentUser); var innerJoin = (from appt in dbcontext.appointments join cust in dbcontext.customers on appt.customerId equals cust.customerId where appt.end >= rangeStart && appt.start < rangeEnd && appt.createdBy == currentUserName orderby appt.start select new { appt.appointmentId, appt.start, appt.end, appt.customerId, appt.title, appt.location, cust.customerName }).ToList(); appointmentBindingSource.DataSource = innerJoin; } dbcontext.customers .OrderBy(cust => cust.customerName) .Load(); customerBindingSource.DataSource = dbcontext.customers.Local; // Show local and UTC time - useful for troubleshooting //labelTime.Text = $"Current Local Time: {DateTime.Now.ToLocalTime()}{Environment.NewLine}Current UTC Time: {DateTime.Now.ToUniversalTime()}"; RefreshReports(); }
private void RefreshMeetingsByType() { textBoxMeetingsByType.Clear(); string output = string.Empty; using (var ctx = new DataLayer.ScheduleEntities()) { for (int i = 1; i <= 12; i++) { // get a list of appts for this month var apptsForMonth = ctx.appointments .Where(a => a.start.Month == i) .Select(a => a.type).ToList(); // group the list by type and produce a count var grouped = apptsForMonth.GroupBy( type => type.ToString(), (outputs) => new { Key = outputs.ToString(), Count = outputs.Count() }); output += Environment.NewLine + CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(i) + Environment.NewLine; if (apptsForMonth.Count() == 0) { output += $"No meetings scheduled for this month.{Environment.NewLine}"; } foreach (var a in grouped) { output += $"{a.Key}: {a.Count()}{Environment.NewLine}"; } } } textBoxMeetingsByType.Text = output; }
private void buttonSave_Click(object sender, EventArgs e) { if (!ValidateChildren()) { DialogResult = DialogResult.None; return; } if (cust.customerId == 0) { createNewCustomer(); } else { using (var ctx = new DataLayer.ScheduleEntities()) { var customerUpdate = (from cs in ctx.customers join a in ctx.addresses on cs.addressId equals a.addressId join c in ctx.cities on a.cityId equals c.cityId join cn in ctx.countries on c.countryId equals cn.countryId where cs.customerId == cust.customerId //orderby appt.start select new { cs, a, c, cn }).Single(); //`customerId`, `customerName`, `addressId`, `active`, `createDate`, `createdBy`, `lastUpdate`, `lastUpdateBy` customerUpdate.cs.customerName = textBoxName.Text; customerUpdate.a.address1 = textBoxAddress1.Text; customerUpdate.a.address2 = textBoxAddress2.Text; customerUpdate.a.postalCode = textBoxPostCode.Text; customerUpdate.a.cityId = comboBoxCity.SelectedIndex + 1; customerUpdate.cs.active = checkBoxActive.Checked; customerUpdate.a.phone = textBoxPhone.Text; customerUpdate.cs.lastUpdate = DateTime.Now.ToUniversalTime(); customerUpdate.cs.lastUpdateBy = CurrentUserName; if (customerUpdate.cs.createdBy is null) { customerUpdate.cs.createdBy = CurrentUserName; } // try to save changes try { ctx.SaveChanges(); // write changes to database file this.Close(); DialogResult = DialogResult.OK; } catch (DbEntityValidationException) { MessageBox.Show("Something has gone wrong saving this data", "Entity Validation Exception"); } } } }