$self->scalar_sql($total_sql);
}
-#all credits applied to matching pkg line items (ie not taxes or fees)
+#all credits applied to matching pkg line items (ie not taxes)
sub cust_bill_pkg_credits {
my $self = shift;
$report_financial{'Tax Liability (vendor tax data)'} = [ $fsurl.'search/report_newtax.html', 'Tax liability report (vendor tax data)' ]
if $taxproducts;
+ $report_financial{'Monthly Sales and Taxes'} = [$fsurl.'search/tax_sales.html', 'Monthly sales and taxes report'];
+
# most sites don't need this but there isn't really a config to enable it
$report_financial{'E911 Fee Summary'} = [ $fsurl.'search/report_e911.html', 'E911 fee summary' ];
#these run parallel to items, and can be given as hashes
'row_labels' => \@row_labels, #required
'colors' => \@colors, #required
+ 'bgcolors' => \@bgcolors, #optional
'graph_labels' => \@graph_labels, #defaults to row_labels
'links' => \@links, #optional
#optional
'nototal' => 1,
- 'graph_type' => 'LinesPoints',
+ 'graph_type' => 'LinesPoints', #can be 'none' for no graph
'bottom_total' => 1,
'sprintf' => '%u', #sprintf format, overrides default %.2f
'disable_money' => 1,
% foreach my $row ( @items ) {
% #make a style
% my $color = shift @{ $opt{'colors'} };
-% push @styles, ".i$i { text-align: right; color: #$color; }";
+% my $bgcolor = $opt{'bgcolors'} ? (shift @{ $opt{'bgcolors'} }) : 'ffffff';
+% push @styles, ".i$i { text-align: right; color: #$color; background: #$bgcolor; }";
% #create the data row
% my $links = shift @{$opt{'links'}} || [''];
% my $link_prefix = shift @$links;
+++ /dev/null
-
-<% include('/graph/elements/report.html',
- 'title' => 'Monthly Sales Tax Report',
- 'items' => \@row_labels,
- 'data' => \@rowdata,
- 'row_labels' => \@row_labels,
- 'colors' => [],
- 'col_labels' => \@col_labels,
- ) %>
-
-<%init>
-
-die "access denied"
- unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
-
-# validate cgi input
-my $start_month = $cgi->param('start_month');
-die "Bad start month" unless $start_month =~ /^\d*$/;
-my $start_year = $cgi->param('start_year');
-die "Bad start year" unless $start_year =~ /^\d*$/;
-my $end_month = $cgi->param('end_month');
-die "Bad end month" unless $end_month =~ /^\d*$/;
-my $end_year = $cgi->param('end_year');
-die "Bad end year" unless $end_year =~ /^\d*$/;
-die "End year before start year" if $end_year < $start_year;
-die "End month before start month" if ($start_year == $end_year) && ($end_month < $start_month);
-my $country = $cgi->param('country');
-die "Bad country code" unless $country =~ /^\w\w$/;
-
-# Data structure for building final table
-# row order will be calculated separately
-#
-# $data->{$rowlabel} = \@rowvalues
-#
-
-my $data = {};
-
-### Calculate package values
-
-my @pkg_class = qsearch('pkg_class');
-my @pkg_classnum = map { $_->classnum } @pkg_class;
-unshift(@pkg_classnum,0);
-my @pkg_classname = map { $_->classname } @pkg_class;
-unshift(@pkg_classname,'(empty class)');
-
-# some false laziness with graph/elements/monthly.html
-my %reportopts = (
- 'items' => [ qw( cust_bill_pkg cust_bill_pkg_credits ) ],
- 'cross_params' => [ map { [ 'classnum', $_ ] } @pkg_classnum ],
- 'start_month' => $start_month,
- 'start_year' => $start_year,
- 'end_month' => $end_month,
- 'end_year' => $end_year,
-);
-my $pkgreport = new FS::Report::Table::Monthly(%reportopts);
-my $pkgdata = $pkgreport->data;
-
-# assuming every month/year combo is included in results,
-# just use this list for the final table
-my @col_labels = @{$pkgdata->{'label'}};
-
-# unpack report data into a more manageable format
-foreach my $item ( qw( invoiced credited ) ) { # invoiced, credited
- my $itemref = shift @{$pkgdata->{'data'}};
- foreach my $label (@{$pkgdata->{'label'}}) { # month/year
- my $labelref = shift @$itemref;
- foreach my $classname (@pkg_classname) { # pkg class
- my $value = shift @$labelref;
- my $rowlabel = $classname.' '.$item;
- $data->{$rowlabel} ||= [];
- push(@{$data->{$rowlabel}},$value);
- }
- }
-}
-
-### Calculate tax values
-
-# false laziness w report_tax.html, put this in FS::Report::Tax?
-my $sth = dbh->prepare('SELECT DISTINCT(COALESCE(taxname, \'Tax\')) FROM cust_main_county');
-$sth->execute or die $sth->errstr;
-my @taxnames = map { $_->[0] } @{ $sth->fetchall_arrayref };
-$sth->finish;
-
-# get DateTime objects for start & end
-my $startdate = DateTime->new(
- year => $start_year,
- month => $start_month,
- day => 1
- );
-my $enddate = DateTime->new(
- year => $end_year,
- month => $end_month,
- day => 1
- );
-$enddate->add( months => 1 )->subtract( seconds => 1 ); # the last second of the month
-
-# common to all tax reports
-my %params = (
- 'country' => $country,
- 'credit_date' => 'cust_bill',
-);
-
-# run a report for each month, for each tax
-my $countdate = $startdate->clone;
-while ($countdate < $enddate) {
-
- # set report start date, iterate to end of this month, set report end date
- $params{'beginning'} = $countdate->epoch;
- $params{'ending'} = $countdate->add( months => 1 )->subtract( seconds => 1 )->epoch;
-
- # run a report for each tax name
- foreach my $taxname (@taxnames) {
- $params{'taxname'} = $taxname;
- my $report = FS::Report::Tax->report_internal(%params);
-
- # extract totals from report, kinda awkward
- my $pkgclass = ''; # this will get more complicated if we breakdown by pkgclass
- my @values = (0,0);
- if ($report->{'total'}->{$pkgclass}) {
- my %totals = map { $$_[0] => $$_[2] } @{$report->{'total'}->{$pkgclass}};
- $values[0] = $totals{'tax'};
- $values[1] = $totals{'credit'};
- }
-
- # treat each tax class like it's an additional pkg class
- foreach my $item ( qw ( invoiced credited ) ) {
- my $rowlabel = $taxname . ' ' . $item;
- my $value = shift @values;
- $data->{$rowlabel} ||= [];
- push(@{$data->{$rowlabel}},$value);
- }
-
- }
-
- # iterate to next month
- $countdate->add( seconds => 1 );
-}
-
-# put the data in the order we want it
-my @row_labels;
-my @rowdata;
-foreach my $classname (@pkg_classname,@taxnames) {
- my @classlabels = ();
- my @classdata = ();
- my $hasdata = 0;
- foreach my $item ( qw( invoiced credited ) ) {
- my $rowlabel = $classname . ' ' . $item;
- my $rowdata = $data->{$rowlabel};
- $hasdata = 1 if grep { $_ } @$rowdata;
- push(@classlabels,$rowlabel);
- push(@classdata,$rowdata);
- }
- next unless $hasdata; # don't include class if it has no data in time range
- push(@row_labels,@classlabels);
- push(@rowdata,@classdata);
-}
-
-</%init>
+++ /dev/null
-<% include('/elements/header.html', 'Monthly Sales Tax Report' ) %>
-
-<FORM ACTION="report_tax_sales.cgi" METHOD="GET">
-
-<TABLE>
-
- <% include('/elements/tr-select-from_to.html') %>
-
- <% include('/elements/tr-select.html',
- 'label' => 'Country',
- 'field' => 'country',
- 'options' => \@countries,
- 'curr_value' => ($conf->config('countrydefault') || 'US'),
- ) %>
-
-</TABLE>
-
-<BR><INPUT TYPE="submit" VALUE="Get Report">
-
-</FORM>
-
-<% include('/elements/footer.html') %>
-<%init>
-
-die "access denied"
- unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
-
-my $conf = new FS::Conf;
-
-# false laziness w report_tax.html, put this in FS::Report::Tax?
-my $sth = dbh->prepare('SELECT DISTINCT(country) FROM cust_location');
-$sth->execute or die $sth->errstr;
-my @countries = map { $_->[0] } @{ $sth->fetchall_arrayref };
-
-</%init>
--- /dev/null
+
+<% include('/graph/elements/report.html',
+ 'title' => 'Monthly Sales and Taxes Report',
+ 'items' => \@row_labels,
+ 'data' => \@rowdata,
+ 'row_labels' => \@row_labels,
+ 'colors' => \@rowcolors,
+ 'bgcolors' => \@rowbgcolors,
+ 'col_labels' => \@col_labels,
+ 'graph_type' => 'none',
+ ) %>
+
+<%init>
+
+die "access denied"
+ unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+
+# validate cgi input
+my $start_month = $cgi->param('start_month');
+die "Bad start month" unless $start_month =~ /^\d*$/;
+my $start_year = $cgi->param('start_year');
+die "Bad start year" unless $start_year =~ /^\d*$/;
+my $end_month = $cgi->param('end_month');
+die "Bad end month" unless $end_month =~ /^\d*$/;
+my $end_year = $cgi->param('end_year');
+die "Bad end year" unless $end_year =~ /^\d*$/;
+die "End year before start year" if $end_year < $start_year;
+die "End month before start month" if ($start_year == $end_year) && ($end_month < $start_month);
+my $country = $cgi->param('country');
+die "Bad country code" unless $country =~ /^\w\w$/;
+
+# Data structure for building final table
+# row order will be calculated separately
+#
+# $data->{$rowlabel} = \@rowvalues
+#
+
+my $data = {};
+
+### Calculate package values
+
+my @pkg_class = qsearch('pkg_class');
+my @pkg_classnum = map { $_->classnum } @pkg_class;
+unshift(@pkg_classnum,0);
+my @pkg_classname = map { $_->classname } @pkg_class;
+unshift(@pkg_classname,'(empty class)');
+
+# some false laziness with graph/elements/monthly.html
+my %reportopts = (
+ 'items' => [ qw( cust_bill_pkg cust_bill_pkg_credits ) ],
+ 'cross_params' => [ map { [ 'classnum', $_ ] } @pkg_classnum ],
+ 'start_month' => $start_month,
+ 'start_year' => $start_year,
+ 'end_month' => $end_month,
+ 'end_year' => $end_year,
+);
+my $pkgreport = new FS::Report::Table::Monthly(%reportopts);
+my $pkgdata = $pkgreport->data;
+
+# assuming every month/year combo is included in results,
+# just use this list for the final table
+my @col_labels = @{$pkgdata->{'label'}};
+
+# unpack report data into a more manageable format
+foreach my $item ( qw( invoiced credited ) ) { # invoiced, credited
+ my $itemref = shift @{$pkgdata->{'data'}};
+ foreach my $label (@{$pkgdata->{'label'}}) { # month/year
+ my $labelref = shift @$itemref;
+ foreach my $classname (@pkg_classname) { # pkg class
+ my $value = shift @$labelref;
+ my $rowlabel = $classname.' '.$item;
+ $data->{$rowlabel} ||= [];
+ push(@{$data->{$rowlabel}},$value);
+ }
+ }
+}
+
+### Calculate tax values
+
+# false laziness w report_tax.html, put this in FS::Report::Tax?
+my $sth = dbh->prepare('SELECT DISTINCT(COALESCE(taxname, \'Tax\')) FROM cust_main_county');
+$sth->execute or die $sth->errstr;
+my @taxnames = map { $_->[0] } @{ $sth->fetchall_arrayref };
+$sth->finish;
+
+# get DateTime objects for start & end
+my $startdate = DateTime->new(
+ year => $start_year,
+ month => $start_month,
+ day => 1
+ );
+my $enddate = DateTime->new(
+ year => $end_year,
+ month => $end_month,
+ day => 1
+ );
+$enddate->add( months => 1 )->subtract( seconds => 1 ); # the last second of the month
+
+# common to all tax reports
+my %params = (
+ 'country' => $country,
+ 'credit_date' => 'cust_bill',
+);
+
+# run a report for each month, for each tax
+my $countdate = $startdate->clone;
+while ($countdate < $enddate) {
+
+ # set report start date, iterate to end of this month, set report end date
+ $params{'beginning'} = $countdate->epoch;
+ $params{'ending'} = $countdate->add( months => 1 )->subtract( seconds => 1 )->epoch;
+
+ # run a report for each tax name
+ foreach my $taxname (@taxnames) {
+ $params{'taxname'} = $taxname;
+ my $report = FS::Report::Tax->report_internal(%params);
+
+ # extract totals from report, kinda awkward
+ my $pkgclass = ''; # this will get more complicated if we breakdown by pkgclass
+ my @values = (0,0);
+ if ($report->{'total'}->{$pkgclass}) {
+ my %totals = map { $$_[0] => $$_[2] } @{$report->{'total'}->{$pkgclass}};
+ $values[0] = $totals{'tax'};
+ $values[1] = $totals{'credit'};
+ }
+
+ # treat each tax class like it's an additional pkg class
+ foreach my $item ( qw ( invoiced credited ) ) {
+ my $rowlabel = $taxname . ' ' . $item;
+ my $value = shift @values;
+ $data->{$rowlabel} ||= [];
+ push(@{$data->{$rowlabel}},$value);
+ }
+
+ }
+
+ # iterate to next month
+ $countdate->add( seconds => 1 );
+}
+
+# put the data in the order we want it
+my @row_labels;
+my @rowdata;
+my @rowcolors;
+my @rowbgcolors;
+my $pkgcount = 0; #for colors
+foreach my $classname (@pkg_classname,@taxnames) {
+ my $istax = ($pkgcount++ < @pkg_classname) ? 0 : 1;
+ my @classlabels = ();
+ my @classdata = ();
+ my @classcolors = ();
+ my @classbgcolors = ();
+ my $hasdata = 0;
+ foreach my $item ( qw( invoiced credited ) ) {
+ my $rowlabel = $classname . ' ' . $item;
+ my $rowdata = $data->{$rowlabel};
+ my $rowcolor = $istax ? '0000ff' : '000000';
+ my $rowbgcolor = ($item eq 'credited') ? 'cccccc' : 'ffffff';
+ $hasdata = 1 if grep { $_ } @$rowdata;
+ push(@classlabels,$rowlabel);
+ push(@classdata,$rowdata);
+ push(@classcolors,$rowcolor);
+ push(@classbgcolors,$rowbgcolor);
+ }
+ next unless $hasdata; # don't include class if it has no data in time range
+ push(@row_labels,@classlabels);
+ push(@rowdata,@classdata);
+ push(@rowcolors,@classcolors);
+ push(@rowbgcolors,@classbgcolors);
+}
+
+</%init>
--- /dev/null
+<% include('/elements/header.html', 'Monthly Sales and Taxes Report' ) %>
+
+<FORM ACTION="tax_sales.cgi" METHOD="GET">
+
+<TABLE>
+
+ <% include('/elements/tr-select-from_to.html') %>
+
+ <% include('/elements/tr-select.html',
+ 'label' => 'Country',
+ 'field' => 'country',
+ 'options' => \@countries,
+ 'curr_value' => ($conf->config('countrydefault') || 'US'),
+ ) %>
+
+</TABLE>
+
+<BR><INPUT TYPE="submit" VALUE="Get Report">
+
+</FORM>
+
+<% include('/elements/footer.html') %>
+<%init>
+
+die "access denied"
+ unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+
+my $conf = new FS::Conf;
+
+# false laziness w report_tax.html, put this in FS::Report::Tax?
+my $sth = dbh->prepare('SELECT DISTINCT(country) FROM cust_location');
+$sth->execute or die $sth->errstr;
+my @countries = map { $_->[0] } @{ $sth->fetchall_arrayref };
+
+</%init>
projects / freeside.git / commitdiff