Calculation/app/service/change_project.js

'use strict';

/**
 *
 *
 * @author Mai
 * @date 2018/8/14
 * @version
 */

const audit = require('../const/audit').changeProject;
// const smsTypeConst = require('../const/sms_type');
// const SMS = require('../lib/sms');
// const SmsAliConst = require('../const/sms_alitemplate');
const wxConst = require('../const/wechat_template');
const pushType = require('../const/audit').pushType;
const projectLogConst = require('../const/project_log');
const codeRuleConst = require('../const/code_rule');

module.exports = app => {
    class ChangeProject extends app.BaseService {
        /**
         * 构造函数
         *
         * @param {Object} ctx - egg全局变量
         * @return {void}
         */
        constructor(ctx) {
            super(ctx);
            this.tableName = 'change_project';
        }

        async add(tenderId, userId, code, name) {
            const type = userId === this.ctx.tender.data.user_id ? codeRuleConst.ruleType.suggestion : codeRuleConst.ruleType.will;
            const sql = 'SELECT COUNT(*) as count FROM ?? WHERE `tid` = ? AND `code` = ? AND type = ?';
            const sqlParam = [this.tableName, tenderId, code, type];
            const codeCount = await this.db.queryOne(sql, sqlParam);
            const count = codeCount.count;
            if (count > 0) {
                throw '立项书编号重复';
            }

            // 初始化事务
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                const change = {
                    tid: tenderId,
                    uid: userId,
                    status: audit.status.uncheck,
                    times: 1,
                    type,
                    in_time: new Date(),
                    code,
                    name,
                };
                const operate = await this.transaction.insert(this.tableName, change);

                if (operate.affectedRows <= 0) {
                    throw '新建变更令数据失败';
                }
                change.id = operate.insertId;
                // 先找出标段最近存在的变更令审批人的变更令info
                const preChangeInfo = await this.getHaveAuditLastInfo(tenderId, type);
                if (preChangeInfo) {
                    // 并把之前存在的变更令审批人添加到zh_change_audit
                    const auditResult = await this.ctx.service.changeProjectAudit.copyPreChangeProjectAuditors(this.transaction, preChangeInfo, change);
                    if (!auditResult) {
                        throw '复制上一次审批流程失败';
                    }
                }
                result = change;
                this.transaction.commit();
            } catch (error) {
                console.log(error);
                // 回滚
                await this.transaction.rollback();
            }

            return result;
        }

        async getHaveAuditLastInfo(tenderId, type) {
            const sql = 'SELECT a.* FROM ?? as a LEFT JOIN ?? as b ON a.`id` = b.`cpid` WHERE a.`tid` = ? AND a.type = ? ORDER BY a.`in_time` DESC';
            const sqlParam = [this.tableName, this.ctx.service.changeProjectAudit.tableName, tenderId, type];
            return await this.db.queryOne(sql, sqlParam);
        }

        /**
         * 获取变更立项列表
         * @param {int} tenderId - 标段id
         * @param {int} status - 状态
         * @param {int} hadlimit - 分页
         * @return {object} list - 列表
         */
        async getListByStatus(tenderId, status = 0, hadlimit = 1, sortBy = '', orderBy = '') {
            let sql = '';
            let sqlParam = '';
            if (this.ctx.tender.isTourist && status === 0) {
                sql = 'SELECT a.*, p.name as account_name FROM ?? As a LEFT JOIN ?? AS p On a.uid = p.id WHERE a.tid = ?';
                sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, tenderId];
            } else {
                switch (status) {
                    case 0: // 包含你的所有变更立项
                        sql =
                            'SELECT a.*, p.name as account_name FROM ?? AS a LEFT JOIN ?? AS p On a.uid = p.id WHERE a.tid = ? AND ' +
                            '(a.uid = ? OR (a.status != ? AND (a.id IN (SELECT b.cpid FROM ?? AS b WHERE b.aid = ? AND a.times = b.times GROUP BY b.cpid) OR a.id IN (SELECT c.cpid FROM ?? AS c WHERE c.aid = ? AND c.tid = ?))) OR a.status = ? )';
                        sqlParam = [
                            this.tableName,
                            this.ctx.service.projectAccount.tableName,
                            tenderId,
                            this.ctx.session.sessionUser.accountId,
                            audit.status.uncheck,
                            this.ctx.service.changeProjectAudit.tableName,
                            this.ctx.session.sessionUser.accountId,
                            this.ctx.service.changeProjectXsAudit.tableName,
                            this.ctx.session.sessionUser.accountId,
                            tenderId,
                            audit.status.checked,
                        ];
                        break;
                    case 1: // 待处理(你的)
                        sql = 'SELECT a.*, p.name as account_name FROM ?? as a LEFT JOIN ?? AS p On a.uid = p.id WHERE a.tid = ? AND (a.id in(SELECT b.cpid FROM ?? as b WHERE b.tid = ? AND b.aid = ? AND b.status = ?) OR (a.uid = ? AND (a.status = ? OR a.status = ?)))';
                        sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, tenderId, this.ctx.service.changeProjectAudit.tableName, tenderId, this.ctx.session.sessionUser.accountId, audit.status.checking, this.ctx.session.sessionUser.accountId, audit.status.uncheck, audit.status.back];
                        break;
                    case 5: // 待上报(所有的)PS：取未上报，退回，修订的变更令
                        sql =
                            'SELECT a.*, p.name as account_name FROM ?? AS a LEFT JOIN ?? AS p On a.uid = p.id WHERE ' +
                            // 'a.id IN (SELECT b.cpid FROM ?? AS b WHERE b.aid = ? AND a.times = b.times GROUP BY b.cpid) AND ' +
                            'a.uid = ? AND a.tid = ? AND (a.status = ? OR a.status = ?)';
                        sqlParam = [
                            this.tableName,
                            this.ctx.service.projectAccount.tableName,
                            // this.ctx.service.changeProjectAudit.tableName,
                            this.ctx.session.sessionUser.accountId,
                            tenderId,
                            audit.status.uncheck,
                            audit.status.back,
                        ];
                        break;
                    case 2: // 进行中（所有的）
                    case 4: // 终止（所有的）
                        sql =
                            'SELECT a.*, p.name as account_name FROM ?? AS a LEFT JOIN ?? AS p On a.uid = p.id WHERE ' +
                            'a.status = ? AND a.tid = ? AND (a.uid = ? OR a.id IN (SELECT b.cpid FROM ?? AS b WHERE b.aid = ? AND a.times = b.times GROUP BY b.cpid) OR a.id IN (SELECT c.cpid FROM ?? AS c WHERE c.aid = ? AND c.tid = ?))';
                        sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, status, tenderId, this.ctx.session.sessionUser.accountId, this.ctx.service.changeProjectAudit.tableName, this.ctx.session.sessionUser.accountId, this.ctx.service.changeProjectXsAudit.tableName, this.ctx.session.sessionUser.accountId, tenderId];
                        break;
                    case 3: // 已完成（所有的）
                        sql = 'SELECT a.*, p.name as account_name FROM ?? AS a LEFT JOIN ?? AS p On a.uid = p.id WHERE a.status = ? AND a.tid = ?';
                        sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, status, tenderId];
                        break;
                    default:
                        break;
                }
            }
            if (sortBy && orderBy) {
                if (sortBy === 'code') {
                    sql += ' ORDER BY CHAR_LENGTH(a.code) ' + orderBy + ',convert(a.code using gbk) ' + orderBy;
                } else {
                    sql += ' ORDER BY a.in_time ' + orderBy;
                }
            } else {
                sql += ' ORDER BY a.in_time DESC';
            }
            if (hadlimit) {
                const limit = this.app.config.pageSize;
                const offset = limit * (this.ctx.page - 1);
                const limitString = offset >= 0 ? offset + ',' + limit : limit;
                sql += ' LIMIT ' + limitString;
            }
            const list = await this.db.query(sql, sqlParam);
            return list;
        }

        /**
         * 获取变更令个数
         * @param {int} tenderId - 标段id
         * @param {int} status - 状态
         * @return {void}
         */
        async getCountByStatus(tenderId, status) {
            if (this.ctx.tender.isTourist) {
                let touristSql;
                let touristSqlParam;
                let touristResult;
                switch (status) {
                    case 0:
                        touristSql = 'SELECT count(*) AS count FROM ?? WHERE tid = ? ORDER BY in_time DESC';
                        touristSqlParam = [this.tableName, tenderId];
                        touristResult = await this.db.query(touristSql, touristSqlParam);
                        return touristResult[0].count;
                    // case 1:
                    //     touristSql = 'SELECT count(*) AS count FROM ?? WHERE tid = ? AND (status = ? OR status = ?) ORDER BY in_time DESC';
                    //     touristSqlParam = [this.tableName, tenderId, audit.status.uncheck, audit.status.checking];
                    //     touristResult = await this.db.query(touristSql, touristSqlParam);
                    //     return touristResult[0].count;
                    // case 5:
                    //     touristSql = 'SELECT count(*) AS count FROM ?? WHERE tid = ? AND status = ? ORDER BY in_time DESC';
                    //     touristSqlParam = [this.tableName, tenderId, audit.status.uncheck];
                    //     touristResult = await this.db.query(touristSql, touristSqlParam);
                    //     return touristResult[0].count;
                    // case 2:
                    //     touristSql = 'SELECT count(*) AS count FROM ?? WHERE tid = ? AND status = ? ORDER BY in_time DESC';
                    //     touristSqlParam = [this.tableName, tenderId, audit.status.uncheck];
                    //     touristResult = await this.db.query(touristSql, touristSqlParam);
                    //     return touristResult[0].count;
                    // case 4:
                    //     touristSql = 'SELECT count(*) AS count FROM ?? WHERE tid = ? AND status = ? ORDER BY in_time DESC';
                    //     touristSqlParam = [this.tableName, tenderId, status];
                    //     touristResult = await this.db.query(touristSql, touristSqlParam);
                    //     return touristResult[0].count;
                    default:
                        break;
                }
            }
            switch (status) {
                case 0: // 包含你的所有变更令
                    const sql =
                        'SELECT count(*) AS count FROM ?? AS a WHERE a.tid = ? AND ' +
                        '(a.uid = ? OR (a.status != ? AND (a.id IN (SELECT b.cpid FROM ?? AS b WHERE b.aid = ? AND a.times = b.times GROUP BY b.cpid) OR a.id IN (SELECT c.cpid FROM ?? AS c WHERE c.aid = ? AND c.tid = ?))) OR a.status = ? )';
                    const sqlParam = [
                        this.tableName,
                        tenderId,
                        this.ctx.session.sessionUser.accountId,
                        audit.status.uncheck,
                        this.ctx.service.changeProjectAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        this.ctx.service.changeProjectXsAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        tenderId,
                        audit.status.checked,
                    ];
                    const result = await this.db.query(sql, sqlParam);
                    return result[0].count;
                case 1: // 待处理(你的)
                    // return await this.ctx.service.changeAudit.count({
                    //     tid: tenderId,
                    //     uid: this.ctx.session.sessionUser.accountId,
                    //     status: 2,
                    // });
                    const sql6 = 'SELECT count(*) AS count FROM ?? as a WHERE a.tid = ? AND (a.id in(SELECT b.cpid FROM ?? as b WHERE b.tid = ? AND b.aid = ? AND b.status = ?) OR (a.uid = ? AND (a.status = ? OR a.status = ?)))';
                    const sqlParam6 = [this.tableName, tenderId, this.ctx.service.changeProjectAudit.tableName, tenderId, this.ctx.session.sessionUser.accountId, audit.status.checking, this.ctx.session.sessionUser.accountId, audit.status.uncheck, audit.status.back];
                    const result6 = await this.db.query(sql6, sqlParam6);
                    return result6[0].count;
                case 5: // 待上报(所有的)PS：取未上报，退回的变更立项
                    const sql2 =
                        'SELECT count(*) AS count FROM ?? AS a WHERE ' +
                        // 'a.id IN (SELECT b.cpid FROM ?? AS b WHERE b.aid = ? AND a.times = b.times GROUP BY b.cpid) ' +
                        'a.uid = ? AND a.tid = ? AND (a.status = ? OR a.status = ?)';
                    const sqlParam2 = [
                        this.tableName,
                        // this.ctx.service.changeProjectAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        tenderId,
                        audit.status.uncheck,
                        audit.status.back,
                    ];
                    const result2 = await this.db.query(sql2, sqlParam2);
                    return result2[0].count;
                case 2: // 进行中（所有的）
                case 4: // 终止（所有的）
                    const sql3 =
                        'SELECT count(*) AS count FROM ?? AS a WHERE ' +
                        'a.status = ? AND a.tid = ? AND (a.uid = ? OR a.id IN (SELECT b.cpid FROM ?? AS b WHERE b.aid = ? AND a.times = b.times GROUP BY b.cpid) OR a.id IN (SELECT c.cpid FROM ?? AS c WHERE c.aid = ? AND c.tid = ?))';
                    const sqlParam3 = [this.tableName, status, tenderId, this.ctx.session.sessionUser.accountId, this.ctx.service.changeProjectAudit.tableName, this.ctx.session.sessionUser.accountId, this.ctx.service.changeProjectXsAudit.tableName, this.ctx.session.sessionUser.accountId, tenderId];
                    const result3 = await this.db.query(sql3, sqlParam3);
                    return result3[0].count;
                case 3: // 已完成（所有的）
                    const sql4 = 'SELECT count(*) AS count FROM ?? WHERE status = ? AND tid = ?';
                    const sqlParam4 = [this.tableName, status, tenderId];
                    const result4 = await this.db.query(sql4, sqlParam4);
                    return result4[0].count;
                default:
                    break;
            }
        }

        /**
         * 保存变更信息
         * @param {int} postData - 表单提交的数据
         * @param {int} tenderId - 标段id
         * @return {void}
         */
        async saveInfo(cpId, postData) {
            // 初始化事务
            const transaction = await this.db.beginTransaction();
            let result = false;
            try {
                const updateData = {
                    id: cpId,
                };
                updateData[postData.name] = postData.val;
                await transaction.update(this.tableName, updateData);
                await transaction.commit();
                result = true;
            } catch (error) {
                await transaction.rollback();
                result = false;
            }
            return result;
        }

        /**
         * 判断是否有重名的变更立项
         * @param cpid
         * @param code
         * @param tid
         * @return {Promise<void>}
         */
        async isRepeat(cpId, code, tid, type) {
            const sql = 'SELECT COUNT(*) as count FROM ?? WHERE `code` = ? AND `id` != ? AND `tid` = ? AND `type` = ?';
            const sqlParam = [this.tableName, code, cpId, tid, type];
            const result = await this.db.queryOne(sql, sqlParam);
            return result.count !== 0;
        }

        /**
         * 查询可用的变更令
         * @param { string } cid - 查询的清单
         * @return {Promise<*>} - 可用的变更令列表
         */
        async delete(id) {
            // 初始化事务
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                const changeInfo = await this.getDataById(id);
                // 先删除审批人列表
                await this.transaction.delete(this.ctx.service.changeProjectAudit.tableName, { cpid: id });
                // 再删除附件和附件文件ni zuo
                const attList = await this.ctx.service.changeProjectAtt.getAllDataByCondition({ where: { cpid: id } });
                await this.ctx.helper.delFiles(attList);
                await this.transaction.delete(this.ctx.service.changeProjectAtt.tableName, { cpid: id });
                // if (attList.length !== 0) {
                //     for (const att of attList) {
                //         await fs.unlinkSync(path.join(this.app.baseDir, att.filepath));
                //     }
                //     await this.transaction.delete(this.ctx.service.changeAtt.tableName, { cid });
                // }
                // 最后删除变更令
                await this.transaction.delete(this.tableName, { id });
                // 记录删除日志
                await this.ctx.service.projectLog.addProjectLog(this.transaction, projectLogConst.type.changeProject, projectLogConst.status.delete, changeInfo.code);
                await this.transaction.commit();
                result = true;
            } catch (e) {
                await this.transaction.rollback();
                result = false;
            }
            return result;
        }
    }

    return ChangeProject;
};