ConstructionOperation/modules/price_info_lib/facade/index.js

const mongoose = require('mongoose');
const uuidV1 = require('uuid/v1');
const _ = require('lodash');
const scMathUtil = require('../../../public/scMathUtil').getUtil();
const { CRAWL_LOG_KEY, ProcessStatus } = require('../../../public/constants/price_info_constant');

const priceInfoLibModel = mongoose.model('std_price_info_lib');
const priceInfoClassModel = mongoose.model('std_price_info_class');
const priceInfoItemModel = mongoose.model('std_price_info_items');
const priceInfoAreaModel = mongoose.model('std_price_info_areas');
const compilationModel = mongoose.model('compilation');
const importLogsModel = mongoose.model('import_logs');
const priceInfoIndexModel = mongoose.model('std_price_info_index');
const priceInfoSummaryModel = mongoose.model('std_price_info_summary');


async function getLibs(query) {
    return await priceInfoLibModel.find(query).lean();
}

async function createLib(name, period, compilationID) {
    // 将2020-01变成2020年01月
    const reg = /(\d{4})-(\d{2})/;
    const formattedPeriod = period.replace(reg, '$1年-$2月');
    const lib = {
        ID: uuidV1(),
        name,
        period: formattedPeriod,
        compilationID,
        createDate: Date.now(),
    };
    await priceInfoLibModel.create(lib);
    return lib;
}

async function updateLib(query, updateData) {
    await priceInfoLibModel.update(query, updateData);
}

async function deleteLib(libID) {
    await priceInfoClassModel.remove({ libID });
    await priceInfoItemModel.remove({ libID });
    await priceInfoLibModel.remove({ ID: libID });
}

async function processChecking(key) {
    const logData = key
        ? await importLogsModel.findOne({ key })
        : await importLogsModel.findOne({ key: CRAWL_LOG_KEY });
    if (!logData) {
        return { status: ProcessStatus.FINISH };
    }
    if (logData.status === ProcessStatus.FINISH || logData.status === ProcessStatus.ERROR) {
        await importLogsModel.remove({ key: logData.key });
    }
    return { status: logData.status, errorMsg: logData.errorMsg || '', key: logData.key };
}

// 爬取数据
async function crawlDataByCompilation(compilationID, from, to) {
    if (!compilationID) {
        throw '无有效费用定额。';
    }
    const compilationData = await compilationModel.findOne({ _id: mongoose.Types.ObjectId(compilationID) }, 'overWriteUrl').lean();
    if (!compilationData || !compilationData.overWriteUrl) {
        throw '无有效费用定额。';
    }
    // 从overWriteUrl提取并组装爬虫文件
    const reg = /\/([^/]+)\.js/;
    const matched = compilationData.overWriteUrl.match(reg);
    const crawlURL = `${matched[1]}_price_crawler.js`;
    let crawlData;
    try {
        const crawler = require(`../../../web/over_write/crawler/${crawlURL}`);
        crawlData = crawler.crawlData;
    } catch (e) {
        console.log(e);
        throw '该费用定额无可用爬虫方法。'
    }
    //await crawlData(from, to);
    // 异步不等结果，结果由checking来获取
    crawlDataByMiddleware(crawlData, from, to, compilationID);
}

// 爬取数据中间件，主要处理checking初始化
async function crawlDataByMiddleware(crawlFunc, from, to, compilationID) {
    const logUpdateData = { status: ProcessStatus.FINISH };
    try {
        const logData = {
            key: CRAWL_LOG_KEY,
            content: '正在爬取数据，请稍候……',
            status: ProcessStatus.START,
            create_time: Date.now()
        };
        await importLogsModel.create(logData);
        await crawlFunc(from, to, compilationID);
    } catch (err) {
        console.log(err);
        logUpdateData.errorMsg = String(err);
        logUpdateData.status = ProcessStatus.ERROR;
    } finally {
        await importLogsModel.update({ key: CRAWL_LOG_KEY }, logUpdateData);
    }
}

// 导入excel数据，格式如下
// 格式1：
//地区	    分类	        编码	名称	        规格型号    单位	不含税价	含税价
//江北区	黑色及有色金属		    热轧光圆钢筋	  φ6（6.5）	         3566.37	4030
//江北区	木、竹材料及其制品		柏木门套线	      60×10	             8.76	    9.9
// 格式2：
//地区	    分类	            编码	名称	        规格型号	    不含税价	含税价
//江北区	黑色及有色金属		        热轧光圆钢筋	  φ6（6.5）	     3566.37	4030
//			                          柏木门套线	    60×10	       8.76	      9.9
//			                          沥青混凝土	    AC-13	       982.3	 1110
//						
//北碚区	木、竹材料及其制品		    热轧光圆钢筋	  φ6（6.5）	      3566.37	4030
async function importExcelData(libID, sheetData) {
    const libs = await getLibs({ ID: libID });
    const compilationID = libs[0].compilationID;
    // 建立区映射表：名称-ID映射、ID-名称映射
    const areaList = await getAreas(compilationID);
    const areaMap = {};
    areaList.forEach(({ ID, name }) => {
        areaMap[name] = ID;
        areaMap[ID] = name;
    });
    // 建立分类映射表：地区名称@分类名称：ID映射
    /*  const classMap = {};
     const classList = await getClassData(libID);
     classList.forEach(({ ID, areaID, name }) => {
         const areaName = areaMap[areaID] || '';
         classMap[`${areaName}@${name}`] = ID;
     }); */
    // 第一行获取行映射
    const colMap = {};
    for (let col = 0; col < sheetData[0].length; col++) {
        const cellText = sheetData[0][col];
        switch (cellText) {
            case '地区':
                colMap.area = col;
                break;
            case '分类':
                colMap.class = col;
                break;
            case '编码':
                colMap.code = col;
                break;
            case '名称':
                colMap.name = col;
                break;
            case '规格型号':
                colMap.specs = col;
                break;
            case '单位':
                colMap.unit = col;
                break;
            case '不含税价':
                colMap.noTaxPrice = col;
                break;
            case '含税价':
                colMap.taxPrice = col;
                break;
        }
    }
    // 提取数据
    const data = [];
    const classData = [];
    const areaClassDataMap = {};
    let curAreaName;
    let curClassName;
    let curClassID;
    for (let row = 1; row < sheetData.length; row++) {
        const areaName = sheetData[row][colMap.area] || '';
        const className = sheetData[row][colMap.class] || '';
        const code = sheetData[row][colMap.code] || '';
        const name = sheetData[row][colMap.name] || '';
        const specs = sheetData[row][colMap.specs] || '';
        const unit = sheetData[row][colMap.unit] || '';
        const noTaxPrice = sheetData[row][colMap.noTaxPrice] || '';
        const taxPrice = sheetData[row][colMap.taxPrice] || '';
        if (!className && !code && !name && !specs && !noTaxPrice && !taxPrice) { // 认为是空数据
            continue;
        }
        if (areaName && areaName !== curAreaName) {
            curAreaName = areaName;
        }
        const areaID = areaMap[curAreaName];
        if (!areaID) {
            continue;
        }
        if (className && className !== curClassName) {
            curClassName = className;
            const classItem = {
                libID,
                areaID,
                ID: uuidV1(),
                ParentID: '-1',
                NextSiblingID: '-1',
                name: curClassName
            };
            curClassID = classItem.ID;
            classData.push(classItem);
            (areaClassDataMap[areaID] || (areaClassDataMap[areaID] = [])).push(classItem);
            const preClassItem = areaClassDataMap[areaID][areaClassDataMap[areaID].length - 2];
            if (preClassItem) {
                preClassItem.NextSiblingID = classItem.ID;
            }
        }
        if (!curClassID) {
            continue;
        }
        data.push({
            ID: uuidV1(),
            compilationID,
            libID,
            areaID,
            classID: curClassID,
            period: libs[0].period,
            code,
            name,
            specs,
            unit,
            noTaxPrice,
            taxPrice
        });
    }
    if (classData.length) {
        await priceInfoClassModel.remove({ libID });
        await priceInfoClassModel.insertMany(classData);
    }
    if (data.length) {
        await priceInfoItemModel.remove({ libID });
        await priceInfoItemModel.insertMany(data);
    } else {
        throw 'excel没有有效数据。'
    }
}

// 导入excel关键字数据（主表+副表），目前只针对珠海，根据列号导入
/* 
主表：主从对应码	别名编码	材料名称	规格	单位	含税价（元）	除税价（元）	月份备注	计算式
副表：主从对应码	关键字	单位	关键字效果	组别	选项号
 */
async function importKeyData(libID, mainData, subData) {
    const lib = await priceInfoLibModel.findOne({ ID: libID }).lean();
    if (!lib) {
        throw new Error('库不存在');
    }
    const zh = await priceInfoAreaModel.findOne({ name: { $regex: '珠海' } }).lean();
    if (!zh) {
        throw new Error('该库不存在珠海地区');
    }
    // 删除珠海地区所有材料
    await priceInfoItemModel.deleteMany({ libID, areaID: zh.ID });

    const classItems = await priceInfoClassModel.find({ libID, areaID: zh.ID }).lean();
    // 分类树前四位编码 - 分类节点ID映射表
    let otherClassID = '';
    const classMap = {};
    classItems.forEach(item => {
        if (item.name) {
            if (!otherClassID && /其他/.test(item.name)) {
                otherClassID = item.ID;
            }
            const code = item.name.substr(0, 4);
            if (/\d{4}/.test(code)) {
                classMap[code] = item.ID;
            }
        }
    });

    // 主从对应码 - 关键字数组映射
    const keywordMap = {};
    for (let row = 1; row < subData.length; row++) {
        const rowData = subData[row];
        const keywordItem = {
            code: rowData[0] ? String(rowData[0]) : '',
            keyword: rowData[1] || '',
            unit: rowData[2] || '',
            coe: rowData[3] || '',
            group: rowData[4] || '',
            optionCode: rowData[5] || '',
        };
        if (!keywordItem.code) {
            continue;
        }
        (keywordMap[keywordItem.code] || (keywordMap[keywordItem.code] = [])).push(keywordItem);
    }

    const priceItems = [];
    for (let row = 1; row < mainData.length; row++) {
        const rowData = mainData[row];
        const code = rowData[0] ? String(rowData[0]) : '';
        if (!code) {
            continue;
        }
        const matchCode = code.substring(0, 4);
        const classID = classMap[matchCode] || otherClassID;
        const priceItem = {
            code,
            libID,
            classID,
            ID: uuidV1(),
            compilationID: lib.compilationID,
            areaID: zh.ID,
            period: lib.period,
            classCode: rowData[1] || '',
            name: rowData[2] || '',
            specs: rowData[3] || '',
            unit: rowData[4] || '',
            taxPrice: rowData[5] || '',
            noTaxPrice: rowData[6] || '',
            dateRemark: rowData[7] || '',
            expString: rowData[8] || '',
            keywordList: keywordMap[code] || [],
        }
        priceItems.push(priceItem);
    }
    if (priceItems.length) {
        await priceInfoItemModel.insertMany(priceItems);
    }
}

/* async function importExcelData(libID, sheetData) {
    const libs = await getLibs({ ID: libID });
    const compilationID = libs[0].compilationID;
    // 建立区映射表：名称-ID映射、ID-名称映射
    const areaList = await getAreas(compilationID);
    const areaMap = {};
    areaList.forEach(({ ID, name }) => {
        areaMap[name] = ID;
        areaMap[ID] = name;
    });
    // 建立分类映射表：地区名称@分类名称：ID映射
    const classMap = {};
    const classList = await getClassData(libID);
    classList.forEach(({ ID, areaID, name }) => {
        const areaName = areaMap[areaID] || '';
        classMap[`${areaName}@${name}`] = ID;
    });
    // 第一行获取行映射
    const colMap = {};
    for (let col = 0; col < sheetData[0].length; col++) {
        const cellText = sheetData[0][col];
        switch (cellText) {
            case '地区':
                colMap.area = col;
                break;
            case '分类':
                colMap.class = col;
                break;
            case '编码':
                colMap.code = col;
                break;
            case '名称':
                colMap.name = col;
                break;
            case '规格型号':
                colMap.specs = col;
                break;
            case '单位':
                colMap.unit = col;
                break;
            case '不含税价':
                colMap.noTaxPrice = col;
                break;
            case '含税价':
                colMap.taxPrice = col;
                break;
        }
    }
    // 提取数据
    const data = [];
    let curAreaName;
    let curClassName;
    for (let row = 1; row < sheetData.length; row++) {
        const areaName = sheetData[row][colMap.area] || '';
        const className = sheetData[row][colMap.class] || '';
        const code = sheetData[row][colMap.code] || '';
        const name = sheetData[row][colMap.name] || '';
        const specs = sheetData[row][colMap.specs] || '';
        const unit = sheetData[row][colMap.unit] || '';
        const noTaxPrice = sheetData[row][colMap.noTaxPrice] || '';
        const taxPrice = sheetData[row][colMap.taxPrice] || '';
        if (!code && !name && !specs && !noTaxPrice && !taxPrice) { // 认为是空数据
            continue;
        }
        if (areaName && areaName !== curAreaName) {
            curAreaName = areaName;
        }
        if (className && className !== curClassName) {
            curClassName = className;
        }
        const areaID = areaMap[curAreaName];
        if (!areaID) {
            continue;
        }
        const classID = classMap[`${curAreaName}@${curClassName}`];
        if (!classID) {
            continue;
        }
        data.push({
            ID: uuidV1(),
            compilationID,
            libID,
            areaID,
            classID,
            period: libs[0].period,
            code,
            name,
            specs,
            unit,
            noTaxPrice,
            taxPrice
        });
    }
    if (data.length) {
        await priceInfoItemModel.remove({ libID });
        await priceInfoItemModel.insertMany(data);
    } else {
        throw 'excel没有有效数据。'
    }
} */

// 获取费用定额的地区数据
async function getAreas(compilationID) {
    return await priceInfoAreaModel.find({ compilationID }, '-_id ID name serialNo').lean();
}

async function updateAres(updateData) {
    const bulks = [];
    updateData.forEach(({ ID, field, value }) => bulks.push({
        updateOne: {
            filter: { ID },
            update: { [field]: value }
        }
    }));
    if (bulks.length) {
        await priceInfoAreaModel.bulkWrite(bulks);
    }
}

async function insertAreas(insertData) {
    await priceInfoAreaModel.insertMany(insertData);
}

async function deleteAreas(deleteData) {
    await priceInfoClassModel.remove({ areaID: { $in: deleteData } });
    await priceInfoItemModel.remove({ areaID: { $in: deleteData } });
    await priceInfoAreaModel.remove({ ID: { $in: deleteData } });
}

async function getClassData(libID, areaID) {
    if (libID && areaID) {
        return await priceInfoClassModel.find({ libID, areaID }, '-_id').lean();
    }
    if (libID) {
        return await priceInfoClassModel.find({ libID }, '-_id').lean();
    }
    if (areaID) {
        return await priceInfoClassModel.find({ areaID }, '-_id').lean();
    }

}

async function getPriceData(classIDList) {
    return await priceInfoItemModel.find({ classID: { $in: classIDList } }, '-_id').lean();
}

const UpdateType = {
    UPDATE: 'update',
    DELETE: 'delete',
    CREATE: 'create',
};

async function editPriceData(postData) {
    const bulks = [];
    postData.forEach(data => {
        if (data.type === UpdateType.UPDATE) {
            bulks.push({
                updateOne: {
                    filter: { ID: data.ID },
                    update: { ...data.data }
                }
            });
        } else if (data.type === UpdateType.DELETE) {
            bulks.push({
                deleteOne: {
                    filter: { ID: data.ID }
                }
            });
        } else {
            bulks.push({
                insertOne: {
                    document: data.data
                }
            });
        }
    });
    if (bulks.length) {
        await priceInfoItemModel.bulkWrite(bulks);
    }
}

async function editClassData(updateData) {
    const bulks = [];
    const deleteIDList = [];
    updateData.forEach(({ type, filter, update, document }) => {
        if (type === UpdateType.UPDATE) {
            bulks.push({
                updateOne: {
                    filter,
                    update
                }
            });
        } else if (type === UpdateType.DELETE) {
            deleteIDList.push(filter.ID);
            bulks.push({
                deleteOne: {
                    filter
                }
            });
        } else {
            bulks.push({
                insertOne: {
                    document
                }
            });
        }
    });
    if (deleteIDList.length) {
        await priceInfoItemModel.remove({ classID: { $in: deleteIDList } });
    }
    if (bulks.length) {
        await priceInfoClassModel.bulkWrite(bulks);
    }
}

//计算指标平均值
function calcIndexAvg(period, areaID, compilationID, preCodeMap) {
    const newData = [];
    for (const code in preCodeMap) {
        const indexArr = preCodeMap[code];
        let total = 0;

        for (const index of indexArr) {
            total = scMathUtil.roundForObj(total + index, 2);
        }
        const avg = scMathUtil.roundForObj(total / indexArr.length, 2);
        newData.push({ ID: uuidV1(), code, period, areaID, compilationID, index: avg })
    }
    return newData
}

//一个月里有classCode相同，但是价格不同的情况，取平均值
function getClassCodePriceAvgMap(items) {
    const classCodeMap = {};
    for (const b of items) {
        classCodeMap[b.classCode] ? classCodeMap[b.classCode].push(b) : classCodeMap[b.classCode] = [b];
    }

    for (const classCode in classCodeMap) {
        const baseItems = classCodeMap[classCode];
        const item = baseItems[0];
        if (baseItems.length > 1) {
            let sum = 0;
            for (const b of baseItems) {
                sum += parseFloat(b.noTaxPrice);
            }
            classCodeMap[classCode] = { code: item.code, name: item.name, price: scMathUtil.roundForObj(sum / baseItems.length, 2) };
        } else {
            classCodeMap[classCode] = { code: item.code, name: item.name, price: parseFloat(item.noTaxPrice) }
        }

    }

    return classCodeMap

}

async function calcPriceIndex(libID, period, areaID, compilationID) {
    const baseItems = await priceInfoItemModel.find({ areaID, period: '2022年-01月' }).lean();//以珠海 22年1月的数据为基准
    const currentItems = await priceInfoItemModel.find({ areaID, period }).lean();
    const preCodeMap = {};//编码前4位-指数映射
    const baseAvgMap = getClassCodePriceAvgMap(baseItems);
    const currentAvgMap = getClassCodePriceAvgMap(currentItems);

    let message = '';

    for (const classCode in currentAvgMap) {
        const c = currentAvgMap[classCode];
        const preCode = c.code.substr(0, 4);
        let index = 1;
        const baseItem = baseAvgMap[classCode];
        const tem = { index, classCode, name: c.name, code: c.code };

        if (baseItem && baseItem.price) {//一个月份里有多个值时，先取平均再计算
            index = scMathUtil.roundForObj(c.price / baseItem.price, 2);
            tem.baseName = baseItem.name;
        }
        tem.index = index;
        if (Math.abs(index - 1) > 0.2) {
            const string = `classCode:${tem.classCode},编号：${tem.code},基础名称：${tem.baseName},当前库中名称：${tem.name}，指数：${tem.index};\n`;
            message += string;
            console.log(string)
        }

        preCodeMap[preCode] ? preCodeMap[preCode].push(index) : preCodeMap[preCode] = [index];
    }
    const newIndexData = calcIndexAvg(period, areaID, compilationID, preCodeMap)
    //删除旧数据
    await priceInfoIndexModel.deleteMany({ areaID, period });
    //插入新数据
    await priceInfoIndexModel.insertMany(newIndexData);
    return message;
}

async function exportExcelData(libID, areaName) {
    const area = await priceInfoAreaModel.findOne({ name: areaName }).lean();
    if (!area) {
        return [];
    }
    const priceItems = await priceInfoItemModel.find({ libID, areaID: area.ID }).lean();
    // 整理数据
    let priceData = [];
    for (const tmp of priceItems) {
        const item = [tmp.code || '', tmp.classCode || '', tmp.name || '', tmp.specs || '', tmp.unit || '', tmp.taxPrice || '', tmp.noTaxPrice || '', tmp.dateRemark || '', tmp.expString || ''];
        priceData.push(item);
    }
    const excelData = [['主从对应码', '别名编码', '材料名称', '规格型号', '单位', '含税价（元）', '除税价（元）', '多价备注', '计算式']];
    excelData.push.apply(excelData, priceData);

    return excelData;
}

const getMatchSummaryKey = (item) => {
    const props = ['name', 'specs', 'unit'];
    return props.map(prop => {
        const subKey = item[prop] ? item[prop].trim() : '';
        return subKey;
    }).join('@');

}

const getSummaryMap = (items) => {
    const map = {};
    items.forEach(item => {
        const key = getMatchSummaryKey(item);
        map[key] = item;
    });
    return map;
}

// 匹配总表
// 按规则匹配信息价的编码、别名编码、计算式(只匹配珠海建筑，要单独标记珠海地区)；
// 匹配规则：名称+规格型号+单位，与总表一致则自动填入编码、别名编码、计算式(珠海建筑)；
const matchSummary = async (compilationID, libID) => {
    const updateBulks = [];
    const areas = await priceInfoAreaModel.find({ compilationID }, '-_id ID name').lean();
    const areaNameMap = {};
    areas.forEach(area => {
        areaNameMap[area.ID] = area.name;
    });
    const priceItems = await priceInfoItemModel.find({ libID }, '-_id ID compilationID name specs unit areaID period').lean();
    const summaryItems = await priceInfoSummaryModel.find({}, '-_id ID name specs unit code classCode expString').lean();
    const summaryMap = getSummaryMap(summaryItems);
    priceItems.forEach(priceItem => {
        const key = getMatchSummaryKey(priceItem);
        const matched = summaryMap[key];
        if (matched) {
            const updateObj = {
                code: matched.code,
                classCode: matched.classCode,
            }
            console.log(matched);
            console.log(updateObj);
            const areaName = areaNameMap[priceItem.areaID];
            if (/珠海/.test(areaName)) {
                updateObj.expString = matched.expString;
            }
            updateBulks.push({
                updateOne: {
                    filter: { ID: priceItem.ID, compilationID: priceItem.compilationID, areaID: priceItem.areaID, period: priceItem.period },
                    update: updateObj
                }
            })
        }
    });
    if (updateBulks.length) {
        console.log(`updateBulks.length`, updateBulks.length);
        await priceInfoItemModel.bulkWrite(updateBulks);
    }
}

module.exports = {
    getLibs,
    createLib,
    updateLib,
    deleteLib,
    processChecking,
    crawlDataByCompilation,
    importExcelData,
    importKeyData,
    getAreas,
    updateAres,
    insertAreas,
    deleteAreas,
    getClassData,
    calcPriceIndex,
    getPriceData,
    editPriceData,
    editClassData,
    exportExcelData,
    matchSummary,
}