Calculation/app/service/quality_inspection.js

'use strict';

/**
 * 质量管理 - 巡检单
 *
 * @author Mai
 * @date 2024/7/22
 * @version
 */
const auditConst = require('../const/audit').inspection;
const auditType = require('../const/audit').auditType;
module.exports = app => {

    class QualityInspection extends app.BaseService {

        /**
         * 构造函数
         *
         * @param {Object} ctx - egg全局变量
         * @return {void}
         */
        constructor(ctx) {
            super(ctx);
            this.tableName = 'quality_inspection';
        }

        async loadUser(inspection) {
            const status = auditConst.status;
            const accountId = this.ctx.session.sessionUser.accountId;

            inspection.user = await this.ctx.service.projectAccount.getAccountInfoById(inspection.uid);
            if (inspection.rectification_uid) {
                inspection.rectification_user = await this.ctx.service.projectAccount.getAccountInfoById(inspection.rectification_uid);
            }
            inspection.auditors = await this.ctx.service.qualityInspectionAudit.getAuditors(inspection.id, inspection.times); // 全部参与的审批人
            inspection.auditorIds = this._.map(inspection.auditors, 'aid');
            inspection.curAuditors = inspection.auditors.filter(x => { return x.status === status.checking || x.status === status.rectification; }); // 当前流程中审批中的审批人
            inspection.curAuditorIds = this._.map(inspection.curAuditors, 'aid');
            inspection.flowAuditors = inspection.curAuditors.length > 0 ? inspection.auditors.filter(x => { return x.order === inspection.curAuditors[0].order; }) : []; // 当前流程中参与的审批人（包含会签时，审批通过的人）
            inspection.flowAuditorIds = this._.map(inspection.flowAuditors, 'aid');
            inspection.nextAuditors = inspection.curAuditors.length > 0 ? inspection.auditors.filter(x => { return x.order === inspection.curAuditors[0].order + 1; }) : [];
            inspection.nextAuditorIds = this._.map(inspection.nextAuditors, 'aid');
            const newAuditors = inspection.auditors.filter(x => { return x.is_old === 0 && x.is_rectification === 0; });
            inspection.auditorGroups = this.ctx.helper.groupAuditors(newAuditors);
            inspection.userGroups = this.ctx.helper.groupAuditorsUniq(inspection.auditorGroups);
            inspection.userGroups.unshift([{
                aid: inspection.user.id, order: 0, times: inspection.times, audit_order: 0, audit_type: auditType.key.common,
                name: inspection.user.name, role: inspection.user.role, company: inspection.user.company,
            }]);
            inspection.finalAuditorIds = inspection.userGroups[inspection.userGroups.length - 1].map(x => { return x.aid; });
        }

        async loadAuditViewData(inspection) {
            const times = inspection.status === auditConst.status.checkNo ? inspection.times - 1 : inspection.times;

            if (!inspection.user) inspection.user = await this.ctx.service.projectAccount.getAccountInfoById(inspection.uid);
            inspection.auditHistory = await this.ctx.service.qualityInspectionAudit.getAuditorHistory(inspection.id, times);
            // 获取审批流程中左边列表
            if (inspection.status === auditConst.status.checkNo && inspection.uid !== this.ctx.session.sessionUser.accountId) {
                const auditors = await this.ctx.service.qualityInspectionAudit.getAuditors(inspection.id, times); // 全部参与的审批人
                const newAuditors = auditors.filter(x => { return x.is_old === 0 && x.is_rectification === 0; });
                const auditorGroups = this.ctx.helper.groupAuditors(newAuditors);
                inspection.auditors2 = this.ctx.helper.groupAuditorsUniq(auditorGroups);
                inspection.auditors2.unshift([{
                    aid: inspection.user.id, order: 0, times: inspection.times - 1, audit_order: 0, audit_type: auditType.key.common,
                    name: inspection.user.name, role: inspection.user.role, company: inspection.user.company,
                }]);
            } else {
                inspection.auditors2 = inspection.userGroups;
            }
            if (inspection.status === auditConst.status.uncheck || inspection.status === auditConst.status.checkNo) {
                inspection.auditorList = await this.ctx.service.qualityInspectionAudit.getAuditors(inspection.id, inspection.times);
            }
        }

        async add(tenderId, userId, code, check_item, check_date) {
            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 '编号重复';
            }

            // 初始化事务
            const transaction = await this.db.beginTransaction();
            let result = false;
            try {
                const inspection = {
                    tid: tenderId,
                    uid: userId,
                    status: auditConst.status.uncheck,
                    times: 1,
                    code,
                    check_item,
                    check_date,
                    inspector: this.ctx.session.sessionUser.name,
                    create_time: new Date(),
                };
                const operate = await transaction.insert(this.tableName, inspection);

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

        async addByWap(tid, data) {
            const sql = 'SELECT COUNT(*) as count FROM ?? WHERE `tid` = ? AND `code` = ?';
            const sqlParam = [this.tableName, tid, data.code];
            const codeCount = await this.db.queryOne(sql, sqlParam);
            const count = codeCount.count;
            if (count > 0) {
                throw '巡检编号已存在，请修改巡检编号';
            }

            // 初始化事务
            const transaction = await this.db.beginTransaction();
            let result = false;
            try {
                const inspection = {
                    tid,
                    uid: this.ctx.session.sessionUser.accountId,
                    status: auditConst.status.uncheck,
                    times: 1,
                    code: data.code,
                    check_item: data.check_item,
                    check_date: data.check_date || null,
                    check_situation: data.check_situation || '',
                    action: data.action || '',
                    inspector: data.inspector || this.ctx.session.sessionUser.name,
                    create_time: new Date(),
                };
                const operate = await transaction.insert(this.tableName, inspection);

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

        async delInspection(id) {
            const transaction = await this.db.beginTransaction();
            try {
                // 删除预付款记录
                await transaction.delete(this.tableName, { id });
                // 删除附件
                const fileInfo = await this.db.select(this.ctx.service.qualityInspectionAtt.tableName, { where: { qiid: id } });
                await transaction.delete(this.ctx.service.qualityInspectionAtt.tableName, { qiid: id });
                await this.ctx.helper.delFiles(fileInfo);
                // 先删除文件
                // for (let i = 0; i < fileInfo.length; i++) {
                //     const file = fileInfo[i];
                //     if (fs.existsSync(path.resolve(this.app.baseDir, './app', file.filepath))) {
                //         fs.unlinkSync(path.resolve(this.app.baseDir, './app', file.filepath));
                //         // fs.unlinkSync(path.resolve(this.app.baseDir, zipPath));
                //     }
                //     await this.ctx.app.fujianOss.delete(file.filepath);
                // }
                // 删除审批记录
                await transaction.delete(this.ctx.service.qualityInspectionAudit.tableName, { qiid: id });
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

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

        async getListByStatus(tid, status, hadlimit = 0, sortBy = '', orderBy = '') {
            let sql = '';
            let sqlParam = '';
            if ((this.ctx.tender.isTourist || this.ctx.session.sessionUser.is_admin || this.ctx.permission.inspection.view_all) && status === 0) {
                sql = 'SELECT a.* FROM ?? As a WHERE a.tid = ?';
                sqlParam = [this.tableName, tid];
            } else {
                switch (status) {
                    case 0: // 所有
                        sql =
                            'SELECT a.* FROM ?? AS a WHERE a.tid = ? AND' +
                            ' (a.uid = ? OR (a.status != ? AND a.id IN (SELECT b.qiid FROM ?? AS b WHERE b.aid = ? GROUP BY b.qiid))' +
                            ' OR a.status = ?)';
                        sqlParam = [
                            this.tableName,
                            tid,
                            this.ctx.session.sessionUser.accountId,
                            auditConst.status.uncheck,
                            this.ctx.service.qualityInspectionAudit.tableName,
                            this.ctx.session.sessionUser.accountId,
                            auditConst.status.checked,
                        ];
                        break;
                    case auditConst.filter.status.pending: // 待处理(你的)
                        sql = 'SELECT a.* FROM ?? as a WHERE a.tid = ? AND (a.id in(SELECT b.qiid FROM ?? as b WHERE b.tid = ? AND b.aid = ? AND (b.status = ? OR b.status = ?)) OR (a.uid = ? AND (a.status = ? OR a.status = ?)))';
                        sqlParam = [this.tableName, tid, this.ctx.service.qualityInspectionAudit.tableName, tid, this.ctx.session.sessionUser.accountId, auditConst.status.checking, auditConst.status.rectification, this.ctx.session.sessionUser.accountId, auditConst.status.uncheck, auditConst.status.checkNo];
                        break;
                    case auditConst.filter.status.uncheck: // 待上报(所有的)PS：取未上报，退回，修订的变更令
                        sql =
                            'SELECT a.* FROM ?? AS a WHERE ' +
                            'a.tid = ? AND (a.status = ? OR a.status = ?)' +
                            (this.ctx.session.sessionUser.is_admin || this.ctx.permission.inspection.view_all ? '' : ' AND a.uid = ' + this.ctx.session.sessionUser.accountId);
                        sqlParam = [
                            this.tableName,
                            tid,
                            auditConst.status.uncheck,
                            auditConst.status.checkNo,
                        ];
                        break;
                    case auditConst.filter.status.checking: // 进行中（所有的）
                        sql =
                            'SELECT a.* FROM ?? AS a WHERE ' +
                            '(a.status = ? OR a.status = ?) AND a.tid = ?' +
                            (this.ctx.session.sessionUser.is_admin || this.ctx.permission.inspection.view_all ? '' :  ' AND (a.uid = ' + this.ctx.session.sessionUser.accountId + ' OR a.id IN (SELECT b.qiid FROM ' + this.ctx.service.qualityInspectionAudit.tableName + ' AS b WHERE b.aid = ' + this.ctx.session.sessionUser.accountId + ' GROUP BY b.qiid))');
                        sqlParam = [this.tableName, status, auditConst.status.checkNoPre, tid];
                        break;
                    case auditConst.filter.status.rectification: // 整改中（所有的）
                    case auditConst.filter.status.checkStop: // 终止（所有的）
                        sql =
                            'SELECT a.* FROM ?? AS a WHERE ' +
                            'a.status = ? AND a.tid = ?' +
                            (this.ctx.session.sessionUser.is_admin || this.ctx.permission.inspection.view_all ? '' :  ' AND (a.uid = ' + this.ctx.session.sessionUser.accountId + ' OR a.id IN (SELECT b.qiid FROM ' + this.ctx.service.qualityInspectionAudit.tableName + ' AS b WHERE b.aid = ' + this.ctx.session.sessionUser.accountId + ' GROUP BY b.qiid))');
                        sqlParam = [this.tableName, status, tid];
                        break;
                    case auditConst.filter.status.checked: // 已完成（所有的）
                        sql = 'SELECT a.* FROM ?? as a WHERE a.status = ? AND a.tid = ?';
                        sqlParam = [this.tableName, status, tid];
                        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.create_time ' + orderBy;
                }
            } else {
                sql += ' ORDER BY a.create_time DESC';
            }
            if (hadlimit) {
                const limit = this.ctx.pageSize ? this.ctx.pageSize : 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} spid - 项目id
         * @param {int} status - 状态
         * @return {void}
         */
        async getCountByStatus(tid, status = 0) {
            if ((this.ctx.tender.isTourist || this.ctx.session.sessionUser.is_admin || this.ctx.permission.inspection.view_all) && status === 0) {
                const sql5 = 'SELECT count(*) AS count FROM ?? AS a WHERE a.tid = ?';
                const sqlParam5 = [this.tableName, tid];
                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.qiid FROM ?? AS b WHERE b.aid = ? GROUP BY b.qiid)) OR a.status = ?)';
                    const sqlParam = [
                        this.tableName,
                        tid,
                        this.ctx.session.sessionUser.accountId,
                        auditConst.status.uncheck,
                        this.ctx.service.qualityInspectionAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        auditConst.status.checked,
                    ];
                    const result = await this.db.query(sql, sqlParam);
                    return result[0].count;
                case auditConst.filter.status.pending: // 待处理(你的)
                    const sql6 = 'SELECT count(*) AS count FROM ?? as a WHERE a.tid = ? AND (a.id in(SELECT b.qiid FROM ?? as b WHERE b.tid = ? AND b.aid = ? AND (b.status = ? OR b.status = ?)) OR (a.uid = ? AND (a.status = ? OR a.status = ?)))';
                    const sqlParam6 = [this.tableName, tid, this.ctx.service.qualityInspectionAudit.tableName, tid, this.ctx.session.sessionUser.accountId, auditConst.status.checking, auditConst.status.rectification, this.ctx.session.sessionUser.accountId, auditConst.status.uncheck, auditConst.status.checkNo];
                    const result6 = await this.db.query(sql6, sqlParam6);
                    return result6[0].count;
                case auditConst.filter.status.uncheck: // 待上报(所有的)PS：取未上报，退回的变更立项
                    const sql2 =
                        'SELECT count(*) AS count FROM ?? AS a WHERE a.tid = ? AND (a.status = ? OR a.status = ?)' +
                        (this.ctx.session.sessionUser.is_admin || this.ctx.permission.inspection.view_all ? '' : ' AND a.uid = ' + this.ctx.session.sessionUser.accountId);
                    const sqlParam2 = [
                        this.tableName,
                        tid,
                        auditConst.status.uncheck,
                        auditConst.status.checkNo,
                    ];
                    const result2 = await this.db.query(sql2, sqlParam2);
                    return result2[0].count;
                case auditConst.filter.status.checking: // 进行中（所有的）
                    const sql7 =
                        'SELECT count(*) AS count FROM ?? as a WHERE ' +
                        '(a.status = ? OR a.status = ?) AND a.tid = ?' +
                        (this.ctx.session.sessionUser.is_admin || this.ctx.permission.inspection.view_all ? '' : ' AND (a.uid = ' + this.ctx.session.sessionUser.accountId + ' OR a.id IN (SELECT b.qiid FROM ' + this.ctx.service.qualityInspectionAudit.tableName + ' AS b WHERE b.aid = ' + this.ctx.session.sessionUser.accountId + ' GROUP BY b.qiid))');
                    const sqlParam7 = [this.tableName, status, auditConst.status.checkNoPre, tid];
                    const result7 = await this.db.query(sql7, sqlParam7);
                    return result7[0].count;
                case auditConst.filter.status.rectification: // 整改中（所有的）
                case auditConst.filter.status.checkStop: // 终止（所有的）
                    const sql3 =
                        'SELECT count(*) AS count FROM ?? as a WHERE ' +
                        'a.status = ? AND a.tid = ?' +
                        (this.ctx.session.sessionUser.is_admin || this.ctx.permission.inspection.view_all ? '' : ' AND (a.uid = ' + this.ctx.session.sessionUser.accountId + ' OR a.id IN (SELECT b.qiid FROM ' + this.ctx.service.qualityInspectionAudit.tableName + ' AS b WHERE b.aid = ' + this.ctx.session.sessionUser.accountId + ' GROUP BY b.qiid))');
                    const sqlParam3 = [this.tableName, status, tid];
                    const result3 = await this.db.query(sql3, sqlParam3);
                    return result3[0].count;
                case auditConst.filter.status.checked: // 已完成（所有的）
                    const sql4 = 'SELECT count(*) AS count FROM ?? as a WHERE a.status = ? AND a.tid = ?';
                    const sqlParam4 = [this.tableName, status, tid];
                    const result4 = await this.db.query(sql4, sqlParam4);
                    return result4[0].count;
                default:
                    break;
            }
        }
    }

    return QualityInspection;
};