← All changes | backend/src/Repository/Entry/EntryRepository.php +479 -0 0.6.1 → 1.5 View file →

		@@ -1,0 +1,479 @@
	1	+<?php
	2	+
	3	+/**
	4	+ * @copyright © Melograno Venture Studio. All rights reserved.
	5	+ * @licence See LICENCE.md for license details.
	6	+ */
	7	+
	8	+namespace IvyForms\Repository\Entry;
	9	+
	10	+use IvyForms\Common\Exceptions\InvalidArgumentException;
	11	+use IvyForms\Common\Exceptions\QueryExecutionException;
	12	+use IvyForms\Common\Helpers\EntryBatchUpdater;
	13	+use IvyForms\Common\Helpers\EntryQueryHelper;
	14	+use IvyForms\Entity\Entry\Entry;
	15	+use IvyForms\Factory\Entry\EntryFactory;
	16	+use IvyForms\Repository\AbstractRepository;
	17	+use IvyForms\Repository\VisibleFormIdsQueryFilter;
	18	+use IvyForms\Services\InstallActions\DB\Entry\EntriesTable;
	19	+use IvyForms\Services\InstallActions\DB\EntryField\EntryFieldsTable as EntryFieldsTable;
	20	+use IvyForms\Services\InstallActions\DB\Form\FormsTable;
	21	+use IvyForms\Services\Translations\BackendStrings;
	22	+
	23	+class EntryRepository extends AbstractRepository implements EntryRepositoryInterface
	24	+{
	25	+ public const FACTORY = EntryFactory::class;
	26	+
	27	+ // Column constants
	28	+ public const COLUMN_STATUS = 'status';
	29	+ public const COLUMN_STARRED = 'starred';
	30	+
	31	+ /**
	32	+ * Add entry to the database
	33	+ *
	34	+ * @param Entry $entity
	35	+ *
	36	+ * @return int
	37	+ *
	38	+ * @throws QueryExecutionException
	39	+ */
	40	+ public function add($entity): int
	41	+ {
	42	+ $data = $entity->toArray(true);
	43	+
	44	+ $dateCreated = !empty($data['dateCreated']) ? (string)$data['dateCreated'] : current_time('mysql');
	45	+ $dateEdited = !empty($data['dateEdited']) ? (string)$data['dateEdited'] : $dateCreated;
	46	+
	47	+ $result = $this->wpdb->insert(
	48	+ $this->table,
	49	+ [
	50	+ 'formId' => $data['formId'],
	51	+ 'userId' => $data['userId'],
	52	+ 'dateCreated' => $dateCreated,
	53	+ 'dateEdited' => $dateEdited,
	54	+ 'status' => $data['status'],
	55	+ 'ipAddress' => $data['ipAddress'],
	56	+ 'userAgent' => $data['userAgent'],
	57	+ 'sourceURL' => $data['sourceURL'],
	58	+ 'starred' => $data['starred'] ? 1 : 0,
	59	+ ],
	60	+ ['%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%d']
	61	+ );
	62	+
	63	+ if ($result === false) {
	64	+ throw new QueryExecutionException(
	65	+ BackendStrings::getExceptionStrings()['unable_to_add_data'] . __CLASS__
	66	+ );
	67	+ }
	68	+
	69	+ return $this->wpdb->insert_id;
	70	+ }
	71	+
	72	+ /**
	73	+ * Update entry in the database
	74	+ *
	75	+ * @param int $id
	76	+ * @param Entry $entity
	77	+ *
	78	+ * @return bool
	79	+ *
	80	+ * @throws QueryExecutionException
	81	+ */
	82	+ public function update(int $id, $entity): bool
	83	+ {
	84	+ $result = $this->wpdb->update(
	85	+ $this->table,
	86	+ [
	87	+ 'dateEdited' => current_time('mysql'),
	88	+ ],
	89	+ ['id' => $id],
	90	+ ['%s'],
	91	+ ['%d']
	92	+ );
	93	+
	94	+ if ($result === false) {
	95	+ throw new QueryExecutionException(
	96	+ BackendStrings::getExceptionStrings()['unable_update_data'] . __CLASS__
	97	+ );
	98	+ }
	99	+
	100	+ return (bool)$result;
	101	+ }
	102	+
	103	+ /**
	104	+ * Update the starred status of an entry.
	105	+ *
	106	+ * @param int $entryId
	107	+ * @param bool $value
	108	+ *
	109	+ * @return void
	110	+ * @throws QueryExecutionException
	111	+ */
	112	+ public function updateEntryStarred(int $entryId, bool $value): void
	113	+ {
	114	+ $result = $this->wpdb->update(
	115	+ $this->table,
	116	+ [
	117	+ 'starred' => $value ? 1 : 0,
	118	+ 'dateEdited' => current_time('mysql'),
	119	+ ],
	120	+ ['id' => $entryId],
	121	+ ['%d', '%s'],
	122	+ ['%d']
	123	+ );
	124	+
	125	+ if ($result === false) {
	126	+ throw new QueryExecutionException(
	127	+ BackendStrings::getExceptionStrings()['unable_update_starred'] . __CLASS__
	128	+ );
	129	+ }
	130	+ }
	131	+
	132	+ /**
	133	+ * Update the status of an entry.
	134	+ *
	135	+ * @param int $entryId
	136	+ * @param string $status
	137	+ *
	138	+ * @return void
	139	+ * @throws QueryExecutionException
	140	+ */
	141	+ public function updateEntryStatus(int $entryId, string $status): void
	142	+ {
	143	+ $result = $this->wpdb->update(
	144	+ $this->table,
	145	+ [
	146	+ 'status' => $status,
	147	+ 'dateEdited' => current_time('mysql'),
	148	+ ],
	149	+ ['id' => $entryId],
	150	+ ['%s', '%s'],
	151	+ ['%d']
	152	+ );
	153	+
	154	+ if ($result === false) {
	155	+ throw new QueryExecutionException(
	156	+ BackendStrings::getExceptionStrings()['unable_update_status'] . __CLASS__
	157	+ );
	158	+ }
	159	+ }
	160	+
	161	+ /**
	162	+ * Search for entries with extended logic (field value, form name) and return paginated results with meta.
	163	+ *
	164	+ * @param array<string, mixed>|null $params
	165	+ * @return array<mixed>
	166	+ * @throws InvalidArgumentException|QueryExecutionException
	167	+ */
	168	+ public function search(?array $params): array
	169	+ {
	170	+ $queryParams = [];
	171	+ $whereClauses = $this->buildWhereClauses($params, $queryParams);
	172	+
	173	+ // Always join entry fields and forms for search/filter/sort
	174	+ $entriesTable = EntriesTable::getTableName();
	175	+ $join = EntryQueryHelper::getEntryJoinClause();
	176	+ $formsTable = FormsTable::getTableName();
	177	+
	178	+ // Sorting
	179	+ $sortableColumns = $this->getSortableColumns();
	180	+ $sortBy = $params['orderBy'] ?? 'id';
	181	+ $order = strtolower($params['order'] ?? 'asc') === 'asc' ? 'ASC' : 'DESC';
	182	+ // Map 'formName' to 'f.name' for sorting
	183	+ $sortColumn = 'e.id';
	184	+ if ($sortBy === 'formName') {
	185	+ $sortColumn = 'f.name';
	186	+ } elseif (in_array($sortBy, $sortableColumns, true)) {
	187	+ $sortColumn = 'e.' . $sortBy;
	188	+ }
	189	+
	190	+ // Pagination
	191	+ $page = max(($params['page'] ?? 1), 1);
	192	+ $perPage = $params['perPage'] ?? 10;
	193	+ $queryBuild = EntryQueryHelper::buildEntrySelectQuery(
	194	+ $entriesTable,
	195	+ $join,
	196	+ $whereClauses,
	197	+ $sortColumn,
	198	+ $order,
	199	+ $queryParams,
	200	+ $perPage,
	201	+ $page
	202	+ );
	203	+ $sql = $queryBuild['sql'];
	204	+ $mainParams = $queryBuild['params'];
	205	+ $results = $this->wpdb->get_results($this->wpdb->prepare($sql, $mainParams), ARRAY_A);
	206	+ if ($results === false) {
	207	+ throw new QueryExecutionException(
	208	+ BackendStrings::getExceptionStrings()['unable_search_entries'] . __CLASS__
	209	+ );
	210	+ }
	211	+
	212	+ // Total count for pagination meta (exclude LIMIT/OFFSET)
	213	+ $countSql = "SELECT COUNT(DISTINCT e.id) FROM {$entriesTable} e $join WHERE {$whereClauses}";
	214	+ $total = (int)$this->wpdb->get_var($this->wpdb->prepare($countSql, $queryParams));
	215	+
	216	+ // Fetch form names for all unique formIds in the results
	217	+ $formIdToName = [];
	218	+ $formNamesQuery = EntryQueryHelper::getFormNamesByIdsQuery($results, $formsTable);
	219	+ if (!empty($formNamesQuery['sql'])) {
	220	+ $formRows = $this->wpdb->get_results(
	221	+ $this->wpdb->prepare($formNamesQuery['sql'], $formNamesQuery['params']),
	222	+ ARRAY_A
	223	+ );
	224	+ if ($formRows === null) {
	225	+ throw new QueryExecutionException(
	226	+ BackendStrings::getExceptionStrings()['unable_get_form_names'] . __CLASS__
	227	+ );
	228	+ }
	229	+ foreach ($formRows as $row) {
	230	+ $formIdToName[$row['id']] = $row['name'];
	231	+ }
	232	+ }
	233	+
	234	+ return [
	235	+ 'data' => $results,
	236	+ 'meta' => [
	237	+ 'page' => $page,
	238	+ 'perPage' => $perPage,
	239	+ 'total' => $total,
	240	+ ],
	241	+ 'formIdToName' => $formIdToName,
	242	+ ];
	243	+ }
	244	+
	245	+ /**
	246	+ * Override addSearchClauses to use EntryQueryHelper for search WHERE clause construction.
	247	+ */
	248	+ protected function addSearchClauses(?array $params, array &$whereClauses, array &$queryParams): void
	249	+ {
	250	+ $searchResult = EntryQueryHelper::buildSearchWhereClause($params);
	251	+ if ($searchResult['where']) {
	252	+ $whereClauses[] = $searchResult['where'];
	253	+ $queryParams = array_merge($queryParams, $searchResult['params']);
	254	+ }
	255	+ }
	256	+
	257	+ /**
	258	+ * Override addFilterClauses to use EntryQueryHelper for filter WHERE clause construction.
	259	+ * @param array<string, mixed>|null $params
	260	+ */
	261	+ protected function addFilterClauses(?array $params, array &$whereClauses, array &$queryParams): void
	262	+ {
	263	+ $filterableColumns = $this->getFilterableColumns();
	264	+ $filterResult = EntryQueryHelper::buildFilterWhereClause($params, $filterableColumns);
	265	+ if (!empty($filterResult['where'])) {
	266	+ $whereClauses = array_merge($whereClauses, $filterResult['where']);
	267	+ // Ensure all values are strings
	268	+ $queryParams = array_merge($queryParams, array_map('strval', $filterResult['params']));
	269	+ }
	270	+
	271	+ VisibleFormIdsQueryFilter::apply($params, $whereClauses, $queryParams, 'e.formId');
	272	+ }
	273	+
	274	+ /**
	275	+ * Get the columns that can be searched in the database (including virtual columns for search).
	276	+ *
	277	+ * @return array<string>
	278	+ */
	279	+ protected function getSearchableColumns(): array
	280	+ {
	281	+ return [
	282	+ 'id',
	283	+ 'formId',
	284	+ 'fieldValue', // virtual, from EntryFields table
	285	+ 'name', // virtual, from Forms table
	286	+ ];
	287	+ }
	288	+
	289	+ /**
	290	+ * Get filterable columns
	291	+ *
	292	+ * @return array<string>
	293	+ */
	294	+ protected function getFilterableColumns(): array
	295	+ {
	296	+ return ['starred', 'status', 'formId', 'userId'];
	297	+ }
	298	+
	299	+ /**
	300	+ * Get sortable columns
	301	+ *
	302	+ * @return array<string>
	303	+ */
	304	+ protected function getSortableColumns(): array
	305	+ {
	306	+ return ['id', 'dateCreated', 'formName'];
	307	+ }
	308	+
	309	+ /**
	310	+ * Get the date column
	311	+ *
	312	+ * @return string
	313	+ */
	314	+ protected function getDateColumn(): string
	315	+ {
	316	+ return 'e.dateCreated';
	317	+ }
	318	+
	319	+ /**
	320	+ * @param int $entryId
	321	+ *
	322	+ * @return bool
	323	+ * @throws QueryExecutionException
	324	+ */
	325	+ public function exists(int $entryId): bool
	326	+ {
	327	+ $query = $this->wpdb->prepare(
	328	+ "SELECT COUNT(*) FROM {$this->table} WHERE id = %d",
	329	+ $entryId
	330	+ );
	331	+
	332	+ $count = $this->wpdb->get_var($query);
	333	+ if ($count === false) {
	334	+ throw new QueryExecutionException(
	335	+ BackendStrings::getExceptionStrings()['unable_find_by_id'] . __CLASS__
	336	+ );
	337	+ }
	338	+ return (int)$count > 0;
	339	+ }
	340	+
	341	+ /**
	342	+ * Get the count of entries for a specific form.
	343	+ *
	344	+ * @param int $formId
	345	+ * @return int
	346	+ * @throws QueryExecutionException
	347	+ */
	348	+ public function getCountByFormId(int $formId): int
	349	+ {
	350	+ $queryData = EntryQueryHelper::getCountByFormIdQuery($this->table, $formId);
	351	+ $query = $this->wpdb->prepare($queryData['sql'], $queryData['params']);
	352	+ $count = $this->wpdb->get_var($query);
	353	+ if ($count === null) {
	354	+ throw new QueryExecutionException(
	355	+ BackendStrings::getExceptionStrings()['unable_find_by_id'] . __CLASS__
	356	+ );
	357	+ }
	358	+ return (int)$count;
	359	+ }
	360	+
	361	+ /**
	362	+ * Get the count of entries for the filter dropdown.
	363	+ *
	364	+ * @param array<string, mixed>|null $params
	365	+ * @return array<string, int>
	366	+ * @throws QueryExecutionException
	367	+ */
	368	+ public function getFilterCount(?array $params = null): array
	369	+ {
	370	+ $queryData = EntryQueryHelper::getFilterCountQuery($this->table, $params);
	371	+ $result = null;
	372	+ if (!empty($queryData['params'])) {
	373	+ $result = $this->wpdb->get_row(
	374	+ $this->wpdb->prepare($queryData['sql'], ...$queryData['params']),
	375	+ ARRAY_A
	376	+ );
	377	+ }
	378	+ if ($result === null) {
	379	+ $result = $this->wpdb->get_row($queryData['sql'], ARRAY_A);
	380	+ }
	381	+ if ($result === null) {
	382	+ throw new QueryExecutionException(
	383	+ BackendStrings::getExceptionStrings()['unable_search_entries'] . __CLASS__
	384	+ );
	385	+ }
	386	+ return array_map('intval', $result ?: [
	387	+ 'readTrueCount' => 0,
	388	+ 'readFalseCount' => 0,
	389	+ 'starredTrueCount' => 0,
	390	+ 'starredFalseCount' => 0,
	391	+ ]);
	392	+ }
	393	+
	394	+ /**
	395	+ * Get entry fields for the given entries.
	396	+ *
	397	+ * @param array<int, array<string, mixed>> $entries
	398	+ * @return array<mixed>
	399	+ *
	400	+ * @throws QueryExecutionException|InvalidArgumentException
	401	+ */
	402	+ public function getEntryFields(array $entries): array
	403	+ {
	404	+ $queryData = EntryQueryHelper::getEntryFieldsQuery($entries);
	405	+ if (empty($queryData['sql'])) {
	406	+ return [];
	407	+ }
	408	+ $results = $this->wpdb->get_results(
	409	+ $this->wpdb->prepare($queryData['sql'], $queryData['params']),
	410	+ ARRAY_A
	411	+ );
	412	+ if ($results === null) {
	413	+ throw new QueryExecutionException(
	414	+ BackendStrings::getExceptionStrings()['unable_get_entry_fields'] . __CLASS__
	415	+ );
	416	+ }
	417	+ return $results;
	418	+ }
	419	+
	420	+ /**
	421	+ * Get the count of entries for multiple form IDs.
	422	+ *
	423	+ * @param int[] $formIds
	424	+ * @return array<int, int> formId => count
	425	+ * @throws QueryExecutionException
	426	+ * @throws InvalidArgumentException
	427	+ */
	428	+ public function getEntryCountByFormIds(array $formIds): array
	429	+ {
	430	+ $queryData = EntryQueryHelper::getEntryCountByFormIdsQuery($formIds);
	431	+ if (empty($queryData['sql'])) {
	432	+ return [];
	433	+ }
	434	+ $results = $this->wpdb->get_results(
	435	+ $this->wpdb->prepare($queryData['sql'], $queryData['params']),
	436	+ ARRAY_A
	437	+ );
	438	+ if ($results === null) {
	439	+ throw new QueryExecutionException(
	440	+ BackendStrings::getExceptionStrings()['unable_search_entries'] . __CLASS__
	441	+ );
	442	+ }
	443	+ $counts = [];
	444	+ foreach ($results as $row) {
	445	+ $counts[(int)$row['formId']] = (int)$row['count'];
	446	+ }
	447	+ foreach ($queryData['formIds'] as $id) {
	448	+ if (!isset($counts[$id])) {
	449	+ $counts[$id] = 0;
	450	+ }
	451	+ }
	452	+ return $counts;
	453	+ }
	454	+
	455	+ /**
	456	+ * Resolve owning form IDs for the given entry IDs in a single query.
	457	+ *
	458	+ * @param int[] $entryIds
	459	+ * @return array<int, int> entryId => formId
	460	+ * @throws QueryExecutionException
	461	+ */
	462	+ public function getFormIdsByEntryIds(array $entryIds): array
	463	+ {
	464	+ return EntryQueryHelper::fetchFormIdsByEntryIds($this->wpdb, $entryIds);
	465	+ }
	466	+
	467	+ /**
	468	+ * Get allowed columns for bulk update operations
	469	+ *
	470	+ * @return string[]
	471	+ */
	472	+ protected function getAllowedBulkUpdateColumns(): array
	473	+ {
	474	+ return [
	475	+ self::COLUMN_STARRED,
	476	+ self::COLUMN_STATUS,
	477	+ ];
	478	+ }
	479	+}