From: Jonathan Prykop Date: Thu, 3 Sep 2015 02:05:28 +0000 (-0500) Subject: RT#32892: Monthly Sales Tax Report [fixed names and colors] X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=9dc9789e61324f46e482107420fd27db55b846ef RT#32892: Monthly Sales Tax Report [fixed names and colors] --- diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 4b22b60b8..eeb99bac5 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -753,7 +753,7 @@ sub cust_bill_pkg_taxes { $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; diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index f96c05ea5..efd9033db 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -389,6 +389,8 @@ if( $curuser->access_right('Financial reports') ) { $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' ]; diff --git a/httemplate/graph/elements/report.html b/httemplate/graph/elements/report.html index f1b0d166d..b5d214816 100644 --- a/httemplate/graph/elements/report.html +++ b/httemplate/graph/elements/report.html @@ -11,6 +11,7 @@ Example: #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 @@ -22,7 +23,7 @@ Example: #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, @@ -231,7 +232,8 @@ any delimiter and linked from the elements in @data. % 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; diff --git a/httemplate/search/report_tax_sales.cgi b/httemplate/search/report_tax_sales.cgi deleted file mode 100644 index 5c531c343..000000000 --- a/httemplate/search/report_tax_sales.cgi +++ /dev/null @@ -1,158 +0,0 @@ - -<% 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); -} - - diff --git a/httemplate/search/report_tax_sales.html b/httemplate/search/report_tax_sales.html deleted file mode 100755 index 374a15601..000000000 --- a/httemplate/search/report_tax_sales.html +++ /dev/null @@ -1,35 +0,0 @@ -<% include('/elements/header.html', 'Monthly Sales Tax Report' ) %> - -
- - - - <% include('/elements/tr-select-from_to.html') %> - - <% include('/elements/tr-select.html', - 'label' => 'Country', - 'field' => 'country', - 'options' => \@countries, - 'curr_value' => ($conf->config('countrydefault') || 'US'), - ) %> - -
- -
- -
- -<% 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 }; - - diff --git a/httemplate/search/tax_sales.cgi b/httemplate/search/tax_sales.cgi new file mode 100644 index 000000000..4b28c934a --- /dev/null +++ b/httemplate/search/tax_sales.cgi @@ -0,0 +1,172 @@ + +<% 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); +} + + diff --git a/httemplate/search/tax_sales.html b/httemplate/search/tax_sales.html new file mode 100755 index 000000000..61cf86e2e --- /dev/null +++ b/httemplate/search/tax_sales.html @@ -0,0 +1,35 @@ +<% include('/elements/header.html', 'Monthly Sales and Taxes Report' ) %> + +
+ + + + <% include('/elements/tr-select-from_to.html') %> + + <% include('/elements/tr-select.html', + 'label' => 'Country', + 'field' => 'country', + 'options' => \@countries, + 'curr_value' => ($conf->config('countrydefault') || 'US'), + ) %> + +
+ +
+ +
+ +<% 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 }; + +