adodatabase_sqlserver.cpp

这是一个加油站管理系统

int CADODataBase_SQLServer::GetMembers(CStringArray &strNumber, CStringArray &strName, CStringArray &strID, CStringArray &strCarNumber, CStringArray &strExp)
{
	CString strQuery;
	strQuery.Format("select * from Member");

	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]));
		strCarNumber.Add(STRING(m_var[3]));
		CString strTemp;
		strTemp.Format("%.2f",(float)m_var[4]);
		strExp.Add(strTemp);
		m_pRS->MoveNext();

		nCount ++;
	}

	return nCount;
}

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

	return TRUE;
}

BOOL CADODataBase_SQLServer::ModifyGasType(int nNumber, CString strName, float &fPrice)
{
	CString strQuery;
	strQuery.Format("update GasType set name = '%s', price = %.2f where number = %d",
		strName,
		fPrice,
		nNumber);
	try {
		Query(strQuery);
	}
	catch(...) {
		return FALSE;
	}

	return TRUE;
}

BOOL CADODataBase_SQLServer::GetGasType(int nNumber, CString &strName, float &fPrice)
{
	CString strQuery;
	strQuery.Format("select * from GasType where number = %d", nNumber);

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

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

	return TRUE;
}

BOOL CADODataBase_SQLServer::GetGasTypeByName(CString strName, int &nNumber, float &fPrice)
{
	CString strQuery;
	strQuery.Format("select * from GasType where name = '%s'", strName);

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

	if (IsEndOfFile())
		return FALSE;
	GetOneRow(3);
	nNumber = (long)(m_var[0]);
	fPrice = (float)(m_var[2]);

	return TRUE;
}

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

	Query(strQuery);

	return TRUE;
}

int CADODataBase_SQLServer::GetGasTypes(CStringArray &strNumber, CStringArray &strName, CStringArray &strPrice)
{
	CString strQuery;
	strQuery.Format("select * from GasType");

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

	int nCount = 0;
	while (!IsEndOfFile()) {
		GetOneRow(3);
		strNumber.Add(STRING(m_var[0]));
		strName.Add(STRING(m_var[1]));
		CString strTemp;
		strTemp.Format("%.2f",(float)m_var[2]);
		strPrice.Add(strTemp);
		m_pRS->MoveNext();

		nCount ++;
	}

	return nCount;
}

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

	return TRUE;
}

BOOL CADODataBase_SQLServer::ModifyMemberLevel(int nNumber, CString strName, float &fAgio, float &fExp)
{
	CString strQuery;
	strQuery.Format("update MemberLevel set name = '%s', agio = %.2f, expenditure = %0.2f where number = %d",
		strName,
		fAgio,
		fExp, 
		nNumber);
	try {
		Query(strQuery);
	}
	catch(...) {
		return FALSE;
	}

	return TRUE;
}

BOOL CADODataBase_SQLServer::GetMemberLevel(int nNumber, CString &strName, float &fAgio, float &fExp)
{
	CString strQuery;
	strQuery.Format("select * from MemberLevel where number = %d", nNumber);

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

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

	return TRUE;
}

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

	Query(strQuery);

	return TRUE;
}

int CADODataBase_SQLServer::GetMemberLevels(CStringArray &strNumber, CStringArray &strName, CStringArray &strAgio, CStringArray &strExp)
{
	CString strQuery;
	strQuery.Format("select * from MemberLevel order by expenditure desc");

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

	int nCount = 0;
	while (!IsEndOfFile()) {
		GetOneRow(4);
		strNumber.Add(STRING(m_var[0]));
		strName.Add(STRING(m_var[1]));
		CString strTemp;
		strTemp.Format("%.2f",(float)m_var[2]);
		strAgio.Add(strTemp);
		strTemp.Format("%.2f",(float)m_var[3]);
		strExp.Add(strTemp);
		m_pRS->MoveNext();

		nCount ++;
	}

	return nCount;
}

BOOL CADODataBase_SQLServer::AddNewStockRecord(CString strTime, CString strGasType, CString strDepot, float fAmount, float fPrice, int nWorker)
{
	int nDepotNumber, nGasTypeNumber;
	CString strDepotLocation;
	float fDepotCapacity,fCurContent, fGasTypePrice;
	if (!GetDepotByName(strDepot, nDepotNumber, strDepotLocation, fDepotCapacity))
		return FALSE;
	if (!GetCurrentContent(nDepotNumber, 0, fCurContent))
		return FALSE;
	if (fCurContent+fAmount > fDepotCapacity) 
		return FALSE;

	if (!GetGasTypeByName(strGasType, nGasTypeNumber, fGasTypePrice))
		return FALSE;

	CString strQuery;
	strQuery.Format("insert into StockRecord values('%s', %d, %d, %.2f, %.2f, %d)",
		strTime,
		nGasTypeNumber, 
		nDepotNumber,
		fAmount,
		fPrice,
		nWorker);
	try {
		Query(strQuery);
	}
	catch(...) {
		return FALSE;
	}

	return TRUE;
}

BOOL CADODataBase_SQLServer::AddNewSellRecord(CString strTime, CString strGasType, CString strDepot, float fAmount, float fPrice, int nWorker, int nMember, float fAgio)
{
	int nDepotNumber, nGasTypeNumber;
	CString strDepotLocation;
	float fDepotCapacity,fCurContent, fGasTypePrice;
	if (!GetDepotByName(strDepot, nDepotNumber, strDepotLocation, fDepotCapacity))
		return FALSE;
	if (!GetGasTypeByName(strGasType, nGasTypeNumber, fGasTypePrice))
		return FALSE;
	if (!GetCurrentContent(nDepotNumber, nGasTypeNumber, fCurContent))
		return FALSE;
	if (fCurContent<fAmount) 
		return FALSE;


	CString strQuery;
	strQuery.Format("insert into SellRecord values('%s', %d, %d, %.2f, %.2f, %d, %d, %.2f)",
		strTime,
		nGasTypeNumber, 
		nDepotNumber,
		fAmount,
		fPrice,
		nWorker, 
		nMember,
		fAgio );
	try {
		Query(strQuery);
	}
	catch(...) {
		return FALSE;
	}

	return TRUE;
}

int CADODataBase_SQLServer::GetStorages(CString strDepot, CString strGasType, CStringArray &strDepotName, CStringArray &strGasTypeName, CStringArray &strAmount)
{
	int nDepotNumber, nGasTypeNumber;
	CString strTemp;
	float fTemp;

	nDepotNumber = nGasTypeNumber = 0;
	if (strDepot != "全部") {
		if (!GetDepotByName(strDepot, nDepotNumber, strTemp, fTemp))
			return -1;
	}
	if (strGasType != "全部") {
		if (!GetGasTypeByName(strGasType, nGasTypeNumber, fTemp))
			return -1;
	}

	CString strQuery, strQueryCount;
	if (nDepotNumber != 0  && nGasTypeNumber == 0) {
		strQueryCount.Format("select count(*) from Storage where depot = %d", nDepotNumber);
		strQuery.Format("select * from Storage where depot = %d", nDepotNumber);
	}
	else if(nDepotNumber == 0  && nGasTypeNumber != 0) {
		strQueryCount.Format("select count(*) from Storage where gas_type = %d", nGasTypeNumber);
		strQuery.Format("select * from Storage where gas_type = %d", nGasTypeNumber);
	}
	else if(nDepotNumber != 0  && nGasTypeNumber != 0) {
		strQueryCount.Format("select count(*) from Storage where gas_type = %d and depot = %d", nGasTypeNumber, nDepotNumber);
		strQuery.Format("select * from Storage where gas_type = %d and depot = %d", nGasTypeNumber, nDepotNumber);
	}
	else {
		strQueryCount.Format("select count(*) from Storage");
		strQuery.Format("select * from Storage");
	}

	if (!Query(strQueryCount)) {
		return -1;
	}
	GetOneRow(1);
	long nCount = (long)m_var[0];
	if (nCount <= 0) {
		return -1;
	}

	if (!Query(strQuery)) {
		return -1;
	}

	CString strName;
	int *depot_number = new int[nCount];
	int *gas_type_number = new int[nCount];
	float *amount = new float[nCount];
	BOOL bError = FALSE;
	int i=0;
	while (!IsEndOfFile()) {
		GetOneRow(3);
		gas_type_number[i] = (long)m_var[0];
		depot_number[i] = (long)m_var[1];
		amount[i] = (float)m_var[2];

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

	for (i=0;i<nCount;i ++) {
		if (!GetDepot(depot_number[i], strName, strTemp, fTemp)) {
			bError = TRUE;
			break;
		}
		strDepotName.Add(strName);
		if (!GetGasType(gas_type_number[i], strName, fTemp)) {
			bError = TRUE;
			break;
		}
		strGasTypeName.Add(strName);
		strTemp.Format("%.2f", amount[i]);
		strAmount.Add(strTemp);
	}

	delete[] depot_number;
	delete[] gas_type_number;
	delete[] amount;

	return bError?-1:nCount;
}

int CADODataBase_SQLServer::GetStockRecords(CStringArray &strTime, CStringArray &strGasType, CStringArray &strDepot, CStringArray &strAmount, CStringArray &strPrice, CStringArray &strWorker)
{
	CString strQuery;
	strQuery.Format("select * from StockRecord");

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

	int nCount = 0;
	CString strTemp;
	while (!IsEndOfFile()) {
		GetOneRow(6);
		strTime.Add(STRING(m_var[0]));
		strGasType.Add(STRING(m_var[1]));
		strDepot.Add(STRING(m_var[2]));
		strTemp.Format("%.2f",(float)m_var[3]);
		strAmount.Add(strTemp);
		strTemp.Format("%.2f",(float)m_var[4]);
		strPrice.Add(strTemp);
		strWorker.Add(STRING(m_var[5]));

		m_pRS->MoveNext();
		nCount ++;
	}

	return nCount;
}

int CADODataBase_SQLServer::GetStockRecordsEx(CStringArray &strTime, CStringArray &strGasType, CStringArray &strDepot, CStringArray &strAmount, CStringArray &strPrice, CStringArray &strWorker, CString strTimeStart, CString strTimeEnd)
{
	CString strQuery;
	strQuery.Format(
		"select * from StockRecord where time between '%s' and '%s'",
		strTimeStart,
		strTimeEnd);

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

	int nCount = 0;
	CString strTemp;
	while (!IsEndOfFile()) {
		GetOneRow(6);
		strTime.Add(STRING(m_var[0]));
		strGasType.Add(STRING(m_var[1]));
		strDepot.Add(STRING(m_var[2]));
		strTemp.Format("%.2f",(float)m_var[3]);
		strAmount.Add(strTemp);
		strTemp.Format("%.2f",(float)m_var[4]);
		strPrice.Add(strTemp);
		strWorker.Add(STRING(m_var[5]));

		m_pRS->MoveNext();
		nCount ++;
	}

	return nCount;
}

int CADODataBase_SQLServer::GetSellRecordsEx(CStringArray &strTime, CStringArray &strGasType, CStringArray &strDepot, CStringArray &strAmount, CStringArray &strPrice, CStringArray &strWorker, CStringArray &strClient, CStringArray &strAgio, CString strTimeStart, CString strTimeEnd)
{
	CString strQuery;
	strQuery.Format(
		"select * from SellRecord where time between '%s' and '%s'",
		strTimeStart,
		strTimeEnd);

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

	int nCount = 0;
	CString strTemp;
	while (!IsEndOfFile()) {
		GetOneRow(8);
		strTime.Add(STRING(m_var[0]));
		strGasType.Add(STRING(m_var[1]));
		strDepot.Add(STRING(m_var[2]));
		strTemp.Format("%.2f",(float)m_var[3]);
		strAmount.Add(strTemp);
		strTemp.Format("%.2f",(float)m_var[4]);
		strPrice.Add(strTemp);
		strWorker.Add(STRING(m_var[5]));
		strClient.Add(STRING(m_var[6]));
		strTemp.Format("%.2f",(float)m_var[7]);
		strAgio.Add(strTemp);

		m_pRS->MoveNext();
		nCount ++;
	}

	return nCount;
}