Calculation/db_script/bak/depart-database-table.js

'use strict';

/**
 *
 *
 * @author Mai
 * @date
 * @version
 */

const mysql = require('mysql');
const fs = require('fs');

const config = process.argv.splice(2);
const mysqlOptions = require(`../config/config.${config}`)({ baseDir: __dirname + '/app', root: __dirname, name: 'calc' }).mysql;

const pool = mysql.createPool(mysqlOptions.client);

const querySql = async function (sql, sqlParam) {
    return new Promise(function (resolve, reject) {
        pool.getConnection(function (err, conn) {
            if (err) {
                reject(err);
            } else {
                conn.query(sql, sqlParam, function (err, rows, fields) {
                    //释放连接
                    conn.release();
                    //传递Promise回调对象
                    resolve({"err": err, "rows": rows, "fields": fields});
                });
            }
        });
    });
};

const ledgerSql = 'CREATE TABLE `zh_ledger_?` (\n' +
    '  `id` varchar(100) NOT NULL COMMENT \'自增id\',\n' +
    '  `tender_id` int(10) NOT NULL COMMENT \'标段id\',\n' +
    '  `ledger_id` int(10) NOT NULL COMMENT \'节点id\',\n' +
    '  `ledger_pid` int(10) NOT NULL COMMENT \'父节点id\',\n' +
    '  `level` tinyint(4) NOT NULL COMMENT \'层级\',\n' +
    '  `order` mediumint(4) NOT NULL DEFAULT \'0\' COMMENT \'同级排序\',\n' +
    '  `full_path` varchar(255) DEFAULT \'\' COMMENT \'层级定位辅助字段parent.full_path.ledger_id\',\n' +
    '  `is_leaf` tinyint(1) NOT NULL COMMENT \'是否叶子节点，界面显示辅助字段\',\n' +
    '  `code` varchar(50) DEFAULT \'\' COMMENT \'节点编号\',\n' +
    '  `b_code` varchar(50) DEFAULT NULL,\n' +
    '  `name` varchar(255) DEFAULT NULL COMMENT \'名称\',\n' +
    '  `unit` varchar(15) DEFAULT \'\' COMMENT \'单位\',\n' +
    '  `source` varchar(30) DEFAULT \'\' COMMENT \'添加源\',\n' +
    '  `remark` varchar(60) DEFAULT \'\' COMMENT \'备注\',\n' +
    '  `quantity` decimal(24,8) DEFAULT NULL COMMENT \'数量\',\n' +
    '  `unit_price` decimal(24,8) DEFAULT NULL COMMENT \'单价\',\n' +
    '  `total_price` decimal(24,8) DEFAULT NULL COMMENT \'金额\',\n' +
    '  `drawing_code` varchar(255) DEFAULT NULL COMMENT \'图册号\',\n' +
    '  `memo` varchar(1000) DEFAULT NULL COMMENT \'备注\',\n' +
    '  `dgn_qty1` decimal(24,8) DEFAULT NULL COMMENT \'设计数量1\',\n' +
    '  `dgn_qty2` decimal(24,8) DEFAULT NULL COMMENT \'设计数量2\',\n' +
    '  `deal_qty` decimal(24,8) DEFAULT NULL COMMENT \'签约 - 数量\',\n' +
    '  `deal_tp` decimal(24,8) DEFAULT NULL COMMENT \'签约 - 金额\',\n' +
    '  `sgfh_qty` decimal(24,8) DEFAULT NULL COMMENT \'施工复核 - 数量\',\n' +
    '  `sgfh_tp` decimal(24,8) DEFAULT NULL COMMENT \'施工复核 - 金额\',\n' +
    '  `sjcl_qty` decimal(24,8) DEFAULT NULL COMMENT \'设计错漏 - 数量\',\n' +
    '  `sjcl_tp` decimal(24,8) DEFAULT NULL COMMENT \'设计错漏 - 金额\',\n' +
    '  `qtcl_qty` decimal(24,8) DEFAULT NULL COMMENT \'其他错漏 - 数量\',\n' +
    '  `qtcl_tp` decimal(24,8) DEFAULT NULL COMMENT \'其他错漏 - 金额\',\n' +
    '  `node_type` int(4) unsigned DEFAULT \'0\' COMMENT \'节点类别\',\n' +
    '  `crid` varchar(100) DEFAULT NULL COMMENT \'create_revise_id(uuid)\',\n' +
    '  `is_tp` tinyint(1) unsigned NOT NULL DEFAULT \'0\' COMMENT \'总额计量\',\n' +
    '  `sgfh_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'施工复核-公式\',\n' +
    '  `sjcl_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'设计错漏-公式\',\n' +
    '  `qtcl_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'其他错漏 - 公式\',\n' +
    '  `gxby_status` tinyint(4) DEFAULT \'-1\' COMMENT \'工序报验-状态\',\n' +
    '  `dagl_status` tinyint(4) DEFAULT \'-1\' COMMENT \'档案管理-状态\',\n' +
    '  `dagl_url` varchar(255) DEFAULT \'\' COMMENT \'档案管理-查看链接\',\n' +
    '  `check_calc` tinyint(1) DEFAULT \'0\' COMMENT \'是否检查计算\',\n' +
    '  PRIMARY KEY (`id`),\n' +
    '  KEY `idx_tender_id` (`tender_id`),\n' +
    '  KEY `idx_template_pid` (`ledger_pid`),\n' +
    '  KEY `idx_level` (`level`),\n' +
    '  KEY `idx_full_path` (`tender_id`,`full_path`) USING BTREE\n' +
    ') ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=\'标段--台账分解数据\';';

const posSql = 'CREATE TABLE `zh_pos_?` (\n' +
    '  `id` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'主键\',\n' +
    '  `tid` int(11) NOT NULL COMMENT \'标段id\',\n' +
    '  `lid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'台账id\',\n' +
    '  `name` varchar(1000) CHARACTER SET utf8 DEFAULT NULL COMMENT \'名称\',\n' +
    '  `drawing_code` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT \'图册号\',\n' +
    '  `quantity` decimal(30,8) DEFAULT NULL COMMENT \'数量\',\n' +
    '  `add_stage` int(11) NOT NULL COMMENT \'新增期数\',\n' +
    '  `add_times` int(11) NOT NULL COMMENT \'创建于第几次审核\',\n' +
    '  `add_user` int(11) NOT NULL COMMENT \'创建人id\',\n' +
    '  `sgfh_qty` decimal(24,8) DEFAULT NULL COMMENT \'施工复核 - 数量\',\n' +
    '  `sjcl_qty` decimal(30,8) DEFAULT NULL COMMENT \'设计错漏 - 数量\',\n' +
    '  `qtcl_qty` decimal(30,8) DEFAULT NULL COMMENT \'其他错漏 - 数量\',\n' +
    '  `crid` varchar(100) CHARACTER SET utf8 DEFAULT NULL COMMENT \'create_revise_id(uuid)\',\n' +
    '  `in_time` datetime DEFAULT NULL COMMENT \'新增时间\',\n' +
    '  `porder` int(11) unsigned NOT NULL DEFAULT \'1\',\n' +
    '  `position` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT \'位置\',\n' +
    '  `sgfh_expr` varchar(255) CHARACTER SET ascii NOT NULL DEFAULT \'\' COMMENT \'施工复核-公式\',\n' +
    '  `sjcl_expr` varchar(255) CHARACTER SET ascii NOT NULL DEFAULT \'\' COMMENT \'设计错漏-公式\',\n' +
    '  `qtcl_expr` varchar(255) CHARACTER SET ascii NOT NULL DEFAULT \'\' COMMENT \'其他错漏-公式\',\n' +
    '  `real_qty` decimal(24,8) DEFAULT \'0.00000000\' COMMENT \'实际使用数量\',\n' +
    '  `gxby_status` int(4) DEFAULT \'-1\' COMMENT \'工序报验-状态\',\n' +
    '  `dagl_status` int(4) DEFAULT \'-1\' COMMENT \'档案管理-状态\',\n' +
    '  `dagl_url` varchar(255) CHARACTER SET utf8 DEFAULT \'\' COMMENT \'档案管理-查看链接\',\n' +
    '  PRIMARY KEY (`id`),\n' +
    '  KEY `tid` (`tid`) USING BTREE,\n' +
    '  KEY `tlid` (`tid`,`lid`) USING BTREE\n' +
    ') ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT=\'部位明细\';';
const reviseBillsSql = 'CREATE TABLE `zh_revise_bills_?` (\n' +
    '  `id` varchar(100) NOT NULL COMMENT \'自增id\',\n' +
    '  `code` varchar(50) DEFAULT \'\' COMMENT \'节点编号\',\n' +
    '  `b_code` varchar(50) DEFAULT NULL,\n' +
    '  `name` varchar(255) DEFAULT NULL COMMENT \'名称\',\n' +
    '  `unit` varchar(15) DEFAULT \'\' COMMENT \'单位\',\n' +
    '  `source` varchar(30) DEFAULT \'\' COMMENT \'添加源\',\n' +
    '  `remark` varchar(60) DEFAULT \'\' COMMENT \'备注\',\n' +
    '  `tender_id` int(10) NOT NULL COMMENT \'标段id\',\n' +
    '  `ledger_id` int(10) NOT NULL COMMENT \'节点id\',\n' +
    '  `ledger_pid` int(10) NOT NULL COMMENT \'父节点id\',\n' +
    '  `level` mediumint(3) NOT NULL COMMENT \'层级\',\n' +
    '  `order` mediumint(5) NOT NULL DEFAULT \'0\' COMMENT \'同级排序\',\n' +
    '  `full_path` varchar(100) DEFAULT \'\' COMMENT \'层级定位辅助字段parent.full_path.ledger_id\',\n' +
    '  `is_leaf` tinyint(1) DEFAULT NULL COMMENT \'是否叶子节点，界面显示辅助字段\',\n' +
    '  `quantity` decimal(30,8) DEFAULT NULL COMMENT \'数量\',\n' +
    '  `unit_price` decimal(30,8) DEFAULT NULL COMMENT \'单价\',\n' +
    '  `total_price` decimal(30,8) DEFAULT NULL COMMENT \'金额\',\n' +
    '  `drawing_code` varchar(255) DEFAULT NULL COMMENT \'图册号\',\n' +
    '  `memo` varchar(1000) DEFAULT NULL COMMENT \'备注\',\n' +
    '  `dgn_qty1` decimal(30,8) DEFAULT NULL COMMENT \'设计数量1\',\n' +
    '  `dgn_qty2` decimal(30,8) DEFAULT NULL COMMENT \'设计数量2\',\n' +
    '  `deal_qty` decimal(30,8) DEFAULT NULL COMMENT \'签约 - 数量\',\n' +
    '  `deal_tp` decimal(30,8) DEFAULT NULL COMMENT \'签约 - 金额\',\n' +
    '  `sgfh_qty` decimal(24,8) DEFAULT NULL COMMENT \'施工复核 - 数量\',\n' +
    '  `sgfh_tp` decimal(24,8) DEFAULT NULL COMMENT \'施工复核 - 金额\',\n' +
    '  `sjcl_qty` decimal(30,8) DEFAULT NULL COMMENT \'设计错漏 - 数量\',\n' +
    '  `qtcl_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'其他错漏-公式\',\n' +
    '  `sjcl_tp` decimal(30,8) DEFAULT NULL COMMENT \'设计错漏 - 金额\',\n' +
    '  `qtcl_qty` decimal(30,8) DEFAULT NULL COMMENT \'其他错漏 - 数量\',\n' +
    '  `qtcl_tp` decimal(30,8) DEFAULT NULL COMMENT \'其他错漏 - 金额\',\n' +
    '  `node_type` int(4) unsigned DEFAULT \'0\' COMMENT \'节点类别\',\n' +
    '  `crid` varchar(100) DEFAULT NULL COMMENT \'create_revise_id(uuid)\',\n' +
    '  `is_tp` tinyint(1) unsigned NOT NULL DEFAULT \'0\' COMMENT \'总额计量\',\n' +
    '  `sgfh_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'施工复核-公式\',\n' +
    '  `sjcl_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'设计错漏-公式\',\n' +
    '  `gxby_status` tinyint(4) DEFAULT \'-1\' COMMENT \'工序报验状态\',\n' +
    '  `dagl_status` tinyint(4) DEFAULT \'-1\' COMMENT \'档案管理状态\',\n' +
    '  `dagl_url` varchar(255) DEFAULT \'\' COMMENT \'档案管理-查看链接\',\n' +
    '  `check_calc` tinyint(1) DEFAULT \'0\' COMMENT \'是否检查计算\',\n' +
    '  PRIMARY KEY (`id`),\n' +
    '  KEY `idx_tender_id` (`tender_id`),\n' +
    '  KEY `idx_template_pid` (`ledger_pid`),\n' +
    '  KEY `idx_level` (`level`),\n' +
    '  KEY `idx_full_path` (`full_path`)\n' +
    ') ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=\'标段--台账分解数据\';';
const revisePosSql = 'CREATE TABLE `zh_revise_pos_?` (\n' +
    '  `id` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'主键\',\n' +
    '  `tid` int(11) NOT NULL COMMENT \'标段id\',\n' +
    '  `lid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'台账id\',\n' +
    '  `name` varchar(1000) CHARACTER SET utf8 DEFAULT NULL COMMENT \'名称\',\n' +
    '  `drawing_code` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT \'图册号\',\n' +
    '  `quantity` decimal(30,8) DEFAULT NULL COMMENT \'数量\',\n' +
    '  `add_stage` int(11) NOT NULL COMMENT \'新增期数\',\n' +
    '  `add_times` int(11) NOT NULL COMMENT \'创建于第几次审核\',\n' +
    '  `add_user` int(11) NOT NULL COMMENT \'创建人id\',\n' +
    '  `sgfh_qty` decimal(24,8) DEFAULT NULL COMMENT \'施工复核 - 数量\',\n' +
    '  `sjcl_qty` decimal(30,8) DEFAULT NULL COMMENT \'设计错漏 - 数量\',\n' +
    '  `qtcl_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'其他错漏-公式\',\n' +
    '  `qtcl_qty` decimal(30,8) DEFAULT NULL COMMENT \'其他错漏 - 数量\',\n' +
    '  `crid` varchar(100) CHARACTER SET utf8 DEFAULT NULL COMMENT \'create_revise_id(uuid)\',\n' +
    '  `in_time` datetime DEFAULT NULL COMMENT \'新增时间\',\n' +
    '  `porder` int(11) unsigned NOT NULL DEFAULT \'1\',\n' +
    '  `position` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT \'位置\',\n' +
    '  `sgfh_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'施工复核-公式\',\n' +
    '  `sjcl_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'设计错漏-公式\',\n' +
    '  `gxby_status` tinyint(4) DEFAULT \'-1\' COMMENT \'工序报验状态\',\n' +
    '  `dagl_status` tinyint(4) DEFAULT \'-1\' COMMENT \'档案管理状态\',\n' +
    '  `dagl_url` varchar(255) CHARACTER SET utf8 DEFAULT \'\' COMMENT \'档案管理-查看链接\',\n' +
    '  `real_qty` decimal(24,8) DEFAULT NULL COMMENT \'现场实际数量\',\n' +
    '  PRIMARY KEY (`id`),\n' +
    '  KEY `tid` (`tid`) USING BTREE,\n' +
    '  KEY `lid` (`tid`,`lid`) USING BTREE\n' +
    ') ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT=\'部位明细\';';
const stageBillsSql = 'CREATE TABLE `zh_stage_bills_?` (\n' +
    '  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT \'主键\',\n' +
    '  `tid` int(11) NOT NULL COMMENT \'标段id\',\n' +
    '  `sid` int(11) NOT NULL COMMENT \'期id(stage_id)\',\n' +
    '  `said` int(11) NOT NULL COMMENT \'期审核记录id(stage_audit_id)\',\n' +
    '  `times` int(11) NOT NULL COMMENT \'期审核次数\',\n' +
    '  `order` int(11) NOT NULL COMMENT \'期审核顺序\',\n' +
    '  `contract_qty` decimal(24,8) DEFAULT NULL COMMENT \'合同计量--数量\',\n' +
    '  `contract_tp` decimal(24,8) DEFAULT NULL COMMENT \'合同计量--金额\',\n' +
    '  `qc_qty` decimal(24,8) DEFAULT NULL COMMENT \'数量变更--数量\',\n' +
    '  `qc_tp` decimal(24,8) DEFAULT NULL COMMENT \'数量变更--金额\',\n' +
    '  `postil` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT \'批注\',\n' +
    '  `lid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'对应台账id\',\n' +
    '  `contract_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'合同计量-公式\',\n' +
    '  PRIMARY KEY (`id`),\n' +
    '  KEY `cur` (`tid`,`sid`,`times`,`order`) USING BTREE\n' +
    ') ENGINE=InnoDB AUTO_INCREMENT=62246 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;';
const stageBillsFinalSql = 'CREATE TABLE `zh_stage_bills_final_?` (\n' +
    '  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT \'主键\',\n' +
    '  `tid` int(11) NOT NULL COMMENT \'标段id\',\n' +
    '  `lid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'对应台账id\',\n' +
    '  `sid` int(11) NOT NULL COMMENT \'期id(stage_id)\',\n' +
    '  `sorder` int(11) NOT NULL DEFAULT \'1\' COMMENT \'第几期\',\n' +
    '  `contract_qty` decimal(24,8) DEFAULT NULL COMMENT \'合同计量--数量\',\n' +
    '  `contract_tp` decimal(24,8) DEFAULT NULL COMMENT \'合同计量--金额\',\n' +
    '  `qc_qty` decimal(24,8) DEFAULT NULL COMMENT \'数量变更--数量\',\n' +
    '  `qc_tp` decimal(24,8) DEFAULT NULL COMMENT \'数量变更--金额\',\n' +
    '  `used` tinyint(1) unsigned zerofill NOT NULL COMMENT \'是否使用\',\n' +
    '  `contract_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'合同计量-公式\',\n' +
    '  PRIMARY KEY (`id`)\n' +
    ') ENGINE=InnoDB AUTO_INCREMENT=119279 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT=\'期 - 台账 - 最终数据(截止本期)\';';
const stagePosSql = 'CREATE TABLE `zh_stage_pos_?` (\n' +
    '  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT \'主键\',\n' +
    '  `tid` int(11) NOT NULL COMMENT \'标段id\',\n' +
    '  `lid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'对应台账id（uuid）\',\n' +
    '  `pid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'部位id(项目节，position_id)（uuid）\',\n' +
    '  `sid` int(11) NOT NULL COMMENT \'期id(stage_id)\',\n' +
    '  `said` int(11) NOT NULL COMMENT \'期审核记录id(stage_audit_id)\',\n' +
    '  `times` int(11) NOT NULL COMMENT \'期审核次数\',\n' +
    '  `order` int(11) NOT NULL COMMENT \'期审核顺序\',\n' +
    '  `contract_qty` decimal(24,8) DEFAULT \'0.00000000\',\n' +
    '  `qc_qty` decimal(24,8) DEFAULT \'0.00000000\',\n' +
    '  `postil` varchar(1000) COLLATE utf8_unicode_ci DEFAULT \'\' COMMENT \'批注\',\n' +
    '  `contract_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'合同计量-公式\',\n' +
    '  PRIMARY KEY (`id`),\n' +
    '  KEY `cur` (`sid`) USING BTREE,\n' +
    '  KEY `delete_lid` (`tid`,`lid`) USING BTREE\n' +
    ') ENGINE=InnoDB AUTO_INCREMENT=357234 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;';
const stagePosFinalSql = 'CREATE TABLE `zh_stage_pos_final_?` (\n' +
    '  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT \'主键\',\n' +
    '  `tid` int(11) NOT NULL COMMENT \'标段id\',\n' +
    '  `lid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'对应台账id（uuid）\',\n' +
    '  `pid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'部位id(项目节，position_id)（uuid）\',\n' +
    '  `sid` int(11) NOT NULL COMMENT \'期id(stage_id)\',\n' +
    '  `sorder` int(11) NOT NULL COMMENT \'第几期\',\n' +
    '  `contract_qty` decimal(24,8) DEFAULT NULL,\n' +
    '  `qc_qty` decimal(24,8) DEFAULT NULL,\n' +
    '  `used` tinyint(1) NOT NULL DEFAULT \'0\' COMMENT \'是否使用\',\n' +
    '  `contract_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'合同计量-公式\',\n' +
    '  PRIMARY KEY (`id`),\n' +
    '  KEY `pre` (`tid`,`sorder`) USING BTREE\n' +
    ') ENGINE=InnoDB AUTO_INCREMENT=752293 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;';

const departTable = {
    ledger: { count: 10, table: 'zh_ledger', relaId: 'tender_id', createSql: ledgerSql },
    pos: { count: 20, table: 'zh_pos', relaId: 'tid', createSql: posSql },
    revise_bills: { count: 10, table: 'zh_revise_bills', relaId: 'tender_id', createSql: reviseBillsSql },
    revise_pos: { count: 20, table: 'zh_revise_pos', relaId: 'tid', createSql: revisePosSql },
    stage_bills: { count: 10, table: 'zh_stage_bills', relaId: 'tid', createSql: stageBillsSql },
    stage_pos: { count: 20, table: 'zh_stage_pos', relaId: 'tid', createSql: stagePosSql },
    stage_bills_final: { count: 10, table: 'zh_stage_bills_final', relaId: 'tid', createSql: stageBillsFinalSql },
    stage_pos_final: { count: 20, table: 'zh_stage_pos_final', relaId: 'tid', createSql: stagePosFinalSql },
};

// 2020/1/12各表行数统计
// 表名                      行数               分表数             * 为保持尽量长的时间，不需要调整分表，台账按10分表
// zh_ledger                832,368             10               * 计量单元数据量大约是台账的3倍，按台账的2倍分表
// zh_pos                   2,498,325           20
// zh_revise_bills          294,272             10
// zh_revise_pos            1,009,742           20
// zh_stage_bills           239,720             10               * 期（台账、计量单元）数据，同台账数据保持一致，便于查询
// zh_stage_pos             861,836             20
// zh_stage_bills_final     444,011             10
// zh_stage_pos_final       2,366,449           20
// zh_stage_detail          38,904              -
// zh_stage_change          27,232              -
// 合计                                         120
// 其他：共110个项目，2013个标段（项目最少1个标段，最多303个标段）。

const generateAllCreateSql = function () {
    for (const prop in departTable) {
        const table = departTable[prop];
        const sql = [];
        for (let i = 0; i < table.count; i++) {
            sql.push(table.createSql.replace('?', i+''), '');
        }
        fs.writeFileSync(__dirname + '/' + prop + '.sql', sql.join('\n'));
    }
};
// generateAllCreateSql();

// const tenders = querySql('Select * From zh_tender');
//
const log = [];
const depart = async function (info) {
    for (let i = 0; i < info.count; i++) {
        await querySql('Delete From ' + info.table + '_' + i);
        const sql = 'Insert Into ' + info.table + '_' + i +
            '  Select * From ' + info.table +
            '  Where ' + info.relaId + ' % ' + info.count + ' = ' + i;
        log.push(sql);
        await querySql(sql);
    }
};
const departAll = async function () {
    for (const prop in departTable) {
        await depart(departTable[prop]);
    }
    pool.end();
};
departAll();
fs.writeFileSync(__dirname + '/depart.log', log.join('\n'));