<& elements/search.html, 'title' => emt("Unearned revenue - ".ucfirst($unearned_mode)) . ' (' . time2str('%b %d %Y', $unearned) . ')', 'name' => emt('line items'), 'query' => $query, 'count_query' => $count_query, 'count_addl' => [ $money_char. '%.2f total', $money_char. '%.2f unearned revenue' ], 'header' => [ map( {emt $_} 'Description', 'Unearned', # depends on mode 'Recurring charge', #recur - usage 'Owed', #recur - usage - credits - payments 'Paid', #payments 'Payment date', #of last payment 'Credit date', #of last credit 'Charge start', 'Charge end', 'Invoice', 'Date' ), FS::UI::Web::cust_header(), ], 'fields' => [ #Description sub { $_[0]->pkgnum > 0 ? $_[0]->get('pkg') # possibly use override.pkg : $_[0]->get('itemdesc') # but i think this correct }, #Unearned money_sub('unearned_revenue'), #Recurring charge money_sub('recur_no_usage'), #Owed money_sub('owed_no_usage'), #Paid money_sub('paid_no_usage'), #Payment date date_sub('last_pay'), #Credit date date_sub('last_credit'), #Charge start date_sub('sdate'), #Charge end, minus most of a day date_sub('before_edate'), #Invoice 'invnum', #Invoice date date_sub('_date'), \&FS::UI::Web::cust_fields, ], 'sort_fields' => [ 'pkg', # SQL expressions work as sort keys... 'unearned_revenue', 'recur_no_usage', 'owed_no_usage', 'paid_no_usage', 'last_pay', 'last_credit', 'sdate', 'edate', 'invnum', '_date', ], 'links' => [ ('' x 9), $ilink, $ilink, ( map { $_ ne 'Cust. Status' ? $clink : '' } FS::UI::Web::cust_header() ), ], 'align' => 'lrrcrccrc'. FS::UI::Web::cust_aligns(), 'color' => [ ('' x 11), FS::UI::Web::cust_colors(), ], 'style' => [ ('' x 11), FS::UI::Web::cust_styles(), ], &> <%init> # Separated from cust_bill_pkg.cgi to simplify things. die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); my $conf = new FS::Conf; my $unearned = ''; my $unearned_mode = ''; my $unearned_base = ''; my $unearned_sql = ''; my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' ); my ($join_cust, $join_pkg ) = ('', ''); #here is the agent virtualization my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' ); my @where = ( $agentnums_sql ); if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { push @where, "cust_main.agentnum = $1"; } my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); if ( $cgi->param('status') =~ /^([a-z]+)$/ ) { push @where, FS::cust_main->cust_status_sql . " = '$1'"; } push @where, "cust_bill._date >= $beginning", "cust_bill._date <= $ending"; # cust_classnum (false laziness w/ elements/cust_main_dayranges.html, elements/cust_pay_or_refund.html, prepaid_income.html, cust_bill_pay.html, cust_bill_pkg.html, cust_bill_pkg_referral.html, cust_credit.html, cust_credit_refund.html, cust_main::Search::search_sql) if ( grep { $_ eq 'cust_classnum' } $cgi->param ) { my @classnums = grep /^\d*$/, $cgi->param('cust_classnum'); push @where, 'COALESCE( cust_main.classnum, 0) IN ( '. join(',', map { $_ || '0' } @classnums ). ' )' if @classnums; } # no pkgclass, no taxclass, no tax location... # unearned revenue mode $cgi->param('date') =~ /^(\d+)$/ or die "date required"; $unearned = $1; $unearned_mode = $cgi->param('mode'); push @where, "cust_bill_pkg.sdate < $unearned", "cust_bill_pkg.edate > $unearned", "cust_bill_pkg.recur != 0", "part_pkg.freq != '0'"; if ( !$cgi->param('include_monthly') ) { push @where, "part_pkg.freq != '1'", "part_pkg.freq NOT LIKE '%h'", "part_pkg.freq NOT LIKE '%d'", "part_pkg.freq NOT LIKE '%w'"; } my @opt = ( $unearned, #before this date '', #after this date setuprecur => 'recur', no_usage => 1 ); my $charged = FS::cust_bill_pkg->charged_sql(@opt); push @select, "($charged) AS recur_no_usage"; my $owed_sql = FS::cust_bill_pkg->owed_sql(@opt); push @select, "($owed_sql) AS owed_no_usage"; my $paid_sql = FS::cust_bill_pkg->paid_sql(@opt); push @select, "$paid_sql AS paid_no_usage"; if ( $unearned_mode eq 'paid' ) { # then use the amount paid, minus usage charges $unearned_base = $paid_sql; } else { # use the amount billed, minus usage charges and credits $unearned_base = "( $charged - " . FS::cust_bill_pkg->credited_sql(@opt) . ' )'; } # whatever we're using as the base, only show rows where it's positive push @where, "$unearned_base > 0"; my $edate_zero = midnight_sql('edate'); my $sdate_zero = midnight_sql('sdate'); # $unearned is one second before midnight on the date requested for the report. # suppress partial days for more accounting-like behavior my $period = "CAST( ($edate_zero - $sdate_zero) / 86400.0 AS DECIMAL(10,0) )"; my $remaining = "GREATEST( CAST( ($edate_zero - $unearned) / 86400.0 AS DECIMAL(10,0) ), 0)"; my $fraction = "$remaining / $period"; $unearned_sql = "CAST( $unearned_base * $fraction AS DECIMAL(10,2) )"; push @select, "$unearned_sql AS unearned_revenue"; # last payment/credit date my %t = (pay => 'cust_bill_pay', credit => 'cust_credit_bill'); foreach my $x (qw(pay credit)) { my $table = "cust_$x"; my $link_bill = $t{$x}; my $link_pkg = $link_bill.'_pkg'; my %pkey = map { $_ => dbdef->table($_)->primary_key } ( $table, $link_bill ); my $last_date_sql = "SELECT MAX($table._date) FROM $table JOIN $link_bill USING ($pkey{$table}) JOIN $link_pkg USING ($pkey{$link_bill}) WHERE $link_pkg.billpkgnum = cust_bill_pkg.billpkgnum AND $table._date <= $unearned"; push @select, "($last_date_sql) AS last_$x"; } push @select, '(edate - 82799) AS before_edate'; #no itemdesc #no tax report group kludge #no tax exemption #usage always excluded # always 'nottax', not 'istax' $join_cust = ' JOIN cust_bill USING ( invnum ) '. FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); $join_pkg .= ' LEFT JOIN cust_pkg USING ( pkgnum ) LEFT JOIN part_pkg USING ( pkgpart ) LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart '; my $where = ' WHERE '. join(' AND ', @where); my $count_query = "SELECT COUNT(DISTINCT billpkgnum), SUM( $unearned_base ), SUM( $unearned_sql ) FROM cust_bill_pkg $join_pkg $join_cust $where"; push @select, 'part_pkg.pkg', 'part_pkg.freq', 'cust_main.custnum', FS::UI::Web::cust_sql_fields(); my $query = { 'table' => 'cust_bill_pkg', 'addl_from' => "$join_pkg $join_cust", 'hashref' => {}, 'select' => join(",\n", @select ), 'extra_sql' => $where, 'order_by' => 'ORDER BY cust_bill._date, billpkgnum', }; my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ]; my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; my $conf = new FS::Conf; my $money_char = $conf->config('money_char') || '$'; sub money_sub { $conf ||= new FS::Conf; $money_char ||= $conf->config('money_char') || '$'; my $field = shift; sub { $money_char . sprintf('%.2f', $_[0]->get($field)); }; } sub date_sub { my $field = shift; sub { my $value = $_[0]->get($field); $value ? time2str('%b %d %Y', $value) : ''; }; };