?using System;
using System.Data;
using System.Collections;

using TQMS.DataAccessLayer;
using TQMS.DataAccessHelper;


namespace TQMS.BusinessLogicLayer
{
    /// <summary>
    /// Chapter 的摘要說明
    /// </summary>
    public class Chapter
    {
        #region 私有成員

        private int _chapterID;//科目編號
        private string _chapterName;//科目名稱
        private string _courseName;
        private int _courseID;

        private bool _exist;

        #endregion

        #region 屬性
        public int ChapterID
        {
            set
            {
                this._chapterID = value;
            }
            get
            {
                return this._chapterID;
            }
        }
        public string ChapterName
        {
            set
            {
                this._chapterName = value;
            }
            get
            {
                return this._chapterName;
            }
        }
        public string CourseName
        {
            set
            {
                this._courseName = value;
            }
            get
            {
                return this._courseName;
            }
        }

        public int CourseID
        {
            set
            {
                this._courseID = value;
            }
            get
            {
                return this._courseID;
            }
        }

        public bool Exist
        {
            set
            {
                this._exist = value;
            }
            get
            {
                return this._exist;
            }
        }


        #endregion

        #region 方法

        /// <summary>
        /// 根據參數chapterID，獲取科目詳細信息
        /// </summary>
        /// <param name="chapterID">科目ID</param>
        public void LoadData(int chapterID)
        {
            Database db = new Database();		//實例化一個Database類
            string sql = "";
            sql = "Select TQ_ChapterID,TQ_ChapterName ,p.TQ_CourseID,TQ_CourseName from [TQ_Chapters] p ,[TQ_Courses] c where p.TQ_CourseID=c.TQ_CourseID and TQ_ChapterID = " + chapterID;
            DataRow dr = db.GetDataRow(sql);	//利用Database類的GetDataRow方法查詢用戶數據
            //根據查詢得到的數據，對成員賦值
            if (dr != null)
            {
                this._chapterID = GetSafeData.ValidateDataRow_N(dr, "TQ_ChapterID");
                this._chapterName = GetSafeData.ValidateDataRow_S(dr, "TQ_ChapterName");
                this._courseName = GetSafeData.ValidateDataRow_S(dr, "TQ_CourseName");
                this._courseID = GetSafeData.ValidateDataRow_N(dr, "TQ_CourseID");
                this._exist = true;
            }
            else
            {
                this._exist = false;
            }
        }


        /// <summary>
        /// 添加一個科目信息
        /// </summary>
        /// <param name="newChapterInfo">科目信息哈希表</param>
        /// <returns >boll</returns>
        public bool Add(Hashtable ChapterInfo)
        {
            Database db = new Database();
            if (db.Insert("[TQ_Chapters]", ChapterInfo))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根據chapterID修改科目信息
        /// </summary>
        /// <param name="newChapterInfo">新的科目信息哈希表</param>
        /// <param name="condition">Update的Where子句</param>
        public bool Update(Hashtable newChapterInfo)
        {
            bool success = false;
            Database db = new Database();
            string condition = "Where TQ_ChapterID =" + this._chapterID;
            if (db.Update("[TQ_Chapters]", newChapterInfo, condition))
            {
                success = true;
            }
            return success;
        }

        /// <summary>
        /// 刪除科目信息
        /// </summary>
        public bool Delete()
        {
            bool success = false;
            Database db = new Database();
            string strSql = "Delete From [TQ_Chapters] Where TQ_ChapterID= " + this._chapterID;
            int i= db.ExecuteSQL(strSql);
            if (i != -1)
            {
                success = true;
            }
            return success;
        }
        /// <summary>
        /// 查詢科目信息
        /// </summary>
        /// <param name="queryItems">查詢條件哈希表</param>
        /// <returns>查詢結果集</returns>
        public static DataTable QueryChapter(Hashtable queryItems)
        {
            string where = SqlStringConstructor.GetConditionClause(queryItems);
          
            string sql = "Select * From [TQ_Chapters] " + where;

            Database db = new Database();
            return db.GetDataTable(sql);
        }
        public static DataTable QueryChapter(int courseID)
        {
            string sql = "Select * From [TQ_Chapters]   where TQ_CourseID="+courseID ;
            Database db = new Database();
            return db.GetDataTable(sql);
        }
      
        public static DataTable QueryAllChapter()
       {
           string sql = "Select TQ_ChapterID ,TQ_ChapterName,[TQ_Chapters].TQ_CourseID AS TQ_CourseID, TQ_CourseName  " +
            "from [TQ_Chapters]  ,[TQ_Courses]   where  [TQ_Chapters].TQ_CourseID = [TQ_Courses].TQ_CourseID ORDER BY  TQ_CourseName";

            Database db = new Database();
            return db.GetDataTable(sql);
        }
        public DataRow QueryChapterbyID(int chapterID)
        {
            string sql = "Select * From [TQ_Chapters]  where TQ_ChapterID=" + chapterID;

            Database db = new Database();
            return db.GetDataRow(sql);
        }

        #endregion

    }
}