← All changes | includes/Core/Database/FormEntryMetaModel.php +326 -217 2.0 → V3.3.2 View file →

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