diff options
Diffstat (limited to 'httemplate/search')
40 files changed, 1209 insertions, 484 deletions
diff --git a/httemplate/search/477.html b/httemplate/search/477.html index 63eab7a..d586406 100755 --- a/httemplate/search/477.html +++ b/httemplate/search/477.html @@ -35,7 +35,7 @@ % next unless ( $part{'IIA'} || $part{'IIB'} ); % } % -% if ( $part eq 'VI' ) { +% if ( $part eq 'VI_census' ) { % next unless $part{'IA'}; % } % @@ -55,12 +55,12 @@ % } % } else { % if ( $type eq 'xml' ) { -<<% 'Part_'. uc($part) %>> +<<% 'Part_'. $part %>> % } % my $url = &{$url_mangler}($part); <% include( "477part${part}.html", 'url' => $url ) %> % if ( $type eq 'xml' ) { -</<% 'Part_'. uc($part) %>> +</<% 'Part_'. $part %>> % } % } % } @@ -88,6 +88,6 @@ my $url_mangler = sub { $url =~ s/477\./477part$part./; $url; }; -my @parts = qw( IA IIA IIB IV V VI ); +my @parts = qw( IA IIA IIB IV V VI_census ); </%init> diff --git a/httemplate/search/477partIA_detail.html b/httemplate/search/477partIA_detail.html index 546d56c..6fea391 100755 --- a/httemplate/search/477partIA_detail.html +++ b/httemplate/search/477partIA_detail.html @@ -9,6 +9,7 @@ 'disable_total' => 1, 'header' => [ '', @column_option_name ], 'xml_elements' => [ @xml_elements ], + 'xml_omit_empty' => 1, 'fields' => [ @fields ], ) %> @@ -60,9 +61,19 @@ 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]; @@ -71,7 +82,7 @@ my $value = sub { return $row_option_name{$row} || 'no such report option'; } elsif ( $column =~ /^(\d+)$/ ) { my @report_option = ( $row || '', - $column_option[$1 - 2] || '', + $column_option[$column] || '', $technology_option[$tech_code] || '', ); @@ -81,45 +92,35 @@ my $value = sub { my $percentage = sprintf('%.2f', $count ? 100 * $residential / $count : 0); my $return = $count; - $return .= "<BR>$percentage% residential" - unless $cgi->param('_type') eq 'xml'; + + 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 = ( - sub { &{$value}(shift, 'name');}, - sub { &{$value}(shift, 2);}, - sub { &{$value}(shift, 3);}, - sub { &{$value}(shift, 4);}, - sub { &{$value}(shift, 5);}, - sub { &{$value}(shift, 6);}, - sub { &{$value}(shift, 7);}, - sub { &{$value}(shift, 8);}, - sub { &{$value}(shift, 9);}, - ); +my @fields = map { my $ci = $_; sub { &{$value}(shift, $ci); } } + ( 'name', (0 .. $#column_option) ); shift @fields if $cgi->param('_type') eq 'xml'; -my $xml_element = sub { - my ($rowref, $column) = (shift, shift); - my $row = $rowref->[0]; - - $row++; - $xml_prefix. $column. $row; - -}; - -my @xml_elements = ( - sub { &{$xml_element}(shift, 'f') }, - sub { &{$xml_element}(shift, 'g') }, - sub { &{$xml_element}(shift, 'h') }, - sub { &{$xml_element}(shift, 'i') }, - sub { &{$xml_element}(shift, 'j') }, - sub { &{$xml_element}(shift, 'k') }, - sub { &{$xml_element}(shift, 'l') }, - sub { &{$xml_element}(shift, 'm') }, +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 index 269f2ca..eb1c116 100755 --- a/httemplate/search/477partIA_summary.html +++ b/httemplate/search/477partIA_summary.html @@ -26,7 +26,7 @@ sub { '100.00' }, sub { '100.00' }, sub { $total_percentage }, - sub { $total_percentage }, + sub { $above_200_percentage }, ], ) %> @@ -54,11 +54,13 @@ 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 ) { @@ -70,11 +72,16 @@ foreach my $row ( @row_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/477partVI.html b/httemplate/search/477partVI_census.html index db572bc..1d625dc 100755 --- a/httemplate/search/477partVI.html +++ b/httemplate/search/477partVI_census.html @@ -1,5 +1,13 @@ <% include( 'elements/search.html', 'html_init' => $html_init, + 'html_foot' => sub { if (scalar(keys %state_hash) > 1) { + '<BR><B>'. + 'WARNING: multiple states found'. + '</B><BR>'; + } else { + ''; + } + }, 'name' => 'regions', 'query' => [ @sql_query ], 'count_query' => $count_query, @@ -28,12 +36,15 @@ 'percentage', ], 'fields' => [ - sub { my $row = shift; substr($row->censustract, 2, 3) }, + sub { my $row = shift; + $state_hash{substr($row->censustract, 0, 2)} = 1; + substr($row->censustract, 2, 3) + }, sub { my $row = shift; substr($row->censustract, 5) }, 'upload', 'download', 'technology_code', - sub { '' }, # doesn't really work + sub { $cgi->param('_type') eq 'xml' ? '0' : '' }, # doesn't really work 'quantity', sub { my $row = shift; sprintf "%.2f", $row->residential }, ], @@ -64,6 +75,7 @@ my $html_init = '<H2>Part VI</H2>'; my %search_hash = (); my @sql_query = (); +my %state_hash = (); for ( qw(agentnum magic classnum) ) { $search_hash{$_} = $cgi->param($_) if $cgi->param($_); diff --git a/httemplate/search/cdr.html b/httemplate/search/cdr.html index a557596..5544ff5 100644 --- a/httemplate/search/cdr.html +++ b/httemplate/search/cdr.html @@ -55,6 +55,8 @@ die "access denied" my $edit_data = $FS::CurrentUser::CurrentUser->access_right('Edit rating data'); +my $conf = new FS::Conf; + my $areboxes = 0; my $title = 'Call Detail Records'; @@ -145,17 +147,24 @@ foreach my $param ( grep /^termpart\d+status$/, $cgi->param ) { } ### -# src/dest/charged_party +# src/dest/charged_party/svcnum ### -if ( $cgi->param('src') =~ /^\s*([\d\-\+\ ]+)\s*$/ ) { - ( my $src = $1 ) =~ s/\D//g; +my $phonenum = qr/^\s*([\d\-\+\ ]+)\s*$/; +my $x = qr/\D/; +if ( $conf->exists('svc_phone-allow_alpha_phonenum') ) { + $phonenum = qr/^\s*([\d\-\+\ A-Za-z]+)\s*$/; + $x = qr/[^\dA-Za-z]/; +} + +if ( $cgi->param('src') =~ $phonenum ) { + ( my $src = $1 ) =~ s/$x//g; $hashref->{'src'} = $src; push @search, "src = '$src'"; } -if ( $cgi->param('dst') =~ /^\s*([\d\-\+ ]+)\s*$/ ) { - ( my $dst = $1 ) =~ s/\D//g; +if ( $cgi->param('dst') =~ $phonenum ) { + ( my $dst = $1 ) =~ s/$x//g; $hashref->{'dst'} = $dst; push @search, "dst = '$dst'"; } @@ -166,15 +175,32 @@ if ( $cgi->param('dcontext') =~ /^\s*(.+)\s*$/ ) { push @search, "dcontext = '$dcontext'"; } -if ( $cgi->param('charged_party') =~ /^\s*([\d\-\+\ ]+)\s*$/ ) { - ( my $charged_party = $1 ) =~ s/\D//g; - #$hashref->{'charged_party'} = $charged_party; - #push @search, "charged_party = '$charged_party'"; - #XXX countrycode +if ( $cgi->param('charged_party') ) { + + my @cp = map { $_, "1$_" } + split(/\s*,\s*/, $cgi->param('charged_party') ); + + my $search = 'charged_party IN ('. join(',', map dbh->quote($_), @cp). ')'; + + push @search, $search; + push @qsearch, $search; +} + +if ( $cgi->param('charged_party_or_src') ) { + + my @cp = map { $_, "1$_" } + split(/\s*,\s*/, $cgi->param('charged_party_or_src') ); + my $in = join(',', map dbh->quote($_), @cp); + + my $search = "( charged_party IN ($in) OR src IN ($in) )"; - my $search = " ( charged_party = '$charged_party' - OR charged_party = '1$charged_party' ) "; + push @search, $search; + push @qsearch, $search; +} +if ( $cgi->param('svcnum') =~ /^([\d, ]+)$/ ) { + my $svcnum = $1; + my $search = "svcnum IN ($svcnum)"; push @search, $search; push @qsearch, $search; } diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html index 1e9ee8d..cf6ce49 100755 --- a/httemplate/search/cust_bill.html +++ b/httemplate/search/cust_bill.html @@ -122,8 +122,16 @@ if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { $search{'newest_percust'} = 1; $count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'"; } - - my $extra_sql = ' WHERE '. FS::cust_bill->search_sql_where( \%search ); + + my $payby_sql = ''; + $payby_sql = ' AND (' . + join(' OR ', map { "cust_main.payby = '$_'" } $cgi->param('payby') ) . + ')' + if $cgi->param('payby'); + + my $extra_sql = ' WHERE '. + FS::cust_bill->search_sql_where( \%search ). + $payby_sql; unless ( $count_query ) { $count_query = 'SELECT COUNT(*), '. join(', ', diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 98a1da9..f2a5ccd 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -502,26 +502,29 @@ if ( $cgi->param('nottax') ) { } elsif ( $cgi->param('istax') ) { #false laziness w/report_tax.cgi $taxfromwhere - if ( $conf->exists('tax-pkg_address') ) { + if ( scalar( grep( /locationtaxid/, $cgi->param ) ) || + $cgi->param('iscredit') eq 'rate') { + + $join_pkg .= + ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '. + ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) '; + + } elsif ( $conf->exists('tax-pkg_address') ) { + $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum ) LEFT JOIN cust_location USING ( locationnum ) '; #quelle kludge, somewhat false laziness w/report_tax.cgi s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g for @where; - } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) || - $cgi->param('iscredit') eq 'rate') { - $join_pkg .= - ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '. - ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) '; } if ( $cgi->param('iscredit') ) { $join_pkg .= ' JOIN cust_credit_bill_pkg USING ( billpkgnum'; - if ( $conf->exists('tax-pkg_address') ) { + if ( $cgi->param('iscredit') eq 'rate' ) { + $join_pkg .= ', billpkgtaxratelocationnum )'; + } elsif ( $conf->exists('tax-pkg_address') ) { $join_pkg .= ', billpkgtaxlocationnum )'; push @where, "billpkgtaxratelocationnum IS NULL"; - } elsif ( $cgi->param('iscredit') eq 'rate' ) { - $join_pkg .= ', billpkgtaxratelocationnum )'; } else { $join_pkg .= ' )'; push @where, "billpkgtaxratelocationnum IS NULL"; diff --git a/httemplate/search/cust_bill_pkg_discount.html b/httemplate/search/cust_bill_pkg_discount.html index 088b291..b472366 100644 --- a/httemplate/search/cust_bill_pkg_discount.html +++ b/httemplate/search/cust_bill_pkg_discount.html @@ -85,6 +85,11 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { push @where, "cust_main.agentnum = $1"; } +#usernum +if ( $cgi->param('usernum') =~ /^(\d+)$/ ) { + push @where, "cust_pkg_discount.usernum = $1"; +} + # #classnum # # not specified: all classes # # 0: empty class @@ -110,18 +115,25 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { my $count_query = "SELECT COUNT(*), SUM(amount)"; -my $join_cust = ' JOIN cust_bill_pkg USING ( billpkgnum ) - JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) '; +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 ) '; -my $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) '; - #LEFT JOIN part_pkg AS override - # ON pkgpart_override = override.pkgpart '; +my $join_pkg = + ' 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 '; my $where = ' WHERE '. join(' AND ', @where); -$count_query .= " FROM cust_bill_pkg_discount $join_cust $join_pkg $where"; +$count_query .= + " FROM cust_bill_pkg_discount $join_cust_pkg_discount $join_cust $join_pkg ". + $where; my @select = ( 'cust_bill_pkg_discount.*', @@ -135,7 +147,7 @@ push @select, 'cust_main.custnum', my $query = { 'table' => 'cust_bill_pkg_discount', - 'addl_from' => "$join_cust $join_pkg", + 'addl_from' => "$join_cust_pkg_discount $join_cust $join_pkg", 'hashref' => {}, 'select' => join(', ', @select ), 'extra_sql' => $where, diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html index 9a14dce..a3b22b1 100755 --- a/httemplate/search/cust_credit.html +++ b/httemplate/search/cust_credit.html @@ -3,47 +3,14 @@ 'name' => 'credits', 'query' => $sql_query, 'count_query' => $count_query, - 'count_addl' => [ '$%.2f total credited (gross)', ], + 'count_addl' => \@count_addl, #'redirect' => $link, - 'header' => [ 'Amount', - 'Date', - 'By', - 'Reason', - FS::UI::Web::cust_header(), - ], - 'fields' => [ - #'crednum', - sub { sprintf('$%.2f', shift->amount ) }, - sub { time2str('%b %d %Y', shift->_date ) }, - 'otaker', - 'reason', - \&FS::UI::Web::cust_fields, - ], - #'align' => 'rrrllll', - 'align' => 'rrll'.FS::UI::Web::cust_aligns(), - 'links' => [ - '', - '', - '', - '', - ( map { $_ ne 'Cust. Status' ? $clink : '' } - FS::UI::Web::cust_header() - ), - ], - 'color' => [ - '', - '', - '', - '', - FS::UI::Web::cust_colors(), - ], - 'style' => [ - '', - '', - '', - '', - FS::UI::Web::cust_styles(), - ], + 'header' => \@header, + 'fields' => \@fields, + 'align' => $align, + 'links' => \@links, + 'color' => \@color, + 'style' => \@style, ) %> <%init> @@ -51,13 +18,74 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); +my $money_char = FS::Conf->new->config('money_char') || '$'; + my $title = 'Credit Search Results'; #my( $count_query, $sql_query ); +my $unapplied = $cgi->param('unapplied'); +$title = "Unapplied $title" if $unapplied; +my $clink = sub { + my $cust_bill = shift; + $cust_bill->cust_main_custnum + ? [ "${p}view/cust_main.cgi?", 'custnum' ] + : ''; +}; + +my (@header, @fields, $align, @links, @color, @style); +$align = ''; + +#amount +push @header, 'Amount'; +push @fields, sub { $money_char .sprintf('%.2f', shift->amount) }; +$align .= 'r'; +push @links, ''; +push @color, ''; +push @style, ''; + +# unapplied amount +if ($unapplied) { + push @header, 'Unapplied'; + push @fields, sub { $money_char .sprintf('%.2f', shift->unapplied_amount) }; + $align .= 'r'; + push @links, ''; + push @color, ''; + push @style, ''; +} + +push @header, 'Date', + 'By', + 'Reason', + FS::UI::Web::cust_header(), + ; +push @fields, sub { time2str('%b %d %Y', shift->_date ) }, + 'otaker', + 'reason', + \&FS::UI::Web::cust_fields, + ; +$align .= 'rll'.FS::UI::Web::cust_aligns(), +push @links, '', + '', + '', + ( map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header() + ), + ; +push @color, '', + '', + '', + FS::UI::Web::cust_colors(), + ; +push @style, '', + '', + '', + FS::UI::Web::cust_styles(), + ; + my @search = (); -if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) { - push @search, "cust_credit.otaker = '$1'"; +if ( $cgi->param('usernum') =~ /^(\d+)$/ ) { + push @search, "cust_credit.usernum = $1"; } if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { @@ -67,6 +95,10 @@ if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { $title = $agent->agent. " $title"; } +if ( $unapplied ) { + push @search, FS::cust_credit->unapplied_sql . ' > 0'; +} + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); push @search, "_date >= $beginning ", "_date <= $ending"; @@ -76,29 +108,33 @@ push @search, FS::UI::Web::parse_lt_gt($cgi, 'amount' ); #here is the agent virtualization push @search, $FS::CurrentUser::CurrentUser->agentnums_sql; +my @select = ( + 'cust_credit.*', + 'cust_main.custnum as cust_main_custnum', + FS::UI::Web::cust_sql_fields(), +); + +if ( $unapplied ) { + push @select, '('.FS::cust_credit->unapplied_sql .') AS unapplied_amount'; + push @search, FS::cust_credit->unapplied_sql .' > 0'; +} + my $where = 'WHERE '. join(' AND ', @search); -my $count_query = 'SELECT COUNT(*), SUM(amount) '. - 'FROM cust_credit LEFT JOIN cust_main USING ( custnum ) '. +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 ) '. $where; +my @count_addl = ( $money_char.'%.2f total credited (gross)' ); +push @count_addl, $money_char.'%.2f unapplied' if $unapplied; + my $sql_query = { 'table' => 'cust_credit', - 'select' => join(', ', - 'cust_credit.*', - 'cust_main.custnum as cust_main_custnum', - FS::UI::Web::cust_sql_fields(), - ), + 'select' => join(', ',@select), 'hashref' => {}, 'extra_sql' => $where, 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', }; - my $clink = sub { - my $cust_bill = shift; - $cust_bill->cust_main_custnum - ? [ "${p}view/cust_main.cgi?", 'custnum' ] - : ''; - }; - </%init> diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi index e65dc71..8fbf636 100755 --- a/httemplate/search/cust_main.cgi +++ b/httemplate/search/cust_main.cgi @@ -204,7 +204,9 @@ % if ( $cgi->param('search_cust') ) { % $sortby = \*company_sort; % $orderby = "ORDER BY LOWER(company || ' ' || last || ' ' || first )"; -% push @cust_main, smart_search( 'search' => $cgi->param('search_cust') ); +% push @cust_main, smart_search( 'search' => $cgi->param('search_cust'), +% 'no_fuzzy_on_exact' => 1, #pref? +% ); % } % % @cust_main = grep { $_->ncancelled_pkgs || ! $_->all_pkgs } @cust_main @@ -270,7 +272,7 @@ % $cgi->param('offset', 0); % print qq!( <a href="!. $cgi->self_url. qq!">hide!; % } -% print ' cancelled customers</a> )'; +% print ' canceled customers</a> )'; % } % % if ( $cgi->param('referral_custnum') ) { @@ -634,7 +636,7 @@ % } % % if ( $last_type{'Fuzzy'} || $last_type{'All'} ) { -% push @cust_main, FS::cust_main->fuzzy_search( { 'last' => $last } ); +% push @cust_main, FS::cust_main::Search->fuzzy_search( { 'last' => $last } ); % } % % #if ($last_type{'Sound-alike'}) { @@ -681,7 +683,7 @@ % } % % if ( $company_type{'Fuzzy'} || $company_type{'All'} ) { -% push @cust_main, FS::cust_main->fuzzy_search( { 'company' => $company } ); +% push @cust_main, FS::cust_main::Search->fuzzy_search( { 'company' => $company } ); % } % % if ($company_type{'Sound-alike'}) { diff --git a/httemplate/search/cust_main.html b/httemplate/search/cust_main.html index 270fc38..04ecf89 100755 --- a/httemplate/search/cust_main.html +++ b/httemplate/search/cust_main.html @@ -44,8 +44,10 @@ my %search_hash = (); #scalars my @scalars = qw ( - agentnum status cancelled_pkgs cust_fields flattened_pkgs custbatch usernum - no_censustract paydate_year paydate_month invoice_terms + agentnum status address paydate_year paydate_month invoice_terms + no_censustract with_geocode custbatch usernum + cancelled_pkgs + cust_fields flattened_pkgs ); for my $param ( @scalars ) { @@ -54,7 +56,7 @@ for my $param ( @scalars ) { } #lists -for my $param (qw( classnum payby )) { +for my $param (qw( classnum payby tagnum )) { $search_hash{$param} = [ $cgi->param($param) ]; } @@ -84,7 +86,7 @@ $search_hash{'current_balance'} = # etc ### -my $sql_query = FS::cust_main->search(\%search_hash); +my $sql_query = FS::cust_main::Search->search(\%search_hash); my $count_query = delete($sql_query->{'count_query'}); my @extra_headers = @{ delete($sql_query->{'extra_headers'}) }; my @extra_fields = @{ delete($sql_query->{'extra_fields'}) }; @@ -104,7 +106,7 @@ if ( $FS::CurrentUser::CurrentUser->access_right('Bulk send customer notices') ) my $query = $uri->query; push @$menubar, 'Email a notice to these customers' => - "${p}misc/email-customers.html?$query", + "${p}misc/email-customers.html?table=cust_main&$query", } diff --git a/httemplate/search/cust_pay.cgi b/httemplate/search/cust_pay.html index 65bd39e..65bd39e 100755 --- a/httemplate/search/cust_pay.cgi +++ b/httemplate/search/cust_pay.html diff --git a/httemplate/search/cust_pay_pending.html b/httemplate/search/cust_pay_pending.html index f46e08a..8b73508 100755 --- a/httemplate/search/cust_pay_pending.html +++ b/httemplate/search/cust_pay_pending.html @@ -19,7 +19,7 @@ my %statusaction = ( 'new' => 'delete', 'pending' => 'complete', #'authorized' => '', - #'captured' => '', + 'captured' => 'capture', #'declined' => '', #wouldn't need to take action on a done state#'done' ); diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 74a3a6d..207e4f6 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -19,6 +19,7 @@ 'Adjourn', 'Susp.', 'Expire', + 'Contract end', 'Cancel', 'Reason', FS::UI::Web::cust_header( @@ -59,7 +60,7 @@ #sub { time2str('%b %d %Y', shift->expire); }, #sub { time2str('%b %d %Y', shift->get('cancel')); }, ( map { time_or_blank($_) } - qw( setup last_bill bill adjourn susp expire cancel ) ), + qw( setup last_bill bill adjourn susp expire contract_end cancel ) ), sub { my $self = shift; my $return = ''; @@ -175,8 +176,9 @@ my %search_hash = (); #some false laziness w/misc/bulk_change_pkg.cgi $search_hash{'query'} = $cgi->keywords; - -for (qw( agentnum custnum magic status classnum custom cust_fields )) { + +#scalars +for (qw( agentnum custnum magic status classnum custom cust_fields pkgbatch )) { $search_hash{$_} = $cgi->param($_) if $cgi->param($_); } @@ -205,7 +207,7 @@ my %disable = ( '' => {}, ); -foreach my $field (qw( setup last_bill bill adjourn susp expire cancel active )) { +foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end cancel active )) { my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, $field); @@ -289,6 +291,10 @@ my $html_init = sub { 'height' => 210, ). '<BR>'; } + $text .= include( '/elements/email-link.html', + 'search_hash' => \%search_hash, + 'table' => 'cust_pkg', + ); } return $text; }; diff --git a/httemplate/search/cust_pkg_discount.html b/httemplate/search/cust_pkg_discount.html index 233345e..d70c311 100644 --- a/httemplate/search/cust_pkg_discount.html +++ b/httemplate/search/cust_pkg_discount.html @@ -78,9 +78,9 @@ if ( $cgi->param('status') eq 'active' ) { "; #XXX also end date } -#otaker -if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) { - push @where, "cust_pkg_discount.otaker = '$1'"; +#usernum +if ( $cgi->param('usernum') =~ /^(\d+)$/ ) { + push @where, "cust_pkg_discount.usernum = $1"; } #agent diff --git a/httemplate/search/cust_pkg_summary.cgi b/httemplate/search/cust_pkg_summary.cgi new file mode 100644 index 0000000..fc71c81 --- /dev/null +++ b/httemplate/search/cust_pkg_summary.cgi @@ -0,0 +1,87 @@ +<% 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> +<%init> +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List packages'); + +my $title = 'Package Summary Report'; +my ($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi); +if($begin > 0) { + $title = "$title (". + $cgi->param('beginning').' - '.$cgi->param('ending').')'; +} + +my @h_sql = FS::h_cust_pkg->sql_h_search($end); + +my ($end_sql, $addl_from) = @h_sql[1,3]; +$end_sql =~ s/ORDER BY.*//; # breaks aggregate queries + +my $begin_sql = $end_sql; +$begin_sql =~ s/$end/$begin/g; + +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", + ); + +$_ =~ s/\bcust_pkg/maintable/g foreach @conds; + +my @head = ('Package', 'Before Period', 'Sales', 'Total', 'Active', 'Suspended'); +my @rows = (); +my @totals = ('Total', 0, 0, 0, 0, 0); + +if( !$begin ) { + splice @conds, 1, 1; + splice @head, 1, 1; +} + +foreach my $part_pkg (qsearch('part_pkg', {} )) { + 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', + 'hashref' => {}, + 'select' => 'count(*)', + 'addl_from' => $addl_from, + 'extra_sql' => 'WHERE pkgpart = '.$part_pkg->pkgpart.$cond, + }); + $row[$i] = $result->getfield('count'); + $totals[$i] += $row[$i]; + } + $i++; + } + $row[2] = $row[3]-$row[1]; + $totals[2] += $row[2]; + push @rows, \@row; +} +</%init> diff --git a/httemplate/search/cust_pkg_summary.html b/httemplate/search/cust_pkg_summary.html new file mode 100644 index 0000000..a0ef472 --- /dev/null +++ b/httemplate/search/cust_pkg_summary.html @@ -0,0 +1,24 @@ +<% include( '/elements/header.html', 'Package Summary Report' ) %> + +<FORM ACTION="cust_pkg_summary.cgi" METHOD="GET"> + +<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') %> + +</TABLE> + +<BR> +<INPUT TYPE="submit" VALUE="Get Report"> + +</FORM> + +<% include('/elements/footer.html') %> +<%init> +</%init> diff --git a/httemplate/search/cust_pkg_susp.cgi b/httemplate/search/cust_pkg_susp.cgi new file mode 100644 index 0000000..53631a2 --- /dev/null +++ b/httemplate/search/cust_pkg_susp.cgi @@ -0,0 +1,107 @@ +<% 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" STYLE="border: 1px solid #cccccc" ALIGN="right" BGCOLOR="<% $r % 2 ? '#ffffff' : '#eeeeee' %>"><% $_ %></TD> +% } + </TR> +% $r++; +% } + <TR> +% foreach (@totals) { + <TD CLASS="grid" STYLE="border: 1px solid #cccccc" ALIGN="right" BGCOLOR="<% $r % 2 ? '#ffffff' : '#eeeeee' %>"><B><% $_ %></B></TD> +% } + </TR> +</TABLE> +<%init> +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List packages'); + +my $money_char = FS::Conf->new()->config('money_char') || '$'; + +$FS::Record::DEBUG=0; + +my $title = 'Suspension/Unsuspension Report'; +my ($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi); +if($begin > 0) { + $title = "$title (". + ($cgi->param('beginning') || 'beginning').' - '. + ($cgi->param('ending') || 'present').')'; +} + + +my $begin_sql = $begin ? "AND h2.history_date > $begin" : ''; +my $end_sql = $end ? "AND h2.history_date < $end" : ''; + +my $h_sql = # self-join FTW! +"SELECT h1.pkgpart, count(h1.pkgnum) as pkgcount + FROM h_cust_pkg AS h1 INNER JOIN h_cust_pkg AS h2 ON (h1.pkgnum = h2.pkgnum) + WHERE h1.history_action = 'replace_old' AND h2.history_action = 'replace_new' + AND h2.historynum - h1.historynum = 1 + $begin_sql $end_sql"; +# This assumes replace_old and replace_new records get consecutive +# numbers. That's true in every case I've seen but is not actually +# enforced anywhere. If this is a problem we can match them up +# line by line but that's cumbersome. + +my @conds = ( + '(h1.susp is null OR h1.susp = 0) AND (h2.susp is not null AND h2.susp != 0)', + '(h1.susp is not null AND h1.susp != 0) AND (h2.susp is null OR h2.susp = 0)', +); + +my @results; +foreach my $cond (@conds) { + my $sql = "$h_sql AND $cond GROUP BY h1.pkgpart"; + my $sth = dbh->prepare($sql) or die dbh->errstr; + $sth->execute() or die $sth->errstr; + push @results, { map { @$_ } @{ $sth->fetchall_arrayref() } }; +} + +my @pay_cond; +push @pay_cond, "cust_bill_pay._date < $end" if $end; +push @pay_cond, "cust_bill_pay._date > $begin" if $begin; + +my $pay_cond = ''; +$pay_cond = 'WHERE '.join(' AND ', @pay_cond) if @pay_cond; + +my $pkg_payments = { + map { $_->pkgpart => $_->total_pay } + qsearch({ + 'table' => 'cust_pkg', + 'select' => 'pkgpart, sum(cust_bill_pay_pkg.amount) AS total_pay', + 'addl_from' => 'INNER JOIN cust_bill_pkg USING (pkgnum) + INNER JOIN cust_bill_pay_pkg USING (billpkgnum) + INNER JOIN cust_bill_pay USING (billpaynum)', + 'extra_sql' => $pay_cond . ' GROUP BY pkgpart', +}) }; + +my @head = ('Package', 'Suspended', 'Unsuspended', 'Payments'); +my @rows = (); +my @totals = map {0} @head; +$totals[0] = 'Total'; + +foreach my $part_pkg (qsearch('part_pkg', {} )) { + my @row = (); + next if !$part_pkg->freq; # exclude one-time packages + my $pkgpart = $part_pkg->pkgpart; + push @row, + $part_pkg->pkg, + $results[0]->{$pkgpart} || 0, + $results[1]->{$pkgpart} || 0, + sprintf("%.02f",$pkg_payments->{$pkgpart}); + + $totals[$_] += $row[$_] foreach (1..3); + $row[3] = $money_char.$row[3]; + + push @rows, \@row; +} +$totals[3] = $money_char.$totals[3]; + +</%init> diff --git a/httemplate/search/cust_pkg_susp.html b/httemplate/search/cust_pkg_susp.html new file mode 100644 index 0000000..c59e6c1 --- /dev/null +++ b/httemplate/search/cust_pkg_susp.html @@ -0,0 +1,24 @@ +<% include( '/elements/header.html', 'Suspension/Reactivation Report' ) %> + +<FORM ACTION="cust_pkg_susp.cgi" METHOD="GET"> + +<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') %> + +</TABLE> + +<BR> +<INPUT TYPE="submit" VALUE="Get Report"> + +</FORM> + +<% include('/elements/footer.html') %> +<%init> +</%init> diff --git a/httemplate/search/cust_pkg_svc.html b/httemplate/search/cust_pkg_svc.html new file mode 100644 index 0000000..4f27d66 --- /dev/null +++ b/httemplate/search/cust_pkg_svc.html @@ -0,0 +1,117 @@ +<% include( 'elements/search.html', + 'title' => $part_svc->svc.' services in package #'.$pkgnum, + 'name' => 'services', + 'html_form' => $html_form, + 'query' => $sql_query, + 'count_query' => $count_query, + 'redirect' => $link, + 'header' => [ '#', + 'Service', + '', #checkboxes + ], + 'fields' => [ 'svcnum', + sub { + ($_[0]->label)[1] + }, + sub { + $areboxes = 1; + '<INPUT TYPE="checkbox" NAME="svcnum" VALUE='.$_[0]->svcnum.'>' + }, + ], + 'links' => [ $link, + $link, + '', + ], + 'align' => 'rrlc', + 'color' => [ + ('')x4, + ], + 'style' => [ + ('')x4, + ], + 'html_foot' => sub { $areboxes ? $html_foot : '' } + ) +%> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List services'); + +my $pkgnum = $cgi->param('pkgnum'); +$pkgnum =~ /^(\d+)$/ or die "invalid pkgnum: $pkgnum"; +my @extra_sql = ( "cust_svc.pkgnum = $pkgnum" ); + +my $svcpart = $cgi->param('svcpart'); +$svcpart =~ /^(\d+)$/ or die "invalid svcpart: $svcpart"; +push @extra_sql, "cust_svc.svcpart = $svcpart"; +my $part_svc = qsearchs('part_svc', {svcpart => $svcpart}); +my $svcdb = $part_svc->svcdb; + +my $orderby = 'ORDER BY svcnum'; #others? + +my $addl_from = " LEFT JOIN part_svc USING (svcpart) +LEFT JOIN cust_pkg USING (pkgnum) +LEFT JOIN cust_main USING (custnum) +INNER JOIN $svcdb USING (svcnum)"; + +my $search_string; +if ( length( $cgi->param('search_svc') ) ) { + + $search_string = $cgi->param('search_svc'); + $search_string =~ s/(^\s+|\s+$)//; + push @extra_sql, "FS::$svcdb"->search_sql($search_string); + +} + +#here is the agent virtualization +push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( + 'null_right' => 'View/link unlinked services' + ); + +my $extra_sql = ' WHERE '. join(' AND ', @extra_sql ); + +my $sql_query = { + 'select' => join(', ', + 'cust_svc.*', + 'part_svc.svc', + ), + 'table' => 'cust_svc', + 'addl_from' => $addl_from, + 'hashref' => {}, + 'extra_sql' => "$extra_sql $orderby", +}; + +#warn Dumper($sql_query)."\n"; + +my $count_query = "SELECT COUNT(*) FROM cust_svc $addl_from $extra_sql"; + +my $link = sub { + my $cust_svc = shift; + my $url = svc_url( + 'm' => $m, + 'action' => 'view', + 'svcdb' => $svcdb, + 'query' => '', + ); + [ $url, 'svcnum' ]; +}; + +my $html_form = qq! +<SCRIPT TYPE="text/javascript"> +function areyousure(obj) { + return confirm('Permanently delete the selected services?'); +} +</SCRIPT> +<FORM METHOD="POST" ACTION="${p}misc/unprovision.cgi" onsubmit="return areyousure()">!; + +my $areboxes = 0; + +my $html_foot = qq! +<BR> +<INPUT TYPE="submit" NAME="submit" VALUE="Unprovision selected"> +<INPUT TYPE="hidden" NAME="pkgnum" VALUE=$pkgnum> +<INPUT TYPE="hidden" NAME="svcpart" VALUE=$svcpart> +</FORM>!; + + +</%init> diff --git a/httemplate/search/elements/cust_main_dayranges.html b/httemplate/search/elements/cust_main_dayranges.html index 9b8b08f..91e039d 100644 --- a/httemplate/search/elements/cust_main_dayranges.html +++ b/httemplate/search/elements/cust_main_dayranges.html @@ -145,8 +145,12 @@ unless ( $cgi->param('all_customers') ) { $days = $1; } + # If this is set, allow cust_main records with nonzero balances + my $negative = $cgi->param('negative') || 0; + push @where, - call_range_sub($range_sub, $days, 0, 'offset' => $offset, 'no_as'=>1). ' > 0'; # != 0'; + call_range_sub($range_sub, $days, 0, 'offset' => $offset, 'no_as'=>1). + ($negative ? ' != 0' : ' > 0'); } if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html index 4f83d0a..6f4aaf8 100755 --- a/httemplate/search/elements/cust_pay_or_refund.html +++ b/httemplate/search/elements/cust_pay_or_refund.html @@ -44,7 +44,7 @@ Examples: 'name_singular' => $name_singular, 'query' => $sql_query, 'count_query' => $count_query, - 'count_addl' => [ '$%.2f total '.$opt{name_verb}, ], + 'count_addl' => \@count_addl, 'redirect_empty' => $opt{'redirect_empty'}, 'header' => \@header, 'fields' => \@fields, @@ -68,7 +68,10 @@ my $table = $opt{'table'} || 'cust_'.$opt{'thing'}; my $amount_field = $opt{'amount_field'}; my $name_singular = $opt{'name_singular'}; -my $title = "\u$name_singular Search Results"; +my $unapplied = $cgi->param('unapplied'); +my $title = ''; +$title = 'Unapplied ' if $unapplied; +$title .= "\u$name_singular Search Results"; my $link = ''; if ( ( $curuser->access_right('View invoices') #XXX for now @@ -100,6 +103,36 @@ my $cust_link = sub { : ''; }; +# only valid for $table == 'cust_pay' atm +my $tax_names = ''; +if ( $cgi->param('tax_names') ) { + if ( dbh->{Driver}->{Name} eq 'Pg' ) { + + $tax_names = " + array_to_string( + array( + SELECT itemdesc + FROM cust_bill_pay + LEFT JOIN cust_bill_pay_pkg USING ( billpaynum ) + LEFT JOIN cust_bill_pkg USING ( billpkgnum ) + WHERE cust_bill_pkg.pkgnum = 0 + AND cust_bill_pay.paynum = cust_pay.paynum + ), '|' + ) AS tax_names" + ; + + } elsif ( dbh->{Driver}->{Name} =~ /^mysql/i ) { + + $tax_names = "GROUP_CONCAT(itemdesc SEPARATOR '|') AS tax_names"; + + } else { + + warn "warning: unknown database type ". dbh->{Driver}->{Name}. + "omitting tax name information from report."; + + } +} + my @header = (); my @fields = (); my $align = ''; @@ -113,15 +146,25 @@ if ( $opt{'pre_header'} ) { push @header, "\u$name_singular", 'Amount', - 'Date', ; -$align .= 'rrr'; -push @links, '', '', ''; +$align .= 'rr'; +push @links, '', ''; push @fields, 'payby_payinfo_pretty', sub { sprintf('$%.2f', shift->$amount_field() ) }, - sub { time2str('%b %d %Y', shift->_date ) }, ; +if ( $unapplied ) { + push @header, 'Unapplied'; + $align .= 'r'; + push @links, ''; + push @fields, sub { sprintf('$%.2f', shift->unapplied_amount) }; +} + +push @header, 'Date'; +$align .= 'r'; +push @links, ''; +push @fields, sub { time2str('%b %d %Y', shift->_date ) }; + unless ( $opt{'disable_by'} ) { push @header, 'By'; $align .= 'c'; @@ -133,6 +176,22 @@ unless ( $opt{'disable_by'} ) { }; } +if ( $tax_names ) { + push @header, ('Tax names', 'Tax province'); + $align .= 'cc'; + push @links, ('',''); + push @fields, sub { join (' + ', map { /^(.*?)(, \w\w)?$/; $1 } + split('\|', shift->tax_names) + ); + }; + push @fields, sub { join (' + ', map { if (/^(?:.*)(?:, )(\w\w)$/){ $1 } + else { () } + } + split('\|', shift->tax_names) + ); + }; +} + push @header, FS::UI::Web::cust_header(); $align .= FS::UI::Web::cust_aligns(); push @links, map { $_ ne 'Cust. Status' ? $cust_link : '' } @@ -146,10 +205,17 @@ push @header, @{ $opt{'addl_header'} } push @fields, @{ $opt{'addl_fields'} } if $opt{'addl_fields'}; -my( $count_query, $sql_query ); +my( $count_query, $sql_query, @count_addl ); if ( $cgi->param('magic') ) { my @search = (); + my @select = ( + "$table.*", + FS::UI::Web::cust_sql_fields(), + 'cust_main.custnum AS cust_main_custnum', + ); + push @select, $tax_names if $tax_names; + my $orderby; if ( $cgi->param('magic') eq '_date' ) { @@ -245,8 +311,8 @@ if ( $cgi->param('magic') ) { push @search, "$table.payinfo = '$1'"; } - if ( $cgi->param('otaker') =~ /^(\w+)$/ ) { - push @search, "$table.otaker = '$1'"; + if ( $cgi->param('usernum') =~ /^(\d+)$/ ) { + push @search, "$table.usernum = $1"; } #for cust_pay_pending... statusNOT=done @@ -282,6 +348,13 @@ if ( $cgi->param('magic') ) { die "unknown search magic: ". $cgi->param('magic'); } + #unapplied payment/refund + if ( $unapplied ) { + push @select, '(' . "FS::$table"->unapplied_sql . ') AS unapplied_amount'; + push @search, "FS::$table"->unapplied_sql . ' > 0'; + + } + #for the history search if ( $cgi->param('history_action') =~ /^([\w,]+)$/ ) { my @history_action = split(/,/, $1); @@ -300,22 +373,49 @@ if ( $cgi->param('magic') ) { #here is the agent virtualization push @search, $curuser->agentnums_sql; + my $addl_from = ' LEFT JOIN cust_main USING ( custnum ) '; + my $group_by = ''; + + if ( $cgi->param('tax_names') ) { + if ( dbh->{Driver}->{Name} eq 'Pg' ) { + + 0;#twiddle thumbs + + } elsif ( dbh->{Driver}->{Name} =~ /^mysql/i ) { + + $addl_from .= "LEFT JOIN cust_bill_pay USING ( paynum ) + LEFT JOIN cust_bill_pay_pkg USING ( billpaynum ) + LEFT JOIN cust_bill_pkg USING ( billpkgnum ) AS tax_names"; + $group_by .= "GROUP BY $table.*,cust_main_custnum,". + FS::UI::Web::cust_sql_fields(); + push @search, + "( cust_bill_pkg.pkgnum = 0 OR cust_bill_pkg.pkgnum is NULL )"; + + } else { + + warn "warning: unknown database type ". dbh->{Driver}->{Name}. + "omitting tax name information from report."; + + } + } + my $search = ' WHERE '. join(' AND ', @search); - $count_query = "SELECT COUNT(*), SUM($amount_field) ". - "FROM $table LEFT JOIN cust_main USING ( custnum )". - $search; + $count_query = "SELECT COUNT(*), SUM($amount_field) "; + $count_query .= ', SUM(' . "FS::$table"->unapplied_sql . ') ' + if $unapplied; + $count_query .= "FROM $table $addl_from". + "$search $group_by"; + + @count_addl = ( '$%.2f total '.$opt{name_verb} ); + push @count_addl, '$%.2f unapplied' if $unapplied; $sql_query = { 'table' => $table, - 'select' => join(', ', - "$table.*", - 'cust_main.custnum as cust_main_custnum', - FS::UI::Web::cust_sql_fields(), - ), + 'select' => join(', ', @select), 'hashref' => {}, - 'extra_sql' => "$search ORDER BY $orderby", - 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', + 'extra_sql' => "$search $group_by ORDER BY $orderby", + 'addl_from' => $addl_from, }; } else { @@ -331,6 +431,7 @@ if ( $cgi->param('magic') ) { $count_query = "SELECT COUNT(*), SUM($amount_field) FROM $table". " WHERE payinfo = '$payinfo' AND payby = '$payby'". " AND ". $curuser->agentnums_sql; + @count_addl = ( '$%.2f total '.$opt{name_verb} ); $sql_query = { 'table' => $table, @@ -342,4 +443,7 @@ if ( $cgi->param('magic') ) { } +# for consistency +$title = join('',map {ucfirst} split(/\b/,$title)); + </%init> diff --git a/httemplate/search/elements/report_cust_pay_or_refund.html b/httemplate/search/elements/report_cust_pay_or_refund.html new file mode 100644 index 0000000..9af4e33 --- /dev/null +++ b/httemplate/search/elements/report_cust_pay_or_refund.html @@ -0,0 +1,149 @@ +<%doc> + +Examples: + + include( 'elements/report_cust_pay_or_refund.html', + 'thing' => 'pay', + 'name_singular' => 'payment', + ) + + include( 'elements/report_cust_pay_or_refund.html', + 'thing' => 'refund', + 'name_singular' => 'refund', + ) + +</%doc> +<% include('/elements/header.html', $title ) %> + +<FORM ACTION="<% $table %>.html" METHOD="GET"> +<INPUT TYPE="hidden" NAME="magic" VALUE="_date"> +<INPUT TYPE="hidden" NAME="unapplied" VALUE="<% $unapplied %>"> + +<TABLE BGCOLOR="#cccccc" CELLSPACING=0> + + <TR> + <TH CLASS="background" COLSPAN=2 ALIGN="left"> + <FONT SIZE="+1">Search options</FONT> + </TH> + </TR> + + <TR> + <TD ALIGN="right"><% ucfirst(PL($name_singular)) %> of type: </TD> + <TD> + <SELECT NAME="payby" onChange="payby_changed(this)"> + <OPTION VALUE="">all</OPTION> + <OPTION VALUE="CARD">credit card (all)</OPTION> + <OPTION VALUE="CARD-VisaMC">credit card (Visa/MasterCard)</OPTION> + <OPTION VALUE="CARD-Amex">credit card (American Express)</OPTION> + <OPTION VALUE="CARD-Discover">credit card (Discover)</OPTION> + <OPTION VALUE="CARD-Maestro">credit card (Maestro/Switch/Solo)</OPTION> + <OPTION VALUE="CHEK">electronic check / ACH</OPTION> + <OPTION VALUE="BILL">check</OPTION> + <OPTION VALUE="PREP">prepaid card</OPTION> + <OPTION VALUE="CASH">cash</OPTION> + <OPTION VALUE="WEST">Western Union</OPTION> + <OPTION VALUE="MCRD">manual credit card</OPTION> + </SELECT> + </TD> + </TR> + + <SCRIPT TYPE="text/javascript"> + + function payby_changed(what) { + if ( what.options[what.selectedIndex].value == 'BILL' ) { + document.getElementById('checkno_caption').style.color = '#000000'; + what.form.payinfo.disabled = false; + what.form.payinfo.style.backgroundColor = '#ffffff'; + } else { + document.getElementById('checkno_caption').style.color = '#bbbbbb'; + what.form.payinfo.disabled = true; + what.form.payinfo.style.backgroundColor = '#dddddd'; + } + } + + </SCRIPT> + + <TR> + <TD ALIGN="right"><FONT ID="checkno_caption" COLOR="#bbbbbb">Check #: </FONT></TD> + <TD> + <INPUT TYPE="text" NAME="payinfo" DISABLED STYLE="background-color: #dddddd"> + </TD> + </TR> + + <% include( '/elements/tr-select-agent.html', + 'curr_value' => scalar($cgi->param('agentnum')), + 'label' => 'for agent: ', + 'disable_empty' => 0, + ) + %> + + <% include( '/elements/tr-select-user.html' ) %> + + <TR> + <TD ALIGN="right" VALIGN="center">Payment</TD> + <TD> + <TABLE> + <% include( '/elements/tr-input-beginning_ending.html', + layout => 'horiz', + ) + %> + </TABLE> + </TD> + </TR> + +% if ( $void ) { + <TR> + <TD ALIGN="right" VALIGN="center">Voided</TD> + <TD> + <TABLE> + <% include( '/elements/tr-input-beginning_ending.html', + prefix => 'void', + layout => 'horiz', + ) + %> + </TABLE> + </TD> + </TR> +% } + + <% include( '/elements/tr-input-lessthan_greaterthan.html', + 'label' => 'Amount', + 'field' => 'paid', + ) + %> + +% if ( $table eq 'cust_pay' ) { + <% include( '/elements/tr-checkbox.html', + 'label' => 'Include tax names', + 'field' => 'tax_names', + 'value' => 1, + ) + %> +% } + +</TABLE> + +<BR> +<INPUT TYPE="submit" VALUE="Get Report"> + +</FORM> + +<% include('/elements/footer.html') %> +<%init> + +my %opt = @_; +my $table = 'cust_'.$opt{'thing'}; +my $name_singular = $opt{'name_singular'}; + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my $void = $cgi->param('void') ? 1 : 0; +my $unapplied = $cgi->param('unapplied') ? 1 : 0; + +my $title = $void ? "Voided $name_singular report" : + $unapplied ? "Unapplied $name_singular report" : + "\u$name_singular report" ; +$table .= '_void' if $void; + +</%init> diff --git a/httemplate/search/elements/search-xml.html b/httemplate/search/elements/search-xml.html index 9f5e9b6..50b1916 100644 --- a/httemplate/search/elements/search-xml.html +++ b/httemplate/search/elements/search-xml.html @@ -14,6 +14,7 @@ % } else { % $value = $row->$field(); % } +% next unless ($value || !$opt{xml_omit_empty}); % <% &{$beginfield}($row, $i) %><% $value |h %><% &{$endfield}($row, $i) %> % diff --git a/httemplate/search/report_477.html b/httemplate/search/report_477.html index bc2a958..7ac497a 100755 --- a/httemplate/search/report_477.html +++ b/httemplate/search/report_477.html @@ -181,7 +181,7 @@ <% include( '/elements/tr-checkbox.html', 'label' => 'Enable part VI?', 'field' => 'part', - 'value' => 'VI', + 'value' => 'VI_census', ) %> diff --git a/httemplate/search/report_cdr.html b/httemplate/search/report_cdr.html index a50e4db..866606c 100644 --- a/httemplate/search/report_cdr.html +++ b/httemplate/search/report_cdr.html @@ -65,7 +65,21 @@ <TR> <TD ALIGN="right">Charged Party #: </TD> <TD> - <INPUT TYPE="text" NAME="charged_party"> + <INPUT TYPE="text" NAME="charged_party" VALUE="<% join(',', @charged_party) |h %>"> + </TD> + </TR> + + <TR> + <TD ALIGN="right">Charged Party or Source #: </TD> + <TD> + <INPUT TYPE="text" NAME="charged_party_or_src" VALUE="<% join(',', @charged_party_or_src ) |h %>" > + </TD> + </TR> + + <TR> + <TD ALIGN="right">Freeside service #: </TD> + <TD> + <INPUT TYPE="text" NAME="svcnum" VALUE="<% join(',', @svcnum ) %>" > </TD> </TR> @@ -145,4 +159,72 @@ my $names_list = [ map { @fields ]; +my @charged_party = (); +my @charged_party_or_src = (); +my @svcnum = (); +if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { + my $custnum = $1; + + my $cust_main = qsearchs( { + 'table' => 'cust_main', + 'hashref' => { 'custnum' => $custnum }, + 'extra_sql' => ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql, + }); + die "Customer not found!" unless $cust_main; + + #historical? + foreach my $cust_pkg ( $cust_main->ncancelled_pkgs ) { + + my @voip_pkgs = + grep { $_->plan eq 'voip_cdr' } $cust_pkg->part_pkg->self_and_bill_linked; + if ( scalar(@voip_pkgs) > 1 ) { + die "multiple voip_cdr packages bundled\n"; + } elsif ( !@voip_pkgs ) { + next; + } + my $voip_pkg = @voip_pkgs[0]; + + my $cdr_svc_method = $voip_pkg->option('cdr_svc_method') + || 'svc_phone.phonenum'; + + my @cust_svc = $cust_pkg->cust_svc; #historical? + + if ( $cdr_svc_method eq 'svc_phone.phonenum' ) { + + my @svc_phone = map $_->svc_x, + grep { $_->part_svc->svcdb eq 'svc_phone' } @cust_svc; + + my @numbers = map { + my $number = $_->phonenum; + $number = $_->countrycode. $number + unless $_->countrycode eq '1'; + $number; + } + @svc_phone; + + if ( $voip_pkg->option('disable_src') ) { + push @charged_party, @numbers; + } else { + push @charged_party_or_src, @numbers; + } + + } elsif ( $cdr_svc_method eq 'svc_pbx.title' ) { + my @svc_pbx = map $_->svc_x, + grep { $_->part_svc->svcdb eq 'svc_pbx' } @cust_svc; + push @charged_party, map $_->title, @svc_pbx; + } elsif ( $cdr_svc_method eq 'svc_pbx.svcnum' ) { + my @cust_svc_pbx = grep { $_->part_svc->svcdb eq 'svc_pbx' } @cust_svc; + push @svcnum, map $_->svcnum, @cust_svc_pbx; + } + + } + + die "No CDR packages for customer $custnum\n" + unless @charged_party || @charged_party_or_src || @svcnum; + + #die "Multiple matching metods for customer $custnum\n" + # if #there's more than one + +} + </%init> diff --git a/httemplate/search/report_cust_bill.html b/httemplate/search/report_cust_bill.html index 00d566a..b1a252e 100644 --- a/httemplate/search/report_cust_bill.html +++ b/httemplate/search/report_cust_bill.html @@ -25,6 +25,13 @@ field => 'owed', ) %> + <% include( '/elements/tr-select-payby.html', + label => 'Payment method:', + payby_type => 'cust', + multiple => 1, + all_selected => 1, + ) + %> <TR> <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="open" VALUE="1" CHECKED></TD> diff --git a/httemplate/search/report_cust_bill_pkg_discount.html b/httemplate/search/report_cust_bill_pkg_discount.html index f1879d4..f9ab901 100644 --- a/httemplate/search/report_cust_bill_pkg_discount.html +++ b/httemplate/search/report_cust_bill_pkg_discount.html @@ -5,9 +5,9 @@ <TABLE> - <% include( '/elements/tr-select-otaker.html', - 'label' => 'Discounts by employee: ', - 'otakers' => \@otakers, + <% include( '/elements/tr-select-user.html', + 'label' => 'Discounts by employee: ', + 'access_user' => \%access_user, ) %> @@ -39,9 +39,12 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); -my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_pkg_discount") +my $sth = dbh->prepare("SELECT DISTINCT usernum FROM cust_pkg_discount") or die dbh->errstr; $sth->execute or die $sth->errstr; -my @otakers = map { $_->[0] } @{$sth->fetchall_arrayref}; +my @usernum = map $_->[0], @{$sth->fetchall_arrayref}; +my %access_user = + map { $_ => qsearchs('access_user',{'usernum'=>$_})->username } + @usernum; </%init> diff --git a/httemplate/search/report_cust_credit.html b/httemplate/search/report_cust_credit.html index 9c719b7..16a75eb 100644 --- a/httemplate/search/report_cust_credit.html +++ b/httemplate/search/report_cust_credit.html @@ -1,13 +1,14 @@ -<% include('/elements/header.html', 'Credit report' ) %> +<% include('/elements/header.html', $title ) %> <FORM ACTION="cust_credit.html" METHOD="GET"> <INPUT TYPE="hidden" NAME="magic" VALUE="_date"> +<INPUT TYPE="hidden" NAME="unapplied" VALUE="<% $unapplied %>"> <TABLE> - <% include( '/elements/tr-select-otaker.html', - 'label' => 'Credits by employee: ', - 'otakers' => \@otakers, + <% include( '/elements/tr-select-user.html', + 'label' => 'Credits by employee: ', + 'access_user' => \%access_user, ) %> @@ -40,9 +41,17 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); -my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_credit") +my $sth = dbh->prepare("SELECT DISTINCT usernum FROM cust_credit") or die dbh->errstr; $sth->execute or die $sth->errstr; -my @otakers = map { $_->[0] } @{$sth->fetchall_arrayref}; +my @usernum = map $_->[0], @{$sth->fetchall_arrayref}; +my %access_user = + map { $_ => qsearchs('access_user',{'usernum'=>$_})->username } + @usernum; + +my $unapplied = $cgi->param('unapplied') ? 1 : 0; + +my $title = $cgi->param('unapplied') ? + 'Unapplied credit report' : 'Credit report'; </%init> diff --git a/httemplate/search/report_cust_main.html b/httemplate/search/report_cust_main.html index eb1a662..d6be4fb 100755 --- a/httemplate/search/report_cust_main.html +++ b/httemplate/search/report_cust_main.html @@ -28,6 +28,11 @@ ) %> + <TR> + <TD ALIGN="right" VALIGN="center">Address</TD> + <TD><INPUT TYPE="text" NAME="address" SIZE=54></TD> + </TR> + % foreach my $field (qw( signupdate )) { <TR> @@ -45,6 +50,14 @@ % } + <% include( '/elements/tr-select-cust_tag.html', + 'cgi' => $cgi, + 'is_report' => 1, + 'multiple' => 1, + 'all_selected' => 1, + ) + %> + <% include( '/elements/tr-select-payby.html', 'payby_type' => 'cust', 'multiple' => 1, @@ -105,13 +118,18 @@ <TD><INPUT TYPE="checkbox" NAME="cancelled_pkgs"></TD> </TR> -% if ( $conf->exists('cust_main-require_censustract') ) { - <TR> <TD ALIGN="right" VALIGN="center">Without census tract</TD> <TD><INPUT TYPE="checkbox" NAME="no_censustract"></TD> </TR> +% if ( $conf->exists('enable_taxproducts') ) { + + <TR> + <TD ALIGN="right" VALIGN="center">With hardcoded tax location</TD> + <TD><INPUT TYPE="checkbox" NAME="with_geocode"></TD> + </TR> + % } <TR> diff --git a/httemplate/search/report_cust_pay.html b/httemplate/search/report_cust_pay.html index 6c10a2e..ea7a215 100644 --- a/httemplate/search/report_cust_pay.html +++ b/httemplate/search/report_cust_pay.html @@ -1,116 +1,5 @@ -<% include('/elements/header.html', $title ) %> - -<FORM ACTION="<% $void ? 'cust_pay_void.html' : 'cust_pay.cgi' %>" METHOD="GET"> -<INPUT TYPE="hidden" NAME="magic" VALUE="_date"> - -<TABLE BGCOLOR="#cccccc" CELLSPACING=0> - - <TR> - <TH CLASS="background" COLSPAN=2 ALIGN="left"> - <FONT SIZE="+1">Search options</FONT> - </TH> - </TR> - - <TR> - <TD ALIGN="right">Payments of type: </TD> - <TD> - <SELECT NAME="payby" onChange="payby_changed(this)"> - <OPTION VALUE="">all</OPTION> - <OPTION VALUE="CARD">credit card (all)</OPTION> - <OPTION VALUE="CARD-VisaMC">credit card (Visa/MasterCard)</OPTION> - <OPTION VALUE="CARD-Amex">credit card (American Express)</OPTION> - <OPTION VALUE="CARD-Discover">credit card (Discover)</OPTION> - <OPTION VALUE="CARD-Maestro">credit card (Maestro/Switch/Solo)</OPTION> - <OPTION VALUE="CHEK">electronic check / ACH</OPTION> - <OPTION VALUE="BILL">check</OPTION> - <OPTION VALUE="PREP">prepaid card</OPTION> - <OPTION VALUE="CASH">cash</OPTION> - <OPTION VALUE="WEST">Western Union</OPTION> - <OPTION VALUE="MCRD">manual credit card</OPTION> - </SELECT> - </TD> - </TR> - - <SCRIPT TYPE="text/javascript"> - - function payby_changed(what) { - if ( what.options[what.selectedIndex].value == 'BILL' ) { - document.getElementById('checkno_caption').style.color = '#000000'; - what.form.payinfo.disabled = false; - what.form.payinfo.style.backgroundColor = '#ffffff'; - } else { - document.getElementById('checkno_caption').style.color = '#bbbbbb'; - what.form.payinfo.disabled = true; - what.form.payinfo.style.backgroundColor = '#dddddd'; - } - } - - </SCRIPT> - - <TR> - <TD ALIGN="right"><FONT ID="checkno_caption" COLOR="#bbbbbb">Check #: </FONT></TD> - <TD> - <INPUT TYPE="text" NAME="payinfo" DISABLED STYLE="background-color: #dddddd"> - </TD> - </TR> - - <% include( '/elements/tr-select-agent.html', - 'curr_value' => scalar($cgi->param('agentnum')), - 'label' => 'for agent: ', - 'disable_empty' => 0, - ) - %> - - <% include( '/elements/tr-select-otaker.html' ) %> - - <TR> - <TD ALIGN="right" VALIGN="center">Payment</TD> - <TD> - <TABLE> - <% include( '/elements/tr-input-beginning_ending.html', - layout => 'horiz', - ) - %> - </TABLE> - </TD> - </TR> - -% if ( $void ) { - <TR> - <TD ALIGN="right" VALIGN="center">Voided</TD> - <TD> - <TABLE> - <% include( '/elements/tr-input-beginning_ending.html', - prefix => 'void', - layout => 'horiz', - ) - %> - </TABLE> - </TD> - </TR> -% } - - <% include( '/elements/tr-input-lessthan_greaterthan.html', - 'label' => 'Amount', - 'field' => 'paid', - ) - %> - -</TABLE> - -<BR> -<INPUT TYPE="submit" VALUE="Get Report"> - -</FORM> - -<% include('/elements/footer.html') %> -<%init> - -die "access denied" - unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); - -my $void = $cgi->param('void') ? 1 : 0; - -my $title = $void ? 'Voided payment report' : 'Payment report'; - -</%init> +<% include( 'elements/report_cust_pay_or_refund.html', + 'thing' => 'pay', + 'name_singular' => 'payment', + ) +%> diff --git a/httemplate/search/report_cust_pkg.html b/httemplate/search/report_cust_pkg.html index 58fcf61..289fec4 100755 --- a/httemplate/search/report_cust_pkg.html +++ b/httemplate/search/report_cust_pkg.html @@ -89,7 +89,7 @@ % } -% foreach my $field (qw( setup last_bill bill adjourn susp expire cancel )) { +% foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end cancel )) { <TR> <TD ALIGN="right" VALIGN="center"><% $label{$field} %></TD> @@ -181,6 +181,7 @@ my %label = ( 'adjourn' => 'Adjourns', 'susp' => 'Suspended', 'expire' => 'Expires', + 'contract_end' => 'Contract ends', 'cancel' => 'Cancelled', ); diff --git a/httemplate/search/report_cust_pkg_discount.html b/httemplate/search/report_cust_pkg_discount.html index 7ebd44f..31774c3 100644 --- a/httemplate/search/report_cust_pkg_discount.html +++ b/httemplate/search/report_cust_pkg_discount.html @@ -16,9 +16,9 @@ </TD> </TR> - <% include( '/elements/tr-select-otaker.html', - 'label' => 'Discounts by employee: ', - 'otakers' => \@otakers, + <% include( '/elements/tr-select-user.html', + 'label' => 'Discounts by employee: ', + 'access_user' => \%access_user, ) %> @@ -42,9 +42,12 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); -my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_pkg_discount") +my $sth = dbh->prepare("SELECT DISTINCT usernum FROM cust_pkg_discount") or die dbh->errstr; $sth->execute or die $sth->errstr; -my @otakers = map { $_->[0] } @{$sth->fetchall_arrayref}; +my @usernum = map $_->[0], @{$sth->fetchall_arrayref}; +my %access_user = + map { $_ => qsearchs('access_user',{'usernum'=>$_})->username } + @usernum; </%init> diff --git a/httemplate/search/report_cust_refund.html b/httemplate/search/report_cust_refund.html index 4d31100..b886f2e 100644 --- a/httemplate/search/report_cust_refund.html +++ b/httemplate/search/report_cust_refund.html @@ -1,116 +1,5 @@ -<% include('/elements/header.html', $title ) %> - -<FORM ACTION="<% $void ? 'cust_refund_void.html' : 'cust_refund.html' %>" METHOD="GET"> -<INPUT TYPE="hidden" NAME="magic" VALUE="_date"> - -<TABLE BGCOLOR="#cccccc" CELLSPACING=0> - - <TR> - <TH CLASS="background" COLSPAN=2 ALIGN="left"> - <FONT SIZE="+1">Search options</FONT> - </TH> - </TR> - - <TR> - <TD ALIGN="right">Refunds of type: </TD> - <TD> - <SELECT NAME="payby" onChange="payby_changed(this)"> - <OPTION VALUE="">all</OPTION> - <OPTION VALUE="CARD">credit card (all)</OPTION> - <OPTION VALUE="CARD-VisaMC">credit card (Visa/MasterCard)</OPTION> - <OPTION VALUE="CARD-Amex">credit card (American Express)</OPTION> - <OPTION VALUE="CARD-Discover">credit card (Discover)</OPTION> - <OPTION VALUE="CARD-Maestro">credit card (Maestro/Switch/Solo)</OPTION> - <OPTION VALUE="CHEK">electronic check / ACH</OPTION> - <OPTION VALUE="BILL">check</OPTION> - <OPTION VALUE="PREP">prepaid card</OPTION> - <OPTION VALUE="CASH">cash</OPTION> - <OPTION VALUE="WEST">Western Union</OPTION> - <OPTION VALUE="MCRD">manual credit card</OPTION> - </SELECT> - </TD> - </TR> - - <SCRIPT TYPE="text/javascript"> - - function payby_changed(what) { - if ( what.options[what.selectedIndex].value == 'BILL' ) { - document.getElementById('checkno_caption').style.color = '#000000'; - what.form.payinfo.disabled = false; - what.form.payinfo.style.backgroundColor = '#ffffff'; - } else { - document.getElementById('checkno_caption').style.color = '#bbbbbb'; - what.form.payinfo.disabled = true; - what.form.payinfo.style.backgroundColor = '#dddddd'; - } - } - - </SCRIPT> - - <TR> - <TD ALIGN="right"><FONT ID="checkno_caption" COLOR="#bbbbbb">Check #: </FONT></TD> - <TD> - <INPUT TYPE="text" NAME="payinfo" DISABLED STYLE="background-color: #dddddd"> - </TD> - </TR> - - <% include( '/elements/tr-select-agent.html', - 'curr_value' => scalar($cgi->param('agentnum')), - 'label' => 'for agent: ', - 'disable_empty' => 0, - ) - %> - - <% include( '/elements/tr-select-otaker.html' ) %> - - <TR> - <TD ALIGN="right" VALIGN="center">Refund</TD> - <TD> - <TABLE> - <% include( '/elements/tr-input-beginning_ending.html', - layout => 'horiz', - ) - %> - </TABLE> - </TD> - </TR> - -% if ( $void ) { - <TR> - <TD ALIGN="right" VALIGN="center">Voided</TD> - <TD> - <TABLE> - <% include( '/elements/tr-input-beginning_ending.html', - prefix => 'void', - layout => 'horiz', - ) - %> - </TABLE> - </TD> - </TR> -% } - - <% include( '/elements/tr-input-lessthan_greaterthan.html', - 'label' => 'Amount', - 'field' => 'paid', - ) - %> - -</TABLE> - -<BR> -<INPUT TYPE="submit" VALUE="Get Report"> - -</FORM> - -<% include('/elements/footer.html') %> -<%init> - -die "access denied" - unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); - -my $void = $cgi->param('void') ? 1 : 0; - -my $title = $void ? 'Voided refund report' : 'Refund report'; - -</%init> +<% include( 'elements/report_cust_pay_or_refund.html', + 'thing' => 'refund', + 'name_singular' => 'refund', + ) +%> diff --git a/httemplate/search/report_h_cust_pay.html b/httemplate/search/report_h_cust_pay.html index fe7c4a9..5c7f27a 100644 --- a/httemplate/search/report_h_cust_pay.html +++ b/httemplate/search/report_h_cust_pay.html @@ -88,7 +88,7 @@ ) %> - <% include( '/elements/tr-select-otaker.html' ) %> + <% include( '/elements/tr-select-user.html' ) %> <TR> <TD ALIGN="right" VALIGN="center">Payment</TD> diff --git a/httemplate/search/report_receivables.html b/httemplate/search/report_receivables.html index 912ef26..e85d786 100755 --- a/httemplate/search/report_receivables.html +++ b/httemplate/search/report_receivables.html @@ -20,8 +20,23 @@ <TR> <TD ALIGN="right">Customers</TD> <TD> - <INPUT TYPE="radio" NAME="all_customers" VALUE="1" onClick="if (this.checked) { document.OneTrueForm.days.disabled=true; document.OneTrueForm.days.style.backgroundColor = '#dddddd'; } else { document.OneTrueForm.days.disabled=false; document.OneTrueForm.days.style.backgroundColor = '#ffffff'; }">All customers (even those without an outstanding balance)<BR> - <INPUT TYPE="radio" NAME="all_customers" VALUE="0" CHECKED onClick="if ( ! this.checked ) { document.OneTrueForm.days.disabled=true; document.OneTrueForm.days.style.backgroundColor = '#dddddd'; } else { document.OneTrueForm.days.disabled=false; document.OneTrueForm.days.style.backgroundColor = '#ffffff'; }">Customers with a balance over <INPUT NAME="days" TYPE="text" SIZE=4 MAXLENGTH=3 VALUE="0"> days old + <SCRIPT TYPE="text/javascript"> +function toggle(obj) { + var f = document.OneTrueForm; + var val = (obj.value == obj.checked); + f.days.disabled = val; + f.negative.disabled = val; + f.days.style.backgroundColor = val ? '#dddddd' : '#ffffff'; +} + </SCRIPT> + <TABLE STYLE="padding: 0px"> + <TR><TD><INPUT TYPE="radio" NAME="all_customers" VALUE="1" onClick="toggle(this)"></TD> + <TD>All customers (even those without an outstanding balance)</TD></TR> + <TR><TD><INPUT TYPE="radio" NAME="all_customers" VALUE="0" CHECKED onClick="toggle(this)"></TD> + <TD>Customers with a balance over <INPUT NAME="days" TYPE="text" SIZE=4 MAXLENGTH=3 VALUE="0"> days old</TD></TR> + <TR><TD></TD> + <TD><INPUT TYPE="checkbox" NAME="negative" VALUE="1"> Including customers with credit balances</TD></TR> + </TABLE> </TD> </TR> <% include( '/elements/tr-input-date-field.html', { diff --git a/httemplate/search/report_svc_broadband.html b/httemplate/search/report_svc_broadband.html new file mode 100755 index 0000000..8571ef1 --- /dev/null +++ b/httemplate/search/report_svc_broadband.html @@ -0,0 +1,100 @@ +<% include('/elements/header.html', $title ) %> + +<FORM ACTION="svc_broadband.cgi" METHOD="GET"> +<INPUT TYPE="hidden" NAME="magic" VALUE="advanced"> +<INPUT TYPE="hidden" NAME="custnum" VALUE="<% $custnum %>"> +%# extensive false laziness with svc_acct + <TABLE BGCOLOR="#cccccc" CELLSPACING=0> + + <TR> + <TH CLASS="background" COLSPAN=2 ALIGN="left"><FONT SIZE="+1">Search options</FONT></TH> + </TR> + +% unless ( $custnum ) { + <% include( '/elements/tr-select-agent.html', + 'curr_value' => scalar( $cgi->param('agentnum') ), + 'disable_empty' => 0, + ) + %> + + <% include( '/elements/tr-select-table.html', + 'label' => 'Routers', + 'table' => 'router', + 'name_col' => 'routername', + 'curr_value' => $routernum, + 'hashref' => {}, + 'multiple' => 'multiple', + ) + %> +% } + + <% include( '/elements/tr-selectmultiple-part_pkg.html', + %pkg_search, + ) + %> + + <TR> + <TH CLASS="background" COLSPAN=2> </TH> + </TR> + + <TR> + <TH CLASS="background" COLSPAN=2 ALIGN="left"><FONT SIZE="+1">Display options</FONT></TH> + </TR> + +% #move to /elements/tr-select-cust_pkg-fields if anything else needs it... + <TR> + <TD ALIGN="right">Package fields</TD> + <TD> + <SELECT NAME="cust_pkg_fields"> + <OPTION VALUE="">(none) + <OPTION VALUE="setup,last_bill,bill,cancel">Setup date | Last bill date | Next bill date | Cancel date + </SELECT> + </TD> + </TR> + + <% include( '/elements/tr-select-cust-fields.html' ) %> + + </TABLE> + +<BR> +<INPUT TYPE="submit" VALUE="Get Report"> + +</FORM> + +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List packages'); #? + +my $title = 'Broadband Service Report'; +my $routernum = [ $cgi->param('routernum') || '' ]; +$routernum = join(',', @$routernum); + +#false laziness w/report_cust_pkg.html +my $custnum = ''; +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 .= ': '. $cust_main->name; +} + +# exclude one-time charges, disabled packages, and packages with no +# broadband services +my %pkg_search = ( + 'extra_sql' => " +WHERE freq != '0' AND disabled IS NULL AND 0 < ( + SELECT COUNT(*) FROM part_svc JOIN pkg_svc USING ( svcpart ) + WHERE pkg_svc.pkgpart = part_pkg.pkgpart AND part_svc.svcdb = 'svc_broadband' + AND pkg_svc.quantity > 0 +)", +); + +</%init> +<%once> + +</%once> diff --git a/httemplate/search/rt_transaction.html b/httemplate/search/rt_transaction.html index 8dda4ba..fb828af 100644 --- a/httemplate/search/rt_transaction.html +++ b/httemplate/search/rt_transaction.html @@ -3,7 +3,7 @@ 'name_singular' => 'transaction', 'query' => $query, 'count_query' => $count_query, - 'count_addl' => [ $format_seconds_sub, $format_seconds_sub, ], + 'count_addl' => [ $format_seconds_sub ],#$format_seconds_sub, ], 'header' => [ 'Ticket #', 'Ticket', 'Date', @@ -54,9 +54,9 @@ my $transactiontime = " "; my $join = 'JOIN Tickets ON Transactions.ObjectId = Tickets.Id '. - 'JOIN Users ON Transactions.Creator = Users.Id '. - 'LEFT JOIN acct_rt_transaction '. - ' ON Transactions.Id = acct_rt_transaction.transaction_id'; + 'JOIN Users ON Transactions.Creator = Users.Id '; #. +# 'LEFT JOIN acct_rt_transaction '. +# ' ON Transactions.Id = acct_rt_transaction.transaction_id'; my $where = " WHERE objecttype='RT::Ticket' AND ( ( Transactions.Type = 'Set' @@ -90,20 +90,28 @@ if ( $cgi->param('ticketid') =~ /^\s*(\d+)\s*$/ ) { } if ( $cgi->param('svcnum') =~ /^\s*(\d+)\s*$/ ) { - $where .= " AND acct_rt_transaction.svcnum = $1"; + $where .= " AND EXISTS( SELECT 1 FROM acct_rt_transaction WHERE acct_rt_transaction.transaction_id = Transactions.id AND svcnum = $1 )"; } my $query = { - 'select' => "Transactions.*, Tickets.Id AS ticketid, Tickets.Subject, Users.name as otaker, $transactiontime AS transaction_time, acct_rt_transaction.support", + 'select' => join(', ', + 'Transactions.*', + 'Tickets.Id AS ticketid', + 'Tickets.Subject', + 'Users.name AS otaker', + "$transactiontime AS transaction_time", + '( SELECT SUM(support) from acct_rt_transaction where transaction_id = Transactions.id ) AS support', + ), + 'table' => 'transactions', #Pg-ism #'table' => 'Transactions', - 'table' => 'transactions', 'addl_from' => $join, 'extra_sql' => $where, 'order by' => 'ORDER BY Created', }; my $count_query = - "SELECT COUNT(*), SUM($transactiontime), SUM(acct_rt_transaction.support) FROM Transactions $join $where"; + #"SELECT COUNT(*), SUM($transactiontime), SUM(acct_rt_transaction.support) FROM Transactions $join $where"; + "SELECT COUNT(*), SUM($transactiontime) FROM Transactions $join $where"; my $link = [ "${p}rt/Ticket/Display.html?id=", sub { shift->get('ticketid'); } ]; diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi index 1407d9e..c3ddd66 100755 --- a/httemplate/search/svc_acct.cgi +++ b/httemplate/search/svc_acct.cgi @@ -262,13 +262,13 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { } $cgi->param('username') =~ /^([\w\-\.\&]+)$/; #untaint username_text - my $username = $1; + my $username = lc($1); - push @username_sql, "username ILIKE '$username'" + push @username_sql, "LOWER(username) LIKE '$username'" if $username_type{'Exact'} || $username_type{'Fuzzy'}; - push @username_sql, "username ILIKE '\%$username\%'" + push @username_sql, "LOWER(username) LIKE '\%$username\%'" if $username_type{'Substring'} || $username_type{'All'}; diff --git a/httemplate/search/svc_broadband.cgi b/httemplate/search/svc_broadband.cgi index d0b1029..7026f52 100755 --- a/httemplate/search/svc_broadband.cgi +++ b/httemplate/search/svc_broadband.cgi @@ -1,8 +1,9 @@ <% include( 'elements/search.html', 'title' => 'Broadband Search Results', 'name' => 'broadband services', + 'html_init' => $html_init, 'query' => $sql_query, - 'count_query' => $count_query, + 'count_query' => $sql_query->{'count_query'}, 'redirect' => [ popurl(2). "view/svc_broadband.cgi?", 'svcnum' ], 'header' => [ '#', 'Service', @@ -43,66 +44,29 @@ %> <%init> -die "access denied" - unless $FS::CurrentUser::CurrentUser->access_right('List services'); +die "access denied" unless + $FS::CurrentUser::CurrentUser->access_right('List services'); my $conf = new FS::Conf; -my $orderby = 'ORDER BY svcnum'; -my %svc_broadband = (); -my @extra_sql = (); -if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { - - push @extra_sql, 'pkgnum IS NULL' - if $cgi->param('magic') eq 'unlinked'; - - if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { - my $sortby = $1; - $orderby = "ORDER BY $sortby"; +my %search_hash; +if ( $cgi->param('magic') eq 'unlinked' ) { + %search_hash = ( 'unlinked' => 1 ); +} +else { + foreach (qw(custnum agentnum svcpart)) { + $search_hash{$_} = $cgi->param($_) if $cgi->param($_); + } + foreach (qw(pkgpart routernum)) { + $search_hash{$_} = [ $cgi->param($_) ] if $cgi->param($_); } - -} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { - push @extra_sql, "svcpart = $1"; -} elsif ( $cgi->param('ip_addr') =~ /^(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})$/ ) { - push @extra_sql, "ip_addr = '$1'"; } -my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. - ' LEFT JOIN part_svc USING ( svcpart ) '. - ' LEFT JOIN cust_pkg USING ( pkgnum ) '. - ' LEFT JOIN cust_main USING ( custnum ) '; - -push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( - 'null_right' => 'View/link unlinked services' - ); - -my $extra_sql = ''; -if ( @extra_sql ) { - $extra_sql = ( keys(%svc_broadband) ? ' AND ' : ' WHERE ' ). - join(' AND ', @extra_sql ); +if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { + $search_hash{'order_by'} = $1; } -my $count_query = "SELECT COUNT(*) FROM svc_broadband $addl_from "; -#if ( keys %svc_broadband ) { -# $count_query .= ' WHERE '. -# join(' AND ', map "$_ = ". dbh->quote($svc_broadband{$_}), -# keys %svc_broadband -# ); -#} -$count_query .= $extra_sql; - -my $sql_query = { - 'table' => 'svc_broadband', - 'hashref' => {}, #\%svc_broadband, - 'select' => join(', ', - 'svc_broadband.*', - 'part_svc.svc', - 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'extra_sql' => $extra_sql, - 'addl_from' => $addl_from, -}; +my $sql_query = FS::svc_broadband->search(\%search_hash); my %routerbyblock = (); foreach my $router (qsearch('router', {})) { @@ -120,4 +84,9 @@ my $link_router = sub { my $routernum = $routerbyblock{shift->blocknum}->routern my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ]; +my $html_init = include('/elements/email-link.html', + 'search_hash' => \%search_hash, + 'table' => 'svc_broadband' + ); + </%init> |