def getCallers(self, sid):
callers = []
user = User.query.filter_by(session_id=sid).first()
if user:
context = user.get_context()
for row in PbxDid.query.filter(PbxDid.context==context).all():
for cal in db.execute("SELECT * FROM channels WHERE dest like :did", {'did': "%"+row.did}).fetchall():
#PbxChannel.query.filter(PbxChannel.dest.like('%'+row.did).all():
l = PbxChannel.query.filter_by(uuid=cal.uuid).first()
if l:
to_user = l.dest
status = l.callstate
else:
to_user = ""
status = ""
direction = "inbound"
if len(cal.cid_num) > 10:
cid_num = cal.cid_num[len(cal.cid_num)-10:]
else:
cid_num = cal.cid_num
time_in_queue = db.execute("SELECT now() FROM channels WHERE uuid = :uuid", {'uuid': cal.uuid}).fetchone()
callers.append(make_caller(cal.dest, cal.cid_name,cid_num, direction, cal.created, time_in_queue[0], to_user, cal.uuid, status))
for cal in PbxChannel.query.filter_by(context=context).distinct(PbxChannel.call_uuid).all():
if len(cal.presence_id)>3:
if cal.presence_id.split("@")[1] == context:
time_in_queue = db.execute("SELECT now() FROM channels WHERE uuid = :uuid", {'uuid': cal.uuid}).fetchone()
callers.append(make_caller(cal.dest, cal.cid_name, cal.cid_num, "outbound", cal.created, "", cal.dest, cal.uuid, cal.callstate))
db.remove()
return callers
def get_volume(ext):
rows = db.execute("SELECT count(*) AS ct "
"FROM cdr WHERE (caller_id_number=:ext OR destination_number=:ext) "
"AND start_stamp BETWEEN CURRENT_DATE "
"AND CURRENT_TIMESTAMP AND bleg_uuid IS NOT null AND context = :context", {'ext':ext, 'context': session['context']})
r = rows.fetchone()
return r.ct
def delete_ivr(name):
for ivr in PbxIVR.query.filter_by(name=name).filter_by(context=session['context']).all():
r = PbxRoute.query.filter(PbxRoute.pbx_route_type_id==5).\
filter(PbxRoute.name==ivr.name).filter(PbxRoute.context==session['context']).first()
did = PbxDid.query.filter(PbxDid.pbx_route_id==r.id).first()
if did:
msg = "Error: IVR is in use by Inbound DID: "+str(did.did)+"!"
tod = db.execute("select * from pbx_tod_routes where match_route_id = :id or nomatch_route_id = :id", {'id': r.id}).first()
if tod:
msg = "Error: IVR is in a TOD route!"
ivr_opt = PbxIVROption.query.filter(PbxIVROption.pbx_route_id==r.id).first()
if ivr_opt:
msg = "Error: IVR belongs to another IVR Option."
if not did and not ivr_opt and not tod:
PbxRoute.query.filter(PbxRoute.pbx_route_type_id==5).\
filter(PbxRoute.name==ivr.name).filter(PbxRoute.context==session['context']).delete()
PbxIVROption.query.filter(PbxIVROption.pbx_ivr_id==ivr.id).delete()
PbxIVR.query.filter(PbxIVR.name==name).filter(PbxIVR.context==session['context']).delete()
db.flush()
else:
return msg
return "Successfully deleted IVR: "+name+"."
def get_findme(name, context):
e = PbxEndpoint.query.filter_by(user_context=context).filter_by(auth_id=name).first()
fm = db.execute("SELECT default_gateway FROM Customers "
"INNER JOIN Users on Users.customer_id=Customers.id "
"WHERE Users.id = :user_id", {'user_id': e.user_id}).fetchone()
ds = []
if e.find_me:
if e.follow_me_1:
if len(e.follow_me_1) < 8:
ds.append(u"user/"+e.follow_me_1+"@"+str(context))
else:
ds.append(u"sofia/gateway/"+str(fm[0])+"/{0}".format(e.follow_me_1))
if e.follow_me_2:
if len(e.follow_me_2) < 8:
ds.append(u"user/"+e.follow_me_2+"@"+str(context))
else:
ds.append(u"sofia/gateway/"+str(fm[0])+"/{0}".format(e.follow_me_2))
if e.follow_me_3:
if len(e.follow_me_3) < 8:
ds.append(u"user/"+e.follow_me_3+"@"+str(context))
else:
ds.append(u"sofia/gateway/"+str(fm[0])+"/{0}".format(e.follow_me_3))
if e.follow_me_4:
if len(e.follow_me_4) < 8:
ds.append(u"user/"+e.follow_me_4+"@"+str(context))
else:
ds.append(u"sofia/gateway/"+str(fm[0])+"/{0}".format(e.follow_me_4))
return ds
def get_queue_calls(self):
try:
user = User.query.filter_by(session_id=request.params.get("sid")).first()
if user:
context = user.get_context()
else:
return ""
items = []
sql = "SELECT * FROM call_center_callers WHERE queue like '%@{0}'".format(context)
for call in db.execute(sql):
items.append({'queue': call.queue.split("@")[0], 'cid_name': call.cid_name,
'cid_number': call.cid_number, 'agent': call.serving_agent.split("@")[0],
'state': call.state, 'uuid': call.uuid})
if not len(items) == 0:
return ""
return items
except Exception, e:
{'is_error': True, 'message': str(e)}
def getUsers(self, sid):
users = []
ep_stats = []
user = User.query.filter_by(session_id=sid).first()
try:
if user:
context = user.get_context()
else:
raise Exception("No session id in db matching the user calling this method.")
for r in db.execute("SELECT DISTINCT users.first_name, users.last_name, users.id, users.customer_id, "
"customers.context AS context, users.portal_extension, "
"users.tel, users.mobile, users.username, sip_dialogs.uuid AS uuid "
"FROM users "
"INNER JOIN customers ON users.customer_id = customers.id "
"LEFT JOIN sip_dialogs ON sip_dialogs.presence_id = users.portal_extension || '@' || customers.context "
"WHERE customers.context = :context ORDER BY users.id", {'context': context}):
for pbx_reg in PbxRegistration.query.filter(PbxRegistration.sip_realm==context).filter(PbxRegistration.sip_user==r[5]).all():
ep_stats.append({'ip': pbx_reg.network_ip, 'port':pbx_reg.network_port})
is_online = True if len(ep_stats) > 0 else False
ep_stats = []
# make_broker_user(name, id, customer_id, email, tel, mobile, ext, uuid, is_online):
users.append(make_broker_user(r[0]+' '+r[1], r[2], r[3], r[8], r[6], r[7], r[5], r[9], is_online))
db.remove()
return users
except Exceptio, e:
log.debug("Exception: %s" % e)
return []
def get_volume(ext):
rows = db.execute("select count(*) as ct "
"from cdr where (caller_id_number=:ext or destination_number=:ext) "
"and start_stamp between CURRENT_DATE "
"and CURRENT_TIMESTAMP and bleg_uuid is not null and context = :context",{'ext':ext, 'context': session['context']})
r = rows.fetchone()
return r.ct
def get_queue_calls(self):
try:
user = User.query.filter_by(
session_id=request.params.get("sid")).first()
if user:
context = user.get_context()
else:
return ""
items = []
sql = "SELECT * FROM call_center_callers WHERE queue like '%@{0}'".format(
context)
for call in db.execute(sql):
items.append({
'queue': call.queue.split("@")[0],
'cid_name': call.cid_name,
'cid_number': call.cid_number,
'agent': call.serving_agent.split("@")[0],
'state': call.state,
'uuid': call.uuid
})
if not len(items) == 0:
return ""
return items
except Exception, e:
{'is_error': True, 'message': str(e)}
def get_campaigns():
return db.execute(
"SELECT crm_campaigns.name FROM crm_campaigns "
"INNER JOIN crm_campaign_groups ON crm_campaigns.id = crm_campaign_groups.crm_campaign_id "
"INNER JOIN crm_group_members ON crm_group_members.crm_group_id = crm_campaign_groups.id "
"WHERE crm_group_members.extension = :ext", {
'ext': session['ext']
}).fetchall()
def get_talk_time(ext, context):
rows = db.execute("SELECT coalesce(sum(billsec)/60,0) AS mins FROM cdr "
"WHERE (caller_id_number=:ext OR destination_number=:ext) "
"AND start_stamp BETWEEN CURRENT_DATE AND CURRENT_TIMESTAMP "
"AND bleg_uuid IS NOT NULL AND context = :context",
{'ext':ext, 'context': context})
r = rows.fetchone()
db.remove()
return r.mins
def get_talk_time(ext):
rows = db.execute(
"select coalesce(sum(billsec)/60,0) as mins from cdr where (caller_id_number=:ext or destination_number=:ext) "
"and start_stamp between CURRENT_DATE and CURRENT_TIMESTAMP and bleg_uuid is not null and context = :context",
{
'ext': ext,
'context': session['context']
})
r = rows.fetchone()
return r.mins
def get_volume(ext):
rows = db.execute(
"select count(*) as ct "
"from cdr where (caller_id_number=:ext or destination_number=:ext) "
"and start_stamp between CURRENT_DATE "
"and CURRENT_TIMESTAMP and bleg_uuid is not null and context = :context",
{
'ext': ext,
'context': session['context']
})
r = rows.fetchone()
return r.ct
def get_volume(ext):
rows = db.execute(
"SELECT count(*) AS ct "
"FROM cdr WHERE (caller_id_number=:ext OR destination_number=:ext) "
"AND start_stamp BETWEEN CURRENT_DATE "
"AND CURRENT_TIMESTAMP AND bleg_uuid IS NOT null AND context = :context",
{
'ext': ext,
'context': session['context']
})
r = rows.fetchone()
return r.ct
def get_route_labels_ids():
route_labels = []
route_ids = []
for row in db.execute("SELECT sr.id, srt.name|| ':' ||sr.name AS name "
"FROM pbx_routes sr "
"INNER JOIN pbx_route_types srt ON sr.pbx_route_type_id = srt.id "
"WHERE sr.context = :context", {'context': session['context']}):
route_labels.append(row.name)
route_ids.append(row.id)
db.remove()
return (route_labels,route_ids)
def get_route_labels_ids():
route_labels = []
route_ids = []
for row in db.execute(
"SELECT sr.id, srt.name|| ':' ||sr.name AS name "
"FROM pbx_routes sr "
"INNER JOIN pbx_route_types srt ON sr.pbx_route_type_id = srt.id "
"WHERE sr.context = :context", {'context': session['context']}):
route_labels.append(row.name)
route_ids.append(row.id)
db.remove()
return (route_labels, route_ids)
def get_talk_time(ext):
rows = db.execute(
"SELECT coalesce(sum(billsec)/60,0) AS mins "
"FROM cdr "
"WHERE (caller_id_number=:ext "
"OR destination_number=:ext) "
"AND start_stamp BETWEEN CURRENT_DATE "
"AND CURRENT_TIMESTAMP "
"AND bleg_uuid IS NOT null "
"AND context = :context", {
'ext': ext,
'context': session['context']
})
r = rows.fetchone()
return r.mins
def callOutbound(self, sid, did):
user = User.query.filter_by(session_id=sid).first()
if user:
context = user.get_context()
else:
return
ep = db.execute("SELECT pbx_endpoints.outbound_caller_id_number AS pbx_endpoints_outbound_caller_id_number, customers.tel AS customers_tel "
"FROM pbx_endpoints "
"INNER JOIN customers on customers.context = pbx_endpoints.user_context "
"WHERE customers.context = :context "
"AND customers.id = :customer_id AND pbx_endpoints.auth_id = :auth_id",
{'context': context,'customer_id': user.customer_id, 'auth_id': user.portal_extension}).fetchone()
if len(ep[0])==10:
origination_caller_id_number = ep[0]
else:
origination_caller_id_number = ep[1]
con = ESLconnection(ESL_HOST, ESL_PORT, ESL_PASS)
if con.connected:
con.bgapi("originate", "{ringback=\'%(2000,4000,440.0,480.0)\',origination_caller_id_name=Click-To-Call,effective_caller_id_number="+str(origination_caller_id_number)+"}user/"+str(user.portal_extension)+"@"+str(context)+" &bridge(sofia/gateway/"+str(user.get_gateway())+"/"+str(did)+")")
def delete_ivr(name):
for ivr in PbxIVR.query.filter_by(name=name).filter_by(
context=session['context']).all():
r = PbxRoute.query.filter(PbxRoute.pbx_route_type_id==5).\
filter(PbxRoute.name==ivr.name).filter(PbxRoute.context==session['context']).first()
did = PbxDid.query.filter(PbxDid.pbx_route_id == r.id).first()
if did:
msg = "Error: IVR is in use by Inbound DID: " + str(did.did) + "!"
tod = db.execute(
"select * from pbx_tod_routes where match_route_id = :id or nomatch_route_id = :id",
{
'id': r.id
}).first()
if tod:
msg = "Error: IVR is in a TOD route!"
ivr_opt = PbxIVROption.query.filter(
PbxIVROption.pbx_route_id == r.id).first()
if ivr_opt:
msg = "Error: IVR belongs to another IVR Option."
if not did and not ivr_opt and not tod:
PbxRoute.query.filter(PbxRoute.pbx_route_type_id==5).\
filter(PbxRoute.name==ivr.name).filter(PbxRoute.context==session['context']).delete()
PbxIVROption.query.filter(
PbxIVROption.pbx_ivr_id == ivr.id).delete()
PbxIVR.query.filter(PbxIVR.name == name).filter(
PbxIVR.context == session['context']).delete()
db.flush()
else:
return msg
return "Successfully deleted IVR: " + name + "."
def get_findme(name, context):
e = PbxEndpoint.query.filter_by(user_context=context).filter_by(
auth_id=name).first()
fm = db.execute(
"SELECT default_gateway FROM Customers "
"INNER JOIN Users on Users.customer_id=Customers.id "
"WHERE Users.id = :user_id", {
'user_id': e.user_id
}).fetchone()
ds = []
if e.find_me:
if e.follow_me_1:
if len(e.follow_me_1) < 8:
ds.append(u"user/" + e.follow_me_1 + "@" + str(context))
else:
ds.append(u"sofia/gateway/" + str(fm[0]) +
"/{0}".format(e.follow_me_1))
if e.follow_me_2:
if len(e.follow_me_2) < 8:
ds.append(u"user/" + e.follow_me_2 + "@" + str(context))
else:
ds.append(u"sofia/gateway/" + str(fm[0]) +
"/{0}".format(e.follow_me_2))
if e.follow_me_3:
if len(e.follow_me_3) < 8:
ds.append(u"user/" + e.follow_me_3 + "@" + str(context))
else:
ds.append(u"sofia/gateway/" + str(fm[0]) +
"/{0}".format(e.follow_me_3))
if e.follow_me_4:
if len(e.follow_me_4) < 8:
ds.append(u"user/" + e.follow_me_4 + "@" + str(context))
else:
ds.append(u"sofia/gateway/" + str(fm[0]) +
"/{0}".format(e.follow_me_4))
return ds
def group_id(self):
g = db.execute(
"SELECT group_id FROM user_groups WHERE user_id = :user_id", {
'user_id': self.id
}).first()
return g[0]
def group_id(self):
g = db.execute("SELECT group_id FROM user_groups WHERE user_id = :user_id", {'user_id': self.id}).first()
return g[0]
def get_campaigns():
return db.execute("SELECT crm_campaigns.name FROM crm_campaigns "
"INNER JOIN crm_campaign_groups ON crm_campaigns.id = crm_campaign_groups.crm_campaign_id "
"INNER JOIN crm_group_members ON crm_group_members.crm_group_id = crm_campaign_groups.id "
"WHERE crm_group_members.extension = :ext", {'ext': session['ext']}).fetchall()
def get_talk_time(ext):
rows = db.execute("select coalesce(sum(billsec)/60,0) as mins from cdr where (caller_id_number=:ext or destination_number=:ext) "
"and start_stamp between CURRENT_DATE and CURRENT_TIMESTAMP and bleg_uuid is not null and context = :context",{'ext':ext, 'context': session['context']})
r = rows.fetchone()
return r.mins
