| 1 |
const TAB_DEFAULT_LABEL = 'New Query'; |
| 2 |
|
| 3 |
var editors = {}; |
| 4 |
var tabIndex = 0; |
| 5 |
var tabs = []; |
| 6 |
var isChanged = {}; |
| 7 |
var isVisual = {}; |
| 8 |
var dbHints = {} |
| 9 |
var columnLink = {}; |
| 10 |
|
| 11 |
function tabActivate(activeIndex) { |
| 12 |
jQuery(".wpda_query_builder").hide(); |
| 13 |
jQuery("#wpda_query_builder_" + activeIndex).show(); |
| 14 |
|
| 15 |
jQuery(".nav-tab").removeClass("nav-tab-active"); |
| 16 |
jQuery("#wpda_query_builder_label_" + activeIndex).addClass("nav-tab-active"); |
| 17 |
} |
| 18 |
|
| 19 |
function tabNew(tabName = TAB_DEFAULT_LABEL, query = '', schema_name = wpda_default_database) { |
| 20 |
tabIndex++; |
| 21 |
if (tabName===TAB_DEFAULT_LABEL) { |
| 22 |
tabName += " (" + tabIndex + ")"; |
| 23 |
dbsName = ''; |
| 24 |
} else { |
| 25 |
dbsName = tabName; |
| 26 |
} |
| 27 |
|
| 28 |
tabLabel = ` |
| 29 |
<a id="wpda_query_builder_label_${tabIndex}" |
| 30 |
class="nav-tab wpda_query_builder_label wpda_tooltip" |
| 31 |
data-id="${tabIndex}" |
| 32 |
href="javascript:void(0)" |
| 33 |
title="Double click to change query name" |
| 34 |
> |
| 35 |
<i class="fas fa-pen wpda_icon_on_button"></i> |
| 36 |
<span id="wpda_query_builder_label_value_${tabIndex}" |
| 37 |
class="wpda_query_builder_label_value" |
| 38 |
contenteditable="true" |
| 39 |
data-dbs-name="${dbsName}" |
| 40 |
onclick="tabActivate('${tabIndex}')" |
| 41 |
ondblclick="selectContent(event)" |
| 42 |
>${tabName}</span> |
| 43 |
<span id="tab-${tabIndex}-icon" |
| 44 |
class="dashicons dashicons-dismiss icon_close" |
| 45 |
style="vertical-align: middle" |
| 46 |
onclick="tabClose('${tabIndex}')" |
| 47 |
></span> |
| 48 |
</a> |
| 49 |
`; |
| 50 |
jQuery("#wpda_query_builder nav.nav-tab-wrapper").append(tabLabel); |
| 51 |
document.getElementById("wpda_query_builder_label_value_" + tabIndex).ondblclick = function(){ |
| 52 |
event.preventDefault(); |
| 53 |
var sel = window.getSelection(); |
| 54 |
var range = document.createRange(); |
| 55 |
range.selectNodeContents(this); |
| 56 |
sel.removeAllRanges(); |
| 57 |
sel.addRange(range); |
| 58 |
}; |
| 59 |
|
| 60 |
// |
| 61 |
let vqbButton = ''; |
| 62 |
if (vqbInstalled) { |
| 63 |
vqbButton = ` |
| 64 |
<span> |
| 65 |
<a href="javascript:void(0)" onclick="addVisual('${tabIndex}')" class="wpda_tooltip button button-primary wpda_vqb_button" title="Enable Visual Query Builder for this query"> |
| 66 |
<i class="fas fa-eye wpda_icon_on_button"></i> Visual Query Builder</a> |
| 67 |
</span> |
| 68 |
`; |
| 69 |
} |
| 70 |
|
| 71 |
tabContent = ` |
| 72 |
<div id="wpda_query_builder_${tabIndex}" class="wpda_query_builder" data-id="${tabIndex}"> |
| 73 |
<div class="wpda_query_builder_taskbar"> |
| 74 |
${vqbButton} |
| 75 |
<span> |
| 76 |
<label> |
| 77 |
Select database |
| 78 |
<select id="wpda_query_builder_dbs_${tabIndex}" onchange="setHints('${tabIndex}')">${wpda_databases}</select> |
| 79 |
</label> |
| 80 |
</span> |
| 81 |
<span> |
| 82 |
<label> |
| 83 |
<input id="wpda_query_builder_wordpress_protect_${tabIndex}" type="checkbox" checked /> |
| 84 |
Protect WordPress tables |
| 85 |
</label> |
| 86 |
</span> |
| 87 |
<span class="wpda_query_builder_actions"> |
| 88 |
<label> |
| 89 |
<input id="use_max_rows_${tabIndex}" type="checkbox" checked/> |
| 90 |
Max rows: |
| 91 |
<input id="max_rows_${tabIndex}" type="number" value="100" min="1" onblur="if (jQuery(this).val()==='') { jQuery(this).val(100) }" style="width: 100px"/> |
| 92 |
</label> |
| 93 |
<a href="javascript:void(0)" onclick="executeQuery('${tabIndex}')" class="wpda_tooltip button button-primary" title="Execute query"> |
| 94 |
<i class="fas fa-play wpda_icon_on_button"></i> Execute</a> |
| 95 |
<span id="executing_query_${tabIndex}" style="display: none"> |
| 96 |
<img src="${wpda_loader_url}" class="wpda_spinner" /> |
| 97 |
</span> |
| 98 |
<a href="javascript:void(0)" onclick="saveQuery('${tabIndex}')" class="wpda_tooltip button button-secondary" title="Save query"> |
| 99 |
<i class="fas fa-cloud-upload wpda_icon_on_button"></i> Save</a> |
| 100 |
<button href="javascript:void(0)" class="wpda_tooltip button button-secondary wpda_copy_to_clipboard" title="Copy query to clipboard" data-clipboard-text="ABC"> |
| 101 |
<i class="fas fa-clipboard wpda_icon_on_button"></i> Copy to clipboard</button> |
| 102 |
|
| 103 |
<a href="javascript:void(0)" class="wpda_tooltip button button-secondary wpda-query-help" style="font-weight:bold" title="Use / to separate multiple SQL commands: |
| 104 |
|
| 105 |
select * from dept |
| 106 |
/ |
| 107 |
select * from emp |
| 108 |
/ |
| 109 |
|
| 110 |
The / must be on an empty line |
| 111 |
">?</a> |
| 112 |
</span> |
| 113 |
</div> |
| 114 |
<div id="wpda_query_builder_sql_container_${tabIndex}" class="wpda_query_builder_sql"> |
| 115 |
<textarea id="wpda_query_builder_sql_${tabIndex}">${query}</textarea> |
| 116 |
</div> |
| 117 |
<div id="wpda_query_builder_tabs_${tabIndex}" class="wpda_query_builder_tabs" style="display: none"></div> |
| 118 |
${queryResult(tabIndex)} |
| 119 |
</div>`; |
| 120 |
jQuery("#wpda_query_builder").append(tabContent); |
| 121 |
|
| 122 |
editors['tab' + tabIndex] = wp.codeEditor.initialize(jQuery('#wpda_query_builder_sql_' + tabIndex), cm_settings); |
| 123 |
editors['tab' + tabIndex].codemirror.setOption('tabindex', tabIndex); |
| 124 |
editors['tab' + tabIndex].codemirror.on('change', function(cm_editor) { |
| 125 |
isChanged[cm_editor.getOption('tabindex')] = true; |
| 126 |
}); |
| 127 |
|
| 128 |
jQuery("#wpda_query_builder_dbs_" + tabIndex).val(schema_name); |
| 129 |
jQuery('.wpda_tooltip').tooltip({ |
| 130 |
track: true |
| 131 |
}); |
| 132 |
new ClipboardJS(".wpda_copy_to_clipboard"); |
| 133 |
jQuery(".wpda_copy_to_clipboard").on("click", { tabIndex: tabIndex }, function() { |
| 134 |
cm = editors['tab' + tabIndex].codemirror; |
| 135 |
cm.save(); |
| 136 |
jQuery(this).attr("data-clipboard-text", jQuery("#wpda_query_builder_sql_" + tabIndex).val()); |
| 137 |
jQuery.notify('Query copied to clipboard', 'success'); |
| 138 |
}); |
| 139 |
|
| 140 |
tabActivate(tabIndex); |
| 141 |
isChanged[tabIndex] = false; |
| 142 |
isVisual[tabIndex] = false; |
| 143 |
setHints(tabIndex); |
| 144 |
|
| 145 |
jQuery('.wpda_tooltip').tooltip({ |
| 146 |
tooltipClass: "wpda_tooltip_dashboard" |
| 147 |
}); |
| 148 |
} |
| 149 |
|
| 150 |
function setHints(activeIndex) { |
| 151 |
var schemaName = jQuery("#wpda_query_builder_dbs_" + activeIndex).val(); |
| 152 |
if (!dbHints[schemaName]) { |
| 153 |
jQuery.ajax({ |
| 154 |
method: 'POST', |
| 155 |
url: wpda_home_url + "?action=wpda_query_builder_get_db_hints", |
| 156 |
data: { |
| 157 |
wpda_wpnonce: wpda_wpnonce, |
| 158 |
wpda_schemaname: schemaName |
| 159 |
} |
| 160 |
}).done( |
| 161 |
function (msg) { |
| 162 |
if (msg.status && msg.tables && msg.status === "OK") { |
| 163 |
tabTables = Object.assign({}, msg.tables); |
| 164 |
for (var table in msg.tables) { |
| 165 |
for (var i = 0; i < msg.tables[table].length; i++) { |
| 166 |
tabTables[msg.tables[table][i]] = []; |
| 167 |
} |
| 168 |
} |
| 169 |
editors['tab' + activeIndex].codemirror.options.hintOptions = { |
| 170 |
tables: tabTables |
| 171 |
} |
| 172 |
// Save tables for new tabs |
| 173 |
dbHints[schemaName] = tabTables; |
| 174 |
// Update visual component if enabled |
| 175 |
if (isVisual[activeIndex]) { |
| 176 |
updateVisual(activeIndex); |
| 177 |
} |
| 178 |
} else { |
| 179 |
editors['tab' + activeIndex].codemirror.options.hintOptions = { |
| 180 |
tables: null |
| 181 |
} |
| 182 |
} |
| 183 |
} |
| 184 |
).fail( |
| 185 |
function (msg) { |
| 186 |
console.log("WP Data Access ERROR:"); |
| 187 |
console.log(msg); |
| 188 |
editors['tab' + activeIndex].codemirror.options.hintOptions = { |
| 189 |
tables: null |
| 190 |
} |
| 191 |
} |
| 192 |
); |
| 193 |
} else { |
| 194 |
editors['tab' + activeIndex].codemirror.options.hintOptions = { |
| 195 |
tables: dbHints[schemaName] |
| 196 |
} |
| 197 |
// Update visual component if enabled |
| 198 |
if (isVisual[activeIndex]) { |
| 199 |
updateVisual(activeIndex); |
| 200 |
} |
| 201 |
} |
| 202 |
|
| 203 |
editors['tab' + activeIndex].codemirror.on('keyup', (cm, event) => { |
| 204 |
if (!jQuery("#wpda_sql_hints").is(":checked")) { |
| 205 |
return; |
| 206 |
} |
| 207 |
|
| 208 |
if ( |
| 209 |
event.key==="Backspace" || |
| 210 |
event.key==="Escape" || |
| 211 |
event.key==="ArrowUp" || |
| 212 |
event.key==="ArrowDown" |
| 213 |
) { |
| 214 |
return; |
| 215 |
} |
| 216 |
|
| 217 |
editors['tab' + activeIndex].codemirror.execCommand('autocomplete'); |
| 218 |
}); |
| 219 |
} |
| 220 |
|
| 221 |
function queryResult(activeIndex) { |
| 222 |
return ` |
| 223 |
<div id="wpda_query_builder_menubar_${activeIndex}" class="wpda_query_builder_menubar" style="display: none"> |
| 224 |
<label>Export to</label> |
| 225 |
<button class="button button-primary" onclick="exportTable('CSV', ${activeIndex})">CSV</button> |
| 226 |
<button class="button button-primary" onclick="exportTable('JSON', ${activeIndex})">JSON</button> |
| 227 |
<button class="button button-primary" onclick="exportTable('XML', ${activeIndex})">XML</button> |
| 228 |
</div> |
| 229 |
<div id="wpda_query_builder_result_${activeIndex}" class="wpda_query_builder_result"></div> |
| 230 |
<div id="wpda_query_builder_statusbar_${activeIndex}" style="display: none" class="wpda_query_builder_statusbar"> |
| 231 |
<a href="javascript:void(0)" onclick="jQuery('#wpda_query_builder_viewer_${activeIndex}').toggle(); jQuery('html, body').animate({ scrollTop: jQuery(window).height()-200}, 600);" class="wpda_tooltip button button-primary" title="View raw output"> |
| 232 |
<i class="fas fa-code wpda_icon_on_button"></i></a> |
| 233 |
<span class="wpda_query_builder_statusbar_message"></span> |
| 234 |
</div> |
| 235 |
<div id="wpda_query_builder_viewer_${activeIndex}" style="display: none" class="wpda_query_builder_viewer"> |
| 236 |
<pre id="wpda_query_builder_json_${activeIndex}"></pre> |
| 237 |
</div> |
| 238 |
`; |
| 239 |
} |
| 240 |
|
| 241 |
function tabClose(activeIndex) { |
| 242 |
if (isChanged[activeIndex]) { |
| 243 |
if (!confirm('Your changes will not be saved! Are you sure you want to leave this page?')) { |
| 244 |
return; |
| 245 |
} |
| 246 |
} |
| 247 |
jQuery("#wpda_query_builder_label_" + activeIndex).remove(); |
| 248 |
jQuery("#wpda_query_builder_" + activeIndex).remove(); |
| 249 |
delete editors['tab' + activeIndex]; |
| 250 |
delete isChanged[activeIndex]; |
| 251 |
if (jQuery(".wpda_query_builder").length>0) { |
| 252 |
if (jQuery(".nav-tab-active").data("id")===undefined) { |
| 253 |
tabActivate(jQuery(".nav-tab").data("id")); |
| 254 |
} |
| 255 |
} else { |
| 256 |
tabNew(); |
| 257 |
} |
| 258 |
} |
| 259 |
|
| 260 |
function tabOpen() { |
| 261 |
var queryName = jQuery("#wpda_query_builder_open_select").find(":selected").text(); |
| 262 |
tabNew( |
| 263 |
queryName, |
| 264 |
jQuery("#wpda_query_builder_open_select").find(":selected").data("sql"), |
| 265 |
jQuery("#wpda_query_builder_open_select").find(":selected").data("dbs") |
| 266 |
); |
| 267 |
closeQuery(); |
| 268 |
|
| 269 |
if (jQuery("#wpda_query_builder_open_select").find(":selected").data("vqb")==true) { |
| 270 |
// Restore Visual Query Builder |
| 271 |
getVisualQueryBuilder(tabIndex, queryName); |
| 272 |
} |
| 273 |
} |
| 274 |
|
| 275 |
function tabOpenAll() { |
| 276 |
jQuery("#wpda_query_builder_open_select option").each( |
| 277 |
function() { |
| 278 |
tabNew( |
| 279 |
jQuery(this).text(), |
| 280 |
jQuery(this).data("sql"), |
| 281 |
jQuery(this).data("dbs") |
| 282 |
); |
| 283 |
closeQuery(); |
| 284 |
} |
| 285 |
); |
| 286 |
} |
| 287 |
|
| 288 |
function showData(activeIndex, msg) { |
| 289 |
if ( msg.tabs.length > 0 ) { |
| 290 |
// Multiple SQL commands |
| 291 |
jQuery("#wpda_query_builder_result_" + activeIndex).html(''); |
| 292 |
showTabs(activeIndex, msg); |
| 293 |
} else { |
| 294 |
// Single SQL command |
| 295 |
jQuery("#wpda_query_builder_tabs_" + activeIndex).empty().hide(); |
| 296 |
showRows(activeIndex, msg); |
| 297 |
} |
| 298 |
} |
| 299 |
|
| 300 |
function queryTabClose(activeIndex, tabIndex) { |
| 301 |
jQuery("li#litab" + activeIndex + "-" + tabIndex).remove(); |
| 302 |
jQuery("div#tab" + activeIndex + "-" + tabIndex).remove(); |
| 303 |
var li = jQuery("div#tabs" + activeIndex + " ul li")[0].id; |
| 304 |
jQuery("#" + li).find("a").click(); |
| 305 |
} |
| 306 |
|
| 307 |
function showTabs(activeIndex, msg) { |
| 308 |
var ul = jQuery("<ul/>"); |
| 309 |
for (var i=0; i<msg.tabs.length; i++) { |
| 310 |
if (msg.tabs[i]['cmd']===null || msg.tabs[i]['cmd']===undefined) { |
| 311 |
sql = "SQL ERROR"; |
| 312 |
} else { |
| 313 |
sql = msg.tabs[i]['cmd']; |
| 314 |
} |
| 315 |
ul.append(jQuery("<li/>", { "id": "litab" + activeIndex + "-" + i }) |
| 316 |
.append(jQuery("<a/>", { "href": "#tab" + activeIndex + "-" + i, "title": sql, "class": "wpda_tooltip" }) |
| 317 |
.html("<span class='dashicons dashicons-database-view'></span> " + (i+1) + ". sql cmd <span class='dashicons dashicons-dismiss icon_close' style='vertical-align: middle' onclick='queryTabClose(" + activeIndex + "," + i + ")'></span>"))); |
| 318 |
} |
| 319 |
var tabs = jQuery("<div/>", { "id": "tabs" + activeIndex }).append(ul); |
| 320 |
|
| 321 |
for (var i=0; i<msg.tabs.length; i++) { |
| 322 |
var tabResultDiv = queryResult("" + activeIndex + i); |
| 323 |
var style = i===0 ? "block" : "none"; |
| 324 |
tabs.append(jQuery("<div/>", { "id": "tab" + activeIndex + "-" + i, "style": "display:"+style }) |
| 325 |
.append(tabResultDiv)); |
| 326 |
} |
| 327 |
jQuery("#wpda_query_builder_tabs_" + activeIndex).empty().append(tabs); |
| 328 |
jQuery("#wpda_query_builder_tabs_" + activeIndex).show(); |
| 329 |
|
| 330 |
for (var i=0; i<msg.tabs.length; i++) { |
| 331 |
showRows("" + activeIndex + i, msg.tabs[i]); |
| 332 |
} |
| 333 |
jQuery("div#tabs" + activeIndex).tabs(); |
| 334 |
jQuery('.wpda_tooltip').tooltip(); |
| 335 |
} |
| 336 |
|
| 337 |
function showRows(activeIndex, msg) { |
| 338 |
if (msg.wpdavar !== undefined) { |
| 339 |
jQuery("#wpda_query_builder_result_" + activeIndex).html('<p style="padding-left: 15px">Done</p>'); |
| 340 |
} else { |
| 341 |
if (msg.status === null || msg.status === undefined) { |
| 342 |
jQuery("#wpda_query_builder_result_" + activeIndex).html("<strong>WP Data Access error:</strong> Query failed"); |
| 343 |
} else { |
| 344 |
if (msg.status.last_result === null || msg.status.last_result === undefined) { |
| 345 |
if (typeof msg.status !== "string") { |
| 346 |
jQuery("#wpda_query_builder_result_" + activeIndex).html("<strong>WP Data Access error:</strong> Query OK"); |
| 347 |
} else { |
| 348 |
jQuery("#wpda_query_builder_result_" + activeIndex).html(msg.status); |
| 349 |
} |
| 350 |
} else { |
| 351 |
if (msg.status.last_error === "") { |
| 352 |
if (msg.status.last_result.length > 0) { |
| 353 |
rows = msg.status.last_result; |
| 354 |
first_row = rows[0]; |
| 355 |
header = "<tr>"; |
| 356 |
for (var col in first_row) { |
| 357 |
header += "<th>" + col + "</th>"; |
| 358 |
} |
| 359 |
header += "</tr>"; |
| 360 |
body = ""; |
| 361 |
for (var i = 0; i < rows.length; i++) { |
| 362 |
body += "<tr>"; |
| 363 |
for (var col in rows[i]) { |
| 364 |
let columnClassName = ""; |
| 365 |
if (msg.columns === null || msg.columns === undefined) { |
| 366 |
// No data type available |
| 367 |
} else { |
| 368 |
columnClassName = "wpda_data_type_" + getColumnDataType(msg.columns, col); |
| 369 |
} |
| 370 |
if (rows[i][col] === null) { |
| 371 |
columnClassName += " wpda_data_value_null"; |
| 372 |
} |
| 373 |
body += |
| 374 |
"<td class='" + columnClassName + "'>" + |
| 375 |
jQuery("<textarea/>").text(rows[i][col]).html() + |
| 376 |
"</td>"; |
| 377 |
} |
| 378 |
body += "</tr>"; |
| 379 |
} |
| 380 |
table = |
| 381 |
jQuery('<table class="wpda_query_builder_table" data-id="' + activeIndex + '"/>') |
| 382 |
.append(jQuery('<thead/>').append(header)) |
| 383 |
.append(jQuery('<tbody/>').append(body)); |
| 384 |
jQuery("#wpda_query_builder_menubar_" + activeIndex).show(); |
| 385 |
jQuery("#wpda_query_builder_result_" + activeIndex).html(table); |
| 386 |
rowLabel = rows.length === 1 ? "row" : "rows"; |
| 387 |
html = rows.length + " " + rowLabel; |
| 388 |
if (msg.status.queries !== null) { |
| 389 |
html += " (" + msg.status.queries[msg.status.num_queries - 1][1].toFixed(5) + " sec)"; |
| 390 |
} |
| 391 |
jQuery("#wpda_query_builder_statusbar_" + activeIndex + " span.wpda_query_builder_statusbar_message").html( |
| 392 |
html |
| 393 |
); |
| 394 |
jQuery("#wpda_query_builder_statusbar_" + activeIndex).show(); |
| 395 |
jQuery("#wpda_query_builder_json_" + activeIndex).jsonViewer(msg.status); |
| 396 |
jQuery("#wpda_query_builder_json_" + activeIndex + " ul li a.json-toggle").click(); |
| 397 |
|
| 398 |
setResultDivHeight(activeIndex); |
| 399 |
} else { |
| 400 |
rowLabel = msg.status.rows_affected === 1 ? "row" : "rows"; |
| 401 |
html = "Query OK, " + msg.status.rows_affected + " " + rowLabel + " affected"; |
| 402 |
if (msg.status.queries !== null) { |
| 403 |
html += " (" + msg.status.queries[msg.status.num_queries - 1][1].toFixed(5) + " sec)" |
| 404 |
} |
| 405 |
jQuery("#wpda_query_builder_menubar_" + activeIndex).hide(); |
| 406 |
jQuery("#wpda_query_builder_result_" + activeIndex).html(""); |
| 407 |
jQuery("#wpda_query_builder_statusbar_" + activeIndex + " span.wpda_query_builder_statusbar_message").html(html); |
| 408 |
jQuery("#wpda_query_builder_statusbar_" + activeIndex).show(); |
| 409 |
jQuery("#wpda_query_builder_json_" + activeIndex).jsonViewer(msg.status); |
| 410 |
jQuery("#wpda_query_builder_json_" + activeIndex + " ul li a.json-toggle").click(); |
| 411 |
} |
| 412 |
} else { |
| 413 |
error = `<strong>WordPress database error:</strong> ${msg.status.last_error}<br/><br/><code>${msg.status.last_query}</code>`; |
| 414 |
jQuery("#wpda_query_builder_result_" + activeIndex).html(error); |
| 415 |
} |
| 416 |
} |
| 417 |
} |
| 418 |
} |
| 419 |
} |
| 420 |
|
| 421 |
function getColumnDataType(columns, columnName) { |
| 422 |
for (let i=0; i<columns.length; i++) { |
| 423 |
if (columns[i].Field===columnName) { |
| 424 |
if ( |
| 425 |
columns[i].Type.includes("int") || |
| 426 |
columns[i].Type.includes("float") || |
| 427 |
columns[i].Type.includes("double") || |
| 428 |
columns[i].Type.includes("decimal") |
| 429 |
) { |
| 430 |
return "number"; |
| 431 |
} else if ( |
| 432 |
columns[i].Type.includes("date") || |
| 433 |
columns[i].Type.includes("time") |
| 434 |
) { |
| 435 |
return "date"; |
| 436 |
} else { |
| 437 |
return "string"; |
| 438 |
} |
| 439 |
} |
| 440 |
} |
| 441 |
return "string"; |
| 442 |
} |
| 443 |
|
| 444 |
function setResultDivHeight(activeIndex) { |
| 445 |
viewHeight = jQuery(window).height(); |
| 446 |
positionX = jQuery("#wpda_query_builder_result_" + activeIndex).offset().top; |
| 447 |
if (positionX===0) { |
| 448 |
positionX = viewHeight/2; |
| 449 |
} |
| 450 |
divHeight = viewHeight - positionX - 140; |
| 451 |
if (divHeight<400) { |
| 452 |
divHeight = 400; |
| 453 |
} |
| 454 |
jQuery("#wpda_query_builder_result_" + activeIndex + " table.wpda_query_builder_table tbody").height(divHeight); |
| 455 |
} |
| 456 |
|
| 457 |
function showError(activeIndex,msg) { |
| 458 |
jQuery("#wpda_query_builder_menubar_" + activeIndex).hide(); |
| 459 |
jQuery("#wpda_query_builder_result_" + activeIndex).html(msg.responseText); |
| 460 |
jQuery("#wpda_query_builder_statusbar_" + activeIndex).hide(); |
| 461 |
} |
| 462 |
|
| 463 |
function executeQuery(activeIndex) { |
| 464 |
if (isVisual[activeIndex]) { |
| 465 |
var currentStatus = isChanged[activeIndex]; |
| 466 |
if (!updateQuery(activeIndex, true)) { |
| 467 |
return; |
| 468 |
} |
| 469 |
isChanged[activeIndex] = currentStatus; |
| 470 |
} |
| 471 |
|
| 472 |
// Execute query |
| 473 |
cm = editors['tab' + activeIndex].codemirror; |
| 474 |
cm.save(); |
| 475 |
|
| 476 |
sql = jQuery("#wpda_query_builder_sql_" + activeIndex).val(); |
| 477 |
limit = ''; |
| 478 |
|
| 479 |
if (jQuery("#use_max_rows_" + activeIndex).is(":checked")) { |
| 480 |
limit = jQuery("#max_rows_" + activeIndex).val(); |
| 481 |
} |
| 482 |
|
| 483 |
jQuery("#executing_query_" + activeIndex).show(); |
| 484 |
|
| 485 |
if (isVisual[activeIndex]) { |
| 486 |
if (isVisualQueryBuilderActive(activeIndex)) { |
| 487 |
jQuery("#visualOutputContainer" + activeIndex).tabs("option", "active", 1); |
| 488 |
} |
| 489 |
} |
| 490 |
|
| 491 |
jQuery.ajax({ |
| 492 |
method: 'POST', |
| 493 |
url: wpda_home_url + "?action=wpda_query_builder_execute_sql", |
| 494 |
data: { |
| 495 |
wpda_wpnonce: wpda_wpnonce, |
| 496 |
wpda_schemaname: jQuery("#wpda_query_builder_dbs_" + activeIndex).val(), |
| 497 |
wpda_sqlquery: escapeHtml(sql), |
| 498 |
wpda_sqllimit: limit, |
| 499 |
wpda_protect: jQuery("#wpda_query_builder_wordpress_protect_" + activeIndex).is(":checked") |
| 500 |
} |
| 501 |
}).done( |
| 502 |
function (msg) { |
| 503 |
jQuery("#executing_query_" + activeIndex).hide(); |
| 504 |
showData(activeIndex, msg); |
| 505 |
} |
| 506 |
).fail( |
| 507 |
function (msg) { |
| 508 |
jQuery("#executing_query_" + activeIndex).hide(); |
| 509 |
showError(activeIndex, msg); |
| 510 |
} |
| 511 |
); |
| 512 |
} |
| 513 |
|
| 514 |
function saveQuery(activeIndex) { |
| 515 |
// Save query |
| 516 |
cm = editors['tab' + activeIndex].codemirror; |
| 517 |
cm.save(); |
| 518 |
|
| 519 |
var data = { |
| 520 |
wpda_wpnonce: wpda_wpnonce, |
| 521 |
wpda_schemaname: jQuery("#wpda_query_builder_dbs_" + activeIndex).val(), |
| 522 |
wpda_sqlqueryname: jQuery("#wpda_query_builder_label_value_" + activeIndex).html(), |
| 523 |
wpda_sqlqueryname_old: jQuery("#wpda_query_builder_label_value_" + activeIndex).data("dbs-name"), |
| 524 |
wpda_sqlquery: escapeHtml(jQuery("#wpda_query_builder_sql_" + activeIndex).val()) |
| 525 |
}; |
| 526 |
|
| 527 |
if (isVisual[activeIndex]) { |
| 528 |
data.wpda_vqb = getWidgets(activeIndex); |
| 529 |
} |
| 530 |
|
| 531 |
jQuery.ajax({ |
| 532 |
method: 'POST', |
| 533 |
url: wpda_home_url + "?action=wpda_query_builder_save_sql", |
| 534 |
data: data |
| 535 |
}).done( |
| 536 |
function (msg) { |
| 537 |
jQuery("#wpda_query_builder_label_value_" + activeIndex) |
| 538 |
.attr("data-dbs-name", jQuery("#wpda_query_builder_label_value_" + activeIndex).text()); |
| 539 |
isChanged[activeIndex] = false; |
| 540 |
jQuery.notify('Query saved', 'success'); |
| 541 |
} |
| 542 |
).fail( |
| 543 |
function (msg) { |
| 544 |
console.log(activeIndex, msg); |
| 545 |
jQuery.notify('Could not save query', 'error'); |
| 546 |
} |
| 547 |
); |
| 548 |
} |
| 549 |
|
| 550 |
function openQuery() { |
| 551 |
activeDbsNames = []; |
| 552 |
jQuery(".wpda_query_builder_label_value").each( |
| 553 |
function() { |
| 554 |
activeDbsNames.push(jQuery(this).data("dbs-name")); |
| 555 |
} |
| 556 |
); |
| 557 |
|
| 558 |
jQuery.ajax({ |
| 559 |
method: 'POST', |
| 560 |
url: wpda_home_url + "?action=wpda_query_builder_open_sql", |
| 561 |
data: { |
| 562 |
wpda_wpnonce: wpda_wpnonce, |
| 563 |
wpda_exclude: activeDbsNames.join(",") |
| 564 |
} |
| 565 |
}).done( |
| 566 |
function (msg) { |
| 567 |
jQuery("#wpda_query_builder_open_select").find("option").remove(); |
| 568 |
if (!Array.isArray(msg.data)) { |
| 569 |
for (var queryName in msg.data) { |
| 570 |
if (msg.data[queryName] !== null && msg.data[queryName] !== undefined) { |
| 571 |
jQuery("#wpda_query_builder_open_select") |
| 572 |
.append( |
| 573 |
jQuery("<option/>", { |
| 574 |
value: queryName, |
| 575 |
text: queryName |
| 576 |
}) |
| 577 |
.attr("data-dbs", msg.data[queryName].schema_name) |
| 578 |
.attr("data-sql", msg.data[queryName].query) |
| 579 |
.attr("data-vqb", msg.data[queryName].is_visual === true) |
| 580 |
); |
| 581 |
} |
| 582 |
} |
| 583 |
jQuery("#wpda_query_builder_open_select").attr("disabled", false); |
| 584 |
jQuery("#wpda_query_builder_open_open").attr("disabled", false); |
| 585 |
jQuery("#wpda_query_builder_open_openall").attr("disabled", false); |
| 586 |
jQuery("#wpda_query_builder_open_delete").attr("disabled", false); |
| 587 |
} else { |
| 588 |
jQuery("#wpda_query_builder_open_select") |
| 589 |
.append( |
| 590 |
jQuery("<option/>", { |
| 591 |
value: "", |
| 592 |
text: "Nothing found..." |
| 593 |
}) |
| 594 |
); |
| 595 |
jQuery("#wpda_query_builder_open_select").attr("disabled", true); |
| 596 |
jQuery("#wpda_query_builder_open_open").attr("disabled", true); |
| 597 |
jQuery("#wpda_query_builder_open_openall").attr("disabled", true); |
| 598 |
jQuery("#wpda_query_builder_open_delete").attr("disabled", true); |
| 599 |
} |
| 600 |
} |
| 601 |
).fail( |
| 602 |
function (msg) { |
| 603 |
console.log("ERROR"); |
| 604 |
console.log(msg); |
| 605 |
} |
| 606 |
); |
| 607 |
|
| 608 |
jQuery("#wpda_query_builder_open").show(); |
| 609 |
} |
| 610 |
|
| 611 |
function closeQuery() { |
| 612 |
jQuery("#wpda_query_builder_open").hide(); |
| 613 |
} |
| 614 |
|
| 615 |
function deleteQuery() { |
| 616 |
if ( confirm("Delete query? This action cannot be undone!") ) { |
| 617 |
wpda_sqlqueryname = jQuery("#wpda_query_builder_open_select").find(":selected").text(); |
| 618 |
|
| 619 |
jQuery.ajax({ |
| 620 |
method: 'POST', |
| 621 |
url: wpda_home_url + "?action=wpda_query_builder_delete_sql", |
| 622 |
data: { |
| 623 |
wpda_wpnonce: wpda_wpnonce, |
| 624 |
wpda_sqlqueryname: wpda_sqlqueryname |
| 625 |
} |
| 626 |
}).done( |
| 627 |
function (msg) { |
| 628 |
closeQuery(); |
| 629 |
} |
| 630 |
).fail( |
| 631 |
function (msg) { |
| 632 |
console.log(msg); |
| 633 |
} |
| 634 |
); |
| 635 |
} |
| 636 |
} |
| 637 |
|
| 638 |
function exportTable(exportType, tabIndex) { |
| 639 |
switch (exportType) { |
| 640 |
case "CSV": |
| 641 |
downloadCSV( |
| 642 |
jQuery("#wpda_query_builder_result_" + tabIndex + " table").html(), |
| 643 |
jQuery("#wpda_query_builder_label_value_" + tabIndex).text() + ".csv" |
| 644 |
); |
| 645 |
break; |
| 646 |
case "JSON": |
| 647 |
downloadJSON( |
| 648 |
jQuery("#wpda_query_builder_result_" + tabIndex + " table").html(), |
| 649 |
jQuery("#wpda_query_builder_label_value_" + tabIndex).text() + ".json" |
| 650 |
); |
| 651 |
break; |
| 652 |
case "XML": |
| 653 |
downloadXML( |
| 654 |
jQuery("#wpda_query_builder_result_" + tabIndex + " table").html(), |
| 655 |
jQuery("#wpda_query_builder_label_value_" + tabIndex).text() + ".xml" |
| 656 |
); |
| 657 |
} |
| 658 |
} |
| 659 |
|
| 660 |
function downloadCSV(html, fileName) { |
| 661 |
csv = []; |
| 662 |
rows = jQuery(html).find("tr"); |
| 663 |
for (i=0; i<rows.length; i++) { |
| 664 |
row = []; |
| 665 |
cols = jQuery(rows[i]).find("td, th"); |
| 666 |
for (j=0; j<cols.length; j++) { |
| 667 |
if (jQuery(cols[j]).hasClass("wpda_data_value_null")) { |
| 668 |
row.push("null"); |
| 669 |
} else if (jQuery(cols[j]).hasClass("wpda_data_type_string")) { |
| 670 |
row.push('"' + cols[j].innerText.replaceAll('"', '""') + '"'); |
| 671 |
} else { |
| 672 |
row.push(cols[j].innerText); |
| 673 |
} |
| 674 |
} |
| 675 |
csv.push(row); |
| 676 |
} |
| 677 |
downloadExport(fileName, "text/csv", encodeURIComponent(csv.join("\n"))); |
| 678 |
} |
| 679 |
|
| 680 |
function createXML(html, fileName) { |
| 681 |
headerCols = []; |
| 682 |
header = jQuery(html).find("tr th"); |
| 683 |
body = jQuery(html)[1]; |
| 684 |
bodyRows = jQuery(body).find("tr"); |
| 685 |
table = jQuery("<table/>"); |
| 686 |
for (i=0; i<header.length; i++) { |
| 687 |
headerCols.push(header[i].innerText); |
| 688 |
} |
| 689 |
for (i=0; i<bodyRows.length; i++) { |
| 690 |
bodyCols = jQuery(bodyRows[i]).find("td"); |
| 691 |
row = jQuery("<row/>"); |
| 692 |
for (j=0; j<bodyCols.length; j++) { |
| 693 |
row.append(jQuery("<" + headerCols[j] + "/>").text(bodyCols[j].innerText)); |
| 694 |
} |
| 695 |
table.append(row); |
| 696 |
} |
| 697 |
xml = jQuery("<xml/>").append(table); |
| 698 |
return jQuery.parseXML(xml[0].outerHTML); |
| 699 |
} |
| 700 |
|
| 701 |
function downloadJSON(html, fileName) { |
| 702 |
xmlDoc = createXML(html, fileName); |
| 703 |
json = jQuery.xml2json(new XMLSerializer().serializeToString(xmlDoc.documentElement)); |
| 704 |
downloadExport(fileName, "text/json", JSON.stringify(json)); |
| 705 |
} |
| 706 |
|
| 707 |
function downloadXML(html, fileName) { |
| 708 |
xmlDoc = createXML(html, fileName); |
| 709 |
downloadExport(fileName, "text/xml", new XMLSerializer().serializeToString(xmlDoc.documentElement)); |
| 710 |
} |
| 711 |
|
| 712 |
function downloadExport(fileName, mimeType, content) { |
| 713 |
download = jQuery("<a/>", { |
| 714 |
href: "data:" + mimeType + ";charset=utf-8," + content, |
| 715 |
download: fileName |
| 716 |
}).appendTo('body'); |
| 717 |
download[0].click(); |
| 718 |
download.remove(); |
| 719 |
} |
| 720 |
|
| 721 |
function unsavedChanges() { |
| 722 |
hasEdited = false; |
| 723 |
for (var tabindex in isChanged) { |
| 724 |
if (isChanged[tabindex]===true) { |
| 725 |
hasEdited = true; |
| 726 |
} |
| 727 |
} |
| 728 |
return hasEdited; |
| 729 |
} |
| 730 |
|
| 731 |
jQuery(window).on('keydown', function(event) { |
| 732 |
if ((event.ctrlKey || event.metaKey) && String.fromCharCode(event.which).toLowerCase()==='s') { |
| 733 |
if (jQuery(event.target).hasClass('CodeMirror-code')) { |
| 734 |
saveQuery(jQuery(event.target).closest(".wpda_query_builder").data("id")) |
| 735 |
event.preventDefault(); |
| 736 |
} |
| 737 |
} |
| 738 |
}); |
| 739 |
|
| 740 |
jQuery(window).on('beforeunload', function() { |
| 741 |
if (unsavedChanges()) { |
| 742 |
return 'Your changes will not be saved! Are you sure you want to leave this page?'; |
| 743 |
} |
| 744 |
}); |
| 745 |
|
| 746 |
|
