APPENDIX C. Advanced template customizations

You can use extra tools to customize templates so that information in invoices is provided in the most convenient and suitable form for your customers. Advanced template customization makes it possible to sort, group and sum up xDR information in the ways you need.

To do this, use the template toolkit engine. Discover its syntax on this website: http://template-toolkit.org.

PortaBilling clients are encouraged to create customized templates themselves. To make this task easy for you we provide typical algorithms that are useful for the most common queries.

You may need to display your xDRs grouped by account ID, destination country or some other key parameter. To do this, follow this basic algorithm:

1. Define a key parameter for sorting and grouping. The SET directive updates the variable’s value:

   [% SET row = iter.next_row('key') -%]

   In this statement, the template engine forms a DB query and assigns the first entry from the xDR tables to the row variable. The variable called key in our example defines the key parameter for the xDR grouping. To insert your own key parameter use its DB name from the xDR row section of Appendix A.

   The following table rows can be obtained by consecutive calls to iter.next_row(’key’).

   The default value for the key variable is billing time – xdr_bill_time. It will be applied if you do not mention any key:

   [% SET row = iter.next_row() -%]

2. Use WHILE loop and the following block of code to define and group xDRs with the same key parameter:

   [% WHILE row -%]
     [% IF prev_row AND prev_row.key != row.key -%]

   The prev_row variable stores the previous value of the row variable. The template engine compares these values to find if the key values differ in these adjacent rows. If they do differ, it means that the previous group of xDRs has ended and a new group starts.

3. Output data by the key parameter:

   <tr>
      <td> Result is [% prev_row.key %]</td>
   </tr>

To see how to apply this algorithm for the template, please consider the code with detailed comments. In this template the system sorts and groups xDRs by account ID (account_id), and then calculates and outputs the total amount for the calls from each account:

<!-- Define the service for a table of your template. It must be one of the services represented in the xDRs. In this example we will work with voice calls (i_service 3). Refer to the Choosing the service subsection for detailed information -->
[% SET iter = services.3 -%]

<!-- Previous row is undefined -->
[% SET prev_row = undef -%]

<-- Reset the counter -->
[% SET total_amount = 0 -%]

<!--Select the key parameter for sorting. In this example – account ID -->
[% SET row = iter.next_row('account_id') -%]

<-- Repeat for all xDRs for a current account. The WHILE loop repeats until all account xDRs are processed -->
[% WHILE row -%]

<-- If the values of key parameter differ in any two adjacent rows, it means that the previous group of xDRs has ended and a new group starts. In this example, the xDRs for the previous account has ended, so the billing engine outputs the subtotals for it, resets the counters and then processes the next account’s xDRs -->
[% IF prev_row AND prev_row.account_id != row.account_id -%]

<!-- Output subtotals for the previous account -->
<tr>
<td>Total for the account #[% prev_row.account_id %]</td>
<td>[% total_amount %]</td>
</tr>

<!-- Reset the counters for the next group. In our example - for the next account -->
[% SET total_amount = 0 -%]
[% END -%]
<!-- Sum up the values for a particular group of xDRs. To do this, add the current xDR’s value to the total sum -->
[% SET total_amount = total_amount + row.xdr_charged_amount -%]
[% SET prev_row = row -%]
[% SET row = srv.next_row -%]
[% END -%]
<-- The WHILE section’s code ends and the loop repeats anew -->

You may need to group the xDRs by two or more parameters. For instance, if your end users often make calls abroad, then you can provide information regarding how much each call to each foreign country costs. For this template two key parameters are needed – account ID (account_ID) and destination country (country_name).

Let’s consider the algorithm of grouping by two parameters:

1. Define the variables for counting and set them to zero:

   [% SET total_amount = 0 -%]
   [% SET country_amount = 0 -%]

2. Define the key parameters for sorting and grouping using a comma:

   [% SET row = iter.next_row('account_id,country_name') -%]

   When you apply this statement, data is sorted in the following way (account ID, destination country, total amount spent):

   11125557100, Austria, $5
   11125557100, Austria, $3
   11125557100, Brazil, $10
   11125557100, Brazil, $3
   18887123555, Austria, $1
   18887123555, Austria, $3
   18887123555, France, $5

3. Use the WHILE loop and compare the adjacent rows in order to group the xDRs with the same key parameters:

   [% WHILE row -%]
   [% IF prev_row %]
   [% IF prev_row.account_id != row.account_id -%]

4. Count subtotals for the groups of xDRs that belong to the current account and current destination country:

   [% SET total_amount = total_amount + row.xdr_charged_amount -%]
   [% SET country_amount = country_amount + row.xdr_charged_amount -%]

   When the WHILE loop is executed for the first time, the prev_row variable is empty, so the variables for counting are simply updated:

   total_amount = 5 (0+5)
   
   country_amount = 5 (0+5)

   After this, the row with 100, Austria, $5 data is saved to the prev_row variable. When the WHILE loop is executed for the second time, the system finds out that the account_id and country_name in the adjacent rows are the same. That is why the variables for counting are easily updated:

   total_amount = 8 (5+3)
   country_amount = 8 (5+3)

5. Output the total amount for the service when a key parameter (either destination country or account ID) in the adjacent rows differs.

In the third WHILE loop execution, the account remains the same for the adjacent rows, but the countries differ. It means that it is time to output the variable for counting total amount per country:

<tr>
   <td> Total amount for calls to [% prev_row.country_name %] for the account #[% prev_row.account_id ] is [% country_amount %]
</td>
</tr>

In this case, the invoice will represent the following information: Total amount for calls to Austria for account #100 is $8. Zeroize the variable for counting the total amount per country:

<!-- Reset the counter for a country -->
 [% SET country_amount = 0 -%]

After this, the WHILE loop processes the next country for the same account.

We provide the full code of the current example with detailed comments about each fragment:

<!-- Define the service for your template’s table. It must be one of the services represented in the xDRs. In this example we will work with voice calls. Refer to the Choosing the service subsection for detailed information -->
[% SET iter = services.3 -%]

<!-- Previous row is undefined -->
[% SET prev_row = undef -%]

<-- Reset the counters -->
[% SET total_amount = 0 -%]
[% SET country_amount = 0 -%]

<!--Select the key parameters for sorting. In this example – account ID and destination country -->
[% SET row = iter.next_row('account_id,country_name') -%]

[% WHILE row -%]
[% IF prev_row %]
[% IF prev_row.account_id != row.account_id -%]

<!-- Output subtotals for the current account if further xDRs belong to the next account -->

<tr>
<td>Total amount for the account #[% prev_row.account_id %]</td>
<td>[% total_amount %]</td>
</tr>

<!-- Reset the counters -->
[% SET total_amount = 0 -%]
[% SET country_amount = 0 -%]

[% ELSIF prev_row.country_name != row.country_name -%]

<!-- Processing the next country name for the same account -->
<!-- Output subtotals for the current country as further xDRs represent calls to the next country -->
<tr>
<td>Total for the account #[% prev_row.country_name %]</td>
<td>[% country_amount %]</td>
</tr>

<!-- Reset the counter for a country -->
[% SET country_amount = 0 -%]
[% END %-]
[% END -%]

<-- Update all counters -->
[% SET total_amount = total_amount + row.xdr_charged_amount -%]
[% SET country_amount = country_amount + row.xdr_charged_amount -%]
[% SET prev_row = row -%]
[% SET row = iter.next_row -%] 

[% END -%]

Before processing the xDRs, you must specify which service the xDRs were created for. To do this, you need the service’s unique database ID that can be found in the Services table of the PortaBilling database. To specify a service, use this code at the very beginning of a template:

[% SET iter = services.X -%]

where X indicates the service’s ID.

Predefined services with their unique IDs can be found in the following table:

If required for business, an ITSP can create customized services via the administrator web-interface. To process the xDRs of such customized services, the administrator must retrieve the service’s unique ID from the Services table. There are two ways to do this:

1. API methods – Use get_service_list to see the full list of services.
2. SQL query – Input this query to see customized services:

   'SELECT i_env, i_service, name FROM Services WHERE i_env != 0'

One day you might want to expand your business and provide additional services. For this reason it would be good to have an adjusted template to represent charges for new services. In order to check whether other services need to be output to the template, use this code:

[% FOREACH srv = services -%]
[% NEXT UNLESS srv -%]
[% SET i_service = srv.i_service %]
[% SER service_name = srv.name %]

This code seeks each service from the Services table, one by one. If a service is found in the xDRs, start the process of sorting and grouping as described previously.

In addition, we provide the full code for the system to check whether other services are present. If they are, they are sorted and grouped by their key parameters. We refer to the previous case with multiple key parameters – account ID and destination country.

<-- Searching for other services -->

[% FOREACH srv = services -%]
[% NEXT UNLESS srv -%]
[% SET i_service = srv.i_service -%]

<!-- Skip the already processed service -->
[% NEXT IF i_service == 3 -%]

!-- Skip the internal 'Cleanup' service -->

[% NEXT IF i_service == 11 -%] 

<!-- This service refers to taxes. We skip them in this example, but do not forget to process them if it is required for your business -->
[% NEXT IF i_service == 12 -%]

<!-- Skip internal 'Placeholder' service -->
[% NEXT IF i_service == 100 -%] 

<!-- Get service name -->
[% SET service_name = srv.name %]

<!-- Use name 'iter' instead of 'srv' for convenience -->
[% SET iter = srv %]

<!-- output xDRs for service -->

<!-- Previous row is undefined -->
[% SET prev_row = undef -%]
<-- Reset the counters -->
[% SET total_amount = 0 -%]
[% SET country_amount = 0 -%]
<!--Select the key parameters for sorting. In this example – account ID and destination country -->
[% SET row = iter.next_row('account_id,country_name') -%]

[% IF row %]
<!-- if have at least one xDR - show header with service name -->
<tr>
<td colspan=2>Service: [% service_name %]</td>
</tr>
[% END %]

[% WHILE row -%]

[% IF prev_row %]
[% IF prev_row.account_id != row.account_id -%]
<!-- Output subtotals for the current account if further xDRs belong to the next account -->
<tr>
<td>Total amount for the account #[% prev_row.account_id %]</td>
<td>[% total_amount %]</td>
</tr>
<!-- Reset the counters -->
[% SET total_amount = 0 -%]
[% SET country_amount = 0 -%]
[% ELSIF prev_row.country_name != row.country_name -%]
<!-- Processing the next country name for the same account -->
<!-- Output subtotals for the current country as further xDRs represent calls to the next country -->
<tr>
<td>Total for the account #[% prev_row.country_name %]</td>
<td>[% country_amount %]</td>
</tr>
<!-- Reset the counter for a country -->
[% SET country_amount = 0 -%]
[% END %-]
[% END -%]
<-- Update all counters -->
[% SET total_amount = total_amount + row.xdr_charged_amount -%]
[% SET country_amount = country_amount + row.xdr_charged_amount -%]
[% SET prev_row = row -%]
[% SET row = srv.next_row -%]
[% END -%]

<!-- end of services loop -->
[% END %]