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();
}