Calculation/app/service/financial_pay.js

'use strict';

/**
 * Created by EllisRan on 2020/3/3.
 */

const BaseService = require('../base/base_service');
const auditConst = require('../const/audit').financial;
const auditType = require('../const/audit').auditType;
const shenpiConst = require('../const/shenpi');

module.exports = app => {

    class FinancialPay extends BaseService {

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

        async getOnePay(id) {
            const pay = await this.getDataById(id);
            const tender = await this.ctx.service.tender.getDataById(pay.tid);
            pay.tenderName = tender.name;
            const payStage = pay.fpsid ? await this.ctx.service.financialPayStage.getOnePayStage(pay.fpsid) : null;
            pay.payStage = payStage ? payStage : null;
            return pay;
        }

        /**
         * 获取列表
         * @param {int} spid - 项目id
         * @param {int} status - 状态
         * @param {int} hadlimit - 分页
         * @return {object} list - 列表
         */
        async getListByStatus(spid, allfpsid = null, fpsid = null, status = 0, tid = null, used = null, hadlimit = 0, sortBy = '', orderBy = '') {
            let addSql = '';
            if (fpsid !== null) {
                fpsid = fpsid instanceof Array ? fpsid : [fpsid];
                addSql += ' AND a.fpsid in (' + this.ctx.helper.getInArrStrSqlFilter(fpsid) + ')';
            } else if (!this.ctx.session.sessionUser.is_admin && this._.includes([0, 5, 3], status)) {
                addSql += ` AND (a.id in (SELECT b.fpid FROM ${this.ctx.service.financialPayAudit.tableName} as b WHERE b.spid = '${spid}' AND b.aid = ${this.ctx.session.sessionUser.accountId}) OR a.uid = ${this.ctx.session.sessionUser.accountId}`;
                if (allfpsid !== null) {
                    allfpsid = allfpsid instanceof Array ? allfpsid : [allfpsid];
                    addSql += allfpsid.length > 0 ? ' OR a.fpsid in (' + this.ctx.helper.getInArrStrSqlFilter(allfpsid) + ')' : '';
                }
                addSql += ')';
            }
            if (tid !== null) {
                if (tid.length === 0) {
                    return [];
                }
                addSql += ' AND a.tid in (' + this.ctx.helper.getInArrStrSqlFilter(tid) + ')';
            }
            if (used) {
                addSql += ' AND a.used = "' + used + '"';
            }
            let sql = '';
            let sqlParam = '';
            switch (status) {
                case 0: // 所有
                    sql = 'SELECT a.* FROM ?? As a WHERE a.spid = ?' + addSql;
                    sqlParam = [this.tableName, spid];
                    break;
                case 1: // 待处理(你的)
                    sql = 'SELECT a.* FROM ?? as a WHERE a.spid = ?' + addSql + ' AND (a.id in(SELECT b.fpid FROM ?? as b WHERE b.spid = ? AND b.aid = ? AND b.status = ?) OR (a.uid = ? AND (a.status = ? OR a.status = ?)))';
                    sqlParam = [this.tableName, spid, this.ctx.service.financialPayAudit.tableName, spid, this.ctx.session.sessionUser.accountId, auditConst.status.checking, this.ctx.session.sessionUser.accountId, auditConst.status.uncheck, auditConst.status.checkNo];
                    break;
                case 5: // 待上报(所有的)PS：取未上报，退回，修订的变更令
                    sql =
                        'SELECT a.* FROM ?? as a WHERE a.spid = ?' + addSql + ' AND (a.status = ? OR a.status = ?)';
                    sqlParam = [
                        this.tableName,
                        spid,
                        auditConst.status.uncheck,
                        auditConst.status.checkNo,
                    ];
                    break;
                case 2: // 进行中（所有的）
                case 4: // 终止（所有的）
                    sql = 'SELECT a.* FROM ?? as a WHERE ' +
                        'a.status = ? AND a.spid = ?' + addSql + ' AND (a.uid = ? OR ' +
                        'a.id IN (SELECT b.fpid FROM ?? AS b WHERE b.aid = ? GROUP BY b.fpid))';
                    sqlParam = [this.tableName, status, spid, this.ctx.session.sessionUser.accountId,
                        this.ctx.service.financialPayAudit.tableName, this.ctx.session.sessionUser.accountId];
                    break;
                case 3: // 已完成（所有的）
                    sql = 'SELECT a.* FROM ?? as a WHERE a.status = ? AND a.spid = ?' + addSql;
                    sqlParam = [this.tableName, status, spid];
                    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(spid, allfpsid = null, fpsid = null, status = 0, tid = null, used = null) {
            let addSql = '';
            if (fpsid !== null) {
                fpsid = fpsid instanceof Array ? fpsid : [fpsid];
                addSql += ' AND a.fpsid in (' + this.ctx.helper.getInArrStrSqlFilter(fpsid) + ')';
            } else if (!this.ctx.session.sessionUser.is_admin && this._.includes([0, 5, 3], status)) {
                addSql += ` AND (a.id in(SELECT b.fpid FROM ${this.ctx.service.financialPayAudit.tableName} as b WHERE b.spid = '${spid}' AND b.aid = ${this.ctx.session.sessionUser.accountId}) OR a.uid = ${this.ctx.session.sessionUser.accountId}`;
                if (allfpsid !== null) {
                    allfpsid = allfpsid instanceof Array ? allfpsid : [allfpsid];
                    addSql += allfpsid.length > 0 ? ' OR a.fpsid in (' + this.ctx.helper.getInArrStrSqlFilter(allfpsid) + ')' : '';
                }
                addSql += ')';
            }
            if (tid !== null) {
                if (tid.length === 0) {
                    return 0;
                }
                addSql += ' AND a.tid in (' + this.ctx.helper.getInArrStrSqlFilter(tid) + ')';
            }
            if (used) {
                addSql += ' AND a.used = "' + used + '"';
            }
            switch (status) {
                case 0: // 所有
                    const sql5 = 'SELECT count(*) AS count FROM ?? As a WHERE a.spid = ?' + addSql;
                    const sqlParam5 = [this.tableName, spid];
                    const result5 = await this.db.query(sql5, sqlParam5);
                    return result5[0].count;
                case 1: // 待处理(你的)
                    const sql6 = 'SELECT count(*) AS count FROM ?? as a WHERE a.spid = ?' + addSql + ' AND (a.id in(SELECT b.fpid FROM ?? as b WHERE b.spid = ? AND b.aid = ? AND b.status = ?) OR (a.uid = ? AND (a.status = ? OR a.status = ?)))';
                    const sqlParam6 = [this.tableName, spid, this.ctx.service.financialPayAudit.tableName, spid, this.ctx.session.sessionUser.accountId, auditConst.status.checking, this.ctx.session.sessionUser.accountId, auditConst.status.uncheck, auditConst.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.spid = ?' + addSql + ' AND (a.status = ? OR a.status = ?)';
                    const sqlParam2 = [
                        this.tableName,
                        spid,
                        auditConst.status.uncheck,
                        auditConst.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.spid = ?' + addSql + ' AND (a.uid = ? OR a.id IN (SELECT b.fpid FROM ?? AS b WHERE b.aid = ? GROUP BY b.fpid))';
                    const sqlParam3 = [this.tableName, status, spid, this.ctx.session.sessionUser.accountId, this.ctx.service.financialPayAudit.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 ?? as a WHERE a.status = ? AND a.spid = ?' + addSql;
                    const sqlParam4 = [this.tableName, status, spid];
                    const result4 = await this.db.query(sql4, sqlParam4);
                    return result4[0].count;
                default:
                    break;
            }
        }

        async getUserTenderList(spid, uid, allFpsidList, fpsid = null) {
            let addSql = '';
            let notAdminSql = '';
            if (fpsid) {
                addSql += ' AND a.fpsid = ' + fpsid;
            }
            if (!this.ctx.session.sessionUser.is_admin) {
                notAdminSql += '(a.tid in (SELECT tid FROM ' + this.ctx.service.financialPayAudit.tableName + ' WHERE spid = "' + spid + '" AND aid = ' + this.ctx.session.sessionUser.accountId + (fpsid ? ' AND fpsid = ' + fpsid : '') + ' GROUP BY tid)';
                if (!fpsid && allFpsidList && allFpsidList.length > 0) {
                    notAdminSql += ' OR a.tid in (SELECT tid FROM ' + this.tableName + ' WHERE spid = "' + spid + '" AND fpsid in (' + this.ctx.helper.getInArrStrSqlFilter(allFpsidList) + ') GROUP BY tid)';
                }
                notAdminSql += ')';
                addSql += ' AND (a.uid = ' + uid + ' OR ' + notAdminSql + ')';
            }
            const sql = 'SELECT a.tid, t.name FROM ?? As a LEFT JOIN ?? As t ON a.tid = t.id' +
                ' WHERE a.spid = ?' + addSql + ' GROUP BY a.tid';
            const sqlParam = [this.tableName, this.ctx.service.tender.tableName, spid];
            return await this.db.query(sql, sqlParam);
        }

        async addPay(spid, payStage, data) {
            if (!data.tid || !data.code || !data.used) {
                throw '参数错误';
            }
            const times = new Date();
            const transaction = await this.db.beginTransaction();
            try {
                const info = await this.ctx.service.financialPay.getDataByCondition({ spid, tid: data.tid, code: data.code });
                if (info) {
                    throw '支付编号已存在';
                }
                const insertData = {
                    spid,
                    fpsid: payStage.id,
                    tid: data.tid,
                    code: data.code,
                    used: data.used,
                    uid: this.ctx.session.sessionUser.accountId,
                    status: auditConst.status.uncheck,
                    times: 1,
                    create_time: times,
                };
                const payTender = await this.ctx.service.financialPayTender.getDataByCondition({ spid, tid: data.tid });
                if (payTender) {
                    insertData.entity = payTender.name;
                    insertData.bank = payTender.bank;
                    insertData.bank_account = payTender.bank_account;
                }
                const result = await transaction.insert(this.tableName, insertData);
                // 添加审批流程
                const auditList = await this.ctx.service.shenpiAudit.getAuditList(data.tid, shenpiConst.sp_other_type.financial, shenpiConst.sp_status.gdspl);
                const insertAuditDatas = [];
                for (const audit of auditList) {
                    insertAuditDatas.push({
                        spid,
                        tid: data.tid,
                        fpid: result.insertId,
                        fpsid: payStage.id,
                        aid: audit.audit_id,
                        order: audit.audit_order,
                        times: 1,
                        status: auditConst.status.uncheck,
                        audit_type: audit.audit_type,
                        audit_order: audit.audit_order,
                    });
                }
                if (insertAuditDatas.length > 0) await transaction.insert(this.ctx.service.financialPayAudit.tableName, insertAuditDatas);
                if (payStage.can_del === 1) await transaction.update(this.ctx.service.financialPayStage.tableName, { id: payStage.id, can_del: 0 });
                await transaction.commit();
                return { id: result.insertId };
            } catch (e) {
                await transaction.rollback();
                throw e;
            }
        }

        async delPay(fpid) {
            if (!fpid) {
                throw '参数有误';
            }
            const node = await this.getDataById(fpid);
            if (!node) {
                throw '资金支付不存在';
            }
            const transaction = await this.db.beginTransaction();
            try {
                await transaction.delete(this.tableName, { id: node.id });
                // 删除合同附件
                const attList = await this.ctx.service.financialPayAtt.getAllDataByCondition({ where: { fpid } });
                await this.ctx.helper.delFiles(attList);
                await transaction.delete(this.ctx.service.financialPayAtt.tableName, { fpid });
                await transaction.delete(this.ctx.service.financialPayAudit.tableName, { fpid });
                await transaction.delete(this.ctx.service.financialPayContract.tableName, { fpid });
                if (node.fpsid) {
                    const payStage = await this.ctx.service.financialPayStage.getDataById(node.fpsid);
                    await this.ctx.service.financialPayStage.updatePayStageAndAfter(transaction, payStage.spid, payStage);
                }
                await transaction.commit();
            } catch (err) {
                console.log(err);
                await transaction.rollback();
                throw err;
            }
            return true;
        }

        async savePay(fpid, postData) {
            // 初始化事务
            const transaction = await this.db.beginTransaction();
            let result = false;
            try {
                const updateData = {
                    id: fpid,
                };
                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;
        }

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

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

        async loadPayUser(pay) {
            const status = auditConst.status;
            const accountId = this.ctx.session.sessionUser.accountId;
            pay.permission = await this.ctx.service.subProjPermission.getFinancailPermission(this.ctx.subProject.permission.fund_trans_permission, this.ctx.subProject.permission.fund_pay_permission);
            pay.user = await this.ctx.service.projectAccount.getAccountInfoById(pay.uid);
            pay.auditors = await this.ctx.service.financialPayAudit.getAuditors(pay.id, pay.times); // 全部参与的审批人
            pay.auditorIds = this._.map(pay.auditors, 'aid');
            pay.curAuditors = pay.auditors.filter(x => { return x.status === status.checking; }); // 当前流程中审批中的审批人
            pay.curAuditorIds = this._.map(pay.curAuditors, 'aid');
            pay.flowAuditors = pay.curAuditors.length > 0 ? pay.auditors.filter(x => { return x.order === pay.curAuditors[0].order; }) : []; // 当前流程中参与的审批人（包含会签时，审批通过的人）
            pay.flowAuditorIds = this._.map(pay.flowAuditors, 'aid');
            pay.nextAuditors = pay.curAuditors.length > 0 ? pay.auditors.filter(x => { return x.order === pay.curAuditors[0].order + 1; }) : [];
            pay.nextAuditorIds = this._.map(pay.nextAuditors, 'aid');
            pay.auditorGroups = this.ctx.helper.groupAuditors(pay.auditors, 'order', true);
            pay.userGroups = this.ctx.helper.groupAuditorsUniq(pay.auditorGroups);
            pay.userGroups.unshift([{
                aid: pay.user.id, order: 0, times: pay.times, audit_order: 0, audit_type: auditType.key.common,
                name: pay.user.name, role: pay.user.role, company: pay.user.company,
            }]);
            pay.finalAuditorIds = pay.userGroups[pay.userGroups.length - 1].map(x => { return x.aid; });
        }

        async batchOldPays(spid, payStage, payIds) {
            if (!payStage || !payIds || payIds.length === 0) {
                throw '参数有误';
            }
            const transaction = await this.db.beginTransaction();
            try {
                const updateAuditDatas = [];
                const updateDatas = [];
                const oldPays = await this.getAllDataByCondition({ where: { id: payIds } });
                const oldPayAudits = await this.ctx.service.financialPayAudit.getAllDataByCondition({ where: { fpid: payIds } });
                for (const node of oldPays) {
                    if (node.fpsid) {
                        throw '该支付已绑定单位期';
                    }
                    updateDatas.push({
                        id: node.id,
                        fpsid: payStage.id,
                    });
                    const oldPayAuditsByFpid = oldPayAudits.filter(x => { return x.fpid === node.id; });
                    for (const audit of oldPayAuditsByFpid) {
                        if (!audit.fpsid) {
                            updateAuditDatas.push({
                                id: audit.id,
                                fpsid: payStage.id,
                            });
                        }
                    }
                }
                if (updateDatas.length > 0) {
                    await transaction.updateRows(this.tableName, updateDatas);
                }
                if (updateAuditDatas.length > 0) {
                    await transaction.updateRows(this.ctx.service.financialPayAudit.tableName, updateAuditDatas);
                }
                // 更新本单位期金额和同单位的上期金额
                await transaction.update(this.ctx.service.financialPayStage.tableName, { id: payStage.id, can_del: 0 });
                await this.ctx.service.financialPayStage.updatePayStageAndAfter(transaction, spid, payStage);
                await transaction.commit();
                return true;
            } catch (err) {
                console.log(err);
                await transaction.rollback();
                throw err;
            }
        }
    }
    return FinancialPay;
};