2 * Copyright (c) 2015 Samsung Electronics Co., Ltd.
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
8 * http://www.apache.org/licenses/LICENSE-2.0
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.
18 #include <types_internal.h>
19 #include <context_mgr.h>
21 #include "common_types.h"
22 #include "db_handle_base.h"
24 #define DAY_OF_WEEK(SECOND) "CAST(strftime('%w', " SECOND ", 'unixepoch') AS INTEGER)"
25 #define HOUR_OF_DAY(SECOND) "CAST(strftime('%H', " SECOND ", 'unixepoch') AS INTEGER)"
27 ctx::stats_db_handle_base::stats_db_handle_base(const char* zone)
28 : is_trigger_item(false)
33 ctx::stats_db_handle_base::~stats_db_handle_base()
37 int ctx::stats_db_handle_base::generate_qid()
41 if (qid++ < 0) qid = 1;
45 bool ctx::stats_db_handle_base::execute_query(const char* subject, ctx::json filter, const char* query)
47 bool ret = db_manager::execute(generate_qid(), query, this);
48 IF_FAIL_RETURN(ret, false);
50 req_subject = subject;
56 std::string ctx::stats_db_handle_base::create_where_clause(ctx::json filter)
58 std::stringstream where_clause;
62 int timespan = DEFAULT_TIMESPAN;
65 std::string time_of_day;
67 if (filter.get(NULL, STATS_DAY_OF_WEEK, &week_str)) {
68 // In case of string (from Trigger)
69 if (week_str == STATS_WEEKDAY) {
70 week = STATS_DAY_OF_WEEK_WEEKDAY;
72 } else if (week_str == STATS_WEEKEND) {
73 week = STATS_DAY_OF_WEEK_WEEKEND;
75 } else if (week_str == STATS_SUN) {
76 week = STATS_DAY_OF_WEEK_SUN;
78 } else if (week_str == STATS_MON) {
79 week = STATS_DAY_OF_WEEK_MON;
81 } else if (week_str == STATS_TUE) {
82 week = STATS_DAY_OF_WEEK_TUE;
84 } else if (week_str == STATS_WED) {
85 week = STATS_DAY_OF_WEEK_WED;
87 } else if (week_str == STATS_THU) {
88 week = STATS_DAY_OF_WEEK_THU;
90 } else if (week_str == STATS_FRI) {
91 week = STATS_DAY_OF_WEEK_FRI;
93 } else if (week_str == STATS_SAT) {
94 week = STATS_DAY_OF_WEEK_SAT;
97 // In case of integer (from History)
98 filter.get(NULL, STATS_DAY_OF_WEEK, &week);
102 case STATS_DAY_OF_WEEK_WEEKDAY:
103 where_clause << "(" DAY_OF_WEEK(STATS_LOCAL_TIME) " > 0 AND " DAY_OF_WEEK(STATS_LOCAL_TIME) " < 6) AND ";
105 case STATS_DAY_OF_WEEK_WEEKEND:
106 where_clause << "(" DAY_OF_WEEK(STATS_LOCAL_TIME) " = 0 OR " DAY_OF_WEEK(STATS_LOCAL_TIME) " = 6) AND ";
108 case STATS_DAY_OF_WEEK_SUN:
109 case STATS_DAY_OF_WEEK_MON:
110 case STATS_DAY_OF_WEEK_TUE:
111 case STATS_DAY_OF_WEEK_WED:
112 case STATS_DAY_OF_WEEK_THU:
113 case STATS_DAY_OF_WEEK_FRI:
114 case STATS_DAY_OF_WEEK_SAT:
115 where_clause << DAY_OF_WEEK(STATS_LOCAL_TIME) " = " << week - STATS_DAY_OF_WEEK_SUN << " AND ";
121 if (filter.get(NULL, STATS_APP_ID, &app_id))
122 where_clause << STATS_APP_ID " = '" << app_id << "' AND ";
124 if (filter.get(NULL, STATS_START_TIME, &start))
125 where_clause << STATS_UNIV_TIME " >= " << start << " AND ";
127 if (filter.get(NULL, STATS_END_TIME, &end))
128 where_clause << STATS_UNIV_TIME " <= " << end << " AND ";
130 if (filter.get(NULL, STATS_TIME_OF_DAY, &time_of_day)) {
131 size_t pivot = time_of_day.find('-');
132 if (pivot != std::string::npos) {
133 std::string from = time_of_day.substr(0, pivot);
134 std::string to = time_of_day.substr(pivot + 1);
135 where_clause << "(" HOUR_OF_DAY(STATS_LOCAL_TIME) " >= " << from \
136 << " AND " HOUR_OF_DAY(STATS_LOCAL_TIME) " < " << to << ") AND ";
140 filter.get(NULL, STATS_TIMESPAN, ×pan);
141 where_clause << STATS_UNIV_TIME " > strftime('%s', 'now', '-" << timespan <<" day')";
143 return where_clause.str();
146 std::string ctx::stats_db_handle_base::create_sql_peak_time(ctx::json filter, const char* table_name, std::string where_clause)
148 std::stringstream query;
149 int limit = DEFAULT_LIMIT;
151 filter.get(NULL, STATS_RESULT_SIZE, &limit);
155 HOUR_OF_DAY(STATS_LOCAL_TIME) " AS " STATS_HOUR_OF_DAY ", COUNT(*) AS " STATS_TOTAL_COUNT \
156 " FROM " << table_name << \
157 " WHERE " << where_clause << \
158 " GROUP BY " HOUR_OF_DAY(STATS_LOCAL_TIME) \
159 " ORDER BY " STATS_TOTAL_COUNT " DESC" \
165 std::string ctx::stats_db_handle_base::create_sql_common_setting(ctx::json filter, const char* table_name, std::string where_clause)
167 std::stringstream query;
170 "SELECT ( SELECT " STATS_AUDIO_JACK \
171 " FROM " << table_name << \
172 " WHERE " << where_clause << \
173 " GROUP BY " STATS_AUDIO_JACK \
174 " ORDER BY count(" STATS_AUDIO_JACK ") DESC" \
175 " LIMIT 1 ) AS " STATS_AUDIO_JACK \
176 ", ( SELECT " STATS_SYSTEM_VOLUME \
177 " FROM " << table_name << \
178 " WHERE " << where_clause << \
179 " GROUP BY " STATS_SYSTEM_VOLUME \
180 " ORDER BY count(" STATS_SYSTEM_VOLUME ") DESC" \
181 " LIMIT 1 ) AS " STATS_SYSTEM_VOLUME \
182 ", ( SELECT " STATS_MEDIA_VOLUME \
183 " FROM " << table_name << \
184 " WHERE " << where_clause << \
185 " GROUP BY " STATS_MEDIA_VOLUME \
186 " ORDER BY count(" STATS_MEDIA_VOLUME ") DESC" \
187 " LIMIT 1 ) AS " STATS_MEDIA_VOLUME;
192 void ctx::stats_db_handle_base::on_creation_result_received(unsigned int query_id, int error)
196 void ctx::stats_db_handle_base::on_insertion_result_received(unsigned int query_id, int error, int64_t row_id)
201 void ctx::stats_db_handle_base::json_vector_to_array(std::vector<json> &vec_json, ctx::json &json_result)
203 std::vector<json>::iterator json_vec_end = vec_json.end();
205 for(std::vector<json>::iterator json_vec_pos = vec_json.begin(); json_vec_pos != json_vec_end; ++json_vec_pos) {
206 json origin_j = *json_vec_pos;
207 json_result.array_append(NULL, STATS_QUERY_RESULT, origin_j);
211 void ctx::stats_db_handle_base::on_query_result_received(unsigned int query_id, int error, std::vector<json>& records)
213 if (is_trigger_item) {
214 if (records.size() == 1) {
215 context_manager::reply_to_read(req_subject.c_str(), req_filter, error, records[0], req_zone.c_str());
217 _E("Invalid query result");
219 context_manager::reply_to_read(req_subject.c_str(), req_filter, ERR_OPERATION_FAILED, dummy, req_zone.c_str());
222 json results = "{\"" STATS_QUERY_RESULT "\":[]}";
223 json_vector_to_array(records, results);
224 context_manager::reply_to_read(req_subject.c_str(), req_filter, error, results, req_zone.c_str());