File indexing completed on 2024-04-28 16:30:11
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 defines classes SKGImportExportManager. 0008 * 0009 * @author Stephane MANKOWSKI / Guillaume DE BURE 0010 */ 0011 #include "skgimportexportmanager.h" 0012 0013 #include <klocalizedstring.h> 0014 #include <kservicetypetrader.h> 0015 0016 #include <qcryptographichash.h> 0017 #include <qfileinfo.h> 0018 #include <qregularexpression.h> 0019 #include <qtemporaryfile.h> 0020 #include <qtextcodec.h> 0021 #include <qstandardpaths.h> 0022 0023 #include <algorithm> 0024 0025 #include "skgbankincludes.h" 0026 #include "skgimportplugin.h" 0027 #include "skgobjectbase.h" 0028 #include "skgpayeeobject.h" 0029 #include "skgrecurrentoperationobject.h" 0030 #include "skgruleobject.h" 0031 #include "skgservices.h" 0032 #include "skgtraces.h" 0033 0034 SKGImportExportManager::SKGImportExportManager(SKGDocumentBank* iDocument, 0035 QUrl iFileName) 0036 : m_document(iDocument), m_fileName(iFileName), 0037 m_defaultAccount(nullptr), m_defaultUnit(nullptr), 0038 m_importPlugin(nullptr), m_exportPlugin(nullptr) 0039 { 0040 SKGTRACEINFUNC(10) 0041 setAutomaticValidation(true); 0042 setAutomaticApplyRules(false); 0043 m_since_last_import = true; 0044 m_codec = QStringLiteral("UTF-8"); 0045 } 0046 0047 SKGImportExportManager::~SKGImportExportManager() 0048 { 0049 SKGTRACEINFUNC(10) 0050 setDefaultAccount(nullptr); 0051 setDefaultUnit(nullptr); 0052 m_document = nullptr; 0053 m_importPlugin = nullptr; 0054 m_exportPlugin = nullptr; 0055 0056 if (!m_localFileName.isEmpty() && m_localFileName != getFileName().toLocalFile()) { 0057 QFile(m_localFileName).remove(); 0058 } 0059 } 0060 0061 QString SKGImportExportManager::getFileNameExtension() const 0062 { 0063 return QFileInfo(getFileName().path()).suffix().toUpper(); 0064 } 0065 0066 QUrl SKGImportExportManager::getFileName() const 0067 { 0068 return m_fileName; 0069 } 0070 0071 QString SKGImportExportManager::getLocalFileName(bool iDownload) 0072 { 0073 if (m_localFileName.isEmpty()) { 0074 if (getFileName().isLocalFile()) { 0075 m_localFileName = getFileName().toLocalFile(); 0076 } else { 0077 if (iDownload) { 0078 SKGServices::download(QUrl(getFileName()), m_localFileName); 0079 } else { 0080 QTemporaryFile tmpFile; 0081 tmpFile.setAutoRemove(false); 0082 tmpFile.open(); 0083 m_localFileName = tmpFile.fileName(); 0084 } 0085 } 0086 } 0087 return m_localFileName; 0088 } 0089 0090 SKGError SKGImportExportManager::setDefaultAccount(SKGAccountObject* iAccount) 0091 { 0092 SKGError err; 0093 SKGTRACEINFUNCRC(10, err) 0094 delete m_defaultAccount; 0095 m_defaultAccount = nullptr; 0096 if (iAccount != nullptr) { 0097 m_defaultAccount = new SKGAccountObject(*iAccount); 0098 } 0099 return err; 0100 } 0101 0102 SKGError SKGImportExportManager::getDefaultAccount(SKGAccountObject& oAccount) 0103 { 0104 SKGError err; 0105 SKGTRACEINFUNCRC(10, err) 0106 if (m_defaultAccount == nullptr && (m_document != nullptr)) { 0107 QFileInfo fInfo(getLocalFileName()); 0108 QStringList items = fInfo.completeBaseName().split('@'); 0109 QString bankname; 0110 QString number; 0111 QString nameComplete; 0112 QString name = items.at(0); 0113 if (items.count() == 2) { 0114 bankname = items.at(1); 0115 0116 QStringList items2 = name.split('-'); 0117 if (items2.count() == 2) { 0118 name = items2.at(0); 0119 number = items2.at(1); 0120 } else { 0121 number = name; 0122 } 0123 0124 name.replace('_', ' '); 0125 name.replace('-', ' '); 0126 nameComplete = name; 0127 } else { 0128 bankname = name; 0129 0130 name.replace('_', ' '); 0131 name.replace('-', ' '); 0132 nameComplete = name; 0133 auto match = QRegularExpression(QStringLiteral("(\\d{6,})")).match(name); 0134 if (match.hasMatch()) { 0135 number = match.captured(1); 0136 if (name != number) { 0137 name = name.remove(number).trimmed(); 0138 } 0139 } 0140 } 0141 0142 // Searching if an account exist 0143 QString whereClause = "t_name='" % SKGServices::stringToSqlString(name) % '\''; 0144 whereClause += " OR t_number='" % SKGServices::stringToSqlString(name) % "'"; 0145 whereClause += " OR t_agency_number||t_number='" % SKGServices::stringToSqlString(name) % "'"; 0146 whereClause += " OR t_BANK_NUMBER||t_agency_number||t_number='" % SKGServices::stringToSqlString(name) % "'"; 0147 whereClause += " OR (t_number!='' AND '" % SKGServices::stringToSqlString(name) % "' LIKE t_BANK_NUMBER||t_agency_number||t_number||'__')"; 0148 if (!number.isEmpty()) { 0149 whereClause += " OR t_number='" % SKGServices::stringToSqlString(number) % "'"; 0150 whereClause += " OR t_agency_number||t_number='" % SKGServices::stringToSqlString(number) % "'"; 0151 whereClause += " OR t_BANK_NUMBER||t_agency_number||t_number='" % SKGServices::stringToSqlString(number) % "'"; 0152 whereClause += " OR (t_number!='' AND '" % SKGServices::stringToSqlString(number) % "' LIKE t_BANK_NUMBER||t_agency_number||t_number||'__')"; 0153 } 0154 whereClause += " OR EXISTS(SELECT 1 FROM parameters WHERE t_uuid_parent=v_account.id||'-account' AND t_name='alias' AND t_value= '" % SKGServices::stringToSqlString(name) % "')"; 0155 0156 const auto words = nameComplete.split(' '); 0157 for (const auto& val : words) { 0158 whereClause += " OR t_number='" % SKGServices::stringToSqlString(val) % '\''; 0159 } 0160 0161 // Avoid to take account with another import ongoing 0162 whereClause = '(' % whereClause % ") AND NOT EXISTS(SELECT 1 FROM operation WHERE operation.rd_account_id=v_account.id AND operation.t_imported='T')"; 0163 0164 SKGObjectBase::SKGListSKGObjectBase listAccount; 0165 err = m_document->getObjects(QStringLiteral("v_account"), whereClause % " ORDER BY t_type ASC", listAccount); 0166 IFOK(err) { 0167 if (!listAccount.isEmpty()) { 0168 // Yes ! Only one account found 0169 SKGAccountObject account(listAccount.at(0)); 0170 m_defaultAccount = new SKGAccountObject(account); 0171 err = m_document->sendMessage(i18nc("An information message", "Using account '%1' for import", account.getName())); 0172 } 0173 } 0174 0175 // If better account not found, then we must create one 0176 if (m_defaultAccount == nullptr) { 0177 SKGAccountObject account; 0178 SKGBankObject bank(m_document); 0179 IFOKDO(err, bank.setName(bankname)) 0180 if (!err && bank.load().isFailed()) { 0181 err = bank.save(false); // Save only 0182 } 0183 IFOKDO(err, bank.addAccount(account)) 0184 IFOKDO(err, account.setNumber(number)) 0185 int index = 1; 0186 do { 0187 IFOKDO(err, account.setName(name % (index > 1 ? SKGServices::intToString(index) : QString()))) 0188 IFOK(err) { 0189 if (account.exist()) { 0190 ++index; 0191 } else { 0192 err = account.save(false); // Save only 0193 index = -1; 0194 } 0195 } 0196 } while (!err && index > 0); 0197 IFOK(err) { 0198 m_defaultAccount = new SKGAccountObject(account); 0199 } 0200 IFOKDO(err, m_document->sendMessage(i18nc("An information message", "Default account '%1' created for import", name))) 0201 } 0202 } 0203 0204 if (m_defaultAccount != nullptr) { 0205 oAccount = *m_defaultAccount; 0206 } 0207 0208 return err; 0209 } 0210 0211 SKGError SKGImportExportManager::setDefaultUnit(SKGUnitObject* iUnit) 0212 { 0213 SKGError err; 0214 SKGTRACEINFUNCRC(10, err) 0215 delete m_defaultUnit; 0216 m_defaultUnit = nullptr; 0217 if (iUnit != nullptr) { 0218 m_defaultUnit = new SKGUnitObject(*iUnit); 0219 } 0220 return err; 0221 } 0222 0223 SKGError SKGImportExportManager::getDefaultUnit(SKGUnitObject& oUnit, const QDate* iDate) 0224 { 0225 SKGError err; 0226 SKGTRACEINFUNCRC(10, err) 0227 if ((m_document != nullptr) && (m_defaultUnit == nullptr || (iDate != nullptr))) { 0228 if (m_defaultUnit != nullptr) { 0229 delete m_defaultUnit; 0230 m_defaultUnit = nullptr; 0231 } 0232 0233 // Do we have to found the best unit for a date ? 0234 QString wc = QStringLiteral("t_type IN ('1', '2', 'C')"); 0235 if (iDate != nullptr) { 0236 // Yes 0237 wc += " AND (d_MINDATE<'" % SKGServices::dateToSqlString(*iDate) % "' OR d_MINDATE IS NULL)"; 0238 } 0239 0240 // Check if a unit exist 0241 SKGObjectBase::SKGListSKGObjectBase listUnits; 0242 err = m_document->getObjects(QStringLiteral("v_unit"), wc % " ORDER BY ABS(f_CURRENTAMOUNT-1) ASC", listUnits); 0243 IFOK(err) { 0244 if (listUnits.isEmpty()) { 0245 SKGUnitObject unit(m_document); 0246 QString name = i18nc("Noun", "Unit for import"); 0247 err = unit.setName(name); 0248 if (unit.load().isFailed()) { 0249 IFOKDO(err, unit.setSymbol(name)) 0250 IFOKDO(err, unit.save(false)) 0251 0252 SKGUnitValueObject unitval; 0253 IFOKDO(err, unit.addUnitValue(unitval)) 0254 IFOKDO(err, unitval.setQuantity(1)) 0255 IFOKDO(err, unitval.setDate(QDate(1970, 1, 1))) 0256 IFOKDO(err, unitval.save(false, false)) 0257 0258 IFOKDO(err, m_document->sendMessage(i18nc("An information message", "Default unit '%1' created for import", name))) 0259 } 0260 0261 IFOK(err) m_defaultUnit = new SKGUnitObject(unit); 0262 } else { 0263 // Found, we can use it 0264 m_defaultUnit = new SKGUnitObject(listUnits.at(0)); 0265 } 0266 } 0267 } 0268 0269 if (m_defaultUnit != nullptr) { 0270 oUnit = *m_defaultUnit; 0271 } 0272 0273 return err; 0274 } 0275 0276 void SKGImportExportManager::setAutomaticValidation(bool iValidation) 0277 { 0278 m_automaticValidationOfImportedOperation = iValidation; 0279 } 0280 0281 bool SKGImportExportManager::automaticValidation() const 0282 { 0283 return m_automaticValidationOfImportedOperation; 0284 } 0285 0286 void SKGImportExportManager::setAutomaticApplyRules(bool iApply) 0287 { 0288 m_automaticApplyRulesOfImportedOperation = iApply; 0289 } 0290 0291 bool SKGImportExportManager::automaticApplyRules() const 0292 { 0293 return m_automaticApplyRulesOfImportedOperation; 0294 } 0295 0296 void SKGImportExportManager::setSinceLastImportDate(bool iSinceLast) 0297 { 0298 m_since_last_import = iSinceLast; 0299 } 0300 0301 bool SKGImportExportManager::sinceLastImportDate() const 0302 { 0303 return m_since_last_import; 0304 } 0305 0306 void SKGImportExportManager::addAccountToCheck(const SKGAccountObject& iAccount, double iBalance) 0307 { 0308 m_AccountToCheck.append(QPair<SKGAccountObject, double>(iAccount, iBalance)); 0309 } 0310 0311 QList<QPair<SKGAccountObject, double> > SKGImportExportManager::getAccountsToCheck() 0312 { 0313 return m_AccountToCheck; 0314 } 0315 0316 SKGError SKGImportExportManager::finalizeImportation() 0317 { 0318 SKGError err; 0319 if (!getImportParameters().contains(QStringLiteral("donotfinalize"))) { 0320 SKGTRACEINFUNCRC(2, err) 0321 if (m_document != nullptr) { 0322 // Count the number of transactions imported but already existing 0323 QString wc = "v_operation.t_imported='T' AND exists (SELECT 1 from v_operation op2 WHERE op2.t_imported!='T' AND op2.rd_account_id=v_operation.rd_account_id AND op2.t_import_id=v_operation.t_import_id AND ABS(op2.f_CURRENTAMOUNT-v_operation.f_CURRENTAMOUNT)<" % SKGServices::doubleToString(EPSILON) % ')'; 0324 SKGObjectBase::SKGListSKGObjectBase objects; 0325 err = m_document->getObjects(QStringLiteral("v_operation"), wc, objects); 0326 int nbOperations = objects.count(); 0327 if (!err && (nbOperations != 0)) { 0328 err = m_document->sendMessage(i18np("One transaction not imported because it already exists", "%1 transactions not imported because they already exist", nbOperations), SKGDocument::Warning); 0329 0330 SKGTRACEL(1) << "### LIST OF OPERATIONS DELETED BY IMPORT_ID ###" << SKGENDL; 0331 for (int i = 0; i < nbOperations; ++i) { 0332 SKGTRACEL(1) << objects.at(i).getDisplayName() << " " << objects.at(i).getAttribute(QStringLiteral("t_import_id")) << SKGENDL; 0333 } 0334 0335 // Remove transactions imported for nothing 0336 IFOKDO(err, m_document->executeSqliteOrder("DELETE from operation WHERE id IN (SELECT id from v_operation WHERE " % wc % ')')) 0337 } 0338 0339 // Delete transactions before the last import 0340 if (sinceLastImportDate() && !err) { 0341 SKGTRACEINRC(2, "SKGImportExportManager::finalizeImportation-since last date", err) 0342 0343 // Count the number of transactions before the last import 0344 QString wc2 = QStringLiteral("operation.t_imported='T' AND " 0345 "EXISTS(SELECT 1 FROM operation o INDEXED BY idx_operation_rd_account_id_t_imported WHERE o.rd_account_id=operation.rd_account_id AND o.t_imported IN ('Y', 'P') AND date(o.d_date,'-4 day')>operation.d_date)"); 0346 err = m_document->getObjects(QStringLiteral("operation"), wc2, objects); 0347 int nbOperations2 = objects.count(); 0348 if (!err && (nbOperations2 != 0)) { 0349 err = m_document->sendMessage(i18np("One transaction was not imported because it was dated before the last imported one, you can uncheck the option to avoid this.", "%1 transactions were not imported because they were dated before the last imported one, you can uncheck the option to avoid this.", nbOperations2), SKGDocument::Warning); 0350 0351 SKGTRACEL(1) << "### LIST OF OPERATIONS DELETED BECAUSE AFTER THE LAST ONE ###" << SKGENDL; 0352 for (int i = 0; i < nbOperations2; ++i) { 0353 SKGTRACEL(1) << objects.at(i).getDisplayName() << SKGENDL; 0354 } 0355 0356 // Remove transactions imported for nothing 0357 IFOKDO(err, m_document->executeSqliteOrder("DELETE from operation WHERE " % wc2)) 0358 } 0359 } 0360 0361 // For performances 0362 IFOKDO(err, m_document->executeSqliteOrder(QStringLiteral("ANALYZE"))) 0363 0364 // Apply rules 0365 if (!err && m_automaticApplyRulesOfImportedOperation) { 0366 SKGTRACEINRC(2, "SKGImportExportManager::finalizeImportation-apply rules", err) 0367 // Get rules 0368 SKGObjectBase::SKGListSKGObjectBase rules; 0369 IFOKDO(err, m_document->getObjects(QStringLiteral("v_rule"), QStringLiteral("1=1 ORDER BY f_sortorder"), rules)) 0370 0371 int nbRules = rules.count(); 0372 IFOKDO(err, m_document->beginTransaction("#INTERNAL#" % i18nc("Progression step", "Finalize import"), nbRules)) 0373 for (int i = 0; !err && i < nbRules; ++i) { 0374 SKGRuleObject rule(rules.at(i)); 0375 err = rule.execute(SKGRuleObject::IMPORTING); 0376 IFOKDO(err, m_document->stepForward(i + 1)) 0377 } 0378 SKGENDTRANSACTION(m_document, err) 0379 0380 // A failure in rule will not block the transaction. 0381 // A warning message is sent 0382 IFKO(err) err = m_document->sendMessage(i18nc("Warning message", "Error during execution of rules:\n%1", err.getFullMessageWithHistorical()), SKGDocument::Error); 0383 } 0384 0385 // Change imported status 0386 IFOK(err) { 0387 SKGTRACEINRC(2, "SKGImportExportManager::finalizeImportation-change imported status", err) 0388 int nbopimported = 0; 0389 err = m_document->getNbObjects(QStringLiteral("operation"), QStringLiteral("t_imported='T'"), nbopimported); 0390 IFOKDO(err, m_document->sendMessage(i18np("%1 transaction imported", "%1 transactions imported", nbopimported), SKGDocument::Positive)) 0391 IFOKDO(err, m_document->executeSqliteOrder(QStringLiteral("UPDATE operation SET t_imported='") % 0392 (m_automaticValidationOfImportedOperation ? QStringLiteral("Y") : QStringLiteral("P")) % 0393 "' WHERE t_imported='T'")); 0394 } 0395 0396 // Check balances of accounts 0397 int nb = m_AccountToCheck.count(); 0398 for (int i = 0; !err && i < nb; ++i) { 0399 // Get the account to check 0400 auto act = m_AccountToCheck.at(i).first; 0401 auto targetBalance = m_AccountToCheck.at(i).second; 0402 err = act.setAttribute(QStringLiteral("f_importbalance"), SKGServices::doubleToString(targetBalance)); 0403 IFOKDO(err, act.setAttribute(QStringLiteral("d_importdate"), SKGServices::dateToSqlString(QDate::currentDate()))) 0404 IFOKDO(err, act.save()) 0405 0406 auto soluces = act.getPossibleReconciliations(targetBalance, false); 0407 if (soluces.isEmpty()) { 0408 IFOKDO(err, m_document->sendMessage(i18nc("Information message", "The balance of account '%1' is not aligned with import balance %2", act.getDisplayName(), m_document->formatMoney(targetBalance, m_document->getPrimaryUnit())), 0409 SKGDocument::Warning, 0410 QString("skg://skrooge_operation_plugin/?title_icon=quickopen&title=" % SKGServices::encodeForUrl(i18nc("Noun, a list of items", "Transactions of account \"%1\" used for auto reconciliation", act.getDisplayName())) % 0411 "&operationWhereClause=" % SKGServices::encodeForUrl("rd_account_id=" + SKGServices::intToString(act.getID()) + " AND t_template='N' AND ((t_status='N' AND t_imported IN ('Y','P')) OR t_status='Y')")))); 0412 } else { 0413 IFOKDO(err, m_document->sendMessage(i18nc("Information message", "The balance of account '%1' is aligned with import balance %2", act.getDisplayName(), m_document->formatMoney(targetBalance, m_document->getPrimaryUnit())), SKGDocument::Positive)) 0414 } 0415 } 0416 } 0417 } 0418 return err; 0419 } 0420 0421 void SKGImportExportManager::setExportParameters(const QMap< QString, QString >& iParameters) 0422 { 0423 SKGImportPlugin* plugin = getExportPlugin(); 0424 if (plugin != nullptr) { 0425 plugin->setExportParameters(iParameters); 0426 } 0427 } 0428 0429 QMap< QString, QString > SKGImportExportManager::getExportParameters() 0430 { 0431 QMap< QString, QString > output; 0432 SKGImportPlugin* plugin = getExportPlugin(); 0433 if (plugin != nullptr) { 0434 output = plugin->getExportParameters(); 0435 } 0436 return output; 0437 } 0438 0439 void SKGImportExportManager::setImportParameters(const QMap< QString, QString >& iParameters) 0440 { 0441 SKGImportPlugin* plugin = getImportPlugin(); 0442 if (plugin != nullptr) { 0443 plugin->setImportParameters(iParameters); 0444 } 0445 } 0446 0447 void SKGImportExportManager::loadPlugins() 0448 { 0449 if (m_plugins.isEmpty()) { 0450 const auto plugins = KPluginMetaData::findPlugins(QStringLiteral("skrooge/import")); 0451 int nb = plugins.count(); 0452 for (int i = 0; i < nb; ++i) { 0453 const auto factory = KPluginFactory::loadFactory(plugins.at(i)); 0454 const auto pluginInterface = factory.plugin ? factory.plugin->create<SKGImportPlugin>(this) : nullptr; 0455 if (pluginInterface != nullptr) { 0456 m_plugins.append(pluginInterface); 0457 } 0458 } 0459 } 0460 } 0461 0462 QString SKGImportExportManager::getParameterDefaultValue(const QString& iParameter) 0463 { 0464 QString output; 0465 0466 loadPlugins(); 0467 0468 int nb = m_plugins.count(); 0469 for (int i = 0; i < nb && output.isEmpty(); ++i) { 0470 auto pluginInterface = m_plugins.at(i); 0471 auto val = pluginInterface->getImportParameters().value(iParameter); 0472 if (!val.isEmpty()) { 0473 output = val; 0474 } else { 0475 output = pluginInterface->getExportParameters().value(iParameter); 0476 } 0477 } 0478 return output; 0479 } 0480 0481 QMap< QString, QString > SKGImportExportManager::getImportParameters() 0482 { 0483 QMap< QString, QString > output; 0484 SKGImportPlugin* plugin = getImportPlugin(); 0485 if (plugin != nullptr) { 0486 output = plugin->getImportParameters(); 0487 } 0488 return output; 0489 } 0490 0491 QString SKGImportExportManager::getImportMimeTypeFilter(bool iIncludingAll) 0492 { 0493 loadPlugins(); 0494 0495 QMap<QString, QString> tmp; 0496 int nb = m_plugins.count(); 0497 for (int i = 0; i < nb; ++i) { 0498 auto pluginInterface = m_plugins.at(i); 0499 if (pluginInterface->isImportPossible()) { 0500 QString mime = pluginInterface->getMimeTypeFilter(); 0501 if (!mime.isEmpty()) { 0502 QStringList lines = SKGServices::splitCSVLine(mime, '\n'); 0503 int nblines = lines.count(); 0504 for (int l = 0; l < nblines; ++l) { 0505 QStringList items = SKGServices::splitCSVLine(lines.at(l), '|'); 0506 tmp[items.at(1)] = items.at(0); 0507 } 0508 } 0509 } 0510 } 0511 0512 QStringList descriptions = tmp.keys(); 0513 std::sort(descriptions.begin(), descriptions.end()); 0514 QStringList regexps = tmp.values(); 0515 0516 QString output; 0517 if (iIncludingAll) { 0518 output = regexps.join(QStringLiteral(" ")) % '|' % i18nc("A file format", "All supported formats"); 0519 } 0520 nb = descriptions.count(); 0521 for (int i = 0; i < nb; ++i) { 0522 if (!output.isEmpty()) { 0523 output += '\n'; 0524 } 0525 output += tmp[descriptions.at(i)] % '|' % descriptions.at(i); 0526 } 0527 0528 return output; 0529 } 0530 0531 QString SKGImportExportManager::getExportMimeTypeFilter(bool iIncludingAll) 0532 { 0533 loadPlugins(); 0534 0535 QMap<QString, QString> tmp; 0536 int nb = m_plugins.count(); 0537 for (int i = 0; i < nb; ++i) { 0538 auto pluginInterface = m_plugins.at(i); 0539 if (pluginInterface->isExportPossible()) { 0540 QString mime = pluginInterface->getMimeTypeFilter(); 0541 if (!mime.isEmpty()) { 0542 QStringList lines = SKGServices::splitCSVLine(mime, '\n'); 0543 int nblines = lines.count(); 0544 for (int l = 0; l < nblines; ++l) { 0545 QStringList items = SKGServices::splitCSVLine(lines.at(l), '|'); 0546 tmp[items.at(1)] = items.at(0); 0547 } 0548 } 0549 } 0550 } 0551 0552 QStringList descriptions = tmp.keys(); 0553 std::sort(descriptions.begin(), descriptions.end()); 0554 QStringList regexps = tmp.values(); 0555 0556 QString output; 0557 if (iIncludingAll) { 0558 output = regexps.join(QStringLiteral(" ")) % '|' % i18nc("A file format", "All supported formats"); 0559 } 0560 nb = descriptions.count(); 0561 for (int i = 0; i < nb; ++i) { 0562 if (!output.isEmpty()) { 0563 output += '\n'; 0564 } 0565 output += tmp[descriptions.at(i)] % '|' % descriptions.at(i); 0566 } 0567 0568 return output; 0569 } 0570 0571 0572 SKGImportPlugin* SKGImportExportManager::getImportPlugin() 0573 { 0574 if (m_importPlugin == nullptr) { 0575 loadPlugins(); 0576 0577 int nb = m_plugins.count(); 0578 for (int i = 0; i < nb && m_importPlugin == nullptr; ++i) { 0579 auto pluginInterface = m_plugins.at(i); 0580 if (pluginInterface->isImportPossible()) { 0581 m_importPlugin = pluginInterface; 0582 } 0583 0584 } 0585 } 0586 return m_importPlugin; 0587 } 0588 0589 SKGImportPlugin* SKGImportExportManager::getExportPlugin() 0590 { 0591 if (m_exportPlugin == nullptr) { 0592 loadPlugins(); 0593 0594 int nb = m_plugins.count(); 0595 for (int i = 0; i < nb && m_exportPlugin == nullptr; ++i) { 0596 auto pluginInterface = m_plugins.at(i); 0597 if (pluginInterface->isExportPossible()) { 0598 m_exportPlugin = pluginInterface; 0599 } 0600 0601 } 0602 } 0603 return m_exportPlugin; 0604 } 0605 0606 SKGError SKGImportExportManager::importFile() 0607 { 0608 SKGError err; 0609 SKGTRACEINFUNCRC(2, err) 0610 if (m_document != nullptr) { 0611 SKGBEGINPROGRESSTRANSACTION(*m_document, i18nc("Noun, name of the user action", "Import with codec %1", m_codec), err, 3) 0612 0613 err = m_document->executeSqliteOrder(QStringLiteral("ANALYZE")); 0614 0615 IFOKDO(err, m_document->stepForward(1)) 0616 0617 IFOK(err) { 0618 // Search plugins 0619 bool fileTreated = false; 0620 SKGImportPlugin* pluginInterface = getImportPlugin(); 0621 if (pluginInterface != nullptr) { 0622 // Import 0623 fileTreated = true; 0624 m_AccountToCheck.clear(); 0625 SKGTRACEL(2) << "Input filename=" << m_fileName.toDisplayString() << SKGENDL; 0626 err = pluginInterface->importFile(); 0627 } 0628 0629 if (!err && !fileTreated) { 0630 err.setReturnCode(ERR_NOTIMPL).setMessage(i18nc("Error message", "The import mode %1 is not yet implemented", getFileNameExtension())); 0631 } 0632 } 0633 IFOKDO(err, m_document->stepForward(2)) 0634 0635 IFOKDO(err, finalizeImportation()) 0636 IFOKDO(err, m_document->stepForward(3)) 0637 } 0638 0639 return err; 0640 } 0641 0642 SKGError SKGImportExportManager::exportFile() 0643 { 0644 SKGError err; 0645 SKGTRACEINFUNCRC(2, err) 0646 if (m_document != nullptr) { 0647 err = m_document->executeSqliteOrder(QStringLiteral("ANALYZE")); 0648 IFOK(err) { 0649 // Search plugins 0650 bool fileTreated = false; 0651 SKGImportPlugin* pluginInterface = getExportPlugin(); 0652 if (pluginInterface != nullptr) { 0653 // Import 0654 fileTreated = true; 0655 SKGTRACEL(2) << "Input filename=" << m_fileName.toDisplayString() << SKGENDL; 0656 err = pluginInterface->exportFile(); 0657 IFOKDO(err, SKGServices::upload(QUrl::fromLocalFile(getLocalFileName(false)), m_fileName)) 0658 } 0659 0660 if (!err && !fileTreated) { 0661 err.setReturnCode(ERR_NOTIMPL).setMessage(i18nc("Error message", "This export mode is not yet implemented")); 0662 } 0663 } 0664 } 0665 0666 return err; 0667 } 0668 0669 SKGError SKGImportExportManager::cleanBankImport() 0670 { 0671 SKGError err; 0672 SKGTRACEINFUNCRC(2, err) 0673 0674 // Begin transaction 0675 if (m_document != nullptr) { 0676 err = m_document->beginTransaction("#INTERNAL#" % i18nc("Progression step", "Clean import"), 3); 0677 IFOK(err) { 0678 // Step 1 Clean transactions without mode and with comment with double space 0679 SKGObjectBase::SKGListSKGObjectBase transactions; 0680 IFOKDO(err, m_document->getObjects(QStringLiteral("operation"), QStringLiteral("t_imported!='N' and t_mode='' and t_comment like '% %'"), transactions)) 0681 0682 int nb = transactions.count(); 0683 for (int i = 0; !err && i < nb ; ++i) { 0684 SKGOperationObject op(transactions.at(i)); 0685 0686 // Comment is like this: <TYPE> <INFO> 0687 // Example: RETRAIT DAB 20/01/08 11H44 013330 LCL GAILLAC 000497 0688 QString comment = op.getComment(); 0689 auto match = QRegularExpression(QStringLiteral("(.+) {2,}(.+)")).match(comment); 0690 if (match.hasMatch()) { 0691 // Get parameters 0692 QString mode = match.captured(1); 0693 QString info = match.captured(2); 0694 0695 // Modify 0696 err = op.setComment(info.trimmed()); 0697 IFOKDO(err, op.setMode(mode.trimmed())) 0698 IFOKDO(err, op.save(true, false)) // No reload 0699 } 0700 } 0701 0702 // Step 1 done 0703 IFOKDO(err, m_document->stepForward(1)) 0704 0705 // Step 2 Clean transactions without mode and with comment 0706 IFOKDO(err, m_document->getObjects(QStringLiteral("operation"), QStringLiteral("t_imported!='N' and t_mode='' and t_comment!=''"), transactions)) 0707 0708 nb = transactions.count(); 0709 for (int i = 0; !err && i < nb ; ++i) { 0710 SKGOperationObject op(transactions.at(i)); 0711 0712 // Comment is like this: <TYPE> <INFO> 0713 // Example: RETRAIT DAB 14-05-16607-482390 0714 QString comment = op.getComment(); 0715 auto match = QRegularExpression(QStringLiteral("(\\S+) +(.+)")).match(comment); 0716 if (match.hasMatch()) { 0717 // Get parameters 0718 QString mode = match.captured(1); 0719 QString info = match.captured(2); 0720 0721 // Modify 0722 err = op.setComment(info.trimmed()); 0723 IFOKDO(err, op.setMode(mode.trimmed())) 0724 IFOKDO(err, op.save(true, false)) // No reload 0725 } 0726 } 0727 0728 // Step 2 done 0729 IFOKDO(err, m_document->stepForward(2)) 0730 0731 // Step 3 Clean cheque without number 0732 IFOKDO(err, m_document->getObjects(QStringLiteral("operation"), QStringLiteral("t_imported!='N' and t_number='' and lower(t_mode)='cheque'"), transactions)) 0733 0734 nb = transactions.count(); 0735 for (int i = 0; !err && i < nb ; ++i) { 0736 SKGOperationObject op(transactions.at(i)); 0737 0738 // Comment is like this: <TYPE> <INFO> 0739 // Example: RETRAIT DAB 20/01/08 11H44 013330 LCL GAILLAC 000497 0740 QString comment = op.getComment(); 0741 auto match = QRegularExpression(QStringLiteral("(\\d+)")).match(comment); 0742 if (match.hasMatch()) { 0743 // Get parameters 0744 auto number = match.captured(1); 0745 0746 // Modify 0747 err = op.setNumber(number); 0748 IFOKDO(err, op.save(true, false)) // No reload 0749 } 0750 } 0751 0752 // Step 3 done 0753 IFOKDO(err, m_document->stepForward(3)) 0754 } 0755 0756 SKGENDTRANSACTION(m_document, err) 0757 } 0758 0759 return err; 0760 } 0761 0762 SKGError SKGImportExportManager::anonymize(const QString& iKey) 0763 { 0764 SKGError err; 0765 SKGTRACEINFUNCRC(2, err) 0766 if (m_document != nullptr) { 0767 if (m_document->isFileModified()) { 0768 err = SKGError(ERR_ABORT, i18nc("An information message", "The document must be saved to be anonymized."), QStringLiteral("skg://file_save")); 0769 } else { 0770 { 0771 // Remove password 0772 m_document->changePassword(QLatin1String("")); 0773 0774 // Anonymize data 0775 QStringList sqlOrders; 0776 if (iKey.isEmpty()) { 0777 // Not reversible 0778 sqlOrders << QStringLiteral("UPDATE bank SET t_bank_number='', t_name='bank_'||id") 0779 << QStringLiteral("UPDATE account SET t_number='', t_agency_number='', t_agency_address='', t_comment='', t_name='account_'||id") 0780 << QStringLiteral("UPDATE category SET t_name='category_'||id") 0781 << QStringLiteral("UPDATE payee SET t_address='', t_name='payee_'||id") 0782 << QStringLiteral("UPDATE refund SET t_comment='', t_name='tracker_'||id") 0783 << QStringLiteral("UPDATE operation SET t_comment=''") 0784 << QStringLiteral("UPDATE suboperation SET t_comment='', f_value=f_value%1234.56") 0785 << QStringLiteral("DELETE FROM parameters WHERE t_name NOT LIKE 'SKG_%' OR t_name='SKG_PASSWORD'"); 0786 } else { 0787 // Reversible 0788 sqlOrders << QStringLiteral("UPDATE bank SET t_bank_number=XOR(t_bank_number,'") + SKGServices::stringToSqlString(iKey) + "'), t_name=XOR(t_name,'" + SKGServices::stringToSqlString(iKey) + "')" 0789 << QStringLiteral("UPDATE account SET t_number=XOR(t_number,'") + SKGServices::stringToSqlString(iKey) + "'), t_agency_number=XOR(t_agency_number,'" + SKGServices::stringToSqlString(iKey) + "'), t_agency_address=XOR(t_agency_address,'" + SKGServices::stringToSqlString(iKey) + "'), t_comment=XOR(t_comment,'" + SKGServices::stringToSqlString(iKey) + "'), t_name=XOR(t_name,'" + SKGServices::stringToSqlString(iKey) + "')" 0790 << QStringLiteral("UPDATE category SET t_name=XOR(t_name,'") + SKGServices::stringToSqlString(iKey) + "')" 0791 << QStringLiteral("UPDATE payee SET t_address=XOR(t_address,'") + SKGServices::stringToSqlString(iKey) + "'), t_name=XOR(t_name,'" + SKGServices::stringToSqlString(iKey) + "')" 0792 << QStringLiteral("UPDATE refund SET t_comment=XOR(t_comment,'") + SKGServices::stringToSqlString(iKey) + "'), t_name=XOR(t_name,'" + SKGServices::stringToSqlString(iKey) + "')" 0793 << QStringLiteral("UPDATE operation SET t_comment=XOR(t_comment,'") + SKGServices::stringToSqlString(iKey) + "')" 0794 << QStringLiteral("UPDATE suboperation SET t_comment=XOR(t_comment,'") + SKGServices::stringToSqlString(iKey) + "'), f_value=XORD(f_value,'" + SKGServices::stringToSqlString(iKey) + "')" 0795 << QStringLiteral("UPDATE parameters SET t_name=XOR(t_name,'") + SKGServices::stringToSqlString(iKey) + "'), t_value=XOR(t_value,'" + SKGServices::stringToSqlString(iKey) + "'), b_blob=XOR(b_blob,'" + SKGServices::stringToSqlString(iKey) + "') WHERE t_name NOT LIKE 'SKG_%'" 0796 << QStringLiteral("DELETE FROM parameters WHERE t_name='SKG_PASSWORD'"); 0797 } 0798 0799 int nb = sqlOrders.count(); 0800 SKGBEGINPROGRESSTRANSACTION(*m_document, "##INTERNAL##" % i18nc("Progression step", "Anonymize"), err, nb) 0801 for (int i = 0; !err && i < nb; ++i) { 0802 err = m_document->executeSqliteOrder(sqlOrders.at(i)); 0803 IFOKDO(err, m_document->stepForward(i + 1)) 0804 } 0805 0806 if (iKey.isEmpty()) { 0807 m_document->sendMessage(i18nc("An information message", "The document has been made anonymous with an irreversible mode."), SKGDocument::Positive); 0808 } else { 0809 m_document->sendMessage(i18nc("An information message", "The document has been made anonymous with a reversible mode. Don't forget the key if you want to reverse it."), SKGDocument::Information); 0810 } 0811 } 0812 0813 // Remove transactions 0814 IFOKDO(err, m_document->removeAllTransactions()) 0815 0816 // Save new file 0817 QString newName = m_document->getCurrentFileName().replace(QStringLiteral(".skg"), QStringLiteral("-anonymized.skg")); 0818 if (newName == m_document->getCurrentFileName()) { 0819 newName = m_document->getCurrentFileName() % "-anonymized.skg"; 0820 } 0821 IFOKDO(err, m_document->saveAs(newName, true)) 0822 } 0823 } 0824 return err; 0825 } 0826 0827 SKGError SKGImportExportManager::findAndGroupTransfers(int& oNbOperationsMerged, const QString& iAdditionnalCondition) 0828 { 0829 SKGError err; 0830 SKGTRACEINFUNCRC(2, err) 0831 0832 oNbOperationsMerged = 0; 0833 0834 // Begin transaction 0835 if (m_document != nullptr) { 0836 err = m_document->beginTransaction("#INTERNAL#" % i18nc("Progression step", "Find and group transfers"), 3); 0837 IFOK(err) { 0838 IFOKDO(err, m_document->executeSqliteOrder(QStringLiteral("ANALYZE"))) 0839 // Look for transactions with 0840 // Same units 0841 // Same dates 0842 // Null i_group_id 0843 // Different accounts 0844 // Opposite amounts 0845 SKGStringListList listTmp; 0846 { 0847 SKGTRACEIN(2, "SKGImportExportManager::findAndGroupTransfers-step 1") 0848 IFOKDO(err, m_document->executeSelectSqliteOrder( 0849 "SELECT ID1, ID2 FROM (SELECT A.id as ID1, B.id as ID2, (SELECT TOTAL(s.f_value) FROM suboperation s " 0850 "WHERE s.rd_operation_id=A.ID) AS quantity1, (SELECT TOTAL(s.f_value) FROM suboperation s " 0851 "WHERE s.rd_operation_id=B.ID) AS quantity2 FROM operation A, operation B " 0852 "WHERE +A.d_date=B.d_date AND A.rc_unit_id=B.rc_unit_id AND A.id<=B.id AND A.rd_account_id!=B.rd_account_id AND A.i_group_id=0 AND B.i_group_id=0" % 0853 (iAdditionnalCondition.isEmpty() ? QString() : QStringLiteral(" AND ") % iAdditionnalCondition) % ") " 0854 "WHERE ABS(quantity1+quantity2)<1e-5 AND quantity1!=0", 0855 listTmp)); 0856 // Step 1 done 0857 IFOKDO(err, m_document->stepForward(1)) 0858 } 0859 0860 SKGStringListList listTmp2; 0861 { 0862 SKGTRACEIN(2, "SKGImportExportManager::findAndGroupTransfers-step 2") 0863 // +A.i_group_id=0 AND +B.i_group_id=0 is for avoiding to use bad index 0864 IFOKDO(err, m_document->executeSelectSqliteOrder( 0865 "SELECT A.id, B.id FROM v_operation A, operation B, parameters P " 0866 "WHERE +P.t_name='SKG_OP_ORIGINAL_AMOUNT' AND +P.t_uuid_parent=B.id||'-operation' AND A.rc_unit_id!=B.rc_unit_id AND A.d_date=B.d_date AND A.rd_account_id!=B.rd_account_id AND ABS(A.f_CURRENTAMOUNT+CAST(P.t_value AS REAL))<" % SKGServices::doubleToString(EPSILON) % " AND +A.i_group_id=0 AND +B.i_group_id=0 AND A.f_CURRENTAMOUNT!=0" % 0867 (iAdditionnalCondition.isEmpty() ? QString() : QStringLiteral(" AND ") % iAdditionnalCondition), 0868 listTmp2)); 0869 0870 // Step 2 done 0871 IFOKDO(err, m_document->stepForward(2)) 0872 listTmp2.removeAt(0); // Remove header 0873 listTmp += listTmp2; 0874 } 0875 0876 // Group 0877 { 0878 SKGTRACEIN(2, "SKGImportExportManager::findAndGroupTransfers-group") 0879 oNbOperationsMerged = listTmp.count(); 0880 IFOKDO(err, m_document->beginTransaction("#INTERNAL#" % i18nc("Progression step", "Find and group transfers"), oNbOperationsMerged - 1)) 0881 for (int i = 1; !err && i < oNbOperationsMerged ; ++i) { // First line ignored because of its header 0882 SKGOperationObject op1(m_document, SKGServices::stringToInt(listTmp.at(i).at(0))); 0883 SKGOperationObject op2(m_document, SKGServices::stringToInt(listTmp.at(i).at(1))); 0884 0885 if (!op1.isInGroup() && !op2.isInGroup()) { 0886 err = op2.setGroupOperation(op1); 0887 IFOKDO(err, op2.save(true, false)) 0888 } 0889 IFOKDO(err, m_document->stepForward(i)) 0890 } 0891 SKGENDTRANSACTION(m_document, err) 0892 } 0893 oNbOperationsMerged = (oNbOperationsMerged - 1) * 2; 0894 0895 // Step 3 done 0896 IFOKDO(err, m_document->stepForward(3)) 0897 } 0898 SKGENDTRANSACTION(m_document, err) 0899 } 0900 0901 return err; 0902 } 0903 0904 SKGError SKGImportExportManager::findAndGroupTransfers(int& oNbOperationsMerged, bool iOnCurrentlyImport) 0905 { 0906 return findAndGroupTransfers(oNbOperationsMerged, iOnCurrentlyImport ? QStringLiteral("A.t_imported='T' AND B.t_imported='T'") : QLatin1String("")); 0907 } 0908 0909 SKGError SKGImportExportManager::getXMLDocument(QDomElement& oDocument) 0910 { 0911 SKGError err; 0912 SKGTRACEINFUNCRC(2, err) 0913 0914 // Open file 0915 QFile file(getLocalFileName()); 0916 if (!file.open(QIODevice::ReadOnly)) { 0917 err.setReturnCode(ERR_INVALIDARG).setMessage(i18nc("Error message", "Open file '%1' failed", getFileName().toDisplayString())); 0918 } else { 0919 QDomDocument doc; 0920 0921 // Set the file without uncompression 0922 QString errorMsg; 0923 int errorLine = 0; 0924 int errorCol = 0; 0925 bool contentOK = doc.setContent(file.readAll(), &errorMsg, &errorLine, &errorCol); 0926 file.close(); 0927 0928 if (!contentOK) { 0929 err.setReturnCode(ERR_ABORT).setMessage(i18nc("Error message", "%1-%2: '%3'", errorLine, errorCol, errorMsg)).addError(ERR_INVALIDARG, i18nc("Error message", "Invalid XML content in file '%1'", getFileName().toDisplayString())); 0930 } else { 0931 // Get root 0932 oDocument = doc.documentElement(); 0933 } 0934 } 0935 0936 return err; 0937 } 0938 0939 SKGError SKGImportExportManager::importItems(QDomElement& docElem) 0940 { 0941 SKGError err; 0942 if (docElem.isNull()) { 0943 return err; 0944 } 0945 auto type = docElem.tagName().remove(QStringLiteral("list_")); 0946 QDomNodeList items = docElem.elementsByTagName(type); 0947 int nb = items.count(); 0948 if (nb > 0) { 0949 SKGTRACEINRC(10, "SKGImportPluginXml::importFile-" + type, err) 0950 err = getDocument()->beginTransaction("#INTERNAL#" % i18nc("Import step", "Import %1", type), nb); 0951 for (int j = 0; !err && j < nb; ++j) { 0952 QDomNode node = items.at(j); 0953 QDomElement item = node.toElement(); 0954 SKGObjectBase* obj; 0955 if (type == "category") { 0956 obj = new SKGCategoryObject(getDocument()); 0957 } else if (type == "unit") { 0958 obj = new SKGUnitObject(getDocument()); 0959 } else if (type == "unitvalue") { 0960 obj = new SKGUnitValueObject(getDocument()); 0961 } else if (type == "refund") { 0962 obj = new SKGTrackerObject(getDocument()); 0963 } else if (type == "bank") { 0964 obj = new SKGBankObject(getDocument()); 0965 } else if (type == "rule") { 0966 obj = new SKGRuleObject(getDocument()); 0967 } else if (type == "account") { 0968 obj = new SKGAccountObject(getDocument()); 0969 } else if (type == "payee") { 0970 obj = new SKGPayeeObject(getDocument()); 0971 } else if (type == "operation") { 0972 obj = new SKGOperationObject(getDocument()); 0973 } else if (type == "suboperation") { 0974 obj = new SKGSubOperationObject(getDocument()); 0975 } else if (type == "budget") { 0976 obj = new SKGBudgetObject(getDocument()); 0977 } else if (type == "budgetrule") { 0978 obj = new SKGBudgetRuleObject(getDocument()); 0979 } else if (type == "interest") { 0980 obj = new SKGInterestObject(getDocument()); 0981 } else if (type == "recurrentoperation") { 0982 obj = new SKGRecurrentOperationObject(getDocument()); 0983 } else if (type == "rule") { 0984 obj = new SKGRuleObject(getDocument()); 0985 } else { 0986 obj = new SKGObjectBase(getDocument(), type); 0987 } 0988 0989 auto attributes = item.attributes(); 0990 int nb2 = attributes.count(); 0991 for (int i = 0 ; !err && i < nb2; ++i) { 0992 auto att = attributes.item(i); 0993 0994 auto name = att.nodeName(); 0995 if (name != "id" && name != "r_recurrentoperation_id") { 0996 auto val = att.nodeValue(); 0997 if (name.startsWith(QStringLiteral("rd_")) || name.startsWith(QStringLiteral("rc_")) || name.startsWith(QStringLiteral("r_"))) { 0998 auto tmp = name.split("_"); 0999 if (m_mapIdObject.contains(tmp[1] + "-" + val)) { 1000 val = SKGServices::intToString(m_mapIdObject[tmp[1] + "-" + val].getID()); 1001 } else { 1002 val = ""; 1003 } 1004 } 1005 IFOKDO(err, obj->setAttribute(name, val)) 1006 } 1007 } 1008 if (type == "operation") { 1009 QString str; 1010 QTextStream stream(&str); 1011 node.save(stream, 0); 1012 QByteArray hash = QCryptographicHash::hash(str.toUtf8(), QCryptographicHash::Md5); 1013 IFOKDO(err, static_cast<SKGOperationObject*>(obj)->setImportID("XML-" + QString(hash.toHex()))) 1014 } 1015 if (obj->exist()) { 1016 IFOKDO(err, obj->load()) 1017 } else { 1018 IFOKDO(err, obj->save(true, true)) 1019 } 1020 m_mapIdObject[type + "-" + item.attribute(QStringLiteral("id"))] = *obj; 1021 1022 delete obj; 1023 1024 IFOKDO(err, getDocument()->stepForward(j + 1)) 1025 } 1026 SKGENDTRANSACTION(getDocument(), err) 1027 } 1028 return err; 1029 }