25 #include <QDomDocument>
26 #include <QDomElement>
27 #include <QFileDialog>
28 #include <QInputDialog>
30 #include <QMessageBox>
32 #include <QPushButton>
33 #include <QTextStream>
39 QWidget *parent, Qt::WindowFlags fl )
40 : QDialog( parent, fl )
41 , mPreviousFieldRow( -1 )
46 connect( btnEqual, &QPushButton::clicked,
this, &QgsQueryBuilder::btnEqual_clicked );
47 connect( btnLessThan, &QPushButton::clicked,
this, &QgsQueryBuilder::btnLessThan_clicked );
48 connect( btnGreaterThan, &QPushButton::clicked,
this, &QgsQueryBuilder::btnGreaterThan_clicked );
49 connect( btnPct, &QPushButton::clicked,
this, &QgsQueryBuilder::btnPct_clicked );
50 connect( btnIn, &QPushButton::clicked,
this, &QgsQueryBuilder::btnIn_clicked );
51 connect( btnNotIn, &QPushButton::clicked,
this, &QgsQueryBuilder::btnNotIn_clicked );
52 connect( btnLike, &QPushButton::clicked,
this, &QgsQueryBuilder::btnLike_clicked );
53 connect( btnILike, &QPushButton::clicked,
this, &QgsQueryBuilder::btnILike_clicked );
54 connect( lstFields, &QListView::clicked,
this, &QgsQueryBuilder::lstFields_clicked );
55 connect( lstFields, &QListView::doubleClicked,
this, &QgsQueryBuilder::lstFields_doubleClicked );
56 connect( lstValues, &QListView::doubleClicked,
this, &QgsQueryBuilder::lstValues_doubleClicked );
57 connect( btnLessEqual, &QPushButton::clicked,
this, &QgsQueryBuilder::btnLessEqual_clicked );
58 connect( btnGreaterEqual, &QPushButton::clicked,
this, &QgsQueryBuilder::btnGreaterEqual_clicked );
59 connect( btnNotEqual, &QPushButton::clicked,
this, &QgsQueryBuilder::btnNotEqual_clicked );
60 connect( btnAnd, &QPushButton::clicked,
this, &QgsQueryBuilder::btnAnd_clicked );
61 connect( btnNot, &QPushButton::clicked,
this, &QgsQueryBuilder::btnNot_clicked );
62 connect( btnOr, &QPushButton::clicked,
this, &QgsQueryBuilder::btnOr_clicked );
63 connect( btnGetAllValues, &QPushButton::clicked,
this, &QgsQueryBuilder::btnGetAllValues_clicked );
64 connect( btnSampleValues, &QPushButton::clicked,
this, &QgsQueryBuilder::btnSampleValues_clicked );
65 connect( buttonBox, &QDialogButtonBox::helpRequested,
this, &QgsQueryBuilder::showHelp );
67 QPushButton *pbn =
new QPushButton( tr(
"&Test" ) );
68 buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
71 pbn =
new QPushButton( tr(
"&Clear" ) );
72 buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
75 pbn =
new QPushButton( tr(
"&Save…" ) );
76 buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
77 pbn->setToolTip( tr(
"Save query to QQF file" ) );
80 pbn =
new QPushButton( tr(
"&Load…" ) );
81 buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
82 pbn->setToolTip( tr(
"Load query from QQF file" ) );
89 mUseUnfilteredLayer->setDisabled( mLayer->
subsetString().isEmpty() );
91 lblDataUri->setText( tr(
"Set provider filter on %1" ).arg( layer->
name() ) );
92 txtSQL->setText( mOrigSubsetString );
94 mFilterLineEdit->setShowSearchIcon(
true );
95 mFilterLineEdit->setPlaceholderText( tr(
"Search…" ) );
96 connect( mFilterLineEdit, &QgsFilterLineEdit::textChanged,
this, &QgsQueryBuilder::onTextChanged );
104 QDialog::showEvent( event );
107 void QgsQueryBuilder::populateFields()
110 txtSQL->setFields( fields );
111 for (
int idx = 0; idx < fields.
count(); ++idx )
119 myItem->setData( idx );
120 myItem->setEditable(
false );
121 mModelFields->insertRow( mModelFields->rowCount(), myItem );
125 setupLstFieldsModel();
128 void QgsQueryBuilder::setupLstFieldsModel()
130 lstFields->setModel( mModelFields );
133 void QgsQueryBuilder::setupGuiViews()
136 mModelFields =
new QStandardItemModel();
137 mModelValues =
new QStandardItemModel();
138 mProxyValues =
new QSortFilterProxyModel();
139 mProxyValues->setSourceModel( mModelValues );
141 lstFields->setViewMode( QListView::ListMode );
142 lstValues->setViewMode( QListView::ListMode );
143 lstFields->setSelectionBehavior( QAbstractItemView::SelectRows );
144 lstValues->setSelectionBehavior( QAbstractItemView::SelectRows );
146 lstFields->setUniformItemSizes(
true );
147 lstValues->setUniformItemSizes(
true );
149 lstFields->setAlternatingRowColors(
true );
150 lstValues->setAlternatingRowColors(
true );
151 lstValues->setModel( mProxyValues );
154 void QgsQueryBuilder::fillValues(
int idx,
int limit )
157 mModelValues->clear();
160 QList<QVariant> values = qgis::setToList( mLayer->
uniqueValues( idx, limit ) );
161 std::sort( values.begin(), values.end() );
165 QgsDebugMsg( QStringLiteral(
"nullValue: %1" ).arg( nullValue ) );
167 const auto constValues = values;
168 for (
const QVariant &var : constValues )
173 else if ( var.type() == QVariant::Date && mLayer->
providerType() == QLatin1String(
"ogr" ) && mLayer->
storageType() == QLatin1String(
"ESRI Shapefile" ) )
174 value = var.toDate().toString( QStringLiteral(
"yyyy/MM/dd" ) );
176 value = var.toString();
178 QStandardItem *myItem =
new QStandardItem( value );
179 myItem->setEditable(
false );
180 myItem->setData( var, Qt::UserRole + 1 );
181 mModelValues->insertRow( mModelValues->rowCount(), myItem );
182 QgsDebugMsg( QStringLiteral(
"Value is null: %1\nvalue: %2" ).arg( var.isNull() ).arg( var.isNull() ? nullValue : var.toString() ) );
186 void QgsQueryBuilder::btnSampleValues_clicked()
188 lstValues->setCursor( Qt::WaitCursor );
191 if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
197 fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), 25 );
204 lstValues->setCursor( Qt::ArrowCursor );
207 void QgsQueryBuilder::btnGetAllValues_clicked()
209 lstValues->setCursor( Qt::WaitCursor );
212 if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
218 fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), -1 );
225 lstValues->setCursor( Qt::ArrowCursor );
236 mUseUnfilteredLayer->setDisabled( mLayer->
subsetString().isEmpty() );
240 if ( featureCount < 0 )
242 QMessageBox::warning(
this,
243 tr(
"Query Result" ),
244 tr(
"An error occurred when executing the query, please check the expression syntax." ) );
248 QMessageBox::information(
this,
249 tr(
"Query Result" ),
250 tr(
"The where clause returned %n row(s).",
"returned test rows", featureCount ) );
255 QMessageBox::warning(
this,
256 tr(
"Query Result" ),
257 tr(
"An error occurred when executing the query." )
258 + tr(
"\nThe data provider said:\n%1" ).arg( mLayer->
dataProvider()->
errors().join( QLatin1Char(
'\n' ) ) ) );
263 QMessageBox::warning(
this,
264 tr(
"Query Result" ),
265 tr(
"An error occurred when executing the query." ) );
271 if ( txtSQL->text() != mOrigSubsetString )
278 QMessageBox::warning(
this,
279 tr(
"Query Result" ),
280 tr(
"An error occurred when executing the query." )
281 + tr(
"\nThe data provider said:\n%1" ).arg( mLayer->
dataProvider()->
errors().join( QLatin1Char(
'\n' ) ) ) );
286 QMessageBox::warning(
this, tr(
"Query Result" ), tr(
"Error in query. The subset string could not be set." ) );
304 void QgsQueryBuilder::btnEqual_clicked()
306 txtSQL->insertText( QStringLiteral(
" = " ) );
310 void QgsQueryBuilder::btnLessThan_clicked()
312 txtSQL->insertText( QStringLiteral(
" < " ) );
316 void QgsQueryBuilder::btnGreaterThan_clicked()
318 txtSQL->insertText( QStringLiteral(
" > " ) );
322 void QgsQueryBuilder::btnPct_clicked()
324 txtSQL->insertText( QStringLiteral(
"%" ) );
328 void QgsQueryBuilder::btnIn_clicked()
330 txtSQL->insertText( QStringLiteral(
" IN " ) );
334 void QgsQueryBuilder::btnNotIn_clicked()
336 txtSQL->insertText( QStringLiteral(
" NOT IN " ) );
340 void QgsQueryBuilder::btnLike_clicked()
342 txtSQL->insertText( QStringLiteral(
" LIKE " ) );
348 return txtSQL->text();
353 txtSQL->setText( sqlStatement );
356 void QgsQueryBuilder::lstFields_clicked(
const QModelIndex &index )
358 if ( mPreviousFieldRow != index.row() )
360 mPreviousFieldRow = index.row();
362 btnSampleValues->setEnabled(
true );
363 btnGetAllValues->setEnabled(
true );
365 mModelValues->clear();
366 mFilterLineEdit->clear();
370 void QgsQueryBuilder::lstFields_doubleClicked(
const QModelIndex &index )
372 txtSQL->insertText(
'\"' + mLayer->
fields().
at( mModelFields->data( index, Qt::UserRole + 1 ).toInt() ).
name() +
'\"' );
376 void QgsQueryBuilder::lstValues_doubleClicked(
const QModelIndex &index )
378 QVariant value = index.data( Qt::UserRole + 1 );
379 if ( value.isNull() )
380 txtSQL->insertText( QStringLiteral(
"NULL" ) );
381 else if ( value.type() == QVariant::Date && mLayer->
providerType() == QLatin1String(
"ogr" ) && mLayer->
storageType() == QLatin1String(
"ESRI Shapefile" ) )
382 txtSQL->insertText(
'\'' + value.toDate().toString( QStringLiteral(
"yyyy/MM/dd" ) ) +
'\'' );
383 else if ( value.type() == QVariant::Int || value.type() == QVariant::Double || value.type() == QVariant::LongLong || value.type() == QVariant::Bool )
384 txtSQL->insertText( value.toString() );
386 txtSQL->insertText(
'\'' + value.toString().replace(
'\'', QLatin1String(
"''" ) ) +
'\'' );
391 void QgsQueryBuilder::btnLessEqual_clicked()
393 txtSQL->insertText( QStringLiteral(
" <= " ) );
397 void QgsQueryBuilder::btnGreaterEqual_clicked()
399 txtSQL->insertText( QStringLiteral(
" >= " ) );
403 void QgsQueryBuilder::btnNotEqual_clicked()
405 txtSQL->insertText( QStringLiteral(
" != " ) );
409 void QgsQueryBuilder::btnAnd_clicked()
411 txtSQL->insertText( QStringLiteral(
" AND " ) );
415 void QgsQueryBuilder::btnNot_clicked()
417 txtSQL->insertText( QStringLiteral(
" NOT " ) );
421 void QgsQueryBuilder::btnOr_clicked()
423 txtSQL->insertText( QStringLiteral(
" OR " ) );
427 void QgsQueryBuilder::onTextChanged(
const QString &text )
429 mProxyValues->setFilterCaseSensitivity( Qt::CaseInsensitive );
430 mProxyValues->setFilterWildcard( text );
437 mUseUnfilteredLayer->setDisabled(
true );
440 void QgsQueryBuilder::btnILike_clicked()
442 txtSQL->insertText( QStringLiteral(
" ILIKE " ) );
448 lblDataUri->setText( uri );
451 void QgsQueryBuilder::showHelp()
453 QgsHelp::openHelp( QStringLiteral(
"working_with_vector/vector_properties.html#query-builder" ) );
459 QString lastQueryFileDir = s.
value( QStringLiteral(
"/UI/lastQueryFileDir" ), QDir::homePath() ).toString();
461 QString saveFileName = QFileDialog::getSaveFileName(
nullptr, tr(
"Save Query to File" ), lastQueryFileDir, tr(
"Query files (*.qqf *.QQF)" ) );
462 if ( saveFileName.isNull() )
467 if ( !saveFileName.endsWith( QLatin1String(
".qqf" ), Qt::CaseInsensitive ) )
469 saveFileName += QLatin1String(
".qqf" );
472 QFile saveFile( saveFileName );
473 if ( !saveFile.open( QIODevice::WriteOnly | QIODevice::Truncate ) )
475 QMessageBox::critical(
nullptr, tr(
"Save Query to File" ), tr(
"Could not open file for writing." ) );
480 QDomElement queryElem = xmlDoc.createElement( QStringLiteral(
"Query" ) );
481 QDomText queryTextNode = xmlDoc.createTextNode( txtSQL->text() );
482 queryElem.appendChild( queryTextNode );
483 xmlDoc.appendChild( queryElem );
485 QTextStream fileStream( &saveFile );
486 xmlDoc.save( fileStream, 2 );
488 QFileInfo fi( saveFile );
489 s.
setValue( QStringLiteral(
"/UI/lastQueryFileDir" ), fi.absolutePath() );
495 QString lastQueryFileDir = s.
value( QStringLiteral(
"/UI/lastQueryFileDir" ), QDir::homePath() ).toString();
497 QString queryFileName = QFileDialog::getOpenFileName(
nullptr, tr(
"Load Query from File" ), lastQueryFileDir, tr(
"Query files" ) +
" (*.qqf);;" + tr(
"All files" ) +
" (*)" );
498 if ( queryFileName.isNull() )
503 QFile queryFile( queryFileName );
504 if ( !queryFile.open( QIODevice::ReadOnly ) )
506 QMessageBox::critical(
nullptr, tr(
"Load Query from File" ), tr(
"Could not open file for reading." ) );
509 QDomDocument queryDoc;
510 if ( !queryDoc.setContent( &queryFile ) )
512 QMessageBox::critical(
nullptr, tr(
"Load Query from File" ), tr(
"File is not a valid xml document." ) );
516 QDomElement queryElem = queryDoc.firstChildElement( QStringLiteral(
"Query" ) );
517 if ( queryElem.isNull() )
519 QMessageBox::critical(
nullptr, tr(
"Load Query from File" ), tr(
"File is not a valid query document." ) );
523 QString query = queryElem.text();
534 txtSQL->insertText( query );