classavgscorelist.cpp

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

// ClassAvgScoreList.cpp: implementation of the ClassAvgScoreList class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "sm.h"
#include "ClassAvgScoreList.h"

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

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

//##ModelId=40A481F700DC
ClassAvgScoreList::ClassAvgScoreList(CListCtrl * plist):BaseList(plist)
{

}

//##ModelId=40A481F700EA
ClassAvgScoreList::~ClassAvgScoreList()
{

}

//##ModelId=40A481F700DB
void ClassAvgScoreList::Init()
{
	pList->InsertColumn(0, _T("单位"), LVCFMT_LEFT, 90);
	pList->InsertColumn(1, _T("班级"), LVCFMT_LEFT, 90);
	pList->InsertColumn(2, _T("科目"), LVCFMT_LEFT, 110);
	pList->InsertColumn(3, _T("平均成绩"), LVCFMT_LEFT, 110);
}
//subject_id==0 , count the all score -2 count total score
//##ModelId=40A481F700CC
void ClassAvgScoreList::LoadData(CString bt, CString ed, long subject_id)
{
	CString sql, s;
	CDaoQueryDef QD(&theDatabase);
	
	if(subject_id==-2)
	{
		sql  = "select unit, class, \"总平均成绩\" as subject,avg_score as score ";
		sql += "  from (SELECT class.id as class_id , avg(score.score ) as avg_score";
		sql += "  from score,class,student,teaching where ";
		sql +=	"  score.date between #" + bt + "# and #" + ed;
		sql +=  "# and student.class_id=class.id and score.student_id=student.id";
		sql +=	" and score.teaching_id = teaching.id  group by class.id) as avg_t, class, unit ";
		sql +=	" where class_id = class.id and class.superior_id = unit.id order by avg_score";
	}
	else
	{
		sql  = "SELECT unit.unit as unit, class.class, subject.subject as subject,\
			    avg_score as score FROM \
			   [SELECT class.id as class_id ,subject.id as subject_id, avg(score.score ) as avg_score\
				from score,class,student,teaching,subject where ";
		sql +=	"  score.date between #" + bt + "# and #" + ed + "# ";
		if(subject_id!=0)
		{
			s.Format("%d", subject_id);
			sql += " and subject.id = "+s;
		}
		sql += " and student.class_id=class.id and score.student_id=student.id\
				and score.teaching_id = teaching.id and subject.id = teaching.subject_id\
				group by class.id, subject.id ]. AS a, subject, class, unit\
				WHERE a.class_id = class.id and  a.subject_id = subject.id and class.superior_id = unit.id order by avg_score";
	}
/*	CDaoTableDef td(&theDatabase);
	td.Open("str");
	CDaoRecordset rs;
	rs.Open(&td);
	rs.AddNew();
	rs.SetFieldValue("str",LPCSTR(sql));
	rs.Update();
	rs.Close();
	td.Close();*/
	
	QD.Create(NULL, sql);
	CDaoRecordset RS;
	RS.Open(&QD);
/*	CDaoFieldInfo  fi;
	RS.GetFieldInfo(0, fi);
	s = fi.m_strName+",";
	RS.GetFieldInfo(1, fi);
	s += fi.m_strName+",";
	RS.GetFieldInfo(2, fi);
	s += fi.m_strName+",";
	RS.GetFieldInfo(3, fi);
	s += fi.m_strName;
	*/
//	s.Format("%d,%d", RS.GetFieldValue("score").vt,VT_R8    );
//	AfxMessageBox(s);
	while(!RS.IsEOF())
	{
		Datas.push_back(ListData(
							V_BSTRT(&RS.GetFieldValue("unit")),
							V_BSTRT(&RS.GetFieldValue("class.class")),
							V_BSTRT(&RS.GetFieldValue("subject")),
							RS.GetFieldValue("score").dblVal
						)
			);
		RS.MoveNext();
	}
	RS.Close();
	QD.Close();

}

//##ModelId=40A481F700BC
void ClassAvgScoreList::Load(CString bt, CString ed, long subject_id)
{
	Clear();
	LoadData(bt, ed, subject_id);
	CString s;
	vector<ListData>::iterator iter;
	int i=0;
	for(iter=Datas.begin(); iter!=Datas.end(); iter++)
	{
		pList->InsertItem(i, iter->UnitTitle);
		pList->SetItem(i, 1, LVIF_TEXT, iter->ClassTitle, 0, 0, 0, 0);
		pList->SetItem(i, 2, LVIF_TEXT, iter->SubjectTitle, 0, 0, 0, 0);
		s.Format("%.2f", iter->AvgScore);
		pList->SetItem(i, 3, LVIF_TEXT, s, 0, 0, 0, 0);
	}
}

//##ModelId=40A481F700BB
void ClassAvgScoreList::Clear()
{
	BaseList::Clear();
	Datas.clear();
}