BillMax questions -- Questions regarding how to correlate service charges with payments

[billmax@db.lm.com]

Hi there.

I am trying to query BillMax's database to determine if a particular
service has been paid for or not.

Is there a more straightforward way (besides what I describe below)
that I can ask BillMax, given a service #, whether or not it has been
paid for?

Here is a little bit of background about what we are doing:

We have a bunch of 802.11b wireless hotspots.  We allow users to
signup directly from the web.  It will create a BillMax account for
them and also take a credit card/EFT payment in real-time.  We have an
agreement with each of the hotspots that says they receive 33% of all
signup revenue.  In other words, everytime a user signs up for either
daily or monthly wireless service, we give the site 33% of the total
price of the service.

Obviously, we only want to pay the hotspots whenever we actually get
paid.  I can't just consider the service table, as this would ignore
declined credit card transactions, refunds, services that are still
waiting_for_payment, etc.  Here is the current method that I'm using
to check whether a particular service has been paid for:

1. Query the service table and get a list of all of the wireless
   services that I am interested in:

      SELECT number,d03,servdef,iprice
        FROM service
       WHERE entdate >= "$opts{b}"
         AND entdate <= "$opts{e}"
         AND servdef in (35,36,37)

   35, 36 & 37 are the wireless servdefs that I am interested in
   seeing.  I don't care about any other servdefs.  We want to do this
   on a monthly basis, so the beginning and ending dates are specified on
   the command line.

2. We iterate through each service and try to determine if the service
   has been paid for.  This is the best that I could come up with:

   First, I look for a charge (type=0) in the payhist table that lies
   between the beginning and ending dates specified on the command
   line for the service we are examining:

          SELECT servicename,account,entdate,generation
            FROM payhist
           WHERE type=0
             AND entdate >= "$opts{b}"
             AND entdate <= "$opts{e}"
             AND service=?

   Given the results (account,entdate,generation), I do another query
   on the payhist table, this time I'm looking for a payment (type=1)
   that matches up with the given account/date/generation #:

          SELECT bankacct
            FROM payhist
           WHERE type=1
             AND account=?
             AND entdate=?
             AND generation=?

So far, I know of a few problems with my approach:

1. Refunds are not accounted for.  If we charge a user, then we take a
   payment, then it gets refunded, I don't catch this.  We end up
   paying the hotspot for a service that we didn't actually get paid for.

2. If a charge happens to occur at, say, 2 seconds before midnight,
   there is a good chance that the corresponding payment might not
   have the same date.  (i.e., the charge's entdate will be 2003-06-01
   and the payment's entdate will be 2003-06-02.)  The generation
   numbers are NOT always unique in my case, which is why I had to
   introduce the entdate linking.

3. I've probably missed some other strange billing scenarios...

So, my BIG Question is, once again:

I'm wondering if there is any better way of figuring out how BillMax
allocated a payment.  Is there any more straightforward way I can ask
BillMax, given a service #, whether or not it has been paid for?

Thanks,
-Chris

--
Chris Tracy <chris@telerama.com>
Telerama Public Access Internet
Senior Network Engineer
http://www.telerama.com

-----------------------------------------------------------------------------
To unsubscribe from the "BillMax Questions" mailing list, please
send a message to "majordomo@billmax.com" with "unsubscribe questions"
in the message body. The message must be sent from the exact email
address on the list.