Ejemplo n.º 1
0
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 ) ;
}
Ejemplo n.º 2
0
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 ) ;
}
Ejemplo n.º 3
0
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 ) ;
}
Ejemplo n.º 4
0
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 );
 }
Ejemplo n.º 6
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 ) ;

}
Ejemplo n.º 7
0
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 ) ;
}
Ejemplo n.º 8
0
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 ) ;
}
Ejemplo n.º 9
0
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 ;
}
Ejemplo n.º 10
0
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 ;
}
Ejemplo n.º 11
0
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 ;
}
Ejemplo n.º 12
0
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 ;
}
Ejemplo n.º 13
0
void Bookmark::getEvaluations( QueryResult& query_r )
{
    query_r.clear();
    QString query = "select Evaluation from Evaluations" ;
    this->execQuery( query , query_r ) ;
}
Ejemplo n.º 14
0
void  Bookmark::getFolders( QueryResult& query_r )
{
    query_r.clear();
    QString query = "select Id, Folder from Folders" ;
    this->execQuery( query , query_r ) ;
}
Ejemplo n.º 15
0
void  Bookmark::getFavorites( QueryResult& query_r )
{
    query_r.clear();
    QString query = "select IdEntry from BookmarkEntries" ;
    this->execQuery( query , query_r ) ;
}
Ejemplo n.º 16
0
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() ;
}