Import of codes and rates in Tariscope
If you are a user of Tariscope Enterprise or Tariscope Provider and you are interested in the cost of calls that were made by your telephone systems subscribers (customers), then you must add rate (tariff) data into the Tariscope database.
If your PBX is connected to Public Switched Data Network (PSDN) through one telecommunications service provider (TSP), then all calls will be rated at the rates of this TSP. If the PBX has a connection with different TSP, then a call for the same destinations will depend on which the TSP was made through. Therefore, Tariscope rates are tied to a specific TSP.
For Tariscope Provider users, which are a TSP themselves, it should also be borne in mind that you may have different rate plans that are assigned to customers, so customers with different rate plans may have different call costs with the same duration. To fulfill this, the cost of a specific rate should be attached to the rate plan.
In order for Tariscope to know what the rate will use to calculate the cost of a specific call, it detects a telephone code in the dialed number and finds the rate that corresponding to this code. Therefore, the rate should be tied to a specific phone code. Of course, one rate can be tied to many phone codes.
Tariscope contains data on several TSPs. Therefore, you can choose any of these TSPs or create a new TSP based on the existing one and then to change a list of rates, their cost and tie to specific telephone codes.
In addition, you can also create a new TSP, create a list of rates and tie these rates to phone codes.
Finally, rates and telephone codes can be imported from external files. It is this option that we will look at this article.
So, to import rates and codes into the Tariscope system you must have a file of one of the following formats:
- Microsoft Excel 2007 (.xlsx).
- Microsoft Excel 2003 (.xls).
- Microsoft Access (.mdb).
- Microsoft Access 2007 (.accdb).
- Comma separated (.csv).
Excel format files are usually used. Therefore, we will consider importing from this file format. But the import for other formats is no different.
Let's list the minimum necessary fields for importing telephone codes and rates:
- Name for phone code and rate.. This is usually the name of a country or city, or a mobile operator.
- Phone code or codes.. If several codes are specified, they must be separated by commas.
- Rate cost.
- Date.. The date from which the rate is valid. If the date is missing, the current date which was set on the computer will be substituted during import.
Additionally, other fields can be specified, which we will mention later.
An example of an Excel file for importing codes and rates is shown in Figure 1.
Figure 1
In it, column A contains the name for the code and rate, column B contains the rate value in dollars, column C contains telephone codes, column D contains the date from which the rate is effective, and column E contains a code correcponds to the call type in Tariscope. There are the following codes in Tariscope:
3- in city;
4 - long distance;
5 - international;
6 - mobile.
Before starting the import, you must have a rate plan to which rates will be imported.
Let's create a new telecommunication service provider and import data from an Excel file into it.
To create a new TSP, select Providers and rates → Provider management in the menu. The Providers page will appear, an example of which is shown in Figure 2.
Figure 2
Click the Add icon on the toolbar. The New provider window appears, where in the Name box, enter the name of TSP. We will create the TSP that is a name of Test 001. If you need to add comments to this name, enter it in the Description position. Click Save. A new row appears on the Providers page.
After that, go to the Common phone codes page, an example of which is shown in Figure 3.
Figure 3
Click the Import Wizard icon on the toolbar. The Import Wizard page appears, an example of which is shown in Figure 4.
Figure 4
Click on the Start button. As a result, the page will take the form shown in Figure 5.
Figure 5
In the File Type list, select your file type with codes and rates. Click the Choose button and select this file. Then click Next.
If you have chosen an Excel file, the next step (Figure 6) will allow you to select the worksheet that contains the necessary data in this file in the Available Tables list.
Figure 6
Click Next and the Import Wizard page appears as shown in Figure 7.
Figure 7
In this import step, the user must associate the Tariscope database fields with the column names of the file being imported from. To quickly identify the columns of the file, they are displayed at the bottom of the page.
In the Code list, select the column that contains the telephone code or codes. In the given example, this is the column named Numbering plan.
The Name list is used for the name of the settlement or mobile operator to which the telephone codes belong. In the given example, this is the column named Destination.
In the Rate name list, in most cases, you should choose the same value as in the previous one. For our example, this is the column named Destination.
In the Rate list, select the column that contains the price of the rate. For our example, this is a column with the new Rates in USD per minute.
If the import file has a column that contains information about the type of call: international, long distance, mobile, and other, then select that column in the Call Type list. This is an optional parameter.
Any telephone code can be linked to a pre-created call category. The presence of a category can allow you to filter calls by them, create special reports, and also set restrictions for subscribers, if the Tariscope license includes a restriction function. To set a category for a phone code, select it in the Category list. This is an optional parameter. The example file does not have a corresponding column.
In the From date list, select the column with the date from which the telephone code becomes effective. In the given example, this is the Effective Date column. If there is no such column in the import file, the current date will be set as the effective date of the phone code during import.
If the import file contains a column with the date until which the telephone code is valid, then select this column in the To date list. This is an optional parameter.
Tariscope can charge for both outgoing and incoming calls. Separate rates are used for each of them. Accordingly, you must choose for which type of calls the tariffs will be imported: for outgoing (in the import file the value must be 0) or incoming (in the import file must be the value 1). If there is no such field in the file, then do not select anything in the Rate direction item. In this case, outgoing calls will be understood.
In the From date (rate) list, select the column with the date from which the rate starts. In the given example, this is the Effective Date column. If there is no such column in the import file, the current date will be set as the effective date of the rate during import.
Thus, for our example, the selection should be made as shown in Figure 8.
Figure 8
Click Next. The wizard page will take the form shown in Figure 9.
Figure 9
These are the final import settings.
If you need to import both telephone codes and rates, the Import only codes switch should be disabled.
In the Provider list, you should select the operator to which rates will be imported. We created the test5 provider, which we selected.
In the Default rate plan list, select the one to which the rate costs will apply.
In the Currency item, select the one in which the rates are specified in the import file. In our file, it is USD.
If you are a TSP and you use Tariscope Provider, and you want calls for all tariffs that will be created to be charged to the subscriber's personal account per month under one name, for example, "International calls", then enter this name in the Description for accounts textbox.
If it is necessary that the tariffing should be carried out with an accuracy of seconds, then the Rounding to minutes switch should be turned off.
The Update rates switch is relevant when you update data on previously entered rates. In this case, you should enable this switch. If it is disabled, then only new telephone codes and their rates, which are in the import file, will be added.
Click Import.
After the import is finished, information about the number of added telephone codes and rates appears.
If you update rates for telephone codes already existing in the database, these codes will not be added again.
If you return to the tariff page of the specific provider for which the data was imported, and select the appropriate rate plan there, you will see all the imported rates (Figure 10).
Figure 10
Only importing codes and rates into the Tariscope system is not enough for the correct charging of calls. There are still some settings that need to be done. There is a separate article about these settings. Let's note only what is related to the codes and the rates of a specific operator. This provider must be tied to routes (groups of communication lines), gateways of the telephone system.
In the case when you have just created a new provider and you did something wrong when importing the codes and rates, you can simply delete this provider and then create it again. To do this, use the Providers page (Figure 2).
If you are a telecommunications service provider and you have several rate plans in which the cost of rates is different, which is a certain percentage of the cost of the main rate plan, then you can easily create additional columns for these rate plans in your Excel file. And then you should repeat the rate import for these rate plans.
There is one more feature for users of the Tariscope Provider edition to be aware of. When importing rates, a separate rate is created for each telephone code or group of telephone codes that are specified in one line. When processing information about calls in the table of personal accounts of subscribers, a separate record will be created for the reporting period for each rate, and all calls for this rate will be summarized in one record. For example, if the subscriber made calls to 20 countries during the month, then 20 entries will be created in the personal account of this subscriber. In most cases, it is not necessary, but one record, for example, with the International calls name, which would combine the charges for all international calls, is enough. There is a setting in rates that allows you to combine all charges for calls of a certain type with different rates in one personal account record. For this, the Description box on the Accounts tab of a specific rate is used (Figure 11). That is, if in all rates related to calls to other countries, enter, for example, the name of International calls in this box, then all charges for calls abroad during the month will be charged to one record.
Figure 11
You can add such a description at the same time for a group of rates if you enable the Show all entered dates and Multiselect switches on the Rates page (Figure 12).
Figure 12
The necessary lines are selected by clicking on the required lines of the rate table.
But with a large number of rates, their selection can take a long time. Therefore, we suggest using SQL query execution. Let's say we want to set the International calls name to personal accounts for all rates starting January 1, 2022.
To do this, select Additional options → SQL queries in the menu, where you should enter the following query:
UPDATE TarifSettings SET AccountsDescription = 'International calls' WHERE RecDate = '20220101'
In this query, change the date 01/01/2022 ('20220101') to the date you want and the value of 'International calls' to the name you want.
Click the Run icon on the toolbar. After that, all calls that will be processed according to these rates will be charged to one record with the name ‘International calls’.
Here are a few more SQL queries that may come in handy.
Change of rate date.
For example, it is necessary to change the effective date of all rates to 01.02.2022, which currently have a date of 01.01.2022. To do this, the following query should be executed on the SQL query page:
UPDATE TarifSettings SET RecDate = '20220201' WHERE RecDate = '20220101'
Deleting rates for a specific date .
For example, you need to delete all tariffs effective from 01.02.2022. To do this, you should execute the following query:
DELETE FROM TarifSettings WHERE RecDate = '20220201'
Related articles
1. Telecommunications service providers.
2. Configuration of call rating in Tariscope.
3. Rate plans.