diff --git a/kmymoney/mymoney/mymoneytransactionfilter.cpp b/kmymoney/mymoney/mymoneytransactionfilter.cpp index caa126d54..cb5eae67e 100644 --- a/kmymoney/mymoney/mymoneytransactionfilter.cpp +++ b/kmymoney/mymoney/mymoneytransactionfilter.cpp @@ -1,1035 +1,1031 @@ /* * Copyright 2003-2019 Thomas Baumgart * Copyright 2004 Ace Jones * Copyright 2008-2010 Alvaro Soliverez * Copyright 2017-2018 Łukasz Wojniłowicz * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License as * published by the Free Software Foundation; either version 2 of * the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see . */ #include "mymoneytransactionfilter.h" // ---------------------------------------------------------------------------- // QT Includes #include +#include #include // ---------------------------------------------------------------------------- // KDE Includes // ---------------------------------------------------------------------------- // Project Includes #include "mymoneymoney.h" #include "mymoneyfile.h" #include "mymoneyaccount.h" #include "mymoneysecurity.h" #include "mymoneypayee.h" #include "mymoneytag.h" #include "mymoneytransaction.h" #include "mymoneysplit.h" #include "mymoneyenums.h" class MyMoneyTransactionFilterPrivate { public: MyMoneyTransactionFilterPrivate() : m_reportAllSplits(false) , m_considerCategory(false) , m_considerCategorySplits(false) , m_matchOnly(false) , m_treatTransfersAsIncomeExpense(false) , m_matchingSplitsCount(0) , m_invertText(false) { - m_filterSet.allFilter = 0; } MyMoneyTransactionFilter::FilterSet m_filterSet; bool m_reportAllSplits; bool m_considerCategory; bool m_considerCategorySplits; bool m_matchOnly; bool m_treatTransfersAsIncomeExpense; uint m_matchingSplitsCount; QRegExp m_text; bool m_invertText; QHash m_accounts; QHash m_payees; QHash m_tags; QHash m_categories; QHash m_states; QHash m_types; QHash m_validity; QString m_fromNr, m_toNr; QDate m_fromDate, m_toDate; MyMoneyMoney m_fromAmount, m_toAmount; }; MyMoneyTransactionFilter::MyMoneyTransactionFilter() : d_ptr(new MyMoneyTransactionFilterPrivate) { Q_D(MyMoneyTransactionFilter); d->m_reportAllSplits = true; d->m_considerCategory = true; } MyMoneyTransactionFilter::MyMoneyTransactionFilter(const QString& id) : d_ptr(new MyMoneyTransactionFilterPrivate) { addAccount(id); } MyMoneyTransactionFilter::MyMoneyTransactionFilter(const MyMoneyTransactionFilter& other) : d_ptr(new MyMoneyTransactionFilterPrivate(*other.d_func())) { } MyMoneyTransactionFilter::~MyMoneyTransactionFilter() { Q_D(MyMoneyTransactionFilter); delete d; } void MyMoneyTransactionFilter::clear() { Q_D(MyMoneyTransactionFilter); - d->m_filterSet.allFilter = 0; + d->m_filterSet = {}; d->m_invertText = false; d->m_accounts.clear(); d->m_categories.clear(); d->m_payees.clear(); d->m_tags.clear(); d->m_types.clear(); d->m_states.clear(); d->m_validity.clear(); d->m_fromDate = QDate(); d->m_toDate = QDate(); } void MyMoneyTransactionFilter::clearAccountFilter() { Q_D(MyMoneyTransactionFilter); - d->m_filterSet.singleFilter.accountFilter = 0; + d->m_filterSet.setFlag(accountFilterActive); d->m_accounts.clear(); } void MyMoneyTransactionFilter::setTextFilter(const QRegExp& text, bool invert) { Q_D(MyMoneyTransactionFilter); - d->m_filterSet.singleFilter.textFilter = 1; + d->m_filterSet.setFlag(textFilterActive); d->m_invertText = invert; d->m_text = text; } void MyMoneyTransactionFilter::addAccount(const QStringList& ids) { Q_D(MyMoneyTransactionFilter); - d->m_filterSet.singleFilter.accountFilter = 1; + d->m_filterSet.setFlag(accountFilterActive); for (const auto& id : ids) addAccount(id); } void MyMoneyTransactionFilter::addAccount(const QString& id) { Q_D(MyMoneyTransactionFilter); if (!d->m_accounts.isEmpty() && !id.isEmpty() && d->m_accounts.contains(id)) return; - d->m_filterSet.singleFilter.accountFilter = 1; + d->m_filterSet.setFlag(accountFilterActive); if (!id.isEmpty()) d->m_accounts.insert(id, QString()); } void MyMoneyTransactionFilter::addCategory(const QStringList& ids) { Q_D(MyMoneyTransactionFilter); - d->m_filterSet.singleFilter.categoryFilter = 1; + d->m_filterSet.setFlag(categoryFilterActive); for (const auto& id : ids) addCategory(id); } void MyMoneyTransactionFilter::addCategory(const QString& id) { Q_D(MyMoneyTransactionFilter); if (!d->m_categories.isEmpty() && !id.isEmpty() && d->m_categories.contains(id)) return; - d->m_filterSet.singleFilter.categoryFilter = 1; + d->m_filterSet.setFlag(categoryFilterActive); if (!id.isEmpty()) d->m_categories.insert(id, QString()); } void MyMoneyTransactionFilter::setDateFilter(const QDate& from, const QDate& to) { Q_D(MyMoneyTransactionFilter); - d->m_filterSet.singleFilter.dateFilter = from.isValid() | to.isValid(); + d->m_filterSet.setFlag(dateFilterActive, from.isValid() | to.isValid()); d->m_fromDate = from; d->m_toDate = to; } void MyMoneyTransactionFilter::setAmountFilter(const MyMoneyMoney& from, const MyMoneyMoney& to) { Q_D(MyMoneyTransactionFilter); - d->m_filterSet.singleFilter.amountFilter = 1; + d->m_filterSet.setFlag(amountFilterActive); d->m_fromAmount = from.abs(); d->m_toAmount = to.abs(); // make sure that the user does not try to fool us ;-) if (from > to) std::swap(d->m_fromAmount, d->m_toAmount); } void MyMoneyTransactionFilter::addPayee(const QString& id) { Q_D(MyMoneyTransactionFilter); if (!d->m_payees.isEmpty() && !id.isEmpty() && d->m_payees.contains(id)) return; - d->m_filterSet.singleFilter.payeeFilter = 1; + d->m_filterSet.setFlag(payeeFilterActive); if (!id.isEmpty()) d->m_payees.insert(id, QString()); } void MyMoneyTransactionFilter::addTag(const QString& id) { Q_D(MyMoneyTransactionFilter); if (!d->m_tags.isEmpty() && !id.isEmpty() && d->m_tags.contains(id)) return; - d->m_filterSet.singleFilter.tagFilter = 1; + d->m_filterSet.setFlag(tagFilterActive); if (!id.isEmpty()) d->m_tags.insert(id, QString()); } void MyMoneyTransactionFilter::addType(const int type) { Q_D(MyMoneyTransactionFilter); if (!d->m_types.isEmpty() && d->m_types.contains(type)) return; - d->m_filterSet.singleFilter.typeFilter = 1; + d->m_filterSet.setFlag(typeFilterActive); d->m_types.insert(type, QString()); } void MyMoneyTransactionFilter::addState(const int state) { Q_D(MyMoneyTransactionFilter); if (!d->m_states.isEmpty() && d->m_states.contains(state)) return; - d->m_filterSet.singleFilter.stateFilter = 1; + d->m_filterSet.setFlag(stateFilterActive); d->m_states.insert(state, QString()); } void MyMoneyTransactionFilter::addValidity(const int type) { Q_D(MyMoneyTransactionFilter); if (!d->m_validity.isEmpty() && d->m_validity.contains(type)) return; - d->m_filterSet.singleFilter.validityFilter = 1; + d->m_filterSet.setFlag(validityFilterActive); d->m_validity.insert(type, QString()); } void MyMoneyTransactionFilter::setNumberFilter(const QString& from, const QString& to) { Q_D(MyMoneyTransactionFilter); - d->m_filterSet.singleFilter.nrFilter = 1; + d->m_filterSet.setFlag(nrFilterActive); d->m_fromNr = from; d->m_toNr = to; } void MyMoneyTransactionFilter::setReportAllSplits(const bool report) { Q_D(MyMoneyTransactionFilter); d->m_reportAllSplits = report; } void MyMoneyTransactionFilter::setConsiderCategorySplits(const bool check) { Q_D(MyMoneyTransactionFilter); d->m_considerCategorySplits = check; } void MyMoneyTransactionFilter::setConsiderCategory(const bool check) { Q_D(MyMoneyTransactionFilter); d->m_considerCategory = check; } void MyMoneyTransactionFilter::setTreatTransfersAsIncomeExpense(const bool check) { Q_D(MyMoneyTransactionFilter); d->m_treatTransfersAsIncomeExpense = check; } bool MyMoneyTransactionFilter::treatTransfersAsIncomeExpense() const { Q_D(const MyMoneyTransactionFilter); return d->m_treatTransfersAsIncomeExpense; } uint MyMoneyTransactionFilter::matchingSplitsCount(const MyMoneyTransaction& transaction) { Q_D(MyMoneyTransactionFilter); d->m_matchOnly = true; matchingSplits(transaction); d->m_matchOnly = false; return d->m_matchingSplitsCount; } QVector MyMoneyTransactionFilter::matchingSplits(const MyMoneyTransaction& transaction) { Q_D(MyMoneyTransactionFilter); QVector matchingSplits; const auto file = MyMoneyFile::instance(); // qDebug("T: %s", transaction.id().data()); // if no filter is set, we can safely return a match // if we should report all splits, then we collect them - if (!d->m_filterSet.allFilter && d->m_reportAllSplits) { + if (!d->m_filterSet && d->m_reportAllSplits) { d->m_matchingSplitsCount = transaction.splitCount(); if (!d->m_matchOnly) matchingSplits = QVector::fromList(transaction.splits()); return matchingSplits; } d->m_matchingSplitsCount = 0; - const auto filter = d->m_filterSet.singleFilter; + const auto filter = d->m_filterSet; // perform checks on the MyMoneyTransaction object first // check the date range - if (filter.dateFilter) { + if (filter & dateFilterActive) { if ((d->m_fromDate != QDate() && transaction.postDate() < d->m_fromDate) || (d->m_toDate != QDate() && transaction.postDate() > d->m_toDate)) { return matchingSplits; } } - auto categoryMatched = !filter.categoryFilter; - auto accountMatched = !filter.accountFilter; + auto categoryMatched = !(filter & categoryFilterActive); + auto accountMatched = !(filter & accountFilterActive); auto isTransfer = true; // check the transaction's validity - if (filter.validityFilter) { + if (filter & validityFilterActive) { if (!d->m_validity.isEmpty() && !d->m_validity.contains((int)validTransaction(transaction))) return matchingSplits; } // if d->m_reportAllSplits == false.. // ...then we don't need splits... // ...but we need to know if there were any found auto isMatchingSplitsEmpty = true; auto extendedFilter = d->m_filterSet; - extendedFilter.singleFilter.dateFilter = 0; - extendedFilter.singleFilter.accountFilter = 0; - extendedFilter.singleFilter.categoryFilter = 0; + extendedFilter.setFlag(dateFilterActive, false); + extendedFilter.setFlag(accountFilterActive, false); + extendedFilter.setFlag(categoryFilterActive, false); - if (filter.accountFilter || - filter.categoryFilter || - extendedFilter.allFilter) { + if (filter.testFlag(accountFilterActive) || + filter.testFlag(categoryFilterActive) || + extendedFilter != 0) { const auto& splits = transaction.splits(); for (const auto& s : splits) { - if (filter.accountFilter || - filter.categoryFilter) { + if (filter.testFlag(accountFilterActive) || + filter.testFlag(categoryFilterActive)) { auto removeSplit = true; if (d->m_considerCategory) { switch (file->account(s.accountId()).accountGroup()) { case eMyMoney::Account::Type::Income: case eMyMoney::Account::Type::Expense: isTransfer = false; // check if the split references one of the categories in the list - if (filter.categoryFilter) { + if (filter.testFlag(categoryFilterActive)) { if (d->m_categories.isEmpty()) { // we're looking for transactions with 'no' categories d->m_matchingSplitsCount = 0; matchingSplits.clear(); return matchingSplits; } else if (d->m_categories.contains(s.accountId())) { categoryMatched = true; removeSplit = false; } } break; default: // check if the split references one of the accounts in the list - if (!filter.accountFilter) { + if (!filter.testFlag(accountFilterActive)) { removeSplit = false; } else if (!d->m_accounts.isEmpty() && d->m_accounts.contains(s.accountId())) { accountMatched = true; removeSplit = false; } break; } } else { - if (!filter.accountFilter) { + if (!filter.testFlag(accountFilterActive)) { removeSplit = false; } else if (!d->m_accounts.isEmpty() && d->m_accounts.contains(s.accountId())) { accountMatched = true; removeSplit = false; } } if (removeSplit) continue; } // check if less frequent filters are active - if (extendedFilter.allFilter) { + if (extendedFilter != 0) { const auto acc = file->account(s.accountId()); if (!(matchAmount(s) && matchText(s, acc))) continue; // Determine if this account is a category or an account auto isCategory = false; switch (acc.accountGroup()) { case eMyMoney::Account::Type::Income: case eMyMoney::Account::Type::Expense: isCategory = true; default: break; } bool includeSplit = d->m_considerCategorySplits || (!d->m_considerCategorySplits && !isCategory); if (includeSplit) { // check the payee list - if (filter.payeeFilter) { + if (filter.testFlag(payeeFilterActive)) { if (!d->m_payees.isEmpty()) { if (s.payeeId().isEmpty() || !d->m_payees.contains(s.payeeId())) continue; } else if (!s.payeeId().isEmpty()) continue; } // check the tag list - if (filter.tagFilter) { + if (filter.testFlag(tagFilterActive)) { const auto tags = s.tagIdList(); if (!d->m_tags.isEmpty()) { if (tags.isEmpty()) { continue; } else { auto found = false; for (const auto& tag : tags) { if (d->m_tags.contains(tag)) { found = true; break; } } if (!found) continue; } } else if (!tags.isEmpty()) continue; } // check the type list - if (filter.typeFilter && + if (filter.testFlag(typeFilterActive) && !d->m_types.isEmpty() && !d->m_types.contains(splitType(transaction, s, acc))) continue; // check the state list - if (filter.stateFilter && + if (filter.testFlag(stateFilterActive) && !d->m_states.isEmpty() && !d->m_states.contains(splitState(s))) continue; - if (filter.nrFilter && + if (filter.testFlag(nrFilterActive) && ((!d->m_fromNr.isEmpty() && s.number() < d->m_fromNr) || (!d->m_toNr.isEmpty() && s.number() > d->m_toNr))) continue; - } else if (filter.payeeFilter - || filter.tagFilter - || filter.typeFilter - || filter.stateFilter - || filter.nrFilter) { + } else if (filter & (payeeFilterActive | tagFilterActive | typeFilterActive | stateFilterActive | nrFilterActive)) { continue; } } if (d->m_reportAllSplits) matchingSplits.append(s); isMatchingSplitsEmpty = false; } } else if (d->m_reportAllSplits) { const auto& splits = transaction.splits(); for (const auto& s : splits) matchingSplits.append(s); d->m_matchingSplitsCount = matchingSplits.count(); return matchingSplits; } else if (transaction.splitCount() > 0) { isMatchingSplitsEmpty = false; } // check if we're looking for transactions without assigned category if (!categoryMatched && transaction.splitCount() == 1 && d->m_categories.isEmpty()) categoryMatched = true; // if there's no category filter and the category did not // match, then we still want to see this transaction if it's // a transfer - if (!categoryMatched && !filter.categoryFilter) + if (!categoryMatched && !filter.testFlag(categoryFilterActive)) categoryMatched = isTransfer; if (isMatchingSplitsEmpty || !(accountMatched && categoryMatched)) { d->m_matchingSplitsCount = 0; return matchingSplits; } if (!d->m_reportAllSplits && !isMatchingSplitsEmpty) { d->m_matchingSplitsCount = 1; if (!d->m_matchOnly) matchingSplits.append(transaction.firstSplit()); } else { d->m_matchingSplitsCount = matchingSplits.count(); } // all filters passed, I guess we have a match // qDebug(" C: %d", m_matchingSplits.count()); return matchingSplits; } QDate MyMoneyTransactionFilter::fromDate() const { Q_D(const MyMoneyTransactionFilter); return d->m_fromDate; } QDate MyMoneyTransactionFilter::toDate() const { Q_D(const MyMoneyTransactionFilter); return d->m_toDate; } bool MyMoneyTransactionFilter::matchText(const MyMoneySplit& s, const MyMoneyAccount& acc) const { Q_D(const MyMoneyTransactionFilter); // check if the text is contained in one of the fields // memo, value, number, payee, tag, account - if (d->m_filterSet.singleFilter.textFilter) { + if (d->m_filterSet & textFilterActive) { const auto file = MyMoneyFile::instance(); const auto sec = file->security(acc.currencyId()); if (s.memo().contains(d->m_text) || s.shares().formatMoney(acc.fraction(sec)).contains(d->m_text) || s.value().formatMoney(acc.fraction(sec)).contains(d->m_text) || s.number().contains(d->m_text) || (d->m_text.pattern().compare(s.transactionId())) == 0) return !d->m_invertText; if (acc.name().contains(d->m_text)) return !d->m_invertText; if (!s.payeeId().isEmpty() && file->payee(s.payeeId()).name().contains(d->m_text)) return !d->m_invertText; const auto& tagIdList = s.tagIdList(); for (const auto& tag : tagIdList) if (file->tag(tag).name().contains(d->m_text)) return !d->m_invertText; return d->m_invertText; } return true; } bool MyMoneyTransactionFilter::matchAmount(const MyMoneySplit& s) const { Q_D(const MyMoneyTransactionFilter); - if (d->m_filterSet.singleFilter.amountFilter) { + if (d->m_filterSet & amountFilterActive) { const auto value = s.value().abs(); const auto shares = s.shares().abs(); if ((value < d->m_fromAmount || value > d->m_toAmount) && (shares < d->m_fromAmount || shares > d->m_toAmount)) return false; } return true; } bool MyMoneyTransactionFilter::match(const MyMoneySplit& s) const { const auto& acc = MyMoneyFile::instance()->account(s.accountId()); return matchText(s, acc) && matchAmount(s); } bool MyMoneyTransactionFilter::match(const MyMoneyTransaction& transaction) { Q_D(MyMoneyTransactionFilter); d->m_matchOnly = true; matchingSplits(transaction); d->m_matchOnly = false; return d->m_matchingSplitsCount > 0; } int MyMoneyTransactionFilter::splitState(const MyMoneySplit& split) const { switch (split.reconcileFlag()) { default: case eMyMoney::Split::State::NotReconciled: return (int)eMyMoney::TransactionFilter::State::NotReconciled; case eMyMoney::Split::State::Cleared: return (int)eMyMoney::TransactionFilter::State::Cleared; case eMyMoney::Split::State::Reconciled: return (int)eMyMoney::TransactionFilter::State::Reconciled; case eMyMoney::Split::State::Frozen: return (int)eMyMoney::TransactionFilter::State::Frozen; } } int MyMoneyTransactionFilter::splitType(const MyMoneyTransaction& t, const MyMoneySplit& split, const MyMoneyAccount& acc) const { Q_D(const MyMoneyTransactionFilter); if (acc.isIncomeExpense()) return (int)eMyMoney::TransactionFilter::Type::All; if (t.splitCount() == 2 && !d->m_treatTransfersAsIncomeExpense) { const auto& splits = t.splits(); const auto file = MyMoneyFile::instance(); const auto& a = splits.at(0).id().compare(split.id()) == 0 ? acc : file->account(splits.at(0).accountId()); const auto& b = splits.at(1).id().compare(split.id()) == 0 ? acc : file->account(splits.at(1).accountId()); if (!a.isIncomeExpense() && !b.isIncomeExpense()) return (int)eMyMoney::TransactionFilter::Type::Transfers; } if (split.value().isPositive()) return (int)eMyMoney::TransactionFilter::Type::Deposits; return (int)eMyMoney::TransactionFilter::Type::Payments; } eMyMoney::TransactionFilter::Validity MyMoneyTransactionFilter::validTransaction(const MyMoneyTransaction& t) const { MyMoneyMoney val; const auto& splits = t.splits(); for (const auto& split : splits) val += split.value(); return (val == MyMoneyMoney()) ? eMyMoney::TransactionFilter::Validity::Valid : eMyMoney::TransactionFilter::Validity::Invalid; } bool MyMoneyTransactionFilter::includesCategory(const QString& cat) const { Q_D(const MyMoneyTransactionFilter); - return !d->m_filterSet.singleFilter.categoryFilter || d->m_categories.contains(cat); + return !d->m_filterSet.testFlag(categoryFilterActive) || d->m_categories.contains(cat); } bool MyMoneyTransactionFilter::includesAccount(const QString& acc) const { Q_D(const MyMoneyTransactionFilter); - return !d->m_filterSet.singleFilter.accountFilter || d->m_accounts.contains(acc); + return !d->m_filterSet.testFlag(accountFilterActive) || d->m_accounts.contains(acc); } bool MyMoneyTransactionFilter::includesPayee(const QString& pye) const { Q_D(const MyMoneyTransactionFilter); - return !d->m_filterSet.singleFilter.payeeFilter || d->m_payees.contains(pye); + return !d->m_filterSet.testFlag(payeeFilterActive) || d->m_payees.contains(pye); } bool MyMoneyTransactionFilter::includesTag(const QString& tag) const { Q_D(const MyMoneyTransactionFilter); - return !d->m_filterSet.singleFilter.tagFilter || d->m_tags.contains(tag); + return !d->m_filterSet.testFlag(tagFilterActive) || d->m_tags.contains(tag); } bool MyMoneyTransactionFilter::dateFilter(QDate& from, QDate& to) const { Q_D(const MyMoneyTransactionFilter); from = d->m_fromDate; to = d->m_toDate; - return d->m_filterSet.singleFilter.dateFilter == 1; + return d->m_filterSet.testFlag(dateFilterActive); } bool MyMoneyTransactionFilter::amountFilter(MyMoneyMoney& from, MyMoneyMoney& to) const { Q_D(const MyMoneyTransactionFilter); from = d->m_fromAmount; to = d->m_toAmount; - return d->m_filterSet.singleFilter.amountFilter == 1; + return d->m_filterSet.testFlag(amountFilterActive); } bool MyMoneyTransactionFilter::numberFilter(QString& from, QString& to) const { Q_D(const MyMoneyTransactionFilter); from = d->m_fromNr; to = d->m_toNr; - return d->m_filterSet.singleFilter.nrFilter == 1; + return d->m_filterSet.testFlag(nrFilterActive); } bool MyMoneyTransactionFilter::payees(QStringList& list) const { Q_D(const MyMoneyTransactionFilter); - auto result = d->m_filterSet.singleFilter.payeeFilter; + auto result = d->m_filterSet.testFlag(payeeFilterActive); if (result) { QHashIterator it_payee(d->m_payees); while (it_payee.hasNext()) { it_payee.next(); list += it_payee.key(); } } return result; } bool MyMoneyTransactionFilter::tags(QStringList& list) const { Q_D(const MyMoneyTransactionFilter); - auto result = d->m_filterSet.singleFilter.tagFilter; + auto result = d->m_filterSet.testFlag(tagFilterActive); if (result) { QHashIterator it_tag(d->m_tags); while (it_tag.hasNext()) { it_tag.next(); list += it_tag.key(); } } return result; } bool MyMoneyTransactionFilter::accounts(QStringList& list) const { Q_D(const MyMoneyTransactionFilter); - auto result = d->m_filterSet.singleFilter.accountFilter; + auto result = d->m_filterSet.testFlag(accountFilterActive); if (result) { QHashIterator it_account(d->m_accounts); while (it_account.hasNext()) { it_account.next(); QString account = it_account.key(); list += account; } } return result; } bool MyMoneyTransactionFilter::categories(QStringList& list) const { Q_D(const MyMoneyTransactionFilter); - auto result = d->m_filterSet.singleFilter.categoryFilter; + auto result = d->m_filterSet.testFlag(categoryFilterActive); if (result) { QHashIterator it_category(d->m_categories); while (it_category.hasNext()) { it_category.next(); list += it_category.key(); } } return result; } bool MyMoneyTransactionFilter::types(QList& list) const { Q_D(const MyMoneyTransactionFilter); - auto result = d->m_filterSet.singleFilter.typeFilter; + auto result = d->m_filterSet.testFlag(typeFilterActive); if (result) { QHashIterator it_type(d->m_types); while (it_type.hasNext()) { it_type.next(); list += it_type.key(); } } return result; } bool MyMoneyTransactionFilter::states(QList& list) const { Q_D(const MyMoneyTransactionFilter); - auto result = d->m_filterSet.singleFilter.stateFilter; + auto result = d->m_filterSet.testFlag(stateFilterActive); if (result) { QHashIterator it_state(d->m_states); while (it_state.hasNext()) { it_state.next(); list += it_state.key(); } } return result; } bool MyMoneyTransactionFilter::validities(QList& list) const { Q_D(const MyMoneyTransactionFilter); - auto result = d->m_filterSet.singleFilter.validityFilter; + auto result = d->m_filterSet.testFlag(validityFilterActive); if (result) { QHashIterator it_validity(d->m_validity); while (it_validity.hasNext()) { it_validity.next(); list += it_validity.key(); } } return result; } bool MyMoneyTransactionFilter::firstType(int&i) const { Q_D(const MyMoneyTransactionFilter); - auto result = d->m_filterSet.singleFilter.typeFilter; + auto result = d->m_filterSet.testFlag(typeFilterActive); if (result) { QHashIterator it_type(d->m_types); if (it_type.hasNext()) { it_type.next(); i = it_type.key(); } } return result; } bool MyMoneyTransactionFilter::firstState(int&i) const { Q_D(const MyMoneyTransactionFilter); - auto result = d->m_filterSet.singleFilter.stateFilter; + auto result = d->m_filterSet.testFlag(stateFilterActive); if (result) { QHashIterator it_state(d->m_states); if (it_state.hasNext()) { it_state.next(); i = it_state.key(); } } return result; } bool MyMoneyTransactionFilter::firstValidity(int&i) const { Q_D(const MyMoneyTransactionFilter); - auto result = d->m_filterSet.singleFilter.validityFilter; + auto result = d->m_filterSet.testFlag(validityFilterActive); if (result) { QHashIterator it_validity(d->m_validity); if (it_validity.hasNext()) { it_validity.next(); i = it_validity.key(); } } return result; } bool MyMoneyTransactionFilter::textFilter(QRegExp& exp) const { Q_D(const MyMoneyTransactionFilter); exp = d->m_text; - return d->m_filterSet.singleFilter.textFilter == 1; + return d->m_filterSet.testFlag(textFilterActive); } bool MyMoneyTransactionFilter::isInvertingText() const { Q_D(const MyMoneyTransactionFilter); return d->m_invertText; } void MyMoneyTransactionFilter::setDateFilter(eMyMoney::TransactionFilter::Date range) { QDate from, to; if (translateDateRange(range, from, to)) setDateFilter(from, to); } static int fiscalYearStartMonth = 1; static int fiscalYearStartDay = 1; void MyMoneyTransactionFilter::setFiscalYearStart(int firstMonth, int firstDay) { fiscalYearStartMonth = firstMonth; fiscalYearStartDay = firstDay; } bool MyMoneyTransactionFilter::translateDateRange(eMyMoney::TransactionFilter::Date id, QDate& start, QDate& end) { bool rc = true; int yr = QDate::currentDate().year(); int mon = QDate::currentDate().month(); switch (id) { case eMyMoney::TransactionFilter::Date::All: start = QDate(); end = QDate(); break; case eMyMoney::TransactionFilter::Date::AsOfToday: start = QDate(); end = QDate::currentDate(); break; case eMyMoney::TransactionFilter::Date::CurrentMonth: start = QDate(yr, mon, 1); end = QDate(yr, mon, 1).addMonths(1).addDays(-1); break; case eMyMoney::TransactionFilter::Date::CurrentYear: start = QDate(yr, 1, 1); end = QDate(yr, 12, 31); break; case eMyMoney::TransactionFilter::Date::MonthToDate: start = QDate(yr, mon, 1); end = QDate::currentDate(); break; case eMyMoney::TransactionFilter::Date::YearToDate: start = QDate(yr, 1, 1); end = QDate::currentDate(); break; case eMyMoney::TransactionFilter::Date::YearToMonth: start = QDate(yr, 1, 1); end = QDate(yr, mon, 1).addDays(-1); break; case eMyMoney::TransactionFilter::Date::LastMonth: start = QDate(yr, mon, 1).addMonths(-1); end = QDate(yr, mon, 1).addDays(-1); break; case eMyMoney::TransactionFilter::Date::LastYear: start = QDate(yr, 1, 1).addYears(-1); end = QDate(yr, 12, 31).addYears(-1); break; case eMyMoney::TransactionFilter::Date::Last7Days: start = QDate::currentDate().addDays(-7); end = QDate::currentDate(); break; case eMyMoney::TransactionFilter::Date::Last30Days: start = QDate::currentDate().addDays(-30); end = QDate::currentDate(); break; case eMyMoney::TransactionFilter::Date::Last3Months: start = QDate::currentDate().addMonths(-3); end = QDate::currentDate(); break; case eMyMoney::TransactionFilter::Date::Last6Months: start = QDate::currentDate().addMonths(-6); end = QDate::currentDate(); break; case eMyMoney::TransactionFilter::Date::Last11Months: start = QDate(yr, mon, 1).addMonths(-12); end = QDate(yr, mon, 1).addDays(-1); break; case eMyMoney::TransactionFilter::Date::Last12Months: start = QDate::currentDate().addMonths(-12); end = QDate::currentDate(); break; case eMyMoney::TransactionFilter::Date::Next7Days: start = QDate::currentDate(); end = QDate::currentDate().addDays(7); break; case eMyMoney::TransactionFilter::Date::Next30Days: start = QDate::currentDate(); end = QDate::currentDate().addDays(30); break; case eMyMoney::TransactionFilter::Date::Next3Months: start = QDate::currentDate(); end = QDate::currentDate().addMonths(3); break; case eMyMoney::TransactionFilter::Date::Next6Months: start = QDate::currentDate(); end = QDate::currentDate().addMonths(6); break; case eMyMoney::TransactionFilter::Date::Next12Months: start = QDate::currentDate(); end = QDate::currentDate().addMonths(12); break; case eMyMoney::TransactionFilter::Date::Next18Months: start = QDate::currentDate(); end = QDate::currentDate().addMonths(18); break; case eMyMoney::TransactionFilter::Date::UserDefined: start = QDate(); end = QDate(); break; case eMyMoney::TransactionFilter::Date::Last3ToNext3Months: start = QDate::currentDate().addMonths(-3); end = QDate::currentDate().addMonths(3); break; case eMyMoney::TransactionFilter::Date::CurrentQuarter: start = QDate(yr, mon - ((mon - 1) % 3), 1); end = start.addMonths(3).addDays(-1); break; case eMyMoney::TransactionFilter::Date::LastQuarter: start = QDate(yr, mon - ((mon - 1) % 3), 1).addMonths(-3); end = start.addMonths(3).addDays(-1); break; case eMyMoney::TransactionFilter::Date::NextQuarter: start = QDate(yr, mon - ((mon - 1) % 3), 1).addMonths(3); end = start.addMonths(3).addDays(-1); break; case eMyMoney::TransactionFilter::Date::CurrentFiscalYear: start = QDate(QDate::currentDate().year(), fiscalYearStartMonth, fiscalYearStartDay); if (QDate::currentDate() < start) start = start.addYears(-1); end = start.addYears(1).addDays(-1); break; case eMyMoney::TransactionFilter::Date::LastFiscalYear: start = QDate(QDate::currentDate().year(), fiscalYearStartMonth, fiscalYearStartDay); if (QDate::currentDate() < start) start = start.addYears(-1); start = start.addYears(-1); end = start.addYears(1).addDays(-1); break; case eMyMoney::TransactionFilter::Date::Today: start = QDate::currentDate(); end = QDate::currentDate(); break; default: qWarning("Unknown date identifier %d in MyMoneyTransactionFilter::translateDateRange()", (int)id); rc = false; break; } return rc; } MyMoneyTransactionFilter::FilterSet MyMoneyTransactionFilter::filterSet() const { Q_D(const MyMoneyTransactionFilter); return d->m_filterSet; } void MyMoneyTransactionFilter::removeReference(const QString& id) { Q_D(MyMoneyTransactionFilter); if (d->m_accounts.end() != d->m_accounts.find(id)) { qDebug("%s", qPrintable(QString("Remove account '%1' from report").arg(id))); d->m_accounts.take(id); } else if (d->m_categories.end() != d->m_categories.find(id)) { qDebug("%s", qPrintable(QString("Remove category '%1' from report").arg(id))); d->m_categories.remove(id); } else if (d->m_payees.end() != d->m_payees.find(id)) { qDebug("%s", qPrintable(QString("Remove payee '%1' from report").arg(id))); d->m_payees.remove(id); } else if (d->m_tags.end() != d->m_tags.find(id)) { qDebug("%s", qPrintable(QString("Remove tag '%1' from report").arg(id))); d->m_tags.remove(id); } } diff --git a/kmymoney/mymoney/mymoneytransactionfilter.h b/kmymoney/mymoney/mymoneytransactionfilter.h index 2acd6b293..d58f645c2 100644 --- a/kmymoney/mymoney/mymoneytransactionfilter.h +++ b/kmymoney/mymoney/mymoneytransactionfilter.h @@ -1,604 +1,603 @@ /* * Copyright 2003-2019 Thomas Baumgart * Copyright 2004 Ace Jones * Copyright 2008-2010 Alvaro Soliverez * Copyright 2017-2018 Łukasz Wojniłowicz * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License as * published by the Free Software Foundation; either version 2 of * the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see . */ #ifndef MYMONEYTRANSACTIONFILTER_H #define MYMONEYTRANSACTIONFILTER_H #include "kmm_mymoney_export.h" // ---------------------------------------------------------------------------- // QT Includes #include // ---------------------------------------------------------------------------- // KDE Includes // ---------------------------------------------------------------------------- // Project Includes class QString; class QDate; template class QList; class MyMoneyMoney; class MyMoneySplit; class MyMoneyAccount; namespace eMyMoney { namespace TransactionFilter { enum class Date; enum class Validity; } } /** * @author Thomas Baumgart * @author Łukasz Wojniłowicz */ class MyMoneyTransaction; class MyMoneyTransactionFilterPrivate; class KMM_MYMONEY_EXPORT MyMoneyTransactionFilter { Q_DECLARE_PRIVATE(MyMoneyTransactionFilter) protected: MyMoneyTransactionFilterPrivate* d_ptr; // name shouldn't colide with the one in mymoneyreport.h public: - typedef union { - unsigned allFilter; - struct { - unsigned textFilter : 1; - unsigned accountFilter : 1; - unsigned payeeFilter : 1; - unsigned tagFilter : 1; - unsigned categoryFilter : 1; - unsigned nrFilter : 1; - unsigned dateFilter : 1; - unsigned amountFilter : 1; - unsigned typeFilter : 1; - unsigned stateFilter : 1; - unsigned validityFilter : 1; - } singleFilter; - } FilterSet; + enum FilterFlags { + textFilterActive = 0x0001, + accountFilterActive = 0x0002, + payeeFilterActive = 0x0004, + tagFilterActive = 0x0008, + categoryFilterActive = 0x0010, + nrFilterActive = 0x0020, + dateFilterActive = 0x0040, + amountFilterActive = 0x0080, + typeFilterActive = 0x0100, + stateFilterActive = 0x0200, + validityFilterActive = 0x0400 + }; + Q_DECLARE_FLAGS(FilterSet, FilterFlags) /** * This is the standard constructor for a transaction filter. * It creates the object and calls setReportAllSplits() to * report all matching splits as separate entries. Use * setReportAllSplits() to override this behaviour. */ MyMoneyTransactionFilter(); /** * This is a convenience constructor to allow construction of * a simple account filter. It is basically the same as the * following: * * @code * : * MyMoneyTransactionFilter filter; * filter.setReportAllSplits(false); * filter.addAccount(id); * : * @endcode * * @param id reference to account id */ explicit MyMoneyTransactionFilter(const QString& id); MyMoneyTransactionFilter(const MyMoneyTransactionFilter & other); MyMoneyTransactionFilter(MyMoneyTransactionFilter && other); MyMoneyTransactionFilter & operator=(MyMoneyTransactionFilter other); friend void swap(MyMoneyTransactionFilter& first, MyMoneyTransactionFilter& second); virtual ~MyMoneyTransactionFilter(); /** * This method is used to clear the filter. All settings will be * removed. */ void clear(); /** * This method is used to clear the accounts filter only. */ void clearAccountFilter(); /** * This method is used to set the regular expression filter to the value specified * as parameter @p exp. The following text based fields are searched: * * - Memo * - Payee * - Tag * - Category * - Shares / Value * - Number * * @param exp The regular expression that must be found in a transaction * before it is included in the result set. * @param invert If true, value must not be contained in any of the above mentioned fields * */ void setTextFilter(const QRegExp& exp, bool invert = false); /** * This method will add the account with id @p id to the list of matching accounts. * If the list is empty, any transaction will match. * * @param id internal ID of the account */ void addAccount(const QString& id); /** * This is a convenience method and behaves exactly like the above * method but for a list of id's. */ void addAccount(const QStringList& ids); /** * This method will add the category with id @p id to the list of matching categories. * If the list is empty, only transaction with a single asset/liability account will match. * * @param id internal ID of the account */ void addCategory(const QString& id); /** * This is a convenience method and behaves exactly like the above * method but for a list of id's. */ void addCategory(const QStringList& ids); /** * This method sets the date filter to match only transactions with posting dates in * the date range specified by @p from and @p to. If @p from equal QDate() * all transactions with dates prior to @p to match. If @p to equals QDate() * all transactions with posting dates past @p from match. If @p from and @p to * are equal QDate() the filter is not activated and all transactions match. * * @param from from date * @param to to date */ void setDateFilter(const QDate& from, const QDate& to); void setDateFilter(eMyMoney::TransactionFilter::Date range); /** * This method sets the amount filter to match only transactions with * an amount in the range specified by @p from and @p to. * If a specific amount should be searched, @p from and @p to should be * the same value. * * @param from smallest value to match * @param to largest value to match */ void setAmountFilter(const MyMoneyMoney& from, const MyMoneyMoney& to); /** * This method will add the payee with id @p id to the list of matching payees. * If the list is empty, any transaction will match. * * @param id internal id of the payee */ void addPayee(const QString& id); /** * This method will add the tag with id @ta id to the list of matching tags. * If the list is empty, any transaction will match. * * @param id internal id of the tag */ void addTag(const QString& id); /** */ void addType(const int type); /** */ void addValidity(const int type); /** */ void addState(const int state); /** * This method sets the number filter to match only transactions with * a number in the range specified by @p from and @p to. * If a specific number should be searched, @p from and @p to should be * the same value. * * @param from smallest value to match * @param to largest value to match * * @note @p from and @p to can contain alphanumeric text */ void setNumberFilter(const QString& from, const QString& to); /** * This method is used to check a specific transaction against the filter. * The transaction will match the whole filter, if all specified filters * match. If the filter is cleared using the clear() method, any transaction * matches. Matching splits from the transaction are returned by @ref * matchingSplits(). * * @param transaction A transaction * * @retval true The transaction matches the filter set * @retval false The transaction does not match at least one of * the filters in the filter set */ bool match(const MyMoneyTransaction& transaction); /** * This method is used to check a specific split against the * text filter. The split will match if all specified and * checked filters match. If the filter is cleared using the clear() * method, any split matches. * * @param sp pointer to the split to be checked * * @retval true The split matches the filter set * @retval false The split does not match at least one of * the filters in the filter set */ bool matchText(const MyMoneySplit& s, const MyMoneyAccount &acc) const; /** * This method is used to check a specific split against the * amount filter. The split will match if all specified and * checked filters match. If the filter is cleared using the clear() * method, any split matches. * * @param sp const reference to the split to be checked * * @retval true The split matches the filter set * @retval false The split does not match at least one of * the filters in the filter set */ bool matchAmount(const MyMoneySplit& s) const; /** * Convenience method which actually returns matchText(sp) && matchAmount(sp). */ bool match(const MyMoneySplit& s) const; /** * This method is used to switch the amount of splits reported * by matchingSplits(). If the argument @p report is @p true (the default * if no argument specified) then matchingSplits() will return all * matching splits of the transaction. If @p report is set to @p false, * then only the very first matching split will be returned by * matchingSplits(). * * @param report controls the behaviour of matchingsSplits() as explained above. */ void setReportAllSplits(const bool report = true); /** * Consider splits in categories * * With this setting, splits in categories that are not considered * by default are taken into account. * * @param check check state */ void setConsiderCategorySplits(const bool check = true); /** * Consider income and expense categories * * If the account or category filter is enabled, categories of * income and expense type are included if enabled with this * method. * * @param check check state */ void setConsiderCategory(const bool check = true); void setTreatTransfersAsIncomeExpense(const bool check = true); /** * This method is to avoid returning matching splits list * if only its count is needed * @return count of matching splits */ uint matchingSplitsCount(const MyMoneyTransaction& transaction); /** * This method returns a list of the matching splits for the filter. * If m_reportAllSplits is set to false, then only the very first * split will be returned. Use setReportAllSplits() to change the * behaviour. * * @return reference list of MyMoneySplit objects containing the * matching splits. If multiple splits match, only the first * one will be returned. * * @note an empty list will be returned, if the filter only required * to check the data contained in the MyMoneyTransaction * object (e.g. posting-date, state, etc.). * * @note The constructors set m_reportAllSplits differently. Please * see the documentation of the constructors MyMoneyTransactionFilter() * and MyMoneyTransactionFilter(const QString&) for details. */ QVector matchingSplits(const MyMoneyTransaction& transaction); /** * This method returns the from date set in the filter. If * no value has been set up for this filter, then QDate() is * returned. * * @return returns m_fromDate */ QDate fromDate() const; /** * This method returns the to date set in the filter. If * no value has been set up for this filter, then QDate() is * returned. * * @return returns m_toDate */ QDate toDate() const; /** * This method is used to return information about the * presence of a specific category in the category filter. * The category in question is included in the filter set, * if it has been set or no category filter is set. * * @param cat id of category in question * @return true if category is in filter set, false otherwise */ bool includesCategory(const QString& cat) const; /** * This method is used to return information about the * presence of a specific account in the account filter. * The account in question is included in the filter set, * if it has been set or no account filter is set. * * @param acc id of account in question * @return true if account is in filter set, false otherwise */ bool includesAccount(const QString& acc) const; /** * This method is used to return information about the * presence of a specific payee in the account filter. * The payee in question is included in the filter set, * if it has been set or no account filter is set. * * @param pye id of payee in question * @return true if payee is in filter set, false otherwise */ bool includesPayee(const QString& pye) const; /** * This method is used to return information about the * presence of a specific tag in the account filter. * The tag in question is included in the filter set, * if it has been set or no account filter is set. * * @param tag id of tag in question * @return true if tag is in filter set, false otherwise */ bool includesTag(const QString& tag) const; /** * This method is used to return information about the * presence of a date filter. * * @param from result value for the beginning of the date range * @param to result value for the end of the date range * @return true if a date filter is set */ bool dateFilter(QDate& from, QDate& to) const; /** * This method is used to return information about the * presence of an amount filter. * * @param from result value for the low end of the amount range * @param to result value for the high end of the amount range * @return true if an amount filter is set */ bool amountFilter(MyMoneyMoney& from, MyMoneyMoney& to) const; /** * This method is used to return information about the * presence of an number filter. * * @param from result value for the low end of the number range * @param to result value for the high end of the number range * @return true if a number filter is set */ bool numberFilter(QString& from, QString& to) const; /** * This method returns whether a payee filter has been set, * and if so, it returns all the payees set in the filter. * * @param list list to append payees into * @return return true if a payee filter has been set */ bool payees(QStringList& list) const; /** * This method returns whether a tag filter has been set, * and if so, it returns all the tags set in the filter. * * @param list list to append tags into * @return return true if a tag filter has been set */ bool tags(QStringList& list) const; /** * This method returns whether an account filter has been set, * and if so, it returns all the accounts set in the filter. * * @param list list to append accounts into * @return return true if an account filter has been set */ bool accounts(QStringList& list) const; /** * This method returns whether a category filter has been set, * and if so, it returns all the categories set in the filter. * * @param list list to append categories into * @return return true if a category filter has been set */ bool categories(QStringList& list) const; /** * This method returns whether a type filter has been set, * and if so, it returns the first type in the filter. * * @param i int to replace with first type filter, untouched otherwise * @return return true if a type filter has been set */ bool firstType(int& i) const; bool types(QList& list) const; /** * This method returns whether a state filter has been set, * and if so, it returns the first state in the filter. * * @param i reference to int to replace with first state filter, untouched otherwise * @return return true if a state filter has been set */ bool firstState(int& i) const; bool states(QList& list) const; /** * This method returns whether a validity filter has been set, * and if so, it returns the first validity in the filter. * * @param i reference to int to replace with first validity filter, untouched otherwise * @return return true if a validity filter has been set */ bool firstValidity(int& i) const; bool validities(QList& list) const; /** * This method returns whether a text filter has been set, * and if so, it returns the text filter. * * @param text regexp to replace with text filter, or blank if none set * @return return true if a text filter has been set */ bool textFilter(QRegExp& text) const; /** * This method returns whether the text filter should return * that DO NOT contain the text */ bool isInvertingText() const; /** * This method returns whether transfers should be treated as * income/expense transactions or not */ bool treatTransfersAsIncomeExpense() const; /** * This method translates a plain-language date range into QDate * start & end * * @param range Plain-language range of dates, e.g. 'CurrentYear' * @param start QDate will be set to corresponding to the first date in @p range * @param end QDate will be set to corresponding to the last date in @p range * @return return true if a range was successfully set, or false if @p range was invalid */ static bool translateDateRange(eMyMoney::TransactionFilter::Date range, QDate& start, QDate& end); static void setFiscalYearStart(int firstMonth, int firstDay); FilterSet filterSet() const; /** * This member removes all references to object identified by @p id. Used * to remove objects which are about to be removed from the engine. */ void removeReference(const QString& id); private: /** * This is a conversion tool from eMyMoney::Split::State * to MyMoneyTransactionFilter::stateE types * * @param split reference to split in question * * @return converted reconcile flag of the split passed as parameter */ int splitState(const MyMoneySplit& split) const; /** * This is a conversion tool from MyMoneySplit::action * to MyMoneyTransactionFilter::typeE types * * @param t reference to transaction * @param split reference to split in question * * @return converted action of the split passed as parameter */ int splitType(const MyMoneyTransaction& t, const MyMoneySplit& split, const MyMoneyAccount &acc) const; /** * This method checks if a transaction is valid or not. A transaction * is considered valid, if the sum of all splits is zero, invalid otherwise. * * @param transaction reference to transaction to be checked * @retval valid transaction is valid * @retval invalid transaction is invalid */ eMyMoney::TransactionFilter::Validity validTransaction(const MyMoneyTransaction& transaction) const; }; inline void swap(MyMoneyTransactionFilter& first, MyMoneyTransactionFilter& second) // krazy:exclude=inline { using std::swap; swap(first.d_ptr, second.d_ptr); } inline MyMoneyTransactionFilter::MyMoneyTransactionFilter(MyMoneyTransactionFilter && other) : MyMoneyTransactionFilter() // krazy:exclude=inline { swap(*this, other); } inline MyMoneyTransactionFilter & MyMoneyTransactionFilter::operator=(MyMoneyTransactionFilter other) // krazy:exclude=inline { swap(*this, other); return *this; } /** * Make it possible to hold @ref MyMoneyTransactionFilter objects inside @ref QVariant objects. */ Q_DECLARE_METATYPE(MyMoneyTransactionFilter) +Q_DECLARE_OPERATORS_FOR_FLAGS(MyMoneyTransactionFilter::FilterSet) #endif diff --git a/kmymoney/plugins/sql/mymoneystoragesql.cpp b/kmymoney/plugins/sql/mymoneystoragesql.cpp index 1ba3f66f8..1fa703f3c 100644 --- a/kmymoney/plugins/sql/mymoneystoragesql.cpp +++ b/kmymoney/plugins/sql/mymoneystoragesql.cpp @@ -1,2922 +1,2922 @@ /*************************************************************************** mymoneystoragesql.cpp --------------------- begin : 11 November 2005 copyright : (C) 2005 by Tony Bloomfield email : tonybloom@users.sourceforge.net : Fernando Vilas : Christian Dávid (C) 2017 by Łukasz Wojniłowicz ***************************************************************************/ /*************************************************************************** * * * This program is free software; you can redistribute it and/or modify * * it under the terms of the GNU General Public License as published by * * the Free Software Foundation; either version 2 of the License, or * * (at your option) any later version. * * * ***************************************************************************/ #include "mymoneystoragesql_p.h" // ---------------------------------------------------------------------------- // System Includes // ---------------------------------------------------------------------------- // QT Includes #include // ---------------------------------------------------------------------------- // KDE Includes #include "KMessageBox" // ---------------------------------------------------------------------------- // Project Includes //************************ Constructor/Destructor ***************************** MyMoneyStorageSql::MyMoneyStorageSql(MyMoneyStorageMgr *storage, const QUrl &url) : QSqlDatabase(QUrlQuery(url).queryItemValue("driver")), d_ptr(new MyMoneyStorageSqlPrivate(this)) { Q_D(MyMoneyStorageSql); d->m_storage = storage; } MyMoneyStorageSql::~MyMoneyStorageSql() { try { close(true); } catch (const MyMoneyException &e) { qDebug() << "Caught Exception in MMStorageSql dtor: " << e.what(); } Q_D(MyMoneyStorageSql); delete d; } uint MyMoneyStorageSql::currentVersion() const { Q_D(const MyMoneyStorageSql); return (d->m_db.currentVersion()); } int MyMoneyStorageSql::open(const QUrl &url, int openMode, bool clear) { Q_D(MyMoneyStorageSql); try { int rc = 0; d->m_driver = MyMoneyDbDriver::create(QUrlQuery(url).queryItemValue("driver")); //get the input options QStringList options = QUrlQuery(url).queryItemValue("options").split(','); d->m_loadAll = true; // force loading whole database into memory since unification of storages // options.contains("loadAll")/*|| m_mode == 0*/; d->m_override = options.contains("override"); // create the database connection // regarding the construction of the database name see the discussion on // https://phabricator.kde.org/D12681. In case of a local file based DB // driver we cut off the leading slash only in those cases, where we // a) have a file based DB on Windows systems and // b) have a server based DB. // so that we do not modify the absolute path on *nix based systems // in case of a DB based driver QString dbName = url.path(); if(d->m_driver->requiresExternalFile()) { #ifdef Q_OS_WIN dbName = url.path().remove(0, 1); // remove separator slash for files on Windows #endif } else { dbName = url.path().remove(0, 1); // remove separator slash for server based databases } setDatabaseName(dbName); setHostName(url.host()); setUserName(url.userName()); setPassword(url.password()); if (QUrlQuery(url).queryItemValue("driver").contains("QMYSQL")) { setConnectOptions("MYSQL_OPT_RECONNECT=1"); } QSqlQuery query(*this); switch (openMode) { case QIODevice::ReadOnly: // OpenDatabase menu entry (or open last file) case QIODevice::ReadWrite: // Save menu entry with database open // this may be a sqlite file opened from the recently used list // but which no longer exists. In that case, open will work but create an empty file. // This is not what the user's after; he may accuse KMM of deleting all his data! if (d->m_driver->requiresExternalFile()) { if (!d->fileExists(dbName)) { rc = 1; break; } } if (!QSqlDatabase::open()) { d->buildError(QSqlQuery(*this), Q_FUNC_INFO, "opening database"); rc = 1; } else { if (driverName().compare(QLatin1String("QSQLCIPHER")) == 0) { auto passphrase = password(); while (true) { if (!passphrase.isEmpty()) { query.exec(QString::fromLatin1("PRAGMA cipher_version")); if(!query.next()) throw MYMONEYEXCEPTION_CSTRING("Based on empty cipher_version, libsqlcipher is not in use."); query.exec(QString::fromLatin1("PRAGMA key = '%1'").arg(passphrase)); // SQLCipher feature to decrypt a database } query.exec(QStringLiteral("SELECT count(*) FROM sqlite_master")); // SQLCipher recommended way to check if password is correct if (query.next()) { query.finish(); rc = d->createTables(); // check all tables are present, create if not break; } auto ok = false; passphrase = QInputDialog::getText(nullptr, i18n("Password"), i18n("You're trying to open an encrypted database.\n" "Please provide a password in order to open it."), QLineEdit::Password, QString(), &ok); if (!ok) { QSqlDatabase::close(); throw MYMONEYEXCEPTION_CSTRING("Bad password."); } } } else { rc = d->createTables(); // check all tables are present, create if not } } break; case QIODevice::WriteOnly: // SaveAs Database - if exists, must be empty, if not will create { // Try to open the database. // If that fails, try to create the database, then try to open it again. d->m_newDatabase = true; // QSqlDatabase::open() always returns true on MS Windows // even if SQLite database doesn't exist auto isSQLiteAutocreated = false; if (driverName().compare(QLatin1String("QSQLITE")) == 0 || driverName().compare(QLatin1String("QSQLCIPHER")) == 0) { if (!QFile::exists(dbName)) isSQLiteAutocreated = true; } const auto isSuccessfullyOpened = QSqlDatabase::open(); if (!isSuccessfullyOpened || (isSQLiteAutocreated && isSuccessfullyOpened)) { if (!d->createDatabase(url)) { rc = 1; } else { if (!QSqlDatabase::open()) { d->buildError(QSqlQuery(*this), Q_FUNC_INFO, "opening new database"); rc = 1; } else { query.exec(QString::fromLatin1("PRAGMA key = '%1'").arg(password())); rc = d->createTables(); } } } else { if (driverName().compare(QLatin1String("QSQLCIPHER")) == 0 && !password().isEmpty()) { KMessageBox::information(nullptr, i18n("Overwriting an existing database with an encrypted database is not yet supported.\n" "Please save your database under a new name.")); QSqlDatabase::close(); rc = 3; return rc; } rc = d->createTables(); if (rc == 0) { if (clear) { d->clean(); } else { rc = d->isEmpty(); } } } break; } default: qWarning("%s", qPrintable(QString("%1 - unknown open mode %2").arg(Q_FUNC_INFO).arg(openMode))); } if (rc != 0) return (rc); // bypass logon check if we are creating a database if (d->m_newDatabase) return(0); // check if the database is locked, if not lock it d->readFileInfo(); if (!d->m_logonUser.isEmpty() && (!d->m_override)) { d->m_error = i18n("Database apparently in use\nOpened by %1 on %2 at %3.\nOpen anyway?", d->m_logonUser, d->m_logonAt.date().toString(Qt::ISODate), d->m_logonAt.time().toString("hh.mm.ss")); qDebug("%s", qPrintable(d->m_error)); close(false); rc = -1; // retryable error } else { d->m_logonUser = url.userName() + '@' + url.host(); d->m_logonAt = QDateTime::currentDateTime(); d->writeFileInfo(); } return(rc); } catch (const QString& s) { qDebug("%s", qPrintable(s)); return (1); } } void MyMoneyStorageSql::close(bool logoff) { Q_D(MyMoneyStorageSql); if (QSqlDatabase::isOpen()) { if (logoff) { MyMoneyDbTransaction t(*this, Q_FUNC_INFO); d->m_logonUser.clear(); d->writeFileInfo(); } QSqlDatabase::close(); QSqlDatabase::removeDatabase(connectionName()); } } ulong MyMoneyStorageSql::getRecCount(const QString& table) const { Q_D(const MyMoneyStorageSql); QSqlQuery q(*const_cast (this)); q.prepare(QString("SELECT COUNT(*) FROM %1;").arg(table)); if ((!q.exec()) || (!q.next())) { // krazy:exclude=crashy d->buildError(q, Q_FUNC_INFO, "error retrieving record count"); qFatal("Error retrieving record count"); // definitely shouldn't happen } return ((ulong) q.value(0).toULongLong()); } ////////////////////////////////////////////////////////////////// bool MyMoneyStorageSql::readFile() { Q_D(MyMoneyStorageSql); d->m_displayStatus = true; try { d->readFileInfo(); d->readInstitutions(); if (d->m_loadAll) { readPayees(); } else { QList user; user.append(QString("USER")); readPayees(user); } readTags(); d->readCurrencies(); d->readSecurities(); d->readAccounts(); if (d->m_loadAll) { d->readTransactions(); } else { - if (d->m_preferred.filterSet().singleFilter.accountFilter) readTransactions(d->m_preferred); + if (d->m_preferred.filterSet().testFlag(MyMoneyTransactionFilter::accountFilterActive)) + readTransactions(d->m_preferred); } d->readSchedules(); d->readPrices(); d->readReports(); d->readBudgets(); d->readOnlineJobs(); //FIXME - ?? if (m_mode == 0) //m_storage->rebuildAccountBalances(); // this seems to be nonsense, but it clears the dirty flag // as a side-effect. d->m_storage->setLastModificationDate(d->m_storage->lastModificationDate()); // FIXME?? if (m_mode == 0) m_storage = NULL; // make sure the progress bar is not shown any longer d->signalProgress(-1, -1); d->m_displayStatus = false; //MyMoneySqlQuery::traceOn(); return true; } catch (const QString &) { return false; } // this seems to be nonsense, but it clears the dirty flag // as a side-effect. } // The following is called from 'SaveAsDatabase' bool MyMoneyStorageSql::writeFile() { Q_D(MyMoneyStorageSql); // initialize record counts and hi ids d->m_institutions = d->m_accounts = d->m_payees = d->m_tags = d->m_transactions = d->m_splits = d->m_securities = d->m_prices = d->m_currencies = d->m_schedules = d->m_reports = d->m_kvps = d->m_budgets = 0; d->m_hiIdInstitutions = d->m_hiIdPayees = d->m_hiIdTags = d->m_hiIdAccounts = d->m_hiIdTransactions = d->m_hiIdSchedules = d->m_hiIdSecurities = d->m_hiIdReports = d->m_hiIdBudgets = 0; d->m_onlineJobs = d->m_payeeIdentifier = 0; d->m_displayStatus = true; try { const auto driverName = this->driverName(); if (driverName.compare(QLatin1String("QSQLITE")) == 0 || driverName.compare(QLatin1String("QSQLCIPHER")) == 0) { QSqlQuery query(*this); query.exec("PRAGMA foreign_keys = ON"); // this is needed for "ON UPDATE" and "ON DELETE" to work } MyMoneyDbTransaction t(*this, Q_FUNC_INFO); d->writeInstitutions(); d->writePayees(); d->writeTags(); d->writeAccounts(); d->writeTransactions(); d->writeSchedules(); d->writeSecurities(); d->writePrices(); d->writeCurrencies(); d->writeReports(); d->writeBudgets(); d->writeOnlineJobs(); d->writeFileInfo(); // this seems to be nonsense, but it clears the dirty flag // as a side-effect. //m_storage->setLastModificationDate(m_storage->lastModificationDate()); // FIXME?? if (m_mode == 0) m_storage = NULL; // make sure the progress bar is not shown any longer d->signalProgress(-1, -1); d->m_displayStatus = false; // this seems to be nonsense, but it clears the dirty flag // as a side-effect. d->m_storage->setLastModificationDate(d->m_storage->lastModificationDate()); return true; } catch (const QString &) { return false; } } QString MyMoneyStorageSql::lastError() const { Q_D(const MyMoneyStorageSql); return d->m_error; } // --------------- SQL Transaction (commit unit) handling ----------------------------------- void MyMoneyStorageSql::startCommitUnit(const QString& callingFunction) { Q_D(MyMoneyStorageSql); if (d->m_commitUnitStack.isEmpty()) { if (!transaction()) throw MYMONEYEXCEPTION(d->buildError(QSqlQuery(), callingFunction, "starting commit unit")); } d->m_commitUnitStack.push(callingFunction); } bool MyMoneyStorageSql::endCommitUnit(const QString& callingFunction) { Q_D(MyMoneyStorageSql); // for now, we don't know if there were any changes made to the data so // we expect the data to have changed. This assumption causes some unnecessary // repaints of the UI here and there, but for now it's ok. If we can determine // that the commit() really changes the data, we can return that information // as value of this method. bool rc = true; if (d->m_commitUnitStack.isEmpty()) { throw MYMONEYEXCEPTION_CSTRING("Empty commit unit stack while trying to commit"); } if (callingFunction != d->m_commitUnitStack.top()) qDebug("%s", qPrintable(QString("%1 - %2 s/be %3").arg(Q_FUNC_INFO).arg(callingFunction).arg(d->m_commitUnitStack.top()))); d->m_commitUnitStack.pop(); if (d->m_commitUnitStack.isEmpty()) { //qDebug() << "Committing with " << QSqlQuery::refCount() << " queries"; if (!commit()) throw MYMONEYEXCEPTION(d->buildError(QSqlQuery(), callingFunction, "ending commit unit")); } return rc; } void MyMoneyStorageSql::cancelCommitUnit(const QString& callingFunction) { Q_D(MyMoneyStorageSql); if (d->m_commitUnitStack.isEmpty()) return; if (callingFunction != d->m_commitUnitStack.top()) qDebug("%s", qPrintable(QString("%1 - %2 s/be %3").arg(Q_FUNC_INFO).arg(callingFunction).arg(d->m_commitUnitStack.top()))); d->m_commitUnitStack.clear(); if (!rollback()) throw MYMONEYEXCEPTION(d->buildError(QSqlQuery(), callingFunction, "cancelling commit unit") + ' ' + callingFunction); } ///////////////////////////////////////////////////////////////////// void MyMoneyStorageSql::fillStorage() { Q_D(MyMoneyStorageSql); // if (!m_transactionListRead) // make sure we have loaded everything d->readTransactions(); // if (!m_payeeListRead) readPayees(); } //------------------------------ Write SQL routines ---------------------------------------- // **** Institutions **** void MyMoneyStorageSql::addInstitution(const MyMoneyInstitution& inst) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmInstitutions"].insertString()); QList iList; iList << inst; d->writeInstitutionList(iList , q); ++d->m_institutions; d->writeFileInfo(); } void MyMoneyStorageSql::modifyInstitution(const MyMoneyInstitution& inst) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmInstitutions"].updateString()); QVariantList kvpList; kvpList << inst.id(); d->deleteKeyValuePairs("OFXSETTINGS", kvpList); QList iList; iList << inst; d->writeInstitutionList(iList , q); d->writeFileInfo(); } void MyMoneyStorageSql::removeInstitution(const MyMoneyInstitution& inst) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QVariantList kvpList; kvpList << inst.id(); d->deleteKeyValuePairs("OFXSETTINGS", kvpList); QSqlQuery query(*this); query.prepare(d->m_db.m_tables["kmmInstitutions"].deleteString()); query.bindValue(":id", inst.id()); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Institution")); // krazy:exclude=crashy --d->m_institutions; d->writeFileInfo(); } void MyMoneyStorageSql::addPayee(const MyMoneyPayee& payee) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery query(*this); query.prepare(d->m_db.m_tables["kmmPayees"].insertString()); d->writePayee(payee, query); ++d->m_payees; QVariantList identIds; QList idents = payee.payeeIdentifiers(); // Store ids which have to be stored in the map table identIds.reserve(idents.count()); foreach (payeeIdentifier ident, idents) { try { // note: this changes ident addPayeeIdentifier(ident); identIds.append(ident.idString()); } catch (const payeeIdentifier::empty &) { } } if (!identIds.isEmpty()) { // Create lists for batch processing QVariantList order; QVariantList payeeIdList; order.reserve(identIds.size()); payeeIdList.reserve(identIds.size()); for (int i = 0; i < identIds.size(); ++i) { order << i; payeeIdList << payee.id(); } query.prepare("INSERT INTO kmmPayeesPayeeIdentifier (payeeId, identifierId, userOrder) VALUES(?, ?, ?)"); query.bindValue(0, payeeIdList); query.bindValue(1, identIds); query.bindValue(2, order); if (!query.execBatch()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("writing payee's identifiers")); // krazy:exclude=crashy } d->writeFileInfo(); } void MyMoneyStorageSql::modifyPayee(MyMoneyPayee payee) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery query(*this); query.prepare(d->m_db.m_tables["kmmPayees"].updateString()); d->writePayee(payee, query); // Get a list of old identifiers first query.prepare("SELECT identifierId FROM kmmPayeesPayeeIdentifier WHERE payeeId = ?"); query.bindValue(0, payee.id()); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("modifying payee's identifiers (getting old values failed)")); // krazy:exclude=crashy QStringList oldIdentIds; oldIdentIds.reserve(query.numRowsAffected()); while (query.next()) oldIdentIds << query.value(0).toString(); // Add new and modify old payeeIdentifiers foreach (payeeIdentifier ident, payee.payeeIdentifiers()) { if (ident.idString().isEmpty()) { payeeIdentifier oldIdent(ident); addPayeeIdentifier(ident); // addPayeeIdentifier could fail (throws an exception then) only remove old // identifier if new one is stored correctly payee.removePayeeIdentifier(oldIdent); payee.addPayeeIdentifier(ident); } else { modifyPayeeIdentifier(ident); payee.modifyPayeeIdentifier(ident); oldIdentIds.removeAll(ident.idString()); } } // Remove identifiers which are not used anymore foreach (QString idToRemove, oldIdentIds) { payeeIdentifier ident(fetchPayeeIdentifier(idToRemove)); removePayeeIdentifier(ident); } // Update relation table query.prepare("DELETE FROM kmmPayeesPayeeIdentifier WHERE payeeId = ?"); query.bindValue(0, payee.id()); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("modifying payee's identifiers (delete from mapping table)")); // krazy:exclude=crashy // Get list again because modifiyPayeeIdentifier which is used above may change the id QList idents(payee.payeeIdentifiers()); QVariantList order; QVariantList payeeIdList; QVariantList identIdList; order.reserve(idents.size()); payeeIdList.reserve(idents.size()); identIdList.reserve(idents.size()); { QList::const_iterator end = idents.constEnd(); int i = 0; for (QList::const_iterator iter = idents.constBegin(); iter != end; ++iter, ++i) { order << i; payeeIdList << payee.id(); identIdList << iter->idString(); } } query.prepare("INSERT INTO kmmPayeesPayeeIdentifier (payeeId, userOrder, identifierId) VALUES(?, ?, ?)"); query.bindValue(0, payeeIdList); query.bindValue(1, order); query.bindValue(2, identIdList); if (!query.execBatch()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("writing payee's identifiers during modify")); // krazy:exclude=crashy d->writeFileInfo(); } void MyMoneyStorageSql::modifyUserInfo(const MyMoneyPayee& payee) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmPayees"].updateString()); d->writePayee(payee, q, true); d->writeFileInfo(); } void MyMoneyStorageSql::removePayee(const MyMoneyPayee& payee) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery query(*this); // Get identifiers first so we know which to delete query.prepare("SELECT identifierId FROM kmmPayeesPayeeIdentifier WHERE payeeId = ?"); query.bindValue(0, payee.id()); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("removing payee's identifiers (getting old values failed)")); // krazy:exclude=crashy QStringList identIds; while (query.next()) identIds << query.value(0).toString(); QMap idents = fetchPayeeIdentifiers(identIds); foreach (payeeIdentifier ident, idents) { removePayeeIdentifier(ident); } // Delete entries from mapping table query.prepare("DELETE FROM kmmPayeesPayeeIdentifier WHERE payeeId = ?"); query.bindValue(0, payee.id()); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("removing payee's identifiers (delete from mapping table)")); // krazy:exclude=crashy // Delete payee query.prepare(d->m_db.m_tables["kmmPayees"].deleteString()); query.bindValue(":id", payee.id()); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Payee")); // krazy:exclude=crashy --d->m_payees; d->writeFileInfo(); } // **** Tags **** void MyMoneyStorageSql::addTag(const MyMoneyTag& tag) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmTags"].insertString()); d->writeTag(tag, q); ++d->m_tags; d->writeFileInfo(); } void MyMoneyStorageSql::modifyTag(const MyMoneyTag& tag) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmTags"].updateString()); d->writeTag(tag, q); d->writeFileInfo(); } void MyMoneyStorageSql::removeTag(const MyMoneyTag& tag) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery query(*this); query.prepare(d->m_db.m_tables["kmmTags"].deleteString()); query.bindValue(":id", tag.id()); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Tag")); // krazy:exclude=crashy --d->m_tags; d->writeFileInfo(); } // **** Accounts **** void MyMoneyStorageSql::addAccount(const MyMoneyAccount& acc) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmAccounts"].insertString()); QList aList; aList << acc; d->writeAccountList(aList, q); ++d->m_accounts; d->writeFileInfo(); } void MyMoneyStorageSql::modifyAccount(const MyMoneyAccount& acc) { QList aList; aList << acc; modifyAccountList(aList); } void MyMoneyStorageSql::modifyAccountList(const QList& acc) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmAccounts"].updateString()); QVariantList kvpList; foreach (const MyMoneyAccount& a, acc) { kvpList << a.id(); } d->deleteKeyValuePairs("ACCOUNT", kvpList); d->deleteKeyValuePairs("ONLINEBANKING", kvpList); d->writeAccountList(acc, q); d->writeFileInfo(); } void MyMoneyStorageSql::removeAccount(const MyMoneyAccount& acc) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QVariantList kvpList; kvpList << acc.id(); d->deleteKeyValuePairs("ACCOUNT", kvpList); d->deleteKeyValuePairs("ONLINEBANKING", kvpList); QSqlQuery query(*this); query.prepare(d->m_db.m_tables["kmmAccounts"].deleteString()); query.bindValue(":id", acc.id()); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Account")); // krazy:exclude=crashy --d->m_accounts; d->writeFileInfo(); } // **** Transactions and Splits **** void MyMoneyStorageSql::addTransaction(const MyMoneyTransaction& tx) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); // add the transaction and splits QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmTransactions"].insertString()); d->writeTransaction(tx.id(), tx, q, "N"); ++d->m_transactions; QList aList; // for each split account, update lastMod date, balance, txCount foreach (const MyMoneySplit& it_s, tx.splits()) { MyMoneyAccount acc = d->m_storage->account(it_s.accountId()); ++d->m_transactionCountMap[acc.id()]; aList << acc; } modifyAccountList(aList); // in the fileinfo record, update lastMod, txCount, next TxId d->writeFileInfo(); } void MyMoneyStorageSql::modifyTransaction(const MyMoneyTransaction& tx) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); // remove the splits of the old tx from the count table QSqlQuery query(*this); query.prepare("SELECT accountId FROM kmmSplits WHERE transactionId = :txId;"); query.bindValue(":txId", tx.id()); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("retrieving old splits")); while (query.next()) { QString id = query.value(0).toString(); --d->m_transactionCountMap[id]; } // add the transaction and splits query.prepare(d->m_db.m_tables["kmmTransactions"].updateString()); d->writeTransaction(tx.id(), tx, query, "N"); QList aList; // for each split account, update lastMod date, balance, txCount foreach (const MyMoneySplit& it_s, tx.splits()) { MyMoneyAccount acc = d->m_storage->account(it_s.accountId()); ++d->m_transactionCountMap[acc.id()]; aList << acc; } modifyAccountList(aList); //writeSplits(tx.id(), "N", tx.splits()); // in the fileinfo record, update lastMod d->writeFileInfo(); } void MyMoneyStorageSql::removeTransaction(const MyMoneyTransaction& tx) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); d->deleteTransaction(tx.id()); --d->m_transactions; QList aList; // for each split account, update lastMod date, balance, txCount foreach (const MyMoneySplit& it_s, tx.splits()) { MyMoneyAccount acc = d->m_storage->account(it_s.accountId()); --d->m_transactionCountMap[acc.id()]; aList << acc; } modifyAccountList(aList); // in the fileinfo record, update lastModDate, txCount d->writeFileInfo(); } // **** Schedules **** void MyMoneyStorageSql::addSchedule(const MyMoneySchedule& sched) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmSchedules"].insertString()); d->writeSchedule(sched, q, true); ++d->m_schedules; d->writeFileInfo(); } void MyMoneyStorageSql::modifySchedule(const MyMoneySchedule& sched) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmSchedules"].updateString()); d->writeSchedule(sched, q, false); d->writeFileInfo(); } void MyMoneyStorageSql::removeSchedule(const MyMoneySchedule& sched) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); d->deleteSchedule(sched.id()); --d->m_schedules; d->writeFileInfo(); } // **** Securities **** void MyMoneyStorageSql::addSecurity(const MyMoneySecurity& sec) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmSecurities"].insertString()); d->writeSecurity(sec, q); ++d->m_securities; d->writeFileInfo(); } void MyMoneyStorageSql::modifySecurity(const MyMoneySecurity& sec) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QVariantList kvpList; kvpList << sec.id(); d->deleteKeyValuePairs("SECURITY", kvpList); QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmSecurities"].updateString()); d->writeSecurity(sec, q); d->writeFileInfo(); } void MyMoneyStorageSql::removeSecurity(const MyMoneySecurity& sec) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QVariantList kvpList; kvpList << sec.id(); d->deleteKeyValuePairs("SECURITY", kvpList); QSqlQuery query(*this); query.prepare(d->m_db.m_tables["kmmSecurities"].deleteString()); query.bindValue(":id", kvpList); if (!query.execBatch()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Security")); --d->m_securities; d->writeFileInfo(); } // **** Prices **** void MyMoneyStorageSql::addPrice(const MyMoneyPrice& p) { Q_D(MyMoneyStorageSql); if (d->m_readingPrices) return; // the app always calls addPrice, whether or not there is already one there MyMoneyDbTransaction t(*this, Q_FUNC_INFO); bool newRecord = false; QSqlQuery query(*this); QString s = d->m_db.m_tables["kmmPrices"].selectAllString(false); s += " WHERE fromId = :fromId AND toId = :toId AND priceDate = :priceDate;"; query.prepare(s); query.bindValue(":fromId", p.from()); query.bindValue(":toId", p.to()); query.bindValue(":priceDate", p.date().toString(Qt::ISODate)); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("finding Price")); // krazy:exclude=crashy if (query.next()) { query.prepare(d->m_db.m_tables["kmmPrices"].updateString()); } else { query.prepare(d->m_db.m_tables["kmmPrices"].insertString()); ++d->m_prices; newRecord = true; } query.bindValue(":fromId", p.from()); query.bindValue(":toId", p.to()); query.bindValue(":priceDate", p.date().toString(Qt::ISODate)); query.bindValue(":price", p.rate(QString()).toString()); const MyMoneySecurity sec = d->m_storage->security(p.to()); query.bindValue(":priceFormatted", p.rate(QString()).formatMoney("", sec.pricePrecision())); query.bindValue(":priceSource", p.source()); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("writing Price")); // krazy:exclude=crashy if (newRecord) d->writeFileInfo(); } void MyMoneyStorageSql::removePrice(const MyMoneyPrice& p) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery query(*this); query.prepare(d->m_db.m_tables["kmmPrices"].deleteString()); query.bindValue(":fromId", p.from()); query.bindValue(":toId", p.to()); query.bindValue(":priceDate", p.date().toString(Qt::ISODate)); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Price")); // krazy:exclude=crashy --d->m_prices; d->writeFileInfo(); } // **** Currencies **** void MyMoneyStorageSql::addCurrency(const MyMoneySecurity& sec) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmCurrencies"].insertString()); d->writeCurrency(sec, q); ++d->m_currencies; d->writeFileInfo(); } void MyMoneyStorageSql::modifyCurrency(const MyMoneySecurity& sec) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmCurrencies"].updateString()); d->writeCurrency(sec, q); d->writeFileInfo(); } void MyMoneyStorageSql::removeCurrency(const MyMoneySecurity& sec) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery query(*this); query.prepare(d->m_db.m_tables["kmmCurrencies"].deleteString()); query.bindValue(":ISOcode", sec.id()); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Currency")); // krazy:exclude=crashy --d->m_currencies; d->writeFileInfo(); } void MyMoneyStorageSql::addReport(const MyMoneyReport& rep) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmReportConfig"].insertString()); d->writeReport(rep, q); ++d->m_reports; d->writeFileInfo(); } void MyMoneyStorageSql::modifyReport(const MyMoneyReport& rep) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmReportConfig"].updateString()); d->writeReport(rep, q); d->writeFileInfo(); } void MyMoneyStorageSql::removeReport(const MyMoneyReport& rep) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery query(*this); query.prepare("DELETE FROM kmmReportConfig WHERE id = :id"); query.bindValue(":id", rep.id()); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Report")); // krazy:exclude=crashy --d->m_reports; d->writeFileInfo(); } void MyMoneyStorageSql::addBudget(const MyMoneyBudget& bud) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmBudgetConfig"].insertString()); d->writeBudget(bud, q); ++d->m_budgets; d->writeFileInfo(); } void MyMoneyStorageSql::modifyBudget(const MyMoneyBudget& bud) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery q(*this); q.prepare(d->m_db.m_tables["kmmBudgetConfig"].updateString()); d->writeBudget(bud, q); d->writeFileInfo(); } void MyMoneyStorageSql::removeBudget(const MyMoneyBudget& bud) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery query(*this); query.prepare(d->m_db.m_tables["kmmBudgetConfig"].deleteString()); query.bindValue(":id", bud.id()); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Budget")); // krazy:exclude=crashy --d->m_budgets; d->writeFileInfo(); } void MyMoneyStorageSql::addOnlineJob(const onlineJob& job) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery query(*this); query.prepare("INSERT INTO kmmOnlineJobs (id, type, jobSend, bankAnswerDate, state, locked) VALUES(:id, :type, :jobSend, :bankAnswerDate, :state, :locked);"); d->writeOnlineJob(job, query); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("writing onlineJob")); // krazy:exclude=crashy ++d->m_onlineJobs; try { // Save online task d->actOnOnlineJobInSQL(MyMoneyStorageSqlPrivate::SQLAction::Save, *job.constTask(), job.id()); } catch (onlineJob::emptyTask&) { } } void MyMoneyStorageSql::modifyOnlineJob(const onlineJob& job) { Q_D(MyMoneyStorageSql); Q_ASSERT(!job.id().isEmpty()); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery query(*this); query.prepare(QLatin1String( "UPDATE kmmOnlineJobs SET " " type = :type, " " jobSend = :jobSend, " " bankAnswerDate = :bankAnswerDate, " " state = :state, " " locked = :locked " " WHERE id = :id" )); d->writeOnlineJob(job, query); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("writing onlineJob")); // krazy:exclude=crashy try { // Modify online task d->actOnOnlineJobInSQL(MyMoneyStorageSqlPrivate::SQLAction::Modify, *job.constTask(), job.id()); } catch (onlineJob::emptyTask&) { // If there is no task attached this is fine as well } } void MyMoneyStorageSql::removeOnlineJob(const onlineJob& job) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); // Remove onlineTask first, because it could have a constraint // which could block the removal of the onlineJob try { // Remove task d->actOnOnlineJobInSQL(MyMoneyStorageSqlPrivate::SQLAction::Remove, *job.constTask(), job.id()); } catch (onlineJob::emptyTask&) { } QSqlQuery query(*this); query.prepare(d->m_db.m_tables["kmmOnlineJobs"].deleteString()); query.bindValue(":id", job.id()); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting onlineJob")); // krazy:exclude=crashy --d->m_onlineJobs; } void MyMoneyStorageSql::addPayeeIdentifier(payeeIdentifier& ident) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); ident = payeeIdentifier(incrementPayeeIdentfierId(), ident); QSqlQuery q(*this); q.prepare("INSERT INTO kmmPayeeIdentifier (id, type) VALUES(:id, :type)"); d->writePayeeIdentifier(ident, q); ++d->m_payeeIdentifier; try { d->actOnPayeeIdentifierObjectInSQL(MyMoneyStorageSqlPrivate::SQLAction::Save, ident); } catch (const payeeIdentifier::empty &) { } } void MyMoneyStorageSql::modifyPayeeIdentifier(const payeeIdentifier& ident) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); QSqlQuery query(*this); query.prepare("SELECT type FROM kmmPayeeIdentifier WHERE id = ?"); query.bindValue(0, ident.idString()); if (!query.exec() || !query.next()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("modifying payeeIdentifier")); // krazy:exclude=crashy bool typeChanged = (query.value(0).toString() != ident.iid()); if (typeChanged) { // Delete old identifier if type changed const payeeIdentifier oldIdent(fetchPayeeIdentifier(ident.idString())); try { d->actOnPayeeIdentifierObjectInSQL(MyMoneyStorageSqlPrivate::SQLAction::Modify, oldIdent); } catch (const payeeIdentifier::empty &) { // Note: this should not happen because the ui does not offer a way to change // the type of an payeeIdentifier if it was not correctly loaded. throw MYMONEYEXCEPTION((QString::fromLatin1("Could not modify payeeIdentifier '") + ident.idString() + QLatin1String("' because type changed and could not remove identifier of old type. Maybe a plugin is missing?")) ); // krazy:exclude=crashy } } query.prepare("UPDATE kmmPayeeIdentifier SET type = :type WHERE id = :id"); d->writePayeeIdentifier(ident, query); try { if (typeChanged) d->actOnPayeeIdentifierObjectInSQL(MyMoneyStorageSqlPrivate::SQLAction::Save, ident); else d->actOnPayeeIdentifierObjectInSQL(MyMoneyStorageSqlPrivate::SQLAction::Modify, ident); } catch (const payeeIdentifier::empty &) { } } void MyMoneyStorageSql::removePayeeIdentifier(const payeeIdentifier& ident) { Q_D(MyMoneyStorageSql); MyMoneyDbTransaction t(*this, Q_FUNC_INFO); // Remove first, the table could have a contraint which prevents removal // of row in kmmPayeeIdentifier try { d->actOnPayeeIdentifierObjectInSQL(MyMoneyStorageSqlPrivate::SQLAction::Remove, ident); } catch (const payeeIdentifier::empty &) { } QSqlQuery query(*this); query.prepare(d->m_db.m_tables["kmmPayeeIdentifier"].deleteString()); query.bindValue(":id", ident.idString()); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting payeeIdentifier")); // krazy:exclude=crashy --d->m_payeeIdentifier; } // **** Key/value pairs **** //******************************** read SQL routines ************************************** /*void MyMoneyStorageSql::setVersion (const QString& version) { m_dbVersion = version.section('.', 0, 0).toUInt(); m_minorVersion = version.section('.', 1, 1).toUInt(); // Okay, I made a cockup by forgetting to include a fixversion in the database // design, so we'll use the minor version as fix level (similar to VERSION // and FIXVERSION in XML file format). A second mistake was setting minor version to 1 // in the first place, so we need to subtract one on reading and add one on writing (sigh)!! m_storage->setFileFixVersion( m_minorVersion - 1); }*/ QMap MyMoneyStorageSql::fetchInstitutions(const QStringList& idList, bool forUpdate) const { Q_D(const MyMoneyStorageSql); int institutionsNb = (idList.isEmpty() ? d->m_institutions : idList.size()); d->signalProgress(0, institutionsNb, QObject::tr("Loading institutions...")); int progress = 0; QMap iList; ulong lastId = 0; const MyMoneyDbTable& t = d->m_db.m_tables["kmmInstitutions"]; QSqlQuery sq(*const_cast (this)); sq.prepare("SELECT id FROM kmmAccounts WHERE institutionId = :id"); QSqlQuery query(*const_cast (this)); QString queryString(t.selectAllString(false)); // Use bind variables, instead of just inserting the values in the queryString, // so that values containing a ':' will work. if (! idList.empty()) { queryString += " WHERE"; for (int i = 0; i < idList.count(); ++i) queryString += QString(" id = :id%1 OR").arg(i); queryString = queryString.left(queryString.length() - 2); } if (forUpdate) queryString += d->m_driver->forUpdateString(); queryString += ';'; query.prepare(queryString); if (! idList.empty()) { QStringList::ConstIterator bindVal = idList.constBegin(); for (int i = 0; bindVal != idList.constEnd(); ++i, ++bindVal) { query.bindValue(QString(":id%1").arg(i), *bindVal); } } if (!query.exec()) throw MYMONEYEXCEPTION(d->buildError(query, Q_FUNC_INFO, QString::fromLatin1("reading Institution"))); // krazy:exclude=crashy int idCol = t.fieldNumber("id"); int nameCol = t.fieldNumber("name"); int managerCol = t.fieldNumber("manager"); int routingCodeCol = t.fieldNumber("routingCode"); int addressStreetCol = t.fieldNumber("addressStreet"); int addressCityCol = t.fieldNumber("addressCity"); int addressZipcodeCol = t.fieldNumber("addressZipcode"); int telephoneCol = t.fieldNumber("telephone"); QStringList kvpInstitutionList(idList); while (query.next()) { MyMoneyInstitution inst; QString iid = GETSTRING(idCol); inst.setName(GETSTRING(nameCol)); inst.setManager(GETSTRING(managerCol)); inst.setSortcode(GETSTRING(routingCodeCol)); inst.setStreet(GETSTRING(addressStreetCol)); inst.setCity(GETSTRING(addressCityCol)); inst.setPostcode(GETSTRING(addressZipcodeCol)); inst.setTelephone(GETSTRING(telephoneCol)); if (idList.isEmpty()) { kvpInstitutionList.append(iid); } // get list of subaccounts sq.bindValue(":id", iid); if (!sq.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading Institution AccountList")); // krazy:exclude=crashy QStringList aList; while (sq.next()) aList.append(sq.value(0).toString()); foreach (const QString& it, aList) inst.addAccountId(it); iList[iid] = MyMoneyInstitution(iid, inst); ulong id = MyMoneyUtils::extractId(iid); if (id > lastId) lastId = id; d->signalProgress(++progress, 0); } auto kvpResult = d->readKeyValuePairs("INSTITUTION", kvpInstitutionList); // old versions used to store the KVP with the false key (OFXSETTINGS) // but we load it nevertheless in case the new one is empty. During the // next write to the database, the key value pairs will be written with the // right id (INSTITUTION). auto kvpResultOld = d->readKeyValuePairs("OFXSETTINGS", kvpInstitutionList); if (kvpResult.isEmpty()) { kvpResult = kvpResultOld; } const auto kvp_end = kvpResult.constEnd(); for (auto it_kvp = kvpResult.constBegin(); it_kvp != kvp_end; ++it_kvp) { iList[it_kvp.key()].setPairs(it_kvp.value().pairs()); } return iList; } QMap MyMoneyStorageSql::fetchInstitutions() const { return fetchInstitutions(QStringList(), false); } void MyMoneyStorageSql::readPayees(const QString& id) { QList list; list.append(id); readPayees(list); } void MyMoneyStorageSql::readPayees(const QList& pid) { Q_D(MyMoneyStorageSql); try { d->m_storage->loadPayees(fetchPayees(pid)); } catch (const MyMoneyException &) { } // if (pid.isEmpty()) m_payeeListRead = true; } void MyMoneyStorageSql::readPayees() { readPayees(QList()); } QMap MyMoneyStorageSql::fetchPayees(const QStringList& idList, bool /*forUpdate*/) const { Q_D(const MyMoneyStorageSql); MyMoneyDbTransaction trans(const_cast (*this), Q_FUNC_INFO); if (d->m_displayStatus) { int payeesNb = (idList.isEmpty() ? d->m_payees : idList.size()); d->signalProgress(0, payeesNb, QObject::tr("Loading payees...")); } int progress = 0; QMap pList; QSqlQuery query(*const_cast (this)); QString queryString = QLatin1String("SELECT kmmPayees.id AS id, kmmPayees.name AS name, kmmPayees.reference AS reference, " " kmmPayees.email AS email, kmmPayees.addressStreet AS addressStreet, kmmPayees.addressCity AS addressCity, kmmPayees.addressZipcode AS addressZipcode, " " kmmPayees.addressState AS addressState, kmmPayees.telephone AS telephone, kmmPayees.notes AS notes, " " kmmPayees.defaultAccountId AS defaultAccountId, kmmPayees.matchData AS matchData, kmmPayees.matchIgnoreCase AS matchIgnoreCase, " " kmmPayees.matchKeys AS matchKeys, " " kmmPayeesPayeeIdentifier.identifierId AS identId " " FROM ( SELECT * FROM kmmPayees "); if (!idList.isEmpty()) { // Create WHERE clause if needed queryString += QLatin1String(" WHERE id IN ("); queryString += QString("?, ").repeated(idList.length()); queryString.chop(2); // remove ", " from end queryString += QLatin1Char(')'); } queryString += QLatin1String( " ) kmmPayees " " LEFT OUTER JOIN kmmPayeesPayeeIdentifier ON kmmPayees.Id = kmmPayeesPayeeIdentifier.payeeId " // The order is used below " ORDER BY kmmPayees.id, kmmPayeesPayeeIdentifier.userOrder;"); query.prepare(queryString); if (!idList.isEmpty()) { // Bind values QStringList::const_iterator end = idList.constEnd(); for (QStringList::const_iterator iter = idList.constBegin(); iter != end; ++iter) { query.addBindValue(*iter); } } if (!query.exec()) throw MYMONEYEXCEPTION(d->buildError(query, Q_FUNC_INFO, QString::fromLatin1("reading Payee"))); // krazy:exclude=crashy const QSqlRecord record = query.record(); const int idCol = record.indexOf("id"); const int nameCol = record.indexOf("name"); const int referenceCol = record.indexOf("reference"); const int emailCol = record.indexOf("email"); const int addressStreetCol = record.indexOf("addressStreet"); const int addressCityCol = record.indexOf("addressCity"); const int addressZipcodeCol = record.indexOf("addressZipcode"); const int addressStateCol = record.indexOf("addressState"); const int telephoneCol = record.indexOf("telephone"); const int notesCol = record.indexOf("notes"); const int defaultAccountIdCol = record.indexOf("defaultAccountId"); const int matchDataCol = record.indexOf("matchData"); const int matchIgnoreCaseCol = record.indexOf("matchIgnoreCase"); const int matchKeysCol = record.indexOf("matchKeys"); const int identIdCol = record.indexOf("identId"); if (query.next()) { while (query.isValid()) { QString pid; MyMoneyPayee payee; uint type; bool ignoreCase; QString matchKeys; pid = GETSTRING(idCol); payee.setName(GETSTRING(nameCol)); payee.setReference(GETSTRING(referenceCol)); payee.setEmail(GETSTRING(emailCol)); payee.setAddress(GETSTRING(addressStreetCol)); payee.setCity(GETSTRING(addressCityCol)); payee.setPostcode(GETSTRING(addressZipcodeCol)); payee.setState(GETSTRING(addressStateCol)); payee.setTelephone(GETSTRING(telephoneCol)); payee.setNotes(GETSTRING(notesCol)); payee.setDefaultAccountId(GETSTRING(defaultAccountIdCol)); type = GETINT(matchDataCol); ignoreCase = (GETSTRING(matchIgnoreCaseCol) == "Y"); matchKeys = GETSTRING(matchKeysCol); payee.setMatchData(static_cast(type), ignoreCase, matchKeys); // Get payeeIdentifier ids QStringList identifierIds; do { identifierIds.append(GETSTRING(identIdCol)); } while (query.next() && GETSTRING(idCol) == pid); // as long as the payeeId is unchanged // Fetch and save payeeIdentifier if (!identifierIds.isEmpty()) { QList< ::payeeIdentifier > identifier = fetchPayeeIdentifiers(identifierIds).values(); payee.resetPayeeIdentifiers(identifier); } if (pid == "USER") d->m_storage->setUser(payee); else pList[pid] = MyMoneyPayee(pid, payee); if (d->m_displayStatus) d->signalProgress(++progress, 0); } } return pList; } QMap MyMoneyStorageSql::fetchPayees() const { return fetchPayees(QStringList(), false); } void MyMoneyStorageSql::readTags(const QString& id) { QList list; list.append(id); readTags(list); } void MyMoneyStorageSql::readTags(const QList& pid) { Q_D(MyMoneyStorageSql); try { d->m_storage->loadTags(fetchTags(pid)); } catch (const MyMoneyException &) { } } void MyMoneyStorageSql::readTags() { readTags(QList()); } QMap MyMoneyStorageSql::fetchOnlineJobs(const QStringList& idList, bool forUpdate) const { Q_D(const MyMoneyStorageSql); Q_UNUSED(forUpdate); MyMoneyDbTransaction trans(const_cast (*this), Q_FUNC_INFO); if (d->m_displayStatus) d->signalProgress(0, idList.isEmpty() ? d->m_onlineJobs : idList.size(), QObject::tr("Loading online banking data...")); // Create query QSqlQuery query(*const_cast (this)); if (idList.isEmpty()) { query.prepare("SELECT id, type, jobSend, bankAnswerDate, state, locked FROM kmmOnlineJobs;"); } else { QString queryIdSet = QString("?, ").repeated(idList.length()); queryIdSet.chop(2); query.prepare(QLatin1String("SELECT id, type, jobSend, bankAnswerDate, state, locked FROM kmmOnlineJobs WHERE id IN (") + queryIdSet + QLatin1String(");")); QStringList::const_iterator end = idList.constEnd(); for (QStringList::const_iterator iter = idList.constBegin(); iter != end; ++iter) { query.addBindValue(*iter); } } if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading onlineJobs")); // krazy:exclude=crashy // Create onlineJobs int progress = 0; QMap jobList; while (query.next()) { const QString& id = query.value(0).toString(); onlineTask *const task = d->createOnlineTaskObject(query.value(1).toString(), id, *this); onlineJob job = onlineJob(task, id); job.setJobSend(query.value(2).toDateTime()); eMyMoney::OnlineJob::sendingState state; const QString stateString = query.value(4).toString(); if (stateString == "acceptedByBank") state = eMyMoney::OnlineJob::sendingState::acceptedByBank; else if (stateString == "rejectedByBank") state = eMyMoney::OnlineJob::sendingState::rejectedByBank; else if (stateString == "abortedByUser") state = eMyMoney::OnlineJob::sendingState::abortedByUser; else if (stateString == "sendingError") state = eMyMoney::OnlineJob::sendingState::sendingError; else // includes: stateString == "noBankAnswer" state = eMyMoney::OnlineJob::sendingState::noBankAnswer; job.setBankAnswer(state, query.value(3).toDateTime()); job.setLock(query.value(5).toString() == QLatin1String("Y") ? true : false); jobList.insert(job.id(), job); if (d->m_displayStatus) d->signalProgress(++progress, 0); } return jobList; } QMap MyMoneyStorageSql::fetchOnlineJobs() const { return fetchOnlineJobs(QStringList(), false); } payeeIdentifier MyMoneyStorageSql::fetchPayeeIdentifier(const QString& id) const { QMap list = fetchPayeeIdentifiers(QStringList(id)); QMap::const_iterator iter = list.constFind(id); if (iter == list.constEnd()) throw MYMONEYEXCEPTION(QString::fromLatin1("payeeIdentifier with id '%1' not found").arg(id)); // krazy:exclude=crashy return *iter; } QMap< QString, payeeIdentifier > MyMoneyStorageSql::fetchPayeeIdentifiers(const QStringList& idList) const { Q_D(const MyMoneyStorageSql); MyMoneyDbTransaction trans(const_cast (*this), Q_FUNC_INFO); // Create query QSqlQuery query(*const_cast (this)); if (idList.isEmpty()) { query.prepare("SELECT id, type FROM kmmPayeeIdentifier;"); } else { QString queryIdSet = QString("?, ").repeated(idList.length()); queryIdSet.chop(2); // remove ", " from end query.prepare(QLatin1String("SELECT id, type FROM kmmPayeeIdentifier WHERE id IN (") + queryIdSet + QLatin1String(");")); QStringList::const_iterator end = idList.constEnd(); for (QStringList::const_iterator iter = idList.constBegin(); iter != end; ++iter) { query.addBindValue(*iter); } } if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading payee identifiers")); // krazy:exclude=crashy QMap identList; while (query.next()) { const auto id = query.value(0).toString(); identList.insert(id, d->createPayeeIdentifierObject(*this, query.value(1).toString(), id)); } return identList; } QMap< QString, payeeIdentifier > MyMoneyStorageSql::fetchPayeeIdentifiers() const { return fetchPayeeIdentifiers(QStringList()); } QMap MyMoneyStorageSql::fetchTags(const QStringList& idList, bool /*forUpdate*/) const { Q_D(const MyMoneyStorageSql); MyMoneyDbTransaction trans(const_cast (*this), Q_FUNC_INFO); if (d->m_displayStatus) { int tagsNb = (idList.isEmpty() ? d->m_tags : idList.size()); d->signalProgress(0, tagsNb, QObject::tr("Loading tags...")); } else { // if (m_tagListRead) return; } int progress = 0; QMap taList; //ulong lastId; const MyMoneyDbTable& t = d->m_db.m_tables["kmmTags"]; QSqlQuery query(*const_cast (this)); if (idList.isEmpty()) { query.prepare(t.selectAllString()); } else { QString whereClause = " where ("; QString itemConnector = ""; foreach (const QString& it, idList) { whereClause.append(QString("%1id = '%2'").arg(itemConnector).arg(it)); itemConnector = " or "; } whereClause += ')'; query.prepare(t.selectAllString(false) + whereClause); } if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading Tag")); // krazy:exclude=crashy int idCol = t.fieldNumber("id"); int nameCol = t.fieldNumber("name"); int notesCol = t.fieldNumber("notes"); int tagColorCol = t.fieldNumber("tagColor"); int closedCol = t.fieldNumber("closed"); while (query.next()) { QString pid; MyMoneyTag tag; pid = GETSTRING(idCol); tag.setName(GETSTRING(nameCol)); tag.setNotes(GETSTRING(notesCol)); tag.setClosed((GETSTRING(closedCol) == "Y")); tag.setTagColor(QColor(GETSTRING(tagColorCol))); taList[pid] = MyMoneyTag(pid, tag); if (d->m_displayStatus) d->signalProgress(++progress, 0); } return taList; } QMap MyMoneyStorageSql::fetchTags() const { return fetchTags(QStringList(), false); } QMap MyMoneyStorageSql::fetchAccounts(const QStringList& idList, bool forUpdate) const { Q_D(const MyMoneyStorageSql); int accountsNb = (idList.isEmpty() ? d->m_accounts : idList.size()); d->signalProgress(0, accountsNb, QObject::tr("Loading accounts...")); int progress = 0; QMap accList; QStringList kvpAccountList(idList); const MyMoneyDbTable& t = d->m_db.m_tables["kmmAccounts"]; QSqlQuery query(*const_cast (this)); QSqlQuery sq(*const_cast (this)); QString childQueryString = "SELECT id, parentId FROM kmmAccounts WHERE "; QString queryString(t.selectAllString(false)); // Use bind variables, instead of just inserting the values in the queryString, // so that values containing a ':' will work. if (! idList.empty()) { queryString += " WHERE id IN ("; childQueryString += " parentId IN ("; QString inString; for (int i = 0; i < idList.count(); ++i) { inString += QString(":id%1, ").arg(i); } inString = inString.left(inString.length() - 2) + ')'; queryString += inString; childQueryString += inString; } else { childQueryString += " NOT parentId IS NULL"; } queryString += " ORDER BY id"; childQueryString += " ORDER BY parentid, id"; if (forUpdate) { queryString += d->m_driver->forUpdateString(); childQueryString += d->m_driver->forUpdateString(); } query.prepare(queryString); sq.prepare(childQueryString); if (! idList.empty()) { QStringList::ConstIterator bindVal = idList.constBegin(); for (int i = 0; bindVal != idList.constEnd(); ++i, ++bindVal) { query.bindValue(QString(":id%1").arg(i), *bindVal); sq.bindValue(QString(":id%1").arg(i), *bindVal); } } if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading Account")); // krazy:exclude=crashy if (!sq.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading subAccountList")); // krazy:exclude=crashy // Reserve enough space for all values. Approximate it with the size of the // idList in case the db doesn't support reporting the size of the // resultset to the caller. //FIXME: this is for if/when there is a QHash conversion //accList.reserve(q.size() > 0 ? q.size() : idList.size()); static const int idCol = t.fieldNumber("id"); static const int institutionIdCol = t.fieldNumber("institutionId"); static const int parentIdCol = t.fieldNumber("parentId"); static const int lastReconciledCol = t.fieldNumber("lastReconciled"); static const int lastModifiedCol = t.fieldNumber("lastModified"); static const int openingDateCol = t.fieldNumber("openingDate"); static const int accountNumberCol = t.fieldNumber("accountNumber"); static const int accountTypeCol = t.fieldNumber("accountType"); static const int accountNameCol = t.fieldNumber("accountName"); static const int descriptionCol = t.fieldNumber("description"); static const int currencyIdCol = t.fieldNumber("currencyId"); static const int balanceCol = t.fieldNumber("balance"); static const int transactionCountCol = t.fieldNumber("transactionCount"); while (query.next()) { QString aid; MyMoneyAccount acc; aid = GETSTRING(idCol); acc.setInstitutionId(GETSTRING(institutionIdCol)); acc.setParentAccountId(GETSTRING(parentIdCol)); acc.setLastReconciliationDate(GETDATE_D(lastReconciledCol)); acc.setLastModified(GETDATE_D(lastModifiedCol)); acc.setOpeningDate(GETDATE_D(openingDateCol)); acc.setNumber(GETSTRING(accountNumberCol)); acc.setAccountType(static_cast(GETINT(accountTypeCol))); acc.setName(GETSTRING(accountNameCol)); acc.setDescription(GETSTRING(descriptionCol)); acc.setCurrencyId(GETSTRING(currencyIdCol)); acc.setBalance(MyMoneyMoney(GETSTRING(balanceCol))); const_cast (this)->d_func()->m_transactionCountMap[aid] = (ulong) GETULL(transactionCountCol); // Process any key value pair if (idList.empty()) kvpAccountList.append(aid); accList.insert(aid, MyMoneyAccount(aid, acc)); if (acc.value("PreferredAccount") == "Yes") { const_cast (this)->d_func()->m_preferred.addAccount(aid); } d->signalProgress(++progress, 0); } QMap::Iterator it_acc; QMap::Iterator accListEnd = accList.end(); while (sq.next()) { it_acc = accList.find(sq.value(1).toString()); if (it_acc != accListEnd && it_acc.value().id() == sq.value(1).toString()) { while (sq.isValid() && it_acc != accListEnd && it_acc.value().id() == sq.value(1).toString()) { it_acc.value().addAccountId(sq.value(0).toString()); if (!sq.next()) break; } sq.previous(); } } //TODO: There should be a better way than this. What's below is O(n log n) or more, // where it may be able to be done in O(n), if things are just right. // The operator[] call in the loop is the most expensive call in this function, according // to several profile runs. QHash kvpResult = d->readKeyValuePairs("ACCOUNT", kvpAccountList); QHash ::const_iterator kvp_end = kvpResult.constEnd(); for (QHash ::const_iterator it_kvp = kvpResult.constBegin(); it_kvp != kvp_end; ++it_kvp) { accList[it_kvp.key()].setPairs(it_kvp.value().pairs()); } kvpResult = d->readKeyValuePairs("ONLINEBANKING", kvpAccountList); kvp_end = kvpResult.constEnd(); for (QHash ::const_iterator it_kvp = kvpResult.constBegin(); it_kvp != kvp_end; ++it_kvp) { accList[it_kvp.key()].setOnlineBankingSettings(it_kvp.value()); } return accList; } QMap MyMoneyStorageSql::fetchAccounts() const { return fetchAccounts(QStringList(), false); } QMap MyMoneyStorageSql::fetchBalance(const QStringList& idList, const QDate& date) const { Q_D(const MyMoneyStorageSql); QMap returnValue; QSqlQuery query(*const_cast (this)); QString queryString = "SELECT action, shares, accountId, postDate " "FROM kmmSplits WHERE txType = 'N'"; if (idList.count() > 0) { queryString += "AND accountId in ("; for (int i = 0; i < idList.count(); ++i) { queryString += QString(":id%1, ").arg(i); } queryString = queryString.left(queryString.length() - 2) + ')'; } // SQLite stores dates as YYYY-MM-DDTHH:mm:ss with 0s for the time part. This makes // the <= operator misbehave when the date matches. To avoid this, add a day to the // requested date and use the < operator. if (date.isValid() && !date.isNull()) queryString += QString(" AND postDate < '%1'").arg(date.addDays(1).toString(Qt::ISODate)); queryString += " ORDER BY accountId, postDate;"; //DBG(queryString); query.prepare(queryString); int i = 0; foreach (const QString& bindVal, idList) { query.bindValue(QString(":id%1").arg(i), bindVal); ++i; } if (!query.exec()) // krazy:exclude=crashy throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("fetching balance")); QString id; QString oldId; MyMoneyMoney temp; while (query.next()) { id = query.value(2).toString(); // If the old ID does not match the new ID, then the account being summed has changed. // Write the balance into the returnValue map and update the oldId to the current one. if (id != oldId) { if (!oldId.isEmpty()) { returnValue.insert(oldId, temp); temp = 0; } oldId = id; } if (MyMoneySplit::actionName(eMyMoney::Split::Action::SplitShares) == query.value(0).toString()) temp *= MyMoneyMoney(query.value(1).toString()); else temp += MyMoneyMoney(query.value(1).toString()); } // Do not forget the last id in the list. returnValue.insert(id, temp); // Return the map. return returnValue; } void MyMoneyStorageSql::readTransactions(const MyMoneyTransactionFilter& filter) { Q_D(MyMoneyStorageSql); try { d->m_storage->loadTransactions(fetchTransactions(filter)); } catch (const MyMoneyException &) { throw; } } QMap MyMoneyStorageSql::fetchTransactions(const QString& tidList, const QString& dateClause, bool /*forUpdate*/) const { Q_D(const MyMoneyStorageSql); // if (m_transactionListRead) return; // all list already in memory if (d->m_displayStatus) { int transactionsNb = (tidList.isEmpty() ? d->m_transactions : tidList.size()); d->signalProgress(0, transactionsNb, QObject::tr("Loading transactions...")); } int progress = 0; // m_payeeList.clear(); QString whereClause = " WHERE txType = 'N' "; if (! tidList.isEmpty()) { whereClause += " AND id IN " + tidList; } if (!dateClause.isEmpty()) whereClause += " AND " + dateClause; const MyMoneyDbTable& t = d->m_db.m_tables["kmmTransactions"]; QSqlQuery query(*const_cast (this)); query.prepare(t.selectAllString(false) + whereClause + " ORDER BY id;"); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading Transaction")); // krazy:exclude=crashy const MyMoneyDbTable& ts = d->m_db.m_tables["kmmSplits"]; whereClause = " WHERE txType = 'N' "; if (! tidList.isEmpty()) { whereClause += " AND transactionId IN " + tidList; } if (!dateClause.isEmpty()) whereClause += " AND " + dateClause; QSqlQuery qs(*const_cast (this)); QString splitQuery = ts.selectAllString(false) + whereClause + " ORDER BY transactionId, splitId;"; qs.prepare(splitQuery); if (!qs.exec()) throw MYMONEYEXCEPTION(d->buildError(qs, Q_FUNC_INFO, "reading Splits")); // krazy:exclude=crashy QString splitTxId = "ZZZ"; MyMoneySplit s; if (qs.next()) { splitTxId = qs.value(0).toString(); s = d->readSplit(qs); } else { splitTxId = "ZZZ"; } QMap txMap; QStringList txList; int idCol = t.fieldNumber("id"); int postDateCol = t.fieldNumber("postDate"); int memoCol = t.fieldNumber("memo"); int entryDateCol = t.fieldNumber("entryDate"); int currencyIdCol = t.fieldNumber("currencyId"); int bankIdCol = t.fieldNumber("bankId"); while (query.next()) { MyMoneyTransaction tx; QString txId = GETSTRING(idCol); tx.setPostDate(GETDATE_D(postDateCol)); tx.setMemo(GETSTRING(memoCol)); tx.setEntryDate(GETDATE_D(entryDateCol)); tx.setCommodity(GETSTRING(currencyIdCol)); tx.setBankID(GETSTRING(bankIdCol)); // skip all splits while the transaction id of the split is less than // the transaction id of the current transaction. Don't forget to check // for the ZZZ flag for the end of the list. while (txId < splitTxId && splitTxId != "ZZZ") { if (qs.next()) { splitTxId = qs.value(0).toString(); s = d->readSplit(qs); } else { splitTxId = "ZZZ"; } } // while the split transaction id matches the current transaction id, // add the split to the current transaction. Set the ZZZ flag if // all splits for this transaction have been read. while (txId == splitTxId) { tx.addSplit(s); if (qs.next()) { splitTxId = qs.value(0).toString(); s = d->readSplit(qs); } else { splitTxId = "ZZZ"; } } // Process any key value pair if (! txId.isEmpty()) { txList.append(txId); tx = MyMoneyTransaction(txId, tx); txMap.insert(tx.uniqueSortKey(), tx); } } // get the kvps QHash kvpMap = d->readKeyValuePairs("TRANSACTION", txList); QMap::Iterator txMapEnd = txMap.end(); for (QMap::Iterator i = txMap.begin(); i != txMapEnd; ++i) { i.value().setPairs(kvpMap[i.value().id()].pairs()); if (d->m_displayStatus) d->signalProgress(++progress, 0); } if ((tidList.isEmpty()) && (dateClause.isEmpty())) { //qDebug("setting full list read"); } return txMap; } QMap MyMoneyStorageSql::fetchTransactions(const QString& tidList) const { return fetchTransactions(tidList, QString(), false); } QMap MyMoneyStorageSql::fetchTransactions() const { return fetchTransactions(QString(), QString(), false); } QMap MyMoneyStorageSql::fetchTransactions(const MyMoneyTransactionFilter& filter) const { Q_D(const MyMoneyStorageSql); // analyze the filter // if (m_transactionListRead) return; // all list already in memory // if the filter is restricted to certain accounts/categories // check if we already have them all in memory QStringList accounts; QString inQuery; filter.accounts(accounts); filter.categories(accounts); // QStringList::iterator it; // bool allAccountsLoaded = true; // for (it = accounts.begin(); it != accounts.end(); ++it) { // if (m_accountsLoaded.find(*it) == m_accountsLoaded.end()) { // allAccountsLoaded = false; // break; // } // } // if (allAccountsLoaded) return; /* Some filter combinations do not lend themselves to implementation * in SQL, or are likely to require such extensive reading of the database * as to make it easier to just read everything into memory. */ bool canImplementFilter = true; MyMoneyMoney m1, m2; if (filter.amountFilter(m1, m2)) { d->alert("Amount Filter Set"); canImplementFilter = false; } QString n1, n2; if (filter.numberFilter(n1, n2)) { d->alert("Number filter set"); canImplementFilter = false; } int t1; if (filter.firstType(t1)) { d->alert("Type filter set"); canImplementFilter = false; } // int s1; // if (filter.firstState(s1)) { // alert("State filter set"); // canImplementFilter = false; // } QRegExp t2; if (filter.textFilter(t2)) { d->alert("text filter set"); canImplementFilter = false; } - MyMoneyTransactionFilter::FilterSet s = filter.filterSet(); - if (s.singleFilter.validityFilter) { + if (filter.filterSet().testFlag(MyMoneyTransactionFilter::validityFilterActive)) { d->alert("Validity filter set"); canImplementFilter = false; } if (!canImplementFilter) { QMap transactionList = fetchTransactions(); std::remove_if(transactionList.begin(), transactionList.end(), FilterFail(filter)); return transactionList; } bool splitFilterActive = false; // the split filter is active if we are selecting on fields in the split table // get start and end dates QDate start = filter.fromDate(); QDate end = filter.toDate(); // not entirely sure if the following is correct, but at best, saves a lot of reads, at worst // it only causes us to read a few more transactions that strictly necessary (I think...) if (start == MyMoneyStorageSqlPrivate::m_startDate) start = QDate(); bool txFilterActive = ((start != QDate()) || (end != QDate())); // and this for fields in the transaction table QString whereClause = ""; QString subClauseconnector = " WHERE txType = 'N' AND "; // payees QStringList payees; if (filter.payees(payees)) { QString itemConnector = "payeeId IN ("; QString payeesClause = ""; foreach (const QString& it, payees) { payeesClause.append(QString("%1'%2'") .arg(itemConnector).arg(it)); itemConnector = ", "; } if (!payeesClause.isEmpty()) { whereClause += subClauseconnector + payeesClause + ')'; subClauseconnector = " AND "; } splitFilterActive = true; } //tags QStringList tags; if (filter.tags(tags)) { QString itemConnector = "splitId IN ( SELECT splitId FROM kmmTagSplits WHERE kmmTagSplits.transactionId = kmmSplits.transactionId AND tagId IN ("; QString tagsClause = ""; foreach (const QString& it, tags) { tagsClause.append(QString("%1'%2'") .arg(itemConnector).arg(it)); itemConnector = ", "; } if (!tagsClause.isEmpty()) { whereClause += subClauseconnector + tagsClause + ')'; subClauseconnector = " AND "; } splitFilterActive = true; } // accounts and categories if (!accounts.isEmpty()) { splitFilterActive = true; QString itemConnector = "accountId IN ("; QString accountsClause = ""; foreach (const QString& it, accounts) { accountsClause.append(QString("%1 '%2'") .arg(itemConnector).arg(it)); itemConnector = ", "; } if (!accountsClause.isEmpty()) { whereClause += subClauseconnector + accountsClause + ')'; subClauseconnector = " AND ("; } } // split states QList splitStates; if (filter.states(splitStates)) { splitFilterActive = true; QString itemConnector = " reconcileFlag IN ("; QString statesClause = ""; foreach (int it, splitStates) { statesClause.append(QString(" %1 '%2'").arg(itemConnector) .arg(d->splitState(TransactionFilter::State(it)))); itemConnector = ','; } if (!statesClause.isEmpty()) { whereClause += subClauseconnector + statesClause + ')'; subClauseconnector = " AND ("; } } // I've given up trying to work out the logic. we keep getting the wrong number of close brackets int obc = whereClause.count('('); int cbc = whereClause.count(')'); if (cbc > obc) { qDebug() << "invalid where clause " << whereClause; qFatal("aborting"); } while (cbc < obc) { whereClause.append(')'); cbc++; } // if the split filter is active, but the where clause and the date filter is empty // it means we already have all the transactions for the specified filter // in memory, so just exit if ((splitFilterActive) && (whereClause.isEmpty()) && (!txFilterActive)) { qDebug("all transactions already in storage"); return fetchTransactions(); } // if we have neither a split filter, nor a tx (date) filter // it's effectively a read all if ((!splitFilterActive) && (!txFilterActive)) { //qDebug("reading all transactions"); return fetchTransactions(); } // build a date clause for the transaction table QString dateClause; QString connector = ""; if (end != QDate()) { dateClause = QString("(postDate < '%1')").arg(end.addDays(1).toString(Qt::ISODate)); connector = " AND "; } if (start != QDate()) { dateClause += QString("%1 (postDate >= '%2')").arg(connector).arg(start.toString(Qt::ISODate)); } // now get a list of transaction ids // if we have only a date filter, we need to build the list from the tx table // otherwise we need to build from the split table if (splitFilterActive) { inQuery = QString("(SELECT DISTINCT transactionId FROM kmmSplits %1)").arg(whereClause); } else { inQuery = QString("(SELECT DISTINCT id FROM kmmTransactions WHERE %1)").arg(dateClause); txFilterActive = false; // kill off the date filter now } return fetchTransactions(inQuery, dateClause); //FIXME: if we have an accounts-only filter, recalc balances on loaded accounts } ulong MyMoneyStorageSql::transactionCount(const QString& aid) const { Q_D(const MyMoneyStorageSql); if (aid.isEmpty()) return d->m_transactions; else return d->m_transactionCountMap[aid]; } QHash MyMoneyStorageSql::transactionCountMap() const { Q_D(const MyMoneyStorageSql); return d->m_transactionCountMap; } bool MyMoneyStorageSql::isReferencedByTransaction(const QString& id) const { Q_D(const MyMoneyStorageSql); //FIXME-ALEX should I add sub query for kmmTagSplits here? QSqlQuery q(*const_cast (this)); q.prepare("SELECT COUNT(*) FROM kmmTransactions " "INNER JOIN kmmSplits ON kmmTransactions.id = kmmSplits.transactionId " "WHERE kmmTransactions.currencyId = :ID OR kmmSplits.payeeId = :ID " "OR kmmSplits.accountId = :ID OR kmmSplits.costCenterId = :ID"); q.bindValue(":ID", id); if ((!q.exec()) || (!q.next())) { // krazy:exclude=crashy d->buildError(q, Q_FUNC_INFO, "error retrieving reference count"); qFatal("Error retrieving reference count"); // definitely shouldn't happen } return (0 != q.value(0).toULongLong()); } QMap MyMoneyStorageSql::fetchSchedules(const QStringList& idList, bool forUpdate) const { Q_D(const MyMoneyStorageSql); int schedulesNb = (idList.isEmpty() ? d->m_schedules : idList.size()); d->signalProgress(0, schedulesNb, QObject::tr("Loading schedules...")); int progress = 0; const MyMoneyDbTable& t = d->m_db.m_tables["kmmSchedules"]; QSqlQuery query(*const_cast (this)); QMap sList; //ulong lastId = 0; const MyMoneyDbTable& ts = d->m_db.m_tables["kmmSplits"]; QSqlQuery qs(*const_cast (this)); qs.prepare(ts.selectAllString(false) + " WHERE transactionId = :id ORDER BY splitId;"); QSqlQuery sq(*const_cast (this)); sq.prepare("SELECT payDate FROM kmmSchedulePaymentHistory WHERE schedId = :id"); QString queryString(t.selectAllString(false)); // Use bind variables, instead of just inserting the values in the queryString, // so that values containing a ':' will work. if (! idList.empty()) { queryString += " WHERE"; for (int i = 0; i < idList.count(); ++i) queryString += QString(" id = :id%1 OR").arg(i); queryString = queryString.left(queryString.length() - 2); } queryString += " ORDER BY id"; if (forUpdate) queryString += d->m_driver->forUpdateString(); query.prepare(queryString); if (! idList.empty()) { QStringList::ConstIterator bindVal = idList.constBegin(); for (int i = 0; bindVal != idList.constEnd(); ++i, ++bindVal) { query.bindValue(QString(":id%1").arg(i), *bindVal); } } if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading Schedules")); // krazy:exclude=crashy int idCol = t.fieldNumber("id"); int nameCol = t.fieldNumber("name"); int typeCol = t.fieldNumber("type"); int occurrenceCol = t.fieldNumber("occurence"); // krazy:exclude=spelling int occurrenceMultiplierCol = t.fieldNumber("occurenceMultiplier"); // krazy:exclude=spelling int paymentTypeCol = t.fieldNumber("paymentType"); int startDateCol = t.fieldNumber("startDate"); int endDateCol = t.fieldNumber("endDate"); int fixedCol = t.fieldNumber("fixed"); int lastDayInMonthCol = t.fieldNumber("lastDayInMonth"); int autoEnterCol = t.fieldNumber("autoEnter"); int lastPaymentCol = t.fieldNumber("lastPayment"); int weekendOptionCol = t.fieldNumber("weekendOption"); int nextPaymentDueCol = t.fieldNumber("nextPaymentDue"); while (query.next()) { MyMoneySchedule s; QString boolChar; QString sId = GETSTRING(idCol); s.setName(GETSTRING(nameCol)); s.setType(static_cast(GETINT(typeCol))); s.setOccurrencePeriod(static_cast(GETINT(occurrenceCol))); s.setOccurrenceMultiplier(GETINT(occurrenceMultiplierCol)); s.setPaymentType(static_cast(GETINT(paymentTypeCol))); s.setStartDate(GETDATE_D(startDateCol)); s.setEndDate(GETDATE_D(endDateCol)); boolChar = GETSTRING(fixedCol); s.setFixed(boolChar == "Y"); boolChar = GETSTRING(lastDayInMonthCol); s.setLastDayInMonth(boolChar == "Y"); boolChar = GETSTRING(autoEnterCol); s.setAutoEnter(boolChar == "Y"); s.setLastPayment(GETDATE_D(lastPaymentCol)); s.setWeekendOption(static_cast(GETINT(weekendOptionCol))); QDate nextPaymentDue = GETDATE_D(nextPaymentDueCol); // convert simple occurrence to compound occurrence int mult = s.occurrenceMultiplier(); Schedule::Occurrence occ = s.occurrence(); MyMoneySchedule::simpleToCompoundOccurrence(mult, occ); s.setOccurrencePeriod(occ); s.setOccurrenceMultiplier(mult); // now assign the id to the schedule MyMoneySchedule _s(sId, s); s = _s; // read the associated transaction // m_payeeList.clear(); const MyMoneyDbTable& transactionTable = d->m_db.m_tables["kmmTransactions"]; QSqlQuery q(*const_cast (this)); q.prepare(transactionTable.selectAllString(false) + " WHERE id = :id;"); q.bindValue(":id", s.id()); if (!q.exec()) throw MYMONEYEXCEPTION(d->buildError(q, Q_FUNC_INFO, QString("reading Scheduled Transaction"))); // krazy:exclude=crashy QSqlRecord rec = q.record(); if (!q.next()) throw MYMONEYEXCEPTION(d->buildError(q, Q_FUNC_INFO, QString("retrieving scheduled transaction"))); MyMoneyTransaction tx(s.id(), MyMoneyTransaction()); // we cannot use the GET.... macros here as they are bound to the query variable tx.setPostDate(d->getDate(q.value(transactionTable.fieldNumber("postDate")).toString())); tx.setMemo(q.value(transactionTable.fieldNumber("memo")).toString()); tx.setEntryDate(d->getDate(q.value(transactionTable.fieldNumber("entryDate")).toString())); tx.setCommodity(q.value(transactionTable.fieldNumber("currencyId")).toString()); tx.setBankID(q.value(transactionTable.fieldNumber("bankId")).toString()); qs.bindValue(":id", s.id()); if (!qs.exec()) throw MYMONEYEXCEPTION(d->buildError(qs, Q_FUNC_INFO, "reading Scheduled Splits")); // krazy:exclude=crashy while (qs.next()) { MyMoneySplit sp(d->readSplit(qs)); tx.addSplit(sp); } // if (!m_payeeList.isEmpty()) // readPayees(m_payeeList); // Process any key value pair tx.setPairs(d->readKeyValuePairs("TRANSACTION", s.id()).pairs()); // If the transaction doesn't have a post date, setTransaction will reject it. // The old way of handling things was to store the next post date in the schedule object // and set the transaction post date to QDate(). // For compatibility, if this is the case, copy the next post date from the schedule object // to the transaction object post date. if (!tx.postDate().isValid()) { tx.setPostDate(nextPaymentDue); } s.setTransaction(tx); // read in the recorded payments sq.bindValue(":id", s.id()); if (!sq.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading schedule payment history")); // krazy:exclude=crashy while (sq.next()) s.recordPayment(sq.value(0).toDate()); sList[s.id()] = s; //FIXME: enable when schedules have KVPs. // s.setPairs(readKeyValuePairs("SCHEDULE", s.id()).pairs()); //ulong id = MyMoneyUtils::extractId(s.id().data()); //if(id > lastId) // lastId = id; d->signalProgress(++progress, 0); } return sList; } QMap MyMoneyStorageSql::fetchSchedules() const { return fetchSchedules(QStringList(), false); } QMap MyMoneyStorageSql::fetchSecurities(const QStringList& /*idList*/, bool /*forUpdate*/) const { Q_D(const MyMoneyStorageSql); d->signalProgress(0, d->m_securities, QObject::tr("Loading securities...")); int progress = 0; QMap sList; ulong lastId = 0; const MyMoneyDbTable& t = d->m_db.m_tables["kmmSecurities"]; QSqlQuery query(*const_cast (this)); query.prepare(t.selectAllString(false) + " ORDER BY id;"); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading Securities")); // krazy:exclude=crashy int idCol = t.fieldNumber("id"); int nameCol = t.fieldNumber("name"); int symbolCol = t.fieldNumber("symbol"); int typeCol = t.fieldNumber("type"); int roundingMethodCol = t.fieldNumber("roundingMethod"); int smallestAccountFractionCol = t.fieldNumber("smallestAccountFraction"); int pricePrecisionCol = t.fieldNumber("pricePrecision"); int tradingCurrencyCol = t.fieldNumber("tradingCurrency"); int tradingMarketCol = t.fieldNumber("tradingMarket"); while (query.next()) { MyMoneySecurity e; QString eid; eid = GETSTRING(idCol); e.setName(GETSTRING(nameCol)); e.setTradingSymbol(GETSTRING(symbolCol)); e.setSecurityType(static_cast(GETINT(typeCol))); e.setRoundingMethod(static_cast(GETINT(roundingMethodCol))); int saf = GETINT(smallestAccountFractionCol); int pp = GETINT(pricePrecisionCol); e.setTradingCurrency(GETSTRING(tradingCurrencyCol)); e.setTradingMarket(GETSTRING(tradingMarketCol)); if (e.tradingCurrency().isEmpty()) e.setTradingCurrency(d->m_storage->pairs()["kmm-baseCurrency"]); if (saf == 0) saf = 100; if (pp == 0 || pp > 10) pp = 4; e.setSmallestAccountFraction(saf); e.setPricePrecision(pp); // Process any key value pairs e.setPairs(d->readKeyValuePairs("SECURITY", eid).pairs()); //tell the storage objects we have a new security object. // FIXME: Adapt to new interface make sure, to take care of the currencies as well // see MyMoneyStorageXML::readSecurites() MyMoneySecurity security(eid, e); sList[security.id()] = security; ulong id = MyMoneyUtils::extractId(security.id()); if (id > lastId) lastId = id; d->signalProgress(++progress, 0); } return sList; } QMap MyMoneyStorageSql::fetchSecurities() const { return fetchSecurities(QStringList(), false); } MyMoneyPrice MyMoneyStorageSql::fetchSinglePrice(const QString& fromId, const QString& toId, const QDate& date_, bool exactDate, bool /*forUpdate*/) const { Q_D(const MyMoneyStorageSql); const MyMoneyDbTable& t = d->m_db.m_tables["kmmPrices"]; static const int priceDateCol = t.fieldNumber("priceDate"); static const int priceCol = t.fieldNumber("price"); static const int priceSourceCol = t.fieldNumber("priceSource"); QSqlQuery query(*const_cast (this)); // Use bind variables, instead of just inserting the values in the queryString, // so that values containing a ':' will work. // See balance query for why the date logic seems odd. QString queryString = t.selectAllString(false) + " WHERE fromId = :fromId AND toId = :toId AND priceDate < :priceDate "; if (exactDate) queryString += "AND priceDate > :exactDate "; queryString += "ORDER BY priceDate DESC;"; query.prepare(queryString); QDate date(date_); if (!date.isValid()) date = QDate::currentDate(); query.bindValue(":fromId", fromId); query.bindValue(":toId", toId); query.bindValue(":priceDate", date.addDays(1).toString(Qt::ISODate)); if (exactDate) query.bindValue(":exactDate", date.toString(Qt::ISODate)); if (! query.exec()) return MyMoneyPrice(); // krazy:exclude=crashy if (query.next()) { return MyMoneyPrice(fromId, toId, GETDATE_D(priceDateCol), MyMoneyMoney(GETSTRING(priceCol)), GETSTRING(priceSourceCol)); } return MyMoneyPrice(); } MyMoneyPriceList MyMoneyStorageSql::fetchPrices(const QStringList& fromIdList, const QStringList& toIdList, bool forUpdate) const { Q_D(const MyMoneyStorageSql); int pricesNb = (fromIdList.isEmpty() ? d->m_prices : fromIdList.size()); d->signalProgress(0, pricesNb, QObject::tr("Loading prices...")); int progress = 0; const_cast (this)->d_func()->m_readingPrices = true; MyMoneyPriceList pList; const MyMoneyDbTable& t = d->m_db.m_tables["kmmPrices"]; QSqlQuery query(*const_cast (this)); QString queryString = t.selectAllString(false); // Use bind variables, instead of just inserting the values in the queryString, // so that values containing a ':' will work. if (! fromIdList.empty()) { queryString += " WHERE ("; for (int i = 0; i < fromIdList.count(); ++i) { queryString += QString(" fromId = :fromId%1 OR").arg(i); } queryString = queryString.left(queryString.length() - 2) + ')'; } if (! toIdList.empty()) { queryString += " AND ("; for (int i = 0; i < toIdList.count(); ++i) { queryString += QString(" toId = :toId%1 OR").arg(i); } queryString = queryString.left(queryString.length() - 2) + ')'; } if (forUpdate) queryString += d->m_driver->forUpdateString(); queryString += ';'; query.prepare(queryString); if (! fromIdList.empty()) { QStringList::ConstIterator bindVal = fromIdList.constBegin(); for (int i = 0; bindVal != fromIdList.constEnd(); ++i, ++bindVal) { query.bindValue(QString(":fromId%1").arg(i), *bindVal); } } if (! toIdList.empty()) { QStringList::ConstIterator bindVal = toIdList.constBegin(); for (int i = 0; bindVal != toIdList.constEnd(); ++i, ++bindVal) { query.bindValue(QString(":toId%1").arg(i), *bindVal); } } if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading Prices")); // krazy:exclude=crashy static const int fromIdCol = t.fieldNumber("fromId"); static const int toIdCol = t.fieldNumber("toId"); static const int priceDateCol = t.fieldNumber("priceDate"); static const int priceCol = t.fieldNumber("price"); static const int priceSourceCol = t.fieldNumber("priceSource"); while (query.next()) { QString from = GETSTRING(fromIdCol); QString to = GETSTRING(toIdCol); QDate date = GETDATE_D(priceDateCol); pList [MyMoneySecurityPair(from, to)].insert(date, MyMoneyPrice(from, to, date, MyMoneyMoney(GETSTRING(priceCol)), GETSTRING(priceSourceCol))); d->signalProgress(++progress, 0); } const_cast (this)->d_func()->m_readingPrices = false; return pList; } MyMoneyPriceList MyMoneyStorageSql::fetchPrices() const { return fetchPrices(QStringList(), QStringList(), false); } QMap MyMoneyStorageSql::fetchCurrencies(const QStringList& idList, bool forUpdate) const { Q_D(const MyMoneyStorageSql); int currenciesNb = (idList.isEmpty() ? d->m_currencies : idList.size()); d->signalProgress(0, currenciesNb, QObject::tr("Loading currencies...")); int progress = 0; QMap cList; const MyMoneyDbTable& t = d->m_db.m_tables["kmmCurrencies"]; QSqlQuery query(*const_cast (this)); QString queryString(t.selectAllString(false)); // Use bind variables, instead of just inserting the values in the queryString, // so that values containing a ':' will work. if (! idList.empty()) { queryString += " WHERE"; for (int i = 0; i < idList.count(); ++i) queryString += QString(" isocode = :id%1 OR").arg(i); queryString = queryString.left(queryString.length() - 2); } queryString += " ORDER BY ISOcode"; if (forUpdate) queryString += d->m_driver->forUpdateString(); queryString += ';'; query.prepare(queryString); if (! idList.empty()) { QStringList::ConstIterator bindVal = idList.constBegin(); for (int i = 0; bindVal != idList.end(); ++i, ++bindVal) { query.bindValue(QString(":id%1").arg(i), *bindVal); } } if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading Currencies")); // krazy:exclude=crashy int ISOcodeCol = t.fieldNumber("ISOcode"); int nameCol = t.fieldNumber("name"); int typeCol = t.fieldNumber("type"); int symbol1Col = t.fieldNumber("symbol1"); int symbol2Col = t.fieldNumber("symbol2"); int symbol3Col = t.fieldNumber("symbol3"); int smallestCashFractionCol = t.fieldNumber("smallestCashFraction"); int smallestAccountFractionCol = t.fieldNumber("smallestAccountFraction"); int pricePrecisionCol = t.fieldNumber("pricePrecision"); while (query.next()) { QString id; MyMoneySecurity c; QChar symbol[3]; id = GETSTRING(ISOcodeCol); c.setName(GETSTRING(nameCol)); c.setSecurityType(static_cast(GETINT(typeCol))); symbol[0] = QChar(GETINT(symbol1Col)); symbol[1] = QChar(GETINT(symbol2Col)); symbol[2] = QChar(GETINT(symbol3Col)); c.setSmallestCashFraction(GETINT(smallestCashFractionCol)); c.setSmallestAccountFraction(GETINT(smallestAccountFractionCol)); c.setPricePrecision(GETINT(pricePrecisionCol)); c.setTradingSymbol(QString(symbol, 3).trimmed()); cList[id] = MyMoneySecurity(id, c); d->signalProgress(++progress, 0); } return cList; } QMap MyMoneyStorageSql::fetchCurrencies() const { return fetchCurrencies(QStringList(), false); } QMap MyMoneyStorageSql::fetchReports(const QStringList& /*idList*/, bool /*forUpdate*/) const { Q_D(const MyMoneyStorageSql); d->signalProgress(0, d->m_reports, QObject::tr("Loading reports...")); int progress = 0; const MyMoneyDbTable& t = d->m_db.m_tables["kmmReportConfig"]; QSqlQuery query(*const_cast (this)); query.prepare(t.selectAllString(true)); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading reports")); // krazy:exclude=crashy int xmlCol = t.fieldNumber("XML"); QMap rList; while (query.next()) { QDomDocument dom; dom.setContent(GETSTRING(xmlCol), false); QDomNode child = dom.firstChild(); child = child.firstChild(); auto report = MyMoneyXmlContentHandler2::readReport(child.toElement()); rList[report.id()] = report; d->signalProgress(++progress, 0); } return rList; } QMap MyMoneyStorageSql::fetchReports() const { return fetchReports(QStringList(), false); } QMap MyMoneyStorageSql::fetchBudgets(const QStringList& idList, bool forUpdate) const { Q_D(const MyMoneyStorageSql); int budgetsNb = (idList.isEmpty() ? d->m_budgets : idList.size()); d->signalProgress(0, budgetsNb, QObject::tr("Loading budgets...")); int progress = 0; const MyMoneyDbTable& t = d->m_db.m_tables["kmmBudgetConfig"]; QSqlQuery query(*const_cast (this)); QString queryString(t.selectAllString(false)); if (! idList.empty()) { queryString += " WHERE id = '" + idList.join("' OR id = '") + '\''; } if (forUpdate) queryString += d->m_driver->forUpdateString(); queryString += ';'; query.prepare(queryString); if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading budgets")); // krazy:exclude=crashy QMap budgets; int xmlCol = t.fieldNumber("XML"); while (query.next()) { QDomDocument dom; dom.setContent(GETSTRING(xmlCol), false); QDomNode child = dom.firstChild(); child = child.firstChild(); auto budget = MyMoneyXmlContentHandler2::readBudget(child.toElement()); budgets.insert(budget.id(), budget); d->signalProgress(++progress, 0); } return budgets; } QMap MyMoneyStorageSql::fetchBudgets() const { return fetchBudgets(QStringList(), false); } ulong MyMoneyStorageSql::getNextBudgetId() const { Q_D(const MyMoneyStorageSql); return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdBudgets>(QLatin1String("kmmBudgetConfig"), QLatin1String("id"), 1); } ulong MyMoneyStorageSql::getNextAccountId() const { Q_D(const MyMoneyStorageSql); return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdAccounts>(QLatin1String("kmmAccounts"), QLatin1String("id"), 1); } ulong MyMoneyStorageSql::getNextInstitutionId() const { Q_D(const MyMoneyStorageSql); return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdInstitutions>(QLatin1String("kmmInstitutions"), QLatin1String("id"), 1); } ulong MyMoneyStorageSql::getNextPayeeId() const { Q_D(const MyMoneyStorageSql); return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdPayees>(QLatin1String("kmmPayees"), QLatin1String("id"), 1); } ulong MyMoneyStorageSql::getNextTagId() const { Q_D(const MyMoneyStorageSql); return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdTags>(QLatin1String("kmmTags"), QLatin1String("id"), 1); } ulong MyMoneyStorageSql::getNextReportId() const { Q_D(const MyMoneyStorageSql); return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdReports>(QLatin1String("kmmReportConfig"), QLatin1String("id"), 1); } ulong MyMoneyStorageSql::getNextScheduleId() const { Q_D(const MyMoneyStorageSql); return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdSchedules>(QLatin1String("kmmSchedules"), QLatin1String("id"), 3); } ulong MyMoneyStorageSql::getNextSecurityId() const { Q_D(const MyMoneyStorageSql); return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdSecurities>(QLatin1String("kmmSecurities"), QLatin1String("id"), 1); } ulong MyMoneyStorageSql::getNextTransactionId() const { Q_D(const MyMoneyStorageSql); return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdTransactions>(QLatin1String("kmmTransactions"), QLatin1String("id"), 1); } ulong MyMoneyStorageSql::getNextOnlineJobId() const { Q_D(const MyMoneyStorageSql); return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdOnlineJobs>(QLatin1String("kmmOnlineJobs"), QLatin1String("id"), 1); } ulong MyMoneyStorageSql::getNextPayeeIdentifierId() const { Q_D(const MyMoneyStorageSql); return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdPayeeIdentifier>(QLatin1String("kmmPayeeIdentifier"), QLatin1String("id"), 5); } ulong MyMoneyStorageSql::getNextCostCenterId() const { Q_D(const MyMoneyStorageSql); return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdCostCenter>(QLatin1String("kmmCostCenterIdentifier"), QLatin1String("id"), 5); } ulong MyMoneyStorageSql::incrementBudgetId() { Q_D(MyMoneyStorageSql); d->m_hiIdBudgets = getNextBudgetId() + 1; return (d->m_hiIdBudgets - 1); } /** * @warning This method uses getNextAccountId() internally. The database is not informed which can cause issues * when the database is accessed concurrently. Then maybe a single id is used twice but the RDBMS will detect the * issue and KMyMoney crashes. This issue can only occur when two instances of KMyMoney access the same database. * But in this unlikly case MyMoneyStorageSql will have a lot more issues, I think. */ ulong MyMoneyStorageSql::incrementAccountId() { Q_D(MyMoneyStorageSql); d->m_hiIdAccounts = getNextAccountId() + 1; return (d->m_hiIdAccounts - 1); } ulong MyMoneyStorageSql::incrementInstitutionId() { Q_D(MyMoneyStorageSql); d->m_hiIdInstitutions = getNextInstitutionId() + 1; return (d->m_hiIdInstitutions - 1); } ulong MyMoneyStorageSql::incrementPayeeId() { Q_D(MyMoneyStorageSql); d->m_hiIdPayees = getNextPayeeId() + 1; return (d->m_hiIdPayees - 1); } ulong MyMoneyStorageSql::incrementTagId() { Q_D(MyMoneyStorageSql); d->m_hiIdTags = getNextTagId() + 1; return (d->m_hiIdTags - 1); } ulong MyMoneyStorageSql::incrementReportId() { Q_D(MyMoneyStorageSql); d->m_hiIdReports = getNextReportId() + 1; return (d->m_hiIdReports - 1); } ulong MyMoneyStorageSql::incrementScheduleId() { Q_D(MyMoneyStorageSql); d->m_hiIdSchedules = getNextScheduleId() + 1; return (d->m_hiIdSchedules - 1); } ulong MyMoneyStorageSql::incrementSecurityId() { Q_D(MyMoneyStorageSql); d->m_hiIdSecurities = getNextSecurityId() + 1; return (d->m_hiIdSecurities - 1); } ulong MyMoneyStorageSql::incrementTransactionId() { Q_D(MyMoneyStorageSql); d->m_hiIdTransactions = getNextTransactionId() + 1; return (d->m_hiIdTransactions - 1); } ulong MyMoneyStorageSql::incrementOnlineJobId() { Q_D(MyMoneyStorageSql); d->m_hiIdOnlineJobs = getNextOnlineJobId() + 1; return (d->m_hiIdOnlineJobs - 1); } ulong MyMoneyStorageSql::incrementPayeeIdentfierId() { Q_D(MyMoneyStorageSql); d->m_hiIdPayeeIdentifier = getNextPayeeIdentifierId() + 1; return (d->m_hiIdPayeeIdentifier - 1); } ulong MyMoneyStorageSql::incrementCostCenterId() { Q_D(MyMoneyStorageSql); d->m_hiIdCostCenter = getNextCostCenterId() + 1; return (d->m_hiIdCostCenter - 1); } void MyMoneyStorageSql::loadAccountId(ulong id) { Q_D(MyMoneyStorageSql); d->m_hiIdAccounts = id; d->writeFileInfo(); } void MyMoneyStorageSql::loadTransactionId(ulong id) { Q_D(MyMoneyStorageSql); d->m_hiIdTransactions = id; d->writeFileInfo(); } void MyMoneyStorageSql::loadPayeeId(ulong id) { Q_D(MyMoneyStorageSql); d->m_hiIdPayees = id; d->writeFileInfo(); } void MyMoneyStorageSql::loadTagId(ulong id) { Q_D(MyMoneyStorageSql); d->m_hiIdTags = id; d->writeFileInfo(); } void MyMoneyStorageSql::loadInstitutionId(ulong id) { Q_D(MyMoneyStorageSql); d->m_hiIdInstitutions = id; d->writeFileInfo(); } void MyMoneyStorageSql::loadScheduleId(ulong id) { Q_D(MyMoneyStorageSql); d->m_hiIdSchedules = id; d->writeFileInfo(); } void MyMoneyStorageSql::loadSecurityId(ulong id) { Q_D(MyMoneyStorageSql); d->m_hiIdSecurities = id; d->writeFileInfo(); } void MyMoneyStorageSql::loadReportId(ulong id) { Q_D(MyMoneyStorageSql); d->m_hiIdReports = id; d->writeFileInfo(); } void MyMoneyStorageSql::loadBudgetId(ulong id) { Q_D(MyMoneyStorageSql); d->m_hiIdBudgets = id; d->writeFileInfo(); } void MyMoneyStorageSql::loadOnlineJobId(ulong id) { Q_D(MyMoneyStorageSql); d->m_hiIdOnlineJobs = id; d->writeFileInfo(); } void MyMoneyStorageSql::loadPayeeIdentifierId(ulong id) { Q_D(MyMoneyStorageSql); d->m_hiIdPayeeIdentifier = id; d->writeFileInfo(); } //**************************************************** void MyMoneyStorageSql::setProgressCallback(void(*callback)(int, int, const QString&)) { Q_D(MyMoneyStorageSql); d->m_progressCallback = callback; } void MyMoneyStorageSql::readFile(QIODevice* s, MyMoneyStorageMgr* storage) { Q_UNUSED(s); Q_UNUSED(storage) } void MyMoneyStorageSql::writeFile(QIODevice* s, MyMoneyStorageMgr* storage) { Q_UNUSED(s); Q_UNUSED(storage) } // **************************** Error display routine ******************************* QDate MyMoneyStorageSqlPrivate::m_startDate = QDate(1900, 1, 1); void MyMoneyStorageSql::setStartDate(const QDate& startDate) { MyMoneyStorageSqlPrivate::m_startDate = startDate; } QMap< QString, MyMoneyCostCenter > MyMoneyStorageSql::fetchCostCenters(const QStringList& idList, bool forUpdate) const { Q_D(const MyMoneyStorageSql); Q_UNUSED(forUpdate); MyMoneyDbTransaction trans(const_cast (*this), Q_FUNC_INFO); if (d->m_displayStatus) { int costCenterNb = (idList.isEmpty() ? 100 : idList.size()); d->signalProgress(0, costCenterNb, QObject::tr("Loading cost center...")); } int progress = 0; QMap costCenterList; //ulong lastId; const MyMoneyDbTable& t = d->m_db.m_tables["kmmCostCenter"]; QSqlQuery query(*const_cast (this)); if (idList.isEmpty()) { query.prepare(t.selectAllString()); } else { QString whereClause = " where ("; QString itemConnector = ""; foreach (const QString& it, idList) { whereClause.append(QString("%1id = '%2'").arg(itemConnector).arg(it)); itemConnector = " or "; } whereClause += ')'; query.prepare(t.selectAllString(false) + whereClause); } if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading CostCenter")); // krazy:exclude=crashy const int idCol = t.fieldNumber("id"); const int nameCol = t.fieldNumber("name"); while (query.next()) { MyMoneyCostCenter costCenter; QString pid = GETSTRING(idCol); costCenter.setName(GETSTRING(nameCol)); costCenterList[pid] = MyMoneyCostCenter(pid, costCenter); if (d->m_displayStatus) d->signalProgress(++progress, 0); } return costCenterList; } QMap< QString, MyMoneyCostCenter > MyMoneyStorageSql::fetchCostCenters() const { return fetchCostCenters(QStringList(), false); }