pubscoredlg.cpp

ACCESS数据库+MFC编写的学生数据库管理系统

// PubscoreDlg.cpp : implementation file
//

#include "stdafx.h"
#include "学籍管理系统.h"
#include "PubscoreDlg.h"
#include "MainView.h"
#include "StuinfoSet1.h"


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

/////////////////////////////////////////////////////////////////////////////
// CPubscoreDlg dialog


CPubscoreDlg::CPubscoreDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CPubscoreDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CPubscoreDlg)
	m_collage = _T("");
	m_team = _T("");
	//}}AFX_DATA_INIT
}


void CPubscoreDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CPubscoreDlg)
	DDX_Control(pDX, IDC_LIST_ENG, m_englist);
	DDX_Control(pDX, IDC_LIST_SUBSCORE, m_pubsublist);
	DDX_Control(pDX, IDC_LIST_PUB, m_pubscorelist);
	DDX_Control(pDX, IDC_COMBOBOX_TEAM, m_comboxteam);
	DDX_Control(pDX, IDC_COMBOBOX_COLLAGE, m_comboxcollage);
	DDX_CBString(pDX, IDC_COMBOBOX_COLLAGE, m_collage);
	DDX_CBString(pDX, IDC_COMBOBOX_TEAM, m_team);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CPubscoreDlg, CDialog)
	//{{AFX_MSG_MAP(CPubscoreDlg)
	ON_CBN_SELCHANGE(IDC_COMBOBOX_COLLAGE, OnSelchangeComboboxCollage)
	ON_CBN_SELCHANGE(IDC_COMBOBOX_TEAM, OnSelchangeComboboxTeam)
	ON_BN_CLICKED(IDC_BUTTON_PRINT, OnButtonPrint)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CPubscoreDlg message handlers

BOOL CPubscoreDlg::OnInitDialog() 
{
	CDialog::OnInitDialog();
	
	// TODO: Add extra initialization here
	//在这对对话框进行初始化,向组合框添加学院信息

	CDatabase db;
	CStuinfoSet rsstu;
	CRecordset rs(&db),rs1(&db),rs2(&db),rs3(&db);
	CString sqlstr,resul,resul0,count,pass,excellence;
	COMBOBOXEXITEM insItem;
	insItem.mask=CBEIF_TEXT;
	insItem.iItem=0;
	insItem.iImage=0;
	sqlstr="select * from colinfo";
	db.Open(rsstu.GetDefaultConnect());
	rs.Open(CRecordset::forwardOnly,sqlstr);
	int i=0,j=0;
	while(!rs.IsEOF())
	{
		rs.GetFieldValue((int)0,resul);
		insItem.pszText=resul.GetBuffer(0);
		resul.ReleaseBuffer();
		m_comboxcollage.InsertItem(&insItem);
		i++;
		rs.MoveNext();
	}
	insItem.pszText="全校";
	m_comboxcollage.InsertItem(&insItem);
	rs.Close();

//////////////////////////初始化名次表///////////////////////////////
	m_pubscorelist.SetExtendedStyle   (LVS_EX_FULLROWSELECT   |LVS_EX_GRIDLINES   |LVS_EX_INFOTIP);
	m_pubsublist.SetExtendedStyle   (LVS_EX_FULLROWSELECT   |LVS_EX_GRIDLINES   |LVS_EX_INFOTIP);
	m_englist.SetExtendedStyle   (LVS_EX_FULLROWSELECT   |LVS_EX_GRIDLINES   |LVS_EX_INFOTIP);
	
	m_pubscorelist.InsertColumn(0,"学号",LVCFMT_LEFT,80);
	m_pubscorelist.InsertColumn(1,"姓名",LVCFMT_LEFT,72);
	m_pubscorelist.InsertColumn(2,"名次",LVCFMT_LEFT,40);

	m_pubsublist.InsertColumn(0,"课程名称",LVCFMT_LEFT,122);
	m_pubsublist.InsertColumn(1,"平均成绩",LVCFMT_RIGHT,82);
	m_pubsublist.InsertColumn(2,"及格率",LVCFMT_RIGHT,55);
	m_pubsublist.InsertColumn(3,"优秀率",LVCFMT_RIGHT,55);


	m_englist.InsertColumn(0,"课程名称",LVCFMT_LEFT,112);
	m_englist.InsertColumn(1,"单科第一",LVCFMT_LEFT,255);

	sqlstr="select subname from subinfo where subtype='公共基础课'";
	rs.Open(CRecordset::forwardOnly,sqlstr);
	i=3;j=0;
	while(!rs.IsEOF())
	{
		//得到课程名
		rs.GetFieldValue((int)0,resul);
		m_pubscorelist.InsertColumn(i,resul,LVCFMT_RIGHT,80);

		sqlstr.Format("select avg(score),count(*) from scoreinfo where subname='%s'",resul);
		m_pubsublist.InsertItem(j,resul);
		m_englist.InsertItem(j,resul);
		rs1.Open(CRecordset::forwardOnly,sqlstr);
		while(!rs1.IsEOF())
		{
			rs1.GetFieldValue((int)0,resul0);
			double avg=atof(resul0);
			resul0.Format("%.4f",avg);
			m_pubsublist.SetItemText(j,1,resul0);
			


			//得到及格率
			sqlstr.Format("select count(*) from scoreinfo where subname='%s' and score>=60",resul);
			rs2.Open(CRecordset::forwardOnly,sqlstr);
			if(!rs2.IsBOF())
			{			
				rs1.GetFieldValue((int)1,count);
				rs2.GetFieldValue((int)0,pass);
				double co=atof(count);
				double pa=atof(pass);
				CString pas;
				if(co!=0)
				{
					
					pas.Format("%.2f",pa/co*100);
					pas+="%";
					
				}
				else
				{
					pas="未录入";
				}
				m_pubsublist.SetItemText(j,2,pas);
			}
			rs2.Close();

			//得到优秀率
			sqlstr.Format("select count(*) from scoreinfo where subname='%s' and score>=85",resul);
			rs2.Open(CRecordset::forwardOnly,sqlstr);
			if(!rs2.IsBOF())
			{
				rs2.GetFieldValue((int)0,excellence);
				double co=atof(count);
				double ex=atof(excellence);
				CString exc;
				if(co!=0)
				{
					
					exc.Format("%.2f",ex/co*100);
					exc+="%";
					
				}
				else
				{
					exc="未录入";
				}
				m_pubsublist.SetItemText(j,3,exc);
			}
			rs2.Close();

			//得到单科第一名
			sqlstr.Format("select stunum from scoreinfo where subname='%s' and score =(select max(score) from scoreinfo where subname='%s')",resul,resul);
			rs2.Open(CRecordset::forwardOnly,sqlstr);
			CString eng;eng="";
			while(!rs2.IsEOF())
			{				
				rs2.GetFieldValue((int)0,resul);
				sqlstr.Format("select stunum,name from stuinfo where stunum ='%s'",resul);
				rs3.Open(CRecordset::forwardOnly,sqlstr);
				if(!rs3.IsBOF())
				{
					CString num,name;
					rs3.GetFieldValue((int)0,num);
					rs3.GetFieldValue((int)1,name);
					eng=eng+" "+name+"("+num+")";

				}
				rs3.Close();
				rs2.MoveNext();
				
			}
			m_englist.SetItemText(j,1,eng);
			rs2.Close();

			rs1.MoveNext();
			j++;
		}
		rs1.Close();


		rs.MoveNext();
		i++;
	}
	m_pubscorelist.InsertColumn(i,"总分",LVCFMT_RIGHT,60);
	i++;
	m_pubscorelist.InsertColumn(i,"平均分",LVCFMT_RIGHT,115);


	rs.Close();

////////////////////////列出所有学生////////////////////////////////

	sqlstr="select stunum,name from stuinfo";
	rs.Open(CRecordset::forwardOnly,sqlstr);
	while(!rs.IsEOF())
	{
		rs.GetFieldValue((int)0,resul0);
		m_pubscorelist.InsertItem(0,resul0);
		rs.GetFieldValue((int)1,resul0);
		m_pubscorelist.SetItemText(0,1,resul0);
		rs.MoveNext();
	}
	rs.Close();	
	ListOutScore();//列出成绩
	int nsub=m_pubscorelist.GetHeaderCtrl()->GetItemCount();//获取列数
	m_pubscorelist.SortTextItems( nsub-2, FALSE );//降序排列平均分
	for(i=0;i<m_pubscorelist.GetItemCount();i++)//写上名次
	{
		resul0.Format("%d",i+1);
		m_pubscorelist.SetItemText(i,2,resul0);
	}



	return TRUE; 
}


//学院下拉框改变
void CPubscoreDlg::OnSelchangeComboboxCollage() 
{
	// TODO: Add your control notification handler code here
	for(int k=-20;k<20;k++)
	{
		m_comboxteam.DeleteItem(0);//删除后选项立即改变,保持有第0项
	}	
	UpdateData();
	m_team="";
	UpdateData(FALSE);
	CDatabase db;
	CStuinfoSet rsstu;
	CRecordset rs(&db);
	CString sqlstr,resul,resul0;
	COMBOBOXEXITEM insItem;
	insItem.mask=CBEIF_TEXT;
	insItem.iItem=0;
	insItem.iImage=0;
	sqlstr="select teamname from teaminfo where belongcol='"+m_collage+"'";
	db.Open(rsstu.GetDefaultConnect());
	rs.Open(CRecordset::forwardOnly,sqlstr);
	int i=0;
	while(!rs.IsEOF())
	{
		rs.GetFieldValue((int)0,resul);
		insItem.pszText=resul.GetBuffer(0);
		resul.ReleaseBuffer();
		m_comboxteam.InsertItem(&insItem);
		rs.MoveNext();
	}
	rs.Close();

//列出该学院学生表及成绩
	if(m_collage=="全校")
		sqlstr="select stunum,name from stuinfo";
	else
		sqlstr="select stunum,name from stuinfo where collage='"+m_collage+"'";
	m_pubscorelist.DeleteAllItems();
	rs.Open(CRecordset::forwardOnly,sqlstr);
	i=0;
	while(!rs.IsEOF())
	{
		rs.GetFieldValue((int)0,resul0);
		m_pubscorelist.InsertItem(i,resul0);
		rs.GetFieldValue((int)1,resul0);
		m_pubscorelist.SetItemText(i,1,resul0);
		i++;
		rs.MoveNext();
	}
	rs.Close();	
	ListOutScore();
	int nsub=m_pubscorelist.GetHeaderCtrl()->GetItemCount();//获取列数
	m_pubscorelist.SortTextItems( nsub-2, FALSE );
}

void CPubscoreDlg::ListOutScore()
{
	UpdateData();
	CDatabase db;
	CStuinfoSet rsstu;
	CRecordset rs(&db);
	CString sqlstr,stunum,subname,resul,resul0;
	db.Open(rsstu.GetDefaultConnect());

	LVCOLUMN lvcol;
	char  str[256];
	lvcol.mask = LVCF_TEXT;
	lvcol.pszText = str;
	lvcol.cchTextMax = 256;	


	int i,j;
	int nitem=m_pubscorelist.GetItemCount();
	int nsub=m_pubscorelist.GetHeaderCtrl()->GetItemCount();
	for(i=0;i<nitem;i++)
	{
		stunum=m_pubscorelist.GetItemText(i,0) ;
		for(j=3;j<nsub-2;j++)
		{
			m_pubscorelist.GetColumn(j, &lvcol);
			subname = lvcol.pszText; 

			sqlstr.Format("select score from scoreinfo where stunum='%s' and subname='%s'",stunum,subname);
			rs.Open(CRecordset::forwardOnly,sqlstr);
			if(rs.IsBOF())
				m_pubscorelist.SetItemText(i,j,"未录入");
			while(!rs.IsEOF())
			{
				rs.GetFieldValue((int)0,resul0);

				m_pubscorelist.SetItemText(i,j,resul0);
				rs.MoveNext();
			}
			rs.Close();			
		
		}

		//加上总分和平均分
		sqlstr.Format("SELECT Sum(score) FROM scoreinfo WHERE stunum='%s' and (((scoreinfo.subname) In (select subname from subinfo where subtype='公共基础课')));",stunum);
		rs.Open(CRecordset::forwardOnly,sqlstr);
		if(rs.IsBOF())
			m_pubscorelist.SetItemText(i,nsub-2,"未录入");
		while(!rs.IsEOF())
		{
			rs.GetFieldValue((int)0,resul0);
			m_pubscorelist.SetItemText(i,nsub-2,resul0);
			rs.MoveNext();
		}
		rs.Close();
		sqlstr.Format("SELECT avg(score) FROM scoreinfo WHERE stunum='%s' and (((scoreinfo.subname) In (select subname from subinfo where subtype='公共基础课')));",stunum);
		rs.Open(CRecordset::forwardOnly,sqlstr);
		if(rs.IsBOF())
			m_pubscorelist.SetItemText(i,nsub-1,"未录入");
		while(!rs.IsEOF())
		{
			rs.GetFieldValue((int)0,resul0);
			m_pubscorelist.SetItemText(i,nsub-1,resul0);
			rs.MoveNext();
		}
		rs.Close();

	}



}



//学员队下拉框改变
void CPubscoreDlg::OnSelchangeComboboxTeam() 
{
	// TODO: Add your control notification handler code here
	UpdateData();
	if(m_team=="")
		return;
	CDatabase db;
	CStuinfoSet rsstu;
	CRecordset rs(&db);
	CString sqlstr,resul,resul0;

	db.Open(rsstu.GetDefaultConnect());
	sqlstr="select stunum,name from stuinfo where stuteam='"+m_team+"' and collage='"+m_collage+"'";
	m_pubscorelist.DeleteAllItems();
	rs.Open(CRecordset::forwardOnly,sqlstr);
	int i=0;
	while(!rs.IsEOF())
	{
		rs.GetFieldValue((int)0,resul0);
		m_pubscorelist.InsertItem(i,resul0);
		rs.GetFieldValue((int)1,resul0);
		m_pubscorelist.SetItemText(i,1,resul0);
		i++;
		rs.MoveNext();
	}
	rs.Close();	
	ListOutScore();
	int nsub=m_pubscorelist.GetHeaderCtrl()->GetItemCount();//获取列数
	m_pubscorelist.SortTextItems( nsub-2, FALSE );

}

void CPubscoreDlg::OnButtonPrint() 
{
	// TODO: Add your control notification handler code here
	PrintListCtrl(m_pubscorelist);	
}


BOOL CPubscoreDlg::PrintListCtrl(CListCtrl &list)
{//该结构用于存储各列的信息
typedef struct tagColAtt
{
int nColIndex;
CString strColText;
int nPrintX;
int nSubItemIndex;
}COLATT;


		PRINTDLG pd;
		pd.lStructSize = sizeof(PRINTDLG);
		pd.Flags = PD_RETURNDC;
		pd.hDC = NULL;
		pd.hwndOwner = NULL;
		pd.hInstance = NULL;
		pd.nMaxPage = 1;
		pd.nMinPage = 1;
		pd.nFromPage = 1;
		pd.nToPage = 1;
		pd.nCopies = 1;
		pd.hDevMode = NULL;
		pd.hDevNames = NULL;

		//显示打印对话框，由用户来设定纸张大小等。
		if(!PrintDlg(&pd)) return FALSE; 
		ASSERT(pd.hDC!=NULL);

		int nHorRes = GetDeviceCaps(pd.hDC, HORZRES); 
		int nVerRes = GetDeviceCaps(pd.hDC, VERTRES);
		int nXMargin = 2;
		int nYMargin = 2;
		TEXTMETRIC tm;
		GetTextMetrics(pd.hDC, &tm);
		int nCharHeight = tm.tmHeight;
		int nCharWidth = tm.tmAveCharWidth;


		CHeaderCtrl* pHeader = list.GetHeaderCtrl();
		//获得行，列的个数
		int nColCount = pHeader->GetItemCount();
		int nLineCount = list.GetItemCount();

		int ColOrderArray[100];
		COLATT ca[100];
		list.GetColumnOrderArray(ColOrderArray, nColCount);
		int nColX =nXMargin*nCharWidth;

		//检索各列的信息，确定列标题的内容长度。
		for(int i =0 ; i<nColCount; i++)
		{
				ca[i].nColIndex = ColOrderArray[i];
				LVCOLUMN lvc;
				char text[100];
				lvc.mask = LVCF_TEXT|LVCF_SUBITEM;
				lvc.pszText = text;
				lvc.cchTextMax = 100;
				list.GetColumn(ca[i].nColIndex, &lvc);
				ca[i].strColText = lvc.pszText;
				ca[i].nSubItemIndex = lvc.iSubItem;
				ca[i].nPrintX = nColX;
				nColX += nCharWidth * strlen(ca[i].strColText);

				if(nColX > nHorRes) 
				{
					DeleteDC(pd.hDC);
					AfxMessageBox("字段太多，无法在一行内打印，请试用较大的纸，或横向打印。");
					return FALSE;
					}


		}

DOCINFO di;
di.cbSize = sizeof(DOCINFO);
di.lpszDocName = "ListCtrl Data Printing"; 
di.lpszOutput = (LPTSTR) NULL; 
di.lpszDatatype = (LPTSTR) NULL; 
di.fwType = 0; 
StartDoc(pd.hDC, &di);
StartPage(pd.hDC);

//调整各列的宽度，以使各列在后面的打印输出时更均匀的打印在纸上。
int space = (nHorRes-nXMargin*nCharWidth-nColX) / (nColCount -1);
for(i =1; i<nColCount; i++)
{
ca[i].nPrintX += i*space;
}

//输出列标题
for(i =0; i<nColCount; i++)
TextOut(pd.hDC, ca[i].nPrintX, nYMargin, 
ca[i].strColText, strlen(ca[i].strColText));

int nMaxLinePerPage = nVerRes/nCharHeight -3;
int nCurPage =1;
//输出各列的数据
for(i =0; i<nLineCount; i++)
{
	for(int j =0; j<nColCount; j++)
	{
		if(i+1-(nCurPage-1)*nMaxLinePerPage > nMaxLinePerPage)
		{
			//新的一页
			EndPage(pd.hDC);
			StartPage(pd.hDC);
			nCurPage ++;
		}
		CString subitem = list.GetItemText(i,j);
		TextOut(pd.hDC, ca[j].nPrintX, 
		nYMargin+(i+1-(nCurPage-1)*nMaxLinePerPage)*nCharHeight, 
		subitem, strlen(subitem));
	}
}

EndPage(pd.hDC);
EndDoc(pd.hDC);
//打印结束
DeleteDC(pd.hDC);
return TRUE;



}