Calculation/app/service/material_list.js

'use strict';

/**
 * 调差清单关联工料表 数据模型
 *
 * @author Mai
 * @date 2018/8/13
 * @version
 */

const auditConst = require('../const/audit').material;

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

        /**
         * 添加工料清单关联
         * @return {void}
         */
        async add(data, ms_id = null) {
            if (!this.ctx.tender || !this.ctx.material) {
                throw '数据错误';
            }
            const list = [];
            for (const mb of data.mb_id) {
                const newLists = {
                    tid: this.ctx.tender.id,
                    order: this.ctx.material.order,
                    mid: this.ctx.material.id,
                    mb_id: mb,
                    gcl_id: data.gcl_id,
                    xmj_id: data.xmj_id,
                    mx_id: data.mx_id,
                    contract_qty: data.contract_qty,
                    qc_qty: data.qc_qty,
                    qc_minus_qty: data.qc_minus_qty,
                    gather_qty: data.gather_qty,
                    is_join: data.is_join,
                    is_self: 1,
                    ms_id: ms_id ? ms_id : null,
                    in_time: new Date(),
                };
                list.push(newLists);
            }

            // 新增工料
            const result = await this.db.insert(this.tableName, list);
            if (result.affectedRows === 0) {
                throw '新增工料数据失败';
            }
            return await this.getMaterialData(this.ctx.tender.id, this.ctx.material.id);
        }

        /**
         * 删除工料清单关联
         * @param {int} id 工料id
         * @return {void}
         */
        async del(id, mb_id, ms_id = null) {
            if (!this.ctx.tender || !this.ctx.material) {
                throw '数据错误';
            }
            const transaction = await this.db.beginTransaction();
            try {
                // 判断是否可删
                await transaction.delete(this.tableName, { id });
                await this.calcQuantityByML(transaction, mb_id, ms_id);
                await transaction.commit();
                return true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 修改工料清单关联信息
         * @param {Object} data 工料内容
         * @param {int} order 期数
         * @return {void}
         */
        async save(data, ms_id = null) {
            if (!this.ctx.tender || !this.ctx.material) {
                throw '数据错误';
            }

            const transaction = await this.db.beginTransaction();
            try {
                const mb_id = data.mb_id;
                delete data.mb_id;
                await transaction.update(this.tableName, data);
                await this.calcQuantityByML(transaction, mb_id, ms_id);
                await transaction.commit();
                return true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 修改工料信息
         * @param {Object} data 工料内容
         * @return {void}
         */
        async saveDatas(datas, ms_id = null) {
            if (!this.ctx.tender || !this.ctx.material) {
                throw '数据错误';
            }
            // 判断是否可修改
            // 判断t_type是否为费用
            const transaction = await this.db.beginTransaction();
            try {
                for (const data of datas) {
                    const mb_id = data.mb_id;
                    delete data.mb_id;
                    await transaction.update(this.tableName, data);
                    await this.calcQuantityByML(transaction, mb_id, ms_id);
                }
                await transaction.commit();
                return true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 应用工料清单到其它清单中
         * @return {void}
         */
        async addOther(data) {
            if (!this.ctx.tender || !this.ctx.material) {
                throw '数据错误';
            }
            const transaction = await this.db.beginTransaction();
            try {
                // 先删除addxmj里所有的清单工料再添加新工料，并重新计算每个工料的单价数量
                // 还要找出删除的工料，更新单价数量
                const list = [];
                const delList = [];
                const materialBills = data.materialBills;
                const mb_idList = [];
                for (const xmj of data.addXmj) {
                    const mlList = await this.getAllDataByCondition({
                        where: {
                            mid: this.ctx.material.id,
                            gcl_id: xmj.gcl_id,
                            xmj_id: xmj.id,
                            mx_id: xmj.mx_id,
                        },
                    });
                    const mbIdList = this._.map(mlList, 'mb_id');
                    mb_idList.push(...mbIdList);
                    delList.push(...this._.map(mlList, 'id'));
                    for (const mb of materialBills) {
                        const newLists = {
                            tid: this.ctx.tender.id,
                            order: mb.order,
                            mid: this.ctx.material.id,
                            mb_id: mb.mb_id,
                            gcl_id: xmj.gcl_id,
                            xmj_id: xmj.id,
                            mx_id: xmj.mx_id,
                            gather_qty: xmj.gather_qty ? xmj.gather_qty : null,
                            quantity: mb.quantity,
                            expr: mb.expr,
                            in_time: new Date(),
                        };
                        list.push(newLists);
                        mb_idList.push(mb.mb_id);
                    }
                }
                // 删除工料清单关联
                if (delList.length > 0) await transaction.delete(this.tableName, { id: delList });
                // 新增工料清单关联
                if (list.length > 0) {
                    const result = await transaction.insert(this.tableName, list);
                    if (result.affectedRows === 0) {
                        throw '新增工料数据失败';
                    }
                }
                // 重算工料和总金额
                const calcMBIdList = this._.uniq(mb_idList);
                if (calcMBIdList.length > 0) {
                    for (const select of calcMBIdList) {
                        await this.calcQuantityByML(transaction, select);
                    }
                }
                // throw 'fail';
                // await this.calcQuantityByML(transaction, select.mb_id);
                await transaction.commit();
                return await this.getMaterialData(this.ctx.tender.id, this.ctx.material.id);
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 修改material_bills的quantity值和计算本期金额
         * @param transaction
         * @param mb_id
         * @return {Promise<*>}
         */
        async calcQuantityByML(transaction, mb_id, ms_id = null, updateAllStage = '') {
            // 修改material_bills值
            const mbInfo = await this.ctx.service.materialBills.getDataById(mb_id);
            if (!mbInfo) {
                throw '不存在该工料';
            }
            let m_spread = mbInfo.m_spread;
            let updateId = mb_id;
            if (ms_id) {
                const msbInfo = await this.ctx.service.materialStageBills.getDataByCondition({ mid: this.ctx.material.id, mb_id, ms_id });
                m_spread = msbInfo.m_spread;
                updateId = msbInfo.id;
            }
            const newQuantity = await this.getMbQuantity(transaction, this.ctx.material.id, this.ctx.material.qty_source, this.ctx.material.decimal.qty, mb_id, ms_id);
            const newTp = this.ctx.helper.round(this.ctx.helper.mul(newQuantity, m_spread), this.ctx.material.decimal.tp);
            const updateData = {
                id: updateId,
                quantity: newQuantity,
                m_tp: newTp,
                m_tax_tp: this.ctx.helper.round(this.ctx.helper.mul(newTp, (1 + this.ctx.helper.div(mbInfo.m_tax, 100))), this.ctx.material.decimal.tp),
            };
            if (ms_id) {
                await transaction.update(this.ctx.service.materialStageBills.tableName, updateData);
                await this.ctx.service.materialStage.updateMtp(transaction, ms_id);
                if (updateAllStage === 'all') {
                    const updateDatas = [];
                    const updateMsIds = [];
                    const msbList = await transaction.select(this.ctx.service.materialStageBills.tableName, { where: { mid: this.ctx.material.id, mb_id } });
                    for (const msb of msbList) {
                        if (msb.ms_id !== parseInt(ms_id)) {
                            const newQuantity4 = await this.getMbQuantity(transaction, this.ctx.material.id, this.ctx.material.qty_source, this.ctx.material.decimal.qty, mb_id, msb.ms_id);
                            const newTp4 = this.ctx.helper.round(this.ctx.helper.mul(newQuantity4, msb.m_spread), this.ctx.material.decimal.tp);
                            const updateData4 = {
                                id: msb.id,
                                quantity: newQuantity4,
                                m_tp: newTp4,
                                m_tax_tp: this.ctx.helper.round(this.ctx.helper.mul(newTp4, (1 + this.ctx.helper.div(mbInfo.m_tax, 100))), this.ctx.material.decimal.tp),
                            };
                            updateDatas.push(updateData4);
                            updateMsIds.push(msb.ms_id);
                        }
                    }
                    if (updateDatas.length > 0) {
                        await transaction.updateRows(this.ctx.service.materialStageBills.tableName, updateDatas);
                        for (const msId of updateMsIds) {
                            await this.ctx.service.materialStage.updateMtp(transaction, msId);
                        }
                    }
                }
                // 还要更新bills表的m_tp和m_tax_tp值
                const sql3 = 'SELECT SUM(`m_tp`) as total_price, SUM(IF(`m_tax_tp` is null, `m_tp`, `m_tax_tp`)) as tax_total_price FROM ' + this.ctx.service.materialStageBills.tableName + ' WHERE `tid` = ? AND `mid` = ? AND `mb_id` = ? AND `is_summary` = 1';
                const sqlParam3 = [this.ctx.tender.id, this.ctx.material.id, mb_id];
                const tp3 = await transaction.queryOne(sql3, sqlParam3);
                const updateBillsData = {
                    id: mb_id,
                    m_tp: tp3.total_price,
                    m_tax_tp: tp3.tax_total_price,
                };
                await transaction.update(this.ctx.service.materialBills.tableName, updateBillsData);
            } else {
                await transaction.update(this.ctx.service.materialBills.tableName, updateData);
            }
            // 计算本期总金额
            const sql2 = 'SELECT SUM(`m_tp`) as total_price, SUM(IF(`m_tax_tp` is null, `m_tp`, `m_tax_tp`)) as tax_total_price FROM ' + this.ctx.service.materialBills.tableName + ' WHERE `tid` = ? AND `is_summary` = 1';
            const sqlParam2 = [this.ctx.tender.id];
            const tp = await transaction.queryOne(sql2, sqlParam2);
            console.log(tp);
            const updateData2 = {
                id: this.ctx.material.id,
                m_tp: tp.total_price,
                m_tax_tp: tp.tax_total_price,
            };
            // 计算建筑税价
            if (this.ctx.material.is_stage_self) {
                const materialStages = await transaction.select(this.ctx.service.materialStage.tableName, { where: { mid: this.ctx.material.id } });
                let rate_tp = 0;
                for (const ms of materialStages) {
                    const ms_rate_tp = this.ctx.helper.round(this.ctx.helper.mul(ms.m_tp, 1 + this.ctx.material.rate / 100), this.ctx.material.decimal.tp);
                    rate_tp = this.ctx.helper.add(rate_tp, ms_rate_tp);
                }
                updateData2.rate_tp = rate_tp;
            } else {
                updateData2.rate_tp = this.ctx.helper.round(this.ctx.helper.mul(tp.total_price, 1 + this.ctx.material.rate / 100), this.ctx.material.decimal.tp);
            }
            const result = await transaction.update(this.ctx.service.material.tableName, updateData2);
            // 找出当前人并更新tp_data
            const tp_data = await this.ctx.service.materialAudit.getTpData(transaction, this.ctx.material.id);
            if (this.ctx.material.status === auditConst.status.uncheck || this.ctx.material.status === auditConst.status.checkNo) {
                await transaction.update(this.ctx.service.material.tableName, {
                    id: this.ctx.material.id,
                    tp_data: JSON.stringify(tp_data),
                });
            } else if (this.ctx.material.curAuditor) {
                await transaction.update(this.ctx.service.materialAudit.tableName, {
                    id: this.ctx.material.curAuditor.id,
                    tp_data: JSON.stringify(tp_data),
                });
            }
            return result;
        }

        /**
         * 修改material_bills的quantity值和计算本期金额
         * @param transaction
         * @param mb_id
         * @return {Promise<*>}
         */
        async calcAllQuantityByML(transaction, mbIds) {
            const mbList = await transaction.select(this.ctx.service.materialBills.tableName, { where: { id: mbIds } });
            // 修改material_bills值
            for (const mbInfo of mbList) {
                if (!mbInfo) {
                    throw '不存在该工料';
                }
                if (this.ctx.material.is_stage_self) {
                    const updateDatas = [];
                    const updateMsIds = [];
                    const msbList = await transaction.select(this.ctx.service.materialStageBills.tableName, { where: { mid: this.ctx.material.id, mb_id: mbInfo.id } });
                    for (const msb of msbList) {
                        const newQuantity = await this.getMbQuantity(transaction, this.ctx.material.id, this.ctx.material.qty_source, this.ctx.material.decimal.qty, mbInfo.id, msb.ms_id);
                        const newTp = this.ctx.helper.round(this.ctx.helper.mul(newQuantity, msb.m_spread), this.ctx.material.decimal.tp);
                        const updateData = {
                            id: msb.id,
                            quantity: newQuantity,
                            m_tp: newTp,
                            m_tax_tp: this.ctx.helper.round(this.ctx.helper.mul(newTp, (1 + this.ctx.helper.div(mbInfo.m_tax, 100))), this.ctx.material.decimal.tp),
                        };
                        updateDatas.push(updateData);
                        updateMsIds.push(msb.ms_id);
                    }
                    if (updateDatas.length > 0) {
                        await transaction.updateRows(this.ctx.service.materialStageBills.tableName, updateDatas);
                        for (const msId of updateMsIds) {
                            await this.ctx.service.materialStage.updateMtp(transaction, msId);
                        }
                    }
                    // 还要更新bills表的m_tp和m_tax_tp值
                    const sql3 = 'SELECT SUM(`m_tp`) as total_price, SUM(IF(`m_tax_tp` is null, `m_tp`, `m_tax_tp`)) as tax_total_price FROM ' + this.ctx.service.materialStageBills.tableName + ' WHERE `tid` = ? AND `mid` = ? AND `mb_id` = ? AND `is_summary` = 1';
                    const sqlParam3 = [this.ctx.tender.id, this.ctx.material.id, mbInfo.id];
                    const tp3 = await transaction.queryOne(sql3, sqlParam3);
                    const updateBillsData = {
                        id: mbInfo.id,
                        m_tp: tp3.total_price,
                        m_tax_tp: tp3.tax_total_price,
                    };
                    await transaction.update(this.ctx.service.materialBills.tableName, updateBillsData);
                } else {
                    const newQuantity = await this.getMbQuantity(transaction, this.ctx.material.id, this.ctx.material.qty_source, this.ctx.material.decimal.qty, mbInfo.id);
                    const newTp = this.ctx.helper.round(this.ctx.helper.mul(newQuantity, mbInfo.m_spread), this.ctx.material.decimal.tp);
                    const updateData = {
                        id: mbInfo.id,
                        quantity: newQuantity,
                        m_tp: newTp,
                        m_tax_tp: this.ctx.helper.round(this.ctx.helper.mul(newTp, (1 + this.ctx.helper.div(mbInfo.m_tax, 100))), this.ctx.material.decimal.tp),
                    };
                    await transaction.update(this.ctx.service.materialBills.tableName, updateData);
                }
            }
            // 计算本期总金额
            const sql2 = 'SELECT SUM(`m_tp`) as total_price, SUM(IF(`m_tax_tp` is null, `m_tp`, `m_tax_tp`)) as tax_total_price FROM ' + this.ctx.service.materialBills.tableName + ' WHERE `tid` = ? AND `is_summary` = 1';
            const sqlParam2 = [this.ctx.tender.id];
            const tp = await transaction.queryOne(sql2, sqlParam2);
            console.log(tp);
            const updateData2 = {
                id: this.ctx.material.id,
                m_tp: tp.total_price,
                m_tax_tp: tp.tax_total_price,
            };
            return await transaction.update(this.ctx.service.material.tableName, updateData2);
        }

        /**
         * 获取工料清单关联表
         * @param {int} tid 标段id
         * @param {Object} mid 期id
         * @return {void}
         */
        async getMaterialData(tid, mid) {
            const sql = 'SELECT ml.`id`, mb.`code`, mb.`name`, mb.`unit`, ml.`order`, ml.`contract_qty`, ml.`qc_qty`, ml.`qc_minus_qty`, ml.`gather_qty`, ml.`quantity`, ml.`expr`, ml.`mb_id`, ml.`gcl_id`, ml.`xmj_id`, ml.`mx_id`, ml.`ms_id`, ml.`tid`, ml.`mid`, mb.m_spread, ml.ms_id, ml.is_join' +
                ' FROM ' + this.tableName + ' as ml' +
                ' LEFT JOIN ' + this.ctx.service.materialBills.tableName + ' as mb' +
                ' ON ml.`mb_id` = mb.`id`' +
                ' WHERE ml.`tid` = ? AND ml.`mid` = ?';
            const sqlParam = [tid, mid];
            return await this.db.query(sql, sqlParam);
        }

        async getPreMaterialData(tid, mid) {
            const sql = 'SELECT ml.`id`, mb.`code`, mb.`name`, mb.`unit`, ml.`order`, ml.`quantity`, ml.`expr`, ml.`mb_id`, ml.`gcl_id`, ml.`xmj_id`, ml.`mx_id`, ml.`ms_id`, ml.`tid`, ml.`mid`, mbh.m_spread, ml.ms_id, ml.is_join' +
                ' FROM ' + this.tableName + ' as ml' +
                ' LEFT JOIN ' + this.ctx.service.materialBills.tableName + ' as mb ON ml.`mb_id` = mb.`id`' +
                ' LEFT JOIN ' + this.ctx.service.materialBillsHistory.tableName + ' as mbh ON ml.`mb_id` = mbh.`mb_id` and mbh.mid = ?' +
                ' WHERE ml.`tid` = ? AND ml.`mid` = ?';
            const sqlParam = [mid, tid, mid];
            return await this.db.query(sql, sqlParam);
        }

        async getMaterialStageData(tid, mid) {
            const sql = 'SELECT ml.`id`, mb.`code`, mb.`name`, mb.`unit`, ml.`order`, ml.`quantity`, ml.`expr`, msb.id AS mb_id, ml.`gcl_id`, ml.`xmj_id`, ml.`mx_id`, ml.`ms_id`, ml.`tid`, ml.`mid`, msb.m_spread, ml.ms_id, ms.sid, ms.order as s_order, ml.is_join' +
                ' FROM ' + this.tableName + ' as ml' +
                ' LEFT JOIN ' + this.ctx.service.materialBills.tableName + ' as mb ON ml.`mb_id` = mb.`id`' +
                ' LEFT JOIN ' + this.ctx.service.materialStageBills.tableName + ' as msb ON ml.mb_id = msb.mb_id AND ml.ms_id = msb.ms_id' +
                ' LEFT JOIN ' + this.ctx.service.materialStage.tableName + ' as ms ON ml.`ms_id` = ms.`id`' +
                ' WHERE ml.`tid` = ? AND ml.`mid` = ?';
            const sqlParam = [tid, mid];
            return await this.db.query(sql, sqlParam);
        }

        async getPreMaterialStageData(tid, mid) {
            const sql = 'SELECT ml.`id`, mb.`code`, mb.`name`, mb.`unit`, ml.`order`, ml.`quantity`, ml.`expr`, msb.id AS mb_id, ml.`gcl_id`, ml.`xmj_id`, ml.`mx_id`, ml.`ms_id`, ml.`tid`, ml.`mid`, msb.m_spread, ml.ms_id, ms.sid, ms.order as s_order, ml.is_join' +
                ' FROM ' + this.tableName + ' as ml' +
                ' LEFT JOIN ' + this.ctx.service.materialBills.tableName + ' as mb ON ml.`mb_id` = mb.`id`' +
                ' LEFT JOIN ' + this.ctx.service.materialStageBills.tableName + ' as msb ON ml.`mb_id` = msb.mb_id AND ml.ms_id = msb.ms_id And ml.mid = msb.mid' +
                ' LEFT JOIN ' + this.ctx.service.materialStage.tableName + ' as ms ON ml.`ms_id` = ms.`id`' +
                ' WHERE ml.`tid` = ? AND ml.`mid` = ?';
            const sqlParam = [tid, mid];
            return await this.db.query(sql, sqlParam);
        }

        /**
         * 复制上一期并生成新一期清单工料关联，计算新一期小计值
         * @param transaction
         * @param preMaterial
         * @param newMid
         * @return {Promise<void>}
         */
        async copyPreMaterialList(transaction, preMaterial, newMaterial) {
            const materialListData = await this.getAllDataByCondition({ where: { tid: this.ctx.tender.id, mid: preMaterial.id } });
            const copyMLArray = [];
            for (const ml of materialListData) {
                // 获取小计值
                let qtys = null;
                if (ml.mx_id !== null && ml.mx_id !== '') {
                    qtys = await this.ctx.service.stagePos.getGatherQtyByMaterial(ml.tid, newMaterial.stage_id, ml.gcl_id, ml.mx_id);
                } else {
                    qtys = await this.ctx.service.stageBills.getGatherQtyByMaterial(ml.tid, newMaterial.stage_id, ml.gcl_id);
                }
                const newMaterialList = {
                    tid: ml.tid,
                    order: ml.order,
                    mid: newMaterial.id,
                    mb_id: ml.mb_id,
                    gcl_id: ml.gcl_id,
                    xmj_id: ml.xmj_id,
                    mx_id: ml.mx_id,
                    contract_qty: qtys ? qtys.contract_qty : null,
                    qc_qty: qtys ? qtys.qc_qty : null,
                    qc_minus_qty: qtys ? qtys.qc_minus_qty : null,
                    gather_qty: qtys ? qtys.gather_qty : null,
                    quantity: ml.quantity,
                    expr: ml.expr,
                    is_join: ml.is_join,
                    in_time: new Date(),
                };
                copyMLArray.push(newMaterialList);
            }
            return copyMLArray.length !== 0 ? await transaction.insert(this.tableName, copyMLArray) : true;
        }

        /**
         * 复制上一期并生成新一期清单工料关联，计算新一期小计值
         * @param transaction
         * @param preMaterial
         * @param newMid
         * @return {Promise<void>}
         */
        async copyPreMaterialList2(transaction, materialListData, materialSelfListData, notJoinList, newMaterial, materialStageData) {
            if (materialListData && materialListData.length > 0) {
                const copyMLArray = [];
                for (const ml of materialListData) {
                    const is_join = this._.find(notJoinList, { gcl_id: ml.gcl_id, xmj_id: ml.xmj_id, mx_id: ml.mx_id, type: 1 });
                    const is_change = this._.find(notJoinList, { gcl_id: ml.gcl_id, xmj_id: ml.xmj_id, mx_id: ml.mx_id, type: 2 });
                    const newMaterialList = {
                        tid: newMaterial.tid,
                        order: ml.order,
                        mid: newMaterial.id,
                        mb_id: ml.mb_id,
                        gcl_id: ml.gcl_id,
                        xmj_id: ml.xmj_id,
                        mx_id: ml.mx_id,
                        contract_qty: ml.contract_qty,
                        qc_qty: ml.qc_qty,
                        qc_minus_qty: ml.qc_minus_qty,
                        gather_qty: ml.gather_qty,
                        quantity: ml.quantity ? ml.quantity : 0,
                        expr: ml.expr ? ml.expr : '',
                        is_join: is_join ? 0 : is_change ? 2 : 1,
                        in_time: new Date(),
                    };
                    if (ml.sid) {
                        const ms = this._.find(materialStageData, { sid: ml.sid });
                        if (ms && ms.id) newMaterialList.ms_id = ms.id;
                    }
                    copyMLArray.push(newMaterialList);
                }
                if (copyMLArray.length !== 0) await transaction.insert(this.tableName, copyMLArray);
            }
            if (materialSelfListData && materialSelfListData.length > 0) {
                const copyMLArray2 = [];
                for (const ml of materialSelfListData) {
                    const is_join = this._.find(notJoinList, { gcl_id: ml.gcl_id, xmj_id: ml.xmj_id, mx_id: ml.mx_id, type: 1 });
                    const is_change = this._.find(notJoinList, { gcl_id: ml.gcl_id, xmj_id: ml.xmj_id, mx_id: ml.mx_id, type: 2 });
                    const newMaterialList = {
                        tid: newMaterial.tid,
                        order: ml.order,
                        mid: newMaterial.id,
                        mb_id: ml.mb_id,
                        gcl_id: ml.gcl_id,
                        xmj_id: ml.xmj_id,
                        mx_id: ml.mx_id,
                        contract_qty: ml.contract_qty,
                        qc_qty: ml.qc_qty,
                        qc_minus_qty: ml.qc_minus_qty,
                        gather_qty: ml.gather_qty,
                        quantity: ml.quantity ? ml.quantity : 0,
                        expr: ml.expr ? ml.expr : '',
                        is_join: is_join ? 0 : is_change ? 2 : 1,
                        is_self: 1,
                        in_time: new Date(),
                    };
                    if (ml.sid) {
                        const ms = this._.find(materialStageData, { sid: ml.sid });
                        if (ms && ms.id) newMaterialList.ms_id = ms.id;
                    }
                    copyMLArray2.push(newMaterialList);
                }
                if (copyMLArray2.length !== 0) await transaction.insert(this.tableName, copyMLArray2);
            }
        }


        /**
         * 添加工料清单关联(多清单对应)
         * @return {void}
         */
        async adds(datas, checklist = false) {
            if (!this.ctx.tender || !this.ctx.material) {
                throw '数据错误';
            }
            const transaction = await this.db.beginTransaction();
            try {
                const list = [];
                const listGcl = [];
                const uplist = [];
                const uplistGcl = [];
                // const delList = [];
                // const mb_idList = [];
                const selfList = await transaction.select(this.ctx.service.materialListSelf.tableName, { where: { tid: this.ctx.tender.id, mid: this.ctx.material.id } });
                const oldGclList = await transaction.select(this.ctx.service.materialListGcl.tableName, { where: { tid: this.ctx.tender.id } });
                const oldMaterialList = await transaction.select(this.ctx.service.materialList.tableName, { where: { tid: this.ctx.tender.id, mid: this.ctx.material.id } });
                for (const xmj of datas.xmjs) {
                    for (const mb of datas.mbIds) {
                        // // 旧数据兼容问题，要去删除相同已存在的工料
                        // const mlInfo = await this.getDataByCondition({
                        //     mid: this.ctx.material.id,
                        //     gcl_id: xmj.gcl_id,
                        //     xmj_id: xmj.xmj_id,
                        //     mx_id: xmj.mx_id ? xmj.mx_id : [null, ''],
                        //     mb_id: mb,
                        // });
                        // if (mlInfo) {
                        //     delList.push(mlInfo.id);
                        //     mb_idList.push(mb);
                        // }
                        const mbId = typeof mb === 'object' ? mb.id : mb;
                        const quantity = typeof mb === 'object' ? mb.quantity : 0;
                        if ((xmj.contract_qty || xmj.qc_qty || xmj.qc_minus_qty || xmj.gather_qty) && this._.findIndex(selfList, { gcl_id: xmj.gcl_id, xmj_id: xmj.xmj_id, mx_id: xmj.mx_id }) === -1) {
                            const mlInfo = this._.find(oldMaterialList, { gcl_id: xmj.gcl_id, xmj_id: xmj.xmj_id, mx_id: xmj.mx_id, mb_id: mbId, ms_id: xmj.ms_id ? xmj.ms_id : null });
                            if (mlInfo) {
                                uplist.push({ id: mlInfo.id, quantity, expr: '' });
                            } else {
                                const newLists = {
                                    tid: this.ctx.tender.id,
                                    order: this.ctx.material.order,
                                    mid: this.ctx.material.id,
                                    mb_id: mbId,
                                    gcl_id: xmj.gcl_id,
                                    xmj_id: xmj.xmj_id,
                                    mx_id: xmj.mx_id,
                                    ms_id: xmj.ms_id ? xmj.ms_id : null,
                                    contract_qty: xmj.contract_qty,
                                    qc_qty: xmj.qc_qty,
                                    qc_minus_qty: xmj.qc_minus_qty,
                                    gather_qty: xmj.gather_qty,
                                    quantity,
                                    in_time: new Date(),
                                    is_join: xmj.is_join,
                                };
                                list.push(newLists);
                            }
                        }
                        const gclIndex = this._.findIndex(oldGclList, { gcl_id: xmj.gcl_id, mb_id: mbId });
                        if (gclIndex === -1 && this._.findIndex(listGcl, { gcl_id: xmj.gcl_id, mb_id: mbId }) === -1) {
                            const newListGcl = {
                                tid: this.ctx.tender.id,
                                order: this.ctx.material.order,
                                mid: this.ctx.material.id,
                                mb_id: mbId,
                                gcl_id: xmj.gcl_id,
                                quantity,
                                expr: '',
                            };
                            listGcl.push(newListGcl);
                        } else if (gclIndex !== -1 && this._.findIndex(uplistGcl, { id: oldGclList[gclIndex].id }) === -1) {
                            uplistGcl.push({
                                id: oldGclList[gclIndex].id,
                                expr: '',
                                quantity,
                            });
                        }
                    }
                }
                // 维护list_gcl表


                // 删除工料清单关联
                // if (delList.length > 0) await transaction.delete(this.tableName, { id: delList });
                // 新增工料清单关联
                if (list.length > 0) {
                    await this.insertBigDatas(transaction, list);
                }
                if (listGcl.length > 0) {
                    const result2 = await transaction.insert(this.ctx.service.materialListGcl.tableName, listGcl);
                    if (result2.affectedRows === 0) {
                        throw '新增工料关联数据失败';
                    }
                }
                // 覆盖
                if (uplist.length > 0) {
                    await transaction.updateRows(this.tableName, uplist);
                }
                if (uplistGcl.length > 0) {
                    await transaction.updateRows(this.ctx.service.materialListGcl.tableName, uplistGcl);
                }
                if (checklist) {
                    await this.ctx.service.materialChecklist.updateHadBills(transaction, checklist.id, checklist.had_bills);
                }
                // 重算工料和总金额
                // const calcMBIdList = this._.uniq(mb_idList);
                // if (calcMBIdList.length > 0) {
                //     for (const select of calcMBIdList) {
                //         await this.calcQuantityByML(transaction, select);
                //     }
                // }
                if ((list.length > 0 || uplist.length > 0) && datas.export) {
                    await this.calcAllQuantityByML(transaction, this._.map(datas.mbIds, 'id'));
                }
                await transaction.commit();
                const gclList = await this.ctx.service.materialListGcl.getAllDataByCondition({ where: { tid: this.ctx.tender.id } });
                return checklist ? gclList : {
                    gclList,
                    materialListData: await this.getMaterialData(this.ctx.tender.id, this.ctx.material.id),
                };
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 删除工料清单关联(多清单对应)
         * @param {int} id 工料id
         * @return {void}
         */
        async dels(datas, checklist = false, fromCheckList = false, ms_id = null) {
            if (!this.ctx.tender || !this.ctx.material) {
                throw '数据错误';
            }
            const transaction = await this.db.beginTransaction();
            try {
                // 判断是否可删
                const listGcl = [];
                for (const xmj of datas.xmjs) {
                    await transaction.delete(this.tableName, { tid: this.ctx.tender.id, mid: this.ctx.material.id, mb_id: datas.mb_id, gcl_id: xmj.gcl_id, xmj_id: xmj.xmj_id, mx_id: xmj.mx_id, is_self: 0 });
                    if (this._.indexOf(listGcl, xmj.gcl_id) === -1) {
                        await transaction.delete(this.service.materialListGcl.tableName, { tid: this.ctx.tender.id, mid: this.ctx.material.id, mb_id: datas.mb_id, gcl_id: xmj.gcl_id });
                        listGcl.push(xmj.gcl_id);
                    }
                }
                // await transaction.delete(this.tableName, { id });
                await this.calcQuantityByML(transaction, datas.mb_id, ms_id, 'all');
                if (checklist) {
                    await this.ctx.service.materialChecklist.updateHadBills(transaction, checklist.id, checklist.had_bills);
                }
                await transaction.commit();
                // console.log(datas);
                const gclList = await this.ctx.service.materialListGcl.getAllDataByCondition({ where: { tid: this.ctx.tender.id } });
                return fromCheckList ? gclList : {
                    gclList,
                    materialListData: await this.getMaterialData(this.ctx.tender.id, this.ctx.material.id),
                };
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 修改工料清单关联信息(多清单对应)
         * @param {Object} data 工料内容
         * @param {int} order 期数
         * @return {void}
         */
        async saves(datas, checklist = false, ms_id = null) {
            if (!this.ctx.tender || !this.ctx.material) {
                throw '数据错误';
            }

            const transaction = await this.db.beginTransaction();
            try {
                const mb_id = datas.mb_id;
                const updateDatas = [];
                const updateListGcl = [];
                const listGcl = [];
                const selfList = await transaction.select(this.ctx.service.materialListSelf.tableName, { where: { tid: this.ctx.tender.id, mid: this.ctx.material.id } });
                for (const xmj of datas.xmjs) {
                    if (this._.findIndex(selfList, { gcl_id: xmj.gcl_id, xmj_id: xmj.xmj_id, mx_id: xmj.mx_id }) === -1) {
                        const udata = {
                            row: {
                                expr: datas.expr,
                                quantity: datas.quantity,
                            },
                            where: {
                                tid: this.ctx.tender.id,
                                mid: this.ctx.material.id,
                                mb_id,
                                gcl_id: xmj.gcl_id,
                                xmj_id: xmj.xmj_id,
                                mx_id: xmj.mx_id,
                            },
                        };
                        updateDatas.push(udata);
                    }
                    if (this._.indexOf(listGcl, xmj.gcl_id) === -1) {
                        listGcl.push(xmj.gcl_id);
                        updateListGcl.push({
                            row: {
                                expr: datas.expr,
                                quantity: datas.quantity,
                            },
                            where: {
                                tid: this.ctx.tender.id,
                                // mid: this.ctx.material.id,
                                mb_id,
                                gcl_id: xmj.gcl_id,
                            },
                        });
                    }
                }
                if (updateDatas.length > 0) await transaction.updateRows(this.tableName, updateDatas);
                if (updateListGcl.length > 0) await transaction.updateRows(this.service.materialListGcl.tableName, updateListGcl);
                await this.calcQuantityByML(transaction, mb_id, ms_id, 'all');
                await transaction.commit();
                const gclList = await this.ctx.service.materialListGcl.getAllDataByCondition({ where: { tid: this.ctx.tender.id } });
                return checklist ? gclList : {
                    gclList,
                    materialListData: await this.getMaterialData(this.ctx.tender.id, this.ctx.material.id),
                };
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 复制粘贴多工料信息(多清单对应)
         * @param {Object} data 工料内容
         * @return {void}
         */
        async savePastes(datas, checklist = false, ms_id = null) {
            if (!this.ctx.tender || !this.ctx.material) {
                throw '数据错误';
            }
            // 判断是否可修改
            // 判断t_type是否为费用
            const transaction = await this.db.beginTransaction();
            try {
                const selfList = await transaction.select(this.ctx.service.materialListSelf.tableName, { where: { tid: this.ctx.tender.id, mid: this.ctx.material.id } });
                for (const data of datas.pasteData) {
                    const updateDatas = [];
                    const updateListGcl = [];
                    const listGcl = [];
                    for (const xmj of datas.xmjs) {
                        if (this._.findIndex(selfList, { gcl_id: xmj.gcl_id, xmj_id: xmj.xmj_id, mx_id: xmj.mx_id }) === -1) {
                            const udata = {
                                row: {
                                    expr: data.expr,
                                    quantity: data.quantity,
                                },
                                where: {
                                    tid: this.ctx.tender.id,
                                    mid: this.ctx.material.id,
                                    mb_id: data.mb_id,
                                    gcl_id: xmj.gcl_id,
                                    xmj_id: xmj.xmj_id,
                                    mx_id: xmj.mx_id,
                                },
                            };
                            updateDatas.push(udata);
                        }
                        if (this._.indexOf(listGcl, xmj.gcl_id) === -1) {
                            listGcl.push(xmj.gcl_id);
                            updateListGcl.push({
                                row: {
                                    expr: data.expr,
                                    quantity: data.quantity,
                                },
                                where: {
                                    tid: this.ctx.tender.id,
                                    // mid: this.ctx.material.id,
                                    mb_id: data.mb_id,
                                    gcl_id: xmj.gcl_id,
                                },
                            });
                        }
                    }
                    if (updateDatas.length > 0) await transaction.updateRows(this.tableName, updateDatas);
                    if (updateListGcl.length > 0) await transaction.updateRows(this.service.materialListGcl.tableName, updateListGcl);
                    await this.calcQuantityByML(transaction, data.mb_id, ms_id, 'all');
                }
                await transaction.commit();
                const gclList = await this.ctx.service.materialListGcl.getAllDataByCondition({ where: { tid: this.ctx.tender.id } });
                return checklist ? gclList : {
                    gclList,
                    materialListData: await this.getMaterialData(this.ctx.tender.id, this.ctx.material.id),
                };
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        async saveQtyHistory(updateList) {
            if (!this.ctx.tender || !this.ctx.material) {
                throw '数据错误';
            }
            // 判断是否可修改
            // 判断t_type是否为费用
            const transaction = await this.db.beginTransaction();
            try {
                if (updateList.length > 0) await transaction.updateRows(this.tableName, updateList);
                await transaction.update(this.ctx.service.material.tableName, { id: this.ctx.material.id, is_new_qty: 1 });
                await transaction.commit();
                return {
                    materialListData: await this.getMaterialData(this.ctx.tender.id, this.ctx.material.id)
                };
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        async getMbQuantity(transaction, mid, qty_source, qty_decimal, mb_id, ms_id = null, needRound = 1) {
            const msSql = ms_id ? ' AND `ms_id` = ' + ms_id : '';
            const sql = `
                SELECT
                    SUM(CASE WHEN is_join = 1 THEN ${this.ctx.helper.getQtySource(qty_source)} * quantity ELSE 0 END) AS quantity1,
                    SUM(CASE WHEN is_join = 2 THEN ${this.ctx.helper.getQtySource(qty_source, 1)} * quantity ELSE 0 END) AS quantity2
                  FROM ${this.tableName}
                  WHERE mid = ? AND mb_id = ?${msSql}
                `;
            const sqlParam = [mid, mb_id];
            const result = await transaction.queryOne(sql, sqlParam);
            const newQuantity = this.ctx.helper.add(result.quantity1, result.quantity2);
            return needRound ? this.ctx.helper.round(newQuantity, qty_decimal) : newQuantity;
        }

        async makeMaterialList(data) {
            if (!data.mid) {
                throw '参数错误';
            }
            const transaction = await this.db.beginTransaction();
            try {
                const material = await this.ctx.service.material.getDataById(data.mid);
                const newCalcStage = material.is_stage_self ? (material.calc_stage ? material.calc_stage.split(',') : []) : data.stage_id;
                if (material.is_stage_self) newCalcStage.push(data.stage_id);
                const materialStages = material.is_stage_self ? await this.ctx.service.materialStage.getAllDataByCondition({ where: { mid: material.id } }) : [];
                const notJoinList = await this.ctx.service.materialListNotjoin.getAllDataByCondition({ where: { tid: this.ctx.tender.id, mid: material.id } });
                // 复制调差清单工料关联表
                // await this.ctx.service.materialList.copyPreMaterialList(transaction, preMaterial, newMaterial);
                await this.copyPreMaterialList2(transaction, data.material_list, [], notJoinList, material, materialStages);
                // 新增或删除list_gcl表
                await this.ctx.service.materialListGcl.insertOrDelGcl(transaction, data.insertGclList, data.removeGclList, material.id);
                // 修改本期应耗数量值和有效价差,需要剔除不参与调差的清单数据,并返回总金额
                const materials = await this.getAllDataByCondition({
                    where: { tid: this.ctx.tender.id },
                    order: ['order'],
                });
                const preMaterial = materials[materials.length - 1];
                if (material.is_stage_self) {
                    await this.ctx.service.materialStageBills.insertMsBills(transaction, this.ctx.tender.id, material.id, this._.find(materialStages, { sid: parseInt(data.stage_id) }), JSON.parse(material.decimal), preMaterial.is_stage_self, material.qty_source, material.rate);
                    await transaction.update(this.ctx.service.material.tableName, {
                        id: material.id, calc_stage: newCalcStage.join(','),
                    });
                } else {
                    let m_tp = null;
                    let m_tax_tp = null;
                    let rate_tp = null;
                    [m_tp, m_tax_tp, rate_tp] = await this.ctx.service.materialBills.updateNewMaterial(transaction, this.ctx.tender.id, material.id, this.ctx, material.stage_id, JSON.parse(material.decimal), preMaterial.is_stage_self, material.qty_source, material.rate);
                    const updateMaterialData = {
                        id: material.id,
                        m_tp,
                        m_tax_tp,
                        rate_tp,
                        calc_tp: 1,
                        calc_stage: newCalcStage,
                    };
                    await transaction.update(this.ctx.service.material.tableName, updateMaterialData);
                }
                await transaction.commit();
                return true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        async makeSelfList(data) {
            if (!data.mid) {
                throw '参数错误';
            }
            const transaction = await this.db.beginTransaction();
            try {
                const material = await this.ctx.service.material.getDataById(data.mid);
                const materialStages = material.is_stage_self ? await this.ctx.service.materialStage.getAllDataByCondition({ where: { mid: material.id } }) : [];
                const notJoinList = await this.ctx.service.materialListNotjoin.getAllDataByCondition({ where: { tid: this.ctx.tender.id, mid: material.id } });
                // 复制调差清单工料关联表
                // await this.ctx.service.materialList.copyPreMaterialList(transaction, preMaterial, newMaterial);
                await this.copyPreMaterialList2(transaction, [], data.material_self_list, notJoinList, material, materialStages);
                // 设置list_gcl表old=>new更新
                await this.ctx.service.materialListGcl.setNewOldData(transaction, this.ctx.tender.id);
                // 修改本期应耗数量值和有效价差,需要剔除不参与调差的清单数据,并返回总金额
                // 找出当前人并更新tp_data
                const tp_data = await this.ctx.service.materialAudit.getTpData(transaction, material.id, JSON.parse(material.decimal));
                const updateMaterialData = {
                    id: material.id,
                    calc_tp: 1,
                    tp_data: JSON.stringify(tp_data),
                };
                if (material.is_stage_self) {
                    // 更新bill表和截止上期数据
                    const materialDecimal = JSON.parse(material.decimal);
                    const updateBillsData = [];
                    const billsList = await transaction.select(this.ctx.service.materialBills.tableName, { where: { tid: this.ctx.tender.id } });
                    const stageBillsDatas = await this.ctx.service.materialStageBills.getAllDataByCondition({ where: { tid: this.ctx.tender.id, mid: material.id } });
                    for (const mb of billsList) {
                        const [newmsg_spread, newm_spread] = await this.ctx.service.materialStageBills.getSpread(mb, null, materialDecimal.up);
                        const newTp = this._.sumBy(stageBillsDatas, function(item) {
                            return item.mb_id === mb.id ? item.m_tp : 0;
                        });
                        const oneBillsData = {
                            id: mb.id,
                            quantity: null,
                            expr: null,
                            msg_tp: null,
                            msg_times: null,
                            msg_spread: newmsg_spread,
                            m_spread: newm_spread,
                            origin: null,
                            m_tp: newTp,
                            pre_tp: mb.m_tp !== null ? this.ctx.helper.add(mb.pre_tp, mb.m_tp) : mb.pre_tp,
                            m_tax_tp: this.ctx.helper.round(this.ctx.helper.mul(newTp, (1 + this.ctx.helper.div(mb.m_tax, 100))), materialDecimal.tp),
                            tax_pre_tp: mb.m_tax_tp !== null ? this.ctx.helper.add(mb.tax_pre_tp, mb.m_tax_tp) : mb.tax_pre_tp,
                        };
                        updateBillsData.push(oneBillsData);
                    }
                    await transaction.updateRows(this.ctx.service.materialBills.tableName, updateBillsData);
                    // 计算得出本期总金额
                    let m_tp = 0;
                    let m_tax_tp = 0;
                    let rate_tp = 0;
                    for (const s of materialStages) {
                        m_tp = this.ctx.helper.add(m_tp, s.m_tp);
                        m_tax_tp = this.ctx.helper.add(m_tax_tp, s.m_tax_tp);
                        const sRateTp = this.ctx.helper.round(this.ctx.helper.mul(s.m_tp, (1 + this.ctx.helper.div(material.rate, 100))), materialDecimal.tp);
                        rate_tp = this.ctx.helper.add(rate_tp, sRateTp);
                    }
                    updateMaterialData.m_tp = m_tp;
                    updateMaterialData.m_tax_tp = m_tax_tp;
                    updateMaterialData.rate_tp = rate_tp;
                }
                await transaction.update(this.ctx.service.material.tableName, updateMaterialData);
                // 删除material_list表冗余数据，减少表数据量
                await transaction.delete(this.tableName, { tid: this.ctx.tender.id, gather_qty: null, is_self: 0 });
                await transaction.commit();
                return true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }
    }
    return MaterialList;
};