?using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;


namespace ExamOnline
{
	//該源碼下載自www.51aspx.com(５１ａｓｐｘ．ｃｏｍ)

    /// <summary>
    /// 和試卷讀取相關的類
    /// </summary>
    public class Exam
    {
        string strcon = "";   //連接字符串

        /// <summary>
        /// 構造函數，連接字符串
        /// </summary>
        public Exam()   
        {
            if (strcon == "")
            {
                if (HttpContext.Current.Application["strcon"] == null)
                {
                    string path = HttpContext.Current.Request.PhysicalApplicationPath + "DBSet.ini";//獲取文件物理路徑
                    StreamReader sr = new StreamReader(path, System.Text.Encoding.Default);
                    strcon = sr.ReadLine();//讀取文件內容
                    HttpContext.Current.Application["strcon"] = strcon;
                }
                else
                {
                    strcon = HttpContext.Current.Application["strcon"].ToString();
                }
            }
        }  


        /// <summary>
        /// 取得當前可以考試的試卷ID
        /// </summary>
        /// <returns></returns>
        public int getCurrentPaperID()
        {
            SqlConnection con = new SqlConnection(strcon);
            string strcmdExam = "select * from testpaper_list where paper_time > @dtNow and test=1 and audit=1 order by paper_time asc ";
            SqlCommand cmdExam = new SqlCommand(strcmdExam, con);
            cmdExam.Parameters.Add("@dtNow", SqlDbType.DateTime).Value =(new Login()).dsDelay(30);//調用自定義函數dsDelay()
            con.Open();
            SqlDataReader drExam = cmdExam.ExecuteReader();
            if (drExam.Read())
            {
                return drExam.GetInt32(0);
            }
            else
            {
                return 0;
            }

        }

        /// <summary>
        /// 取得當前考試試卷的名稱
        /// </summary>
        /// <returns></returns>
        public string getCurrentPageTitle()
        {
            SqlConnection con = new SqlConnection(strcon);
            string strcmdExam = "select paper_name from testpaper_list where paper_id=@ID";
            SqlCommand cmdExam = new SqlCommand(strcmdExam,con);
            cmdExam.Parameters.Add("@ID",SqlDbType.Int).Value = getCurrentPaperID();
            con.Open();
            string strExamTitle = "信息讀取錯誤";
            SqlDataReader dr = cmdExam.ExecuteReader();
            if (dr.Read())
            {
                strExamTitle = dr.GetString(0);
                con.Close();
            }
            return strExamTitle;
        }

        public string getCurrentPageTitle(int paperid)
        {
            SqlConnection con = new SqlConnection(strcon);
            string strcmdExam = "select paper_name from testpaper_list where paper_id=@ID";
            SqlCommand cmdExam = new SqlCommand(strcmdExam, con);
            cmdExam.Parameters.Add("@ID", SqlDbType.Int).Value = paperid;
            con.Open();
            string strExamTitle = "信息讀取錯誤";
            SqlDataReader dr = cmdExam.ExecuteReader();
            if (dr.Read())
            {
                strExamTitle = dr.GetString(0);
                con.Close();
            }
            return strExamTitle;
        }

        /// <summary>
        /// 取得考生詳細信息，通過學號
        /// </summary>
        /// <param name="stu_id"></param>
        /// <returns></returns>
        public string getCurrentStudMessage(string stu_id)
        {
            SqlConnection con = new SqlConnection(strcon);
            string strcmdExam = "select name,sex,grade,major,class from students where stu_id = @ID";
            SqlCommand cmdExam = new SqlCommand(strcmdExam,con);
            cmdExam.Parameters.Add("@ID",SqlDbType.VarChar).Value = stu_id;
            con.Open();
            SqlDataReader drExam = cmdExam.ExecuteReader();
            if (drExam.Read())
            {
                string strName = drExam.GetString(0);
                string strSex = drExam.GetString(1);
                string strGrade = drExam.GetString(2);
                string strMajor = drExam.GetString(3);
                string strClass = drExam.GetString(4);
                string strAll = "學號：<u>" + stu_id + "</u>；姓名：<u>" + strName + "</u>；性別：<u>" + strSex + "</u>；年級：<u>" + strGrade + "</u>；專業：<u>" + strMajor + "</u>；班級：<u>" + strClass + "</u>；";
                drExam.Close();
                con.Close();
                return strAll;
            }
            else
            {
                con.Close();
                string strMessage = "數據讀取錯誤！";
                return strMessage;
            }

        }

        /// <summary>
        /// 通過試卷ID來統計該試卷的總題量
        /// </summary>
        /// <param name="paperID"></param>
        /// <returns></returns>
        public int getQuesNum(int paperID)
        {
            SqlConnection con = new SqlConnection(strcon);
            string strcmdExam = "select count(*) from testpaper where paper_id=@paperID";
            SqlCommand cmdExam = new SqlCommand(strcmdExam,con);
            cmdExam.Parameters.Add("@paperID",SqlDbType.Int).Value = paperID;
            con.Open();
            SqlDataReader drExam = cmdExam.ExecuteReader();
            if(drExam.Read())
            {
                int quesNum = drExam.GetInt32(0);
                con.Close();
                return quesNum;
            }
            con.Close();
            return 0;
        }

        /// <summary>
        /// 通過試卷ID來統計該試卷的總分
        /// </summary>
        /// <param name="paperID"></param>
        /// <returns></returns>
        public int getQuesScore(int paperID)
        {
            SqlConnection con = new SqlConnection(strcon);
            string strcmdExam = "select qscore from testpaper where paper_id=@paperID";
            SqlCommand cmdExam = new SqlCommand(strcmdExam, con);
            cmdExam.Parameters.Add("@paperID", SqlDbType.Int).Value = paperID;
            con.Open();
            SqlDataReader drExam = cmdExam.ExecuteReader();
            int scoreAll = 0;
            while (drExam.Read())
            {
                scoreAll += drExam.GetInt32(0);
            }
            con.Close();
            return scoreAll;
        }

        /// <summary>
        /// 通過試卷ID來取得試卷的題型
        /// </summary>
        /// <param name="paperID"></param>
        /// <returns></returns>
        public DataTable getStyles(int paperID)
        {
            SqlConnection con = new SqlConnection(strcon);
            string strcmdExam = "select distinct q.sid,s.sname from styles s left join questions q on q.sid = s.sid left join testpaper t on t.qid = q.qid where t.paper_id =@paperID order by q.sid asc";
            SqlCommand cmdExam = new SqlCommand(strcmdExam, con);
            cmdExam.Parameters.Add("@paperID", SqlDbType.Int).Value = paperID;
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmdExam);
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            da.Fill(ds, "table");
            dt = ds.Tables["table"];
            con.Close();
            return dt;
        }

        /// <summary>
        /// 通過試卷ID和題型ID來取得某一個題型題目table[q_num 題目順序,qid 題目ID,content題目內容] 
        /// </summary>
        /// <param name="paperID">試卷ID</param>
        /// <param name="sID">題型ID</param>
        /// <returns>返回該題型的題目datatable</returns>
        public DataTable getQues(int paperID,int sID)
        {
            SqlConnection con = new SqlConnection(strcon);
            string strcmdExam = "select tp.q_num,tp.qid,qs.content from testpaper tp left join questions qs on tp.qid=qs.qid where tp.paper_id=@paperID and qs.sid=@sID   order by tp.q_num";
            SqlCommand cmdExam = new SqlCommand(strcmdExam, con);
            cmdExam.Parameters.Add("@paperID", SqlDbType.Int).Value = paperID;
            cmdExam.Parameters.Add("@sID", SqlDbType.Int).Value = sID;
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmdExam);
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            da.Fill(ds, "table");
            dt = ds.Tables["table"];
            con.Close();
            return dt;
        }