diff options
Diffstat (limited to 'httemplate/search')
| -rwxr-xr-x | httemplate/search/cust_bill.html | 2 | ||||
| -rw-r--r-- | httemplate/search/cust_bill_pay.html | 50 | ||||
| -rw-r--r-- | httemplate/search/cust_bill_pkg.cgi | 92 | ||||
| -rw-r--r-- | httemplate/search/cust_main-zip.html | 11 | ||||
| -rwxr-xr-x | httemplate/search/cust_pkg.cgi | 41 | ||||
| -rwxr-xr-x | httemplate/search/elements/cust_pay_or_refund.html | 230 | ||||
| -rw-r--r-- | httemplate/search/elements/search-html.html | 5 | ||||
| -rwxr-xr-x | httemplate/search/h_cust_pay.html | 9 | ||||
| -rw-r--r-- | httemplate/search/inventory_item.html | 12 | ||||
| -rw-r--r-- | httemplate/search/mailinglistmember.html | 57 | ||||
| -rw-r--r-- | httemplate/search/part_pkg.html | 213 | ||||
| -rw-r--r-- | httemplate/search/report_cust_main-zip.html | 19 | ||||
| -rw-r--r-- | httemplate/search/report_employee_commission.html | 34 | ||||
| -rw-r--r-- | httemplate/search/report_h_cust_pay.html | 124 | ||||
| -rw-r--r-- | httemplate/search/report_prepaid_income.cgi | 288 | ||||
| -rw-r--r-- | httemplate/search/report_prepaid_income.html | 5 |
16 files changed, 957 insertions, 235 deletions
diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html index c1f5517af..8d72357dc 100755 --- a/httemplate/search/cust_bill.html +++ b/httemplate/search/cust_bill.html @@ -22,7 +22,7 @@ sub { time2str('%b %d %Y', shift->_date ) }, \&FS::UI::Web::cust_fields, ], - 'align' => 'rrrr'.FS::UI::Web::cust_aligns(), + 'align' => 'rrrrl'.FS::UI::Web::cust_aligns(), 'links' => [ $link, $link, diff --git a/httemplate/search/cust_bill_pay.html b/httemplate/search/cust_bill_pay.html index 3c390e706..4272d8669 100644 --- a/httemplate/search/cust_bill_pay.html +++ b/httemplate/search/cust_bill_pay.html @@ -5,34 +5,34 @@ 'count_query' => $count_query, 'count_addl' => [ '$%.2f total paid (net)', ], 'header' => [ 'Net applied', - 'to Invoice', + 'Invoice', + 'Invoice amount', + 'Invoice date', 'Payment', + 'Payment amount', + 'Payment date', 'By', FS::UI::Web::cust_header(), ], 'fields' => [ - sub { $money_char. sprintf('%.2f', shift->amount ) }, - sub { my $cbp = shift; - '#'.$cbp->invnum. ' '. - time2str('%b %d %Y', $cbp->cust_bill_date ). - " ($money_char". - sprintf('%.2f', $cbp->cust_bill_amount). - ")" - }, - sub { my $cbp = shift; - $cbp->cust_pay->payby_payinfo_pretty. ' '. - time2str('%b %d %Y', $cbp->_date ). - " ($money_char". - sprintf('%.2f', $cbp->cust_pay_paid ). - ")" - }, - sub { shift->cust_pay->otaker }, + sub { $money_char.sprintf('%.2f', shift->amount ) }, + 'invnum', + sub { $money_char.sprintf('%.2f', shift->cust_bill_charged)}, + sub { time2str('%b %d %Y', shift->cust_bill_date ) }, + sub { shift->cust_pay->payby_payinfo_pretty }, + sub { $money_char.sprintf('%.2f', shift->cust_pay_paid)}, + sub { time2str('%b %d %Y', shift->cust_pay_date ) }, + sub { shift->cust_pay_otaker }, \&FS::UI::Web::cust_fields, ], - 'align' => 'rrrl'.FS::UI::Web::cust_aligns(), + 'align' => 'rrrrlrrl'.FS::UI::Web::cust_aligns(), 'links' => [ '', $cust_bill_link, + $cust_bill_link, + $cust_bill_link, + $cust_pay_link, + $cust_pay_link, $cust_pay_link, '', ( map { $_ ne 'Cust. Status' ? $cust_link : '' } @@ -44,6 +44,10 @@ '', '', '', + '', + '', + '', + '', FS::UI::Web::cust_colors(), ], 'style' => [ @@ -51,6 +55,10 @@ '', '', '', + '', + '', + '', + '', FS::UI::Web::cust_styles(), ], ) @@ -93,9 +101,11 @@ my $sql_query = { 'table' => 'cust_bill_pay', 'select' => join(', ', 'cust_bill_pay.*', - 'cust_pay.paid AS cust_pay_paid', 'cust_bill._date AS cust_bill_date', - #'cust_bill.charged AS cust_bill_charged', + 'cust_bill.charged AS cust_bill_charged', + 'cust_pay.paid AS cust_pay_paid', + 'cust_pay._date AS cust_pay_date', + 'cust_pay.otaker AS cust_pay_otaker', 'cust_pay.custnum AS custnum', 'cust_main.custnum AS cust_main_custnum', FS::UI::Web::cust_sql_fields(), diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 9b0201c29..2e79cd75d 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -3,15 +3,24 @@ 'name' => 'line items', 'query' => $query, 'count_query' => $count_query, - 'count_addl' => [ $money_char. '%.2f total', ], + 'count_addl' => [ $money_char. '%.2f total', + $unearned ? ( $money_char. '%.2f unearned revenue' ) : (), + ], 'header' => [ '#', 'Description', - 'Setup charge', + ( $unearned + ? 'Unearned' + : 'Setup charge' + ), ( $use_usage eq 'usage' ? 'Usage charge' : 'Recurring charge' ), + ( $unearned + ? ( 'Charge start', 'Charge end' ) + : () + ), 'Invoice', 'Date', FS::UI::Web::cust_header(), @@ -24,7 +33,22 @@ }, #strikethrough or "N/A ($amount)" or something these when # they're not applicable to pkg_tax search - sub { sprintf($money_char.'%.2f', shift->setup ) }, + sub { my $cust_bill_pkg = shift; + if ( $unearned ) { + my $period = + $cust_bill_pkg->edate - $cust_bill_pkg->sdate; + my $elapsed = $unearned - $cust_bill_pkg->sdate; + $elapsed = 0 if $elapsed < 0; + + my $remaining = 1 - $elapsed/$period; + + sprintf($money_char. '%.2f', + $remaining * $cust_bill_pkg->recur ); + + } else { + sprintf($money_char.'%.2f', $cust_bill_pkg->setup ); + } + }, sub { my $row = shift; my $value = 0; if ( $use_usage eq 'recurring' ) { @@ -36,6 +60,12 @@ } sprintf($money_char.'%.2f', $value ); }, + ( $unearned + ? ( sub { time2str('%b %d %Y', shift->sdate ) }, + sub { time2str('%b %d %Y', shift->edate ) }, + ) + : () + ), 'invnum', sub { time2str('%b %d %Y', shift->_date ) }, \&FS::UI::Web::cust_fields, @@ -45,6 +75,7 @@ '', '', '', + ( $unearned ? ( '', '' ) : () ), $ilink, $ilink, ( map { $_ ne 'Cust. Status' ? $clink : '' } @@ -52,12 +83,16 @@ ), ], #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(), - 'align' => 'lrrrc'.FS::UI::Web::cust_aligns(), + 'align' => 'lrr'. + ( $unearned ? 'cc' : '' ). + 'rc'. + FS::UI::Web::cust_aligns(), 'color' => [ #'', '', '', '', + ( $unearned ? ( '', '' ) : () ), '', '', FS::UI::Web::cust_colors(), @@ -67,6 +102,7 @@ '', '', '', + ( $unearned ? ( '', '' ) : () ), '', '', FS::UI::Web::cust_styles(), @@ -80,6 +116,8 @@ die "access denied" my $conf = new FS::Conf; +my $unearned = ''; + #here is the agent virtualization my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' ); @@ -282,6 +320,19 @@ if ( $cgi->param('out') ) { keys %ph ); +} elsif ( $cgi->param('unearned_now') =~ /^(\d+)$/ ) { + + $unearned = $1; + + push @where, "cust_bill_pkg.sdate < $unearned", + "cust_bill_pkg.edate > $unearned", + "cust_bill_pkg.recur != 0", + "part_pkg.freq != '0'", + "part_pkg.freq != '1'", + "part_pkg.freq NOT LIKE '%h'", + "part_pkg.freq NOT LIKE '%d'", + "part_pkg.freq NOT LIKE '%w'"; + } if ( $cgi->param('itemdesc') ) { @@ -399,10 +450,31 @@ if ( $cgi->param('pkg_tax') ) { $count_query .= "SUM(setup + recur - usage)"; } elsif ( $use_usage eq 'usage' ) { $count_query .= "SUM(usage)"; + } elsif ( $unearned ) { + $count_query .= "SUM(cust_bill_pkg.recur)"; } else { $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)"; } + if ( $unearned ) { + + #false laziness w/report_prepaid_income.cgi + + my $float = 'REAL'; #'DOUBLE PRECISION'; + + my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS $float)"; + my $elapsed = "(CASE WHEN cust_bill_pkg.sdate > $unearned + THEN 0 + ELSE ($unearned - cust_bill_pkg.sdate) + END)"; + #my $elapsed = "CAST($unearned - cust_bill_pkg.sdate AS $float)"; + + my $remaining = "(1 - $elapsed/$period)"; + + $count_query .= ", SUM($remaining * cust_bill_pkg.recur)"; + + } + } my $where = ' WHERE '. join(' AND ', @where); @@ -458,11 +530,13 @@ if ($use_usage) { } warn "count_query is $count_query\n"; -my @select = ( - 'cust_bill_pkg.*', - 'cust_bill._date', - ); -push @select, 'part_pkg.pkg' unless $cgi->param('istax'); +my @select = ( 'cust_bill_pkg.*', + 'cust_bill._date', ); + +push @select, 'part_pkg.pkg', + 'part_pkg.freq', + unless $cgi->param('istax'); + push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields(); diff --git a/httemplate/search/cust_main-zip.html b/httemplate/search/cust_main-zip.html index 56df924bc..e87b21474 100644 --- a/httemplate/search/cust_main-zip.html +++ b/httemplate/search/cust_main-zip.html @@ -32,6 +32,17 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { $agentnum = $1; push @where, "cust_main.agentnum = $agentnum"; } + +# select svcdb + +if ( $cgi->param('svcdb') =~ /^(\w+)$/ ) { + my $svcdb = $1; + push @where, "EXISTS( SELECT 1 FROM $svcdb LEFT JOIN cust_svc USING ( svcnum ) + LEFT JOIN cust_pkg USING ( pkgnum ) + WHERE cust_pkg.custnum = cust_main.custnum + )"; +} + my $where = scalar(@where) ? 'WHERE '. join(' AND ', @where) : ''; # bill zip vs ship zip diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index ee4c82d8e..83cd206cb 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -143,7 +143,6 @@ ), '', ], - 'extra_choices_callback'=> $extra_choices, ) %> <%init> @@ -253,22 +252,32 @@ sub time_or_blank { }; } -my $html_init = include('/elements/init_overlib.html'); - -my $extra_choices = sub { +my $html_init = sub { my $query = shift; - - return '' unless - $FS::CurrentUser::CurrentUser->access_right('Bulk change customer packages'); - - '<BR><BR>'. - include( '/elements/popup_link.html', - 'label' => 'Change these packages', - 'action' => "${p}misc/bulk_change_pkg.cgi?$query", - 'actionlabel' => 'Change Packages', - 'width' => 763, - 'height' => 336, - ); + my $text = ''; + my $curuser = $FS::CurrentUser::CurrentUser; + + if ( $curuser->access_right('Bulk change customer packages') ) { + $text .= include('/elements/init_overlib.html'). + include( '/elements/popup_link.html', + 'label' => 'Change these packages', + 'action' => "${p}misc/bulk_change_pkg.cgi?$query", + 'actionlabel' => 'Change Packages', + 'width' => 569, + 'height' => 210, + ). '<BR>'; + + if ( $curuser->access_right('Edit customer package dates') ) { + $text .= include( '/elements/popup_link.html', + 'label' => 'Increment next bill date', + 'action' => "${p}misc/bulk_pkg_increment_bill.cgi?$query", + 'actionlabel' => 'Increment Bill Date', + 'width' => 569, + 'height' => 210, + ). '<BR>'; + } + } + return $text; }; </%init> diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html index b1296d1b0..4f83d0ab6 100755 --- a/httemplate/search/elements/cust_pay_or_refund.html +++ b/httemplate/search/elements/cust_pay_or_refund.html @@ -29,6 +29,15 @@ Examples: 'redirect_empty' => $redirect_empty, ) + include( 'elements/cust_pay_or_refund.html', + 'table' => 'h_cust_pay', + 'amount_field' => 'paid', + 'name_singular' => 'payment', + 'name_verb' => 'paid', + 'pre_header' => [ 'Transaction', 'By' ], + 'pre_fields' => [ 'history_action', 'history_user' ], + ) + </%doc> <% include( 'search.html', 'title' => $title, @@ -37,46 +46,12 @@ Examples: 'count_query' => $count_query, 'count_addl' => [ '$%.2f total '.$opt{name_verb}, ], 'redirect_empty' => $opt{'redirect_empty'}, - 'header' => [ "\u$name_singular", - 'Amount', - 'Date', - @header, - FS::UI::Web::cust_header(), - ], - 'fields' => [ - 'payby_payinfo_pretty', - sub { sprintf('$%.2f', shift->$amount_field() ) }, - sub { time2str('%b %d %Y', shift->_date ) }, - @fields, - \&FS::UI::Web::cust_fields, - ], - #'align' => 'lrrrll', - 'align' => 'rrr'. - join('', map 'c', @fields ). - FS::UI::Web::cust_aligns(), - 'links' => [ - $link, - $link, - $link, - ( map '', @fields ), - ( map { $_ ne 'Cust. Status' ? $cust_link : '' } - FS::UI::Web::cust_header() - ), - ], - 'color' => [ - '', - '', - '', - ( map '', @fields ), - FS::UI::Web::cust_colors(), - ], - 'style' => [ - '', - '', - '', - ( map '', @fields ), - FS::UI::Web::cust_styles(), - ], + 'header' => \@header, + 'fields' => \@fields, + 'align' => $align, + 'links' => \@links, + 'color' => \@color, + 'style' => \@style, ) %> <%init> @@ -88,16 +63,69 @@ my $curuser = $FS::CurrentUser::CurrentUser; die "access denied" unless $curuser->access_right('Financial reports'); -my $thing = $opt{'thing'}; +my $table = $opt{'table'} || 'cust_'.$opt{'thing'}; + my $amount_field = $opt{'amount_field'}; my $name_singular = $opt{'name_singular'}; my $title = "\u$name_singular Search Results"; +my $link = ''; +if ( ( $curuser->access_right('View invoices') #XXX for now + || $curuser->access_right('View customer payments') + ) + && ! $opt{'disable_link'} + ) +{ + + my $key; + my $q = ''; + if ( $table eq 'cust_pay_void' ) { + $key = 'paynum'; + $q .= 'void=1;'; + } elsif ( $table eq /^cust_(\w+)$/ ) { + $key = $1.'num'; + } + + if ( $key ) { + $q .= "$key="; + $link = [ "${p}view/$table.html?$q", $key ] + } +} + +my $cust_link = sub { + my $cust_thing = shift; + $cust_thing->cust_main_custnum + ? [ "${p}view/cust_main.cgi?", 'custnum' ] + : ''; +}; + my @header = (); my @fields = (); +my $align = ''; +my @links = (); +if ( $opt{'pre_header'} ) { + push @header, @{ $opt{'pre_header'} }; + $align .= 'c' x scalar(@{ $opt{'pre_header'} }); + push @links, map '', @{ $opt{'pre_header'} }; + push @fields, @{ $opt{'pre_fields'} }; +} + +push @header, "\u$name_singular", + 'Amount', + 'Date', +; +$align .= 'rrr'; +push @links, '', '', ''; +push @fields, 'payby_payinfo_pretty', + sub { sprintf('$%.2f', shift->$amount_field() ) }, + sub { time2str('%b %d %Y', shift->_date ) }, +; + unless ( $opt{'disable_by'} ) { push @header, 'By'; + $align .= 'c'; + push @links, ''; push @fields, sub { my $o = shift->otaker; $o = 'auto billing' if $o eq 'fs_daily'; $o = 'customer self-service' if $o eq 'fs_selfservice'; @@ -105,6 +133,14 @@ unless ( $opt{'disable_by'} ) { }; } +push @header, FS::UI::Web::cust_header(); +$align .= FS::UI::Web::cust_aligns(); +push @links, map { $_ ne 'Cust. Status' ? $cust_link : '' } + FS::UI::Web::cust_header(); +my @color = ( ( map '', @fields ), FS::UI::Web::cust_colors() ); +my @style = ( ( map '', @fields ), FS::UI::Web::cust_styles() ); +push @fields, \&FS::UI::Web::cust_fields; + push @header, @{ $opt{'addl_header'} } if $opt{'addl_header'}; push @fields, @{ $opt{'addl_fields'} } @@ -132,7 +168,7 @@ if ( $cgi->param('magic') ) { $cgi->param('payby') =~ /^(CARD|CHEK|BILL|PREP|CASH|WEST|MCRD)(-(VisaMC|Amex|Discover|Maestro))?$/ or die "illegal payby ". $cgi->param('payby'); - push @search, "cust_$thing.payby = '$1'"; + push @search, "$table.payby = '$1'"; if ( $3 ) { my $cardtype = $3; @@ -141,53 +177,53 @@ if ( $cgi->param('magic') ) { if ( $cardtype eq 'VisaMC' ) { #avoid posix regexes for portability $search = - " ( ( substring(cust_$thing.payinfo from 1 for 1) = '4' ". - " AND substring(cust_$thing.payinfo from 1 for 4) != '4936' ". - " AND substring(cust_$thing.payinfo from 1 for 6) ". + " ( ( substring($table.payinfo from 1 for 1) = '4' ". + " AND substring($table.payinfo from 1 for 4) != '4936' ". + " AND substring($table.payinfo from 1 for 6) ". " NOT SIMILAR TO '49030[2-9]' ". - " AND substring(cust_$thing.payinfo from 1 for 6) ". + " AND substring($table.payinfo from 1 for 6) ". " NOT SIMILAR TO '49033[5-9]' ". - " AND substring(cust_$thing.payinfo from 1 for 6) ". + " AND substring($table.payinfo from 1 for 6) ". " NOT SIMILAR TO '49110[1-2]' ". - " AND substring(cust_$thing.payinfo from 1 for 6) ". + " AND substring($table.payinfo from 1 for 6) ". " NOT SIMILAR TO '49117[4-9]' ". - " AND substring(cust_$thing.payinfo from 1 for 6) ". + " AND substring($table.payinfo from 1 for 6) ". " NOT SIMILAR TO '49118[1-2]' ". " )". - " OR substring(cust_$thing.payinfo from 1 for 2) = '51' ". - " OR substring(cust_$thing.payinfo from 1 for 2) = '52' ". - " OR substring(cust_$thing.payinfo from 1 for 2) = '53' ". - " OR substring(cust_$thing.payinfo from 1 for 2) = '54' ". - " OR substring(cust_$thing.payinfo from 1 for 2) = '54' ". - " OR substring(cust_$thing.payinfo from 1 for 2) = '55' ". - " OR substring(cust_$thing.payinfo from 1 for 2) = '36' ". #Diner's int'l processed as Visa/MC inside US + " OR substring($table.payinfo from 1 for 2) = '51' ". + " OR substring($table.payinfo from 1 for 2) = '52' ". + " OR substring($table.payinfo from 1 for 2) = '53' ". + " OR substring($table.payinfo from 1 for 2) = '54' ". + " OR substring($table.payinfo from 1 for 2) = '54' ". + " OR substring($table.payinfo from 1 for 2) = '55' ". + " OR substring($table.payinfo from 1 for 2) = '36' ". #Diner's int'l processed as Visa/MC inside US " ) "; } elsif ( $cardtype eq 'Amex' ) { $search = - " ( substring(cust_$thing.payinfo from 1 for 2 ) = '34' ". - " OR substring(cust_$thing.payinfo from 1 for 2 ) = '37' ". + " ( substring($table.payinfo from 1 for 2 ) = '34' ". + " OR substring($table.payinfo from 1 for 2 ) = '37' ". " ) "; } elsif ( $cardtype eq 'Discover' ) { $search = - " ( substring(cust_$thing.payinfo from 1 for 4 ) = '6011' ". - " OR substring(cust_$thing.payinfo from 1 for 2 ) = '65' ". - " OR substring(cust_$thing.payinfo from 1 for 3 ) = '622' ". #China Union Pay processed as Discover outside CN + " ( substring($table.payinfo from 1 for 4 ) = '6011' ". + " OR substring($table.payinfo from 1 for 2 ) = '65' ". + " OR substring($table.payinfo from 1 for 3 ) = '622' ". #China Union Pay processed as Discover outside CN " ) "; } elsif ( $cardtype eq 'Maestro' ) { $search = - " ( substring(cust_$thing.payinfo from 1 for 2 ) = '63' ". - " OR substring(cust_$thing.payinfo from 1 for 2 ) = '67' ". - " OR substring(cust_$thing.payinfo from 1 for 6 ) = '564182' ". - " OR substring(cust_$thing.payinfo from 1 for 4 ) = '4936' ". - " OR substring(cust_$thing.payinfo from 1 for 6 ) ". + " ( substring($table.payinfo from 1 for 2 ) = '63' ". + " OR substring($table.payinfo from 1 for 2 ) = '67' ". + " OR substring($table.payinfo from 1 for 6 ) = '564182' ". + " OR substring($table.payinfo from 1 for 4 ) = '4936' ". + " OR substring($table.payinfo from 1 for 6 ) ". " SIMILAR TO '49030[2-9]' ". - " OR substring(cust_$thing.payinfo from 1 for 6 ) ". + " OR substring($table.payinfo from 1 for 6 ) ". " SIMILAR TO '49033[5-9]' ". - " OR substring(cust_$thing.payinfo from 1 for 6 ) ". + " OR substring($table.payinfo from 1 for 6 ) ". " SIMILAR TO '49110[1-2]' ". - " OR substring(cust_$thing.payinfo from 1 for 6 ) ". + " OR substring($table.payinfo from 1 for 6 ) ". " SIMILAR TO '49117[4-9]' ". - " OR substring(cust_$thing.payinfo from 1 for 6 ) ". + " OR substring($table.payinfo from 1 for 6 ) ". " SIMILAR TO '49118[1-2]' ". " ) "; } else { @@ -195,10 +231,10 @@ if ( $cgi->param('magic') ) { } my $masksearch = $search; - $masksearch =~ s/cust_$thing\.payinfo/cust_$thing.paymask/gi; + $masksearch =~ s/$table\.payinfo/$table.paymask/gi; push @search, - "( $search OR ( cust_$thing.paymask IS NOT NULL AND $masksearch ) )"; + "( $search OR ( $table.paymask IS NOT NULL AND $masksearch ) )"; } } @@ -206,11 +242,11 @@ if ( $cgi->param('magic') ) { if ( $cgi->param('payinfo') ) { $cgi->param('payinfo') =~ /^\s*(\d+)\s*$/ or die "illegal payinfo ". $cgi->param('payinfo'); - push @search, "cust_$thing.payinfo = '$1'"; + push @search, "$table.payinfo = '$1'"; } if ( $cgi->param('otaker') =~ /^(\w+)$/ ) { - push @search, "cust_$thing.otaker = '$1'"; + push @search, "$table.otaker = '$1'"; } #for cust_pay_pending... statusNOT=done @@ -222,7 +258,7 @@ if ( $cgi->param('magic') ) { push @search, "_date >= $beginning ", "_date <= $ending"; - if ( $thing eq 'pay_void' ) { + if ( $table eq 'cust_pay_void' ) { my($v_beginning, $v_ending) = FS::UI::Web::parse_beginning_ending($cgi, 'void'); push @search, "void_date >= $v_beginning ", @@ -246,19 +282,34 @@ if ( $cgi->param('magic') ) { die "unknown search magic: ". $cgi->param('magic'); } + #for the history search + if ( $cgi->param('history_action') =~ /^([\w,]+)$/ ) { + my @history_action = split(/,/, $1); + push @search, 'history_action IN ('. + join(',', map "'$_'", @history_action ). ')'; + } + + if ( $cgi->param('history_date_beginning') + || $cgi->param('history_date_ending') ) { + my($h_beginning, $h_ending) = + FS::UI::Web::parse_beginning_ending($cgi, 'history_date'); + push @search, "history_date >= $h_beginning ", + "history_date <= $h_ending"; + } + #here is the agent virtualization push @search, $curuser->agentnums_sql; my $search = ' WHERE '. join(' AND ', @search); $count_query = "SELECT COUNT(*), SUM($amount_field) ". - "FROM cust_$thing LEFT JOIN cust_main USING ( custnum )". + "FROM $table LEFT JOIN cust_main USING ( custnum )". $search; $sql_query = { - 'table' => "cust_$thing", + 'table' => $table, 'select' => join(', ', - "cust_$thing.*", + "$table.*", 'cust_main.custnum as cust_main_custnum', FS::UI::Web::cust_sql_fields(), ), @@ -277,12 +328,12 @@ if ( $cgi->param('magic') ) { $cgi->param('payby') =~ /^(\w+)$/ or die "illegal payby"; my $payby = $1; - $count_query = "SELECT COUNT(*), SUM($amount_field) FROM cust_$thing". + $count_query = "SELECT COUNT(*), SUM($amount_field) FROM $table". " WHERE payinfo = '$payinfo' AND payby = '$payby'". " AND ". $curuser->agentnums_sql; $sql_query = { - 'table' => "cust_$thing", + 'table' => $table, 'hashref' => { 'payinfo' => $payinfo, 'payby' => $payby }, 'extra_sql' => $curuser->agentnums_sql. @@ -291,23 +342,4 @@ if ( $cgi->param('magic') ) { } -my $link = ''; -if ( ( $curuser->access_right('View invoices') #XXX for now - || $curuser->access_right('View customer payments') - ) - && ! $opt{'disable_link'} - ) -{ - my $key = $thing eq 'pay_void' ? 'paynum' : $thing.'num'; - my $q = ( $thing eq 'pay_void' ? 'void=1;' : '' ). "$key="; - $link = [ "${p}view/cust_$thing.html?$q", $key ] -} - -my $cust_link = sub { - my $cust_thing = shift; - $cust_thing->cust_main_custnum - ? [ "${p}view/cust_main.cgi?", 'custnum' ] - : ''; -}; - </%init> diff --git a/httemplate/search/elements/search-html.html b/httemplate/search/elements/search-html.html index c0bb721f7..6b915a617 100644 --- a/httemplate/search/elements/search-html.html +++ b/httemplate/search/elements/search-html.html @@ -144,11 +144,6 @@ % $cgi->param('_type', 'html-print'); as <A HREF="<% $cgi->self_url %>">printable copy</A> - <% $opt{'extra_choices_callback'} - ? &{$opt{'extra_choices_callback'}}($cgi->query_string) - : '' - %> - </TD> % $cgi->param('_type', "html" ); % } diff --git a/httemplate/search/h_cust_pay.html b/httemplate/search/h_cust_pay.html new file mode 100755 index 000000000..99330fadd --- /dev/null +++ b/httemplate/search/h_cust_pay.html @@ -0,0 +1,9 @@ +<% include( 'elements/cust_pay_or_refund.html', + 'table' => 'h_cust_pay', + 'amount_field' => 'paid', + 'name_singular' => 'payment', + 'name_verb' => 'paid', + 'pre_header' => [ 'Transaction', 'By' ], + 'pre_fields' => [ 'history_action', 'history_user' ], + ) +%> diff --git a/httemplate/search/inventory_item.html b/httemplate/search/inventory_item.html index cd37e267b..ba449ecd7 100644 --- a/httemplate/search/inventory_item.html +++ b/httemplate/search/inventory_item.html @@ -9,6 +9,7 @@ 'hashref' => { 'classnum' => $classnum }, 'select' => join(', ', 'inventory_item.*', + 'part_svc.svcdb', 'cust_main.custnum', FS::UI::Web::cust_sql_fields(), ), @@ -103,7 +104,16 @@ my $count_query = my $link = sub { my $inventory_item = shift; if ( $inventory_item->svcnum ) { - [ "${p}view/svc_acct.cgi?", 'svcnum' ]; + + #[ "${p}view/svc_acct.cgi?", 'svcnum' ]; + my $url = svc_url( + 'm' => $m, + 'action' => 'view', + #'svcdb' => $inventory_item->cust_svc->part_svc->svcdb, + 'svcdb' => $inventory_item->svcdb, #we have it from the joined search + 'query' => '', + ); + [ $url, 'svcnum' ]; } else { ''; } diff --git a/httemplate/search/mailinglistmember.html b/httemplate/search/mailinglistmember.html new file mode 100644 index 000000000..ee395f416 --- /dev/null +++ b/httemplate/search/mailinglistmember.html @@ -0,0 +1,57 @@ +<% include('elements/search.html', + 'title' => $title, + 'name_singular' => 'member', + 'query' => $query, + 'count_query' => $count_query, + 'header' => [ 'Email address' ], + 'fields' => [ $email_sub, ], #just this one for now + 'html_init' => $html_init, + ) +%> +<%init> + +#XXX ACL: +#make sure the mailing list is attached to a customer service i can see/view + +$cgi->param('listnum') =~ /^(\d+)$/ or die 'illegal listnum'; +my $listnum = $1; + +my $mailinglist = qsearchs('mailinglist', { 'listnum' => $listnum }) + or die "unknown listnum $listnum"; +my $title = $mailinglist->listname. ' mailing list'; + +my $svc_mailinglist = $mailinglist->svc_mailinglist; + +my $query = { + 'table' => 'mailinglistmember', + 'hashref' => { 'listnum' => $listnum }, +}; + +my $count_query = "SELECT COUNT(*) FROM mailinglistmember WHERE listnum = $listnum"; + +my $email_sub = sub { + my $member = shift; + my $r = $member->email; #just this one for now + my $a = qq[<A HREF="javascript:areyousure('$r', ]. $member->membernum. ')">'; + $r .= " (${a}remove</A>)"; + $r; +}; + +my $html_init = ''; +if ( $svc_mailinglist ) { + my $svcnum = $svc_mailinglist->svcnum; + my $label = encode_entities($svc_mailinglist->label); + $html_init .= qq[<A HREF="${p}/view/svc_mailinglist.cgi?$svcnum">View customer mailing list: $label</A><BR><BR>]; +} + +$html_init .= <<"END"; +<SCRIPT TYPE="text/javascript"> + function areyousure(email,membernum) { + if ( confirm('Are you sure you want to remove ' + email + ' from this mailing list?') ) + window.location.href="${p}misc/delete-mailinglistmember.html?" + membernum; + + } +</SCRIPT> +END + +</%init> diff --git a/httemplate/search/part_pkg.html b/httemplate/search/part_pkg.html new file mode 100644 index 000000000..87237c7cf --- /dev/null +++ b/httemplate/search/part_pkg.html @@ -0,0 +1,213 @@ +<% include( 'elements/search.html', + 'title' => $title, + 'name' => $name, + 'header' => \@header, + 'query' => { 'select' => $select, + 'table' => 'part_pkg', + 'addl_from' => $addl_from, + 'hashref' => {}, + 'extra_sql' => $extra_sql, + 'order_by' => "ORDER BY $order_by", + }, + 'count_query' => $count_query, + 'fields' => \@fields, + 'links' => \@links, + 'align' => $align, + ) +%> +<%init> + +#this is about reports about packages definitions (starting w/commission ones) +# while browse/part_pkg.cgi is config->package definitions + +my $curuser = $FS::CurrentUser::CurrentUser; +die "access denied" + unless $curuser->access_right('Financial reports'); + +my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; + +my $title = 'Package definition report'; +my $name = 'package definition'; + +my $select = ''; +my $addl_from = ''; +my @where = (); +my @order_by = (); +my @header = (); +my @fields = (); +my @links = (); +my $align = ''; + +if (1) { #commission reports + + if (1) { #employee commission reports + + $select = 'DISTINCT usernum, username, part_pkg.*'; + + $addl_from .= ' CROSS JOIN access_user '; + + if ( $cgi->param('otaker') =~ /^(\w+)$/ ) { + + #XXX in this context, agent virt for employees, not package defs + my $access_user = qsearchs('access_user', { 'username' => $1 }) + or die "unknown usernum"; + + $title = $access_user->name; + + } else { + + push @header, 'Employee'; + push @fields, sub { shift->get('username'); }; #access_user->name + push @links, ''; #link to employee edit w/ACL? + $align .= 'c'; + + push @order_by, 'otaker'; + + $title = 'Employee'; + + } + + } elsif (0) { #agent commission reports + + if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + + #agent virt + my $agent = qsearchs('agent', { 'agentnum' => $1 }) + or die "unknown agentnum"; + + $title = $agent->agent; + + push @header, 'Agent'; + push @fields, sub { 'XXXagent' }; + push @links, ''; #link to agent edit w/ACL? + $align .= 'c'; + + push @order_by, 'agentnum'; #join to agent? we're mostly interested in grouping rather than order + + } else { + $title = 'Agent'; + } + + } + + $title .= ' commission report'; + $name = "commissionable $name"; + + +} + +push @header, 'Package definition'; +push @fields, 'pkg_comment'; +push @links, ''; #link to pkg definition edit w/ACL? +$align .= 'l'; + +if (1) { #commission reports + + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); + + my $match = ''; + if (1) { #employee commission reports + $match = 'cust_pkg.otaker = access_user.username'; + } elsif (0) { #agent commission reports + $match = 'cust_main.agentnum = agent.agentnum'; + } + + my $from_cust_bill_pkg_where = "FROM cust_bill_pkg + LEFT JOIN ( cust_bill ) USING ( invnum ) + LEFT JOIN ( cust_pkg ) USING ( pkgnum ) + WHERE cust_bill_pkg.pkgnum > 0 + AND cust_bill._date >= $beginning + AND cust_bill._date <= $ending "; + my $and = " AND $match + AND cust_pkg.pkgpart = part_pkg.pkgpart"; + + push @where, "EXISTS( SELECT 1 $from_cust_bill_pkg_where $and )"; + + push @header, '#'; # of sales'; + push @links, ''; #link to detail report + $align .= 'r'; + push @fields, 'num_cust_pkg'; + $select .= ", ( SELECT COUNT(DISTINCT pkgnum) + $from_cust_bill_pkg_where $and ) + AS num_cust_pkg"; +# push @fields, sub { +# my $part_pkg = shift; +# my $sql = +# #"SELECT COUNT( SELECT DISTINCT pkgnum $from_cust_bill_pkg_where )"; +# "SELECT COUNT(DISTINCT pkgnum) $from_cust_bill_pkg_where"; +# my $sth = dbh->prepare($sql) or die dbh->errstr; +# $sth->execute or die $sth->errstr; +# $sth->fetchrow_arrayref->[0]; +# }; + + push @header, 'Sales'; + push @links, ''; #link to detail report + $align .= 'r'; +# push @fields, sub { $money_char. sprintf('%.2f', shift->get('pkg_sales')); }; +# $select .= +# ", SUM( SELECT setup+recur $from_cust_bill_pkg_where ) AS pkg_sales"; + push @fields, sub { + my $part_pkg = shift; + my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $from_cust_bill_pkg_where AND pkgpart = ? AND "; + my @arg = ($part_pkg->pkgpart); + if (1) { #employee commission reports + $sql .= 'otaker = ?'; + push @arg, $part_pkg->get('username'); + } elsif (0) { #agent commission reports + $match = 'cust_main.agentnum = agent.agentnum'; + } + my $sth = dbh->prepare($sql) or die dbh->errstr; + $sth->execute(@arg) or die $sth->errstr; + $money_char. sprintf('%.2f', $sth->fetchrow_arrayref->[0] ); + }; + + push @header, 'Commission'; + push @links, ''; #link to detail report + $align .= 'r'; + #push @fields, sub { $money_char. sprintf('%.2f', shift->get('pkg_commission')); }; + push @fields, sub { + my $part_pkg = shift; + my $sql = "SELECT SUM(amount) FROM cust_credit + LEFT JOIN cust_event USING ( eventnum ) + LEFT JOIN part_event USING ( eventpart ) + LEFT JOIN cust_pkg ON ( cust_event.tablenum = cust_pkg.pkgnum ) + WHERE eventnum IS NOT NULL + AND action IN ( 'pkg_employee_credit', + 'pkg_employee_credit_pkg' + ) + AND cust_credit._date >= $beginning + AND cust_credit._date <= $ending + AND pkgpart = ? + AND cust_credit.custnum = ? + "; + my @arg = ($part_pkg->pkgpart); + if (1) { #employee commission reports + + #XXX in this context, agent virt for employees, not package defs + my $access_user = qsearchs('access_user', { 'username' => $part_pkg->get('username') }) + or die "unknown usernum"; + + return 0 unless $access_user->user_custnum; + push @arg, $access_user->user_custnum; + + } elsif (0) { #agent commission reports + push @arg, 'XXXagent_custnum'; #$agent->agent_custnum + } + my $sth = dbh->prepare($sql) or die dbh->errstr; + $sth->execute(@arg) or die $sth->errstr; + $money_char. sprintf('%.2f', $sth->fetchrow_arrayref->[0] ); + + }; + +} + +push @order_by, 'pkgpart'; #pkg? + +$select ||= 'part_pkg.*'; +my $extra_sql = scalar(@where) ? 'WHERE ' . join(' AND ', @where) : ''; +my $order_by = join(', ', @order_by); + +my $count_query = "SELECT COUNT(*) FROM part_pkg $addl_from $extra_sql"; + +</%init> diff --git a/httemplate/search/report_cust_main-zip.html b/httemplate/search/report_cust_main-zip.html index aa802f302..00cb9ed2c 100644 --- a/httemplate/search/report_cust_main-zip.html +++ b/httemplate/search/report_cust_main-zip.html @@ -20,7 +20,7 @@ </TR> <TR> - <TD ALIGN="right">Show customers with status:</TD> + <TD ALIGN="right">Show customers with status</TD> <TD> <SELECT NAME="status"> <OPTION VALUE="">all @@ -33,6 +33,23 @@ </TD> </TR> + <TR> + <TD ALIGN="right">Limit to customers with provisioned service</TD> + <TD> + <SELECT NAME="svcdb"> + <OPTION VALUE="">(no) + <OPTION VALUE="svc_acct">Account (svc_acct) + <OPTION VALUE="svc_broadband">Broadband service (svc_broadband) + <OPTION VALUE="svc_domain">Domain (svc_domain) + <OPTION VALUE="svc_external">External service (svc_external) + <OPTION VALUE="svc_forward">Mail forward (svc_foward) + <OPTION VALUE="svc_pbx">PBX (svc_pbx) + <OPTION VALUE="svc_phone">Phone number (svc_phone) + <OPTION VALUE="svc_www">Hosting (svc_www) + </SELECT> + </TD> + </TR> + <% include( '/elements/tr-select-agent.html', 'curr_value' => scalar( $cgi->param('agentnum') ), 'label' => 'For agent: ', diff --git a/httemplate/search/report_employee_commission.html b/httemplate/search/report_employee_commission.html new file mode 100644 index 000000000..a79630a76 --- /dev/null +++ b/httemplate/search/report_employee_commission.html @@ -0,0 +1,34 @@ +<% include('/elements/header.html', 'Employee commission report' ) %> + +<FORM ACTION="part_pkg.html"> + +<TABLE BGCOLOR="#cccccc" CELLSPACING=0> + +%# +%# <% include( '/elements/tr-select-agent.html', +%# 'curr_value' => scalar( $cgi->param('agentnum') ), +%# 'disable_empty' => 0, +%# ) +%# %> +%# + +%#2.1 +<% include( '/elements/tr-select-user.html' ) %> + +%#1.9 +%# <%include( '/elements/tr-select-otaker.html' ) %> + +<% include( '/elements/tr-input-beginning_ending.html', ) %> + +</TABLE> + +<BR> +<INPUT TYPE="submit" VALUE="Get Report"> + +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +</%init> diff --git a/httemplate/search/report_h_cust_pay.html b/httemplate/search/report_h_cust_pay.html new file mode 100644 index 000000000..4e47b3831 --- /dev/null +++ b/httemplate/search/report_h_cust_pay.html @@ -0,0 +1,124 @@ +<% include('/elements/header.html', 'Payment transaction history' ) %> + +<FORM ACTION="h_cust_pay.html" METHOD="GET"> +<INPUT TYPE="hidden" NAME="magic" VALUE="_date"> + +<TABLE BGCOLOR="#cccccc" CELLSPACING=0> + + <TR> + <TH BGCOLOR="#e8e8e8" COLSPAN=2 ALIGN="left"> + <FONT SIZE="+1">Search options</FONT> + </TH> + </TR> + +%#history stuff + <TR> + <TD ALIGN="right">Search transactions for: </TD> + <TD> + <SELECT NAME="history_action"> + <OPTION VALUE="insert,replace_old,replace_new,delete">(all changes) + <OPTION VALUE="delete">Insertions + <OPTION VALUE="replace_old,replace_new">Replacements + <OPTION VALUE="delete">Deletions + </SELECT> + </TD> + </TR> + + <TR> + <TD ALIGN="right" VALIGN="center">Transaction date: </TD> + <TD> + <TABLE> + <% include( '/elements/tr-input-beginning_ending.html', + prefix => 'history_date', + layout => 'horiz', + ) + %> + </TABLE> + </TD> + </TR> +%#eo history stuff + + <TR> + <TD ALIGN="right">Payments of type: </TD> + <TD> + <SELECT NAME="payby" onChange="payby_changed(this)"> + <OPTION VALUE="">all</OPTION> + <OPTION VALUE="CARD">credit card (all)</OPTION> + <OPTION VALUE="CARD-VisaMC">credit card (Visa/MasterCard)</OPTION> + <OPTION VALUE="CARD-Amex">credit card (American Express)</OPTION> + <OPTION VALUE="CARD-Discover">credit card (Discover)</OPTION> + <OPTION VALUE="CARD-Maestro">credit card (Maestro/Switch/Solo)</OPTION> + <OPTION VALUE="CHEK">electronic check / ACH</OPTION> + <OPTION VALUE="BILL">check</OPTION> + <OPTION VALUE="PREP">prepaid card</OPTION> + <OPTION VALUE="CASH">cash</OPTION> + <OPTION VALUE="WEST">Western Union</OPTION> + <OPTION VALUE="MCRD">manual credit card</OPTION> + </SELECT> + </TD> + </TR> + + <SCRIPT TYPE="text/javascript"> + + function payby_changed(what) { + if ( what.options[what.selectedIndex].value == 'BILL' ) { + document.getElementById('checkno_caption').style.color = '#000000'; + what.form.payinfo.disabled = false; + what.form.payinfo.style.backgroundColor = '#ffffff'; + } else { + document.getElementById('checkno_caption').style.color = '#bbbbbb'; + what.form.payinfo.disabled = true; + what.form.payinfo.style.backgroundColor = '#dddddd'; + } + } + + </SCRIPT> + + <TR> + <TD ALIGN="right"><FONT ID="checkno_caption" COLOR="#bbbbbb">Check #: </FONT></TD> + <TD> + <INPUT TYPE="text" NAME="payinfo" DISABLED STYLE="background-color: #dddddd"> + </TD> + </TR> + + <% include( '/elements/tr-select-agent.html', + 'curr_value' => scalar($cgi->param('agentnum')), + 'label' => 'for agent: ', + 'disable_empty' => 0, + ) + %> + + <% include( '/elements/tr-select-otaker.html' ) %> + + <TR> + <TD ALIGN="right" VALIGN="center">Payment</TD> + <TD> + <TABLE> + <% include( '/elements/tr-input-beginning_ending.html', + layout => 'horiz', + ) + %> + </TABLE> + </TD> + </TR> + + <% include( '/elements/tr-input-lessthan_greaterthan.html', + 'label' => 'Amount', + 'field' => 'paid', + ) + %> + +</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'); + +</%init> diff --git a/httemplate/search/report_prepaid_income.cgi b/httemplate/search/report_prepaid_income.cgi index ce928b81c..c0e2b807e 100644 --- a/httemplate/search/report_prepaid_income.cgi +++ b/httemplate/search/report_prepaid_income.cgi @@ -1,36 +1,86 @@ <% include("/elements/header.html", 'Prepaid Income (Unearned Revenue) Report') %> -<% table() %> - <TR> - <TH>Actual Unearned Revenue</TH> - <TH>Legacy Unearned Revenue</TH> - </TR> +<% include( '/elements/table-grid.html' ) %> + <TR> - <TD ALIGN="right">$<% $total %> - <TD ALIGN="right"> - <% $now == $time ? "\$$total_legacy" : '<i>N/A</i>'%> - </TD> +% if ( scalar(@agentnums) > 1 ) { + <TH CLASS="grid" BGCOLOR="#cccccc">Agent</TH> +% } + <TH CLASS="grid" BGCOLOR="#cccccc"><% $actual_label %>Unearned Revenue</TH> +% if ( $legacy ) { + <TH CLASS="grid" BGCOLOR="#cccccc">Legacy Unearned Revenue</TH> +% } </TR> +% my $bgcolor1 = '#eeeeee'; +% my $bgcolor2 = '#ffffff'; +% my $bgcolor; +% +% push @agentnums, 0 unless scalar(@agentnums) < 2; +% foreach my $agentnum (@agentnums) { +% +% if ( $bgcolor eq $bgcolor1 ) { +% $bgcolor = $bgcolor2; +% } else { +% $bgcolor = $bgcolor1; +% } +% +% my $alink = $agentnum ? "$link;agentnum=$agentnum" : $link; +% +% my $agent_name = 'Total'; +% if ( $agentnum ) { +% my $agent = qsearchs('agent', { 'agentnum' => $agentnum }) +% or die "unknown agentnum $agentnum"; +% $agent_name = $agent->agent; +% } + + <TR> + +% if ( scalar(@agentnums) > 1 ) { + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $agent_name |h %></TD> +% } + + <TD ALIGN="right" CLASS="grid" BGCOLOR="<% $bgcolor %>"><A HREF="<% $alink %>"><% $money_char %><% $total{$agentnum} %></A></TD> + +% if ( $legacy ) { + <TD ALIGN="right" CLASS="grid" BGCOLOR="<% $bgcolor %>"> + <% $now == $time ? $money_char.$total_legacy{$agentnum} : '<i>N/A</i>'%> + </TD> +% } + + </TR> + +% } + </TABLE> + <BR> -Actual unearned revenue is the amount of unearned revenue Freeside has -actually invoiced for packages with longer-than monthly terms. -<BR><BR> -Legacy unearned revenue is the amount of unearned revenue represented by -customer packages. This number may be larger than actual unearned -revenue if you have imported longer-than monthly customer packages from -a previous billing system. -</BODY> -</HTML> +<% $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. + +% if ( $legacy ) { + <BR><BR> + Legacy unearned revenue is the amount of unearned revenue represented by + customer packages. This number may be larger than actual unearned + revenue if you have imported longer-than monthly customer packages from + a previous billing system. +% } + +<% include('/elements/footer.html') %> <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); -#doesn't yet deal with daily/weekly packages +my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; + +my $legacy = $conf->exists('enable_legacy_prepaid_income'); +my $actual_label = $legacy ? 'Actual ' : ''; -#needs to be re-written in sql for efficiency +#doesn't yet deal with daily/weekly packages my $time = time; @@ -38,74 +88,148 @@ my $now = $cgi->param('date') && str2time($cgi->param('date')) || $time; $now =~ /^(\d+)$/ or die "unparsable date?"; $now = $1; -my @where = (); +my $link = "cust_bill_pkg.cgi?nottax=1;unearned_now=$now"; -if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { - my $agentnum = $1; - push @where, "agentnum = $agentnum"; -} - -#here is the agent virtualization -push @where, $FS::CurrentUser::CurrentUser->agentnums_sql; - -my $where = join(' AND ', @where); -$where = "AND $where" if $where; - -my( $total, $total_legacy ) = ( 0, 0 ); - -my @cust_bill_pkg = - grep { $_->cust_pkg && $_->cust_pkg->part_pkg->freq !~ /^([01]|\d+[dw])$/ } - 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 }, - 'edate' => { op=>'>', value=>$now }, - }, - 'extra_sql' => $where, - }); - -my @cust_pkg = - grep { $_->part_pkg->recur != 0 - && $_->part_pkg->freq !~ /^([01]|\d+[dw])$/ - } - qsearch({ - 'select' => 'cust_pkg.*', - 'table' => 'cust_pkg', - 'addl_from' => ' LEFT JOIN cust_main USING ( custnum ) ', - 'hashref' => { 'bill' => { 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; +my $curuser = $FS::CurrentUser::CurrentUser; +my $agentnum = ''; +my @agentnums = (); +$agentnum ? ($agentnum) : $curuser->agentnums; +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + @agentnums = ($1); + #XXX#push @where, "agentnum = $agentnum"; + #XXX#$link .= ";agentnum=$agentnum"; +} else { + @agentnums = $curuser->agentnums; } -foreach my $cust_pkg ( @cust_pkg ) { - my $period = $cust_pkg->bill - $cust_pkg->last_bill; - - my $elapsed = $now - $cust_pkg->last_bill; - $elapsed = 0 if $elapsed < 0; - - my $remaining = 1 - $elapsed/$period; +my @where = (); - my $unearned = $remaining * $cust_pkg->part_pkg->recur; #!! only works for flat/legacy - $total_legacy += $unearned; +#here is the agent virtualization +push @where, $curuser->agentnums_sql( 'table'=>'cust_main' ); + +#well, because cust_bill_pkg.cgi has it and without it the numbers don't match.. +push @where , " payby != 'COMP' " + unless $cgi->param('include_comp_cust'); + +my %total = (); +my %total_legacy = (); +foreach my $agentnum (@agentnums) { + + my $where = join(' AND ', @where, "cust_main.agentnum = $agentnum"); + $where = "AND $where" if $where; + + 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)"; + my $elapsed = "(CASE WHEN cust_bill_pkg.sdate > $now + THEN 0 + ELSE ($now - cust_bill_pkg.sdate) + END)"; + #my $elapsed = "CAST($unearned - cust_bill_pkg.sdate AS $float)"; + + my $remaining = "(1 - $elapsed/$period)"; + + my $select = "SUM($remaining * cust_bill_pkg.recur)"; + + #[...] + + 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 ) + WHERE pkgpart > 0 + AND sdate < $now + AND 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 + "; + + my $sth = dbh->prepare($sql) or die dbh->errstr; + $sth->execute or die $sth->errstr; + my $total = $sth->fetchrow_arrayref->[0]; + + $total = sprintf('%.2f', $total); + $total{$agentnum} = $total; + $total{0} += $total; + + if ( $legacy ) { + + #not yet rewritten in sql, but now not enabled by default + + my @cust_pkg = + grep { $_->part_pkg->recur != 0 + && $_->part_pkg->freq !~ /^([01]|\d+[dw])$/ + } + qsearch({ + 'select' => 'cust_pkg.*', + 'table' => 'cust_pkg', + 'addl_from' => ' LEFT JOIN cust_main USING ( custnum ) ', + 'hashref' => { 'bill' => { op=>'>', value=>$now } }, + 'extra_sql' => $where, + }); + + foreach my $cust_pkg ( @cust_pkg ) { + my $period = $cust_pkg->bill - $cust_pkg->last_bill; + + my $elapsed = $now - $cust_pkg->last_bill; + $elapsed = 0 if $elapsed < 0; + + my $remaining = 1 - $elapsed/$period; + + my $unearned = $remaining * $cust_pkg->part_pkg->recur; #!! only works for flat/legacy + $total_legacy += $unearned; + + } + + $total_legacy = sprintf('%.2f', $total_legacy); + $total_legacy{$agentnum} = $total_legacy; + $total_legacy{0} += $total_legacy; + + } } -$total = sprintf('%.2f', $total); -$total_legacy = sprintf('%.2f', $total_legacy); - +$total{0} = sprintf('%.2f', $total{0}); +$total_legacy{0} = sprintf('%.2f', $total_legacy{0}); + </%init> diff --git a/httemplate/search/report_prepaid_income.html b/httemplate/search/report_prepaid_income.html index d707bd81b..04801beee 100644 --- a/httemplate/search/report_prepaid_income.html +++ b/httemplate/search/report_prepaid_income.html @@ -44,7 +44,7 @@ <SCRIPT TYPE="text/javascript"> Calendar.setup({ inputField: "date_text", - ifFormat: "%m/%d/%Y", + ifFormat: "<% $date_format %>", button: "date_button", align: "BR" }); @@ -58,4 +58,7 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); +my $conf = new FS::Conf; +my $date_format = $conf->config('date_format') || '%m/%d/%Y'; + </%init> |