File indexing completed on 2024-04-28 16:30:09
0001 /*************************************************************************** 0002 * SPDX-FileCopyrightText: 2022 S. MANKOWSKI stephane@mankowski.fr 0003 * SPDX-FileCopyrightText: 2022 G. DE BURE support@mankowski.fr 0004 * SPDX-License-Identifier: GPL-3.0-or-later 0005 ***************************************************************************/ 0006 /** @file 0007 * This file defines classes SKGBudgetObject. 0008 * 0009 * @author Stephane MANKOWSKI / Guillaume DE BURE 0010 */ 0011 #include "skgbudgetobject.h" 0012 0013 #include <klocalizedstring.h> 0014 0015 #include "skgbudgetruleobject.h" 0016 #include "skgcategoryobject.h" 0017 #include "skgdefine.h" 0018 #include "skgdocumentbank.h" 0019 #include "skgtraces.h" 0020 #include "skgtransactionmng.h" 0021 0022 SKGBudgetObject::SKGBudgetObject() : SKGBudgetObject(nullptr) 0023 {} 0024 0025 SKGBudgetObject::SKGBudgetObject(SKGDocument* iDocument, int iID) 0026 : SKGObjectBase(iDocument, QStringLiteral("v_budget"), iID) 0027 {} 0028 0029 SKGBudgetObject::~SKGBudgetObject() 0030 = default; 0031 0032 SKGBudgetObject::SKGBudgetObject(const SKGBudgetObject& iObject) 0033 = default; 0034 0035 SKGBudgetObject::SKGBudgetObject(const SKGObjectBase& iObject) 0036 { 0037 if (iObject.getRealTable() == QStringLiteral("budget")) { 0038 copyFrom(iObject); 0039 } else { 0040 *this = SKGObjectBase(iObject.getDocument(), QStringLiteral("v_budget"), iObject.getID()); 0041 } 0042 } 0043 0044 SKGBudgetObject& SKGBudgetObject::operator= (const SKGObjectBase& iObject) 0045 { 0046 copyFrom(iObject); 0047 return *this; 0048 } 0049 0050 QString SKGBudgetObject::getWhereclauseId() const 0051 { 0052 // Could we use the id 0053 QString output = SKGObjectBase::getWhereclauseId(); 0054 if (output.isEmpty()) { 0055 QString y = getAttribute(QStringLiteral("i_year")); 0056 if (!y.isEmpty()) { 0057 output = "i_year=" % y; 0058 } 0059 0060 QString m = getAttribute(QStringLiteral("i_month")); 0061 if (!m.isEmpty()) { 0062 if (!output.isEmpty()) { 0063 output = output % " AND "; 0064 } 0065 output = output % "i_month=" % m; 0066 } 0067 0068 QString r = getAttribute(QStringLiteral("rc_category_id")); 0069 if (!output.isEmpty()) { 0070 output = output % " AND "; 0071 } 0072 output = output % "rc_category_id=" % (r.isEmpty() ? QStringLiteral("0") : r); 0073 } 0074 return output; 0075 } 0076 0077 SKGError SKGBudgetObject::setBudgetedAmount(double iAmount) 0078 { 0079 SKGError err = setAttribute(QStringLiteral("f_budgeted"), SKGServices::doubleToString(iAmount)); 0080 IFOKDO(err, setAttribute(QStringLiteral("f_budgeted_modified"), SKGServices::doubleToString(iAmount))) 0081 IFOKDO(err, setAttribute(QStringLiteral("t_modification_reasons"), QLatin1String(""))) 0082 return err; 0083 } 0084 0085 double SKGBudgetObject::getBudgetedAmount() const 0086 { 0087 return SKGServices::stringToDouble(getAttribute(QStringLiteral("f_budgeted"))); 0088 } 0089 0090 double SKGBudgetObject::getBudgetedModifiedAmount() const 0091 { 0092 return SKGServices::stringToDouble(getAttribute(QStringLiteral("f_budgeted_modified"))); 0093 } 0094 0095 QString SKGBudgetObject::getModificationReasons() const 0096 { 0097 return getAttribute(QStringLiteral("t_modification_reasons")); 0098 } 0099 0100 double SKGBudgetObject::getDelta() const 0101 { 0102 return SKGServices::stringToDouble(getAttribute(QStringLiteral("f_DELTABEFORETRANSFER"))); 0103 } 0104 0105 SKGError SKGBudgetObject::setYear(int iYear) 0106 { 0107 return setAttribute(QStringLiteral("i_year"), SKGServices::intToString(iYear)); 0108 } 0109 0110 int SKGBudgetObject::getYear() const 0111 { 0112 return SKGServices::stringToInt(getAttribute(QStringLiteral("i_year"))); 0113 } 0114 0115 SKGError SKGBudgetObject::setMonth(int iMonth) 0116 { 0117 return setAttribute(QStringLiteral("i_month"), SKGServices::intToString(iMonth)); 0118 } 0119 0120 int SKGBudgetObject::getMonth() const 0121 { 0122 return SKGServices::stringToInt(getAttribute(QStringLiteral("i_month"))); 0123 } 0124 0125 SKGError SKGBudgetObject::setCategory(const SKGCategoryObject& iCategory) 0126 { 0127 return setAttribute(QStringLiteral("rc_category_id"), SKGServices::intToString(iCategory.getID())); 0128 } 0129 0130 SKGError SKGBudgetObject::getCategory(SKGCategoryObject& oCategory) const 0131 { 0132 return getDocument()->getObject(QStringLiteral("v_category"), "id=" % getAttribute(QStringLiteral("rc_category_id")), oCategory); 0133 } 0134 0135 SKGError SKGBudgetObject::enableSubCategoriesInclusion(bool iEnable) 0136 { 0137 return setAttribute(QStringLiteral("t_including_subcategories"), iEnable ? QStringLiteral("Y") : QStringLiteral("N")); 0138 } 0139 0140 bool SKGBudgetObject::isSubCategoriesInclusionEnabled() const 0141 { 0142 return (getAttribute(QStringLiteral("t_including_subcategories")) == QStringLiteral("Y")); 0143 } 0144 0145 SKGError SKGBudgetObject::removeCategory() 0146 { 0147 return setAttribute(QStringLiteral("rc_category_id"), QStringLiteral("0")); 0148 } 0149 0150 SKGError SKGBudgetObject::process() 0151 { 0152 SKGError err; 0153 SKGTRACEINFUNCRC(10, err) 0154 // Main values 0155 int m = getMonth(); 0156 int y = getYear(); 0157 double transferred = SKGServices::stringToDouble(getAttribute(QStringLiteral("f_transferred"))); 0158 0159 // Get budgets rules ordered 0160 SKGObjectBase::SKGListSKGObjectBase budgetsRules; 0161 QString sql = "(t_year_condition='N' OR i_year=" % SKGServices::intToString(y) % ") AND " 0162 "(t_month_condition='N' OR i_month=" % SKGServices::intToString(m) % ") AND " 0163 "(t_category_condition='N' OR rc_category_id=" % getAttribute(QStringLiteral("rc_category_id")) % ") " 0164 "ORDER BY i_ORDER ASC"; 0165 err = getDocument()->getObjects(QStringLiteral("v_budgetrule"), sql, budgetsRules); 0166 0167 int nb = budgetsRules.count(); 0168 if (!err && (nb != 0)) { 0169 err = getDocument()->beginTransaction("#INTERNAL#" % i18nc("Progression step", "Apply budget rules"), nb); 0170 for (int i = 0; !err && i < nb; ++i) { 0171 SKGBudgetRuleObject rule(budgetsRules.at(i)); 0172 0173 // Do we have something to do 0174 SKGBudgetRuleObject::Condition cond = rule.getCondition(); 0175 double delta = getDelta(); 0176 double quantity = rule.getQuantity(); 0177 if (delta != 0.0) { 0178 if (cond == SKGBudgetRuleObject::ALL || (delta < 0 && cond == SKGBudgetRuleObject::NEGATIVE) || (delta > 0 && cond == SKGBudgetRuleObject::POSITIVE)) { 0179 // Compute value to transfer 0180 double value = (rule.isAbolute() ? (cond == SKGBudgetRuleObject::NEGATIVE ? qMax(delta, quantity) : qMin(delta, quantity)) : quantity * (delta - transferred) / 100.0); 0181 0182 // Get impacted budget 0183 SKGBudgetObject impactedBudget = *this; 0184 impactedBudget.resetID(); 0185 0186 SKGBudgetRuleObject::Mode mode = rule.getTransferMode(); 0187 if (mode == SKGBudgetRuleObject::NEXT) { 0188 // Next 0189 int mi = m; 0190 int yi = y; 0191 if (mi == 0) { 0192 // Yearly budget 0193 ++yi; 0194 } else { 0195 // Monthly budget 0196 ++mi; 0197 if (mi == 13) { 0198 mi = 1; 0199 ++yi; 0200 } 0201 } 0202 IFOKDO(err, impactedBudget.setYear(yi)) 0203 IFOKDO(err, impactedBudget.setMonth(mi)) 0204 } else if (mode == SKGBudgetRuleObject::YEAR) { 0205 // Year 0206 IFOKDO(err, impactedBudget.setYear(y)) 0207 IFOKDO(err, impactedBudget.setMonth(0)) 0208 } 0209 0210 // Change category 0211 if (!err && rule.isCategoryChangeEnabled()) { 0212 SKGCategoryObject transferCategory; 0213 rule.getTransferCategory(transferCategory); 0214 err = impactedBudget.setCategory(transferCategory); 0215 } 0216 0217 IFOK(err) { 0218 if (impactedBudget.exist()) { 0219 err = impactedBudget.load(); 0220 QString newBudget = SKGServices::doubleToString(impactedBudget.getBudgetedModifiedAmount() - value); 0221 IFOKDO(err, impactedBudget.setAttribute(QStringLiteral("f_budgeted_modified"), newBudget)) 0222 QString reasons = impactedBudget.getAttribute(QStringLiteral("t_modification_reasons")); 0223 if (!reasons.isEmpty()) { 0224 reasons += '\n'; 0225 } 0226 reasons += i18nc("Message", "Transfer of %1 from '%2' to '%3' due to the rule '%4'", value, getDisplayName(), impactedBudget.getDisplayName(), rule.getDisplayName()); 0227 IFOKDO(err, impactedBudget.setAttribute(QStringLiteral("t_modification_reasons"), reasons)) 0228 IFOKDO(err, impactedBudget.save()) 0229 0230 transferred += value; 0231 IFOKDO(err, setAttribute(QStringLiteral("f_transferred"), SKGServices::doubleToString(transferred))) 0232 IFOKDO(err, save()) 0233 } else { 0234 getDocument()->sendMessage(i18nc("", "Impossible to apply the rule '%1' for budget '%2' because the impacted budget does not exist", rule.getDisplayName(), this->getDisplayName()), SKGDocument::Warning); 0235 } 0236 } 0237 } 0238 } 0239 IFOKDO(err, getDocument()->stepForward(i + 1)) 0240 } 0241 0242 SKGENDTRANSACTION(getDocument(), err) 0243 } 0244 0245 return err; 0246 } 0247 0248 SKGError SKGBudgetObject::createAutomaticBudget(SKGDocumentBank* iDocument, int iYear, int iBaseYear, bool iUseScheduledOperation, bool iRemovePreviousBudget) 0249 { 0250 Q_UNUSED(iUseScheduledOperation) 0251 SKGError err; 0252 SKGTRACEINFUNCRC(10, err) 0253 QString baseYear = SKGServices::intToString(iBaseYear); 0254 int fistMonth = 0; 0255 if (iDocument != nullptr) { 0256 SKGStringListList listTmp; 0257 err = iDocument->executeSelectSqliteOrder( 0258 "SELECT MIN(STRFTIME('%m', d_date)) FROM operation WHERE i_group_id = 0 AND STRFTIME('%Y', d_date) = '" % baseYear % 0259 "' AND t_template='N'", 0260 listTmp); 0261 if (listTmp.count() == 2) { 0262 fistMonth = SKGServices::stringToInt(listTmp.at(1).at(0)); 0263 } 0264 } 0265 if (!err && (iDocument != nullptr)) { 0266 SKGStringListList listTmp; 0267 QString sql = "SELECT t_REALCATEGORY, COUNT(TOT)," 0268 "100*COUNT(TOT)/((CASE WHEN STRFTIME('%Y', date('now', 'localtime'))<>'" % baseYear % "' THEN 12 ELSE STRFTIME('%m', date('now', 'localtime'))-1 END)-" % 0269 SKGServices::intToString(fistMonth) % "+1) AS CPOUR," 0270 "ROUND(TOTAL(TOT)/COUNT(TOT)), MAX(MONTH), TOTAL(TOT) FROM (" 0271 "SELECT t_REALCATEGORY, STRFTIME('%m', d_date) AS MONTH, TOTAL(f_REALCURRENTAMOUNT) AS TOT " 0272 "FROM v_suboperation_consolidated WHERE i_group_id = 0 AND d_DATEYEAR = '" % baseYear % "' AND d_DATEMONTH<STRFTIME('%Y-%m', date('now', 'localtime')) " 0273 "GROUP BY t_REALCATEGORY, d_DATEMONTH" 0274 ") GROUP BY t_REALCATEGORY ORDER BY COUNT(TOT) DESC, (MAX(TOT)-MIN(TOT))/ABS(ROUND(TOTAL(TOT)/COUNT(TOT))) ASC, ROUND(TOTAL(TOT)/COUNT(TOT)) ASC"; 0275 err = iDocument->executeSelectSqliteOrder(sql, listTmp); 0276 0277 0278 // SELECT r.d_date,r.i_period_increment,r.t_period_unit, r.i_nb_times, r. t_times, r.t_CATEGORY, r.f_CURRENTAMOUNT FROM v_recurrentoperation_display r WHERE r.t_TRANSFER='N' 0279 0280 /*double sumBudgeted = 0; 0281 double sumOps = 0;*/ 0282 0283 int nbval = listTmp.count(); 0284 if (!err) { 0285 int step = 0; 0286 err = iDocument->beginTransaction("#INTERNAL#" % i18nc("Progression step", "Create automatic budget"), nbval - 1 + 1 + (iRemovePreviousBudget ? 1 : 0)); 0287 0288 // Remove previous budgets 0289 if (iRemovePreviousBudget) { 0290 IFOKDO(err, iDocument->executeSqliteOrder("DELETE FROM budget WHERE i_year=" % SKGServices::intToString(iYear))) 0291 ++step; 0292 IFOKDO(err, iDocument->stepForward(step)) 0293 } 0294 0295 // Create budgets 0296 for (int i = 1; !err && i < nbval; ++i) { // Ignore header 0297 // Get values 0298 QString catName = listTmp.at(i).at(0); 0299 int count = SKGServices::stringToInt(listTmp.at(i).at(1)); 0300 int countPercent = SKGServices::stringToInt(listTmp.at(i).at(2)); 0301 double amount = SKGServices::stringToDouble(listTmp.at(i).at(3)); 0302 int month = SKGServices::stringToInt(listTmp.at(i).at(4)); 0303 // sumOps += SKGServices::stringToDouble(listTmp.at(i).at(5)); 0304 0305 if (!catName.isEmpty() && (countPercent > 85 || count == 1)) { 0306 SKGCategoryObject cat; 0307 err = iDocument->getObject(QStringLiteral("v_category"), "t_fullname = '" % SKGServices::stringToSqlString(catName) % '\'', cat); 0308 for (int m = fistMonth; !err && m <= (count == 1 ? fistMonth : 12); ++m) { 0309 SKGBudgetObject budget(iDocument); 0310 err = budget.setBudgetedAmount(amount); 0311 IFOKDO(err, budget.setYear(iYear)) 0312 IFOKDO(err, budget.setMonth(count == 1 ? month : m)) 0313 IFOKDO(err, budget.setCategory(cat)) 0314 IFOKDO(err, budget.save()) 0315 0316 // sumBudgeted += amount; 0317 } 0318 } 0319 ++step; 0320 IFOKDO(err, iDocument->stepForward(step)) 0321 } 0322 0323 // Analyze 0324 IFOKDO(err, iDocument->executeSqliteOrder(QStringLiteral("ANALYZE"))) 0325 ++step; 0326 IFOKDO(err, iDocument->stepForward(step)) 0327 0328 SKGENDTRANSACTION(iDocument, err) 0329 } 0330 } 0331 return err; 0332 } 0333 0334 SKGError SKGBudgetObject::balanceBudget(SKGDocumentBank* iDocument, int iYear, int iMonth, bool iBalanceYear) 0335 { 0336 SKGError err; 0337 SKGTRACEINFUNCRC(10, err) 0338 if (iDocument != nullptr) { 0339 err = iDocument->beginTransaction("#INTERNAL#" % i18nc("Progression step", "Balance budgets"), 2); 0340 0341 // Monthly balancing 0342 if (!err && iMonth != -1) { 0343 for (int m = (iMonth == 0 ? 1 : qAsConst(iMonth)); !err && m <= (iMonth == 0 ? 12 : iMonth); ++m) { 0344 SKGStringListList listTmp; 0345 err = iDocument->executeSelectSqliteOrder("SELECT TOTAL(f_budgeted) FROM budget WHERE i_year=" % SKGServices::intToString(iYear) % " AND i_month=" % SKGServices::intToString(m) % " AND rc_category_id<>0", listTmp); 0346 if (!err && listTmp.count() == 2) { 0347 SKGBudgetObject budget(iDocument); 0348 double amount = -SKGServices::stringToDouble(listTmp.at(1).at(0)); 0349 err = budget.setBudgetedAmount(amount); 0350 IFOKDO(err, budget.setYear(iYear)) 0351 IFOKDO(err, budget.setMonth(m)) 0352 IFOKDO(err, budget.save()) 0353 } 0354 } 0355 } 0356 IFOKDO(err, iDocument->stepForward(1)) 0357 0358 // Annual balancing 0359 if (!err && iBalanceYear) { 0360 SKGStringListList listTmp; 0361 err = iDocument->executeSelectSqliteOrder("SELECT TOTAL(f_budgeted) FROM budget WHERE i_year=" % SKGServices::intToString(iYear) % " AND (i_month<>0 OR rc_category_id<>0)", listTmp); 0362 if (!err && listTmp.count() == 2) { 0363 SKGBudgetObject budget(iDocument); 0364 double amount = -SKGServices::stringToDouble(listTmp.at(1).at(0)); 0365 err = budget.setBudgetedAmount(amount); 0366 IFOKDO(err, budget.setYear(iYear)) 0367 IFOKDO(err, budget.setMonth(0)) 0368 IFOKDO(err, budget.save()) 0369 } 0370 } 0371 IFOKDO(err, iDocument->stepForward(2)) 0372 0373 SKGENDTRANSACTION(iDocument, err) 0374 } 0375 return err; 0376 } 0377 0378