subform_4.cpp

考试管理系统

// SubForm_4.cpp : implementation file
//

#include "stdafx.h"
#include "Exam.h"
#include "SubForm_4.h"
#include "ExamDoc.h"
#include "ExamView.h"

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

/////////////////////////////////////////////////////////////////////////////
// CSubForm_4 dialog


CSubForm_4::CSubForm_4(CWnd* pParent /*=NULL*/)
	: CDialog(CSubForm_4::IDD, pParent)
{
	//{{AFX_DATA_INIT(CSubForm_4)
	m_nNum1 = 0;
	m_sGrade = _T("");
	m_sClass = _T("");
	m_fAvg = 0.0f;
	m_nNum2 = 0;
	m_nNum3 = 0;
	//}}AFX_DATA_INIT
}


void CSubForm_4::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CSubForm_4)
	DDX_Control(pDX, IDC_COURSE, m_ctrlCourse);
	DDX_Control(pDX, IDC_RESULT, m_ctrlResult);
	DDX_Text(pDX, IDC_NUM1, m_nNum1);
	DDX_CBString(pDX, IDC_GRADE, m_sGrade);
	DDX_CBString(pDX, IDC_CLASS, m_sClass);
	DDX_Text(pDX, IDC_AVG, m_fAvg);
	DDX_Text(pDX, IDC_NUM2, m_nNum2);
	DDX_Text(pDX, IDC_NUM3, m_nNum3);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CSubForm_4, CDialog)
	//{{AFX_MSG_MAP(CSubForm_4)
	ON_CBN_SELCHANGE(IDC_COURSE, OnSelchangeCourse)
	ON_BN_CLICKED(IDC_VIEW1, OnView1)
	ON_BN_CLICKED(IDC_VIEW2, OnView2)
	ON_BN_CLICKED(IDC_VIEW3, OnView3)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CSubForm_4 message handlers

BOOL CSubForm_4::OnInitDialog() 
{
	CDialog::OnInitDialog();
	
	// 设置列表框的扩展风格
	DWORD dwExStyle = LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES | LVS_EX_HEADERDRAGDROP | LVS_EX_TRACKSELECT;
	m_ctrlResult.SetExtendedStyle(LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES);

	LV_COLUMN lvColumn;
	lvColumn.mask = LVCF_FMT | LVCF_WIDTH | LVCF_TEXT | LVCF_SUBITEM;
	lvColumn.fmt = LVCFMT_LEFT;
	lvColumn.cx = 67;

	// 对各列进行处理
	CString sField[5] = {"考号", "姓名", "年级", "班级", "成绩"};
	for (int i = 0; i < 5; i++)
	{    
		// 得到并插入字段名
		int len = sField[i].GetLength();
		CString temp = sField[i];
		TCHAR* szBuffer = new TCHAR[len + 1];
		strcpy(szBuffer, temp.GetBuffer(len));
		temp.ReleaseBuffer();
		lvColumn.pszText = szBuffer;
	    m_ctrlResult.InsertColumn(i, &lvColumn);
		delete szBuffer;
	}

	m_pView = (CExamView*)GetParent();
	
	// Recordset对象
	_RecordsetPtr pRecordset;
	
	try
	{
		// 创建Recordset对象
		pRecordset.CreateInstance(__uuidof(Recordset));
	
		// 打开记录集，使用静态光标和开放式锁定方式
		pRecordset->Open(_bstr_t("SELECT * FROM 课程信息"), 
						 _variant_t((IDispatch*)m_pView->m_pConnection, true), 
						 adOpenStatic,
						 adLockOptimistic,
						 adCmdText);
	}
	catch (_com_error e)
	{
		// 显示错误信息
		CString errormessage;
		errormessage.Format("打开记录集失败!\r\n错误信息:%s", e.ErrorMessage());
		AfxMessageBox(errormessage);
		return FALSE;
	}

	// 显示信息录入对话框
	_variant_t vField;
	try
	{
		// 取得所有课程名称
		for (int i = 0; i < pRecordset->GetRecordCount(); i++)
		{
			vField = pRecordset->GetCollect(long(1));
			m_ctrlCourse.AddString(CString(vField.bstrVal));
			pRecordset->MoveNext();
		}

		// 关闭记录集
		pRecordset->Close();
	}
	catch (_com_error e)
	{
		// 显示错误信息
		CString errormessage;
		errormessage.Format("访问记录字段失败!\r\n错误信息:%s", e.ErrorMessage());
		AfxMessageBox(errormessage);
	}

	return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}

void CSubForm_4::OnSelchangeCourse() 
{
	UpdateData();
	if (m_sClass == "" || m_sGrade == "")
		return;

	// 得到选中的课程名称
	CString sCourse;
	int nSel = m_ctrlCourse.GetCurSel();
	if (nSel >= 0)
		m_ctrlCourse.GetLBText(nSel, sCourse);
	UpdateData(TRUE);
	
	// 得到班级索引
	int nClassID = GetClassID();

	// 得到课程索引
	int nCourseID = GetCourseID(sCourse);

	// 完成查询语句
	CString sCmd = "SELECT * FROM 考生信息 WHERE ";
	CString sTemp;
	if (nClassID >= 0)
	{
		sTemp.Format("班级ID = %d AND ", nClassID);
		sCmd += sTemp;
	}

	if (nCourseID >= 0)
	{
		sTemp.Format("课程ID = %d AND ", nCourseID);
		sCmd += sTemp;
	}

	// 全部
	m_sCmd1 = sCmd + "1 = 1";

	// 优秀
	m_sCmd2 = sCmd + "成绩 >= 85";

	// 及格
	m_sCmd3 = sCmd + "成绩 >= 60 AND 成绩 < 85";

	// 不及格
	m_sCmd4 = sCmd + "成绩 < 60";

	// Recordset对象
	_RecordsetPtr pRecordset;
	_variant_t vField;
	try
	{
		// 创建Recordset对象
		pRecordset.CreateInstance(__uuidof(Recordset));
	
		// 打开记录集，使用静态光标和开放式锁定方式
		pRecordset->Open(_bstr_t(m_sCmd1), 
						 _variant_t((IDispatch*)m_pView->m_pConnection, true), 
						 adOpenStatic,
						 adLockOptimistic,
						 adCmdText);

		// 计算平均分
		float fSum = 0.0f;
		for (int i = 0; i < pRecordset->GetRecordCount(); i++)
		{
			vField = pRecordset->GetCollect(long(5));
			fSum += vField.lVal;
		}
		if (pRecordset->GetRecordCount() > 0)
			m_fAvg = fSum / pRecordset->GetRecordCount();
		pRecordset->Close();

		// 打开记录集，使用静态光标和开放式锁定方式
		pRecordset->Open(_bstr_t(m_sCmd2), 
						 _variant_t((IDispatch*)m_pView->m_pConnection, true), 
						 adOpenStatic,
						 adLockOptimistic,
						 adCmdText);

		// 优秀人数
		m_nNum1 = pRecordset->GetRecordCount();
		pRecordset->Close();

		// 打开记录集，使用静态光标和开放式锁定方式
		pRecordset->Open(_bstr_t(m_sCmd3), 
						 _variant_t((IDispatch*)m_pView->m_pConnection, true), 
						 adOpenStatic,
						 adLockOptimistic,
						 adCmdText);

		// 及格人数
		m_nNum2 = pRecordset->GetRecordCount();
		pRecordset->Close();

		// 打开记录集，使用静态光标和开放式锁定方式
		pRecordset->Open(_bstr_t(m_sCmd4), 
						 _variant_t((IDispatch*)m_pView->m_pConnection, true), 
						 adOpenStatic,
						 adLockOptimistic,
						 adCmdText);

		// 及格人数
		m_nNum3 = pRecordset->GetRecordCount();
		pRecordset->Close();
	}
	catch (_com_error e)
	{
		// 显示错误信息
		CString errormessage;
		errormessage.Format("打开记录集失败!\r\n错误信息:%s", e.ErrorMessage());
		AfxMessageBox(errormessage);
		return;
	}
	UpdateData(FALSE);
}

int CSubForm_4::GetClassID()
{
	int ret;

	// Recordset对象
	_RecordsetPtr pRecordset;
	_variant_t vField;
	try
	{
		// 创建Recordset对象
		pRecordset.CreateInstance(__uuidof(Recordset));
	
		// 打开记录集，使用静态光标和开放式锁定方式
		CString sCmd = "SELECT * FROM 班级信息 WHERE 班级名称 = '" + m_sClass + "' AND 年级 = '" + m_sGrade + "'";
		pRecordset->Open(_bstr_t(sCmd), 
						 _variant_t((IDispatch*)m_pView->m_pConnection, true), 
						 adOpenStatic,
						 adLockOptimistic,
						 adCmdText);

		// 得到班级索引
		vField = pRecordset->GetCollect(long(0));
		ret = vField.lVal;
		pRecordset->Close();
	}
	catch (_com_error e)
	{
		// 显示错误信息
		CString errormessage;
		errormessage.Format("打开记录集失败!\r\n错误信息:%s", e.ErrorMessage());
		AfxMessageBox(errormessage);
		return -1;
	}

	return ret;
}


int CSubForm_4::GetCourseID(CString sCourse)
{
	int ret;

	// Recordset对象
	_RecordsetPtr pRecordset;
	_variant_t vField;
	try
	{
		// 创建Recordset对象
		pRecordset.CreateInstance(__uuidof(Recordset));
	
		// 打开记录集，使用静态光标和开放式锁定方式
		CString sCmd = "SELECT * FROM 课程信息 WHERE 课程名称 = '" + sCourse + "'";
		pRecordset->Open(_bstr_t(sCmd), 
						 _variant_t((IDispatch*)m_pView->m_pConnection, true), 
						 adOpenStatic,
						 adLockOptimistic,
						 adCmdText);

		// 得到班级索引
		vField = pRecordset->GetCollect(long(0));
		ret = vField.lVal;
		pRecordset->Close();
	}
	catch (_com_error e)
	{
		// 显示错误信息
		CString errormessage;
		errormessage.Format("打开记录集失败!\r\n错误信息:%s", e.ErrorMessage());
		AfxMessageBox(errormessage);
		return FALSE;
	}

	return ret;
}

void CSubForm_4::OnView1() 
{
	DoView(m_sCmd2);
}

void CSubForm_4::OnView2() 
{
	DoView(m_sCmd3);
}

void CSubForm_4::OnView3() 
{
	DoView(m_sCmd4);
}

CString CSubForm_4::GetName(int nID)
{
	CString ret;

	// Recordset对象
	_RecordsetPtr pRecordset;
	_variant_t vField;
	try
	{
		// 创建Recordset对象
		pRecordset.CreateInstance(__uuidof(Recordset));
	
		// 打开记录集，使用静态光标和开放式锁定方式
		CString sCmd;
		sCmd.Format("SELECT * FROM 学生信息 WHERE 学生ID = %d", nID);
		pRecordset->Open(_bstr_t(sCmd), 
						 _variant_t((IDispatch*)m_pView->m_pConnection, true), 
						 adOpenStatic,
						 adLockOptimistic,
						 adCmdText);

		// 得到班级索引
		vField = pRecordset->GetCollect(long(2));
		ret = vField.bstrVal;
		pRecordset->Close();
	}
	catch (_com_error e)
	{
		// 显示错误信息
		CString errormessage;
		errormessage.Format("打开记录集失败!\r\n错误信息:%s", e.ErrorMessage());
		AfxMessageBox(errormessage);
		return "";
	}

	return ret;
}

void CSubForm_4::DoView(CString sSQL)
{
	// Recordset对象
	_RecordsetPtr pRecordset;
	_variant_t vField;
	try
	{
		// 创建Recordset对象
		pRecordset.CreateInstance(__uuidof(Recordset));
	
		// 打开记录集，使用静态光标和开放式锁定方式
		pRecordset->Open(_bstr_t(sSQL), 
						 _variant_t((IDispatch*)m_pView->m_pConnection, true), 
						 adOpenStatic,
						 adLockOptimistic,
						 adCmdText);

		m_ctrlResult.DeleteAllItems();
		CString sTemp;
		for (int i = 0; i < pRecordset->GetRecordCount(); i++)
		{
			// 考号
			vField = pRecordset->GetCollect(long(1));
			sTemp.Format("%d", vField.lVal);
			m_ctrlResult.InsertItem(i, sTemp, 0);

			// 姓名
			vField = pRecordset->GetCollect(long(2));
			sTemp = GetName(vField.lVal);
			m_ctrlResult.SetItemText(i, 1, sTemp);

			// 年级
			m_ctrlResult.SetItemText(i, 2, m_sGrade);

			// 班级
			m_ctrlResult.SetItemText(i, 3, m_sClass);

			// 成绩
			vField = pRecordset->GetCollect(long(5));
			sTemp.Format("%d", vField.lVal);
			m_ctrlResult.SetItemText(i, 4, sTemp);

			pRecordset->MoveNext();
		}

		pRecordset->Close();
	}
	catch (_com_error e)
	{
		// 显示错误信息
		CString errormessage;
		errormessage.Format("打开记录集失败!\r\n错误信息:%s", e.ErrorMessage());
		AfxMessageBox(errormessage);
		return;
	}
}