X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_main.cgi;h=7d5941a16d42abd2b714fffe6d67c3ea66944d38;hb=1053db7f76169cbbc87840539959a4c362aff242;hp=89e325d09cf9e21aa8197a90e7a8ecd34e5ec040;hpb=0bdec843e4a9bb7f947c9ba980a40f7bf37020fb;p=freeside.git diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi index 89e325d09..7d5941a16 100755 --- a/httemplate/search/cust_main.cgi +++ b/httemplate/search/cust_main.cgi @@ -45,24 +45,56 @@ $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'; +my @select = (); +my @addl_headers = (); +my @addl_cols = (); +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 )"; + } elsif ( $query eq 'tickets' ) { + $sortby = \*tickets_sort; + $orderby = "ORDER BY tickets DESC"; + push @select, FS::TicketSystem->sql_num_customer_tickets. " as tickets"; + push @addl_headers, 'Tickets'; + push @addl_cols, 'tickets'; + } 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 @@ -70,32 +102,54 @@ if ( $cgi->param('browse') ) { && ! $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 - ) - "; - } + push @qual, FS::cust_main->uncancel_sql; - my $statement = "SELECT COUNT(*) FROM cust_main $ncancelled"; - my $sth = dbh->prepare($statement) - or die dbh->errstr. " doing $statement"; + } + + 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); + + #here is the agent virtualization + $addl_qual .= ( $addl_qual ? ' AND ' : '' ). + $FS::CurrentUser::CurrentUser->agentnums_sql; + + 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]; - my @just_cust_main = qsearch('cust_main',{}, '', - "$ncancelled $orderby $limit" - ); + if ( $addl_qual ) { + if ( %search ) { + $addl_qual = " AND $addl_qual"; + } else { + $addl_qual = " WHERE $addl_qual"; + } + } - @cust_main = @just_cust_main; + my $select; + if ( @select ) { + $select = 'cust_main.*, '. join (', ', @select); + } else { + $select = '*'; + } + + @cust_main = qsearch('cust_main', \%search, $select, + "$addl_qual $orderby $limit" ); # foreach my $cust_main ( @just_cust_main ) { # @@ -116,12 +170,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'); @@ -130,10 +190,23 @@ if ( $cgi->param('browse') ) { push @cust_main, @{&companysearch}; } + if ( $cgi->param('search_cust') ) { + $sortby = \*company_sort; + $orderby = "ORDER BY LOWER(company || ' ' || last || ' ' || first )"; + warn "smart searching for: ". $cgi->param('search_cust'); + push @cust_main, smart_search( 'search' => $cgi->param('search_cust') ); + } + @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; @@ -152,14 +225,19 @@ if ( scalar(@cust_main) == 1 && ! $cgi->param('referral_custnum') ) { } #exit; } elsif ( scalar(@cust_main) == 0 ) { +%> + +<% eidiot "No matching customers found!\n"; } else { +%> +<%= include('/elements/header.html', "Customer Search Results", '' ) %> - $total ||= scalar(@cust_main); - print header("Customer Search Results",menubar( - 'Main Menu', popurl(2) - )), "$total matching customers found "; + <% $total ||= scalar(@cust_main); %> + <%= $total %> matching customers found + + <% #begin pager my $pager = ''; if ( $total != scalar(@cust_main) && $maxrecords ) { @@ -186,26 +264,30 @@ 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+)$/ or eidiot "Illegal referral_custnum\n"; my $referral_custnum = $1; my $cust_main = qsearchs('cust_main', { custnum => $referral_custnum } ); - print '
'. + print ''. qq!!. 'referrals of $referral_custnum: ". @@ -235,31 +317,52 @@ END ''; } - print "

". $pager. &table(). <config('ticket_system-custom_priority_field') + && @{[ $conf->config('ticket_system-custom_priority_field-values') ]} ) { + @custom_priorities = + $conf->config('ticket_system-custom_priority_field-values'); + } + + print "

". $pager. include('/elements/table-grid.html'). < - - (bill) name - company + + (bill) name + company END if ( defined dbdef->table('cust_main')->column('ship_last') ) { print <(service) name - company + (service) name + company END } +foreach my $addl_header ( @addl_headers ) { + print ''. "$addl_header"; +} + print <Packages - Services + Packages + Services END + my $bgcolor1 = '#eeeeee'; + my $bgcolor2 = '#ffffff'; + my $bgcolor; + my(%saw,$cust_main); - my $p = popurl(2); foreach $cust_main ( sort $sortby grep(!$saw{$_->custnum}++, @cust_main) ) { + + if ( $bgcolor eq $bgcolor1 ) { + $bgcolor = $bgcolor2; + } else { + $bgcolor = $bgcolor1; + } + my($custnum,$last,$first,$company)=( $cust_main->custnum, $cust_main->getfield('last'), @@ -283,22 +386,77 @@ END } else { $view = $p. 'view/cust_main.cgi?'. $custnum; } - print <$company
! + : ' '; + %> + - $custnum - $last, $first - $company -END + ><%= $custnum %> + ><%= "$last, $first" %> + ><%= $pcompany %> + + <% if ( defined dbdef->table('cust_main')->column('ship_last') ) { my($ship_last,$ship_first,$ship_company)=( $cust_main->ship_last || $cust_main->getfield('last'), $cust_main->ship_last ? $cust_main->ship_first : $cust_main->first, $cust_main->ship_last ? $cust_main->ship_company : $cust_main->company, ); -print <$ship_last, $ship_first - $ship_company -END + my $pship_company = $ship_company + ? qq!$ship_company! + : ' '; + %> + + ><%= "$ship_last, $ship_first" %> + ><%= $pship_company %> + + <% } + + foreach my $addl_col ( @addl_cols ) { %> + + ALIGN=right> + + <% if ( $addl_col eq 'tickets' ) { + if ( @custom_priorities ) { + print &itable('', 0); + foreach my $priority ( @custom_priorities, '' ) { + + my $num = + FS::TicketSystem->num_customer_tickets($custnum,$priority); + my $ahref = ''; + $ahref= '' + if $num; + + print ''. + " $ahref$num". + "$ahref". + ( $priority || '(none)' ). + ""; + + } + print ''. + ''; + } + + my $ahref = ''; + $ahref = '' + if $cust_main->get($addl_col); + + print $ahref. $cust_main->get($addl_col). ''; + print "". + "${ahref}Total". + "" + if @custom_priorities; + + } else { + print $cust_main->get($addl_col); + } + print ""; } my($n1)=''; @@ -309,49 +467,81 @@ 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; - print $n1, qq!$pkg - $comment!; + print $n1, qq!$pkg - $comment!; + my($n2)=''; foreach my $cust_svc ( @cust_svc ) { my($label, $value, $svcdb) = $cust_svc->label; my($svcnum) = $cust_svc->svcnum; my($sview) = $p.'view'; - print $n2,qq!$label!, - qq!$value!; + print $n2,qq!$label!, + qq!$value!; $n2=""; } + + unless ( @cust_svc ) { + print qq! !; + } + #print qq!\n!; $n1=""; } + + unless ( @{$all_pkgs{$custnum}} ) { + print qq! !; + } + print ""; } + + %> - print "$pager"; + <%= $pager %> -} + <%= include('/elements/footer.html') %> + +<% } #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 tickets_sort { + $b->getfield('tickets') <=> $a->getfield('tickets'); +} + +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 +549,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,39 +581,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'=>$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{'Substring'} || $last_type{'All'} ) { - &FS::cust_main::check_and_rebuild_fuzzyfiles; - my $all_last = &FS::cust_main::all_last; + push @cust_main, qsearch( 'cust_main', + { 'last' => { 'op' => 'ILIKE', + 'value' => "%$last%" } } ); - my %last; - if ($last_type{'Fuzzy'}) { - foreach ( amatch($last, [ qw(i) ], @$all_last) ) { - $last{$_}++; - } - } - - #if ($last_type{'Sound-alike'}) { - #} + push @cust_main, qsearch( 'cust_main', + { 'ship_last' => { 'op' => 'ILIKE', + 'value' => "%$last%" } } ) + if defined dbdef->table('cust_main')->column('ship_last'); - foreach ( keys %last ) { - push @cust_main, qsearch('cust_main',{'last'=>$_}); - push @cust_main, qsearch('cust_main',{'ship_last'=>$_}) - if defined dbdef->table('cust_main')->column('ship_last'); - } + } + if ( $last_type{'Fuzzy'} || $last_type{'All'} ) { + push @cust_main, FS::cust_main->fuzzy_search( { 'last' => $last } ); } + + #if ($last_type{'Sound-alike'}) { + #} + \@cust_main; } @@ -433,44 +623,94 @@ 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'} -# # && ! $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'); + } + + 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'=>$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'} ) { + } - &FS::cust_main::check_and_rebuild_fuzzyfiles; - my $all_company = &FS::cust_main::all_company; + if ( $company_type{'Fuzzy'} || $company_type{'All'} ) { + push @cust_main, FS::cust_main->fuzzy_search( { 'company' => $company } ); + } - my %company; - if ($company_type{'Fuzzy'}) { - foreach ( amatch($company, [ qw(i) ], @$all_company ) ) { - $company{$_}++; - } - } + if ($company_type{'Sound-alike'}) { + } - #if ($company_type{'Sound-alike'}) { - #} + \@cust_main; +} - foreach ( keys %company ) { - push @cust_main, qsearch('cust_main',{'company'=>$_}); - push @cust_main, qsearch('cust_main',{'ship_company'=>$_}) - if defined dbdef->table('cust_main')->column('ship_last'); - } +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; } + %>