src/social-stats/DbHandle.cpp

   1 /*
   2  * Copyright (c) 2015 Samsung Electronics Co., Ltd.
   3  *
   4  * Licensed under the Apache License, Version 2.0 (the "License");
   5  * you may not use this file except in compliance with the License.
   6  * You may obtain a copy of the License at
   7  *
   8  * http://www.apache.org/licenses/LICENSE-2.0
   9  *
  10  * Unless required by applicable law or agreed to in writing, software
  11  * distributed under the License is distributed on an "AS IS" BASIS,
  12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  13  * See the License for the specific language governing permissions and
  14  * limitations under the License.
  15  */
  16
  17 #include <sstream>
  18 #include <contacts.h>
  19 #include <Types.h>
  20 #include "SocialStatisticsTypes.h"
  21 #include "DbHandle.h"
  22
  23 using namespace ctx;
  24
  25 SocialDbHandle::SocialDbHandle(ContextProvider *provider) :
  26         StatsDbHandleBase(provider)
  27 {
  28 }
  29
  30 SocialDbHandle::~SocialDbHandle()
  31 {
  32 }
  33
  34 int SocialDbHandle::read(CtxJson1 filter)
  35 {
  36         std::string query;
  37         const char *subject = reqProvider->getSubject();
  38
  39         if (STR_EQ(subject, SOCIAL_SUBJ_FREQ_ADDRESS)) {
  40                 query = createSqlFreqAddress(filter);
  41
  42         } else if (STR_EQ(subject, SOCIAL_SUBJ_FREQUENCY)) {
  43                 isTriggerItem = true;
  44                 query = createSqlFrequency(filter);
  45         }
  46
  47         IF_FAIL_RETURN(!query.empty(), ERR_OPERATION_FAILED);
  48
  49         bool ret = executeQuery(filter, query.c_str());
  50         IF_FAIL_RETURN(ret, ERR_OPERATION_FAILED);
  51
  52         return ERR_NONE;
  53 }
  54
  55 std::string SocialDbHandle::createWhereClause(CtxJson1 filter)
  56 {
  57         std::stringstream whereClause;
  58         int commType = -1;
  59
  60         whereClause << StatsDbHandleBase::createWhereClause(filter);
  61
  62         filter.get(NULL, SOCIAL_COMMUNICATION_TYPE, &commType);
  63
  64         switch(commType) {
  65         case SOCIAL_COMMUNICATION_TYPE_CALL:
  66                 whereClause <<
  67                         " AND " SOCIAL_PHONE_LOG_TYPE " >= " << CONTACTS_PLOG_TYPE_VOICE_INCOMING <<
  68                         " AND " SOCIAL_PHONE_LOG_TYPE " <= " << CONTACTS_PLOG_TYPE_VIDEO_BLOCKED;
  69                 break;
  70         case SOCIAL_COMMUNICATION_TYPE_MESSAGE:
  71                 whereClause <<
  72                         " AND " SOCIAL_PHONE_LOG_TYPE " >= " << CONTACTS_PLOG_TYPE_MMS_INCOMING <<
  73                         " AND " SOCIAL_PHONE_LOG_TYPE " <= " << CONTACTS_PLOG_TYPE_MMS_BLOCKED;
  74                 break;
  75         default:
  76                 break;
  77         }
  78
  79         return whereClause.str();
  80 }
  81
  82 std::string SocialDbHandle::createSqlFreqAddress(CtxJson1 filter)
  83 {
  84         std::stringstream query;
  85         int limit = DEFAULT_LIMIT;
  86
  87         filter.get(NULL, KEY_RESULT_SIZE, &limit);
  88
  89         query <<
  90                 "SELECT " SOCIAL_ADDRESS ", " \
  91                         "COUNT(*) AS " KEY_TOTAL_COUNT ", " \
  92                         "SUM(" KEY_DURATION ") AS " KEY_TOTAL_DURATION ", " \
  93                         "MAX(" KEY_UNIV_TIME ") AS " KEY_LAST_TIME \
  94                 " FROM " SOCIAL_TABLE_CONTACT_LOG \
  95                 " WHERE " << createWhereClause(filter) <<
  96                 " GROUP BY " SOCIAL_ADDRESS \
  97                 " ORDER BY COUNT(*) DESC" \
  98                 " LIMIT " << limit;
  99
 100         return query.str();
 101 }
 102
 103 std::string SocialDbHandle::createSqlFrequency(CtxJson1 filter)
 104 {
 105         CtxJson1 filterCleaned;
 106         std::string weekStr;
 107         std::string timeOfDay;
 108         std::string address;
 109
 110         if (!filter.get(NULL, SOCIAL_ADDRESS, &address)) {
 111                 _E("Invalid parameter");
 112                 return "";
 113         }
 114
 115         if (filter.get(NULL, KEY_DAY_OF_WEEK, &weekStr))
 116                 filterCleaned.set(NULL, KEY_DAY_OF_WEEK, weekStr);
 117
 118         if (filter.get(NULL, KEY_TIME_OF_DAY, &timeOfDay))
 119                 filterCleaned.set(NULL, KEY_TIME_OF_DAY, timeOfDay);
 120
 121         std::stringstream query;
 122
 123         query <<
 124                 "DELETE FROM " SOCIAL_TEMP_CONTACT_FREQ ";";
 125
 126         query <<
 127                 "INSERT INTO " SOCIAL_TEMP_CONTACT_FREQ \
 128                 " SELECT " SOCIAL_ADDRESS ", COUNT(*) AS " KEY_TOTAL_COUNT \
 129                 " FROM " SOCIAL_TABLE_CONTACT_LOG \
 130                 " WHERE " << createWhereClause(filterCleaned) <<
 131                 " GROUP BY " SOCIAL_ADDRESS ";";
 132
 133         query <<
 134                 "INSERT OR IGNORE INTO " SOCIAL_TEMP_CONTACT_FREQ " (" SOCIAL_ADDRESS ")" \
 135                 " VALUES ('" << address << "');";
 136
 137         query <<
 138                 "SELECT S." SOCIAL_ADDRESS ", S." KEY_TOTAL_COUNT ", 1+COUNT(lesser." KEY_TOTAL_COUNT ") AS " KEY_RANK \
 139                 " FROM " SOCIAL_TEMP_CONTACT_FREQ " AS S" \
 140                 " LEFT JOIN " SOCIAL_TEMP_CONTACT_FREQ " AS lesser" \
 141                 " ON S." KEY_TOTAL_COUNT " < lesser." KEY_TOTAL_COUNT \
 142                 " WHERE S." SOCIAL_ADDRESS " = '" << address << "'";
 143
 144
 145         return query.str();
 146 }
 147
 148 void SocialDbHandle::replyTriggerItem(int error, CtxJson1 &jsonResult)
 149 {
 150         IF_FAIL_VOID_TAG(STR_EQ(reqProvider->getSubject(), SOCIAL_SUBJ_FREQUENCY), _E, "Invalid subject");
 151
 152         CtxJson1 results;
 153         std::string valStr;
 154         int val;
 155
 156         jsonResult.get(NULL, SOCIAL_ADDRESS, &valStr);
 157         results.set(NULL, SOCIAL_ADDRESS, valStr);
 158         jsonResult.get(NULL, KEY_TOTAL_COUNT, &val);
 159         results.set(NULL, KEY_TOTAL_COUNT, val);
 160         jsonResult.get(NULL, KEY_RANK, &val);
 161         results.set(NULL, KEY_RANK, val);
 162
 163         reqProvider->replyToRead(reqFilter, error, results);
 164 }