Home

PortaBilling: User Manual

1. Address https demo portaone com 8060 tariff html v ag Go Gf M Edit Termination to PowerTel Tariff gt te Save fl Save amp Close Xj Download amp Upload Edit Rates Currency USD Name Termination to PowerTel Customer ANY General Info Web Upload Download Email Upload Notepad Default Upload Template PowerTel upload Excel Default Download Template Not defined Done 8 S Internet a Figure 4 7 Now we ate ready to upload the tariff data Upload tariff data Click on Upload so the upload screen will open It is possible to type the name of the input file manually but it is more convenient to use Open file dialog click on the Browse to invoke it c 2001 2006 PortaOne Inc All rights Reserved www portaone com 46 PORTA Billing1 00 Practical session File Edit View Favorites Tools Help Q pack v ix a P Search Se Favorites media e B a 5 PIX B emacs d BJPrinter MyRecent Documents and Settings Documents Download 3 Games Inst Desktop My Documents 3 My Music e2 Prog Program Files My Documents Temp utils C WINDOWS My Computer 2 zpl File name Files of type all Files FA l Figure 4 8 After the file name is entered into the form we can click Save amp Close This will transfer the file to the server where the processing will start NOTE If you had the input file
2. Table 1 4 Output value type for the other format rules Post processing rule Sometime you need to perform some very specific conversion which is not possible with the standard PortaBilling formats In this case you can use post processing rules A post processing rule is a piece of Perl code which will be executed at run time and the value it returns will become the final value for the field This is something similar to formulas or macros in Excel and gives you virtually unlimited ability to change your data the way you need There are a few requirements for writing post processing rules e A processing rule is Perl code one or more Perl operations separated by semicolons e Input value to be processed is in the value variable c 2001 2006 PortaOne I nc All rights Reserved www portaone com 20 PORTA Billing 100 Templates Concepts e The final result is the value returned by the last expression in the list TIP One of the most useful things you can do with the post processing rules is to create some new data which is not based on the input data This is required for example when some data fields e g Interval 1 and Interval N are absent in the input file Nevertheless you know that Interval 1 is 30 seconds and Interval N is 6 seconds You can just assign those values as constants in the post processing rule Let s take a few examples Post processing rule Input data Result Remove all leading whi
3. 16041234567 And during the download exactly the opposite will happen to the number 16041234567 will be prepended 011 so the number in the file will be 01116041234567 In order to provide better data quality control PortaBilling supports several data types Data type defines what kind of data are in the data field and possible ways to process them Currently supported data types are e Destination phone number e Date time e Number e Time interval number of minutes seconds e Text e Boolean yes no The data type is predefined for each data field so you do not have to specify it For each data type in PortaBilling there is an set of default formats so normally you do 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 Then you will be able to specify a custom format rule c 2001 2006 PortaOne I nc All rights Reserved www portaone com 1 6 PORTA Billing100 Templates Concepts Other format rule If none of the PortaBilling default formats suit your needs you can choose the Other format and put your own formatting rule here See below for description how the format rule work for the data field of particular type Number For the numeric data fields for example Price 1 or Connect fee the value you put into the Other Format field is considered to be a format specific
4. Billing100 Templates Concepts types of the 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 the post process rules Template groups Concepts Template groups are necessary for doing sorting subtotals on the large sets of data For example we need to make a list of all of the phone calls with subtotal for each country 8 Acrobat Reader invoice 32 pdf f Eile Edit Document Tools View Window Help BBS MEN 4 4 gt on e DO TOX 5x SIA E Z amp Page 2 of 2 Call Detals Records By Country SLOVENIA Bookmarks 120224947236 38631860359 SLOVENIA Mobile TOTAL By SLOVENIA UKRAINE 3 5 5 0118001 380442450336 UKRAINE 2003 07 19 14 28 35 Kiev Region 2003 07 19 07 45 05 120224947236 380565241979 UKRAINE Proper 2003 07 19 13 18 59 420224947236 380565241979 UKRAINE Proper TOTAL By UKRAINE UNITED STATES UNITED 45215277 25750093 p a 160452152 14257500934 STATES Washington KM 4 202 n 85x11in O 8 H Figure 1 10 Group 2003 07 19 17 46 01 2003 07 19 16 30 29 Each group is a portion of data which have the identical value of some parameter or parameters All of the input data will be sorted according to the groups and rows in
5. select part of the text and use keyboard shortcuts listed below To insert the data fields into the cell do the following e Right click on the cell where data field should be inserted e In the top part of the menu click on the component name to which data field belongs e g Header Sub menu with the list of all data fields in this component will appear e Click on the name of the data field you would like to insert You can always enter into the cell edit mode and add text before or after the data field Also by removing all of over lined data field name you can completely delete the data field from the cell Changing the text attributes You can apply multiple formatting styles to the text elements in the table this includes both static text and data fields notably e Text alignment within the cell e Text font and font attributes text color e Table cell borders and border style Consult the online help for the complete list of all possible text operations c 2001 2006 PortaOne I nc All rights Reserved www portaone com 38 PORTA Billing100 Practical session c 2001 2006 PortaOne Inc All rights Reserved www portaone com Practical session 39 PORTA Billing 100 Practical session In the following chapter we will try to solve situations very similar to what you might encounter Of course details will likely be different in your case but he we will cover general techniques Importing rates
6. this definition should also include the time zone Let s assume that your vendor sends you a data file which contains his new rates and for each rate the date when it becomes effective This is good but is 01 May 2003 00 00 in the Pacific Standard time zone or Central European Time Obviously time zone misinterpretation can make the rest of the data incorrect In most cases specification of the time zone is not included in the date itself but specified externally For example your vendor might tell you All of the given times are in Eastern Standard Time Or there might be a note in the file saying this In any case template engine needs to know This is specified as the Template time zone parameter If this parameter is set to Auto it means that the time zone of the user performing the upload is used This is convenient when working with your own tariffs but usually is not acceptable when uploading tariffs from vendors NOTE If the date includes time zone for example 2003 04 10 12 00 00 Australia Sydney this time zone will have precedence over the template s time zone However in this case make sure that time zone specification is supported and unambiguous For example EST could mean both Eastern Time in the US GMT 5 and Eastern Time in Australia GMT 10 quite a big difference Media parameters Media parameters define the data layout inside of the file where the data for the individual components are located i
7. 1 9 k an 1234 12345 c 2001 2006 PortaOne I nc All rights Reserved www portaone com 15 PORTA Billing 100 Templates Concepts Source column for upload templates Source columns define which column in the original data contains the desired value If the data for this field is absent in the file choose None Multiple data fields can be used for the same source column For example if your input file has a single price for calling a specific destination both Price 1 and Price N will use it as a source column Destination column for download templates Destination columns define where to position the result value NOTE For the download templates with the printable form media this option is not available since there you specify where you would like the value to appear using Layout Designer Format A format describes the data fields so that e during upload they may be converted from that format to the unified representation in the database and e during upload they may be converted from the database PortaBilling default format to the specific format For example if for the Destination data field you choose format 011E 164 then the template engine will know that destinations in the input file data are prefixed with the 011 So for example the destination 01116041234567 number would have 011 stripped off during upload and the number inserted into the PortaBilling will be
8. Concepts Destinatior Country 42066667 CZECH REPUB Test 25551 TANZANIA UNI Dar Es Sa 380 UKRAINE Off peak P Free Seconds hr 5 10 Description canadian tariff Post Call Login Fee Connect Fee 5 1 50 Descriptior Interval 1 Proper 1 30 Figure 1 8 Template data fields 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 3 PortaBilling100 Devel Microsoft Internet Explorer Eile Edit View Favorites Tools Help Q Back Address https demo portaone com 8060 edit template html gt fel Save i Save amp tlose Close Edit Template Q Peach Beravorites GM wena O Ar 2 LJ amp 3 Interval N Price 1 Price N 2 g 2 0 00039 3 12345 vw E Links ee Tariff Upload a Name Exarnple tariff upload Media csv Tye Media Header Fields Columns Timezone csv Auto Description Destination a ai Destination description None Interval 4 to Source Column v v E 164 Text Post Processing Rule Interval N 2 0 Price 1 6 1234 12345 Price N e 7 Off peak Interval 1 a t8 Off peak Interval N x 9 Off peak Price 1 v NJ v 3 ao 1234 12345 M sec v E 1234 12345 Off peak Price N Figure
9. PM 12 34 pm c 2001 2006 PortaOne I nc All rights Reserved www portaone com 19 PORTA Billing100 Templates Concepts 01 May 2003 12 34 56 HH MI 12 34 01 May 2003 12 34 56 HH MI SS 12 34 56 01 May 2003 12 34 56 YYYY MM DD 2003 05 01 HH MI SS 12 34 56 01 May 2003 12 34 56 MM DD YY 05 01 03 12 34 56 HH12 MESS pm AM PM 01 May 2003 12 34 56 D MON YY 1 May 03 01 May 2003 12 34 56 D of MONTH 1 of May 2003 YYYY 01 May 2003 12 34 56 YYYYMMDD 20030501 123456 HHMISS Table 1 3 Examples of the date and time formats for download Other data types For the other data types e g Destination Text or Interval there are no specific format elements The Other format rule is considered to be a Perl macro so it is evaluated and the result value of the evaluation is considered a new value For detailed description of Perl macros see the Post Processing rule It receives the original string value as a parameter and should return the converted value in the required format The following table provides a description of which type the output value is required to be for the other format rules depending on the data field type Data field type Other format rule is 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
10. any country c 2001 2006 PortaOne Inc All rights Reserved www portaone com 5 4 PORTA Billing 100 Practical session Microsoft Excel Cannot Be Create OX Eie Edit View Insert Format Tools Data Window Help Prefix Country two letter ISO code Description 809537 Internal 259 TZ ZANZIBAR M 4 n Cannot_Be_ Created Ready Figure 4 17 Repeat the destination upload but upload Cannot_Be_Created csv file this time NOTE Make sure that you perform upload as supervisor user since only this user is allowed to manually override country information If you are not a supervisor e g an admin the data you put in the Country two letter 1 SO code column will be ignored After we have imported all of the missing destinations in the system we can upload the file again Destination_Unknown with the data which was rejected the first time due to missing destinations This time it should import without any problems As a last step verify that data for your tariff is correct From the main menu go to Tariffs and then click on the L edit rates icon to the right of the tariff description After the rates screen appears try to look up some rates Type in some prefix and then click on the Prefix button to show all rates for the destinations under this prefix Or alternatively you can type country name and search by the country name search is case insensetive c 2001 2006 PortaOne I nc All rights Reserved w
11. are not invoiced How often Each customer has his billing period which could be e Daily e Weekly e Bi weekly e Monthly When the current billing period is over for example on the first day of the month a summary of all the calls made by customer is calculated Customer call statistics are generated and stored on the server optionally they could also be automatically mailed to the customer and the invoice is created Note that the billing period is based on customer s time zone For example if customer has a weekly billing period and his time zone is Australia Melbourne his statistics would cover period from 24 Mar 2003 00 00 Melbourne time 23 Mar 2003 14 00 GMT until 31 Mar 2003 00 00 Melbourne time 30 Mar 2003 14 00 GMT For another customer who also has the weekly billing period but New York time zone this billing period will cover calls made in the period 24 Mar 2003 00 00 New York time 24 Mar 2003 05 00 GMT until 31 Mar 2003 00 00 New York time 30 Mar 2003 14 00 GMT When are statistics available Statistics are generated with a sufficient delay to make sure that the billing period has ended for a particular customer according to that customer s time zone Also statistics calculation takes place only at a certain time of day usually during the night hours when there is the least amount of activity in the system Weekly invoices cover Monday Sunday Assuming that your system does statistical calculation fro
12. not make much sense to upload more than one rate for the same destination effective immediately in one upload session Import result in the email message In any case you will receive the email which contains detailed information about the import results The email message would look similar to the one on Figure 4 11 There will be detailed log explaining why each particular row could not have been uploaded Also there will be one or more files as attachment c 2001 2006 PortaOne I nc All rights Reserved www portaone com 49 PORTA Billing100 Practical session Termination to PowerTel tariff has been uploaded TOR a File Edit View Go Message Tools Window Help 4 amp 4 62 d Gb D d al m Get Msgs Compose Reply Reply All Forward File Next Delete Print Subject Termination to PowerTel tariff has Attachments vin pas eee Data Error xls Eee X Destination_Unknown xls Date 23 27 AN eens To andrew lt andrew portaone com gt nets ad De Some of the update operations failed see below for more details Data which were not imported are in the attached files Destination 2131 destination does not exist Destination 24495 destination does not exist Destination 1268409 destination does not exist Destination 1268454 destination does not exist Destination 541 destination does not exist Destination 5441 destination does not exist Destination 2970 destination does not exist Destination 2979 d
13. since there is some a formula error in the original file And the price for the Czech Republic has comma instead of the dot in place of the decimal point After we fix those errors we can upload the file again and it should go smoothly this time Fixing the missing destinations First let s take a look at the New_Destinations csv file NOTE Regardless of the input file type CSV XLS the file type for destination upload is always CSV file as there is only one template used for destination uploads the default one It is possible to create a user defined destination upload download template and it is will be possible to apply them to data in a future release c 2001 2006 PortaOne I nc All rights Reserved www portaone com 51 PORTA Billing100 Practical session Microsoft Excel New Destinations csv TOX File Edit View Insert Format Tools Data Window 10 AU M BE fra vo f Action C Country two letter ISO code Description Algeria Cell Angola Cell Antigua Cell Antigua Cell Argentina BA Argentina Cell Aruba Cell add Aruba Cell add Aruba Cell add Australia Adelaide add Australia Adelaide add AAustralia Adelaide x Eu 4 4 New Destinations Ready A Figure 4 13 We can see that phone prefix and destination description were transferred from the original input file Add in the Action column says that we are going to add new destinations What about the Co
14. the 420602 mobile in the Czech Republic However for the purpose of maintaining data quality each phone prefix first have c 2001 2006 PortaOne Inc All rights Reserved www portaone com 48 PORTA Billing 100 Practical session to de defined as a destination At this step some extra information associated with the prefix for example the country name of the custom description So if destination 420602 does not exist yet this rate cannot be inserted In this case we will have to create a destination first and then try to upload the rate again e Attempt to upload rate with an effective from date in the past Since PortaBilling does real time billing as soon as call has been completed CDRs are created and accounts customers vendors are billed So if you try to upload rate with the effective time 1 hour before the current time this would not change anything since the calls which were made during this hour have been billed already Moreover it could only lead to confusion later since it might seem that calls were billed by some rates which were not effective at the moment So you can only upload rates with the effective time immediately or in the future NOTE You can upload many rates for the same destination within the same import session provided that their effective from dates are different For example you can upload rate effective immediately effective 01 Apr 2003 effective 01 May 2003 and so on It does
15. the same group will be presented together In the example above there are three groups visible each of them contains all of the calls made to the certain country Groups can be nested for example group By Country will contain several sub groups By Prefix In the c 2001 2006 PortaOne Inc All rights Reserved www portaone com ee PORTA Billing100 Templates Concepts list of groups they are sorted according to their include order so that the outmost group is on the top and the inmost at the bottom Group Break This is the parameter which defines to which group data should belong All of the available data will be sorted according to this field In the example above the list of all calls made will be sorted by the name of the destination country so that calls made to Slovenia will appear before calls made to Ukraine Next rows which have the same group break expression value will be joined in the same group as seen in the example above for all calls made to Slovenia Ukraine or the US Normally you will just choose one of the available columns to break group on in the example above that would be the Country column You can also create a custom group break and specify any valid SQL expression Group Header This is a template component which will be inserted into the document immediately before the group data In addition to the static text variable fields e g Destination can be used In the examp
16. with the template parameters click Save amp Close and then go to the tariff management screen Create a tariff From the tariff management screen press Add New in order to create a new tariff File Edit View Favorites Tools Help 4 PortaBilling100 Devel Microsoft Internet Explorer TOK L Q se Q x JO Search she Favorites media 2 7 sob https demo portaone com 8060 tariff htm v E Links z HET Add Tariff Pr seve ot sacs Core Name Termination to PowerTel Currency usp US Dollar Customer any M General Info Off Peak Period Free seconds Post Call Surcharge o Login Fee Connect Fee o Description What PowerTel charges us for the termination A e Internet Figure 4 6 After tariff name and other parameters are filled we can press Save so tariff will be saved and extra tabs will become available Now in the Default Upload Template let s choose the template we have just created PowerTel upload and click Save NOTE Always save the changes in tariff information by clicking save before proceed to the upload of the tariff data c 2001 2006 PortaOne I nc All rights Reserved www portaone com 45 PORTA Billing100 Practical session 3 PortaBilling100 Devel Microsoft Internet Explorer TOK File Edit View Favorites Tools Help ay Qs ix P search she Favorites QU media SM P gt Links
17. ORTA Billing100 Invoicing 4 PortaBilling100 Devel Microsoft Internet Explorer C mx Eile Edit View Favorites Tools Help ay Q pack o a Js Search she Favorites media fa amp m Address https demo portaone com 8060 edit_template html Ee Links HH Edit Template gt fel Save i Save amp tlose Close Name Example invoice Media Printable Form Printable Form Type Invoice Timezone Auto Media Header Fields Footer Description Target Column Other Format Rule Post Processing Rule Currency Abbreviation Text Subtotal 1234 10 Le value Porta Format Numt Tax1 1234 10 value Porta Format Nume Tax 2 1234 10 value Porta Format Numt Tax3 1234 10 Tax 4 1234 10 Tax5 1234 10 Total 1234 10 A aaa AA Figure 2 1 There is function in the PortaBilling template library which can be used to easily calculate the amount of certain tax types The function Porta Format Number backcalcTax takes three parameters e Total amount e Total tax percentage e Percentage value of this tax type Let s take the example of British Columbia Canada We would need to have two types of tax GST 7 and PST 7 5 Then we just have to assign for the Tax1 data field post processing rule of Porta Format Number backcalcTax value 7 7 5 7 0 and for the Tax2 post processing rule would be Porta Form
18. PORTA ONE PORTA Billing 100 Maintenance Release 9 Ig Templates Guide www portaone com PORTA Billing100 PortaBilling Templates Guide Copyright notice amp disclaimers Copyright c 2000 2006 PortaOne Inc All rights reserved PortaBilling100 Templates Guide V 1 9 1 August 2004 Please address your comments and suggestions to Sales Department PortaO ne Inc Suite 400 2963 Glen D rive Coquitlam BC V3B 2P7 Canada Changes may be made periodically to the information in this publication Such changes will be incorporated in new editions of this guide The software described in this document is furnished under a license agreement and may be used or copied only in accordance with the terms of the license agreement It is against the law to copy the software on any other medium except as specifically allowed in the license agreement The licensee may make one copy of the software for backup purposes No part of this publication may be reproduced stored in a retrieval system or transmitted in any form or by any means electronic mechanical photocopy recording or otherwise without the prior written permission of PortaO ne Inc The softwate license and limited warranty for the accompanying product are set forth in the information packet that supplied with the product and are incorporated herein by this reference If you can t locate the software license contact your PortaO ne Representative for a copy All pr
19. SIWYG tool for creating document templates It is implemented using only Javascript and DOM HTML extensions so it will work in the MS IE or Mozilla web browser no installation of extra components on the user s computer is required When you design the template in the layout designer you define how the document should look like location of the components pictures fonts colors You also place the data fields on the document These are the containers for the real data which define where the data element should appear For example on the template for the invoice you put Invoice number data field in the place where you would like invoice number to be printed on your invoices top right corner in the example below When the actual invoice will be generated basing on this template the Invoice number will be replaced with the actual invoice number e g 557 Make sure you insert the data field using the pop up menu because if you just type Invoice number it will be regarded as a text string and presented on the invoice exactly as you have typed it NOTE During the design data fields will be represented on the screen by the example data e g 25 for the invoice number This data is provided only for your convenience and are used only in the design mode All of example data are shown in the over lined font which makes it easy to recognize them 3 PortaBilling100 Demo Microsoft Internet Explorer File Edit View Favorites Tool
20. acting the vendor directly So the file contains necessaty data and may be imported Create a template From the main menu choose Templates to open the template management screen 3 PortaBilling100 Devel Microsoft Internet TOR Eie Edit View Favorites Tools Help a Q pack ix l2 yo Search Sie Favorites media A Address E https demo portaone com 8060 templates html Ec Links E 4 Templates L E Addnew close Name Type Media Delete an test Tariff Upload andrew xls Tariff Download cdr down CDR Download Example invoice Invoice B Example tariff upload Tariff Upload lal test down Tariff Download a a test up Tariff Upload Done Internet NE Figure 4 2 Click Add new and fill in information about the new template The template type is Tariff upload the media is XLS and the template is created as a copy of the Tariff upload XLS default PortaBilling template Press Save c 2001 2006 PortaOne I nc All rights Reserved www portaone com 41 PORTA Billing100 Practical session 3 PortaBilling100 Devel Microsoft Internet OK Eile Edit View Favorites Tools Help a pack z ix a yo Search jf Favorites media amp Address a https demo portaone com 8060 templates html a Go Links E M Add Template FT ed Save el savetclore Close P 8 Logout Name PowerTel u load Type Tariff Upload sil Media Ob M A
21. alue s value Destination data is in the second column so we choose B 2 as a source column Since phone prefixes are already in the E 164 format we choose E 164 as a format and no post processing rule is required Interval 1 and Interval N there is no data for them in the file so we choose None as a source column Let s assume that vendor provided us with the information that he uses 1 second billing intervals during peak hours and otherwise 30 6 billing intervals Then we just have to put those values as constants in the post processing rule Price 1 and Price N since in our case the price per minute for the first interval and the rest of the intervals are the same we use the same source column C 3 We use format 12345 12345 since numbers are with as a decimal point Offpeak Interval 1 and Offpeak Interval N we fill them with constants similar how it was done with the Interval 1 and Interval N c 2001 2006 PortaOne I nc All rights Reserved www portaone com 4 4 PORTA Billing1 00 Practical session e Offpeak Price 1 and Offpeak Price N since the price is the same for on peak and off peak we use the same source column and format as for the Price 1 and Price N e Forbidden Discontinued Hidden there is no data for these data fields in the file so we choose None as a source column e Effective from we choose None so rates will be active immediately After we are done
22. alue of the column is in the value vatiable the current value of the total is in the total variable and the result returned by the expression becomes the new total In the example below we see what a list of calls might look like if we use two groups 1 Break group by countty so that each group contains all of the calls made to the same country 2 Break group by destination This group is inside the first group so it will contain all of the calls made to the specific destination within a certain country Group header CLI ANI CLD DNIS Call time Duration Amount C Canada Group header C All calls to Canada British Columbia 1604 b 42021234567 16045215722 01 Sep 2003 13 03 1 06 0 21 Gious 42021234567 16044781200 01 Sep 2003 15 34 2 18 0 43 by 70958931289 16042041212 02 Sep 2003 12 27 0 42 0 13 destination Total for Canada British Columbia 1604 4 06 0 77 Y Group header C All calls to Canada Ontario 1416 iens 70954503258 14166901248 03 Sep 2003 07 40 3 36 1 04 P S V 420517880831 14167316703 03 Sep 2003 12 21 2 12 074 C Total for Canada Ontario 1416 5 48 1 78 Total for Canada 9 54 255 Group footer Group footer Things to remember Destination upload Why can t use custom templates for destination upload download There is one format available for uploading destinations at the moment since e The format is fairly simple e You almost never
23. are column C titled Prefix e Format of the data if they need to be transformed somehow prior to be entered into the system do not enter 011 on the beginning Now let s take a look on the alternative to this which will let you do the same but mote easily faster and with fewer errors Solution 2 Template engine You can use the PortaBilling template engine to handle import or export of the data from PortaBilling When using templates in PortaBilling you are in a situation similar to the one described above Since the template processor knows nothing about the format of your data you will have to provide the template engine with detailed instructions similar to the directions you gave to the human person in our example You will have to tell template processor about file format of the input file where the useful c 2001 2006 PortaOne I nc All rights Reserved www portaone com 6 PORTA Billing100 Templates Concepts data is inside of that file and formatting information of the data Of course you will enter your instructions as a template definition so e They stay stored in the system and you can always reuse them later e Everything is specified explicitly no communication errors e The template engine processing is fast and automatic so you do not need to wait for the human data entry which might take days Template engine The template engine allows you to define data conversion rules templates and exe
24. at Number backcalcTax value 7 7 5 7 5 As you can see it is quite easy Instead of writing 7 7 5 we could have written 14 5 but why do the calculations if template processor can do it for us Finally the only thing is necessary is to place Footer Tax1 and Footer Tax2 fields on the invoice where we want them to appear and label them approptiately c 2001 2006 PortaOne I nc All rights Reserved www portaone com 30 PORTA K Billing1OO 3 PortaBilling100 Devel Microsoft Internet Explorer Eile Edit view Favorites Tools Help Invoicing TOX ar Qe x a JO search sic Favorites media 4 B p ia 33 Address https demo portaone com 8060 layout designer editor html Statement Period From To Terms October 1 2002 October 31 2002 Due on receipt E co Description Vice Over IP Services GST7 EST 75 Amount Due 100 00 CAD Footer Taxl Footer Currency Abbreviation 750 CAD 11450 CAD Payment Details Explain how to pay here Links Figure 2 2 c 2001 2006 PortaOne Inc All rights Reserved www portaone com 33 PORTA Billing 100 Layout designer 3 Layout designer c 2001 2006 PortaOne Inc All rights Reserved www portaone com 3 4 PORTA Billing100 Overview Layout designer Layout designer is a visual WY
25. ation HH AHH F where A sign or digits 0 9 represent one digit of the input or output number The first character from the right non or digit specifies decimal separator The first character from the right non or digit specifies group separator The number of characters after the decimal separator specifies precision of the number But means no zero padding and 0 means zero padding If the number has too many digits compared to the format specification it will be rounded to the number of decimal digits specifies 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 specified then no group separation is done It is enough to specify the group separator only once even if your real numbers will be large and will contain more than one digit group All of the digits to the left of the decimal point are always shown regardless of how many digits to the left of the decimal point actually are in the format This is mostly important for the download templates when format or precision you use to present numbers in documents is different from the format data is stored in the database For upload templates number format specification is mainly used to handle non standard group and decimal separators See the table below for the examples of using formats to convert numbers Format Number Result Comment HAHAHA 1 6 1 6 No z
26. available in your version of PortaBilling if it does not include multi page invoice support Fields Similar to the Header this component contains global data for the object to be processed tariff invoice etc Data fields are split between Header and Fields just for convenience so the Header contains the main parameters and auxiliary parameters are Fields For instance templates for the tariff upload download contain specialized Fields off peak period definition free seconds connection fee etc c 2001 2006 PortaOne Inc All rights Reserved www portaone com 1 1 PORTA Billing100 Templates Concepts 3 PortaBilling100 Devel Microsoft Internet Explorer TOX ay Eile Edit View Favorites Tools Help Qe Q be a Search sig Favorites Aredia 27 22 iml ae D 3g Address le https demo portaone com 8060 edit template html v E3co Links B b Edit Template gt fel Save fe Save amp Close Close ial Name Example tariff upload Media CSV csv Type Tarif Upload Timezone Auto Media Header Fields Columns Description Source Column Other Format Rule Post Processing Rule Off peak Period a tn M rex Free Seconds B iz M sec PostCallSurcharge c 3 1234 1234s v Login Fee D 4 1234 12345 Connect Fee E 5 M 1234 12345 A Internet Columns repeating groups This is the most important component since it de
27. ax Also this method does not work well with the pre paid cards when the customer buys a 10 calling card he expects to spend 10 on calls and he is interested what his price per minute is without complications like taxes Calculating the CDRs with the tax calculations You enter rates into the PortaBilling including the taxes so it is final price CDRs are calculated accordingly with tax being included In this case balances are correct and pre paid products work fine The only complicated part is to produce proper tax information on the invoice if it is required since it has to be done the opposite way 1 e you have to deduce tax from the amounts This is the recommended method since it provides accurate billing for both the pre paid and post paid products The default invoice template supplied with the PortaBilling is an example how you could set up invoicing in this way Reverse calculating taxes basing on the invoice total In the event you wish to price with the tax included in the rates you need to show proper tax information on the invoice There are 5 data fields available for different types of taxes for example GST and PST in Canada which you can fill in using different formulas They all receive a total invoice value as the input value and then you can calculate the actual tax value by your own formula in the post processing rule c 2001 2006 PortaOne I nc All rights Reserved www portaone com 31 P
28. calls i related to the invoice In the layout designer you can use the button to switch between the main page and the call details page You can include all of the calls made by the customet or only summaties Billing invoicing and taxes Does the PortaBilling perform billing including ot excluding taxes Actually PortaBilling is a rating engine so it just depends on how you will be using it PortaBilling does all the calculations basing on the rates you enter into the system tariff parameters and call duration in this way there is no difference if price already includes taxes or no So there ate two scenarios available for dealing with the taxes both have their own advantages and drawbacks c 2001 2006 PortaOne I nc All rights Reserved www portaone com 30 PORTA Billing 100 Invoicing Calculating the CDRs without the tax calculations You enter rates into the PortaBilling without the taxes CDRs are calculates without the taxes When you create an invoice for your customer you can include all the necessary tax calculations there Advantage of this method is unlimited flexibility you can implement support for any taxation scheme you need The big disadvantage of this method is though that since all of the CDRs exclude taxes the customer balances will also be without tax It makes it difficult to match customer balances in PortaBilling with the actual balances since customers pay you amounts including the t
29. ch in the media parameters this tab can be ignored Fields Since there is no field data in the file and we activated No Fields switch in the media parameters this tab can be ignored Columns c 2001 2006 PortaOne I nc All rights Reserved www portaone com 43 PORTA Billing100 Practical session 3 PortaBilling100 Devel Microsoft Internet Explorer AOX a Eile Edit View Favorites Tools Help Address ES https demo portaone com 8060 templates html v Be Links HS Edit Template fel Save amp Close P Logout e Name PowerTel upload Media Excel Excel Type Tariff Upload Timezone Auto Media Header Fields Columns Description Source Column Other Format Rule Post Processing Rule Destination description 1 Destination B 2 Interval 1 D 4 sec Interval N E 5 M sec Price 1 C 3 1234 12345 Price N C 3 1234 12345 Off peak Interval 1 i sec Off peak Interval N sec Off peak Price 1 1234 12345 Off peak Price N 1234 12345 Forbidden Hidden Discontinued Effective From Figure 4 5 Destination description data is in the first column so we choose A 1 as a source column No extra formatting is reqired Hint in order to extract the location description Tirana from the destination description Albania Tirana the following post processing rule can be used v
30. characters represent themselves So they will be placed as is in the output date string and have to be present in the input string exactly how they were entered Let s take a look at some examples for the importing dates into the PortaBilling Date time string Description Correct format specification 07 04 2003 July 4 2003 US MM DD YYYY date format 04 07 2003 July 4 2003 DD MM YYYY European date format 07 04 03 July 4 2003 US MM DD YY date format short year format 11 05 am Time 12 hour HH12 MI AM PM format 11 05 Time 24 hour HH MI format 18 58 00 Time with seconds HH MESS 2003 07 04 14 00 00 Date and time ISO YYYY MM DD format HH MI SS 07 04 03 2 00 00 pm Date and time US MM DD YY format HH12 MESS AM PM 1 May 2002 18 52 Date and time D MON YYYY abbreviated month HH MI name Sun 16 Mar 03 Date and time skip the weekday name XXX D MON YY 12th of March 2003 Date with as is elements Dth of MONTH YYYY Table 1 2 Examples of the date and time formats for upload The following example shows examples of using templates to convert values in the PortaBilling database into the desired format Date time in the Fotmat Result value PortaBilling specification 01 May 2003 12 34 56 MM DD YYYY 05 01 2003 01 May 2003 12 34 56 DD MM YYYY 01 05 2003 01 May 2003 12 34 56 MM DD YY 05 01 03 01 May 2003 12 34 56 HH12 MI AM
31. cute them There are two ways of template based data processing and two types of templates respectively Import data into PortaBilling upload Format Post Process Conversion Rules 1 303 2 gt 00 1303 2 value 60 21 72 Figure 1 1 Such a template called an upload template defines e The input file type for example CSV or Excel e The location of the input file e The format of the file e Transformations to apply to the data before inserting into the database When data processing occurs the template engine parses the input file according to it s type locates the data inside of the file converts the data into an internal representation according to the format specification and applies post processing rules and writes data to the database Export data from PortaBilling download c 2001 2006 PortaOne Inc All rights Reserved www portaone com PORTA Billing 100 Templates Concepts Post Process Rules Format Conversion File XXXXXX m 00 1300 10 E value 1300 10 Internal Representation Figure 1 2 Such a template called a download template defines e In which format the data should be represented e How it should be processed before being inserted into the database e How the data should be arranged in the output file e The outpu
32. d www portaone com The PortaBilling100 User Guide gives a brief explanation of templates This manual describes in detail how to optimally import and export data from PortaBilling Where to get the latest version of this guide The hard copy of this guide is updated at major releases only and does not always contain the latest material for enhancements occurring between minor releases The online copy of this guide is always up to date and integrates the latest changes to the product You can access the latest copy of this guide at www portaone com solutions billing docs Conventions This publication uses the following conventions Commands and keywords are in boldface Terminal sessions console screens system file names are displayed in fixed width font Caution means reader be careful You are capable of doing something that might result in program malfunction or loss of data NOTE Means reader take note Notes contain helpful suggestions or references to materials not contained in this manual Timesaver means the described action saves time You can save time by performing the action described in the paragraph Tips Means the following information might help you solve a problem PORTA Billing100 Templates Concepts 1 Templates Concepts PORTA Billing 100 Templates Concepts Real life situation Before we discuss technical details about the PortaBilling and templates let s take a look at a real
33. ency So this option is not available when importing tariffs from PortaBilling version 2 3 or older You can only upload rates effective immediately or sometime in the future Since billing is done in the real time a call is billed as soon as possible and a CDR is written to the database So in order to modify CDRs which are already in the database re feed should be used So if you are copying data between tariffs and there is an Effective from column included in the data make sure that you replace values which are in the past with an empty value An empty value in the Effective from column means immediately c 2001 2006 PortaOne I nc All rights Reserved www portaone com 2B PORTA K Billing1OO Invoicing c 2001 2006 PortaOne Inc All rights Reserved www portaone com 27 PORTA Billing100 Invoicing How does invoicing in the PortaBilling work PortaBilling provides ready to use invoicing functionality You can design how your invoices should appear invoices are created automatically based on the calls the customer has made invoices ate stored as a PDF file and can be viewed online or printed They are also automatically emailed to the customer if he has an email address set up in his properties Who is invoiced Only customers both resellers and retail customers are invoiced since only they have all the required information for invoicing such as address tax payer ID and so forth Individual accounts
34. ero padding H 00 1 6 1 60 Zero padding HH 00 34567 6 34 567 60 Digit groups HHHH 00 34567 6 34567 60 No digit groups Hd 2 382 24 Rounding Hd 2569 31 2569 3 Significant part is never truncated HHAHH HH 2569 31 2 569 31 German format c 2001 2006 PortaOne I nc All rights Reserved www portaone com 17 PORTA Billing 100 Templates Concepts Yet another custom format H HHH HH 7892569 31 7 892 569 31 Table 1 1 Examples of the number formats Date and Time This data type is used for fields containing either both date and time e g Effective from or just date e g Invoice date Please note that when importing dates from an unusual format it is possible to use the functionality of the Time ParseDate module and hand off all the dirty work to it This module is able to look at your data string and find bits of data regardless of how they are placed there or how they are separated In order to use Time ParseDate module for parsing the dates choose Auto as a format Unfortunately in some situations even Time ParseDate is unable to properly identify the date For example string 02 01 2003 could either mean February 1 or January 274 In such situations exact format specification is required The Other format uses syntax very similar to the one used in applications like Microsoft Excel The elements are e YYYY year including century e YY year without the century N
35. estination does not exist Destination 297127 destination does not exist Destination 61881 destination does not exist Destination 61882 destination does not exist fs amp GJ EJ Document Done Figure 4 11 e Data Error File contains lines from the input file which were not imported because of incorrect data missing value incorrect format etc e Destination Uknown File contains lines from the input file which were not imported because the phone prefix for this rate does not exist in the database e New Destinations File contains a list of phone prefixes which seem to be missing in he database in a format suitable for destination upload The names of those files are defined by PortaBilling and the extension depends on the actual input file format For example if you try to upload a CSV file then Data_Error and Destination Uknown will also have a CSVextension Fixing the errors in data Let s open the Data Error xls file and see its contents c 2001 2006 PortaOne I nc All rights Reserved www portaone com 50 PORTA Billing100 Practical session amp Microsoft Excel Data Error xls TOX File Edit View Insert Format Tools Data Window E rial yio vy BU D Destination description Destination Price N Interval 1 Interval N 2 Ukraine Kiev 38044 YALUEI 38055 VALUE 4205 0 125 4 4 gt nMSheeti Ready Figure 4 12 We can see that rates for the destination in Ukraine were rejected
36. ext Delete Print Subject Destinations have been uploaded Attachments From porta billing portaone com Z Cannot Be Created csy Date 23 38 cie To andrew portaone com Some of the update operations failed see attached files for more details 2901 destinations have been updated 0 destinations have been removed sy amp OF Document Done Figure 4 15 As we can see most of our destinations were uploaded The ones which system failed to create are in the attached file c 2001 2006 PortaOne Inc All rights Reserved www portaone com 5 3 PORTA Billing 100 Practical session Microsoft Excel Cannot Be Create OX File Edit View Insert Format Tools Data Window Help zo drial I Uu E C2 M fe A B Action Prefix Country two letter ISO code Description add 809537 internal add 259 ZANZIBAR M 4 M Cannot Be Created Ready Figure 4 16 These two destinations were rejected because there was no country code provided and system was unable to fill it automatically there is no existing prefix in the system under which 259 or 809537 would fall under So we have to fill country code manually save the file and repeat the upload File should look like the one on the Figure 4 15 Note that as the country code has special meaning Not Available It can be used for special destinations for example Inmarsat or your company s internal numbering plan which do not belong to
37. from your vendors into PortaBilling Input data Your vendor PowerTel sends you list of his new rates for termination You need to import them into the PortaBilling Microsoft Excel PowerTel xls Edit View Insert Format Tools Data Window Help E Times New Roman 13 n z U EIE ta cl Reply with Changes Destination I _ Afghanistan 0 755 Albania 0 1298 Albania Cell 0 1305 Albania Cell 0 1305 Albania Tirana 0 085 Algeria 0 166 Algeria Cell 0 176 9 American Samoa 0 097 10 American Samoa Cell n Mo mn Sheet1 Sheet2 Sheet3 Ready Figure 4 1 Input data analysis First of all let s try to identify if file I suitable for upload at all which structure is has and where the data is located e The file is in Microsoft Excel format e There is no tariff name currency or description in the file In other words there is not any Header data e There is no information about off peak periods connect fees or other Field parameters either e There is data for the rates It is located on the worksheet called Sheet1 Each rate is on a separate row and the two most important parameters phone prefix and price are present The first row with data is at the row 2 The missing information for c 2001 2006 PortaOne I nc All rights Reserved www portaone com 40 PORTA Billing100 Practical session example duration of the billing intervals should be obtained by some other means for example cont
38. ice Amount Payment Credit Applied TOTAL No We can clearly identify a few independent groups of data here 1 Invoice header is present only once in the file and contains global information 2 Invoice line s groups of data which repeat multiple times in a file each time with different values 3 Invoice footer is present only once in the file and contains global information Here is another example this time for the tariff c 2001 2006 PortaOne I nc All rights Reserved www portaone com PORTA Billing 100 Templates Concepts Microsoft Excel PowerTel term xls OX File Edit View Insert Format Tools Data Window Help Yx E Arial mio m iz f None Termination to PowerTel prepared by John 04 05 03 Special parameters Connect fee Off peak period 0 00 None 1 1 USA 1108 Canada 1204 Canada 1242 Bahamas 1242357 Bahamas Mobile 1242359 Bahamas Mobile 1242427 Bahamas Mobile 1242457 Bahamas Mobile 1242477 Bahamas Mobile 1242557 Bahamas Mobile 1246 Barbados General Online Rate Clients n Figure 1 4 Again we have different groups here 1 Header is present only once in the file and contains global information 2 Tariff parameters is present only once in the file and also contains global information 3 Rate data a grouping of data which repeats multiple times in a file with different values So there ate 6 possible template components Header Header contai
39. ixes See the example below c 2001 2006 PortaOne Inc All rights Reserved www portaone com 57 PORTA Billing100 Practical session Microsoft Excel PowerTel xls File Edit View Insert Format Tools Data Arial 12 BZU a fa a ABS v Vu dB YeReply with Changes A34 f Argentina Cell B Argentina Cell 5441 0 0592 Argentina Cell 1541 19 150 0 0592 Argentina Cell 54153 0 0592 Argentina Cell 54156 0 0592 Argentina Cell 54157 8 9 0 0592 Argentina Cell 54167 0 0592 n 4 gt n Sheet1 Sheet Sheet ll 1 Ready Figure 4 20 This is also not suitable for the uploading It is necessary to convert this file first using macros in Excel or some other tool such that one line contains a single prefix Cases like row 37 in the file on Figure 4 20 should be replaces with three lines as shown below Microsoft Excel PowerTel xls File Edit View Insert Format Tools Data Arial 12 BZU a Ca 73 248 Vm Ka v 9 dd YeReply with Changes Aa X f Argentina Cell 35 Argentina Cell 54153 0 0592 36 Argentina Cell 54156 0 0592 era Argentina Cell 54157 0 0592 EE Argentina Cell 54158 0 0592 Ex Argentina Cell 54159 0 0592 Argentina Cell 54167 0 0592 M 4 mA Sheet1 f Sheet2 9 Sheet3 FA Ready Sum 162474 1776 Figure 4 21 c 2001 2006 PortaOne Inc All rights Reserved www portaone com 58
40. le above this is the row which contains the name of the country Group Footer Similar to the group header this element will be placed immediately after the last row of the group It can consist of the static text and variable fields In the example above this is the row which contains TOTAL by and the name of the country and the total number of seconds and amount for this country Group Fields These variable elements are very similar to the data fields in template and they are actually based on them They have their own name so you can choose format and apply the post processing rule in exactly the same way as you would for the ordinary elements Group totals Since one group may consist of many rows of data what would be value in the group fields in the header footer Fields in the group header have the value of the first row in the group You can specify how you would like to populate the value of the fields in the group footer Possible pre defined options include e Summary sum of the values for the corresponding column across all rows e Count number of the rows in the group e Last value value of this column in the last row You can always write your own custom total rules This is very similar to the post processing rule you can specify any Perl expression The current c 2001 2006 PortaOne Inc All rights Reserved www portaone com 2 PORTA Billing 100 Templates Concepts Group by Country 4 v
41. life example which will help us in our study Situation You are the owner of a VoIP billing system You receive a list of rates from your vendors and need to import them into the system Solution 1 Manual entry In order to do this you decide to hire a temporary worker who will type in all the data That person does not know much about your business so you will have to give her detailed instructions how to proceed So your instructions might sound similar to following 1 Here is an Excel file from our vendor 2 After you open it on a second worksheet called Rates on the fist row you will see a title and list of rates under it 3 The value in column C titled Prefix goes into the field Destination on the web interface but do not enter 011 on the beginning of the number The value in column F titled Price per minute goes into the fields Price 1 and Price N on the web There is no Effective From data in the file so just put current date into that field on the web Does not sound too complicated Now let s take a look on what you have just done e You have specified in which file format data are an Excel file e You have specified where within that file are the requied data second worksheet called Rates on the fist row e And finally for the each data element which needs to be imported into the system Destination Price you have specified e In which column data
42. m 2am until 8am Central European Time every day then statistics for those customers who have a time zone in Asia or Europe should be available each Monday morning c 2001 2006 PortaOne I nc All rights Reserved www portaone com 2g PORTA Billing100 Invoicing For customers with a time zone in the US the billing period will not have ended yet when statistics are calculated on Monday morning thus they are created on Monday night and made available Tuesday morning There is currently an invoice on demand functionality in PortaBilling i e invoices should match customer billing periods However if you want to include a certain amount for some special service in the invoice for your customer manually this is easy to do simply perform a balance adjustment This will modify the customer s balance and will also create a CDR record which will be included in the customer s next invoice Invoice total There are two methods available for calculating the invoice total All charges in the period This is the simpler method and the only one which was available prior to PortaBilling100 Maintenance Release 9 The invoice contains all of the call charges in the specified period with the invoice total equaling the sum of these charges Balance adjustments refunds and payments made by a customer via the online payment system are not included since these refer to transactions which have already been made and should not occur again So f
43. n the file and if they are present at all c 2001 2006 PortaOne I nc All rights Reserved www portaone com 1 3 PORTA Billing100 Templates Concepts 3 PortaBilling100 Devel Microsoft Internet Explorer TOX a File Edit View Favorites Tools Help Qs T x a Search She Favorites QU media a LJ Address https demo portaone com 8060 edit_template html Eje Links Edit Template fel Save amp Close Close aj Name Example tariff upload Media csv csv Type TariffUpload Timezone uto Media Header Fields Columns CSV Delimiter comma v CSV Text qualifier double quotes M No Header o Header Data Stat At Row 2 No Fields Fields Data Start At Row Columns Data Start At Row amp A Internet E Figure 1 7 Here is an example data file that would match this template Note that Data start at row means the first row with the actual data not including the title For example line 7 contains titles for column data therefore in the Column Data Start At Row is 8 the numbet of the first row with rate data 4066667 c 2001 2006 PortaOne I nc All rights Reserved www portaone com 1 4 PORTA K Billing 100 Microsoft Excel CAD tariff csv E File Edit View Insert Arial E deam Mo4 ACAD tariff Ready Format Tools Data 10 CRU Templates
44. n those countries You can download it using Get default set button in the Destinations screen customize it according to your needs and then upload Tariff download and upload There are few helpful features regarding the upload of tariffs New destinations files are created automatically When you try to upload a rate for a phone prefix which is not entered in the destinations that rate will not be inserted However PortaBilling will create list of such missing prefixes in the format suitable for upload Integrity checks during the tariff upload c 2001 2006 PortaOne I nc All rights Reserved www portaone com 25 PORTA Billing100 Templates Concepts It is a very common error to upload the wrong file e g from a different vendor or in a different currency In order to prevent this situation the following improvement has been made in the Portabilling100 e tis possible to include tariff name and tariff currency in the data The default templates for upload and download of tariffs provided with the Portabilling100 include it as well e When you try to upload a tariff the template processor compares Tariff name and Currency in your file to the tariff you are trying to upload If they do not match the upload will be rejected This prevents you from such errors This feature works only if you have Tariff name and Currency in your file NOTE PortaBilling 2 3 and older tariff export format did not include tariff name or curr
45. ns data fields with the global data for the object For example on the invoice this would be an invoice number or the name of the company issuing the invoice The tariff header contains tariff name currency and description c 2001 2006 PortaOne Inc All rights Reserved www portaone com 1 0 PORTA Billing 100 Templates Concepts 3 PortaBilling100 Devel Microsoft Internet Explorer DOR ay File Edit View Favorites Tools Help Qe Q x 2 DO Search sie Favorites media e eB Address https demo portaone com 8060 edit template html a Go Links HB Edit Template i sove Savetcose ose Name Example invoice Media Printable Form Printable Form Type Invoice Timezone Auto Media Header Fields Footer Description Target Column Other Format Rule Post Processing R Date IE j Invoice Number Company Name Company Address1 Company Address2 Company Address3 Company Address4 Company Country Company Phone Company Fax E EE EE EE E AA Figure 1 5 Page Header This template component is only available only for printable forms in which the data is split across pages For CVS or Excel files the data is placed as one single set Data fields defined in this component will be placed at the beginning of each printed page NOTE This component might not be
46. oduct names mentioned in this manual are for identification only and are either trademarks or registered trademarks of their respective owners c 2001 2006 PortaOne I nc All rights Reserved www portaone com 2 PORTA Billing100 PortaBilling Templates Guide Table of contents 1 Templates Concepts 5 Real life situation sn 6 Template engine 7 Template comporients iere atest ene ceo atit ita 8 Template parameters 12 Template data fields 15 Template groups aire a eae n pet set inp TR ERU 22 Things to remember 24 2 AVOIGING ne Had ERE 27 How does invoicing in the PortaBilling work 28 invoice template iocos toten arti iem m e tre ta 30 Billing INVOICING and taxes sse ttes 30 3 Layout designer 34 OIM H 35 General document properties sssssssssssssssssssssssssssssosstsssotsetetssetessresseesssnsssssss 36 Working with the images 37 Placing the data elements 37 Changing the text attributes sse tentes 38 4 Practical session 39 Importing rates from your vendors into PortaBilling 40 When is it impossible to import tariff data sssssssssss 56 c 2001 2006 PortaOne I nc All rights Reserved www portaone com 3 PORTA Billing 100 PortaBilling Templates Guide Preface c 2001 2006 PortaOne Inc All rights Reserve
47. ollowing chapter for a detailed description of the layout designer It defines how the invoice should appear with information on fonts colors pictures placement of the components So when you view the invoice on the web interface or when the invoice is automatically mailed to the customer the template engine takes the invoice template and just fills data fields such as Header Invoice Number with their real values for example 1001 for the invoice number Result of this processing could be a HTML document or PDF file Requirements for the invoice are very different from country to country and even in the same country The default invoice template supplied with the system is suitable for British Columbia Canada and provided just as an example Make sure you create your own invoice template and assign it to customers Two predefined invoice templates are supplied with PortaBilling Single page invoice and Multi page invoice what is the difference between them Single page invoice This is a traditional style invoice similar to the invoice template in the older PortaBilling version It contains one page with an invoice header your company name customer name etc invoice fields invoice number invoice date and invoice footer subtotal total Multi page invoice This template s first page is identical to the single page invoice template In addition to this it may contain multiple pages with details of the
48. open in Excel make sure that you Save amp Close it first Excel locks the file so no other applications can access it c 2001 2006 PortaOne I nc All rights Reserved www portaone com 47 PORTA Billing1 00 Practical session 3 PortaBilling100 Devel Microsoft Internet Explorer TOK ar File Edit View Favorites Tools Help Qs ix po Search sie Favorites media a ss https demo portaone com 8060 tariff html v Eco Links fi 4 Upload Termination to PowerTel tariff gt Te save id Savencone hse File c TempiPowerTel xls amp S Internet Figure 4 9 Dealing with the upload errors Let s assume that there have been some errors during the upload We will see the following message then Microsoft Internet Explorer 4 Some of the tariff upload operations failed See email for detailed information Figure 4 10 Types of errors There are few main reasons why rate will not be inserted e There is some error in the input data For example number does not match the format so some field is empty All such rows will be grouped together in a single file so simplify their correction and re importing So if we have tried to upload 5000 new rates and 30 of them had some error we have to edit the file which contain only 30 lines and upload again only that file and not the original one e A destination is missing For example we have tried to upload rate for
49. or example if during August a customer makes calls for 120 and pays 100 for his services in July on August 10th his invoice will show 120 Of course this payment will be recorded in the system and affect the customer s balance Including the previous balance The invoice total will be calculated as e Previous invoice total e Sum of all charges during the period call related or manual charges e Sum of all payments or refunds made during the period So taking our previous example if the customer s invoice for July was 110 and he paid 100 and made calls for 120 during August his total on the August invoice will be 110 120 100 130 The invoicing modes are controlled by the New_Style_Invoice_Calculation switch in the porta admin conf file What the invoice is based on For a reseller the call charges on the invoice will be based on the sum total of all his CDRs for a given period For a retail customer the call charges on the invoice will be the sum total of all the CDRs call charges and maintenance fees for the credit accounts which this customer has debit accounts are prepaid thus they have been already paid in full and there is no need to invoice for calls c 2001 2006 PortaOne I nc All rights Reserved www portaone com 29 PORTA Billing100 Invoicing Invoice template The invoice template is a specially formatted HTML document which is created using the PortaBilling Layout Designer see the f
50. orm of the two letter ISO code Why not the country name Unfortunately country name could be very ambiguous For example UK United Kingdom Great Britain Britain England are all synonyms for the same country which could also could be called The United Kingdom of Great Britain and Northern Ireland and you may never know which version of the name your vendor will decide to use Plus the country names in the local languages e g Velka Britanie in Czech spelling errors e g United Kigndom punctuation marks and so on complicate the matter That is why PortaBilling uses country codes instead which exactly describe which country is intended Does this mean that you now have to manually fill in country codes for several hundred missing destinations No there is a better way TIP When uploading destinations with empty country codes PortaBilling will try to find country information basing on the data which are already in the database This means for example if the prefix 61 already exists in the database with the country assigned as Australia that when you upload a new destination with prefix 61881 PortaBilling will automatically fill in the country information for you NOTE Of course for this method to work efficiently you need to have some initial set of destinations already in the database PortaBilling comes with the default set of almost 3000 destinations which covers most of the countries in the world and popular destinations withi
51. ot supported by the PortaBilling It could be that your vendor sends you data in PDF Microsoft Word or some other format which is not currently supported by the PortaBilling template engine PortaBilling currently supports e Comma separated values files CSV e Microsoft Excel files XLS So try to either ask your vendor to use different format or do conversion by yourself before importing the data There is no phone prefix information in the file Unfortunately this is very common situation when your partner will send you a list of rates but only include country or location name like in the example below c 2001 2006 PortaOne I nc All rights Reserved www portaone com 56 PORTA Billing 100 Practical session Microsoft Excel PowerTel xls File Edit View Insert Format Tools Data Arial 1i2 B z u G v Vu dd Reply with Changes f Destination Afghanistan Albania Albania Cell Albania Tirana Algeria M 4 gt M Sheet1 Sheet2 Sheet3 Ready Figure 4 19 It is not feasible to import such data because you can never be sure which destinations were meant to be under the Albania Cell description Different vendors could use a different numbering for that location The only solution here is to request from your partner to provide you with the list of prefixes There is more than one phone prefix per data line Sometimes you are provided with data using a special coding for the pref
52. ote that this format is depreciated since it lead to the potential data interpretation problems since 10 could be either 2010 or 1910 PortaBilling template engine considers year values 00 30 to be in the 21 century and 31 99 to be in the 20 century e MM number of the month 01 12 e MON abbreviated name of the month e g Jan Only English names of the months are currently supported e MONTH full name of the month e g January Only English names of the months ate currently supported e DD day of the month with the leading zero for 1 9 for example 01 06 14 25 e D day of the month without the leading zero for 1 9 for example 1 6 14 25 e HH or HH24 hour 24 hour format e HH12 hour 12 hour format e AM PM AM or PM indicator e MI minutes NOTE 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 certainly not work icorrectly e SS seconds e TZ time zone name e g CET e XXX any combination of the non space characters Used in the formats for parsing the dates to skip some variable part of the c 2001 2006 PortaOne I nc All rights Reserved www portaone com 1 8 PORTA Billing100 Templates Concepts date for example name of weekday see examples below in the Table 1 2 e Allother symbols in the format spaces delimiters like or and other
53. pe styles Grid properties Grid helps you to easy adjust objects in the template There are few buttons at the toolbar which control grid s behavior e EN turn the grid on or off by default is on c 2001 2006 PortaOne I nc All rights Reserved www portaone com 36 PORTA Billing 100 Layout designer e HE _ turn snap to grid on ot off When snap to grid is on object edges will always be on one of the grid lines so you cannot put object between the grid lines when you drag it or resize e Select menu allows you to choose size of the grid cells shows or hides envelope window on the template Working with the images You can add your images to the template These images will be stored on the server then and used by any one who will be browsing document created from the template To add a new image e Click on the empty background of the template so no other object such as picture or table is selected Add picture button should become enabled e Click on the Add Picture button e Open File dialog will appear choose the picture on your local disk Any picture format which is supported by the popular web browsers can be used JPG GIF PNG e Click Open and the picture will be transferred to your template You can now move it or resize Placing the data elements The main object of the Layout Designer is Table the placeholder for all inserted information It contains text or data fields which otherwise co
54. s Copy Of Tariff Upload XLS Internet Figure 4 3 Template parameters After the new template is created we need to fill in media parameters and formats for the individual data fields Media parameters Our media parameters would be e No Header since there is no header data in the file This completely disables the component so values in the Header Worksheet and Header Data Start At Row are ignored No Fields since there is no field data in the file e Column Worksheet is Sheet1 and Column Data Start At Row is 2 c 2001 2006 PortaOne Inc All rights Reserved www portaone com 42 PORTA Billing100 Practical session PortaBilling100 Devel Microsoft Internet Explorer TOX ay File Edit View Favorites Tools Help Qe x a JO Search sie Favorites media e a A LJ E Address https demo portaone com 8060 templates html Eco Links LT i sere Serstcher Ip Ea Name PowerTel upload Media Excel Excel Type Tariff Upload Timezone Auto E O Media Header Fields Columns No Header Header Worksheet Tariff info Header Data Start At Row 2 No Fields Fields Worksheet Tariff parameters Fields Data StartAtRow 2 Columns Worksheet Sheet1 Columns Data Start At Row 2 ii Done 8 Internet E Figure 4 4 Header Since there is no header data in the file and we activated No Header swit
55. s Help ear 1 t i Invoice Date Invoice amp November 1 2002 sm mI Nj 35 Statement Period From To Terms October 31 2002 Due on receipt p October 1 2002 Description sl Vice Over IP Services PST 7 596 Amount Due 11450 CAD Done Figure 3 1 Layout designer consists of two main parts e manipulating content in the editor window c 2001 2006 PortaOne Inc All rights Reserved www portaone com g Internet Toolbar on the left Contains buttons to controls for 39 PORTA Billing100 Layout designer e Worksheet on the right Contains the document itself General document properties Page setup In order to access page setup properties right click on the template window on the clear background and not on the object such as table or image Invoice Figure 3 2 Choose Page Setup item 3 PortaBilling100 Demo 2 X Paper size Envelope 3 9903 2 ma Ml Margins Left 0 25in Right o 25i Top 0 25in Bottom 0 25in Figure 3 3 You will be able to choose paper format margins and position of the envelope s windows Consult the online help for the detailed description of the different envelo
56. scribes the variable portion of data For example rate entries all have the same structure and format phone prefix in the first column country name in the second price in the third etc but there are many such rows in the file and each row is an independent object So you define how one single row of data is formatted and then it will be applied to all rows in the file Footer Contains additional data about the object for example invoice totals Page Footer Available only for printable forms data fields to be placed at the end of each printed page Groups See Template groups section Template parameters Template type The template type defines the intended usage of the template e fit will be for data import upload or for export of data from PortaBilling download c 2001 2006 PortaOne I nc All rights Reserved www portaone com 1 2 PORTA Billing100 Templates Concepts e What kind of data will be involved invoice data tariff data destinations etc Examples of template types are Tariff Download and Destination Upload Template media Defines the file format to be processed for example CSV Comma Separated Values XLS Microsoft Excel or Printable form specific HTML which could be converted to PDF Template time zone Normally when people specify a point in time they declare the day of the month month year hour and minute and optionally seconds However to be absolutely precise
57. simply need to upload just new destinations normally you need to input new rates in the system which requires the new destinations and in this case the PortaBilling template processor will provide you with list of new destinations in the proper format automatically However functionality to create your custom templates for destination upload or download is available and might be included in the future releases c 2001 2006 PortaOne I nc All rights Reserved www portaone com g 4 PORTA Billing100 Templates Concepts How to fill in country information for the destination For each destination entered into the PortaBilling it is necessary to provide information on which country a destination belongs to and optionally an administrative subdivision in that country This will help you to better keep track of the rates for the specific country and also will be used to provide a correct description in the call details and statistics for you and your customers PortaBilling comes with the complete list of the countries which are registered with the International Standards Organization and have allocated their country ISO code for them e g US for United States of America CA for the Canada and so on When new destination is manually entered using the PortaBilling web interface you can pick country from the drop down select menu When importing many new destinations at once the country information has to be provided for each of them in the f
58. t data forma When data processing occurs the template engine extracts data from the database converts it into the specified format applies post processing rules and then creates a file of the specified type placing the data inside the file where it should be Template components Data for an invoice or tariff may have a very complex structure To deal with these cases more easily data within the template is split into different groups called template components so they can be managed separately See an example invoice on the picture below c 2001 2006 PortaOne Inc All rights Reserved www portaone com 8 PORTA E Billing1OO PORTA PowerTel Inc 1505 Pavilion Place ONE Norcross GA 30093 3203 UBI 99 GA100 USA EIN 100 201 110 Contact phone 770 724 4000 Templates Concepts Invoice Invoice Number 5073 Invoice Date 15 Mar 2003 Bill To Ship to Tidmore Real Estate Tidmore Real Estate 4555 Indian Lake Lane 4555 Indian Lake Lane Marietta GA 30069 Marietta GA 30069 Customer ID Purchase Order Number Payment Terms TIDMORE 01 2 10 Net 30 Days Customer Tax ID Customer Contact Ship Date Due Date Magazine Ad Wade Tidmore 14 Apr 2003 olP services t Not rica 723 00 Payment details Beneficiary PowerTel Inc Account 77777777 Bank Routing Number 125000024 Figure 1 3 Subtotal Sales Tax Bank Bank of America Total Invo
59. te space from the string value s st Aruba Aruba Remove all leading and trailing white space from the string value s Ns Aruba Aruba value s Ns Add 1 in from of the phone number but only if it does not start with 0 Svalue m 0 6041234567 16041234567 Svalue 1 value Take only part of the input string after the hyphen my Ga split Norway Oslo Oslo value 2 a 1 Assign a constant value to the field value 30 None 30 30 None 30 Replace all hyphens with spaces and remove all quotes Aer m Si Germany Berlin Germany Berlin Use an external custom module to translate English country name into Czech use Czech Translate Austria Rakousko transl data value Table 1 5 Examples of post process rule usage NOTE This manual does not cover details of the programming in Perl There are excellent books on this subject for example e Programming Perl by Larry Wall Tom Christiansen Jon Orwant ISBN 0 596 00027 8 e Learning Perl by Randal L Schwartz Tom Phoenix ISBN 0 596 00132 0 e Perl Cookbook by Tom Christiansen Nathan Torkington ISBN 1 56592 243 3 Values returned by the post processing rule should have the same type as the input value it receives The following table provides information about c 2001 2006 PortaOne I nc All rights Reserved www portaone com 2 PORTA
60. uld not be placed into the template To add a new table to the template e Click on the empty background of the template so no other object such as picture or table is selected Add Table button should become enabled e Click on the Add Table button A new table will be added to the template This is a very basic table which consists only from a single cell however you can change it To move the table to the desired place simply drag it there click on the table and move the mouse without releasing the mouse button To do advanced operations with the table e g add a row tight click on the table so the menu will appear c 2001 2006 PortaOne I nc All rights Reserved www portaone com 97 PORTA Billing100 Layout designer Header Fields Footer B Insert Row di Insert Col Remove Row fi Remove Col Ta Send to Back FibBring to Front X Remove Table Figure 3 4 Table or table cell within a table can be moved and scaled dragging the cells handlers Double click a table or a cell within table to enter the Cell Editing mode where you can type something in or edit cell s text To change attributes of the cell s text make sure the cell is selected 8 black square handlers are visible and set text attributes in the toolbar on the left Changing text attributes of a cell from the Toolbar applies to all text in the current cell To change attributes of part of text enter the cell editing mode double click
61. untry two letter ISO code column which is empty Country information for the new destinations As you know from the previous chapters PortaBilling is able to automatically match the country for the phone prefix basing on the data which is already in the database For example in our case if in the database already has destination 61 which states that it is Australia and we try to upload destination 61881 line 11 in the on the Figure 4 13 PortaBilling will fill in the country information automatically So we need to go to the Destinations screen choose Upload there enter the name of the file and click Save amp Close c 2001 2006 PortaOne Inc All rights Reserved www portaone com 52 PORTA Billing100 Practical session 4 PortaBilling100 New installation Microsoft Internet OK Eile Edit View Favorites Tools Help aw Q pack Y ix a po Search sie Favorites media A P Address https 172 19 0 4 destination html v Go Links B 4 Destinations Upload t save el sevettiose Cose File c ternp New_Destinations csv A Internet Figure 4 14 Soon we will receive email similar to the one below which contains information about the success or failure of the import Destinations have been uploaded Inbox for andrew TOR a File Edit View Go Message Tools Window Help Qu Ed Hd 64 d Gy Ry H st GetMsgsCompose Reply Reply All Forward File N
62. ww portaone com 55 PORTA Billing 100 Practical session PortaBilling100 Devel Microsoft Internet Explorer COX ar Eile Edit View Favorites Tools Help Q pack Q x a s Search sie Favorites media PM a LJ p M 33 Address https demo portaone com 8060 rates html v Be Links B EF Termination to PowerTel tariff rates gt Add New E Edit Tariff Close PE Logout Effective From Destination vow 420 Interval 1 Interval H Price1 PriceN Effective From ect Mecs i anmi on rer oo nemisa D Dose oo 00 00 OO O 420 CZECH REPUBLIC Proper 0 05670 0 05670 2003 03 17 08 27 44 Edit Destination Country Description CZECH REPUBLIC Prague 0 04480 0 04480 2003 03 17 08 27 44 CZECH REPUBLIC Mobile 0 10070 0 10070 2003 03 17 08 27 44 CZECH REPUBLIC Mobile 0 10070 0 10070 2003 03 17 08 27 44 CZECH REPUBLIC Mobile 0 10070 0 10070 2003 03 17 08 27 44 CZECH REPUBLIC Mobile 0 10070 0 10070 2003 03 17 08 27 44 CZECH REPUBLIC Mobile 0 10070 0 10070 2003 03 17 08 27 44 CZECH REPUBLIC Mobile 0 10070 0 10070 2003 03 17 08 27 44 Figure 4 18 When is it impossible to import tariff data Despite all the flexibility and power of the PortaBilling template engine it may still be that it is not possible to directly import certain files Let s take a look on the most typical cases File format is n

PortaBilling: User Manual

Contents

Download Pdf Manuals

Related Search

Related Contents