manager.cpp

数据库编程

// Manager.cpp: implementation of the CManager class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "Course.h"
#include "Manager.h"
#include "Connection.h"

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

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////
CConnection conn;
_RecordsetPtr rs;

CManager::CManager()
{

}

CManager::~CManager()
{

}

bool CManager::Search(CAdministrator &s)
{
	rs=NULL;
	conn.open();
	CString sql="select count(*) from 管理员信息 where ID号='"+s.GetNum()+"'and 密码='"+s.GetPassword()+"'";
	rs=conn.execute(sql);
	_variant_t vCount = rs->GetCollect((_variant_t)((long)0));
	if(vCount.lVal==0)
		return false;
	sql="select * from 管理员信息 where ID号='"+s.GetNum()+"'and 密码='"+s.GetPassword()+"'";
	rs=conn.execute(sql);
	_variant_t vname;
	vname=rs->GetCollect("姓名");
	s.SetName((LPCTSTR)_bstr_t(vname));
	return true;
}

bool CManager::Search(CTeacher &s)
{
	rs=NULL;
	conn.open();
	CString sql="select count(*) from 教师信息 where 教师号='"+s.GetNum()+"'and 密码='"+s.GetPassword()+"'";
	rs=conn.execute(sql);
	_variant_t vCount = rs->GetCollect((_variant_t)((long)0));
	if(vCount.lVal==0)
		return false;
	sql="select * from 教师信息 where 教师号='"+s.GetNum()+"'and 密码='"+s.GetPassword()+"'";
	rs=conn.execute(sql);
	_variant_t vname;
	vname=rs->GetCollect("教师姓名");
	s.SetName((LPCTSTR)_bstr_t(vname));
	return true; 
}

bool CManager::Search(CStudent &s)
{
	rs=NULL;
	conn.open();
	CString sql="select count(*) from 学生信息 where 学号='"+s.GetNum()+"'and 密码='"+s.GetPassword()+"'";
	rs=conn.execute(sql);
	_variant_t vCount = rs->GetCollect((_variant_t)((long)0));
	if(vCount.lVal==0)
		return false;
	sql="select * from 学生信息 where 学号='"+s.GetNum()+"'and 密码='"+s.GetPassword()+"'";
	rs=conn.execute(sql);
	_variant_t vname,vclass;
	vname=rs->GetCollect("姓名");
	vclass=rs->GetCollect("班级");
	s.Set((LPCTSTR)_bstr_t(vname),s.GetNum(),s.GetPassword(),(LPCTSTR)_bstr_t(vclass));
	return true; 
}

bool CManager::ReSearch(CAdministrator &s)
{
	rs=NULL;
	conn.open();
	CString sql="select count(*) from 管理员信息 where ID号='"+s.GetNum()+"'";
	rs=conn.execute(sql);
	_variant_t vCount = rs->GetCollect((_variant_t)((long)0));
	if(vCount.lVal==0)
		return false;
	return true; 
}

bool CManager::ReSearch(CTeacher &s)
{
	rs=NULL;
	conn.open();
	CString sql="select count(*) from 教师信息 where 教师号='"+s.GetNum()+"'";
	rs=conn.execute(sql);
	_variant_t vCount = rs->GetCollect((_variant_t)((long)0));
	if(vCount.lVal==0)
		return false;
	return true; 
}

bool CManager::ReSearch(CStudent &s)
{
	rs=NULL;
	conn.open();
	CString sql="select count(*) from 学生信息 where 学号='"+s.GetNum()+"'";
	rs=conn.execute(sql);
	_variant_t vCount = rs->GetCollect((_variant_t)((long)0));
	if(vCount.lVal==0)
		return false;
	return true; 
}

bool CManager::Search(CSC &s)
{
	rs=NULL;
	conn.open();
	CString sql="select count(*) from 选课信息 where 学号='"+s.GetStudent_id()+"'and 课程号='"+s.GetCourse_id()+"'";
	rs=conn.execute(sql);
	_variant_t vCount = rs->GetCollect((_variant_t)((long)0));
	if(vCount.lVal==0)
		return false;
	return true; 
}

bool CManager::Search(CCCourse &s)
{
	rs=NULL;
	conn.open();
	CString sql="select count(*) from 课程信息 where 课程号='"+s.GetId()+"'";
	rs=conn.execute(sql);
	_variant_t vCount = rs->GetCollect((_variant_t)((long)0));
	if(vCount.lVal==0)
		return false;
	return true; 
}

bool CManager::ReSearch(CCCourse &s)
{
	rs=NULL;
	conn.open();
	CString sql="select count(*) from 课程信息 where 课程号='"+s.GetId()+"' and 任课老师='"+s.GetTeacher()+"'";
	rs=conn.execute(sql);
	_variant_t vCount = rs->GetCollect((_variant_t)((long)0));
	if(vCount.lVal==0)
		return false;
	return true; 
}

void CManager::Add(CAdministrator s)
{
	if(ReSearch(s)){
		AfxMessageBox("该ID已被选用!");
		return ;
	}
	conn.open();
	CString sql="insert into 管理员信息(ID号,姓名,密码) values('"+s.GetNum()+"','"+s.GetName()+"','"+s.GetPassword()+"')";
	conn.executeUpdate(sql);
}

void CManager::Add(CTeacher s)
{
	if(ReSearch(s)){
		AfxMessageBox("该ID已被选用!");
		return ;
	}
	conn.open();
	CString sql="insert into 教师信息(教师号,教师姓名,密码) values('"+s.GetNum()+"','"+s.GetName()+"','"+s.GetPassword()+"')";
	conn.executeUpdate(sql);
}

void CManager::Add(CStudent s)
{
	if(ReSearch(s)){
		AfxMessageBox("该ID已被选用!");
		return ;
	}
	conn.open();
	CString sql="insert into 学生信息(学号,姓名,班级,密码) values('"+s.GetNum()+"','"+s.GetName()+"','"+s.GetClass()+"','"+s.GetPassword()+"')";
	conn.executeUpdate(sql);
}

void CManager::Add(CCCourse s)
{
	if(Search(s)){
		AfxMessageBox("该ID已被选用!");
		return ;
	}
	CString upnum,renum;
	upnum.Format("%d",s.GetUpnum());
	renum.Format("%d",s.GetRenum());
	conn.open();
	CString sql="insert into 课程信息(课程号,课程名,任课老师,已选人数,限选人数) values('"+s.GetId()+"','"+s.GetName()+"','"+s.GetTeacher()+"','"+renum+"','"+upnum+"')";
	conn.executeUpdate(sql);
}

void CManager::Add(CSC s)
{
	if(Search(s)){
		AfxMessageBox("该课程已选过!");
		return ;
	}
	CString sql;
	rs=NULL;
	conn.open();
	sql="select count(*) from 课程信息 where 课程号='"+s.GetCourse_id()+"' and 已选人数<限选人数";
	rs=conn.execute(sql);
	_variant_t vCount = rs->GetCollect((_variant_t)((long)0));
	if(vCount.lVal==0){
		AfxMessageBox("该课程不存在或者是选课已满!");
		return ;
	}
	sql="insert into 选课信息(课程号,学号) values('"+s.GetCourse_id()+"','"+s.GetStudent_id()+"')";
	conn.executeUpdate(sql);
	sql="update 课程信息 set 已选人数=已选人数+1 where 课程号='"+s.GetCourse_id()+"'";
	conn.executeUpdate(sql);
}

void CManager::Delete(CAdministrator s)
{
	conn.open();
	CString sql="delete from 管理员信息 where ID号='"+s.GetNum()+"'";	
	conn.executeUpdate(sql);
}

void CManager::Delete(CTeacher s)
{
	conn.open();
	CString sql;
	sql="delete from 选课信息 where 课程号 in (select 课程号 from 课程信息 where 任课老师 in(select 教师姓名 from 教师信息 where 教师号='"+s.GetNum()+"'))";
	conn.executeUpdate(sql);
	sql="delete from 课程信息 where 任课老师 in (select 教师姓名 from 教师信息 where 教师号='"+s.GetNum()+"')";
	conn.executeUpdate(sql);
	sql="delete from 教师信息 where 教师号='"+s.GetNum()+"'";
	conn.executeUpdate(sql);
}

void CManager::Delete(CStudent s)
{
	conn.open();
	CString sql;
	sql="update 课程信息 set 已选人数=已选人数-1 where 课程号 in (select 课程号 from 选课信息 where 学号='"+s.GetNum()+"')";
	conn.executeUpdate(sql);
	sql="delete from 选课信息 where 学号='"+s.GetNum()+"'";
	conn.executeUpdate(sql);
	sql="delete from 学生信息 where 学号='"+s.GetNum()+"'";
	conn.executeUpdate(sql);
}

void CManager::Delete(CSC s)
{
	conn.open();
	CString sql;
	sql="update 课程信息 set 已选人数=已选人数-1 where 课程号 in (select 课程号 from 选课信息 where 课程号='"+s.GetCourse_id()+"' and 学号='"+s.GetStudent_id()+"')";
	conn.executeUpdate(sql);
	sql="delete from 选课信息 where 学号='"+s.GetStudent_id()+"'and 课程号='"+s.GetCourse_id()+"'";
	conn.executeUpdate(sql);
	
}

void CManager::Delete(CCCourse s)
{
	conn.open();
	CString sql;
	sql="delete from 选课信息 where 课程号 in (select 课程号 from 课程信息 where 课程号='"+s.GetId()+"' and 任课老师='"+s.GetTeacher()+"')";
	conn.executeUpdate(sql);
	sql="delete from 课程信息 where 课程号='"+s.GetId()+"' and 任课老师='"+s.GetTeacher()+"'";
	conn.executeUpdate(sql);
}

void CManager::Modify(CAdministrator s)
{
	conn.open();
	CString sql="update  管理员信息 set 密码='"+s.GetPassword()+"' where ID号='"+s.GetNum()+"'";
	conn.executeUpdate(sql);
}

void CManager::Modify(CTeacher s)
{
	conn.open();
	CString sql="update  教师信息 set 密码='"+s.GetPassword()+"' where 教师号='"+s.GetNum()+"'";
	conn.executeUpdate(sql);
}

void CManager::Modify(CStudent s)
{
	conn.open();
	CString sql="update  学生信息 set 密码='"+s.GetPassword()+"' where 学号='"+s.GetNum()+"'";
	conn.executeUpdate(sql);
}

_RecordsetPtr CManager::CourseView()
{
	rs=NULL;
	conn.open();
	CString sql="select * from 课程信息";
	rs=conn.execute(sql);
	return rs;
}

_RecordsetPtr CManager::CourseView(CStudent s)
{
	rs=NULL;
	conn.open();
	CString sql="select * from 课程信息 where 课程号 in (select 课程号 from 选课信息 where 学号='"+s.GetNum()+"')";
	rs=conn.execute(sql);
	return rs;
}

_RecordsetPtr CManager::CourseView(CTeacher s)
{
	rs=NULL;
	conn.open();
	CString sql="select * from 课程信息 where 任课老师 in (select 教师姓名 from 教师信息 where 教师号='"+s.GetNum()+"')";
	rs=conn.execute(sql);
	return rs;
}

_RecordsetPtr CManager::StudentView(CCCourse s)
{
	rs=NULL;
	conn.open();
	CString sql="select * from 学生信息 where 学号 in (select 学号 from 选课信息 where 课程号='"+s.GetId()+"')";
	rs=conn.execute(sql);
	return rs;
}

_RecordsetPtr CManager::StudentView()
{
	rs=NULL;
	conn.open();
	CString sql="select * from 学生信息";
	rs=conn.execute(sql);
	return rs;
}

_RecordsetPtr CManager::TeacherView()
{
	rs=NULL;
	conn.open();
	CString sql="select * from 教师信息";
	rs=conn.execute(sql);
	return rs;
}