1 /*jshint node:true, indent:2, curly:false, eqeqeq:true, immed:true, latedef:true, newcap:true, noarg:true,
2 regexp:true, undef:true, strict:true, trailing:true, white:true */
3 /*global X:true, XT:true */
9 var data = require("./data");
10 var queryForData = function (session, query, callback) {
12 var userId = session.passport.user.username,
13 adminUser = X.options.databaseServer.user, // execute this query as admin
14 userQueryPayload = '{"nameSpace":"SYS","type":"User","id":"%@","username":"%@"}'
15 .f(userId, adminUser),
16 userQuery = "select xt.get('%@')".f(userQueryPayload),
17 queryOptions = XT.dataSource.getAdminCredentials(session.passport.user.organization);
19 // first make sure that the user has permissions to export to CSV
20 // (can't trust the client)
21 XT.dataSource.query(userQuery, queryOptions, function (err, res) {
23 if (err || !res || res.rowCount < 1) {
24 callback({isError: true, message: "Error verifying user permissions"});
28 retrievedRecord = JSON.parse(res.rows[0].get);
29 if (retrievedRecord.data.disableExport) {
31 callback({isError: true, message: "Stop trying to hack into our database"});
35 query.printFormat = true;
36 data.queryDatabase("get", query, session, callback);
40 // https://localtest.com/export?details={"requestType":"fetch","query":{"recordType":"XM.Locale"}}
43 Recurses through json object to find all keys and subkeys
45 var getAllJsonKeys = function (json, prefix, keys, exclude) {
46 var key, newPrefix, suffix;
48 if (typeof json === 'object') {
50 if (json.hasOwnProperty(key)) {
51 if (json && json.length) {
52 // don't want to pass the key if it's an array
55 // don't add a dot unless there's something to add a dot to
58 newPrefix = prefix + '.' + key;
60 getAllJsonKeys(json[key], newPrefix, keys, exclude);
65 // add this key unless it's already added or unless it's meant to be excluded
67 if (suffix.indexOf('.') >= 0) {
68 suffix = suffix.substring(suffix.lastIndexOf('.') + 1);
70 if (keys.indexOf(prefix) < 0 && exclude.indexOf(suffix) < 0) {
78 Translates a string of a json array into a string of CSV.
79 Flattens nested objects. Does not assume that the first result has all the keys
81 var jsonToCsv = function (results) {
84 // Each key (toplevel or otherwise) will be a column.
85 // Note that we could save some computational power if we could assume that the
86 // first record has all the keys, but it might not, espectially the subkeys
87 keys = getAllJsonKeys(results, '', [], ['id', 'type', 'dataState']),
94 // print the column headers
96 if (keys.hasOwnProperty(j)) {
97 csv += '"%@",'.f(keys[j]);
103 for (i = 0; i < results.length; i++) {
106 if (keys.hasOwnProperty(j)) {
110 if (key.indexOf('.') >= 0) {
111 // recurse down into values of objects for nested values
112 recursingValue = row;
113 while (recursingValue && key.indexOf('.') >= 0) {
114 recursingValue = recursingValue[key.substring(0, key.indexOf('.'))];
115 key = key.substring(key.indexOf('.') + 1);
117 value = recursingValue ? recursingValue[key] : "";
120 // escape double quotes: " becomes ""
121 if (typeof value === 'string' && value.indexOf("\"") >= 0) {
122 value.replace(/\"/g, /\"\"/);
126 } else if (typeof value === 'number' || !isNaN(value)) {
127 // don't put numbers in quotes
128 csv += '%@,'.f(value);
130 csv += '"%@",'.f(value);
140 // export is a reserved word
141 exports.exxport = function (req, res) {
142 var requestDetails = JSON.parse(req.query.details),
143 contentType = 'text/csv',
146 queryForData(req.session, requestDetails, function (result) {
147 if (result.isError) {
154 number = requestDetails.query &&
155 requestDetails.query.details &&
156 requestDetails.query.details.id,
157 attr = requestDetails.query &&
158 requestDetails.query.details &&
159 requestDetails.query.details.attr
162 type = requestDetails.type;
163 filename = type.replace("ListItem", "Export") +
164 (attr && number ? "-" + number : "") +
165 (attr ? "-" + attr : "")
169 // "export" will have to do.
173 /* export requests have 2 flavors: export a list of records (data.data)
174 or export a list of children of the current record ([0][attr]) */
176 resultAsCsv = jsonToCsv(result.data.data[0][attr]);
178 resultAsCsv = jsonToCsv(result.data.data);
180 res.attachment(filename + ".csv");
182 resultAsCsv = jsonToCsv(error);
184 res.send(resultAsCsv);
189 exports.queryForData = queryForData;