diff options
| -rw-r--r-- | FS/FS/Report/Table/Monthly.pm | 58 | ||||
| -rw-r--r-- | FS/FS/cust_pkg.pm | 29 | ||||
| -rw-r--r-- | httemplate/elements/menu.html | 1 | ||||
| -rw-r--r-- | httemplate/graph/cust_pkg_cost.cgi | 61 | ||||
| -rw-r--r-- | httemplate/graph/report_cust_pkg_cost.html | 26 | ||||
| -rwxr-xr-x | httemplate/search/cust_pkg.cgi | 2 |
6 files changed, 167 insertions, 10 deletions
diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm index 9e7a2882f..4b866c4dc 100644 --- a/FS/FS/Report/Table/Monthly.pm +++ b/FS/FS/Report/Table/Monthly.pm @@ -307,6 +307,64 @@ sub _subtract_11mo { timelocal($sec,$min,$hour,$mday,$mon,$year); } +sub cust_pkg_setup_cost { + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; + my $where = ''; + my $comparison = ''; + if ( $opt{'classnum'} =~ /^(\d+)$/ ) { + if ( $1 == 0 ) { + $comparison = 'IS NULL'; + } + else { + $comparison = "= $1"; + } + $where = "AND part_pkg.classnum $comparison"; + } + $agentnum ||= $opt{'agentnum'}; + + my $total_sql = " SELECT SUM(part_pkg.setup_cost) "; + $total_sql .= " FROM cust_pkg + LEFT JOIN cust_main USING ( custnum ) + LEFT JOIN part_pkg USING ( pkgpart ) + WHERE pkgnum != 0 + $where + AND ".$self->in_time_period_and_agent( + $speriod, $eperiod, $agentnum, 'cust_pkg.setup'); + return $self->scalar_sql($total_sql); +} + +sub cust_pkg_recur_cost { + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; + my $where = ''; + my $comparison = ''; + if ( $opt{'classnum'} =~ /^(\d+)$/ ) { + if ( $1 == 0 ) { + $comparison = 'IS NULL'; + } + else { + $comparison = "= $1"; + } + $where = " AND part_pkg.classnum $comparison"; + } + $agentnum ||= $opt{'agentnum'}; + # duplication of in_time_period_and_agent + # because we do it a little differently here + $where .= " AND cust_main.agentnum = $agentnum" if $agentnum; + $where .= " AND ". + $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main'); + + my $total_sql = " SELECT SUM(part_pkg.recur_cost) "; + $total_sql .= " FROM cust_pkg + LEFT JOIN cust_main USING ( custnum ) + LEFT JOIN part_pkg USING ( pkgpart ) + WHERE pkgnum != 0 + $where + AND cust_pkg.setup < $eperiod + AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL) + "; + return $self->scalar_sql($total_sql); +} + sub cust_bill_pkg { my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm index e9cc7d935..cd628160c 100644 --- a/FS/FS/cust_pkg.pm +++ b/FS/FS/cust_pkg.pm @@ -2733,21 +2733,32 @@ sub search { '' => {}, ); - foreach my $field (qw( setup last_bill bill adjourn susp expire cancel )) { + if( exists($params->{'active'} ) ) { + # This overrides all the other date-related fields + my($beginning, $ending) = @{$params->{'active'}}; + push @where, + "cust_pkg.setup IS NOT NULL", + "cust_pkg.setup <= $ending", + "(cust_pkg.cancel IS NULL OR cust_pkg.cancel >= $beginning )", + "NOT (".FS::cust_pkg->onetime_sql . ")"; + } + else { + foreach my $field (qw( setup last_bill bill adjourn susp expire cancel )) { - next unless exists($params->{$field}); + next unless exists($params->{$field}); - my($beginning, $ending) = @{$params->{$field}}; + my($beginning, $ending) = @{$params->{$field}}; - next if $beginning == 0 && $ending == 4294967295; + next if $beginning == 0 && $ending == 4294967295; - push @where, - "cust_pkg.$field IS NOT NULL", - "cust_pkg.$field >= $beginning", - "cust_pkg.$field <= $ending"; + push @where, + "cust_pkg.$field IS NOT NULL", + "cust_pkg.$field >= $beginning", + "cust_pkg.$field <= $ending"; - $orderby ||= "ORDER BY cust_pkg.$field"; + $orderby ||= "ORDER BY cust_pkg.$field"; + } } $orderby ||= 'ORDER BY bill'; diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index db5befc8a..bb219bdd4 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -266,6 +266,7 @@ if($curuser->access_right('Financial reports')) { 'Employee Commission Report' => [ $fsurl.'search/report_employee_commission.html', '' ], 'Credit Report' => [ $fsurl.'search/report_cust_credit.html', 'Credit report (by employee and/or date range)' ], 'Refund Report' => [ $fsurl.'search/report_cust_refund.html', 'Refund report (by type and/or date range)' ], + 'Package Costs Report' => [ $fsurl.'graph/report_cust_pkg_cost.html', 'Package setup and recurring costs graph' ], ); $report_financial{'A/R Aging'} = [ $fsurl.'search/report_receivables.html', 'Accounts Receivable Aging report' ]; $report_financial{'Prepaid Income'} = [ $fsurl.'search/report_prepaid_income.html', 'Prepaid income (unearned revenue) report' ]; diff --git a/httemplate/graph/cust_pkg_cost.cgi b/httemplate/graph/cust_pkg_cost.cgi new file mode 100644 index 000000000..0aa7e3262 --- /dev/null +++ b/httemplate/graph/cust_pkg_cost.cgi @@ -0,0 +1,61 @@ +<% include('elements/monthly.html', + 'title' => $agentname. + 'Package Costs Report', + 'graph_type' => 'Lines', + 'items' => \@items, + 'labels' => \%label, + 'graph_labels' => \%label, + 'colors' => \%color, + 'links' => \%link, + 'agentnum' => $agentnum, + 'nototal' => scalar($cgi->param('12mo')), + ) +%> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +#XXX or virtual +my( $agentnum, $agent ) = ('', ''); +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $agentnum = $1; + $agent = qsearchs('agent', { 'agentnum' => $agentnum } ); + die "agentnum $agentnum not found!" unless $agent; +} + +my $agentname = $agent ? $agent->agent.' ' : ''; + +my @items = qw( cust_pkg_setup_cost cust_pkg_recur_cost ); +if ( $cgi->param('12mo') == 1 ) { + @items = map $_.'_12mo', @items; +} + +my %label = ( + 'cust_pkg_setup_cost' => 'Setup Costs', + 'cust_pkg_recur_cost' => 'Recurring Costs', +); + +$label{$_.'_12mo'} = $label{$_}. " (prev 12 months)" + foreach keys %label; + +my %color = ( + 'cust_pkg_setup_cost' => '0000cc', + 'cust_pkg_recur_cost' => '00cc00', +); +$color{$_.'_12mo'} = $color{$_} + foreach keys %color; + +my %link = ( + 'cust_pkg_setup_cost' => { 'link' => "${p}search/cust_pkg.cgi?agentnum=$agentnum;", + 'fromparam' => 'setup_begin', + 'toparam' => 'setup_end', + }, + 'cust_pkg_recur_cost' => { 'link' => "${p}search/cust_pkg.cgi?agentnum=$agentnum;", + 'fromparam' => 'active_begin', + 'toparam' => 'active_end', + }, +); +# XXX link 12mo? + +</%init> diff --git a/httemplate/graph/report_cust_pkg_cost.html b/httemplate/graph/report_cust_pkg_cost.html new file mode 100644 index 000000000..553db096b --- /dev/null +++ b/httemplate/graph/report_cust_pkg_cost.html @@ -0,0 +1,26 @@ +<% include('/elements/header.html', 'Package Costs Report' ) %> + +<FORM ACTION="cust_pkg_cost.cgi" METHOD="GET"> + +<TABLE> + +<% include('/elements/tr-select-from_to.html' ) %> + +<% include('/elements/tr-select-agent.html', + 'label' => 'For agent: ', + 'disable_empty' => 0, + ) +%> + +</TABLE> + +<BR><INPUT TYPE="submit" VALUE="Display"> +</FORM> + +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +</%init> diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 83cd206cb..adbec7a74 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -192,7 +192,7 @@ my %disable = ( '' => {}, ); -foreach my $field (qw( setup last_bill bill adjourn susp expire cancel )) { +foreach my $field (qw( setup last_bill bill adjourn susp expire cancel active )) { my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, $field); |