column.cpp

一个电视台专用的信息管理软件源代码

// Column.cpp: implementation of the CColumn class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"

#include "Column.h"

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

#endif

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

CColumn::CColumn()
{

}

CColumn::~CColumn()
{

}


int CColumn::GetUID()
{
	return UID;
}

void CColumn::SetUID(int iUID)
{
	UID=iUID;
}


CString CColumn::GetName()
{
	return Name;
}
long CColumn::GetChannelID()
{
	return ChannelID;
}

void CColumn::SetChannelID(long lChannelID)
{
	ChannelID = lChannelID;
}
void CColumn::SetName(CString cName)
{
	Name = cName;
}


CString CColumn::GetDesc()
{
	return Desc;
}

void CColumn::SetDesc(CString cDesc)
{
	Desc = cDesc;
}


//数据库操作

	
void CColumn::sql_insert(CString cName,CString cDesc,long lChannelID,long& iFlg,CString& cMessage)
{
	try
	{
		m_pCommand.CreateInstance(__uuidof(Command));
		m_pCommand->ActiveConnection=m_pConnection;
		m_pCommand->CommandType=adCmdStoredProc;
		m_pCommand->CommandText=_bstr_t("ins_Columns");
		
		_variant_t vvar1,vvar2,vvar3,vvar4,vvar5;
	
		vvar1=_variant_t(_bstr_t(cName));
		vvar2=_variant_t(_bstr_t(cDesc));
		vvar3=_variant_t(lChannelID);
		vvar4=_variant_t(iFlg);
		vvar5=_variant_t(_bstr_t(cMessage));

		_ParameterPtr mp_var1,mp_var2,mp_var3,mp_var4,mp_var5;
		mp_var1.CreateInstance(__uuidof(Parameter));
		mp_var2.CreateInstance(__uuidof(Parameter));
		mp_var3.CreateInstance(__uuidof(Parameter));
		mp_var4.CreateInstance(__uuidof(Parameter));
		mp_var5.CreateInstance(__uuidof(Parameter));
	
		mp_var1=m_pCommand->CreateParameter
		(
		_bstr_t("var1"),
		adVarChar,
		adParamInput,
		50,
		vvar1
		);
		m_pCommand->Parameters->Append(mp_var1); 

		mp_var2=m_pCommand->CreateParameter
		(
		_bstr_t("var2"),
		adVarChar,
		adParamInput,
		200,
		vvar2
		);
		m_pCommand->Parameters->Append(mp_var2); 
		
		mp_var3=m_pCommand->CreateParameter
		(
		_bstr_t("var3"),
		adBigInt,
		adParamInput,
		5,
		vvar3
		);
		m_pCommand->Parameters->Append(mp_var3); 
	
		mp_var4=m_pCommand->CreateParameter
		(
		_bstr_t("var4"),
		adBigInt,
		adParamOutput,
		3,
		vvar4
		);
		m_pCommand->Parameters->Append(mp_var4); 

		mp_var5=m_pCommand->CreateParameter
		(
		_bstr_t("var5"),
		adVarChar,
		adParamOutput,
		200,
		vvar5
		);
		m_pCommand->Parameters->Append(mp_var5); 


		_variant_t vNull;
		vNull.vt=VT_ERROR;
		vNull.scode=DISP_E_PARAMNOTFOUND;
		m_pCommand->Execute(&vNull,&vNull,adCmdStoredProc);

		iFlg=mp_var4->Value;
		cMessage=mp_var5->Value.bstrVal;
		}
		catch(_com_error &error)
		{
			AfxMessageBox(error.ErrorMessage(),MB_OK,0);
			AfxMessageBox(error.Description(),MB_OK,0);
			AfxMessageBox("ADO错误!",MB_OK,0);
		}

}


void CColumn::sql_update(long iUID,CString cName,CString cDesc,long lChannelID,long& iFlg,CString& cMessage)
{

	try
	{
		m_pCommand.CreateInstance(__uuidof(Command));
		m_pCommand->ActiveConnection=m_pConnection;
		m_pCommand->CommandType=adCmdStoredProc;
		m_pCommand->CommandText=_bstr_t("edt_Columns");
		
		_variant_t vvar1,vvar2,vvar3,vvar4,vvar5,vvar6;
	
		vvar1=_variant_t(_bstr_t(cName));
		vvar2=_variant_t(_bstr_t(cDesc));
		vvar3=_variant_t(lChannelID);
		vvar4=_variant_t(iUID);
		vvar5=_variant_t(iFlg);
		vvar6=_variant_t(_bstr_t(cMessage));

		_ParameterPtr mp_var1,mp_var2,mp_var3,mp_var4,mp_var5,mp_var6;
		mp_var1.CreateInstance(__uuidof(Parameter));
		mp_var2.CreateInstance(__uuidof(Parameter));
		mp_var3.CreateInstance(__uuidof(Parameter));
		mp_var4.CreateInstance(__uuidof(Parameter));
		mp_var5.CreateInstance(__uuidof(Parameter));
		mp_var6.CreateInstance(__uuidof(Parameter));
	
		mp_var1=m_pCommand->CreateParameter
		(
		_bstr_t("var1"),
		adVarChar,
		adParamInput,
		50,
		vvar1
		);
		m_pCommand->Parameters->Append(mp_var1); 

		mp_var2=m_pCommand->CreateParameter
		(
		_bstr_t("var2"),
		adVarChar,
		adParamInput,
		200,
		vvar2
		);
		m_pCommand->Parameters->Append(mp_var2); 
		
		mp_var3=m_pCommand->CreateParameter
		(
		_bstr_t("var3"),
		adBigInt,
		adParamInput,
		5,
		vvar3
		);
		m_pCommand->Parameters->Append(mp_var3); 

		mp_var4=m_pCommand->CreateParameter
		(
		_bstr_t("var4"),
		adBigInt,
		adParamInput,
		5,
		vvar4
		);
		m_pCommand->Parameters->Append(mp_var4); 
	
		mp_var5=m_pCommand->CreateParameter
		(
		_bstr_t("var5"),
		adBigInt,
		adParamOutput,
		3,
		vvar5
		);
		m_pCommand->Parameters->Append(mp_var5); 

		mp_var6=m_pCommand->CreateParameter
		(
		_bstr_t("var6"),
		adVarChar,
		adParamOutput,
		200,
		vvar6
		);
		m_pCommand->Parameters->Append(mp_var6); 


		_variant_t vNull;
		vNull.vt=VT_ERROR;
		vNull.scode=DISP_E_PARAMNOTFOUND;
		m_pCommand->Execute(&vNull,&vNull,adCmdStoredProc);

		iFlg=mp_var5->Value;
		cMessage=mp_var6->Value.bstrVal;
		}
		catch(_com_error &error)
		{
			AfxMessageBox(error.ErrorMessage(),MB_OK,0);
			AfxMessageBox(error.Description(),MB_OK,0);
			AfxMessageBox("ADO错误!",MB_OK,0);
		}

}

void CColumn::sql_delete(long iUID,long& iFlg,CString& cMessage)
{

	try
	{
		m_pCommand.CreateInstance(__uuidof(Command));
		m_pCommand->ActiveConnection=m_pConnection;
		m_pCommand->CommandType=adCmdStoredProc;
		m_pCommand->CommandText=_bstr_t("del_Columns");
		
		_variant_t vvar1,vvar2,vvar3;
	
		
		vvar1=_variant_t(iUID);
		vvar2=_variant_t(iFlg);
		vvar3=_variant_t(_bstr_t(cMessage));

		_ParameterPtr mp_var1,mp_var2,mp_var3;
		mp_var1.CreateInstance(__uuidof(Parameter));
		mp_var2.CreateInstance(__uuidof(Parameter));
		mp_var3.CreateInstance(__uuidof(Parameter));
		
		mp_var1=m_pCommand->CreateParameter
		(
		_bstr_t("var1"),
		adBigInt,
		adParamInput,
		5,
		vvar1
		);
		m_pCommand->Parameters->Append(mp_var1); 
	
		mp_var2=m_pCommand->CreateParameter
		(
		_bstr_t("var2"),
		adBigInt,
		adParamOutput,
		3,
		vvar2
		);
		m_pCommand->Parameters->Append(mp_var2); 

		mp_var3=m_pCommand->CreateParameter
		(
		_bstr_t("var3"),
		adVarChar,
		adParamOutput,
		200,
		vvar3
		);
		m_pCommand->Parameters->Append(mp_var3); 


		_variant_t vNull;
		vNull.vt=VT_ERROR;
		vNull.scode=DISP_E_PARAMNOTFOUND;
		m_pCommand->Execute(&vNull,&vNull,adCmdStoredProc);

		iFlg=mp_var2->Value;
		cMessage=mp_var3->Value.bstrVal;
		}
		catch(_com_error &error)
		{
			AfxMessageBox(error.ErrorMessage(),MB_OK,0);
			AfxMessageBox(error.Description(),MB_OK,0);
			AfxMessageBox("ADO错误!",MB_OK,0);
		}
		 AfxMessageBox(cMessage); 
	
}
	


//根据员工编号读取所有字段值
void CColumn::GetData(int UID)
{

	//设置SELECT语句
	CString strUID;
	strUID.Format("%d",UID);
	vSQL = "SELECT * FROM Columns WHERE UID=" + strUID;
	//执行SELETE语句
	m_pRecordset = GetRecordSet(vSQL);

	//返回各列的值
	if (m_pRecordset->adoEOF)
		CColumn();
	else
	{
		Name = (LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("Name");
		Desc = (LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("Description");
		ChannelID = atoi((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("ChannelID"));
	}
}

//PASS
void CColumn:: GetAllColumn(CStringArray& allColumns,int iChannelID)
{
	//设置SELECT语句
	CString strChannelID;
	strChannelID.Format("%d",iChannelID);
	vSQL = "SELECT Name FROM Columns WHERE IsDelete = 0 AND ChannelID="+strChannelID;

	m_pRecordset = GetRecordSet(vSQL);
	
	while(!m_pRecordset->adoEOF)
	{
		allColumns.Add((_bstr_t)m_pRecordset->GetCollect("Name"));

		m_pRecordset->MoveNext();
	}
}

//PASS
int CColumn::getUIDByColumnName(CString columnName)
{
	//设置SELECT语句
	
	vSQL = "SELECT UID FROM Columns WHERE Name='"+columnName+"'";
	//执行SELETE语句

	m_pRecordset = GetRecordSet(vSQL);
	
	if (m_pRecordset->adoEOF)
	{
		CColumn();
	}
	else
	{
		
		UID = atoi((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("UID"));
		
	}
	return UID;
}

void CColumn::ShowList(CListCtrl& listctrl,CString strSQL)
{
	int i=0;
    LV_ITEM lvitem;
	lvitem.mask=LVIF_TEXT | LVIF_IMAGE | LVIF_STATE;
	lvitem.state=0;
	lvitem.stateMask=0;

	CString strUID,strChannelName,strDescription,strName;

	//设置SELECT语句
	vSQL = "SELECT Columns.UID as UID,Channel.Name as ChannelName,Columns.Name as Name,Columns.Description as Description  FROM Columns,Channel WHERE Channel.IsDelete = 0 AND Columns.IsDelete = 0 AND ChannelID= Channel.UID" + strSQL;
	//执行SELETE语句
	m_pRecordset = GetRecordSet(vSQL);

    listctrl.DeleteAllItems();
	
	 while(!m_pRecordset->adoEOF)
	{   
		lvitem.iItem=i;
		lvitem.iSubItem=0;
		_variant_t varUID = m_pRecordset->GetCollect("UID");
		if(varUID.vt != VT_NULL)
		{
			strUID=(LPCTSTR)(_bstr_t)varUID;
		}
		
		lvitem.pszText=(LPTSTR)(LPCTSTR)strUID;  
		listctrl.InsertItem(&lvitem);

		
		_variant_t varChannelName = m_pRecordset->GetCollect("ChannelName");
		if(varChannelName.vt != VT_NULL)
		{
			strChannelName=(LPCTSTR)(_bstr_t)varChannelName;
		}
		listctrl.SetItemText(i,2,strChannelName);

		_variant_t varName = m_pRecordset->GetCollect("Name");
		if(varName.vt != VT_NULL)
		{
			strName=(LPCTSTR)(_bstr_t)varName;
		}
		listctrl.SetItemText(i,1,strName);

		_variant_t varDescription = m_pRecordset->GetCollect("Description");
		if(varDescription.vt != VT_NULL)
		{
			strDescription=(LPCTSTR)(_bstr_t)varDescription;
		}
		listctrl.SetItemText(i,3,strDescription);


		i++;
		m_pRecordset->MoveNext();
	}

}
void CColumn::GetAllColumn(CStringArray& allColumns,CString strSQL)
{
	//设置SELECT语句
	vSQL = "SELECT Name FROM Columns WHERE IsDelete = 0 " + strSQL;
	//执行SELETE语句
	m_pRecordset = GetRecordSet(vSQL);
	while(!m_pRecordset->adoEOF)
	{
		allColumns.Add(_T((_bstr_t)m_pRecordset->GetCollect("Name")));
		m_pRecordset->MoveNext();
	}

}
int CColumn::GetIDByName(CString cName)
{
	int iUID=0;
	vSQL = "SELECT UID FROM Columns WHERE IsDelete = 0 AND Name='" + cName+"'";
	//执行SELETE语句
	m_pRecordset = GetRecordSet(vSQL);
	//返回各列的值
	if (m_pRecordset->adoEOF)
		CColumn();
	else
	{
		
		iUID = atoi((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("UID"));
	
	}
	
	return iUID;

}

void CColumn::ShowTree(CTreeCtrl& treectrl,CString strCondition)
{

	treectrl.DeleteAllItems();

	HTREEITEM root = treectrl.InsertItem("电台",0,1,TVI_ROOT,TVI_LAST);

	vSQL = "SELECT *  FROM Channel WHERE IsDelete = 0 " +strCondition;
	//执行SELETE语句
	m_pRecordset = GetRecordSet(vSQL);

   	int i=0;	
	 while(!m_pRecordset->adoEOF)
	 {   
		HTREEITEM son1[MAX];
		CString strChannelUID,strChannelName;

		strChannelUID=(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("UID");

		strChannelName =(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("Name");

		son1[i] = treectrl.InsertItem(_T(strChannelName), root);

		_RecordsetPtr m_pRecordsetTemp;	
		m_pRecordsetTemp.CreateInstance(__uuidof(Recordset));
		// 取得表中的记录
		
	
		_bstr_t vSQLTemp = "SELECT *  FROM Columns WHERE IsDelete = 0 AND ChannelID= " + strChannelUID  ;
	
	    m_pRecordsetTemp->Open(vSQLTemp,m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
		while(!m_pRecordsetTemp->adoEOF)
		{   
		
			CString strColumnName;

			strColumnName =(LPCTSTR)(_bstr_t)m_pRecordsetTemp->GetCollect("Name");

			treectrl.InsertItem(_T(strColumnName), son1[i]);
			m_pRecordsetTemp->MoveNext();
		}		
		
		i++;
		m_pRecordset->MoveNext();
	}
}