/*
 * 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 java.text.*;

import javax.servlet.http.HttpSession;

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

/**
 * 針對答題歷史記錄的數據處理類
 * @author ShenYK
 * @version 1.0
 */
public class DRecord extends DCommon
{
    //更新用戶考試記錄，并計算用戶名次
    public int registerRecord( String sUsername, String sCategoryId, int iRecord, Vector allQuestions )
        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語句生成最大的recordId
            String sRecordId = "";
            String sQuery = "select max(record_id) from test_record";
            rs = stmt.executeQuery( sQuery );
            rs.next();
            String sCurrentMaxId = rs.getString(1);
            //當前是第一次登錄
            if ( sCurrentMaxId == null )
            {
                sRecordId = "0000000001";
            }
            else
            {
                int iMaxCd = Integer.parseInt(sCurrentMaxId);
                sRecordId = String.valueOf(iMaxCd+1);
                int iLength = sRecordId.length();
                for(int i=10; i>iLength; i--)
                {
                    sRecordId = "0" + sRecordId;
                }
            }
            
            //得到當前系統時間
            String sDate = (new SimpleDateFormat("yyyyMMddHHmmss")).format(new Date(System.currentTimeMillis()));
            
            //執行SQL語句，插入數據庫
            String sUpdateQuery = "insert into test_record set record_id='" + sRecordId 
                                + "', username = '" + sUsername
                                + "', category_id = '" + sCategoryId
                                + "', test_time = '" + sDate
                                + "', test_result=" + iRecord
                                + ", use_time=0";
            stmt.executeUpdate( sUpdateQuery );
            
            //通過檢索得出用戶當前的名次
            sQuery = "select count(*) from test_record where category_id='" + sCategoryId 
                   + "' and test_result>" + iRecord ;
            rs = stmt.executeQuery( sQuery );
            rs.next();
            int iOrder = rs.getInt(1);

            return iOrder+1;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                rs.close();
                stmt.close();
                conn.close();
            }catch(Exception ex)
            {
            }
        }
    }
    
    //得到某一用戶在某一分類下的所有記錄
    public Vector getAllRecords( String sUsername, String sCategoryId )
        throws Exception
    {
        //獲得數據庫連接
        Connection conn = this.getDBConnection();
        if ( conn == null )
        {
            throw new Exception("數據庫連接獲得失敗！");
        }
        Statement stmt = null;
        ResultSet rs = null;
        
        try
        {
            Vector vRecords = new Vector();
            stmt = conn.createStatement();
            
            //執行SQL語句
            String sQuery = "select * from test_record where username='" + sUsername 
                          + "' and category_id = '" + sCategoryId + "'";
            rs = stmt.executeQuery( sQuery );
            while( rs.next() )
            {
                TestRecord testRecordObj = new TestRecord(rs);
                vRecords.add( testRecordObj );
            }
            
            return vRecords;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                rs.close();
                stmt.close();
                conn.close();
            }catch(Exception ex)
            {
            }
        }
    }
    
    //得到某一分類下的所有記錄
    public Vector getAllRecords( String sCategoryId )
        throws Exception
    {
        //獲得數據庫連接
        Connection conn = this.getDBConnection();
        if ( conn == null )
        {
            throw new Exception("數據庫連接獲得失敗！");
        }
        Statement stmt = null;
        ResultSet rs = null;
        
        try
        {
            Vector vRecords = new Vector();
            stmt = conn.createStatement();
            
            //執行SQL語句
            String sQuery = "select * from test_record where category_id = '" + sCategoryId + "'";
            rs = stmt.executeQuery( sQuery );
            while( rs.next() )
            {
                TestRecord testRecordObj = new TestRecord(rs);
                vRecords.add( testRecordObj );
            }
            
            return vRecords;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                rs.close();
                stmt.close();
                conn.close();
            }catch(Exception ex)
            {
            }
        }
    }
}