Exemplo n.º 1
0
    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
Exemplo n.º 2
0
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
Exemplo n.º 3
0
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+"."
Exemplo n.º 4
0
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
Exemplo n.º 5
0
    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)}
Exemplo n.º 6
0
    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 []
Exemplo n.º 7
0
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
Exemplo n.º 8
0
    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)}
Exemplo n.º 9
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()
Exemplo n.º 10
0
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
Exemplo n.º 11
0
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
Exemplo n.º 12
0
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
Exemplo n.º 13
0
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
Exemplo n.º 14
0
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)
Exemplo n.º 15
0
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)
Exemplo n.º 16
0
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
Exemplo n.º 17
0
    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)+")")
Exemplo n.º 18
0
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 + "."
Exemplo n.º 19
0
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
Exemplo n.º 20
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]
Exemplo n.º 21
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]
Exemplo n.º 22
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()
Exemplo n.º 23
0
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