def view(request): # collect subscriptions result_array = [] for sub in Subscription.objects.all().order_by("plan"): if sub.plan != settings.DEFAULT_PLAN[ "id"] and sub.user.username not in helpers.company_accounts: result_array.append([ helpers.userlink(sub.user), sub.plan, sub.managed_by, sub.notes ]) headers = ["User", "Plan", "Payment Method", "Notes"] result = helpers.table(result_array, headers) result += helpers.header("Plans") # collect Plans def renderplan(plan): features = "" for key, value in plan.iteritems(): if "feature" in key: feature = key.replace("feature_", "") if value: feature = feature else: feature = "<span style='color:lightgray'>" + feature + "</span>" features += feature + " " result = [ "<b>" + plan["name"] + "</b><br />" + plan["id"], humanize.intcomma(plan["price_usd"] / 100), humanize.intcomma(plan["price_eur"] / 100), humanize.intcomma(plan["price_gbp"] / 100), plan['payment_interval'], features, "x" if plan["subscribable"] else "", "x" if plan["listable"] else "", Subscription.objects.filter(plan=plan['id']).count() ] return result array = [] for plan in settings.PLANS_SORTED: array.append(renderplan(plan)) result += helpers.table(array, [ "Name", "USD", "EUR", "GBP", "Interval", "Features", "Subs.", "Listable", "No. Users" ]) return SimpleTemplateResponse("insights/base.html", { "title": "Subscriptions and Plans", "insight_content": result })
def view(request): result = "" # # Most views # result += helpers.header("Users with the most views on their videos") count = viewsets.all_videos().values('team__owner__username').annotate( score=Sum("total_plays")).order_by('-score') max_rows = 25 result_array = [] for entry in count: result_array.append([ helpers.userlink(entry["team__owner__username"]), str(entry["score"]) ]) max_rows -= 1 if max_rows == 10: break result += helpers.table(result_array, ["username", "plays"]) # # Seen in last 30 days # enddate = date.today() - timedelta(days=30) daily_data = viewsets.user_activity_daily().filter(day__gt=enddate) userlist = {} for data in daily_data: if data.day == data.user.date_joined.date(): continue username = data.user.username if not username in userlist: userlist[username] = [username, 0] userlist[username][1] += 1 userlist = userlist.values() userlist.sort(key=lambda x: -x[1]) userlist = map(lambda x: [helpers.userlink(x[0]), str(x[1]) + " days"], userlist) result += helpers.header("Users seen in last 30 days") result += "Users seen on the same day as they signed up are stripped out<br /> <br />" result += helpers.table(userlist) return SimpleTemplateResponse("insights/base.html", { "title": "User statistics", "insight_content": result })
def listview(request): result = "" users = [] for u in User.objects.all(): videos = Video.objects.filter_for_user.filter(archived=False).count() # published videos_published = u.videos.filter(published=Video.PUBLIC, archived=False).count() demo_videos = Video.objects.filter( video__team__owner=u, service_identifier="2rtGFAnyf-s").count() if videos == 0: color = "red" elif videos > 0 and videos_published == 0 or demo_videos == videos: color = "darkorange" else: color = "green" user = [ "<span style = 'color:" + color + "'>" + helpers.userlink(u) + "</span>", "<b>" + str(videos) + "</b> videos", "<b>" + str(videos_published) + "</b> published", "<b>" + str(demo_videos) + "</b> demos" ] users.append(user) result += helpers.table(users) return SimpleTemplateResponse("insights/base.html", { "title": "All Users", "insight_content": result })
def build_overall_section(): result = helpers.header("Overall") result += helpers.table([ ['Users', viewsets.all_users().count()], ['Videos (incl. deleted)', viewsets.all_videos().count()], ['Published Videos', viewsets.published_videos().count()], ['Shared Videos', viewsets.shared_videos().count()], ['Upgraded', viewsets.upgraded_users().count()] ]); return result
def listview_sales(request): result = "" users = [] for u in User.objects.extra( select= { 'campaign_name': 'SELECT name FROM users_usercampaigndata WHERE users_usercampaigndata.user_id = auth_user.id', 'country': 'SELECT country FROM users_usercampaigndata WHERE users_usercampaigndata.user_id = auth_user.id', 'referrer': 'SELECT referrer FROM users_usercampaigndata WHERE users_usercampaigndata.user_id = auth_user.id', 'num_videos': 'SELECT COUNT(*) FROM videos_video JOIN users_team ON (videos_video.team_id = users_team.id) WHERE users_team.owner_id = auth_user.id AND videos_video.archived != True', 'num_videos_published': 'SELECT COUNT(*) FROM videos_video JOIN users_team ON (videos_video.team_id = users_team.id) WHERE users_team.owner_id = auth_user.id AND videos_video.archived != True AND videos_video.published = 1', }).order_by('-date_joined'): user = [ "<span>" + helpers.userlink(u) + "</span>", u.email, "" + str(u.country if u.country else '') + "", "" + str(u.campaign_name if u.campaign_name else '') + "", referral_link(u.referrer), "<b>" + str(u.num_videos) + "</b> v", "<b>" + str(u.num_videos_published) + "</b> p", "<b>" + str(u.date_joined.date()) + "</b>", ] users.append(user) result += helpers.table(users) return SimpleTemplateResponse("insights/base.html", { "title": "All Users (Sales List)", "insight_content": result })
def view(request): # # General Query Building blocks # SELECT_PUBLISHED_REVISIONS = "SELECT COUNT(*) FROM videos_video as v JOIN videos_videorevision as vr ON(v.current_revision_id = vr.id) JOIN users_team as t ON (t.id = v.team_id) JOIN auth_user as u ON (u.id = t.owner_id) " SELECT_PUBLISHED_MARKERS = SELECT_PUBLISHED_REVISIONS + " JOIN videos_marker vm ON (vm.video_revision_id = vr.id)" SELECT_PUBLISHED_CONTENT_BLOCKS = SELECT_PUBLISHED_MARKERS + " JOIN videos_markercontent vmc ON (vmc.marker_id = vm.id)" SELECT_VIDEO_SOURCES = SELECT_PUBLISHED_REVISIONS + " JOIN videos_source as s ON (vr.source_id = s.id)" result = "Staff videos are not included in these statistics. <br />Only published videos are counted." # # Overall stats # result += helpers.header("Overall Stats (Published)") num_videos = get_result(SELECT_PUBLISHED_REVISIONS) num_markers = get_result(SELECT_PUBLISHED_MARKERS) num_blocks = get_result(SELECT_PUBLISHED_CONTENT_BLOCKS) table = [] table.append(["Published Videos", num_videos]) table.append(["Published Markers", num_markers]) table.append(["Published Content Blocks", num_blocks]) table.append([""]) table.append(["Markers per publ. Video", num_markers / num_videos]) table.append(["Content Blocks per publ. Video", num_blocks / num_videos]) table.append(["Content Blocks per publ. Marker", num_blocks / num_markers]) result += helpers.table(table, ["Feature", "All", "Upgraded"]) # # Import Source Stats # result += helpers.header("Import Source Stats (Published)") num_uploaded = get_result(SELECT_VIDEO_SOURCES + " WHERE s.service like 'videopath'") num_youtube = get_result(SELECT_VIDEO_SOURCES + " WHERE s.service like 'youtube'") num_vimeo = get_result(SELECT_VIDEO_SOURCES + " WHERE s.service like 'vimeo'") num_wistia = get_result(SELECT_VIDEO_SOURCES + " WHERE s.service like 'wistia'") num_brightcove = get_result(SELECT_VIDEO_SOURCES + " WHERE s.service like 'brightcove'") table = [["Import Source", 'Videos']] table.append(["Uploaded Files", num_uploaded]) table.append(["Youtube Hosting", num_youtube]) table.append(["Vimeo Hosting", num_vimeo]) table.append(["Wistia Hosting", num_wistia]) table.append(["Brightcove Hosting", num_brightcove]) result += helpers.chart(table, 'column') # # Video Feature Stats # result += helpers.header("Video Feature Stats (Published)") num_custom_colors = get_result(SELECT_PUBLISHED_REVISIONS + " WHERE vr.ui_color_1 != '#424242'") num_continuous_playback = get_result( SELECT_PUBLISHED_REVISIONS + " WHERE vr.continuous_playback = true") num_equal_marker_lengths = get_result( SELECT_PUBLISHED_REVISIONS + "WHERE vr.ui_equal_marker_lengths = true") num_custom_thumbnail = get_result(SELECT_PUBLISHED_REVISIONS + " WHERE vr.custom_thumbnail_id != 0") num_disable_share_buttons = get_result( SELECT_PUBLISHED_REVISIONS + " WHERE vr.ui_disable_share_buttons = true") num_fit_video = get_result(SELECT_PUBLISHED_REVISIONS + " WHERE vr.ui_fit_video = true") num_custom_tracking_code = get_result( SELECT_PUBLISHED_REVISIONS + " WHERE vr.custom_tracking_code != ''") num_password = get_result(SELECT_PUBLISHED_REVISIONS + " WHERE vr.password !=''") # num_iphone_enabled = get_result(SELECT_PUBLISHED_REVISIONS + " WHERE vr.iphone_images > 20") table = [['Feature', 'Videos']] table.append(["Custom Colors", num_custom_colors]) table.append(["Custom Thumbnail", num_custom_thumbnail]) table.append(["Disabled Share Buttons", num_disable_share_buttons]) table.append(["Equal Marker Lengths", num_equal_marker_lengths]) table.append(["Fit Video", num_fit_video]) table.append(["Custom Tracking Code", num_custom_tracking_code]) table.append(["Password Protection", num_password]) table.append(["Continuous Playback", num_continuous_playback]) # table.append(["Iphone enabled", num_iphone_enabled]) result += helpers.chart(table, 'column') # # Content Block Stats # result += helpers.header("Content Block Stats (Published)") num_text_block = get_result(SELECT_PUBLISHED_CONTENT_BLOCKS + " WHERE vmc.type = 'text'") num_image_block = get_result(SELECT_PUBLISHED_CONTENT_BLOCKS + " WHERE vmc.type = 'image'") num_social_block = get_result(SELECT_PUBLISHED_CONTENT_BLOCKS + " WHERE vmc.type = 'social'") num_media_block = get_result(SELECT_PUBLISHED_CONTENT_BLOCKS + " WHERE vmc.type = 'media'") num_website_block = get_result(SELECT_PUBLISHED_CONTENT_BLOCKS + " WHERE vmc.type = 'website'") num_maps_block = get_result(SELECT_PUBLISHED_CONTENT_BLOCKS + " WHERE vmc.type = 'maps'") num_button_block = get_result(SELECT_PUBLISHED_CONTENT_BLOCKS + " WHERE vmc.type = 'simple_button'") table = [["Type", "Amount"]] table.append(["Text ", num_text_block]) table.append(["Image ", num_image_block]) table.append(["Social ", num_social_block]) table.append(["Media ", num_media_block]) table.append(["Website ", num_website_block]) table.append(["Button ", num_button_block]) table.append(["Maps ", num_maps_block]) result += helpers.chart(table, 'pie') return SimpleTemplateResponse("insights/base.html", { "title": "Features", "insight_content": result })
def listview(request): # video plays result = helpers.header("Plays of videos by user in the last 7 days") last_day = date.today() first_day = last_day - timedelta(days=7) count = DailyAnalyticsData.objects.filter( date__gte=first_day, date__lt=last_day)\ .values('video__team__owner__username')\ .annotate(score=Sum("plays_all"))\ .order_by('-score') rows = 0 result_array = [] for entry in count: result_array.append( [entry["video__team__owner__username"], str(entry["score"])]) rows += 1 if rows >= 10: break result += helpers.table(result_array, ["username", "plays"]) # # Popular videos # result += helpers.header("Most popular video the last 7 days") last_day = date.today() first_day = last_day - timedelta(days=7) count = DailyAnalyticsData.objects.filter( date__gte=first_day, date__lt=last_day)\ .values('video_id')\ .annotate(score=Sum("plays_all"))\ .order_by('-score') result_array = [] max_rows = 25 for entry in count: video = Video.objects.get(pk=entry["video_id"]) link = helpers.videolink(video) if link: link.append(entry['score']) result_array.append(link) max_rows -= 1 if max_rows == 0: break result += helpers.table( result_array, ["Title", "User", "Modified", "Plays all", "Plays last 7"]) # # published vids # startdate = datetime.now() enddate = startdate - timedelta(days=30) result += helpers.header("Recently published videos") videos = Video.objects.filter( current_revision__modified__range=[enddate, startdate]).order_by( '-current_revision__modified') result += helpers.videolist(videos) return SimpleTemplateResponse("insights/base.html", { "title": "Videos", "insight_content": result })