+----------------------------------------------------------+
|  XtremWeb-HEP Offering, Provisioning and Billing Manual  |
+----------------------------------------------------------+


Requirements, Specifications and Relational Model
-------------------------------------------------
The requirements are in 'xwhep-offering-and-billing-requirements.odt'.

The specifications are in 'xwhep-offering-and-billing-specifications.odt'.

The relational model is available in 'xwhep-offering-provisioning-billing.mwb' (MySQL Workbench).


Implementation
--------------
The implementation consists in following database resources :

-  'view_works_for_billing' generated by 'src/scripts/db-maintenance/xwhep-core-tables-create-views-for-offering-and-billing.sql'.

-  Tables and static data generated by 'xwhep-offering-provisioning-billing-create-tables.sql'.

-  Initial data for tables 'Customers', 'CustomerDetails', 'CustomerContracts' and 'TariffDetails'.
   Billing uses an SQL table join on Customers.CustomerAccount = users.login, so their respective values must match.
   Example of scripts generating such initial data :
   -  'xwhep-customers-insert-oleg-lodygensky-first-name-example.sql'
   -  'xwhep-tariff-details-insert-amazon-example.sql'

-  Billing views generated by 'xwhep-billing-create-views.sql'.
   This implementation of billing through SQL views permits you to use the full power of SQL queries on them.


Units of columns in table 'TariffDetails'
-----------------------------------------
-  If billed usage is time, 'TariffPackageThreshold' and 'TariffUsageIndivisible' must be expressed in seconds,
-  Column 'TariffUsageRate' must be expressed with unit = Euros * 'TariffUsageIndivisible'.

For example :

-  If you want to bill 0.01 € for a 30 mn time limit, you have to set :
   -  TariffPackageThreshold = 30 * 60 =   1800
   -  TariffPackageFare      =             0.01

-  If you want to bill 0.01 € for each indivisible 30 mn above 30 mn, you have to set :
   -  TariffUsageIndivisible = 30 * 60 =   1800
   -  TariffUsageRate        =             0.01

-  If you want to bill 0.1068 € / h for each indivisible 5 mn above 30 mn, you have to set :
   -  TariffUsageIndivisible = 5 * 60              =    300
   -  TariffUsageRate        = 0.1068 * 300 / 3600 = 0.0089


Examples of usage of billing views
----------------------------------

-  Monthly billing for the 12 last months :    select  *  from  View_Billing_By_Month  where  MonthDiff < 12;

-  Daily   billing for the 30 last days :      select  *  from  View_Billing_By_Day    where  DayDiff   < 30;

-  Hourly  billing for the 24 last hours :     select  *  from  View_Billing_By_Hour   where  HourDiff  < 24;

-  Detail  billing for 1 specific day :        select  *  from  View_Billing_Details   where  date(completedDate) = '2013-10-15';

-  Detail  billing for works (of 1 specific day) whose duration is above the package threshold :
   select  *  from  View_Billing_Details   where  (date(completedDate) = '2013-06-30')  and  (TimeAbovePack > 0);