Manually selecting Items from a database

[Abderaouf]

Gents;

I am about to start coding a program that gives the user the choice of selecting items from a database. The database contains a table with few columns (reference, description, unit, price). The program connects to the database through a pushbutton and displays all the items in a dialog window, the user will be able to select the items he wants using a checkbox for example. My question is, is that possible with sql database or is there any other way to do that.

Regards;

[SGaist]

Hi,

Unless I misunderstand you can use a QSqlTableModel to show the content of the table you are interested in.

Hope it helps

[Abderaouf]

@SGaist

Yeah, I can display the database in a QSqlTableModel in the dialog window, the question is can I select manually the items I need and then display them in a QTablewidget where I can add the quantity of each item and calculate the price per row?

[VRonin]

I have two solutions but they depend on a very important question:
Do any change done in either the Dialog or the final QTableWidget need to be reflected in the DB (i.e. if I change something in the app it gets changed in the database)?

[Abderaouf]

@VRonin
Absolutely not. The database is something fixed, kind of contract that I just want to take items from.

cheers;

[VRonin]

flaggableproxy.h

#ifndef FLAGGABLEPROXY_H
#define FLAGGABLEPROXY_H

#include <QSortFilterProxyModel>

class FlaggableProxy : public QSortFilterProxyModel
{
    Q_OBJECT
	Q_DISABLE_COPY(FlaggableProxy)
public:
    explicit FlaggableProxy(QObject *parent=nullptr);
    virtual ~FlaggableProxy() = default;
    virtual Qt::ItemFlags flags(const QModelIndex & index) const override;
    virtual void setFlagForColumn(qint32 col, Qt::ItemFlags val);
    virtual void removeFlagForColumn(qint32 col);
    virtual Qt::ItemFlags flagsForColumn(qint32 col) const;
    virtual void setFlagForRow(qint32 row, Qt::ItemFlags val);
    virtual void removeFlagForRow(qint32 row);
    virtual Qt::ItemFlags flagsForRow(qint32 row) const;
    virtual void setFlag(qint32 row, qint32 col, Qt::ItemFlags val);
    virtual void removeFlag(qint32 row, qint32 col);
    virtual Qt::ItemFlags getFlag(qint32 row, qint32 col)const;
    virtual void clearFlags();

    virtual void sort(int column, Qt::SortOrder order = Qt::AscendingOrder) override;
    virtual void setSortRoleForColumn(qint32 col, int val);
    virtual int sortRoleForColumn(qint32 col) const;
    virtual void clearSortRoles();
private:
    QHash<qint64, Qt::ItemFlags> m_flagStorage;
    QHash<qint32, Qt::ItemFlags> m_rowFlagStorage;
    QHash<qint32, Qt::ItemFlags> m_colFlagStorage;
    QHash<qint32, int> m_sortRoles;
};
#endif // FLAGGABLEPROXY_H

flaggableproxy.cpp

#include "FlaggableProxy.h"

FlaggableProxy::FlaggableProxy(QObject *parent)
    : QSortFilterProxyModel(parent)
{

}


Qt::ItemFlags FlaggableProxy::flags(const QModelIndex & idx) const
{
    const QModelIndex index = mapToSource(idx);
    const qint64 hashIndex = (static_cast<qint64>(index.row()) << 32) | static_cast<qint64>(index.column());        
    if (m_flagStorage.contains(hashIndex))
        return m_flagStorage.value(hashIndex) | Qt::ItemNeverHasChildren;
    if (m_rowFlagStorage.contains(index.row()))
        return m_rowFlagStorage.value(index.row()) | Qt::ItemNeverHasChildren;
    if (m_colFlagStorage.contains(index.column()))
        return m_colFlagStorage.value(index.column()) | Qt::ItemNeverHasChildren;
    return QSortFilterProxyModel::flags(idx) | Qt::ItemNeverHasChildren;
}

void FlaggableProxy::setFlagForColumn(qint32 col, Qt::ItemFlags val)
{
    m_colFlagStorage[col] = val;
}

void FlaggableProxy::removeFlagForColumn(qint32 col)
{
    m_colFlagStorage.remove(col);
}

Qt::ItemFlags FlaggableProxy::flagsForColumn(qint32 col) const
{
    return m_colFlagStorage.value(col, QSortFilterProxyModel::flags(index(0, col)));
}

void FlaggableProxy::setFlagForRow(qint32 row, Qt::ItemFlags val)
{
    m_rowFlagStorage[row] = val;
}

void FlaggableProxy::removeFlagForRow(qint32 row)
{
    m_colFlagStorage.remove(row);
}

Qt::ItemFlags FlaggableProxy::flagsForRow(qint32 row) const
{
    return m_rowFlagStorage.value(row, QSortFilterProxyModel::flags(index(row, 0)));
}

void FlaggableProxy::setFlag(qint32 row, qint32 col, Qt::ItemFlags val)
{
    const qint64 hashIndex = (static_cast<qint64>(row) << 32) | static_cast<qint64>(col);
    m_flagStorage[hashIndex] = val;
}

void FlaggableProxy::removeFlag(qint32 row, qint32 col)
{
    const qint64 hashIndex = (static_cast<qint64>(row) << 32) | static_cast<qint64>(col);
    m_flagStorage.remove(hashIndex);
}

Qt::ItemFlags FlaggableProxy::getFlag(qint32 row, qint32 col) const
{
    const qint64 hashIndex = (static_cast<qint64>(row) << 32) | static_cast<qint64>(col);
    return m_flagStorage.value(hashIndex, QSortFilterProxyModel::flags(index(row, col)));
}

void FlaggableProxy::clearFlags()
{
    m_flagStorage.clear();
    m_rowFlagStorage.clear();
    m_colFlagStorage.clear();
}

void FlaggableProxy::sort(int column, Qt::SortOrder order)
{
    setSortRole(m_sortRoles.value(column, sortRole()));
    QSortFilterProxyModel::sort(column, order);
}

void FlaggableProxy::setSortRoleForColumn(qint32 col, int val)
{
    if (val == Qt::DisplayRole) {
        m_sortRoles.remove(col);
        return;
    }
    m_sortRoles[col] = val;
}

int FlaggableProxy::sortRoleForColumn(qint32 col) const
{
    return m_sortRoles.value(col, Qt::DisplayRole);
}

void FlaggableProxy::clearSortRoles()
{
    m_sortRoles.clear();
}

now:

    enum ModelColumns{
        mcReference
        ,mcDescription
        ,mcUnit
        ,mcPrice
        ,mcQuantity
        ,mcTotalPrice
        
        ,ModColCount
    };
    
    QAbstractItemModel* m_model=new QStandardItemModel(this);
    m_model->insertColumns(0,ModColCount);
    m_model->setHeaderData(mcReference,Qt::Horizontal,QStringLiteral("Reference"));
    m_model->setHeaderData(mcDescription,Qt::Horizontal,QStringLiteral("Description"));
    m_model->setHeaderData(mcUnit,Qt::Horizontal,QStringLiteral("Unit"));
    m_model->setHeaderData(mcPrice,Qt::Horizontal,QStringLiteral("Price"));
    m_model->setHeaderData(mcQuantity,Qt::Horizontal,QStringLiteral("Quantity"));
    m_model->setHeaderData(mcTotalPrice,Qt::Horizontal,QStringLiteral("Total Price"));
    
    
    QSqlQuery dataQuery;
    dataQuery.prepare("select Reference,Description,Unit,Price from MyTable");
    if(dataQuery.exec()){
        while(dataQuery.next()){
            const QSqlRecord currRec= dataQuery.record();
            const int newRow=m_model->rowCount();
            m_model->insertRow(newRow);
            m_model->setData(m_model->index(newRow,mcReference),currRec.value(QStringLiteral("Reference")));
            m_model->setData(m_model->index(newRow,mcDescription),currRec.value(QStringLiteral("Description")));
            m_model->setData(m_model->index(newRow,mcUnit),currRec.value(QStringLiteral("Unit")));
            m_model->setData(m_model->index(newRow,mcPrice),currRec.value(QStringLiteral("Price")));
        }
    }
    
    connect(m_model,&QAbstractItemModel::dataChanged,[=](const QModelIndex& idx)->void{
        if(idx.column()==mcQuantity || idx.column()==mcPrice)
            m_model->setData(
                        m_model->index(id.row(),mcTotalPrice)
                        , m_model->index(id.row(),mcQuantity).data().toDouble()*m_model->index(id.row(),mcPrice).data().toDouble()
                        );
    });
    
    FlaggableProxy* dialogModelProxy = new FlaggableProxy(this);
    dialogModelProxy->setSourceModel(m_model);
    dialogModelProxy->setFlagForColumn(0,Qt::ItemIsSelectable | Qt::ItemIsUserCheckable | Qt::ItemIsEnabled);
    
    QSortFilterProxyModel* finalProxy = new QSortFilterProxyModel(this);
    finalProxy->setFilterKeyColumn(0);
    finalProxy->setFilterRole(Qt::CheckStateRole);
    finalProxy->setFilterFixedString(QString::number(Qt::Checked));

finalProxy is the model for the QTableView that will replace the final QTableWidget and dialogModelProxy is the model for the QTableView in the dialog.

you can use QTableView::setColumnHidden to decide which columns to hide in each table