public List <int> getUnAvailableEmployees(int trainingId, DateTime desiredStartDate, DateTime desiredEndDate)
{
List <int> employeesList = new Training().getAssignedEmployeesForTraining(trainingId);
List <int> unAvailableList = new List <int>();
foreach (int id in employeesList)
{
connection();
SqlCommand cmd = new SqlCommand("isEmployeeAvailableOnDate", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@employeeId", id);
cmd.Parameters.AddWithValue("@start_date", desiredStartDate);
cmd.Parameters.AddWithValue("@end_date", desiredEndDate);
cmd.Parameters.Add("@count", SqlDbType.TinyInt).Direction = ParameterDirection.Output;
con.Open();
cmd.ExecuteNonQuery();
int count = Convert.ToInt32(cmd.Parameters["@count"].Value);
if (count > 0)
{
unAvailableList.Add(id);
}
}
return(unAvailableList);
}
public List <Training> viewListOfTraining()
{
List <Training> trainingList = new List <Training>();
List <int> trainingId = new List <int>();
connection();
SqlCommand cmd = new SqlCommand("viewListOfTraining", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sd = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
con.Open();
sd.Fill(dt);
con.Close();
foreach (DataRow dr in dt.Rows)
{
//int id = Convert.ToInt32(dr["training_id"]);
//trainingId.Add(id);
Training training = new Training();
training.TrainingId = Convert.ToInt32(dr["training_id"]);
training.TrainingName = Convert.ToString(dr["name"]);
training.StartDate = Convert.ToDateTime(dr["start_date"]).Date;
training.EndDate = Convert.ToDateTime(dr["end_date"]).Date;
training.Location = Convert.ToString(dr["location"]);
training.ParticipationsNum = Convert.ToInt32(dr["number_of_participants"]);
training.HoursPerDay = Convert.ToInt32(dr["hours_per_day"]);
training.SkillId = Convert.ToInt32(dr["skill_id"]);
if (DBNull.Value.Equals(dr["maxRank"]))
{
training.MaxRank = 0;
}
else
{
training.MaxRank = Convert.ToInt32(dr["maxRank"]);
}
if (DBNull.Value.Equals(dr["positionId"]))
{
training.PositionId = 0;
}
else
{
training.PositionId = Convert.ToInt32(dr["positionId"]);
}
if (DBNull.Value.Equals(dr["departmentId"]))
{
training.DepartmentId = 0;
}
else
{
training.DepartmentId = Convert.ToInt32(dr["departmentId"]);
}
training.MaxNumOfParticipants = Convert.ToInt32(dr["max_number_of_participants"]);
trainingList.Add(training);
}
/* foreach(int id in trainingId)
* {
* Training training = viewTraining(id);
* trainingList.Add(training);
* }*/
return(trainingList);
}
public Training viewTraining(int trainingId)
{
Training training = new Training();
connection();
SqlCommand cmd = new SqlCommand("viewTraining", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@trainingId", trainingId);
SqlDataAdapter sd = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
con.Open();
sd.Fill(dt);
con.Close();
foreach (DataRow dr in dt.Rows)
{
training.TrainingId = Convert.ToInt32(dr["training_id"]);
training.TrainingName = Convert.ToString(dr["name"]);
if (DBNull.Value.Equals(dr["start_date"]))
{
training.StartDate = new DateTime();
}
else
{
training.StartDate = Convert.ToDateTime(dr["start_date"]).Date;
}
if (DBNull.Value.Equals(dr["end_date"]))
{
training.EndDate = new DateTime();
}
else
{
training.EndDate = Convert.ToDateTime(dr["end_date"]).Date;
}
training.Location = Convert.ToString(dr["location"]);
training.ParticipationsNum = Convert.ToInt32(dr["number_of_participants"]);
training.HoursPerDay = Convert.ToInt32(dr["hours_per_day"]);
training.SkillId = Convert.ToInt32(dr["skill_id"]);
if (DBNull.Value.Equals(dr["maxRank"]))
{
training.MaxRank = 0;
}
else
{
training.MaxRank = Convert.ToInt32(dr["maxRank"]);
}
if (DBNull.Value.Equals(dr["positionId"]))
{
training.PositionId = 0;
}
else
{
training.PositionId = Convert.ToInt32(dr["positionId"]);
}
if (DBNull.Value.Equals(dr["departmentId"]))
{
training.DepartmentId = 0;
}
else
{
training.DepartmentId = Convert.ToInt32(dr["departmentId"]);
}
training.MaxNumOfParticipants = Convert.ToInt32(dr["max_number_of_participants"]);
}
return(training);
}
public int assignTrainingToEmloyee(int trainingId, int employeeId)
{
if (checkEmptyTrainingPlace(trainingId) == 1)
{
Training training = new Training();
DateTime date = training.getTrainingStartDate(trainingId);
// if date is not set yet assign employees without constrains
if (date == DateTime.MinValue)
{
connection();
SqlCommand cmd = new SqlCommand("assignTraining", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@trainingId", trainingId);
cmd.Parameters.AddWithValue("@employeeId", employeeId);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
// assigned successfully
return(1);
}
// if date is set check first if employees available
else
{
connection();
SqlCommand cmd = new SqlCommand("isEmployeeAvailableOnDate", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@employeeId", employeeId);
string start_date = date.Date.ToString("MM/dd/yyyy");
cmd.Parameters.AddWithValue("@start_date", start_date);
DateTime end = training.getTrainingEndDate(trainingId);
string end_date = end.Date.ToString("MM/dd/yyyy");
cmd.Parameters.AddWithValue("@end_date", end_date);
cmd.Parameters.Add("@count", SqlDbType.Int).Direction = ParameterDirection.Output;
con.Open();
cmd.ExecuteNonQuery();
int count = 0;
count = Convert.ToInt32(cmd.Parameters["@count"].Value);
if (count > 0)
{
// employee is not available at the training time
return(0);
}
// employee available
else
{
//assign
connection();
SqlCommand cmd_1 = new SqlCommand("assignTraining", con);
cmd_1.CommandType = CommandType.StoredProcedure;
cmd_1.Parameters.AddWithValue("@trainingId", trainingId);
cmd_1.Parameters.AddWithValue("@employeeId", employeeId);
con.Open();
cmd_1.ExecuteNonQuery();
con.Close();
// assigned successfully
return(1);
}
}
}
else
{
return(2);
}
}