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 is Skrooge plugin to generate report.
8  *
9  * @author Stephane MANKOWSKI / Guillaume DE BURE
10  */
11 #include "skgreportpluginwidget.h"
12 
13 #include <klocalizedstring.h>
14 
15 #include <qgraphicsscene.h>
16 #include <qlistwidget.h>
17 #include <qmenu.h>
18 #include <qwidgetaction.h>
19 
20 #include "skgbankincludes.h"
21 #include "skgcolorbutton.h"
22 #include "skgmainpanel.h"
23 #include "skgreport_settings.h"
24 #include "skgreportplugin.h"
25 #include "skgtraces.h"
26 
27 /**
28  * The size of the forecast
29  */
30 static const int FORECASTMAX = 100;
31 
SKGReportPluginWidget(QWidget * iParent,SKGDocumentBank * iDocument,bool iMinimmumMode)32 SKGReportPluginWidget::SKGReportPluginWidget(QWidget* iParent, SKGDocumentBank* iDocument, bool iMinimmumMode)
33     : SKGTabPage(iParent, iDocument), m_openReportAction(nullptr), m_openAction(nullptr), m_mode(0), m_nbLevelLines(0), m_nbLevelColumns(0), m_refreshNeeded(true)
34 {
35     SKGTRACEINFUNC(10)
36     if (iDocument == nullptr) {
37         return;
38     }
39 
40     m_timer.setSingleShot(true);
41     connect(&m_timer, &QTimer::timeout, this, [ = ]() {
42         this->dataModified();
43     }, Qt::QueuedConnection);
44 
45     ui.setupUi(this);
46 
47     ui.kCorrectedBy->setDocument(iDocument);
48     ui.kCorrectedBy->setCurrentIndex(0);
49     ui.kCorrectedBy->setWhereClauseCondition(QStringLiteral("t_type='I'"));
50 
51     ui.kCorrectedByMode->addItem(QStringLiteral("x"));
52     ui.kCorrectedByMode->addItem(QStringLiteral("/"));
53 
54     ui.kLineLabel->setText(QLatin1String(""));
55 
56     ui.kLineUp->setIcon(SKGServices::fromTheme(QStringLiteral("format-indent-more")));
57     ui.kLineDown->setIcon(SKGServices::fromTheme(QStringLiteral("format-indent-less")));
58     ui.kColUp->setIcon(SKGServices::fromTheme(QStringLiteral("format-indent-more")));
59     ui.kColDown->setIcon(SKGServices::fromTheme(QStringLiteral("format-indent-less")));
60 
61     ui.kLineAdd->setIcon(SKGServices::fromTheme(QStringLiteral("arrow-right")));
62     ui.kLineRemove->setIcon(SKGServices::fromTheme(QStringLiteral("edit-delete")));
63 
64     ui.kMode->addItem(SKGServices::fromTheme(QStringLiteral("office-chart-line")), i18nc("Display graph values as the sum of operations amount", "Sum of operations"), 0);
65     ui.kMode->addItem(SKGServices::fromTheme(QStringLiteral("office-chart-line-stacked")), i18nc("Display graph values as the cumulated sum of operations amount", "Cumulated sum of operations"), 1);
66     ui.kMode->addItem(SKGServices::fromTheme(QStringLiteral("office-chart-bar-percentage")), i18nc("Display graph values in base 100", "Base 100"), 7);
67     ui.kMode->addItem(SKGServices::fromTheme(QStringLiteral("office-chart-bar-percentage")), i18nc("Display graph values in base 100", "Cumulated sum in base 100"), 8);
68     ui.kMode->addItem(SKGServices::fromTheme(QStringLiteral("office-chart-area")), i18nc("Display graph values in percentage", "Percent of columns"), 2);
69     ui.kMode->addItem(SKGServices::fromTheme(QStringLiteral("office-chart-area")), i18nc("Display graph values in percentage", "Absolute percent of columns"), 5);
70     ui.kMode->addItem(SKGServices::fromTheme(QStringLiteral("office-chart-line")), i18nc("Display graph values in percentage", "Percent of lines"), 4);
71     ui.kMode->addItem(SKGServices::fromTheme(QStringLiteral("office-chart-line")), i18nc("Display graph values in percentage", "Absolute percent of lines"), 6);
72     ui.kMode->addItem(SKGServices::fromTheme(QStringLiteral("irc-operator")), i18nc("Display graph values as a number of operations (or transaction)", "Count number of operations"), 3);
73 
74     ui.kForecastCmb->addItem(i18nc("Noun", "None"), 0);
75     ui.kForecastCmb->addItem(SKGServices::fromTheme(QStringLiteral("download-later")), i18nc("Noun", "Moving average"), 2);
76     ui.kForecastCmb->addItem(SKGServices::fromTheme(QStringLiteral("download-later")), i18nc("Noun", "Weighted moving average"), 3);
77     // TODO(Stephane MANKOWSKI): ui.kForecastCmb->addItem(SKGServices::fromTheme("applications-education-mathematics"), i18nc("Noun", "Interest rate") , 5);
78     ui.kForecastCmb->addItem(SKGServices::fromTheme(QStringLiteral("chronometer")), i18nc("Noun", "Schedule"), 1);
79     ui.kForecastCmb->addItem(SKGServices::fromTheme(QStringLiteral("view-calendar-whatsnext")), i18nc("Noun", "Budget"), 4);
80 
81     ui.kWidgetSelector->addButton(SKGServices::fromTheme(QStringLiteral("configure")), i18n("Setup Report"), i18n("Display the edit panel for report"), ui.setupWidget);
82     connect(ui.kWidgetSelector, &SKGWidgetSelector::selectedModeChanged, this, &SKGReportPluginWidget::onBtnModeClicked);
83 
84     // Set colors
85     setSettings();
86     if (SKGMainPanel::getMainPanel() != nullptr) {
87         connect(SKGMainPanel::getMainPanel(), &SKGMainPanel::settingsChanged, this, &SKGReportPluginWidget::setSettings);
88     }
89 
90     // Build contextual menu
91     QMenu* tableMenu = ui.kTableWithGraph->getTableContextualMenu();
92     if (tableMenu != nullptr) {
93         tableMenu->addSeparator();
94         m_openAction = tableMenu->addAction(SKGServices::fromTheme(QStringLiteral("quickopen")), i18nc("Verb", "Open..."));
95         if (m_openAction != nullptr) {
96             m_openAction->setEnabled(false);
97         }
98         QStringList overlayopen;
99         overlayopen.push_back(QStringLiteral("quickopen"));
100         m_openReportAction = tableMenu->addAction(SKGServices::fromTheme(QStringLiteral("view-statistics"), overlayopen), i18nc("Verb", "Open report..."));
101         if (m_openReportAction != nullptr) {
102             m_openReportAction->setEnabled(false);
103         }
104     }
105 
106     QMenu* graphMenu = ui.kTableWithGraph->getGraphContextualMenu();
107     SKGComboBox* secondColumns = nullptr;
108     if (iMinimmumMode && graphMenu != nullptr) {
109         graphMenu->addSeparator();
110         QList<QCheckBox*> list;
111         list << ui.kIncomes << ui.kExpenses << ui.kGrouped << ui.kTransfers << ui.kTracked;
112         for (auto actref : qAsConst(list)) {
113             auto act = graphMenu->addAction(actref->text());
114             if (act != nullptr) {
115                 act->setCheckable(true);
116                 act->setChecked(actref->isChecked());
117                 connect(act, &QAction::triggered, actref, &QCheckBox::setChecked);
118                 connect(actref, &QCheckBox::toggled, act, &QAction::setChecked);
119             }
120         }
121         graphMenu->addSeparator();
122         auto act = new QWidgetAction(this);
123 
124         secondColumns = new SKGComboBox(this);
125 
126         connect(ui.kColumns, static_cast<void (SKGComboBox::*)(const QString&)>(&SKGComboBox::currentTextChanged), secondColumns, &SKGComboBox::setCurrentText);
127         connect(secondColumns, static_cast<void (SKGComboBox::*)(const QString&)>(&SKGComboBox::currentTextChanged), ui.kColumns, &SKGComboBox::setCurrentText);
128 
129         act->setDefaultWidget(secondColumns);
130         graphMenu->addAction(act);
131 
132         graphMenu->addSeparator();
133         graphMenu->addAction(m_openAction);
134         graphMenu->addAction(m_openReportAction);
135     }
136 
137     connect(m_openAction, &QAction::triggered, this, &SKGReportPluginWidget::onOpen);
138     connect(m_openReportAction, &QAction::triggered, this, &SKGReportPluginWidget::onOpenReport);
139 
140     // Init comboboxes
141     m_attsForColumns << QStringLiteral("d_date") << QStringLiteral("d_DATEWEEK") << QStringLiteral("d_DATEMONTH") << QStringLiteral("d_DATEQUARTER") << QStringLiteral("d_DATESEMESTER") << QStringLiteral("d_DATEYEAR");
142     m_attsForLines << QStringLiteral("#NOTHING#") <<
143                    QStringLiteral("t_BANK") << QStringLiteral("t_ACCOUNTTYPE") << QStringLiteral("t_ACCOUNT") << QStringLiteral("t_TOACCOUNT") <<
144                    QStringLiteral("t_REALCATEGORY") <<
145                    QStringLiteral("t_PAYEE") <<
146                    QStringLiteral("t_TYPEEXPENSENLS") <<
147                    QStringLiteral("t_mode") <<
148                    QStringLiteral("t_status") <<
149                    QStringLiteral("t_UNITTYPE") << QStringLiteral("t_UNIT") <<
150                    QStringLiteral("t_REALREFUND") <<
151                    QStringLiteral("t_TRANSFER");
152 
153     // Adding properties of operations and sub operations
154     QStringList properties;
155     iDocument->getDistinctValues(QStringLiteral("parameters"), QStringLiteral("'p_'||t_name"), QStringLiteral("(t_uuid_parent like '%-operation' OR t_uuid_parent like '%-suboperation')  AND t_name NOT LIKE 'SKG_%'"), properties);
156     int nb = properties.count();
157     m_attsForLines.reserve(m_attsForLines.count() + nb);
158     for (int i = 0; i < nb; ++i) {
159         m_attsForLines.push_back(properties.at(i));
160     }
161 
162     // Adding properties of categories
163     iDocument->getDistinctValues(QStringLiteral("parameters"), '\'' + iDocument->getDisplay(QStringLiteral("t_category")) + ".p_'||t_name", QStringLiteral("t_uuid_parent like '%-category' AND t_name NOT LIKE 'SKG_%'"), properties);
164     nb = properties.count();
165     m_attsForLines.reserve(m_attsForLines.count() + nb);
166     for (int i = 0; i < nb; ++i) {
167         m_attsForLines.push_back(properties.at(i));
168     }
169 
170     // Adding properties of accounts
171     iDocument->getDistinctValues(QStringLiteral("parameters"), '\'' + iDocument->getDisplay(QStringLiteral("t_account")) + ".p_'||t_name", QStringLiteral("t_uuid_parent like '%-account' AND t_name NOT LIKE 'SKG_%'"), properties);
172     nb = properties.count();
173     m_attsForLines.reserve(m_attsForLines.count() + nb);
174     for (int i = 0; i < nb; ++i) {
175         m_attsForLines.push_back(properties.at(i));
176     }
177 
178     // Adding properties of payee
179     iDocument->getDistinctValues(QStringLiteral("parameters"), '\'' + iDocument->getDisplay(QStringLiteral("t_payee")) + ".p_'||t_name", QStringLiteral("t_uuid_parent like '%-payee' AND t_name NOT LIKE 'SKG_%'"), properties);
180     nb = properties.count();
181     m_attsForLines.reserve(m_attsForLines.count() + nb);
182     for (int i = 0; i < nb; ++i) {
183         m_attsForLines.push_back(properties.at(i));
184     }
185 
186     // Adding properties of unit
187     iDocument->getDistinctValues(QStringLiteral("parameters"), '\'' + iDocument->getDisplay(QStringLiteral("t_unit")) + ".p_'||t_name", QStringLiteral("t_uuid_parent like '%-unit' AND t_name NOT LIKE 'SKG_%'"), properties);
188     nb = properties.count();
189     m_attsForLines.reserve(m_attsForLines.count() + nb);
190     for (int i = 0; i < nb; ++i) {
191         m_attsForLines.push_back(properties.at(i));
192     }
193 
194     if (!iMinimmumMode) {
195         m_attsForColumns += m_attsForLines;
196     }
197 
198     for (const auto& att : qAsConst(m_attsForColumns)) {
199         ui.kColumns->addItem(iDocument->getIcon(att), iDocument->getDisplay(att));
200         if (secondColumns) {
201             secondColumns->addItem(iDocument->getIcon(att), iDocument->getDisplay(att));
202         }
203     }
204 
205     for (const auto& att : qAsConst(m_attsForLines)) {
206         ui.kLines->addItem(iDocument->getIcon(att), iDocument->getDisplay(att));
207     }
208 
209     ui.kColumns->setCurrentIndex(2);
210 
211     connect(ui.kTableWithGraph, &SKGTableWithGraph::selectionChanged, this, &SKGReportPluginWidget::onSelectionChanged);
212 
213     // Refresh
214     connect(ui.kPeriod, &SKGPeriodEdit::changed, this, &SKGReportPluginWidget::refresh, Qt::QueuedConnection);
215 
216     connect(ui.kColumns, static_cast<void (SKGComboBox::*)(const QString&)>(&SKGComboBox::currentTextChanged), this, &SKGReportPluginWidget::refresh, Qt::QueuedConnection);
217     connect(ui.kLines, static_cast<void (SKGComboBox::*)(const QString&)>(&SKGComboBox::currentTextChanged), this, &SKGReportPluginWidget::refresh, Qt::QueuedConnection);
218     connect(ui.kMode, static_cast<void (SKGComboBox::*)(const QString&)>(&SKGComboBox::currentTextChanged), this, &SKGReportPluginWidget::refresh, Qt::QueuedConnection);
219     connect(ui.kIncomes, &QCheckBox::stateChanged, this, &SKGReportPluginWidget::refresh, Qt::QueuedConnection);
220     connect(ui.kExpenses, &QCheckBox::stateChanged, this, &SKGReportPluginWidget::refresh, Qt::QueuedConnection);
221     connect(ui.kTransfers, &QCheckBox::stateChanged, this, &SKGReportPluginWidget::refresh, Qt::QueuedConnection);
222     connect(ui.kGrouped, &QCheckBox::stateChanged, this, &SKGReportPluginWidget::refresh, Qt::QueuedConnection);
223     connect(ui.kTracked, &QCheckBox::stateChanged, this, &SKGReportPluginWidget::refresh, Qt::QueuedConnection);
224     connect(ui.kForecastCmb, static_cast<void (SKGComboBox::*)(const QString&)>(&SKGComboBox::currentTextChanged), this, &SKGReportPluginWidget::refresh, Qt::QueuedConnection);
225     connect(ui.kForecastValue, static_cast<void (QSlider::*)(int)>(&QSlider::valueChanged), this, &SKGReportPluginWidget::refresh, Qt::QueuedConnection);
226     connect(ui.kCorrectedBy, static_cast<void (SKGUnitComboBox::*)(int)>(&SKGUnitComboBox::currentIndexChanged), this, &SKGReportPluginWidget::refresh, Qt::QueuedConnection);
227     connect(ui.kCorrectedByMode, static_cast<void (SKGComboBox::*)(const QString&)>(&SKGComboBox::currentTextChanged), this, &SKGReportPluginWidget::refresh, Qt::QueuedConnection);
228 
229     connect(getDocument(), &SKGDocument::tableModified, this, &SKGReportPluginWidget::dataModified);
230     connect(SKGMainPanel::getMainPanel(), &SKGMainPanel::currentPageChanged, this, &SKGReportPluginWidget::pageChanged, Qt::QueuedConnection);
231     connect(SKGMainPanel::getMainPanel(), &SKGMainPanel::pageClosed, this, &SKGReportPluginWidget::pageChanged, Qt::QueuedConnection);
232     connect(ui.kOtherFilters, &QListWidget::itemChanged, this, &SKGReportPluginWidget::pageChanged, Qt::QueuedConnection);
233     connect(ui.kOtherFilters, &QListWidget::itemChanged, this, &SKGReportPluginWidget::refresh, Qt::QueuedConnection);
234 
235     connect(ui.kTableWithGraph, &SKGTableWithGraph::cellDoubleClicked, this, &SKGReportPluginWidget::onDoubleClick);
236     connect(ui.kLineRemove, &QToolButton::clicked, this, &SKGReportPluginWidget::onRemoveLine);
237     connect(ui.kLineAdd, &QToolButton::clicked, this, &SKGReportPluginWidget::onAddLine);
238     connect(ui.kLineDown, &QToolButton::clicked, this, &SKGReportPluginWidget::onOneLevelLess);
239     connect(ui.kLineUp, &QToolButton::clicked, this, &SKGReportPluginWidget::onOneLevelMore);
240     connect(ui.kColDown, &QToolButton::clicked, this, &SKGReportPluginWidget::onOneLevelLess);
241     connect(ui.kColUp, &QToolButton::clicked, this, &SKGReportPluginWidget::onOneLevelMore);
242     connect(ui.kGrouped, &QCheckBox::toggled, ui.kTransfers, &QCheckBox::setEnabled);
243 
244     if (iMinimmumMode) {
245         ui.kTableWithGraph->getShowWidget()->setState(QStringLiteral("\"graph\""));
246         ui.kWidgetSelector->setSelectedMode(-1);
247     } else {
248         ui.kWidgetSelector->setSelectedMode(0);
249     }
250     onBtnModeClicked(ui.kWidgetSelector->getSelectedMode());
251 }
252 
~SKGReportPluginWidget()253 SKGReportPluginWidget::~SKGReportPluginWidget()
254 {
255     SKGTRACEINFUNC(10)
256     m_openAction = nullptr;
257     m_openReportAction = nullptr;
258 }
259 
getState()260 QString SKGReportPluginWidget::getState()
261 {
262     SKGTRACEINFUNC(10)
263     QDomDocument doc(QStringLiteral("SKGML"));
264     QDomElement root = doc.createElement(QStringLiteral("parameters"));
265     doc.appendChild(root);
266 
267     root.setAttribute(QStringLiteral("columns"), m_attsForColumns.value(ui.kColumns->currentIndex()));
268 
269     QString lineString;
270     int nb = m_attsForLinesAdded.count();
271     for (int i = 0; i < nb; ++i) {
272         lineString += m_attsForLinesAdded.at(i);
273         if (i < nb - 1) {
274             lineString += OBJECTSEPARATOR;
275         }
276     }
277 
278     root.setAttribute(QStringLiteral("lines"), lineString);
279     root.setAttribute(QStringLiteral("lines2"), m_attsForLines.value(ui.kLines->currentIndex()));
280     root.setAttribute(QStringLiteral("mode"), SKGServices::intToString(ui.kMode->itemData(ui.kMode->currentIndex()).toInt()));
281     root.setAttribute(QStringLiteral("periodDef"), ui.kPeriod->getState());
282     root.setAttribute(QStringLiteral("incomes"), ui.kIncomes->isChecked() ? QStringLiteral("Y") : QStringLiteral("N"));
283     root.setAttribute(QStringLiteral("expenses"), ui.kExpenses->isChecked() ? QStringLiteral("Y") : QStringLiteral("N"));
284     root.setAttribute(QStringLiteral("transfers"), ui.kTransfers->isChecked() ? QStringLiteral("Y") : QStringLiteral("N"));
285     root.setAttribute(QStringLiteral("grouped"), ui.kGrouped->isChecked() ? QStringLiteral("Y") : QStringLiteral("N"));
286     root.setAttribute(QStringLiteral("tracked"), ui.kTracked->isChecked() ? QStringLiteral("Y") : QStringLiteral("N"));
287     root.setAttribute(QStringLiteral("currentPage"), SKGServices::intToString(ui.kWidgetSelector->getSelectedMode()));
288     root.setAttribute(QStringLiteral("tableAndGraphState"), ui.kTableWithGraph->getState());
289     root.setAttribute(QStringLiteral("nbLevelLines"), SKGServices::intToString(m_nbLevelLines));
290     root.setAttribute(QStringLiteral("nbLevelColumns"), SKGServices::intToString(m_nbLevelColumns));
291     root.setAttribute(QStringLiteral("forecast"), SKGServices::intToString(ui.kForecastCmb->itemData(ui.kForecastCmb->currentIndex()).toInt()));
292     root.setAttribute(QStringLiteral("forecastValue"), SKGServices::intToString(ui.kForecastValue->value()));
293     root.setAttribute(QStringLiteral("zoomPosition"), SKGServices::intToString(zoomPosition()));
294     root.setAttribute(QStringLiteral("correctedby"), ui.kCorrectedBy->currentText());
295     root.setAttribute(QStringLiteral("correctedbymode"), ui.kCorrectedByMode->currentText());
296 
297     // Addition of other filter
298     QString wc;
299     QString title = root.attribute(QStringLiteral("title"));
300     QString title_icon;
301     nb = ui.kOtherFilters->count();
302     for (int i = 0; i < nb; ++i) {
303         QListWidgetItem* item = ui.kOtherFilters->item(i);
304         if (item->checkState() == Qt::Checked) {
305             if (!wc.isEmpty()) {
306                 wc = '(' % wc % ") AND (" % item->data(1000).toString() % ')';
307             } else {
308                 wc = item->data(1000).toString();
309             }
310             title_icon = item->data(1001).toString();
311 
312             title += item->text();
313         }
314     }
315 
316     if (!wc.isEmpty()) {
317         root.setAttribute(QStringLiteral("title"), title);
318         root.setAttribute(QStringLiteral("operationWhereClause"), wc);
319         root.setAttribute(QStringLiteral("title_icon"), title_icon);
320     }
321 
322     return doc.toString();
323 }
324 
setState(const QString & iState)325 void SKGReportPluginWidget::setState(const QString& iState)
326 {
327     SKGTRACEINFUNC(10)
328     m_timer.stop();
329 
330     QDomDocument doc(QStringLiteral("SKGML"));
331     doc.setContent(iState);
332     QDomElement root = doc.documentElement();
333 
334     QString columns = root.attribute(QStringLiteral("columns"));
335     QString lines = root.attribute(QStringLiteral("lines"));
336     QString lines2 = root.attribute(QStringLiteral("lines2"));
337     QString mode = root.attribute(QStringLiteral("mode"));
338     QString incomes = root.attribute(QStringLiteral("incomes"));
339     QString expenses = root.attribute(QStringLiteral("expenses"));
340     QString transfers = root.attribute(QStringLiteral("transfers"));
341     QString grouped = root.attribute(QStringLiteral("grouped"));
342     QString tracked = root.attribute(QStringLiteral("tracked"));
343     QString currentPage = root.attribute(QStringLiteral("currentPage"));
344     QString forecast = root.attribute(QStringLiteral("forecast"));
345     QString forecastValue = root.attribute(QStringLiteral("forecastValue"));
346     QString tableAndGraphState = root.attribute(QStringLiteral("tableAndGraphState"));
347     QString title = root.attribute(QStringLiteral("title"));
348     QString title_icon = root.attribute(QStringLiteral("title_icon"));
349     QString wc = root.attribute(QStringLiteral("operationWhereClause"));
350     QString nbLevelLinesString = root.attribute(QStringLiteral("nbLevelLines"));
351     QString nbLevelColumnsString = root.attribute(QStringLiteral("nbLevelColumns"));
352     QString zoomPositionString = root.attribute(QStringLiteral("zoomPosition"));
353     QString correctedby = root.attribute(QStringLiteral("correctedby"));
354     QString correctedbymode = root.attribute(QStringLiteral("correctedbymode"));
355     QString periodDef = root.attribute(QStringLiteral("periodDef"));
356 
357     // Default values
358     if (nbLevelLinesString.isEmpty()) {
359         nbLevelLinesString = '0';
360     }
361     if (nbLevelColumnsString.isEmpty()) {
362         nbLevelColumnsString = '0';
363     }
364     if (columns.isEmpty()) {
365         columns = m_attsForColumns.at(2);
366     }
367     if (lines2.isEmpty()) {
368         lines2 = m_attsForLines.at(0);
369     }
370     if (mode.isEmpty()) {
371         mode = '0';
372     }
373     if (incomes.isEmpty()) {
374         incomes = 'Y';
375     }
376     if (expenses.isEmpty()) {
377         expenses = 'Y';
378     }
379     if (transfers.isEmpty()) {
380         transfers = 'N';
381     }
382     if (grouped.isEmpty()) {
383         grouped = 'Y';
384     }
385     if (tracked.isEmpty()) {
386         tracked = 'Y';
387     }
388     if (currentPage.isEmpty()) {
389         currentPage = '0';
390     }
391     if (forecast.isEmpty()) {
392         forecast = '0';
393     }
394     if (forecastValue.isEmpty()) {
395         forecastValue = '0';
396     }
397 
398     m_nbLevelLines = SKGServices::stringToInt(nbLevelLinesString);
399     m_nbLevelColumns = SKGServices::stringToInt(nbLevelColumnsString);
400     ui.kColumns->setCurrentIndex(m_attsForColumns.indexOf(columns));
401     ui.kLines->setCurrentIndex(m_attsForLines.indexOf(lines2));
402     m_attsForLinesAdded.clear();
403     if (!lines.isEmpty()) {
404         m_attsForLinesAdded = lines.split(OBJECTSEPARATOR);
405     }
406     ui.kMode->setCurrentIndex(ui.kMode->findData(SKGServices::stringToInt(mode)));
407     if (periodDef.isEmpty()) {
408         QString period = root.attribute(QStringLiteral("period"));
409         QString interval = root.attribute(QStringLiteral("interval"));
410         QString nb_interval = root.attribute(QStringLiteral("nb_intervals"));
411         QString timeline = root.attribute(QStringLiteral("timeline"));
412         QString date_begin = root.attribute(QStringLiteral("date_begin"));
413         QString date_end = root.attribute(QStringLiteral("date_end"));
414         if (period.isEmpty()) {
415             period = '1';
416         }
417         if (interval.isEmpty()) {
418             interval = '2';
419         }
420         if (nb_interval.isEmpty()) {
421             nb_interval = '1';
422         }
423         if (timeline.isEmpty()) {
424             timeline = '1';
425         }
426 
427         QDomDocument doc2(QStringLiteral("SKGML"));
428         QDomElement root2 = doc2.createElement(QStringLiteral("parameters"));
429         doc2.appendChild(root2);
430 
431         root2.setAttribute(QStringLiteral("period"), period);
432         if (period == QStringLiteral("4")) {
433             root2.setAttribute(QStringLiteral("date_begin"), date_begin);
434             root2.setAttribute(QStringLiteral("date_end"), date_end);
435         }
436         root2.setAttribute(QStringLiteral("interval"), interval);
437         root2.setAttribute(QStringLiteral("nb_intervals"), nb_interval);
438         root2.setAttribute(QStringLiteral("timeline"), timeline);
439 
440         periodDef = doc2.toString();
441     }
442     ui.kPeriod->setState(periodDef);
443 
444     ui.kIncomes->setChecked(incomes != QStringLiteral("N"));
445     ui.kExpenses->setChecked(expenses != QStringLiteral("N"));
446     ui.kTransfers->setChecked(transfers != QStringLiteral("N"));
447     ui.kGrouped->setChecked(grouped != QStringLiteral("N"));
448     ui.kTracked->setChecked(tracked != QStringLiteral("N"));
449     int currentPageInt = SKGServices::stringToInt(currentPage);
450     ui.kWidgetSelector->setSelectedMode(currentPageInt);
451     ui.kLine->setVisible(currentPageInt >= -1);
452     ui.kForecastCmb->setCurrentIndex(ui.kForecastCmb->findData(SKGServices::stringToInt(forecast)));
453     ui.kForecastValue->setValue(SKGServices::stringToInt(forecastValue));
454     if (!zoomPositionString.isEmpty()) {
455         setZoomPosition(SKGServices::stringToInt(zoomPositionString));
456     }
457     if (!correctedby.isEmpty()) {
458         ui.kCorrectedBy->setCurrentIndex(ui.kCorrectedBy->findText(correctedby));
459     }
460     if (!correctedbymode.isEmpty()) {
461         ui.kCorrectedByMode->setCurrentIndex(ui.kCorrectedByMode->findText(correctedbymode));
462     }
463 
464     refresh();
465 
466     ui.kTableWithGraph->setState(tableAndGraphState);
467 
468     // Remove previous other filters
469     ui.kOtherFilters->clear();
470 
471     if (!title.isEmpty() && !wc.isEmpty()) {
472         // Add new filter
473         auto item = new QListWidgetItem(SKGServices::fromTheme(title_icon), title);
474         item->setCheckState(Qt::Checked);
475         item->setFlags(Qt::ItemIsUserCheckable | Qt::ItemIsEnabled);
476         item->setData(1000, wc);
477         item->setData(1001, title_icon);
478         ui.kOtherFilters->addItem(item);
479     }
480 
481     m_previousParametersUsed = QLatin1String("");
482 }
483 
getDefaultStateAttribute()484 QString SKGReportPluginWidget::getDefaultStateAttribute()
485 {
486     return QStringLiteral("SKGREPORT_DEFAULT_PARAMETERS");
487 }
488 
zoomableWidget()489 QWidget* SKGReportPluginWidget::zoomableWidget()
490 {
491     return ui.kTableWithGraph->table();
492 }
493 
printableWidgets()494 QList< QWidget* > SKGReportPluginWidget::printableWidgets()
495 {
496     QList<QWidget*> output;
497     if (ui.kTableWithGraph->isTableVisible()) {
498         output.push_back(ui.kTableWithGraph->table());
499     }
500     if (ui.kTableWithGraph->isGraphVisible()) {
501         output.push_back(ui.kTableWithGraph->graph()->graphicsView());
502     }
503     if (ui.kTableWithGraph->isTextReportVisible()) {
504         output.push_back(ui.kTableWithGraph->textReport());
505     }
506     return output;
507 }
508 
getWhereClauseAndTitleForItem(int row,int column,QString & oWc,QString & oTitle)509 void SKGReportPluginWidget::getWhereClauseAndTitleForItem(int row, int column, QString& oWc, QString& oTitle)
510 {
511     // Build where clause and title
512     oTitle.clear();
513 
514     // Addition of other filter
515     int nb = ui.kOtherFilters->count();
516     for (int i = 0; i < nb; ++i) {
517         QListWidgetItem* item = ui.kOtherFilters->item(i);
518         if (item->checkState() == Qt::Checked) {
519             oTitle += item->text() % '.';
520         }
521     }
522 
523     // Condition on line attribute
524     oTitle += i18nc("Noun, a list of items", "Sub operations");
525     auto* btn = qobject_cast<SKGColorButton*>(ui.kTableWithGraph->table()->cellWidget(row, 0));
526 
527     QString attLine;
528     QStringList listAtt = m_attsForLinesAdded;
529     if (listAtt.isEmpty() || ui.kLines->currentIndex() > 0) {
530         listAtt.push_back(m_attsForLines.at(ui.kLines->currentIndex()));
531     }
532     nb = listAtt.count();
533     for (int i = 0; i < nb; ++i) {
534         QString att = listAtt.at(i);
535         if (att == QStringLiteral("#NOTHING#")) {
536             att = QLatin1String("");
537         }
538         if (att.startsWith(QLatin1String("p_")) || att.contains(QStringLiteral(".p_"))) {
539             att = getWhereClauseForProperty(att);
540         }
541         if (att.isEmpty()) {
542             att = '\'' % i18nc("Noun", "All") % '\'';
543         }
544 
545         if (!attLine.isEmpty()) {
546             attLine += "||'" % OBJECTSEPARATOR % "'||";
547         }
548         attLine += att;
549     }
550 
551     oWc = attLine;
552     QString lineVal = (btn != nullptr ? btn->text() : ui.kTableWithGraph->table()->item(row, 0)->data(1).toString());
553     QString title = ui.kTableWithGraph->table()->horizontalHeaderItem(0)->text();
554     bool avoidAnd = false;
555     if (lineVal.isEmpty() && row == ui.kTableWithGraph->table()->rowCount() - 1) {
556         // This is the last sum
557         oWc = QLatin1String("");
558         if (!attLine.isEmpty()) {
559             oWc = attLine % " NOT LIKE '%#NULL#%'";
560             avoidAnd = true;
561         }
562     } else {
563         // This is not the last sum
564         if (lineVal.isEmpty()) {
565             oWc += " IS NULL OR " % attLine;
566         }
567         oWc += " = '" % SKGServices::stringToSqlString(lineVal) % "' OR " %
568                attLine % " like '" % SKGServices::stringToSqlString(lineVal) % OBJECTSEPARATOR % "%'";
569         oWc = '(' % oWc % ')';
570         oTitle += i18nc("Noun",  " with ");
571         oTitle += i18nc("Noun",  "'%1' with '%2'", title, lineVal);
572     }
573 
574     // Condition on column attribute
575     int nbCol = ui.kTableWithGraph->getNbColumns();
576     QString att = m_attsForColumns[ui.kColumns->currentIndex()];
577     if (att == QStringLiteral("#NOTHING#")) {
578         att = QLatin1String("");
579     }
580     if (att.startsWith(QLatin1String("p_")) || att.contains(QStringLiteral(".p_"))) {
581         att = getWhereClauseForProperty(att);
582     }
583     if (!att.isEmpty()) {
584         if (column != 0 && column < nbCol) {
585             if (!oWc.isEmpty()) {
586                 oWc += QStringLiteral(" AND ");
587                 if (!avoidAnd) {
588                     oTitle += i18nc("Noun",  " and ");
589                 } else {
590                     oTitle += i18nc("Noun",  " with ");
591                 }
592             } else {
593                 oTitle += i18nc("Noun",  " with ");
594             }
595 
596             oWc += '(' % att;
597             QString val = ui.kTableWithGraph->table()->horizontalHeaderItem(column)->text();
598             if (val.isEmpty()) {
599                 oWc += " IS NULL OR " % att % "=''";
600                 oTitle += i18nc("Noun",  "'%1' are empty", ui.kColumns->currentText());
601             } else {
602                 oWc += " = '" % SKGServices::stringToSqlString(val) % "' OR " %
603                        att % " like '" % SKGServices::stringToSqlString(val) % OBJECTSEPARATOR % "%'";
604                 oTitle += i18nc("Noun",  "'%1' with '%2'", ui.kColumns->currentText(), val);
605             }
606             oWc += ')';
607         } else {
608             oWc = '(' % oWc % ") AND " % att % " NOT LIKE '%#NULL#%'";
609         }
610     }
611 
612     // Condition on other attribute
613     if (!oWc.isEmpty()) {
614         oWc += QStringLiteral(" AND ");
615         oTitle += i18nc("Noun",  " and ");
616     }
617     oWc += getConsolidatedWhereClause();
618 
619     QString during = ui.kPeriod->text();
620 
621     QStringList types;
622     if (ui.kIncomes->isChecked()) {
623         types.push_back(i18nc("Noun",  "incomes"));
624     }
625     if (ui.kExpenses->isChecked()) {
626         types.push_back(i18nc("Noun",  "expenses"));
627     }
628     if (ui.kTransfers->isChecked()) {
629         types.push_back(i18nc("Noun",  "transfers"));
630     } else if (ui.kGrouped->isChecked()) {
631         types.push_back(i18nc("Noun",  "grouped"));
632     }
633     if (ui.kTracked->isChecked()) {
634         types.push_back(i18nc("Noun",  "tracked"));
635     }
636 
637     oTitle += i18nc("Noun",  "during '%1' for '%2'", during, types.join(QStringLiteral(" ")));
638 }
639 
getWhereClauseAndTitleForSelection(QString & oWc,QString & oTitle)640 void SKGReportPluginWidget::getWhereClauseAndTitleForSelection(QString& oWc, QString& oTitle)
641 {
642     oWc.clear();
643     oTitle.clear();
644 
645     QList<QTableWidgetItem*> selection = ui.kTableWithGraph->table()->selectedItems();
646     int nb = selection.count();
647     if (nb != 0) {
648         for (int i = 0; i < nb; ++i) {
649             QString wc2;
650             QString title2;
651             getWhereClauseAndTitleForItem(selection.at(i)->row(), selection.at(i)->column(), wc2, title2);
652             if (!wc2.isEmpty()) {
653                 if (!oWc.isEmpty()) {
654                     oWc = '(' % oWc % ") OR (" % wc2 % ')';
655                 } else {
656                     oWc = wc2;
657                 }
658             }
659             if (!title2.isEmpty()) {
660                 if (!oTitle.isEmpty()) {
661                     oTitle = i18n("(%1) or (%2)", oTitle, title2);
662                 } else {
663                     oTitle = title2;
664                 }
665             }
666         }
667     }
668 }
669 
onDoubleClick(int row,int column)670 void SKGReportPluginWidget::onDoubleClick(int row, int column)
671 {
672     _SKGTRACEINFUNC(10)
673 
674     QString wc;
675     QString title;
676     getWhereClauseAndTitleForItem(row, column, wc, title);
677     SKGMainPanel::getMainPanel()->openPage("skg://skrooge_operation_plugin/SKGOPERATION_CONSOLIDATED_DEFAULT_PARAMETERS/?currentPage=-1&title_icon=view-statistics&operationTable=v_suboperation_consolidated&operationWhereClause=" % SKGServices::encodeForUrl(wc) % "&title=" % SKGServices::encodeForUrl(title));
678 }
679 
onBtnModeClicked(int mode)680 void SKGReportPluginWidget::onBtnModeClicked(int mode)
681 {
682     ui.kTableWithGraph->setFilterVisibility(mode == 0);
683 }
684 
onOpen()685 void SKGReportPluginWidget::onOpen()
686 {
687     QString wc;
688     QString title;
689     getWhereClauseAndTitleForSelection(wc, title);
690     SKGMainPanel::getMainPanel()->openPage("skg://skrooge_operation_plugin/SKGOPERATION_CONSOLIDATED_DEFAULT_PARAMETERS/?currentPage=-1&title_icon=view-statistics&operationTable=v_suboperation_consolidated&operationWhereClause=" % SKGServices::encodeForUrl(wc) % "&title=" % SKGServices::encodeForUrl(title));
691 }
692 
onOpenReport()693 void SKGReportPluginWidget::onOpenReport()
694 {
695     SKGError err;
696     SKGTRACEINFUNCRC(10, err)
697     QString wc;
698     QString title;
699     getWhereClauseAndTitleForSelection(wc, title);
700     if (!wc.isEmpty()) {
701         // Call report plugin
702         QDomDocument doc(QStringLiteral("SKGML"));
703         doc.setContent(getState());
704         QDomElement root = doc.documentElement();
705         root.setAttribute(QStringLiteral("operationWhereClause"), wc);
706         root.setAttribute(QStringLiteral("title"), title);
707         root.setAttribute(QStringLiteral("title_icon"), QStringLiteral("view-statistics"));
708         QString currentPage = root.attribute(QStringLiteral("currentPage"));
709         if (SKGServices::stringToInt(currentPage) < -1) {
710             root.setAttribute(QStringLiteral("currentPage"), QStringLiteral("-1"));
711         }
712         SKGMainPanel::getMainPanel()->openPage(SKGMainPanel::getMainPanel()->getPluginByName(QStringLiteral("Skrooge report plugin")), -1, doc.toString());
713     }
714 }
715 
onOneLevelMore()716 void SKGReportPluginWidget::onOneLevelMore()
717 {
718     _SKGTRACEINFUNC(10)
719     if (sender() == ui.kLineUp) {
720         ++m_nbLevelLines;
721     } else {
722         ++m_nbLevelColumns;
723     }
724     refresh();
725 }
726 
onOneLevelLess()727 void SKGReportPluginWidget::onOneLevelLess()
728 {
729     _SKGTRACEINFUNC(10)
730     if (sender() == ui.kLineDown) {
731         --m_nbLevelLines;
732     } else {
733         --m_nbLevelColumns;
734     }
735     refresh();
736 }
737 
onAddLine()738 void SKGReportPluginWidget::onAddLine()
739 {
740     _SKGTRACEINFUNC(10)
741     m_attsForLinesAdded.push_back(m_attsForLines.value(ui.kLines->currentIndex()));
742     ui.kLines->setCurrentIndex(0);
743     refresh();
744 }
745 
onRemoveLine()746 void SKGReportPluginWidget::onRemoveLine()
747 {
748     _SKGTRACEINFUNC(10)
749     if (!m_attsForLinesAdded.isEmpty()) {
750         m_attsForLinesAdded.pop_back();
751     }
752     refresh();
753 }
754 
getConsolidatedWhereClause(QString * oWhereClausForPreviousData,QString * oWhereClausForForecastData)755 QString SKGReportPluginWidget::getConsolidatedWhereClause(QString* oWhereClausForPreviousData, QString*  oWhereClausForForecastData)
756 {
757     // Build where clause
758     int forecastmode = ui.kForecastCmb->itemData(ui.kForecastCmb->currentIndex()).toInt();
759     QString wc = ui.kPeriod->getWhereClause(forecastmode != 1, oWhereClausForPreviousData, oWhereClausForForecastData);
760 
761     wc = "((" % wc % ") OR d_date='0000') AND d_date!='0000-00-00'";
762     if (oWhereClausForPreviousData != nullptr) {
763         *oWhereClausForPreviousData = "((" % *oWhereClausForPreviousData % ") OR d_date='0000-00-00')";
764     }
765 
766     QString operationTypes;
767     if (ui.kIncomes->isChecked() && !ui.kExpenses->isChecked()) {
768         operationTypes = QStringLiteral("t_TYPEEXPENSE='+'");
769     } else if (ui.kExpenses->isChecked() && !ui.kIncomes->isChecked()) {
770         operationTypes = QStringLiteral("t_TYPEEXPENSE='-'");
771     }
772     if (!operationTypes.isEmpty()) {
773         QString condition = " AND " % operationTypes;
774         wc += condition;
775         if (oWhereClausForPreviousData != nullptr) {
776             *oWhereClausForPreviousData += condition;
777         }
778     }
779 
780     if (!ui.kGrouped->isChecked()) {
781         QString condition = QStringLiteral(" AND i_group_id=0");
782         wc += condition;
783         if (oWhereClausForPreviousData != nullptr) {
784             *oWhereClausForPreviousData += condition;
785         }
786     } else {
787         if (!ui.kTransfers->isChecked()) {
788             QString condition = QStringLiteral(" AND t_TRANSFER='N'");
789             wc += condition;
790             if (oWhereClausForPreviousData != nullptr) {
791                 *oWhereClausForPreviousData += condition;
792             }
793         }
794     }
795 
796     if (!ui.kTracked->isChecked()) {
797         QString condition = QStringLiteral(" AND r_refund_id=0");
798         wc += condition;
799         if (oWhereClausForPreviousData != nullptr) {
800             *oWhereClausForPreviousData += condition;
801         }
802     }
803 
804     // Addition of other filters
805     int nb = ui.kOtherFilters->count();
806     for (int i = 0; i < nb; ++i) {
807         QListWidgetItem* item = ui.kOtherFilters->item(i);
808         if (item->checkState() == Qt::Checked) {
809             QString condition = " AND (" % item->data(1000).toString() % ")";
810             wc += condition;
811             if (oWhereClausForPreviousData != nullptr) {
812                 *oWhereClausForPreviousData += condition;
813             }
814         }
815     }
816 
817     return wc;
818 }
819 
onSelectionChanged()820 void SKGReportPluginWidget::onSelectionChanged()
821 {
822     if (m_openReportAction != nullptr) {
823         m_openReportAction->setEnabled(!ui.kTableWithGraph->table()->selectedItems().isEmpty());
824     }
825     if (m_openAction != nullptr) {
826         m_openAction->setEnabled(!ui.kTableWithGraph->table()->selectedItems().isEmpty());
827     }
828 }
829 
refresh()830 void SKGReportPluginWidget::refresh()
831 {
832     int p = ui.kPeriod->mode();
833 
834     bool dateCol = (m_attsForColumns.value(ui.kColumns->currentIndex()).startsWith(QLatin1String("d_")));
835     if (!dateCol) {
836         ui.kForecastCmb->setCurrentIndex(0);
837     }
838     ui.kForecastCmb->setEnabled(dateCol);
839     ui.kForecastValue->setEnabled(ui.kForecastCmb->currentIndex() > 0);
840     ui.kLineRemove->setEnabled(!m_attsForLinesAdded.isEmpty());
841 
842     int mode = ui.kMode->itemData(ui.kMode->currentIndex()).toInt();
843     ui.kCorrectedBy->setEnabled(mode != 3);
844     ui.kCorrectedByMode->setEnabled(mode != 3);
845 
846     // Check income & expense
847     if (!ui.kIncomes->isChecked() && !ui.kExpenses->isChecked()) {
848         if (sender() == ui.kIncomes) {
849             ui.kExpenses->setChecked(true);
850         } else {
851             ui.kIncomes->setChecked(true);
852         }
853     }
854 
855     bool timeline = (p == 5);
856     ui.kForecastFrm->setEnabled(!timeline);
857     if (timeline) {
858         ui.kForecastCmb->setCurrentIndex(0);
859     }
860 
861     m_timer.start(300);
862 }
863 
pageChanged()864 void SKGReportPluginWidget::pageChanged()
865 {
866     if (m_refreshNeeded) {
867         m_timer.start(300);
868     }
869 
870     // Refresh other filter
871     auto* rep = qobject_cast<SKGReportPlugin*>(SKGMainPanel::getMainPanel()->getPluginByName(QStringLiteral("Skrooge report plugin")));
872     if (rep != nullptr) {
873         // Remove unused filters
874         int nbf = ui.kOtherFilters->count();
875         for (int i = nbf - 1; i >= 0; --i) {
876             QListWidgetItem* item = ui.kOtherFilters->item(i);
877             if (item->checkState() == Qt::Unchecked) {
878                 QListWidgetItem* item = ui.kOtherFilters->takeItem(i);
879                 delete item;
880             }
881         }
882 
883         // Add pages filters
884         int nb = SKGMainPanel::getMainPanel()->countPages();
885         for (int i = 0; i < nb; ++i) {
886             SKGTabPage* page = SKGMainPanel::getMainPanel()->page(i);
887             SKGObjectBase::SKGListSKGObjectBase selection = page->getSelectedObjects();
888 
889             QString title;
890             QString wc;
891             rep->getTitleAndWhereClause(selection, title, wc);
892             if (!title.isEmpty()) {
893                 // Check if already existing
894                 bool existing = false;
895                 int nbf = ui.kOtherFilters->count();
896                 for (int j = 0; !existing && j < nbf; ++j) {
897                     QListWidgetItem* item2 = ui.kOtherFilters->item(j);
898                     if (item2->data(1000).toString() == wc) {
899                         existing = true;
900                     }
901                 }
902 
903                 // Add it if not existing
904                 if (!existing) {
905                     QString icon = SKGMainPanel::getMainPanel()->getPluginByName(page->objectName())->icon();
906                     auto item = new QListWidgetItem(SKGServices::fromTheme(icon), title);
907                     item->setFlags(Qt::ItemIsUserCheckable | Qt::ItemIsEnabled);
908                     item->setCheckState(Qt::Unchecked);
909                     item->setData(1000, wc);
910                     item->setData(1001, icon);
911                     ui.kOtherFilters->addItem(item);
912                 }
913             }
914         }
915     }
916 }
917 
dataModified(const QString & iTableName,int iIdTransaction)918 void SKGReportPluginWidget::dataModified(const QString& iTableName, int iIdTransaction)
919 
920 {
921     SKGTRACEINFUNC(10)
922     Q_UNUSED(iIdTransaction)
923 
924     // Refresh panel
925     QSqlDatabase* db = getDocument()->getMainDatabase();
926     setEnabled(db != nullptr);
927     // Check if needed
928     if (db != nullptr && (iTableName == QStringLiteral("v_suboperation_consolidated") || iTableName.isEmpty())) {
929         SKGError err;
930         auto* doc = qobject_cast<SKGDocumentBank*>(getDocument());
931         if (doc != nullptr) {
932             // Check if update is needed
933             SKGTabPage* page = SKGTabPage::parentTabPage(this->parentWidget());
934             SKGTabPage* cpage = SKGMainPanel::getMainPanel()->currentPage();
935             if (page != nullptr && page != cpage) {
936                 m_refreshNeeded = true;
937                 return;
938             }
939 
940             QString ParametersUsed = getState() % ';' % SKGServices::intToString(doc->getTransactionToProcess(SKGDocument::UNDO));
941             if (ParametersUsed == m_previousParametersUsed) {
942                 SKGTRACEL(10) << "Same parameters. Refresh ignored" << SKGENDL;
943                 return;
944             }
945 
946             QApplication::setOverrideCursor(QCursor(Qt::WaitCursor));
947             m_refreshNeeded = false;
948             m_previousParametersUsed = ParametersUsed;
949 
950             // Fill line and create title
951             QString lineString;
952             QString title;
953             int nb = m_attsForLinesAdded.count();
954             for (int i = 0; i < nb; ++i) {
955                 lineString += doc->getDisplay(m_attsForLinesAdded.at(i));
956                 if (i < nb - 1) {
957                     lineString += OBJECTSEPARATOR;
958                 }
959             }
960             if (ui.kLines->currentIndex() > 0 && (nb != 0)) {
961                 lineString += OBJECTSEPARATOR;
962             }
963             title = lineString;
964             if (ui.kLines->currentIndex() > 0 || nb == 0) {
965                 title += ui.kLines->text();
966             }
967             ui.kLineLabel->setVisible(nb > 0);
968             ui.kLineLabel->setText(lineString);
969             ui.kLineLabel->setToolTip(lineString);
970 
971             // Get parameters
972             int col = ui.kColumns->currentIndex();
973             int line = ui.kLines->currentIndex();
974 
975             if (col >= 0 && line >= 0) {
976                 int mode = ui.kMode->itemData(ui.kMode->currentIndex()).toInt();
977 
978                 // Mode history ?
979                 bool modeHistory = (mode == 1 || mode == 8);
980 
981                 // Get condition (must be done before forecast on scheduled operations)
982                 int nbVirtualColumn = 0;
983                 QString conditionPrevious;
984                 QString conditionForecast;
985                 QString condition = getConsolidatedWhereClause(&conditionPrevious, &conditionForecast);
986                 int forecastMode = ui.kForecastCmb->itemData(ui.kForecastCmb->currentIndex()).toInt();
987 
988                 // Compute forecast on scheduled operations
989                 bool transactionToRollback = false;
990                 if (forecastMode == 1 || forecastMode == 4) {
991                     // Create operations
992                     QDate lastDate = QDate::currentDate().addDays(ui.kForecastValue->value() * skgreport_settings::maxForecasts() * 365.0 / ui.kForecastValue->maximum());
993                     if (forecastMode == 1) {
994                         // Create scheduled operations
995                         doc->beginTransaction("#INTERNAL#" % i18nc("Progression step", "Create scheduled operations"));
996                         transactionToRollback = true;
997                         int nbInserted = 0;
998                         SKGRecurrentOperationObject::process(doc, nbInserted, true, lastDate);
999                     } else {
1000                         // Create budgeted operations
1001                         SKGObjectBase::SKGListSKGObjectBase budgets;
1002                         IFOKDO(err, doc->getObjects(QStringLiteral("v_budget"),
1003                                                     "t_PERIOD>STRFTIME('%Y-%m', date('now', 'localtime')) AND "
1004                                                     "t_PERIOD<=STRFTIME('%Y-%m', '" % SKGServices::dateToSqlString(lastDate) % "')", budgets));
1005                         int nbBudgets = budgets.count();
1006                         if (nbBudgets != 0) {
1007                             // Get most active account
1008                             SKGStringListList list;
1009                             err = doc->executeSelectSqliteOrder(QStringLiteral("SELECT  rd_account_id, r_category_id FROM v_suboperation_consolidated WHERE d_date>date('now', 'localtime', '-3 month') group by r_category_id, rd_account_id order by count(rd_account_id) DESC"), list);
1010                             int nbmap = list.count();
1011                             if (!err && nbmap >= 2) {
1012                                 // Build map
1013                                 int default_account = 0;
1014                                 QMap<int, int> category_account;
1015                                 for (int i = 1; i < nbmap; ++i) {
1016                                     if (i == 1) {
1017                                         default_account = SKGServices::stringToInt(list.at(i).at(0));
1018                                     }
1019                                     category_account[SKGServices::stringToInt(list.at(i).at(1))] = SKGServices::stringToInt(list.at(i).at(0));
1020                                 }
1021 
1022                                 // Get unit
1023                                 SKGUnitObject unit(doc);
1024                                 IFOKDO(err, unit.setName(doc->getPrimaryUnit().Name))
1025                                 IFOKDO(err, unit.setSymbol(doc->getPrimaryUnit().Symbol))
1026                                 IFOKDO(err, unit.load())
1027 
1028 
1029                                 doc->beginTransaction("#INTERNAL#" % i18nc("Progression step", "Create budgeted operations"));
1030                                 transactionToRollback = true;
1031                                 for (int i = 0; !err && i < nbBudgets; ++i) {
1032                                     SKGBudgetObject budget(budgets.at(i));
1033 
1034                                     SKGCategoryObject cat;
1035                                     budget.getCategory(cat);
1036 
1037                                     // Get better account for the category
1038                                     SKGAccountObject account(doc, category_account.contains(cat.getID()) ? category_account[cat.getID()] : default_account);
1039                                     account.load();
1040 
1041 
1042                                     SKGOperationObject op;
1043                                     IFOKDO(err, account.addOperation(op))
1044                                     IFOKDO(err, op.setDate(QDate(budget.getYear(), qMax(budget.getMonth(), 1), 1)))
1045                                     IFOKDO(err, op.setUnit(unit))
1046                                     IFOKDO(err, op.save())
1047 
1048                                     SKGSubOperationObject sop;
1049                                     IFOKDO(err, op.addSubOperation(sop))
1050                                     IFOKDO(err, sop.setCategory(cat))
1051                                     IFOKDO(err, sop.setQuantity(budget.getBudgetedAmount()))
1052                                     IFOKDO(err, sop.save())
1053                                 }
1054                             }
1055                         }
1056                     }
1057                     ui.kForecastValue->setToolTip(SKGServices::dateToSqlString(lastDate));
1058                 } else {
1059                     ui.kForecastValue->setToolTip(QLatin1String(""));
1060                     conditionForecast = QStringLiteral("1=0");
1061                 }
1062 
1063                 // Execute sql order
1064                 SKGStringListList table;
1065                 QString tableName = QStringLiteral("v_suboperation_consolidated");
1066                 QString attCol = m_attsForColumns[col ];
1067                 if (attCol == QStringLiteral("#NOTHING#")) {
1068                     attCol = QLatin1String("");
1069                 }
1070                 if (attCol.startsWith(QLatin1String("p_")) || attCol.contains(QStringLiteral(".p_"))) {
1071                     attCol = getWhereClauseForProperty(attCol);
1072                 }
1073 
1074                 QStringList listAtt = m_attsForLinesAdded;
1075                 if (listAtt.isEmpty() || line > 0) {
1076                     listAtt.push_back(m_attsForLines.at(line));
1077                 }
1078                 QString attLine;
1079                 int nb2 = listAtt.count();
1080                 for (int i = 0; i < nb2; ++i) {
1081                     QString att = listAtt.at(i);
1082                     if (att == QStringLiteral("#NOTHING#")) {
1083                         att = QLatin1String("");
1084                     }
1085                     if (att.startsWith(QLatin1String("p_")) || att.contains(QStringLiteral(".p_"))) {
1086                         att = getWhereClauseForProperty(att);
1087                     }
1088                     if (att.isEmpty()) {
1089                         att = '\'' % i18nc("Noun", "All") % '\'';
1090                     }
1091 
1092                     if (!attLine.isEmpty()) {
1093                         attLine += "||'" % OBJECTSEPARATOR % "'||";
1094                     }
1095                     attLine += att;
1096                 }
1097 
1098                 if ((modeHistory || (forecastMode != 0)) && !attCol.isEmpty()) {
1099                     tableName = QStringLiteral("(SELECT ");
1100                     if (!modeHistory) {
1101                         tableName += QStringLiteral("d_date, ");
1102                         tableName += attCol % "||(CASE WHEN " % conditionForecast % " THEN '999' ELSE '' END) AS " % m_attsForColumns[col ] % ",* FROM v_suboperation_consolidated) as v_suboperation_consolidated";
1103                     } else {
1104                         if (attCol != QStringLiteral("d_date")) {
1105                             tableName += "(CASE WHEN (" % conditionPrevious % ") AND f_REALQUANTITY<>0 THEN '0000' ELSE d_date END) AS d_date, ";
1106                         }
1107                         tableName += "(CASE WHEN (" % conditionPrevious % ") AND f_REALQUANTITY<>0 THEN '0000' ELSE " % attCol % "||(CASE WHEN " % conditionForecast % " THEN '999' ELSE '' END) END) AS " % m_attsForColumns[col ] % ",* FROM v_suboperation_consolidated) as v_suboperation_consolidated";
1108                     }
1109                 }
1110 
1111                 // Remove #NULL# columns and lines
1112                 if (!attLine.isEmpty()) {
1113                     condition = '(' % condition % ") AND " % attLine % " NOT LIKE '%#NULL#%'";
1114                 }
1115                 if (!attCol.isEmpty()) {
1116                     condition = '(' % condition % ") AND " % attCol % " NOT LIKE '%#NULL#%'";
1117                 }
1118 
1119                 // Limit size of the result
1120                 condition = '(' % condition % ") AND (d_date>(SELECT date('now', 'localtime', '-50 year')) OR d_date='0000') AND d_date<(SELECT date('now', 'localtime', '+50 year'))";
1121 
1122                 QString coef;
1123                 if (ui.kCorrectedBy->currentIndex() > 0 && mode != 3) {
1124                     SKGUnitObject unit = ui.kCorrectedBy->getUnit();
1125                     QString id = SKGServices::intToString(unit.getID());
1126                     if (ui.kCorrectedByMode->currentIndex() == 0) {
1127                         coef = '*';
1128                     } else {
1129                         coef = '/';
1130                     }
1131                     coef = coef % "COALESCE((SELECT u1.f_quantity FROM unitvalue u1 where u1.rd_unit_id=" % id % " and u1.d_date=(SELECT MAX(u2.d_date) FROM unitvalue u2 where u2.rd_unit_id=" % id % " and u2.d_date<=v_suboperation_consolidated.d_date)), (SELECT u1.f_quantity FROM unitvalue u1 where u1.rd_unit_id=" % id % " and u1.d_date=(SELECT MIN(u2.d_date) FROM unitvalue u2 where u2.rd_unit_id=" % id % ")),0)";
1132                 }
1133 
1134                 IFOKDO(err, doc->getConsolidatedView(tableName, attCol, attLine, "f_REALCURRENTAMOUNT" % coef,
1135                                                      (mode == 3 ? QStringLiteral("COUNT") : QStringLiteral("TOTAL")), condition, table));
1136                 int nbLines = table.count();
1137                 IFSKGTRACEL(10) {
1138                     QStringList dump = SKGServices::tableToDump(table, SKGServices::DUMP_TEXT);
1139                     int nbl = dump.count();
1140                     for (int i = 0; i < nbl; ++i) {
1141                         SKGTRACE << dump.at(i) << SKGENDL;
1142                     }
1143                 }
1144 
1145                 // Rollback create operations
1146                 if (transactionToRollback) {
1147                     doc->endTransaction(false);
1148                 }
1149 
1150                 if (forecastMode == 1 || forecastMode == 4) {
1151                     // Compute nb date in futur
1152                     if (nbLines != 0) {
1153                         QStringList line1 = table.at(0);
1154                         int nbCol = line1.count();
1155                         for (int i = 0; i < nbCol; ++i) {
1156                             QString newTitle = line1.at(i);
1157                             if (newTitle.endsWith(QLatin1String("999"))) {
1158                                 newTitle = newTitle.left(newTitle.count() - 3);
1159                                 line1.replace(i, newTitle);
1160                                 ++nbVirtualColumn;
1161                             }
1162                         }
1163                         table.replace(0, line1);
1164                     }
1165                 }
1166 
1167                 IFOK(err) {
1168                     // Update title
1169                     if (nbLines != 0) {
1170                         // Change title
1171                         QStringList line1 = table.at(0);
1172                         if (!line1.isEmpty()) {
1173                             line1.replace(0, title);
1174                             table.replace(0, line1);
1175                         }
1176                     }
1177 
1178                     // Compute forecast on average
1179                     if ((nbLines != 0) && (forecastMode == 2 || forecastMode == 3)) {
1180                         QStringList newLine = table.at(0);
1181                         int nbVals = newLine.count() - 1;
1182                         int percent = ui.kForecastValue->value();
1183                         if (nbVals >= 3 && percent > 0) {
1184                             // Compute nb value to add
1185                             nbVirtualColumn = percent * nbVals / FORECASTMAX;
1186 
1187                             // Build header
1188                             newLine.reserve(newLine.count() + nbVirtualColumn);
1189                             for (int i = 0; i < nbVirtualColumn; ++i) {
1190                                 newLine.push_back(i18nc("Noun", "N %1", (i + 1)));
1191                             }
1192                             table.replace(0, newLine);
1193 
1194                             // Build values
1195                             for (int j = 1; j < nbLines; ++j) {
1196                                 QStringList newLineTmp = table.at(j);
1197                                 newLineTmp.reserve(newLineTmp.count() + nbVirtualColumn);
1198                                 for (int i = 0; i < nbVirtualColumn; ++i) {
1199                                     // Moving average
1200                                     double nv = 0;
1201                                     double weight = 0;
1202                                     for (int k = 0; k < nbVirtualColumn; ++k) {
1203                                         if (forecastMode == 2) {
1204                                             // Simple mode
1205                                             nv += SKGServices::stringToDouble(newLineTmp.at(nbVals + i - k));
1206                                             ++weight;
1207                                         } else {
1208                                             // Weighted mode
1209                                             nv += (nbVirtualColumn - k) * SKGServices::stringToDouble(newLineTmp.at(nbVals + i - k));
1210                                             weight += nbVirtualColumn - k;
1211                                         }
1212                                     }
1213                                     newLineTmp.push_back(SKGServices::doubleToString(nv / weight));
1214                                 }
1215                                 table.replace(j, newLineTmp);
1216                             }
1217                         }
1218                     }
1219 
1220                     // Create grouped by lines table
1221                     int nbLevelLineMax = 0;
1222                     {
1223                         SKGStringListList groupedByLineTable = table;
1224                         {
1225                             int nbCols = -1;
1226                             QString previousLineName = QStringLiteral("###");
1227                             if (!groupedByLineTable.isEmpty()) {
1228                                 nbCols = groupedByLineTable.at(0).count();
1229                             }
1230                             for (int i = 1; (nbCols != 0) && i < groupedByLineTable.count(); ++i) {  // Dynamically modified
1231                                 QStringList line2 = groupedByLineTable.at(i);
1232                                 QString val = line2.at(0);
1233 
1234                                 // Rebuild val for the number of level
1235                                 QStringList vals = val.split(OBJECTSEPARATOR);
1236                                 int nbvals = vals.count();
1237                                 if (nbvals > m_nbLevelLines + 1) {
1238                                     // Rebuild val
1239                                     val = QLatin1String("");
1240                                     for (int k = 0; k <= m_nbLevelLines; ++k) {
1241                                         val += vals[k];
1242                                         if (k != m_nbLevelLines) {
1243                                             val += OBJECTSEPARATOR;
1244                                         }
1245                                     }
1246                                 }
1247                                 nbLevelLineMax = qMax(nbLevelLineMax, nbvals - 1);
1248 
1249                                 if (val == previousLineName) {
1250                                     // Current line is merged with previous one
1251                                     QStringList newLine;
1252                                     newLine.reserve(nbCols);
1253                                     newLine.push_back(val);
1254                                     for (int k = 1; k < nbCols; ++k) {
1255                                         const QString& valstring1 = line2.at(k);
1256                                         QString valstring2 = groupedByLineTable.at(i - 1).at(k);
1257 
1258                                         if (!valstring1.isEmpty() || !valstring2.isEmpty()) {
1259                                             double sum2 = 0;
1260                                             if (!valstring1.isEmpty()) {
1261                                                 sum2 += SKGServices::stringToDouble(valstring1);
1262                                             }
1263                                             if (!valstring2.isEmpty()) {
1264                                                 sum2 += SKGServices::stringToDouble(valstring2);
1265                                             }
1266                                             newLine.push_back(SKGServices::doubleToString(sum2));
1267                                         } else {
1268                                             newLine.push_back(QLatin1String(""));
1269                                         }
1270                                     }
1271 
1272                                     groupedByLineTable.replace(i - 1, newLine);
1273 
1274                                     // Remove current line
1275                                     groupedByLineTable.removeAt(i);
1276                                     --i;
1277                                 } else {
1278                                     // Current line is just modified
1279                                     QStringList newLine;
1280                                     newLine.reserve(nbCols);
1281                                     newLine.push_back(val);
1282                                     for (int k = 1; k < nbCols; ++k) {
1283                                         newLine.push_back(line2.at(k));
1284                                     }
1285                                     groupedByLineTable.replace(i, newLine);
1286 
1287                                     previousLineName = val;
1288                                 }
1289                             }
1290                         }
1291                         table = groupedByLineTable;
1292                     }
1293 
1294                     // Create grouped by columns table
1295                     int nbLevelColMax = 0;
1296                     {
1297                         SKGStringListList groupedByColTable = table;
1298                         int nbLines2 = groupedByColTable.count();
1299                         if (nbLines2 != 0) {
1300                             QString previousColumnName = QStringLiteral("###");
1301                             for (int i = 1; (nbLines2 != 0) && i < groupedByColTable.at(0).count(); ++i) {  // Dynamically modified
1302                                 QString val = groupedByColTable.at(0).at(i);
1303 
1304                                 // Rebuild val for the number of level
1305                                 QStringList vals = val.split(OBJECTSEPARATOR);
1306                                 int nbvals = vals.count();
1307                                 if (nbvals > m_nbLevelColumns + 1) {
1308                                     // Rebuild val
1309                                     val = QLatin1String("");
1310                                     for (int k = 0; k <= m_nbLevelColumns; ++k) {
1311                                         val += vals[k];
1312                                         if (k != m_nbLevelColumns) {
1313                                             val += OBJECTSEPARATOR;
1314                                         }
1315                                     }
1316                                 }
1317                                 nbLevelColMax = qMax(nbLevelColMax, nbvals - 1);
1318 
1319                                 if (val == previousColumnName) {
1320                                     // Current column is merged with previous one
1321                                     QStringList newLine = groupedByColTable.at(0);
1322                                     newLine.removeAt(i);
1323                                     groupedByColTable.replace(0, newLine);
1324 
1325                                     for (int k = 1; k < nbLines2; ++k) {
1326                                         newLine = groupedByColTable.at(k);
1327                                         QString valstring1 = newLine.at(i);
1328                                         QString valstring2 = newLine.at(i - 1);
1329 
1330                                         if (!valstring1.isEmpty() || !valstring2.isEmpty()) {
1331                                             double sum2 = 0;
1332                                             if (!valstring1.isEmpty()) {
1333                                                 sum2 += SKGServices::stringToDouble(valstring1);
1334                                             }
1335                                             if (!valstring2.isEmpty()) {
1336                                                 sum2 += SKGServices::stringToDouble(valstring2);
1337                                             }
1338                                             newLine.replace(i - 1, SKGServices::doubleToString(sum2));
1339                                         } else {
1340                                             newLine.removeAt(i - 1);
1341                                         }
1342                                         newLine.removeAt(i);
1343                                         groupedByColTable.replace(k, newLine);
1344                                     }
1345 
1346                                     // Remove current line
1347                                     --i;
1348                                 } else {
1349                                     // Current column is just modified
1350                                     QStringList newLine = groupedByColTable.at(0);
1351                                     newLine.replace(i, val);
1352                                     groupedByColTable.replace(0, newLine);
1353 
1354                                     previousColumnName = val;
1355                                 }
1356                             }
1357                         }
1358                         table = groupedByColTable;
1359                     }
1360 
1361                     // Initialize parameters
1362                     SKGServices::SKGUnitInfo primaryUnit = doc->getPrimaryUnit();
1363                     SKGServices::SKGUnitInfo secondaryUnit = doc->getSecondaryUnit();
1364 
1365                     SKGTableWithGraph::DisplayAdditionalFlag dmode = SKGTableWithGraph::NONE;
1366                     if (m_attsForColumns.at(col).startsWith(QLatin1String("d_"))) {
1367                         dmode |= SKGTableWithGraph::AVERAGE | SKGTableWithGraph::LIMITS;
1368                     }
1369 
1370                     // Create history (must be done after groups)
1371                     if (modeHistory) {
1372                         table = SKGServices::getHistorizedTable(table);
1373                     } else {
1374                         // Add SUM if not in HISTORY mode
1375                         dmode |= SKGTableWithGraph::SUM;
1376                     }
1377 
1378                     // Mode percent
1379                     bool modePercent = (mode == 2 || mode == 4 || mode == 5 || mode == 6);
1380                     if (modePercent) {
1381                         primaryUnit.Symbol = '%';
1382                         SKGServices::SKGUnitInfo empty;
1383                         empty.Value = 0.0;
1384                         empty.NbDecimal = 2;
1385                         secondaryUnit = empty;
1386 
1387                         table = SKGServices::getPercentTable(table, (mode == 2 || mode == 5), (mode == 5 || mode == 6));
1388                     }
1389 
1390                     // Mode base 100
1391                     if (mode == 7 || mode == 8) {
1392                         table = SKGServices::getBase100Table(table);
1393                     }
1394 
1395                     // Mode Count
1396                     if (mode == 3) {
1397                         primaryUnit.Symbol = ' ';
1398                         SKGServices::SKGUnitInfo empty;
1399                         empty.Value = 0.0;
1400                         empty.NbDecimal = 2;
1401                         secondaryUnit = empty;
1402                     }
1403 
1404                     // Set data
1405                     ui.kTableWithGraph->setData(table, primaryUnit, secondaryUnit, dmode, nbVirtualColumn);
1406 
1407                     // Enable/Disable buttons
1408                     m_nbLevelLines = qMin(nbLevelLineMax, m_nbLevelLines);
1409                     ui.kLineDown->setEnabled(m_nbLevelLines > 0);
1410                     ui.kLineUp->setEnabled(m_nbLevelLines < nbLevelLineMax);
1411 
1412                     m_nbLevelColumns = qMin(nbLevelColMax, m_nbLevelColumns);
1413                     ui.kColDown->setEnabled(m_nbLevelColumns > 0);
1414                     ui.kColUp->setEnabled(m_nbLevelColumns < nbLevelColMax);
1415                 }
1416             }
1417             QApplication::restoreOverrideCursor();
1418         }
1419 
1420         // Display error
1421         SKGMainPanel::displayErrorMessage(err);
1422     }
1423 }
1424 
setSettings()1425 void SKGReportPluginWidget::setSettings()
1426 {
1427     ui.kTableWithGraph->setAxisColor(skgreport_settings::axisColor());
1428     ui.kTableWithGraph->setGridColor(skgreport_settings::gridColor());
1429     ui.kTableWithGraph->setMinColor(skgreport_settings::minColor());
1430     ui.kTableWithGraph->setMaxColor(skgreport_settings::maxColor());
1431     ui.kTableWithGraph->setParetoColor(skgreport_settings::paretoColor());
1432     ui.kTableWithGraph->setAverageColor(skgreport_settings::averageColor());
1433     ui.kTableWithGraph->setTendencyColor(skgreport_settings::tendencyColor());
1434     ui.kTableWithGraph->setBackgroundColor(skgreport_settings::backgroundColor());
1435     ui.kTableWithGraph->setTextColor(skgreport_settings::textColor());
1436     ui.kTableWithGraph->setOutlineColor(skgreport_settings::outlineColor());
1437 
1438     ui.kTableWithGraph->setAntialiasing(skgreport_settings::antialiasing());
1439 
1440     ui.kTableWithGraph->redrawGraphDelayed();
1441 }
1442 
getWhereClauseForProperty(const QString & iProperty) const1443 QString SKGReportPluginWidget::getWhereClauseForProperty(const QString& iProperty) const
1444 {
1445     QString propertyName = iProperty.right(iProperty.length() - 2);
1446     QString check = getDocument()->getDisplay(QStringLiteral("t_category"));
1447     if (iProperty.startsWith(check)) {
1448         propertyName = iProperty.right(iProperty.length() - check.length() - 1 - 2);
1449         return "(SELECT t_value FROM parameters WHERE t_uuid_parent=v_suboperation_consolidated.i_IDCATEGORY||'-category' AND t_name='" % propertyName % "')";
1450     }
1451     check = getDocument()->getDisplay(QStringLiteral("t_account"));
1452     if (iProperty.startsWith(check)) {
1453         propertyName = iProperty.right(iProperty.length() - check.length() - 1 - 2);
1454         return "(SELECT t_value FROM parameters WHERE t_uuid_parent=v_suboperation_consolidated.rd_account_id||'-account' AND t_name='" % propertyName % "')";
1455     }
1456     check = getDocument()->getDisplay(QStringLiteral("t_payee"));
1457     if (iProperty.startsWith(check)) {
1458         propertyName = iProperty.right(iProperty.length() - check.length() - 1 - 2);
1459         return "(SELECT t_value FROM parameters WHERE t_uuid_parent=v_suboperation_consolidated.r_payee_id||'-payee' AND t_name='" % propertyName % "')";
1460     }
1461     check = getDocument()->getDisplay(QStringLiteral("t_unit"));
1462     if (iProperty.startsWith(check)) {
1463         propertyName = iProperty.right(iProperty.length() - check.length() - 1 - 2);
1464         return "(SELECT t_value FROM parameters WHERE t_uuid_parent=v_suboperation_consolidated.rc_unit_id||'-unit' AND t_name='" % propertyName % "')";
1465     }
1466     return "IFNULL((SELECT t_value FROM parameters WHERE t_uuid_parent=v_suboperation_consolidated.id||'-suboperation' AND t_name='" % propertyName % "'), IFNULL((SELECT t_value FROM parameters WHERE t_uuid_parent=v_suboperation_consolidated.i_OPID||'-operation' AND t_name='" % propertyName % "'), '#NULL#'))";
1467 }
1468