/* * @Description: 车位数据库管理 * @Author: yct * @Date: 2020-07-19 09:57:45 * @LastEditTime: 2020-07-22 19:07:45 * @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::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 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("parkingSpaceIndex")); if (tp_result->getInt("parkingSpaceDirection") > 0) { t_parkspace->set_direction(message::Direction::eForward); } else { t_parkspace->set_direction(message::Direction::eBackward); } t_parkspace->set_floor(tp_result->getInt("parkingSpaceFloor")); 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_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::clean_all_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); sprintf(update_space_sql, "update parkingspace set parkingSpaceState = 0, parkSpaceCarLicense=''"); Error_manager ec = mp_db_controller->sql_update(update_space_sql); return ec; } return ERROR; } // 更新数据库中所有车位信息 Error_manager Parkspace_db_manager::update_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 if(all_parkspace_info.parkspace_info_size() >0){ Error_manager ec = SUCCESS; for (size_t i = 0; i < all_parkspace_info.parkspace_info_size(); i++) { Error_manager t_ec = update_parkspace_status(all_parkspace_info.parkspace_info(i)); ec.compare_and_cover_error(&t_ec); } return ec; } return ERROR; } // 更新数据库中车位状态 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 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 query_vehicle_result = nullptr; mp_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 = 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().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 = 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().c_str(), 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().c_str(), 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 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("parkingSpaceIndex")); parkspace_info.set_direction(tp_result->getInt("parkingSpaceDirection") > 0 ? message::Direction::eForward : message::Direction::eBackward); parkspace_info.set_floor(tp_result->getInt("parkingSpaceFloor")); 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 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 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; } } }