yatra / app / Scripts / DepartureDeduplicationScript.php

DepartureDeduplicationScript.php in Yatra – Travel Booking & Tour Operator Software trunk, at app/Scripts/DepartureDeduplicationScript.php

138 lines 3.6 KB

Raw Download Zip

1	<?php
2	/**
3	* Departure Deduplication Script
4	*
5	* Removes duplicate departure entries for the same trip and date combination.
6	* This script helps maintain data integrity by ensuring each trip-date pair
7	* has only one departure record.
8	*
9	* @package Yatra\Scripts
10	* @since 3.0.0
11	*/
12
13	declare(strict_types=1);
14
15	namespace Yatra\Scripts;
16
17	use Yatra\Core\Database;
18	use Yatra\Services\DepartureService;
19
20	/**
21	* Class DepartureDeduplicationScript
22	*
23	* Handles the removal of duplicate departure records to maintain data integrity.
24	*/
25	class DepartureDeduplicationScript
26	{
27	/**
28	* Database instance
29	*
30	* @var Database
31	*/
32	private Database $db;
33
34	/**
35	* Constructor
36	*/
37	public function __construct()
38	{
39	$this->db = new Database();
40	}
41
42	/**
43	* Run the deduplication process
44	*
45	* @return array Results of the deduplication process
46	*/
47	public function run(): array
48	{
49	$results = [
50	'duplicates_found' => 0,
51	'duplicates_removed' => 0,
52	'errors' => []
53	];
54
55	try {
56	// Find all duplicate departure records
57	$duplicates = $this->findDuplicateDepartures();
58	$results['duplicates_found'] = count($duplicates);
59
60	if (!empty($duplicates)) {
61	// Remove duplicates, keeping the first occurrence
62	$results['duplicates_removed'] = $this->removeDuplicates($duplicates);
63	}
64	} catch (\Exception $e) {
65	$results['errors'][] = $e->getMessage();
66	}
67
68	return $results;
69	}
70
71	/**
72	* Find duplicate departure records
73	*
74	* @return array Array of duplicate departure IDs
75	*/
76	private function findDuplicateDepartures(): array
77	{
78	$sql = "
79	SELECT GROUP_CONCAT(id) as duplicate_ids, trip_id, date, COUNT(*) as count
80	FROM {$this->db->getPrefix()}yatra_departures
81	WHERE status != 'trash'
82	GROUP BY trip_id, date
83	HAVING count > 1
84	";
85
86	$duplicates = $this->db->getResults($sql);
87	$duplicateIds = [];
88
89	foreach ($duplicates as $duplicate) {
90	// Get all IDs except the first one (keep the first occurrence)
91	$ids = explode(',', $duplicate->duplicate_ids);
92	array_shift($ids); // Remove the first ID (keep it)
93	$duplicateIds = array_merge($duplicateIds, $ids);
94	}
95
96	return $duplicateIds;
97	}
98
99	/**
100	* Remove duplicate departure records
101	*
102	* @param array $duplicateIds Array of duplicate departure IDs
103	* @return int Number of duplicates removed
104	*/
105	private function removeDuplicates(array $duplicateIds): int
106	{
107	if (empty($duplicateIds)) {
108	return 0;
109	}
110
111	$placeholders = str_repeat('?,', count($duplicateIds) - 1) . '?';
112	$sql = "
113	DELETE FROM {$this->db->getPrefix()}yatra_departures
114	WHERE id IN ($placeholders)
115	";
116
117	return $this->db->execute($sql, $duplicateIds);
118	}
119
120	/**
121	* Get a summary of departure data before deduplication
122	*
123	* @return array Summary statistics
124	*/
125	public function getSummary(): array
126	{
127	$sql = "
128	SELECT
129	COUNT(*) as total_departures,
130	COUNT(DISTINCT CONCAT(trip_id, '-', date)) as unique_trip_dates,
131	COUNT(*) - COUNT(DISTINCT CONCAT(trip_id, '-', date)) as potential_duplicates
132	FROM {$this->db->getPrefix()}yatra_departures
133	WHERE status != 'trash'
134	";
135
136	return $this->db->getRow($sql);
137	}
138	}