avgsubjectdlg.cpp

课程设计的简单Access学生数据库系统

// AvgSubjectDlg.cpp : implementation file
//

#include "stdafx.h"
#include "sm.h"
#include "AvgSubjectDlg.h"

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

/////////////////////////////////////////////////////////////////////////////
// CAvgSubjectDlg dialog


//##ModelId=40A481F70281
CAvgSubjectDlg::CAvgSubjectDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CAvgSubjectDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CAvgSubjectDlg)
	m_Avg = _T("");
	//}}AFX_DATA_INIT
}


//##ModelId=40A481F702AF
void CAvgSubjectDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CAvgSubjectDlg)
	DDX_Control(pDX, IDC_SUBJECT_COMBO, m_Subject);
	DDX_Text(pDX, IDC_AVG_SUBJECT_EDIT, m_Avg);
	DDX_Control(pDX, IDC_DTPICKER1, m_Begin);
	DDX_Control(pDX, IDC_DTPICKER2, m_End);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CAvgSubjectDlg, CDialog)
	//{{AFX_MSG_MAP(CAvgSubjectDlg)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CAvgSubjectDlg message handlers

//##ModelId=40A481F702C0
void CAvgSubjectDlg::OnOK() 
{
	// TODO: Add extra validation here
	COleDateTime bt(m_Begin.GetYear().intVal, m_Begin.GetMonth().intVal, m_Begin.GetDay().intVal, 0, 0, 0);
	COleDateTime et(m_End.GetYear().intVal, m_End.GetMonth().intVal, m_End.GetDay().intVal, 23, 59, 59);
	CString sql, s;
	CDaoQueryDef QD(&theDatabase);
	s.Format("%ld", m_Subject.GetItemData(m_Subject.GetCurSel()));
	sql  = "SELECT avg(score) as avg_score from subject,score,teaching where ";
	sql += "subject.id = " + s; 
	sql += " and score.date between  #"+bt.Format("%Y-%m-%d %H:%M:%S")+"# and #"+et.Format("%Y-%m-%d %H:%M:%S");
	sql += "# and score.teaching_id = teaching.id and teaching.subject_id = subject.id  ";
	QD.Create(NULL, sql);
	CDaoRecordset RS;
	RS.Open(&QD);
	if(!RS.IsEOF())
		m_Avg.Format("%.2f", RS.GetFieldValue("avg_score").dblVal);
	else
		m_Avg = "无平均成绩";
//	AfxMessageBox(m_Avg);
	UpdateData(false);
	RS.Close();
	QD.Close();

	//CDialog::OnOK();
}

//##ModelId=40A481F702C2
void CAvgSubjectDlg::OnCancel() 
{
	// TODO: Add extra cleanup here
	
	CDialog::OnCancel();
}

//##ModelId=40A481F702CF
BOOL CAvgSubjectDlg::OnInitDialog() 
{
	CDialog::OnInitDialog();

	LoadSubject();
	CTime t = CTime::GetCurrentTime();
	CTimeSpan cts(60, 0, 0, 0);
	m_End.SetYear(COleVariant((short)t.GetYear()));
	m_End.SetMonth(COleVariant((short)t.GetMonth()));
	m_End.SetDay(COleVariant((short)t.GetDay()));
	t -= cts;
	m_Begin.SetYear(COleVariant((short)t.GetYear()));
	m_Begin.SetMonth(COleVariant((short)t.GetMonth()));
	m_Begin.SetDay(COleVariant((short)t.GetDay()));
	
	return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}

//##ModelId=40A481F70280
void CAvgSubjectDlg::LoadSubject()
{
	CDaoTableDef TD(&theDatabase);
	TD.Open("subject");
	CDaoRecordset RS;
	RS.Open(&TD);
	int i=0;
	while(!RS.IsEOF())
	{
		m_Subject.AddString(V_BSTRT(&RS.GetFieldValue("subject")));
		m_Subject.SetItemData(i++, RS.GetFieldValue("id").lVal);
		RS.MoveNext();
	}
	RS.Close();
	TD.Close();
	m_Subject.SetCurSel(0);
}