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");
}
}
}
}
