Calculation/app/service/tender.js

'use strict';

/**
 * 标段数据模型
 *
 * @author CaiAoLin
 * @date 2017/11/30
 * @version
 */

const tenderConst = require('../const/tender');
const auditConst = require('../const/audit');
const projectLogConst = require('../const/project_log');
const fs = require('fs');
const path = require('path');
const commonQueryColumns = [
    'id', 'project_id', 'name', 'status', 'category', 'ledger_times', 'ledger_status', 'measure_type', 'user_id', 'valuation', 'create_time',
    'total_price', 'deal_tp', 'copy_id',
    's2b_gxby_check', 's2b_gxby_limit', 's2b_dagl_check', 's2b_dagl_limit', 's2b_multi_check', 's2b_multi_limit',
    'has_rela', 'his_id', 'rpt_show_level',
    'build_status', 'settle_order', 'spid',
];

module.exports = app => {

    class Tender extends app.BaseService {

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

            // 状态相关
            this.status = {
                TRY: 1,
                NORMAL: 2,
                DISABLE: 3,
            };
            this.displayStatus = [];
            this.displayStatus[this.status.TRY] = '试用';
            this.displayStatus[this.status.NORMAL] = '正常';
            this.displayStatus[this.status.DISABLE] = '禁用';

            this.statusClass = [];
            this.statusClass[this.status.TRY] = 'warning';
            this.statusClass[this.status.NORMAL] = 'success';
            this.statusClass[this.status.DISABLE] = 'danger';
        }

        /**
         * 数据规则
         *
         * @param {String} scene - 场景
         * @return {Object} - 返回数据规则
         */
        rule(scene) {
            let rule = {};
            switch (scene) {
                case 'add':
                    rule = {
                        name: { type: 'string', required: true, min: 2 },
                        type: { type: 'string', required: true, min: 1 },
                    };
                    break;
                case 'save':
                    rule = {
                        name: { type: 'string', required: true, min: 2 },
                        type: { type: 'string', required: true, min: 1 },
                    };
                default:
                    break;
            }

            return rule;
        }

        /**
         * 获取你所参与的标段的列表
         *
         * @param {String} listStatus - 取列表状态，如果是管理页要传
         * @param {String} permission - 根据权限取值
         * @param {Number} getAll - 是否取所有标段
         * @return {Array} - 返回标段数据
         */
        async getList(listStatus = '', permission = null, getAll = 0, buildStatusFilter = '', subProject = this.ctx.subProject) {
            if (!subProject) return [];

            // 获取当前项目信息
            const session = this.ctx.session;
            let sql = '';
            let sqlParam = [];
            if (listStatus === 'manage') {
                const userFilter = getAll ? '' : this.db.format(' And t.user_id = ? ', [session.sessionUser.accountId]);
                // 管理页面只取属于自己创建的标段
                sql = 'SELECT t.`id`, t.`project_id`, t.`name`, t.`status`, t.`category`, t.`ledger_times`, t.`ledger_status`, t.`measure_type`, t.`user_id`, t.`create_time`, t.`total_price`, t.`deal_tp`, t.`spid`, t.filter_budget, t.filter_fund,' +
                    '    pa.`name` As `user_name`, pa.`role` As `user_role`, pa.`company` As `user_company` ' +
                    '  FROM ?? As t ' +
                    '  Left Join ?? As pa ' +
                    '  ON t.`user_id` = pa.`id` ' +
                    '  WHERE t.`spid` = ? ' + buildStatusFilter + userFilter + ' ORDER BY CONVERT(t.`name` USING GBK) ASC';
                sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, subProject.id];
            } else if (getAll === 1 || (permission !== null && permission.tender !== undefined && permission.tender.indexOf('2') !== -1)) {
                // 具有查看所有标段权限的用户查阅标段
                sql = 'SELECT t.`id`, t.`project_id`, t.`name`, t.`status`, t.`category`, t.`ledger_times`, t.`ledger_status`, t.`measure_type`, t.`user_id`, t.`create_time`, t.`total_price`, t.`deal_tp`, t.`spid`, t.filter_budget, t.filter_fund,' +
                    '    pa.`name` As `user_name`, pa.`role` As `user_role`, pa.`company` As `user_company` ' +
                    '  FROM ?? As t ' +
                    '  Left Join ?? As pa ' +
                    '  ON t.`user_id` = pa.`id` ' +
                    '  WHERE t.`spid` = ?' + buildStatusFilter + ' ORDER BY CONVERT(t.`name` USING GBK) ASC';
                sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, subProject.id];
            } else {
                // 根据用户权限查阅标段
                // tender 163条数据，project_account 68条数据测试
                // 查询两张表耗时0.003s，查询tender左连接project_account耗时0.002s
                const changeProjectSql = subProject.page_show.openChangeProject ? '    OR (t.`ledger_status` = ' + auditConst.ledger.status.checked + ' AND ' +
                    '        t.id IN ( SELECT cpa.`tid` FROM ' + this.ctx.service.changeProjectAudit.tableName + ' AS cpa WHERE cpa.`aid` = ' + session.sessionUser.accountId + ' GROUP BY cpa.`tid`))' : '';
                const changeApplySql = subProject.page_show.openChangeApply ? '    OR (t.`ledger_status` = ' + auditConst.ledger.status.checked + ' AND ' +
                    '        t.id IN ( SELECT caa.`tid` FROM ' + this.ctx.service.changeApplyAudit.tableName + ' AS caa WHERE caa.`aid` = ' + session.sessionUser.accountId + ' GROUP BY caa.`tid`))' : '';
                const changePlanSql = subProject.page_show.openChangePlan ? '    OR (t.`ledger_status` = ' + auditConst.ledger.status.checked + ' AND ' +
                    '        t.id IN ( SELECT cpla.`tid` FROM ' + this.ctx.service.changePlanAudit.tableName + ' AS cpla WHERE cpla.`aid` = ' + session.sessionUser.accountId + ' GROUP BY cpla.`tid`))' : '';
                const changeProjectXsSql = subProject.page_show.openChangeProject ? '    OR (t.`ledger_status` = ' + auditConst.ledger.status.checked + ' AND ' +
                    '        t.id IN ( SELECT cpxa.`tid` FROM ' + this.ctx.service.changeProjectXsAudit.tableName + ' AS cpxa WHERE cpxa.`aid` = ' + session.sessionUser.accountId + ' GROUP BY cpxa.`tid`))' : '';
                // const xxjdSql = subProject.page_show.xxjd ? '    OR (t.id IN ( SELECT xxjd.`tid` FROM ' + this.ctx.service.scheduleAudit.tableName + ' AS xxjd WHERE xxjd.`permission` != 0 AND xxjd.`audit_id` = ' + session.sessionUser.accountId + ' GROUP BY xxjd.`tid`))' : '';
                sql = 'SELECT t.`id`, t.`project_id`, t.`name`, t.`status`, t.`category`, t.`ledger_times`, t.`ledger_status`, t.`measure_type`, t.`user_id`, t.`create_time`, t.`total_price`, t.`deal_tp`,' +
                    '    pa.`name` As `user_name`, pa.`role` As `user_role`, pa.`company` As `user_company` ' +
                    // '  FROM ?? As t, ?? As pa ' +
                    // '  WHERE t.`project_id` = ? AND t.`user_id` = pa.`id` AND (' +
                    '  FROM ?? As t ' +
                    '  Left Join ?? As pa ' +
                    '  ON t.`user_id` = pa.`id` ' +
                    '  WHERE t.`spid` = ? ' + buildStatusFilter + ' AND (' +
                    // 创建的标段
                    '    t.`user_id` = ?' +
                    // 参与审批 台账 的标段
                    '    OR (t.`ledger_status` != ' + auditConst.ledger.status.uncheck + ' AND ' +
                    '        t.id IN ( SELECT la.`tender_id` FROM ?? As la WHERE la.`audit_id` = ? GROUP BY la.`tender_id`))' +
                    // 参与审批 计量期 的标段
                    '    OR (t.`ledger_status` = ' + auditConst.ledger.status.checked + ' AND ' +
                    '        t.id IN ( SELECT sa.`tid` FROM ?? As sa WHERE sa.`aid` = ? GROUP BY sa.`tid`))' +
                    // 参与协审
                    '    OR (t.`ledger_status` = ' + auditConst.ledger.status.checked + ' AND ' +
                    '        t.id IN ( SELECT sa.`tid` FROM ?? As sa WHERE sa.`ass_user_id` = ? GROUP BY sa.`tid`))' +
                    // 参与审批 结算期 的标段
                    '    OR (t.`ledger_status` = ' + auditConst.ledger.status.checked + ' AND ' +
                    '        t.id IN ( SELECT sa.`tid` FROM ?? As sa WHERE sa.`audit_id` = ? GROUP BY sa.`tid`))' +
                    // 参与审批 合同支付期 的标段
                    '    OR (t.`ledger_status` = ' + auditConst.ledger.status.checked + ' AND ' +
                    '        t.id IN ( SELECT sa.`tid` FROM ?? As sa WHERE sa.`audit_id` = ? GROUP BY sa.`tid`))' +
                    // 参与审批 变更令 的标段
                    '    OR (t.`ledger_status` = ' + auditConst.ledger.status.checked + ' AND ' +
                    '        t.id IN ( SELECT ca.`tid` FROM ?? AS ca WHERE ca.`uid` = ? GROUP BY ca.`tid`))' +
                    // 参与审批 台账修订 的标段
                    '    OR (t.`ledger_status` = ' + auditConst.ledger.status.checked + ' AND ' +
                    '        t.id IN ( SELECT ra.`tender_id` FROM ?? AS ra WHERE ra.`audit_id` = ? GROUP BY ra.`tender_id`))' +
                    // 参与审批 材料调差 的标段
                    '    OR (t.`ledger_status` = ' + auditConst.ledger.status.checked + ' AND ' +
                    '        t.id IN ( SELECT ma.`tid` FROM ?? AS ma WHERE ma.`aid` = ? GROUP BY ma.`tid`))' +
                    // 参与审批 预付款 的标段
                    '    OR (t.id IN ( SELECT ad.`tid` FROM ?? AS ad WHERE ad.`audit_id` = ? GROUP BY ad.`tid`))' +
                    // 参与审批 变更立项书及变更申请 的标段
                    changeProjectSql + changeApplySql + changePlanSql + changeProjectXsSql +
                    // 游客权限的标段
                    '    OR (t.id IN ( SELECT tt.`tid` FROM ?? AS tt WHERE tt.`user_id` = ?))' +
                    // 协作标段
                    '    OR (t.id IN ( SELECT tt.`tid` FROM ?? AS tt WHERE tt.`user_id` = ?))' +
                    // 未参与，但可见的标段
                    ') ORDER BY CONVERT(t.`name` USING GBK) ASC';
                sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, subProject.id, session.sessionUser.accountId,
                    this.ctx.service.ledgerAudit.tableName, session.sessionUser.accountId,
                    this.ctx.service.stageAudit.tableName, session.sessionUser.accountId,
                    this.ctx.service.auditAss.tableName, session.sessionUser.accountId,
                    this.ctx.service.settleAudit.tableName, session.sessionUser.accountId,
                    this.ctx.service.phasePayAudit.tableName, session.sessionUser.accountId,
                    this.ctx.service.changeAudit.tableName, session.sessionUser.accountId,
                    this.ctx.service.reviseAudit.tableName, session.sessionUser.accountId,
                    this.ctx.service.materialAudit.tableName, session.sessionUser.accountId,
                    this.ctx.service.advanceAudit.tableName, session.sessionUser.accountId,
                    this.ctx.service.tenderTourist.tableName, session.sessionUser.accountId,
                    this.ctx.service.tenderAss.tableName, session.sessionUser.accountId,
                ];
            }
            const list = await this.db.query(sql, sqlParam);
            for (const l of list) {
                l.category = l.category && l.category !== '' ? JSON.parse(l.category) : null;
            }

            return list;
        }

        /**
         * 获取你所参与的标段的列表 - 完工
         *
         * @param {String} listStatus - 取列表状态，如果是管理页要传
         * @param {String} permission - 根据权限取值
         * @param {Number} getAll - 是否取所有标段
         * @return {Array} - 返回标段数据
         */
        async getFinishList(listStatus = '', permission = null, getAll = 0) {
            const buildStatusFilter = this.db.format(' AND build_status = ?', [tenderConst.buildStatus.status.finish]);
            return await this.getList(listStatus, permission, getAll, buildStatusFilter);
        }

        /**
         * 获取你所参与的标段的列表 - 在建
         *
         * @param {String} listStatus - 取列表状态，如果是管理页要传
         * @param {String} permission - 根据权限取值
         * @param {Number} getAll - 是否取所有标段
         * @return {Array} - 返回标段数据
         */
        async getBuildList(listStatus = '', permission = null, getAll = 0) {
            const buildStatusFilter = this.db.format(' AND build_status = ?', [tenderConst.buildStatus.status.build]);
            return await this.getList(listStatus, permission, getAll, buildStatusFilter);
        }

        async getList4Select(selectType) {
            const accountInfo = await this.ctx.service.projectAccount.getDataById(this.ctx.session.sessionUser.accountId);
            const userPermission = accountInfo !== undefined && accountInfo.permission !== '' ? JSON.parse(accountInfo.permission) : null;
            const tenderList = await this.ctx.service.tender.getList('', userPermission, this.ctx.session.sessionUser.is_admin);
            for (const t of tenderList) {
                if (t.ledger_status === auditConst.ledger.status.checked) {
                    t.lastStage = await this.ctx.service.stage.getLastestStage(t.id, false);
                    t.completeStage = await this.ctx.service.stage.getLastestCompleteStage(t.id);
                }
            }
            switch (selectType) {
                case 'ledger': return tenderList.filter(x => {
                    return x.ledger_status === auditConst.ledger.status.checked;
                });
                case 'revise': tenderList.filter(x => {
                    return x.ledger_status === auditConst.ledger.status.checked;
                });
                case 'stage': return tenderList.filter(x => {
                    return x.ledger_status === auditConst.ledger.status.checked && !!x.lastStage;
                });
                case 'stage-checked': return tenderList.filter(x => {
                    return !!x.completeStage;
                });
                default: return tenderList;
            }
        }

        async getTender(id, columns = commonQueryColumns) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('id', {
                value: id,
                operate: '=',
            });
            this.sqlBuilder.columns = columns;
            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);

            const tender = await this.db.queryOne(sql, sqlParam);
            if (tender && this._.includes(columns, 'category')) {
                tender.category = tender.category && tender.category !== '' ? JSON.parse(tender.category) : null;
            }
            return tender;
        }

        async getManageTenderList(projectId) {
            return await this.ctx.service.tender.getAllDataByCondition({ where: { project_id: projectId }});
        }

        /**
         * 新增标段
         *
         * @param {Object} data - 提交的数据
         * @return {Boolean} - 返回新增结果
         */
        async add(data) {
            let result = false;
            this.transaction = await this.db.beginTransaction();
            try {
                // 获取当前用户信息
                const sessionUser = this.ctx.session.sessionUser;
                // 获取当前项目信息
                const sessionProject = this.ctx.session.sessionProject;

                const insertData = {
                    name: data.name,
                    status: tenderConst.status.APPROVAL,
                    project_id: sessionProject.id,
                    user_id: sessionUser.accountId,
                    create_time: new Date(),
                    category: JSON.stringify(data.category),
                    valuation: data.valuation,
                    spid: data.spid,
                };
                const operate = await this.transaction.insert(this.tableName, insertData);

                result = operate.insertId > 0;
                if (!result) {
                    throw '新增标段数据失败';
                }
                await this.ctx.service.tenderCache.insertTenderCache(this.transaction, operate.insertId, sessionUser.accountId);
                if (data.spid) await this.ctx.service.subProject.addRelaTender(this.transaction, data.spid, operate.insertId);


                // 获取合同支付模板 并添加到标段
                result = await this.ctx.service.pay.addDefaultPayData(operate.insertId, this.transaction);
                if (!result) {
                    throw '新增合同支付数据失败';
                }
                await this.ctx.service.tenderTag.addTenderTag(operate.insertId, sessionProject.id, this.transaction);
                await this.transaction.commit();
                const sql = 'SELECT t.`id`, t.`project_id`, t.`name`, t.`status`, t.`category`, t.`ledger_times`, t.`ledger_status`, t.`measure_type`, t.`user_id`, t.`create_time`, t.`total_price`, t.`deal_tp`, t.`spid`,' +
                    '    pa.`name` As `user_name`, pa.`role` As `user_role`, pa.`company` As `user_company` ' +
                    '  FROM ?? As t ' +
                    '  Left Join ?? As pa ' +
                    '  ON t.`user_id` = pa.`id` ' +
                    '  WHERE t.`id` = ?';
                const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, operate.insertId];
                const tender = await this.db.queryOne(sql, sqlParam);
                if (tender) {
                    tender.category = tender.category && tender.category !== '' ? JSON.parse(tender.category) : null;
                }

                // 是否加入到决策大屏中
                if (this.ctx.subProject.page_show.addDataCollect === 0) {
                    await this.ctx.service.datacollectTender.add(sessionProject.id, operate.insertId);
                }
                return tender;
            } catch (error) {
                await this.transaction.rollback();
                throw error;
            }
        }

        /**
         * 保存标段
         *
         * @param {Object} postData - 表单post过来的数据
         * @param {Number} id - 用于判断修改还是新增的id
         * @return {Boolean} - 返回执行结果
         */
        async save(postData, id = 0) {
            id = parseInt(id);
            const tender = await this.getDataById(id);

            const rowData = {
                id,
                name: postData.name,
                type: postData.type,
                category: JSON.stringify(postData.category),
            };
            const conn = await this.db.beginTransaction();
            try {
                if (tender.spid !== postData.spid) {
                    if (postData.spid) await this.ctx.service.subProject.addRelaTender(conn, postData.spid, id);
                    if (tender.spid) await this.ctx.service.subProject.removeRelaTender(conn, tender.spid, id);
                    const subProject = await this.ctx.service.subProject.getDataById(postData.spid);
                    await this._addSubProjPermission(conn, subProject, [id]);
                }
                rowData.spid = postData.spid || '';
                const result = await conn.update(this.tableName, rowData);
                await conn.commit();
                return result.affectedRows > 0;
            } catch(err) {
                await conn.rollback();
                throw err;
            }
        }

        async batchUpdate (data) {
            const validFields = ['filter_budget', 'filter_fund'];
            const updateData = [];
            for (const d of data) {
                const ud = { id: d.id };
                for (const prop in d) {
                    if (validFields.indexOf(prop) < 0) continue;
                    ud[prop] = d[prop];
                }
                updateData.push(d);
            }
            await this.db.updateRows(this.tableName, updateData);
            return updateData;
        }

        /**
         * 假删除
         *
         * @param {Number} id - 删除的id
         * @return {Boolean} - 删除结果
         */
        async deleteTenderById(id) {
            const updateData = {
                status: this.status.DISABLE,
                id,
            };
            const result = await this.db.update(this.tableName, updateData);

            return result.affectedRows > 0;
        }

        /**
         * 真删除
         * @param {Number} id - 删除的标段id
         * @return {Promise<boolean>} - 结果
         */
        async deleteTenderNoBackup(id) {
            const transaction = await this.db.beginTransaction();
            try {
                const tenderMsg = await this.getDataById(id);
                // 先删除附件文件
                const attList = await this.ctx.service.changeAtt.getAllDataByCondition({ where: { tid: id } });
                const newAttList = await this.ctx.service.materialFile.getAllMaterialFiles(id);
                const changeProjectAttList = await this.ctx.service.changeProjectAtt.getAllDataByCondition({ where: { tid: id } });
                const changeApplyAttList = await this.ctx.service.changeApplyAtt.getAllDataByCondition({ where: { tid: id } });
                const changePlanAttList = await this.ctx.service.changePlanAtt.getAllDataByCondition({ where: { tid: id } });
                const advanceAttList = await this.ctx.service.advanceFile.getAllDataByCondition({ where: { tid: id } });
                attList.concat(newAttList, changeProjectAttList, changeApplyAttList, changePlanAttList, advanceAttList);
                await this.ctx.helper.delFiles(attList);
                await transaction.delete(this.tableName, { id });
                await transaction.delete(this.ctx.service.tenderInfo.tableName, { tid: id });
                await transaction.delete(this.ctx.service.tenderCache.tableName, { id });
                await transaction.delete(this.ctx.service.tenderTourist.tableName, { tid: id });
                await transaction.delete(this.ctx.service.tenderMap.tableName, { tid: id });
                await transaction.delete(this.ctx.service.tenderTag.tableName, { tid: id });
                await transaction.delete(this.ctx.service.ledger.departTableName(id), { tender_id: id });
                await transaction.delete(this.ctx.service.ledgerAudit.tableName, { tender_id: id });
                await transaction.delete(this.ctx.service.pos.departTableName(id), { tid: id });
                await transaction.delete(this.ctx.service.pay.tableName, { tid: id });

                await transaction.delete(this.ctx.service.stage.tableName, { tid: id });
                await transaction.delete(this.ctx.service.stageAudit.tableName, { tid: id });
                await transaction.delete(this.ctx.service.stageBills.departTableName(id), { tid: id });
                await transaction.delete(this.ctx.service.stagePos.departTableName(id), { tid: id });
                await transaction.delete(this.ctx.service.stageBillsDgn.tableName, { tid: id });
                await transaction.delete(this.ctx.service.stageBillsFinal.departTableName(id), { tid: id });
                await transaction.delete(this.ctx.service.stagePosFinal.departTableName(id), { tid: id });
                await transaction.delete(this.ctx.service.stageDetail.tableName, { tid: id });
                await transaction.delete(this.ctx.service.stagePay.tableName, { tid: id });
                await transaction.delete(this.ctx.service.stageChange.tableName, { tid: id });
                await transaction.delete(this.ctx.service.stageAtt.tableName, { tid: id });

                await transaction.delete(this.ctx.service.stageJgcl.tableName, { tid: id });
                await transaction.delete(this.ctx.service.stageBonus.tableName, { tid: id });
                await transaction.delete(this.ctx.service.stageOther.tableName, { tid: id });

                await transaction.delete(this.ctx.service.stageRela.tableName, { tid: id });
                await transaction.delete(this.ctx.service.stageRelaBills.tableName, { tid: id });
                await transaction.delete(this.ctx.service.stageRelaBillsFinal.tableName, { tid: id });

                await transaction.delete(this.ctx.service.change.tableName, { tid: id });
                await transaction.delete(this.ctx.service.changeAudit.tableName, { tid: id });
                await transaction.delete(this.ctx.service.changeAuditList.tableName, { tid: id });
                await transaction.delete(this.ctx.service.changeCompany.tableName, { tid: id });

                await transaction.delete(this.ctx.service.changeLedger.tableName, { tender_id: id });
                await transaction.delete(this.ctx.service.changePos.tableName, { tid: id });
                await transaction.delete(this.ctx.service.changeReviseLog.tableName, { tid: id });

                await transaction.delete(this.ctx.service.changeProject.tableName, { tid: id });
                await transaction.delete(this.ctx.service.changeProjectAudit.tableName, { tid: id });
                await transaction.delete(this.ctx.service.changeProjectXsAudit.tableName, { tid: id });
                await transaction.delete(this.ctx.service.changeProjectAtt.tableName, { tid: id });

                await transaction.delete(this.ctx.service.changeApply.tableName, { tid: id });
                await transaction.delete(this.ctx.service.changeApplyAudit.tableName, { tid: id });
                await transaction.delete(this.ctx.service.changeApplyList.tableName, { tid: id });
                await transaction.delete(this.ctx.service.changeApplyAtt.tableName, { tid: id });

                await transaction.delete(this.ctx.service.changePlan.tableName, { tid: id });
                await transaction.delete(this.ctx.service.changePlanAudit.tableName, { tid: id });
                await transaction.delete(this.ctx.service.changePlanList.tableName, { tid: id });
                await transaction.delete(this.ctx.service.changePlanAtt.tableName, { tid: id });

                await transaction.delete(this.ctx.service.ledgerRevise.tableName, { tid: id });
                await transaction.delete(this.ctx.service.reviseAudit.tableName, { tender_id: id });
                await transaction.delete(this.ctx.service.reviseBills.departTableName(id), { tender_id: id });
                await transaction.delete(this.ctx.service.revisePos.departTableName(id), { tid: id });

                await transaction.delete(this.ctx.service.material.tableName, { tid: id });
                await transaction.delete(this.ctx.service.materialAudit.tableName, { tid: id });
                await transaction.delete(this.ctx.service.materialBills.tableName, { tid: id });
                await transaction.delete(this.ctx.service.materialBillsHistory.tableName, { tid: id });
                await transaction.delete(this.ctx.service.materialList.tableName, { tid: id });
                await transaction.delete(this.ctx.service.materialListNotjoin.tableName, { tid: id });
                await transaction.delete(this.ctx.service.materialExponent.tableName, { tid: id });
                await transaction.delete(this.ctx.service.materialExponentHistory.tableName, { tid: id });
                await transaction.delete(this.ctx.service.materialListGcl.tableName, { tid: id });
                await transaction.delete(this.ctx.service.materialListSelf.tableName, { tid: id });
                await transaction.delete(this.ctx.service.materialChecklist.tableName, { tid: id });


                await transaction.delete(this.ctx.service.materialFile.tableName, { tid: id });
                await transaction.delete(this.ctx.service.signatureUsed.tableName, { tender_id: id });
                await transaction.delete(this.ctx.service.signatureRole.tableName, { tender_id: id });

                await transaction.delete(this.ctx.service.changeAtt.tableName, { tid: id });
                // await transaction.delete(this.ctx.service.materialFile.tableName, { tid: id });

                await transaction.delete(this.ctx.service.advanceFile.tableName, { tid: id });

                await transaction.delete(this.ctx.service.datacollectTender.tableName, { pid: this.ctx.session.sessionProject.id, tid: id });

                await transaction.delete(this.ctx.service.schedule.tableName, { tid: id });
                await transaction.delete(this.ctx.service.scheduleAudit.tableName, { tid: id });
                await transaction.delete(this.ctx.service.scheduleLedger.tableName, { tid: id });
                await transaction.delete(this.ctx.service.scheduleLedgerHistory.tableName, { tid: id });
                await transaction.delete(this.ctx.service.scheduleLedgerMonth.tableName, { tid: id });
                await transaction.delete(this.ctx.service.scheduleMonth.tableName, { tid: id });
                await transaction.delete(this.ctx.service.scheduleStage.tableName, { tid: id });


                await transaction.delete(this.ctx.service.shenpiAudit.tableName, { tid: id });

                // 记录删除日志
                await this.ctx.service.projectLog.addProjectLog(transaction, projectLogConst.type.tender, projectLogConst.status.delete, tenderMsg.name, id);
                await transaction.commit();
                return true;
            } catch (err) {
                this.ctx.helper.log(err);
                await transaction.rollback();
                return false;
            }
        }

        async getCheckTender(tid) {
            const tender = await this.ctx.service.tender.getTender(tid);
            if (tender.measure_type) tender.info = await this.ctx.service.tenderInfo.getTenderInfo(tid);
            return tender;
        }

        async checkTender(tid) {
            if (this.ctx.tender) return this.ctx.tender;
            this.ctx.tender = await this.getCheckTender(tid);
            return this.ctx.tender;
        }

        async setTenderType(tender, type) {
            const templateId = await this.ctx.service.valuation.getValuationTemplate(tender.valuation, type);
            if (templateId === -1) throw '该模式下，台账模板不存在';

            // 获取标段项目节点模板
            const tenderNodeTemplateData = await this.ctx.service.tenderNodeTemplate.getData(templateId);
            const conn = await this.db.beginTransaction();
            try {
                await conn.update(this.tableName, { id: tender.id, measure_type: type });

                // 复制模板数据到标段数据表
                const result = await this.ctx.service.ledger.innerAdd(tenderNodeTemplateData, tender.id, conn);
                if (!result) {
                    throw '初始化台账失败';
                }
                await conn.commit();
            } catch (err) {
                await conn.rollback();
                throw err;
            }
        }

        async checkTenderCanFinish(tender) {
            // 检查台账、台账修订、预付款、计量期、材差期、变更令状态
            if (tender.ledger_status !== auditConst.ledger.status.checked) return false;
            const lastRevise = await this.ctx.service.ledgerRevise.getLastestRevise(tender.id, true);
            if (lastRevise && lastRevise.status !== auditConst.revise.status.checked) return false;
            const advanceOn = await this.db.queryOne(`SELECT * FROM ${this.ctx.service.advance.tableName} WHERE tid = ${tender.id} AND status <> ${auditConst.advance.status.checked}`);
            if (advanceOn) return false;
            const stageOn = await this.db.queryOne(`SELECT * FROM ${this.ctx.service.stage.tableName} WHERE tid = ${tender.id} AND status <> ${auditConst.stage.status.checked}`);
            if (stageOn) return false;
            const materialOn = await this.db.queryOne(`SELECT * FROM ${this.ctx.service.material.tableName} WHERE tid = ${tender.id} AND status <> ${auditConst.material.status.checked}`);
            if (materialOn) return false;
            const changeOn = await this.db.queryOne(`SELECT * FROM ${this.ctx.service.change.tableName} WHERE tid = ${tender.id} AND valid = 1 AND status <> ${auditConst.flow.status.checked}`);
            if (changeOn) return false;
            const changeApplyOn = await this.db.queryOne(`SELECT * FROM ${this.ctx.service.changeApply.tableName} WHERE tid = ${tender.id} AND status <> ${auditConst.changeApply.status.checked}`);
            if (changeApplyOn) return false;
            const changeProjectOn = await this.db.queryOne(`SELECT * FROM ${this.ctx.service.changeProject.tableName} WHERE tid = ${tender.id} AND status <> ${auditConst.changeProject.status.checked}`);
            if (changeProjectOn) return false;
            const changePlanOn = await this.db.queryOne(`SELECT * FROM ${this.ctx.service.changePlan.tableName} WHERE tid = ${tender.id} AND status <> ${auditConst.changePlan.status.checked}`);
            if (changePlanOn) return false;
            return true;
        }

        async saveBuildStatus(tender, status) {
            const str = tenderConst.buildStatus.statusStr[status];
            if (!str) throw '参数错误';

            if (status === tenderConst.buildStatus.status.finish) {
                const check = await this.checkTenderCanFinish(tender);
                if (!check) throw '存在未审批完成的流程，请审批完成后再修改状态';
            }

            await this.defaultUpdate({ id: tender.id, build_status: status });
        }

        async saveApiRela(tid, updateData) {
            await this.db.update(this.tableName, updateData, {where: { id: tid } });
        }

        async saveTenderData(tid, updateData) {
            return await this.db.update(this.tableName, updateData, { where: { id: tid } });
        }

        // 仅要求授权表用tid/uid授权（条件：存在数据行即授权查看，取消授权直接删除行）
        // 如果授权表是牵扯多个模块的授权，则part是模块权限字段
        // filter目前仅支持['self', 'all']，可随时增加过滤类型
        async getSpecList(specPermissionService, part = '', filter = 'self') {
            const partSql = part ? ` AND ${part} <> '' ` : '';
            const filterSql = filter === 'all' ? '' : ` AND t.id IN (SELECT tid FROM ${specPermissionService.tableName} WHERE uid = ? ${partSql}) `;
            const sql = 'SELECT t.`id`, t.`project_id`, t.`name`, t.`status`, t.`category`, t.`user_id`, t.`create_time`, t.`spid`,' +
                '    pa.`name` As `user_name`, pa.`role` As `user_role`, pa.`company` As `user_company` ' +
                `  FROM ${this.tableName} As t Left Join ${this.ctx.service.projectAccount.tableName} As pa ON t.user_id = pa.id` +
                '  WHERE t.`spid` = ? ' + filterSql +
                '  ORDER BY CONVERT(t.`name` USING GBK) ASC';
            const sqlParam = [this.ctx.subProject.id];
            if (filter !== 'all') sqlParam.push(this.ctx.session.sessionUser.accountId);
            const list = await this.db.query(sql, sqlParam);
            for (const l of list) {
                l.category = l.category && l.category !== '' ? JSON.parse(l.category) : null;
            }

            return list;
        }

        /**
         * 获取你所参与的施工标段的列表
         *
         * @param {String} listStatus - 取列表状态，如果是管理页要传
         * @param {String} permission - 根据权限取值
         * @param {Number} getAll - 是否取所有标段
         * @return {Array} - 返回标段数据
         */
        async getConstructionList(listStatus = '', permission = null, getAll = 0) {
            // 获取当前项目信息
            const session = this.ctx.session;
            let sql = '';
            let sqlParam = [];
            if (getAll === 1 || (permission !== null && permission.construction !== undefined && permission.construction.indexOf('1') !== -1)) {
                // 具有查看所有标段权限的用户查阅标段
                sql = 'SELECT t.`id`, t.`project_id`, t.`name`, t.`status`, t.`category`, t.`user_id`, t.`create_time`,' +
                    '    pa.`name` As `user_name`, pa.`role` As `user_role`, pa.`company` As `user_company` ' +
                    '  FROM ?? As t ' +
                    '  Left Join ?? As pa ' +
                    '  ON t.`user_id` = pa.`id` ' +
                    '  WHERE t.`project_id` = ? AND t.`spid` = ? ORDER BY CONVERT(t.`name` USING GBK) ASC';
                sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, session.sessionProject.id, this.ctx.subProject.id];
            } else {
                // 根据用户权限查阅标段
                sql = 'SELECT t.`id`, t.`project_id`, t.`name`, t.`status`, t.`category`, t.`user_id`, t.`create_time`,' +
                    '    pa.`name` As `user_name`, pa.`role` As `user_role`, pa.`company` As `user_company` ' +
                    // '  FROM ?? As t, ?? As pa ' +
                    // '  WHERE t.`project_id` = ? AND t.`user_id` = pa.`id` AND (' +
                    '  FROM ?? As t ' +
                    '  Left Join ?? As pa ' +
                    '  ON t.`user_id` = pa.`id` ' +
                    '  WHERE t.`project_id` = ? AND t.`spid` = ? AND ' +
                    // 参与施工 的标段
                    ' t.id IN ( SELECT ca.`tid` FROM ?? As ca WHERE ca.`uid` = ?)' +
                    ' ORDER BY CONVERT(t.`name` USING GBK) ASC';
                sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, session.sessionProject.id, this.ctx.subProject.id,
                    this.ctx.service.constructionAudit.tableName, session.sessionUser.accountId,
                ];
            }
            const list = await this.db.query(sql, sqlParam);
            for (const l of list) {
                l.category = l.category && l.category !== '' ? JSON.parse(l.category) : null;
            }

            return list;
        }

        /**
         * 获取你所参与的合同标段的列表
         *
         * @param {String} listStatus - 取列表状态，如果是管理页要传
         * @param {String} permission - 根据权限取值
         * @param {Number} getAll - 是否取所有标段
         * @return {Array} - 返回标段数据
         */
        async getContractList(listStatus = '', permission = null, getAll = 0) {
            // 获取当前项目信息
            const session = this.ctx.session;
            let sql = '';
            let sqlParam = [];
            if (getAll === 1) {
                // 具有查看所有标段权限的用户查阅标段
                sql = 'SELECT t.`id`, t.`project_id`, t.`name`, t.`status`, t.`category`, t.`user_id`, t.`create_time`,' +
                    '    pa.`name` As `user_name`, pa.`role` As `user_role`, pa.`company` As `user_company`, t.`spid` ' +
                    '  FROM ?? As t ' +
                    '  Left Join ?? As pa ' +
                    '  ON t.`user_id` = pa.`id` ' +
                    '  WHERE t.`project_id` = ? AND t.`spid` = ? ORDER BY CONVERT(t.`name` USING GBK) ASC';
                sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, session.sessionProject.id, this.ctx.subProject.id];
            } else {
                // 根据用户权限查阅标段
                sql = 'SELECT t.`id`, t.`project_id`, t.`name`, t.`status`, t.`category`, t.`user_id`, t.`create_time`,' +
                    '    pa.`name` As `user_name`, pa.`role` As `user_role`, pa.`company` As `user_company`, t.`spid` ' +
                    // '  FROM ?? As t, ?? As pa ' +
                    // '  WHERE t.`project_id` = ? AND t.`user_id` = pa.`id` AND (' +
                    '  FROM ?? As t ' +
                    '  Left Join ?? As pa ' +
                    '  ON t.`user_id` = pa.`id` ' +
                    '  WHERE t.`project_id` = ? AND ' +
                    // 参与施工 的标段
                    ' t.id IN ( SELECT ca.`tid` FROM ?? As ca WHERE ca.`uid` = ?)' +
                    ' AND t.`spid` = ? ORDER BY CONVERT(t.`name` USING GBK) ASC';
                sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, session.sessionProject.id,
                    this.ctx.service.contractAudit.tableName, session.sessionUser.accountId, this.ctx.subProject.id,
                ];
            }
            const list = await this.db.query(sql, sqlParam);
            for (const l of list) {
                l.category = l.category && l.category !== '' ? JSON.parse(l.category) : null;
            }

            return list;
        }

        async bindSp(pid, spid, tids) {
            const subProject = await this.ctx.service.subProject.getDataById(spid);
            const orgRelaTenderId = subProject.rela_tender !== '' ? subProject.rela_tender.split(',') : [];
            orgRelaTenderId.push(...tids);
            // 更新category
            const orgCategoryData = await this.ctx.service.category.getOrgAllCategory(pid);
            const newCategoryData = await this.ctx.service.category.getAllCategory(subProject);
            const updateTenders = [];
            for (const tid of tids) {
                const tender = await this.getDataById(tid);
                const oneUpdate = {
                    id: tid,
                    spid,
                };
                if (tender.category) {
                    const newCategory = [];
                    const orgCategory = JSON.parse(tender.category);
                    for (const c of orgCategory) {
                        const orgC = orgCategoryData.find(item => item.id === c.cid);
                        if (orgC) {
                            const newC = newCategoryData.find(item => item.name === orgC.name);
                            if (newC) {
                                const orgV = orgC.value.find(item => item.id === c.value);
                                if (orgV) {
                                    const newV = newC.value.find(item => item.value === orgV.value);
                                    if (newV) {
                                        newCategory.push({
                                            cid: newC.id,
                                            value: newV.id,
                                        });
                                    }
                                }
                            }
                        }
                    }
                    if (newCategory.length > 0) {
                        oneUpdate.category = JSON.stringify(newCategory);
                    }
                }
                updateTenders.push(oneUpdate);
            }
            const conn = await this.db.beginTransaction();
            try {
                await conn.updateRows(this.tableName, updateTenders);
                await conn.update(this.ctx.service.subProject.tableName, { id: subProject.id, rela_tender: this._.uniq(orgRelaTenderId).join(',') });
                await conn.update(this.ctx.service.budget.tableName, { id: subProject.budget_id, rela_tender: this._.uniq(orgRelaTenderId).join(',') });
                await this._addSubProjPermission(conn, subProject, tids);
                await conn.commit();
                return true;
            } catch (error) {
                await conn.rollback();
                throw error;
            }
            // return await this.ctx.subProject.setRelaTender({ id: spid, rela_tender: newTids });
        }

        async _addSubProjPermission(conn, subProject, tids) {
            // 需要把所有审批人也迁移至项目下
            const subProjPermissionAudits = await this.ctx.service.subProjPermission.getAllDataByCondition({ where: { spid: subProject.id } });
            const hadUids = this._.map(subProjPermissionAudits, 'uid');
            const newUids = this._.cloneDeep(hadUids) || [];
            const tenderAuditsSql = `SELECT user_id FROM ?? WHERE id in (${tids.join(',')}) ${newUids.length > 0 ? ` AND user_id NOT IN (${newUids.join(',')})` : ''} GROUP BY user_id`;
            const tenderAuditParams = [this.tableName];
            const tenderResult = await conn.query(tenderAuditsSql, tenderAuditParams);
            newUids.push(...this._.map(tenderResult, 'user_id'));
            const stageAuditsSql = `SELECT aid FROM ?? WHERE tid in (${tids.join(',')}) ${newUids.length > 0 ? ` AND aid NOT IN (${newUids.join(',')})` : ''} GROUP BY aid`;
            const stageAuditParams = [this.ctx.service.stageAudit.tableName];
            const stageResult = await conn.query(stageAuditsSql, stageAuditParams);
            newUids.push(...this._.map(stageResult, 'aid'));
            const ledgerAuditsSql = `SELECT audit_id FROM ?? WHERE tender_id in (${tids.join(',')}) ${newUids.length > 0 ? ` AND audit_id NOT IN (${newUids.join(',')})` : ''} GROUP BY audit_id`;
            const ledgerAuditParams = [this.ctx.service.ledgerAudit.tableName];
            const ledgerResult = await conn.query(ledgerAuditsSql, ledgerAuditParams);
            newUids.push(...this._.map(ledgerResult, 'audit_id'));
            const auditAssAuditsSql = `SELECT ass_user_id FROM ?? WHERE tid in (${tids.join(',')}) ${newUids.length > 0 ? ` AND ass_user_id NOT IN (${newUids.join(',')})` : ''} GROUP BY ass_user_id`;
            const auditAssAuditParams = [this.ctx.service.auditAss.tableName];
            const auditAssResult = await conn.query(auditAssAuditsSql, auditAssAuditParams);
            newUids.push(...this._.map(auditAssResult, 'ass_user_id'));
            const settleAuditsSql = `SELECT audit_id FROM ?? WHERE tid in (${tids.join(',')}) ${newUids.length > 0 ? ` AND audit_id NOT IN (${newUids.join(',')})` : ''} GROUP BY audit_id`;
            const settleAuditParams = [this.ctx.service.settleAudit.tableName];
            const settleResult = await conn.query(settleAuditsSql, settleAuditParams);
            newUids.push(...this._.map(settleResult, 'audit_id'));
            const changeAuditsSql = `SELECT uid FROM ?? WHERE tid in (${tids.join(',')}) ${newUids.length > 0 ? ` AND uid NOT IN (${newUids.join(',')})` : ''} GROUP BY uid`;
            const changeAuditParams = [this.ctx.service.changeAudit.tableName];
            const changeResult = await conn.query(changeAuditsSql, changeAuditParams);
            newUids.push(...this._.map(changeResult, 'uid'));
            const changeApplyAuditsSql = `SELECT aid FROM ?? WHERE tid in (${tids.join(',')}) ${newUids.length > 0 ? ` AND aid NOT IN (${newUids.join(',')})` : ''} GROUP BY aid`;
            const changeApplyAuditParams = [this.ctx.service.changeApplyAudit.tableName];
            const changeApplyResult = await conn.query(changeApplyAuditsSql, changeApplyAuditParams);
            newUids.push(...this._.map(changeApplyResult, 'aid'));
            const changeProjectAuditsSql = `SELECT aid FROM ?? WHERE tid in (${tids.join(',')}) ${newUids.length > 0 ? ` AND aid NOT IN (${newUids.join(',')})` : ''} GROUP BY aid`;
            const changeProjectAuditParams = [this.ctx.service.changeProjectAudit.tableName];
            const changeProjectResult = await conn.query(changeProjectAuditsSql, changeProjectAuditParams);
            newUids.push(...this._.map(changeProjectResult, 'aid'));
            const changeProjectXsAuditsSql = `SELECT aid FROM ?? WHERE tid in (${tids.join(',')}) ${newUids.length > 0 ? ` AND aid NOT IN (${newUids.join(',')})` : ''} GROUP BY aid`;
            const changeProjectXsAuditParams = [this.ctx.service.changeProjectXsAudit.tableName];
            const changeProjectXsResult = await conn.query(changeProjectXsAuditsSql, changeProjectXsAuditParams);
            newUids.push(...this._.map(changeProjectXsResult, 'aid'));
            const changePlanAuditsSql = `SELECT aid FROM ?? WHERE tid in (${tids.join(',')}) ${newUids.length > 0 ? ` AND aid NOT IN (${newUids.join(',')})` : ''} GROUP BY aid`;
            const changePlanAuditParams = [this.ctx.service.changePlanAudit.tableName];
            const changePlanResult = await conn.query(changePlanAuditsSql, changePlanAuditParams);
            newUids.push(...this._.map(changePlanResult, 'aid'));
            const reviseAuditsSql = `SELECT audit_id FROM ?? WHERE tender_id in (${tids.join(',')}) ${newUids.length > 0 ? ` AND audit_id NOT IN (${newUids.join(',')})` : ''} GROUP BY audit_id`;
            const reviseAuditParams = [this.ctx.service.reviseAudit.tableName];
            const reviseResult = await conn.query(reviseAuditsSql, reviseAuditParams);
            newUids.push(...this._.map(reviseResult, 'audit_id'));
            const materialAuditsSql = `SELECT aid FROM ?? WHERE tid in (${tids.join(',')}) ${newUids.length > 0 ? ` AND aid NOT IN (${newUids.join(',')})` : ''} GROUP BY aid`;
            const materialAuditParams = [this.ctx.service.materialAudit.tableName];
            const materialResult = await conn.query(materialAuditsSql, materialAuditParams);
            newUids.push(...this._.map(materialResult, 'aid'));
            const advanceAuditsSql = `SELECT audit_id FROM ?? WHERE tid in (${tids.join(',')}) ${newUids.length > 0 ? ` AND audit_id NOT IN (${newUids.join(',')})` : ''} GROUP BY audit_id`;
            const advanceAuditParams = [this.ctx.service.advanceAudit.tableName];
            const advanceResult = await conn.query(advanceAuditsSql, advanceAuditParams);
            newUids.push(...this._.map(advanceResult, 'audit_id'));
            const tenderTouristSql = `SELECT user_id FROM ?? WHERE tid in (${tids.join(',')}) ${newUids.length > 0 ? ` AND user_id NOT IN (${newUids.join(',')})` : ''} GROUP BY user_id`;
            const tenderTouristParams = [this.ctx.service.tenderTourist.tableName];
            const tenderTouristResult = await conn.query(tenderTouristSql, tenderTouristParams);
            newUids.push(...this._.map(tenderTouristResult, 'user_id'));
            // const xxjdAuditsSql = `SELECT audit_id FROM ?? WHERE tid in (${tids.join(',')}) ${newUids.length > 0 ? ` AND audit_id NOT IN (${newUids.join(',')})` : ''} GROUP BY audit_id`;
            // const xxjdAuditParams = [this.ctx.service.scheduleAudit.tableName];
            // const xxjdResult = await conn.query(xxjdAuditsSql, xxjdAuditParams);
            // newUids.push(...this._.map(xxjdResult, 'audit_id'));
            const diffUids = this._.difference(newUids, hadUids);
            if (diffUids.length > 0) {
                const insertData = diffUids.map(x => {
                    return { id: this.uuid.v4(), spid: subProject.id, pid: subProject.project_id, uid: x };
                });
                await conn.insert(this.ctx.service.subProjPermission.tableName, insertData);
            }
        }

        async getNoSpTenders(pid) {
            const list = await this.getAllDataByCondition({ where: { project_id: pid, spid: '' }, orders: [['create_time', 'desc']] });
            const accountList = await this.ctx.service.projectAccount.getAllDataByCondition({ where: { project_id: pid } });
            for (const l of list) {
                const user = accountList.find(item => item.id === l.user_id);
                l.user_name = user ? user.name : '';
            }
            return list;
        }
    }

    return Tender;
};