X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_main.cgi;h=632d68df9993640d0cf275a9f5c017a3aaa88422;hp=db33df7844f1296c54a30a5a2e7c7a6f36ac6e9b;hb=4d80033767fa916faa15709e69bafaf9a939a5a1;hpb=fd72d2af8120195f96826eb044e217dbfcaee1c7 diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi index db33df784..632d68df9 100755 --- a/httemplate/search/cust_main.cgi +++ b/httemplate/search/cust_main.cgi @@ -1,25 +1,9 @@ <% -# -use strict; -#use vars qw( $conf %ncancelled_pkgs %all_pkgs $cgi @cust_main $sortby ); -use vars qw( $conf %all_pkgs $cgi @cust_main $sortby ); -use CGI; -use CGI::Carp qw(fatalsToBrowser); -use IO::Handle; -use String::Approx qw(amatch); -use FS::UID qw(cgisuidsetup); -use FS::Record qw(qsearch qsearchs dbdef jsearch); -use FS::CGI qw(header menubar eidiot popurl table); -use FS::cust_main; -use FS::cust_svc; +my $conf = new FS::Conf; +my $maxrecords = $conf->config('maxsearchrecordsperpage'); -$cgi = new CGI; -cgisuidsetup($cgi); - -$conf = new FS::Conf; - -my $cache; +#my $cache; #my $monsterjoin = <param('browse') ) { - my $query = $cgi->param('browse'); - if ( $query eq 'custnum' ) { - $sortby=\*custnum_sort; -# @cust_main=qsearch('cust_main',{}); - ( $cache, @cust_main ) = - jsearch($monsterjoin, {}, '', '', 'cust_main', 'custnum' ); - } elsif ( $query eq 'last' ) { - $sortby=\*last_sort; -# @cust_main=qsearch('cust_main',{}); - ( $cache, @cust_main ) = - jsearch($monsterjoin, {}, '', '', 'cust_main', 'custnum' ); - } elsif ( $query eq 'company' ) { - $sortby=\*company_sort; -# @cust_main=qsearch('cust_main',{}); - ( $cache, @cust_main ) = - jsearch($monsterjoin, {}, '', '', 'cust_main', 'custnum' ); +my $limit = ''; +$limit .= "LIMIT $maxrecords" if $maxrecords; + +my $offset = $cgi->param('offset') || 0; +$limit .= " OFFSET $offset" if $offset; + +my $total = 0; + +my(@cust_main, $sortby, $orderby); +if ( $cgi->param('browse') + || $cgi->param('otaker_on') + || $cgi->param('agentnum_on') +) { + + my %search = (); + if ( $cgi->param('browse') ) { + my $query = $cgi->param('browse'); + if ( $query eq 'custnum' ) { + $sortby=\*custnum_sort; + $orderby = "ORDER BY custnum"; + } elsif ( $query eq 'last' ) { + $sortby=\*last_sort; + $orderby = "ORDER BY LOWER(last || ' ' || first)"; + } elsif ( $query eq 'company' ) { + $sortby=\*company_sort; + $orderby = "ORDER BY LOWER(company || ' ' || last || ' ' || first )"; + } else { + die "unknown browse field $query"; + } } else { - die "unknown browse field $query"; + $sortby = \*last_sort; #?? + $orderby = "ORDER BY LOWER(last || ' ' || first)"; #?? + if ( $cgi->param('otaker_on') ) { + $cgi->param('otaker') =~ /^(\w{1,32})$/ or eidiot "Illegal otaker\n"; + $search{otaker} = $1; + } elsif ( $cgi->param('agentnum_on') ) { + $cgi->param('agentnum') =~ /^(\d+)$/ or eidiot "Illegal agentnum\n"; + $search{agentnum} = $1; + } else { + die "unknown query..."; + } + } + + my @qual = (); + + my $ncancelled = ''; + + if ( $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me + || ( $conf->exists('hidecancelledcustomers') + && ! $cgi->param('showcancelledcustomers') ) + ) { + #grep { $_->ncancelled_pkgs || ! $_->all_pkgs } + push @qual, " + ( 0 < ( SELECT COUNT(*) FROM cust_pkg + WHERE cust_pkg.custnum = cust_main.custnum + AND ( cust_pkg.cancel IS NULL + OR cust_pkg.cancel = 0 + ) + ) + OR 0 = ( SELECT COUNT(*) FROM cust_pkg + WHERE cust_pkg.custnum = cust_main.custnum + ) + ) + "; + } + + push @qual, FS::cust_main->cancel_sql if $cgi->param('cancelled'); + push @qual, FS::cust_main->prospect_sql if $cgi->param('prospect'); + push @qual, FS::cust_main->active_sql if $cgi->param('active'); + push @qual, FS::cust_main->susp_sql if $cgi->param('suspended'); + + #EWWWWWW + my $qual = join(' AND ', + map { "$_ = ". dbh->quote($search{$_}) } keys %search ); + + my $addl_qual = join(' AND ', @qual); + + if ( $addl_qual ) { + $qual .= ' AND ' if $qual; + $qual .= $addl_qual; + } + + $qual = " WHERE $qual" if $qual; + my $statement = "SELECT COUNT(*) FROM cust_main $qual"; + my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement"; + $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; + + $total = $sth->fetchrow_arrayref->[0]; + + if ( $addl_qual ) { + if ( %search ) { + $addl_qual = " AND $addl_qual"; + } else { + $addl_qual = " WHERE $addl_qual"; + } } + + @cust_main = qsearch('cust_main', \%search, '', + "$addl_qual $orderby $limit" ); + +# foreach my $cust_main ( @just_cust_main ) { +# +# my @one_cust_main; +# $FS::Record::DEBUG=1; +# ( $cache, @one_cust_main ) = jsearch( +# "$monsterjoin", +# { 'custnum' => $cust_main->custnum }, +# '', +# '', +# 'cust_main', +# 'custnum', +# ); +# push @cust_main, @one_cust_main; +# } + } else { @cust_main=(); - &cardsearch if $cgi->param('card_on') && $cgi->param('card'); - &lastsearch if $cgi->param('last_on') && $cgi->param('last_text'); - &companysearch if $cgi->param('company_on') && $cgi->param('company_text'); - &referralsearch if $cgi->param('referral_custnum'); + $sortby = \*last_sort; + + push @cust_main, @{&custnumsearch} + if $cgi->param('custnum_on') && $cgi->param('custnum_text'); + push @cust_main, @{&cardsearch} + if $cgi->param('card_on') && $cgi->param('card'); + push @cust_main, @{&lastsearch} + if $cgi->param('last_on') && $cgi->param('last_text'); + push @cust_main, @{&companysearch} + if $cgi->param('company_on') && $cgi->param('company_text'); + push @cust_main, @{&address2search} + if $cgi->param('address2_on') && $cgi->param('address2_text'); + push @cust_main, @{&phonesearch} + if $cgi->param('phone_on') && $cgi->param('phone_text'); + push @cust_main, @{&referralsearch} + if $cgi->param('referral_custnum'); + + if ( $cgi->param('company_on') && $cgi->param('company_text') ) { + $sortby = \*company_sort; + push @cust_main, @{&companysearch}; + } + + @cust_main = grep { $_->ncancelled_pkgs || ! $_->all_pkgs } @cust_main + if ! $cgi->param('cancelled') + && ( + $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me + || ( $conf->exists('hidecancelledcustomers') + && ! $cgi->param('showcancelledcustomers') ) + ); + + my %saw = (); + @cust_main = grep { !$saw{$_->custnum}++ } @cust_main; } -@cust_main = grep { $_->ncancelled_pkgs || ! $_->all_pkgs } @cust_main - if $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me - || ( $conf->exists('hidecancelledcustomers') - && ! $cgi->param('showcancelledcustomers') ); +my %all_pkgs; if ( $conf->exists('hidecancelledpackages' ) ) { %all_pkgs = map { $_->custnum => [ $_->ncancelled_pkgs ] } @cust_main; } else { @@ -95,26 +199,69 @@ if ( $conf->exists('hidecancelledpackages' ) ) { #%all_pkgs = (); if ( scalar(@cust_main) == 1 && ! $cgi->param('referral_custnum') ) { - print $cgi->redirect(popurl(2). "view/cust_main.cgi?". $cust_main[0]->custnum); - exit; + if ( $cgi->param('quickpay') eq 'yes' ) { + print $cgi->redirect(popurl(2). "edit/cust_pay.cgi?quickpay=yes;custnum=". $cust_main[0]->custnum); + } else { + print $cgi->redirect(popurl(2). "view/cust_main.cgi?". $cust_main[0]->custnum); + } + #exit; } elsif ( scalar(@cust_main) == 0 ) { +%> + +<% eidiot "No matching customers found!\n"; } else { +%> + +<% - my $total = scalar(@cust_main); + $total ||= scalar(@cust_main); print header("Customer Search Results",menubar( 'Main Menu', popurl(2) )), "$total matching customers found "; - if ( $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me - || ( $conf->exists('hidecancelledcustomers') - && ! $cgi->param('showcancelledcustomers') - ) - ) { - $cgi->param('showcancelledcustomers', 1); - print qq!( show cancelled customers )!; - } else { - $cgi->param('showcancelledcustomers', 0); - print qq!( hide cancelled customers )!; + + #begin pager + my $pager = ''; + if ( $total != scalar(@cust_main) && $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 + + unless ( $cgi->param('cancelled') ) { + if ( $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me + || ( $conf->exists('hidecancelledcustomers') + && ! $cgi->param('showcancelledcustomers') + ) + ) { + $cgi->param('showcancelledcustomers', 1); + $cgi->param('offset', 0); + print qq!( show!; + } else { + $cgi->param('showcancelledcustomers', 0); + $cgi->param('offset', 0); + print qq!( hide!; + } + print ' cancelled customers )'; } if ( $cgi->param('referral_custnum') ) { $cgi->param('referral_custnum') =~ /^(\d+)$/ @@ -150,7 +297,8 @@ END ''. ''; } - print "
", &table(), <
". $pager. &table(). < (bill) name @@ -192,12 +340,20 @@ END } #my($rowspan) = scalar(@{$all_pkgs{$custnum}}); - my $view = $p. 'view/cust_main.cgi?'. $custnum; + my $view; + if ( defined $cgi->param('quickpay') && $cgi->param('quickpay') eq 'yes' ) { + $view = $p. 'edit/cust_pay.cgi?quickpay=yes;custnum='. $custnum; + } else { + $view = $p. 'view/cust_main.cgi?'. $custnum; + } + my $pcompany = $company + ? qq!$company! + : ' '; print < $custnum $last, $first - $company + $pcompany END if ( defined dbdef->table('cust_main')->column('ship_last') ) { my($ship_last,$ship_first,$ship_company)=( @@ -205,9 +361,12 @@ END $cust_main->ship_last ? $cust_main->ship_first : $cust_main->first, $cust_main->ship_last ? $cust_main->ship_company : $cust_main->company, ); -print <$ship_company! + : ' '; + print <$ship_last, $ship_first - $ship_company + $pship_company END } @@ -219,7 +378,7 @@ END my $pkg = $part_pkg->pkg; my $comment = $part_pkg->comment; - my $pkgview = $p. 'view/cust_pkg.cgi?'. $pkgnum; + my $pkgview = "${p}view/cust_main.cgi?$custnum#cust_pkg$pkgnum"; my @cust_svc = @{shift @lol_cust_svc}; #my(@cust_svc) = qsearch( 'cust_svc', { 'pkgnum' => $_->pkgnum } ); my $rowspan = scalar(@cust_svc) || 1; @@ -240,30 +399,41 @@ END print ""; } - print < - - -END + print "$pager"; } +#undef $cache; #does this help? + # sub last_sort { - $a->getfield('last') cmp $b->getfield('last'); + lc($a->getfield('last')) cmp lc($b->getfield('last')) + || lc($a->first) cmp lc($b->first); } sub company_sort { return -1 if $a->company && ! $b->company; return 1 if ! $a->company && $b->company; - $a->getfield('company') cmp $b->getfield('company'); + lc($a->company) cmp lc($b->company) + || lc($a->getfield('last')) cmp lc($b->getfield('last')) + || lc($a->first) cmp lc($b->first);; } sub custnum_sort { $a->getfield('custnum') <=> $b->getfield('custnum'); } +sub custnumsearch { + + my $custnum = $cgi->param('custnum_text'); + $custnum =~ s/\D//g; + $custnum =~ /^(\d{1,23})$/ or eidiot "Illegal customer number\n"; + $custnum = $1; + + [ qsearchs('cust_main', { 'custnum' => $custnum } ) ]; +} + sub cardsearch { my($card)=$cgi->param('card'); @@ -271,8 +441,9 @@ sub cardsearch { $card =~ /^(\d{13,16})$/ or eidiot "Illegal card number\n"; my($payinfo)=$1; - push @cust_main, qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'CARD'}); - $sortby=\*last_sort; + [ qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'CARD'}), + qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'DCRD'}) + ]; } sub referralsearch { @@ -288,12 +459,12 @@ sub referralsearch { } else { $depth = 1; } - push @cust_main, $cust_main->referral_cust_main($depth); - $sortby=\*last_sort; + [ $cust_main->referral_cust_main($depth) ]; } sub lastsearch { my(%last_type); + my @cust_main; foreach ( $cgi->param('last_type') ) { $last_type{$_}++; } @@ -302,23 +473,37 @@ sub lastsearch { or eidiot "Illegal last name"; my($last)=$1; - if ( $last_type{'Exact'} - && ! $last_type{'Fuzzy'} - # && ! $last_type{'Sound-alike'} - ) { + if ( $last_type{'Exact'} || $last_type{'Fuzzy'} ) { + push @cust_main, qsearch( 'cust_main', + { 'last' => { 'op' => 'ILIKE', + 'value' => $last } } ); + + push @cust_main, qsearch( 'cust_main', + { 'ship_last' => { 'op' => 'ILIKE', + 'value' => $last } } ) + if defined dbdef->table('cust_main')->column('ship_last'); + } - push @cust_main, qsearch('cust_main',{'last'=>$last}); + if ( $last_type{'Substring'} || $last_type{'All'} ) { - push @cust_main, qsearch('cust_main',{'ship_last'=>$last}) + push @cust_main, qsearch( 'cust_main', + { 'last' => { 'op' => 'ILIKE', + 'value' => "%$last%" } } ); + + push @cust_main, qsearch( 'cust_main', + { 'ship_last' => { 'op' => 'ILIKE', + 'value' => "%$last%" } } ) if defined dbdef->table('cust_main')->column('ship_last'); - } else { + } + + if ( $last_type{'Fuzzy'} || $last_type{'All'} ) { &FS::cust_main::check_and_rebuild_fuzzyfiles; my $all_last = &FS::cust_main::all_last; my %last; - if ($last_type{'Fuzzy'}) { + if ( $last_type{'Fuzzy'} || $last_type{'All'} ) { foreach ( amatch($last, [ qw(i) ], @$all_last) ) { $last{$_}++; } @@ -334,37 +519,54 @@ sub lastsearch { } } - $sortby=\*last_sort; + + \@cust_main; } sub companysearch { my(%company_type); + my @cust_main; foreach ( $cgi->param('company_type') ) { $company_type{$_}++ }; - $cgi->param('company_text') =~ /^([\w \,\.\-\']*)$/ - or eidiot "Illegal company"; - my($company)=$1; + $cgi->param('company_text') =~ + /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]*)$/ + or eidiot "Illegal company"; + my $company = $1; - if ( $company_type{'Exact'} - && ! $company_type{'Fuzzy'} - # && ! $company_type{'Sound-alike'} - ) { + if ( $company_type{'Exact'} || $company_type{'Fuzzy'} ) { + push @cust_main, qsearch( 'cust_main', + { 'company' => { 'op' => 'ILIKE', + 'value' => $company } } ); + + push @cust_main, qsearch( 'cust_main', + { 'ship_company' => { 'op' => 'ILIKE', + 'value' => $company } } ) + if defined dbdef->table('cust_main')->column('ship_last'); + } + + if ( $company_type{'Substring'} || $company_type{'All'} ) { - push @cust_main, qsearch('cust_main',{'company'=>$company}); + push @cust_main, qsearch( 'cust_main', + { 'company' => { 'op' => 'ILIKE', + 'value' => "%$company%" } } ); - push @cust_main, qsearch('cust_main',{'ship_company'=>$company}) + push @cust_main, qsearch( 'cust_main', + { 'ship_company' => { 'op' => 'ILIKE', + 'value' => "%$company%" } }) if defined dbdef->table('cust_main')->column('ship_last'); - } else { + } + + if ( $company_type{'Fuzzy'} || $company_type{'All'} ) { &FS::cust_main::check_and_rebuild_fuzzyfiles; my $all_company = &FS::cust_main::all_company; my %company; - if ($company_type{'Fuzzy'}) { + if ( $company_type{'Fuzzy'} || $company_type{'All'} ) { foreach ( amatch($company, [ qw(i) ], @$all_company ) ) { $company{$_}++; } @@ -380,7 +582,59 @@ sub companysearch { } } - $sortby=\*company_sort; + \@cust_main; +} + +sub address2search { + my @cust_main; + + $cgi->param('address2_text') =~ + /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]*)$/ + or eidiot "Illegal address2"; + my $address2 = $1; + + push @cust_main, qsearch( 'cust_main', + { 'address2' => { 'op' => 'ILIKE', + 'value' => $address2 } } ); + push @cust_main, qsearch( 'cust_main', + { 'address2' => { 'op' => 'ILIKE', + 'value' => $address2 } } ) + if defined dbdef->table('cust_main')->column('ship_last'); + + \@cust_main; +} + +sub phonesearch { + my @cust_main; + + my $phone = $cgi->param('phone_text'); + + #(no longer really) false laziness with Record::ut_phonen + #only works with US/CA numbers... + $phone =~ s/\D//g; + if ( $phone =~ /^(\d{3})(\d{3})(\d{4})(\d*)$/ ) { + $phone = "$1-$2-$3"; + $phone .= " x$4" if $4; + } elsif ( $phone =~ /^(\d{3})(\d{4})$/ ) { + $phone = "$1-$2"; + } elsif ( $phone =~ /^(\d{3,4})$/ ) { + $phone = $1; + } else { + eidiot gettext('illegal_phone'). ": $phone"; + } + + my @fields = qw(daytime night fax); + push @fields, qw(ship_daytime ship_night ship_fax) + if defined dbdef->table('cust_main')->column('ship_last'); + + for my $field ( @fields ) { + push @cust_main, qsearch ( 'cust_main', + { $field => { 'op' => 'LIKE', + 'value' => "%$phone%" } } ); + } + + \@cust_main; } + %>