X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_main.cgi;h=d9d4e657c67a3fcb2bb45f999a6ec1fe92afde0b;hb=76ce9aeaeb39f496797398e84bfdc20fbd817b4d;hp=03997ee35bbfed1f4d713a1db5e4ae944fc2baff;hpb=081fd913aa0383c06425ca1a832d30f7f79e7a06;p=freeside.git
diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi
index 03997ee35..d9d4e657c 100755
--- a/httemplate/search/cust_main.cgi
+++ b/httemplate/search/cust_main.cgi
@@ -57,19 +57,19 @@ if ( $cgi->param('browse')
my $query = $cgi->param('browse');
if ( $query eq 'custnum' ) {
$sortby=\*custnum_sort;
- $orderby = 'ORDER BY custnum';
+ $orderby = "ORDER BY custnum";
} elsif ( $query eq 'last' ) {
$sortby=\*last_sort;
- $orderby = 'ORDER BY last';
+ $orderby = "ORDER BY LOWER(last || ' ' || first)";
} elsif ( $query eq 'company' ) {
$sortby=\*company_sort;
- $orderby = 'ORDER BY company';
+ $orderby = "ORDER BY LOWER(company || ' ' || last || ' ' || first )";
} else {
die "unknown browse field $query";
}
} else {
$sortby = \*last_sort; #??
- $orderby = 'ORDER BY last'; #??
+ $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;
@@ -80,23 +80,52 @@ if ( $cgi->param('browse')
my $ncancelled = '';
+ if ( driver_name eq 'mysql' ) {
+
+ my $query = "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($query) or die dbh->errstr. " preparing $query";
+ $sth->execute or die "Error executing \"$query\": ". $sth->errstr;
+ $query = "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";
+ my $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query";
+ $sth->execute or die "Error executing \"$query\": ". $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 = "
- 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
- )
- ";
+ 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
+ )
+ ";
+ }
+
}
#EWWWWWW
@@ -109,10 +138,14 @@ if ( $cgi->param('browse')
}
$qual = " WHERE $qual" if $qual;
-
- my $statement = "SELECT COUNT(*) FROM cust_main $qual";
- my $sth = dbh->prepare($statement)
- or die dbh->errstr. " doing $statement";
+ 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];
@@ -124,10 +157,20 @@ if ( $cgi->param('browse')
$ncancelled = " WHERE $ncancelled";
}
}
- my @just_cust_main = qsearch('cust_main', \%search, '',
- "$ncancelled $orderby $limit"
- );
+ my @just_cust_main;
+ if ( driver_name eq 'mysql' ) {
+ @just_cust_main = qsearch('cust_main', \%search, 'cust_main.*',
+ ",temp1_$$,temp2_$$ $ncancelled $orderby $limit");
+ } else {
+ @just_cust_main = qsearch('cust_main', \%search, '',
+ "$ncancelled $orderby $limit" );
+ }
+ if ( driver_name eq 'mysql' ) {
+ $query = "DROP TABLE temp1_$$,temp2_$$;";
+ my $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query";
+ $sth->execute or die "Error executing \"$query\": ". $sth->errstr;
+ }
@cust_main = @just_cust_main;
# foreach my $cust_main ( @just_cust_main ) {
@@ -340,12 +383,10 @@ END
$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!
- : ' ';
- print <$ship_company!
+ : ' ';
+ print <$ship_last, $ship_first
$pship_company |
END
@@ -389,13 +430,16 @@ 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 {