ConstructionOperation/modules/reports/controllers/adhoc_task_controller.js

'use strict';

/**
 * Created by Tony on 2021/11/3.
 */
// const odbc = require('odbc');
// const odbcdb = new odbc.Database();

const ADODB = require('node-adodb');
const connection = ADODB.open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/GitHome/公路养护支持文件/空白.mdb;');
const rationItemDAO = require("../../ration_repository/models/ration_item");

module.exports = {
    createDesktopMdb: async function(req, res) {
        // console.log('request createDesktopMdb!');
        // console.log(req);
        let params = JSON.parse(req.body.params);
        let compilationId = params.compilationId,
            rationLibId = params.rationLibId,
            hasRationTree = params.hasRationTree,
            hasRation = params.hasRation,
            hasAssistRation = params.hasAssistRation,
            hasGLJ = params.hasGLJ,
            hasCOE = params.hasCOE
        ;
        // console.log(params);
        // let rst = {};
        if (hasRationTree || hasRation || hasGLJ || hasCOE) {
            //const rst = await rationItem.prepareInitData(data.rationRepId);
            let mixData = await rationItemDAO.prepareInitData(rationLibId);
            let gljCache = {};
            const prefix = '_'

            if (hasRationTree) {
                //mixData.sectionTree //定额树
                // await _addTreeData(mixData.sectionTree);
            }
            if (hasRation || hasGLJ) {
                for (let gljItem of mixData.gljList) {
                    gljCache[prefix + gljItem.ID] = gljItem;
                }
                //mixData.gljList //总工料机
                // await _addGLJData(mixData.gljList);
            }
            if (hasRation || hasCOE) {
                let rationItems = await rationItemDAO.getRationItemsByLib(rationLibId); //定额
                await _addRationData(rationItems);
                // await _addRationGljData(rationItems, gljCache, prefix);
            }
        }
        if (hasAssistRation) {
            //
        }
        res.json({error: 0, message: 'success', data: ''});
    }
};

const NODE_ID = "ID", P_ID = "ParentID", NEXT_ID = "NextSiblingID", ADHOC_PRE_ID="Previous_ID", CHILDREN_NODE = "items", SUB_ID = "sub_ids",
    EMPTY_ID_VAL = -1, TREE_LEVEL = 'treeLevel', TOP_BILL_ID = "topBillID";

let tree_Data_Helper = {
    buildTreeNodeDirectly: function(data, addLevel) {
        let topArr = [], rst = [], tmpNodes = {}, prefix = "id_";
        let private_getStartNode = function (idArr) {
            let tmpNodeRst = null;
            for (let i = 0; i < idArr.length; i++) {
                if (parseInt(tmpNodes[prefix + idArr[i]][ADHOC_PRE_ID]) === EMPTY_ID_VAL) {
                    tmpNodeRst = tmpNodes[prefix + idArr[i]];
                    break;
                }
            }
            return tmpNodeRst;
        };
        let private_buildNodeData = function(parentItem, idArr, treeLevel, tbID) {
            let iter = [], nextNode = private_getStartNode(idArr), pushedIds = [];
            while (nextNode !== null && nextNode !== undefined ) {
                if (parentItem) {
                    parentItem[CHILDREN_NODE].push(nextNode);
                } else {
                    rst.push(nextNode);
                }
                iter.push(nextNode);
                pushedIds.push(nextNode[NODE_ID]);
                nextNode[TOP_BILL_ID] = tbID;
                if (parentItem === null) {
                    nextNode[TOP_BILL_ID] = nextNode[NODE_ID];
                    if (nextNode.flags && nextNode.flags.length > 0) {
                        for (let flag of nextNode.flags) {
                            if (flag.fieldName === "fixed") {
                                nextNode[TOP_BILL_ID] = flag.flag;
                                break;
                            }
                        }
                    }
                }
                if (addLevel) nextNode[TREE_LEVEL] = treeLevel;
                nextNode = tmpNodes[prefix + nextNode[NEXT_ID]];
                if (nextNode === null || nextNode === undefined) {
                    //备注： 考虑到实际数据的健壮性，有些节点会掉链子，需要用 parentItem[SUB_ID] 比对已经加上的节点，如发现加上的节点数量不够，那就得在这里补充上去
                    if (parentItem) {
                        if (parentItem[SUB_ID].length > iter.length) {
                            for (let subId of parentItem[SUB_ID]) {
                                if (pushedIds.indexOf(subId) < 0) {
                                    let restNode = tmpNodes[prefix + subId];
                                    if (addLevel) restNode[TREE_LEVEL] = treeLevel;
                                    restNode[TOP_BILL_ID] = tbID;
                                    parentItem[CHILDREN_NODE].push(restNode);
                                    iter.push(restNode);
                                }
                            }
                        }
                    } else {
                        if (idArr.length > iter.length) {
                            for (let topId of idArr) {
                                if (pushedIds.indexOf(topId) < 0) {
                                    let restNode = tmpNodes[prefix + topId];
                                    if (addLevel) restNode[TREE_LEVEL] = treeLevel;
                                    restNode[TOP_BILL_ID] = restNode[NODE_ID];
                                    if (restNode.flags && restNode.flags.length > 0) {
                                        for (let flag of restNode.flags) {
                                            if (flag.fieldName === "fixed") {
                                                restNode[TOP_BILL_ID] = flag.flag;
                                                break;
                                            }
                                        }
                                    }
                                    rst.push(restNode);
                                    iter.push(restNode);
                                }
                            }
                        }
                    }
                }
            }
            pushedIds = [];
            for (let i = 0; i < iter.length; i++) {
                let rtbID = tbID;
                if (parentItem === null) {
                    rtbID = iter[i][TOP_BILL_ID];
                }
                private_buildNodeData(iter[i], iter[i][SUB_ID], (treeLevel + 1), rtbID);
            }
        };
        //1. 给每个节点设置key, 顺便找Top Node
        for (let i = 0; i < data.length; i++) {
            tmpNodes[prefix + data[i][NODE_ID]] = data[i];
            data[i][ADHOC_PRE_ID] = EMPTY_ID_VAL;
            data[i][SUB_ID] = [];
            data[i][CHILDREN_NODE] = [];
            if (parseInt(data[i][P_ID]) === EMPTY_ID_VAL) {
                topArr.push(data[i][NODE_ID]);
            }
        }
        //2. 通过key，设置好兄弟/父子关系
        for (let i = 0; i < data.length; i++) {
            if (parseInt(data[i][NEXT_ID]) !== EMPTY_ID_VAL) {
                if (tmpNodes[prefix + data[i][NEXT_ID]] !== undefined){
                    if (tmpNodes[prefix + data[i][NEXT_ID]][P_ID] === data[i][P_ID]) {
                        tmpNodes[prefix + data[i][NEXT_ID]][ADHOC_PRE_ID] = data[i][NODE_ID];
                    } else {
                        tmpNodes[prefix + data[i][NEXT_ID]][ADHOC_PRE_ID] = EMPTY_ID_VAL;
                        data[i][NEXT_ID] = EMPTY_ID_VAL;
                    }
                }
            }
            if (parseInt(data[i][P_ID]) !== EMPTY_ID_VAL) {
                tmpNodes[prefix + data[i][P_ID]][SUB_ID].push(data[i][NODE_ID]);
            }
        }
        //3. 开build
        private_buildNodeData(null, topArr, 0, -1);
        //try to release and return
        tmpNodes = null;
        topArr.length = 0;
        return rst;
    },

    getFlatArray: function (srcArr, destArr) {
        let private_put = function (parentItem) {
            destArr.push(parentItem);
            if (parentItem.items) {
                for (let subItem of parentItem.items) {
                    private_put(subItem);
                }
            }
        }
        for (let node of srcArr) {
            private_put(node);
        }
        for (let item of destArr) {
            delete item[CHILDREN_NODE];
            delete item[SUB_ID];
            delete item[ADHOC_PRE_ID];
        }
    }
};

async function _addRationGljData(rationItemArr, gljCache, prefix) {
    console.log('start Ration GLJ!');
    // console.log(`Ration GLJ total amount: ${rationGljArr.length}`);
    for (let idx = 0; idx < rationItemArr.length; idx++) {
        let rationItem = rationItemArr[idx];
        for (let rgljItem of rationItem.rationGljList) {
            if (parseFloat(rgljItem.consumeAmt) > 0) {
                let fieldStr = 'RationID, GLJID, Amount, Proportion, Type';
                let valueStr = `${rationItem.ID}, ${rgljItem.gljId}, ${rgljItem.consumeAmt}, ${(rgljItem.proportion !== undefined)?rgljItem.proportion:0}, ${gljCache[prefix + rgljItem.gljId].gljType}`;
                let sqlStr = `INSERT INTO Ration_GLJ(${fieldStr}) VALUES (${valueStr})`;
                try {
                    // console.log(`executing SQL: ${sqlStr}`);
                    await connection.execute(sqlStr);
                } catch(ex) {
                    console.log(`execute SQL with error: ${sqlStr}`);
                }
            }
        }
    }
    console.log('end Ration GLJ!');
}

async function _addRationData(rationItemArr) {
    console.log('start Ration!');
    console.log(`Ration total amount: ${rationItemArr.length}`);
    for (let idx = 0; idx < rationItemArr.length; idx++) {
        let item = rationItemArr[idx];
        let fieldStr = 'ID, IDCode, Code, Name, Unit, BasePrice, SectionID, Caption, FeeType, Name2, Unit2, UUID';
        let valueStr = `${item.ID}, ${_getIDCode(item.code)}, '${item.code}', '${item.name}', '${item.unit}', ${item.basePrice}, ${item.sectionId}, '${item.caption}', ${item.feeType}, '', '', '' `;
        let sqlStr = `INSERT INTO RationItems(${fieldStr}) VALUES (${valueStr})`;
        try {
            // console.log(`executing SQL: ${sqlStr}`);
            await connection.execute(sqlStr);
        } catch(ex) {
            console.log(`execute SQL with error: ${sqlStr}`);
        }
    }
    console.log('end Ration!');
}

async function _addGLJData(gljItemArr) {
    console.log('start GLJ!');
    console.log(`GLJ total amount: ${gljItemArr.length}`);
    for (let idx = 0; idx < gljItemArr.length; idx++) {
        let item = gljItemArr[idx];
        let fieldStr = 'ID, Code, Name, Specs, Unit, BasePrice, Main, New, Type, DetailType, ShortName, Name2, Unit2, SortParam, CalculateType, UUID, 字段1, 字段2';
        let valueStr = `${item.ID}, '${item.code}', '${item.name}', '${item.specs}', '${item.unit}', ${(item.basePrice !== undefined)?item.basePrice:0}, 0, 0, ${item.gljType}, ${item.gljClass}, '${item.shortName}', '', '', '', '', '', '', ''`;
        let sqlStr = `INSERT INTO GLJList(${fieldStr}) VALUES (${valueStr})`;
        try {
            // console.log(`executing SQL: ${sqlStr}`);
            await connection.execute(sqlStr);
        } catch(ex) {
            console.log(`execute SQL with error: ${sqlStr}`);
        }
    }
    console.log('end GLJ!');
}

async function _addTreeData(treeDataArr) {
    console.log('start tree!');
    console.log(`GLJ total amount: ${treeDataArr.length}`);
    let tmpTreeArr = tree_Data_Helper.buildTreeNodeDirectly(treeDataArr, true);
    for (let idx = 0; idx < tmpTreeArr[0].items.length; idx++) {
        _setChapterLevel(tmpTreeArr[0].items[idx], idx + 1);
    }
    let sectionTreeArr = [];
    tree_Data_Helper.getFlatArray(tmpTreeArr, sectionTreeArr);
    for (let sectionNode of sectionTreeArr) {
        let fieldStr = 'ID, ParentID, NextSiblingID, Name, Code, FullCode, Name2';
        let valueStr = `${sectionNode.ID},${sectionNode.ParentID},${sectionNode.NextSiblingID},'${sectionNode.name}',${sectionNode.chapterLv === undefined?-1:sectionNode.chapterLv},'',''`;
        let sqlStr = `INSERT INTO RationTree(${fieldStr}) VALUES (${valueStr})`;
        try {
            // console.log(`executing SQL: ${sqlStr}`);
            await connection.execute(sqlStr);
        } catch(ex) {
            console.log(`execute SQL with error: ${sqlStr}`);
        }
    }
    console.log('end tree!');
}

function _setChapterLevel(chapterNode, cLv) {
    chapterNode.chapterLv = cLv;
    if (chapterNode.items && chapterNode.items.length > 0) {
        for (let subNode of chapterNode.items) {
            _setChapterLevel(subNode, cLv);
        }
    }
}

function _getIDCode(code) {
    let rst = '';
    if (code !== null && code !== undefined) {
        let codes = code.split('-');
        if (codes.length === 3) {
            let suppls = ['', '', ''];

            let regExp = new RegExp('D', "gm");
            codes[0] = codes[0].replace(regExp, '');

            if (codes[0].length < 2) {
                for (let idx = codes[0].length; idx < 2; idx++) {
                    suppls[0] = suppls[0] + '0';
                }
            }
            if (codes[1].length < 2) {
                for (let idx = codes[1].length; idx < 2; idx++) {
                    suppls[1] = suppls[1] + '0';
                }
            }
            if (codes[2].length < 3) {
                for (let idx = codes[2].length; idx < 3; idx++) {
                    suppls[2] = suppls[2] + '0';
                }
            }
            for (let si = 0; si < codes.length; si++) {
                rst += (suppls[si] + codes[si]);
            }
        } else {
            //
        }
    }
    return rst;
}