SQL temporal tables to collect invoice data for customers

Projcct Hours Time Tracking has been online now for over a year and it is time to send out the first invoices to customers. I realized that in order to correctly calculate the amounts to charge for users I will need historical data, i.e. when did users start or when where they set to inactive.

To record historical data I decided to use SQL temporal tables. I could have used database triggers to record table changes but after reading up on SQL temporal tables I decided to give it a try. Here is a good link to learn about this feature: SQL Server Temporal Tables Recipes .

One thing I like very much about temporal tables is that there are special queries that make querying on time intervals much easier, for instance you can do a query like

SELECT email, SysStartTime, SysEndTime FROM AspNetusers
FOR SYSTEM_TIME FROM @StartDate TO @EndDate
WHERE Active = 1 

This will retrieve all records with changes in a certain time period. Writing this yourself in SQL is more complicated. A good article explaining how to query temporal tables: How to query data on versioned temporal tables in SQL

This is what tables with history look like in SQL Server Management Studio:

That's it for now,
Bert-Jan

ProjectHours needs online marketing

ProjectHours has been online for a few years. It is reliable and there are some happy customers. Now the time has come to pay attention to expanding the customer base. I think the best way would be to look for cooperation with people that have experience with online marketing and support. My knowledge is primarily technical and I think it will be benficial for ProjectHours if I start working with someone with online marketing expertise. I have posted this message on LinkedIn as well. Let's find out if I can find some interesting people to work with!

Updating Windows 10 UWP app, added a privacy policy

I am adding the time feature to the Project Hours Windows 10 app. All is going well but I realized a really had to create a privacy policy because time management data is confidential information. That is why Project Hours has a privacy policy now! It is very basic, but it is a start..

Project Hours available on Google worldwide!

Multi-language site

So far, Project Hours was only available in the Netherlands. The English language was supported, but in a way that Google did not like, and therefore there was no real presence in the rest of the world.

I finally managed to sort out this issue. The most important fact is that a multilanguage site only works well in Google if the url contains the language of the site (i.e.  https://projecthours.net/nl)  and the web pages must contain certain tags that indicate in what (other) languages the page is available.

Project Hours now works as follows:

• https://projecthours.net :   this links determines the language of the browser and redirects to the appropriate language specific url (see below)
• https://projecthours.net/nl:  this link displays Dutch UI and content 
• https://projecthours.net/en: this link displays English UI and content

Simple, but it took me a while to get it right. The pages must contain the following tags so Google knows what the language scheme of the site is so the appropriate language versions of the site can be returned in Google search results.

The following tags are included in every page for every language:

Visibility on the Internet

To attract more potential customers, I created Google advertisements for each language with Google Adwords Express. The ads link to the appropriate language url (/en or /nl).  This is important, ads will never work with generic urls that display either Dutch or English! The ads are displayed according to regional language, so in Engish-speaking countries people will see the English ad, NL users see the Dutch ad of course.

Mind you, this is not for free, makes one realize how powerful Google is.. You pay and you are visible on the web, or you do not pay and it is very difficult to be visible..

With my Google account I can check user statistics for these ads.
This sunday morning, there was one click on the Dutch ad and 80 (!) clicks on the English ad.

Also note that all clicks come from smartphones!
That's all for now, hopefully Project Hours will have some international customers soon!
Bert-Jan

Projects and Activities sort order Added

On request of a customer  it is now possible in Project Hours to set the sort order voor project lists and activity lists that are displayed on web pages and in phone apps. This is configurable for each organisation:

Options are: Unsorted (order as in db), Ascending or Descending.

That's all for now,
Bert-Jan

Mobile app development, challenges..

The past weeks I have worked on new versions of the ProjectHours mobile apps (new Timer page and multilanguage support).

Updating the Android app went well. The new version is available in the Google Play Store and I am happy with it. Windows app will follow, had one issue where the tap gesture (touch) did not work immediately.

The iPhone/iPad app is more of a challenge. There were some setbacks:
- The Xamarin forms controls are not so easy to manipulate for some reason in iOS so it is difficult to build an acceptable UI in a short span of time.

- I had problems with provisioning certificates that are necessary to deploy the app. It works fine now, but took a lot of time to figure out how provisioning of apps works.

- There was a compiler issue with linking the code, it turned out that if you choose the option 'link all assemblies' some code (default constructors) is removed in an attempt to optimize, but it breaks the app. Unfortunately this did not occur immediately in the virtual environment that I use to build and try out the app, it only happens on physical devices when deployed via the Apple Test Flight beta program. Took a long time to figure out.

But finally I got the iPhone app working. There is a Beta version available online.. click the link to try it out if you want. After a little bit of testing I think the apps functionality works ok but the UI is horrible, especially the Timer page:

More work to do..
Bert-Jan

Beta version Android App Update 2 now available

The timer feature has been added to the project hours mobile apps. The beta version of the android app update for Project Hours is available on: https://play.google.com/apps/testing/com.projecthours.projecthours Try out the app, and especially the new timer feature and send us you valuable feedback!

New features:
- Multiple language support (English and Dutch)
- Timer feature added

Regards,
Bert-Jan

Working on new mobile features

The timer feature is available on the Project Hours web site and it must be added to the project hours mobile apps. At the same time I want to make the mobile apps multilingual. And finally, for the next release I want to add user license info and a privacy policy.

The multilingual feature is finished, most time went into sorting out how to deal with localization for Windows Universal Apps, Microsoft renamed the resource file extension for UWP apps from .RESX to RESW. In itself not a problem but I want to keep all translations for the three platforms in one place and Xamarin still uses RESX for iOS and Android. Eventually I ended up with a pre build batch file that copies the RESX to a RESW file before building the UWP application.
More info: Universal Windows App Localization


Before I can start building the timer feature in Xamarin, I will have to extend the REST interface that is used by the mobile apps so that it supports creating, pausing, stopping and saving timers. Currently I am working on the REST interface. I realized that mobile apps with timers will generate a lot of REST service calls, for instance to get progress on running timers, so the first thing I did was adding some caching to store data so calls to the SQL database are minimized. Usernames, passwords and active timers will be cached for a couple of hours in order to speed things up. I am using the good old System.Web.Caching.Cache that has been around for years in .NET

To be continued...

Fixed issue with cached content

In past week two users reported issues that time entries from other users were displayed in their overviews! Not good..  I looked into the matter and I think this is because some JSON responses were being cached and displayed to several users while the content in those JSON responses was only meant for one person. To disable caching of JSON responses on the IIS Server, I added the following attribute on each controller that has JSON actions:

[OutputCache(NoStore = true, Duration = 0)]

Perhaps a bit of overkill to specify this for all actions on a controller, but it is important that cached content is never served to the wrong user..

More about ASP.NET MVC caching in these links:

http://dejanstojanovic.net/aspnet/2015/april/microsoft-iis-and-aspnet-mvc-caching-techniques/

http://stackoverflow.com/questions/10011780/prevent-caching-in-asp-net-mvc-for-specific-actions-using-an-attribute

Bert-Jan

Timer feature available for beta testing

I published the new timer feature to the Project Hours Test Site for beta testing (use the Demo login to try it out.)

The timer runs on the server, the timer web page has buttons to start, pause, resume, cancel and stop and save time. If all goes wel the feature will be available on Project Hours by the end of next week. I am working on some localization issues as you can see in the screen shot below.

Bert-Jan

Building a timer feature for Project Hours

I am back from a relaxing skiing holiday in France, time to continue improving Project Hours! On request I am adding a timer feature. Users will be able to start and stop a timer to track time spent on projects and activities. The timer will run on the server, no need to keep the Project Hours program or web site open while the timer runs. The timer feature will be  available on the web site  first, mobile apps will follow.To be continued.. Bert-Jan