allstar/tools/mongo.js

const fs = require('fs')
const path = require('path')
const XLSX = require('xlsx')
// const Rsync = require('rsync')
const {MongoConf} = require('./config')

const MongoClient = require('mongodb').MongoClient
const assert = require('assert')

const dbIns = MongoConf['R1']
const connStr = `mongodb://${dbIns.host}:${dbIns.port}`
const connOpt = {
    auth : {
        user: dbIns.username,
        password: dbIns.password,
    },
    authSource: dbIns.dbname,
    authMechanism: "SCRAM-SHA-1",
    useNewUrlParser: true
}

const dataPath = "../assets/scripts/data"
const dataPrefix =  "allstar_"


/**
 * excel表转换为json对象
 * @param {String} filename 文件名
 */
const getSheet = function (filename) {
    let filePath = path.join(__dirname, "/excel", filename)
    // let buf = fs.readFile(filePath);
    let workbook = XLSX.readFileSync(filePath)
    let sheets = workbook.SheetNames
    let sheetsObj = {}
    
    sheets.forEach(n => {
        let ws = workbook.Sheets[n]
        let def = XLSX.utils.sheet_to_json(ws, {range: 0, header: 1})
        let typeMap = {}
        let typeArr = def[0]
        let keyArr = def[2]
        keyArr.forEach((item, index) => {
            typeMap[item] = typeArr[index]
        })

        let res = XLSX.utils.sheet_to_json(ws, {range: 2})
        res.forEach(item => {
            for(let i in item) {
                item[i] = formatType(item[i], typeMap[i])
            }
        })
        sheetsObj[n] = res
    })
    return sheetsObj
    
    // console.log(XLSX.utils.sheet_to_json(ws));
    // console.log(workbook.SheetNames);
}

/**
 * 写入json内容到客户端assets目录
 * @param {String} filename 文件名
 * @param {Object} content json内容
 */
const writeToAssets = function (filename, content) {
    let writeFileName = filename.replace(/\.xlsx|\.xls/, '.js').replace(dataPrefix, "")
    let writePath = path.join(__dirname, dataPath, writeFileName)
    fs.writeFileSync(writePath, 'module.exports=' + JSON.stringify(content))
    // console.log(JSON.stringify(content, null, 2));
}

/**
 * 配置表生成的json内容rsync到静态资源服务器
 * @param {String} filename 文件名
 * @param {Object} content json内容
 */
const writeJson = function (filename, content) {
    let writeFileName = filename.replace(/\.xlsx|\.xls/, '.json').replace(dataPrefix, "")
    let writePath = path.join(__dirname, "/sheet", writeFileName)

    fs.writeFileSync(writePath, JSON.stringify(content))
}

const formatType = function(target, type) {
    let _type = type.toLowerCase()
    switch (_type) {
        case 'integer':
            target = parseInt(target, 10)
            break;
        case 'double':
            target = parseFloat(target)
            break;
        case 'string':
            target = target.toString()
            break;
        case 'object':
            target = JSON.parse(target)
            break;
        case 'array':
            target = JSON.parse(target)
            break;
        default:
            break;
    }
    return target
}

const init = function () {
    fs.readdir("./excel", (err, file) => {
        file.forEach(n => {
            if(n.match(/\~\$/)) {
                return
            }
            let ws = getSheet(n)
            let sheetName = n.replace(/\.xlsx|\.xls/, "")

            let target
            if(ws.length > 0) {
                //单个表
                // ws.forEach(n => {
                //     if(typeof n['id'] != undefined) {
                //         n['_id'] = n['id']
                //     }
                // })

                target = ws
            } else {
                //多个表合拼成一个Array
                let _array = []
                for(let i in ws) {
                    _array = _array.concat(ws[i])
                }
                target = _array                
            }

            MongoClient.connect(connStr, connOpt, (err, client) => {
                var db = client.db(dbIns.dbname)
                var collection = db.collection(sheetName)
                collection.drop(null, () => {
                    collection.insert(target, () => {
                        console.log(`成功更新配置表：${sheetName}`)
                        client.close()
                    })
                })
            });

            //把生成的js写入客户端assets目录
            let wsKeys = Object.keys(ws)
            if(wsKeys.length == 1) {
                writeToAssets(n, ws[wsKeys[0]])
                writeJson(n, ws[wsKeys[0]])
            } else {
                writeToAssets(n, ws)
                writeJson(n, ws)
            }
        })

        // process.exit()
    })
}

init()