skrooge/skgbasemodeler/skgservices.cpp

0001 /***************************************************************************
0002  * SPDX-FileCopyrightText: 2022 S. MANKOWSKI stephane@mankowski.fr
0003  * SPDX-FileCopyrightText: 2022 G. DE BURE support@mankowski.fr
0004  * SPDX-License-Identifier: GPL-3.0-or-later
0005  ***************************************************************************/
0006 /** @file
0007 * This file implements classes SKGServices.
0008 *
0009 * @author Stephane MANKOWSKI / Guillaume DE BURE
0010  */
0011 #include "skgservices.h"
0012
0013 #include <kaboutdata.h>
0014 #include <kiconloader.h>
0015 #include <kio/filecopyjob.h>
0016 #include <klocalizedstring.h>
0017 #ifndef Q_OS_WIN
0018 #include <sys/time.h>
0019 #endif
0020
0021 #include <qapplication.h>
0022 #include <qca.h>
0023 #include <qdom.h>
0024 #include <qfile.h>
0025 #include <qlocale.h>
0026 #include <qmath.h>
0027 #include <qregularexpression.h>
0028 #include <qsavefile.h>
0029 #include <qscriptengine.h>
0030 #include <qsqldatabase.h>
0031 #include <qsqldriver.h>
0032 #include <qsqlerror.h>
0033 #include <qsqlquery.h>
0034 #include <qsqlrecord.h>
0035 #include <qstandardpaths.h>
0036 #include <qtemporaryfile.h>
0037 #include <qvariant.h>
0038
0039 #include "skgdocument.h"
0040 #include "skgtraces.h"
0041
0042 #define SQLCIPHERHEARDER "SQLCipher format"
0043
0044 int SKGServices::SKGSqlTraces = (SKGServices::getEnvVariable(QStringLiteral("SKGTRACESQL")).isEmpty() ? -1 : SKGServices::stringToInt(SKGServices::getEnvVariable(QStringLiteral("SKGTRACESQL"))));
0045
0046 SKGError SKGServices::m_lastCallbackError;
0047
0048 QString SKGServices::searchCriteriasToWhereClause(const SKGServices::SKGSearchCriteriaList& iSearchCriterias, const QStringList& iAttributes, const SKGDocument* iDocument, bool iForDisplay)
0049 {
0050     QString whereclause;
0051     int nbCriterias = iSearchCriterias.count();
0052     int nbAttributes = iAttributes.count();
0053     for (int i = 0; i < nbCriterias; ++i) {
0054         SKGSearchCriteria criteria = iSearchCriterias.at(i);
0055         QString subWhereClause;
0056
0057         int nbWords = criteria.words.count();
0058         for (int w = 0; w < nbWords; ++w) {
0059             QString subWhereClause2;
0060
0061             QString word = criteria.words[w].toLower();
0062             QString att;
0063             QString op(':');
0064             bool modeStartWith = true;
0065
0066             // Check if the word follows the format attribute:value
0067             int pos = word.indexOf(QStringLiteral(":"));
0068             int pos2 = word.indexOf(QStringLiteral("<="));
0069             int pos3 = word.indexOf(QStringLiteral(">="));
0070             int pos4 = word.indexOf(QStringLiteral("="));
0071             int pos5 = word.indexOf(QStringLiteral("<"));
0072             int pos6 = word.indexOf(QStringLiteral(">"));
0073             int pos7 = word.indexOf(QStringLiteral("#"));
0074             int opLength = 1;
0075             if (pos2 != -1 && (pos2 < pos || pos == -1)) {
0076                 pos = pos2;
0077                 opLength = 2;
0078             }
0079             if (pos3 != -1 && (pos3 < pos || pos == -1)) {
0080                 pos = pos3;
0081                 opLength = 2;
0082             }
0083             if (pos4 != -1 && (pos4 < pos || pos == -1)) {
0084                 pos = pos4;
0085             }
0086             if (pos5 != -1 && (pos5 < pos || pos == -1)) {
0087                 pos = pos5;
0088             }
0089             if (pos6 != -1 && (pos6 < pos || pos == -1)) {
0090                 pos = pos6;
0091             }
0092             if (pos7 != -1 && (pos7 < pos || pos == -1)) {
0093                 pos = pos7;
0094             }
0095
0096             if (pos != -1) {
0097                 att = word.left(pos);
0098                 if (att.endsWith(QStringLiteral("."))) {
0099                     modeStartWith = false;
0100                     att = att.left(att.count() - 1);
0101                 }
0102                 op = word.mid(pos, opLength);
0103                 word = word.right(word.count() - pos - op.count());
0104             }
0105
0106             word = SKGServices::stringToSqlString(word);
0107
0108             for (int j = 0; j < nbAttributes; ++j) {
0109                 QString attDatabase = iAttributes.at(j);
0110                 QString attForComparison = (iDocument != nullptr ? iDocument->getDisplay(attDatabase) : attDatabase).toLower();
0111                 if (att.isEmpty() ||
0112                     (modeStartWith && attForComparison.startsWith(att)) ||
0113                     (!modeStartWith && attForComparison.compare(att, Qt::CaseInsensitive) == 0)) {
0114                     if (iForDisplay) {
0115                         QString n = attForComparison + op + word;
0116                         if (subWhereClause2.isEmpty()) {
0117                             subWhereClause2 = n;
0118                         } else {
0119                             subWhereClause2 = i18nc("Logical condition", "%1 or %2", subWhereClause2, n);
0120                         }
0121                     } else {
0122                         if (!subWhereClause2.isEmpty()) {
0123                             subWhereClause2 = subWhereClause2 % " OR ";
0124                         }
0125
0126                         if (attDatabase.startsWith(QLatin1String("p_"))) {
0127                             // Case property
0128                             QString propName = attDatabase.right(attDatabase.length() - 2);
0129                             if (op == QStringLiteral(":")) {
0130                                 subWhereClause2 = subWhereClause2 % "i_PROPPNAME='" % SKGServices::stringToSqlString(propName) % "' AND (lower(i_PROPVALUE) LIKE '%" % word % "%')";
0131                             } else if (op == QStringLiteral("#")) {
0132                                 subWhereClause2 = subWhereClause2 % "i_PROPPNAME='" % SKGServices::stringToSqlString(propName) % "' AND REGEXP('" % word % "',i_PROPVALUE)";
0133                             } else {
0134                                 attDatabase = "i_PROPPNAME='" % SKGServices::stringToSqlString(propName) % "' AND i_PROPVALUE";
0135                                 subWhereClause2 = subWhereClause2 % attDatabase % op % word;
0136                             }
0137                         } else {
0138                             // Case normal attribute
0139                             if (op == QStringLiteral(":")) {
0140                                 subWhereClause2 = subWhereClause2 % "lower(" % attDatabase % ") LIKE '%" % word % "%'";
0141                             } else if (op == QStringLiteral("#")) {
0142                                 subWhereClause2 = subWhereClause2 % "REGEXP('" % word % "'," % attDatabase % ")";
0143                             } else {
0144                                 if (attDatabase.startsWith(QLatin1String("f_")) || attDatabase.startsWith(QLatin1String("i_"))) {
0145                                     subWhereClause2 = subWhereClause2 % attDatabase % op % word;
0146                                 } else {
0147                                     subWhereClause2 = subWhereClause2 % "lower(" % attDatabase % ")" % op % "'" % word % "'";
0148                                 }
0149                             }
0150                         }
0151                     }
0152                 }
0153             }
0154             if (iForDisplay) {
0155                 if (!subWhereClause2.isEmpty()) {
0156                     if (subWhereClause.isEmpty()) {
0157                         subWhereClause = subWhereClause2;
0158                     } else {
0159                         subWhereClause = i18nc("Logical condition", "(%1) and (%2)", subWhereClause, subWhereClause2);
0160                     }
0161                 }
0162             } else {
0163                 if (!subWhereClause2.isEmpty()) {
0164                     if (!subWhereClause.isEmpty()) {
0165                         subWhereClause = subWhereClause % " AND ";
0166                     }
0167                     subWhereClause = subWhereClause % "(" % subWhereClause2 % ")";
0168                 } else {
0169                     subWhereClause = QStringLiteral("1=0");
0170                 }
0171             }
0172         }
0173         if (iForDisplay) {
0174             if (!subWhereClause.isEmpty()) {
0175                 if (criteria.mode == '+') {
0176                     if (whereclause.isEmpty()) {
0177                         whereclause = subWhereClause;
0178                     } else {
0179                         whereclause = i18nc("Logical condition", "(%1) and (%2)", whereclause, subWhereClause);
0180                     }
0181                 } else if (criteria.mode == '-') {
0182                     if (subWhereClause.isEmpty()) {
0183                         whereclause = i18nc("Logical condition", "not (%1)", subWhereClause);
0184                     } else {
0185                         whereclause = i18nc("Logical condition", "(%1) and not (%2)", whereclause, subWhereClause);
0186                     }
0187                 }
0188             }
0189         } else {
0190             if (!subWhereClause.isEmpty()) {
0191                 if (criteria.mode == '+') {
0192                     if (!whereclause.isEmpty()) {
0193                         whereclause = whereclause % " OR ";
0194                     }
0195                     whereclause = whereclause % "(" % subWhereClause % ")";
0196                 } else if (criteria.mode == '-') {
0197                     if (!whereclause.isEmpty()) {
0198                         whereclause = whereclause % " AND NOT";
0199                     } else {
0200                         whereclause = QStringLiteral("NOT");
0201                     }
0202                     whereclause = whereclause % "(" % subWhereClause % ")";
0203                 }
0204             }
0205         }
0206     }
0207     return whereclause;
0208 }
0209
0210 SKGServices::SKGSearchCriteriaList SKGServices::stringToSearchCriterias(const QString& iString)
0211 {
0212     SKGServices::SKGSearchCriteriaList output;
0213
0214     QStringList words = SKGServices::splitCSVLine(iString, ' ', true);
0215
0216     int nbwords = words.count();
0217     output.reserve(nbwords);
0218
0219     SKGServices::SKGSearchCriteria criteria;
0220     criteria.mode = '+';
0221     bool atLeastOnePlus = false;
0222     for (int i = 0; i < nbwords; ++i) {
0223         QString word = words.at(i);
0224         bool isWordStartingByPlus = word.startsWith(QLatin1String("+"));
0225         bool isWordStartingByLess = word.startsWith(QLatin1String("-"));
0226         if (isWordStartingByPlus || isWordStartingByLess) {
0227             QChar nextChar;
0228             if (word.count() > 1) {
0229                 nextChar = word[1];
0230             }
0231             if (nextChar < '0' || nextChar > '9') {
0232                 word = word.right(word.length() - 1);
0233                 if (Q_LIKELY(i != 0)) {
0234                     if (criteria.mode == '-') {
0235                         output.push_back(criteria);
0236                     } else {
0237                         output.push_front(criteria);
0238                         atLeastOnePlus = true;
0239                     }
0240                 }
0241                 criteria.words.clear();
0242                 criteria.mode = (isWordStartingByPlus ? '+' : '-');
0243             }
0244         }
0245         criteria.words.push_back(word);
0246     }
0247     if (criteria.mode == '-') {
0248         output.push_back(criteria);
0249     } else {
0250         output.push_front(criteria);
0251         atLeastOnePlus = true;
0252     }
0253
0254     if (!atLeastOnePlus) {
0255         // Add a '+' always true
0256         SKGServices::SKGSearchCriteria criteria2;
0257         criteria2.mode = '+';
0258         criteria2.words.push_back(QLatin1String(""));
0259         output.push_front(criteria2);
0260     }
0261
0262     return output;
0263 }
0264
0265 QString SKGServices::getEnvVariable(const QString& iAttribute)
0266 {
0267     return QString::fromUtf8(qgetenv(iAttribute.toUtf8().constData()));
0268 }
0269
0270 QString SKGServices::intToString(qlonglong iNumber)
0271 {
0272     QString output;
0273     output.setNum(iNumber);
0274     return output;
0275 }
0276
0277 qlonglong SKGServices::stringToInt(const QString& iNumber)
0278 {
0279     if (Q_UNLIKELY(iNumber.isEmpty())) {
0280         return 0;
0281     }
0282
0283     bool ok;
0284     qlonglong output = iNumber.toLongLong(&ok);
0285     if (Q_LIKELY(!ok)) {
0286         SKGTRACE << "WARNING: SKGServices::stringToInt(" << iNumber << ") failed" << SKGENDL;
0287     }
0288
0289     return output;
0290 }
0291
0292 QString SKGServices::stringToSqlString(const QString& iString)
0293 {
0294     QString output;
0295
0296     for (const auto& c : iString) {
0297         if (c.isPrint() || c == QChar('\n')) {
0298             output.append(QChar(c));
0299         }
0300     }
0301
0302     output.replace('\'', QStringLiteral("''"));
0303     return output;
0304 }
0305
0306 QString SKGServices::stringToHtml(const QString& iString)
0307 {
0308     QString output = iString;
0309     output.replace('&', QStringLiteral("&amp;"));  // Must be done first
0310     output.replace('<', QStringLiteral("&lt;"));
0311     output.replace('>', QStringLiteral("&gt;"));
0312     output.replace('"', QStringLiteral("&quot;"));
0313
0314     return output;
0315 }
0316
0317 QString SKGServices::htmlToString(const QString& iString)
0318 {
0319     QString output = iString;
0320     output.replace(QStringLiteral("&lt;"), QStringLiteral("<"));
0321     output.replace(QStringLiteral("&gt;"), QStringLiteral(">"));
0322     output.replace(QStringLiteral("&quot;"), QStringLiteral("\""));
0323     output.replace(QStringLiteral("&amp;"), QStringLiteral("&"));
0324
0325     return output;
0326 }
0327
0328 QString SKGServices::stringsToCsv(const QStringList& iList, QChar iSeparator)
0329 {
0330     QString output;
0331     int nb = iList.count();
0332     for (int i = 0; i < nb; ++i) {
0333         output.append(SKGServices::stringToCsv(iList.at(i)));
0334         if (Q_LIKELY(i < nb - 1)) {
0335             output.append(iSeparator);
0336         }
0337     }
0338
0339     return output;
0340 }
0341
0342 QString SKGServices::stringToCsv(const QString& iNumber)
0343 {
0344     QString output = iNumber;
0345     output.replace('"', QStringLiteral("#SKGDOUBLECOTE#"));
0346     output.replace(QStringLiteral("#SKGDOUBLECOTE#"), QStringLiteral("\"\""));
0347     output = '"' % output % '"';
0348     return output;
0349 }
0350
0351 double SKGServices::stringToDouble(const QString& iNumber)
0352 {
0353     if (Q_UNLIKELY(iNumber.isEmpty() || iNumber == QStringLiteral("nan"))) {
0354         return 0;
0355     }
0356     if (Q_UNLIKELY(iNumber == QStringLiteral("inf"))) {
0357         return 1e300;
0358     }
0359     if (Q_UNLIKELY(iNumber == QStringLiteral("-inf"))) {
0360         return -1e300;
0361     }
0362     QString number = iNumber;
0363     number.remove(QRegularExpression(QStringLiteral("[^0-9-+/eE,.]")));
0364     if (number.contains(QStringLiteral("/"))) {
0365         // Use script engine
0366         QScriptEngine myEngine;
0367         QScriptValue result = myEngine.evaluate(number);
0368         if (result.isNumber()) {
0369             return result.toNumber();
0370         }
0371     }
0372
0373     bool ok;
0374     double output = number.toDouble(&ok);
0375     if (Q_LIKELY(!ok)) {
0376         QString tmp = number;
0377         tmp.replace(',', '.');
0378         if (tmp.count('.') > 1) {
0379             tmp.remove(tmp.indexOf('.'), 1);
0380         }
0381         output = tmp.toDouble(&ok);
0382         if (Q_LIKELY(!ok)) {
0383             QString tmp2 = number;
0384             tmp2.replace('.', ',');
0385             if (tmp2.count(',') > 1) {
0386                 tmp2.remove(tmp2.indexOf(','), 1);
0387             }
0388             output = tmp2.toDouble(&ok);
0389             if (!ok) {
0390                 QString tmp3 = number;
0391                 tmp3.remove(',');
0392                 output = tmp3.toDouble(&ok);
0393             }
0394         }
0395     }
0396     if (Q_LIKELY(!ok)) {
0397         SKGTRACE << "WARNING: SKGServices::stringToDouble(" << iNumber << ") failed" << SKGENDL;
0398     }
0399     return output;
0400 }
0401
0402 QString SKGServices::doubleToString(double iNumber)
0403 {
0404     QString output;
0405     output.setNum(iNumber, 'g', 10);
0406     return output;
0407 }
0408
0409 QString SKGServices::getNextString(const QString& iString)
0410 {
0411     QString output = iString;
0412     bool ok;
0413     qlonglong val = output.toLongLong(&ok);
0414     if (Q_LIKELY(ok)) {
0415         // This is a int
0416         output = SKGServices::intToString(val + 1);
0417     } else {
0418         // This is a string
0419         output = QLatin1String("");
0420     }
0421     return output;
0422 }
0423
0424 QString SKGServices::dateToPeriod(QDate iDate, const QString& iPeriod)
0425 {
0426     QString period;
0427     if (iPeriod == QStringLiteral("D")) {
0428         // Day
0429         period = iDate.toString(QStringLiteral("yyyy-MM-dd"));
0430     } else if (iPeriod == QStringLiteral("W")) {
0431         // Week
0432         int yearNumber;
0433         auto weekNumber = iDate.weekNumber(&yearNumber);
0434         period = SKGServices::intToString(yearNumber) % "-W" % SKGServices::intToString(weekNumber).rightJustified(2, '0');
0435     } else if (iPeriod == QStringLiteral("M")) {
0436         // Month
0437         period = iDate.toString(QStringLiteral("yyyy-MM"));
0438     } else if (iPeriod == QStringLiteral("Q")) {
0439         // Quarter
0440         period = iDate.toString(QStringLiteral("yyyy-Q")) % (iDate.month() <= 3 ? '1' : (iDate.month() <= 6 ? '2' : (iDate.month() <= 9 ? '3' : '4')));
0441     } else if (iPeriod == QStringLiteral("S")) {
0442         // Semester
0443         period = iDate.toString(QStringLiteral("yyyy-S")) % (iDate.month() <= 6 ? '1' : '2');
0444     } else if (iPeriod == QStringLiteral("Y")) {
0445         // Year
0446         period = iDate.toString(QStringLiteral("yyyy"));
0447     }
0448     return period;
0449 }
0450
0451 QString SKGServices::timeToString(const QDateTime& iDateTime)
0452 {
0453     QDateTime d = iDateTime;
0454     if (Q_UNLIKELY(!d.isValid())) {
0455         d = QDateTime::currentDateTime();
0456     }
0457     return d.toString(QStringLiteral("yyyy-MM-dd HH:mm:ss"));
0458 }
0459
0460 QString SKGServices::dateToSqlString(QDate iDate)
0461 {
0462 #if QT_VERSION < QT_VERSION_CHECK(5, 14, 0)
0463     return dateToSqlString(QDateTime(iDate));
0464 #else
0465     return dateToSqlString(iDate.startOfDay());
0466 #endif
0467 }
0468
0469 QString SKGServices::dateToSqlString(const QDateTime& iDateTime)
0470 {
0471     QDateTime d = iDateTime;
0472     if (Q_UNLIKELY(!d.isValid())) {
0473         d = QDateTime::currentDateTime();
0474     }
0475     return d.toString(QStringLiteral("yyyy-MM-dd"));
0476 }
0477
0478 int SKGServices::nbWorkingDays(QDate iFrom, QDate iTo)
0479 {
0480     int nb = 0;
0481     QDate min = (iFrom < iTo ? iFrom : iTo);
0482     QDate max = (iFrom < iTo ? iTo : iFrom);
0483
0484     while (min != max) {
0485         if (min.dayOfWeek() <= 5) {
0486             ++nb;
0487         }
0488         min = min.addDays(1);
0489     }
0490     if (nb == 0) {
0491         nb = 1;
0492     }
0493     return nb;
0494 }
0495
0496 QDateTime SKGServices::stringToTime(const QString& iDateString)
0497 {
0498     QDateTime output = QDateTime::fromString(iDateString, QStringLiteral("yyyy-MM-dd HH:mm:ss"));
0499     if (Q_UNLIKELY(!output.isValid())) {
0500         output = QDateTime::fromString(iDateString, QStringLiteral("yyyy-MM-dd"));
0501     }
0502
0503     return output;
0504 }
0505
0506 QDate SKGServices::partialStringToDate(const QString& iDateString, bool iFixupBackward)
0507 {
0508     QDate result;
0509     QStringList items = iDateString.split('/');
0510     int size = items.count();
0511     bool ok = false;
0512
0513     if (size == 1) {
0514         int dayCount = items.at(0).toInt(&ok);
0515
0516         result = QDate(QDate::currentDate().year(), QDate::currentDate().month(), dayCount);
0517
0518         if (iFixupBackward) {
0519             if (result > QDate::currentDate()) {
0520                 result = result.addMonths(-1);
0521             }
0522         } else {
0523             if (result < QDate::currentDate()) {
0524                 result = result.addMonths(1);
0525             }
0526         }
0527     } else if (size == 2) {
0528         int dayCount = items.at(0).toInt(&ok);
0529         int monthCount = items.at(1).toInt(&ok);
0530
0531         result = QDate(QDate::currentDate().year(), monthCount, dayCount);
0532
0533         if (iFixupBackward) {
0534             if (result > QDate::currentDate()) {
0535                 result = result.addYears(-1);
0536             }
0537         } else {
0538             if (result < QDate::currentDate()) {
0539                 result = result.addYears(1);
0540             }
0541         }
0542     } else if (size == 3) {
0543         int dayCount = items.at(0).toInt(&ok);
0544         int monthCount = items.at(1).toInt(&ok);
0545         int yearCount = items.at(2).toInt(&ok);
0546         int lengthYear = items.at(2).count();
0547
0548         result = QDate(QDate::currentDate().year(), monthCount, dayCount);
0549
0550         if (lengthYear < 4) {
0551             auto y = static_cast<int>(result.year() / qPow(10, lengthYear)) * qPow(10, lengthYear) + yearCount;
0552             if (y > result.year() && iFixupBackward) {
0553                 y = y - qPow(10, lengthYear);
0554             } else if (y < result.year() && !iFixupBackward) {
0555                 y = y + qPow(10, lengthYear);
0556             }
0557             result = result.addYears(y - result.year());
0558         } else {
0559             result = result.addYears(yearCount - result.year());
0560         }
0561     }
0562
0563     if (!ok) {
0564         result = QDate();
0565     }
0566     return result;
0567 }
0568
0569 QStringList SKGServices::splitCSVLine(const QString& iString, QChar iSeparator, bool iCoteDefineBlock)
0570 {
0571     return splitCSVLine(iString, iSeparator, iCoteDefineBlock, nullptr);
0572 }
0573
0574 QStringList SKGServices::splitCSVLine(const QString& iString, QChar iSeparator, bool iCoteDefineBlock, QChar* oRealSeparator)
0575 {
0576     QStringList items;
0577     QString item;
0578     bool isInBlock = false;
0579     QChar realSeparator = iSeparator;
0580
0581     QChar cote = ' ';  // Not yet defined
0582     int nb = iString.length();
0583     items.reserve(nb);
0584     for (int pos = 0; pos < nb; ++pos) {
0585         QChar c = iString.at(pos);
0586         if (isInBlock) {
0587             if (c == cote) {
0588                 if (pos < nb - 1 && iString.at(pos + 1) == cote) {
0589                     ++pos;  // separator escaped
0590                 } else {
0591                     items.push_back(item);
0592                     item.clear();
0593                     isInBlock = false;
0594                     // 320112 vvvv
0595                     // Reset the block character to autorize mix
0596                     cote = ' ';
0597                     // 320112 ^^^^
0598
0599                     if (realSeparator != ' ') while (pos < nb - 1 && iString.at(pos + 1) == ' ') {
0600                             ++pos;
0601                         }
0602                     ++pos;
0603                     if (pos < nb) {
0604                         realSeparator = iString.at(pos);    // To get the real separator
0605                     }
0606                 }
0607             }
0608
0609             if (isInBlock) {
0610                 item += c;
0611             }
0612         } else  if ((c == '\"' || c == '\'') && item.trimmed().isEmpty() && iCoteDefineBlock) {
0613             if (cote == ' ') {
0614                 cote = c;    // Set the real cote char
0615             }
0616             isInBlock = true;
0617             item.clear();
0618         } else  if (QString(c) == realSeparator) {
0619             items.push_back(item);
0620             item.clear();
0621             isInBlock = false;
0622             // 320112 vvvv
0623             // Reset the block character to autorize mix
0624             cote = ' ';
0625             // 320112 ^^^^
0626         } else {
0627             item += c;
0628         }
0629     }
0630
0631     if (!item.isEmpty() || (nb > 0 && iString.at(nb - 1) == realSeparator)) {
0632         items.push_back(item);
0633     }
0634
0635     if (oRealSeparator != nullptr) {
0636         *oRealSeparator = realSeparator;
0637     }
0638
0639     if (isInBlock) {
0640         items.clear();
0641     }
0642
0643     return items;
0644 }
0645
0646 QString SKGServices::getDateFormat(const QStringList& iDates)
0647 {
0648     SKGTRACEINFUNC(2)
0649     bool f_YYYY_MM_DD = true;
0650     bool f_YYYYMMDD = true;
0651     bool f_DDMMYYYY = true;
0652     bool f_MMDDYYYY = true;
0653     bool f_MM_DD_YY = true;
0654     bool f_DD_MM_YY = true;
0655     bool f_MM_DD_YYYY = true;
0656     bool f_DD_MM_YYYY = true;
0657     bool f_DDMMMYYYY = true;
0658     bool f_DD_MMM_YY = true;
0659     bool f_DD_MMM_YYYY = true;
0660
0661     // Build regexp
0662     QRegularExpression rx(QStringLiteral("(.+)-(.+)-(.+)"));
0663
0664     // Check all dates
0665     int nb = iDates.count();
0666     for (int i = 0; i < nb; ++i) {
0667         QString val = iDates.at(i).trimmed();
0668         if (val.count() > 10) {
0669             auto l = SKGServices::splitCSVLine(val, ' ');
0670             val = l[0];
0671         }
0672         if (!val.isEmpty()) {
0673             val = val.replace(' ', '0');
0674             val = val.replace('\\', '-');
0675             val = val.replace('/', '-');
0676             val = val.replace('.', '-');
0677             val = val.replace(QStringLiteral("'20"), QStringLiteral("-20"));
0678             val = val.replace(QStringLiteral("' "), QStringLiteral("-200"));
0679             val = val.replace('\'', QStringLiteral("-20"));
0680             val = val.replace(QStringLiteral("-90"), QStringLiteral("-1990"));
0681             val = val.replace(QStringLiteral("-91"), QStringLiteral("-1991"));
0682             val = val.replace(QStringLiteral("-92"), QStringLiteral("-1992"));
0683             val = val.replace(QStringLiteral("-93"), QStringLiteral("-1993"));
0684             val = val.replace(QStringLiteral("-94"), QStringLiteral("-1994"));
0685             val = val.replace(QStringLiteral("-95"), QStringLiteral("-1995"));
0686             val = val.replace(QStringLiteral("-96"), QStringLiteral("-1996"));
0687             val = val.replace(QStringLiteral("-97"), QStringLiteral("-1997"));
0688             val = val.replace(QStringLiteral("-98"), QStringLiteral("-1998"));
0689             val = val.replace(QStringLiteral("-99"), QStringLiteral("-1999"));
0690             auto match = rx.match(val);
0691             if (!match.hasMatch()) {
0692                 f_YYYY_MM_DD = false;
0693                 f_MM_DD_YY = false;
0694                 f_DD_MM_YY = false;
0695                 f_MM_DD_YYYY = false;
0696                 f_DD_MM_YYYY = false;
0697                 f_DD_MMM_YY = false;
0698                 f_DD_MMM_YYYY = false;
0699
0700                 if (val.length() == 8) {
0701                     auto left2 = SKGServices::stringToInt(val.left(2));
0702                     if (left2 > 12) {
0703                         f_MMDDYYYY = false;
0704                     }
0705                     if (left2 > 31) {
0706                         f_DDMMYYYY = false;
0707                     }
0708
0709                     auto mid2 = SKGServices::stringToInt(val.mid(2, 2));
0710                     if (mid2 > 12) {
0711                         f_DDMMYYYY = false;
0712                     }
0713                     if (mid2 > 31) {
0714                         f_MMDDYYYY = false;
0715                     }
0716
0717                     auto mid4 = SKGServices::stringToInt(val.mid(4, 2));
0718                     if (mid4 > 12) {
0719                         f_YYYYMMDD = false;
0720                     }
0721
0722                     auto right2 = SKGServices::stringToInt(val.right(2));
0723                     if (right2 > 31) {
0724                         f_YYYYMMDD = false;
0725                     }
0726
0727                     f_DDMMMYYYY = false;
0728                 } else if (val.length() == 9) {
0729                     f_MMDDYYYY = false;
0730                     f_DDMMYYYY = false;
0731                     f_YYYYMMDD = false;
0732                 } else {
0733                     f_MMDDYYYY = false;
0734                     f_DDMMYYYY = false;
0735                     f_YYYYMMDD = false;
0736                     f_DDMMMYYYY = false;
0737                 }
0738             } else {
0739                 f_YYYYMMDD = false;
0740                 f_DDMMYYYY = false;
0741                 f_MMDDYYYY = false;
0742                 f_DDMMMYYYY = false;
0743
0744                 QString v1 = match.captured(1);
0745                 QString v2 = match.captured(2);
0746                 QString v3 = match.captured(3);
0747
0748                 if (SKGServices::stringToInt(v1) > 12) {
0749                     f_MM_DD_YY = false;
0750                     f_MM_DD_YYYY = false;
0751                 }
0752
0753                 if (SKGServices::stringToInt(v2) > 12) {
0754                     f_DD_MM_YY = false;
0755                     f_DD_MM_YYYY = false;
0756                 }
0757
0758                 if (v2.length() > 2) {
0759                     f_MM_DD_YY = false;
0760                     f_MM_DD_YYYY = false;
0761                     f_DD_MM_YY = false;
0762                     f_DD_MM_YYYY = false;
0763                     f_YYYY_MM_DD = false;
0764                 }
0765
0766                 if (v2.length() != 3) {
0767                     f_DD_MMM_YYYY = false;
0768                     f_DD_MMM_YY = false;
0769                 }
0770
0771                 if (SKGServices::stringToInt(v1) > 31 || SKGServices::stringToInt(v2) > 31) {
0772                     f_MM_DD_YY = false;
0773                     f_MM_DD_YYYY = false;
0774                     f_DD_MM_YY = false;
0775                     f_DD_MM_YYYY = false;
0776                 }
0777
0778                 if (SKGServices::stringToInt(v3) > 31) {
0779                     f_YYYY_MM_DD = false;
0780                 }
0781
0782                 if (v1.length() == 4) {
0783                     f_MM_DD_YY = false;
0784                     f_DD_MM_YY = false;
0785                     f_MM_DD_YYYY = false;
0786                     f_DD_MM_YYYY = false;
0787                 } else {
0788                     // To be more permissive and support mix of date: f_YYYY_MM_DD = false;
0789                 }
0790
0791                 if (v3.length() == 4) {
0792                     f_YYYY_MM_DD = false;
0793                     f_MM_DD_YY = false;
0794                     f_DD_MM_YY = false;
0795                 } else {
0796                     // To be more permissive and support mix of date: f_MM_DD_YYYY = false;
0797                     // To be more permissive and support mix of date: f_DD_MM_YYYY = false;
0798                 }
0799             }
0800         }
0801     }
0802
0803     if (f_YYYYMMDD) {
0804         return QStringLiteral("YYYYMMDD");
0805     }
0806     if (f_MMDDYYYY) {
0807         return QStringLiteral("MMDDYYYY");
0808     }
0809     if (f_DDMMYYYY) {
0810         return QStringLiteral("DDMMYYYY");
0811     }
0812     if (f_DD_MM_YY && f_MM_DD_YY) {
0813         QString sFormat = QLocale().dateFormat(QLocale::ShortFormat);
0814         if (sFormat.startsWith(QLatin1String("%m")) || sFormat.startsWith(QLatin1String("%n"))) {
0815             return QStringLiteral("MM-DD-YY");
0816         }
0817         return QStringLiteral("DD-MM-YY");
0818     }
0819     if (f_MM_DD_YY) {
0820         return QStringLiteral("MM-DD-YY");
0821     }
0822     if (f_DD_MM_YY) {
0823         return QStringLiteral("DD-MM-YY");
0824     }
0825     if (f_DD_MM_YYYY && f_MM_DD_YYYY) {
0826         QString sFormat = QLocale().dateFormat(QLocale::ShortFormat);
0827         if (sFormat.startsWith(QLatin1String("%m")) || sFormat.startsWith(QLatin1String("%n"))) {
0828             return QStringLiteral("MM-DD-YYYY");
0829         }
0830         return QStringLiteral("DD-MM-YYYY");
0831     }
0832     if (f_MM_DD_YYYY) {
0833         return QStringLiteral("MM-DD-YYYY");
0834     }
0835     if (f_DD_MM_YYYY) {
0836         return QStringLiteral("DD-MM-YYYY");
0837     }
0838     if (f_YYYY_MM_DD) {
0839         return QStringLiteral("YYYY-MM-DD");
0840     }
0841     if (f_DDMMMYYYY) {
0842         return QStringLiteral("DDMMMYYYY");
0843     }
0844     if (f_DD_MMM_YY) {
0845         return QStringLiteral("DD-MMM-YY");
0846     }
0847     if (f_DD_MMM_YYYY) {
0848         return QStringLiteral("DD-MMM-YYYY");
0849     }
0850
0851     return QLatin1String("");
0852 }
0853
0854 QString SKGServices::toPercentageString(double iAmount, int iNbDecimal)
0855 {
0856     return toCurrencyString(iAmount, QString(), iNbDecimal) % " %";
0857 }
0858
0859 QString SKGServices::toCurrencyString(double iAmount, const QString& iSymbol, int iNbDecimal)
0860 {
0861     static auto lc_monetary = (SKGServices::getEnvVariable(QStringLiteral("LC_MONETARY")).isEmpty() ? "" : SKGServices::splitCSVLine(SKGServices::getEnvVariable(QStringLiteral("LC_MONETARY")), '.')[0]);
0862
0863
0864     if (iSymbol == QStringLiteral("%")) {
0865         return toPercentageString(iAmount, iNbDecimal);
0866     }
0867     return QLocale(lc_monetary).toCurrencyString(iAmount, iSymbol.isEmpty() ? QStringLiteral(" ") : iSymbol, iNbDecimal).trimmed();
0868 }
0869
0870 QString SKGServices::dateToSqlString(const QString& iDate, const QString& iFormat)
0871 {
0872     QString input = iDate;
0873     if (input.count() > 10) {
0874         auto l = SKGServices::splitCSVLine(input, ' ');
0875         input = l[0];
0876     }
0877
0878     QString format = QStringLiteral("yyyy-MM-dd");
0879     QString YYYY = QStringLiteral("0000");
0880     QString MM = QStringLiteral("00");
0881     QString DD = QStringLiteral("00");
0882     if (iFormat == QStringLiteral("YYYYMMDD")) {
0883         YYYY = input.mid(0, 4);
0884         MM = input.mid(4, 2);
0885         DD = input.mid(6, 2);
0886     } else if (iFormat == QStringLiteral("DDMMYYYY") || iFormat == QStringLiteral("DDMMYY")) {
0887         YYYY = input.mid(4, 4);
0888         MM = input.mid(2, 2);
0889         DD = input.mid(0, 2);
0890     } else if (iFormat == QStringLiteral("DDMMMYYYY") || iFormat == QStringLiteral("DDMMMYY")) {
0891         YYYY = input.mid(5, 4);
0892         MM = input.mid(2, 3);
0893         DD = input.mid(0, 2);
0894         format = QStringLiteral("yyyy-MMM-dd");
0895     } else if (iFormat == QStringLiteral("MMDDYYYY") || iFormat == QStringLiteral("MMDDYY")) {
0896         YYYY = input.mid(4, 4);
0897         MM = input.mid(0, 2);
0898         DD = input.mid(2, 2);
0899
0900     } else {
0901         QString val = input;
0902         val = val.replace(' ', '0');
0903         val = val.replace('\\', '-');
0904         val = val.replace('/', '-');
0905         val = val.replace('.', '-');
0906         val = val.replace(QStringLiteral("'20"), QStringLiteral("-20"));
0907         val = val.replace(QStringLiteral("' "), QStringLiteral("-200"));
0908         val = val.replace('\'', QStringLiteral("-20"));
0909         val = val.replace(QStringLiteral("-90"), QStringLiteral("-1990"));
0910         val = val.replace(QStringLiteral("-91"), QStringLiteral("-1991"));
0911         val = val.replace(QStringLiteral("-92"), QStringLiteral("-1992"));
0912         val = val.replace(QStringLiteral("-93"), QStringLiteral("-1993"));
0913         val = val.replace(QStringLiteral("-94"), QStringLiteral("-1994"));
0914         val = val.replace(QStringLiteral("-95"), QStringLiteral("-1995"));
0915         val = val.replace(QStringLiteral("-96"), QStringLiteral("-1996"));
0916         val = val.replace(QStringLiteral("-97"), QStringLiteral("-1997"));
0917         val = val.replace(QStringLiteral("-98"), QStringLiteral("-1998"));
0918         val = val.replace(QStringLiteral("-99"), QStringLiteral("-1999"));
0919         QRegularExpression rx(QStringLiteral("(.+)-(.+)-(.+)"));
0920         auto match = rx.match(val);
0921         if (match.hasMatch()) {
0922             QString v1 = match.captured(1);
0923             QString v2 = match.captured(2);
0924             QString v3 = match.captured(3);
0925             if (iFormat == QStringLiteral("YYYY-MM-DD")) {
0926                 YYYY = v1;
0927                 MM = v2;
0928                 DD = v3;
0929             } else if (iFormat == QStringLiteral("MM/DD/YY") || iFormat == QStringLiteral("MM-DD-YY") || iFormat == QStringLiteral("MM/DD/YYYY") || iFormat == QStringLiteral("MM-DD-YYYY")) {
0930                 MM = v1;
0931                 DD = v2;
0932                 YYYY = v3;
0933             } else if (iFormat == QStringLiteral("DD/MM/YY") || iFormat == QStringLiteral("DD-MM-YY") || iFormat == QStringLiteral("DD/MM/YYYY") || iFormat == QStringLiteral("DD-MM-YYYY")) {
0934                 DD = v1;
0935                 MM = v2;
0936                 YYYY = v3;
0937             } else if (iFormat == QStringLiteral("DD/MMM/YY") || iFormat == QStringLiteral("DD-MMM-YY") || iFormat == QStringLiteral("DD/MMM/YYYY") || iFormat == QStringLiteral("DD-MMM-YYYY")) {
0938                 DD = v1;
0939                 MM = v2;
0940                 YYYY = v3;
0941                 format = QStringLiteral("yyyy-MMM-dd");
0942             }
0943         }
0944     }
0945
0946     if (MM.length() == 1) {
0947         MM = '0' % MM;
0948     }
0949     if (DD.length() == 1) {
0950         DD = '0' % DD;
0951     }
0952     if (YYYY.length() == 1) {
0953         YYYY = '0' % YYYY;
0954     }
0955     if (YYYY.length() == 2) {
0956         if (stringToInt(YYYY) > 70) {
0957             YYYY = "19" % YYYY;
0958         } else {
0959             YYYY = "20" % YYYY;
0960         }
0961     }
0962
0963     QString date = YYYY % '-' % MM % '-' % DD;
0964     date.replace(' ', '0');
0965     return dateToSqlString(QDateTime::fromString(date, format));
0966 }
0967
0968 QString SKGServices::getPeriodWhereClause(const QString& iPeriod, const QString& iDateAttribute, const QString& iComparator)
0969 {
0970     QString output = QStringLiteral("1=0");
0971     if (iPeriod == QStringLiteral("ALL")) {
0972         output = QStringLiteral("1=1");
0973     } else if (iPeriod.length() == 4) {
0974         // 2014
0975         output = "STRFTIME('%Y'," + SKGServices::stringToSqlString(iDateAttribute) + ")" + iComparator + "'" + SKGServices::stringToSqlString(iPeriod) + '\'';
0976     } else if (iPeriod.length() == 7 && iPeriod[4] == '-') {
0977         if (iPeriod[5] == 'S') {
0978             // 2014-S1
0979             output = "STRFTIME('%Y'," + SKGServices::stringToSqlString(iDateAttribute) + ")||'-S'||(CASE WHEN STRFTIME('%m'," + SKGServices::stringToSqlString(iDateAttribute) + ")<='06' THEN '1' ELSE '2' END)" + iComparator + "'" + SKGServices::stringToSqlString(iPeriod) + '\'';
0980         } else if (iPeriod[5] == 'Q') {
0981             // 2014-Q1
0982             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) + '\'';
0983         } else {
0984             // 2014-07
0985             output = "STRFTIME('%Y-%m'," + SKGServices::stringToSqlString(iDateAttribute) + ")" + iComparator + "'" + SKGServices::stringToSqlString(iPeriod) + '\'';
0986         }
0987     }
0988     if (iComparator == QStringLiteral("<") || iComparator == QStringLiteral("<=")) {
0989         output = "(" + output + " OR " + iDateAttribute + "='0000-00-00')";
0990     }
0991     return output;
0992 }
0993
0994 QDate SKGServices::periodToDate(const QString& iPeriod)
0995 {
0996     QDate output;
0997
0998     if (iPeriod == QStringLiteral("ALL")) {
0999         output = QDate::currentDate();
1000     } else if (iPeriod.length() == 4) {
1001         // 2014
1002         output = QDate::fromString(iPeriod, QStringLiteral("yyyy")).addYears(1).addDays(-1);
1003     } else if (iPeriod.length() == 7) {
1004         if (iPeriod[5] == 'S') {
1005             // 2014-S1
1006             output = QDate::fromString(iPeriod, QStringLiteral("yyyy-SM"));
1007             output = output.addMonths(output.month() * 6 - output.month());  // convert semester in month
1008             output = output.addMonths(1).addDays(-1);
1009         } else if (iPeriod[5] == 'Q') {
1010             // 2014-Q1
1011             output = QDate::fromString(iPeriod, QStringLiteral("yyyy-QM"));
1012             output = output.addMonths(output.month() * 3 - output.month());  // convert quarter in month
1013             output = output.addMonths(1).addDays(-1);
1014         } else {
1015             // 2014-07
1016             output = QDate::fromString(iPeriod, QStringLiteral("yyyy-MM")).addMonths(1).addDays(-1);
1017         }
1018     }
1019     return output;
1020 }
1021
1022 QString SKGServices::getNeighboringPeriod(const QString& iPeriod, int iDelta)
1023 {
1024     QString output = QStringLiteral("1=0");
1025     if (iPeriod.length() == 4) {
1026         // 2014
1027         QDate date = QDate::fromString(iPeriod, QStringLiteral("yyyy")).addYears(iDelta);
1028         output = date.toString(QStringLiteral("yyyy"));
1029     } else if (iPeriod.length() == 7) {
1030         if (iPeriod[5] == 'S') {
1031             // 2014-S1
1032             QDate date2 = QDate::fromString(iPeriod, QStringLiteral("yyyy-SM"));
1033             date2 = date2.addMonths(date2.month() * 6 - date2.month());  // convert semester in month
1034             date2 = date2.addMonths(6 * iDelta);
1035             output = date2.toString(QStringLiteral("yyyy-S")) % (date2.month() <= 6 ? '1' : '2');
1036         } else if (iPeriod[5] == 'Q') {
1037             // 2014-Q1
1038             QDate date2 = QDate::fromString(iPeriod, QStringLiteral("yyyy-QM"));
1039             date2 = date2.addMonths(date2.month() * 3 - date2.month());  // convert quarter in month
1040             date2 = date2.addMonths(3 * iDelta);
1041             output = date2.toString(QStringLiteral("yyyy-Q")) % (date2.month() <= 3 ? '1' : (date2.month() <= 6 ? '2' : (date2.month() <= 9 ? '3' : '4')));
1042         } else {
1043             // 2014-07
1044             QDate date2 = QDate::fromString(iPeriod, QStringLiteral("yyyy-MM")).addMonths(iDelta);
1045             output = date2.toString(QStringLiteral("yyyy-MM"));
1046         }
1047     }
1048     return output;
1049 }
1050
1051 QStringList SKGServices::tableToDump(const SKGStringListList& iTable, SKGServices::DumpMode iMode)
1052 {
1053     SKGTRACEINFUNC(10)
1054     // initialisation
1055     QStringList oResult;
1056
1057     // Compute max size of each column
1058     int* maxSizes = nullptr;
1059     int nbMaxSizes = 0;
1060     if (iMode == DUMP_TEXT) {
1061         int nb = iTable.count();
1062         for (int i = 0; i < nb; ++i) {
1063             const QStringList& line = iTable.at(i);
1064             int nb2 = line.size();
1065
1066             if (maxSizes == nullptr) {
1067                 nbMaxSizes = nb2;
1068                 maxSizes = new int[nbMaxSizes];
1069                 for (int j = 0; j < nbMaxSizes; ++j) {
1070                     maxSizes[j] = 0;
1071                 }
1072             }
1073
1074             for (int j = 0; j < nb2; ++j) {
1075                 const QString& s = line.at(j);
1076                 if (j < nbMaxSizes && s.length() > maxSizes[j]) {
1077                     maxSizes[j] = s.length();
1078                 }
1079             }
1080         }
1081     }
1082
1083     // dump
1084     int nb = iTable.count();
1085     oResult.reserve(nb);
1086     for (int i = 0; i < nb; ++i) {
1087         QString lineFormated;
1088         if (iMode == DUMP_TEXT && nbMaxSizes > 1) {
1089             lineFormated = QStringLiteral("| ");
1090         }
1091
1092         const QStringList& line = iTable.at(i);
1093         int nb2 = line.size();
1094         for (int j = 0; j < nb2; ++j) {
1095             QString s = line.at(j);
1096             s.remove('\n');
1097
1098             if (iMode == DUMP_CSV) {
1099                 if (j > 0) {
1100                     lineFormated += ';';
1101                 }
1102                 lineFormated += stringToCsv(s);
1103             } else if (maxSizes != nullptr) {
1104                 if (j < nbMaxSizes) {
1105                     s = s.leftJustified(maxSizes[j], ' ');
1106                 }
1107                 lineFormated += s;
1108                 if (nbMaxSizes > 1) {
1109                     lineFormated += " | ";
1110                 }
1111             }
1112         }
1113         oResult.push_back(lineFormated);
1114     }
1115
1116     // delete
1117     if (maxSizes != nullptr) {
1118         delete [] maxSizes;
1119         maxSizes = nullptr;
1120     }
1121
1122     return oResult;
1123 }
1124
1125 QString SKGServices::getRealTable(const QString& iTable)
1126 {
1127     QString output = iTable;
1128     if (output.length() > 2 && output.startsWith(QLatin1String("v_"))) {
1129         output = output.mid(2, output.length() - 2);
1130
1131         int pos = output.indexOf(QStringLiteral("_"));
1132         if (pos != -1) {
1133             output = output.left(pos);
1134         }
1135     }
1136
1137     return output;
1138 }
1139
1140 SKGError SKGServices::downloadToStream(const QUrl& iSourceUrl, QByteArray& oStream)
1141 {
1142     SKGError err;
1143     SKGTRACEINFUNCRC(10, err)
1144     QString tmpFile;
1145     if (iSourceUrl.isLocalFile()) {
1146         tmpFile = iSourceUrl.toLocalFile();
1147     } else {
1148         err = download(iSourceUrl, tmpFile);
1149     }
1150     IFOK(err) {
1151         // Open file
1152         QFile file(tmpFile);
1153         if (Q_UNLIKELY(!file.open(QIODevice::ReadOnly))) {
1154             err.setReturnCode(ERR_FAIL).setMessage(i18nc("An information message", "Open file '%1' failed", tmpFile));
1155         } else {
1156             oStream = file.readAll();
1157
1158             // close file
1159             file.close();
1160         }
1161         if (!iSourceUrl.isLocalFile()) {
1162             QFile(tmpFile).remove();
1163         }
1164     }
1165     return err;
1166 }
1167
1168 SKGError SKGServices::download(const QUrl& iSourceUrl, QString& oTemporaryFile)
1169 {
1170     SKGError err;
1171     SKGTRACEINFUNCRC(10, err)
1172     QTemporaryFile tmpFile;
1173     tmpFile.setAutoRemove(false);
1174     if (tmpFile.open()) {
1175         err = upload(iSourceUrl, QUrl::fromLocalFile(tmpFile.fileName()));
1176         IFOK(err) oTemporaryFile = tmpFile.fileName();
1177     }
1178     return err;
1179 }
1180
1181 SKGError SKGServices::upload(const QUrl& iSourceUrl, const QUrl& iDescUrl)
1182 {
1183     SKGError err;
1184     SKGTRACEINFUNCRC(10, err)
1185     if (iDescUrl != iSourceUrl) {
1186         if (iDescUrl.isLocalFile() && iSourceUrl.isLocalFile()) {
1187             QFile(iDescUrl.toLocalFile()).remove();
1188             if (!QFile::copy(iSourceUrl.toLocalFile(), iDescUrl.toLocalFile())) {
1189                 err = SKGError(ERR_ABORT, i18nc("Error message", "Impossible to copy '%1' to '%2'", iSourceUrl.toDisplayString(), iDescUrl.toDisplayString()));
1190             }
1191         } else {
1192             KIO::FileCopyJob* getJob = KIO::file_copy(iSourceUrl, iDescUrl, -1, KIO::Overwrite | KIO::HideProgressInfo);
1193             if (!getJob->exec()) {
1194                 err.setReturnCode(ERR_ABORT).setMessage(getJob->errorString());
1195                 err.addError(ERR_ABORT, i18nc("Error message", "Impossible to copy '%1' to '%2'", iSourceUrl.toDisplayString(), iDescUrl.toDisplayString()));
1196             }
1197         }
1198     }
1199     return err;
1200 }
1201
1202 SKGError SKGServices::cryptFile(const QString& iFileSource, const QString& iFileTarget, const QString& iPassword, bool iEncrypt, const QString& iHeaderFile, bool& oModeSQLCipher)
1203 {
1204     SKGError err;
1205     SKGTRACEINFUNCRC(10, err)
1206     SKGTRACEL(10) << "Input parameter [iFileSource]=[" << iFileSource << ']' << SKGENDL;
1207     SKGTRACEL(10) << "Input parameter [iFileTarget]=[" << iFileTarget << ']' << SKGENDL;
1208     SKGTRACEL(10) << "Input parameter [iPassword]  =[" << iPassword << ']' << SKGENDL;
1209     SKGTRACEL(10) << "Input parameter [iHeaderFile]=[" << iHeaderFile << ']' << SKGENDL;
1210
1211     oModeSQLCipher = false;
1212
1213     // Read document
1214     QByteArray input;
1215     QByteArray uba;
1216     err = downloadToStream(QUrl::fromUserInput(iFileSource), input);
1217     IFOK(err) {
1218         bool isFileEncrypted = (input.startsWith(QByteArray((iHeaderFile % "_ENCRYPT").toLatin1())));
1219         bool sqliteMode = (input.left(15) == "SQLite format 3");
1220         SKGTRACEL(10) << "isFileEncrypted=[" << static_cast<unsigned int>(isFileEncrypted) << ']' << SKGENDL;
1221
1222         // !!!!! Remove Cipher encryption to remove security hole (thank you to Vincent P) !!!!!
1223         // Only in sqlcipher mode. WARNING: in sqlite mode the issue is still there => add a message to push people to swith in sqlcipher mode
1224         if (iEncrypt && !sqliteMode) {
1225             // The input file is a sqlcipher file and we must save it
1226             // We just have to add a new header to the input file
1227             uba.reserve(input.length() + iHeaderFile.length() + 11);
1228             uba.append(iHeaderFile.toLatin1());
1229             uba.append(!iPassword.isEmpty() ? "_ENCRYPTE3-" : "_DECRYPTE3-");
1230             uba.append(input);
1231             oModeSQLCipher = true;
1232         } else if (!iEncrypt && input.startsWith(QByteArray((iHeaderFile % "_ENCRYPTE3-").toLatin1()))) {
1233             // This check is done to improve performances
1234             if (iPassword.isEmpty() || iPassword == QStringLiteral("DEFAULTPASSWORD")) {
1235                 err = SKGError(ERR_ENCRYPTION, i18nc("Error message", "Wrong password"));
1236             } else {
1237                 // The input file encrypter with the new mode
1238                 // We just have to remove the header
1239                 if (!iHeaderFile.isEmpty() && input.startsWith(iHeaderFile.toLatin1())) {
1240                     input = input.right(input.length() - iHeaderFile.length() - 11);
1241                 }
1242                 uba = input;
1243                 oModeSQLCipher = true;
1244             }
1245         } else {
1246             // WARNING: This part is not really secured but is kept for compatibility
1247             SKGTRACEL(10) << "Mode not secured" << SKGENDL;
1248             QCA::Initializer init;
1249             QCA::SymmetricKey key(QByteArray("skrooge"));
1250
1251             SKGTRACEL(10) << "QCA::Initializer done" << SKGENDL;
1252             if (!iPassword.isEmpty() && !QCA::isSupported("aes128-ecb")) {
1253                 // Set error message
1254                 err.setReturnCode(ERR_INSTALL);  // To avoid password request
1255                 err.setMessage(i18nc("An error message about encryption", "AES128 encryption is not supported (%1). Please install qca-ossl.", QCA::supportedFeatures().join(QStringLiteral(","))));
1256             } else {
1257                 QCA::Cipher* cipher = nullptr;
1258
1259                 QCA::InitializationVector iv(iPassword.toLatin1());
1260
1261                 // Create a 128 bit AES cipher object using Cipher Block Chaining (CBC) mode
1262                 if ((isFileEncrypted || iEncrypt) && !iPassword.isEmpty()) {
1263                     cipher = new QCA::Cipher(QStringLiteral("aes128"), QCA::Cipher::CBC,
1264                                              // use Default padding, which is equivalent to PKCS7 for CBC
1265                                              QCA::Cipher::DefaultPadding,
1266                                              iEncrypt ? QCA::Encode : QCA::Decode,
1267                                              key, iv);
1268                 }
1269
1270                 // BUG 249955 vvv
1271                 if ((cipher == nullptr) && isFileEncrypted) {
1272                     err = SKGError(ERR_ENCRYPTION, i18nc("Error message about encrypting a file", "Encryption failed"));
1273                 }
1274                 // BUG 249955 ^^^
1275
1276                 // Suppress header
1277                 SKGTRACEL(10) << "input=[" << input.left(50) << "…]" << SKGENDL;
1278                 if (!iHeaderFile.isEmpty() && input.startsWith(iHeaderFile.toLatin1())) {
1279                     input = input.right(input.length() - iHeaderFile.length() - 11);
1280                 }
1281                 SKGTRACEL(10) << "input without header=[" << input.left(50) << "…]" << SKGENDL;
1282
1283                 QCA::SecureArray u;
1284                 if (cipher != nullptr) {
1285                     if (!err) {
1286                         // Process encryption or decryption
1287                         u = cipher->process(input);
1288
1289                         // We need to check if that update() call worked.
1290                         if (!cipher->ok()) {
1291                             err = SKGError(ERR_UNEXPECTED, i18nc("Error message about encrypting a file", "Encryption failed"));
1292                         } else {
1293                             uba = u.toByteArray();
1294                         }
1295                     }
1296                 } else {
1297                     uba = input;
1298                 }
1299
1300                 IFOK(err) {
1301                     // Check if decryption is OK
1302                     SKGTRACEL(10) << "output 1=[" << uba.left(50) << "…]" << SKGENDL;
1303                     if (!iEncrypt) {
1304                         if (!uba.startsWith(QByteArray("SQLite format 3"))) {
1305                             if (!uba.startsWith(SQLCIPHERHEARDER)) {
1306                                 if (isFileEncrypted) {
1307                                     err = SKGError(ERR_ENCRYPTION, i18nc("Error message", "Wrong password"));
1308                                 } else {
1309                                     oModeSQLCipher = true;
1310                                 }
1311                             } else {
1312                                 uba = uba.right(uba.length() - QStringLiteral(SQLCIPHERHEARDER).length());
1313                                 oModeSQLCipher = true;
1314                             }
1315                         }
1316                     }
1317                 }
1318
1319                 IFOK(err) {
1320                     // Add headers
1321                     if (iEncrypt && !iHeaderFile.isEmpty()) {
1322                         QByteArray h = (iHeaderFile % (cipher != nullptr ? "_ENCRYPTED-" : "_DECRYPTED-")).toLatin1();
1323                         uba = uba.insert(0, h);
1324                     }
1325                 }
1326
1327                 delete cipher;
1328                 cipher = nullptr;
1329             }
1330         }
1331         SKGTRACEL(10) << "output 2=[" << uba.left(50) << "…]" << SKGENDL;
1332
1333         // output the results of that stage
1334         IFOK(err) {
1335             SKGTRACEIN(10, "SKGServices::cryptFile-save file")
1336             QSaveFile fileOutput(iFileTarget);
1337             if (!fileOutput.open(QIODevice::WriteOnly)) {
1338                 err = SKGError(ERR_WRITEACCESS, i18nc("Error message: writing a file failed", "Write file '%1' failed", iFileTarget));
1339             } else {
1340                 // Write document
1341                 fileOutput.write(uba);
1342
1343                 // Close the file
1344                 if (!fileOutput.commit()) {
1345                     IFOK(err) {
1346                         err = SKGError(ERR_WRITEACCESS, i18nc("Error message: writing a file failed", "Write file '%1' failed", iFileTarget));
1347                     }
1348                 }
1349             }
1350         }
1351     }
1352     SKGTRACEL(10) << "Output parameter [oModeSQLCipher]=[" << static_cast<unsigned int>(oModeSQLCipher) << ']' << SKGENDL;
1353     return err;
1354 }
1355
1356 SKGError SKGServices::copySqliteDatabaseToXml(const QSqlDatabase& iDb, QDomDocument& oDocument, QVector<QString>* iIgnore)
1357 {
1358     SKGError err;
1359     SKGTRACEINFUNCRC(10, err)
1360     oDocument = QDomDocument(QStringLiteral("SKGML"));
1361     QDomElement document = oDocument.createElement(QStringLiteral("skrooge"));
1362     oDocument.appendChild(document);
1363
1364     // Copy the tables
1365     QStringList listTables = iDb.tables();
1366     int nb = listTables.count();
1367     for (int i = 0; !err && i < nb; ++i) {
1368         const QString& tableName = listTables.at(i);
1369         if (!tableName.startsWith(QLatin1String("sqlite_")) && !tableName.startsWith(QLatin1String("vm_"))) {
1370             if (iIgnore == nullptr || !iIgnore->contains(tableName)) {
1371                 QDomElement table = oDocument.createElement(QStringLiteral("list_") + tableName);
1372                 document.appendChild(table);
1373
1374                 SKGStringListList listRows;
1375                 err = SKGServices::executeSelectSqliteOrder(iDb, "SELECT * FROM " % tableName, listRows);
1376                 int nbRows = listRows.count();
1377                 if (nbRows != 0) {
1378                     const QStringList& titles = listRows.at(0);
1379                     for (int j = 1; !err && j < nbRows; ++j) {  // Forget title
1380                         const QStringList& values = listRows.at(j);
1381
1382                         QDomElement row = oDocument.createElement(tableName);
1383                         table.appendChild(row);
1384
1385                         int nbVals = values.count();
1386                         for (int k = 0; k < nbVals; ++k) {
1387                             if (iIgnore == nullptr || !iIgnore->contains(tableName + "." + titles.at(k))) {
1388                                 row.setAttribute(titles.at(k), values.at(k));
1389                             }
1390                         }
1391                     }
1392                 }
1393             }
1394         }
1395     }
1396     return err;
1397 }
1398
1399 SKGError SKGServices::copySqliteDatabase(const QSqlDatabase& iFileDb, const QSqlDatabase& iMemoryDb, bool iFromFileToMemory, const QString& iPassword)
1400 {
1401     SKGError err;
1402     SKGTRACEINFUNCRC(10, err)
1403     SKGTRACEL(20) << "Input parameter [iFileDb]=[" << iFileDb.databaseName() << ']' << SKGENDL;
1404     SKGTRACEL(20) << "Input parameter [iMemoryDb]=[" << iMemoryDb.databaseName() << ']' << SKGENDL;
1405     SKGTRACEL(10) << "Input parameter [iFromFileToMemory]=[" << (iFromFileToMemory ? "FILE->MEMORY" : "MEMORY->FILE") << ']' << SKGENDL;
1406
1407     QString dbFileName = iFileDb.databaseName();
1408     // Copy the tables
1409     SKGStringListList listTables;
1410     int nb = 0;
1411     IFOK(err) {
1412         err = SKGServices::executeSelectSqliteOrder((iFromFileToMemory ? iFileDb : iMemoryDb),
1413                 QStringLiteral("SELECT sql, tbl_name FROM sqlite_master WHERE type='table' AND sql NOT NULL and name NOT LIKE 'sqlite_%'"),
1414                 listTables);
1415
1416         nb = listTables.count();
1417         for (int i = 1; !err && i < nb; ++i) {  // Forget header
1418             QString val = listTables.at(i).at(0);
1419             err = SKGServices::executeSqliteOrder((iFromFileToMemory ? iMemoryDb : iFileDb), val);
1420         }
1421     }
1422     // Attach db
1423     IFOK(err) {
1424         QString add;
1425         if (!iPassword.isEmpty()) {
1426             add = " KEY '" % SKGServices::stringToSqlString(iPassword) % "'";
1427         }
1428         err = SKGServices::executeSqliteOrder(iMemoryDb, "ATTACH DATABASE '" % dbFileName % "' as source" % add);
1429     }
1430
1431     // Copy records
1432     IFOK(err) {
1433         err = SKGServices::executeSqliteOrder(iMemoryDb, QStringLiteral("BEGIN"));
1434         IFOK(err) {
1435             for (int i = 1; !err && i < nb; ++i) {  // Forget header
1436                 QString val = listTables.at(i).at(1);
1437                 if (iFromFileToMemory)  {
1438                     err = SKGServices::executeSqliteOrder(iMemoryDb, "insert into main." % val % " select * from source." % val);
1439                 } else {
1440                     err = SKGServices::executeSqliteOrder(iMemoryDb, "insert into source." % val % " select * from main." % val);
1441                 }
1442             }
1443         }
1444         SKGServices::executeSqliteOrder(iMemoryDb, QStringLiteral("COMMIT"));
1445     }
1446
1447     // Detach
1448     {
1449         SKGError err2 = SKGServices::executeSqliteOrder(iMemoryDb, QStringLiteral("DETACH DATABASE source"));
1450         if (!err && err2) {
1451             err = err2;
1452         }
1453     }
1454
1455     // Optimization
1456     IFOK(err) {
1457         QStringList optimization;
1458         optimization << QStringLiteral("PRAGMA case_sensitive_like=true")
1459                      << QStringLiteral("PRAGMA journal_mode=MEMORY")
1460                      << QStringLiteral("PRAGMA temp_store=MEMORY")
1461                      // << QStringLiteral("PRAGMA locking_mode=EXCLUSIVE")
1462                      << QStringLiteral("PRAGMA synchronous = OFF")
1463                      << QStringLiteral("PRAGMA recursive_triggers=true");
1464         err = SKGServices::executeSqliteOrders(iFromFileToMemory ? iMemoryDb : iFileDb, optimization);
1465     }
1466
1467     // Copy the indexes
1468     IFOK(err) {
1469         SKGStringListList listSqlOrder;
1470         err = SKGServices::executeSelectSqliteOrder((iFromFileToMemory ? iFileDb : iMemoryDb),
1471                 QStringLiteral("SELECT sql FROM sqlite_master WHERE type='index' AND sql NOT NULL and name NOT LIKE 'sqlite_%'"),
1472                 listSqlOrder);
1473
1474         int nb2 = listSqlOrder.count();
1475         for (int i = 1; !err && i < nb2; ++i) {  // Forget header
1476             QString val = listSqlOrder.at(i).at(0);
1477             err = SKGServices::executeSqliteOrder((iFromFileToMemory ? iMemoryDb : iFileDb), val);
1478         }
1479     }
1480
1481     // Copy the views
1482     IFOK(err) {
1483         SKGStringListList listSqlOrder;
1484         err = SKGServices::executeSelectSqliteOrder((iFromFileToMemory ? iFileDb : iMemoryDb),
1485                 QStringLiteral("SELECT sql FROM sqlite_master WHERE type='view' AND sql NOT NULL and name NOT LIKE 'sqlite_%'"),
1486                 listSqlOrder);
1487
1488         int nb2 = listSqlOrder.count();
1489         for (int i = 1; !err && i < nb2; ++i) {  // Forget header
1490             QString val = listSqlOrder.at(i).at(0);
1491             err = SKGServices::executeSqliteOrder((iFromFileToMemory ? iMemoryDb : iFileDb), val);
1492         }
1493     }
1494
1495     // Copy the triggers, must be done after the views
1496     IFOK(err) {
1497         SKGStringListList listSqlOrder;
1498         err = SKGServices::executeSelectSqliteOrder((iFromFileToMemory ? iFileDb : iMemoryDb),
1499                 QStringLiteral("SELECT sql FROM sqlite_master WHERE type='trigger' AND sql NOT NULL and name NOT LIKE 'sqlite_%'"),
1500                 listSqlOrder);
1501
1502         int nb2 = listSqlOrder.count();
1503         for (int i = 1; !err && i < nb2; ++i) {  // Forget header
1504             QString val = listSqlOrder.at(i).at(0);
1505             err = SKGServices::executeSqliteOrder((iFromFileToMemory ? iMemoryDb : iFileDb), val);
1506         }
1507     }
1508
1509     // Check if created file exists
1510     if (!err && !iFromFileToMemory && !QFile(dbFileName).exists()) {
1511         err.setReturnCode(ERR_FAIL).setMessage(i18nc("An error message: creating a file failed", "Creation file '%1' failed", dbFileName));
1512     }
1513     IFKO(err) {
1514         err.addError(SQLLITEERROR + ERR_FAIL, i18nc("Error message: something failed", "%1 failed", QStringLiteral("SKGServices::copySqliteDatabase()")));
1515     }
1516     return err;
1517 }
1518
1519 SKGError SKGServices::executeSqliteOrders(const QSqlDatabase& iDb, const QStringList& iSqlOrders)
1520 {
1521     SKGError err;
1522     _SKGTRACEINFUNCRC(10, err)
1523     int nb = iSqlOrders.count();
1524     for (int i = 0; !err && i < nb; ++i) {
1525         err = executeSqliteOrder(iDb, iSqlOrders.at(i));
1526     }
1527     return err;
1528 }
1529
1530 SKGError SKGServices::executeSqliteOrder(const QSqlDatabase& iDb, const QString& iSqlOrder, const QMap<QString, QVariant>& iBind, int* iLastId)
1531 {
1532     SKGError err;
1533     _SKGTRACEINFUNCRC(10, err)
1534     SKGTRACEL(20) << "Input parameter [iSqlOrder]=[" << iSqlOrder << ']' << SKGENDL;
1535
1536     QSqlQuery query(QString(), iDb);
1537     query.setForwardOnly(true);
1538
1539     double elapse = 0;
1540     if (SKGServices::SKGSqlTraces != -1) {
1541         elapse = SKGServices::getMicroTime();
1542     }
1543
1544     // Prepare sql order
1545     bool prep = query.prepare(iSqlOrder);
1546
1547     // Bind values
1548     QMapIterator<QString, QVariant> i(iBind);
1549     while (i.hasNext()) {
1550         i.next();
1551         query.bindValue(i.key(), i.value());
1552     }
1553
1554     if (!prep || !query.exec()) {
1555         QSqlError sqlError = query.lastError();
1556         if (sqlError.nativeErrorCode().toInt() != 19 /*SQLITE_CONSTRAINT*/ && iSqlOrder != QStringLiteral("SELECT count(*) FROM sqlite_master") /*Test password*/) {
1557             SKGTRACE << "WARNING: " << iSqlOrder << SKGENDL;
1558             SKGTRACE << "         returns :" << sqlError.text() << SKGENDL;
1559         }
1560
1561         err = SKGError(SQLLITEERROR + sqlError.nativeErrorCode().toInt(), iSqlOrder);
1562         err.addError(SQLLITEERROR + sqlError.nativeErrorCode().toInt(), sqlError.text());
1563
1564         if (sqlError.nativeErrorCode().toInt() == 19 && iSqlOrder.startsWith(QLatin1String("INSERT "))) {
1565             err.addError(ERR_FAIL, i18nc("Error message", "Creation failed. The object already exists."));
1566         }
1567     } else {
1568         if (iLastId != nullptr) {
1569             *iLastId = query.lastInsertId().toInt();
1570         }
1571     }
1572     if (SKGServices::SKGSqlTraces != -1) {
1573         elapse = SKGServices::getMicroTime() - elapse;
1574         if (elapse >= SKGServices::SKGSqlTraces) {
1575             SKGTRACE << "executeSqliteOrder :" << iSqlOrder << " TIME=" << elapse << " ms" << SKGENDL;
1576         }
1577     }
1578     return err;
1579 }
1580
1581 SKGError SKGServices::executeSqliteOrder(const QSqlDatabase& iDb, const QString& iSqlOrder, int* iLastId)
1582 {
1583     return executeSqliteOrder(iDb, iSqlOrder, QMap< QString, QVariant >(), iLastId);
1584 }
1585
1586 SKGError SKGServices::dumpSelectSqliteOrder(const QSqlDatabase& iDb, const QString& iSqlOrder, SKGServices::DumpMode iMode)
1587 {
1588     return dumpSelectSqliteOrder(iDb, iSqlOrder, nullptr, iMode);
1589 }
1590
1591 SKGError SKGServices::dumpSelectSqliteOrder(const QSqlDatabase& iDb, const QString& iSqlOrder, QTextStream* oStream, SKGServices::DumpMode iMode)
1592 {
1593     SKGError err;
1594     _SKGTRACEINFUNCRC(10, err)
1595     SKGTRACEL(20) << "Input parameter [iSqlOrder]=[" << iSqlOrder << ']' << SKGENDL;
1596
1597     // initialisation
1598     QStringList oResult;
1599     err = SKGServices::dumpSelectSqliteOrder(iDb, iSqlOrder, oResult, iMode);
1600     IFOK(err) {
1601         // dump
1602         int nb = oResult.size();
1603         for (int i = 0; i < nb; ++i) {
1604             if (oStream == nullptr) {
1605                 SKGTRACESUITE << oResult.at(i) << SKGENDL;
1606             } else {
1607                 *oStream << oResult.at(i) << SKGENDL;
1608             }
1609         }
1610     }
1611     return err;
1612 }
1613
1614 SKGError SKGServices::dumpSelectSqliteOrder(const QSqlDatabase& iDb, const QString& iSqlOrder, QString& oResult, SKGServices::DumpMode iMode)
1615 {
1616     SKGError err;
1617     _SKGTRACEINFUNCRC(10, err)
1618     // initialisation
1619     oResult = QLatin1String("");
1620
1621     QStringList oResultTmp;
1622     err = SKGServices::dumpSelectSqliteOrder(iDb, iSqlOrder, oResultTmp, iMode);
1623     IFOK(err) {
1624         // dump
1625         int nb = oResultTmp.size();
1626         for (int i = 0; i < nb; ++i) {
1627             oResult += oResultTmp.at(i) % '\n';
1628         }
1629     }
1630     return err;
1631 }
1632
1633 SKGError SKGServices::dumpSelectSqliteOrder(const QSqlDatabase& iDb, const QString& iSqlOrder, QStringList& oResult, SKGServices::DumpMode iMode)
1634 {
1635     SKGError err;
1636     _SKGTRACEINFUNCRC(10, err)
1637
1638     // Execution of sql order
1639     SKGStringListList oResultTmp;
1640     err = executeSelectSqliteOrder(iDb, iSqlOrder, oResultTmp);
1641     IFOK(err) oResult = tableToDump(oResultTmp, iMode);
1642     return err;
1643 }
1644
1645 SKGError SKGServices::executeSingleSelectSqliteOrder(const QSqlDatabase& iDb, const QString& iSqlOrder, QString& oResult)
1646 {
1647     SKGStringListList result;
1648     SKGError err = executeSelectSqliteOrder(iDb, iSqlOrder, result);
1649     oResult = result.value(1).value(0);
1650     return err;
1651 }
1652
1653 SKGError SKGServices::executeSelectSqliteOrder(const QSqlDatabase& iDb, const QString& iSqlOrder, SKGStringListList& oResult)
1654 {
1655     SKGError err;
1656     _SKGTRACEINFUNCRC(10, err)
1657     // initialisation
1658     oResult.clear();
1659
1660     QSqlQuery query(QString(), iDb);
1661     query.setForwardOnly(true);
1662     double elapse = 0;
1663     if (SKGServices::SKGSqlTraces != -1) {
1664         elapse = SKGServices::getMicroTime();
1665     }
1666
1667     if (!query.exec(iSqlOrder)) {
1668         QSqlError sqlError = query.lastError();
1669         if (qApp->thread() == QThread::currentThread()) {
1670             SKGTRACE << "WARNING: " << iSqlOrder << SKGENDL;
1671             SKGTRACE << "         returns :" << sqlError.text() << SKGENDL;
1672         }
1673         err = SKGError(SQLLITEERROR + sqlError.nativeErrorCode().toInt(), iSqlOrder);
1674         err.addError(SQLLITEERROR + sqlError.nativeErrorCode().toInt(), sqlError.text());
1675     } else {
1676         double elapse1 = 0;
1677         if (SKGServices::SKGSqlTraces != -1) {
1678             elapse1 = SKGServices::getMicroTime() - elapse;
1679         }
1680
1681         // Addition of column names
1682         QSqlRecord rec = query.record();
1683         QStringList line;
1684         int index = 0;
1685         while (index != -1) {
1686             QString val = rec.fieldName(index);
1687             if (!val.isEmpty()) {
1688                 line.push_back(val);
1689                 ++index;
1690             } else {
1691                 index = -1;
1692             }
1693         }
1694         oResult.push_back(line);
1695
1696         // Addition of rows
1697         while (query.next()) {
1698             QStringList line2;
1699             int index2 = 0;
1700             while (index2 != -1) {
1701                 QVariant val = query.value(index2);
1702                 if (val.isValid()) {
1703                     line2.push_back(val.toString());
1704                     ++index2;
1705                 } else {
1706                     index2 = -1;
1707                 }
1708             }
1709             oResult.push_back(line2);
1710         }
1711         if (SKGServices::SKGSqlTraces != -1) {
1712             double elapse2 = SKGServices::getMicroTime() - elapse;
1713             if (elapse1 >= SKGServices::SKGSqlTraces) {
1714                 SKGTRACE << "executeSqliteOrder:" << iSqlOrder << " TIME=" << elapse1 << " ms,  (with fetch):" << elapse2 << " ms" << SKGENDL;
1715             }
1716         }
1717     }
1718     return err;
1719 }
1720
1721 SKGError SKGServices::readPropertyFile(const QString& iFileName, QHash< QString, QString >& oProperties)
1722 {
1723     SKGError err;
1724     oProperties.clear();
1725
1726     // Open file
1727     QFile file(iFileName);
1728     if (!file.open(QIODevice::ReadOnly | QIODevice::Text)) {
1729         err = SKGError(ERR_FAIL, i18nc("An erro message", "Open file '%1' failed", iFileName));
1730     } else {
1731         // Read file
1732         QTextStream stream(&file);
1733         while (!stream.atEnd() && !err) {
1734             // Read line
1735             QString line = stream.readLine().trimmed();
1736             if (!line.isEmpty() && !line.startsWith(QLatin1String("#"))) {
1737                 int pos = line.indexOf(QStringLiteral("="));
1738                 if (pos != -1) {
1739                     oProperties[line.left(pos).trimmed().toLower()] = line.right(line.count() - pos - 1);
1740                 }
1741             }
1742         }
1743
1744         // close file
1745         file.close();
1746     }
1747     return err;
1748 }
1749
1750 double SKGServices::getMicroTime()
1751 {
1752 #ifdef Q_OS_WIN
1753     return static_cast<double>(GetTickCount());
1754 #else
1755     struct timeval tv {};
1756     struct timezone tz {};
1757
1758     // get time
1759     gettimeofday(&tv, &tz);
1760
1761     // return time
1762     return (static_cast<double>(1000.0 * tv.tv_sec)) + (static_cast<double>(tv.tv_usec / 1000));
1763 #endif
1764 }
1765
1766 SKGStringListList SKGServices::getBase100Table(const SKGStringListList& iTable)
1767 {
1768     SKGTRACEINFUNC(10)
1769
1770     // Build history
1771     SKGStringListList output;
1772     int nblines = iTable.count();
1773     int nbCols = 0;
1774     if (nblines != 0) {
1775         nbCols = iTable.at(0).count();
1776     }
1777
1778     output.reserve(nblines + 1);
1779     output.push_back(iTable.at(0));
1780
1781     // Create table
1782     for (int i = 1; i < nblines; ++i) {
1783         QStringList newLine;
1784         newLine.reserve(nbCols + 1);
1785         newLine.push_back(iTable.at(i).at(0));
1786
1787         double valInitial = 0;
1788
1789         for (int j = 1; j < nbCols; ++j) {
1790             double val = SKGServices::stringToDouble(iTable.at(i).at(j));
1791             if (j == 1) {
1792                 valInitial = val;
1793                 val = 100.0;
1794             } else {
1795                 if (valInitial != 0.0) {
1796                     val = 100.0 * val / valInitial;
1797                 }
1798             }
1799             newLine.push_back(SKGServices::doubleToString(val));
1800         }
1801         output.push_back(newLine);
1802     }
1803
1804     return output;
1805 }
1806
1807 SKGStringListList SKGServices::getPercentTable(const SKGStringListList& iTable, bool iOfColumns, bool iAbsolute)
1808 {
1809     SKGTRACEINFUNC(10)
1810
1811     // Build history
1812     SKGStringListList output;
1813     int nblines = iTable.count();
1814     int nbCols = 0;
1815     if (nblines != 0) {
1816         nbCols = iTable.at(0).count();
1817     }
1818
1819     output.reserve(nblines + 1);
1820     output.push_back(iTable.at(0));
1821
1822     // Compute sums
1823     QList<double> sums;
1824     if (iOfColumns) {
1825         // Compute sum of columns
1826         sums.reserve(nbCols);
1827         for (int j = 1; j < nbCols; ++j) {
1828             // Compute sum
1829             double sum = 0;
1830             for (int i = 1; i < nblines; ++i) {
1831                 double v = SKGServices::stringToDouble(iTable.at(i).at(j));
1832                 sum += (iAbsolute ? qAbs(v) : v);
1833             }
1834
1835             sums.push_back(sum);
1836         }
1837     } else {
1838         // Compute sum of lines
1839         sums.reserve(nblines);
1840         for (int j = 1; j < nblines; ++j) {
1841             // Compute sum
1842             double sum = 0;
1843             for (int i = 1; i < nbCols; ++i) {
1844                 double v = SKGServices::stringToDouble(iTable.at(j).at(i));
1845                 sum += (iAbsolute ? qAbs(v) : v);
1846             }
1847
1848             sums.push_back(sum);
1849         }
1850     }
1851
1852     // Create table
1853     for (int i = 1; i < nblines; ++i) {
1854         QStringList newLine;
1855         newLine.reserve(nbCols + 1);
1856         newLine.push_back(iTable.at(i).at(0));
1857
1858         for (int j = 1; j < nbCols; ++j) {
1859             double val = SKGServices::stringToDouble(iTable.at(i).at(j));
1860             val = (iAbsolute ? qAbs(val) : val);
1861             double sum = (iOfColumns ? sums.at(j - 1) : sums.at(i - 1));
1862             newLine.push_back(SKGServices::doubleToString(sum == 0.0 ? 0.0 : 100.0 * val / sum));
1863         }
1864         output.push_back(newLine);
1865     }
1866
1867     return output;
1868 }
1869
1870 SKGStringListList SKGServices::getHistorizedTable(const SKGStringListList& iTable)
1871 {
1872     SKGTRACEINFUNC(10)
1873
1874     // Build history
1875     SKGStringListList output;
1876     int nblines = iTable.count();
1877     int nbCols = 0;
1878     if (nblines != 0) {
1879         nbCols = iTable.at(0).count();
1880     }
1881
1882     output.reserve(nblines + 1);
1883     output.push_back(iTable.at(0));
1884     for (int i = 1; i < nblines; ++i) {
1885         QStringList newLine;
1886         newLine.reserve(nbCols + 1);
1887         newLine.push_back(iTable.at(i).at(0));
1888
1889         double sum = 0;
1890         for (int j = 1; j < nbCols; ++j) {
1891             sum += SKGServices::stringToDouble(iTable.at(i).at(j));
1892             newLine.push_back(SKGServices::doubleToString(sum));
1893         }
1894         output.push_back(newLine);
1895     }
1896
1897     return output;
1898 }
1899
1900 QString SKGServices::encodeForUrl(const QString& iString)
1901 {
1902     return QUrl::toPercentEncoding(iString);
1903 }
1904
1905 QIcon SKGServices::fromTheme(const QString& iName, const QStringList& iOverlays)
1906 {
1907     QIcon output;
1908     if (!iOverlays.isEmpty()) {
1909         output = KDE::icon(iName, iOverlays);
1910     } else {
1911         output = KDE::icon(iName);
1912     }
1913     if (output.isNull() && !iName.isEmpty()) {
1914         static QHash<QString, QString> alternatives;
1915         if (alternatives.count() == 0) {
1916             // Build alternatives
1917             alternatives[QStringLiteral("arrow-down")] = QStringLiteral("go-down");
1918             alternatives[QStringLiteral("arrow-right")] = QStringLiteral("go-next");
1919             alternatives[QStringLiteral("arrow-up")] = QStringLiteral("go-up");
1920             alternatives[QStringLiteral("arrow-down-double")] = QStringLiteral("go-down");
1921             alternatives[QStringLiteral("arrow-up-double")] = QStringLiteral("go-up");
1922             alternatives[QStringLiteral("bookmark")] = QStringLiteral("bookmark-new");
1923             alternatives[QStringLiteral("bookmarks")] = QStringLiteral("bookmark-new");
1924             alternatives[QStringLiteral("checkbox")] = QStringLiteral("emblem-symbolic-link");
1925             alternatives[QStringLiteral("chronometer")] = QStringLiteral("appointment");
1926             alternatives[QStringLiteral("configure")] = QStringLiteral("preferences-desktop");
1927             alternatives[QStringLiteral("dashboard-show")] = QStringLiteral("user-desktop");
1928             alternatives[QStringLiteral("dialog-cancel")] = QStringLiteral("process-stop");
1929             alternatives[QStringLiteral("dialog-close")] = QStringLiteral("process-stop");
1930             alternatives[QStringLiteral("dialog-ok")] = QLatin1String("");
1931             alternatives[QStringLiteral("download-later")] = QStringLiteral("internet-services");
1932             alternatives[QStringLiteral("download")] = QStringLiteral("internet-services");
1933             alternatives[QStringLiteral("draw-freehand")] = QStringLiteral("accessories-text-editor");
1934             alternatives[QStringLiteral("edit-guides")] = QStringLiteral("text-x-generic");
1935             alternatives[QStringLiteral("edit-rename")] = QStringLiteral("accessories-text-editor");
1936             alternatives[QStringLiteral("emblem-locked")] = QStringLiteral("lock");
1937             alternatives[QStringLiteral("exchange-positions")] = QLatin1String("");
1938             alternatives[QStringLiteral("format-fill-color")] = QLatin1String("");
1939             alternatives[QStringLiteral("games-solve")] = QStringLiteral("application-certificate");
1940             alternatives[QStringLiteral("get-hot-new-stuff")] = QStringLiteral("applications-other");
1941             alternatives[QStringLiteral("irc-operator")] = QLatin1String("");
1942             alternatives[QStringLiteral("ktip")] = QStringLiteral("dialog-information");
1943             alternatives[QStringLiteral("labplot-xy-plot-two-axes-centered-origin")] = QStringLiteral("x-office-spreadsheet");
1944             alternatives[QStringLiteral("layer-visible-off")] = QLatin1String("");
1945             alternatives[QStringLiteral("layer-visible-on")] = QLatin1String("");
1946             alternatives[QStringLiteral("merge")] = QLatin1String("");
1947             alternatives[QStringLiteral("office-chart-area")] = QStringLiteral("x-office-spreadsheet");
1948             alternatives[QStringLiteral("office-chart-area-stacked")] = QStringLiteral("x-office-spreadsheet");
1949             alternatives[QStringLiteral("office-chart-bar-percentage")] = QStringLiteral("x-office-spreadsheet");
1950             alternatives[QStringLiteral("office-chart-bar")] = QStringLiteral("x-office-spreadsheet");
1951             alternatives[QStringLiteral("office-chart-bar-stacked")] = QStringLiteral("x-office-spreadsheet");
1952             alternatives[QStringLiteral("office-chart-line")] = QStringLiteral("x-office-spreadsheet");
1953             alternatives[QStringLiteral("office-chart-line-stacked")] = QStringLiteral("x-office-spreadsheet");
1954             alternatives[QStringLiteral("office-chart-pie")] = QStringLiteral("x-office-spreadsheet");
1955             alternatives[QStringLiteral("office-chart-ring")] = QStringLiteral("x-office-spreadsheet");
1956             alternatives[QStringLiteral("map-flat")] = QStringLiteral("x-office-spreadsheet");
1957             alternatives[QStringLiteral("office-chart-scatter")] = QStringLiteral("x-office-spreadsheet");
1958             alternatives[QStringLiteral("preview")] = QStringLiteral("document-print-preview");
1959             alternatives[QStringLiteral("quickopen")] = QStringLiteral("emblem-symbolic-link");
1960             alternatives[QStringLiteral("run-build-configure")] = QStringLiteral("media-playback-start");
1961             alternatives[QStringLiteral("run-build")] = QStringLiteral("media-playback-start");
1962             alternatives[QStringLiteral("show-menu")] = QStringLiteral("applications-system");
1963             alternatives[QStringLiteral("skrooge_category")] = QStringLiteral("folder-open");
1964             alternatives[QStringLiteral("split")] = QStringLiteral("edit-cut");
1965             alternatives[QStringLiteral("taxes-finances")] = QStringLiteral("fonts");
1966             alternatives[QStringLiteral("tools-wizard")] = QStringLiteral("applications-other");
1967             alternatives[QStringLiteral("user-group-properties")] = QStringLiteral("system-users");
1968             alternatives[QStringLiteral("user-properties")] = QStringLiteral("document-properties");
1969             alternatives[QStringLiteral("utilities-file-archiver")] = QStringLiteral("package-x-generic");
1970             alternatives[QStringLiteral("vcs-conflicting")] = QStringLiteral("dialog-warning");
1971             alternatives[QStringLiteral("vcs-normal")] = QStringLiteral("dialog-information");
1972             alternatives[QStringLiteral("view-bank-account-checking")] = QStringLiteral("go-home");
1973             alternatives[QStringLiteral("view-bank-account")] = QStringLiteral("x-office-address-book");
1974             alternatives[QStringLiteral("view-bank-account-savings")] = QStringLiteral("go-home");
1975             alternatives[QStringLiteral("view-bank")] = QStringLiteral("go-home");
1976             alternatives[QStringLiteral("view-calendar-journal")] = QStringLiteral("x-office-calendar");
1977             alternatives[QStringLiteral("view-calendar-month")] = QStringLiteral("x-office-calendar");
1978             alternatives[QStringLiteral("view-calendar")] = QStringLiteral("x-office-calendar");
1979             alternatives[QStringLiteral("view-calendar-week")] = QStringLiteral("x-office-calendar");
1980             alternatives[QStringLiteral("view-calendar-whatsnext")] = QStringLiteral("x-office-calendar");
1981             alternatives[QStringLiteral("view-categories")] = QStringLiteral("folder-open");
1982             alternatives[QStringLiteral("view-categories-expenditures")] = QStringLiteral("face-sad");
1983             alternatives[QStringLiteral("view-categories-incomes")] = QStringLiteral("face-smile");
1984             alternatives[QStringLiteral("view-file-columns")] = QStringLiteral("go-home");
1985             alternatives[QStringLiteral("view-financial-list")] = QStringLiteral("go-home");
1986             alternatives[QStringLiteral("view-investment")] = QStringLiteral("go-home");
1987             alternatives[QStringLiteral("view-list-details")] = QStringLiteral("go-home");
1988             alternatives[QStringLiteral("view-list-text")] = QStringLiteral("go-home");
1989             alternatives[QStringLiteral("view-pim-calendar")] = QStringLiteral("x-office-spreadsheet");
1990             alternatives[QStringLiteral("view-statistics")] = QStringLiteral("x-office-spreadsheet");
1991             alternatives[QStringLiteral("window-duplicate")] = QStringLiteral("edit-copy");
1992             alternatives[QStringLiteral("zoom-fit-width")] = QStringLiteral("media-playback-stop");
1993             alternatives[QStringLiteral("smallclock")] = QLatin1String("");
1994             alternatives[QStringLiteral("edit_undo")] = QStringLiteral("edit-undo");
1995             alternatives[QStringLiteral("nextuntranslated")] = QStringLiteral("debug-execute-to-cursor");
1996             alternatives[QStringLiteral("format-precision-less")] = QStringLiteral("visibility");
1997             alternatives[QStringLiteral("format-indent-more")] = QStringLiteral("go-next");
1998             alternatives[QStringLiteral("format-indent-less")] = QStringLiteral("go-previous");
1999             alternatives[QStringLiteral("crosshairs")] = QStringLiteral("emblem-symbolic-link");
2000         }
2001         bool alternativeEmpty = false;
2002         if (alternatives.contains(iName)) {
2003             auto alternative = alternatives.value(iName);
2004             alternativeEmpty = (alternative.isEmpty());
2005             if (!alternativeEmpty) {
2006                 if (!iOverlays.isEmpty()) {
2007                     output = KDE::icon(alternative, iOverlays);
2008                 } else {
2009                     output = KDE::icon(alternative);
2010                 }
2011             }
2012         }
2013         if (output.isNull() && !alternativeEmpty) {
2014             SKGTRACE << "WARNING: Icon [" << iName << "] not found" << SKGENDL;
2015             output = KDE::icon(QStringLiteral("script-error"));
2016             if (output.isNull()) {
2017                 output = KDE::icon(QStringLiteral("image-missing"));
2018             }
2019         }
2020     }
2021     return output;
2022 }
2023
2024 QString SKGServices::getMajorVersion(const QString& iVersion)
2025 {
2026     QString output = iVersion;
2027     int pos = output.indexOf('.');
2028     if (pos != -1) {
2029         pos = output.indexOf('.', pos + 1);
2030         if (pos != -1) {
2031             output = output.left(pos);
2032         }
2033     }
2034     return output;
2035 }
2036
2037 QString SKGServices::getFullPathCommandLine(const QString& iCommandLine)
2038 {
2039     QString output = iCommandLine;
2040     if (!output.isEmpty()) {
2041         auto pathWords = SKGServices::splitCSVLine(output, QLatin1Char(' '));
2042         QString fullpath = QStandardPaths::locate(QStandardPaths::GenericDataLocation, qApp->applicationName() % "/" % pathWords.at(0));
2043         if (!fullpath.isEmpty()) {
2044             pathWords[0] = fullpath;
2045             output = pathWords.join(QLatin1Char(' '));
2046         }
2047     }
2048     return output;
2049 }