First, read Financial (Billing) Overview
Automated billing consists of, at several points in the application, adding a Financial Transaction to publishers in an "Out" direction. This means that money is being removed from their account. Publishers will have to have transactions in an "In" direction.
This feature has not been discussed fully with the sponsors, but several plans have been laid in anticipation of this. The database schema can be found here: Database Schema Diagram
The main part of automated billing deals with the "MediaAction" table. Each Media Action has a "MediaActionType", similar to Financial Transactions and their Types. The Media Action Type would be something like "Viewed Media". Because new rows are being added for each Media Action, there is no worry for concurrency issues. There is not one single field to update at a time, so issues of "lost updates" are alleviated (A good example of this can be found here: http://stackoverflow.com/a/880815/5141403 - we solve this by not using updates but instead adding new records)
MediaActionTypes have a ChargePerAction record. ActionTypes with a $0 ChargePerAction can be ignored for automated billing. ChargePerAction may be fractions of a cent. For example, a ChargePerAction of $0.0001 would mean that for every 100,000 actions of that type, the Publisher is billed $10.
In order to not create many, financial transactions, MediaActions should be batched at the end of the week (or day). For each publisher, the sum of their MediaAction's Type's ChargePerAction can be charged to their account (in the form of a new FinancialTransaction).
Here is an example of pseudocode to load this value (Note that the pseudocode does not use SQL joins, which would make this much easier):
function WeeklyChargePublishers() {
var publishers = getAllPublishers();
var now = getEndTimeOfCurrentDate();
var before = now.AddDays(-7);
for (var p in publishers) {
var publisher = publishers[p];
var charge = 0;
var publications = getAllPublicationsForPublisher(publisher.PublisherID);
for (var pub in publications) {
var publication = publication[pub];
var issues = getAllPublicationIssuesForPublication(publication.PublicationID);
for (var i in issues) {
var issue = issues[i];
var pages = getAllPagesInIssue(issue.PublicationIssueID);
for (var pag in pages) {
var page = pages[pag];
var medias = getAllMediaInPage(page.PublicationIssuePageID);
for (var m in medias) {
var media = medias[m];
var actions = getAllMediaActionsFromMedia(media.MediaActionID);
for (var a in actions) {
var action = actions[a];
if (action.TimePerformed >= before && action.TimePerformed < now) {
var type = getMediaActionType(action.MediaActionTypeID);
if (type.ChargePerAction != 0) {
charge += type.ChargePerAction;
}
}
}
}
}
}
}
if (charge != 0) {
createNewFinancialTransaction(getFinancialTypeID("WeeklyCharge"), publisher.PublisherID, charge);
}
}
}
This is much easier in SQL:
DECLARE @now DATETIME; DECLARE @before DATETIME; SET @now = DATEADD(DAY, 1, CONVERT(DATE, CURRENT_TIMESTAMP)); -- Get the end of the day today SET @before = DATEADD(DAY, -7, @now); -- Get a week before the end of the day today SELECT p.PublisherID, SUM(mat.ChargePerAction) FROM Publisher p JOIN Publication pub ON p.PublisherID = pub.PublisherID JOIN PublicationIssue pi ON pub.PublicationID = pi.PublicationID JOIN PublicationIssuePage pip ON pi.PublicationIssueID = pip.PublicationIssueID JOIN PublicationIssuePageMedia pipm ON pip.PublicationIssuePageID = pipm.PublicationIssuePageID JOIN MediaAction ma ON pipm.PublicationIssuePageMediaID = ma.PublicationIssuePageMediaID JOIN MediaActionType mat ON ma.MediaActionTypeID = mat.MediaActionTypeID WHERE ma.TimePerformed >= before AND ma.TimePerformed < now GROUP BY p.PublisherID