1<?php 2// (c) Copyright by authors of the Tiki Wiki CMS Groupware Project 3// 4// All Rights Reserved. See copyright.txt for details and a complete list of authors. 5// Licensed under the GNU LESSER GENERAL PUBLIC LICENSE. See license.txt for details. 6// $Id$ 7 8class PaymentLib extends TikiDb_Bridge 9{ 10 private $gateways = []; 11 12 public $fieldmap = [ 13 'paymentRequestId' => [ 14 'table' => 'tpr', 15 ], 16 'description' => [ 17 'table' => 'tpr', 18 ], 19 'detail' => [ 20 'table' => 'tpr', 21 ], 22 'details' => [ 23 'table' => 'tp', 24 ], 25 'amount' => [ 26 'table' => 'tpr', 27 ], 28 'request_date' => [ 29 'table' => 'tpr', 30 ], 31 'payment_date' => [ 32 'table' => 'tp', 33 ], 34 'type' => [ 35 'table' => 'tp', 36 ], 37 'login' => [ 38 'table' => 'uu', 39 ], 40 'payer' => [ 41 'table' => 'uup', 42 'field' => 'login', 43 ], 44 ]; 45 46 private function setTable($field) 47 { 48 return isset($this->fieldmap[$field]['table']) ? $this->fieldmap[$field]['table'] : ''; 49 } 50 51 private function setField($field) 52 { 53 return isset($this->fieldmap[$field]['field']) ? $this->fieldmap[$field]['field'] : $field; 54 } 55 56 private function fieldTableArray() 57 { 58 $ret = []; 59 foreach ($this->fieldmap as $field => $info) { 60 $table = $this->setTable($field); 61 $rfield = $this->setField($field); 62 $ret[] = $table . '.' . $rfield; 63 } 64 return $ret; 65 } 66 67 function request_payment($description, $amount, $paymentWithin, $detail = null, $currency = null) 68 { 69 global $prefs, $user; 70 $userlib = TikiLib::lib('user'); 71 72 $description = substr($description, 0, 100); 73 if (empty($currency)) { 74 $currency = $prefs['payment_currency']; 75 } else { 76 $currency = substr($currency, 0, 3); 77 } 78 79 $query = 'INSERT INTO `tiki_payment_requests`' . 80 ' ( `amount`, `amount_paid`, `currency`, `request_date`, `due_date`, `description`, `detail`, `userId` )' . 81 ' VALUES( ?, 0, ?, NOW(), DATE_ADD(NOW(), INTERVAL ? DAY), ?, ?, ? )'; 82 83 $bindvars = [ $amount, $currency, (int) $paymentWithin, $description, $detail, $userlib->get_user_id($user) ]; 84 85 $this->query($query, $bindvars); 86 87 return $this->lastInsertId(); 88 } 89 90 private function get_payments($conditions, $offset, $max, array $bindvars, $what = '') 91 { 92 $mid = '`tiki_payment_requests` tpr LEFT JOIN `users_users` uu ON (uu.`userId` = tpr.`userId`)'; 93 $count = 'SELECT COUNT(*) FROM ' . $mid . ' WHERE ' . $conditions; 94 $data = 'SELECT tpr.*, uu.`login` as `user` ' . $what . ' FROM ' . $mid . ' WHERE ' . $conditions; 95 96 $all = $this->fetchAll($data, $bindvars, $max, $offset); 97 98 return [ 99 'cant' => $this->getOne($count, $bindvars), 100 'data' => Perms::filter( 101 [ 'type' => 'payment' ], 102 'object', 103 $all, 104 [ 'object' => 'paymentRequestId' ], 105 'payment_view' 106 ), 107 ]; 108 } 109 110 function get_outstanding($offset, $max, $ofUser = '', $filter = [], $sort = null) 111 { 112 $conditions = '`amount_paid` < `amount` AND NOW() <= `due_date` AND `cancel_date` IS NULL AND (`authorized_until` IS NULL OR `authorized_until` <= NOW())'; 113 if ($ofUser) { 114 $conditions .= " AND uu.`login` = " . $this->qstr($ofUser); 115 } 116 $bindvars = []; 117 $conditions .= $this->addFilterSort($filter, $sort, $bindvars); 118 return $this->get_payments($conditions, $offset, $max, $bindvars); 119 } 120 121 function get_past($offset, $max, $ofUser = '', $filter = [], $sort = null) 122 { 123 global $prefs; 124 $parserlib = TikiLib::lib('parser'); 125 126 $conditions = 'tpr.`amount` <= tpr.`amount_paid` AND tpr.`cancel_date` IS NULL'; 127 if ($ofUser) { 128 $conditions .= " AND uu.`login` = " . $this->qstr($ofUser); 129 } 130 $bindvars = []; 131 $conditions .= $this->addFilterSort($filter, $sort, $bindvars); 132 133 $count = 'SELECT COUNT(*)' . 134 ' FROM `tiki_payment_requests` tpr' . 135 ' LEFT JOIN `users_users` uu ON (uu.`userId` = tpr.`userId`)' . 136 ' LEFT JOIN `tiki_payment_received` tp ON (tp.`paymentRequestId`=tpr.`paymentRequestId` AND tp.`status` = "paid")' . 137 ' LEFT JOIN `users_users` uup ON (uup.`userId` = tp.`userId`) WHERE ' . $conditions; 138 139 $data = 'SELECT tpr.*, uu.`login` as `user`, tp.`type`, tp.`payment_date`,' . 140 ' tp.`details` as `payment_detail`, tpr.`detail` as `request_detail`, uup.`login` as `payer`' . 141 ' FROM `tiki_payment_requests` tpr' . 142 ' LEFT JOIN `users_users` uu ON (uu.`userId` = tpr.`userId`)' . 143 ' LEFT JOIN `tiki_payment_received` tp ON (tp.`paymentRequestId`=tpr.`paymentRequestId` AND tp.`status` = "paid")' . 144 ' LEFT JOIN `users_users` uup ON (uup.`userId` = tp.`userId`) WHERE ' . $conditions; 145 146 $all = $this->fetchAll($data, $bindvars, $max, $offset); 147 148 foreach ($all as & $payment) { 149 if (empty($payment['payer'])) { // anonymous 150 $details = json_decode($payment['payment_detail'], true); 151 if ($details && ! empty($details['payer_email'])) { 152 $payment['payer_email'] = $details['payer_email']; 153 } 154 } 155 156 if (! empty($payment['request_detail']) && $prefs['feature_jquery_tablesorter']) { 157 $payment['request_detail'] = strip_tags(str_replace(['</td>','</td></tr>'], [' </td>','<br></td></tr>'], $parserlib->parse_data($payment['request_detail'])), '<a><br>'); 158 } 159 } 160 161 return [ 162 'cant' => $this->getOne($count, $bindvars), 163 'data' => Perms::filter( 164 [ 'type' => 'payment' ], 165 'object', 166 $all, 167 [ 'object' => 'paymentRequestId' ], 168 'payment_view' 169 ), 170 ]; 171 } 172 173 function get_overdue($offset, $max, $ofUser = '', $filter = [], $sort = null) 174 { 175 $conditions = '`amount_paid` < `amount` AND NOW() > `due_date` AND `cancel_date` IS NULL AND (`authorized_until` IS NULL OR `authorized_until` <= NOW())'; 176 if ($ofUser) { 177 $conditions .= " AND uu.`login` = " . $this->qstr($ofUser); 178 } 179 $bindvars = []; 180 $conditions .= $this->addFilterSort($filter, $sort, $bindvars); 181 return $this->get_payments($conditions, $offset, $max, $bindvars); 182 } 183 184 function get_authorized($offset, $max, $ofUser = '', $filter = [], $sort = null) 185 { 186 $conditions = '`amount_paid` < `amount` AND `cancel_date` IS NULL AND `authorized_until` IS NOT NULL AND `authorized_until` >= NOW()'; 187 if ($ofUser) { 188 $conditions .= " AND uu.`login` = " . $this->qstr($ofUser); 189 } 190 $bindvars = []; 191 $conditions .= $this->addFilterSort($filter, $sort, $bindvars); 192 return $this->get_payments($conditions, $offset, $max, $bindvars); 193 } 194 195 function get_canceled($offset, $max, $ofUser = '', $filter = [], $sort = null) 196 { 197 $conditions = '`cancel_date` IS NOT NULL'; 198 if ($ofUser) { 199 $conditions .= " AND uu.`login` = " . $this->qstr($ofUser); 200 } 201 $bindvars = []; 202 $conditions .= $this->addFilterSort($filter, $sort, $bindvars); 203 return $this->get_payments($conditions, $offset, $max, $bindvars); 204 } 205 206 function uncancel_payment($id) 207 { 208 $this->query('UPDATE `tiki_payment_requests` SET `cancel_date` = NULL WHERE `paymentRequestId` = ?', [ $id ]); 209 } 210 211 function cancel_payment($id) 212 { 213 if ($info = $this->get_payment($id)) { 214 if ($info['state'] != 'canceled') { 215 $this->run_behaviors($info, 'cancel'); 216 } 217 } 218 219 $this->query('UPDATE `tiki_payment_requests` SET `cancel_date` = NOW() WHERE `paymentRequestId` = ?', [ $id ]); 220 } 221 222 function get_payment($id) 223 { 224 global $tikilib, $prefs; 225 $info = $this->fetchAll( 226 'SELECT tpr.*, uu.`login` as `user` FROM `tiki_payment_requests` tpr' . 227 ' LEFT JOIN `users_users` uu ON (uu.`userId` = tpr.`userId`)' . 228 ' WHERE `paymentRequestId` = ?', 229 [$id] 230 ); 231 $info = reset($info); 232 233 if ($info) { 234 $info['state'] = $this->find_state($info); 235 $info['amount_original'] = number_format($info['amount'], 2, '.', ','); 236 $info['amount_remaining_raw'] = $info['amount'] - $info['amount_paid']; 237 $info['amount_remaining'] = number_format($info['amount_remaining_raw'], 2, '.', ','); 238 $info['url'] = $tikilib->tikiUrl( 239 'tiki-payment.php', 240 ['invoice' => $info['paymentRequestId'],] 241 ); 242 243 $info['returnurl'] = $tikilib->tikiUrl( 244 'tiki-payment.php', 245 ['invoice' => $info['paymentRequestId'],] 246 ); 247 248 // Add token if feature is activated (need prefs 249 global $user; 250 if ($prefs['auth_token_access'] == 'y' && 251 (! $user || isset($_SESSION['forceanon']) && 252 $_SESSION['forceanon'] == 'y' && 253 ! Perms::get('payment', $info['paymentRequestId'])->manual_payment) 254 ) { 255 require_once('lib/wiki-plugins/wikiplugin_getaccesstoken.php'); 256 $info['returnurl'] = $tikilib->tikiUrl( 257 'tiki-payment.php', 258 [ 259 'invoice' => $info['paymentRequestId'], 260 'TOKEN' => wikiplugin_getaccesstoken( 261 '', 262 [ 263 'entry' => 'tiki-payment.php', 264 'keys' => ['invoice'], 265 'values' => [$info['paymentRequestId']] 266 ] 267 ), 268 ] 269 ); 270 } 271 272 $info['paypal_ipn'] = $tikilib->tikiUrl( 273 'tiki-payment.php', 274 ['ipn' => 1,'invoice' => $info['paymentRequestId'],] 275 ); 276 277 $info['payments'] = []; 278 279 $payments = $this->fetchAll( 280 'SELECT * FROM `tiki_payment_received` WHERE `paymentRequestId` = ? ORDER BY `payment_date` DESC', 281 [$id] 282 ); 283 284 foreach ($payments as $payment) { 285 $payment['details'] = json_decode($payment['details'], true); 286 $payment['amount_paid'] = number_format($payment['amount'], 2, '.', ','); 287 $info['payments'][] = $payment; 288 } 289 290 $info['actions'] = $this->extract_actions($info['actions']); 291 292 return $info; 293 } 294 } 295 296 private function find_state($info) 297 { 298 if (! empty($info['cancel_date'])) { 299 return 'canceled'; 300 } 301 302 if ($info['amount_paid'] >= $info['amount']) { 303 return 'past'; 304 } 305 306 $current = date('Y-m-d H:i:s'); 307 308 if ($info['authorized_until'] && $info['authorized_until'] > $current) { 309 return 'authorized'; 310 } 311 312 if ($info['due_date'] < $current) { 313 return 'overdue'; 314 } 315 316 return 'outstanding'; 317 } 318 319 private function extract_actions($actions) 320 { 321 $out = [ 322 'authorize' => [], 323 'complete' => [], 324 'cancel' => [], 325 ]; 326 if (! empty($actions)) { 327 $out = array_merge( 328 $out, 329 json_decode($actions, true) 330 ); 331 } 332 333 return $out; 334 } 335 336 function enter_payment($invoice, $amount, $type, array $data) 337 { 338 $tx = TikiDb::get()->begin(); 339 340 global $user; 341 $userlib = TikiLib::lib('user'); 342 if ($info = $this->get_payment($invoice)) { 343 if ($info['state'] != 'past' && $info['amount_remaining_raw'] - $amount <= 0) { 344 $results = $this->run_behaviors($info, 'complete'); 345 if ($info['state'] == 'canceled') { 346 // in the case of canceled payments being paid (e.g. user was delayed at Paypal when cancellation happened) 347 $this->uncancel_payment($invoice); 348 } 349 } 350 if (! empty($results)) { 351 $data = array_merge($results, $data); 352 } 353 $data = json_encode($data); 354 $this->query( 355 'INSERT INTO `tiki_payment_received` ( `paymentRequestId`, `payment_date`, `amount`, `type`, `details`, `userId` )' . 356 ' VALUES( ?, NOW(), ?, ?, ?, ? )', 357 [ 358 $invoice, 359 $amount, 360 $type, 361 $data, 362 empty($user) ? $info['userId'] : $userlib->get_user_id($user) 363 ] 364 ); 365 $this->query( 366 'UPDATE `tiki_payment_requests` SET `amount_paid` = `amount_paid` + ? WHERE `paymentRequestId` = ?', 367 [ $amount, $invoice ] 368 ); 369 } 370 371 $tx->commit(); 372 } 373 374 function enter_authorization($invoice, $type, $validForDays, array $data) 375 { 376 global $user; 377 $userlib = TikiLib::lib('user'); 378 if ($info = $this->get_payment($invoice)) { 379 if ($info['state'] != 'past') { 380 $results = $this->run_behaviors($info, 'authorize'); 381 if ($info['state'] == 'canceled') { 382 // in the case of canceled payments being paid (e.g. user was delayed at Paypal when cancellation happened) 383 $this->uncancel_payment($invoice); 384 } 385 } 386 if (! empty($results)) { 387 $data = array_merge($results, $data); 388 } 389 $data = json_encode($data); 390 $this->query( 391 'INSERT INTO `tiki_payment_received` ( `paymentRequestId`, `payment_date`, `amount`, `type`, `details`, `userId`, `status` )' . 392 ' VALUES( ?, NOW(), ?, ?, ?, ?, "auth_pending" )', 393 [ 394 $invoice, 395 0, 396 $type, 397 $data, 398 empty($user) ? $info['userId'] : $userlib->get_user_id($user) 399 ] 400 ); 401 $this->query( 402 'UPDATE `tiki_payment_requests` SET `authorized_until` = DATE_ADD(NOW(), INTERVAL ? DAY) WHERE `paymentRequestId` = ?', 403 [ (int) $validForDays, $invoice ] 404 ); 405 } 406 } 407 408 function capture_payment($paymentId) 409 { 410 if ($info = $this->get_payment($paymentId)) { 411 foreach ($info['payments'] as $received) { 412 if ($received['status'] != 'auth_pending') { 413 continue; 414 } 415 416 if ($received['amount']) { 417 // When electing to capture a specific amount, assume that amount is the total to be paid. 418 $table = $this->table('tiki_payment_requests'); 419 $table->update(['amount' => (float) $received['amount']], ['paymentRequestId' => $paymentId]); 420 } 421 422 if ($gateway = $this->gateway($received['type'])) { 423 if (is_callable([$gateway, 'capture_payment'])) { 424 // Result is about request reception success, not actual capture success 425 $result = $gateway->capture_payment($info, $received); 426 427 if ($result) { 428 $this->query('UPDATE `tiki_payment_received` SET `status` = "auth_captured" WHERE `paymentReceivedId` = ?', [$received['paymentReceivedId']]); 429 } 430 } 431 } 432 } 433 } 434 } 435 436 function register_behavior($invoice, $event, $behavior, array $arguments) 437 { 438 if (! in_array($event, [ 'complete', 'cancel', 'authorize' ])) { 439 return false; 440 } 441 442 if (! $callback = $this->get_behavior($behavior)) { 443 return false; 444 } 445 446 if ($info = $this->get_payment($invoice)) { 447 $actions = $info['actions']; 448 449 $actions[$event][] = [ 'behavior' => $behavior, 'arguments' => $arguments ]; 450 $this->query( 451 'UPDATE `tiki_payment_requests` SET `actions` = ? WHERE `paymentRequestId` = ?', 452 [json_encode($actions), $invoice] 453 ); 454 } else { 455 return false; 456 } 457 } 458 459 private function run_behaviors($info, $event) 460 { 461 $behaviors = $info['actions'][$event]; 462 $results = []; 463 464 foreach ($behaviors as $b) { 465 if ($callback = $this->get_behavior($b['behavior'])) { 466 $results[str_replace('payment_behavior_', '', $callback)] = call_user_func_array($callback, $b['arguments']); 467 } 468 } 469 return $results; 470 } 471 472 private function get_behavior($name) 473 { 474 $file = __DIR__ . "/behavior/$name.php"; 475 $function = 'payment_behavior_' . $name; 476 if (is_readable($file)) { 477 require_once $file; 478 if (is_callable($function)) { 479 return $function; 480 } 481 } 482 } 483 484 function gateway($name) 485 { 486 if (isset($this->gateways[$name])) { 487 return $this->gateways[$name]; 488 } 489 490 switch ($name) { 491 case 'israelpost': 492 require_once 'lib/payment/israelpostlib.php'; 493 return $this->gateways[$name] = new IsraelPostLib($this); 494 } 495 } 496 497 private function addFilterSort(array $filter, $sort, array & $bindvars) 498 { 499 $ret = ''; 500 if (! empty($filter)) { 501 foreach ($filter as $field => $value) { 502 if (isset($this->fieldmap[$field])) { 503 if ($field === 'payer') { 504 $field = 'details'; 505 } 506 $table = $this->setTable($field); 507 $col = $this->setField($field); 508 $ret .= " AND " . $table . '.`' . $col . '`'; 509 if ($field == 'description' || $field == 'detail' || $field == 'details') { 510 $ret .= ' LIKE ?'; 511 $bindvars[] = "%$value%"; 512 } elseif (in_array($field, ['payment_date', 'request_date'])) { 513 if (preg_match('/([<>=]*) \'?([^\']*)\' AND ([^ ]*) ([<>=]*) \'?([^\']*)/i', $value, $matches)) { 514 // quoted date range? 515 if ($matches[3] === $table . '.`' . $col . '`') { 516 $ret .= " {$matches[1]} ? AND {$matches[3]} {$matches[4]} ?"; 517 $bindvars[] = $matches[2]; 518 $bindvars[] = $matches[5]; 519 } 520 } elseif (preg_match('/([<>=]*) \'([^\']*)/', $value, $matches)) { 521 // single quoted date 522 $ret .= " {$matches[1]} ? "; 523 $bindvars[] = $matches[2]; 524 } elseif (preg_match('/BETWEEN FROM_UNIXTIME\(([^\)]*?)\) AND FROM_UNIXTIME\(([^\)]*)\)/i', $value, $matches)) { // single quoted date 525 // between date range 526 $ret .= " BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?)"; 527 $bindvars[] = $matches[1]; 528 $bindvars[] = $matches[2]; 529 } elseif (preg_match('/([<>=]*) FROM_UNIXTIME\(([^\)]*)\)/i', $value, $matches)) { // single quoted date 530 $ret .= " {$matches[1]} FROM_UNIXTIME(?) "; 531 $bindvars[] = $matches[2]; 532 } 533 } else { 534 $ret .= ' LIKE ?'; 535 $bindvars[] = "$value%"; 536 } 537 } 538 } 539 } 540 if (! empty($sort)) { 541 if (! is_array($sort)) { 542 $sort = explode(',', $sort); 543 } 544 foreach ($sort as $s) { 545 if (strpos($s, '.') === false) { 546 $dir = strrchr($s, '_'); 547 $sfield = substr($s, 0, strlen($s) - strlen($dir)); 548 $stable = $this->setTable($sfield); 549 $scol = $this->setField($sfield); 550 $newsort[] = $stable . '.' . $scol . $dir; 551 } 552 } 553 if (! empty($newsort)) { 554 $fields = $this->fieldTableArray(); 555 $newsort = implode(',', $newsort); 556 $ret .= ' ORDER BY ' . $this->convertSortMode($newsort, $fields); 557 } 558 } 559 return $ret; 560 } 561} 562