X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_main.cgi;h=b2f8befe780635c848740c3d99434d15fabf6f89;hb=f7fd2a3e34da751cbc02bbf215e99c6dc89adc15;hp=b26889d0b24e25e4456b8aa822fb1256deae8575;hpb=9bb09778b787ed8a69436c8da34616e80a9addf2;p=freeside.git diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi index b26889d0b..b2f8befe7 100755 --- a/httemplate/search/cust_main.cgi +++ b/httemplate/search/cust_main.cgi @@ -1,25 +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::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; @@ -57,31 +39,60 @@ $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); +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_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 @@ -89,32 +100,60 @@ 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 - ) - ) + 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 $statement = "SELECT COUNT(*) FROM cust_main $ncancelled"; - my $sth = dbh->prepare($statement) - or die dbh->errstr. " doing $statement"; + 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]; + $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 ) { # @@ -133,17 +172,41 @@ 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, @{&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('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; if ( $conf->exists('hidecancelledpackages' ) ) { %all_pkgs = map { $_->custnum => [ $_->ncancelled_pkgs ] } @cust_main; } else { @@ -152,17 +215,28 @@ 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 { +%> + +<% $total ||= scalar(@cust_main); print header("Customer Search Results",menubar( 'Main Menu', popurl(2) )), "$total matching customers found "; + #begin pager my $pager = ''; if ( $total != scalar(@cust_main) && $maxrecords ) { unless ( $offset == 0 ) { @@ -187,19 +261,23 @@ if ( scalar(@cust_main) == 1 && ! $cgi->param('referral_custnum') ) { '">Next '; } } - - 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 )!; + #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+)$/ @@ -250,6 +328,10 @@ if ( defined dbdef->table('cust_main')->column('ship_last') ) { END } +foreach my $addl_header ( @addl_headers ) { + print "