예제 #1
0
파일: reports.py 프로젝트: diegows/xreport
    def fetch_sites_report(self, page = 1, rp = 25,
                    sortname = 'download_bytes', sortorder = 'desc',
                    qtype = None, query = None):

        clients = DBSession.query(Domain.text,
                                func.count(MiniAccess.address_id.distinct()),
                                func.count(MiniAccess.proxy_user_id.distinct()),
                                func.sum(MiniAccess.count),
                                func.sum(MiniAccess.download_bytes),
                                func.sum(MiniAccess.upload_bytes),
                                Domain.id)
        clients = clients.join(MiniAccess)
        clients = clients.group_by(Domain.text)

        clients = clients.filter(MiniAccess.date == session['date'])
        
        sort_fn = get_sortfn(sortorder)
        if sortname == 'address':
            clients = clients.order_by(sort_fn('count_1'))
        elif sortname == 'proxy_user':
            clients = clients.order_by(sort_fn('count_2'))
        elif sortname == 'requests':
            clients = clients.order_by(sort_fn('sum_1'))
        elif sortname == 'download_bytes':
            clients = clients.order_by(sort_fn('sum_2'))
        elif sortname == 'upload_bytes':
            clients = clients.order_by(sort_fn('sum_3'))

        total = clients.count()
        offset = (page - 1) * rp
        clients = clients.offset(offset).limit(rp)

        rows = []
        for data in clients:
            clients = link("reports/sites_ip", data[6], data[1])
            users = link("reports/sites_users", data[6], int(data[2]) - 1)
            values = [ data[0], clients, users, data[3],
                        mb(data[4]),
                        mb(data[5]) ]
            row = { 'id' : data[0],
                    'cell' : values }
            rows.append(row)

        return dict(page = page, total = total, rows = rows)
예제 #2
0
파일: reports.py 프로젝트: diegows/xreport
    def fetch_users_report(self, page = 1, rp = 25,
                    sortname = 'download_bytes', sortorder = 'desc',
                    qtype = None, query = None):

        clients = DBSession.query(ProxyUser.text,
                                func.count(MiniAccess.domain_id.distinct()),
                                func.sum(MiniAccess.count),
                                func.sum(MiniAccess.download_bytes),
                                func.sum(MiniAccess.upload_bytes))
        clients = clients.join(MiniAccess)
        clients = clients.group_by(ProxyUser.text)

        clients = clients.filter(and_(MiniAccess.date == session['date'],
                                        ProxyUser.text != '-'))
        
        sort_fn = get_sortfn(sortorder)
        if sortname == 'sites':
            clients = clients.order_by(sort_fn('count_1'))
        elif sortname == 'requests':
            clients = clients.order_by(sort_fn('sum_1'))
        elif sortname == 'download_bytes':
            clients = clients.order_by(sort_fn('sum_2'))
        elif sortname == 'upload_bytes':
            clients = clients.order_by(sort_fn('sum_3'))

        total = clients.count()
        offset = (page - 1) * rp
        clients = clients.offset(offset).limit(rp)

        rows = []
        for data in clients:
            sites = link('reports/user_sites', data[0], data[1])
            values = [ data[0], sites, data[2],
                        mb(data[3]),
                        mb(data[4]) ]
            row = { 'id' : data[0],
                    'cell' : values }
            rows.append(row)

        return dict(page = page, total = total, rows = rows)
예제 #3
0
파일: reports.py 프로젝트: diegows/xreport
    def fetch_main_report(self, page = 1, rp = 25, sortname = 'date',
                    sortorder = 'desc', qtype = None, query = None):

        dash_id = DBSession.query(ProxyUser.id).filter(ProxyUser.text == "-")
        dash_id = dash_id.one()[0]

        count_ip = """count(distinct if(miniaccess.proxy_user_id = %d,
            miniaccess.address_id, 0))""" % (dash_id)

        count_user = """count(distinct if(miniaccess.proxy_user_id <> %d,
            miniaccess.proxy_user_id, 0))""" % (dash_id)

        if session['group_by'] == 'day':
            date_col = MiniAccess.date
        else:
            date_col = func.concat(func.min(MiniAccess.date), ' - ', 
                            func.max(MiniAccess.date))
                   
        #count the clients address only if the request is not authenticated
        #I use literal SQL because i don't find how to do this with ORM.
        main = DBSession.query(date_col,
                                count_ip,
                                count_user,
                                func.count(MiniAccess.domain_id.distinct()),
                                func.sum(MiniAccess.count),
                                func.sum(MiniAccess.download_bytes),
                                func.sum(MiniAccess.upload_bytes))
        main = main.group_by(MiniAccess.date)

        sort_fn = get_sortfn(sortorder)
        if sortname == 'date':
            main = main.order_by(sort_fn(MiniAccess.date))
        elif sortname == 'clients':
            main = main.order_by(sort_fn('2'))
        elif sortname == 'users':
            main = main.order_by(sort_fn('3'))
        elif sortname == 'sites':
            main = main.order_by(sort_fn('count_1'))
        elif sortname == 'requests':
            main = main.order_by(sort_fn('sum_1'))
        elif sortname == 'download_bytes':
            main = main.order_by(sort_fn('sum_2'))
        elif sortname == 'upload_bytes':
            main = main.order_by(sort_fn('sum_3'))

        total = main.count()
        offset = (page - 1) * rp
        main = main.offset(offset).limit(rp)

        rows = []
        print main
        for data in main:
            urldate = data[0]
            clients = link('reports/clients', urldate, data[1])
            # We don't count '-' user because it's used in unauth. msgs.
            users = link('reports/users', urldate, data[2])
            sites = link('reports/sites', urldate, data[3])
            values = [ data[0], clients, users, sites, data[4],
                        mb(data[5]),
                        mb(data[6]) ]
            row = { 'id' : data[0],
                    'cell' : values }
            rows.append(row)

        return dict(page = page, total = total, rows = rows)