1 /***************************************************************************
2 * SPDX-FileCopyrightText: 2021 S. MANKOWSKI stephane@mankowski.fr
3 * SPDX-FileCopyrightText: 2021 G. DE BURE support@mankowski.fr
4 * SPDX-License-Identifier: GPL-3.0-or-later
5 ***************************************************************************/
6 /** @file
7 * This file defines classes SKGBudgetObject.
8 *
9 * @author Stephane MANKOWSKI / Guillaume DE BURE
10 */
11 #include "skgbudgetobject.h"
12
13 #include <klocalizedstring.h>
14
15 #include "skgbudgetruleobject.h"
16 #include "skgcategoryobject.h"
17 #include "skgdefine.h"
18 #include "skgdocumentbank.h"
19 #include "skgtraces.h"
20 #include "skgtransactionmng.h"
21
SKGBudgetObject()22 SKGBudgetObject::SKGBudgetObject() : SKGBudgetObject(nullptr)
23 {}
24
SKGBudgetObject(SKGDocument * iDocument,int iID)25 SKGBudgetObject::SKGBudgetObject(SKGDocument* iDocument, int iID)
26 : SKGObjectBase(iDocument, QStringLiteral("v_budget"), iID)
27 {}
28
29 SKGBudgetObject::~SKGBudgetObject()
30 = default;
31
32 SKGBudgetObject::SKGBudgetObject(const SKGBudgetObject& iObject)
33 = default;
34
SKGBudgetObject(const SKGObjectBase & iObject)35 SKGBudgetObject::SKGBudgetObject(const SKGObjectBase& iObject)
36 {
37 if (iObject.getRealTable() == QStringLiteral("budget")) {
38 copyFrom(iObject);
39 } else {
40 *this = SKGObjectBase(iObject.getDocument(), QStringLiteral("v_budget"), iObject.getID());
41 }
42 }
43
operator =(const SKGObjectBase & iObject)44 SKGBudgetObject& SKGBudgetObject::operator= (const SKGObjectBase& iObject)
45 {
46 copyFrom(iObject);
47 return *this;
48 }
49
getWhereclauseId() const50 QString SKGBudgetObject::getWhereclauseId() const
51 {
52 // Could we use the id
53 QString output = SKGObjectBase::getWhereclauseId();
54 if (output.isEmpty()) {
55 QString y = getAttribute(QStringLiteral("i_year"));
56 if (!y.isEmpty()) {
57 output = "i_year=" % y;
58 }
59
60 QString m = getAttribute(QStringLiteral("i_month"));
61 if (!m.isEmpty()) {
62 if (!output.isEmpty()) {
63 output = output % " AND ";
64 }
65 output = output % "i_month=" % m;
66 }
67
68 QString r = getAttribute(QStringLiteral("rc_category_id"));
69 if (!output.isEmpty()) {
70 output = output % " AND ";
71 }
72 output = output % "rc_category_id=" % (r.isEmpty() ? QStringLiteral("0") : r);
73 }
74 return output;
75 }
76
setBudgetedAmount(double iAmount)77 SKGError SKGBudgetObject::setBudgetedAmount(double iAmount)
78 {
79 SKGError err = setAttribute(QStringLiteral("f_budgeted"), SKGServices::doubleToString(iAmount));
80 IFOKDO(err, setAttribute(QStringLiteral("f_budgeted_modified"), SKGServices::doubleToString(iAmount)))
81 IFOKDO(err, setAttribute(QStringLiteral("t_modification_reasons"), QLatin1String("")))
82 return err;
83 }
84
getBudgetedAmount() const85 double SKGBudgetObject::getBudgetedAmount() const
86 {
87 return SKGServices::stringToDouble(getAttribute(QStringLiteral("f_budgeted")));
88 }
89
getBudgetedModifiedAmount() const90 double SKGBudgetObject::getBudgetedModifiedAmount() const
91 {
92 return SKGServices::stringToDouble(getAttribute(QStringLiteral("f_budgeted_modified")));
93 }
94
getModificationReasons() const95 QString SKGBudgetObject::getModificationReasons() const
96 {
97 return getAttribute(QStringLiteral("t_modification_reasons"));
98 }
99
getDelta() const100 double SKGBudgetObject::getDelta() const
101 {
102 return SKGServices::stringToDouble(getAttribute(QStringLiteral("f_DELTABEFORETRANSFER")));
103 }
104
setYear(int iYear)105 SKGError SKGBudgetObject::setYear(int iYear)
106 {
107 return setAttribute(QStringLiteral("i_year"), SKGServices::intToString(iYear));
108 }
109
getYear() const110 int SKGBudgetObject::getYear() const
111 {
112 return SKGServices::stringToInt(getAttribute(QStringLiteral("i_year")));
113 }
114
setMonth(int iMonth)115 SKGError SKGBudgetObject::setMonth(int iMonth)
116 {
117 return setAttribute(QStringLiteral("i_month"), SKGServices::intToString(iMonth));
118 }
119
getMonth() const120 int SKGBudgetObject::getMonth() const
121 {
122 return SKGServices::stringToInt(getAttribute(QStringLiteral("i_month")));
123 }
124
setCategory(const SKGCategoryObject & iCategory)125 SKGError SKGBudgetObject::setCategory(const SKGCategoryObject& iCategory)
126 {
127 return setAttribute(QStringLiteral("rc_category_id"), SKGServices::intToString(iCategory.getID()));
128 }
129
getCategory(SKGCategoryObject & oCategory) const130 SKGError SKGBudgetObject::getCategory(SKGCategoryObject& oCategory) const
131 {
132 return getDocument()->getObject(QStringLiteral("v_category"), "id=" % getAttribute(QStringLiteral("rc_category_id")), oCategory);
133 }
134
enableSubCategoriesInclusion(bool iEnable)135 SKGError SKGBudgetObject::enableSubCategoriesInclusion(bool iEnable)
136 {
137 return setAttribute(QStringLiteral("t_including_subcategories"), iEnable ? QStringLiteral("Y") : QStringLiteral("N"));
138 }
139
isSubCategoriesInclusionEnabled() const140 bool SKGBudgetObject::isSubCategoriesInclusionEnabled() const
141 {
142 return (getAttribute(QStringLiteral("t_including_subcategories")) == QStringLiteral("Y"));
143 }
144
removeCategory()145 SKGError SKGBudgetObject::removeCategory()
146 {
147 return setAttribute(QStringLiteral("rc_category_id"), QStringLiteral("0"));
148 }
149
process()150 SKGError SKGBudgetObject::process()
151 {
152 SKGError err;
153 SKGTRACEINFUNCRC(10, err)
154 // Main values
155 int m = getMonth();
156 int y = getYear();
157 double transferred = SKGServices::stringToDouble(getAttribute(QStringLiteral("f_transferred")));
158
159 // Get budgets rules ordered
160 SKGObjectBase::SKGListSKGObjectBase budgetsRules;
161 QString sql = "(t_year_condition='N' OR i_year=" % SKGServices::intToString(y) % ") AND "
162 "(t_month_condition='N' OR i_month=" % SKGServices::intToString(m) % ") AND "
163 "(t_category_condition='N' OR rc_category_id=" % getAttribute(QStringLiteral("rc_category_id")) % ") "
164 "ORDER BY i_ORDER ASC";
165 err = getDocument()->getObjects(QStringLiteral("v_budgetrule"), sql, budgetsRules);
166
167 int nb = budgetsRules.count();
168 if (!err && (nb != 0)) {
169 err = getDocument()->beginTransaction("#INTERNAL#" % i18nc("Progression step", "Apply budget rules"), nb);
170 for (int i = 0; !err && i < nb; ++i) {
171 SKGBudgetRuleObject rule(budgetsRules.at(i));
172
173 // Do we have something to do
174 SKGBudgetRuleObject::Condition cond = rule.getCondition();
175 double delta = getDelta();
176 double quantity = rule.getQuantity();
177 if (delta != 0.0) {
178 if (cond == SKGBudgetRuleObject::ALL || (delta < 0 && cond == SKGBudgetRuleObject::NEGATIVE) || (delta > 0 && cond == SKGBudgetRuleObject::POSITIVE)) {
179 // Compute value to transfer
180 double value = (rule.isAbolute() ? (cond == SKGBudgetRuleObject::NEGATIVE ? qMax(delta, quantity) : qMin(delta, quantity)) : quantity * (delta - transferred) / 100.0);
181
182 // Get impacted budget
183 SKGBudgetObject impactedBudget = *this;
184 impactedBudget.resetID();
185
186 SKGBudgetRuleObject::Mode mode = rule.getTransferMode();
187 if (mode == SKGBudgetRuleObject::NEXT) {
188 // Next
189 int mi = m;
190 int yi = y;
191 if (mi == 0) {
192 // Yearly budget
193 ++yi;
194 } else {
195 // Monthly budget
196 ++mi;
197 if (mi == 13) {
198 mi = 1;
199 ++yi;
200 }
201 }
202 IFOKDO(err, impactedBudget.setYear(yi))
203 IFOKDO(err, impactedBudget.setMonth(mi))
204 } else if (mode == SKGBudgetRuleObject::YEAR) {
205 // Year
206 IFOKDO(err, impactedBudget.setYear(y))
207 IFOKDO(err, impactedBudget.setMonth(0))
208 }
209
210 // Change category
211 if (!err && rule.isCategoryChangeEnabled()) {
212 SKGCategoryObject transferCategory;
213 rule.getTransferCategory(transferCategory);
214 err = impactedBudget.setCategory(transferCategory);
215 }
216
217 IFOK(err) {
218 if (impactedBudget.exist()) {
219 err = impactedBudget.load();
220 QString newBudget = SKGServices::doubleToString(impactedBudget.getBudgetedModifiedAmount() - value);
221 IFOKDO(err, impactedBudget.setAttribute(QStringLiteral("f_budgeted_modified"), newBudget))
222 QString reasons = impactedBudget.getAttribute(QStringLiteral("t_modification_reasons"));
223 if (!reasons.isEmpty()) {
224 reasons += '\n';
225 }
226 reasons += i18nc("Message", "Transfer of %1 from '%2' to '%3' due to the rule '%4'", value, getDisplayName(), impactedBudget.getDisplayName(), rule.getDisplayName());
227 IFOKDO(err, impactedBudget.setAttribute(QStringLiteral("t_modification_reasons"), reasons))
228 IFOKDO(err, impactedBudget.save())
229
230 transferred += value;
231 IFOKDO(err, setAttribute(QStringLiteral("f_transferred"), SKGServices::doubleToString(transferred)))
232 IFOKDO(err, save())
233 } else {
234 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);
235 }
236 }
237 }
238 }
239 IFOKDO(err, getDocument()->stepForward(i + 1))
240 }
241
242 SKGENDTRANSACTION(getDocument(), err)
243 }
244
245 return err;
246 }
247
createAutomaticBudget(SKGDocumentBank * iDocument,int iYear,int iBaseYear,bool iUseScheduledOperation,bool iRemovePreviousBudget)248 SKGError SKGBudgetObject::createAutomaticBudget(SKGDocumentBank* iDocument, int iYear, int iBaseYear, bool iUseScheduledOperation, bool iRemovePreviousBudget)
249 {
250 Q_UNUSED(iUseScheduledOperation)
251 SKGError err;
252 SKGTRACEINFUNCRC(10, err)
253 QString baseYear = SKGServices::intToString(iBaseYear);
254 int fistMonth = 0;
255 if (iDocument != nullptr) {
256 SKGStringListList listTmp;
257 err = iDocument->executeSelectSqliteOrder(
258 "SELECT MIN(STRFTIME('%m', d_date)) FROM operation WHERE i_group_id = 0 AND STRFTIME('%Y', d_date) = '" % baseYear %
259 "' AND t_template='N'",
260 listTmp);
261 if (listTmp.count() == 2) {
262 fistMonth = SKGServices::stringToInt(listTmp.at(1).at(0));
263 }
264 }
265 if (!err && (iDocument != nullptr)) {
266 SKGStringListList listTmp;
267 QString sql = "SELECT t_REALCATEGORY, COUNT(TOT),"
268 "100*COUNT(TOT)/((CASE WHEN STRFTIME('%Y', date('now', 'localtime'))<>'" % baseYear % "' THEN 12 ELSE STRFTIME('%m', date('now', 'localtime'))-1 END)-" %
269 SKGServices::intToString(fistMonth) % "+1) AS CPOUR,"
270 "ROUND(TOTAL(TOT)/COUNT(TOT)), MAX(MONTH), TOTAL(TOT) FROM ("
271 "SELECT t_REALCATEGORY, STRFTIME('%m', d_date) AS MONTH, TOTAL(f_REALCURRENTAMOUNT) AS TOT "
272 "FROM v_suboperation_consolidated WHERE i_group_id = 0 AND d_DATEYEAR = '" % baseYear % "' AND d_DATEMONTH<STRFTIME('%Y-%m', date('now', 'localtime')) "
273 "GROUP BY t_REALCATEGORY, d_DATEMONTH"
274 ") 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";
275 err = iDocument->executeSelectSqliteOrder(sql, listTmp);
276
277
278 // 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'
279
280 /*double sumBudgeted = 0;
281 double sumOps = 0;*/
282
283 int nbval = listTmp.count();
284 if (!err) {
285 int step = 0;
286 err = iDocument->beginTransaction("#INTERNAL#" % i18nc("Progression step", "Create automatic budget"), nbval - 1 + 1 + (iRemovePreviousBudget ? 1 : 0));
287
288 // Remove previous budgets
289 if (iRemovePreviousBudget) {
290 IFOKDO(err, iDocument->executeSqliteOrder("DELETE FROM budget WHERE i_year=" % SKGServices::intToString(iYear)))
291 ++step;
292 IFOKDO(err, iDocument->stepForward(step))
293 }
294
295 // Create budgets
296 for (int i = 1; !err && i < nbval; ++i) { // Ignore header
297 // Get values
298 QString catName = listTmp.at(i).at(0);
299 int count = SKGServices::stringToInt(listTmp.at(i).at(1));
300 int countPercent = SKGServices::stringToInt(listTmp.at(i).at(2));
301 double amount = SKGServices::stringToDouble(listTmp.at(i).at(3));
302 int month = SKGServices::stringToInt(listTmp.at(i).at(4));
303 // sumOps += SKGServices::stringToDouble(listTmp.at(i).at(5));
304
305 if (!catName.isEmpty() && (countPercent > 85 || count == 1)) {
306 SKGCategoryObject cat;
307 err = iDocument->getObject(QStringLiteral("v_category"), "t_fullname = '" % SKGServices::stringToSqlString(catName) % '\'', cat);
308 for (int m = fistMonth; !err && m <= (count == 1 ? fistMonth : 12); ++m) {
309 SKGBudgetObject budget(iDocument);
310 err = budget.setBudgetedAmount(amount);
311 IFOKDO(err, budget.setYear(iYear))
312 IFOKDO(err, budget.setMonth(count == 1 ? month : m))
313 IFOKDO(err, budget.setCategory(cat))
314 IFOKDO(err, budget.save())
315
316 // sumBudgeted += amount;
317 }
318 }
319 ++step;
320 IFOKDO(err, iDocument->stepForward(step))
321 }
322
323 // Analyze
324 IFOKDO(err, iDocument->executeSqliteOrder(QStringLiteral("ANALYZE")))
325 ++step;
326 IFOKDO(err, iDocument->stepForward(step))
327
328 SKGENDTRANSACTION(iDocument, err)
329 }
330 }
331 return err;
332 }
333
balanceBudget(SKGDocumentBank * iDocument,int iYear,int iMonth,bool iBalanceYear)334 SKGError SKGBudgetObject::balanceBudget(SKGDocumentBank* iDocument, int iYear, int iMonth, bool iBalanceYear)
335 {
336 SKGError err;
337 SKGTRACEINFUNCRC(10, err)
338 if (iDocument != nullptr) {
339 err = iDocument->beginTransaction("#INTERNAL#" % i18nc("Progression step", "Balance budgets"), 2);
340
341 // Monthly balancing
342 if (!err && iMonth != -1) {
343 for (int m = (iMonth == 0 ? 1 : qAsConst(iMonth)); !err && m <= (iMonth == 0 ? 12 : iMonth); ++m) {
344 SKGStringListList listTmp;
345 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);
346 if (!err && listTmp.count() == 2) {
347 SKGBudgetObject budget(iDocument);
348 double amount = -SKGServices::stringToDouble(listTmp.at(1).at(0));
349 err = budget.setBudgetedAmount(amount);
350 IFOKDO(err, budget.setYear(iYear))
351 IFOKDO(err, budget.setMonth(m))
352 IFOKDO(err, budget.save())
353 }
354 }
355 }
356 IFOKDO(err, iDocument->stepForward(1))
357
358 // Annual balancing
359 if (!err && iBalanceYear) {
360 SKGStringListList listTmp;
361 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);
362 if (!err && listTmp.count() == 2) {
363 SKGBudgetObject budget(iDocument);
364 double amount = -SKGServices::stringToDouble(listTmp.at(1).at(0));
365 err = budget.setBudgetedAmount(amount);
366 IFOKDO(err, budget.setYear(iYear))
367 IFOKDO(err, budget.setMonth(0))
368 IFOKDO(err, budget.save())
369 }
370 }
371 IFOKDO(err, iDocument->stepForward(2))
372
373 SKGENDTRANSACTION(iDocument, err)
374 }
375 return err;
376 }
377
378
379