void Bookmark::getRootFolders( QueryResult& query_r )
{
query_r.clear();
QString query = "select * from Folders where " ;
query += "Id not in ( select IdSubFolder from Folder_SubFolders ) " ;
this->execQuery( query , query_r ) ;
}
void QueryDB::execQuery( const QString& db_path , const QString& query , QueryResult& q_result )
{
sqlite3 *db;
sqlite3_stmt *stmt;
int rc = 0 ;
int col, cols;
q_result.clear();
rc = sqlite3_open( db_path.toAscii().data() , &db );
if( rc ){
return ;
}
rc = sqlite3_prepare_v2( db, query.toUtf8().data() , -1, &stmt, 0);
if( rc ){
return ;
}
cols = sqlite3_column_count(stmt);
for( col=0; col<cols; col++)
{
char *sql_buffer = NULL ;
sql_buffer = (char*)sqlite3_column_name( stmt, col ) ;
QString name = QString::fromUtf8( (const char*)sql_buffer ) ;
q_result.appendColumnName( name , col );
// qDebug() << QString( (const char*)sqlite3_column_name( stmt, col ) ) ;
}
QStringList row ;
cols = sqlite3_column_count(stmt);
// execute the statement
do{
rc = sqlite3_step(stmt);
switch( rc ){
case SQLITE_DONE:
break;
case SQLITE_ROW:
row.clear();
// print results for this row
for( col=0; col<cols; col++){
const unsigned char *sql_buffer = NULL ;
sql_buffer = sqlite3_column_text( stmt, col ) ;
//if ( cols == 1 )
row.append( QString::fromUtf8( (const char*)sql_buffer ) ) ;
}
q_result.appendResultRow( row ) ;
break;
default:
break;
}
} while ( rc == SQLITE_ROW ) ;
// finalize the statement to release resources
sqlite3_finalize(stmt);
sqlite3_close( db ) ;
}
void Bookmark::getParentFolder( QueryResult& query_r , const QString& categoria )
{
query_r.clear();
QString query = "select Id, Folder from Folders where " ;
query += "Id in ( select IdFolder from Folder_SubFolders where IdSubFolder in " ;
query += " ( select Id from Folders where Folder like \"" ;
query += categoria ;
query += "\"" ;
query += " ) ) " ;
this->execQuery( query , query_r ) ;
}
void Bookmark::getFavoriteFullData( QueryResult& query_r , const QString& id )
{
query_r.clear() ;
QString query ;
query = "select * from Articoli where Id = \"" ;
query += id ;
query += "\" " ;
QueryDB db ;
query_r = db.execQuery( query ) ;
}
void BookmarkGui::fillFavoriteInfo( const QString& id , const QString& Id_f )
{
this->fillFavoriteInfo( id ) ;
this->current_favorite_id = Id_f ;
Bookmark bk ;
QueryResult res ;
bk.getComment( res , Id_f ) ;
if ( res.empty() )
ui->Comments->setHtml( "" ) ;
else
ui->Comments->setHtml( res.getField(0,0) ) ;
ui->SaveComment->setDisabled( true );
res.clear() ;
bk.getState( res , Id_f ) ;
if ( !res.empty() )
{
int index = ui->State->findText( res.getField(0,0) ) ;
ui->State->setCurrentIndex( index );
}
else
ui->State->setCurrentIndex( 0 );
res.clear() ;
bk.getEvaluation( res , Id_f ) ;
if ( !res.empty() )
{
int index = ui->Evaluation->findData( res.getField(0,0) ) ;
ui->Evaluation->setCurrentIndex( index );
}
else
ui->Evaluation->setCurrentIndex( 0 );
}
void Bookmark::getFoldersId( QueryResult& query_r , const QString& parent_id )
{
query_r.clear();
if ( parent_id.size() == 0 )
{
this->getRootFolders( query_r );
return ;
}
QString query = "select Id, Folder from Folders where " ;
query += "Id in ( select IdSubFolder from Folder_SubFolders where IdFolder = " ;
query += parent_id ;
query += " ) " ;
this->execQuery( query , query_r ) ;
}
void Bookmark::getFolders( QueryResult& query_r , const QString& parent )
{
query_r.clear();
if ( parent.size() == 0 )
{
this->getRootFolders( query_r );
return ;
}
QString query = "select Id, Folder from Folders where " ;
query += "Id in ( select IdSubFolder from Folder_SubFolders where IdFolder in " ;
query += " ( select Id from Folders where Folder like \"" ;
query += parent ;
query += "\" ) ) " ;
this->execQuery( query , query_r ) ;
}
void Bookmark::getFavoritesByParentId( QueryResult& query_r , const QString& parent_id )
{
QString query ;
if ( parent_id.isEmpty() )
{
query = "select * from BookmarkEntries where Id not in ( select IdBookmarkEntry from Folders_BookmarkEntries ) " ;
}
else
{
query = "select * from BookmarkEntries where " ;
query += "Id in ( select IdBookmarkEntry from Folders_BookmarkEntries where IdFolder = " ;
query += parent_id ;
query += " ) " ;
}
query_r.clear() ;
this->execQuery( query , query_r ) ;
}
bool Bookmark::getState( QueryResult& query_r , QString favorite_id )
{
if ( favorite_id.isEmpty() ) return false ;
query_r.clear();
QString query ;
query = "select StateName from UserStates where Id in ( " ;
query += " select IdUserState from UserStates_BookmarkEntries where " ;
query += " IdBookmarkEntry = " ;
query += favorite_id ;
query += " ) " ;
this->execQuery( query , query_r ) ;
if ( query_r.empty() )
return false ;
else
return true ;
}
bool Bookmark::getEvaluation( QueryResult& query_r , QString favorite_id )
{
if ( favorite_id.isEmpty() ) return false ;
query_r.clear();
QString query ;
query = "select Evaluation from Evaluations where Id in ( " ;
query += " select IdEvaluation from Evaluations_BookmarkEntries where " ;
query += " IdBookmarkEntry = " ;
query += favorite_id ;
query += " ) " ;
this->execQuery( query , query_r ) ;
if ( query_r.empty() )
return false ;
else
return true ;
}
bool Bookmark::getFavoritesByState( QueryResult& query_r , const QString& state_name )
{
if ( state_name.isEmpty() ) return false ;
query_r.clear();
QString query ;
query = " select * from BookmarkEntries where Id in ( select IdBookmarkEntry " ;
query += " from UserStates_BookmarkEntries where IdUserState in " ;
query += " ( select Id from UserStates where StateName = \"" ;
query += state_name ;
query += "\" ) ) " ;
this->execQuery( query , query_r ) ;
qDebug() << query ;
if ( query_r.empty() )
return false ;
else
return true ;
}
bool Bookmark::getFavoritesByEvaluation( QueryResult& query_r , const QString& stars )
{
if ( stars.isEmpty() ) return false ;
query_r.clear();
QString query ;
query = " select * from BookmarkEntries where Id in ( select IdBookmarkEntry " ;
query += " from Evaluations_BookmarkEntries where IdEvaluation in " ;
query += " ( select Id from Evaluations where Evaluation = " ;
query += stars ;
query += " ) ) " ;
this->execQuery( query , query_r ) ;
qDebug() << query ;
if ( query_r.empty() )
return false ;
else
return true ;
}
void Bookmark::getEvaluations( QueryResult& query_r )
{
query_r.clear();
QString query = "select Evaluation from Evaluations" ;
this->execQuery( query , query_r ) ;
}
void Bookmark::getFolders( QueryResult& query_r )
{
query_r.clear();
QString query = "select Id, Folder from Folders" ;
this->execQuery( query , query_r ) ;
}
void Bookmark::getFavorites( QueryResult& query_r )
{
query_r.clear();
QString query = "select IdEntry from BookmarkEntries" ;
this->execQuery( query , query_r ) ;
}
void Bookmark::getStates( QueryResult& query_r )
{
query_r.clear();
QString query = "select * from UserStates" ;
this->execQuery( query , query_r ) ;
}
void QueryDB::execMainQuery( QueryResult& q_result )
{
QString query ;
QString logical_global ;
if ( this->global_and )
logical_global = " AND " ;
else
logical_global = " OR " ;
bool prevq = false ;
query.append( " ( " ) ;
if ( p_chiave )
{
QString logical_at = " OR " ;
QString logical = this->logical_titolo ;
query.append( " ( " ) ;
if ( parole_chiave.size() > 0 )
{
query.append( "( " ) ;
for ( QStringList::iterator it = parole_chiave.begin() ; it < parole_chiave.end() ; it++ )
{
QString first_word , word , last_word ;
first_word.append( *it ) ; first_word.append( " %" ) ;
word.append("%") ; word.append(*it) ; word.append("%") ;
last_word.append("% ") ; last_word.append(*it) ;
query.append(" ( titolo like \"") ;
query.append( first_word ) ;
query.append( "\" or " ) ;
query.append("titolo like \"") ;
query.append( word ) ;
query.append( "\" or " ) ;
query.append("titolo like \"") ;
query.append( last_word ) ;
query.append( "\" ) " ) ;
if ( it+1 != parole_chiave.end() )
query.append( logical ) ;
}
query.append( " ) " ) ;
}
else
{
query.append( "( titolo like null )" ) ;
}
query.append( logical_at ) ;
if ( parole_chiave_abstract.size() > 0 )
{
query.append( "( " ) ;
for ( QStringList::iterator it = parole_chiave_abstract.begin() ; it < parole_chiave_abstract.end() ; it++ )
{
QString first_word , word , last_word ;
first_word.append( *it ) ; first_word.append( " %" ) ;
word.append("%") ; word.append(*it) ; word.append("%") ;
last_word.append("% ") ; last_word.append(*it) ;
query.append(" ( abstract like \"") ;
query.append( first_word ) ;
query.append( "\" or " ) ;
query.append("abstract like \"") ;
query.append( word ) ;
query.append( "\" or " ) ;
query.append("abstract like \"") ;
query.append( last_word ) ;
query.append( "\" ) " ) ;
if ( it+1 != parole_chiave_abstract.end() )
query.append( logical ) ;
}
query.append( " )" ) ;
}
else
{
query.append( "( abstract like null )" ) ;
}
query.append( " )" ) ;
prevq = true ;
}
//list<string> lista_rubriche = this->getRubriche() ;
if ( rubriche && lista_rubriche.size() > 0 )
{
if ( prevq )
{
query.append( logical_global ) ;
prevq = false ;
}
query.append( " ( idrubrica in ( " ) ;
query.append( " select id from rubriche where " ) ;
for ( QStringList::iterator it = lista_rubriche.begin() ; it < lista_rubriche.end() ; it++ )
{
query.append( " ( idrubrica in ( " ) ;
query.append( " select id from rubriche where rubrica like \"" );
query.append( *it ) ;
query.append( "\" ) ) " ) ;
if ( it < lista_rubriche.end() - 1 )
{
query.append( " OR " ) ;
}
}
query.append( " ) ) " ) ;
prevq = true ;
}
if ( categorie && lista_categorie.size() > 0 )
{
if ( prevq )
{
query.append( logical_global ) ;
prevq = false ;
}
query.append( " ( articoli.id in ( " ) ;
query.append( " select idarticolo from articoli_categorie where " ) ;
for ( QStringList::iterator it = lista_categorie.begin() ; it < lista_categorie.end() ; it++ )
{
query.append( " ( idcategoria in ( " ) ;
query.append( " select id from categorie where categoria like \"" );
query.append( *it ) ;
query.append( "\" ) ) " ) ;
if ( it < lista_categorie.end() - 1 )
{
query.append( " OR " ) ;
}
}
query.append( " ) ) " ) ;
prevq = true ;
}
if ( autori_l && lista_autori.size() > 0 )
{
if ( prevq )
{
query.append( logical_global ) ;
prevq = false ;
}
query.append( " ( articoli.id in ( " ) ;
query.append( " select idarticolo from articoli_autori where " ) ;
for ( QStringList::iterator it = lista_autori.begin() ; it < lista_autori.end() ; it++ )
{
query.append( " ( idautore in ( " ) ;
query.append( " select id from autori where Autore like \"" ) ;
query.append( *it ) ;
query.append( "\" ) ) " ) ;
if ( it < lista_autori.end() - 1 )
{
query.append( " OR " ) ;
}
}
query.append( " ) ) " ) ;
prevq = true ;
}
if ( anno && lista_anni.size() > 0 )
{
if ( prevq )
{
query.append( logical_global ) ;
prevq = false ;
}
typedef QPair<int,int> yearsInterval ;
QList<yearsInterval> years ;
int a1 , a2 ;
qSort( lista_anni.begin() , lista_anni.end() , StrIntLessThan ) ;
for ( QStringList::iterator it = lista_anni.begin() ; it < lista_anni.end() ; it++ )
{
qDebug() << *it ;
a1 = (*it).toInt() ;
if ( it+1 < lista_anni.end() )
a2 = (*(it+1)).toInt() ;
else
a2 = a1 ;
if ( ( a2 - a1 ) == 1 )
{
int a3 = a1 ;
int a4 ;
QStringList::iterator it_b ;
for ( it_b = it + 1 ; it_b < lista_anni.end() ; it_b++ )
{
a4 = (*it_b).toInt() ;
if ( a4 - a3 == 1 )
a3 = a4 ;
else
break ;
}
yearsInterval y_int ;
y_int.first = a1 ;
y_int.second = a3 ;
years.append( y_int );
it = it_b-1 ;
qDebug() << "Intervallo: " << a1 << " " << a3 ;
}
else
{
yearsInterval y_int ;
y_int.first = a1 ;
y_int.second = a1 ;
years.append( y_int );
qDebug() << "Intervallo: " << a1 << " " << a1 ;
}
}
query.append( " ( articoli.idrivista in ( " ) ;
query.append( " select id from riviste where " ) ;
for ( QList<yearsInterval>::iterator it = years.begin() ; it < years.end() ; it++ )
{
query.append( " ( " ) ;
query.append( " anno >= " ) ;
query.append( QString::number( it->first ) ) ;
query.append( " and anno <= " ) ;
query.append( QString::number( it->second ) ) ;
query.append( " ) " ) ;
if ( it < years.end() - 1 )
{
query.append( " or " ) ;
}
}
query.append( " ) ) " ) ;
prevq = true ;
}
if ( testo )
{
if ( prevq )
{
query.append( logical_global ) ;
prevq = false ;
}
buildQuerySegTestoEsteso( query ) ;
prevq = true ;
}
query.append( " ) " ) ;
if ( fvorites_only )
{
Bookmark bk ;
QueryResult query_r ;
bk.getFavorites( query_r );
if ( !query_r.empty() )
{
query.append( " and articoli.id in ( " ) ;
for( QueryResult::iterator itr = query_r.begin() ; itr < query_r.end() ; itr++ )
{
query += query_r.getField( "IdEntry" , itr ) ;
if ( itr < query_r.end() - 1 )
query += " , " ;
}
query.append( " ) " ) ;
}
}
query.append( " ORDER BY idrivista" ) ;
//query.prepend( "SELECT titolo,idRivista,id FROM articoli WHERE " ) ;
query.prepend( "SELECT titolo,numero,articoli.id FROM articoli, riviste WHERE articoli.idrivista = riviste.id and " ) ;
qDebug() << query ;
q_result.clear() ;
this->execQuery( this->dbPath , query , q_result ) ;
//q_result.printResult() ;
}