C++ QSqlTableModel::setFilter方法代码示例

void MainWindow::importData()
{
	int exam_id;
	QString dataFileName = QFileDialog::getOpenFileName(this,tr("Import Data From a CSV File"),QDir::homePath(),tr("CSV File (*.csv)"));
	if(dataFileName.isEmpty())
		return;
	QFile dataFile(dataFileName);
	if(!dataFile.open(QIODevice::ReadOnly))
	{
		QMessageBox::critical(this,tr("Open File Failed"),tr("Cannot open selected file"));;
		return;
	}

	QSqlTableModel* student = m_scoreView->studentListModel();
	QSqlTableModel* exam = m_scoreView->examListModel();
	QSqlRelationalTableModel* score = m_scoreView->scoreListModel();

	QFileInfo tmp(dataFileName);
	QString exam_name = tmp.baseName();
	exam->setFilter(QString("name = \'%1\'").arg(exam_name));
	if(exam->rowCount() == 1)
	{
		QSqlRecord rq = exam->record(0);
		exam_id = rq.value("id").toInt();
	}
	else
	{
		exam->insertRow(0);
		exam->setData(exam->index(0,Global::Exam_Name),exam_name);
		exam->submitAll();
		exam->setFilter(QString("name = \'%1\'").arg(exam_name));
		QSqlRecord rq = exam->record(0);
		exam_id = rq.value("id").toInt();
	}

	QHash<QString,int> studentMap;
	for(int row = 0;row < student->rowCount();++row)
		studentMap.insert(student->data(student->index(row,1)).toString(),student->data(student->index(row,0)).toInt());

	QTextStream in(&dataFile);
	QString record = in.readLine();
	QStringList candidate_seps;
	candidate_seps << ";" << "," << "\t" << " ";
	QString real_sep;
	bool is_legal = false;
	QStringList cols;
	foreach(real_sep, candidate_seps)
	{
		cols = record.split(real_sep);
		if(cols.size() == 11)
		{
			is_legal = true;
			break;
		}
	}

void CollectionTreeWidget::showChildrenOf(QModelIndex index) {
    CollectionTreeWidgetItem *item = (CollectionTreeWidgetItem*)itemFromIndex(index);

    // If the item pressed was an artist, add albums
    if (item->getNodeLevel() == LevelArtist) {
        QString artist = item->text(0).toUtf8();

        // Looks for artist id
        QString artistId = QString::number(item->getId());

        // Looks for artist albums
        QSqlTableModel *albumModel = service->albumModel();
        albumModel->setFilter("id_artist = " + artistId);
        albumModel->select();

        while (albumModel->canFetchMore()) albumModel->fetchMore();
        int total = albumModel->rowCount();

        for (int i = 0; i < total; i++) {
            QString album = albumModel->record(i).value(albumModel->fieldIndex("title")).toString();
            unsigned int id = albumModel->record(i).value(albumModel->fieldIndex("id")).toInt();
            addAlbum(artist, album, id);
        }

        delete albumModel;
    }
    // If the item pressed was an album, add songs
    else if (item->getNodeLevel() == LevelAlbum) {
        QString albumId = QString::number(item->getId());

        QSqlTableModel *musicModel = service->musicModel();
        musicModel->setFilter("id_album = " + albumId);
        musicModel->setSort(musicModel->fieldIndex("track_number"), Qt::AscendingOrder);
        musicModel->select();

        while (musicModel->canFetchMore()) musicModel->fetchMore();
        int total = musicModel->rowCount();

        for (int i = 0; i < total; i++) {
            QString path = musicModel->record(i).value(musicModel->fieldIndex("path")).toString();
            unsigned int id = musicModel->record(i).value(musicModel->fieldIndex("id")).toInt();

            Music *music = new Music(QUrl(path));
            addMusic(music, id);
            delete music;
        }
    }

    expand(index);
}

int main( int argc, char *argv[] )
{
    QApplication app( argc, argv );

    QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );
    db.setDatabaseName( "testdatabase.db" );

    if( !db.open() )
    {
        qDebug() << db.lastError();
        qFatal( "Failed to connect." );
    }

    QSqlQuery qry;
    qry.prepare( "CREATE TABLE IF NOT EXISTS names (id INTEGER UNIQUE PRIMARY KEY, "
            "firstname VARCHAR(30), "
            "lastname VARCHAR(30))" );
    if( !qry.exec() )
    {
        qDebug() << qry.lastError();
        qFatal( "Failed to create the table" );
    }

    qry.prepare( "INSERT INTO names (id, firstname, lastname) "
            "VALUES (:id, :firstname, :lastname)" );
    qry.bindValue( ":id", 1 );
    qry.bindValue( ":firstname", "John" );
    qry.bindValue( ":lastname", "Doe" );
    if( !qry.exec() )
    {
        qDebug() << qry.lastError();
        qFatal( "Failed to insert" );
    }

    QSqlTableModel *model = new QSqlTableModel();

    model->setTable( "names" );
    model->setFilter( "lastname = 'Doe'" );
    model->select();

    // if removeColumn is enabled, the table won't be editable, this
    // is a bug under this old version
    //model->removeColumn( 0 );

    QTableView *view = new QTableView();
    //view->setColumnHidden( 0, true );
    view->setModel( model );
    view->show();

    /*
    QSqlQueryModel *model = new QSqlQueryModel();
    model->setQuery( "SELECT firstname, lastname FROM names" );

    QTableView *view = new QTableView();
    view->setModel( model );
    view->show();
    */

    return app.exec();
}

//Compare button
void MainWindow::on_pushButton_clicked()
{

    //Create a Table Model
    QString q_filter = QString("");
    QList<QString> rows;
    QSqlTableModel *model = new QSqlTableModel;
    model->setTable("important");
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);

    for(int i = 0; i < ui->listWidget_2->count(); i++)
    {
        //Use a regex to extract the ID from the row
        QString row_id = ui->listWidget_2->item(i)->text();
        QRegExp rx("(^\\d+)");
        rx.indexIn(row_id);
        row_id = rx.cap(1);

        //Build the filter for the Table Model
        if(i > 0)
            q_filter = q_filter + " or ";
        q_filter = QString(q_filter + "parentTableFK = " + row_id);

    }

    //Run the query, populate the table
    model->setFilter(q_filter);
    model -> select();

    this->ui->tableView->setModel(model);
    this->ui->tableView->show();
}

void TableWindow::Prepare(QString filter)
{
    Database database = Database::getInstance();
    QSqlDatabase db = database.getDatabase();

    if (db.open()) {
        Users *user = new Users();
        if (user->userCanEdit(login)) {
            QSqlTableModel *model = new QSqlTableModel();
            model->setTable("money_management");
            model->setFilter(filter);
            model->select();
            model->setEditStrategy(QSqlTableModel::OnFieldChange);
            ui->tableView->setModel(model);
        }
        else {
            QSqlQueryModel *model = new QSqlQueryModel();
            QString query = "select * from money_management";

            if (!filter.isEmpty()) {
                query +=  " where " + filter;
            }

            model->setQuery(query, db);
            ui->tableView->setModel(model);
        }
    }
    else {
        qDebug() << "Open db error.";
    }
}

void MaterialSaverDB::saveTemplateChapter( StockMaterial* tmpl )
{
    if( ! tmpl ) {
        kDebug() << "Parameter error, zero material!";
        return;
    }
    dbID id = tmpl->getID();
    dbID chapId = tmpl->chapterId();

    QSqlTableModel model;
    model.setTable("stockMaterial");
    QString templID = id.toString();
    model.setFilter( "matID=" + templID );
    model.select();

    QSqlRecord buffer = model.record();

    if( model.rowCount() > 0)
    {
        kDebug() << "Updating material chapter " << templID << endl;
        buffer = model.record(0);
        buffer.setValue( "chapterID", chapId.toString() );
        model.setRecord(0, buffer);
        model.submitAll();
    } else {
        kDebug() << "Could not update material chapter, not found with id " << templID;
    }
}

bool DatabaseHandler::writeCensus(census * obj) {
    qDebug() << "Writing object data to database.";
    QSqlTableModel table;
    table.setTable("census");
    table.setFilter("rcns_id=" + QString::number(obj->id) + " AND usr='" + obj->usr + "'");
    table.select();
    // get record structure from db
    QSqlRecord record(table.record());
    // initialize record with census-structure values
    setRecordTable(&record, obj);

    // insert or update records in db
    if (table.rowCount() == 0) { //INSERT
        qDebug() << "Insert!";
        // remove first entry of record
        // auto increment of id is handled by postgres
        record.remove(0);
        bool done = table.insertRecord(-1,record);
        qDebug() << table.lastError();
        return done;
    } else { //UPDATE
        qDebug() << "Update!";
        record.setValue("fcns_id",table.record(0).value(0).toInt());
        bool check = true;
        check = check && table.setRecord(0, record);
        check = check && table.submitAll();
        qDebug() << table.lastError();
        return check;
    }
    return true;
}

void DlgLogin::on_buttonBox_accepted()
{
    try {

            QByteArray byteArray = ui->txtPassword->text().toUtf8();
            const char* cString = byteArray.constData();
            QString pwd=QString(QCryptographicHash::hash(cString, QCryptographicHash::Md5).toHex());
            QSqlTableModel personal;
            personal.setTable("personal");
            QString filter=QString("login='%1' and pwd='%2'").arg(ui->txtUsuario->text().trimmed()).arg(pwd);
            personal.setFilter(filter);
            personal.select();
            if(personal.rowCount()>0){
                MainWindow* w=(MainWindow *) this->parent();
                w->statusBar()->showMessage(tr("Ready"));
                QSqlRecord record=personal.record(0);
                QString userName= QString("%1 %2").arg(record.value("nombre").toString()).arg(record.value("paterno").toString());
                w->setUserName(userName.toUpper());
                qApp->setProperty("user",record.value("id"));
                qApp->setProperty("profile",record.value("perfil_id"));
//                qDebug()<< qApp->property("profile");
            }else{
                ui->lblStateLogin->setText("Usuario o Contraseña errada");
                this->setVisible(true);
            }
    } catch (...) {
        QMessageBox::information(this, "info", "We are in throwExcept()",
                                 QMessageBox::Ok);
    }

}

void MemberDlg::deleteRecords()
{
    QItemSelectionModel *select_mode=view->selectionModel();

    QModelIndexList list = select_mode->selectedRows();
    qDebug()<<"list:"<<list.size();
    if(0 ==  list.count())
    {
        QMessageBox::information(NULL,tr("删除记录"),tr("对不起，请选择至少一条要删除的记录！"),
                                         QMessageBox::Yes);
        return;
    }
    else
    {
        int ret=QMessageBox::information(NULL,tr("删除记录"),tr("您确定要删除该记录？"),
                                         QMessageBox::Yes,QMessageBox::No);
        if(ret==QMessageBox::No)
            return;

       QSqlTableModel tableModel;
       tableModel.setTable("Account");
       for(int i=0; i<list.count();i++)
       {
           int MainID = model->data(model->index(list.at(i).row(),0)).toInt();
           tableModel.setFilter(QString("FamilyMember = %1").arg(MainID));
           tableModel.select();
           tableModel.removeRows(0,tableModel.rowCount()); //先删除Account与该成员关联的记录
       }

       model->removeRows(list.at(0).row(),list.count());
       model->submitAll();
       model->select();
    }
    emit updateFamilyMember();//通知其他窗体更新
}

void FormGestioneFerie::on_cbMatricola_currentIndexChanged(const QString &arg1)
{

    ui->cbCognome->setCurrentIndex(ui->cbMatricola->currentIndex());
    QSqlQuery query1(QString("SELECT nome FROM anagrafica where Matricola=\"%1\"").arg(ui->cbMatricola->currentText()));
    query1.next();
    ui->lNome->setText( query1.value(0).toString() );

    QSqlTableModel *model = new QSqlTableModel(this, this->db);
    model->setTable("assenze");
    model->setEditStrategy(QSqlTableModel::OnFieldChange);
    model->select();
    model->setFilter(QString("Matricola=\"%1\"").arg(ui->cbMatricola->currentText()));

    model->sort(2,Qt::AscendingOrder);
      model->setHeaderData(1, Qt::Horizontal, tr("MATRICOLA"));
      model->setHeaderData(2, Qt::Horizontal, tr("DATA"));
      model->setHeaderData(3, Qt::Horizontal, tr("CAUSA"));
      ui->tvPermessi->setModel(model);
      ui->tvPermessi->hideColumn(0);
      ui->tvPermessi->resizeColumnsToContents();
      ui->tvPermessi->resizeRowsToContents();
      ui->tvPermessi->selectRow(0);

}

void MainWindow::setModel(QTableView *tableViewName, QString modelName, QString tableName, QStringList *tableFields, QString filter, int orderNum)
{
    QSqlTableModel *model;
    if (modelName == "modelEdit") {
        modelEdit = new QSqlTableModel;
        model = modelEdit;
    } else if (modelName == "modelChoose") {
        modelChoose = new QSqlTableModel;
        model = modelChoose;
    } else if (modelName == "modelQ") {
        modelQ = new QSqlTableModel;
        model = modelQ;
    } else if (modelName == "modelQE") {
        modelQE = new QSqlTableModel;
        model = modelQE;
    } else if (modelName == "modelNote") {
        modelNote = new QSqlTableModel;
        model = modelNote;
    } else if (modelName== "modelNotes") {
        modelNotes = new QSqlTableModel;
        model = modelNotes;
    } else {
        return;
    }

    model= new QSqlTableModel(this);
    model->setTable(tableName);
    model->setEditStrategy(QSqlTableModel::OnFieldChange);

    model->setSort(orderNum, Qt::DescendingOrder);

    int tableFieldsNum = tableFields->length();
    for (int i = 0; i < tableFieldsNum; i++) {
        model->setHeaderData(i, Qt::Horizontal, tableFields->at(i));
    }

    tableViewName->setModel(model);
    tableViewName->alternatingRowColors();
    tableViewName->horizontalHeader()->setStretchLastSection(true);

    model->setFilter(filter);
    model->select();
    tableViewName->reset();
    qDebug() << modelName << model << model->rowCount();

    if (modelName == "modelEdit") {
        modelEdit = model;
    } else if (modelName == "modelChoose") {
        modelChoose = model;
    } else if (modelName == "modelQ") {
        modelQ = model;
    } else if (modelName == "modelQE") {
        modelQE = model;
    } else if (modelName == "modelNote") {
        modelNote = model;
    } else if (modelName == "modelNotes") {
        modelNotes = model;
    } else return;
}

void CDeleteAclTask::sendCMD(int groupId)
{
	QSqlTableModel model;
	QString filter1;
	SACL* psacl;
	char* pSendBuff;
	int len,len1;
	int ruleCount;

	model.setTable("aclrules");
	filter1 = QString("groupid=%1").arg(groupId);
	model.setFilter(filter1);
	model.select();

	ruleCount=model.rowCount();

	if (ruleCount==0)
	{
		//QMessageBox::information(this,"information", G2U(""));
		return;
	}

	psacl=new SACL[ruleCount];

	for (int i=0;i<model.rowCount();++i)
	{
		QSqlRecord record=model.record(i);
		psacl[i].cmdType=ACL_DELETE;             
		psacl[i].id=record.value("id").toInt();
	}

	len1=sizeof(SACL);
	len=ruleCount*len1;
	pSendBuff=new char[len];

	for (int i=0;i!=ruleCount;++i)
	{
		memcpy_s(pSendBuff+len1*i, len, (char*)&(psacl[i]), len1 );
	}

	boost::asio::io_service ios;
	CSocketClientSyn socketClientSyn(ios); 
	socketClientSyn.startConnection();
	socketClientSyn.sendData(pSendBuff,len);

	char* pRecvBuff=new char[len];
	socketClientSyn.recvData(pRecvBuff,len);
	socketClientSyn.closeConnection();

	for (int i=0;i!=ruleCount;++i)
	{
		memcpy_s(&psacl[i],len1,pRecvBuff+len1*i,len1);
		updateRules(psacl[i].id,psacl[i].cmdType);
	}
	delete [] psacl;
	delete [] pRecvBuff;
	delete [] pSendBuff;
}

bool
ToolsFunc::setExerciseTable( const QString &user, QString &tableName )
{
    QSqlTableModel table;
    table.setTable( "instructor" );
    table.setFilter( QObject::tr( " user = '%0'" ).arg( user ) );
    if( !table.select() || !table.rowCount() )
        return false;
    tableName = table.record(0).value( Enum::InstructorSection::Instructor_exercise_tables ).toString();
    return true;
}

void simplePerson::setData(QString idx, QString tableName)
{
    QSqlTableModel model;
    model.setTable(tableName);
    model.setFilter(QString("id = %1").arg(idx));
    model.select();

    id = model.data(model.index(0,0)).toString();
    surname = model.data(model.index(0,1)).toString();
    name = model.data(model.index(0,2)).toString();
    patronimic = model.data(model.index(0,3)).toString();;
}

void FormGestioneFerie::on_pbAggiungiPermesso_clicked()
{
    for(QDate a=ui->dePermessiDal->date(); a.operator <=( ui->dePermessiAl->date());a= a.addDays(1) )
   {

    qDebug() << "Permesso per "+ui->cbMatricola->currentText()+" per il giorno "+a.toString("dd/MM/yyyy");


    QSqlQuery query;
    query.prepare("INSERT INTO assenze (ID, matricola, causa, data)" "VALUES (?, ?, ?, ?)");
   // query.addBindValue( ui->cbMatricola->currentText()+"_"+ui->dePermessiDal->date().toString("dd/MM/yyyy"));
    query.addBindValue( ui->cbMatricola->currentText()+"_"+a.toString("dd/MM/yyyy"));
    query.addBindValue( ui->cbMatricola->currentText());
    query.addBindValue( ui->cbTipoPermesso->currentText());
   // query.addBindValue( ui->dePermessiDal->date().toString("dd/MM/yyyy"));
    query.addBindValue( a.toString("dd/MM/yyyy"));
    if(query.exec())
    {
        QMessageBox msgBox;
        msgBox.setText("Inserzione nel database.");
        msgBox.setInformativeText(QString("Permesso per il giorno %1 inserito correttamente.").arg(a.toString("dd/MM/yyyy")));
        msgBox.setStandardButtons(QMessageBox::Ok );
        msgBox.setIcon(QMessageBox::Information);
        msgBox.exec();
    }
    else
    {
        QMessageBox msgBox;
        msgBox.setText("Inserzione nel database.");
        msgBox.setInformativeText(QString("Permesso per il giorno %1 NON inserito nel database. Probabilmente è gia stato inserito un permesso per lo stesso giorno.").arg(a.toString("dd/MM/yyyy")));
        msgBox.setStandardButtons(QMessageBox::Ok );
        msgBox.setIcon(QMessageBox::Critical);
        msgBox.exec();
    }

    QSqlTableModel *model = new QSqlTableModel(this, this->db);
    model->setTable("assenze");
    model->setEditStrategy(QSqlTableModel::OnFieldChange);
    model->select();
    model->setFilter(QString("Matricola=\"%1\"").arg(ui->cbMatricola->currentText()));

    model->sort(2,Qt::AscendingOrder);
      model->setHeaderData(1, Qt::Horizontal, tr("MATRICOLA"));
      model->setHeaderData(2, Qt::Horizontal, tr("DATA"));
      model->setHeaderData(3, Qt::Horizontal, tr("CAUSA"));
      ui->tvPermessi->setModel(model);
      ui->tvPermessi->hideColumn(0);
      ui->tvPermessi->resizeColumnsToContents();
      ui->tvPermessi->resizeRowsToContents();
      ui->tvPermessi->selectRow(0);

  }
}