← All changes | includes/Core/Database/FormEntryMetaModel.php +617 -332 1.4 → 2.21.4 View file →

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