Prepay Biller

The example creates a series of memory stores named StaticStore, CDRsStore, AccountCDRsStore, AccountSummariesStore, AuthsStore, AccountAuthStore, and AccountAuthsMinsStore.

CREATE  MEMORY  STORE StaticStore PROPERTIES  INDEXTYPE ='tree',  INDEXSIZEHINT =8;
CREATE  MEMORY  STORE CDRsStore PROPERTIES  INDEXTYPE ='tree',  INDEXSIZEHINT =8;
CREATE  MEMORY  STORE AccountCDRsStore PROPERTIES  INDEXTYPE ='tree',  INDEXSIZEHINT =8;
CREATE  MEMORY  STORE AccountSummariesStore PROPERTIES  INDEXTYPE ='tree',  INDEXSIZEHINT =8;
CREATE  MEMORY  STORE AuthsStore PROPERTIES  INDEXTYPE ='tree',  INDEXSIZEHINT =8;
CREATE  MEMORY  STORE AccountAuthStore PROPERTIES  INDEXTYPE ='tree',  INDEXSIZEHINT =8;
CREATE  MEMORY  STORE AccountAuthsMinsStore PROPERTIES  INDEXTYPE ='tree',  INDEXSIZEHINT =8;

The example creates two input windows named Accounts and CallPlans, and an output window named AccountPlans, all of which reference StaticStore. AccountPlans creates a join between Accounts and CallPlans using their call plan and plan type values.

CREATE  INPUT  WINDOW Accounts
SCHEMA (AccountID INTEGER, FirstName STRING, 
	LastName STRING, Street STRING, City STRING, 
	State STRING, Zip STRING, 
	CallPlan INTEGER, PrepaidTotal FLOAT)
PRIMARY KEY (AccountID)
STORE StaticStore;

CREATE  INPUT  WINDOW CallPlans
SCHEMA (CallPlanType INTEGER, MonthlyRate FLOAT, 
    PlanMinutes FLOAT, AddlMinutesRate FLOAT)
PRIMARY KEY (CallPlanType)
STORE StaticStore;

CREATE  OUTPUT  WINDOW AccountPlans
SCHEMA (AccountId INTEGER, MonthlyRate FLOAT, 
    PlanMinutes FLOAT, AddlMinutesRate FLOAT, PrepaidTotal FLOAT)
PRIMARY KEY (AccountId)
STORE StaticStore
AS SELECT  Accounts.AccountID  AS AccountId,  CallPlans.MonthlyRate  AS MonthlyRate,  
	CallPlans.PlanMinutes  AS PlanMinutes,  CallPlans.AddlMinutesRate  AS AddlMinutesRate,
    Accounts.PrepaidTotal  AS PrepaidTotal 
FROM Accounts JOIN CallPlans ON Accounts.CallPlan = CallPlans.CallPlanType;

The example creates an input window named CDRs that references CDRsStore, and an output window named AccountSummariesJoin that references AccountCDRsStore. CDRs refers to call data records. AccountSummariesJoin creates a join between CDRs and AccountPlans using their bill type code (BillTypCd) and account ID values.

CREATE  INPUT  WINDOW CDRs
SCHEMA (Id INTEGER, BillTypCd INTEGER, CallDuration FLOAT, 
	CallStartDt STRING, CallTypeCd INTEGER, DestTelNo STRING,
	DomIntlFlag INTEGER, LongDurCallFlag INTEGER, 
	OrigTelNo STRING, OrigTrunkGrpNo INTEGER, 
	SensorNumber INTEGER, UnansweredFlag INTEGER)
PRIMARY KEY (Id)
STORE CDRsStore;

CREATE  OUTPUT  WINDOW AccountSummariesJoin
SCHEMA (AccountPlansAccountId INTEGER, AccountPlansMonthlyRate FLOAT, 
AccountPlansPlanMinutes FLOAT, AccountPlansAddlMinutesRate FLOAT,
CDRsCallDuration FLOAT, CDRsBillTypCd INTEGER, CDRsId INTEGER)
PRIMARY KEY (CDRsId)
 STORE AccountCDRsStore
 AS 
SELECT AccountPlans.AccountId AS AccountPlansAccountId, 
  AccountPlans.MonthlyRate AS AccountPlansMonthlyRate, 
  AccountPlans.PlanMinutes AS AccountPlansPlanMinutes, 
  AccountPlans.AddlMinutesRate AS AccountPlansAddlMinutesRate, 
  CDRs.CallDuration AS CDRsCallDuration, CDRs.BillTypCd AS CDRsBillTypCd, 
  CDRs.Id AS CDRsId 
FROM CDRs JOIN AccountPlans ON CDRs.BillTypCd = AccountPlans.AccountId;

The example creates an output window named AccountSummaries that summarizes AccountSummariesStore. AccountSummaries uses SELECT and FROM clauses to pull data from AccountSummariesJoin, and groups the data by account plan ID.

CREATE  OUTPUT  WINDOW AccountSummaries
SCHEMA (AccountId INTEGER, MonthlyRate FLOAT, TotalRatedUsage FLOAT, TotalMinutes FLOAT, CallCount INTEGER)
PRIMARY KEY DEDUCED 
STORE AccountSummariesStore
AS S
ELECT AccountSummariesJoin.AccountPlansAccountId AS AccountId, 
	AccountSummariesJoin.AccountPlansMonthlyRate AS MonthlyRate, 
	(( ( (sum(AccountSummariesJoin.CDRsCallDuration) > AccountSummariesJoin.AccountPlansPlanMinutes) ) *
AccountSummariesJoin.AccountPlansAddlMinutesRate) * (sum(AccountSummariesJoin.CDRsCallDuration) -
AccountSummariesJoin.AccountPlansPlanMinutes)) AS TotalRatedUsage,
	sum(AccountSummariesJoin.CDRsCallDuration) AS TotalMinutes, 
	count(AccountSummariesJoin.CDRsCallDuration) AS CallCount
FROM AccountSummariesJoin
 GROUP BY AccountSummariesJoin.AccountPlansAccountId;

The example creates an input window named Auths that references AuthsStore and an output window named AccountAuthsMinsJoin that references AccountAuthsStore. AccountAuthsMinsJoin creates a join between Auths, AccountPlans, and AccountSummaries using their bill type and account ID values.

CREATE  INPUT  WINDOW Auths
SCHEMA (Id INTEGER, BillTypCd INTEGER, CallStartDt STRING, 
  CallTypeCd INTEGER, DestTelNo STRING, DomIntlFlag INTEGER, 
  LongDurCallFlag INTEGER, OrigTelNo STRING, 
  OrigTrunkGrpNo INTEGER, SensorNumber INTEGER)
PRIMARY KEY (Id)
STORE AuthsStore;

CREATE  OUTPUT  WINDOW AccountAuthsMinsJoin
SCHEMA (AccountPlansAccountId INTEGER, AccountPlansPrepaidTotal FLOAT, 
  AccountPlansAddlMinutesRate FLOAT, AccountSummariesTotalRatedUsage FLOAT, 
  AccountSummariesAccountId INTEGER, AuthsId INTEGER, 
  AuthsBillTypCd INTEGER)
PRIMARY KEY (AuthsId)
STORE AccountAuthsStore
AS SELECT AccountPlans.AccountId AS AccountPlansAccountId, 
	AccountPlans.PrepaidTotal AS AccountPlansPrepaidTotal, 
	AccountPlans.AddlMinutesRate AS AccountPlansAddlMinutesRate, 
	AccountSummaries.TotalRatedUsage AS AccountSummariesTotalRatedUsage, 
	AccountSummaries.AccountId AS AccountSummariesAccountId, 
	Auths.Id AS AuthsId, Auths.BillTypCd AS AuthsBillTypCd 
FROM Auths 
JOIN AccountPlans ON Auths.BillTypCd = AccountPlans.AccountId 
JOIN AccountSummaries ON Auths.BillTypCd = AccountSummaries.AccountId;

The example creates an output window named AccountAuthsMins that references AccountAuthsMinsStore. AccountAuthsMins uses SELECT and FROM clauses to pull data from AccountAuthsMinsJoin, and groups the data by account plan ID.

CREATE  OUTPUT  WINDOW AccountAuthsMins
SCHEMA (AccountId INTEGER, Id INTEGER, AuthMinutes FLOAT)
 PRIMARY KEY DEDUCED 
 STORE AccountAuthsMinsStore
AS SELECT AccountAuthsMinsJoin.AccountPlansAccountId AS AccountId, 
 AccountAuthsMinsJoin.AuthsId AS Id, 
 ((AccountAuthsMinsJoin.AccountPlansPrepaidTotal - AccountAuthsMinsJoin.AccountSummariesTotalRatedUsage) /
AccountAuthsMinsJoin.AccountPlansAddlMinutesRate) AS AuthMinutes
FROM AccountAuthsMinsJoin GROUP BY AccountAuthsMinsJoin.AccountPlansAccountId;

The example concludes by attaching File XML Input adapters to Accounts, CallPlans, CDRs, and Auths.

ATTACH INPUT ADAPTER Adapter1 TYPE xml_in TO Accounts 
	PROPERTIES 
		dir = '../exampledata' ,
		file = 'Accounts.xml', matchStreamName = TRUE ;
		
ATTACH INPUT ADAPTER Adapter2 TYPE xml_in TO CallPlans 
	PROPERTIES 
		dir = '../exampledata' ,
		file ='CallPlans.xml' , matchStreamName = TRUE ;
		
ATTACH INPUT ADAPTER Adapter3 TYPE xml_in TO CDRs 
	PROPERTIES 
		dir = '../exampledata' , 
		file = 'CDRs.xml' ,matchStreamName = TRUE ;
		
ATTACH INPUT ADAPTER Adapter4 TYPE xml_in TO Auths 
	PROPERTIES 
		dir = '../exampledata' , 
		file = 'Auths.xml' ,matchStreamName = TRUE ;