view release on metacpan or  search on metacpan

lib/App/WHMCSUtils.pm  view on Meta::CPAN

are deferred. This is how the deferment will go:

    invoice \ period   2019-11   2019-12   2020-01   2020-02   2020-03   2020-04
    ----------------   -------   -------   -------   -------   -------   -------
    1001                  10.5
    1002                   3.0       3.0       3.0
    1003                   2.0       2.0       2.0       2.0       2.0       2.0

    TOTAL                 15.5       5.0       5.0       2.0       2.0       2.0

This utility collects invoice items from paid invoices, filters eligible ones,
then defers the revenue to separate months for items that should be deferred
(determined using some heuristic and additionally configurable options), and
finally sums the amounts to calculate total monthly deferred revenues.

This utility can also be instructed (via setting the `full` option to true) to
output the full CSV report (each items with their categorizations and deferred
revenues).

Recognizes English and Indonesian description text.

Categorization heuristics:

* Fund deposits are not recognized as revenues.
* Hosting revenues are deferred, but when the description indicates starting and
  ending dates and the dates are not too old.
* Domain and addon revenues are not deferred, they are recognized immediately.
* Other items will be assumed as immediate revenues.

Extra rules (applied first) can be specified via the `extra_rules` option.

To use this utility, install the Perl CPAN distribution <pm:App::WHMCSUtils>.
Then, create a configuration file `~/whmcs-calc-deferred-revenue.conf`
containing something like:

    db_name=YOURDBNAME
    db_host=YOURDBHOST
    db_user=YOURDBUSER
    db_pass=YOURDBPASS

`db_host` defaults to `localhost`. `db_user` and `db_pass` can be omitted if you
have `/etc/my.cnf` or `~/.my.cnf`. This utility can search for username/password
from those files.

You can also add other configuration like `extra_rules`, e.g.:

    extra_rules=[{"type": "^$", "description": "^(?^i)sewa\\b.*ruang", "category": "rent"}]

You can then run the utility for the desired, e.g.:

    % whmcs-calc-deferred-revenue --date-start 2013-01-01 --date-end 2017-10-31 \
        --date-old-limit 2013-01-01 --full --output-file ~/output.csv

Wait for a while and check the output at `~/output.csv`.

_
    args => {
        %args_db,
        date_start => {
            summary => 'Start from this date (based on invoice payment date)',
            schema => ['date*', 'x.perl.coerce_to' => 'DateTime'],
            tags => ['category:filtering'],
        },
        date_end => {
            summary => 'End at this date (based on invoice payment date)',
            schema => ['date*', 'x.perl.coerce_to' => 'DateTime'],
            tags => ['category:filtering'],
        },
        date_old_limit => {
            summary => 'Set what date will be considered too old to recognize item as revenue',
            schema => ['date*', 'x.perl.coerce_to' => 'DateTime'],
            description => <<'_',

Default is 2008-01-01.

_
        },
        extra_rules => {
            'x.name.is_plural' => 1,
            'x.name.singular' => 'extra_rule',
            schema => ['array*', of=>['hash*', of=>'re*']],
            description => <<'_',

Example (in JSON):

    [
        {
            "type": "^$",
            "description": "^SEWA",
            "category": "rent"
        }
    ]

_
            tags => ['category:rule'],
        },
        full => {
            schema => 'true*',
            tags => ['category:output'],
        },
        output_file => {
            schema => 'filename*',
        },
    },
    features => {
        progress => 1,
    },
};
sub calc_deferred_revenue {
    require String::Escape;

    my %args = @_;

    log_trace "args=%s", \%args;

    my $date_old_limit = $args{date_old_limit} ?
        $args{date_old_limit}->ymd : '2008-01-01';

    my $progress = $args{-progress};

    my $dbh = _connect_db(%args);

    my $extra_wheres = '';
    if ($args{date_start}) {
        $extra_wheres .= " AND i.datepaid >= '".$args{date_start}->ymd()." 00:00:00'";
    }
    if ($args{date_end}) {
        $extra_wheres .= " AND i.datepaid <= '".$args{date_end}->ymd()." 23:59:59'";
    }

    my @fields = qw(id invoiceid datepaid clientid type relid amount category description);