X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fsvc_acct.cgi;h=592aa150ae447b844919179f4ce3cf9cbd2d63aa;hp=cd86031c57e287ff9485149a9a3f4f051278d4d7;hb=633c48448d9468690b7ad77eb6ff7c660a286658;hpb=3ca42bd26b6724fa389c9a3e08d091e65954b616 diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi index cd86031c5..592aa150a 100755 --- a/httemplate/search/svc_acct.cgi +++ b/httemplate/search/svc_acct.cgi @@ -1,288 +1,153 @@ -<% - -my $conf = new FS::Conf; -my $maxrecords = $conf->config('maxsearchrecordsperpage'); - -my $orderby = ''; #removeme - -my $limit = ''; -$limit .= "LIMIT $maxrecords" if $maxrecords; - -my $offset = $cgi->param('offset') || 0; -$limit .= " OFFSET $offset" if $offset; - -my $total; - -my($query)=$cgi->keywords; -$query ||= ''; #to avoid use of unitialized value errors - -my $unlinked = ''; -if ( $query =~ /^UN_(.*)$/ ) { - $query = $1; - my $empty = driver_name eq 'Pg' ? qq('') : qq(""); - if ( driver_name eq 'mysql' ) { - $unlinked = "LEFT JOIN cust_svc ON cust_svc.svcnum = svc_acct.svcnum - WHERE cust_svc.pkgnum IS NULL - OR cust_svc.pkgnum = 0 - OR cust_svc.pkgnum = $empty"; - } else { - $unlinked = " - WHERE 0 < - ( SELECT count(*) FROM cust_svc - WHERE cust_svc.svcnum = svc_acct.svcnum - AND ( pkgnum IS NULL OR pkgnum = 0 ) - ) - "; - } -} - -my $tblname = driver_name eq 'mysql' ? 'svc_acct.' : ''; -my(@svc_acct, $sortby); -if ( $query eq 'svcnum' ) { - $sortby=\*svcnum_sort; - $orderby = "ORDER BY ${tblname}svcnum"; -} elsif ( $query eq 'username' ) { - $sortby=\*username_sort; - $orderby = "ORDER BY ${tblname}username"; -} elsif ( $query eq 'uid' ) { - $sortby=\*uid_sort; - $orderby = ( $unlinked ? 'AND' : 'WHERE' ). - " ${tblname}uid IS NOT NULL ORDER BY ${tblname}uid"; -} elsif ( $query =~ /^popnum=(\d+)$/ ) { - my $popnum = $1; - $unlinked .= ( $unlinked ? 'AND' : 'WHERE' ). - " popnum = $popnum"; - $sortby=\*username_sort; - $orderby = "ORDER BY ${tblname}username"; -} else { - $sortby=\*uid_sort; - @svc_acct = @{&usernamesearch}; -} - - -if ( $query eq 'svcnum' - || $query eq 'username' - || $query eq 'uid' - || $query =~ /^popnum=(\d+)$/ - ) { - - my $statement = "SELECT COUNT(*) FROM svc_acct $unlinked"; - my $sth = dbh->prepare($statement) - or die dbh->errstr. " doing $statement"; - $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; - - $total = $sth->fetchrow_arrayref->[0]; - - @svc_acct = qsearch('svc_acct', {}, '', "$unlinked $orderby $limit"); - -} - -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 { -%> - -<% - $total ||= scalar(@svc_acct); - - #begin pager - my $pager = ''; - if ( $total != scalar(@svc_acct) && $maxrecords ) { - unless ( $offset == 0 ) { - $cgi->param('offset', $offset - $maxrecords); - $pager .= 'Previous '; - } - my $poff; - my $page; - for ( $poff = 0; $poff < $total; $poff += $maxrecords ) { - $page++; - if ( $offset == $poff ) { - $pager .= qq!$page !; - } else { - $cgi->param('offset', $poff); - $pager .= qq!$page !; - } - } - unless ( $offset + $maxrecords > $total ) { - $cgi->param('offset', $offset + $maxrecords); - $pager .= 'Next '; - } - } - #end pager - - print header("Account Search Results",menubar('Main Menu'=>popurl(2))), - "$total matching accounts found

$pager", - &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 { - die "No svc_domain.svcnum record for svc_acct.domsvc: ". - $svc_acct->domsvc; - } - 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 ""; - - } - - print "$pager
". - ''; - -} - -sub svcnum_sort { - $a->getfield('svcnum') <=> $b->getfield('svcnum'); -} - -sub username_sort { - $a->getfield('username') cmp $b->getfield('username'); -} - -sub uid_sort { - $a->getfield('uid') <=> $b->getfield('uid'); -} - -sub usernamesearch { - - my @svc_acct; - - my %username_type; - foreach ( $cgi->param('username_type') ) { - $username_type{$_}++; - } - - $cgi->param('username') =~ /^([\w\-\.\&]+)$/; #untaint username_text - my $username = $1; - - if ( $username_type{'Exact'} || $username_type{'Fuzzy'} ) { - push @svc_acct, qsearch( 'svc_acct', - { 'username' => { 'op' => 'ILIKE', - 'value' => $username } } ); - } - - if ( $username_type{'Substring'} || $username_type{'All'} ) { - push @svc_acct, qsearch( 'svc_acct', - { 'username' => { 'op' => 'ILIKE', - 'value' => "%$username%" } } ); - } - - if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { - &FS::svc_acct::check_and_rebuild_fuzzyfiles; - my $all_username = &FS::svc_acct::all_username; - - my %username; - if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { - foreach ( amatch($username, [ qw(i) ], @$all_username) ) { - $username{$_}++; - } - } - - #if ($username_type{'Sound-alike'}) { - #} - - foreach ( keys %username ) { - push @svc_acct, qsearch('svc_acct',{'username'=>$_}); - } - - } - - #[ qsearch('svc_acct',{'username'=>$username}) ]; - \@svc_acct; - -} - +%my @extra_sql = (); +% +% if ( $cgi->param('domain') ) { +% my $svc_domain = +% qsearchs('svc_domain', { 'domain' => $cgi->param('domain') } ); +% unless ( $svc_domain ) { +% #it would be nice if this looked more like the other "not found" +% #errors, but this will do for now. +% eidiot "Domain ". $cgi->param('domain'). " not found at all"; +% } else { +% push @extra_sql, 'domsvc = '. $svc_domain->svcnum; +% } +% } +% +%my $orderby = 'ORDER BY svcnum'; +%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; +% $sortby = "LOWER($sortby)" +% if $sortby eq 'username'; +% push @extra_sql, "$sortby IS NOT NULL" +% if $sortby eq 'uid'; +% $orderby = "ORDER BY $sortby"; +% } +% +%} elsif ( $cgi->param('popnum') =~ /^(\d+)$/ ) { +% push @extra_sql, "popnum = $1"; +% $orderby = "ORDER BY LOWER(username)"; +%} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { +% push @extra_sql, "svcpart = $1"; +% $orderby = "ORDER BY uid"; +% #$orderby = "ORDER BY svcnum"; +%} else { +% $orderby = "ORDER BY uid"; +% +% my @username_sql; +% +% my %username_type; +% foreach ( $cgi->param('username_type') ) { +% $username_type{$_}++; +% } +% +% $cgi->param('username') =~ /^([\w\-\.\&]+)$/; #untaint username_text +% my $username = $1; +% +% push @username_sql, "username ILIKE '$username'" +% if $username_type{'Exact'} +% || $username_type{'Fuzzy'}; +% +% push @username_sql, "username ILIKE '\%$username\%'" +% if $username_type{'Substring'} +% || $username_type{'All'}; +% +% if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { +% &FS::svc_acct::check_and_rebuild_fuzzyfiles; +% my $all_username = &FS::svc_acct::all_username; +% +% my %username; +% if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { +% foreach ( amatch($username, [ qw(i) ], @$all_username) ) { +% $username{$_}++; +% } +% } +% +% #if ($username_type{'Sound-alike'}) { +% #} +% +% push @username_sql, "username = '$_'" +% foreach (keys %username); +% +% } +% +% push @extra_sql, '( '. join( ' OR ', @username_sql). ' )'; +% +%} +% +%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; +% +%my $extra_sql = +% scalar(@extra_sql) +% ? ' WHERE '. join(' AND ', @extra_sql ) +% : ''; +% +%my $count_query = "SELECT COUNT(*) FROM svc_acct $addl_from $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.*', +% 'part_svc.svc', +% 'cust_main.custnum', +% FS::UI::Web::cust_sql_fields(), +% ), +% 'extra_sql' => "$extra_sql $orderby", +% 'addl_from' => $addl_from, +%}; +% +%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' => [ '#', + 'Service', + 'Account', + 'UID', + FS::UI::Web::cust_header(), + ], + 'fields' => [ 'svcnum', + 'svc', + 'email', + 'uid', + \&FS::UI::Web::cust_fields, + ], + 'links' => [ $link, + $link, + $link, + $link, + ( map { $link_cust } + FS::UI::Web::cust_header() + ), + ], + ) %>