X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_main.cgi;h=24d49c588130828813bcdc9ab40ec983bb0f53d2;hp=658069b08eb4915099e8f0d5b6d53115f64d959c;hb=1fc8addc56f8daf12397da568eb1ac1b27fd3984;hpb=3a02c437eadd9a39f7e56056ca987a4846650209 diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi index 658069b08..24d49c588 100755 --- a/httemplate/search/cust_main.cgi +++ b/httemplate/search/cust_main.cgi @@ -1,345 +1,102 @@ -%my $curuser = $FS::CurrentUser::CurrentUser; -% -%die "access denied" -% unless $curuser->access_right('List customers'); -% -%my $conf = new FS::Conf; -%my $maxrecords = $conf->config('maxsearchrecordsperpage'); -% -%#my $cache; -% -%#my $monsterjoin = <param('offset') || 0; -%$limit .= " OFFSET $offset" if $offset; -% -%my $total = 0; -% -%my(@cust_main, $sortby, $orderby); -%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' ) { -% if ( $conf->exists('cust_main-default_agent_custid') ) { -% $sortby=\*display_custnum_sort; -% $orderby = "ORDER BY CASE WHEN agent_custid IS NOT NULL AND agent_custid != '' THEN CAST(agent_custid AS BIGINT) ELSE custnum END"; -% } else { -% $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 { -% $sortby = \*last_sort; #?? -% $orderby = "ORDER BY LOWER(last || ' ' || first)"; #?? -% } -% -% if ( $cgi->param('otaker_on') ) { -% die "access denied" -% unless $FS::CurrentUser::CurrentUser->access_right('Configuration'); -% $cgi->param('otaker') =~ /^(\w{1,32})$/ or errorpage("Illegal otaker"); -% $search{otaker} = $1; -% } elsif ( $cgi->param('agentnum_on') ) { -% $cgi->param('agentnum') =~ /^(\d+)$/ or errorpage("Illegal agentnum"); -% $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, FS::cust_main->uncancel_sql; -% -% } -% -% 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->inactive_sql if $cgi->param('inactive'); -% 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]; -% -% if ( $addl_qual ) { -% if ( %search ) { -% $addl_qual = " AND $addl_qual"; -% } else { -% $addl_qual = " WHERE $addl_qual"; -% } -% } -% -% 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 ) { -%# -%# 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=(); -% $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}; -% } -% -% if ( $cgi->param('search_cust') ) { -% $sortby = \*company_sort; -% $orderby = "ORDER BY LOWER(company || ' ' || last || ' ' || first )"; -% push @cust_main, smart_search( 'search' => $cgi->param('search_cust') ); -% } -% -% @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; -%} -% -%my %all_pkgs; -%if ( $conf->exists('hidecancelledpackages' ) ) { -% %all_pkgs = map { $_->custnum => [ $_->ncancelled_pkgs ] } @cust_main; -%} else { -% %all_pkgs = map { $_->custnum => [ $_->all_pkgs ] } @cust_main; -%} -%#%all_pkgs = (); -% -%if ( scalar(@cust_main) == 1 && ! $cgi->param('referral_custnum') ) { +% if ( scalar(@cust_main) == 1 && ! $cgi->param('referral_custnum') ) { % if ( $cgi->param('quickpay') eq 'yes' ) { -% print $cgi->redirect(popurl(2). "edit/cust_pay.cgi?quickpay=yes;custnum=". $cust_main[0]->custnum); + <% $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); + <% $cgi->redirect(popurl(2). "view/cust_main.cgi?". $cust_main[0]->custnum) %> % } -% #exit; +% $m->abort; %} elsif ( scalar(@cust_main) == 0 ) { -% +% errorpage(emt("No matching customers found!")); +% } # errorpage quits, so we don't need an 'else' below - -% -% errorpage("No matching customers found!"); -%} else { -% - -<% include('/elements/header.html', "Customer Search Results", '' ) %> +<& /elements/header.html, mt("Customer Search Results"), '' &> % $total ||= scalar(@cust_main); - - <% $total %> matching customers found +<% mt("[_1] matching customers found",$total) |h %> % my $pager = include( '/elements/pager.html', % 'offset' => $offset, -% 'num_rows' => scalar(@cust_main), -% 'total' => $total, -% 'maxrecords' => $maxrecords, +% 'num_rows' => scalar(@cust_main), +% 'total' => $total, +% 'maxrecords' => $maxrecords, % ); -% + % unless ( $cgi->param('cancelled') ) { +% my $linklabel = ''; % 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!; +% $linklabel = 'show'; % } else { % $cgi->param('showcancelledcustomers', 0); -% $cgi->param('offset', 0); -% print qq!( hide!; +% $linklabel = 'hide'; % } -% print ' cancelled customers )'; +% $cgi->param('offset', 0); + ( <% mt("$linklabel canceled customers") |h %> ) % } -% + +% unless ($cgi->param('browse')) { + <& /elements/agent_transfer_link.html, {'list' => \@cust_main} &> +% } + % if ( $cgi->param('referral_custnum') ) { % $cgi->param('referral_custnum') =~ /^(\d+)$/ -% or errorpage("Illegal referral_custnum"); +% or errorpage(emt("Illegal referral_custnum")); % my $referral_custnum = $1; % my $cust_main = qsearchs('cust_main', { custnum => $referral_custnum } ); -% print '
'. -% qq!!. -% 'referrals of $referral_custnum: ". -% ( $cust_main->company -% || $cust_main->last. ', '. $cust_main->first ). -% ''; -% print "\n",< -% function changed(what) { -% what.form.submit(); -% } -% -%END -% print ' +% my $refcustlabel = "$referral_custnum: " . +% ( $cust_main->company || $cust_main->last. ', '. $cust_main->first ); + referrals of + "><% $refcustlabel |h %> + levels deep". -% ''. -% ''; + levels deep + % } -% + % my @custom_priorities = (); % if ( $conf->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'). < -% # -% Status -% (bill) name -% company -%END -% -%if ( defined dbdef->table('cust_main')->column('ship_last') ) { -% print <(service) name -% company -%END -%} -% + +

<% $pager.include('/elements/table-grid.html') %> + + <% mt('#') |h %> + <% mt('Status') |h %> + <% mt('Name') |h %> + <% mt('Company') |h %> + %foreach my $addl_header ( @addl_headers ) { -% print ''. "$addl_header"; + <% $addl_header %> %} -% -%print <Packages -% Services -% -%END -% + + <% mt('Packages') |h %> + <% mt('Services') |h %> + + % my $bgcolor1 = '#eeeeee'; % my $bgcolor2 = '#ffffff'; % my $bgcolor; @@ -362,16 +119,49 @@ % $cust_main->company, % ); % -% my(@lol_cust_svc); -% my($rowspan)=0;#scalar( @{$all_pkgs{$custnum}} ); -% foreach ( @{$all_pkgs{$custnum}} ) { -% #my(@cust_svc) = qsearch( 'cust_svc', { 'pkgnum' => $_->pkgnum } ); -% my @cust_svc = $_->cust_svc; -% push @lol_cust_svc, \@cust_svc; -% $rowspan += scalar(@cust_svc) || 1; -% } +% my @all_cust_svc; +% my @pkg_rowspans; +% foreach my $cust_pkg ( @{$all_pkgs{$custnum}} ) { +% my %cust_svc_by_svcpart; +% my $rows = 0; +% #local($FS::part_pkg::cache_enabled) = 1; #for $cust_pkg->part_svc +% local($FS::cust_svc::cache_enabled) = 1; #for $cust_svc->part_svc +% #local($FS::pkg_svc::cache_enabled) = 1; #for $pkg_svc->part_svc +% foreach my $part_svc ( +% #$cust_pkg->part_svc( summarize_size=>$large_pkg_size ) +% qsearch({ +% 'select' => 'part_svc.*, COUNT(*) AS num_cust_svc', +% 'table' => 'part_svc', +% 'addl_from' => 'LEFT JOIN cust_svc USING ( svcpart )', +% 'extra_sql' => 'WHERE pkgnum = ? '. +% ' GROUP BY '. join(', ', +% map "part_svc.$_", fields('part_svc') +% ), +% 'extra_param' => [ [$cust_pkg->pkgnum,'int'] ], +% }) +% ) { +% my $svcpart = $part_svc->svcpart; +% my $num_cust_svc = $part_svc->num_cust_svc; +% if ( $large_pkg_size > 0 and $num_cust_svc >= $large_pkg_size ) { +% # don't retrieve the cust_svc records, just stash the +% # part_svc and num_cust_svc for later +% $cust_svc_by_svcpart{$svcpart} = +% [ 'summarize', $part_svc, $num_cust_svc ]; +% $rows += 2; +% } +% elsif ( $num_cust_svc ) { +% #$cust_svc_by_svcpart{$svcpart} = $part_svc->cust_pkg_svc; +% #further optimization opportunities: don't need to re-pull in another $part_svc object, sorting this is expensive, etc. +% $cust_svc_by_svcpart{$svcpart} = [ $cust_pkg->cust_svc($part_svc->svcpart) ]; +% $rows += $num_cust_svc; +% } #if summarize +% } #foreach $part_svc +% $rows ||= 1; # in case the package has no services +% push @all_cust_svc, \%cust_svc_by_svcpart; +% push @pkg_rowspans, $rows; +% } #foreach $cust_pkg +% my $rowspan = List::Util::sum(@pkg_rowspans) || 1; % -% #my($rowspan) = scalar(@{$all_pkgs{$custnum}}); % my $view; % if ( defined $cgi->param('quickpay') && $cgi->param('quickpay') eq 'yes' ) { % $view = $p. 'edit/cust_pay.cgi?quickpay=yes;custnum='. $custnum; @@ -379,44 +169,34 @@ % $view = $p. 'view/cust_main.cgi?'. $custnum; % } % my $pcompany = $company -% ? qq!$company! +% ? qq!!. encode_entities($company). '' % : ' '; % -% my $status = $cust_main->status; -% my $statuscol = $cust_main->statuscolor; +% my $status_label = $cust_main->status_label; +% my $statuscolor = $cust_main->statuscolor; - ><% $cust_main->display_custnum %> - ><% ucfirst($status) %> - ><% "$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, -% ); -% my $pship_company = $ship_company -% ? qq!$ship_company! -% : ' '; -% - - - ><% "$ship_last, $ship_first" %> - ><% $pship_company %> -% } -% + > + <% $cust_main->display_custnum %> + + > + <% $status_label %> + + > + <% "$last, $first" |h %> + + > + <% $pcompany %> + + % foreach my $addl_col ( @addl_cols ) { % if ( $addl_col eq 'tickets' ) { % if ( @custom_priorities ) { - - ALIGN=right> - + ALIGN=right> + % foreach my $priority ( @custom_priorities, '' ) { -% % my $num = % FS::TicketSystem->num_customer_tickets($custnum,$priority); % my $ahref = ''; @@ -424,29 +204,24 @@ % FS::TicketSystem->href_customer_tickets($custnum,$priority). % '">' % if $num; -% - % } -
<% $ahref.$num %> - <% $ahref %><% $priority || '(none)' %> + <% $ahref %><% $priority || '('.emt('none').')' %>
% } else { - - - ALIGN=right> + ALIGN=right> + % } -% % my $ahref = ''; % $ahref = ' <% ${ahref} %>Total @@ -467,276 +240,521 @@
% } - % } else { - - ALIGN=right> + ALIGN=right> <% $cust_main->get($addl_col) %> -% + % } % } -% -% my($n1)=''; + +% my $n1 = ''; % foreach ( @{$all_pkgs{$custnum}} ) { +% local($FS::cust_svc::cache_enabled) = 1; #for $cust_svc->part_svc % my $pkgnum = $_->pkgnum; -%# my $part_pkg = qsearchs( 'part_pkg', { pkgpart => $_->pkgpart } ); % my $part_pkg = $_->part_pkg; % -% my $pkg = $part_pkg->pkg; -% my $comment = $part_pkg->comment; -% my $show = $curuser->default_customer_view =~ /^(jumbo|packages)$/ +% my $pkg_comment = $part_pkg->pkg_comment( cust_pkg=>$_, nopkgpart=>1 ); +% my $show = $default_customer_view =~ /^(jumbo|packages)$/ % ? '' % : ';show=packages'; % my $frag = "cust_pkg$pkgnum"; #hack for IE ignoring real #fragment % my $pkgview = "${p}view/cust_main.cgi?custnum=$custnum$show;fragment=$frag#$frag"; -% 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!; -% -% 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!!. FS::UI::Web::svc_link($m, $cust_svc->part_svc, $cust_svc) . qq! !. -% qq!!. FS::UI::Web::svc_label_link($m, $cust_svc->part_svc, $cust_svc) . qq! !; -% $n2=""; -% } -% -% unless ( @cust_svc ) { -% print qq! !; +% # cust_svc stuff, built earlier +% my %cust_svc_by_svcpart = %{ shift @all_cust_svc }; +% my $pkg_rowspan = shift @pkg_rowspans; + + <% $n1 %> + <% $pkg_comment |h %> + + +% my $n2 = ''; +% my $td = ''; +% +% foreach my $svcpart ( sort keys %cust_svc_by_svcpart ) { #sort order? +% my $these = $cust_svc_by_svcpart{$svcpart}; +% if ( $these->[0] eq 'summarize' ) { +% my $part_svc = $these->[1]; +% my $num_cust_svc = $these->[2]; + <% $n2 %> +% # summarize +% # link opens a new search for this pkgnum/svcpart combo +% my $href = $p.'search/cust_pkg_svc.html?svcpart='.$svcpart. +% ';pkgnum='.$pkgnum; + <% $td %> + <% $part_svc->svc %> + + <% $td %> + (<% mt("view all [_1]", $num_cust_svc) |h %>) + + + <% $td %> + <% $td %><& /elements/search-cust_svc.html, + 'svcpart' => $svcpart, + 'pkgnum' => $pkgnum, + 'svcdb' => $part_svc->svcdb, + &> +% $n2=""; +% } +% elsif ( scalar @$these ) { # do not summarize +% foreach my $cust_svc ( @$these ) { +% my $part_svc = $cust_svc->part_svc; + <% $n2 %> + <% $td %> + <% FS::UI::Web::svc_link($m, $part_svc, $cust_svc) %> + + <% $td %> + <% FS::UI::Web::svc_label_link($m, $part_svc, $cust_svc) %> + +% $n2=""; +% } #foreach $cust_svc +% } +% } # foreach $svcpart +% +% unless ( %cust_svc_by_svcpart ) { +   % } % -% #print qq!\n!; % $n1=""; % } % % unless ( @{$all_pkgs{$custnum}} ) { -% print qq! !; +   % } % -% print ""; + % } -% -% - <% $pager %> - <% include('/elements/footer.html') %> -% } -% -%#undef $cache; #does this help? -% -%# -% -%sub last_sort { -% 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; -% lc($a->company) cmp lc($b->company) -% || lc($a->getfield('last')) cmp lc($b->getfield('last')) -% || lc($a->first) cmp lc($b->first);; -%} -% -%sub display_custnum_sort { -% $a->display_custnum <=> $b->display_custnum; -%} -% -%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 errorpage("Illegal customer number"); -% $custnum = $1; -% -% [ qsearchs('cust_main', { 'custnum' => $custnum } ) ]; -%} -% -%sub cardsearch { -% -% my($card)=$cgi->param('card'); -% $card =~ s/\D//g; -% $card =~ /^(\d{13,16})$/ or errorpage("Illegal card number"); -% my($payinfo)=$1; -% -% [ qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'CARD'}), -% qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'DCRD'}) -% ]; -%} -% -%sub referralsearch { -% $cgi->param('referral_custnum') =~ /^(\d+)$/ -% or errorpage("Illegal referral_custnum"); -% my $cust_main = qsearchs('cust_main', { 'custnum' => $1 } ) -% or errorpage("Customer $1 not found"); -% my $depth; -% if ( $cgi->param('referral_depth') ) { -% $cgi->param('referral_depth') =~ /^(\d+)$/ -% or errorpage("Illegal referral_depth"); -% $depth = $1; -% } else { -% $depth = 1; -% } -% [ $cust_main->referral_cust_main($depth) ]; -%} -% -%sub lastsearch { -% my(%last_type); -% my @cust_main; -% foreach ( $cgi->param('last_type') ) { -% $last_type{$_}++; -% } -% -% $cgi->param('last_text') =~ /^([\w \,\.\-\']*)$/ -% or errorpage("Illegal last name"); -% my($last)=$1; -% -% 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' => { '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{'Fuzzy'} || $last_type{'All'} ) { -% push @cust_main, FS::cust_main->fuzzy_search( { 'last' => $last } ); -% } -% -% #if ($last_type{'Sound-alike'}) { -% #} -% -% \@cust_main; -%} -% -%sub companysearch { -% -% my(%company_type); -% my @cust_main; -% foreach ( $cgi->param('company_type') ) { -% $company_type{$_}++ -% }; -% -% $cgi->param('company_text') =~ -% /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]*)$/ -% or errorpage("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{'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'); -% -% } -% -% if ( $company_type{'Fuzzy'} || $company_type{'All'} ) { -% push @cust_main, FS::cust_main->fuzzy_search( { 'company' => $company } ); -% } -% -% if ($company_type{'Sound-alike'}) { -% } -% -% \@cust_main; -%} -% -%sub address2search { -% my @cust_main; -% -% $cgi->param('address2_text') =~ -% /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]*)$/ -% or errorpage("Illegal address2"); -% my $address2 = $1; -% -% push @cust_main, qsearch( 'cust_main', -% { 'address2' => { 'op' => 'ILIKE', -% 'value' => $address2 } } ); -% push @cust_main, qsearch( 'cust_main', -% { 'ship_address2' => { 'op' => 'ILIKE', -% 'value' => $address2 } } ); -% -% \@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 { -% errorpage(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; -%} + <& /elements/footer.html &> +<%init> +my $curuser = $FS::CurrentUser::CurrentUser; + +die "access denied" + unless $curuser->access_right('List all customers') + || $curuser->access_right('List customers'); + +my $conf = new FS::Conf; +my $maxrecords = $conf->config('maxsearchrecordsperpage') || 100; +# summarize more than this many services of the same svcpart +my $large_pkg_size = $conf->config('cust_pkg-large_pkg_size') || 0; + +my $default_customer_view = $curuser->default_customer_view; + +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); +my @select = (); +my @addl_headers = (); +my @addl_cols = (); +if ( ( $cgi->param('browse') + || $cgi->param('otaker_on') + || $cgi->param('agentnum_on') + ) + and $curuser->access_right('List all customers') +) { + + my %search = (); + + if ( $cgi->param('browse') ) { + my $query = $cgi->param('browse'); + if ( $query eq 'custnum' ) { + if ( $conf->exists('cust_main-default_agent_custid') ) { + $sortby=\*display_custnum_sort; + $orderby = "ORDER BY CASE WHEN agent_custid IS NOT NULL + AND agent_custid != '' + AND agent_custid ". regexp_sql. " '^[0-9]+\$' + THEN CAST(agent_custid AS BIGINT) + ELSE custnum + END"; + } else { + $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'; + } elsif ( $query eq 'uspsunvalid' ) { + $search{'country'} = 'US'; + $sortby=\*custnum_sort; + $orderby = "ORDER BY custnum"; + } else { + die "unknown browse field $query"; + } + } else { + $sortby = \*last_sort; #?? + $orderby = "ORDER BY LOWER(last || ' ' || first)"; #?? + } + + if ( $cgi->param('otaker_on') ) { + die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Configuration'); + $cgi->param('otaker') =~ /^(\w{1,32})$/ or errorpage("Illegal otaker"); + $search{otaker} = $1; + } elsif ( $cgi->param('agentnum_on') ) { + $cgi->param('agentnum') =~ /^(\d+)$/ or errorpage("Illegal agentnum"); + $search{agentnum} = $1; + } + + my @qual = (); + + my $ncancelled = ''; + + if ( $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me + || ( $conf->exists('hidecancelledcustomers') + && ! $cgi->param('showcancelledcustomers') ) + ) { + push @qual, FS::cust_main->uncancel_sql; + } + + 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->ordered_sql if $cgi->param('ordered'); + push @qual, FS::cust_main->active_sql if $cgi->param('active'); + push @qual, FS::cust_main->inactive_sql if $cgi->param('inactive'); + 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 ( $cgi->param('browse') && $cgi->param('browse') eq 'uspsunvalid' ) { + $addl_qual .= ' AND ( length(zip) < 9 OR upper(address1) != address1 OR upper(city) != city ) '; + } + + 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"; + } + } + + my $select; + if ( @select ) { + $select = 'cust_main.*, '. join (', ', @select); + } else { + $select = '*'; + } + + @cust_main = qsearch('cust_main', \%search, $select, + "$addl_qual $orderby $limit" ); + +} else { + @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'); + + if ( $cgi->param('company_on') && $cgi->param('company_text') ) { + $sortby = \*company_sort; + push @cust_main, @{&companysearch}; + } + + if ( $cgi->param('search_cust') ) { + $sortby = \*company_sort; + $orderby = "ORDER BY LOWER(company || ' ' || last || ' ' || first )"; + push @cust_main, smart_search( + 'search' => scalar($cgi->param('search_cust')), + ); + } + + @cust_main = grep { $_->status ne 'cancelled' } @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; +} + +my %all_pkgs = (); +if ( scalar(@cust_main) > 1 || $cgi->param('referral_custnum') ) { + + my $pkgs_method = $conf->exists('hidecancelledpackages') + ? 'ncancelled_pkgs' + : 'all_pkgs'; + + #false laziness w/httemplate/view/cust_main/packages.html + my $select = join(',', + 'cust_pkg.*', + 'part_pkg.*', + 'setup_option.optionvalue AS _opt_setup_fee', + 'recur_option.optionvalue AS _opt_recur_fee', + ); + + my $addl_from = ' LEFT JOIN part_pkg USING ( pkgpart ) '. + FS::part_pkg->join_options_sql; + + local($FS::cust_pkg::cache_enabled) = 1; #for $cust_pkg->part_pkg + %all_pkgs = map { $_->custnum => + [ $_->$pkgs_method({ select => $select, + addl_from => $addl_from, + skip_label_sort => 1, + }) + ]; + } + @cust_main; + +} + +sub last_sort { + 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; + lc($a->company) cmp lc($b->company) + || lc($a->getfield('last')) cmp lc($b->getfield('last')) + || lc($a->first) cmp lc($b->first);; +} + +sub display_custnum_sort { + $a->display_custnum <=> $b->display_custnum; +} + +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 errorpage(emt("Illegal customer number")); + $custnum = $1; + + [ qsearchs('cust_main', { 'custnum' => $custnum } ) ]; +} + +sub cardsearch { + + my($card)=$cgi->param('card'); + $card =~ s/\D//g; + $card =~ /^(\d{13,19}|\d{8,9})$/ or errorpage(emt("Illegal card number")); + my($payinfo)=$1; + + [ qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'CARD'}), + qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'DCRD'}) + ]; +} + +sub referralsearch { + $cgi->param('referral_custnum') =~ /^(\d+)$/ + or errorpage("Illegal referral_custnum"); + my $cust_main = qsearchs('cust_main', { 'custnum' => $1 } ) + or errorpage(emt("Customer [_1] not found",$1)); + my $depth; + if ( $cgi->param('referral_depth') ) { + $cgi->param('referral_depth') =~ /^(\d+)$/ + or errorpage(emt("Illegal referral_depth")); + $depth = $1; + } else { + $depth = 1; + } + [ $cust_main->referral_cust_main($depth) ]; +} + +sub lastsearch { + my(%last_type); + my @cust_main; + foreach ( $cgi->param('last_type') ) { + $last_type{$_}++; + } + + $cgi->param('last_text') =~ /^([\w \,\.\-\']*)$/ + or errorpage(emt("Illegal last name")); + my($last)=$1; + + 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' => { '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{'Fuzzy'} || $last_type{'All'} ) { + push @cust_main, FS::cust_main::Search->fuzzy_search( { 'last' => $last } ); + } + + \@cust_main; +} + +sub companysearch { + + my(%company_type); + my @cust_main; + foreach ( $cgi->param('company_type') ) { + $company_type{$_}++ + }; + + $cgi->param('company_text') =~ + /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]*)$/ + or errorpage(emt("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{'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'); + + } + + if ( $company_type{'Fuzzy'} || $company_type{'All'} ) { + push @cust_main, FS::cust_main::Search->fuzzy_search( { 'company' => $company } ); + } + + if ($company_type{'Sound-alike'}) { + } + + \@cust_main; +} + +sub address2search { + my @cust_main; + + $cgi->param('address2_text') =~ + /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]*)$/ + or errorpage(emt("Illegal address2")); + my $address2 = $1; + + # matching at the start or end of an address, but not in the middle + my @where; + foreach my $toggle (0,1) { + push @where, 'LOWER(cust_location.address2) LIKE LOWER(' + . dbh->quote($toggle ? $address2 . '%' : '%' . $address2) + . ')'; + } + + push @cust_main, qsearch({ + 'debug' => 1, + 'table' => 'cust_main', + 'addl_from' => 'JOIN cust_location ON (cust_location.locationnum IN (cust_main.bill_locationnum, cust_main.ship_locationnum))', + 'extra_sql' => 'WHERE ' . join(' OR ',@where), + }); + + \@cust_main; +} + +sub phonesearch { + my @cust_main; + + my $phone = $cgi->param('phone_text'); + + $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 { + errorpage(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; +} +