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; }
int ArchiveMan::archivePos( int archDocId, KraftDoc *doc ) { /* mysql> describe archdocpos; +-----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+----------------+ | archPosID | int(11) | NO | PRI | NULL | auto_increment | | archDocID | int(11) | NO | MUL | | | | ordNumber | int(11) | NO | | | | | text | text | YES | | NULL | | | amount | decimal(6,2) | YES | | NULL | | | unit | varchar(64) | YES | | NULL | | | price | decimal(6,2) | YES | | NULL | | | vat | decimal(3,1) | YES | | 0.0 | | +-----------+--------------+------+-----+---------+----------------+ */ if( ! doc ) return -1; QSqlTableModel model; model.setTable("archdocpos"); QSqlRecord record = model.record(); int cnt = 0; DocPositionList posList = doc->positions(); DocPositionListIterator it( posList ); kDebug() << "Archiving pos for " << archDocId << endl; while ( it.hasNext() ) { DocPosition *dp = static_cast<DocPosition*>( it.next() ); record.setValue( "archDocID", archDocId ); record.setValue( "ordNumber", 1+cnt /* dp->position() */ ); record.setValue( "kind", dp->attribute( DocPosition::Kind ) ); record.setValue( "text", dp->text() ); // expandItemText( dp ) ); record.setValue( "amount", dp->amount() ); record.setValue( "unit", dp->unit().einheit( dp->amount() ) ); record.setValue( "price", dp->unitPrice().toDouble() ); record.setValue( "overallPrice", dp->overallPrice().toDouble() ); record.setValue( "taxType", dp->taxTypeNumeric() ); if(!model.insertRecord(-1, record)) { kDebug() << model.lastError(); } dbID id = KraftDB::self()->getLastInsertID(); // kDebug() << "Inserted for id " << id.toString() << endl; cnt++; // save the attributes of the positions in the attributes // table but with a new host type which reflects the arch state AttributeMap attribs = dp->attributes(); attribs.setHost( "ArchPosition" ); attribs.save( id ); } return cnt; }
dbID ArchiveMan::archiveDocumentDb( KraftDoc *doc ) { /* mysql> describe archdoc; +---------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+-------------------+----------------+ | archDocID | int(11) | NO | PRI | NULL | auto_increment | | ident | varchar(32) | YES | MUL | NULL | | | docType | varchar(255) | YES | | NULL | | | clientAddress | text | YES | | NULL | | | clientUid | varchar(32) | YES | | NULL | | | salut | varchar(255) | YES | | NULL | | | goodbye | varchar(128) | YES | | NULL | | | printDate | timestamp | NO | | CURRENT_TIMESTAMP | | | date | date | YES | | NULL | | | pretext | text | YES | | NULL | | | posttext | text | YES | | NULL | | | country | varchar(32) | YES | | NULL | | | language | varchar(32) | YES | | NULL | | | projectLabel | varchar(255) | YES | | NULL | | | state | int(11) | YES | | NULL | | +---------------+--------------+------+-----+-------------------+----------------+ */ if( ! doc ) return dbID(); QSqlTableModel model; model.setTable("archdoc"); QSqlRecord record = model.record(); if( doc->isNew() ) { kDebug() << "Strange: Document in archiving is new!" << endl; } record.setValue( "ident", doc->ident() ); record.setValue( "docType", doc->docType() ); record.setValue( "docDescription", KraftDB::self()->mysqlEuroEncode( doc->whiteboard() ) ); record.setValue( "clientAddress", doc->address() ); record.setValue( "clientUid", doc->addressUid() ); record.setValue( "salut", doc->salut() ); record.setValue( "goodbye", doc->goodbye() ); record.setValue( "printDate", KraftDB::self()->currentTimeStamp() ); record.setValue( "date", doc->date() ); record.setValue( "pretext", KraftDB::self()->mysqlEuroEncode(doc->preText() ) ); record.setValue( "posttext", KraftDB::self()->mysqlEuroEncode(doc->postText() ) ); record.setValue( "projectLabel", KraftDB::self()->mysqlEuroEncode(doc->projectLabel() ) ); record.setValue( "country", doc->country() ); record.setValue( "language", doc->language() ); record.setValue( "tax", DocumentMan::self()->tax( doc->date() ) ); record.setValue( "reducedTax", DocumentMan::self()->reducedTax( doc->date() ) ); if(!model.insertRecord(-1, record)) { kDebug() << model.lastError(); } dbID id = KraftDB::self()->getLastInsertID(); archivePos( id.toInt(), doc ); return id; }
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; }
int Dialog::addNewArtist(const QString &name) { QSqlTableModel *artistModel = model->relationModel(2); QSqlRecord record; int id = generateArtistId(); QSqlField f1("id", QVariant::Int); QSqlField f2("artist", QVariant::String); QSqlField f3("albumcount", QVariant::Int); f1.setValue(QVariant(id)); f2.setValue(QVariant(name)); f3.setValue(QVariant(0)); record.append(f1); record.append(f2); record.append(f3); artistModel->insertRecord(-1, record); return id; }
//插入一条记录 void Widget::CStudentDialog::insertRecord() { bool ok; QString name = m_pNameEdit->text(); QString Sex = m_pSexEdit->text(); double age = m_pAgeEdit->text().toDouble( &ok ); if( !ok ){ QMessageBox::warning( this , "错误" , "请重新填写年龄" ); return; } QString number = m_pNumberEdit->text(); QString post = m_pPostEdit->text(); QSqlTableModel table; table.setTable( "person" ); table.select(); QSqlRecord record; record = table.record(); record.setValue( "name" , name ); record.setValue( "gender" , Sex ); record.setValue( "age" , age ); ok = table.insertRecord( -1, record ); if( !ok ){ AddStudentError } table.submitAll(); table.setTable( "person" ); table.setFilter( tr("name = '%0'").arg( name ) ); qDebug() << table.select(); int id = table.record(0).value( Enum::PersonSection::Person_id ).toInt( &ok ); if( !ok ){ table.removeRow( 0 ); table.submitAll(); AddStudentError }
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(); }