Sage Developers' Blog

In part 4 of my series of posts discussing web extensibility I plan to give you a step-by-step guide on how as a developer you can extend the behaviour of Sage 200 services. As mentioned in my initial post, although the posts are primarily aimed at Sage 200 Business Channel Developers (or 3rd parties), the ‘downloads’ are not specific to any current or future Sage 200 library (assembly) or service, it is the concepts and patterns that are important.

This post is not intended to be used as a reference guide for WCF, various links can be found here.

If you haven’t already can I recommend you read the previous post article. In that post I included a reference diagram that described the ‘hook’ points that are surfaced to enable developers to extend the operation behaviour.

Whereas in Part 3 I concentrated on looking at the ‘hook’ points on the server, in this post I intend to demonstrate how a 3rd party can influence the behaviour of the service request within the client / consuming application – in the diagram below these are hook points 1 and 7.

Code

The Expense Scenario – Part 3

The functionality of the out-of-the-box application itself hasn’t changed since my previous post. As before all the application will do is allow a user to login and then either view, create or delete an expense.

However, through an extension discovered at runtime (not compile time), the application now includes some extra behaviours. As before, these behaviours have not been written by a Sage 200 (employee) developer but rather a Sage 200 Business Partner to add additional behaviour not already part of the core product.

What scenarios does this solve? Well as you will see in a later post what we are hoping to support are 3rd party extensions of the core web UI – for example, when entering an Expense allow the user to enter the address of the hotel they stayed in. This would be great, but what if this extra information needs to be stored along with the  actual expense? How can a 3rd party ensure their extra ‘data’ is persisted when the expense is submitted? This is what these hooks are trying to solve.

For this post I don’t intend to show extensions to the UI – I’ll leave that for a later post…so for the purpose of this post at ‘hook’ 1 I will add a simple date stamp (as a string) to the expense being submitted. We’ll then see, based on a new extension at point 5, that date stamp being persisted along with the expense item in the spare field called SpareString1.

Download the solution here and give it a go.

Ok so what’s new? Well you might have noticed that there is a new project in the Extensibility solution folder called Services.Extensibility.Client. This project has 2 key responsibilities:

1. Discovery of Extensions
2. Client-side SDK

The discovery mechanism is much as before. Like the server-side equivalent of this assembly it uses the Managed Extensibility Framework to discover parts that have been marked as [Export]. The only difference here is the location…on the client I have specified the discovery mechanism to look in the bin\debug  folder of the hosting client application – in this case the SimpleConsoleConsumer.

Now lets look at how a 3rd party would use the client-side SDK to inject their own behaviour. Once again all the 3rd party has to do is realise one interface and then ensure their assembly is then discoverable. The interface they need to realise is IEndpointBehaviorExtender. Notice again that the 3rd party does not have to reference any of the MEF components, the discovery process is completely hidden from the 3rd party.

There are only 2 methods to implement, Before the request has been sent and After the reply has been received. The Before method allows the 3rd party to ‘manipulate’ the data contract. This could be to include additional data (as in the snippet above) or it could be they want to do additional client-side validation. The eagle-eyed amongst you will have noticed that the service operations are no longer using simple data contracts. The service operations are now message-based, and this is a deliberate long-term decision we (Sage 200) have made with regards our service design (I intend to cover this in more detail in a future post).

In future posts I intend to cover how a developer can extend the behaviour of the client. In these posts I will cover in a lot more detail how to use the your own data captured in your own controls in our service contracts.

Feedback and comments are very welcome including any suggestions for future content.

(My disclaimer) Please be aware, this series of blog posts are being written at the same time as the next release of Sage 200 2011. Although many of the concepts have been qualified and included within the 2011 development project plan there is no guarantee they will make the final release.

In part 3 of my series of posts discussing web extensibility I plan to give you a step-by-step guide on how as a developer you can extend the behaviour of Sage 200 services. As mentioned in my initial post, although the posts are primarily aimed at Sage 200 Business Channel Developers (or 3rd parties), the ‘downloads’ are not specific to any current or future Sage 200 library (assembly) or service, it is the concepts and patterns that are important.

This post is not intended to be used as a reference guide for WCF, various links can be found here.

If you haven’t already can I recommend you read the previous post article. In that post I included a reference diagram that described the ‘hook’ points that are surfaced to enable developers to extend the operation behaviour.

Service Layer Extensibility Hook Points

This post will concentrate on ‘hook’ points  2,3,5 and 6. Note I haven’t included hook 4 – this hook point requires knowledge of the Sage 200 business objects (not part of this scope). Hook points 1 and 7 will be discussed in my next post.

Code

The Expense Scenario – Part 2

The functionality of the out-of-the-box application itself hasn’t changed since my previous post. As before all the application will do is allow a user to login and then either view, create or delete an expense.

However, through an extension discovered at runtime (not compile time), the application now includes some extra validation. What is special about this validation is that it has not been written by a Sage 200 (employee) developer but rather a Sage 200 Business Partner to add additional behaviour not part of the core product.

The solution can be downloaded here

Try out the application now…you should find that if you create an expense for a value greater than £600 or try to remove an expense that has an Id which is an odd number, a new message will appear in the console and the request is aborted.

Have a look in the Expenses.Logic.Library, there is no code that implements this logic. Now look in the ‘Extensions’ Solution Folder in the project Expenses.Services.Server.Extensions. There are 2 classes CreateExtension and DeleteExtension…the logic you are looking for can be found in the method ValidateBeforeCall.

However, if you look at the projects included in Service Items solution folder you will notice that none of the projects reference the project Expenses.Services.Server.Extensions. As said previously the extension assembly is discovered at runtime and not compile time. This is achieved using the Microsoft Managed Extensibility Framework (also known as MEF) a new component included in .Net 4 framework (the assembly is called System.ComponentModel.Composition). I do not intend to cover the detail of this component but if you are new to MEF then I suggest you look here for a description and overview its capabilities, but in summary MEF:

• simplifies the creation of extensible applications,
• offers discovery and composition capabilities that you can leverage to load application extensions.

From a 3rd party point of view, adding new extensions is quite straightforward. If we look closely at the type DeleteExtension.cs (in the project Expenses.Service.Server.Extensions) the only task for the 3rd party developer is to realise the interface IOperationBehaviorExtender (the proposal is that the interface will be shipped as part of a Sage 200 Web SDK).

In the code example above the 3rd party developer has decided to only extend the behaviour of the ValidateBeforeCall method. This is where they have injected the logic to abort the execution of the DeleteExpense logic if the id of the Expense is odd (I know not real-world but hopefully it gets the point across…I promise that in later editions of the series I’ll do something more realistic). Note, the 3rd party developer has not had to worry about how the discovery process happens, for example they have not had to include any reference to the MEF assembly System.ComponentModel.Compositon. All they need to ensure is they have fully realised the interface IOperationBehaviorExtender and they have included the correct Action value (the proposal is that all actions that are extendable will be documented as part of a Sage 200 Web SDK)

Next, what has the Sage 200 Developer had to do so that their method is marked as an extensible operation? Well I’ve taken the decision that developers must opt in if they require their operation to be extensible, rather than all operations being extensible by default. To do so the Sage 200 Developer must decorate the concrete implementation of their method with a custom attribute.

They must also ensure that they include a unique action name for the interface definition of the service operation. It needs to be unique as this will be the Action value the 3rd party will set within their extension implementation (see snippet above).

Finally the discovery process…the code for discovering 3rd party extensions can be found in the type ExtensionsFactory in the project Services.Extensibility.Server. Please consider this code as a first draft of what the final (release) code may look like, for example the final location for all 3rd party extensions will be predetermined most probably by the Sage 200 Add-on Manager not the bin folder of the hosting WCF application. But for now consider this code as an introduction to the MEF discovery process. One thing worth noting is how implementations of IOperationBehaviorExtender become discoverable…as I noted above, the 3rd party developer has not had to include any references to MEF in their implementation. The part is discoverable because I have included the attribute [InheritedExport] on the declaration of the type IOperationBehaviorExtender.

James Eggers has a good blog post outlining the benefits of using this approach. Later on we’ll see that this approach is not always appropriate when there becomes a need to include / exclude extensions on a case by case basis.

The Next Post

In my next post I intend to build on this solution by including additional service behaviours within the Client (consumer) of the service. It will show the changes that are required to the Service Contract and though the use of Microsoft’s Managed Extensibility Framework (MEF) show how 3rd party extensions can be included in the WCF Service Pipeline.

Feedback and comments are very welcome including any suggestions for future content.

(My disclaimer) Please be aware, this series of blog posts are being written at the same time as the next release of Sage 200 2011. Although many of the concepts have been qualified and included within the 2011 development project plan there is no guarantee they will make the final release.

UPDATE – the Sage 200 2011 release introduced a new naming convention for Content Part Types assemblies required so that we could support a new discovery mechanism. Therefore, if you have already downloaded and installed the package and would like it to run as part of your 2011 release, you will need to find the CustomMapContentList.dll assembly and rename it to CustomMapContentList.Plugins.dll If you have not installed the package the simplest thing to do is rebuild the solution and rename the assembly that is built to CustomMapContentList.Plugins.dll – you can then repackage the content part type yourself for future distribution.

Some people may have heard of a concept called Sage 200 Labs that we floated a while ago. The idea is that we provide an area where developers can download source code for useful, innovative or experimental add-ons to Sage 200 that can then be built on and shipped to customers.

The projects will usually be much bigger and more complete than the samples that we ship with the developer SDK, but at the same time, they are not necessarily release quality. They should provide a really good platform for you to build on though.

The source code is free to download and use and is released under a permissive open source license that is designed to encourage commercial use. Over time we hope that the labs concept will evolve into a genuine open source community with contributions coming from Sage and external developers. From day one though, we expect to be creating the projects ourselves.

Anyway, I’m very pleased to announce that the first labs project is now ready! It is a custom content part type that allows you make workspaces showing Sage 200 data on a map. Like this:

Example workspace with maps

To find out more and to download the project source code follow this link to the mapping content part landing page. You need to be a registered developer to get there. There is also a place on the forums where you can comment or ask questions. There is also some general information about the Labs concept.

Hopefully, you will find this interesting and useful. As always, comments and feedback are welcome.

The Service Layer

In part 2 of my series of posts discussing web extensibility I plan to give you an overview of the service layer we are implementing for the 2011 release of our new web client – Sage 200 Web Timesheets and Expenses. As mentioned in my previous post, although the posts are primarily aimed at Sage 200 Business Channel Developers, the ‘downloads’ are not specific to any current or future Sage 200 library (assembly) or service, it is the concepts and patterns that are important.

This post is not intended to be used as a reference guide for WCF, various links can be found here

Its vital we (Sage 200) produce the right Service Layer Architecture. The Service Layer underpins several future initiatives (see diagram below) being undertaken by the Sage 200 Development team.

In terms of extensibility in the service layer, the goal is to try and replicate the kind of experience a desktop solution developer has today when using the core Sage 200 business objects. That is, give the developer the opportunity to extend the behaviour of a service by providing them with the ‘hook’ points in the service pipeline to inject their code.

The following diagram illustrates the ‘hook’ points we want surface to a developer. Over the next couple of posts I will dive deeper into how we are planning to accomplish this.

Service Layer Extensibility Hook Points

Code

The Expense Scenario – Part 1

Initially all the application will do is allow a user to login and then either view, create or delete an expense.

The expense claims are then stored in the back office within a SQL Server database.

In this part of this blog series the user is not being authenticated.

The Expense Solution – Part 1

The solution can be downloaded here

The solution is broken into 3 parts:

1. The main application which is a simple Console application (don’t worry it will get better) called SimpleConsoleConsumer.
2. The WCF service libraries, a host Expenses.Service.Host, an implementation library Expenses.Service.Library and the WCF contracts Expenses.Service.Contracts. Although it is not strictly necessary to separate the service classes into 3 distinct assemblies (Host, Contracts, Implementation) it is felt it is good practice. As you will see later, one benefit of this separation of concerns (SoC) is reuse.
3. The core business logic Expenses.Logic.Library – essentially these mimic the core Sage 200 Business objects that exist today and are being used in the desktop application.

The following diagram illustrates how the 3 parts fit together.

Expense Service Overview

Hopefully when you read the code it should all make sense – there is nothing complicated or ‘out-of-the-ordinary’ happening. However to make it even easier to follow I’ll point out some of the key stages in the development process:

• SimpleConsoleConsumer is basically a menu driven application, allowing the user to create, read and delete expenses. Business logic has been kept to a minimum (simple type checking only) with the majority of the logic being handled by the Expense service implementation.  I added a service reference to the project, specifying http://localhost:56943/ExpenseService.svc as the endpoint. I then make use of the Service Client class that is generated by Visual Studio (ExpenseServiceClient)  as a proxy to the WCF service.

• The Service Host (the service endpoint) is a WCF Application project template. You’ll notice that there is not a code-behind class for the ExpenseService.svc type. If you view the mark-up for the type you will see that it points at the type ExpenseService.cs in the Expense.Service.Library assembly.

• The ExpenseService.cs in the assembly Expenses.Service.Library is a realisation of the IExpenseService interface (see code snippet below).

It has the following responsibilities:

- delegate business logic responsibility to the core Sage 200 business objects (represented by the _coordinator in the code snippet below)

- handle the success or failure(s) that results from the business logic processing in the core Sage 200 business objects. Notice that the code is not just handling one failure that is being thrown by the business objects, but rather it is collating them within a single FaultContract and throwing a single FaultException.

• If you are familiar with Sage 200 business objects the Expense.cs and ExpenseDataObject.cs (aka PersistentBusinessObject) shouldn’t be a surprise. However, you may be wondering what the role of the ExpenseCoordinator.cs is. Its role is to bridge the gap between the core Sage 200 business objects and the Sage 200 Service Implementation layer.

- It orchestrates the one or many business objects required to fulfil the service process.

- It translates and maps data contract members to sage 200 business object properties

- It handles the warnings, exceptions and invalid rules thrown by the sage 200 business objects

In essence, the ExpenseCoordinator hides the complexities of the business objects from the user (developer) of the service layer.

One thing to note is the implementation of the ExpenseDataObject is by no means a direct representation of the ORM layer (ObjectStore) that exists in the current Sage 200.  This is one example where I have simplified the implementation so that there are no dependencies on any existing Sage 200 assemblies or services.

The Next Post

In my next post I intend to build on this solution by including additional service behaviours within the Service Layer. It will show the changes that are required to the Service Contract and though the use of Microsoft’s Managed Extensibility Framework (MEF) show how 3rd party extensions can be included in the WCF Service Pipeline.

Feedback and comments are very welcome including any suggestions for future content.

(My disclaimer) Please be aware, this series of blog posts are being written at the same time as the next release of Sage 200 2011. Although many of the concepts have been qualified and included within the 2011 development project plan there is no guarantee they will make the final release.

Introduction

Following on from the success of our 2010 Purchase Order Remote Authorisation release, 2011 will see the release of our next generation of web client – Sage 200 Web Timesheets and Expenses. With this release we hope to provide support for a wide range of extensibility points both in the Web Client and the underlying Web Services so that customisations and extensions are a lot more easier to implement and manitain.

Most of the concepts will be available within the 2011, the intention is that ‘web-extensibility’ will not be fully supported until 2012. The intention of this blog series is to give you, a Sage 200 developer, an insight into the technologies, architectures, patterns and practices we  have employed. Over the next few weeks I intend to cover the following topics:

• Part 1 – An Introduction
• Part 2 – The Service Layer
• Part 3 – Extending the Service Request Behaviour (Server)
• Part 4 – Extending the Service Request Behaviour (Client)
• Part 5 – Custom Authorisation in the Service Application
• Part 6 – Custom Authentication in the Service Application
• Part 7 – Service Transactions
• Part 8 – The Web Client Application
• Part 9 – Extending the Web Client Application User Interface Validation (Client and Server-side)
• Part 10 – Amending the Web Client Application User Interface Description (Metadata)
• Part 11 – Extending the Web Client Application User Interface Content (Controls)
• Part 12 – Custom Authorisation in the Web Application
• Part 13 – Deployment options

Architecture

Web reference archiecture

The web service architecture is based on Microsoft’s Windows Communication Foundation (WCF). We chose this framework for the following reasons:

• It is a mature, tried and tested flexible framework that is built into .Net and is actively developed by Microsoft
• It is extremely well documented and supported
• It provides protocol, hosting and service consumer flexibility via configuration allowing developer to concentrate on services logic rather than plumbing and protocols
• It exposes the necessary hooks for amendability/extensibility in the service pipeline
• It provides a range of toolkits and SDKs to implement different kinds of service

For more details see the following links:

• What is Windows Communication Foundation?
• Extending Windows Communication Foundation

The web client application architecture is based on ASP.Net Model-View-Controller (MVC) 2. We chose this framework for the following reasons:

• It is a fully supported part of .Net that is getting significant development effort from Microsoft
• It is standards based and open source (including embracing the open source jQuery javascript library).
• It strongly promotes a clean separation of UI and business logic
• It is well integrated with unit test frameworks
• Experience in the Sage 200 team with Remote Purchase Order Authorisation and the experience of the French Ligne 100 Etendue team is that it is a productive, easy to use framework.

For more details see ASP.NET MVC Official Microsoft Site

To deliver certain elements of the solution, we have used Rx for JavaScript. This is so we can handle the mix of Html DOM events  (e.g. onclick, onchange, key-up, etc…) on the client, in a consistent manner. For more details see the following links

• http://msdn.microsoft.com/en-us/devlabs/ee794896.aspx
• http://blogs.msdn.com/b/rxteam/archive/2010/03/17/reactive-extensions-for-javascript.aspx
• http://blogs.msdn.com/b/rxteam/archive/2010/07/15/rx-hands-on-labs-published.aspx

Although the posts are primarily aimed at Sage 200 Business Channel Developers, the ‘concepts’ are not specific to any current or future Sage 200 library (assembly) or service.  To prove this, as part of the series I will be including a ‘mock’ expenses application which will be available for download. In each new post the application will be updated to include the intiative described in that post, so that by part 13 of the series you will have an application that fully describes the key initiatives we plan to deliver in 2011.

Feedback and comments are very welcome including any suggestions for future content.

(My disclaimer) Please be aware, this series of blog posts are being written at the same time as the next release of Sage 200 2011. Although many of the concepts have been qualified and included within the 2011 development project plan there is no guarantee they will make the final release.

Workspaces make it possible to add all sorts of rich content to the Sage 200 desktop. Sometimes this needs genuine development work (e.g. custom content part types) but the Linked Web Browser content part type that we ship with Sage 200 v2009 makes it possible to include some pretty nice content quickly and easily. This post will show how you can add charts to your workspaces in a lightweight way using the Google Image Chart API. Now, you may know that we are including a dedicated chart content part type with Sage 200 v2010 so this post will be redundant for customers on that version. It could be useful though as long as you have customers on v2009. As well as this, it is also a good example of the kind of rich content you can get into workspaces using the Linked Web Browser content part type.

To set the scene, I’m going to show you how to create a workspace in Sage 200 v2009 that looks like this

And this

The first panel is a simple customer list and the details view on the right shows the customer details. The bottom left panel allows the user to look at either the top 10 products for the selected customer in a pie chart, or the last 6 months sales for the selected customer in a bar chart. The charts are provided courtesy of the Google Chart API and the Linked Web Browser content part type. No custom content parts are needed for this. In principle you could go to a customer site and create this right inside the Sage 200 desktop in front of them (Note: The query behind it is a bit complex so I wouldn’t recommend actually doing this unless you are very confident with your LINQ!)

If you’ve read enough and you just want to get your hands on the charts, you can just download a package with the sample workspace in it. If you want to delve into some of the LINQ behind the charts, read on…

The Google Image Chart API is a web service that dynamically generates an image of a chart based on the query string of a URI. A simple example from the Google help pages uses this URI

http://chart.apis.google.com/chart?cht=p3&chd=t:60,40&chs=250×100&chl=Hello|World

To generate this chart image

The API is tricky but quite flexible and you can generate lots of different chart types. The key for us is to create a LINQ query that generates the right URI from the source data. You can see from the sample that the chart data itself is contained in the URI. The bit of the URI that says

…chd=t:60,40…

Specifies the size of the pie chart segments. The labels are specified by the bit that says

…chl=Hello|World

You can cut and paste this URI into a browser and play with the values to see how it works.

The Linked Web Browser content part type works in a  simple way. It exposes a single property which represents the URI of the web content it will display. The query behind its parent content part must generate a URI which is then passed to the Linked Web Browser using the normal parent/child relationship mechanism for workspaces. This URI can be a local one (e.g. a file:: scheme URI to display a PDF file from the local file system) or a web one as in this post. The only thing the Linked Web Browser content part does is validate that the URI is well-formed. If doesn’t do any database access or manipulate the passed-in URI in any way. So the responsibility for creating the URI sits with the LINQ query from the parent content part.

This chart API is easy for simple charts like the Hello World pie chart, but obviously more complex when you have to generate a more realistic URI in a LINQ query. In real situations these queries will probably be quite involved so performance may be an issue if you’re not careful. A tip that can help this is to use a simple linking content part to ensure that you only construct each URI when you need it. In the example this is the small list in the middle on the left hand side. The query logic  to generate the chart API URIs is contained in this content part, rather than the main customer list. The linking part is then filtered by the selected customer ID in the first list so the complicated part of the query is only executed for a single customer at a time. This should significantly reduce the query time, but as always, it’s best to thoroughly check out performance on real data before you put anything live.

In the sample example for this post, there are actually two different queries (one for each chart type) that are concatenated together using the LINQ Concat query operator.

The first query (called last6MonthSales) gets the last six months sales (obviously). It is structured into 4 sections:

The first section sets does basic filtering  to get the right SOPOrderReturns to start with

DateTime date = DateTime.Today;
string baseUri = "http://chart.apis.google.com/chart?cht=bvg&chs=800x375&chd=t:";
var last6MonthSales = cxt.SOPOrderReturns
.Where(order => order.DocumentDate.Value.Year == date.Year)
.Where(order => order.DocumentDate.Value.Month >= date.Month - 6 && order.DocumentDate.Value.Month <= date.Month) .Where(order => order.DocumentType.SOPOrderReturnTypeName == "Sales Order")
.Where(order => order.DocumentStatus.Name != "Cancelled")

The second section groups the results by customer ID and the month of the order relative to the current date and selects the sum of the the order gross value

.GroupBy(
order => new
{
order.CustomerID,
RelativeMonth = date.Month - order.DocumentDate.Value.Month
},
(key, orders) => new
{
key.CustomerID, key.RelativeMonth,
Total = orders.Sum(order => order.TotalGrossValue)
})

This is then grouped again, this time just by the customer ID and the total order value for each of the current month and six previous months are separately selected. This select also pulls out the month name for each month.
.GroupBy(
item => item.CustomerID,
(customer, items) => new
{
SLCustomerAccountID = customer,
Max = items.Max(item => item.Total),
Month0Total = (decimal?)(items.SingleOrDefault(item => item.RelativeMonth == 0).Total) ?? 0,
Month1Total = (decimal?)(items.SingleOrDefault(item => item.RelativeMonth == 1).Total) ?? 0,
Month2Total = (decimal?)(items.SingleOrDefault(item => item.RelativeMonth == 2).Total) ?? 0,
Month3Total = (decimal?)(items.SingleOrDefault(item => item.RelativeMonth == 3).Total) ?? 0,
Month4Total = (decimal?)(items.SingleOrDefault(item => item.RelativeMonth == 4).Total) ?? 0,
Month5Total = (decimal?)(items.SingleOrDefault(item => item.RelativeMonth == 5).Total) ?? 0,
Month6Total = (decimal?)(items.SingleOrDefault(item => item.RelativeMonth == 6).Total) ?? 0,
Month0Name = (date.AddMonths(-0)).ToString("MMM"),
Month1Name = (date.AddMonths(-1)).ToString("MMM"),
Month2Name = (date.AddMonths(-2)).ToString("MMM"),
Month3Name = (date.AddMonths(-3)).ToString("MMM"),
Month4Name = (date.AddMonths(-4)).ToString("MMM"),
Month5Name = (date.AddMonths(-5)).ToString("MMM"),
Month6Name = (date.AddMonths(-6)).ToString("MMM"),
})
The final select uses these monthly totals to construct the chart URI
.Select(
customer => new
{
GraphType = "Last 6 month sales",
ID = customer.SLCustomerAccountID,
ChartUri = baseUri +
customer.Month6Total + "," +
customer.Month5Total + "," +
customer.Month4Total + "," +
customer.Month3Total + "," +
customer.Month2Total + "," +
customer.Month1Total + "," +
customer.Month0Total +
"&chds=0," +
customer.Max +
"&chco=5131C9&chxt=y,x" +
"&chxl=1:|" +
customer.Month6Name + "|" +
customer.Month5Name + "|" +
customer.Month4Name + "|" +
customer.Month3Name + "|" +
customer.Month2Name + "|" +
customer.Month1Name + "|" +
customer.Month0Name +
"&chxs=0,000000,12,-1|1,000000,12,0" +
"&chxr=0,0," + customer.Max +
"&chbh=a" });
The second query (productBreakdown) gets the top 10 stock items per customer. This is structured as follows:

The first section selects from SLCustomerAccounts with a sub-query to select the top 10 stock items using a GroupJoin (which results in a SQL left outer join) between StockItems and SOPOrderReturnLines

var productBreakdown = cxt.SLCustomerAccounts
.Select
(
s=>new
{
ID=s.SLCustomerAccountID,
Items = cxt.StockItems
.GroupJoin
(
SOPOrderReturnLines.Where(line => line.SOPOrderReturn.CustomerID == s.SLCustomerAccountID),
item => item.Code,
line => line.ItemCode,
(item, line) => new { item.Code, line }
)
.SelectMany
(
itemLine => itemLine.line.DefaultIfEmpty(),
(itemLine, line) => new
{
itemLine.Code,
Value = (decimal?)line.LineTotalValue
}
)
.GroupBy
(
itemLine => itemLine.Code,
(code, itemLines) => new { code, Total = itemLines.Sum(item => item.Value) }
)
.OrderByDescending(item => item.Total)
.ThenBy(item => item.code)
}
)

The second section constructs the desired chart URI

.Select
(
s=>new
{
GraphType = "Product breakdown",
s.ID,
ChartUri="http://chart.apis.google.com/chart?cht=p&chds=0,100000&chco=FF0000,FFFF00,00FF00,0000FF&chs=500x250&chd=t:" +
s.Items.Skip(0).First().Total + "," +
s.Items.Skip(1).First().Total + "," +
s.Items.Skip(2).First().Total + "," +
s.Items.Skip(3).First().Total + "," +
s.Items.Skip(4).First().Total + "," +
s.Items.Skip(5).First().Total + "," +
s.Items.Skip(6).First().Total + "," +
s.Items.Skip(7).First().Total + "," +
s.Items.Skip(8).First().Total + "," +
s.Items.Skip(9).First().Total +
"&chdl=" +
s.Items.Skip(0).First().code + "|" +
s.Items.Skip(1).First().code + "|" +
s.Items.Skip(2).First().code + "|" +
s.Items.Skip(3).First().code + "|" +
s.Items.Skip(4).First().code + "|" +
s.Items.Skip(5).First().code + "|" +
s.Items.Skip(6).First().code + "|" +
s.Items.Skip(7).First().code + "|" +
s.Items.Skip(8).First().code + "|" +
s.Items.Skip(9).First().code
}
);

You can download an add-on package containing a complete workspace featuring the two queries. Note – you will need to be a registered developer to access this. If you are not a developer, post a comment and I’ll find another way to get it to you.

This is a lightweight way of livening up workspaces with charts. It doesn’t have any advanced features such as being able to interact with the chart, and the underlying querying can be complicated because you have to build the entire chart data and formatting into a single string. These are the reasons why we have developed a dedicated chart content part type for Sage 200 v2010. However, if you can’t wait until then or if you have customers that do not want to upgrade to v2010, you could use this approach.

The syntax for a flat left outer join in LINQ is a bit different from the corresponding SQL. Using Lambda syntax it looks like this.

(Note: to make these samples work in LINQPad you have to remove the references to “context”. See this post for details.)

var q =
context.PCProjectItems
　　　　.GroupJoin
　　　　(
　　　　　　　　context.PCProjectEntries,
　　　　　　　　item=>item.PCProjectItemID,
　　　　　　　　entry=>entry.ProjectItemID,
　　　　　　　　(item,entry)=>new{item.PCProjectItemID,entry}
　　　　)
　　　　.SelectMany
　　　　(
　　　　　　　　itemEntry=>itemEntry.entry.DefaultIfEmpty(),
　　　　　　　　(itemEntry,entry)=>new
　　　　　　　　{
　　　　　　　　　　　　itemEntry.PCProjectItemID,
　　　　　　　　　　　　Goods=(decimal?)entry.GoodsAmountInBaseCurrency
　　　　　　　　}
　　　　);

In comprehension syntax, the same query looks like this:

var q = from item in context.PCProjectItems
join entry in context.PCProjectEntries
on item.PCProjectItemID equals entry.ProjectItemID into itemEntries
from itemEntry in itemEntries.DefaultIfEmpty()
select new
{
item.PCProjectItemID,
Goods = (decimal?)itemEntry.GoodsAmountInBaseCurrency
};

Take your pick which syntax you prefer, or you can mix them in the same query if you like! In any case, both queries result in the same SQL being issued to the database:
SELECT [t0].[PCProjectItemID], [t1].[GoodsAmountInBaseCurrency] AS [Goods]
FROM [PCProjectItem] AS [t0]
LEFT OUTER JOIN [PCProjectEntry]
AS [t1] ON [t0].[PCProjectItemID] = [t1].[ProjectItemID]

In Sage 200 2010 purchase order authorisation has been extended to offer, amongst other things, an email notification feature. Purchase orders that qualify for authorisation can trigger email notifications as they progress through the system, alerting those involved in the authorisation workflow when they might need to take action.

Out of the box, there are two kinds of email format supported: plain text and HTML. The default format is HTML because of the rich formatting options it provides and its wide support from email progams. A typical email notification will look like this:

Organisations with concerns over HTML security or that simply prefer a text layout may find it more appropriate to use the plain text option.

Changing between email formats is a relatively simple matter of changing a configuration option and since this is the kind of change you will only ever make once or twice, we hid the setting away in the Sage200 Web Services web.config file.

To change the setting:

• open the web.config file in your favourite text editor, on most systems it should be somewhere like C:\inetpub\wwwroot\Sage200WebServices
• search for: mapTo=”Sage.Web.Services.EmailFormatting.HtmlNotification, Sage.Web.Services.EmailFormatting”
• replace “HtmlNotification” with “PlainTextNotification”
• save the file and restart the web server

The next set of authorisation emails will look like this:

You may notice that both designs are fairly plain, this is intentional so that they will display consistently on a wide variety of desktop and web email client programs.

Their content is also a balance between including all the possible information about a purchase order and conveying the most important information to be able to quickly make an authorisation decision.

You may find that too much or too little information is included in these layouts or that you want to add some graphics or branding to the emails to better fit in with your organisation’s existing designs. We based the email generation on XML and XSLT so reformatting the information and changing the entire look is fairly easy. When the notification service generates the emails it creates an XML representation of the purchase order and applies a set of default XLST transforms to it to produce the email body. This default transformation can be overridden by creating an XLST file in the service bin folder called POPOrderNotification.xslt. If this file exists, the notification service will use it in preference to the defaults.

Below is an example of what can be achieved in a short amount of time. The changes we will make are:

1. Added a Sage logo
2. Changed the main heading to include the order creation date
3. Reduced the amount of information on each order line
4. Changed background and highlight colours

The XSLT code has been slightly simplified for clarity.

<xsl:template match=”/”>
<html>
<body>
<img src=”http://www.sage.co.uk/images/sageLogo80.gif” />

<!– Order Header –>
<h2 style=”background: #fff; color:#666; margin: 0px; padding: 0 2px 2px 4px; line-height: 0.9em;”>
P.O. <xsl:value-of select=”PurchaseOrder/Header/DocumentNumber” /> entered on <xsl:value-of select=”PurchaseOrder/Header/DocumentDate” />
</h2>
<h3 style=”background: #fff; color:#666; font-family: Segoe UI, Tahoma, Arial, Helvetica, sans-serif;”>
<xsl:value-of select =”PurchaseOrder/Header/Supplier/Name”/> (<xsl:value-of select =”PurchaseOrder/Header/Supplier/Reference”/>)
</h3>
<!– End of Order Header –>

<!– Order Lines –>
<xsl:apply-templates select=”PurchaseOrder/Detail” />
<!– End of Order Lines –>

<!– Totals –>
<xsl:apply-templates select=”PurchaseOrder/Header/Totals” />
<!– End of Totals –>
</body>
</html>
</xsl:template>

<xsl:template match=”Detail”>
<table width=”100%” border=”0″ cellpadding=”2″ style=”color:#000000;font-family: Segoe UI, Tahoma, Arial, Helvetica, sans-serif; font-size: 0.875em;”>
<tr>
<th align=”left” style=”background-color: #DAE3A8; color: #fff;” ><b>Code</b></th>
<th align=”left” style=”background-color: #DAE3A8; color: #fff;” ><b>Description</b></th>
<th align=”left” style=”background-color: #DAE3A8; color: #fff;” ><b>Quantity</b></th>
<th align=”left” style=”background-color: #DAE3A8; color: #fff;” ><b>Net Value</b></th>
</tr>

<xsl:for-each select=”Line”>
<tr>
<td valign=”top” style=”background-color: #eee; color: #000;” >
<xsl:value-of select=”Code”/>
</td>
<td valign=”top” style=”background-color: #eee; color: #000;” >
<xsl:value-of select=”Description”/>
</td>
<td valign=”top” align=”right” width=”50″ style=”background-color: #eee; color: #000;” >
<xsl:value-of select=”Quantity”/>
</td>
<td valign=”top” align=”right” width=”100″ style=”background-color: #eee; color: #000;” >
<xsl:value-of select=”Total”/>
</td>
</tr>
</xsl:for-each>

</table>
</xsl:template>

One thing to note in designing the XSLT is that the HTML used in the email not very (or at all) compliant with modern web standards. This is a consequence of trying to achieve a relatively consistent look across the diversity of HTML rendering engines used by email programs. Most of these are still not standards compliant and none of them agree on what a correctly formatted HTML email looks like. Creating an HTML layout is often more about finding an agreeable “lowest common denominator” than the single “correct” solution. If you are targeting a single email client application you may be able to clean up a lot of this code and reduce the number of table and style attributes.

This is what the new email should look like:

If neither of these alternatives will give you the results you need, you can also write your own .NET assembly and have that do whatever formatting you like. We will put more information on this approach in the Sage 200 2010 developer SDK.

One of our key focuses for the 2010 release, as part of our ongoing Information Management strategy, is to improve the ease of reporting on Sage 200 data.

As you may have read in a previous article, one way we’re doing this is through the introduction of the new Report Designer and a LINQ data model. This isn’t all we’re doing, however; the changes I’m going to discuss in this article specifically concern the changes we are making to Analysis Codes throughout the system to make them easier to report on.

Sage 200 is a fairly highly normalised database which has been designed for transactional performance and efficiency. Making it easy for the application itself to quickly and reliably read/write data in this way is not always consistent with easy access to the data in a “human readable” format.
This is always a trade-off in database design and is particularly relevant to Analysis Codes, where up to now we have put the emphasis on power and flexibility more than ease of reporting.

In 2010 we’re aiming to increase the emphasis on usability – to make Analysis Code much easier to report on, without significantly affecting their flexibility. As part of this overall simplification, we’ll be improving consistency between the implementation of Analysis Codes in different modules that were developed over a period of time – the improvements affect SL, PL, SOP, POP, Stock Transactions and BOM.

The overarching approach is that Analysis Code values will now be stored directly in the “entity” table in the Sage 200 database:

We believe that this will make it really easy to get the information you want out of the database!

Another change is that Analysis Codes are created centrally and then assigned to a field on a particular object – this means, for example, that a “Region” field on a customer account could actually be the same Analysis Code as the “Region” field on a supplier account.

The central list of Analysis Codes is stored in the AnalysisCode table; mapping to the data tables is through the AnalysisCodeMapping table (in both cases the schema has been simplified slightly for clarity”):

These tables allow you to look up the user-friendly name for any given Analysis Code and also store some of the other options for a particular code.
For any given Analysis Code it is possible to specify that values:

• can only be selected from a pre-defined list (stored in the AnalysisCodeValue table)
• may be selected from a pre-defined list or entered ad-hoc
• can be added to the pre-defined list on entry

It is also possible to specify that a field is mandatory for a particular object – i.e. the user interface will always insist that the user enter a value.

The (very) observant amongst you may have noticed that while this approach great simplifies Analysis Codes and hopefully makes them far more useful and valuable, there is one minor limitation to this approach: there will now be a limit on the number of Analysis Codes that can be applied to a particular table.
Well, that’s true – it’s now only possible to have 20 Analysis Codes on any single object (although the list of possible Analysis Codes is still unlimited). While it was theoretically possible to have an unlimited number of Analysis Codes on a SL customer (for example) in prior versions, the practical complexities of managing user interface, etc. means that very few customers use more than a handful – we firmly believe that 20 slots per module will be more than sufficient (if you disagree, please let us know!)

This article has only briefly covered the changes but has hopefully given you a flavour of what’s coming. Of course, this subject will be covered in a great deal more detail in the SDK documentation.
In the meantime, if you have any questions – please comment below!

Please note: This is a long and very technical discussion of some of the .NET language features we use in Workspaces. It is not Sage 200 specific.
Please let us know what you think of the technical level of the article – we’d love to get feedback on how we’re doing that helps us improve the value of this blog!

Lambda Expressions and Extension Methods are powerful new features in .NET 3.0 and are often used extensively when writing LINQ. In fact, these features are little more than syntactic sugar; they don’t enable functionality that wasn’t previously possible, but they do allow code that would previously have been quite complex to be written in a succinct, clear and elegant way.

While they are quite straightforward to use, they are not particularly easy to explain or intuitive to understand. This document aims to explain how these language features work.

Error checking has been excluded from all examples for brevity and clarity – the code is not intended to be production quality. Some of the examples are also quite weak; they are intended to demonstrate use of the language, not necessarily good examples of when to use the features!

Many of the examples make use of IEnumerable collections. This was to allow complete examples to be given, including the data types. When using LINQ to SQL the collection types are based on the IQueryable interface, but this makes little difference to the code using the collections.

Simple Extension Methods
At their simplest, Extension Methods allow the definition of a class to be extended with additional methods; to add new methods to the class that appear identical to member functions.

For example, given a very simple “bank account” class:

class Account
{
public Account(string name, decimal balance)
{
this.name = name;
this.balance = balance;
}

public string name;
public decimal balance;
}

We may wish to create a function that allows funds to be deposited in the account.
This could be added as a member function, or as a public function taking the account as a parameter:

public void Deposit(Account account, decimal deposit)
{
account.balance += deposit;
}

// ....
// To deposit £10~~
Deposit(account, 10);

Alternatively, we could write this as an Extension Method.

static class Extensions
{
public static void Deposit(this Account account, decimal deposit)
{
account.balance += deposit;
}
}

// ....
// To deposit £10
myAccount.Deposit(10);

The defining characteristics of the extension method are:
• It’s a static function in a static class
• Use of the “this” keyword in the parameter list

Note that the method is not a “true” member function; private members of the class cannot be accessed, for example.

Extending Collections
Extension methods are not limited to concrete types; they can be used to extend anything that the compiler recognises as a strong type such as a collection of objects:

public List accounts = … ;

// ....
// To deposit £10 in Steve’s account
accounts.Deposit(“Steve”, 10);

Alternatively, we might want a method that finds all of the overdrawn accounts:

public static IEnumerable GetOverdrawnAccounts(this IEnumerable accounts)
{
foreach (Account account in accounts)
{
if (account.balance < 0)
yield return account;
}
}
Note the use of the “yield” keyword, which essentially says “add the item to the return set and continue”

Extension Methods and Delegates
A very common pattern with Extension Methods is to have a function delegate as one of the parameters, allowing function logic to be determined by the caller.

For example, we could rewrite our “GetOverdrawnAccounts” example as:

public static IEnumerable GetAccounts(this IEnumerable accounts, Func predicate)
{
foreach (Account account in accounts)
{
if (predicate(account))
yield return account;
}
}

IEnumerable overdrawnAccounts = accounts.GetAccounts(IsOverdrawn);
Note the use of the “Func” delegate overload, specifying a delegate parameter which takes an Account as its parameter and returns a bool.

The advantage of this approach is that we can now supply an alternative delegate without changing the code of the extension method:

static public bool IsStevesAccount(Account a)
{
return a.name == "Steve";
}

… = accounts.GetAccounts(IsStevesAccount);
This can be written (slightly) more succinctly using an anonymous delegate:

… = accounts.GetAccounts(delegate(Account a)
{
return a.name == "Steve";
});
This approach has an additional advantage in that the anonymous delegate can access variables from the calling scope.

In the examples above, we have determined the name of the account to be found at design time – if, however, the account name we want is not known until run-time then the named delegate approach would have a problem. As the prototype of the predicate is controlled by the extension method, we have no way to pass in a variable containing the name we want to find: we would need to create an alternative extension method, or use a variable at a higher scope (e.g. a global variable).

Using an anonymous delegate we can keep everything else the same, and:

string requiredAccount = "Steve";
… = accounts.GetAccounts(delegate(Account a)
{
return a.name == requiredAccount;
});
Similarly, we can now allow for an arbitrary overdraft limit in our list of the overdrawn accounts:

int overdraftLimit = 25;
… = accounts.GetAccounts(delegate(Account a)
{
return a.balance + overdraftLimit < 0;
});

Standard Extension Methods for Collections
In the examples given so far, we have written all of our own Extension Methods. This has been largely for expository purposes; a wide variety of extension methods are provided as part of the .NET framework which implement many of the standard operations you might wish to perform on a collection of objects (such as an IEnumerable or an IQueryable).

Here we give some simple examples of the standard extension methods, including versions of some of the earlier examples:

string requiredAccount = "Steve";
… = accounts.Where(delegate(Account a)
{
return a.name == requiredAccount;
});

int overdraftLimit = 25;
… = accounts.Where(delegate(Account a)
{
return a.balance + overdraftLimit < 0;
});

decimal totalBalance = accounts.Sum(delegate(Account a)
{
return a.balance;
});

decimal averageBalance = accounts.Average(delegate(Account a)
{
return a.balance;
});

For lists of the standard methods, see the MSDN articles IEnumberable Members and IQueryable Members.

Lambda Expressions
Lambda expressions are nothing more than an alternative syntax for defining an anonymous function delegate.

The expression follows the pattern:

Parameter list => Expression

Therefore :
delegate(Account a)
{
return a.name == requiredAccount;
}
Can be re-written as:
(Account a) => a.name == requiredAccount

As the compiler can infer the return type of the expression, it is not actually necessary to supply the type of the parameter, so this can be furthered simplified to:
a => a.name == requiredAccount

So we can now re-write our previous examples as:
string requiredAccount = "Steve";
… = accounts.Where(a => a.name == requiredAccount);
int overdraftLimit = 25;
… = accounts.Where(a => a.balance + overdraftLimit < 0);
decimal totalBalance = accounts.Sum(a => a.balance);
decimal averageBalance= accounts.Average(a => a.balance);
For more detail on other possibilities for Lambda expressions, including support for multiple parameters and multi-statement expressions, see the MSDN “Lambda Expressions in LINQ
One of the places we make most use of lambda expressions and the standard extension methods is in the LINQ for the Sage 200 Workspaces.

So an example of a simple query which returns a stock item code and the total confirmed quantity in stock across all warehouses could be written as:
var q = from si in StockItems
let wiSet = WarehouseItems.Where(wi => si.ItemID == wi.ItemID)
select new
{
si.Code,
ConfirmedQtyInStock = (decimal?)wiSet.Sum(wi => wi.ConfirmedQtyInStock)
};
Replacing the lambda expressions with anonymous delegate syntax:
var q = from si in StockItems
let wiSet = WarehouseItems.Where(delegate(WarehouseItem wi)
{
return (si.ItemID == wi.ItemID);
})
select new
{
si.Code,
ConfirmedQtyInStock = (decimal?)wiSet.Sum(delegate(WarehouseItem wi)
{
return wi.ConfirmedQtyInStock;
})
};
return q;

And without using extension methods (assuming very simplistic implementation of the query functions):
var q = from si in StockItems
select new
{
si.Code,
ConfirmedQtyInStock = GetTotalConfirmedQtyInStock(
GetWarehouseItemsForStockItem(WarehouseItems, si))
};
return q;

return result;
}