yatra / app / Repositories / TripCategoryRepository.php

TripCategoryRepository.php in Yatra – Travel Booking & Tour Operator Software 3.0.2.7, at app/Repositories/TripCategoryRepository.php

343 lines 10.3 KB

Raw Download Zip

1	<?php
2
3	declare(strict_types=1);
4
5	namespace Yatra\Repositories;
6
7	use Yatra\Constants\ClassificationTypes;
8
9	/**
10	* Trip Category Repository
11	* Handles database operations for trip categories
12	*/
13	class TripCategoryRepository extends BaseRepository
14	{
15	/**
16	* Rich text fields
17	*/
18	protected array $richTextFields = ['description'];
19
20	/**
21	* Integer fields
22	*/
23	protected array $integerFields = ['id', 'parent_id', 'created_by', 'updated_by'];
24
25	/**
26	* Get table name
27	*/
28	protected function getTableName(): string
29	{
30	// Use ClassificationsTable for categories (type = 'category')
31	return \Yatra\Database\Tables\ClassificationsTable::getTableName();
32	}
33
34	/**
35	* Build where clause (override to filter by type='category')
36	*/
37	protected function buildWhereClause(array $args): string
38	{
39	$where = parent::buildWhereClause($args);
40
41	// Always filter by type = 'category' for this repository
42	if ($where) {
43	$where .= sprintf(" AND type = '%s'", ClassificationTypes::CATEGORY);
44	} else {
45	$where = sprintf("WHERE type = '%s'", ClassificationTypes::CATEGORY);
46	}
47
48	return $where;
49	}
50
51	/**
52	* Find by slug
53	*/
54	public function findBySlug(string $slug): ?\stdClass
55	{
56	$table = esc_sql($this->table);
57	$result = $this->wpdb->get_row(
58	$this->wpdb->prepare(
59	"SELECT * FROM `{$table}` WHERE slug = %s AND type = %s",
60	$slug,
61	ClassificationTypes::CATEGORY
62	)
63	);
64
65	return $result ?: null;
66	}
67
68	/**
69	* Get published categories
70	*/
71	public function getPublished(array $args = []): array
72	{
73	$args['where']['status'] = 'publish';
74	return $this->all($args);
75	}
76
77	/**
78	* Get categories by status
79	*/
80	public function getByStatus(string $status, array $args = []): array
81	{
82	$args['where']['status'] = $status;
83	return $this->all($args);
84	}
85
86	/**
87	* Get top-level categories (no parent)
88	*/
89	public function getTopLevel(array $args = []): array
90	{
91	$args['where']['parent_id'] = null;
92	return $this->all($args);
93	}
94
95	/**
96	* Get subcategories by parent ID
97	*/
98	public function getSubcategories(int $parentId, array $args = []): array
99	{
100	$args['where']['parent_id'] = $parentId;
101	return $this->all($args);
102	}
103
104	/**
105	* Get category with subcategories
106	*/
107	public function getWithSubcategories(int $id): ?\stdClass
108	{
109	$category = $this->find($id);
110	if (!$category) {
111	return null;
112	}
113
114	$subcategories = $this->getSubcategories($id);
115	$category->subcategories = $subcategories;
116
117	return $category;
118	}
119
120	/**
121	* Get all categories with subcategories (hierarchical)
122	*/
123	public function getHierarchical(array $args = []): array
124	{
125	// Get all top-level categories
126	$topLevelArgs = $args;
127	$topLevelArgs['where']['parent_id'] = null;
128	$categories = $this->all($topLevelArgs);
129
130	// For each category, get its subcategories
131	foreach ($categories as $category) {
132	$subArgs = $args;
133	unset($subArgs['where']['parent_id']); // Remove parent_id filter for subcategories
134	$category->subcategories = $this->getSubcategories((int) $category->id, $subArgs);
135	}
136
137	return $categories;
138	}
139
140	/**
141	* Check if category has subcategories
142	*/
143	public function hasSubcategories(int $id): bool
144	{
145	$table = esc_sql($this->table);
146	$count = $this->wpdb->get_var(
147	$this->wpdb->prepare(
148	"SELECT COUNT(*) FROM `{$table}` WHERE parent_id = %d",
149	$id
150	)
151	);
152
153	return (int) $count > 0;
154	}
155
156	/**
157	* Check if category can be deleted (no subcategories and not used by trips)
158	*/
159	public function canDelete(int $id): bool
160	{
161	// Check if has subcategories
162	if ($this->hasSubcategories($id)) {
163	return false;
164	}
165
166	// Check if used by trips (you may want to add this check later)
167	// For now, we'll allow deletion if no subcategories
168
169	return true;
170	}
171
172	/**
173	* Get published categories with trip counts, ratings, and pricing stats
174	*/
175	public function getPublishedWithTripCounts(): array
176	{
177	$table = esc_sql($this->table);
178
179	// Use TripRepository for trips table
180	$tripRepository = new \Yatra\Repositories\TripRepository();
181	$trip_table = esc_sql($tripRepository->getTableName());
182
183	// Use TripClassificationsTable for trip-category relationships
184	$trip_cat_table = esc_sql(\Yatra\Database\Tables\TripClassificationsTable::getTableName());
185	$reviews_table = esc_sql(\Yatra\Database\Tables\ReviewsTable::getTableName());
186
187	// TripClassificationsTable uses classification_id + classification_type (not category_id).
188	// ClassificationsTable holds all taxonomy rows — restrict to type = category.
189	$query = $this->wpdb->prepare(
190	"SELECT c.*,
191	COUNT(DISTINCT tc.trip_id) AS trips_count,
192	COALESCE(AVG(r.rating), 0) AS avg_rating,
193	GROUP_CONCAT(DISTINCT tc.trip_id) AS trip_ids
194	FROM `{$table}` c
195	LEFT JOIN `{$trip_cat_table}` tc
196	ON tc.classification_id = c.id
197	AND tc.classification_type = %s
198	LEFT JOIN `{$trip_table}` t ON t.id = tc.trip_id AND t.status = 'publish'
199	LEFT JOIN `{$reviews_table}` r ON r.trip_id = t.id AND r.status = 'approved'
200	WHERE c.type = %s AND c.status = 'publish'
201	GROUP BY c.id
202	ORDER BY c.name ASC",
203	ClassificationTypes::CATEGORY,
204	ClassificationTypes::CATEGORY
205	);
206
207	$categories = $this->wpdb->get_results($query) ?: [];
208
209	// Calculate starting prices for each category
210	foreach ($categories as $category) {
211	$category->starting_price = 0;
212	if (!empty($category->trip_ids)) {
213	$trip_ids = explode(',', $category->trip_ids);
214	$category->starting_price = $this->computeStartingPriceForTripIds($trip_ids);
215	}
216	}
217
218	return $categories;
219	}
220
221	/**
222	* Compute starting price for given trip IDs
223	*/
224	private function computeStartingPriceForTripIds(array $trip_ids): float
225	{
226	if (empty($trip_ids)) {
227	return 0;
228	}
229
230	// Use TripRepository for trips table
231	$tripRepository = new \Yatra\Repositories\TripRepository();
232	$trip_table = esc_sql($tripRepository->getTableName());
233	$placeholders = implode(',', array_fill(0, count($trip_ids), '%d'));
234
235	$query = "SELECT MIN(CAST(original_price AS DECIMAL(10,2))) as min_price
236	FROM `{$trip_table}`
237	WHERE id IN ({$placeholders}) AND original_price > 0";
238
239	$min_price = $this->wpdb->get_var(
240	$this->wpdb->prepare($query, ...$trip_ids)
241	);
242
243	return $min_price ? (float) $min_price : 0;
244	}
245
246	/**
247	* Search categories
248	*/
249	public function search(string $search, array $args = []): array
250	{
251	$table = esc_sql($this->table);
252	$where = $this->buildWhereClause($args);
253	$order = $this->buildOrderClause($args);
254	$limit = $this->buildLimitClause($args);
255
256	$search_where = $this->wpdb->prepare(
257	"WHERE (name LIKE %s OR slug LIKE %s OR description LIKE %s)",
258	'%' . $this->wpdb->esc_like($search) . '%',
259	'%' . $this->wpdb->esc_like($search) . '%',
260	'%' . $this->wpdb->esc_like($search) . '%'
261	);
262
263	if ($where) {
264	$search_where .= ' AND ' . str_replace('WHERE ', '', $where);
265	}
266
267	$query = "SELECT * FROM `{$table}` {$search_where} {$order} {$limit}";
268
269	return $this->wpdb->get_results($query) ?: [];
270	}
271
272	/**
273	* Get trip count for a trip category
274	*
275	* @param int $categoryId Category ID
276	* @return int Number of trips with this category
277	*/
278	public function getTripCount(int $categoryId): int
279	{
280	global $wpdb;
281	$tripRepository = new \Yatra\Repositories\TripRepository();
282	$tripsTable = $tripRepository->getTableName();
283
284	// Use TripClassificationsTable for trip-category relationships
285	$tripClassificationsTable = \Yatra\Database\Tables\TripClassificationsTable::getTableName();
286
287	return (int) $wpdb->get_var($wpdb->prepare(
288	"SELECT COUNT(DISTINCT t.id)
289	FROM `{$tripsTable}` t
290	INNER JOIN `{$tripClassificationsTable}` tc ON tc.trip_id = t.id
291	WHERE tc.classification_id = %d
292	AND tc.classification_type = %s
293	AND t.status != 'trash'",
294	$categoryId,
295	ClassificationTypes::CATEGORY
296	));
297	}
298
299	/**
300	* Get trip count for trip category (direct field method)
301	*
302	* @param int $categoryId Category ID
303	* @return int Number of trips with this category
304	*/
305	public function getTripCountDirect(int $categoryId): int
306	{
307	global $wpdb;
308	$tripRepository = new \Yatra\Repositories\TripRepository();
309	$tripTable = $tripRepository->getTableName();
310
311	// Use TripClassificationsTable for trip-category relationships
312	$tripCatTable = \Yatra\Database\Tables\TripClassificationsTable::getTableName();
313
314	return (int) $wpdb->get_var($wpdb->prepare(
315	"SELECT COUNT(DISTINCT t.id)
316	FROM `{$tripTable}` t
317	INNER JOIN `{$tripCatTable}` tc ON tc.trip_id = t.id
318	WHERE tc.classification_id = %d
319	AND tc.classification_type = %s
320	AND t.status != 'trash'",
321	$categoryId,
322	ClassificationTypes::CATEGORY
323	));
324	}
325
326	/**
327	* Get published categories for templates
328	*/
329	public function getPublishedCategories(): array
330	{
331	return $this->getPublished();
332	}
333
334	/**
335	* Get difficulty levels for templates (published rows from classifications).
336	*/
337	public function getDifficultyLevels(): array
338	{
339	return (new \Yatra\Repositories\DifficultyLevelRepository())->getPublished();
340	}
341	}
342
343