scbc_repos/scbc.tools/scbc.tools/DataImpl.cpp

#include "StdAfx.h"
#include "DataImpl.h"
#include "CharEncoding.h"
#include "Global.h"

#define FREE_MSG if ( m_pszErrmsg ) { sqlite3_free(m_pszErrmsg); m_pszErrmsg = NULL;}
#define FREE_MSG2 if ( psqlite_error ) { sqlite3_free(psqlite_error); psqlite_error = NULL;}

CDataImpl::CDataImpl(void):m_psqlite3(NULL),m_pszErrmsg(NULL)
{
	//sqlite3_threadsafe();
}

CDataImpl::~CDataImpl(void)
{
	Close();
}

BOOL CDataImpl::Open()
{
	Close();
	CHAR szpath[MAX_PATH] = {0};
	_stprintf_s(szpath, _T("%ssms.db"), Global::g_szCurModuleDir);
	std::string strPath;
	if ( !CharEncoding::ASCII2UTF8(szpath,strPath))
	{
		return FALSE;
	}

	INT nResult = sqlite3_open(strPath.c_str(), &m_psqlite3);
	if ( nResult != SQLITE_OK )
		return FALSE;

	// 创建表;
	char* psqlite_error = NULL;
	if ( !QueryTable("mid"))
	{
		sqlite3_exec(m_psqlite3, _CREATE_MID_TABLE_, NULL, NULL, &psqlite_error);
		FREE_MSG2
	}

	if ( !QueryTable("keys"))
	{
		sqlite3_exec(m_psqlite3, _CREATE_KEYS_TABLE_, NULL, NULL, &psqlite_error);
		FREE_MSG2
	}

	if ( !QueryTable("log"))
	{
		sqlite3_exec(m_psqlite3, _CREATE_LOG_TABLE_, NULL, NULL, &psqlite_error);
		FREE_MSG2
	}

	return TRUE;
}

void CDataImpl::Close()
{
	if ( m_psqlite3 )
		sqlite3_close(m_psqlite3);

	m_psqlite3 = NULL;
	if ( m_pszErrmsg )
		sqlite3_free(m_pszErrmsg);
	m_pszErrmsg = NULL;
}

BOOL CDataImpl::ExecteSQL(IN LPCSTR lpSQL)
{
	if ( lpSQL == NULL || lpSQL[0] == '\0' )
	{
		//Global::WriteTextLog(_T("ExecteSQL：执行语句空！"));
		return FALSE;
	}

	if(m_psqlite3 == NULL)
		return FALSE;

	char* psqlite_error = NULL;
	int sqlite_error = sqlite3_exec(m_psqlite3, lpSQL, NULL, 0, &psqlite_error);
	if(SQLITE_OK != sqlite_error)
	{
		//Global::WriteTextLog(_T("ExecteSQL:%s"), psqlite_error);
		FREE_MSG2
		return FALSE;
	}

	return TRUE;
}

BOOL CDataImpl::QueryTable(std::string table)
{
	if ( table.size() == 0 )
	{
		//Global::WriteTextLog(_T("ExecteSQL：执行语句空！"));
		return FALSE;
	}

	if(m_psqlite3 == NULL)
		return FALSE;

	INT nRow = 0;
	INT nCol = 0;

	char** pazResult = NULL;
	char* psqlite_error = NULL;
	char szSql[MAX_PATH] = {0};
	_stprintf_s(szSql, _T("select * from sqlite_master where type = 'table' and name = '%s'"), table.c_str());
	int sqlite_error = sqlite3_get_table(m_psqlite3, szSql, &pazResult, &nRow, &nCol, &psqlite_error);
	if ( sqlite_error != SQLITE_OK)
	{
		//Global::WriteTextLog(_T("QueryContactsType:%s"), m_pszErrmsg);
		FREE_MSG2
		return FALSE;
	}

	sqlite3_free_table(pazResult);

	return nRow != 0;
}


INT CDataImpl::QueryMidInfo(std::string order, STMid &data)
{
	if ( m_psqlite3 == NULL )
		return -1;

	INT nRow = 0;
	INT nCol = 0;

	char** pazResult = NULL;
	char* psqlite_error = NULL;
	std::string strSql = _SELECT_MID_TABLE_;
	strSql.append(" WHERE bid = '");
	strSql.append(order);
	strSql.append("';");
	int sqlite_error = sqlite3_get_table(m_psqlite3, strSql.c_str(), &pazResult, &nRow, &nCol, &psqlite_error);
	if ( sqlite_error != SQLITE_OK)
	{
		//Global::WriteTextLog(_T("QueryContactsType:%s"), m_pszErrmsg);
		FREE_MSG2
		return -1;
	}

	if( nRow == 1)
	{
#ifndef USE_UTF8
		data.order = pazResult[nCol+0];
		data.number = pazResult[nCol+1];
		data.pid = pazResult[nCol+2];
		data.ctype = pazResult[nCol+3];
		data.version = pazResult[nCol+4];
		data.purl = pazResult[nCol+5];
		data.psize = pazResult[nCol+6];
		data.pmd5 = pazResult[nCol+7];
		data.status = pazResult[nCol+8];
		data.start_date = pazResult[nCol+9];
		data.finish_date = pazResult[nCol+10];
		data.des = pazResult[nCol+11];
#else
		// 由Native for SQLite3插入的数据，都是utf8格式;
		data.order = CharEncoding::UTF82ASCII(pazResult[nCol+0]);
		data.number = pazResult[nCol+1];
		data.pid = pazResult[nCol+2];
		data.ctype = CharEncoding::UTF82ASCII(pazResult[nCol+3]);
		data.version = CharEncoding::UTF82ASCII(pazResult[nCol+4]);
		data.purl = CharEncoding::UTF82ASCII(pazResult[nCol+5]);
		data.psize = pazResult[nCol+6];
		data.pmd5 = CharEncoding::UTF82ASCII(pazResult[nCol+7]);
		data.status = pazResult[nCol+8];
		data.start_date = CharEncoding::UTF82ASCII(pazResult[nCol+9]);
		data.finish_date = CharEncoding::UTF82ASCII(pazResult[nCol+10]);
		data.des = CharEncoding::UTF82ASCII(pazResult[nCol+11]);
#endif
	}

	sqlite3_free_table(pazResult);

	return nRow;
}

INT CDataImpl::QueryKeyInfo(std::string sn, STKeys &data)
{
	if ( m_psqlite3 == NULL )
		return -1;

	INT nRow = 0;
	INT nCol = 0;

	char** pazResult = NULL;
	char* psqlite_error = NULL;
	std::string strSql = _SELECT_KEYS_TABLE_;
	strSql.append(" WHERE sn = '");
	strSql.append(sn);
	strSql.append("';");
	int sqlite_error = sqlite3_get_table(m_psqlite3, strSql.c_str(), &pazResult, &nRow, &nCol, &psqlite_error);
	if ( sqlite_error != SQLITE_OK)
	{
		//Global::WriteTextLog(_T("QueryContactsInfo:%s"), m_pszErrmsg);
		FREE_MSG2
		return -1;
	}

	if ( nRow == 1)
	{
#ifndef USE_UTF8
		data.sn = pazResult[nCol+0];
		data.keys = pazResult[nCol+1];
		data.copy_date = pazResult[nCol+2];
		data.report_date = pazResult[nCol+3];
		data.copy_status = pazResult[nCol+4];
		data.report_status = pazResult[nCol+5];
#else
		// 由Native for SQLite3插入的数据，都是utf8格式;
		data.sn = CharEncoding::UTF82ASCII(pazResult[nCol+0]);
		data.keys = CharEncoding::UTF82ASCII(pazResult[nCol+1]);
		data.copy_date = CharEncoding::UTF82ASCII(pazResult[nCol+2]);
		data.report_date = CharEncoding::UTF82ASCII(pazResult[nCol+3]);
		data.copy_status = pazResult[nCol+4];
		data.report_status = pazResult[nCol+5];
#endif
	}

	sqlite3_free_table(pazResult);

	return nRow;
}

INT CDataImpl::QueryUnReportKeyInfo(std::vector<STKeys> &vtdata)
{
	if ( m_psqlite3 == NULL )
		return -1;

	INT nRow = 0;
	INT nCol = 0;

	char** pazResult = NULL;
	char* psqlite_error = NULL;
	std::string strSql = "SELECT sn,copy_date FROM keys WHERE copy_date <> '' and report_status in(0,-1)";
	int sqlite_error = sqlite3_get_table(m_psqlite3, strSql.c_str(), &pazResult, &nRow, &nCol, &psqlite_error);
	if ( sqlite_error != SQLITE_OK)
	{
		//Global::WriteTextLog(_T("QueryContactsInfo:%s"), m_pszErrmsg);
		FREE_MSG2
		return -1;
	}

	vtdata.resize(nRow);
	std::vector<STKeys>::iterator it = vtdata.begin();
	for(int i = 1; i <= nRow; i++, it++)
	{
		it->sn = pazResult[i*nCol+0];
		it->copy_date= pazResult[i*nCol+1];
	}

	sqlite3_free_table(pazResult);

	return nRow;
}

INT CDataImpl::QueryLogInfo(std::string sn, STLog &data)
{
	if ( m_psqlite3 == NULL )
		return -1;

	INT nRow = 0;
	INT nCol = 0;

	char** pazResult = NULL;
	char* psqlite_error = NULL;
	int sqlite_error = sqlite3_get_table(m_psqlite3, _SELECT_LOG_TABLE_, &pazResult, &nRow, &nCol, &psqlite_error);
	if ( sqlite_error != SQLITE_OK)
	{
		//Global::WriteTextLog(_T("QueryTaskInfo:%s"), m_pszErrmsg);
		FREE_MSG2
		return -1;
	}

	if ( nRow == 1 )
	{
#ifndef USE_UTF8
		data.type = pazResult[nCol+0];
		data.sn = pazResult[nCol+1];
		data.content = pazResult[nCol+2];
		data.gdate = pazResult[nCol+3];
		data.report_status = pazResult[nCol+4];
#else
		// 由Native for SQLite3插入的数据，都是utf8格式;
		data.type = CharEncoding::UTF82ASCII(pazResult[nCol+0]);
		data.sn = CharEncoding::UTF82ASCII(pazResult[nCol+1]);
		data.content = CharEncoding::UTF82ASCII(pazResult[nCol+2]);
		data.gdate = pazResult[nCol+3];
		data.report_status = pazResult[nCol+4];
#endif
	}

	sqlite3_free_table(pazResult);

	return nRow;
}

INT CDataImpl::QueryUnReportLogInfo(std::vector<STLog> &vtdata)
{
	if ( m_psqlite3 == NULL )
		return -1;

	INT nRow = 0;
	INT nCol = 0;

	char** pazResult = NULL;
	char* psqlite_error = NULL;
	std::string strSql = "SELECT type, sn, content, gdate FROM log WHERE report_status in(0,-1)";
	int sqlite_error = sqlite3_get_table(m_psqlite3, strSql.c_str(), &pazResult, &nRow, &nCol, &psqlite_error);
	if ( sqlite_error != SQLITE_OK)
	{
		//Global::WriteTextLog(_T("QueryContactsInfo:%s"), m_pszErrmsg);
		FREE_MSG2
		return -1;
	}

	vtdata.resize(nRow);
	std::vector<STLog>::iterator it = vtdata.begin();
	for(int i = 1; i <= nRow; i++, it++)
	{
		it->type = pazResult[i*nCol+0];
		it->sn= pazResult[i*nCol+1];
		it->content= pazResult[i*nCol+2];
		it->gdate= pazResult[i*nCol+3];
	}

	sqlite3_free_table(pazResult);

	return nRow;
}

BOOL CDataImpl::InsertMidInfo(STMid &data)
{
	if(m_psqlite3 == NULL)
		return FALSE;

	std::string strInsert = "INSERT INTO mid(bid, number, pid, ctype, version, purl, psize, pmd5)VALUES ('";
	strInsert.append(data.order);
	strInsert.append("','");
	strInsert.append(data.number);
	strInsert.append("','");
	strInsert.append(data.pid);
	strInsert.append("','");
	strInsert.append(data.ctype);
	strInsert.append("','");
	strInsert.append(data.version);
	strInsert.append("','");
	strInsert.append(data.purl);
	strInsert.append("','");
	strInsert.append(data.psize);
	strInsert.append("','");
	strInsert.append(data.pmd5);
	strInsert.append("');");

	char* psqlite_error = NULL;
	int sqlite_error = sqlite3_exec(m_psqlite3, strInsert.c_str(), NULL, 0, &psqlite_error);
	if(SQLITE_OK != sqlite_error)
	{
		//Global::WriteTextLog(_T("InsertContactsType:%s"), psqlite_error);
		FREE_MSG2
		return FALSE;
	}

	return TRUE;
}


BOOL CDataImpl::InsertKeyInfo(STKeys &data)
{
	if(m_psqlite3 == NULL)
		return FALSE;

	std::string strInsert = "INSERT INTO keys(sn, keys) VALUES ('";
	strInsert.append(data.sn);
	strInsert.append("','");
	strInsert.append(data.keys);
	strInsert.append("');");
	char* psqlite_error = NULL;
	int sqlite_error = sqlite3_exec(m_psqlite3, strInsert.c_str(), NULL, 0, &psqlite_error);
	if(SQLITE_OK != sqlite_error)
	{
		//Global::WriteTextLog(_T("InsertContactsInfo:%s"), psqlite_error);
		FREE_MSG2
		return FALSE;
	}

	return TRUE;
}

BOOL CDataImpl::InsertLogInfo(STLog &data)
{
	if(m_psqlite3 == NULL)
		return FALSE;

	std::string strInsert = "INSERT INTO log(type, sn, content, report_date, report_status) VALUES ('";
	strInsert.append(data.type);
	strInsert.append("','");
	strInsert.append(data.sn);
	strInsert.append("','");
	strInsert.append(data.content);
	strInsert.append("','");
	strInsert.append(data.report_date);
	strInsert.append("','");
	strInsert.append(data.report_status);
	strInsert.append("');");
	char* psqlite_error = NULL;
	int sqlite_error = sqlite3_exec(m_psqlite3, strInsert.c_str(), NULL, 0, &psqlite_error);
	if(SQLITE_OK != sqlite_error)
	{
		//Global::WriteTextLog(_T("InsertContactsInfo:%s"), psqlite_error);
		FREE_MSG2
		return FALSE;
	}

	return TRUE;
}

INT CDataImpl::BatchInsertKeyInfo(std::vector<STKeys> &vtdata)
{
	char* psqlite_error = NULL;
	INT nRet = sqlite3_exec(m_psqlite3, "begin;", 0, 0, &psqlite_error);
	if ( nRet != SQLITE_OK )
	{
		FREE_MSG2
		return -1;
	}

	sqlite3_stmt *stmt;
	const char* sql = "INSERT INTO keys(sn, keys) VALUES (?,?)";
	sqlite3_prepare_v2(m_psqlite3, sql, strlen(sql), &stmt, 0);

	std::vector<STKeys>::iterator it = vtdata.begin();
	for ( int i = 0; it != vtdata.end(); it++, i++ )
	{
		sqlite3_reset(stmt);  
		sqlite3_bind_text(stmt, 1, it->sn.c_str(), it->sn.size(), SQLITE_STATIC);  
		sqlite3_bind_text(stmt, 2, it->keys.c_str(), it->keys.size(), SQLITE_STATIC);   
		sqlite3_step(stmt);  
	}

	nRet = sqlite3_finalize(stmt); 
	if ( nRet != SQLITE_OK )
	{
		return -1;
	}

	nRet = sqlite3_exec(m_psqlite3, "commit;", 0, 0, &psqlite_error);
	if ( nRet != SQLITE_OK )
	{
		FREE_MSG2
		return -1;
	}

	return vtdata.size();
}

BOOL CDataImpl::UpdateDownloadStatus(std::string order, int status, std::string des /* = "" */ )
{
	if(m_psqlite3 == NULL)
		return FALSE;

	char szStatus[10] = {0};
	_itoa_s(status, szStatus, 10);
	std::string strInsert = "UPDATE mid SET status ='";
	strInsert.append(szStatus);
	strInsert.append("', des='");
	if ( status == -1 )
	{	
		strInsert.append(des);
		strInsert.append("'");
	}
	else if ( status == 1 )
	{
		strInsert.append("下载成功");
		strInsert.append("',finish_date=current_timestamp");
	}
	strInsert.append(" WHERE bid ='");
	strInsert.append(order);
	strInsert.append("';");

	char* psqlite_error = NULL;
	int sqlite_error = sqlite3_exec(m_psqlite3, strInsert.c_str(), NULL, 0, &psqlite_error);
	if(SQLITE_OK != sqlite_error)
	{
		//Global::WriteTextLog(_T("UpdateContactsType:%s"), psqlite_error);
		FREE_MSG2
		return FALSE;
	}

	return TRUE;
}

BOOL CDataImpl::UpdateKeyCopyStatus(std::string sn, int status)
{
	if(m_psqlite3 == NULL)
		return FALSE;

	// 只记录抄写成功;
	if ( status != 1)
		return FALSE;

	char szStatus[10] = {0};
	_itoa_s(status, szStatus, 10);
	std::string strInsert = "UPDATE keys SET copy_status = '";
	strInsert.append(szStatus);
	strInsert.append("', copy_date=current_timestamp");
	strInsert.append(" WHERE sn ='");
	strInsert.append(sn);
	strInsert.append("';");

	char* psqlite_error = NULL;
	int sqlite_error = sqlite3_exec(m_psqlite3, strInsert.c_str(), NULL, 0, &psqlite_error);
	if(SQLITE_OK != sqlite_error)
	{
		//Global::WriteTextLog(_T("UpdateContactsInfo:%s"), psqlite_error);
		FREE_MSG2
		return FALSE;
	}

	return TRUE;
}

BOOL CDataImpl::UpdateKeyReportStatus(std::string sn, int status)
{
	if(m_psqlite3 == NULL)
		return FALSE;

	// 只记录抄写成功;
	if ( status != 1)
		return FALSE;

	char szStatus[10] = {0};
	_itoa_s(status, szStatus, 10);
	std::string strInsert = "UPDATE keys SET report_status = '";
	strInsert.append(szStatus);
	strInsert.append("', report_date=current_timestamp");
	strInsert.append(" WHERE sn ='");
	strInsert.append(sn);
	strInsert.append("';");

	char* psqlite_error = NULL;
	int sqlite_error = sqlite3_exec(m_psqlite3, strInsert.c_str(), NULL, 0, &psqlite_error);
	if(SQLITE_OK != sqlite_error)
	{
		//Global::WriteTextLog(_T("UpdateContactsInfo:%s"), psqlite_error);
		FREE_MSG2
		return FALSE;
	}

	return TRUE;
}

BOOL CDataImpl::UpdateLogReportStatus(std::string sn, std::string type, int status)
{
	if(m_psqlite3 == NULL)
		return FALSE;

	// 只记录抄写成功;
	if ( status != 1)
		return FALSE;

	char szStatus[10] = {0};
	_itoa_s(status, szStatus, 10);
	std::string strInsert = "UPDATE log SET report_status = '";
	strInsert.append(szStatus);
	strInsert.append("', report_date=current_timestamp");
	strInsert.append(" WHERE sn ='");
	strInsert.append(sn);
	strInsert.append("' and type ='");
	strInsert.append(type);
	strInsert.append("';");

	char* psqlite_error = NULL;
	int sqlite_error = sqlite3_exec(m_psqlite3, strInsert.c_str(), NULL, 0, &psqlite_error);
	if(SQLITE_OK != sqlite_error)
	{
		//Global::WriteTextLog(_T("UpdateContactsInfo:%s"), psqlite_error);
		FREE_MSG2
		return FALSE;
	}

	return TRUE;
}