PNPkurs-PIbd-31-Alekseev-I-S/service/serviceloaddb.cpp

#include "serviceloaddb.h"

ServiceLoadDB::ServiceLoadDB(QObject *parent)
    : QObject{parent}
{}

void ServiceLoadDB::start()
{
    db = QSqlDatabase::addDatabase("QPSQL");
    db.setHostName("127.0.0.1");
    db.setDatabaseName("ProjectsAccountingDB");
    db.setUserName("postgres");
    db.setPassword("12345");
    if(!db.open()){
        qDebug() << "Не удалось открыть БД ";
        return;
    }
    query = QSqlQuery(db);

    loadStages();
    loadWorkers();
    loadTeams();
    loadDeviceTypes();
    loadDeviceModels();
    loadProjects();
}

void ServiceLoadDB::loadStages()
{
    QFile file("../../data/requestStages.sql");
    if (!file.open(QIODevice::ReadOnly))
        return;

    db_input = QString(file.readAll());

    if (!query.exec(db_input)) {
        qDebug() << "Ошибка запроса к таблице stages: " << query.lastError().text();
        return;
    }

    QSqlRecord rec;
    while (query.next()) {
        rec = query.record();

        Stages stages;
        int id = query.value(rec.indexOf("ID")).toInt();
        stages.setId(id);
        stages.setName(query.value(rec.indexOf("Name")).toString());
        stages.setStartDate(query.value(rec.indexOf("StartDate")).toDateTime());
        stages.setFinishDate(query.value(rec.indexOf("FinishDate")).toDateTime());
        stages.setCompleted(query.value(rec.indexOf("Completed")).toBool());
        stages.setExpenses(query.value(rec.indexOf("Expenses")).toDouble());
        stages.setIdWorker(query.value(rec.indexOf("WorkerID")).toInt());
        stages.setNameWorker(query.value(rec.indexOf("worker_name")).toString());

        mapStages.insert(stages.id(), stages);
    }

    emit sendStages(mapStages);
}

void ServiceLoadDB::loadWorkers()
{
    QFile file("../../data/requestWorkers.sql");
    if (!file.open(QIODevice::ReadOnly))
        return;

    db_input = QString(file.readAll());

    if (!query.exec(db_input)) {
        qDebug() << "Ошибка запроса к таблице Worker: " << query.lastError().text();
        return;
    }

    QSqlRecord rec;
    while (query.next()) {
        rec = query.record();

        Worker worker;
        int id = query.value(rec.indexOf("ID")).toInt();
        worker.setId(id);
        worker.setName(query.value(rec.indexOf("Name")).toString());
        worker.setExperience(query.value(rec.indexOf("Experience")).toInt());
        worker.setPhoneNumber(query.value(rec.indexOf("PhoneNumber")).toString());
        worker.setTeamName(query.value(rec.indexOf("team_name")).toString());

        mapWorkers.insert(worker.id(), worker);
    }

    emit sendWorkers(mapWorkers);
}

void ServiceLoadDB::loadTeams()
{
    db_input = "SELECT * FROM public.Team";
    if (!query.exec(db_input)) {
        qDebug() << "Ошибка запроса к таблице Team: " << query.lastError().text();
        return;
    }

    QSqlRecord rec;
    while (query.next()) {
        rec = query.record();

        Team team;
        team.setId(query.value(rec.indexOf("ID")).toInt());
        team.setName(query.value(rec.indexOf("Name")).toString());
        team.setStatus(query.value(rec.indexOf("Status")).toBool());

        mapTeams.insert(team.id(), team);
    }

    emit sendTeams(mapTeams);
}

void ServiceLoadDB::loadDeviceTypes()
{
    db_input = "SELECT * FROM public.DeviceType";
    if (!query.exec(db_input)) {
        qDebug() << "Ошибка запроса к таблице DeviceType: " << query.lastError().text();
        return;
    }

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

    emit sendDeviceTypes(mapDeviceTypes);
}

void ServiceLoadDB::loadDeviceModels()
{
    QFile file("../../data/requestDeviceModels.sql");
    if (!file.open(QIODevice::ReadOnly))
        return;

    db_input = QString(file.readAll());

    if (!query.exec(db_input)) {
        qDebug() << "Ошибка запроса при получении информации о моделях проектов: " << query.lastError().text();
        return;
    }

    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(query.value(rec.indexOf("Description")).toString());
        deviceModel.setWorkEfficiency(query.value(rec.indexOf("WorkEfficiency")).toInt());
        deviceModel.setReliability(query.value(rec.indexOf("reliability")).toInt());
        deviceModel.setDurability(query.value(rec.indexOf("durability")).toInt());
        deviceModel.setCreateDate(query.value(rec.indexOf("CreateDate")).toDateTime());
        deviceModel.setStructureElements(readStructureElements(id));
        deviceModel.setIdType(query.value(rec.indexOf("DeviceTypeID")).toInt());
        deviceModel.setNameType(query.value(rec.indexOf("device_type_name")).toString());

        mapDeviceModels.insert(deviceModel.id(), deviceModel);
    }

    emit sendDeviceModels(mapDeviceModels);
}

void ServiceLoadDB::loadProjects()
{
    QFile file("../../data/requestProjects.sql");
    if (!file.open(QIODevice::ReadOnly))
        return;
    db_input = QString(file.readAll());

    if (!query.exec(db_input)) {
        qDebug() << "Ошибка запроса при получении информации о проектах: " << query.lastError().text();
        return;
    }

    QSqlRecord rec;
    while (query.next()) {
        rec = query.record();

        Project project;
        int id = query.value(rec.indexOf("id")).toInt();
        int idModel = query.value(rec.indexOf("ModelID")).toInt();
        if (!mapDeviceModels.contains(idModel)) {
            qDebug() << "Не загружена модель проекта. Идентификатор проекта: " << id;
            return;
        }

        project.setId(id);
        project.setName(query.value(rec.indexOf("Name")).toString());
        project.setDescription(query.value(rec.indexOf("Description")).toString());
        project.setStartProjectDate(query.value(rec.indexOf("StartProjectDate")).toDateTime());
        project.setFinishProjectDate(query.value(rec.indexOf("FinishProjectDate")).toDateTime());
        project.setBudget(query.value(rec.indexOf("Budget")).toDouble());
        project.setIsReady(query.value(rec.indexOf("isReady")).toBool());
        project.setIdTeam(query.value(rec.indexOf("TeamID")).toInt());
        project.setNameTeam(query.value(rec.indexOf("team_name")).toString());
        project.setIdStages(query.value(rec.indexOf("StagesID")).toInt());
        project.setNameStages(query.value(rec.indexOf("stages_name")).toString());
        project.setDeviceModel(mapDeviceModels[idModel]);
        project.setIsLiked(query.value(rec.indexOf("isLiked")).toBool());

        mapProjects.insert(project.id(), project);
    }

    emit sendProjects(mapProjects);
}

void ServiceLoadDB::updateLikesState(QMap<int, Project> &map)
{
    if (map.isEmpty()) {
        qDebug() << "Нет проектов для обновления";
        return;
    }

    QStringList updateQueries;
    for (auto &project : map) {
        int id = project.id();
        bool isLiked = project.isLiked();

        updateQueries << QString("WHEN %1 THEN %2").arg(id).arg(isLiked ? "TRUE" : "FALSE");
    }

    QString caseQuery = updateQueries.join(" ");

    QStringList idStrings;
    QList<int> keys = map.keys();
    for (int id : keys) {
        idStrings << QString::number(id);
    }

    db_input = QString("UPDATE Project SET isLiked = CASE id %1 END WHERE id IN (%2)")
                   .arg(caseQuery, idStrings.join(", "));

    if (!query.exec(db_input)) {
        qDebug() << "Ошибка обновления состояния isLiked у проектов: " << query.lastError().text();
    }
}

QList<DeviceStructureElement> ServiceLoadDB::readStructureElements(int modelId)
{
    QSqlQuery secondQuery;
    db_input = "SELECT * FROM get_model_structure(:model_id)";
    secondQuery.prepare(db_input);
    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.setName(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;
}