diff options
Diffstat (limited to 'httemplate')
| -rw-r--r-- | httemplate/search/cust_bill_pkg.cgi | 79 | ||||
| -rw-r--r-- | httemplate/search/prepaid_income.html (renamed from httemplate/search/report_prepaid_income.cgi) | 98 | ||||
| -rw-r--r-- | httemplate/search/report_prepaid_income.html | 19 | ||||
| -rwxr-xr-x | httemplate/search/report_tax.cgi | 69 |
4 files changed, 156 insertions, 109 deletions
diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 94860d3f2..820b17864 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -9,7 +9,9 @@ 'header' => [ emt('Description'), ( $unearned - ? ( emt('Unearned'), emt('Owed'), emt('Payment date') ) + ? ( emt('Unearned'), + emt('Owed'), # useful in 'paid' mode? + emt('Payment date') ) : ( emt('Setup charge') ) ), ( $use_usage eq 'usage' @@ -39,9 +41,10 @@ $elapsed = 0 if $elapsed < 0; my $remaining = 1 - $elapsed/$period; + my $base = ($unearned_mode eq 'paid' ? 'total_paid' : 'recur'); sprintf($money_char. '%.2f', - $remaining * $cust_bill_pkg->recur ); + $remaining * $cust_bill_pkg->$base ); } else { sprintf($money_char.'%.2f', $cust_bill_pkg->setup ); @@ -137,6 +140,12 @@ die "access denied" my $conf = new FS::Conf; my $unearned = ''; +my $unearned_mode = ''; +my $unearned_base = ''; + +my @select = ( 'cust_bill_pkg.*', + 'cust_bill._date', ); +my ($join_cust, $join_pkg ) = ('', ''); #here is the agent virtualization my $agentnums_sql = @@ -146,14 +155,18 @@ my @where = ( $agentnums_sql ); my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +if ( $cgi->param('status') =~ /^([a-z]+)$/ ) { + push @where, FS::cust_main->cust_status_sql . " = '$1'"; +} + if ( $cgi->param('distribute') == 1 ) { push @where, "sdate <= $ending", "edate > $beginning", ; } else { - push @where, "_date >= $beginning", - "_date <= $ending"; + push @where, "cust_bill._date >= $beginning", + "cust_bill._date <= $ending"; } if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { @@ -218,7 +231,7 @@ if ( $cgi->param('taxclass') } -my @loc_param = qw( city county state country ); +my @loc_param = qw( district city county state country ); if ( $cgi->param('out') ) { @@ -266,7 +279,7 @@ if ( $cgi->param('out') ) { my %ph = ( 'county' => dbh->quote($_), map { $_ => dbh->quote( $cgi->param($_) ) } - qw( city state country ) + qw( district city state country ) ); my ( $loc_sql, @param ) = FS::cust_pkg->location_sql; @@ -330,22 +343,46 @@ if ( $cgi->param('out') ) { push @where, FS::tax_rate_location->location_sql( map { $_ => (scalar($cgi->param($_)) || '') } - qw( city county state locationtaxid ) + qw( district city county state locationtaxid ) ); } elsif ( $cgi->param('unearned_now') =~ /^(\d+)$/ ) { $unearned = $1; + $unearned_mode = $cgi->param('mode'); push @where, "cust_bill_pkg.sdate < $unearned", "cust_bill_pkg.edate > $unearned", "cust_bill_pkg.recur != 0", - "part_pkg.freq != '0'", + "part_pkg.freq != '0'"; + + if ( !$cgi->param('include_monthly') ) { + push @where, "part_pkg.freq != '1'", "part_pkg.freq NOT LIKE '%h'", "part_pkg.freq NOT LIKE '%d'", "part_pkg.freq NOT LIKE '%w'"; + } + if ( !$unearned_mode or $unearned_mode eq 'billed' ) { + $unearned_base = 'cust_bill_pkg.recur'; + } + elsif ( $unearned_mode eq 'paid' ) { + $join_pkg .= "JOIN ( + SELECT billpkgnum, SUM(cust_bill_pay_pkg.amount) AS total_paid + FROM cust_bill_pay_pkg + JOIN cust_bill_pay USING (billpaynum) + JOIN cust_pay USING (paynum) + WHERE cust_bill_pay_pkg.setuprecur = 'recur' + AND cust_pay._date <= $unearned + GROUP BY billpkgnum + ) AS cust_bill_pkg_paid USING (billpkgnum)"; + $unearned_base = 'total_paid'; + push @select, 'total_paid'; + } + else { + die "invalid mode '$unearned_mode'"; + } } if ( $cgi->param('itemdesc') ) { @@ -468,7 +505,7 @@ if ( $cgi->param('pkg_tax') ) { } elsif ( $use_usage eq 'usage' ) { $count_query .= "SUM(usage)"; } elsif ( $unearned ) { - $count_query .= "SUM(cust_bill_pkg.recur)"; + $count_query .= "SUM($unearned_base)"; } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) { $count_query .= "SUM( COALESCE(cust_bill_pkg_tax_rate_location.amount, cust_bill_pkg.setup + cust_bill_pkg.recur))"; } elsif ( $cgi->param('iscredit') eq 'rate') { @@ -492,23 +529,21 @@ if ( $cgi->param('pkg_tax') ) { my $remaining = "(1 - $elapsed/$period)"; - $count_query .= ", SUM($remaining * cust_bill_pkg.recur)"; + $count_query .= ", SUM($remaining * $unearned_base)"; } } -my $join_cust = ' JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) '; +$join_cust = ' JOIN cust_bill USING ( invnum ) + LEFT JOIN cust_main USING ( custnum ) '; - -my $join_pkg; if ( $cgi->param('nottax') ) { - $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) - LEFT JOIN part_pkg AS override - ON pkgpart_override = override.pkgpart '; + $join_pkg .= ' LEFT JOIN cust_pkg USING ( pkgnum ) + LEFT JOIN part_pkg USING ( pkgpart ) + LEFT JOIN part_pkg AS override + ON pkgpart_override = override.pkgpart '; $join_pkg .= ' LEFT JOIN cust_location USING ( locationnum ) ' if $conf->exists('tax-pkg_address'); @@ -567,9 +602,6 @@ if ($use_usage) { $count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where"; } -my @select = ( 'cust_bill_pkg.*', - 'cust_bill._date', ); - push @select, 'part_pkg.pkg', 'part_pkg.freq', unless $cgi->param('istax'); @@ -583,7 +615,7 @@ my $query = { 'hashref' => {}, 'select' => join(', ', @select ), 'extra_sql' => $where, - 'order_by' => 'ORDER BY _date, billpkgnum', + 'order_by' => 'ORDER BY cust_bill._date, billpkgnum', }; my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ]; @@ -593,9 +625,8 @@ my $conf = new FS::Conf; my $money_char = $conf->config('money_char') || '$'; my $owed_sub = sub { - $money_char. shift->owed_recur; #_recur :/ + $money_char . shift->owed_recur; }; - my $payment_date_sub = sub { #my $cust_bill_pkg = shift; my @cust_pay = sort { $a->_date <=> $b->_date } diff --git a/httemplate/search/report_prepaid_income.cgi b/httemplate/search/prepaid_income.html index 2fe5b6f10..d224af9d7 100644 --- a/httemplate/search/report_prepaid_income.cgi +++ b/httemplate/search/prepaid_income.html @@ -58,7 +58,9 @@ <% $actual_label %><% $actual_label ? 'u' : 'U' %>nearned revenue is the amount of unearned revenue <% $actual_label ? 'Freeside has actually' : '' %> -invoiced for packages with longer-than monthly terms. +invoiced for packages with +<% $cgi->param('include_monthly') ? 'terms extending into the future.' + : 'longer-than monthly terms.' %> % if ( $legacy ) { <BR><BR> @@ -82,13 +84,19 @@ my $actual_label = $legacy ? 'Actual ' : ''; #doesn't yet deal with daily/weekly packages +my $mode = $cgi->param('mode'); + my $time = time; my $now = $cgi->param('date') && parse_datetime($cgi->param('date')) || $time; $now =~ /^(\d+)$/ or die "unparsable date?"; $now = $1; -my $link = "cust_bill_pkg.cgi?nottax=1;unearned_now=$now"; +my $link = "cust_bill_pkg.cgi?nottax=1;unearned_now=$now;mode=$mode"; + +if ( $cgi->param('include_monthly') ) { + $link .= ';include_monthly=1'; +} my $curuser = $FS::CurrentUser::CurrentUser; @@ -108,6 +116,13 @@ my @where = (); #here is the agent virtualization push @where, $curuser->agentnums_sql( 'table'=>'cust_main' ); +my $status = ''; +if ( $cgi->param('status') =~ /^([a-z]+)$/ ) { + $status = $1; + $link .= ";status=$status"; + push @where, FS::cust_main->cust_status_sql . " = '$status'"; +} + my %total = (); my %total_legacy = (); foreach my $agentnum (@agentnums) { @@ -117,38 +132,6 @@ foreach my $agentnum (@agentnums) { my( $total, $total_legacy ) = ( 0, 0 ); - # my @cust_bill_pkg = - # grep { $_->cust_pkg && $_->cust_pkg->part_pkg->freq !~ /^([01]|\d+[hdw])$/ } - # qsearch({ - # 'select' => 'cust_bill_pkg.*', - # 'table' => 'cust_bill_pkg', - # 'addl_from' => ' LEFT JOIN cust_bill USING ( invnum ) '. - # ' LEFT JOIN cust_main USING ( custnum ) ', - # 'hashref' => { - # 'recur' => { op=>'!=', value=>0 }, - # 'sdate' => { op=>'<', value=>$now }, - # 'edate' => { op=>'>', value=>$now }, - # }, - # 'extra_sql' => $where, - # }); - # - # foreach my $cust_bill_pkg ( @cust_bill_pkg) { - # my $period = $cust_bill_pkg->edate - $cust_bill_pkg->sdate; - # - # my $elapsed = $now - $cust_bill_pkg->sdate; - # $elapsed = 0 if $elapsed < 0; - # - # my $remaining = 1 - $elapsed/$period; - # - # my $unearned = $remaining * $cust_bill_pkg->recur; - # $total += $unearned; - # - # } - - #re-written in sql: - - #false laziness w/cust_bill_pkg.cgi - my $float = 'REAL'; #'DOUBLE PRECISION'; my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS $float)"; @@ -160,23 +143,46 @@ foreach my $agentnum (@agentnums) { my $remaining = "(1 - $elapsed/$period)"; - my $select = "SUM($remaining * cust_bill_pkg.recur)"; + my $base; + my $from = ''; + if ( $mode eq 'billed' ) { + $base = 'cust_bill_pkg.recur'; + $from = 'cust_bill_pkg'; + } + elsif ( $mode eq 'paid' ) { + $base = 'cust_bill_pay_pkg.amount'; + $from = 'cust_bill_pay_pkg + LEFT JOIN cust_bill_pkg USING ( billpkgnum ) + LEFT JOIN cust_bill_pay USING ( billpaynum ) + LEFT JOIN cust_pay USING ( paynum )'; + $where .= ' AND ' if $where; + $where .= "cust_bill_pay_pkg.setuprecur = 'recur' + AND cust_pay._date <= $now"; + } + else { + die "invalid mode '$mode'"; + } + my $select = "SUM($remaining * $base)"; - #[...] + if ( !$cgi->param('include_monthly') ) { + # all except freq != 0; one-time charges should never be included + $where .= " + AND part_pkg.freq != '1' + AND part_pkg.freq NOT LIKE '%h' + AND part_pkg.freq NOT LIKE '%d' + AND part_pkg.freq NOT LIKE '%w'"; + } - my $sql = "SELECT $select FROM cust_bill_pkg - LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) - LEFT JOIN cust_main USING ( custnum ) + my $sql = + "SELECT $select FROM $from + LEFT JOIN cust_pkg ON (cust_bill_pkg.pkgnum = cust_pkg.pkgnum) + LEFT JOIN part_pkg USING ( pkgpart ) + LEFT JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum) WHERE pkgpart > 0 - AND sdate < $now - AND edate > $now + AND cust_bill_pkg.sdate < $now + AND cust_bill_pkg.edate > $now AND cust_bill_pkg.recur != 0 AND part_pkg.freq != '0' - AND part_pkg.freq != '1' - AND part_pkg.freq NOT LIKE '%h' - AND part_pkg.freq NOT LIKE '%d' - AND part_pkg.freq NOT LIKE '%w' $where "; diff --git a/httemplate/search/report_prepaid_income.html b/httemplate/search/report_prepaid_income.html index 061b24c68..90b72f60b 100644 --- a/httemplate/search/report_prepaid_income.html +++ b/httemplate/search/report_prepaid_income.html @@ -2,7 +2,7 @@ <% include('/elements/init_calendar.html') %> -<FORM ACTION="report_prepaid_income.cgi" METHOD="GET"> +<FORM ACTION="prepaid_income.html" METHOD="GET"> <TABLE BGCOLOR="#cccccc" CELLSPACING=0> @@ -13,7 +13,7 @@ </TR> <TR> - <TD>As of </TD> + <TD ALIGN="right">As of </TD> <TD> <INPUT TYPE="text" NAME="date" ID="date_text" VALUE="now"> <IMG SRC="../images/calendar.png" ID="date_button" STYLE="cursor: pointer" TITLE="Select date"> @@ -30,7 +30,20 @@ </TR> <% include( '/elements/tr-select-agent.html', 'disable_empty'=>0 ) %> - + + <& /elements/tr-select-cust_main-status.html, + label => mt('Customer Status') &> + <& /elements/tr-select.html, + label => 'Invoice Status', + field => 'mode', + options => [ qw(billed paid) ] &> + <TR> + <TD ALIGN="right"> + <INPUT TYPE="checkbox" NAME="include_monthly" VALUE=1> + </TD> + <TD ALIGN="left"><% emt('Include packages with period <= 1 month') %> + </TD> + </TR> <TR> <TD COLSPAN=2> </TD> </TR> diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index 0cd652d83..bfbc5fe5a 100755 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -275,7 +275,10 @@ sub gotcust { my $table = shift; my $prefix = @_ ? shift : ''; " - ( $table.${prefix}city = cust_main_county.city + ( $table.${prefix}district = cust_main_county.district + OR cust_main_county.district = '' + OR cust_main_county.district IS NULL ) + AND ( $table.${prefix}city = cust_main_county.city OR cust_main_county.city = '' OR cust_main_county.city IS NULL ) AND ( $table.${prefix}county = cust_main_county.county @@ -332,6 +335,7 @@ if ( $conf->exists('tax-pkg_address') ) { } my $out = 'Out of taxable region(s)'; +# these are actually tax labels, not regions my %regions = (); foreach my $r ( qsearch({ 'table' => 'cust_main_county', @@ -341,6 +345,7 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', { #warn $r->county. ' '. $r->state. ' '. $r->country. "\n"; + # set up a %regions entry for this region's tax label my $label = getlabel($r); $regions{$label}->{'label'} = $label; @@ -366,6 +371,7 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', } else { + # SQL for "taxclass doesn't match any other tax in the region" my $same_sql = $r->sql_taxclass_sameregion; $mywhere .= " AND $same_sql" if $same_sql; @@ -375,42 +381,24 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', } + # FROM cust_bill_pkg JOIN (whatever is needed to determine tax location) + # WHERE (matches tax location and agentnum and taxclass) + # takes parameters in @base_param, plus taxclass if there is one my $fromwhere = "$from_join_cust_pkg $mywhere"; # AND payby != 'COMP' "; -# my $label = getlabel($r); -# $regions{$label}->{'label'} = $label; - my $nottax = 'pkgnum != 0'; - ## calculate total for this region + ## calculate total of sales (non-tax line items) for this region my $t_sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax"; my $t = scalar_sql($r, \@param, $t_sql); $regions{$label}->{'total'} += $t; - #if ( $label eq $out ) # && $t ) { - # warn "adding $t for ". - # join('/', map $r->$_, qw( taxclass county state country ) ). "\n"; - # #warn $t_sql if $r->state eq 'FL'; - #} + #$regions{$label}->{subtotals}->{$r->taxnum} = $t; #useful debug ## calculate customer-exemption for this region -## my $taxable = $t; - -# my($taxable, $x_cust) = (0, 0); -# foreach my $e ( grep { $r->get($_.'tax') !~ /^Y/i } -# qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) { -# $taxable += scalar_sql($r, \@param, -# "SELECT SUM($e) $fromwhere AND $nottax AND ( tax != 'Y' OR tax IS NULL )" -# ); -# -# $x_cust += scalar_sql($r, \@param, -# "SELECT SUM($e) $fromwhere AND $nottax AND tax = 'Y'" -# ); -# } - #false laziness -ish w/report_tax.cgi my $cust_exempt; if ( $r->taxname ) { @@ -486,10 +474,12 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', } else { $regions{$label}->{'rate'} = $r->tax.'%'; } - } +#warn Dumper(\%regions); +# $regions{$label} now contains 'total', 'exempt_cust', 'exempt_pkg', +# 'exempt_monthly', summed over each set of regions with the same label. -my $distinct = "country, state, county, city, +my $distinct = "country, state, county, city, district, CASE WHEN taxname IS NULL THEN '' ELSE taxname END AS taxname"; my $taxclass_distinct = #a little bit unsure of this part... test? @@ -528,11 +518,19 @@ $creditfromwhere .= ")"; $taxfromwhere .= " $taxwhere "; #AND payby != 'COMP' "; $creditfromwhere .= " $taxwhere AND billpkgtaxratelocationnum IS NULL"; #AND payby != 'COMP' "; -my @taxparam = @base_param; +#should i be a cust_main_county method or something +# yes. yes, you should. +# $taxfromwhere: Most of a query to find cust_bill_pkg records linked to a +# customer matching a given state/county/city/district (and within the date +# range for the report). +# @base_param: A list of the fields from cust_main_county to use as parameters. + +# $_taxamount_sub: Takes a cust_main_county and returns the sum of taxes billed +# within the report period for all customers located in that county. If +# the cust_main_county has a taxname, limits to taxes with that name; otherwise +# includes all line items with pkgnum = 0 and description either 'Tax' or empty. -#should i be a cust_main_county method or something -#need to pass in $taxfromwhere & @taxparam??? my $_taxamount_sub = sub { my $r = shift; @@ -545,9 +543,11 @@ my $_taxamount_sub = sub { my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) ". " $taxfromwhere AND cust_bill_pkg.pkgnum = 0 $named_tax"; - scalar_sql($r, \@taxparam, $sql ); + scalar_sql($r, [ @base_param ], $sql ); }; +# $_creditamount_sub: As above, but returns the sum of credits applied + my $_creditamount_sub = sub { my $r = shift; @@ -560,7 +560,7 @@ my $_creditamount_sub = sub { my $sql = "SELECT SUM(cust_credit_bill_pkg.amount) ". " $creditfromwhere AND cust_bill_pkg.pkgnum = 0 $named_tax"; - scalar_sql($r, \@taxparam, $sql ); + scalar_sql($r, [ @base_param ], $sql ); }; #tax-report_groups filtering @@ -734,7 +734,8 @@ sub getlabel { my $label; if ( $r->tax == 0 - && ! scalar( qsearch('cust_main_county', { 'city' => $r->city, + && ! scalar( qsearch('cust_main_county', { 'district'=> $r->district, + 'city' => $r->city, 'county' => $r->county, 'state' => $r->state, 'country' => $r->country, @@ -747,10 +748,6 @@ sub getlabel { #kludge to avoid "will not stay shared" warning my $out = 'Out of taxable region(s)'; $label = $out; -# } elsif ( $r->taxname && count_taxname($r->taxname) == 1 ) { -# $label = $r->taxname; -## $regions{$label}->{'taxname'} = $label; -## push @{$regions{$label}->{$_}}, $r->$_() foreach qw( county state country ); } else { $label = $r->country; $label = $r->state.", $label" if $r->state; |