Invoicing

Invoice templates

Invoice templates are the XHTML documents with embedded macros that are converted into the XSL Formatting Objects using the ~ssp/etc/xsl/xhtml2fo.xsl, the slightly modified version of the style sheet from http://antennahouse.com/. The resulting XSL FO document is finally converted into the PDF file using Apache FOP. Starting from Sippy 5.0, version wkhtmltopdf is used as PDF converter.

The invoice templates are stored in the database, table invoice_templates. The templates with the i_invoice_template field values in the 1 to 1000 range are system templates, they are additionally filed as:  invoice_templates.is_system = True

Invoice templates for accounts

An invoice template is a mandatory attribute of a Service Plan (billing_plans.i_invoice_template).  The invoice template has a system default value (see below).  Accounts have optional attributes for invoice templates (accounts.i_invoice_template) which can override the setting from the Service Plan.  Accounts also have settings to enable the generation of invoices.  Invoices can be sent via e-mail to addresses specified in the contacts of the Account, filed as: accounts.invoicing_enabled

On the Sippy interface the Account can view the type of invoice which is enabled by checking "Invoicing Enabled" in his settings (the sub-Account, after logging in can change it.)

Invoice templates can be chosen from the Invoice Template list after invoicing is enabled.  

The same could be done using the bulk upload feature for multiple Accounts.

Currently assigned system templates

• 1 - The default invoice template for accounts

Creating a custom invoice template

It is recommended that you avoid changing the Default invoice template.  By using the following strategy you'll always keep one Default working invoice template and will be able adjust a custom one on your own.

1. Log into your switch and open the page which contains a list of all invoice templates. Starting from 5.2 version of Sippy the page is available under Customers - Invoicing - Invoice Templates menu, in earlier versions it is hidden, so you need to type "invoice_templates.php" manually in your browser's address line. The full link looks as https://11.22.33.44/c1/invoice_templates.php (where 11.22.33.44 is the address or domain name of your switch).
2. By default, only one template is available called "Default...". Open it and copy the HTML content of the Default invoice template into a clipboard or a notepad, or press Add Based on Existing button on Invoice Templates page (starting from 5.2)

3. Next, go back to the Invoice Templates page and click "Add New Invoice Template".

4. Open HTML editor again.

5. Paste the HTML content which you copied at step #2.

6. Save the template. Now you have a full copy of the Default working invoice template which you can customize on your own.

Available macros list

Starting from 5.0 version of Sippy the list of available macros are accessible from invoice editor:

Macros that contain the attributes of an account:

• ${I_INVOICE} - Unique invoice identifier
• ${I_ACCOUNT} - Unique account identifier
• ${USERNAME} - Account name
• ${SALUTATION} - Salutation from account preferences. Mr./Mrs.
• ${FIRST_NAME} - First name from account preferences
• ${LAST_NAME} - Last name from account preferences
• ${CURRENCY} - Account base currency
• ${STREET_ADDR} - Street address from account preferences
• ${POSTAL_CODE} - Postal code from account preferences
• ${CITY} - City from account preferences
• ${COMPANY_NAME} - Company Name from account preferences
• ${COUNTRY} - Country from account preferences
• ${STATE} - Province/State from account preferences. (starting from Sippy 2020)
• ${BALANCE} - The balance of an account at the end of the billing period. (starting from Sippy 2021)

Macros containing the current invoice information are:

${TOTAL_CHARGES} - Total sum of all minute plans and charges from the Service Plan

${PAYMENTS_AMOUNT} - Amount of payments for the current billing period

${FORWARD_AMOUNT} - Unpaid amount from the previous period

${LAST_BILL_AMOUNT} - Amount due from the previous invoice

${AMOUNT_DUE} - Amount account owes at the end of the billing period. Will contain non-zero values only if the account's balance is below zero.

${TARIFF_CALLS_PRICE} - Total sum of the calls exceeding the service plan minutes.

${TARIFF_CALLS_DURATION} - Total billed duration of the calls exceeding the service plan minutes. Format of duration is [hh:]mm:ss

${PLAN_CALLS_DURATION} - Total billed duration of the calls within the service plan minutes. Format of duration is [hh:]mm:ss

${ALL_CALLS_DURATION} - Total billed duration of all calls. Format of duration is [hh:]mm:ss.

${ADDITIONAL_CHARGES} - Charges that were applied in the current billing period in addition to the ${TOTAL_CHARGES}

${BILL_DATE} - Date at the moment of an invoice creation

${LAST_BILL_DATE} - Date at which the previous invoice was issued

${TOTAL_DID_CALLS_CHARGES} - Total sum of DID calls charges to the account

${INVOICE_START_DATE} - Start date of invoice period. (starting from 2021)

${INVOICE_END_DATE} - End date of invoice period. (starting from 2021)

Tables:

• ${TABLE:service_plan_charges} - Contains all fields from the service_charges database table.

• ${TABLE:service_plan_minutes} - Contains all fields from the service_plans database table.

• ${TABLE:charges} - Contains all charges applied to the account for the billing period. A table that could include service charges, minute plan fees, DID monthly fee and DID setup fee. Available fields within the table:
• charge_time - date and time of the charge
• date - date only part of the charge_time
• amount - positive number of the charge applied
• currency - currency
• name - name of the charge
• description - description of the charge
• _num - Sequential number of the record

• ${TABLE:cdrs} - Contains all fields from the cdrs table plus the following additional fields that should be placed inside the TABLE macros:

• _num - Sequential number of the record
• date - Date part of the connect_time field
• time - Time part of the connect_time field

           Examples of the macro within ${TABLE:cdrs}:

• ${TD:cld_in} - cld_in field
  
• ${TD:cost} - cost field
• ${TD:duration;fmt=duration} - The 'duration' format displays value in the format [hh:]mm:ss
• ${TD:country} - country from Destinations resolved based on prefix. Starting from Sippy 2020
• ${TD:description} - Description from Destinations resolved based on prefix. Starting from Sippy 2020

The cdrs table accepts two optional parameters:

• max_rows - Maximum number of CDRs to display in invoice, default is 100
• show_zero_cdrs - Display zero duration CDRs in invoice, default is false

Examples:

• ${TABLE:cdrs;max_rows=0} - fetch unlimited number of CDRs
• ${TABLE:cdrs;show_zero_cdrs=true;max_rows=10000} - display zero duration cdrs in the invoice, max 10000 records

• ${TABLE:cdrs_summary} - Table with summary for data stored in cdrs table plus the following additional fields that should be placed inside the TABLE macros. Available starting from Sippy2022

• _num - Sequential number of the record

           Examples of the macro within ${TABLE:cdrs_summary}:

• ${TD:num_calls} - number of calls that hit this destination for an Account. 
• ${TD:effective_rate} - effective rate: Cost / Duration (per min)
• ${TD:duration;fmt=duration} - summary duration for the calls in this group, in seconds. The 'duration' format displays value in the format [hh:]mm:ss. 
• ${TD:country} - country from Destinations resolved based on prefix. E.g. CANADA or ITALY
• ${TD:description} - Description from Destinations resolved based on prefix. E.g. Mobile, Milan or Ontario
• ${TD:cost} - summary of the cost for the calls in this group

The cdrs_summary table accepts two optional parameters:

• group_by - Aggregate data based on specified fields, default is group_by=country,description. It must be one of the following 3 patterns.

1. group_by=country,description (group_by=descripton,country is also applicable)
2. group_by=country
3. group_by=description

• order_by - Sort records based on specified fields, default is country in ascending order. One of keywords are available.

1.ASC - sort field in ascending order. Without it, it's considered as ASC (e.g. country or country:ASC)
2.DESC - sort field in descending order. (e.g. country:DESC)

if multiple fields need to be applied, the format must be as follows:
order_by=field1:ASC,field2:DESC,....
(e.g. order_by=country:ASC,cost:DESC,duration)

Grouping and ordering of data

Parameters group_by and order_by have to meet one of conditions: 

1. If group_by=description, country can not be chosen for order_by.

2. If group_by=country, description can not be chosen for order_by. 

3. If group_by is country,description(or description,country), country and description are applicable for order_by.

There might be some instances where {TD: description} is inserted into a template, however group_by meets 

the condition, group_by=country. In this case, {TD: description} is displayed as Unknown. The same logic is

applied  where {TD: country} is inserted into a template, however group_by is used as group_by=description. 

Examples: 

Following data is going to be grouped by country.

An invoice template is customized as follows:

Generated invoice will be as follows:

NOTE:

If optional parameters are not properly described, errors will be raised or

following default value will be set.

group_by=country,description

order_by=country:ASC

Examples of valid and invalid combinatons:

Valid: 

1. ${TABLE:cdrs_summary;group_by=country;order_by=country:DESC} - return records grouped by country in descending order.

Invalid:

1. ${TABLE:cdrs_summary;group_by=description;order_by=country:DESC} - It will be an error, It doesn't meet condition described above:
If group_by=description, country can not be chosen for order_by.

2. ${TABLE:cdrs_summary;group_by=country;order_by=description} - It will be an error It doesn't meet condition described above:
If group_by=country, description can not be chosen for order_by.

3. ${TABLE:cdrs_summary;group_by=description;} - It will be an error. order_by is not specified and it means that the default order_by, order_by=country:ASC is applied. The macro is regarded as the following macro:
${TABLE:cdrs_summary;group_by=description;order_by=country:ASC}
It is the same case as Invalid case 1.

Macro substitutions

Invoice template can contain macros. The macros can be of two types - common and context specific.

• Common macros

• Macros to create tables

To represent a table data there are a macros ${TABLE}, ${TD} and ${TABLE_END} which are designed to be used as shown in the example:

<table>
<tr>
<th>Call</th>
<th>Date</th>
<th>Time</th>
<th>Number Called</th>
</tr><tr>
<td>${TABLE:cdrs}</td>
</tr><tr>
<td>${TD:_num}</td>
<td>${TD:date}</td>
<td>${TD:time}</td>
<td>${TD:cld_in}</td>
</tr><tr>
<td>${TABLE_END}</td>
</tr>
</table> 

All rows between the macros ${TABLE} and ${TABLE_END} are repeated as many times as many rows has to be displayed, and the HTML table rows containing these macros are always skipped and never included in the final document.

EVAL macro

With this macro you are allowed to make arbitrary arithmetical calculations and place them on the page in a desired format. The general form for the macro is:

${EVAL:<expression>[;<format>]}

Examples:

Round the result to second digit after the dot:

${EVAL: ( ${TOTAL_CHARGES} ) ; %.02f }

Perform the division with 3 and round to the second digit after the dot:

${EVAL: ( ${LAST_BILL_AMOUNT} / 3.0 ) ; %.02f }

Some arithmetic operation:

${EVAL: ( ${TD:_num} + 15 ) / 2 }

Related web pages:

Customers - Invoicing - Invoice Templates

Customers - Invoicing - Images

Customers - Invoicing - Invoices

List of supported formats for the images:

JPG, JPEG, PNG, GIF(static)

List of supported fonts:

Sans Serif

Monospace

Serif

Custom CSS

Starting from our Sippy 5.0 version Our users can now add their own custom CSS to their own invoice template.  The Invoice Template will have a field specifically for CSS so you can brand your invoices as needed.  The style sheet just needs to be copied into the field below.  A very simple example is provided below and the sample out put is shown.

**

To help you make use of this feature here is the sample Invoice template updated to include a basic style sheet and the resulting invoice template.

body {
  background-color: #ffffff;
}

h2 {
  colour: orange;
  background-color: #ff9900;
  border: 1px solid #544f4f;
}

p, em {
  colour: orange;
  background-color: #ff9900;
  border: 1px solid #544f4f;
}

p, li {
  text-decoration: underline;
}

Example of the final output:

Test(validate) Invoice template

It's possible to generate test invoice based on modified invoice template to see the expected result prior to the billing pass. 

Navigate to needed Account on Accounts page, in its Advanced parameters select Generate Invoice and specify needed period.

XMLAPI call could also be used for that purpose, see documentation:

https://support.sippysoft.com/solution/articles/3000080953-xml-rpc-api-testinvoice