Сервис отчетов на Node.js + Google Charts.

Покуда есть на свете манагеры, тема визуализации данных будет актуальна. Аборигенам - бусы, манагерам - картинки. Если вам прилетела задача нарисовать какую-нибудь диаграмму по мотивам деятельности вашей компании, будьте уверены, что она прилетит еще не раз, поэтому предлагаю сразу приготовить сервис на Node.js c использованием Google Charts. Результат будет выглядеть примерно так:

В процессе приготовления я буду использовать MS Windows. Аналогичный процесс в осях семейства Linux почти не отличается, за исключением некоторых моментов, рассматривать которые в рамках настоящего изложения я не планирую.

Довольно лирики, приступим.
Открываем консоль. Каталог приложения по имени chart я создал заранее.
Установим модули mysql (понятно для чего) и colors (раскрасим вывод консоли).

Проверим подключение к базе данных. У меня установлен denwer с настройками по умолчанию, поэтому подключение от имени root без пароля.

var util = require('util');
var colors = require('colors');

var mysql = require('mysql');
var pool = mysql.createPool({
  host:'127.0.0.1',
  user:'root',
  password:'',
 database:'test_db', 
  insecureAuth: true, 
 debug: true
});

pool.getConnection(function(err, connection) {
    if (err) {
        util.log('pool.getConnection error:'.red + '\n' + err);
        process.exit(1);
    }
  util.log('Connection set successfully'.green);
    connection.release();
  process.exit(0);
});

Разумеется на забываем запустить MySQL и изменить настройки подключения.
В случае успеха наблюдаем следующую картину:

Создадим базу данных и таблицы. Я использую phpMyAdmin.

create database if not exists test_db 
character set utf8 collate utf8_general_ci;
use test_db;

create table if not exists `consumer` (
 `id` mediumint unsigned not null auto_increment, 
 `name` varchar(20) not null, 
 primary key(`id`)
);

create table if not exists `money` ( 
 `date` date not null,
 `consumer_id` mediumint not null,
 `sum` float(9,2) unsigned not null,
 primary key(`date`, `consumer_id`)
);

create table if not exists `user` (
 `id` smallint unsigned not null auto_increment,
 `name` varchar(20) not null,
 `pw` varchar(32) not null,
 primary key(`id`)
);

Добавим тестовые данные, которые будут олицетворять существующие данные вашей организации.

var util = require('util'), 
colors = require('colors'), 
crypto = require('crypto');

var mysql = require('mysql');
var pool = mysql.createPool({
  host:'127.0.0.1',
  user:'root',
  password:'',
 database:'test_db', 
  insecureAuth: true, 
 debug: true
});

// проверяем подключение, в случае ошибки выполнять следующий код не имеет смысла
pool.getConnection(function(err, connection) {
    if (err) {
        util.log('pool.getConnection error:'.red + '\n' + err);
        process.exit(1);
    }
  util.log('Connection set successfully'.green);
    connection.release();
});

// закидываем тестовые данные
pool.getConnection(function(err, connection) {
  if (err) {util.log('pool.getConnection error:'.red + '\n' + err); return;}
 var val = [['microsoft'],['google'],['apple']];
 connection.query('INSERT INTO `consumer` (`name`) VALUES ?', [val], function(err, result) {
  if (err) {util.log('connection.query error:'.red + '\n' + err); return;}
   val = [];
   for (var i=0; i<30; i++) { // рандомные даты, consumer_id и суммы
    val.push(['2013-09-0' + rnd(5,9), rnd(1,3), rnd(5000,10000) + '.' + rnd(1,99)]);
   }
   connection.query('INSERT INTO money VALUES ? ON DUPLICATE KEY UPDATE `sum` = `sum` + VALUES(`sum`)', [val], function(err, result) {
    if (err) {util.log('connection.query error:'.red + '\n' + err); return;}
    val = [['user', crypto.createHash('md5').update('pw').digest('hex')]];
    connection.query('INSERT INTO user (name, pw) VALUES ?', [val], function(err, result) {
     if (err) {util.log('connection.query error:'.red + '\n' + err); return;}
     connection.release();
     process.exit(0);
    } );        
   });
         
  });     
});

function rnd(from, to) {
 return Math.floor(Math.random()*(to-from+1)+from);
}

В результате у нас три таблицы с данными:
- consumer - потребители наших сервисов

- user - пользователи сервиса отчетов

- money - без комментариев :)

Продолжаем разговор. Установим глобально express:
- npm install express -g

Cоздадим каркас приложения:
- express ..\chart

Установим express и jade локально:
- npm install

Cмотрим что получилось. Запускаем приложение:
- node app

В браузере идем по адресу http://localhost:3000/:

Каркас готов. Разберемся как это работает.

Посмотрим из чего состоит файл package.json, который находится в корневом каталоге приложения.

{
  "name": "application-name",
  "version": "0.0.1",
  "private": true,
  "scripts": {
    "start": "node app.js"
  },
  "dependencies": {
    "express": "3.3.8",
    "jade": "*"
  }
}

Нетрудно догадаться что к чему. Поясню только, что "dependencies" - модули express и jade с целью портабельности приложения были установлены локально ранее в процессе выполнения команды npm install в каталог node_modules.

Откроем файл запуска приложения - app.js.

/**
 * Module dependencies.
 */

var express = require('express');
var routes = require('./routes');
var user = require('./routes/user');
var http = require('http');
var path = require('path');

var app = express();

// all environments
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')));

// development only
if ('development' == app.get('env')) {
  app.use(express.errorHandler());
}

app.get('/', routes.index);
app.get('/users', user.list);

http.createServer(app).listen(app.get('port'), function(){
  console.log('Express server listening on port ' + app.get('port'));
});

Получив запрос наш http-сервер отправил его маршрутизатору (app.get('/', routes.index);), который приложение получило с помощью инструкции var routes = require('./routes'); .

Рассмотрим код маршрутизатора - файл index.js в каталоге routes.

/*
 * GET home page.
 */

exports.index = function(req, res){
  res.render('index', { title: 'Express' });
}; 

Маршрутизатор рендерит содержимое файла index.jade из каталога views с помощью движка jade  (app.set('view engine', 'jade');) и отправляет его в ответ на запрос.

Открываем файл index.jade.

extends layout

block content
  h1= title
  p Welcome to #{title}

Контент наследует макет из файла layout.jade. Откроем файл макета.

doctype 5
html
  head
    title= title
    link(rel='stylesheet', href='/stylesheets/style.css')
  body
    block content

Приложение раздает статические файлы из каталога public (app.use(express.static(path.join(__dirname, 'public')));).
Макет цепляет стиль из файла style.css, который находится в каталоге public/stylesheets/.

Теперь, когда механизм работы приложения стал немного яснее, переходим к кодингу.

Начнем с аутентификации. В корневом каталоге приложения создаем файл модуля работы с базой данных MySQL по имени db.js.

var crypto = require('crypto');
var mysql = require('mysql');
var pool = mysql.createPool({
  host:'127.0.0.1',
  user:'root',
  password:'',
 database:'test_db', 
  insecureAuth: true
});

// аутентификация
module.exports.auth = function(user, pw, cb) { 
 pool.getConnection(function(err, connection) {
  if (err) {throw err; return;}   
  connection.query('SELECT * FROM user WHERE name = ? AND pw = ?', [user, crypto.createHash('md5').update(pw).digest('hex')], function(err, data) {   
   if (err) {throw err; return;}   
   connection.release();   
   cb(null /* error */, data.length);   
  });   
 }); 
}

Редактируем код файла запуска приложения app.js.

var express = require('express');
var routes = require('./routes');
var http = require('http');
var path = require('path');

var app = express();

var db = require('./db'); // для аутентификации

// all environments
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());

// аутентификация
var auth = express.basicAuth(function(user, pw, cb) {
 if (!user || !pw) {cb(null, false); return;}
 db.auth(user, pw, cb); 
});

app.use(app.router);
app.use(express.static(path.join(__dirname, 'public')));

// development only
if ('development' == app.get('env')) {
  app.use(express.errorHandler());
}

app.get('/', auth, routes.index);

http.createServer(app).listen(app.get('port'), function(){  
 console.log("Express server listening on port %d in %s mode", app.get('port'), app.settings.env);
});

Запускаем приложение, тестируем.

Пытаемся авторизоваться.

Вспоминаем, что на этапе заполнения таблиц тестовыми данными в таблицу user мы закинули пользователя user с паролем pw.

Займемся контентом.
В каталоге view создадим следующие файлы (по умолчанию присутствуют только index.jade и layout.jade):

По алфавиту:
- about.jade - о себе любимом - единственный контент, доступ к которому не требует авторизации

extends layout

block content 
 h3 Designed by Anatoly Demidovich

- bar.jade - контент диаграммы Bar Chart, наследует от layout.jade

extends layout

block content 
 include ./bar-in

- bar-in.jade - инклуд диаграммы Bar Chart, не наследует от layout.jade, содержит клиентский скрипт визуализации

#bar_div 
 script.
  google.load('visualization', '1', {packages:['corechart']});
  google.setOnLoadCallback(drawTable);
  function drawTable() {
   var data = new google.visualization.DataTable();
   data.addColumn('string', 'Date');   
   var mydata =!{JSON.stringify(mydata1)};
   var tmp = [], tmpd = []; // временные массивы для уникальных значений
   for (var i=0; i<mydata.length; i++) { // ищем уникальных потребителей
    if (tmp.indexOf(mydata[i].name) === -1) {
     tmp.push(mydata[i].name); 
     data.addColumn('number', mydata[i].name);
    }    
   }   
   for (var i=0; i<mydata.length; i++) {
    if (tmpd.indexOf(mydata[i].date) === -1) { // ищем уникальные даты
     data.addRow();
     data.setCell(tmpd.length, 0, new Date(mydata[i].date).toLocaleDateString());       
     tmpd.push(mydata[i].date);
    }
    for (var j=0; j<tmp.length; j++) {
     if (tmp[j] === mydata[i].name) {
      data.setCell(tmpd.length-1, j+1, mydata[i].sum);
      break;
     }
    }
   }  
   var table = new google.visualization.BarChart(document.getElementById('bar_div'));
   table.draw(data);
  }

- column.jade - контент диаграммы Column Chart, наследует от layout.jade

extends layout

block content
 include ./column-in

- column-in.jade - инклуд диаграммы Column Chart, не наследует от layout.jade, содержит клиентский скрипт визуализации

#column_div 
 script.
  google.load('visualization', '1', {'packages':['corechart']});
  google.setOnLoadCallback(drawTable);
  function drawTable() {
   var data = new google.visualization.DataTable();
   data.addColumn('string', 'Name');   
   data.addColumn('number', 'Sum');
   var mydata =!{JSON.stringify(mydata2)}  
   for (var i=0; i<mydata.length; i++) {  
    data.addRow([mydata[i].name, mydata[i].sum]);
   }  
   var table = new google.visualization.ColumnChart(document.getElementById('column_div'));
   table.draw(data);
  }

- index.jade - контент главной страницы приложения, включает в себя все диаграммы приложения, наследует от layout.jade

extends layout

block content
 include ./table-in
 include ./bar-in
 include ./pie-in
 include ./column-in 

- layuot.jade - макет приложения, включает в себя ссылки на внешние библиотеки, файл стиля и меню навигации

doctype 5
html
 head
  title= title
  link(rel='stylesheet', href='stylesheets/style.css')
  script(src='http://code.jquery.com/jquery-1.9.1.js')
  script(src='http://code.jquery.com/ui/1.10.3/jquery-ui.js')  
  script(src='https://www.google.com/jsapi')
 body  
  include ./nav
  hr
  block content
  hr
  a(href='./about') About

- nav.jade - меню навигации приложения, также содержит jQuery Datepicker и клиентский скрипт

a(href='./') All
a(href='./table') Table
a(href='./bar') Bar
a(href='./pie') Pie
a(href='./column') Column
form
 label(for='from') Date from:
 input#from_dt(type='text', name='from')
 label(for='to') Date to:
 input#to_dt(type='text', name='to')
 input#submit(type='submit', value='Submit')

script.
 $(document).ready(function() {
  $("#from_dt").datepicker({ dateFormat: 'mm-dd-yy'});
  $("#to_dt").datepicker({ dateFormat: 'mm-dd-yy'});
 });

- pie.jade -  контент диаграммы Pie Chart, наследует от layout.jade

extends layout

block content
 include ./pie-in

- pie-in.jade - инклуд диаграммы Pie Chart, не наследует от layout.jade, содержит клиентский скрипт визуализации

#pie_div 
 script.
  google.load('visualization', '1', {'packages':['corechart']});
  google.setOnLoadCallback(drawTable);
  function drawTable() {
   var data = new google.visualization.DataTable();
   data.addColumn('string', 'Name');   
   data.addColumn('number', 'Sum');
   var mydata =!{JSON.stringify(mydata2)}  
   for (var i=0; i<mydata.length; i++) {  
    data.addRow([mydata[i].name, mydata[i].sum]);
   }
   var options = {is3D: true};
   var table = new google.visualization.PieChart(document.getElementById('pie_div'));
   table.draw(data, options);
  }

- table.jade -  контент визуализации Table, наследует от layout.jade

extends layout

block content 
 include ./table-in

- table-in.jade - инклуд визуализации Table, не наследует от layout.jade, содержит клиентский скрипт визуализации

#table_div 
 script.
  google.load('visualization', '1', {packages:['table']});
  google.setOnLoadCallback(drawTable);
  function drawTable() {
   var data = new google.visualization.DataTable();
   data.addColumn('date', 'Date');
   data.addColumn('string', 'Name');  
   data.addColumn('number', 'Sum');
   var mydata =!{JSON.stringify(mydata1)}  
   for (var i=0; i<mydata.length; i++) {  
    data.addRow([new Date(mydata[i].date), mydata[i].name, mydata[i].sum]);
   }
   var options = {'showRowNumber': true};
   var table = new google.visualization.Table(document.getElementById('table_div'));
   table.draw(data, options);
  }

Не забываем, что jade использует отступы в два пробела для определения местонахождения HTML-элементов. По этому поводу есть версия, на мой взгляд вполне правдоподобная, что jade придумали "питонисты" (поклонники языка python).

Как вы уже наверняка заметили, в jade-шаблоны данные прилетают в виде JSON:

- var mydata =!{JSON.stringify(mydata1)} , или var mydata =!{JSON.stringify(mydata2)}, 

после чего обрабатываются клиентским скриптом. 

Отправлять эти самые данные в шаблоны мы будем из маршрутизатора.

Открываем файл index.js в каталоге routes, редактируем.

var db = require('../db'), 
conf = require('../conf');

// главная страница - все диаграммы
exports.index = function(req, res){
 var obj = {'title': 'All Charts'};
 db.get(req, conf['table']['query'], function (data1) {
  obj['mydata1'] = data1;  
   db.get(req, conf['pie']['query'], function (data2) {
    obj['mydata2'] = data2;    
    res.render('index', obj);    
   });
 });
};

// отдельная диаграмма
exports.chart = function(req, res){
 var obj = {'title': req.params.page.charAt(0).toUpperCase() + req.params.page.substr(1) +  ' Chart'};
 db.get(req, conf[req.params.page]['query'], function (data) {  
  obj[conf[req.params.page]['data']] = data;
  res.render(req.params.page, obj);
 });
};

exports.about = function(req, res){
 res.render('about', {'title': 'About'});
}

В корневом каталоге приложения создадим файл conf.js, в котором будем хранить настройки приложения, в том числе текст запросов серверу MySQL. Маршрутизатор импортирует эти настройки (conf = require('../conf');) и использует их для отправки запросов, после чего отправляет полученные данные движку jade (res.render('index', obj);).

var q1 = 'SELECT m.date, c.name, m.sum FROM money m JOIN consumer c ON c.id = m.consumer_id WHERE `date` >= DATE(?) AND `date` <= DATE(?) ORDER BY m.date', 
q2 = 'SELECT c.id, c.name, SUM(m.sum) as sum FROM money m JOIN consumer c ON c.id = m.consumer_id WHERE `date` >= DATE(?) AND `date` <= DATE(?) GROUP BY c.id ORDER BY c.id';

module.exports = {
 'table': {'query': q1, 'data': 'mydata1'}, 
 'bar': {'query': q1, 'data': 'mydata1'}, 
 'pie': {'query': q2, 'data': 'mydata2'}, 
 'column': {'query': q2, 'data': 'mydata2'}
};

Редактируем созданный ранее модуль работы с базой данных, функцию get() которого активно использует наш маршрутизатор в процессе получения данных сервера MySQL.

var crypto = require('crypto');
var mysql = require('mysql');
var pool = mysql.createPool({
  host:'127.0.0.1',
  user:'root',
  password:'',
 database:'test_db', 
  insecureAuth: true
});

// аутентификация
module.exports.auth = function(user, pw, cb) { 
 pool.getConnection(function(err, connection) {
  if (err) {throw err; return;}   
  connection.query('SELECT * FROM user WHERE name = ? AND pw = ?', [user, crypto.createHash('md5').update(pw).digest('hex')], function(err, data) {   
   if (err) {throw err; return;}   
   connection.release();   
   cb(null /* error */, data.length);   
  });   
 }); 
}

// запросы
module.exports.get = function getData(req, q, cb) {
 pool.getConnection(function(err, connection) {
  if (err) {throw err; return;}  
  var from = req.query.from ? new Date(req.query.from) : new Date();
  var to = req.query.to ? new Date(req.query.to) : new Date();  
  connection.query(q, [from, to], function(err, data) {   
   if (err) {throw err; return;}   
   connection.release();   
   cb(data);
  });   
 }); 
}

В коде файла запуска приложения app.js растасуем маршруты согласно функциям, экспортируемым маршрутизатором:

app.get('/', auth, routes.index);
app.get('/:page((table|bar|pie|column))', auth, routes.chart);
app.get('/about', routes.about);

В итоге код файла app.js выглядит следующим образом:

var express = require('express');
var routes = require('./routes');
var http = require('http');
var path = require('path');

var app = express();

var db = require('./db'); // для аутентификации

// all environments
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());

// аутентификация
var auth = express.basicAuth(function(user, pw, cb) {
 if (!user || !pw) {cb(null, false); return;}
 db.auth(user, pw, cb); 
});

app.use(app.router);
app.use(express.static(path.join(__dirname, 'public')));

// development only
if ('development' == app.get('env')) {
  app.use(express.errorHandler());
}

app.get('/', auth, routes.index);
app.get('/:page((table|bar|pie|column))', auth, routes.chart);
app.get('/about', routes.about);

http.createServer(app).listen(app.get('port'), function(){  
 console.log("Express server listening on port %d in %s mode", app.get('port'), app.settings.env);
});

Наведем красоту - редактируем код файла style.css, проживающего по адресу public/stylesheets/:

body {  
  font: 14px "Lucida Grande", Helvetica, Arial, sans-serif;
}
form{
 margin: 2px 10px;
}
#from_dt, #to_dt{
 width: 70px; 
 height: 20px; 
 margin: 2px;
}
#submit{ 
 margin: 2px;
 padding: 5px;
 font-weight: bold; 
 font-size: 16px;
 border: none; 
 background-color: #dcdcdc;
}
a {
 min-width: 50px; 
 text-decoration: none;
}
a, .ui-datepicker-title, form{
 display: inline-block;
}
a, .ui-datepicker-title, .ui-datepicker-calendar thead{
  color: #000;  
 margin: 2px;
 padding: 5px;
 text-align: center;
 background-color: #dcdcdc;
 font-weight: bold;
 font-size: 16px; 
}
.ui-datepicker-title:hover, a:hover, .ui-datepicker-calendar thead:hover, #submit:hover{
 color: #fff; 
 background-color: #4169e1;
}
.ui-datepicker-prev, .ui-datepicker-next, #submit{
 cursor: pointer;
}
a, .ui-datepicker-title, .ui-datepicker-calendar thead, #submit{
 transition: all 0.6s ease-in-out; 
 -webkit-transition: all 0.6s ease-in-out;
 -moz-transition:: all 0.6s ease-in-out;
 -ms-transition: all 0.6s ease-in-out;
 -o-transition: all 0.6s ease-in-out; 
 opacity: 0.9;
 -moz-opacity: 0.9;
 -khtml-opacity: 0.9;
 -webkit-opacity: 0.9;
 -ms-filter: progid:DXImageTransform.Microsoft.Alpha(opacity=90);
 filter: alpha(opacity=90);
}

Я решил не использовать стандартную тему виджета jQuery Datepicker, поэтому посвятил ему несколько строчек кода css, приведенного выше.

Сервис отчетов готов к употреблению. Тестируем:
- стартуем приложение:  node app
- посещаем адрес http://localhost:3000/, в случае необходимости авторизуемся - логин: user, пароль: pw
- выбираем даты в диапазоне 05.09.2013 - 09.09.2013 (поле `date` таблицы `money`)

- правда нарядный календарик получился? подтверждаем отправку данных формы на сервер - нажимаем Submit... ta-da :) (см. вступительную картинку)
- выбираем, к примеру, Pie Chart:

- не забываем нажать на кнопочку About :)

В реальных условиях запускаем сервис с помощью forever (я сто раз так делал) или pm2 (ни разу не пробовал, нужно найти время, по описанию - классный модуль).

Манагеры в восторге, Рио де Жанейро, белые парусиновые штаны, креолки и мулатки :).