bookit / includes / classes / database / Payments.php

Payments.php in Bookit — Booking & Appointment Calendar 2.6.0.5, at includes/classes/database/Payments.php

283 lines 8.3 KB

Raw Download Zip

1	<?php
2
3	namespace Bookit\Classes\Database;
4
5	use Bookit\Classes\Vendor\DatabaseModel;
6
7	class Payments extends DatabaseModel {
8
9	public static $defaultType = 'locally';
10	public static $freeType = 'free';
11	public static $completeType = 'complete';
12	public static $defaultStatus = 'pending';
13	public static $completeStatus = 'complete';
14	public static $rejectedStatus = 'rejected';
15	public static $statusList = array( 'pending', 'cancelled', 'rejected', 'complete' );
16	public static $typeList = array( 'locally', 'stripeConnect', 'paypal', 'stripe', 'woocommerce', 'free' );
17
18	/**
19	* How many times a contended claim is retried before giving up.
20	*
21	* @since 2.6.0.4
22	*
23	* @var int
24	*/
25	const CLAIM_MAX_ATTEMPTS = 3;
26
27	/**
28	* Create Table
29	*/
30	public static function create_table() {
31	global $wpdb;
32	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
33	$table_name = self::_table();
34	$primary_key = self::$primary_key;
35
36	$sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
37	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
38	`appointment_id` INT UNSIGNED NOT NULL,
39	`coupon_id` INT UNSIGNED DEFAULT NULL,
40	`discount_id` INT UNSIGNED DEFAULT NULL,
41	`type` ENUM('locally', 'stripeConnect', 'paypal', 'stripe', 'woocommerce', 'free') NOT NULL DEFAULT 'locally',
42	`status` ENUM('pending', 'cancelled', 'rejected', 'complete') NOT NULL DEFAULT 'pending',
43	`total` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
44	`tax` DECIMAL(10,2) DEFAULT 0.00,
45	`transaction` VARCHAR(255) DEFAULT NULL,
46	`process_id` VARCHAR(64) DEFAULT NULL,
47	`notes` longtext DEFAULT NULL,
48	`created_at` DATETIME NOT NULL,
49	`updated_at` DATETIME NOT NULL,
50	`paid_at` DATETIME,
51	PRIMARY KEY ({$primary_key}),
52	INDEX `idx_appointment_id` (`appointment_id`),
53	INDEX `idx_coupon_id` (`coupon_id`),
54	INDEX `idx_discount_id` (`discount_id`),
55	INDEX `idx_status` (`status`),
56	INDEX `idx_transaction` (`transaction`(191))
57	) {$wpdb->get_charset_collate()};";
58
59	maybe_create_table( $table_name, $sql );
60	}
61
62	/**
63	* Change Payment Status
64	* @param $id
65	* @param $payment_status
66	*/
67	public static function change_payment_status( $id, $payment_status ) {
68	$data = array( 'status' => $payment_status );
69	$where = array( 'id' => $id );
70
71	if ( 'complete' == $payment_status ) {
72	$data['paid_at'] = wp_date( 'Y-m-d H:i:s' );
73	}
74	self::update( $data, $where );
75	}
76
77	/**
78	* Update Payment Methods Enum to Include stripeConnect
79	*
80	* @since 2.5.0
81	*/
82	public static function update_payment_methods_enum() {
83	global $wpdb;
84
85	$sql = sprintf(
86	"ALTER TABLE `%s` MODIFY COLUMN `type` ENUM('locally', 'stripeConnect', 'paypal', 'stripe', 'woocommerce', 'free') NOT NULL DEFAULT 'locally';",
87	esc_sql( self::_table() )
88	);
89	$wpdb->query( $sql );
90	}
91
92	/**
93	* Add the claim column and the `transaction` lookup index, and drop the
94	* unique index an earlier build may have left behind.
95	*
96	* The column is nullable and the index is not unique, so neither can fail
97	* on pre-existing data and every install ends up with the same schema.
98	*
99	* @since 2.6.0.4
100	*
101	* @return bool Whether the schema is in place.
102	*/
103	public static function add_payment_transaction_claim_schema() {
104	global $wpdb;
105
106	$table = esc_sql( self::_table() );
107	$clauses = array();
108
109	if ( ! $wpdb->get_var( "SHOW COLUMNS FROM `{$table}` LIKE 'process_id'" ) ) {
110	$clauses[] = 'ADD COLUMN `process_id` VARCHAR(64) DEFAULT NULL';
111	}
112
113	if ( ! $wpdb->get_var( "SHOW INDEX FROM `{$table}` WHERE Key_name = 'idx_transaction'" ) ) {
114	$clauses[] = 'ADD INDEX `idx_transaction` (`transaction`(191))';
115	}
116
117	if ( $wpdb->get_var( "SHOW INDEX FROM `{$table}` WHERE Key_name = 'idx_transaction_unique'" ) ) {
118	$clauses[] = 'DROP INDEX `idx_transaction_unique`';
119	}
120
121	if ( empty( $clauses ) ) {
122	return true;
123	}
124
125	$applied = false !== $wpdb->query( sprintf( 'ALTER TABLE `%s` %s', $table, implode( ', ', $clauses ) ) );
126
127	if ( ! $applied ) {
128	// Without the column every claim fails, so a silent failure here
129	// would reject otherwise valid payments with no way to diagnose it.
130	error_log( sprintf( 'Bookit: failed to apply the payment claim schema to %s: %s', $table, $wpdb->last_error ) );
131	}
132
133	return $applied;
134	}
135
136	/**
137	* Claim a gateway transaction id for one appointment and write the row.
138	*
139	* The claim is written before the conflict is looked for, so a competing
140	* request has always written its own claim by the time either one checks:
141	* the locking read then blocks on it rather than missing it, which is what
142	* makes this safe without a unique constraint. Losing the race, hitting a
143	* deadlock, or hitting a lock-wait timeout all leave the row untouched.
144	*
145	* @since 2.6.0.4
146	*
147	* @param int $appointment_id Appointment whose payment row is claiming the id.
148	* @param string $transaction_id Gateway transaction id being claimed.
149	* @param array $data Row data to write if the claim is won.
150	*
151	* @return bool Whether the claim was won.
152	*/
153	public static function claim_transaction( $appointment_id, $transaction_id, array $data ) {
154	global $wpdb;
155
156	$table = esc_sql( self::_table() );
157	$had_errors_shown = $wpdb->hide_errors();
158	$won = false;
159
160	for ( $attempt = 0; $attempt < self::CLAIM_MAX_ATTEMPTS; $attempt++ ) {
161	$data['process_id'] = uniqid( 'process_', true );
162
163	if ( false === $wpdb->query( 'START TRANSACTION' ) ) {
164	break;
165	}
166
167	self::update( $data, array( 'appointment_id' => $appointment_id ) );
168
169	if ( ! empty( $wpdb->last_error ) ) {
170	$retry = self::is_retryable_error();
171	$wpdb->query( 'ROLLBACK' );
172
173	if ( $retry ) {
174	continue;
175	}
176
177	break;
178	}
179
180	// process_id is rewritten every attempt, so the row always changes
181	// when it exists: no rows touched means there was nothing to claim.
182	if ( 1 !== (int) $wpdb->rows_affected ) {
183	$wpdb->query( 'ROLLBACK' );
184	break;
185	}
186
187	/**
188	* Fires inside the open claim transaction, after this request has
189	* written its claim and before it looks for a competing one.
190	*
191	* @since 2.6.0.4
192	*
193	* @param int $appointment_id Appointment claiming the id.
194	* @param string $transaction_id Gateway transaction id being claimed.
195	* @param int $attempt Zero-based attempt number.
196	*/
197	do_action( 'bookit_payment_claim_written', $appointment_id, $transaction_id, $attempt );
198
199	$conflicts = $wpdb->query(
200	$wpdb->prepare(
201	"SELECT `id` FROM `{$table}` WHERE `transaction` = %s AND `status` = %s AND `appointment_id` <> %d LIMIT 1 FOR UPDATE",
202	$transaction_id,
203	self::$completeStatus,
204	$appointment_id
205	)
206	);
207
208	// false is an error, 0 is a clean "nobody else holds it" -- the
209	// two must not collapse, or a failed statement reads as a win.
210	if ( false === $conflicts ) {
211	$retry = self::is_retryable_error();
212	$wpdb->query( 'ROLLBACK' );
213
214	if ( $retry ) {
215	continue;
216	}
217
218	break;
219	}
220
221	if ( $conflicts > 0 ) {
222	$wpdb->query( 'ROLLBACK' );
223	break;
224	}
225
226	$won = false !== $wpdb->query( 'COMMIT' );
227	break;
228	}
229
230	if ( $had_errors_shown ) {
231	$wpdb->show_errors();
232	}
233
234	return $won;
235	}
236
237	/**
238	* Whether the last database error is one the server expects the caller to
239	* retry, rather than a genuine failure.
240	*
241	* @since 2.6.0.4
242	*
243	* @return bool
244	*/
245	private static function is_retryable_error() {
246	global $wpdb;
247
248	// 1213 deadlock, 1205 lock wait timeout. A deadlock is rolled back
249	// server-side; a lock wait timeout rolls back only the statement,
250	// which is why the caller issues its own ROLLBACK either way.
251	if ( $wpdb->dbh instanceof \mysqli ) {
252	return in_array( mysqli_errno( $wpdb->dbh ), array( 1213, 1205 ), true );
253	}
254
255	return false !== stripos( (string) $wpdb->last_error, 'try restarting transaction' );
256	}
257
258	/**
259	* Find the completed payment row, if any, already holding a given
260	* gateway transaction id.
261	*
262	* @since 2.6.0.4
263	*
264	* @param string $transaction_id Gateway transaction id to look up.
265	*
266	* @return object\|null
267	*/
268	public static function get_completed_by_transaction( $transaction_id ) {
269	global $wpdb;
270
271	$sql = $wpdb->prepare(
272	sprintf(
273	'SELECT * FROM `%s` WHERE `transaction` = %%s AND `status` = %%s ORDER BY `id` ASC LIMIT 1',
274	esc_sql( self::_table() )
275	),
276	$transaction_id,
277	self::$completeStatus
278	);
279
280	return $wpdb->get_row( $sql );
281	}
282	}
283