CSV File Generation
Introduction
The purpose of this document is to explain how to generate comma separated value (CSV) files from Patriot. This document describes CSV file generation in Patriot Version 6.1 and higher.
The Patriot CSV file generation module is an add-on which gives Patriot the ability to export all types of information stored in its database into CSV text file format. The module has been designed with external accounting software package integration in mind, but is not limited to this application. CSV files are simple text files which delimit data with a comma. Other character delimited file types (tab, white space, new line delimited files etc.) are likewise supported.
CSV file integration allows data to be exported from Patriot and then subsequently imported into a third party application such as accounting software. To use CSV file integration you must have purchased a registration for the accounting CSV module.
There are three main types of report used for accounting integration Periodic Billing (Type CSV), Activation Billing (Type CSVActivation) and Work Order billing (Type CSVWorkOrder).
Setup Master Charges
Master Charges must be created for each class of periodic billing fee (Billing > Master Charges). For example, after pressing the add charge button enter "Business Monitoring Fee" or "Standard Home Monitoring Fee" into the description field and select "Service" as the charge type indicating a regular periodic billing fee. The Charge Types "Activation" and "Work Order" should be used for "one-off" charges intended to be attached to particular Activations or Work Orders respectively. Enter the scale of the "one-off" or recurring charge noting that Patriot does not natively support multiple currencies, this is left to the supporting accounting software package, but it may be useful to specify the charges local currency in the description field or via ledger code. Save the new Master Charge before adding new charges or leaving the setup tab.
Setup Client Charges
From inside a client record select the Billing tab and click the add button on the client charges form.
Master charges, as discussed in the previous section will pop-up and the appropriate Master Charge can be selected. It is possible to have multiple charges for a particular customer; for instance a customer may have a monthly monitoring fee, a quarterly system seducing and maintenance fee and a monthly video surveillance fee.
You will be required to enter a start and end date for each service charge - this is very important because when you start a batch run any charges that don't have a valid start and end date will create a "Part Charging Dates" error, and Patriot is consequently unable to calculate the required part charging accounting sums.
Query
The Patriot billing module is designed to be flexible so that almost any information in the Patriot database can be extracted in the appropriate format and then imported into the third party billing software. To extract the required information an ESQL query must be created and stored. An ESQL query is similar to an SQL query but with some significant semantic differences. Normally it will be best to contact your distributor to get an ESQL query written to extract the information you require - there may be a small one off charge for writing the query.
Design an ESQL query such as:
SELECT
s.client_no,
SqlServer.GetDate() AS DATETIME,
s.externalrefno,
c.startdate,
c.enddate,
s.Account_No,
CAST(m.Amount AS Edm.Decimal(7,2)) AS Amount
FROM PatriotEntities.Memalarm AS s,
PatriotEntities.ClientCharges AS c,
PatriotEntities.MasterCharges AS m
WHERE S.Client_no = c.clientno AND
c.ChargeCode = m.chargecode AND
m.ChargeType = 2
Queries can be tested in Linqpad which can be downloaded directly from thw web - contact Patriot support for set-up instructions.
A more comprehensive query for dealer service billling with dealer and client overrides might look like this:
Dealer query
SQL Option two
select
i.contactphone2 as CardID,
-- This must be the ID of a receivable account
AddMonths(
SqlServer.GetDate(),
1
) as date_due,
'42800' as SalesLedgerCode,
CURRENTDATETIME() as date_invoice,
CURRENTDATETIME() as create_date,
(
SELECT
CAST(
SUM(
case when cc.AmountOverride is null then (
case when ic is null then mc.amount else ic.amount end
) else cc.AmountOverride end
) * 4.33 AS Edm.Decimal(7, 2)
) AS __InvTotalExclGst,
COUNT(
case when cc.AmountOverride is null then (
case when ic is null then mc.amount else ic.amount end
) else cc.AmountOverride end
) AS __InvLineCount,
CAST(
SUM(
case when cc.AmountOverride is null then (
case when ic is null then mc.amount else ic.amount end
) else cc.AmountOverride end
) * 4.33 * 1.1 AS Edm.Decimal(7, 2)
) AS __InvTotalInclGst,
CAST(
SUM(
case when cc.AmountOverride is null then (
case when ic is null then mc.amount else ic.amount end
) else cc.AmountOverride end
) * 4.33 *.1 AS Edm.Decimal(7, 2)
) AS __InvGst
FROM
PatriotEntities.memalarm as m
join PatriotEntities.usertoclient as utc on m.client_no = utc.clientno
join PatriotEntities.clientcharges as cc on utc.clientno = cc.clientno
join PatriotEntities.mastercharges as mc on cc.chargecode = mc.chargecode
left join PatriotEntities.installercharges as ic on ic.userid = i.userid
and ic.chargecode = mc.chargecode
where
m.currentworkgroup != 1
and utc.userid = u.userid
) as Aggregates,
(
SELECT
i.contactphone2 as bureau,
--CONCAT(CONCAT(CONCAT(CONCAT(m.Name, ' ('), m.Client_No), ') - '), mc.Description) as name,
m.Client_No as reference,
m.location as location,
mc.description as chargetype,
CAST(
case when cc.AmountOverride is null then (
case when ic is null then mc.amount else ic.amount end
) else cc.AmountOverride end AS Edm.Decimal(7, 2)
) as price_unit
from
PatriotEntities.memalarm as m
join PatriotEntities.usertoclient as utc on m.client_no = utc.clientno
join PatriotEntities.clientcharges as cc on utc.clientno = cc.clientno
join PatriotEntities.mastercharges as mc on cc.chargecode = mc.chargecode
left join PatriotEntities.installercharges as ic on ic.userid = i.userid
and ic.chargecode = mc.chargecode
where
m.currentworkgroup != 1
and utc.userid = u.userid
) as INVOICELINES,
u.userid as EXTERNALREF,
u.user_name as Dealername,
'@STARTDATE' as STARTDATE,
'@ENDDATE' as ENDDATE
from
PatriotEntities.muser as u
join PatriotEntities.installers as i on i.userid = u.userid
where
u.type = 'N'
and i.contactphone2 is not null
and i.contactphone2 != ''
Aggregates
In the Dealer query above a nested query column called 'Aggregates' - this is a special type of sub query for pulling out required aggregates such as counts and sub query totals etc. This nested query must be named 'Aggregates' and the column fields must have an alias prepended by a double underscore e.g.__InvTotalExclGst
Each aggregate value used must be set-up with an output format as shown in the following screen shot:
Aggregates
Nested Queries
A query can contain a nested query column for invoice or workorder lines as in the dealer query above. These must be set-up accordingly in the output format with the nested query name selected from the available query fields.
Nested Query
Nested query fields must immediately follow the nested query record and these must have the same field name as there respective nested query column in the query used.
Nested Query Field
To store this query from the reporting menu: Reports > Queries > Stored Queries. Give the query an appropriate name in the Description box and copy the query into the query box.
Preset Report
To set-up CSV file generation for periodic fee billing select Billing from the main tool bar and then select Batch setup and run from the billing menu.
If the Batch Types do not appear check that you have the correct module license registration.
Enter a descriptive Batch Name and select "CSV" as the batch Type. You can choose to have the CSV file generated automatically at predefined intervals or choose "Manual" to run and generate files when desired
If the Authorised check box is marked the batch will be run and CSV file generated without the opportunity to review providing there is no critical errors. If the Authorised option is not chosen then you will have the opportunity to review batch transactions in the transaction history area.
Batches can be generated for various period durations; yearly, half yearly, quarterly, bi-monthly, monthly, weekly. The first month of the financial year must be selected.
Various part charging run options are available; None is the default which means part periods will be free to the subscriber. For instance if a monitoring service began in January and the run frequency was monthly the first charge would be for the month of February. Whole means the whole period will be charged even though the service may have commenced or ceased part way through the period. Monthly means a minimum part charging period of a month and daily means the part charging is calculated from the exact date the service began.
A suitable stored query (explained in the previous section) must be selected to extract the appropriate data for the batch run.
Auto Run CSV Billing
To have a CSV file automatically generate at specified intervals set the Manual / Automatic radio button to Automatic and check the authorized check box. Select the first run date and the days or months interval that you want it to auto generate thereafter. This option will only be available for CSVWorkOrders and CSVActivation type billing runs. The days months interval settings are not available for CSV service reports and would not make sense to do this with recurring billing service runs which will run automatically anyway if set to automatic.
Import / Export
A comprehensive CSV report can take a considerable amount of work to set-up so usually it will be best to import an existing report and use this at least as a starting point.
Preset report settings can be exported to an XML file which can be useful to make a backup copy of the settings or to import later and for use as a template.
Preset report templates previously exported from Patriot can be imported back in and provide a good starting point for setting up CSV billing.
Output Format
The output format panel is opened by pressing the button beside the query selection field. This panel is a powerful tool for customising the formatting of a CSV file. The in-depth usage of the options on this panel is beyond the scope of this document and in most cases it is recommended you contact your Patriot Distributor regarding your requirements and a purpose built template will supplied. There may be a one time fee charged for the production of this template.
Data Types
The following data types are available for each output field:
- Text -any text that you want to include e.g. "Monitoring Fee for the period beginning: "
- Date - date / time e.g. period start date or end date.
- Integer - used for numbers that have no decimal places e.g. 3 widgets.
- Decimal - used for currency amounts e.g. $50.00 or quantities that have fractions e.g. 3.5 metres.
- Part Charging Decimal
- Exclude from Acct Query - some query fields are required but you may not want them in the output produced for your accounting software.
Date Time format specifiers
"d" Short Date format specifier with date set at 10th April 2008:
With US culture displays: 4/10/2008
With English culture displays: 10/04/2008
With German culture: displays: 10.04.2008
"D" Long Date Format:
With US culture displays: Thursday, April 10, 2008
"f" Short Date and Time Format:
Thursday, 10 April 2008 6:30 a.m.
"F" Long Date and Time Format:
Thursday, 10 April 2008 6:30:37 a.m.
Compulsory Fields
In a service batch run the following fields are compulsory:
- Client Charge Start Date
- Client Charge End Date
- Control Total (the line total - note this must not have before or after strings else it will fail)
- External Reference No
If any of the above fields are not required by your CVS format, use the "Exclude From Acct Query" option in the Output Format field properties window. Note that the "Part charging dates object ref. not set" error message is related to a missing compulsory field upsetting the part charging accounting calculation.
Invoice Numbering
Some systems such as Xero require you to include a unique number for each invoice. If the field type is a literal you can include the following character sequence in the literal field to retrieve a unique number that can be used to yield a generated invoice number :-
##INV##
When the CSV file is generated this sequence will be replace by the SQL database transaction ID or in the case where several transactions are combined on to a separate invoice it will yield the SQL ID of the first transaction on that invoice. The Quickbooks accounting software takes this a step further, requiring each transaction on an invoice to include a unique sequential ID number.
Some systems (e.g. Xero) require the invoice number to actually be on the invoice line - the same invoice number on two or more consecutive lines indicates to the billing system that those invoice lines are on the same invoice. You can use this tag in the InvoiceLines subquery also:
'##INV##' as InvoiceNumb .
It is also possible to add a string before or after it if this is a requirement.
For this system, and others like it, an additional character sequence has been provided:
##TRN##
This literal character sequence will be replaced with a sequential number starting from one and incrementing with each transaction on the invoice. An example of usage for the Quickbooks .IIF system might look like:-
##INV##-##TRN##
Field Types
There are two field types; Literal and Query. Literals are simple text strings entered into the text box labelled "Literal Content" and they will appear exactly as input into the generated CVS file. Query fields are the names of fields used in the select section of the ESQL query used in the preset report.
Some values, when used in the ESQL query, have special meaning built into the CVS module:
'@STARTDATE' - will be replaced in the ESQL query as the start date of the period for the particular batch run.
'@ENDDATE' - will be replaced in the ESQL query as the end date of the period for the particular batch run.
'@ExternalRef' - will be replaced by the External reference number of the client - only applicable where client updates from external accounting package - refer to Patriot support.
These variables are cast into the ESQL query as a DateTime, and therefore SqlServer functions can be used to format them as required.
Special Options
Surround Before - A literal text string to be inserted before the literal or query field content.
Surround After - A literal text string to be inserted after the literal or query field content.
Carriage returns, tabs, and line feeds can be introduced in the surround before/after fields of literal or query data types. These characters are referenced by their hexadecimal Unicode representations:-
A Carriage Return is indicated by:
\u000D
A Linefeed is indicated by:
\u000F
An Horizontal Tab is indicated by:
\u0009
Strip Characters - Strips a range of unicode characters from the field content.
For example strip all characters with unicode hexadecimal values 00h to 1Fh, 22h,60h,2Ch,27h:-
[\u0000-\u001F\u0022\u0060\u002C\u0027]
This option is useful for processing query fields which may return unhelpful data from the database, for example white space, horizontal tabs, linefeeds etc.
CSV Task Settings
A Patriot Task must be added to the active task list (Tasks > Task Settings) of type CSVMaker before your first CSV file can be exported. Again, note that the CSV File Generator is an add-on module and your Patriot registration must be updated (contact your software distributor) with a license for the module before this task can be added.
Activation Billing
Action plans can have a default charge assigned so that in the event of an activation created in the signal Hub that charge type will automatically be assigned to the activation and this will have a bearing on our the charge status is set. The attend charge status will default to Not Charged if the Action Plan Action Plan has a Master Charge assigned that has a zero ($0.00) dollar value charge or if the Action Plan has No Master charge assigned. In this case the fee on the attend should also be set to $0.00. If the Action Plan has a Master Charge assigned with a value > $0.00 then the charge flag should be set to Chargeable and the fee on the attend should be set to the Master Charge fee.
In SQL the where charge has the following values:-
NotCharged = 0
Charged = 1
Chargeable = 2