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++) {
                            const value = row[iCol];
                            if (typeof value !== "string") { continue }
                            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++) {
                        const value = row[iCol];
                        if (typeof value !== "string") { continue }
                        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 = {
                            deal_id: 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 = ?';
            const sqlParam = [tenderId];
            return await this.db.query(sql, sqlParam);
            // let rst = await this.getDataByCondition({tender_id: tenderId});
            // return rst;
        }
    }

    return DealBills;
}