depinfo.cpp

&#61557 职责描述：

// DepInfo.cpp: implementation of the  class.
//用来管理班级、专业、年级表
// 1 DepId int 所在部门编号 Identity = Yes Allow Null = False 主键
// 2 DepName varchar(50) 部门名称 Allow Null = False
// 3 Describes varchar(250) 描述 Allow Null = True
// 4 UID int 上级部门编号 Allow Null = False

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

#include "stdafx.h"
#include "StudentCheck.h"
#include "DepInfo.h"
#include "ADOConn.h"
#include "DepEditDlg.h"

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

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CDepInfo::CDepInfo()
{
	DepId = "";
	DepName = "";
	Describes = "";
	UID = 0;
	
	m_AdoConn.InitialDB();

	//初始化数组
	sa_ClassID.RemoveAll();    
	sa_ClassName.RemoveAll();
	sa_ClassDes.RemoveAll();
	sa_ClassSpeID.RemoveAll();    
	sa_ClassGradeID.RemoveAll();
	sa_ClassIsOn.RemoveAll();
}

CDepInfo::~CDepInfo()
{
	m_AdoConn.ClosedDB();
}

// 读取所有部门编号、名称和上级部门编号等信息到数组对象CStringArray
void CDepInfo::LoadDep()
{	
	//跟别读取班级、年级 、专业的数据
	LoadClass();
	LoadGrade();
	LoadSpecialty();
    

}

void CDepInfo::LoadClass()
{
	//设置SELECT语句
	_bstr_t vSQL,vSQL1;
	vSQL = "SELECT * FROM Class ORDER BY ClassID";

	//执行SELETE语句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);
    
//	//初始化数组
//	sa_ClassID.RemoveAll();    
//	sa_ClassName.RemoveAll();
//	sa_ClassDes.RemoveAll();
//	sa_ClassSpeID.RemoveAll();    
//	sa_ClassGradeID.RemoveAll();
// 	sa_ClassIsOn.RemoveAll();
	//执行SELECT语句
	while (m_pRecordset->adoEOF == 0)
	{	
		sa_ClassID.Add((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("ClassID"));
		sa_ClassName.Add((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("ClassName"));
		sa_ClassDes.Add((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("ClassDes"));
		sa_ClassSpeID.Add((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("SpecialtyID"));
		sa_ClassGradeID.Add((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("GradeID"));
        sa_ClassIsOn.Add((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("IsOn"));
		m_pRecordset->MoveNext();
	}

	//断开与数据库的连接
//	m_AdoConn.ExitConnect();
//	m_AdoConn.ClosedDB();
}

void CDepInfo::LoadSpecialty()
{

	//设置SELECT语句
	_bstr_t vSQL,vSQL1;
	vSQL = "SELECT * FROM Specialty ORDER BY ID";

	//执行SELETE语句
	_RecordsetPtr m_pRecordset ;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);
    
	//初始化数组
	sa_SpeID.RemoveAll();    
	sa_SpeName.RemoveAll();
	sa_SpeDes.RemoveAll();
	sa_SpeIsOn.RemoveAll();    

	//执行SELECT语句
	while (m_pRecordset->adoEOF == 0)
	{
		sa_SpeID.Add((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("SpecialtyID"));
		sa_SpeName.Add((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("SpecialtyName"));
		sa_SpeDes.Add((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("SpecialtyDes"));
		sa_SpeIsOn.Add((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("IsOn"));
		m_pRecordset->MoveNext();
	}

	//断开与数据库的连接
// 	m_AdoConn.ExitConnect();
}

void CDepInfo::LoadGrade()
{

	//设置SELECT语句
	_bstr_t vSQL,vSQL1;
	vSQL = "SELECT * FROM Grade ORDER BY ID";

	//执行SELETE语句
	_RecordsetPtr m_pRecordset ;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);
    
	//初始化数组
	sa_GradeID.RemoveAll();    
	sa_GradeName.RemoveAll();

	sa_GradeIsOn.RemoveAll();    

	//执行SELECT语句
	while (m_pRecordset->adoEOF == 0)
	{
		sa_GradeID.Add((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("GradeID"));
		sa_GradeName.Add((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("GradeName"));

		sa_GradeIsOn.Add((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("GradeIsOn"));
		m_pRecordset->MoveNext();
	}


}

// 判断指定的部门名称是否存在
int CDepInfo::HaveName(CString paraName)
{	
    CString strtable,strnode;
	//通过参数判断所修改部门
/*	if (WhichDep==0) 
	{
		strtable=""
	}*/
	//设置SELECT语句
	_bstr_t vSQL;
	vSQL = "SELECT * FROM Class WHERE ClassName = '" + paraName + "'";

	//执行SELECT语句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);

	if (m_pRecordset->adoEOF)
		return -1;
	else
		return 1;

}

// 判断指定的部门是否包含下级部门（如果包含则不能删除）
int CDepInfo::HaveSon(CString paraId)
{	

	//设置SELECT语句
	_bstr_t vSQL;
	if (paraId.GetLength()==3)	//查询年级
		vSQL = " SELECT * FROM Grade WHERE GradeID = '" + paraId + "'";

	if (paraId.GetLength()==5)	//查询专业
		vSQL = " SELECT * FROM Specialty WHERE SpecialtyID = '" + paraId + "'";

	if (paraId.GetLength()==9)	//查询班级
		vSQL = " SELECT * FROM Class WHERE ClassID = '" + paraId + "'";
	
//	vSQL = "SELECT * FROM DepInfo WHERE UID = " + paraId;
	
	//执行SELECT语句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);

	if (m_pRecordset->adoEOF)
		return -1;
	else
		return 1;

}

// 判断指定的部门中是否包含员工（如果有则不能删除）
int CDepInfo::HaveEmp(CString paraId)
{	

	//设置SELECT语句
	_bstr_t vSQL;
	vSQL = "SELECT * FROM EmpInfo WHERE DepId = " + paraId;
	
	//执行SELECT语句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);

	if (m_pRecordset->adoEOF)
		return -1;
	else
		return 1;
}

//根据部门编号读取所有字段值
void CDepInfo::GetInfo(CString paraId)
{

	//设置SELECT语句
	_bstr_t vSQL;
	vSQL = "SELECT * FROM DepInfo WHERE DepId = " + paraId;

	//执行SELETE语句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);
	
	//返回各列的值
	if (m_pRecordset->adoEOF)
		CDepInfo();
	else
	{
		DepId = paraId;
		DepName = (LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("DepName");
		Describes = (LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("Describes");
		UID = atol((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("UID"));
	}
}

// 插入操作
CString  CDepInfo::SqlInsert(CString UperId,CString UpUperID)
{	

	//设置INSERT语句
	CString strUID;
	strUID.Format("%d", UID);
    _bstr_t vSQL;
    //判断对哪个表进行插入
	//首先判断对哪个节点插入

	AfxMessageBox(UperId);
	CString str;


	if (UperId.GetLength()==1)
	{
		//对年级进行插入
    	vSQL ="INSERT INTO Grade (GradeID,GradeName,GradeDes, GradeIsOn) \
		select (Max(GradeID)+1),'" + DepName + "','" + Describes + "','1' from Grade";
	}
	else if(UperId.GetLength()==3)
	{
		//对专业进行插入
		vSQL = "INSERT INTO Specialty (SpecialtyID,SpecialtyName,SpecialtyDes,CollegeID,IsOn) \
	    select (Max(SpecialtyID)+1),'" + DepName + "','" + Describes + "','03','1' from Specialty";
	 
	}
	else
	{
		//对班级进行插入
		vSQL = "INSERT INTO Class (ClassID,ClassName,ClassDes,SpecialtyID,GradeID,IsOn) \
		select (Max(ClassID)+1),'" + DepName + "','" + Describes + "','" + UperId + "','" + UpUperID + "','1'  from Class";
	}
 

	
	//执行INSERT语句
	m_AdoConn.ExecuteSQL(vSQL);	

	//读取最大编号
	CString lDepId;
	vSQL = "SELECT MAX(GradeID) AS MaxId FROM Grade";	

	//执行SELETE语句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);

	//返回各列的值
	if (m_pRecordset->adoEOF)
		lDepId = '0';
	else
		lDepId =(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("MaxId");

	return lDepId;
}

// 更新数据
void CDepInfo::SqlUpdate(CString paraId)
{

	//设置UPDATE语句
	_bstr_t vSQL;
	if (paraId.GetLength()==3) 	//更新年级	
		vSQL = "UPDATE  Grade SET GradeName = '"+ DepName
		      + "', GradeDes= '" + Describes + "' WHERE GradeID= '" + paraId +"'";

	if (paraId.GetLength()==5) 	//更新专业	
		vSQL = "UPDATE  Specialty SET SpecialtyName = '"+ DepName
		      + "', SpecialtyDes= '" + Describes + "' WHERE SpecialtyID= '" + paraId +"'";

	if (paraId.GetLength()==9) 	//更新班级
		vSQL = "UPDATE  Grade SET ClassName = '"+ DepName
		      + "', ClassDes= '" + Describes + "' WHERE ClassID= '" + paraId +"'";

	//执行UPDATE语句
	m_AdoConn.ExecuteSQL(vSQL);	

}

// 删除操作
void CDepInfo::SqlDelete(CString paraId)
{

	//设置DELETE语句
	_bstr_t vSQL, vSQL1;
	if (paraId.GetLength()==3) 	//删除年级	
	{
		vSQL = "DELETE FROM Grade Where GradeID='" + paraId +" '";
		vSQL1 = "DELETE FROM Class Where GradeID='" + paraId +" '";
	}


	if (paraId.GetLength()==5) 	//删除专业	
		vSQL = "DELETE FROM Specialty Where SpecialtyID='" + paraId +" '";

	if (paraId.GetLength()==9) 	//删除班级
		vSQL = "DELETE FROM Class Where ClassID='" + paraId +" '";


	//执行DELETE语句
	m_AdoConn.ExecuteSQL(vSQL);	
	m_AdoConn.ExecuteSQL(vSQL1);
}


void CDepInfo::CreatTree(HTREEITEM _hnode, CTreeCtrl & _pCurTree)
{
    int i,j,k;
	HTREEITEM hFristChildLevel, hSecondChildLevel, hLeafageLevel;
 	CString strCurName;
	for (i=0; i < sa_GradeID.GetSize(); i++)
	{
		strCurName = sa_GradeName.GetAt(i);
		hFristChildLevel=_pCurTree.InsertItem(strCurName, _hnode);
		_pCurTree.SetItemData(hFristChildLevel, atol(sa_GradeID.GetAt(i)));
		
		for (j=0; j < sa_SpeID.GetSize(); j++)
		{
			strCurName = sa_SpeName.GetAt(j);
			hSecondChildLevel =_pCurTree.InsertItem(strCurName, hFristChildLevel);
			_pCurTree.SetItemData(hSecondChildLevel, atol(sa_SpeID.GetAt(j)));

			for(k=0; k < sa_ClassID.GetSize(); k++)
			{
				CString str1,str2;
				str1=sa_ClassSpeID.GetAt(k);
				str2=sa_ClassGradeID.GetAt(k);
				if(str1.Compare(sa_SpeID.GetAt(j))==0 &&
 										str2.Compare(sa_GradeID.GetAt(i))==0)
				{
					hLeafageLevel =_pCurTree.InsertItem(sa_ClassName.GetAt(k),hSecondChildLevel);
					_pCurTree.SetItemData(hLeafageLevel, atol(sa_ClassID.GetAt(k)));
				}
			}
 		}
	}

}

void CDepInfo::AddtoTree()

{
  /*
	HTREEITEM m_root;				//保存Tree控件的根节点
	HTREEITEM m_child[20];
	HTREEITEM m_SubChild[20][20];
	HTREEITEM m_SubChild3[20][20][30];
	int i,j,k;
	m_root = m_tree.InsertItem("环境学院");//添加根节点
    m_tree.SetItemData(m_root,0);	
	//使用数组的方法以次将年级、专业、班级添加到TreeView控件中  
	//依次访问dep对象中的部门数组，查找满足条件的记录添加到Tree控件中
	//插入三级节点
	for( i=0; i<dep.sa_GradeID.GetSize(); i++)
	{
		m_child[i]=m_tree.InsertItem(dep.sa_GradeName.GetAt(i),m_root);
		m_tree.SetItemData(m_child[i], atol(dep.sa_GradeID.GetAt(i)));
		for( j=0; j<dep.sa_SpeID.GetSize(); j++)


		{
			m_SubChild[i][j]=m_tree.InsertItem(dep.sa_SpeName.GetAt(j),m_child[i]);
			m_tree.SetItemData(m_SubChild[i][j], atol(dep.sa_SpeID.GetAt(j)));
			for( k=0; k<dep.sa_ClassID.GetSize();k++)


			{

				CString str1,str2;
				str1=dep.sa_ClassSpeID.GetAt(k);
				str2=dep.sa_ClassGradeID.GetAt(k);

				if(str1.Compare(dep.sa_SpeID.GetAt(j))==0 &&
							     str2.Compare(dep.sa_GradeID.GetAt(i))==0)
				{
					m_SubChild3[i][j][k]=m_tree.InsertItem(dep.sa_ClassName.GetAt(k),m_SubChild[i][j]);
					m_tree.SetItemData(m_SubChild3[i][j][k], atol(dep.sa_ClassID.GetAt(k)));
				}
			
			}
		}
	}
	m_tree.Expand(m_root, TVE_EXPAND); //展开根节点*/
}