private void CounselorUpdateBtn_Click(object sender, RoutedEventArgs e)
{
jeffersonEntities dbContext = new jeffersonEntities();
//Create an object called CurrentCounselor of type Counselor1
try
{
//The update statement updates all fields. This enables the user to update any and all the information
dbContext.Database.ExecuteSqlCommand("Update Person set FirstName = @FirstName, LastName = @LastName, Address = @Address, City = @City, State = @State, ZIP = @ZIP, Phone = @Phone, Email = @Email where PersonID = @CounselorID",
//Assigns parameters values found in the textfields
new SqlParameter("FirstName", CounselorFNameTxt.Text),
new SqlParameter("LastName", CounselorLNameTxt.Text),
new SqlParameter("Address", CounselorAddressTxt.Text),
new SqlParameter("City", CounselorCityTxt.Text),
new SqlParameter("State", cbState.SelectionBoxItem),
new SqlParameter("Zip", CounselorZipTxt.Text),
new SqlParameter("Phone", CounselorPhoneTxt.Text),
new SqlParameter("Email", CounselorEmailTxt.Text),
new SqlParameter("CounselorID", this.CounselorID));
//updates the Degree by using the counselorID so that we know we are updating the correct record
dbContext.Database.ExecuteSqlCommand("Update counselor set DegreeSuffix = @Degree where CounselorID = @CounselorID",
new SqlParameter("Degree", DegreeCB.SelectionBoxItem),
new SqlParameter("CounselorID", this.CounselorID));
//Populates the datagrid with the updated information
var Counselor = dbContext.Counselor1.SqlQuery("select * from Counselor1").ToList();
CounselorDataGrid.ItemsSource = Counselor;
CounselorDataGrid.SelectedIndex = Index;
String AddName = CounselorFNameTxt.Text + " " + CounselorLNameTxt.Text;
MessageBox.Show(AddName + " Updated");
//Resets the text fields
CounselorFNameTxt.Text = null;
CounselorLNameTxt.Text = null;
DegreeCB.SelectedIndex = -1;
CounselorAddressTxt.Text = null;
CounselorCityTxt.Text = null;
cbState.SelectedIndex = -1;
CounselorZipTxt.Text = null;
CounselorPhoneTxt.Text = null;
CounselorEmailTxt.Text = null;
this.CounselorID = null;
}
catch
{
MessageBox.Show("Please Select a Counselor from 'Current Counselor', and click the 'Edit Counselor' button to populate Counselor's information in the fields");
}
}
private void RoomUpdateBtn_Click(object sender, RoutedEventArgs e)
{
jeffersonEntities dbContext = new jeffersonEntities();
try
{
//executes the query to update rooms
dbContext.Database.ExecuteSqlCommand("Update Room set RoomName = @RoomName where RoomNumber = @RNum",
new SqlParameter("RoomName", RoomNameTxt1.Text),
new SqlParameter("RNum", RNum));
var Room = dbContext.Rooms1.SqlQuery("select * from Rooms").ToList();
RoomDataGrid.ItemsSource = Room;
RoomDataGrid.SelectedIndex = Index; //selected the room that was updated
MessageBox.Show(RoomNameTxt1.Text + " was Updated"); //confirmation message that the room was updated
//resets the values
RoomNameTxt1.Text = null;
RoomNumberTxt.Text = null;
RNum = null;
Index = 0;
//Makes the RoomNumber collapsed so that the user doesn't see it
RoomNumberTxt.Visibility = System.Windows.Visibility.Collapsed;
RoomNumLbl.Visibility = System.Windows.Visibility.Collapsed;
RoomEditBtn.IsEnabled = true;
RoomAddBtn.IsEnabled = true;
}
catch
{
//if the user hasn't selected a room from the datagrid and click the edit button it creates an error and this message is displayed
MessageBox.Show("Please select a Room from Current Rooms, and click the 'Edit Room' button");
}
}
private void EmployeeUpdateBtn_Click_1(object sender, RoutedEventArgs e)
{
try
{
jeffersonEntities dbContext = new jeffersonEntities();
//Create an object called CurrentEmployee of type Employee1
//This code gets the supervisor for the current selection, splits the name into first and last, and then finds the associated ID
//Gets name of the supervisor for selected record
var supervisorName = SupervisorCB.SelectionBoxItem.ToString();
string Sup = supervisorName;
//Ex: ger = Holly White
var EmployeeName = Sup.Split(' ');
//Ex: [1] = White [0] = Holly
string supervisorLastName = EmployeeName[1].ToString();
string supervisorFirstName = EmployeeName[0].ToString();
//runs a query to obtain ID
int query_super_ID = (from Person in dbContext.People
where Person.FirstName == supervisorFirstName &&
Person.LastName == supervisorLastName
select Person).First <Person>().personID;
int superID = Convert.ToInt32(query_super_ID);
Console.WriteLine(superID);
//The update statement updates all fields. This enables the user to update any and all the information
dbContext.Database.ExecuteSqlCommand("Update Person set FirstName = @FirstName, LastName = @LastName, Address = @Address, City = @City, State = @State, ZIP = @ZIP, Phone = @Phone, Email = @Email where PersonID = @EmployeeID",
//Assigns parameters values found in the textfields
new SqlParameter("FirstName", EmployeeFNameTxt.Text),
new SqlParameter("LastName", EmployeeLNameTxt.Text),
new SqlParameter("Address", EmployeeAddressTxt.Text),
new SqlParameter("City", EmployeeCityTxt.Text),
new SqlParameter("State", cbState.SelectionBoxItem),
new SqlParameter("Zip", EmployeeZipTxt.Text),
new SqlParameter("Phone", EmployeePhoneTxt.Text),
new SqlParameter("Email", EmployeeEmailTxt.Text),
new SqlParameter("EmployeeID", this.EmployeeID));
//updates the Degree by using the EmployeeID so that we know we are updating the correct record
dbContext.Database.ExecuteSqlCommand("Update Employee set DateHired = @DateHired, Wage = @Wage, SupervisorID = @SupervisorID where EmployeeID = @EmployeeID",
new SqlParameter("DateHired", DateHired.SelectedDate.Value.ToShortDateString()),
new SqlParameter("Wage", EmployeeWageTxt.Text),
new SqlParameter("SupervisorID", superID),
new SqlParameter("EmployeeID", this.EmployeeID));
String AddName = EmployeeFNameTxt.Text + " " + EmployeeLNameTxt.Text;
//Resets the text fields
EmployeeFNameTxt.Text = null;
EmployeeLNameTxt.Text = null;
DateHired.Text = null;
EmployeeWageTxt.Text = null;
SupervisorCB.SelectedIndex = -1;
EmployeeAddressTxt.Text = null;
EmployeeCityTxt.Text = null;
cbState.SelectedIndex = -1;
EmployeeZipTxt.Text = null;
EmployeePhoneTxt.Text = null;
EmployeeEmailTxt.Text = null;
this.EmployeeID = null;
//Populates the datagrid with the updated information
var Employee = dbContext.Employees1.SqlQuery("select * from Employees").ToList();
EmployeeDataGrid.ItemsSource = Employee;
EmployeeDataGrid.SelectedIndex = Index;
//confirmation message that record was updated
MessageBox.Show(AddName + " Updated");
EmployeeAddBtn.IsEnabled = true;
EmployeeEditBtn.IsEnabled = true;
}
catch
{
MessageBox.Show("Please select an Employee from 'Current Employees' and then click the 'Edit Employee' Button");
}
}
private void ApptUpdateBtn_Click(object sender, RoutedEventArgs e)
{
try
{
jeffersonEntities dbContext = new jeffersonEntities();
//concatenates date and time so that you can get the right Starttime
String Date = ApptDate.SelectedDate.Value.ToShortDateString();
String Time = (String)TimeCB.SelectionBoxItem;
String DateTime = Date + " " + Time;
string Counselor = CounselorCB.SelectionBoxItem.ToString();
//Ex: ger = Holly White
var CounselorName = Counselor.Split(' ');
//Ex: [1] = White [0] = Holly
string CounselorLastName = CounselorName[1].ToString();
string CounselorFirstName = CounselorName[0].ToString();
int query_Couns_ID = (from Person in dbContext.People
where Person.FirstName == CounselorFirstName &&
Person.LastName == CounselorLastName
select Person).First <Person>().personID;
int CLRID = Convert.ToInt32(query_Couns_ID);
//Separate Employee First Name and Last Name
string Employee = ScheduledByCB.SelectionBoxItem.ToString();
//Ex: ger = Holly White
var EmployeeName = Employee.Split(' ');
//Ex: [1] = White [0] = Holly
string EmployeeLastName = EmployeeName[1].ToString();
string EmployeeFirstName = EmployeeName[0].ToString();
int query_Emp_ID = (from Person in dbContext.People
where Person.FirstName == EmployeeFirstName &&
Person.LastName == EmployeeLastName
select Person).First <Person>().personID;
int EmpID = Convert.ToInt32(query_Emp_ID);
//Client
string Client = ClientCB.SelectionBoxItem.ToString();
//Ex: ger = Holly White
var ClientName = Client.Split(' ');
//Ex: [1] = White [0] = Holly
string ClientLastName = ClientName[1].ToString();
string ClientFirstName = ClientName[0].ToString();
int query_Client_ID = (from Person in dbContext.People
where Person.FirstName == ClientFirstName &&
Person.LastName == ClientLastName
select Person).First <Person>().personID;
int CID = Convert.ToInt32(query_Client_ID);
//The update statement updates all fields. This enables the user to update any and all the information
dbContext.Database.ExecuteSqlCommand("Update Appointment set StartTime = @StartTime, Duration = @Duration, CounselorID =@Counselor, EmployeeID = @Employee, RoomNumber = (Select RoomNumber from Room where RoomName =@RoomName), Notes = @Notes where AppointmentID = @AppointmentID",
//Assigns parameters values found in the textfields
new SqlParameter("StartTime", DateTime),
new SqlParameter("Duration", DurationCB.SelectionBoxItem),
new SqlParameter("Counselor", CLRID),
new SqlParameter("Employee", EmpID),
new SqlParameter("RoomName", RoomCB.SelectionBoxItem),
new SqlParameter("Notes", NotesTxt.Text),
new SqlParameter("AppointmentID", this.AppointmentID));
//updates the Degree by using the ClientID so that we know we are updating the correct record
dbContext.Database.ExecuteSqlCommand("UPDATE ScheduledFor SET ClientID = @NewClientID WHERE AppointmentID = @AppointmentID AND ClientID = (Select ClientID from ScheduledFor where AppointmentID =@AppointmentID)",
new SqlParameter("AppointmentID", this.AppointmentID),
new SqlParameter("NewClientID", CID));
Console.WriteLine(CID);
Console.WriteLine(this.ClientID);
var Appointment = dbContext.Appointments1.SqlQuery("select * from Appointments").ToList();
ApptDataGrid.ItemsSource = Appointment;
ApptDataGrid.SelectedIndex = Index;
MessageBox.Show("Appointment #" + AppointmentID + " was Updated");
//Resets the text fields
ApptDate.Text = null;
TimeCB.SelectedIndex = -1;
DurationCB.SelectedIndex = -1;
CounselorCB.SelectedIndex = -1;
ScheduledByCB.SelectedIndex = -1;
RoomCB.SelectedIndex = -1;
NotesTxt.Text = null;
ClientCB.SelectedIndex = -1;
AppointmentID = null;
ApptAddBtn.IsEnabled = true;
ApptEditBtn.IsEnabled = true;
}
catch
{
MessageBox.Show("Please Select an Appointment from 'Appointments', and then Click 'Edit Appointment'");
}
}
private void ClientAddBtn_Click(object sender, RoutedEventArgs e)
{
jeffersonEntities dbContext = new jeffersonEntities();
//makes so the user has to enter all critical information
if (ClientFNameTxt.Text == null || ClientLNameTxt.Text == null ||
ClientAddressTxt.Text == null ||
ClientCityTxt.Text == null ||
ClientZipTxt.Text == null ||
ClientPhoneTxt.Text == null ||
ClientEmailTxt.Text == null ||
cbState.SelectedIndex == -1)
{
MessageBox.Show("Please Enter All information");
}
else
{
int iMatch = 0;
//the for loop goes through all the records to check if there are any matches
for (int iCount = 0; iCount < ClientDataGrid.Items.Count - 1; iCount++)
{
Client1 CurrentClient = new Client1();
ClientDataGrid.SelectedIndex = iCount;// set the index to that of the iCount
CurrentClient = (Client1)ClientDataGrid.SelectedItem;
//if the new name matches a record, one is added to iMatch
if (ClientFNameTxt.Text.ToLowerInvariant() == CurrentClient.FirstName.ToLowerInvariant() &&
ClientLNameTxt.Text.ToLowerInvariant() == CurrentClient.LastName.ToLowerInvariant() &&
ClientAddressTxt.Text.ToLowerInvariant() == CurrentClient.Address.ToLowerInvariant())
{
iMatch++;
}
}
if (iMatch > 0)
{
//asks the user if they want to add the name even tho it already exists
MessageBoxResult messageBoxResult = System.Windows.MessageBox.Show("Client already exists with this name, add anyway?", "Add Confirmation", System.Windows.MessageBoxButton.YesNo);
if (messageBoxResult == MessageBoxResult.Yes)
{
dbContext.Database.ExecuteSqlCommand("Insert into Person(FirstName, LastName, Address, City, State, Zip, Phone, Email) Values (@FirstName, @LastName, @Address, @City, @State, @Zip, @Phone, @Email)",
//assign the parameters values
new SqlParameter("FirstName", ClientFNameTxt.Text),
new SqlParameter("LastName", ClientLNameTxt.Text),
new SqlParameter("Address", ClientAddressTxt.Text),
new SqlParameter("City", ClientCityTxt.Text),
new SqlParameter("State", cbState.SelectionBoxItem),
new SqlParameter("Zip", ClientZipTxt.Text),
new SqlParameter("Phone", ClientPhoneTxt.Text),
new SqlParameter("Email", ClientEmailTxt.Text));
//the if statement verifies if we need to grab a clientID by spliting the first name and last name
if (ReferredByCB.SelectedIndex != -1)
{
//Execute the following query to add new record to the person table.
//make variables that capture the values
var refName = ReferredByCB.SelectionBoxItem.ToString();
string Ref = refName;
//Ex: ger = Holly White
var reName = Ref.Split(' ');
//Ex: [1] = White [0] = Holly
string refLastName = reName[1].ToString();
string refFirstName = reName[0].ToString();
Console.WriteLine(refLastName);
//gets ClientID for referred by
int query_super_ID = (from Person in dbContext.People
where Person.FirstName == refFirstName &&
Person.LastName == refLastName
select Person).First <Person>().personID;
int RefID = Convert.ToInt32(query_super_ID);
//runds query to run query
dbContext.Database.ExecuteSqlCommand("insert into Client values((select max(personid) from person), @FoundOut, @ReminderMethod, @ReferredBy)",
new SqlParameter("FoundOut", FoundOutCB.SelectionBoxItem),
new SqlParameter("ReminderMethod", ClientReminderCB.SelectionBoxItem),
new SqlParameter("ReferredBy", RefID));
}
else
{
//Execute the follwoing query to add new record to the Client table
dbContext.Database.ExecuteSqlCommand("insert into Client(ClientID, FoundOut, ReminderMethod) values((select max(personid) from person), @FoundOut, @ReminderMethod)",
new SqlParameter("FoundOut", FoundOutCB.SelectionBoxItem),
new SqlParameter("ReminderMethod", ClientReminderCB.SelectionBoxItem));
}
//Populate the datagrid with the new records
var Client = dbContext.Clients1.SqlQuery("select * from Clients").ToList();
ClientDataGrid.ItemsSource = Client;
//select the first record in the datagrid
ClientDataGrid.SelectedIndex = 0;
//Display that the new client has been added
String UpdateName = ClientFNameTxt.Text + " " + ClientLNameTxt.Text;
MessageBox.Show(UpdateName + " Added to Current Clients");
//Reset the values in the fields
ClientFNameTxt.Text = null;
ClientLNameTxt.Text = null;
FoundOutCB.SelectedIndex = -1;
ClientReminderCB.SelectedIndex = -1;
ReferredByCB.SelectedIndex = -1;
ClientAddressTxt.Text = null;
ClientCityTxt.Text = null;
cbState.SelectedIndex = -1;
ClientZipTxt.Text = null;
ClientPhoneTxt.Text = null;
ClientEmailTxt.Text = null;
ReferredByCB.SelectedIndex = -1;
ClientID = null;
}
else
{
//Reset the values in the fields
ClientFNameTxt.Text = null;
ClientLNameTxt.Text = null;
FoundOutCB.SelectedIndex = -1;
ClientReminderCB.SelectedIndex = -1;
ReferredByCB.SelectedIndex = -1;
ClientAddressTxt.Text = null;
ClientCityTxt.Text = null;
cbState.SelectedIndex = -1;
ClientZipTxt.Text = null;
ClientPhoneTxt.Text = null;
ClientEmailTxt.Text = null;
ReferredByCB.SelectedIndex = -1;
ClientID = null;
}
}
else
{
//runs query to add records
dbContext.Database.ExecuteSqlCommand("Insert into Person(FirstName, LastName, Address, City, State, Zip, Phone, Email) Values (@FirstName, @LastName, @Address, @City, @State, @Zip, @Phone, @Email)",
//assign the parameters values
new SqlParameter("FirstName", ClientFNameTxt.Text),
new SqlParameter("LastName", ClientLNameTxt.Text),
new SqlParameter("Address", ClientAddressTxt.Text),
new SqlParameter("City", ClientCityTxt.Text),
new SqlParameter("State", cbState.SelectionBoxItem),
new SqlParameter("Zip", ClientZipTxt.Text),
new SqlParameter("Phone", ClientPhoneTxt.Text),
new SqlParameter("Email", ClientEmailTxt.Text));
//the if statement verifies if we need to grab a clientID by spliting the first name and last name
if (ReferredByCB.SelectedIndex != -1)
{
//Execute the following query to add new record to the person table.
//make variables that capture the values
var refName = ReferredByCB.SelectionBoxItem.ToString();
string Ref = refName;
//Ex: ger = Holly White
var reName = Ref.Split(' ');
//Ex: [1] = White [0] = Holly
string refLastName = reName[1].ToString();
string refFirstName = reName[0].ToString();
Console.WriteLine(refLastName);
int query_super_ID = (from Person in dbContext.People
where Person.FirstName == refFirstName &&
Person.LastName == refLastName
select Person).First <Person>().personID;
int RefID = Convert.ToInt32(query_super_ID);
dbContext.Database.ExecuteSqlCommand("insert into Client values((select max(personid) from person), @FoundOut, @ReminderMethod, @ReferredBy)",
new SqlParameter("FoundOut", FoundOutCB.SelectionBoxItem),
new SqlParameter("ReminderMethod", ClientReminderCB.SelectionBoxItem),
new SqlParameter("ReferredBy", RefID));
}
else
{
//Execute the follwoing query to add new record to the Client table
dbContext.Database.ExecuteSqlCommand("insert into Client(ClientID, FoundOut, ReminderMethod) values((select max(personid) from person), @FoundOut, @ReminderMethod)",
new SqlParameter("FoundOut", FoundOutCB.SelectionBoxItem),
new SqlParameter("ReminderMethod", ClientReminderCB.SelectionBoxItem));
}
//Populate the datagrid with the new records
var Client = dbContext.Clients1.SqlQuery("select * from Clients").ToList();
ClientDataGrid.ItemsSource = Client;
//select the first record in the datagrid
ClientDataGrid.SelectedIndex = 0;
//Display that the new client has been added
String UpdateName = ClientFNameTxt.Text + " " + ClientLNameTxt.Text;
MessageBox.Show(UpdateName + " Added to Current Clients");
//Reset the values in the fields
ClientFNameTxt.Text = null;
ClientLNameTxt.Text = null;
FoundOutCB.SelectedIndex = -1;
ClientReminderCB.SelectedIndex = -1;
ReferredByCB.SelectedIndex = -1;
ClientAddressTxt.Text = null;
ClientCityTxt.Text = null;
cbState.SelectedIndex = -1;
ClientZipTxt.Text = null;
ClientPhoneTxt.Text = null;
ClientEmailTxt.Text = null;
ReferredByCB.SelectedIndex = -1;
ClientID = null;
}
}
}
private void ClientUpdateBtn_Click(object sender, RoutedEventArgs e)
{
jeffersonEntities dbContext = new jeffersonEntities();
//Execute the following query to add new record to the person table.
//make variables that capture the values
try
{
int RefID;
//The update statement updates all fields. This enables the user to update any and all the information
dbContext.Database.ExecuteSqlCommand("Update Person set FirstName = @FirstName, LastName = @LastName, Address = @Address, City = @City, State = @State, ZIP = @ZIP, Phone = @Phone, Email = @Email where PersonID = @ClientID",
//Assigns parameters values found in the textfields
new SqlParameter("FirstName", ClientFNameTxt.Text),
new SqlParameter("LastName", ClientLNameTxt.Text),
new SqlParameter("Address", ClientAddressTxt.Text),
new SqlParameter("City", ClientCityTxt.Text),
new SqlParameter("State", cbState.SelectionBoxItem),
new SqlParameter("Zip", ClientZipTxt.Text),
new SqlParameter("Phone", ClientPhoneTxt.Text),
new SqlParameter("Email", ClientEmailTxt.Text),
new SqlParameter("ClientID", this.ClientID));
if (ReferredByCB.SelectedIndex != -1)
{
var refName = ReferredByCB.SelectionBoxItem.ToString();
string Ref = refName;
//Ex: ger = Holly White
var reName = Ref.Split(' ');
//Ex: [1] = White [0] = Holly
string refLastName = reName[1].ToString();
string refFirstName = reName[0].ToString();
int query_Referred_ID = (from Person in dbContext.People
where Person.FirstName == refFirstName &&
Person.LastName == refLastName
select Person).First <Person>().personID;
RefID = Convert.ToInt32(query_Referred_ID);
//updates the Degree by using the ClientID so that we know we are updating the correct record
dbContext.Database.ExecuteSqlCommand("Update Client set FoundOut = @FoundOut, ReminderMethod = @ReminderMethod, ReferredByClientID = @ReferredBy where ClientID = @ClientID",
new SqlParameter("FoundOut", FoundOutCB.SelectionBoxItem),
new SqlParameter("ReminderMethod", ClientReminderCB.SelectionBoxItem),
new SqlParameter("ReferredBy", RefID),
new SqlParameter("ClientID", this.ClientID));
}
else
{
//updates the Degree by using the ClientID so that we know we are updating the correct record
dbContext.Database.ExecuteSqlCommand("Update Client set FoundOut = @FoundOut, ReminderMethod = @ReminderMethod where ClientID = @ClientID",
new SqlParameter("FoundOut", FoundOutCB.SelectionBoxItem),
new SqlParameter("ReminderMethod", ClientReminderCB.SelectionBoxItem),
new SqlParameter("ClientID", this.ClientID));
}
String UpdateName = ClientFNameTxt.Text + " " + ClientLNameTxt.Text;
//Resets the text fields
ClientFNameTxt.Text = null;
ClientLNameTxt.Text = null;
FoundOutCB.SelectedIndex = -1;
ClientReminderCB.SelectedIndex = -1;
ReferredByCB.SelectedIndex = -1;
ClientAddressTxt.Text = null;
ClientCityTxt.Text = null;
cbState.SelectedIndex = -1;
ClientZipTxt.Text = null;
ClientPhoneTxt.Text = null;
ClientEmailTxt.Text = null;
ReferredByCB.SelectedIndex = -1;
this.ClientID = null;
ClientAddBtn.IsEnabled = true;
ClientDeleteBtn.IsEnabled = true;
//Populates the datagrid with the updated information
var Client = dbContext.Clients1.SqlQuery("select * from Clients").ToList();
ClientDataGrid.ItemsSource = Client;
ClientDataGrid.SelectedIndex = Index;
MessageBox.Show(UpdateName + " Updated");
ClientAddBtn.IsEnabled = true;
ClientEditBtn.IsEnabled = true;
}
catch
{
MessageBox.Show("Please select a Client from 'Current Clients' and Click the 'Edit Client' Button to populate fields with the Client's information");
}
}
