File indexing completed on 2024-05-19 08:59:32
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 is a query creator for skrooge 0008 * 0009 * @author Stephane MANKOWSKI / Guillaume DE BURE 0010 */ 0011 #include "skgquerycreator.h" 0012 0013 #include <qdom.h> 0014 #include <qheaderview.h> 0015 #include <qmenu.h> 0016 0017 #include "skgdocument.h" 0018 #include "skgpredicatcreator.h" 0019 #include "skgquerydelegate.h" 0020 #include "skgruleobject.h" 0021 #include "skgservices.h" 0022 0023 #define ADDOPERATOR(title, op) \ 0024 { \ 0025 QAction* act = helpMenu->addAction(title); \ 0026 act->setData(op); \ 0027 connect(act, &QAction::triggered, this, &SKGQueryCreator::onAddText); \ 0028 } 0029 0030 SKGQueryCreator::SKGQueryCreator(QWidget* iParent) 0031 : QWidget(iParent), m_document(nullptr), m_updateMode(false) 0032 { 0033 ui.setupUi(this); 0034 0035 ui.kList->horizontalHeader()->setSectionResizeMode(QHeaderView::Interactive); 0036 ui.kList->verticalHeader()->setSectionResizeMode(QHeaderView::Fixed); 0037 ui.kList->setWordWrap(false); 0038 ui.kList->horizontalHeader()->setSectionsMovable(true); 0039 0040 ui.kToolHelp->setIcon(SKGServices::fromTheme(QStringLiteral("dialog-information"))); 0041 ui.kCheckMode->setIcon(SKGServices::fromTheme(QStringLiteral("arrow-down"))); 0042 ui.kCheckMode->setToolTip(i18nc("A tool tip", "Switch in advanced mode")); 0043 0044 connect(ui.kFilterEdit, &QLineEdit::textChanged, this, &SKGQueryCreator::onTextFilterChanged); 0045 connect(ui.kCheckMode, &QToolButton::clicked, this, &SKGQueryCreator::switchAdvancedSearchMode); 0046 connect(ui.kList, &SKGTableWidget::removeLine, this, &SKGQueryCreator::removeLine); 0047 connect(ui.kFilterEdit, &QLineEdit::returnPressed, this, &SKGQueryCreator::search); 0048 connect(ui.kListAtt, &QListWidget::doubleClicked, this, &SKGQueryCreator::onAddColumn); 0049 0050 addNewLine(); 0051 onTextFilterChanged(QLatin1String("")); 0052 } 0053 0054 SKGQueryCreator::~SKGQueryCreator() 0055 { 0056 m_document = nullptr; 0057 } 0058 0059 void SKGQueryCreator::onTextFilterChanged(const QString& iFilter) 0060 { 0061 auto tooltip = i18nc("Tooltip", "<html><head/><body><p>Searching is case-insensitive. So table, Table, and TABLE are all the same.<br/>" 0062 "If you just put a word or series of words in the search box, the application will filter the table to keep all lines having these words (logical operator AND). <br/>" 0063 "If you want to add (logical operator OR) some lines, you must prefix your word by '+'.<br/>" 0064 "If you want to remove (logical operator NOT) some lines, you must prefix your word by '-'.<br/>" 0065 "If you want to search only on some columns, you must prefix your word by the beginning of column name like: col1:word.<br/>" 0066 "If you want to search only on one column, you must prefix your word by the column name and a dot like: col1.:word.<br/>" 0067 "If you want to use the character ':' in value, you must specify the column name like this: col1:value:rest.<br/>" 0068 "If you want to search for a phrase or something that contains spaces, you must put it in quotes, like: 'yes, this is a phrase'.</p>" 0069 "<p>You can also use operators '<', '>', '<=', '>=', '=' and '#' (for regular expression).</p>" 0070 "<p><span style=\"font-weight:600; text-decoration: underline;\">Examples:</span><br/>" 0071 "+val1 +val2 => Keep lines containing val1 OR val2<br/>" 0072 "+val1 -val2 => Keep lines containing val1 but NOT val2<br/>" 0073 "'abc def' => Keep lines containing the sentence 'abc def' <br/>" 0074 "'-att:abc def' => Remove lines having a column name starting by abc and containing 'abc def' <br/>" 0075 "abc:def => Keep lines having a column name starting by abc and containing def<br/>" 0076 ":abc:def => Keep lines containing 'abc:def'<br/>" 0077 "Date>2015-03-01 => Keep lines where at least one attribute starting by Date is greater than 2015-03-01<br/>" 0078 "Date.>2015-03-01 => Keep lines where at the Date attribute is greater than 2015-03-01<br/>" 0079 "Amount<10 =>Keep lines where at least one attribute starting by Amount is less than 10<br/>" 0080 "Amount=10 =>Keep lines where at least one attribute starting by Amount is equal to 10<br/>" 0081 "Amount<=10 =>Keep lines where at least one attribute starting by Amount is less or equal to 10<br/>" 0082 "abc#^d.*f$ => Keep lines having a column name starting by abc and matching the regular expression ^d.*f$</p>" 0083 "<span style=\"font-weight:600; text-decoration: underline;\">Your filter is understood like this:</span><br/>" 0084 "%1</body></html>", SKGServices::stringToHtml(SKGServices::searchCriteriasToWhereClause(SKGServices::stringToSearchCriterias(iFilter), m_attributes, m_document, true))); 0085 ui.kFilterEdit->setToolTip(tooltip); 0086 } 0087 0088 bool SKGQueryCreator::advancedSearchMode() const 0089 { 0090 return (!ui.kFilterEdit->isVisible()); 0091 } 0092 0093 void SKGQueryCreator::setAdvancedSearchMode(bool iAdvancedMode) const 0094 { 0095 if (iAdvancedMode) { 0096 ui.kToolHelp->hide(); 0097 ui.kFrmAdvanced->show(); 0098 ui.kFilterEdit->hide(); 0099 ui.kCheckMode->setIcon(SKGServices::fromTheme(QStringLiteral("arrow-up"))); 0100 ui.kCheckMode->setToolTip(i18nc("A tool tip", "Switch in simple mode")); 0101 } else { 0102 ui.kToolHelp->show(); 0103 ui.kFrmAdvanced->hide(); 0104 ui.kFilterEdit->show(); 0105 ui.kCheckMode->setIcon(SKGServices::fromTheme(QStringLiteral("arrow-down"))); 0106 ui.kCheckMode->setToolTip(i18nc("A tool tip", "Switch in advanced mode")); 0107 } 0108 } 0109 0110 void SKGQueryCreator::switchAdvancedSearchMode() const 0111 { 0112 setAdvancedSearchMode(!advancedSearchMode()); 0113 } 0114 0115 void SKGQueryCreator::setParameters(SKGDocument* iDocument, const QString& iTable, const QStringList& iListAttribute, bool iModeUpdate) 0116 { 0117 m_document = iDocument; 0118 m_table = iTable; 0119 m_updateMode = iModeUpdate; 0120 m_attributes = iListAttribute; 0121 0122 setAdvancedSearchMode(m_updateMode); 0123 ui.kCheckMode->setVisible(!m_updateMode); 0124 0125 // QString txt=(updateMode ? i18nc("Description", "Double click on a field name to add it to your modification definition.") : i18nc("Description", "Double click on a field name to add it to your search definition."))+'\n'+i18nc("Description", "Double click on a cell to modify it."); 0126 // ui.kLabel->setComment ( "<html><body><b>"+SKGServices::stringToHtml ( txt ) +"</b></body></html>" ); 0127 // ui.kLabel->setIcon ( SKGServices::fromTheme( updateMode ? "view-refresh" :"edit-find" ), KTitleWidget::ImageLeft ); 0128 0129 // Build list of attributes 0130 if (m_document != nullptr) { 0131 auto delegate = new SKGQueryDelegate(ui.kList, m_document, m_updateMode, iListAttribute); 0132 connect(delegate, &SKGQueryDelegate::commitData, this, &SKGQueryCreator::onCloseEditor, Qt::QueuedConnection); 0133 0134 ui.kList->setItemDelegate(delegate); 0135 0136 // Keep only existing attribute 0137 SKGServices::SKGAttributesList listAtts; 0138 int nb = iListAttribute.count(); 0139 0140 SKGServices::SKGAttributesList attributes; 0141 m_document->getAttributesDescription(m_table, attributes); 0142 listAtts.reserve(nb + attributes.count()); 0143 for (const auto& att : qAsConst(attributes)) { 0144 if (iListAttribute.isEmpty() || iListAttribute.contains(att.name)) { 0145 listAtts.push_back(att); 0146 } 0147 } 0148 0149 // Adding properties 0150 for (int i = 0; i < nb; ++i) { 0151 const QString& att = iListAttribute.at(i); 0152 if (att.startsWith(QLatin1String("p_"))) { 0153 SKGServices::SKGAttributeInfo info; 0154 info.name = att; 0155 info.display = att.right(att.length() - 2); 0156 info.type = SKGServices::TEXT; 0157 info.icon = iDocument->getIcon(att); 0158 listAtts.push_back(info); 0159 } 0160 } 0161 0162 ui.kList->setRowCount(0); 0163 0164 // Build list of attributes 0165 QMenu* helpMenu = nullptr; 0166 if (!iModeUpdate) { 0167 helpMenu = new QMenu(this); 0168 { 0169 QAction* act = helpMenu->addAction(i18nc("Operator contains", "or")); 0170 act->setData(QStringLiteral(" +")); 0171 connect(act, &QAction::triggered, this, &SKGQueryCreator::onAddText); 0172 } 0173 { 0174 QAction* act = helpMenu->addAction(i18nc("Operator contains", "but not")); 0175 act->setData(QStringLiteral(" -")); 0176 connect(act, &QAction::triggered, this, &SKGQueryCreator::onAddText); 0177 } 0178 helpMenu->addSeparator(); 0179 } 0180 int nbCol = listAtts.count(); 0181 for (int i = 0; i < nbCol; ++i) { 0182 auto listItem = new QListWidgetItem(listAtts.at(i).icon, listAtts.at(i).display); 0183 ui.kListAtt->addItem(listItem); 0184 listItem->setData(Qt::UserRole, listAtts.at(i).name); 0185 0186 if (helpMenu != nullptr) { 0187 QAction* act = helpMenu->addAction(listAtts.at(i).icon, listAtts.at(i).display); 0188 act->setData(listAtts.at(i).display); 0189 connect(act, &QAction::triggered, this, &SKGQueryCreator::onAddText); 0190 } 0191 } 0192 ui.kListAtt->sortItems(); 0193 ui.kListAtt->setModelColumn(nbCol); 0194 if (helpMenu != nullptr) { 0195 helpMenu->addSeparator(); 0196 { 0197 QAction* act = helpMenu->addAction(i18nc("Operator contains", "Contains")); 0198 act->setData(QStringLiteral(":")); 0199 connect(act, &QAction::triggered, this, &SKGQueryCreator::onAddText); 0200 } 0201 ADDOPERATOR(QStringLiteral("="), QStringLiteral("=")) 0202 ADDOPERATOR(i18nc("Noun", "Regular expression"), QStringLiteral("#")) 0203 ADDOPERATOR(QStringLiteral(">"), QStringLiteral(">")) 0204 ADDOPERATOR(QStringLiteral("<"), QStringLiteral("<")) 0205 ADDOPERATOR(QStringLiteral(">="), QStringLiteral(">=")) 0206 ADDOPERATOR(QStringLiteral("<="), QStringLiteral("<=")) 0207 ui.kToolHelp->setMenu(helpMenu); 0208 } 0209 0210 connect(ui.kList->verticalHeader(), &QHeaderView::sectionClicked, this, &SKGQueryCreator::removeLine); 0211 connect(ui.kList->horizontalHeader(), &QHeaderView::sectionClicked, this, &SKGQueryCreator::removeColumn); 0212 0213 addNewLine(); 0214 } 0215 } 0216 0217 int SKGQueryCreator::getIndexQueryColumn(const QString& iAttribute, int row) 0218 { 0219 // Search column for this attribute 0220 int output = -1; 0221 int nbCol = ui.kList->columnCount(); 0222 for (int i = 0; i < nbCol && output == -1; ++i) { 0223 QTableWidgetItem* it_h = ui.kList->horizontalHeaderItem(i); 0224 if ((it_h != nullptr) && iAttribute == it_h->data(Qt::UserRole).toString()) { 0225 if (row >= 0) { 0226 // Check if the cell is empty 0227 QTableWidgetItem* it = ui.kList->item(row, i); 0228 if (it != nullptr) { 0229 if (it->text().isEmpty()) { 0230 output = i; 0231 } 0232 } 0233 } else { 0234 output = i; 0235 } 0236 } 0237 } 0238 0239 // If not existing, we have to create it 0240 if (output == -1) { 0241 int nb = ui.kListAtt->count(); 0242 for (int i = 0; i < nb; ++i) { 0243 QListWidgetItem* it = ui.kListAtt->item(i); 0244 if ((it != nullptr) && iAttribute == it->data(Qt::UserRole).toString()) { 0245 addColumnFromAttribut(it); 0246 output = nbCol; 0247 break; 0248 } 0249 } 0250 } 0251 0252 return output; 0253 } 0254 0255 void SKGQueryCreator::clearContents() 0256 { 0257 ui.kList->clearContents(); 0258 ui.kList->setRowCount(0); 0259 0260 addNewLine(); 0261 } 0262 0263 void SKGQueryCreator::setXMLCondition(const QString& iXML) 0264 { 0265 QDomDocument doc(QStringLiteral("SKGML")); 0266 doc.setContent(iXML); 0267 QDomElement root = doc.documentElement(); 0268 0269 ui.kList->clearContents(); 0270 ui.kList->setRowCount(0); 0271 ui.kList->setColumnCount(0); 0272 0273 if (root.tagName() == QStringLiteral("element")) { 0274 // Mode advanced 0275 setAdvancedSearchMode(true); 0276 int row = -1; 0277 QDomNode l = root.firstChild(); 0278 while (!l.isNull()) { 0279 QDomElement elementl = l.toElement(); 0280 if (!elementl.isNull()) { 0281 // Add new line 0282 addNewLine(); 0283 ++row; 0284 0285 QDomNode n = elementl.firstChild(); 0286 while (!n.isNull()) { 0287 QDomElement element = n.toElement(); 0288 if (!element.isNull()) { 0289 QString attribute = element.attribute(QStringLiteral("attribute")); 0290 int idx = getIndexQueryColumn(attribute, row); 0291 if (idx >= 0) { 0292 QDomDocument doc2(QStringLiteral("SKGML")); 0293 QDomElement root2 = doc2.createElement(QStringLiteral("element")); 0294 doc2.appendChild(root2); 0295 root2.setAttribute(QStringLiteral("operator"), element.attribute(QStringLiteral("operator"))); 0296 root2.setAttribute(QStringLiteral("value"), element.attribute(QStringLiteral("value"))); 0297 root2.setAttribute(QStringLiteral("value2"), element.attribute(QStringLiteral("value2"))); 0298 root2.setAttribute(QStringLiteral("att2"), element.attribute(QStringLiteral("att2"))); 0299 root2.setAttribute(QStringLiteral("att2s"), element.attribute(QStringLiteral("att2s"))); 0300 QTableWidgetItem* it = ui.kList->item(row, idx); 0301 if (it != nullptr) { 0302 QString xml = doc2.toString(); 0303 0304 it->setText(SKGPredicatCreator::getTextFromXml(xml)); 0305 it->setData(Qt::UserRole, xml); 0306 } 0307 } 0308 } 0309 n = n.nextSibling(); 0310 } 0311 } 0312 l = l.nextSibling(); 0313 } 0314 0315 addNewLine(); 0316 } else { 0317 // Mode simple 0318 setAdvancedSearchMode(false); 0319 ui.kFilterEdit->setText(root.attribute(QStringLiteral("query"))); 0320 } 0321 } 0322 0323 QString SKGQueryCreator::getXMLCondition() 0324 { 0325 QString output; 0326 if (advancedSearchMode()) { 0327 // Mode advanced 0328 QHeaderView* hHeader = ui.kList->horizontalHeader(); 0329 if (hHeader != nullptr) { 0330 QDomDocument doc(QStringLiteral("SKGML")); 0331 QDomElement element = doc.createElement(QStringLiteral("element")); 0332 doc.appendChild(element); 0333 0334 element.appendChild(doc.createComment(QStringLiteral("OR"))); 0335 0336 bool empty = true; 0337 int nbRow = ui.kList->rowCount(); 0338 int nbCol = hHeader->count(); 0339 for (int j = 0; j < nbRow; ++j) { 0340 QDomElement elementLine = doc.createElement(QStringLiteral("element")); 0341 element.appendChild(elementLine); 0342 0343 elementLine.appendChild(doc.createComment(QStringLiteral("AND"))); 0344 0345 bool atLeastOne = false; 0346 for (int i = 0; i < nbCol; ++i) { 0347 int iRealPos = hHeader->logicalIndex(i); 0348 QTableWidgetItem* it = ui.kList->item(j, iRealPos); 0349 if (it != nullptr) { 0350 QString co = it->data(Qt::UserRole).toString(); 0351 if (!co.isEmpty()) { 0352 QDomElement elementElement = doc.createElement(QStringLiteral("element")); 0353 elementLine.appendChild(elementElement); 0354 0355 QTableWidgetItem* it_h = ui.kList->horizontalHeaderItem(iRealPos); 0356 QString attname = it_h->data(Qt::UserRole).toString(); 0357 0358 QDomDocument doc2(QStringLiteral("SKGML")); 0359 doc2.setContent(co); 0360 QDomElement root2 = doc2.documentElement(); 0361 0362 elementElement.setAttribute(QStringLiteral("attribute"), attname); 0363 elementElement.setAttribute(QStringLiteral("operator"), root2.attribute(QStringLiteral("operator"))); 0364 if (root2.hasAttribute(QStringLiteral("value"))) { 0365 elementElement.setAttribute(QStringLiteral("value"), root2.attribute(QStringLiteral("value"))); 0366 } 0367 if (root2.hasAttribute(QStringLiteral("value2"))) { 0368 elementElement.setAttribute(QStringLiteral("value2"), root2.attribute(QStringLiteral("value2"))); 0369 } 0370 if (root2.hasAttribute(QStringLiteral("att2"))) { 0371 elementElement.setAttribute(QStringLiteral("att2"), root2.attribute(QStringLiteral("att2"))); 0372 } 0373 if (root2.hasAttribute(QStringLiteral("att2s"))) { 0374 elementElement.setAttribute(QStringLiteral("att2s"), root2.attribute(QStringLiteral("att2s"))); 0375 } 0376 0377 atLeastOne = true; 0378 empty = false; 0379 } 0380 } 0381 } 0382 0383 if (!atLeastOne) { 0384 element.removeChild(elementLine); 0385 } 0386 } 0387 if (!empty) { 0388 output = doc.toString(); 0389 } 0390 } 0391 } else { 0392 // Mode simple 0393 QDomDocument doc(QStringLiteral("SKGML")); 0394 QDomElement element = doc.createElement(QStringLiteral("simple")); 0395 element.setAttribute(QStringLiteral("query"), ui.kFilterEdit->text()); 0396 element.setAttribute(QStringLiteral("sql"), SKGServices::searchCriteriasToWhereClause(SKGServices::stringToSearchCriterias(ui.kFilterEdit->text()), m_attributes, m_document)); 0397 doc.appendChild(element); 0398 output = doc.toString(); 0399 } 0400 0401 return output; 0402 } 0403 0404 void SKGQueryCreator::onCloseEditor() 0405 { 0406 // If all lignes have at least one value then add a new line 0407 bool lineEmpty = false; 0408 int nbCol = ui.kList->columnCount(); 0409 int nbRow = ui.kList->rowCount(); 0410 for (int j = 0; !lineEmpty && j < nbRow; ++j) { 0411 lineEmpty = true; 0412 for (int i = 0; lineEmpty && i < nbCol; ++i) { 0413 QTableWidgetItem* it = ui.kList->item(j, i); 0414 if ((it != nullptr) && !it->text().isEmpty()) { 0415 lineEmpty = false; 0416 } 0417 } 0418 } 0419 0420 if (!lineEmpty) { 0421 addNewLine(); 0422 } 0423 0424 resizeColumns(); 0425 } 0426 0427 void SKGQueryCreator::onAddText() 0428 { 0429 auto* act = qobject_cast< QAction* >(sender()); 0430 if (act != nullptr) { 0431 ui.kFilterEdit->insert(act->data().toString()); 0432 } 0433 } 0434 0435 void SKGQueryCreator::onAddColumn() 0436 { 0437 QList<QListWidgetItem*> selection = ui.kListAtt->selectedItems(); 0438 if (selection.count() == 1) { 0439 QListWidgetItem* listItem = selection.at(0); 0440 addColumnFromAttribut(listItem); 0441 } 0442 } 0443 0444 void SKGQueryCreator::addColumnFromAttribut(const QListWidgetItem* iListItem) 0445 { 0446 if (iListItem != nullptr) { 0447 bool previous = ui.kList->blockSignals(true); 0448 0449 int nb = ui.kList->columnCount(); 0450 ui.kList->setColumnCount(nb + 1); 0451 0452 // Create header 0453 auto item = new QTableWidgetItem(iListItem->icon(), iListItem->text()); 0454 item->setData(Qt::UserRole, iListItem->data(Qt::UserRole)); 0455 ui.kList->setHorizontalHeaderItem(nb, item); 0456 0457 // Create items 0458 int nbRows = ui.kList->rowCount(); 0459 for (int i = 0; i < nbRows; ++i) { 0460 ui.kList->setItem(i, nb, new QTableWidgetItem()); 0461 } 0462 ui.kList->blockSignals(previous); 0463 0464 resizeColumns(); 0465 } 0466 } 0467 0468 void SKGQueryCreator::addNewLine() 0469 { 0470 // add line is only for 0471 if (!m_updateMode || ui.kList->rowCount() < 1) { 0472 bool previous = ui.kList->blockSignals(true); 0473 0474 int nbCol = ui.kList->columnCount(); 0475 int row = ui.kList->rowCount(); 0476 ui.kList->insertRow(row); 0477 0478 // Add a delete icon 0479 if (!m_updateMode) { 0480 ui.kList->setVerticalHeaderItem(row, new QTableWidgetItem(SKGServices::fromTheme(QStringLiteral("edit-delete")), QLatin1String(""))); 0481 } 0482 0483 for (int i = 0; i < nbCol; ++i) { 0484 auto item = new QTableWidgetItem(); 0485 ui.kList->setItem(row, i, item); 0486 } 0487 ui.kList->blockSignals(previous); 0488 0489 resizeColumns(); 0490 } 0491 } 0492 0493 int SKGQueryCreator::getColumnsCount() 0494 { 0495 return ui.kList->horizontalHeader()->count(); 0496 } 0497 0498 int SKGQueryCreator::getLinesCount() 0499 { 0500 return ui.kList->rowCount(); 0501 } 0502 0503 void SKGQueryCreator::removeColumn(int iColumn) 0504 { 0505 ui.kList->removeColumn(iColumn); 0506 0507 // To be sure that we have at least an empty line 0508 onCloseEditor(); 0509 } 0510 0511 void SKGQueryCreator::removeLine(int iRow) 0512 { 0513 ui.kList->removeRow(iRow); 0514 0515 // To be sure that we have at least an empty line 0516 onCloseEditor(); 0517 } 0518 0519 void SKGQueryCreator::resizeColumns() 0520 { 0521 ui.kList->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch); 0522 qApp->processEvents(QEventLoop::AllEvents, 300); 0523 ui.kList->horizontalHeader()->setSectionResizeMode(QHeaderView::Interactive); 0524 } 0525 0526 0527