diff options
Diffstat (limited to 'httemplate/search')
105 files changed, 3096 insertions, 1063 deletions
diff --git a/httemplate/search/477.html b/httemplate/search/477.html index 6f5fcdf3b..eed3df946 100755 --- a/httemplate/search/477.html +++ b/httemplate/search/477.html @@ -3,6 +3,14 @@ <Form_477_submission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="https://specialreports.fcc.gov/wcb/Form477/XMLSchema-instance/form_477_upload_Schema.xsd" > % } else { #html <& /elements/header.html, "FCC Form 477 Results - $state" &> +%# XXX when we stop supporting IE8, add this to freeside.css using :nth-child +%# selectors, and remove it from everywhere else +<STYLE TYPE="text/css"> +.grid TH { background-color: #cccccc; padding: 0px 3px 2px; text-align: right } +.row0 TD { background-color: #eeeeee; padding: 0px 3px 2px; text-align: right } +.row1 TD { background-color: #ffffff; padding: 0px 3px 2px; text-align: right } +</STYLE> + <TABLE WIDTH="100%"> <TR> <TD></TD> @@ -38,8 +46,11 @@ % if ( $type eq 'xml' ) { <<% 'Part_IA_'. chr(65 + $tech) %>> % } -<& "477part${part}_summary.html", 'tech_code' => $tech, 'url' => $url &> -<& "477part${part}_detail.html", 'tech_code' => $tech, 'url' => $url &> +<& "477part${part}.html", + 'tech_code' => $tech, + 'url' => $url, + 'type' => $type +&> % if ( $type eq 'xml' ) { </<% 'Part_IA_'. chr(65 + $tech) %>> % } @@ -97,6 +108,11 @@ for(my $i=0; $i < scalar(@part2b_row_option); $i++) { &FS::Report::FCC_477::save_fcc477map("part2b_row_option_$i",$part2b_row_option[$i]); } +my $part5_report_option = $cgi->param('part5_report_option'); +if ( $part5_report_option ) { + FS::Report::FCC_477::save_fcc477map('part5_report_option', $part5_report_option); +} + my $url_mangler = sub { my $part = shift; my $url = $cgi->url('-path_info' => 1, '-full' => 1); diff --git a/httemplate/search/477partIA.html b/httemplate/search/477partIA.html new file mode 100755 index 000000000..1cd0b70e0 --- /dev/null +++ b/httemplate/search/477partIA.html @@ -0,0 +1,165 @@ +% if ( $opt{'type'} eq 'xml' ) { +%# container element <Part_IA_$tech> is in 477.html +% my $col = 'a'; +% foreach ( @summary_row ) { +% my $el = $xml_prefix . $col . '1'; # PartIA_Aa1, PartIA_Ab1, etc. + <<% $el %>><% $_ %><<% "/$el" %>> +% $col++; +% } +% foreach my $col_data ( @data ) { +% my $row = 1; +% foreach my $cell ( @$col_data ) { +% my $el = $xml_prefix . $col . $row; # PartIA_Af1, PartIA_Af2... + <<% $el %>><% $cell->[0] %><<% "/$el" %>> +% if ( $percentages ) { +% $el = $xml_percent . $col . $row; # Part_p_IA_Af1, ... + <<% $el %>><% $cell->[1] %><<% "/$el" %>> +% } +% $row++; +% } # foreach $cell +% $col++; +% } # foreach $col_data +% } else { # not XML + +<H2><% $title %> totals</H2> +<& /elements/table-grid.html &> + <TR> +% foreach ( 'Total Connections', +% '% owned loop', +% '% billed to end users', +% '% residential', +% '% residential > 200 kbps') { + <TH WIDTH="20%"><% $_ |h %></TH> +% } + </TR> + <TR CLASS="row0"> +% foreach ( @summary_row ) { + <TD><% $_ %></TD> +% } + </TR> +</TABLE> +<H2><% $title %> breakdown by speed</H2> +<TABLE CLASS="grid" CELLSPACING=0> + <TR> + <TH WIDTH="12%"></TH> +% for (my $col = 0; $col < scalar(@download_option); $col++) { + <TH WIDTH="11%"> + <% $FS::Report::FCC_477::download[$col] |h %> + </TH> +% } + </TR> +% for (my $row = 0; $row < scalar(@upload_option); $row++) { + <TR CLASS="row<% $row % 2%>"> + <TD STYLE="text-align: left; font-weight: bold"> +% if ( $asymmetric ) { + <% $FS::Report::FCC_477::upload[$row] |h %> +% } + </TD> +% for (my $col = 0; $col < scalar(@download_option); $col++) { + <TD> + <% $data[$col][$row][0] %> +% if ( $percentages ) { + <BR><% $data[$col][$row][1] %> +% } + </TD> +% } # for $col + </TR> +% } # for $row +</TABLE> +% } +<%init> + +my $curuser = $FS::CurrentUser::CurrentUser; + +die "access denied" + unless $curuser->access_right('List packages'); + +my %opt = @_; +my %search_hash; + +for ( qw(agentnum state) ) { + $search_hash{$_} = $cgi->param($_) if $cgi->param($_); +} +$search_hash{'status'} = 'active'; +$search_hash{'country'} = 'US'; +$search_hash{'classnum'} = [ $cgi->param('classnum') ]; + +# arrays of report_option_ numbers, running parallel to +# the download and upload speed arrays +my @download_option = $cgi->param('part1_column_option'); +my @upload_option = $cgi->param('part1_row_option'); + +my @technology_option = &FS::Report::FCC_477::parse_technology_option($cgi); + +my $total_count = 0; +my $total_residential = 0; +my $above_200 = 0; +my $tech_code = $opt{tech_code}; +my $technology = $FS::Report::FCC_477::technology[$tech_code] || 'unknown'; +my $title = "Part IA $technology"; +my $xml_prefix = 'PartIA_'. chr(65 + $tech_code); +my $xml_percent = 'Part_p_IA_'. chr(65 + $tech_code); # yes, seriously + +# whether to show the results as a matrix (upload speeds in rows) or a single +# row +my $asymmetric = 1; +if ( $technology eq 'Symmetric xDSL' or $technology eq 'Other Wireline' ) { + $asymmetric = 0; + @upload_option = ( undef ); +} +# whether to show residential percentages in each cell of the matrix +my $percentages = ($technology eq 'Terrestrial Mobile Wireless'); + +my $query = FS::cust_pkg->search(\%search_hash); +my $count_query = $query->{'count_query'}; + +my $is_residential = " AND COALESCE(cust_main.company, '') = ''"; +my $has_option = sub { + my $optionnum = shift; + $optionnum =~ /^\d+$/ ? + " AND EXISTS( + SELECT 1 FROM part_pkg_option + WHERE part_pkg_option.pkgpart = part_pkg.pkgpart + AND optionname = 'report_option_$optionnum' + AND optionvalue = '1' + )" : ''; +}; + +# limit to those that have technology option $tech_code +$count_query .= $has_option->($technology_option[$tech_code]); + +my @data; +for ( my $row = 0; $row < scalar @upload_option; $row++ ) { + for ( my $col = 0; $col < scalar @download_option; $col++ ) { + + my $this_count_query = $count_query . + $has_option->($upload_option[$row]) . + $has_option->($download_option[$col]); + + my $count = FS::Record->scalar_sql($this_count_query); + my $residential = FS::Record->scalar_sql($this_count_query . $is_residential); + + my $percent = sprintf('%.2f', $count ? 100 * $residential / $count : 0); + $data[$col][$row] = [ $count, $percent ]; + + $total_count += $count; + $total_residential += $residential; + $above_200 += $residential if $row > 0 or !$asymmetric; + } +} + +my $total_percentage = + sprintf("%.2f", $total_count ? 100*$total_residential/$total_count : 0); + +my $above_200_percentage = + sprintf("%.2f", $total_count ? 100*$above_200/$total_count : 0); + +my @summary_row = ( + $total_count, + 100.00, # own local loop--consistent with previous practice, but probably wrong + 100.00, # billed to end user--also wrong + $total_percentage, # residential percentage + $above_200_percentage, +); + +</%init> diff --git a/httemplate/search/477partIA_detail.html b/httemplate/search/477partIA_detail.html deleted file mode 100755 index 66f3a8651..000000000 --- a/httemplate/search/477partIA_detail.html +++ /dev/null @@ -1,129 +0,0 @@ -<% include( 'elements/search.html', - 'html_init' => $html_init, - 'name' => 'lines', - 'query' => $query, - 'count_query' => $count_query, - 'really_disable_download' => 1, - 'disable_download' => 1, - 'nohtmlheader' => 1, - 'disable_total' => 1, - 'header' => [ '', @column_option_name ], - 'xml_elements' => [ @xml_elements ], - 'xml_omit_empty' => 1, - 'fields' => [ @fields ], - ) -%> -<%init> - -my $curuser = $FS::CurrentUser::CurrentUser; - -die "access denied" - unless $curuser->access_right('List packages'); - -my %opt = @_; -my %search_hash = (); - -for ( qw(agentnum magic state) ) { - $search_hash{$_} = $cgi->param($_) if $cgi->param($_); -} -$search_hash{'country'} = 'US'; - -$search_hash{'classnum'} = [ $cgi->param('classnum') ]; - -my @column_option = grep { /^\d+/ } $cgi->param('part1_column_option') - if $cgi->param('part1_column_option'); - -my @row_option = grep { /^\d+/ } $cgi->param('part1_row_option') - if $cgi->param('part1_row_option'); - -my @technology_option = &FS::Report::FCC_477::parse_technology_option($cgi); - -my @column_option_name = scalar(@column_option) - ? ( map { my $part_pkg_report_option = - qsearchs({ 'table' => 'part_pkg_report_option', - 'hashref' => { num => $_ }, - }); - $part_pkg_report_option ? $part_pkg_report_option->name - : 'no such report option'; - } @column_option - ) - : ( 'all packages' ); - -my $where = join(' OR ', map { "num = $_" } @row_option ); -my %row_option_name = $where ? - ( map { $_->num => $_->name } - qsearch({ 'table' => 'part_pkg_report_option', - 'hashref' => {}, - 'extra_sql' => "WHERE $where", - }) - ) : - (); - -my $tech_code = $opt{tech_code}; -my $technology = $FS::Report::FCC_477::technology[$tech_code] || 'unknown'; -my $html_init = "<H2>Part IA $technology breakdown by speeds</H2>"; -my $xml_prefix = 'PartIA_'. chr(65 + $tech_code); - -if ($cgi->param('_type') eq 'xml') { - #rotate data pi/2 - my @temp = @column_option; - @column_option = @row_option; - @row_option = @temp; -} - -my $query = 'SELECT '. join(' UNION ALL SELECT ',@row_option); -my $count_query = 'SELECT '. scalar(@row_option); - -my $xml_element = 'OOPS, I was never set'; -my $rowchar = 101; # 'e' -- rows are columns! (pi/2) - -my $value = sub { - my ($rowref, $column) = (shift, shift); - my $row = $rowref->[0]; - - if ($column eq 'name') { - return $row_option_name{$row} || 'no such report option'; - } elsif ( $column =~ /^(\d+)$/ ) { - my @report_option = ( $row || '', - $column_option[$column] || '', - $technology_option[$tech_code] || '', - ); - - my ( $count, $residential ) = FS::cust_pkg->fcc_477_count( - { %search_hash, 'report_option' => join(',', @report_option) } - ); - - my $percentage = sprintf('%.2f', $count ? 100 * $residential / $count : 0); - my $return = $count; - - if ($cgi->param('_type') eq 'xml') { - $rowchar++ if $column == 0; - $xml_element = $xml_prefix. chr($rowchar). ($column+1); - $return = '' if $count == 0 and $cgi->param('_type') eq 'xml'; - } else { - $return .= "<BR>$percentage% residential"; - } - - return $return; - } else { - return '<FONT SIZE="+1" COLOR="#ff0000">Bad call to column_value</FONT>'; - } -}; - -my @fields = map { my $ci = $_; sub { &{$value}(shift, $ci); } } - ( 'name', (0 .. $#column_option) ); -shift @fields if $cgi->param('_type') eq 'xml'; - -my @xml_elements = ( # -- columns are rows! (pi/2) - sub { return $xml_element; }, - sub { return $xml_element; }, - sub { return $xml_element; }, - sub { return $xml_element; }, - sub { return $xml_element; }, - sub { return $xml_element; }, - sub { return $xml_element; }, - sub { return $xml_element; }, - sub { return $xml_element; }, -); - -</%init> diff --git a/httemplate/search/477partIA_summary.html b/httemplate/search/477partIA_summary.html deleted file mode 100755 index f5c2bc251..000000000 --- a/httemplate/search/477partIA_summary.html +++ /dev/null @@ -1,89 +0,0 @@ -<% include( 'elements/search.html', - 'html_init' => $html_init, - 'name' => 'lines', - 'query' => 'SELECT 1', - 'count_query' => 'SELECT 1', - 'really_disable_download' => 1, - 'disable_download' => 1, - 'nohtmlheader' => 1, - 'disable_total' => 1, - 'header' => [ - 'Total Connections', - '% owned loop', - '% billed to end users', - '% residential', - '% residential > 200kbps', - ], - 'xml_elements' => [ - $xml_prefix. 'a1', - $xml_prefix. 'b1', - $xml_prefix. 'c1', - $xml_prefix. 'd1', - $xml_prefix. 'e1', - ], - 'fields' => [ - sub { $total_count }, - sub { '100.00' }, - sub { '100.00' }, - sub { $total_percentage }, - sub { $above_200_percentage }, - ], - ) -%> -<%init> - -my $curuser = $FS::CurrentUser::CurrentUser; - -die "access denied" - unless $curuser->access_right('List packages'); - -my %opt = @_; -my %search_hash = (); - -for ( qw(agentnum magic state) ) { - $search_hash{$_} = $cgi->param($_) if $cgi->param($_); -} -$search_hash{'country'} = 'US'; -$search_hash{'classnum'} = [ $cgi->param('classnum') ]; - -my @column_option = grep { /^\d+$/ } $cgi->param('part1_column_option') - if $cgi->param('part1_column_option'); - -my @row_option = grep { /^\d+$/ } $cgi->param('part1_row_option') - if $cgi->param('part1_row_option'); - -my @technology_option = &FS::Report::FCC_477::parse_technology_option($cgi); - -my $total_count = 0; -my $total_residential = 0; -my $above_200 = 0; -my $tech_code = $opt{tech_code}; -my $technology = $FS::Report::FCC_477::technology[$tech_code] || 'unknown'; -my $html_init = "<H2>Part IA $technology totals</H2>"; -my $xml_prefix = 'PartIA_'. chr(65 + $tech_code); - -my $not_first_row = 0; # ugh; -foreach my $row ( @row_option ) { - foreach my $column ( @column_option ) { - - my @report_option = ( $row || '-1', $column || '-1', $technology_option[$tech_code] ); - - my ( $count, $residential ) = FS::cust_pkg->fcc_477_count( - { %search_hash, 'report_option' => join(',', @report_option) } - ); - - $total_count += $count; - $total_residential += $residential; - $above_200 += $residential if $not_first_row; - } - $not_first_row++; -} - -my $total_percentage = - sprintf("%.2f", $total_count ? 100*$total_residential/$total_count : 0); - -my $above_200_percentage = - sprintf("%.2f", $total_count ? 100*$above_200/$total_count : 0); - - -</%init> diff --git a/httemplate/search/477partIIA.html b/httemplate/search/477partIIA.html index d2cc8c3e9..95c00a3e0 100755 --- a/httemplate/search/477partIIA.html +++ b/httemplate/search/477partIIA.html @@ -1,17 +1,44 @@ -<% include( 'elements/search.html', - 'html_init' => $html_init, - 'name' => 'lines', - 'query' => $query, - 'count_query' => 'SELECT 11', - 'really_disable_download' => 1, - 'disable_download' => 1, - 'nohtmlheader' => 1, - 'disable_total' => 1, - 'header' => [ @headers ], - 'xml_elements' => [ @xml_elements ], - 'fields' => [ @fields ], - ) -%> +% if ( $cgi->param('_type') eq 'xml' ) { +% my @cols = qw(a b c d); +% for ( my $row = 0; $row < scalar(@rows); $row++ ) { +% for my $col (0..3) { +% if ( exists($data[$col][$row]) and $data[$col][$row] > 0 ) { +<PartII_<% $row + 1 %><% $cols[$col] %>>\ +<% $data[$col][$row] %>\ +</PartII_<% $row + 1 %><% $cols[$col] %>> +% } +% } #for $col +% } #for $row +% } else { # HTML mode +% # fake up the search-html.html header +<H2>Part IIA</H2> +<TABLE> + <TR><TD VALIGN="bottom"><BR></TD></TR> + <TR><TD COLSPAN=2> + <TABLE CLASS="grid" CELLSPACING=0> + <TR> +% foreach (@row1_headers) { + <TH><% $_ %></TH> +% } + </TR> +% my $row = 0; +% foreach my $rowhead (@rows) { + <TR CLASS="row<%$row % 2%>"> + <TD STYLE="text-align: left; font-weight: bold"><% $rowhead %></TD> +% for my $col (0..3) { + <TD> +% if ( exists($data[$col][$row]) ) { + <% $data[$col][$row] %> +% } + </TD> +% } # for $col + </TR> +% $row++; +% } #for $rowhead + </TABLE> + </TD></TR> +</TABLE> +% } #XML/HTML <%init> my $curuser = $FS::CurrentUser::CurrentUser; @@ -19,83 +46,76 @@ my $curuser = $FS::CurrentUser::CurrentUser; die "access denied" unless $curuser->access_right('List packages'); -my $html_init = '<H2>Part IIA</H2>'; my %search_hash = (); - -for ( qw(agentnum magic state) ) { - $search_hash{$_} = $cgi->param($_) if $cgi->param($_); -} -$search_hash{'country'} = 'US'; -$search_hash{'classnum'} = [ $cgi->param('classnum') ]; - -my @row_option = grep { /^\d+$/ } $cgi->param('part2a_row_option') - if $cgi->param('part2a_row_option'); - -# fudge in two rows of LD carrier -unshift @row_option, $row_option[0]; - -# fudge in the first pair of rows -unshift @row_option, ''; -unshift @row_option, ''; - -my $query = 'SELECT '. join(' UNION SELECT ', 1..11); -my $total_count = 0; -my $column_value = sub { - my $row = shift; - - my @report_option = ( $row_option[$row - 1] || '' ); - - my $sql_query = FS::cust_pkg->search( - { %search_hash, 'report_option' => join(',', @report_option) } - ); - - my $count_sql = delete($sql_query->{'count_query'}); - if ( $row == 2 || $row == 4 ) { - $count_sql =~ s/COUNT\(\*\) FROM/sum(COALESCE(CASE WHEN cust_main.company IS NULL OR cust_main.company = '' THEN CASE WHEN part_pkg.fcc_ds0s IS NOT NULL AND part_pkg.fcc_ds0s > 0 THEN part_pkg.fcc_ds0s WHEN pkg_class.fcc_ds0s IS NOT NULL AND pkg_class.fcc_ds0s > 0 THEN pkg_class.fcc_ds0s ELSE 0 END ELSE 0 END, 0) ) FROM/ - or die "couldn't parse count_sql"; - } else { - $count_sql =~ s/COUNT\(\*\) FROM/sum(COALESCE(CASE WHEN part_pkg.fcc_ds0s IS NOT NULL AND part_pkg.fcc_ds0s > 0 THEN part_pkg.fcc_ds0s WHEN pkg_class.fcc_ds0s IS NOT NULL AND pkg_class.fcc_ds0s > 0 THEN pkg_class.fcc_ds0s ELSE 0 END, 0)) FROM/ - or die "couldn't parse count_sql"; - } - - 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]; +$search_hash{'agentnum'} = $cgi->param('agentnum'); +$search_hash{'state'} = $cgi->param('state'); +$search_hash{'classnum'} = [ $cgi->param('classnum') ]; +$search_hash{'status'} = 'active'; - $total_count = $count if $row == 1; - $count = sprintf('%.2f', $total_count ? 100*$count/$total_count : 0) - if $row != 1; +my @row_option; +foreach ($cgi->param('part2a_row_option')) { + push @row_option, (/^\d+$/ ? $_ : undef); +} - return "$count"; +my $is_residential = "AND COALESCE(cust_main.company, '') = ''"; +my $has_report_option = sub { + map { + defined($row_option[$_]) ? + " AND EXISTS( + SELECT 1 FROM part_pkg_option + WHERE part_pkg_option.pkgpart = part_pkg.pkgpart + AND optionname = 'report_option_" . $row_option[$_]."' + AND optionvalue = '1' + )" : ' AND FALSE' + } @_ }; -my @headers = ( - '', - 'End user lines', - 'UNE-P replacement', - 'UNE (unswitched)', - 'UNE-P', +# an arrayref for each column +my @data; +# get the skeleton of the query +my $sql_query = FS::cust_pkg->search(\%search_hash); +my $from_where = $sql_query->{'count_query'}; +$from_where =~ s/^SELECT COUNT\(\*\) //; + +# for row 1 +my $query_ds0 = "SELECT SUM(COALESCE(part_pkg.fcc_ds0s, pkg_class.fcc_ds0s, 0)) + $from_where AND fcc_voip_class = '4'"; # 4 = Local Exchange + +my $total_lines = FS::Record->scalar_sql($query_ds0); +# always return zero for the number of resold lines, until an actual ILEC +# starts using this report + +@data = ( + [ $total_lines ], + [ 0 ], + [ 0 ], + [ 0 ], ); -my @xml_elements = ( - sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}a" }, - sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}b" }, - sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}c" }, - sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}d" }, +my @row_conds = ( + $is_residential, + $has_report_option->(0), # LD carrier + ($has_report_option->(0))[0] . $is_residential, + $has_report_option->(1..7), ); +if ( $total_lines > 0 ) { + foreach (@row_conds) { + my $sql = $query_ds0 . $_; + my $lines = FS::Record->scalar_sql($sql); + my $percent = sprintf('%.2f', 100 * $lines / $total_lines); + push @{ $data[0] }, $percent; + } +} my @rows = ( 'lines', '% residential', '% LD carrier', - '% residential and LD carrier', - '% own loops', - '% obtained unswitched UNE loops', + '% residential and LD', + '% owned loops', + '% unswitched UNE', '% UNE-P', '% UNE-P replacement', '% FTTP', @@ -103,13 +123,12 @@ my @rows = ( '% wireless', ); -my @fields = ( - sub { my $row = shift; $rows[$row->[0] - 1]; }, - sub { my $row = shift; &{$column_value}($row->[0]); }, - sub { 0; }, - sub { 0; }, - sub { 0; }, +my @row1_headers = ( + '', + 'End user lines', + 'UNE-P replacement', + 'unswitched UNE', + 'UNE-P', ); -shift @fields if $cgi->param('_type') eq 'xml'; </%init> diff --git a/httemplate/search/477partIIB.html b/httemplate/search/477partIIB.html index c58310d36..5b9b30769 100755 --- a/httemplate/search/477partIIB.html +++ b/httemplate/search/477partIIB.html @@ -3,9 +3,10 @@ % for ( my $row = 0; $row < scalar(@rows); $row++ ) { % for my $col (0..2) { % if ( exists($data[$col][$row]) ) { -<PartII_<% $row %><% $cols[$col] %>> +<PartII_<% $row + 1 %><% $cols[$col] %>>\ +<% $data[$col][$row] %>\ +</PartII_<% $row + 1 %><% $cols[$col] %>> % } -</PartII_<% $row %><% $cols[$col] %>> % } #for $col % } #for $row % } else { # HTML mode @@ -14,19 +15,18 @@ <TABLE> <TR><TD VALIGN="bottom"><BR></TD></TR> <TR><TD COLSPAN=2> - <TABLE CLASS="grid" CELLSPACING=0 STYLE="border: 1px solid #cccccc;" BGCOLOR="#cccccc"> + <TABLE CLASS="grid" CELLSPACING=0> <TR> % foreach (@headers) { - <TH class="grid"><% $_ %></TH> + <TH><% $_ %></TH> % } </TR> -% my @bgcolor = ('eeeeee','ffffff'); % my $row = 0; % foreach my $rowhead (@rows) { - <TR> - <TD CLASS="grid" BGCOLOR="#<% $bgcolor[$row % 2] %>"><% $rowhead %></TD> + <TR CLASS="row<% $row % 2 %>"> + <TD STYLE="text-align: left; font-weight: bold"><% $rowhead %></TD> % for my $col (0..2) { - <TD CLASS="grid" BGCOLOR="#<% $bgcolor[$row % 2] %>"> + <TD> % if ( exists($data[$col][$row]) ) { <% $data[$col][$row] %> % } diff --git a/httemplate/search/477partV.html b/httemplate/search/477partV.html index 2fd5119d1..b2dd9ca95 100755 --- a/httemplate/search/477partV.html +++ b/httemplate/search/477partV.html @@ -1,4 +1,7 @@ -<% include( 'elements/search.html', +% if ( $cgi->param('_type') =~ /^xml$/ ) { +<zip_code> +% } +<& elements/search.html, 'html_init' => $html_init, 'name' => 'zip code', 'query' => $sql_query, @@ -12,8 +15,11 @@ 'url' => $opt{url} || '', 'really_disable_download' => 1, - ) -%> + +&> +% if ( $cgi->param('_type') =~ /^xml$/ ) { +</zip_code> +% } <%init> my $curuser = $FS::CurrentUser::CurrentUser; @@ -32,8 +38,8 @@ for ( qw(agentnum magic state) ) { } $search_hash{'country'} = 'US'; $search_hash{'classnum'} = [ $cgi->param('classnum') ]; -$search_hash{report_option} = $cgi->param('partv_report_option') - if $cgi->param('partv_report_option'); +$search_hash{report_option} = $cgi->param('part5_report_option') + if $cgi->param('part5_report_option'); my $sql_query = FS::cust_pkg->search( { %search_hash, 'fcc_line' => 1, diff --git a/httemplate/search/477partVI_census.html b/httemplate/search/477partVI_census.html index 8425c4b48..59a6fb50d 100755 --- a/httemplate/search/477partVI_census.html +++ b/httemplate/search/477partVI_census.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'html_init' => '<H2>Part VI</H2>', 'html_foot' => $html_foot, 'name' => 'regions', @@ -24,8 +24,8 @@ 'url' => $opt{url} || '', 'xml_row_element' => 'Datarow', 'really_disable_download' => 1, - ) -%> + +&> <%init> my $curuser = $FS::CurrentUser::CurrentUser; diff --git a/httemplate/search/agent_commission.html b/httemplate/search/agent_commission.html new file mode 100644 index 000000000..b94ae9f6e --- /dev/null +++ b/httemplate/search/agent_commission.html @@ -0,0 +1,197 @@ +%# still not a good way to do rows grouped by some field in a search.html +%# report +% if ( $type eq 'xls' ) { +<% $data %>\ +% } else { +<& /elements/header.html, $title &> +<P ALIGN="right" CLASS="noprint"> +Download full results<BR> +as <A HREF="<% $cgi->self_url %>;_type=xls">Excel spreadsheet</A></P> +<BR> +<STYLE TYPE="text/css"> +td.cust_head { + border-left: none; + border-right: none; + padding-top: 0.5em; + font-weight: bold; + background-color: #ffffff; +} +td.money { text-align: right; } +td.money:before { content: '<% $money_char %>'; } +.row0 { background-color: #eeeeee; } +.row1 { background-color: #ffffff; } +</STYLE> +<& /elements/table-grid.html &> + <TR STYLE="background-color: #cccccc"> + <TH CLASS="grid">Package</TH> + <TH CLASS="grid">Sales</TH> + <TH CLASS="grid">Percentage</TH> + <TH CLASS="grid">Commission</TH> + </TR> +% my ($custnum, $sales, $commission, $row, $bgcolor) = (0, 0, 0, 0); +% foreach my $cust_pkg ( @cust_pkg ) { +% if ( $custnum ne $cust_pkg->custnum ) { +% # start of a new customer section +% my $cust_main = $cust_pkg->cust_main; +% my $label = $cust_main->custnum . ': '. $cust_main->name; +% $bgcolor = 0; + <TR> + <TD COLSPAN=4 CLASS="cust_head"> + <A HREF="<%$p%>view/cust_main.cgi?<%$cust_main->custnum%>"><% $label %></A> + </TD> + </TR> +% } + <TR CLASS="row<% $bgcolor %>"> + <TD CLASS="grid"><% $cust_pkg->pkg_label %></TD> + <TD CLASS="money"><% sprintf('%.2f', $cust_pkg->sum_charged) %></TD> + <TD ALIGN="right"><% $cust_pkg->percent %>%</TD> + <TD CLASS="money"><% sprintf('%.2f', + $cust_pkg->sum_charged * $cust_pkg->percent / 100) %></TD> + </TR> +% $sales += $cust_pkg->sum_charged; +% $commission += $cust_pkg->sum_charged * $cust_pkg->percent / 100; +% $row++; +% $bgcolor = 1-$bgcolor; +% $custnum = $cust_pkg->custnum; +% } + <TR STYLE="background-color: #f5f6be"> + <TD CLASS="grid"> + <% emt('[quant,_1,package] with commission', $row) %> + </TD> + <TD CLASS="money"><% sprintf('%.2f', $sales) %></TD> + <TD></TD> + <TD CLASS="money"><% sprintf('%.2f', $commission) %></TD> + </TR> +</TABLE> +<& /elements/footer.html &> +% } +<%init> +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my ($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi); +$cgi->param('agentnum') =~ /^(\d+)$/ or die "bad agentnum"; +my $agentnum = $1; +my $agent = FS::agent->by_key($agentnum); + +my $title = $agent->agent . ' commissions'; + +my $sum_charged = + '(SELECT SUM(setup + recur) FROM cust_bill_pkg JOIN cust_bill USING (invnum)'. + 'WHERE cust_bill_pkg.pkgnum = cust_pkg.pkgnum AND '. + "cust_bill._date >= $begin AND cust_bill._date < $end)"; + +my @select = ( + 'cust_pkg.*', + 'agent_pkg_class.commission_percent AS percent', + "$sum_charged AS sum_charged", +); + +my $query = { + 'table' => 'cust_pkg', + 'select' => join(',', @select), + 'addl_from' => 'JOIN cust_main USING (custnum) '. + 'JOIN part_pkg USING (pkgpart) '. + 'JOIN agent_pkg_class ON ( '. + 'cust_main.agentnum = agent_pkg_class.agentnum AND '. + '( agent_pkg_class.classnum = part_pkg.classnum OR '. + '(agent_pkg_class IS NULL AND part_pkg.classnum IS NULL)'. + ' ) ) ', + 'extra_sql' => "WHERE cust_main.agentnum = $agentnum AND ". + 'agent_pkg_class.commission_percent > 0 AND '. + "$sum_charged > 0", + 'order_by' => 'ORDER BY cust_pkg.custnum ASC', +}; + +my @cust_pkg = qsearch($query); + +my $money_char = FS::Conf->new->config('money_char') || '$'; + +my $data = ''; +my $type = $cgi->param('_type'); +if ( $type eq 'xls') { + # some false laziness with the above... + my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format; + my $filename = 'agent_commission' . $format->{extension}; + http_header('Content-Type' => $format->{mime_type}); + http_header('Content-Disposition' => qq!attachment;filename="$filename"!); + my $XLS = IO::Scalar->new(\$data); + my $workbook = $format->{class}->new($XLS); + my $worksheet = $workbook->add_worksheet(substr($title, 0, 31)); + + my $cust_head_format = $workbook->add_format( + bold => 1, + underline => 1, + text_wrap => 0, + bg_color => 'white', + ); + + my $col_head_format = $workbook->add_format( + bold => 1, + align => 'center', + bg_color => 'silver' + ); + + my @format; + foreach (0, 1) { + my %bg = (bg_color => $_ ? 'white' : 'silver'); + $format[$_] = { + 'text' => $workbook->add_format(%bg), + 'money' => $workbook->add_format(%bg, num_format => $money_char.'#0.00'), + 'percent' => $workbook->add_format(%bg, num_format => '0.00%'), + }; + } + my $total_format = $workbook->add_format( + bg_color => 'yellow', + num_format => $money_char.'#0.00', + top => 1 + ); + + my ($r, $c) = (0, 0); + foreach (qw(Package Sales Percentage Commission)) { + $worksheet->write($r, $c++, $_, $col_head_format); + } + $r++; + + my ($custnum, $sales, $commission, $row, $bgcolor) = (0, 0, 0, 0); + my $label_length = 0; + foreach my $cust_pkg ( @cust_pkg ) { + if ( $custnum ne $cust_pkg->custnum ) { + # start of a new customer section + my $cust_main = $cust_pkg->cust_main; + my $label = $cust_main->custnum . ': '. $cust_main->name; + $bgcolor = 0; + $worksheet->set_row($r, 20); + $worksheet->merge_range($r, 0, $r, 3, $label, $cust_head_format); + $r++; + } + $c = 0; + my $percent = $cust_pkg->percent / 100; + $worksheet->write($r, $c++, $cust_pkg->pkg_label, $format[$bgcolor]{text}); + $worksheet->write($r, $c++, $cust_pkg->sum_charged, $format[$bgcolor]{money}); + $worksheet->write($r, $c++, $percent, $format[$bgcolor]{percent}); + $worksheet->write($r, $c++, ($cust_pkg->sum_charged * $percent), + $format[$bgcolor]{money}); + + $label_length = max($label_length, length($cust_pkg->pkg_label)); + $sales += $cust_pkg->sum_charged; + $commission += $cust_pkg->sum_charged * $cust_pkg->percent / 100; + $row++; + $bgcolor = 1-$bgcolor; + $custnum = $cust_pkg->custnum; + $r++; + } + + $c = 0; + $label_length = max($label_length, 20); + $worksheet->set_column($c, $c, $label_length); + $worksheet->write($r, $c++, mt('[quant,_1,package] with commission', $row), + $total_format); + $worksheet->set_column($c, $c + 2, 11); + $worksheet->write($r, $c++, $sales, $total_format); + $worksheet->write($r, $c++, '', $total_format); + $worksheet->write($r, $c++, $commission, $total_format); + + $workbook->close; +} +</%init> diff --git a/httemplate/search/agent_inventory.html b/httemplate/search/agent_inventory.html index ac65371ca..015aca46b 100644 --- a/httemplate/search/agent_inventory.html +++ b/httemplate/search/agent_inventory.html @@ -1,4 +1,4 @@ -<% include('elements/search.html', +<& elements/search.html, 'title' => 'Inventory summary per agent', 'name_singular' => 'agent', 'query' => { 'table' => 'agent', @@ -10,8 +10,7 @@ " AND $agentnums_sql", 'header' => \@header, 'fields' => \@fields, - ) -%> +&> <%init> die "access denied" diff --git a/httemplate/search/bill_batch.cgi b/httemplate/search/bill_batch.cgi index b6676f261..b740bdc68 100755 --- a/httemplate/search/bill_batch.cgi +++ b/httemplate/search/bill_batch.cgi @@ -26,7 +26,7 @@ function start() { % -expires => '-1d', % ); % $r->headers_out->add( 'Set-Cookie' => $cookie->as_string ); -<% include( 'elements/search.html', +<& elements/search.html, 'title' => 'Invoice Batches', 'name_singular' => 'batch', 'query' => { 'table' => 'bill_batch', @@ -67,9 +67,7 @@ function start() { 'agent_pos' => 1, 'html_foot' => include('.foot'), - ) - -%> +&> %} <%def .foot> <SCRIPT type="text/javascript"> diff --git a/httemplate/search/cdr.html b/httemplate/search/cdr.html index d0d7292d1..ca303d386 100644 --- a/httemplate/search/cdr.html +++ b/httemplate/search/cdr.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => $title, 'name' => 'call detail records', 'query' => $query, @@ -9,27 +9,8 @@ 'fields' => \@fields, 'links' => \@links, 'html_form' => qq!<FORM NAME="cdrForm" ACTION="$p/misc/cdr.cgi" METHOD="POST">!, - #false laziness w/queue.html - 'html_foot' => sub { - if ( $areboxes ) { - '<BR><INPUT TYPE="button" VALUE="select all" onClick="setAll(true)">'. - '<INPUT TYPE="button" VALUE="unselect all" onClick="setAll(false)">'. - qq!<BR><INPUT TYPE="submit" NAME="action" VALUE="reprocess selected" onClick="return confirm('Are you sure you want to reprocess the selected CDRs?')">!. - qq!<INPUT TYPE="submit" NAME="action" VALUE="delete selected" onClick="return confirm('Are you sure you want to delete the selected CDRs?')"><BR>!. - '<SCRIPT TYPE="text/javascript">'. - ' function setAll(setTo) { '. - ' theForm = document.cdrForm;'. - ' for (i=0,n=theForm.elements.length;i<n;i++)'. - ' if (theForm.elements[i].name.indexOf("acctid") != -1)'. - ' theForm.elements[i].checked = setTo;'. - ' }'. - '</SCRIPT>'; - } else { - ''; - } - }, - ) -%> + 'html_foot' => $html_foot, +&> <%init> die "access denied" @@ -44,8 +25,6 @@ my $totalminutes_sub = sub { my $conf = new FS::Conf; -my $areboxes = 0; - my $title = 'Call Detail Records'; my $hashref = {}; @@ -355,7 +334,6 @@ my %links = ( @fields = map { exists($fields{$_}) ? $fields{$_} : $_ } @fields; unshift @fields, sub { return '' unless $edit_data; - $areboxes = 1; my $cdr = shift; my $acctid = $cdr->acctid; qq!<INPUT NAME="acctid$acctid" TYPE="checkbox" VALUE="1">!; @@ -409,4 +387,14 @@ if ( $topmode ) { $nototalminutes = 1; } +my $html_foot = include('/search/elements/checkbox-foot.html', + actions => [ + { submit => "reprocess selected", + name => "action", + confirm => "Are you sure you want to reprocess the selected CDRs?" }, + { submit => "delete selected", + name => "action", + confirm => "Are you sure you want to delete the selected CDRs?" }, + ] +); </%init> diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html index 406486a85..473aed311 100755 --- a/httemplate/search/cust_bill.html +++ b/httemplate/search/cust_bill.html @@ -62,7 +62,7 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('List invoices'); -my $join_cust_main = 'LEFT JOIN cust_main USING ( custnum )'; +my $join_cust_main = FS::UI::Web::join_cust_main('cust_bill'); #here is the agent virtualization my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql; @@ -97,6 +97,10 @@ if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { $search{'refnum'} = $1; } +if ( grep { $_ eq 'cust_classnum' } $cgi->param ) { + $search{'cust_classnum'} = [ $cgi->param('cust_classnum') ]; + } + if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { $search{'custnum'} = $1; } @@ -194,7 +198,6 @@ if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { }; } - my $link = [ "${p}view/cust_bill.cgi?", 'invnum', ]; my $clink = sub { my $cust_bill = shift; diff --git a/httemplate/search/cust_bill_event.cgi b/httemplate/search/cust_bill_event.cgi index 90c89139c..9fb533a5f 100644 --- a/httemplate/search/cust_bill_event.cgi +++ b/httemplate/search/cust_bill_event.cgi @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => $title, 'html_init' => $html_init, 'menubar' => $menubar, @@ -60,8 +60,8 @@ '', FS::UI::Web::cust_styles(), ], - ) -%> + +&> <%init> my $curuser = $FS::CurrentUser::CurrentUser; @@ -100,7 +100,7 @@ my $where = 'WHERE '. FS::cust_bill_event->search_sql_where( \%search ); my $join = 'LEFT JOIN part_bill_event USING ( eventpart ) '. 'LEFT JOIN cust_bill USING ( invnum ) '. - 'LEFT JOIN cust_main USING ( custnum ) '; + FS::UI::Web::join_cust_main('cust_bill'); my $sql_query = { 'table' => 'cust_bill_event', diff --git a/httemplate/search/cust_bill_pay.html b/httemplate/search/cust_bill_pay.html index 22e9a6795..ff20458d8 100644 --- a/httemplate/search/cust_bill_pay.html +++ b/httemplate/search/cust_bill_pay.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => $title, 'name' => 'net payments', 'query' => $sql_query, @@ -71,8 +71,8 @@ '', FS::UI::Web::cust_styles(), ], - ) -%> + +&> <%init> die "access denied" @@ -99,6 +99,16 @@ if ( $cgi->param('refnum') && $cgi->param('refnum') =~ /^(\d+)$/ ) { $title = $part_referral->referral. " $title"; } +# cust_classnum (false laziness w/ elements/cust_main_dayranges.html, prepaid_income.html, cust_bill_pkg.html, cust_bill_pkg_referral.html, unearned_detail.html, cust_credit.html, cust_credit_refund.html, cust_main::Search::search_sql) +if ( grep { $_ eq 'cust_classnum' } $cgi->param ) { + my @classnums = grep /^\d*$/, $cgi->param('cust_classnum'); + push @search, 'COALESCE( cust_main.classnum, 0) IN ( '. + join(',', map { $_ || '0' } @classnums ). + ' )' + if @classnums; +} + + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); push @search, "cust_bill._date >= $beginning ", "cust_bill._date <= $ending"; @@ -110,8 +120,8 @@ my $where = 'WHERE '. join(' AND ', @search); # my $count_query = 'SELECT COUNT(*), SUM(amount) FROM cust_bill_pay - LEFT JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) '. + LEFT JOIN cust_bill USING ( invnum ) '. + FS::UI::Web::join_cust_main('cust_bill') . $where; my $sql_query = { @@ -130,8 +140,8 @@ my $sql_query = { 'hashref' => {}, 'extra_sql' => $where, 'addl_from' => 'LEFT JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_pay USING ( paynum ) - LEFT JOIN cust_main ON ( cust_bill.custnum = cust_main.custnum )', + LEFT JOIN cust_pay USING ( paynum ) '. + FS::UI::Web::join_cust_main('cust_bill') }; my $cust_bill_link = sub { diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 4c0fa4a56..7d9172aca 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -5,57 +5,98 @@ 'count_query' => $count_query, 'count_addl' => \@total_desc, 'header' => [ + @pkgnum_header, + emt('Pkg Def'), emt('Description'), + @post_desc_header, @peritem_desc, + @currency_desc, emt('Invoice'), emt('Date'), + emt('Paid'), + emt('Credited'), FS::UI::Web::cust_header(), ], 'fields' => [ + @pkgnum, sub { $_[0]->pkgnum > 0 - ? $_[0]->get('pkg') # possibly use override.pkg - : $_[0]->get('itemdesc') # but i think this correct + # possibly use override.pkg but i think this correct + ? $_[0]->get('pkgpart') + : '' }, + sub { $_[0]->pkgnum > 0 + # possibly use override.pkg but i think this correct + ? $_[0]->get('pkg') + : $_[0]->get('itemdesc') + }, + @post_desc, #strikethrough or "N/A ($amount)" or something these when # they're not applicable to pkg_tax search @peritem_sub, + @currency_sub, 'invnum', sub { time2str('%b %d %Y', shift->_date ) }, + sub { sprintf($money_char.'%.2f', shift->get('pay_amount')) }, + sub { sprintf($money_char.'%.2f', shift->get('credit_amount')) }, \&FS::UI::Web::cust_fields, ], 'sort_fields' => [ + @pkgnum_null, + '', '', + @post_desc_null, @peritem, + @currency, 'invnum', '_date', + #'pay_amount', + #'credit_amount', ], 'links' => [ - #'', + @pkgnum_null, '', + '', + @post_desc_null, @peritem_null, + @currency_null, $ilink, $ilink, + $pay_link, + $credit_link, ( map { $_ ne 'Cust. Status' ? $clink : '' } FS::UI::Web::cust_header() ), ], #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(), - 'align' => 'l'. + 'align' => $pkgnum_align. + 'rl'. + $post_desc_align. $peritem_align. - 'rc'. + $currency_align. + 'rcrr'. FS::UI::Web::cust_aligns(), 'color' => [ - #'', + @pkgnum_null, + '', '', + @post_desc_null, @peritem_null, + @currency_null, + '', + '', '', '', FS::UI::Web::cust_colors(), ], 'style' => [ - #'', + @pkgnum_null, + '', '', + @post_desc_null, @peritem_null, + @currency_null, + '', + '', '', '', FS::UI::Web::cust_styles(), @@ -63,13 +104,13 @@ &> <%doc> -Output parameters: +Output control parameters: - distribute: Boolean. If true, recurring fees will be "prorated" for the portion of the package date range (sdate-edate) that falls within the date range of the report. Line items will be limited to those for which this portion is > 0. This disables filtering on invoice date. -- use_usage: Separate usage (cust_bill_pkg_detail records) from +- usage: Separate usage (cust_bill_pkg_detail records) from recurring charges. If set to "usage", will show usage instead of recurring charges. If set to "recurring", will deduct usage and only show the flat rate charge. If not passed, the "recurring charge" column @@ -86,6 +127,8 @@ Filtering parameters: - refnum: Filter on customer reference source. +- cust_classnum: Filter on customer class. + - classnum: Filter on package class. - use_override: Apply "classnum" and "taxclass" filtering based on the @@ -146,25 +189,66 @@ Filtering parameters: </%doc> <%init> -die "access denied" - unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); +my $curuser = $FS::CurrentUser::CurrentUser; + +die "access denied" unless $curuser->access_right('Financial reports'); my $conf = new FS::Conf; my $money_char = $conf->config('money_char') || '$'; my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' ); my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)'); -my @total_desc = ( '%d line items', $money_char.'%.2f total' ); # sprintf strings +my @total_desc = ( $money_char.'%.2f total' ); # sprintf strings + my @peritem = ( 'setup', 'recur' ); my @peritem_desc = ( 'Setup charge', 'Recurring charge' ); -my ($join_cust, $join_pkg ) = ('', ''); -my $use_usage; + +my @currency_desc = (); +my @currency_sub = (); +my @currency = (); +if ( $conf->config('currencies') ) { + @currency_desc = ( 'Setup billed', 'Recurring billed' ); + @currency_sub = ( + map { + my $what = $_; + sub { my $currency = $_[0]->get($what.'_billed_currency') or return ''; + $currency. ' '. currency_symbol($currency, SYM_HTML). + $_[0]->get($what.'_billed_amount'); + }; + } qw( setup recur ) + ); + @currency = ( 'setup_billed_amount', 'recur_billed_amount' ); #for sorting +} + +my @pkgnum_header = (); +my @pkgnum = (); +my @pkgnum_null; +my $pkgnum_align = ''; +if ( $curuser->option('show_pkgnum') ) { + push @select, 'cust_bill_pkg.pkgnum'; + push @pkgnum_header, 'Pkg Num'; + push @pkgnum, sub { $_[0]->pkgnum > 0 ? $_[0]->pkgnum : '' }; + push @pkgnum_null, ''; + $pkgnum_align .= 'r'; +} + +my @post_desc_header = (); +my @post_desc = (); +my @post_desc_null = (); +my $post_desc_align = ''; +if ( $conf->exists('enable_taxclasses') ) { + push @post_desc_header, 'Tax class'; + push @post_desc, 'taxclass'; + push @post_desc_null, ''; + $post_desc_align .= 'l'; + push @select, 'part_pkg.taxclass'; # or should this use override? +} # valid in both the tax and non-tax cases -$join_cust = - " LEFT JOIN cust_bill USING (invnum) - LEFT JOIN cust_main USING (custnum) - "; +my $join_cust = + " LEFT JOIN cust_bill ON (cust_bill_pkg.invnum = cust_bill.invnum)". + # use cust_pkg.locationnum if it exists + FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg'); #agent virtualization my $agentnums_sql = @@ -200,26 +284,41 @@ if ( $cgi->param('refnum') =~ /^(\d+)$/ ) { push @where, "cust_main.refnum = $1"; } -# the non-tax case -if ( $cgi->param('nottax') ) { +# cust_classnum (false laziness w/ elements/cust_main_dayranges.html, elements/cust_pay_or_refund.html, prepaid_income.html, cust_bill_pay.html, cust_bill_pkg_referral.html, unearned_detail.html, cust_credit.html, cust_credit_refund.html, cust_main::Search::search_sql) +if ( grep { $_ eq 'cust_classnum' } $cgi->param ) { + my @classnums = grep /^\d*$/, $cgi->param('cust_classnum'); + push @where, 'COALESCE( cust_main.classnum, 0) IN ( '. + join(',', map { $_ || '0' } @classnums ). + ' )' + if @classnums; +} - push @where, 'cust_bill_pkg.pkgnum > 0'; - # then we want the package and its definition - $join_pkg = +# custnum +if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { + push @where, "cust_main.custnum = $1"; +} + +# we want the package and its definition if available +my $join_pkg = ' LEFT JOIN cust_pkg USING (pkgnum) LEFT JOIN part_pkg USING (pkgpart)'; - my $part_pkg = 'part_pkg'; - if ( $cgi->param('use_override') ) { - # still need the real part_pkg for tax applicability, - # so alias this one - $join_pkg .= " LEFT JOIN part_pkg AS override ON ( - COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = part_pkg.pkgpart - )"; - $part_pkg = 'override'; - } - push @select, 'part_pkg.pkg'; # or should this use override? +my $part_pkg = 'part_pkg'; +if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents" + # still need the real part_pkg for tax applicability, + # so alias this one + $join_pkg .= " LEFT JOIN part_pkg AS override ON ( + COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart + )"; + $part_pkg = 'override'; +} +push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override? + +# the non-tax case +if ( $cgi->param('nottax') ) { + + push @where, 'cust_bill_pkg.pkgnum > 0'; my @tax_where; # will go into a subquery my @exempt_where; # will also go into a subquery @@ -374,16 +473,15 @@ if ( $cgi->param('nottax') ) { } # recur/usage separation - $use_usage = $cgi->param('usage'); - if ( $use_usage eq 'recurring' ) { + if ( $cgi->param('usage') eq 'recurring' ) { my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1); push @select, "($recur_no_usage) AS recur_no_usage"; $peritem[1] = 'recur_no_usage'; $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)"; - $total_desc[1] .= ' (excluding usage)'; + $total_desc[0] .= ' (excluding usage)'; - } elsif ( $use_usage eq 'usage' ) { + } elsif ( $cgi->param('usage') eq 'usage' ) { my $usage = FS::cust_bill_pkg->usage_sql(); push @select, "($usage) AS _usage"; @@ -391,7 +489,7 @@ if ( $cgi->param('nottax') ) { $peritem[1] = '_usage'; $peritem_desc[1] = 'Usage charge'; $total[1] = "SUM($usage)"; - $total_desc[1] .= ' usage charges'; + $total_desc[0] .= ' usage charges'; } } elsif ( $cgi->param('istax') ) { @@ -418,7 +516,7 @@ if ( $cgi->param('nottax') ) { } elsif ( $cgi->param('out') ) { - $join_pkg = ' + $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum) '; push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL'; @@ -429,7 +527,7 @@ if ( $cgi->param('nottax') ) { } else { # not locationtaxid or 'out'--the normal case - $join_pkg = ' + $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum) JOIN cust_main_county USING (taxnum) '; @@ -486,6 +584,15 @@ if ( $cgi->param('nottax') ) { } # nottax / istax + +#total payments +my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount) + FROM cust_bill_pay_pkg + WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum + "; +push @select, "($pay_sub) AS pay_amount"; + + # credit if ( $cgi->param('credit') ) { @@ -544,7 +651,19 @@ if ( $cgi->param('credit') ) { push @peritem_desc, 'Credited', 'By', 'Reason'; push @total, 'SUM(credit_amount)'; push @total_desc, "$money_char%.2f credited"; -} # if credit + +} else { + + #still want a credit total column + + my $credit_sub = " + SELECT SUM(cust_credit_bill_pkg.amount) + FROM cust_credit_bill_pkg + WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum + "; + push @select, "($credit_sub) AS credit_amount"; + +} push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields(); @@ -553,7 +672,7 @@ $where &&= "WHERE $where"; my $query = { 'table' => 'cust_bill_pkg', - 'addl_from' => "$join_cust $join_pkg", + 'addl_from' => "$join_pkg $join_cust", 'hashref' => {}, 'select' => join(",\n", @select ), 'extra_sql' => $where, @@ -562,11 +681,9 @@ my $query = { my $count_query = 'SELECT ' . join(',', @total) . - " FROM cust_bill_pkg $join_cust $join_pkg + " FROM cust_bill_pkg $join_pkg $join_cust $where"; -shift @total_desc; #the first one is implicit - @peritem_desc = map {emt($_)} @peritem_desc; my @peritem_sub = map { my $field = $_; @@ -579,9 +696,17 @@ my @peritem_sub = map { my @peritem_null = map { '' } @peritem; # placeholders my $peritem_align = 'r' x scalar(@peritem); +@currency_desc = map {emt($_)} @currency_desc; +my @currency_null = map { '' } @currency; # placeholders +my $currency_align = 'r' x scalar(@currency); + my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ]; my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; +my $pay_link = ''; #[, 'billpkgnum', ]; +my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ]; + warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n" if $cgi->param('debug'); + </%init> diff --git a/httemplate/search/cust_bill_pkg_discount.html b/httemplate/search/cust_bill_pkg_discount.html index bb8038a44..f598341a0 100644 --- a/httemplate/search/cust_bill_pkg_discount.html +++ b/httemplate/search/cust_bill_pkg_discount.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => 'Discounts', 'name' => 'discounts', 'query' => $query, @@ -68,8 +68,8 @@ '', FS::UI::Web::cust_styles(), ], - ) -%> + +&> <%init> #a little false laziness below w/cust_bill_pkg.cgi @@ -127,12 +127,12 @@ my $join_cust_pkg_discount = 'LEFT JOIN cust_pkg_discount USING (pkgdiscountnum)'; my $join_cust = - ' JOIN cust_bill_pkg USING ( billpkgnum ) - JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) '; + ' JOIN cust_bill USING ( invnum ) '. + FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg'); my $join_pkg = - ' LEFT JOIN cust_pkg ON ( cust_bill_pkg.pkgnum = cust_pkg.pkgnum ) + ' JOIN cust_bill_pkg USING ( billpkgnum ) + LEFT JOIN cust_pkg ON ( cust_bill_pkg.pkgnum = cust_pkg.pkgnum ) LEFT JOIN part_pkg USING ( pkgpart ) '; #LEFT JOIN part_pkg AS override # ON pkgpart_override = override.pkgpart '; @@ -140,7 +140,7 @@ my $join_pkg = my $where = ' WHERE '. join(' AND ', @where); $count_query .= - " FROM cust_bill_pkg_discount $join_cust_pkg_discount $join_cust $join_pkg ". + " FROM cust_bill_pkg_discount $join_cust_pkg_discount $join_pkg $join_cust ". $where; my @select = ( @@ -155,7 +155,7 @@ push @select, 'cust_main.custnum', my $query = { 'table' => 'cust_bill_pkg_discount', - 'addl_from' => "$join_cust_pkg_discount $join_cust $join_pkg", + 'addl_from' => "$join_cust_pkg_discount $join_pkg $join_cust", 'hashref' => {}, 'select' => join(', ', @select ), 'extra_sql' => $where, diff --git a/httemplate/search/cust_bill_pkg_referral.html b/httemplate/search/cust_bill_pkg_referral.html index 77b486021..c4dde32a0 100644 --- a/httemplate/search/cust_bill_pkg_referral.html +++ b/httemplate/search/cust_bill_pkg_referral.html @@ -156,6 +156,15 @@ if ( @refnum ) { push @where, 'cust_main.refnum IN ('.join(',', @refnum).')'; } +# cust_classnum (false laziness w/ elements/cust_main_dayranges.html, elements/cust_pay_or_refund.html, prepaid_income.html, cust_bill_pay.html, cust_bill_pkg.html, unearned_detail.html, cust_credit.html, cust_credit_refund.html, cust_main::Search::search_sql) +if ( grep { $_ eq 'cust_classnum' } $cgi->param ) { + my @classnums = grep /^\d*$/, $cgi->param('cust_classnum'); + push @where, 'COALESCE( cust_main.classnum, 0) IN ( '. + join(',', map { $_ || '0' } @classnums ). + ' )' + if @classnums; +} + if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { push @where, "cust_main.agentnum = $1"; } diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html index 38f03491d..cabf8c002 100755 --- a/httemplate/search/cust_credit.html +++ b/httemplate/search/cust_credit.html @@ -103,6 +103,16 @@ if ( $cgi->param('refnum') && $cgi->param('refnum') =~ /^(\d+)$/ ) { $title = $part_referral->referral. " $title"; } + +# cust_classnum (false laziness w/ elements/cust_main_dayranges.html, elements/cust_pay_or_refund.html, prepaid_income.html, cust_bill_pay.html, cust_bill_pkg.html, cust_bill_pkg_referral.html, unearned_detail.html, cust_credit_refund.html, cust_main::Search::search_sql) +if ( grep { $_ eq 'cust_classnum' } $cgi->param ) { + my @classnums = grep /^\d*$/, $cgi->param('cust_classnum'); + push @search, 'COALESCE( cust_main.classnum, 0) IN ( '. + join(',', map { $_ || '0' } @classnums ). + ' )' + if @classnums; +} + if ( $unapplied ) { push @search, FS::cust_credit->unapplied_sql . ' > 0'; } @@ -131,7 +141,7 @@ my $where = 'WHERE '. join(' AND ', @search); my $count_query = 'SELECT COUNT(*), SUM(amount) '; $count_query .= ', SUM(' . FS::cust_credit->unapplied_sql . ') ' if $unapplied; -$count_query .= 'FROM cust_credit LEFT JOIN cust_main USING ( custnum ) '. +$count_query .= 'FROM cust_credit'. FS::UI::Web::join_cust_main('cust_credit'). $where; my @count_addl = ( $money_char.'%.2f total credited (gross)' ); @@ -142,7 +152,7 @@ my $sql_query = { 'select' => join(', ',@select), 'hashref' => {}, 'extra_sql' => $where, - 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', + 'addl_from' => FS::UI::Web::join_cust_main('cust_credit') }; </%init> diff --git a/httemplate/search/cust_credit_bill.html b/httemplate/search/cust_credit_bill.html index 9fd6a987a..88f897d70 100644 --- a/httemplate/search/cust_credit_bill.html +++ b/httemplate/search/cust_credit_bill.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => $title, 'name' => 'net credits', 'query' => $sql_query, @@ -64,8 +64,8 @@ '', FS::UI::Web::cust_styles(), ], - ) -%> + +&> <%init> die "access denied" @@ -103,8 +103,8 @@ my $where = 'WHERE '. join(' AND ', @search); # my $count_query = 'SELECT COUNT(*), SUM(amount) FROM cust_credit_bill - LEFT JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) '. + LEFT JOIN cust_bill USING ( invnum ) '. + FS::UI::Web::join_cust_main('cust_bill') . $where; my $sql_query = { @@ -121,8 +121,8 @@ my $sql_query = { 'hashref' => {}, 'extra_sql' => $where, 'addl_from' => 'LEFT JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_credit USING ( crednum ) - LEFT JOIN cust_main ON ( cust_bill.custnum = cust_main.custnum )', + LEFT JOIN cust_credit USING ( crednum )'. + FS::UI::Web::join_cust_main('cust_bill') }; my $cust_bill_link = sub { diff --git a/httemplate/search/cust_credit_bill_pkg.html b/httemplate/search/cust_credit_bill_pkg.html index 4612118a2..63d70c27e 100644 --- a/httemplate/search/cust_credit_bill_pkg.html +++ b/httemplate/search/cust_credit_bill_pkg.html @@ -1,10 +1,10 @@ -<% include( 'elements/search.html', - 'title' => 'Tax credits', #well, actually application of - 'name' => 'tax credits', # credit to line item - 'query' => $query, - 'count_query' => $count_query, - 'count_addl' => [ $money_char. '%.2f total', ], - 'header' => [ +<& elements/search.html, + 'title' => 'Credit application detail', #to line item + 'name_singular' => 'credit application', + 'query' => $query, + 'count_query' => $count_query, + 'count_addl' => [ $money_char. '%.2f total', ], + 'header' => [ #'#', 'Amount', @@ -16,13 +16,15 @@ # line item 'Description', + 'Location', + @post_desc_header, #invoice 'Invoice', 'Date', FS::UI::Web::cust_header(), - ], - 'fields' => [ + ], + 'fields' => [ #'creditbillpkgnum', sub { sprintf($money_char.'%.2f', shift->amount ) }, @@ -34,58 +36,72 @@ ? $_[0]->get('pkg') # possibly use override.pkg : $_[0]->get('itemdesc') # but i think this correct }, + $location_sub, + @post_desc, 'invnum', sub { time2str('%b %d %Y', shift->_date ) }, \&FS::UI::Web::cust_fields, - ], - 'sort_fields' => [ + ], + 'sort_fields' => [ 'amount', 'cust_credit_date', '', #'otaker', '', #reason '', #line item description + '', #location + @post_desc_null, 'invnum', '_date', #cust fields - ], - 'links' => [ + ], + 'links' => [ '', '', '', '', '', + '', + @post_desc_null, $ilink, $ilink, ( map { $_ ne 'Cust. Status' ? $clink : '' } FS::UI::Web::cust_header() ), - ], - 'align' => 'rrlllrr'.FS::UI::Web::cust_aligns(), - 'color' => [ + ], + 'align' => 'rrllll'. + $post_desc_align. + 'rr'. + FS::UI::Web::cust_aligns(), + 'color' => [ + '', '', '', '', '', '', + @post_desc_null, '', '', FS::UI::Web::cust_colors(), ], - 'style' => [ + 'style' => [ '', '', '', '', '', '', + @post_desc_null, + '', '', FS::UI::Web::cust_styles(), - ], - ) -%> + ], + +&> <%init> #LOTS of false laziness below w/cust_bill_pkg.cgi +# and a little w/cust_credit.html die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); @@ -98,14 +114,31 @@ my $agentnums_sql = my @where = ( $agentnums_sql ); +if ( $cgi->param('usernum') =~ /^(\d+)$/ ) { + push @where, "cust_credit.usernum = $1"; +} + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); push @where, "cust_bill._date >= $beginning", "cust_bill._date <= $ending"; +my($cr_begin, $cr_end) = FS::UI::Web::parse_beginning_ending($cgi, 'credit'); +push @where, "cust_credit._date >= $cr_begin", + "cust_credit._date <= $cr_end"; + +#credit amount? seems more what is expected than the applied amount +my @lt_gt = FS::UI::Web::parse_lt_gt($cgi, 'amount' ); +s/amount/cust_credit.amount/g foreach (@lt_gt); +push @where, @lt_gt; + if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { push @where, "cust_main.agentnum = $1"; } +if ( $cgi->param('billpkgnum') =~ /^(\d+)$/ ) { + push @where, "billpkgnum = $1"; +} + #classnum # not specified: all classes # 0: empty class @@ -346,12 +379,12 @@ if ( $cgi->param('cust_tax') ) { push @where, $cust_exempt; } -my $count_query = "SELECT COUNT(DISTINCT billpkgnum), +my $count_query = "SELECT COUNT(DISTINCT creditbillpkgnum), SUM(cust_credit_bill_pkg.amount)"; my $join_cust = - ' JOIN cust_bill ON ( cust_bill_pkg.invnum = cust_bill.invnum ) - LEFT JOIN cust_main ON ( cust_bill.custnum = cust_main.custnum ) '; + ' JOIN cust_bill ON ( cust_bill_pkg.invnum = cust_bill.invnum )'. + FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg'); my $join_pkg; @@ -396,10 +429,9 @@ if ( $cgi->param('nottax') ) { s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g for @where; } -} else { +} else { - #die? - warn "neiether nottax nor istax parameters specified"; + #warn "neither nottax nor istax parameters specified"; #same as before? $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum ) LEFT JOIN part_pkg USING ( pkgpart ) '; @@ -412,8 +444,8 @@ my $join_credit = ' LEFT JOIN cust_credit_bill USING ( creditbillnum ) LEFT JOIN cust_credit USING ( crednum ) '; $count_query .= " FROM cust_credit_bill_pkg - $join_pkg $join_cust_bill_pkg + $join_pkg $join_credit $join_cust $where"; @@ -428,10 +460,22 @@ push @select, 'part_pkg.pkg' unless $cgi->param('istax'); push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields(); +my @post_desc_header = (); +my @post_desc = (); +my @post_desc_null = (); +my $post_desc_align = ''; +if ( $conf->exists('enable_taxclasses') && ! $cgi->param('istax') ) { + push @post_desc_header, 'Tax class'; + push @post_desc, 'taxclass'; + push @post_desc_null, ''; + $post_desc_align .= 'l'; + push @select, 'part_pkg.taxclass'; # or should this use override? +} + my $query = { 'table' => 'cust_credit_bill_pkg', - 'addl_from' => "$join_pkg - $join_cust_bill_pkg + 'addl_from' => "$join_cust_bill_pkg + $join_pkg $join_credit $join_cust", 'hashref' => {}, @@ -446,4 +490,57 @@ my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; my $conf = new FS::Conf; my $money_char = $conf->config('money_char') || '$'; +my $tax_pkg_address = $conf->exists('tax-pkg_address'); +my $tax_ship_address = $conf->exists('tax-ship_address'); + +my $location_sub = sub { + #my $cust_credit_bill_pkg = shift; + my $self = shift; + my $tax_Xlocation = $self->cust_bill_pkg_tax_Xlocation; + if ( defined($tax_Xlocation) && $tax_Xlocation ) { + + if ( ref($tax_Xlocation) eq 'FS::cust_bill_pkg_tax_location' ) { + + if ( $tax_Xlocation->taxtype eq 'FS::cust_main_county' ) { + my $cust_main_county = $tax_Xlocation->cust_main_county; + if ( $cust_main_county ) { + $cust_main_county->label; + } else { + ''; #cust_main_county record is gone... history? yuck. + } + } else { + '(CCH tax_rate)'; #XXX FS::tax_rate.. vendor taxes not yet handled here + } + + } elsif ( ref($tax_Xlocation) eq 'FS::cust_bill_pkg_tax_rate_location' ) { + '(CCH)'; #XXX vendor taxes not yet handled here + } else { + 'unknown tax_Xlocation '. ref($tax_Xlocation); + } + + } else { + + my $cust_bill_pkg = $self->cust_bill_pkg; + if ( $cust_bill_pkg->pkgnum > 0 ) { + my $cust_pkg = $cust_bill_pkg->cust_pkg; + if ( $tax_pkg_address && (my $cust_location = $cust_pkg->cust_location) ){ + $cust_location->county_state_country; + } else { + my $cust_main = $cust_pkg->cust_main; + if ( $tax_ship_address && $cust_main->has_ship_address ) { + $cust_main->county_state_country('ship_'); + } else { + $cust_main->county_state_country; + } + } + + } else { + #tax? we shouldn't have wound up here then... + ''; #return customer ship or bill address? (depending on tax-ship_address) + } + + } + +}; + </%init> diff --git a/httemplate/search/cust_credit_refund.html b/httemplate/search/cust_credit_refund.html index 361c8ad2f..817420054 100644 --- a/httemplate/search/cust_credit_refund.html +++ b/httemplate/search/cust_credit_refund.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => $title, 'name' => 'net refunds', 'query' => $sql_query, @@ -57,8 +57,8 @@ '', FS::UI::Web::cust_styles(), ], - ) -%> + +&> <%init> die "access denied" @@ -85,6 +85,16 @@ if ( $cgi->param('refnum') && $cgi->param('refnum') =~ /^(\d+)$/ ) { $title = $part_referral->referral. " $title"; } +# cust_classnum (false laziness w/ elements/cust_main_dayranges.html, elements/cust_pay_or_refund.html, prepaid_income.html, cust_bill_pay.html, cust_bill_pkg.html, cust_bill_pkg_referral.html, unearned_detail.html, cust_credit.html, cust_main::Search::search_sql) +if ( grep { $_ eq 'cust_classnum' } $cgi->param ) { + my @classnums = grep /^\d*$/, $cgi->param('cust_classnum'); + push @search, 'COALESCE( cust_main.classnum, 0) IN ( '. + join(',', map { $_ || '0' } @classnums ). + ' )' + if @classnums; +} + + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); push @search, "cust_credit._date >= $beginning ", "cust_credit._date <= $ending"; @@ -96,8 +106,8 @@ my $where = 'WHERE '. join(' AND ', @search); # my $count_query = 'SELECT COUNT(*), SUM(cust_credit_refund.amount) FROM cust_credit_refund - LEFT JOIN cust_credit USING ( crednum ) - LEFT JOIN cust_main USING ( custnum ) '. + LEFT JOIN cust_credit USING ( crednum ) '. + FS::UI::Web::join_cust_main('cust_credit') . $where; my $sql_query = { @@ -114,8 +124,8 @@ my $sql_query = { 'hashref' => {}, 'extra_sql' => $where, 'addl_from' => 'LEFT JOIN cust_credit USING ( crednum ) - LEFT JOIN cust_refund USING ( refundnum ) - LEFT JOIN cust_main ON ( cust_credit.custnum = cust_main.custnum )', + LEFT JOIN cust_refund USING ( refundnum )'. + FS::UI::Web::join_cust_main('cust_credit') }; #my $cust_credit_link = sub { diff --git a/httemplate/search/cust_event.html b/httemplate/search/cust_event.html index deb34b9e5..bfc5f43e8 100644 --- a/httemplate/search/cust_event.html +++ b/httemplate/search/cust_event.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => $title, 'html_init' => $html_init, 'menubar' => $menubar, @@ -62,8 +62,7 @@ #'', FS::UI::Web::cust_styles(), ], - ) -%> +&> <%once> my $status_sub = sub { @@ -175,7 +174,13 @@ $search{'ending'} = $ending; my $where = ' WHERE '. FS::cust_event->search_sql_where( \%search ); -my $join = FS::cust_event->join_sql(); +my $join = FS::cust_event->join_sql() . + 'LEFT JOIN cust_location bill_location '. + 'ON (cust_main.bill_locationnum = bill_location.locationnum) '. + 'LEFT JOIN cust_location ship_location '. + 'ON (cust_main.ship_locationnum = ship_location.locationnum)'; + # warning: does not show the true service address for package events. + # the query to do that would be painfully slow. my $sql_query = { 'table' => 'cust_event', diff --git a/httemplate/search/cust_main-zip.html b/httemplate/search/cust_main-zip.html index 08800d431..e22d3ea2e 100644 --- a/httemplate/search/cust_main-zip.html +++ b/httemplate/search/cust_main-zip.html @@ -1,13 +1,11 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => 'Zip code Search Results', 'name' => 'zip codes', 'query' => $sql_query, 'count_query' => $count_sql, 'header' => [ 'Zip code', 'Customers', ], - 'fields' => [ 0, 1 ], 'links' => [ '', $link ], - ) -%> +&> <%init> die "access denied" diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi index 7c3ad3384..2c09c692c 100755 --- a/httemplate/search/cust_main.cgi +++ b/httemplate/search/cust_main.cgi @@ -54,7 +54,7 @@ % my $refcustlabel = "$referral_custnum: " . % ( $cust_main->company || $cust_main->last. ', '. $cust_main->first ); referrals of - <A HREF="<% popurl(2)."view/cust_main.cgi?$referral_custnum" %>"><% $refcustlabel %></A> + <A HREF="<% popurl(2)."view/cust_main.cgi?$referral_custnum" %>"><% $refcustlabel |h %></A> <SELECT NAME="referral_depth" SIZE="1" onChange="changed(this)">'; % my $max = 8; @@ -147,7 +147,7 @@ % $view = $p. 'view/cust_main.cgi?'. $custnum; % } % my $pcompany = $company -% ? qq!<A HREF="$view"><FONT SIZE=-1>$company</FONT></A>! +% ? qq!<A HREF="$view"><FONT SIZE=-1>!. encode_entities($company). '</FONT></A>' % : '<FONT SIZE=-1> </FONT>'; % % my $status = $cust_main->status; @@ -161,7 +161,7 @@ <FONT SIZE="-1" COLOR="#<% $statuscol %>"><B><% ucfirst($status) %></B></FONT> </TD> <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ROWSPAN=<% $rowspan %>> - <A HREF="<% $view %>"><FONT SIZE=-1><% "$last, $first" %></FONT></A> + <A HREF="<% $view %>"><FONT SIZE=-1><% "$last, $first" |h %></FONT></A> </TD> <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ROWSPAN=<% $rowspan %>> <% $pcompany %> @@ -244,7 +244,7 @@ % my $pkg_rowspan = shift @pkg_rowspans; <% $n1 %><TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ROWSPAN="<% $pkg_rowspan%>"> - <A HREF="<% $pkgview %>"><FONT SIZE=-1><% $pkg_comment %></FONT></A> + <A HREF="<% $pkgview %>"><FONT SIZE=-1><% $pkg_comment |h %></FONT></A> </TD> % my $n2 = ''; diff --git a/httemplate/search/cust_main.html b/httemplate/search/cust_main.html index fa79b4dfb..24348ff8a 100755 --- a/httemplate/search/cust_main.html +++ b/httemplate/search/cust_main.html @@ -42,9 +42,11 @@ my %search_hash = (); #scalars my @scalars = qw ( agentnum status address zip paydate_year paydate_month invoice_terms - no_censustract with_geocode custbatch usernum + no_censustract with_geocode with_email POST no_POST + custbatch usernum cancelled_pkgs cust_fields flattened_pkgs + all_tags ); for my $param ( @scalars ) { diff --git a/httemplate/search/cust_pay_batch.cgi b/httemplate/search/cust_pay_batch.cgi index 800df8702..9f9eb30ce 100755 --- a/httemplate/search/cust_pay_batch.cgi +++ b/httemplate/search/cust_pay_batch.cgi @@ -1,4 +1,4 @@ -<% include('elements/search.html', +<& elements/search.html, 'title' => 'Batch payment details', 'name' => 'batch details', 'query' => $sql_query, @@ -7,55 +7,41 @@ 'disable_download' => 1, 'header' => [ '#', 'Inv #', - 'Customer', + 'Cust #', 'Customer', 'Card Name', 'Card', 'Exp', 'Amount', 'Status', + '', # error_message ], - 'fields' => [ sub { - shift->[0]; - }, - sub { - shift->[1]; - }, - sub { - shift->[2]; - }, - sub { - my $cpb = shift; - $cpb->[3] . ', ' . $cpb->[4]; - }, - sub { - shift->[5]; - }, - sub { - my $cardnum = shift->[6]; - 'x'x(length($cardnum)-4). substr($cardnum,(length($cardnum)-4)); - }, - sub { - shift->[7] =~ /^\d{2}(\d{2})[\/\-](\d+)[\/\-]\d+$/; - my( $mon, $year ) = ( $2, $1 ); - $mon = "0$mon" if length($mon) == 1; - "$mon/$year"; - }, - sub { - shift->[8]; - }, - sub { - shift->[9]; - }, - ], - 'align' => 'lllllllrl', - 'links' => [ ['', sub{'#';}], - ["${p}view/cust_bill.cgi?", sub{shift->[1];},], - ["${p}view/cust_main.cgi?", sub{shift->[2];},], - ["${p}view/cust_main.cgi?", sub{shift->[2];},], + 'fields' => [ 'paybatchnum', + 'invnum', + 'custnum', + sub { $_[0]->cust_main->name_short }, + 'payname', + 'mask_payinfo', + sub { + return('') if $_[0]->payby ne 'CARD'; + $_[0]->get('exp') =~ /^\d\d(\d\d)-(\d\d)/; + sprintf('%02d/%02d',$1,$2); + }, + sub { + sprintf('%.02f', $_[0]->amount) + }, + 'status', + 'error_message', + ], + 'align' => 'rrrlllcrll', + 'links' => [ '', + ["${p}view/cust_bill.cgi?", 'invnum'], + (["${p}view/cust_main.cgi?", 'custnum']) x 2, ], - ) -%> + 'link_onclicks' => [ ('') x 8, + $sub_receipt + ], +&> <%init> my $conf = new FS::Conf; @@ -101,7 +87,7 @@ if ( $cgi->param('payby') ) { } if ( not $cgi->param('dcln') ) { - push @search, "cpb.status IS DISTINCT FROM 'Approved'"; + push @search, "cust_pay_batch.status IS DISTINCT FROM 'Approved'"; } my ($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); @@ -119,18 +105,30 @@ push @search, $curuser->agentnums_sql({ table => 'pay_batch', my $search = ' WHERE ' . join(' AND ', @search); -$count_query = 'SELECT COUNT(*) FROM cust_pay_batch AS cpb ' . +$count_query = 'SELECT COUNT(*) FROM cust_pay_batch ' . 'LEFT JOIN cust_main USING ( custnum ) ' . 'LEFT JOIN pay_batch USING ( batchnum )' . $search; -#grr -$sql_query = "SELECT paybatchnum,invnum,custnum,cpb.last,cpb.first," . - "cpb.payname,cpb.payinfo,cpb.exp,amount,cpb.status " . - "FROM cust_pay_batch AS cpb " . - 'LEFT JOIN cust_main USING ( custnum ) ' . - 'LEFT JOIN pay_batch USING ( batchnum ) ' . - "$search ORDER BY $orderby"; +$sql_query = { + 'table' => 'cust_pay_batch', + 'select' => 'cust_pay_batch.*, cust_main.*, cust_pay.paynum', + 'hashref' => {}, + 'addl_from' => 'LEFT JOIN pay_batch USING ( batchnum ) '. + 'LEFT JOIN cust_main USING ( custnum ) '. + + 'LEFT JOIN cust_pay USING ( batchnum, custnum ) ', + 'extra_sql' => $search, + 'order_by' => "ORDER BY $orderby", +}; + +my $sub_receipt = sub { + my $paynum = shift->paynum or return ''; + include('/elements/popup_link_onclick.html', + 'action' => $p.'view/cust_pay.html?link=popup;paynum='.$paynum, + 'actionlabel' => emt('Payment Receipt'), + ); +}; my $html_init = ''; if ( $pay_batch ) { diff --git a/httemplate/search/cust_pay_pending.html b/httemplate/search/cust_pay_pending.html index 2afce0ce9..54c9935ef 100755 --- a/httemplate/search/cust_pay_pending.html +++ b/httemplate/search/cust_pay_pending.html @@ -1,4 +1,4 @@ -<% include( 'elements/cust_pay_or_refund.html', +<& elements/cust_pay_or_refund.html, 'thing' => 'pay_pending', 'amount_field' => 'paid', 'name_singular' => 'pending payment', @@ -10,8 +10,7 @@ $status_sub, ], 'redirect_empty' => $redirect_empty, - ) -%> +&> <%init> my %statusaction = ( diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 887ec6039..110da91ae 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -9,6 +9,7 @@ emt('Package'), emt('Class'), emt('Status'), + emt('Ordered by'), emt('Setup'), emt('Base Recur'), emt('Freq.'), @@ -34,6 +35,7 @@ sub { $_[0]->pkg; }, 'classname', sub { ucfirst(shift->status); }, + 'otaker', sub { sprintf( $money_char.'%.2f', shift->part_pkg->option('setup_fee'), ); @@ -96,13 +98,14 @@ '', '', '', + '', FS::UI::Web::cust_colors(), '', ], - 'style' => [ '', '', '', '', 'b', '', '', '', '', '', '', '', '', '', '', '', '', '', '', + 'style' => [ '', '', '', '', 'b', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', FS::UI::Web::cust_styles() ], 'size' => [ '', '', '', '', '-1' ], - 'align' => 'rrlccrrlrrrrrrrrrrl'. FS::UI::Web::cust_aligns(). 'r', + 'align' => 'rrlcccrrlrrrrrrrrrrl'. FS::UI::Web::cust_aligns(). 'r', 'links' => [ $link, $link, @@ -119,6 +122,7 @@ '', '', '', + '', '', # link to changed-from package? '', '', diff --git a/httemplate/search/cust_pkg_discount.html b/httemplate/search/cust_pkg_discount.html index d70c3116f..23af1802e 100644 --- a/httemplate/search/cust_pkg_discount.html +++ b/httemplate/search/cust_pkg_discount.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => 'Package discounts', 'name' => 'discounts', 'query' => $query, @@ -50,8 +50,8 @@ '', FS::UI::Web::cust_styles(), ], - ) -%> + +&> <%init> die "access denied" @@ -92,8 +92,8 @@ my $count_query = "SELECT COUNT(*), SUM(amount)"; my $join = ' LEFT JOIN discount USING ( discountnum ) LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) - LEFT JOIN cust_main USING ( custnum ) '; + LEFT JOIN part_pkg USING ( pkgpart ) '. + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); my $where = ' WHERE '. join(' AND ', @where); diff --git a/httemplate/search/cust_pkg_summary.cgi b/httemplate/search/cust_pkg_summary.cgi index fbeeb92ce..c0eb69920 100644 --- a/httemplate/search/cust_pkg_summary.cgi +++ b/httemplate/search/cust_pkg_summary.cgi @@ -1,25 +1,14 @@ -<% include('/elements/header.html', $title) %> -<% include('/elements/table-grid.html') %> - <TR> -% foreach (@head) { - <TH CLASS="grid" BGCOLOR="#cccccc"><% $_ %></TH> -% } - </TR> -% my $r=0; -% foreach my $row (@rows) { - <TR> -% foreach (@$row) { - <TD CLASS="grid" ALIGN="right" BGCOLOR="<% $r % 2 ? '#ffffff' : '#eeeeee' %>"><% $_ %></TD> -% } - </TR> -% $r++; -% } - <TR> -% foreach (@totals) { - <TD CLASS="grid" ALIGN="right" BGCOLOR="<% $r % 2 ? '#ffffff' : '#eeeeee' %>"><B><% $_ %></B></TD> -% } - </TR> -</TABLE> +<& elements/search.html, + 'title' => $title, + 'name' => 'package types', + 'query' => $query, + 'count_query' => $count_query, + 'header' => \@head, + 'fields' => \@fields, + 'links' => \@links, + 'align' => 'clrrrrr', + 'footer_data' => $totals, +&> <%init> my $curuser = $FS::CurrentUser::CurrentUser; @@ -34,74 +23,92 @@ if($begin > 0) { $cgi->param('beginning').' - '.$cgi->param('ending').')'; } -my @h_sql = FS::h_cust_pkg->sql_h_search($end); +my $agentnums_sql = $curuser->agentnums_sql( + 'null' => 1, + 'table' => 'main', + ); -my ($end_sql, $addl_from) = @h_sql[1,3]; -$end_sql =~ s/ORDER BY.*//; # breaks aggregate queries +my $extra_sql = " freq != '0' AND $agentnums_sql"; -my $begin_sql = $end_sql; -$begin_sql =~ s/$end/$begin/g; +#tiny bit of false laziness w/cust_pkg.pm::search +if ( grep { $_ eq 'classnum' } $cgi->param ) { + if ( $cgi->param('classnum') eq '' ) { + $extra_sql .= ' AND main.classnum IS NULL'; + } elsif ( $cgi->param('classnum') =~ /^(\d+)$/ && $1 ne '0' ) { + $extra_sql .= " AND main.classnum = $1 "; + } +} -my $active_sql = FS::cust_pkg->active_sql; -my $suspended_sql = FS::cust_pkg->suspended_sql; -my @conds = ( - # SQL WHERE clauses for each column of the table. - " $begin_sql AND ($active_sql OR $suspended_sql)", - '', - " $end_sql AND ($active_sql OR $suspended_sql)", - " $end_sql AND $active_sql", - " $end_sql AND $suspended_sql", +my $active_sql = 'setup IS NOT NULL AND susp IS NULL AND cancel IS NULL'; +my $suspended_sql = 'setup IS NOT NULL AND susp IS NOT NULL AND cancel IS NULL'; +my $active_or_suspended_sql = 'setup IS NOT NULL AND cancel IS NULL'; +my %conds; + +$conds{'before'} = { 'date' => $begin, 'status' => 'active,suspended' }; +$conds{'after'} = { 'date' => $end, 'status' => 'active,suspended' }; +$conds{'active'} = { 'date' => $end, 'status' => 'active' }; +$conds{'suspended'} = { 'date' => $end, 'status' => 'suspended' }; + +my @select; +my $totals = FS::part_pkg->new({pkg => 'Total'}); +foreach my $column (keys %conds) { + my $h_search = FS::h_cust_pkg->search($conds{$column}); + my $count_query = $h_search->{count_query}; + + # push a select expression for the total packages with pkgpart=main.pkgpart + push @select, "($count_query AND h_cust_pkg.pkgpart = main.pkgpart) AS $column"; + + # and query the total packages with pkgpart=any of the main.pkgparts + my $total = FS::Record->scalar_sql($count_query . + " AND h_cust_pkg.pkgpart IN(SELECT pkgpart FROM part_pkg AS main WHERE $extra_sql)" ); + $totals->set($column => $total); +} -$_ =~ s/\bcust_pkg/maintable/g foreach @conds; +my $query = { + 'table' => 'part_pkg', + 'addl_from' => 'AS main', + 'select' => join(', ', 'main.*', @select), + 'extra_sql' => "WHERE $extra_sql", +}; -my @head = ('Package', 'Before Period', 'Sales', 'Total', 'Active', 'Suspended'); -my @rows = (); -my @totals = ('Total', 0, 0, 0, 0, 0); +my $count_query = "SELECT COUNT(*) FROM part_pkg AS main WHERE $extra_sql"; -if( !$begin ) { - splice @conds, 1, 1; - splice @head, 1, 1; +my $baselink = "h_cust_pkg.html?"; +if ( $cgi->param('classnum') =~ /^\d*$/ ) { + $baselink .= "classnum=".$cgi->param('classnum').';'; } +my @links = ( #arguments to h_cust_pkg.html, except for pkgpart + '', + '', + [ $baselink . "status=active,suspended;date=$begin;pkgpart=", 'pkgpart' ], + '', + [ $baselink . "status=active,suspended;date=$end;pkgpart=", 'pkgpart' ], + [ $baselink . "status=active;date=$end;pkgpart=", 'pkgpart' ], + [ $baselink . "status=suspended;date=$end;pkgpart=", 'pkgpart' ], +); -my $agentnums_sql = $curuser->agentnums_sql( - 'null' => 1, - 'table' => 'part_pkg', - ); +my @head = ('#', + 'Package', + 'Before Period', + 'Sales', + 'Total', + 'Active', + 'Suspended'); -my $extra_sql = " WHERE $agentnums_sql"; - -foreach my $part_pkg (qsearch({ 'table' => 'part_pkg', - 'hashref' => {}, - 'extra_sql' => $extra_sql, - }) - ) -{ - my @row = (); - next if !$part_pkg->freq; # exclude one-time packages - push @row, $part_pkg->pkg; - my $i=1; - foreach my $cond (@conds) { - if($cond) { - my $result = qsearchs({ - 'table' => 'h_cust_pkg', - 'addl_from' => $addl_from. - ' LEFT JOIN cust_main USING ( custnum )', - - 'hashref' => {}, - 'select' => 'count(*)', - 'extra_sql' => 'WHERE pkgpart = '.$part_pkg->pkgpart.$cond. - ' AND '. $curuser->agentnums_sql( - 'table' => 'cust_main', - ), - }); - $row[$i] = $result->getfield('count'); - $totals[$i] += $row[$i]; - } - $i++; - } - $row[2] = $row[3]-$row[1]; - $totals[2] += $row[2]; - push @rows, \@row; +my @fields = ( + 'pkgpart', + 'pkg', + 'before', + sub { $_[0]->after - $_[0]->before }, + 'after', + 'active', + 'suspended', + ); + +if ( !$begin ) { + # remove the irrelevant 'before' column + splice(@$_,2,1) foreach \@head, \@fields, \@links; } + </%init> diff --git a/httemplate/search/cust_pkg_summary.html b/httemplate/search/cust_pkg_summary.html index 8c05f7382..18a5690d0 100644 --- a/httemplate/search/cust_pkg_summary.html +++ b/httemplate/search/cust_pkg_summary.html @@ -1,4 +1,4 @@ -<% include( '/elements/header.html', 'Package Summary Report' ) %> +<& /elements/header.html, 'Package Summary Report' &> <FORM ACTION="cust_pkg_summary.cgi" METHOD="GET"> @@ -10,7 +10,12 @@ </TH> </TR> - <% include ('/elements/tr-input-beginning_ending.html') %> + <& /elements/tr-input-beginning_ending.html &> + + <& /elements/tr-select-pkg_class.html, + 'pre_options' => [ '0' => 'all' ], + 'empty_label' => '(empty class)', + &> </TABLE> @@ -19,7 +24,7 @@ </FORM> -<% include('/elements/footer.html') %> +<& /elements/footer.html &> <%init> die "access denied" diff --git a/httemplate/search/cust_pkg_susp.html b/httemplate/search/cust_pkg_susp.html index 2ac643260..bdc3c5402 100644 --- a/httemplate/search/cust_pkg_susp.html +++ b/httemplate/search/cust_pkg_susp.html @@ -23,6 +23,6 @@ <%init> die "access denied" - unless $curuser->access_right('Summarize packages'); + unless $FS::CurrentUser::CurrentUser->access_right('Summarize packages'); </%init> diff --git a/httemplate/search/cust_pkg_svc.html b/httemplate/search/cust_pkg_svc.html index 9c5b32fc7..cdc70351a 100644 --- a/httemplate/search/cust_pkg_svc.html +++ b/httemplate/search/cust_pkg_svc.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => $part_svc->svc.' services in package #'.$pkgnum, 'name' => 'services', 'html_form' => $html_form, @@ -30,8 +30,8 @@ ('')x4, ], 'html_foot' => sub { $areboxes ? $html_foot : '' } - ) -%> + +&> <%init> die "access denied" diff --git a/httemplate/search/cust_svc.html b/httemplate/search/cust_svc.html index 2adcbd76f..3b770432e 100644 --- a/httemplate/search/cust_svc.html +++ b/httemplate/search/cust_svc.html @@ -47,8 +47,8 @@ die "access denied" my $sql_query; -my $orderby = 'ORDER BY svcnum'; #has to be ordered by something - #for pagination to work +my $orderby = 'ORDER BY cust_svc.svcnum'; #has to be ordered by something + #for pagination to work if ( length( $cgi->param('search_svc') ) ) { @@ -62,7 +62,7 @@ if ( length( $cgi->param('search_svc') ) ) { my $addl_from = ' LEFT JOIN part_svc USING ( svcpart ) '. ' LEFT JOIN cust_pkg USING ( pkgnum ) '. - ' LEFT JOIN cust_main USING ( custnum ) '; + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); my @extra_sql = (); @@ -96,6 +96,7 @@ if ( length( $cgi->param('search_svc') ) ) { my $extra_sql = ' WHERE '. join(' AND ', @extra_sql ); $sql_query = { + 'select' => 'cust_svc.*, part_svc.*', 'table' => 'cust_svc', 'addl_from' => $addl_from, 'hashref' => {}, @@ -104,9 +105,10 @@ if ( length( $cgi->param('search_svc') ) ) { } +# at this point the query must provide all fields from +# cust_svc and part_svc, and must include join_cust_main. $sql_query->{'select'} = join(', ', - 'cust_svc.*', - 'part_svc.*', + $sql_query->{'select'}, 'cust_main.custnum', FS::UI::Web::cust_sql_fields(), ); @@ -117,14 +119,17 @@ my $count_query = "SELECT COUNT(*) FROM cust_svc ". $sql_query->{addl_from}. my $link = sub { my $cust_svc = shift; - my $url = svc_url( - 'm' => $m, - 'action' => 'view', - #'part_svc' => $cust_svc->part_svc, - 'svcdb' => $cust_svc->svcdb, #we have it from the joined search - #'svc' => $cust_svc, #redundant - 'query' => '', - ); + my $url; + if ( $cust_svc->svcpart ) { + $url = svc_url( + 'm' => $m, + 'action' => 'view', + 'svcdb' => $cust_svc->svcdb, #we have it from the joined search + 'query' => '', + ); + } else { # bizarre unlinked service case + $url = $p.'view/svc_Common.html?svcdb='.$cust_svc->svcdb.';svcnum='; + } [ $url, 'svcnum' ]; }; diff --git a/httemplate/search/cust_tax_adjustment.html b/httemplate/search/cust_tax_adjustment.html index 925476516..6125a1c04 100644 --- a/httemplate/search/cust_tax_adjustment.html +++ b/httemplate/search/cust_tax_adjustment.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => $title, 'name_singular' => 'tax adjustment', 'query' => $query, @@ -12,9 +12,8 @@ }, ], 'links' => [ '', '', '', $ilink ], - ) -%> - + +&> <%init> die "access denied" diff --git a/httemplate/search/cust_tax_exempt.cgi b/httemplate/search/cust_tax_exempt.cgi index 3704b208a..005d77c33 100644 --- a/httemplate/search/cust_tax_exempt.cgi +++ b/httemplate/search/cust_tax_exempt.cgi @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => 'Legacy tax exemptions', 'name' => 'legacy tax exemptions', 'query' => $query, @@ -46,13 +46,11 @@ '', FS::UI::Web::cust_styles(), ], - ) -%> + +&> <%init> -my $join_cust = " - LEFT JOIN cust_main USING ( custnum ) -"; +my $join_cust = FS::UI::Web::join_cust_main('cust_tax_exempt'); die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('View customer tax exemptions'); diff --git a/httemplate/search/cust_tax_exempt_pkg.cgi b/httemplate/search/cust_tax_exempt_pkg.cgi index 1b767f846..40b9ed78f 100644 --- a/httemplate/search/cust_tax_exempt_pkg.cgi +++ b/httemplate/search/cust_tax_exempt_pkg.cgi @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => 'Tax exemptions', 'name' => 'tax exemptions', 'query' => $query, @@ -77,14 +77,12 @@ '', FS::UI::Web::cust_styles(), ], - ) -%> +&> <%once> my $join_cust = " - JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) -"; + JOIN cust_bill USING ( invnum )" . + FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg'); my $join_pkg = " LEFT JOIN cust_pkg USING ( pkgnum ) @@ -93,8 +91,8 @@ my $join_pkg = " my $join = " JOIN cust_bill_pkg USING ( billpkgnum ) - $join_cust $join_pkg + $join_cust "; </%once> diff --git a/httemplate/search/customer_accounting_summary.html b/httemplate/search/customer_accounting_summary.html index 72a00ed95..b48ff21e3 100644 --- a/httemplate/search/customer_accounting_summary.html +++ b/httemplate/search/customer_accounting_summary.html @@ -1,25 +1,125 @@ -<% include('/graph/elements/monthly.html', - #Dumper( - 'title' => $title, - 'graph_type' => 'none', - 'items' => \@items, - 'params' => \@params, - 'labels' => \@labels, - 'graph_labels' => \@labels, - 'remove_empty' => 1, - 'bottom_total' => 1, - 'agentnum' => $agentnum, - 'doublemonths' => \@doublemonths, - 'nototal' => 1, - ) -%> +% if ( $cgi->param('_type') =~ /(xls)$/ ) { +<%perl> + # egregious false laziness w/ search/report_tax-xls.cgi + my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format; + my $filename = $cgi->url(-relative => 1); + $filename =~ s/\.html$//; + $filename .= $format->{extension}; + http_header('Content-Type' => $format->{mime_type}); + http_header('Content-Disposition' => qq!attachment;filename="$filename"!); + + my $output = ''; + my $XLS = IO::String->new($output); + my $workbook = $format->{class}->new($XLS) + or die "Error opening .xls file: $!"; + + my $worksheet = $workbook->add_worksheet('Summary'); + + my %format = ( + header => { + size => 11, + bold => 1, + align => 'center', + valign => 'vcenter', + text_wrap => 1, + }, + money => { + size => 11, + align => 'right', + valign => 'bottom', + num_format=> 8, + }, + '' => {}, + ); + my %default = ( + font => 'Calibri', + border => 1, + ); + foreach (keys %format) { + my %f = (%default, %{$format{$_}}); + $format{$_} = $workbook->add_format(%f); + $format{"m_$_"} = $workbook->add_format(%f); + } + + my ($r, $c) = (0, 0); + for my $row (@rows) { + $c = 0; + my $thisrow = shift @cells; + for my $cell (@$thisrow) { + if (!ref($cell)) { + # placeholder, so increment $c so that we write to the correct place + $c++; + next; + } + # format name + my $f = ''; + $f = 'header' if $row->{header} or $cell->{header}; + $f = 'money' if $cell->{format} eq 'money'; + if ( $cell->{rowspan} > 1 or $cell->{colspan} > 1 ) { + my $range = xl_range_formula( + 'Summary', + $r, $r - 1 + ($cell->{rowspan} || 1), + $c, $c - 1 + ($cell->{colspan} || 1) + ); + #warn "merging $range\n"; + $worksheet->merge_range($range, $cell->{value}, $format{"m_$f"}); + } else { + #warn "writing ".xl_rowcol_to_cell($r, $c)."\n"; + $worksheet->write( $r, $c, $cell->{value}, $format{$f} ); + } + $c++; + } #$cell + $r++; + } #$row + $workbook->close; + + http_header('Content-Length' => length($output)); + $m->print($output); +</%perl> +% } else { +<& /elements/header.html, $title &> +% my $myself = $cgi->self_url; +<P ALIGN="right" CLASS="noprint"> +Download full reports<BR> +as <A HREF="<% "$myself;_type=xls" %>">Excel spreadsheet</A><BR> +% # as <A HREF="<% "$myself;_type=csv" %>">CSV file</A> # is this still needed? +</P> +<style type="text/css"> +.report * { + background-color: #f8f8f8; + border: 1px solid black; + padding: 2px; +} +.report td { + text-align: right; +} +.total * { background-color: #f5f6be; } +.shaded * { background-color: #c8c8c8; } +.totalshaded * { background-color: #bfc094; } +</style> +<table class="report" width="100%" cellspacing=0> +% foreach my $rowinfo (@rows) { + <tr<% $rowinfo->{class} ? ' class="'.$rowinfo->{class}.'"' : ''%>> +% my $thisrow = shift @cells; +% foreach my $cell (@$thisrow) { +% next if !ref($cell); # placeholders +% my $td = $cell->{header} ? 'th' : 'td'; +% my $style = ''; +% $style .= " rowspan=".$cell->{rowspan} if $cell->{rowspan} > 1; +% $style .= " colspan=".$cell->{colspan} if $cell->{colspan} > 1; + <<%$td%><%$style%>><% $cell->{value} |h %></<%$td%>> +% } + </tr> +% } +</table> + +<& /elements/footer.html &> +% } <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); -my @doublemonths = ( 'Billed', 'Paid' ); - my ($agentnum,$sel_agent); if ( $cgi->param('agentnum') eq 'all' ) { $agentnum = 0; @@ -32,9 +132,6 @@ elsif ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { my $title = $sel_agent ? $sel_agent->agent.' ' : ''; my ($refnum,$sel_part_referral); -#if ( $cgi->param('refnum') eq 'all' ) { -# $refnum = 0; -#} els if ( $cgi->param('refnum') =~ /^(\d+)$/ ) { $refnum = $1; $sel_part_referral = qsearchs('part_referral', { 'refnum' => $refnum } ); @@ -45,29 +142,168 @@ $title .= $sel_part_referral->referral.' ' $title .= 'Customer Accounting Summary Report'; -my @custs = (); -@custs = qsearch('cust_main', {} ); - -my @items = (); -my @params = (); +my @items = ('netsales', 'cashflow'); +my @params = ( [], [] ); +my $setuprecur = ''; +if ( $cgi->param('setuprecur') ) { + $setuprecur = 1; + # instead of 'cashflow' (payments - refunds), use 'receipts' + # (applied payments), because it's divisible into setup and recur. + @items = ('netsales', 'receipts', 'netsales', 'receipts'); + @params = ( + [ setuprecur => 'setup' ], + [ setuprecur => 'setup' ], + [ setuprecur => 'recur' ], + [ setuprecur => 'recur' ], + ); +} my @labels = (); +my @cross_params = (); +my @custnames = (); my $status = $cgi->param('status'); die "invalid status" unless $status =~ /^\w+|$/; +my %search_hash; +foreach (qw(agentnum refnum status)) { + if ( defined $cgi->param($_) ) { + $search_hash{$_} = $cgi->param($_); + } +} +$search_hash{'classnum'} = [ $cgi->param('cust_classnum') ] + if grep { $_ eq 'cust_classnum' } $cgi->param; + +my $query = FS::cust_main::Search->search(\%search_hash); +my @custs = qsearch($query); + foreach my $cust_main ( @custs ) { + # XXX should do this in the qsearch next unless ($status eq '' || $status eq $cust_main->status); next unless ($agentnum == 0 || $cust_main->agentnum eq $agentnum); next unless ($refnum == 0 || $cust_main->refnum eq $refnum); - push @items, 'netsales', 'cashflow'; + push @custnames, $cust_main->name; + + push @cross_params, [ ('custnum' => $cust_main->custnum) ]; +} + +my %opt = ( + items => \@items, + params => \@params, + cross_params => \@cross_params, + agentnum => $agentnum, + refnum => $refnum, +); +for ( qw(start_month start_year end_month end_year) ) { + if ( $cgi->param($_) =~ /^(\d+)$/ ) { + $opt{$_} = $1; + } +} + +warn Dumper(OPTIONS => \%opt) if $cgi->param('debug'); +my $report = FS::Report::Table::Monthly->new(%opt); +my $data = $report->data; +warn Dumper(DATA => $data) if $cgi->param('debug') >= 2; + +my @total; + +my @rows; # hashes of row info +my @cells; # arrayrefs of cell info +# We use Excel currency format, but not Excel dates, because +# these are whole months and there's no nice way to express that. +# This is the historical behavior for monthly reports. - push @labels, $cust_main->name; +# header row +$rows[0] = {}; +$cells[0] = [ + { header => 1, rowspan => 2, colspan => ($setuprecur ? 2 : 1) }, + ($setuprecur ? '' : ()), + map { + { header => 1, colspan => 2, value => time2str('%b %Y', $_) }, + '' + } @{ $data->{speriod} } +]; +my $ncols = scalar(@{ $data->{speriod} }); + +$rows[1] = {}; +$cells[1] = [ '', + ($setuprecur ? '' : ()), + map { + ( { header => 1, value => mt('Billed') }, + { header => 1, value => mt('Paid') } + ) } (1..$ncols) +]; + +# use PDL; # ha ha, I just might. +my $row = 0; +foreach my $name (@custnames) { # correspond to cross_params + my $skip = 1; # skip the customer iff ALL of their values are zero + for my $subrow (0..($setuprecur ? 1 : 0)) { # the setup/recur axis + push @rows, { class => $subrow ? 'shaded' : '' }; + my @thisrow; + if ( $subrow == 0 ) { + # customer name + push @thisrow, + { value => $name, + header => 1, + rowspan => ($setuprecur ? 2 : 1) }; + } else { + push @thisrow, ''; + } + if ( $setuprecur ) { + # subheading + push @thisrow, + { value => $subrow ? mt('recurring') : mt('setup'), + header => 1 }; + } + for my $col (0..$ncols-1) { # the month + for my $subcol (0..1) { # the billed/paid axis + my $item = $subrow * 2 + $subcol; + my $value = $data->{data}[$item][$col][$row]; + $skip = 0 if abs($value) > 0.005; + push @thisrow, { value => sprintf('%0.2f', $value), format => 'money' }; + $total[( ($ncols * $subrow) + $col ) * 2 + $subcol] += $value; + } #subcol + } #col + push @cells, \@thisrow; + } #subrow + if ( $skip ) { + # all values are zero--remove the rows we just added + pop @rows; + pop @cells; + if ( $setuprecur ) { + pop @rows; + pop @cells; + } + } + $row++; +} +for my $subrow (0..($setuprecur ? 1 : 0)) { + push @rows, { class => ($subrow ? 'totalshaded' : 'total') }; + my @thisrow; + if ( $subrow == 0 ) { + push @thisrow, + { value => mt('Total'), + header => 1, + rowspan => ($setuprecur ? 2 : 1), }; + } else { + push @thisrow, ''; + } + if ( $setuprecur ) { + push @thisrow, + { value => $subrow ? mt('recurring') : mt('setup'), + header => 1 }; + } + for my $col (0..($ncols * 2)-1) { # month and billed/paid axis + my $value = $total[($subrow * $ncols * 2) + $col]; + push @thisrow, { value => sprintf('%0.2f', $value), format => 'money' }; + } + push @cells, \@thisrow; +} #subrow - push @params, [ ('custnum' => $cust_main->custnum), - ], - [ ('custnum' => $cust_main->custnum), - ]; +if ( $cgi->param('debug') >= 3 ) { + warn Dumper(\@rows, \@cells); } +my $title = 'Customer Accounting Summary'; </%init> diff --git a/httemplate/search/e911.html b/httemplate/search/e911.html new file mode 100644 index 000000000..6a9dd0a16 --- /dev/null +++ b/httemplate/search/e911.html @@ -0,0 +1,106 @@ +% if ( $row ) { +%# pretty minimal report +<& /elements/header.html, 'E911 Fee Report' &> +<& /elements/table-grid.html &> +<STYLE TYPE="text/css"> +table.grid TD:first-child { font-weight: normal } +table.grid TD { font-weight: bold; + text-align: right; + padding: 1px 2px } +</STYLE> + <TR><TH COLSPAN=2><% $legend %></TH></TR> + <TR> + <TD>E911 access lines:</TD> + <TD><% $row->{quantity} || 0 %></TD> + </TR> + <TR> + <TD>Total fees collected: </TD> + <TD><% $money_char.sprintf('%.2f', $row->{paid_amount}) %></TD> + </TR> + <TR> + <TD>Administrative fee (1%): </TD> + <TD><% $money_char.sprintf('%.2f', $row->{paid_amount} * $admin_fee) %></TD> + </TR> + <TR> + <TD>Amount due: </TD> + <TD><% $money_char.sprintf('%.2f', $row->{paid_amount} * (1-$admin_fee) ) %> + </TD> + </TR> +</TABLE> +<& /elements/footer.html &> +% } else { # no data +% $cgi->param('error' => 'No paid E911 fees found.'); +<& /elements/errorpage.html &> +% } +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my $money_char = FS::Conf->new->config('money_char') || '$'; + +my($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi); + +$cgi->param('e911pkgpart') =~ /^(\d+)$/; +my $pkgpart = $1 or die 'bad e911pkgpart'; + +$cgi->param('agentnum') =~ /^(\d*)$/; +my $agentnum = $1; + +# This has the potential to become as nightmarish as the old tax report. +# If we end up doing multiple rows for some reason (date intervals, +# package classes, etc.), do NOT simply loop through this and do a +# bazillion scalar_sql queries. Use a properly grouped aggregate query. + +my $select = 'SELECT cust_bill_pkg.billpkgnum, cust_bill_pkg.quantity, '. +'SUM(cust_bill_pay_pkg.amount) AS paid_amount'; + +my $from = 'FROM cust_pkg + JOIN cust_bill_pkg USING (pkgnum) + JOIN cust_bill USING (invnum) + JOIN cust_bill_pay_pkg USING (billpkgnum) + JOIN cust_bill_pay USING (billpaynum) +'; +# going by payment application date here, which should be +# max(invoice date, payment date) +my $where = "WHERE cust_pkg.pkgpart = $pkgpart +AND cust_bill_pay._date >= $begin AND cust_bill_pay._date < $end"; + +if ( $agentnum ) { + $from .= ' JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)'; + $where .= "\n AND cust_main.agentnum = $agentnum"; +} + +my $subquery = "$select $from $where +GROUP BY cust_bill_pkg.billpkgnum, cust_bill_pkg.quantity"; +# This has one row for each E911 line item that has any payments applied. +# Fields are the billpkgnum of the item (currently unused), the number of +# E911 charges, and the total amount paid (always > 0). + +# now sum those rows. +my $sql = "SELECT SUM(quantity) AS quantity, SUM(paid_amount) AS paid_amount +FROM ($subquery) AS paid_fees"; # no grouping + +my $sth = dbh->prepare($sql); +$sth->execute; +my $row = $sth->fetchrow_hashref; + +my $admin_fee = 0.01; # 1% admin fee, allowed in Texas + +$end = '' if $end == 4294967295; +my $legend = ''; +if ( $agentnum ) { + $legend = FS::agent->by_key($agentnum)->agent . ', '; +} +if ( $begin and $end ) { + $legend .= time2str('%h %o %Y', $begin) . '—' . + time2str('%h %o %Y', $end); +} elsif ( $begin ) { + $legend .= time2str('after %h %o %Y', $begin); +} elsif ( $end ) { + $legend .= time2str('before %h %o %Y', $end); +} else { + $legend .= 'any time'; +} +$legend = ucfirst($legend); +</%init> diff --git a/httemplate/search/elements/checkbox-foot.html b/httemplate/search/elements/checkbox-foot.html new file mode 100644 index 000000000..be1caab91 --- /dev/null +++ b/httemplate/search/elements/checkbox-foot.html @@ -0,0 +1,86 @@ +<%doc> +<& /elements/search.html, + # options... + html_foot => include('elements/checkbox-foot.html', + actions => [ + { label => 'Edit selected packages', + action => 'popup_package_edit()', + }, + { submit => 'Delete selected packages', + confirm => 'Really delete these packages?' + }, + ], + filter => '.name = "pkgpart"', # see below + ), +&> + +This creates a footer for a search page containing a column of checkboxes. +Typically this is used to select several items from the search result and +apply some change to all of them at once. The footer always provides +"select all" and "unselect all" buttons. + +"actions" is an arrayref of action buttons to show. Each element of the +array is a hashref of either: + +- "submit" and, optionally, "confirm". Creates a submit button. The value +of "submit" becomes the "value" property of the button (and thus its label). +If "confirm" is specified, the button will have an onclick handler that +displays the value of "confirm" in a popup message box and asks the user to +confirm the choice. + +- "onclick" and "label". Creates a non-submit button that executes the +Javascript code in "onclick". "label" is used as the text of the button. + +If you want only a single action, you can forget the arrayref-of-hashrefs +business and just put "submit" and "confirm" (or "onclick" and "label") +elements in the argument list. + +"filter" is a javascript expression to limit which checkboxes are included in +the "select/unselect all" actions. By default, any input with type="checkbox" +will be included. If this option is given, it will be evaluated with the +HTML node in a variable named "obj". The expression should return true or +false. + +</%doc> +<DIV ID="checkbox_footer" STYLE="display:block"> +<INPUT TYPE="button" VALUE="<% emt('select all') %>" onclick="setAll(true)"> +<INPUT TYPE="button" VALUE="<% emt('unselect all') %>" onclick="setAll(false)"> +<BR> +% foreach my $action (@$actions) { +% if ( $action->{onclick} ) { +<INPUT TYPE="button" <% $action->{name} %> onclick="<% $opt{onclick} %>"\ + VALUE="<% $action->{label} |h%>"> +% } elsif ( $action->{submit} ) { +<INPUT TYPE="submit" <% $action->{name} %> <% $action->{confirm} %>\ + VALUE="<% $action->{submit} |h%>"> +% } # else do nothing +% } #foreach +</DIV> +<SCRIPT> +var checkboxes = []; +var inputs = document.getElementsByTagName('input'); +for (var i = 0; i < inputs.length; i++) { + var obj = inputs[i]; + if ( obj.type == "checkbox" && <% $filter %> ) { + checkboxes.push(obj); + } +} +%# avoid the need for "$areboxes" late-evaluation hackery +if ( checkboxes.length == 0 ) { + document.getElementById('checkbox_footer').style.display = 'none'; +} +function setAll(setTo) { + for (var i = 0; i < checkboxes.length; i++) { + checkboxes[i].checked = setTo; + } +} +</SCRIPT> +<%init> +my %opt = @_; +my $actions = $opt{'actions'} || [ \%opt ]; +foreach (@$actions) { + $_->{confirm} &&= qq!onclick="return confirm('! . $_->{confirm} . qq!')"!; + $_->{name} &&= qq!NAME="! . $_->{name} . qq!"!; +} +my $filter = $opt{filter} || 'true'; +</%init> diff --git a/httemplate/search/elements/cust_main_dayranges.html b/httemplate/search/elements/cust_main_dayranges.html index eb7566494..493365281 100644 --- a/httemplate/search/elements/cust_main_dayranges.html +++ b/httemplate/search/elements/cust_main_dayranges.html @@ -2,10 +2,10 @@ Example: - include( 'elements/cust_main_dayranges.html', + <& elements/cust_main_dayranges.html, 'title' => 'Accounts Receivable Aging Summary', 'range_sub' => $mysub, - ) + &> my $mysub = sub { my( $start, $end ) = @_; @@ -44,7 +44,7 @@ Example: $row->{'rangecol_60_90'} ), sprintf( $money_char.'%.2f', $row->{'rangecol_90_0'} ), - sprintf( '<b>'. $money_char.'%.2f'. '</b>', + sprintf( '<b>'.$money_char.'%.2f</b>', $row->{'rangecol_0_0'} ), ('') x @pay_labels, ], @@ -81,6 +81,9 @@ Example: '', '', '', '', 'b', ( map '', @pay_labels ), ], + 'xls_format' => [ (map '', FS::UI::Web::cust_styles), + '', '', '', '', { bold => 1 }, + ], 'color' => [ FS::UI::Web::cust_colors(), '', @@ -162,6 +165,15 @@ if ( grep { $cgi->param('status') eq $_ } FS::cust_main->statuses() ) { push @where, FS::cust_main->$method(); } +# cust_classnum (false laziness w/prepaid_income.html, elements/cust_pay_or_refund.html, cust_bill_pay.html, cust_bill_pkg.html, cust_bill_pkg_referral.html, unearned_detail.html, cust_credit.html, cust_credit_refund.html, cust_main::Search::search_sql) +if ( grep { $_ eq 'cust_classnum' } $cgi->param ) { + my @classnums = grep /^\d*$/, $cgi->param('cust_classnum'); + push @where, 'COALESCE( cust_main.classnum, 0) IN ( '. + join(',', map { $_ || '0' } @classnums ). + ' )' + if @classnums; +} + #here is the agent virtualization push @where, $FS::CurrentUser::CurrentUser->agentnums_sql; @@ -172,10 +184,11 @@ my $count_sql = "select count(*) from cust_main $where"; my $sql_query = { 'table' => 'cust_main', + 'addl_from' => FS::UI::Web::join_cust_main('cust_main'), 'hashref' => {}, 'select' => join(',', #'cust_main.*', - 'custnum', + 'cust_main.custnum', $range_cols, $packages_cols, FS::UI::Web::cust_sql_fields(), diff --git a/httemplate/search/elements/cust_pay_batch_top.html b/httemplate/search/elements/cust_pay_batch_top.html index 1dcc37ac1..bf3047769 100644 --- a/httemplate/search/elements/cust_pay_batch_top.html +++ b/httemplate/search/elements/cust_pay_batch_top.html @@ -120,6 +120,7 @@ my $fixed = $conf->config("batch-fixed_format-$payby"); tie my %download_formats, 'Tie::IxHash', ( '' => 'Default batch mode', +'NACHA' => '94 byte NACHA', 'csv-td_canada_trust-merchant_pc_batch' => 'CSV file for TD Canada Trust Merchant PC Batch', 'csv-chase_canada-E-xactBatch' => diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html index c60411107..234121fa3 100755 --- a/httemplate/search/elements/cust_pay_or_refund.html +++ b/httemplate/search/elements/cust_pay_or_refund.html @@ -51,6 +51,7 @@ Examples: 'sort_fields' => \@sort_fields, 'align' => $align, 'links' => \@links, + 'link_onclicks' => \@link_onclicks, 'color' => \@color, 'style' => \@style, &> @@ -134,11 +135,12 @@ if ( $cgi->param('tax_names') ) { } } -my @header = (); -my @fields = (); -my @sort_fields = (); +my @header; +my @fields; +my @sort_fields; my $align = ''; -my @links = (); +my @links; +my @link_onclicks; if ( $opt{'pre_header'} ) { push @header, @{ $opt{'pre_header'} }; $align .= 'c' x scalar(@{ $opt{'pre_header'} }); @@ -147,6 +149,16 @@ if ( $opt{'pre_header'} ) { push @sort_fields, @{ $opt{'pre_fields'} }; } +my $sub_receipt = sub { + my $obj = shift; + my $objnum = $obj->primary_key . '=' . $obj->get($obj->primary_key); + + include('/elements/popup_link_onclick.html', + 'action' => $p.'view/cust_pay.html?link=popup;'.$objnum, + 'actionlabel' => emt('Payment Receipt'), + ); +}; + push @header, "\u$name_singular", 'Amount', ; @@ -155,6 +167,7 @@ push @links, '', ''; push @fields, 'payby_payinfo_pretty', sub { sprintf('$%.2f', shift->$amount_field() ) }, ; +push @link_onclicks, $sub_receipt, '', push @sort_fields, '', $amount_field; if ( $unapplied ) { @@ -239,83 +252,146 @@ if ( $cgi->param('magic') ) { $title = $part_referral->referral. " $title"; } + # cust_classnum (false laziness w/ elements/cust_main_dayranges.html, prepaid_income.html, cust_bill_pay.html, cust_bill_pkg.html cust_bill_pkg_referral.html, unearned_detail.html, cust_credit.html, cust_credit_refund.html, cust_main::Search::search_sql) + if ( grep { $_ eq 'cust_classnum' } $cgi->param ) { + my @classnums = grep /^\d*$/, $cgi->param('cust_classnum'); + push @search, 'COALESCE( cust_main.classnum, 0) IN ( '. + join(',', map { $_ || '0' } @classnums ). + ' )' + if @classnums; + } + if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { push @search, "custnum = $1"; } if ( $cgi->param('payby') ) { - $cgi->param('payby') =~ - /^(CARD|CHEK|BILL|PREP|CASH|WEST|MCRD)(-(VisaMC|Amex|Discover|Maestro))?$/ - or die "illegal payby ". $cgi->param('payby'); - push @search, "$table.payby = '$1'"; - if ( $3 ) { - - my $cardtype = $3; - - my $search; - if ( $cardtype eq 'VisaMC' ) { - #avoid posix regexes for portability - $search = - " ( ( substring($table.payinfo from 1 for 1) = '4' ". - " AND substring($table.payinfo from 1 for 4) != '4936' ". - " AND substring($table.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49030[2-9]' ". - " AND substring($table.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49033[5-9]' ". - " AND substring($table.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49110[1-2]' ". - " AND substring($table.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49117[4-9]' ". - " AND substring($table.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49118[1-2]' ". - " )". - " OR substring($table.payinfo from 1 for 2) = '51' ". - " OR substring($table.payinfo from 1 for 2) = '52' ". - " OR substring($table.payinfo from 1 for 2) = '53' ". - " OR substring($table.payinfo from 1 for 2) = '54' ". - " OR substring($table.payinfo from 1 for 2) = '54' ". - " OR substring($table.payinfo from 1 for 2) = '55' ". - " OR substring($table.payinfo from 1 for 2) = '36' ". #Diner's int'l processed as Visa/MC inside US - " ) "; - } elsif ( $cardtype eq 'Amex' ) { - $search = - " ( substring($table.payinfo from 1 for 2 ) = '34' ". - " OR substring($table.payinfo from 1 for 2 ) = '37' ". - " ) "; - } elsif ( $cardtype eq 'Discover' ) { - $search = - " ( substring($table.payinfo from 1 for 4 ) = '6011' ". - " OR substring($table.payinfo from 1 for 2 ) = '65' ". - " OR substring($table.payinfo from 1 for 3 ) = '622' ". #China Union Pay processed as Discover outside CN - " ) "; - } elsif ( $cardtype eq 'Maestro' ) { - $search = - " ( substring($table.payinfo from 1 for 2 ) = '63' ". - " OR substring($table.payinfo from 1 for 2 ) = '67' ". - " OR substring($table.payinfo from 1 for 6 ) = '564182' ". - " OR substring($table.payinfo from 1 for 4 ) = '4936' ". - " OR substring($table.payinfo from 1 for 6 ) ". - " SIMILAR TO '49030[2-9]' ". - " OR substring($table.payinfo from 1 for 6 ) ". - " SIMILAR TO '49033[5-9]' ". - " OR substring($table.payinfo from 1 for 6 ) ". - " SIMILAR TO '49110[1-2]' ". - " OR substring($table.payinfo from 1 for 6 ) ". - " SIMILAR TO '49117[4-9]' ". - " OR substring($table.payinfo from 1 for 6 ) ". - " SIMILAR TO '49118[1-2]' ". - " ) "; - } else { - die "unknown card type $cardtype"; - } - my $masksearch = $search; - $masksearch =~ s/$table\.payinfo/$table.paymask/gi; + my @all_payby_search = (); + foreach my $payby ( $cgi->param('payby') ) { + + $payby =~ + /^(CARD|CHEK|BILL|PREP|CASH|WEST|MCRD)(-(VisaMC|Amex|Discover|Maestro|Tokenized))?$/ + or die "illegal payby $payby"; + + my $payby_search = "$table.payby = '$1'"; + + if ( $3 ) { + + my $cardtype = $3; + + my $search; + if ( $cardtype eq 'VisaMC' ) { + + #avoid posix regexes for portability + $search = + " ( ( substring($table.payinfo from 1 for 1) = '4' ". + " AND substring($table.payinfo from 1 for 4) != '4936' ". + " AND substring($table.payinfo from 1 for 6) ". + " NOT SIMILAR TO '49030[2-9]' ". + " AND substring($table.payinfo from 1 for 6) ". + " NOT SIMILAR TO '49033[5-9]' ". + " AND substring($table.payinfo from 1 for 6) ". + " NOT SIMILAR TO '49110[1-2]' ". + " AND substring($table.payinfo from 1 for 6) ". + " NOT SIMILAR TO '49117[4-9]' ". + " AND substring($table.payinfo from 1 for 6) ". + " NOT SIMILAR TO '49118[1-2]' ". + " )". + " OR substring($table.payinfo from 1 for 2) = '51' ". + " OR substring($table.payinfo from 1 for 2) = '52' ". + " OR substring($table.payinfo from 1 for 2) = '53' ". + " OR substring($table.payinfo from 1 for 2) = '54' ". + " OR substring($table.payinfo from 1 for 2) = '54' ". + " OR substring($table.payinfo from 1 for 2) = '55' ". +# " OR substring($table.payinfo from 1 for 2) = '36' ". #Diner's int'l was processed as Visa/MC inside US, now Discover + " ) "; + + } elsif ( $cardtype eq 'Amex' ) { + + $search = + " ( substring($table.payinfo from 1 for 2 ) = '34' ". + " OR substring($table.payinfo from 1 for 2 ) = '37' ". + " ) "; + + } elsif ( $cardtype eq 'Discover' ) { + + my $conf = new FS::Conf; + my $country = $conf->config('countrydefault') || 'US'; + + $search = + " ( substring($table.payinfo from 1 for 4 ) = '6011' ". + " OR substring($table.payinfo from 1 for 2 ) = '65' ". + " OR substring($table.payinfo from 1 for 3 ) = '300' ". + " OR substring($table.payinfo from 1 for 3 ) = '301' ". + " OR substring($table.payinfo from 1 for 3 ) = '302' ". + " OR substring($table.payinfo from 1 for 3 ) = '303' ". + " OR substring($table.payinfo from 1 for 3 ) = '304' ". + " OR substring($table.payinfo from 1 for 3 ) = '305' ". + " OR substring($table.payinfo from 1 for 4 ) = '3095' ". + " OR substring($table.payinfo from 1 for 2 ) = '36' ". + " OR substring($table.payinfo from 1 for 2 ) = '38' ". + " OR substring($table.payinfo from 1 for 2 ) = '39' ". + " OR substring($table.payinfo from 1 for 3 ) = '644' ". + " OR substring($table.payinfo from 1 for 3 ) = '645' ". + " OR substring($table.payinfo from 1 for 3 ) = '646' ". + " OR substring($table.payinfo from 1 for 3 ) = '647' ". + " OR substring($table.payinfo from 1 for 3 ) = '648' ". + " OR substring($table.payinfo from 1 for 3 ) = '649' ". + ( $country =~ /^(US|CA)$/ + ?" OR substring($table.payinfo from 1 for 4 ) = '3528' ". # JCB cards in the 3528-3589 range identified as Discover inside US/CA + " OR substring($table.payinfo from 1 for 4 ) = '3529' ". + " OR substring($table.payinfo from 1 for 3 ) = '353' ". + " OR substring($table.payinfo from 1 for 3 ) = '354' ". + " OR substring($table.payinfo from 1 for 3 ) = '355' ". + " OR substring($table.payinfo from 1 for 3 ) = '356' ". + " OR substring($table.payinfo from 1 for 3 ) = '357' ". + " OR substring($table.payinfo from 1 for 3 ) = '358' " + :"" + ). + " OR substring($table.payinfo from 1 for 3 ) = '622' ". #China Union Pay processed as Discover outside CN + " ) "; + + } elsif ( $cardtype eq 'Maestro' ) { + + $search = + " ( substring($table.payinfo from 1 for 2 ) = '63' ". + " OR substring($table.payinfo from 1 for 2 ) = '67' ". + " OR substring($table.payinfo from 1 for 6 ) = '564182' ". + " OR substring($table.payinfo from 1 for 4 ) = '4936' ". + " OR substring($table.payinfo from 1 for 6 ) ". + " SIMILAR TO '49030[2-9]' ". + " OR substring($table.payinfo from 1 for 6 ) ". + " SIMILAR TO '49033[5-9]' ". + " OR substring($table.payinfo from 1 for 6 ) ". + " SIMILAR TO '49110[1-2]' ". + " OR substring($table.payinfo from 1 for 6 ) ". + " SIMILAR TO '49117[4-9]' ". + " OR substring($table.payinfo from 1 for 6 ) ". + " SIMILAR TO '49118[1-2]' ". + " ) "; + + } elsif ( $cardtype eq 'Tokenized' ) { + + $search = " substring($table.payinfo from 1 for 2 ) = '99' "; + + } else { + die "unknown card type $cardtype"; + } + + my $masksearch = $search; + $masksearch =~ s/$table\.payinfo/$table.paymask/gi; + + $payby_search = "( $payby_search AND ( $search OR ( $table.paymask IS NOT NULL AND $masksearch ) ) )"; - push @search, - "( $search OR ( $table.paymask IS NOT NULL AND $masksearch ) )"; + } + + push @all_payby_search, $payby_search; } + + push @search, ' ( '. join(' OR ', @all_payby_search). ' ) ' if @all_payby_search; + } if ( $cgi->param('payinfo') ) { @@ -324,6 +400,16 @@ if ( $cgi->param('magic') ) { push @search, "$table.payinfo = '$1'"; } + if ( $cgi->param('ccpay') =~ /^([\w-:]+)$/ ) { + # I think that's all the characters we need to allow. + # To avoid confusion, this parameter searches both auth and order_number. + push @search, "($table.auth LIKE '$1%') OR ($table.order_number LIKE '$1%')"; + push @fields, 'auth', 'order_number'; + push @header, 'Auth #', 'Transaction #'; + $align .= 'rr'; + + } + if ( $cgi->param('usernum') =~ /^(\d+)$/ ) { push @search, "$table.usernum = $1"; } @@ -334,6 +420,7 @@ if ( $cgi->param('magic') ) { } my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); + push @search, "_date >= $beginning ", "_date <= $ending"; @@ -395,7 +482,7 @@ if ( $cgi->param('magic') ) { #here is the agent virtualization push @search, $curuser->agentnums_sql; - my $addl_from = ' LEFT JOIN cust_main USING ( custnum ) '; + my $addl_from = FS::UI::Web::join_cust_main($table); my $group_by = ''; if ( $cgi->param('tax_names') ) { diff --git a/httemplate/search/elements/report_cust_pay_or_refund.html b/httemplate/search/elements/report_cust_pay_or_refund.html index a2b90b47d..cdbcee234 100644 --- a/httemplate/search/elements/report_cust_pay_or_refund.html +++ b/httemplate/search/elements/report_cust_pay_or_refund.html @@ -30,43 +30,34 @@ Examples: <TR> <TD ALIGN="right"><% ucfirst(PL($name_singular)) %> of type: </TD> <TD> - <SELECT NAME="payby" onChange="payby_changed(this)"> - <OPTION VALUE=""><% mt('all') |h %></OPTION> - <OPTION VALUE="CARD"><% mt('credit card (all)') |h %></OPTION> - <OPTION VALUE="CARD-VisaMC"><% mt('credit card (Visa/MasterCard)') |h %></OPTION> - <OPTION VALUE="CARD-Amex"><% mt('credit card (American Express)') |h %></OPTION> - <OPTION VALUE="CARD-Discover"><% mt('credit card (Discover)') |h %></OPTION> - <OPTION VALUE="CARD-Maestro"><% mt('credit card (Maestro/Switch/Solo)') |h %></OPTION> - <OPTION VALUE="CHEK"><% mt('electronic check / ACH') |h %></OPTION> - <OPTION VALUE="BILL"><% mt('check') |h %></OPTION> - <OPTION VALUE="PREP"><% mt('prepaid card') |h %></OPTION> - <OPTION VALUE="CASH"><% mt('cash') |h %></OPTION> - <OPTION VALUE="WEST"><% mt('Western Union') |h %></OPTION> - <OPTION VALUE="MCRD"><% mt('manual credit card') |h %></OPTION> + <SELECT NAME="payby" SIZE=10 MULTIPLE> +%# <OPTION VALUE=""><% mt('all') |h %></OPTION> +%# <OPTION VALUE="CARD"><% mt('credit card (all)') |h %></OPTION> + <OPTION VALUE="CARD-VisaMC" SELECTED><% mt('credit card (Visa/MasterCard)') |h %></OPTION> + <OPTION VALUE="CARD-Amex" SELECTED><% mt('credit card (American Express)') |h %></OPTION> + <OPTION VALUE="CARD-Discover" SELECTED><% mt('credit card (Discover)') |h %></OPTION> + <OPTION VALUE="CARD-Maestro" SELECTED><% mt('credit card (Maestro/Switch/Solo)') |h %></OPTION> + <OPTION VALUE="CARD-Tokenized" SELECTED><% mt('credit card (Tokenized)') |h %></OPTION> + <OPTION VALUE="CHEK" SELECTED><% mt('electronic check / ACH') |h %></OPTION> + <OPTION VALUE="BILL" SELECTED><% mt('check') |h %></OPTION> + <OPTION VALUE="PREP" SELECTED><% mt('prepaid card') |h %></OPTION> + <OPTION VALUE="CASH" SELECTED><% mt('cash') |h %></OPTION> + <OPTION VALUE="WEST" SELECTED><% mt('Western Union') |h %></OPTION> + <OPTION VALUE="MCRD" SELECTED><% mt('manual credit card') |h %></OPTION> </SELECT> </TD> </TR> - <SCRIPT TYPE="text/javascript"> - - function payby_changed(what) { - if ( what.options[what.selectedIndex].value == 'BILL' ) { - document.getElementById('checkno_caption').style.color = '#000000'; - what.form.payinfo.disabled = false; - what.form.payinfo.style.backgroundColor = '#ffffff'; - } else { - document.getElementById('checkno_caption').style.color = '#bbbbbb'; - what.form.payinfo.disabled = true; - what.form.payinfo.style.backgroundColor = '#dddddd'; - } - } - - </SCRIPT> - <TR> - <TD ALIGN="right"><FONT ID="checkno_caption" COLOR="#bbbbbb"><% mt('Check #:') |h %> </FONT></TD> + <TD ALIGN="right"><% mt('Check #:') |h %> </TD> + <TD> + <INPUT TYPE="text" ID="payinfo" NAME="payinfo"> + </TD> + </TR> + <TR> + <TD ALIGN="right"><% mt('Transaction #:') |h %> </TD> <TD> - <INPUT TYPE="text" NAME="payinfo" DISABLED STYLE="background-color: #dddddd"> + <INPUT TYPE="text" ID="ccpay" NAME="ccpay"> </TD> </TR> @@ -83,7 +74,8 @@ Examples: <TD> <TABLE> <& /elements/tr-input-beginning_ending.html, - layout => 'horiz', + layout => 'horiz', + input_time => $conf->exists('report-cust_pay-select_time'), &> </TABLE> </TD> @@ -133,6 +125,8 @@ my $name_singular = $opt{'name_singular'}; die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); +my $conf = new FS::Conf; + my $void = $cgi->param('void') ? 1 : 0; my $unapplied = $cgi->param('unapplied') ? 1 : 0; diff --git a/httemplate/search/elements/report_svc_Common.html b/httemplate/search/elements/report_svc_Common.html new file mode 100644 index 000000000..434197078 --- /dev/null +++ b/httemplate/search/elements/report_svc_Common.html @@ -0,0 +1,122 @@ +<%doc> + +Example: + + <& elements/report_svc_Common.html, + + #required + 'table' => 'svc_something', + 'title' => 'Page title', + + #optional + 'action' => 'svc_tablename.html', #defaults to svc_tablename.html + + &> + +</%doc> +<& /elements/header.html, $title &> + +<FORM ACTION="<% $opt{'action'} || $opt{'table'}. '.html' %>" METHOD="GET"> +<INPUT TYPE="hidden" NAME="magic" VALUE="advanced"> +<INPUT TYPE="hidden" NAME="custnum" VALUE="<% $custnum %>"> + + <TABLE BGCOLOR="#cccccc" CELLSPACING=0> + + <TR> + <TH CLASS="background" COLSPAN=2 ALIGN="left"><FONT SIZE="+1"><% mt('Search options') |h %></FONT></TH> + </TR> + +% unless ( $custnum ) { + + <& /elements/tr-select-agent.html, + curr_value => scalar( $cgi->param('agentnum') ), + disable_empty => 0, + &> + + <& /elements/tr-select-cust_main-status.html, + label => 'Customer Status', + field => 'cust_status', + &> + + <& /elements/tr-select-payby.html, + label => emt('Payment method:'), + payby_type => 'cust', + multiple => 1, + all_selected => 1, + &> + + <& /elements/tr-input-money.html, + label => 'Balance over', + field => 'balance', + &> + + <& /elements/tr-input-text.html, + label => 'Balance age (days)', + field => 'balance_days', + size => 4, + &> + +% } + +% # just this customer's domains? +%# <& /elements/tr-select-domain.html, +%# 'element_name' => 'domsvc', +%# 'curr_value' => scalar( $cgi->param('domsvc') ), +%# 'disable_empty' => 0, +%# &> + + <& /elements/tr-selectmultiple-part_pkg.html &> + + <& /elements/tr-select-part_svc.html, + 'svcdb' => $svcdb, + 'label' => 'Services', + &> + + <TR> + <TH CLASS="background" COLSPAN=2> </TH> + </TR> + + <TR> + <TH CLASS="background" COLSPAN=2 ALIGN="left"><FONT SIZE="+1"><% mt('Display options') |h %></FONT></TH> + </TR> + +% #"package fields" ala advanced svc_acct search? +% #move to /elements/tr-select-cust_pkg-fields and use it from there if so... + + <& /elements/tr-select-cust-fields.html &> + + </TABLE> + +<BR> +<INPUT TYPE="submit" VALUE="<% mt('Get Report') |h %>"> + +</FORM> + +<& /elements/footer.html &> +<%init> + +my(%opt) = @_; + +my $svcdb = $opt{'table'}; + +my $name = "FS::$svcdb"->table_info->{'name_plural'} + || PL( "FS::$svcdb"->table_info->{'name'} ); + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right("Services: $name: Advanced search"); + +my $title = $opt{'title'}; + +#false laziness w/report_cust_pkg.html +my( $custnum, $cust_main) = ('', ''); +if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { + $custnum = $1; + my $cust_main = qsearchs({ + 'table' => 'cust_main', + 'hashref' => { 'custnum' => $custnum }, + 'extra_sql' => ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql, + }) or die "unknown custnum $custnum"; + $title = mt("$title: [_1]", $cust_main->name); +} + +</%init> diff --git a/httemplate/search/elements/search-html.html b/httemplate/search/elements/search-html.html index d7e81282b..bee33cfe8 100644 --- a/httemplate/search/elements/search-html.html +++ b/httemplate/search/elements/search-html.html @@ -253,12 +253,29 @@ % $bgcolor = $bgcolor1; % } - <TR> +% my $rowstyle = ''; +% if ( $row eq $opt{'footer_data'} ) { +% $rowstyle = ' STYLE="border-top: dashed 1px black; font-style: italic"'; +% $bgcolor = '#dddddd'; +% } + +% my $trid = ''; +% if ( $opt{'link_field' } ) { +% my $link_field = $opt{'link_field'}; +% if ( ref($link_field) eq 'CODE' ) { +% $trid = &{$link_field}($row); +% } else { +% $trid = $row->$link_field(); +% } +% } + <TR ID="<%$trid |h%>"<%$rowstyle%>> + % if ( $opt{'fields'} ) { % % my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; % my $onclicks = $opt{'link_onclicks'} ? [ @{$opt{'link_onclicks'}} ] : []; +% my $tooltips = $opt{'tooltips'} ? [ @{$opt{'tooltips'}} ] : []; % my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : ''; % my $colors = $opt{'color'} ? [ @{$opt{'color'}} ] : []; % my $sizes = $opt{'size'} ? [ @{$opt{'size'}} ] : []; @@ -341,9 +358,9 @@ % $_ =~ /^\d+$/ ) { % # for the 'straight SQL' case: specify fields % # by position -% $row->[$_]; +% encode_entities($row->[$_]); % } else { -% $row->$_(); +% encode_entities($row->$_()); % } % } % @{$opt{'fields'}} @@ -360,6 +377,7 @@ % if ( $links ) { % my $link = shift @$links; % my $onclick = shift @$onclicks; +% my $tooltip = shift @$tooltips; % % if ( ! $opt{'agent_virt'} % || ( $null_link && ! $row->agentnum ) @@ -374,6 +392,14 @@ % if ref($onclick) eq 'CODE'; % $onclick = qq( onClick="$onclick") if $onclick; % +% $tooltip = &{$tooltip}($row) +% if ref($tooltip) eq 'CODE'; +% $tooltip = qq! id="a$id" !. +% qq! onmouseover="return overlib(!. +% $m->interp->apply_escapes($tooltip, 'h', 'js_string'). +% qq!, FGCLASS, 'tooltip', REF, 'a$id', !. +% qq!REFC, 'LL', REFP, 'UL')"! if $tooltip; +% % if ( $link ) { % my( $url, $method ) = @{$link}; % if ( ref($method) eq 'CODE' ) { @@ -381,11 +407,16 @@ % } else { % $a = $url. $row->$method(); % } -% $a = qq(<A HREF="$a"$onclick>); +% $a = qq(<A HREF="$a"$onclick$tooltip>); % } % elsif ( $onclick ) { % $a = qq(<A HREF="javascript:void(0);"$onclick>); % } +% elsif ( $tooltip ) { +% $a = qq(<A $tooltip>); +% } +% $id++; + % } % % } @@ -499,4 +530,5 @@ $count_sth->execute my $count_arrayref = $count_sth->fetchrow_arrayref; my $total = $count_arrayref->[0]; +my $id = 0; </%init> diff --git a/httemplate/search/elements/search-xls.html b/httemplate/search/elements/search-xls.html index 94d88b096..8334497d2 100644 --- a/httemplate/search/elements/search-xls.html +++ b/httemplate/search/elements/search-xls.html @@ -1,4 +1,3 @@ -<% $data %> <%init> my %args = @_; @@ -7,6 +6,8 @@ my $header = $args{'header'}; my $rows = $args{'rows'}; my %opt = %{ $args{'opt'} }; +my $style = $opt{'style'}; + my $override = scalar(@$rows) >= 65536 ? 'XLSX' : ''; my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format($override); @@ -31,7 +32,10 @@ my $XLS = new IO::Scalar \$data; my $workbook = $format->{class}->new($XLS) or die "Error opening Excel file: $!"; -my $worksheet = $workbook->add_worksheet(substr($opt{'title'},0,31)); +my $title = $opt{'title'}; +$title =~ s/[\[\]\:\*\?\/\/]//g; +$title = substr($title, 0, 31); +my $worksheet = $workbook->add_worksheet($title); $worksheet->protect(); @@ -43,6 +47,12 @@ my $header_format = $workbook->add_format( bg_color => 55, #22, bottom => 3, ); +my $footer_format = $workbook->add_format( + italic => 1, + locked => 1, + bg_color => 55, + top => 3, +); my $default_format = $workbook->add_format(locked => 0); my %money_format; @@ -51,10 +61,24 @@ my $money_char = FS::Conf->new->config('money_char') || '$'; my %date_format; xl_parse_date_init(); +my %bold_format; + my $writer = sub { # Wrapper for $worksheet->write. # Do any massaging of the value/format here. my ($r, $c, $value, $format) = @_; + #warn "writer called with format $format\n"; + + if ( $style->[$c] eq 'b' or $value =~ /<b>/i ) { # the only one in common use + $value =~ s[</?b>][]ig; + if ( !exists($bold_format{$format}) ) { + $bold_format{$format} = $workbook->add_format(); + $bold_format{$format}->copy($format); + $bold_format{$format}->set_bold(); + } + $format = $bold_format{$format}; + } + # convert HTML entities # both Spreadsheet::WriteExcel and Excel::Writer::XLSX accept UTF-8 strings $value = decode_entities($value); @@ -87,6 +111,7 @@ my $writer = sub { # String: replace line breaks with newlines $value =~ s/<BR>/\n/gi; } + #warn "writing with format $format\n"; $worksheet->write($r, $c, $value, $format); }; @@ -141,12 +166,13 @@ if ( $opt{'footer'} ) { if ( ref($item) eq 'CODE' ) { $item = &{$item}(); } - $writer->( $r, $c++, $item, $header_format ); + $writer->( $r, $c++, $item, $footer_format ); } } $workbook->close();# or die "Error creating .xls file: $!"; http_header('Content-Length' => length($data) ); +$m->print($data); </%init> diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index eca68a2f8..8f6272030 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -167,6 +167,11 @@ Example: # miscellany 'download_label' => 'Download this report', # defaults to 'Download full results' + 'link_field' => 'pkgpart' + # will create internal links for each row, + # with the value of this field as the NAME attribute + # If this is a coderef, will evaluate it, passing the + # row as an argument, and use the result as the NAME. &> </%doc> @@ -176,7 +181,9 @@ Example: % % } elsif ( $type =~ /\.xls$/ ) { % -<% include('search-xls.html', header=>$header, rows=>$rows, opt=>\%opt ) %> +<& 'search-xls.html', header=>$header, rows=>$rows, opt=>\%opt &>\ +% # prevent the caller from polluting our output stream +% $m->abort; % % } elsif ( $type eq 'xml' ) { % @@ -346,7 +353,7 @@ if ( $opt{'disableable'} ) { my $limit = ''; my($confmax, $maxrecords, $offset ); -unless ( $type =~ /^(csv|\w*.xls)$/) { +unless ( $type =~ /^(csv|xml|\w*.xls)$/) { # html mode unless (exists($opt{count_query}) && length($opt{count_query})) { ( $opt{count_query} = $opt{query} ) =~ @@ -446,4 +453,6 @@ if ( ref($opt{query}) ) { $header ||= $sth->{NAME}; } +push @$rows, $opt{'footer_data'} if $opt{'footer_data'}; + </%init> diff --git a/httemplate/search/elements/svc_Common.html b/httemplate/search/elements/svc_Common.html new file mode 100644 index 000000000..56c75bba3 --- /dev/null +++ b/httemplate/search/elements/svc_Common.html @@ -0,0 +1,48 @@ +<& search.html, %opt &> +<%doc> +Currently does nothing but insert the classnames for fields chosen from an +inventory class. +</%doc> +<%init> +my %opt = @_; +my $query = $opt{query}; +my $svcdb = $query->{'table'}; + +# to avoid looking up the inventory class of every service in the database, +# keep as much of the base query as possible. +my $item_query = { %$query }; +$item_query->{'table'} = 'inventory_item'; +$item_query->{'addl_from'} = + " JOIN ( $svcdb ". $query->{'addl_from'} . + ") ON inventory_item.svcnum = $svcdb.svcnum ". + " JOIN inventory_class ON (inventory_item.classnum = inventory_class.classnum)"; +# avoid conflict with inventory_item.agentnum +$item_query->{'extra_sql'} =~ s/ agentnum/ cust_main.agentnum/g; +$item_query->{'select'} = 'inventory_item.svcnum, '. + 'inventory_item.svc_field, '. + 'inventory_class.classname'; +my @items = qsearch($item_query); +my %item_fields; +foreach my $i (@items) { + $item_fields{ $i->svc_field } ||= {}; + $item_fields{ $i->svc_field }{ $i->svcnum } = $i->classname; +} + +$opt{'sort_fields'} ||= []; +for ( my $i = 0; $i < @{ $opt{'fields'} }; $i++ ) { + my $f = $opt{'fields'}[$i]; + next if ref($f); # it's not a plain table column + $opt{'sort_fields'}[$i] ||= $f; + my $classnames = $item_fields{$f}; # hashref of svcnum -> classname + next if !$classnames; # there are no inventory items in this column + $opt{'fields'}[$i] = sub { + my $svc = $_[0]; + if ( exists($classnames->{$svc->svcnum}) ) { + return $svc->$f . '<BR><I>('. $classnames->{$svc->svcnum} . ')</I>'; + } else { + return $svc->$f; + } + }; #sub +} + +</%init> diff --git a/httemplate/search/employee_audit.html b/httemplate/search/employee_audit.html index 753c7bff3..2bc6ff46e 100644 --- a/httemplate/search/employee_audit.html +++ b/httemplate/search/employee_audit.html @@ -7,7 +7,7 @@ <%init> die "access denied" - unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + unless $FS::CurrentUser::CurrentUser->access_right('Employees: Audit Report'); my %tables = ( cust_pay => 'Payments', diff --git a/httemplate/search/h_cust_pay.html b/httemplate/search/h_cust_pay.html index 99330fadd..6d2dd9955 100755 --- a/httemplate/search/h_cust_pay.html +++ b/httemplate/search/h_cust_pay.html @@ -1,9 +1,8 @@ -<% include( 'elements/cust_pay_or_refund.html', +<& elements/cust_pay_or_refund.html, 'table' => 'h_cust_pay', 'amount_field' => 'paid', 'name_singular' => 'payment', 'name_verb' => 'paid', 'pre_header' => [ 'Transaction', 'By' ], 'pre_fields' => [ 'history_action', 'history_user' ], - ) -%> +&> diff --git a/httemplate/search/h_cust_pkg.html b/httemplate/search/h_cust_pkg.html new file mode 100644 index 000000000..b23a57be6 --- /dev/null +++ b/httemplate/search/h_cust_pkg.html @@ -0,0 +1,239 @@ +<& elements/search.html, + 'html_init' => $html_init, + 'title' => $title, + 'name' => 'packages', + 'query' => $query, + 'count_query' => $count_query, + 'header' => [ emt('#'), + emt('Quan.'), + emt('Package'), + emt('Class'), + emt('Status'), + emt('Ordered by'), + emt('Setup'), + emt('Base Recur'), + emt('Freq.'), + emt('Setup'), + emt('Last bill'), + emt('Next bill'), + emt('Adjourn'), + emt('Susp.'), + emt('Susp. delay'), + emt('Expire'), + emt('Contract end'), + emt('Changed'), + emt('Cancel'), + emt('Reason'), + FS::UI::Web::cust_header( + $cgi->param('cust_fields') + ), + emt('As of'), + ], + 'fields' => [ + 'pkgnum', + 'quantity', + 'pkg', + 'classname', + sub { ucfirst(shift->status); }, + 'otaker', + 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 { FS::part_pkg::freq_pretty(shift); }, + + ( map { time_or_blank($_) } + qw( setup last_bill bill adjourn susp dundate expire contract_end change_date cancel ) ), + + sub { my $self = shift; + my $return = ''; + foreach my $action ( qw ( cancel susp ) ) { + my $reason = $self->last_reason($action); + $return = $reason->reason if $reason; + last if $return; + } + $return; + }, + + \&FS::UI::Web::cust_fields, + # in cust_pkg.cgi, service labels would go here + time_or_blank('history_date'), + ], + 'color' => [ + '', + '', + '', + '', + sub { shift->statuscolor; }, + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + FS::UI::Web::cust_colors(), + '', + ], + 'style' => [ '', '', '', '', 'b', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', + FS::UI::Web::cust_styles() ], + 'size' => [ '', '', '', '', '-1' ], + 'align' => 'rrlcccrrlrrrrrrrrrrl'. FS::UI::Web::cust_aligns(). 'r', + 'links' => [ + $link, + $link, + $link, + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', + '', # link to changed-from package? + '', + '', + '', + ( map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header( + $cgi->param('cust_fields') + ) + ), + '', + ], +&> +<%init> + +# shamelessly cloned from cust_pkg.cgi, with minimal changes to make it work + +my $curuser = $FS::CurrentUser::CurrentUser; + +die "access denied" + unless $curuser->access_right('List packages'); + +my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; + +my %search_hash = (); + +#some false laziness w/misc/bulk_change_pkg.cgi + +$search_hash{'query'} = $cgi->keywords; + +#scalars +for (qw( agentnum custnum magic status custom cust_fields pkgbatch )) { + $search_hash{$_} = $cgi->param($_) if $cgi->param($_); +} + +#arrays +for my $param (qw( pkgpart classnum )) { + $search_hash{$param} = [ $cgi->param($param) ] + if grep { $_ eq $param } $cgi->param; +} + +#scalars that need to be passed if empty +for my $param (qw( censustract censustract2 )) { + $search_hash{$param} = $cgi->param($param) || '' + if grep { $_ eq $param } $cgi->param; +} + +my $report_option = $cgi->param('report_option'); +$search_hash{report_option} = $report_option if $report_option; + +for my $param (grep /^report_option_any/, $cgi->param) { + $search_hash{$param} = $cgi->param($param); +} + +### +# parse dates +### + +#false laziness w/report_cust_pkg.html +my %disable = ( + 'all' => {}, + 'one-time charge' => { 'last_bill'=>1, 'bill'=>1, 'adjourn'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, 'contract_end'=>1, 'dundate'=>1, }, + 'active' => { 'susp'=>1, 'cancel'=>1 }, + 'suspended' => { 'cancel' =>1, 'dundate'=>1, }, + 'cancelled' => {}, + '' => {}, +); + +foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel active )) { + + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, $field); + + next if $beginning == 0 && $ending == 4294967295 + or $disable{$cgi->param('status')}->{$field}; + + $search_hash{$field} = [ $beginning, $ending ]; + +} + +my $date; +if ( $cgi->param('date') =~ /^(\d+)$/ ) { + $date = $1; + $search_hash{'date'} = $date; +} + +my $query = FS::h_cust_pkg->search(\%search_hash); +my $count_query = delete($query->{'count_query'}); + +my $show = $curuser->default_customer_view =~ /^(jumbo|packages)$/ + ? '' + : ';show=packages'; + +my $link = sub { + 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 { + my $cust_pkg = shift; + $cust_pkg->cust_main_custnum + ? [ "${p}view/cust_main.cgi?", 'custnum' ] + : ''; +}; + +sub time_or_blank { + my $column = shift; + return sub { + my $record = shift; + my $value = $record->get($column); #mmm closures + $value ? time2str('%b %d %Y', $value ) : ''; + }; +} + +my $html_init = ''; + +my $title = 'Historical Package View - '; +if ( $date == 0 ) { + $title .= 'start'; +} elsif ( $date == 4294967295 ) { + $title .= 'present'; +} else { + $title .= time2str('%h %o %Y', $date); +} +</%init> diff --git a/httemplate/search/inventory_item.html b/httemplate/search/inventory_item.html index 086c8e92d..0e4251f74 100644 --- a/httemplate/search/inventory_item.html +++ b/httemplate/search/inventory_item.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => $title, 'menubar' => [ 'View inventory classes' => @@ -87,8 +87,8 @@ <INPUT TYPE="hidden" NAME="classnum" VALUE="$classnum"> <INPUT TYPE="hidden" NAME="avail" VALUE="! .$cgi->param('avail') . '">', #' 'html_foot' => $sub_foot, - ) -%> + +&> <%init> my $curuser = $FS::CurrentUser::CurrentUser; @@ -157,7 +157,7 @@ my $link_cust = sub { 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 ) '; + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); my $areboxes = 0; my $sub_checkbox = sub { diff --git a/httemplate/search/log.html b/httemplate/search/log.html new file mode 100644 index 000000000..d1bfb6cc9 --- /dev/null +++ b/httemplate/search/log.html @@ -0,0 +1,221 @@ +<& elements/search.html, + 'title' => 'System Log', + 'name_singular' => 'event', + 'html_init' => include('.head'), + 'query' => $query, + 'count_query' => $count_query, + 'header' => [ #'#', # lognum, probably not useful + 'Date', + 'Level', + 'Context', + 'Applies To', + 'Message', + ], + 'fields' => [ #'lognum', + $date_sub, + $level_sub, + $context_sub, + $object_sub, + $message_sub, + ], + 'sort_fields' => [ + '_date', + 'level', + '', + 'tablename,tablenum', + 'message', + ], + 'links' => [ + '', #date + '', #level + '', #context + $object_link_sub, + '', #message + ], + 'tooltips' => [ + '', #date + '', #level + $tt_sub, + '', #object + $tt_sub, + ], + 'color' => [ + $color_sub, + $color_sub, + '', + '', + '', + ], + # aligns + 'download_label' => 'Download this log', +&>\ +<%def .head> +<STYLE type="text/css"> +a:link {text-decoration: none} +a:visited {text-decoration: none} +.tooltip { + background-color: #ffffff; + font-size: 100%; + font-weight: bold; +} +</STYLE> +<FORM ACTION="<%$p%>search/log.html" METHOD="GET"> +<TABLE CELLSPACING="10"> +<TR> + <TD>From + <& /elements/input-date-field.html, { + name => 'beginning', + value => $cgi->param('beginning'), + } &> + </TD> + <TD>To + <& /elements/input-date-field.html, { + name => 'ending', + value => $cgi->param('ending') || '', + noinit => 1, + } &> + </TD> +</TR> +<TR> + <TD>Level + <& /elements/select.html, + field => 'min_level', + options => [ 0..7 ], + labels => { map {$_ => $FS::Log::LEVELS[$_]} 0..7 }, + curr_value => $cgi->param('min_level'), + &> + to + <& /elements/select.html, + field => 'max_level', + options => [ 0..7 ], + labels => { map {$_ => $FS::Log::LEVELS[$_]} 0..7 }, + curr_value => $cgi->param('max_level'), + &> + </TD> + <TD> + Context + <& /elements/select.html, + field => 'context', + options => \@contexts, + labels => { map {$_, $_} @contexts }, + curr_value => ($cgi->param('context') || ''), + &> + </TD> +</TR> +<TR> + <TD COLSPAN=2> + Containing text + <& /elements/input-text.html, + field => 'message', + size => 30, + size => 30, + curr_value => ($cgi->param('message') || ''), + &> + <DIV STYLE="display:inline; float:right"> + <INPUT TYPE="submit" VALUE="Refresh"> + </DIV> + </TD> +</TR> +</TABLE> +</%def> +<%once> +my $date_sub = sub { time2str('%Y-%m-%d %T', $_[0]->_date) }; + +my $level_sub = sub { $FS::Log::LEVELS[$_[0]->level] }; + +my $context_sub = sub { + my $log = shift; + ($log->context)[-1] . (scalar($log->context) > 1 ? '...' : '') ; + # XXX find a way to make this use less space (dropdown?) +}; + +my $tt_sub = sub { + my $log = shift; + my @context = $log->context; + # don't create a tooltip if there's only one context entry and the + # message isn't cut off + return '' if @context == 1 and length($log->message) <= 60; + my $html = '<DIV CLASS="tooltip">'.(shift @context).'</DIV>'; + my $pre = '↳'; + foreach (@context, $log->message) { + $html .= "<DIV>$pre$_</DIV>"; + $pre = ' '.$pre; + } + $html; +}; + +my $object_sub = sub { + my $log = shift; + return '' unless $log->tablename; + # this is a sysadmin log; anyone reading it should be able to understand + # 'cust_main #2319' with no trouble. + $log->tablename . ' #' . $log->tablenum; +}; + +my $message_sub = sub { + my $log = shift; + my $message = $log->message; + if ( length($message) > 60 ) { # pretty arbitrary + $message = substr($message, 0, 57) . '...'; + } + $message; +}; + +my $object_link_sub = sub { + my $log = shift; + my $table = $log->tablename or return; + # sigh + if ( grep {$_ eq $table} (qw( cust_bill cust_main cust_pkg cust_svc )) + or $table =~ /^svc_/ ) + { + + return [ $fsurl.'view/'.$table.'.cgi?'. $log->tablenum ]; + + } elsif ( grep {$_ eq $table} (qw( cust_msg cust_pay cust_pay_void + cust_refund cust_statement )) ) + { + + return [ $fsurl.'view/'.$table.'.html?', $log->tablenum ]; + + } else { # you're on your own + + return ''; + + } +}; + +my @colors = ( + '404040', #debug + '0000aa', #info + '00aa00', #notice + 'aa0066', #warning + '000000', #error + 'aa0000', #critical + 'ff0000', #alert + 'ff0000', #emergency +); + +my $color_sub = sub { $colors[ $_[0]->level ]; }; + +my @contexts = ('', sort FS::log_context->contexts); +</%once> +<%init> +my $curuser = $FS::CurrentUser::CurrentUser; +die "access denied" + unless $curuser->access_right([ 'View system logs', 'Configuration' ]); + +$cgi->param('min_level', 0) unless defined($cgi->param('min_level')); +$cgi->param('max_level', 7) unless defined($cgi->param('max_level')); + +my %search = (); +$search{'date'} = [ FS::UI::Web::parse_beginning_ending($cgi) ]; +$search{'level'} = [ $cgi->param('min_level'), $cgi->param('max_level') ]; +foreach my $param (qw(agentnum context tablename tablenum custnum message)) { + if ( $cgi->param($param) ) { + $search{$param} = $cgi->param($param); + } +} +my $query = FS::log->search(\%search); # validates everything +my $count_query = delete $query->{'count_query'}; + +</%init> diff --git a/httemplate/search/mailinglistmember.html b/httemplate/search/mailinglistmember.html index ee395f416..a678d45ed 100644 --- a/httemplate/search/mailinglistmember.html +++ b/httemplate/search/mailinglistmember.html @@ -1,4 +1,4 @@ -<% include('elements/search.html', +<& elements/search.html, 'title' => $title, 'name_singular' => 'member', 'query' => $query, @@ -6,8 +6,7 @@ 'header' => [ 'Email address' ], 'fields' => [ $email_sub, ], #just this one for now 'html_init' => $html_init, - ) -%> +&> <%init> #XXX ACL: diff --git a/httemplate/search/part_pkg.html b/httemplate/search/part_pkg.html index 57da9d459..a90f13c95 100644 --- a/httemplate/search/part_pkg.html +++ b/httemplate/search/part_pkg.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => $title, 'name_singular' => $name, 'header' => \@header, @@ -14,8 +14,8 @@ 'links' => \@links, 'align' => $align, 'sort_fields' => [], - ) -%> + +&> <%init> #this is about reports about packages definitions (starting w/commission ones) @@ -23,7 +23,7 @@ my $curuser = $FS::CurrentUser::CurrentUser; die "access denied" - unless $curuser->access_right('Financial reports'); + unless $curuser->access_right('Employees: Commission Report'); #that's all this does so far my $conf = new FS::Conf; my $money_char = $conf->config('money_char') || '$'; diff --git a/httemplate/search/pay_batch.cgi b/httemplate/search/pay_batch.cgi index aeaa012f4..620996abd 100755 --- a/httemplate/search/pay_batch.cgi +++ b/httemplate/search/pay_batch.cgi @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => 'Payment Batches', 'name_singular' => 'batch', 'query' => { 'table' => 'pay_batch', @@ -101,8 +101,7 @@ ], 'html_init' => $html_init, 'html_foot' => include('.upload_incoming'), - ) -%> +&> <%def .upload_incoming> % if ( FS::payment_gateway->count("gateway_namespace = 'Business::BatchPayment' AND disabled IS NULL") > 0 ) { <& /elements/form-file_upload.html, @@ -149,16 +148,10 @@ my $count_query = 'SELECT COUNT(*) FROM pay_batch'; my($begin, $end) = ( '', '' ); my @where; -if ( $cgi->param('beginning') - && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { - $begin = parse_datetime($1); - push @where, "download >= $begin"; -} -if ( $cgi->param('ending') - && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { - $end = parse_datetime($1) + 86399; - push @where, "download < $end"; -} + +my($beginning,$ending) = FS::UI::Web::parse_beginning_ending($cgi); +push @where, "( (download >= $beginning AND download <= $ending) ". + ' OR download IS NULL )'; my @status; if ( $cgi->param('open') ) { diff --git a/httemplate/search/phone_avail.html b/httemplate/search/phone_avail.html index 1335379ae..faf354420 100644 --- a/httemplate/search/phone_avail.html +++ b/httemplate/search/phone_avail.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => 'Phone Number (DID) Search Results', 'name_singular' => 'phone number', 'query' => { @@ -81,8 +81,8 @@ FS::UI::Web::cust_styles(), '', ], - ) -%> + +&> <%init> die "access denied" @@ -125,9 +125,11 @@ my $search = scalar(@search) 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 ) '; + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); my $count_query = "SELECT COUNT(*) FROM phone_avail $search"; #$addl_from? +# All of these relationships are left joined in the many-to-one direction, +# so including $addl_from won't affect the count. Logic! my $hashref = {}; $hashref->{'ordernum'} = $1 if $cgi->param('ordernum') =~ /^(\d+)$/; diff --git a/httemplate/search/phone_inventory_provisioned.html b/httemplate/search/phone_inventory_provisioned.html index 03d21547d..b3efdbd77 100644 --- a/httemplate/search/phone_inventory_provisioned.html +++ b/httemplate/search/phone_inventory_provisioned.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => 'LATA Search Results', 'name_singular' => 'LATA', 'query' => { @@ -72,8 +72,8 @@ '', '', ], - ) -%> + +&> <%init> die "access denied" diff --git a/httemplate/search/prepaid_income.html b/httemplate/search/prepaid_income.html index ebac5a2a9..cb58a666d 100644 --- a/httemplate/search/prepaid_income.html +++ b/httemplate/search/prepaid_income.html @@ -129,6 +129,16 @@ if ( $cgi->param('status') =~ /^([a-z]+)$/ ) { push @where, FS::cust_main->cust_status_sql . " = '$status'"; } +# cust_classnum (false laziness w/ elements/cust_main_dayranges.html, elements/cust_pay_or_refund.html, cust_bill_pay.html, cust_bill_pkg.html, cust_bill_pkg_referral.html, unearned_detail.html, cust_credit.html, cust_credit_refund.html, cust_main::Search::search_sql) +if ( grep { $_ eq 'cust_classnum' } $cgi->param ) { + my @classnums = grep /^\d*$/, $cgi->param('cust_classnum'); + $link .= ";cust_classnum=$_" foreach @classnums; + push @where, 'COALESCE( cust_main.classnum, 0) IN ( '. + join(',', map { $_ || '0' } @classnums ). + ' )' + if @classnums; +} + my %total = (); my %total_legacy = (); foreach my $agentnum (@agentnums) { diff --git a/httemplate/search/prepay_credit.html b/httemplate/search/prepay_credit.html index 36403511b..7566e657e 100644 --- a/httemplate/search/prepay_credit.html +++ b/httemplate/search/prepay_credit.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => 'Unused Prepaid Cards'. ($agent ? ' for '. $agent->agent : ''), 'menubar' => [ @@ -47,8 +47,8 @@ $agent ? [ "${p}edit/agent.cgi?", 'agentnum' ] : ''; }, ], - ) -%> + +&> <%init> die "access denied" diff --git a/httemplate/search/prospect_main.html b/httemplate/search/prospect_main.html index 328d1202f..ab37b9089 100644 --- a/httemplate/search/prospect_main.html +++ b/httemplate/search/prospect_main.html @@ -1,4 +1,4 @@ -<% include('elements/search.html', +<& elements/search.html, 'title' => 'Prospect Search Results', 'name_singular' => 'prospect', 'query' => $query, @@ -23,8 +23,7 @@ '', #link to contact edit??? ], 'agent_virt' => 1, - ) -%> +&> <%init> die "access denied" diff --git a/httemplate/search/qual.cgi b/httemplate/search/qual.cgi index 7133ef056..7b718e498 100755 --- a/httemplate/search/qual.cgi +++ b/httemplate/search/qual.cgi @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => 'Qualifications', 'name_singular' => 'qualification', 'query' => { 'table' => 'qual', @@ -51,8 +51,8 @@ '', '', ], - ) -%> + +&> <%init> die "access denied" diff --git a/httemplate/search/queue.html b/httemplate/search/queue.html index 1c124706c..141c535da 100644 --- a/httemplate/search/queue.html +++ b/httemplate/search/queue.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => 'Job Queue', 'name' => 'jobs', 'html_form' => qq!<FORM NAME="jobForm" ACTION="$p/misc/queue.cgi" METHOD="POST">!, @@ -120,9 +120,8 @@ ''; } }, - ) - -%> + +&> <%init> die "access denied" diff --git a/httemplate/search/quotation.html b/httemplate/search/quotation.html index 259c85c22..fbc35bea1 100755 --- a/httemplate/search/quotation.html +++ b/httemplate/search/quotation.html @@ -72,7 +72,7 @@ die "access denied" unless $curuser->access_right('List quotations'); my $join_prospect_main = 'LEFT JOIN prospect_main USING ( prospectnum )'; -my $join_cust_main = 'LEFT JOIN cust_main ON ( quotation.custnum = cust_main.custnum )'; +my $join_cust_main = FS::UI::Web::join_cust_main('quotation'); #here is the agent virtualization my $agentnums_sql = ' ( '. $curuser->agentnums_sql( table=>'prospect_main' ). diff --git a/httemplate/search/reg_code.html b/httemplate/search/reg_code.html index f7d6d2061..42211e571 100644 --- a/httemplate/search/reg_code.html +++ b/httemplate/search/reg_code.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => 'Unused Registration Codes for '. $agent->agent, 'name' => 'registration codes', @@ -23,8 +23,8 @@ #$plink, '', ], - ) -%> + +&> <%init> die "access denied" diff --git a/httemplate/search/report_477.html b/httemplate/search/report_477.html index f593a94d8..b842b1f3f 100755 --- a/httemplate/search/report_477.html +++ b/httemplate/search/report_477.html @@ -231,7 +231,9 @@ 'table' => 'part_pkg_report_option', 'name_col' => 'name', 'hashref' => { 'disabled' => '' }, - 'element_name' => 'partv_report_option', + 'element_name' => 'part5_report_option', + 'curr_value' => + FS::Report::FCC_477::restore_fcc477map("part5_report_option"), ) %> </TD> diff --git a/httemplate/search/report_agent_commission.html b/httemplate/search/report_agent_commission.html new file mode 100644 index 000000000..79f94c52e --- /dev/null +++ b/httemplate/search/report_agent_commission.html @@ -0,0 +1,22 @@ +<% include('/elements/header.html', 'Agent commission report' ) %> + +<FORM ACTION="agent_commission.html"> + +<TABLE BGCOLOR="#cccccc" CELLSPACING=0> + +<% include( '/elements/tr-select-agent.html', disable_empty => 1 ) %> + +<% include( '/elements/tr-input-beginning_ending.html', ) %> + +</TABLE> + +<BR> +<INPUT TYPE="submit" VALUE="Get Report"> + +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +</%init> diff --git a/httemplate/search/report_cust_bill.html b/httemplate/search/report_cust_bill.html index 51618fb24..b339c80e0 100644 --- a/httemplate/search/report_cust_bill.html +++ b/httemplate/search/report_cust_bill.html @@ -4,7 +4,7 @@ <INPUT TYPE="hidden" NAME="magic" VALUE="_date"> <INPUT TYPE="hidden" NAME="custnum" VALUE="<% $custnum %>"> -<TABLE BGCOLOR="#cccccc" CELLSPACING=0 +<TABLE BGCOLOR="#cccccc" CELLSPACING=0> % unless ( $custnum ) { <& /elements/tr-select-agent.html, diff --git a/httemplate/search/report_cust_bill_pkg.html b/httemplate/search/report_cust_bill_pkg.html new file mode 100644 index 000000000..f121ef49f --- /dev/null +++ b/httemplate/search/report_cust_bill_pkg.html @@ -0,0 +1,117 @@ +<& /elements/header.html, mt('Line item report') &> + +<FORM ACTION="cust_bill_pkg.cgi" METHOD="GET"> +<!--<INPUT TYPE="hidden" NAME="magic" VALUE="_date">--> + +<TABLE BGCOLOR="#cccccc" CELLSPACING=0 + +<& /elements/tr-select-agent.html, + curr_value => scalar( $cgi->param('agentnum') ), + #label => emt('Line items for agent: '), + disable_empty => 0, +&> + +<& /elements/tr-select-cust_main-status.html, + label => emt('Customer status'), +&> + +<& /elements/tr-select-cust_class.html, + 'label' => emt('Customer class'), + 'field' => 'cust_classnum', + 'multiple' => 1, + 'pre_options' => [ '' => emt('(none)') ], + 'all_selected' => 1, +&> + +<& /elements/tr-input-beginning_ending.html &> + +<!-- needs support in cust_bill_pkg.cgi +<& /elements/tr-input-lessthan_greaterthan.html, + label => emt('Amount'), + field => 'amount', +&> +--> + +<!-- customer payment method i guess + <& /elements/tr-select-payby.html, + label => emt('Payment method:'), + payby_type => 'cust', + multiple => 1, + all_selected => 1, + &> +--> + +<TR> + <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="nottax" VALUE="Y" onClick="nottax_changed(this)" onChange="nottax_change(thid)"></TD> + <TD><% mt('Omit taxes') |h %></TD> +</TD> + +<TR> + <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="istax" VALUE="Y" onClick="istax_changed(this)" onChange="istax_change(thid)"></TD> + <TD><% mt('Taxes only') |h %></TD> +</TD> + +<!-- +<TR> + <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="credit" VALUE="Y"></TD> + <TD><% mt("Credit (what's this do?)") |h %></TD> +</TD> +--> + +</TABLE> + +<SCRIPT TYPE="text/javascript"> + function nottax_changed (what) { + if (what.checked && what.form.istax.checked) { + what.form.istax.checked = false; + } + } + function istax_changed (what) { + if (what.checked && what.form.nottax.checked) { + what.form.nottax.checked = false; + } + } +</SCRIPT> + +<BR> +<INPUT TYPE="submit" VALUE="<% mt('Get Report') |h %>"> + +</FORM> + +<& /elements/footer.html &> +<%init> + +#Financial reports? +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List invoices'); + +my $conf = new FS::Conf; + +#other available params (cust_bill_pkg.cgi): +# +#distribute = 1 +# +#(when nottax) +# use_override something about part_pkg +# classnum package class +# taxclass / taxclassNULL +# exempt_cust +# exempt_pkg +# region (country:state:county:city:district) +# taxable +# out (of taxable region) +# usage +#(when istax) +# locationtaxid (& district/city/ciounty/state) +# out (of taxable region) +# taxclassNULL +# report_group (itemdesc) +# itemdesc +# +#taxname/taxnameNULL cust_main_county +#taxnum cust_main_county +#credit (hmm need to look more at what this does) + + +</%init> + diff --git a/httemplate/search/report_cust_bill_pkg_referral.html b/httemplate/search/report_cust_bill_pkg_referral.html index b4716d4fc..47478aa42 100644 --- a/httemplate/search/report_cust_bill_pkg_referral.html +++ b/httemplate/search/report_cust_bill_pkg_referral.html @@ -23,6 +23,11 @@ 'disable_empty' => 1, &> +<& /elements/tr-select-cust_class.html, + 'field' => 'cust_classnum', + 'multiple' => 1, +&> + <& /elements/tr-select-pkg_class.html, 'pre_options' => [ '' => 'all', '0' => '(empty class)' ], 'disable_empty' => 1, diff --git a/httemplate/search/report_cust_credit_bill_pkg.html b/httemplate/search/report_cust_credit_bill_pkg.html new file mode 100644 index 000000000..2b9e1e69d --- /dev/null +++ b/httemplate/search/report_cust_credit_bill_pkg.html @@ -0,0 +1,104 @@ +<& /elements/header.html, mt('Credit application report') &> + +<FORM ACTION="cust_credit_bill_pkg.html" METHOD="GET"> +<!--<INPUT TYPE="hidden" NAME="magic" VALUE="_date">--> + +<TABLE BGCOLOR="#cccccc" CELLSPACING=0 + +<& /elements/tr-select-user.html, + 'label' => emt('Employee: '), + 'access_user' => \%access_user, +&> + +<& /elements/tr-select-agent.html, + curr_value => scalar( $cgi->param('agentnum') ), + #label => emt('Line items for agent: '), + disable_empty => 0, +&> + +<!-- +<& /elements/tr-select-cust_main-status.html, + label => emt('Customer status'), +&> +--> + +<!-- customer +<& /elements/tr-select-cust_class.html, + 'label' => emt('Class'), + 'multiple' => 1, + 'pre_options' => [ '' => emt('(none)') ], + 'all_selected' => 1, +&> +--> + +<!-- some sort of label saying this is the credit date... --> +<& /elements/tr-input-beginning_ending.html, + 'prefix' => 'credit', +&> + +<& /elements/tr-input-lessthan_greaterthan.html, + label => emt('Amount'), + field => 'amount', +&> + +<!-- customer payment method i guess + <& /elements/tr-select-payby.html, + label => emt('Payment method:'), + payby_type => 'cust', + multiple => 1, + all_selected => 1, + &> +--> + +<!-- +<TR> + <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="nottax" VALUE="Y" onClick="nottax_changed(this)" onChange="nottax_change(thid)"></TD> + <TD><% mt('Omit taxes') |h %></TD> +</TD> + +<TR> + <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="istax" VALUE="Y" onClick="istax_changed(this)" onChange="istax_change(thid)"></TD> + <TD><% mt('Taxes only') |h %></TD> +</TD> + +<SCRIPT TYPE="text/javascript"> + function nottax_changed (what) { + if (what.checked && what.form.istax.checked) { + what.form.istax.checked = false; + } + } + function istax_changed (what) { + if (what.checked && what.form.nottax.checked) { + what.form.nottax.checked = false; + } + } +</SCRIPT> +--> + +</TABLE> + +<BR> +<INPUT TYPE="submit" VALUE="<% mt('Get Report') |h %>"> + +</FORM> + +<& /elements/footer.html &> +<%init> + +#Financial reports? +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +#false laziness w/report_cust_credit.html +my $sth = dbh->prepare("SELECT DISTINCT usernum FROM cust_credit") + or die dbh->errstr; +$sth->execute or die $sth->errstr; +my @usernum = map $_->[0], @{$sth->fetchall_arrayref}; +my %access_user = + map { $_ => qsearchs('access_user',{'usernum'=>$_})->username } + @usernum; + +my $conf = new FS::Conf; + +</%init> + diff --git a/httemplate/search/report_cust_main.html b/httemplate/search/report_cust_main.html index 3e7181d4f..bac4346cf 100755 --- a/httemplate/search/report_cust_main.html +++ b/httemplate/search/report_cust_main.html @@ -96,11 +96,21 @@ </TR> % } - <& /elements/tr-select-cust_tag.html, - 'cgi' => $cgi, - 'is_report' => 1, - 'multiple' => 1, - &> + <TR> + <TD ALIGN="right">Tags</TD> + <TD> + <& /elements/select-cust_tag.html, + 'cgi' => $cgi, + 'is_report' => 1, + 'multiple' => 1, + &> + <DIV STYLE="display:inline-block; vertical-align:baseline"> + <INPUT TYPE="radio" NAME="all_tags" VALUE="0" CHECKED> Any of these + <BR> + <INPUT TYPE="radio" NAME="all_tags" VALUE="1"> All of these + </DIV> + </TD> + </TR> <& /elements/tr-select-payby.html, 'payby_type' => 'cust', @@ -169,6 +179,34 @@ % } <TR> + <TD ALIGN="right" VALIGN="center"><% mt('With email address(es)') |h %></TD> + <TD><INPUT TYPE="checkbox" NAME="with_email"></TD> + </TR> + + <TR> + <TD ALIGN="right" VALIGN="center"><% mt('With postal mail invoices') |h %></TD> + <TD><INPUT TYPE="checkbox" NAME="POST" ID="POST" onClick="POST_changed();"></TD> + </TR> + + <TR> + <TD ALIGN="right" VALIGN="center"><% mt('Without postal mail invoices') |h %></TD> + <TD><INPUT TYPE="checkbox" NAME="no_POST" ID="no_POST" onClick="no_POST_changed();"></TD> + </TR> + + <SCRIPT TYPE="text/javascript"> + function POST_changed() { + if ( document.getElementById('POST').checked == true ) { + document.getElementById('no_POST').checked = false; + } + } + function no_POST_changed() { + if ( document.getElementById('no_POST').checked == true ) { + document.getElementById('POST').checked = false; + } + } + </SCRIPT> + + <TR> <TH CLASS="background" COLSPAN=2> </TH> </TR> diff --git a/httemplate/search/report_customer_accounting_summary.html b/httemplate/search/report_customer_accounting_summary.html index f2a13a27b..8206f34ca 100755 --- a/httemplate/search/report_customer_accounting_summary.html +++ b/httemplate/search/report_customer_accounting_summary.html @@ -24,6 +24,19 @@ <% include( '/elements/tr-select-cust_main-status.html', 'label' => 'Customer Status' ) %> + + <& /elements/tr-select-cust_class.html, + 'label' => 'Customer Class', + 'field' => 'cust_classnum', + 'multiple' => 1, + &> + + <& /elements/tr-checkbox.html, + 'label' => 'Separate setup fees', + 'field' => 'setuprecur', + 'value' => 1, + &> + </TABLE> diff --git a/httemplate/search/report_e911.html b/httemplate/search/report_e911.html new file mode 100644 index 000000000..fd9686028 --- /dev/null +++ b/httemplate/search/report_e911.html @@ -0,0 +1,41 @@ +<& /elements/header.html, 'E911 Fee Report' &> + +<FORM ACTION="e911.html" METHOD="GET"> + +<TABLE BGCOLOR="#cccccc" CELLSPACING=0> + + <& /elements/tr-select-agent.html, + curr_value => scalar( $cgi->param('agentnum') ), + disable_empty => 0, + &> + + <& /elements/tr-input-beginning_ending.html &> + + <& /elements/tr-select-part_pkg.html, + field => 'e911pkgpart', + label => 'E911 package', + curr_value => $e911pkgpart, + disable_empty => 1, + &> + +</TABLE> + +<BR> +<INPUT TYPE="submit" VALUE="<% mt('Get Report') |h %>"> + +</FORM> + +<& /elements/footer.html &> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my $e911pkgpart; +# make an educated guess +my $e911_pkg = qsearchs('part_pkg', + { 'pkg' => { op=>'LIKE', value=>'%E911%' }, + 'disabled' => '', } ); +$e911pkgpart = $e911_pkg->pkgpart if $e911_pkg; + +</%init> diff --git a/httemplate/search/report_employee_audit.html b/httemplate/search/report_employee_audit.html index 757b8232f..461849b76 100644 --- a/httemplate/search/report_employee_audit.html +++ b/httemplate/search/report_employee_audit.html @@ -23,7 +23,7 @@ <%init> die "access denied" - unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + unless $FS::CurrentUser::CurrentUser->access_right('Employees: Audit Report'); my %tables = ( cust_pay => 'Payments', diff --git a/httemplate/search/report_employee_commission.html b/httemplate/search/report_employee_commission.html index 51afad3b5..ebfcae82d 100644 --- a/httemplate/search/report_employee_commission.html +++ b/httemplate/search/report_employee_commission.html @@ -25,6 +25,6 @@ <%init> die "access denied" - unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + unless $FS::CurrentUser::CurrentUser->access_right('Employees: Commission Report'); </%init> diff --git a/httemplate/search/report_prepaid_income.html b/httemplate/search/report_prepaid_income.html index 4743e2d21..dfb2ea249 100644 --- a/httemplate/search/report_prepaid_income.html +++ b/httemplate/search/report_prepaid_income.html @@ -33,6 +33,8 @@ <& /elements/tr-select-cust_main-status.html, label => mt('Customer Status') &> + <& /elements/tr-select-cust_class.html, + label => mt('Customer Class'), field => 'cust_classnum', multiple => 1 &> <& /elements/tr-select.html, label => 'Invoice Status', field => 'mode', diff --git a/httemplate/search/report_receivables.html b/httemplate/search/report_receivables.html index 5cff0f4fc..854b24a00 100755 --- a/httemplate/search/report_receivables.html +++ b/httemplate/search/report_receivables.html @@ -15,7 +15,15 @@ <& /elements/tr-select-cust_main-status.html, 'label' => emt('Customer Status'), &> - + + <& /elements/tr-select-cust_class.html, + 'label' => emt('Customer class'), + 'field' => 'cust_classnum', + 'multiple' => 1, + 'pre_options' => [ '' => emt('(none)') ], + 'all_selected' => 1, + &> + <TR> <TD ALIGN="right"><% mt('Customers') |h %></TD> <TD> diff --git a/httemplate/search/report_sqlradius_usage.html b/httemplate/search/report_sqlradius_usage.html index 01215e834..7e54465d3 100644 --- a/httemplate/search/report_sqlradius_usage.html +++ b/httemplate/search/report_sqlradius_usage.html @@ -8,13 +8,18 @@ 'empty_label' => 'all', &> -% my @exporttypes = map { "'$_'" } qw(sqlradius broadband_sqlradius); +%#more future-proof to actually ask all exports if they ->can('usage_sessions') +% my @exporttypes = qw( sqlradius sqlradius_withdomain broadband_sqlradius +% phone_sqlradius radiator +% ); <& /elements/tr-select-table.html, 'label' => 'Export', 'table' => 'part_export', 'name_col' => 'label', 'hashref' => {}, - 'extra_sql' => ' WHERE exporttype IN('.join(',', @exporttypes).')', + 'extra_sql' => ' WHERE exporttype IN ( '. + join(',', map "'$_'", @exporttypes). + ')', 'disable_empty' => 1, 'order_by' => 'ORDER BY exportnum', &> diff --git a/httemplate/search/report_svc_acct.html b/httemplate/search/report_svc_acct.html index 74bf5538e..e47f72726 100755 --- a/httemplate/search/report_svc_acct.html +++ b/httemplate/search/report_svc_acct.html @@ -116,7 +116,7 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Services: Accounts: Advanced search'); #? -my $title = emt('Account Report'); +my $title = mt('Account Report'); #false laziness w/report_cust_pkg.html my $custnum = ''; @@ -127,7 +127,7 @@ if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { 'hashref' => { 'custnum' => $custnum }, 'extra_sql' => ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql, }) or die "unknown custnum $custnum"; - $title = emt("Account Report: [_1]", $cust_main->name); + $title = mt("Account Report: [_1]", $cust_main->name); } </%init> diff --git a/httemplate/search/report_svc_phone.html b/httemplate/search/report_svc_phone.html index 9f1042608..63ca03e16 100644 --- a/httemplate/search/report_svc_phone.html +++ b/httemplate/search/report_svc_phone.html @@ -1,32 +1,6 @@ -<% include('/elements/header.html', 'Phone number total usage' ) %> +<& elements/report_svc_Common.html, + 'table' => 'svc_phone', + 'title' => 'Phone number report', -<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 CLASS="background" 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> + 'action' => 'svc_phone.cgi', +&> diff --git a/httemplate/search/report_svc_phone_usage.html b/httemplate/search/report_svc_phone_usage.html new file mode 100644 index 000000000..9f1042608 --- /dev/null +++ b/httemplate/search/report_svc_phone_usage.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 CLASS="background" 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-xls.cgi b/httemplate/search/report_tax-xls.cgi index f19f85aaa..bb843a73f 100755 --- a/httemplate/search/report_tax-xls.cgi +++ b/httemplate/search/report_tax-xls.cgi @@ -1,4 +1,3 @@ -<% $data %> <%init> my $htmldoc = include('report_tax.cgi'); @@ -155,4 +154,6 @@ for my $x (0..scalar(@widths)-1) { $workbook->close; +http_header('Content-Length' => length($data)); +$m->print($data); </%init> diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index 42a52d154..479b99044 100755 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -250,8 +250,10 @@ my $conf = new FS::Conf; my $out = 'Out of taxable region(s)'; my %label_opt = ( out => 1 ); #enable 'Out of Taxable Region' label -$label_opt{no_city} = 1 unless $cgi->param('show_cities'); -$label_opt{no_taxclass} = 1 unless $cgi->param('show_taxclasses'); +$label_opt{with_city} = 1 if $cgi->param('show_cities'); +$label_opt{with_district} = 1 if $cgi->param('show_districts'); + +$label_opt{with_taxclass} = 1 if $cgi->param('show_taxclasses'); my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); @@ -487,7 +489,8 @@ my $tot_tax = 0; my $tot_credit = 0; my @loc_params = qw(country state county); -push @loc_params, qw(city district) if $cgi->param('show_cities'); +push @loc_params, 'city' if $cgi->param('show_cities'); +push @loc_params, 'district' if $cgi->param('show_districts'); foreach my $r ( qsearch({ 'table' => 'cust_main_county', })) { my $taxnum = $r->taxnum; @@ -522,7 +525,7 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', })) { } if ( $cgi->param('show_taxclasses') ) { - my $base_label = $r->label(%label_opt, 'no_taxclass' => 1); + my $base_label = $r->label(%label_opt, 'with_taxclass' => 0); $base_regions{$base_label} ||= { label => $base_label, diff --git a/httemplate/search/report_tax.html b/httemplate/search/report_tax.html index 2ab0e0b2e..8a207aafb 100755 --- a/httemplate/search/report_tax.html +++ b/httemplate/search/report_tax.html @@ -34,9 +34,21 @@ % if ( $city ) { <TR> - <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="show_cities" VALUE="1"></TD> + <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="show_cities" VALUE="1" onclick="toggle_show_cities(this)"></TD> <TD>Show cities</TD> </TR> + <TR> + <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="show_districts" VALUE="1" DISABLED></TD> + <TD>Show districts</TD> + </TR> + <SCRIPT TYPE="text/javascript"> + function toggle_show_cities() { + what = document.getElementsByName('show_cities')[0]; + what.form.show_districts.disabled = !what.checked; + what.form.show_districts.checked = what.checked; + } + toggle_show_cities(); + </SCRIPT> % } % if ( $conf->exists('enable_taxclasses') ) { diff --git a/httemplate/search/rt_ticket.html b/httemplate/search/rt_ticket.html index 1ed5a3883..f5ac023b5 100644 --- a/httemplate/search/rt_ticket.html +++ b/httemplate/search/rt_ticket.html @@ -1,21 +1,21 @@ -<% include('elements/search.html', +<& elements/search.html, 'title' => 'Time worked summary', 'name_singular' => 'ticket', 'query' => $query, 'count_query' => $count_query, 'count_addl' => [ $format_seconds_sub, - $applied_time ? $format_seconds_sub : () ], + $applied ? $format_seconds_sub : () ], 'header' => [ 'Ticket #', 'Ticket', 'Time', - $applied_time ? 'Applied' : (), + $applied ? 'Applied' : (), ], 'fields' => [ 'ticketid', sub { encode_entities(shift->get('subject')) }, sub { my $seconds = shift->get('ticket_time'); &{ $format_seconds_sub }( $seconds ); }, - ($applied_time ? + ($applied ? sub { my $seconds = shift->get('applied_time'); &{ $format_seconds_sub }( $seconds ); } : () ), @@ -23,7 +23,7 @@ 'sort_fields' => [ 'ticketid', 'subject', 'transaction_time', - $applied_time ? 'applied_time' : (), + $applied ? 'applied_time' : (), ], 'links' => [ $link, @@ -31,8 +31,7 @@ '', '', ], - ) -%> +&> <%once> my $format_seconds_sub = sub { @@ -60,7 +59,6 @@ my @select = ( ); my @select_total = ( 'COUNT(*)' ); -my ($transaction_time, $applied_time); my $join = 'JOIN Users ON Transactions.Creator = Users.Id '; #. my $twhere = " @@ -68,6 +66,8 @@ my $twhere = " AND Transactions.ObjectId = Tickets.Id "; +my $transaction_time; +my $applied = ''; my $cfname = ''; if ( $cgi->param('cfname') =~ /^\w(\w|\s)*$/ ) { @@ -104,15 +104,14 @@ if ( $cgi->param('cfname') =~ /^\w(\w|\s)*$/ ) { $twhere .= " AND CustomFields.Name = '$cfname' AND (ocfv_new.Id IS NOT NULL OR ocfv_old.Id IS NOT NULL OR ocfv_main.Id IS NOT NULL)"; -} -else { +} else { + $transaction_time = " CASE transactions.type when 'Set' THEN (to_number(newvalue,'999999')-to_number(oldvalue, '999999')) * 60 ELSE timetaken*60 END"; - my $applied = ''; if ( $cgi->param('svcnum') =~ /^\s*(\d+)\s*$/ ) { $twhere .= " AND EXISTS( SELECT 1 FROM acct_rt_transaction WHERE acct_rt_transaction.transaction_id = Transactions.id AND svcnum = $1 )"; $applied = "AND svcnum = $1"; @@ -122,13 +121,11 @@ else { AND ( ( Transactions.Type = 'Set' AND Transactions.Field = 'TimeWorked' AND Transactions.NewValue != Transactions.OldValue ) - OR ( ( Transactions.Type='Create' OR Transactions.Type='Comment' OR Transactions.Type='Correspond' OR Transactions.Type='Touch' ) + OR ( Transactions.Type IN ( 'Create', 'Comment', 'Correspond', 'Touch' ) AND Transactions.TimeTaken > 0 ) )"; - $applied_time = "( SELECT SUM(support) FROM acct_rt_transaction LEFT JOIN Transactions ON ( transaction_id = Id ) $twhere $applied )"; - } @@ -155,9 +152,13 @@ my $ticket_time = "( SELECT SUM($transaction_time) $transactions )"; push @select, "$ticket_time AS ticket_time"; push @select_total, "SUM($ticket_time)"; -if ( $applied_time) { +if ( $applied ) { + + my $applied_time = "( SELECT SUM(support) FROM acct_rt_transaction LEFT JOIN Transactions ON ( transaction_id = Id ) $twhere $applied )"; + push @select, "$applied_time AS applied_time"; push @select_total, "SUM($applied_time)"; + } my $query = { diff --git a/httemplate/search/rt_transaction.html b/httemplate/search/rt_transaction.html index 1ae607be1..eb250fb27 100644 --- a/httemplate/search/rt_transaction.html +++ b/httemplate/search/rt_transaction.html @@ -1,4 +1,4 @@ -<% include('elements/search.html', +<& elements/search.html, 'title' => 'Time worked', 'name_singular' => 'transaction', 'query' => $query, @@ -29,8 +29,7 @@ '', '', ], - ) -%> +&> <%once> my $format_seconds_sub = sub { diff --git a/httemplate/search/sql.html b/httemplate/search/sql.html index bf5446975..71aa00671 100644 --- a/httemplate/search/sql.html +++ b/httemplate/search/sql.html @@ -1,9 +1,9 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => 'Query Results', 'name' => 'rows', 'query' => "SELECT $sql", - ) -%> + +&> <%init> die "access denied" diff --git a/httemplate/search/sqlradius.cgi b/httemplate/search/sqlradius.cgi index 5363944e4..22984731a 100644 --- a/httemplate/search/sqlradius.cgi +++ b/httemplate/search/sqlradius.cgi @@ -51,7 +51,7 @@ % @{ $part_export->usage_sessions( { % 'stoptime_start' => $beginning, % 'stoptime_end' => $ending, -% 'open_sessions' => $open_sessions, +% 'session_status' => $status, % 'starttime_start' => $starttime_beginning, % 'starttime_end' => $starttime_ending, % 'svc_acct' => $cgi_svc_acct, @@ -117,9 +117,9 @@ if ( $cgi->param('end') && $cgi->param('end') =~ /^(\d+)$/ ) { $ending = $1; } -my $open_sessions = ''; -if ( $cgi->param('open_sessions') =~ /^(\d*)$/ ) { - $open_sessions = $1; +my $status = ''; +if ( $cgi->param('session_status') =~ /^(closed|open)$/ ) { + $status = $1; } my( $starttime_beginning, $starttime_ending ) = ( '', '' ); @@ -242,8 +242,15 @@ my $time_format = sub { $pretty; }; +my $time_format_or_open = sub { + my $time = shift; + return '<CENTER>OPEN</CENTER>' if $time == 0; + &{$time_format}($time); +}; + my $duration_format = sub { my $seconds = shift; + return '' if $seconds eq ''; # open session my $hour = int($seconds/3600); my $min = int( ($seconds%3600) / 60 ); my $sec = $seconds%60; @@ -339,7 +346,7 @@ tie %fields, 'Tie::IxHash', 'acctstoptime' => { name => 'End time', attrib => 'Acct-Stop-Time', - fmt => $time_format, + fmt => $time_format_or_open, align => 'left', }, 'acctsessiontime' => { diff --git a/httemplate/search/sqlradius.html b/httemplate/search/sqlradius.html index 7b9fce310..547a9bb44 100644 --- a/httemplate/search/sqlradius.html +++ b/httemplate/search/sqlradius.html @@ -52,8 +52,9 @@ <TR> <TD>Show:</TD> <TD> - <INPUT TYPE="radio" NAME="open_sessions" VALUE="0" onClick="open_changed(this);" CHECKED>Completed sessions<BR> - <INPUT TYPE="radio" NAME="open_sessions" VALUE="1" onClick="open_changed(this);">Open sessions + <INPUT TYPE="radio" NAME="session_status" VALUE="" onClick="enable_stop(true);" CHECKED>All sessions<BR> + <INPUT TYPE="radio" NAME="session_status" VALUE="closed" onClick="enable_stop(true);">Completed sessions<BR> + <INPUT TYPE="radio" NAME="session_status" VALUE="open" onClick="enable_stop(false);">Open sessions </TD> </TR> @@ -69,41 +70,31 @@ <SCRIPT TYPE="text/javascript"> - function open_changed(what) { - - var value=get_open_value(what); - if ( value == '1' ) { - what.form.stoptime_beginning_text.disabled = true; - what.form.stoptime_ending_text.disabled = true; - what.form.stoptime_beginning_text.style.backgroundColor = '#dddddd'; - what.form.stoptime_ending_text.style.backgroundColor = '#dddddd'; - what.form.stoptime_beginning_button.style.display = 'none'; - what.form.stoptime_ending_button.style.display = 'none'; - what.form.stoptime_beginning_disabled.style.display = ''; - what.form.stoptime_ending_disabled.style.display = ''; - } else if ( value == '0' ) { - what.form.stoptime_beginning_text.disabled = false; - what.form.stoptime_ending_text.disabled = false; - what.form.stoptime_beginning_text.style.backgroundColor = '#ffffff'; - what.form.stoptime_ending_text.style.backgroundColor = '#ffffff'; - what.form.stoptime_beginning_button.style.display = ''; - what.form.stoptime_ending_button.style.display = ''; - what.form.stoptime_beginning_disabled.style.display = 'none'; - what.form.stoptime_ending_disabled.style.display = 'none'; + function enable_stop(value) { + + var f = document.OneTrueForm; + if ( value ) { + f.stoptime_beginning_text.disabled = false; + f.stoptime_ending_text.disabled = false; + f.stoptime_beginning_text.style.backgroundColor = '#ffffff'; + f.stoptime_ending_text.style.backgroundColor = '#ffffff'; + f.stoptime_beginning_button.style.display = ''; + f.stoptime_ending_button.style.display = ''; + f.stoptime_beginning_disabled.style.display = 'none'; + f.stoptime_ending_disabled.style.display = 'none'; + } else { + f.stoptime_beginning_text.disabled = true; + f.stoptime_ending_text.disabled = true; + f.stoptime_beginning_text.style.backgroundColor = '#dddddd'; + f.stoptime_ending_text.style.backgroundColor = '#dddddd'; + f.stoptime_beginning_button.style.display = 'none'; + f.stoptime_ending_button.style.display = 'none'; + f.stoptime_beginning_disabled.style.display = ''; + f.stoptime_ending_disabled.style.display = ''; } } - function get_open_value(what) { - var rad_val = ''; - for (var i=0; i < what.form.open_sessions.length; i++) { - if (what.form.open_sessions[i].checked) { - var rad_val = what.form.open_sessions[i].value; - } - } - return rad_val; - } - </SCRIPT> <TR> diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi index 92e1c500c..b9e5a7cc9 100755 --- a/httemplate/search/svc_acct.cgi +++ b/httemplate/search/svc_acct.cgi @@ -1,4 +1,4 @@ -<& elements/search.html, +<& elements/svc_Common.html, 'title' => emt('Account Search Results'), 'name' => emt('accounts'), 'query' => $sql_query, diff --git a/httemplate/search/svc_broadband.cgi b/httemplate/search/svc_broadband.cgi index ee62e9084..8366d214b 100755 --- a/httemplate/search/svc_broadband.cgi +++ b/httemplate/search/svc_broadband.cgi @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/svc_Common.html, 'title' => 'Broadband Search Results', 'name' => 'broadband services', 'html_init' => $html_init, @@ -49,8 +49,8 @@ '', FS::UI::Web::cust_styles(), ], - ) -%> + +&> <%init> die "access denied" unless @@ -72,7 +72,7 @@ else { } if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { - $search_hash{'order_by'} = $1; + $search_hash{'order_by'} = "ORDER BY $1"; } my $sql_query = FS::svc_broadband->search(\%search_hash); diff --git a/httemplate/search/svc_dish.cgi b/httemplate/search/svc_dish.cgi index 94da03537..1f8cbc395 100755 --- a/httemplate/search/svc_dish.cgi +++ b/httemplate/search/svc_dish.cgi @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/svc_Common.html, 'title' => 'Dish Network Search Results', 'name' => 'services', 'query' => $sql_query, @@ -34,8 +34,8 @@ '', FS::UI::Web::cust_styles(), ], - ) -%> + +&> <%init> die "access denied" @@ -61,7 +61,7 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { 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 ) '; + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); #here is the agent virtualization push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( diff --git a/httemplate/search/svc_domain.cgi b/httemplate/search/svc_domain.cgi index 9827b8d38..56cfa30c8 100755 --- a/httemplate/search/svc_domain.cgi +++ b/httemplate/search/svc_domain.cgi @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => "Domain Search Results", 'name' => 'domains', 'query' => $sql_query, @@ -34,8 +34,8 @@ '', FS::UI::Web::cust_styles(), ], - ) -%> + +&> <%init> die "access denied" @@ -66,7 +66,7 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { 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 ) '; + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); #here is the agent virtualization push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( diff --git a/httemplate/search/svc_external.cgi b/httemplate/search/svc_external.cgi index cb51d44fd..b282939a7 100755 --- a/httemplate/search/svc_external.cgi +++ b/httemplate/search/svc_external.cgi @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/svc_Common.html, 'title' => 'External service search results', 'name' => 'external services', 'query' => $sql_query, @@ -40,9 +40,8 @@ '', FS::UI::Web::cust_styles(), ], - ) -%> - + +&> <%init> die "access denied" @@ -90,7 +89,7 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { 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 ) '; + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); #here is the agent virtualization push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( diff --git a/httemplate/search/svc_forward.cgi b/httemplate/search/svc_forward.cgi index f17f131ab..6a23bb3bb 100755 --- a/httemplate/search/svc_forward.cgi +++ b/httemplate/search/svc_forward.cgi @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => "Mail forward Search Results", 'name' => 'mail forwards', 'query' => $sql_query, @@ -39,8 +39,8 @@ '', FS::UI::Web::cust_styles(), ], - ) -%> + +&> <%init> die "access denied" @@ -67,7 +67,7 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { 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 ) '; + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); #here is the agent virtualization push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( diff --git a/httemplate/search/svc_hardware.cgi b/httemplate/search/svc_hardware.cgi index ec09be82b..93fc2c391 100644 --- a/httemplate/search/svc_hardware.cgi +++ b/httemplate/search/svc_hardware.cgi @@ -1,4 +1,4 @@ -<% include('elements/search.html', +<& elements/svc_Common.html, 'title' => 'Hardware service search results', 'name' => 'installations', 'query' => $sql_query, @@ -34,8 +34,7 @@ FS::UI::Web::cust_colors() ], 'style' => [ $svc_cancel_style, ('') x 7, FS::UI::Web::cust_styles() ], - ) -%> +&> <%init> die "access denied" @@ -44,8 +43,8 @@ die "access denied" 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 ) + LEFT JOIN cust_pkg USING ( pkgnum )'. + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg').' LEFT JOIN hardware_type USING ( typenum )'; my @extra_sql; diff --git a/httemplate/search/svc_phone.cgi b/httemplate/search/svc_phone.cgi index 29434083f..f3a056475 100644 --- a/httemplate/search/svc_phone.cgi +++ b/httemplate/search/svc_phone.cgi @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/svc_Common.html, 'title' => "Phone number search results", 'name' => 'phone numbers', 'query' => $sql_query, @@ -9,7 +9,7 @@ 'Country code', 'Phone number', @header, - FS::UI::Web::cust_header(), + FS::UI::Web::cust_header($cgi->param('cust_fields')), ], 'fields' => [ 'svcnum', 'svc', @@ -24,7 +24,7 @@ $link, ( map '', @header ), ( map { $_ ne 'Cust. Status' ? $link_cust : '' } - FS::UI::Web::cust_header() + FS::UI::Web::cust_header($cgi->param('cust_fields')) ), ], 'align' => 'rlrr'. @@ -46,8 +46,8 @@ ( map '', @header ), FS::UI::Web::cust_styles(), ], - ) -%> + +&> <%init> die "access denied" @@ -56,8 +56,6 @@ die "access denied" my $conf = new FS::Conf; my @select = (); -my %svc_phone = (); -my @extra_sql = (); my $orderby = 'ORDER BY svcnum'; my @header = (); @@ -65,9 +63,12 @@ my @fields = (); my $link = [ "${p}view/svc_phone.cgi?", 'svcnum' ]; my $redirect = $link; +my %search_hash = (); +my @extra_sql = (); + if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { - push @extra_sql, 'pkgnum IS NULL' + $search_hash{'unlinked'} = 1 if $cgi->param('magic') eq 'unlinked'; if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { @@ -119,52 +120,31 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { } +} elsif ( $cgi->param('magic') =~ /^advanced$/ ) { + + for (qw( agentnum custnum cust_status balance balance_days cust_fields )) { + $search_hash{$_} = $cgi->param($_) if length($cgi->param($_)); + } + + for (qw( payby pkgpart svcpart )) { + $search_hash{$_} = [ $cgi->param($_) ] if $cgi->param($_); + } + } elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { - push @extra_sql, "svcpart = $1"; + $search_hash{'svcpart'} = [ $1 ]; } else { $cgi->param('phonenum') =~ /^([\d\- ]+)$/; - ( $svc_phone{'phonenum'} = $1 ) =~ s/\D//g; + my $phonenum = $1; + $phonenum =~ s/\D//g; + push @extra_sql, "phonenum = '$phonenum'"; } -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_phone) ? ' AND ' : ' WHERE ' ). - join(' AND ', @extra_sql ); -} +$search_hash{'addl_select'} = \@select; +$search_hash{'order_by'} = $orderby; +$search_hash{'where'} = \@extra_sql; -my $count_query = "SELECT COUNT(*) FROM svc_phone $addl_from "; -if ( keys %svc_phone ) { - $count_query .= ' WHERE '. - join(' AND ', map "$_ = ". dbh->quote($svc_phone{$_}), - keys %svc_phone - ); -} -$count_query .= $extra_sql; - -my $sql_query = { - 'table' => 'svc_phone', - 'hashref' => \%svc_phone, - 'select' => join(', ', - 'svc_phone.*', - 'part_svc.svc', - @select, - 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'extra_sql' => $extra_sql, - 'order_by' => $orderby, - 'addl_from' => $addl_from, -}; +my $sql_query = FS::svc_phone->search(\%search_hash); +my $count_query = delete($sql_query->{'count_query'}); #smaller false laziness w/svc_*.cgi here my $link_cust = sub { diff --git a/httemplate/search/svc_www.cgi b/httemplate/search/svc_www.cgi index adc31c88a..7410262e8 100755 --- a/httemplate/search/svc_www.cgi +++ b/httemplate/search/svc_www.cgi @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/svc_Common.html, 'title' => 'Virtual Host Search Results', 'name' => 'virtual hosts', 'query' => $sql_query, @@ -45,8 +45,8 @@ '', FS::UI::Web::cust_styles(), ], - ) -%> + +&> <%init> die "access denied" @@ -73,7 +73,7 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { 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 ) '; + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); #here is the agent virtualization push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( diff --git a/httemplate/search/timeworked.html b/httemplate/search/timeworked.html index bbfd0334d..fa4b89539 100644 --- a/httemplate/search/timeworked.html +++ b/httemplate/search/timeworked.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => 'Time Worked', 'name' => 'time', 'html_form' => qq!<FORM NAME="timeForm" ACTION="${p}misc/timeworked.html" METHOD="POST">!, @@ -33,9 +33,8 @@ '', ], 'html_foot' => $html_foot, - ) - -%> + +&> <%init> die "access denied" diff --git a/httemplate/search/unapplied_cust_pay.html b/httemplate/search/unapplied_cust_pay.html index e232291fe..f5c2bf0f9 100755 --- a/httemplate/search/unapplied_cust_pay.html +++ b/httemplate/search/unapplied_cust_pay.html @@ -1,9 +1,8 @@ -<% include( 'elements/cust_main_dayranges.html', +<& elements/cust_main_dayranges.html, #'title' => 'Prepaid Balance Aging Summary', #??? 'title' => 'Unapplied Payments Aging Summary', 'range_sub' => \&unapplied_payments, - ) -%> +&> <%init> die "access denied" diff --git a/httemplate/search/unearned_detail.html b/httemplate/search/unearned_detail.html index 02d514cbe..285fb50a7 100644 --- a/httemplate/search/unearned_detail.html +++ b/httemplate/search/unearned_detail.html @@ -114,8 +114,13 @@ if ( $cgi->param('status') =~ /^([a-z]+)$/ ) { push @where, "cust_bill._date >= $beginning", "cust_bill._date <= $ending"; -if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { - push @where, "cust_main.agentnum = $1"; +# cust_classnum (false laziness w/ elements/cust_main_dayranges.html, elements/cust_pay_or_refund.html, prepaid_income.html, cust_bill_pay.html, cust_bill_pkg.html, cust_bill_pkg_referral.html, cust_credit.html, cust_credit_refund.html, cust_main::Search::search_sql) +if ( grep { $_ eq 'cust_classnum' } $cgi->param ) { + my @classnums = grep /^\d*$/, $cgi->param('cust_classnum'); + push @where, 'COALESCE( cust_main.classnum, 0) IN ( '. + join(',', map { $_ || '0' } @classnums ). + ' )' + if @classnums; } # no pkgclass, no taxclass, no tax location... @@ -204,8 +209,8 @@ push @select, '(edate - 82799) AS before_edate'; #usage always excluded # always 'nottax', not 'istax' -$join_cust = ' JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) '; +$join_cust = ' JOIN cust_bill USING ( invnum ) '. + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); $join_pkg .= ' LEFT JOIN cust_pkg USING ( pkgnum ) LEFT JOIN part_pkg USING ( pkgpart ) @@ -216,7 +221,7 @@ my $where = ' WHERE '. join(' AND ', @where); my $count_query = "SELECT COUNT(DISTINCT billpkgnum), SUM( $unearned_base ), SUM( $unearned_sql ) - FROM cust_bill_pkg $join_cust $join_pkg $where"; + FROM cust_bill_pkg $join_pkg $join_cust $where"; push @select, 'part_pkg.pkg', 'part_pkg.freq', @@ -225,7 +230,7 @@ push @select, 'part_pkg.pkg', my $query = { 'table' => 'cust_bill_pkg', - 'addl_from' => "$join_cust $join_pkg", + 'addl_from' => "$join_pkg $join_cust", 'hashref' => {}, 'select' => join(",\n", @select ), 'extra_sql' => $where, @@ -235,7 +240,8 @@ my $query = { my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ]; my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; -my $money_char; +my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; sub money_sub { $conf ||= new FS::Conf; diff --git a/httemplate/search/unprovisioned_services.html b/httemplate/search/unprovisioned_services.html index f85e4fb19..a7791ba86 100644 --- a/httemplate/search/unprovisioned_services.html +++ b/httemplate/search/unprovisioned_services.html @@ -1,4 +1,4 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => 'Unprovisioned Service Search Results', 'name' => 'packages with unprovisioned services', 'query' => { @@ -54,8 +54,8 @@ '', FS::UI::Web::cust_styles(), ], - ) -%> + +&> <%init> die "access denied" @@ -74,7 +74,8 @@ my $search = " where cust_pkg.cancel is null and pkg_svc.quantity > 0 and " . " cust_svc.pkgnum = cust_pkg.pkgnum and " . " cust_svc.svcpart = pkg_svc.svcpart) $svcpart_limit"; -my $addl_from = " join pkg_svc using (pkgpart) join cust_main using (custnum) "; +my $addl_from = " join pkg_svc using (pkgpart) ". + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); # this was very painful to derive but it appears correct #select cust_pkg.custnum,cust_pkg.pkgpart,cust_pkg.pkgnum, pkg_svc.svcpart from cust_pkg join |