Calculation/app/service/deal_bills.js

'use strict';

/**
 *
 *
 * @author Mai
 * @date 2018/5/8
 * @version
 */

module.exports = app => {
    class DealBills extends app.BaseService {

        /**
         * 构造函数
         * @param ctx
         */
        constructor(ctx) {
            super(ctx);
            this.tableName = 'deal_bills';
        }

        async getSum(tenderId) {
            const sql = 'SELECT Sum(`total_price`) As `total_price` ' +
                '  From ' + this.tableName +
                '  WHERE `tender_id` = ? ';
            const sqlParam = [tenderId];
            return await this.db.queryOne(sql, sqlParam);
        }

        /**
         * 导入Excel数据(node-xlsx)
         *
         * @param {Array} sheet - Excel文件中的全部工作表
         * @param {Number} tenderId - 所属标段Id
         * @returns {Promise<boolean>}
         */
        async importData(sheet, tenderId) {
            let result = false;
            const transaction = await this.db.beginTransaction();
            try {
                const bills = [];
                // 识别表头导入
                let iCode = -1, iName = -1, iUnit = -1, iUp = -1, iQty = -1, iTp = -1, bCheckCol = false;
                for (let iRow = 0; iRow < sheet.data.length; iRow++) {
                    const row = sheet.data[iRow];
                    if (!bCheckCol) {
                        for (let iCol = 0; iCol < row.length; iCol++) {
                            let value = row[iCol];
                            if (typeof value !== "string") { continue }
                            value = this.ctx.helper._.trim(value);
                            if (['清单编号', '子目号', '子目编号', '编号', '清单号'].indexOf(value) >= 0) iCode = iCol;
                            if (['清单名称', '名称', '子目名称'].indexOf(value) >= 0) iName = iCol;
                            if (value.indexOf('单位') >= 0) iUnit = iCol;
                            if (value.indexOf('单价') >= 0) iUp = iCol;
                            if (value.indexOf('数量') >= 0) iQty = iCol;
                            if (value.indexOf('金额') >= 0 || value.indexOf('合价') >= 0) iTp = iCol;
                        }
                        bCheckCol = (iCode >= 0 && iName >= 0 && iUnit >= 0 && iUp >= 0 && iQty >= 0);
                        if (!bCheckCol) {
                            iCode = -1;
                            iName = -1;
                            iUnit = -1;
                            iUp = -1;
                            iQty = -1;
                        }
                    } else {
                        const code = this.ctx.helper._.trim(row[iCode]);
                        console.log(code);
                        if (this.ctx.helper.validBillsCode(code)) {
                            const data = {
                                deal_id: bills.length + 1,
                                tender_id: tenderId,
                                code: code,
                                name: row[iName],
                                unit: row[iUnit],
                                unit_price: row[iUp],
                                quantity: row[iQty],
                            };
                            if ((data.unit_price && !this._.isNumber(data.unit_price)) || (data.quantity && !this._.isNumber(data.quantity))) {
                                throw '导入的Excel的数据类型有误，请检查第' + (iRow + 1) + '行';
                            }
                            bills.push(data);
                        }
                    } 
                }
                if (!bCheckCol) {
                    throw '导入的Excel表头定义有误，请下载示例检查';
                }
                // 固定列，从第一行开始导入
                // let iCode = 0, iName = 1, iUnit = 2, iUp = 4, iQty = 3;
                // for (let iRow = 1; iRow < sheet.data.length; iRow++) {
                //     const data = {
                //         deal_id: bills.length + 1,
                //         tender_id: tenderId,
                //         code: row[iCode],
                //         name: row[iName],
                //         unit: row[iUnit],
                //         unit_price: row[iUp],
                //         quantity: row[iQty],
                //     };
                //     if (!data.code || data.code === '' || !data.name || data.name === '') continue;
                //     if ((data.unit_price && !this._.isNumber(data.unit_price)) || (data.quantity && !this._.isNumber(data.quantity))) {
                //         throw '导入的Excel的数据类型有误，请检查第' + (iRow + 1) + '行';
                //     }
                //     bills.push(data);
                // }
                if (bills.length > 0) {
                    await transaction.delete(this.tableName, {tender_id: tenderId});
                    const billsResult = await transaction.insert(this.tableName, bills);
                    if (billsResult.affectedRows !== bills.length) {
                        throw '导入签约清单数据出错';
                    }
                } else {
                    throw 'Excel文件中无签约清单数据';
                }
                await transaction.commit();
                result = true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
            return result;
        }

        /**
         * 导入Excel数据(js-xlsx)
         *
         * @param {Array} sheet - Excel文件中的全部工作表
         * @param {Number} tenderId - 所属标段Id
         * @returns {Promise<boolean>}
         */
        async importDataJsXlsx(sheet, tenderId) {
            let result = false;
            // 整理数据
            const bills = [];
            let iCode = -1, iName = -1, iUnit = -1, iUp = -1, iQty = -1, iTp = -1, bCheckCol = false;
            for (let iRow = 0; iRow < sheet.rows.length; iRow++) {
                const row = sheet.rows[iRow];
                if (!bCheckCol) {
                    for (let iCol = 0; iCol < row.length; iCol++) {
                        let value = row[iCol];
                        if (typeof value !== "string") { continue }
                        value = this.ctx.helper._.trim(value);
                        if (value === '子目号' || value === '清单编号') iCode = iCol;
                        if (value.indexOf('名称') >= 0) iName = iCol;
                        if (value.indexOf('单位') >= 0) iUnit = iCol;
                        if (value.indexOf('单价') >= 0) iUp = iCol;
                        if (value.indexOf('数量') >= 0) iQty = iCol;
                        if (value.indexOf('金额') >= 0 || value.indexOf('合价') >= 0) iTp = iCol;
                    }
                    bCheckCol = (iCode >= 0 && iName >= 0 && iUnit >= 0 && iUp >= 0 && iQty >= 0 && iTp >= 0);
                    if (!bCheckCol) {
                        iCode = -1;
                        iName = -1;
                        iUnit = -1;
                        iUp = -1;
                        iQty = -1;
                        iTp = -1;
                    }
                } else {
                    const code = this.ctx.helper.replaceReturn(this.ctx.helper._.trim(row[iCode]));
                    //if (this.ctx.helper.validBillsCode(code)) {
                    if (code) {
                        const data = {
                            id: this.uuid.v4(),
                            order: bills.length + 1,
                            tender_id: tenderId,
                            code: code,
                            name: this.ctx.helper.replaceReturn(row[iName]),
                            unit: this.ctx.helper.replaceReturn(row[iUnit]),
                            unit_price: (row[iUp] === undefined || row[iUp] === null) ? 0 : this._.toNumber(row[iUp]),
                            quantity: (row[iQty] === undefined || row[iQty] === null) ? 0 : this._.toNumber(row[iQty]),
                            total_price: (row[iTp] === undefined || row[iTp] === null) ? 0 : this._.toNumber(row[iTp]),
                        };
                        if (this._.isNaN(data.unit_price) || this._.isNaN(data.quantity) || this._.isNaN(data.total_price)) {
                            throw '导入的Excel的数据类型有误，请检查第' + (iRow + 1) + '行';
                        }
                        bills.push(data);
                    }
                }
            }
            if (!bCheckCol) {
                throw '导入的Excel表头定义有误，请下载示例检查';
            }
            // 写入数据
            const transaction = await this.db.beginTransaction();
            try {
                if (bills.length > 0) {
                    await transaction.delete(this.tableName, {tender_id: tenderId});
                    const billsResult = await transaction.insert(this.tableName, bills);
                    if (billsResult.affectedRows !== bills.length) {
                        throw '导入签约清单数据出错';
                    }
                } else {
                    throw 'Excel文件中无签约清单数据';
                }
                await transaction.commit();
                result = true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
            return result;
        }

        /*
        * 报表用
        * @param {Number} tenderId - 所属标段Id
        */
        async getDataByTenderId(tenderId) {
            const sql = 'SELECT Bills.* FROM ' + this.tableName + ' As Bills WHERE tender_id = ? ORDER BY `order` ASC';
            const sqlParam = [tenderId];
            return await this.db.query(sql, sqlParam);
            // let rst = await this.getDataByCondition({tender_id: tenderId});
            // return rst;
        }

        async _addDatas(data) {
            const info =  this.ctx.tender.info;

            const datas = data instanceof Array ? data : [data];
            const insertData = [];
            for (const d of datas) {
                if (!d.code || !d.order) throw '新增签约清单，提交的数据错误';
                const nd = { id: this.uuid.v4(), tender_id: this.ctx.tender.id };
                nd.code = d.code;
                nd.order = d.order;
                if (d.name) nd.name = d.name;
                if (d.unit) nd.unit = d.unit;
                if (d.unit_price) nd.unit_price = this.ctx.helper.round(d.unit_price, info.decimal.up);
                const precision = this.ctx.helper.findPrecision(info.precision, d.unit);
                if (d.quantity) {
                    nd.quantity = this.ctx.helper.round(d.quantity, precision.value);
                    nd.total_price = this.ctx.helper.mul(nd.unit_price, nd.quantity, info.decimal.tp);
                }
                insertData.push(nd);
            }
            const result = await this.db.insert(this.tableName, insertData);
            return insertData;
        }

        async _delDatas (data) {
            await this.db.delete(this.tableName, {id: data});
            return data;
        }

        async _updateDatas (data) {
            const info =  this.ctx.tender.info;

            const datas = data instanceof Array ? data : [data];
            const orgDatas = await this.getAllDataByCondition({where: {id: this.ctx.helper._.map(datas, 'id')}});

            const uDatas = [];
            for (const d of datas) {
                const od = this.ctx.helper._.find(orgDatas, {id: d.id});
                if (!od) continue;

                const nd = {id: od.id};

                if (d.code !== undefined) nd.code = d.code;
                if (d.order !== undefined) nd.order = d.order;
                if (d.name !== undefined) nd.name = d.name;
                if (d.unit !== undefined) nd.unit = d.unit;
                nd.unit_price = d.unit_price !== undefined ? this.ctx.helper.round(d.unit_price, info.decimal.up) : od.unit_price;
                const precision = this.ctx.helper.findPrecision(info.precision, d.unit);
                if (d.quantity !== undefined) {
                    nd.quantity = this.ctx.helper.round(d.quantity, precision.value);
                    nd.total_price = this.ctx.helper.mul(nd.unit_price, nd.quantity, info.decimal.tp);
                } else {
                    nd.quantity = this.ctx.helper.round(od.quantity, precision.value);
                    nd.total_price = this.ctx.helper.mul(nd.unit_price, nd.quantity, info.decimal.tp);
                }
                uDatas.push(nd);
            }
            if (uDatas.length > 0) {
                await this.db.updateRows(this.tableName, uDatas);
                return uDatas;
            } else {
                return [];
            }
        }

        async updateDatas(data) {
            const result = {add: [], del: [], update: []};
            try {
                if (data.add) {
                    result.add = await this._addDatas(data.add);
                }
                if (data.update) {
                    result.update = await this._updateDatas(data.update);
                }
                if (data.del) {
                    result.del = await this._delDatas(data.del);
                }
                return result;
            } catch (err) {
                if (err) result.err = err;
                return result;
            }
        }
    }

    return DealBills;
}