← All changes | includes/Core/Database/FormEntryMetaModel.php +325 -217 2.0 → 3.3.1 View file →

		@@ -5,29 +5,35 @@
5	5	*/
6	6
7	7	namespace BitCode\BitForm\Core\Database;
8	8
	9	+use BitCode\BitForm\Core\Util\FileHandler;
	10	+
9	11	/**
10		- * Undocumented class
	12	+ * Manages entry meta (per-field values) for each form submission.
11	13	*/
12	14
13		-class FormEntryMetaModel extends Model {
	15	+class FormEntryMetaModel extends Model
	16	+{
14	17	protected static $table = 'bitforms_form_entrymeta';
15	18
16		- public function duplicateEntryMeta($data) {
	19	+ public function duplicateEntryMeta($data)
	20	+ {
17	21	$values[] = $data['duplicateID'];
18	22	$values[] = $data['entryID'];
19	23	$sql = "INSERT INTO $this->table_name (bitforms_form_entry_id,meta_key,meta_value)"
20		- . ' SELECT %d as bitforms_form_entry_id,meta_key,meta_value'
21		- . " FROM `$this->table_name` WHERE bitforms_form_entry_id = %d";
	24	+ . ' SELECT %d as bitforms_form_entry_id,meta_key,meta_value'
	25	+ . " FROM `$this->table_name` WHERE bitforms_form_entry_id = %d";
22	26	return $this->execute($sql, $values)->getResult();
23	27	}
24	28
25		- public function update(array $data, array $condition) {
	29	+ public function update(array $data, array $condition)
	30	+ {
26	31	$entryID = $condition['bitforms_form_entry_id'];
27	32	if (empty($entryID)) {
28	33	return false;
29	34	}
	35	+ // Form entry meta lookup; meta_key/meta_value query required to map dynamic field keys per entry.
30	36	$formEntryMeta = $this->get(
31	37	[
32	38	'meta_key',
33	39	'meta_value',
		@@ -43,11 +49,11 @@
43	49	$updatedData = [];
44	50	$oldEntriesKey = array_keys($oldEntries);
45	51	foreach ($data as $upKey => $upValue) {
46	52	$updatedData[$upKey] = is_string($upValue) ?
47		- $upValue :
48		- wp_json_encode($upValue);
49		- if (!\in_array($upKey, $oldEntriesKey)) {
	53	+ $upValue :
	54	+ wp_json_encode($upValue);
	55	+ if (!in_array($upKey, $oldEntriesKey, true)) {
50	56	$this->insert(
51	57	[
52	58	'bitforms_form_entry_id' => $entryID,
53	59	'meta_key' => $upKey,
		@@ -69,9 +75,9 @@
69	75	$condition['meta_key'] = array_keys($data);
70	76	foreach ($data as $key => $value) {
71	77	$value = is_string($value) ? $value : wp_json_encode($value);
72	78	$case_part .= "
73		- WHEN '$key' THEN " . $this->getFieldFormat($value);
	79	+ WHEN '" . esc_sql($key) . "' THEN " . $this->getFieldFormat($value);
74	80	$all_values[] = $value;
75	81	}
76	82	$formattedCondition = $this->getFormatedCondition($condition);
77	83	if ($formattedCondition) {
		@@ -93,9 +99,10 @@
93	99	}
94	100	return $updatedData;
95	101	}
96	102
97		- public function validQueryCondition($conditions) {
	103	+ public function validQueryCondition($conditions)
	104	+ {
98	105	foreach ($conditions as $index => $condition) {
99	106	if (is_object($condition)) {
100	107	if (empty($condition->field) || empty($condition->logic) || empty($condition->val) && !in_array($condition->val, ['0', 0, 0.0], true)) {
101	108	unset($conditions[$index], $conditions[$index + 1]);
		@@ -104,9 +111,10 @@
104	111	}
105	112	return $conditions;
106	113	}
107	114
108		- public function sqlQryGenerateByFldCondition($conditions) {
	115	+ public function sqlQryGenerateByFldCondition($conditions)
	116	+ {
109	117	$dbHelper = new Helper();
110	118	$sql = '';
111	119
112	120	$dateQuery = $dbHelper->dateQueryList();
		@@ -111,11 +119,17 @@
111	119
112	120	$dateQuery = $dbHelper->dateQueryList();
113	121
114	122	$validCondtions = $this->validQueryCondition($conditions);
	123	+ $safeOperators = ['=', '!=', '>', '<', '>=', '<=', 'LIKE', 'NOT LIKE'];
115	124
116	125	foreach ($validCondtions as $condition) {
117	126	if (is_object($condition)) {
	127	+ $field = sanitize_key($condition->field);
	128	+ if (empty($field)) {
	129	+ continue;
	130	+ }
	131	+
118	132	if (is_array($condition->val)) {
119	133	$value = $dbHelper->arrValueModifyByLogic($condition->logic, $condition->val);
120	134	} else {
121	135	$value = $dbHelper->strValueModifyByLogic($condition->logic, $condition->val);
		@@ -121,20 +135,23 @@
121	135	$value = $dbHelper->strValueModifyByLogic($condition->logic, $condition->val);
122	136	}
123	137
124	138	$operator = $dbHelper->convertToSqlOperator($condition->logic);
	139	+ if (!in_array($operator, $safeOperators, true)) {
	140	+ continue;
	141	+ }
125	142
126		- if (isset($dateQuery[$condition->val])) {
127		- $sql .= $dbHelper->fieldQueryByDate($condition->field, $operator, $value, $condition->logic);
	143	+ if (!is_array($condition->val) && isset($dateQuery[$condition->val])) {
	144	+ $sql .= $dbHelper->fieldQueryByDate($field, $operator, $value, $condition->logic);
128	145	} else {
129	146	if (!is_int($value)) {
130		- $value = "'" . $value . "'";
	147	+ $value = "'" . esc_sql($value) . "'";
131	148	}
132	149
133		- $sql .= "`$condition->field` $operator $value";
	150	+ $sql .= "`{$field}` $operator $value";
134	151	}
135		- } else {
136		- $sql .= ' ' . $condition;
	152	+ } elseif (in_array(strtoupper(trim((string) $condition)), ['AND', 'OR'], true)) {
	153	+ $sql .= ' ' . strtoupper(trim((string) $condition));
137	154	}
138	155	}
139	156
140	157	return trim($sql);
		@@ -139,9 +156,10 @@
139	156
140	157	return trim($sql);
141	158	}
142	159
143		- public function queryRecount($selectedMeta, $groupedCondition, $orderCondition, $all_values) {
	160	+ public function queryRecount($selectedMeta, $groupedCondition, $orderCondition, $all_values)
	161	+ {
144	162	$entry_table = $this->app_db->prefix . 'bitforms_form_entries';
145	163	$sql = 'SELECT count(*) as count FROM (';
146	164	$sql .= "SELECT $selectedMeta FROM `$this->table_name` em";
147	165	$sql .= " INNER JOIN $entry_table e on e.id = em.bitforms_form_entry_id ";
		@@ -150,11 +168,14 @@
150	168	$countResult = $this->execute($sql, $all_values)->getResult();
151	169	return $countResult[0]->count;
152	170	}
153	171
154		- public function selectedEntryMeta($formFields, $fieldCount) {
	172	+ public function selectedEntryMeta($formFields, $fieldCount, $filter = null)
	173	+ {
155	174	$all_values = [];
156	175	$formFieldsNames = [];
	176	+ $globalFilterString = '';
	177	+ $globalFilterValues = [];
157	178	$metaChecker = 0;
158	179	$selectedMeta = '`bitforms_form_entry_id` as entry_id,';
159	180	$selectedMeta .= "e.user_id as '__user_id',";
160	181	$selectedMeta .= "e.user_ip as '__user_ip',";
		@@ -169,9 +190,10 @@
169	190	$selectedMeta .= ',';
170	191	}
171	192
172	193	foreach ($formFields as $fieldDetails) {
173		- $fieldFormat = $this->getFieldFormat($fieldDetails['key']);
	194	+ $safeFieldKey = sanitize_key($fieldDetails['key']);
	195	+ $fieldFormat = $this->getFieldFormat($safeFieldKey);
174	196	$selectedMeta .= "GROUP_CONCAT(
175	197	CASE
176	198	`meta_key`
177	199	WHEN '$fieldFormat' THEN `meta_value`
		@@ -177,32 +199,34 @@
177	199	WHEN '$fieldFormat' THEN `meta_value`
178	200	END
179	201	) AS '$fieldFormat'";
180	202	$metaChecker += 1;
181		- $all_values[] = $fieldDetails['key'];
182		- $all_values[] = $fieldDetails['key'];
183		- $formFieldsNames[] = $fieldDetails['key'];
	203	+ $all_values[] = $safeFieldKey;
	204	+ $all_values[] = $safeFieldKey;
	205	+ $formFieldsNames[] = $safeFieldKey;
184	206	if ($metaChecker < $fieldCount) {
185	207	$selectedMeta .= ',';
186	208	}
187		- //#unused code commented by me##
188		- // if ( !empty( $filter['global'] ) ) {
189		- // $globalFilterString .= " `" . $fieldDetails['key'] . "` LIKE '%%" . $this->getFieldFormat( $filter['global'] ) . "%%' ";
190		- // if ( $metaChecker < $fieldCount ) {
191		- // $globalFilterString .= " OR ";
192		- // }
193		- // $globalFilterValues[] = $filter['global'];
194		- // }
	209	+ if (!empty($filter['global'])) {
	210	+ $globalFilterString .= ' `' . $safeFieldKey . '` LIKE %s ';
	211	+ if ($metaChecker < $fieldCount) {
	212	+ $globalFilterString .= ' OR ';
	213	+ }
	214	+ $globalFilterValues[] = '%' . $this->app_db->esc_like($filter['global']) . '%';
	215	+ }
195	216	}
196	217
197	218	return [
198		- 'selected_meta' => $selectedMeta,
199		- 'form_fields_names' => $formFieldsNames,
200		- 'all_values' => $all_values,
	219	+ 'selected_meta' => $selectedMeta,
	220	+ 'form_fields_names' => $formFieldsNames,
	221	+ 'all_values' => $all_values,
	222	+ 'global_filter_string' => $globalFilterString,
	223	+ 'global_filter_values' => $globalFilterValues,
201	224	];
202	225	}
203	226
204		- public function groupedCondition($condition, $all_values, $fieldConditions) {
	227	+ public function groupedCondition($condition, $all_values, $fieldConditions, $filter = null, $globalFilterString = '', $globalFilterValues = [])
	228	+ {
205	229	$isFldCondition = false;
206	230	$formattedCondition = $this->getFormatedCondition($condition);
207	231	if ($formattedCondition) {
208	232	$groupedCondition = $formattedCondition['conditions'] . 'GROUP BY
		@@ -210,51 +234,23 @@
210	234	$all_values = array_merge($all_values, $formattedCondition['values']);
211	235	} else {
212	236	$groupedCondition = null;
213	237	}
214		- //#unused code commented by me##
215		- //$isRecount = false;
	238	+ if (!empty($filter['global']) && !empty($globalFilterString) && !empty($globalFilterValues)) {
	239	+ $isFldCondition = true;
	240	+ if ($groupedCondition && false !== strpos($groupedCondition, 'HAVING')) {
	241	+ $groupedCondition .= ' AND (' . $globalFilterString . ') ';
	242	+ } else {
	243	+ $groupedCondition .= ' HAVING (' . $globalFilterString . ') ';
	244	+ }
	245	+ $all_values = array_merge($all_values, $globalFilterValues);
	246	+ }
216	247
217		- // if ( !empty( $filter['field'] ) ) {
218		- // $isRecount = true;
219		- // $filterFieldCount = count( $filter['field'] );
220		- // $filterFieldChecker = 0;
221		- // if ( $filterFieldCount > 0 ) {
222		- // $groupedCondition .= " HAVING ";
223		- // }
224		- // foreach ( $filter['field'] as $filterFieldKey => $filterFieldDetails ) {
225		- // $groupedCondition .= " `$filterFieldDetails->id` ='%%" . $this->getFieldFormat( $filterFieldDetails->value ) . "%%'";
226		- // $all_values[] = $filterFieldDetails->value;
227		- // if ( $filterFieldChecker < $filterFieldCount ) {
228		- // $groupedCondition .= " AND ";
229		- // }
230		- // }
231		- // }
232		-
233		- // if ( !empty( $filter['global'] ) && !empty( $globalFilterString ) && !empty( $globalFilterValues ) ) {
234		- // $isRecount = true;
235		- // if ( !empty( $filter['field'] ) ) {
236		- // $groupedCondition .= " AND (" . $globalFilterString . ") ";
237		- // } else {
238		- // $groupedCondition .= " HAVING $globalFilterString ";
239		- // }
240		- // $offset = 0;
241		- // $all_values = array_merge( $all_values, $globalFilterValues );
242		- // }
243		-
244		- // if ( !empty( $dateBetweenFilter ) && !empty( $dateBetweenFilter->start_date ) && !empty( $dateBetweenFilter->end_date ) ) {
245		- // if ( strpos( $groupedCondition, 'HAVING' ) !== false ) {
246		- // $groupedCondition .= " AND `__created_at` BETWEEN '" . $dateBetweenFilter->start_date . "' AND '" . $dateBetweenFilter->end_date . "' ";
247		- // } else {
248		- // $groupedCondition .= " HAVING `__created_at` BETWEEN '" . $dateBetweenFilter->start_date . "' AND '" . $dateBetweenFilter->end_date . "' ";
249		- // }
250		- // }
251		-
252	248	$sqlQryByFldCondtion = $this->sqlQryGenerateByFldCondition($fieldConditions);
253	249
254	250	if (!empty($sqlQryByFldCondtion)) {
255	251	$isFldCondition = true;
256		- if (false !== strpos($groupedCondition, 'HAVING')) {
	252	+ if ($groupedCondition && false !== strpos($groupedCondition, 'HAVING')) {
257	253	$groupedCondition .= ' AND (' . $sqlQryByFldCondtion . ') ';
258	254	} else {
259	255	$groupedCondition .= ' HAVING (' . $sqlQryByFldCondtion . ') ';
260	256	}
		@@ -265,9 +261,10 @@
265	261	'isFldCondition' => $isFldCondition,
266	262	];
267	263	}
268	264
269		- public function orderCondition($formFieldsNames, $sortBy) {
	265	+ public function orderCondition($formFieldsNames, $sortBy)
	266	+ {
270	267	$orderCondition = null;
271	268	if (!empty($sortBy)) {
272	269	$sortableFieldCount = count($sortBy);
273	270	$sortableFieldChecker = 0;
		@@ -275,12 +272,12 @@
275	272	$orderCondition .= ' ORDER BY ';
276	273	}
277	274	$orderList = '';
278	275	foreach ($sortBy as $sortableFieldKey => $sortableFieldDetails) {
279		- // $orderCondition .=" ".$this->getFieldFormat($sortableFieldDetails->id);
280	276	$sortableFieldChecker += 1;
281		- if (in_array($sortableFieldDetails->id, $formFieldsNames)) {
282		- $orderList .= " `$sortableFieldDetails->id` ";
	277	+ if (in_array($sortableFieldDetails->id, $formFieldsNames, true)) {
	278	+ $safeId = sanitize_key($sortableFieldDetails->id);
	279	+ $orderList .= " `$safeId` ";
283	280	$orderFollow = $sortableFieldDetails->desc ? ' DESC ' : ' ASC ';
284	281	$orderList .= ' ' . $orderFollow;
285	282	if ($sortableFieldChecker < $sortableFieldCount) {
286	283	$orderList .= ', ';
		@@ -296,9 +293,10 @@
296	293	$orderCondition .= empty($orderCondition) ? ' ORDER BY `bitforms_form_entry_id` DESC ' : ',`bitforms_form_entry_id` DESC ';
297	294	return $orderCondition;
298	295	}
299	296
300		- public function isEntryMetaExist($conditions) {
	297	+ public function isEntryMetaExist($conditions)
	298	+ {
301	299	$existMetaData = $this->get(
302	300	[
303	301	'meta_key',
304	302	'meta_value',
		@@ -310,23 +308,22 @@
310	308	}
311	309	return false;
312	310	}
313	311
314		- public function getEntryMeta($formFields, $entries, $limit = null, $offset = null, $filter = null, $sortBy = null, $fieldConditions = null, $dateBetweenFilter = null) {
	312	+ public function getEntryMeta($formFields, $entries, $limit = null, $offset = null, $filter = null, $sortBy = null, $fieldConditions = null, $dateBetweenFilter = null)
	313	+ {
315	314	$entry_table = $this->app_db->prefix . 'bitforms_form_entries';
316	315	$fieldCount = count($formFields);
317		- $getSelectedMetaFldValue = $this->selectedEntryMeta($formFields, $fieldCount);
318		-
	316	+ $getSelectedMetaFldValue = $this->selectedEntryMeta($formFields, $fieldCount, $filter);
319	317	$selectedMeta = $getSelectedMetaFldValue['selected_meta'];
320	318	$formFieldsNames = $getSelectedMetaFldValue['form_fields_names'];
321	319	$all_values = $getSelectedMetaFldValue['all_values'];
	320	+ $globalFilterString = $getSelectedMetaFldValue['global_filter_string'];
	321	+ $globalFilterValues = $getSelectedMetaFldValue['global_filter_values'];
322	322	$entryIDs = [];
323	323	$entryCount = count($entries);
324		- // $paginateEntry = empty($sortBy) && empty($filter['field']) && empty($filter['global']);
325		- // $entries = $paginateEntry ? array_slice($entries, $offset, $limit) : $entries;
326		- $paginateEntry = false;
327	324	foreach ($entries as $entryDetail) {
328		- $entryIDs[] = $entryDetail->id;
	325	+ $entryIDs[] = $entryDetail->id ?? $entryDetail;
329	326	}
330	327	if (empty($entryIDs)) {
331	328	return [
332	329	'count' => 0,
		@@ -333,34 +330,36 @@
333	330	'entries' => [],
334	331	];
335	332	}
336	333	$condition['bitforms_form_entry_id'] = $entryIDs;
337		- $group = $this->groupedCondition($condition, $all_values, $fieldConditions);
	334	+ $group = $this->groupedCondition($condition, $all_values, $fieldConditions, $filter, $globalFilterString, $globalFilterValues);
338	335	$groupedCondition = $group['groupedCondition'];
339	336	$all_values = $group['all_values'];
340	337	$isFldCondition = $group['isFldCondition'];
341		- $orderCondition = $this->orderCondition($formFieldsNames, $sortBy);
	338	+ $orderCondition = $this->orderCondition($formFieldsNames, (array) $sortBy);
342	339
343	340	$paginate = null;
344		- if (!\is_null($limit)) {
345		- $limit = \intval($limit);
	341	+ if (!is_null($limit)) {
	342	+ $limit = intval($limit);
346	343	$paginate .= " LIMIT $limit ";
347	344	}
348		- if (!\is_null($offset)) {
349		- $offset = \intval($offset);
350		- $paginate .= " OFFSET $offset ";
	345	+ if (!is_null($offset)) {
	346	+ $offset = intval($offset);
	347	+ $paginate .= " OFFSET $offset ";
351	348	}
	349	+
352	350	$sql = "SELECT $selectedMeta FROM `$this->table_name` em";
353	351	$sql .= " INNER JOIN $entry_table e on e.id = em.bitforms_form_entry_id ";
354	352	if ($dateBetweenFilter) {
355		- $startDate = $dateBetweenFilter->start_date;
356		- $endDate = $dateBetweenFilter->end_date;
	353	+ $startDate = sanitize_text_field($dateBetweenFilter->start_date ?? '');
	354	+ $endDate = sanitize_text_field($dateBetweenFilter->end_date ?? '');
	355	+
357	356	if ($startDate && $endDate) {
358		- $sql .= " AND DATE(e.created_at) BETWEEN '$startDate' AND '$endDate' ";
	357	+ $sql .= $this->app_db->prepare(' AND e.created_at BETWEEN %s AND %s', $startDate . ' 00:00:00', $endDate . ' 23:59:59');
359	358	} elseif ($startDate) {
360		- $sql .= " AND DATE(e.created_at) >= '$startDate' ";
	359	+ $sql .= $this->app_db->prepare(' AND e.created_at >= %s', $startDate . ' 00:00:00');
361	360	} elseif ($endDate) {
362		- $sql .= " AND DATE(e.created_at) <= '$endDate' ";
	361	+ $sql .= $this->app_db->prepare(' AND e.created_at <= %s', $endDate . ' 23:59:59');
363	362	}
364	363	}
365	364	$sql .= $groupedCondition . $orderCondition . $paginate;
366	365	$result = $this->execute($sql, $all_values)->getResult();
		@@ -365,18 +364,15 @@
365	364	$sql .= $groupedCondition . $orderCondition . $paginate;
366	365	$result = $this->execute($sql, $all_values)->getResult();
367	366	if (is_wp_error($result)) {
368	367	return [
369		- 'count' => $paginateEntry ? $entryCount : 0,
	368	+ 'count' => 0,
370	369	'entries' => [],
371	370	'error' => $result->get_error_message()
372	371	];
373	372	}
374	373	if ($isFldCondition) {
375		- $condition['bitforms_form_entry_id'] = $entryIDs;
376		- $group = $this->groupedCondition($condition, $all_values, $fieldConditions);
377		- $all_values = $group['all_values'];
378		- $entryCount = $this->queryRecount($selectedMeta, $group['groupedCondition'], $orderCondition, $all_values);
	374	+ $entryCount = $this->queryRecount($selectedMeta, $groupedCondition, $orderCondition, $all_values);
379	375	}
380	376	$resultedEntries = [
381	377	'count' => $entryCount,
382	378	'entries' => $result,
		@@ -383,144 +379,256 @@
383	379	];
384	380	return $resultedEntries;
385	381	}
386	382
387		- private function csvInjectionPrevent($value) {
388		- $formula = ['=', '-', '+', '@', "\t", "\r"];
389		- $valueFilter = preg_replace('/[\]["]/i', '', $value);
390		- if (\in_array(substr($value, 0, 1), $formula, true)) {
391		- $valueFilter = "'" . trim($valueFilter);
	383	+ public function getSingleEntryMeta($formFields, $entryId)
	384	+ {
	385	+ $entry_table = $this->app_db->prefix . 'bitforms_form_entries';
	386	+ $fieldCount = count($formFields);
	387	+ $getSelectedMetaFldValue = $this->selectedEntryMeta($formFields, $fieldCount);
	388	+ $selectedMeta = $getSelectedMetaFldValue['selected_meta'];
	389	+ $formFieldsNames = $getSelectedMetaFldValue['form_fields_names'];
	390	+ $all_values = $getSelectedMetaFldValue['all_values'];
	391	+ $condition['bitforms_form_entry_id'] = [$entryId];
	392	+ $group = $this->groupedCondition($condition, $all_values, []);
	393	+ $groupedCondition = $group['groupedCondition'];
	394	+ $all_values = $group['all_values'];
	395	+ $orderCondition = $this->orderCondition($formFieldsNames, null);
	396	+ $sql = "SELECT $selectedMeta FROM `$this->table_name` em";
	397	+ $sql .= " INNER JOIN $entry_table e on e.id = em.bitforms_form_entry_id ";
	398	+ $sql .= $groupedCondition . $orderCondition;
	399	+ $result = $this->execute($sql, $all_values)->getResult();
	400	+
	401	+ if (is_wp_error($result)) {
	402	+ return [];
	403	+ }
	404	+ return $result;
	405	+ }
	406	+
	407	+ private static function csvInjectionPrevent($value)
	408	+ {
	409	+ $formula = ['=', '-', '+', '@', "\t", "\r"];
	410	+ $valueFilter = preg_replace('/[\]["]/i', '', $value);
	411	+ if (in_array(substr($valueFilter, 0, 1), $formula, true)) {
	412	+ $valueFilter = "'" . trim($valueFilter);
	413	+ }
	414	+
	415	+ return $valueFilter;
	416	+ }
	417	+
	418	+ private static function unescapeString($str)
	419	+ {
	420	+ if (is_string($str) && '' !== $str) {
	421	+ $decoded = json_decode('"' . str_replace('"', '\\"', $str) . '"');
	422	+ return (null !== $decoded) ? $decoded : $str;
	423	+ }
	424	+ return $str;
	425	+ }
	426	+
	427	+ private static function formatRepeaterValue($rawValue, $fieldMap)
	428	+ {
	429	+ if (empty($rawValue)) {
	430	+ return '';
	431	+ }
	432	+ $rows = [];
	433	+ preg_match_all('/\{([^}]+)\}/', $rawValue, $matches);
	434	+
	435	+ foreach ($matches[1] as $row) {
	436	+ $pairs = explode(',', $row);
	437	+ $formattedPairs = [];
	438	+
	439	+ foreach ($pairs as $pair) {
	440	+ if (false === strpos($pair, ':')) {
	441	+ continue;
	442	+ }
	443	+ [$childKey, $value] = explode(':', $pair, 2);
	444	+ $childKey = trim($childKey);
	445	+ $value = trim($value);
	446	+
	447	+ // Get label from fieldMap or use key
	448	+ $label = $fieldMap[$childKey]['adminLbl'] ?? $childKey;
	449	+ $formattedPairs[] = "$label: " . self::csvInjectionPrevent(self::unescapeString($value));
392	450	}
393	451
394		- return $valueFilter;
	452	+ $rows[] = implode(', ', $formattedPairs);
395	453	}
396	454
397		- public function getExportEntry($formFields, $entries, $formId, $fieldLabels, $limit = null, $sortBy = null, $sortByField = null) {
398		- $entry_table = $this->app_db->prefix . 'bitforms_form_entries';
399		- $selectedEntryMeta = '`bitforms_form_entry_id` as entry_id,';
400		- $selectedEntryMeta .= "e.user_id as '__user_id',";
401		- $selectedEntryMeta .= "e.status as '__entry_status',";
402		- $selectedEntryMeta .= "e.user_ip as '__user_ip',";
403		- $selectedEntryMeta .= "e.user_location as '__user_location',";
404		- $selectedEntryMeta .= "e.user_device as '__user_device',";
405		- $selectedEntryMeta .= "e.referer as '__referer',";
406		- $selectedEntryMeta .= "e.created_at as '__created_at',";
407		- $selectedEntryMeta .= "e.updated_at as '__updated_at',";
408		- $metaChecker = 0;
	455	+ return implode('; ', $rows);
	456	+ }
409	457
410		- $entryInfo = ['__user_id', '__user_ip', /* '__user_location', */'__user_device',
411		- '__referer', '__created_at', '__updated_at'];
412		- $all_values = [];
413		- if ([] === $formFields) {
414		- $data = [
415		- 'count' => 0,
416		- 'entries' => [],
417		- ];
418		- wp_send_json_success($data, 200);
	458	+ public function getExportEntry($formFields, $entries, $formId, $fieldLabels, $limit = null, $sortBy = null, $sortByField = null, $offset = null, $entryConditions = null)
	459	+ {
	460	+ $entry_table = $this->app_db->prefix . 'bitforms_form_entries';
	461	+ $selectedEntryMeta = '`bitforms_form_entry_id` as entry_id,';
	462	+ $selectedEntryMeta .= 'e.user_id as `__user_id`,';
	463	+ $selectedEntryMeta .= 'e.status as `__entry_status`,';
	464	+ $selectedEntryMeta .= 'e.user_ip as `__user_ip`,';
	465	+ $selectedEntryMeta .= 'e.user_location as `__user_location`,';
	466	+ $selectedEntryMeta .= 'e.user_device as `__user_device`,';
	467	+ $selectedEntryMeta .= 'e.referer as `__referer`,';
	468	+ $selectedEntryMeta .= 'e.created_at as `__created_at`,';
	469	+ $selectedEntryMeta .= 'e.updated_at as `__updated_at`,';
	470	+ $metaChecker = 0;
	471	+
	472	+ $entryInfo = [
	473	+ '__user_id',
	474	+ '__user_ip', /* '__user_location', */
	475	+ '__user_device',
	476	+ '__entry_status',
	477	+ '__referer',
	478	+ '__created_at',
	479	+ '__updated_at'
	480	+ ];
	481	+ $all_values = [];
	482	+ if ([] === $formFields) {
	483	+ return [
	484	+ 'count' => 0,
	485	+ 'entries' => [],
	486	+ ];
	487	+ }
	488	+ $fieldCount = count($formFields) - count(array_intersect($formFields, $entryInfo));
	489	+ $formFieldsNames = [];
	490	+ foreach ($formFields as $fldKey) {
	491	+ $formFieldsNames[] = $fldKey;
	492	+ if (in_array($fldKey, $entryInfo, true)) {
	493	+ continue;
419	494	}
420		- $fieldCount = count($formFields) - count(array_intersect($formFields, $entryInfo));
421		- $formFieldsNames = [];
422		- foreach ($formFields as $fldKey) {
423		- $formFieldsNames[] = $fldKey;
424		- if (in_array($fldKey, $entryInfo)) {
425		- continue;
426		- }
427		- $fieldFormat = $this->getFieldFormat($fldKey);
428		- $selectedEntryMeta .= "GROUP_CONCAT(
	495	+ $fieldFormat = $this->getFieldFormat($fldKey);
	496	+ $selectedEntryMeta .= "GROUP_CONCAT(
429	497	CASE
430	498	`meta_key`
431	499	WHEN '$fieldFormat' THEN `meta_value`
432	500	END
433	501	) AS '$fieldFormat'";
434		- $metaChecker += 1;
435		- $all_values[] = $fldKey;
436		- $all_values[] = $fldKey;
437		- if ($metaChecker < $fieldCount) {
438		- $selectedEntryMeta .= ',';
439		- }
	502	+ $metaChecker += 1;
	503	+ $all_values[] = $fldKey;
	504	+ $all_values[] = $fldKey;
	505	+ if ($metaChecker < $fieldCount) {
	506	+ $selectedEntryMeta .= ',';
440	507	}
441		- $entryIDs = [];
442		- foreach ($entries as $entryDetail) {
443		- $entryIDs[] = $entryDetail->id;
	508	+ }
	509	+ $entryIDs = [];
	510	+ foreach ($entries as $entryDetail) {
	511	+ $entryIDs[] = $entryDetail->id;
	512	+ }
	513	+ if (empty($entryIDs)) {
	514	+ return [
	515	+ 'count' => 0,
	516	+ 'entries' => [],
	517	+ ];
	518	+ }
	519	+ $condition['bitforms_form_entry_id'] = $entryIDs;
	520	+ $grpCon = $this->groupedCondition($condition, $all_values, $entryConditions);
	521	+ $groupedCondition = $grpCon['groupedCondition'];
	522	+ $all_values = $grpCon['all_values'];
	523	+
	524	+ $order = 'DESC' === $sortBy ? 'DESC ' : 'ASC ';
	525	+ $validSortFields = array_column($fieldLabels, 'key');
	526	+ $orderField = (!is_null($sortByField) && in_array($sortByField, $validSortFields, true))
	527	+ ? '`' . sanitize_key($sortByField) . '`'
	528	+ : '`bitforms_form_entry_id`';
	529	+
	530	+ $orderCondition = "ORDER BY $orderField $order ";
	531	+ $limitClause = '';
	532	+ if (!is_null($limit)) {
	533	+ $limitInt = intval($limit);
	534	+ $limitClause = " LIMIT $limitInt ";
	535	+ if (!is_null($offset)) {
	536	+ $offsetInt = intval($offset);
	537	+ $limitClause .= " OFFSET $offsetInt ";
444	538	}
445		- if (empty($entryIDs)) {
446		- return [
447		- 'count' => 0,
448		- 'entries' => [],
449		- ];
	539	+ }
	540	+
	541	+ $this->app_db->query('SET SESSION group_concat_max_len = 10000');
	542	+ $sql = "SELECT $selectedEntryMeta FROM `$this->table_name` em";
	543	+ $sql .= " INNER JOIN $entry_table e on e.id = em.bitforms_form_entry_id ";
	544	+ $sql .= $groupedCondition . $orderCondition . $limitClause;
	545	+ $result = $this->execute($sql, $all_values)->getResult();
	546	+ if (is_wp_error($result)) {
	547	+ return new \WP_Error('db_error', 'Internal server error');
	548	+ }
	549	+
	550	+ $allData = [];
	551	+ $entryStatus = [
	552	+ '0' => 'Read',
	553	+ '1' => 'Unread',
	554	+ '2' => 'Unconfirmed',
	555	+ '3' => 'Confirmed',
	556	+ '9' => 'Draft',
	557	+ ];
	558	+ $userIds = array_unique(array_filter(
	559	+ array_map(static fn ($row) => (int) $row->__user_id, (array) $result),
	560	+ static fn ($id) => $id > 0
	561	+ ));
	562	+ $userNames = [];
	563	+ if (!empty($userIds)) {
	564	+ $users = get_users(['include' => $userIds, 'fields' => ['ID', 'display_name']]);
	565	+ foreach ($users as $user) {
	566	+ $userNames[$user->ID] = $user->display_name;
450	567	}
451		- $condition['bitforms_form_entry_id'] = $entryIDs;
452		- $formattedCondition = $this->getFormatedCondition($condition);
453		- $groupedCondition = null;
454		- if ($formattedCondition) {
455		- $groupedCondition = $formattedCondition['conditions'] . ' GROUP BY
456		- `bitforms_form_entry_id` ';
457		- $all_values = array_merge($all_values, $formattedCondition['values']);
	568	+ }
	569	+ foreach ($result as $key => $value) {
	570	+ foreach ($formFieldsNames as $formFieldName) {
	571	+ $allData[$key]['entry_id'] = preg_replace('/[\]["]/i', '', $value->entry_id);
	572	+ if ('__user_id' === $formFieldName && intval($value->$formFieldName) > 0) {
	573	+ $allData[$key][$formFieldName] = $userNames[$value->$formFieldName] ?? '';
	574	+ } elseif ('__user_ip' === $formFieldName) {
	575	+ $allData[$key][$formFieldName] = long2ip((int) $value->$formFieldName);
	576	+ } elseif ('__entry_status' === $formFieldName) {
	577	+ $allData[$key][$formFieldName] = $entryStatus[$value->{$formFieldName}] ?? '';
	578	+ } else {
	579	+ $allData[$key][$formFieldName] = preg_replace('/[\]["]/i', '', $value->$formFieldName);
	580	+ }
458	581	}
459		- $order = \is_null($sortBy) ? 'DESC ' : "$sortBy";
460		- $orderField = \is_null($sortByField) ? 'bitforms_form_entry_id' : "`$sortByField`";
	582	+ }
461	583
462		- $orderCondition = "ORDER BY $orderField $order ";
463		- if (!\is_null($limit)) {
464		- $limitInt = \intval($limit);
465		- $limit = " LIMIT $limitInt ";
	584	+ $fieldMap = [];
	585	+ $repeaterFields = [];
	586	+ $fileFields = [];
	587	+ $downloadableFieldType = ['file-up', 'signature', 'advanced-file-up'];
	588	+
	589	+ foreach ($fieldLabels as $field) {
	590	+ $key = $field['key'];
	591	+ $fieldMap[$key] = $field;
	592	+ if ('repeater' === $field['type']) {
	593	+ $repeaterFields[] = $key;
	594	+ } elseif (in_array($field['type'], $downloadableFieldType, true)) {
	595	+ $fileFields[] = $key;
466	596	}
467		- $sql = "SELECT $selectedEntryMeta FROM `$this->table_name` em";
468		- $sql .= " INNER JOIN $entry_table e on e.id = em.bitforms_form_entry_id ";
469		- $sql .= $groupedCondition . $orderCondition . $limit;
	597	+ }
470	598
471		- $result = $this->execute($sql, $all_values)->getResult();
472		- $allData = [];
473		- $entry_id = 'entry_id';
474		- $users = get_users(['fields' => ['ID', 'display_name']]);
475		- $userNames = [];
476		- foreach ($users as $key => $value) {
477		- $userNames[$value->ID] = $value->display_name;
478		- }
479		- foreach ($result as $key => $value) {
480		- foreach ($formFieldsNames as $formFieldName) {
481		- $allData[$key]['entry_id'] = preg_replace('/[\]["]/i', '', $value->$entry_id);
482		- if ('__user_id' === $formFieldName && intval($value->$formFieldName) > 0) {
483		- $allData[$key][$formFieldName] = $userNames[$value->$formFieldName];
484		- } elseif ('__user_ip' === $formFieldName) {
485		- $allData[$key][$formFieldName] = long2ip($value->$formFieldName);
486		- } else {
487		- $allData[$key][$formFieldName] = preg_replace('/[\]["]/i', '', $value->$formFieldName);
488		- }
	599	+ foreach ($allData as &$entry) {
	600	+ foreach ($entry as $key => &$value) {
	601	+ if (is_string($value)) {
	602	+ $value = self::csvInjectionPrevent(self::unescapeString($value));
489	603	}
	604	+ if (in_array($key, $repeaterFields, true)) {
	605	+ $value = self::formatRepeaterValue($value, $fieldMap);
	606	+ }
490	607	}
	608	+ unset($value);
	609	+ }
	610	+ unset($entry, $value);
491	611
492		- if (is_wp_error($result)) {
493		- wp_send_json_error('Internal server error', 500);
494		- } else {
495		- foreach ($fieldLabels as $field) {
496		- foreach ($allData as $index => $entry) {
497		- if (array_key_exists($field['key'], $entry) && 'file-up' === $field['type']) {
498		- $key = $field['key'];
499		- if (empty($entry[$key])) {
500		- continue;
501		- }
502		- $_upload_dir = BITFORMS_UPLOAD_DIR . DIRECTORY_SEPARATOR . $formId . DIRECTORY_SEPARATOR . $entry['entry_id'];
503		- if (is_array(explode(',', $entry[$key]))) {
504		- $fileData = [];
505		- foreach (explode(',', $entry[$key]) as $file) {
506		- $uploadedFile = explode('_', $file);
507		- $path = "bitforms/bitforms-file/?formID=$formId&entryID=" . $entry['entry_id'] . "&fileID=$uploadedFile[0]";
508		- if (file_exists($_upload_dir . DIRECTORY_SEPARATOR . $uploadedFile[0])) {
509		- $fileData[] = site_url($path, null);
510		- }
511		- }
512		- $allData[$index][$key] = implode(',', $fileData);
513		- } else {
514		- $uploadedFile = explode('_', $entry[$key]);
515		- $path = "bitforms/bitforms-file/?formID=$formId&entryID=" . $entry['entry_id'] . '&fileID=' . $uploadedFile[0];
516		- if (file_exists($_upload_dir . DIRECTORY_SEPARATOR . $uploadedFile[0])) {
517		- $allData[$index][$key] = site_url($path, null);
518		- }
519		- }
520		- }
	612	+ foreach ($allData as &$entry) {
	613	+ $entryId = $entry['entry_id'];
	614	+ $_upload_dir = FileHandler::getEntriesFileUploadDir($formId, $entryId);
	615	+ foreach ($fileFields as $fileKey) {
	616	+ if (empty($entry[$fileKey])) {
	617	+ continue;
	618	+ }
	619	+ $fileIds = explode(',', $entry[$fileKey]);
	620	+ $urls = [];
	621	+ foreach ($fileIds as $fileId) {
	622	+ $path = "bitforms/bitforms-file/?formID=$formId&entryID=$entryId&fileID=$fileId";
	623	+ if (file_exists($_upload_dir . DIRECTORY_SEPARATOR . $fileId)) {
	624	+ $urls[] = site_url($path);
521	625	}
522	626	}
523		- wp_send_json_success($allData, 200);
	627	+ $entry[$fileKey] = implode(',', $urls);
524	628	}
525	629	}
	630	+ unset($entry);
	631	+
	632	+ return $allData;
	633	+ }
526	634	}