QFD2/QFD/dbService/CNodeDataService.cpp

#include "CNodeDataService.h"
#include "SqlDBHelper.h"
#include <QDebug>
CNodeDataService::CNodeDataService(QObject *parent) { }
//新增节点
int CNodeDataService::AddCNodeData(const CNodeData &cNodeData)
{
    int ret = -1;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        InsertQuery query = t.insertInto("t_node_data(`project_id`, `eval_type`, `number`, `p_number`, `name`, "
                                         "`remark`, `create_time`, `dimension`, `type`, `is_effective`)");

        NonQueryResult result = query.values(cNodeData.projectId, cNodeData.indexType, cNodeData.number,
                                             cNodeData.pNumber, cNodeData.name, cNodeData.remark,
                                             QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss"),
                                             cNodeData.dimension, cNodeData.type, cNodeData.isEffective)
                                        .exec();
        t.commit();
        ret = result.lastInsertId().toInt();
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool CNodeDataService::SaveCNodeData(const CNodeData &cNodeData)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        InsertQuery query = t.insertInto("t_node_data(`id`, `project_id`, `eval_type`, `number`, `p_number`, `name`, "
                                         "`remark`,  `dimension`, `type`, `is_effective`)");

        NonQueryResult result = query.values(cNodeData.id, cNodeData.projectId, cNodeData.indexType, cNodeData.number,
                                             cNodeData.pNumber, cNodeData.name, cNodeData.remark, cNodeData.dimension,
                                             cNodeData.type, cNodeData.isEffective)
                                        .exec();
        // qDebug() << "SaveCNodeData" << result.lastQuery();
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << "SaveCNodeData" << ex.lastError.text();
    }
    return ret;
}

//更新节点信息
bool CNodeDataService::UpdateCNodeData(const CNodeData &cNodeData)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.update("t_node_data")
                .set("project_id", cNodeData.projectId)
                .set("eval_type", cNodeData.indexType)
                .set("number", cNodeData.number)
                .set("p_number", cNodeData.pNumber)
                .set("name", cNodeData.name)
                .set("remark", cNodeData.remark)
                .set("dimension", cNodeData.dimension)
                .set("type", cNodeData.type)
                .set("is_effective", cNodeData.isEffective)
                .set("update_time", QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss"))
                .where("id = ?", cNodeData.id);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

/*根据项目id查询节点信息*/
bool CNodeDataService::QueryCNodeDataById(CNodeData &cNodeData, int id)
{

    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret = false;
    QString selectSql =
            QString("SELECT id,project_id, eval_type,number,p_number,name,remark,dimension,type,is_effective FROM "
                    "t_node_data WHERE id = %1")
                    .arg(id);
    if (query.exec(selectSql)) {
        if (query.next()) {
            if (query.isNull(0) == false) {
                cNodeData.id          = query.value(0).toInt();
                cNodeData.projectId   = query.value(1).toInt();
                cNodeData.indexType   = query.value(1).toInt();
                cNodeData.number      = query.value(2).toInt();
                cNodeData.pNumber     = query.value(3).toInt();
                cNodeData.name        = query.value(4).toString();
                cNodeData.remark      = query.value(5).toString();
                cNodeData.dimension   = query.value(6).toString();
                cNodeData.type        = query.value(7).toInt();
                cNodeData.isEffective = query.value(8).toInt();
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

//查询所有
bool CNodeDataService::QueryAll(QList<CNodeData> &cNodeDataList, int projectId, int evalType)
{

    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret = false;
    QString selectSql =
            QString("SELECT id,project_id, eval_type,number,p_number,name,remark,dimension,type,is_effective FROM "
                    "t_node_data WHERE "
                    "project_id = %1 and eval_type= %2 order by number")
                    .arg(projectId)
                    .arg(evalType);
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                CNodeData cNodeData;
                cNodeData.id          = query.value(0).toInt();
                cNodeData.projectId   = query.value(1).toInt();
                cNodeData.indexType   = query.value(2).toInt();
                cNodeData.number      = query.value(3).toInt();
                cNodeData.pNumber     = query.value(4).toInt();
                cNodeData.name        = query.value(5).toString();
                cNodeData.remark      = query.value(6).toString();
                cNodeData.dimension   = query.value(7).toString();
                cNodeData.type        = query.value(8).toInt();
                cNodeData.isEffective = query.value(9).toInt();
                cNodeDataList.append(cNodeData);
            }
        }
        ret = true;
    } else {
        qDebug() << "QueryAll" << query.lastError();
    }
    return ret;
}

bool CNodeDataService::QueryAllValid(QList<CNodeData> &cNodeDataList, int projectId, int evalType)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret = false;
    QString selectSql =
            QString("SELECT id,project_id, eval_type,number,p_number,name,remark,dimension,type,is_effective FROM "
                    "t_node_data WHERE "
                    "project_id = %1 and eval_type= %2 order by number")
                    .arg(projectId)
                    .arg(evalType);

    QList<CNodeData> tempList;
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                CNodeData cNodeData;
                cNodeData.id          = query.value(0).toInt();
                cNodeData.projectId   = query.value(1).toInt();
                cNodeData.indexType   = query.value(2).toInt();
                cNodeData.number      = query.value(3).toInt();
                cNodeData.pNumber     = query.value(4).toInt();
                cNodeData.name        = query.value(5).toString();
                cNodeData.remark      = query.value(6).toString();
                cNodeData.dimension   = query.value(7).toString();
                cNodeData.type        = query.value(8).toInt();
                cNodeData.isEffective = query.value(9).toInt();

                tempList.append(cNodeData);
            }
        }
        ret = true;

        CMind mind;
        mind.setNodeList(tempList);

        for (CNodeData node : tempList) {
            if (mind.isInvalidated(node.number) == false) {
                cNodeDataList.append(node);
            }
        }
    } else {
        qDebug() << "QueryAllValid" << query.lastError();
    }
    return ret;
}

bool CNodeDataService::QueryAllByProjectId(QList<CNodeData> &cNodeDataList, int projectId)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret = false;
    QString selectSql =
            QString("SELECT id,project_id, eval_type,number,p_number,name,remark,dimension,type,is_effective FROM "
                    "t_node_data WHERE "
                    "project_id = %1 ")
                    .arg(projectId);
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                CNodeData cNodeData;
                cNodeData.id          = query.value(0).toInt();
                cNodeData.projectId   = query.value(1).toInt();
                cNodeData.indexType   = query.value(2).toInt();
                cNodeData.number      = query.value(3).toInt();
                cNodeData.pNumber     = query.value(4).toInt();
                cNodeData.name        = query.value(5).toString();
                cNodeData.remark      = query.value(6).toString();
                cNodeData.dimension   = query.value(7).toString();
                cNodeData.type        = query.value(8).toInt();
                cNodeData.isEffective = query.value(9).toInt();
                cNodeDataList.append(cNodeData);
            }
        }
        ret = true;
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

//根据id删除
bool CNodeDataService::DeleteCNodeDataById(int id)
{
    bool ret = false;
    try {
        qDebug() << __FUNCTION__ << __LINE__
                 << SqlDBHelper::getDatabase().driver()->hasFeature(QSqlDriver::Transactions) << endl;
        Transaction t(SqlDBHelper::getDatabase());
        t.deleteFrom("t_node_data").where("id = ?", id);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

//根据id删除
bool CNodeDataService::DeleteAllNodeData()
{

    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString deleteSql = QString("DELETE FROM t_node_data ");
    if (query.exec(deleteSql)) {
        ret = true;
        qDebug() << "t_node_data deleteSql success!";
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}