1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
|
/**
* @fileoverview JavaScript functions used on /table/search
*
* @requires jQuery
* @requires js/functions.js
*/
/* global changeValueFieldType, verifyAfterSearchFieldChange */ // js/table/change.js
/* global openGISEditor, gisEditorLoaded, loadJSAndGISEditor, loadGISEditor */ // js/gis_data_editor.js
var TableSelect = {};
/**
* Checks if given data-type is numeric or date.
*
* @param {string} dataType Column data-type
*
* @return {boolean | string}
*/
TableSelect.checkIfDataTypeNumericOrDate = function (dataType) {
// To test for numeric data-types.
var numericRegExp = new RegExp(
'TINYINT|SMALLINT|MEDIUMINT|INT|BIGINT|DECIMAL|FLOAT|DOUBLE|REAL',
'i'
);
// To test for date data-types.
var dateRegExp = new RegExp(
'DATETIME|DATE|TIMESTAMP|TIME|YEAR',
'i'
);
// Return matched data-type
if (numericRegExp.test(dataType)) {
return numericRegExp.exec(dataType)[0];
}
if (dateRegExp.test(dataType)) {
return dateRegExp.exec(dataType)[0];
}
return false;
};
/**
* Unbind all event handlers before tearing down a page
*/
window.AJAX.registerTeardown('table/select.js', function () {
$('#togglesearchformlink').off('click');
$(document).off('submit', '#tbl_search_form.ajax');
$('select.geom_func').off('change');
$(document).off('click', 'span.open_search_gis_editor');
$('body').off('change', 'select[name*="criteriaColumnOperators"]'); // Fix for bug #13778, changed 'click' to 'change'
});
window.AJAX.registerOnload('table/select.js', function () {
/**
* Prepare a div containing a link, otherwise it's incorrectly displayed
* after a couple of clicks
*/
$('<div id="togglesearchformdiv"><a id="togglesearchformlink"></a></div>')
.insertAfter('#tbl_search_form')
// don't show it until we have results on-screen
.hide();
$('#togglesearchformlink')
.html(window.Messages.strShowSearchCriteria)
.on('click', function () {
var $link = $(this);
$('#tbl_search_form').slideToggle();
if ($link.text() === window.Messages.strHideSearchCriteria) {
$link.text(window.Messages.strShowSearchCriteria);
} else {
$link.text(window.Messages.strHideSearchCriteria);
}
// avoid default click action
return false;
});
var tableRows = $('#fieldset_table_qbe select.column-operator');
$.each(tableRows, function (index, item) {
$(item).on('change', function () {
changeValueFieldType(this, index);
verifyAfterSearchFieldChange(index, '#tbl_search_form');
});
});
/**
* Ajax event handler for Table search
*/
$(document).on('submit', '#tbl_search_form.ajax', function (event) {
var unaryFunctions = [
'IS NULL',
'IS NOT NULL',
'= \'\'',
'!= \'\''
];
var geomUnaryFunctions = [
'IsEmpty',
'IsSimple',
'IsRing',
'IsClosed',
];
// jQuery object to reuse
var $searchForm = $(this);
event.preventDefault();
// empty previous search results while we are waiting for new results
$('#sqlqueryresultsouter').empty();
var $msgbox = Functions.ajaxShowMessage(window.Messages.strSearching, false);
Functions.prepareForAjaxRequest($searchForm);
var values = {};
$searchForm.find(':input').each(function () {
var $input = $(this);
if ($input.attr('type') === 'checkbox' || $input.attr('type') === 'radio') {
if ($input.is(':checked')) {
values[this.name] = $input.val();
}
} else {
values[this.name] = $input.val();
}
});
var columnCount = $('select[name="columnsToDisplay[]"] option').length;
// Submit values only for the columns that have unary column operator or a search criteria
for (var a = 0; a < columnCount; a++) {
if ($.inArray(values['criteriaColumnOperators[' + a + ']'], unaryFunctions) >= 0) {
continue;
}
if (values['geom_func[' + a + ']'] &&
$.inArray(values['geom_func[' + a + ']'], geomUnaryFunctions) >= 0) {
continue;
}
if (values['criteriaValues[' + a + ']'] === '' || values['criteriaValues[' + a + ']'] === null) {
delete values['criteriaValues[' + a + ']'];
delete values['criteriaColumnOperators[' + a + ']'];
delete values['criteriaColumnNames[' + a + ']'];
delete values['criteriaColumnTypes[' + a + ']'];
delete values['criteriaColumnCollations[' + a + ']'];
}
}
// If all columns are selected, use a single parameter to indicate that
if (values['columnsToDisplay[]'] !== null) {
if (values['columnsToDisplay[]'].length === columnCount) {
delete values['columnsToDisplay[]'];
values.displayAllColumns = true;
}
} else {
values.displayAllColumns = true;
}
$.post($searchForm.attr('action'), values, function (data) {
Functions.ajaxRemoveMessage($msgbox);
if (typeof data !== 'undefined' && data.success === true) {
if (typeof data.sql_query !== 'undefined') { // zero rows
$('#sqlqueryresultsouter').html(data.sql_query);
} else { // results found
$('#sqlqueryresultsouter').html(data.message);
$('.sqlqueryresults').trigger('makegrid');
}
$('#tbl_search_form')
// workaround for bug #3168569 - Issue on toggling the "Hide search criteria" in chrome.
.slideToggle()
.hide();
$('#togglesearchformlink')
// always start with the Show message
.text(window.Messages.strShowSearchCriteria);
$('#togglesearchformdiv')
// now it's time to show the div containing the link
.show();
$('html, body').animate({ scrollTop: 0 }, 'fast');
} else {
$('#sqlqueryresultsouter').html(data.error);
}
Functions.highlightSql($('#sqlqueryresultsouter'));
}); // end $.post()
});
// Following section is related to the 'function based search' for geometry data types.
// Initially hide all the open_gis_editor spans
$('span.open_search_gis_editor').hide();
$('select.geom_func').on('change', function () {
var $geomFuncSelector = $(this);
var binaryFunctions = [
'Contains',
'Crosses',
'Disjoint',
'Equals',
'Intersects',
'Overlaps',
'Touches',
'Within',
'MBRContains',
'MBRDisjoint',
'MBREquals',
'MBRIntersects',
'MBROverlaps',
'MBRTouches',
'MBRWithin',
'ST_Contains',
'ST_Crosses',
'ST_Disjoint',
'ST_Equals',
'ST_Intersects',
'ST_Overlaps',
'ST_Touches',
'ST_Within'
];
var tempArray = [
'Envelope',
'EndPoint',
'StartPoint',
'ExteriorRing',
'Centroid',
'PointOnSurface'
];
var outputGeomFunctions = binaryFunctions.concat(tempArray);
// If the chosen function takes two geometry objects as parameters
var $operator = $geomFuncSelector.parents('tr').find('td').eq(4).find('select');
if ($.inArray($geomFuncSelector.val(), binaryFunctions) >= 0) {
$operator.prop('readonly', true);
} else {
$operator.prop('readonly', false);
}
// if the chosen function's output is a geometry, enable GIS editor
var $editorSpan = $geomFuncSelector.parents('tr').find('span.open_search_gis_editor');
if ($.inArray($geomFuncSelector.val(), outputGeomFunctions) >= 0) {
$editorSpan.show();
} else {
$editorSpan.hide();
}
});
$(document).on('click', 'span.open_search_gis_editor', function (event) {
event.preventDefault();
var $span = $(this);
// Current value
var value = $span.parent('td').children('input[type=\'text\']').val();
// Field name
var field = 'Parameter';
// Column type
var geomFunc = $span.parents('tr').find('.geom_func').val();
var type;
if (geomFunc === 'Envelope') {
type = 'polygon';
} else if (geomFunc === 'ExteriorRing') {
type = 'linestring';
} else {
type = 'point';
}
// Names of input field and null checkbox
var inputName = $span.parent('td').children('input[type=\'text\']').attr('name');
// Token
openGISEditor();
if (!gisEditorLoaded) {
loadJSAndGISEditor(value, field, type, inputName);
} else {
loadGISEditor(value, field, type, inputName);
}
});
/**
* Ajax event handler for Range-Search.
*/
$('body').on('change', 'select[name*="criteriaColumnOperators"]', function () { // Fix for bug #13778, changed 'click' to 'change'
var $sourceSelect = $(this);
// Get the column name.
var columnName = $(this)
.closest('tr')
.find('th')
.first()
.text();
// Get the data-type of column excluding size.
var dataType = $(this)
.closest('tr')
.find('td[data-type]')
.attr('data-type');
dataType = TableSelect.checkIfDataTypeNumericOrDate(dataType);
// Get the operator.
var operator = $(this).val();
if ((operator === 'BETWEEN' || operator === 'NOT BETWEEN') && dataType) {
var $msgbox = Functions.ajaxShowMessage();
$.ajax({
url: 'index.php?route=/table/search',
type: 'POST',
data: {
'server': window.CommonParams.get('server'),
'ajax_request': 1,
'db': $('input[name="db"]').val(),
'table': $('input[name="table"]').val(),
'column': columnName,
'range_search': 1
},
success: function (response) {
Functions.ajaxRemoveMessage($msgbox);
if (response.success) {
// Get the column min value.
var min = response.column_data.min
? '(' + window.Messages.strColumnMin +
' ' + response.column_data.min + ')'
: '';
// Get the column max value.
var max = response.column_data.max
? '(' + window.Messages.strColumnMax +
' ' + response.column_data.max + ')'
: '';
$('#rangeSearchModal').modal('show');
$('#rangeSearchLegend').first().html(operator);
$('#rangeSearchMin').first().text(min);
$('#rangeSearchMax').first().text(max);
// Reset input values on reuse
$('#min_value').first().val('');
$('#max_value').first().val('');
// Add datepicker wherever required.
Functions.addDatepicker($('#min_value'), dataType);
Functions.addDatepicker($('#max_value'), dataType);
$('#rangeSearchModalGo').on('click', function () {
var minValue = $('#min_value').val();
var maxValue = $('#max_value').val();
var finalValue = '';
if (minValue.length && maxValue.length) {
finalValue = minValue + ', ' +
maxValue;
}
var $targetField = $sourceSelect.closest('tr')
.find('[name*="criteriaValues"]');
// If target field is a select list.
if ($targetField.is('select')) {
$targetField.val(finalValue);
var $options = $targetField.find('option');
var $closestMin = null;
var $closestMax = null;
// Find closest min and max value.
$options.each(function () {
if (
$closestMin === null
|| Math.abs($(this).val() - minValue) < Math.abs($closestMin.val() - minValue)
) {
$closestMin = $(this);
}
if (
$closestMax === null
|| Math.abs($(this).val() - maxValue) < Math.abs($closestMax.val() - maxValue)
) {
$closestMax = $(this);
}
});
$closestMin.attr('selected', 'selected');
$closestMax.attr('selected', 'selected');
} else {
$targetField.val(finalValue);
}
$('#rangeSearchModal').modal('hide');
});
} else {
Functions.ajaxShowMessage(response.error);
}
},
error: function () {
Functions.ajaxShowMessage(window.Messages.strErrorProcessingRequest);
}
});
}
});
var windowWidth = $(window).width();
$('.jsresponsive').css('max-width', (windowWidth - 69) + 'px');
});
|