+my @where = ();
+
+#here is the agent virtualization
+push @where, $curuser->agentnums_sql( 'table'=>'cust_main' );
+
+my %total = ();
+my %total_legacy = ();
+foreach my $agentnum (@agentnums) {
+
+ my $where = join(' AND ', @where, "cust_main.agentnum = $agentnum");
+ $where = "AND $where" if $where;
+
+ my( $total, $total_legacy ) = ( 0, 0 );
+
+ # my @cust_bill_pkg =
+ # grep { $_->cust_pkg && $_->cust_pkg->part_pkg->freq !~ /^([01]|\d+[hdw])$/ }
+ # qsearch({
+ # 'select' => 'cust_bill_pkg.*',
+ # 'table' => 'cust_bill_pkg',
+ # 'addl_from' => ' LEFT JOIN cust_bill USING ( invnum ) '.
+ # ' LEFT JOIN cust_main USING ( custnum ) ',
+ # 'hashref' => {
+ # 'recur' => { op=>'!=', value=>0 },
+ # 'sdate' => { op=>'<', value=>$now },
+ # 'edate' => { op=>'>', value=>$now },
+ # },
+ # 'extra_sql' => $where,
+ # });
+ #
+ # foreach my $cust_bill_pkg ( @cust_bill_pkg) {
+ # my $period = $cust_bill_pkg->edate - $cust_bill_pkg->sdate;
+ #
+ # my $elapsed = $now - $cust_bill_pkg->sdate;
+ # $elapsed = 0 if $elapsed < 0;
+ #
+ # my $remaining = 1 - $elapsed/$period;
+ #
+ # my $unearned = $remaining * $cust_bill_pkg->recur;
+ # $total += $unearned;
+ #
+ # }
+
+ #re-written in sql:
+
+ #false laziness w/cust_bill_pkg.cgi
+
+ my $float = 'REAL'; #'DOUBLE PRECISION';
+
+ my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS $float)";
+ my $elapsed = "(CASE WHEN cust_bill_pkg.sdate > $now
+ THEN 0
+ ELSE ($now - cust_bill_pkg.sdate)
+ END)";
+ #my $elapsed = "CAST($unearned - cust_bill_pkg.sdate AS $float)";
+
+ my $remaining = "(1 - $elapsed/$period)";
+
+ my $select = "SUM($remaining * cust_bill_pkg.recur)";
+
+ #[...]
+
+ my $sql = "SELECT $select FROM cust_bill_pkg
+ LEFT JOIN cust_pkg USING ( pkgnum )
+ LEFT JOIN part_pkg USING ( pkgpart )
+ LEFT JOIN cust_main USING ( custnum )
+ WHERE pkgpart > 0
+ AND sdate < $now
+ AND edate > $now
+ AND cust_bill_pkg.recur != 0
+ AND part_pkg.freq != '0'
+ AND part_pkg.freq != '1'
+ AND part_pkg.freq NOT LIKE '%h'
+ AND part_pkg.freq NOT LIKE '%d'
+ AND part_pkg.freq NOT LIKE '%w'
+ $where
+ ";
+
+ my $sth = dbh->prepare($sql) or die dbh->errstr;
+ $sth->execute or die $sth->errstr;
+ my $total = $sth->fetchrow_arrayref->[0];
+
+ $total = sprintf('%.2f', $total);
+ $total{$agentnum} = $total;
+ $total{0} += $total;
+
+ if ( $legacy ) {
+
+ #not yet rewritten in sql, but now not enabled by default
+
+ my @cust_pkg =
+ grep { $_->part_pkg->recur != 0
+ && $_->part_pkg->freq !~ /^([01]|\d+[dw])$/
+ }
+ qsearch({
+ 'select' => 'cust_pkg.*',
+ 'table' => 'cust_pkg',
+ 'addl_from' => ' LEFT JOIN cust_main USING ( custnum ) ',
+ 'hashref' => { 'bill' => { op=>'>', value=>$now } },
+ 'extra_sql' => $where,
+ });
+
+ foreach my $cust_pkg ( @cust_pkg ) {
+ my $period = $cust_pkg->bill - $cust_pkg->last_bill;
+
+ my $elapsed = $now - $cust_pkg->last_bill;
+ $elapsed = 0 if $elapsed < 0;
+
+ my $remaining = 1 - $elapsed/$period;
+
+ my $unearned = $remaining * $cust_pkg->part_pkg->recur; #!! only works for flat/legacy
+ $total_legacy += $unearned;
+
+ }
+
+ $total_legacy = sprintf('%.2f', $total_legacy);
+ $total_legacy{$agentnum} = $total_legacy;
+ $total_legacy{0} += $total_legacy;
+
+ }