/*

* Copyright (c) 2007-2009 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: 

*

*/

#include "pltables.h"

#include "dbsqlconstants.h"

#include "plplugins.h"

#include <cntdb.h>

/**

@param aDatabase A handle to the database.

@param aProperties A contact properties object.

@return A pointer to a new CPplCommAddrTable object.

*/

CPplCommAddrTable* CPplCommAddrTable::NewL(RSqlDatabase& aDatabase, CLplContactProperties& aProperties)

	{

	CPplCommAddrTable* self = CPplCommAddrTable::NewLC(aDatabase, aProperties);

	CleanupStack::Pop(self);

	return self;

	}

/**

@param aDatabase A handle to the database.

@param aProperties A contact properties object.

@return A pointer to a new CPplCommAddrTable object.

*/

CPplCommAddrTable* CPplCommAddrTable::NewLC(RSqlDatabase& aDatabase, CLplContactProperties& aProperties)

	{

	CPplCommAddrTable* self = new (ELeave) CPplCommAddrTable(aDatabase, aProperties);

	CleanupStack::PushL(self);

	self->ConstructL();

	return self;

	}

/**

Set up the CCntSqlStatement objects held by the class.

*/

void CPplCommAddrTable::ConstructL()

	{

	// Statement types

	TCntSqlStatementType insertType(EInsert, KSqlContactCommAddrTableName() );

	TCntSqlStatementType selectType(ESelect, KSqlContactCommAddrTableName() );

	TCntSqlStatementType updateType(EUpdate, KSqlContactCommAddrTableName() );

	TCntSqlStatementType deleteType(EDelete, KSqlContactCommAddrTableName() );

	// Where clauses

	// sizes of the clauses

	const TInt KWhereContactIdBufSize(KCommAddrContactId().Size() +

		KWhereStringEqualsStringFormatText().Size() + KCommAddrContactIdParam().Size() );

	const TInt KWhereCommAddrIdBufSize(KCommAddrId().Size() +

		KWhereStringEqualsStringFormatText().Size() + KCommAddrIdParam().Size() );

	const TInt KWhereAndClauseBufSize(KWhereCommAddrIdBufSize + KSqlAnd().Size() + KWhereCommAddrIdBufSize);

	HBufC* whereContactIdClause = HBufC::NewLC(KWhereContactIdBufSize);

	// for WHERE contact_id = [contact id value]

	whereContactIdClause->Des().AppendFormat(KWhereStringEqualsStringFormatText,

		&KCommAddrContactId, &KCommAddrContactIdParam );

	// for WHERE comm_addr_id = [comm addr id value]

	HBufC* whereCommAddrIdClause = HBufC::NewLC(KWhereCommAddrIdBufSize);

	whereCommAddrIdClause->Des().AppendFormat(KWhereStringEqualsStringFormatText,

		&KCommAddrId, &KCommAddrIdParam );

	// for WHERE value = [value string] AND type = [type value]

	HBufC* whereValueAndTypeClause = HBufC::NewLC(KWhereAndClauseBufSize);

	TPtr whereValueAndTypeClausePtr = whereValueAndTypeClause->Des();

	whereValueAndTypeClausePtr.AppendFormat(KWhereStringEqualsStringFormatText,

		&KCommAddrValue, &KCommAddrValueParam );

	whereValueAndTypeClausePtr.Append(KSqlAnd);

	whereValueAndTypeClausePtr.AppendFormat(KWhereStringEqualsStringFormatText,

		&KCommAddrType, &KCommAddrTypeParam );

	// INSERT

	// insert new comm addr record

	// 	INSERT INTO comm_addr

	//		(comm_addr_id, contact_id, type, value, extra_value)

	//		VALUES (NULL, [contact id value], [type value],

	//			[value string], [extra value string]);

	//

	iInsertStmnt = TSqlProvider::GetSqlStatementL(insertType);

	iInsertStmnt->SetParamL(KCommAddrId(), KCommAddrIdParam() );

	iInsertStmnt->SetParamL(KCommAddrContactId(), KCommAddrContactIdParam() );

	iInsertStmnt->SetParamL(KCommAddrValue(), KCommAddrValueParam() );

	iInsertStmnt->SetParamL(KCommAddrExtraValue(), KCommAddrExtraValueParam() );

	iInsertStmnt->SetParamL(KCommAddrType(), KCommAddrTypeParam() );

	// SELECT

	// select all fields for all comm addrs for a particular item id

	// For a statement in the following format:

	// 	SELECT comm_addr_id, type, value, extra_value FROM comm_addr

	//		WHERE contact_id = [contact id value];

	//

	iWholeSelectStmnt = TSqlProvider::GetSqlStatementL(selectType);

	iWholeSelectStmnt->SetParamL(KCommAddrId(), KNullDesC() );

	iWholeSelectStmnt->SetParamL(KCommAddrType(), KNullDesC() );

	iWholeSelectStmnt->SetParamL(KCommAddrValue(), KNullDesC() );

	iWholeSelectStmnt->SetParamL(KCommAddrExtraValue(), KNullDesC() );

	iWholeSelectStmnt->SetConditionL(*whereContactIdClause);

	// select fields for contacts that match phone, email or SIP lookup

	// For a statement in the following format:

	// 	SELECT contact_id, extra_value FROM comm_addr

	//		WHERE value = [value string] AND type = [type value];

	//

	iMatchSelectStmnt = TSqlProvider::GetSqlStatementL(selectType);

	iMatchSelectStmnt->SetParamL(KCommAddrContactId(), KNullDesC() );

	iMatchSelectStmnt->SetParamL(KCommAddrExtraValue(), KNullDesC() );

	iMatchSelectStmnt->SetConditionL(*whereValueAndTypeClause);

	// UPDATE

	// update comm addr record

	// For a statement in the following format:

	// 	UPDATE comm_addr SET

	//		contact_id = [contact id value],

	//		type = [type value],

	//		value = [value string],

	//		extra_value = [extra value string]

	//		WHERE comm_addr_id = [comm addr id value];

	//

	iUpdateStmnt = TSqlProvider::GetSqlStatementL(updateType);

	iUpdateStmnt->SetParamL(KCommAddrContactId(), KCommAddrContactIdParam() );

	iUpdateStmnt->SetParamL(KCommAddrValue(), KCommAddrValueParam() );

	iUpdateStmnt->SetParamL(KCommAddrExtraValue(), KCommAddrExtraValueParam() );

	iUpdateStmnt->SetParamL(KCommAddrType(), KCommAddrTypeParam() );

	iUpdateStmnt->SetConditionL(*whereCommAddrIdClause);

	// DELETE

	// delete single comm addr record

	// For a statement in the following format:

	// 	DELETE FROM comm_addr WHERE comm_addr_id = [comm addr id value];

	//

	iSingleDeleteStmnt = TSqlProvider::GetSqlStatementL(deleteType);

	iSingleDeleteStmnt->SetConditionL(*whereCommAddrIdClause);

	// delete all comm addrs for a particular contact item

	// For a statement in the following format:

	// 	DELETE FROM comm_addr WHERE contact_id = [contact id value];

	//

	iAllForItemDeleteStmnt = TSqlProvider::GetSqlStatementL(deleteType);

	iAllForItemDeleteStmnt->SetConditionL(*whereContactIdClause);

	CleanupStack::PopAndDestroy(3, whereContactIdClause); // and whereCommAddrIdClause, whereValueAndTypeClause

	}

/**

Insert new communication addresses into the comm_addr table.

@param aItem A contact item whose communication addresses are to be added to the contacts database.

*/

void CPplCommAddrTable::CreateInDbL(CContactItem& aItem)

	{

	// Check that the contact item is a card, own card or ICC entry.

	const TUid KType = aItem.Type();

	if (KType != KUidContactCard && KType != KUidContactOwnCard && KType != KUidContactICCEntry)

		{

		return;

		}

	// create lists for comm_addrs to keep track of what we have already seen so as to avoid duplicates

	RArray<TMatch> newPhones;

	RArray<TPtrC>  newEmails;

	RArray<TPtrC>  newSips;

	CleanupClosePushL(newPhones);

	CleanupClosePushL(newEmails);

	CleanupClosePushL(newSips);

	for (TInt fieldNum = aItem.CardFields().Count() - 1; fieldNum >= 0; --fieldNum)

		{

		CContactItemField& currField = aItem.CardFields()[fieldNum];

		const CContentType& contType = currField.ContentType();

		TBool isPhone(contType.ContainsFieldType(KUidContactFieldPhoneNumber) ||

					  contType.ContainsFieldType(KUidContactFieldFax)		  ||

					  contType.ContainsFieldType(KUidContactFieldSms)		  );

		TBool isEmail(contType.ContainsFieldType(KUidContactFieldEMail) );

		TBool isSip(contType.ContainsFieldType(KUidContactFieldSIPID) );

		// check it's a field we want and that it's not empty

		// insert a new address only if we haven't already seen it -- no point storing the same one twice.

		if ((isPhone || isEmail || isSip) && currField.StorageType() == KStorageTypeText

				&& currField.TextStorage()->IsFull() )

			{

			const TContactItemId KItemId(aItem.Id());

			// get phone numbers

			if (isPhone)

				{

				TMatch phoneNumber;

				phoneNumber = CreatePaddedPhoneDigitsL(currField.TextStorage()->Text(), KLowerSevenDigits,

													   KMaxPhoneMatchLength - KLowerSevenDigits);

				if (newPhones.Find(phoneNumber, TIdentityRelation<TMatch>(&TMatch::Equals) ) == KErrNotFound)

					{

					DoPhoneNumWriteOpL(phoneNumber, EInsert, KItemId);

					newPhones.AppendL(phoneNumber);

					}

				}

			// get email addresses

			else if (isEmail && newEmails.Find(currField.TextStorage()->Text() ) == KErrNotFound)

				{

				DoNonPhoneWriteOpL(currField.TextStorage()->Text(), EInsert, KItemId, EEmailAddress);

				newEmails.AppendL(currField.TextStorage()->Text() );

				}

			// get SIP addresses

			else if (newSips.Find(currField.TextStorage()->Text() ) == KErrNotFound)

				{

				DoNonPhoneWriteOpL(currField.TextStorage()->Text(), EInsert, KItemId, ESipAddress);

				newSips.AppendL(currField.TextStorage()->Text() );

				}

			}

		}

	CleanupStack::PopAndDestroy(3, &newPhones); // and newSips, newEmails

	}

/**

Updates communication addresses in the database.

If there are the same number of items to be updated as are already in the database, the

existing records are overwritten using the update statement. However, if there is a

different number of addresses to be updated, records in the database (if there are any)

are deleted (using DeleteL() ) and the new data is inserted (using CreateInDbL() ).

@param aItem A contact item whose communication addresses are to be updated in the contacts database.

*/

void CPplCommAddrTable::UpdateL(const CContactItem& aItem)

	{

	// Check that the contact item is a card, own card or ICC entry.

	const TUid type(aItem.Type() );

	if (type != KUidContactCard && type != KUidContactOwnCard && type != KUidContactICCEntry)

		{

		return;

		}

	const TContactItemId KItemId(aItem.Id() );

	// create lists for comm_addrs and go through contact item to populate them with any new ones we find

	RArray<TMatch> newPhones;

	RArray<TPtrC>  newEmails;

	RArray<TPtrC>  newSips;

	CleanupClosePushL(newPhones);

	CleanupClosePushL(newEmails);

	CleanupClosePushL(newSips);

	for (TInt fieldNum = aItem.CardFields().Count() - 1; fieldNum >= 0; --fieldNum)

		{

		CContactItemField& currField = aItem.CardFields()[fieldNum];

		const CContentType& contType = currField.ContentType();

		TBool isPhone(contType.ContainsFieldType(KUidContactFieldPhoneNumber) ||

					  contType.ContainsFieldType(KUidContactFieldFax)		  ||

					  contType.ContainsFieldType(KUidContactFieldSms)		  );

		TBool isEmail(contType.ContainsFieldType(KUidContactFieldEMail) );

		TBool isSip(contType.ContainsFieldType(KUidContactFieldSIPID) );

		// check it's a field we want and that it's not empty

		// store a new address if we haven't already seen it -- no point storing the same one twice.

		if ((isPhone || isEmail || isSip) && currField.StorageType() == KStorageTypeText

				&& currField.TextStorage()->IsFull() )

			{

			// get phone numbers

			if (isPhone)

				{

				TMatch phoneNumber;

				phoneNumber = CreatePaddedPhoneDigitsL(currField.TextStorage()->Text(), KLowerSevenDigits,

													   KMaxPhoneMatchLength - KLowerSevenDigits);

				if (newPhones.Find(phoneNumber, TIdentityRelation<TMatch>(&TMatch::Equals) ) == KErrNotFound)

					{

					newPhones.AppendL(phoneNumber);

					}

				}

			// get email addresses

			else if (isEmail && newEmails.Find(currField.TextStorage()->Text() ) == KErrNotFound)

				{

				newEmails.AppendL(currField.TextStorage()->Text() );

				}

			// get SIP addresses

			else if (newSips.Find(currField.TextStorage()->Text() ) == KErrNotFound)

				{

				newSips.AppendL(currField.TextStorage()->Text() );

				}

			}

		}

	// if there are no comm addresses in the contact item, delete any from the database

	if (!(newPhones.Count() + newEmails.Count() + newSips.Count() ) )

		{

		TBool lowDiskErr(EFalse);

		DeleteL(aItem, lowDiskErr);

		CleanupStack::PopAndDestroy(3, &newPhones); // and newSips, newEmails

		if (lowDiskErr)

			{

			User::Leave(KErrDiskFull);

			}

		return;

		}

	// create from the database a list of comm_addr_ids that can be recycled as their

	// comm_addrs are in the database but not in the new version of the contact item

	RArray<TInt> freeCommAddrIds;

	CleanupClosePushL(freeCommAddrIds);

	// weed out addresses from the list that are already in the db but haven't changed

	// and populate the freeCommAddrIds list

	RemoveNonUpdatedAddrsL(newPhones, newEmails, newSips, freeCommAddrIds, KItemId);

	// do the actual updating on an address-by-address basis

	DoUpdateCommAddrsL(newPhones, newEmails, newSips, freeCommAddrIds, KItemId);

	CleanupStack::PopAndDestroy(4, &newPhones); // and freeCommAddrIds, newSips, newEmails

	}

/**

Removes comm addresses from the 3 lists that are already in the database and have been updated.

It takes the 3 lists in as parameters and modifies them accordingly. It also populates the list

of comm address ids that are free to be recycled during updating.

*/

void CPplCommAddrTable::RemoveNonUpdatedAddrsL(RArray<TMatch>& aNewPhones, RArray<TPtrC>& aNewEmails, RArray<TPtrC>& aNewSips,

					  RArray<TInt>& aFreeCommAddrIds, const TInt aItemId)

	{

	// build the RSqlStatement

	RSqlStatement stmnt;

	CleanupClosePushL(stmnt);

	stmnt.PrepareL(iDatabase, iWholeSelectStmnt->SqlStringL() );

	const TInt KContactIdParamIndex(KFirstIndex); // first and only parameter in the query

	User::LeaveIfError(stmnt.BindInt(KContactIdParamIndex, aItemId) ) ;

	// fetch the results from the query and compare them with the new comm_addrs we have

	TInt err(KErrNone);

	while ((err = stmnt.Next() ) == KSqlAtRow)

		{

		const TInt KType(stmnt.ColumnInt(iWholeSelectStmnt->ParameterIndex(KCommAddrType() ) ) );

		if (KType == EPhoneNumber)

			{

			TMatch phoneNumber;

			TPtrC valString    = stmnt.ColumnTextL(iWholeSelectStmnt->ParameterIndex(KCommAddrValue() ) );

			TPtrC extValString = stmnt.ColumnTextL(iWholeSelectStmnt->ParameterIndex(KCommAddrExtraValue() ) );

			User::LeaveIfError(TLex(valString).Val(phoneNumber.iLowerSevenDigits) );

			User::LeaveIfError(TLex(extValString).Val(phoneNumber.iUpperDigits) );

			TInt matchIndex(aNewPhones.Find(phoneNumber, TIdentityRelation<TMatch>(&TMatch::Equals) ) );

			// remove any phone numbers from the new list if we already

			// have them in the db and they haven't changed...

			if (matchIndex != KErrNotFound)

				{

				aNewPhones.Remove(matchIndex);

				}

			// ...and add any spare ids to the recycle list

			else

				{

				aFreeCommAddrIds.AppendL(

					stmnt.ColumnInt(iWholeSelectStmnt->ParameterIndex(KCommAddrId() ) ) );

				}

			}

		else // is Email or SIP

			{

			TPtrC valString = stmnt.ColumnTextL(iWholeSelectStmnt->ParameterIndex(KCommAddrValue() ) );

			TInt matchIndex(0);

			// remove any email and sip addresses from the new list if

			// we already have them in the db and they haven't changed...

			if (KType == EEmailAddress)

				{

				matchIndex = aNewEmails.Find(valString);

				if (matchIndex != KErrNotFound)

					{

					aNewEmails.Remove(matchIndex);

					}

				}

			else // SIP

				{

				matchIndex = aNewSips.Find(valString);

				if (matchIndex != KErrNotFound)

					{

					aNewSips.Remove(matchIndex);

					}

				}

			// ...and add any spare ids to the recycle list

			if (matchIndex == KErrNotFound)

				{

				aFreeCommAddrIds.AppendL(

					stmnt.ColumnInt(iWholeSelectStmnt->ParameterIndex(KCommAddrId() ) ) );

				}

			}

		}

	// leave if we didn't complete going through the results properly

	if(err != KSqlAtEnd)

		{

		User::Leave(err);

		}

	CleanupStack::PopAndDestroy(&stmnt);

	}

/**

Does comm_addr updating on an address-by-address basis.

Takes 3 lists of new addresses and a list of free comm_addr_ids.

*/

void CPplCommAddrTable::DoUpdateCommAddrsL(RArray<TMatch>& aNewPhones, RArray<TPtrC>& aNewEmails, RArray<TPtrC>& aNewSips,

					  RArray<TInt>& aFreeCommAddrIds, const TInt aItemId)

	{

	// if we have free ids to recycle and new comm_addrs, insert them by UPDATE

	const TInt KFirstElementId(0);

	while (aFreeCommAddrIds.Count() && (aNewPhones.Count() || aNewEmails.Count() || aNewSips.Count() ) )

		{

		if(aNewPhones.Count() )

			{

			DoPhoneNumWriteOpL(aNewPhones[KFirstElementId], EUpdate, aItemId, aFreeCommAddrIds[KFirstElementId]);

			aNewPhones.Remove(KFirstElementId);

			aFreeCommAddrIds.Remove(KFirstElementId);

			}

		else if(aNewEmails.Count() )

			{

			DoNonPhoneWriteOpL(aNewEmails[KFirstElementId], EUpdate, aItemId, EEmailAddress,

								aFreeCommAddrIds[KFirstElementId]);

			aNewEmails.Remove(KFirstElementId);

			aFreeCommAddrIds.Remove(KFirstElementId);

			}

		else if(aNewSips.Count() )

			{

			DoNonPhoneWriteOpL(aNewSips[KFirstElementId], EUpdate, aItemId, ESipAddress,

								aFreeCommAddrIds[KFirstElementId]);

			aNewSips.Remove(KFirstElementId);

			aFreeCommAddrIds.Remove(KFirstElementId);

			}

		}

	// if we still have free ids to recycle but no new comm_addrs,

	// delete the existing comm_addrs for these ids

	const TInt KNumFreeIds(aFreeCommAddrIds.Count() );

	for (TInt i = 0; i < KNumFreeIds; ++i)

		{

		TBool lowDiskErr(EFalse);

		DeleteSingleCommAddrL(aFreeCommAddrIds[i], lowDiskErr);

		if (lowDiskErr)

			{

			User::Leave(KErrDiskFull);

			}

		}

	// if we still have new comm_addrs but no free ids to recycle,

	// put them in the database using INSERT

	const TInt KNumNewPhones(aNewPhones.Count() );

	const TInt KNumNewEmails(aNewEmails.Count() );

	const TInt KNumNewSips(aNewSips.Count() );

	for (TInt i = 0; i < KNumNewPhones; ++i)

		{

		DoPhoneNumWriteOpL(aNewPhones[i], EInsert, aItemId);

		}

	for (TInt i = 0; i < KNumNewEmails; ++i)

		{

		DoNonPhoneWriteOpL(aNewEmails[i], EInsert, aItemId, EEmailAddress);

		}

	for (TInt i = 0; i < KNumNewSips; ++i)

		{

		DoNonPhoneWriteOpL(aNewSips[i], EInsert, aItemId, ESipAddress);

		}

	}

/**

Deletes individual communication addresses from the database. In other words, deletes at

the sub-contact item level rather than deleting everything with a specific contact item ID.

*/

void CPplCommAddrTable::DeleteSingleCommAddrL(TInt aCommAddrId, TBool& aLowDiskErrorOccurred)

	{

	RSqlStatement stmnt;

	CleanupClosePushL(stmnt);

	stmnt.PrepareL(iDatabase, iSingleDeleteStmnt->SqlStringL() );

	const TInt KCommAddrIdParamIndex(KFirstIndex); // first and only parameter in the query

	User::LeaveIfError(stmnt.BindInt(KCommAddrIdParamIndex, aCommAddrId ) );

	TInt err = stmnt.Exec();

	CleanupStack::PopAndDestroy(&stmnt);

	if (err == KErrDiskFull)

		{

		aLowDiskErrorOccurred = ETrue;

		}

	else

		{

		User::LeaveIfError(err);

		}

	}

/**

Performs write operations for individual communication addresses of type "phone number".

This version of the method does not require a CommAddrId argument. Therefore, this can only be used

for insertion and not updating.

*/

void CPplCommAddrTable::DoPhoneNumWriteOpL(const CPplCommAddrTable::TMatch& aPhoneNum, TCntSqlStatement aType,

											TInt aCntId)

	{

	// provide a commaddr of 0 as a default argument

	DoPhoneNumWriteOpL(aPhoneNum, aType, aCntId, 0);

	}

/**

Performs write (Insert/Update) operations for indiviual communication addresses of type "phone number".

*/

void CPplCommAddrTable::DoPhoneNumWriteOpL(const CPplCommAddrTable::TMatch& aPhoneNum, TCntSqlStatement aType,

											TInt aCntId, TInt aCommAddrId)

	{

	// leave if the statement type is not insert or update.

	// also, we can't update if aCommAddrId is 0 as we don't know the record's id