X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fsvc_acct.cgi;h=0954bf65224993a8db78e639521cbea944ced5b4;hp=dba5ac302e42dac9e3ce0a014fdceff46c36c6aa;hb=71eb3c33da045ffdc963b5ad8111af8a8bef878d;hpb=8a8c9386cbd3383b0134aae8e32b5995f8886fb2 diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi index dba5ac302..0954bf652 100755 --- a/httemplate/search/svc_acct.cgi +++ b/httemplate/search/svc_acct.cgi @@ -1,77 +1,144 @@ <% -# - -use strict; -use vars qw( $cgi @svc_acct $sortby $query ); -use CGI; -use CGI::Carp qw(fatalsToBrowser); -use FS::UID qw(cgisuidsetup); -use FS::Record qw(qsearch qsearchs); -use FS::CGI qw(header eidiot popurl table); -use FS::svc_acct; -use FS::cust_main; - -$cgi = new CGI; -&cgisuidsetup($cgi); - -($query)=$cgi->keywords; + +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 -#this tree is a little bit redundant + +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; - @svc_acct=qsearch('svc_acct',{}); + $orderby = "ORDER BY ${tblname}svcnum"; } elsif ( $query eq 'username' ) { $sortby=\*username_sort; - @svc_acct=qsearch('svc_acct',{}); + $orderby = "ORDER BY ${tblname}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' ) { + $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; - @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 ${tblname}username"; } else { $sortby=\*uid_sort; - &usernamesearch; + @svc_acct = @{&usernamesearch}; +} + + +if ( $query eq 'svcnum' + || $query eq 'username' + || $query eq 'uid' + || $query eq 'popnum' + ) { + + 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; + #exit; } elsif ( scalar(@svc_acct) == 0 ) { #error - eidiot("Account not found"); +%> + +<% + idiot("Account not found"); } else { - my($total)=scalar(@svc_acct); - print $cgi->header( '-expires' => 'now' ), - header("Account Search Results",''), - "$total matching accounts found", +%> + +<% + $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(), < - Service # + # Username Domain UID Service - Customer # - Contact name - Company - + 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); @@ -82,8 +149,16 @@ END 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 $svc_domain = qsearchs('svc_domain', { 'svcnum' => $svc_acct->domsvc }) - or die "No svc_domain record for domsvc ". $cust_svc->domsvc; + + 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 }) @@ -91,10 +166,9 @@ END $cust_main = qsearchs('cust_main', { 'custnum' => $cust_pkg->custnum }) or die "No cust_main record for custnum ". $cust_pkg->custnum; } - my($svcnum,$username,$domain,$uid,$svc,$custnum,$last,$first,$company)=( + my($svcnum, $username, $uid, $svc, $custnum, $last, $first, $company) = ( $svc_acct->svcnum, $svc_acct->getfield('username'), - $svc_domain->getfield('domain'), $svc_acct->getfield('uid'), $part_svc->svc, $cust_svc->pkgnum ? $cust_main->custnum : '', @@ -106,8 +180,24 @@ END ? "$custnum" : "(unlinked)" ; - my($pname) = $custnum ? "$last, $first" : ''; + 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 @@ -118,17 +208,19 @@ END $pcustnum $pname $pcompany - END + if ( defined dbdef->table('cust_main')->column('ship_last') ) { + print <$pship_name + $pship_company +END + } + print ""; } - print < - - - -END + print "$pager
". + ''; } @@ -146,12 +238,51 @@ sub uid_sort { sub usernamesearch { - $cgi->param('username') =~ /^([\w\d\-]+)$/; #untaint username_text - my($username)=$1; + my @svc_acct; - @svc_acct=qsearch('svc_acct',{'username'=>$username}); + 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; + +} %>