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