Error: Username not found."; return $ret; } # include payments created by the user $sth = $dbh->prepare("SELECT payid FROM payments WHERE userid=?"); $sth->execute($userid); $matched{$_} = 1 while $_ = $sth->fetchrow_array; # include payments with payment items for that user $sth = $dbh->prepare("SELECT payid FROM payitems WHERE rcptid=?"); $sth->execute($userid); $matched{$_} = 1 while $_ = $sth->fetchrow_array; # HACK: mysql doesn't optimize these queries properly, so we'll do it by hand: much faster { my @acid = ( @{ $dbh->selectcol_arrayref ("SELECT acid FROM acctcode WHERE userid=? LIMIT 5000", undef, $userid)||[] }, @{ $dbh->selectcol_arrayref ("SELECT acid FROM acctcode WHERE rcptid=? LIMIT 5000", undef, $userid)||[] }, ); my $bind = join(",", map { "?" } @acid); # include payments tied to account codes either purchased by or used by the user (new payment system) $sth = $dbh->prepare("SELECT pi.payid FROM acctpayitem p, payitems pi " . "WHERE pi.piid=p.piid AND p.acid IN ($bind) LIMIT 5000"); $sth->execute(@acid); $matched{$_} = 1 while $_ = $sth->fetchrow_array; # include payments tied to account codes either purchased by or used by the user (new payment system) $sth = $dbh->prepare("SELECT payid FROM acctpay WHERE acid IN ($bind) LIMIT 5000"); $sth->execute(@acid); $matched{$_} = 1 while $_ = $sth->fetchrow_array; } } # by-email search if ($GET{'method'} eq "email") { my $email = $GET{'value'}; # payment search vars: ppemail (from a paypal payment notification) # and 'handemail' (manually entered (before cart system)) push @ps_vars, qw(ppemail handemail); # from rcptemail $sth = $dbh->prepare("SELECT payid FROM payitems WHERE ". "rcptemail=?"); $sth->execute($email); $matched{$_} = 1 while $_ = $sth->fetchrow_array; } # coupon search if ($GET{'method'} eq "cpid") { my $cpid = $GET{'value'}; # accept $cpid-$auth, but only care about $cpid $cpid =~ s/^(\d+).*/$1/; # get the payid that used/bought this coupon my ($payid, $ppayid) = $dbh->selectrow_array("SELECT payid, ppayid FROM coupon " . "WHERE cpid=?", undef, $1); $matched{$payid} = 1 if $payid; # transaction coupon was used on $matched{$ppayid} = 1 if $ppayid; # transaction where coupon was purchased } # paypal transaction ID or last name push @ps_vars, "pplastname" if $GET{'method'} eq "lastname"; push @ps_vars, "pptxnid" if $GET{'method'} eq "pptxnid"; # include any paymentsearch vars the above modes might want for my $var (@ps_vars) { $sth = $dbh->prepare("SELECT payid FROM paymentsearch WHERE ". "ikey=? AND ival=?"); $sth->execute($var, $GET{'value'}); $matched{$_} = 1 while $_ = $sth->fetchrow_array; } return $ret. "No matches" unless %matched; my $in = join(',', keys %matched); $sth = $dbh->prepare("SELECT p.*, u.user ". "FROM payments p LEFT JOIN useridmap u ". "ON p.userid=u.userid ". "WHERE p.payid IN ($in) ORDER BY p.payid"); $sth->execute; $ret .= "
| Pay ID# | User | Date Sent/Recv | Amount | Months | Used/Mailed | Method |
| {'payid'}&userid=$row->{'userid'}\">#$row->{'payid'} | {'user'}\">$row->{'user'} | $row->{'datesent'} $row->{'daterecv'} | $amount | $row->{'months'} | $usedmailed | $row->{'method'} |