Designing Reports
When designing reports you must put on your developer hat!
Designing a report involves using the BIRT design client to design your
report. Designs are captured in a ".rptdesign" file.
You'll want to apply configuration management techniques to any
.rptdesign file (e.g., cvs checkin)
Generating Bills
Some scheduled tasks (may be outside the NPS in this case) should be
available to kick the BIRT to generate all the PDF bills...
This task will scan the Account Billing Info table
periodically, and generate a bill for each entry that has a past Bill
Date.
<account_name> equal "*" denotes a combined bill for all
user within the <account_domain>.
Domain Wide Service Charges
Domain wide service charges, such as T1, Profession Service, Monthly
Fee, can be subscribed as Service Items to the "domain@<domain>"
account in the Service Subscription Table and
similarily identified in the Service Record Table.
So when BIRT query the Service Record Table for all
users in a specific domain, these "collective" charges will be retrieved
from the "domain" user, and can be presented by in some special section
of the bill.
BIRT has a very clean and straight forward way of handlinig collectice
charges. They call it "grouping", which can be used to
sort/sum/muiltiple/whatever on the individual groups that the developer
defines.
Multi-Tier Rating
Multi-Tier rating will be implemented by Multiple Post Rating to the
same set of CDR.
As an example, some CDR's would need to be rated twice, once with
the rate a carrier is selling it's service for, and once with it's
cost, so the margin a carrier makes can be calculated. Let's say if you
click on 'generate invoice' for a user/domain/reseller, if there could
be another link to 'generate cost & margin', which would take a
different rate plan and run all CDR's through this rate plan.
A Post Rating Scheduler driven by the Post Rating Schedule will pull
the CDRs from the designated Account, apply post rating using the rate
plan of the Charge To Account and charge the result to the Charge To
Account.
Design
Service Package
This specify all the features and the associated charges provisioned
for each user
|
|
| Plan | Description
|
| Dial Plan
| This specify the Dial Plan applied to the user
|
| Dial Permission
| This specify the Dial Permission applied to the user
|
| Service Plan
| This specify the Service Plan applied to the user
|
| Service Subscription
| This specify the count of subscription for each Service Items
as listed in the Service Plan
|
| Rate plan
| This specify the Rate Plan applied to the user
|
| Billing Account Info
| This register the billing info for the account
|
| Misc
| e.g. Grace period for tax, extra charges......
|
Service Plan
|
|
| Field | type | Descriptions
|
| Plan Name
| varchar(127) | Name of the Service Plan
<Plan
Name> is unique within each domain, but can be re-used in different
domain.
|
| Plan Domain
| varchar(127) | Domain that the Service Plan belong to
<Plan
Name>@<Plan Domain> uniquely identify a specific Servie Plan
in the system.
|
| Charge Period
| varchar(31) | The period, e.g. 1 week, 1 month, that
the charge associated with the Service Plan should be applied.
|
| Description
| varchar(128) | General description of the Service Plan
|
Service Item
This is a list of service item belonging to a all the Service Plan
|
|
| Field | type | Descriptions
|
| Plan Name
| varchar(127) | Name of the Service Plan that this
Service Item belong to
|
| Plan Domain
| varchar(127) | Domain that this Service item belong to
|
| Item Category
| varchar(32) | See Service Item Category
|
| Item Name
| varchar(128) | Name of the service item, e.g. VMail,
Monthly Subscription
|
| Item Parameter
| varchar(127) | Parameter specific to the Item, e.g. #
minutes for Minute Bucket
|
| Default Count
| double | Default count of the service item.
Optional
Service Item MUST have "0" Default Count, and election of such are
explicitly specified in Service Item Subscription table.
|
| Item Unit
| varchar(32) | e.g. month, line
|
| Unit Charge
| double | Charge per unit
|
| Tax
| double | Tax rate for this service item
|
| Item Description
| varchar(128) | General description of the item
|
Note
- All charges for Service Item are for the next period
- Need special handling for 1st and last period
Minute Buckets (Not Implemented Yet)
Minute Buckets is a special Service Item, which is used by Bill
generator
- Defined as a Service Item
- Define the Bucket ID and Amount in minutes
- Bucket window is with respect to the Service Plan Period
- CDR are included in the bucker based on their Release Time
- Two passes CDR processing
- Each rate entry will have a Bucket ID, and applied during
Rating at 1st pass
- During Srvice Charge event, each rated CDR entry for the past
Service Period will be examined and tallied for bucket usage
- Register the Post-Bucket Charge, and may be a time stamp for
Bucket processing
- Straddling Entry will fully charged. May be an option to make
them free by a system parameter
- Need pro-rating of Charged and Bucket for 1st and last Service
Period
|
|
| Field | type | Descriptions
|
| Plan Name
| varchar(127) | Name of the Service Plan that this
Service Item belong to
|
| Plan Domain
| varchar(127) | Domain that this Service item belong to
|
| Item Category
| varchar(32) | "buk"
|
| Item Name
| varchar(127) | Name of the service item, e.g. 500
Minute Bucket
|
| Item Parameter
| varchar(127) | <bucket id>:<# minutes in
bucket>, e.g. D:500 for domestic 500 minutes
|
| Item Count
| double | Total minutes in bucket X = <Item
Count> * <# minutes in bucket X>
|
| Item Unit
| varchar(31) | e.g. 500 minutes
|
| Unit Charge
| double | Charge per bucket count
|
| Tax
| double | Tax rate for this service item
|
| Item Description
| varchar(128) | General description of the item
|
Extended Tax (Not Implemented Yet)
- Implement similar to minute bucket
- need Tax ID in Rate and CDR entry
- as service item with zero charge and non-zero tax for
non-usage base taxes
- as service item with zero charge, zero tax but tax_id and rate
in parameter for usage base taxes
Service Item Subscription
This list the additional Service Items subscribed by the user, e.g.
10 Vanity Number, 10G of storage....
|
|
| Field | type | Descriptions
|
| Account User
| varchar(127) | <user> component of the account.
<Account
User>@<Account Domain> together uniquely identify the user in
the system.
<Account User>=="domain" denotes a service
subscription by a domain.
<Account User> must not be "*" for
this table.
|
| Account Domain
| varchar(127) | <domain> component of the
account.
|
| Plan Name
| varchar(127) | Name of the Service Plan that this
Service Item belong to
This Service Item may not pre-exist in the
corresponding Service Plan, i.e. The Service Item Subscription can add
new "Service Item" to the corresponding Service Plan for a specific
Account.
|
| Plan Domain
| varchar(127) | Domain that this Service item belong to
|
| Item Category
| varchar(32) | See Service Item Category
|
| Item Name
| varchar(127) | Name of the service item, e.g. VMail,
Monthly Subscription
|
| Item Parameter
| varchar(127) | Parameter specific to the Item, e.g. #
minutes for Minute Bucket
|
| Item Count
| double | Count of the service item in ADDITION to the
"Default Count" in the corresponding Service Plan.
This can either
increase the count of an included items in the Service Plan, or elect
an Optional Item to the Service Plan.
|
| Item Unit
| varchar(31) | e.g. month, line
|
| Unit Charge
| double | Charge per unit
|
| Tax
| double | Tax rate for this service item
|
| Item Description
| varchar(128) | General description of the item
|
Post Rating Schedule
To be used by Post Rating Scheduler to poll the CDR from an NMS,
applying rating, and record the charges to the CDR in the NPS.
|
|
| Name | type | Descriptions
|
| Account User
| varchar(127) | <user> component of the account.
<Account
User>@<Account Domain> together uniquely identify the user in
the system.
<Account User>=="*" denotes a post rating for all
users in a domain.
<Account User>=="domain" denotes post rating
for the "domain" user, and not the whole domain.
|
| Account Domain
| varchar(127) | <domain> component of the
account.
|
| Charge To User
| varchar(127) | <user> component of the charge to
account.
<Charge To User>@<Charge To Domain> together
uniquely identify the user in the system to be charged.
<Charge To
User>="*" denotes charge to the corresponding <Account User>
|
| Charge To Domain
| varchar(127) | <domain> component of the account
to be charged
|
| Last CDR Time
| varchar(31) | Release Time of last polled CDR
(This
is a read-only field)
|
| Next Poll
| varchar(31) | Next CDR Release Time to poll from
|
- The output CDR entries written to the NPS's CDR by the Post
Rating are marked with cdr_type=="post"
- The Post Rating Schedule is being scanned every PostRatingScanPeriod,
and PostRatingPollSize time interval of CDR are polled by each
scan.
- The ratio PostRatingPollSize/PostRatingScanPeriod determines
the amount of time to process a period of CDR. e.g a ratio of 6 will
take 1 day to process every 6 day of CDR.
- Call Per Second (CPS) must be be considered in choosing the
PostRatingPollSize to prevent the number of CDR per poll to become too
large (i.e. no more then a few thosand). e.g. 10 CPS will result in 3000
CDR per poll if PostRatingPollSize=="5 minute".
- See NPS System Parameters for more details of these
parameters.
- When adding a new Post Rating Schedule entry, set the Next
Poll to the desired CDR Release Time where the specific Post Rating
should start polling the CDR from.
- Next Poll for an existing Post Rating Schedule entry can be
modified to skip over a period of CDR, or to re-rate an already rated
period.
Service Record
This will be an additional table parallel to the CDR to register all
service charges. Any non-usage base charge should be removed from CDR
and collected in this Service Record.
Entries Type
- Recurrent Monthly Service Plan items
- Adjustment to Account
- Payment Received with Total and Date Received
|
|
| Field | type | Descriptions
|
| Account User
| varchar(127) | <user> component of the account.
<Account
User>@<Account Domain> together uniquely identify the user in
the system.
<Account User>=="domain" denotes a summary service
charge applied to the domain.
<Account User> must not be "*"
for this table.
|
| Account Domain
| varchar(127) | <domain> component of the
account.
|
| Plan Name
| varchar(127) | Name of the service plan, e.g. Basic
VoIP
|
| Item Category
| varchar(31) | See Service Item Category
|
| Item Name
| varchar(127) | Name of the service item, e.g. VMail,
Monthly Subscription
|
| Item Count
| int(10) | Count of the service item
|
| Item Unit
| varchar(31) | e.g. month, line
|
| Unit Charge
| double | Charge per unit
|
| Tax
| double | Tax
|
| Total Charge
| double | Total charge
|
| Time Serviced
| datetime | Date and Time that the charges was incurred
|
| Time Recorded
| datetime | Date and Time that the charges was recorded
|
| Account Debited
| bool | Tag whether this charge has already been
subtracted from the user's account balance
|
Service Item Category
|
|
| Category | Descriptions
|
| adj
| Adjustment to account balance
|
| ctx
| Tax applied to the Usage Charge before credit from Minute
bucket
|
| buk
| Minute Bucket
|
| nrc
| Non Recurrent Charge
|
| pmt
| Payment received
|
| srv
| Recurrent Service
|
| tax
| Tax applied to account
|
| utx
| Tax applied to the amount of Usage Minute.
|
Account Billing Info
To be used by Bill generator
|
|
| Name | type | Descriptions
|
| Account Number
| varchar(127) | Account Number
(Imported from
external account system)
|
| Account User
| varchar(127) | <user> component of the account.
<Account
User>@<Account Domain> together uniquely identify the user in
the system.
<Account User>=="*" denotes a combined bill for all
users in a domain.
<Account User>=="domain" denotes a bill for
the "domain" user, and not the whole domain.
|
| Account Domain
| varchar(127) | <domain> component of the
account.
|
| Company Name
| varchar(128) |
|
| First Name
| varchar(128) |
|
| Last Name
| varchar(128) |
|
| Street Address
| varchar(128) |
|
| State
| varchar(32) |
|
| Zip Code
| varchar(16) |
|
| Country
| varchar(32) |
|
| Phone
| varchar(32) |
|
| Fax
| varchar(32) |
|
| E-Mail
| varchar(64) |
|
| Status
| enum('active','inactive') |
|
| Time Zone
| varchar(64) | e.g. US/Pacific
|
| Last Bill Date
| varchar(32) | Date of last bill.
|
| Billing Period
| varchar(32) | e.g. week, month, quarter
|
| Billing Date
| varchar(32) | Date to generate next bill
|
Bills
This table lists all the generated bills and their summary.
- The Billing Period may be different from the Service Plan
Period
- Each bill includes an integral number of Service Period
- There can be more then one Service Period in each bill.
- The billed usage window correspond to the billed Service
Period(s)
- Billing period is >= Bill From Date and < Bill Date
- Credit/Debit from "Usage Record" and "Service Record" are
applied to the corresponding account at the instance a Bill entry is
inserted.
- Non-Recurrent Charge will be entries in Service Record with
special Category code.
- This file info is written by BIRT each time it generate a new
bill.
- There will be a periodic auto export mechanism, similar to the
NMS's CDR export.
- 1st Bill if there was no previous bill
- 1st Bill period start from Date 1st Use for a single account or
MIN(Date 1st user) for a domain
|
|
| Name | type | Descriptions
|
| Account User
| varchar(127) | <user> component of the account.
<Account
User>@<Account Domain> together uniquely identify the user in
the system.
|
| Account Domain
| varchar(127) | <domain> component of the
account.
|
| Bill From Date
| yyyy-mm-dd | Start date for this billing period
|
| Bill #
| varchar(31) | 10-digit sequence number
|
| Bill Date
| yyyy-mm-dd | Billing date
Billing period is >=
Bill From Date and < Bill Date
|
| Creation Date
| yyyy-mm-dd hh:mm:ss | Date that the bill is created
|
| Last Bill Date
| yyyy-mm-dd | Date of last bill.
|
| Last Bill Total
| double | Total of last bill
|
| Total Payment
| double | Total Payment in the Service Record for this
billing period
|
| Total Adjustment
| double | Total Adjustment in the Service Record for
this billing period
|
| Past Due
| double | Last Bill Total + Total Adjustments - Total
Payment Total
|
| Minute Usage
| double | Total number of minute consumed in this
billing period
|
| Minute Charge
| double | Total usage charges, e.g. minutes
|
| Service Charge
| double | Total of all items belonging to the account's
Service Plan in the Service Record for this billing period.
|
| Non-Recurrent Charge
| double | Total of all items NOT belonging to the
account's Service Plan, NOT Payment and NOT Adjustment in the Service
Record for this billing period.
|
| Tax
| double | Tax applied
|
| New Charge
| double | Usage Charge + Non-Recurrent Charge +
Recurrent Charge + Tax
|
| Total Charge
| double | New Charge + Past Due
|
| Filename
| varchar(127) | Filename of the Bill
|
| File Date
| yyyy-mm-dd hh:mm:ss | Date that the bill file was
generated
|
| Status
| varchar(31) | Status of this bill to help keep
tracking of the file generation, sending of invoice, and receive of
payment.
(enum value TBD)
|
BIRT Billing
Purpose
- Auto generate bills using BIRT and updating the bills table.
Process Steps
- Reads configuration from /etc/netsapiens/billing.conf
- Looks at the bills table
- For every row with a empty status field and a Bill_date that is
prior to current date.
- Servlet requests pdf.
- If PDF generated, saves to \usr\local\Netsapiens\Nps\bills\
- Saves PDF's using syntax:
- Domain = DOMAIN_BILLDATE.pdf
- User = DOMAIN_USER_BILLDATE.pdf
- Updates Bills table adding in the filename, file_date, and
status.
- If completed properly the status will be "Bill Created"
otherwise "Bill Failed"
- goes back to sleep
Install
- This is meant to work in partnering with Netsapiens NPS
(1-1138x4 or higher)
- Copy over the two necessary .rpm files.
netsapiens-billing-x.x.x-x.i386.rpm and
netsapiens-birt-x.x.x-x.i386.rpm. They are on the share under
netsapiens/code archive/billing
- You can install them on separate servers or the same server.
- Install birt by running #rpm -i
netsapiens-birt-x.x.x-x.i386.rpm
- Test by using firefox and going to http://SERVER:9090/netsapiens-birt
- Next step is to install the bill generation app. This need to
be installed on the main server (until we get file replication working)
- Install billing by running #rpm -i
netsapiens-billing-x.x.x-x.i386.rpm
- Next we need to update the billing config,
/etc/netsapiens/billing.conf
- Start by coping over the sample file, #cp
/etc/netsapiens/billing.conf.sample /etc/netsapiens/billing.conf
- edit the file, #vi /etc/netsapiens/billing.conf
- The applications variables are:
- tac_url - URL of tac-server for the nps. e.g. https://localhost/tac/nps
- birt_host - Hostname of birt server, server where you installed
netsapiens-birt e.g. localhost
- tac_login - login used by tac-server
- tac_password - password used by tac-server
- idle_time_min - minutes the app waits before search for more
bills
- ms_between_bill - milliseconds between each pdf generation.
helps avoid system locks
- window_start - beginning of valid window, so you can force it
to only run during off hours, HH:MM:SS
- window_stop - ending of valid window, so you can force it to
only run during off hours, HH:MM:SS
- Save the fill and then restart billing by typing #touch
/usr/local/Netsapiens/tomcat/webapps/billing.war
Monitoring
- You can view logs of both apps by viewing
/usr/local/Netsapiens/tomcat/logs/catalina.out
Appendix: BIRT Tutorial
Please see BIRT Tutorial.
