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