Git commit 640086d9bad0ea20118fa2d9fe63b72f069d9031 by Stephane MANKOWSKI. Committed on 28/11/2020 at 17:09. Pushed by smankowski into branch 'master'.
The ability to create aliases for accounts to assist with import BUG:421302 M +1 -0 CHANGELOG M +1 -0 doc/index.docbook M +1 -1 doc/kde_docbook M +2 -1 plugins/import/skrooge_import_ofx/skgimportpluginofx.cpp M +2 -0 skgbankmodeler/skgimportexportmanager.cpp A +11 -0 tests/input/skgtestimportcsv/XXXX485.csv A +92 -0 tests/input/skgtestimportofx/XXXX485.ofx M +30 -0 tests/skgbankmodelertest/skgtestimportcsv.cpp M +24 -0 tests/skgbankmodelertest/skgtestimportofx.cpp https://invent.kde.org/office/skrooge/commit/640086d9bad0ea20118fa2d9fe63b72f069d9031 diff --git a/CHANGELOG b/CHANGELOG index d232b67bb..651adb481 100644 --- a/CHANGELOG +++ b/CHANGELOG @@ -1,6 +1,7 @@ skrooge (2.24.0) *Correction bug 424728: Skrooge reconciliation should play happy sounds when delta is 0 and when you validate pointed operations *Correction bug 425208: Import of .gnucash failed + *Correction bug 421302: The ability to create aliases for accounts to assist with import *Correction: Ledger-cli export doesn't export negative values in the correct format *Feature: DBUS is now optional (useful for MacOS) *Feature: In monthly reports, the templates are now able to filter on the selected period diff --git a/doc/index.docbook b/doc/index.docbook index fbd3b2a01..f4eb03407 100644 --- a/doc/index.docbook +++ b/doc/index.docbook @@ -726,6 +726,7 @@ <title>Account</title> <para>One thing that might surprise you is that you won't be asked in which account the operations should be imported. That is because &skrooge; will read the account number in the file, and will associate imported operations to that account.</para> <para>If no account exists with this account number, or if the file does not contain this information, &skrooge; will use the file name as the account number.</para> + <para>If you create a property named <quote>alias</quote> on the account, &skrooge; will try to match the value of the property too to find the most appropriated account.</para> <para>If there is still no account with that number, &skrooge; will create a new account having the filename as the account number, and import all operations in that account. You will then be free to either rename the account, perform a <link linkend="mass_update">mass update</link>, or use <link linkend="search_and_process">Search & Process</link> to associate imported operations to an existing account.</para> </sect3> diff --git a/doc/kde_docbook b/doc/kde_docbook index e0ca0f2d5..c63e11d3b 100755 --- a/doc/kde_docbook +++ b/doc/kde_docbook @@ -5,7 +5,7 @@ export XML_CATALOG_FILES="/usr/share/kf5/kdoctools/customization/catalog.xml" # Check if docbook is well formed echo "Running checkXML... "; -checkXML $1; +checkXML5 $1; if [ $? -gt 0 ]; then echo " -> checkXML failed !"; exit 1; diff --git a/plugins/import/skrooge_import_ofx/skgimportpluginofx.cpp b/plugins/import/skrooge_import_ofx/skgimportpluginofx.cpp index b2da44a7d..5451494eb 100644 --- a/plugins/import/skrooge_import_ofx/skgimportpluginofx.cpp +++ b/plugins/import/skrooge_import_ofx/skgimportpluginofx.cpp @@ -176,7 +176,8 @@ SKGError SKGImportPluginOfx::getAccount(OfxAccountData* iAccountData, SKGDocumen oAccount = m_accounts[name]; } else { SKGTRACEL(3) << "NOT found in index" << Qt::endl; - err = iDoc->getObject(QStringLiteral("v_account"), "t_number='" % SKGServices::stringToSqlString(name) % '\'', oAccount); + QString wc = "t_number='" % SKGServices::stringToSqlString(name) % "' OR EXISTS(SELECT 1 FROM parameters WHERE t_uuid_parent=v_account.id||'-account' AND t_name='alias' AND t_value= '" % SKGServices::stringToSqlString(name) % "')"; + err = iDoc->getObject(QStringLiteral("v_account"), wc, oAccount); } } diff --git a/skgbankmodeler/skgimportexportmanager.cpp b/skgbankmodeler/skgimportexportmanager.cpp index f2634d480..8dd2e3212 100644 --- a/skgbankmodeler/skgimportexportmanager.cpp +++ b/skgbankmodeler/skgimportexportmanager.cpp @@ -161,6 +161,8 @@ SKGError SKGImportExportManager::getDefaultAccount(SKGAccountObject& oAccount) whereClause += " OR t_BANK_NUMBER||t_agency_number||t_number='" % SKGServices::stringToSqlString(number) % "'"; whereClause += " OR (t_number!='' AND '" % SKGServices::stringToSqlString(number) % "' LIKE t_BANK_NUMBER||t_agency_number||t_number||'__')"; } + 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) % "')"; + const auto words = nameComplete.split(' '); for (const auto& val : words) { whereClause += " OR t_number='" % SKGServices::stringToSqlString(val) % '\''; diff --git a/tests/input/skgtestimportcsv/XXXX485.csv b/tests/input/skgtestimportcsv/XXXX485.csv new file mode 100644 index 000000000..bdc857b42 --- /dev/null +++ b/tests/input/skgtestimportcsv/XXXX485.csv @@ -0,0 +1,11 @@ +Export Format, Date (YYYY-MM-DD as UTC), Time (HH:MM:SS), Merchant, Txn Amount (Funding Card), Txn Currency (Funding Card), Txn Amount (Foreign Spend), Txn Currency (Foreign Spend), Card Name, Card Last 4 Digits, Type, Category, Notes +CSV, 2019-08-01, 11:49:53, "Ireland Vending", 3.500000, EUR, 0.000000, , "KBC Credit", 7427, , Food & Drink, +CSV, 2019-08-02, 09:04:35, "Gather & Gather", 3.150000, EUR, 0.000000, , "KBC Credit", 7427, , Food & Drink, +CSV, 2019-08-03, 10:24:05, "Curve Cash: Tesco", 98.000000, CPT, 0.000000, , "Curve Cash", (null), , (null), +CSV, 2019-08-03, 12:07:12, "Jack Jones", 25.000000, EUR, 0.000000, , "KBC Credit", 7427, , Shopping, +CSV, 2019-09-03, 08:12:19, "Amznmktplace", 42.720000, EUR, 38.060000, GBP, "Cepsa", 1575, , Shopping, +CSV, 2019-09-03, 08:12:21, "Curve Cash: Amznmktplace ", 39.000000, CPT, 0.000000, , "Curve Cash", (null), , , +CSV, 2019-09-21, 10:20:47, "Mip*3ireland Top Up", 20.000000, EUR, 0.000000, , "KBC Credit", 7427, , Bills, +CSV, 2019-09-21, 16:30:43, "Tesco", 79.720000, EUR, 0.000000, , "KBC Credit", 7427, , Groceries, +CSV, 2019-09-21, 16:30:46, "Curve Cash: Tesco", 70.000000, CPT, 0.000000, , "Curve Cash", (null), , , +CSV, 2019-09-21, 16:45:31, "Star Buys", 9.150000, EUR, 0.000000, , "KBC Credit", 7427, , Shopping, diff --git a/tests/input/skgtestimportofx/XXXX485.ofx b/tests/input/skgtestimportofx/XXXX485.ofx new file mode 100644 index 000000000..17b29032a --- /dev/null +++ b/tests/input/skgtestimportofx/XXXX485.ofx @@ -0,0 +1,92 @@ +OFXHEADER:100 +DATA:OFXSGML +VERSION:151 +SECURITY:NONE +ENCODING:USASCII +CHARSET:1252 +COMPRESSION:NONE +OLDFILEUID:NONE +NEWFILEUID:NONE +<OFX> +<SIGNONMSGSRSV2> +<SONRS> +<STATUS> +<CODE>0 +<SEVERITY>INFO +</STATUS> +<DTSERVER>20100419065700 +<LANGUAGE>FR +</SONRS> +</SIGNONMSGSRSV2> +<BANKMSGSRSV2> +<STMTTRNRS> +<TRNUID>00 +<STATUS> +<CODE>0 +<SEVERITY>INFO +</STATUS> +<STMTRS> +<CURDEF>EUR +<BANKACCTFROM> +<BANKID>18206 +<BRANCHID>XXXX485 +<ACCTID>43980062001 +<ACCTKEY>78 +<ACCTTYPE2>CHECKING +</BANKACCTFROM> +<BANKTRANLIST> +<DTSTART>20100410 +<DTEND>20100419 +<STMTTRN> +<TRNTYPE>DEBIT +<DTPOSTED>20100414 +<TRNAMT>-30.48 +<FITID>0007000017 +<NAME>Plt Free Telecom +<MEMO>N.104090458nne459654 +</STMTTRN> +<STMTTRN> +<TRNTYPE>DEBIT +<DTPOSTED>20100412 +<TRNAMT>-31.96 +<FITID>0007000016 +<NAME>Paiement Carte 100410 +<MEMO>Marche Fra Illzach +</STMTTRN> +<STMTTRN> +<TRNTYPE>DEBIT +<DTPOSTED>20100412 +<TRNAMT>-38.16 +<FITID>0007000015 +<NAME>Paiement Carte 100410 +<MEMO>Parea Ilen Mulhouse +</STMTTRN> +<STMTTRN> +<TRNTYPE>DEBIT +<DTPOSTED>20100412 +<TRNAMT>-90.00 +<FITID>0007000014 +<NAME>Ret Dab 090410 +<MEMO>Illzach +</STMTTRN> +<STMTTRN> +<TRNTYPE>CREDIT +<DTPOSTED>20100412 +<TRNAMT>15.20 +<FITID>0007000013 +<NAME>Vrt I.p.e.c.a. Prevoya +<MEMO>Virt Ipeca - Frais De San +</STMTTRN> +</BANKTRANLIST> +<LEDGERBAL> +<BALAMT>1029.32 +<DTASOF>20100419 +</LEDGERBAL> +<AVAILBAL> +<BALAMT>1029.32 +<DTASOF>20100419 +</AVAILBAL> +</STMTRS> +</STMTTRNRS> +</BANKMSGSRSV2> +</OFX> diff --git a/tests/skgbankmodelertest/skgtestimportcsv.cpp b/tests/skgbankmodelertest/skgtestimportcsv.cpp index 1cff96502..1a2f077e7 100644 --- a/tests/skgbankmodelertest/skgtestimportcsv.cpp +++ b/tests/skgbankmodelertest/skgtestimportcsv.cpp @@ -854,6 +854,36 @@ int main(int argc, char** argv) SKGTESTERROR(QStringLiteral("OPERATION.getNbObjects"), document1.getNbObjects(QStringLiteral("operation"), QStringLiteral("i_group_id=1"), nb), true) SKGTEST(QStringLiteral("OPERATION:nb"), nb, 2) } + + { + // 421302 + SKGDocumentBank document1; + SKGTESTERROR(QStringLiteral("document1.initialize()"), document1.initialize(), true) + SKGError err; + { + // Create an account without number + SKGBEGINTRANSACTION(document1, QStringLiteral("CREATE_ACCOUNT"), err) + SKGTESTERROR(QStringLiteral("DOC.addOrModifyAccount"), document1.addOrModifyAccount(QStringLiteral("COURANT"), QLatin1String("5823485"), QStringLiteral("BANK")), true) + SKGObjectBase account; + SKGTESTERROR(QStringLiteral("DOC.getObject"), document1.getObject(QStringLiteral("v_account"), QStringLiteral("t_name='COURANT'"), account), true) + SKGTESTERROR(QStringLiteral("ACCOUNT.setProperty"), account.setProperty(QStringLiteral("alias"), QStringLiteral("XXXX485")), true) + } + { + // Scope of the transaction + SKGBEGINTRANSACTION(document1, QStringLiteral("IMPORT_OFX"), err) + SKGImportExportManager imp1(&document1, QUrl::fromLocalFile(SKGTest::getTestPath(QStringLiteral("IN")) % "/skgtestimportcsv/XXXX485.csv")); + QMap<QString, QString> parameters = imp1.getImportParameters(); + parameters[QStringLiteral("automatic_search_columns")] = 'N'; + parameters[QStringLiteral("header_position")] = QStringLiteral("1"); + parameters[QStringLiteral("columns_positions")] = QStringLiteral("|date||payee|amount||||mode"); + imp1.setImportParameters(parameters); + SKGTESTERROR(QStringLiteral("CSV.importFile"), imp1.importFile(), true) + document1.dump(DUMPACCOUNT|DUMPPARAMETERS); + int nb2 = 0; + SKGTESTERROR(QStringLiteral("imp1.getNbObjects"), document1.getNbObjects(QStringLiteral("account"), QStringLiteral(""), nb2), true) + SKGTEST(QStringLiteral("ACCOUNT:nb"), SKGServices::intToString(nb2), QStringLiteral("1")) + } + } // End test SKGENDTEST() } // NOLINT(readability/fn_size) diff --git a/tests/skgbankmodelertest/skgtestimportofx.cpp b/tests/skgbankmodelertest/skgtestimportofx.cpp index 3daea30f4..fa398a3e8 100644 --- a/tests/skgbankmodelertest/skgtestimportofx.cpp +++ b/tests/skgbankmodelertest/skgtestimportofx.cpp @@ -473,6 +473,30 @@ int main(int argc, char** argv) } } + { + // 421302 + SKGDocumentBank document1; + SKGTESTERROR(QStringLiteral("document1.initialize()"), document1.initialize(), true) + SKGError err; + { + // Create an account without number + SKGBEGINTRANSACTION(document1, QStringLiteral("CREATE_ACCOUNT"), err) + SKGTESTERROR(QStringLiteral("DOC.addOrModifyAccount"), document1.addOrModifyAccount(QStringLiteral("COURANT"), QLatin1String("5823485"), QStringLiteral("BANK")), true) + SKGObjectBase account; + SKGTESTERROR(QStringLiteral("DOC.getObject"), document1.getObject(QStringLiteral("v_account"), QStringLiteral("t_name='COURANT'"), account), true) + SKGTESTERROR(QStringLiteral("ACCOUNT.setProperty"), account.setProperty(QStringLiteral("alias"), QStringLiteral("XXXX485")), true) + } + { + // Scope of the transaction + SKGBEGINTRANSACTION(document1, QStringLiteral("IMPORT_OFX"), err) + SKGImportExportManager imp1(&document1, QUrl::fromLocalFile(SKGTest::getTestPath(QStringLiteral("IN")) % "/skgtestimportofx/XXXX485.ofx")); + SKGTESTERROR(QStringLiteral("imp1.importFile"), imp1.importFile(), true) + document1.dump(DUMPACCOUNT|DUMPPARAMETERS); + int nb2 = 0; + SKGTESTERROR(QStringLiteral("imp1.getNbObjects"), document1.getNbObjects(QStringLiteral("account"), QStringLiteral(""), nb2), true) + SKGTEST(QStringLiteral("ACCOUNT:nb"), SKGServices::intToString(nb2), QStringLiteral("1")) + } + } // End test SKGENDTEST() }