function ProcessPaypalPost()
{
$db = DBConnect();
$test_ipn = isset($_POST['test_ipn']) ? 1 : 0;
$txn_id = $_POST['txn_id'];
$stmt = $db->prepare('select * from tblPaypalTransactions where test_ipn = ? and txn_id = ?');
$stmt->bind_param('is', $test_ipn, $txn_id);
$stmt->execute();
$result = $stmt->get_result();
$txnRow = $result->fetch_assoc();
$result->close();
$stmt->close();
LogPaypalMessage("Existing transaction row: " . (isset($txnRow['lastupdate']) ? $txnRow['lastupdate'] : 'no'));
$addPaidTime = !$txnRow || $txnRow['payment_status'] != 'Completed';
$txn_type = null;
if (isset($_POST['txn_type'])) {
$txn_type = $_POST['txn_type'];
$addPaidTime &= $txn_type == 'web_accept';
} elseif (isset($_POST['payment_status'])) {
$txn_type = $_POST['payment_status'];
$addPaidTime = false;
}
$payment_date_timestamp = isset($_POST['payment_date']) ? strtotime($_POST['payment_date']) : time();
$payment_date = date('Y-m-d H:i:s', $payment_date_timestamp);
$parent_txn_id = isset($_POST['parent_txn_id']) ? $_POST['parent_txn_id'] : null;
$mc_currency = isset($_POST['mc_currency']) ? $_POST['mc_currency'] : null;
$mc_fee = isset($_POST['mc_fee']) ? $_POST['mc_fee'] : null;
$mc_gross = isset($_POST['mc_gross']) ? $_POST['mc_gross'] : null;
$pending_reason = isset($_POST['pending_reason']) ? $_POST['pending_reason'] : null;
$reason_code = isset($_POST['reason_code']) ? $_POST['reason_code'] : null;
$payment_status = isset($_POST['payment_status']) ? $_POST['payment_status'] : null;
$hasUserHMAC = isset($_POST['custom']) && $_POST['custom'] != '';
$addPaidTime &= $payment_status == 'Completed';
$addPaidTime &= $hasUserHMAC;
// donations do not have user hmac
LogPaypalMessage("Adding paid time: " . ($addPaidTime ? 'yes' : 'no'));
$user = $hasUserHMAC ? GetUserFromPublicHMAC($_POST['custom'], $payment_date_timestamp) : null;
if (!isset($user) && isset($txnRow['user'])) {
$user = $txnRow['user'];
}
LogPaypalMessage("User: {$user}");
$cols = ['test_ipn', 'txn_id', 'txn_type', 'payment_date', 'parent_txn_id', 'mc_currency', 'mc_fee', 'mc_gross', 'payment_status', 'user', 'pending_reason', 'reason_code'];
$sql = 'insert into tblPaypalTransactions (' . implode(',', $cols) . ') values (' . substr(str_repeat(',?', count($cols)), 1) . ') on duplicate key update ';
for ($x = 2; $x < count($cols); $x++) {
$sql .= ($x == 2 ? '' : ', ') . sprintf('%1$s = ifnull(values(%1$s), %1$s)', $cols[$x]);
}
$stmt = $db->prepare($sql);
$stmt->bind_param('isssssddsiss', $test_ipn, $txn_id, $txn_type, $payment_date, $parent_txn_id, $mc_currency, $mc_fee, $mc_gross, $payment_status, $user, $pending_reason, $reason_code);
$success = $stmt->execute();
$stmt->close();
if (!$success) {
LogPaypalError("Error updating Paypal transaction record");
return false;
}
if ($addPaidTime) {
if (!$user) {
LogPaypalError("Unknown user should have time added.");
}
return ['addTime' => $user];
}
if (!is_null($user) && in_array($payment_status, ['Reversed', 'Refunded'])) {
$skipReverse = false;
if (!is_null($parent_txn_id)) {
$sql = <<<'EOF'
select count(*), max(payment_date)
from tblPaypalTransactions
where test_ipn = ?
and txn_id != ?
and parent_txn_id = ?
and payment_status in ('Reversed', 'Canceled_Reversal', 'Refunded')
EOF;
$stmt = $db->prepare($sql);
$stmt->bind_param('iss', $test_ipn, $txn_id, $parent_txn_id);
$stmt->execute();
$c = $dt = null;
$stmt->bind_result($c, $dt);
$stmt->fetch();
$stmt->close();
if ($c != 0) {
$skipReverse = true;
LogPaypalError("Already had a reversal on {$dt}", "Paypal Payment Reversal Skipped - {$user}");
// not fatal, already processed
}
}
if (!$skipReverse) {
$stmt = $db->prepare('select paiduntil from tblUser where id = ?');
$stmt->bind_param('i', $user);
$stmt->execute();
$paidUntil = null;
$stmt->bind_result($paidUntil);
if (!$stmt->fetch()) {
$paidUntil = false;
}
$stmt->close();
if ($paidUntil === false) {
LogPaypalError("Could not process reversal for missing user {$user}", "Paypal Payment Reversal Failed - {$user}");
return false;
}
$paidUntil = is_null($paidUntil) ? 0 : strtotime($paidUntil);
if ($paidUntil > time()) {
LogPaypalError("", "Paypal Payment Reversed - {$user}");
return ['delTime' => $user];
} else {
LogPaypalError("", "Redundant Paypal Payment Reversal - {$user}");
}
}
} elseif ($mc_gross < 0) {
LogPaypalError("", "Unknown Paypal withdrawal - {$user}");
}
return [];
}
function UpdateBitPayTransaction($bitPayJson, $isNew = false)
{
$isNew = !!$isNew;
// force boolean
if (!is_array($bitPayJson)) {
$jsonString = $bitPayJson;
$bitPayJson = json_decode($jsonString, true);
if (json_last_error() != JSON_ERROR_NONE) {
DebugMessage("Invalid BitPay JSON\n" . print_r($jsonString, true), E_USER_ERROR);
return false;
}
}
$requiredFields = ['btcDue', 'btcPaid', 'btcPrice', 'currency', 'currentTime', 'exceptionStatus', 'expirationTime', 'id', 'invoiceTime', 'posData', 'price', 'rate', 'status', 'url'];
foreach ($requiredFields as $fieldName) {
if (!array_key_exists($fieldName, $bitPayJson)) {
DebugMessage("BitPay JSON missing required field: {$fieldName}\n" . print_r($bitPayJson, true), E_USER_ERROR);
return false;
}
}
$invoiced = floor($bitPayJson['invoiceTime'] / 1000);
$expired = floor($bitPayJson['expirationTime'] / 1000);
$db = DBConnect();
$stmt = $db->prepare('select id, user, subextended from tblBitPayTransactions where id = ?');
$stmt->bind_param('s', $bitPayJson['id']);
$stmt->execute();
$txnId = $userId = $subExtended = null;
$stmt->bind_result($txnId, $userId, $subExtended);
if (!$stmt->fetch()) {
$txnId = $userId = $subExtended = null;
}
$stmt->close();
if (is_null($txnId) != $isNew) {
if ($isNew) {
DebugMessage("New BitPay transaction already found in database:\n" . print_r($bitPayJson, true), E_USER_ERROR);
} else {
DebugMessage("Existing BitPay transaction not found in database:\n" . print_r($bitPayJson, true), E_USER_ERROR);
}
return false;
}
if ($isNew) {
$userId = GetUserFromPublicHMAC($bitPayJson['posData'], $invoiced);
if (is_null($userId)) {
DebugMessage("Could not get valid user from BitPay posData:\n" . print_r($bitPayJson, true), E_USER_ERROR);
return false;
}
}
$sql = <<<'EOF'
insert into tblBitPayTransactions (
id, user, price, currency, rate,
btcprice, btcpaid, btcdue, status,
exception, url, posdata,
invoiced, expired, updated
) values (
?, ?, ?, ?, ?,
?, ?, ?, ?,
?, ?, ?,
from_unixtime(?), from_unixtime(?), now())
on duplicate key update
user=ifnull(user,values(user)), price=values(price), currency=values(currency), rate=values(rate),
btcprice=values(btcprice), btcpaid=values(btcpaid), btcdue=values(btcdue), status=values(status),
exception=values(exception), url=values(url), posdata=values(posdata),
invoiced=values(invoiced), expired=values(expired), updated=values(updated)
EOF;
$stmt = $db->prepare($sql);
$exceptionStatus = $bitPayJson['exceptionStatus'] ?: null;
$stmt->bind_param('sissssssssssii', $bitPayJson['id'], $userId, $bitPayJson['price'], $bitPayJson['currency'], $bitPayJson['rate'], $bitPayJson['btcPrice'], $bitPayJson['btcPaid'], $bitPayJson['btcDue'], $bitPayJson['status'], $exceptionStatus, $bitPayJson['url'], $bitPayJson['posData'], $invoiced, $expired);
if (!$stmt->execute()) {
DebugMessage("Error updating BitPay transaction record: " . print_r($bitPayJson, true), E_USER_ERROR);
return false;
}
$stmt->close();
if ($userId && !$subExtended) {
$stmt = $db->prepare('select locale from tblUser where id = ?');
$locale = null;
$stmt->bind_param('i', $userId);
$stmt->execute();
$stmt->bind_result($locale);
if (!$stmt->fetch()) {
$locale = 'enus';
}
$stmt->close();
$LANG = GetLang($locale);
if (in_array($bitPayJson['status'], ['confirmed', 'complete'])) {
$paidUntil = AddPaidTime($userId, SUBSCRIPTION_PAID_ADDS_SECONDS);
if ($paidUntil === false) {
LogBitPayError($bitPayJson, "Failed to add paid time to {$userId}");
SendUserMessage($userId, 'Subscription', $LANG['paidSubscription'], $LANG['SubscriptionErrors']['paiderror']);
return false;
} else {
$stmt = $db->prepare('update tblBitPayTransactions set subextended=1 where id=?');
$stmt->bind_param('s', $bitPayJson['id']);
if (!$stmt->execute()) {
LogBitPayError($bitPayJson, "Failed to mark transaction as extending the sub for {$userId}");
} else {
$message = $LANG['subscriptionTimeAddedMessage'];
$message .= "<br><br>" . sprintf(preg_replace('/\\{(\\d+)\\}/', '%$1$s', $LANG['paidExpires']), date('Y-m-d H:i:s e', $paidUntil));
SendUserMessage($userId, 'Subscription', $LANG['paidSubscription'], $message);
}
$stmt->close();
}
} elseif (in_array($bitPayJson['status'], ['invalid']) || $bitPayJson['exceptionStatus']) {
LogBitPayError($bitPayJson, "BitPay exception for {$userId}");
SendUserMessage($userId, 'Subscription', $LANG['paidSubscription'], $LANG['SubscriptionErrors']['paiderror']);
}
}
return true;
}
