#include "stdafx.h"
#include "GB2860SQLite.h"
#include "ToolUnits.h"


CGB2860SQLite::CGB2860SQLite()
{
	m_db = nullptr;
}


CGB2860SQLite::~CGB2860SQLite()
{
}

void CGB2860SQLite::setDbFilepath(const char* pszFilepath)
{
	m_strDbFilepath = pszFilepath;
}

int CGB2860SQLite::init(std::string& strError)
{
	char* pszError = nullptr;
	int nRet;


	// ´ò¿ªÖ¸¶¨µÄÊý¾Ý¿âÎļþ, Èç¹û²»´æÔÚ½«´´½¨Ò»¸öͬÃûµÄÊý¾Ý¿âÎļþ
	nRet = sqlite3_open(m_strDbFilepath.c_str(), &m_db);
	if (nRet != 0) {
		sqlite3_close(m_db);
		m_db = nullptr;
		return -1;
	}


	// ´´½¨Panel±í, Èç¹û¸Ã±í´æÔÚ£¬Ôò²»´´½¨£¬
	// ²¢¸ø³öÌáʾÐÅÏ¢£¬´æ´¢ÔÚzErrMsg ÖÐ
	char* sql = "CREATE TABLE Panels (ID INTEGER PRIMARY KEY,"		\
		"PanelID VARCHAR(64),"										\
		"UnitId INTEGER,"											\
		"RecipeName VARCHAR(64),"									\
		"ReceivedTime TIME,"										\
		"BeginSamplingTime TIME,"									\
		"EndSamplingTime TIME,"										\
		"RemovedTime TIME,"											\
		"Ari1 REAL,"												\
		"Ari2 REAL,"												\
		"Ari3 REAL,"												\
		"Pre1 INTEGER,"												\
		"Tmp1 REAL,"												\
		"Tmp2 REAL,"												\
		"Temp0Max REAL,"											\
		"Temp0Min REAL,"											\
		"Temp0Ave REAL,"											\
		"Temp1Max REAL,"											\
		"Temp1Min REAL,"											\
		"Temp1Ave REAL,"											\
		"Temp2Max REAL,"											\
		"Temp2Min REAL,"											\
		"Temp2Ave REAL,"											\
		"Temp3Max REAL,"											\
		"Temp3Min REAL,"											\
		"Temp3Ave REAL,"											\
		"Temp4Max REAL,"											\
		"Temp4Min REAL,"											\
		"Temp4Ave REAL,"											\
		"Temp5Max REAL,"											\
		"Temp5Min REAL,"											\
		"Temp5Ave REAL,"											\
		"Temp6Max REAL,"											\
		"Temp6Min REAL,"											\
		"Temp6Ave REAL,"											\
		"Temp7Max REAL,"											\
		"Temp7Min REAL,"											\
		"Temp7Ave REAL,"											\
		"Temp8Max REAL,"											\
		"Temp8Min REAL,"											\
		"Temp8Ave REAL,"											\
		"Temp9Max REAL,"											\
		"Temp9Min REAL,"											\
		"Temp9Ave REAL,"											\
		"VacuumMax REAL,"										\
		"VacuumMin REAL,"										\
		"VacuumAve REAL,"										\
		"Pressure0Max REAL,"										\
		"Pressure0Min REAL,"										\
		"Pressure0Ave REAL,"										\
		"Pressure1Max REAL,"										\
		"Pressure1Min REAL,"										\
		"Pressure1Ave REAL,"										\
		"Pressure2Max REAL,"										\
		"Pressure2Min REAL,"										\
		"Pressure2Ave REAL,"										\
		"Pressure3Max REAL,"										\
		"Pressure3Min REAL,"										\
		"Pressure3Ave REAL,"										\
		"Pressure4Max REAL,"										\
		"Pressure4Min REAL,"										\
		"Pressure4Ave REAL,"										\
		"Datas BLOB);";
	nRet = sqlite3_exec(m_db, sql, 0, 0, &pszError);
	if (pszError != nullptr) {
		strError = pszError;
		sqlite3_free(pszError);
	}

	return nRet;
}

int CGB2860SQLite::term()
{
	if (m_db != nullptr) {
		sqlite3_close(m_db);
		m_db = nullptr;
	}

	return 0;
}

int CGB2860SQLite::insertPanel(CPanel* pPanel, std::string& strError)
{
	if (m_db == nullptr) return -1;

	//²åÈëÊý¾Ý
	char* pszError = nullptr;
	char szSQL[1024];

	UNITDATA1& dataMax = pPanel->getMathData(IMAX);
	UNITDATA1& dataMin = pPanel->getMathData(IMIN);
	UNITDATA1& dataAve = pPanel->getMathData(IAVE);
	sprintf_s(szSQL, 1024, "INSERT INTO Panels VALUES(NULL, '%s', %d, '%s', "		\
		"'%s', '%s', '%s', '%s',"		\
		"%f, %f, %f, %d, %f, %f, "		\
		"%f, %f, %f,"			\
		"%f, %f, %f,"			\
		"%f, %f, %f,"			\
		"%f, %f, %f,"			\
		"%f, %f, %f,"			\
		"%f, %f, %f,"			\
		"%f, %f, %f,"			\
		"%f, %f, %f,"			\
		"%f, %f, %f,"			\
		"%f, %f, %f,"			\
		"%f, %f, %f,"			\
		"%f, %f, %f,"			\
		"%f, %f, %f,"			\
		"%f, %f, %f,"			\
		"%f, %f, %f,"			\
		"%f, %f, %f, ?);",
		pPanel->getQRCode().c_str(),
		pPanel->getUnitId(),
		pPanel->getRecipeName().c_str(),
		CToolUnits::timeToString2(pPanel->getReceivedTime()).c_str(),
		CToolUnits::timeToString2(pPanel->getBeginSamplingTime()).c_str(),
		CToolUnits::timeToString2(pPanel->getEndSamplingTime()).c_str(),
		CToolUnits::timeToString2(pPanel->getRemovedTime()).c_str(),
		pPanel->getAir1(),
		pPanel->getAir2(),
		pPanel->getAir3(),
		pPanel->getPre1(),
		pPanel->getTmp1(),
		pPanel->getTmp2(),
		dataMax.temp[0], dataMin.temp[0], dataAve.temp[0],
		dataMax.temp[1], dataMin.temp[1], dataAve.temp[1],
		dataMax.temp[2], dataMin.temp[2], dataAve.temp[2],
		dataMax.temp[3], dataMin.temp[3], dataAve.temp[3],
		dataMax.temp[4], dataMin.temp[4], dataAve.temp[4],
		dataMax.temp[5], dataMin.temp[5], dataAve.temp[5],
		dataMax.temp[6], dataMin.temp[6], dataAve.temp[6],
		dataMax.temp[7], dataMin.temp[7], dataAve.temp[7],
		dataMax.temp[8], dataMin.temp[8], dataAve.temp[8],
		dataMax.temp[9], dataMin.temp[9], dataAve.temp[9],
		dataMax.vacuum, dataMin.vacuum, dataAve.vacuum,
		dataMax.pressure[0], dataMin.pressure[0], dataAve.pressure[0],
		dataMax.pressure[1], dataMin.pressure[1], dataAve.pressure[1],
		dataMax.pressure[2], dataMin.pressure[2], dataAve.pressure[2],
		dataMax.pressure[3], dataMin.pressure[3], dataAve.pressure[3],
		dataMax.pressure[4], dataMin.pressure[4], dataAve.pressure[4]);

	char* pBlod = nullptr;
	int nSize = pPanel->getBlodLen();
	pBlod = new char[nSize];
	pPanel->getBlod(pBlod, nSize);
	sqlite3_stmt *stmt;
	sqlite3_prepare(m_db, szSQL, (int)strlen(szSQL), &stmt, 0);
	sqlite3_bind_blob(stmt, 1, pBlod, nSize, NULL);
	int nRet = sqlite3_step(stmt);
	sqlite3_finalize(stmt);
	return 0;
}

int CGB2860SQLite::getPanelList(const char* pszKeyword, int nSearchFlag,
	int dtFlag, const char* pszStartTime, const char* pszEndTime,
	std::list<CPanel*>& list, std::string& strError)
{
	if (m_db == nullptr) return -1;

	int nRet;
	CString strSQL = _T("SELECT * FROM Panels Where (1=2");
	if ((nSearchFlag & 0x01) == 0x01) {
		strSQL.AppendFormat(" OR PanelID like '%s%s%s'", "%", pszKeyword, "%");
	}
	strSQL.Append(")");
	if (dtFlag != 0) {
		strSQL.AppendFormat(" AND (ReceivedTime BETWEEN '%s' AND '%s')",
			pszStartTime, pszEndTime);
	}
	strSQL.Append(" ORDER BY ID DESC LIMIT 3000");	// ASC

	sqlite3_stmt *stmt;
	sqlite3_prepare(m_db, (LPTSTR)(LPCTSTR)strSQL, strSQL.GetLength(), &stmt, 0);
	nRet = sqlite3_step(stmt);

	while (nRet == SQLITE_ROW) {
		int id, unitId;
		double air1, air2, air3, tmp1, tmp2;
		int pre1;
		const unsigned char* pszQRCode, *pszRecipeName;
		const unsigned char* pszReceivedTime, *pszBeginTime, *pszEndTime, *pszRemovedTime;
		const void * pBolbData;
		int nBlodDataLen;

		id = sqlite3_column_int(stmt, 0);
		pszQRCode = sqlite3_column_text(stmt, 1);
		unitId = sqlite3_column_int(stmt, 2);
		pszRecipeName = sqlite3_column_text(stmt, 3);
		pszReceivedTime = sqlite3_column_text(stmt, 4);
		pszBeginTime = sqlite3_column_text(stmt, 5);
		pszEndTime = sqlite3_column_text(stmt, 6);
		pszRemovedTime = sqlite3_column_text(stmt, 7);
		air1 = sqlite3_column_double(stmt, 8);
		air2 = sqlite3_column_double(stmt, 9);
		air3 = sqlite3_column_double(stmt, 10);
		pre1 = sqlite3_column_int(stmt, 11);
		tmp1 = sqlite3_column_double(stmt, 12);
		tmp2 = sqlite3_column_double(stmt, 13);
		pBolbData = sqlite3_column_blob(stmt, 62);
		nBlodDataLen = sqlite3_column_bytes(stmt, 62);

		CPanel* pPanel = new CPanel();
		pPanel->setQRCode((const char*)pszQRCode);
		pPanel->setUnitId(unitId);
		pPanel->setRecipeName((const char*)pszRecipeName);
		pPanel->setReceivedTime(CToolUnits::stringToTime((const char*)pszReceivedTime));
		pPanel->setBeginSamplingTime(CToolUnits::stringToTime((const char*)pszBeginTime));
		pPanel->setEndSamplingTime(CToolUnits::stringToTime((const char*)pszEndTime));
		pPanel->setRemovedTime(CToolUnits::stringToTime((const char*)pszRemovedTime));
		pPanel->setAir1(air1);
		pPanel->setAir2(air2);
		pPanel->setAir3(air3);
		pPanel->setPre1(pre1);
		pPanel->setTmp1(tmp1);
		pPanel->setTmp2(tmp2);
		pPanel->setBlod((char*)pBolbData, nBlodDataLen);
		list.push_back(pPanel);

		nRet = sqlite3_step(stmt);
	}
	sqlite3_finalize(stmt);

	return (int)list.size();
}

int CGB2860SQLite::getPanelWithPanelId(const char* pszBoxId, CPanel*& pPanel, std::string& strError)
{
	if (m_db == nullptr) return -1;

	int nRet;
	CString strSQL;
	strSQL.Format(_T("SELECT * FROM Panels Where PanelID = '%s' ORDER BY ID ASC"), pszBoxId);
	sqlite3_stmt *stmt;
	sqlite3_prepare(m_db, (LPTSTR)(LPCTSTR)strSQL, strSQL.GetLength(), &stmt, 0);
	nRet = sqlite3_step(stmt);

	if (nRet == SQLITE_ROW) {
		int id, unitId;
		double air1, air2, air3, tmp1, tmp2;
		int pre1;
		const unsigned char* pszQRCode, *pszRecipeName;
		const unsigned char* pszReceivedTime, *pszBeginTime, *pszEndTime, *pszRemovedTime;
		const void * pBolbData;
		int nBlodDataLen;

		id = sqlite3_column_int(stmt, 0);
		pszQRCode = sqlite3_column_text(stmt, 1);
		unitId = sqlite3_column_int(stmt, 2);
		pszRecipeName = sqlite3_column_text(stmt, 3);
		pszReceivedTime = sqlite3_column_text(stmt, 4);
		pszBeginTime = sqlite3_column_text(stmt, 5);
		pszEndTime = sqlite3_column_text(stmt, 6);
		pszRemovedTime = sqlite3_column_text(stmt, 7);
		air1 = sqlite3_column_double(stmt, 8);
		air2 = sqlite3_column_double(stmt, 9);
		air3 = sqlite3_column_double(stmt, 10);
		pre1 = sqlite3_column_int(stmt, 11);
		tmp1 = sqlite3_column_double(stmt, 12);
		tmp2 = sqlite3_column_double(stmt, 13);
		pBolbData = sqlite3_column_blob(stmt, 62);
		nBlodDataLen = sqlite3_column_bytes(stmt, 62);

		CPanel* pPanel = new CPanel();
		pPanel->setQRCode((const char*)pszQRCode);
		pPanel->setUnitId(unitId);
		pPanel->setRecipeName((const char*)pszRecipeName);
		pPanel->setReceivedTime(CToolUnits::stringToTime((const char*)pszReceivedTime));
		pPanel->setBeginSamplingTime(CToolUnits::stringToTime((const char*)pszBeginTime));
		pPanel->setEndSamplingTime(CToolUnits::stringToTime((const char*)pszEndTime));
		pPanel->setRemovedTime(CToolUnits::stringToTime((const char*)pszRemovedTime));
		pPanel->setAir1(air1);
		pPanel->setAir2(air2);
		pPanel->setAir3(air3);
		pPanel->setPre1(pre1);
		pPanel->setTmp1(tmp1);
		pPanel->setTmp2(tmp2);
		pPanel->setBlod((char*)pBolbData, nBlodDataLen);
	}
	sqlite3_finalize(stmt);

	return 0;
}