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