def viewcalls(self): """ Returns an HTML table containing all telephone records. """ cursor = db.get_db_cursor() cursor.execute(db.normalize_sql('select * from calls order by datetime desc')) headings = ['service','callee','datetime','duration','seg','cost', 'call_group'] return self.result_set_to_html_table(cursor, headings = headings)
def addphone(self,number,desc): """ Adds or changes a description for a telephone number. """ conn = db.get_db_conn() cursor = conn.cursor() desc = desc.strip() if len(desc) == 0: cursor.execute(db.normalize_sql('delete from telbook where number=?'),(number,)) conn.commit() return cursor.execute(db.normalize_sql('select count(*) from telbook where number=?'),(number,)) if cursor.fetchone()[0] == 0: cursor.execute(db.normalize_sql('insert into telbook values (?,?)'), (number,desc)) else: cursor.execute(db.normalize_sql('update telbook set `desc`=? where number=?'), (desc,number)) conn.commit()
def get_calls_from_db(self, min, max): """ Retrieves calls from the database. """ cursor = db.get_db_cursor() sql = '''select callee,'''+db.datetime('datetime')+''' datetime, duration, cost, call_category from calls where '''+db.datetime('datetime')+''' >= ? and '''+db.datetime('datetime')+''' < ?''' sql = db.normalize_sql(sql) cursor.execute(sql, (str(min),str(max))) rows = [row for row in cursor] return rows
def get_data_for_top(top,top_condition): params = (min,max) sql = '''select '''+db.datetime_start_of('datetime',unit)+''' as day, sum(cost) from calls where call_category '''+top_condition+''' and '''+db.datetime('datetime')+''' >= ? and ''' sql += db.datetime('datetime')+''' <= ? group by day order by day''' sql = db.normalize_sql(sql) cursor.execute(sql, params) return { 'label':self.call_category[top]['desc'], 'color':self.call_category[top]['color'], 'data':[[1000*int(row[0]),float(row[1])] for row in cursor], }
def gettops(self,min=None,max=None): """ Returns the top 4 most expensive call groups for a specified period of time. """ cursor = db.get_db_cursor() (min,max)=self.get_default_min_max_time(cursor,min,max) params = (min,max) cursor.execute(db.normalize_sql('''select call_category from calls where '''+ db.datetime('datetime')+'''>= ? and '''+ db.datetime('datetime')+''' <= ? group by call_category order by sum(cost) desc limit 4'''), params) tops = [row[0] for row in cursor] return json.dumps(tops)
def get_default_min_max_time(self,cursor,min,max): """ Returns the default minimum and maximum timestamps so that all telephone records are retrieved from the database. The default minimum timestamp is 0 and the default maximum timestamp is the current timestamp. """ if min is None: min = 0 if max is None: cursor.execute(db.normalize_sql('select ' + db.datetime_now())) max = cursor.fetchone()[0] return (min,max)
def getcalls(self,min,max): """ Returns a detailed listing of the calls made between the specified timestamps. """ cursor = db.get_db_cursor() params = (min,max) sql = '''select callee,round(sum(cost),2),call_category,`desc` from calls left outer join telbook on callee=number where ''' sql += db.datetime('datetime')+'''>= ? and '''+db.datetime('datetime') sql += ''' <= ? group by callee order by sum(cost) desc''' cursor.execute(db.normalize_sql(sql), params) data = [[row[0], float(row[1]), self.call_category[row[2]]['color'], row[3]] for row in cursor] return json.dumps(data)