Implemented extended filters

Extended the filter function in SeriesTreeWidget. By passing an operator
to the filter dialog series can be filtered. The code generates highly
dynamic SQL queries. I hope I didn't introduce possible SQL injections.

Filter operators:
g: genre
a: actor
q: quality
n: filename
d: dvd no
s: size

1 files changed, 103 insertions, 0 deletions

diff --git a/seriestreemodel.cpp b/seriestreemodel.cpp
index e25e759..0f20f3a 100644
--- a/seriestreemodel.cpp
+++ b/seriestreemodel.cpp
@@ -40,6 +40,8 @@ SeriesTreeModel::SeriesTreeModel(QStringList &headers, QObject *parent) : SmTree
 	mSortedMovieListQuery->prepare("SELECT files.tfilename, files.cmd5sum FROM series, seriesparts, files WHERE series.iseries_id = :id AND seriesparts.iseries_id = series.iseries_id AND seriesparts.iseriesparts_id = files.iseriespart_id AND files.sifiletype = 1 ORDER BY seriesparts.iseriespart, files.sifileno");
 	mNextDvdNoQuery = new QSqlQuery(mDb);
 	mNextDvdNoQuery->prepare("SELECT MAX(idvd) FROM files");
+	mGenreFilterQuery = new QSqlQuery(mDb);
+	mGenreFilterQuery->prepare("SELECT DISTINCT(series.iseries_id), series.tseries_name FROM series, seriesparts, seriesparts_genremap, genres where tgenrename LIKE :pat AND genres.igenres_id = seriesparts_genremap.igenres_id AND seriesparts_genremap.iseriesparts_id = seriesparts.iseriesparts_id AND seriesparts.iseries_id = series.iseries_id");
 	populate();
 }
 
@@ -324,9 +326,110 @@ bool SeriesTreeModel::addSeriesPart(int seriesPart, const QModelIndex &parent){
 	return false;
 }
 
+void SeriesTreeModel::clearFilter(){
+	populate();
+}
+
+void SeriesTreeModel::setMappingFilter(const QString &filter, const QString &table){
+	QString pattern = QString("%%1%").arg(filter);
+	QString tableIdTemplate = QString("SELECT i%1s_id FROM %1s WHERE t%1name LIKE :pat").arg(table);
+	QSqlQuery tableIdQuery(mDb);
+	tableIdQuery.prepare(tableIdTemplate);
+	tableIdQuery.bindValue(":pat", pattern);
+	if(tableIdQuery.exec()){
+		mSeriesPartSeriesMap.clear();
+		QStringList ids;
+		SmTreeItem *rootItem = new SmTreeItem(5);
+		while(tableIdQuery.next()){
+			ids << QString::number(tableIdQuery.value(0).toInt());
+		}
+		if(ids.isEmpty()){
+			return;
+		}
+		QString seriesPartsTemplate("SELECT DISTINCT(seriesparts.iseriesparts_id), seriesparts.iseriespart FROM seriesparts, seriesparts_%1map WHERE seriesparts.iseries_id = :id AND seriesparts_%1map.iseriesparts_id = seriesparts.iseriesparts_id AND seriesparts_%1map.i%1s_id IN (%2) ORDER BY iseriespart");
+		QString seriesIdTemplate = QString("SELECT DISTINCT(series.iseries_id), series.tseries_name FROM series, seriesparts, seriesparts_%1map, %1s WHERE %1s.i%1s_id IN (%2) AND %1s.i%1s_id = seriesparts_%1map.i%1s_id AND seriesparts_%1map.iseriesparts_id = seriesparts.iseriesparts_id AND seriesparts.iseries_id = series.iseries_id ORDER BY series.tseries_name").arg(table).arg(ids.join(","));
+		QSqlQuery seriesIdQuery(seriesIdTemplate, mDb);
+		while(seriesIdQuery.next()){
+			QList<QVariant> seriesData;
+			seriesData << seriesIdQuery.value(1) << seriesIdQuery.value(0) << QVariant() << QVariant() << Series;
+			SmTreeItem *seriesItem = new SmTreeItem(seriesData, rootItem);
+			rootItem->appendChild(seriesItem);
+			QString partsQueryString = seriesPartsTemplate.arg(table).arg(ids.join(","));
+			QSqlQuery partsQuery(mDb);
+			partsQuery.prepare(partsQueryString);
+			partsQuery.bindValue(":id", seriesIdQuery.value(0));
+			partsQuery.exec();
+			while(partsQuery.next()){
+				QList<QVariant> partData;
+				partData << seriesData.at(Name) << seriesData.at(SeriesId) << partsQuery.value(0) << partsQuery.value(1) << Part;
+				SmTreeItem *partItem = new SmTreeItem(partData, seriesItem);
+				seriesItem->appendChild(partItem);
+				mSeriesPartSeriesMap.insert(partsQuery.value(0).toInt(), seriesData.at(1).toInt());
+			}
+		}
+		setRoot(rootItem);
+	}
+}
+
+void SeriesTreeModel::setFileFilter(const QString &filter, const QString &column, int queryType){
+	QSqlQuery seriesIdQuery(mDb);
+	QString partsQueryTemplate;
+	QString op("=");
+	QString value = filter;
+	if(queryType == TextQuery){
+		QString idQueryTemplate = QString("SELECT DISTINCT(series.iseries_id), series.tseries_name FROM series, seriesparts, files WHERE files.%1 LIKE :value AND files.iseriespart_id = seriesparts.iseriesparts_id AND seriesparts.iseries_id = series.iseries_id ORDER BY series.tseries_name").arg(column);
+		seriesIdQuery.prepare(idQueryTemplate);
+		value = QString("%%1%").arg(filter);
+		seriesIdQuery.bindValue(":value", value);
+		partsQueryTemplate = QString("SELECT DISTINCT(seriesparts.iseriesparts_id), seriesparts.iseriespart FROM seriesparts, series, files WHERE series.iseries_id = :id AND series.iseries_id = seriesparts.iseries_id AND seriesparts.iseriesparts_id = files.iseriespart_id AND files.%1 LIKE :value");
+	}else if(queryType == NumericQuery){
+		if(value.startsWith("<") || value.startsWith(">") || value.startsWith("=")){
+			op = value.at(0);
+			value = value.right(value.size() - 1);
+		}
+		QString idQueryTemplate = QString("SELECT DISTINCT(series.iseries_id), series.tseries_name FROM series, seriesparts, files WHERE files.%1 %2 :value AND files.iseriespart_id = seriesparts.iseriesparts_id AND seriesparts.iseries_id = series.iseries_id ORDER BY series.tseries_name").arg(column).arg(op);
+		seriesIdQuery.prepare(idQueryTemplate);
+		seriesIdQuery.bindValue(":value", value);
+		partsQueryTemplate = QString("SELECT DISTINCT(seriesparts.iseriesparts_id), seriesparts.iseriespart FROM seriesparts, series, files WHERE series.iseries_id = :id AND series.iseries_id = seriesparts.iseries_id AND seriesparts.iseriesparts_id = files.iseriespart_id AND files.%1 %2 :value");
+	}else{
+		return;
+	}
+	if(seriesIdQuery.exec()){
+		mSeriesPartSeriesMap.clear();
+		SmTreeItem *rootItem = new SmTreeItem(5);
+		while(seriesIdQuery.next()){
+			QList<QVariant> seriesData;
+			seriesData << seriesIdQuery.value(1) << seriesIdQuery.value(0) << QVariant() << QVariant() << Series;
+			SmTreeItem *seriesItem = new SmTreeItem(seriesData, rootItem);
+			rootItem->appendChild(seriesItem);
+			QString partsQueryString;
+			if(queryType == TextQuery){
+				partsQueryString = partsQueryTemplate.arg(column);
+				value = QString("%%1%").arg(value);
+			}else{
+				partsQueryString = partsQueryTemplate.arg(column).arg(op);
+			}
+			QSqlQuery partsQuery(mDb);
+			partsQuery.prepare(partsQueryString);
+			partsQuery.bindValue(":id", seriesIdQuery.value(0));
+			partsQuery.bindValue(":value", value);
+			if(partsQuery.exec()){
+				while(partsQuery.next()){
+					QList<QVariant> partData;
+					partData << seriesData.at(Name) << seriesData.at(SeriesId) << partsQuery.value(0) << partsQuery.value(1) << Part;
+					SmTreeItem *partItem = new SmTreeItem(partData, seriesItem);
+					seriesItem->appendChild(partItem);
+					mSeriesPartSeriesMap.insert(partsQuery.value(0).toInt(), seriesData.at(1).toInt());
+				}
+			}
+		}
+		setRoot(rootItem);
+	}
+}
 
 void SeriesTreeModel::populate(){
 	QSqlQuery seriesQuery = QSqlQuery("SELECT iseries_id, tseries_name FROM series ORDER BY tseries_name", mDb);
+	mSeriesPartSeriesMap.clear();
 	SmTreeItem *rootItem = new SmTreeItem(5);
 	while(seriesQuery.next()){
 		QList<QVariant> seriesData;