← All changes | includes/Core/Database/FormEntryMetaModel.php +586 -263 1.1.8 → V3.3.2 View file →

		@@ -5,308 +5,631 @@
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		-use BitCode\BitForm\Core\Database\Model;
14		-
15	16	class FormEntryMetaModel extends Model
16	17	{
17		- protected static $table = 'bitforms_form_entrymeta';
	18	+ protected static $table = 'bitforms_form_entrymeta';
18	19
	20	+ public function duplicateEntryMeta($data)
	21	+ {
	22	+ $values[] = $data['duplicateID'];
	23	+ $values[] = $data['entryID'];
	24	+ $sql = "INSERT INTO $this->table_name (bitforms_form_entry_id,meta_key,meta_value)"
	25	+ . ' SELECT %d as bitforms_form_entry_id,meta_key,meta_value'
	26	+ . " FROM `$this->table_name` WHERE bitforms_form_entry_id = %d";
	27	+ return $this->execute($sql, $values)->getResult();
	28	+ }
19	29
20		- public function duplicateEntryMeta($data)
21		- {
22		- $values[] = $data['duplicateID'];
23		- $values[] = $data['entryID'];
24		- $sql = "INSERT INTO $this->table_name (bitforms_form_entry_id,meta_key,meta_value)"
25		- . " SELECT %d as bitforms_form_entry_id,meta_key,meta_value"
26		- . " FROM `$this->table_name` WHERE bitforms_form_entry_id = %d";
27		- return $this->execute($sql, $values)->getResult();
	30	+ public function update(array $data, array $condition)
	31	+ {
	32	+ $entryID = $condition['bitforms_form_entry_id'];
	33	+ if (empty($entryID)) {
	34	+ return false;
28	35	}
	36	+ // Form entry meta lookup; meta_key/meta_value query required to map dynamic field keys per entry.
	37	+ $formEntryMeta = $this->get(
	38	+ [
	39	+ 'meta_key',
	40	+ 'meta_value',
	41	+ ],
	42	+ [
	43	+ 'bitforms_form_entry_id' => $entryID,
	44	+ ]
	45	+ );
	46	+ $oldEntries = [];
	47	+ foreach ($formEntryMeta as $oldKey => $oldValue) {
	48	+ $oldEntries[$oldValue->meta_key] = $oldValue->meta_value;
	49	+ }
	50	+ $updatedData = [];
	51	+ $oldEntriesKey = array_keys($oldEntries);
	52	+ foreach ($data as $upKey => $upValue) {
	53	+ $updatedData[$upKey] = is_string($upValue) ?
	54	+ $upValue :
	55	+ wp_json_encode($upValue);
	56	+ if (!in_array($upKey, $oldEntriesKey, true)) {
	57	+ $this->insert(
	58	+ [
	59	+ 'bitforms_form_entry_id' => $entryID,
	60	+ 'meta_key' => $upKey,
	61	+ 'meta_value' => $updatedData[$upKey],
	62	+ ]
	63	+ );
	64	+ unset($data[$upKey]);
	65	+ }
	66	+ }
	67	+ if (empty($data)) {
	68	+ return $updatedData;
	69	+ }
	70	+ $checkCondition = $this->checkCondition($condition);
	71	+ if (is_wp_error($checkCondition)) {
	72	+ return $checkCondition;
	73	+ }
	74	+ $case_part = ' ';
	75	+ $all_values = [];
	76	+ $condition['meta_key'] = array_keys($data);
	77	+ foreach ($data as $key => $value) {
	78	+ $value = is_string($value) ? $value : wp_json_encode($value);
	79	+ $case_part .= "
	80	+ WHEN '" . esc_sql($key) . "' THEN " . $this->getFieldFormat($value);
	81	+ $all_values[] = $value;
	82	+ }
	83	+ $formattedCondition = $this->getFormatedCondition($condition);
	84	+ if ($formattedCondition) {
	85	+ $condition_to_check = $formattedCondition['conditions'];
	86	+ $all_values = array_merge($all_values, $formattedCondition['values']);
	87	+ } else {
	88	+ $condition_to_check = null;
	89	+ }
29	90
30		- public function update(array $data, array $condition)
31		- {
32		- $entryID = $condition['bitforms_form_entry_id'];
33		- if (empty($entryID)) {
34		- return false;
	91	+ $sql = "UPDATE $this->table_name
	92	+ SET meta_value = ( CASE meta_key
	93	+ $case_part
	94	+ END
	95	+ )";
	96	+ $sql .= ' ' . $condition_to_check;
	97	+ $status = $this->execute($sql, $all_values)->getResult();
	98	+ if (is_wp_error($status) && 'result_empty' !== $status->get_error_code()) {
	99	+ return $status;
	100	+ }
	101	+ return $updatedData;
	102	+ }
	103	+
	104	+ public function validQueryCondition($conditions)
	105	+ {
	106	+ foreach ($conditions as $index => $condition) {
	107	+ if (is_object($condition)) {
	108	+ if (empty($condition->field) || empty($condition->logic) || empty($condition->val) && !in_array($condition->val, ['0', 0, 0.0], true)) {
	109	+ unset($conditions[$index], $conditions[$index + 1]);
35	110	}
36		- $formEntryMeta = $this->get(
37		- array(
38		- 'meta_key',
39		- 'meta_value'
40		- ),
41		- array(
42		- 'bitforms_form_entry_id' => $entryID
43		- )
44		- );
45		- $oldEntries = array();
46		- foreach ($formEntryMeta as $oldKey => $oldValue) {
47		- $oldEntries[$oldValue->meta_key] = $oldValue->meta_value;
	111	+ }
	112	+ }
	113	+ return $conditions;
	114	+ }
	115	+
	116	+ public function sqlQryGenerateByFldCondition($conditions)
	117	+ {
	118	+ $dbHelper = new Helper();
	119	+ $sql = '';
	120	+
	121	+ $dateQuery = $dbHelper->dateQueryList();
	122	+
	123	+ $validCondtions = $this->validQueryCondition($conditions);
	124	+ $safeOperators = ['=', '!=', '>', '<', '>=', '<=', 'LIKE', 'NOT LIKE'];
	125	+
	126	+ foreach ($validCondtions as $condition) {
	127	+ if (is_object($condition)) {
	128	+ $field = sanitize_key($condition->field);
	129	+ if (empty($field)) {
	130	+ continue;
48	131	}
49		- $updatedData = array();
50		- $oldEntriesKey = array_keys($oldEntries);
51		- foreach ($data as $upKey => $upValue) {
52		- $updatedData[$upKey] = is_string($upValue) ?
53		- $upValue :
54		- wp_json_encode($upValue);
55		- if (!\in_array($upKey, $oldEntriesKey)) {
56		- $this->insert(
57		- array(
58		- 'bitforms_form_entry_id' => $entryID,
59		- 'meta_key' => $upKey,
60		- 'meta_value' => $updatedData[$upKey]
61		- )
62		- );
63		- unset($data[$upKey]);
64		- }
	132	+
	133	+ if (is_array($condition->val)) {
	134	+ $value = $dbHelper->arrValueModifyByLogic($condition->logic, $condition->val);
	135	+ } else {
	136	+ $value = $dbHelper->strValueModifyByLogic($condition->logic, $condition->val);
65	137	}
66		- if (empty($data)) {
67		- return $updatedData;
	138	+
	139	+ $operator = $dbHelper->convertToSqlOperator($condition->logic);
	140	+ if (!in_array($operator, $safeOperators, true)) {
	141	+ continue;
68	142	}
69		- $checkCondition = $this->checkCondition($condition);
70		- if (is_wp_error($checkCondition)) {
71		- return $checkCondition;
72		- }
73		- $case_part = ' ';
74		- $all_values = array();
75		- $condition['meta_key'] = array_keys($data);
76		- foreach ($data as $key => $value) {
77		- $value = is_string($value) ? $value : wp_json_encode($value);
78		- $case_part .= "
79		- WHEN '$key' THEN " . $this->getFieldFormat($value);
80		- $all_values[] = $value;
81		- }
82		- $formattedCondition = $this->getFormatedCondition($condition);
83		- if ($formattedCondition) {
84		- $condition_to_check = $formattedCondition['conditions'];
85		- $all_values = array_merge($all_values, $formattedCondition['values']);
	143	+
	144	+ if (!is_array($condition->val) && isset($dateQuery[$condition->val])) {
	145	+ $sql .= $dbHelper->fieldQueryByDate($field, $operator, $value, $condition->logic);
86	146	} else {
87		- $condition_to_check = null;
	147	+ if (!is_int($value)) {
	148	+ $value = "'" . esc_sql($value) . "'";
	149	+ }
	150	+
	151	+ $sql .= "`{$field}` $operator $value";
88	152	}
	153	+ } elseif (in_array(strtoupper(trim((string) $condition)), ['AND', 'OR'], true)) {
	154	+ $sql .= ' ' . strtoupper(trim((string) $condition));
	155	+ }
	156	+ }
89	157
90		- $sql = "UPDATE $this->table_name
91		- SET meta_value = ( CASE meta_key
92		- $case_part
93		- END
94		- )";
95		- $sql .= " " . $condition_to_check;
96		- $status = $this->execute($sql, $all_values)->getResult();
97		- if (is_wp_error($status) && $status->get_error_code() !== 'result_empty') {
98		- return $status;
99		- }
100		- return $updatedData;
	158	+ return trim($sql);
	159	+ }
	160	+
	161	+ public function queryRecount($selectedMeta, $groupedCondition, $orderCondition, $all_values)
	162	+ {
	163	+ $entry_table = $this->app_db->prefix . 'bitforms_form_entries';
	164	+ $sql = 'SELECT count(*) as count FROM (';
	165	+ $sql .= "SELECT $selectedMeta FROM `$this->table_name` em";
	166	+ $sql .= " INNER JOIN $entry_table e on e.id = em.bitforms_form_entry_id ";
	167	+ $sql .= $groupedCondition . $orderCondition;
	168	+ $sql .= ') as retrievedData';
	169	+ $countResult = $this->execute($sql, $all_values)->getResult();
	170	+ return $countResult[0]->count;
	171	+ }
	172	+
	173	+ public function selectedEntryMeta($formFields, $fieldCount, $filter = null)
	174	+ {
	175	+ $all_values = [];
	176	+ $formFieldsNames = [];
	177	+ $globalFilterString = '';
	178	+ $globalFilterValues = [];
	179	+ $metaChecker = 0;
	180	+ $selectedMeta = '`bitforms_form_entry_id` as entry_id,';
	181	+ $selectedMeta .= "e.user_id as '__user_id',";
	182	+ $selectedMeta .= "e.user_ip as '__user_ip',";
	183	+ $selectedMeta .= "e.status as '__entry_status',";
	184	+ $selectedMeta .= "e.user_location as '__user_location',";
	185	+ $selectedMeta .= "e.user_device as '__user_device',";
	186	+ $selectedMeta .= "e.referer as '__referer',";
	187	+ $selectedMeta .= "e.created_at as '__created_at',";
	188	+ $selectedMeta .= "e.updated_at as '__updated_at'";
	189	+
	190	+ if ($fieldCount > 0) {
	191	+ $selectedMeta .= ',';
101	192	}
102	193
103		- public function getEntryMeta($formFields, $entries, $limit = null, $offset = null, $filter = null, $sortBy = null)
104		- {
105		- $selectedMeta = "`bitforms_form_entry_id` as entry_id,";
106		- $all_values = array();
107		- $metaChecker = 0;
108		- $fieldCount = count($formFields);
109		- $globalFilterString = '';
110		- $globalFilterValues = array();
111		- $formFieldsNames = array();
112		- foreach ($formFields as $fieldKey => $fieldDetails) {
113		- $fieldFormat = $this->getFieldFormat($fieldDetails['key']);
114		- $selectedMeta .= "GROUP_CONCAT(
	194	+ foreach ($formFields as $fieldDetails) {
	195	+ $safeFieldKey = sanitize_key($fieldDetails['key']);
	196	+ $fieldFormat = $this->getFieldFormat($safeFieldKey);
	197	+ $selectedMeta .= "GROUP_CONCAT(
115	198	CASE
116	199	`meta_key`
117	200	WHEN '$fieldFormat' THEN `meta_value`
118	201	END
119	202	) AS '$fieldFormat'";
120		- $metaChecker += 1;
121		- $all_values[] = $fieldDetails['key'];
122		- $all_values[] = $fieldDetails['key'];
123		- $formFieldsNames[] = $fieldDetails['key'];
124		- if ($metaChecker < $fieldCount) {
125		- $selectedMeta .= ",";
126		- }
	203	+ $metaChecker += 1;
	204	+ $all_values[] = $safeFieldKey;
	205	+ $all_values[] = $safeFieldKey;
	206	+ $formFieldsNames[] = $safeFieldKey;
	207	+ if ($metaChecker < $fieldCount) {
	208	+ $selectedMeta .= ',';
	209	+ }
	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	+ }
	217	+ }
127	218
128		- if (!empty($filter['global'])) {
129		- $globalFilterString .= " `" . $fieldDetails['key'] . "` LIKE '%%" . $this->getFieldFormat($filter['global']) . "%%' ";
130		- if ($metaChecker < $fieldCount) {
131		- $globalFilterString .= " OR ";
132		- }
133		- $globalFilterValues[] = $filter['global'];
134		- }
	219	+ return [
	220	+ 'selected_meta' => $selectedMeta,
	221	+ 'form_fields_names' => $formFieldsNames,
	222	+ 'all_values' => $all_values,
	223	+ 'global_filter_string' => $globalFilterString,
	224	+ 'global_filter_values' => $globalFilterValues,
	225	+ ];
	226	+ }
	227	+
	228	+ public function groupedCondition($condition, $all_values, $fieldConditions, $filter = null, $globalFilterString = '', $globalFilterValues = [])
	229	+ {
	230	+ $isFldCondition = false;
	231	+ $formattedCondition = $this->getFormatedCondition($condition);
	232	+ if ($formattedCondition) {
	233	+ $groupedCondition = $formattedCondition['conditions'] . 'GROUP BY
	234	+ `bitforms_form_entry_id` ';
	235	+ $all_values = array_merge($all_values, $formattedCondition['values']);
	236	+ } else {
	237	+ $groupedCondition = null;
	238	+ }
	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	+ }
	248	+
	249	+ $sqlQryByFldCondtion = $this->sqlQryGenerateByFldCondition($fieldConditions);
	250	+
	251	+ if (!empty($sqlQryByFldCondtion)) {
	252	+ $isFldCondition = true;
	253	+ if ($groupedCondition && false !== strpos($groupedCondition, 'HAVING')) {
	254	+ $groupedCondition .= ' AND (' . $sqlQryByFldCondtion . ') ';
	255	+ } else {
	256	+ $groupedCondition .= ' HAVING (' . $sqlQryByFldCondtion . ') ';
	257	+ }
	258	+ }
	259	+ return [
	260	+ 'groupedCondition' => $groupedCondition,
	261	+ 'all_values' => $all_values,
	262	+ 'isFldCondition' => $isFldCondition,
	263	+ ];
	264	+ }
	265	+
	266	+ public function orderCondition($formFieldsNames, $sortBy)
	267	+ {
	268	+ $orderCondition = null;
	269	+ if (!empty($sortBy)) {
	270	+ $sortableFieldCount = count($sortBy);
	271	+ $sortableFieldChecker = 0;
	272	+ if ($sortableFieldCount > 0) {
	273	+ $orderCondition .= ' ORDER BY ';
	274	+ }
	275	+ $orderList = '';
	276	+ foreach ($sortBy as $sortableFieldKey => $sortableFieldDetails) {
	277	+ $sortableFieldChecker += 1;
	278	+ if (in_array($sortableFieldDetails->id, $formFieldsNames, true)) {
	279	+ $safeId = sanitize_key($sortableFieldDetails->id);
	280	+ $orderList .= " `$safeId` ";
	281	+ $orderFollow = $sortableFieldDetails->desc ? ' DESC ' : ' ASC ';
	282	+ $orderList .= ' ' . $orderFollow;
	283	+ if ($sortableFieldChecker < $sortableFieldCount) {
	284	+ $orderList .= ', ';
	285	+ }
135	286	}
136		- $entryIDs = array();
137		- $entryCount = count($entries);
138		- $paginateEntry = empty($sortBy) && empty($filter['field']) && empty($filter['global']);
139		- $entries = $paginateEntry ? array_slice($entries, $offset, $limit) : $entries;
140		- foreach ($entries as $entryKey => $entryDetail) {
141		- $entryIDs[] = $entryDetail->id;
142		- }
143		- if (empty($entryIDs)) {
144		- return array(
145		- 'count' => 0,
146		- 'entries' => [],
147		- );
148		- }
149		- $condition['bitforms_form_entry_id'] = $entryIDs;
150		- $formattedCondition = $this->getFormatedCondition($condition);
151		- if ($formattedCondition) {
152		- $groupedCondition = $formattedCondition['conditions'] . " GROUP BY
153		- `bitforms_form_entry_id` ";
154		- $all_values = array_merge($all_values, $formattedCondition['values']);
155		- } else {
156		- $groupedCondition = null;
157		- }
158		- $isRecount = false;
159		- if (!empty($filter['field'])) {
160		- $isRecount = true;
161		- $filterFieldCount = count($filter['field']);
162		- $filterFieldChecker = 0;
163		- if ($filterFieldCount > 0) {
164		- $groupedCondition .= " HAVING ";
165		- }
166		- foreach ($filter['field'] as $filterFieldKey => $filterFieldDetails) {
167		- $groupedCondition .= " `$filterFieldDetails->id` ='%%" . $this->getFieldFormat($filterFieldDetails->value) . "%%'";
168		- $all_values[] = $filterFieldDetails->value;
169		- if ($filterFieldChecker < $filterFieldCount) {
170		- $groupedCondition .= " AND ";
171		- }
172		- }
173		- }
174		- if (!empty($filter['global']) && !empty($globalFilterString) && !empty($globalFilterValues)) {
175		- $isRecount = true;
176		- if (!empty($filter['field'])) {
177		- $groupedCondition .= " AND (" . $globalFilterString . ") ";
178		- } else {
179		- $groupedCondition .= " HAVING $globalFilterString ";
180		- }
181		- $offset = 0;
182		- $all_values = array_merge($all_values, $globalFilterValues);
183		- }
	287	+ }
	288	+ if (empty($orderList)) {
184	289	$orderCondition = null;
185		- if (!empty($sortBy)) {
186		- $sortableFieldCount = count($sortBy);
187		- $sortableFieldChecker = 0;
188		- if ($sortableFieldCount > 0) {
189		- $orderCondition .= " ORDER BY ";
190		- }
191		- $orderList = '';
192		- foreach ($sortBy as $sortableFieldKey => $sortableFieldDetails) {
193		- // $orderCondition .=" ".$this->getFieldFormat($sortableFieldDetails->id);
194		- $sortableFieldChecker += 1;
195		- if (in_array($sortableFieldDetails->id, $formFieldsNames)) {
196		- $orderList .= " `$sortableFieldDetails->id` ";
197		- $orderFollow = $sortableFieldDetails->desc ? ' DESC ' : ' ASC ';
198		- $orderList .= " " . $orderFollow;
199		- if ($sortableFieldChecker < $sortableFieldCount) {
200		- $orderList .= ", ";
201		- }
202		- }
203		- }
204		- if (empty($orderList)) {
205		- $orderCondition = null;
206		- } else {
207		- $orderCondition .= $orderList;
208		- }
	290	+ } else {
	291	+ $orderCondition .= $orderList;
	292	+ }
	293	+ }
	294	+ $orderCondition .= empty($orderCondition) ? ' ORDER BY `bitforms_form_entry_id` DESC ' : ',`bitforms_form_entry_id` DESC ';
	295	+ return $orderCondition;
	296	+ }
	297	+
	298	+ public function isEntryMetaExist($conditions)
	299	+ {
	300	+ $existMetaData = $this->get(
	301	+ [
	302	+ 'meta_key',
	303	+ 'meta_value',
	304	+ ],
	305	+ $conditions
	306	+ );
	307	+ if (!is_wp_error($existMetaData) && count($existMetaData) > 0) {
	308	+ return true;
	309	+ }
	310	+ return false;
	311	+ }
	312	+
	313	+ public function getEntryMeta($formFields, $entries, $limit = null, $offset = null, $filter = null, $sortBy = null, $fieldConditions = null, $dateBetweenFilter = null)
	314	+ {
	315	+ $entry_table = $this->app_db->prefix . 'bitforms_form_entries';
	316	+ $fieldCount = count($formFields);
	317	+ $getSelectedMetaFldValue = $this->selectedEntryMeta($formFields, $fieldCount, $filter);
	318	+ $selectedMeta = $getSelectedMetaFldValue['selected_meta'];
	319	+ $formFieldsNames = $getSelectedMetaFldValue['form_fields_names'];
	320	+ $all_values = $getSelectedMetaFldValue['all_values'];
	321	+ $globalFilterString = $getSelectedMetaFldValue['global_filter_string'];
	322	+ $globalFilterValues = $getSelectedMetaFldValue['global_filter_values'];
	323	+ $entryIDs = [];
	324	+ $entryCount = count($entries);
	325	+ foreach ($entries as $entryDetail) {
	326	+ $entryIDs[] = $entryDetail->id ?? $entryDetail;
	327	+ }
	328	+ if (empty($entryIDs)) {
	329	+ return [
	330	+ 'count' => 0,
	331	+ 'entries' => [],
	332	+ ];
	333	+ }
	334	+ $condition['bitforms_form_entry_id'] = $entryIDs;
	335	+ $group = $this->groupedCondition($condition, $all_values, $fieldConditions, $filter, $globalFilterString, $globalFilterValues);
	336	+ $groupedCondition = $group['groupedCondition'];
	337	+ $all_values = $group['all_values'];
	338	+ $isFldCondition = $group['isFldCondition'];
	339	+ $orderCondition = $this->orderCondition($formFieldsNames, (array) $sortBy);
	340	+
	341	+ $paginate = null;
	342	+ if (!is_null($limit)) {
	343	+ $limit = intval($limit);
	344	+ $paginate .= " LIMIT $limit ";
	345	+ }
	346	+ if (!is_null($offset)) {
	347	+ $offset = intval($offset);
	348	+ $paginate .= " OFFSET $offset ";
	349	+ }
	350	+
	351	+ $sql = "SELECT $selectedMeta FROM `$this->table_name` em";
	352	+ $sql .= " INNER JOIN $entry_table e on e.id = em.bitforms_form_entry_id ";
	353	+ if ($dateBetweenFilter) {
	354	+ $startDate = sanitize_text_field($dateBetweenFilter->start_date ?? '');
	355	+ $endDate = sanitize_text_field($dateBetweenFilter->end_date ?? '');
	356	+
	357	+ if ($startDate && $endDate) {
	358	+ $sql .= $this->app_db->prepare(' AND e.created_at BETWEEN %s AND %s', $startDate . ' 00:00:00', $endDate . ' 23:59:59');
	359	+ } elseif ($startDate) {
	360	+ $sql .= $this->app_db->prepare(' AND e.created_at >= %s', $startDate . ' 00:00:00');
	361	+ } elseif ($endDate) {
	362	+ $sql .= $this->app_db->prepare(' AND e.created_at <= %s', $endDate . ' 23:59:59');
	363	+ }
	364	+ }
	365	+ $sql .= $groupedCondition . $orderCondition . $paginate;
	366	+ $result = $this->execute($sql, $all_values)->getResult();
	367	+ if (is_wp_error($result)) {
	368	+ return [
	369	+ 'count' => 0,
	370	+ 'entries' => [],
	371	+ 'error' => $result->get_error_message()
	372	+ ];
	373	+ }
	374	+ if ($isFldCondition) {
	375	+ $entryCount = $this->queryRecount($selectedMeta, $groupedCondition, $orderCondition, $all_values);
	376	+ }
	377	+ $resultedEntries = [
	378	+ 'count' => $entryCount,
	379	+ 'entries' => $result,
	380	+ ];
	381	+ return $resultedEntries;
	382	+ }
	383	+
	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;
209	443	}
210		- $orderCondition .= empty($orderCondition) ? " ORDER BY `bitforms_form_entry_id` DESC " : ",`bitforms_form_entry_id` DESC ";
211		- $paginate = null;
212		- if (!\is_null($limit) && !$paginateEntry) {
213		- $limit = \intval($limit);
214		- $paginate .= " LIMIT $limit ";
215		- }
216		- if (!\is_null($offset) && !$paginateEntry) {
217		- $offset = \intval($offset);
218		- $paginate .= " OFFSET $offset ";
219		- }
220		- $sql = "SELECT $selectedMeta FROM `$this->table_name`"
221		- . $groupedCondition . $orderCondition . $paginate;
222		- // echo $sql;
223		- $result = $this->execute($sql, $all_values)->getResult();
224		- if (is_wp_error($result) && $result->get_error_code() === 'result_empty') {
225		- return array(
226		- 'count' => $paginateEntry ? $entryCount : 0,
227		- 'entries' => [],
228		- );
229		- }
230		- // var_dump('test');
231		- if ($isRecount) {
232		- $sql = "SELECT count(*) as count FROM (";
233		- $sql .= "SELECT $selectedMeta FROM `$this->table_name`";
234		- $sql .= $groupedCondition . $orderCondition;
235		- $sql .= ") as retrievedData";
236		- $countResult = $this->execute($sql, $all_values)->getResult();
237		- if (!(is_wp_error($result) && $result->get_error_code() === 'result_empty')) {
238		- $entryCount = $countResult[0]->count;
239		- }
240		- }
241		- $resultedEntries = array(
242		- 'count' => $entryCount,
243		- 'entries' => $result,
244		- );
245		- return $resultedEntries;
	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));
	451	+ }
	452	+
	453	+ $rows[] = implode(', ', $formattedPairs);
246	454	}
247	455
248		- public function getExportEntry($formFields, $entries, $limit = null, $sortBy = null, $sortByField = null, $filter = null)
249		- {
250		- $selectedEntryMeta = null;
251		- $fieldCount = count($formFields);
252		- $metaChecker = 0;
253		- $all_values = array();
254		- // $selected_fields = ['bf29-1-','bf29-2-','bf29-6'];
255		- $formFieldsNames = array();
256		- foreach ($formFields as $fieldKey => $fieldDetails) {
257		- $fieldFormat = $this->getFieldFormat($fieldDetails['key']);
258		- $selectedEntryMeta .= "GROUP_CONCAT(
	456	+ return implode('; ', $rows);
	457	+ }
	458	+
	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;
	495	+ }
	496	+ $fieldFormat = $this->getFieldFormat($fldKey);
	497	+ $selectedEntryMeta .= "GROUP_CONCAT(
259	498	CASE
260	499	`meta_key`
261	500	WHEN '$fieldFormat' THEN `meta_value`
262	501	END
263	502	) AS '$fieldFormat'";
264		- $metaChecker += 1;
265		- $all_values[] = $fieldDetails['key'];
266		- $all_values[] = $fieldDetails['key'];
267		- $formFieldsNames[] = $fieldDetails['key'];
268		- if ($metaChecker < $fieldCount) {
269		- $selectedEntryMeta .= ",";
270		- }
	503	+ $metaChecker += 1;
	504	+ $all_values[] = $fldKey;
	505	+ $all_values[] = $fldKey;
	506	+ if ($metaChecker < $fieldCount) {
	507	+ $selectedEntryMeta .= ',';
	508	+ }
	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 ";
	539	+ }
	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;
	568	+ }
	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);
271	581	}
272		- $entryIDs = array();
273		- foreach ($entries as $entryKey => $entryDetail) {
274		- $entryIDs[] = $entryDetail->id;
	582	+ }
	583	+ }
	584	+
	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;
	597	+ }
	598	+ }
	599	+
	600	+ foreach ($allData as &$entry) {
	601	+ foreach ($entry as $key => &$value) {
	602	+ if (is_string($value)) {
	603	+ $value = self::csvInjectionPrevent(self::unescapeString($value));
275	604	}
276		- if (empty($entryIDs)) {
277		- return array(
278		- 'count' => 0,
279		- 'entries' => [],
280		- );
	605	+ if (in_array($key, $repeaterFields, true)) {
	606	+ $value = self::formatRepeaterValue($value, $fieldMap);
281	607	}
282		- $condition['bitforms_form_entry_id'] = $entryIDs;
283		- $formattedCondition = $this->getFormatedCondition($condition);
284		- $groupedCondition = null;
285		- if ($formattedCondition) {
286		- $groupedCondition = $formattedCondition['conditions'] . " GROUP BY
287		- `bitforms_form_entry_id` ";
288		- $all_values = array_merge($all_values, $formattedCondition['values']);
	608	+ }
	609	+ unset($value);
	610	+ }
	611	+ unset($entry, $value);
	612	+
	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;
289	619	}
290		- $order = \is_null($sortBy) ? "DESC " : "$sortBy";
291		- $orderField = \is_null($sortByField) ? "bitforms_form_entry_id " : "`$sortByField`";
292		- $orderCondition = "ORDER BY $orderField $order ";
293		- if (!\is_null($limit)) {
294		- $limitInt = \intval($limit);
295		- $limit = " LIMIT $limitInt ";
	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);
	626	+ }
296	627	}
297		- $sql = "SELECT $selectedEntryMeta FROM `$this->table_name`"
298		- . $groupedCondition . $orderCondition . $limit;
299		- $result = $this->execute($sql, $all_values)->getResult();
300		- $allData = [];
301		- foreach ($result as $key => $value) {
302		- foreach ($formFieldsNames as $formFieldName) {
303		- $allData[$key][$formFieldName] = preg_replace('/[\]["]/i', '', $value->$formFieldName);
304		- }
305		- }
306		- if (is_wp_error($result)) {
307		- wp_send_json_error('Internal server error', 500);
308		- } else {
309		- wp_send_json_success($allData, 200);
310		- }
	628	+ $entry[$fileKey] = implode(',', $urls);
	629	+ }
311	630	}
	631	+ unset($entry);
	632	+
	633	+ return $allData;
	634	+ }
312	635	}