JuniorHighClient/HJUnHandle/DataBaseService.cpp

#include "DataBaseService.h"
#include <fstream>
#include <iostream>
#include "../Util/Util.h"

CDataBaseService::CDataBaseService(void)
{
	GetModuleDir(m_strModuleDir);
	m_strIndexDbPath.append(m_strModuleDir);
	m_strIndexDbPath.append(L"index.db3");
}


CDataBaseService::~CDataBaseService(void)
{

}

bool CDataBaseService::OpenSQLiteDB(CppSQLite3DB& db, const wstring& strdbpath)
{
	try
	{
		string strTest = UnicodeToUtf8(strdbpath.c_str());
		db.open(strTest.c_str());
		db.execDML("pragma journal_mode = MEMORY");
	}
	catch (...)
	{
		return false;
	}
	return true;
}

vector<batch_info> CDataBaseService::GetFailedBatch()
{
	vector<batch_info> infos;
	CppSQLite3DB index_db;
	if (OpenSQLiteDB(index_db, m_strIndexDbPath))
	{
		CppSQLite3Query q = index_db.execQuery("select * from batchs where state > 0");
		while (!q.eof())
		{
			batch_info info;
			info.id = q.getIntField("id");
			strcpy_s(info.create_time, q.getStringField("create_time"));
			string strVal = q.getStringField("batchdb_path");
			strVal = UtfToGbk(strVal);
			strcpy_s(info.batchdb_path, strVal.c_str());
			strcpy_s(info.work_dir, q.getStringField("work_dir"));
			info.total_cnt = q.getIntField("total_cnt");
			info.success_cnt = q.getIntField("success_cnt");
			info.state = q.getIntField("state");
			strcpy_s(info.zipfile_name, q.getStringField("zipfile_name"));
			info.zipfile_cnt = q.getIntField("zipfile_cnt");
			infos.push_back(info);
			q.nextRow();
		}
		q.finalize();
		index_db.close();
	}
	
	return infos;
}

void CDataBaseService::GetBatchInfo(batch_info& info)
{
	CppSQLite3DB index_db;
	if (OpenSQLiteDB(index_db, m_strIndexDbPath))
	{
		char buf[256] = { 0 };
		sprintf(buf, "select * from batchs where id=%d", info.id);
		CppSQLite3Query q = index_db.execQuery(buf);
		if (!q.eof())
		{
			info.state = q.getIntField("state");
		}
		q.finalize();
		index_db.close();
	}
}

vector<wstring> CDataBaseService::GetCashBatch()
{
	vector<wstring> vct;
	CppSQLite3DB index_db;
	if (OpenSQLiteDB(index_db, m_strIndexDbPath))
	{
		char sql[512] = { 0 };
		__int64 _date = std::time(0) - 3600 * 24 * 30; // ÏÖÔÚʱ¼äµ¹ÍÆ30Ììʱ¼ä´Á
		sprintf_s(sql, "select work_dir from batchs where state == -1 and create_time < %lld order by id asc", _date);
		CppSQLite3Query q = index_db.execQuery(sql);
		while (!q.eof())
		{
		    vct.push_back(Utf8ToUnicode(q.getStringField("work_dir")));
			q.nextRow();
		}

		memset(sql, 0, sizeof(sql) / sizeof(char));
		sprintf_s(sql, "delete from batchs where state == -1 and create_time < %lld", _date);
		index_db.execDML(sql);
		q.finalize();

		index_db.close();
	}
	return vct;
}

bool CDataBaseService::InsertPapers(const wstring& db_path, vector<string>& list_papers)
{
	CppSQLite3DB batch_db;
	if (OpenSQLiteDB(batch_db, db_path))
	{
		try
		{
			batch_db.execDML("begin transaction");
			batch_db.execDML("delete from papers");
			for (size_t i = 0; i < list_papers.size() / 2; i++)
			{
				string _page1 = list_papers[i * 2];
				string _page2 = list_papers[i * 2 + 1];

				CppSQLite3Statement stmt = batch_db.compileStatement("INSERT INTO papers (page0, page1, state) VALUES (:page0, :page1, :state)");
				stmt.bind(":page0", _page1.c_str());
				stmt.bind(":page1", _page2.c_str());
				stmt.bind(":state", 0);
				stmt.execDML();
				stmt.finalize();
			}
			batch_db.execDML("commit transaction");
			batch_db.close();
			return true;
		}
		catch (...)
		{
			batch_db.execDML("rollback transaction");
			OutputDebugString(_T("IResultHandler.SavePaper.catch   CppSQLite3Exception"));
			batch_db.close();

			return false;
		}
	}
	else
	{
		return false;
	}
}

bool CDataBaseService::DeletePaper(const wstring& db_path, int paper_id)
{
	CppSQLite3DB batch_db;
	if (OpenSQLiteDB(batch_db, db_path))
	{
		try
		{
			CppSQLite3Statement stmt = batch_db.compileStatement("delete from papers where id=:id");
			stmt.bind(":id", paper_id);
			stmt.execDML();
			stmt.finalize();
			batch_db.close();
			return true;
		}
		catch (...)
		{
			OutputDebugString(_T("IResultHandler.SavePaper.catch   CppSQLite3Exception"));
			batch_db.close();
			return false;
		}
	}
	else
	{
		return false;
	}
}

bool CDataBaseService::DeletePaperAll(const wstring& db_path, int state)
{
	CppSQLite3DB batch_db;
	if (OpenSQLiteDB(batch_db, db_path))
	{
		try
		{
			CppSQLite3Statement stmt = batch_db.compileStatement("delete from papers where state=:state");
			stmt.bind(":state", state);
			stmt.execDML();
			stmt.finalize();
			batch_db.close();
			return true;
		}
		catch (...)
		{
			OutputDebugString(_T("IResultHandler.SavePaper.catch   CppSQLite3Exception"));
			batch_db.close();
			return false;
		}
	}
	else
	{
		return false;
	}
}

bool CDataBaseService::DeleteIndex(int id)
{
	CppSQLite3DB index_db;
	if (OpenSQLiteDB(index_db, m_strIndexDbPath))
	{
		try
		{
			CppSQLite3Statement stmt = index_db.compileStatement("update batchs set state=:state where id=:id");
			stmt.bind(":state", batch_exc_success);
			stmt.bind(":id", id);
			stmt.execDML();
			stmt.finalize();
			index_db.close();
			return true;
		}
		catch (...)
		{
			OutputDebugString(_T("IResultHandler.SavePaper.catch   CppSQLite3Exception"));
			index_db.close();
			return false;
		}
	}
	else
	{
		return false;
	}
}

bool CDataBaseService::UpdateIndexPicWrong(int total_cnt, int id)
{
	CppSQLite3DB index_db;
	if (OpenSQLiteDB(index_db, m_strIndexDbPath))
	{
		try
		{
			CppSQLite3Statement stmt = index_db.compileStatement("UPDATE batchs SET total_cnt=:total_cnt, state=:state WHERE id=:id");
			stmt.bind(":total_cnt", total_cnt);
			stmt.bind(":state", batch_exc_ori);
			stmt.bind(":id", id);
			stmt.execDML();
			stmt.finalize();

			index_db.close();
			return true;
		}
		catch (...)
		{
			OutputDebugString(_T("IResultHandler.SavePaper.catch   CppSQLite3Exception"));
			index_db.close();

			return false;
		}
	}
	else
	{
		return false;
	}
}

bool CDataBaseService::InsertIndex(const wstring& strBatchDBPath, const wstring& strWorkPath, const wstring& strZipfileName, int total_cnt, int& last_id)
{
	CppSQLite3DB index_db;
	if (!OpenSQLiteDB(index_db, m_strIndexDbPath))
	{
		return false;
	}

	CString strBatch(strBatchDBPath.c_str());
	strBatch.Replace(L"\\", L"/");
	CString strDir(strWorkPath.c_str());
	strDir.Replace(L"\\", L"/");

	CppSQLite3Statement stmt = index_db.compileStatement("INSERT INTO batchs (state, create_time, batchdb_path, total_cnt, success_cnt, work_dir, zipfile_name, zipfile_cnt) \
														  														   		VALUES (:state, :create_time, :batchdb_path, :total_cnt, :success_cnt, :work_dir, :zipfile_name, :zipfile_cnt)");
	stmt.bind(":state", 0);
	stmt.bind(":create_time", (sqlite_int64)std::time(0));
	stmt.bind(":batchdb_path", UnicodeToUtf8(strBatch).c_str());
	stmt.bind(":total_cnt", total_cnt);
	stmt.bind(":success_cnt", 0);
	stmt.bind(":work_dir", UnicodeToUtf8(strDir).c_str());
	stmt.bind(":zipfile_cnt", total_cnt * 2);
	stmt.bind(":zipfile_name", UnicodeToUtf8(strZipfileName.c_str()).c_str());
	
	stmt.execDML();
	stmt.finalize();

	last_id = index_db.execScalar("SELECT id FROM batchs ORDER BY ID DESC");
	index_db.close();

	return true;
}

void CDataBaseService::GetBatchPapers(const wstring& db_path, vector<paper_info>& vctPapers)
{
	vctPapers.clear();
	CppSQLite3DB batch_db;
	if (!OpenSQLiteDB(batch_db, db_path))
	{
		return;
	}

	CppSQLite3Query q = batch_db.execQuery("select p.*, b.* from papers p left join batchinfo b order by p.state");
	while (!q.eof())
	{
		paper_info info;
		info.id = q.getIntField("id");
		strcpy_s(info.page0_path, q.getStringField("page0"));
		strcpy_s(info.page1_path, q.getStringField("page1"));
		strcpy_s(info.student_id, q.getStringField("student_id"));
		strcpy_s(info.qr_code, q.getStringField("qr_code"));
		strcpy_s(info.exam_name, q.getStringField("exam_name"));
		strcpy_s(info.subject_name, q.getStringField("subject_name"));
		strcpy_s(info.err_msg, q.getStringField("err_msg"));
		info.state = q.getIntField("state");
		info.scan_time = q.getIntField("scan_time");
		vctPapers.push_back(info);
		q.nextRow();
	}
	q.finalize();
	batch_db.close();
}

void CDataBaseService::GetPaperByID(const wstring& db_path, int id, paper_info& info)
{
	CppSQLite3DB batch_db;
	if (!OpenSQLiteDB(batch_db, db_path))
	{
		return;
	}
	char sql[512] = { 0 };
	sprintf_s(sql, "select * from papers where id=%d", id);
	CppSQLite3Query q = batch_db.execQuery(sql);
	if (!q.eof())
	{
		info.id = q.getIntField("id");
		strcpy_s(info.page0_path, q.getStringField("page0"));
		strcpy_s(info.page1_path, q.getStringField("page1"));
		info.state = q.getIntField("state");
	}
	q.finalize();
	batch_db.close();
}

bool CDataBaseService::UpdatePapersPath(const wstring& db_path, paper_info& info)
{
	CppSQLite3DB batch_db;
	if (!OpenSQLiteDB(batch_db, db_path))
	{
		return false;
	}
	try
	{
		CppSQLite3Statement stmt = batch_db.compileStatement("update papers set page0=:page0, page1=:page1 where id=:id");
		stmt.bind(":page0", info.page0_path);
		stmt.bind(":page1", info.page1_path);
		stmt.bind(":id", info.id);
		stmt.execDML();
		stmt.finalize();

		batch_db.close();
		return true;
	}
	catch (...)
	{
		OutputDebugString(_T("IResultHandler.SavePaper.catch   CppSQLite3Exception"));
		batch_db.close();

		return false;
	}
	return false;
}