scbc_repos/TCL Copy Tool/TCL Copy Tool/db.cpp

#include "pch.h"
#include "db.h"
#include "Global.h"
#include "CharEncoding.h"

// 上报表;
#define _CREATE_REPORT_TABLE_ \
	"CREATE TABLE report \
(url  TEXT NOT NULL,\
content  TEXT NOT NULL, \
gener_date  DATETIME NOT NULL DEFAULT current_timestamp,\
report_date  DATETIME NOT NULL DEFAULT '');"
#define _SELECT_REPORT_TABLE_ "SELECT url, content, report_status FROM report"
#define _INSERT_REPORT_TABLE_ "INSERT INTO report(url, content, report_status) VALUES (%s, %s, %s);"

// 普通日志表;
#define _CREATE_LOG_TABLE_ \
	"CREATE TABLE report \
(type  TEXT(16) NOT NULL,\
content  TEXT NOT NULL, \
gener_date  DATETIME NOT NULL DEFAULT current_timestamp,\
report_date  DATETIME NOT NULL DEFAULT '');"
#define _SELECT_LOG_TABLE_ "SELECT url, content, report_status FROM log"
#define _INSERT_LOG_TABLE_ "INSERT INTO log(url, content, report_status) VALUES (%s, %s, %s);"

#define FREE_MSG if ( psqlite_error )  sqlite3_free(psqlite_error),psqlite_error = NULL

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

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

BOOL CDataImpl::Open(std::string com)
{
	if ( com.size() == 0 )
		return FALSE;

	com.replace(0, 3, "port");

	Close();
	CHAR szpath[MAX_PATH] = {0};
	_stprintf_s(szpath, _T("%sdb\\%s.db"), Global::g_szCurModuleDir, com.c_str());
	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("report"))
	{
		sqlite3_exec(m_psqlite3, _CREATE_REPORT_TABLE_, NULL, NULL, &psqlite_error);
		FREE_MSG;
	}

	return TRUE;
}

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

	m_psqlite3 = 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_MSG;
		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_MSG;
		return FALSE;
	}

	sqlite3_free_table(pazResult);

	return nRow != 0;
}

// 已上报的;
INT CDataImpl::QueryReportInfo(std::vector<STReport> &vtdata)
{
	if ( m_psqlite3 == NULL )
		return -1;

	INT nRow = 0;
	INT nCol = 0;

	char** pazResult = NULL;
	char* psqlite_error = NULL;
	std::string strSql = "SELECT url, content FROM report WHERE report_date <> ''";
	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_MSG;
		return -1;
	}

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

	sqlite3_free_table(pazResult);

	return nRow;
}

// 未上报的;
INT CDataImpl::QueryUnReportInfo(std::vector<STReport>& vtdata)
{
	if (m_psqlite3 == NULL)
		return -1;

	INT nRow = 0;
	INT nCol = 0;

	char** pazResult = NULL;
	char* psqlite_error = NULL;
	std::string strSql = "SELECT url, content FROM report WHERE report_date = ''";
	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_MSG;
		return -1;
	}

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

	sqlite3_free_table(pazResult);

	return nRow;
}

// INT CDataImpl::InsertReportInfo(STReport& data)
// {
// 	if(m_psqlite3 == NULL)
// 		return -1;
// 
// 	std::string strInsert = "INSERT INTO report(url, content)VALUES ('";
// 	strInsert.append(data.url);
// 	strInsert.append("','");
// 	strInsert.append(data.content);
// 	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_MSG;
// 	}
// 
// 	return sqlite_error;
// }

INT CDataImpl::InsertReportInfo(std::string url, std::string content, bool report_status)
{
	if (m_psqlite3 == NULL)
		return -1;

	std::string strInsert = "INSERT INTO report(url, content, report_date)VALUES ('";
	strInsert.append(url);
	strInsert.append("','");
	strInsert.append(content);
	strInsert.append("',");
	if (report_status == true)
	{//上报成功;
		strInsert.append("current_timestamp");
		strInsert.append(");");
	}
	else
	{
		strInsert.append("''");
		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_MSG;
	}

	return sqlite_error;
}

// INT CDataImpl::InsertReportInfo(std::string content)
// {
// 	if (m_psqlite3 == NULL)
// 		return -1;
// 
// 	std::string strInsert = "INSERT INTO report(url, content)VALUES ('";
// 	strInsert.append("https://cn.ota.qhmoka.com/ota-services/report/reportToolsLog.do");
// 	strInsert.append("','");
// 	strInsert.append(content);
// 	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_MSG;
// 	}
// 
// 	return sqlite_error;
// }

INT CDataImpl::InsertReportInfo(std::string report_type, std::map<std::string, std::string> report_data)
{
	if (m_psqlite3 == NULL)
		return -1;

	// Json数据;
	std::string content;
	cJSON* pJson = cJSON_CreateObject();
	if (!pJson)
		return -1;
	cJSON_AddStringToObject(pJson, "reportType", report_type.c_str());

	cJSON* pObj = cJSON_AddObjectToObject(pJson, _T("reportData"));
	for (auto it : report_data)
	{
		cJSON_AddStringToObject(pObj, it.first.c_str(), it.second.c_str());
	}

	char* pJsonText = cJSON_Print(pJson);
	if (!pJsonText)
	{
		cJSON_Delete(pJson);
		return -1;
	}

	content = pJsonText;
	if (pJsonText)
		free(pJsonText);
	cJSON_Delete(pJson);

	std::string strInsert = "INSERT INTO report(url, content)VALUES ('";
	strInsert.append("https://cn.ota.qhmoka.com/ota-services/report/reportToolsLog");
	strInsert.append("','");
	strInsert.append(content);
	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_MSG;
	}

	return sqlite_error;
}

INT CDataImpl::RemoveReportInfo()
{
	if (m_psqlite3 == NULL)
		return -1;

	char** pazResult = NULL;
	char* psqlite_error = NULL;
	CTime ct = CTime::GetCurrentTime() - CTimeSpan(30, 0, 0, 0);
	std::string strSql = "delete from report where report_date <> '' and report_date < '";
	strSql.append(ct.Format("%Y-%m-%d %H:%M:%S").GetString());
	strSql.append("';");
	// 删除嵌套的content数据;
	strSql.append("delete from report where length(content) > 8192 and url = 'https://cn.ota.qhmoka.com/ota-services/report/reportToolsLog';");
	int sqlite_error = sqlite3_exec(m_psqlite3, strSql.c_str(),0, 0, &psqlite_error);
	if (sqlite_error != SQLITE_OK)
	{
		//Global::WriteTextLog(_T("QueryContactsInfo:%s"), m_pszErrmsg);
		FREE_MSG;
		return -1;
	}
	return 0;
}

BOOL CDataImpl::UpdateKeyReportStatus(STReport& data)
{
	if(m_psqlite3 == NULL)
		return FALSE;

	std::string strInsert = "UPDATE report SET report_date= ";
	strInsert.append("current_timestamp");
	strInsert.append(" WHERE url ='");
	strInsert.append(data.url);
	strInsert.append("' and content = '");
	strInsert.append(data.content);
	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_MSG;
		return FALSE;
	}

	return TRUE;
}