1 /***************************************************************************
2 * SPDX-FileCopyrightText: 2021 S. MANKOWSKI stephane@mankowski.fr
3 * SPDX-FileCopyrightText: 2021 G. DE BURE support@mankowski.fr
4 * SPDX-License-Identifier: GPL-3.0-or-later
5 ***************************************************************************/
6 /** @file
7 * This file implements classes SKGServices.
8 *
9 * @author Stephane MANKOWSKI / Guillaume DE BURE
10 */
11 #include "skgservices.h"
12
13 #include <kaboutdata.h>
14 #include <kiconloader.h>
15 #include <kio/filecopyjob.h>
16 #include <klocalizedstring.h>
17 #ifndef Q_OS_WIN
18 #include <sys/time.h>
19 #endif
20
21 #include <qapplication.h>
22 #include <qca.h>
23 #include <qdom.h>
24 #include <qfile.h>
25 #include <qlocale.h>
26 #include <qmath.h>
27 #include <qregularexpression.h>
28 #include <qsavefile.h>
29 #include <qscriptengine.h>
30 #include <qsqldatabase.h>
31 #include <qsqldriver.h>
32 #include <qsqlerror.h>
33 #include <qsqlquery.h>
34 #include <qsqlrecord.h>
35 #include <qstandardpaths.h>
36 #include <qtemporaryfile.h>
37 #include <qvariant.h>
38
39 #include "skgdocument.h"
40 #include "skgtraces.h"
41
42 #define SQLCIPHERHEARDER "SQLCipher format"
43
44 int SKGServices::SKGSqlTraces = (SKGServices::getEnvVariable(QStringLiteral("SKGTRACESQL")).isEmpty() ? -1 : SKGServices::stringToInt(SKGServices::getEnvVariable(QStringLiteral("SKGTRACESQL"))));
45
46 SKGError SKGServices::m_lastCallbackError;
47
searchCriteriasToWhereClause(const SKGServices::SKGSearchCriteriaList & iSearchCriterias,const QStringList & iAttributes,const SKGDocument * iDocument,bool iForDisplay)48 QString SKGServices::searchCriteriasToWhereClause(const SKGServices::SKGSearchCriteriaList& iSearchCriterias, const QStringList& iAttributes, const SKGDocument* iDocument, bool iForDisplay)
49 {
50 QString whereclause;
51 int nbCriterias = iSearchCriterias.count();
52 int nbAttributes = iAttributes.count();
53 for (int i = 0; i < nbCriterias; ++i) {
54 SKGSearchCriteria criteria = iSearchCriterias.at(i);
55 QString subWhereClause;
56
57 int nbWords = criteria.words.count();
58 for (int w = 0; w < nbWords; ++w) {
59 QString subWhereClause2;
60
61 QString word = criteria.words[w].toLower();
62 QString att;
63 QString op(':');
64 bool modeStartWith = true;
65
66 // Check if the word follows the format attribute:value
67 int pos = word.indexOf(QStringLiteral(":"));
68 int pos2 = word.indexOf(QStringLiteral("<="));
69 int pos3 = word.indexOf(QStringLiteral(">="));
70 int pos4 = word.indexOf(QStringLiteral("="));
71 int pos5 = word.indexOf(QStringLiteral("<"));
72 int pos6 = word.indexOf(QStringLiteral(">"));
73 int pos7 = word.indexOf(QStringLiteral("#"));
74 int opLength = 1;
75 if (pos2 != -1 && (pos2 < pos || pos == -1)) {
76 pos = pos2;
77 opLength = 2;
78 }
79 if (pos3 != -1 && (pos3 < pos || pos == -1)) {
80 pos = pos3;
81 opLength = 2;
82 }
83 if (pos4 != -1 && (pos4 < pos || pos == -1)) {
84 pos = pos4;
85 }
86 if (pos5 != -1 && (pos5 < pos || pos == -1)) {
87 pos = pos5;
88 }
89 if (pos6 != -1 && (pos6 < pos || pos == -1)) {
90 pos = pos6;
91 }
92 if (pos7 != -1 && (pos7 < pos || pos == -1)) {
93 pos = pos7;
94 }
95
96 if (pos != -1) {
97 att = word.left(pos);
98 if (att.endsWith(QStringLiteral("."))) {
99 modeStartWith = false;
100 att = att.left(att.count() - 1);
101 }
102 op = word.mid(pos, opLength);
103 word = word.right(word.count() - pos - op.count());
104 }
105
106 word = SKGServices::stringToSqlString(word);
107
108 for (int j = 0; j < nbAttributes; ++j) {
109 QString attDatabase = iAttributes.at(j);
110 QString attForComparison = (iDocument != nullptr ? iDocument->getDisplay(attDatabase) : attDatabase).toLower();
111 if (att.isEmpty() ||
112 (modeStartWith && attForComparison.startsWith(att)) ||
113 (!modeStartWith && attForComparison.compare(att, Qt::CaseInsensitive) == 0)) {
114 if (iForDisplay) {
115 QString n = attForComparison + op + word;
116 if (subWhereClause2.isEmpty()) {
117 subWhereClause2 = n;
118 } else {
119 subWhereClause2 = i18nc("Logical condition", "%1 or %2", subWhereClause2, n);
120 }
121 } else {
122 if (!subWhereClause2.isEmpty()) {
123 subWhereClause2 = subWhereClause2 % " OR ";
124 }
125
126 if (attDatabase.startsWith(QLatin1String("p_"))) {
127 // Case property
128 QString propName = attDatabase.right(attDatabase.length() - 2);
129 if (op == QStringLiteral(":")) {
130 subWhereClause2 = subWhereClause2 % "i_PROPPNAME='" % SKGServices::stringToSqlString(propName) % "' AND (lower(i_PROPVALUE) LIKE '%" % word % "%')";
131 } else if (op == QStringLiteral("#")) {
132 subWhereClause2 = subWhereClause2 % "i_PROPPNAME='" % SKGServices::stringToSqlString(propName) % "' AND REGEXP('" % word % "',i_PROPVALUE)";
133 } else {
134 attDatabase = "i_PROPPNAME='" % SKGServices::stringToSqlString(propName) % "' AND i_PROPVALUE";
135 subWhereClause2 = subWhereClause2 % attDatabase % op % word;
136 }
137 } else {
138 // Case normal attribute
139 if (op == QStringLiteral(":")) {
140 subWhereClause2 = subWhereClause2 % "lower(" % attDatabase % ") LIKE '%" % word % "%'";
141 } else if (op == QStringLiteral("#")) {
142 subWhereClause2 = subWhereClause2 % "REGEXP('" % word % "'," % attDatabase % ")";
143 } else {
144 if (attDatabase.startsWith(QLatin1String("f_")) || attDatabase.startsWith(QLatin1String("i_"))) {
145 subWhereClause2 = subWhereClause2 % attDatabase % op % word;
146 } else {
147 subWhereClause2 = subWhereClause2 % "lower(" % attDatabase % ")" % op % "'" % word % "'";
148 }
149 }
150 }
151 }
152 }
153 }
154 if (iForDisplay) {
155 if (!subWhereClause2.isEmpty()) {
156 if (subWhereClause.isEmpty()) {
157 subWhereClause = subWhereClause2;
158 } else {
159 subWhereClause = i18nc("Logical condition", "(%1) and (%2)", subWhereClause, subWhereClause2);
160 }
161 }
162 } else {
163 if (!subWhereClause2.isEmpty()) {
164 if (!subWhereClause.isEmpty()) {
165 subWhereClause = subWhereClause % " AND ";
166 }
167 subWhereClause = subWhereClause % "(" % subWhereClause2 % ")";
168 } else {
169 subWhereClause = QStringLiteral("1=0");
170 }
171 }
172 }
173 if (iForDisplay) {
174 if (!subWhereClause.isEmpty()) {
175 if (criteria.mode == '+') {
176 if (whereclause.isEmpty()) {
177 whereclause = subWhereClause;
178 } else {
179 whereclause = i18nc("Logical condition", "(%1) and (%2)", whereclause, subWhereClause);
180 }
181 } else if (criteria.mode == '-') {
182 if (subWhereClause.isEmpty()) {
183 whereclause = i18nc("Logical condition", "not (%1)", subWhereClause);
184 } else {
185 whereclause = i18nc("Logical condition", "(%1) and not (%2)", whereclause, subWhereClause);
186 }
187 }
188 }
189 } else {
190 if (!subWhereClause.isEmpty()) {
191 if (criteria.mode == '+') {
192 if (!whereclause.isEmpty()) {
193 whereclause = whereclause % " OR ";
194 }
195 whereclause = whereclause % "(" % subWhereClause % ")";
196 } else if (criteria.mode == '-') {
197 if (!whereclause.isEmpty()) {
198 whereclause = whereclause % " AND NOT";
199 } else {
200 whereclause = QStringLiteral("NOT");
201 }
202 whereclause = whereclause % "(" % subWhereClause % ")";
203 }
204 }
205 }
206 }
207 return whereclause;
208 }
209
stringToSearchCriterias(const QString & iString)210 SKGServices::SKGSearchCriteriaList SKGServices::stringToSearchCriterias(const QString& iString)
211 {
212 SKGServices::SKGSearchCriteriaList output;
213
214 QStringList words = SKGServices::splitCSVLine(iString, ' ', true);
215
216 int nbwords = words.count();
217 output.reserve(nbwords);
218
219 SKGServices::SKGSearchCriteria criteria;
220 criteria.mode = '+';
221 bool atLeastOnePlus = false;
222 for (int i = 0; i < nbwords; ++i) {
223 QString word = words.at(i);
224 bool isWordStartingByPlus = word.startsWith(QLatin1String("+"));
225 bool isWordStartingByLess = word.startsWith(QLatin1String("-"));
226 if (isWordStartingByPlus || isWordStartingByLess) {
227 QChar nextChar;
228 if (word.count() > 1) {
229 nextChar = word[1];
230 }
231 if (nextChar < '0' || nextChar > '9') {
232 word = word.right(word.length() - 1);
233 if (Q_LIKELY(i != 0)) {
234 if (criteria.mode == '-') {
235 output.push_back(criteria);
236 } else {
237 output.push_front(criteria);
238 atLeastOnePlus = true;
239 }
240 }
241 criteria.words.clear();
242 criteria.mode = (isWordStartingByPlus ? '+' : '-');
243 }
244 }
245 criteria.words.push_back(word);
246 }
247 if (criteria.mode == '-') {
248 output.push_back(criteria);
249 } else {
250 output.push_front(criteria);
251 atLeastOnePlus = true;
252 }
253
254 if (!atLeastOnePlus) {
255 // Add a '+' always true
256 SKGServices::SKGSearchCriteria criteria2;
257 criteria2.mode = '+';
258 criteria2.words.push_back(QLatin1String(""));
259 output.push_front(criteria2);
260 }
261
262 return output;
263 }
264
getEnvVariable(const QString & iAttribute)265 QString SKGServices::getEnvVariable(const QString& iAttribute)
266 {
267 return QString::fromUtf8(qgetenv(iAttribute.toUtf8().constData()));
268 }
269
intToString(qlonglong iNumber)270 QString SKGServices::intToString(qlonglong iNumber)
271 {
272 QString output;
273 output.setNum(iNumber);
274 return output;
275 }
276
stringToInt(const QString & iNumber)277 qlonglong SKGServices::stringToInt(const QString& iNumber)
278 {
279 if (Q_UNLIKELY(iNumber.isEmpty())) {
280 return 0;
281 }
282
283 bool ok;
284 qlonglong output = iNumber.toLongLong(&ok);
285 if (Q_LIKELY(!ok)) {
286 SKGTRACE << "WARNING: SKGServices::stringToInt(" << iNumber << ") failed" << SKGENDL;
287 }
288
289 return output;
290 }
291
stringToSqlString(const QString & iString)292 QString SKGServices::stringToSqlString(const QString& iString)
293 {
294 QString output;
295
296 for (const auto& c : iString) {
297 if (c.isPrint() || c == QChar('\n')) {
298 output.append(QChar(c));
299 }
300 }
301
302 output.replace('\'', QStringLiteral("''"));
303 return output;
304 }
305
stringToHtml(const QString & iString)306 QString SKGServices::stringToHtml(const QString& iString)
307 {
308 QString output = iString;
309 output.replace('&', QStringLiteral("&")); // Must be done first
310 output.replace('<', QStringLiteral("<"));
311 output.replace('>', QStringLiteral(">"));
312 output.replace('"', QStringLiteral("""));
313
314 return output;
315 }
316
htmlToString(const QString & iString)317 QString SKGServices::htmlToString(const QString& iString)
318 {
319 QString output = iString;
320 output.replace(QStringLiteral("<"), QStringLiteral("<"));
321 output.replace(QStringLiteral(">"), QStringLiteral(">"));
322 output.replace(QStringLiteral("""), QStringLiteral("\""));
323 output.replace(QStringLiteral("&"), QStringLiteral("&"));
324
325 return output;
326 }
327
stringsToCsv(const QStringList & iList,QChar iSeparator)328 QString SKGServices::stringsToCsv(const QStringList& iList, QChar iSeparator)
329 {
330 QString output;
331 int nb = iList.count();
332 for (int i = 0; i < nb; ++i) {
333 output.append(SKGServices::stringToCsv(iList.at(i)));
334 if (Q_LIKELY(i < nb - 1)) {
335 output.append(iSeparator);
336 }
337 }
338
339 return output;
340 }
341
stringToCsv(const QString & iNumber)342 QString SKGServices::stringToCsv(const QString& iNumber)
343 {
344 QString output = iNumber;
345 output.replace('"', QStringLiteral("#SKGDOUBLECOTE#"));
346 output.replace(QStringLiteral("#SKGDOUBLECOTE#"), QStringLiteral("\"\""));
347 output = '"' % output % '"';
348 return output;
349 }
350
stringToDouble(const QString & iNumber)351 double SKGServices::stringToDouble(const QString& iNumber)
352 {
353 if (Q_UNLIKELY(iNumber.isEmpty() || iNumber == QStringLiteral("nan"))) {
354 return 0;
355 }
356 if (Q_UNLIKELY(iNumber == QStringLiteral("inf"))) {
357 return 1e300;
358 }
359 if (Q_UNLIKELY(iNumber == QStringLiteral("-inf"))) {
360 return -1e300;
361 }
362 QString number = iNumber;
363 number.remove(QRegularExpression(QStringLiteral("[^0-9-+/eE,.]")));
364 if (number.contains(QStringLiteral("/"))) {
365 // Use script engine
366 QScriptEngine myEngine;
367 QScriptValue result = myEngine.evaluate(number);
368 if (result.isNumber()) {
369 return result.toNumber();
370 }
371 }
372
373 bool ok;
374 double output = number.toDouble(&ok);
375 if (Q_LIKELY(!ok)) {
376 QString tmp = number;
377 tmp.replace(',', '.');
378 if (tmp.count('.') > 1) {
379 tmp.remove(tmp.indexOf('.'), 1);
380 }
381 output = tmp.toDouble(&ok);
382 if (Q_LIKELY(!ok)) {
383 QString tmp2 = number;
384 tmp2.replace('.', ',');
385 if (tmp2.count(',') > 1) {
386 tmp2.remove(tmp2.indexOf(','), 1);
387 }
388 output = tmp2.toDouble(&ok);
389 if (!ok) {
390 QString tmp3 = number;
391 tmp3.remove(',');
392 output = tmp3.toDouble(&ok);
393 }
394 }
395 }
396 if (Q_LIKELY(!ok)) {
397 SKGTRACE << "WARNING: SKGServices::stringToDouble(" << iNumber << ") failed" << SKGENDL;
398 }
399 return output;
400 }
401
doubleToString(double iNumber)402 QString SKGServices::doubleToString(double iNumber)
403 {
404 QString output;
405 output.setNum(iNumber, 'g', 10);
406 return output;
407 }
408
getNextString(const QString & iString)409 QString SKGServices::getNextString(const QString& iString)
410 {
411 QString output = iString;
412 bool ok;
413 qlonglong val = output.toLongLong(&ok);
414 if (Q_LIKELY(ok)) {
415 // This is a int
416 output = SKGServices::intToString(val + 1);
417 } else {
418 // This is a string
419 output = QLatin1String("");
420 }
421 return output;
422 }
423
dateToPeriod(QDate iDate,const QString & iPeriod)424 QString SKGServices::dateToPeriod(QDate iDate, const QString& iPeriod)
425 {
426 QString period;
427 if (iPeriod == QStringLiteral("D")) {
428 // Day
429 period = iDate.toString(QStringLiteral("yyyy-MM-dd"));
430 } else if (iPeriod == QStringLiteral("W")) {
431 // Week
432 period = iDate.toString(QStringLiteral("yyyy-W")) % SKGServices::intToString(iDate.weekNumber()).rightJustified(2, '0');
433 } else if (iPeriod == QStringLiteral("M")) {
434 // Month
435 period = iDate.toString(QStringLiteral("yyyy-MM"));
436 } else if (iPeriod == QStringLiteral("Q")) {
437 // Quarter
438 period = iDate.toString(QStringLiteral("yyyy-Q")) % (iDate.month() <= 3 ? '1' : (iDate.month() <= 6 ? '2' : (iDate.month() <= 9 ? '3' : '4')));
439 } else if (iPeriod == QStringLiteral("S")) {
440 // Semester
441 period = iDate.toString(QStringLiteral("yyyy-S")) % (iDate.month() <= 6 ? '1' : '2');
442 } else if (iPeriod == QStringLiteral("Y")) {
443 // Year
444 period = iDate.toString(QStringLiteral("yyyy"));
445 }
446 return period;
447 }
448
timeToString(const QDateTime & iDateTime)449 QString SKGServices::timeToString(const QDateTime& iDateTime)
450 {
451 QDateTime d = iDateTime;
452 if (Q_UNLIKELY(!d.isValid())) {
453 d = QDateTime::currentDateTime();
454 }
455 return d.toString(QStringLiteral("yyyy-MM-dd HH:mm:ss"));
456 }
457
dateToSqlString(QDate iDate)458 QString SKGServices::dateToSqlString(QDate iDate)
459 {
460 #if QT_VERSION < QT_VERSION_CHECK(5, 14, 0)
461 return dateToSqlString(QDateTime(iDate));
462 #else
463 return dateToSqlString(iDate.startOfDay());
464 #endif
465 }
466
dateToSqlString(const QDateTime & iDateTime)467 QString SKGServices::dateToSqlString(const QDateTime& iDateTime)
468 {
469 QDateTime d = iDateTime;
470 if (Q_UNLIKELY(!d.isValid())) {
471 d = QDateTime::currentDateTime();
472 }
473 return d.toString(QStringLiteral("yyyy-MM-dd"));
474 }
475
nbWorkingDays(QDate iFrom,QDate iTo)476 int SKGServices::nbWorkingDays(QDate iFrom, QDate iTo)
477 {
478 int nb = 0;
479 QDate min = (iFrom < iTo ? iFrom : iTo);
480 QDate max = (iFrom < iTo ? iTo : iFrom);
481
482 while (min != max) {
483 if (min.dayOfWeek() <= 5) {
484 ++nb;
485 }
486 min = min.addDays(1);
487 }
488 if (nb == 0) {
489 nb = 1;
490 }
491 return nb;
492 }
493
stringToTime(const QString & iDateString)494 QDateTime SKGServices::stringToTime(const QString& iDateString)
495 {
496 QDateTime output = QDateTime::fromString(iDateString, QStringLiteral("yyyy-MM-dd HH:mm:ss"));
497 if (Q_UNLIKELY(!output.isValid())) {
498 output = QDateTime::fromString(iDateString, QStringLiteral("yyyy-MM-dd"));
499 }
500
501 return output;
502 }
503
partialStringToDate(const QString & iDateString,bool iFixupBackward)504 QDate SKGServices::partialStringToDate(const QString& iDateString, bool iFixupBackward)
505 {
506 QDate result;
507 QStringList items = iDateString.split('/');
508 int size = items.count();
509 bool ok = false;
510
511 if (size == 1) {
512 int dayCount = items.at(0).toInt(&ok);
513
514 result = QDate(QDate::currentDate().year(), QDate::currentDate().month(), dayCount);
515
516 if (iFixupBackward) {
517 if (result > QDate::currentDate()) {
518 result = result.addMonths(-1);
519 }
520 } else {
521 if (result < QDate::currentDate()) {
522 result = result.addMonths(1);
523 }
524 }
525 } else if (size == 2) {
526 int dayCount = items.at(0).toInt(&ok);
527 int monthCount = items.at(1).toInt(&ok);
528
529 result = QDate(QDate::currentDate().year(), monthCount, dayCount);
530
531 if (iFixupBackward) {
532 if (result > QDate::currentDate()) {
533 result = result.addYears(-1);
534 }
535 } else {
536 if (result < QDate::currentDate()) {
537 result = result.addYears(1);
538 }
539 }
540 } else if (size == 3) {
541 int dayCount = items.at(0).toInt(&ok);
542 int monthCount = items.at(1).toInt(&ok);
543 int yearCount = items.at(2).toInt(&ok);
544 int lengthYear = items.at(2).count();
545
546 result = QDate(QDate::currentDate().year(), monthCount, dayCount);
547
548 if (lengthYear < 4) {
549 auto y = static_cast<int>(result.year() / qPow(10, lengthYear)) * qPow(10, lengthYear) + yearCount;
550 if (y > result.year() && iFixupBackward) {
551 y = y - qPow(10, lengthYear);
552 } else if (y < result.year() && !iFixupBackward) {
553 y = y + qPow(10, lengthYear);
554 }
555 result = result.addYears(y - result.year());
556 } else {
557 result = result.addYears(yearCount - result.year());
558 }
559 }
560
561 if (!ok) {
562 result = QDate();
563 }
564 return result;
565 }
566
splitCSVLine(const QString & iString,QChar iSeparator,bool iCoteDefineBlock)567 QStringList SKGServices::splitCSVLine(const QString& iString, QChar iSeparator, bool iCoteDefineBlock)
568 {
569 return splitCSVLine(iString, iSeparator, iCoteDefineBlock, nullptr);
570 }
571
splitCSVLine(const QString & iString,QChar iSeparator,bool iCoteDefineBlock,QChar * oRealSeparator)572 QStringList SKGServices::splitCSVLine(const QString& iString, QChar iSeparator, bool iCoteDefineBlock, QChar* oRealSeparator)
573 {
574 QStringList items;
575 QString item;
576 bool isInBlock = false;
577 QChar realSeparator = iSeparator;
578
579 QChar cote = ' '; // Not yet defined
580 int nb = iString.length();
581 items.reserve(nb);
582 for (int pos = 0; pos < nb; ++pos) {
583 QChar c = iString.at(pos);
584 if (isInBlock) {
585 if (c == cote) {
586 if (pos < nb - 1 && iString.at(pos + 1) == cote) {
587 ++pos; // separator escaped
588 } else {
589 items.push_back(item);
590 item.clear();
591 isInBlock = false;
592 // 320112 vvvv
593 // Reset the block character to autorize mix
594 cote = ' ';
595 // 320112 ^^^^
596
597 if (realSeparator != ' ') while (pos < nb - 1 && iString.at(pos + 1) == ' ') {
598 ++pos;
599 }
600 ++pos;
601 if (pos < nb) {
602 realSeparator = iString.at(pos); // To get the real separator
603 }
604 }
605 }
606
607 if (isInBlock) {
608 item += c;
609 }
610 } else if ((c == '\"' || c == '\'') && item.trimmed().isEmpty() && iCoteDefineBlock) {
611 if (cote == ' ') {
612 cote = c; // Set the real cote char
613 }
614 isInBlock = true;
615 item.clear();
616 } else if (QString(c) == realSeparator) {
617 items.push_back(item);
618 item.clear();
619 isInBlock = false;
620 // 320112 vvvv
621 // Reset the block character to autorize mix
622 cote = ' ';
623 // 320112 ^^^^
624 } else {
625 item += c;
626 }
627 }
628
629 if (!item.isEmpty() || (nb > 0 && iString.at(nb - 1) == realSeparator)) {
630 items.push_back(item);
631 }
632
633 if (oRealSeparator != nullptr) {
634 *oRealSeparator = realSeparator;
635 }
636
637 if (isInBlock) {
638 items.clear();
639 }
640
641 return items;
642 }
643
getDateFormat(const QStringList & iDates)644 QString SKGServices::getDateFormat(const QStringList& iDates)
645 {
646 SKGTRACEINFUNC(2)
647 bool f_YYYY_MM_DD = true;
648 bool f_YYYYMMDD = true;
649 bool f_DDMMYYYY = true;
650 bool f_MMDDYYYY = true;
651 bool f_MM_DD_YY = true;
652 bool f_DD_MM_YY = true;
653 bool f_MM_DD_YYYY = true;
654 bool f_DD_MM_YYYY = true;
655 bool f_DDMMMYYYY = true;
656 bool f_DD_MMM_YY = true;
657 bool f_DD_MMM_YYYY = true;
658
659 // Build regexp
660 QRegularExpression rx(QStringLiteral("(.+)-(.+)-(.+)"));
661
662 // Check all dates
663 int nb = iDates.count();
664 for (int i = 0; i < nb; ++i) {
665 QString val = iDates.at(i).trimmed();
666 if (val.count() > 10) {
667 auto l = SKGServices::splitCSVLine(val, ' ');
668 val = l[0];
669 }
670 if (!val.isEmpty()) {
671 val = val.replace(' ', '0');
672 val = val.replace('\\', '-');
673 val = val.replace('/', '-');
674 val = val.replace('.', '-');
675 val = val.replace(QStringLiteral("'20"), QStringLiteral("-20"));
676 val = val.replace(QStringLiteral("' "), QStringLiteral("-200"));
677 val = val.replace('\'', QStringLiteral("-20"));
678 val = val.replace(QStringLiteral("-90"), QStringLiteral("-1990"));
679 val = val.replace(QStringLiteral("-91"), QStringLiteral("-1991"));
680 val = val.replace(QStringLiteral("-92"), QStringLiteral("-1992"));
681 val = val.replace(QStringLiteral("-93"), QStringLiteral("-1993"));
682 val = val.replace(QStringLiteral("-94"), QStringLiteral("-1994"));
683 val = val.replace(QStringLiteral("-95"), QStringLiteral("-1995"));
684 val = val.replace(QStringLiteral("-96"), QStringLiteral("-1996"));
685 val = val.replace(QStringLiteral("-97"), QStringLiteral("-1997"));
686 val = val.replace(QStringLiteral("-98"), QStringLiteral("-1998"));
687 val = val.replace(QStringLiteral("-99"), QStringLiteral("-1999"));
688 auto match = rx.match(val);
689 if (!match.hasMatch()) {
690 f_YYYY_MM_DD = false;
691 f_MM_DD_YY = false;
692 f_DD_MM_YY = false;
693 f_MM_DD_YYYY = false;
694 f_DD_MM_YYYY = false;
695 f_DD_MMM_YY = false;
696 f_DD_MMM_YYYY = false;
697
698 if (val.length() == 8) {
699 auto left2 = SKGServices::stringToInt(val.left(2));
700 if (left2 > 12) {
701 f_MMDDYYYY = false;
702 }
703 if (left2 > 31) {
704 f_DDMMYYYY = false;
705 }
706
707 auto mid2 = SKGServices::stringToInt(val.mid(2, 2));
708 if (mid2 > 12) {
709 f_DDMMYYYY = false;
710 }
711 if (mid2 > 31) {
712 f_MMDDYYYY = false;
713 }
714
715 auto mid4 = SKGServices::stringToInt(val.mid(4, 2));
716 if (mid4 > 12) {
717 f_YYYYMMDD = false;
718 }
719
720 auto right2 = SKGServices::stringToInt(val.right(2));
721 if (right2 > 31) {
722 f_YYYYMMDD = false;
723 }
724
725 f_DDMMMYYYY = false;
726 } else if (val.length() == 9) {
727 f_MMDDYYYY = false;
728 f_DDMMYYYY = false;
729 f_YYYYMMDD = false;
730 } else {
731 f_MMDDYYYY = false;
732 f_DDMMYYYY = false;
733 f_YYYYMMDD = false;
734 f_DDMMMYYYY = false;
735 }
736 } else {
737 f_YYYYMMDD = false;
738 f_DDMMYYYY = false;
739 f_MMDDYYYY = false;
740 f_DDMMMYYYY = false;
741
742 QString v1 = match.captured(1);
743 QString v2 = match.captured(2);
744 QString v3 = match.captured(3);
745
746 if (SKGServices::stringToInt(v1) > 12) {
747 f_MM_DD_YY = false;
748 f_MM_DD_YYYY = false;
749 }
750
751 if (SKGServices::stringToInt(v2) > 12) {
752 f_DD_MM_YY = false;
753 f_DD_MM_YYYY = false;
754 }
755
756 if (v2.length() > 2) {
757 f_MM_DD_YY = false;
758 f_MM_DD_YYYY = false;
759 f_DD_MM_YY = false;
760 f_DD_MM_YYYY = false;
761 f_YYYY_MM_DD = false;
762 }
763
764 if (v2.length() != 3) {
765 f_DD_MMM_YYYY = false;
766 f_DD_MMM_YY = false;
767 }
768
769 if (SKGServices::stringToInt(v1) > 31 || SKGServices::stringToInt(v2) > 31) {
770 f_MM_DD_YY = false;
771 f_MM_DD_YYYY = false;
772 f_DD_MM_YY = false;
773 f_DD_MM_YYYY = false;
774 }
775
776 if (SKGServices::stringToInt(v3) > 31) {
777 f_YYYY_MM_DD = false;
778 }
779
780 if (v1.length() == 4) {
781 f_MM_DD_YY = false;
782 f_DD_MM_YY = false;
783 f_MM_DD_YYYY = false;
784 f_DD_MM_YYYY = false;
785 } else {
786 // To be more permissive and support mix of date: f_YYYY_MM_DD = false;
787 }
788
789 if (v3.length() == 4) {
790 f_YYYY_MM_DD = false;
791 f_MM_DD_YY = false;
792 f_DD_MM_YY = false;
793 } else {
794 // To be more permissive and support mix of date: f_MM_DD_YYYY = false;
795 // To be more permissive and support mix of date: f_DD_MM_YYYY = false;
796 }
797 }
798 }
799 }
800
801 if (f_YYYYMMDD) {
802 return QStringLiteral("YYYYMMDD");
803 }
804 if (f_MMDDYYYY) {
805 return QStringLiteral("MMDDYYYY");
806 }
807 if (f_DDMMYYYY) {
808 return QStringLiteral("DDMMYYYY");
809 }
810 if (f_DD_MM_YY && f_MM_DD_YY) {
811 QString sFormat = QLocale().dateFormat(QLocale::ShortFormat);
812 if (sFormat.startsWith(QLatin1String("%m")) || sFormat.startsWith(QLatin1String("%n"))) {
813 return QStringLiteral("MM-DD-YY");
814 }
815 return QStringLiteral("DD-MM-YY");
816 }
817 if (f_MM_DD_YY) {
818 return QStringLiteral("MM-DD-YY");
819 }
820 if (f_DD_MM_YY) {
821 return QStringLiteral("DD-MM-YY");
822 }
823 if (f_DD_MM_YYYY && f_MM_DD_YYYY) {
824 QString sFormat = QLocale().dateFormat(QLocale::ShortFormat);
825 if (sFormat.startsWith(QLatin1String("%m")) || sFormat.startsWith(QLatin1String("%n"))) {
826 return QStringLiteral("MM-DD-YYYY");
827 }
828 return QStringLiteral("DD-MM-YYYY");
829 }
830 if (f_MM_DD_YYYY) {
831 return QStringLiteral("MM-DD-YYYY");
832 }
833 if (f_DD_MM_YYYY) {
834 return QStringLiteral("DD-MM-YYYY");
835 }
836 if (f_YYYY_MM_DD) {
837 return QStringLiteral("YYYY-MM-DD");
838 }
839 if (f_DDMMMYYYY) {
840 return QStringLiteral("DDMMMYYYY");
841 }
842 if (f_DD_MMM_YY) {
843 return QStringLiteral("DD-MMM-YY");
844 }
845 if (f_DD_MMM_YYYY) {
846 return QStringLiteral("DD-MMM-YYYY");
847 }
848
849 return QLatin1String("");
850 }
851
toPercentageString(double iAmount,int iNbDecimal)852 QString SKGServices::toPercentageString(double iAmount, int iNbDecimal)
853 {
854 return toCurrencyString(iAmount, QString(), iNbDecimal) % " %";
855 }
856
toCurrencyString(double iAmount,const QString & iSymbol,int iNbDecimal)857 QString SKGServices::toCurrencyString(double iAmount, const QString& iSymbol, int iNbDecimal)
858 {
859 if (iSymbol == QStringLiteral("%")) {
860 return toPercentageString(iAmount, iNbDecimal);
861 }
862 return QLocale::system().toCurrencyString(iAmount, iSymbol.isEmpty() ? QStringLiteral(" ") : iSymbol, iNbDecimal).trimmed();
863 }
864
dateToSqlString(const QString & iDate,const QString & iFormat)865 QString SKGServices::dateToSqlString(const QString& iDate, const QString& iFormat)
866 {
867 QString input = iDate;
868 if (input.count() > 10) {
869 auto l = SKGServices::splitCSVLine(input, ' ');
870 input = l[0];
871 }
872
873 QString format = QStringLiteral("yyyy-MM-dd");
874 QString YYYY = QStringLiteral("0000");
875 QString MM = QStringLiteral("00");
876 QString DD = QStringLiteral("00");
877 if (iFormat == QStringLiteral("YYYYMMDD")) {
878 YYYY = input.mid(0, 4);
879 MM = input.mid(4, 2);
880 DD = input.mid(6, 2);
881 } else if (iFormat == QStringLiteral("DDMMYYYY") || iFormat == QStringLiteral("DDMMYY")) {
882 YYYY = input.mid(4, 4);
883 MM = input.mid(2, 2);
884 DD = input.mid(0, 2);
885 } else if (iFormat == QStringLiteral("DDMMMYYYY") || iFormat == QStringLiteral("DDMMMYY")) {
886 YYYY = input.mid(5, 4);
887 MM = input.mid(2, 3);
888 DD = input.mid(0, 2);
889 format = QStringLiteral("yyyy-MMM-dd");
890 } else if (iFormat == QStringLiteral("MMDDYYYY") || iFormat == QStringLiteral("MMDDYY")) {
891 YYYY = input.mid(4, 4);
892 MM = input.mid(0, 2);
893 DD = input.mid(2, 2);
894
895 } else {
896 QString val = input;
897 val = val.replace(' ', '0');
898 val = val.replace('\\', '-');
899 val = val.replace('/', '-');
900 val = val.replace('.', '-');
901 val = val.replace(QStringLiteral("'20"), QStringLiteral("-20"));
902 val = val.replace(QStringLiteral("' "), QStringLiteral("-200"));
903 val = val.replace('\'', QStringLiteral("-20"));
904 val = val.replace(QStringLiteral("-90"), QStringLiteral("-1990"));
905 val = val.replace(QStringLiteral("-91"), QStringLiteral("-1991"));
906 val = val.replace(QStringLiteral("-92"), QStringLiteral("-1992"));
907 val = val.replace(QStringLiteral("-93"), QStringLiteral("-1993"));
908 val = val.replace(QStringLiteral("-94"), QStringLiteral("-1994"));
909 val = val.replace(QStringLiteral("-95"), QStringLiteral("-1995"));
910 val = val.replace(QStringLiteral("-96"), QStringLiteral("-1996"));
911 val = val.replace(QStringLiteral("-97"), QStringLiteral("-1997"));
912 val = val.replace(QStringLiteral("-98"), QStringLiteral("-1998"));
913 val = val.replace(QStringLiteral("-99"), QStringLiteral("-1999"));
914 QRegularExpression rx(QStringLiteral("(.+)-(.+)-(.+)"));
915 auto match = rx.match(val);
916 if (match.hasMatch()) {
917 QString v1 = match.captured(1);
918 QString v2 = match.captured(2);
919 QString v3 = match.captured(3);
920 if (iFormat == QStringLiteral("YYYY-MM-DD")) {
921 YYYY = v1;
922 MM = v2;
923 DD = v3;
924 } else if (iFormat == QStringLiteral("MM/DD/YY") || iFormat == QStringLiteral("MM-DD-YY") || iFormat == QStringLiteral("MM/DD/YYYY") || iFormat == QStringLiteral("MM-DD-YYYY")) {
925 MM = v1;
926 DD = v2;
927 YYYY = v3;
928 } else if (iFormat == QStringLiteral("DD/MM/YY") || iFormat == QStringLiteral("DD-MM-YY") || iFormat == QStringLiteral("DD/MM/YYYY") || iFormat == QStringLiteral("DD-MM-YYYY")) {
929 DD = v1;
930 MM = v2;
931 YYYY = v3;
932 } else if (iFormat == QStringLiteral("DD/MMM/YY") || iFormat == QStringLiteral("DD-MMM-YY") || iFormat == QStringLiteral("DD/MMM/YYYY") || iFormat == QStringLiteral("DD-MMM-YYYY")) {
933 DD = v1;
934 MM = v2;
935 YYYY = v3;
936 format = QStringLiteral("yyyy-MMM-dd");
937 }
938 }
939 }
940
941 if (MM.length() == 1) {
942 MM = '0' % MM;
943 }
944 if (DD.length() == 1) {
945 DD = '0' % DD;
946 }
947 if (YYYY.length() == 1) {
948 YYYY = '0' % YYYY;
949 }
950 if (YYYY.length() == 2) {
951 if (stringToInt(YYYY) > 70) {
952 YYYY = "19" % YYYY;
953 } else {
954 YYYY = "20" % YYYY;
955 }
956 }
957
958 QString date = YYYY % '-' % MM % '-' % DD;
959 date.replace(' ', '0');
960 return dateToSqlString(QDateTime::fromString(date, format));
961 }
962
getPeriodWhereClause(const QString & iPeriod,const QString & iDateAttribute,const QString & iComparator)963 QString SKGServices::getPeriodWhereClause(const QString& iPeriod, const QString& iDateAttribute, const QString& iComparator)
964 {
965 QString output = QStringLiteral("1=0");
966 if (iPeriod == QStringLiteral("ALL")) {
967 output = QStringLiteral("1=1");
968 } else if (iPeriod.length() == 4) {
969 // 2014
970 output = "STRFTIME('%Y'," + SKGServices::stringToSqlString(iDateAttribute) + ")" + iComparator + "'" + SKGServices::stringToSqlString(iPeriod) + '\'';
971 } else if (iPeriod.length() == 7 && iPeriod[4] == '-') {
972 if (iPeriod[5] == 'S') {
973 // 2014-S1
974 output = "STRFTIME('%Y'," + SKGServices::stringToSqlString(iDateAttribute) + ")||'-S'||(CASE WHEN STRFTIME('%m'," + SKGServices::stringToSqlString(iDateAttribute) + ")<='06' THEN '1' ELSE '2' END)" + iComparator + "'" + SKGServices::stringToSqlString(iPeriod) + '\'';
975 } else if (iPeriod[5] == 'Q') {
976 // 2014-Q1
977 output = "STRFTIME('%Y'," + SKGServices::stringToSqlString(iDateAttribute) + ")||'-Q'||(CASE WHEN STRFTIME('%m'," + SKGServices::stringToSqlString(iDateAttribute) + ")<='03' THEN '1' WHEN STRFTIME('%m'," + SKGServices::stringToSqlString(iDateAttribute) + ")<='06' THEN '2' WHEN STRFTIME('%m'," + SKGServices::stringToSqlString(iDateAttribute) + ")<='09' THEN '3' ELSE '4' END)" + iComparator + "'" + SKGServices::stringToSqlString(iPeriod) + '\'';
978 } else {
979 // 2014-07
980 output = "STRFTIME('%Y-%m'," + SKGServices::stringToSqlString(iDateAttribute) + ")" + iComparator + "'" + SKGServices::stringToSqlString(iPeriod) + '\'';
981 }
982 }
983 if (iComparator == QStringLiteral("<") || iComparator == QStringLiteral("<=")) {
984 output = "(" + output + " OR " + iDateAttribute + "='0000-00-00')";
985 }
986 return output;
987 }
988
periodToDate(const QString & iPeriod)989 QDate SKGServices::periodToDate(const QString& iPeriod)
990 {
991 QDate output;
992
993 if (iPeriod == QStringLiteral("ALL")) {
994 output = QDate::currentDate();
995 } else if (iPeriod.length() == 4) {
996 // 2014
997 output = QDate::fromString(iPeriod, QStringLiteral("yyyy")).addYears(1).addDays(-1);
998 } else if (iPeriod.length() == 7) {
999 if (iPeriod[5] == 'S') {
1000 // 2014-S1
1001 output = QDate::fromString(iPeriod, QStringLiteral("yyyy-SM"));
1002 output = output.addMonths(output.month() * 6 - output.month()); // convert semester in month
1003 output = output.addMonths(1).addDays(-1);
1004 } else if (iPeriod[5] == 'Q') {
1005 // 2014-Q1
1006 output = QDate::fromString(iPeriod, QStringLiteral("yyyy-QM"));
1007 output = output.addMonths(output.month() * 3 - output.month()); // convert quarter in month
1008 output = output.addMonths(1).addDays(-1);
1009 } else {
1010 // 2014-07
1011 output = QDate::fromString(iPeriod, QStringLiteral("yyyy-MM")).addMonths(1).addDays(-1);
1012 }
1013 }
1014 return output;
1015 }
1016
getNeighboringPeriod(const QString & iPeriod,int iDelta)1017 QString SKGServices::getNeighboringPeriod(const QString& iPeriod, int iDelta)
1018 {
1019 QString output = QStringLiteral("1=0");
1020 if (iPeriod.length() == 4) {
1021 // 2014
1022 QDate date = QDate::fromString(iPeriod, QStringLiteral("yyyy")).addYears(iDelta);
1023 output = date.toString(QStringLiteral("yyyy"));
1024 } else if (iPeriod.length() == 7) {
1025 if (iPeriod[5] == 'S') {
1026 // 2014-S1
1027 QDate date2 = QDate::fromString(iPeriod, QStringLiteral("yyyy-SM"));
1028 date2 = date2.addMonths(date2.month() * 6 - date2.month()); // convert semester in month
1029 date2 = date2.addMonths(6 * iDelta);
1030 output = date2.toString(QStringLiteral("yyyy-S")) % (date2.month() <= 6 ? '1' : '2');
1031 } else if (iPeriod[5] == 'Q') {
1032 // 2014-Q1
1033 QDate date2 = QDate::fromString(iPeriod, QStringLiteral("yyyy-QM"));
1034 date2 = date2.addMonths(date2.month() * 3 - date2.month()); // convert quarter in month
1035 date2 = date2.addMonths(3 * iDelta);
1036 output = date2.toString(QStringLiteral("yyyy-Q")) % (date2.month() <= 3 ? '1' : (date2.month() <= 6 ? '2' : (date2.month() <= 9 ? '3' : '4')));
1037 } else {
1038 // 2014-07
1039 QDate date2 = QDate::fromString(iPeriod, QStringLiteral("yyyy-MM")).addMonths(iDelta);
1040 output = date2.toString(QStringLiteral("yyyy-MM"));
1041 }
1042 }
1043 return output;
1044 }
1045
tableToDump(const SKGStringListList & iTable,SKGServices::DumpMode iMode)1046 QStringList SKGServices::tableToDump(const SKGStringListList& iTable, SKGServices::DumpMode iMode)
1047 {
1048 SKGTRACEINFUNC(10)
1049 // initialisation
1050 QStringList oResult;
1051
1052 // Compute max size of each column
1053 int* maxSizes = nullptr;
1054 int nbMaxSizes = 0;
1055 if (iMode == DUMP_TEXT) {
1056 int nb = iTable.count();
1057 for (int i = 0; i < nb; ++i) {
1058 const QStringList& line = iTable.at(i);
1059 int nb2 = line.size();
1060
1061 if (maxSizes == nullptr) {
1062 nbMaxSizes = nb2;
1063 maxSizes = new int[nbMaxSizes];
1064 for (int j = 0; j < nbMaxSizes; ++j) {
1065 maxSizes[j] = 0;
1066 }
1067 }
1068
1069 for (int j = 0; j < nb2; ++j) {
1070 const QString& s = line.at(j);
1071 if (j < nbMaxSizes && s.length() > maxSizes[j]) {
1072 maxSizes[j] = s.length();
1073 }
1074 }
1075 }
1076 }
1077
1078 // dump
1079 int nb = iTable.count();
1080 oResult.reserve(nb);
1081 for (int i = 0; i < nb; ++i) {
1082 QString lineFormated;
1083 if (iMode == DUMP_TEXT) {
1084 lineFormated = QStringLiteral("| ");
1085 }
1086
1087 const QStringList& line = iTable.at(i);
1088 int nb2 = line.size();
1089 for (int j = 0; j < nb2; ++j) {
1090 QString s = line.at(j);
1091 s.remove('\n');
1092
1093 if (iMode == DUMP_CSV) {
1094 if (j > 0) {
1095 lineFormated += ';';
1096 }
1097 lineFormated += stringToCsv(s);
1098 } else if (maxSizes != nullptr) {
1099 if (j < nbMaxSizes) {
1100 s = s.leftJustified(maxSizes[j], ' ');
1101 }
1102 lineFormated += s % " | ";
1103 }
1104 }
1105 oResult.push_back(lineFormated);
1106 }
1107
1108 // delete
1109 if (maxSizes != nullptr) {
1110 delete [] maxSizes;
1111 maxSizes = nullptr;
1112 }
1113
1114 return oResult;
1115 }
1116
getRealTable(const QString & iTable)1117 QString SKGServices::getRealTable(const QString& iTable)
1118 {
1119 QString output = iTable;
1120 if (output.length() > 2 && output.startsWith(QLatin1String("v_"))) {
1121 output = output.mid(2, output.length() - 2);
1122
1123 int pos = output.indexOf(QStringLiteral("_"));
1124 if (pos != -1) {
1125 output = output.left(pos);
1126 }
1127 }
1128
1129 return output;
1130 }
1131
downloadToStream(const QUrl & iSourceUrl,QByteArray & oStream)1132 SKGError SKGServices::downloadToStream(const QUrl& iSourceUrl, QByteArray& oStream)
1133 {
1134 SKGError err;
1135 SKGTRACEINFUNCRC(10, err)
1136 QString tmpFile;
1137 if (iSourceUrl.isLocalFile()) {
1138 tmpFile = iSourceUrl.toLocalFile();
1139 } else {
1140 err = download(iSourceUrl, tmpFile);
1141 }
1142 IFOK(err) {
1143 // Open file
1144 QFile file(tmpFile);
1145 if (Q_UNLIKELY(!file.open(QIODevice::ReadOnly))) {
1146 err.setReturnCode(ERR_FAIL).setMessage(i18nc("An information message", "Open file '%1' failed", tmpFile));
1147 } else {
1148 oStream = file.readAll();
1149
1150 // close file
1151 file.close();
1152 }
1153 if (!iSourceUrl.isLocalFile()) {
1154 QFile(tmpFile).remove();
1155 }
1156 }
1157 return err;
1158 }
1159
download(const QUrl & iSourceUrl,QString & oTemporaryFile)1160 SKGError SKGServices::download(const QUrl& iSourceUrl, QString& oTemporaryFile)
1161 {
1162 SKGError err;
1163 SKGTRACEINFUNCRC(10, err)
1164 QTemporaryFile tmpFile;
1165 tmpFile.setAutoRemove(false);
1166 if (tmpFile.open()) {
1167 err = upload(iSourceUrl, QUrl::fromLocalFile(tmpFile.fileName()));
1168 IFOK(err) oTemporaryFile = tmpFile.fileName();
1169 }
1170 return err;
1171 }
1172
upload(const QUrl & iSourceUrl,const QUrl & iDescUrl)1173 SKGError SKGServices::upload(const QUrl& iSourceUrl, const QUrl& iDescUrl)
1174 {
1175 SKGError err;
1176 SKGTRACEINFUNCRC(10, err)
1177 if (iDescUrl != iSourceUrl) {
1178 if (iDescUrl.isLocalFile() && iSourceUrl.isLocalFile()) {
1179 QFile(iDescUrl.toLocalFile()).remove();
1180 if (!QFile::copy(iSourceUrl.toLocalFile(), iDescUrl.toLocalFile())) {
1181 err = SKGError(ERR_ABORT, i18nc("Error message", "Impossible to copy '%1' to '%2'", iSourceUrl.toDisplayString(), iDescUrl.toDisplayString()));
1182 }
1183 } else {
1184 KIO::FileCopyJob* getJob = KIO::file_copy(iSourceUrl, iDescUrl, -1, KIO::Overwrite | KIO::HideProgressInfo);
1185 if (!getJob->exec()) {
1186 err.setReturnCode(ERR_ABORT).setMessage(getJob->errorString());
1187 err.addError(ERR_ABORT, i18nc("Error message", "Impossible to copy '%1' to '%2'", iSourceUrl.toDisplayString(), iDescUrl.toDisplayString()));
1188 }
1189 }
1190 }
1191 return err;
1192 }
1193
cryptFile(const QString & iFileSource,const QString & iFileTarget,const QString & iPassword,bool iEncrypt,const QString & iHeaderFile,bool & oModeSQLCipher)1194 SKGError SKGServices::cryptFile(const QString& iFileSource, const QString& iFileTarget, const QString& iPassword, bool iEncrypt, const QString& iHeaderFile, bool& oModeSQLCipher)
1195 {
1196 SKGError err;
1197 SKGTRACEINFUNCRC(10, err)
1198 SKGTRACEL(10) << "Input parameter [iFileSource]=[" << iFileSource << ']' << SKGENDL;
1199 SKGTRACEL(10) << "Input parameter [iFileTarget]=[" << iFileTarget << ']' << SKGENDL;
1200 SKGTRACEL(10) << "Input parameter [iPassword] =[" << iPassword << ']' << SKGENDL;
1201 SKGTRACEL(10) << "Input parameter [iHeaderFile]=[" << iHeaderFile << ']' << SKGENDL;
1202
1203 oModeSQLCipher = false;
1204
1205 // Read document
1206 QByteArray input;
1207 QByteArray uba;
1208 err = downloadToStream(QUrl::fromUserInput(iFileSource), input);
1209 IFOK(err) {
1210 bool isFileEncrypted = (input.startsWith(QByteArray((iHeaderFile % "_ENCRYPT").toLatin1())));
1211 bool sqliteMode = (input.left(15) == "SQLite format 3");
1212 SKGTRACEL(10) << "isFileEncrypted=[" << static_cast<unsigned int>(isFileEncrypted) << ']' << SKGENDL;
1213
1214 // !!!!! Remove Cipher encryption to remove security hole (thank you to Vincent P) !!!!!
1215 // Only in sqlcipher mode. WARNING: in sqlite mode the issue is still there => add a message to push people to swith in sqlcipher mode
1216 if (iEncrypt && !sqliteMode) {
1217 // The input file is a sqlcipher file and we must save it
1218 // We just have to add a new header to the input file
1219 uba.reserve(input.length() + iHeaderFile.length() + 11);
1220 uba.append(iHeaderFile.toLatin1());
1221 uba.append(!iPassword.isEmpty() ? "_ENCRYPTE3-" : "_DECRYPTE3-");
1222 uba.append(input);
1223 oModeSQLCipher = true;
1224 } else if (!iEncrypt && input.startsWith(QByteArray((iHeaderFile % "_ENCRYPTE3-").toLatin1()))) {
1225 // This check is done to improve performances
1226 if (iPassword.isEmpty() || iPassword == QStringLiteral("DEFAULTPASSWORD")) {
1227 err = SKGError(ERR_ENCRYPTION, i18nc("Error message", "Wrong password"));
1228 } else {
1229 // The input file encrypter with the new mode
1230 // We just have to remove the header
1231 if (!iHeaderFile.isEmpty() && input.startsWith(iHeaderFile.toLatin1())) {
1232 input = input.right(input.length() - iHeaderFile.length() - 11);
1233 }
1234 uba = input;
1235 oModeSQLCipher = true;
1236 }
1237 } else {
1238 // WARNING: This part is not really secured but is kept for compatibility
1239 SKGTRACEL(10) << "Mode not secured" << SKGENDL;
1240 QCA::Initializer init;
1241 QCA::SymmetricKey key(QByteArray("skrooge"));
1242
1243 SKGTRACEL(10) << "QCA::Initializer done" << SKGENDL;
1244 if (!iPassword.isEmpty() && !QCA::isSupported("aes128-ecb")) {
1245 // Set error message
1246 err.setReturnCode(ERR_INSTALL); // To avoid password request
1247 err.setMessage(i18nc("An error message about encryption", "AES128 encryption is not supported (%1). Please install qca-ossl.", QCA::supportedFeatures().join(QStringLiteral(","))));
1248 } else {
1249 QCA::Cipher* cipher = nullptr;
1250
1251 QCA::InitializationVector iv(iPassword.toLatin1());
1252
1253 // Create a 128 bit AES cipher object using Cipher Block Chaining (CBC) mode
1254 if ((isFileEncrypted || iEncrypt) && !iPassword.isEmpty()) {
1255 cipher = new QCA::Cipher(QStringLiteral("aes128"), QCA::Cipher::CBC,
1256 // use Default padding, which is equivalent to PKCS7 for CBC
1257 QCA::Cipher::DefaultPadding,
1258 iEncrypt ? QCA::Encode : QCA::Decode,
1259 key, iv);
1260 }
1261
1262 // BUG 249955 vvv
1263 if ((cipher == nullptr) && isFileEncrypted) {
1264 err = SKGError(ERR_ENCRYPTION, i18nc("Error message about encrypting a file", "Encryption failed"));
1265 }
1266 // BUG 249955 ^^^
1267
1268 // Suppress header
1269 SKGTRACEL(10) << "input=[" << input.left(50) << "...]" << SKGENDL;
1270 if (!iHeaderFile.isEmpty() && input.startsWith(iHeaderFile.toLatin1())) {
1271 input = input.right(input.length() - iHeaderFile.length() - 11);
1272 }
1273 SKGTRACEL(10) << "input without header=[" << input.left(50) << "...]" << SKGENDL;
1274
1275 QCA::SecureArray u;
1276 if (cipher != nullptr) {
1277 if (!err) {
1278 // Process encryption or decryption
1279 u = cipher->process(input);
1280
1281 // We need to check if that update() call worked.
1282 if (!cipher->ok()) {
1283 err = SKGError(ERR_UNEXPECTED, i18nc("Error message about encrypting a file", "Encryption failed"));
1284 } else {
1285 uba = u.toByteArray();
1286 }
1287 }
1288 } else {
1289 uba = input;
1290 }
1291
1292 IFOK(err) {
1293 // Check if decryption is OK
1294 SKGTRACEL(10) << "output 1=[" << uba.left(50) << "...]" << SKGENDL;
1295 if (!iEncrypt) {
1296 if (!uba.startsWith(QByteArray("SQLite format 3"))) {
1297 if (!uba.startsWith(SQLCIPHERHEARDER)) {
1298 if (isFileEncrypted) {
1299 err = SKGError(ERR_ENCRYPTION, i18nc("Error message", "Wrong password"));
1300 } else {
1301 oModeSQLCipher = true;
1302 }
1303 } else {
1304 uba = uba.right(uba.length() - QStringLiteral(SQLCIPHERHEARDER).length());
1305 oModeSQLCipher = true;
1306 }
1307 }
1308 }
1309 }
1310
1311 IFOK(err) {
1312 // Add headers
1313 if (iEncrypt && !iHeaderFile.isEmpty()) {
1314 QByteArray h = (iHeaderFile % (cipher != nullptr ? "_ENCRYPTED-" : "_DECRYPTED-")).toLatin1();
1315 uba = uba.insert(0, h);
1316 }
1317 }
1318
1319 delete cipher;
1320 cipher = nullptr;
1321 }
1322 }
1323 SKGTRACEL(10) << "output 2=[" << uba.left(50) << "...]" << SKGENDL;
1324
1325 // output the results of that stage
1326 IFOK(err) {
1327 SKGTRACEIN(10, "SKGServices::cryptFile-save file")
1328 QSaveFile fileOutput(iFileTarget);
1329 if (!fileOutput.open(QIODevice::WriteOnly)) {
1330 err = SKGError(ERR_WRITEACCESS, i18nc("Error message: writing a file failed", "Write file '%1' failed", iFileTarget));
1331 } else {
1332 // Write document
1333 fileOutput.write(uba);
1334
1335 // Close the file
1336 if (!fileOutput.commit()) {
1337 IFOK(err) {
1338 err = SKGError(ERR_WRITEACCESS, i18nc("Error message: writing a file failed", "Write file '%1' failed", iFileTarget));
1339 }
1340 }
1341 }
1342 }
1343 }
1344 SKGTRACEL(10) << "Output parameter [oModeSQLCipher]=[" << static_cast<unsigned int>(oModeSQLCipher) << ']' << SKGENDL;
1345 return err;
1346 }
1347
copySqliteDatabaseToXml(const QSqlDatabase & iDb,QDomDocument & oDocument)1348 SKGError SKGServices::copySqliteDatabaseToXml(const QSqlDatabase& iDb, QDomDocument& oDocument)
1349 {
1350 SKGError err;
1351 SKGTRACEINFUNCRC(10, err)
1352 oDocument = QDomDocument(QStringLiteral("SKGML"));
1353 QDomElement document = oDocument.createElement(QStringLiteral("document"));
1354 oDocument.appendChild(document);
1355
1356 // Copy the tables
1357 QStringList listTables = iDb.tables();
1358 int nb = listTables.count();
1359 for (int i = 0; !err && i < nb; ++i) {
1360 const QString& tableName = listTables.at(i);
1361 if (!tableName.startsWith(QLatin1String("sqlite_")) && !tableName.startsWith(QLatin1String("vm_"))) {
1362 QDomElement table = oDocument.createElement(QStringLiteral("table"));
1363 document.appendChild(table);
1364 table.setAttribute(QStringLiteral("name"), tableName);
1365
1366 SKGStringListList listRows;
1367 err = SKGServices::executeSelectSqliteOrder(iDb, "SELECT * FROM " % tableName, listRows);
1368 int nbRows = listRows.count();
1369 if (nbRows != 0) {
1370 const QStringList& titles = listRows.at(0);
1371 for (int j = 1; !err && j < nbRows; ++j) { // Forget title
1372 const QStringList& values = listRows.at(j);
1373
1374 QDomElement row = oDocument.createElement(QStringLiteral("row"));
1375 table.appendChild(row);
1376
1377 int nbVals = values.count();
1378 for (int k = 0; k < nbVals; ++k) {
1379 row.setAttribute(titles.at(k), values.at(k));
1380 }
1381 }
1382 }
1383 }
1384 }
1385 return err;
1386 }
1387
copySqliteDatabase(const QSqlDatabase & iFileDb,const QSqlDatabase & iMemoryDb,bool iFromFileToMemory,const QString & iPassword)1388 SKGError SKGServices::copySqliteDatabase(const QSqlDatabase& iFileDb, const QSqlDatabase& iMemoryDb, bool iFromFileToMemory, const QString& iPassword)
1389 {
1390 SKGError err;
1391 SKGTRACEINFUNCRC(10, err)
1392 SKGTRACEL(20) << "Input parameter [iFileDb]=[" << iFileDb.databaseName() << ']' << SKGENDL;
1393 SKGTRACEL(20) << "Input parameter [iMemoryDb]=[" << iMemoryDb.databaseName() << ']' << SKGENDL;
1394 SKGTRACEL(10) << "Input parameter [iFromFileToMemory]=[" << (iFromFileToMemory ? "FILE->MEMORY" : "MEMORY->FILE") << ']' << SKGENDL;
1395
1396 QString dbFileName = iFileDb.databaseName();
1397 // Copy the tables
1398 SKGStringListList listTables;
1399 int nb = 0;
1400 IFOK(err) {
1401 err = SKGServices::executeSelectSqliteOrder((iFromFileToMemory ? iFileDb : iMemoryDb),
1402 QStringLiteral("SELECT sql, tbl_name FROM sqlite_master WHERE type='table' AND sql NOT NULL and name NOT LIKE 'sqlite_%'"),
1403 listTables);
1404
1405 nb = listTables.count();
1406 for (int i = 1; !err && i < nb; ++i) { // Forget header
1407 QString val = listTables.at(i).at(0);
1408 err = SKGServices::executeSqliteOrder((iFromFileToMemory ? iMemoryDb : iFileDb), val);
1409 }
1410 }
1411 // Attach db
1412 IFOK(err) {
1413 QString add;
1414 if (!iPassword.isEmpty()) {
1415 add = " KEY '" % SKGServices::stringToSqlString(iPassword) % "'";
1416 }
1417 err = SKGServices::executeSqliteOrder(iMemoryDb, "ATTACH DATABASE '" % dbFileName % "' as source" % add);
1418 }
1419
1420 // Copy records
1421 IFOK(err) {
1422 err = SKGServices::executeSqliteOrder(iMemoryDb, QStringLiteral("BEGIN"));
1423 IFOK(err) {
1424 for (int i = 1; !err && i < nb; ++i) { // Forget header
1425 QString val = listTables.at(i).at(1);
1426 if (iFromFileToMemory) {
1427 err = SKGServices::executeSqliteOrder(iMemoryDb, "insert into main." % val % " select * from source." % val);
1428 } else {
1429 err = SKGServices::executeSqliteOrder(iMemoryDb, "insert into source." % val % " select * from main." % val);
1430 }
1431 }
1432 }
1433 SKGServices::executeSqliteOrder(iMemoryDb, QStringLiteral("COMMIT"));
1434 }
1435
1436 // Detach
1437 {
1438 SKGError err2 = SKGServices::executeSqliteOrder(iMemoryDb, QStringLiteral("DETACH DATABASE source"));
1439 if (!err && err2) {
1440 err = err2;
1441 }
1442 }
1443
1444 // Optimization
1445 IFOK(err) {
1446 QStringList optimization;
1447 optimization << QStringLiteral("PRAGMA case_sensitive_like=true")
1448 << QStringLiteral("PRAGMA journal_mode=MEMORY")
1449 << QStringLiteral("PRAGMA temp_store=MEMORY")
1450 // << QStringLiteral("PRAGMA locking_mode=EXCLUSIVE")
1451 << QStringLiteral("PRAGMA synchronous = OFF")
1452 << QStringLiteral("PRAGMA recursive_triggers=true");
1453 err = SKGServices::executeSqliteOrders(iFromFileToMemory ? iMemoryDb : iFileDb, optimization);
1454 }
1455
1456 // Copy the indexes
1457 IFOK(err) {
1458 SKGStringListList listSqlOrder;
1459 err = SKGServices::executeSelectSqliteOrder((iFromFileToMemory ? iFileDb : iMemoryDb),
1460 QStringLiteral("SELECT sql FROM sqlite_master WHERE type='index' AND sql NOT NULL and name NOT LIKE 'sqlite_%'"),
1461 listSqlOrder);
1462
1463 int nb2 = listSqlOrder.count();
1464 for (int i = 1; !err && i < nb2; ++i) { // Forget header
1465 QString val = listSqlOrder.at(i).at(0);
1466 err = SKGServices::executeSqliteOrder((iFromFileToMemory ? iMemoryDb : iFileDb), val);
1467 }
1468 }
1469
1470 // Copy the views
1471 IFOK(err) {
1472 SKGStringListList listSqlOrder;
1473 err = SKGServices::executeSelectSqliteOrder((iFromFileToMemory ? iFileDb : iMemoryDb),
1474 QStringLiteral("SELECT sql FROM sqlite_master WHERE type='view' AND sql NOT NULL and name NOT LIKE 'sqlite_%'"),
1475 listSqlOrder);
1476
1477 int nb2 = listSqlOrder.count();
1478 for (int i = 1; !err && i < nb2; ++i) { // Forget header
1479 QString val = listSqlOrder.at(i).at(0);
1480 err = SKGServices::executeSqliteOrder((iFromFileToMemory ? iMemoryDb : iFileDb), val);
1481 }
1482 }
1483
1484 // Copy the triggers, must be done after the views
1485 IFOK(err) {
1486 SKGStringListList listSqlOrder;
1487 err = SKGServices::executeSelectSqliteOrder((iFromFileToMemory ? iFileDb : iMemoryDb),
1488 QStringLiteral("SELECT sql FROM sqlite_master WHERE type='trigger' AND sql NOT NULL and name NOT LIKE 'sqlite_%'"),
1489 listSqlOrder);
1490
1491 int nb2 = listSqlOrder.count();
1492 for (int i = 1; !err && i < nb2; ++i) { // Forget header
1493 QString val = listSqlOrder.at(i).at(0);
1494 err = SKGServices::executeSqliteOrder((iFromFileToMemory ? iMemoryDb : iFileDb), val);
1495 }
1496 }
1497
1498 // Check if created file exists
1499 if (!err && !iFromFileToMemory && !QFile(dbFileName).exists()) {
1500 err.setReturnCode(ERR_FAIL).setMessage(i18nc("An error message: creating a file failed", "Creation file '%1' failed", dbFileName));
1501 }
1502 IFKO(err) {
1503 err.addError(SQLLITEERROR + ERR_FAIL, i18nc("Error message: something failed", "%1 failed", QStringLiteral("SKGServices::copySqliteDatabase()")));
1504 }
1505 return err;
1506 }
1507
executeSqliteOrders(const QSqlDatabase & iDb,const QStringList & iSqlOrders)1508 SKGError SKGServices::executeSqliteOrders(const QSqlDatabase& iDb, const QStringList& iSqlOrders)
1509 {
1510 SKGError err;
1511 _SKGTRACEINFUNCRC(10, err)
1512 int nb = iSqlOrders.count();
1513 for (int i = 0; !err && i < nb; ++i) {
1514 err = executeSqliteOrder(iDb, iSqlOrders.at(i));
1515 }
1516 return err;
1517 }
1518
executeSqliteOrder(const QSqlDatabase & iDb,const QString & iSqlOrder,const QMap<QString,QVariant> & iBind,int * iLastId)1519 SKGError SKGServices::executeSqliteOrder(const QSqlDatabase& iDb, const QString& iSqlOrder, const QMap<QString, QVariant>& iBind, int* iLastId)
1520 {
1521 SKGError err;
1522 _SKGTRACEINFUNCRC(10, err)
1523 SKGTRACEL(20) << "Input parameter [iSqlOrder]=[" << iSqlOrder << ']' << SKGENDL;
1524
1525 QSqlQuery query(QString(), iDb);
1526 query.setForwardOnly(true);
1527
1528 double elapse = 0;
1529 if (SKGServices::SKGSqlTraces != -1) {
1530 elapse = SKGServices::getMicroTime();
1531 }
1532
1533 // Prepare sql order
1534 bool prep = query.prepare(iSqlOrder);
1535
1536 // Bind values
1537 QMapIterator<QString, QVariant> i(iBind);
1538 while (i.hasNext()) {
1539 i.next();
1540 query.bindValue(i.key(), i.value());
1541 }
1542
1543 if (!prep || !query.exec()) {
1544 QSqlError sqlError = query.lastError();
1545 if (sqlError.nativeErrorCode().toInt() != 19 /*SQLITE_CONSTRAINT*/ && iSqlOrder != QStringLiteral("SELECT count(*) FROM sqlite_master") /*Test password*/) {
1546 SKGTRACE << "WARNING: " << iSqlOrder << SKGENDL;
1547 SKGTRACE << " returns :" << sqlError.text() << SKGENDL;
1548 }
1549
1550 err = SKGError(SQLLITEERROR + sqlError.nativeErrorCode().toInt(), iSqlOrder);
1551 err.addError(SQLLITEERROR + sqlError.nativeErrorCode().toInt(), sqlError.text());
1552
1553 if (sqlError.nativeErrorCode().toInt() == 19 && iSqlOrder.startsWith(QLatin1String("INSERT "))) {
1554 err.addError(ERR_FAIL, i18nc("Error message", "Creation failed. The object already exists."));
1555 }
1556 } else {
1557 if (iLastId != nullptr) {
1558 *iLastId = query.lastInsertId().toInt();
1559 }
1560 }
1561 if (SKGServices::SKGSqlTraces != -1) {
1562 elapse = SKGServices::getMicroTime() - elapse;
1563 if (elapse >= SKGServices::SKGSqlTraces) {
1564 SKGTRACE << "executeSqliteOrder :" << iSqlOrder << " TIME=" << elapse << " ms" << SKGENDL;
1565 }
1566 }
1567 return err;
1568 }
1569
executeSqliteOrder(const QSqlDatabase & iDb,const QString & iSqlOrder,int * iLastId)1570 SKGError SKGServices::executeSqliteOrder(const QSqlDatabase& iDb, const QString& iSqlOrder, int* iLastId)
1571 {
1572 return executeSqliteOrder(iDb, iSqlOrder, QMap< QString, QVariant >(), iLastId);
1573 }
1574
dumpSelectSqliteOrder(const QSqlDatabase & iDb,const QString & iSqlOrder,SKGServices::DumpMode iMode)1575 SKGError SKGServices::dumpSelectSqliteOrder(const QSqlDatabase& iDb, const QString& iSqlOrder, SKGServices::DumpMode iMode)
1576 {
1577 return dumpSelectSqliteOrder(iDb, iSqlOrder, nullptr, iMode);
1578 }
1579
dumpSelectSqliteOrder(const QSqlDatabase & iDb,const QString & iSqlOrder,QTextStream * oStream,SKGServices::DumpMode iMode)1580 SKGError SKGServices::dumpSelectSqliteOrder(const QSqlDatabase& iDb, const QString& iSqlOrder, QTextStream* oStream, SKGServices::DumpMode iMode)
1581 {
1582 SKGError err;
1583 _SKGTRACEINFUNCRC(10, err)
1584 SKGTRACEL(20) << "Input parameter [iSqlOrder]=[" << iSqlOrder << ']' << SKGENDL;
1585
1586 // initialisation
1587 QStringList oResult;
1588 err = SKGServices::dumpSelectSqliteOrder(iDb, iSqlOrder, oResult, iMode);
1589 IFOK(err) {
1590 // dump
1591 int nb = oResult.size();
1592 for (int i = 0; i < nb; ++i) {
1593 if (oStream == nullptr) {
1594 SKGTRACESUITE << oResult.at(i) << SKGENDL;
1595 } else {
1596 *oStream << oResult.at(i) << SKGENDL;
1597 }
1598 }
1599 }
1600 return err;
1601 }
1602
dumpSelectSqliteOrder(const QSqlDatabase & iDb,const QString & iSqlOrder,QString & oResult,SKGServices::DumpMode iMode)1603 SKGError SKGServices::dumpSelectSqliteOrder(const QSqlDatabase& iDb, const QString& iSqlOrder, QString& oResult, SKGServices::DumpMode iMode)
1604 {
1605 SKGError err;
1606 _SKGTRACEINFUNCRC(10, err)
1607 // initialisation
1608 oResult = QLatin1String("");
1609
1610 QStringList oResultTmp;
1611 err = SKGServices::dumpSelectSqliteOrder(iDb, iSqlOrder, oResultTmp, iMode);
1612 IFOK(err) {
1613 // dump
1614 int nb = oResultTmp.size();
1615 for (int i = 0; i < nb; ++i) {
1616 oResult += oResultTmp.at(i) % '\n';
1617 }
1618 }
1619 return err;
1620 }
1621
dumpSelectSqliteOrder(const QSqlDatabase & iDb,const QString & iSqlOrder,QStringList & oResult,SKGServices::DumpMode iMode)1622 SKGError SKGServices::dumpSelectSqliteOrder(const QSqlDatabase& iDb, const QString& iSqlOrder, QStringList& oResult, SKGServices::DumpMode iMode)
1623 {
1624 SKGError err;
1625 _SKGTRACEINFUNCRC(10, err)
1626
1627 // Execution of sql order
1628 SKGStringListList oResultTmp;
1629 err = executeSelectSqliteOrder(iDb, iSqlOrder, oResultTmp);
1630 IFOK(err) oResult = tableToDump(oResultTmp, iMode);
1631 return err;
1632 }
1633
executeSingleSelectSqliteOrder(const QSqlDatabase & iDb,const QString & iSqlOrder,QString & oResult)1634 SKGError SKGServices::executeSingleSelectSqliteOrder(const QSqlDatabase& iDb, const QString& iSqlOrder, QString& oResult)
1635 {
1636 SKGStringListList result;
1637 SKGError err = executeSelectSqliteOrder(iDb, iSqlOrder, result);
1638 oResult = result.value(1).value(0);
1639 return err;
1640 }
1641
executeSelectSqliteOrder(const QSqlDatabase & iDb,const QString & iSqlOrder,SKGStringListList & oResult)1642 SKGError SKGServices::executeSelectSqliteOrder(const QSqlDatabase& iDb, const QString& iSqlOrder, SKGStringListList& oResult)
1643 {
1644 SKGError err;
1645 _SKGTRACEINFUNCRC(10, err)
1646 // initialisation
1647 oResult.clear();
1648
1649 QSqlQuery query(QString(), iDb);
1650 query.setForwardOnly(true);
1651 double elapse = 0;
1652 if (SKGServices::SKGSqlTraces != -1) {
1653 elapse = SKGServices::getMicroTime();
1654 }
1655
1656 if (!query.exec(iSqlOrder)) {
1657 QSqlError sqlError = query.lastError();
1658 if (qApp->thread() == QThread::currentThread()) {
1659 SKGTRACE << "WARNING: " << iSqlOrder << SKGENDL;
1660 SKGTRACE << " returns :" << sqlError.text() << SKGENDL;
1661 }
1662 err = SKGError(SQLLITEERROR + sqlError.nativeErrorCode().toInt(), iSqlOrder);
1663 err.addError(SQLLITEERROR + sqlError.nativeErrorCode().toInt(), sqlError.text());
1664 } else {
1665 double elapse1 = 0;
1666 if (SKGServices::SKGSqlTraces != -1) {
1667 elapse1 = SKGServices::getMicroTime() - elapse;
1668 }
1669
1670 // Addition of column names
1671 QSqlRecord rec = query.record();
1672 QStringList line;
1673 int index = 0;
1674 while (index != -1) {
1675 QString val = rec.fieldName(index);
1676 if (!val.isEmpty()) {
1677 line.push_back(val);
1678 ++index;
1679 } else {
1680 index = -1;
1681 }
1682 }
1683 oResult.push_back(line);
1684
1685 // Addition of rows
1686 while (query.next()) {
1687 QStringList line2;
1688 int index2 = 0;
1689 while (index2 != -1) {
1690 QVariant val = query.value(index2);
1691 if (val.isValid()) {
1692 line2.push_back(val.toString());
1693 ++index2;
1694 } else {
1695 index2 = -1;
1696 }
1697 }
1698 oResult.push_back(line2);
1699 }
1700 if (SKGServices::SKGSqlTraces != -1) {
1701 double elapse2 = SKGServices::getMicroTime() - elapse;
1702 if (elapse1 >= SKGServices::SKGSqlTraces) {
1703 SKGTRACE << "executeSqliteOrder:" << iSqlOrder << " TIME=" << elapse1 << " ms, (with fetch):" << elapse2 << " ms" << SKGENDL;
1704 }
1705 }
1706 }
1707 return err;
1708 }
1709
readPropertyFile(const QString & iFileName,QHash<QString,QString> & oProperties)1710 SKGError SKGServices::readPropertyFile(const QString& iFileName, QHash< QString, QString >& oProperties)
1711 {
1712 SKGError err;
1713 oProperties.clear();
1714
1715 // Open file
1716 QFile file(iFileName);
1717 if (!file.open(QIODevice::ReadOnly | QIODevice::Text)) {
1718 err = SKGError(ERR_FAIL, i18nc("An erro message", "Open file '%1' failed", iFileName));
1719 } else {
1720 // Read file
1721 QTextStream stream(&file);
1722 while (!stream.atEnd() && !err) {
1723 // Read line
1724 QString line = stream.readLine().trimmed();
1725 if (!line.isEmpty() && !line.startsWith(QLatin1String("#"))) {
1726 int pos = line.indexOf(QStringLiteral("="));
1727 if (pos != -1) {
1728 oProperties[line.left(pos).trimmed().toLower()] = line.right(line.count() - pos - 1);
1729 }
1730 }
1731 }
1732
1733 // close file
1734 file.close();
1735 }
1736 return err;
1737 }
1738
getMicroTime()1739 double SKGServices::getMicroTime()
1740 {
1741 #ifdef Q_OS_WIN
1742 return static_cast<double>(GetTickCount());
1743 #else
1744 struct timeval tv {};
1745 struct timezone tz {};
1746
1747 // get time
1748 gettimeofday(&tv, &tz);
1749
1750 // return time
1751 return (static_cast<double>(1000.0 * tv.tv_sec)) + (static_cast<double>(tv.tv_usec / 1000));
1752 #endif
1753 }
1754
getBase100Table(const SKGStringListList & iTable)1755 SKGStringListList SKGServices::getBase100Table(const SKGStringListList& iTable)
1756 {
1757 SKGTRACEINFUNC(10)
1758
1759 // Build history
1760 SKGStringListList output;
1761 int nblines = iTable.count();
1762 int nbCols = 0;
1763 if (nblines != 0) {
1764 nbCols = iTable.at(0).count();
1765 }
1766
1767 output.reserve(nblines + 1);
1768 output.push_back(iTable.at(0));
1769
1770 // Create table
1771 for (int i = 1; i < nblines; ++i) {
1772 QStringList newLine;
1773 newLine.reserve(nbCols + 1);
1774 newLine.push_back(iTable.at(i).at(0));
1775
1776 double valInitial = 0;
1777
1778 for (int j = 1; j < nbCols; ++j) {
1779 double val = SKGServices::stringToDouble(iTable.at(i).at(j));
1780 if (j == 1) {
1781 valInitial = val;
1782 val = 100.0;
1783 } else {
1784 if (valInitial != 0.0) {
1785 val = 100.0 * val / valInitial;
1786 }
1787 }
1788 newLine.push_back(SKGServices::doubleToString(val));
1789 }
1790 output.push_back(newLine);
1791 }
1792
1793 return output;
1794 }
1795
getPercentTable(const SKGStringListList & iTable,bool iOfColumns,bool iAbsolute)1796 SKGStringListList SKGServices::getPercentTable(const SKGStringListList& iTable, bool iOfColumns, bool iAbsolute)
1797 {
1798 SKGTRACEINFUNC(10)
1799
1800 // Build history
1801 SKGStringListList output;
1802 int nblines = iTable.count();
1803 int nbCols = 0;
1804 if (nblines != 0) {
1805 nbCols = iTable.at(0).count();
1806 }
1807
1808 output.reserve(nblines + 1);
1809 output.push_back(iTable.at(0));
1810
1811 // Compute sums
1812 QList<double> sums;
1813 if (iOfColumns) {
1814 // Compute sum of columns
1815 sums.reserve(nbCols);
1816 for (int j = 1; j < nbCols; ++j) {
1817 // Compute sum
1818 double sum = 0;
1819 for (int i = 1; i < nblines; ++i) {
1820 double v = SKGServices::stringToDouble(iTable.at(i).at(j));
1821 sum += (iAbsolute ? qAbs(v) : v);
1822 }
1823
1824 sums.push_back(sum);
1825 }
1826 } else {
1827 // Compute sum of lines
1828 sums.reserve(nblines);
1829 for (int j = 1; j < nblines; ++j) {
1830 // Compute sum
1831 double sum = 0;
1832 for (int i = 1; i < nbCols; ++i) {
1833 double v = SKGServices::stringToDouble(iTable.at(j).at(i));
1834 sum += (iAbsolute ? qAbs(v) : v);
1835 }
1836
1837 sums.push_back(sum);
1838 }
1839 }
1840
1841 // Create table
1842 for (int i = 1; i < nblines; ++i) {
1843 QStringList newLine;
1844 newLine.reserve(nbCols + 1);
1845 newLine.push_back(iTable.at(i).at(0));
1846
1847 for (int j = 1; j < nbCols; ++j) {
1848 double val = SKGServices::stringToDouble(iTable.at(i).at(j));
1849 val = (iAbsolute ? qAbs(val) : val);
1850 double sum = (iOfColumns ? sums.at(j - 1) : sums.at(i - 1));
1851 newLine.push_back(SKGServices::doubleToString(sum == 0.0 ? 0.0 : 100.0 * val / sum));
1852 }
1853 output.push_back(newLine);
1854 }
1855
1856 return output;
1857 }
1858
getHistorizedTable(const SKGStringListList & iTable)1859 SKGStringListList SKGServices::getHistorizedTable(const SKGStringListList& iTable)
1860 {
1861 SKGTRACEINFUNC(10)
1862
1863 // Build history
1864 SKGStringListList output;
1865 int nblines = iTable.count();
1866 int nbCols = 0;
1867 if (nblines != 0) {
1868 nbCols = iTable.at(0).count();
1869 }
1870
1871 output.reserve(nblines + 1);
1872 output.push_back(iTable.at(0));
1873 for (int i = 1; i < nblines; ++i) {
1874 QStringList newLine;
1875 newLine.reserve(nbCols + 1);
1876 newLine.push_back(iTable.at(i).at(0));
1877
1878 double sum = 0;
1879 for (int j = 1; j < nbCols; ++j) {
1880 sum += SKGServices::stringToDouble(iTable.at(i).at(j));
1881 newLine.push_back(SKGServices::doubleToString(sum));
1882 }
1883 output.push_back(newLine);
1884 }
1885
1886 return output;
1887 }
1888
encodeForUrl(const QString & iString)1889 QString SKGServices::encodeForUrl(const QString& iString)
1890 {
1891 return QUrl::toPercentEncoding(iString);
1892 }
1893
fromTheme(const QString & iName,const QStringList & iOverlays)1894 QIcon SKGServices::fromTheme(const QString& iName, const QStringList& iOverlays)
1895 {
1896 QIcon output;
1897 if (!iOverlays.isEmpty()) {
1898 output = KDE::icon(iName, iOverlays);
1899 } else {
1900 output = KDE::icon(iName);
1901 }
1902 if (output.isNull() && !iName.isEmpty()) {
1903 static QHash<QString, QString> alternatives;
1904 if (alternatives.count() == 0) {
1905 // Build alternatives
1906 alternatives[QStringLiteral("arrow-down")] = QStringLiteral("go-down");
1907 alternatives[QStringLiteral("arrow-right")] = QStringLiteral("go-next");
1908 alternatives[QStringLiteral("arrow-up")] = QStringLiteral("go-up");
1909 alternatives[QStringLiteral("arrow-down-double")] = QStringLiteral("go-down");
1910 alternatives[QStringLiteral("arrow-up-double")] = QStringLiteral("go-up");
1911 alternatives[QStringLiteral("bookmark")] = QStringLiteral("bookmark-new");
1912 alternatives[QStringLiteral("bookmarks")] = QStringLiteral("bookmark-new");
1913 alternatives[QStringLiteral("checkbox")] = QStringLiteral("emblem-symbolic-link");
1914 alternatives[QStringLiteral("chronometer")] = QStringLiteral("appointment");
1915 alternatives[QStringLiteral("configure")] = QStringLiteral("preferences-desktop");
1916 alternatives[QStringLiteral("dashboard-show")] = QStringLiteral("user-desktop");
1917 alternatives[QStringLiteral("dialog-cancel")] = QStringLiteral("process-stop");
1918 alternatives[QStringLiteral("dialog-close")] = QStringLiteral("process-stop");
1919 alternatives[QStringLiteral("dialog-ok")] = QLatin1String("");
1920 alternatives[QStringLiteral("download-later")] = QStringLiteral("internet-services");
1921 alternatives[QStringLiteral("download")] = QStringLiteral("internet-services");
1922 alternatives[QStringLiteral("draw-freehand")] = QStringLiteral("accessories-text-editor");
1923 alternatives[QStringLiteral("edit-guides")] = QStringLiteral("text-x-generic");
1924 alternatives[QStringLiteral("edit-rename")] = QStringLiteral("accessories-text-editor");
1925 alternatives[QStringLiteral("emblem-locked")] = QStringLiteral("lock");
1926 alternatives[QStringLiteral("exchange-positions")] = QLatin1String("");
1927 alternatives[QStringLiteral("format-fill-color")] = QLatin1String("");
1928 alternatives[QStringLiteral("games-solve")] = QStringLiteral("application-certificate");
1929 alternatives[QStringLiteral("get-hot-new-stuff")] = QStringLiteral("applications-other");
1930 alternatives[QStringLiteral("irc-operator")] = QLatin1String("");
1931 alternatives[QStringLiteral("ktip")] = QStringLiteral("dialog-information");
1932 alternatives[QStringLiteral("labplot-xy-plot-two-axes-centered-origin")] = QStringLiteral("x-office-spreadsheet");
1933 alternatives[QStringLiteral("layer-visible-off")] = QLatin1String("");
1934 alternatives[QStringLiteral("layer-visible-on")] = QLatin1String("");
1935 alternatives[QStringLiteral("merge")] = QLatin1String("");
1936 alternatives[QStringLiteral("office-chart-area")] = QStringLiteral("x-office-spreadsheet");
1937 alternatives[QStringLiteral("office-chart-area-stacked")] = QStringLiteral("x-office-spreadsheet");
1938 alternatives[QStringLiteral("office-chart-bar-percentage")] = QStringLiteral("x-office-spreadsheet");
1939 alternatives[QStringLiteral("office-chart-bar")] = QStringLiteral("x-office-spreadsheet");
1940 alternatives[QStringLiteral("office-chart-bar-stacked")] = QStringLiteral("x-office-spreadsheet");
1941 alternatives[QStringLiteral("office-chart-line")] = QStringLiteral("x-office-spreadsheet");
1942 alternatives[QStringLiteral("office-chart-line-stacked")] = QStringLiteral("x-office-spreadsheet");
1943 alternatives[QStringLiteral("office-chart-pie")] = QStringLiteral("x-office-spreadsheet");
1944 alternatives[QStringLiteral("office-chart-ring")] = QStringLiteral("x-office-spreadsheet");
1945 alternatives[QStringLiteral("map-flat")] = QStringLiteral("x-office-spreadsheet");
1946 alternatives[QStringLiteral("office-chart-scatter")] = QStringLiteral("x-office-spreadsheet");
1947 alternatives[QStringLiteral("preview")] = QStringLiteral("document-print-preview");
1948 alternatives[QStringLiteral("quickopen")] = QStringLiteral("emblem-symbolic-link");
1949 alternatives[QStringLiteral("run-build-configure")] = QStringLiteral("media-playback-start");
1950 alternatives[QStringLiteral("run-build")] = QStringLiteral("media-playback-start");
1951 alternatives[QStringLiteral("show-menu")] = QStringLiteral("applications-system");
1952 alternatives[QStringLiteral("skrooge_category")] = QStringLiteral("folder-open");
1953 alternatives[QStringLiteral("split")] = QStringLiteral("edit-cut");
1954 alternatives[QStringLiteral("taxes-finances")] = QStringLiteral("fonts");
1955 alternatives[QStringLiteral("tools-wizard")] = QStringLiteral("applications-other");
1956 alternatives[QStringLiteral("user-group-properties")] = QStringLiteral("system-users");
1957 alternatives[QStringLiteral("user-properties")] = QStringLiteral("document-properties");
1958 alternatives[QStringLiteral("utilities-file-archiver")] = QStringLiteral("package-x-generic");
1959 alternatives[QStringLiteral("vcs-conflicting")] = QStringLiteral("dialog-warning");
1960 alternatives[QStringLiteral("vcs-normal")] = QStringLiteral("dialog-information");
1961 alternatives[QStringLiteral("view-bank-account-checking")] = QStringLiteral("go-home");
1962 alternatives[QStringLiteral("view-bank-account")] = QStringLiteral("x-office-address-book");
1963 alternatives[QStringLiteral("view-bank-account-savings")] = QStringLiteral("go-home");
1964 alternatives[QStringLiteral("view-bank")] = QStringLiteral("go-home");
1965 alternatives[QStringLiteral("view-calendar-journal")] = QStringLiteral("x-office-calendar");
1966 alternatives[QStringLiteral("view-calendar-month")] = QStringLiteral("x-office-calendar");
1967 alternatives[QStringLiteral("view-calendar")] = QStringLiteral("x-office-calendar");
1968 alternatives[QStringLiteral("view-calendar-week")] = QStringLiteral("x-office-calendar");
1969 alternatives[QStringLiteral("view-calendar-whatsnext")] = QStringLiteral("x-office-calendar");
1970 alternatives[QStringLiteral("view-categories")] = QStringLiteral("folder-open");
1971 alternatives[QStringLiteral("view-categories-expenditures")] = QStringLiteral("face-sad");
1972 alternatives[QStringLiteral("view-categories-incomes")] = QStringLiteral("face-smile");
1973 alternatives[QStringLiteral("view-file-columns")] = QStringLiteral("go-home");
1974 alternatives[QStringLiteral("view-financial-list")] = QStringLiteral("go-home");
1975 alternatives[QStringLiteral("view-investment")] = QStringLiteral("go-home");
1976 alternatives[QStringLiteral("view-list-details")] = QStringLiteral("go-home");
1977 alternatives[QStringLiteral("view-list-text")] = QStringLiteral("go-home");
1978 alternatives[QStringLiteral("view-pim-calendar")] = QStringLiteral("x-office-spreadsheet");
1979 alternatives[QStringLiteral("view-statistics")] = QStringLiteral("x-office-spreadsheet");
1980 alternatives[QStringLiteral("window-duplicate")] = QStringLiteral("edit-copy");
1981 alternatives[QStringLiteral("zoom-fit-width")] = QStringLiteral("media-playback-stop");
1982 alternatives[QStringLiteral("smallclock")] = QLatin1String("");
1983 alternatives[QStringLiteral("edit_undo")] = QStringLiteral("edit-undo");
1984 alternatives[QStringLiteral("nextuntranslated")] = QStringLiteral("debug-execute-to-cursor");
1985 alternatives[QStringLiteral("format-precision-less")] = QStringLiteral("visibility");
1986 alternatives[QStringLiteral("format-indent-more")] = QStringLiteral("go-next");
1987 alternatives[QStringLiteral("format-indent-less")] = QStringLiteral("go-previous");
1988 alternatives[QStringLiteral("crosshairs")] = QStringLiteral("emblem-symbolic-link");
1989 }
1990 bool alternativeEmpty = false;
1991 if (alternatives.contains(iName)) {
1992 auto alternative = alternatives.value(iName);
1993 alternativeEmpty = (alternative.isEmpty());
1994 if (!alternativeEmpty) {
1995 if (!iOverlays.isEmpty()) {
1996 output = KDE::icon(alternative, iOverlays);
1997 } else {
1998 output = KDE::icon(alternative);
1999 }
2000 }
2001 }
2002 if (output.isNull() && !alternativeEmpty) {
2003 SKGTRACE << "WARNING: Icon [" << iName << "] not found" << SKGENDL;
2004 output = KDE::icon(QStringLiteral("script-error"));
2005 if (output.isNull()) {
2006 output = KDE::icon(QStringLiteral("image-missing"));
2007 }
2008 }
2009 }
2010 return output;
2011 }
2012
getMajorVersion(const QString & iVersion)2013 QString SKGServices::getMajorVersion(const QString& iVersion)
2014 {
2015 QString output = iVersion;
2016 int pos = output.indexOf('.');
2017 if (pos != -1) {
2018 pos = output.indexOf('.', pos + 1);
2019 if (pos != -1) {
2020 output = output.left(pos);
2021 }
2022 }
2023 return output;
2024 }
2025
getFullPathCommandLine(const QString & iCommandLine)2026 QString SKGServices::getFullPathCommandLine(const QString& iCommandLine)
2027 {
2028 QString output = iCommandLine;
2029 if (!output.isEmpty()) {
2030 auto pathWords = SKGServices::splitCSVLine(output, QLatin1Char(' '));
2031 QString fullpath = QStandardPaths::locate(QStandardPaths::GenericDataLocation, qApp->applicationName() % "/" % pathWords.at(0));
2032 if (!fullpath.isEmpty()) {
2033 pathWords[0] = fullpath;
2034 output = pathWords.join(QLatin1Char(' '));
2035 }
2036 }
2037 return output;
2038 }
2039