path: root/mappingtablemodel.cpp

/*
  This program is free software; you can redistribute it and/or
  modify it under the terms of the GNU General Public License
  as published by the Free Software Foundation; either version
  2 of the License, or (at your option) any later version.
*/

#include <QSqlQuery>

#include "mappingtablemodel.h"
#include "smtreeitem.h"

MappingTableModel::MappingTableModel(QStringList &headers, const QString &table, QObject *parent) : SmTreeModel(headers, parent), mTable(table){
	//database setup
	mDb = QSqlDatabase::database("treedb");
	QString tmpTable = mTable;
	tmpTable.chop(1);
	mMappingTable = QString("seriesparts_%1map").arg(tmpTable);
	mIdColumnName = QString("i%1_id").arg(table);
	mNameColumnName = QString("t%1name").arg(tmpTable);
	mSequenceName = QString("%1_i%1_id__seq").arg(mTable);

	//queries
	QString updateNameQuery = QString("UPDATE %1 set %2 = :name WHERE %3 = :id").arg(mTable).arg(mNameColumnName).arg(mIdColumnName);
	mUpdateItemNameQuery = new QSqlQuery(mDb);
	mUpdateItemNameQuery->prepare(updateNameQuery);
	QString insertItemQuery = QString("INSERT INTO %1 (%2) VALUES (:name)").arg(mTable).arg(mNameColumnName);
	mInsertItemQuery = new QSqlQuery(mDb);
	mInsertItemQuery->prepare(insertItemQuery);
	QString deleteItemQuery = QString("DELETE FROM %1 WHERE %2 = :id").arg(mTable).arg(mIdColumnName);
	mDeleteItemQuery = new QSqlQuery(mDb);
	mDeleteItemQuery->prepare(deleteItemQuery);
	QString addMappingQuery = QString("INSERT INTO %1 (iseriesparts_id, %2) VALUES (:id1, :id2)").arg(mMappingTable).arg(mIdColumnName);
	mAddMappingQuery = new QSqlQuery(mDb);
	mAddMappingQuery->prepare(addMappingQuery);
	QString removeMappingQuery = QString("DELETE FROM %1 WHERE iseriesparts_id = :id1 AND %2 = :id2").arg(mMappingTable).arg(mIdColumnName);
	mRemoveMappingQuery = new QSqlQuery(mDb);
	mRemoveMappingQuery->prepare(removeMappingQuery);
	QString mappingQuery = QString("SELECT %1 FROM %2, %3 WHERE %2.%4 = %3.%4 AND %3.iseriesparts_id = :id").arg(mNameColumnName).arg(mTable).arg(mMappingTable).arg(mIdColumnName);
	mMappingQuery = new QSqlQuery(mDb);
	mMappingQuery->prepare(mappingQuery);
	QString allItemsQuery = QString("SELECT %1, %2 FROM %3").arg(mIdColumnName).arg(mNameColumnName).arg(mTable);
	mAllItemsQuery = new QSqlQuery(mDb);
	mAllItemsQuery->prepare(allItemsQuery);
	QString itemCountQuery = QString("SELECT COUNT(*) FROM %1 WHERE %2 = :id").arg(mMappingTable).arg(mIdColumnName);
	mItemCountQuery = new QSqlQuery(mDb);
	mItemCountQuery->prepare(itemCountQuery);

	//get data
	populate();
}

MappingTableModel::~MappingTableModel(){
	delete mUpdateItemNameQuery;
	delete mInsertItemQuery;
	delete mDeleteItemQuery;
	delete mAddMappingQuery;
	delete mRemoveMappingQuery;
	delete mMappingQuery;
	delete mAllItemsQuery;
	delete mItemCountQuery;
	mDb = QSqlDatabase();
}

Qt::ItemFlags MappingTableModel::flags(const QModelIndex &index) const{
	if(!index.isValid()){
		return 0;
	}
	Qt::ItemFlags retval = Qt::ItemIsEnabled | Qt::ItemIsSelectable;
	if(index.column() == ItemName){
		return retval | Qt::ItemIsEditable;
	}
	return retval;
}

QVariant MappingTableModel::data(const QModelIndex &index, int role) const{
	if(!index.isValid()){
		return QVariant();
	}

	SmTreeItem *item = static_cast<SmTreeItem*>(index.internalPointer());

	if(role == Qt::DisplayRole || role == Qt::EditRole){
		return item->data(index.column());
	}
	if(role == ItemNameRole){
		return item->data(ItemName);
	}
	if(role == ItemIdRole){
		return item->data(ItemId);
	}
	if(role == Qt::DecorationRole){
		return SmTreeModel::data(index, role);
	}
	return QVariant();
}

bool MappingTableModel::setData(const QModelIndex &index, const QVariant &value, int role){
	if(role != Qt::EditRole || !index.isValid()){
		return false;
	}
	if(index.column() != ItemName){
		return false;
	}
	SmTreeItem *item = static_cast<SmTreeItem*>(index.internalPointer());
	QString newName = value.toString().toLower().trimmed();
	mUpdateItemNameQuery->bindValue(":name", newName);
	mUpdateItemNameQuery->bindValue(":id", item->data(ItemId));
	if(mUpdateItemNameQuery->exec()){
		item->setData(ItemName, newName);
		return true;
	}
	return false;
}

bool MappingTableModel::contains(const QString &value) const{
	return mItemNames.contains(value);
}

void MappingTableModel::setMappings(const QStringList &itemNames, int seriesPartId){
	if(seriesPartId == -1){
		return;
	}
	foreach(const QString name, itemNames){
		QModelIndex itemIdx = find(name);
		if(!itemIdx.isValid()){
			addItem(name);
		}
		itemIdx = find(name);
		int itemId = itemIdx.data(ItemIdRole).toInt();
		addMapping(seriesPartId, itemId);
	}
}

QStringList MappingTableModel::findPartialName(const QString &value) const{
	QStringList retval;
	if(value.isEmpty()){
		retval = mItemNames;
	}else{
		foreach(const QString cur, mItemNames){
			if(cur.contains(value)){
				retval << cur;
			}
		}
	}
	qSort(retval);
	return retval;
}

bool MappingTableModel::addItem(const QVariant &name){
	QString newValue = name.toString().toLower().trimmed();
	if(mItemNames.contains(newValue)){
		return false;
	}
	mDb.transaction();
	mInsertItemQuery->bindValue(":name", newValue);
	if(mInsertItemQuery->exec()){
		QString sequenceQuery = QString("SELECT currval('%1')").arg(mSequenceName);
		QSqlQuery lastId(sequenceQuery, mDb);
		if(lastId.next()){
			QVariant newId = lastId.value(0);
			QList<QVariant> data;
			data << newValue << newId;
			addRow(data, QModelIndex());
			mItemNames << newValue;
			mDb.commit();
			emit needResort();
			return true;
		}
	}
	mDb.rollback();
	return false;
}

bool MappingTableModel::removeItem(const QModelIndex &idx){
	if(!idx.isValid()){
		return false;
	}
	QString name = idx.data(ItemNameRole).toString();
	if(!mItemNames.contains(name)){
		return false;
	}
	mDeleteItemQuery->bindValue(":id", idx.data(ItemIdRole));
	if(mDeleteItemQuery->exec()){
		removeRows(idx.row(), 1, idx.parent());
		return true;
	}
	return false;
}

bool MappingTableModel::addMapping(int seriesPartId, int itemId){
	if(seriesPartId == -1){
		return false;
	}
	mDb.transaction();
	mAddMappingQuery->bindValue(":id1", seriesPartId);
	mAddMappingQuery->bindValue(":id2", itemId);
	if(!mAddMappingQuery->exec()){
		mDb.rollback();
		return false;
	}
	mDb.commit();
	return true;
}

bool MappingTableModel::removeMapping(int seriesId, int itemId){
	mRemoveMappingQuery->bindValue(":id1", seriesId);
	mRemoveMappingQuery->bindValue(":id2", itemId);
	return mRemoveMappingQuery->exec();
}

QList<QVariant> MappingTableModel::mappings(int seriesId){
	QList<QVariant> retval;
	mMappingQuery->bindValue(":id", seriesId);
	if(mMappingQuery->exec()){
		while(mMappingQuery->next()){
			retval << mMappingQuery->value(0);
		}
	}
	return retval;
}

QHash<QString, int> MappingTableModel::zeroUsers(){
	QHash<int, QString> items;
	mAllItemsQuery->exec();
	while(mAllItemsQuery->next()){
		items.insert(mAllItemsQuery->value(0).toInt(), mAllItemsQuery->value(1).toString());
	}
	QHash<QString, int> retval;
	if(!items.isEmpty()){
		QList<int> ids = items.keys();
		foreach(int id, ids){
			mItemCountQuery->bindValue(":id", id);
			if(mItemCountQuery->exec()){
				mItemCountQuery->first();
				int count = mItemCountQuery->value(0).toInt();
				if(count == 0){
					QString itemName = items.value(id);
					retval.insert(itemName, id);
				}
			}
		}
	}
	return retval;
}

void MappingTableModel::deleteItems(const QList<int> &ids){
	if(ids.isEmpty()){
		return;
	}
	foreach(int id, ids){
		mDeleteItemQuery->bindValue(":id", id);
		mDeleteItemQuery->exec();
	}
	populate();
}

void MappingTableModel::populate(){
	QString query = QString("SELECT %1, %2 FROM %3 ORDER BY %2").arg(mIdColumnName).arg(mNameColumnName).arg(mTable);
	QList<QVariant> rootData;
	mItemNames.clear();
	rootData << QString(tr("%1 name")).arg(mTable) << tr("Id");
	SmTreeItem *root = new SmTreeItem(rootData);
	QSqlQuery dataQuery(query, mDb);
	while(dataQuery.next()){
		QList<QVariant> data;
		data << dataQuery.value(1); //name
		data << dataQuery.value(0); //id
		SmTreeItem *item = new SmTreeItem(data, root);
		root->appendChild(item);
		mItemNames << dataQuery.value(1).toString();
	}
	setRoot(root);
}