Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        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);
        }
Esempio n. 3
0
        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);
        }
Esempio n. 4
0
        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);
            }
        }