Retrieving statistics about URL clicks

Question

I'm pretty new to ASP/MVC but have had some prior programming experience.

I am trying to retrieve statistics about URL clicks - total clicks and unique clicks by IP address. I started with:

ViewBag.ClicksToday = context.EmailLinkClicks
     .Where(c => c.CreatedOn == DateTime.Today).Count();

ViewBag.ClicksWeek = context.EmailLinkClicks
     .Where(c => c.CreatedOn > System.Data.Entity.DbFunctions.AddDays(c.CreatedOn, -7)).Count();

ViewBag.ClicksMonth = context.EmailLinkClicks
     .Where(c => c.CreatedOn.Month == DateTime.Today.Month).Count();

ViewBag.UniqueClicksToday = context.EmailLinkClicks.Where(c => c.CreatedOn == DateTime.Today)
     .Select(c => c.IPAddress).Distinct().Count();

ViewBag.UniqueClicksWeek = context.EmailLinkClicks.Where(c => c.CreatedOn > System.Data.Entity.DbFunctions.AddDays(c.CreatedOn, -7))
     .Select(c => c.IPAddress).Distinct().Count();

ViewBag.UniqueClicksMonth = context.EmailLinkClicks.Where(c => c.CreatedOn.Month == DateTime.Today.Month)
     .Select(c => c.IPAddress).Distinct().Count();

Then I thought why not make it a function:

private static int GetClicks(AppContext context, string period, bool unique)
{
    int Clicks = 0;
    switch (period)
    {
        case "today":
            var ClicksToday = context.EmailLinkClicks
                .Where(c => c.CreatedOn == DateTime.Today);
            Clicks = unique ? ClicksToday.Select(c => c.IPAddress).Distinct().Count()
                : ClicksToday.Count();
            break;
        case "week":
            var ClicksWeek = context.EmailLinkClicks
                .Where(c => c.CreatedOn > System.Data.Entity.DbFunctions.AddDays(c.CreatedOn, -7));
            Clicks = unique ? ClicksWeek.Select(c => c.IPAddress).Distinct().Count()
                : ClicksWeek.Count();
            break;
        case "month":
            var ClicksMonth = context.EmailLinkClicks
                .Where(c => c.CreatedOn.Month == DateTime.Today.Month);
            Clicks = unique ? ClicksMonth.Select(c => c.IPAddress).Distinct().Count()
                : ClicksMonth.Count();
            break;
    }
    return Clicks;
}

But I still have to call quite a lot of code:

ViewBag.ClicksToday = GetClicks(context, "today", false);
ViewBag.ClicksWeek = GetClicks(context, "week", false);
ViewBag.ClicksMonth = GetClicks(context, "month", false);

ViewBag.UniqueClicksToday = GetClicks(context, "today", true);
ViewBag.UniqueClicksWeek = GetClicks(context, "week", true);
ViewBag.UniqueClicksMonth = GetClicks(context, "month", true);

My questions:

1. Is there any way to further simplify/reduce this code?

   • perhaps have the total & unique in one value? [total],[unique]

2. Is it poor practice to pass period as a string and then interrogate it?

3. Where should you store these kind of database queries? It currently resides in my controller and then returned to the view.

Answer 1

Here's the first thing I came up with. Wrote it in Notepad, so it might not compile exactly as is.

private static int GetClicks(AppContext context, Period period, bool unique)
{
    int Clicks = 0;
    switch (period)
    {
        var query = context.EmailLinkClicks.AsQueryable();
        case Period.Today:
            query = query.Where(c => c.CreatedOn == DateTime.Today);
            break;
        case Period.Week:
            query = query.Where(c => c.CreatedOn > System.Data.Entity.DbFunctions.AddDays(c.CreatedOn, -7));
            break;
        case Period.Month:
            query = query.Where(c => c.CreatedOn.Month == DateTime.Today.Month);
            break;
    }

    var ipAddresses = query.Select(c => c.IpAddress);

    if (unique)
    {
        query = query.Distinct();
    }

    return query.Count();
}

enum Period 
{
    Today,
    Week,
    Month
}

Update: As to where to store these queries... Doing it in your controller isn't the worst thing in the world. There are a ton of different places you could put that code though. The Repository pattern seems to be popular. I'm not strict about that kind of thing though.

Update: if you wanted to get both the total and unique stats in the same call, you could do something like this: https://gist.github.com/alexdresko/8059762/bcf233b86869042c4b6f360f6b461999b2bf4899

Update: But what you probably want, assuming you want to create some kind of report, is this: https://gist.github.com/alexdresko/8059762/c4afa948f020e8395bb27d565269c914aad926a7

That one will allow you to simply iterate over the collections of stats in your view, display all relevant information.