IM-FormaDesignerの出力する設計書から論理名を使用したVIEWのDDLを生成する。2度と使う機会はなさそう。

forma_logical_view.js

var system_cols = [
    "imfr_sd_insert_id",
    "imfr_sd_application_id",
    "imfr_sd_application_no",
    "imfr_sd_version_no",
    "imfr_sd_create_date",
    "imfr_sd_create_user_cd",
    "imfr_sd_record_date",
    "imfr_sd_record_user_cd",
    "imfr_sd_preserve_flag",
    "imfr_ud_dept_nm"
];

var system_cols_sub = [
    "imfr_sd_insert_id",
    "imfr_sd_row_no"
];


function getCols(base){
    var x=[];
    for(var i=0;i<base.length;i++){
        x.push({
            pn : base[i],
            ln : base[i]
        });
    }
    return x;
}

function debug(msg){
    // WScript.Echo(msg);
}

// 不要か？
function getSheet(book, name){
    var sheetsCount = book.Sheets.Count;
    for(var i=0;i<sheetsCount;i++){
        if(book.Sheets.Item(i+1).Name == name){
            return book.Sheets.Item(i+1);
        }
    }
    return null;
}

function getGridList(workbook){
    var sheet = getSheet(workbook, '詳細(グリッドテーブル)');
    var emptyCount=0;

    var grids = [];
    var currentGrid = null;
    for(var i=1;i<20000;i++){
        if(!sheet.Range("A"+i).Value){
            if(++ emptyCount>30){
                break;
            }
        }else{
            emptyCount=0;
        }

        if(sheet.Range("A"+i).Value == '画面アイテム概要'){
            if(currentGrid){
                grids.push(currentGrid);
            }

            currentGrid = {
                no : sheet.Range("B"+ (i+2)).Value,
                ln : sheet.Range("B"+ (i+3)).Value };
            continue;
        }

        if(sheet.Range("A" + i).Value == '詳細設定' 
            && sheet.Range("B"+i).Value == 'テーブル識別ID'){
                currentGrid.pn = sheet.Range("C"+i).Value;
                continue;
        }
    }

    grids.push(currentGrid);

    return grids;
}

function getGrid(grids, no){
    for(var i=0;i<grids.length;i++){
        if(grids[i].no == no){
            return grids[i];
        }
    }
    return null;
}

function sanitize(x){
    debug('sanitizing ' + x);
    if(x){
        return x.replace(/[.（）()/／\-\+\* 　：]/ig, '_').replace(/㎡/ig,'m2');
    }{
        return x;
    }
}

function viewName(tableName){
    debug('view name ' + tableName);
    return 'tnavi_v_' + sanitize(tableName).replace(/_+/ig, '_').replace(/フォーム/ig, '');
}

function scanItemList(book){
    var sheet = getSheet(book, 'フィールド一覧');
    var abstSheet = getSheet(book, 'フォーム概要');
    var appId = abstSheet.Range("B3").Value;
    var formName = abstSheet.Range("B19").Value;
    var baseTableName = 'imfr_ut_' + appId.toLowerCase();
    var tables = [];
    var mainTable = { 
        nln: appId  + '_' + formName + '_MAIN',
        pn : baseTableName,
        cols : getCols(system_cols) };
    var currentTable;
    var grids = getGridList(book);
    var emptyCount = 0;

    for(var i=2;i<20000;i++){
        if(!sheet.Range("A" + i).Value){
            if(++emptyCount>30){
                break;
            }
        }else{
            emptyCount=0;
        }

        var grid = getGrid(grids, sheet.Range("A"+i).Value);

        if((!!grid) && sheet.Range("C"+i).Value === '-' ){
            if(currentTable){
                tables.push(currentTable);
            }

        debug('table:' + grid.ln + ' pn:' + baseTableName + '_' + grid.pn);

            currentTable = { 
                nln: appId  + '_' + formName + '_' + grid.pn,
                ln : grid.ln,
                pn : baseTableName + '_' + grid.pn,
                cols:getCols(system_cols_sub) 
            };
        }

        debug('  col:' + sheet.Range("D"+ i).Value + ' pn:' + sheet.Range("C"+ i).Value);
        if(!sheet.Range("C"+ i).Value){
            continue;
        }

        if(sheet.Range("I"+ i).Value !== true){
            debug('### not db item ###');
            continue;
        }
        

        var col = {
            ln : sheet.Range("D"+ i).Value,
            pn : 'imfr_ud_' + sheet.Range("C"+ i).Value
        }

        if(!!grid){
            currentTable.cols.push(col);
        }else{
            mainTable.cols.push(col);
        }
    }

    if(currentTable){
        tables.push(currentTable);
    }

    return {
        main : mainTable,
        sub : tables
    };
}

function sqlify(table){
    var cols = [];
    for(var i=0;i<table.cols.length;i++){
        cols.push(table.cols[i].pn.toLowerCase() + ' as ' + sanitize(table.cols[i].ln));
    }
    var tableName = viewName(table.nln);
    return 'drop view ' + tableName + ';\n'
         + 'create view ' + tableName + ' as \n'
         + 'select ' + cols.join('\n  ,') + '\nfrom ' + table.pn + ';\n';
}

function writeDDL(table, dir){
    debug('writing ddl ... ' + table.nln);
    var path = dir + '\\' + viewName(table.nln) + '.sql';
    var fs = new ActiveXObject('Scripting.FileSystemObject');
    var ts = fs.CreateTextFile(path);
    ts.Write(sqlify(table));
    ts.Close();
}

function main(){

    var dir = WScript.Arguments(0).substr(0, WScript.Arguments(0).lastIndexOf('\\'));
    var ex = new ActiveXObject('Excel.Application');
    ex.visible = true;
    ex.Workbooks.Open(WScript.Arguments(0));
    var book = ex.ActiveWorkbook;

    var tables = scanItemList(book);

    writeDDL(tables.main, dir);
    debug('outputing main table ' + tables.main.nln );
    for(var i=0;i<tables.sub.length;i++){
        debug('outputing sub table ' + tables.sub[i].nln);
        writeDDL(tables.sub[i], dir);
    }
    ex.Quit();
    WScript.Echo('Done');
}

main();