void Database::deactivateUser(QString &userName) { QSqlTableModel model; model.setTable("user"); model.select(); for (int i=0; i<model.rowCount(); ++i) { QSqlRecord record = model.record(i); if (record.value("name").toString()==userName) { record.setValue("isActive", false); model.setRecord(i, record); model.submitAll(); break; } } }
void CDeleteAclTask::updateRules(int ruleId,int cmdType) { QSqlTableModel model; model.setTable("aclrules"); QString filter1=QString("id=%1").arg(ruleId); model.setFilter(filter1); model.select(); if (model.rowCount()==1) { QSqlRecord record=model.record(0); record.setValue("cmdtype",cmdType); model.setRecord(0,record); } model.submitAll(); //QString error=model.lastError().text(); //QMessageBox::information(this, tr("information"), error); }
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='******'"); 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 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; } }
void PatientAccounter::printBill(const QDate& date) { QString Document; addRTFHeader(Document); addDocumentHeader(Document); addPatientHeader(Document); addInfoText(Document); QString billFile = createBillFile(date); //std::cerr << "Target: " << billFile.toStdString() << std::endl; if (!addTreatments(Document)) { //Again nothing to account, simple return return; } addDocumentFooter(Document); finishRTF(Document); //Get The document Footer std::ofstream outStream; outStream.open(billFile.toStdString().c_str()); outStream << Document.toStdString(); //Increment the Bill Identifier QSqlTableModel* miscTable = new QSqlTableModel; miscTable->setTable("misc"); miscTable->select(); QSqlRecord miscRecord = miscTable->record(0); miscRecord.setValue(BillNumber, m_billNumber + 1); miscTable->setRecord(0, miscRecord); miscTable->submit(); delete miscTable; }
bool MaterialSaverDB::saveTemplate( StockMaterial *mat ) { bool res = true; // Transaktion ? QSqlTableModel model; model.setTable("stockMaterial"); QString templID = QString::number( mat->getID() ); model.setFilter( "matID=" + templID ); model.select(); QSqlRecord buffer = model.record(); if( model.rowCount() > 0) { kDebug() << "Updating material " << mat->getID() << endl; // mach update buffer = model.record(0); fillMaterialBuffer( buffer, mat, false ); model.setRecord(0, buffer); model.submitAll(); } else { // insert kDebug() << "Creating new material database entry" << endl; fillMaterialBuffer( buffer, mat, true ); model.insertRecord(-1, buffer); model.submitAll(); /* Jetzt die neue Template-ID selecten */ dbID id = KraftDB::self()->getLastInsertID(); kDebug() << "New Database ID=" << id.toInt() << endl; if( id.isOk() ) { mat->setID( id.toInt() ); templID = id.toString(); } else { kDebug() << "ERROR: Kann AUTOINC nicht ermitteln" << endl; res = false; } } return res; }
//------------------Above Date-------------------------- void Liveinfo::UpdateRoom(int RoomNum,int change) { QSqlTableModel * updateroom = new QSqlTableModel; //修改已入住的房间 updateroom->setTable("Room"); updateroom->select(); for(int i = 0;i < updateroom->rowCount(); ++i) { QSqlRecord Roomrecord = updateroom->record(i); int check_room = Roomrecord.value("Num").toInt(); if( check_room == RoomNum ) { Roomrecord.setValue("Live",change); updateroom->setRecord(i, Roomrecord); } } updateroom->submitAll(); }
void Database::saveXP(QString &userName, int xp) { QSqlTableModel model; model.setTable("user"); model.select(); for (int i=0; i<model.rowCount(); ++i) { QSqlRecord record = model.record(i); if (record.value("name").toString()==userName) { record.setValue("experiencePoints", xp); model.setRecord(i, record); model.submitAll(); break; } } }
void Database::saveLevel(QString &userName, int level) { QSqlTableModel model; model.setTable("user"); model.select(); for (int i=0; i<model.rowCount(); ++i) { QSqlRecord record = model.record(i); if (record.value("name").toString()==userName) { record.setValue("level", level); model.setRecord(i, record); model.submitAll(); break; } } }
void Database::saveUser(QString &userName, User user) { QSqlTableModel model; model.setTable("user"); model.select(); for (int i=0; i<model.rowCount(); ++i) { QSqlRecord record = model.record(i); if (record.value("name").toString()==userName) { record.setValue("experiencePoints", user.experiencePoints); record.setValue("isActive", user.isActive); record.setValue("level", user.level); model.setRecord(i, record); model.submitAll(); break; } } }
void sql_intro_snippets() { { //! [26] QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL"); db.setHostName("bigblue"); db.setDatabaseName("flightdb"); db.setUserName("acarlson"); db.setPassword("1uTbSbAs"); bool ok = db.open(); //! [26] Q_UNUSED(ok); } { //! [27] QSqlDatabase firstDB = QSqlDatabase::addDatabase("QMYSQL", "first"); QSqlDatabase secondDB = QSqlDatabase::addDatabase("QMYSQL", "second"); //! [27] } { //! [28] QSqlDatabase defaultDB = QSqlDatabase::database(); //! [28] //! [29] QSqlDatabase firstDB = QSqlDatabase::database("first"); //! [29] //! [30] QSqlDatabase secondDB = QSqlDatabase::database("second"); //! [30] } { // SELECT1 //! [31] QSqlQuery query; query.exec("SELECT name, salary FROM employee WHERE salary > 50000"); //! [31] //! [32] while (query.next()) { QString name = query.value(0).toString(); int salary = query.value(1).toInt(); qDebug() << name << salary; } //! [32] } { // FEATURE //! [33] QSqlQuery query; int numRows; query.exec("SELECT name, salary FROM employee WHERE salary > 50000"); QSqlDatabase defaultDB = QSqlDatabase::database(); if (defaultDB.driver()->hasFeature(QSqlDriver::QuerySize)) { numRows = query.size(); } else { // this can be very slow query.last(); numRows = query.at() + 1; } //! [33] } { // INSERT1 //! [34] QSqlQuery query; query.exec("INSERT INTO employee (id, name, salary) " "VALUES (1001, 'Thad Beaumont', 65000)"); //! [34] } { // NAMED BINDING //! [35] QSqlQuery query; query.prepare("INSERT INTO employee (id, name, salary) " "VALUES (:id, :name, :salary)"); query.bindValue(":id", 1001); query.bindValue(":name", "Thad Beaumont"); query.bindValue(":salary", 65000); query.exec(); //! [35] } { // POSITIONAL BINDING //! [36] QSqlQuery query; query.prepare("INSERT INTO employee (id, name, salary) " "VALUES (?, ?, ?)"); query.addBindValue(1001); query.addBindValue("Thad Beaumont"); query.addBindValue(65000); query.exec(); //! [36] } { // UPDATE1 //! [37] QSqlQuery query; query.exec("UPDATE employee SET salary = 70000 WHERE id = 1003"); //! [37] } { // DELETE1 //! [38] QSqlQuery query; query.exec("DELETE FROM employee WHERE id = 1007"); //! [38] } { // TRANSACTION //! [39] QSqlDatabase::database().transaction(); QSqlQuery query; query.exec("SELECT id FROM employee WHERE name = 'Torild Halvorsen'"); if (query.next()) { int employeeId = query.value(0).toInt(); query.exec("INSERT INTO project (id, name, ownerid) " "VALUES (201, 'Manhattan Project', " + QString::number(employeeId) + ')'); } QSqlDatabase::database().commit(); //! [39] } { // SQLQUERYMODEL1 //! [40] QSqlQueryModel model; model.setQuery("SELECT * FROM employee"); for (int i = 0; i < model.rowCount(); ++i) { int id = model.record(i).value("id").toInt(); QString name = model.record(i).value("name").toString(); qDebug() << id << name; } //! [40] } { // SQLTABLEMODEL1 //! [41] QSqlTableModel model; model.setTable("employee"); model.setFilter("salary > 50000"); model.setSort(2, Qt::DescendingOrder); model.select(); for (int i = 0; i < model.rowCount(); ++i) { QString name = model.record(i).value("name").toString(); int salary = model.record(i).value("salary").toInt(); qDebug() << name << salary; } //! [41] } { // SQLTABLEMODEL2 QSqlTableModel model; model.setTable("employee"); //! [42] for (int i = 0; i < model.rowCount(); ++i) { QSqlRecord record = model.record(i); double salary = record.value("salary").toInt(); salary *= 1.1; record.setValue("salary", salary); model.setRecord(i, record); } model.submitAll(); //! [42] // SQLTABLEMODEL3 int row = 1; int column = 2; //! [43] model.setData(model.index(row, column), 75000); model.submitAll(); //! [43] // SQLTABLEMODEL4 //! [44] model.insertRows(row, 1); model.setData(model.index(row, 0), 1013); model.setData(model.index(row, 1), "Peter Gordon"); model.setData(model.index(row, 2), 68500); model.submitAll(); //! [44] //! [45] model.removeRows(row, 5); //! [45] //! [46] model.submitAll(); //! [46] } }
bool DocumentSaverDB::saveDocument(KraftDoc *doc ) { bool result = false; if( ! doc ) return result; QSqlTableModel model; model.setTable("document"); QSqlRecord record; kDebug() << "############### Document Save ################" << endl; if( doc->isNew() ) { record = model.record(); } else { model.setFilter("docID=" + doc->docID().toString()); model.select(); if ( model.rowCount() > 0 ) { record = model.record(0); } else { kError() << "Could not select document record" << endl; return result; } // The document was already saved. } if( !doc->isNew() && doc->docTypeChanged() && doc->newIdent() ) { // an existing doc has a new document type. Fix the doc number cycle and pick a new ident DocType dt( doc->docType() ); QString ident = dt.generateDocumentIdent( doc ); doc->setIdent( ident ); } fillDocumentBuffer( record, doc ); if( doc->isNew() ) { kDebug() << "Doc is new, inserting" << endl; if( !model.insertRecord(-1, record)) { QSqlError err = model.lastError(); kDebug() << "################# SQL Error: " << err.text(); } model.submitAll(); dbID id = KraftDB::self()->getLastInsertID(); doc->setDocID( id ); // get the uniq id and write it into the db DocType dt( doc->docType() ); QString ident = dt.generateDocumentIdent( doc ); doc->setIdent( ident ); model.setFilter("docID=" + id.toString()); model.select(); if ( model.rowCount() > 0 ) { model.setData(model.index(0, 1), ident); model.submitAll(); } } else { kDebug() << "Doc is not new, updating #" << doc->docID().intID() << endl; record.setValue( "docID", doc->docID().toString() ); model.setRecord(0, record); model.submitAll(); } saveDocumentPositions( doc ); kDebug() << "Saved document no " << doc->docID().toString() << endl; return result; }
void DocumentSaverDB::saveDocumentPositions( KraftDoc *doc ) { DocPositionList posList = doc->positions(); // invert all pos numbers to avoid a unique violation // FIXME: We need non-numeric ids QSqlQuery upq; QString queryStr = "UPDATE docposition SET ordNumber = -1 * ordNumber WHERE docID="; queryStr += doc->docID().toString(); queryStr += " AND ordNumber > 0"; upq.prepare( queryStr ); upq.exec(); int ordNumber = 1; QSqlTableModel model; model.setTable("docposition"); model.setEditStrategy(QSqlTableModel::OnManualSubmit); QVector<int> deleteIds; DocPositionListIterator it( posList ); while( it.hasNext() ) { DocPositionBase *dpb = it.next(); DocPosition *dp = static_cast<DocPosition*>(dpb); QSqlRecord record ; bool doInsert = true; int posDbID = dp->dbId().toInt(); kDebug() << "Saving Position DB-Id: " << posDbID << endl; if( dp->toDelete() ) { kDebug() << "Delete item " << dp->dbId().toString() << endl; // store the id to delete, rather than killing the model index. // did that before here, which removed wrong items. deleteIds << posDbID; // delete all existing attributes no, which will not disturb the model index dp->attributes().dbDeleteAll( dp->dbId() ); continue; } if( posDbID > -1 ) { const QString selStr = QString("docID=%1 AND positionID=%2").arg( doc->docID().toInt() ).arg( posDbID ); // kDebug() << "Selecting with " << selStr << endl; model.setFilter( selStr ); model.select(); if ( model.rowCount() > 0 ) { if( ! dp->toDelete() ) record = model.record(0); doInsert = false; } else { kError() << "ERR: Could not select document position record" << endl; return; } } else { // The record is new record = model.record(); } if( record.count() > 0 ) { // kDebug() << "Updating position " << dp->position() << " is " << dp->text() << endl; QString typeStr = PosTypePosition; double price = dp->unitPrice().toDouble(); if ( dp->type() == DocPositionBase::ExtraDiscount ) { typeStr = PosTypeExtraDiscount; } record.setValue( "docID", QVariant(doc->docID().toInt())); record.setValue( "ordNumber", QVariant(ordNumber)); record.setValue( "text", QVariant(dp->text())); record.setValue( "postype", QVariant(typeStr)); record.setValue( "amount", QVariant(dp->amount())); int unitId = dp->unit().id(); record.setValue( "unit", QVariant(unitId)); record.setValue( "price", QVariant(price)); record.setValue( "taxType", QVariant(dp->taxType())); ordNumber++; // FIXME if( doInsert ) { kDebug() << "Inserting!" << endl; model.insertRecord(-1, record); model.submitAll(); dp->setDbId( KraftDB::self()->getLastInsertID().toInt() ); } else { kDebug() << "Updating!" << endl; model.setRecord(0, record); model.submitAll(); } } else { kDebug() << "ERR: No record object found!" << endl; } dp->attributes().save( dp->dbId() ); QSqlError err = model.lastError(); if( err.type() != QSqlError::NoError ) { kDebug() << "SQL-ERR: " << err.text() << " in " << model.tableName() << endl; } } model.submitAll(); /* remove the docpositions that were marked to be deleted */ if( deleteIds.count() ) { QSqlQuery delQuery; delQuery.prepare( "DELETE FROM docposition WHERE positionID=:id" ); foreach( int id, deleteIds ) { kDebug() << "Deleting attribute id " << id; delQuery.bindValue( ":id", id ); delQuery.exec(); }