zhipuzi_pos_windows/zhipuzi_pos_windows/tool/CSqlite3.cpp

#include "../pch/pch.h"
#include "CSqlite3.h"

#include "CSetting.h"

CSqlite3::CSqlite3()
{
    wstring folderPath = CSystem::GetProgramDir() + L"\\db";
	if (!CSystem::IsDirExist(folderPath))
	{
		LOG_INFO("folderPath:" << folderPath.c_str()<<"，没有找到对应的目录，即将创建");
		bool flag = CreateDirectory(folderPath.c_str(), NULL);
		if (flag == false)
		{
			LOG_INFO("新建 db 目录失败！");
		}

		LOG_INFO("新建 db 目录成功！");
	}

    //如果没有这个文件，这里会创建这个文件
    wstring path = CSystem::GetProgramDir() + L"\\db\\pos.db";
    string s_path = CLewaimaiString::UnicodeToUTF8(path);

    m_rc = sqlite3_open(s_path.c_str(), &m_db);

    if(m_rc)
    {
        LOG_INFO("Can't open database: " << sqlite3_errmsg(m_db));
        return;
    }

    else
    {
        //LOG_INFO("Opened database successfully");
    }
}

CSqlite3::~CSqlite3()
{
    if(m_db != NULL)
    {
        sqlite3_close(m_db);
    }
}

bool CSqlite3::InitDB()
{
    //先处理pos_config表
	bool ret = this->InitPosConfig();
	if (!ret)
	{
		return false;
	}

	//再处理pos_chufang_printer表
	ret = this->InitPosChufangPrinter();
	if (!ret)
	{
		return false;
	}

	//再处理pos_user表
	ret = this->InitPosUser();
	if (!ret)
	{
		return false;
	}

	return true;
}

bool CSqlite3::InitPosConfig()
{
	//检查有没有pos_config这个表，如果没有就创建
	std::string sql = "SELECT COUNT(*) FROM sqlite_master where type = 'table' and name = 'pos_config';";
	sqlite3_stmt * stmt = NULL;

	if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
	{
		if (sqlite3_step(stmt) == SQLITE_ROW)
		{
			int count = sqlite3_column_int(stmt, 0);

			if (count == 0)
			{
				//说明没找到这个表，那么这个时候新建这个表，先释放前面的stmt
				sqlite3_finalize(stmt);
				stmt = NULL;

				sql = "CREATE TABLE pos_config("  \
					"name  CHAR(100)  UNIQUE       NOT NULL," \
					"value CHAR(2000)              NOT NULL);";

				if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
				{
					//执行该语句
					if (sqlite3_step(stmt) != SQLITE_DONE)
					{
						LOG_INFO("create table fail: " << sqlite3_errmsg(m_db));

						sqlite3_finalize(stmt);
						return false;
					}

					//走到这里就是表创建成功了
					//LOG_INFO("create table success");
					sqlite3_finalize(stmt);
				}
				else
				{
					LOG_INFO("create table prepare fail: " << sqlite3_errmsg(m_db));

					sqlite3_finalize(stmt);

					return false;
				}
			}
			else
			{
				//说明已经有这个表了，就不用再创建了
				sqlite3_finalize(stmt);
			}

			std::string sql = "SELECT * FROM pos_config;";
			sqlite3_stmt * stmt = NULL;

			if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
			{
				while (sqlite3_step(stmt) == SQLITE_ROW)
				{
					std::string name = (char*)sqlite3_column_text(stmt, 0);
					std::string value = (char*)sqlite3_column_text(stmt, 1);

					CSetting::SetParam(name, value, false);
				}

				sqlite3_finalize(stmt);
			}
			else
			{
				//异常情况
				sqlite3_finalize(stmt);
				return false;
			}
		}
		else
		{
			//异常情况
			sqlite3_finalize(stmt);
			return false;
		}
	}
	else
	{
		//异常情况
		sqlite3_finalize(stmt);
		return false;
	}

	return true;
}

bool CSqlite3::InitPosChufangPrinter()
{
	std::string sql = "SELECT COUNT(*) FROM sqlite_master where type = 'table' and name = 'pos_chufang_printer';";
	sqlite3_stmt * stmt = NULL;

	//读取厨房打印机的参数
	if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
	{
		if (sqlite3_step(stmt) == SQLITE_ROW)
		{
			int count = sqlite3_column_int(stmt, 0);

			if (count == 0)
			{
				//说明没找到这个表，那么这个时候新建这个表，先释放前面的stmt
				sqlite3_finalize(stmt);
				stmt = NULL;

				sql = "CREATE TABLE pos_chufang_printer("  \
					"id         INTEGER          PRIMARY KEY AUTOINCREMENT,"\
					"date       CHAR(100)        NOT NULL," \
					"name       CHAR(100)        NOT NULL," \
					"ip         CHAR(100)        NOT NULL," \
					"guige      CHAR(100)        NOT NULL," \
					"fendan     CHAR(100)        NOT NULL," \
					"fenlei     CHAR(100)        NOT NULL," \
					"fenlei_ids CHAR(2000)              );";

				if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
				{
					//执行该语句
					if (sqlite3_step(stmt) != SQLITE_DONE)
					{
						std::string err = sqlite3_errmsg(m_db);
						LOG_INFO("create table fail: " << err.c_str());

						sqlite3_finalize(stmt);
						return false;
					}

					//走到这里就是表创建成功了
					LOG_INFO("create table success");
					sqlite3_finalize(stmt);
				}
				else
				{
					LOG_INFO("create table prepare fail: " << sqlite3_errmsg(m_db));

					sqlite3_finalize(stmt);

					return false;
				}
			}
			else
			{
				//说明已经有这个表了，就不用再创建了
				sqlite3_finalize(stmt);
			}

			//兼容性检查，判断字段是否存在
			std::string sql_pandduan = "select COUNT(*) from sqlite_master where type = 'table' and tbl_name = 'pos_chufang_printer' and sql like '%fenlei%';";
			stmt = NULL;

			if (sqlite3_prepare_v2(m_db, sql_pandduan.c_str(), -1, &stmt, NULL) == SQLITE_OK)
			{
				if (sqlite3_step(stmt) == SQLITE_ROW)
				{
					int count = sqlite3_column_int(stmt, 0);

					if (count == 0)
					{
						//说明没找到这个字段，准备添加字段
						LOG_INFO("not fount field");
						sqlite3_finalize(stmt);
						stmt = NULL;

						sql = "ALTER TABLE pos_chufang_printer "  \
							"add fenlei     CHAR(100)         NOT NULL DEFAULT '0';";

						LOG_INFO("sql:" << sql.c_str());

						if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
						{
							//执行该语句
							if (sqlite3_step(stmt) != SQLITE_DONE)
							{
								std::string err = sqlite3_errmsg(m_db);
								LOG_INFO("alter table fail: " << err.c_str());

								sqlite3_finalize(stmt);
								return false;
							}

							//走到这里就是表创建成功了
							LOG_INFO("alter table success");
							sqlite3_finalize(stmt);
						}
						else
						{
							LOG_INFO("alter table prepare fail: " << sqlite3_errmsg(m_db));

							sqlite3_finalize(stmt);

							return false;
						}
					}
					else
					{
						LOG_INFO("fount field");

						//说明已经有这2个字段了
						sqlite3_finalize(stmt);
					}
				}
			}
			else
			{
				//异常情况
				sqlite3_finalize(stmt);
				return false;
			}

			sql_pandduan = "select COUNT(*) from sqlite_master where type = 'table' and tbl_name = 'pos_chufang_printer' and sql like '%fenlei_ids%';";
			stmt = NULL;

			if (sqlite3_prepare_v2(m_db, sql_pandduan.c_str(), -1, &stmt, NULL) == SQLITE_OK)
			{
				if (sqlite3_step(stmt) == SQLITE_ROW)
				{
					int count = sqlite3_column_int(stmt, 0);

					if (count == 0)
					{
						//说明没找到这个字段，准备添加字段
						LOG_INFO("not fount field");
						sqlite3_finalize(stmt);
						stmt = NULL;

						sql = "ALTER TABLE pos_chufang_printer "  \
							"add fenlei_ids CHAR(2000) NOT NULL DEFAULT '';";

						LOG_INFO("sql:" << sql.c_str());

						if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
						{
							//执行该语句
							if (sqlite3_step(stmt) != SQLITE_DONE)
							{
								std::string err = sqlite3_errmsg(m_db);
								LOG_INFO("alter table fail: " << err.c_str());

								sqlite3_finalize(stmt);
								return false;
							}

							//走到这里就是表创建成功了
							LOG_INFO("alter table success");
							sqlite3_finalize(stmt);
						}

						else
						{
							LOG_INFO("alter table prepare fail: " << sqlite3_errmsg(m_db));

							sqlite3_finalize(stmt);

							return false;;
						}
					}
					else
					{
						LOG_INFO("fount field");

						//说明已经有这2个字段了
						sqlite3_finalize(stmt);
					}
				}
			}
			else
			{
				//异常情况
				sqlite3_finalize(stmt);
				return false;
			}

			std::string sql = "SELECT * FROM pos_chufang_printer;";
			stmt = NULL;

			if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
			{
				while (sqlite3_step(stmt) == SQLITE_ROW)
				{
					std::string date = (char*)sqlite3_column_text(stmt, 1);
					std::string name = (char*)sqlite3_column_text(stmt, 2);
					std::string ip = (char*)sqlite3_column_text(stmt, 3);
					std::string guige = (char*)sqlite3_column_text(stmt, 4);
					std::string fendan = (char*)sqlite3_column_text(stmt, 5);
					std::string fenlei = (char*)sqlite3_column_text(stmt, 6);
					std::string fenlei_ids = (char*)sqlite3_column_text(stmt, 7);

					//这里仅仅是把数据库内容读到内存，所以之类用false
					CSetting::AddChufangPrinter(date, name, ip, guige, fendan, fenlei, fenlei_ids, false);
				}

				sqlite3_finalize(stmt);
			}
			else
			{
				//异常情况
				sqlite3_finalize(stmt);
				return false;
			}
		}
	}

	return true;
}

bool CSqlite3::InitPosUser()
{
	std::string sql = "SELECT COUNT(*) FROM sqlite_master where type = 'table' and name = 'pos_user';";
	sqlite3_stmt * stmt = NULL;	

	//读取厨房打印机的参数
	if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
	{
		if (sqlite3_step(stmt) == SQLITE_ROW)
		{
			int count = sqlite3_column_int(stmt, 0);

			if (count == 0)
			{
				//说明没找到这个表，那么这个时候新建这个表，先释放前面的stmt
				sqlite3_finalize(stmt);
				stmt = NULL;

				sql = "CREATE TABLE pos_user("  \
					"id         INTEGER          PRIMARY KEY AUTOINCREMENT,"\
					"username       CHAR(100)        NOT NULL," \
					"password         CHAR(100)        NOT NULL);";

				if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
				{
					//执行该语句
					if (sqlite3_step(stmt) != SQLITE_DONE)
					{
						std::string err = sqlite3_errmsg(m_db);
						LOG_INFO("create table fail: " << err.c_str());

						sqlite3_finalize(stmt);
						return false;
					}

					//走到这里就是表创建成功了
					//LOG_INFO("create table success");
					sqlite3_finalize(stmt);
				}
				else
				{
					LOG_INFO("create table prepare fail: " << sqlite3_errmsg(m_db));

					sqlite3_finalize(stmt);

					return false;
				}
			}
			else
			{
				//说明已经有这个表了，就不用再创建了
				sqlite3_finalize(stmt);
			}

			std::string sql = "SELECT * FROM pos_user;";
			sqlite3_stmt * stmt = NULL;

			if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
			{
				while (sqlite3_step(stmt) == SQLITE_ROW)
				{
					std::string username = (char*)sqlite3_column_text(stmt, 1);
					std::string password = (char*)sqlite3_column_text(stmt, 2);

					//这里仅仅是把数据库内容读到内存，所以之类用false
					CSetting::SetUser(username, password);
				}

				sqlite3_finalize(stmt);
			}
			else
			{
				//异常情况
				sqlite3_finalize(stmt);
				return false;
			}
		}
	}

	return true;
}

bool CSqlite3::SaveParams(std::map<std::string, std::string>& params)
{
    int result = sqlite3_exec(m_db, "BEGIN;", 0, 0, 0);

    std::string sql = "delete from pos_config;";
    result = sqlite3_exec(m_db, sql.c_str(), 0, 0, 0);

    for(std::map<std::string, std::string>::iterator it = params.begin(); it != params.end(); it++)
    {
        std::string name = it->first;
        std::string value = it->second;

        sql = "INSERT INTO pos_config (name, value) VALUES ('" + name + "','" + value + "');";
        result = sqlite3_exec(m_db, sql.c_str(), 0, 0, 0);
    }

    result = sqlite3_exec(m_db, "COMMIT;", 0, 0, 0);

    if(result == SQLITE_OK)
    {
        //LOG_INFO("save params success");
        return true;
    }

    LOG_INFO("save params fail:"<< sqlite3_errmsg(m_db));
    return false;
}

bool CSqlite3::SaveChufangPrinter(std::vector<ChufangPrinter>& printers)
{
	int result = sqlite3_exec(m_db, "BEGIN;", 0, 0, 0);

	std::string sql = "delete from pos_chufang_printer;";
	result = sqlite3_exec(m_db, sql.c_str(), 0, 0, 0);

	for (std::vector<ChufangPrinter>::iterator it = printers.begin(); it != printers.end(); it++)
	{
		std::string date = (*it).date;
		std::string name = (*it).name;
		std::string ip = (*it).ip;
		std::string guige = (*it).guige;
		std::string fendan = (*it).fendan;
		std::string fenlei = (*it).fenlei;
		std::string fenlei_ids = (*it).fenlei_ids;

		sql = "INSERT INTO pos_chufang_printer (date, name, ip, guige, fendan, fenlei, fenlei_ids) VALUES ('" + date + "' ,'" + name + "','" + ip + "','" + guige + "','" + fendan + "','" + fenlei + "','" + fenlei_ids + "')";
		result = sqlite3_exec(m_db, sql.c_str(), 0, 0, 0);
	}

	result = sqlite3_exec(m_db, "COMMIT;", 0, 0, 0);

	if (result == SQLITE_OK)
	{
		LOG_INFO("save params success");
		return true;
	}

	LOG_INFO("save params fail");
	return false;
}

bool CSqlite3::SaveUsers(std::map<string, string> users)
{
    int result = sqlite3_exec(m_db, "BEGIN;", 0, 0, 0);

    std::string sql = "delete from pos_user;";
    result = sqlite3_exec(m_db, sql.c_str(), 0, 0, 0);

    for(std::map<std::string, std::string>::iterator it = users.begin(); it != users.end(); it++)
    {
        std::string name = it->first;
        std::string password = it->second;

        sql = "INSERT INTO pos_user (username, password) VALUES ('" + name + "' ,'" + password + "')";
        result = sqlite3_exec(m_db, sql.c_str(), 0, 0, 0);
    }

    result = sqlite3_exec(m_db, "COMMIT;", 0, 0, 0);

    if(result == SQLITE_OK)
    {
        //LOG_INFO("save params success");
        return true;
    }

    //LOG_INFO("save params fail");
    return false;
}