pc-system/blastProject/hprojectdao.cpp

#include "HProjectDao.h"

#include "../utils/global.h"

HProjectDao::HProjectDao(QSqlDatabase db) : database(db) {}

bool HProjectDao::addHProject(const HProject &project) {
    QSqlQuery query;
    query.prepare(
        "INSERT INTO h_project (uuid, name, company_code, htid, xmbh, "
        "operator_identity, blaster_identity, safety_inspector_identity, address_uuid, address_path, "
        "level4_address, det_sum, file_name, file_url, blast_status, blast_count, created_at, updated_at, "
        "deleted_at, create_by, update_by) "
        "VALUES (:uuid, :name, :companyCode, :htid, :xmbh, :operatorIdentity, "
        ":blasterIdentity, :safetyInspectorIdentity, :addressUuid, :addressPath, :level4Address, :detSum, "
        ":fileName, :fileUrl, :blastStatus, :blastCount,:createdAt, :updatedAt, :deletedAt, :createBy, :updateBy)");

    query.bindValue(":uuid", project.getUuid());
    query.bindValue(":name", project.getName());
    query.bindValue(":companyCode", project.getCompanyCode());
    query.bindValue(":htid", project.getHtid());
    query.bindValue(":xmbh", project.getXmbh());
    query.bindValue(":operatorIdentity", project.getOperatorIdentity());
    query.bindValue(":blasterIdentity", project.getBlasterIdentity());
    query.bindValue(":safetyInspectorIdentity", project.getSafetyInspectorIdentity());
    query.bindValue(":addressUuid", project.getAddressUuid());
    query.bindValue(":addressPath", project.getAddressPath());
    query.bindValue(":level4Address", project.getLevel4Address());
    query.bindValue(":detSum", project.getDetSum());
    query.bindValue(":fileName", project.getFileName());
    query.bindValue(":fileUrl", project.getFileUrl());
    query.bindValue(":blastStatus", project.getBlastStatus());
    query.bindValue(":blastCount", project.getBlastCount());
    query.bindValue(":createdAt", project.getCreatedAt().toString(Qt::ISODateWithMs));
    query.bindValue(":updatedAt", project.getUpdatedAt().toString(Qt::ISODateWithMs));
    query.bindValue(":deletedAt", QVariant(QVariant::String));  // 这里可以设置为NULL或当前时间
    query.bindValue(":createBy", project.getCreateBy());
    query.bindValue(":updateBy", project.getUpdateBy());
    if (!query.exec()) {
        qDebug() << "HProjectDao::addHProject query: " << query.lastQuery() << query.lastError().text();
        return false;
    }

    return true;
}

bool HProjectDao::updateHProject(const HProject &project) {
    QSqlQuery query;
    query.prepare(
        "UPDATE h_project SET "
        "uuid = :uuid, "
        "name = :name, "
        "company_code = :companyCode, "
        "htid = :htid, "
        "xmbh = :xmbh, "
        "operator_identity = :operatorIdentity, "
        "blaster_identity = :blasterIdentity, "
        "safetyInspector_identity = :safetyInspectorIdentity, "
        "address_uuid = :addressUuid, "
        "address_path = :addressPath, "
        "det_sum = :detSum, "
        "file_name = :fileName, "
        "file_url = :fileUrl, "
        "blast_status = :blastStatus, "
        "created_at = :createdAt, "
        "updated_at = :updatedAt, "
        "deleted_at = :deletedAt, "
        "create_by = :createBy, "
        "update_by = :updateBy "
        "WHERE id = :id");

    query.bindValue(":id", project.getId());
    query.bindValue(":uuid", project.getUuid());
    query.bindValue(":name", project.getName());
    query.bindValue(":companyCode", project.getCompanyCode());
    query.bindValue(":htid", project.getHtid());
    query.bindValue(":xmbh", project.getXmbh());
    query.bindValue(":operatorIdentity", project.getOperatorIdentity());
    query.bindValue(":blasterIdentity", project.getBlasterIdentity());
    query.bindValue(":safetyInspectorIdentity", project.getSafetyInspectorIdentity());
    query.bindValue(":addressUuid", project.getAddressUuid());
    query.bindValue(":addressPath", project.getAddressPath());
    query.bindValue(":detSum", project.getDetSum());
    query.bindValue(":fileName", project.getFileName());
    query.bindValue(":fileUrl", project.getFileUrl());
    query.bindValue(":blastStatus", project.getBlastStatus());
    query.bindValue(":createdAt", project.getCreatedAt().toString(Qt::ISODateWithMs));
    query.bindValue(":updatedAt", project.getUpdatedAt().toString(Qt::ISODateWithMs));
    query.bindValue(":deletedAt", project.getDeletedAt().toString(Qt::ISODateWithMs));
    query.bindValue(":createBy", project.getCreateBy());
    query.bindValue(":updateBy", project.getUpdateBy());

    if (!query.exec()) {
        return false;
    }

    return true;
}

bool HProjectDao::deleteHProject(const HProject &project) {
    QSqlQuery query;
    query.prepare("update h_project set deleted_at = :deletionDate WHERE id = :id");
    query.bindValue(":id", project.getId());
    query.bindValue(":deletionDate", QDateTime::currentDateTime());
    if (!query.exec()) {
        return false;
    }
    return true;
}

// HProject HProjectDao::getHProjectById(qint64 id)
// {
//     QSqlQuery query;
//     // query.prepare("SELECT * FROM hproject WHERE id = :id");
//     // query.bindValue(":id", id);
//     // if (query.exec() && query.next()) {
//     //     return recordToProject(query.record());
//     // }
//     return HProject();
// }

PaginatedHProjectResult HProjectDao::getAllHProjects(int page, int pageSize) {
    QList<QSharedPointer<HProject>> projects;
    QSqlQuery query(database);
    int offset = (page - 1) * pageSize;
    query.prepare(
        "SELECT * FROM h_project WHERE deleted_at IS NULL ORDER BY created_at desc LIMIT :pageSize OFFSET :offset");
    query.bindValue(":pageSize", pageSize);
    query.bindValue(":offset", offset);

    if (query.exec()) {
        while (query.next()) {
            projects.append(recordToProject(query.record()));
        }
    } else {
        qWarning() << "Query execution failed: " << query.lastError().text();
    }

    query.prepare("SELECT COUNT(*) FROM h_project where deleted_at IS NULL");
    int totalCount = 0;
    if (query.exec() && query.next()) {
        totalCount = query.value(0).toInt();
    }
    return {projects, totalCount};
}

PaginatedHProjectResult HProjectDao::getAllHProjectsByOpera(int page, int pageSize) {
    QList<QSharedPointer<HProject>> projects;
    QSqlQuery query(database);
    int offset = (page - 1) * pageSize;
    query.prepare(
        "SELECT * FROM h_project WHERE blast_status = :safeCheckedStatus and deleted_at IS NULL ORDER BY created_at "
        "desc "
        "LIMIT :pageSize OFFSET :offset");
    query.bindValue(":safeCheckedStatus", BlastStatus::Registered);
    query.bindValue(":pageSize", pageSize);
    query.bindValue(":offset", offset);
    if (query.exec()) {
        while (query.next()) {
            projects.append(recordToProject(query.record()));
        }
    } else {
        qWarning() << "Query execution failed: " << query.lastError().text();
    }

    query.prepare("SELECT COUNT(*) FROM h_project WHERE blast_status = :safeCheckedStatus and deleted_at IS NULL");
    query.bindValue(":safeCheckedStatus", BlastStatus::Registered);
    int totalCount = 0;
    if (query.exec() && query.next()) {
        totalCount = query.value(0).toInt();
    }
    return {projects, totalCount};
}

QList<QSharedPointer<HProject>> HProjectDao::getRegistedProjectByAddressUuid(QList<QString> addressUuids) {
    QList<QSharedPointer<HProject>> projects;
    QSqlQuery query(database);

    if (addressUuids.isEmpty()) {
        return projects;
    }

    QString placeholders;
    for (int i = 0; i < addressUuids.size(); i++) {
        if (i > 0) {
            placeholders += ", ";
        }
        placeholders += QString(":addressUuid%1").arg(i);
    }

    QString queryStr = QString(
                           "SELECT * FROM h_project WHERE address_uuid IN (%1) "
                           "AND blast_status = %2 and deleted_at is NULL ORDER BY created_at")
                           .arg(placeholders)
                           .arg(BlastStatus::Registered);

    query.prepare(queryStr);

    // Bind each address UUID separately
    for (int i = 0; i < addressUuids.size(); i++) {
        query.bindValue(QString(":addressUuid%1").arg(i), addressUuids.at(i));
    }

    if (!query.exec()) {
        QMessageBox::critical(nullptr, "数据搜索异常",
                              QString("通过地址查询注册工程数据异常: %1").arg(query.lastError().text()));
        return projects;
    }
    while (query.next()) {
        projects.append(recordToProject(query.record()));
    }
    return projects;
}

// get all registered projects
QList<QSharedPointer<HProject>> HProjectDao::getAllHProjectsReg() {
    QList<QSharedPointer<HProject>> projects;
    QSqlQuery query(database);
    query.prepare(QString("SELECT * FROM h_project WHERE blast_status = %1 and deleted_at is null ORDER BY created_at ")
                      .arg(BlastStatus::Created));
    // 执行查询并打印SQL
    if (query.exec()) {
        while (query.next()) {
            projects.append(recordToProject(query.record()));
        }
    } else {
        qWarning() << "Query execution failed: " << query.lastError().text();
    }
    return projects;
}

// 更改状态
bool HProjectDao::updateBlastStatusByUuid(const QString &uuid, const QString &blastStatus) {
    QSqlQuery query;
    query.prepare(
        "UPDATE h_project SET "
        "blast_status = :blastStatus "
        "WHERE uuid = :uuid and blast_status != :blastStatus and deleted_at is NULL;");
    query.bindValue(":uuid", uuid);
    query.bindValue(":blastStatus", blastStatus);

    if (!query.exec()) {
        qCritical() << "Failed to update blast_status for HProject:" << query.lastError().text();
        return false;
    }
    return true;
}

QSharedPointer<HProject> HProjectDao::recordToProject(const QSqlRecord &record) {
    QSharedPointer<HProject> project = QSharedPointer<HProject>::create();
    project->setId(record.value("id").toInt());
    project->setUuid(record.value("uuid").toString());
    project->setName(record.value("name").toString());
    project->setCompanyCode(record.value("company_code").toString());
    project->setHtid(record.value("htid").toString());
    project->setXmbh(record.value("xmbh").toString());
    project->setOperatorIdentity(record.value("operator_identity").toString());
    project->setBlasterIdentity(record.value("blaster_identity").toString());
    project->setSafetyInspectorIdentity(record.value("safety_inspector_identity").toString());
    project->setAddressUuid(record.value("address_uuid").toString());
    project->setAddressPath(record.value("address_path").toString());
    project->setLevel4Address(record.value("level4_address").toString());
    project->setDetSum(record.value("det_sum").toString());
    project->setBlastCount(record.value("blast_count").toString());
    project->setFileName(record.value("file_name").toString());
    project->setFileUrl(record.value("file_url").toString());
    project->setBlastStatus(record.value("blast_status").toString());
    project->setCreatedAt(QDateTime::fromString(record.value("created_at").toString(), Qt::ISODateWithMs));
    project->setUpdatedAt(QDateTime::fromString(record.value("updated_at").toString(), Qt::ISODateWithMs));
    project->setDeletedAt(QDateTime::fromString(record.value("deleted_at").toString(), Qt::ISODateWithMs));
    project->setCreateBy(record.value("create_by").toInt());
    project->setUpdateBy(record.value("update_by").toInt());
    return project;
}

QList<QSharedPointer<HProject>> HProjectDao::getHProjectsFromJsonArray(const QJsonArray &jsonArray) {
    QList<QSharedPointer<HProject>> projects;

    for (const QJsonValue &value : jsonArray) {
        if (value.isObject()) {
            QJsonObject obj = value.toObject();
            QSharedPointer<HProject> project = QSharedPointer<HProject>::create();

            // 解析JSON对象并设置HProject属性
            project->setId(obj["id"].toInt());
            project->setUuid(obj["uuid"].toString());
            project->setName(obj["name"].toString());
            project->setCompanyCode(obj["companyCode"].toString());
            project->setHtid(obj["htid"].toString());
            project->setXmbh(obj["xmbh"].toString());
            project->setOperatorIdentity(obj["operatorIdentity"].toString());
            project->setBlasterIdentity(obj["blasterIdentity"].toString());

            // 处理地址相关字段
            project->setAddressPath(obj["address"].toString());
            project->setLevel4Address(obj["lv4Address"].toString());

            // 处理数值字段
            project->setDetSum(QString::number(obj["detSum"].toInt()));
            project->setBlastCount(QString::number(obj["explosiveQuantity"].toInt()));
            project->setPcSn(obj["pcSn"].toString());

            // 处理状态
            project->setBlastStatus(obj["blastStatus"].toString());

            // 处理时间字段
            QString createdAtStr = obj["createdAt"].toString();
            if (!createdAtStr.isEmpty()) {
                project->setCreatedAt(QDateTime::fromString(createdAtStr, Qt::ISODateWithMs));
            }

            QString updatedAtStr = obj["updatedAt"].toString();
            if (!updatedAtStr.isEmpty()) {
                project->setUpdatedAt(QDateTime::fromString(updatedAtStr, Qt::ISODateWithMs));
            }

            // 处理创建者和更新者
            project->setCreateBy(obj["createBy"].toInt());
            project->setUpdateBy(obj["updateBy"].toInt());

            projects.append(project);
        }
    }

    return projects;
}