QFD2/QFD/dbService/NodeMatrixService.cpp

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

NodeMatrixService::NodeMatrixService(QObject *parent) { }
bool NodeMatrixService::AddNodeMatrixInfo(const NodeMatrixInfo &nodeMatrixInfo)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        InsertQuery q = t.insertInto("t_node_matrix_info (expert_name, engineer_id, node, "
                                     "abscissa, ordinate, "
                                     "node_value, expert_id,mind_name,write_date,mark)");
        q.values(nodeMatrixInfo.expertName, nodeMatrixInfo.engineerId, nodeMatrixInfo.node, nodeMatrixInfo.abscissa,
                 nodeMatrixInfo.ordinate, nodeMatrixInfo.nodeValue, nodeMatrixInfo.expertId, nodeMatrixInfo.mindId,
                 nodeMatrixInfo.writeDate.toString(), nodeMatrixInfo.mark)
                .exec();
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

/*批量节点信息新增*/
bool NodeMatrixService::AddNodeMatrixInfoList(const QList<NodeMatrixInfo *> &jbInfoList)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        for (int i = 0; i < jbInfoList.length(); i++) {
            NodeMatrixInfo *nodeMatrixInfo = jbInfoList.at(i);
            InsertQuery query = t.insertInto("t_node_matrix_info (expert_name, engineer_id, node, abscissa, "
                                             "ordinate, "
                                             "node_value, expert_id,mind_id,write_date,mark,table_msg)");
            query.values(nodeMatrixInfo->expertName, nodeMatrixInfo->engineerId, nodeMatrixInfo->node,
                         nodeMatrixInfo->abscissa, nodeMatrixInfo->ordinate, nodeMatrixInfo->nodeValue,
                         nodeMatrixInfo->expertId, nodeMatrixInfo->mindId, nodeMatrixInfo->writeDate.toString(),
                         nodeMatrixInfo->mark, nodeMatrixInfo->tableMsg)
                    .exec();
            t.commit();
        }

        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

/*批量节点信息新增----专家端*/
bool NodeMatrixService::AddNodeMatrixInfoList2(const QList<NodeMatrixInfo *> &jbInfoList)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase2());
        for (int i = 0; i < jbInfoList.length(); i++) {
            NodeMatrixInfo *nodeMatrixInfo = jbInfoList.at(i);
            InsertQuery query = t.insertInto("t_node_matrix_info (expert_name, engineer_id, node, abscissa, "
                                             "ordinate, "
                                             "node_value, expert_id,mind_id,write_date,mark,table_msg,tab_index)");
            query.values(nodeMatrixInfo->expertName, nodeMatrixInfo->engineerId, nodeMatrixInfo->node,
                         nodeMatrixInfo->abscissa, nodeMatrixInfo->ordinate, nodeMatrixInfo->nodeValue,
                         nodeMatrixInfo->expertId, nodeMatrixInfo->mindId, nodeMatrixInfo->writeDate.toString(),
                         nodeMatrixInfo->mark, nodeMatrixInfo->tableMsg, nodeMatrixInfo->tabIndex)
                    .exec();
            t.commit();
        }

        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

/*修改节点值*/
bool NodeMatrixService::UpdateNodeMatrixNodeValue(const NodeMatrixInfo &nodeMatrixInfo)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.update("t_node_matrix_info")
                .set("NODE_VALUE", nodeMatrixInfo.nodeValue)
                .where("ENGINEER_ID = ? and expert_name = and abscissa = ? and "
                       "ordinate = ?  ",
                       nodeMatrixInfo.engineerId, nodeMatrixInfo.expertName, nodeMatrixInfo.abscissa,
                       nodeMatrixInfo.ordinate);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool NodeMatrixService::UpdateNodeMatrixNodeValueList(const QList<NodeMatrixInfo *> jbInfoList)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret = false;
    for (int i = 0; i < jbInfoList.length(); i++) {
        NodeMatrixInfo *nodeMatrixInfo = jbInfoList.at(i);
        QString updateSql              = QString("UPDATE t_node_matrix_info SET NODE_VALUE ='%1' WHERE "
                                    "ENGINEER_ID = %2 AND expert_name = '%3'"
                                    " AND abscissa = '%4' AND ordinate = '%5' and table_msg = '%6' "
                                    "and mark = '%7'")
                                    .arg(nodeMatrixInfo->nodeValue)
                                    .arg(nodeMatrixInfo->engineerId)
                                    .arg(nodeMatrixInfo->expertName)
                                    .arg(nodeMatrixInfo->abscissa)
                                    .arg(nodeMatrixInfo->ordinate)
                                    .arg(nodeMatrixInfo->tableMsg)
                                    .arg(nodeMatrixInfo->mark);
        // qDebug() << updateSql;
        query.exec(updateSql);
        ret = true;
    }

    return ret;
}

bool NodeMatrixService::UpdateNodeMatrixNodeValueList2(const QList<NodeMatrixInfo *> jbInfoList)
{
    QSqlDatabase db = SqlDBHelper::getDatabase2();
    QSqlQuery query(db);
    bool ret = false;
    for (int i = 0; i < jbInfoList.length(); i++) {
        NodeMatrixInfo *nodeMatrixInfo = jbInfoList.at(i);
        QString updateSql              = QString("UPDATE t_node_matrix_info SET NODE_VALUE ='%1' WHERE "
                                    "ENGINEER_ID = %2 AND expert_name = '%3'"
                                    " AND abscissa = '%4' AND ordinate = '%5' and table_msg = '%6' "
                                    "and mark = '%7' and tab_index = '%8'")
                                    .arg(nodeMatrixInfo->nodeValue)
                                    .arg(nodeMatrixInfo->engineerId)
                                    .arg(nodeMatrixInfo->expertName)
                                    .arg(nodeMatrixInfo->abscissa)
                                    .arg(nodeMatrixInfo->ordinate)
                                    .arg(nodeMatrixInfo->tableMsg)
                                    .arg(nodeMatrixInfo->mark)
                                    .arg(nodeMatrixInfo->tabIndex);
        // qDebug() << updateSql;
        query.exec(updateSql);
        ret = true;
    }

    return ret;
}

/*根据专家姓名和工程id获取节点信息*/
bool NodeMatrixService::QueryNodeMatrixListByExpertNameAndEngineerId(QString expertName, int engineerId,
                                                                     QString tableMsg, QString mark)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select id,expert_name, engineer_id, node, abscissa, ordinate, "
                                "node_value, expert_id,mind_id,write_date from "
                                "t_node_matrix_info where expert_name "
                                "= '%1' and engineer_id ='%2' and table_msg='%3' and mark = '%4'")
                                .arg(expertName)
                                .arg(QString::number(engineerId))
                                .arg(tableMsg)
                                .arg(mark);

    if (query.exec(selectSql)) {
        if (query.next()) {
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    qDebug() << ret;
    return ret;
}

/*根据专家姓名和工程id获取节点信息*/
bool NodeMatrixService::QueryNodeMatrixListByExpertIdAndEngineerId(QString expertId, int engineerId, QString tableMsg)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select id,expert_name, engineer_id, node, abscissa, ordinate, "
                                "node_value, expert_id,mind_id,write_date from "
                                "t_node_matrix_info where expert_id "
                                "= '%1' and engineer_id ='%2' and table_msg='%3'")
                                .arg(expertId)
                                .arg(QString::number(engineerId))
                                .arg(tableMsg);
    // qDebug() << selectSql;

    if (query.exec(selectSql)) {
        if (query.next()) {
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    // qDebug() << ret;
    return ret;
}

bool NodeMatrixService::QueryNodeMatrixListByExpertNameAndEngineerId(QList<NodeMatrixInfo *> *nodeMatrixInfoList,
                                                                     QString expertName, int engineerId)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select id,expert_name, engineer_id, node, abscissa, ordinate, "
                                "node_value, expert_id,mind_id,write_date from "
                                "t_node_matrix_info where expert_name "
                                "= '%1' and engineer_id ='%2'")
                                .arg(expertName)
                                .arg(QString::number(engineerId));
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                NodeMatrixInfo *nodeMatrixInfo = new NodeMatrixInfo();
                nodeMatrixInfo->id             = query.value(0).toInt();
                nodeMatrixInfo->expertName     = query.value(1).toString();
                nodeMatrixInfo->node           = query.value(2).toString();
                nodeMatrixInfo->engineerId     = query.value(3).toInt();
                nodeMatrixInfo->abscissa       = query.value(4).toString();
                nodeMatrixInfo->ordinate       = query.value(5).toString();
                nodeMatrixInfo->nodeValue      = query.value(6).toString();
                nodeMatrixInfo->expertId       = query.value(7).toInt();
                nodeMatrixInfo->mindId         = query.value(8).toInt();
                nodeMatrixInfo->writeDate      = query.value(9).toDateTime();
                nodeMatrixInfo->mark           = query.value(10).toString();
                nodeMatrixInfoList->append(nodeMatrixInfo);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool NodeMatrixService::QueryNodeMatrixListByExpertNameAndEngineerId2(QString expertName, int engineerId,
                                                                      QString tableMsg, QString mark, int tabIndex)
{
    QSqlDatabase db = SqlDBHelper::getDatabase2();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select id,expert_name, engineer_id, node, abscissa, ordinate, "
                                "node_value, expert_id,mind_id,write_date from "
                                "t_node_matrix_info where expert_name "
                                "= '%1' and engineer_id ='%2' and table_msg='%3' and mark = '%4' and tab_index = '%5'")
                                .arg(expertName)
                                .arg(QString::number(engineerId))
                                .arg(tableMsg)
                                .arg(mark)
                                .arg(tabIndex);

    if (query.exec(selectSql)) {
        if (query.next()) {
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    qDebug() << ret;
    return ret;
}

bool NodeMatrixService::QueryNodesByExpertNameAndEngineerId2(QList<NodeMatrixInfo *> *nodeMatrixInfoList,
                                                             QString expertName, int engineerId, QString tableMsg,
                                                             QString mark, int tabIndex)
{
    QSqlDatabase db = SqlDBHelper::getDatabase2();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select id,expert_name, engineer_id, node, abscissa, ordinate, "
                                "node_value, expert_id,mind_id,write_date from "
                                "t_node_matrix_info where expert_name "
                                "= '%1' and engineer_id ='%2' and table_msg='%3' and mark = '%4' and tab_index = '%5'")
                                .arg(expertName)
                                .arg(QString::number(engineerId))
                                .arg(tableMsg)
                                .arg(mark)
                                .arg(tabIndex);
    // qDebug() << "----" << selectSql;
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                NodeMatrixInfo *nodeMatrixInfo = new NodeMatrixInfo();
                nodeMatrixInfo->id             = query.value(0).toInt();
                nodeMatrixInfo->expertName     = query.value(1).toString();
                nodeMatrixInfo->node           = query.value(2).toString();
                nodeMatrixInfo->engineerId     = query.value(3).toInt();
                nodeMatrixInfo->abscissa       = query.value(4).toString();
                nodeMatrixInfo->ordinate       = query.value(5).toString();
                nodeMatrixInfo->nodeValue      = query.value(6).toString();
                nodeMatrixInfo->expertId       = query.value(7).toInt();
                nodeMatrixInfo->mindId         = query.value(8).toInt();
                nodeMatrixInfo->writeDate      = query.value(9).toDateTime();
                nodeMatrixInfo->mark           = query.value(10).toString();
                nodeMatrixInfoList->append(nodeMatrixInfo);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    qDebug() << ret;
    return ret;
}

bool NodeMatrixService::QueryNodeValueByUserIdAndEngineerId(int experId, int engineerId)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select id,expert_name, engineer_id, node, abscissa, ordinate, "
                                "node_value, expert_id,mind_id,write_date from "
                                "t_node_matrix_info where expert_id "
                                "= '%1' and engineer_id ='%2'")
                                .arg(QString::number(experId))
                                .arg(QString::number(engineerId));
    // qDebug() << "selectSql=" << selectSql;
    if (query.exec(selectSql)) {
        if (query.next()) {

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

bool NodeMatrixService::QueryNodeMatrixListByExpertIdAndEngineerId(QList<NodeMatrixInfo *> *nodeMatrixInfoList,
                                                                   int expertId, int engineerId, QString tableMsg)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select id,expert_name, engineer_id, node, abscissa, ordinate, "
                                "node_value, expert_id,mind_id,write_date from "
                                "t_node_matrix_info where expert_id "
                                "= '%1' and engineer_id ='%2' and table_msg ='%3'")
                                .arg(QString::number(expertId))
                                .arg(QString::number(engineerId))
                                .arg(tableMsg);
    // qDebug() << "selectSql=" << selectSql;
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                NodeMatrixInfo *nodeMatrixInfo = new NodeMatrixInfo();
                nodeMatrixInfo->id             = query.value(0).toInt();
                nodeMatrixInfo->expertName     = query.value(1).toString();
                nodeMatrixInfo->node           = query.value(2).toString();
                nodeMatrixInfo->engineerId     = query.value(3).toInt();
                nodeMatrixInfo->abscissa       = query.value(4).toString();
                nodeMatrixInfo->ordinate       = query.value(5).toString();
                nodeMatrixInfo->nodeValue      = query.value(6).toString();
                nodeMatrixInfo->expertId       = query.value(7).toInt();
                nodeMatrixInfo->mindId         = query.value(8).toInt();
                nodeMatrixInfo->writeDate      = query.value(9).toDateTime();
                nodeMatrixInfo->mark           = query.value(10).toString();
                nodeMatrixInfoList->append(nodeMatrixInfo);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool NodeMatrixService::QueryNodeMatrixListByExpertIdAndEngineerId2(QList<NodeMatrixInfo *> *nodeMatrixInfoList,
                                                                    int expertId, int engineerId, QString tableMsg)
{
    QSqlDatabase db = SqlDBHelper::getDatabase2();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select id,expert_name, engineer_id, node, abscissa, ordinate, "
                                "node_value, expert_id,mind_id,write_date from "
                                "t_node_matrix_info where expert_id "
                                "= '%1' and engineer_id ='%2' and table_msg ='%3'")
                                .arg(QString::number(expertId))
                                .arg(QString::number(engineerId))
                                .arg(tableMsg);
    // qDebug() << "selectSql=" << selectSql;
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                NodeMatrixInfo *nodeMatrixInfo = new NodeMatrixInfo();
                nodeMatrixInfo->id             = query.value(0).toInt();
                nodeMatrixInfo->expertName     = query.value(1).toString();
                nodeMatrixInfo->node           = query.value(2).toString();
                nodeMatrixInfo->engineerId     = query.value(3).toInt();
                nodeMatrixInfo->abscissa       = query.value(4).toString();
                nodeMatrixInfo->ordinate       = query.value(5).toString();
                nodeMatrixInfo->nodeValue      = query.value(6).toString();
                nodeMatrixInfo->expertId       = query.value(7).toInt();
                nodeMatrixInfo->mindId         = query.value(8).toInt();
                nodeMatrixInfo->writeDate      = query.value(9).toDateTime();
                nodeMatrixInfo->mark           = query.value(10).toString();
                nodeMatrixInfoList->append(nodeMatrixInfo);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

/*根据专家姓名查询对应的节点信息*/
bool NodeMatrixService::QueryNodeMatrixListByExpertName(QList<NodeMatrixInfo *> *nodeMatrixInfoList, QString expertName)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select id,expert_name, engineer_id, node, abscissa, ordinate, "
                                "node_value, expert_id,mind_name,write_date from "
                                "t_node_matrix_info where expert_name = '%1'")
                                .arg(expertName);
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                NodeMatrixInfo *nodeMatrixInfo = new NodeMatrixInfo();
                nodeMatrixInfo->id             = query.value(0).toInt();
                nodeMatrixInfo->expertName     = query.value(1).toString();
                nodeMatrixInfo->node           = query.value(2).toString();
                nodeMatrixInfo->engineerId     = query.value(3).toInt();
                nodeMatrixInfo->abscissa       = query.value(4).toString();
                nodeMatrixInfo->ordinate       = query.value(5).toString();
                nodeMatrixInfo->nodeValue      = query.value(6).toString();
                nodeMatrixInfo->expertId       = query.value(7).toInt();
                nodeMatrixInfo->mindId         = query.value(8).toInt();
                nodeMatrixInfo->writeDate      = query.value(9).toDateTime();
                nodeMatrixInfo->mark           = query.value(10).toString();
                nodeMatrixInfoList->append(nodeMatrixInfo);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

/*根据专家姓名编号对应的节点信息*/
bool NodeMatrixService::QueryNodeMatrixListByExpertId(QList<NodeMatrixInfo *> *nodeMatrixInfoList, int expertId)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select id,expert_name, engineer_id, node, abscissa, ordinate, "
                                "node_value, expert_id,mind_name,write_date from "
                                "t_node_matrix_info where expert_id = '%1'")
                                .arg(expertId);
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                NodeMatrixInfo *nodeMatrixInfo = new NodeMatrixInfo();
                nodeMatrixInfo->id             = query.value(0).toInt();
                nodeMatrixInfo->expertName     = query.value(1).toString();
                nodeMatrixInfo->node           = query.value(2).toString();
                nodeMatrixInfo->engineerId     = query.value(3).toInt();
                nodeMatrixInfo->abscissa       = query.value(4).toString();
                nodeMatrixInfo->ordinate       = query.value(5).toString();
                nodeMatrixInfo->nodeValue      = query.value(6).toString();
                nodeMatrixInfo->expertId       = query.value(7).toInt();
                nodeMatrixInfo->mindId         = query.value(8).toInt();
                nodeMatrixInfo->writeDate      = query.value(9).toDateTime();
                nodeMatrixInfo->mark           = query.value(10).toString();
                nodeMatrixInfoList->append(nodeMatrixInfo);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}
/*根据工程编号查询对应的节点信息*/
bool NodeMatrixService::QueryNodeMatrixListByEngineerId(QList<NodeMatrixInfo *> *nodeMatrixInfoList, int engineerId)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select id,expert_name, engineer_id, node, abscissa, ordinate, "
                                "node_value, expert_id ,mind_name,write_date from "
                                "t_node_matrix_info where engineer_id = '%1'")
                                .arg(engineerId);
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                NodeMatrixInfo *nodeMatrixInfo = new NodeMatrixInfo();
                nodeMatrixInfo->id             = query.value(0).toInt();
                nodeMatrixInfo->expertName     = query.value(1).toString();
                nodeMatrixInfo->node           = query.value(2).toString();
                nodeMatrixInfo->engineerId     = query.value(3).toInt();
                nodeMatrixInfo->abscissa       = query.value(4).toString();
                nodeMatrixInfo->ordinate       = query.value(5).toString();
                nodeMatrixInfo->nodeValue      = query.value(6).toString();
                nodeMatrixInfo->expertId       = query.value(7).toInt();
                nodeMatrixInfo->mindId         = query.value(8).toInt();
                nodeMatrixInfo->writeDate      = query.value(9).toDateTime();
                nodeMatrixInfo->mark           = query.value(10).toString();
                nodeMatrixInfoList->append(nodeMatrixInfo);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

/*根据工程编号删除对应的节点信息*/
bool NodeMatrixService::DeleteNodeMatrixListByEngineerId(int engineerId)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.deleteFrom("t_node_matrix_info").where("engineer_id = ?", engineerId);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}
/*根据专家名称删除对应的节点信息*/
bool NodeMatrixService::DeleteNodeMatrixListByExpertName(QString expertName)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.deleteFrom("t_node_matrix_info").where("expert_name = ?", expertName);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}
/*根据专家编号删除对应的节点信息*/
bool NodeMatrixService::DeleteNodeMatrixListByExpertId(int expertId)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.deleteFrom("t_node_matrix_info").where("expert_id = ?", expertId);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}