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