Node.js Report Server
這是一個下午想到的，簡單的Node.js Report Server
目的是讓懂SQL又不太懂後端程式的人可以利用這個Report Server來產出一些基本的報表
當然，他要會使用ajax接json的資料，這邊的報表只有吐json出來而已
概念是：
Step 1. user簡單定義report config，將預執行的report sql配置於config file中，並可以動態配置where子句來作查詢
Step 2. 將report config放在指定資料夾($REPORT_SERVER_HOME/report)內
Step 3. 然後就可以透過REST GET的方式取得該report的json output
curl -sS http://yout_ip:your_port/report/rest/simple.rpt?MEMBER_ID=0066f7b4&USER_ID=xxxx@oooo.com.tw -X GET
/**
PS. 附加提供整體report的查看，如果要看有哪些configured report，則可以透過下面REST GET呼叫
curl -sS http://yout_ip:your_port/report/restdoc -X GET
實作部分，專案資料結構：
$
|____routes
| |____index.js.......................router管理
|____views
| |____(Skip)
|____report
| |____simple.rpt....................report config file
|____lib
| |____db-manager.js.............資料庫聯結程式，將來可以修改為連接不同資料庫
| |____db-config.js.................資料庫設定檔案，目前為mysql
|____public
| |____(Skip)
|____node_modules
| |____(Skip)
|____app.js............................server啟動點
|____package.json
先來看config file的配置，透過指定主sql (select...)，並配合事先拆解好的conditions(field=將來要跟url參數對應的key，condition=欲附加到sql子句的sub sql string)，其中conditions可以設定多組condition
$ vi report/simple.rpt
{
"sql" : "select * from member_info where 1=1 ",
"conditions": [
{"field":"MEMBER_ID", "condition":"and member_id = ?"},
{"field":"USER_ID", "condition":"and user_id = ?"}
]
}
接下來看app.js這支主要啟動檔案，裡面僅比express gen出來的code多加了兩個router(紅色部分)
$ vi app.js
/**
* Module dependencies.
*/
var express = require('express')
, routes = require('./routes')
, user = require('./routes/user')
, http = require('http')
, path = require('path')
, dbm = require('./lib/db-manager');
var app = express();
app.configure(function(){
app.set('port', process.env.PORT || 3000);
app.set('views', __dirname + '/views');
app.set('view engine', 'jade');
app.use(express.favicon());
app.use(express.logger('dev'));
app.use(express.bodyParser());
app.use(express.methodOverride());
app.use(app.router);
app.use(express.static(path.join(__dirname, 'public')));
});
app.configure('development', function(){
app.use(express.errorHandler());
});
app.get('/report/rest/:rpt', routes.getRestFromConfig);
app.get('/report/restdoc', routes.getRestDoc);
http.createServer(app).listen(app.get('port'), function(){
console.log("Express server listening on port " + app.get('port'));
});
接下來看routes/index.js，這支主要負責接收app.js pass過來的routing，然後呼叫report主要查詢模組作查詢動作
$ vi routes/index.js
var dbm = require('../lib/db-manager')
, url = require('url');
/*
* GET home page.
*/
exports.index = function(req, res){
res.render('index', { title: 'Express' });
};
/**
* module getRestFromConfig
*/
exports.getRestFromConfig = function(req, res){
console.log('Do report of : %s', req.params.rpt);
var url_parts = url.parse(req.url, true);
dbm.generateReport(req.params.rpt, url_parts.query, function(err, rows, fiels){
res.writeHead(200,{'Content-Type': 'application/json'} );
res.end(JSON.stringify(rows));
});
}
/**
* module getRestDoc
*/
exports.getRestDoc = function(req, res){
dbm.generateReportDoc(function(err, files){
res.writeHead(200,{'Content-Type': 'application/json'} );
res.end(JSON.stringify(files));
});
}
接下來看主要report的產生模組，這邊主要是把config file抓出來，然後直接組sql語句與condition，最後作查詢並回覆查詢結果
$ vi lib/db-manager.js
var config = require('./db-config')
, db = config.db
, fs = require('fs')
, CONFIG_PATH = '/root/project/report/report/';
/**
* module generateReportDoc
*/
exports.generateReportDoc = function(callback) {
//1. list files in CONFIG_PATH
var files = fs.readdirSync(CONFIG_PATH);
//2. read the configure file
var filesVo = new Array();
for ( i in files ) {
var confStr = fs.readFileSync(CONFIG_PATH + files[i], "utf8");
var vo = JSON.parse(confStr);
vo.config_file = files[i];
filesVo.push(vo);
}
//3. show json
var err = null;
callback(err, filesVo);
}
/**
* module generateReport
*/
exports.generateReport = function(rpt, params, callback) {
/* Get report configure file */
var fileName = CONFIG_PATH + rpt;
var confStr = fs.readFileSync(fileName, "utf8");
console.log('[Report Config]\n%s', confStr);
/* Combine the report config to sql */
var conf = JSON.parse(confStr);
var sql = conf.sql;
var cond = new Array();
if(conf.conditions) {
conf.conditions.forEach(function(v){
console.log('[info]field=%s, condition=%s', v.field, v.condition);
console.log('[info]got field value:', params[v.field]);
if(params[v.field]){
console.log('[info]find field mapping, add to sql....');
sql += (' ' + v.condition);
cond.push(params[v.field]);
}
});
}
/* Send to DB query */
console.log('[SQL] %s', sql);
console.log('[COND]%s', cond);
db.query(sql, cond, function(err, rows, fiels) {
if(err) return next(err);
callback(err, rows, rows);
});
}
透過建制上述程式碼，就可以有一個json output的report server了，接下來只要專心地處理前端頁面的變化就可以了噢∼