QFD2/QFD/dbService/NodeMatrixService.cpp

#include "NodeMatrixService.h"
#include "SqlDBHelper.h"
#include <QDebug>

NodeMatrixService::NodeMatrixService(QObject *parent) { }

bool NodeMatrixService::AddNodeWeightInfo(const DemandWeight &demandWeight)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        InsertQuery q = t.insertInto("t_demand_weight (engineer_id,expert_id, node_name, node_weight, "
                                     "node_value,table_index )");
        q.values(demandWeight.engineerId, demandWeight.expertId, demandWeight.nodeName, demandWeight.nodeWeight,
                 demandWeight.nodeValue, demandWeight.tableIndex)
                .exec();
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

/*批量节点信息新增*/
bool NodeMatrixService::AddNodeWeightInfoList(const QList<DemandWeight *> &demandWeightList)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        for (int i = 0; i < demandWeightList.length(); i++) {
            DemandWeight *demandWeight = demandWeightList.at(i);
            InsertQuery q              = t.insertInto("t_demand_weight (engineer_id,expert_id, node_name, node_weight, "
                                         "node_value,table_index,table_msg,is_valid,page_index )");
            q.values(demandWeight->engineerId, demandWeight->expertId, demandWeight->nodeName, demandWeight->nodeWeight,
                     demandWeight->nodeValue, demandWeight->tableIndex, demandWeight->tableMsg, demandWeight->isValid,
                     demandWeight->pageIndex)
                    .exec();
            t.commit();
        }
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

/*修改节点值*/
bool NodeMatrixService::UpdateNodeValue(const DemandWeight &demandWeight)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.update("t_demand_weight")
                .set("NODE_VALUE", demandWeight.nodeValue)
                .set("NODE_WEIGHT", demandWeight.nodeWeight)
                .where("ENGINEER_ID = ? and expert_id = ? and node_name = ? ", demandWeight.engineerId,
                       demandWeight.expertId, demandWeight.nodeName);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool NodeMatrixService::UpdateNodeValueList(const QList<DemandWeight *> demandWeightList)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret = false;
    for (int i = 0; i < demandWeightList.length(); i++) {
        DemandWeight *demandWeight = demandWeightList.at(i);
        QString updateSql          = QString("UPDATE t_demand_weight SET NODE_VALUE ='%1' , NODE_WEIGHT = '%2' "
                                    "WHERE ENGINEER_ID =%3 AND expert_id =%4"
                                    " AND node_name = '%5' AND table_index =%6 and table_msg ='%7' and page_index=%8")
                                    .arg(demandWeight->nodeValue)
                                    .arg(demandWeight->nodeWeight)
                                    .arg(demandWeight->engineerId)
                                    .arg(demandWeight->expertId)
                                    .arg(demandWeight->nodeName)
                                    .arg(demandWeight->tableIndex)
                                    .arg(demandWeight->tableMsg)
                                    .arg(demandWeight->pageIndex);
        // qDebug() << updateSql;
        query.exec(updateSql);
        ret = true;
    }

    return ret;
}
bool NodeMatrixService::QueryByTableIndexAndTableMsg(int expertId, int engineerId, int tableIndex, QString tableMsg)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select * from t_demand_weight where expert_id "
                                "=%1 and engineer_id =%2 and table_index=%3 and table_msg='%4'")
                                .arg(QString::number(expertId))
                                .arg(QString::number(engineerId))
                                .arg(QString::number(tableIndex))
                                .arg(tableMsg);
    // qDebug() << "sql===" << selectSql;
    if (query.exec(selectSql)) {
        if (query.next()) {
            ret = true;
        }

    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool NodeMatrixService::QueryByTableIndexAndTableMsg(QString expertId, int engineerId, int tableIndex, QString tableMsg)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select * from t_demand_weight where expert_id "
                                "='%1' and engineer_id =%2 and table_index=%3 and table_msg='%4'")
                                .arg(expertId)
                                .arg(QString::number(engineerId))
                                .arg(QString::number(tableIndex))
                                .arg(tableMsg);
    // qDebug() << "sql===" << selectSql;
    if (query.exec(selectSql)) {
        if (query.next()) {
            ret = true;
        }

    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool NodeMatrixService::QueryByTableIndexAndTableMsgAndPage(QString expertId, int engineerId, int tableIndex,
                                                            QString tableMsg, int page)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select * from t_demand_weight where expert_id "
                                "='%1' and engineer_id =%2 and table_index=%3 and table_msg='%4' and page_index=%5")
                                .arg(expertId)
                                .arg(QString::number(engineerId))
                                .arg(QString::number(tableIndex))
                                .arg(tableMsg)
                                .arg(page);
    // qDebug() << "sql===" << selectSql;
    if (query.exec(selectSql)) {
        if (query.next()) {
            ret = true;
        }

    } else {
        qDebug() << query.lastError();
    }
    return ret;
}
bool NodeMatrixService::QueryByTableIndexAndTableMsg(QList<DemandWeight *> *demandWeightList, int expertId,
                                                     int engineerId, int tableIndex, QString tableMsg)

{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select * from t_demand_weight where expert_id "
                                "=%1 and engineer_id =%2 and table_index=%3 and table_msg='%4'")
                                .arg(QString::number(expertId))
                                .arg(QString::number(engineerId))
                                .arg(QString::number(tableIndex))
                                .arg(tableMsg);
    // qDebug() << "sql=" << selectSql;
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                DemandWeight *demandWeight = new DemandWeight();
                demandWeight->id           = query.value(0).toInt();
                demandWeight->engineerId   = query.value(1).toInt();
                demandWeight->expertId     = query.value(2).toInt();
                demandWeight->nodeName     = query.value(3).toString();
                demandWeight->nodeValue    = query.value(4).toDouble();
                demandWeight->nodeWeight   = query.value(5).toDouble();
                demandWeight->tableIndex   = query.value(6).toInt();
                demandWeight->tableMsg     = query.value(7).toString();
                demandWeightList->append(demandWeight);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool NodeMatrixService::QueryByTableIndexAndTableMsg(QList<DemandWeight *> *demandWeightList, QString expertId,
                                                     int engineerId, int tableIndex, QString tableMsg)

{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select * from t_demand_weight where expert_id "
                                "='%1' and engineer_id =%2 and table_index=%3 and table_msg='%4'")
                                .arg(expertId)
                                .arg(QString::number(engineerId))
                                .arg(QString::number(tableIndex))
                                .arg(tableMsg);
    // qDebug() << "sql=" << selectSql;
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                DemandWeight *demandWeight = new DemandWeight();
                demandWeight->id           = query.value(0).toInt();
                demandWeight->engineerId   = query.value(1).toInt();
                demandWeight->expertId     = query.value(2).toInt();
                demandWeight->nodeName     = query.value(3).toString();
                demandWeight->nodeValue    = query.value(4).toDouble();
                demandWeight->nodeWeight   = query.value(5).toDouble();
                demandWeight->tableIndex   = query.value(6).toInt();
                demandWeight->tableMsg     = query.value(7).toString();
                demandWeightList->append(demandWeight);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool NodeMatrixService::QueryByPageIndexAndTableMsg(QList<DemandWeight *> *demandWeightList, QString expertId,
                                                    int engineerId, int pageIndex, QString tableMsg)

{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select * from t_demand_weight where expert_id "
                                "='%1' and engineer_id =%2 and page_index=%3 and table_msg='%4'")
                                .arg(expertId)
                                .arg(QString::number(engineerId))
                                .arg(QString::number(pageIndex))
                                .arg(tableMsg);
    // qDebug() << "sql=" << selectSql;
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                DemandWeight *demandWeight = new DemandWeight();
                demandWeight->id           = query.value(0).toInt();
                demandWeight->engineerId   = query.value(1).toInt();
                demandWeight->expertId     = query.value(2).toInt();
                demandWeight->nodeName     = query.value(3).toString();
                demandWeight->nodeValue    = query.value(4).toDouble();
                demandWeight->nodeWeight   = query.value(5).toDouble();
                demandWeight->tableIndex   = query.value(6).toInt();
                demandWeight->tableMsg     = query.value(7).toString();
                demandWeightList->append(demandWeight);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool NodeMatrixService::updateValidByExperIdAndEngineerId(int expertId, int engineerId)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret = false;
    try {
        QString updateSql = QString("UPDATE t_demand_weight SET is_valid =1  "
                                    "WHERE ENGINEER_ID =%1 AND expert_id =%2")
                                    .arg(engineerId)
                                    .arg(expertId);
        // qDebug() << updateSql;
        query.exec(updateSql);
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}
bool NodeMatrixService::QueryFirstDemandWeightByEngineerId(QList<DemandWeight *> *demandWeightList, int expertId,
                                                           int engineerId, QString tableMsg)
{

    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select * from t_demand_weight where expert_id "
                                "=%1 and engineer_id =%2 and table_msg = "
                                "'%3' and table_index = 0")
                                .arg(QString::number(expertId))
                                .arg(QString::number(engineerId))
                                .arg(tableMsg);
    // qDebug() << "sql=" << selectSql;
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                DemandWeight *demandWeight = new DemandWeight();
                demandWeight->id           = query.value(0).toInt();
                demandWeight->engineerId   = query.value(1).toInt();
                demandWeight->expertId     = query.value(2).toInt();
                demandWeight->nodeName     = query.value(3).toString();
                demandWeight->nodeValue    = query.value(4).toDouble();
                demandWeight->nodeWeight   = query.value(5).toDouble();
                demandWeight->tableIndex   = query.value(6).toInt();
                demandWeightList->append(demandWeight);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool NodeMatrixService::QueryLastDemandWeightByEngineerId(QList<DemandWeight *> *demandWeightList, int expertId,
                                                          int engineerId, QString tableMsg, int tableIndex)
{

    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select * from t_demand_weight where expert_id "
                                "=%1 and engineer_id =%2 and table_msg = "
                                "'%3' and table_index = %4")
                                .arg(QString::number(expertId))
                                .arg(QString::number(engineerId))
                                .arg(tableMsg)
                                .arg(QString::number(tableIndex));
    qDebug() << "sql=" << selectSql;
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                DemandWeight *demandWeight = new DemandWeight();
                demandWeight->id           = query.value(0).toInt();
                demandWeight->engineerId   = query.value(1).toInt();
                demandWeight->expertId     = query.value(2).toInt();
                demandWeight->nodeName     = query.value(3).toString();
                demandWeight->nodeValue    = query.value(4).toDouble();
                demandWeight->nodeWeight   = query.value(5).toDouble();
                demandWeight->tableIndex   = query.value(6).toInt();
                demandWeightList->append(demandWeight);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool NodeMatrixService::QueryFirstDemandWeightByEngineerId(QList<DemandWeight *> *demandWeightList, QString expertId,
                                                           int engineerId, QString tableMsg)
{

    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select * from t_demand_weight where expert_id "
                                "='%1' and engineer_id =%2 and table_msg = "
                                "'%3' and table_index = 0")
                                .arg(expertId)
                                .arg(QString::number(engineerId))
                                .arg(tableMsg);
    // qDebug() << "sql=" << selectSql;
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                DemandWeight *demandWeight = new DemandWeight();
                demandWeight->id           = query.value(0).toInt();
                demandWeight->engineerId   = query.value(1).toInt();
                demandWeight->expertId     = query.value(2).toInt();
                demandWeight->nodeName     = query.value(3).toString();
                demandWeight->nodeValue    = query.value(4).toDouble();
                demandWeight->nodeWeight   = query.value(5).toDouble();
                demandWeight->tableIndex   = query.value(6).toInt();
                demandWeightList->append(demandWeight);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool NodeMatrixService::QueryLastPageDemandWeightByEngineerId(QList<DemandWeight *> *demandWeightList, QString expertId,
                                                              int engineerId, QString tableMsg, int page)
{

    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select * from t_demand_weight where expert_id "
                                "='%1' and engineer_id =%2 and table_msg = "
                                "'%3' and page_index = %4")
                                .arg(expertId)
                                .arg(QString::number(engineerId))
                                .arg(tableMsg)
                                .arg(page);
    // qDebug() << "sql=" << selectSql;
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                DemandWeight *demandWeight = new DemandWeight();
                demandWeight->id           = query.value(0).toInt();
                demandWeight->engineerId   = query.value(1).toInt();
                demandWeight->expertId     = query.value(2).toInt();
                demandWeight->nodeName     = query.value(3).toString();
                demandWeight->nodeValue    = query.value(4).toDouble();
                demandWeight->nodeWeight   = query.value(5).toDouble();
                demandWeight->tableIndex   = query.value(6).toInt();
                demandWeightList->append(demandWeight);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool NodeMatrixService::QuerySecondDemandWeightByEngineerId(QList<DemandWeight *> *demandWeightList, int expertId,
                                                            int engineerId, QString tableMsg)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select * from t_demand_weight where expert_id "
                                "=%1 and engineer_id =%2 and table_msg = "
                                "'%3' and table_index != 0")
                                .arg(QString::number(expertId))
                                .arg(QString::number(engineerId))
                                .arg(tableMsg);
    // qDebug() << "sql=" << selectSql;
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                DemandWeight *demandWeight = new DemandWeight();
                demandWeight->id           = query.value(0).toInt();
                demandWeight->engineerId   = query.value(1).toInt();
                demandWeight->expertId     = query.value(2).toInt();
                demandWeight->nodeName     = query.value(3).toString();
                demandWeight->nodeValue    = query.value(4).toDouble();
                demandWeight->nodeWeight   = query.value(5).toDouble();
                demandWeight->tableIndex   = query.value(6).toInt();
                demandWeightList->append(demandWeight);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool NodeMatrixService::QuerySecondDemandWeightByEngineerIdAndMaxPage(QList<DemandWeight *> *demandWeightList,
                                                                      int expertId, int engineerId, QString tableMsg)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select * from t_demand_weight where expert_id "
                                "=%1 and engineer_id =%2 and table_msg = "
                                "'%3' and page_index = (select max(page_index) from t_demand_weight where expert_id "
                                "=%1 and engineer_id =%2 and table_msg = '%3' ) order by table_index ")
                                .arg(QString::number(expertId))
                                .arg(QString::number(engineerId))
                                .arg(tableMsg);
    // qDebug() << "sql=" << selectSql;
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                DemandWeight *demandWeight = new DemandWeight();
                demandWeight->id           = query.value(0).toInt();
                demandWeight->engineerId   = query.value(1).toInt();
                demandWeight->expertId     = query.value(2).toInt();
                demandWeight->nodeName     = query.value(3).toString();
                demandWeight->nodeValue    = query.value(4).toDouble();
                demandWeight->nodeWeight   = query.value(5).toDouble();
                demandWeight->tableIndex   = query.value(6).toInt();
                demandWeightList->append(demandWeight);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool NodeMatrixService::QuerySecondDemandWeightByEngineerId(QList<DemandWeight *> *demandWeightList, QString expertId,
                                                            int engineerId, QString tableMsg)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select * from t_demand_weight where expert_id "
                                "='%1' and engineer_id =%2 and table_msg = "
                                "'%3' and table_index != 0")
                                .arg(expertId)
                                .arg(QString::number(engineerId))
                                .arg(tableMsg);
    // qDebug() << "sql=" << selectSql;
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                DemandWeight *demandWeight = new DemandWeight();
                demandWeight->id           = query.value(0).toInt();
                demandWeight->engineerId   = query.value(1).toInt();
                demandWeight->expertId     = query.value(2).toInt();
                demandWeight->nodeName     = query.value(3).toString();
                demandWeight->nodeValue    = query.value(4).toDouble();
                demandWeight->nodeWeight   = query.value(5).toDouble();
                demandWeight->tableIndex   = query.value(6).toInt();
                demandWeightList->append(demandWeight);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}