Twinfield Journal Entries Export
This document describes the export template for journal entries to the Twinfield accounting system.
General Concept
The general concept is the same as that explained for the Default General Ledger Template. That is, BillingOutputTags (BOTs) are used to indicate for which values journal entries need to be included. The Twinfield format is slightly different in a few critical points though, which we will discuss here.
First off, the format is an excel sheet (which is generated in xml format), but can be named as .xls and used immediately. This format is a little more complicated and has some more stringent requirements in terms of whitespace and formatting, which introduces some line-crossing comments in the actual template. In order to suppress a newline in some situations.
Second, the rows in the output follow a slightly different format, requiring some additional logic in the template. The main difference is that instead of having a customerCode or identifier on every line, only the debtors journal entry requires a ‘relation code’. So we have to know which account number is the debtors account (1300 by default) and condition on that to include an extra cell containing the customerCode.
Currently, we support one VAT code for all entries (except for the debtors entry, since that doesn’t take a VAT code). This can easily be extended to include multiple codes though, which could be specified as part of the GL_Entry BOT.
Template
@using Kolonel.Domain.Model
@{
var nlCult = System.Globalization.CultureInfo.CreateSpecificCulture("nl");
var invoices = Model.ExportInvoices;
var rows = 1;//start with 1: Header row.
var deb = "1300";//Debtors account
var booking = 0; //Bookingg number
var vatCode = "VH";//VAT Code
var paymentTerm = 14;//Payment term
foreach(var inv in invoices){
foreach(var item in inv.InvoiceItems.Where(iit => iit.InvoiceRuleResult.BillingOutputTags.Contains("GL"))){
rows += item.InvoiceRuleResult.BillingOutputTags.Count(bot => bot.StartsWith("GL_Entry"));
}
}
var today = DateTime.Now.ToString("yyyy-MM-dd'T'HH:mm:ss");
}@* Comment to remove newline from output
*@<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>CloudBilling</Author>
<LastAuthor>CloudBilling</LastAuthor>
<Created>@today</Created>
<LastSaved>@today</LastSaved>
<Version>1.0</Version>
</DocumentProperties>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s16">
<Font/>
<NumberFormat/>
</Style>
<Style ss:ID="s17">
<Font/>
<Interior/>
<NumberFormat ss:Format="Short Date"/>
</Style>
<Style ss:ID="s18">
<Font/>
<Interior/>
<NumberFormat/>
</Style>
<Style ss:ID="s19">
<Font/>
<NumberFormat ss:Format="Fixed"/>
</Style>
<Style ss:ID="s20">
<Font/>
<Interior/>
</Style>
<Style ss:ID="s22">
<Font/>
</Style>
<Style ss:ID="s24">
<Font x:Family="Swiss" ss:Bold="1"/>
<Interior ss:Color="#FCF305" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s25">
<Font x:Family="Swiss" ss:Bold="1"/>
<Interior ss:Color="#FCF305" ss:Pattern="Solid"/>
<NumberFormat ss:Format="Short Date"/>
</Style>
<Style ss:ID="s72">
<Font x:Family="Swiss" ss:Bold="1"/>
</Style>
<Style ss:ID="s73">
<NumberFormat ss:Format="0%"/>
</Style>
</Styles>
<Worksheet ss:Name="Verkoopboekingen">
<Table ss:ExpandedColumnCount="14" ss:ExpandedRowCount="@rows" x:FullColumns="1"
x:FullRows="1" ss:StyleID="s22" ss:DefaultColumnWidth="55"
ss:DefaultRowHeight="12">
<Column ss:StyleID="s16" ss:AutoFitWidth="0" ss:Span="1"/>
<Column ss:Index="3" ss:StyleID="s17" ss:Width="81"/>
<Column ss:StyleID="s16" ss:AutoFitWidth="0"/>
<Column ss:StyleID="s16" ss:Width="82"/>
<Column ss:StyleID="s17" ss:Width="61"/>
<Column ss:StyleID="s16" ss:AutoFitWidth="0" ss:Width="61" ss:Span="1"/>
<Column ss:Index="9" ss:StyleID="s16" ss:Width="81"/>
<Column ss:StyleID="s16" ss:Width="76"/>
<Column ss:StyleID="s19" ss:AutoFitWidth="0"/>
<Column ss:StyleID="s16" ss:Width="59"/>
<Column ss:StyleID="s16" ss:Width="62"/>
<Column ss:StyleID="s16" ss:AutoFitWidth="0" ss:Width="57"/>
<Row ss:StyleID="s20">
<Cell ss:StyleID="s24"><Data ss:Type="String">Code</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">Currency</Data></Cell>
<Cell ss:StyleID="s25"><Data ss:Type="String">Invoice date</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">Period</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">Invoice number</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">Due date</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">Number</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">GL account</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">Rel/Cost centre</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">Prj/fixed asset</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">Amount</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">DebitCredit</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">Description</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">VAT code</Data></Cell>
</Row>@*
Here the actual bookings begin, comment to suppress newline
*@@foreach(var inv in invoices){
booking++;//
var items = inv.InvoiceItems.Where(iit => iit.InvoiceRuleResult.BillingOutputTags.Contains("GL"))
.OrderBy(iit => iit.InvoiceRuleResult.BillingOutputTags.Where(bot => bot.StartsWith("GL_Entry:")).FirstOrDefault().Substring(9).Split(new[] {";"}, StringSplitOptions.None)[0]);
foreach(var item in items) {
var lineCodes = item.InvoiceRuleResult.BillingOutputTags.Where(bot => bot.StartsWith("GL_Entry:"));
foreach(var lineCode in lineCodes){
var codeItem = lineCode == null?new[] {""}:lineCode.Substring(9).Split(new[] {";"}, StringSplitOptions.None);
if(codeItem.Length < 3){
continue;
} else if(codeItem[2]=="VAT"){//Skip VAT rules. -- handled by TWF
continue;
}
<Row>
<Cell><Data ss:Type="String">VRK</Data></Cell>
<Cell><Data ss:Type="String">EUR</Data></Cell>
<Cell><Data ss:Type="DateTime">@(inv.InvoiceExportDate==null?"":inv.InvoiceExportDate.ToString("yyyy-MM-dd'T'HH:mm:ss"))</Data></Cell>
<Cell ss:StyleID="s18"><Data ss:Type="String">@(inv.BillingPeriodStart==null?"":inv.BillingPeriodStart.ToString("yyyy/MM"))</Data></Cell>
<Cell ss:StyleID="s18"><Data ss:Type="String">@(inv.InvoiceNumber)</Data></Cell>
<Cell><Data ss:Type="DateTime">@(inv.InvoiceExportDate==null?"":(inv.InvoiceExportDate.AddDays(paymentTerm)).ToString("yyyy-MM-dd'T'HH:mm:ss"))</Data></Cell>
<Cell ss:StyleID="s18"><Data ss:Type="Number">@booking</Data></Cell>
<Cell><Data ss:Type="Number">@(codeItem[0])</Data></Cell>
@if(codeItem[0]==deb){
<Cell><Data ss:Type="String">@(inv.CustomerCode)</Data></Cell>
}
<Cell ss:Index="11"><Data ss:Type="Number">@(item.InvoiceRuleResult.Value.ToString("#0.00"))</Data></Cell>
<Cell ss:StyleID="s18"><Data ss:Type="String">@(codeItem[1])</Data></Cell>
<Cell ss:StyleID="s18"><Data ss:Type="String">@(codeItem[2])</Data></Cell>
<Cell><Data ss:Type="String">@(codeItem[0]==deb?"":vatCode)</Data></Cell>
</Row>
}
}
}
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
</WorksheetOptions>
</Worksheet>
</Workbook>