'start_year' => 2000,
'end_month' => 4,
'end_year' => 2020,
+ #opt
+ 'agentnum' => 54
+ 'params' => [ [ 'paramsfor', 'item_one' ], [ 'item', 'two' ] ], # ...
+ 'remove_empty' => 1, #collapse empty rows, default 0
+ 'item_labels' => [ ], #useful with remove_empty
);
my $data = $report->data;
sub data {
my $self = shift;
+ #use Data::Dumper;
+ #warn Dumper($self);
+
my $smonth = $self->{'start_month'};
my $syear = $self->{'start_year'};
my $emonth = $self->{'end_month'};
my $eperiod = timelocal(0,0,0,1,$smonth-1,$syear);
push @{$data{eperiod}}, $eperiod;
+ my $col = 0;
+ my @row = ();
+ foreach my $item ( @{$self->{'items'}} ) {
+ my @param = $self->{'params'} ? @{ $self->{'params'}[$col] }: ();
+ my $value = $self->$item($speriod, $eperiod, $agentnum, @param);
+ #push @{$data{$item}}, $value;
+ push @{$data{data}->[$col++]}, $value;
+ }
+
+ }
+
+ #these need to get generalized, sheesh
+ $data{'items'} = $self->{'items'};
+ $data{'item_labels'} = $self->{'item_labels'} || $self->{'items'};
+ $data{'colors'} = $self->{'colors'};
+ $data{'links'} = $self->{'links'} || [];
+
+ #use Data::Dumper;
+ #warn Dumper(\%data);
+
+ if ( $self->{'remove_empty'} ) {
+
+ #warn "removing empty rows\n";
+
+ my $col = 0;
+ #these need to get generalized, sheesh
+ my @newitems = ();
+ my @newlabels = ();
+ my @newdata = ();
+ my @newcolors = ();
+ my @newlinks = ();
foreach my $item ( @{$self->{'items'}} ) {
- push @{$data{$item}}, $self->$item($speriod, $eperiod, $agentnum);
+
+ if ( grep { $_ != 0 } @{$data{'data'}->[$col]} ) {
+ push @newitems, $data{'items'}->[$col];
+ push @newlabels, $data{'item_labels'}->[$col];
+ push @newdata, $data{'data'}->[$col];
+ push @newcolors, $data{'colors'}->[$col];
+ push @newlinks, $data{'links'}->[$col];
+ }
+
+ $col++;
}
+ $data{'items'} = \@newitems;
+ $data{'item_labels'} = \@newlabels;
+ $data{'data'} = \@newdata;
+ $data{'colors'} = \@newcolors;
+ $data{'links'} = \@newlinks;
+
}
+ #use Data::Dumper;
+ #warn Dumper(\%data);
+
\%data;
}
sub invoiced { #invoiced
my( $self, $speriod, $eperiod, $agentnum ) = @_;
+
$self->scalar_sql("
SELECT SUM(charged)
FROM cust_bill
LEFT JOIN cust_main USING ( custnum )
WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
);
+
}
sub netsales { #net sales
timelocal($sec,$min,$hour,$mday,$mon,$year);
}
+sub cust_bill_pkg {
+ my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
+
+ my $where = '';
+ if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
+ if ( $1 == 0 ) {
+ $where = "classnum IS NULL";
+ } else {
+ $where = "classnum = $1";
+ }
+ }
+
+ $agentnum ||= $opt{'agentnum'};
+
+ $self->scalar_sql("
+ SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)
+ FROM cust_bill_pkg
+ LEFT JOIN cust_bill USING ( invnum )
+ LEFT JOIN cust_main USING ( custnum )
+ LEFT JOIN cust_pkg USING ( pkgnum )
+ LEFT JOIN part_pkg USING ( pkgpart )
+ WHERE pkgnum != 0
+ AND $where
+ AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
+ );
+
+}
+
# NEEDS TO BE AGENTNUM-capable
sub canceled { #active
my( $self, $speriod, $eperiod, $agentnum ) = @_;
+++ /dev/null
-<%
-
-#my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
-my ($curmon,$curyear) = (localtime(time))[4,5];
-
-#find first month
-my $syear = $cgi->param('syear') || 1899+$curyear;
-my $smonth = $cgi->param('smonth') || $curmon+1;
-
-#find last month
-my $eyear = $cgi->param('eyear') || 1900+$curyear;
-my $emonth = $cgi->param('emonth') || $curmon+1;
-#if ( $emonth++>12 ) { $emonth-=12; $eyear++; }
-
-# XXX or virtual
-my $agentnum = '';
-if ( $cgi->param('agentnum') =~ /^(\d*)$/ ) {
- $agentnum = $1;
-}
-
-#my @labels;
-#my %data;
-
-my @items = qw( invoiced netsales credits payments receipts );
-if ( $cgi->param('12mo') == 1 ) {
- @items = map $_.'_12mo', @items;
-}
-
-my %label = (
- 'invoiced' => 'Gross Sales (invoiced)',
- 'netsales' => 'Net Sales (invoiced - applied credits)',
- 'credits' => 'Credits',
- 'payments' => 'Gross Receipts (payments)',
- 'receipts' => 'Net Receipts/Cashflow (payments - refunds)',
-);
-$label{$_.'_12mo'} = $label{$_}. " (previous 12 months)"
- foreach keys %label;
-
-my %color = (
- 'invoiced' => [ 153, 153, 255 ], #light blue
- 'netsales' => [ 0, 0, 204 ], #blue
- 'credits' => [ 204, 0, 0 ], #red
- 'payments' => [ 153, 204, 153 ], #light green
- 'receipts' => [ 0, 204, 0 ], #green
-);
-$color{$_.'_12mo'} = $color{$_}
- foreach keys %color;
-
-my $report = new FS::Report::Table::Monthly (
- 'items' => \@items,
- 'start_month' => $smonth,
- 'start_year' => $syear,
- 'end_month' => $emonth,
- 'end_year' => $eyear,
- 'agentnum' => $agentnum,
-);
-my %data = %{$report->data};
-
-#my $chart = Chart::LinesPoints->new(1024,480);
-#my $chart = Chart::LinesPoints->new(768,480);
-my $chart = Chart::LinesPoints->new(976,384);
-
-my $d = 0;
-$chart->set(
- #'min_val' => 0,
- 'legend' => 'bottom',
- 'colors' => { ( map { 'dataset'.$d++ => $color{$_} } @items ),
- #'grey_background' => [ 211, 211, 211 ],
- 'grey_background' => 'white',
- 'background' => [ 0xe8, 0xe8, 0xe8 ], #grey
- },
- #'grey_background' => 'false',
- 'legend_labels' => [ map { $label{$_} } @items ],
- 'brush_size' => 4,
- #'pt_size' => 12,
-);
-
-my @data = map { $data{$_} } ( 'label', @items );
-
-http_header('Content-Type' => 'image/png' );
-
-$chart->_set_colors();
-
-%><%= $chart->scalar_png(\@data) %>
}
my $hue = 0;
-my $hue_increment = 145;
+#my $hue_increment = 170;
+#my $hue_increment = 145;
+my $hue_increment = 125;
-my @items = ();
+my @items = ();
my @params = ();
my @labels = ();
my @colors = ();
+my @links = ();
+
+my $link = "${p}search/cust_bill_pkg.cgi?nottax=1;include_comp_cust=1";
foreach my $agent ( $sel_agent || qsearch('agent', { 'disabled' => '' } ) ) {
my $col_scheme = Color::Scheme->new
- ->from_hue($hue)
+ ->from_hue($hue) #->from_hex($agent->color)
->scheme('analogic')
;
my @recur_colors = ();
: ''
);
- # push some params
- push @params,
- [ 'classnum' => ( ref($pkg_class) ? $pkg_class->classnum : '' ) ];
+ my $row_classnum = ref($pkg_class) ? $pkg_class->classnum : 0;
+ my $row_agentnum = $agent->agentnum;
+ push @params, [ 'classnum' => $row_classnum,
+ 'agentnum' => $row_agentnum,
+ ];
+
+ push @links, "$link;agentnum=$row_agentnum;classnum=$row_classnum;";
@recur_colors = ($col_scheme->colors)[0,4,8,1,5,9]
unless @recur_colors;
}
-use Data::Dumper;
-warn Dumper(\@items);
+#use Data::Dumper;
+#warn Dumper(\@items);
%><%= include('elements/monthly.html',
- 'title' => $title. 'Sales Report',
+ 'title' => $title. 'Sales Report (Gross)',
'graph_type' => 'Mountain',
'items' => \@items,
'params' => \@params,
'labels' => \@labels,
'graph_labels' => \@labels,
'colors' => \@colors,
- #'links' => \%link,
+ 'links' => \@links,
+ 'remove_empty' => 1,
+ 'bottom_total' => 1,
+ 'bottom_link' => "$link;",
'start_month' => $smonth,
'start_year' => $syear,
'end_month' => $emonth,
# 'title' => 'Page title',
# 'items' => \@items,
# 'params' => \@params, # opt,
- # 'labels' => \@labels, # or \%labels (keys are items)
- # 'graph_labels' => \%graph_labels,
- # 'colors' => \%colors,
- # 'links => \%link, #opt
+ # 'labels' => \@labels, # or \%labels (keys are items)
+ # 'graph_labels' => \@graph_labels, # or \%graph_labels,
+ # 'colors' => \@colors, # or \%colors,
+ # 'links => \@links, # or \%link, #opt
# 'start_month' => $smonth,
# 'start_year' => $syear,
# 'end_month' => $emonth,
# 'agentnum' => $agentnum, #opt
# 'nototal' => 1, #opt,
# 'graph_type' => 'LinesPoints', #opt
+ # 'remove_empty' => 1, #opt,
+ # 'bottom_total' => 1, #opt,
my(%opt) = @_;
my @items = @{ $opt{'items'} };
- #foreach my $other (qw( labels graph_labels colors links )) {
- foreach my $other (qw( labels graph_labels colors )) {
+ foreach my $other (qw( labels graph_labels colors links )) {
+ #foreach my $other (qw( labels graph_labels colors )) {
if ( ref($opt{$other}) eq 'HASH' ) {
$opt{$other} = [ map $opt{$other}{$_}, @items ];
}
}
my $report = new FS::Report::Table::Monthly (
+
#'items' => $opt{'items'},
- 'items' => \@items,
- 'params' => $opt{'params'},
- 'start_month' => $opt{'start_month'},
- 'start_year' => $opt{'start_year'},
- 'end_month' => $opt{'end_month'},
- 'end_year' => $opt{'end_year'},
- 'agentnum' => $opt{'agentnum'},
+ 'items' => \@items,
+ 'params' => $opt{'params'},
+ 'item_labels' => ( $cgi->param('_type') =~ /^(png)$/
+ ? $opt{'graph_labels'}
+ : $opt{'labels'}
+ ),
+ 'colors' => $opt{'colors'},
+ 'links' => $opt{'links'},
+
+ 'start_month' => $opt{'start_month'},
+ 'start_year' => $opt{'start_year'},
+ 'end_month' => $opt{'end_month'},
+ 'end_year' => $opt{'end_year'},
+
+ 'agentnum' => $opt{'agentnum'},
+ 'remove_empty' => $opt{'remove_empty'},
);
my $data = $report->data;
'dataset'.$d++ =>
[ map hex($_), unpack 'a2a2a2', $color ]
}
- @{ $opt{'colors'} }
+ #@{ $opt{'colors'} }
+ @{ $data->{'colors'} }
),
#'grey_background' => [ 211, 211, 211 ],
'grey_background' => 'white',
'background' => [ 0xe8, 0xe8, 0xe8 ], #grey
},
#'grey_background' => 'false',
- 'legend_labels' => $opt{'graph_labels'},
+ 'legend_labels' => $data->{'item_labels'},
'brush_size' => 4,
#'pt_size' => 12,
);
</TR>
-<% foreach my $row (@items) {
+<% my @bottom_total = ();
+ foreach my $row ( @{ $data->{'items'} } ) {
- my $color = shift( @{ $opt{'colors'} } );
+ #my $color = shift( @{ $opt{'colors'} } );
+ my $color = shift( @{ $data->{'colors'} } );
+ my $link = shift( @{ $data->{'links'} } );
+ $link = $link ? qq(<A HREF="$link) : '';
%>
<TR>
- <TH><FONT COLOR="#<%= $color %>"><%= shift( @{ $opt{'labels'} } ) %></FONT></TH>
+ <TH><FONT COLOR="#<%= $color %>"><%= shift( @{ $data->{'item_labels'} } ) %></FONT></TH>
- <% my $link = exists($opt{'links'}{$row})
- ? qq(<A HREF="$opt{'links'}{$row})
- : '';
+ <% #my $link = exists($opt{'links'}{$row})
+
# ? qq(<A HREF="$opt{'links'}{$row})
+ # : '';
my @speriod = @{$data->{speriod}};
my @eperiod = @{$data->{eperiod}};
my $total = 0;
%>
- <% foreach my $column ( @{ shift( @{$data->{data}} ) } ) { # ( @{$data->{$row}} ) {
+ <% my $col = 0;
+ foreach my $column ( @{ shift( @{$data->{data}} ) } ) { # ( @{$data->{$row}} ) {
%>
<TD ALIGN="right" BGCOLOR="#ffffff">
<%= $link ? $link. 'begin='. shift(@speriod). ';end='. shift(@eperiod). '">' : '' %><FONT COLOR="#<%= $color %>">$<%= sprintf("%.2f", $column) %></FONT><%= $link ? '</A>' : '' %>
</TD>
- <% $total += $column; %>
+ <%
+ $total += $column;
+ $bottom_total[$col++] += $column;
+ %>
<% } %>
<% unless ( $opt{'nototal'} ) { %>
<TD ALIGN="right" BGCOLOR="#f5f6be">
- <%= $link ? $link. 'begin='. @{$data->{speriod}}[0]. ';end='. @{$data->{eperiod}}[-1]. '">' : '' %><FONT COLOR="#<%= $color %>">$<%= sprintf("%.2f", $total) %></FONT><%= $link ? '</A>' : '' %>
+ <%= $link ? $link. 'begin='. ${$data->{speriod}}[0]. ';end='. ${$data->{eperiod}}[-1]. '">' : '' %><FONT COLOR="#<%= $color %>">$<%= sprintf("%.2f", $total) %></FONT><%= $link ? '</A>' : '' %>
+ </TD>
+
+ <% $bottom_total[$col++] += $total; %>
+
+ <% } %>
+
+ </TR>
+
+<% } %>
+
+<% if ( $opt{'bottom_total'} ) {
+ my @speriod = ( @{$data->{speriod}}, ${$data->{speriod}}[0] );
+ my @eperiod = ( @{$data->{eperiod}}, ${$data->{eperiod}}[-1] );
+%>
+
+ <TR>
+ <TH>Total</TH>
+
+ <% foreach my $total ( @bottom_total ) { %>
+
+ <TD ALIGN="right" BGCOLOR="#f5f6be">
+ <%= $opt{'bottom_link'}
+ ? '<A HREF="'. $opt{'bottom_link'}.
+ 'begin='. shift(@speriod).
+ ';end='. shift(@eperiod). '">'
+ : ''
+ %>$<%= sprintf("%.2f", $total) %><%= $opt{'bottom_link'} ? '</A>' : '' %>
+
</TD>
<% } %>
<LI><a href="search/cust_bill_event.html">All invoice events for a date range</a>
<LI><a href="search/cust_bill_event.html?failed=1">Invoice event errors for a date range (failed credit cards, processor or printer problems, etc.)</a>
</UL>
- <A HREF="search/report_cust_pay.html">Payment report (by type and/or date range)</A>
+ <A HREF="graph/report_money_time.html">Sales, Credits and Receipts Summary</A>
+ <BR><BR><A HREF="graph/report_cust_bill_pkg.html">Sales report (by agent, package class and/or date range)</A>
<BR><BR><A HREF="search/report_cust_credit.html">Credit report (by employee and/or date range)</A>
- <BR><BR><A HREF="graph/report_money_time.html">Sales, Credits and Receipts Summary</A>
+ <BR><BR><A HREF="search/report_cust_pay.html">Payment report (by type and/or date range)</A>
<BR><BR><A HREF="search/report_receivables.html">Accounts Receivable Aging Summary</A>
<BR><BR><A HREF="search/report_prepaid_income.html">Prepaid Income (Unearned Revenue) Report</A>
<BR><BR><A HREF="search/report_tax.html">Sales Tax Liability Report</A>
LEFT JOIN part_pkg USING ( pkgpart )
";
-my $where = "
- WHERE _date >= $beginning AND _date <= $ending
- AND payby != 'COMP'
-";
+my $where = " WHERE _date >= $beginning AND _date <= $ending ";
+
+$where .= " AND payby != 'COMP' "
+ unless $cgi->param('include_comp_cust');
if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
$where .= " AND agentnum = $1 ";
}
+if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
+ if ( $1 == 0 ) {
+ $where .= " AND classnum IS NULL ";
+ } else {
+ $where .= " AND classnum = $1 ";
+ }
+}
+
if ( $cgi->param('out') ) {
$where .= "
projects / freeside.git / commitdiff