viewstu.cpp

成绩管理系统

	pctrl->SetImageList(&pFrame->m_ImageListFORstu,LVSIL_SMALL);
	pctrl->InsertColumn(0,"学号",LVCFMT_CENTER,120);
	pctrl->InsertColumn(1,"学生姓名",LVCFMT_LEFT,80);
	pctrl->InsertColumn(2,"性别",LVCFMT_LEFT,40);	
	pctrl->InsertColumn(3,"出生日期",LVCFMT_LEFT,90);
	pctrl->InsertColumn(4,"总学分",LVCFMT_LEFT,60);
	pctrl->InsertColumn(5,"班级名称",LVCFMT_LEFT,130);
    pctrl->InsertColumn(6,"专业名称",LVCFMT_LEFT,130);
    pctrl->InsertColumn(7,"所在系名",LVCFMT_LEFT,130);
	pctrl->InsertColumn(8,"手机",LVCFMT_LEFT,100);
	pctrl->InsertColumn(9,"政治面貌",LVCFMT_LEFT,100);
    curSQL = "SELECT 学号,姓名,性别,出生日期,总得学分,班级名称,专业名称,系名,手机,政治面貌 FROM 学生基本信息,班级信息,专业信息,系别信息 WHERE 学生基本信息.所属班级 = 班级信息.班级代码 AND 班级信息.所属专业 = 专业信息.专业代码 AND 专业信息.所属系别 = 系别信息.系别代码";
	RefreshStuList(curSQL + " order by 学号,姓名");
}

void CViewStu::OnSize(UINT nType, int cx, int cy) 
{
	CFormView::OnSize(nType, cx, cy);	
	// TODO: Add your message handler code here
	::SetWindowPos(*GetDlgItem(IDC_LIST_Stu),NULL,130,0,cx-130,cy,SWP_NOZORDER);		
}

int CViewStu::RefreshStuList(CString szSQL)
{
	int state=0;  //返回记录数
	CListCtrl *pList = (CListCtrl*)GetDlgItem(IDC_LIST_Stu);
	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;
	}
	while ( !p_set->adoEOF )
	{  
		state++;
             //为空值时不能这个(char*)取,切记!
		CString Sno =   (char*) ((_bstr_t) p_set->Fields->Item["学号"]->Value);
		CString Sname = (char*) ((_bstr_t) p_set->Fields->Item["姓名"]->Value);
		_variant_t vSex =  p_set->GetCollect("性别");
		_variant_t vBirth = p_set->GetCollect("出生日期");
		_variant_t vCent = p_set->GetCollect("总得学分");
		_variant_t vClass = p_set->GetCollect("班级名称");
		_variant_t vDname = p_set->GetCollect("专业名称");
		_variant_t vPname = p_set->GetCollect("系名");
		_variant_t vMobile = p_set->GetCollect("手机");
		_variant_t vpolity = p_set->GetCollect("政治面貌");

		int iItem = pList->InsertItem(pList->GetItemCount(),Sno,0);
        CString Sex = "Null";
		CString Birth = "Null";
		CString Class = "Null";
		CString Dname = "Null";
		CString Pname = "Null";
		CString Mobile = "Null";
        CString polity = "Null";

		if (vSex.vt != VT_NULL) Sex = (BSTR) vSex.bstrVal;		
		if (vBirth.vt !=VT_NULL) Birth = (BSTR) vBirth.bstrVal;
		float tempf = vCent.dblVal;
		CString Cent; Cent.Format("%f",tempf);	
		if (vClass.vt !=VT_NULL) Class = (BSTR) vClass.bstrVal;
		if (vDname.vt !=VT_NULL) Dname = (BSTR) vDname.bstrVal;
		if (vPname.vt !=VT_NULL) Pname = (BSTR) vPname.bstrVal;
		if (vMobile.vt !=VT_NULL) Mobile = (BSTR) vMobile.bstrVal;
		if (vpolity.vt !=VT_NULL) polity = (BSTR) vpolity.bstrVal;

		pList->SetItemText(iItem,1,Sname);			
		pList->SetItemText(iItem,2,Sex);			
		pList->SetItemText(iItem,3,Birth);			
		pList->SetItemText(iItem,4,Cent);			
		pList->SetItemText(iItem,5,Class);
		pList->SetItemText(iItem,6,Dname);
		pList->SetItemText(iItem,7,Pname);
		pList->SetItemText(iItem,8,Mobile);
		pList->SetItemText(iItem,9,polity);

		p_set->MoveNext();
	}
	p_set->Close();	
	CString Scount;
	Scount.Format("共有%d条记录...",state);
    pList->InsertItem(pList->GetItemCount(),Scount,0);
	return state;	
}


void CViewStu::OnListNext() //下一条
{
	CListCtrl *pList = (CListCtrl*)GetDlgItem(IDC_LIST_Stu);
	if(pList->GetSelectedCount()!=1)
	{
		pList->SetSelectionMark(0);
		pList->SetItemState(0, LVIS_SELECTED, LVIS_SELECTED);
		ASSERT(pList->GetItemState(0, LVIS_SELECTED) == LVIS_SELECTED);
	}
	else
	{  
		int n=pList->GetSelectionMark();
		if (n!=pList->GetItemCount()-1)
		{   
			pList->SetSelectionMark(n+1);
			pList->SetItemState(n+1, LVIS_SELECTED, LVIS_SELECTED);
			ASSERT(pList->GetItemState(n+1, LVIS_SELECTED) == LVIS_SELECTED);
		}
		else 
		{  
			pList->SetSelectionMark(0);
			pList->SetItemState(0, LVIS_SELECTED, LVIS_SELECTED);
			ASSERT(pList->GetItemState(0, LVIS_SELECTED) == LVIS_SELECTED);
			
		}
		
	}
		
}

void CViewStu::OnListPrv() //上一条
{
	
	CListCtrl *pList = (CListCtrl*)GetDlgItem(IDC_LIST_Stu);
	if(pList->GetSelectedCount()!=1)
	{
		pList->SetSelectionMark(pList->GetItemCount()-1);
		pList->SetItemState(pList->GetItemCount()-1, LVIS_SELECTED, LVIS_SELECTED);
		ASSERT(pList->GetItemState(pList->GetItemCount()-1, LVIS_SELECTED) == LVIS_SELECTED);
	}
	else
	{   
		int n=pList->GetSelectionMark();
		if (n!=0)
		{   
			pList->SetSelectionMark(n-1);
			pList->SetItemState(n-1, LVIS_SELECTED, LVIS_SELECTED);
			ASSERT(pList->GetItemState(n-1, LVIS_SELECTED) == LVIS_SELECTED);
		}
		else 
		{ 
			pList->SetSelectionMark(pList->GetItemCount()-1);
			pList->SetItemState(pList->GetItemCount()-1, LVIS_SELECTED, LVIS_SELECTED);
			ASSERT(pList->GetItemState(pList->GetItemCount()-1, LVIS_SELECTED) == LVIS_SELECTED);
			
		}
	}		
}

void CViewStu::OnDeleteGrade() //工具栏的删除按钮
{
	CViewStu::OnDELStu();	
}

void CViewStu::OnBUTTONrefresh() //工具栏的刷新按钮
{
	curSQL = "SELECT 学号,姓名,性别,出生日期,总得学分,班级名称,专业名称,系名,手机,政治面貌 FROM 学生基本信息,班级信息,专业信息,系别信息 WHERE 学生基本信息.所属班级 = 班级信息.班级代码 AND 班级信息.所属专业 = 专业信息.专业代码 AND 专业信息.所属系别 = 系别信息.系别代码";
	RefreshStuList(curSQL + " order by 学号,姓名");
}

void CViewStu::OnColumnclickLISTStu(NMHDR* pNMHDR, LRESULT* pResult) 
{
	NM_LISTVIEW* pNMListView = (NM_LISTVIEW*)pNMHDR;
	// TODO: Add your control notification handler code here
		// pNMListView->iSubItem;  //返回点中的第几列
	CString szSQL;
	if( pNMListView->iSubItem == m_nSortedCol )
		m_fAsc = !m_fAsc;
	else
	{
		m_fAsc = TRUE;
		m_nSortedCol = pNMListView->iSubItem;
	}
	switch (m_nSortedCol)
	{
	case 0:  //按班级排
        if (m_fAsc)
			szSQL = curSQL + " ORDER BY 学生基本信息.学号";
		else 
			szSQL =  curSQL + " ORDER BY 学生基本信息.学号 DESC";
		break;
	case 1:
		if (m_fAsc)
			szSQL =  curSQL + " ORDER BY 学生基本信息.姓名";
		else 
			szSQL =  curSQL + " ORDER BY 学生基本信息.姓名 DESC";
		break;
	case 2:
		if (m_fAsc)
			szSQL =  curSQL + " ORDER BY 学生基本信息.性别";
		else 
			szSQL =  curSQL + " ORDER BY 学生基本信息.性别 DESC";
		break;
	case 3:
		if (m_fAsc)
			szSQL =  curSQL + " ORDER BY 学生基本信息.出生日期";
		else 
			szSQL =  curSQL + " ORDER BY 学生基本信息.出生日期 DESC";
		break;
	case 4:
		if (m_fAsc)
			szSQL =  curSQL + " ORDER BY 学生基本信息.总得学分";
		else 
			szSQL =  curSQL + " ORDER BY 学生基本信息.总得学分 DESC";
		break;
	case 5:
		if (m_fAsc)
			szSQL =  curSQL + " ORDER BY 班级信息.班级名称";
		else 
			szSQL =  curSQL + " ORDER BY 班级信息.班级名称 DESC";
		break;
	case 6:
		if (m_fAsc)
			szSQL =  curSQL + " ORDER BY 专业信息.专业名称";
		else 
			szSQL =  curSQL + " ORDER BY 专业信息.专业名称 DESC";
		break;
	case 7:
		if (m_fAsc)
			szSQL =  curSQL + " ORDER BY 系别信息.系名";
		else 
			szSQL =  curSQL + " ORDER BY 系别信息.系名 DESC";
		break;
	case 8:
		if (m_fAsc)
			szSQL =  curSQL + " ORDER BY 学生基本信息.手机";
		else 
			szSQL =  curSQL + " ORDER BY 学生基本信息.手机 DESC";
		break;
	case 9:
		if (m_fAsc)
			szSQL =  curSQL + " ORDER BY 学生基本信息.政治面貌";
		else 
			szSQL =  curSQL + " ORDER BY 学生基本信息.政治面貌 DESC";
		break;		
    default:
		szSQL = curSQL;
		break;
	}
	//AfxMessageBox(szSQL);
     RefreshStuList(szSQL);
	*pResult = 0;
}

void CViewStu::OnRdblclkLISTStu(NMHDR* pNMHDR, LRESULT* pResult) 
{
	// TODO: Add your control notification handler code here
	OnModifyStu();
	*pResult = 0;
}

void CViewStu::OnOUTExcel() 
{ 
	ToExcel("open");
}
void CViewStu::ToExcel(CString opr)
{
	//检测如果打开了excel,则关闭进程
  HANDLE han,Ophan; char pname[50]="EXCEL.EXE";
  tagPROCESSENTRY32 lppe;
  BOOL ok=false,oknext=true;
   han=CreateToolhelp32Snapshot(TH32CS_SNAPPROCESS,NULL);
   ok=Process32First(han,&lppe);  //tagPROCESSENTRY32类型的结构体，包含有进程ID DWORD th32ProcessID; 和进程名字char szExeFile[MAX_PATH];  信息
  if (ok)
    {  while (oknext)
       {
         oknext=Process32Next(han,&lppe);
       if(!strcmp(lppe.szExeFile,pname))
         {        
           Ophan=OpenProcess(PROCESS_ALL_ACCESS,NULL,lppe.th32ProcessID);  //???不太懂
           TerminateProcess(Ophan,0);  //kill
           //Ophan=OpenProcess(PROCESS_CREATE_PROCESS,NULL,lppe.th32ProcessID);
          }
       }
    }//关闭excel完成

   	BeginWaitCursor();
	CDatabase database;
	CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // Excel安装驱动
	CString sExcelFile = "c:\\STUoutExcel.xls";                // 要建立的Excel文件
	CString sSql;
//	::ShellExecute(NULL,"close",sExcelFile,0,0,0);  //关闭外部程序打开文件
	TRY
	{
		CFile::Remove(sExcelFile);
	}
	CATCH( CFileException, e )
	{
#ifdef _DEBUG
		afxDump << "File " << sExcelFile << " cannot be removed\n";
#endif
	}
	END_CATCH		
	TRY
	{ 
		sSql.Format("DRIVER={%s};DSN="";FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",
			sDriver,sExcelFile,sExcelFile);
		// 创建数据库 (既Excel表格文件)
		if (database.OpenEx(sSql,CDatabase::noOdbcDialog))
		{
			 //sSql = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[grade]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[grade] GO";
			sSql = "CREATE TABLE student (学号 TEXT,姓名 TEXT,性别 TEXT,出生日期 TEXT,总学分 NUMBER,班级名称 TEXT,专业名称 TEXT,所在系名 TEXT,手机 TEXT,政治面貌 TEXT)";
			database.ExecuteSQL(sSql);		
			// 插入数值
			CListCtrl *pList = (CListCtrl*)GetDlgItem(IDC_LIST_Stu);
			for (int i=0;i<pList->GetItemCount()-1;i++)
			{  			
				CString Sno,Sname,Ssex,Sbirth,TScore,Class,Dname,Pname,Sphone,Sdang;
				
				Sno = pList->GetItemText(i,0);
				Sname = pList->GetItemText(i,1);
				Ssex = pList->GetItemText(i,2);
				Sbirth = pList->GetItemText(i,3);								
				TScore = pList->GetItemText(i,4);				
				Class = pList->GetItemText(i,5);
				Dname = pList->GetItemText(i,6);
				Pname = pList->GetItemText(i,7);
			    Sphone = pList->GetItemText(i,8);
                Sdang = pList->GetItemText(i,9);
				sSql.Format("INSERT INTO student VALUES ('%s','%s','%s','%s',%s,'%s','%s','%s','%s','%s')",Sno,Sname,Ssex,Sbirth,TScore,Class,Dname,Pname,Sphone,Sdang);
			//	AfxMessageBox(sSql);
				database.ExecuteSQL(sSql);	
			}
		}
		database.Close();
	}
	CATCH_ALL(CDBException, e)
	{
		TRACE1("Excel驱动没有安装: %s",sDriver);	 
	}
	END_CATCH_ALL

	::ShellExecute(NULL,opr,sExcelFile,0,0,SW_SHOW);  //调用外部程序打开
	EndWaitCursor();
}

void CViewStu::OnFilePrint() 
{
  ToExcel("print");	
}

void CViewStu::OnDblclkLISTStu(NMHDR* pNMHDR, LRESULT* pResult) 
{
	// TODO: Add your control notification handler code here
	OnModifyStu();
	*pResult = 0;
}