adodatabase_sqlserver.cpp

这是一个加油站管理系统

 #include "StdAfx.h"
#include "ADODataBase_SQLServer.h"

HRESULT CADODataBase_SQLServer::Init()
{
	if(FAILED(CADODataBase::Init()))
		return E_UNEXPECTED;
	SetProvider("sqloledb");
	return S_OK;
}

_RecordsetPtr CADODataBase_SQLServer::ExecuteQuery(CString strQuery)
{
	memset(m_lpQuery, 0, 1024);
	strcpy(m_lpQuery, strQuery.GetBuffer(1024));
	strQuery.ReleaseBuffer();
	return CADODataBase::ExecuteQuery(m_lpQuery);
}

BOOL CADODataBase_SQLServer::Query(CString strQuery)
{
	try {
		ExecuteQuery(strQuery);
		if (m_pRS == NULL)
			return FALSE;
	}
	catch (...) {
		return FALSE;
	}

	return TRUE;
}

HRESULT CADODataBase_SQLServer::GetOneRow(long lColumns)
{
	return CADODataBase::GetOneRow(lColumns, m_var);
}

BOOL CADODataBase_SQLServer::IsEndOfFile()
{
	if (m_pRS == NULL) {
		return TRUE;
	}
	if (m_pRS->EndOfFile)
		return TRUE;

	return FALSE;
}

BOOL CADODataBase_SQLServer::IsValidWorkerPassword(int nNumber, CString strPassword)
{
	CString strQuery;
	strQuery.Format("select password from Worker where number = %d", nNumber);
	if (!Query(strQuery)) {
		return FALSE;
	}

	if (IsEndOfFile())
		return FALSE;
	
	GetOneRow(1);

	return strPassword == CString((LPCTSTR)(_bstr_t)m_var[0]);
}

int CADODataBase_SQLServer::GetWorkerType(int nNumber)
{
	CString strQuery;
	strQuery.Format("select type from Worker where number = %d", nNumber);
	if (!Query(strQuery)) {
		return -1;
	}

	if (IsEndOfFile())
		return -1;
	
	GetOneRow(1);

	return (short)m_var[0];
}

BOOL CADODataBase_SQLServer::AutoAssignNumber(CString strTableName, int &nNumber)
{
	nNumber = -1;
	long nRowCount;
	CString strQuery;

	strQuery.Format("select count(number) from %s", strTableName);
	if (!Query(strQuery)) {
		return FALSE;
	}
	
	if (IsEndOfFile())
		return FALSE;
	
	GetOneRow(1);
	nRowCount = (long)(m_var[0]);
	if (nRowCount == 0) {
		nNumber = 1;
		return TRUE;
	}

	strQuery.Format("select number from %s order by number asc", strTableName);
	if (!Query(strQuery)) {
		return FALSE;
	}

	long *aryNumber = new long[nRowCount];
	long nRow = 0;
	while(!IsEndOfFile()) {
		GetOneRow(1);
		aryNumber[nRow++] = (long)m_var[0];
		m_pRS->MoveNext();
		if (nRow > 1) {
			if (aryNumber[nRow-1]-aryNumber[nRow-2] > 1) {
				nNumber = aryNumber[nRow-2]+1;
				break;
			}
		}
	}
	delete[] aryNumber;

	if (nNumber == -1) {
		nNumber = nRowCount+1;
	}
	return TRUE;
}

BOOL CADODataBase_SQLServer::AddNewWorker(int nNumber, CString strName, CString strID, int nType, CString strPassword)
{
	CString strQuery;
	strQuery.Format("insert into Worker values(%d,'%s','%s',%d,'%s')",
		nNumber,
		strName,
		strID,
		nType,
		strPassword);
	try {
		Query(strQuery);
	}
	catch(...) {
		return FALSE;
	}

	return TRUE;
}

BOOL CADODataBase_SQLServer::ModifyWorker(int nNumber, CString strName, CString strID, int nType, CString strPassword)
{
	CString strQuery;
	strQuery.Format("update Worker set name = '%s', id = '%s', type = %d, password = '%s' where number = %d",
		strName,
		strID,
		nType,
		strPassword,
		nNumber);
	try {
		Query(strQuery);
	}
	catch(...) {
		return FALSE;
	}

	return TRUE;
}

BOOL CADODataBase_SQLServer::RemoveWorker(int nNumber)
{
	CString strQuery;
	strQuery.Format("delete from Worker where number = %d", nNumber);

	Query(strQuery);

	return TRUE;
}

BOOL CADODataBase_SQLServer::GetWorker(int nNumber, CString &strName, CString &strID, int &nType, CString &strPassword)
{
	CString strQuery;
	strQuery.Format("select * from Worker where number = %d", nNumber);

	if (!Query(strQuery)) {
		return FALSE;
	}

	if (IsEndOfFile())
		return FALSE;
	GetOneRow(5);
	strName = STRING(m_var[1]);
	strID = STRING(m_var[2]);
	nType = (short)(m_var[3]);
	strPassword = STRING(m_var[4]);

	return TRUE;
}

int CADODataBase_SQLServer::GetWorkers(CStringArray &strNumber, CStringArray &strName, CStringArray &strID, CStringArray &strType, CStringArray &strPassword)
{
	CString strQuery;
	strQuery.Format("select * from Worker");

	if (!Query(strQuery)) {
		return 0;
	}

	int nCount = 0;
	while (!IsEndOfFile()) {
		GetOneRow(5);
		strNumber.Add(STRING(m_var[0]));
		strName.Add(STRING(m_var[1]));
		strID.Add(STRING(m_var[2]));
		strType.Add(STRING(m_var[3]));
		strPassword.Add(STRING(m_var[4]));
		m_pRS->MoveNext();

		nCount ++;
	}
	
	return nCount;
}

BOOL CADODataBase_SQLServer::AddNewDepot(int nNumber, CString strName, CString strLocation, float &fCapacity)
{
	CString strQuery;
	strQuery.Format("insert into Depot values(%d,'%s','%s',%.2f)",
		nNumber,
		strName,
		strLocation,
		fCapacity);
	try {
		Query(strQuery);
	}
	catch(...) {
		return FALSE;
	}

	return TRUE;
}

BOOL CADODataBase_SQLServer::ModifyDepot(int nNumber, CString strName, CString strLocation, float &fCapacity)
{
	CString strQuery;
	strQuery.Format("update Depot set name = '%s', location = '%s', capacity = %.2f where number = %d",
		strName,
		strLocation,
		fCapacity,
		nNumber);
	try {
		Query(strQuery);
	}
	catch(...) {
		return FALSE;
	}

	return TRUE;
}

BOOL CADODataBase_SQLServer::GetCurrentContent(int nDepotNumber, int nGasTypeNumber, float &fCurContent)
{
	CString strQuery;
	if (nGasTypeNumber <= 0)
		strQuery.Format("select count(amount) from Storage where depot = %d", nDepotNumber);
	else
		strQuery.Format("select count(amount) from Storage where depot = %d and gas_type = %d", nDepotNumber, nGasTypeNumber);

	if (!Query(strQuery)) {
		return FALSE;
	}

	if (IsEndOfFile())
		return FALSE;
	GetOneRow(1);

	if ((long)(m_var[0]) == 0) {
		fCurContent = 0;
		return TRUE;
	}

	if (nGasTypeNumber <= 0)
		strQuery.Format("select sum(amount) from Storage where depot = %d", nDepotNumber);
	else
		strQuery.Format("select sum(amount) from Storage where depot = %d and gas_type = %d", nDepotNumber, nGasTypeNumber);

	if (!Query(strQuery)) {
		return FALSE;
	}

	if (IsEndOfFile())
		return FALSE;
	GetOneRow(1);
	
	fCurContent = (float)m_var[0];
	return TRUE;
}

BOOL CADODataBase_SQLServer::GetDepot(int nNumber, CString &strName, CString &strLocation, float &fCapacity)
{
	CString strQuery;
	strQuery.Format("select * from Depot where number = %d", nNumber);

	if (!Query(strQuery)) {
		return FALSE;
	}

	if (IsEndOfFile())
		return FALSE;
	GetOneRow(4);
	strName = STRING(m_var[1]);
	strLocation = STRING(m_var[2]);
	fCapacity = (float)(m_var[3]);

	return TRUE;
}

BOOL CADODataBase_SQLServer::GetDepotByName(CString strName, int &nNumber, CString &strLocation, float &fCapacity)
{
	CString strQuery;
	strQuery.Format("select * from Depot where name = '%s'", strName);

	if (!Query(strQuery)) {
		return FALSE;
	}

	if (IsEndOfFile())
		return FALSE;
	GetOneRow(4);
	nNumber = (long)(m_var[0]);
	strLocation = STRING(m_var[2]);
	fCapacity = (float)(m_var[3]);

	return TRUE;
}

BOOL CADODataBase_SQLServer::RemoveDepot(int nNumber)
{
	CString strQuery;
	strQuery.Format("delete from Depot where number = %d", nNumber);

	Query(strQuery);

	return TRUE;
}

int CADODataBase_SQLServer::GetDepots(CStringArray &strNumber, CStringArray &strName, CStringArray &strLocation, CStringArray &strCapacity)
{
	CString strQuery;
	strQuery.Format("select * from Depot");

	if (!Query(strQuery)) {
		return 0;
	}

	int nCount = 0;
	while (!IsEndOfFile()) {
		GetOneRow(4);
		strNumber.Add(STRING(m_var[0]));
		strName.Add(STRING(m_var[1]));
		strLocation.Add(STRING(m_var[2]));
		strCapacity.Add(STRING(m_var[3]));
		m_pRS->MoveNext();

		nCount ++;
	}

	return nCount;
}

BOOL CADODataBase_SQLServer::AddNewMember(int nNumber, CString strName, CString strID, CString strCarNumber, float &fExp)
{
	CString strQuery;
	strQuery.Format("insert into Member values(%d,'%s','%s','%s', %.2f)",
		nNumber,
		strName,
		strID,
		strCarNumber,
		fExp);
	try {
		Query(strQuery);
	}
	catch(...) {
		return FALSE;
	}

	return TRUE;
}

BOOL CADODataBase_SQLServer::ModifyMember(int nNumber, CString strName, CString strID, CString strCarNumber, float &fExp)
{
	CString strQuery;
	strQuery.Format("update Member set name = '%s', id = '%s', car_number = '%s', expenditure = %.2f where number = %d",
		strName,
		strID,
		strCarNumber,
		fExp,
		nNumber);
	try {
		Query(strQuery);
	}
	catch(...) {
		return FALSE;
	}

	return TRUE;
}

BOOL CADODataBase_SQLServer::GetMember(int nNumber, CString &strName, CString &strID, CString &strCarNumber, float &fExp)
{
	CString strQuery;
	strQuery.Format("select * from Member where number = %d", nNumber);

	if (!Query(strQuery)) {
		return FALSE;
	}

	if (IsEndOfFile())
		return FALSE;
	GetOneRow(5);
	strName = STRING(m_var[1]);
	strID = STRING(m_var[2]);
	strCarNumber = STRING(m_var[3]);
	fExp = (float)(m_var[4]);

	return TRUE;
}

BOOL CADODataBase_SQLServer::GetMemberAgio(int nNumber, float &fAgio)
{
	CString strQuery;
	float fExp;
	strQuery.Format("select expenditure from Member where number = %d", nNumber);

	if (!Query(strQuery)) {
		return FALSE;
	}

	if (IsEndOfFile())
		return FALSE;
	GetOneRow(1);
	fExp = (float)(m_var[0]);

	CStringArray strTemp, strAgio, strExp;
	int nCount = GetMemberLevels(strTemp, strTemp, strAgio, strExp);
	if (nCount <= 1) {
		fAgio = 1;
		return TRUE;
	}
	float *agio = new float[nCount];
	float *exp = new float[nCount];
	fAgio = 1.0;
	for (int i=0;i<strAgio.GetSize();i ++) {
		GET_FLOAT(agio[i], strAgio.GetAt(i));
		GET_FLOAT(exp[i], strExp.GetAt(i));
		if (fExp >= exp[i]) {
			fAgio = agio[i];
			break;
		}
	}
	delete[] agio;
	delete[] exp;

	return TRUE;
}

BOOL CADODataBase_SQLServer::RemoveMember(int nNumber)
{
	CString strQuery;
	strQuery.Format("delete from Member where number = %d", nNumber);

	Query(strQuery);

	return TRUE;
}