public List <ScheduleTraineeDTO> GetScheduleOfTrainees()
{
using (MySwoleMateEntities db = new MySwoleMateEntities())
{
//select Schedule.ScheduleDtTime, Trainee.FirstName, Trainee.LastName, Trainee.Email, Trainee.Height, Trainee.CellNbr,
//Trainee.Gender, Trainee.Age, Schedule.InputDtTime from Schedule INNER JOIN Trainee ON Schedule.TraineeID = Trainee.TraineeID;
var data = from Sc in db.Schedules
join Tr in db.Trainees
on Sc.TraineeID equals Tr.TraineeID
select new ScheduleTraineeDTO
{
//Sc = Sc, Tr = Tr
ScheduleID = Sc.ScheduleID,
ScheduleDtTime = Sc.ScheduleDtTime,
FirstName = Tr.FirstName,
LastName = Tr.LastName,
Email = Tr.Email,
Height = Tr.Height,
CellNbr = Tr.CellNbr,
Gender = Tr.Gender,
Age = Tr.Age,
InputDtTime = Sc.InputDtTime
};
return(data.ToList());
}
}
public List <MeasurementScheduleTraineeDTO> GetMeasurementByTraineeId(int tID)
{
using (MySwoleMateEntities db = new MySwoleMateEntities())
{
//select Trainee.FirstName, Schedule.ScheduleDtTime, Measurement.Weight,
//Measurement.Waist, Measurement.BodyFat, Measurement.Chest, Measurement.UpperArm
//From((Trainee
//INNER JOIN Schedule ON Trainee.TraineeID = Schedule.TraineeID)
//INNER JOIN Measurement ON Schedule.ScheduleID = Measurement.ScheduleID);
// Using Navigational Properties to join 3 tables
var data = from Mes in db.Measurements
where Mes.Schedule.TraineeID == tID
select new MeasurementScheduleTraineeDTO
{
TraineeID = Mes.Schedule.TraineeID,
MeasurementID = Mes.MeasurementID,
FirstName = Mes.Schedule.Trainee.FirstName,
ScheduleDtTime = Mes.Schedule.ScheduleDtTime,
Weight = Mes.Weight,
Waist = Mes.Waist,
BodyFat = Mes.BodyFat,
Chest = Mes.BodyFat,
UpperArm = Mes.UpperArm,
name = Mes.Schedule.Trainee.FirstName + " " + Mes.Schedule.Trainee.LastName
};
return(data.ToList());
}
}
public void DeleteSchedule(Schedule schedule)
{
using (MySwoleMateEntities db = new MySwoleMateEntities())
{
db.Entry(schedule).State = System.Data.Entity.EntityState.Deleted;
db.SaveChanges();
}
}
public void DeleteMeasurement(Measurement measurement)
{
using (MySwoleMateEntities db = new MySwoleMateEntities())
{
db.Entry(measurement).State = System.Data.Entity.EntityState.Deleted;
db.SaveChanges();
}
}
public void AddMeasurement(Measurement measurement)
{
using (MySwoleMateEntities db = new MySwoleMateEntities())
{
db.Measurements.Add(measurement);
db.SaveChanges();
}
}
public void AddSchedule(Schedule schedule)
{
using (MySwoleMateEntities db = new MySwoleMateEntities())
{
db.Schedules.Add(schedule);
db.SaveChanges();
}
}
//Returns All Schedules
public List <Schedule> GetSchedules()
{
List <Schedule> allSchedules = new List <Schedule>();
using (MySwoleMateEntities db = new MySwoleMateEntities())
{
allSchedules = db.Schedules.ToList();
}
return(allSchedules);
}
//Get Schedule By Id
public Schedule GetScheduleById(int scId)
{
Schedule sc = new Schedule();
using (MySwoleMateEntities db = new MySwoleMateEntities())
{
sc = db.Schedules.Find(scId);
}
return(sc);
}
public List <Measurement> GetMeasurements()
{
List <Measurement> allMeasuremnets = new List <Measurement>();
using (MySwoleMateEntities db = new MySwoleMateEntities())
{
allMeasuremnets = db.Measurements.ToList();
}
return(allMeasuremnets);
}
//Get Measurement By Id
public Measurement GetMeasurementById(int mId)
{
Measurement m = new Measurement();
using (MySwoleMateEntities db = new MySwoleMateEntities())
{
//m = db.Measurements.Single(MeasurementID => m.MeasurementID == mId);
m = db.Measurements.Find(mId);
}
return(m);
}
//Edit Schedule
public int EditSchedule(Schedule editSc)
{
using (MySwoleMateEntities db = new MySwoleMateEntities())
{
var sc = new Schedule {
ScheduleID = editSc.ScheduleID, ScheduleDtTime = editSc.ScheduleDtTime, InputDtTime = editSc.ScheduleDtTime, TraineeID = editSc.TraineeID
};
db.Entry(sc).State = System.Data.Entity.EntityState.Modified;
return(db.SaveChanges());
}
}
//Delete a Measurement
public void DeleteMeasurement(int mesId)
{
Measurement deleteMes = new Measurement()
{
MeasurementID = mesId
};
using (MySwoleMateEntities db = new MySwoleMateEntities())
{
db.Entry(deleteMes).State = System.Data.Entity.EntityState.Deleted;
db.SaveChanges();
}
}
//Delete a Schedule
public void DeleteSchedule(int scId)
{
Schedule deleteSc = new Schedule()
{
ScheduleID = scId
};
using (MySwoleMateEntities db = new MySwoleMateEntities())
{
db.Entry(deleteSc).State = System.Data.Entity.EntityState.Deleted;
db.SaveChanges();
}
}
//Add a Schedule
public int AddSchedule(Schedule newSc)
{
Schedule sc = new Schedule()
{
ScheduleDtTime = newSc.ScheduleDtTime,
InputDtTime = newSc.InputDtTime,
TraineeID = newSc.TraineeID
};
using (MySwoleMateEntities db = new MySwoleMateEntities())
{
db.Schedules.Add(sc);
return(db.SaveChanges());
}
}
public List <MeasurementScheduleTraineeDTO> GetMeasurementPerTrainee()
{
using (MySwoleMateEntities db = new MySwoleMateEntities())
{
//select Trainee.FirstName, Schedule.ScheduleDtTime, Measurement.Weight,
//Measurement.Waist, Measurement.BodyFat, Measurement.Chest, Measurement.UpperArm
//From((Trainee
//INNER JOIN Schedule ON Trainee.TraineeID = Schedule.TraineeID)
//INNER JOIN Measurement ON Schedule.ScheduleID = Measurement.ScheduleID);
// Using Navigational Properties to join 3 tables
var data = from Mes in db.Measurements
select new MeasurementScheduleTraineeDTO
{
TraineeID = Mes.Schedule.TraineeID,
MeasurementID = Mes.MeasurementID,
FirstName = Mes.Schedule.Trainee.FirstName,
ScheduleDtTime = Mes.Schedule.ScheduleDtTime,
Weight = Mes.Weight,
Waist = Mes.Waist,
BodyFat = Mes.BodyFat,
Chest = Mes.BodyFat,
UpperArm = Mes.UpperArm,
name = Mes.Schedule.Trainee.FirstName + " " + Mes.Schedule.Trainee.LastName
};
/*
* // Using explicit sql join to join 3 tables
* var data = from Tr in db.Trainees
* join Sc in db.Schedules
* on Tr.TraineeID equals Sc.TraineeID
* join Mes in db.Measurements
* on Sc.ScheduleID equals Mes.ScheduleID
* select new MeasurementScheduleTraineeDTO
* {
* MeasurementID = Mes.ScheduleID,
* ScheduleDtTime = Sc.ScheduleDtTime,
* FirstName = Tr.FirstName,
* Weight = Mes.Weight,
* Waist = Mes.Waist,
* BodyFat = Mes.BodyFat,
* Chest = Mes.Chest,
* UpperArm = Mes.UpperArm
* };
*/
return(data.ToList());
}
}
//Edit Measurement
public int EditMeasurement(Measurement editMes)
{
using (MySwoleMateEntities db = new MySwoleMateEntities())
{
var m = new Measurement {
MeasurementID = editMes.MeasurementID,
Weight = editMes.Weight,
Waist = editMes.Waist,
BodyFat = editMes.BodyFat,
Chest = editMes.Chest,
UpperArm = editMes.UpperArm,
ScheduleID = editMes.ScheduleID
};
db.Entry(m).State = System.Data.Entity.EntityState.Modified;
return(db.SaveChanges());
}
}
//Add a Measurement
public int AddMeasurement(Measurement newMes)
{
Measurement mes = new Measurement()
{
Weight = newMes.Weight,
Waist = newMes.Waist,
BodyFat = newMes.BodyFat,
Chest = newMes.Chest,
UpperArm = newMes.UpperArm,
ScheduleID = newMes.ScheduleID
};
using (MySwoleMateEntities db = new MySwoleMateEntities())
{
db.Measurements.Add(mes);
return(db.SaveChanges());
}
}
