QFD2/QFD/dbService/DBServiceSet.cpp

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

DBServiceSet::DBServiceSet(QObject *parent) { }

//////////////////////common-start////////////////////
int DBServiceSet::getNextId(QString tableName)
{
    int nextId = -1;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        QString selectSql       = QString("SELECT seq from sqlite_sequence  WHERE name = '%1'").arg(tableName);
        QueryResult queryResult = t.execQuery(selectSql);
        if (queryResult.next()) {
            nextId = queryResult.value(0).toInt() + 1;
        }
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return nextId;
}
//////////////////////common-end////////////////////

//////////////////////技术重要度持久化-start////////////////////

bool DBServiceSet::AddTechnicalImportInfo(const TechnicalImport &technicalImport)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        InsertQuery q = t.insertInto("t_technical_import (engineer_id,expert_id,node_name,node_value)");
        q.values(technicalImport.engineerId, technicalImport.expertId, technicalImport.nodeName,
                 technicalImport.nodeValue)
                .exec();
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

/*批量节点信息新增*/
bool DBServiceSet::AddTechnicalImportInfoList(const QList<TechnicalImport *> &technicalImportList)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        for (int i = 0; i < technicalImportList.length(); i++) {
            TechnicalImport *technical = technicalImportList.at(i);
            InsertQuery q = t.insertInto("t_technical_import (engineer_id,expert_id,node_name,node_value)");
            q.values(technical->engineerId, technical->expertId, technical->nodeName, technical->nodeValue).exec();
            t.commit();
        }
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool DBServiceSet::UpdateTechnicalImportInfoList(const QList<TechnicalImport *> technicalImportList)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret = false;
    for (int i = 0; i < technicalImportList.length(); i++) {
        TechnicalImport *demandWeight = technicalImportList.at(i);
        QString updateSql             = QString("UPDATE t_technical_import SET NODE_VALUE ='%1'  "
                                    "WHERE ENGINEER_ID =%2 AND expert_id =%3"
                                    " AND node_name = '%4' ")
                                    .arg(demandWeight->nodeValue)
                                    .arg(demandWeight->engineerId)
                                    .arg(demandWeight->expertId)
                                    .arg(demandWeight->nodeName);
        // qDebug() << updateSql;
        query.exec(updateSql);
        ret = true;
    }

    return ret;
}

bool DBServiceSet::QueryTechnicalImportInfoByEngineerId(QList<TechnicalImport *> *demandWeightList, int expertId,
                                                        int engineerId)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select * from t_technical_import where "
                                "expert_id =%1 and engineer_id =%2 ")
                                .arg(QString::number(expertId))
                                .arg(QString::number(engineerId));
    // qDebug() << "sql=" << selectSql;
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                TechnicalImport *demandWeight = new TechnicalImport();
                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();
                demandWeightList->append(demandWeight);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool DBServiceSet::QueryTechnicalImportInfoByEngineerId(int expertId, int engineerId)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("select * from t_technical_import where "
                                "expert_id =%1 and engineer_id =%2 ")
                                .arg(QString::number(expertId))
                                .arg(QString::number(engineerId));
    // qDebug() << "sql=" << selectSql;
    if (query.exec(selectSql)) {
        if (query.next()) {

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

//////////////////////common-end////////////////////

//////////////////////节点矩阵信息表-start////////////////////

//////////////////////节点矩阵信息表-end////////////////////

//////////////////////评估方案信息表-start////////////////////
bool DBServiceSet::AddPlanInfo(const PlanInfo &planInfo)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        InsertQuery q = t.insertInto("t_plan_info (plan_name, engineer_id, desc,create_time)");
        q.values(planInfo.planName, planInfo.engineerId, planInfo.desc,
                 QDateTime::currentDateTime().toString("yyyy.MM.dd hh:mm:ss.zzz"))
                .exec();
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool DBServiceSet::UpdatePlanInfo(const PlanInfo &planInfo)
{

    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.update("t_plan_info")
                .set("plan_name", planInfo.planName)
                .set("engineer_id", planInfo.engineerId)
                .set("desc", planInfo.desc)
                .set("update_time", QDateTime::currentDateTime().toString("yyyy.MM.dd hh:mm:ss.zzz"))
                .where("id=?", planInfo.id);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}
bool DBServiceSet::QueryPlanInfoById(PlanInfo *planInfo, int planId)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        QString selectSql = QString("SELECT id, plan_name,engineer_id, desc from t_plan_info "
                                    " WHERE id = %1")
                                    .arg(planId);
        QueryResult queryResult = t.execQuery(selectSql);
        if (queryResult.next()) {
            planInfo->id         = queryResult.value(0).toInt();
            planInfo->planName   = queryResult.value(1).toString();
            planInfo->engineerId = queryResult.value(2).toInt();
            planInfo->desc       = queryResult.value(3).toString();
            ret                  = true;
        }
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}
bool DBServiceSet::QueryPlanListByColumnAndColumnValue(QList<PlanInfo *> *planInfoList, QString columnName,
                                                       QString columnValue)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        QString selectSql = QString("SELECT id, plan_name,engineer_id, desc from t_plan_info "
                                    " WHERE %1 = '%2'")
                                    .arg(columnName)
                                    .arg(columnValue);
        QueryResult queryResult = t.execQuery(selectSql);
        while (queryResult.next()) {
            PlanInfo *planInfo   = new PlanInfo();
            planInfo->id         = queryResult.value(0).toInt();
            planInfo->planName   = queryResult.value(1).toString();
            planInfo->engineerId = queryResult.value(2).toInt();
            planInfo->desc       = queryResult.value(3).toString();
            planInfoList->append(planInfo);
            ret = true;
        }
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool DBServiceSet::QueryPlanList(QList<PlanInfo *> *planInfoList)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        QString selectSql       = QString("SELECT id, plan_name,engineer_id, desc from t_plan_info ");
        QueryResult queryResult = t.execQuery(selectSql);
        while (queryResult.next()) {
            PlanInfo *planInfo   = new PlanInfo();
            planInfo->id         = queryResult.value(0).toInt();
            planInfo->planName   = queryResult.value(1).toString();
            planInfo->engineerId = queryResult.value(2).toInt();
            planInfo->desc       = queryResult.value(3).toString();
            planInfoList->append(planInfo);
            ret = true;
        }
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool DBServiceSet::DeletePlanById(int planId)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString deleteSql = QString("DELETE FROM t_plan_info WHERE id = %1").arg(planId);
    if (query.exec(deleteSql)) {
        ret = true;
        qDebug() << "deleteSql success!";
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}
//////////////////////评估方案信息表-end////////////////////
//////////////////////指标体系评估方案关系信息表-start////////////////////

/*批量信息新增*/
bool DBServiceSet::AddIndexSetPlanInfoList(const QList<IndexSetPlanInfo *> planInfoList)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        QString insertTime = QDateTime::currentDateTime().toString("yyyy.MM.dd hh:mm:ss.zzz");

        for (int i = 0; i < planInfoList.length(); i++) {
            IndexSetPlanInfo *indexSetPlanInfo = planInfoList.at(i);
            InsertQuery query = t.insertInto("t_plan_index (index_set_id, plan_id, weight, create_time)");
            query.values(indexSetPlanInfo->indexSetId, indexSetPlanInfo->planId, indexSetPlanInfo->weight, insertTime)
                    .exec();
        }
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool DBServiceSet::UpdateIndexSetPlanInfo(const IndexSetPlanInfo &planInfo)
{

    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.update("t_plan_index")
                .set("weight", planInfo.weight)
                .set("update_time", QDateTime::currentDateTime().toString("yyyy.MM.dd hh:mm:ss.zzz"))
                .where("index_set_id =? and plan_id=?", planInfo.indexSetId, planInfo.planId);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool DBServiceSet::UpdateIndexSetPlanInfoList(const QList<IndexSetPlanInfo *> planInfoList)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        QString updateTime = QDateTime::currentDateTime().toString("yyyy.MM.dd hh:mm:ss.zzz");
        for (int i = 0; i < planInfoList.length(); i++) {
            IndexSetPlanInfo *indexSetPlanInfo = planInfoList.at(i);
            t.update("t_plan_index")
                    .set("weight", indexSetPlanInfo->weight)
                    .set("update_time", updateTime)
                    .where("index_set_id =? and plan_id=?", indexSetPlanInfo->indexSetId, indexSetPlanInfo->planId);
        }
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}
bool DBServiceSet::DeleteIndexSetPlanById(int Id)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString deleteSql = QString("DELETE FROM t_plan_index WHERE id = %1").arg(Id);
    if (query.exec(deleteSql)) {
        ret = true;
        qDebug() << "deleteSql success!";
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}
bool DBServiceSet::DeleteIndexSetPlanByPlanId(int planId)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString deleteSql = QString("DELETE FROM t_plan_index WHERE plan_id = %1").arg(planId);
    if (query.exec(deleteSql)) {
        ret = true;
        qDebug() << "deleteSql success!";
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}
bool DBServiceSet::DeleteIndexSetPlanByIndexSetId(int indexSetId)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString deleteSql = QString("DELETE FROM t_plan_index WHERE index_set_id = %1").arg(indexSetId);
    if (query.exec(deleteSql)) {
        ret = true;
        qDebug() << "deleteSql success!";
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool DBServiceSet::QueryIndexSetPlanInfo(IndexSetPlanInfo *planInfo, int Id)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        QString selectSql = QString("SELECT id, plan_id,index_set_id,weight from t_plan_index "
                                    " WHERE id = %1")
                                    .arg(Id);
        QueryResult queryResult = t.execQuery(selectSql);
        if (queryResult.next()) {
            planInfo->id         = queryResult.value(0).toInt();
            planInfo->planId     = queryResult.value(1).toInt();
            planInfo->indexSetId = queryResult.value(2).toInt();
            planInfo->weight     = queryResult.value(3).toDouble();
            ret                  = true;
        }
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool DBServiceSet::QueryIndexSetPlanListByColumnAndColumnValue(QList<IndexSetPlanInfo *> *planInfoList,
                                                               QString columnName, QString columnValue)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        QString selectSql = QString("SELECT id, plan_id,index_set_id,weight from "
                                    "t_plan_index where %1 = '%2' ")
                                    .arg(columnName)
                                    .arg(columnValue);
        QueryResult queryResult = t.execQuery(selectSql);
        while (queryResult.next()) {
            IndexSetPlanInfo *planInfo = new IndexSetPlanInfo();
            planInfo->id               = queryResult.value(0).toInt();
            planInfo->planId           = queryResult.value(1).toInt();
            planInfo->indexSetId       = queryResult.value(2).toInt();
            planInfo->weight           = queryResult.value(3).toDouble();
            planInfoList->append(planInfo);
            ret = true;
        }
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

//////////////////////指标体系评估方案关系信息表-end////////////////////