inspescoredlg.cpp

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

// InspescoreDlg.cpp : implementation file
//

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

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

/////////////////////////////////////////////////////////////////////////////
// CInspescoreDlg dialog


CInspescoreDlg::CInspescoreDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CInspescoreDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CInspescoreDlg)
	m_boxcollage = _T("");
	m_boxspe = _T("");
	m_boxsub = _T("");
	m_name = _T("");
	m_stunum = _T("");
	m_score = _T("");
	//}}AFX_DATA_INIT
}


void CInspescoreDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CInspescoreDlg)
	DDX_Control(pDX, IDC_LIST_INSPE, m_inspelist);
	DDX_Control(pDX, IDC_COMBOBOXEXSUB, m_comboxsub);
	DDX_Control(pDX, IDC_COMBOBOXEXSPE, m_comboxspe);
	DDX_Control(pDX, IDC_COMBOBOXEXCOLLAGE, m_comboxcollage);
	DDX_CBString(pDX, IDC_COMBOBOXEXCOLLAGE, m_boxcollage);
	DDX_CBString(pDX, IDC_COMBOBOXEXSPE, m_boxspe);
	DDX_CBString(pDX, IDC_COMBOBOXEXSUB, m_boxsub);
	DDX_Text(pDX, IDC_EDIT_NAME, m_name);
	DDX_Text(pDX, IDC_EDIT_NUM, m_stunum);
	DDX_Text(pDX, IDC_EDIT_SCORE, m_score);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CInspescoreDlg, CDialog)
	//{{AFX_MSG_MAP(CInspescoreDlg)
	ON_CBN_SELCHANGE(IDC_COMBOBOXEXCOLLAGE, OnSelchangeComboboxexcollage)
	ON_NOTIFY(NM_CLICK, IDC_LIST_INSPE, OnClickListInspe)
	ON_CBN_SELCHANGE(IDC_COMBOBOXEXSUB, OnSelchangeComboboxexsub)
	ON_CBN_SELCHANGE(IDC_COMBOBOXEXSPE, OnSelchangeComboboxexspe)
	ON_BN_CLICKED(IDC_BUTTON_INPUT, OnButtonInput)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CInspescoreDlg message handlers

BOOL CInspescoreDlg::OnInitDialog() 
{
	CDialog::OnInitDialog();
	
	// TODO: Add extra initialization here
	m_inspelist.SetExtendedStyle   (LVS_EX_FULLROWSELECT   |LVS_EX_GRIDLINES   |LVS_EX_INFOTIP);
    int  nWidth = 110 ;
    m_inspelist.InsertColumn( 0 , "学号" ,LVCFMT_LEFT,120 );
	m_inspelist.InsertColumn( 1 , "姓名" ,LVCFMT_LEFT,nWidth * 2 / 3 );
	m_inspelist.InsertColumn( 2 , "得分" ,LVCFMT_LEFT,nWidth * 2 / 3 );


	//在这对对话框进行初始化,向组合框添加学院信息

	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 * from colinfo";
	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_comboxcollage.InsertItem(&insItem);
		i++;
		rs.MoveNext();
	}
	insItem.pszText="所有学院";
	m_comboxcollage.InsertItem(&insItem);
	rs.Close();

/////////////////////////////////////////////////////////

	sqlstr="select stunum,name from stuinfo";
	rs.Open(CRecordset::forwardOnly,sqlstr);
	i=0;
	while(!rs.IsEOF())
	{
		rs.GetFieldValue((int)0,resul0);
		m_inspelist.InsertItem(i,resul0);
		rs.GetFieldValue((int)1,resul0);
		m_inspelist.SetItemText(i,1,resul0);
		i++;
		rs.MoveNext();
	}
	rs.Close();	

	UpdateData(FALSE);
	
	sqlstr="select spename from speinfo";
	rs.Open(CRecordset::forwardOnly,sqlstr);
	i=0;
	while(!rs.IsEOF())
	{
		rs.GetFieldValue((int)0,resul);
		insItem.pszText=resul.GetBuffer(0);
		resul.ReleaseBuffer();
		m_comboxspe.InsertItem(&insItem);
		rs.MoveNext();
	}
	rs.Close();
	
	return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}



void CInspescoreDlg::OnSelchangeComboboxexcollage() //学院框改变
{
	// TODO: Add your control notification handler code here
	//专业
	for(int k=-20;k<20;k++)
	{
		m_comboxspe.DeleteItem(0);//删除后选项立即改变,保持有第0项
	}	
	CInspescoreDlg::UpdateData();
	m_boxspe="";
	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;
	if(m_boxcollage=="所有学院")
	{
		sqlstr="select spename from speinfo";
	}
	else
		sqlstr="select spename from speinfo where belongcol='"+m_boxcollage+"'";
	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_comboxspe.InsertItem(&insItem);
		rs.MoveNext();
	}
	rs.Close();

	if(m_boxcollage=="所有学院")
		sqlstr="select stunum,name from stuinfo";
	else
		sqlstr="select stunum,name from stuinfo where collage='"+m_boxcollage+"'";
	m_inspelist.DeleteAllItems();
	rs.Open(CRecordset::forwardOnly,sqlstr);
	i=0;
	while(!rs.IsEOF())
	{
		rs.GetFieldValue((int)0,resul0);
		m_inspelist.InsertItem(i,resul0);
		rs.GetFieldValue((int)1,resul0);
		m_inspelist.SetItemText(i,1,resul0);
		i++;
		rs.MoveNext();
	}
	rs.Close();	
	if(m_boxsub!="")
		OnSelchangeComboboxexsub();
	if(m_boxsub=="")
		for(int k=-20;k<20;k++)
			{
				m_comboxsub.DeleteItem(0);//删除后选项立即改变,保持有第0项
			}


	
}

int CInspescoreDlg::GetSelectedItem()
{
	  for(int i=0; i<m_inspelist.GetItemCount(); i++)
      {
           if( m_inspelist.GetItemState(i, LVIS_SELECTED) == LVIS_SELECTED )
           {
				return i;//当有一行被选中,返回行号
           }
      }
	  return -2;//如果未选中任何一行则返回-2


}

void CInspescoreDlg::OnClickListInspe(NMHDR* pNMHDR, LRESULT* pResult) 
{
	// TODO: Add your control notification handler code here
//单击表内某个记录

	int item=GetSelectedItem();//得到被选的行
	CString resul=m_inspelist.GetItemText(item,0);
	m_stunum=resul;
	resul=m_inspelist.GetItemText(item,1);
	m_name=resul;
	resul=m_inspelist.GetItemText(item,2);
	m_score=resul;
	UpdateData(FALSE);
	
	*pResult = 0;
}

void CInspescoreDlg::OnSelchangeComboboxexsub() 
{
	// TODO: Add your control notification handler code here
	CInspescoreDlg::UpdateData();//得到m_boxsub值
	if(m_boxsub=="")
		return;
	CDatabase db;
	CStuinfoSet rsstu;
	CRecordset rs(&db);
	CString sqlstr,resul,resul0;
	db.Open(rsstu.GetDefaultConnect());



	for(int j=0;j<m_inspelist.GetItemCount();j++)
	{
		resul=m_inspelist.GetItemText(j,0);
		sqlstr="select score from scoreinfo where stunum='"+resul+"' and subname='"+m_boxsub+"'";
		rs.Open(CRecordset::forwardOnly,sqlstr);
		if(rs.IsBOF())
		{
			m_inspelist.SetItemText(j,2,"未录入");
		}
		else
		{
			rs.GetFieldValue((int)0,resul0);
			m_inspelist.SetItemText(j,2,resul0);
			rs.MoveNext();
		}
		rs.Close();	
	}
	m_stunum="";
	m_name="";
	m_score="";
	UpdateData(FALSE);	
}

void CInspescoreDlg::OnSelchangeComboboxexspe() 
{
	// TODO: Add your control notification handler code here
	CInspescoreDlg::UpdateData();
	if(m_boxspe=="")
		return;
	CDatabase db;
	CStuinfoSet rsstu;
	CRecordset rs(&db);
	CString sqlstr,resul,resul0;
	COMBOBOXEXITEM insItem;
	insItem.mask=CBEIF_TEXT;
	insItem.iItem=0;
	insItem.iImage=0;
	db.Open(rsstu.GetDefaultConnect());
	if(m_boxcollage=="所有学院"||m_boxcollage=="")
	{
		sqlstr="select stunum,name from stuinfo where spename='"+m_boxspe+"'";
	}
	else
		sqlstr="select stunum,name from stuinfo where spename='"+m_boxspe+"' and collage='"+m_boxcollage+"'";
	m_inspelist.DeleteAllItems();
	rs.Open(CRecordset::forwardOnly,sqlstr);
	int i=0;
	while(!rs.IsEOF())
	{
		rs.GetFieldValue((int)0,resul0);
		m_inspelist.InsertItem(i,resul0);
		rs.GetFieldValue((int)1,resul0);
		m_inspelist.SetItemText(i,1,resul0);
		i++;
		rs.MoveNext();
	}
	rs.Close();	


	sqlstr="select subname from subinfo where belongspc='"+m_boxspe+"'";

	for(int k=-20;k<20;k++)
	{
		m_comboxsub.DeleteItem(0);//删除后选项立即改变,保持有第0项
	}
	CInspescoreDlg::UpdateData();
	m_boxsub="";
	UpdateData(FALSE);
	
	rs.Open(CRecordset::forwardOnly,sqlstr);
	 i=0;
	while(!rs.IsEOF())
	{
		rs.GetFieldValue((int)0,resul);
		insItem.pszText=resul.GetBuffer(0);
		resul.ReleaseBuffer();
		m_comboxsub.InsertItem(&insItem);
		i++;
		rs.MoveNext();
	}
	rs.Close();

	if(m_boxsub!="")
		OnSelchangeComboboxexsub();	
}

void CInspescoreDlg::OnButtonInput() 
{
	// TODO: Add your control notification handler code here
	UpdateData(TRUE);
	CString noin="未录入";
	if(noin.Find(m_score)!=-1||m_score=="")
	{
		AfxMessageBox("请输入成绩");
		return;
	}
	if(m_boxsub=="")
	{
		AfxMessageBox("选择课程");
		return;
	}
	if(m_stunum=="")
	{
		AfxMessageBox("选择学员");
		return;
	}
	CDatabase db;
	CStuinfoSet rsstu;
	CRecordset rs(&db);
	CString sqlstr;
	db.Open(rsstu.GetDefaultConnect());
	sqlstr="select score from scoreinfo where stunum='"+m_stunum+"' and subname='"+m_boxsub+"'";
	rs.Open(CRecordset::forwardOnly,sqlstr);
	int item=GetSelectedItem();
	double scor=atof(m_score);
	if(rs.IsBOF())
	{
		//记录不存在,插入
		sqlstr.Format("insert into scoreinfo values('%s','%s','%f')",m_stunum,m_boxsub,scor);
	}
	else
	{
		//记录存在,更新
		sqlstr.Format("update scoreinfo	set score=%f  where stunum='%s' and subname='%s'",scor,m_stunum,m_boxsub);		
	}
	CMainView::RunSQL(sqlstr);
	m_inspelist.SetItemText(item,2,m_score);	
}