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)
