//------Begin UserEventLog Class/Model-------//
}
UserEventLog::~UserEventLog(){
}
int UserEventLog
::rowCount(const QModelIndex &parent
) const{ Q_UNUSED(parent);
return m_userEventList.count();
}
QHash<int, QByteArray> UserEventLog::roleNames() const{
QHash<int, QByteArray> roleNames;
roleNames.insert(idRole, "id");
roleNames.insert(nameRole, "userName");
roleNames.insert(msgRole, "eventMessage");
roleNames.insert(dateRole, "dateTime");
return roleNames;
}
if (index.row() < 0 || index.row() >= m_userEventList.count()){
}
if(role == idRole){
userEventLogMsg msg = m_userEventList.at(index.row());
text = msg.id;
}
else if(role == nameRole){
userEventLogMsg msg = m_userEventList.at(index.row());
text = msg.username;
}
else if(role == msgRole){
userEventLogMsg msg = m_userEventList.at(index.row());
text = msg.eventmessage;
}
if(role == dateRole){
userEventLogMsg msg = m_userEventList.at(index.row());
text = msg.datetime;
}
return text;
}
void UserEventLog::addEvent(const userEventLogMsg &msg){
m_userEventList.insert(0, msg);
endInsertRows();
}
void UserEventLog::init(){
//emit showBusy(true);
dbConnect();
archiveEvent();
selectEvent();
//emit showBusy(false);
}
bool UserEventLog::dbConnect(){
//---check if database is connected---//
if(!m_selectDataBase.isValid()){
qDebug() << "error in connecting to DB";
m_selectDataBase
= QSqlDatabase::addDatabase("QSQLITE",
"conn2");
m_selectDataBase.setDatabaseName(Paths::root() + "/userLog.db");
qDebug() << "database connect path: "+Paths::root()+"/userLog.db";
m_selectDataBase.open();
}
else{
qDebug() <<"connected to DB" ;
m_selectDataBase.open();
}
return m_selectDataBase.isValid();
}
bool UserEventLog::selectEvent(){
dbConnect();
emit showBusy(true);
QSqlQuery selectQuery
("SELECT * FROM userlogevents WHERE dateTime BETWEEN ? and ?", m_selectDataBase
);
selectQuery.addBindValue(beginDate);
selectQuery.addBindValue(endDate);
if(selectQuery.exec()){
qDebug()<<"sql statement executed fine";
}
else{
emit xmui
->alertMsg
(QMessageBox::Warning,
"Database Error Message 1",
"Error: sql select script..."+selectQuery.
lastError().
text());
return selectQuery.exec();
}
userEventLogMsg msg;
beginResetModel();
m_userEventList.clear();
while (selectQuery.next()){
msg.id = selectQuery.value(0).toString();
msg.username = selectQuery.value(1).toString();
msg.eventmessage = selectQuery.value(2).toString();
msg.datetime = selectQuery.value(3).toString();
addEvent(msg);
}
endResetModel();
emit showBusy(false);
m_selectDataBase.close();
return selectQuery.exec();
}
bool UserEventLog::archiveEvent(){
//---connect to DB---//
dbConnect();
emit showBusy(true);
QString sql_str
= "ATTACH DATABASE '" + Paths
::root() + "/userLogArchive.db' as db2";
attachDbQry.prepare(sql_str);
qDebug() << "attached database path: "+sql_str;
//---execute attach DB---//
if(attachDbQry.exec()){
qDebug()<<"attached database succesfully";
}
else{
emit xmui
->alertMsg
(QMessageBox::Warning,
"Database Error Message 2",
"Error searching database..."+attachDbQry.
lastError().
text());
}
//---start DB transaction---//
m_selectDataBase.transaction();
//---get archive date vlaue--//
QSqlQuery dateQry
("SELECT * FROM userlogevents", m_selectDataBase
);
//---get currentDate minus 30days---//
QDate archiveDate
= QDate::currentDate().
addDays(-30);
qDebug() << "archiveDate: "+archiveDate.toString("yyyy-MM-dd");
//---copy from one Db table to another---//
bool prepareSqlBool;
prepareSqlBool = copyDataQry.prepare("INSERT INTO db2.userlogarchive (userName, eventMessage, dateTime) SELECT userName, eventMessage, dateTime FROM main.userlogevents WHERE dateTime < ?");
copyDataQry.addBindValue(archiveDate);
//---prepare sql copy---//
if(prepareSqlBool){
qDebug()<<"prepare copy sql statement exicuted fine";
}
else{
emit xmui
->alertMsg
(QMessageBox::Warning,
"Database Error Message 3",
"Error: prepare script..."+copyDataQry.
lastError().
text());
}
bool copySqlBool;
copySqlBool = copyDataQry.exec();
//---execute sql copy---//
if(copySqlBool){
qDebug()<<"insert copy sql statement exicuted fine";
//---Copy rows to archive then delete old rows---//
while(dateQry.next()){
//---Get date value from dateQry---//
dateStr = dateQry.value(3).toString();
rowDate
= QDate::fromString(dateStr,
"yyyy-MM-dd");
//---Executes only if copy qry executes---//
if(rowDate < archiveDate){
//---Sql delete statement---//
archiveDataQry.prepare("DELETE FROM userlogevents WHERE dateTime < ?");
archiveDataQry.addBindValue(archiveDate);
//---execute delete---//
if(archiveDataQry.exec()){
qDebug()<<"delete sql statement exicuted fine";
}
else{
emit xmui
->alertMsg
(QMessageBox::Warning,
"Database Error Message 5",
"Error: deleting old data..."+archiveDataQry.
lastError().
text());
//m_selectDataBase.rollback();
return archiveDataQry.exec();
}
}
else{
//qDebug() << "no old records to delete";
return false;
}
}
}
else{
emit xmui
->alertMsg
(QMessageBox::Warning,
"Database Error Message 4",
"Error: copy/insert archive data script..."+copyDataQry.
lastError().
text());
//m_selectDataBase.rollback();
return copySqlBool;
}
//---commit transaction & close---//
m_selectDataBase.commit();
m_selectDataBase.close();
emit showBusy(false);
return copySqlBool;
}
//---Leaving out search fcn definitions---//
