{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[this doc on github](https://github.com/dotnet/interactive/tree/master/samples/notebooks/csharp/Samples)\n",
    "\n",
    "# .NET interactive report \n",
    "\n",
    "project report for [.NET interactive repo]()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup\n",
    "Importing pacakges and setting up connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "#i \"nuget:https://pkgs.dev.azure.com/dnceng/public/_packaging/dotnet5/nuget/v3/index.json\" \r\n",
    "#i \"nuget:https://pkgs.dev.azure.com/dnceng/public/_packaging/dotnet-tools/nuget/v3/index.json\" \r\n",
    "\r\n",
    "#r \"nuget:NodaTime, 2.4.8\"\r\n",
    "#r \"nuget:Octokit, 0.47.0\"\r\n",
    "#r \"nuget: XPlot.Plotly.Interactive, 4.0.2\"\r\n",
    "\r\n",
    "using static Microsoft.DotNet.Interactive.Formatting.PocketViewTags;\r\n",
    "using Microsoft.DotNet.Interactive.Formatting;\r\n",
    "using Octokit;\r\n",
    "using NodaTime;\r\n",
    "using NodaTime.Extensions;\r\n",
    "using XPlot.Plotly;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "var organization = \"dotnet\";\n",
    "var repositoryName = \"interactive\";\n",
    "var options = new ApiOptions();\n",
    "var gitHubClient = new GitHubClient(new ProductHeaderValue(\"notebook\"));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Generate a user token](https://help.github.com/en/github/authenticating-to-github/creating-a-personal-access-token-for-the-command-line) to get rid of public [api](https://github.com/octokit/octokit.net/blob/master/docs/getting-started.md) throttling policies for anonymous users "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "var tokenAuth = new Credentials(\"your token\");\n",
    "gitHubClient.Credentials = tokenAuth;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "var today = SystemClock.Instance.InUtc().GetCurrentDate();\n",
    "var startOfTheMonth = today.With(DateAdjusters.StartOfMonth);\n",
    "var startOfPreviousMonth = today.With(DateAdjusters.StartOfMonth) - Period.FromMonths(1);\n",
    "var startOfTheYear = new LocalDate(today.Year, 1, 1).AtMidnight();\n",
    "\n",
    "var currentYearIssuesRequest = new RepositoryIssueRequest {\n",
    "     State = ItemStateFilter.All,\n",
    "     Since = startOfTheYear.ToDateTimeUnspecified()\n",
    "};\n",
    "\n",
    "var pullRequestRequest = new PullRequestRequest {\n",
    "    State = ItemStateFilter.All\n",
    "};"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Perform github queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "#!time\n",
    "var branches = await gitHubClient.Repository.Branch.GetAll(organization, repositoryName);\n",
    "var pullRequests = await gitHubClient.Repository.PullRequest.GetAllForRepository(organization, repositoryName, pullRequestRequest);\n",
    "var forks = await gitHubClient.Repository.Forks.GetAll(organization, repositoryName);\n",
    "var currentYearIssues = await gitHubClient.Issue.GetAllForRepository(organization, repositoryName, currentYearIssuesRequest);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Branch data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pull request data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "var pullRequestCreatedThisMonth = pullRequests.Where(pr => pr.CreatedAt > startOfTheMonth.ToDateTimeUnspecified());\n",
    "var pullRequestClosedThisMonth =pullRequests.Where(pr => (pr.MergedAt != null && pr.MergedAt > startOfTheMonth.ToDateTimeUnspecified()));\n",
    "var contributorsCount = pullRequestClosedThisMonth.GroupBy(pr => pr.User.Login);\n",
    "\n",
    "var pullRequestLifespan = pullRequests.GroupBy(pr =>\n",
    "            {\n",
    "                var lifeSpan = (pr.ClosedAt ?? today.ToDateTimeUnspecified()) - pr.CreatedAt;\n",
    "                return Math.Max(0, Math.Ceiling(lifeSpan.TotalDays));\n",
    "            })\n",
    "            .Where(g => g.Key > 0)\n",
    "            .OrderBy(g => g.Key)\n",
    "            .ToDictionary(g => g.Key, g => g.Count());"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fork data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "var forkCreatedThisMonth = forks.Where(fork => fork.CreatedAt >= startOfTheMonth.ToDateTimeUnspecified());\n",
    "var forkCreatedPreviousMonth = forks.Where(fork => (fork.CreatedAt >= startOfPreviousMonth.ToDateTimeUnspecified()) && (fork.CreatedAt < startOfTheMonth.ToDateTimeUnspecified()));\n",
    "var forkCreatedByMonth = forks.GroupBy(fork => new DateTime(fork.CreatedAt.Year, fork.CreatedAt.Month, 1));\n",
    "var forkUpdateByMonth = forks.GroupBy(f => new DateTime(f.UpdatedAt.Year, f.UpdatedAt.Month,  1) ).Select(g => new {Date = g.Key, Count = g.Count()}).OrderBy(g => g.Date).ToArray();\n",
    "var total = 0;\n",
    "var forkCountByMonth = forkCreatedByMonth.OrderBy(g => g.Key).Select(g => new {Date = g.Key, Count = total += g.Count()}).ToArray();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Issues data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "bool IsBug(Issue issue){\n",
    "    return issue.Labels.FirstOrDefault(l => l.Name == \"bug\")!= null;\n",
    "}\n",
    "\n",
    "bool TargetsArea(Issue issue){\n",
    "    return issue.Labels.FirstOrDefault(l => l.Name.StartsWith(\"Area-\"))!= null;\n",
    "}\n",
    "\n",
    "string GetArea(Issue issue){\n",
    "    return issue.Labels.FirstOrDefault(l => l.Name.StartsWith(\"Area-\"))?.Name;\n",
    "}\n",
    "\n",
    "var openIssues = currentYearIssues.Where(IsBug).Where(issue => issue.State == \"open\");\n",
    "var closedIssues = currentYearIssues.Where(IsBug).Where(issue => issue.State == \"closed\");\n",
    "var oldestIssues = openIssues.OrderBy(issue => today.ToDateTimeUnspecified() - issue.CreatedAt).Take(20);\n",
    "var createdCurrentMonth = currentYearIssues.Where(IsBug).Where(issue => issue.CreatedAt >= startOfTheMonth.ToDateTimeUnspecified());\n",
    "var createdPreviousMonth = currentYearIssues.Where(IsBug).Where(issue => (issue.CreatedAt >= startOfPreviousMonth.ToDateTimeUnspecified()) && (issue.CreatedAt < startOfTheMonth.ToDateTimeUnspecified()));\n",
    "var openFromPreviousMonth = openIssues.Where(issue => (issue.CreatedAt > startOfPreviousMonth.ToDateTimeUnspecified()) && (issue.CreatedAt < startOfTheMonth.ToDateTimeUnspecified()));\n",
    "var createdByMonth = currentYearIssues.Where(IsBug).GroupBy(issue => new DateTime(issue.CreatedAt.Year, issue.CreatedAt.Month, 1)).OrderBy(g=>g.Key).ToDictionary(g => g.Key, g => g.Count());\n",
    "var closedByMonth = closedIssues.GroupBy(issue => new DateTime((int) issue.ClosedAt?.Year, (int) issue.ClosedAt?.Month, 1)).OrderBy(g=>g.Key).ToDictionary(g => g.Key, g => g.Count());\n",
    "var openIssueAge = openIssues.GroupBy(issue => new DateTime(issue.CreatedAt.Year, issue.CreatedAt.Month, issue.CreatedAt.Day)).ToDictionary(g => g.Key, g => g.Max(issue =>Math.Max(0, Math.Ceiling( (today.ToDateTimeUnspecified() - issue.CreatedAt).TotalDays))));\n",
    "var openByMonth = new Dictionary<DateTime, int>();\n",
    "var minDate = createdByMonth.Min(g => g.Key);\n",
    "var maxCreatedAtDate = createdByMonth.Max(g => g.Key);\n",
    "var maxClosedAtDate = closedByMonth.Max(g => g.Key);\n",
    "var maxDate = maxCreatedAtDate > maxClosedAtDate ?maxCreatedAtDate : maxClosedAtDate;\n",
    "var cursor = minDate;\n",
    "var runningTotal = 0;\n",
    "var issuesCreatedThisMonthByArea = currentYearIssues.Where(issue => issue.CreatedAt >= startOfTheMonth.ToDateTimeUnspecified()).Where(issue => IsBug(issue) && TargetsArea(issue)).GroupBy(issue => GetArea(issue)).ToDictionary(g => g.Key, g => g.Count());\n",
    "var openIssueByArea = currentYearIssues.Where(issue => issue.State == \"open\").Where(issue => IsBug(issue) && TargetsArea(issue)).GroupBy(issue => GetArea(issue)).ToDictionary(g => g.Key, g => g.Count());\n",
    "\n",
    "while (cursor <= maxDate )\n",
    "{\n",
    "    createdByMonth.TryGetValue(cursor, out var openCount);\n",
    "    closedByMonth.TryGetValue(cursor, out var closedCount);\n",
    "    runningTotal += (openCount - closedCount);\n",
    "    openByMonth[cursor] = runningTotal;\n",
    "    cursor = cursor.AddMonths(1);\n",
    "}\n",
    "\n",
    "var issueLifespan = currentYearIssues.Where(IsBug).GroupBy(issue =>\n",
    "            {\n",
    "                var lifeSpan = (issue.ClosedAt ?? today.ToDateTimeUnspecified()) - issue.CreatedAt;\n",
    "                return Math.Max(0, Math.Round(Math.Ceiling(lifeSpan.TotalDays),0));\n",
    "            })\n",
    "            .Where(g => g.Key > 0)\n",
    "            .OrderBy(g => g.Key)\n",
    "            .ToDictionary(g => g.Key, g => g.Count());\n",
    "\n",
    "display(new { \n",
    "    less_then_one_sprint = issueLifespan.Where(i=> i.Key < 21).Select(i => i.Value).Sum(),\n",
    "    less_then_two_sprint = issueLifespan.Where(i=> i.Key >= 21 && i.Key < 42).Select(i => i.Value).Sum(),\n",
    "    more_then_two_sprint = issueLifespan.Where(i=> i.Key >= 42).Select(i => i.Value).Sum()   \n",
    "    });"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Activity dashboard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "var createdByMonthSeries = new Scattergl{\n",
    "    name = \"Created\",\n",
    "    x = createdByMonth.Select(g => g.Key),\n",
    "    y = createdByMonth.Select(g => g.Value),\n",
    "};\n",
    "\n",
    "var openByMonthSeries = new Scattergl{\n",
    "    name = \"Open\",\n",
    "    x = openByMonth.Select(g => g.Key),    \n",
    "    y = openByMonth.Select(g => g.Value),\n",
    "};\n",
    "\n",
    "var closedByMonthSeries = new Scattergl{\n",
    "    name = \"Closed\",\n",
    "    x = closedByMonth.Select(g => g.Key),\n",
    "    y = closedByMonth.Select(g => g.Value),\n",
    "};\n",
    "\n",
    "var issueChart = Chart.Plot(new[] {createdByMonthSeries, closedByMonthSeries, openByMonthSeries});\n",
    "issueChart.WithTitle(\"Bugs by month\");\n",
    "display(issueChart);\n",
    "\n",
    "var issueLifespanOnWeekSeries = new Bar\n",
    "{\n",
    "    name = \"One week old\",\n",
    "    y = issueLifespan.Where(issue => issue.Key < 7).OrderBy(issue => issue.Key).Select(issue => issue.Value),\n",
    "    x = issueLifespan.Where(issue => issue.Key < 7).OrderBy(issue => issue.Key).Select(issue => issue.Key) ,\n",
    "    marker = new Marker{\n",
    "        color = \"green\"\n",
    "    }  \n",
    "};\n",
    "\n",
    "var issueLifespanOneSprintSeries = new Bar\n",
    "{\n",
    "    name = \"One Sprint old\",\n",
    "    y = issueLifespan.Where(issue => issue.Key >= 7 && issue.Key < 21).OrderBy(issue => issue.Key).Select(issue => issue.Value),\n",
    "    x = issueLifespan.Where(issue => issue.Key >= 7 && issue.Key < 21).OrderBy(issue => issue.Key).Select(issue => issue.Key) ,\n",
    "    marker = new Marker{\n",
    "        color = \"yellow\"\n",
    "    }  \n",
    "};\n",
    "\n",
    "var issueLifespanOldSeries = new Bar\n",
    "{\n",
    "    name = \"More then a Sprint\",\n",
    "    y = issueLifespan.Where(issue => issue.Key >= 21).OrderBy(issue => issue.Key).Select(issue => issue.Value),\n",
    "    x = issueLifespan.Where(issue => issue.Key >= 21).OrderBy(issue => issue.Key).Select(issue => issue.Key) ,\n",
    "    marker = new Marker{\n",
    "        color = \"red\"\n",
    "    }  \n",
    "};\n",
    "\n",
    "var issueLifespanChart = Chart.Plot(new[] {issueLifespanOnWeekSeries, issueLifespanOneSprintSeries, issueLifespanOldSeries});\n",
    "\n",
    "issueLifespanChart.WithLayout(new Layout.Layout\n",
    "            {\n",
    "                title = \"Bugs by life span\",\n",
    "                xaxis = new Xaxis {\n",
    "                    title = \"Number of days a bug stays open\",\n",
    "                    showgrid = false,\n",
    "                    zeroline = false\n",
    "                },\n",
    "                yaxis = new Yaxis {                \n",
    "                    showgrid = true,\n",
    "                    zeroline = false\n",
    "                }\n",
    "            });\n",
    "\n",
    "display(issueLifespanChart);\n",
    "\n",
    "var openIssuesAgeSeriesWeek = new Bar\n",
    "{\n",
    "    name = \"Closed in a week\",\n",
    "    y = openIssueAge.Where(issue => issue.Value < 7).OrderBy(issue => issue.Key).Select(issue => issue.Value),\n",
    "    x = openIssueAge.Where(issue => issue.Value < 7).OrderBy(issue => issue.Key).Select(issue => issue.Key) ,\n",
    "    marker = new Marker{\n",
    "        color = \"green\"\n",
    "    }  \n",
    "};\n",
    "\n",
    "var openIssuesAgeSeriesSprint = new Bar\n",
    "{\n",
    "    name = \"Closed within a sprint\",\n",
    "    y = openIssueAge.Where(issue => issue.Value >= 7 && issue.Value < 21).OrderBy(issue => issue.Key).Select(issue => issue.Value),\n",
    "    x = openIssueAge.Where(issue => issue.Value >= 7 && issue.Value < 21).OrderBy(issue => issue.Key).Select(issue => issue.Key) ,\n",
    "    marker = new Marker{\n",
    "        color = \"yellow\"\n",
    "    }  \n",
    "};\n",
    "\n",
    "var openIssuesAgeSeriesLong = new Bar\n",
    "{\n",
    "    name = \"Long standing\",\n",
    "    y = openIssueAge.Where(issue => issue.Value >= 21).OrderBy(issue => issue.Key).Select(issue => issue.Value),\n",
    "    x = openIssueAge.Where(issue => issue.Value >= 21).OrderBy(issue => issue.Key).Select(issue => issue.Key) ,\n",
    "    marker = new Marker{\n",
    "        color = \"red\"\n",
    "    }  \n",
    "};\n",
    "\n",
    "var openIssuesAgeChart = Chart.Plot(new[] {openIssuesAgeSeriesWeek, openIssuesAgeSeriesSprint, openIssuesAgeSeriesLong});\n",
    "openIssuesAgeChart.WithLayout(new Layout.Layout\n",
    "            {\n",
    "                title = \"Open bugs age\",\n",
    "                yaxis = new Yaxis {\n",
    "                    title = \"Number of days a bug stays open\",\n",
    "                    showgrid = true,\n",
    "                    zeroline = false\n",
    "                }\n",
    "            });\n",
    "display(openIssuesAgeChart);\n",
    "\n",
    "var createdThisMonthAreaSeries = new Pie {\n",
    "    values = issuesCreatedThisMonthByArea.Select(e => e.Value),\n",
    "    labels = issuesCreatedThisMonthByArea.Select(e => e.Key),\n",
    "};\n",
    "\n",
    "var createdArea = Chart.Plot(new[] {createdThisMonthAreaSeries});\n",
    "\n",
    "createdArea.WithLayout(new Layout.Layout\n",
    "            {\n",
    "                title = \"Bugs created this month by Area\",                \n",
    "            });\n",
    "\n",
    "display(createdArea);\n",
    "\n",
    "var openAreaSeries = new Pie {\n",
    "    values = openIssueByArea.Select(e => e.Value),\n",
    "    labels = openIssueByArea.Select(e => e.Key),\n",
    "};\n",
    "\n",
    "var openArea = Chart.Plot(new[] {openAreaSeries});\n",
    "\n",
    "openArea.WithLayout(new Layout.Layout\n",
    "            {\n",
    "                title = \"Open bugs by Area\",                \n",
    "            });\n",
    "\n",
    "display(openArea);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "var prColors = pullRequestLifespan.OrderBy(pr => pr.Key).Select(pr => pr.Key < 7 ? \"green\" : pr.Key < 21 ? \"yellow\" : \"red\");\n",
    "var prLifespanOneWeekSeries = new Bar\n",
    "{\n",
    "    name = \"One week\",\n",
    "    y = pullRequestLifespan.Where(issue => issue.Key < 7).OrderBy(pr => pr.Key).Select(pr => pr.Value),\n",
    "    x = pullRequestLifespan.Where(issue => issue.Key < 7).OrderBy(pr => pr.Key).Select(pr => pr.Key) ,\n",
    "    marker = new Marker{\n",
    "        color = \"green\"\n",
    "    }  \n",
    "};\n",
    "\n",
    "var prLifespanOneSprintSeries = new Bar\n",
    "{\n",
    "    name = \"One Sprint\",\n",
    "    y = pullRequestLifespan.Where(issue => issue.Key >= 7 && issue.Key < 21).OrderBy(pr => pr.Key).Select(pr => pr.Value),\n",
    "    x = pullRequestLifespan.Where(issue => issue.Key >= 7 && issue.Key < 21).OrderBy(pr => pr.Key).Select(pr => pr.Key) ,\n",
    "    marker = new Marker{\n",
    "        color = \"yellow\"\n",
    "    }  \n",
    "};\n",
    "\n",
    "var prLifespanMoreThanASprintSeries = new Bar\n",
    "{\n",
    "    name = \"More than a Sprint\",\n",
    "    y = pullRequestLifespan.Where(issue => issue.Key >= 21).OrderBy(pr => pr.Key).Select(pr => pr.Value),\n",
    "    x = pullRequestLifespan.Where(issue => issue.Key >= 21).OrderBy(pr => pr.Key).Select(pr => pr.Key) ,\n",
    "    marker = new Marker{\n",
    "        color = \"red\"\n",
    "    }  \n",
    "};\n",
    "\n",
    "var prLifespanChart = Chart.Plot(new[] {prLifespanOneWeekSeries, prLifespanOneSprintSeries, prLifespanMoreThanASprintSeries});\n",
    "prLifespanChart.WithLayout(new Layout.Layout\n",
    "            {\n",
    "                title = \"Pull Request by life span\",\n",
    "                xaxis = new Xaxis {\n",
    "                    title = \"Number of days a PR stays open\",\n",
    "                    showgrid = false,\n",
    "                    zeroline = false\n",
    "                },\n",
    "                yaxis = new Yaxis {\n",
    "                    title = \"Number of PR\",\n",
    "                    showgrid = true,\n",
    "                    zeroline = false\n",
    "                }\n",
    "            });\n",
    "display(prLifespanChart);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "var forkCreationSeries = new Scattergl\n",
    "{\n",
    "    name = \"created by month\",\n",
    "    y = forkCreatedByMonth.Select(g => g.Count() ).ToArray(),\n",
    "    x = forkCreatedByMonth.Select(g => g.Key ).ToArray()\n",
    "};\n",
    "\n",
    "var forkTotalSeries = new Scattergl\n",
    "{\n",
    "    name = \"running total\",\n",
    "    y = forkCountByMonth.Select(g => g.Count ).ToArray(),\n",
    "    x = forkCountByMonth.Select(g => g.Date ).ToArray()\n",
    "};\n",
    "\n",
    "var forkUpdateSeries = new Scattergl\n",
    "{\n",
    "    name = \"last update by month\",\n",
    "    y = forkUpdateByMonth.Select(g => g.Count ).ToArray(),\n",
    "    x = forkUpdateByMonth.Select(g => g.Date ).ToArray()\n",
    "};\n",
    "\n",
    "\n",
    "\n",
    "var chart = Chart.Plot(new[] {forkCreationSeries,forkTotalSeries,forkUpdateSeries});\n",
    "chart.WithTitle(\"Fork activity\");\n",
    "display(chart);"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".NET (C#)",
   "language": "C#",
   "name": ".net-csharp"
  },
  "language_info": {
   "file_extension": ".cs",
   "mimetype": "text/x-csharp",
   "name": "C#",
   "pygments_lexer": "csharp",
   "version": "8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}