From 6b754c279d149e91162f19c3b60a185721bdc9a3 Mon Sep 17 00:00:00 2001 From: Jacob O'Mara Date: Mon, 23 Jan 2023 16:03:20 +0000 Subject: [PATCH] Bug 32613: Add autocomplete to SQL reports editor This adds auto-complete for the sql reports editor codemirror instance. To prevent a regression in syntax highlighting the overlay mode 'sqlPlaceholders' has been removed and replaced with a highlighting engine that works correctly with the autocomplete engine. Test Plan: 1. Navigate to reports and create a new sql report 2. Write an Sql query and observe that there is no autocomplete options 3. Apply patch 4. Write a new Sql query and observe that there are now auto-complete options that can be navigated through with the arrow keys and accepted with either tab or the enter keys. 5. Ensure that items bounded in << >> or [[ ]] are still syntax highlighted post-patch --- .../lib/codemirror/highlight.css | 6 + .../intranet-tmpl/lib/codemirror/highlight.js | 24 + .../lib/codemirror/show-hint.css | 36 ++ .../intranet-tmpl/lib/codemirror/show-hint.js | 479 ++++++++++++++++++ .../intranet-tmpl/lib/codemirror/sql-hint.js | 304 +++++++++++ .../modules/reports/guided_reports_start.tt | 121 +++-- 6 files changed, 932 insertions(+), 38 deletions(-) create mode 100644 koha-tmpl/intranet-tmpl/lib/codemirror/highlight.css create mode 100644 koha-tmpl/intranet-tmpl/lib/codemirror/highlight.js create mode 100644 koha-tmpl/intranet-tmpl/lib/codemirror/show-hint.css create mode 100644 koha-tmpl/intranet-tmpl/lib/codemirror/show-hint.js create mode 100644 koha-tmpl/intranet-tmpl/lib/codemirror/sql-hint.js diff --git a/koha-tmpl/intranet-tmpl/lib/codemirror/highlight.css b/koha-tmpl/intranet-tmpl/lib/codemirror/highlight.css new file mode 100644 index 0000000000..1cfb3c0431 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/lib/codemirror/highlight.css @@ -0,0 +1,6 @@ +.cm-style1 { + color: maroon !important; +} +.cm-style2 { + color: limegreen !important; +} diff --git a/koha-tmpl/intranet-tmpl/lib/codemirror/highlight.js b/koha-tmpl/intranet-tmpl/lib/codemirror/highlight.js new file mode 100644 index 0000000000..2fa61d5ce5 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/lib/codemirror/highlight.js @@ -0,0 +1,24 @@ +(function(mod) { + if (typeof exports == "object" && typeof module == "object") // CommonJS + mod(require("../../lib/codemirror")); + else if (typeof define == "function" && define.amd) // AMD + define(["../../lib/codemirror"], mod); + else // Plain browser env + mod(CodeMirror); +})(function(CodeMirror) { + CodeMirror.defineOption("keyword", {}, function(cm, val, prev) { + if (prev == CodeMirror.Init) prev = false; + if (prev && !val) + cm.removeOverlay("keyword"); + else if (!prev && val) + cm.addOverlay({ + token: function(stream) { + for (var key in cm.options.keyword) { + if (stream.match(new RegExp(key))) return cm.options.keyword[key]; + } + stream.next(); + }, + name: "keyword" + }); + }); +}); diff --git a/koha-tmpl/intranet-tmpl/lib/codemirror/show-hint.css b/koha-tmpl/intranet-tmpl/lib/codemirror/show-hint.css new file mode 100644 index 0000000000..5617ccca2b --- /dev/null +++ b/koha-tmpl/intranet-tmpl/lib/codemirror/show-hint.css @@ -0,0 +1,36 @@ +.CodeMirror-hints { + position: absolute; + z-index: 10; + overflow: hidden; + list-style: none; + + margin: 0; + padding: 2px; + + -webkit-box-shadow: 2px 3px 5px rgba(0,0,0,.2); + -moz-box-shadow: 2px 3px 5px rgba(0,0,0,.2); + box-shadow: 2px 3px 5px rgba(0,0,0,.2); + border-radius: 3px; + border: 1px solid silver; + + background: white; + font-size: 90%; + font-family: monospace; + + max-height: 20em; + overflow-y: auto; +} + +.CodeMirror-hint { + margin: 0; + padding: 0 4px; + border-radius: 2px; + white-space: pre; + color: black; + cursor: pointer; +} + +li.CodeMirror-hint-active { + background: #08f; + color: white; +} diff --git a/koha-tmpl/intranet-tmpl/lib/codemirror/show-hint.js b/koha-tmpl/intranet-tmpl/lib/codemirror/show-hint.js new file mode 100644 index 0000000000..cd0d6a7bd5 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/lib/codemirror/show-hint.js @@ -0,0 +1,479 @@ +// CodeMirror, copyright (c) by Marijn Haverbeke and others +// Distributed under an MIT license: https://codemirror.net/LICENSE + +(function(mod) { + if (typeof exports == "object" && typeof module == "object") // CommonJS + mod(require("../../lib/codemirror")); + else if (typeof define == "function" && define.amd) // AMD + define(["../../lib/codemirror"], mod); + else // Plain browser env + mod(CodeMirror); +})(function(CodeMirror) { + "use strict"; + + var HINT_ELEMENT_CLASS = "CodeMirror-hint"; + var ACTIVE_HINT_ELEMENT_CLASS = "CodeMirror-hint-active"; + + // This is the old interface, kept around for now to stay + // backwards-compatible. + CodeMirror.showHint = function(cm, getHints, options) { + if (!getHints) return cm.showHint(options); + if (options && options.async) getHints.async = true; + var newOpts = {hint: getHints}; + if (options) for (var prop in options) newOpts[prop] = options[prop]; + return cm.showHint(newOpts); + }; + + CodeMirror.defineExtension("showHint", function(options) { + options = parseOptions(this, this.getCursor("start"), options); + var selections = this.listSelections() + if (selections.length > 1) return; + // By default, don't allow completion when something is selected. + // A hint function can have a `supportsSelection` property to + // indicate that it can handle selections. + if (this.somethingSelected()) { + if (!options.hint.supportsSelection) return; + // Don't try with cross-line selections + for (var i = 0; i < selections.length; i++) + if (selections[i].head.line != selections[i].anchor.line) return; + } + + if (this.state.completionActive) this.state.completionActive.close(); + var completion = this.state.completionActive = new Completion(this, options); + if (!completion.options.hint) return; + + CodeMirror.signal(this, "startCompletion", this); + completion.update(true); + }); + + CodeMirror.defineExtension("closeHint", function() { + if (this.state.completionActive) this.state.completionActive.close() + }) + + function Completion(cm, options) { + this.cm = cm; + this.options = options; + this.widget = null; + this.debounce = 0; + this.tick = 0; + this.startPos = this.cm.getCursor("start"); + this.startLen = this.cm.getLine(this.startPos.line).length - this.cm.getSelection().length; + + var self = this; + cm.on("cursorActivity", this.activityFunc = function() { self.cursorActivity(); }); + } + + var requestAnimationFrame = window.requestAnimationFrame || function(fn) { + return setTimeout(fn, 1000/60); + }; + var cancelAnimationFrame = window.cancelAnimationFrame || clearTimeout; + + Completion.prototype = { + close: function() { + if (!this.active()) return; + this.cm.state.completionActive = null; + this.tick = null; + this.cm.off("cursorActivity", this.activityFunc); + + if (this.widget && this.data) CodeMirror.signal(this.data, "close"); + if (this.widget) this.widget.close(); + CodeMirror.signal(this.cm, "endCompletion", this.cm); + }, + + active: function() { + return this.cm.state.completionActive == this; + }, + + pick: function(data, i) { + var completion = data.list[i], self = this; + this.cm.operation(function() { + if (completion.hint) + completion.hint(self.cm, data, completion); + else + self.cm.replaceRange(getText(completion), completion.from || data.from, + completion.to || data.to, "complete"); + CodeMirror.signal(data, "pick", completion); + self.cm.scrollIntoView(); + }) + this.close(); + }, + + cursorActivity: function() { + if (this.debounce) { + cancelAnimationFrame(this.debounce); + this.debounce = 0; + } + + var identStart = this.startPos; + if(this.data) { + identStart = this.data.from; + } + + var pos = this.cm.getCursor(), line = this.cm.getLine(pos.line); + if (pos.line != this.startPos.line || line.length - pos.ch != this.startLen - this.startPos.ch || + pos.ch < identStart.ch || this.cm.somethingSelected() || + (!pos.ch || this.options.closeCharacters.test(line.charAt(pos.ch - 1)))) { + this.close(); + } else { + var self = this; + this.debounce = requestAnimationFrame(function() {self.update();}); + if (this.widget) this.widget.disable(); + } + }, + + update: function(first) { + if (this.tick == null) return + var self = this, myTick = ++this.tick + fetchHints(this.options.hint, this.cm, this.options, function(data) { + if (self.tick == myTick) self.finishUpdate(data, first) + }) + }, + + finishUpdate: function(data, first) { + if (this.data) CodeMirror.signal(this.data, "update"); + + var picked = (this.widget && this.widget.picked) || (first && this.options.completeSingle); + if (this.widget) this.widget.close(); + + this.data = data; + + if (data && data.list.length) { + if (picked && data.list.length == 1) { + this.pick(data, 0); + } else { + this.widget = new Widget(this, data); + CodeMirror.signal(data, "shown"); + } + } + } + }; + + function parseOptions(cm, pos, options) { + var editor = cm.options.hintOptions; + var out = {}; + for (var prop in defaultOptions) out[prop] = defaultOptions[prop]; + if (editor) for (var prop in editor) + if (editor[prop] !== undefined) out[prop] = editor[prop]; + if (options) for (var prop in options) + if (options[prop] !== undefined) out[prop] = options[prop]; + if (out.hint.resolve) out.hint = out.hint.resolve(cm, pos) + return out; + } + + function getText(completion) { + if (typeof completion == "string") return completion; + else return completion.text; + } + + function buildKeyMap(completion, handle) { + var baseMap = { + Up: function() {handle.moveFocus(-1);}, + Down: function() {handle.moveFocus(1);}, + PageUp: function() {handle.moveFocus(-handle.menuSize() + 1, true);}, + PageDown: function() {handle.moveFocus(handle.menuSize() - 1, true);}, + Home: function() {handle.setFocus(0);}, + End: function() {handle.setFocus(handle.length - 1);}, + Enter: handle.pick, + Tab: handle.pick, + Esc: handle.close + }; + + var mac = /Mac/.test(navigator.platform); + + if (mac) { + baseMap["Ctrl-P"] = function() {handle.moveFocus(-1);}; + baseMap["Ctrl-N"] = function() {handle.moveFocus(1);}; + } + + var custom = completion.options.customKeys; + var ourMap = custom ? {} : baseMap; + function addBinding(key, val) { + var bound; + if (typeof val != "string") + bound = function(cm) { return val(cm, handle); }; + // This mechanism is deprecated + else if (baseMap.hasOwnProperty(val)) + bound = baseMap[val]; + else + bound = val; + ourMap[key] = bound; + } + if (custom) + for (var key in custom) if (custom.hasOwnProperty(key)) + addBinding(key, custom[key]); + var extra = completion.options.extraKeys; + if (extra) + for (var key in extra) if (extra.hasOwnProperty(key)) + addBinding(key, extra[key]); + return ourMap; + } + + function getHintElement(hintsElement, el) { + while (el && el != hintsElement) { + if (el.nodeName.toUpperCase() === "LI" && el.parentNode == hintsElement) return el; + el = el.parentNode; + } + } + + function Widget(completion, data) { + this.completion = completion; + this.data = data; + this.picked = false; + var widget = this, cm = completion.cm; + var ownerDocument = cm.getInputField().ownerDocument; + var parentWindow = ownerDocument.defaultView || ownerDocument.parentWindow; + + var hints = this.hints = ownerDocument.createElement("ul"); + var theme = completion.cm.options.theme; + hints.className = "CodeMirror-hints " + theme; + this.selectedHint = data.selectedHint || 0; + + var completions = data.list; + for (var i = 0; i < completions.length; ++i) { + var elt = hints.appendChild(ownerDocument.createElement("li")), cur = completions[i]; + var className = HINT_ELEMENT_CLASS + (i != this.selectedHint ? "" : " " + ACTIVE_HINT_ELEMENT_CLASS); + if (cur.className != null) className = cur.className + " " + className; + elt.className = className; + if (cur.render) cur.render(elt, data, cur); + else elt.appendChild(ownerDocument.createTextNode(cur.displayText || getText(cur))); + elt.hintId = i; + } + + var container = completion.options.container || ownerDocument.body; + var pos = cm.cursorCoords(completion.options.alignWithWord ? data.from : null); + var left = pos.left, top = pos.bottom, below = true; + var offsetLeft = 0, offsetTop = 0; + if (container !== ownerDocument.body) { + // We offset the cursor position because left and top are relative to the offsetParent's top left corner. + var isContainerPositioned = ['absolute', 'relative', 'fixed'].indexOf(parentWindow.getComputedStyle(container).position) !== -1; + var offsetParent = isContainerPositioned ? container : container.offsetParent; + var offsetParentPosition = offsetParent.getBoundingClientRect(); + var bodyPosition = ownerDocument.body.getBoundingClientRect(); + offsetLeft = (offsetParentPosition.left - bodyPosition.left - offsetParent.scrollLeft); + offsetTop = (offsetParentPosition.top - bodyPosition.top - offsetParent.scrollTop); + } + hints.style.left = (left - offsetLeft) + "px"; + hints.style.top = (top - offsetTop) + "px"; + + // If we're at the edge of the screen, then we want the menu to appear on the left of the cursor. + var winW = parentWindow.innerWidth || Math.max(ownerDocument.body.offsetWidth, ownerDocument.documentElement.offsetWidth); + var winH = parentWindow.innerHeight || Math.max(ownerDocument.body.offsetHeight, ownerDocument.documentElement.offsetHeight); + container.appendChild(hints); + var box = hints.getBoundingClientRect(), overlapY = box.bottom - winH; + var scrolls = hints.scrollHeight > hints.clientHeight + 1 + var startScroll = cm.getScrollInfo(); + + if (overlapY > 0) { + var height = box.bottom - box.top, curTop = pos.top - (pos.bottom - box.top); + if (curTop - height > 0) { // Fits above cursor + hints.style.top = (top = pos.top - height - offsetTop) + "px"; + below = false; + } else if (height > winH) { + hints.style.height = (winH - 5) + "px"; + hints.style.top = (top = pos.bottom - box.top - offsetTop) + "px"; + var cursor = cm.getCursor(); + if (data.from.ch != cursor.ch) { + pos = cm.cursorCoords(cursor); + hints.style.left = (left = pos.left - offsetLeft) + "px"; + box = hints.getBoundingClientRect(); + } + } + } + var overlapX = box.right - winW; + if (overlapX > 0) { + if (box.right - box.left > winW) { + hints.style.width = (winW - 5) + "px"; + overlapX -= (box.right - box.left) - winW; + } + hints.style.left = (left = pos.left - overlapX - offsetLeft) + "px"; + } + if (scrolls) for (var node = hints.firstChild; node; node = node.nextSibling) + node.style.paddingRight = cm.display.nativeBarWidth + "px" + + cm.addKeyMap(this.keyMap = buildKeyMap(completion, { + moveFocus: function(n, avoidWrap) { widget.changeActive(widget.selectedHint + n, avoidWrap); }, + setFocus: function(n) { widget.changeActive(n); }, + menuSize: function() { return widget.screenAmount(); }, + length: completions.length, + close: function() { completion.close(); }, + pick: function() { widget.pick(); }, + data: data + })); + + if (completion.options.closeOnUnfocus) { + var closingOnBlur; + cm.on("blur", this.onBlur = function() { closingOnBlur = setTimeout(function() { completion.close(); }, 100); }); + cm.on("focus", this.onFocus = function() { clearTimeout(closingOnBlur); }); + } + + cm.on("scroll", this.onScroll = function() { + var curScroll = cm.getScrollInfo(), editor = cm.getWrapperElement().getBoundingClientRect(); + var newTop = top + startScroll.top - curScroll.top; + var point = newTop - (parentWindow.pageYOffset || (ownerDocument.documentElement || ownerDocument.body).scrollTop); + if (!below) point += hints.offsetHeight; + if (point <= editor.top || point >= editor.bottom) return completion.close(); + hints.style.top = newTop + "px"; + hints.style.left = (left + startScroll.left - curScroll.left) + "px"; + }); + + CodeMirror.on(hints, "dblclick", function(e) { + var t = getHintElement(hints, e.target || e.srcElement); + if (t && t.hintId != null) {widget.changeActive(t.hintId); widget.pick();} + }); + + CodeMirror.on(hints, "click", function(e) { + var t = getHintElement(hints, e.target || e.srcElement); + if (t && t.hintId != null) { + widget.changeActive(t.hintId); + if (completion.options.completeOnSingleClick) widget.pick(); + } + }); + + CodeMirror.on(hints, "mousedown", function() { + setTimeout(function(){cm.focus();}, 20); + }); + this.scrollToActive() + + CodeMirror.signal(data, "select", completions[this.selectedHint], hints.childNodes[this.selectedHint]); + return true; + } + + Widget.prototype = { + close: function() { + if (this.completion.widget != this) return; + this.completion.widget = null; + this.hints.parentNode.removeChild(this.hints); + this.completion.cm.removeKeyMap(this.keyMap); + + var cm = this.completion.cm; + if (this.completion.options.closeOnUnfocus) { + cm.off("blur", this.onBlur); + cm.off("focus", this.onFocus); + } + cm.off("scroll", this.onScroll); + }, + + disable: function() { + this.completion.cm.removeKeyMap(this.keyMap); + var widget = this; + this.keyMap = {Enter: function() { widget.picked = true; }}; + this.completion.cm.addKeyMap(this.keyMap); + }, + + pick: function() { + this.completion.pick(this.data, this.selectedHint); + }, + + changeActive: function(i, avoidWrap) { + if (i >= this.data.list.length) + i = avoidWrap ? this.data.list.length - 1 : 0; + else if (i < 0) + i = avoidWrap ? 0 : this.data.list.length - 1; + if (this.selectedHint == i) return; + var node = this.hints.childNodes[this.selectedHint]; + if (node) node.className = node.className.replace(" " + ACTIVE_HINT_ELEMENT_CLASS, ""); + node = this.hints.childNodes[this.selectedHint = i]; + node.className += " " + ACTIVE_HINT_ELEMENT_CLASS; + this.scrollToActive() + CodeMirror.signal(this.data, "select", this.data.list[this.selectedHint], node); + }, + + scrollToActive: function() { + var margin = this.completion.options.scrollMargin || 0; + var node1 = this.hints.childNodes[Math.max(0, this.selectedHint - margin)]; + var node2 = this.hints.childNodes[Math.min(this.data.list.length - 1, this.selectedHint + margin)]; + var firstNode = this.hints.firstChild; + if (node1.offsetTop < this.hints.scrollTop) + this.hints.scrollTop = node1.offsetTop - firstNode.offsetTop; + else if (node2.offsetTop + node2.offsetHeight > this.hints.scrollTop + this.hints.clientHeight) + this.hints.scrollTop = node2.offsetTop + node2.offsetHeight - this.hints.clientHeight + firstNode.offsetTop; + }, + + screenAmount: function() { + return Math.floor(this.hints.clientHeight / this.hints.firstChild.offsetHeight) || 1; + } + }; + + function applicableHelpers(cm, helpers) { + if (!cm.somethingSelected()) return helpers + var result = [] + for (var i = 0; i < helpers.length; i++) + if (helpers[i].supportsSelection) result.push(helpers[i]) + return result + } + + function fetchHints(hint, cm, options, callback) { + if (hint.async) { + hint(cm, callback, options) + } else { + var result = hint(cm, options) + if (result && result.then) result.then(callback) + else callback(result) + } + } + + function resolveAutoHints(cm, pos) { + var helpers = cm.getHelpers(pos, "hint"), words + if (helpers.length) { + var resolved = function(cm, callback, options) { + var app = applicableHelpers(cm, helpers); + function run(i) { + if (i == app.length) return callback(null) + fetchHints(app[i], cm, options, function(result) { + if (result && result.list.length > 0) callback(result) + else run(i + 1) + }) + } + run(0) + } + resolved.async = true + resolved.supportsSelection = true + return resolved + } else if (words = cm.getHelper(cm.getCursor(), "hintWords")) { + return function(cm) { return CodeMirror.hint.fromList(cm, {words: words}) } + } else if (CodeMirror.hint.anyword) { + return function(cm, options) { return CodeMirror.hint.anyword(cm, options) } + } else { + return function() {} + } + } + + CodeMirror.registerHelper("hint", "auto", { + resolve: resolveAutoHints + }); + + CodeMirror.registerHelper("hint", "fromList", function(cm, options) { + var cur = cm.getCursor(), token = cm.getTokenAt(cur) + var term, from = CodeMirror.Pos(cur.line, token.start), to = cur + if (token.start < cur.ch && /\w/.test(token.string.charAt(cur.ch - token.start - 1))) { + term = token.string.substr(0, cur.ch - token.start) + } else { + term = "" + from = cur + } + var found = []; + for (var i = 0; i < options.words.length; i++) { + var word = options.words[i]; + if (word.slice(0, term.length) == term) + found.push(word); + } + + if (found.length) return {list: found, from: from, to: to}; + }); + + CodeMirror.commands.autocomplete = CodeMirror.showHint; + + var defaultOptions = { + hint: CodeMirror.hint.auto, + completeSingle: true, + alignWithWord: true, + closeCharacters: /[\s()\[\]{};:>,]/, + closeOnUnfocus: true, + completeOnSingleClick: true, + container: null, + customKeys: null, + extraKeys: null + }; + + CodeMirror.defineOption("hintOptions", null); +}); diff --git a/koha-tmpl/intranet-tmpl/lib/codemirror/sql-hint.js b/koha-tmpl/intranet-tmpl/lib/codemirror/sql-hint.js new file mode 100644 index 0000000000..de84707db3 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/lib/codemirror/sql-hint.js @@ -0,0 +1,304 @@ +// CodeMirror, copyright (c) by Marijn Haverbeke and others +// Distributed under an MIT license: https://codemirror.net/LICENSE + +(function(mod) { + if (typeof exports == "object" && typeof module == "object") // CommonJS + mod(require("../../lib/codemirror"), require("../../mode/sql/sql")); + else if (typeof define == "function" && define.amd) // AMD + define(["../../lib/codemirror", "../../mode/sql/sql"], mod); + else // Plain browser env + mod(CodeMirror); +})(function(CodeMirror) { + "use strict"; + + var tables; + var defaultTable; + var keywords; + var identifierQuote; + var CONS = { + QUERY_DIV: ";", + ALIAS_KEYWORD: "AS" + }; + var Pos = CodeMirror.Pos, cmpPos = CodeMirror.cmpPos; + + function isArray(val) { return Object.prototype.toString.call(val) == "[object Array]" } + + function getKeywords(editor) { + var mode = editor.doc.modeOption; + if (mode === "sql") mode = "text/x-sql"; + return CodeMirror.resolveMode(mode).keywords; + } + + function getIdentifierQuote(editor) { + var mode = editor.doc.modeOption; + if (mode === "sql") mode = "text/x-sql"; + return CodeMirror.resolveMode(mode).identifierQuote || "`"; + } + + function getText(item) { + return typeof item == "string" ? item : item.text; + } + + function wrapTable(name, value) { + if (isArray(value)) value = {columns: value} + if (!value.text) value.text = name + return value + } + + function parseTables(input) { + var result = {} + if (isArray(input)) { + for (var i = input.length - 1; i >= 0; i--) { + var item = input[i] + result[getText(item).toUpperCase()] = wrapTable(getText(item), item) + } + } else if (input) { + for (var name in input) + result[name.toUpperCase()] = wrapTable(name, input[name]) + } + return result + } + + function getTable(name) { + return tables[name.toUpperCase()] + } + + function shallowClone(object) { + var result = {}; + for (var key in object) if (object.hasOwnProperty(key)) + result[key] = object[key]; + return result; + } + + function match(string, word) { + var len = string.length; + var sub = getText(word).substr(0, len); + return string.toUpperCase() === sub.toUpperCase(); + } + + function addMatches(result, search, wordlist, formatter) { + if (isArray(wordlist)) { + for (var i = 0; i < wordlist.length; i++) + if (match(search, wordlist[i])) result.push(formatter(wordlist[i])) + } else { + for (var word in wordlist) if (wordlist.hasOwnProperty(word)) { + var val = wordlist[word] + if (!val || val === true) + val = word + else + val = val.displayText ? {text: val.text, displayText: val.displayText} : val.text + if (match(search, val)) result.push(formatter(val)) + } + } + } + + function cleanName(name) { + // Get rid name from identifierQuote and preceding dot(.) + if (name.charAt(0) == ".") { + name = name.substr(1); + } + // replace doublicated identifierQuotes with single identifierQuotes + // and remove single identifierQuotes + var nameParts = name.split(identifierQuote+identifierQuote); + for (var i = 0; i < nameParts.length; i++) + nameParts[i] = nameParts[i].replace(new RegExp(identifierQuote,"g"), ""); + return nameParts.join(identifierQuote); + } + + function insertIdentifierQuotes(name) { + var nameParts = getText(name).split("."); + for (var i = 0; i < nameParts.length; i++) + nameParts[i] = identifierQuote + + // doublicate identifierQuotes + nameParts[i].replace(new RegExp(identifierQuote,"g"), identifierQuote+identifierQuote) + + identifierQuote; + var escaped = nameParts.join("."); + if (typeof name == "string") return escaped; + name = shallowClone(name); + name.text = escaped; + return name; + } + + function nameCompletion(cur, token, result, editor) { + // Try to complete table, column names and return start position of completion + var useIdentifierQuotes = false; + var nameParts = []; + var start = token.start; + var cont = true; + while (cont) { + cont = (token.string.charAt(0) == "."); + useIdentifierQuotes = useIdentifierQuotes || (token.string.charAt(0) == identifierQuote); + + start = token.start; + nameParts.unshift(cleanName(token.string)); + + token = editor.getTokenAt(Pos(cur.line, token.start)); + if (token.string == ".") { + cont = true; + token = editor.getTokenAt(Pos(cur.line, token.start)); + } + } + + // Try to complete table names + var string = nameParts.join("."); + addMatches(result, string, tables, function(w) { + return useIdentifierQuotes ? insertIdentifierQuotes(w) : w; + }); + + // Try to complete columns from defaultTable + addMatches(result, string, defaultTable, function(w) { + return useIdentifierQuotes ? insertIdentifierQuotes(w) : w; + }); + + // Try to complete columns + string = nameParts.pop(); + var table = nameParts.join("."); + + var alias = false; + var aliasTable = table; + // Check if table is available. If not, find table by Alias + if (!getTable(table)) { + var oldTable = table; + table = findTableByAlias(table, editor); + if (table !== oldTable) alias = true; + } + + var columns = getTable(table); + if (columns && columns.columns) + columns = columns.columns; + + if (columns) { + addMatches(result, string, columns, function(w) { + var tableInsert = table; + if (alias == true) tableInsert = aliasTable; + if (typeof w == "string") { + w = tableInsert + "." + w; + } else { + w = shallowClone(w); + w.text = tableInsert + "." + w.text; + } + return useIdentifierQuotes ? insertIdentifierQuotes(w) : w; + }); + } + + return start; + } + + function eachWord(lineText, f) { + var words = lineText.split(/\s+/) + for (var i = 0; i < words.length; i++) + if (words[i]) f(words[i].replace(/[,;]/g, '')) + } + + function findTableByAlias(alias, editor) { + var doc = editor.doc; + var fullQuery = doc.getValue(); + var aliasUpperCase = alias.toUpperCase(); + var previousWord = ""; + var table = ""; + var separator = []; + var validRange = { + start: Pos(0, 0), + end: Pos(editor.lastLine(), editor.getLineHandle(editor.lastLine()).length) + }; + + //add separator + var indexOfSeparator = fullQuery.indexOf(CONS.QUERY_DIV); + while(indexOfSeparator != -1) { + separator.push(doc.posFromIndex(indexOfSeparator)); + indexOfSeparator = fullQuery.indexOf(CONS.QUERY_DIV, indexOfSeparator+1); + } + separator.unshift(Pos(0, 0)); + separator.push(Pos(editor.lastLine(), editor.getLineHandle(editor.lastLine()).text.length)); + + //find valid range + var prevItem = null; + var current = editor.getCursor() + for (var i = 0; i < separator.length; i++) { + if ((prevItem == null || cmpPos(current, prevItem) > 0) && cmpPos(current, separator[i]) <= 0) { + validRange = {start: prevItem, end: separator[i]}; + break; + } + prevItem = separator[i]; + } + + if (validRange.start) { + var query = doc.getRange(validRange.start, validRange.end, false); + + for (var i = 0; i < query.length; i++) { + var lineText = query[i]; + eachWord(lineText, function(word) { + var wordUpperCase = word.toUpperCase(); + if (wordUpperCase === aliasUpperCase && getTable(previousWord)) + table = previousWord; + if (wordUpperCase !== CONS.ALIAS_KEYWORD) + previousWord = word; + }); + if (table) break; + } + } + return table; + } + + CodeMirror.registerHelper("hint", "sql", function(editor, options) { + tables = parseTables(options && options.tables) + var defaultTableName = options && options.defaultTable; + var disableKeywords = options && options.disableKeywords; + defaultTable = defaultTableName && getTable(defaultTableName); + keywords = getKeywords(editor); + identifierQuote = getIdentifierQuote(editor); + + if (defaultTableName && !defaultTable) + defaultTable = findTableByAlias(defaultTableName, editor); + + defaultTable = defaultTable || []; + + if (defaultTable.columns) + defaultTable = defaultTable.columns; + + var cur = editor.getCursor(); + var result = []; + var token = editor.getTokenAt(cur), start, end, search; + if (token.end > cur.ch) { + token.end = cur.ch; + token.string = token.string.slice(0, cur.ch - token.start); + } + + if (token.string.match(/^[.`"'\w@][\w$#]*$/g)) { + search = token.string; + start = token.start; + end = token.end; + } else { + start = end = cur.ch; + search = ""; + } + if (search.charAt(0) == "." || search.charAt(0) == identifierQuote) { + start = nameCompletion(cur, token, result, editor); + } else { + var objectOrClass = function(w, className) { + if (typeof w === "object") { + w.className = className; + } else { + w = { text: w, className: className }; + } + return w; + }; + addMatches(result, search, defaultTable, function(w) { + return objectOrClass(w, "CodeMirror-hint-table CodeMirror-hint-default-table"); + }); + addMatches( + result, + search, + tables, function(w) { + return objectOrClass(w, "CodeMirror-hint-table"); + } + ); + if (!disableKeywords) + addMatches(result, search, keywords, function(w) { + return objectOrClass(w.toUpperCase(), "CodeMirror-hint-keyword"); + }); + } + + return {list: result, from: Pos(cur.line, start), to: Pos(cur.line, end)}; + }); +}); diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt index e1247b0420..997fb25a80 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt @@ -7,6 +7,7 @@ [% USE JSON.Escape %] [% PROCESS 'i18n.inc' %] [% SET footerjs = 1 %] +[% USE To %] [%- BLOCK area_name -%] [%- SWITCH area -%] @@ -1259,7 +1260,7 @@ SQL:
[% PROCESS insert_runtime_parameter %] - + Required
@@ -1495,7 +1496,13 @@ [% Asset.js( "lib/codemirror/codemirror.min.js" ) | $raw %] [% Asset.js( "lib/codemirror/overlay.min.js" ) | $raw %] [% Asset.js( "lib/codemirror/sql.min.js" ) | $raw %] + [% Asset.js( "lib/codemirror/show-hint.js" ) | $raw %] + [% Asset.css("lib/codemirror/show-hint.css") | $raw %] + [% Asset.js( "lib/codemirror/sql-hint.js" ) | $raw %] + [% Asset.js( "lib/codemirror/highlight.js" ) | $raw %] + [% Asset.css("lib/codemirror/highlight.css") | $raw %] [% Asset.js( "js/mana.js" ) | $raw %] +