parkspace_module/parkspace_allocation/parkspace_db_manager.cpp

/*
 * @Description: 车位数据库管理
 * @Author: yct
 * @Date: 2020-07-19 09:57:45
 * @LastEditTime: 2020-09-11 11:41:09
 * @LastEditors: yct
 */

#include "parkspace_db_manager.h"



Parkspace_db_manager::Parkspace_db_manager()
{
	mb_initialized=false;
}
Parkspace_db_manager::~Parkspace_db_manager()
{
	Parkspace_db_manager_uninit();
}

// 车位数据库管理初始化
Error_manager Parkspace_db_manager::Parkspace_db_manager_init(parkspace_proto::database_config config)
{
    Error_manager ec = SUCCESS;


	ec =   m_db_controller.database_controller_init(config.db_ip(), config.db_port(), config.db_username(), config.db_passwd(), config.db_name(), config.db_conn_pool_size());
	mb_initialized = true;
	return ec;
}
// 车位数据库管理反初始化
Error_manager Parkspace_db_manager::Parkspace_db_manager_uninit()
{
	mb_initialized=false;
	return POINTER_IS_NULL;
}

// 连接状态
bool Parkspace_db_manager::is_connected()
{

	mb_connected =   m_db_controller.is_connected();
	return mb_connected;
}

// 初始化状态
bool Parkspace_db_manager::is_initialized()
{
    return mb_initialized;
}

// 从数据库获得所有车位信息，用于车位模块初始化
Error_manager Parkspace_db_manager::get_all_parkspace_info(message::Parkspace_allocation_status_msg &all_parkspace_info)
{
    // 基本条件判断
    if(!mb_initialized)
    {
        return DB_UNINITIALIZED;
    }
    
	//执行sql操作
	std::string get_all_space_sql = "select * from parkingspace";
	boost::shared_ptr<sql::ResultSet>  tp_result = nullptr;
	Error_manager ec =   m_db_controller.sql_query(get_all_space_sql, tp_result);
	if(ec == SUCCESS)
	{
		if(tp_result == nullptr)
		{
			return DB_RESULT_SET_EMPTY;
		}
		all_parkspace_info.clear_parkspace_info();
		while (tp_result->next())
		{
			message::Parkspace_info *t_parkspace = all_parkspace_info.add_parkspace_info();
			char buf[1024];
			memset(buf, 0, 1024);
			try
			{
				t_parkspace->set_parkspace_id(tp_result->getInt("parkingSpaceID"));
				t_parkspace->set_index(tp_result->getInt("parkingSpaceX"));
				if (tp_result->getInt("parkingSpaceY") > 0)
				{
					t_parkspace->set_direction(message::Direction::eForward);
				}
				else
				{
					t_parkspace->set_direction(message::Direction::eBackward);
				}
				t_parkspace->set_floor(tp_result->getInt("parkingSpaceZ"));
				switch (tp_result->getInt("parkingSpaceState"))
				{
				case 0:
					t_parkspace->set_parkspace_status(message::Parkspace_status::eParkspace_empty);
					break;
				case 1:
					t_parkspace->set_parkspace_status(message::Parkspace_status::eParkspace_occupied);
					break;
				case 2:
					t_parkspace->set_parkspace_status(message::Parkspace_status::eParkspace_reserved);
					break;
				case 3:
					t_parkspace->set_parkspace_status(message::Parkspace_status::eParkspace_locked);
					break;
				case 4:
					t_parkspace->set_parkspace_status(message::Parkspace_status::eParkspace_error);
					break;
				default:
					t_parkspace->set_parkspace_status(message::Parkspace_status::eParkspace_error);
					break;
				}
				t_parkspace->set_block_id(tp_result->getInt("parkingSpaceBlockID"));
				t_parkspace->set_width(tp_result->getDouble("parkSpaceWidth"));
				t_parkspace->set_height(tp_result->getDouble("parkSpaceHeight"));
				t_parkspace->mutable_car_info()->set_license(tp_result->getString("parkSpaceCarLicense"));
				t_parkspace->mutable_car_info()->set_car_length(tp_result->getDouble("parkSpaceCarLength"));
				t_parkspace->mutable_car_info()->set_car_width(tp_result->getDouble("parkSpaceCarWidth"));
				t_parkspace->mutable_car_info()->set_car_height(tp_result->getDouble("parkSpaceCarHeight"));
				t_parkspace->set_entry_time(tp_result->getString("entryTime"));
				t_parkspace->set_leave_time(tp_result->getString("leaveTime"));
			}
			catch (sql::SQLException &e)
			{
				// Use what() (derived from std::runtime_error) to fetch the error message
				sprintf(buf, "# ERR: %s\n (MySQL error code: %d, SQLState: %s", e.what(), e.getErrorCode(), e.getSQLState().c_str());
				return Error_manager(DB_RESULT_SET_PARSE_ERROR, NEGLIGIBLE_ERROR, buf);
			}
			catch (std::runtime_error &e)
			{
				sprintf(buf, "# ERR: %s\n ERR: runtime_error in  %s ", e.what(), __FILE__);
				return Error_manager(DB_RESULT_SET_PARSE_ERROR, NEGLIGIBLE_ERROR, buf);
			}
		}
		return SUCCESS;
	}
	else
	{
		return ec;
	}
    
}

// 更新数据库中所有车位信息
Error_manager Parkspace_db_manager::update_all_parkspace_info(message::Parkspace_allocation_status_msg all_parkspace_info)
{
    return SUCCESS;
}

// 更新数据库中车位状态
Error_manager Parkspace_db_manager::update_parkspace_status(message::Parkspace_info parkspace_info)
{
// 基本条件判断
    if(!mb_initialized)
    {
        return DB_UNINITIALIZED;
    }

	//执行sql操作
	char update_space_sql[1024];
	memset(update_space_sql, 0, 1024);
	int status = -1;
	switch (parkspace_info.parkspace_status())
	{
	case message::Parkspace_status::eParkspace_empty:
		status = 0;
		break;
	case message::Parkspace_status::eParkspace_occupied:
		status = 1;
		break;
	case message::Parkspace_status::eParkspace_reserved:
		status = 2;
		break;
	case message::Parkspace_status::eParkspace_locked:
		status = 3;
		break;
	case message::Parkspace_status::eParkspace_error:
		status = 4;
		break;
	default:
		break;
	}
	if(parkspace_info.has_car_info())
	{
		sprintf(update_space_sql, "update parkingspace set parkingSpaceState = %d,parkSpaceCarLicense = '%s',parkSpaceCarLength = %.3f,parkSpaceCarWidth = %.3f,parkSpaceCarHeight = %.3f,entryTime = '%s',leaveTime = '%s' where parkingSpaceID = %d",
				status, 
				parkspace_info.car_info().license().c_str(),
				parkspace_info.car_info().has_car_length()?parkspace_info.car_info().car_length():0.0f,
				parkspace_info.car_info().car_width(),
				parkspace_info.car_info().car_height(),
				parkspace_info.entry_time().c_str(),
				parkspace_info.leave_time().c_str(),
				parkspace_info.parkspace_id());
	}else{
		sprintf(update_space_sql, "update parkingspace set parkingSpaceState = %d where parkingSpaceID = %d", status, parkspace_info.parkspace_id());
	}
	if(status < 0)
		return ERROR;
	boost::shared_ptr<sql::ResultSet>  tp_result = nullptr;
	Error_manager ec =   m_db_controller.sql_update(update_space_sql);
	return ec;

}

// 更新数据库车辆状态，车位信息中包含车辆信息用于匹配更新
Error_manager Parkspace_db_manager::update_vehicle_status(message::Parkspace_info parkspace_info, message::Vehicle_status &vehicle_status)
{
    // 基本条件判断
    if(!mb_initialized)
    {
        return DB_UNINITIALIZED;
    }

	//执行sql操作
	if(!parkspace_info.has_car_info())
	{
		return PARAMETER_ERROR;
	}
	std::string find_vehicle_sql = std::string("select * from vehicle where numberPlate = '").append(parkspace_info.car_info().license()).append("'");
	boost::shared_ptr<sql::ResultSet> query_vehicle_result = nullptr;
	  m_db_controller.sql_query(find_vehicle_sql, query_vehicle_result);
	//判断车辆是否存在
	if(query_vehicle_result == nullptr || !query_vehicle_result->next())
	{
		return insert_vehicle(parkspace_info, vehicle_status);
	}else{
		char update_vehicle_sql[1024];
		memset(update_vehicle_sql, 0, 1024);
		int vehicle_status_code = -1;
		switch (vehicle_status)
		{
		case message::Vehicle_status::eVehicle_idle:
			vehicle_status_code = 0;
			break;
		case message::Vehicle_status::eVehicle_in_garage:
			vehicle_status_code = 1;
			break;
		case message::Vehicle_status::eVehicle_parking:
			vehicle_status_code = 2;
			break;
		case message::Vehicle_status::eVehicle_fetching:
			vehicle_status_code = 3;
			break;
		case message::Vehicle_status::eVehicle_reserved:
			vehicle_status_code = 4;
			break;
		default:
			break;
		}
		//车位为空，仅更新车辆状态与长宽高
		if(parkspace_info.parkspace_id() <= 0)
		{
			sprintf(update_vehicle_sql, "update vehicle set vehicleParkState = %d,carLength=%.3f,carWidth=%.3f,carHeight=%.3f where numberPlate = '%s'",
					vehicle_status_code,
					parkspace_info.car_info().car_length(),
					parkspace_info.car_info().car_width(),
					parkspace_info.car_info().car_height(),
					parkspace_info.car_info().license().c_str());
		}
		//车位不为空，表示车辆所在车位，同时更新
		else
		{
			 sprintf(update_vehicle_sql, "update vehicle set vehicleParkState = %d,carLength=%.3f,carWidth=%.3f,carHeight=%.3f,parkingSpaceID=%d where numberPlate = '%s'",
					vehicle_status_code,
					parkspace_info.car_info().car_length(),
					parkspace_info.car_info().car_width(),
					parkspace_info.car_info().car_height(),
					parkspace_info.parkspace_id(),
					parkspace_info.car_info().license().c_str());
		}
		if (vehicle_status_code < 0)
			return ERROR;
		Error_manager ec =   m_db_controller.sql_update(update_vehicle_sql);
		return ec;
	}

}

// 插入车辆
Error_manager Parkspace_db_manager::insert_vehicle(message::Parkspace_info parkspace_info, message::Vehicle_status &vehicle_status)
{		

	// 基本条件判断
    if(!mb_initialized)
    {
        return DB_UNINITIALIZED;
    }
	//执行sql操作
	if(!parkspace_info.has_car_info())
	{
		return PARAMETER_ERROR;
	}
	char insert_vehicle_sql[1024];
	memset(insert_vehicle_sql, 0, 1024);
	int vehicle_status_code = -1;
	switch (vehicle_status)
	{
	case message::Vehicle_status::eVehicle_idle:
		vehicle_status_code = 0;
		break;
	case message::Vehicle_status::eVehicle_in_garage:
		vehicle_status_code = 1;
		break;
	case message::Vehicle_status::eVehicle_parking:
		vehicle_status_code = 2;
		break;
	case message::Vehicle_status::eVehicle_fetching:
		vehicle_status_code = 3;
		break;
	case message::Vehicle_status::eVehicle_reserved:
		vehicle_status_code = 4;
		break;
	default:
		break;
	}
	//车位为空，仅更新车辆状态与长宽高
	if(parkspace_info.parkspace_id() <= 0)
	{
		sprintf(insert_vehicle_sql, "INSERT INTO vehicle (numberPlate,vehicleParkState,carLength,carWidth,carHeight) values ('%s',%d,%.3f,%.3f,%.3f)", 
				parkspace_info.car_info().license().c_str(),
				vehicle_status_code,
				parkspace_info.car_info().car_length(),
				parkspace_info.car_info().car_width(),
				parkspace_info.car_info().car_height());
	}
	//车位不为空，表示车辆在车位，同时更新
	else
	{
		
		sprintf(insert_vehicle_sql, "INSERT INTO vehicle (numberPlate,vehicleParkState,carLength,carWidth,carHeight,parkingSpaceID) values ('%s',%d,%.3f,%.3f,%.3f,%d)",
			parkspace_info.car_info().license().c_str(),
			vehicle_status_code,
			parkspace_info.car_info().car_length(),
			parkspace_info.car_info().car_width(),
			parkspace_info.car_info().car_height(),
			parkspace_info.parkspace_id());
	}
	if (vehicle_status_code < 0)
		return ERROR;
	Error_manager ec =   m_db_controller.sql_insert(insert_vehicle_sql);

	return ec;
    
}

// 插入停车记录
Error_manager Parkspace_db_manager::insert_parking_record(message::Parkspace_info parkspace_info)
{
    // 基本条件判断
    if(!mb_initialized)
    {
        return DB_UNINITIALIZED;
    }

	//参数中必须包含车辆信息与车位编号
	if(!parkspace_info.has_car_info() || parkspace_info.parkspace_id() <= 0)
	{
		return PARAMETER_ERROR;
	}
	char insert_parking_record_sql[1024];
	memset(insert_parking_record_sql, 0, 1024);
	//将车辆号牌，车位ID，记录状态，停车与取车时间写入
		sprintf(insert_parking_record_sql, "INSERT INTO parkingrecords (numberPlate,parkingSpaceID,realParkTime) values ('%s',%d,'%s')", 
				parkspace_info.car_info().license().c_str(),
				parkspace_info.parkspace_id(),
				parkspace_info.entry_time().c_str());
	Error_manager ec =   m_db_controller.sql_insert(insert_parking_record_sql);
	return ec;
    
}

// 更新停车记录，待计费系统加入后完善！！！
Error_manager Parkspace_db_manager::update_parking_record(message::Parkspace_info parkspace_info, int record_id)
{
    // 基本条件判断
    if(!mb_initialized)
    {
        return DB_UNINITIALIZED;
    }

        //执行sql操作
        if(!parkspace_info.has_car_info() || parkspace_info.parkspace_id() <= 0)
        {
            return PARAMETER_ERROR;
        }
        char update_parking_record_sql[1024];
        memset(update_parking_record_sql, 0, 1024);
        sprintf(update_parking_record_sql, "update parkingrecords set realGetTime='%s',parkingPrice=%d where parkingRecordsID = %d",
                parkspace_info.leave_time().c_str(),
                0,
                record_id);
        Error_manager ec =   m_db_controller.sql_update(update_parking_record_sql);
        return ec;
    
}

// 根据车位编号查询车位状态
Error_manager Parkspace_db_manager::query_parkspace(int parkspace_id, message::Parkspace_info &parkspace_info)
{
    // 基本条件判断
    if(!mb_initialized)
    {
        return DB_UNINITIALIZED;
    }

        //执行sql操作
        std::string query_parkspace_sql = std::string("select * from parkingspace where parkingSpaceID=").append(std::to_string(parkspace_id));
        boost::shared_ptr<sql::ResultSet>  tp_result = nullptr;
        Error_manager ec =   m_db_controller.sql_query(query_parkspace_sql, tp_result);
        if(ec == SUCCESS)
        {
            if(tp_result == nullptr)
            {
                return DB_RESULT_SET_EMPTY;
            }
            if (tp_result->next())
            {
                char buf[1024];
                memset(buf, 0, 1024);
                try
                {
                    switch (tp_result->getInt("parkingSpaceState"))
                    {
                    case 0:
                        parkspace_info.set_parkspace_status(message::Parkspace_status::eParkspace_empty);
                        break;
                    case 1:
                        parkspace_info.set_parkspace_status(message::Parkspace_status::eParkspace_occupied);
                        break;
                    case 2:
                        parkspace_info.set_parkspace_status(message::Parkspace_status::eParkspace_reserved);
                        break;
                    case 3:
                        parkspace_info.set_parkspace_status(message::Parkspace_status::eParkspace_locked);
                        break;
                    case 4:
                        parkspace_info.set_parkspace_status(message::Parkspace_status::eParkspace_error);
                        break;
                    default:
                        parkspace_info.set_parkspace_status(message::Parkspace_status::eParkspace_error);
                        break;
                    }
                    parkspace_info.set_index(tp_result->getInt("parkingSpaceX"));
                    parkspace_info.set_direction(tp_result->getInt("parkingSpaceY") > 0 ? message::Direction::eForward : message::Direction::eBackward);
                    parkspace_info.set_floor(tp_result->getInt("parkingSpaceZ"));
//					parkspace_info.set_length();//数据库暂时没有此字段 后续添加再用
					parkspace_info.set_width(tp_result->getDouble("parkSpaceWidth"));
					parkspace_info.set_height(tp_result->getDouble("parkSpaceheight"));

					parkspace_info.mutable_car_info()->set_license(tp_result->getString("parkSpaceCarLicense"));
					parkspace_info.mutable_car_info()->set_car_length(tp_result->getDouble("parkSpaceCarLength"));
					parkspace_info.mutable_car_info()->set_car_width(tp_result->getDouble("parkSpaceCarWidth"));
                    parkspace_info.mutable_car_info()->set_car_height(tp_result->getDouble("parkSpaceCarHeight"));

                    parkspace_info.set_entry_time(tp_result->getString("entryTime"));
                    parkspace_info.set_leave_time(tp_result->getString("leaveTime"));
                }
                catch (sql::SQLException &e)
                {
                    /* Use what() (derived from std::runtime_error) to fetch the error message */
                    sprintf(buf, "# ERR: %s\n (MySQL error code: %d, SQLState: %s", e.what(), e.getErrorCode(), e.getSQLState().c_str());
                    return Error_manager(DB_RESULT_SET_PARSE_ERROR, NEGLIGIBLE_ERROR, buf);
                }
                catch (std::runtime_error &e)
                {
                    sprintf(buf, "# ERR: %s\n ERR: runtime_error in  %s ", e.what(), __FILE__);
                    return Error_manager(DB_RESULT_SET_PARSE_ERROR, NEGLIGIBLE_ERROR, buf);
                }
            }
            return SUCCESS;
        }
        else
        {
            return ec;
        }
    
}

// 查询车辆状态，暂时不使用
Error_manager Parkspace_db_manager::query_vehicle(std::string license, message::Vehicle_status &vehicle_status, int &park_record_id)
{
    // 基本条件判断
    if(!mb_initialized)
    {
        return DB_UNINITIALIZED;
    }

	//执行sql操作
	std::string query_parkspace_sql = std::string("select * from vehicle where numberPlate='").append(license).append("'");
	boost::shared_ptr<sql::ResultSet>  tp_result = nullptr;
	Error_manager ec =   m_db_controller.sql_query(query_parkspace_sql, tp_result);
	if(ec == SUCCESS)
	{
		if(tp_result == nullptr)
		{
			return DB_RESULT_SET_EMPTY;
		}
		if (tp_result->next())
		{
			char buf[1024];
			memset(buf, 0, 1024);
			try
			{
				switch (tp_result->getInt("vehicleParkState"))
				{
				case 0:
					vehicle_status = message::Vehicle_status::eVehicle_idle;
					break;
				case 1:
					vehicle_status = message::Vehicle_status::eVehicle_in_garage;
					break;
				case 2:
					vehicle_status = message::Vehicle_status::eVehicle_parking;
					break;
				case 3:
					vehicle_status = message::Vehicle_status::eVehicle_fetching;
					break;
				case 4:
					vehicle_status = message::Vehicle_status::eVehicle_reserved;
					break;
				default:
					vehicle_status = message::Vehicle_status::eVehicle_reserved;
					break;
				}
				park_record_id = tp_result->getInt("parkingRecordsID");
			}
			catch (sql::SQLException &e)
			{
				/* Use what() (derived from std::runtime_error) to fetch the error message */
				sprintf(buf, "# ERR: %s\n (MySQL error code: %d, SQLState: %s", e.what(), e.getErrorCode(), e.getSQLState().c_str());
				return Error_manager(DB_RESULT_SET_PARSE_ERROR, NEGLIGIBLE_ERROR, buf);
			}
			catch (std::runtime_error &e)
			{
				sprintf(buf, "# ERR: %s\n ERR: runtime_error in  %s ", e.what(), __FILE__);
				return Error_manager(DB_RESULT_SET_PARSE_ERROR, NEGLIGIBLE_ERROR, buf);
			}
		}
		return SUCCESS;
	}
	else
	{
		return ec;
	}

}

// 查询停车记录，根据车牌号、车位id查询，反馈停车记录id
Error_manager Parkspace_db_manager::query_parking_record(message::Parkspace_info &parkspace_info, int &record_id)
{
    // 基本条件判断
    if(!mb_initialized)
    {
        return DB_UNINITIALIZED;
    }

	//执行sql操作
	if(!parkspace_info.has_car_info() || parkspace_info.parkspace_id() <= 0)
	{
		return PARAMETER_ERROR;
	}
	std::string query_park_record_sql = std::string("select * from parkingrecords where numberPlate='").append(parkspace_info.car_info().license()).append("' and parkingSpaceID=").append(std::to_string(parkspace_info.parkspace_id())).append(" ORDER BY parkingRecordsID DESC");
	boost::shared_ptr<sql::ResultSet>  tp_result = nullptr;
	Error_manager ec =   m_db_controller.sql_query(query_park_record_sql, tp_result);
	if(ec == SUCCESS)
	{
		if(tp_result == nullptr)
		{
			return DB_RESULT_SET_EMPTY;
		}
		if (tp_result->next())
		{
			char buf[1024];
			memset(buf, 0, 1024);
			try
			{
				record_id = tp_result->getInt("parkingRecordsID");
			}
			catch (sql::SQLException &e)
			{
				/* Use what() (derived from std::runtime_error) to fetch the error message */
				sprintf(buf, "# ERR: %s\n (MySQL error code: %d, SQLState: %s", e.what(), e.getErrorCode(), e.getSQLState().c_str());
				return Error_manager(DB_RESULT_SET_PARSE_ERROR, NEGLIGIBLE_ERROR, buf);
			}
			catch (std::runtime_error &e)
			{
				sprintf(buf, "# ERR: %s\n ERR: runtime_error in  %s ", e.what(), __FILE__);
				return Error_manager(DB_RESULT_SET_PARSE_ERROR, NEGLIGIBLE_ERROR, buf);
			}
		}
		return SUCCESS;
	}
	else
	{
		return ec;
	}
    
}