← All changes | includes/Core/Database/FormEntryMetaModel.php +584 -334 1.4.18 → 3.3.1 View file →

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