/*

 * Copyright (c) 2008 Nokia Corporation and/or its subsidiary(-ies).

 * All rights reserved.

 * This component and the accompanying materials are made available

 * under the terms of "Eclipse Public License v1.0"

 * which accompanies this distribution, and is available

 * at the URL "http://www.eclipse.org/legal/epl-v10.html".

 *

 * Initial Contributors:

 * Nokia Corporation - initial contribution.

 *

 * Contributors:

 *

 * Description: CASqlQueryCreator implementation

 *

 */

#include <e32cmn.h>

#include <badesca.h>

#include "casqlquerycreator.h"

#include "casqlcommands.h"

#include "cainnerentry.h"

#include "cainnerquery.h"

#include "caarraycleanup.inl"

#include "casqlquery.h"

#include "cadebug.h"

#include "cadef.h"

#include "caoperationparams.h"

// ---------------------------------------------------------------------------

//

// ---------------------------------------------------------------------------

//

void CaSqlQueryCreator::CreateAddQueryL( CCaInnerEntry* aEntry,

        RPointerArray<CCaSqlQuery>& aSqlQuery, RSqlDatabase& aSqlDb )

    {

    DEBUG(("_CA_:CASqlQueryCreator::CreateAddQueryL"));

    CCaSqlQuery* queryIcon = CCaSqlQuery::NewLC( aSqlDb );

    TIconType iconType = CaSqlQueryCreator::CheckIconType( aEntry );

    if( iconType == EProperIcon )

        {

        CreateAddIconQueryL( aEntry, queryIcon, aSqlDb );

        //add new icon to DB

        aSqlQuery.AppendL( queryIcon );

        CleanupStack::Pop( queryIcon );

        }

    else

        {

        // icon added to DB by another entry or not added

        CleanupStack::PopAndDestroy( queryIcon );

        queryIcon = NULL;

        }

    if( aEntry->GetId() != -1 )

        {

        // check if entry is new and has to be added to DB or just updated

        CCaSqlQuery* query = CCaSqlQuery::NewLC( aSqlDb );

        if( aEntry->GetId() > 0 )

            {

            //Update query is faster than replace query

            CreateUpdateQueryEntryL( aEntry, query );

            }

        else

            {

            // check if entry's Uid was set

            if( aEntry->GetUid() == 0 )

                {

                if( ( aEntry->GetIconId() == 0 ) && ( queryIcon == NULL ) )

                    {

                    query->SetQueryL( 

                            KSQLInsertToEntryUniqueUidWithoutIcon );

                    }

                else

                    {

                    query->SetQueryL( KSQLInsertToEntryUniqueUid );

                    }

                }

            else

                {

                if( ( aEntry->GetIconId() == 0 ) && ( queryIcon == NULL ) )

                    {

                    query->SetQueryL( KSQLInsertToEntryWithoutIcon );

                    }

                else

                    {

                    query->SetQueryL( KSQLInsertToEntry );

                    }

                }

            }

        query->SetTableType( CCaSqlQuery::EEntryTable );

        aSqlQuery.AppendL( query );

        CleanupStack::Pop( query );

        }

    // remove all entry's attributs from DB

    CCaSqlQuery* query = CCaSqlQuery::NewLC( aSqlDb );

    query->SetQueryL( KSQLDeleteAttribute );

    aSqlQuery.Append( query );

    query->SetTableType( CCaSqlQuery::EAttributeTable );

    CleanupStack::Pop( query );

    if( aEntry->GetAttributes().Count() > 0 )

        {

        CCaSqlQuery* query = CCaSqlQuery::NewLC( aSqlDb );

        query->SetQueryL( KSQLInsertToAttribute );

        aSqlQuery.AppendL( query );

        query->SetTableType( CCaSqlQuery::EAttributeTable );

        CleanupStack::Pop( query );

        }

    if( iconType == ENullIconToRemove )

        {

        RBuf queryRemoveIcon;

        queryRemoveIcon.CleanupClosePushL();

        queryRemoveIcon.CreateL( KSQLDeleteIconWhereIconId().Length() );

        queryRemoveIcon.Append( KSQLDeleteIconWhereIconId );

        queryRemoveIcon.ReAllocL( KSQLDeleteIconWhereIconId().Length() + KMaxIntNumLength );

        queryRemoveIcon.AppendNum( aEntry->GetIconId() );

        CCaSqlQuery* query = CCaSqlQuery::NewLC( aSqlDb );

        query->SetQueryL( queryRemoveIcon );

        aSqlQuery.AppendL( query );

        query->SetTableType( CCaSqlQuery::EIconTable );

        CleanupStack::Pop( query );

        CleanupStack::PopAndDestroy( &queryRemoveIcon );

        }

    }

// ---------------------------------------------------------------------------

//

// ---------------------------------------------------------------------------

//

void CaSqlQueryCreator::CreateUpdateQueryEntryL(

        const CCaInnerEntry* aEntry, CCaSqlQuery* aQuery )

    {

    DEBUG( ("_CA_:CASqlQueryCreator::CreateUpdateQueryEntryL") );

    RBuf query;

    query.CleanupClosePushL();

    query.CreateL( KSQLUpdateEntry().Length() );

    query.Append( KSQLUpdateEntry );

    // text

    if( aEntry->GetText().Length() != 0 )

        {

        query.ReAllocL( query.Length() + KSQLUpdateEntryText().Length()

                + KComma().Length() );

        query.Append( KSQLUpdateEntryText );

        query.Append( KComma );

        }

    // description

    query.ReAllocL( query.Length() + KSQLUpdateEntryDescription().Length()

            + KComma().Length() );

    query.Append( KSQLUpdateEntryDescription );

    query.Append( KComma );

    // role

    query.ReAllocL( query.Length() + KSQLUpdateEntryRole().Length()

            + KComma().Length() );

    query.Append( KSQLUpdateEntryRole );

    query.Append( KComma );

    // type_name

    if( aEntry->GetEntryTypeName().Length() != 0 )

        {

        query.ReAllocL( query.Length() + KSQLUpdateEntryTypeName().Length()

                + KComma().Length() );

        query.Append( KSQLUpdateEntryTypeName );

        query.Append( KComma );

        }

    // id_icon

    TIconType iconType = CaSqlQueryCreator::CheckIconType( aEntry );

    if( iconType == ENullIconToRemove || iconType == ENullIcon )

        {

        query.ReAllocL( query.Length( ) + 

                KSQLUpdateEntryIdIconNull().Length() + KComma().Length() );

        query.Append( KSQLUpdateEntryIdIconNull );

        }

    else

        { 

        query.ReAllocL( query.Length( ) + KSQLUpdateEntryIdIcon().Length()

                + KComma().Length() );

        query.Append( KSQLUpdateEntryIdIcon );

        }

    query.Append( KComma );

    // check if entry's Uid was set

    if( aEntry->GetUid() )

        {

        query.ReAllocL( query.Length() + KSQLUpdateEntryUid().Length()

                + KComma().Length() );

        query.Append( KSQLUpdateEntryUid );

        query.Append( KComma );

        }

    else

        {

        query.ReAllocL( query.Length() + KSQLUpdateEntryUidNULL().Length()

                + KComma().Length() );

        query.Append( KSQLUpdateEntryUidNULL );

        query.Append( KComma );

        }

    //flags

    query.ReAllocL( query.Length() + KSQLUpdateEntryFlags().Length()

            + KComma().Length() );

    query.Append( KSQLUpdateEntryFlags );

    query.ReAllocL( query.Length() + KSQLUpdateWhere().Length() );

    query.Append( KSQLUpdateWhere );

    aQuery->SetQueryL( query );

    CleanupStack::PopAndDestroy( &query );

    }

// ---------------------------------------------------------------------------

//

// ---------------------------------------------------------------------------

//

void CaSqlQueryCreator::CreateFindIconQueryL( CCaInnerEntry* aEntry,

        CCaSqlQuery* aQuery)

    {

    RBuf iconQuery;

    iconQuery.CleanupClosePushL();

    iconQuery.CreateL( KSQLGetIconIdWhere );

    if( aEntry->Icon()->FileName().Compare( KNullDesC ) )

        {

        iconQuery.ReAllocL( iconQuery.Length() + KSQLUpdateIconFileName().Length()

                + KAnd().Length() );

        iconQuery.Append( KSQLUpdateIconFileName );

        iconQuery.Append( KAnd );

        }

    else

        { 

        iconQuery.ReAllocL( iconQuery.Length() + KSQLEmptyIconFileName().Length()

                + KAnd().Length() );

        iconQuery.Append( KSQLEmptyIconFileName );

        iconQuery.Append( KAnd );

        }

    if( aEntry->Icon()->SkinId().Compare( KNullDesC ) )

        {

        iconQuery.ReAllocL( iconQuery.Length() + KSQLUpdateIconSkinId().Length()

                + KAnd().Length() );

        iconQuery.Append( KSQLUpdateIconSkinId );

        iconQuery.Append( KAnd );

        }

    else

        {

        iconQuery.ReAllocL( iconQuery.Length() + KSQLEmptyIconSkinId().Length()

                + KAnd().Length() );

        iconQuery.Append( KSQLEmptyIconSkinId );

        iconQuery.Append( KAnd );

        }

    if( aEntry->Icon()->ApplicationId().Compare( KNullDesC ) )

        {

        iconQuery.ReAllocL( iconQuery.Length() + KSQLUpdateIconAppId().Length()

                + KAnd().Length() );

        iconQuery.Append( KSQLUpdateIconAppId );

        iconQuery.Append( KAnd );

        }

    else

        {

        iconQuery.ReAllocL( iconQuery.Length() + KSQLEmptyIconAppId().Length()

                + KAnd().Length() );

        iconQuery.Append( KSQLEmptyIconAppId );

        iconQuery.Append( KAnd );

        }

    if (!iconQuery.Right(KAnd().Length()).Compare(KAnd))

        {    

        iconQuery.Delete( iconQuery.Length() - KAnd().Length(), KAnd().Length() );

        }

    aQuery->SetQueryL( iconQuery );

    CleanupStack::PopAndDestroy( &iconQuery );

    }

// ---------------------------------------------------------------------------

//

// ---------------------------------------------------------------------------

//

void CaSqlQueryCreator::CreateAddIconQueryForNewL( CCaInnerEntry* aEntry,

        CCaSqlQuery* aQuery, RSqlDatabase& aSqlDb )

    {

    DEBUG( ("_CA_:CASqlQueryCreator::CreateAddIconQueryForNewL") );

    CCaSqlQuery* query = CCaSqlQuery::NewLC( aSqlDb );

    CreateFindIconQueryL(aEntry, query);

    query->SetTableType( CCaSqlQuery::EIconTable );

    query->PrepareL();

    query->BindValuesForGetIconL( aEntry );

    TInt idIcon( 0 );

    query->ExecuteL( idIcon );

    CleanupStack::PopAndDestroy( query );

    if( idIcon == 0 )  

        {

        // entry's icon data is not in DB

        if( ( aEntry->Icon()->FileName().Length() != 0 ) ||

            ( aEntry->Icon()->SkinId().Length() != 0 ) ||

            ( aEntry->Icon()->ApplicationId().Length() != 0 )

             )

            {

            // icon is not null, so that insert it to storage

            aQuery->SetQueryL( KSQLInsertToIcon );

            aQuery->SetTableType( CCaSqlQuery::EIconTable );

            }

        else

            {

            // icon is null

            aQuery->SetTableType( CCaSqlQuery::ENoTableType );

            }

        }

    else if( idIcon > 0 )

        {

        aEntry->SetIconId( idIcon );

        aQuery->SetTableType( CCaSqlQuery::ENoTableType );

        }

    }

// ---------------------------------------------------------------------------

//

// ---------------------------------------------------------------------------

//

void CaSqlQueryCreator::CreateAddIconQueryForUpdateL(CCaInnerEntry* aEntry,

        CCaSqlQuery* aQuery, RSqlDatabase& aSqlDb )

    {

    DEBUG( ("_CA_:CASqlQueryCreator::CreateAddIconQueryForUpdateL") );

    CCaSqlQuery* query = CCaSqlQuery::NewLC( aSqlDb );

    query->SetQueryL( KSQLGetIconDataByIdIcon );

    query->SetTableType( CCaSqlQuery::EIconTable );

    query->PrepareL();

    query->BindValuesForGetEntriesL( aEntry );

    CCaInnerIconDescription* innerIcon = CCaInnerIconDescription::NewLC();

    query->ExecuteL( innerIcon );

    query->CloseStatement();

    if( !(     !innerIcon->FileName().Compare( aEntry->Icon()->FileName() )

            && (!innerIcon->SkinId().Compare( aEntry->Icon()->SkinId() ))

            && (!innerIcon->ApplicationId().Compare( aEntry->Icon()->ApplicationId() ))

                    ) )

        {

        // entry's icon data is updated

        // if icon is used by another entry(s), new icon'll added to DB and

        // new Id_Icon'll set to entry, otherwise it'll just updated

        query->SetQueryL( KSQLGetIconsItems );

        query->SetTableType( CCaSqlQuery::EEntryTable );

        query->PrepareL();

        query->BindValuesForGetEntriesL( aEntry );

        RPointerArray<CCaInnerEntry> innerEntries;

        CleanupResetAndDestroyPushL( innerEntries );

        TInt count = query->ExecuteL( innerEntries, CCaSqlQuery::EEntry );

        query->CloseStatement();

        if( count > 1 )

            {

            //icon's data is used by another entry(s)

            aQuery->SetQueryL( KSQLInsertToIcon );

            aQuery->SetTableType( CCaSqlQuery::EIconTable );

            }

        else

            {

            // icon's data is used just by this entry

            CreateUpdateIconQueryL( aEntry, aQuery );

            }

        CleanupStack::PopAndDestroy( &innerEntries );

        }

    CleanupStack::PopAndDestroy( innerIcon );

    CleanupStack::PopAndDestroy( query );

    }

// ---------------------------------------------------------------------------

//

// ---------------------------------------------------------------------------

//

void CaSqlQueryCreator::CreateAddIconQueryL( CCaInnerEntry* aEntry,

        CCaSqlQuery* aQuery, RSqlDatabase& aSqlDb )

    {

    DEBUG( ("_CA_:CASqlQueryCreator::CreateAddIconQueryL") );

    // check if entry's icon id was set

    if( aEntry->GetIconId() == 0 )

        {

        // if entry doesn't have id_icon yet

        // check if entry's icon data doesn't exist in DB

        CreateAddIconQueryForNewL( aEntry, aQuery, aSqlDb );

        }

    else if( aEntry->GetIconId() > 0 )

        {

        // entry has id_icon set

        // check if entry's icon data are the same as in database

        CreateAddIconQueryForUpdateL( aEntry, aQuery, aSqlDb );

        }

    }

// ---------------------------------------------------------------------------

//

// ---------------------------------------------------------------------------

//

void CaSqlQueryCreator::CreateUpdateIconQueryL(

        const CCaInnerEntry* aEntry, CCaSqlQuery* aQuery )

    {

    DEBUG(("_CA_:CASqlQueryCreator::CreateUpdateIconQueryL"));

    RBuf query;

    query.CleanupClosePushL();

    query.CreateL( KSQLUpdateIcon().Length() );

    query.Append( KSQLUpdateIcon );

    if( aEntry->Icon()->FileName().Compare( KNullDesC ) )

        {

        query.ReAllocL( query.Length() + KSQLUpdateIconFileName().Length()

                + KComma().Length() );

        query.Append( KSQLUpdateIconFileName );

        query.Append( KComma );

        }

    if( aEntry->Icon()->SkinId().Compare( KNullDesC ) )

        {

        query.ReAllocL( query.Length() + KSQLUpdateIconSkinId().Length()

                + KComma().Length() );

        query.Append( KSQLUpdateIconSkinId );

        query.Append( KComma );

        }

     if( aEntry->Icon()->ApplicationId().Compare(KNullDesC) )

        {

        query.ReAllocL( query.Length() + KSQLUpdateIconAppId().Length()

                + KComma().Length() );

        query.Append( KSQLUpdateIconAppId );

        }

    if( !query.Right( KComma().Length() ).Compare( KComma ) )

        {

        query.Delete( query.Length() - KComma().Length(), KComma().Length() );

        }

    // add WHERE expr

    query.ReAllocL( query.Length() + KSQLUpdateIconWhere().Length() );

    query.Append( KSQLUpdateIconWhere );

    aQuery->SetQueryL( query );

    aQuery->SetTableType( CCaSqlQuery::EIconTable );

    CleanupStack::PopAndDestroy( &query );

    }

// ---------------------------------------------------------------------------

//

// ---------------------------------------------------------------------------

//

void CaSqlQueryCreator::CreateGetEntriesQueryL(

        const CCaInnerQuery* aQuery, CCaSqlQuery* aSqlQuery )

    {

    DEBUG( ("_CA_:CASqlQueryCreator::CreateGetEntriesQueryL") );

    if( aQuery->GetIds().Count() >= 1 )

        {

        //        just one or more ids so select just those items. easy case.

        CreateGetEntriesQueryByIdL( aQuery->GetIds().Count(), aSqlQuery );

        }

    else

        {

        //        create a sql query for a list of items to return.

        CreateGetEntriesQueryByQueryL( aQuery, aSqlQuery );

        }

    }

// ---------------------------------------------------------------------------

//

// ---------------------------------------------------------------------------

//

void CaSqlQueryCreator::CreateGetParentsIdsQueryL(

        const RArray<TInt>& aEntryIdArray, CCaSqlQuery* aSqlQuery,

        const RArray<TInt>& aParentIdArray )

    {

    DEBUG(("_CA_:CASqlQueryCreator::CreateGetParentsIdsQueryL"));

    RBuf entryIdList;

    entryIdList.CleanupClosePushL();

    CreateIdListL( aEntryIdArray.Count(), entryIdList, KSQLGEEntryId );

    RBuf parentIdList;

    parentIdList.CleanupClosePushL();

    CreateIdListL( aParentIdArray.Count(), parentIdList, KSQLGEIdGroup );

    RBuf query;

    query.CleanupClosePushL();

    query.CreateL( KSQLGetParentIds().Length() + entryIdList.Length() );

    query.AppendFormat( KSQLGetParentIds, &entryIdList );

    if( aParentIdArray.Count() > 0 )

        {

        query.ReAllocL( query.Length() + parentIdList.Length()

                + KSQLNotINIds().Length() );

        query.AppendFormat( KSQLNotINIds, &parentIdList );

        }

    aSqlQuery->SetQueryL( query );

    CleanupStack::PopAndDestroy( &query );

    CleanupStack::PopAndDestroy( &parentIdList );

    CleanupStack::PopAndDestroy( &entryIdList );

    }

// ---------------------------------------------------------------------------

//

// ---------------------------------------------------------------------------

//

void CaSqlQueryCreator::CreateGetAttributesQueryL(

        const TInt aIdEntryCount, CCaSqlQuery* aSqlQuery )

    {

    DEBUG(("_CA_:CASqlQueryCreator::CreateGetAttributesQueryL"));

    PopulateQueryWithIdListL( aIdEntryCount, aSqlQuery,

            KSQLGetAttributesByEntryId );

    }

// ---------------------------------------------------------------------------

//

// ---------------------------------------------------------------------------

//

void CaSqlQueryCreator::CreateRemoveQueryL(

        RPointerArray<CCaSqlQuery>& aSqlQuery, RSqlDatabase& aSqlDb )

    {

    DEBUG(("_CA_:CASqlQueryCreator::CreateRemoveQueryL"));