Import File Formats
To Import a file, Users must first define the nature of the data to be Imported. Since a single file often contains multiple types of information, the User must define the format of the file itself, allowing the PCR-360 application to process the data using the same format as that implemented by the external file. These Import Formats all related to specific things that can be imported. A list of all the definitions that can be utilized can be found in the 'Imports' wiki.
Use of HTML Tags Some fields within PCR-360 allow Users to input HTML tags to make displaying that information a richer experience for those that are expected to consume it. For more information on HTML tag usage, please see the wiki entry for it. Navigate to the Import File Formats grid by opening the 'Admin' tab and clicking on the node labeled 'File Management'. Click on the node labeled 'Import Files'. Then click on the node labeled 'Import File Formats'. To add a new import file format, click the 'Add' button on the Grid Toolbar above the Import File Formats Grid. In the data entry form, the User must define a number of required fields. First, give the new import file format a Name. Next, define the File Type as either Delimited or Fixed width. If the User defines the File Type as Delimited, the User must also define a Delimiter (generally a comma but not necessarily). Next, specify the number of 'Header Lines' and 'Footer Lines' that exist on the file to be imported, if any. A User can also specify the optional 'Retention' value. This is a number of days to keep the Import Files and Imported Records that are imported with this Import Format. This does not affect the data that was imported, it just cleans up the logs and original raw files of the import job. If this is blank, then the old logs are never deleted. Users can also define the fields under the header 'Record Volume Notifications'. When a 'Low' and 'High' threshold is specified, the PCR-360 application will automatically send an email to the 'Recipient' if the file is above or below a certain size. The 'Record Format Flag' values allow a User to specify a position or delimited index where there might be a value that will determine if the input line should be processed. This is described more below. The Import Process has the ability to automatically process files that are put into a directory on the server. For this to work, the filenames must match the values provided in the Filename Prefix and Extension fields. The Filename will match if it begins with the same characters provided in the Prefix field. The Extension must exactly match. If no value is provided for the Extension, then the Filename extension must be blank. More information about uploading a file can be found here. If the 'Allow Duplicate File' checkbox is set, then it will allow you to upload and process the same file content twice. This does not check the filename, it is checking the file content. For most import types this can be turned on. It is turned off by default as a precaution against accidentally uploading the same GLA Charge or Service Charge file twice, which will create duplicate charges. If this checkbox is turned off, then an error will happen on the upload of a duplicate file.Creating Import File Formats
Import File Record Format
In the tab labeled 'Record Format', the User can define all of the unique record types and what columns are contained in each one.
The Sequence value is a way to number the Record Formats if there are multiple of them, so a User can control the processing order. Each line of the input file will be processed by each Record Format, in the Sequence number order. It is recommended to increment these values by 10s, so it is easier to insert one into the series later if needed.
The Flag Value is used with the Record Format Flag fields from the parent form. On the parent form, the location of the Record Format Flag was identified. In this form a value can be provided that might exist in that position. If the provided value is in that position, then the line will be processed. If there is a different value in that position, then the line will be ignored. This can be used to prevent non-data lines from being processed. For example, in a file with Calls and other data, if the Call records always have a 'C' in a certain column, then the Flag Value could look for that, and ignore all other lines. Or, a User might have a file with Calls and Charges in one file. If there is a column that contains a value to identify which is which, such as the keywords Call or Charge, then a User could have two Import Formats. One with Flag Value 'Call', and one with Flag Value 'Charge'.
If the Flag Value is left blank, then all lines will be processed by this Import Format.
The Record Type indicates what is being imported by this Record Format. Selecting different values for this field will change what columns are available to be mapped, and what tables the imported data will go into. Changing this field may also show or hide other fields on this form.
Calls and Usage Imports
If Calls or Usage are selected as the Import Record Type, then the User will see these additional fields on the Import File Record Format form:
When the Usage or Call Record Types are selected there are some fields that become required. Below are what those required fields are and how they operate.
Duplicates
The Duplicates flag allows a User to control what happens when two calls have the same From Number, To Number, Date, Time, and Duration.
The File Format can be set to do the following upon reading in another copy of the Call:
1. Allow
2. Drop
3. Keep with an error status
Usage Type
The Usage Type Field is an informational field that inserts a Value as a Column into all the records for that Record Format.
When a value is selected from the drop-down then this value will get applied to the resulting Usage Records.
The name of a Usage Type can be changed as well as new Usage types added by going to Admin > System Tables > List Values.
The PCR-360 default values for Usage Type are Data Storage and Bandwidth. However, the User can add as many new values as desired through the List Value USAGE_TYPE.
Service Type
If a value for this is selected, then only that type of Service will be searched for when looking up the Service that the Usage will be billed to.
Rating Method
A Rating Method is a method used to assign Charges to a call record format. For a Usage Record Type the Rating Method is required. The three types or Rating Methods are listed below.
- Rate Tables
Maps to given Rate Table defined in Admin.
The rate must be found in the Call Rates Table.
- Pass through cost in file
The costs for the call will come from the file that is being imported.
- Flat Rate
Activates additional fields to set flat rate.
The additional information comes from the fields in the Initial and Additional sections.
Column Mapping
Now that we know that call records all have a C in position 1, we need to say what and where the rest of the data is in this record type - that is what the Column Mapping is for.
To add entries into the Column Mapping table, simply select the Add button in the Grid. The data entry form looks like this:
The Map To field is required and the list of values is displayed is linked to the type of data that will be imported in this record type. In our example, because the “Call” Map Type was selected, the Map To fields are all related to Call data.
Note: the import file may not have all of the columns in the Map To field. This is OK.
Once the column is selected, the User must enter the Start and Width information for Fixed Width files or the Delimited Index for Delimited files to define where this column of data is in the record. The Start Field defines where the column start in the Import File. The Width Column defines where the Column starts in the Import File.
The Format Mask field will be enabled if the data type of the field is Duration, Date, Time, or Date/Time. For any of these types, you should provide a format string to tell the import logic how the value will look in the input file. If you click on this field, a small list of values will display to let you know what the legal values are for this field.
For Duration, these values are available:
- H - Hours with separator
- hh - 2 digit hours
- h - 1 digit hours
- I - Minutes with separator
- ii - 2 digit minutes
- i - 1 digit minutes
- S - Seconds with separator
- ss - 2 digit seconds
- s - 1 digit seconds
- f# 1/# - fractions of a minute
Common Examples:
Hour, minute, seconds, separated by colon: "H:I:S"
2 digit hour, 2 digit minute, 2 digit second: "hhiiss"
2 digit zero padded hour, minute, seconds, separated by colon: "H:I:S" or "hh:ii:ss"
1 digit hour, 2 digit minutes, then 1 digit of 10ths of minutes (Avaya call duration): "hiif10"
For Date, Time, and Date/Time fields, these values are available:
- yyyy - 4 digit year
- yy - 2 digit year
- mm - 2 digit month
- m - 1 or 2 digit month
- MMM - 3 letter month abbreviation
- Month - Full month name
- dd - 2 digit day
- d - 1 or 2 digit day
- j - Flag indicating a Julian date
- o - Flag indicating an ordinal date (day of year)
- HH - 2 digit hours in 24-hour format
- H -1 or 2 digit hours in 24-hour format
- hh - 2 digit hours in 12-hour format
- h - 1 or 2 digit hours in 12-hour format
- ii - 2 digit minutes
- i - 1 or 2 digit minutes
- ss - 2 digit seconds
- s - 1 or 2 digit seconds
- u - Fractional seconds
- AP - Ante meridiem and post meridiam
Common Examples:
Month, day, year with separators: "m/d/yyyy"
Time with AM/PM: "hh:ii:ss A" or "hh:ii:ss P"
Date and Time with fractional seconds: "mmddyyyy HHiiss.u"
Conditional Logic
The Conditional Logic form allows a User to attach conditional logic to the import. This functionality is very flexible but also highly technical and will require the skills of technical personnel. Due to the complex nature of the capabilities of this functionality, this portion of the import will be reviewed by the customer and a PCR-360 programmer.
- 1 Summary
- 1.1 Help Button
- 1.2 Allowed Functions
- 1.2.1 Array
- 1.2.2 JSON
- 1.2.3 Date/Time
- 1.2.4 Logical
- 1.2.5 Mathematical
- 1.2.6 PCR-360 Utility
- 1.2.7 String
- 1.2.8 User Defined Variables
- 1.2.9 Date Formatting
- 1.2.10 Conditional Logic Flags
- 1.2.11 User Defined Fields
- 1.3 Examples
Summary
The Import Conditional Logic is similar to PHP code, except in how some of the variables are named and referenced, and the very limited white list of available functions. Whenever the 
button on the Conditional Logic form is pressed, a validator will run to check the logic for errors. If errors are detected, the form will not be saved. At any time, the User can press the Validate button, and it will run the check and report any existing errors.
Help Button
A 
button is displayed on the form where the User can enter Conditional Logic. When that button is pressed, a complete list of the PHP constructs and functions that are available will be displayed.
Allowed Functions
Array
explode , implode , is_array , in_array , array_diff , array_diff_key , count
JSON
Date/Time
date , time , mktime , strtotime , DateTime , DateInterval , add , sub, diff, format , setTimestamp , setTime , setDate
Logical
if , elseif , else , switch , case , default , break , for , foreach , as , while , do-while , return , new
Mathematical
round , trunc ,ceil , floor , rand , srand , pow , exp , abs , max , min , bindec , hexdec , octdec , base_convert
PCR-360 Utility
PCR_Utility_Logger, getInstance, notice
String
is_numeric , strtolower , strtoupper , substr , stristr , strstr , stripos , strpos , strripos , strrpos , addslashes , chr , trim , ltrim , rtrim , str_replace , preg_match , preg_replace , preg_split , str_pad , sprintf , substr_replace , strlen
Referencing a Value from the Import File
To reference a value from the Import File, square brackets surrounding numbers to specify the csv index (column) must be used, or the position and size, we use 0 based counting from the start of the line, the first column or first character position is actually 0. For example, with a delimited file, to reference the index (column) 5, [4] would be used. For a fixed-width file, to reference a string starting at character 10 and spanning 4 characters, [9,4] would be used.
To assign a value to one of the index (column) being assigned by the Import, the name of the table and name of the index (column) surrounded by square brackets would be referenced. For example, for a Contact Import, the User may want to set the first name. The reference would be [CONTACTS.FIRST_NAME]. On the Conditional Logic form, there is a helper button to help build these references. Click in the Conditional Logic window to position the Caret, then use the Record Column drop-down to select which index (column) to be referenced, then press the 
button. This will insert the correct reference into the Conditional Logic window.
The Conditional Logic cannot read any values from the Database. It is limited to working only with one line at a time of the input file.
User Defined Variables
User Defined Variables may be used within Conditional Logic. However, if the User wants to use them in the Conditional Logic, they all MUST begin with: "$cl_". This requirement is in place so that User Defined Variables do not conflict with internal system variables.
Date Formatting
If a User needs to assign a Date type field in the Conditional Logic, the format is assumed to be in one of these formats: m/d/y or d-m-y.
Conditional Logic Flags
Sometimes an Import process needs to behave in a non-standard way due to special circumstances. To control this behavior, a User can set flags in the Import Conditional Logic. Usually, the flags are boolean, but other types are supported.
Syntax to set the flag in the Conditional Logic:
[FLAGS.IGNORE] = true;
The following is a list of the Import Conditional Logic Flags that are available in all Import types:
[FLAGS.IGNORE] default false
If set to true, this line of the input file will be ignored (skipped) by the Import process.
You can also set the ignore flag to a string, using an array syntax, like this:
<code block>
[FLAGS.IGNORE][] = "The reason for the ignore";
<end code block>
This will let the reason get counted in the Import Files report totals. This can be useful if your Conditional Logic has several different rules that can cause a record to be ignored. It will let you see totals of how many records are ignored for each reason.
[FLAGS.FILENAME]
This is a read only field that contains the filename of the Import File being imported.
[FLAGS.FILELINE]
This is a read only field that contains the line number from the Import File of the record currently being imported.
There are other flags available in specific Import types that are described in the specific Import wiki pages.
User Defined Fields
User Defined Fields (UDFs) are handled through the Conditional Logic tab. Any UDFs have to be included in Conditional Logic in order to Import them into PCR-360.
To assign a UDF value, the User must know the Identifier name for the UDF. This is the value in the first index (column) of the User Defined Fields grid.
For example, to assign the Rollover GLA Date from input index (column) 4 in the GLA import, the assignment would look like this:
[GLA.UDF_ROLLOVERGLA68] = [4];
Where GLA is the table with the UDF and ROLLOVERGLA68 is the UDF Identifier.
The Identifier and table associations are verified by the Conditional Logic validations. If an invalid Identifier is used in the Conditional Logic, the Validate button or Save button shows the error when clicked, and it will not able to Save until valid data is entered.
Examples
Set a Contact First Name conditionally to field 3 or 4, whichever is filled in.
if ([3] != '') {
[CONTACTS.FIRST_NAME];
} elseif ([4] != '') {
[CONTACTS.FIRST_NAME];
}
Ignore input lines if the name is Joe.
if ([CONTACTS.FIRST_NAME] == 'Joe') {
[FLAG.IGNORE] = true;
}
This is a special assignment that will cause this input line to not get processed.
Note: The above example assumes [CONTACTS.FIRST_NAME] had been assigned prior to that code.
Build and set email address. This will take the first letter of the first name, the last name, and append them together with other email address components. So John Smith will get an email address of 'jsmith@pcr.com'
$cl_companyName = 'pcr'; [CONTACTS.EMAIL] = substr([CONTACTS.FIRST_NAME], 0, 1) . [CONTACTS.FIRST_NAME] . '@' . $cl_companyName . '.com';
Header Lines Tab
If the User specified either Header or Footer lines in the Manage Import File Format in Tenant form, the Header Lines Tab will appear. These headers and footers may be defined by pressing the Header Lines tab. The data entry form is displayed.
The User should define the total number of lines that were specified in the Manage Import File Format in Tenant form.
For each line, the User should define the columns that have pertinent information relating to the file, for example, bill date. If the header or footer is simply memo data from the originating application (description of file, record counts, etc.), then those columns don’t need to be defined, just the line itself. In this case, the line needs to be defined so that PCR-360 can recognize that it is a record that can be ignored since it doesn’t contain importable information.
If columns need to be defined, add or edit the line via the Grid. The data entry form will be displayed:
Select the valid Map To field from the displayed list and for Fixed Width file types, specify the Start position and width of the column. For Delimited file types, select the Delimited Index (the column number for this field).
Setting up a Batch Import
A User can combine multiple Import Types to create more powerful Import File Formats. This allows for the creation of one file, to run a batch of Imports at once.
For example, say a User wants to Import a new Payment, by a new GLA Charge for a GLA that does not yet exist. With Batch Importing, a File Format can be setup to perform all intended operations at once, rather than requiring one Import File for each Record Type.
To set this up, A User would create one Import File Format that has three Import Record Formats. Each Import Record Format would have Mappings and Conditional Logic that referred to one csv File Format, but using only the columns that were needed by each Import Type.
It is important for a User to be careful to have each Import Record Format have the correct Sequence value. Lower Sequence numbers are imported before higher values. In this example, the GLA would need to be first, since it is needed for the GLA Charges and the Payments.
The Import Process will run each line of the csv file on all of the Record Formats before moving on to the next csv line. If any part of the processing of a line fails, then the remainder of that line will abort, and all changes made by that line will roll back out of the database. So in this example, if the GLA succeeds, then an error happens while creating the GLA Charges, the Contact and Payment steps will not get run, and the successful GLA operation from that line will get undone. The GLA Charges error will get reported in the Imported Records grid. Then the process will move on to the next csv line. What this means is that each line of the Import will succeed or fail on it's own and not affect the rest of the records in the Import File.