// QueryScoreDlg.cpp : implementation file
//

#include "stdafx.h"
#include "StudentScore.h"
#include "QueryScoreDlg.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

/////////////////////////////////////////////////////////////////////////////
// CQueryScoreDlg dialog


CQueryScoreDlg::CQueryScoreDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CQueryScoreDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CQueryScoreDlg)
	//}}AFX_DATA_INIT
}


void CQueryScoreDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CQueryScoreDlg)
	DDX_Control(pDX, IDC_LIST_QUERY_SCORE, m_list);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CQueryScoreDlg, CDialog)
	//{{AFX_MSG_MAP(CQueryScoreDlg)
	ON_BN_CLICKED(IDC_RADIO_BYSTUDENT, OnRadioBystudent)
	ON_BN_CLICKED(IDC_RADIO_BYCOURSE, OnRadioBycourse)
	ON_BN_CLICKED(ID_SCORE_QUERY, OnScoreQuery)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CQueryScoreDlg message handlers

BOOL CQueryScoreDlg::OnInitDialog()
{
	CDialog::OnInitDialog();
	//如果數據庫未打開，則打開數據庫
	if(!m_database.IsOpen())
	{
		m_database.Open(_T("studentscore"));
	    m_recordSet.m_pDatabase=&m_database;
	}
	//用來標識CRadio
	sortBy=0;

  return true;
}

void CQueryScoreDlg::OnRadioBystudent() //點擊按學生單選按鈕事件
{
	sortBy=1;
}

void CQueryScoreDlg::OnRadioBycourse() //點擊按課程單選按鈕事件
{
	sortBy=2;
}

void CQueryScoreDlg::OnScoreQuery() //查詢按鈕事件　
{
	BOOL bSuccess=true;
	if(sortBy==0)//如果未選中單選按鈕
	{
		MessageBox("請先選擇查詢條件！");
	}
	if(sortBy==1)//選中按學生單選按鈕
	{
		//獲得控件
		CEdit* pName=(CEdit*) this->GetDlgItem(IDC_QUERY_NAME);
		CEdit* pScore=(CEdit*) this->GetDlgItem(IDC_QUERY_SCORE);
		CComboBox* pFilter=(CComboBox*) this->GetDlgItem(IDC_COMBO_FILTER);
		//將控件上的值賦給字符串
		CString m_name,m_score,m_filter;
		pName->GetWindowText(m_name);
		pScore->GetWindowText(m_score);
        pFilter->GetWindowText(m_filter);
		if(m_name=="")//如果姓名為空
		{
			MessageBox("請輸入學生姓名");
			bSuccess=false;
		}
		//判斷條件
		if(m_filter=="大于")
		{
			m_filter=">";
		}else if(m_filter=="等于")
		{
			m_filter="=";
		}else if(m_filter=="小于")
		{
			m_filter="<";
		}
		if(bSuccess)
		{
			//組織查詢語句
			CString strSQL;
			if(m_score==""||m_filter=="")
				strSQL.Format("select * from score,course where student_no in (select student_no from student where student_name='%s') and course.course_no=score.course_no and score.active_status='Y'",m_name);
			else 
                strSQL.Format("select * from score,course where student_no in (select student_no from student where student_name='%s') and course.course_no=score.course_no and score%s%s and score.active_status='Y'",m_name,m_filter,m_score);
			//清空list數據
			m_list.DeleteColumn(1);
			m_list.DeleteColumn(0);
			m_list.DeleteAllItems();
			m_list.InsertColumn(0,"課程名");
			m_list.InsertColumn(1,"學生成績");
			//設置header的寬度
			RECT rectList;
			m_list.GetWindowRect(&rectList);
			int wid=rectList.right-rectList.left-4;
			for(int i=0;i<2;i++)
				m_list.SetColumnWidth(i,wid/2);
			m_list.SetExtendedStyle(LVS_EX_FULLROWSELECT);
			//打開記錄集
			m_recordSet.Open(CRecordset::forwardOnly,strSQL);
			//將記錄集顯示到 CList中
			for(int j=0;j<m_recordSet.GetRecordCount();j++){
				CString temp;
				m_recordSet.GetFieldValue("course_name",temp);
				m_list.InsertItem(j,temp);
				m_recordSet.GetFieldValue("score",temp);
				m_list.SetItemText(j,1,temp);
				m_recordSet.MoveNext();
			}
			//將關閉記錄集
			m_recordSet.Close();			
		}		
	
	}
	if(sortBy==2)//選中按課程單選按鈕
	{
		//獲得控件并賦值
		CEdit* pCourse=(CEdit*) this->GetDlgItem(IDC_QUERY_COURSE);
		CString m_course;
		pCourse->GetWindowText(m_course);
		//如果課程名為空，提示信息
		if(m_course=="")
		{
			MessageBox("請輸入課程名稱");
		}
		else
		{
			//組織查詢語句
			CString strSQL;
            strSQL.Format("select * from student,score where student.student_no=score.student_no and course_no in (select course_no from course where course_name='%s') and score.active_status='Y'",m_course);	
			//清空list數據
			m_list.DeleteColumn(1);
            m_list.DeleteColumn(0);
			m_list.DeleteAllItems();
			m_list.InsertColumn(0," 學生名");
			m_list.InsertColumn(1,"學生成績");
            
			//設置header的寬度
			RECT rectList;
			m_list.GetWindowRect(&rectList);
			int wid=rectList.right-rectList.left-4;
			for(int i=0;i<2;i++)
				m_list.SetColumnWidth(i,wid/2);
			m_list.SetExtendedStyle(LVS_EX_FULLROWSELECT);
			//打開記錄集
			m_recordSet.Open(CRecordset::forwardOnly,strSQL);
			//將記錄集顯示到 CList中
			for(int j=0;j<m_recordSet.GetRecordCount();j++){
				CString temp;
				m_recordSet.GetFieldValue("student_name",temp);
				m_list.InsertItem(j,temp);
				m_recordSet.GetFieldValue("score",temp);
				m_list.SetItemText(j,1,temp);
				m_recordSet.MoveNext();
			}
			//關閉記錄集
			m_recordSet.Close();		
		}
	}
	
}