1 /***************************************************************************
2 templatesaverbase -
3 -------------------
4 begin : 2005-20-01
5 copyright : (C) 2005 by Klaas Freitag
6 email : freitag@kde.org
7 ***************************************************************************/
8
9 /***************************************************************************
10 * *
11 * This program is free software; you can redistribute it and/or modify *
12 * it under the terms of the GNU General Public License as published by *
13 * the Free Software Foundation; either version 2 of the License, or *
14 * (at your option) any later version. *
15 * *
16 ***************************************************************************/
17
18 // include files for Qt
19 #include <QSqlRecord>
20 #include <QSqlTableModel>
21 #include <QSqlQuery>
22 #include <QSqlError>
23
24 // include files for KDE
25 #include <QDebug>
26
27 #include "documentsaverdb.h"
28 #include "docposition.h"
29 #include "kraftdoc.h"
30 #include "kraftdb.h"
31 #include "unitmanager.h"
32 #include "dbids.h"
33 #include "kraftsettings.h"
34 #include "doctype.h"
35 #include "defaultprovider.h"
36
37 /* Table document:
38 * +----------------+--------------+------+-----+-------------------+----------------+
39 * | Field | Type | Null | Key | Default | Extra |
40 * +----------------+--------------+------+-----+-------------------+----------------+
41 * | docID | int(11) | NO | PRI | NULL | auto_increment |
42 * | ident | varchar(32) | YES | MUL | NULL | |
43 * | docType | varchar(255) | YES | | NULL | |
44 * | docDescription | text | YES | | NULL | |
45 * | clientID | varchar(32) | YES | MUL | NULL | |
46 * | clientAddress | text | YES | | NULL | |
47 * | salut | varchar(255) | YES | | NULL | |
48 * | goodbye | varchar(128) | YES | | NULL | |
49 * | lastModified | timestamp | NO | | CURRENT_TIMESTAMP | |
50 * | date | date | YES | | NULL | |
51 * | pretext | text | YES | | NULL | |
52 * | posttext | text | YES | | NULL | |
53 * | country | varchar(32) | YES | | NULL | |
54 * | language | varchar(32) | YES | | NULL | |
55 * | projectLabel | varchar(255) | YES | | NULL | |
56 * | predecessor | varchar(32) | YES | | NULL | |
57 * +----------------+--------------+------+-----+-------------------+----------------+
58 * 14 rows in set (0.00 sec)
59 *
60 */
61
62 namespace {
checkAndSet(bool & changes,QSqlRecord & record,const QString & name,const QVariant & setValue)63 void checkAndSet(bool& changes, QSqlRecord& record, const QString& name, const QVariant& setValue)
64 {
65 Q_ASSERT(record.contains(name)); // the record must have the column
66
67 if( record.value(name) != setValue) {
68 record.setValue(name, setValue);
69 changes = true;
70 }
71 }
72
73
fillDocumentBuffer(QSqlRecord & buf,KraftDoc * doc)74 bool fillDocumentBuffer(QSqlRecord &buf, KraftDoc *doc)
75 {
76 bool changes {false};
77 if( doc ) {
78 checkAndSet(changes, buf, "ident", doc->ident());
79 checkAndSet(changes, buf, "docType", doc->docType());
80 checkAndSet(changes, buf, "docDescription", KraftDB::self()->mysqlEuroEncode(doc->whiteboard()));
81 checkAndSet(changes, buf, "clientID", doc->addressUid());
82 checkAndSet(changes, buf, "clientAddress", doc->address());
83 checkAndSet(changes, buf, "salut", doc->salut());
84 checkAndSet(changes, buf, "goodbye", doc->goodbye());
85 checkAndSet(changes, buf, "date", doc->date());
86 checkAndSet(changes, buf, "pretext", KraftDB::self()->mysqlEuroEncode( doc->preText()));
87 checkAndSet(changes, buf, "posttext", KraftDB::self()->mysqlEuroEncode( doc->postText()));
88 checkAndSet(changes, buf, "country", DefaultProvider::self()->locale()->bcp47Name());
89 checkAndSet(changes, buf, "language", "");
90 checkAndSet(changes, buf, "projectLabel", doc->projectLabel());
91 checkAndSet(changes, buf, "predecessor", doc->predecessor());
92
93 }
94 return changes;
95 }
96 }
97
DocumentSaverDB()98 DocumentSaverDB::DocumentSaverDB( ) : DocumentSaverBase(),
99 PosTypePosition( QString::fromLatin1( "Position" ) ),
100 PosTypeExtraDiscount( QString::fromLatin1( "ExtraDiscount" ) ),
101 PosTypeHeader( QString::fromLatin1( "Header" ) )
102 {
103
104 }
105
saveDocument(KraftDoc * doc)106 bool DocumentSaverDB::saveDocument(KraftDoc *doc )
107 {
108 bool result = false;
109 if( ! doc ) return result;
110
111 QSqlTableModel model;
112 model.setTable("document");
113
114 QSqlRecord record;
115
116 // qDebug () << "############### Document Save ################" << endl;
117
118 if( doc->isNew() ) {
119 record = model.record();
120 } else {
121 model.setFilter("docID=" + doc->docID().toString());
122 model.select();
123 if ( model.rowCount() > 0 ) {
124 record = model.record(0);
125 } else {
126 qCritical() << "Could not select document record" << endl;
127 return result;
128 }
129 // The document was already saved.
130 }
131
132 if( !doc->isNew() && doc->docTypeChanged() ) {
133 // an existing doc has a new document type. Fix the doc number cycle and pick a new ident
134 DocType dt( doc->docType() );
135 QString ident = dt.generateDocumentIdent( doc->date(), doc->docType(),
136 doc->addressUid() );
137 doc->setIdent( ident );
138 }
139
140 bool hasChanges = fillDocumentBuffer( record, doc );
141
142 if( doc->isNew() ) {
143 // qDebug () << "Doc is new, inserting" << endl;
144 if( !model.insertRecord(-1, record)) {
145 QSqlError err = model.lastError();
146 // qDebug () << "################# SQL Error: " << err.text();
147 }
148 model.submitAll();
149
150 dbID id = KraftDB::self()->getLastInsertID();
151 doc->setDocID( id );
152
153 // get the uniq id and write it into the db
154 DocType dt( doc->docType() );
155 QString ident = dt.generateDocumentIdent( doc->date(), doc->docType(), doc->addressUid() );
156 doc->setIdent( ident );
157 model.setFilter("docID=" + id.toString());
158 model.select();
159 if ( model.rowCount() > 0 ) {
160 model.setData(model.index(0, 1), ident);
161 model.submitAll();
162 }
163
164 } else {
165 // qDebug () << "Doc is not new, updating #" << doc->docID().intID() << endl;
166 checkAndSet(hasChanges, record, "docID", doc->docID().toString());
167 if (!hasChanges) {
168 // if there haven't been changes in the document record, we update the changes
169 // timestamp manually, otherwise it is not updated at all.
170 const QString dt = QDateTime::currentDateTime().toString("yyyy-MM-ddThh:mm:ss");
171 checkAndSet(hasChanges, record, "lastModified", QVariant(dt));
172 }
173 }
174 model.setRecord(0, record);
175 model.submitAll();
176
177 saveDocumentPositions( doc );
178
179 // qDebug () << "Saved document no " << doc->docID().toString() << endl;
180
181 return result;
182 }
183
saveDocumentPositions(KraftDoc * doc)184 void DocumentSaverDB::saveDocumentPositions( KraftDoc *doc )
185 {
186 DocPositionList posList = doc->positions();
187
188 // invert all pos numbers to avoid a unique violation
189 // FIXME: We need non-numeric ids
190 QSqlQuery upq;
191 QString queryStr = "UPDATE docposition SET ordNumber = -1 * ordNumber WHERE docID=";
192 queryStr += doc->docID().toString();
193 queryStr += " AND ordNumber > 0";
194 upq.prepare( queryStr );
195 upq.exec();
196
197 int ordNumber = 1;
198
199 QSqlTableModel model;
200 model.setTable("docposition");
201 model.setEditStrategy(QSqlTableModel::OnManualSubmit);
202
203 QVector<int> deleteIds;
204
205 DocPositionListIterator it( posList );
206 while( it.hasNext() ) {
207 DocPositionBase *dpb = it.next();
208
209 DocPosition *dp = static_cast<DocPosition*>(dpb);
210 QSqlRecord record ;
211 bool doInsert = true;
212
213 int posDbID = dp->dbId().toInt();
214 if( posDbID > -1 ) {
215 const QString selStr = QString("docID=%1 AND positionID=%2").arg( doc->docID().toInt() ).arg( posDbID );
216 // qDebug() << "Selecting with " << selStr << endl;
217 model.setFilter( selStr );
218 model.select();
219 if ( model.rowCount() > 0 ) {
220 if( ! dp->toDelete() )
221 record = model.record(0);
222 doInsert = false;
223 } else {
224 qCritical() << "ERR: Could not select document position record" << endl;
225 return;
226 }
227 } else {
228 // The record is new
229 record = model.record();
230 }
231
232 if( dp->toDelete() ) {
233 // qDebug () << "This one is to delete, do it!" << endl;
234
235 if( doInsert ) {
236 qWarning() << "Attempt to delete a toInsert-Item, obscure" << endl;
237 }
238 // delete all existing attributes
239 dp->attributes().dbDeleteAll( dp->dbId() );
240
241 model.removeRow(0);
242 model.submitAll();
243
244 continue;
245 }
246
247 if( record.count() > 0 ) {
248 // qDebug() << "Updating position " << dp->position() << " is " << dp->text() << endl;
249 QString typeStr = PosTypePosition;
250 double price = dp->unitPrice().toDouble();
251
252 if ( dp->type() == DocPositionBase::ExtraDiscount ) {
253 typeStr = PosTypeExtraDiscount;
254 }
255
256 record.setValue( "docID", QVariant(doc->docID().toInt()));
257 record.setValue( "ordNumber", QVariant(ordNumber));
258 record.setValue( "text", QVariant(dp->text()));
259 record.setValue( "postype", QVariant(typeStr));
260 record.setValue( "amount", QVariant(dp->amount()));
261 int unitId = dp->unit().id();
262 record.setValue( "unit", QVariant(unitId));
263 record.setValue( "price", QVariant(price));
264 record.setValue( "taxType", QVariant(dp->taxType()));
265
266 ordNumber++; // FIXME
267
268 if( doInsert ) {
269 // qDebug () << "Inserting!" << endl;
270 model.insertRecord(-1, record);
271 model.submitAll();
272 dp->setDbId( KraftDB::self()->getLastInsertID().toInt() );
273 } else {
274 // qDebug () << "Updating!" << endl;
275 model.setRecord(0, record);
276 model.submitAll();
277 }
278 } else {
279 // qDebug () << "ERR: No record object found!" << endl;
280 }
281
282 dp->attributes().save( dp->dbId() );
283
284 QSqlError err = model.lastError();
285 if( err.type() != QSqlError::NoError ) {
286 // qDebug () << "SQL-ERR: " << err.text() << " in " << model.tableName() << endl;
287 }
288
289 }
290 model.submitAll();
291
292 /* remove the docpositions that were marked to be deleted */
293 if( deleteIds.count() ) {
294 QSqlQuery delQuery;
295 delQuery.prepare( "DELETE FROM docposition WHERE positionID=:id" );
296 foreach( int id, deleteIds ) {
297 // kDebug() << "Deleting attribute id " << id;
298 delQuery.bindValue( ":id", id );
299 delQuery.exec();
300 }
301 }
302
303
304 }
305
load(const QString & id,KraftDoc * doc)306 void DocumentSaverDB::load( const QString& id, KraftDoc *doc )
307 {
308 if( !id.isEmpty() ) {
309 QSqlQuery q;
310 q.prepare("SELECT ident, docType, clientID, clientAddress, salut, goodbye, date, lastModified, language, country, "
311 "pretext, posttext, docDescription, projectlabel, predecessor FROM document WHERE docID=:docID");
312 q.bindValue(":docID", id);
313 q.exec();
314 // qDebug () << "Loading document id " << id << endl;
315
316 if( q.next())
317 {
318 // qDebug () << "loading document with id " << id << endl;
319 dbID dbid;
320 dbid = id;
321 doc->setDocID(dbid);
322
323 doc->setIdent( q.value( 0 ).toString() );
324 doc->setDocType( q.value( 1 ).toString() );
325 doc->setAddressUid( q.value( 2 ).toString() );
326 doc->setAddress( q.value( 3 ).toString() );
327 QString salut = q.value(4).toString();
328 doc->setSalut( salut );
329 doc->setGoodbye( q.value( 5 ).toString() );
330 doc->setDate ( q.value( 6 ).toDate() );
331 QDateTime dt = q.value(7).toDateTime();
332
333 // Sqlite stores the timestamp as UTC in the database. Mysql does not.
334 if (KraftDB::self()->isSqlite()) {
335 dt.setTimeSpec(Qt::UTC);
336 doc->setLastModified(dt.toLocalTime());
337 } else {
338 doc->setLastModified(dt);
339 }
340
341 // Removed, as with Kraft 0.80 there is no locale management on doc level any more
342 // doc->setCountryLanguage( q.value( 8 ).toString(),
343 // q.value( 9 ).toString());
344
345 doc->setPreText( KraftDB::self()->mysqlEuroDecode( q.value( 10 ).toString() ) );
346 doc->setPostText( KraftDB::self()->mysqlEuroDecode( q.value( 11 ).toString() ) );
347 doc->setWhiteboard( KraftDB::self()->mysqlEuroDecode( q.value( 12 ).toString() ) );
348 doc->setProjectLabel( q.value(13).toString() );
349 doc->setPredecessor( q.value(14).toString() );
350 }
351 }
352 // load the dbID of the predecessor document from the database.
353 const QString pIdent = doc->predecessor();
354 if( ! pIdent.isEmpty() ) {
355 QSqlQuery q1;
356 q1.prepare("SELECT docID FROM document WHERE ident=:docID");
357 q1.bindValue(":docID", pIdent);
358 q1.exec();
359 if( q1.next() ) {
360 const QString pDbId = q1.value(0).toString();
361 doc->setPredecessorDbId(pDbId);
362 }
363 }
364
365 // finally load the item data.
366 loadPositions( id, doc );
367 }
368 /* docposition:
369 +------------+--------------+------+-----+---------+----------------+
370 | Field | Type | Null | Key | Default | Extra |
371 +------------+--------------+------+-----+---------+----------------+
372 | positionID | int(11) | | PRI | NULL | auto_increment |
373 | docID | int(11) | | MUL | 0 | |
374 | ordNumber | int(11) | | | 0 | |
375 | text | mediumtext | YES | | NULL | |
376 | amount | decimal(6,2) | YES | | NULL | |
377 | unit | varchar(64) | YES | | NULL | |
378 | price | decimal(6,2) | YES | | NULL | |
379 +------------+--------------+------+-----+---------+----------------+
380 */
loadPositions(const QString & id,KraftDoc * doc)381 void DocumentSaverDB::loadPositions( const QString& id, KraftDoc *doc )
382 {
383 QSqlQuery q;
384 q.prepare("SELECT positionID, postype, text, amount, unit, price, taxType FROM docposition WHERE docID=:docID ORDER BY ordNumber");
385 q.bindValue(":docID", id);
386 q.exec();
387
388 // qDebug () << "* loading document positions for document id " << id << endl;
389 while( q.next() ) {
390 // qDebug () << " loading position id " << q.value( 0 ).toInt() << endl;
391
392 DocPositionBase::PositionType type = DocPositionBase::Position;
393 QString typeStr = q.value( 1 ).toString();
394 if ( typeStr == PosTypeExtraDiscount ) {
395 type = DocPositionBase::ExtraDiscount;
396 } else if ( typeStr == PosTypePosition ) {
397 // nice, default position type.
398 type = DocPositionBase::Position;
399 } else {
400 // qDebug () << "ERROR: Strange type string loaded from db: " << typeStr << endl;
401 }
402
403 DocPosition *dp = doc->createPosition( type );
404 dp->setDbId( q.value(0).toInt() );
405 dp->setText( q.value(2).toString() );
406
407 // Note: empty fields are treated as Positions which is intended because
408 // the type col was added later and thus might be empty for older entries
409
410 dp->setAmount( q.value(3).toDouble() );
411
412 dp->setUnit( UnitManager::self()->getUnit( q.value(4).toInt() ) );
413 dp->setUnitPrice( q.value(5).toDouble() );
414 dp->setTaxType( q.value(6).toInt() );
415
416 dp->loadAttributes();
417 }
418 }
419
~DocumentSaverDB()420 DocumentSaverDB::~DocumentSaverDB( )
421 {
422
423 }
424
425 /* END */
426
427