1 /**
  2  * @fileOverview
  3  * @author <a href="https://www.labkey.org">LabKey</a> (<a href="mailto:info@labkey.com">info@labkey.com</a>)
  4  * @license Copyright (c) 2014-2018 LabKey Corporation
  5  * <p/>
  6  * Licensed under the Apache License, Version 2.0 (the "License");
  7  * you may not use this file except in compliance with the License.
  8  * You may obtain a copy of the License at
  9  * <p/>
 10  * http://www.apache.org/licenses/LICENSE-2.0
 11  * <p/>
 12  * Unless required by applicable law or agreed to in writing, software
 13  * distributed under the License is distributed on an "AS IS" BASIS,
 14  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 15  * See the License for the specific language governing permissions and
 16  * limitations under the License.
 17  * <p/>
 18  */
 19 LABKEY.Query = new function(impl, $) {
 20 
 21     // Insert a hidden html FORM into to page, put the JSON into it, and submit it - the server's response
 22     // will make the browser pop up a dialog
 23     function submitForm(url, formData) {
 24         if (!formData['X-LABKEY-CSRF'])
 25             formData['X-LABKEY-CSRF'] = LABKEY.CSRF;
 26 
 27         var formId = LABKEY.Utils.generateUUID();
 28 
 29         var html = [];
 30         html.push('<f'); html.push('orm method="POST" id="' + formId + '"action="' + url + '">');   // avoid form tag, it causes skipfish false positive
 31         for (var name in formData)
 32         {
 33             if (!formData.hasOwnProperty(name))
 34                 continue;
 35 
 36             var value = formData[name];
 37             if (value === undefined)
 38                 continue;
 39 
 40             html.push( '<input type="hidden"' +
 41                     ' name="' + LABKEY.Utils.encodeHtml(name) + '"' +
 42                     ' value="' + LABKEY.Utils.encodeHtml(value) + '" />');
 43         }
 44         html.push("</form>");
 45 
 46         $('body').append(html.join(''));
 47         $('form#' + formId).submit();
 48     }
 49 
 50     /**
 51      * Documentation specified in core/Query.js -- search for "@name exportSql"
 52      */
 53     impl.exportSql = function(config) {
 54 
 55         var url = LABKEY.ActionURL.buildURL("query", "exportSql", config.containerPath);
 56         var formData = {
 57             sql: config.sql,
 58             schemaName: config.schemaName,
 59             format: config.format,
 60             containerFilter: config.containerFilter
 61         };
 62 
 63         submitForm(url, formData);
 64     };
 65 
 66     /**
 67      * @private Not yet official API
 68      * Export a set of tables
 69      * @param config An object which contains the following:
 70      * @param {String} config.schemas An object with the following structure:
 71      * <pre>
 72      * {
 73      *    schemas: {
 74      *
 75      *      // export the named queries from schema "A" using the default view or the named view
 76      *      "A": [{
 77      *          queryName: "a"
 78      *          filters: [ LABKEY.Filters.create("Name", "bob", LABKEY.Filter.Types.NEQ) ],
 79      *          sort: "Name"
 80      *      },{
 81      *          queryName: "b",
 82      *          viewName: "b-view"
 83      *      }]
 84      *
 85      *    }
 86      * }
 87      * </pre>
 88      * @param {String} [config.headerType] Column header type
 89      *
 90      */
 91     impl.exportTables = function(config) {
 92 
 93         var formData = {};
 94 
 95         if (config.headerType)
 96             formData.headerType = config.headerType;
 97 
 98         // Create a copy of the schema config that we can mutate
 99         var schemas = LABKEY.Utils.merge({}, config.schemas);
100         for (var schemaName in schemas)
101         {
102             if (!schemas.hasOwnProperty(schemaName))
103                 continue;
104 
105             var queryList = schemas[schemaName];
106             for (var i = 0; i < queryList.length; i++)
107             {
108                 var querySettings = queryList[i];
109                 var o = LABKEY.Utils.merge({}, querySettings);
110 
111                 delete o.filter;
112                 delete o.filterArray;
113                 delete o.sort;
114 
115                 // Turn the filters array into a filters map similar to LABKEY.QueryWebPart
116                 o.filters = LABKEY.Filter.appendFilterParams(null, querySettings.filters || querySettings.filterArray);
117 
118                 if (querySettings.sort)
119                     o.filters["query.sort"] = querySettings.sort;
120 
121                 queryList[i] = o;
122             }
123         }
124 
125         formData.schemas = JSON.stringify(schemas);
126 
127         var url = LABKEY.ActionURL.buildURL("query", "exportTables.view");
128         submitForm(url, formData);
129     };
130 
131     function loadingSelect(select) {
132         select.prop('disabled', true);
133         select.empty().append($('<option>', {text: 'Loading...'}));
134     }
135 
136     function populateSelect(select, options, valueProperty, textProperty, initialValue, isRequired, includeBlankOption) {
137         select.empty();
138 
139         // if we have duplicate text options, fall back to displaying the value
140         var textOptions = {}, duplicates = {};
141         $.each(options, function(i, option) {
142             var textValue = option[textProperty];
143             if (textOptions[textValue] === undefined)
144                 textOptions[textValue] = true;
145             else {
146                 option[textProperty] = option[valueProperty];
147                 duplicates[textValue] = true;
148             }
149         });
150 
151         var validInitialValue = false;
152         $.each(options, function (i, option) {
153             var value = valueProperty ? option[valueProperty] : option;
154             var text = textProperty ? option[textProperty] : option;
155             if (duplicates[text] !== undefined)
156                 text = value;
157             var selected = initialValue != undefined && value === initialValue;
158             if (selected)
159                 validInitialValue = true;
160             select.append($('<option>', { value: value,  text: text,  selected: selected}));
161         });
162 
163         if (includeBlankOption !== false) {
164             var elem = '<option';
165             if (isRequired)
166                 elem += ' hidden';
167             if (!validInitialValue)
168                 elem += ' selected';
169             elem += '></option>';
170             select.prepend($(elem));
171         }
172 
173         select.prop('disabled', false);
174         select.on('change', function(){
175             if (initialValue !== select.val())
176                 LABKEY.setDirty(true);
177         });
178     }
179 
180     function sortObjectArrayByTitle(a, b){
181         var aTitle = a.title ? a.title : a.caption;
182         var bTitle = b.title ? b.title : b.caption;
183         return aTitle.localeCompare(bTitle);
184     }
185 
186     var SCHEMA_QUERIES_CACHE = {}; // cache of queries by schema
187     function loadQueries(schemaSelect, querySelect, selectedSchema, initialValue, isRequired, includeBlankOption) {
188         schemaSelect.prop('disabled', true);
189         loadingSelect(querySelect);
190 
191         if (SCHEMA_QUERIES_CACHE[selectedSchema]) {
192             populateSelect(querySelect, SCHEMA_QUERIES_CACHE[selectedSchema], 'name', 'title', initialValue, isRequired, includeBlankOption);
193             schemaSelect.prop('disabled', false);
194         }
195         else {
196             LABKEY.Query.getQueries({
197                 schemaName: selectedSchema,
198                 includeColumns: false,
199                 success: function(data) {
200                     // add the sorted set of queries for this schema to the cache
201                     SCHEMA_QUERIES_CACHE[selectedSchema] = data.queries.sort(sortObjectArrayByTitle);
202 
203                     populateSelect(querySelect, SCHEMA_QUERIES_CACHE[selectedSchema], 'name', 'title', initialValue, isRequired, includeBlankOption);
204                     schemaSelect.prop('disabled', false);
205 
206                     // if there is a selected query, fire the change event
207                     if (querySelect.val()) {
208                         querySelect.trigger('change');
209                     }
210                 }
211             });
212         }
213     }
214 
215     var QUERY_COLUMNS_CACHE = {}; // cache of columns by schema|query|view
216     function loadQueryColumns(select, schemaName, queryName, viewName, filterFn, initValue, isRequired, includeBlankOption, sortFn) {
217         loadingSelect(select);
218 
219         if (viewName === undefined || viewName === null)
220             viewName = ""; //'default' view has an empty string as its name
221 
222         var queryKey = schemaName + '|' + queryName + "|" + viewName;
223         if (LABKEY.Utils.isArray(QUERY_COLUMNS_CACHE[queryKey])) {
224             populateColumnsWithFilterFn(select, QUERY_COLUMNS_CACHE[queryKey], filterFn, initValue, isRequired, includeBlankOption, sortFn);
225             LABKEY.Utils.signalWebDriverTest("queryColumnsLoaded"); // used for test
226         }
227         else if (QUERY_COLUMNS_CACHE[queryKey] === 'loading') {
228             setTimeout(loadQueryColumns, 500, select, schemaName, queryName, viewName, filterFn, initValue, isRequired, includeBlankOption, sortFn);
229         }
230         else {
231             QUERY_COLUMNS_CACHE[queryKey] = 'loading';
232             LABKEY.Query.getQueryDetails({
233                 schemaName: schemaName,
234                 queryName: queryName,
235                 viewName: "*",
236                 success: function(data) {
237                     var queryView = null;
238                     $.each(data.views, function(i, view) {
239                         if (view['name'] === viewName) {
240                             queryView = view;
241                             return false;
242                         }
243                     });
244 
245                     QUERY_COLUMNS_CACHE[queryKey] = [];
246                     if (queryView) {
247                         QUERY_COLUMNS_CACHE[queryKey] = queryView.fields.sort(sortObjectArrayByTitle);
248                     }
249 
250                     populateColumnsWithFilterFn(select, QUERY_COLUMNS_CACHE[queryKey], filterFn, initValue, isRequired, includeBlankOption, sortFn);
251                     LABKEY.Utils.signalWebDriverTest("queryColumnsLoaded"); // used for test
252                 }
253             });
254         }
255     }
256 
257     function populateColumnsWithFilterFn(select, origFields, filterFn, initValue, isRequired, includeBlankOption, sortFn) {
258         var fields = [];
259         $.each(origFields, function(i, field) {
260             var includeField = true;
261 
262             // allow for a filter function to be called for each field
263             if (filterFn && LABKEY.Utils.isFunction(filterFn)) {
264                 includeField = filterFn.call(this, field);
265             }
266 
267             // issue 34203: if the field doesn't have a caption, don't include it
268             if (field.caption == null || field.caption ==='' || field.caption === ' ') {
269                 includeField = false;
270             }
271 
272             if (includeField) {
273                 fields.push($.extend({}, field));
274             }
275         });
276 
277         if (fields.length > 0) {
278             // allow for a sort function to be called to order fields
279             if (sortFn && LABKEY.Utils.isFunction(sortFn)) {
280                 fields.sort(sortFn);
281             }
282             populateSelect(select, fields, 'name', 'caption', initValue, isRequired, includeBlankOption);
283         }
284         else {
285             select.empty().append($('<option>', {text: 'No columns available'}));
286         }
287     }
288 
289     var QUERY_VIEWS_CACHE = {}; // cache of columns by schema|query
290     function loadQueryViews(schemaSelect, querySelect, queryViewselect, initValue) {
291         var schemaName = schemaSelect.val(), queryName = querySelect.val();
292         if (!schemaName || !queryName)
293             return;
294 
295         schemaSelect.prop('disabled', true);
296         querySelect.prop('disabled', true);
297 
298         loadingSelect(queryViewselect);
299 
300         var queryKey = schemaName + '|' + queryName;
301         if (LABKEY.Utils.isArray(QUERY_VIEWS_CACHE[queryKey])) {
302             populateViews(queryViewselect, QUERY_VIEWS_CACHE[queryKey], initValue);
303             schemaSelect.prop('disabled', false);
304             querySelect.prop('disabled', false);
305         }
306         else if (QUERY_VIEWS_CACHE[queryKey] === 'loading') {
307             setTimeout(loadQueryViews, 500, schemaSelect, querySelect, queryViewselect, initValue);
308         }
309         else {
310             QUERY_COLUMNS_CACHE[queryKey] = 'loading';
311             LABKEY.Query.getQueryViews({
312                 schemaName: schemaName,
313                 queryName: queryName,
314                 success: function(data) {
315                     var views = [];
316                     $.each(data.views, function(i, view) {
317                         if (!view.hidden) {
318                             views.push(view)
319                         }
320                     });
321 
322                     QUERY_VIEWS_CACHE[queryKey] = views;
323 
324                     schemaSelect.prop('disabled', false);
325                     querySelect.prop('disabled', false);
326                     populateViews(queryViewselect, QUERY_VIEWS_CACHE[queryKey], initValue);
327                 }
328             })
329         }
330     }
331 
332     function populateViews(select, views, initValue) {
333         if (views.length > 0) {
334             populateSelect(select, views, 'name', 'label', initValue, true, false);
335         }
336         else {
337             select.empty().append($('<option>', {text: 'No views available'}));
338         }
339     }
340 
341     /**
342      * Documentation specified in core/Query.js -- search for "@name schemaSelectInput"
343      */
344     impl.schemaSelectInput = function(config) {
345         var SCHEMA_SELECT;
346 
347         if (!config || !config.renderTo) {
348             console.error('Invalid config object. Missing renderTo property for the <select> element.');
349             return;
350         }
351 
352         SCHEMA_SELECT = $("select[id='" + config.renderTo + "']");
353         if (SCHEMA_SELECT.length !== 1) {
354             console.error('Invalid config object. Expect to find exactly one <select> element for the renderTo provided (found: ' + SCHEMA_SELECT.length + ').');
355             return;
356         }
357 
358         loadingSelect(SCHEMA_SELECT);
359         LABKEY.Query.getSchemas({
360             includeHidden: false,
361             success: function(data) {
362                 populateSelect(SCHEMA_SELECT, data.schemas.sort(), null, null, config.initValue, config.isRequired, config.includeBlankOption);
363 
364                 // if there is a selected schema, fire the change event
365                 if (SCHEMA_SELECT.val()) {
366                     SCHEMA_SELECT.trigger('change', [SCHEMA_SELECT.val()]);
367                 }
368             }
369         });
370     };
371 
372     /**
373      * Documentation specified in core/Query.js -- search for "@name querySelectInput"
374      */
375     impl.querySelectInput = function(config) {
376         var SCHEMA_SELECT, QUERY_SELECT;
377 
378         if (!config || !config.renderTo || !config.schemaInputId) {
379             var msg = 'Invalid config object. ';
380             if (!config.renderTo) {
381                 msg += 'Missing renderTo property for the <select> element. ';
382             }
383             if (!config.schemaInputId) {
384                 msg += 'Missing schemaInputId property for the parent <select> element. ';
385             }
386             console.error(msg);
387             return;
388         }
389 
390         QUERY_SELECT = $("select[id='" + config.renderTo + "']");
391         if (QUERY_SELECT.length !== 1) {
392             console.error('Invalid config object. Expect to find exactly one <select> element with the name provided (found: ' + QUERY_SELECT.length + ').');
393             return;
394         }
395 
396         SCHEMA_SELECT = $("select[id='" + config.schemaInputId + "']");
397         if (SCHEMA_SELECT.length !== 1) {
398             console.error('Invalid config object. Expect to find exactly one <select> element with the name provided (found: ' + SCHEMA_SELECT.length + ').');
399             return;
400         }
401 
402         SCHEMA_SELECT.on('change', function (event, schemaName) {
403             loadQueries(SCHEMA_SELECT, QUERY_SELECT, schemaName || event.target.value, config.initValue, config.isRequired, config.includeBlankOption);
404         });
405     };
406 
407     /**
408      * Documentation specified in core/Query.js -- search for "@name columnSelectInput"
409      */
410     impl.columnSelectInput = function(config) {
411         var COLUMN_SELECT;
412 
413         if (!config || !config.renderTo || !config.schemaName || !config.queryName) {
414             var msg = 'Invalid config object. ';
415             if (!config.renderTo) {
416                 msg += 'Missing renderTo property for the <select> element. ';
417             }
418             if (!config.schemaName) {
419                 msg += 'Missing schemaName property. ';
420             }
421             if (!config.queryName) {
422                 msg += 'Missing queryName property. ';
423             }
424             console.error(msg);
425             return;
426         }
427 
428         COLUMN_SELECT = $("select[id='" + config.renderTo + "']");
429         if (COLUMN_SELECT.length !== 1) {
430             console.error('Invalid config object. Expect to find exactly one <select> element with the name provided (found: ' + COLUMN_SELECT.length + ').');
431             return;
432         }
433 
434         loadQueryColumns(COLUMN_SELECT, config.schemaName, config.queryName, config.viewName, config.filterFn, config.initValue, config.isRequired, config.includeBlankOption, config.sortFn);
435     };
436 
437     impl.queryViewSelectInput = function(config) {
438         var QUERYVIEW_SELECT, SCHEMA_SELECT, QUERY_SELECT;
439 
440         if (!config || !config.renderTo || !config.schemaInputId || !config.queryInputId) {
441             var msg = 'Invalid config object. ';
442             if (!config.renderTo) {
443                 msg += 'Missing renderTo property for the <select> element. ';
444             }
445             if (!config.schemaInputId) {
446                 msg += 'Missing schemaInputId property for the schema <select> element. ';
447             }
448             if (!config.queryInputId) {
449                 msg += 'Missing queryInputId property for the query <select> element. ';
450             }
451             console.error(msg);
452             return;
453         }
454 
455         if (!config.initValue) {
456             config.initValue = '';
457         }
458 
459         QUERYVIEW_SELECT = $("select[id='" + config.renderTo + "']");
460         if (QUERYVIEW_SELECT.length !== 1) {
461             console.error('Invalid config object. Expect to find exactly one <select> element with the name provided (found: ' + QUERYVIEW_SELECT.length + ').');
462             return;
463         }
464 
465         SCHEMA_SELECT = $("select[id='" + config.schemaInputId + "']");
466         if (SCHEMA_SELECT.length !== 1) {
467             console.error('Invalid config object. Expect to find exactly one <select> element with the name provided (found: ' + SCHEMA_SELECT.length + ').');
468             return;
469         }
470 
471         QUERY_SELECT = $("select[id='" + config.queryInputId + "']");
472         if (QUERY_SELECT.length !== 1) {
473             console.error('Invalid config object. Expect to find exactly one <select> element with the name provided (found: ' + QUERY_SELECT.length + ').');
474             return;
475         }
476 
477         QUERY_SELECT.on('change', function () {
478             loadQueryViews(SCHEMA_SELECT, QUERY_SELECT, QUERYVIEW_SELECT, config.initValue, config.isRequired); //never include a blank option
479         });
480     };
481 
482     /**
483      * Documentation specified in core/Query.js -- search for "@name importData"
484      */
485     impl.importData = function(config) {
486         if (!window.FormData) {
487             throw new Error('modern browser required');
488         }
489 
490         var form = new FormData();
491 
492         form.append('schemaName', config.schemaName);
493         form.append('queryName', config.queryName);
494         if (config.text)
495             form.append('text', config.text);
496         if (config.path)
497             form.append('path', config.path);
498         if (config.format)
499             form.append('format', config.format);
500         if (config.module)
501             form.append('module', config.module);
502         if (config.moduleResource)
503             form.append('moduleResource', config.moduleResource);
504         if (config.importIdentity)
505             form.append('importIdentity', config.importIdentity);
506         if (config.importLookupByAlternateKey !== undefined)
507             form.append('importLookupByAlternateKey', config.importLookupByAlternateKey);
508         if (config.saveToPipeline !== undefined)
509             form.append('saveToPipeline', config.saveToPipeline);
510 
511         if (config.file) {
512             if (config.file instanceof File)
513                 form.append('file', config.file);
514             else if (config.file.tagName === 'INPUT' && config.file.files.length > 0)
515                 form.append('file', config.file.files[0]);
516         }
517 
518         return LABKEY.Ajax.request({
519             url: config.importUrl || LABKEY.ActionURL.buildURL('query', 'import.api', config.containerPath),
520             method: 'POST',
521             success: LABKEY.Utils.getCallbackWrapper(LABKEY.Utils.getOnSuccess(config), config.scope, false),
522             failure: LABKEY.Utils.getCallbackWrapper(LABKEY.Utils.getOnFailure(config), config.scope, true),
523             form: form,
524             timeout: config.timeout
525         });
526     };
527 
528     return impl;
529 
530 }(LABKEY.Query || new function() { return {}; }, jQuery);
531