Project and Time Reporting with Freedcamp and Power BI

There are many project management applications which you can use. Probably the best known is Microsoft Project, the very sophisticated business application which is used by big corporations with many ongoing projects and employees. However, Microsoft Project has so many features that a user needs tens of hours to manage it fully. This means that the app is too complicated (and also too expensive) for small business or personal projects. In addition, it is a desktop app and it cannot be used on mobile devices. Most users nowadays prefer web apps which do not need to be installed and updated on their devices.

To make things easier, many overviews of these apps have been written. I did some research on these articles two years ago to find the best application for my personal projects (like blogging, tutoring, language studying etc.). First I tried Trello. Despite being the most recommended application on the market, I had missed some features there (especially a time tracking) so I decided to delete my account and to try something else. My second choice was Freedcamp and I have been using this app since then. (Just a small note here: I do not want to say that Trello is a bad app, on the contrary, I admit that it has a very nice and intuitive user interface. It just is, like any other app, not ideal for everybody.)

This Spring, developers on Freedcamp have released a beta version of API which allows you to connect Freedcamp to another application. This brings a huge amount of new possibilities of using Freedcamp. In this article, I would like to show you how to use API to get data from Freedcamp and present them in another app. I have selected Power BI, a popular analytics app. Of course, you can select any front-end app you want (such as Tableau, Qlik, Microsoft Excel etc.)

To present data in a Power BI report, I have developed a simple .NET app. The app loads data from Freedcamp and stores them in an SQL database (hosted in Azure). You can see an example of one report in a picture below.

freedcamp-powerbi-1

There are two charts in the picture. The one on the top shows time spent on different projects this year (the columns show months and colours projects). The left down picture presents how many tasks of the projects have been closed this year. The table contains a list of recently finished tasks with an information if the task was closed in time or not.

And one more example. I created a customized Eisenhower matrix with three fields: the Do First field, the Schedule field and the In Case of Free Time. I also added the pie chart with a count of open tasks by project. As you can see, this article is one of two Do First tasks.

freedcamp-powerbi-2.PNG

As you can see, I use both tasks and time in my projects so I need to load projects, tasks and time records data.

Let’s look at the app. I will share the code of the app with a brief explanation.

Though I have experience with T SQL language, I used Entity Framework in the code (mostly to gain more experience with it). Entity Framework creates database structures (mostly tables, but indexes as well) and arranges data loading and saving as the application loads and saves objects instances. (In my opinion, Entity Framework allows extensive usage of LINQ and makes the code shorter and easier to read. On the other hand, it can make debugging much more challenging.)

Freedcamp uses an API key and an API secret to secure your data. If a user wants to have the API key with no expiration date, the API key with a current timestamp hashed with API secret has to be provided. The timestamp is generated by the GetTimestamp function and then encoded by Encode function. Both key and secret are loaded from the app.config file.

The most important function is the LoadFreedCampTasks function. It uses HttpClient to communicate with Freedcamp API. JSON messages are parsed by a very useful Json.NET library. First, the app loads project data. For each project, it checks if a project with a given ID exists. If not, the project is created. For existing projects, data fields (except IDs) are updated. The same process is repeated for tasks and projects. Date fields are parsed and if Data field is null, Date.MinValue or Date.MaxValue is used. All changes are saved to the database by SaveChanges function.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Entity;
using System.Data.SqlTypes;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Newtonsoft.Json.Linq;
using System.Security.Cryptography;
using System.Text;
using Microsoft.Exchange.WebServices.Data;
using PocketSharp;

namespace ProjectReportingTool
{
  class Program
  {
    private static string _freedcampAPIKey = String.Empty;
    private static string _freedcampAPISecret = String.Empty;
    private static readonly string _freedcampAPIurlGetProjects = "https://freedcamp.com/api/v1/projects/";
    private static readonly string _freedcampAPIurlGetTasks = "https://freedcamp.com/api/v1/tasks/";
    private static readonly string _freedcampAPIurlGetTime = "https://freedcamp.com/api/v1/times/";
    private static readonly int _bufferTimeLimit = 100;

    public class AzureDbContext : DbContext
    {
      public AzureDbContext() : base("name=AzureContext")
      {
      }
      public DbSet Projects { get; set; }
      public DbSet TimeRecords { get; set; }
      public DbSet Tasks { get; set; }
    }

    public static Int32 GetTimestamp()
    {
      return (Int32) DateTime.UtcNow.Subtract(new DateTime(1970, 1, 1)).TotalSeconds;
    }

    public static string Encode(string input, byte[] key)
    {
      HMACSHA1 myhmacsha1 = new HMACSHA1(key);
      byte[] byteArray = Encoding.ASCII.GetBytes(input);
      MemoryStream stream = new MemoryStream(byteArray);
      return myhmacsha1.ComputeHash(stream).Aggregate("", (s, e) => s + String.Format("{0:x2}", e), s => s);
    }

    public static string GetFreedcampSecurityPostfixes()
    {
      Int32 unixTimestamp = GetTimestamp();
      return "?api_key=" + _freedcampAPIKey + "&timestamp=" + unixTimestamp + "&hash=" + Encode((_freedcampAPIKey + unixTimestamp), Encoding.UTF8.GetBytes(_freedcampAPISecret));
    }

    public static DateTime UnixTimeStampToDateTime(double unixTimeStamp)
    {
      System.DateTime dtDateTime = new DateTime(1970, 1, 1, 0, 0, 0, 0, System.DateTimeKind.Utc);
      dtDateTime = dtDateTime.AddSeconds(unixTimeStamp).ToLocalTime();
      return dtDateTime;
    }

    public static async Task LoadFreedCampTasks()
    {
      HttpClient client = new HttpClient();
      using (var db = new AzureDbContext())
      {
        string freedcampURL = _freedcampAPIurlGetProjects;
        freedcampURL += GetFreedcampSecurityPostfixes();

        var responseProjects =
          await
            client.GetAsync(
              freedcampURL);
        var projectsResponseString = await responseProjects.Content.ReadAsStringAsync();
        JObject projectJson = JObject.Parse(projectsResponseString);
        JArray projects = (JArray)projectJson["data"]["projects"];
        foreach (JObject jsonProject in projects)
        {
          int freedcampProjectID = (int)jsonProject["project_id"];
          string projectName = (string)jsonProject["project_name"];
          Project project = db.Projects.FirstOrDefault(x => x.FreedcampProjectID == freedcampProjectID);
          if (project is null)
          {
            project = new Project();
            db.Projects.Add(project);
            project.FreedcampProjectID = freedcampProjectID;
          }
          project.ProjectName = projectName;
        }
        db.SaveChanges();

        freedcampURL = _freedcampAPIurlGetTasks;
        freedcampURL += GetFreedcampSecurityPostfixes();

        var responseTasks =
          await
            client.GetAsync(
              freedcampURL);
        var tasksResponseString = await responseTasks.Content.ReadAsStringAsync();
        JObject tasksJson = JObject.Parse(tasksResponseString);
        JArray tasks = (JArray)tasksJson["data"]["tasks"];
        List tasksIDs = new List();
        foreach (JObject jsonTask in tasks)
        {
          int freedcampTaskID = (int)jsonTask["id"];
          tasksIDs.Add(freedcampTaskID);
          string taskName = (string)jsonTask["title"];
          int freedcampProjectID = (int)jsonTask["project_id"];
          Task task = db.Tasks.FirstOrDefault(x => x.FreedcampTaskId == freedcampTaskID);
          if (task is null)
          {
            task = new Task();
            db.Tasks.Add(task);
            task.FreedcampTaskId = freedcampTaskID;
            task.Inactive = false;
          }
          task.TaskName = taskName;
          Project taskProject = db.Projects.FirstOrDefault(x => x.FreedcampProjectID == freedcampProjectID);
          task.Project = taskProject;
          task.Level = (int)jsonTask["h_level"];
          task.ImportedOn = DateTime.Now;
          task.ModifiedOn = DateTime.Now;

          int tsDateCreated = (int)jsonTask["created_ts"];
          task.DateCreated = UnixTimeStampToDateTime(tsDateCreated);
          task.DueDate = jsonTask["due_ts"].Type == JTokenType.Null ? (DateTime)SqlDateTime.MaxValue : UnixTimeStampToDateTime((int)jsonTask["due_ts"]);
          task.DateCompleted = jsonTask["completed_ts"].Type == JTokenType.Null ? (DateTime)SqlDateTime.MaxValue : UnixTimeStampToDateTime((int)jsonTask["completed_ts"]);
          task.Priority = (int)jsonTask["priority"];
          task.Status = (int)jsonTask["status"];
        }
        db.SaveChanges();
        db.Tasks.Where(x => !tasksIDs.Contains(x.FreedcampTaskId)).ToList().ForEach(x => x.Inactive = true);
        db.SaveChanges();

        foreach (JObject jsonTask in tasks)
        {
          int freedcampTaskID = (int)jsonTask["id"];
          int parentTaskID = ((string)jsonTask["h_parent_id"]).Length == 0 ? Int32.MinValue : (int)jsonTask["h_parent_id"];
          if (parentTaskID != Int32.MinValue)
          {
            Task task = db.Tasks.FirstOrDefault(x => x.FreedcampTaskId == freedcampTaskID);
            Task parentTask = db.Tasks.FirstOrDefault(x => x.FreedcampTaskId == parentTaskID);
            task.ParentTask = parentTask;
          }
        }
        db.SaveChanges();

        int offset = 0;
        bool hasMore = true;
        freedcampURL = _freedcampAPIurlGetTime;
        freedcampURL += GetFreedcampSecurityPostfixes();
        string pat = @"\d{7,12}";
        Regex r = new Regex(pat, RegexOptions.IgnoreCase);
        while (hasMore == true)
        {
          freedcampURL += "&offset=" + offset + " &limit=" + _bufferTimeLimit;
          var responseTime =
            await
              client.GetAsync(
                freedcampURL);
          var timeResponseString = await responseTime.Content.ReadAsStringAsync();
          JObject timeJson = JObject.Parse(timeResponseString);
          JArray timeRecords = (JArray)timeJson["data"]["times"];

          foreach (JObject jsonTimeRecord in timeRecords)
          {
            int freedcampTimeRecordID = (int)jsonTimeRecord["id"];
            TimeRecord timeRecord = db.TimeRecords.FirstOrDefault(x => x.FreedcampTimeRecordId == freedcampTimeRecordID);
            if (timeRecord == null)
            {
              timeRecord = new TimeRecord();
              timeRecord.FreedcampTimeRecordId = freedcampTimeRecordID;
              db.TimeRecords.Add(timeRecord);
            }
            timeRecord.MinutesWorked = (int)jsonTimeRecord["minutes_count"];
            timeRecord.Date = jsonTimeRecord["date_ts"].Type == JTokenType.Null ? (DateTime)SqlDateTime.MinValue : UnixTimeStampToDateTime((int)jsonTimeRecord["date_ts"]);
            string description = jsonTimeRecord["date_ts"].Type == JTokenType.Null ? "" : (string)jsonTimeRecord["description"];
            Match m = r.Match(description);
            if (m.Success)
            {
              int freedcampTakId = 0;
              Int32.TryParse(m.Value, out freedcampTakId);
              timeRecord.Task = db.Tasks.FirstOrDefault(x => x.FreedcampTaskId == freedcampTakId);
            }
            int freedcampProjectID = (int)jsonTimeRecord["project_id"];
            timeRecord.Project = db.Projects.FirstOrDefault(x => x.FreedcampProjectID == freedcampProjectID);
          }
          db.SaveChanges();

          offset += _bufferTimeLimit;
          hasMore = (int)timeJson["data"]["meta"]["total_count"] > offset;
        }
      }

      return 0;
    }

    static void Main(string[] args)
    {
      var appSetting = ConfigurationManager.AppSettings;
      var connectionStrings = ConfigurationManager.ConnectionStrings;
      _freedcampAPIKey = appSetting["FreedcampAPIKey"];
      _freedcampAPISecret = appSetting["FreedcampAPISecret"];

      LoadFreedCampTasks().GetAwaiter().GetResult();
    }
  }
}

To make the code complete, I will add codes for data clases. The first one is the Project class.


using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;

namespace ProjectReportingTool
{
  class Project
  {
    public int ProjectId { get; set; }
    public string ProjectName { get; set; }
    [Index("IX_FreedcampFreedProjectFreedID", IsUnique = true)]
    public int FreedcampProjectID { get; set; }
    public virtual List Tasks { get; set; }
    public virtual List TimeRecords { get; set; }
  }
}

The second one is the Task class.


using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ProjectReportingTool
{
  class Task
  {
    public DateTime DueDate { get; set; }
    public DateTime DateCreated { get; set; }
    public DateTime DateCompleted { get; set; }
    public string TaskName { get; set; }
    public int Status { get; set; }
    public int Priority { get; set; }
    public virtual Project Project { get; set; }
    public virtual Task ParentTask { get; set; }
    public virtual List ChildTasks { get; set; }
    public virtual List TimeRecords { get; set; }
    public int Level { get; set; }
    public int TaskId { get; set; }
    [Index("IX_FreedcampFreedTaskId", IsUnique = true)]
    public int FreedcampTaskId { get; set; }
    public DateTime ImportedOn { get; set; }
    public DateTime ModifiedOn { get; set; }
    public bool Inactive { get; set; }
  }
}

And the last one it the TimeRecord class.


using System;
using System.ComponentModel.DataAnnotations.Schema;

namespace ProjectReportingTool
{
  class TimeRecord
  {
    public int TimeRecordId { get; set; }
    [Index("IX_FreedcampFreedcampTaskRecordID", IsUnique = true)]
    public int FreedcampTimeRecordId { get; set; }
    public int MinutesWorked { get; set; }
    public virtual Task Task { get; set; }
    public DateTime Date { get; set; }
    public string Description { get; set; }
    public virtual Project Project { get; set; }
  }
}

 

Leave a Reply

Name and email address are required. Your email address will not be published.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

You may use these HTML tags and attributes:

<a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <pre> <q cite=""> <s> <strike> <strong> 

%d bloggers like this: