?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>
    /// AutoExercise 的摘要說明:在線練習(xí)－－自動(dòng)組卷的功能實(shí)現(xiàn)
    /// </summary>
    public class AutoExercise
    {
        string strcon = "";   //連接字符串

        /// <summary>
        /// 構(gòu)造函數(shù)，連接字符串
        /// </summary>
        public AutoExercise()   
        {
            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();//讀取文件內(nèi)容
                    HttpContext.Current.Application["strcon"] = strcon;
                }
                else
                {
                    strcon = HttpContext.Current.Application["strcon"].ToString();
                }
            }
        }

        /// <summary>
        /// 用SQL語(yǔ)句綁定GridView
        /// </summary>
        /// <param name="gv">要綁定的GridView</param>
        /// <param name="strcmd">SQL語(yǔ)句</param>
        public void bindGV(GridView gv, string strcmd)
        {
            SqlDataSource sqlds = new SqlDataSource();
            sqlds.ConnectionString = strcon;
            sqlds.SelectCommandType = SqlDataSourceCommandType.Text;
            sqlds.SelectCommand = strcmd;
            gv.DataSource = sqlds;
            gv.DataBind();
        }

        /// <summary>
        /// 取得一門課程某題型難度的最大值
        /// </summary>
        /// <param name="cid"></param>
        /// <param name="sid"></param>
        /// <returns></returns>
        public int getQlevel(string cid, int sid)
        {
            SqlConnection con = new SqlConnection(strcon);
            string strcmd = "select max(qlevel) from questions where cid='" + cid + "' and sid='" + sid + "'";
            SqlCommand cmd = new SqlCommand(strcmd, con);
            con.Open();
            int heighLevel =Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
            return heighLevel;
        }

        /// <summary>
        /// 取得一門課程某題型的總題量
        /// </summary>
        /// <param name="cid"></param>
        /// <param name="sid"></param>
        /// <returns></returns>
        public int getQNum(string cid, int sid)
        {
            SqlConnection con = new SqlConnection(strcon);
            string strcmd = "select count(*) from questions where cid='" + cid + "' and sid='" + sid + "'";
            SqlCommand cmd = new SqlCommand(strcmd, con);
            con.Open();
            int qNum = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
            return qNum;
        }

        public string getStyleName(int sid)
        {
            SqlConnection con = new SqlConnection(strcon);
            string strcmd = "select sname from styles where sid='" + sid + "'";
            SqlCommand cmd = new SqlCommand(strcmd, con);
            con.Open();
            string sName = Convert.ToString(cmd.ExecuteScalar());
            con.Close();
            return sName;
        }

        /// <summary>
        /// 根據(jù)課程的cid來(lái)取得課程名稱
        /// </summary>
        /// <param name="cid"></param>
        /// <returns></returns>
        public string getCourseName(string cid)
        {
            SqlConnection con = new SqlConnection(strcon);
            string strcmd = "select cname from course where cid='" + cid + "'";
            SqlCommand cmd = new SqlCommand(strcmd, con);
            con.Open();
            string cname = Convert.ToString(cmd.ExecuteScalar());
            con.Close();
            return cname;
        }

        /// <summary>
        /// 根據(jù)qid取得判斷題、單選題的題目?jī)?nèi)容、選項(xiàng)、答案并填充
        /// </summary>
        /// <param name="rbl"></param>
        /// <param name="title"></param>
        /// <param name="answers"></param>
        /// <param name="qid"></param>
        public void FillRbl(RadioButtonList rbl,Literal title,Literal answers, int qid)
        {
            SqlConnection con = new SqlConnection(strcon);
            string strTitle = "select content from questions where qid='" + qid + "'"; 
            string strcmd = "select optionsname,options,answer from answers where qid='" + qid + "'";
            SqlCommand cmdTitle = new SqlCommand(strTitle, con);
            SqlCommand cmd = new SqlCommand(strcmd, con);
            con.Open();
            //取得題目?jī)?nèi)容
            title.Text = Convert.ToString(cmdTitle.ExecuteScalar());
            //取得選項(xiàng)和答案
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                ListItem li = new ListItem();
                li.Value = dr.GetString(0).ToString();
                li.Text = dr.GetString(0).ToString() + "、" + dr.GetString(1).ToString();
                rbl.Items.Add(li);
                if (dr.GetValue(2).ToString() == "1")
                {
                    answers.Text = dr.GetString(0).ToString();
                }
            }
            con.Close();
        }

        /// <summary>
        /// 根據(jù)qid取得多選題的題目?jī)?nèi)容、選項(xiàng)、答案并填充
        /// </summary>
        /// <param name="cbl"></param>
        /// <param name="title"></param>
        /// <param name="answers"></param>
        /// <param name="qid"></param>
        public void FillCbl(CheckBoxList cbl, Literal title, Literal answers, int qid)
        {
            string strAnswers = "";
            SqlConnection con = new SqlConnection(strcon);
            string strTitle = "select content from questions where qid='" + qid + "'";
            string strcmd = "select optionsname,options,answer from answers where qid='" + qid + "'";
            SqlCommand cmdTitle = new SqlCommand(strTitle, con);
            SqlCommand cmd = new SqlCommand(strcmd, con);
            con.Open();
            //取得題目?jī)?nèi)容
            title.Text = Convert.ToString(cmdTitle.ExecuteScalar());
            //取得選項(xiàng)和答案
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                ListItem li = new ListItem();
                li.Value = dr.GetString(0).ToString();
                li.Text = dr.GetString(0).ToString() + "、" + dr.GetString(1).ToString();
                cbl.Items.Add(li);
                if (dr.GetValue(2).ToString() == "1")
                {
                    if(strAnswers == "")
                        strAnswers = dr.GetString(0).ToString();
                    else
                        strAnswers += dr.GetString(0).ToString();
                }
            }
            answers.Text = strAnswers;
            con.Close();
        }


        /// <summary>
        /// 根據(jù)qid取得客觀題的題目?jī)?nèi)容、答案并填充
        /// </summary>
        /// <param name="title"></param>
        /// <param name="answer"></param>
        /// <param name="qid"></param>
        public void FillTb(Literal title, Literal answer, int qid)
        {
            SqlConnection con = new SqlConnection(strcon);
            string strTitle = "select content from questions where qid='" + qid + "'";
            string strAnswer = "select options from answers where qid='" + qid + "'";
            SqlCommand cmdTitle = new SqlCommand(strTitle, con);
            SqlCommand cmdAnswer = new SqlCommand(strAnswer, con);
            con.Open();
            title.Text = Convert.ToString(cmdTitle.ExecuteScalar());
            answer.Text = Convert.ToString(cmdAnswer.ExecuteScalar());
            con.Close();
        }



        /// <summary>
        /// 取得隨機(jī)題目
        /// </summary>
        /// <param name="cid"></param>
        /// <param name="styles"></param>
        /// <param name="level"></param>
        /// <param name="num"></param>
        /// <returns></returns>
        public DataTable dt(string cid, int styles, int level, int num)
        {
            DataTable dt = new DataTable("QuesTable");
            DataColumn q_num = dt.Columns.Add("q_num", typeof(Int32));
            q_num.AllowDBNull = false;
            q_num.Unique = true;

            dt.Columns.Add("qid", typeof(Int32));
            dt.Columns.Add("qlevel", typeof(Int32));

            //難度系數(shù)總的范圍  
            int heightLevel = getQlevel(cid,styles);
            int minLevel = num;
            int maxLevel = num * heightLevel;

            //第一次隨機(jī)讀取一道題
            SqlConnection con = new SqlConnection(strcon);
            con.Open();
            string strFirst = "select top 1 qid,qlevel from questions where cid='" + cid + "' and sid='" + styles + "' and audit=1 order by newid()";
            SqlCommand cmd = new SqlCommand(strFirst, con);
            SqlDataReader datareader = cmd.ExecuteReader();
            string strAllqid = "";         //保存已選取的題目qid
            int normalLevel = num * level;  //還剩下的難度系數(shù)總和
            while (datareader.Read())
            {
                int qid = datareader.GetInt32(0);
                int qlevel = datareader.GetInt32(1);
                DataRow drow = dt.NewRow();
                drow["q_num"] = 1;
                drow["qid"] = qid;
                drow["qlevel"] = qlevel;
                dt.Rows.Add(drow);
                strAllqid = qid.ToString();
                normalLevel -= qlevel;
            }
            datareader.Close();
            //取剩下的題目
            for (int i = 2; i <= num; i++)
            {
                DataRow drow = dt.NewRow();
                string strcmd = "";
                if (normalLevel <= (num-i))
                {
                    strcmd = "select top 1 qid,qlevel from questions where cid='" + cid + "' and sid='" + styles + "' and qlevel=1 and audit=1";
                    strcmd += " and qid not in(" + strAllqid + ") order by newid()";
                }
                else if (normalLevel >= (num-i) * heightLevel)
                {
                    strcmd = "select top 1 qid,qlevel from questions where cid='" + cid + "' and sid='" + styles + "'";
                    strcmd += " and qlevel=" + heightLevel + " and audit=1";
                    strcmd += " and qid not in(" + strAllqid + ") order by newid()";
                }
                else
                {
                    strcmd = "select top 1 qid,qlevel from questions where cid='" + cid + "' and sid='" + styles + "' and audit=1";
                    strcmd += " and qid not in(" + strAllqid + ") order by newid()";
                }

                SqlCommand cmdLeaveQues = new SqlCommand(strcmd, con);
                SqlDataReader drLeavelQues = cmdLeaveQues.ExecuteReader();
                while (drLeavelQues.Read())
                {
                    int qid = drLeavelQues.GetInt32(0);
                    int qlevel = drLeavelQues.GetInt32(1);
                    drow["q_num"] = i;
                    drow["qid"] = qid;
                    drow["qlevel"] = qlevel;
                    dt.Rows.Add(drow);
                    strAllqid += "," + qid.ToString();
                    normalLevel -= qlevel;
                }
                drLeavelQues.Close();

            }
            con.Close();
            return dt;
        }

    }
}