adodb.cpp

电信机房MDF、电源柜监控源码,主要用在通信机房配线设备监控、电源柜监控

// AdoDB.cpp: implementation of the CAdoDB class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "MDF.h"
#include "AdoDB.h"
#include "MDFDlg.h"	//-测试用-
#include ".\adodb.h"
#include "system.h"

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

extern CMDFApp theApp ; //-测试用-
//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

//##ModelId=44B6F888004E
CAdoDB::CAdoDB()
{
	m_bIsConnect = FALSE ;
	//hEvent = CreateEvent ( NULL , FALSE , TRUE , NULL ) ; //建立数据库读取信号标志
}

//##ModelId=44B6F888004F
CAdoDB::~CAdoDB()
{
	//m_ConPtr->Close() ;
}

//##ModelId=44B6F8880042
BOOL CAdoDB::InitDB()
{

	CString strTip,strDB,strSQL ;
	CString strConnect ;

	try                 
	{
		m_ConPtr.CreateInstance(__uuidof(Connection));
		strConnect.Format( "Provider=SQLOLEDB.1; Server=%s;uid=%s;pwd=%s",theApp.m_strIp,theApp.m_strUserId,theApp.m_strPassWord ) ;
		m_ConPtr->ConnectionTimeout = 30;
		m_ConPtr->Open(_bstr_t(strConnect),"","",adModeUnknown);
		strSQL = "USE MDFDB" ;
		BSTR bstrSQL = strSQL.AllocSysString();			//转为UNICODE码
		m_ConPtr->Execute(bstrSQL,NULL,adCmdText) ;
		//strTip.Format("系统：MDF监测中心 %s 连接到数据库 %s 成功",theApp.m_pComm->m_sHostIp.strIp,m_strIp) ;
		//WriteLog(strTip) ;
		return TRUE ;
	}
	catch(_com_error e)
	{
		int num = e.WCode() ; 
		strTip.Format( "异常：数据库连接异常：%s 出错码：%d 请正确配置",(LPCSTR)e.Description(),num ) ;
		theApp.Show( strTip ) ; //提示数据库连接不成功
		return FALSE ;
	}
}

//##ModelId=44B6F8880041
int CAdoDB::CloseDB()
{
	//CString strTip ;
	if (m_ConPtr->GetState())
		m_ConPtr->Close();
	return(0);
}

//##ModelId=44B6F8880034
int CAdoDB::ReadDB(CString strSQL, _RecordsetPtr& pRsPtr,CString strTip)
{
	CString strError ;

	try
	{
		BSTR bstrSQL = strSQL.AllocSysString();
		pRsPtr->Open(	bstrSQL ,
						m_ConPtr.GetInterfacePtr(),	// 获取库接库的IDispatch指针
						adOpenStatic,
						adLockBatchOptimistic,			//adLockOptimistic
						adCmdText);
		return 0;
	}
	catch(_com_error e)
	{
		strError.Format("异常：执行 %s 发生异常：%s",strSQL,(LPCSTR)e.Description()) ;
		strTip += strError ; 
		WriteLog(strTip) ;

		return -1 ;
	}
}

//##ModelId=44B6F8880032
BOOL CAdoDB::ExecuteSQL(CString strSQL)
{
	CString strTip ;

	//theApp.m_strDBArray.AddTail(strSQL);
	
	try
	{
		if(m_ConPtr == NULL || !m_ConPtr->State)
			return FALSE ;
		BSTR bstrSQL = strSQL.AllocSysString();	//转为UNICODE码
		m_ConPtr->Execute(bstrSQL,NULL,adCmdText) ;
		return TRUE ;
	}
	catch(_com_error e)
	{
		int num = e.WCode() ;
		strTip.Format( "异常：执行SQL语句：%s 产生异常：%d,%s ",strSQL,num,(LPCSTR)e.Description() ) ;
		TRACE0(strTip);
		return FALSE ;
	}
	
	return TRUE ;

}

//##ModelId=44B6F8880025
int CAdoDB::GetRdData(CString Name , _RecordsetPtr& pRsPtr , _variant_t& var, CString strTip)
{
	CString strError ;

	try
	{
		BSTR bstrSQL = Name.AllocSysString();
		var = pRsPtr->GetCollect(bstrSQL) ;
		return 0 ;
	}
	catch(_com_error e)
	{
		strError.Format("异常：读字段 %s 发生异常：%s",Name,(LPCSTR)e.Description()) ;
		strTip += strError ; 
		WriteLog(strTip) ;
		return -1 ;
	}

}

//写通讯记录
//##ModelId=44B6F8880015
int CAdoDB::WriteCommRd(CString strTime, int nResult , int nType, CString strAddr, CString strNote, CString strData, CString strSource, CString strMemo)
{
	CString strSQL,strTip ;

	strSQL.Format("insert into CMTHistoryComm (CRDTime,CRDResult,CRDType,CRDAddr,CRDNote,CRDData,CRDSource,CRDMemo) values ('%s',%d,%d,'%s','%s','%s','%s','%s')",strTime,nResult,nType,strAddr,strNote,strData,strSource,strMemo) ;
	ExecuteSQL(strSQL) ;	//记录如数据库

	return 0 ;

}
//写告警记录
//##ModelId=44B6F88703DE
int CAdoDB::WriteAlarm(int nSVMID, int nPortType, CString strPortName, CString strEQAddr, CString strEQName, int nSVDID, CString strSpotSort, int nALevel, CString strAStartTime, CString strAEndTime, CString strANote, int nAState, float fAValue, CString strAMemo)
{
	CString strSQL,strTip ;

	try
	{

		if( strAEndTime == "" )
		{
			//插入告警信息
			strSQL.Format("insert into CMTAlarmInfo (SVMID,SVMPSort,SVMAddrCode,SVDID,SVSpotSort,AlarmLevel,AStartTime,AlarmNote,AlarmState,AlarmValue,AlarmMemo) values (%d,'%s','%s',%d,'%s',%d,'%s','%s',%d,%f,'%s' ) " ,nSVMID,strPortName,strEQAddr,nSVDID,strSpotSort,nALevel,strAStartTime,strEQName,nAState,fAValue,strAMemo) ;
		}
		else
		{
			//修改告警信息为消警
			strSQL.Format("update CMTAlarmInfo set AEndTime='%s',AlarmMemo='%s',AlarmState=%d WHERE SVMID=%d AND SVMPSort=%s AND SVMAddrCode='%s' AND SVDID=%d AND SVSpotSort='%s' AND AlarmState<>3 " , strAEndTime, strAMemo,3, nSVMID,strPortName,strEQAddr,nSVDID,strSpotSort ) ;
		}
		ExecuteSQL(strSQL) ;
		return 0 ;
	}
	catch(_com_error e)
	{
		strTip.Format( "异常：%s 产生异常：%s ",strSQL,(LPCSTR)e.Description() ) ;
		theApp.DebugShow(strTip) ;
		theApp.OutPut(strTip) ;
		return -1 ;
	}
}

//##ModelId=44B6F88703DC
void CAdoDB::ErrorTrans(WORD dwError)
{
	if(dwError == 0 )
	{
		//m_bIsConnect = FALSE ;
		/*
		while(!m_bIsConnect)
		{
			Connect() ;
			Sleep(500) ;
			if(m_bIsConnect)
				theApp.Show("系统：数据库重新连接成功") ;
		}
		*/

	}
}

//##ModelId=44B6F88703CC
int CAdoDB::WriteCheckData(int nSVMID, CString strSVMPSort, int nSVDID , CString strSpotSort , float fValue)
{
	CString strTime,strTip,strSort,strSQL ;
	try
	{
		strTime = CTime::GetCurrentTime().Format("%Y-%m-%d %H:%M:%S") ;
		strSQL.Format("insert into CMTCheckData (SVMID,SVMPSort,CHKValue,CHKTime,SVDID,SVSpotSort) values (%d,'%s',%f,'%s',%d,'%s') ",nSVMID,strSVMPSort,fValue,strTime,nSVDID,strSpotSort) ;
		ExecuteSQL(strSQL) ;
		return 0 ;
	}
	catch(_com_error e)
	{
		strTip.Format( "异常：%s 产生异常：%s ",strSQL,(LPCSTR)e.Description() ) ;
		theApp.DebugShow(strTip) ;
		theApp.OutPut(strTip) ;
		return -1 ;
	}
}

//##ModelId=44B6F88703CA
int CAdoDB::WriteLog(CString strNote)
{
	CString strSQL,strTip ;

	try
	{
		strSQL.Format("insert into CMTMSJournal (MSJDesc) values ('%s') ",strNote) ;
		ExecuteSQL(strSQL);	//记录入数据库
		return 0;
	}
	catch(_com_error e)
	{
		strTip.Format( "异常：写监测中心日志 %s 产生异常：%s ",strSQL,(LPCSTR)e.Description() ) ;
		theApp.DebugShow(strTip) ;
		theApp.Show(strTip) ;
		return -1 ;
	}

}


//##ModelId=44B6F88703BD
int CAdoDB::GetIntVar(_variant_t var,int& nValue,CString strTip)
{
	try
	{
		nValue = atoi((LPCSTR)_bstr_t(var)) ;
		return 0 ;
	}
	catch(_com_error e)
	{
		nValue = 0;
		return -1 ;
	}
}

//##ModelId=44B6F88703B9
int CAdoDB::GetStrVar(_variant_t var, CString &strValue, CString strTip)
{
	try
	{
		strValue = (LPCSTR)_bstr_t(var) ;
		return 0 ;
	}
	catch(_com_error e)
	{
		strValue = "";
		return -1 ;
	}
}

//##ModelId=44B6F887039B
int CAdoDB::GetDoubleVar(_variant_t var, double &dValue, CString strTip)
{
	CString strError ;
	try
	{
		dValue = atof((LPCSTR)_bstr_t(var)) ;
		return 0 ;
	}
	catch(_com_error e)
	{
		dValue = 0;
		return -1 ;
	}
}


//##ModelId=44B6F888005E
BOOL CAdoDB::Connect(CBaseInfo baseinfo)
{
	CString strConnect,strSQL,strTip;
	try                 
	{
		if(m_ConPtr != NULL )
		{
			m_ConPtr->Close();
			m_ConPtr.Release();
		}
		m_ConPtr.CreateInstance(__uuidof(Connection));
		//Provider=SQLOLEDB.1;Password=%s;Persist Security Info=True;User ID=%s;Initial Catalog=%s;Data Source=%s
		strConnect.Format( "Provider=SQLOLEDB.1;Password=%s;Persist Security Info=True;User ID=%s;Initial Catalog=%s;Data Source=%s",\
			baseinfo.UserPassword,baseinfo.UserID,baseinfo.DBIP,baseinfo.DBNam) ;
		m_ConPtr->ConnectionTimeout = 10;
		m_ConPtr->Open(_bstr_t(strConnect),"","",adModeUnknown);
		if  (m_ConPtr->GetState() == 0)
			return FALSE;
		else
			return TRUE;
	}
	catch(_com_error e)
	{
		int num = e.WCode() ; 
		strTip.Format( "异常：数据库连接异常：%s 出错码：%d 请正确配置",(LPCSTR)e.Description(),num ) ;
		char *cBuf = new char[strTip.GetLength()+1];
		cBuf = strTip.GetBuffer();
		cBuf[strTip.GetLength()] = '\0';
		TRACE0(strTip);
		//SendMessage(theApp.m_pMainWnd->m_hWnd,WM_LOGMESSAGE,(WPARAM)cBuf,0);
		
		return FALSE ;
	}
}

//##ModelId=44B6F887039A
BOOL CAdoDB::GetConnectState()
{
	return m_ConPtr->State ;
}

//##ModelId=44B6F887038B
BOOL CAdoDB::ExecuteDB(CString strNote)
{
	CString strTip ;

	try
	{
		
		if(m_ConPtr == NULL || !m_ConPtr->State)
			return FALSE ;
		BSTR bstrSQL = strNote.AllocSysString();	//转为UNICODE码

		m_ConPtr->Execute(bstrSQL,NULL,adCmdText) ;

		return TRUE ;
	}
	catch(_com_error e)
	{
		
		int num = e.WCode() ;
		strTip.Format( "异常：执行SQL语句：%s 产生异常：%d,%s ",strNote,num,(LPCSTR)e.Description() ) ;
		theApp.DebugShow(strTip) ;
		theApp.Show(strTip) ; 

		return FALSE ;
	}
}

// 初始化数据库连接
//##ModelId=44B6F888006D
BOOL CAdoDB::InitDB(CBaseInfo baseinfo)
{
	
	CString strTip;
	CString strConnect ;
	
	try                 
	{
		m_ConPtr.CreateInstance(__uuidof(Connection));
		//strConnect = "Provider=SQLOLEDB.1;Password="";Persist Security Info=True;User ID=sa;Initial Catalog=MDFDB;Data Source=cm2";

		strConnect.Format( "Provider=SQLOLEDB.1;Password=%s;Persist Security Info=True;User ID=%s;Initial Catalog=%s;Data Source=%s",\
			baseinfo.UserPassword,baseinfo.UserID,baseinfo.DBNam,baseinfo.DBIP) ;
		m_ConPtr->ConnectionTimeout = 30;
		m_ConPtr->Open(_bstr_t(strConnect),"","",-1);
		if  (m_ConPtr->GetState() == 0)
			return FALSE;
		else
			return TRUE ;

	}
	catch(_com_error e)
	{
		int num = e.WCode() ; 
		strTip.Format( "异常：数据库连接异常：%s 出错码：%d 请正确配置",(LPCSTR)e.Description(),num ) ;
		//theApp.Show( strTip ) ; //提示数据库连接不成功
		TRACE(strTip);
		//SendMessage(theApp.m_pMainWnd->m_hWnd,WM_LOGMESSAGE,(WPARAM)&strTip,0);
		//throw(1);
		return FALSE ;
	}	

}

// 存储原始数据包内容
//##ModelId=44B6F888006F
int CAdoDB::WriteCommRd(CInfoFrame frame)
{
	CString strSQL,strTip,strTime ;
	if (frame.nRLen > 0 )
	{
		
		strTip.SetString(frame.ucBuf,frame.nRLen);
		strTime = frame.time.Format("%Y-%m-%d %H:%M:%S");
		
		strSQL.Format("insert into CMTOriginalFrame (CRDTime, CRDData, CRDSource, CRDCommMode,CRDPort ) \
					  values ('%s','%s','%s',%d,%d)",strTime,strTip,frame.address,frame.addrType,frame.iPort) ;
	}

	//strSQL.Format("insert into CMTHistoryComm (CRDTime,CRDResult,CRDType,CRDAddr,CRDNote,CRDData,CRDSource,CRDMemo) values ('%s',%d,%d,'%s','%s','%s','%s','%s')",strTime,nResult,nType,strAddr,strNote,strData,strSource,strMemo) ;
	ExecuteSQL(strSQL) ;	//记录如数据库
	return 0;
}

// 取得字段的值
BOOL CAdoDB::GetFieldValue(const CString strField, _RecordsetPtr& pRsPtr,int & iValue)
{
	_variant_t var;
	try
	{
		BSTR bstrSQL = strField.AllocSysString();
		var = pRsPtr->GetCollect(bstrSQL) ;
		if (var.vt == VT_NULL)
			iValue = 0;
		else
			iValue = atoi((LPCSTR)_bstr_t(var)) ;
		return TRUE ;
	}
	catch(_com_error e)
	{
		iValue = 0;
		return FALSE ;
	}
	return 0;
}

BOOL CAdoDB::GetFieldValue(const CString strField, _RecordsetPtr& pRsPtr,double & iValue)
{
	_variant_t var;
	try
	{
		BSTR bstrSQL = strField.AllocSysString();
		var = pRsPtr->GetCollect(bstrSQL) ;
		if (var.vt == VT_NULL)
			iValue = 0 ;
		else
			iValue = atof((LPCSTR)_bstr_t(var)) ;
		return TRUE ;
	}
	catch(_com_error e)
	{
		iValue = 0;
		return FALSE ;
	}
	return 0;
}


BOOL CAdoDB::GetFieldValue(const CString strField, _RecordsetPtr& pRsPtr,CString &iValue)
{
	_variant_t var;
	try
	{
		BSTR bstrSQL = strField.AllocSysString();
		var = pRsPtr->GetCollect(bstrSQL) ;
		if(var.vt == VT_NULL)
			iValue.Empty();
		else
			iValue = (LPCSTR)_bstr_t(var) ;
		return TRUE ;
	}
	catch(_com_error e)
	{
		iValue.Empty();
		return FALSE ;
	}
	return 0;
}