278 lines
10 KiB
C++
278 lines
10 KiB
C++
#include "serviceloaddb.h"
|
||
|
||
ServiceLoadDB::ServiceLoadDB(QObject *parent) : QObject(parent)
|
||
{}
|
||
|
||
void ServiceLoadDB::start()
|
||
{
|
||
_db = QSqlDatabase::addDatabase("QPSQL");
|
||
_db.setHostName("127.0.0.1");
|
||
_db.setDatabaseName("hardware_accounting_db");
|
||
_db.setUserName("postgres");
|
||
_db.setPassword("pass");
|
||
if(!_db.open()){
|
||
qDebug() << "Не удалось открыть БД";
|
||
return;
|
||
}
|
||
_query = QSqlQuery(_db);
|
||
}
|
||
|
||
QMap<int, Location> ServiceLoadDB::loadLocations()
|
||
{
|
||
QMap<int, Location> mapLocations;
|
||
|
||
_dbInput = "SELECT * FROM public.location";
|
||
if (!_query.exec(_dbInput)) {
|
||
qDebug() << "Ошибка запроса к таблице location: " << _query.lastError().text();
|
||
return mapLocations;
|
||
}
|
||
|
||
QSqlRecord rec;
|
||
while (_query.next()) {
|
||
rec = _query.record();
|
||
|
||
Location location;
|
||
location.setId(_query.value(rec.indexOf("id")).toInt());
|
||
location.setName(_query.value(rec.indexOf("name")).toString());
|
||
|
||
mapLocations.insert(location.id(), location);
|
||
}
|
||
|
||
return mapLocations;
|
||
}
|
||
|
||
QMap<int, Department> ServiceLoadDB::loadDepartments()
|
||
{
|
||
QMap<int, Department> mapDepartments;
|
||
|
||
_dbInput = "SELECT * FROM public.department";
|
||
if (!_query.exec(_dbInput)) {
|
||
qDebug() << "Ошибка запроса к таблице department: " << _query.lastError().text();
|
||
return mapDepartments;
|
||
}
|
||
|
||
QSqlRecord rec;
|
||
while (_query.next()) {
|
||
rec = _query.record();
|
||
|
||
Department department;
|
||
department.setId(_query.value(rec.indexOf("id")).toInt());
|
||
department.setName(_query.value(rec.indexOf("name")).toString());
|
||
|
||
mapDepartments.insert(department.id(), department);
|
||
}
|
||
|
||
return mapDepartments;
|
||
}
|
||
|
||
QMap<int, Manufacturer> ServiceLoadDB::loadManufacturers()
|
||
{
|
||
QMap<int, Manufacturer> mapManufacturers;
|
||
|
||
_dbInput = "SELECT * FROM public.manufacturer";
|
||
if (!_query.exec(_dbInput)) {
|
||
qDebug() << "Ошибка запроса к таблице manufacturer: " << _query.lastError().text();
|
||
return mapManufacturers;
|
||
}
|
||
|
||
QSqlRecord rec;
|
||
while (_query.next()) {
|
||
rec = _query.record();
|
||
|
||
Manufacturer manufacturer;
|
||
manufacturer.setId(_query.value(rec.indexOf("id")).toInt());
|
||
manufacturer.setName(_query.value(rec.indexOf("name")).toString());
|
||
|
||
mapManufacturers.insert(manufacturer.id(), manufacturer);
|
||
}
|
||
|
||
return mapManufacturers;
|
||
}
|
||
|
||
QMap<int, DeviceType> ServiceLoadDB::loadDeviceTypes()
|
||
{
|
||
QMap<int, DeviceType> mapDeviceTypes;
|
||
|
||
_dbInput = "SELECT * FROM public.device_type";
|
||
if (!_query.exec(_dbInput)) {
|
||
qDebug() << "Ошибка запроса к таблице device_type: " << _query.lastError().text();
|
||
return mapDeviceTypes;
|
||
}
|
||
|
||
QSqlRecord rec;
|
||
while (_query.next()) {
|
||
rec = _query.record();
|
||
|
||
DeviceType deviceType;
|
||
deviceType.setId(_query.value(rec.indexOf("id")).toInt());
|
||
deviceType.setName(_query.value(rec.indexOf("name")).toString());
|
||
|
||
mapDeviceTypes.insert(deviceType.id(), deviceType);
|
||
}
|
||
|
||
return mapDeviceTypes;
|
||
}
|
||
|
||
QMap<int, DeviceModel> ServiceLoadDB::loadDeviceModels()
|
||
{
|
||
QFile file("../../data/requestDeviceModels.sql");
|
||
if (!file.open(QIODevice::ReadOnly)) {
|
||
qDebug() << "Не получилось открыть файл \"requestDeviceModels.sql\"";
|
||
return QMap<int, DeviceModel>();
|
||
}
|
||
|
||
_dbInput = QString(file.readAll());
|
||
|
||
if (!_query.exec(_dbInput)) {
|
||
qDebug() << "Ошибка запроса при получении информации о моделях устройств: " << _query.lastError().text();
|
||
return QMap<int, DeviceModel>();
|
||
}
|
||
|
||
QSqlRecord rec;
|
||
while (_query.next()) {
|
||
rec = _query.record();
|
||
|
||
DeviceModel deviceModel;
|
||
int id = _query.value(rec.indexOf("id")).toInt();
|
||
deviceModel.setId(id);
|
||
deviceModel.setName(_query.value(rec.indexOf("name")).toString());
|
||
deviceModel.setDescription(getValueOrDefault<QString>(_query, rec, "description", QString("Описание отсутствует")));
|
||
deviceModel.setWorkEfficiency(_query.value(rec.indexOf("work_efficiency")).toInt());
|
||
deviceModel.setReliability(_query.value(rec.indexOf("reliability")).toInt());
|
||
deviceModel.setEnergyEfficiency(_query.value(rec.indexOf("energy_efficiency")).toInt());
|
||
deviceModel.setUserFriendliness(_query.value(rec.indexOf("user_friendliness")).toInt());
|
||
deviceModel.setDurability(_query.value(rec.indexOf("durability")).toInt());
|
||
deviceModel.setAestheticQualities(_query.value(rec.indexOf("aesthetic_qualities")).toInt());
|
||
deviceModel.setIdType(_query.value(rec.indexOf("fk_id_type")).toInt());
|
||
deviceModel.setNameType(_query.value(rec.indexOf("device_type_name")).toString());
|
||
deviceModel.setIdManuf(_query.value(rec.indexOf("fk_id_manuf")).toInt());
|
||
deviceModel.setNameManuf(_query.value(rec.indexOf("manufacturer_name")).toString());
|
||
deviceModel.setStructureElements(readStructureElements(id));
|
||
|
||
_mapDeviceModels.insert(deviceModel.id(), deviceModel);
|
||
}
|
||
|
||
return _mapDeviceModels;
|
||
}
|
||
|
||
QMap<int, Device> ServiceLoadDB::loadDevices()
|
||
{
|
||
QMap<int, Device> mapDevices;
|
||
|
||
QFile file("../../data/requestDevices.sql");
|
||
if (!file.open(QIODevice::ReadOnly)) {
|
||
qDebug() << "Не получилось открыть файл \"requestDevices.sql\"";
|
||
return mapDevices;
|
||
}
|
||
|
||
_dbInput = QString(file.readAll());
|
||
|
||
if (!_query.exec(_dbInput)) {
|
||
qDebug() << "Ошибка запроса при получении информации о устройствах: " << _query.lastError().text();
|
||
return mapDevices;
|
||
}
|
||
|
||
QSqlRecord rec;
|
||
while (_query.next()) {
|
||
rec = _query.record();
|
||
|
||
Device device;
|
||
int id = _query.value(rec.indexOf("id")).toInt();
|
||
int idModel = _query.value(rec.indexOf("fk_id_model")).toInt();
|
||
if (!_mapDeviceModels.contains(idModel)) {
|
||
qDebug() << "Не загружена модель устройства. Идентификатор устройства: " << id;
|
||
return QMap<int, Device>();
|
||
}
|
||
|
||
device.setId(id);
|
||
device.setSerialNumber(_query.value(rec.indexOf("serial_number")).toString());
|
||
device.setPurchaseDate(_query.value(rec.indexOf("purchase_date")).toDateTime());
|
||
device.setPrice(_query.value(rec.indexOf("price")).toDouble());
|
||
device.setWarrantyExpireDate(_query.value(rec.indexOf("warranty_expire_date")).toDateTime());
|
||
device.setIsWorking(_query.value(rec.indexOf("is_working")).toBool());
|
||
device.setFurtherInformation(getValueOrDefault<QString>(_query, rec, "further_information", QString("")));
|
||
device.setIdLocation(_query.value(rec.indexOf("fk_id_location")).toInt());
|
||
device.setNameLocation(_query.value(rec.indexOf("location_name")).toString());
|
||
device.setIdEmployee(getValueOrDefault<int>(_query, rec, "fk_id_employee", 0));
|
||
device.setNameEmployee(getValueOrDefault<QString>(_query, rec, "employee_full_name", QString("Не назначен")));
|
||
device.setIdDepartment(getValueOrDefault<int>(_query, rec, "department_id", 0));
|
||
device.setNameDepartment(getValueOrDefault<QString>(_query, rec, "department_name", QString("Не относится к отделу")));
|
||
device.setDeviceModel(_mapDeviceModels[idModel]);
|
||
device.setIsLiked(_query.value(rec.indexOf("is_liked")).toBool());
|
||
|
||
mapDevices.insert(device.id(), device);
|
||
}
|
||
|
||
return mapDevices;
|
||
}
|
||
|
||
bool ServiceLoadDB::updateDevice(const Device &device)
|
||
{
|
||
QFile file("../../data/updateDevice.sql");
|
||
if (!file.open(QIODevice::ReadOnly)) {
|
||
qDebug() << "Не удалось открыть файл с запросом для обновления устройства";
|
||
return false;
|
||
}
|
||
|
||
QString db_input = QString(file.readAll());
|
||
|
||
db_input = db_input.arg(device.serialNumber())
|
||
.arg(device.purchaseDate().toString("yyyy-MM-dd HH:mm:ss"))
|
||
.arg(device.price())
|
||
.arg(device.warrantyExpireDate().toString("yyyy-MM-dd HH:mm:ss"))
|
||
.arg(device.isWorking() ? "TRUE" : "FALSE")
|
||
.arg(device.furtherInformation())
|
||
.arg(device.idLocation())
|
||
.arg(device.idEmployee())
|
||
.arg(device.deviceModel().id())
|
||
.arg(device.isLiked() ? "TRUE" : "FALSE")
|
||
.arg(device.id());
|
||
|
||
if (!_query.exec(db_input)) {
|
||
qDebug() << "Ошибка обновления устройства с id = " << device.id() << ": " << _query.lastError().text();
|
||
return false;
|
||
}
|
||
|
||
return true;
|
||
}
|
||
|
||
QList<DeviceStructureElement> ServiceLoadDB::readStructureElements(int modelId)
|
||
{
|
||
QSqlQuery secondQuery;
|
||
_dbInput = "SELECT * FROM get_model_structure(:model_id)";
|
||
secondQuery.prepare(_dbInput);
|
||
secondQuery.bindValue(":model_id", modelId);
|
||
if (!secondQuery.exec()) {
|
||
qDebug() << "Ошибка при выполнении запроса для получения элементов структуры: " << secondQuery.lastError().text();
|
||
return QList<DeviceStructureElement>();
|
||
}
|
||
|
||
QSqlRecord rec;
|
||
QList<DeviceStructureElement> elements;
|
||
while (secondQuery.next()) {
|
||
rec = secondQuery.record();
|
||
|
||
DeviceStructureElement element;
|
||
element.setId(secondQuery.value(rec.indexOf("Идентификатор элемента")).toInt());
|
||
element.setNameModel(secondQuery.value(rec.indexOf("Модель")).toString());
|
||
element.setNameManuf(secondQuery.value(rec.indexOf("Производитель")).toString());
|
||
element.setDescription(secondQuery.value(rec.indexOf("Описание элемента")).toString());
|
||
element.setNameType(secondQuery.value(rec.indexOf("Тип элемента")).toString());
|
||
element.setCount(secondQuery.value(rec.indexOf("Количество")).toInt());
|
||
|
||
elements.append(element);
|
||
}
|
||
|
||
return elements;
|
||
}
|
||
|
||
template<typename T>
|
||
T ServiceLoadDB::getValueOrDefault(const QSqlQuery &query, const QSqlRecord &record, const QString &fieldName, const T &defaultValue)
|
||
{
|
||
int index = record.indexOf(fieldName);
|
||
if (index == -1 || query.value(index).isNull()) {
|
||
return defaultValue;
|
||
}
|
||
return query.value(index).value<T>();
|
||
}
|