measure_module/parkspace_allocation/parkspace_db_manager.cpp

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

#include "parkspace_db_manager.h"

// 车位数据库管理初始化
Error_manager Parkspace_db_manager::Parkspace_db_manager_init(parkspace_proto::database_config config)
{
    Error_manager ec = SUCCESS;
    mp_db_controller = boost::shared_ptr<Database_controller>(Database_controller::get_instance_pointer());
    if(mp_db_controller != nullptr)
    {
        ec = mp_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;
    }
    else
    {
        mb_initialized = false;
        return POINTER_IS_NULL;
    }
}
// 车位数据库管理反初始化
Error_manager Parkspace_db_manager::Parkspace_db_manager_uninit()
{
    if(mp_db_controller!=nullptr)
    {
        return mp_db_controller->database_controller_uninit();
    }else{
        return POINTER_IS_NULL;
    }
}

// 连接状态
bool Parkspace_db_manager::is_connected()
{
    if(mp_db_controller!=nullptr)
    {
        mb_connected = mp_db_controller->is_connected();
        return mb_connected;
    }
    else{
        return false;
    }
}

// 初始化状态
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;
    }
    if(mp_db_controller == nullptr)
    {
        return POINTER_IS_NULL;
    }else{
        //执行sql操作
        std::string get_all_space_sql = "select * from parkingspace";
        boost::shared_ptr<sql::ResultSet>  tp_result = nullptr;
        Error_manager ec = mp_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->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;
    }
    if(mp_db_controller == nullptr)
    {
        return POINTER_IS_NULL;
    }else{
        //执行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 = mp_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;
    }
    if(mp_db_controller == nullptr)
    {
        return POINTER_IS_NULL;
    }else{
        //执行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;
        mp_db_controller->sql_query(find_vehicle_sql, query_vehicle_result);
        //判断车辆是否存在
        if(query_vehicle_result == nullptr || !query_vehicle_result->next())
        {
            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());
            }
            //车位不为空，表示车辆所在车位，同时更新
            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());
            }
            if (vehicle_status_code < 0)
                return ERROR;
            Error_manager ec = mp_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;
    }
    if(mp_db_controller == nullptr)
    {
        return POINTER_IS_NULL;
    }else{
        //执行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(),
                    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(),
                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 = mp_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(mp_db_controller == nullptr)
    {
        return POINTER_IS_NULL;
    }else{
        //参数中必须包含车辆信息与车位编号
        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(),
                    parkspace_info.parkspace_id(),
                    parkspace_info.entry_time().c_str());
        Error_manager ec = mp_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;
    }
    if(mp_db_controller == nullptr)
    {
        return POINTER_IS_NULL;
    }else{
        //执行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(),
                0,
                record_id);
        Error_manager ec = mp_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;
    }
    if(mp_db_controller == nullptr)
    {
        return POINTER_IS_NULL;
    }else{
        //执行sql操作
        std::string query_parkspace_sql = std::string("select * from parkingspace where parkspace_id=").append(std::to_string(parkspace_id));
        boost::shared_ptr<sql::ResultSet>  tp_result = nullptr;
        Error_manager ec = mp_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.mutable_car_info()->set_license(tp_result->getString("parkSpaceCarLicense"));
                    parkspace_info.mutable_car_info()->set_car_length(tp_result->getDouble("parkSpaceCatLength"));
                    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;
    }
    if(mp_db_controller == nullptr)
    {
        return POINTER_IS_NULL;
    }else{
        //执行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 = mp_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;
    }
    if(mp_db_controller == nullptr)
    {
        return POINTER_IS_NULL;
    }else{
        //执行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 = mp_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;
        }
    }
}