X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_main.cgi;h=50d367e1edb028ef0d96d95d76e98ee0459ad968;hp=f21dd7ed1bdc3c4e379b84063c229639fd0440fc;hb=baa3c17313351ba4330686a664d3843365e31e37;hpb=1fd6d8cf5d7854860ef4fd10ed89828e0c04ec39 diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi index f21dd7ed1..50d367e1e 100755 --- a/httemplate/search/cust_main.cgi +++ b/httemplate/search/cust_main.cgi @@ -45,56 +45,155 @@ $limit .= "LIMIT $maxrecords" if $maxrecords; my $offset = $cgi->param('offset') || 0; $limit .= " OFFSET $offset" if $offset; -my $total; +my $total = 0; my(@cust_main, $sortby, $orderby); -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'; +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 $ncancelled = ''; + if ( driver_name eq 'mysql' ) { + + my $sql = "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($sql) or die dbh->errstr. " preparing $sql"; + $sth->execute or die "Error executing \"$sql\": ". $sth->errstr; + $sql = "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"; + $sth = dbh->prepare($sql) or die dbh->errstr. " preparing $sql"; + $sth->execute or die "Error executing \"$sql\": ". $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 + 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 $cancelled = ''; + if ( $cgi->param('cancelled') ) { + $cancelled = " + 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 + ) + AND 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 ( $cancelled ) { + $qual .= ' AND ' if $qual; + $qual .= $cancelled; + } elsif ( $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" - ); + my $rqual = $cancelled || $ncancelled; + if ( $rqual ) { + if ( %search ) { + $rqual = " AND $rqual"; + } else { + $rqual = " WHERE $rqual"; + } + } + my @just_cust_main; + if ( driver_name eq 'mysql' ) { + @just_cust_main = qsearch('cust_main', \%search, 'cust_main.*', + ",temp1_$$,temp2_$$ $rqual $orderby $limit"); + } else { + @just_cust_main = qsearch('cust_main', \%search, '', + "$rqual $orderby $limit" ); + } + if ( driver_name eq 'mysql' ) { + my $sql = "DROP TABLE temp1_$$,temp2_$$;"; + my $sth = dbh->prepare($sql) or die dbh->errstr. " preparing $sql"; + $sth->execute or die "Error executing \"$sql\": ". $sth->errstr; + } @cust_main = @just_cust_main; # foreach my $cust_main ( @just_cust_main ) { @@ -116,12 +215,18 @@ if ( $cgi->param('browse') ) { @cust_main=(); $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'); @@ -131,9 +236,15 @@ if ( $cgi->param('browse') ) { } @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') ); + 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; } my %all_pkgs; @@ -150,10 +261,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( @@ -186,19 +303,22 @@ if ( scalar(@cust_main) == 1 && ! $cgi->param('referral_custnum') ) { } } #end pager - - 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 cancelled customers )!; - } else { - $cgi->param('showcancelledcustomers', 0); - $cgi->param('offset', 0); - print qq!( hide cancelled customers )!; + + 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+)$/ @@ -283,11 +403,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)=( @@ -295,9 +418,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 } @@ -339,19 +465,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"; + $custnum = $1; + + [ qsearchs('cust_main', { 'custnum' => $custnum } ) ]; +} + sub cardsearch { my($card)=$cgi->param('card'); @@ -359,7 +498,9 @@ sub cardsearch { $card =~ /^(\d{13,16})$/ or eidiot "Illegal card number\n"; my($payinfo)=$1; - [ qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'CARD'}) ]; + [ qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'CARD'}), + qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'DCRD'}) + ]; } sub referralsearch { @@ -389,24 +530,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',{'last'=>$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',{'ship_last'=>$last}) + if ( $last_type{'Substring'} || $last_type{'All'} ) { + + 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'} ) { + } + + 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{$_}++; } @@ -422,6 +576,7 @@ sub lastsearch { } } + \@cust_main; } @@ -433,28 +588,42 @@ sub companysearch { $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'} ) { + 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{'Exact'} -# && ! $company_type{'Fuzzy'} -# # && ! $company_type{'Sound-alike'} -# ) { + 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'} ) { + } + + 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{$_}++; } @@ -473,4 +642,56 @@ sub companysearch { \@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; +} + %>