Calculation/app/service/change_plan.js

'use strict';

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

const audit = require('../const/audit').changePlan;
// 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');
const changeConst = require('../const/change');

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

        async add(tenderId, userId, code, apply_code, name) {
            const sql = 'SELECT COUNT(*) as count FROM ?? WHERE `tid` = ? AND `code` = ?';
            const sqlParam = [this.tableName, tenderId, code];
            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,
                    in_time: new Date(),
                    code,
                    apply_code,
                    name,
                    quality: changeConst.quality.common.name,
                };
                let caid = null;
                if (apply_code) {
                    const applyInfo = await this.ctx.service.changeApply.getDataByCondition({ tid: tenderId, code: apply_code });
                    if (applyInfo) {
                        caid = applyInfo.id;
                        change.org_name = applyInfo.org_name;
                        change.peg = applyInfo.peg;
                        change.new_code = applyInfo.new_code;
                        change.c_new_code = applyInfo.c_new_code;
                        change.design_name = applyInfo.design_name;
                        change.class = applyInfo.class;
                        change.quality = applyInfo.quality;
                        change.reason = applyInfo.reason;
                        change.content = applyInfo.content;
                        change.total_price = applyInfo.total_price;
                    }
                }
                const operate = await this.transaction.insert(this.tableName, change);

                if (operate.affectedRows <= 0) {
                    throw '新建变更令数据失败';
                }
                change.id = operate.insertId;
                // 清单也要同步
                if (caid) {
                    const changeList = await this.ctx.service.changeApplyList.getList(caid);
                    const insertArray = [];
                    for (const c of changeList) {
                        insertArray.push({
                            tid: this.ctx.tender.id,
                            cpid: operate.insertId,
                            code: c.code,
                            name: c.name,
                            unit: c.unit,
                            unit_price: c.unit_price,
                            oamount: c.oamount,
                            camount: c.camount,
                            spamount: c.camount,
                        });
                    }
                    if (insertArray.length > 0) await this.transaction.insert(this.ctx.service.changePlanList.tableName, insertArray);
                }
                // 先找出标段最近存在的变更令审批人的变更令info
                const preChangeInfo = await this.getHaveAuditLastInfo(tenderId);
                if (preChangeInfo) {
                    // 并把之前存在的变更令审批人添加到zh_change_audit
                    const auditResult = await this.ctx.service.changePlanAudit.copyPreChangePlanAuditors(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) {
            const sql = 'SELECT a.* FROM ?? as a LEFT JOIN ?? as b ON a.`id` = b.`cpid` WHERE a.`tid` = ? ORDER BY a.`in_time` DESC';
            const sqlParam = [this.tableName, this.ctx.service.changePlanAudit.tableName, tenderId];
            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.status = ?)';
                        sqlParam = [
                            this.tableName,
                            this.ctx.service.projectAccount.tableName,
                            tenderId,
                            this.ctx.session.sessionUser.accountId,
                            audit.status.uncheck,
                            this.ctx.service.changePlanAudit.tableName,
                            this.ctx.session.sessionUser.accountId,
                            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.changePlanAudit.tableName, tenderId, this.ctx.session.sessionUser.accountId, audit.status.checking, this.ctx.session.sessionUser.accountId, audit.status.uncheck, audit.status.checkNo];
                        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.changePlanAudit.tableName,
                            this.ctx.session.sessionUser.accountId,
                            tenderId,
                            audit.status.uncheck,
                            audit.status.checkNo,
                        ];
                        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))';
                        sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, status, tenderId, this.ctx.session.sessionUser.accountId, this.ctx.service.changePlanAudit.tableName, this.ctx.session.sessionUser.accountId];
                        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 && status === 0) {
                const sql5 = 'SELECT count(*) AS count FROM ?? WHERE tid = ? ORDER BY in_time DESC';
                const sqlParam5 = [this.tableName, tenderId];
                const result5 = await this.db.query(sql5, sqlParam5);
                return result5[0].count;
            }
            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.status = ?)';
                    const sqlParam = [
                        this.tableName,
                        tenderId,
                        this.ctx.session.sessionUser.accountId,
                        audit.status.uncheck,
                        this.ctx.service.changePlanAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        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.changePlanAudit.tableName, tenderId, this.ctx.session.sessionUser.accountId, audit.status.checking, this.ctx.session.sessionUser.accountId, audit.status.uncheck, audit.status.checkNo];
                    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.changePlanAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        tenderId,
                        audit.status.uncheck,
                        audit.status.checkNo,
                    ];
                    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))';
                    const sqlParam3 = [this.tableName, status, tenderId, this.ctx.session.sessionUser.accountId, this.ctx.service.changePlanAudit.tableName, this.ctx.session.sessionUser.accountId];
                    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} tenderId - 标段id
         * @param {int} status - 状态
         * @return {void}
         */
        async getTp(tenderId, status) {
            if (this.ctx.tender.isTourist && status === 0) {
                const sql5 = 'SELECT SUM(cast (total_price as decimal(18,6))) AS total_price FROM ?? WHERE tid = ?';
                const sqlParam5 = [this.tableName, tenderId];
                const result5 = await this.db.query(sql5, sqlParam5);
                return result5[0].total_price ? result5[0].total_price : 0;
            }
            switch (status) {
                case 0: // 包含你的所有变更令
                    const sql =
                        'SELECT SUM(cast (a.total_price as decimal(18,6))) AS total_price 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.status = ?)';
                    const sqlParam = [
                        this.tableName,
                        tenderId,
                        this.ctx.session.sessionUser.accountId,
                        audit.status.uncheck,
                        this.ctx.service.changePlanAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        audit.status.checked,
                    ];
                    const result = await this.db.query(sql, sqlParam);
                    return result[0].total_price ? result[0].total_price : 0;
                case 1: // 待处理(你的)
                    // return await this.ctx.service.changeAudit.count({
                    //     tid: tenderId,
                    //     uid: this.ctx.session.sessionUser.accountId,
                    //     status: 2,
                    // });
                    const sql6 = 'SELECT SUM(cast (a.total_price as decimal(18,6))) AS total_price 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.changePlanAudit.tableName, tenderId, this.ctx.session.sessionUser.accountId, audit.status.checking, this.ctx.session.sessionUser.accountId, audit.status.uncheck, audit.status.checkNo];
                    const result6 = await this.db.query(sql6, sqlParam6);
                    return result6[0].total_price ? result6[0].total_price : 0;
                case 5: // 待上报(所有的)PS：取未上报，退回的变更立项
                    const sql2 =
                        'SELECT SUM(cast (a.total_price as decimal(18,6))) AS total_price 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.changePlanAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        tenderId,
                        audit.status.uncheck,
                        audit.status.checkNo,
                    ];
                    const result2 = await this.db.query(sql2, sqlParam2);
                    return result2[0].total_price ? result2[0].total_price : 0;
                case 2: // 进行中（所有的）
                case 4: // 终止（所有的）
                    const sql3 =
                        'SELECT SUM(cast (a.total_price as decimal(18,6))) AS total_price 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))';
                    const sqlParam3 = [this.tableName, status, tenderId, this.ctx.session.sessionUser.accountId, this.ctx.service.changePlanAudit.tableName, this.ctx.session.sessionUser.accountId];
                    const result3 = await this.db.query(sql3, sqlParam3);
                    return result3[0].total_price ? result3[0].total_price : 0;
                case 3: // 已完成（所有的）
                    const sql4 = 'SELECT SUM(cast (a.total_price as decimal(18,6))) AS total_price FROM ?? WHERE status = ? AND tid = ?';
                    const sqlParam4 = [this.tableName, status, tenderId];
                    const result4 = await this.db.query(sql4, sqlParam4);
                    return result4[0].total_price ? result4[0].total_price : 0;
                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) {
            const sql = 'SELECT COUNT(*) as count FROM ?? WHERE `code` = ? AND `id` != ? AND `tid` = ?';
            const sqlParam = [this.tableName, code, cpid, tid];
            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.changePlanAudit.tableName, { cpid: id });
                // 删除清单
                await this.transaction.delete(this.ctx.service.changePlanList.tableName, { cpid: id });
                // 再删除附件和附件文件ni zuo
                const attList = await this.ctx.service.changePlanAtt.getAllDataByCondition({ where: { cpid: id } });
                await this.ctx.helper.delFiles(attList);
                await this.transaction.delete(this.ctx.service.changePlanAtt.tableName, { cpid: id });
                // 最后删除变更令
                await this.transaction.delete(this.tableName, { id });
                // 记录删除日志
                await this.ctx.service.projectLog.addProjectLog(this.transaction, projectLogConst.type.changePlan, projectLogConst.status.delete, changeInfo.code);
                await this.transaction.commit();
                result = true;
            } catch (e) {
                await this.transaction.rollback();
                result = false;
            }
            return result;
        }
    }

    return ChangePlan;
};