From: jeff Date: Thu, 13 May 2010 05:43:57 +0000 (+0000) Subject: cope with poor tax liability report performance by allowing queuing of reports RT... X-Git-Tag: freeside_1_9_4~99 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=16ac0d9bacf239ecbf24b4b7d2231981bd9efe89 cope with poor tax liability report performance by allowing queuing of reports RT#8274 --- diff --git a/FS/FS/queue.pm b/FS/FS/queue.pm index 1f2abe3ae..99e349c61 100644 --- a/FS/FS/queue.pm +++ b/FS/FS/queue.pm @@ -12,6 +12,7 @@ use FS::Record qw( qsearch qsearchs dbh ); use FS::queue_arg; use FS::queue_depend; use FS::cust_svc; +use FS::CGI qw (rooturl); @ISA = qw(FS::Record); @EXPORT_OK = qw( joblisting ); @@ -192,6 +193,14 @@ sub delete { my @del = qsearch( 'queue_arg', { 'jobnum' => $self->jobnum } ); push @del, qsearch( 'queue_depend', { 'depend_jobnum' => $self->jobnum } ); + my $reportname = ''; + if ( $self->status =~/^done/ ) { + my $dropstring = rooturl(). '/misc/queued_report\?report='; + if ($self->statustext =~ /.*$dropstring([.\w]+)\>/) { + $reportname = "$FS::UID::cache_dir/cache.$FS::UID::datasrc/report.$1"; + } + } + my $error = $self->SUPER::delete; if ( $error ) { $dbh->rollback if $oldAutoCommit; @@ -207,6 +216,8 @@ sub delete { } $dbh->commit or die $dbh->errstr if $oldAutoCommit; + + unlink $reportname if $reportname; ''; @@ -235,7 +246,7 @@ sub check { $self->ut_numbern('jobnum') || $self->ut_anything('job') || $self->ut_numbern('_date') - || $self->ut_enum('status',['', qw( new locked failed )]) + || $self->ut_enum('status',['', qw( new locked failed done )]) || $self->ut_anything('statustext') || $self->ut_numbern('svcnum') ; diff --git a/FS/FS/tax_rate.pm b/FS/FS/tax_rate.pm index 75e72c542..3ccfb8b5d 100644 --- a/FS/FS/tax_rate.pm +++ b/FS/FS/tax_rate.pm @@ -27,6 +27,10 @@ use FS::part_pkg_taxproduct; use FS::cust_main; use FS::Misc qw( csv_from_fixed ); +#i'd like to dump these +use FS::CGI qw(rooturl popurl); +use URI::Escape; + @ISA = qw( FS::Record ); $DEBUG = 0; @@ -1757,12 +1761,250 @@ sub browse_queries { return ($query, "SELECT COUNT(*) FROM tax_rate $extra_sql"); } +=item queue_liability_report PARAMS + +Launches a tax liability report. +=cut + +sub queue_liability_report { + my $cgi = shift; + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); + my $agentnum = $cgi->param('agentnum'); + $agentnum =~ /^(\d+)$/ ? $agentnum = $1 : $agentnum = ''; + my $job = new FS::queue { job => 'FS::tax_rate::generate_liability_report' }; + $job->insert( + 'beginning' => $beginning, + 'ending' => $ending, + 'agentnum' => $agentnum, + 'p' => popurl(2), + 'rooturl' => rooturl, + ); +} + +=item generate_liability_report PARAMS + +Generates a tax liability report. Provide a hash including desired +agentnum, beginning, and ending + +=cut + +sub generate_liability_report { + my %args = @_; + + #let us open the temp file early + my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc; + my $report = new File::Temp( TEMPLATE => 'report.tax.liability.XXXXXXXX', + DIR => $dir, + UNLINK => 0, # not so temp + ) or die "can't open report file: $!\n"; + + my $conf = new FS::Conf; + my $money_char = $conf->config('money_char') || '$'; + + my $join_cust = " + JOIN cust_bill USING ( invnum ) + LEFT JOIN cust_main USING ( custnum ) + "; + + my $join_loc = + "LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum )"; + my $join_tax_loc = "LEFT JOIN tax_rate_location USING ( taxratelocationnum )"; + + my $addl_from = " $join_cust $join_loc $join_tax_loc "; + + my $where = "WHERE _date >= $args{beginning} AND _date <= $args{ending} "; + + my $agentname = ''; + if ( $args{agentnum} =~ /^(\d+)$/ ) { + my $agent = qsearchs('agent', { 'agentnum' => $1 } ); + die "agent not found" unless $agent; + $agentname = $agent->agent; + $where .= ' AND cust_main.agentnum = '. $agent->agentnum; + } + + # my ( $location_sql, @location_param ) = FS::cust_pkg->location_sql; + # $where .= " AND $location_sql"; + #my @taxparam = ( 'itemdesc', @location_param ); + # now something along the lines of geocode matching ? + #$where .= FS::cust_pkg->_location_sql_where('cust_tax_location');; + my @taxparam = ( 'itemdesc', 'tax_rate_location.state', 'tax_rate_location.county', 'tax_rate_location.city', 'cust_bill_pkg_tax_rate_location.locationtaxid' ); + + my $select = 'DISTINCT itemdesc,locationtaxid,tax_rate_location.state,tax_rate_location.county,tax_rate_location.city'; + + #false laziness w/FS::Report::Table::Monthly (sub should probably be moved up + #to FS::Report or FS::Record or who the fuck knows where) + my $scalar_sql = sub { + my( $r, $param, $sql ) = @_; + my $sth = dbh->prepare($sql) or die dbh->errstr; + $sth->execute( map $r->$_(), @$param ) + or die "Unexpected error executing statement $sql: ". $sth->errstr; + $sth->fetchrow_arrayref->[0] || 0; + }; + + my $tax = 0; + my $credit = 0; + my %taxes = (); + my %basetaxes = (); + foreach my $t (qsearch({ table => 'cust_bill_pkg', + select => $select, + hashref => { pkgpart => 0 }, + addl_from => $addl_from, + extra_sql => $where, + }) + ) + { + my @params = map { my $f = $_; $f =~ s/.*\.//; $f } @taxparam; + my $label = join('~', map { $t->$_ } @params); + $label = 'Tax'. $label if $label =~ /^~/; + unless ( exists( $taxes{$label} ) ) { + my ($baselabel, @trash) = split /~/, $label; + + $taxes{$label}->{'label'} = join(', ', split(/~/, $label) ); + $taxes{$label}->{'url_param'} = + join(';', map { "$_=". uri_escape($t->$_) } @params); + + my $taxwhere = "FROM cust_bill_pkg $addl_from $where AND payby != 'COMP' ". + "AND ". join( ' AND ', map { "( $_ = ? OR ? = '' AND $_ IS NULL)" } @taxparam ); + + my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) ". + " $taxwhere AND cust_bill_pkg.pkgnum = 0"; + + my $x = &{$scalar_sql}($t, [ map { $_, $_ } @params ], $sql ); + $tax += $x; + $taxes{$label}->{'tax'} += $x; + + unless ( exists( $taxes{$baselabel} ) ) { + + $basetaxes{$baselabel}->{'label'} = $baselabel; + $basetaxes{$baselabel}->{'url_param'} = "itemdesc=$baselabel"; + $basetaxes{$baselabel}->{'base'} = 1; + + } + + $basetaxes{$baselabel}->{'tax'} += $x; + + } + + # calculate customer-exemption for this tax + # calculate package-exemption for this tax + # calculate monthly exemption (texas tax) for this tax + # count up all the cust_tax_exempt_pkg records associated with + # the actual line items. + } + + + #ordering + my @taxes = (); + + foreach my $tax ( sort { $a cmp $b } keys %taxes ) { + my ($base, @trash) = split '~', $tax; + my $basetax = delete( $basetaxes{$base} ); + if ($basetax) { + if ( $basetax->{tax} == $taxes{$tax}->{tax} ) { + $taxes{$tax}->{base} = 1; + } else { + push @taxes, $basetax; + } + } + push @taxes, $taxes{$tax}; + } + + push @taxes, { + 'label' => 'Total', + 'url_param' => '', + 'tax' => $tax, + 'base' => 1, + }; + + + my $dateagentlink = "begin=$args{beginning};end=$args{ending}"; + $dateagentlink .= ';agentnum='. $args{agentnum} + if length($agentname); + my $baselink = $args{p}. "search/cust_bill_pkg.cgi?$dateagentlink"; + + + print $report < + + <% include('/elements/table-grid.html') %> + + + + + Tax collected + +EOF + + my $bgcolor1 = '#eeeeee'; + my $bgcolor2 = '#ffffff'; + my $bgcolor = ''; + + foreach my $tax ( @taxes ) { + + if ( $bgcolor eq $bgcolor1 ) { + $bgcolor = $bgcolor2; + } else { + $bgcolor = $bgcolor1; + } + + my $link = ''; + if ( $tax->{'label'} ne 'Total' ) { + $link = ';'. $tax->{'url_param'}; + } + + print $report < + <% '$tax->{label}' %> + <% $tax->{base} ? qq!! : '' %> + + <% '$money_char' %><% sprintf('%.2f', $tax->{'tax'} ) %> + + <% !($tax->{base}) ? qq!! : '' %> + +EOF + } + + print $report < + + + +EOF + + my $reportname = $report->filename; + close $report; + + my $dropstring = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/report.'; + $reportname =~ s/^$dropstring//; + + my $reporturl = $args{rooturl}. "/misc/queued_report?report=$reportname"; + die "view\n"; + +} + + + =back =head1 BUGS Mixing automatic and manual editing works poorly at present. + Tax liability calculations take too long and arguably don't belong here. + Tax liability report generation not entirely safe (escaped). + =head1 SEE ALSO L, L, L, schema.html from the base diff --git a/FS/bin/freeside-queued b/FS/bin/freeside-queued index d5d84cced..3734bf893 100644 --- a/FS/bin/freeside-queued +++ b/FS/bin/freeside-queued @@ -207,10 +207,14 @@ while (1) { warn 'running "&'. $ljob->job. '('. join(', ', @args). ")\n" if $DEBUG; eval $eval; #throw away return value? suppose so if ( $@ ) { - warn "job $eval failed"; my %hash = $ljob->hash; - $hash{'status'} = 'failed'; $hash{'statustext'} = $@; + if ( $hash{'statustext'} =~ /\/misc\/queued_report/ ) { #use return? + $hash{'status'} = 'done'; + } else { + $hash{'status'} = 'failed'; + warn "job $eval failed"; + } my $fjob = new FS::queue( \%hash ); my $error = $fjob->replace($ljob); die $error if $error; diff --git a/httemplate/misc/queued_report.html b/httemplate/misc/queued_report.html new file mode 100755 index 000000000..875404546 --- /dev/null +++ b/httemplate/misc/queued_report.html @@ -0,0 +1,29 @@ +<% include($report_comp) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my $report = ''; +$cgi->param('report') =~ /^([.\w]+)$/ && ($report = $1); +die "no such report" unless $report; + +my $prefix = "$FS::UID::cache_dir/cache.$FS::UID::datasrc"; +open my $fh, "$prefix/report.$report" + or die "can't open report: $!\n"; + +my $reportvalue = ''; +{ + local $/; + $reportvalue = <$fh>; +} +close $fh; + +#my ($interp, $other) = FS::Mason::mason_interps('standalone'); +my $interp = $m->interp; +my $report_comp = + eval { $interp->make_component ( comp_source => $reportvalue ) }; +die $@ if $@; +errorpage($@) if $@; + + diff --git a/httemplate/search/queue.html b/httemplate/search/queue.html index 125a6f7f6..e5f7aed6a 100644 --- a/httemplate/search/queue.html +++ b/httemplate/search/queue.html @@ -50,10 +50,14 @@ || ( ! $noactions && $status =~ /^failed/ || $status =~ /^locked/ + || $status =~ /^done/ ); if ( $changable ) { + $status .= qq! (!; + $status .= + qq!  retry |! + unless $status =~ /^done/; $status .= - qq! ( retry |!. qq! remove )!; } $status; diff --git a/httemplate/search/report_newtax.html b/httemplate/search/report_newtax.html index daf2d23b6..2588b48d3 100755 --- a/httemplate/search/report_newtax.html +++ b/httemplate/search/report_newtax.html @@ -1,6 +1,6 @@ <% include('/elements/header.html', 'Tax Report' ) %> -
+ diff --git a/httemplate/search/report_queued_newtax.cgi b/httemplate/search/report_queued_newtax.cgi new file mode 100755 index 000000000..1d5813ece --- /dev/null +++ b/httemplate/search/report_queued_newtax.cgi @@ -0,0 +1,16 @@ +<% include("/elements/header.html", "Queue Tax Report") %> +<% include("/elements/error.html") %> +% unless ($error) { +
+ Report queued. Check the job queue for status. +
+% } +<% include("/elements/footer.html") %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my $error = FS::tax_rate::queue_liability_report($cgi); + +