← All changes | admin/helpers/src/performance/cleaner.php +378 -18 1.7.2 → 1.8.16 View file →

		@@ -48,10 +48,13 @@
48	48	// number of records affected
49	49	$affected_records = 0;
50	50
51	51	if (!in_array('seasons', $skip_checks)) {
52		- // clean up expired seasonal records
53		- $affected_records += self::pricingAlterations();
	52	+ // clean up expired seasonal records (expired at least 7 days ago)
	53	+ $affected_records += self::pricingAlterations(7);
	54	+
	55	+ // clean up ghost records
	56	+ $affected_records += self::ghostRecords();
54	57	}
55	58
56	59	return $affected_records;
57	60	}
		@@ -56,19 +59,252 @@
56	59	return $affected_records;
57	60	}
58	61
59	62	/**
	63	+ * Performs a general database optimization.
	64	+ *
	65	+ * @param ?array $options Optional optimization options.
	66	+ *
	67	+ * @return array Optimization results.
	68	+ *
	69	+ * @since 1.18.5 (J) - 1.8.5 (WP)
	70	+ */
	71	+ public static function optimizeDatabase(?array $options = null)
	72	+ {
	73	+ // operation results pool
	74	+ $results = [];
	75	+
	76	+ // check whether database optimization should run
	77	+ $min_ts = mktime(date('G'), 0, 0, date('n'), date('j'), date('Y'));
	78	+ $max_ts = mktime(date('G'), 59, 59, date('n'), date('j'), date('Y'));
	79	+ $scheduled_ts = 0;
	80	+ if ($db_opt_time = VBOFactory::getConfig()->get('dboptimizetime')) {
	81	+ $time_parts = explode(':', $db_opt_time);
	82	+ $scheduled_ts = mktime((int) $time_parts[0], (int) ($time_parts[1] ?? 0), 0, date('n'), date('j'), date('Y'));
	83	+ }
	84	+
	85	+ if (!($options['forced'] ?? 0) && (!$scheduled_ts || !($scheduled_ts >= $min_ts && $scheduled_ts <= $max_ts))) {
	86	+ // execution not allowed at all or at this time
	87	+ return $results;
	88	+ }
	89	+
	90	+ // check, at this point, if some custom optimization options were set
	91	+ if ($custom_options = VBOFactory::getConfig()->getArray('db_optimize_options', [])) {
	92	+ // merge default options with custom ones
	93	+ $options = array_merge(($options ?: []), $custom_options);
	94	+ }
	95	+
	96	+ // define script max execution time
	97	+ ignore_user_abort(true);
	98	+ ini_set('max_execution_time', (int) ($options['max_execution_time'] ?? 600));
	99	+ set_time_limit((int) ($options['max_execution_time'] ?? 600));
	100	+
	101	+ // operations time start
	102	+ $microStart = microtime(true);
	103	+
	104	+ // list of database optimization operations
	105	+ $db_operations = [
	106	+ 'listings_global_snapshot' => function($options) {
	107	+ // get rid of redundant pricing alteration records by performing a global listing snapshot
	108	+ return static::listingsGlobalSnapshot($options);
	109	+ },
	110	+ 'delete_old_guest_messages' => function($options) {
	111	+ // get rid of the "old" guest messages
	112	+ return static::deleteOldGuestMessages($options);
	113	+ },
	114	+ ];
	115	+
	116	+ foreach ($db_operations as $operation_name => $operation_callback) {
	117	+ if (isset($options['ignore_' . $operation_name])) {
	118	+ // skip this operation
	119	+ continue;
	120	+ }
	121	+
	122	+ try {
	123	+ // execute the operation callback
	124	+ $results[$operation_name] = $operation_callback($options);
	125	+ } catch (Exception $e) {
	126	+ // push the error
	127	+ $results[$operation_name] = $e;
	128	+ }
	129	+ }
	130	+
	131	+ // operations time end
	132	+ $microEnd = microtime(true);
	133	+
	134	+ // set the total execution time in seconds
	135	+ $results['_durationSeconds'] = round(($microEnd - $microStart) / 1000, 2);
	136	+
	137	+ if (!($options['ignore_notification'] ?? 0)) {
	138	+ // store an entry within the notifications center
	139	+ try {
	140	+ VBOFactory::getNotificationCenter()
	141	+ ->store([
	142	+ [
	143	+ 'sender' => 'website',
	144	+ 'type' => 'info',
	145	+ 'title' => JText::translate('VBO_MAINTENANCE'),
	146	+ 'summary' => JText::sprintf('VBO_DB_OPTIM_DURATION', $results['_durationSeconds']),
	147	+ ],
	148	+ ]);
	149	+ } catch (Exception $e) {
	150	+ // do nothing
	151	+ }
	152	+ }
	153	+
	154	+ // return the pool of results
	155	+ return $results;
	156	+ }
	157	+
	158	+ /**
	159	+ * Performs a global rates snapshot over all listings.
	160	+ *
	161	+ * @param ?array $options Optional execution options.
	162	+ *
	163	+ * @return array Snapshot results.
	164	+ *
	165	+ * @since 1.18.5 (J) - 1.8.5 (WP)
	166	+ */
	167	+ public static function listingsGlobalSnapshot(?array $options = null)
	168	+ {
	169	+ // get all listings
	170	+ $listingsData = VikBooking::getAvailabilityInstance()->loadRooms($options['listing_ids'] ?? [], $max = 0, $anew = true);
	171	+
	172	+ // filter out the unpublished ones, if any
	173	+ $listingsData = array_filter($listingsData, function($listing) {
	174	+ return !empty($listing['avail']);
	175	+ });
	176	+
	177	+ // shuffle the elements
	178	+ shuffle($listingsData);
	179	+
	180	+ // build snapshot data
	181	+ $snapshotData = [
	182	+ 'listing_id' => null,
	183	+ 'id_price' => $options['id_price'] ?? null,
	184	+ 'from_date' => $options['from_date'] ?? date('Y-m-d'),
	185	+ 'to_date' => $options['to_date'] ?? date('Y-m-d', strtotime(sprintf('+%d months', (int) ($options['months'] ?? 3)))),
	186	+ 'skip_derived' => boolval($options['skip_derived'] ?? true),
	187	+ 'use_cache' => true,
	188	+ 'forced' => true,
	189	+ ];
	190	+
	191	+ // processing results
	192	+ $processingResults = [];
	193	+
	194	+ // process listings
	195	+ foreach ($listingsData as $listing) {
	196	+ try {
	197	+ // inject options
	198	+ static::setOptions(
	199	+ array_merge(
	200	+ $snapshotData,
	201	+ [
	202	+ 'listing_id' => $listing['id'],
	203	+ ]
	204	+ )
	205	+ );
	206	+
	207	+ // perform listing rates snapshot
	208	+ $snapshotResult = static::listingSeasonSnapshot();
	209	+
	210	+ // process the result
	211	+ if ($options['full_response'] ?? null) {
	212	+ // set the full snapshot response
	213	+ $processingResults[$listing['id']] = $snapshotResult;
	214	+ } else {
	215	+ // include only errors, if any
	216	+ $processingResults[$listing['id']] = array_column($snapshotResult, 'errors');
	217	+ }
	218	+ } catch (Exception $e) {
	219	+ $processingResults[$listing['id']] = $e->getMessage();
	220	+ }
	221	+ }
	222	+
	223	+ return $processingResults;
	224	+ }
	225	+
	226	+ /**
	227	+ * Deletes the guest messages that belong to past reservations.
	228	+ *
	229	+ * @param ?array $options Optional execution options.
	230	+ *
	231	+ * @return array Deletion results.
	232	+ *
	233	+ * @since 1.18.5 (J) - 1.8.5 (WP)
	234	+ */
	235	+ public static function deleteOldGuestMessages(?array $options = null)
	236	+ {
	237	+ $dbo = JFactory::getDbo();
	238	+
	239	+ $results = [
	240	+ '_threadsDeleted' => 0,
	241	+ '_messagesDeleted' => 0,
	242	+ ];
	243	+
	244	+ try {
	245	+ // get all the "expired" threads
	246	+ $dbo->setQuery(
	247	+ $dbo->getQuery(true)
	248	+ ->select([
	249	+ $dbo->qn('t.id'),
	250	+ $dbo->qn('t.idorder'),
	251	+ $dbo->qn('o.checkout'),
	252	+ ])
	253	+ ->from($dbo->qn('#__vikchannelmanager_threads', 't'))
	254	+ ->leftJoin($dbo->qn('#__vikbooking_orders', 'o') . ' ON ' . $dbo->qn('t.idorder') . ' = ' . $dbo->qn('o.id'))
	255	+ ->where($dbo->qn('o.checkout') . ' < ' . strtotime(sprintf('-%d months', (int) ($options['past_months'] ?? 9))))
	256	+ );
	257	+
	258	+ $threads = $dbo->loadAssocList();
	259	+
	260	+ if (!$threads) {
	261	+ // nothing to clean
	262	+ return $results;
	263	+ }
	264	+
	265	+ // delete guest messages
	266	+ $dbo->setQuery(
	267	+ $dbo->getQuery(true)
	268	+ ->delete($dbo->qn('#__vikchannelmanager_threads_messages'))
	269	+ ->where($dbo->qn('idthread') . ' IN (' . implode(', ', array_map('intval', array_column($threads, 'id'))) . ')')
	270	+ );
	271	+ $dbo->execute();
	272	+
	273	+ $results['_messagesDeleted'] += (int) $dbo->getAffectedRows();
	274	+
	275	+ // delete threads
	276	+ $dbo->setQuery(
	277	+ $dbo->getQuery(true)
	278	+ ->delete($dbo->qn('#__vikchannelmanager_threads'))
	279	+ ->where($dbo->qn('id') . ' IN (' . implode(', ', array_map('intval', array_column($threads, 'id'))) . ')')
	280	+ );
	281	+ $dbo->execute();
	282	+
	283	+ $results['_threadsDeleted'] += (int) $dbo->getAffectedRows();
	284	+ } catch (Exception $e) {
	285	+ // do nothing
	286	+ }
	287	+
	288	+ return $results;
	289	+ }
	290	+
	291	+ /**
60	292	* Cleans up expired season pricing records.
61	293	*
62		- * @return int The number of rows affected.
	294	+ * @param ?int $pastDays Optional days in the past to use as target date.
	295	+ *
	296	+ * @return int The number of rows affected.
	297	+ *
	298	+ * @since 1.18.4 (J) - 1.8.4 (WP) added argument $pastDays.
63	299	*/
64		- public static function pricingAlterations()
	300	+ public static function pricingAlterations(?int $pastDays = null)
65	301	{
66	302	$dbo = JFactory::getDbo();
67	303
68	304	$affected = 0;
69	305
70		- $nowinfo = getdate();
	306	+ $nowinfo = is_int($pastDays) ? getdate(strtotime(sprintf('-%d days', abs($pastDays)))) : getdate();
71	307
72	308	$year_base = mktime(0, 0, 0, 1, 1, $nowinfo['year']);
73	309	$midnight_base = ($nowinfo['hours'] * 3600) + ($nowinfo['minutes'] * 60) + $nowinfo['seconds'];
74	310
		@@ -113,8 +349,115 @@
113	349	return $affected;
114	350	}
115	351
116	352	/**
	353	+ * Identifies and cleans up ghost records occupying listings with non existing bookings.
	354	+ * If the E4jConnect Channel Manager is available, an auto bulk-action is triggered.
	355	+ *
	356	+ * @return int The number of rows affected.
	357	+ *
	358	+ * @since 1.17.7 (J) - 1.7.7 (WP)
	359	+ */
	360	+ public static function ghostRecords()
	361	+ {
	362	+ $dbo = JFactory::getDbo();
	363	+
	364	+ $affected = 0;
	365	+
	366	+ // identify the room-busy relations whose booking IDs are empty
	367	+ $dbo->setQuery(
	368	+ $dbo->getQuery(true)
	369	+ ->select($dbo->qn('idbusy'))
	370	+ ->from($dbo->qn('#__vikbooking_ordersbusy'))
	371	+ ->where(1)
	372	+ ->andWhere([
	373	+ $dbo->qn('idorder') . ' = 0',
	374	+ $dbo->qn('idorder') . ' IS NULL',
	375	+ ], 'OR')
	376	+ );
	377	+
	378	+ // set involved busy record IDs, if any (column is `idbusy`)
	379	+ $hanging_busy_ids = array_column($dbo->loadAssocList(), 'idbusy');
	380	+
	381	+ // identify the occupied records whose busy relations have empty booking IDs
	382	+ $dbo->setQuery(
	383	+ $dbo->getQuery(true)
	384	+ ->select($dbo->qn('b') . '.*')
	385	+ ->select($dbo->qn('ob.idorder'))
	386	+ ->select($dbo->qn('o.id', 'res_id'))
	387	+ ->from($dbo->qn('#__vikbooking_busy', 'b'))
	388	+ ->leftJoin($dbo->qn('#__vikbooking_ordersbusy', 'ob') . ' ON ' . $dbo->qn('b.id') . ' = ' . $dbo->qn('ob.idbusy'))
	389	+ ->leftJoin($dbo->qn('#__vikbooking_orders', 'o') . ' ON ' . $dbo->qn('ob.idorder') . ' = ' . $dbo->qn('o.id'))
	390	+ ->where($dbo->qn('b.checkout') . ' >= ' . time())
	391	+ ->andWhere([
	392	+ $dbo->qn('ob.idorder') . ' = 0',
	393	+ $dbo->qn('ob.idorder') . ' IS NULL',
	394	+ $dbo->qn('o.id') . ' IS NULL',
	395	+ ], 'OR')
	396	+ );
	397	+
	398	+ $ghost_records = $dbo->loadAssocList();
	399	+
	400	+ // merge involved busy record IDs, if any (column is `id`)
	401	+ $hanging_busy_ids = array_merge($hanging_busy_ids, array_column($ghost_records, 'id'));
	402	+
	403	+ // map to integer and filter involved busy record IDs for removal
	404	+ $hanging_busy_ids = array_values(array_unique(array_filter(array_map('intval', $hanging_busy_ids))));
	405	+
	406	+ if ($hanging_busy_ids) {
	407	+ // delete records involved
	408	+ $dbo->setQuery(
	409	+ $dbo->getQuery(true)
	410	+ ->delete($dbo->qn('#__vikbooking_busy'))
	411	+ ->where($dbo->qn('id') . ' IN (' . implode(', ', $hanging_busy_ids) . ')')
	412	+ );
	413	+
	414	+ $dbo->execute();
	415	+
	416	+ $affected += (int) $dbo->getAffectedRows();
	417	+ }
	418	+
	419	+ if ($ghost_records && class_exists('VikChannelManager')) {
	420	+ // ghost records were removed, but OTAs may require a sync of availability
	421	+ $listing_times_pool = [];
	422	+ foreach ($ghost_records as $ghost_record) {
	423	+ if (empty($ghost_record['idroom']) || empty($ghost_record['checkin']) || empty($ghost_record['checkout'])) {
	424	+ continue;
	425	+ }
	426	+ if (!isset($listing_times_pool[$ghost_record['idroom']])) {
	427	+ $listing_times_pool[$ghost_record['idroom']] = [];
	428	+ }
	429	+ // push listing check-in and check-out date times involved
	430	+ $listing_times_pool[$ghost_record['idroom']][] = $ghost_record['checkin'];
	431	+ $listing_times_pool[$ghost_record['idroom']][] = $ghost_record['checkout'];
	432	+ }
	433	+
	434	+ $min_ts = 0;
	435	+ $max_ts = 0;
	436	+ foreach ($listing_times_pool as $listing_id => $listing_times) {
	437	+ $min_ts = $min_ts ? min(min($listing_times), $min_ts) : min($listing_times);
	438	+ $max_ts = $max_ts ? max(max($listing_times), $max_ts) : max($listing_times);
	439	+ }
	440	+
	441	+ if ($min_ts && $max_ts) {
	442	+ // no past dates allowed
	443	+ $min_ts = $min_ts < time() ? time() : $min_ts;
	444	+
	445	+ // prepare the options for an auto bulk-action of type "availability"
	446	+ VikChannelManager::autoBulkActions([
	447	+ 'from_date' => date('Y-m-d', $min_ts),
	448	+ 'to_date' => date('Y-m-d', $max_ts),
	449	+ 'forced_rooms' => array_keys($listing_times_pool),
	450	+ 'update' => 'availability',
	451	+ ]);
	452	+ }
	453	+ }
	454	+
	455	+ // return the number of affected records
	456	+ return $affected;
	457	+ }
	458	+
	459	+ /**
117	460	* Performs a pricing snapshot of a listing for a range of dates, by cleaning up
118	461	* all previous seasonal rates and by re-creating only the needed records. Useful
119	462	* for those listings who had hundreds of pricing alteration updates for the same
120	463	* calendar day. Strongly recommended only for those who use a SINGLE rate plan.
		@@ -121,17 +464,21 @@
121	464	*
122	465	* @return array Seasons snapshot operation results.
123	466	*
124	467	* @throws Exception
	468	+ *
	469	+ * @since 1.18.3 (J) - 1.8.3 (WP) performance skip list preferences applied.
125	470	*/
126	471	public static function listingSeasonSnapshot()
127	472	{
128	473	$dbo = JFactory::getDbo();
129	474
130		- $listing_id = (int) (static::$options['listing_id'] ?? null);
131		- $id_price = (int) (static::$options['id_price'] ?? null);
132		- $from_date = static::$options['from_date'] ?? date('Y-m-d');
133		- $to_date = static::$options['to_date'] ?? date('Y-m-d', strtotime('+3 months'));
	475	+ $listing_id = (int) (static::$options['listing_id'] ?? null);
	476	+ $id_price = (int) (static::$options['id_price'] ?? null);
	477	+ $from_date = static::$options['from_date'] ?? date('Y-m-d');
	478	+ $to_date = static::$options['to_date'] ?? date('Y-m-d', strtotime('+3 months'));
	479	+ $skip_derived = (bool) (static::$options['skip_derived'] ?? true);
	480	+ $use_cache = (bool) (static::$options['use_cache'] ?? false);
134	481
135	482	if (!$listing_id) {
136	483	throw new Exception('Missing required listing ID.', 400);
137	484	}
		@@ -139,9 +486,19 @@
139	486	if (!$from_date || !$to_date || strtotime($from_date) > strtotime($to_date)) {
140	487	throw new Exception('Invalid dates provided.', 400);
141	488	}
142	489
	490	+ // list of operations that should be skipped
	491	+ $skip_checks = VBOFactory::getConfig()->getArray('performance_cleaner_skip_list', []);
	492	+
	493	+ // ensure season records cleaning is not disabled, unless forced
	494	+ if (in_array('seasons', $skip_checks) && !(static::$options['forced'] ?? false)) {
	495	+ // abort the process
	496	+ return [];
	497	+ }
	498	+
143	499	// obtain a pricing snapshot for the given listing and dates
	500	+ // given rate plan ID or main one will be used (single rate plan only)
144	501	$snapshot = VBOModelPricing::getInstance()->getRoomRates([
145	502	'from_date' => $from_date,
146	503	'to_date' => $to_date,
147	504	'id_room' => $listing_id,
		@@ -147,8 +504,9 @@
147	504	'id_room' => $listing_id,
148	505	'id_price' => $id_price,
149	506	'all_rplans' => false,
150	507	'restrictions' => false,
	508	+ 'use_cache' => $use_cache,
151	509	]);
152	510
153	511	// confirm the rate plan ID
154	512	foreach ($snapshot as $dayrate) {
		@@ -166,9 +524,9 @@
166	524	}
167	525	}
168	526
169	527	if (!$involved_seasons) {
170		- throw new Exception('No seasonal records to clean for the given listing and dates.', 500);
	528	+ throw new Exception('No seasonal records to clean for the given listing and dates.', 406);
171	529	}
172	530
173	531	// clean up database records
174	532	$dbo->setQuery(
		@@ -229,16 +587,18 @@
229	587	foreach ($season_intervals as $season_snap) {
230	588	try {
231	589	// set the new rate for this calculated interval
232	590	VBOModelPricing::getInstance([
233		- 'from_date' => $season_snap['from'],
234		- 'to_date' => $season_snap['to'],
235		- 'id_room' => $listing_id,
236		- 'id_price' => $id_price,
237		- 'rate' => $season_snap['cost'],
238		- 'min_los' => 0,
239		- 'max_los' => 0,
240		- 'update_otas' => false,
	591	+ 'from_date' => $season_snap['from'],
	592	+ 'to_date' => $season_snap['to'],
	593	+ 'id_room' => $listing_id,
	594	+ 'id_price' => $id_price,
	595	+ 'rate' => $season_snap['cost'],
	596	+ 'min_los' => 0,
	597	+ 'max_los' => 0,
	598	+ 'update_otas' => false,
	599	+ 'skip_derived' => $skip_derived,
	600	+ 'use_cache' => $use_cache,
241	601	])->modifyRateRestrictions();
242	602	} catch (Exception $e) {
243	603	// silently push the error
244	604	$errors[] = sprintf('%s - %s: %s', $season_snap['from'], $season_snap['to'], $e->getMessage());