X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Freport_receivables.cgi;h=b08fc8143029aa2fcb3b1cd1c58b181411c24a30;hb=29e7e361910ee76e9d487b2df88710af78997c8f;hp=535afcca0931dcefe3a8755ed3bd2f1de999b934;hpb=08662d58e7b9a13cf841e9c89daa39b28655724e;p=freeside.git diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi index 535afcca0..b08fc8143 100755 --- a/httemplate/search/report_receivables.cgi +++ b/httemplate/search/report_receivables.cgi @@ -1,6 +1,38 @@ <% - my $charged = < extract(epoch from now()) - ". + ($end * 86400) + if $end; + + #handle 'cust' option + + push @where, "cust_main.custnum = cust_bill.custnum" + if $opt{'cust'}; + + #handle 'agentnum' option + my $join = ''; + if ( $opt{'agentnum'} ) { + $join = 'LEFT JOIN cust_main USING ( custnum )'; + push @where, "agentnum = '$opt{'agentnum'}'"; + } + + my $where = scalar(@where) ? 'WHERE '.join(' AND ', @where) : ''; + + my $as = $opt{'noas'} ? '' : "as owed_${start}_$end"; + + my $charged = < extract(epoch from now())-2592000 - and cust_main.custnum = cust_bill.custnum - ) - ,0 - ) as owed_0_30, - - coalesce( - ( select $charged from cust_bill - where cust_bill._date > extract(epoch from now())-5184000 - and cust_bill._date <= extract(epoch from now())-2592000 - and cust_main.custnum = cust_bill.custnum - ) - ,0 - ) as owed_30_60, - - coalesce( - ( select $charged from cust_bill - where cust_bill._date > extract(epoch from now())-7776000 - and cust_bill._date <= extract(epoch from now())-5184000 - and cust_main.custnum = cust_bill.custnum - ) - ,0 - ) as owed_60_90, - - coalesce( - ( select $charged from cust_bill - where cust_bill._date <= extract(epoch from now())-7776000 - and cust_main.custnum = cust_bill.custnum - ) - ,0 - ) as owed_90_pl, - - coalesce( - ( select $charged from cust_bill - where cust_main.custnum = cust_bill.custnum - ) - ,0 - ) as owed_total -END + "coalesce( ( select $charged from cust_bill $join $where ) ,0 ) $as"; + + } - my $recurring = <1 ), @ranges ); - ( select count(*) from cust_pkg - where cust_main.custnum = cust_pkg.custnum - and $recurring - and ( cancel = 0 or cancel is null ) - ) as uncancelled_pkgs, + my $select_count_pkgs = FS::cust_main->select_count_pkgs_sql; - ( select count(*) from cust_pkg - where cust_main.custnum = cust_pkg.custnum - and $recurring - and ( cancel = 0 or cancel is null ) - and ( susp = 0 or susp is null ) - ) as active_pkgs + my $active_sql = FS::cust_pkg->active_sql; + my $inactive_sql = FS::cust_pkg->inactive_sql; + my $suspended_sql = FS::cust_pkg->inactive_sql; + my $cancelled_sql = FS::cust_pkg->inactive_sql; + my $packages_cols = <1, 'noas'=>1). " > 0"; my $agentnum = ''; if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { @@ -97,6 +85,9 @@ END $where .= " AND agentnum = '$agentnum' "; } + #here is the agent virtualization + $where .= ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql; + my $count_sql = "select count(*) from cust_main $where"; my $sql_query = { @@ -106,13 +97,11 @@ END 'extra_sql' => "$where order by coalesce(lower(company), ''), lower(last)", }; - if ( $agentnum ) { - $owed_cols =~ - s/cust_bill\.custnum/cust_bill.custnum AND cust_main.agentnum = '$agentnum'/g; - } - my $total_sql = "select $owed_cols"; + my $total_sql = "select ". + join(',', map owed( @$_, 'agentnum'=>$agentnum ), @ranges ); + my $total_sth = dbh->prepare($total_sql) or die dbh->errstr; - $total_sth->execute or die $total_sth->errstr; + $total_sth->execute or die "error executing $total_sql: ". $total_sth->errstr; my $row = $total_sth->fetchrow_hashref(); my $conf = new FS::Conf; @@ -121,6 +110,29 @@ END my $align = join('', map { /#/ ? 'r' : 'l' } FS::UI::Web::cust_header() ). 'crrrrr'; + my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; + + my $status_statuscol = sub { + #conceptual false laziness with cust_main::status... + my $row = shift; + + my $status = 'unknown'; + if ( $row->num_pkgs == 0 ) { + $status = 'prospect'; + } elsif ( $row->active_pkgs > 0 ) { + $status = 'active'; + } elsif ( $row->inactive_pkgs > 0 ) { + $status = 'inactive'; + } elsif ( $row->suspended_pkgs > 0 ) { + $status = 'suspended'; + } elsif ( $row->cancelled_pkgs > 0 ) { + $status = 'cancelled' + } + + ( ucfirst($status), $FS::cust_main::statuscolor{$status} ); + }; + + %><%= include( 'elements/search.html', 'title' => 'Accounts Receivable Aging Summary', 'name' => 'customers', @@ -152,26 +164,13 @@ END sprintf( $money_char.'%.2f', $row->{'owed_60_90'} ), sprintf( $money_char.'%.2f', - $row->{'owed_90_pl'} ), + $row->{'owed_90_0'} ), sprintf( ''. $money_char.'%.2f'. '', - $row->{'owed_total'} ), + $row->{'owed_0_0'} ), ], 'fields' => [ \&FS::UI::Web::cust_fields, - sub { - my $row = shift; - my $status = 'Cancelled'; - my $statuscol = 'FF0000'; - if ( $row->uncancelled_pkgs ) { - $status = 'Suspended'; - $statuscol = 'FF9900'; - if ( $row->active_pkgs ) { - $status = 'Active'; - $statuscol = '00CC00'; - } - } - $status; - }, + sub { ( &{$status_statuscol}(shift) )[0] }, #sub { ucfirst(shift->status) }, sub { sprintf( $money_char.'%.2f', shift->get('owed_0_30') ) }, @@ -180,13 +179,12 @@ END sub { sprintf( $money_char.'%.2f', shift->get('owed_60_90') ) }, sub { sprintf( $money_char.'%.2f', - shift->get('owed_90_pl') ) }, + shift->get('owed_90_0') ) }, sub { sprintf( $money_char.'%.2f', - shift->get('owed_total') ) }, + shift->get('owed_0_0') ) }, ], 'links' => [ - [ "${p}view/cust_main.cgi?", 'custnum' ], - [ "${p}view/cust_main.cgi?", 'custnum' ], + ( map $clink, FS::UI::Web::cust_header() ), '', #'', '', @@ -205,20 +203,7 @@ END 'b', '', '', '', '', 'b', ], 'color' => [ ( map '', FS::UI::Web::cust_header() ), - sub { - my $row = shift; - my $status = 'Cancelled'; - my $statuscol = 'FF0000'; - if ( $row->uncancelled_pkgs ) { - $status = 'Suspended'; - $statuscol = 'FF9900'; - if ( $row->active_pkgs ) { - $status = 'Active'; - $statuscol = '00CC00'; - } - } - $statuscol; - }, + sub { ( &{$status_statuscol}(shift) )[1] }, #sub { shift->statuscolor; }, '', '',