lyfz_repos/经典版源码/lyfzAccessControl/trunk/lyfzAttendance/DBInterface.cpp

#include "StdAfx.h"
#include "DBInterface.h"
#include <string>
#include "Global.h"

#define MSG_SHOWPROMPTING WM_USER + 101

extern HWND g_hwnd;

CDBInterface::CDBInterface(void)
{
	m_bOpen = FALSE;
	m_bSolve = FALSE;
	m_pdbInstance = NULL;
	m_hReConnectEvent = NULL;
	m_hReConnectThread = NULL;
}

CDBInterface::~CDBInterface(void)
{
	CloseDatabase();
}

//************************************//
// 数据库地址;
// 数据库端口;
// 数据库管理员账号;
// 数据库管理员密码(使用加密);
// 数据库名称;
//
// [函数]：GetIniInfo
// [描述]：获取指定ini信息;
// [参数]：
//		szPath：ini所在目录;
//		szIniName：ini名;
//		szConnectSQL:返回的sql连接字符串;
// [返回]：void
//
//************************************//
void CDBInterface::GetIniInfo(const char *szPath, const char *szIniName)
{
	WCHAR szFile[MAX_PATH + 1];
	::wmemset(szFile, 0, MAX_PATH + 1);

	if (szPath != NULL && szIniName != NULL)
		wsprintf(szFile, _T("%s\\%s"), szPath, szIniName);
	else
		wsprintf(szFile, _T("%s\\lyfzServiceInfo.ini"), g_ModulePath);

	TRACE("szFile =%s\n", szFile);

	GetPrivateProfileString(_T("ServerInfo"), _T("dbServer"), _T(""), g_szDBServer, MAX_PATH, szFile);
	g_dwDBServerPort = GetPrivateProfileInt(_T("ServerInfo"), _T("dbServerPort"), 0, szFile);
	GetPrivateProfileString(_T("ServerInfo"), _T("dbAccount"), _T(""), g_szDBAccount, MAX_PATH, szFile);
	GetPrivateProfileString(_T("ServerInfo"), _T("dbPassWord"), _T(""), g_szDBPassWord, MAX_PATH, szFile);
	GetPrivateProfileString(_T("ServerInfo"), _T("dbName"), _T(""), g_szDBName, MAX_PATH, szFile);

	if (g_dwDBServerPort != 0)
		wsprintf(m_szConnectString, _T("driver={SQL Server};Server=%s,%d;database=%s;uid=%s;pwd=%s"),
		g_szDBServer, g_dwDBServerPort, g_szDBName, g_szDBAccount, g_szDBPassWord);
	else
		wsprintf(m_szConnectString, _T("driver={SQL Server};Server=%s;database=%s;uid=%s;pwd=%s"),
		g_szDBServer, g_szDBName, g_szDBAccount, g_szDBPassWord);
}

BOOL CDBInterface::SolveDBError(CONST DWORD &dwError, LPCTSTR lpErrorString)
{
	AutoThreadSection aSection(&s_critSection);
	if (m_bSolve) return TRUE;
	switch (dwError)
	{
	case ERROR_PIPE_NOT_CONNECTED:
	case WSAECONNRESET:
	case WSAECONNABORTED:
	{
		m_bSolve = TRUE;
		m_strConnectErrorDescriptor = lpErrorString;
		// 启用重连线程;
		CloseDatabase();
		StartThread();
	}
	break;
	default:
		// 其他错误,输出日志;
		//WriteClientLog(lpErrorString);
		break;
	}

	return FALSE;
}

int CDBInterface::StartThread()
{
	if (m_hReConnectEvent == NULL)
		m_hReConnectEvent = CreateEvent(NULL, TRUE, FALSE, NULL);	// 无信号事件;

	if (m_hReConnectEvent == NULL)
	{
		m_bSolve = FALSE;
		return -1;
	}

	if (m_hReConnectThread == NULL)
		m_hReConnectThread = CreateThread(NULL, 0, ReConnectDatabaseThread, this, 0, NULL);

	if (m_hReConnectThread == NULL)
	{
		m_bSolve = FALSE;
		return -1;
	}
	else
	{
		CloseHandle(m_hReConnectThread);
		m_hReConnectThread = NULL;
	}

	return 0;
}

int CDBInterface::EndofThread()
{
	if (m_hReConnectEvent)
		SetEvent(m_hReConnectEvent);

	if (m_hReConnectThread)
	{
		if (WaitForSingleObject(m_hReConnectThread, INFINITE) != WAIT_TIMEOUT)
			CloseHandle(m_hReConnectThread);
		m_hReConnectThread = NULL;
	}

	if (m_hReConnectEvent)
		CloseHandle(m_hReConnectEvent);
	m_hReConnectEvent = NULL;

	return 0L;
}

DWORD CDBInterface::ReConnectDatabaseThread(LPVOID lpPara)
{
	CDBInterface *pInstance = (CDBInterface*)lpPara;

	do
	{
		if (pInstance->OpenDatabase())
		{
			pInstance->m_bSolve = FALSE;
			break;
		}
	} while (WaitForSingleObject(pInstance->m_hReConnectEvent, 5000L) == WAIT_TIMEOUT);

	if (pInstance->m_hReConnectEvent)
		CloseHandle(pInstance->m_hReConnectEvent);
	pInstance->m_hReConnectEvent = NULL;

	return 0L;
}

BOOL CDBInterface::OpenDatabase(const TCHAR *szSQLConnectString)
{
	AutoThreadSection aSection(&s_critSection);
	if (m_pdbInstance == NULL)
	{
		m_pdbInstance = new CDatabase;
		if (szSQLConnectString == NULL)
			szSQLConnectString = m_szConnectString;

		try
		{
			m_pdbInstance->OpenEx(szSQLConnectString, CDatabase::noOdbcDialog);
			CString strShow;
			strShow.Format(_T("打开数据库成功"));
			::SendMessage(g_hwnd, MSG_SHOWPROMPTING, (WPARAM)&strShow, 0);
		}
		catch (CDBException* e)
		{
			m_pdbInstance->Close();
			delete m_pdbInstance;
			m_pdbInstance = NULL;

			CString strShow;
			strShow.Format(_T("打开数据库失败:%s"), e->m_strError);

			::SendMessage(g_hwnd, MSG_SHOWPROMPTING, (WPARAM)&strShow, 1);
			e->Delete();
			return FALSE;
		}

		m_bOpen = TRUE;
	}

	return m_bOpen;
}

void CDBInterface::CloseDatabase()
{
	m_bOpen = FALSE;
	if (m_pdbInstance)
	{
		m_pdbInstance->Close();
		delete m_pdbInstance;
	}
	m_pdbInstance = NULL;
}

int CDBInterface::Execute(LPCTSTR pSql)
{
	try
	{
		AutoThreadSection aSection(&s_critSection);
		if (!m_bOpen) return FALSE;
		//m_pdbInstance->BeginTrans();
		m_pdbInstance->ExecuteSQL(pSql);
		//m_pdbInstance->CommitTrans();
	}
	catch (CDBException* e)
	{
		//LOG4C((LOG_NOTICE,"执行SQL失败：%s",e->m_strError));
		//m_pdbInstance->Rollback();
		SolveDBError(GetLastError(), e->m_strError);
		e->Delete();
		return -1;
	}

	return 0;
}

//************************************//
// [函数]：GetSelectCount
// [描述]：通过选择语句“获取数据”
// [参数]：
//		CString* pTableName,					表名
//		CString* pFilter						where 字段条件
// [返回]：返回查询的数量
//
//************************************//
DWORD CDBInterface::GetSelectCount(CString* pTableName, CString* pFilter)
{
	try
	{
		if (!m_bOpen) 
			return -1;
	
		if(pTableName == NULL || (*pTableName) == _T(""))
			return  -1;

		CString	strSQL = _T("select count(*) as cot from ");
		if (!pFilter || (*pFilter) == _T(""))
			strSQL += (*pTableName);
		else
			strSQL += (*pTableName) + _T(" where ") + (*pFilter);
			
		char szSQL[MAX_PATH] = {0};
		WChar2Char(szSQL, strSQL);
		LOG4C((LOG_NOTICE, "执行语句[%s]", szSQL));

		CRecordset tagRecordset(m_pdbInstance);
		tagRecordset.Open(CRecordset::forwardOnly, strSQL);
		tagRecordset.GetFieldValue(_T("cot"), strSQL);
		tagRecordset.Close();

		char szValue[64] = {0};
		WChar2Char(szValue, strSQL);
		LOG4C((LOG_NOTICE, "记录数[%s]", szValue));
		return atol(szValue);
	}
	catch (CDBException* e)
	{
		char szTbaleName[128] = {0};
		char szError[512] = {0};
		WChar2Char(szTbaleName, (*pTableName));
		WChar2Char(szError, e->m_strError);

		LOG4C((LOG_NOTICE, "获取记录集数失败[%s]：%s", szTbaleName, szError));
		SolveDBError(GetLastError(), e->m_strError);
		e->Delete();
		return -1;
	}
}


//************************************//
// [函数]：GetDataBySelect
// [描述]：通过选择语句“获取数据”
// [参数]：
//		CArray<CStringArray, CStringArray>& arr	--------------数据容器
//		const std::vector<CString>& vFieldName -------------- 要读取的字段索引集
//		const UINT nTable	---------------------------------数据表名
//		CString* pfilter --------------------------------sql语句的条件限制Where clause
//		CString* pSort ----------------------------------排序Order By Clause
//		const int nType	---------------------------------=0(将所有字段的数据写入容器)，>0(按索引字段读取数据写入容器)
// [返回]：BOOL
//
//************************************//
BOOL CDBInterface::GetDataBySelect(CArray<CStringArray, CStringArray>& arr, const UINT nTable, std::vector<CString>& vFieldName, CString* pfilter, CString* pSort)
{
	if(vFieldName.empty())
		return FALSE;

	try{
		CString strTableName = _T("");
		GetTableNameByType(nTable, strTableName);
		if(strTableName == _T(""))
			return FALSE;

		AutoThreadSection aSection(&s_critSection);
		if (!IsOpen())
			return FALSE;

		DWORD nCount = GetSelectCount(&strTableName);
		if(nCount == 0)
			return FALSE;

		CRecordset set(m_pdbInstance);
		if (pfilter && _tcscmp((const WCHAR*)pfilter, _T("")) != 0)
			set.m_strFilter = (*pfilter);

		if (pSort && _tcscmp((const WCHAR*)pSort, _T("")) != 0)
			set.m_strSort = (*pSort);


		CString strOutField = _T("");
		int idx = 0;
		int nSize = vFieldName.size();
		std::vector<CString>::iterator it = vFieldName.begin();
		for(;it != vFieldName.end();)
		{
			CString strtmp = _T("");
			if(idx != (nSize - 1))
				strtmp = (*it) + _T(",");
			else
				strtmp = (*it);

			strOutField += strtmp;
			++idx;
			++it;
		}

		CString strSQL = _T("select ") + strOutField + _T(" from ") + strTableName;;
		set.Open(CRecordset::dynaset, strSQL);
		arr.SetSize(nCount, 1);

		int nIndex = 0;
		while (!set.IsEOF())
		{
			arr.ElementAt(nIndex).RemoveAll();
			int i = 0;
			std::vector<CString>::iterator it = vFieldName.begin();
			for(;it != vFieldName.end();)
			{
				arr.ElementAt(nIndex).Add(_T(""));
				CString strValue = _T("");
				set.GetFieldValue((*it), strValue);
				arr.ElementAt(nIndex).SetAt(i, strValue);
				i++;
				++it;
			}

			set.MoveNext();

			nIndex++;
			if(arr.GetSize() <= nIndex)
				break;
		}
		arr.SetSize(nIndex, 1);
		set.Close();
	}
	catch (CDBException *e)
	{
		SolveDBError(GetLastError(), e->m_strError);
		e->Delete();
		return FALSE;
	}

	return TRUE;
}

//************************************//
// [函数]：GetTableNameByString
// [描述]：通过表名类型获取表名
// [参数]：
//		const UINT nTable-------------------------------------表名的enum类型
//		CString& strTableName---------------------------------返回数据表名
// [返回]：
//
//************************************//
void CDBInterface::GetTableNameByType(const UINT nTable, CString& strTableName)
{
	switch(nTable)
	{
	case NAME_renyuan1:
		strTableName = _T("renyuan");//
		break;
	case NAME_renyuan2:
		strTableName = _T("dbo.View_UserInfo");
		break;
	case NAME_accountview:
		strTableName = _T("dbo.accountview");//
		break;
	case NAME_LoginTimeSet:
		strTableName = _T("lonintimeset");
		break;
	case NAME_SetLoginBan:
		strTableName = _T("loninbanset");
		break;
	case NAME_lonindata:
		strTableName = _T("lonindata");
		break;
	case NAME_loginsign:
		strTableName = _T("loginsign");
		break;
	}
}