stugradeview.cpp

成绩管理系统

// STUGRADEView.cpp : implementation of the CSTUGRADEView class
//

#include "stdafx.h"
#include "STUGRADE.h"

#include "STUGRADEDoc.h"
#include "STUGRADEView.h"
#include "DlgGrade.h"
#include "Dlgquery.h"
#include "DLGpption.h"

#include "ViewShowCent.h"

#include "MainFrm.h"
//Excel
#include <afxdb.h> 
#include <odbcinst.h>
#include "TlHelp32.h"

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

/////////////////////////////////////////////////////////////////////////////
// CSTUGRADEView
extern CMainFrame *pFrame; 


IMPLEMENT_DYNCREATE(CSTUGRADEView, CFormView)

BEGIN_MESSAGE_MAP(CSTUGRADEView, CFormView)
	//{{AFX_MSG_MAP(CSTUGRADEView)
	ON_BN_CLICKED(IDC_ADDnew_Grade, OnADDnewGrade)
	ON_WM_SIZE()
	ON_BN_CLICKED(IDC_MAKEUP_GRADE, OnMakeupGrade)
	ON_BN_CLICKED(IDC_QUERY_GRADE, OnQueryGrade)
	ON_BN_CLICKED(IDC_reelect_GRADE, OnreelectGRADE)
	ON_BN_CLICKED(IDC_together_GRADE, OntogetherGRADE)
	ON_BN_CLICKED(IDC_DELETE_GRADE, OnDeleteGrade)
	ON_BN_CLICKED(IDC_modify_GRADE, OnmodifyGRADE)
	ON_COMMAND(ID_LIST_NEXT, OnListNext)
	ON_COMMAND(ID_LIST_PRV, OnListPrv)
	ON_COMMAND(IDC_BUTTON_refresh, OnBUTTONrefresh)
	ON_COMMAND(IDF_SHOW_PS, OnShowPs)
	ON_COMMAND(IDF_SHOW_KS, OnShowKs)
	ON_COMMAND(IDF_SHOW_ZP, OnShowZp)
	ON_COMMAND(IDF_SHOW_BK, OnShowBk)
	ON_COMMAND(IDF_SHOW_ALL, OnShowAll)
	ON_COMMAND(IDF_SHOW_CX, OnShowCx)
	ON_BN_CLICKED(IDC_Cent_GRADE, OnCentGRADE)
	ON_BN_CLICKED(IDC_GOOD_GRADE, OnGoodGrade)
	ON_COMMAND(ID_FILE_PRINT, OnFilePrint)
	ON_NOTIFY(NM_DBLCLK, IDC_GradeLIST, OnDblclkGradeLIST)
	ON_NOTIFY(LVN_COLUMNCLICK, IDC_GradeLIST, OnColumnclickGradeLIST)
	ON_BN_CLICKED(IDC_OUTExcel, OnOUTExcel)
	//}}AFX_MSG_MAP
	// Standard printing commands
	ON_COMMAND(ID_FILE_PRINT, CFormView::OnFilePrint)
	ON_COMMAND(ID_FILE_PRINT_DIRECT, CFormView::OnFilePrint)
	ON_COMMAND(ID_FILE_PRINT_PREVIEW, CFormView::OnFilePrintPreview)
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CSTUGRADEView construction/destruction

CSTUGRADEView::CSTUGRADEView()
	: CFormView(CSTUGRADEView::IDD)
{
	//{{AFX_DATA_INIT(CSTUGRADEView)
	//}}AFX_DATA_INIT
	// TODO: add construction code here

}

CSTUGRADEView::~CSTUGRADEView()
{
}

void CSTUGRADEView::DoDataExchange(CDataExchange* pDX)
{
	CFormView::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CSTUGRADEView)
	//}}AFX_DATA_MAP
}

BOOL CSTUGRADEView::PreCreateWindow(CREATESTRUCT& cs)
{
	// TODO: Modify the Window class or styles here by modifying
	//  the CREATESTRUCT cs

	return CFormView::PreCreateWindow(cs);
}

void CSTUGRADEView::OnInitialUpdate()
{
	CFormView::OnInitialUpdate();
    CListCtrl *pctrl = (CListCtrl*)GetDlgItem(IDC_GradeLIST);
		::SetWindowLong(*pctrl,GWL_STYLE,::GetWindowLong(*pctrl,GWL_STYLE)&~LVS_SORTASCENDING&~LVS_SORTDESCENDING);
	pctrl->SetExtendedStyle(pctrl->GetExStyle()&~LVS_EX_CHECKBOXES |
		LVS_EX_FULLROWSELECT|LVS_EX_HEADERDRAGDROP|
		LVS_EX_GRIDLINES|LVS_EX_TRACKSELECT);
	pctrl->SetImageList(&pFrame->m_ImageListFORLIST,LVSIL_SMALL);
	pctrl->InsertColumn(0,"班级名称",LVCFMT_CENTER,120);
	pctrl->InsertColumn(1,"学号",LVCFMT_CENTER,100);
	pctrl->InsertColumn(2,"学生姓名",LVCFMT_LEFT,60);
	pctrl->InsertColumn(3,"课程名称",LVCFMT_LEFT,100);
	pctrl->InsertColumn(4,"考试类型",LVCFMT_LEFT,60);
	
	pctrl->InsertColumn(5,"成绩",LVCFMT_LEFT,50);
	pctrl->InsertColumn(6,"专业名称",LVCFMT_LEFT,100);
	pctrl->InsertColumn(7,"院系名称",LVCFMT_LEFT,100);
	curSQL = "SELECT 班级名称,学生基本信息.学号,姓名,课程名称,考试名称,成绩,专业名称,系名 FROM 成绩表,学生基本信息,考试类型代码,班级信息,课程信息,专业信息,系别信息 WHERE 系别信息.系别代码 = 专业信息.所属系别 AND 专业信息.专业代码 = 班级信息.所属专业 AND 成绩表.学号 = 学生基本信息.学号 AND 成绩表.考试代码 = 考试类型代码.考试代码 AND      学生基本信息.所属班级 = 班级信息.班级代码 AND 成绩表.课程代码 = 课程信息.课程代码 AND 考试名称 <> '总评成绩'";
	RefreshGradeList(curSQL);
	CFrameWnd *pParent = GetParentFrame();  
	if(pParent)
	{
		CRect rtWnd;
		pParent->GetClientRect(rtWnd);
		OnSize(0,rtWnd.Width(),rtWnd.Height());
	}
//	ResizeParentToFit();
//	this->GetParentFrame()->ShowWindow(SW_MAXIMIZE); 最大化

}

/////////////////////////////////////////////////////////////////////////////
// CSTUGRADEView printing

BOOL CSTUGRADEView::OnPreparePrinting(CPrintInfo* pInfo)
{
	// default preparation
	return DoPreparePrinting(pInfo);
}

void CSTUGRADEView::OnBeginPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
	// TODO: add extra initialization before printing
}

void CSTUGRADEView::OnEndPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
	// TODO: add cleanup after printing
}

void CSTUGRADEView::OnPrint(CDC* pDC, CPrintInfo* /*pInfo*/)
{
	// TODO: add customized printing code here
}

/////////////////////////////////////////////////////////////////////////////
// CSTUGRADEView diagnostics

#ifdef _DEBUG
void CSTUGRADEView::AssertValid() const
{
	CFormView::AssertValid();
}

void CSTUGRADEView::Dump(CDumpContext& dc) const
{
	CFormView::Dump(dc);
}

CSTUGRADEDoc* CSTUGRADEView::GetDocument() // non-debug version is inline
{
	ASSERT(m_pDocument->IsKindOf(RUNTIME_CLASS(CSTUGRADEDoc)));
	return (CSTUGRADEDoc*)m_pDocument;
}
#endif //_DEBUG

/////////////////////////////////////////////////////////////////////////////
// CSTUGRADEView message handlers

//返回-1表示出错,0是空记录 数字是记录条数是OK
int CSTUGRADEView::RefreshGradeList(CString szSQL) //注意这里不是通用的SQL查询!对应有班名,学号等列
{  
	int state=0;
   	CListCtrl *pList = (CListCtrl*)GetDlgItem(IDC_GradeLIST);
	pList->DeleteAllItems();
	_RecordsetPtr p_set;
	if(FAILED(p_set.CreateInstance( __uuidof( Recordset ) ) ) )	  
	{
		MessageBox("Fail to create recordset instance!",NULL,MB_ICONSTOP);
		return -1; 
	}
	try{ 
		p_set->Open((LPCSTR)szSQL,RS_PARAM);
	}
	catch(_com_error e)
	{ CString errormessage;
	  errormessage.Format("打开记录错误!\r\n数据连接错误:%s",e.ErrorMessage());
	  AfxMessageBox(errormessage);///显示错误信息
	  return -1;
	}

//	COLORREF GetColor,UnderColor,NameColor;  //设color
// UnderColor = RGB(255,0,0);
	//NameColor =RGB (0,255,0);
     // ::GetSysColor(COLOR_WINDOWTEXT);
	while ( !p_set->adoEOF )
	{   state++;
		CString Class = (char*) ((_bstr_t) p_set->Fields->Item["班级名称"]->Value);
		CString Sno =   (char*) ((_bstr_t) p_set->Fields->Item["学号"]->Value);
		CString Sname = (char*) ((_bstr_t) p_set->Fields->Item["姓名"]->Value);
		CString Cname = (char*) ((_bstr_t) p_set->Fields->Item["课程名称"]->Value);
		CString Ename = (char*) ((_bstr_t) p_set->Fields->Item["考试名称"]->Value);
		CString Rname = (char*) ((_bstr_t) p_set->Fields->Item["成绩"]->Value);
		CString Dname = (char*) ((_bstr_t) p_set->Fields->Item["专业名称"]->Value);
		CString Pname = (char*) ((_bstr_t) p_set->Fields->Item["系名"]->Value);
		
	    int iItem = pList->InsertItem(pList->GetItemCount(),Class,0);
        pList->SetItemText(iItem,1,Sno);
		pList->SetItemText(iItem,2,Sname); 
		pList->SetItemText(iItem,3,Cname);
		pList->SetItemText(iItem,4,Ename);
        //GetColor = pList->GetTextColor();
		//pList->SetTextColor(NameColor);
		pList->SetItemText(iItem,5,Rname);
        //pList->SetTextColor(GetColor);
		pList->SetItemText(iItem,6,Dname);
		pList->SetItemText(iItem,7,Pname);
        p_set->MoveNext();
	}
	p_set->Close();
	CString Scount;
	Scount.Format("共有%d条记录...",state);
    pList->InsertItem(pList->GetItemCount(),Scount,0);
	return state;

}

void CSTUGRADEView::OnSize(UINT nType, int cx, int cy) 
{
	CFormView::OnSize(nType, cx, cy);
		// TODO: Add your message handler code here 列表自动大小
		::SetWindowPos(*GetDlgItem(IDC_GradeLIST),NULL,130,0,cx-130,cy,SWP_NOZORDER);	
}

void CSTUGRADEView::OnADDnewGrade() //增加成绩
{
   	CDlgGrade dlg;
	dlg.m_nFlag=1; //增加标志
	dlg.pListCtrl=(CListCtrl*)GetDlgItem(IDC_GradeLIST);
	dlg.DoModal();
}
void CSTUGRADEView::OnQueryGrade() //查询成绩
{
	char SQL[1000];
	CDLGquery dlg;
    dlg.szSQLt=SQL;
	int a=dlg.DoModal();
	if (a==IDOK)
	{
		curSQL = (LPSTR)SQL; //AfxMessageBox(szSQL);		
		int count = RefreshGradeList(curSQL);
		if (count==0) MessageBox("没有查到符合条件的记录!","查询结果",MB_OK|MB_ICONINFORMATION);
	}
	
}

void CSTUGRADEView::OnMakeupGrade() //补考
{ 
	CString szSQL;
   	CDlgGrade dlg; int b_Np;
    CString b_Sno,b_Sname,b_Cname,b_Ename,b_MinScore,b_Score,b_School; //b_Score为合格分数线//*Np,*school树层数和各值
	dlg.m_nFlag = 2; //补考标志
    dlg.b_Sno = &b_Sno; 
	dlg.b_Sname = &b_Sname;
	dlg.b_Cname = &b_Cname;
	dlg.b_Ename = &b_Ename;
	dlg.b_MiniScore=&b_MinScore;
	dlg.b_Score = &b_Score;
	dlg.b_Np = &b_Np;
    dlg.b_School = &b_School;
	dlg.pListCtrl=(CListCtrl*)GetDlgItem(IDC_GradeLIST);
	int ask = dlg.DoModal();
	if (ask == 1)  //确定的话
	{ 
		CString szSQLlast;
		szSQL="SELECT 班级名称,学生基本信息.学号,姓名,课程名称,考试名称,成绩,专业名称,系名 FROM 成绩表,学生基本信息,考试类型代码,班级信息,课程信息,专业信息,系别信息 WHERE 系别信息.系别代码 = 专业信息.所属系别 AND 专业信息.专业代码 = 班级信息.所属专业 AND 成绩表.学号 = 学生基本信息.学号 AND 成绩表.考试代码 = 考试类型代码.考试代码 AND 学生基本信息.所属班级 = 班级信息.班级代码 AND 成绩表.课程代码 = 课程信息.课程代码 ";
		szSQLlast.Format(" AND 成绩>= %s AND 成绩<%s",b_MinScore,b_Score);	  
		szSQL += szSQLlast;

		szSQLlast.Format(" AND 考试名称 = '%s'",b_Ename);
		szSQL += szSQLlast;
	//	AfxMessageBox(szSQL);
		if ((b_Sno!="所有...") && (b_Sname != "所有..."))
		{   
			szSQLlast.Format(" AND 成绩表.学号 = '%s'",b_Sno);
			szSQL += szSQLlast;
		}
        if (b_Cname!="所有...")  //有课程限制
		{
			szSQLlast.Format(" AND 课程名称='%s'",b_Cname);
			szSQL += szSQLlast;
		}
		if (b_Np)  //学院树层  0层全校
		{
            if (b_Np==1)  //学院
			{
				szSQLlast.Format(" AND 系名 = '%s'",b_School);
				szSQL += szSQLlast;	
			}
			if (b_Np==2)  //专业
			{
				szSQLlast.Format(" AND 专业名称 = '%s'",b_School);
				szSQL += szSQLlast;	
			}
			if (b_Np==3)  //班级
			{
				szSQLlast.Format(" AND 班级名称 = '%s'",b_School);
				szSQL += szSQLlast;	
			}
		}	//end (b_Np)
	}
	else 
	{
        szSQL="SELECT 班级名称,学生基本信息.学号,姓名,课程名称,考试名称,成绩,专业名称,系名 FROM 成绩表,学生基本信息,考试类型代码,班级信息,课程信息,专业信息,系别信息 WHERE 系别信息.系别代码 = 专业信息.所属系别 AND 专业信息.专业代码 = 班级信息.所属专业 AND 成绩表.学号 = 学生基本信息.学号 AND 成绩表.考试代码 = 考试类型代码.考试代码 AND 学生基本信息.所属班级 = 班级信息.班级代码 AND 成绩表.课程代码 = 课程信息.课程代码 AND 考试类型代码.考试名称 = '总评成绩' AND 成绩<60 ";
	}   
    curSQL=szSQL;
    int state = RefreshGradeList(szSQL);
    if (state==0)
	  MessageBox("此条件没有补考记录!","补考",MB_OK|MB_ICONINFORMATION);
 
}

void CSTUGRADEView::OnreelectGRADE() //显示重修
{
	CString szSQL,szSQL1,szSQL2;
   	CDlgGrade dlg; int b_Np;
    CString b_Sno,b_Sname,b_Cname,b_Ename,b_MiniScore,b_Score,b_School; //b_Score为合格分数线//*Np,*school树层数和各值
	dlg.m_nFlag = 3; //重修标志
    dlg.b_Sno = &b_Sno; 
	dlg.b_Sname = &b_Sname;
	dlg.b_Cname = &b_Cname;
	dlg.b_Ename = &b_Ename;
	dlg.b_MiniScore=&b_MiniScore;
	dlg.b_Score = &b_Score;
	dlg.b_Np = &b_Np;
    dlg.b_School = &b_School;
	dlg.pListCtrl=(CListCtrl*)GetDlgItem(IDC_GradeLIST);
	int ask = dlg.DoModal();
	if (ask == 1)  //确定的话
	{ 
		CString szSQLlast;
		szSQL="SELECT 班级名称,学生基本信息.学号,姓名,课程名称,考试名称,成绩,专业名称,系名 FROM 成绩表,学生基本信息,考试类型代码,班级信息,课程信息,专业信息,系别信息 WHERE 系别信息.系别代码 = 专业信息.所属系别 AND 专业信息.专业代码 = 班级信息.所属专业 AND 成绩表.学号 = 学生基本信息.学号 AND 成绩表.考试代码 = 考试类型代码.考试代码 AND 学生基本信息.所属班级 = 班级信息.班级代码 AND 成绩表.课程代码 = 课程信息.课程代码 ";

	//	AfxMessageBox(szSQL);
		if ((b_Sno!="所有...") && (b_Sname != "所有..."))
		{   
			szSQLlast.Format(" AND 成绩表.学号 = '%s'",b_Sno);
			szSQL += szSQLlast;
		}
        if (b_Cname!="所有...")  //有课程限制
		{
			szSQLlast.Format(" AND 课程名称='%s'",b_Cname);
			szSQL += szSQLlast;
		}
		if (b_Np)  //学院树层  0层全校
		{
            if (b_Np==1)  //学院
			{
				szSQLlast.Format(" AND 系名 = '%s'",b_School);
				szSQL += szSQLlast;	
			}
			if (b_Np==2)  //专业
			{
				szSQLlast.Format(" AND 专业名称 = '%s'",b_School);
				szSQL += szSQLlast;	
			}
			if (b_Np==3)  //班级
			{
				szSQLlast.Format(" AND 班级名称 = '%s'",b_School);
				szSQL += szSQLlast;	
			}
		}	//end (b_Np)
		//AND ((考试类型代码.考试名称 = '补考成绩' AND 成绩<60) or (考试类型代码.考试名称 = '总评成绩' AND 成绩<40))
		szSQL1.Empty();
		szSQLlast.Format("考试名称 = '%s'",b_Ename);
		szSQL1 += szSQLlast;
		szSQLlast.Format(" AND 成绩<%s",b_Score);		
		szSQL1 += szSQLlast;
		szSQL2.Empty();
		szSQL2="考试类型代码.考试名称 = '总评成绩'";
		szSQLlast.Format(" AND 成绩<%s",b_MiniScore);
        szSQL2 += szSQLlast;
		szSQLlast.Format(" AND ((%s) or (%s))",szSQL1,szSQL2);
        szSQL += szSQLlast;	



	}
	else //取消时默认为 补考成绩<60
	{