def getUserDestinationNumberDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
tarifica_call.dialed_number as dialed_number, \
tarifica_extension.id as extension_number, \
DATE(tarifica_call.date) as date, \
tarifica_destinationgroup.prefix as prefix \
FROM tarifica_call LEFT JOIN tarifica_extension \
ON tarifica_call.extension_number = tarifica_extension.extension_number \
LEFT JOIN tarifica_destinationgroup \
ON tarifica_call.destination_group_id = tarifica_destinationgroup.id \
WHERE date > %s AND date < %s \
GROUP BY tarifica_call.dialed_number"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append((
row['extension_number'],
row['total_calls'],
row['total_seconds'],
row['cost'],
row['prefix'],
row['dialed_number'],
row['date']
))
return callDetail
def getPinsetDestinationDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
DATE(tarifica_call.date) as date, \
tarifica_pinset.id as pinset_number, \
tarifica_call.destination_group_id as destination_group_id \
FROM tarifica_call \
LEFT JOIN tarifica_pinset ON \
tarifica_call.pinset_number = tarifica_pinset.pinset_number \
WHERE date > %s AND date < %s AND tarifica_call.pinset_number IS NOT NULL\
GROUP BY tarifica_call.pinset_number, tarifica_call.destination_group_id"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append((
row['pinset_number'],
row['total_calls'],
row['total_seconds'],
row['cost'],
row['destination_group_id'],
row['date']
))
return callDetail
def getProviderDailyDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
DATE(tarifica_call.date) as date, \
tarifica_destinationgroup.provider_id as provider \
FROM tarifica_call JOIN tarifica_destinationgroup \
ON tarifica_call.destination_group_id = tarifica_destinationgroup.id \
WHERE date > %s AND date < %s \
GROUP BY tarifica_destinationgroup.provider_id"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append(
(row['provider'], row['cost'], row['total_calls'],
row['total_seconds'], row['date']))
return callDetail
def getPinsetDailyDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
tarifica_pinset.id as pinset_number, \
DATE(tarifica_call.date) as date \
FROM tarifica_call JOIN tarifica_pinset \
ON tarifica_call.pinset_number = tarifica_pinset.pinset_number \
WHERE date > %s AND date < %s \
GROUP BY pinset_number"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
d = (row['pinset_number'], row['total_calls'],
row['total_seconds'], row['cost'], row['date'])
print d
callDetail.append(d)
return callDetail
def getUserDestinationDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
DATE(tarifica_call.date) as date, \
tarifica_extension.id as extension_number, \
tarifica_call.destination_group_id as destination_group_id \
FROM tarifica_call \
LEFT JOIN tarifica_extension ON \
tarifica_call.extension_number = tarifica_extension.extension_number \
WHERE date > %s AND date < %s \
GROUP BY tarifica_call.extension_number, tarifica_call.destination_group_id"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append((row['extension_number'], row['total_calls'],
row['total_seconds'], row['cost'],
row['destination_group_id'], row['date']))
return callDetail
def getProviderDailyDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
DATE(tarifica_call.date) as date, \
tarifica_destinationgroup.provider_id as provider \
FROM tarifica_call JOIN tarifica_destinationgroup \
ON tarifica_call.destination_group_id = tarifica_destinationgroup.id \
WHERE date > %s AND date < %s \
GROUP BY tarifica_destinationgroup.provider_id"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append((
row['provider'],
row['cost'],
row['total_calls'],
row['total_seconds'],
row['date']
))
return callDetail
def getPinsetDailyDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
tarifica_pinset.id as pinset_number, \
DATE(tarifica_call.date) as date \
FROM tarifica_call JOIN tarifica_pinset \
ON tarifica_call.pinset_number = tarifica_pinset.pinset_number \
WHERE date > %s AND date < %s \
GROUP BY pinset_number"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
d = (
row['pinset_number'],
row['total_calls'],
row['total_seconds'],
row['cost'],
row['date']
)
print d
callDetail.append(d)
return callDetail
def getPinsetDestinationNumberDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
tarifica_call.dialed_number as dialed_number, \
tarifica_pinset.id as pinset_number, \
DATE(tarifica_call.date) as date, \
tarifica_destinationgroup.prefix as prefix \
FROM tarifica_call LEFT JOIN tarifica_pinset \
ON tarifica_call.pinset_number = tarifica_pinset.pinset_number \
LEFT JOIN tarifica_destinationgroup \
ON tarifica_call.destination_group_id = tarifica_destinationgroup.id \
WHERE date > %s AND date < %s \
GROUP BY tarifica_call.dialed_number"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append(
(row['pinset_number'], row['total_calls'],
row['total_seconds'], row['cost'], row['prefix'],
row['dialed_number'], row['date']))
return callDetail