/*
 * Creates an sql query to fetch contact item IDs for all the contact items
 * which may contain the specified telephone number in a telephone, fax
 * or SMS type field.
 *
 * The comparison method used is not exact.  The number is compared starting from
 * the right side of the number and the method returns an array of candidate
 * matches.  Punctuation (e.g. spaces) and other alphabetic characters are ignored
 * when comparing.
 * 
 * Note that due to the way numbers are stored in the database, it is recommended
 * that at least 7 match digits are specified even when matching a number
 * containing fewer digits.  Failure to follow this guideline may (depending on the
 * database contents) mean that the function will not return the expected Contact
 * IDs.
 */
void CntFilterDetail::createMatchPhoneNumberQuery(
                                      const QContactFilter& filter,
                                      QString& sqlQuery,
                                      QContactManager::Error* error)

{
    if (!filterSupported(filter) ) {
      *error = QContactManager::NotSupportedError;
      return;
    }
          
    QContactDetailFilter detailFilter(filter);
    QString number((detailFilter.value()).toString());
    TPtrC numberPtr(reinterpret_cast<const TUint16*>(number.utf16()));
    
    TInt matchLengthFromRight(KDefaultMatchLength);
    // no need to propagate error, we can use the default match length
    TRAP_IGNORE(getMatchLengthL(matchLengthFromRight));
    
    TInt numLowerDigits = matchLengthFromRight;
    TInt numUpperDigits = 0;
    
    if (numLowerDigits > KLowerSevenDigits) {
        numLowerDigits = KLowerSevenDigits;
        numUpperDigits = matchLengthFromRight - KLowerSevenDigits;
    }
    else if (numLowerDigits == 0) {
        // best match phonenumbers
        numLowerDigits = KLowerSevenDigits;
    }
    
    TMatch phoneDigits = createPaddedPhoneDigits(
                          numberPtr, numLowerDigits, numUpperDigits, error);
    
    if (*error == QContactManager::NoError) {
        // select fields for contacts that match phone lookup
        //  SELECT contact_id FROM comm_addr
        //      WHERE value = [value string] AND type = [type value];
        //
        QString type =  QString(" type = %1").arg(CntDbInfo::EPhoneNumber);
        QString value =  QString(" value = %1").arg(phoneDigits.iLowerSevenDigits);
        QString whereClause = " WHERE" + value + " AND" + type;
        if (matchLengthFromRight <= KLowerSevenDigits) {
            // Matching 7 or less digits...
            sqlQuery = "SELECT contact_id FROM comm_addr" + whereClause;
        }
        else {
            // Checking the upper digits...
        
            // select fields for contacts that match phone lookup
            //  SELECT contact_id, extra_value FROM comm_addr
            //      WHERE value = [value string] AND type = [type value];
            //
            QString type =  QString(" type = %1").arg(CntDbInfo::EPhoneNumber);
            QString value =  QString(" value = %1").arg(phoneDigits.iLowerSevenDigits);
            QString whereClause = " WHERE" + value + " AND" + type;
            sqlQuery = "SELECT contact_id, extra_value FROM comm_addr" + whereClause;
            
            QList<QPair<QContactLocalId, QString> > contactMatches =  m_srvConnection.searchPhoneNumbers(sqlQuery, error);
            
            // Check if search query was successful
            if (*error != QContactManager::NoError) {
                  return;
                }
            
            QStringList list;
            for (int i=0; i<contactMatches.count(); ++i) {
                // Check the upper digits...
                TInt32 storedUpperDigits(0);
                QString extraValue = contactMatches.at(i).second;
                TPtrC extValString(reinterpret_cast<const TUint16*>(extraValue.utf16()));
                if (TLex(extValString).Val(storedUpperDigits) == KErrNone) {
                
                    const TInt KDigitsToRemove = KMaxPhoneMatchLength - KLowerSevenDigits - phoneDigits.iNumUpperDigits;
                    for (TInt j = 0; j < KDigitsToRemove; ++j) {
                        // repeatedly divide by 10 to lop off the appropriate number of digits from the right
                        storedUpperDigits /= 10;
                    }
                
                    storedUpperDigits = TMatch::padOutPhoneMatchNumber(storedUpperDigits, KDigitsToRemove);
                
                    if (phoneDigits.iUpperDigits == storedUpperDigits) {
                        list.append(QString("%1").arg(contactMatches.at(i).first));
                    }
                }
                else {
                    *error = QContactManager::UnspecifiedError;
                }
            }
            // Recreate query to fetch all match ids
            // SELECT DISTINCT contact_id FROM contact WHERE contact_id in (
            //      ..
            // )  
            QString ids = list.join(" ,");
            sqlQuery = "SELECT DISTINCT contact_id FROM contact WHERE contact_id in (";
            sqlQuery += ids;
            sqlQuery += ')';
        }
      
        // refine search
        if (bestMatchingEnabled()) {
            QList<QContactLocalId> list =  m_srvConnection.searchContacts(sqlQuery,error);
            QList<QContactLocalId> bestMatchingIds;
            if (*error == QContactManager::NoError) {
                TRAP_IGNORE(
                        bestMatchingIds = getBestMatchPhoneNumbersL(number, list, error);
                )
                if (bestMatchingIds.count()>0) {
                    // recreate query
                    QString selectQuery = " SELECT contact_id FROM comm_addr WHERE contact_id in (";
                    QString ids = QString("%1").arg(bestMatchingIds.at(0));
                    for(int i=1; i<bestMatchingIds.count(); ++i) {
                        ids += QString(" ,%1").arg(bestMatchingIds.at(i));
                    }
                    selectQuery += ids + ')';
                    sqlQuery = selectQuery;
                }
                else {
                    // empty list
                    QString selectQuery = " SELECT contact_id FROM comm_addr WHERE contact_id in (null)";
                    sqlQuery = selectQuery;
                }
            }
/*
 * Returns an sql query to fetch contact item IDs for all the contact items which may contain
 * the specified telephone number in a telephone, fax or SMS type field.
 *
 * This is improved version of createMatchPhoneNumberQuery method.
 * The number is compared starting from the right side of the number and 
 * the method returns an array of candidate matches.  
 * Punctuation (e.g. spaces) and other alphabetic characters are ignored
 * when comparing. Leading zeros are removed. Digits are compared up to 
 * the length of shorter number.
 */
void CntFilterDetail::bestMatchPhoneNumberQuery(const QContactFilter& filter,
                                                QString& sqlQuery,
                                                QContactManager::Error* error)
    {
    if (!filterSupported(filter) ) {
          *error = QContactManager::NotSupportedError;
          return;
        }
              
    QContactDetailFilter detailFilter(filter);
    QString number((detailFilter.value()).toString());
    TPtrC numberPtr(reinterpret_cast<const TUint16*>(number.utf16()));
    
    const TInt KUpperMaxLength = KMaxPhoneMatchLength - KLowerSevenDigits;
    TMatch phoneDigits = createPaddedPhoneDigits(numberPtr, KLowerSevenDigits, KUpperMaxLength, error);
    
    if (*error == QContactManager::NoError) {
        // select fields for contacts that match phone lookup
        //  SELECT contact_id, extra_value FROM comm_addr
        //      WHERE value = [value string] AND type = [type value];
        //
        QString type =  QString(" type = %1").arg(CntDbInfo::EPhoneNumber);
        QString value =  QString(" value = %1").arg(phoneDigits.iLowerSevenDigits);
        QString whereClause = " WHERE" + value + " AND" + type;
        sqlQuery = "SELECT contact_id, extra_value FROM comm_addr" + whereClause;
        
        QList<QPair<QContactLocalId, QString> > contactMatches =  m_srvConnection.searchPhoneNumbers(sqlQuery, error);
        
        // Check if search query was successful
        if (*error != QContactManager::NoError) {
              return;
            }
        
        QStringList list;
        for (int i=0; i<contactMatches.count(); ++i) {
            // Check the upper digits...
            TInt32 number = phoneDigits.iUpperDigits;
            QString extraValue = contactMatches.at(i).second;
            TPtrC extValString(reinterpret_cast<const TUint16*>(extraValue.utf16()));
            
            TInt32 storedUpperDigits(0);
            if (TLex(extValString).Val(storedUpperDigits) == KErrNone) {
                
                TInt32 stored = storedUpperDigits;
                TBool nonZeroInStoredFound = EFalse;
                TBool nonZeroInNumberFound = EFalse;
                while ((number != 0) && (stored != 0)) {
                    nonZeroInNumberFound |= (number % 10 != 0);
                    nonZeroInStoredFound |= (stored % 10 != 0);
                    if (nonZeroInStoredFound && nonZeroInNumberFound) {
                        break;
                    }
                    number /= 10;
                    stored /= 10;
                }
                
                if ((phoneDigits.iUpperDigits == 0) || (storedUpperDigits == 0) ||
                     (number == stored)) {
                    list.append(QString("%1").arg(contactMatches.at(i).first));
                }
            }
            else {
                *error = QContactManager::UnspecifiedError;
                return;
            }
        }
        // Recreate query to fetch all match ids
        // SELECT DISTINCT contact_id FROM contact WHERE contact_id in (
        //      ..
        // )  
        QString ids = list.join(" ,");
        sqlQuery = "SELECT DISTINCT contact_id FROM contact WHERE contact_id in (";
        sqlQuery += ids;
        sqlQuery += ')';
        }
    }