autocoursedal.cs

guan yu pai ke xi tong de ruan jian

                        classe.ClassCode = reader.GetString(1);

                        list.Add(classe);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return list;
        }
        #endregion

        #region 获得空闲教学设施列表
        /// <summary>
        /// 获得空闲教学设施列表
        /// </summary>
        /// <param name="timeId">教学时间</param>
        /// <param name="weekId">周信息</param>
        /// <returns>教学设施列表</returns>
        public IList<ClassRoom> GetIdleRooms(int timeId, int weekId)
        {
            string sql = "SELECT RoomId, Title "
                       + " FROM ClassRooms "
                       + " WHERE RoomId NOT IN "
                       + " (SELECT RoomId FROM WeekCourses WHERE TimeId=@TimeId AND WeekId=@WeekId) AND TypeId = 1";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@TimeId", timeId),
                new SqlParameter("@WeekId", weekId)
            };

            IList<ClassRoom> list = new List<ClassRoom>();

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    while (reader.Read())
                    {
                        ClassRoom classRoom = new ClassRoom();

                        classRoom.RoomId = reader.GetInt32(0);
                        classRoom.Title = reader.GetString(1);

                        list.Add(classRoom);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return list;
        }
        #endregion

        #region 获得指定班级在某天上课的周课表信息
        /// <summary>
        /// 获得指定班级在某天上课的周课表信息
        /// </summary>
        /// <param name="classCode">班级</param>
        /// <param name="weekId">星期</param>
        /// <returns>true=有, false=无.</returns>
        public bool GetWeekCoursesClassCode(string classCode, int weekId)
        {
            string sql = "SELECT ClassCode FROM WeekCourses WHERE ClassCode=@ClassCode AND WeekId=@WeekId";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@ClassCode", classCode),
                new SqlParameter("@WeekId", weekId)
            };

            bool flg = false;

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    if (reader.Read())
                    {
                        flg = true;
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return flg;
        }
        #endregion

        #region 获取某教员当天的空闲时间
        /// <summary>
        /// 获取某教员当天的空闲时间
        /// </summary>
        /// <param name="name">教员名称</param>
        /// <param name="timeId">时间段</param>
        /// <param name="weekId">星期</param>
        /// <returns>true=有, false=无.</returns>
        public bool GetIdleTeacher(string name, int timeId, int weekId)
        {
            string sql = " SELECT [Name],W.ClassCode, W.TimeId, W.WeekId, W.RoomId "
                       + " FROM Teachers "
                       + " INNER JOIN Teachings AS T "
                       + " ON Teachers.TeacherId = T.TeacherId "
                       + " INNER JOIN WeekCourses AS W "
                       + " ON W.ClassCode = T.ClassCode "
                       + " WHERE [Name] = @Name AND W.TimeId = @TimeId AND WeekId = @WeekId ";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@Name", name),
                new SqlParameter("@TimeId", timeId),
                new SqlParameter("@WeekId", weekId)
            };

            bool flg = false;

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    if (reader.Read())
                    {
                        flg = true;
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return flg;
        }
        #endregion

        #region 添加周信息
        /// <summary>
        /// 添加周信息
        /// </summary>
        /// <param name="classCode">班级</param>
        /// <param name="timeId">时间段</param>
        /// <param name="weekId">星期</param>
        /// <param name="roomId">教学设施编号</param>
        /// <returns>true = 成功, false = 失败.</returns>
        public bool Create(string classCode, int timeId, int weekId, int roomId)
        {
            string sql = "INSERT INTO WeekCourses(ClassCode, TimeId, WeekId, RoomId) VALUES(@ClassCode, @TimeId, @WeekId, @RoomId)";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@ClassCode", classCode),
                new SqlParameter("@TimeId", timeId),
                new SqlParameter("@WeekId", weekId),
                new SqlParameter("@RoomId", roomId)
            };

            int rows = 0;

            try
            {
                rows = SQLDBHelper.ExecuteNonQuery(sql, parms);
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return (rows > 0) ? true : false;
        }
        #endregion

        #region 删除周信息
        /// <summary>
        /// 删除周信息
        /// </summary>
        /// <returns>true = 成功, false = 失败.</returns>
        public bool Delete()
        {
            string sql = "DELETE FROM WeekCourses";

            int rows = 0;

            try
            {
                rows = SQLDBHelper.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return (rows > 0) ? true : false;
        }
        #endregion

        #region 获得周课表列表
        /// <summary>
        /// 获得周课表列表
        /// </summary>
        /// <returns>周课表列表</returns>
        public IList<WeekCourse> GetWeekCourses()
        {
            string sql = "SELECT ClassCode, TimeId, WeekId, RoomId FROM WeekCourses";

            IList<WeekCourse> list = new List<WeekCourse>();

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        WeekCourse weekCourse = new WeekCourse();

                        weekCourse.Classe.ClassCode = reader.GetString(0);

                        int timeId = reader.GetInt32(1);
                        weekCourse.TeachingTime = new AutoCourseDAL().GetTeachingTime(timeId);


                        int weekId = reader.GetInt32(2);
                        weekCourse.Week = new AutoCourseDAL().GetWeek(weekId);

                        int roomId = reader.GetInt32(3);
                        weekCourse.ClassRoom = new ClassRoomManagerDAL().GetClassRoom(roomId);

                        list.Add(weekCourse);
                    }
                    reader.Close();
                }

                //DataTable dt = SQLDBHelper.GetDataTable(sql, null);
                //for (int i = 0; i < dt.Rows.Count; i++)
                //{
                //    DataRow row = dt.Rows[i];
                //    WeekCourse weekCourse = new WeekCourse();

                //    weekCourse.Classe.ClassCode = row[0].ToString();

                //    int timeId = Int32.Parse(row[1].ToString());
                //    weekCourse.TeachingTime = new AutoCourseDAL().GetTeachingTime(timeId);


                //    int weekId = Int32.Parse(row[2].ToString());
                //    weekCourse.Week = new AutoCourseDAL().GetWeek(weekId);

                //    //int roomId = Int32.Parse(row[3].ToString());
                //    //weekCourse.ClassRoom = new ClassRoomManagerDAL().GetClassRoom(roomId);

                //    list.Add(weekCourse);
                //}
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return list;
        }
        #endregion

        #region 获得指定班级的周课表信息
        /// <summary>
        /// 获得指定班级的周课表信息
        /// </summary>
        /// <param name="classCode">班级</param>
        /// <returns>某班级的周课表</returns>
        public IList<WeekCourse> GetWeekCourse(string classCode)
        {
            string sql = "SELECT ClassCode, TimeId, WeekId, RoomId FROM WeekCourses WHERE ClassCode=@ClassCode ORDER BY WeekId ";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@ClassCode", classCode )
            };

            IList<WeekCourse> list = new List<WeekCourse>();

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    while (reader.Read())
                    {
                        WeekCourse weekCourse = new WeekCourse();

                        weekCourse.Classe.ClassCode = reader.GetString(0);

                        int timeId = reader.GetInt32(1);
                        weekCourse.TeachingTime = new AutoCourseDAL().GetTeachingTime(timeId);


                        int weekId = reader.GetInt32(2);
                        weekCourse.Week = new AutoCourseDAL().GetWeek(weekId);

                        int roomId = reader.GetInt32(3);
                        weekCourse.ClassRoom = new ClassRoomManagerDAL().GetClassRoom(roomId);

                        list.Add(weekCourse);
                    }

                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return list;
        }
        #endregion
    }
}