Calculation/app/service/advance.js

'use strict';

const auditConst = require('../const/audit').advance;
const advanceConst = require('../const/advance');
const path = require('path');
const fs = require('fs');

module.exports = app => {
    class Advance extends app.BaseService {
        constructor(ctx) {
            super(ctx);
            this.tableName = 'advance_pay';
        }
        /**
         * 获取预付款列表
         * @param {Number} tid - 标段id
         * @param {Number} type - 预付款类型
         * @param {Number} decimal - 小数位数
         * @param {Number} advancePayTotal - 预付款总额
         */
        async getAdvanceList(tid, type, decimal, advancePayTotal) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tid', {
                value: tid,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('type', {
                value: type,
                operate: '=',
            });
            if (this.ctx.session.sessionUser.accountId !== this.ctx.tender.data.user_id && !this.ctx.tender.isTourist && !this.ctx.session.sessionUser.is_admin) {
                this.sqlBuilder.setAndWhere('status', {
                    value: auditConst.status.uncheck,
                    operate: '!=',
                });
            }
            this.sqlBuilder.orderBy = [['order', 'desc']];
            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            const advance = await this.db.query(sql, sqlParam);
            let isLastItem = false;
            for (const item of advance) {
                let s1,
                    s3;
                const s2 = item.prev_amount.toString().split('.')[1];
                isLastItem = this.ctx.helper.add(item.cur_amount, item.prev_amount) === advancePayTotal;
                item.pay_ratio = this.ctx.helper.mul(this.ctx.helper.div(item.cur_amount, advancePayTotal), 100, 2) || 0;
                if (item.status === auditConst.status.uncheck || item.status === auditConst.status.checkNo) {
                    const cur_amount = item.cur_amount || 0;
                    s1 = parseFloat(cur_amount).toString().split('.')[1];
                    s3 = parseFloat(this.ctx.helper.add(cur_amount, item.prev_amount)).toString().split('.')[1];
                    isLastItem && (s3 = advancePayTotal.toString().split('.')[1]);
                    item.cur_amount = this.ctx.helper.formatMoney(cur_amount, ',', isLastItem ? s1 && s1.length || 0 : 2);
                    item.prev_total_amount = this.ctx.helper.formatMoney(this.ctx.helper.add(cur_amount, item.prev_amount), ',', isLastItem ? s3 && s3.length || 0 : 2);
                } else {
                    s1 = item.cur_amount && item.cur_amount.toString().split('.')[1];
                    s3 = item.prev_total_amount.toString().split('.')[1];
                    item.cur_amount = this.ctx.helper.formatMoney(item.cur_amount, ',', s1 && s1.length || 0);
                    item.prev_total_amount = this.ctx.helper.formatMoney(item.prev_total_amount, ',', s3 && s3.length || 0);
                }
                item.prev_amount = this.ctx.helper.formatMoney(item.prev_amount, ',', s2 && s2.length || 0);
                item.curAuditor = await this.ctx.service.advanceAudit.getAuditorByStatus(item.id, item.status, item.times);
                if (item.status === auditConst.status.checkNoPre) {
                    item.curAuditor2 = await this.ctx.service.advanceAudit.getAuditorByStatus(item.id, auditConst.status.checking);
                }
                item.fileList = await this.ctx.service.advanceFile.getAdvanceFiles({ vid: item.id });
            }
            return advance;
        }

        // 获取预付款列表（直接获取，报表用）
        async getAdvanceListDirectly(tid, type = null) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tid', {
                value: tid,
                operate: '=',
            });
            if (type) {
                this.sqlBuilder.setAndWhere('type', {
                    value: type,
                    operate: '=',
                });
            }
            if (this.ctx.session.sessionUser.accountId !== this.ctx.tender.data.user_id && !this.ctx.tender.isTourist) {
                this.sqlBuilder.setAndWhere('status', {
                    value: auditConst.status.uncheck,
                    operate: '!=',
                });
            }
            this.sqlBuilder.orderBy = [['order', 'desc']];
            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            // console.log(sql);
            // console.log(sqlParam);
            const advance = await this.db.query(sql, sqlParam);
            return advance;
        }

        /**
         * 获取预付款最新一期
         * @param {Number} tid 标段id
         * @param {String} type 类型: 开工预付款|材料预付款 (0|1)
         * @param {Boolean} includeUnCheck 包括未上报的
         * @return {Promise<*>} 实例结果集
         */
        async getLastestAdvance(tid, type, includeUnCheck = false) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tid', {
                value: tid,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('type', {
                value: type,
                operate: '=',
            });
            if (!includeUnCheck) {
                this.sqlBuilder.setAndWhere('status', {
                    value: auditConst.status.uncheck,
                    operate: '!=',
                });
            }
            this.sqlBuilder.orderBy = [['order', 'desc']];
            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            const advance = await this.db.queryOne(sql, sqlParam);
            return advance;
        }

        async getSumAdvance(tid) {
            const sql = 'Select sum(cur_amount) as tp From ' + this.tableName + ' where tid = ?';
            const result = await this.db.queryOne(sql, [tid]);
            return result ? result.tp || 0 : 0;
        }

        /**
         * 创建一条新的记录
         * @param {String} type 类型: 开工预付款|材料预付款 (start|material)
         * @return {Promise<*>} 插入结果集
         */
        async createRecord(type) {
            const { ctx } = this;
            const uid = ctx.session.sessionUser.accountId;
            const tid = ctx.tender.id;
            let latestOrder = await this.getLastestAdvance(tid, type, true);
            if (latestOrder && latestOrder.status === auditConst.status.uncheck) {
                return '';
            }
            if (!latestOrder) {
                latestOrder = {
                    order: 1,
                    prev_amount: 0,
                    prev_total_amount: 0,
                };
            } else {
                latestOrder.order = latestOrder.order + 1;
            }
            const record = await this.db.insert(this.tableName, { type, uid, tid, status: auditConst.status.uncheck, order: latestOrder.order, prev_amount: latestOrder.prev_total_amount, prev_total_amount: latestOrder.prev_total_amount, pay_time: new Date() });
            const auditors = await ctx.service.advanceAudit.getAuditGroupByList(latestOrder.id, latestOrder.times);
            for (let idx = 0; idx < auditors.length; idx++) {
                const { audit_id } = auditors[idx];
                await ctx.service.advanceAudit.db.insert(ctx.service.advanceAudit.tableName, {
                    tid: latestOrder.tid, audit_id, type: latestOrder.type, vid: record.insertId, times: 1, order: idx + 1, status: 1, create_time: new Date(),
                });
            }
            // auditors.forEach(async (auditor, idx) => {
            //     const { audit_id } = auditor;
            //     await ctx.service.advanceAudit.db.insert(ctx.service.advanceAudit.tableName, {
            //         tid: latestOrder.tid, audit_id, type: latestOrder.type, vid: record.insertId, times: 1, order: idx + 1, status: 1, create_time: new Date(),
            //     });
            // });
            return await this.getDataById(record.insertId);
        }

        /**
         * 获取上一期预付款记录
         * @param {Number} tid 标段id
         * @param {Number} type 预付款类型
         */
        async getPreviousRecord(tid, type) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tid', {
                value: tid,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('type', {
                value: type,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('order', {
                value: this.ctx.advance.order - 1,
                operate: '=',
            });
            this.sqlBuilder.orderBy = [['order', 'desc']];
            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            return await this.db.queryOne(sql, sqlParam);
        }

        /**
         * 计算列表进度条所需数据
         * @param {Object} latest 最新期的数据
         * @param {Number} payTotal 预付款金额总数
         * @return {Object} progress所需数据
         */
        async calcProgress(latest, payTotal) {
            const { ctx } = this;
            if (!latest) {
                latest = { prev_total_amount: 0, prev_amount: 0, cur_amount: 0 };
            }
            const surplus = ctx.helper.sub(payTotal, latest.prev_total_amount || 0);
            const p_ratio = ctx.helper.mul(ctx.helper.div(latest.prev_amount || 0, payTotal), 100, 2); // 截止上期金额总数
            const c_ratio = ctx.helper.mul(ctx.helper.div(latest.cur_amount || 0, payTotal), 100, 2); // 截止本期金额总数
            const s_ratio = ctx.helper.mul(ctx.helper.div(surplus, payTotal), 100, 2); // 剩余金额总数
            return {
                p_amount: latest.prev_amount,
                c_amount: latest.cur_amount,
                s_amount: surplus,
                p_ratio,
                c_ratio,
                s_ratio,
            };
        }

        /**
         * 更新预付款记录
         * @param {Object} payload 载荷
         * @param {Number} id 预付款id
         */
        async updateAdvance(payload, id) {
            const { ctx } = this;
            if (!payload.pay_time) {
                const prevRecord = await this.getPreviousRecord(ctx.tender.id, ctx.advance.type) || { prev_total_amount: 0 };
                const { cur_amount } = payload;
                payload.prev_total_amount = ctx.helper.add(cur_amount, prevRecord.prev_total_amount);
            } else {
                payload.pay_time = new Date(payload.pay_time);
            }
            return await this.update(payload, {
                id,
            });
        }

        /**
         * 删除预付款记录
         * @param {String} id 预付款id
         * @param {String} tid 标段id
         */
        async deleteAdvance(id, tid) {
            const transaction = await this.db.beginTransaction();
            try {
                // 删除预付款记录
                await transaction.delete(this.tableName, { id, tid });
                // 删除附件
                const fileInfo = await this.db.select(this.ctx.service.advanceFile.tableName, { where: { vid: id, tid } });
                await transaction.delete(this.ctx.service.advanceFile.tableName, { vid: id, tid });
                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.advanceAudit.tableName, { vid: id, tid });
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        async getListByArchives(tid, ids) {
            if (ids.length === 0) return [];
            const sql = 'SELECT c.* FROM ?? as c LEFT JOIN (SELECT vid, MAX(end_time) as end_time FROM ?? WHERE ' +
                'tid = ? AND vid in (' + this.ctx.helper.getInArrStrSqlFilter(ids) + ') GROUP BY vid) as ca ON c.id = ca.vid WHERE' +
                ' c.tid = ? AND c.id in (' + this.ctx.helper.getInArrStrSqlFilter(ids) + ') AND c.status = ? ORDER BY ca.end_time DESC';
            const params = [this.tableName, this.ctx.service.advanceAudit.tableName, tid, tid, auditConst.status.checked];
            const list = await this.db.query(sql, params);
            // 再按预付款顺序再进行一次排列
            const advances = [];
            for (const tc of advanceConst.typeCol) {
                const typeList = list.filter(item => item.type === tc.type);
                advances.push(...typeList);
            }
            return advances;
        }
    }
    return Advance;
};