X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fsvc_acct.cgi;h=b14591958c263649bf91fba1fecaf125c755822f;hb=1a033848671cad2cbe7687b37fc718b3b2a68b83;hp=64085ea93ec10577dc9a0ffbfb94746131d3e1c5;hpb=e6b57805f6b3e76448ab9b6d280f2c53bc1410f3;p=freeside.git diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi index 64085ea93..b14591958 100755 --- a/httemplate/search/svc_acct.cgi +++ b/httemplate/search/svc_acct.cgi @@ -1,205 +1,140 @@ <% -# -use strict; -use vars qw( $cgi @svc_acct $sortby $query $mydomain ); -use CGI; -use CGI::Carp qw(fatalsToBrowser); -use FS::UID qw(cgisuidsetup); -use FS::Record qw(qsearch qsearchs dbdef); -use FS::CGI qw(header idiot popurl table); -use FS::svc_acct; -use FS::cust_main; +my $orderby = 'ORDER BY svcnum'; -$mydomain = ''; +my($query)=$cgi->keywords; +$query ||= ''; #to avoid use of unitialized value errors -$cgi = new CGI; -&cgisuidsetup($cgi); +my $cjoin = ''; +my @extra_sql = (); +if ( $query =~ /^UN_(.*)$/ ) { + $query = $1; + $cjoin = 'LEFT JOIN cust_svc USING ( svcnum )'; + push @extra_sql, 'pkgnum IS NULL'; +} -($query)=$cgi->keywords; -$query ||= ''; #to avoid use of unitialized value errors -#this tree is a little bit redundant if ( $query eq 'svcnum' ) { - $sortby=\*svcnum_sort; - @svc_acct=qsearch('svc_acct',{}); + #$orderby = "ORDER BY svcnum"; } elsif ( $query eq 'username' ) { - $sortby=\*username_sort; - @svc_acct=qsearch('svc_acct',{}); + $orderby = "ORDER BY LOWER(username)"; } elsif ( $query eq 'uid' ) { - $sortby=\*uid_sort; - @svc_acct=grep $_->uid ne '', qsearch('svc_acct',{}); -} elsif ( $query eq 'UN_svcnum' ) { - $sortby=\*svcnum_sort; - @svc_acct = grep qsearchs('cust_svc',{ - 'svcnum' => $_->svcnum, - 'pkgnum' => '', - }), qsearch('svc_acct',{}); -} elsif ( $query eq 'UN_username' ) { - $sortby=\*username_sort; - @svc_acct = grep qsearchs('cust_svc',{ - 'svcnum' => $_->svcnum, - 'pkgnum' => '', - }), qsearch('svc_acct',{}); -} elsif ( $query eq 'UN_uid' ) { - $sortby=\*uid_sort; - @svc_acct = grep qsearchs('cust_svc',{ - 'svcnum' => $_->svcnum, - 'pkgnum' => '', - }), qsearch('svc_acct',{}); + $orderby = "ORDER BY uid"; + push @extra_sql, "uid IS NOT NULL"; +} elsif ( $cgi->param('popnum') =~ /^(\d+)$/ ) { + push @extra_sql, "popnum = $1"; + $orderby = "ORDER BY LOWER(username)"; +} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { + $cjoin ||= 'LEFT JOIN cust_svc USING ( svcnum )'; + push @extra_sql, "svcpart = $1"; + $orderby = "ORDER BY uid"; + #$orderby = "ORDER BY svcnum"; } else { - $sortby=\*uid_sort; - &usernamesearch; -} + $orderby = "ORDER BY uid"; -if ( scalar(@svc_acct) == 1 ) { - my($svcnum)=$svc_acct[0]->svcnum; - print $cgi->redirect(popurl(2). "view/svc_acct.cgi?$svcnum"); #redirect - #exit; -} elsif ( scalar(@svc_acct) == 0 ) { #error - idiot("Account not found"); -} else { - my($total)=scalar(@svc_acct); - print $cgi->header( '-expires' => 'now' ), - header("Account Search Results",''), - "$total matching accounts found", - &table(), < - # - Username - Domain - UID - Service - Cust# - (bill) name - company -END - if ( defined dbdef->table('cust_main')->column('ship_last') ) { - print <(service) name - company -END - } - print ""; - - my(%saw,$svc_acct); - my $p = popurl(2); - foreach $svc_acct ( - sort $sortby grep(!$saw{$_->svcnum}++, @svc_acct) - ) { - my $cust_svc = qsearchs('cust_svc', { 'svcnum' => $svc_acct->svcnum }) - or die "No cust_svc record for svcnum ". $svc_acct->svcnum; - my $part_svc = qsearchs('part_svc', { 'svcpart' => $cust_svc->svcpart }) - or die "No part_svc record for svcpart ". $cust_svc->svcpart; - - my $domain; - my $svc_domain = qsearchs('svc_domain', { 'svcnum' => $svc_acct->domsvc }); - if ( $svc_domain ) { - $domain = "svcnum. - "\">". $svc_domain->domain. ""; - } else { - unless ( $mydomain ) { - my $conf = new FS::Conf; - unless ( $mydomain = $conf->config('domain') ) { - die "No legacy domain config file and no svc_domain.svcnum record ". - "for svc_acct.domsvc: ". $cust_svc->domsvc; - } - } - $domain = "$mydomain*"; - } - my($cust_pkg,$cust_main); - if ( $cust_svc->pkgnum ) { - $cust_pkg = qsearchs('cust_pkg', { 'pkgnum' => $cust_svc->pkgnum }) - or die "No cust_pkg record for pkgnum ". $cust_svc->pkgnum; - $cust_main = qsearchs('cust_main', { 'custnum' => $cust_pkg->custnum }) - or die "No cust_main record for custnum ". $cust_pkg->custnum; - } - my($svcnum, $username, $uid, $svc, $custnum, $last, $first, $company) = ( - $svc_acct->svcnum, - $svc_acct->getfield('username'), - $svc_acct->getfield('uid'), - $part_svc->svc, - $cust_svc->pkgnum ? $cust_main->custnum : '', - $cust_svc->pkgnum ? $cust_main->getfield('last') : '', - $cust_svc->pkgnum ? $cust_main->getfield('first') : '', - $cust_svc->pkgnum ? $cust_main->company : '', - ); - my($pcustnum) = $custnum - ? "$custnum" - : "(unlinked)" - ; - my $pname = $custnum ? "$last, $first" : ''; - my $pcompany = $custnum ? "$company" : ''; - my($pship_name, $pship_company); - if ( defined dbdef->table('cust_main')->column('ship_last') ) { - my($ship_last, $ship_first, $ship_company) = ( - $cust_svc->pkgnum ? ( $cust_main->ship_last || $last ) : '', - $cust_svc->pkgnum ? ( $cust_main->ship_last - ? $cust_main->ship_first - : $first - ) : '', - $cust_svc->pkgnum ? ( $cust_main->ship_last - ? $cust_main->ship_company - : $company - ) : '', - ); - $pship_name = $custnum ? "$ship_last, $ship_first" : ''; - $pship_company = $custnum ? "$ship_company" : ''; - } - print < - $svcnum - $username - $domain - $uid - $svc - $pcustnum - $pname - $pcompany -END - if ( defined dbdef->table('cust_main')->column('ship_last') ) { - print <$pship_name - $pship_company -END - } - print ""; + my @username_sql; - } - - print ''; - - if ( $mydomain ) { - print "
* The $mydomain domain ". - "is contained in your legacy domain ". - "configuration file. ". - "You should run the bin/fs-migrate-svc_acct_sm script ". - "to create a proper svc_domain record for this domain." + my %username_type; + foreach ( $cgi->param('username_type') ) { + $username_type{$_}++; } - print ''; + $cgi->param('username') =~ /^([\w\-\.\&]+)$/; #untaint username_text + my $username = $1; -} + push @username_sql, "username ILIKE '$username'" + if $username_type{'Exact'} + || $username_type{'Fuzzy'}; -sub svcnum_sort { - $a->getfield('svcnum') <=> $b->getfield('svcnum'); -} + push @username_sql, "username ILIKE '\%$username\%'" + if $username_type{'Substring'} + || $username_type{'All'}; -sub username_sort { - $a->getfield('username') cmp $b->getfield('username'); -} + if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { + &FS::svc_acct::check_and_rebuild_fuzzyfiles; + my $all_username = &FS::svc_acct::all_username; -sub uid_sort { - $a->getfield('uid') <=> $b->getfield('uid'); -} + my %username; + if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { + foreach ( amatch($username, [ qw(i) ], @$all_username) ) { + $username{$_}++; + } + } -sub usernamesearch { + #if ($username_type{'Sound-alike'}) { + #} - $cgi->param('username') =~ /^([\w\d\-]+)$/; #untaint username_text - my($username)=$1; + push @username_sql, "username = '$_'" + foreach (keys %username); - @svc_acct=qsearch('svc_acct',{'username'=>$username}); + } -} + push @extra_sql, '( '. join( ' OR ', @username_sql). ' )'; +} +my $extra_sql = + scalar(@extra_sql) + ? ' WHERE '. join(' AND ', @extra_sql ) + : ''; + +my $count_query = "SELECT COUNT(*) FROM svc_acct $cjoin $extra_sql"; +#if ( keys %svc_acct ) { +# $count_query .= ' WHERE '. +# join(' AND ', map "$_ = ". dbh->quote($svc_acct{$_}), +# keys %svc_acct +# ); +#} + +my $sql_query = { + 'table' => 'svc_acct', + 'hashref' => {}, # \%svc_acct, + 'select' => join(', ', + 'svc_acct.*', + 'cust_main.custnum', + FS::UI::Web::cust_sql_fields(), + ), + 'extra_sql' => "$extra_sql $orderby", + '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 ) ', +}; + +my $link = [ "${p}view/svc_acct.cgi?", 'svcnum' ]; +my $link_cust = sub { + my $svc_acct = shift; + if ( $svc_acct->custnum ) { + [ "${p}view/cust_main.cgi?", 'custnum' ]; + } else { + ''; + } +}; + +%><%= include( 'elements/search.html', + 'title' => 'Account Search Results', + 'name' => 'accounts', + 'query' => $sql_query, + 'count_query' => $count_query, + 'redirect' => $link, + 'header' => [ '#', + 'Account', + 'UID', + 'Service', + FS::UI::Web::cust_header(), + ], + 'fields' => [ 'svcnum', + 'email', + 'uid', + 'svc', + \&FS::UI::Web::cust_fields, + ], + 'links' => [ $link, + $link, + $link, + '', + ( map { $link_cust } + FS::UI::Web::cust_header() + ), + ], + ) %>