diff options
Diffstat (limited to 'httemplate/search')
35 files changed, 2373 insertions, 1160 deletions
diff --git a/httemplate/search/477.html b/httemplate/search/477.html new file mode 100755 index 0000000..9102c20 --- /dev/null +++ b/httemplate/search/477.html @@ -0,0 +1,155 @@ +<% include( 'elements/search.html', + 'title' => 'FCC Form 477 Results', + 'html_init' => $html_init, + 'name' => 'regions', + 'query' => [ @sql_query ], + 'count_query' => $count_query, + 'order_by' => 'ORDER BY censustract', + 'header' => [ + 'County code', + 'Census tract code', + 'Upload rate', + 'Download rate', + 'Technology code', + 'Technology code other', + 'Quantity', + 'Percentage residential', + ], + 'fields' => [ + sub { my $row = shift; substr($row->censustract, 2, 3) }, + sub { my $row = shift; substr($row->censustract, 5) }, + 'upload', + 'download', + sub { 7 }, + sub { '' }, + 'quantity', + sub { my $row = shift; sprintf "%.2f", $row->residential }, + ], + 'links' => [ + [ $link, $link_suffix ], + [ $link, $link_suffix ], + [ $link, $link_suffix ], + [ $link, $link_suffix ], + [ $link, $link_suffix ], + [ $link, $link_suffix ], + [ $link, $link_suffix ], + [ $link, $link_suffix ], + ], + ) +%> +<%init> + +my $curuser = $FS::CurrentUser::CurrentUser; + +die "access denied" + unless $curuser->access_right('List packages'); + +my %search_hash = (); +my @sql_query = (); + +for ( qw(agentnum magic classnum) ) { + $search_hash{$_} = $cgi->param($_) if $cgi->param($_); +} + +my @column_option = $cgi->param('column_option') + if $cgi->param('column_option'); + +my @row_option = $cgi->param('row_option') + if $cgi->param('row_option'); + +my $where = join(' OR ', map { "num = $_" } grep { /^\d+$/ } @column_option ); +my %column_option_name = $where ? + ( map { $_->name => $_->num } + qsearch({ 'table' => 'part_pkg_report_option', + 'hashref' => {}, + 'extra_sql' => "WHERE $where", + }) + ) : + ( 'all packages' => '' ); + +$where = join(' OR ', map { "num = $_" } grep { /^\d+$/ } @row_option ); +my %row_option_name = $where ? + ( map { $_->name => $_->num } + qsearch({ 'table' => 'part_pkg_report_option', + 'hashref' => {}, + 'extra_sql' => "WHERE $where", + }) + ) : + ( 'all packages' => '' ); + +@row_option = map { $row_option_name{$_} } sort keys %row_option_name; +@column_option = map { $column_option_name{$_} } sort keys %column_option_name; + +#$search_hash{row_option} = join(',', @row_option) if @row_option; +my $html_init = '<H2>Summary</H2>'. include('/elements/table.html'); + $html_init .= '<TR><TH></TH>'; +foreach my $column ( sort keys %column_option_name ) { + $html_init .= "<TH>$column</TH>"; +} + $html_init .= "</TR>"; + +my $rowcount = 1; +foreach my $row ( sort keys %row_option_name ) { + + $html_init .= "<TR><TH>$row</TH>"; + + my $columncount = 2; + foreach my $column ( sort keys %column_option_name ) { + my @report_option = (); + push @report_option, $row_option_name{$row} + if $row_option_name{$row}; + push @report_option, $column_option_name{$column} + if $column_option_name{$column}; + my $report_option = join(',', @report_option) if @report_option; + + my $sql_query = FS::cust_pkg->search_sql( + { %search_hash, + ($report_option ? ( 'report_option' => $report_option ) : () ), + } + ); + my $extracolumns = "$rowcount AS upload, $columncount AS download"; + my $percent = "100-100*cast(count(cust_main.company) as numeric)/cast(count(*) as numeric) AS residential"; + $sql_query->{select} = "count(*) AS quantity, $extracolumns, censustract, $percent"; + $sql_query->{extra_sql} =~ /^(.*)(ORDER BY pkgnum)(.*)$/s + or die "couldn't parse extra_sql"; + $sql_query->{extra_sql} = "$1 GROUP BY censustract $3"; + + my $count_sql = delete($sql_query->{'count_query'}); + + my $count_sth = dbh->prepare($count_sql) + or die "Error preparing $count_sql: ". dbh->errstr; + $count_sth->execute + or die "Error executing $count_sql: ". $count_sth->errstr; + my $count_arrayref = $count_sth->fetchrow_arrayref; + my $count = $count_arrayref->[0]; + + $html_init .= "<TD>$count</TD>"; + push @sql_query, $sql_query; + $columncount++; + } + + $html_init .= "</TR>"; + $rowcount++; +} + +$html_init .= "</TABLE><BR><H2>Details</H2>"; + +my $count_query = 'SELECT count(*) FROM ( ('. + join( ') UNION (', + map { my $extra = $_->{extra_sql}; my $addl = $_->{addl_from}; + "SELECT censustract from cust_pkg $addl $extra"; + } + @sql_query + ). ') ) AS foo'; + +my $link = 'cust_pkg.cgi?'. + join(';', map{ "$_=". $search_hash{$_} } keys %search_hash). ';'; +my $link_suffix = sub { my $row = shift; + my $result = 'censustract='. $row->censustract. ';'; + $result .= 'report_option='. @row_option[$row->upload - 1] + if @row_option[$row->upload - 1]; + $result .= 'report_option='. @column_option[$row->download - 1] + if @column_option[$row->download - 1]; + $result; + }; +</%init> diff --git a/httemplate/search/cdr.html b/httemplate/search/cdr.html index 852eeba..d1f68c5 100644 --- a/httemplate/search/cdr.html +++ b/httemplate/search/cdr.html @@ -10,7 +10,7 @@ 'count_query' => $count_query, 'header' => [ '', # checkbox column - fields('cdr'), #XXX fill in some nice names + @header, ], 'fields' => [ sub { @@ -20,9 +20,10 @@ my $acctid = $cdr->acctid; qq!<INPUT NAME="acctid$acctid" TYPE="checkbox" VALUE="1">!; }, - fields('cdr'), #XXX fill in some pretty-print + @fields, #XXX fill in some pretty-print #processing, etc. ], + 'links' => \@links, 'html_form' => qq!<FORM NAME="cdrForm" ACTION="$p/misc/cdr.cgi" METHOD="POST">!, #false laziness w/queue.html @@ -65,46 +66,87 @@ my $hashref = {}; my @search = (); ### +# dates +### + +my $str2time_sql = str2time_sql; + +my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +push @search, "$str2time_sql calldate) >= $beginning ", + "$str2time_sql calldate) <= $ending"; + +### +# duration / billsec +### + +push @search, FS::UI::Web::parse_lt_gt($cgi, 'duration'); +push @search, FS::UI::Web::parse_lt_gt($cgi, 'billsec'); + +#above here things just push @search +#below here things also have to define $hashref->{} or push @qsearch +my @qsearch = @search; + +### # freesidestatus ### if ( $cgi->param('freesidestatus') eq 'NULL' ) { - my $title = "Unprocessed $title"; + $title = "Unprocessed $title"; $hashref->{'freesidestatus'} = ''; # Record.pm will take care of it push @search, "( freesidestatus IS NULL OR freesidestatus = '' )"; } elsif ( $cgi->param('freesidestatus') =~ /^([\w ]+)$/ ) { - my $title = "Processed $title"; + $title = "Processed $title"; $hashref->{'freesidestatus'} = $1; push @search, "freesidestatus = '$1'"; } ### -# dates +# termpartNstatus ### -my $str2time_sql = str2time_sql; +foreach my $param ( grep /^termpart\d+status$/, $cgi->param ) { -my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); -push @search, "$str2time_sql calldate) >= $beginning ", - "$str2time_sql calldate) <= $ending"; + my $status = $cgi->param($param); -### -# duration / billsec -### + $param =~ /^termpart(\d+)status$/ or die 'guru meditation 54something'; + my $termpart = $1; -push @search, FS::UI::Web::parse_lt_gt($cgi, 'duration'); -push @search, FS::UI::Web::parse_lt_gt($cgi, 'billsec'); + my $search = ''; + if ( $status eq 'NULL' ) { + + #false lazienss w/cdr_termination.pm (i should be a part_termination method) + my $where_term = + "( cdr.acctid = cdr_termination.acctid AND termpart = $termpart ) "; + #my $join_term = "LEFT JOIN cdr_termination ON ( $where_term )"; + $search = + "NOT EXISTS ( SELECT 1 FROM cdr_termination WHERE $where_term )"; + + } elsif ( $cgi->param('freesidestatus') =~ /^([\w ]+)$/ ) { + + #false lazienss w/cdr_termination.pm (i should be a part_termination method) + my $where_term = + "( cdr.acctid = cdr_termination.acctid AND termpart = $termpart AND status = '$1' ) "; + #my $join_term = "LEFT JOIN cdr_termination ON ( $where_term )"; + $search = + "EXISTS ( SELECT 1 FROM cdr_termination WHERE $where_term )"; + + } + + if ( $search ) { + push @search, $search; + push @qsearch, $search; + } + +} ### # src/dest/charged_party ### -my @qsearch = @search; - if ( $cgi->param('src') =~ /^\s*([\d\-\+\ ]+)\s*$/ ) { ( my $src = $1 ) =~ s/\D//g; $hashref->{'src'} = $src; @@ -117,15 +159,23 @@ if ( $cgi->param('dst') =~ /^\s*([\d\-\+ ]+)\s*$/ ) { push @search, "dst = '$dst'"; } +if ( $cgi->param('dcontext') =~ /^\s*(.+)\s*$/ ) { + my $dcontext = $1; + $hashref->{'dcontext'} = $dcontext; + push @search, "dcontext = '$dcontext'"; +} + if ( $cgi->param('charged_party') =~ /^\s*([\d\-\+\ ]+)\s*$/ ) { ( my $charged_party = $1 ) =~ s/\D//g; #$hashref->{'charged_party'} = $charged_party; #push @search, "charged_party = '$charged_party'"; #XXX countrycode - push @search, " ( charged_party = '$charged_party' - OR charged_party = '1$charged_party' ) "; - push @qsearch, " ( charged_party = '$charged_party' - OR charged_party = '1$charged_party' ) "; + + my $search = " ( charged_party = '$charged_party' + OR charged_party = '1$charged_party' ) "; + + push @search, $search; + push @qsearch, $search; } ### @@ -144,6 +194,23 @@ if ( $cgi->param('cdrbatch') ne '__ALL__' ) { } ### +# acctid +### + +if ( $cgi->param('acctid') =~ /\d/ ) { + my $acctid = $cgi->param('acctid'); + $acctid =~ s/\r\n/\n/g; #browsers? + my @acctid = map { /^\s*(\d+)\s*$/ or die "guru meditation #4"; $1; } + grep { /^\s*(\d+)\s*$/ } + split(/\n/, $acctid); + if ( @acctid ) { + my $search = 'acctid IN ( '. join(',', @acctid). ' )'; + push @qsearch, $search; + push @search, $search; + } +} + +### # finish it up ### @@ -156,4 +223,53 @@ my $qsearch = join(' AND ', @qsearch); $qsearch = ( scalar(keys %$hashref) ? ' AND ' : ' WHERE ' ) . $qsearch if $qsearch; +### +# display fields +### + +my %header = %{ FS::cdr->table_info->{'fields'} }; + +my @first = qw( acctid calldate clid charged_party src dst dcontext ); +my %first = map { $_=>1 } @first; + +my @fields = ( @first, grep !$first{$_}, fields('cdr') ); + +if ( $cgi->param('show') ) { + @fields = grep $cgi->param("show_$_"), @fields; +} + +my @header = map { + if ( exists($header{$_}) ) { + $header{$_}; + } else { + my $header = $_; + $header =~ s/\_/ /g; #//wtf + ucfirst($header); + } + } @fields; + +my $date_sub_factory = sub { + my $column = shift; + sub { + #my $cdr = shift; + my $date = shift->$column(); + $date ? time2str( '%Y-%m-%d %T', $date ) : ''; #config time2str format? + }; +}; + +my %fields = ( + #any other formatters? + map { $_ => &{ $date_sub_factory }($_) } qw( startdate answerdate enddate ) +); + +my %links = ( + 'svcnum' => + sub { $_[0]->svcnum ? [ $p.'view/svc_phone.cgi?', 'svcnum' ] : ''; }, +); + +@fields = map { exists($fields{$_}) ? $fields{$_} : $_ } @fields; + + #checkbox column +my @links = ( '', map { exists($links{$_}) ? $links{$_} : '' } @fields ); + </%init> diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 89901ac..52f59de 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -8,7 +8,10 @@ '#', 'Description', 'Setup charge', - 'Recurring charge', + ( $use_usage eq 'usage' + ? 'Usage charge' + : 'Recurring charge' + ), 'Invoice', 'Date', FS::UI::Web::cust_header(), @@ -16,13 +19,23 @@ 'fields' => [ 'billpkgnum', sub { $_[0]->pkgnum > 0 - ? $_[0]->get('pkg') - : $_[0]->get('itemdesc') + ? $_[0]->get('pkg') # possibly use override.pkg + : $_[0]->get('itemdesc') # but i think this correct }, #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 { sprintf($money_char.'%.2f', shift->recur ) }, + sub { my $row = shift; + my $value = 0; + if ( $use_usage eq 'recurring' ) { + $value = $row->recur - $row->usage; + } elsif ( $use_usage eq 'usage' ) { + $value = $row->usage; + } else { + $value = $row->recur; + } + sprintf($money_char.'%.2f', $value ); + }, 'invnum', sub { time2str('%b %d %Y', shift->_date ) }, \&FS::UI::Web::cust_fields, @@ -83,32 +96,66 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { push @where, "cust_main.agentnum = $1"; } +#classnum +# not specified: all classes +# 0: empty class +# N: classnum +my $use_override = $cgi->param('use_override'); if ( $cgi->param('classnum') =~ /^(\d+)$/ ) { + my $comparison = ''; if ( $1 == 0 ) { - push @where, "classnum IS NULL"; + $comparison = "IS NULL"; } else { - push @where, "classnum = $1"; + $comparison = "= $1"; + } + + if ( $use_override ) { + push @where, "( + part_pkg.classnum $comparison AND pkgpart_override IS NULL OR + override.classnum $comparison AND pkgpart_override IS NOT NULL + )"; + } else { + push @where, "part_pkg.classnum $comparison"; } } -#sub _where { -# my $table = shift; -# my $prefix = @_ ? shift : ''; -# " -# ( cust_main_county.county = $table.${prefix}.county -# OR ( cust_main_county.county IS NULL AND $table.${prefix}.county = '' ) -# OR ( cust_main_county.county = '' AND $table.${prefix}.county IS NULL) -# OR ( cust_main_county.county IS NULL AND $table.${prefix}.county IS NULL) -# ) -# AND ( cust_main_county.state = $table.${prefix}.state -# OR ( cust_main_county.state IS NULL AND $table.${prefix}.state = '' ) -# OR ( cust_main_county.state = '' AND $table.${prefix}.state IS NULL ) -# OR ( cust_main_county.state IS NULL AND $table.${prefix}.state IS NULL ) -# ) -# AND cust_main_county.country = $table.${prefix}.country -# "; -# -#} +if ( $cgi->param('taxclass') + && ! $cgi->param('istax') #no part_pkg.taxclass in this case + #(should we save a taxclass or a link to taxnum + # in cust_bill_pkg or something like + # cust_bill_pkg_tax_location?) + ) +{ + + #override taxclass when use_override is specified? probably + #if ( $use_override ) { + # + # push @where, + # ' ( '. join(' OR ', + # map { + # ' ( part_pkg.taxclass = '. dbh->quote($_). + # ' AND pkgpart_override IS NULL '. + # ' OR '. + # ' override.taxclass = '. dbh->quote($_). + # ' AND pkgpart_override IS NOT NULL '. + # ' ) ' + # } + # $cgi->param('taxclass') + # ). + # ' ) '; + # + #} else { + + push @where, + ' ( '. join(' OR ', + map ' part_pkg.taxclass = '.dbh->quote($_), + $cgi->param('taxclass') + ). + ' ) '; + + #} + +} if ( $cgi->param('out') ) { @@ -143,17 +190,49 @@ if ( $cgi->param('out') ) { } -} elsif ( $cgi->param('country' ) ) { +} elsif ( $cgi->param('country') ) { - my %ph = map { $_ => dbh->quote( $cgi->param($_) ) } - qw( county state country ); + my @counties = $cgi->param('county'); + + if ( scalar(@counties) > 1 ) { - my ( $loc_sql, @param ) = FS::cust_pkg->location_sql; - while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution - $loc_sql =~ s/\?/$ph{shift(@param)}/e; - } + #hacky, could be more efficient. care if it is ever used for more than the + # tax-report_groups filtering kludge + + my $locs_sql = + ' ( '. join(' OR ', map { + + my %ph = ( 'county' => dbh->quote($_), + map { $_ => dbh->quote( $cgi->param($_) ) } + qw( state country ) + ); + + my ( $loc_sql, @param ) = FS::cust_pkg->location_sql; + while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution + $loc_sql =~ s/\?/$ph{shift(@param)}/e; + } + + $loc_sql; - push @where, $loc_sql; + } @counties + + ). ' ) '; + + push @where, $locs_sql; + + } else { + + my %ph = map { $_ => dbh->quote( $cgi->param($_) ) } + qw( county state country ); + + my ( $loc_sql, @param ) = FS::cust_pkg->location_sql; + while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution + $loc_sql =~ s/\?/$ph{shift(@param)}/e; + } + + push @where, $loc_sql; + + } if ( $cgi->param('istax') ) { if ( $cgi->param('taxname') ) { @@ -168,13 +247,6 @@ if ( $cgi->param('out') ) { #warn "neither nottax nor istax parameters specified"; } - push @where, ' taxclass = '. dbh->quote( $cgi->param('taxclass') ) - if $cgi->param('taxclass') - && ! $cgi->param('istax'); #no part_pkg.taxclass in this case - #(should we save a taxclass or a link to taxnum - # in cust_bill_pkg or something like - # cust_bill_pkg_tax_location?) - if ( $cgi->param('taxclassNULL') ) { my %hash = ( 'country' => scalar($cgi->param('country')) ); @@ -189,19 +261,72 @@ if ( $cgi->param('out') ) { } +} elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) { + + # this should really be shoved out to FS::cust_pkg->location_sql or something + # along with the code in report_newtax.cgi + + my %pn = ( + 'county' => 'tax_rate_location.county', + 'state' => 'tax_rate_location.state', + 'city' => 'tax_rate_location.city', + 'locationtaxid' => 'cust_bill_pkg_tax_rate_location.locationtaxid', + ); + + my %ph = map { ( $pn{$_} => dbh->quote( $cgi->param($_) || '' ) ) } + qw( county state city locationtaxid ); + + push @where, + join( ' AND ', map { "( $_ = $ph{$_} OR $ph{$_} = '' AND $_ IS NULL)" } + keys %ph + ); + } -if ($cgi->param('itemdesc')) { - if ($cgi->param('itemdesc') eq 'Tax') { +if ( $cgi->param('itemdesc') ) { + if ( $cgi->param('itemdesc') eq 'Tax' ) { push @where, "(itemdesc='Tax' OR itemdesc is null)"; - }else{ + } else { push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc')); } } + +if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ && $cgi->param('istax') ) { + my ( $group_op, $group_value ) = ( $1, $2 ); + if ( $group_op eq '=' ) { + #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%'); + push @where, 'itemdesc = '. dbh->quote($group_value); + } elsif ( $group_op eq '!=' ) { + push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )'; + } else { + die "guru meditation #00de: group_op $group_op\n"; + } + +} + push @where, 'cust_bill_pkg.pkgnum != 0' if $cgi->param('nottax'); push @where, 'cust_bill_pkg.pkgnum = 0' if $cgi->param('istax'); -push @where, " tax = 'Y' " if $cgi->param('cust_tax'); +if ( $cgi->param('cust_tax') ) { + #false laziness -ish w/report_tax.cgi + my $cust_exempt; + if ( $cgi->param('taxname') ) { + my $q_taxname = dbh->quote($cgi->param('taxname')); + $cust_exempt = + "( tax = 'Y' + OR EXISTS ( SELECT 1 FROM cust_main_exemption + WHERE cust_main_exemption.custnum = cust_main.custnum + AND cust_main_exemption.taxname = $q_taxname ) + ) + "; + } else { + $cust_exempt = " tax = 'Y' "; + } + + push @where, $cust_exempt; +} + +my $use_usage = $cgi->param('use_usage'); my $count_query; if ( $cgi->param('pkg_tax') ) { @@ -267,8 +392,15 @@ if ( $cgi->param('pkg_tax') ) { } else { - $count_query = - "SELECT COUNT(*), SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)"; + $count_query = "SELECT COUNT(*), "; + + if ( $use_usage eq 'recurring' ) { + $count_query .= "SUM(setup + recur - usage)"; + } elsif ( $use_usage eq 'usage' ) { + $count_query .= "SUM(usage)"; + } else { + $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)"; + } } @@ -282,7 +414,9 @@ 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 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'); @@ -295,6 +429,10 @@ if ( $cgi->param('nottax') ) { #quelle kludge, false laziness w/report_tax.cgi $where =~ s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g; + } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) { + $join_pkg .= + ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '. + ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) '; } } else { @@ -307,7 +445,17 @@ if ( $cgi->param('nottax') ) { } -$count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where"; +if ($use_usage) { + $count_query .= + " FROM (SELECT cust_bill_pkg.setup, cust_bill_pkg.recur, + ( SELECT COALESCE( SUM(amount), 0 ) FROM cust_bill_pkg_detail + WHERE cust_bill_pkg.billpkgnum = cust_bill_pkg_detail.billpkgnum + ) AS usage FROM cust_bill_pkg $join_cust $join_pkg $where + ) AS countquery"; +} else { + $count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where"; +} +warn "count_query is $count_query\n"; my @select = ( 'cust_bill_pkg.*', diff --git a/httemplate/search/cust_event.html b/httemplate/search/cust_event.html index d55b5c6..f8cf6b2 100644 --- a/httemplate/search/cust_event.html +++ b/httemplate/search/cust_event.html @@ -75,29 +75,34 @@ my $status_sub = sub { my $part_event = $cust_event->part_event; - if ( $part_event->eventtable eq 'cust_bill' && $part_event->templatename ) { - my $alt_templatename = $part_event->templatename; - my $alt_link = "$alt_templatename-". $cust_event->tablenum; + if ( $part_event->eventtable eq 'cust_bill' + && ( $part_event->templatename || $part_event->option('notice_name') ) + ) + { + my $link = 'invnum='. $cust_event->tablenum; + $link .= ';template='. uri_escape($part_event->templatename) + if $part_event->templatename; + $link .= ';notice_name='. uri_escape($part_event->option('notice_name')) + if $part_event->option('notice_name'); my $conf = new FS::Conf; my $cust_bill = $cust_event->cust_X; $status .= qq{ - ( <A HREF="${p}view/cust_bill.cgi?$alt_link">view</A> - | <A HREF="${p}view/cust_bill-pdf.cgi?$alt_link.pdf">view - typeset</A> - | <A HREF="${p}misc/print-invoice.cgi?$alt_link">re-print</A> + ( <A HREF="${p}view/cust_bill.cgi?$link">view</A> + | <A HREF="${p}view/cust_bill-pdf.cgi?$link">view typeset</A> + | <A HREF="${p}misc/send-invoice.cgi?method=print;$link">re-print</A> }; if ( grep { $_ ne 'POST' } $cust_bill->cust_main->invoicing_list ) { $status .= qq{ - | <A HREF="${p}misc/email-invoice.cgi?$alt_link">re-email</A> + | <A HREF="${p}misc/send-invoice.cgi?method=email;$link">re-email</A> }; } if ( $conf->exists('hylafax') && length($cust_bill->cust_main->fax) ) { $status .= qq{ - | <A HREF="${p}misc/fax-invoice.cgi?$alt_link">re-fax</A> + | <A HREF="${p}misc/send-invoice.cgi?method=fax;$link">re-fax</A> } } @@ -124,7 +129,12 @@ my $trigger_link = sub { my $eventtable = $cust_event->eventtable; if ( $eventtable eq 'cust_pkg' ) { my $custnum = $cust_event->cust_main_custnum; - [ "${p}view/cust_main.cgi?$custnum#cust_pkg", 'tablenum' ]; + my $show = $FS::CurrentUser::CurrentUser->default_customer_view =~ /^(jumbo|packages)$/ + ? '' + : ';show=packages'; + my $pkgnum = $cust_event->tablenum; + my $frag = "cust_pkg$pkgnum"; #hack for IE ignoring real #fragment + [ "${p}view/cust_main.cgi?custnum=$custnum$show;fragment=$frag#cust_pkg", 'tablenum' ]; } else { [ "${p}view/$eventtable.cgi?", 'tablenum' ]; } @@ -142,61 +152,24 @@ die "access denied" || $cgi->param('invnum') =~ /^(\d+)$/ || $cgi->param('pkgnum') =~ /^(\d+)$/ ); - -my $title = $cgi->param('failed') - ? 'Failed billing events' - : 'Billing events'; +my $title = $cgi->param('failed') ? 'Failed billing events' : 'Billing events'; -my @search = (); +my %search = (); -if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { - push @search, "cust_main.agentnum = $1"; - #my $agent = qsearchs('agent', { 'agentnum' => $1 } ); - #die "unknown agentnum $1" unless $agent; +my @scalars = qw ( agentnum custnum invnum pkgnum failed ); +for my $param ( @scalars ) { + $search{$param} = scalar( $cgi->param($param) ) + if $cgi->param($param); } my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); -push @search, "cust_event._date >= $beginning", - "cust_event._date <= $ending"; +$search{'beginning'} = $beginning; +$search{'ending'} = $ending; -if ( $cgi->param('failed') ) { - push @search, "statustext != ''", - "statustext IS NOT NULL", - "statustext != 'N/A'"; -} - -#if ( $cgi->param('part_event.payby') =~ /^(\w+)$/ ) { -# push @search, "part_event.payby = '$1'"; -#} - -if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { - push @search, "cust_main.custnum = '$1'"; -} -if ( $cgi->param('invnum') =~ /^(\d+)$/ ) { - push @search, "part_event.eventtable = 'cust_bill'", - "tablenum = '$1'"; -} -if ( $cgi->param('pkgnum') =~ /^(\d+)$/ ) { - push @search, "part_event.eventtable = 'cust_pkg'", - "tablenum = '$1'"; -} - -#here is the agent virtualization -push @search, $curuser->agentnums_sql( 'table' => 'cust_main' ); - -my $where = 'WHERE '. join(' AND ', @search ); +my $where = ' WHERE '. FS::cust_event->search_sql( \%search ); -my $join = " - JOIN part_event USING ( eventpart ) - LEFT JOIN cust_bill ON ( eventtable = 'cust_bill' AND tablenum = invnum ) - LEFT JOIN cust_pkg ON ( eventtable = 'cust_pkg' AND tablenum = pkgnum ) - LEFT JOIN cust_main ON ( ( eventtable = 'cust_main' AND tablenum = cust_main.custnum ) - OR ( eventtable = 'cust_bill' AND cust_bill.custnum = cust_main.custnum ) - OR ( eventtable = 'cust_pkg' AND cust_pkg.custnum = cust_main.custnum ) - ) -"; - #'LEFT JOIN cust_main USING ( custnum ) '; +my $join = FS::cust_event->join_sql(); my $sql_query = { 'table' => 'cust_event', @@ -217,22 +190,24 @@ my $count_sql = "SELECT COUNT(*) FROM cust_event $join $where"; my $conf = new FS::Conf; -my $failed = $cgi->param('failed'); +my @params = ( @scalars, qw( beginning ending ) ); my $html_init = join("\n", map { ( my $action = $_ ) =~ s/_$//; include('/elements/progress-init.html', $_.'form', - [ 'action', 'beginning', 'ending', 'failed' ], + [ 'action', @params ], "../misc/${_}events.cgi", { 'message' => "Invoices re-${action}ed" }, #would be nice to show the number of them, but... $_, #key ), qq!<FORM NAME="${_}form">!, qq!<INPUT TYPE="hidden" NAME="action" VALUE="$_">!, #not used though - qq!<INPUT TYPE="hidden" NAME="beginning" VALUE="$beginning">!, - qq!<INPUT TYPE="hidden" NAME="ending" VALUE="$ending">!, - qq!<INPUT TYPE="hidden" NAME="failed" VALUE="$failed">!, + ( map { my $value = encode_entities( $search{$_} ); + qq(<INPUT TYPE="hidden" NAME="$_" VALUE="$value">); + } + @params #keys %search + ), qq!</FORM>! } qw( print_ email_ fax_ ) ). diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi index 36e4374..e65dc71 100755 --- a/httemplate/search/cust_main.cgi +++ b/httemplate/search/cust_main.cgi @@ -1,5 +1,7 @@ +%my $curuser = $FS::CurrentUser::CurrentUser; +% %die "access denied" -% unless $FS::CurrentUser::CurrentUser->access_right('List customers'); +% unless $curuser->access_right('List customers'); % %my $conf = new FS::Conf; %my $maxrecords = $conf->config('maxsearchrecordsperpage'); @@ -483,14 +485,17 @@ %# my $part_pkg = qsearchs( 'part_pkg', { pkgpart => $_->pkgpart } ); % my $part_pkg = $_->part_pkg; % -% my $pkg = $part_pkg->pkg; -% my $comment = $part_pkg->comment; -% my $pkgview = "${p}view/cust_main.cgi?$custnum#cust_pkg$pkgnum"; +% my $pkg_comment = $part_pkg->pkg_comment(nopkgpart => 1); +% my $show = $curuser->default_customer_view =~ /^(jumbo|packages)$/ +% ? '' +% : ';show=packages'; +% my $frag = "cust_pkg$pkgnum"; #hack for IE ignoring real #fragment +% my $pkgview = "${p}view/cust_main.cgi?custnum=$custnum$show;fragment=$frag#$frag"; % my @cust_svc = @{shift @lol_cust_svc}; % #my(@cust_svc) = qsearch( 'cust_svc', { 'pkgnum' => $_->pkgnum } ); % my $rowspan = scalar(@cust_svc) || 1; % -% print $n1, qq!<TD CLASS="grid" BGCOLOR="$bgcolor" ROWSPAN=$rowspan><A HREF="$pkgview"><FONT SIZE=-1>$pkg - $comment</FONT></A></TD>!; +% print $n1, qq!<TD CLASS="grid" BGCOLOR="$bgcolor" ROWSPAN=$rowspan><A HREF="$pkgview"><FONT SIZE=-1>$pkg_comment</FONT></A></TD>!; % % my($n2)=''; % foreach my $cust_svc ( @cust_svc ) { diff --git a/httemplate/search/cust_main.html b/httemplate/search/cust_main.html index 3282f0f..f098fd3 100755 --- a/httemplate/search/cust_main.html +++ b/httemplate/search/cust_main.html @@ -43,9 +43,12 @@ my %search_hash = (); #$search_hash{'query'} = $cgi->keywords; #scalars -for my $param (qw( +my @scalars = qw ( agentnum status cancelled_pkgs cust_fields flattened_pkgs custbatch -)) { + no_censustract +); + +for my $param ( @scalars ) { $search_hash{$param} = scalar( $cgi->param($param) ) if $cgi->param($param); } @@ -97,7 +100,7 @@ my $menubar = []; if ( $FS::CurrentUser::CurrentUser->access_right('Bulk send customer notices') ) { - my $uri = new URI::URL; + my $uri = new URI; $uri->query_form( \%search_hash ); my $query = $uri->query; diff --git a/httemplate/search/cust_pay_batch.cgi b/httemplate/search/cust_pay_batch.cgi index 1576963..2056876 100755 --- a/httemplate/search/cust_pay_batch.cgi +++ b/httemplate/search/cust_pay_batch.cgi @@ -132,6 +132,9 @@ if ( $pay_batch ) { || ( $pay_batch->status eq 'I' && $FS::CurrentUser::CurrentUser->access_right('Reprocess batches') ) + || ( $pay_batch->status eq 'R' + && $FS::CurrentUser::CurrentUser->access_right('Redownload resolved batches') + ) ) { $html_init .= qq!<FORM ACTION="$p/misc/download-batch.cgi" METHOD="POST">!; if ( $fixed ) { @@ -144,6 +147,7 @@ if ( $pay_batch ) { qq!<OPTION VALUE="PAP">80 byte file for TD Canada Trust PAP Batch</OPTION>!. qq!<OPTION VALUE="BoM">Bank of Montreal ECA batch</OPTION>!. qq!<OPTION VALUE="ach-spiritone">Spiritone ACH batch</OPTION>!. + qq!<OPTION VALUE="paymentech">Chase Paymentech</OPTION>!. qq!</SELECT>!; } $html_init .= qq!<INPUT TYPE="hidden" NAME="batchnum" VALUE="$batchnum"><INPUT TYPE="submit" VALUE="Download"></FORM><BR>!; @@ -168,6 +172,7 @@ if ( $pay_batch ) { qq!<OPTION VALUE="PAP">264 byte results for TD Canada Trust PAP Batch</OPTION>!. qq!<OPTION VALUE="BoM">Bank of Montreal ECA results</OPTION>!. qq!<OPTION VALUE="ach-spiritone">Spiritone ACH batch</OPTION>!. + qq!<OPTION VALUE="paymentech">Chase Paymentech</OPTION>!. qq!</SELECT><BR>!; } $html_init .= qq!<INPUT TYPE="hidden" NAME="batchnum" VALUE="$batchnum">!; diff --git a/httemplate/search/cust_pay_void.html b/httemplate/search/cust_pay_void.html new file mode 100755 index 0000000..431bb2c --- /dev/null +++ b/httemplate/search/cust_pay_void.html @@ -0,0 +1,13 @@ +<% include( 'elements/cust_pay_or_refund.html', + 'thing' => 'pay_void', + 'amount_field' => 'paid', + 'name_singular' => 'voided payment', + 'name_verb' => 'voided', # 'paid', + 'disable_by' => 1, #showing original not voiding otaker + 'addl_header' => [ 'Void Date', ], # 'Void Reason' ], + 'addl_fields' => [ + sub { time2str('%b %d %Y', shift->void_date ) }, + #'reason', + ], + ) +%> diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index bd4a946..f03bbc2 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -10,6 +10,8 @@ 'Package', 'Class', 'Status', + 'Setup', + 'Base Recur', 'Freq.', 'Setup', 'Last bill', @@ -33,6 +35,15 @@ }, 'classname', sub { ucfirst(shift->status); }, + sub { sprintf( $money_char.'%.2f', + shift->part_pkg->option('setup_fee'), + ); + }, + sub { my $c = shift; + sprintf( $money_char.'%.2f', + $c->part_pkg->base_recur($c) + ); + }, sub { #shift->part_pkg->freq_pretty; #my $part_pkg = $part_pkg{shift->pkgpart}; @@ -99,13 +110,15 @@ '', '', '', + '', + '', FS::UI::Web::cust_colors(), '', ], - 'style' => [ '', '', '', '', 'b', '', '', '', '', '', '', '', '', '', + 'style' => [ '', '', '', '', 'b', '', '', '', '', '', '', '', '', '', '', '', FS::UI::Web::cust_styles() ], 'size' => [ '', '', '', '', '-1' ], - 'align' => 'rrlcclrrrrrrrl'. FS::UI::Web::cust_aligns(). 'r', + 'align' => 'rrlccrrlrrrrrrrl'. FS::UI::Web::cust_aligns(). 'r', 'links' => [ $link, $link, @@ -121,6 +134,8 @@ '', '', '', + '', + '', ( map { $_ ne 'Cust. Status' ? $clink : '' } FS::UI::Web::cust_header( $cgi->param('cust_fields') @@ -133,19 +148,36 @@ %> <%init> +my $curuser = $FS::CurrentUser::CurrentUser; + die "access denied" - unless $FS::CurrentUser::CurrentUser->access_right('List packages'); + unless $curuser->access_right('List packages'); + +my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; # my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {}); - my %search_hash = (); +my %search_hash = (); + +#some false laziness w/misc/bulk_change_pkg.cgi - $search_hash{'query'} = $cgi->keywords; +$search_hash{'query'} = $cgi->keywords; - for my $param (qw(agentnum magic status classnum pkgpart)) { - $search_hash{$param} = $cgi->param($param) - if $cgi->param($param); - } +for (qw( agentnum magic status classnum custom )) { + $search_hash{$_} = $cgi->param($_) if $cgi->param($_); +} + +$search_hash{'pkgpart'} = [ $cgi->param('pkgpart') ]; + +for my $param ( qw(censustract) ) { + $search_hash{$param} = $cgi->param($param) || '' + if ( grep { /$param/ } $cgi->param ); +} + +my @report_option = $cgi->param('report_option') + if $cgi->param('report_option'); +$search_hash{report_option} = join(',', @report_option) if @report_option; ### # parse dates @@ -175,8 +207,17 @@ foreach my $field (qw( setup last_bill bill adjourn susp expire cancel )) { my $sql_query = FS::cust_pkg->search_sql(\%search_hash); my $count_query = delete($sql_query->{'count_query'}); +my $show = $curuser->default_customer_view =~ /^(jumbo|packages)$/ + ? '' + : ';show=packages'; + my $link = sub { - [ "${p}view/cust_main.cgi?".shift->custnum.'#cust_pkg', 'pkgnum' ]; + my $self = shift; + my $frag = 'cust_pkg'. $self->pkgnum; #hack for IE ignoring real #fragment + [ "${p}view/cust_main.cgi?custnum=".$self->custnum. + "$show;fragment=$frag#cust_pkg", + 'pkgnum' + ]; }; my $clink = sub { diff --git a/httemplate/search/cust_tax_adjustment.html b/httemplate/search/cust_tax_adjustment.html new file mode 100644 index 0000000..9254765 --- /dev/null +++ b/httemplate/search/cust_tax_adjustment.html @@ -0,0 +1,54 @@ +<% include( 'elements/search.html', + 'title' => $title, + 'name_singular' => 'tax adjustment', + 'query' => $query, + 'count_query' => $count_query, + 'header' => [ 'Tax', 'Amount', 'Comment', 'Invoice' ], + 'fields' => [ 'taxname', + sub { $money_char. shift->amount }, + 'comment', + sub { my $l = shift->cust_bill_pkg; + $l ? '#'.$l->invnum : ''; + }, + ], + 'links' => [ '', '', '', $ilink ], + ) +%> + +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Add customer tax adjustment'); + +my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; + +my $count_query = 'SELECT COUNT(*) FROM cust_tax_adjustment'; + +my $hashref = {}; + +my $custnum = ''; +my $cust_main = ''; +if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { + $custnum = $1; + $cust_main = qsearchs('cust_main', { 'custnum' => $custnum } ); + $hashref->{'custnum'} = $custnum; + $count_query .= " WHERE custnum = $custnum "; +} + +my $title = 'Tax adjustments'; +$title .= ' for '. $cust_main->name if $cust_main; + +my $query = { 'table' => 'cust_tax_adjustment', + 'hashref' => $hashref, + }; + +my $ilink = [ $p.'view/cust_bill.cgi?', sub { my $l = shift->cust_bill_pkg; + $l ? $l->invnum : 'EXCEPTION'; + } + ]; + +#XXX would be nice to list customer fields on the report too, if we ever need +# to link to here without a custnum (i'm sure we will, eventually...) + +</%init> diff --git a/httemplate/search/elements/cust_main_dayranges.html b/httemplate/search/elements/cust_main_dayranges.html new file mode 100644 index 0000000..cc01492 --- /dev/null +++ b/httemplate/search/elements/cust_main_dayranges.html @@ -0,0 +1,219 @@ +<%doc> + +Example: + + include( 'elements/cust_main_dayranges.html', + 'title' => 'Accounts Receivable Aging Summary', + 'range_sub' => $mysub, + ) + + my $mysub = sub { + my( $start, $end ) = @_; + + "SQL EXPRESSION BASED ON $start AND $end"; + }; + +</%doc> +<% include( 'search.html', + 'name' => 'customers', + 'query' => $sql_query, + 'count_query' => $count_sql, + 'header' => [ + FS::UI::Web::cust_header(), + '0-30', + '30-60', + '60-90', + '90+', + 'Total', + ], + 'footer' => [ + 'Total', + ( map '', + ( 1 .. + scalar(FS::UI::Web::cust_header()-1) + ) + ), + sprintf( $money_char.'%.2f', + $row->{'rangecol_0_30'} ), + sprintf( $money_char.'%.2f', + $row->{'rangecol_30_60'} ), + sprintf( $money_char.'%.2f', + $row->{'rangecol_60_90'} ), + sprintf( $money_char.'%.2f', + $row->{'rangecol_90_0'} ), + sprintf( '<b>'. $money_char.'%.2f'. '</b>', + $row->{'rangecol_0_0'} ), + ], + 'fields' => [ + \&FS::UI::Web::cust_fields, + format_rangecol('0_30'), + format_rangecol('30_60'), + format_rangecol('60_90'), + format_rangecol('90_0'), + format_rangecol('0_0'), + ], + 'links' => [ + ( map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header() + ), + '', + '', + '', + '', + '', + ], + #'align' => 'rlccrrrrr', + 'align' => FS::UI::Web::cust_aligns(). 'rrrrr', + #'size' => [ '', '', '-1', '-1', '', '', '', '', '', ], + #'style' => [ '', '', 'b', 'b', '', '', '', '', 'b', ], + 'size' => [ ( map '', FS::UI::Web::cust_header() ), + #'-1', '', '', '', '', '', ], + '', '', '', '', '', ], + 'style' => [ FS::UI::Web::cust_styles(), + #'b', '', '', '', '', 'b', ], + '', '', '', '', 'b', ], + 'color' => [ + FS::UI::Web::cust_colors(), + '', + '', + '', + '', + '', + ], + %opt, + ) +%> +<%init> + +my %opt = @_; + +#actually need to auto-generate other things too for a passed-in ranges to work +my $ranges = $opt{'ranges'} ? delete($opt{'ranges'}) : [ + [ 0, 30 ], + [ 30, 60 ], + [ 60, 90 ], + [ 90, 0 ], + [ 0, 0 ], +]; + +my $range_sub = delete($opt{'range_sub'}); #or die + +#my $range_cols = join(',', map &{$range_sub}( @$_ ), @ranges ); +my $range_cols = join(',', map call_range_sub($range_sub, @$_ ), @$ranges ); + +my $select_count_pkgs = FS::cust_main->select_count_pkgs_sql; + +my $active_sql = FS::cust_pkg->active_sql; +my $inactive_sql = FS::cust_pkg->inactive_sql; +my $suspended_sql = FS::cust_pkg->suspended_sql; +my $cancelled_sql = FS::cust_pkg->cancelled_sql; + +my $packages_cols = <<END; + ( $select_count_pkgs ) AS num_pkgs_sql, + ( $select_count_pkgs AND $active_sql ) AS active_pkgs, + ( $select_count_pkgs AND $inactive_sql ) AS inactive_pkgs, + ( $select_count_pkgs AND $suspended_sql ) AS suspended_pkgs, + ( $select_count_pkgs AND $cancelled_sql ) AS cancelled_pkgs +END + +my @where = (); + +unless ( $cgi->param('all_customers') ) { + + my $days = 0; + if ( $cgi->param('days') =~ /^\s*(\d+)\s*$/ ) { + $days = $1; + } + + push @where, + call_range_sub($range_sub, $days, 0, 'no_as'=>1). ' > 0'; # != 0'; +} + +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + my $agentnum = $1; + push @where, "agentnum = $agentnum"; +} + +#status (false laziness w/cust_main::search_sql + +#prospect active inactive suspended cancelled +if ( grep { $cgi->param('status') eq $_ } FS::cust_main->statuses() ) { + my $method = $cgi->param('status'). '_sql'; + #push @where, $class->$method(); + push @where, FS::cust_main->$method(); +} + +#here is the agent virtualization +push @where, $FS::CurrentUser::CurrentUser->agentnums_sql; + +my $where = join(' AND ', @where); +$where = "WHERE $where" if $where; + +my $count_sql = "select count(*) from cust_main $where"; + +my $sql_query = { + 'table' => 'cust_main', + 'hashref' => {}, + 'select' => join(',', + #'cust_main.*', + 'custnum', + $range_cols, + $packages_cols, + FS::UI::Web::cust_sql_fields(), + ), + 'extra_sql' => $where, + 'order_by' => "order by coalesce(lower(company), ''), lower(last)", +}; + +my $total_sql = + "SELECT ". + join(',', map call_range_sub( $range_sub, @$_, 'sum'=>1 ), @$ranges). + " FROM cust_main $where"; + +my $total_sth = dbh->prepare($total_sql) or die dbh->errstr; +$total_sth->execute or die "error executing $total_sql: ". $total_sth->errstr; +my $row = $total_sth->fetchrow_hashref(); + +my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; + +</%init> +<%once> + +my $conf = new FS::Conf; + +my $money_char = $conf->config('money_char') || '$'; + +#Example: +# +# my $balance = balance( +# $start, $end, +# 'no_as' => 1, #set to true when using in a WHERE clause (supress AS clause) +# #or 0 / omit when using in a SELECT clause as a column +# # ("AS balance_$start_$end") +# 'sum' => 1, #set to true to get a SUM() of the values, for totals +# +# #obsolete? options for totals (passed to cust_main::balance_date_sql) +# 'total' => 1, #set to true to remove all customer comparison clauses +# 'join' => $join, #JOIN clause +# 'where' => \@where, #WHERE clause hashref (elements "AND"ed together) +# ) + +sub call_range_sub { + my($range_sub, $start, $end, %opt) = @_; + + my $as = $opt{'no_as'} ? '' : " AS rangecol_${start}_$end"; + + my $sql = &{$range_sub}( $start, $end ); #%opt? + + $sql = "SUM($sql)" if $opt{'sum'}; + + $sql.$as; + +} + +sub format_rangecol { #closures help alot + my $range = shift; + sub { sprintf( $money_char.'%.2f', shift->get("rangecol_$range") ) }; +} + +</%once> diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html index add8427..874bd8a 100755 --- a/httemplate/search/elements/cust_pay_or_refund.html +++ b/httemplate/search/elements/cust_pay_or_refund.html @@ -117,7 +117,6 @@ if ( $cgi->param('magic') ) { my $orderby; if ( $cgi->param('magic') eq '_date' ) { - if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { push @search, "agentnum = $1"; # $search{'agentnum'} = $1; my $agent = qsearchs('agent', { 'agentnum' => $1 } ); @@ -219,6 +218,13 @@ if ( $cgi->param('magic') ) { push @search, "_date >= $beginning ", "_date <= $ending"; + if ( $thing eq 'pay_void' ) { + my($v_beginning, $v_ending) = + FS::UI::Web::parse_beginning_ending($cgi, 'void'); + push @search, "void_date >= $v_beginning ", + "void_date <= $v_ending"; + } + push @search, FS::UI::Web::parse_lt_gt($cgi, $amount_field ); $orderby = '_date'; @@ -288,7 +294,9 @@ if ( ( $curuser->access_right('View invoices') #XXX for now && ! $opt{'disable_link'} ) { - $link = [ "${p}view/cust_$thing.html?${thing}num=", $thing.'num' ] + 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 { diff --git a/httemplate/search/elements/search-csv.html b/httemplate/search/elements/search-csv.html new file mode 100644 index 0000000..cd4ea63 --- /dev/null +++ b/httemplate/search/elements/search-csv.html @@ -0,0 +1,48 @@ +% $csv->combine(@$header); #or die $csv->status; +% +<% $csv->string %>\ +% +% foreach my $row ( @$rows ) { +% +% if ( $opt{'fields'} ) { +% +% my @line = (); +% +% foreach my $field ( @{$opt{'fields'}} ) { +% if ( ref($field) eq 'CODE' ) { +% push @line, map { +% ref($_) eq 'ARRAY' +% ? '(N/A)' #unimplemented +% : $_; +% } +% &{$field}($row); +% } else { +% push @line, $row->$field(); +% } +% } +% +% $csv->combine(@line); #or die $csv->status; +% +% } else { +% $csv->combine(@$row); #or die $csv->status; +% } +% +% +<% $csv->string %>\ +% +% } +<%init> + +my %args = @_; +my $header = $args{'header'}; +my $rows = $args{'rows'}; +my %opt = %{ $args{'opt'} }; + +#http_header('Content-Type' => 'text/comma-separated-values' ); #IE chokes +http_header('Content-Type' => 'text/plain' ); + +my $csv = new Text::CSV_XS { 'always_quote' => 1, + 'eol' => "\n", #"\015\012", #"\012" + }; + +</%init> diff --git a/httemplate/search/elements/search-html.html b/httemplate/search/elements/search-html.html new file mode 100644 index 0000000..297774d --- /dev/null +++ b/httemplate/search/elements/search-html.html @@ -0,0 +1,454 @@ +% +% if ( exists($opt{'redirect'}) && $opt{'redirect'} +% && scalar(@$rows) == 1 && $total == 1 +% && $type ne 'html-print' +% ) { +% my $redirect = $opt{'redirect'}; +% $redirect = &{$redirect}($rows->[0], $cgi) if ref($redirect) eq 'CODE'; +% my( $url, $method ) = @$redirect; +% redirect( $url. $rows->[0]->$method() ); +% } elsif ( exists($opt{'redirect_empty'}) && ! scalar(@$rows) && $total == 0 +% && $type ne 'html-print' +% && $opt{'redirect_empty'} +% && ( ref($opt{'redirect_empty'}) ne 'CODE' +% || &{$opt{'redirect_empty'}}($cgi) ) +% ) { +% my $redirect = $opt{'redirect_empty'}; +% $redirect = &{$redirect}($cgi) if ref($redirect) eq 'CODE'; +% redirect( $redirect ); +% } else { +% if ( $opt{'name_singular'} ) { +% $opt{'name'} = PL($opt{'name_singular'}); +% } +% ( my $xlsname = $opt{'name'} ) =~ s/\W//g; +% if ( $total == 1 ) { +% if ( $opt{'name_singular'} ) { +% $opt{'name'} = $opt{'name_singular'} +% } else { +% #$opt{'name'} =~ s/s$// if $total == 1; +% $opt{'name'} =~ s/((s)e)?s$/$2/ if $total == 1; +% } +% } +% +% if ( $type eq 'html-print' ) { + + <% include( '/elements/header-popup.html', $opt{'title'} ) %> + +% } elsif ( $type eq 'select' ) { + + <% include( '/elements/header-popup.html', $opt{'title'} ) %> + <% defined($opt{'html_init'}) + ? ( ref($opt{'html_init'}) + ? &{$opt{'html_init'}}() + : $opt{'html_init'} + ) + : '' + %> + +% } else { +% +% my @menubar = (); +% if ( $opt{'menubar'} ) { +% @menubar = @{ $opt{'menubar'} }; +% #} else { +% # @menubar = ( 'Main menu' => $p ); +% } + + <% include( '/elements/header.html', $opt{'title'}, + include( '/elements/menubar.html', @menubar ) + ) + %> + + <% defined($opt{'html_init'}) + ? ( ref($opt{'html_init'}) + ? &{$opt{'html_init'}}() + : $opt{'html_init'} + ) + : '' + %> + +% } + +% unless ( $total ) { +% unless ( $opt{'disable_nonefound'} ) { + No matching <% $opt{'name'} %> found.<BR> +% } +% } +% +% if ( $total || $opt{'disableable'} ) { #hmm... and there *are* ones to show?? + + <TABLE> + <TR> + + <TD VALIGN="bottom"> + + <FORM> + +% if (! $opt{'disable_total'}) { + <% $total %> total <% $opt{'name'} %> +% } + +% if ( $confmax && $total > $confmax +% && ! $opt{'disable_maxselect'} +% && $type ne 'html-print' ) +% { +% $cgi->delete('maxrecords'); +% $cgi->param('_dummy', 1); + + ( show <SELECT NAME="maxrecords" onChange="window.location = '<% $cgi->self_url %>;maxrecords=' + this.options[this.selectedIndex].value;"> + +% foreach my $max ( map { $_ * $confmax } qw( 1 5 10 25 ) ) { + <OPTION VALUE="<% $max %>" <% ( $maxrecords == $max ) ? 'SELECTED' : '' %>><% $max %></OPTION> +% } + + </SELECT> per page ) + +% $cgi->param('maxrecords', $maxrecords); +% } + +% if ( defined($opt{'html_posttotal'}) && $type ne 'html-print' ) { + <% ref($opt{'html_posttotal'}) + ? &{$opt{'html_posttotal'}}() + : $opt{'html_posttotal'} + %> +% } + <BR> + +% if ( $opt{'count_addl'} ) { +% my $n=0; +% foreach my $count ( @{$opt{'count_addl'}} ) { +% my $data = $count_arrayref->[++$n]; +% if ( ref($count) ) { + <% &{ $count }( $data ) %> +% } else { + <% sprintf( $count, $data ) %><BR> +% } +% } +% } + </FORM> + + </TD> + +% unless ( $opt{'disable_download'} || $type eq 'html-print' ) { + + <TD ALIGN="right"> + + Download full results<BR> + +% $cgi->param('_type', "$xlsname.xls" ); + as <A HREF="<% $cgi->self_url %>">Excel spreadsheet</A><BR> + +% $cgi->param('_type', 'csv'); + as <A HREF="<% $cgi->self_url %>">CSV file</A><BR> + +% $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" ); +% } + + </TR> + <TR> + <TD COLSPAN=2> + +% my $pager = ''; +% unless ( $type eq 'html_print' ) { + + <% $pager = include( '/elements/pager.html', + 'offset' => $offset, + 'num_rows' => scalar(@$rows), + 'total' => $total, + 'maxrecords' => $maxrecords, + ) + %> + + <% defined($opt{'html_form'}) + ? ( ref($opt{'html_form'}) + ? &{$opt{'html_form'}}() + : $opt{'html_form'} + ) + : '' + %> + +% } + + <% include('/elements/table-grid.html') %> + + <TR> +% my $h2 = 0; +% foreach my $header ( @{ $opt{header} } ) { +% my $label = ref($header) ? $header->{label} : $header; +% my $rowspan = 1; +% my $style = ''; +% if ( $opt{header2} ) { +% if ( !length($opt{header2}->[$h2]) ) { +% $rowspan = 2; +% splice @{ $opt{header2} }, $h2, 1; +% } else { +% $h2++; +% $style = 'STYLE="border-bottom: none"' +% } +% } + <TH CLASS = "grid" + BGCOLOR = "#cccccc" + ROWSPAN = "<% $rowspan %>" + <% $style %> + + > + <% $label %> + </TH> +% } + </TR> + +% if ( $opt{header2} ) { + <TR> +% foreach my $header ( @{ $opt{header2} } ) { +% my $label = ref($header) ? $header->{label} : $header; + <TH CLASS="grid" BGCOLOR="#cccccc"> + <FONT SIZE="-1"><% $label %></FONT> + </TH> +% } + </TR> +% } + +% my $bgcolor1 = '#eeeeee'; +% my $bgcolor2 = '#ffffff'; +% my $bgcolor; +% +% foreach my $row ( @$rows ) { +% +% if ( $bgcolor eq $bgcolor1 ) { +% $bgcolor = $bgcolor2; +% } else { +% $bgcolor = $bgcolor1; +% } + + <TR> + +% if ( $opt{'fields'} ) { +% +% my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; +% my $onclicks = $opt{'link_onclicks'} ? [ @{$opt{'link_onclicks'}} ] : []; +% my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : ''; +% my $colors = $opt{'color'} ? [ @{$opt{'color'}} ] : []; +% my $sizes = $opt{'size'} ? [ @{$opt{'size'}} ] : []; +% my $styles = $opt{'style'} ? [ @{$opt{'style'}} ] : []; +% my $cstyles = $opt{'cell_style'} ? [ @{$opt{'cell_style'}} ] : []; +% +% foreach my $field ( +% +% map { +% if ( ref($_) eq 'ARRAY' ) { +% +% my $tableref = $_; +% +% '<TABLE CLASS="inv" CELLSPACING=0 CELLPADDING=0 WIDTH="100%">'. +% +% join('', map { +% +% my $rowref = $_; +% +% '<tr>'. +% +% join('', map { +% +% my $e = $_; +% +% '<TD '. +% join(' ', map { +% uc($_).'="'. $e->{$_}. '"'; +% } +% grep exists($e->{$_}), +% qw( align bgcolor colspan rowspan +% style valign width ) +% ). +% '>'. +% +% ( $e->{'link'} +% ? '<A HREF="'. $e->{'link'}. '">' +% : '' +% ). +% ( $e->{'size'} +% ? '<FONT SIZE="'.uc($e->{'size'}).'">' +% : '' +% ). +% ( $e->{'data_style'} +% ? '<'. uc($e->{'data_style'}). '>' +% : '' +% ). +% $e->{'data'}. +% ( $e->{'data_style'} +% ? '</'. uc($e->{'data_style'}). '>' +% : '' +% ). +% ( $e->{'size'} ? '</FONT>' : '' ). +% ( $e->{'link'} ? '</A>' : '' ). +% '</td>'; +% +% } @$rowref ). +% +% '</tr>'; +% } @$tableref ). +% +% '</table>'; +% +% } else { +% $_; +% } +% } +% +% map { +% if ( ref($_) eq 'CODE' ) { +% &{$_}($row); +% } else { +% $row->$_(); +% } +% } +% @{$opt{'fields'}} +% +% ) { +% +% my $class = ( $field =~ /^<TABLE/i ) ? 'inv' : 'grid'; +% +% my $align = $aligns ? shift @$aligns : ''; +% $align = " ALIGN=$align" if $align; +% +% my $a = ''; +% if ( $links ) { +% my $link = shift @$links; +% my $onclick = shift @$onclicks; +% +% if ( ! $opt{'agent_virt'} +% || ( $null_link && ! $row->agentnum ) +% || grep { $row->agentnum == $_ } +% @link_agentnums +% ) { +% +% $link = &{$link}($row) +% if ref($link) eq 'CODE'; +% +% $onclick = &{$onclick}($row) +% if ref($onclick) eq 'CODE'; +% $onclick = qq( onClick="$onclick") if $onclick; +% +% if ( $link ) { +% my( $url, $method ) = @{$link}; +% if ( ref($method) eq 'CODE' ) { +% $a = $url. &{$method}($row); +% } else { +% $a = $url. $row->$method(); +% } +% $a = qq(<A HREF="$a"$onclick>); +% } +% +% } +% +% } +% +% my $font = ''; +% my $color = shift @$colors; +% $color = &{$color}($row) if ref($color) eq 'CODE'; +% my $size = shift @$sizes; +% $size = &{$size}($row) if ref($size) eq 'CODE'; +% if ( $color || $size ) { +% $font = '<FONT '. +% ( $color ? "COLOR=#$color " : '' ). +% ( $size ? qq(SIZE="$size" ) : '' ). +% '>'; +% } +% +% my($s, $es) = ( '', '' ); +% my $style = shift @$styles; +% $style = &{$style}($row) if ref($style) eq 'CODE'; +% if ( $style ) { +% $s = join( '', map "<$_>", split('', $style) ); +% $es = join( '', map "</$_>", split('', $style) ); +% } +% +% my $cstyle = shift @$cstyles; +% $cstyle = &{$cstyle}($row) if ref($cstyle) eq 'CODE'; +% $cstyle = qq(STYLE="$cstyle") +% if $cstyle; + + <TD CLASS="<% $class %>" BGCOLOR="<% $bgcolor %>" <% $align %> <% $cstyle %>><% $font %><% $a %><% $s %><% $field %><% $es %><% $a ? '</A>' : '' %><% $font ? '</FONT>' : '' %></TD> + +% } +% +% } else { +% +% foreach ( @$row ) { + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $_ %></TD> +% } +% +% } + + </TR> + +% } + +% if ( $opt{'footer'} ) { + + <TR> + +% foreach my $footer ( @{ $opt{'footer'} } ) { + <TD CLASS="grid" BGCOLOR="#dddddd" STYLE="border-top: dashed 1px black;"><i><% $footer %></i></TD> +% } + + </TR> +% } + + </TABLE> + + <% $pager %> + + </TD> + </TR> + </TABLE> +% } + +% if ( $type eq 'html-print' ) { + + </BODY></HTML> + +% } else { + + <% defined($opt{'html_foot'}) + ? ( ref($opt{'html_foot'}) + ? &{$opt{'html_foot'}}() + : $opt{'html_foot'} + ) + : '' + %> + + <% include( '/elements/footer.html' ) %> + +% } + +% } +<%init> + +my %args = @_; +my $type = $args{'type'}; +my $header = $args{'header'}; +my $rows = $args{'rows'}; +my @link_agentnums = @{ $args{'link_agentnums'} }; +my $null_link = $args{'null_link'}; +my $confmax = $args{'confmax'}; +my $maxrecords = $args{'maxrecords'}; +my $offset = $args{'offset'}; +my %opt = %{ $args{'opt'} }; + +my $count_sth = dbh->prepare($opt{'count_query'}) + or die "Error preparing $opt{'count_query'}: ". dbh->errstr; +$count_sth->execute + or die "Error executing $opt{'count_query'}: ". $count_sth->errstr; +my $count_arrayref = $count_sth->fetchrow_arrayref; +my $total = $count_arrayref->[0]; + +</%init> diff --git a/httemplate/search/elements/search-xls.html b/httemplate/search/elements/search-xls.html new file mode 100644 index 0000000..8a05e47 --- /dev/null +++ b/httemplate/search/elements/search-xls.html @@ -0,0 +1,83 @@ +<% $data %> +<%init> + +my %args = @_; +my $type = $args{'type'}; +my $header = $args{'header'}; +my $rows = $args{'rows'}; +my %opt = %{ $args{'opt'} }; + +#http_header('Content-Type' => 'application/excel' ); #eww +#http_header('Content-Type' => 'application/msexcel' ); #alas +#http_header('Content-Type' => 'application/x-msexcel' ); #? + +#http://support.microsoft.com/kb/199841 +http_header('Content-Type' => 'application/vnd.ms-excel' ); + +#http://support.microsoft.com/kb/812935 +#http://support.microsoft.com/kb/323308 +$HTML::Mason::Commands::r->headers_out->{'Cache-control'} = 'max-age=0'; + +my $data = ''; +my $XLS = new IO::Scalar \$data; +my $workbook = Spreadsheet::WriteExcel->new($XLS) + or die "Error opening .xls file: $!"; + +my $worksheet = $workbook->add_worksheet(substr($opt{'title'},0,31)); + +$worksheet->protect(); + +my($r,$c) = (0,0); + +my $header_format = $workbook->add_format( + bold => 1, + locked => 1, + bg_color => 55, #22, + bottom => 3, +); + +$worksheet->write($r, $c++, $_, $header_format ) foreach @$header; + +foreach my $row ( @$rows ) { + $r++; + $c = 0; + + if ( $opt{'fields'} ) { + + #my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; + #my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : ''; + #could also translate color, size, style into xls equivalents? + my $formats = $opt{'xls_format'} ? [ @{$opt{'xls_format'}} ] : []; + + foreach my $field ( @{$opt{'fields'}} ) { + + my $format = shift @$formats; + $format = &{$format}($row) if ref($format) eq 'CODE'; + $format ||= {}; + my $xls_format = $workbook->add_format(locked=>0, %$format); + + if ( ref($field) eq 'CODE' ) { + foreach my $value ( &{$field}($row) ) { + if ( ref($value) eq 'ARRAY' ) { + $worksheet->write($r, $c++, '(N/A)' ); #unimplemented + } else { + $worksheet->write($r, $c++, $value, $xls_format ); + } + } + } else { + $worksheet->write($r, $c++, $row->$field(), $xls_format ); + } + } + + } else { + my $xls_format = $workbook->add_format(locked=>0); + $worksheet->write($r, $c++, $_, $xls_format ) foreach @$row; + } + +} + +$workbook->close();# or die "Error creating .xls file: $!"; + +http_header('Content-Length' => length($data) ); + +</%init> diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index 8835f8c..4bfe8b0 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -16,7 +16,8 @@ Example: # (deprecated, will be singularlized # simplisticly) - #literal SQL query string (deprecated?) or qsearch hashref + #literal SQL query string (deprecated?) or qsearch hashref or arrayref + #of qsearch hashrefs for a union of qsearches 'query' => { 'table' => 'tablename', #everything else is optional... @@ -148,570 +149,42 @@ Example: 'align' => 'lrc.', #listrefs of ( scalars or coderefs ) - #currently only HTML, maybe eventually Excel too + # currently only HTML, maybe eventually Excel too 'color' => [], 'size' => [], 'style' => [], #<B> or <I>, etc. 'cell_style' => [], #STYLE= attribute of TR, very HTML-specific... + + # Excel-specific listref of ( hashrefs or coderefs ) + # each hashref: http://search.cpan.org/dist/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel.pm#Format_methods_and_Format_properties + 'xls_format' => => [], ); </%doc> % if ( $type eq 'csv' ) { % -% #http_header('Content-Type' => 'text/comma-separated-values' ); #IE chokes -% http_header('Content-Type' => 'text/plain' ); -% -% my $csv = new Text::CSV_XS { 'always_quote' => 1, -% 'eol' => "\n", #"\015\012", #"\012" -% }; -% -% $csv->combine(@$header); #or die $csv->status; -% -<% $csv->string %> -% -% -% foreach my $row ( @$rows ) { -% -% if ( $opt{'fields'} ) { -% -% my @line = (); -% -% foreach my $field ( @{$opt{'fields'}} ) { -% if ( ref($field) eq 'CODE' ) { -% push @line, map { -% ref($_) eq 'ARRAY' -% ? '(N/A)' #unimplemented -% : $_; -% } -% &{$field}($row); -% } else { -% push @line, $row->$field(); -% } -% } -% -% $csv->combine(@line); #or die $csv->status; -% -% } else { -% $csv->combine(@$row); #or die $csv->status; -% } -% -% -<% $csv->string %> -% -% -% } +<% include('search-csv.html', header=>$header, rows=>$rows, opt=>\%opt ) %> % % #} elsif ( $type eq 'excel' ) { % } elsif ( $type =~ /\.xls$/ ) { % -% #http_header('Content-Type' => 'application/excel' ); #eww -% #http_header('Content-Type' => 'application/msexcel' ); #alas -% #http_header('Content-Type' => 'application/x-msexcel' ); #? -% -% #http://support.microsoft.com/kb/199841 -% http_header('Content-Type' => 'application/vnd.ms-excel' ); -% -% #http://support.microsoft.com/kb/812935 -% #http://support.microsoft.com/kb/323308 -% $HTML::Mason::Commands::r->headers_out->{'Cache-control'} = 'max-age=0'; -% -% my $data = ''; -% my $XLS = new IO::Scalar \$data; -% my $workbook = Spreadsheet::WriteExcel->new($XLS) -% or die "Error opening .xls file: $!"; -% -% my $worksheet = $workbook->add_worksheet(substr($opt{'title'},0,31)); -% -% my($r,$c) = (0,0); -% -% $worksheet->write($r, $c++, $_) foreach @$header; -% -% foreach my $row ( @$rows ) { -% $r++; -% $c = 0; -% -% if ( $opt{'fields'} ) { -% -% #my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; -% #my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : ''; -% -% foreach my $field ( @{$opt{'fields'}} ) { -% #my $align = $aligns ? shift @$aligns : ''; -% #$align = " ALIGN=$align" if $align; -% #my $a = ''; -% #if ( $links ) { -% # my $link = shift @$links; -% # $link = &{$link}($row) if ref($link) eq 'CODE'; -% # if ( $link ) { -% # my( $url, $method ) = @{$link}; -% # if ( ref($method) eq 'CODE' ) { -% # $a = $url. &{$method}($row); -% # } else { -% # $a = $url. $row->$method(); -% # } -% # $a = qq(<A HREF="$a">); -% # } -% #} -% if ( ref($field) eq 'CODE' ) { -% foreach my $value ( &{$field}($row) ) { -% if ( ref($value) eq 'ARRAY' ) { -% $worksheet->write($r, $c++, '(N/A)' ); #unimplemented -% } else { -% $worksheet->write($r, $c++, $value ); -% } -% } -% } else { -% $worksheet->write($r, $c++, $row->$field() ); -% } -% } -% -% } else { -% $worksheet->write($r, $c++, $_) foreach @$row; -% } -% -% } -% -% $workbook->close();# or die "Error creating .xls file: $!"; -% -% http_header('Content-Length' => length($data) ); -% -<% $data %> -% +<% include('search-xls.html', header=>$header, rows=>$rows, opt=>\%opt ) %> % % } else { # regular HTML % -% if ( exists($opt{'redirect'}) && scalar(@$rows) == 1 && $total == 1 -% && $type ne 'html-print' -% ) { -% my $redirect = $opt{'redirect'}; -% $redirect = &{$redirect}($rows->[0], $cgi) if ref($redirect) eq 'CODE'; -% my( $url, $method ) = @$redirect; -% redirect( $url. $rows->[0]->$method() ); -% } elsif ( exists($opt{'redirect_empty'}) && ! scalar(@$rows) && $total == 0 -% && $type ne 'html-print' -% && $opt{'redirect_empty'} -% && ( ref($opt{'redirect_empty'}) ne 'CODE' -% || &{$opt{'redirect_empty'}}($cgi) ) -% ) { -% my $redirect = $opt{'redirect_empty'}; -% $redirect = &{$redirect}($cgi) if ref($redirect) eq 'CODE'; -% redirect( $redirect ); -% } else { -% if ( $opt{'name_singular'} ) { -% $opt{'name'} = PL($opt{'name_singular'}); -% } -% ( my $xlsname = $opt{'name'} ) =~ s/\W//g; -% if ( $total == 1 ) { -% if ( $opt{'name_singular'} ) { -% $opt{'name'} = $opt{'name_singular'} -% } else { -% #$opt{'name'} =~ s/s$// if $total == 1; -% $opt{'name'} =~ s/((s)e)?s$/$2/ if $total == 1; -% } -% } -% -% if ( $type eq 'html-print' ) { - - <% include( '/elements/header-popup.html', $opt{'title'} ) %> - -% } elsif ( $type eq 'select' ) { - - <% include( '/elements/header-popup.html', $opt{'title'} ) %> - <% defined($opt{'html_init'}) - ? ( ref($opt{'html_init'}) - ? &{$opt{'html_init'}}() - : $opt{'html_init'} - ) - : '' - %> - -% } else { -% -% my @menubar = (); -% if ( $opt{'menubar'} ) { -% @menubar = @{ $opt{'menubar'} }; -% #} else { -% # @menubar = ( 'Main menu' => $p ); -% } - - <% include( '/elements/header.html', $opt{'title'}, - include( '/elements/menubar.html', @menubar ) - ) - %> - - <% defined($opt{'html_init'}) - ? ( ref($opt{'html_init'}) - ? &{$opt{'html_init'}}() - : $opt{'html_init'} - ) - : '' - %> - -% } - -% unless ( $total ) { -% unless ( $opt{'disable_nonefound'} ) { - No matching <% $opt{'name'} %> found.<BR> -% } -% } -% -% if ( $total || $opt{'disableable'} ) { #hmm... and there *are* ones to show?? - - <TABLE> - <TR> - - <TD VALIGN="bottom"> - - <FORM> - -% if (! $opt{'disable_total'}) { - <% $total %> total <% $opt{'name'} %> -% } - -% if ( $confmax && $total > $confmax -% && ! $opt{'disable_maxselect'} -% && $type ne 'html-print' ) -% { -% $cgi->delete('maxrecords'); -% $cgi->param('_dummy', 1); - - ( show <SELECT NAME="maxrecords" onChange="window.location = '<% $cgi->self_url %>;maxrecords=' + this.options[this.selectedIndex].value;"> - -% foreach my $max ( map { $_ * $confmax } qw( 1 5 10 25 ) ) { - <OPTION VALUE="<% $max %>" <% ( $maxrecords == $max ) ? 'SELECTED' : '' %>><% $max %></OPTION> -% } - - </SELECT> per page ) - -% $cgi->param('maxrecords', $maxrecords); -% } - -% if ( defined($opt{'html_posttotal'}) && $type ne 'html-print' ) { - <% ref($opt{'html_posttotal'}) - ? &{$opt{'html_posttotal'}}() - : $opt{'html_posttotal'} - %> -% } - <BR> - -% if ( $opt{'count_addl'} ) { -% my $n=0; -% foreach my $count ( @{$opt{'count_addl'}} ) { -% my $data = $count_arrayref->[++$n]; -% if ( ref($count) ) { - <% &{ $count }( $data ) %> -% } else { - <% sprintf( $count, $data ) %><BR> -% } -% } -% } - </FORM> - - </TD> - -% unless ( $opt{'disable_download'} || $type eq 'html-print' ) { - - <TD ALIGN="right"> - - Download full results<BR> - -% $cgi->param('_type', "$xlsname.xls" ); - as <A HREF="<% $cgi->self_url %>">Excel spreadsheet</A><BR> - -% $cgi->param('_type', 'csv'); - as <A HREF="<% $cgi->self_url %>">CSV file</A><BR> - -% $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" ); -% } - - </TR> - <TR> - <TD COLSPAN=2> - -% my $pager = ''; -% unless ( $type eq 'html_print' ) { - - <% $pager = include( '/elements/pager.html', - 'offset' => $offset, - 'num_rows' => scalar(@$rows), - 'total' => $total, - 'maxrecords' => $maxrecords, - ) - %> - - <% defined($opt{'html_form'}) - ? ( ref($opt{'html_form'}) - ? &{$opt{'html_form'}}() - : $opt{'html_form'} - ) - : '' - %> - -% } - - <% include('/elements/table-grid.html') %> - - <TR> -% my $h2 = 0; -% foreach my $header ( @{ $opt{header} } ) { -% my $label = ref($header) ? $header->{label} : $header; -% my $rowspan = 1; -% my $style = ''; -% if ( $opt{header2} ) { -% if ( !length($opt{header2}->[$h2]) ) { -% $rowspan = 2; -% splice @{ $opt{header2} }, $h2, 1; -% } else { -% $h2++; -% $style = 'STYLE="border-bottom: none"' -% } -% } - <TH CLASS = "grid" - BGCOLOR = "#cccccc" - ROWSPAN = "<% $rowspan %>" - <% $style %> - - > - <% $label %> - </TH> -% } - </TR> - -% if ( $opt{header2} ) { - <TR> -% foreach my $header ( @{ $opt{header2} } ) { -% my $label = ref($header) ? $header->{label} : $header; - <TH CLASS="grid" BGCOLOR="#cccccc"> - <FONT SIZE="-1"><% $label %></FONT> - </TH> -% } - </TR> -% } - -% my $bgcolor1 = '#eeeeee'; -% my $bgcolor2 = '#ffffff'; -% my $bgcolor; -% -% foreach my $row ( @$rows ) { -% -% if ( $bgcolor eq $bgcolor1 ) { -% $bgcolor = $bgcolor2; -% } else { -% $bgcolor = $bgcolor1; -% } - - <TR> - -% if ( $opt{'fields'} ) { -% -% my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; -% my $onclicks = $opt{'link_onclicks'} ? [ @{$opt{'link_onclicks'}} ] : []; -% my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : ''; -% my $colors = $opt{'color'} ? [ @{$opt{'color'}} ] : []; -% my $sizes = $opt{'size'} ? [ @{$opt{'size'}} ] : []; -% my $styles = $opt{'style'} ? [ @{$opt{'style'}} ] : []; -% my $cstyles = $opt{'cell_style'} ? [ @{$opt{'cell_style'}} ] : []; -% -% foreach my $field ( -% -% map { -% if ( ref($_) eq 'ARRAY' ) { -% -% my $tableref = $_; -% -% '<TABLE CLASS="inv" CELLSPACING=0 CELLPADDING=0 WIDTH="100%">'. -% -% join('', map { -% -% my $rowref = $_; -% -% '<tr>'. -% -% join('', map { -% -% my $e = $_; -% -% '<TD '. -% join(' ', map { -% uc($_).'="'. $e->{$_}. '"'; -% } -% grep exists($e->{$_}), -% qw( align bgcolor colspan rowspan -% style valign width ) -% ). -% '>'. -% -% ( $e->{'link'} -% ? '<A HREF="'. $e->{'link'}. '">' -% : '' -% ). -% ( $e->{'size'} -% ? '<FONT SIZE="'.uc($e->{'size'}).'">' -% : '' -% ). -% ( $e->{'data_style'} -% ? '<'. uc($e->{'data_style'}). '>' -% : '' -% ). -% $e->{'data'}. -% ( $e->{'data_style'} -% ? '</'. uc($e->{'data_style'}). '>' -% : '' -% ). -% ( $e->{'size'} ? '</FONT>' : '' ). -% ( $e->{'link'} ? '</A>' : '' ). -% '</td>'; -% -% } @$rowref ). -% -% '</tr>'; -% } @$tableref ). -% -% '</table>'; -% -% } else { -% $_; -% } -% } -% -% map { -% if ( ref($_) eq 'CODE' ) { -% &{$_}($row); -% } else { -% $row->$_(); -% } -% } -% @{$opt{'fields'}} -% -% ) { -% -% my $class = ( $field =~ /^<TABLE/i ) ? 'inv' : 'grid'; -% -% my $align = $aligns ? shift @$aligns : ''; -% $align = " ALIGN=$align" if $align; -% -% my $a = ''; -% if ( $links ) { -% my $link = shift @$links; -% my $onclick = shift @$onclicks; -% -% if ( ! $opt{'agent_virt'} -% || ( $null_link && ! $row->agentnum ) -% || grep { $row->agentnum == $_ } -% @link_agentnums -% ) { -% -% $link = &{$link}($row) -% if ref($link) eq 'CODE'; -% -% $onclick = &{$onclick}($row) -% if ref($onclick) eq 'CODE'; -% $onclick = qq( onClick="$onclick") if $onclick; -% -% if ( $link ) { -% my( $url, $method ) = @{$link}; -% if ( ref($method) eq 'CODE' ) { -% $a = $url. &{$method}($row); -% } else { -% $a = $url. $row->$method(); -% } -% $a = qq(<A HREF="$a"$onclick>); -% } -% -% } -% -% } -% -% my $font = ''; -% my $color = shift @$colors; -% $color = &{$color}($row) if ref($color) eq 'CODE'; -% my $size = shift @$sizes; -% $size = &{$size}($row) if ref($size) eq 'CODE'; -% if ( $color || $size ) { -% $font = '<FONT '. -% ( $color ? "COLOR=#$color " : '' ). -% ( $size ? qq(SIZE="$size" ) : '' ). -% '>'; -% } -% -% my($s, $es) = ( '', '' ); -% my $style = shift @$styles; -% $style = &{$style}($row) if ref($style) eq 'CODE'; -% if ( $style ) { -% $s = join( '', map "<$_>", split('', $style) ); -% $es = join( '', map "</$_>", split('', $style) ); -% } -% -% my $cstyle = shift @$cstyles; -% $cstyle = &{$cstyle}($row) if ref($cstyle) eq 'CODE'; -% $cstyle = qq(STYLE="$cstyle") -% if $cstyle; - - <TD CLASS="<% $class %>" BGCOLOR="<% $bgcolor %>" <% $align %> <% $cstyle %>><% $font %><% $a %><% $s %><% $field %><% $es %><% $a ? '</A>' : '' %><% $font ? '</FONT>' : '' %></TD> - -% } -% -% } else { -% -% foreach ( @$row ) { - <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $_ %></TD> -% } -% -% } - - </TR> - -% } - -% if ( $opt{'footer'} ) { - - <TR> - -% foreach my $footer ( @{ $opt{'footer'} } ) { - <TD CLASS="grid" BGCOLOR="#dddddd" STYLE="border-top: dashed 1px black;"><i><% $footer %></i></TD> -% } - - </TR> -% } - - </TABLE> - - <% $pager %> - - </TD> - </TR> - </TABLE> -% } - -% if ( $type eq 'html-print' ) { - - </BODY></HTML> - -% } else { - - <% defined($opt{'html_foot'}) - ? ( ref($opt{'html_foot'}) - ? &{$opt{'html_foot'}}() - : $opt{'html_foot'} - ) - : '' - %> - - <% include( '/elements/footer.html' ) %> - -% } - -% } +<% include('search-html.html', + type => $type, + header => $header, + rows => $rows, + link_agentnums => \@link_agentnums, + null_link => $null_link, + confmax => $confmax, + maxrecords => $maxrecords, + offset => $offset, + opt => \%opt + ) +%> % % } <%init> @@ -759,7 +232,7 @@ if ( $opt{'agent_virt'} ) { #false laziness w/statuspos above my $pos = $opt{'agent_pos'}; - foreach my $att (qw( align style color size )) { + foreach my $att (qw( align color size style cell_style xls_format )) { $opt{$att} ||= [ map '', @{ $opt{'fields'} } ]; } @@ -833,7 +306,7 @@ my $type = $cgi->param('_type') =~ /^(csv|\w*\.xls|select|html(-print)?)$/ ? $1 : 'html'; my $limit = ''; -my($confmax, $maxrecords, $total, $offset, $count_arrayref); +my($confmax, $maxrecords, $offset ); unless ( $type =~ /^(csv|\w*\.xls)$/ ) { @@ -867,13 +340,6 @@ unless ( $type =~ /^(csv|\w*\.xls)$/ ) { } - my $count_sth = dbh->prepare($opt{'count_query'}) - or die "Error preparing $opt{'count_query'}: ". dbh->errstr; - $count_sth->execute - or die "Error executing $opt{'count_query'}: ". $count_sth->errstr; - $count_arrayref = $count_sth->fetchrow_arrayref; - $total = $count_arrayref->[0]; - } # run the query @@ -882,6 +348,15 @@ my $header = [ map { ref($_) ? $_->{'label'} : $_ } @{$opt{header}} ]; my $rows; if ( ref($opt{query}) ) { + my @query; + if (ref($opt{query}) eq 'HASH') { + @query = ( $opt{query} ); + } elsif (ref($opt{query}) eq 'ARRAY') { + @query = @{ $opt{query} }; + } else { + die "invalid query reference"; + } + if ( $opt{disableable} && ! $cgi->param('showdisabled') ) { #%search = ( 'disabled' => '' ); $opt{'query'}->{'hashref'}->{'disabled'} = ''; @@ -889,14 +364,15 @@ if ( ref($opt{query}) ) { } #eval "use FS::$opt{'query'};"; - $rows = [ qsearch({ - 'select' => $opt{'query'}->{'select'}, - 'table' => $opt{'query'}->{'table'}, - 'addl_from' => (exists($opt{'query'}->{'addl_from'}) ? $opt{'query'}->{'addl_from'} : ''), - 'hashref' => $opt{'query'}->{'hashref'} || {}, - 'extra_sql' => $opt{'query'}->{'extra_sql'}, - 'order_by' => $opt{'query'}->{'order_by'}. " $limit", - }) ]; + my @param = qw( select table addl_from hashref extra_sql order_by ); + $rows = [ qsearch( [ map { my $query = $_; + ({ map { $_ => $query->{$_} } @param }); + } + @query + ], + 'order_by' => $opt{order_by}. " ". $limit, + ) + ]; } else { my $sth = dbh->prepare("$opt{'query'} $limit") or die "Error preparing $opt{'query'}: ". dbh->errstr; diff --git a/httemplate/search/reg_code.html b/httemplate/search/reg_code.html index f65b00d..f7d6d20 100644 --- a/httemplate/search/reg_code.html +++ b/httemplate/search/reg_code.html @@ -13,7 +13,7 @@ sub { map { qq!<A HREF="${p}edit/part_pkg.cgi?!. $_->pkgpart. '">'. - $_->pkg. ' - '. $_->comment. + $_->pkg_comment(nopkgpart => 1). '</A><BR>' } $_[0]->part_pkg }, diff --git a/httemplate/search/report_477.html b/httemplate/search/report_477.html new file mode 100755 index 0000000..7b85c13 --- /dev/null +++ b/httemplate/search/report_477.html @@ -0,0 +1,64 @@ +<% include('/elements/header.html', 'FCC Form 477 Report' ) %> + +<FORM ACTION="477.html" METHOD="GET"> +<INPUT TYPE="hidden" NAME="magic" VALUE="active"> + + <TABLE BGCOLOR="#cccccc" CELLSPACING=0> + + <TR> + <TH BGCOLOR="#e8e8e8" COLSPAN=2 ALIGN="left"> + <FONT SIZE="+1">Search options</FONT> + </TH> + </TR> + + <% include( '/elements/tr-select-agent.html', + 'curr_value' => scalar( $cgi->param('agentnum') ), + 'disable_empty' => 0, + ) + %> + + <% include( '/elements/tr-select-pkg_class.html', + 'pre_options' => [ '0' => 'all' ], + 'empty_label' => '(empty class)', + ) + %> + +% if ( scalar( qsearch( 'part_pkg_report_option', { 'disabled' => '' } ) ) ) { +% # the m2 javascript magic in edit/elements/edit.html would be better here + + <% include( '/elements/tr-select-table.html', + 'label' => 'Column report classes', + 'table' => 'part_pkg_report_option', + 'name_col' => 'name', + 'hashref' => { 'disabled' => '' }, + 'element_name' => 'column_option', + 'multiple' => 'multiple', + ) + %> + + <% include( '/elements/tr-select-table.html', + 'label' => 'Row report classes', + 'table' => 'part_pkg_report_option', + 'name_col' => 'name', + 'hashref' => { 'disabled' => '' }, + 'element_name' => 'row_option', + 'multiple' => 'multiple', + ) + %> + +% } + + </TABLE> + +<BR> +<INPUT TYPE="submit" VALUE="Get Report"> + +</FORM> + +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List packages'); + +</%init> diff --git a/httemplate/search/report_cdr.html b/httemplate/search/report_cdr.html index 2851631..c685198 100644 --- a/httemplate/search/report_cdr.html +++ b/httemplate/search/report_cdr.html @@ -3,6 +3,13 @@ <FORM ACTION="cdr.html" METHOD="GET"> <TABLE BGCOLOR="#cccccc" CELLSPACING=0> + + <TR> + <TH BGCOLOR="#e8e8e8" COLSPAN=2 ALIGN="left"> + <FONT SIZE="+1">Search options</FONT> + </TH> + </TR> + <TR> <TD ALIGN="right">Status: </TD> <TD> @@ -14,6 +21,23 @@ </TD> </TR> +% #if ( ) { # disable for everyone not using termination billing... +% foreach my $termpart ( 1..1 ) { #qsearch('part_termination + + <TR> + <TD ALIGN="right">Termination Status: </TD> + <TD> + <SELECT NAME="termpart<%$termpart%>status"> + <OPTION VALUE="">(all) + <OPTION VALUE="NULL">unprocessed + <OPTION VALUE="done">processed + </SELECT> + </TD> + </TR> + +% } +% #} + <% include ( '/elements/tr-input-beginning_ending.html' ) %> <TR> @@ -30,6 +54,21 @@ </TD> </TR> + <TR> + <TD ALIGN="right">Destination Context: </TD> + <TD> + <INPUT TYPE="text" NAME="dcontext"> + </TD> + </TR> + + + <TR> + <TD ALIGN="right">Charged Party #: </TD> + <TD> + <INPUT TYPE="text" NAME="charged_party"> + </TD> + </TR> + <% include( '/elements/tr-input-lessthan_greaterthan.html', 'label' => 'Duration (sec)', 'field' => 'duration', @@ -44,15 +83,66 @@ <% include( '/elements/tr-select-cdrbatch.html' ) %> + <TR> + <TD ALIGN="right">Acct ID (one per-line):</TD> + <TD><TEXTAREA NAME="acctid"></TEXTAREA></TD> + </TR> + + <TR> + <TH BGCOLOR="#e8e8e8" COLSPAN=2> </TH> + </TR> + + <TR> + <TH BGCOLOR="#e8e8e8" COLSPAN=2 ALIGN="left"><FONT SIZE="+1">Display options</FONT></TH> + </TR> + + <INPUT TYPE="hidden" NAME="show" VALUE="1"> + + <TR> + <TD COLSPAN=2> + <% include('/elements/checkboxes.html', + 'names_list' => $names_list, + 'element_name_prefix' => 'show_', + 'checked_callback' => sub { $show_default{$_[1]} }, + # my($cgi, $name) = @_; + ) + %> + </TD> + </TR> + </TABLE> <BR> <INPUT TYPE="submit" VALUE="Search Call Detail Records"> +</FORM> + <% include('/elements/footer.html') %> <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('List rating data'); +my @fields = fields('cdr'); +my $labels = FS::cdr->table_info->{'fields'}; + +#XXX config +my @show_default = qw( + calldate clid src dst dcontext charged_party + startdate answerdate enddate duration billsec + disposition amaflags accountcode userfield + rated_price upstream_price carrierid + svcnum freesidestatus freesiderewritestatus cdrbatch +); +my %show_default = map { $_=>1 } @show_default; + +my $names_list = [ map { + [ $_ => { + 'label' => 'Show '. ( $labels->{$_} || $_ ) + } + ] + } + @fields + ]; + </%init> diff --git a/httemplate/search/report_cust_main.html b/httemplate/search/report_cust_main.html index b0c5fde..f139d4b 100755 --- a/httemplate/search/report_cust_main.html +++ b/httemplate/search/report_cust_main.html @@ -56,6 +56,15 @@ <TD><INPUT TYPE="checkbox" NAME="cancelled_pkgs"></TD> </TR> +% if ( $conf->exists('cust_main-require_censustract') ) { + + <TR> + <TD ALIGN="right" VALIGN="center">Without census tract</TD> + <TD><INPUT TYPE="checkbox" NAME="no_censustract"></TD> + </TR> + +% } + <TR> <TH BGCOLOR="#e8e8e8" COLSPAN=2> </TH> </TR> @@ -84,6 +93,8 @@ die "access denied" $FS::CurrentUser::CurrentUser->access_right('List packages') );; +my $conf = new FS::Conf; + </%init> <%once> diff --git a/httemplate/search/report_cust_pay.html b/httemplate/search/report_cust_pay.html index 0627131..dd2358a 100644 --- a/httemplate/search/report_cust_pay.html +++ b/httemplate/search/report_cust_pay.html @@ -1,9 +1,15 @@ -<% include('/elements/header.html', 'Payment report' ) %> +<% include('/elements/header.html', $title ) %> -<FORM ACTION="cust_pay.cgi" METHOD="GET"> +<FORM ACTION="<% $void ? 'cust_pay_void.html' : 'cust_pay.cgi' %>" METHOD="GET"> <INPUT TYPE="hidden" NAME="magic" VALUE="_date"> -<TABLE> +<TABLE BGCOLOR="#cccccc" CELLSPACING=0> + + <TR> + <TH BGCOLOR="#e8e8e8" COLSPAN=2 ALIGN="left"> + <FONT SIZE="+1">Search options</FONT> + </TH> + </TR> <TR> <TD ALIGN="right">Payments of type: </TD> @@ -55,7 +61,32 @@ ) %> - <% include( '/elements/tr-input-beginning_ending.html' ) %> + <TR> + <TD ALIGN="right" VALIGN="center">Payment</TD> + <TD> + <TABLE> + <% include( '/elements/tr-input-beginning_ending.html', + layout => 'horiz', + ) + %> + </TABLE> + </TD> + </TR> + +% if ( $void ) { + <TR> + <TD ALIGN="right" VALIGN="center">Voided</TD> + <TD> + <TABLE> + <% include( '/elements/tr-input-beginning_ending.html', + prefix => 'void', + layout => 'horiz', + ) + %> + </TABLE> + </TD> + </TR> +% } <% include( '/elements/tr-input-lessthan_greaterthan.html', 'label' => 'Amount', @@ -76,4 +107,8 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); +my $void = $cgi->param('void') ? 1 : 0; + +my $title = $void ? 'Voided payment report' : 'Payment report'; + </%init> diff --git a/httemplate/search/report_cust_pkg.html b/httemplate/search/report_cust_pkg.html index aef1c24..66dd7d1 100755 --- a/httemplate/search/report_cust_pkg.html +++ b/httemplate/search/report_cust_pkg.html @@ -72,6 +72,20 @@ ) %> +% if ( scalar( qsearch( 'part_pkg_report_option', { 'disabled' => '' } ) ) ) { + + <% include( '/elements/tr-select-table.html', + 'label' => 'Report classes', + 'table' => 'part_pkg_report_option', + 'name_col' => 'name', + 'hashref' => { 'disabled' => '' }, + 'element_name' => 'report_option', + 'multiple' => 'multiple', + ) + %> + +% } + % foreach my $field (qw( setup last_bill bill adjourn susp expire cancel )) { <TR> @@ -89,6 +103,34 @@ % } + <SCRIPT TYPE="text/javascript"> + + function custom_changed(what) { + + if ( what.checked ) { + + what.form.pkgpart.disabled = true; + what.form.pkgpart.style.backgroundColor = '#dddddd'; + + } else { + + what.form.pkgpart.disabled = false; + what.form.pkgpart.style.backgroundColor = '#ffffff'; + + } + + } + + </SCRIPT> + + <% include( '/elements/tr-checkbox.html', + 'label' => 'Custom packages', + 'field' => 'custom', + 'value' => 1, + 'onchange' => 'custom_changed(this);', + ) + %> + <% include( '/elements/tr-selectmultiple-part_pkg.html' ) %> <TR> @@ -129,6 +171,7 @@ my %label = ( #false laziness w/cust_pkg.cgi my %disable = ( 'all' => {}, + 'not yet billed' => { 'setup'=>1, 'last_bill'=>1, 'bill'=>1, 'adjourn'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, }, 'one-time charge' => { 'last_bill'=>1, 'bill'=>1, 'adjourn'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, }, 'active' => { 'susp'=>1, 'cancel'=>1 }, 'suspended' => { 'cancel' => 1 }, diff --git a/httemplate/search/report_newtax.cgi b/httemplate/search/report_newtax.cgi index 586fddd..0fb5483 100755 --- a/httemplate/search/report_newtax.cgi +++ b/httemplate/search/report_newtax.cgi @@ -15,6 +15,7 @@ <TR> <TH CLASS="grid" BGCOLOR="#cccccc"></TH> + <TH CLASS="grid" BGCOLOR="#cccccc"></TH> <TH CLASS="grid" BGCOLOR="#cccccc">Tax collected</TH> </TR> % my $bgcolor1 = '#eeeeee'; @@ -37,9 +38,11 @@ <TR> <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $tax->{'label'} %></TD> + <% $tax->{base} ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %> <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right"> <A HREF="<% $baselink. $link %>;istax=1"><% $money_char %><% sprintf('%.2f', $tax->{'tax'} ) %></A> </TD> + <% !($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %> </TR> % } @@ -61,10 +64,11 @@ my $join_cust = " JOIN cust_bill USING ( invnum ) LEFT JOIN cust_main USING ( custnum ) "; -my $from_join_cust = " - FROM cust_bill_pkg - $join_cust -"; + +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 >= $beginning AND _date <= $ending "; @@ -76,65 +80,87 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { $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'; + my $tax = 0; my %taxes = (); +my %basetaxes = (); foreach my $t (qsearch({ table => 'cust_bill_pkg', + select => $select, hashref => { pkgpart => 0 }, - addl_from => $join_cust, + addl_from => $addl_from, extra_sql => $where, }) ) { - #warn $t->itemdesc. "\n"; + 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; - my $label = $t->itemdesc; - $label ||= 'Tax'; - $taxes{$label}->{'label'} = $label; - $taxes{$label}->{'url_param'} = "itemdesc=$label"; + $taxes{$label}->{'label'} = join(', ', split(/~/, $label) ); + $taxes{$label}->{'url_param'} = + join(';', map { "$_=". uri_escape($t->$_) } @params); - # calculate total for this tax - # 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. -} + 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"; -foreach my $t (qsearch({ table => 'cust_bill_pkg', - select => 'DISTINCT itemdesc', - hashref => { pkgpart => 0 }, - addl_from => $join_cust, - extra_sql => $where, - }) - ) -{ + my $x = scalar_sql($t, [ map { $_, $_ } @params ], $sql ); + $tax += $x; + $taxes{$label}->{'tax'} += $x; + + unless ( exists( $taxes{$baselabel} ) ) { - my $label = $t->itemdesc; - $label ||= 'Tax'; - my @taxparam = ( 'itemdesc' ); - my $taxwhere = "$from_join_cust $where AND payby != 'COMP' ". - "AND itemdesc = ?" ; + $basetaxes{$baselabel}->{'label'} = $baselabel; + $basetaxes{$baselabel}->{'url_param'} = "itemdesc=$baselabel"; + $basetaxes{$baselabel}->{'base'} = 1; - my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) ". - " $taxwhere AND pkgnum = 0"; + } - my $x = scalar_sql($t, \@taxparam, $sql ); - $tax += $x; - $taxes{$label}->{'tax'} += $x; + $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 = - map $taxes{$_}, - sort { ($b cmp $a) } - keys %taxes; +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, }; #-- @@ -143,7 +169,6 @@ push @taxes, { #to FS::Report or FS::Record or who the fuck knows where) sub scalar_sql { my( $r, $param, $sql ) = @_; - #warn "$sql\n"; my $sth = dbh->prepare($sql) or die dbh->errstr; $sth->execute( map $r->$_(), @$param ) or die "Unexpected error executing statement $sql: ". $sth->errstr; diff --git a/httemplate/search/report_prepaid_income.cgi b/httemplate/search/report_prepaid_income.cgi index 27dbcbf..ce928b8 100644 --- a/httemplate/search/report_prepaid_income.cgi +++ b/httemplate/search/report_prepaid_income.cgi @@ -38,22 +38,46 @@ my $now = $cgi->param('date') && str2time($cgi->param('date')) || $time; $now =~ /^(\d+)$/ or die "unparsable date?"; $now = $1; +my @where = (); + +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( 'cust_bill_pkg', { - 'recur' => { op=>'!=', value=>0 }, - 'edate' => { op=>'>', value=>$now }, - }, ); + 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 ( 'cust_pkg', { - 'bill' => { op=>'>', value=>$now } - } ); + 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; diff --git a/httemplate/search/report_prepaid_income.html b/httemplate/search/report_prepaid_income.html index 81adb64..d707bd8 100644 --- a/httemplate/search/report_prepaid_income.html +++ b/httemplate/search/report_prepaid_income.html @@ -1,28 +1,46 @@ -<% include('/elements/header.html', 'Prepaid Income (Unearned Revenue) Report', - '', - '', - '<LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> - <SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> - <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> - <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> - ' -) %> - - <FORM ACTION="report_prepaid_income.cgi" METHOD="GET"> - <TABLE> - <TR> - <TD>Prepaid income (unearned revenue) 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"> - </TD> - </TR> - <TR> - <TD> - </TD> - <TD><i>m/d/y</i></TD> - </TR> - </TABLE> +<% include('/elements/header.html','Prepaid Income (Unearned Revenue) Report')%> + +<% include('/elements/init_calendar.html') %> + +<FORM ACTION="report_prepaid_income.cgi" METHOD="GET"> + +<TABLE BGCOLOR="#cccccc" CELLSPACING=0> + + <TR> + <TH BGCOLOR="#e8e8e8" COLSPAN=2 ALIGN="left"> + <FONT SIZE="+1">Search options</FONT> + </TH> + </TR> + + <TR> + <TD>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"> + </TD> + </TR> + <TR> + <TD> + </TD> + <TD><FONT SIZE="-1"><i>m/d/y</i></FONT></TD> + </TR> + + <TR> + <TD COLSPAN=2> </TD> + </TR> + + <% include( '/elements/tr-select-agent.html', 'disable_empty'=>0 ) %> + + <TR> + <TD COLSPAN=2> </TD> + </TR> + + <TR> + <TD COLSPAN=2 ALIGN="center"><INPUT TYPE="submit" VALUE="Generate report"></TD> + </TR> + +</TABLE> + <SCRIPT TYPE="text/javascript"> Calendar.setup({ inputField: "date_text", @@ -32,7 +50,7 @@ }); </SCRIPT> -<INPUT TYPE="submit" VALUE="Generate report"> +</FORM> <% include('/elements/footer.html') %> <%init> diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi index 58d87fa..6df0161 100755 --- a/httemplate/search/report_receivables.cgi +++ b/httemplate/search/report_receivables.cgi @@ -1,159 +1,17 @@ -<% include( 'elements/search.html', +<% include( 'elements/cust_main_dayranges.html', 'title' => 'Accounts Receivable Aging Summary', - 'name' => 'customers', - 'query' => $sql_query, - 'count_query' => $count_sql, - 'header' => [ - FS::UI::Web::cust_header(), - '0-30', - '30-60', - '60-90', - '90+', - 'Total', - ], - 'footer' => [ - 'Total', - ( map '', - ( 1 .. - scalar(FS::UI::Web::cust_header()-1) - ) - ), - sprintf( $money_char.'%.2f', - $row->{'balance_0_30'} ), - sprintf( $money_char.'%.2f', - $row->{'balance_30_60'} ), - sprintf( $money_char.'%.2f', - $row->{'balance_60_90'} ), - sprintf( $money_char.'%.2f', - $row->{'balance_90_0'} ), - sprintf( '<b>'. $money_char.'%.2f'. '</b>', - $row->{'balance_0_0'} ), - ], - 'fields' => [ - \&FS::UI::Web::cust_fields, - format_balance('0_30'), - format_balance('30_60'), - format_balance('60_90'), - format_balance('90_0'), - format_balance('0_0'), - ], - 'links' => [ - ( map { $_ ne 'Cust. Status' ? $clink : '' } - FS::UI::Web::cust_header() - ), - '', - '', - '', - '', - '', - ], - #'align' => 'rlccrrrrr', - 'align' => FS::UI::Web::cust_aligns(). 'rrrrr', - #'size' => [ '', '', '-1', '-1', '', '', '', '', '', ], - #'style' => [ '', '', 'b', 'b', '', '', '', '', 'b', ], - 'size' => [ ( map '', FS::UI::Web::cust_header() ), - #'-1', '', '', '', '', '', ], - '', '', '', '', '', ], - 'style' => [ FS::UI::Web::cust_styles(), - #'b', '', '', '', '', 'b', ], - '', '', '', '', 'b', ], - 'color' => [ - FS::UI::Web::cust_colors(), - '', - '', - '', - '', - '', - ], - - ) + 'range_sub' => \&balance, + ) %> <%init> die "access denied" - unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); - -my @ranges = ( - [ 0, 30 ], - [ 30, 60 ], - [ 60, 90 ], - [ 90, 0 ], - [ 0, 0 ], -); - -my $owed_cols = join(',', map balance( @$_ ), @ranges ); - -my $select_count_pkgs = FS::cust_main->select_count_pkgs_sql; - -my $active_sql = FS::cust_pkg->active_sql; -my $inactive_sql = FS::cust_pkg->inactive_sql; -my $suspended_sql = FS::cust_pkg->suspended_sql; -my $cancelled_sql = FS::cust_pkg->cancelled_sql; - -my $packages_cols = <<END; - ( $select_count_pkgs ) AS num_pkgs_sql, - ( $select_count_pkgs AND $active_sql ) AS active_pkgs, - ( $select_count_pkgs AND $inactive_sql ) AS inactive_pkgs, - ( $select_count_pkgs AND $suspended_sql ) AS suspended_pkgs, - ( $select_count_pkgs AND $cancelled_sql ) AS cancelled_pkgs -END - -my @where = (); - -unless ( $cgi->param('all_customers') ) { - - my $days = 0; - if ( $cgi->param('days') =~ /^\s*(\d+)\s*$/ ) { - $days = $1; - } - - push @where, balance($days, 0, 'no_as'=>1). ' > 0'; # != 0'; - -} - -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 = "WHERE $where" if $where; - -my $count_sql = "select count(*) from cust_main $where"; - -my $sql_query = { - 'table' => 'cust_main', - 'hashref' => {}, - 'select' => join(',', - #'cust_main.*', - 'custnum', - $owed_cols, - $packages_cols, - FS::UI::Web::cust_sql_fields(), - ), - 'extra_sql' => $where, - 'order_by' => "order by coalesce(lower(company), ''), lower(last)", -}; - -my $total_sql = "SELECT ". join(',', map balance( @$_, 'sum'=>1 ), @ranges). - " FROM cust_main $where"; - -my $total_sth = dbh->prepare($total_sql) or die dbh->errstr; -$total_sth->execute or die "error executing $total_sql: ". $total_sth->errstr; -my $row = $total_sth->fetchrow_hashref(); - -my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; + unless $FS::CurrentUser::CurrentUser->access_right('Receivables report') + or $FS::CurrentUser::CurrentUser->access_right('Financial reports'); </%init> <%once> -my $conf = new FS::Conf; - -my $money_char = $conf->config('money_char') || '$'; - #Example: # # my $balance = balance( @@ -170,9 +28,7 @@ my $money_char = $conf->config('money_char') || '$'; # ) sub balance { - my($start, $end, %opt) = @_; - - my $as = $opt{'no_as'} ? '' : " AS balance_${start}_$end"; + my($start, $end) = @_; #, %opt ? #handle start and end ranges (86400 = 24h * 60m * 60s) my $str2time = str2time_sql; @@ -180,18 +36,10 @@ sub balance { $start = $start ? "( $str2time now() $closing - ".($start * 86400). ' )' : ''; $end = $end ? "( $str2time now() $closing - ".($end * 86400). ' )' : ''; - $opt{'unapplied_date'} = 1; - - ( $opt{sum} ? 'SUM( ' : '' ). - FS::cust_main->balance_date_sql( $start, $end, %opt ). - ( $opt{sum} ? ' )' : '' ). - $as; + #$opt{'unapplied_date'} = 1; -} + FS::cust_main->balance_date_sql( $start, $end, 'unapplied_date'=>1,); -sub format_balance { #closures help alot - my $range = shift; - sub { sprintf( $money_char.'%.2f', shift->get("balance_$range") ) }; } </%once> diff --git a/httemplate/search/report_receivables.html b/httemplate/search/report_receivables.html index 19b1ee7..bfb0169 100755 --- a/httemplate/search/report_receivables.html +++ b/httemplate/search/report_receivables.html @@ -11,6 +11,11 @@ </TR> <% include( '/elements/tr-select-agent.html', 'disable_empty'=>0 ) %> + + <% include( '/elements/tr-select-cust_main-status.html', + 'label' => 'Customer Status' + ) + %> <TR> <TD ALIGN="right">Customers</TD> @@ -30,6 +35,7 @@ <%init> die "access denied" - unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + unless $FS::CurrentUser::CurrentUser->access_right('Receivables report') + or $FS::CurrentUser::CurrentUser->access_right('Financial reports'); </%init> diff --git a/httemplate/search/report_svc_phone.html b/httemplate/search/report_svc_phone.html new file mode 100644 index 0000000..2d64340 --- /dev/null +++ b/httemplate/search/report_svc_phone.html @@ -0,0 +1,32 @@ +<% include('/elements/header.html', 'Phone number total usage' ) %> + +<FORM ACTION="svc_phone.cgi" METHOD="GET"> + +<INPUT TYPE="hidden" NAME="magic" VALUE="all"> +<INPUT TYPE="hidden" NAME="usage_total" VALUE="1"> + +<TABLE BGCOLOR="#cccccc" CELLSPACING=0> + +%# <TR> +%# <TH BGCOLOR="#e8e8e8" COLSPAN=2 ALIGN="left"> +%# <FONT SIZE="+1">Search options</FONT> +%# </TH> +%# </TR> + + <% include ( '/elements/tr-input-beginning_ending.html', prefix=>'usage' ) %> + +</TABLE> + +<BR> +<INPUT TYPE="submit" VALUE="Search phone numbers"> + +</FORM> + +<% include('/elements/footer.html') %> +<%init> + +#? 'List services' ? something new? +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List rating data'); + +</%init> diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index a7630dd..e89c665 100755 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -1,4 +1,4 @@ -<% include("/elements/header.html", "$agentname Sales Tax Report - ". +<% include("/elements/header.html", "$agentname Tax Report - ". ( $beginning ? time2str('%h %o %Y ', $beginning ) : '' @@ -44,12 +44,11 @@ % foreach my $region ( @regions ) { % % my $link = ''; -% if ( $region->{'label'} ne 'Total' ) { -% if ( $region->{'label'} eq $out ) { -% $link = ';out=1'; -% } else { -% $link = ';'. $region->{'url_param'}; -% } +% if ( $region->{'label'} eq $out ) { +% $link = ';out=1'; +% } else { +% $link = ';'. $region->{'url_param'} +% if $region->{'url_param'}; % } % % if ( $bgcolor eq $bgcolor1 ) { @@ -111,8 +110,12 @@ </TD> % unless ( $cgi->param('show_taxclasses') ) { +% my $invlink = $region->{'url_param_inv'} +% ? ';'. $region->{'url_param_inv'} +% : $link; + <<%$tdh%> ALIGN="right"> - <A HREF="<% $baselink. $link %>;istax=1" + <A HREF="<% $baselink. $invlink %>;istax=1" ><% &$money_sprintf( $region->{'tax'} ) %></A> </TD> % } @@ -138,13 +141,12 @@ % foreach my $region ( @base_regions ) { % % my $link = ''; -% #if ( $region->{'label'} ne 'Total' ) { -% if ( $region->{'label'} eq $out ) { -% $link = ';out=1'; -% } else { -% $link = ';'. $region->{'url_param'}; -% } -% #} +% if ( $region->{'label'} eq $out ) { +% $link = ';out=1'; +% } else { +% $link = ';'. $region->{'url_param'} +% if $region->{'url_param'}; +% } % % if ( $bgcolor eq $bgcolor1 ) { % $bgcolor = $bgcolor2; @@ -174,7 +176,7 @@ <<%$td%>>Total</TD> <<%$td%> ALIGN="right"> <A HREF="<% $baselink %>;istax=1" - ><% &$money_sprintf( $tax ) %></A> + ><% &$money_sprintf( $tot_tax ) %></A> </TD> </TR> @@ -275,8 +277,6 @@ if ( $conf->exists('tax-pkg_address') ) { "WHERE 0 < ( SELECT COUNT(*) FROM cust_main WHERE $gotcust LIMIT 1 )"; } -my($total, $tot_taxable, $owed, $tax) = ( 0, 0, 0, 0 ); -my( $exempt_cust, $exempt_pkg, $exempt_monthly ) = ( 0, 0, 0 ); my $out = 'Out of taxable region(s)'; my %regions = (); @@ -289,6 +289,9 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', my $label = getlabel($r); $regions{$label}->{'label'} = $label; + + $regions{$label}->{$_} = $r->$_() for (qw( county state country )); #taxname? + $regions{$label}->{'url_param'} = join(';', map "$_=".uri_escape($r->$_()), qw( county state country taxname ) @@ -304,6 +307,8 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', $regions{$label}->{'url_param'} .= ';taxclass='. uri_escape($r->taxclass); #no, always# if $cgi->param('show_taxclasses'); + $regions{$label}->{'taxclass'} = $r->taxclass; + } else { $regions{$label}->{'url_param'} .= ';taxclassNULL=1' @@ -326,10 +331,9 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', my $t_sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax"; my $t = scalar_sql($r, \@param, $t_sql); - $total += $t; $regions{$label}->{'total'} += $t; - #if ( $label eq $out ) {# && $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'; @@ -351,12 +355,27 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', # ); # } + #false laziness -ish w/report_tax.cgi + my $cust_exempt; + if ( $r->taxname ) { + my $q_taxname = dbh->quote($r->taxname); + $cust_exempt = + "( tax = 'Y' + OR EXISTS ( SELECT 1 FROM cust_main_exemption + WHERE cust_main_exemption.custnum = cust_main.custnum + AND cust_main_exemption.taxname = $q_taxname + ) + ) + "; + } else { + $cust_exempt = " tax = 'Y' "; + } + my $x_cust = scalar_sql($r, \@param, "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) - $fromwhere AND $nottax AND tax = 'Y' " + $fromwhere AND $nottax AND $cust_exempt " ); - $exempt_cust += $x_cust; $regions{$label}->{'exempt_cust'} += $x_cust; ## calculate package-exemption for this region @@ -384,7 +403,6 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', AND ( tax != 'Y' OR tax IS NULL ) " ); - $exempt_pkg += $x_pkg; $regions{$label}->{'exempt_pkg'} += $x_pkg; ## calculate monthly exemption (texas tax) for this region @@ -399,20 +417,11 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', $join_cust_pkg $mywhere" ); -# if ( $x_monthly ) { -# #warn $r->taxnum(). ": $x_monthly\n"; -# $taxable -= $x_monthly; -# } - - $exempt_monthly += $x_monthly; $regions{$label}->{'exempt_monthly'} += $x_monthly; my $taxable = $t - $x_cust - $x_pkg - $x_monthly; - - $tot_taxable += $taxable; $regions{$label}->{'taxable'} += $taxable; - $owed += $taxable * ($r->tax/100); $regions{$label}->{'owed'} += $taxable * ($r->tax/100); if ( defined($regions{$label}->{'rate'}) @@ -474,20 +483,43 @@ my $_taxamount_sub = sub { scalar_sql($r, \@taxparam, $sql ); }; +#tax-report_groups filtering +my($group_op, $group_value) = ( '', '' ); +if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) { + ( $group_op, $group_value ) = ( $1, $2 ); +} +my $group_test = sub { + my $label = shift; + return 1 unless $group_op; #in case we get called inadvertantly + if ( $label eq $out ) { #don't display "out of taxable region" in this case + 0; + } elsif ( $group_op eq '=' ) { + $label =~ /^$group_value/; + } elsif ( $group_op eq '!=' ) { + $label !~ /^$group_value/; + } else { + die "guru meditation #00de: group_op $group_op\n"; + } +}; + +my $tot_tax = 0; #foreach my $label ( keys %regions ) { foreach my $r ( qsearch(\%qsearch) ) { #warn join('-', map { $r->$_() } qw( country state county taxname ) )."\n"; my $label = getlabel($r); + if ( $group_op ) { + next unless &{$group_test}($label); + } #my $fromwhere = $join_pkg. $where. " AND payby != 'COMP' "; #my @param = @base_param; my $x = &{$_taxamount_sub}($r); - $tax += $x unless $cgi->param('show_taxclasses'); $regions{$label}->{'tax'} += $x; + $tot_tax += $x unless $cgi->param('show_taxclasses'); } @@ -508,34 +540,86 @@ if ( $cgi->param('show_taxclasses') ) { ); $base_regions{$base_label}->{'tax'} += $x; - $tax += $x; + $tot_tax += $x; } } +my @regions = keys %regions; + +#tax-report_groups filtering +@regions = grep &{$group_test}($_), @regions + if $group_op; + +#calculate totals +my( $total, $tot_taxable, $tot_owed ) = ( 0, 0, 0 ); +my( $exempt_cust, $exempt_pkg, $exempt_monthly ) = ( 0, 0, 0 ); +my %taxclasses = (); +my %county = (); +my %state = (); +my %country = (); +foreach (@regions) { + $total += $regions{$_}->{'total'}; + $tot_taxable += $regions{$_}->{'taxable'}; + $tot_owed += $regions{$_}->{'owed'}; + $exempt_cust += $regions{$_}->{'exempt_cust'}; + $exempt_pkg += $regions{$_}->{'exempt_pkg'}; + $exempt_monthly += $regions{$_}->{'exempt_monthly'}; + $taxclasses{$regions{$_}->{'taxclass'}} = 1 + if $regions{$_}->{'taxclass'}; + $county{$regions{$_}->{'county'}} = 1; + $state{$regions{$_}->{'state'}} = 1; + $country{$regions{$_}->{'country'}} = 1; +} + +my $total_url_param = ''; +my $total_url_param_invoiced = ''; +if ( $group_op ) { + + my @country = keys %country; + warn "WARNING: multiple countries on this grouped report; total links broken" + if scalar(@country) > 1; + my $country = $country[0]; + + my @state = keys %state; + warn "WARNING: multiple countries on this grouped report; total links broken" + if scalar(@state) > 1; + my $state = $state[0]; + + $total_url_param_invoiced = + $total_url_param = + 'report_group='.uri_escape("$group_op $group_value").';'. + join(';', map 'taxclass='.uri_escape($_), keys %taxclasses ); + $total_url_param .= ';'. + "country=$country;state=".uri_escape($state).';'. + join(';', map 'county='.uri_escape($_), keys %county ) ; + +} #ordering -my @regions = +@regions = map $regions{$_}, sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } - keys %regions; + @regions; my @base_regions = map $base_regions{$_}, sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } keys %base_regions; +#add total line push @regions, { 'label' => 'Total', - 'url_param' => '', + 'url_param' => $total_url_param, + 'url_param_inv' => $total_url_param_invoiced, 'total' => $total, 'exempt_cust' => $exempt_cust, 'exempt_pkg' => $exempt_pkg, 'exempt_monthly' => $exempt_monthly, 'taxable' => $tot_taxable, 'rate' => '', - 'owed' => $owed, - 'tax' => $tax, + 'owed' => $tot_owed, + 'tax' => $tot_tax, }; #-- diff --git a/httemplate/search/report_tax.html b/httemplate/search/report_tax.html index e5ffa9a..217f481 100755 --- a/httemplate/search/report_tax.html +++ b/httemplate/search/report_tax.html @@ -4,29 +4,49 @@ <TABLE> +% if ( $conf->config('tax-report_groups') ) { +% my @lines = $conf->config('tax-report_groups'); + + <TR> + <TD ALIGN="right">Tax group</TD> + <TD> + <SELECT NAME="report_group"> + + <OPTION VALUE="">all</OPTION> + +% foreach my $line ( @lines ) { +% $line =~ /^\s*(.+)\s+(=|!=)\s+(.*)\s*$/ #or next; +% or do { warn "bad report_group line: $line\n"; next; }; +% my($label, $op, $value) = ($1, $2, $3); + + <OPTION VALUE="<% "$op $value" %>"><% $label %></OPTION> +% } + + </SELECT> + </TD> + </TR> + +% } + <% include( '/elements/tr-select-agent.html', 'disable_empty'=>0 ) %> <% include( '/elements/tr-input-beginning_ending.html' ) %> -% my $conf = new FS::Conf; -% if ( $conf->exists('enable_taxclasses') ) { -% +% if ( $conf->exists('enable_taxclasses') ) { <TR> <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="show_taxclasses" VALUE="1"></TD> <TD>Show tax classes</TD> </TR> % } -% my @pkg_class = qsearch('pkg_class', {}); -% if ( @pkg_class ) { -% +% my @pkg_class = qsearch('pkg_class', {}); +% if ( @pkg_class ) { <TR> <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="show_pkgclasses" VALUE="1"></TD> <TD>Show package classes</TD> </TR> % } - </TABLE> <BR><INPUT TYPE="submit" VALUE="Get Report"> @@ -39,4 +59,6 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); +my $conf = new FS::Conf; + </%init> diff --git a/httemplate/search/report_unapplied_cust_pay.html b/httemplate/search/report_unapplied_cust_pay.html new file mode 100755 index 0000000..10093e5 --- /dev/null +++ b/httemplate/search/report_unapplied_cust_pay.html @@ -0,0 +1,41 @@ +<% include('/elements/header.html', 'Unapplied Payments Aging Summary' ) %> +%# 'Prepaid Balance Aging Summary', #??? + +<FORM NAME="OneTrueForm" ACTION="unapplied_cust_pay.html" METHOD="GET"> + +<TABLE BGCOLOR="#cccccc" CELLSPACING=0> + + <TR> + <TH BGCOLOR="#e8e8e8" COLSPAN=2 ALIGN="left"> + <FONT SIZE="+1">Search options</FONT> + </TH> + </TR> + + <% include( '/elements/tr-select-agent.html', 'disable_empty'=>0 ) %> + + <% include( '/elements/tr-select-cust_main-status.html', + 'label' => 'Customer Status' + ) + %> + + <TR> + <TD ALIGN="right">Customers</TD> + <TD> + <INPUT TYPE="radio" NAME="all_customers" VALUE="1" onClick="if (this.checked) { document.OneTrueForm.days.disabled=true; document.OneTrueForm.days.style.backgroundColor = '#dddddd'; } else { document.OneTrueForm.days.disabled=false; document.OneTrueForm.days.style.backgroundColor = '#ffffff'; }">All customers (even those without unapplied payments)<BR> + <INPUT TYPE="radio" NAME="all_customers" VALUE="0" CHECKED onClick="if ( ! this.checked ) { document.OneTrueForm.days.disabled=true; document.OneTrueForm.days.style.backgroundColor = '#dddddd'; } else { document.OneTrueForm.days.disabled=false; document.OneTrueForm.days.style.backgroundColor = '#ffffff'; }">Customers with unapplied payments over <INPUT NAME="days" TYPE="text" SIZE=4 MAXLENGTH=3 VALUE="0"> days old + </TD> + </TR> + +</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/svc_broadband.cgi b/httemplate/search/svc_broadband.cgi index 2cb0c1e..d0b1029 100755 --- a/httemplate/search/svc_broadband.cgi +++ b/httemplate/search/svc_broadband.cgi @@ -111,13 +111,13 @@ foreach my $router (qsearch('router', {})) { } } -my $link = [ $p.'view/svc_broadband.cgi', 'svcnum' ]; +my $link = [ $p.'view/svc_broadband.cgi?', 'svcnum' ]; #XXX get the router link working my $link_router = sub { my $routernum = $routerbyblock{shift->blocknum}->routernum; [ $p.'view/router.cgi?'.$routernum, 'routernum' ]; }; -my $link_cust = [ $p.'view/cust_main.cgi', 'custnum' ]; +my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ]; </%init> diff --git a/httemplate/search/svc_external.cgi b/httemplate/search/svc_external.cgi index 2710d75..f061754 100755 --- a/httemplate/search/svc_external.cgi +++ b/httemplate/search/svc_external.cgi @@ -1,153 +1,135 @@ -%die "access denied" -% unless $FS::CurrentUser::CurrentUser->access_right('List services'); -% -%my $conf = new FS::Conf; -% -%my @svc_external = (); -%my @h_svc_external = (); -%my $sortby=\*svcnum_sort; -%if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { -% -% @svc_external=qsearch('svc_external',{}); -% -% if ( $cgi->param('magic') eq 'unlinked' ) { -% @svc_external = grep { qsearchs('cust_svc', { -% 'svcnum' => $_->svcnum, -% 'pkgnum' => '', -% } -% ) -% } -% @svc_external; -% } -% -% if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { -% my $sortby = $1; -% if ( $sortby eq 'id' ) { -% $sortby = \*id_sort; -% } -% } -% -%} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { -% -% @svc_external = -% qsearch( 'svc_external', {}, '', -% " WHERE $1 = ( SELECT svcpart FROM cust_svc ". -% " WHERE cust_svc.svcnum = svc_external.svcnum ) " -% ); -% -%} elsif ( $cgi->param('title') =~ /^(.*)$/ ) { -% $sortby=\*id_sort; -% @svc_external=qsearch('svc_external',{ title => $1 }); -% if( $cgi->param('history') == 1 ) { -% @h_svc_external=qsearch('h_svc_external',{ title => $1 }); -% } -%} elsif ( $cgi->param('id') =~ /^([\w\-\.]+)$/ ) { -% my $id = $1; -% @svc_external = qsearchs('svc_external',{'id'=>$id}); -%} -% -%if ( scalar(@svc_external) == 1 ) { -% -% -<% $cgi->redirect(popurl(2). "view/svc_external.cgi?". $svc_external[0]->svcnum) %> -% -% -%} elsif ( scalar(@svc_external) == 0 ) { -% -% -<% include('/elements/header.html', 'External Search Results' ) %> - - No matching external services found -% } else { -% -% -<% include('/elements/header.html', 'External Search Results', '') %> - - <% scalar(@svc_external) %> matching external services found - <TABLE BORDER=4 CELLSPACING=0 CELLPADDING=0> - <TR> - <TH>Service #</TH> - <TH><% FS::Msgcat::_gettext('svc_external-id') || 'External ID' %></TH> - <TH><% FS::Msgcat::_gettext('svc_external-title') || 'Title' %></TH> - </TR> -% -% foreach my $svc_external ( -% sort $sortby (@svc_external) -% ) { -% my($svcnum, $id, $title)=( -% $svc_external->svcnum, -% $svc_external->id, -% $svc_external->title, -% ); -% -% my $rowspan = 1; -% -% print <<END; -% <TR> -% <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_external.cgi?$svcnum">$svcnum</A></TD> -% <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_external.cgi?$svcnum">$id</A></TD> -% <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_external.cgi?$svcnum">$title</A></TD> -%END -% -% #print @rows; -% print "</TR>"; -% -% } -% if( scalar(@h_svc_external) > 0 ) { -% print <<HTML; -% </TABLE> -% <TABLE BORDER=4 CELLSPACING=0 CELLPADDING=0> -% <TR> -% <TH>Freeside ID</TH> -% <TH>Service #</TH> -% <TH>Title</TH> -% <TH>Date</TH> -% </TR> -%HTML -% -% foreach my $h_svc ( @h_svc_external ) { -% my($svcnum, $id, $title, $user, $date)=( -% $h_svc->svcnum, -% $h_svc->id, -% $h_svc->title, -% $h_svc->history_user, -% $h_svc->history_date, -% ); -% my $rowspan = 1; -% my ($h_cust_svc) = qsearchs( 'h_cust_svc', { -% svcnum => $svcnum, -% }); -% my $cust_pkg = qsearchs( 'cust_pkg', { -% pkgnum => $h_cust_svc->pkgnum, -% }); -% my $custnum = $cust_pkg->custnum; -% -% print <<END; -% <TR> -% <TD ROWSPAN=$rowspan><A HREF="${p}view/cust_main.cgi?$custnum">$custnum</A></TD> -% <TD ROWSPAN=$rowspan><A HREF="${p}view/cust_main.cgi?$custnum">$svcnum</A></TD> -% <TD ROWSPAN=$rowspan><A HREF="${p}view/cust_main.cgi?$custnum">$title</A></TD> -% <TD ROWSPAN=$rowspan><A HREF="${p}view/cust_main.cgi?$custnum">$date</A></TD> -% </TR> -%END -% } -% } -% -% print <<END; -% </TABLE> -% </BODY> -%</HTML> -%END -% -%} -% -%sub svcnum_sort { -% $a->getfield('svcnum') <=> $b->getfield('svcnum'); -%} -% -%sub id_sort { -% $a->getfield('id') <=> $b->getfield('id'); -%} -% -% +<% include( 'elements/search.html', + 'title' => 'External service search results', + 'name' => 'external services', + 'query' => $sql_query, + 'count_query' => $count_query, + 'redirect' => $redirect, + 'header' => [ '#', + 'Service', + ( FS::Msgcat::_gettext('svc_external-id') || 'External ID' ), + ( FS::Msgcat::_gettext('svc_external-title') || 'Title' ), + FS::UI::Web::cust_header(), + ], + 'fields' => [ 'svcnum', + 'svc', + 'id', + 'title', + \&FS::UI::Web::cust_fields, + ], + 'links' => [ $link, + $link, + $link, + $link, + ( map { $_ ne 'Cust. Status' ? $link_cust : '' } + FS::UI::Web::cust_header() + ), + ], + 'align' => 'rlrr'. + FS::UI::Web::cust_aligns(), + 'color' => [ + '', + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + '', + FS::UI::Web::cust_styles(), + ], + ) +%> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List services'); + +my $conf = new FS::Conf; + +my %svc_external; +my @extra_sql = (); +my $orderby = 'ORDER BY svcnum'; + +my $link = [ "${p}view/svc_external.cgi?", 'svcnum' ]; +my $redirect = $link; + +if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { + + push @extra_sql, 'pkgnum IS NULL' + if $cgi->param('magic') eq 'unlinked'; + + if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { + my $sortby = $1; + $orderby = "ORDER BY $sortby"; + } + +} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { + + push @extra_sql, "svcpart = $1"; + +} elsif ( $cgi->param('title') =~ /^(.*)$/ ) { + + $svc_external{'title'} = $1; + $orderby = 'ORDER BY id'; + + # is this linked from anywhere??? + # if( $cgi->param('history') == 1 ) { + # @h_svc_external=qsearch('h_svc_external',{ title => $1 }); + # } + +} elsif ( $cgi->param('id') =~ /^([\w\-\.]+)$/ ) { + + $svc_external{'id'} = $1; + +} + +my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. + ' LEFT JOIN part_svc USING ( svcpart ) '. + ' LEFT JOIN cust_pkg USING ( pkgnum ) '. + ' LEFT JOIN cust_main USING ( custnum ) '; + +#here is the agent virtualization +push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( + 'null_right' => 'View/link unlinked services' + ); + +my $extra_sql = ''; +if ( @extra_sql ) { + $extra_sql = ( keys(%svc_external) ? ' AND ' : ' WHERE ' ). + join(' AND ', @extra_sql ); +} + +my $count_query = "SELECT COUNT(*) FROM svc_external $addl_from "; +if ( keys %svc_external ) { + $count_query .= ' WHERE '. + join(' AND ', map "$_ = ". dbh->quote($svc_external{$_}), + keys %svc_external + ); +} +$count_query .= $extra_sql; + +my $sql_query = { + 'table' => 'svc_external', + 'hashref' => \%svc_external, + 'select' => join(', ', + 'svc_external.*', + 'part_svc.svc', + 'cust_main.custnum', + FS::UI::Web::cust_sql_fields(), + ), + 'extra_sql' => "$extra_sql $orderby", + 'addl_from' => $addl_from, +}; + +#smaller false laziness w/svc_*.cgi here +my $link_cust = sub { + my $svc_x = shift; + $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; +}; + + +</%init> diff --git a/httemplate/search/svc_phone.cgi b/httemplate/search/svc_phone.cgi index 49340c6..21e1a92 100644 --- a/httemplate/search/svc_phone.cgi +++ b/httemplate/search/svc_phone.cgi @@ -3,33 +3,39 @@ 'name' => 'phone numbers', 'query' => $sql_query, 'count_query' => $count_query, - 'redirect' => $link, + 'redirect' => $redirect, 'header' => [ '#', 'Service', 'Country code', 'Phone number', + @header, FS::UI::Web::cust_header(), ], 'fields' => [ 'svcnum', 'svc', 'countrycode', 'phonenum', + @fields, \&FS::UI::Web::cust_fields, ], 'links' => [ $link, $link, $link, $link, + ( map '', @header ), ( map { $_ ne 'Cust. Status' ? $link_cust : '' } FS::UI::Web::cust_header() ), ], - 'align' => 'rlrr'. FS::UI::Web::cust_aligns(), + 'align' => 'rlrr'. + join('', map 'r', @header). + FS::UI::Web::cust_aligns(), 'color' => [ '', '', '', '', + ( map '', @header ), FS::UI::Web::cust_colors(), ], 'style' => [ @@ -37,6 +43,7 @@ '', '', '', + ( map '', @header ), FS::UI::Web::cust_styles(), ], ) @@ -48,9 +55,16 @@ die "access denied" my $conf = new FS::Conf; -my $orderby = 'ORDER BY svcnum'; +my @select = (); my %svc_phone = (); my @extra_sql = (); +my $orderby = 'ORDER BY svcnum'; + +my @header = (); +my @fields = (); +my $link = [ "${p}view/svc_phone.cgi?", 'svcnum' ]; +my $redirect = $link; + if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { push @extra_sql, 'pkgnum IS NULL' @@ -61,6 +75,50 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { $orderby = "ORDER BY $sortby"; } + if ( $cgi->param('usage_total') ) { + + my($beginning,$ending) = FS::UI::Web::parse_beginning_ending($cgi, 'usage'); + + $redirect = ''; + + #my $and_date = " AND startdate >= $beginning AND startdate <= $ending "; + my $and_date = " AND enddate >= $beginning AND enddate <= $ending "; + + my $fromwhere = " FROM cdr WHERE cdr.svcnum = svc_phone.svcnum $and_date"; + + #more efficient to join against cdr just once... this will do for now + push @select, map { " ( SELECT SUM($_) $fromwhere ) AS $_ " } + qw( billsec rated_price ); + + my $money_char = $conf->config('money_char') || '$'; + + push @header, 'Minutes', 'Billed'; + push @fields, + sub { sprintf('%.3f', shift->get('billsec') / 60 ); }, + sub { $money_char. sprintf('%.2f', shift->get('rated_price') ); }; + + #XXX and termination... (this needs a config to turn on, not by default) + if ( 1 ) { # $conf->exists('cdr-termination_hack') { #} + + my $f_w = + " FROM cdr_termination LEFT JOIN cdr USING ( acctid ) ". + " WHERE cdr.acctid = svc_phone.phonenum ". # XXX connectone-specific + $and_date; + + push @select, + " ( SELECT SUM(billsec) $f_w ) AS term_billsec ", + " ( SELECT SUM(cdr_termination.rated_price) $f_w ) AS term_rated_price"; + + push @header, 'Term Min', 'Term Billed'; + push @fields, + sub { sprintf('%.3f', shift->get('term_billsec') / 60 ); }, + sub { $money_char. sprintf('%.2f', shift->get('rated_price') ); }; + + } + + + } + } elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { push @extra_sql, "svcpart = $1"; } else { @@ -99,15 +157,14 @@ my $sql_query = { 'select' => join(', ', 'svc_phone.*', 'part_svc.svc', - 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(), + @select, + 'cust_main.custnum', + FS::UI::Web::cust_sql_fields(), ), 'extra_sql' => "$extra_sql $orderby", 'addl_from' => $addl_from, }; -my $link = [ "${p}view/svc_phone.cgi?", 'svcnum' ]; - #smaller false laziness w/svc_*.cgi here my $link_cust = sub { my $svc_x = shift; diff --git a/httemplate/search/unapplied_cust_pay.html b/httemplate/search/unapplied_cust_pay.html new file mode 100755 index 0000000..8d064d1 --- /dev/null +++ b/httemplate/search/unapplied_cust_pay.html @@ -0,0 +1,28 @@ +<% include( 'elements/cust_main_dayranges.html', + #'title' => 'Prepaid Balance Aging Summary', #??? + 'title' => 'Unapplied Payments Aging Summary', + 'range_sub' => \&unapplied_payments, + ) +%> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +</%init> +<%once> + +sub unapplied_payments { + my($start, $end, %opt) = @_; + + #handle start and end ranges (86400 = 24h * 60m * 60s) + my $str2time = str2time_sql; + my $closing = str2time_sql_closing; + $start = $start ? "( $str2time now() $closing - ".($start * 86400). ' )' : ''; + $end = $end ? "( $str2time now() $closing - ".($end * 86400). ' )' : ''; + + FS::cust_main->unapplied_payments_date_sql( $start, $end ); + +} + +</%once> |