-
Notifications
You must be signed in to change notification settings - Fork 0
/
dolisql.html
120 lines (111 loc) · 3.59 KB
/
dolisql.html
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
<html>
<head>
<meta charset="utf-8">
<title>SQL to PHP</title>
<style>
textarea {
width: 100%;
height: 35vh;
}
h2 {
font-size: 60%;
font-family: Sans-Serif;
}
pre {
border: solid 1px #b6b6b6;
padding: 5px;
}
</style>
</head>
<body>
<h2>Paste your SQL query here</h2>
<textarea id="ta1"></textarea>
<h2>Copy this and paste it in your PHP code for Dolibarr</h2>
<textarea id="ta2"></textarea>
<details>
<summary>HTML Markup</summary>
<h2>Copy this and paste it in a text input that allows simple HTML markup</h2>
<textarea id="ta3"></textarea>
<h2>Preview of the HTML markup</h2>
<div id="d4"></div>
</details>
</body>
<script type="text/javascript">
let SQLKeywordRegexp = new RegExp(
'\\b(SELECT|UPDATE|SET|UPDATE|INSERT INTO'
+ '|ORDER BY|DROP|FROM|WHERE|AND|OR|AS|NOT|IS NULL'
+ '|(?:LEFT |RIGHT |INNER |OUTER )?JOIN|ON|GROUP BY|LIMIT'
+ '|DISTINCT'
+ '|CURDATE|INTERVAL|SUM|COUNT|REGEX|LIKE|IN|COALESCE)\\b',
'gi'
);
function upperCaseSQLKeywords(sql) {
return sql.replace(SQLKeywordRegexp, (g) => g.toUpperCase());
}
/**
*
* @param {string} txt
* @param {boolean} usedoublequotes
* @param {string} dbprefix
* @returns {string}
*/
function prepareForDolibarrPHP(txt, usedoublequotes = true, dbprefix = '$db->prefix()') {
txt = txt.replace(/\\/g, '\\\\');
if (usedoublequotes) {
txt = txt.replace(/"/g, '\\"');
} else {
txt = txt.replace(/'/g, '\\\'');
}
txt = txt.replace(/llx_/g, usedoublequotes ? `" . ${dbprefix} . "` : `' . ${dbprefix} . '`);
return txt;
}
/**
* Returns the SQL query wrapped in quotes, where 'llx_' is replaced
* with $db->prefix(), single quotes are escaped etc.
* @param {string} sql
* @param {boolean} usedoublequotes
* @param {string} dbprefix
* @returns {string}
*/
function makePHPSQL(sql, usedoublequotes = true, dbprefix = '$db->prefix()') {
sql = sql.replace(/\r\n/g, '\n');
sql = upperCaseSQLKeywords(sql);
lines = sql.split('\n');
lines = lines.map(function (line, i) {
line = prepareForDolibarrPHP(line, usedoublequotes, dbprefix);
if (!i) {
return usedoublequotes ?
`$sql = /** @lang SQL */\n "${line}"` :
`$sql = /** @lang SQL */\n '${line}'`;
} else {
return usedoublequotes ?
` . " ${line}"` :
` . ' ${line}'`;
}
});
return lines.join('\n') + ';';
}
/**
* Returns the SQL query wrapped in some HTML markup for pasting
* @param sql
* @returns {string}
*/
function wrapSQLInHTML(sql) {
let boldUpperCase = (group) => '<b>' + group.toUpperCase() + '</b>';
sql = sql.replace(/\r\n/g, '\n');
sql = '<pre>' + sql.replace(SQLKeywordRegexp, boldUpperCase) + '</pre>';
return sql;
}
window.addEventListener('load', function () {
let ta = document.getElementById('ta1');
let ta2 = document.getElementById('ta2');
let ta3 = document.getElementById('ta3');
setInterval(function () {
ta2.value = makePHPSQL(ta.value);
let mantisCode = wrapSQLInHTML(ta.value);
ta3.value = mantisCode;
document.getElementById('d4').innerHTML = mantisCode;
}, 500);
});
</script>
</html>