QFD2/QFD/dbService/ProjectService.cpp

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

ProjectService::ProjectService(QObject *parent) { }

//新增项目
int ProjectService::AddProjectInfo(const ProjectInfo &projectInfo)
{
    int ret = -1;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        InsertQuery query = t.insertInto(
                "t_project_info(`project_name`, `remark`, `task_name`, `estimate_time`, `estimate_objective`, "
                "`estimate_dept`, `estimate_person`,`estimate_type`, `positional_titles`, `create_time`)");

        NonQueryResult result =
                query.values(projectInfo.projectName, projectInfo.remark, projectInfo.taskName,
                             projectInfo.estimateTime, projectInfo.estimateObjective, projectInfo.estimateDept,
                             projectInfo.estimatePerson, projectInfo.estimateType, projectInfo.positionalTitles,
                             QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss"))
                        .exec();
        t.commit();
        ret = result.lastInsertId().toInt();
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

//保存项目
bool ProjectService::SaveProjectInfo(const ProjectInfo &projectInfo)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        InsertQuery query = t.insertInto(
                "t_project_info(`id`,`project_name`, `remark`, `task_name`, `estimate_time`, `estimate_objective`, "
                "`estimate_dept`, `estimate_person`,`estimate_type`, `positional_titles`, `create_time`)");

        NonQueryResult result =
                query.values(projectInfo.id, projectInfo.projectName, projectInfo.remark, projectInfo.taskName,
                             projectInfo.estimateTime, projectInfo.estimateObjective, projectInfo.estimateDept,
                             projectInfo.estimatePerson, projectInfo.estimateType, projectInfo.positionalTitles,
                             projectInfo.createTime)
                        .exec();
        // qDebug() << "------------------" << result.lastQuery();

        t.commit();

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

//更新项目信息
bool ProjectService::UpdateProjectInfo(const ProjectInfo &proInfo)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.update("t_project_info")
                .set("project_name", proInfo.projectName)
                .set("remark", proInfo.remark)
                .set("task_name", proInfo.taskName)
                .set("estimate_time", proInfo.estimateTime)
                .set("estimate_objective", proInfo.estimateObjective)
                .set("estimate_dept", proInfo.estimateDept)
                .set("estimate_person", proInfo.estimatePerson)
                .set("estimate_type", proInfo.estimateType)
                .set("positional_titles", proInfo.positionalTitles)
                .set("update_time", proInfo.updateTime)
                .where("id = ?", proInfo.id);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}
//更新脑图ID
bool ProjectService::UpdateDemandById(int enjId, int demandId)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.update("t_project_info").set("demand_mind_id", demandId).where("id = ?", enjId);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}
bool ProjectService::UpdateGeneralById(int enjId, int generalId)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.update("t_project_info").set("general", generalId).where("id = ?", enjId);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}
bool ProjectService::UpdateProgrammeById(int enjId, int programmeId)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.update("t_project_info").set("programme_mind_id", programmeId).where("id = ?", enjId);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

//获取项目详情
bool ProjectService::QueryProjectById(ProjectInfo *proJectInfo, int id)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret = false;
    QString selectSql =
            QString("SELECT id, project_name,remark,task_name,estimate_time,estimate_objective,estimate_dept,"
                    "estimate_person,positional_titles,create_time,update_time,estimate_type FROM "
                    "t_project_info WHERE id = %1")
                    .arg(id);
    if (query.exec(selectSql)) {
        if (query.next()) {
            if (query.isNull(0) == false) {
                proJectInfo->id                = query.value(0).toInt();
                proJectInfo->projectName       = query.value(1).toString();
                proJectInfo->remark            = query.value(2).toString();
                proJectInfo->taskName          = query.value(3).toString();
                proJectInfo->estimateTime      = query.value(4).toString();
                proJectInfo->estimateObjective = query.value(5).toString();
                proJectInfo->estimateDept      = query.value(6).toString();
                proJectInfo->estimatePerson    = query.value(7).toString();
                proJectInfo->positionalTitles  = query.value(8).toString();
                proJectInfo->createTime        = query.value(9).toString();
                proJectInfo->updateTime        = query.value(10).toString();
                proJectInfo->estimateType      = query.value(11).toString();
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

//判断项目名是否存在
bool ProjectService::QueryProjectByName(QString name)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret = false;
    QString selectSql =
            QString("SELECT id, project_name,remark,task_name,estimate_time,estimate_objective,estimate_dept,"
                    "estimate_person,positional_titles,create_time,update_time,estimate_type FROM "
                    "t_project_info WHERE project_name = %1")
                    .arg(name);
    if (query.exec(selectSql)) {
        if (query.next()) {
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool ProjectService::QueryAll(QList<ProjectInfo *> *projectInfoList)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret = false;
    QString selectSql =
            QString("SELECT id, project_name, remark,task_name,estimate_time,estimate_objective,estimate_dept,"
                    "estimate_person,positional_titles,create_time,update_time,estimate_type FROM "
                    "t_project_info order by create_time desc");
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                ProjectInfo *projectInfo       = new ProjectInfo();
                projectInfo->id                = query.value(0).toInt();
                projectInfo->projectName       = query.value(1).toString();
                projectInfo->remark            = query.value(2).toString();
                projectInfo->taskName          = query.value(3).toString();
                projectInfo->estimateTime      = query.value(4).toString();
                projectInfo->estimateObjective = query.value(5).toString();
                projectInfo->estimateDept      = query.value(6).toString();
                projectInfo->estimatePerson    = query.value(7).toString();
                projectInfo->positionalTitles  = query.value(8).toString();
                projectInfo->createTime        = query.value(9).toString();
                projectInfo->updateTime        = query.value(10).toString();
                projectInfo->estimateType      = query.value(11).toString();
                projectInfoList->append(projectInfo);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

//根据id删除
bool ProjectService::DeleteById(int id)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.deleteFrom("t_project_info").where("id = ?", id);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

//根据id删除
bool ProjectService::DeleteAll()
{

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

bool ProjectService::SelectAllByPage(QList<ProjectInfo *> *projectInfoList, int &totalPages, int page, int pageSize,
                                     QString name)
{
    int offset = (page - 1) * pageSize;
    name       = "'%" + name + "%'";

    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret = false;
    QString selectSql =
            QString("SELECT id, project_name, remark,task_name,estimate_time,estimate_objective,estimate_dept,"
                    "estimate_person,positional_titles,create_time,update_time,estimate_type FROM "
                    "t_project_info where project_name like %1 order by create_time desc")
                    .arg(name);
    QString selectByPage = QString(" LIMIT %1 OFFSET %2 ").arg(pageSize).arg(offset);
    selectSql.append(selectByPage);
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                ProjectInfo *projectInfo       = new ProjectInfo();
                projectInfo->id                = query.value(0).toInt();
                projectInfo->projectName       = query.value(1).toString();
                projectInfo->remark            = query.value(2).toString();
                projectInfo->taskName          = query.value(3).toString();
                projectInfo->estimateTime      = query.value(4).toString();
                projectInfo->estimateObjective = query.value(5).toString();
                projectInfo->estimateDept      = query.value(6).toString();
                projectInfo->estimatePerson    = query.value(7).toString();
                projectInfo->positionalTitles  = query.value(8).toString();
                projectInfo->createTime        = query.value(9).toString();
                projectInfo->updateTime        = query.value(10).toString();
                projectInfo->estimateType      = query.value(11).toString();
                projectInfoList->append(projectInfo);
            }
        }
        QString selectSqlAll = QString("SELECT COUNT(*) FROM t_project_info where project_name like %1 ").arg(name);
        if (query.exec(selectSqlAll)) {
            query.next();
            totalPages = query.value(0).toInt();
            ret        = true;
        }
    } else {
        qDebug() << query.lastError();
    }

    return ret;
}