bool db
::backupData(QDate startDate,
QDate endDate
){
//database name
databaseName = current.toString();
//retreive old data
this->connect();
if( this->isConnected){
query.prepare("SELECT * FROM `orders` WHERE (`currentStatus` = '2' OR `currentStatus` = '3') AND deadline BETWEEN :start AND :end");
query.
bindValue(QString(":start"),
QVariant(startDate.
toString("yyyy-MM-dd")));
query.
bindValue(QString(":end"),
QVariant(endDate.
toString("yyyy-MM-dd")));
if(!query.exec()){
QMessageBox::critical(this, tr
("SQL error"), tr
("Query failed, please contact the developer or try again"));
}
if(!queryClients.exec("SELECT * FROM `clients`")){
QMessageBox::critical(this, tr
("SQL error"), tr
("Query failed, please contact the developer or try again"));
}
}
//close the current database connection
this->closeConnection();
//create new database
backup.setDatabaseName(databaseName);
if(!backup.open()){
QMessageBox::critical(this, tr
("Woops"), tr
("Couldn't establish connection with the database, pleas contact the developer or try again"));
return false;
}
this->createTables();
//backup clients table
while(queryClients.next()){
queryInsertClients.prepare("INSERT INTO `clients` "
"(`cid`, "
"`firstName`, "
"`lastName`, "
"`address`, "
"`zipCode`, "
"`city`, "
"`phoneNr`, "
"`email`)"
" VALUES "
"("
":cid, "
":firstName, "
":lastName, "
":address, "
":zipCode, "
":city, "
":phoneNr, "
":email "
")");
queryInsertClients.
bindValue(QString(":cid"),
QVariant(queryClients.
value(0).
toString()));
queryInsertClients.
bindValue(QString(":firstName"),
QVariant(queryClients.
value(1).
toString()));
queryInsertClients.
bindValue(QString(":lastName"),
QVariant(queryClients.
value(2).
toString()));
queryInsertClients.
bindValue(QString(":address"),
QVariant(queryClients.
value(3).
toString()));
queryInsertClients.
bindValue(QString(":zipCode"),
QVariant(queryClients.
value(4).
toString()));
queryInsertClients.
bindValue(QString(":city"),
QVariant(queryClients.
value(5).
toString()));
queryInsertClients.
bindValue(QString(":phoneNr"),
QVariant(queryClients.
value(6).
toString()));
queryInsertClients.
bindValue(QString(":email"),
QVariant(queryClients.
value(7).
toString()));
if( !queryInsertClients.exec()){
QMessageBox::critical(this, tr
("Woops"), tr
("Cannot commit query, backup clients failed"));
return false;
}
}
//backup order table
while(query.next()){
//debug
QMessageBox::critical(this, tr
("Debug"), tr
("Backup order"));
queryInsert.prepare("INSERT INTO `orders`"
"(`oid`,"
"`cid`,"
"`articleDescription`,"
"`articleNr`, "
"`ordered`, "
"`suspectedOn`,"
"`delivered`, "
"`customized`,"
"`deadline`, "
"`totalAmount`,"
"`prePayment`, "
"`previousStatus`,"
"`currentStatus`)"
"VALUES"
"(:oid, "
":cid, "
":articleDesc,"
":articleNr, "
":ordered,"
":suspectedOn,"
":delivered,"
":customized,"
":deadline,"
":totalAmount,"
":prepayment,"
":previousStatus,"
":currentStatus)");
queryInsert.
bindValue(QString(":oid"),
QVariant(query.
value(0).
toString()));
queryInsert.
bindValue(QString(":cid"),
QVariant(query.
value(1).
toString()));
queryInsert.
bindValue(QString(":articleDesc"),
QVariant(query.
value(2).
toString()));
queryInsert.
bindValue(QString(":articleNr"),
QVariant(query.
value(3).
toString()));
queryInsert.
bindValue(QString(":ordered"),
QVariant(query.
value(4).
toString()));
queryInsert.
bindValue(QString(":suspectedOn"),
QVariant(query.
value(5).
toString()));
queryInsert.
bindValue(QString(":delivered"),
QVariant(query.
value(6).
toString()));
queryInsert.
bindValue(QString(":customized"),
QVariant(query.
value(7).
toString()));
queryInsert.
bindValue(QString(":deadline"),
QVariant(query.
value(8).
toString()));
queryInsert.
bindValue(QString(":totalAmount"),
QVariant(query.
value(9).
toString()));
queryInsert.
bindValue(QString(":prepayment"),
QVariant(query.
value(10).
toString()));
queryInsert.
bindValue(QString(":previousStatus"),
QVariant(query.
value(11).
toString()));
queryInsert.
bindValue(QString(":currentStatus"),
QVariant(query.
value(12).
toString()));
//cannot commit query
if(!queryInsert.exec()){
QMessageBox::critical(this, tr
("Woops"), tr
("Cannot commit query, backup orders failed"));
return false;
}
}
//close database
backup.close();
//remove the items from the current database
this->connect();
if( this->isConnected){
//scrollback in the result list
while(query.previous()){
//debug
QMessageBox::critical(this, tr
("Debug"), query.
value(0).
toString());
queryDEL.prepare("DELETE FROM `orders` WHERE oid = :oid");
if(!queryDEL.exec()){
QMessageBox::critical(this, tr
("Woops"), tr
("Cannot commit query, removing items from the current database failed"));
//debug
return false;
}
}
}
this->closeConnection();
return true;
}
bool db::backupData(QDate startDate, QDate endDate){
//database name
QString databaseName;
QDate current = QDate::currentDate();
databaseName = current.toString();
//retreive old data
this->connect();
QSqlQuery query, queryClients;
if( this->isConnected){
query.prepare("SELECT * FROM `orders` WHERE (`currentStatus` = '2' OR `currentStatus` = '3') AND deadline BETWEEN :start AND :end");
query.bindValue(QString(":start"), QVariant(startDate.toString("yyyy-MM-dd")));
query.bindValue(QString(":end"), QVariant(endDate.toString("yyyy-MM-dd")));
if(!query.exec()){
QMessageBox::critical(this, tr("SQL error"), tr("Query failed, please contact the developer or try again"));
}
if(!queryClients.exec("SELECT * FROM `clients`")){
QMessageBox::critical(this, tr("SQL error"), tr("Query failed, please contact the developer or try again"));
}
}
//close the current database connection
this->closeConnection();
//create new database
QSqlDatabase backup = QSqlDatabase::addDatabase("QSQLITE");
backup.setDatabaseName(databaseName);
if(!backup.open()){
QMessageBox::critical(this, tr("Woops"), tr("Couldn't establish connection with the database, pleas contact the developer or try again"));
return false;
}
this->createTables();
//backup clients table
while(queryClients.next()){
QSqlQuery queryInsertClients;
queryInsertClients.prepare("INSERT INTO `clients` "
"(`cid`, "
"`firstName`, "
"`lastName`, "
"`address`, "
"`zipCode`, "
"`city`, "
"`phoneNr`, "
"`email`)"
" VALUES "
"("
":cid, "
":firstName, "
":lastName, "
":address, "
":zipCode, "
":city, "
":phoneNr, "
":email "
")");
queryInsertClients.bindValue(QString(":cid"), QVariant(queryClients.value(0).toString()));
queryInsertClients.bindValue(QString(":firstName"), QVariant(queryClients.value(1).toString()));
queryInsertClients.bindValue(QString(":lastName"), QVariant(queryClients.value(2).toString()));
queryInsertClients.bindValue(QString(":address"), QVariant(queryClients.value(3).toString()));
queryInsertClients.bindValue(QString(":zipCode"), QVariant(queryClients.value(4).toString()));
queryInsertClients.bindValue(QString(":city"), QVariant(queryClients.value(5).toString()));
queryInsertClients.bindValue(QString(":phoneNr"), QVariant(queryClients.value(6).toString()));
queryInsertClients.bindValue(QString(":email"), QVariant(queryClients.value(7).toString()));
if( !queryInsertClients.exec()){
QMessageBox::critical(this, tr("Woops"), tr("Cannot commit query, backup clients failed"));
return false;
}
}
//backup order table
while(query.next()){
//debug
QMessageBox::critical(this, tr("Debug"), tr("Backup order"));
QSqlQuery queryInsert;
queryInsert.prepare("INSERT INTO `orders`"
"(`oid`,"
"`cid`,"
"`articleDescription`,"
"`articleNr`, "
"`ordered`, "
"`suspectedOn`,"
"`delivered`, "
"`customized`,"
"`deadline`, "
"`totalAmount`,"
"`prePayment`, "
"`previousStatus`,"
"`currentStatus`)"
"VALUES"
"(:oid, "
":cid, "
":articleDesc,"
":articleNr, "
":ordered,"
":suspectedOn,"
":delivered,"
":customized,"
":deadline,"
":totalAmount,"
":prepayment,"
":previousStatus,"
":currentStatus)");
queryInsert.bindValue(QString(":oid"), QVariant(query.value(0).toString()));
queryInsert.bindValue(QString(":cid"), QVariant(query.value(1).toString()));
queryInsert.bindValue(QString(":articleDesc"), QVariant(query.value(2).toString()));
queryInsert.bindValue(QString(":articleNr"), QVariant(query.value(3).toString()));
queryInsert.bindValue(QString(":ordered"), QVariant(query.value(4).toString()));
queryInsert.bindValue(QString(":suspectedOn"), QVariant(query.value(5).toString()));
queryInsert.bindValue(QString(":delivered"), QVariant(query.value(6).toString()));
queryInsert.bindValue(QString(":customized"), QVariant(query.value(7).toString()));
queryInsert.bindValue(QString(":deadline"), QVariant(query.value(8).toString()));
queryInsert.bindValue(QString(":totalAmount"), QVariant(query.value(9).toString()));
queryInsert.bindValue(QString(":prepayment"), QVariant(query.value(10).toString()));
queryInsert.bindValue(QString(":previousStatus"), QVariant(query.value(11).toString()));
queryInsert.bindValue(QString(":currentStatus"), QVariant(query.value(12).toString()));
//cannot commit query
if(!queryInsert.exec()){
QMessageBox::critical(this, tr("Woops"), tr("Cannot commit query, backup orders failed"));
return false;
}
}
//close database
backup.close();
//remove the items from the current database
this->connect();
if( this->isConnected){
//scrollback in the result list
while(query.previous()){
QSqlQuery queryDEL;
//debug
QMessageBox::critical(this, tr("Debug"), query.value(0).toString());
queryDEL.prepare("DELETE FROM `orders` WHERE oid = :oid");
queryDEL.bindValue(QString(":oid"), QVariant(query.value(0).toString()));
if(!queryDEL.exec()){
QMessageBox::critical(this, tr("Woops"), tr("Cannot commit query, removing items from the current database failed"));
//debug
QSqlError error = queryDEL.lastQuery();
QString sError = error.text();
QMessageBox::critical(this, tr("SQL error"), sError);
return false;
}
}
}
this->closeConnection();
return true;
}
To copy to clipboard, switch view to plain text mode
Bookmarks