I have come up with a similar solution previously, initially making use of some hand-rolled calculation routines which I have since replaced using Date::Calc.

The setting in which these calculations are used is a billing system where the billing for each customer is determined by their plan id (which in turn relates to billing information in another database table) and the activation date of their account. The following is an edited snippet of the transaction calculation code:

#   return customer id, plan id and activation date for activated acco
+unts and customers

my $acc = $dbh->prepare(qq/
    select  accounts.customerid, accounts.planid, accounts.activationd
+ate
    from    accounts, customers
    where
            accounts.customerid = customers.id
        and accounts.terminationdate = NULL
        and customers.activated = 'Y'
/);
$acc->execute;

while (my $res = $acc->fetchrow_hashref) {

    #   Information regarding billing fees for each plan is stored in 
+@plans where the array index correlates to the billing id

    my $plan = $plans[ $res->{'planid'} ];

    #   $delta is the number of days since account activation - note t
+hat @date holds todays date

    my $delta = Delta_Days((split '-', $res->{'activationdate'}), @dat
+e);
    if ($delta == 0) {
        if ($plan->{'setup'}) {

            #   account was opened today - charge set up fee if necess
+ary

        }
    } else {

        #   Alternatively, if the plan has a regular billing period, c
+alculated as number of billing periods per annum, calculate whether t
+he client should be billed today

        if ($plan->{'fee'} && $plan->{'period'}) {
            my $calc = $delta / int (Days_in_Year((@date)[0..1]) / $pl
+an->{'period'});
            if ($calc == int($calc)) {

                #   Client should be billed $plan->{'fee'} amount

            }
        }
    }
}
$acc->finish;
[download]

The details of billing plans is stored in @plans and includes an unique plan id, plan setup fee (if applicable), the number of billing periods per annum and the fee per billing period. The use of number of billing periods per annum was selected as a measure for billing as it allowed the maximum in flexibility with regard to future plans - There is no fixed limitation with regard to all plans having to monthly or annual or alike. The number of billing periods per annum is converted to day period by the int (Days_in_Year((@date)[0..1]) / $plan->{'period'}) which in turn allows the date of billing to be calculated if the modulus of days since the account activation to day of transaction run ($delta) is zero.

 

perl -e 's&&rob@cowsnet.com.au&&&split/[@.]/&&s&.com.&_&&&print'

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.