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

// MID表;
#define _CREATE_MID_TABLE_ \
	"CREATE TABLE mid \
(bid  TEXT(16) NOT NULL,\
number  INTEGER NOT NULL,\
pid  INTEGER NOT NULL,\
ctype  TEXT(32) NOT NULL,\
version  TEXT(32) NOT NULL,\
purl  TEXT NOT NULL,\
psize  INTEGER NOT NULL,\
pmd5  TEXT(32) NOT NULL,\
status  INTEGER NOT NULL DEFAULT 0,\
start_date  DATETIME DEFAULT current_timestamp,\
finish_date  DATETIME DEFAULT '',\
des  TEXT NOT NULL DEFAULT \"未下载\",\
PRIMARY KEY (bid ASC));"
#define _SELECT_MID_TABLE_ "SELECT bid, number, pid, ctype, version, purl, psize, pmd5, status, start_date, finish_date, des FROM mid"
#define _INSERT_MID_TABLE_ "INSERT INTO mid(bid, number, pid, ctype, version, purl, psize, pmd5)VALUES\
(%s, %s, %s, %s, %s, %s, %s, %s);"

// Key表;
#define _CREATE_KEYS_TABLE_ \
	"CREATE TABLE keys \
(bid  TEXT(16) NOT NULL,\
sn  TEXT(32) PRIMARY KEY NOT NULL,\
pid  INTEGER NOT NULL,\
keys  TEXT NOT NULL,\
copy_date  DATETIME DEFAULT '',\
report_date  DATETIME DEFAULT '');"
#define _SELECT_KEYS_TABLE_ "SELECT bid, pid, sn, keys, copy_date, report_date FROM keys"

// Log表;
#define _CREATE_LOG_TABLE_ \
	"CREATE TABLE log \
(bid  TEXT(16) NOT NULL,\
type  TEXT(32) NOT NULL,\
sn  TEXT(32) NOT NULL,\
content  TEXT NOT NULL,\
gdate  DATETIME DEFAULT current_timestamp,\
report_date  DATETIME DEFAULT '',\
PRIMARY KEY (type, sn));"
#define _SELECT_LOG_TABLE_ "SELECT type, bid, sn, content, gdate, report_date FROM log"

#define _SELECT_BID_INFO__ \
"select mid.number as tc, \
count(keys.bid) as ac,\
count(case when keys.copy_date <> '' then '抄写成功' end) as cc,\
count(case when keys.report_date <> '' and keys.copy_date <> '' then '上报成功' end) as rc \
from mid inner join keys on mid.bid = keys.bid where mid.bid = '%s';"

#define FREE_MSG if ( psqlite_error )  \
	printf("%s\n",psqlite_error),\
	sqlite3_free(psqlite_error),\
	psqlite_error = NULL


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

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

BOOL CDataImpl::Open()
{
	Close();
	CHAR szpath[MAX_PATH] = {0};
	_stprintf_s(szpath, _T("%sdb\\scbc.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_MSG;
	}

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

	if ( !QueryTable("log"))
	{
		sqlite3_exec(m_psqlite3, _CREATE_LOG_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("QueryTable：表名空！"));
		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("QueryTable:%s"), psqlite_error);
		FREE_MSG;
		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("QueryMidInfo:%s"), psqlite_error);
		FREE_MSG;
		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;
}

// 主要返回keys，用于抄写;
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("QueryKeyInfo:%s"), psqlite_error);
		FREE_MSG;
		return -1;
	}

	if ( nRow == 1)
	{
		data.bid = pazResult[nCol+0];
		data.pid = pazResult[nCol+1];
		data.sn = pazResult[nCol+2];
		data.keys = pazResult[nCol+3];
		data.copy_date = pazResult[nCol+4];
		data.report_date = pazResult[nCol+5];
	}

	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_date = '' LIMIT 0,1000";
	int sqlite_error = sqlite3_get_table(m_psqlite3, strSql.c_str(), &pazResult, &nRow, &nCol, &psqlite_error);
	if ( sqlite_error != SQLITE_OK)
	{
		//Global::WriteTextLog(_T("QueryUnReportKeyInfo:%s"), psqlite_error);
		FREE_MSG;
		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("QueryLogInfo:%s"), psqlite_error);
		FREE_MSG;
		return -1;
	}

	if ( nRow == 1 )
	{
#ifndef USE_UTF8
		data.type = pazResult[nCol+0];
		data.bid = pazResult[nCol+1];
		data.sn = pazResult[nCol+2];
		data.content = pazResult[nCol+3];
		data.gdate = pazResult[nCol+4];
		data.report_date = pazResult[nCol+5];
#else
		// 由Native for SQLite3插入的数据，都是utf8格式;
		data.type = CharEncoding::UTF82ASCII(pazResult[nCol+0]);
		data.bid = CharEncoding::UTF82ASCII(pazResult[nCol+1]);
		data.sn = CharEncoding::UTF82ASCII(pazResult[nCol+2]);
		data.content = CharEncoding::UTF82ASCII(pazResult[nCol+3]);
		data.gdate = pazResult[nCol+4];
		data.report_date = pazResult[nCol+5];
#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, bid, sn, content, gdate FROM log 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("QueryUnReportLogInfo:%s"), psqlite_error);
		FREE_MSG;
		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->bid= pazResult[i*nCol+1];
		it->sn= pazResult[i*nCol+2];
		it->content= pazResult[i*nCol+3];
		it->gdate= pazResult[i*nCol+4];
	}

	sqlite3_free_table(pazResult);

	return nRow;
}

INT CDataImpl::QueryBidInfo(std::string order, BidInfo& binfo)
{
	if ( m_psqlite3 == NULL )
		return -1;

	INT nRow = 0;
	INT nCol = 0;

	char** pazResult = NULL;
	char* psqlite_error = NULL;
	TCHAR szSQL[1024] = {0};
	_stprintf_s(szSQL, _SELECT_BID_INFO__, order.c_str());
	int sqlite_error = sqlite3_get_table(m_psqlite3, szSQL, &pazResult, &nRow, &nCol, &psqlite_error);
	if ( sqlite_error != SQLITE_OK)
	{
		//Global::WriteTextLog(_T("QueryUnReportLogInfo:%s"), psqlite_error);
		FREE_MSG;
		return -1;
	}

	if ( nRow == 1 )
	{
		binfo.tcount = pazResult[nCol+0];
		binfo.sn_count = pazResult[nCol+1];
		binfo.copy_count = pazResult[nCol+2];
		binfo.report_count = pazResult[nCol+3];
	}

	sqlite3_free_table(pazResult);

	return nRow;
}

INT CDataImpl::InsertMidInfo(STMid &data)
{
	if(m_psqlite3 == NULL)
		return -1;

	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("InsertMidInfo:%s"), psqlite_error);
		FREE_MSG;
	}

	return sqlite_error;
}


INT CDataImpl::InsertKeyInfo(STKeys &data)
{
	if(m_psqlite3 == NULL)
		return -1;

	std::string strInsert = "INSERT INTO keys(bid, pid, sn, keys) VALUES ('";
	strInsert.append(data.bid);
	strInsert.append("','");
	strInsert.append(data.pid);
	strInsert.append("','");
	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("InsertKeyInfo:%s"), psqlite_error);
		FREE_MSG;
	}

	return sqlite_error;
}

INT CDataImpl::InsertLogInfo(STLog &data)
{
	if(m_psqlite3 == NULL)
		return -1;

	std::string strInsert = "INSERT INTO log(type, bid, sn, content, report_date) VALUES ('";
	strInsert.append(data.type);
	strInsert.append("','");
	strInsert.append(data.bid);
	strInsert.append("','");
	strInsert.append(data.sn);
	strInsert.append("','");
	strInsert.append(data.content);
	strInsert.append("','");
	strInsert.append(data.report_date);
	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("InsertLogInfo:%s"), psqlite_error);
		FREE_MSG;
	}

	return sqlite_error;
}

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

	char* psqlite_error = NULL;
	INT nRet = sqlite3_exec(m_psqlite3, "begin;", 0, 0, &psqlite_error);
	if ( nRet != SQLITE_OK )
	{
		FREE_MSG;
		return nRet;
	}

	sqlite3_stmt *stmt;
	const char* sql = "INSERT INTO keys(bid, pid, 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->bid.c_str(), it->bid.size(), SQLITE_STATIC);  
		sqlite3_bind_text(stmt, 2, it->pid.c_str(), it->pid.size(), SQLITE_STATIC);  
		sqlite3_bind_text(stmt, 3, it->sn.c_str(), it->sn.size(), SQLITE_STATIC);  
		sqlite3_bind_text(stmt, 4, it->keys.c_str(), it->keys.size(), SQLITE_STATIC);   
		sqlite3_step(stmt);  
	}

	nRet = sqlite3_finalize(stmt); 
	if ( nRet != SQLITE_OK )
	{
		// 回滚事务;
		nRet = sqlite3_exec(m_psqlite3, "rollback;", 0, 0, &psqlite_error);
		FREE_MSG;
		return nRet;
	}

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

	return SQLITE_OK;
}

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

	std::string strInsert = "UPDATE mid SET pid ='";
	strInsert.append(data.pid);
	strInsert.append("', ctype='");
	strInsert.append(data.ctype);
	strInsert.append("', version='");
	strInsert.append(data.version);
	strInsert.append("', purl='");
	strInsert.append(data.purl);
	strInsert.append("', psize='");
	strInsert.append(data.psize);
	strInsert.append("', pmd5='");
	strInsert.append(data.pmd5);
	strInsert.append("', number='");
	strInsert.append(data.number);
	strInsert.append("'");
	strInsert.append(" WHERE bid ='");
	strInsert.append(data.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("UpdateMidInfo:%s"), psqlite_error);
		FREE_MSG;
		return FALSE;
	}

	return TRUE;
}

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("UpdateDownloadStatus:%s"), psqlite_error);
		FREE_MSG;
		return FALSE;
	}

	return TRUE;
}

// 抄写成功状态;
BOOL CDataImpl::UpdateKeyCopyStatus(std::string sn)
{
	if(m_psqlite3 == NULL)
		return FALSE;

	std::string strInsert = "UPDATE keys SET 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("UpdateKeyCopyStatus:%s"), psqlite_error);
		FREE_MSG;
		return FALSE;
	}

	return TRUE;
}

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

	std::string strInsert = "UPDATE keys SET report_date=current_timestamp";
	strInsert.append(" WHERE sn ='");
	strInsert.append(sn);
	strInsert.append("' and copy_date <> '';");

	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("UpdateKeyReportStatus:%s"), psqlite_error);
		FREE_MSG;
		return FALSE;
	}

	return TRUE;
}

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

	std::string strInsert = "UPDATE log SET 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("UpdateLogReportStatus:%s"), psqlite_error);
		FREE_MSG;
		return FALSE;
	}

	return TRUE;
}

BOOL CDataImpl::RemoveBidData(std::string order)
{
	if(m_psqlite3 == NULL)
		return FALSE;

	if ( TransactionBegin() != 0 )
		return FALSE;

	TCHAR szSQL[1024] = {0};
	_stprintf_s(szSQL, "delete from mid where bid = '%s'; delete from keys where bid = '%s'; delete from log where bid = '%s'", order.c_str(), order.c_str(), order.c_str());
	char* psqlite_error = NULL;
	int sqlite_error = sqlite3_exec(m_psqlite3, szSQL, NULL, 0, &psqlite_error);
	if(SQLITE_OK != sqlite_error)
	{
		//Global::WriteTextLog(_T("UpdateLogReportStatus:%s"), psqlite_error);
		FREE_MSG;
		// 回滚事务;
		sqlite3_exec(m_psqlite3, "rollback;", 0, 0, &psqlite_error);
		FREE_MSG;
		return FALSE;
	}

	if ( TransactionCommit() != 0 )
		return FALSE;

	return TRUE;
}