public List <User_Appointments> Get_UnacceptedAppointments(string Username)
{
List <User_Appointments> uApp = new List <User_Appointments>();
SqlConnection scon = new SqlConnection(GlobalVariables.connectionString);
try
{
string sQuery = @"Select a.Appointment_ID ," +
" a.Appointment_Date ," +
" a.Appointment_Time ," +
" a.Company_IDs," +
" a.Appointment_Header," +
" a.Appointment_Description," +
" a.Expected_Duration from Appointments as a " +
" left join AppointmentsUsers as au on a.Appointment_ID = au.Appointment_ID" +
" where au.Username = @Val_Username and " +
" au.Accepted = 0";
scon.Open();
SqlCommand scom = new SqlCommand(sQuery, scon);
scom.Parameters.AddWithValue("@Val_Username", Username);
scom.CommandType = System.Data.CommandType.Text;
SqlDataReader sread = scom.ExecuteReader();
while (sread.Read())
{
User_Appointments returnAppoint = new User_Appointments();
returnAppoint.Appointment_ID = Convert.ToInt32(sread["Appointment_ID"].ToString());
returnAppoint.Appointment_DateTime = Convert.ToDateTime(Convert.ToDateTime(sread["Appointment_Date"]).ToString("yyyy-MM-dd") + " " + sread["Appointment_Time"].ToString());
returnAppoint.Company_IDs = sread["Company_IDs"].ToString().Split(",".ToCharArray()).ToList();
returnAppoint.Appointment_Header = sread["Appointment_Header"].ToString();
returnAppoint.Appointment_Description = sread["Appointment_Description"].ToString();
returnAppoint.Expected_Duration = new TimeSpan(0, Convert.ToInt16(sread["Expected_Duration"].ToString()), 0);
uApp.Add(returnAppoint);
}
sread.Close();
foreach (User_Appointments uap in uApp)
{
SqlCommand scom1 = new SqlCommand(@"Select Username from AppointmentsUsers where Appointment_ID = @Val_Appointment_ID ", scon);
scom1.CommandType = System.Data.CommandType.Text;
scom1.Parameters.AddWithValue("@Val_Appointment_ID", uap.Appointment_ID);
SqlDataReader sread1 = scom1.ExecuteReader();
List <string> attendees = new List <string>();
while (sread1.Read())
{
string testString = sread1["Username"].ToString();
attendees.Add(testString);
}
uap.usernames = attendees;
sread1.Close();
}
}
catch (Exception ex) { }
finally
{
scon.Close();
}
return(uApp);
}
public bool UpdateAppointmentsXML(User_Appointments appointmentOld, User_Appointments appointmentNew, bool errorChecking)
{
if (!Directory.Exists(AppDomain.CurrentDomain.BaseDirectory + @"\XML"))//creates directory
{
Directory.CreateDirectory(AppDomain.CurrentDomain.BaseDirectory + @"\XML");
}
if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + @"\XML\Appointments.xml"))//File exists
{
XDocument doc = XDocument.Load(AppDomain.CurrentDomain.BaseDirectory + @"\XML\Appointments.xml");
List <XElement> listOfAppointments = doc.Descendants("Appointments").ToList()[0].Elements("Appointment").ToList();
XElement theUpdate = listOfAppointments
.Where(tempSearch => tempSearch.Attribute("Usernames").Value.ToString() == string.Join(",", appointmentOld.usernames))
.Where(tempSearch => tempSearch.Attribute("Appointment_DateTime").Value.ToString() == appointmentOld.Appointment_DateTime.ToString(""))
.ToList()[0];
listOfAppointments.Remove(theUpdate);
theUpdate.Attribute("Usernames").Value = string.Join(",", appointmentNew.usernames);
theUpdate.Attribute("Appointment_Header").Value = appointmentNew.Appointment_Header;
theUpdate.Attribute("Appointment_Description").Value = appointmentNew.Appointment_Description;
listOfAppointments.Add(theUpdate);
doc.Descendants("Appointments").Elements("Appointment").Remove();
doc.Descendants("Appointments").ToList()[0].Add(listOfAppointments);
/*
*
* listOfAppointments.Add(
* new XElement("Appointment",
* new XAttribute("Appointment_ID", appointments.Appointment_ID.ToString()),
* new XAttribute("Appointment_DateTime", appointments.Appointment_DateTime.ToString()),//"dd/MM/yyyy HH:mm"
* new XAttribute("Usernames", string.Join(",", appointments.usernames)),
* new XAttribute("Company_IDs", string.Join(",", appointments.Company_IDs)),
* new XAttribute("Appointment_Header", appointments.Appointment_Header),
* new XAttribute("Appointment_Description", appointments.Appointment_Description),
* new XAttribute("Expected_Duration", appointments.Expected_Duration.TotalMinutes.ToString())
* )
* );
*/
//doc.Descendants("Appointments").ToList()[0].Add(listOfAppointments);
doc.Save("Appointments.xml");
File.Delete(AppDomain.CurrentDomain.BaseDirectory + @"\XML\Appointments.xml");
File.Move(AppDomain.CurrentDomain.BaseDirectory + @"\Appointments.xml", AppDomain.CurrentDomain.BaseDirectory + @"\XML\Appointments.xml");
File.Delete(AppDomain.CurrentDomain.BaseDirectory + @"\Appointments.xml");
}
else
{
CreateAppointmentsXML(new List <User_Appointments>()
{
appointmentNew
});
}
return(true);
}
public bool Update_Appointments(User_Appointments appointmentOld, User_Appointments appointmentNew)
{
bool sReturn = false;
SqlConnection scon = new SqlConnection(GlobalVariables.connectionString);
try
{
scon.Open();
SqlCommand scom = new SqlCommand(@"update Appointments set Appointment_Date = @Val_Appointment_Date, " +
" Appointment_Time = @Val_Appointment_Time, " +
" Appointment_Header = @Val_Appointment_Header," +
" Appointment_Description = @Val_Appointment_Description," +
" Expected_Duration = @Val_Expected_Duration " +
" where Appointment_ID = @Val_Appointment_ID", scon);
scom.Parameters.AddWithValue("@Val_Appointment_Date", appointmentNew.Appointment_DateTime.ToString("MM/dd/yyyy"));
scom.Parameters.AddWithValue("@Val_Appointment_Time", appointmentNew.Appointment_DateTime.ToString("HH:mm"));
scom.Parameters.AddWithValue("@Val_Appointment_Header", appointmentNew.Appointment_Header);
scom.Parameters.AddWithValue("@Val_Appointment_Description", appointmentNew.Appointment_Description);
scom.Parameters.AddWithValue("@Val_Expected_Duration", appointmentNew.Expected_Duration.TotalMinutes);
scom.Parameters.AddWithValue("@Val_Appointment_ID", appointmentOld.Appointment_ID);
scom.CommandType = System.Data.CommandType.Text;
scom.ExecuteNonQuery();
SqlCommand scom1 = new SqlCommand(@"delete from AppointmentsUsers where Appointment_ID = @Val_Appointment_ID", scon);
scom1.Parameters.AddWithValue("@Val_Appointment_ID", appointmentOld.Appointment_ID);
scom1.CommandType = System.Data.CommandType.Text;
scom1.ExecuteNonQuery();
foreach (string insertionUser in appointmentNew.usernames)
{
SqlCommand scom2 = new SqlCommand(@"insert into AppointmentsUsers values (@Val_Appointment_ID, @Val_Username, @Val_Accepted)", scon);
scom2.Parameters.AddWithValue("@Val_Appointment_ID", appointmentOld.Appointment_ID);
scom2.Parameters.AddWithValue("@Val_Username", insertionUser);
if (GlobalVariables.currentUser == insertionUser)
{
scom2.Parameters.AddWithValue("@Val_Accepted", true);
}
else
{
scom2.Parameters.AddWithValue("@Val_Accepted", false);
}
scom2.CommandType = System.Data.CommandType.Text;
scom2.ExecuteNonQuery();
}
sReturn = true;
}
catch (Exception ex) { }
finally
{
scon.Close();
}
return(sReturn);
}
public bool InsertAppointmentsXML(User_Appointments appointments, bool errorChecking)
{
if (!Directory.Exists(AppDomain.CurrentDomain.BaseDirectory + @"\XML"))//creates directory
{
Directory.CreateDirectory(AppDomain.CurrentDomain.BaseDirectory + @"\XML");
}
if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + @"\XML\Appointments.xml"))//File exists
{
XDocument doc = XDocument.Load(AppDomain.CurrentDomain.BaseDirectory + @"\XML\Appointments.xml");
List <XElement> listOfAppointments = doc.Descendants("Appointments").Descendants("Appointment").ToList();
if (listOfAppointments.Count != 0)
{
}
listOfAppointments.Add(
new XElement("Appointment",
new XAttribute("Appointment_ID", appointments.Appointment_ID.ToString()),
new XAttribute("Appointment_DateTime", appointments.Appointment_DateTime.ToString()),//"dd/MM/yyyy HH:mm"
new XAttribute("Usernames", string.Join(",", appointments.usernames)),
new XAttribute("Company_IDs", string.Join(",", appointments.Company_IDs)),
new XAttribute("Appointment_Header", appointments.Appointment_Header),
new XAttribute("Appointment_Description", appointments.Appointment_Description),
new XAttribute("Expected_Duration", appointments.Expected_Duration.TotalMinutes.ToString())
)
);
doc.Descendants("Appointments").Elements("Appointment").Remove();
doc.Descendants("Appointments").ToList()[0].Add(listOfAppointments);
doc.Save("Appointments.xml");
File.Delete(AppDomain.CurrentDomain.BaseDirectory + @"\XML\Appointments.xml");
File.Move(AppDomain.CurrentDomain.BaseDirectory + @"\Appointments.xml", AppDomain.CurrentDomain.BaseDirectory + @"\XML\Appointments.xml");
File.Delete(AppDomain.CurrentDomain.BaseDirectory + @"\Appointments.xml");
}
else
{
CreateAppointmentsXML(new List <User_Appointments> ()
{
appointments
});
}
return(true);
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
appointmentUpdates = new User_Appointments();
appointmentUpdates.Appointment_DateTime = new DateTime();
appointmentUpdates.Appointment_Description = "";
appointmentUpdates.Appointment_Header = "";
appointmentUpdates.Appointment_ID = 0;
appointmentUpdates.Company_IDs = new List <string>();
appointmentUpdates.Expected_Duration = new TimeSpan(0, 60, 0);
appointmentUpdates.usernames = new List <string>();
appointmentOld = new User_Appointments();
appointmentOld.Appointment_DateTime = new DateTime();
appointmentOld.Appointment_Description = "";
appointmentOld.Appointment_Header = "";
appointmentOld.Appointment_ID = 0;
appointmentOld.Company_IDs = new List <string>();
appointmentOld.Expected_Duration = new TimeSpan(0, 60, 0);
appointmentOld.usernames = new List <string>();
LoadTimes();
}
void mouseLeftButtonUp_DisplayAppointment(object sender, MouseButtonEventArgs e)
{
AppointmentsHeader_DetailsLabel.Content = ((sender as StackPanel).Children[0] as Label).Content.ToString();
List <string> appointmentAttendeesList = new List <string>();
List <User_Appointments> chosenAppoint = dailyAppointments.Where(appoinntItem => appoinntItem.Appointment_DateTime.ToString("HH:mm") == ((sender as StackPanel).Children[0] as Label).Content.ToString()).ToList();
if (chosenAppoint.Count != 0)
{
textBox_AppointmentHeader.Text = chosenAppoint[0].Appointment_Header;
textBox_AppointmentDescription.Text = chosenAppoint[0].Appointment_Description;
appointmentUpdates.Appointment_DateTime = chosenAppoint[0].Appointment_DateTime;
appointmentOld = chosenAppoint[0];
appointmentAttendeesList = chosenAppoint[0].usernames;
}
else
{
textBox_AppointmentHeader.Text = "";
textBox_AppointmentDescription.Text = "";
}
Stackpanel_Attendees.Children.Clear();
List <B_Users> allUsers = new List <B_Users>();
if (!string.IsNullOrEmpty(GlobalVariables.connectionString))
{
allUsers = bSQL.Get_AllUsers();
//no exceptions. there should be users in the system if you got this far. you are a user after all
}
else
{
allUsers = xmlClass.getAllUsersFromXML();
//no exceptions. there should be users in the system if you got this far. you are a user after all
}
if (allUsers.Count != 0)
{
foreach (B_Users tempUser in allUsers)
{
Border theBorder = new Border()
{
BorderThickness = new Thickness(1, 1, 1, 1),
CornerRadius = new CornerRadius(5, 5, 5, 5),
Width = 180,
Height = 30,
Margin = new Thickness(-10, 0, 0, 0),
Background = gvars.ThemeColors[GlobalVariables.chosenTheme][10] as Brush
};
StackPanel theStackpanel = new StackPanel()
{
HorizontalAlignment = HorizontalAlignment.Left,
VerticalAlignment = VerticalAlignment.Top,
Height = 30, Orientation = Orientation.Horizontal
};
theBorder.Child = theStackpanel;
CheckBox theCheckbox = new CheckBox()
{
Height = 15,
Width = 20
};
theStackpanel.Children.Add(theCheckbox);
Label theLabel = new Label()
{
Height = 27,
Width = 150,
Content = tempUser.username,
Foreground = gvars.ThemeColors[GlobalVariables.chosenTheme][8] as Brush
};
theStackpanel.Children.Add(theLabel);
if (tempUser.username == GlobalVariables.currentUser)
{
theBorder.IsEnabled = false;
theCheckbox.IsChecked = true;
}
else if (appointmentAttendeesList.Contains(tempUser.username))
{
theCheckbox.IsChecked = true;
}
else
{
theStackpanel.MouseEnter += new MouseEventHandler(this.hoverOver_ColorChange_Attendees);
theStackpanel.MouseLeave += new MouseEventHandler(this.hoverLeave_ColorChange_Attendees);
theStackpanel.MouseLeftButtonUp += new MouseButtonEventHandler(this.mouseLeftButtonUp_CheckUser);
}
Stackpanel_Attendees.Children.Add(theBorder);
}
}
}
public bool Insert_Appointments(User_Appointments appointments)
{
bool sReturn = false;
List <User_Appointments> uApp = new List <User_Appointments>();
SqlConnection scon = new SqlConnection(GlobalVariables.connectionString);
try
{
int appointmentID = 0;
DateTime insertionDate = DateTime.Now;
scon.Open();
SqlCommand scom = new SqlCommand(@"insert into Appointments (Appointment_Date, Appointment_Time, Usernames, Company_IDs, Appointment_Header, Appointment_Description, Expected_Duration) values (@Val_Appointment_Date, @Val_Appointment_Time, @Val_Usernames, @Val_Company_IDs, @Val_Appointment_Header, @Val_Appointment_Description, @Val_Expected_Duration)", scon);
scom.Parameters.AddWithValue("@Val_Appointment_Date", insertionDate.ToString("yyyy-MM-dd"));
scom.Parameters.AddWithValue("@Val_Appointment_Time", insertionDate.ToString("HH:mm"));
scom.Parameters.AddWithValue("@Val_Usernames", "");//insert users into AppointmentUsers once this completes
scom.Parameters.AddWithValue("@Val_Company_IDs", "");
scom.Parameters.AddWithValue("@Val_Appointment_Header", appointments.Appointment_Header);
scom.Parameters.AddWithValue("@Val_Appointment_Description", appointments.Appointment_Description);
scom.Parameters.AddWithValue("@Val_Expected_Duration", appointments.Expected_Duration.TotalMinutes);
scom.CommandType = System.Data.CommandType.Text;
scom.ExecuteNonQuery();
SqlCommand scom1 = new SqlCommand(@"select Appointment_ID from Appointments where Appointment_Date = @Val_Appointment_Date and Appointment_Time = @Val_Appointment_Time", scon);
scom1.Parameters.AddWithValue("@Val_Appointment_Date", insertionDate.ToString("yyyy-MM-dd"));
scom1.Parameters.AddWithValue("@Val_Appointment_Time", insertionDate.ToString("HH:mm"));
scom1.CommandType = System.Data.CommandType.Text;
appointmentID = Convert.ToInt32(scom1.ExecuteScalar());
SqlCommand scom2 = new SqlCommand(@"update Appointments set Appointment_Date = @Val_Appointment_Date, Appointment_Time = @Val_Appointment_Time where Appointment_ID = @Val_Appointment_ID", scon);
scom2.CommandType = System.Data.CommandType.Text;
scom2.Parameters.AddWithValue("@Val_Appointment_Date", appointments.Appointment_DateTime.ToString("MM/dd/yyyy"));
scom2.Parameters.AddWithValue("@Val_Appointment_Time", appointments.Appointment_DateTime.ToString("HH:mm"));
scom2.Parameters.AddWithValue("@Val_Appointment_ID", appointmentID);
scom2.ExecuteNonQuery();
foreach (string insertionUser in appointments.usernames)
{
SqlCommand scom3 = new SqlCommand(@"insert into AppointmentsUsers values (@Val_Appointment_ID, @Val_Username, @Val_Accepted)", scon);
scom3.Parameters.AddWithValue("@Val_Appointment_ID", appointmentID);
scom3.Parameters.AddWithValue("@Val_Username", insertionUser);
if (GlobalVariables.currentUser == insertionUser)
{
scom3.Parameters.AddWithValue("@Val_Accepted", true);
}
else
{
scom3.Parameters.AddWithValue("@Val_Accepted", false);
}
scom3.CommandType = System.Data.CommandType.Text;
scom3.ExecuteNonQuery();
}
sReturn = true;
}
catch (Exception ex) { }
finally
{
scon.Close();
}
return(sReturn);
}
