Data fields describe a single element of data, such as "invoice number", "price" or "company name". For each data field in the template you specify the following:
Target columns (for download templates)
Target columns define where to position the result value. If you want to skip the downloading of specific data, choose None for this data field. To change the target column, click Edit and select the new column value in the Column list. Multiple data fields can be populated from the same target column.
Format
A format describes data fields so that they may be converted from the database (default) format to a specific format during download.
For example, if you choose format "011E.164" for the "Destination" data field, then the template engine will know that you require the downloaded numbers to be in a US dialing format, so the international number must be prefixed by "011." Therefore, "011" will be prefixed to the number "4201234567" during the download, so the number in the output file will be "0114201234567."
In order to provide better quality control of data, PortaBilling supports several data types. These define what kind of data appears in the data field, as well as possible ways to process it. Currently supported data types include:
- Destination (phone number).
- Date/time
- Number
- Time interval (number of minutes/seconds)
- Text
- Boolean (yes/no)
A data type is predefined for each data field, so you do not need to specify one. For each data type in PortaBilling there is a set of default formats so that you will usually not have to invent anything from scratch. If you encounter a data format for which there is no suitable data type in PortaBilling, you may choose "Other". You will then be able to specify a custom format rule.
"Other" format rule
If none of the PortaBilling default formats suit your needs, you can choose "Other" and enter your own formatting rule. See below for a description of how a format rule works for data fields of a particular type.
Number
For numeric data fields (for example, "First price" or "Connect fee"), the value you enter in the Other Format field is considered to be the format specification ###,###.##, where:
- A # sign or the digits 0-9 represent one digit of the input or output number.
- The first character from the right which is not a digit or # (e.g., in #,###.00) specifies a decimal separator.
- The first character from the left which is not a digit or # (e.g., in #,###.00) specifies a group separator.
- The number of characters after the decimal separator specifies the precision of the number. However, # indicates no zero padding, and 0 indicates zero padding.
- If the number has too many digits for the format specification, it will be rounded to the number of decimal digits specified in the format.
- A group separator (if present) specifies which character should be used to split groups of 3 digits in the number. If none is specified, then no group separation will be done.
- It is sufficient to specify a group separator only once, even if your real numbers will be large and contain more than one digit group. All digits to the left of the decimal point are always shown, regardless of how many of them are actually shown in the format.
This is important mainly for download templates, where the format or precision you use to present numbers in documents differs from the format in which data is stored in the database. For upload templates, number format specification is mainly used to handle non-standard group or decimal separators. See the table below for examples of formats for converting numbers.
Format |
Number |
Result |
Comment |
---|---|---|---|
#,###.## |
1.6 |
1.6 |
No zero padding |
#,###.00 |
1.6 |
1.60 |
Zero padding |
#,###.00 |
34567.6 |
34,567.60 |
Digit groups |
####.00 |
34567.6 |
34567.60 |
No digit groups |
#.# |
2.382 |
2.4 |
Rounding |
#.# |
2569.31 |
2569.3 |
Significant part is never truncated |
#.###,## |
2569.31 |
2.569,31 |
German format |
# ###,## |
7892569.31 |
7 892 569,31 |
Yet another custom format |
Table 1-1 – Examples of number formats.
Date and Time
This type is used for data fields that contain either both the date and time (e.g., Effective from) or just the date (e.g., Invoice date). Each data field has a default format. For instance, for Period From Date on the Field tab of the invoice template it is YYYY-MM-DD. This means that end users see dates in this format: 2015-08-23. All of the date and time format specifiers are listed below:
- YYYY – year (four digits, e.g., 2009).
- YY – year (short format, e.g., 05). Note that this format can lead to potential data interpretation problems, since "10" could be either 2010 or 1910. The PortaBilling template engine considers the year values 00-30 to be in the 21st century, and 31-99 to be in the 20th century.
- MM – number of the month (01…12).
- MON – abbreviated name of the month (e.g., Jan). Only English names of the months are currently supported.
- MONTH – full name of the month (e.g., January). Only English names of the months are currently supported.
- DD – day of the month with a leading zero for 1 through 9; for example, 01, 06, 14, 25.
- D – day of the month without a leading zero for 1 through 9; for example, 1, 6, 14, 25.
- HH or HH24 – hour (24-hour format).
- HH12 – hour (12-hour format).
- AM/PM – a.m. or p.m. indicator.
- MI – minutes.
Very often people make the mistake of using MM as a format for minutes. This is incorrect, as MM is a format for month. Thus, HH:MM:SS will clearly not work correctly.
- SS – seconds.
- TZ – time zone name (e.g., CET).
- XXX – any combination of non-space characters; used in formats for parsing dates to skip a variable part of the date, for example, the name of a weekday (see examples below in Table 1-2).
- All other symbols in the format (spaces, delimiters like /, -, : or ; and other characters) represent themselves. So they are placed as-is in the output date string, and must be found in the input string exactly as they were entered.
PortaBilling administrators can customize date and time formats according to their needs and we show you how to do it for different template types.
Rate upload and download templates
Specify your customized date and time format for the required data field within the Other Format Rule field. Click Save to apply the changes:
Let’s take a look at some examples for importing dates into:
Date/time string |
Description |
Correct format specification |
---|---|---|
07/04/2009 |
July 4th 2009, US date format |
MM/DD/YYYY |
04-07-2009 |
July 4th 2009, European date format |
DD-MM-YYYY |
07-04-09 |
July 4th 2009, US date format, short year format |
MM-DD-YY |
11:05 a.m. |
Time, 12-hour format |
HH12:MI AM/PM |
11:05 |
Time, 24-hour format |
HH:MI |
18:58:00 |
Time with seconds |
HH:MI:SS |
2009-07-04 14:00:00 |
Date and time, ISO format |
YYYY-MM-DD HH:MI:SS |
07/04/09 2:00:00 p.m. |
Date and time, US format |
MM/DD/YY HH12:MI:SS AM/PM |
1-May-2009 18:52 |
Date and time, abbreviated month name |
D-MON-YYYY HH:MI |
Sun, 16 Mar 09 |
Date and time, skip weekday name |
XXX D MON YY |
12th of March, 2009 |
Date with “as is” elements |
Dth of MONTH, YYYY |
Table 1-2 – Examples of date and time formats for upload.
The following table gives examples for using templates to convert values in the PortaBilling database to the desired format.
Date/time in |
Format specification |
Resulting value |
---|---|---|
01-May-2014 12:34:56 |
MM/DD/YYYY |
05/01/2014 |
01-May-2014 12:34:56 |
DD-MM-YYYY |
01-05-2014 |
01-May-2014 12:34:56 |
MM-DD-YY |
05-01-14 |
01-May-2014 12:34:56 |
HH12:MI AM/PM |
12:34 p.m. |
01-May-2014 12:34:56 |
HH:MI |
12:34 |
01-May-2014 12:34:56 |
HH:MI:SS |
12:34:56 |
01-May-2014 12:34:56 |
YYYY-MM-DD HH:MI:SS |
2014-05-01 12:34:56 |
01-May-2014 12:34:56 |
MM/DD/YY HH12:MI:SS AM/PM |
05/01/14 12:34:56 p.m. |
01-May-2014 12:34:56 |
D-MON-YY |
1-May-14 |
01-May-2014 12:34:56 |
D of MONTH, YYYY |
1 of May, 2014 |
01-May-2014 12:34:56 |
YYYYMMDD HHMISS |
20140501 123456 |
Table 1-3 – Examples of date and time formats for download.
Invoice template
To apply your customized date and time format for the invoice template, insert this code into the Other Format Rule field of the required data field:
Porta::Date->new_iso($value)->asCustomFormat('DD-MM-YYYY')
where 'DD-MM-YYYY' is an example of date format.
Parsing module
Please note that when importing data that has dates in a customized format (e.g., rate upload), you can use the Time::ParseDate module to recognize the date format. This module examines a data string and finds bits of data, regardless of how they are placed or separated there. In order to use the Time::ParseDate module for parsing dates, choose "Auto" as the format.
Unfortunately, in some situations even Time::ParseDate is unable to properly identify the date. For example, the string "02-01-2009" could either mean February 1st or January 2nd. In such situations, an exact format specification is required.
Other data types
For other data types (e.g., Destination, Text, or Interval) there are no specific format elements. The "Other" format rule is regarded as a Perl macro; thus it is evaluated, and the result of the evaluation is considered a new value. For a detailed description of Perl macros, see the Post-processing rules section below. The original string value is received as a parameter, and the converted value should be returned in the required format.
The following table provides a description of which type of output value is required for other format rules, depending on the data field type.
Data field type |
Other format rule in |
Output value |
---|---|---|
Destination |
Perl code |
string |
Number |
Number format |
N/A |
Boolean |
Perl code |
string (‘Y’ or ‘N’) |
Interval |
Perl code |
number |
DateTime |
Date/time format |
N/A |
Text |
Perl code |
string |
Table 1-4 – Output value type for other format rules.
Post-processing rules
Sometimes you will need to perform a very specific conversion which is not possible using the standard PortaBilling formats. In this case, you can use post-processing rules. A post-processing rule is a piece of Perl code that is executed at run-time, and the value it returns becomes the final value for a field. This is similar to formulas or macros in Excel, giving you a virtually unlimited ability to change your data as you need to. There are a few requirements for writing post-processing rules:
- A processing rule is Perl code, i.e. one or more Perl operations separated by semicolons.
- The input value (to be processed) is in the $value variable.
- The final result is the value returned by the last expression in the list.
TIP: One of the most useful things post-processing rules can do is to create new data which is not based on the input data. This is required, for example, when certain data fields (e.g., Interval 1 and Interval N) are absent from the input file. If you know that Interval 1 is 30 seconds, and Interval N is 6 seconds, you can simply assign these values as constants in the post-processing rule.
Let’s look at a few examples:
Post-processing rule |
Input data |
Result |
---|---|---|
Remove all leading white space from the string |
||
$value =~ s/^\s+//;$value |
Aruba |
Aruba |
Remove all leading and trailing white space from the string |
||
$value =~ s/^\s+//; $value =~ s/\s+$//;$value |
Aruba |
Aruba |
Add 1 before the phone number, but only if it does not start with 0 |
||
$value =~ m/^0/ ? $value : '1'.$value; |
6041234567 |
16041234567 |
Take only the part of the input string following the hyphen |
||
my @a = split('-', $value, 2); $a[-1]; |
Norway-Oslo |
Oslo |
Assign a constant value to the field |
||
$value = 30 |
None |
30 |
30 |
None |
30 |
Replace all hyphens with spaces and remove all quotes |
||
$value =~ s/-/ /g; $value =~ s/\”//; $value |
Germany-'Berlin' |
Germany Berlin |
Use an external (custom) module to translate English country names into Czech |
||
use Czech::Translate; transl_data($value); |
Austria |
Rakousko |
Table 1–5 – Examples of using post-processing rules.
- Programming Perl by Larry Wall, Tom Christiansen, Jon Orwant (ISBN 0-596-00027-8)
- Learning Perl by Randal L. Schwartz, Tom Phoenix (ISBN 0-596-00132-0)
- Perl Cookbook by Tom Christiansen, Nathan Torkington (ISBN 1-56592-243-3)
Values returned by a post-processing rule should have the same type as the input value it receives. The following table provides information about types of input values for post-processing rules, depending on the data field type.
Data field type |
Input value ($_) |
---|---|
Destination |
String |
Number |
number |
Boolean |
string (‘Y’ or ‘N’) |
Interval |
number |
DateTime |
unixtime |
Text |
string |
Table 1–6 – Input value type for post-processing rules.