Professional Time Cockpit Scripting with PTVS

Thursday, March 30, 2017 by Rainer Stropek

Introduction

Extensibility is an important feature of time cockpit. In contrast to many of our competitors, we enable you to tailor time cockpit to your needs. Time cockpit’s extensibility features include amongst other things…

  • …extending time cockpit’s data model by adding tables, persisted and calculated properties, relations, validation rules, permission rules, etc.
  • …extending time cockpit’s UI by adding custom lists, forms, reports, etc.
  • …embed time cockpit in a Microservice landscape by using time cockpit RESTful Web API.
  • …add advanced custom logic using Python scripting.

In this blog post, I want to focus on the last point. Python scripts are just text, so you can theoretically develop them in any text editor. However, when logic gets more complex, you need a more advanced development environment supporting syntax coloring, IntelliSense, debugging, unit testing, etc. Microsoft offers such an extension to Visual Studio: Python Tools for Visual Studio (PTVS). It is free and it works with Microsoft’s free Visual Studio Community Edition. So, let us see how you can use PTVS to professionalize you Python scripting in time cockpit.

Getting Started

Here is what you need to get started:

Note that at the time of writing, PTVS was not supported for Microsoft’s brand new 2017 version of Visual Studio. It was included in the Release Candidate of VS2017 gut isn’t available for the RTM version. If you read this article in a few weeks or months, you will probably be able to use VS2017, too.

Also, note that time cockpit uses IronPython (mentioned above) because of its great integration with .NET. After installing the requirements mentioned above, you can make IronPython your default environment for new projects. This can be done in the Python Environments window in Visual Studio.

You can also change the environment on a per-project basis:

Time Cockpit Script Sources

With time cockpit, you have two options for working with Python scripts:

  •         Write and run stand-alone scripts to automate e.g. administrative tasks
  •         Embed scripts in time cockpit’s data model (e.g. as a data source for a list, as the code for an action associated with a button in the UI, etc.)

In this article, I focus on the second option. I want to demonstrate how we can use PTVS to write, debug and automatically test a time cockpit list with a Python script as its data source (aka “Python list”). An example for a Python list in time cockpit’s standard data model is APP_DefaultVacationList. The following image shows how you can inspect this list’s Python source:

Note that you can automate updating your time cockpit’s data model based on stand-alone Python files (e.g. maintained in a source code control system like Git). This is out of scope of this article. Please contact us if you want to know more about that.

Creating the Project

Now we are ready to create our project in PTVS:

PTVS will create a single file with a print command. We can replace it with the code of our test list.

Please note that this blog article uses a slightly refactored version of the list APP_DefaultVacationList.

In the data model that we ship, the list looks a little bit different. Please use the list source as shown here if you experiment with PTVS as this version makes it easier to demonstrate automated testing.

import clr
clr.AddReference("System.Core")
import System
clr.ImportExtensions(System.Linq)

def getResultModelEntity(context):
	clr.AddReference("TimeCockpit.Data")
	from TimeCockpit.Data.DataModel import *
	entity = ModelEntity({ "Name": "Result" })
	entity.Properties.Add(GuidProperty({ "Name": "VacationUuid" }))
	entity.Properties.Add(GuidProperty({ "Name": "UserDetailUuid" }))
	entity.Properties.Add(TextProperty({ "Name": "UserDetailName" }))
	entity.Properties.Add(BooleanProperty({ "Name": "Consumption" }))
	entity.Properties.Add(DateTimeProperty({ "Name": "BeginTime" }))
	entity.Properties.Add(DateTimeProperty({ "Name": "EndTime" }))
	entity.Properties.Add(DateProperty({ "Name": "VacationEntitlementDate" }))
	entity.Properties.Add(TextProperty({ "Name": "Description" }))
	entity.Properties.Add(BooleanProperty({ "Name": "SpecialLeave" }))
	entity.Properties.Add(NumericProperty({ "Name": "NumberOfWeeks", "Precision": 10, "Scale": 2 }))
	entity.Properties.Add(NumericProperty({ "Name": "RemainingWeeks", "Precision": 10, "Scale": 2 }))
	entity.Properties.Add(BooleanProperty({ "Name": "IsWholeDay" }))
	return entity

def getTimeCockpitVersion():
	clr.AddReference("TimeCockpit.Programmability")
	from TimeCockpit.Programmability.Functions import Constants
	from System import Version, String
	return clr.GetClrType(Constants).Assembly.GetName().Version

def isRecent(version):
	from System import Version
	return (version >= Version(1, 18, 0, 0) or version == Version(0, 0, 0, 0))

def getVacationQuery(isRecent):
	vacationDurationCall = \
		':VacationDurationInWeeks2(Current.VacationUuid)' if isRecent \
		else ':VacationDurationInWeeks(V.APP_VacationUuid)'
	remainingVacationCall = \
		':RemainingVacationWeeks2(Current.UserDetailUuid, Current.EndTime)' if isRecent \
		else ':RemainingVacationWeeks(V.APP_UserDetail.APP_UserDetailUuid, V.APP_EndTime)'
	return '''From V In APP_Vacation Where V.APP_UserDetail.APP_UserDetailUuid = @UserDetail Select New With 
		{{ 
			.VacationUuid = V.APP_VacationUuid, 
			.UserDetailUuid = V.APP_UserDetail.APP_UserDetailUuid, 
			.UserDetailName = :DisplayValue(V.APP_UserDetail), 
			.Consumption =  True, 
			.BeginTime = V.APP_BeginTime, 
			.EndTime = V.APP_EndTime, 
			.VacationEntitlementDate = :AddHours(#1970-01-01#, 0), 
			.Description = V.APP_Description, 
			.SpecialLeave = V.APP_SpecialLeave, 
			.NumberOfWeeks = {} * -1, 
			.RemainingWeeks = {}, 
			.IsWholeDay = V.APP_IsWholeDay 
		}}'''.format(vacationDurationCall, remainingVacationCall)

def getEntitlementQuery(isRecent):
	remainingVacationAtEntitlementCall = \
		':RemainingVacationWeeks2(Current.UserDetailUuid, Current.VacationEntitlementDate)' if isRecent \
		else ':RemainingVacationWeeks(V.APP_UserDetail.APP_UserDetailUuid, V.APP_VacationEntitlementDate)'
	return '''From V In APP_VacationEntitlement Where V.APP_UserDetail.APP_UserDetailUuid = @UserDetail Select New With 
		{{ 
			.VacationUuid = {{00000000-0000-0000-0000-000000000000}}, 
			.UserDetailUuid = V.APP_UserDetail.APP_UserDetailUuid, 
			.UserDetailName = :DisplayValue(V.APP_UserDetail), 
			.Consumption =  False, 
			.BeginTime = :AddHours(V.APP_VacationEntitlementDate, 0), 
			.EndTime = :AddHours(V.APP_VacationEntitlementDate, 0), 
			.VacationEntitlementDate = :AddHours(V.APP_VacationEntitlementDate, 0), 
			.Description = :Translate('List.APP_DefaultVacationList.VacationEntitlement'), 
			.SpecialLeave = False, 
			.NumberOfWeeks = V.NumberOfWeeks, 
			.RemainingWeeks = {0}, 
			.IsWholeDay = True 
		}}'''.format(remainingVacationAtEntitlementCall)

def getEmptyVacationQuery():
	return '''From U In APP_UserDetail Where U.APP_UserDetailUuid = @UserDetail Select New With 
		{ 
			.VacationUuid = {00000000-0000-0000-0000-000000000000}, 
			.UserDetailUuid = U.APP_UserDetailUuid, 
			.UserDetailName = :DisplayValue(U), 
			.Consumption =  False, 
			.BeginTime = :Iif(1 = 0, U.APP_DateOfJoining, Null), 
			.EndTime = :Iif(1 = 0, U.APP_DateOfJoining, Null), 
			.VacationEntitlementDate = :AddHours(#1970-01-01#, 0), 
			.Description = :Translate('List.APP_DefaultVacationList.NoEntries'), 
			.SpecialLeave = False, 
			.NumberOfWeeks = 0, 
			.RemainingWeeks = 0, 
			.IsWholeDay = True 
		}''';

def getItems(context, queryParameters):
	from TimeCockpit.Data import EntityObject
	recent = isRecent(getTimeCockpitVersion())
	vacation = context.SelectWithParams({ "Query": getVacationQuery(recent), "QueryParameters": queryParameters })
	vacationEntitlement = context.SelectWithParams({ "Query": getEntitlementQuery(recent), "QueryParameters": queryParameters })
			
	if vacation.Count == 0 and vacationEntitlement.Count == 0:
		vacationEntitlement = context.SelectWithParams({ "Query": getEmptyVacationQuery(), "QueryParameters": queryParameters })
	
	return vacation \
		.Concat(vacationEntitlement) \
		.OrderBy(lambda t: t.EndTime) \
		.Cast[EntityObject]()

Our goals are:

  •         Debugging the list’s code including breakpoints and watch expressions
  •         Implement automatic unit tests for the list’s script code

Debugging

Let’s add a new Python file main.py to our project with the following code:

# Add a reference to time cockpit's data API
import clr
clr.AddReferenceToFileAndPath(R"C:\Program Files\software architects\time cockpit\time cockpit 2010\TimeCockpit.Data")

# Import list code as a python module
import PtvsTimeCockpitList

me = PtvsTimeCockpitList.getResultModelEntity(None)
print "Created ModelEntity with name {}".format(me.Name)

Note that you get IntelliSense while typing your Python code.

Don’t forget to make main.py your startup file. Once you have entered the code shown above, you should be able to run your program by hitting Ctrl + F5 (run without debugger). It should print Created ModelEntity with name USR_Result.

Now let’s try debugging. Set a breakpoint at the end of the list’s method getResultModelEntity as shown in the next screenshot. Hit F5 to start debugging. Visual Studio will stop at your breakpoint and you can inspect variable values:

Automated Tests

Now let’s write some automated tests. Add a new Python file list.py to your project and make it the startup file. We can use Python’s unittest Unit Test Framework.

import clr
clr.AddReference("System.Core")
import System
clr.ImportExtensions(System.Linq)
clr.AddReferenceToFileAndPath(R"C:\Program Files\software architects\time cockpit\time cockpit 2010\TimeCockpit.Data")
from TimeCockpit.Data.DataModel import ModelEntity
from TimeCockpit.Data import EntityObject
from System import Version, DateTime
from System.Collections.Generic import List

import unittest
import PtvsTimeCockpitList

def createVacation(me, description, endTime):
	"""Helper function for creating test EntityObjects"""
	newItem = me.CreateEntityObject()
	newItem.Description = description
	newItem.EndTime = endTime
	return newItem

class TestListMethods(unittest.TestCase):

	def test_model_entity(self):
		"""Verifies structure of generated ModelEntity"""
		me = PtvsTimeCockpitList.getResultModelEntity(None)
		self.assertIsNotNone(me)
		self.assertEqual(me.Name, "USR_Result")
		self.assertGreater(me.Properties.Count, 0)

	def test_get_tc_version(self):
		"""Verifies that tc version can be found out"""
		version = PtvsTimeCockpitList.getTimeCockpitVersion()
		self.assertIsNotNone(version)
		self.assertEqual(version.Major, 1)

	def test_is_recent(self):
		"""Verifies check whether tc version is a recent version"""
		self.assertTrue(PtvsTimeCockpitList.isRecent(Version(0, 0, 0, 0)))
		self.assertTrue(PtvsTimeCockpitList.isRecent(Version(1, 18, 0, 0)))
		self.assertFalse(PtvsTimeCockpitList.isRecent(Version(1, 17, 0, 0)))

	def test_vacation_query(self):
		"""Verifies that vacation query is generated correctly depending on tc version"""
		self.assertTrue(PtvsTimeCockpitList.getVacationQuery(True).Contains(":VacationDurationInWeeks2("))
		self.assertTrue(PtvsTimeCockpitList.getVacationQuery(True).Contains(":RemainingVacationWeeks2("))
		self.assertFalse(PtvsTimeCockpitList.getVacationQuery(True).Contains(":VacationDurationInWeeks("))
		self.assertFalse(PtvsTimeCockpitList.getVacationQuery(True).Contains(":RemainingVacationWeeks("))

		self.assertFalse(PtvsTimeCockpitList.getVacationQuery(False).Contains(":VacationDurationInWeeks2("))
		self.assertFalse(PtvsTimeCockpitList.getVacationQuery(False).Contains(":RemainingVacationWeeks2("))
		self.assertTrue(PtvsTimeCockpitList.getVacationQuery(False).Contains(":VacationDurationInWeeks("))
		self.assertTrue(PtvsTimeCockpitList.getVacationQuery(False).Contains(":RemainingVacationWeeks("))

	def test_entitlement_query(self):
		"""Verifies that entitlement query is generated correctly depending on tc version"""
		self.assertTrue(PtvsTimeCockpitList.getEntitlementQuery(True).Contains(":RemainingVacationWeeks2("))
		self.assertFalse(PtvsTimeCockpitList.getEntitlementQuery(True).Contains(":RemainingVacationWeeks("))

		self.assertFalse(PtvsTimeCockpitList.getEntitlementQuery(False).Contains(":RemainingVacationWeeks2("))
		self.assertTrue(PtvsTimeCockpitList.getEntitlementQuery(False).Contains(":RemainingVacationWeeks("))

	def test_vacation_entitlement_merge(self):
		"""Verifies that vacation and entitlement data is merged properly"""
		# Create a mockup class for time cockpit's DataContext
		class dc:
			def SelectWithParams(self, queryParameters):
				me = PtvsTimeCockpitList.getResultModelEntity(None)
				result = List[EntityObject]()
				if (queryParameters["Query"].Contains("From V In APP_Vacation ")):
					result.Add(createVacation(me, "V1", DateTime(2017, 2, 1)))
					result.Add(createVacation(me, "V2", DateTime(2017, 4, 1)))
				elif (queryParameters["Query"].Contains("From V In APP_VacationEntitlement ")):
					result.Add(createVacation(me, "VE1", DateTime(2017, 1, 1)))
					result.Add(createVacation(me, "VE2", DateTime(2017, 3, 1)))
				return result
		result = PtvsTimeCockpitList.getItems(dc(), None).ToArray()
		self.assertIsNotNone(result)
		self.assertEqual(result.Count, 4)
		self.assertEqual(result[0].Description, "VE1")
		self.assertEqual(result[1].Description, "V1")
		self.assertEqual(result[2].Description, "VE2")
		self.assertEqual(result[3].Description, "V2")

	def test_empty_result(self):
		"""Verifies that record for empty result is created properly"""
		class dc:
			def SelectWithParams(self, queryParameters):
				me = PtvsTimeCockpitList.getResultModelEntity(None)
				result = List[EntityObject]()
				if (queryParameters["Query"].Contains("From U In APP_UserDetail ")):
					result.Add(createVacation(me, "Dummy", DateTime(2017, 1, 1)))
				return result
		result = PtvsTimeCockpitList.getItems(dc(), None).ToArray()
		self.assertIsNotNone(result)
		self.assertEqual(result.Count, 1)
		self.assertEqual(result[0].Description, "Dummy")

if __name__ == '__main__':
	unittest.main()

As soon as you save the test code, PTVS will recognize the unit tests and display them in the Test Explorer:

Unfortunately, we have not been able to debug unit tests. It would be necessary to launch IronPython with specific arguments for debugging but PTVS does not allow to change the arguments past to IronPython during unit test debugging.

Summary

Some of our customers have customized time cockpit quite a bit. Although more and more customizations are built based on our RESTful Web API, Python is still a vital part of time cockpit. Python Tools for Visual Studio are important because they provide essential functions for managing non-trivial scripts made for time cockpit.

Did you manage to follow along? Any questions or feedback? Feel free to use the comment section below to send us feedback or questions.

comments powered by Disqus

Rainer Stropek

Rainer Stropek

Co-founder, architect, developer

Bio

I am co-founder and CEO of the company software architects and have been serving this role since 2008. At software architects my team and I are developing the award-winning SaaS solution time cockpit. Previously, I founded and led IT consulting firms that worked in the area of developing software solutions based on the Microsoft technology stack.

In my work I focus on .NET development and software architecture. I have written some books and articles on C#, database development, Windows Azure, Windows 8 development, WPF, and Silverlight. Regularly I speak at conferences, do workshops and conduct trainings in Europe and the US. Since 2010 I have been MVP for Windows Azure.

I graduated the Higher Technical School Leonding (AT) for MIS with honors and hold a BSc (Hons) Computer Studies of the University of Derby (UK).

Contact

Twitter: @rstropek
Facebook
Google+
Xing
LinkedIn

Authors