X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_main.cgi;h=c4b0ce0cd182204a3ea920e809042e70338bbbc3;hb=0d1d2630e726ab4ce32bab9c3e4a889eee43fcb4;hp=69a78d6fef076f6fbceb72d85ca3f008a351342c;hpb=e3f327b2af9e6a591bae41d315fda731ec7f5128;p=freeside.git diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi index 69a78d6fe..c4b0ce0cd 100755 --- a/httemplate/search/cust_main.cgi +++ b/httemplate/search/cust_main.cgi @@ -48,6 +48,9 @@ $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') @@ -65,6 +68,12 @@ if ( $cgi->param('browse') } 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"; } @@ -82,119 +91,69 @@ if ( $cgi->param('browse') } } - my $ncancelled = ''; + my @qual = (); - 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; - } + 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 } - 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 - ) - ) - AND 0 < ( SELECT COUNT(*) FROM cust_pkg + 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 ); - if ( $cancelled ) { - $qual .= ' AND ' if $qual; - $qual .= $cancelled; - } elsif ( $ncancelled ) { + my $addl_qual = join(' AND ', @qual); + + if ( $addl_qual ) { $qual .= ' AND ' if $qual; - $qual .= $ncancelled; + $qual .= $addl_qual; } $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 $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 $rqual = $cancelled || $ncancelled; - if ( $rqual ) { + if ( $addl_qual ) { if ( %search ) { - $rqual = " AND $rqual"; + $addl_qual = " AND $addl_qual"; } else { - $rqual = " WHERE $rqual"; + $addl_qual = " WHERE $addl_qual"; } } - my @just_cust_main; - if ( driver_name eq 'mysql' ) { - @just_cust_main = qsearch('cust_main', \%search, 'cust_main.*', - ",temp1_$$,temp2_$$ $rqual $orderby $limit"); + my $select; + if ( @select ) { + $select = 'cust_main.*, '. join (', ', @select); } 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; + $select = '*'; } - @cust_main = @just_cust_main; + + @cust_main = qsearch('cust_main', \%search, $select, + "$addl_qual $orderby $limit" ); # foreach my $cust_main ( @just_cust_main ) { # @@ -369,6 +328,10 @@ if ( defined dbdef->table('cust_main')->column('ship_last') ) { END } +foreach my $addl_header ( @addl_headers ) { + print "$addl_header"; +} + print <Packages Services @@ -427,6 +390,12 @@ END END } + foreach my $addl_col ( @addl_cols ) { + print qq!!. + $cust_main->get($addl_col). + ""; + } + my($n1)=''; foreach ( @{$all_pkgs{$custnum}} ) { my $pkgnum = $_->pkgnum; @@ -481,6 +450,10 @@ sub custnum_sort { $a->getfield('custnum') <=> $b->getfield('custnum'); } +sub tickets_sort { + $a->getfield('tickets') <=> $b->getfield('tickets'); +} + sub custnumsearch { my $custnum = $cgi->param('custnum_text'); @@ -555,28 +528,12 @@ sub lastsearch { } 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'} || $last_type{'All'} ) { - foreach ( amatch($last, [ qw(i) ], @$all_last) ) { - $last{$_}++; - } - } - - #if ($last_type{'Sound-alike'}) { - #} - - 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'); - } - + push @cust_main, FS::cust_main->fuzzy_search( { 'last' => $last } ); } + #if ($last_type{'Sound-alike'}) { + #} + \@cust_main; } @@ -618,26 +575,10 @@ sub companysearch { } if ( $company_type{'Fuzzy'} || $company_type{'All'} ) { + push @cust_main, FS::cust_main->fuzzy_search( { 'company' => $company } ); + } - &FS::cust_main::check_and_rebuild_fuzzyfiles; - my $all_company = &FS::cust_main::all_company; - - my %company; - if ( $company_type{'Fuzzy'} || $company_type{'All'} ) { - foreach ( amatch($company, [ qw(i) ], @$all_company ) ) { - $company{$_}++; - } - } - - #if ($company_type{'Sound-alike'}) { - #} - - 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'); - } - + if ($company_type{'Sound-alike'}) { } \@cust_main;