X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_main.cgi;h=1bd5fb60a26506bbce33909771dab223ec12a002;hp=4d3ec35642705accfed8f8ff16d272aa2714bba9;hb=141003fc6cab41daf1a4a697a610c953ec42906e;hpb=f91771606ce8a2bb4f2fa44e7bf54c9b7c945db6 diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi index 4d3ec3564..1bd5fb60a 100755 --- a/httemplate/search/cust_main.cgi +++ b/httemplate/search/cust_main.cgi @@ -1,27 +1,7 @@ <% -# - -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 vars qw( $conf %all_pkgs $cgi @cust_main $sortby - $orderby $maxrecords $limit $offset ); -use CGI; -use CGI::Carp qw(fatalsToBrowser); -use IO::Handle; -use String::Approx qw(amatch); -use FS::UID qw(dbh cgisuidsetup); -use FS::Conf; -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; - -$cgi = new CGI; -cgisuidsetup($cgi); - -$conf = new FS::Conf; -$maxrecords = $conf->config('maxsearchrecordsperpage'); + +my $conf = new FS::Conf; +my $maxrecords = $conf->config('maxsearchrecordsperpage'); #my $cache; @@ -59,63 +39,138 @@ $maxrecords = $conf->config('maxsearchrecordsperpage'); #) using (custnum) #END -$orderby = ''; #removeme - -$limit = ''; +my $limit = ''; $limit .= "LIMIT $maxrecords" if $maxrecords; -$offset = $cgi->param('offset') || 0; +my $offset = $cgi->param('offset') || 0; $limit .= " OFFSET $offset" if $offset; -my $total; - -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 last'; - } elsif ( $query eq 'company' ) { - $sortby=\*company_sort; - $orderby = 'ORDER BY company'; +my $total = 0; + +my(@cust_main, $sortby, $orderby); +if ( $cgi->param('browse') + || $cgi->param('otaker_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; + } else { + die "unknown query..."; + } } my $ncancelled = ''; + if ( driver_name eq 'mysql' ) { + + my $query = "CREATE TEMPORARY TABLE temp1_$$ TYPE=MYISAM + SELECT cust_pkg.custnum,COUNT(*) as count + FROM cust_pkg,cust_main + WHERE cust_pkg.custnum = cust_main.custnum + AND ( cust_pkg.cancel IS NULL + OR cust_pkg.cancel = 0 ) + GROUP BY cust_pkg.custnum"; + my $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; + $sth->execute or die "Error executing \"$query\": ". $sth->errstr; + $query = "CREATE TEMPORARY TABLE temp2_$$ TYPE=MYISAM + SELECT cust_pkg.custnum,COUNT(*) as count + FROM cust_pkg,cust_main + WHERE cust_pkg.custnum = cust_main.custnum + GROUP BY cust_pkg.custnum"; + my $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; + $sth->execute or die "Error executing \"$query\": ". $sth->errstr; + } + if ( $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me || ( $conf->exists('hidecancelledcustomers') && ! $cgi->param('showcancelledcustomers') ) ) { #grep { $_->ncancelled_pkgs || ! $_->all_pkgs } - #needed for MySQL??? OR cust_pkg.cancel = \"\" - $ncancelled = " - WHERE 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 - ) - "; + if ( driver_name eq 'mysql' ) { + $ncancelled = " + temp1_$$.custnum = cust_main.custnum + AND temp2_$$.custnum = cust_main.custnum + AND (temp1_$$.count > 0 + OR temp2_$$.count = 0 ) + "; + } else { + $ncancelled = " + 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 + ) + "; + } + } - my $statement = "SELECT COUNT(*) FROM cust_main $ncancelled"; - my $sth = dbh->prepare($statement) - or die dbh->errstr. " doing $statement"; + #EWWWWWW + my $qual = join(' AND ', + map { "$_ = ". dbh->quote($search{$_}) } keys %search ); + + if ( $ncancelled ) { + $qual .= ' AND ' if $qual; + $qual .= $ncancelled; + } + + $qual = " WHERE $qual" if $qual; + my $statement; + if ( driver_name eq 'mysql' ) { + $statement = "SELECT COUNT(*) FROM cust_main"; + $statement .= ", temp1_$$, temp2_$$ $qual" if $qual; + } else { + $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]; - my @just_cust_main = qsearch('cust_main',{}, '', - "$ncancelled $orderby $limit" - ); + if ( $ncancelled ) { + if ( %search ) { + $ncancelled = " AND $ncancelled"; + } else { + $ncancelled = " WHERE $ncancelled"; + } + } + my @just_cust_main; + if ( driver_name eq 'mysql' ) { + @just_cust_main = qsearch('cust_main', \%search, 'cust_main.*', + ",temp1_$$,temp2_$$ $ncancelled $orderby $limit"); + } else { + @just_cust_main = qsearch('cust_main', \%search, '', + "$ncancelled $orderby $limit" ); + } + if ( driver_name eq 'mysql' ) { + $query = "DROP TABLE temp1_$$,temp2_$$;"; + my $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; + $sth->execute or die "Error executing \"$query\": ". $sth->errstr; + } @cust_main = @just_cust_main; # foreach my $cust_main ( @just_cust_main ) { @@ -135,17 +190,36 @@ if ( $cgi->param('browse') ) { } 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, @{&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('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; } +my %all_pkgs; if ( $conf->exists('hidecancelledpackages' ) ) { %all_pkgs = map { $_->custnum => [ $_->ncancelled_pkgs ] } @cust_main; } else { @@ -159,10 +233,16 @@ if ( scalar(@cust_main) == 1 && ! $cgi->param('referral_custnum') ) { } else { print $cgi->redirect(popurl(2). "view/cust_main.cgi?". $cust_main[0]->custnum); } - exit; + #exit; } elsif ( scalar(@cust_main) == 0 ) { +%> + +<% eidiot "No matching customers found!\n"; } else { +%> + +<% $total ||= scalar(@cust_main); print header("Customer Search Results",menubar( @@ -292,11 +372,14 @@ END } 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)=( @@ -304,9 +387,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 } @@ -348,19 +434,32 @@ END # 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"; + my $custnum = $1; + + [ qsearchs('cust_main', { 'custnum' => $custnum } ) ]; +} + sub cardsearch { my($card)=$cgi->param('card'); @@ -368,8 +467,7 @@ 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'}) ]; } sub referralsearch { @@ -385,12 +483,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{$_}++; } @@ -399,23 +497,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'); + } + + if ( $last_type{'Substring'} || $last_type{'All'} ) { - push @cust_main, qsearch('cust_main',{'last'=>$last}); + push @cust_main, qsearch( 'cust_main', + { 'last' => { 'op' => 'ILIKE', + 'value' => "%$last%" } } ); - push @cust_main, qsearch('cust_main',{'ship_last'=>$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{$_}++; } @@ -431,12 +543,14 @@ sub lastsearch { } } - $sortby=\*last_sort; + + \@cust_main; } sub companysearch { my(%company_type); + my @cust_main; foreach ( $cgi->param('company_type') ) { $company_type{$_}++ }; @@ -445,23 +559,37 @@ sub companysearch { 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',{'company'=>$company}); + push @cust_main, qsearch( 'cust_main', + { 'ship_company' => { 'op' => 'ILIKE', + 'value' => $company } } ) + if defined dbdef->table('cust_main')->column('ship_last'); + } - push @cust_main, qsearch('cust_main',{'ship_company'=>$company}) + if ( $company_type{'Substring'} || $company_type{'All'} ) { + + 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'); - } 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{$_}++; } @@ -477,7 +605,33 @@ sub companysearch { } } - $sortby=\*company_sort; + \@cust_main; +} + +sub phonesearch { + my @cust_main; + + my $phone = $cgi->param('phone_text'); + + #false laziness with Record::ut_phonen, only works with US/CA numbers... + $phone =~ s/\D//g; + $phone =~ /^(\d{3})(\d{3})(\d{4})(\d*)$/ + or eidiot gettext('illegal_phone'). ": $phone"; + $phone = "$1-$2-$3"; + $phone .= " x$4" if $4; + + 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; } + %>