zhipuzi_pos_windows/zhipuzi_pos_windows/tool/CSqlite3.cpp

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

CSqlite3::CSqlite3()
{
	try
	{
		sqlite3_config(SQLITE_CONFIG_MULTITHREAD);

		std::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 目录成功！");
		}

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

		//LOG_INFO("s_path:" << s_path.c_str());

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

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

		//LOG_INFO("Opened database successfully");

	}
	catch (const std::exception& e)
	{
		std::string aa = std::string(e.what());
		LOG_INFO(("创建数据库文件失败: " + std::string(e.what())).c_str());
		return ;
	}
}

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

/**
 * 对数据库表进行一些初始化和检查，这个函数每次程序启动的时候执行1次，就不会再执行了
 */
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;
	}

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

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

	//再处理pos_foodpackage表
	ret = this->InitPosFoodPackage();
	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_ERROR("create table fail: " << sqlite3_errmsg(m_db));

						sqlite3_finalize(stmt);
						return false;
					}

					//走到这里就是表创建成功了
					//LOG_INFO("create table success");
					sqlite3_finalize(stmt);
				}
				else
				{
					LOG_ERROR("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::GetInstance()->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;
					}

					//走到这里就是表创建成功了
					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)
					{
						//说明没找到这个字段，准备添加字段
						sqlite3_finalize(stmt);
						stmt = NULL;

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

						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
					{
						//说明已经有这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)
					{
						//说明没找到这个字段，准备添加字段
						sqlite3_finalize(stmt);
						stmt = NULL;

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

						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;
							}

							//走到这里就是表创建成功了
							sqlite3_finalize(stmt);
						}

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

							sqlite3_finalize(stmt);

							return false;;
						}
					}
					else
					{
						//说明已经有这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::GetInstance()->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::GetInstance()->SetUser(username, password);
				}

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

	return true;
}

bool CSqlite3::InitPosFood()
{
	//检查有没有pos_food这个表，如果没有就创建
	std::string sql = "SELECT COUNT(*) FROM sqlite_master where type = 'table' and name = 'pos_food';";
	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_food("  \
					"id         CHAR(20)          UNIQUE NOT NULL,"\
					"shop_id    CHAR(200)         NOT NULL," \
					"name       CHAR(100)        NOT NULL," \
					"price      CHAR(100)        NOT NULL," \
					"tag        CHAR(100)        NOT NULL," \
					"status     CHAR(100)        NOT NULL," \
					"type_id    CHAR(20)          NOT NULL," \
					"is_dabao    CHAR(20)          NOT NULL," \
					"dabao_money    CHAR(100)          NOT NULL," \
					"is_nature    CHAR(20)         NOT NULL," \
					"nature   CHAR(10000)          NOT NULL," \
					"autostocknum    CHAR(100)          NOT NULL," \
					"goods_img    CHAR(100)          NOT NULL," \
					"unit    CHAR(100)          NOT NULL," \
					"barcode    CHAR(100)          NOT NULL," \
					"member_price_used    CHAR(100)          NOT NULL," \
					"member_price   CHAR(100)          NOT NULL," \
					"buying_price    CHAR(100)          NOT NULL," \
					"stock    CHAR(100)          NOT NULL," \
					"stockvalid    CHAR(100)          NOT NULL," \
					"stock_warning    CHAR(100)          NOT NULL," \
					"is_shouyinji_show    CHAR(20)          NOT NULL," \
					"is_waimai_show    CHAR(20)          NOT NULL," \
					"is_tangshi_show    CHAR(20)          NOT NULL," \
					"is_zhengcan_show    CHAR(20)          NOT NULL," \
					"expiration_date   CHAR(100)          NOT NULL," \
					"is_weight    CHAR(20)          NOT NULL," \
					"weight_plu_code    CHAR(20)          NOT NULL," \
					"weight_food_code    CHAR(20)          NOT NULL," \
					"jiagong_type    CHAR(20)          NOT NULL," \
					"cost_mode    CHAR(20)          NOT NULL," \
					"member_price_json    CHAR(100)          NOT NULL);";

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

						sqlite3_finalize(stmt);
						return false;
					}

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

					sqlite3_finalize(stmt);

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

	return true;
}

bool CSqlite3::InitPosFoodType()
{
	//检查有没有pos_foodtype这个表，如果没有就创建
	std::string sql = "SELECT COUNT(*) FROM sqlite_master where type = 'table' and name = 'pos_foodtype';";
	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_foodtype("  \
					"id         CHAR(20)          UNIQUE NOT NULL,"\
					"name    CHAR(200)         NOT NULL," \
					"is_shouyinji_show   CHAR(100)          NOT NULL," \
					"tag   CHAR(100)          NOT NULL);";

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

						sqlite3_finalize(stmt);
						return false;
					}

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

					sqlite3_finalize(stmt);

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

	return true;
}

bool CSqlite3::InitPosFoodPackage()
{
	//检查有没有pos_foodpackage这个表，如果没有就创建
	std::string sql = "SELECT COUNT(*) FROM sqlite_master where type = 'table' and name = 'pos_foodpackage';";
	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_foodpackage("  \
					"id         CHAR(20)          UNIQUE NOT NULL,"\
					"shop_id    CHAR(200)         NOT NULL," \
					"name       CHAR(100)        NOT NULL," \
					"price      CHAR(100)        NOT NULL," \
					"tag        CHAR(100)        NOT NULL," \
					"status     CHAR(100)        NOT NULL," \
					"is_dabao    CHAR(20)          NOT NULL," \
					"dabao_money    CHAR(100)          NOT NULL," \
					"nature   CHAR(10000)          NOT NULL," \
					"goods_img    CHAR(100)          NOT NULL," \
					"unit    CHAR(100)          NOT NULL," \
					"supporttype    CHAR(100)          NOT NULL," \
					"is_shouyinji_show    CHAR(100)          NOT NULL," \
					"barcode    CHAR(100)          NOT NULL);";

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

						sqlite3_finalize(stmt);
						return false;
					}

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

					sqlite3_finalize(stmt);

					return false;
				}
			}
			else
			{
				//说明已经有这个表了，就不用再创建了
				sqlite3_finalize(stmt);
			}
		}
		else
		{
			//异常情况
			sqlite3_finalize(stmt);
			return false;
		}
	}
	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 = CLewaimaiString::SqlZhuanyi(it->first);
        std::string value = CLewaimaiString::SqlZhuanyi(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 = CLewaimaiString::SqlZhuanyi((*it).date);
		std::string name = CLewaimaiString::SqlZhuanyi((*it).name);
		std::string ip = CLewaimaiString::SqlZhuanyi((*it).ip);
		std::string guige = CLewaimaiString::SqlZhuanyi((*it).guige);
		std::string fendan = CLewaimaiString::SqlZhuanyi((*it).fendan);
		std::string fenlei = CLewaimaiString::SqlZhuanyi((*it).fenlei);
		std::string fenlei_ids = CLewaimaiString::SqlZhuanyi((*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<std::string, std::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 = CLewaimaiString::SqlZhuanyi(it->first);
        std::string password = CLewaimaiString::SqlZhuanyi(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;
}

bool CSqlite3::InitFoodData(rapidjson::Value& foodrows)
{
	this->ExeSQl("begin;");

	//先清空之前的旧数据，重新完整写入新数据
	std::string sql_delete = "delete from pos_food;";
	this->ExeSQl(sql_delete);

	for (rapidjson::SizeType i = 0; i < foodrows.Size(); ++i)
	{
		rapidjson::Value& foodinfo = foodrows[i];

		std::string id = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["id"]));
		std::string shop_id = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["shop_id"]));
		std::string name = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["name"]));
		std::string price = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["price"]));
		std::string tag = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["tag"]));
		std::string status = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["status"]));
		std::string type_id = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["type_id"]));
		std::string is_dabao = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["is_dabao"]));
		std::string dabao_money = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["dabao_money"]));
		std::string is_nature = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["is_nature"]));

		std::string nature = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::JsonToString(foodinfo["nature"]));

		std::string autostocknum = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["autostocknum"]));
		std::string goods_img = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["goods_img"]));
		std::string unit = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["unit"]));
		std::string barcode = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["barcode"]));
		std::string member_price_used = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["member_price_used"]));
		std::string member_price = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["member_price"]));
		std::string buying_price = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["buying_price"]));
		std::string stock = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["stock"]));
		std::string stockvalid = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["stockvalid"]));
		std::string stock_warning = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["stock_warning"]));
		std::string is_shouyinji_show = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["is_shouyinji_show"]));
		std::string expiration_date = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["expiration_date"]));
		std::string is_weight = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["is_weight"]));
		std::string member_price_json = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["member_price_json"]));

		std::string is_waimai_show = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["is_waimai_show"]));
		std::string is_tangshi_show = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["is_tangshi_show"]));
		std::string is_zhengcan_show = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["is_zhengcan_show"]));
		std::string jiagong_type = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["jiagong_type"]));
		std::string cost_mode = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["cost_mode"]));

		std::string weight_plu_code;
		if (foodinfo["weight_plu_code"].IsString())
		{
			weight_plu_code = CLewaimaiString::SqlZhuanyi(foodinfo["weight_plu_code"].GetString());
		}
		else
		{
			weight_plu_code = "";
		}

		std::string weight_food_code;
		if (foodinfo["weight_food_code"].IsString())
		{
			weight_food_code = CLewaimaiString::SqlZhuanyi(foodinfo["weight_food_code"].GetString());
		}
		else
		{
			weight_food_code = "";
		}

		//插入一个商品数据
		std::string sql = "INSERT INTO pos_food (id,shop_id,name,price,tag,status,type_id,is_dabao,dabao_money,is_nature,nature,autostocknum,goods_img,unit,barcode,member_price_used,member_price, \
			buying_price,stock,stockvalid,stock_warning,is_shouyinji_show,is_waimai_show,is_tangshi_show,is_zhengcan_show,expiration_date,is_weight,member_price_json,weight_plu_code,weight_food_code,jiagong_type,cost_mode) VALUES ('" + id + "' ,'" + shop_id + "','" \
			+ name + "', '" + price + "', '" + tag + "', '" + status + "', '" + type_id + "', '" + is_dabao + "', '" + dabao_money + "', '" + is_nature + "', '" + nature + "', '" \
			+ autostocknum + "', '" + goods_img + "', '" + unit + "', '" + barcode + "', '" + member_price_used + "', '" + member_price + "', '" + buying_price + "', '" + stock + "', '" \
			+ stockvalid + "', '" + stock_warning + "', '" + is_shouyinji_show + "', '" + is_waimai_show + "', '" + is_tangshi_show + "', '" + is_zhengcan_show + "', '" + expiration_date + "','" + is_weight + "','" + member_price_json + "','" + weight_plu_code + "','" + weight_food_code + "','" + jiagong_type + "','" + cost_mode + "')";

		bool ret = this->ExeSQl(sql);
		if (!ret)
		{
			LOG_INFO("商品插入失败，sql:" << sql.c_str());
			return false;
		}
	}

	this->ExeSQl("commit;");

	return true;
}

bool CSqlite3::InitFoodtypeData(rapidjson::Value& foodtyperows)
{
	this->ExeSQl("begin;");

	//先清空之前的旧数据，重新完整写入新数据
	std::string sql = "delete from pos_foodtype;";
	this->ExeSQl(sql);

	for (rapidjson::SizeType i = 0; i < foodtyperows.Size(); ++i)
	{
		rapidjson::Value& foodtypeinfo = foodtyperows[i];

		std::string type_id = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodtypeinfo["type_id"]));
		std::string name = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodtypeinfo["name"]));
		std::string is_shouyinji_show = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodtypeinfo["is_shouyinji_show"]));
		std::string tag = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodtypeinfo["tag"]));

		//插入一个商品数据
		std::string sql = "INSERT INTO pos_foodtype (id,name,is_shouyinji_show,tag) VALUES ('" + type_id + "' ,'" +  name + "', '" + is_shouyinji_show + "', '" + tag + "')";

		bool ret = this->ExeSQl(sql);
		if (!ret)
		{
			LOG_INFO("商品分类插入失败：" << i);
			return false;
		}
	}

	this->ExeSQl("commit;");

	return true;
}

bool CSqlite3::InitFoodpackageData(rapidjson::Value& foodpackagerows)
{
	//先清空之前的旧数据，重新完整写入新数据
	this->ExeSQl("begin;");

	std::string sql = "delete from pos_foodpackage;";
	this->ExeSQl(sql);

	for (rapidjson::SizeType i = 0; i < foodpackagerows.Size(); ++i)
	{
		rapidjson::Value& foodinfo = foodpackagerows[i];

		std::string id = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["id"]));
		std::string shop_id = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["shop_id"]));
		std::string name = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["name"]));
		std::string price = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["price"]));
		std::string tag = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["tag"]));
		std::string status = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["status"]));
		std::string is_dabao = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["is_dabao"]));
		std::string dabao_money = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["dabao_money"]));

		std::string nature = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::JsonToString(foodinfo["nature"]));

		std::string goods_img = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["goods_img"]));
		std::string unit = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["unit"]));
		std::string supporttype = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["supporttype"]));
		std::string is_shouyinji_show = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["is_shouyinji_show"]));
		std::string barcode = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["barcode"]));

		//插入一个商品数据
		std::string sql = "INSERT INTO pos_foodpackage (id,shop_id,name,price,tag,status,is_dabao,dabao_money,nature,goods_img,unit,supporttype,is_shouyinji_show,barcode) VALUES ('" + id + "' ,'" + shop_id + "','" \
			+ name + "', '" + price + "', '" + tag + "', '" + status + "', '" + is_dabao + "', '" + dabao_money +  "', '" + nature + "', '" \
			+ goods_img + "', '" + unit + "', '" + supporttype + "', '" + is_shouyinji_show + "', '" + barcode + "')";

		bool ret = this->ExeSQl(sql);
		if (!ret)
		{
			LOG_INFO("商品套餐插入失败：" << i);
			return false;
		}
	}

	this->ExeSQl("commit;");

	return true;
}

bool CSqlite3::UpdateOneFood(rapidjson::Value& foodrows)
{
	this->ExeSQl("begin;");

	rapidjson::Value& foodinfo = foodrows;
	std::string id = CLewaimaiString::SqlZhuanyi(foodinfo["goods_id"].GetString());

	//不管当前有没有这个商品，执行一下删除
	std::string sql_delete = "delete from pos_food where id = '" + id + "';";
	this->ExeSQl(sql_delete);
	
	std::string shop_id = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["shop_id"]));
	std::string name = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["goods_name"]));
	std::string price = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["goods_price"]));
	std::string tag = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["goods_tag"]));
	std::string status = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["goods_status"]));
	std::string type_id = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["type_lv1_id"]));
	std::string is_dabao = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["is_dabao"]));
	std::string dabao_money = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["dabao_money"]));
	std::string is_nature = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["is_nature"]));

	std::string nature;
	if (is_nature == "1")
	{
		nature = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::JsonToString(foodinfo["nature"]));
	}
	else
	{
		nature = "";
	}

	std::string autostocknum = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["autostocknum"]));
	std::string goods_img = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["goods_img"]));
	std::string unit = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["unit"]));
	std::string barcode = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["barcode"]));
	std::string member_price_used = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["member_price_used"]));
	std::string member_price = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["member_price"]));
	std::string buying_price = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["buying_price"]));
	std::string stock = CLewaimaiString::SqlZhuanyi(std::to_string(foodinfo["stock"].GetDouble()));

	std::string stockvalid;
	std::string stock_status = CLewaimaiJson::ToString(foodinfo["stock_status"]);
	if (stock_status == "OPEN")
	{
		stockvalid = "1";
	}
	else
	{
		stockvalid = "0";
	}

	std::string stock_warning = CLewaimaiString::SqlZhuanyi(std::to_string(foodinfo["stock_warning"].GetDouble()));
	std::string is_shouyinji_show = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["is_shouyinji_show"]));
	std::string expiration_date = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["expiration_date"]));
	std::string is_weight = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["is_weight"]));
	std::string member_price_json = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["member_price_json"]));

	std::string is_waimai_show = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["is_waimai_show"]));
	std::string is_tangshi_show = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["is_tangshi_show"]));
	std::string is_zhengcan_show = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["is_zhengcan_show"]));
	std::string jiagong_type = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["jiagong_type"]));
	std::string cost_mode = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["cost_mode"]));

	std::string weight_plu_code;
	if (foodinfo["weight_plu_code"].IsString())
	{
		weight_plu_code = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["weight_plu_code"]));
	}
	else
	{
		weight_plu_code = "";
	}

	std::string weight_food_code;
	if (foodinfo["weight_food_code"].IsString())
	{
		weight_food_code = CLewaimaiString::SqlZhuanyi(CLewaimaiJson::ToString(foodinfo["weight_food_code"]));
	}
	else
	{
		weight_food_code = "";
	}

	//插入一个商品数据
	std::string sql = "INSERT INTO pos_food (id,shop_id,name,price,tag,status,type_id,is_dabao,dabao_money,is_nature,nature,autostocknum,goods_img,unit,barcode,member_price_used,member_price, \
			buying_price,stock,stockvalid,stock_warning,is_shouyinji_show,is_waimai_show,is_tangshi_show,is_zhengcan_show,expiration_date,is_weight,member_price_json,weight_plu_code,weight_food_code,jiagong_type,cost_mode) VALUES ('" + id + "' ,'" + shop_id + "','" \
		+ name + "', '" + price + "', '" + tag + "', '" + status + "', '" + type_id + "', '" + is_dabao + "', '" + dabao_money + "', '" + is_nature + "', '" + nature + "', '" \
		+ autostocknum + "', '" + goods_img + "', '" + unit + "', '" + barcode + "', '" + member_price_used + "', '" + member_price + "', '" + buying_price + "', '" + stock + "', '" \
		+ stockvalid + "', '" + stock_warning + "', '" + is_shouyinji_show + "', '" + is_waimai_show + "', '" + is_tangshi_show + "', '" + is_zhengcan_show + "', '" + expiration_date + "','" + is_weight + "','" + member_price_json + "','" + weight_plu_code + "','" + weight_food_code + "','" + jiagong_type + "','" + cost_mode + "')";

	bool ret = this->ExeSQl(sql);
	if (!ret)
	{
		return false;
	}

	this->ExeSQl("commit;");

	return true;
}

bool CSqlite3::DeleteOneFood(std::string food_id)
{
	food_id = CLewaimaiString::SqlZhuanyi(food_id);

	this->ExeSQl("begin;");

	//不管当前有没有这个商品，执行一下删除
	std::string sql_delete = "delete from pos_food where id = '" + food_id + "';";
	this->ExeSQl(sql_delete);

	this->ExeSQl("commit;");

	return true;
}

bool CSqlite3::AddOneFoodtype(std::string type_id, std::string name, std::string is_shouyinji_show, std::string tag)
{
	this->ExeSQl("begin;");

	type_id = CLewaimaiString::SqlZhuanyi(type_id);
	name = CLewaimaiString::SqlZhuanyi(name);
	is_shouyinji_show = CLewaimaiString::SqlZhuanyi(is_shouyinji_show);
	tag = CLewaimaiString::SqlZhuanyi(tag);

	//插入一个商品数据
	std::string sql = "INSERT INTO pos_foodtype (id,name,is_shouyinji_show,tag) VALUES ('" + type_id + "' ,'" + name + "', '" + is_shouyinji_show + "', '" + tag + "')";

	bool ret = this->ExeSQl(sql);
	if (!ret)
	{
		LOG_INFO("商品分类插入失败：");
		return false;
	}

	this->ExeSQl("commit;");

	return true;
}

bool CSqlite3::ExeSQl(std::string sql)
{
	char *zErrMsg = 0;
	int rc;

	rc = sqlite3_exec(m_db, sql.c_str(), 0, 0, &zErrMsg);
	if (rc != SQLITE_OK) 
	{
		LOG_ERROR("SQL error: "<<zErrMsg);
		sqlite3_free(zErrMsg);

		return false;
	}
	else 
	{
		return true;
	}

	return true;
}

//获取套餐商品的数量
int CSqlite3::GetFoodpackageNum()
{
	int count = 0;

	std::string sql = "SELECT count(*) as num FROM pos_foodpackage WHERE status = 'NORMAL';";
	sqlite3_stmt * stmt = NULL;

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

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

	return count;
}

std::vector<CFoodType> CSqlite3::GetFoodtypes(bool is_shouyinji_show)
{
	std::vector<CFoodType> data;

	std::string sql;
	
	if (is_shouyinji_show)
	{
		sql = "SELECT * FROM pos_foodtype WHERE is_shouyinji_show = '1';";
	}
	else
	{
		sql = "SELECT * FROM pos_foodtype;";
	}
	
	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 id = (char*)sqlite3_column_text(stmt, 0);
			std::string name = (char*)sqlite3_column_text(stmt, 1);
			std::string is_shouyinji_show = (char*)sqlite3_column_text(stmt, 2);
			std::string tag = (char*)sqlite3_column_text(stmt, 3);

			CFoodType newtype;
			newtype.id = id;
			newtype.name = name;
			newtype.is_shouyinji_show = is_shouyinji_show;
			newtype.tag = tag;

			data.push_back(newtype);
		}

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

	return data;
}

bool CSqlite3::GetFoodtypeById(std::string foodtype_id, CFoodType& newFoodType)
{
	foodtype_id = CLewaimaiString::SqlZhuanyi(foodtype_id);

	bool is_found = false;

	std::string sql = "SELECT * FROM pos_foodtype WHERE id = '" + foodtype_id + "'";

	sqlite3_stmt * stmt = NULL;

	if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
	{
		while (sqlite3_step(stmt) == SQLITE_ROW)
		{
			is_found = true;

			newFoodType.id = (char*)sqlite3_column_text(stmt, 0);
			newFoodType.name = (char*)sqlite3_column_text(stmt, 1);
			newFoodType.is_shouyinji_show = (char*)sqlite3_column_text(stmt, 2);
			newFoodType.tag = (char*)sqlite3_column_text(stmt, 3);
		}

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

	return is_found;
}

/**
 * 如果type_id为0，表示读取所有商品，否则只读取当前type_id的商品
 */
std::vector<CFood> CSqlite3::GetFoodByTypeid(std::string type_id, bool is_shouyinji_show)
{
	type_id = CLewaimaiString::SqlZhuanyi(type_id);

	std::vector<CFood> data;

	std::string sql;

	if (is_shouyinji_show)
	{
		sql = "SELECT * FROM pos_food WHERE status='NORMAL' and is_shouyinji_show = '1'";
	}
	else
	{
		sql = "SELECT * FROM pos_food WHERE status='NORMAL'";
	}

	if (type_id != "0")
	{
		sql += " AND type_id = '" + type_id + "'";
	}

	sql += " ORDER BY tag ASC";

	sqlite3_stmt * stmt = NULL;

	if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
	{
		while (sqlite3_step(stmt) == SQLITE_ROW)
		{
			CFood newFood;

			newFood.id = (char*)sqlite3_column_text(stmt, 0);
			newFood.shop_id = (char*)sqlite3_column_text(stmt, 1);
			newFood.name = (char*)sqlite3_column_text(stmt, 2);
			newFood.price = (char*)sqlite3_column_text(stmt, 3);
			newFood.tag = (char*)sqlite3_column_text(stmt, 4);
			newFood.status = (char*)sqlite3_column_text(stmt, 5);
			newFood.type_id = (char*)sqlite3_column_text(stmt, 6);
			newFood.is_dabao = (char*)sqlite3_column_text(stmt, 7);
			newFood.dabao_money = (char*)sqlite3_column_text(stmt, 8);
			newFood.is_nature = (char*)sqlite3_column_text(stmt, 9);
			newFood.nature = (char*)sqlite3_column_text(stmt, 10);
			newFood.autostocknum = (char*)sqlite3_column_text(stmt, 11);
			newFood.goods_img = (char*)sqlite3_column_text(stmt, 12);
			newFood.unit = (char*)sqlite3_column_text(stmt, 13);
			newFood.barcode = (char*)sqlite3_column_text(stmt, 14);
			newFood.member_price_used = (char*)sqlite3_column_text(stmt, 15);
			newFood.member_price = (char*)sqlite3_column_text(stmt, 16);
			newFood.buying_price = (char*)sqlite3_column_text(stmt, 17);
			newFood.stock = (char*)sqlite3_column_text(stmt, 18);
			newFood.stockvalid = (char*)sqlite3_column_text(stmt, 19);
			newFood.stock_warning = (char*)sqlite3_column_text(stmt, 20);
			newFood.is_shouyinji_show = (char*)sqlite3_column_text(stmt, 21);
			newFood.is_waimai_show = (char*)sqlite3_column_text(stmt, 22);
			newFood.is_tangshi_show = (char*)sqlite3_column_text(stmt, 23);
			newFood.is_zhengcan_show = (char*)sqlite3_column_text(stmt, 24);
			newFood.expiration_date = (char*)sqlite3_column_text(stmt, 25);
			newFood.is_weight = (char*)sqlite3_column_text(stmt, 26);
			newFood.weight_plu_code = (char*)sqlite3_column_text(stmt, 27);
			newFood.weight_food_code = (char*)sqlite3_column_text(stmt, 28);
			newFood.jiagong_type = (char*)sqlite3_column_text(stmt, 29);
			newFood.cost_mode = (char*)sqlite3_column_text(stmt, 30);
			newFood.member_price_json = (char*)sqlite3_column_text(stmt, 31);

			data.push_back(newFood);
		}

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

	return data;
}

std::vector<CFood> CSqlite3::GetFoodForTiaomacheng()
{
	std::vector<CFood> data;

	std::string sql;

	sql = "SELECT * FROM pos_food WHERE is_weight = '1' AND weight_plu_code != '' AND weight_food_code != ''";

	sqlite3_stmt * stmt = NULL;

	if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
	{
		while (sqlite3_step(stmt) == SQLITE_ROW)
		{
			CFood newFood;

			newFood.id = (char*)sqlite3_column_text(stmt, 0);
			newFood.shop_id = (char*)sqlite3_column_text(stmt, 1);
			newFood.name = (char*)sqlite3_column_text(stmt, 2);
			newFood.price = (char*)sqlite3_column_text(stmt, 3);
			newFood.tag = (char*)sqlite3_column_text(stmt, 4);
			newFood.status = (char*)sqlite3_column_text(stmt, 5);
			newFood.type_id = (char*)sqlite3_column_text(stmt, 6);
			newFood.is_dabao = (char*)sqlite3_column_text(stmt, 7);
			newFood.dabao_money = (char*)sqlite3_column_text(stmt, 8);
			newFood.is_nature = (char*)sqlite3_column_text(stmt, 9);
			newFood.nature = (char*)sqlite3_column_text(stmt, 10);
			newFood.autostocknum = (char*)sqlite3_column_text(stmt, 11);
			newFood.goods_img = (char*)sqlite3_column_text(stmt, 12);
			newFood.unit = (char*)sqlite3_column_text(stmt, 13);
			newFood.barcode = (char*)sqlite3_column_text(stmt, 14);
			newFood.member_price_used = (char*)sqlite3_column_text(stmt, 15);
			newFood.member_price = (char*)sqlite3_column_text(stmt, 16);
			newFood.buying_price = (char*)sqlite3_column_text(stmt, 17);
			newFood.stock = (char*)sqlite3_column_text(stmt, 18);
			newFood.stockvalid = (char*)sqlite3_column_text(stmt, 19);
			newFood.stock_warning = (char*)sqlite3_column_text(stmt, 20);
			newFood.is_shouyinji_show = (char*)sqlite3_column_text(stmt, 21);
			newFood.is_waimai_show = (char*)sqlite3_column_text(stmt, 22);
			newFood.is_tangshi_show = (char*)sqlite3_column_text(stmt, 23);
			newFood.is_zhengcan_show = (char*)sqlite3_column_text(stmt, 24);
			newFood.expiration_date = (char*)sqlite3_column_text(stmt, 25);
			newFood.is_weight = (char*)sqlite3_column_text(stmt, 26);
			newFood.weight_plu_code = (char*)sqlite3_column_text(stmt, 27);
			newFood.weight_food_code = (char*)sqlite3_column_text(stmt, 28);
			newFood.jiagong_type = (char*)sqlite3_column_text(stmt, 29);
			newFood.cost_mode = (char*)sqlite3_column_text(stmt, 30);
			newFood.member_price_json = (char*)sqlite3_column_text(stmt, 31);

			data.push_back(newFood);
		}

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

	return data;
}

std::vector<CFood> CSqlite3::GetFoodByFoodname(std::string foodname, bool is_shouyinji_show)
{
	std::vector<CFood> data;

	std::string sql;

	foodname = CLewaimaiString::SqlZhuanyi(foodname);

	if (is_shouyinji_show)
	{
		sql = "SELECT * FROM pos_food WHERE name LIKE '%" + foodname + "%' AND status='NORMAL' AND is_shouyinji_show = '1'";
	}
	else
	{
		sql = "SELECT * FROM pos_food WHERE name LIKE '%" + foodname + "%' AND status='NORMAL'";
	}

	sql += " ORDER BY tag";

	sqlite3_stmt * stmt = NULL;

	if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
	{
		while (sqlite3_step(stmt) == SQLITE_ROW)
		{
			CFood newFood;

			newFood.id = (char*)sqlite3_column_text(stmt, 0);
			newFood.shop_id = (char*)sqlite3_column_text(stmt, 1);
			newFood.name = (char*)sqlite3_column_text(stmt, 2);
			newFood.price = (char*)sqlite3_column_text(stmt, 3);
			newFood.tag = (char*)sqlite3_column_text(stmt, 4);
			newFood.status = (char*)sqlite3_column_text(stmt, 5);
			newFood.type_id = (char*)sqlite3_column_text(stmt, 6);
			newFood.is_dabao = (char*)sqlite3_column_text(stmt, 7);
			newFood.dabao_money = (char*)sqlite3_column_text(stmt, 8);
			newFood.is_nature = (char*)sqlite3_column_text(stmt, 9);
			newFood.nature = (char*)sqlite3_column_text(stmt, 10);
			newFood.autostocknum = (char*)sqlite3_column_text(stmt, 11);
			newFood.goods_img = (char*)sqlite3_column_text(stmt, 12);
			newFood.unit = (char*)sqlite3_column_text(stmt, 13);
			newFood.barcode = (char*)sqlite3_column_text(stmt, 14);
			newFood.member_price_used = (char*)sqlite3_column_text(stmt, 15);
			newFood.member_price = (char*)sqlite3_column_text(stmt, 16);
			newFood.buying_price = (char*)sqlite3_column_text(stmt, 17);
			newFood.stock = (char*)sqlite3_column_text(stmt, 18);
			newFood.stockvalid = (char*)sqlite3_column_text(stmt, 19);
			newFood.stock_warning = (char*)sqlite3_column_text(stmt, 20);
			newFood.is_shouyinji_show = (char*)sqlite3_column_text(stmt, 21);
			newFood.is_waimai_show = (char*)sqlite3_column_text(stmt, 22);
			newFood.is_tangshi_show = (char*)sqlite3_column_text(stmt, 23);
			newFood.is_zhengcan_show = (char*)sqlite3_column_text(stmt, 24);
			newFood.expiration_date = (char*)sqlite3_column_text(stmt, 25);
			newFood.is_weight = (char*)sqlite3_column_text(stmt, 26);
			newFood.weight_plu_code = (char*)sqlite3_column_text(stmt, 27);
			newFood.weight_food_code = (char*)sqlite3_column_text(stmt, 28);
			newFood.jiagong_type = (char*)sqlite3_column_text(stmt, 29);
			newFood.cost_mode = (char*)sqlite3_column_text(stmt, 30);
			newFood.member_price_json = (char*)sqlite3_column_text(stmt, 31);

			data.push_back(newFood);
		}

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

	return data;
}

std::vector<CFoodpackage> CSqlite3::GetFoodpackages(bool is_shouyinji_show)
{
	std::vector<CFoodpackage> data;

	std::string sql;

	if (is_shouyinji_show)
	{
		sql = "SELECT * FROM pos_foodpackage WHERE status='NORMAL' AND is_shouyinji_show = '1' ORDER BY tag";
	}
	else
	{
		sql = "SELECT * FROM pos_foodpackage WHERE status='NORMAL' ORDER BY tag";
	}

	sqlite3_stmt * stmt = NULL;

	if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
	{
		while (sqlite3_step(stmt) == SQLITE_ROW)
		{
			CFoodpackage newFood;

			newFood.id = (char*)sqlite3_column_text(stmt, 0);
			newFood.shop_id = (char*)sqlite3_column_text(stmt, 1);
			newFood.name = (char*)sqlite3_column_text(stmt, 2);
			newFood.price = (char*)sqlite3_column_text(stmt, 3);
			newFood.tag = (char*)sqlite3_column_text(stmt, 4);
			newFood.status = (char*)sqlite3_column_text(stmt, 5);
			newFood.is_dabao = (char*)sqlite3_column_text(stmt, 6);
			newFood.dabao_money = (char*)sqlite3_column_text(stmt, 7);
			newFood.nature = (char*)sqlite3_column_text(stmt, 8);
			newFood.goods_img = (char*)sqlite3_column_text(stmt, 9);
			newFood.unit = (char*)sqlite3_column_text(stmt, 10);
			newFood.supporttype = (char*)sqlite3_column_text(stmt, 11);
			newFood.is_shouyinji_show = (char*)sqlite3_column_text(stmt, 12);
			newFood.barcode = (char*)sqlite3_column_text(stmt, 13);

			data.push_back(newFood);
		}

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

	return data;
}

bool CSqlite3::GetFoodById(std::string food_id, CFood& newFood)
{
	food_id = CLewaimaiString::SqlZhuanyi(food_id);

	bool is_found = false;

	std::string sql = "SELECT * FROM pos_food WHERE id = '" + food_id + "'";

	sqlite3_stmt * stmt = NULL;

	if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
	{
		while (sqlite3_step(stmt) == SQLITE_ROW)
		{
			is_found = true;

			newFood.id = (char*)sqlite3_column_text(stmt, 0);
			newFood.shop_id = (char*)sqlite3_column_text(stmt, 1);
			newFood.name = (char*)sqlite3_column_text(stmt, 2);
			newFood.price = (char*)sqlite3_column_text(stmt, 3);
			newFood.tag = (char*)sqlite3_column_text(stmt, 4);
			newFood.status = (char*)sqlite3_column_text(stmt, 5);
			newFood.type_id = (char*)sqlite3_column_text(stmt, 6);
			newFood.is_dabao = (char*)sqlite3_column_text(stmt, 7);
			newFood.dabao_money = (char*)sqlite3_column_text(stmt, 8);
			newFood.is_nature = (char*)sqlite3_column_text(stmt, 9);
			newFood.nature = (char*)sqlite3_column_text(stmt, 10);
			newFood.autostocknum = (char*)sqlite3_column_text(stmt, 11);
			newFood.goods_img = (char*)sqlite3_column_text(stmt, 12);
			newFood.unit = (char*)sqlite3_column_text(stmt, 13);
			newFood.barcode = (char*)sqlite3_column_text(stmt, 14);
			newFood.member_price_used = (char*)sqlite3_column_text(stmt, 15);
			newFood.member_price = (char*)sqlite3_column_text(stmt, 16);
			newFood.buying_price = (char*)sqlite3_column_text(stmt, 17);
			newFood.stock = (char*)sqlite3_column_text(stmt, 18);
			newFood.stockvalid = (char*)sqlite3_column_text(stmt, 19);
			newFood.stock_warning = (char*)sqlite3_column_text(stmt, 20);
			newFood.is_shouyinji_show = (char*)sqlite3_column_text(stmt, 21);
			newFood.is_waimai_show = (char*)sqlite3_column_text(stmt, 22);
			newFood.is_tangshi_show = (char*)sqlite3_column_text(stmt, 23);
			newFood.is_zhengcan_show = (char*)sqlite3_column_text(stmt, 24);
			newFood.expiration_date = (char*)sqlite3_column_text(stmt, 25);
			newFood.is_weight = (char*)sqlite3_column_text(stmt, 26);
			newFood.weight_plu_code = (char*)sqlite3_column_text(stmt, 27);
			newFood.weight_food_code = (char*)sqlite3_column_text(stmt, 28);
			newFood.jiagong_type = (char*)sqlite3_column_text(stmt, 29);
			newFood.cost_mode = (char*)sqlite3_column_text(stmt, 30);
			newFood.member_price_json = (char*)sqlite3_column_text(stmt, 31);
		}

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

	return is_found;
}

bool CSqlite3::GetFoodByBarcode(std::string barcode, CFood& newFood)
{
	bool is_found = false;

	barcode = CLewaimaiString::SqlZhuanyi(barcode);

	std::string sql = "SELECT * FROM pos_food WHERE barcode = '" + barcode + "'";

	sqlite3_stmt * stmt = NULL;

	if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
	{
		while (sqlite3_step(stmt) == SQLITE_ROW)
		{
			is_found = true;

			newFood.id = (char*)sqlite3_column_text(stmt, 0);
			newFood.shop_id = (char*)sqlite3_column_text(stmt, 1);
			newFood.name = (char*)sqlite3_column_text(stmt, 2);
			newFood.price = (char*)sqlite3_column_text(stmt, 3);
			newFood.tag = (char*)sqlite3_column_text(stmt, 4);
			newFood.status = (char*)sqlite3_column_text(stmt, 5);
			newFood.type_id = (char*)sqlite3_column_text(stmt, 6);
			newFood.is_dabao = (char*)sqlite3_column_text(stmt, 7);
			newFood.dabao_money = (char*)sqlite3_column_text(stmt, 8);
			newFood.is_nature = (char*)sqlite3_column_text(stmt, 9);
			newFood.nature = (char*)sqlite3_column_text(stmt, 10);
			newFood.autostocknum = (char*)sqlite3_column_text(stmt, 11);
			newFood.goods_img = (char*)sqlite3_column_text(stmt, 12);
			newFood.unit = (char*)sqlite3_column_text(stmt, 13);
			newFood.barcode = (char*)sqlite3_column_text(stmt, 14);
			newFood.member_price_used = (char*)sqlite3_column_text(stmt, 15);
			newFood.member_price = (char*)sqlite3_column_text(stmt, 16);
			newFood.buying_price = (char*)sqlite3_column_text(stmt, 17);
			newFood.stock = (char*)sqlite3_column_text(stmt, 18);
			newFood.stockvalid = (char*)sqlite3_column_text(stmt, 19);
			newFood.stock_warning = (char*)sqlite3_column_text(stmt, 20);
			newFood.is_shouyinji_show = (char*)sqlite3_column_text(stmt, 21);
			newFood.is_waimai_show = (char*)sqlite3_column_text(stmt, 22);
			newFood.is_tangshi_show = (char*)sqlite3_column_text(stmt, 23);
			newFood.is_zhengcan_show = (char*)sqlite3_column_text(stmt, 24);
			newFood.expiration_date = (char*)sqlite3_column_text(stmt, 25);
			newFood.is_weight = (char*)sqlite3_column_text(stmt, 26);
			newFood.weight_plu_code = (char*)sqlite3_column_text(stmt, 27);
			newFood.weight_food_code = (char*)sqlite3_column_text(stmt, 28);
			newFood.jiagong_type = (char*)sqlite3_column_text(stmt, 29);
			newFood.cost_mode = (char*)sqlite3_column_text(stmt, 30);
			newFood.member_price_json = (char*)sqlite3_column_text(stmt, 31);
		}

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

	return is_found;
}

bool CSqlite3::GetFoodByPluBianma(std::string barcode, CFood& newFood)
{
	bool is_found = false;

	barcode = CLewaimaiString::SqlZhuanyi(barcode);

	std::string sql = "SELECT * FROM pos_food WHERE is_weight = '1' AND weight_food_code = '" + barcode + "'";

	sqlite3_stmt * stmt = NULL;

	if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
	{
		while (sqlite3_step(stmt) == SQLITE_ROW)
		{
			is_found = true;

			newFood.id = (char*)sqlite3_column_text(stmt, 0);
			newFood.shop_id = (char*)sqlite3_column_text(stmt, 1);
			newFood.name = (char*)sqlite3_column_text(stmt, 2);
			newFood.price = (char*)sqlite3_column_text(stmt, 3);
			newFood.tag = (char*)sqlite3_column_text(stmt, 4);
			newFood.status = (char*)sqlite3_column_text(stmt, 5);
			newFood.type_id = (char*)sqlite3_column_text(stmt, 6);
			newFood.is_dabao = (char*)sqlite3_column_text(stmt, 7);
			newFood.dabao_money = (char*)sqlite3_column_text(stmt, 8);
			newFood.is_nature = (char*)sqlite3_column_text(stmt, 9);
			newFood.nature = (char*)sqlite3_column_text(stmt, 10);
			newFood.autostocknum = (char*)sqlite3_column_text(stmt, 11);
			newFood.goods_img = (char*)sqlite3_column_text(stmt, 12);
			newFood.unit = (char*)sqlite3_column_text(stmt, 13);
			newFood.barcode = (char*)sqlite3_column_text(stmt, 14);
			newFood.member_price_used = (char*)sqlite3_column_text(stmt, 15);
			newFood.member_price = (char*)sqlite3_column_text(stmt, 16);
			newFood.buying_price = (char*)sqlite3_column_text(stmt, 17);
			newFood.stock = (char*)sqlite3_column_text(stmt, 18);
			newFood.stockvalid = (char*)sqlite3_column_text(stmt, 19);
			newFood.stock_warning = (char*)sqlite3_column_text(stmt, 20);
			newFood.is_shouyinji_show = (char*)sqlite3_column_text(stmt, 21);
			newFood.is_waimai_show = (char*)sqlite3_column_text(stmt, 22);
			newFood.is_tangshi_show = (char*)sqlite3_column_text(stmt, 23);
			newFood.is_zhengcan_show = (char*)sqlite3_column_text(stmt, 24);
			newFood.expiration_date = (char*)sqlite3_column_text(stmt, 25);
			newFood.is_weight = (char*)sqlite3_column_text(stmt, 26);
			newFood.weight_plu_code = (char*)sqlite3_column_text(stmt, 27);
			newFood.weight_food_code = (char*)sqlite3_column_text(stmt, 28);
			newFood.jiagong_type = (char*)sqlite3_column_text(stmt, 29);
			newFood.cost_mode = (char*)sqlite3_column_text(stmt, 30);
			newFood.member_price_json = (char*)sqlite3_column_text(stmt, 31);
		}

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

	return is_found;
}

bool CSqlite3::GetFoodpackageById(std::string foodpackage_id, CFoodpackage& newFoodpackage)
{
	bool is_found = false;

	std::string sql = "SELECT * FROM pos_foodpackage WHERE id = '" + foodpackage_id + "'";

	sqlite3_stmt * stmt = NULL;

	if (sqlite3_prepare_v2(m_db, sql.c_str(), -1, &stmt, NULL) == SQLITE_OK)
	{
		while (sqlite3_step(stmt) == SQLITE_ROW)
		{
			is_found = true;

			newFoodpackage.id = (char*)sqlite3_column_text(stmt, 0);
			newFoodpackage.shop_id = (char*)sqlite3_column_text(stmt, 1);
			newFoodpackage.name = (char*)sqlite3_column_text(stmt, 2);
			newFoodpackage.price = (char*)sqlite3_column_text(stmt, 3);
			newFoodpackage.tag = (char*)sqlite3_column_text(stmt, 4);
			newFoodpackage.status = (char*)sqlite3_column_text(stmt, 5);
			newFoodpackage.is_dabao = (char*)sqlite3_column_text(stmt, 6);
			newFoodpackage.dabao_money = (char*)sqlite3_column_text(stmt, 7);
			newFoodpackage.nature = (char*)sqlite3_column_text(stmt, 8);
			newFoodpackage.goods_img = (char*)sqlite3_column_text(stmt, 9);
			newFoodpackage.unit = (char*)sqlite3_column_text(stmt, 10);
			newFoodpackage.supporttype = (char*)sqlite3_column_text(stmt, 11);
			newFoodpackage.is_shouyinji_show = (char*)sqlite3_column_text(stmt, 12);
			newFoodpackage.barcode = (char*)sqlite3_column_text(stmt, 13);
		}

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

	return is_found;
}

//更新商品ID的库存
void CSqlite3::UpdateFoodStock(std::string food_id, std::string stock)
{
	food_id = CLewaimaiString::SqlZhuanyi(food_id);

	int result = sqlite3_exec(m_db, "BEGIN;", 0, 0, 0);

	std::string sql = "UPDATE pos_food SET stock = '" + stock + "' WHERE id = " + food_id;
	result = sqlite3_exec(m_db, sql.c_str(), 0, 0, 0);

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