/*
 * This product includes software developed by the
 * Apache Software Foundation (http://www.apache.org/).
 */
package ch07.database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

import javax.servlet.http.HttpSession;

import ch07.*;
import ch07.object.unit.*;

/**
 * 針對問題信息的數據處理類
 * @author ShenYK
 * @version 1.0
 */
public class DQuestion extends DCommon
{
    //隨機獲得該分類指定數目的題目
    public Vector getRandomQuestions( String sUsername, String sCategoryId, int iNumber )
        throws Exception
    {
        //獲得數據庫連接
        Connection conn = this.getDBConnection();
        if ( conn == null )
        {
            throw new Exception("數據庫連接獲得失敗！");
        }
        Statement stmt = null;
        ResultSet rs = null;
        
        try
        {
            Vector vQuestions = new Vector();
            stmt = conn.createStatement();
            //執行SQL語句，找出所有可用的題目ID
            String sQuery = "select * from question where category_id='" + sCategoryId 
                          + "' order by rand() limit 0," + iNumber;
            rs = stmt.executeQuery( sQuery );
            while ( rs.next() )
            {
                Question objQuestion = new Question(rs);
                vQuestions.add( objQuestion );
            }
            return vQuestions;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                rs.close();
                stmt.close();
                conn.close();
            }catch(Exception ex)
            {
            }
        }
    }
    
    //獲得指定分類的題目
    public Vector getAllQuestionsByCategoryId(  String sCategoryId )
        throws Exception
    {
        //獲得數據庫連接
        Connection conn = this.getDBConnection();
        if ( conn == null )
        {
            throw new Exception("數據庫連接獲得失敗！");
        }
        Statement stmt = null;
        ResultSet rs = null;
        
        try
        {
            Vector vQuestions = new Vector();
            stmt = conn.createStatement();
            //執行SQL語句，找出所有可用的題目ID
            String sQuery = "select * from question where category_id='" + sCategoryId 
                          + "' order by question_id";
            rs = stmt.executeQuery( sQuery );
            while ( rs.next() )
            {
                Question objQuestion = new Question(rs);
                vQuestions.add( objQuestion );
            }
            return vQuestions;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                rs.close();
                stmt.close();
                conn.close();
            }catch(Exception ex)
            {
            }
        }
    }
    
    //刪除指定的題目
    public void deleteQuestionById(  String sQuestionId )
        throws Exception
    {
        //獲得數據庫連接
        Connection conn = this.getDBConnection();
        if ( conn == null )
        {
            throw new Exception("數據庫連接獲得失敗！");
        }
        Statement stmt = null;
        
        try
        {
            stmt = conn.createStatement();
            //執行SQL語句，刪除ID
            String sUpdateQuery = "delete from question where question_id='" + sQuestionId + "'";
            stmt.executeUpdate( sUpdateQuery );
            return;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                stmt.close();
                conn.close();
            }catch(Exception ex)
            {
            }
        }
    }
    
    //獲得指定ID的題目詳細信息
    public Question getQuestionById(  String sQuestionId )
        throws Exception
    {
        //獲得數據庫連接
        Connection conn = this.getDBConnection();
        if ( conn == null )
        {
            throw new Exception("數據庫連接獲得失敗！");
        }
        Statement stmt = null;
        ResultSet rs = null;
        
        try
        {
            stmt = conn.createStatement();
            //執行SQL語句，找出所有可用的題目ID
            String sQuery = "select * from question where question_id='" + sQuestionId + "'";
            rs = stmt.executeQuery( sQuery );
            if ( rs.next() )
            {
                Question objQuestion = new Question(rs);
                return objQuestion;
            }
            return null;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                rs.close();
                stmt.close();
                conn.close();
            }catch(Exception ex)
            {
            }
        }
    }
    
    //修改指定的題目
    public void modifyQuestion ( Question questionObj )
        throws Exception
    {
        //獲得數據庫連接
        Connection conn = this.getDBConnection();
        if ( conn == null )
        {
            throw new Exception("數據庫連接獲得失敗！");
        }
        Statement stmt = null;
        
        try
        {
            stmt = conn.createStatement();
            //執行SQL語句，修改題目
            String sUpdateQuery = "update question set "
                                + "subject = '" + questionObj.getSubject() + "', "
                                + "choice_a = '" + questionObj.getChoiceA() + "', "
                                + "choice_b = '" + questionObj.getChoiceB() + "', "
                                + "choice_c = '" + questionObj.getChoiceC() + "', "
                                + "choice_d = '" + questionObj.getChoiceD() + "', "
                                + "answer = '" + questionObj.getAnswer() + "' "
                                + "where question_id = '" + questionObj.getQuestionId() + "'";
            stmt.executeUpdate( sUpdateQuery );
            return;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                stmt.close();
                conn.close();
            }catch(Exception ex)
            {
            }
        }
    }
    
    //添加一個新試題
    public void addQuestion ( Question questionObj )
        throws Exception
    {
        //獲得數據庫連接
        Connection conn = this.getDBConnection();
        if ( conn == null )
        {
            throw new Exception("數據庫連接獲得失敗！");
        }
        Statement stmt = null;
        ResultSet rs = null;
        try
        {
            stmt = conn.createStatement();
            
            //執行SQL語句生成最大的recordId
            String sQuestionId = "";
            String sQuery = "select max(question_id) from question";
            rs = stmt.executeQuery( sQuery );
            rs.next();
            String sCurrentMaxId = rs.getString(1);
            //當前是第一次登錄
            if ( sCurrentMaxId == null )
            {
                sQuestionId = "0000000001";
            }
            else
            {
                int iMaxCd = Integer.parseInt(sCurrentMaxId);
                sQuestionId = String.valueOf(iMaxCd+1);
                int iLength = sQuestionId.length();
                for(int i=10; i>iLength; i--)
                {
                    sQuestionId = "0" + sQuestionId;
                }
            }
            //執行SQL語句，修改題目
            String sUpdateQuery = "insert into question set "
                                + "question_id = '" + sQuestionId + "', "
                                + "category_id = '" + questionObj.getCategoryId() + "', "
                                + "difficulty = '" + questionObj.getDifficulty() + "', "
                                + "subject = '" + questionObj.getSubject() + "', "
                                + "choice_a = '" + questionObj.getChoiceA() + "', "
                                + "choice_b = '" + questionObj.getChoiceB() + "', "
                                + "choice_c = '" + questionObj.getChoiceC() + "', "
                                + "choice_d = '" + questionObj.getChoiceD() + "', "
                                + "answer = '" + questionObj.getAnswer() + "'";
            stmt.executeUpdate( sUpdateQuery );
            return;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                rs.close();
                stmt.close();
                conn.close();
            }catch(Exception ex)
            {
            }
        }
    }
}