File indexing completed on 2024-04-28 16:30:08
0001 /*************************************************************************** 0002 * SPDX-FileCopyrightText: 2022 S. MANKOWSKI stephane@mankowski.fr 0003 * SPDX-FileCopyrightText: 2022 G. DE BURE support@mankowski.fr 0004 * SPDX-License-Identifier: GPL-3.0-or-later 0005 ***************************************************************************/ 0006 /** @file 0007 * This file implements classes SKGAccountObject. 0008 * 0009 * @author Stephane MANKOWSKI / Guillaume DE BURE 0010 */ 0011 #include "skgaccountobject.h" 0012 0013 #include <klocalizedstring.h> 0014 0015 #include "skgbankobject.h" 0016 #include "skgdocumentbank.h" 0017 #include "skginterestobject.h" 0018 #include "skgoperationobject.h" 0019 #include "skgpayeeobject.h" 0020 #include "skgsuboperationobject.h" 0021 #include "skgtraces.h" 0022 #include "skgunitobject.h" 0023 0024 int factorial(int n) 0025 { 0026 return (n == 1 || n == 0) ? 1 : factorial(n - 1) * n; 0027 } 0028 0029 SKGAccountObject::SKGAccountObject() : SKGAccountObject(nullptr, 0) {} 0030 0031 SKGAccountObject::SKGAccountObject(SKGDocument* iDocument, int iID) : SKGNamedObject(iDocument, QStringLiteral("v_account"), iID) {} 0032 0033 SKGAccountObject::~SKGAccountObject() = default; 0034 0035 SKGAccountObject::SKGAccountObject(const SKGAccountObject& iObject) 0036 = default; 0037 0038 SKGAccountObject::SKGAccountObject(const SKGNamedObject& iObject) 0039 : SKGNamedObject(iObject.getDocument(), QStringLiteral("v_account"), iObject.getID()) 0040 { 0041 if (iObject.getRealTable() == QStringLiteral("account")) { 0042 copyFrom(iObject); 0043 } else { 0044 *this = SKGNamedObject(iObject.getDocument(), QStringLiteral("v_account"), iObject.getID()); 0045 } 0046 } 0047 0048 SKGAccountObject::SKGAccountObject(const SKGObjectBase& iObject) 0049 { 0050 if (iObject.getRealTable() == QStringLiteral("account")) { 0051 copyFrom(iObject); 0052 } else { 0053 *this = SKGNamedObject(iObject.getDocument(), QStringLiteral("v_account"), iObject.getID()); 0054 } 0055 } 0056 0057 SKGAccountObject& SKGAccountObject::operator= (const SKGObjectBase& iObject) 0058 { 0059 copyFrom(iObject); 0060 return *this; 0061 } 0062 0063 SKGAccountObject& SKGAccountObject::operator= (const SKGAccountObject& iObject) 0064 { 0065 copyFrom(iObject); 0066 return *this; 0067 } 0068 0069 SKGError SKGAccountObject::setInitialBalance(double iBalance, const SKGUnitObject& iUnit) 0070 { 0071 SKGError err; 0072 SKGTRACEINFUNCRC(10, err) 0073 if (getDocument() != nullptr) { 0074 // Delete previous initial balance for this account 0075 err = getDocument()->executeSqliteOrder("DELETE FROM operation WHERE d_date='0000-00-00' AND rd_account_id=" % SKGServices::intToString(getID())); 0076 0077 // Creation of new initial balance 0078 IFOK(err) { 0079 SKGOperationObject initialBalanceOp; 0080 err = addOperation(initialBalanceOp, true); 0081 IFOKDO(err, initialBalanceOp.setAttribute(QStringLiteral("d_date"), QStringLiteral("0000-00-00"))) 0082 IFOKDO(err, initialBalanceOp.setUnit(iUnit)) 0083 IFOKDO(err, initialBalanceOp.setStatus(SKGOperationObject::CHECKED)) 0084 IFOKDO(err, initialBalanceOp.save()) 0085 0086 SKGSubOperationObject initialBalanceSubOp; 0087 IFOKDO(err, initialBalanceOp.addSubOperation(initialBalanceSubOp)) 0088 IFOKDO(err, initialBalanceSubOp.setAttribute(QStringLiteral("d_date"), QStringLiteral("0000-00-00"))) 0089 IFOKDO(err, initialBalanceSubOp.setQuantity(iBalance)) 0090 IFOKDO(err, initialBalanceSubOp.save()) 0091 } 0092 } 0093 return err; 0094 } 0095 0096 SKGError SKGAccountObject::getInitialBalance(double& oBalance, SKGUnitObject& oUnit) 0097 { 0098 SKGError err; 0099 SKGTRACEINFUNCRC(10, err) 0100 // Initialisation 0101 oBalance = 0; 0102 oUnit = SKGUnitObject(); 0103 QString unitName = qobject_cast<SKGDocumentBank*>(getDocument())->getPrimaryUnit().Symbol; 0104 0105 // Get initial balance 0106 SKGStringListList listTmp; 0107 err = getDocument()->executeSelectSqliteOrder("SELECT f_QUANTITY, t_UNIT FROM v_operation_tmp1 WHERE d_date='0000-00-00' AND rd_account_id=" % SKGServices::intToString(getID()), listTmp); 0108 if (!err && listTmp.count() > 1) { 0109 oBalance = SKGServices::stringToDouble(listTmp.at(1).at(0)); 0110 unitName = listTmp.at(1).at(1); 0111 0112 oUnit = SKGUnitObject(getDocument()); 0113 err = oUnit.setSymbol(unitName); 0114 IFOKDO(err, oUnit.load()) 0115 } 0116 return err; 0117 } 0118 0119 SKGError SKGAccountObject::setBank(const SKGBankObject& iBank) 0120 { 0121 return setAttribute(QStringLiteral("rd_bank_id"), SKGServices::intToString(iBank.getID())); 0122 } 0123 0124 SKGError SKGAccountObject::getBank(SKGBankObject& oBank) const 0125 { 0126 SKGError err = getDocument()->getObject(QStringLiteral("v_bank"), "id=" % getAttribute(QStringLiteral("rd_bank_id")), oBank); 0127 return err; 0128 } 0129 0130 SKGError SKGAccountObject::setLinkedAccount(const SKGAccountObject& iAccount) 0131 { 0132 return setAttribute(QStringLiteral("r_account_id"), SKGServices::intToString(iAccount.getID())); 0133 } 0134 0135 SKGError SKGAccountObject::getLinkedAccount(SKGAccountObject& oAccount) const 0136 { 0137 SKGError err = getDocument()->getObject(QStringLiteral("v_account"), "id=" % getAttribute(QStringLiteral("r_account_id")), oAccount); 0138 return err; 0139 } 0140 0141 SKGError SKGAccountObject::getLinkedByAccounts(SKGListSKGObjectBase& oAccounts) const 0142 { 0143 SKGError err; 0144 if (getDocument() != nullptr) { 0145 err = getDocument()->getObjects(QStringLiteral("v_account"), 0146 "r_account_id=" % SKGServices::intToString(getID()), 0147 oAccounts); 0148 } 0149 return err; 0150 } 0151 0152 SKGError SKGAccountObject::setNumber(const QString& iNumber) 0153 { 0154 return setAttribute(QStringLiteral("t_number"), iNumber); 0155 } 0156 0157 QString SKGAccountObject::getNumber() const 0158 { 0159 return getAttribute(QStringLiteral("t_number")); 0160 } 0161 0162 SKGError SKGAccountObject::setComment(const QString& iComment) 0163 { 0164 return setAttribute(QStringLiteral("t_comment"), iComment); 0165 } 0166 0167 QString SKGAccountObject::getComment() const 0168 { 0169 return getAttribute(QStringLiteral("t_comment")); 0170 } 0171 0172 SKGError SKGAccountObject::setAgencyNumber(const QString& iNumber) 0173 { 0174 return setAttribute(QStringLiteral("t_agency_number"), iNumber); 0175 } 0176 0177 QString SKGAccountObject::getAgencyNumber() const 0178 { 0179 return getAttribute(QStringLiteral("t_agency_number")); 0180 } 0181 0182 SKGError SKGAccountObject::setAgencyAddress(const QString& iAddress) 0183 { 0184 return setAttribute(QStringLiteral("t_agency_address"), iAddress); 0185 } 0186 0187 QString SKGAccountObject::getAgencyAddress() const 0188 { 0189 return getAttribute(QStringLiteral("t_agency_address")); 0190 } 0191 0192 SKGError SKGAccountObject::addOperation(SKGOperationObject& oOperation, bool iForce) 0193 { 0194 SKGError err; 0195 if (getID() == 0) { 0196 err = SKGError(ERR_FAIL, i18nc("Error message", "%1 failed because linked object is not yet saved in the database.", QStringLiteral("SKGAccountObject::addOperation"))); 0197 } else { 0198 oOperation = SKGOperationObject(getDocument()); 0199 err = oOperation.setParentAccount(*this, iForce); 0200 } 0201 return err; 0202 } 0203 0204 int SKGAccountObject::getNbOperation() const 0205 { 0206 int nb = 0; 0207 if (getDocument() != nullptr) { 0208 getDocument()->getNbObjects(QStringLiteral("operation"), "rd_account_id=" % SKGServices::intToString(getID()), nb); 0209 } 0210 return nb; 0211 } 0212 0213 SKGError SKGAccountObject::getOperations(SKGListSKGObjectBase& oOperations) const 0214 { 0215 SKGError err; 0216 if (getDocument() != nullptr) { 0217 err = getDocument()->getObjects(QStringLiteral("v_operation"), 0218 "rd_account_id=" % SKGServices::intToString(getID()), 0219 oOperations); 0220 } 0221 return err; 0222 } 0223 0224 double SKGAccountObject::getCurrentAmount() const 0225 { 0226 return SKGServices::stringToDouble(getAttributeFromView(QStringLiteral("v_account_amount"), QStringLiteral("f_CURRENTAMOUNT"))); 0227 } 0228 0229 double SKGAccountObject::getAmount(QDate iDate, bool iOnlyCurrencies) const 0230 { 0231 SKGTRACEINFUNC(10) 0232 double output = 0; 0233 if (getDocument() != nullptr) { 0234 // Search result in cache 0235 QString ids = SKGServices::intToString(getID()); 0236 QString dates = SKGServices::dateToSqlString(iDate); 0237 QString key = "getamount-" % ids % '-' % dates; 0238 QString val = getDocument()->getCachedValue(key); 0239 if (val.isEmpty()) { 0240 SKGStringListList listTmp; 0241 SKGError err = getDocument()->executeSelectSqliteOrder("SELECT TOTAL(f_QUANTITY), rc_unit_id FROM v_operation_tmp1 WHERE " 0242 "d_date<='" % dates % "' AND t_template='N' AND rd_account_id=" % ids % 0243 (iOnlyCurrencies ? " AND t_TYPEUNIT IN ('1', '2', 'C')" : "") % 0244 " GROUP BY rc_unit_id", 0245 listTmp); 0246 int nb = listTmp.count(); 0247 for (int i = 1; !err && i < nb ; ++i) { 0248 QString quantity = listTmp.at(i).at(0); 0249 QString unitid = listTmp.at(i).at(1); 0250 0251 double coef = 1; 0252 QString val2 = getDocument()->getCachedValue("unitvalue-" % unitid); 0253 if (!val2.isEmpty()) { 0254 // Yes 0255 coef = SKGServices::stringToDouble(val2); 0256 } else { 0257 // No 0258 SKGUnitObject unit(getDocument(), SKGServices::stringToInt(unitid)); 0259 if (unit.getType() != SKGUnitObject::PRIMARY) { 0260 coef = unit.getAmount(iDate); 0261 } 0262 } 0263 0264 output += coef * SKGServices::stringToDouble(quantity); 0265 } 0266 getDocument()->addValueInCache(key, SKGServices::doubleToString(output)); 0267 } else { 0268 output = SKGServices::stringToDouble(val); 0269 } 0270 } 0271 return output; 0272 } 0273 0274 SKGError SKGAccountObject::setType(SKGAccountObject::AccountType iType) 0275 { 0276 return setAttribute(QStringLiteral("t_type"), (iType == CURRENT ? QStringLiteral("C") : 0277 (iType == CREDITCARD ? QStringLiteral("D") : 0278 (iType == ASSETS ? QStringLiteral("A") : 0279 (iType == INVESTMENT ? QStringLiteral("I") : 0280 (iType == WALLET ? QStringLiteral("W") : 0281 (iType == PENSION ? QStringLiteral("P") : 0282 (iType == LOAN ? QStringLiteral("L") : 0283 (iType == SAVING ? QStringLiteral("S") : 0284 QStringLiteral("O")))))))))); 0285 } 0286 0287 SKGAccountObject::AccountType SKGAccountObject::getType() const 0288 { 0289 QString typeString = getAttribute(QStringLiteral("t_type")); 0290 return (typeString == QStringLiteral("C") ? CURRENT : 0291 (typeString == QStringLiteral("D") ? CREDITCARD : 0292 (typeString == QStringLiteral("A") ? ASSETS : 0293 (typeString == QStringLiteral("I") ? INVESTMENT : 0294 (typeString == QStringLiteral("W") ? WALLET : 0295 (typeString == QStringLiteral("P") ? PENSION : 0296 (typeString == QStringLiteral("L") ? LOAN : 0297 (typeString == QStringLiteral("S") ? SAVING : OTHER)))))))); 0298 } 0299 0300 SKGError SKGAccountObject::setClosed(bool iClosed) 0301 { 0302 return setAttribute(QStringLiteral("t_close"), iClosed ? QStringLiteral("Y") : QStringLiteral("N")); 0303 } 0304 0305 bool SKGAccountObject::isClosed() const 0306 { 0307 return (getAttribute(QStringLiteral("t_close")) == QStringLiteral("Y")); 0308 } 0309 0310 SKGError SKGAccountObject::bookmark(bool iBookmark) 0311 { 0312 return setAttribute(QStringLiteral("t_bookmarked"), iBookmark ? QStringLiteral("Y") : QStringLiteral("N")); 0313 } 0314 0315 bool SKGAccountObject::isBookmarked() const 0316 { 0317 return (getAttribute(QStringLiteral("t_bookmarked")) == QStringLiteral("Y")); 0318 } 0319 0320 SKGError SKGAccountObject::maxLimitAmountEnabled(bool iEnabled) 0321 { 0322 return setAttribute(QStringLiteral("t_maxamount_enabled"), iEnabled ? QStringLiteral("Y") : QStringLiteral("N")); 0323 } 0324 0325 bool SKGAccountObject::isMaxLimitAmountEnabled() const 0326 { 0327 return (getAttribute(QStringLiteral("t_maxamount_enabled")) == QStringLiteral("Y")); 0328 } 0329 0330 SKGError SKGAccountObject::setMaxLimitAmount(double iAmount) 0331 { 0332 SKGError err = setAttribute(QStringLiteral("f_maxamount"), SKGServices::doubleToString(iAmount)); 0333 if (!err && getMinLimitAmount() > iAmount) { 0334 err = setMinLimitAmount(iAmount); 0335 } 0336 return err; 0337 } 0338 0339 double SKGAccountObject::getMaxLimitAmount() const 0340 { 0341 return SKGServices::stringToDouble(getAttribute(QStringLiteral("f_maxamount"))); 0342 } 0343 0344 SKGError SKGAccountObject::minLimitAmountEnabled(bool iEnabled) 0345 { 0346 return setAttribute(QStringLiteral("t_minamount_enabled"), iEnabled ? QStringLiteral("Y") : QStringLiteral("N")); 0347 } 0348 0349 bool SKGAccountObject::isMinLimitAmountEnabled() const 0350 { 0351 return (getAttribute(QStringLiteral("t_minamount_enabled")) == QStringLiteral("Y")); 0352 } 0353 0354 SKGError SKGAccountObject::setMinLimitAmount(double iAmount) 0355 { 0356 SKGError err = setAttribute(QStringLiteral("f_minamount"), SKGServices::doubleToString(iAmount)); 0357 if (!err && getMaxLimitAmount() < iAmount) { 0358 err = setMaxLimitAmount(iAmount); 0359 } 0360 return err; 0361 } 0362 0363 double SKGAccountObject::getMinLimitAmount() const 0364 { 0365 return SKGServices::stringToDouble(getAttribute(QStringLiteral("f_minamount"))); 0366 } 0367 0368 SKGError SKGAccountObject::setReconciliationDate(QDate iDate) 0369 { 0370 return setAttribute(QStringLiteral("d_reconciliationdate"), SKGServices::dateToSqlString(iDate)); 0371 } 0372 0373 QDate SKGAccountObject::getReconciliationDate() const 0374 { 0375 return SKGServices::stringToTime(getAttribute(QStringLiteral("d_reconciliationdate"))).date(); 0376 } 0377 0378 SKGError SKGAccountObject::setReconciliationBalance(double iAmount) 0379 { 0380 return setAttribute(QStringLiteral("f_reconciliationbalance"), SKGServices::doubleToString(iAmount)); 0381 } 0382 0383 double SKGAccountObject::getReconciliationBalance() const 0384 { 0385 return SKGServices::stringToDouble(getAttribute(QStringLiteral("f_reconciliationbalance"))); 0386 } 0387 0388 SKGError SKGAccountObject::getUnit(SKGUnitObject& oUnit) const 0389 { 0390 // Get initial amount 0391 SKGStringListList listTmp; 0392 SKGError err = getDocument()->executeSelectSqliteOrder("SELECT t_UNIT FROM v_suboperation_consolidated WHERE d_date='0000-00-00' AND rd_account_id=" % SKGServices::intToString(getID()), listTmp); 0393 IFOK(err) { 0394 // Is initial amount existing ? 0395 if (listTmp.count() > 1) { 0396 // Yes ==> then the amount is the amount of the initial value 0397 oUnit = SKGUnitObject(getDocument()); 0398 err = oUnit.setSymbol(listTmp.at(1).at(0)); 0399 IFOKDO(err, oUnit.load()) 0400 } else { 0401 // No ==> we get the preferred unit 0402 SKGObjectBase::SKGListSKGObjectBase units; 0403 err = getDocument()->getObjects(QStringLiteral("v_unit"), 0404 "t_type IN ('1', '2', 'C') AND EXISTS(SELECT 1 FROM operation WHERE rc_unit_id=v_unit.id AND rd_account_id=" % SKGServices::intToString(getID()) % ") ORDER BY t_type", units); 0405 int nb = units.count(); 0406 if (nb != 0) { 0407 oUnit = units.at(0); 0408 } 0409 } 0410 } 0411 return err; 0412 } 0413 0414 SKGError SKGAccountObject::addInterest(SKGInterestObject& oInterest) 0415 { 0416 SKGError err; 0417 if (getID() == 0) { 0418 err = SKGError(ERR_FAIL, i18nc("Error message", "%1 failed because linked object is not yet saved in the database.", QStringLiteral("SKGAccountObject::addInterest"))); 0419 } else { 0420 oInterest = SKGInterestObject(qobject_cast<SKGDocumentBank*>(getDocument())); 0421 err = oInterest.setAccount(*this); 0422 } 0423 return err; 0424 } 0425 0426 SKGError SKGAccountObject::getInterests(SKGListSKGObjectBase& oInterestList) const 0427 { 0428 SKGError err = getDocument()->getObjects(QStringLiteral("v_interest"), 0429 "rd_account_id=" % SKGServices::intToString(getID()), 0430 oInterestList); 0431 return err; 0432 } 0433 0434 SKGError SKGAccountObject::getInterest(QDate iDate, SKGInterestObject& oInterest) const 0435 { 0436 QString ids = SKGServices::intToString(getID()); 0437 QString dates = SKGServices::dateToSqlString(iDate); 0438 SKGError err = SKGObjectBase::getDocument()->getObject(QStringLiteral("v_interest"), 0439 "rd_account_id=" % ids % " AND d_date<='" % dates % 0440 "' AND ABS(strftime('%s','" % dates % 0441 "')-strftime('%s',d_date))=(SELECT MIN(ABS(strftime('%s','" % dates % 0442 "')-strftime('%s',u2.d_date))) FROM interest u2 WHERE u2.rd_account_id=" % ids % 0443 " AND u2.d_date<='" % dates % "')", 0444 oInterest); 0445 0446 // If not found then get first 0447 IFKO(err) err = SKGObjectBase::getDocument()->getObject(QStringLiteral("v_interest"), 0448 "rd_account_id=" % SKGServices::intToString(getID()) % " AND d_date=(SELECT MIN(d_date) FROM interest WHERE rd_account_id=" % 0449 SKGServices::intToString(getID()) % ')', 0450 oInterest); 0451 return err; 0452 } 0453 0454 SKGError SKGAccountObject::getInterestItems(SKGAccountObject::SKGInterestItemList& oInterestList, double& oInterests, int iYear) const 0455 { 0456 oInterestList.clear(); 0457 SKGError err; 0458 0459 // Initial date 0460 int y = iYear; 0461 if (y == 0) { 0462 y = QDate::currentDate().year(); 0463 } 0464 QDate initialDate = QDate(y, 1, 1); 0465 QDate lastDate = QDate(y, 12, 31); 0466 0467 oInterests = 0; 0468 bool computationNeeded = false; 0469 0470 // Add transactions 0471 SKGObjectBase::SKGListSKGObjectBase items; 0472 err = getDocument()->getObjects(QStringLiteral("v_operation"), "rd_account_id=" % SKGServices::intToString(getID()) % 0473 " AND t_template='N' AND t_TYPEUNIT IN ('1', '2', 'C')" 0474 " AND d_date>='" % SKGServices::dateToSqlString(initialDate) % "' " 0475 " AND d_date<='" % SKGServices::dateToSqlString(lastDate) % "' ORDER BY d_date", items); 0476 int nb = items.count(); 0477 for (int i = 0; !err && i < nb; ++i) { 0478 SKGOperationObject ob(items.at(i)); 0479 0480 SKGInterestItem itemI; 0481 itemI.object = ob; 0482 itemI.date = ob.getDate(); 0483 itemI.valueDate = itemI.date; 0484 itemI.rate = 0; 0485 itemI.base = 0; 0486 itemI.coef = 0; 0487 itemI.annualInterest = 0; 0488 itemI.accruedInterest = 0; 0489 itemI.amount = ob.getCurrentAmount(); 0490 0491 oInterestList.push_back(itemI); 0492 } 0493 0494 // Add interest 0495 IFOK(err) { 0496 err = getDocument()->getObjects(QStringLiteral("v_interest"), "rd_account_id=" % SKGServices::intToString(getID()) % 0497 " AND d_date>='" % SKGServices::dateToSqlString(initialDate) % "' " 0498 " AND d_date<='" % SKGServices::dateToSqlString(lastDate) % "' ORDER BY d_date", items); 0499 0500 int pos = 0; 0501 int nb2 = items.count(); 0502 for (int i = 0; !err && i < nb2; ++i) { 0503 SKGInterestObject ob(items.at(i)); 0504 0505 SKGInterestItem itemI; 0506 itemI.object = ob; 0507 itemI.date = ob.getDate(); 0508 itemI.valueDate = itemI.date; 0509 itemI.rate = ob.getRate(); 0510 itemI.base = SKGServices::stringToInt(ob.getAttribute(QStringLiteral("t_base"))); 0511 itemI.coef = 0; 0512 itemI.annualInterest = 0; 0513 itemI.accruedInterest = 0; 0514 itemI.amount = 0; 0515 0516 int nb3 = oInterestList.count(); 0517 for (int j = pos; !err && j < nb3; ++j) { 0518 if (itemI.date <= oInterestList.at(j).date) { 0519 break; 0520 } 0521 ++pos; 0522 } 0523 0524 oInterestList.insert(pos, itemI); 0525 computationNeeded = true; 0526 } 0527 } 0528 0529 // Get first interest 0530 IFOK(err) { 0531 SKGInterestObject firstInterest; 0532 if (getInterest(initialDate, firstInterest).isSucceeded()) { 0533 if (firstInterest.getDate() < initialDate) { 0534 SKGInterestItem itemI; 0535 itemI.object = firstInterest; 0536 itemI.date = initialDate; 0537 itemI.valueDate = initialDate; 0538 itemI.rate = firstInterest.getRate(); 0539 itemI.base = 0; 0540 itemI.coef = 0; 0541 itemI.annualInterest = 0; 0542 itemI.accruedInterest = 0; 0543 itemI.amount = 0; 0544 0545 oInterestList.insert(0, itemI); 0546 computationNeeded = true; 0547 } 0548 } 0549 } 0550 0551 // Launch computation 0552 IFOK(err) { 0553 if (computationNeeded) { 0554 err = computeInterestItems(oInterestList, oInterests, y); 0555 } else { 0556 // Drop temporary table 0557 IFOKDO(err, getDocument()->executeSqliteOrder(QStringLiteral("DROP TABLE IF EXISTS interest_result"))) 0558 // Create fake table 0559 IFOKDO(err, getDocument()->executeSqliteOrder(QStringLiteral("CREATE TEMP TABLE interest_result(a)"))) 0560 } 0561 } 0562 return err; 0563 } 0564 0565 SKGError SKGAccountObject::computeInterestItems(SKGAccountObject::SKGInterestItemList& ioInterestList, double& oInterests, int iYear) const 0566 { 0567 SKGError err; 0568 0569 // Sum annual interest 0570 oInterests = 0; 0571 0572 // Initial date 0573 int y = iYear; 0574 if (y == 0) { 0575 y = QDate::currentDate().year(); 0576 } 0577 QDate initialDate = QDate(y, 1, 1); 0578 0579 // Default interest item 0580 SKGInterestItem currentInterest; 0581 currentInterest.date = initialDate; 0582 currentInterest.valueDate = currentInterest.date; 0583 currentInterest.rate = 0; 0584 currentInterest.coef = 0; 0585 currentInterest.annualInterest = 0; 0586 currentInterest.accruedInterest = 0; 0587 0588 int nb = ioInterestList.count(); 0589 for (int i = 0; !err && i < nb; ++i) { 0590 SKGInterestItem tmp = ioInterestList.at(i); 0591 SKGObjectBase object = tmp.object; 0592 if (object.getRealTable() == QStringLiteral("operation")) { 0593 // Get transactions 0594 SKGOperationObject op(object); 0595 0596 // Get current amount 0597 tmp.amount = op.getCurrentAmount(); 0598 0599 // Get value date computation mode 0600 SKGInterestObject::ValueDateMode valueMode = SKGInterestObject::FIFTEEN; 0601 SKGInterestObject::InterestMode baseMode = SKGInterestObject::FIFTEEN24; 0602 if (currentInterest.object.getRealTable() == QStringLiteral("interest")) { 0603 SKGInterestObject interestObj(currentInterest.object); 0604 valueMode = (tmp.amount >= 0 ? interestObj.getIncomeValueDateMode() : interestObj.getExpenditueValueDateMode()); 0605 baseMode = interestObj.getInterestComputationMode(); 0606 0607 tmp.rate = interestObj.getRate(); 0608 } 0609 0610 // Compute value date 0611 if (object.getRealTable() == QStringLiteral("operation")) { 0612 if (valueMode == SKGInterestObject::FIFTEEN) { 0613 if (tmp.amount >= 0) { 0614 if (tmp.date.day() <= 15) { 0615 tmp.valueDate = tmp.date.addDays(16 - tmp.date.day()); 0616 } else { 0617 tmp.valueDate = tmp.date.addMonths(1).addDays(1 - tmp.date.day()); 0618 } 0619 } else { 0620 if (tmp.date.day() <= 15) { 0621 tmp.valueDate = tmp.date.addDays(1 - tmp.date.day()); 0622 } else { 0623 tmp.valueDate = tmp.date.addDays(16 - tmp.date.day()); 0624 } 0625 } 0626 } else { 0627 tmp.valueDate = tmp.date.addDays(tmp.amount >= 0 ? (static_cast<int>(valueMode)) - 1 : - (static_cast<int>(valueMode)) + 1); 0628 } 0629 } 0630 0631 // Compute coef 0632 if (baseMode == SKGInterestObject::DAYS365) { 0633 QDate last(tmp.date.year(), 12, 31); 0634 tmp.coef = tmp.valueDate.daysTo(last) + 1; 0635 tmp.coef /= 365; 0636 } else if (baseMode == SKGInterestObject::DAYS360) { 0637 QDate last(tmp.date.year(), 12, 31); 0638 tmp.coef = 360 * (last.year() - tmp.valueDate.year()) + 30 * (last.month() - tmp.valueDate.month()) + (last.day() - tmp.valueDate.day()); 0639 tmp.coef /= 360; 0640 } else { 0641 tmp.coef = 2 * (12 - tmp.valueDate.month()) + (tmp.valueDate.day() <= 15 ? 2 : 1); 0642 tmp.coef /= 24; 0643 } 0644 if (tmp.valueDate.year() != iYear) { 0645 tmp.coef = 0; 0646 } 0647 0648 // Compute annual interest 0649 tmp.annualInterest = tmp.amount * tmp.coef * tmp.rate / 100; 0650 0651 } else if (object.getRealTable() == QStringLiteral("interest")) { 0652 // Compute coef 0653 if (tmp.base == 365) { 0654 QDate last(tmp.date.year(), 12, 31); 0655 tmp.coef = tmp.valueDate.daysTo(last) + 1; 0656 tmp.coef /= 365; 0657 } else if (tmp.base == 360) { 0658 QDate last(tmp.date.year(), 12, 31); 0659 tmp.coef = 360 * (last.year() - tmp.valueDate.year()) + 30 * (last.month() - tmp.valueDate.month()) + (last.day() - tmp.valueDate.day()); 0660 tmp.coef /= 360; 0661 } else { 0662 tmp.coef = 2 * (12 - tmp.valueDate.month()) + (tmp.valueDate.day() <= 15 ? 2 : 1); 0663 tmp.coef /= 24; 0664 } 0665 if (tmp.valueDate.year() != iYear) { 0666 tmp.coef = 0; 0667 } 0668 0669 // Compute annual interest 0670 // BUG 329568: We must ignore transactions of the day 0671 tmp.amount = getAmount(tmp.valueDate.addDays(-1), true); 0672 tmp.annualInterest = tmp.amount * tmp.coef * (tmp.rate - currentInterest.rate) / 100; 0673 0674 currentInterest = tmp; 0675 } 0676 0677 // Compute sum 0678 oInterests += tmp.annualInterest; 0679 0680 // Compute accrued interest 0681 tmp.accruedInterest = oInterests - getAmount(tmp.date, true) * tmp.coef * tmp.rate / 100; 0682 0683 ioInterestList[i] = tmp; 0684 } 0685 0686 // Create temporary table 0687 IFOK(err) { 0688 QStringList sqlOrders; 0689 sqlOrders << QStringLiteral("DROP TABLE IF EXISTS interest_result") 0690 << QStringLiteral("CREATE TEMP TABLE interest_result(" 0691 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 0692 "d_date DATE NOT NULL," 0693 "d_valuedate DATE NOT NULL," 0694 "t_comment TEXT NOT NULL DEFAULT ''," 0695 "f_currentamount FLOAT NOT NULL DEFAULT 0," 0696 "f_coef FLOAT NOT NULL DEFAULT 0," 0697 "f_rate FLOAT NOT NULL DEFAULT 0," 0698 "f_annual_interest FLOAT NOT NULL DEFAULT 0," 0699 "f_accrued_interest FLOAT NOT NULL DEFAULT 0" 0700 ")"); 0701 err = getDocument()->executeSqliteOrders(sqlOrders); 0702 0703 // Fill table 0704 int nb2 = ioInterestList.count(); 0705 for (int i = 0; !err && i < nb2; ++i) { 0706 SKGInterestItem interest = ioInterestList.at(i); 0707 SKGObjectBase object = interest.object; 0708 QString sqlinsert = 0709 "INSERT INTO interest_result (d_date,d_valuedate,t_comment,f_currentamount,f_coef,f_rate,f_annual_interest,f_accrued_interest) " 0710 " VALUES ('" % SKGServices::dateToSqlString(interest.date) % 0711 "','" % SKGServices::dateToSqlString(interest.valueDate) % 0712 "','" % SKGServices::stringToSqlString(object.getRealTable() == QStringLiteral("operation") ? i18nc("Noun", "Relative to transaction '%1'", SKGOperationObject(object).getDisplayName()) : i18nc("Noun", "Rate change")) % 0713 "'," % SKGServices::doubleToString(interest.amount) % 0714 ',' % SKGServices::doubleToString(interest.coef) % 0715 ',' % SKGServices::doubleToString(interest.rate) % 0716 ',' % SKGServices::doubleToString(interest.annualInterest) % 0717 ',' % SKGServices::doubleToString(interest.accruedInterest) % 0718 ")"; 0719 0720 err = getDocument()->executeSqliteOrder(sqlinsert); 0721 } 0722 } 0723 return err; 0724 } 0725 0726 SKGError SKGAccountObject::transferDeferredOperations(const SKGAccountObject& iTargetAccount, QDate iDate) 0727 { 0728 SKGError err; 0729 SKGTRACEINFUNCRC(10, err) 0730 // 0731 auto* doc = qobject_cast<SKGDocumentBank*>(getDocument()); 0732 if (doc != nullptr) { 0733 // Get marked transactions 0734 SKGObjectBase::SKGListSKGObjectBase transactions; 0735 IFOKDO(err, getDocument()->getObjects(QStringLiteral("v_operation"), "rd_account_id=" % SKGServices::intToString(getID()) % " AND t_status='P'", transactions)) 0736 int nb = transactions.count(); 0737 if (nb != 0) { 0738 SKGOperationObject mergedOperations; 0739 SKGOperationObject balancedOperations; 0740 for (int i = 0; !err && i < nb; ++i) { 0741 SKGOperationObject op(transactions.at(i)); 0742 0743 // Create the balance operation 0744 SKGOperationObject opdup; 0745 IFOKDO(err, op.duplicate(opdup, iDate)) 0746 0747 SKGListSKGObjectBase subops; 0748 IFOKDO(err, opdup.getSubOperations(subops)) 0749 int nbsupops = subops.count(); 0750 for (int j = 0; !err && j < nbsupops; ++j) { 0751 SKGSubOperationObject subop(subops.at(j)); 0752 IFOKDO(err, subop.setDate(op.getDate())) 0753 IFOKDO(err, subop.setQuantity(-subop.getQuantity())) 0754 IFOKDO(err, subop.save()) 0755 } 0756 0757 if (i == 0) { 0758 mergedOperations = opdup; 0759 } else { 0760 IFOKDO(err, mergedOperations.mergeSuboperations(opdup)) 0761 } 0762 0763 // Create the duplicate in target account 0764 SKGOperationObject opduptarget; 0765 IFOKDO(err, op.duplicate(opduptarget)) 0766 IFOKDO(err, opduptarget.setDate(op.getDate())) 0767 IFOKDO(err, opduptarget.setParentAccount(iTargetAccount)) 0768 IFOKDO(err, opduptarget.setImported(op.isImported())) 0769 IFOKDO(err, opduptarget.setImportID(op.getImportID())) 0770 IFOKDO(err, opduptarget.setGroupOperation(mergedOperations)) 0771 IFOKDO(err, opduptarget.setStatus(SKGOperationObject::MARKED)) 0772 IFOKDO(err, opduptarget.save()) 0773 IFOKDO(err, mergedOperations.load()) // To reload the modif done by the setGroupOperation 0774 0775 // Check the operation 0776 IFOKDO(err, op.setStatus(SKGOperationObject::CHECKED)) 0777 IFOKDO(err, op.save()) 0778 } 0779 0780 // Check the balance operation 0781 IFOKDO(err, mergedOperations.setPayee(SKGPayeeObject())) 0782 IFOKDO(err, mergedOperations.setStatus(SKGOperationObject::CHECKED)) 0783 IFOKDO(err, mergedOperations.save()) 0784 } 0785 } 0786 0787 return err; 0788 } 0789 0790 QVector< QVector<SKGOperationObject> > SKGAccountObject::getPossibleReconciliations(double iTargetBalance, bool iSearchAllPossibleReconciliation) const 0791 { 0792 SKGTRACEINFUNC(5) 0793 QVector< QVector<SKGOperationObject> > output; 0794 auto* doc = qobject_cast<SKGDocumentBank*>(getDocument()); 0795 if (doc != nullptr) { 0796 // Get unit 0797 SKGServices::SKGUnitInfo unit1 = doc->getPrimaryUnit(); 0798 SKGUnitObject unitAccount; 0799 if (getUnit(unitAccount).isSucceeded()) { 0800 if (!unitAccount.getSymbol().isEmpty()) { 0801 unit1.Symbol = unitAccount.getSymbol(); 0802 unit1.Value = SKGServices::stringToDouble(unitAccount.getAttribute(QStringLiteral("f_CURRENTAMOUNT"))); 0803 } 0804 } 0805 SKGTRACEL(5) << "iTargetBalance=" << doc->formatMoney(iTargetBalance, unit1, false) << SKGENDL; 0806 0807 // Get balance of checked transactions 0808 QString balanceString; 0809 getDocument()->executeSingleSelectSqliteOrder("SELECT f_CHECKED from v_account_display WHERE id=" % SKGServices::intToString(getID()), balanceString); 0810 double balance = SKGServices::stringToDouble(balanceString); 0811 SKGTRACEL(5) << "balance=" << doc->formatMoney(balance, unit1, false) << SKGENDL; 0812 0813 0814 QString zero = doc->formatMoney(0, unit1, false); 0815 QString negativezero = doc->formatMoney(-EPSILON, unit1, false); 0816 QString sdiff = doc->formatMoney(balance - iTargetBalance * unit1.Value, unit1, false); 0817 if (sdiff == zero || sdiff == negativezero) { 0818 // This is an empty soluce 0819 output.push_back(QVector<SKGOperationObject>()); 0820 SKGTRACEL(5) << "empty solution found !!!" << SKGENDL; 0821 } else { 0822 // Get all imported transaction 0823 SKGObjectBase::SKGListSKGObjectBase transactions; 0824 getDocument()->getObjects(QStringLiteral("v_operation"), "rd_account_id=" % SKGServices::intToString(getID()) % " AND t_status!='Y' AND t_template='N' AND t_imported IN ('Y','P') ORDER BY d_date, id", transactions); 0825 int nb = transactions.count(); 0826 0827 if (!iSearchAllPossibleReconciliation) { 0828 // Check if all transactions are a solution 0829 double amount = 0.0; 0830 QVector<SKGOperationObject> list; 0831 list.reserve(nb); 0832 for (int i = 0; i < nb; ++i) { 0833 SKGOperationObject op(transactions.at(i)); 0834 amount += op.getCurrentAmount(); 0835 list.push_back(op); 0836 } 0837 QString sdiff = doc->formatMoney(amount + balance - iTargetBalance * unit1.Value, unit1, false); 0838 if (sdiff == zero || sdiff == negativezero) { 0839 SKGTRACEL(5) << "all transactions are a solution !!!" << SKGENDL; 0840 output.push_back(list); 0841 return output; 0842 } 0843 } 0844 0845 // Search 0846 int nbmax = 500; 0847 SKGTRACEL(5) << "Nb transactions:" << nb << SKGENDL; 0848 if (nb > nbmax) { 0849 SKGTRACEL(5) << "Too many transactions (" << nb << ") ==> Reducing the size of the computation" << SKGENDL; 0850 for (int i = 0; i < nb - nbmax; ++i) { 0851 SKGOperationObject op(transactions.at(0)); 0852 auto amount = op.getCurrentAmount(); 0853 balance += amount; 0854 transactions.removeFirst(); 0855 } 0856 } 0857 output = getPossibleReconciliations(transactions, balance, iTargetBalance, unit1, iSearchAllPossibleReconciliation); 0858 } 0859 } 0860 return output; 0861 } 0862 0863 QVector< QVector<SKGOperationObject> > SKGAccountObject::getPossibleReconciliations(const SKGObjectBase::SKGListSKGObjectBase& iOperations, double iBalance, double iTargetBalance, const SKGServices::SKGUnitInfo& iUnit, bool iSearchAllPossibleReconciliation) const 0864 { 0865 SKGTRACEINFUNC(5) 0866 QVector< QVector<SKGOperationObject> > output; 0867 output.reserve(5); 0868 auto* doc = qobject_cast<SKGDocumentBank*>(getDocument()); 0869 if (doc != nullptr) { 0870 SKGTRACEL(5) << "iTargetBalance=" << doc->formatMoney(iTargetBalance, iUnit, false) << SKGENDL; 0871 0872 // Comparison 0873 QString zero = doc->formatMoney(0, iUnit, false); 0874 QString negativezero = doc->formatMoney(-EPSILON, iUnit, false); 0875 0876 // Check transactions list 0877 int nb = iOperations.count(); 0878 if (nb > 0) { 0879 // Get all transactions of the next date 0880 QVector<SKGOperationObject> nextOperations; 0881 nextOperations.reserve(iOperations.count()); 0882 QString date = iOperations.at(0).getAttribute(QStringLiteral("d_date")); 0883 for (int i = 0; i < nb; ++i) { 0884 SKGOperationObject op(iOperations.at(i)); 0885 if (op.getAttribute(QStringLiteral("d_date")) == date) { 0886 nextOperations.push_back(op); 0887 } else { 0888 break; 0889 } 0890 } 0891 0892 // Get all combination of transactions 0893 int nbNext = nextOperations.count(); 0894 SKGTRACEL(5) << date << ":" << nbNext << " transactions found" << SKGENDL; 0895 std::vector<int> v(nbNext); 0896 for (int i = 0; i < nbNext; ++i) { 0897 v[i] = i; 0898 } 0899 0900 double nextBalance = iBalance; 0901 int index = 0; 0902 if (nbNext > 7) { 0903 SKGTRACEL(5) << "Too many combination: " << factorial(nbNext) << " ==> limited to 5040" << SKGENDL; 0904 nbNext = 7; 0905 } 0906 nb = factorial(nbNext); 0907 bool stopTests = false; 0908 QVector<SKGOperationObject> combi; 0909 QVector<double> combiAmount; 0910 combi.reserve(nbNext); 0911 combiAmount.reserve(nbNext); 0912 do { 0913 // Build the next combination 0914 combi.resize(0); 0915 combiAmount.resize(0); 0916 double sumOperationPositives = 0.0; 0917 double sumOperationsNegatives = 0.0; 0918 for (int i = 0; i < nbNext; ++i) { 0919 const SKGOperationObject& op = nextOperations.at(v[i]); 0920 combi.push_back(op); 0921 auto amount = op.getCurrentAmount(); 0922 combiAmount.push_back(amount); 0923 if (Q_LIKELY(amount < 0)) { 0924 sumOperationsNegatives += amount; 0925 } else if (amount > 0) { 0926 sumOperationPositives += amount; 0927 } 0928 } 0929 0930 // Test the combination 0931 double diff = iBalance - iTargetBalance * iUnit.Value; 0932 double previousDiff = diff; 0933 SKGTRACEL(5) << "Check combination " << (index + 1) << "/" << nb << ": Diff=" << doc->formatMoney(diff, iUnit, false) << SKGENDL; 0934 0935 // Try to find an immediate soluce 0936 int nbop = combi.count(); 0937 for (int j = 0; j < nbop; ++j) { 0938 auto amount = combiAmount.at(j); 0939 diff += amount; 0940 if (Q_UNLIKELY(index == 0)) { 0941 nextBalance += amount; 0942 } 0943 QString sdiff = doc->formatMoney(diff, iUnit, false); 0944 SKGTRACEL(5) << (j + 1) << "/" << nbop << ": Amount=" << amount << " / New diff=" << sdiff << SKGENDL; 0945 if (sdiff == zero || sdiff == negativezero) { 0946 // This is a soluce 0947 auto s = combi.mid(0, j + 1); 0948 if (output.contains(s)) { 0949 SKGTRACEL(5) << "found but already existing !!!" << SKGENDL; 0950 } else { 0951 output.push_back(s); 0952 SKGTRACEL(5) << "found !!!" << SKGENDL; 0953 if (j == nbop - 1 || iSearchAllPossibleReconciliation) { 0954 // No need to test all combinations 0955 SKGTRACEL(5) << "No need to test all combinations" << SKGENDL; 0956 stopTests = true; 0957 } 0958 } 0959 } 0960 } 0961 0962 // Check if tests of all combinations can be cancelled 0963 if ((previousDiff > 0 && previousDiff + sumOperationsNegatives > 0) || (previousDiff < 0 && previousDiff + sumOperationPositives < 0)) { 0964 SKGTRACEL(5) << "No need to test all combinations due to signs of transactions and diffs" << SKGENDL; 0965 stopTests = true; 0966 } 0967 0968 ++index; 0969 } while (index < nb && std::next_permutation(v.begin(), v.end()) && !stopTests); 0970 0971 // Try to find next solutions 0972 auto reconciliations = getPossibleReconciliations(iOperations.mid(nbNext), nextBalance, iTargetBalance, iUnit, iSearchAllPossibleReconciliation); 0973 int nbReconciliations = reconciliations.count(); 0974 output.reserve(nbReconciliations + 5); 0975 for (int i = 0; i < nbReconciliations; ++i) { 0976 QVector<SKGOperationObject> output2 = nextOperations; 0977 output2 = output2 << reconciliations.at(i); 0978 output.push_back(output2); 0979 } 0980 } 0981 } 0982 0983 SKGTRACEL(5) << output.count() << " soluces found" << SKGENDL; 0984 if (!output.isEmpty()) { 0985 SKGTRACEL(5) << "Size of the first soluce: " << output.at(0).count() << SKGENDL; 0986 } 0987 return output; 0988 } 0989 0990 SKGError SKGAccountObject::autoReconcile(double iBalance) 0991 { 0992 SKGError err; 0993 SKGTRACEINFUNCRC(5, err) 0994 0995 // Soluces 0996 auto soluces = getPossibleReconciliations(iBalance); 0997 int nbSoluces = soluces.count(); 0998 if (nbSoluces > 0) { 0999 if (nbSoluces > 1) { 1000 err = getDocument()->sendMessage(i18nc("An information message", "More than one solution is possible for this auto reconciliation.")); 1001 } 1002 1003 // Choose the longest solution 1004 QVector<SKGOperationObject> soluce; 1005 int length = 0; 1006 for (int i = 0; i < nbSoluces; ++i) { 1007 const auto& s = soluces.at(i); 1008 int l = s.count(); 1009 if (l > length) { 1010 soluce = s; 1011 length = l; 1012 } 1013 } 1014 1015 // Check all 1016 SKGTRACEL(5) << length << " transactions marked" << SKGENDL; 1017 for (int i = 0; i < length; ++i) { 1018 SKGOperationObject op(soluce.at(i)); 1019 err = op.setStatus(SKGOperationObject::MARKED); 1020 IFOKDO(err, op.save(true, false)) 1021 } 1022 } else { 1023 err = SKGError(ERR_FAIL, i18nc("Error message", "Can not find the imported transactions for obtaining the expected final balance"), 1024 QString("skg://skrooge_operation_plugin/?title_icon=quickopen&title=" % SKGServices::encodeForUrl(i18nc("Noun, a list of items", "Transactions of account \"%1\" used for auto reconciliation", getDisplayName())) % 1025 "&operationWhereClause=" % SKGServices::encodeForUrl("rd_account_id=" + SKGServices::intToString(getID()) + " AND t_template='N' AND ((t_status='N' AND t_imported IN ('Y','P')) OR t_status='Y')"))); 1026 } 1027 1028 return err; 1029 } 1030 1031 SKGError SKGAccountObject::merge(const SKGAccountObject& iAccount, bool iMergeInitalBalance) 1032 { 1033 SKGError err; 1034 SKGTRACEINFUNCRC(10, err) 1035 1036 // Get initial balances 1037 double balance1 = 0.0; 1038 SKGUnitObject unit1; 1039 err = getInitialBalance(balance1, unit1); 1040 1041 double balance2 = 0.0; 1042 SKGUnitObject unit2; 1043 if (iMergeInitalBalance) { 1044 IFOKDO(err, const_cast<SKGAccountObject*>(&iAccount)->getInitialBalance(balance2, unit2)) 1045 } 1046 1047 // Transfer transactions 1048 SKGObjectBase::SKGListSKGObjectBase ops; 1049 IFOKDO(err, iAccount.getOperations(ops)) 1050 int nb = ops.count(); 1051 for (int i = 0; !err && i < nb; ++i) { 1052 SKGOperationObject op(ops.at(i)); 1053 err = op.setParentAccount(*this); 1054 IFOKDO(err, op.save(true, false)) 1055 } 1056 1057 // Set initial balance 1058 SKGUnitObject unit = unit1; 1059 if (!unit1.exist()) { 1060 unit = unit2; 1061 } 1062 if (unit.exist() && balance2 != 0.0) { 1063 double balance = balance1 + SKGUnitObject::convert(balance2, unit2, unit); 1064 IFOKDO(err, setInitialBalance(balance, unit)) 1065 } 1066 // Remove account 1067 IFOKDO(err, iAccount.remove(false)) 1068 return err; 1069 } 1070 1071