def dashboard(): user_count = User.query.filter_by(status=USER_STATUS.ACTIVE).count() mau = db.session.query('mau').from_statement( db.text( '''SELECT COUNT(DISTINCT(user_session.user_id)) AS mau FROM user_session, "user" WHERE user_session.user_id = "user".id AND "user".status = :status AND user_session.accessed_at >= (NOW() AT TIME ZONE 'UTC') - INTERVAL '30 days' ''' )).params(status=USER_STATUS.ACTIVE).first()[0] return render_template('dashboard.html', user_count=user_count, mau=mau)
def dashboard(): user_count = User.query.filter_by(status=USER_STATUS.ACTIVE).count() mau = db.session.query('mau').from_statement(db.text( '''SELECT COUNT(DISTINCT(user_session.user_id)) AS mau FROM user_session, "user" WHERE user_session.user_id = "user".id AND "user".status = :status AND user_session.accessed_at >= (NOW() AT TIME ZONE 'UTC') - INTERVAL '30 days' ''' )).params(status=USER_STATUS.ACTIVE).first()[0] return render_template('dashboard.html', user_count=user_count, mau=mau )
def dashboard_data_users_by_client(): client_users = defaultdict( lambda: { 'counts': { 'hour': 0, 'day': 0, 'week': 0, 'month': 0, 'quarter': 0, 'halfyear': 0, 'year': 0 } }) for label, interval in ( ('hour', '1 hour'), ('day', '1 day'), ('week', '1 week'), ('month', '1 month'), ('quarter', '3 months'), ('halfyear', '6 months'), ('year', '1 year'), ): clients = db.session.query( 'client_id', 'count', 'title', 'website' ).from_statement( db.text( '''SELECT client_users.client_id, count(*) AS count, client.title AS title, client.website AS website FROM (SELECT user_session.user_id, session_client.client_id FROM user_session, session_client, "user" WHERE user_session.user_id = "user".id AND session_client.user_session_id = user_session.id AND "user".status = :status AND session_client.updated_at >= (NOW() AT TIME ZONE 'UTC') - INTERVAL :interval GROUP BY session_client.client_id, user_session.user_id) AS client_users, client WHERE client.id = client_users.client_id GROUP by client_users.client_id, client.title, client.website ORDER BY count DESC''' )).params(status=USER_STATUS.ACTIVE, interval=interval).all() for row in clients: client_users[row.client_id]['title'] = row.title client_users[row.client_id]['website'] = row.website client_users[row.client_id]['id'] = row.client_id client_users[row.client_id]['counts'][label] = row.count - sum( client_users[row.client_id]['counts'].values()) users_by_client = sorted(client_users.values(), key=lambda r: sum(r['counts'].values()), reverse=True) outfile = StringIO() out = unicodecsv.writer(outfile, 'excel') out.writerow([ 'title', 'hour', 'day', 'week', 'month', 'quarter', 'halfyear', 'year' ]) for row in users_by_client: out.writerow([ row['title'], row['counts']['hour'], row['counts']['day'], row['counts']['week'], row['counts']['month'], row['counts']['quarter'], row['counts']['halfyear'], row['counts']['year'] ]) return outfile.getvalue(), 200, {'Content-Type': 'text/plain'}
def dashboard_data_users_by_month(): users_by_month = db.session.query('month', 'count').from_statement(db.text( '''SELECT date_trunc('month', "user".created_at) AS month, count(*) AS count FROM "user" WHERE status=:status GROUP BY month ORDER BY month''' )).params(status=USER_STATUS.ACTIVE) outfile = StringIO() out = unicodecsv.writer(outfile, 'excel') out.writerow(['month', 'count']) for month, count in users_by_month: out.writerow([month.strftime('%Y-%m-%d'), count]) return outfile.getvalue(), 200, {'Content-Type': 'text/plain'}
def dashboard_data_users_by_month(): users_by_month = db.session.query('month', 'count').from_statement(db.text( '''SELECT date_trunc('month', "user".created_at) AS month, count(*) AS count FROM "user" WHERE status=:status GROUP BY month ORDER BY month''' )).params(status=USER_STATUS.ACTIVE) outfile = StringIO() out = unicodecsv.writer(outfile, 'excel') out.writerow(['month', 'count']) for month, count in users_by_month: out.writerow([month.strftime('%Y-%m-%d'), count]) return outfile.getvalue(), 200, {'Content-Type': 'text/plain'}
def dashboard(): user_count = User.active_user_count() mau = ( db.session.query('mau') .from_statement( db.text( ''' SELECT COUNT(DISTINCT(user_session.user_id)) AS mau FROM user_session, "user" WHERE user_session.user_id = "user".id AND "user".status = :status AND user_session.accessed_at >= NOW() - INTERVAL '30 days' ''' ) ) .params(status=USER_STATUS.ACTIVE) .scalar() ) return render_template('dashboard.html.jinja2', user_count=user_count, mau=mau)
def dashboard_data_users_by_client(): client_users = defaultdict(lambda: {'counts': {'hour': 0, 'day': 0, 'week': 0, 'month': 0, 'quarter': 0, 'halfyear': 0, 'year': 0}}) for label, interval in ( ('hour', '1 hour'), ('day', '1 day'), ('week', '1 week'), ('month', '1 month'), ('quarter', '3 months'), ('halfyear', '6 months'), ('year', '1 year'), ): clients = db.session.query('client_id', 'count', 'title', 'website').from_statement(db.text( '''SELECT client_users.client_id, count(*) AS count, client.title AS title, client.website AS website FROM (SELECT user_session.user_id, session_client.client_id FROM user_session, session_client, "user" WHERE user_session.user_id = "user".id AND session_client.user_session_id = user_session.id AND "user".status = :status AND session_client.updated_at >= (NOW() AT TIME ZONE 'UTC') - INTERVAL :interval GROUP BY session_client.client_id, user_session.user_id) AS client_users, client WHERE client.id = client_users.client_id GROUP by client_users.client_id, client.title, client.website ORDER BY count DESC''' )).params(status=USER_STATUS.ACTIVE, interval=interval).all() for row in clients: client_users[row.client_id]['title'] = row.title client_users[row.client_id]['website'] = row.website client_users[row.client_id]['id'] = row.client_id client_users[row.client_id]['counts'][label] = row.count - sum(client_users[row.client_id]['counts'].values()) users_by_client = sorted(client_users.values(), key=lambda r: sum(r['counts'].values()), reverse=True) outfile = StringIO() out = unicodecsv.writer(outfile, 'excel') out.writerow(['title', 'hour', 'day', 'week', 'month', 'quarter', 'halfyear', 'year']) for row in users_by_client: out.writerow([ row['title'], row['counts']['hour'], row['counts']['day'], row['counts']['week'], row['counts']['month'], row['counts']['quarter'], row['counts']['halfyear'], row['counts']['year'] ]) return outfile.getvalue(), 200, {'Content-Type': 'text/plain'}