addrecord.cpp

KTV点歌系统

// AddRecord.cpp: implementation of the CAddRecord class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "songserver.h"
#include "AddRecord.h"

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

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

//连接数据库的指针
_ConnectionPtr CAddRecord::m_pConnectionPtr = NULL;  

CAddRecord::CAddRecord()
{
	m_pstrUserlist = NULL;
}

CAddRecord::~CAddRecord()
{
 	if( m_pstrUserlist != NULL )
	{
		delete[] m_pstrUserlist;
		m_pstrUserlist = NULL;
	}
}

//-------------------------------------------------------
//从外界得到连接到数据的指针
//-------------------------------------------------------
void CAddRecord::SetConnectionPtr(_ConnectionPtr pConnectionPtr)
{
    m_pConnectionPtr = pConnectionPtr;
}

BOOL CAddRecord::PointerIsNull()
{
	if( m_pConnectionPtr == NULL )
	{
		AfxMessageBox("连接数据库的指针为空，请先设置指针");
		return false;
	}

	return true;
}

//--------------------------------------------------
//下面为歌曲表操作
//--------------------------------------------------
BOOL CAddRecord::SaveRecord(CString strSongname, 
							CString strNameLen, 
							CString strSpeech, 
							CString strFstalphabet, 
							CString strSinger, 
							CString strSongpath, 
							BOOL bNewrecord, 
							int nID)
{
	if( !PointerIsNull() )
		return false;

	if( strSongname.IsEmpty() || strNameLen.IsEmpty() || strSpeech.IsEmpty() 
		|| strFstalphabet.IsEmpty() || strSinger.IsEmpty() || strSongpath.IsEmpty())
	{
		AfxMessageBox("数据不能为空,请重新输入.");
		return false;
	}

  	// 在ADO操作中建议语句中要常用try...catch()来捕获错误信息，
	// 因为它有时会经常出现一些想不到的错误。
	try
	{
		_RecordsetPtr pSonglistPtr;
		pSonglistPtr.CreateInstance(__uuidof(Recordset));
 		if( bNewrecord )//如果是新记录,则按ID倒序排序，得到当前最大的ID,新记录的ID自动加1
		{
			pSonglistPtr->Open( "select * from songlist order by ID DESC", 
				m_pConnectionPtr.GetInterfacePtr(), 
				adOpenDynamic,
				adLockOptimistic,
				adCmdText );
			
			int nMaxid;
			_variant_t vtMaxid;
			if( !pSonglistPtr->BOF )//根据排序结果,得到最大ID
			{
				pSonglistPtr->MoveFirst();
				vtMaxid = pSonglistPtr->GetCollect("ID");
				nMaxid = atoi((_bstr_t)vtMaxid);
			}
			else//表为空,最大ID值为0
				nMaxid = 0;
			
			char szIndex[10];
			itoa(++nMaxid, szIndex,   10);
			
			pSonglistPtr->AddNew();//执行新增操作
			pSonglistPtr->PutCollect("ID",          (_variant_t)szIndex);//写ID
		}
		else//如果不是新记录,则执行重写操作,根据nID找到需要重写的记录
		{
			if( nID == -1 )
			{
				AfxMessageBox("ID值错误");
				return false;
			}

			CString strsql;
			strsql.Format("select * from songlist where ID = %d", nID);
			pSonglistPtr->Open( (_bstr_t)strsql, 
				m_pConnectionPtr.GetInterfacePtr(), 
				adOpenDynamic,
				adLockOptimistic,
				adCmdText );
		}
		
	//	CString strNamelen;
	//	strNamelen.Format("%d", (strSpeech == "国语") ? strSongname.GetLength()/2 : strSongname.GetLength());

		pSonglistPtr->PutCollect("songname",    (_variant_t)strSongname);
		pSonglistPtr->PutCollect("namelen",     (_variant_t)strNameLen);
		pSonglistPtr->PutCollect("speech",      (_variant_t)strSpeech);
		pSonglistPtr->PutCollect("fstalphabet", (_variant_t)strFstalphabet);
		pSonglistPtr->PutCollect("singer",      (_variant_t)strSinger);
		pSonglistPtr->PutCollect("songpath",    (_variant_t)strSongpath);
		pSonglistPtr->Update();
	}
	catch(_com_error e)
	{
		AfxMessageBox((CString)"保存数据出错: " + e.ErrorMessage());
		return false;
	}

	AfxMessageBox( bNewrecord ? "添加歌曲成功." : "修改记录成功.", MB_OK | MB_ICONINFORMATION);
	return true;
}

//------------------------------------------------------------
//删除记录，nIndex为0时删除当前记录,不为0时删除ID为nIndex的记录
//------------------------------------------------------------
BOOL CAddRecord::DeleteRecord(int nIndex)
{
	if( !PointerIsNull() )
		return false;

	try
	{
		CString strsql;
		strsql.Format("delete from songlist where ID=%d", nIndex);
	
  		_variant_t RecordsAffected;
		m_pConnectionPtr->Execute((_bstr_t)strsql, &RecordsAffected, adCmdText);
	}
	catch(_com_error e)
	{
		AfxMessageBox((CString)"删除数据出错: " + e.ErrorMessage());
		return false;
	}
	
 	return true;
}

_RecordsetPtr CAddRecord::GetRecordsetPtr(LPCTSTR lpszsql)
{
	if( !PointerIsNull() )
		return NULL;

    _variant_t RecordsAffected;
 	return m_pConnectionPtr->Execute((_bstr_t)lpszsql, &RecordsAffected, adCmdText);
}
//-------------------------------------------------------
//上面为歌曲表操作
//-------------------------------------------------------

//-------------------------------------------------------
//下面为用户表操作
//-------------------------------------------------------
BOOL CAddRecord::AdminLogin(CString &strUsername, CString &strPassword)
{
	if( !PointerIsNull() )
		return false;

	CString strsql;
	strsql.Format("select * from userinfo where username='%s' and password='%s' and levels=0", \
		strUsername, strPassword);

	try
	{
		_RecordsetPtr pUserinfoPtr;
		pUserinfoPtr.CreateInstance(__uuidof(Recordset));
		pUserinfoPtr->Open( (_bstr_t)strsql,
			m_pConnectionPtr.GetInterfacePtr(),
			adOpenDynamic,
			adLockOptimistic,
			adCmdText );
		
		if( pUserinfoPtr->BOF )
			return false;	
	}
	catch(_com_error e)
	{
		AfxMessageBox((CString)e.ErrorMessage() + "登录出错");
		return false;
	}

	return true;
}

BOOL CAddRecord::UserLogin(CString strUsername, CString strPassword)
{
	if( !PointerIsNull() )
		return false;

	CString strsql;
	strsql.Format("select * from userinfo where username='%s' and password='%s'", 
		strUsername, strPassword);

	try
	{
		_RecordsetPtr pUserinfoPtr;
		pUserinfoPtr.CreateInstance(__uuidof(Recordset));
		pUserinfoPtr->Open( (_bstr_t)strsql,
			m_pConnectionPtr.GetInterfacePtr(),
			adOpenDynamic,
			adLockOptimistic,
			adCmdText );
		
		if( pUserinfoPtr->adoEOF )
			return false;	
	}
	catch(_com_error e)
	{
		AfxMessageBox((CString)e.ErrorMessage() + "登录出错");
		return false;
	}

	return true;
}

BOOL CAddRecord::AddUser(CString strUsername, 
						 CString strPassword, 
						 CString strLevels)
{
	if( !PointerIsNull() )
		return false;

 	if(strUsername.IsEmpty() || strPassword.IsEmpty() || strLevels.IsEmpty())
	{
		AfxMessageBox("数据不能为空,请重新输入.");
		return false;
	}

	try
	{
 		if(strLevels == "管理员")
			strLevels.Format("%d", 0);
		else
			strLevels.Format("%d", 1);

		_RecordsetPtr pUserinfoPtr;
		pUserinfoPtr.CreateInstance(__uuidof(Recordset));
		pUserinfoPtr->Open("select * from userinfo",
			m_pConnectionPtr.GetInterfacePtr(),
			adOpenDynamic,
			adLockOptimistic,
			adCmdText );
 
		pUserinfoPtr->AddNew();
		pUserinfoPtr->PutCollect("username", (_variant_t)(_bstr_t)strUsername);
		pUserinfoPtr->PutCollect("password", (_variant_t)(_bstr_t)strPassword);
		pUserinfoPtr->PutCollect("levels",   (_variant_t)(_bstr_t)strLevels);
		pUserinfoPtr->Update();
	}
	catch(_com_error e)
	{
 		AfxMessageBox((CString)"增加用户出错: " + e.ErrorMessage());
		return false;
	}

	AfxMessageBox("增加成功", MB_OK | MB_ICONINFORMATION);
	return true;
}

BOOL CAddRecord::DeleteUser(CString strUsername)
{
	if( !PointerIsNull() )
		return false;

 	if( strUsername.IsEmpty() )
	{
		AfxMessageBox("数据不能为空,请重新输入.");
		return false;
	}

	try
	{
		_variant_t RecordsAffected;
		CString strsql;
		strsql.Format("delete from userinfo where username = '%s'", strUsername);
 		m_pConnectionPtr->Execute((_bstr_t)strsql, &RecordsAffected, adCmdText);
	}
	catch(_com_error e)
	{
 		AfxMessageBox((CString)"删除用户出错: " + e.ErrorMessage());
		return false;
	}

	AfxMessageBox("删除成功", MB_OK | MB_ICONINFORMATION);	
	return true;
}

CString* CAddRecord::GetUserlist(int *nCount)
{
	if( !PointerIsNull() )
		return NULL;

	try
	{
		int Count=0;
		_variant_t RecordsAffected;
		_bstr_t bstrsql = "select * from userinfo";

 		_RecordsetPtr pUserinfoPtr;
 		pUserinfoPtr = m_pConnectionPtr->Execute(bstrsql, &RecordsAffected, adCmdText);
		
		if( !pUserinfoPtr->BOF )
			pUserinfoPtr->MoveFirst();
		else
		{
			AfxMessageBox("表为空.");
			return NULL;
		}
			
 		while( !pUserinfoPtr->adoEOF )
		{
			Count++;
 			pUserinfoPtr->MoveNext();
		}
		*nCount = Count;

 		if( m_pstrUserlist != NULL)
		{
			delete[] m_pstrUserlist;
			m_pstrUserlist = NULL;
		}
		m_pstrUserlist = new CString[ Count ];

		int i=0;
		_variant_t username;
		pUserinfoPtr->MoveFirst();
		while(!pUserinfoPtr->adoEOF)
		{
 			username = pUserinfoPtr->GetCollect("username");
			m_pstrUserlist[ i++ ] = (LPCTSTR)(_bstr_t)username;
 			pUserinfoPtr->MoveNext();
		}
	}
	catch(_com_error e)
	{
 		AfxMessageBox( (CString)"取用户列表出错: " + e.ErrorMessage() );
		return NULL;
	}

	return m_pstrUserlist;
}

CString CAddRecord::GetUserLevels(CString strUsername)
{
	if( !PointerIsNull() )
		return "";

	if( strUsername.IsEmpty() )
	{
		AfxMessageBox("用户名为空.");
		return "";
	}

 	_variant_t levels;
	try
	{
		int Count=0;
		CString strsql;
		strsql.Format("select levels from userinfo where username = '%s'", strUsername);

 		_RecordsetPtr pUserinfoPtr;
		_variant_t    RecordsAffected;
 		pUserinfoPtr = m_pConnectionPtr->Execute((_bstr_t)strsql, &RecordsAffected, adCmdText);
	
		if( !pUserinfoPtr->BOF )
			pUserinfoPtr->MoveFirst();
		else
		{
			AfxMessageBox("用户名不存在.");
			return "";
		}

		pUserinfoPtr->MoveFirst();
		levels = pUserinfoPtr->GetCollect("levels");
	}
	catch(_com_error e)
	{
 		AfxMessageBox( (CString)"出错: " + e.ErrorMessage() );
		return "";
	}
	
 	return (LPCTSTR)(_bstr_t)levels;
}
//-------------------------------------------------------
//上面为用户表操作
//-------------------------------------------------------