3 var _ = require('lodash');
4 var util = require('util');
5 var sequelize = require('../../models').sequelize;
6 var CustomReport = require('../../models').CustomReport;
7 var ReportField = require('../../models').ReportField;
8 var ExtractedReport = require('../../models').ExtractedReport;
9 var extractUtils = require('../../utils/report_extraction');
11 // Get list of custom reports
12 exports.index = function(req, res, next) {
14 var attributes = ['name', 'description', 'parent'];
15 var per_page = req.query.per_page ? parseInt(req.query.per_page, 10) : 100;
16 var page = req.query.page ? parseInt(req.query.page, 10) : 0;
21 offset: page * per_page
24 _.forIn(req.query, function(value, key) {
30 query.order = util.format('%s %s', req.query.sort_by, req.query.sort_order || 'ASC') || null;
36 attributes.forEach(function(attribute) {
39 $like: '%' + value + '%'
42 query.where.$or.push(tmp);
47 $or: value.split(/[\s,]+/)
51 var params = JSON.parse(value);
52 _.forOwn(params, function(pValue, pKey) {
53 query.where[pKey] = pValue;
60 query.where[key].$like = '%' + value + '%';
65 .findAndCountAll(query)
66 .then(function(result) {
67 var total_pages = Math.ceil(result.count / per_page);
68 var next_page = total_pages > (query.offset + 1) ? util.format('%s://%s%s?page=%d', req.protocol, req.headers.host, req.baseUrl, page + 1) : null;
69 var previous_page = page > 0 ? util.format('%s://%s%s?page=%d', req.protocol, req.headers.host, req.baseUrl, page - 1) : null;
71 res.status(200).send({
75 previous_page: previous_page,
76 total_pages: total_pages
80 .catch(function(err) {
81 res.status(500).send({
82 error: 'Something blew up!'
87 exports.preview = function(req, res) {
89 .findById(req.params.id, {
94 .then(function(report) {
95 console.log('found report');
97 return res.sendStatus(404);
99 var query = buildReport(report.dataValues, req.query);
100 // console.log(query);
101 var queries = [sequelize.query(query, {
102 type: sequelize.QueryTypes.SELECT
104 if (req.query.uiPagination) {
105 var countQuery = buildReport(report.dataValues, req.query, true);
106 queries.push(sequelize.query(countQuery, {
107 type: sequelize.QueryTypes.SELECT
113 .spread(function(results, totalItems) {
114 console.log(results.length);
115 console.log(totalItems);
116 res.status(200).send({
118 count: totalItems ? totalItems[0] : null
121 .catch(function(err) {
123 return handleError(res, err);
127 exports.extractPdf = function(req, res) {
128 // console.log('extractPdf api function');
129 docExtract(req, res, 'PDF')
132 exports.extractCsv = function(req, res) {
133 // console.log('extractCsv api function');
134 docExtract(req, res, 'CSV');
137 function docExtract(req, res, typeFunction) {
138 // console.log('common docExtract api function');
139 if (!req.body.startDate || !req.body.endDate) {
140 return res.status(500).send({
141 message: 'MESSAGE_START_OR_END_DATE_MISSING'
145 .findById(req.params.id, {
150 .then(function(report) {
152 return res.sendStatus(404);
154 // console.log('found the report, creating the new DB row for extracted reports..');
155 //CREATE A DB ROW IN A TABLE WITH THE REPORT INFO, QUERY DATES, AND THE STATUS 'ELABORATING'
156 return [report, ExtractedReport.create({
158 startDate: req.body.startDate,
159 endDate: req.body.endDate,
163 .spread(function(report, extractedReport) {
164 // console.log('created new extracted reports db entry, calling external extraction function: ' + typeFunction);
165 extractUtils['extract' + typeFunction](report, req.body.startDate, req.body.endDate, extractedReport.id);
167 .catch(function(err) {
169 return handleError(res, err);
171 return res.sendStatus(200);
174 // Get a single report
175 exports.show = function(req, res) {
177 .findById(req.params.id, {
182 .then(function(report) {
184 return res.sendStatus(404);
186 return res.send(report);
188 .catch(function(err) {
189 return handleError(res, err);
193 // Copy a custom report
194 exports.copy = function(req, res) {
196 .findById(req.params.id, {
201 .then(function(report) {
203 return res.sendStatus(404);
205 var newReport = report.dataValues;
207 delete newReport.createdAt;
208 delete newReport.updatedAt;
209 newReport.parent = req.body.parent;
210 newReport.Fields = _.pluck(newReport.Fields, 'dataValues');
211 _.forEach(newReport.Fields, function(elem) {
213 delete elem.CustomReportId;
214 delete elem.createdAt;
215 delete elem.updatedAt;
218 return CustomReport.create(newReport, {
225 return res.sendStatus(201);
227 .catch(function(err) {
229 return handleError(res, err);
234 exports.import = function(req, res) {
241 .then(function(report) {
242 return res.status(201).send(report);
244 .catch(function(err) {
245 return handleError(res, err);
249 function buildReport(report, query, count) {
253 _.forEach(report.Fields, function(elem) {
256 switch (elem.function) {
257 case 'COUNT DISTINCT':
258 field = util.format('COUNT(DISTINCT %s)', elem.field);
260 case 'GROUP_CONCAT ASC':
261 field = util.format('GROUP_CONCAT(%s ORDER BY %s ASC)', elem.field, elem.field);
263 case 'GROUP_CONCAT DESC':
264 field = util.format('GROUP_CONCAT(%s ORDER BY %s DESC)', elem.field, elem.field);
267 field = util.format('%s(%s)', elem.function, elem.field);
272 fields.push(util.format('%s AS \'%s\'', field, elem.alias));
274 groupBy.push(elem.field);
277 orderBy.push(util.format('%s %s', elem.field, elem.orderBy));
280 var conditions = getConditions(JSON.parse(report.conditions).group);
281 var resultQuery = 'SELECT';
282 // query += fields.length ? ' ' + fields.join(',') : ' *';//right if you want to select all fields also
283 resultQuery += ' ' + fields.join(','); //generate a wrong query if there isn' t any field
285 resultQuery += ',COUNT(*)';
287 resultQuery += ' FROM ' + report.table;
288 resultQuery += conditions !== '()' ? ' WHERE ' + conditions : '';
292 _.forOwn(query, function(value, key) {
296 limit = parseInt(query.perPage, 10);
297 offset = parseInt(value, 10) * limit;
301 limit = parseInt(value, 10);
304 resultQuery += ' AND createdAt >= \'' + value + '\'';
307 resultQuery += ' AND createdAt <= \'' + value + '\'';
312 var column = _.find(report.Fields, {
316 resultQuery += ' AND ' + column + ' LIKE \'%' + value + '%\'';
318 resultQuery += ' AND ' + key + ' LIKE \'%' + value + '%\'';
323 resultQuery += groupBy.length ? ' GROUP BY ' + groupBy.join(',') : '';
324 resultQuery += orderBy.length ? ' ORDER BY ' + orderBy.join(',') : '';
325 resultQuery += limit && !count ? ' LIMIT ' + limit : '';
326 resultQuery += offset && !count ? ' OFFSET ' + offset : '';
330 function getConditions(group) {
331 if (!group) return "";
332 for (var str = "(", i = 0; i < group.rules.length; i++) {
333 i > 0 && (str += " " + group.operator + " ");
334 str += group.rules[i].group ?
335 getConditions(group.rules[i].group) :
336 group.rules[i].field + " " + group.rules[i].condition + " '" + (group.rules[i].condition === 'LIKE' ? "%" + group.rules[i].value + "%" : group.rules[i].value) + "'";
342 // Creates a new report in the DB.
343 exports.create = function(req, res) {
346 .then(function(report) {
347 return res.status(201).send(report);
349 .catch(function(err) {
350 return handleError(res, err);
354 // Updates an existing report in the DB.
355 exports.update = function(req, res) {
359 ReportField.destroy({
361 CustomReportId: req.params.id
365 return ReportField.bulkCreate(req.body.Fields);
368 delete req.body.Fields;
369 return CustomReport.update(req.body, {
376 return res.sendStatus(200);
378 .catch(function(err) {
380 return handleError(res, err);
384 // Deletes a report from the DB.
385 exports.destroy = function(req, res) {
387 .findById(req.params.id)
388 .then(function(report) {
390 return res.sendStatus(404);
394 return res.sendStatus(204);
396 .catch(function(err) {
397 return handleError(res, err);
400 .catch(function(err) {
401 return handleError(res, err);
405 exports.bulkDestroy = function(req, res) {
411 individualHooks: true
414 return res.sendStatus(204);
416 .catch(function(err) {
417 return handleError(res, err);
421 function handleError(res, err) {
422 return res.status(500).send(err);