File indexing completed on 2024-06-23 05:03: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 is Skrooge plugin for CSV import / export. 0008 * 0009 * @author Stephane MANKOWSKI / Guillaume DE BURE 0010 */ 0011 #include "skgimportplugincsv.h" 0012 0013 #include <klocalizedstring.h> 0014 0015 #include <kpluginfactory.h> 0016 0017 #include <qcryptographichash.h> 0018 #include <qfileinfo.h> 0019 #include <qsavefile.h> 0020 #include <qregularexpression.h> 0021 0022 #include "skgbankincludes.h" 0023 #include "skgimportexportmanager.h" 0024 #include "skgservices.h" 0025 #include "skgtraces.h" 0026 0027 /** 0028 * This plugin factory. 0029 */ 0030 K_PLUGIN_CLASS_WITH_JSON(SKGImportPluginCsv, "metadata.json") 0031 0032 SKGImportPluginCsv::SKGImportPluginCsv(QObject* iImporter, const QVariantList& iArg) 0033 : SKGImportPlugin(iImporter), 0034 m_csvSeparator(QChar()), m_csvHeaderIndex(-1) 0035 { 0036 SKGTRACEINFUNC(10) 0037 Q_UNUSED(iArg) 0038 0039 m_importParameters[QStringLiteral("mapping_date")] = i18nc("This is a regular expression to find the column in a csv file. You should keep the ^ and translate the word", "^date"); 0040 m_importParameters[QStringLiteral("mapping_account")] = i18nc("This is a regular expression to find the column in a csv file. You should keep the ^ and translate the word", "^account"); 0041 m_importParameters[QStringLiteral("mapping_number")] = i18nc("This is a regular expression to find the column in a csv file. You should keep the ^ and | and translate the words", "^number|^num?ro"); 0042 m_importParameters[QStringLiteral("mapping_mode")] = i18nc("This is a regular expression to find the column in a csv file. You should keep the ^ and | and translate the words", "^mode|^type"); 0043 m_importParameters[QStringLiteral("mapping_payee")] = i18nc("This is a regular expression to find the column in a csv file. You should keep the ^ and | and translate the words", "^payee|^tiers"); 0044 m_importParameters[QStringLiteral("mapping_comment")] = i18nc("This is a regular expression to find the column in a csv file. You should keep the ^ and | and translate the words", "^comment|^libell?|^d?tail|^info"); 0045 m_importParameters[QStringLiteral("mapping_status")] = i18nc("This is a regular expression to find the column in a csv file. You should keep the ^ and | and translate the words", "^status|^pointage"); 0046 m_importParameters[QStringLiteral("mapping_bookmarked")] = i18nc("This is a regular expression to find the column in a csv file. You should keep the ^ and translate the word", "^bookmarked"); 0047 m_importParameters[QStringLiteral("mapping_category")] = i18nc("This is a regular expression to find the column in a csv file. You should keep the ^ and translate the word", "^cat\\w*gor\\w*"); 0048 m_importParameters[QStringLiteral("mapping_amount")] = i18nc("This is a regular expression to find the column in a csv file. You should keep the ^ and | and translate the words", "^value|^amount|^valeur|^montant|^credit|^debit"); 0049 m_importParameters[QStringLiteral("mapping_quantity")] = i18nc("This is a regular expression to find the column in a csv file. You should keep the ^ and translate the word", "^quantity"); 0050 m_importParameters[QStringLiteral("mapping_unit")] = i18nc("This is a regular expression to find the column in a csv file. You should keep the ^ and translate the word", "^unit"); 0051 m_importParameters[QStringLiteral("mapping_sign")] = i18nc("This is a regular expression to find the column in a csv file. You should keep the ^ and | and translate the words", "^sign|^sens"); 0052 m_importParameters[QStringLiteral("mapping_debit")] = i18nc("This is a regular expression to find the column in a csv file. You should keep the ^ and | and translate the words", "^-|^debit|^withdrawal"); 0053 m_importParameters[QStringLiteral("mapping_idgroup")] = i18nc("This is a regular expression to find the column in a csv file. You should keep the ^ and translate the word", "^idgroup"); 0054 m_importParameters[QStringLiteral("mapping_idtransaction")] = i18nc("This is a regular expression to find the column in a csv file. You should keep the ^ and translate the word", "^idtransaction"); 0055 m_importParameters[QStringLiteral("mapping_property")] = QString(); 0056 0057 m_importParameters[QStringLiteral("automatic_search_header")] = 'Y'; 0058 m_importParameters[QStringLiteral("header_position")] = '1'; 0059 m_importParameters[QStringLiteral("automatic_search_columns")] = 'Y'; 0060 m_importParameters[QStringLiteral("columns_positions")] = QString(); 0061 0062 m_importParameters[QStringLiteral("mode_csv_unit")] = 'N'; 0063 m_importParameters[QStringLiteral("mode_csv_rule")] = 'N'; 0064 0065 m_importParameters[QStringLiteral("date_format")] = QString(); 0066 } 0067 0068 SKGImportPluginCsv::~SKGImportPluginCsv() 0069 = default; 0070 0071 void SKGImportPluginCsv::setImportParameters(const QMap< QString, QString >& iParameters) 0072 { 0073 SKGImportPlugin::setImportParameters(iParameters); 0074 0075 if (m_importParameters.value(QStringLiteral("automatic_search_header")) == QStringLiteral("N")) { 0076 int header_position = SKGServices::stringToInt(m_importParameters.value(QStringLiteral("header_position"))); 0077 setCSVHeaderIndex(header_position); 0078 } 0079 0080 if (m_importParameters.value(QStringLiteral("automatic_search_columns")) == QStringLiteral("N")) { 0081 QStringList columns_positions = m_importParameters.value(QStringLiteral("columns_positions")).split('|'); 0082 if (m_csvHeaderIndex == -1) { 0083 m_csvHeaderIndex = 1; 0084 } 0085 setCSVMapping(&columns_positions); 0086 } 0087 } 0088 0089 bool SKGImportPluginCsv::isImportPossible() 0090 { 0091 SKGTRACEINFUNC(10) 0092 return isExportPossible(); 0093 } 0094 0095 QStringList SKGImportPluginCsv::getCSVMappingFromLine(const QString& iLine) 0096 { 0097 QStringList output; 0098 QString line = iLine.trimmed(); 0099 0100 // Split first line 0101 QStringList csvAttributes = SKGServices::splitCSVLine(line, getCSVSeparator(iLine), true); 0102 int nb = csvAttributes.count(); 0103 0104 for (int i = 0; i < nb; ++i) { 0105 QString att = csvAttributes.at(i).toLower(); 0106 0107 // Search if this csv column is mapping a std attribute 0108 QMapIterator<QString, QString> csvMapperIterator(m_importParameters); 0109 bool found = false; 0110 while (!found && csvMapperIterator.hasNext()) { 0111 csvMapperIterator.next(); 0112 0113 QString key = csvMapperIterator.key(); 0114 if (key.startsWith(QLatin1String("mapping_"))) { 0115 key = key.right(key.length() - 8); 0116 if (key != QStringLiteral("debit") && 0117 key != QStringLiteral("property") && 0118 !csvMapperIterator.value().isEmpty() && 0119 QRegularExpression(csvMapperIterator.value(), QRegularExpression::CaseInsensitiveOption).match(att).hasMatch() && 0120 (!output.contains(key) || key == QStringLiteral("comment") || key == QStringLiteral("category") || key == QStringLiteral("amount"))) { 0121 output.push_back(key); 0122 found = true; 0123 } 0124 } 0125 } 0126 0127 // Search if this csv column must be added as a property 0128 if (!found && 0129 !m_importParameters.value(QStringLiteral("mapping_property")).isEmpty() && 0130 QRegularExpression(m_importParameters.value(QStringLiteral("mapping_property")), QRegularExpression::CaseInsensitiveOption).match(att).hasMatch() && 0131 !output.contains(att)) { 0132 output.push_back(att); 0133 found = true; 0134 } 0135 0136 if (!found) { 0137 output.push_back(QString()); // To ignore this column 0138 } 0139 } 0140 return output; 0141 } 0142 0143 SKGError SKGImportPluginCsv::setCSVMapping(const QStringList* iCSVMapping) 0144 { 0145 SKGError err; 0146 SKGTRACEINFUNCRC(10, err) 0147 0148 m_csvMapping.clear(); 0149 0150 if (iCSVMapping == nullptr) { 0151 // Automatic build 0152 // Open file 0153 QFile file(m_importer->getLocalFileName()); 0154 if (Q_UNLIKELY(!file.open(QIODevice::ReadOnly))) { 0155 err.setReturnCode(ERR_INVALIDARG).setMessage(i18nc("Error message", "Open file '%1' failed", m_importer->getFileName().toDisplayString())); 0156 } else { 0157 QTextStream stream(&file); 0158 if (!m_importer->getCodec().isEmpty()) { 0159 stream.setCodec(m_importer->getCodec().toLatin1().constData()); 0160 } 0161 0162 // Ignore useless lines 0163 int headerIndex = getCSVHeaderIndex(); 0164 for (int i = 1; i < headerIndex; ++i) { 0165 stream.readLine(); 0166 } 0167 0168 // Get mapping 0169 if (!stream.atEnd()) { 0170 m_csvMapping = getCSVMappingFromLine(stream.readLine()); 0171 } else { 0172 err.setReturnCode(ERR_INVALIDARG); 0173 } 0174 0175 // close file 0176 file.close(); 0177 } 0178 } else { 0179 // Manual build 0180 m_csvMapping = *iCSVMapping; 0181 } 0182 0183 IFOK(err) { 0184 // Check if mandatory attributes have been found 0185 if (m_importParameters.value(QStringLiteral("mode_csv_rule")) == QStringLiteral("Y")) { 0186 if (!m_csvMapping.contains(QStringLiteral("payee")) || !m_csvMapping.contains(QStringLiteral("category"))) { 0187 err = SKGError(ERR_FAIL, i18nc("Error message", "Columns payee and category not found. Set import parameters in settings (Configure Skrooge… / Import/Export / CSV / Edit regular expressions…).")); 0188 } 0189 } else { 0190 if (!m_csvMapping.contains(QStringLiteral("date")) || !m_csvMapping.contains(QStringLiteral("amount"))) { 0191 err = SKGError(ERR_FAIL, i18nc("Error message", "Columns date and amount not found. Set import parameters in settings (Configure Skrooge… / Import/Export / CSV / Edit regular expressions…).")); 0192 } 0193 } 0194 } 0195 0196 return err; 0197 } 0198 0199 SKGError SKGImportPluginCsv::setCSVHeaderIndex(int iIndex) 0200 { 0201 SKGError err; 0202 SKGTRACEINFUNCRC(10, err) 0203 0204 if (iIndex == -1) { 0205 // Automatic build 0206 // Open file 0207 QFile file(m_importer->getLocalFileName()); 0208 if (Q_UNLIKELY(!file.open(QIODevice::ReadOnly))) { 0209 err.setReturnCode(ERR_INVALIDARG).setMessage(i18nc("Error message", "Open file '%1' failed", m_importer->getFileName().toDisplayString())); 0210 } else { 0211 QTextStream stream(&file); 0212 if (!m_importer->getCodec().isEmpty()) { 0213 stream.setCodec(m_importer->getCodec().toLatin1().constData()); 0214 } 0215 0216 int i = 1; 0217 m_csvHeaderIndex = -1; 0218 while (!stream.atEnd() && m_csvHeaderIndex == -1) { 0219 // Read line 0220 QStringList map = getCSVMappingFromLine(stream.readLine()); 0221 if (m_importParameters.value(QStringLiteral("mode_csv_rule")) == QStringLiteral("Y")) { 0222 if (map.contains(QStringLiteral("payee")) && map.contains(QStringLiteral("category"))) { 0223 m_csvHeaderIndex = i; 0224 } 0225 } else { 0226 if (map.contains(QStringLiteral("date")) && map.contains(QStringLiteral("amount"))) { 0227 m_csvHeaderIndex = i; 0228 } 0229 } 0230 0231 ++i; 0232 } 0233 0234 // close file 0235 file.close(); 0236 } 0237 } else { 0238 // Manual build 0239 m_csvHeaderIndex = iIndex; 0240 } 0241 0242 return err; 0243 } 0244 0245 int SKGImportPluginCsv::getCSVHeaderIndex() 0246 { 0247 SKGTRACEINFUNC(10) 0248 if (m_csvHeaderIndex == -1) { 0249 setCSVHeaderIndex(-1); 0250 } 0251 return m_csvHeaderIndex; 0252 } 0253 0254 QChar SKGImportPluginCsv::getCSVSeparator(const QString& iLine) 0255 { 0256 SKGTRACEINFUNC(10) 0257 if (m_csvSeparator.isNull()) { 0258 QStringList csvAttributes = SKGServices::splitCSVLine(iLine, ';', true, &m_csvSeparator); 0259 int nb = csvAttributes.count(); 0260 0261 // If the split fails, we try with another separator 0262 if (nb == 1) { 0263 csvAttributes = SKGServices::splitCSVLine(iLine, ',', true, &m_csvSeparator); 0264 nb = csvAttributes.count(); 0265 0266 // If the split fails, we try with another separator 0267 if (nb == 1) { 0268 csvAttributes = SKGServices::splitCSVLine(iLine, '\t', true, &m_csvSeparator); 0269 } 0270 } 0271 } 0272 return m_csvSeparator; 0273 } 0274 0275 SKGError SKGImportPluginCsv::importFile() 0276 { 0277 if (m_importParameters.value(QStringLiteral("mode_csv_rule")) == QStringLiteral("Y")) { 0278 return importCSVRule(); 0279 } 0280 if (m_importParameters.value(QStringLiteral("mode_csv_unit")) == QStringLiteral("Y")) { 0281 return importCSVUnit(); 0282 } 0283 if (m_importer->getDocument() == nullptr) { 0284 return SKGError(ERR_ABORT, i18nc("Error message", "Invalid parameters")); 0285 } 0286 0287 SKGError err; 0288 SKGTRACEINFUNCRC(2, err) 0289 // Begin transaction 0290 err = m_importer->getDocument()->beginTransaction("#INTERNAL#" % i18nc("Import step", "Import %1 file", "CSV"), 3); 0291 IFOK(err) { 0292 // Initialize some variables 0293 QDateTime now = QDateTime::currentDateTime(); 0294 QString postFix = SKGServices::dateToSqlString(now); 0295 0296 // Default mapping 0297 if (m_csvMapping.isEmpty()) { 0298 err = setCSVMapping(nullptr); 0299 IFOKDO(err, m_importer->getDocument()->sendMessage(i18nc("An information message", "Use automatic search of the columns"))) 0300 } 0301 IFOKDO(err, m_importer->getDocument()->sendMessage(i18nc("An information message", "Mapping used: %1", m_csvMapping.join(QStringLiteral("|"))))) 0302 0303 // Step 1 done 0304 IFOKDO(err, m_importer->getDocument()->stepForward(1)) 0305 0306 // Open file 0307 IFOK(err) { 0308 QFile file(m_importer->getLocalFileName()); 0309 if (!file.open(QIODevice::ReadOnly | QIODevice::Text)) { 0310 err.setReturnCode(ERR_INVALIDARG).setMessage(i18nc("Error message", "Open file '%1' failed", m_importer->getFileName().toDisplayString())); 0311 } else { 0312 QTextStream stream(&file); 0313 if (!m_importer->getCodec().isEmpty()) { 0314 stream.setCodec(m_importer->getCodec().toLatin1().constData()); 0315 } 0316 0317 // Ignore useless lines 0318 int headerIndex = getCSVHeaderIndex(); 0319 if (headerIndex == -1) { 0320 err.setReturnCode(ERR_FAIL).setMessage(i18nc("Error message", "Header not found in CSV file")); 0321 } 0322 0323 for (int i = 1; i <= headerIndex; ++i) { 0324 stream.readLine(); 0325 } 0326 0327 QList<int> amountIndexes; 0328 int nb = m_csvMapping.count(); 0329 for (int i = 0; i < nb; ++i) { 0330 if (m_csvMapping.at(i) == QStringLiteral("amount")) { 0331 amountIndexes.push_back(i); 0332 } 0333 } 0334 int nbAmount = amountIndexes.count(); 0335 0336 // Get data column 0337 QStringList dates; 0338 QStringList lines; 0339 int posdate = -1; 0340 bool modeAutoCreditDebit = (!m_csvMapping.contains(QStringLiteral("sign")) && nbAmount == 2); 0341 IFOK(err) { 0342 posdate = m_csvMapping.indexOf(QStringLiteral("date")); 0343 if (posdate == -1) { 0344 posdate = m_csvMapping.indexOf(QStringLiteral("date1")); 0345 } 0346 if (posdate == -1) { 0347 posdate = m_csvMapping.indexOf(QStringLiteral("date2")); 0348 } 0349 if (posdate != -1) { 0350 QString currentLine; 0351 while (!stream.atEnd()) { 0352 // Read line 0353 QString line = stream.readLine().trimmed(); 0354 if (!line.isEmpty()) { 0355 if (line.startsWith(QLatin1String("\""))) { 0356 currentLine.clear(); 0357 } 0358 currentLine += line; 0359 0360 // Get date 0361 QStringList field = SKGServices::splitCSVLine(currentLine, getCSVSeparator(currentLine)); 0362 if (field.isEmpty()) { 0363 // This is a multi line csv filename 0364 } else { 0365 lines.push_back(currentLine); 0366 currentLine.clear(); 0367 0368 if (field.count() >= m_csvMapping.count()) { 0369 if (posdate < field.count()) { 0370 dates.push_back(field.value(posdate).remove(QStringLiteral(" 00:00:00")).trimmed()); 0371 } 0372 0373 // Check if all amounts are positive 0374 if (modeAutoCreditDebit) { 0375 for (int i = 0; modeAutoCreditDebit && i < nbAmount; ++i) { 0376 QString s = field.at(amountIndexes.at(i)).trimmed(); 0377 if (!s.isEmpty() && SKGServices::stringToDouble(s) < 0) { 0378 modeAutoCreditDebit = false; 0379 } 0380 } 0381 } 0382 } 0383 } 0384 } 0385 } 0386 } 0387 } 0388 0389 // close file 0390 file.close(); 0391 0392 // Select dateformat 0393 QString dateFormat = m_importParameters.value(QStringLiteral("date_format")); 0394 if (dateFormat.isEmpty()) { 0395 dateFormat = SKGServices::getDateFormat(dates); // Automatic detection 0396 } 0397 if (!err) { 0398 int posdate2 = m_csvMapping.indexOf(QStringLiteral("date2")); 0399 if (dateFormat.isEmpty()) { 0400 // Check if another "date" attribute exists 0401 if (posdate2 != -1) { 0402 m_csvMapping[posdate] = QLatin1String(""); 0403 0404 err = importFile(); 0405 0406 SKGENDTRANSACTION(m_importer->getDocument(), err) 0407 return err; 0408 } 0409 err.setReturnCode(ERR_FAIL).setMessage(i18nc("Error message", "Date format not supported")); 0410 } else { 0411 // Disable other date columns 0412 if (posdate2 != -1 && posdate2 != posdate) { 0413 m_csvMapping[posdate2] = QLatin1String(""); 0414 } 0415 } 0416 } 0417 IFOKDO(err, m_importer->getDocument()->sendMessage(i18nc("An information message", "Import of '%1' with code '%2' and date format '%3'", m_importer->getFileName().toDisplayString(), m_importer->getCodec(), dateFormat))) 0418 0419 // Step 2 done 0420 IFOKDO(err, m_importer->getDocument()->stepForward(2)) 0421 0422 // Treat all lines 0423 IFOK(err) { 0424 int nb2 = lines.size(); 0425 err = m_importer->getDocument()->beginTransaction("#INTERNAL#" % i18nc("Import step", "Import transactions"), nb2); 0426 0427 // Save last mapping used in a settings 0428 QString mappingDesc; 0429 int nbMap = m_csvMapping.count(); 0430 for (int i = 0; i < nbMap; ++i) { 0431 if (i != 0) { 0432 mappingDesc += '|'; 0433 } 0434 mappingDesc += m_csvMapping.at(i); 0435 } 0436 IFOKDO(err, m_importer->getDocument()->setParameter(QStringLiteral("SKG_LAST_CSV_MAPPING_USED"), mappingDesc)) 0437 0438 SKGUnitObject defUnit; 0439 SKGAccountObject defAccount; 0440 QMap<int, SKGOperationObject> mapGroup; 0441 QMap<int, SKGOperationObject> mapOperation; 0442 bool noUnitColumn = (m_csvMapping.indexOf(QStringLiteral("unit")) == -1); 0443 bool noAccountColumn = (m_csvMapping.indexOf(QStringLiteral("account")) == -1); 0444 bool emptyAccount = false; 0445 0446 for (int i = 0; !err && i < nb2; ++i) { 0447 QString currentCategory; 0448 SKGOperationObject currentOperation(m_importer->getDocument()); 0449 SKGSubOperationObject currentSubOperation(m_importer->getDocument()); 0450 0451 // Valuate mandatory attribute with default value 0452 if (noUnitColumn && i == 0) { 0453 err = m_importer->getDefaultUnit(defUnit); 0454 } 0455 IFOKDO(err, currentOperation.setUnit(defUnit)) 0456 0457 if (!err && noAccountColumn) { 0458 err = m_importer->getDefaultAccount(defAccount); 0459 if (i == 0) { 0460 emptyAccount = (defAccount.getNbOperation() == 0); 0461 } 0462 IFOKDO(err, currentOperation.setParentAccount(defAccount)) 0463 } 0464 0465 const QString& line = lines.at(i); 0466 QString skg_op_original_amount; 0467 QStringList atts = SKGServices::splitCSVLine(line, getCSVSeparator(line)); 0468 int nbcol = m_csvMapping.count(); 0469 if (atts.count() < nbcol) { 0470 if (i == nb2 - 1) { 0471 // This is a footer (see 408284) 0472 break; 0473 } 0474 err = SKGError(ERR_INVALIDARG, i18nc("Error message", "Invalid number of columns in line %1. Expected %2. Found %3.", 0475 headerIndex + i + 1, nbcol, atts.count())); 0476 } 0477 QString linecleaned; 0478 for (int z = 0; !err && z < nbcol; ++z) { 0479 if (!m_csvMapping[z].isEmpty()) { 0480 if (!linecleaned.isEmpty()) { 0481 linecleaned += getCSVSeparator(); 0482 } 0483 linecleaned += atts[z]; 0484 } 0485 } 0486 0487 QByteArray hash = QCryptographicHash::hash(linecleaned.toUtf8(), QCryptographicHash::Md5); 0488 0489 int initialBalance = 0; 0490 int idgroup = 0; 0491 int idtransaction = 0; 0492 QStringList propertiesAtt; 0493 QStringList propertiesVal; 0494 double sign = (modeAutoCreditDebit ? -1.0 : 1.0); 0495 bool amountSet = false; 0496 for (int c = 0; !err && c < nbcol; ++c) { 0497 QString col = m_csvMapping[c]; 0498 if (!col.isEmpty()) { 0499 QString val; 0500 if (c >= 0 && c < atts.count()) { 0501 val = atts.at(c).trimmed(); 0502 } 0503 if (col == QStringLiteral("date") || col == QStringLiteral("date1") || col == QStringLiteral("date2")) { 0504 QDate d = SKGServices::stringToTime(SKGServices::dateToSqlString(val.remove(QStringLiteral(" 00:00:00")), dateFormat)).date(); 0505 err = currentOperation.setDate(d); 0506 IFOKDO(err, currentSubOperation.setDate(d)) 0507 if (val == QStringLiteral("0000-00-00")) { 0508 initialBalance = 1; 0509 } 0510 } else if (col == QStringLiteral("number")) { 0511 if (!val.isEmpty()) { 0512 err = currentOperation.setNumber(val); 0513 } 0514 } else if (col == QStringLiteral("mode")) { 0515 if (val == QStringLiteral("1")) { 0516 val = i18nc("An transaction mode", "Transfer"); 0517 } else if (val == QStringLiteral("2")) { 0518 val = i18nc("An transaction mode", "Direct debit"); 0519 } else if (val == QStringLiteral("3")) { 0520 val = i18nc("An transaction mode", "Check"); 0521 } else if (val == QStringLiteral("4")) { 0522 val = i18nc("An transaction mode", "Deposit"); 0523 } else if (val == QStringLiteral("5")) { 0524 val = i18nc("An transaction mode", "Payback"); 0525 } else if (val == QStringLiteral("6")) { 0526 val = i18nc("An transaction mode", "Withdrawal"); 0527 } else if (val == QStringLiteral("7")) { 0528 val = i18nc("An transaction mode", "Card"); 0529 } else if (val == QStringLiteral("8")) { 0530 val = i18nc("An transaction mode", "Loan payment"); 0531 } else if (val == QStringLiteral("9")) { 0532 val = i18nc("An transaction mode", "Subscription"); 0533 } else if (val == QStringLiteral("0")) { 0534 val = QString(); 0535 } else if (val == QStringLiteral("10")) { 0536 val = i18nc("An transaction mode", "Cash deposit"); 0537 } else if (val == QStringLiteral("11")) { 0538 val = i18nc("An transaction mode", "Card summary"); 0539 } else if (val == QStringLiteral("12")) { 0540 val = i18nc("An transaction mode", "Deferred card"); 0541 } 0542 err = currentOperation.setMode(val); 0543 } else if (col == QStringLiteral("payee")) { 0544 SKGPayeeObject payeeObj; 0545 err = SKGPayeeObject::createPayee(m_importer->getDocument(), val, payeeObj); 0546 IFOKDO(err, currentOperation.setPayee(payeeObj)) 0547 } else if (col == QStringLiteral("comment")) { 0548 QString comment = currentOperation.getComment(); 0549 if (!comment.isEmpty()) { 0550 comment += ' '; 0551 } 0552 comment += val; 0553 err = currentOperation.setComment(comment); 0554 IFOKDO(err, currentSubOperation.setComment(comment)) 0555 } else if (col == QStringLiteral("status")) { 0556 err = currentOperation.setStatus(val == QStringLiteral("C") || val == QStringLiteral("Y") ? SKGOperationObject::CHECKED : val == QStringLiteral("P") ? SKGOperationObject::MARKED : SKGOperationObject::NONE); 0557 } else if (col == QStringLiteral("bookmarked")) { 0558 err = currentOperation.bookmark(val == QStringLiteral("Y")); 0559 } else if (col == QStringLiteral("idgroup")) { 0560 idgroup = SKGServices::stringToInt(val); 0561 } else if (col == QStringLiteral("idtransaction")) { 0562 idtransaction = SKGServices::stringToInt(val); 0563 } else if (col == QStringLiteral("amount")) { 0564 if (!val.isEmpty() && SKGServices::stringToDouble(val) != 0.0 && !amountSet) { 0565 amountSet = true; 0566 if (m_csvMapping.contains(QStringLiteral("quantity"))) { 0567 // 209705 vvvv 0568 skg_op_original_amount = val; 0569 // 209705 ^^^^ 0570 } else { 0571 err = currentSubOperation.setQuantity(sign * SKGServices::stringToDouble(val)); 0572 } 0573 } else if (modeAutoCreditDebit) { 0574 sign = 1.0; // Next one will be considered as a credit 0575 } 0576 } else if (col == QStringLiteral("quantity")) { 0577 err = currentSubOperation.setQuantity(SKGServices::stringToDouble(val)); 0578 } else if (col == QStringLiteral("sign")) { 0579 if (QRegularExpression(m_importParameters.value(QStringLiteral("mapping_debit")), QRegularExpression::CaseInsensitiveOption).match(val).hasMatch()) { 0580 sign = -1; 0581 double cval = currentSubOperation.getQuantity(); 0582 if (cval > 0) { 0583 err = currentSubOperation.setQuantity(-cval); 0584 } 0585 } 0586 } else if (col == QStringLiteral("unit")) { 0587 // Looking for unit 0588 SKGUnitObject unit(m_importer->getDocument()); 0589 if (val != defUnit.getName()) { // For performance 0590 err = unit.setName(val); 0591 IFOKDO(err, unit.setSymbol(val)) 0592 if (!err && unit.load().isFailed()) { 0593 err = unit.save(false); // Save only 0594 } 0595 0596 // This unit is now the default one, it is better for performance 0597 defUnit = unit; 0598 } else { 0599 unit = defUnit; 0600 } 0601 0602 SKGUnitValueObject unitval; 0603 IFOKDO(err, unit.addUnitValue(unitval)) 0604 IFOK(err) { 0605 int posAmount = m_csvMapping.indexOf(QStringLiteral("amount")); 0606 int posQuantity = m_csvMapping.indexOf(QStringLiteral("quantity")); 0607 if (posAmount != -1 && posQuantity != -1) { 0608 err = unitval.setQuantity(SKGServices::stringToDouble(atts.at(posAmount)) / SKGServices::stringToDouble(atts.at(posQuantity))); 0609 } else { 0610 err = unitval.setQuantity(1); 0611 } 0612 } 0613 IFOKDO(err, unitval.setDate(now.date())) 0614 IFOKDO(err, unitval.save()) 0615 IFOKDO(err, currentOperation.setUnit(unit)) 0616 } else if (col == QStringLiteral("account")) { 0617 // Looking for account 0618 if (val.isEmpty()) { 0619 err = m_importer->getDefaultAccount(defAccount); 0620 if (i == 0) { 0621 emptyAccount = (defAccount.getNbOperation() == 0); 0622 } 0623 } else if (val != defAccount.getName()) { // For performance 0624 SKGAccountObject account(m_importer->getDocument()); 0625 account.setName(val); 0626 err = account.load(); 0627 IFKO(err) { 0628 // Not found, we have to create one 0629 SKGBankObject bank(m_importer->getDocument()); 0630 QString name = i18nc("Noun", "Bank for import %1", postFix); 0631 err = bank.setName(name); 0632 if (!err && bank.load().isFailed()) { 0633 err = bank.save(false); // Save only 0634 IFOKDO(err, m_importer->getDocument()->sendMessage(i18nc("An information message", "Default bank '%1' created for import", name))) 0635 } 0636 IFOKDO(err, bank.addAccount(account)) 0637 IFOKDO(err, account.setName(val)) 0638 if (!err && account.load().isFailed()) { 0639 err = account.save(false); // Save only 0640 } 0641 } 0642 0643 // This account is now the default one, it is better for performance 0644 defAccount = account; 0645 } 0646 IFOKDO(err, currentOperation.setParentAccount(defAccount)) 0647 } else if (col == QStringLiteral("category")) { 0648 // Set Category 0649 if (!val.isEmpty()) { 0650 // Prepare val 0651 val.replace('/', OBJECTSEPARATOR); 0652 val.replace(':', OBJECTSEPARATOR); 0653 val.replace(',', OBJECTSEPARATOR); 0654 val.replace(';', OBJECTSEPARATOR); 0655 // Get previous category 0656 if (!currentCategory.isEmpty()) { 0657 val = currentCategory % OBJECTSEPARATOR % val; 0658 } 0659 currentCategory = val; 0660 0661 // Create and set category 0662 SKGCategoryObject Category; 0663 err = SKGCategoryObject::createPathCategory(m_importer->getDocument(), val, Category); 0664 IFOKDO(err, currentSubOperation.setCategory(Category)) 0665 0666 IFOK(err) { 0667 if (m_csvMapping.indexOf(QStringLiteral("payee")) == -1) { 0668 SKGPayeeObject payeeObj; 0669 QString p = m_importer->getDocument()->getCategoryForPayee(val, false); 0670 err = SKGPayeeObject::createPayee(m_importer->getDocument(), p, payeeObj); 0671 IFOKDO(err, currentOperation.setPayee(payeeObj)) 0672 } 0673 } 0674 } 0675 } else { 0676 // A property 0677 propertiesAtt.push_back(col); 0678 propertiesVal.push_back(val); 0679 } 0680 } 0681 } 0682 0683 if (!err && (initialBalance != 0)) { 0684 // Specific values for initial balance 0685 err = currentOperation.setStatus(SKGOperationObject::CHECKED); 0686 IFOKDO(err, currentOperation.setAttribute(QStringLiteral("d_date"), QStringLiteral("0000-00-00"))) 0687 IFOKDO(err, currentSubOperation.setAttribute(QStringLiteral("d_date"), QStringLiteral("0000-00-00"))) 0688 } 0689 IFOKDO(err, currentOperation.setImportID(hash.toHex())) 0690 IFOK(err) { 0691 if (idtransaction != 0) { 0692 if (mapOperation.contains(idtransaction)) { 0693 currentOperation = mapOperation[idtransaction]; 0694 skg_op_original_amount = QString(); 0695 } else { 0696 err = currentOperation.save(); 0697 mapOperation[idtransaction] = currentOperation; 0698 } 0699 } else { 0700 err = currentOperation.save(false); // Save only 0701 } 0702 } 0703 if (!err && idgroup != 0) { 0704 if (mapGroup.contains(idgroup) && currentOperation != mapGroup[idgroup]) { 0705 err = currentOperation.setGroupOperation(mapGroup[idgroup]); 0706 IFOKDO(err, currentOperation.save()) 0707 } 0708 mapGroup[idgroup] = currentOperation; 0709 } 0710 0711 IFOKDO(err, currentSubOperation.setParentOperation(currentOperation)) 0712 IFOKDO(err, currentSubOperation.save(false, false)) 0713 0714 // 209705 vvvv 0715 if (!err && !skg_op_original_amount.isEmpty()) { 0716 err = currentOperation.setProperty(QStringLiteral("SKG_OP_ORIGINAL_AMOUNT"), skg_op_original_amount); 0717 } 0718 // 209705 ^^^^ 0719 0720 // Add properties 0721 int nbp = propertiesAtt.count(); 0722 for (int p = 0; !err && p < nbp; ++p) { 0723 err = currentOperation.setProperty(propertiesAtt.at(p), propertiesVal.at(p)); 0724 } 0725 0726 if (!err && i % 500 == 0) { 0727 err = m_importer->getDocument()->executeSqliteOrder(QStringLiteral("ANALYZE")); 0728 } 0729 IFOKDO(err, m_importer->getDocument()->stepForward(i + 1)) 0730 } 0731 0732 0733 IFOK(err) { 0734 QString balance = m_importParameters.value(QStringLiteral("balance")); 0735 if (!balance.isEmpty()) { 0736 if (emptyAccount) { 0737 // Current amount 0738 double currentAmount = defAccount.getAmount(QDate::currentDate()); 0739 0740 // Update account 0741 IFOKDO(err, defAccount.setInitialBalance(SKGServices::stringToDouble(balance) - currentAmount, defUnit)) 0742 IFOKDO(err, defAccount.save()) 0743 IFOKDO(err, m_importer->getDocument()->sendMessage(i18nc("An information message", "The initial balance of '%1' has been set", defAccount.getName()), SKGDocument::Warning)) 0744 0745 } else { 0746 m_importer->getDocument()->sendMessage(i18nc("An information message", "The initial balance of '%1' has not been set because some transactions are already existing", defAccount.getName())); 0747 } 0748 } 0749 } 0750 0751 SKGENDTRANSACTION(m_importer->getDocument(), err) 0752 0753 // Lines treated 0754 IFOKDO(err, m_importer->getDocument()->stepForward(3)) 0755 } 0756 } 0757 } 0758 } 0759 SKGENDTRANSACTION(m_importer->getDocument(), err) 0760 0761 return err; 0762 } 0763 0764 0765 bool SKGImportPluginCsv::isExportPossible() 0766 { 0767 SKGTRACEINFUNC(10) 0768 return (m_importer->getDocument() == nullptr ? true : m_importer->getFileNameExtension() == QStringLiteral("CSV")); 0769 } 0770 0771 SKGError SKGImportPluginCsv::exportFile() 0772 { 0773 if (m_importer->getDocument() == nullptr) { 0774 return SKGError(ERR_ABORT, i18nc("Error message", "Invalid parameters")); 0775 } 0776 SKGError err; 0777 SKGTRACEINFUNCRC(2, err) 0778 0779 auto listUUIDs = SKGServices::splitCSVLine(m_exportParameters.value(QStringLiteral("uuid_of_selected_accounts_or_operations"))); 0780 0781 QString wc; 0782 for (const auto& uuid : qAsConst(listUUIDs)) { 0783 auto items = SKGServices::splitCSVLine(uuid, '-'); 0784 if (items.at(1) == QStringLiteral("operation")) { 0785 if (!wc.isEmpty()) { 0786 wc += QLatin1String(" OR "); 0787 } 0788 wc += " i_OPID=" + items.at(0); 0789 } else if (items.at(1) == QStringLiteral("account")) { 0790 if (!wc.isEmpty()) { 0791 wc += QLatin1String(" OR "); 0792 } 0793 wc += " rd_account_id=" + items.at(0); 0794 } 0795 } 0796 if (wc.isEmpty()) { 0797 wc = QStringLiteral("1=1"); 0798 } else { 0799 IFOKDO(err, m_importer->getDocument()->sendMessage(i18nc("An information message", "Only selected accounts and transactions have been exported"))) 0800 } 0801 0802 // Open file 0803 QSaveFile file(m_importer->getLocalFileName(false)); 0804 if (!file.open(QIODevice::WriteOnly)) { 0805 err.setReturnCode(ERR_INVALIDARG).setMessage(i18nc("Error message", "Save file '%1' failed", m_importer->getFileName().toDisplayString())); 0806 } else { 0807 QTextStream out(&file); 0808 if (!m_importer->getCodec().isEmpty()) { 0809 out.setCodec(m_importer->getCodec().toLatin1().constData()); 0810 } 0811 err = m_importer->getDocument()->dumpSelectSqliteOrder( 0812 QStringLiteral("SELECT v.d_date as date, v.t_BANK as bank, v.t_ACCOUNT as account, v.t_number as number, v.t_mode as mode, " 0813 "v.t_PAYEE as payee, v.t_REALCOMMENT as comment, PRINTF('%.'||u.i_nbdecimal||'f', v.f_REALQUANTITY) as quantity, " 0814 "v.t_UNIT as unit, PRINTF('%.'||u.i_nbdecimal||'f', v.f_REALCURRENTAMOUNT) as amount, v.t_TYPEEXPENSE as sign, v.t_REALCATEGORY as category, v.t_status as status, " 0815 "v.t_REALREFUND as tracker, v.t_bookmarked as bookmarked, v.i_SUBOPID id, v.i_OPID idtransaction, v.i_group_id idgroup " 0816 "FROM v_suboperation_consolidated as v, unit as u WHERE v.rc_unit_id=u.id AND (") % wc % ") ORDER BY v.d_date, v.i_OPID, v.i_SUBOPID", &out, SKGServices::DUMP_CSV); 0817 0818 // Close file 0819 file.commit(); 0820 } 0821 0822 return err; 0823 } 0824 0825 QString SKGImportPluginCsv::getMimeTypeFilter() const 0826 { 0827 return "*.csv|" % i18nc("A file format", "CSV file"); 0828 } 0829 0830 SKGError SKGImportPluginCsv::importCSVUnit() 0831 { 0832 SKGError err; 0833 SKGTRACEINFUNCRC(2, err) 0834 0835 if (m_importer->getDocument() != nullptr) { 0836 // Begin transaction 0837 err = m_importer->getDocument()->beginTransaction("#INTERNAL#" % i18nc("Import step", "Import units"), 3); 0838 IFOK(err) { 0839 // File name is the name of the unit 0840 QFileInfo fInfo(m_importer->getFileName().path()); 0841 QString unitName = fInfo.baseName(); 0842 0843 // Default mapping 0844 if (m_csvMapping.isEmpty()) { 0845 err = setCSVMapping(nullptr); 0846 IFOKDO(err, m_importer->getDocument()->sendMessage(i18nc("An information message", "Use automatic search of the columns"))) 0847 } 0848 0849 // Step 1 done 0850 IFOKDO(err, m_importer->getDocument()->stepForward(1)) 0851 0852 // Open file 0853 IFOK(err) { 0854 QFile file(m_importer->getLocalFileName()); 0855 if (!file.open(QIODevice::ReadOnly | QIODevice::Text)) { 0856 err.setReturnCode(ERR_INVALIDARG).setMessage(i18nc("Error message", "Open file '%1' failed", m_importer->getFileName().toDisplayString())); 0857 } else { 0858 QTextStream stream(&file); 0859 if (!m_importer->getCodec().isEmpty()) { 0860 stream.setCodec(m_importer->getCodec().toLatin1().constData()); 0861 } 0862 0863 // Ignore useless lines 0864 int headerIndex = getCSVHeaderIndex(); 0865 for (int i = 1; i <= headerIndex; ++i) { 0866 stream.readLine(); 0867 } 0868 0869 // Get data column 0870 QStringList dates; 0871 QStringList lines; 0872 int posdate = m_csvMapping.indexOf(QStringLiteral("date")); 0873 if (posdate != -1) { 0874 while (!stream.atEnd()) { 0875 // Read line 0876 QString line = stream.readLine().trimmed(); 0877 if (!line.isEmpty()) { 0878 lines.push_back(line); 0879 0880 // Get date 0881 QStringList field = SKGServices::splitCSVLine(line, getCSVSeparator(line)); 0882 if (posdate < field.count()) { 0883 dates.push_back(field.at(posdate)); 0884 } 0885 } 0886 } 0887 } 0888 0889 // close file 0890 file.close(); 0891 0892 // Select dateformat 0893 QString dateFormat = SKGServices::getDateFormat(dates); 0894 if (dateFormat.isEmpty()) { 0895 err.setReturnCode(ERR_FAIL).setMessage(i18nc("Error message", "Date format not supported")); 0896 } 0897 IFOKDO(err, m_importer->getDocument()->sendMessage(i18nc("An information message", "Import of '%1' with codec '%2' and date format '%3'", m_importer->getFileName().toDisplayString(), m_importer->getCodec(), dateFormat))) 0898 0899 // Step 2 done 0900 IFOKDO(err, m_importer->getDocument()->stepForward(2)) 0901 0902 // Treat all lines 0903 IFOK(err) { 0904 int nb = lines.size(); 0905 err = m_importer->getDocument()->beginTransaction("#INTERNAL#" % i18nc("Import step", "Import units"), nb); 0906 0907 // Save last mapping used in a settings 0908 QString mappingDesc; 0909 int nbMap = m_csvMapping.count(); 0910 for (int i = 0; i < nbMap; ++i) { 0911 if (i != 0) { 0912 mappingDesc += '|'; 0913 } 0914 mappingDesc += m_csvMapping.at(i); 0915 } 0916 IFOKDO(err, m_importer->getDocument()->setParameter(QStringLiteral("SKG_LAST_CSV_UNIT_MAPPING_USED"), mappingDesc)) 0917 0918 int posdate2 = m_csvMapping.indexOf(QStringLiteral("date")); 0919 int posvalue = m_csvMapping.indexOf(QStringLiteral("amount")); 0920 if (posdate2 != -1 && posvalue != -1) { 0921 for (int i = 0; !err && i < nb; ++i) { 0922 QStringList atts = SKGServices::splitCSVLine(lines.at(i), getCSVSeparator(lines.at(i))); 0923 err = m_importer->getDocument()->addOrModifyUnitValue(unitName, 0924 SKGServices::stringToTime(SKGServices::dateToSqlString(atts.at(posdate2), dateFormat)).date(), 0925 SKGServices::stringToDouble(atts.at(posvalue))); 0926 0927 IFOKDO(err, m_importer->getDocument()->stepForward(i + 1)) 0928 } 0929 } 0930 0931 SKGENDTRANSACTION(m_importer->getDocument(), err) 0932 0933 // Lines treated 0934 IFOKDO(err, m_importer->getDocument()->stepForward(3)) 0935 } 0936 } 0937 } 0938 } 0939 SKGENDTRANSACTION(m_importer->getDocument(), err) 0940 } 0941 return err; 0942 } 0943 0944 SKGError SKGImportPluginCsv::importCSVRule() 0945 { 0946 SKGError err; 0947 SKGTRACEINFUNCRC(2, err) 0948 0949 if (m_importer->getDocument() != nullptr) { 0950 // Begin transaction 0951 err = m_importer->getDocument()->beginTransaction("#INTERNAL#" % i18nc("Import step", "Import rules"), 3); 0952 IFOK(err) { 0953 // Default mapping 0954 if (m_csvMapping.isEmpty()) { 0955 err = setCSVMapping(nullptr); 0956 IFOKDO(err, m_importer->getDocument()->sendMessage(i18nc("An information message", "Use automatic search of the columns"))) 0957 } 0958 0959 // Step 1 done 0960 IFOKDO(err, m_importer->getDocument()->stepForward(1)) 0961 0962 // Open file 0963 IFOK(err) { 0964 QFile file(m_importer->getLocalFileName()); 0965 if (!file.open(QIODevice::ReadOnly | QIODevice::Text)) { 0966 err.setReturnCode(ERR_INVALIDARG).setMessage(i18nc("Error message", "Open file '%1' failed", m_importer->getFileName().toDisplayString())); 0967 } else { 0968 QTextStream stream(&file); 0969 if (!m_importer->getCodec().isEmpty()) { 0970 stream.setCodec(m_importer->getCodec().toLatin1().constData()); 0971 } 0972 0973 // Ignore useless lines 0974 int headerIndex = getCSVHeaderIndex(); 0975 for (int i = 1; i <= headerIndex; ++i) { 0976 stream.readLine(); 0977 } 0978 0979 // Get data column 0980 QStringList lines; 0981 while (!stream.atEnd()) { 0982 // Read line 0983 QString line = stream.readLine().trimmed(); 0984 if (!line.isEmpty()) { 0985 lines.push_back(line); 0986 } 0987 } 0988 0989 // close file 0990 file.close(); 0991 0992 IFOKDO(err, m_importer->getDocument()->sendMessage(i18nc("An information message", "Import of '%1' with codec '%2'", m_importer->getFileName().toDisplayString(), m_importer->getCodec()))) 0993 0994 // Step 2 done 0995 IFOKDO(err, m_importer->getDocument()->stepForward(2)) 0996 0997 // Treat all lines 0998 IFOK(err) { 0999 int nb = lines.size(); 1000 err = m_importer->getDocument()->beginTransaction("#INTERNAL#" % i18nc("Import step", "Import rules"), nb); 1001 1002 // Save last mapping used in a settings 1003 QString mappingDesc; 1004 int nbMap = m_csvMapping.count(); 1005 for (int i = 0; i < nbMap; ++i) { 1006 if (i != 0) { 1007 mappingDesc += '|'; 1008 } 1009 mappingDesc += m_csvMapping.at(i); 1010 } 1011 IFOKDO(err, m_importer->getDocument()->setParameter(QStringLiteral("SKG_LAST_CSV_RULE_MAPPING_USED"), mappingDesc)) 1012 1013 int pospayee = m_csvMapping.indexOf(QStringLiteral("payee")); 1014 int poscategory = m_csvMapping.indexOf(QStringLiteral("category")); 1015 if (pospayee != -1 && poscategory != -1) { 1016 for (int i = 0; !err && i < nb; ++i) { 1017 QStringList atts = SKGServices::splitCSVLine(lines.at(i), getCSVSeparator(lines.at(i))); 1018 1019 SKGRuleObject oRule; 1020 err = SKGRuleObject::createPayeeCategoryRule(m_importer->getDocument(), atts.at(pospayee), atts.at(poscategory), oRule); 1021 1022 IFOKDO(err, m_importer->getDocument()->stepForward(i + 1)) 1023 } 1024 } 1025 1026 SKGENDTRANSACTION(m_importer->getDocument(), err) 1027 1028 // Lines treated 1029 IFOKDO(err, m_importer->getDocument()->stepForward(3)) 1030 } 1031 } 1032 } 1033 } 1034 SKGENDTRANSACTION(m_importer->getDocument(), err) 1035 } 1036 return err; 1037 } 1038 1039 #include <skgimportplugincsv.moc>