X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_main.cgi;h=1bd5fb60a26506bbce33909771dab223ec12a002;hp=03997ee35bbfed1f4d713a1db5e4ae944fc2baff;hb=141003fc6cab41daf1a4a697a610c953ec42906e;hpb=081fd913aa0383c06425ca1a832d30f7f79e7a06 diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi index 03997ee35..1bd5fb60a 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 ) { @@ -149,12 +192,16 @@ 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, @{&phonesearch} + if $cgi->param('phone_on') && $cgi->param('phone_text'); push @cust_main, @{&referralsearch} if $cgi->param('referral_custnum'); @@ -340,12 +387,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,19 +434,32 @@ 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 { $a->getfield('custnum') <=> $b->getfield('custnum'); } +sub custnumsearch { + + my $custnum = $cgi->param('custnum_text'); + $custnum =~ s/\D//g; + $custnum =~ /^(\d{1,23})$/ or eidiot "Illegal customer number\n"; + my $custnum = $1; + + [ qsearchs('cust_main', { 'custnum' => $custnum } ) ]; +} + sub cardsearch { my($card)=$cgi->param('card'); @@ -550,4 +608,30 @@ sub companysearch { \@cust_main; } + +sub phonesearch { + my @cust_main; + + my $phone = $cgi->param('phone_text'); + + #false laziness with Record::ut_phonen, only works with US/CA numbers... + $phone =~ s/\D//g; + $phone =~ /^(\d{3})(\d{3})(\d{4})(\d*)$/ + or eidiot gettext('illegal_phone'). ": $phone"; + $phone = "$1-$2-$3"; + $phone .= " x$4" if $4; + + 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; +} + %>