Python Session.execute方法代码示例

# 需要导入模块: from freepybx.model.meta import Session [as 别名]
# 或者: from freepybx.model.meta.Session import execute [as 别名]
    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

# 需要导入模块: from freepybx.model.meta import Session [as 别名]
# 或者: from freepybx.model.meta.Session import execute [as 别名]
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

# 需要导入模块: from freepybx.model.meta import Session [as 别名]
# 或者: from freepybx.model.meta.Session import execute [as 别名]
    def getUsers(self, sid):
        users = []
        ep_stats = []
        user = User.query.filter_by(session_id=sid).first()

        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

# 需要导入模块: from freepybx.model.meta import Session [as 别名]
# 或者: from freepybx.model.meta.Session import execute [as 别名]
    def get_queue_calls(self):
        log.debug(request.params.get("sid"))
        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 ""

        out = items
        response = make_response(out)
        response.headers = [("Content-type", "application/json; charset=UTF-8")]

        return response(request.environ, self.start_response)

# 需要导入模块: from freepybx.model.meta import Session [as 别名]
# 或者: from freepybx.model.meta.Session import execute [as 别名]
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.did.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.commit()
            db.flush()
            db.remove()
        else:
            return msg

        db.remove()
        return "Successfully deleted IVR: "+name+"."

# 需要导入模块: from freepybx.model.meta import Session [as 别名]
# 或者: from freepybx.model.meta.Session import execute [as 别名]
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

# 需要导入模块: from freepybx.model.meta import Session [as 别名]
# 或者: from freepybx.model.meta.Session import execute [as 别名]
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

# 需要导入模块: from freepybx.model.meta import Session [as 别名]
# 或者: from freepybx.model.meta.Session import execute [as 别名]
def get_volume(ext, context):
    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': context})
    r = rows.fetchone()
    db.remove()
    return r.ct

# 需要导入模块: from freepybx.model.meta import Session [as 别名]
# 或者: from freepybx.model.meta.Session import execute [as 别名]
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)

# 需要导入模块: from freepybx.model.meta import Session [as 别名]
# 或者: from freepybx.model.meta.Session import execute [as 别名]
    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)+")")

# 需要导入模块: from freepybx.model.meta import Session [as 别名]
# 或者: from freepybx.model.meta.Session import execute [as 别名]
 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]

# 需要导入模块: from freepybx.model.meta import Session [as 别名]
# 或者: from freepybx.model.meta.Session import execute [as 别名]
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

# 需要导入模块: from freepybx.model.meta import Session [as 别名]
# 或者: from freepybx.model.meta.Session import execute [as 别名]
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()