| @@ -9,304 +9,531 @@ | ||
| 9 | 9 | /** |
| 10 | 10 | * Undocumented class |
| 11 | 11 | */ |
| 12 | 12 | |
| 13 | -use BitCode\BitForm\Core\Database\Model; | |
| 14 | - | |
| 15 | 13 | class FormEntryMetaModel extends Model |
| 16 | 14 | { |
| 17 | - protected static $table = 'bitforms_form_entrymeta'; | |
| 15 | + protected static $table = 'bitforms_form_entrymeta'; | |
| 18 | 16 | |
| 17 | + public function duplicateEntryMeta($data) | |
| 18 | + { | |
| 19 | + $values[] = $data['duplicateID']; | |
| 20 | + $values[] = $data['entryID']; | |
| 21 | + $sql = "INSERT INTO $this->table_name (bitforms_form_entry_id,meta_key,meta_value)" | |
| 22 | + . ' SELECT %d as bitforms_form_entry_id,meta_key,meta_value' | |
| 23 | + . " FROM `$this->table_name` WHERE bitforms_form_entry_id = %d"; | |
| 24 | + return $this->execute($sql, $values)->getResult(); | |
| 25 | + } | |
| 19 | 26 | |
| 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(); | |
| 27 | + public function update(array $data, array $condition) | |
| 28 | + { | |
| 29 | + $entryID = $condition['bitforms_form_entry_id']; | |
| 30 | + if (empty($entryID)) { | |
| 31 | + return false; | |
| 28 | 32 | } |
| 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 | - ) | |
| 33 | + $formEntryMeta = $this->get( | |
| 34 | + [ | |
| 35 | + 'meta_key', | |
| 36 | + 'meta_value', | |
| 37 | + ], | |
| 38 | + [ | |
| 39 | + 'bitforms_form_entry_id' => $entryID, | |
| 40 | + ] | |
| 41 | + ); | |
| 42 | + $oldEntries = []; | |
| 43 | + foreach ($formEntryMeta as $oldKey => $oldValue) { | |
| 44 | + $oldEntries[$oldValue->meta_key] = $oldValue->meta_value; | |
| 45 | + } | |
| 46 | + $updatedData = []; | |
| 47 | + $oldEntriesKey = array_keys($oldEntries); | |
| 48 | + foreach ($data as $upKey => $upValue) { | |
| 49 | + $updatedData[$upKey] = is_string($upValue) ? | |
| 50 | + $upValue : | |
| 51 | + wp_json_encode($upValue); | |
| 52 | + if (!\in_array($upKey, $oldEntriesKey)) { | |
| 53 | + $this->insert( | |
| 54 | + [ | |
| 55 | + 'bitforms_form_entry_id' => $entryID, | |
| 56 | + 'meta_key' => $upKey, | |
| 57 | + 'meta_value' => $updatedData[$upKey], | |
| 58 | + ] | |
| 44 | 59 | ); |
| 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 .= " | |
| 60 | + unset($data[$upKey]); | |
| 61 | + } | |
| 62 | + } | |
| 63 | + if (empty($data)) { | |
| 64 | + return $updatedData; | |
| 65 | + } | |
| 66 | + $checkCondition = $this->checkCondition($condition); | |
| 67 | + if (is_wp_error($checkCondition)) { | |
| 68 | + return $checkCondition; | |
| 69 | + } | |
| 70 | + $case_part = ' '; | |
| 71 | + $all_values = []; | |
| 72 | + $condition['meta_key'] = array_keys($data); | |
| 73 | + foreach ($data as $key => $value) { | |
| 74 | + $value = is_string($value) ? $value : wp_json_encode($value); | |
| 75 | + $case_part .= " | |
| 79 | 76 | 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 | - } | |
| 77 | + $all_values[] = $value; | |
| 78 | + } | |
| 79 | + $formattedCondition = $this->getFormatedCondition($condition); | |
| 80 | + if ($formattedCondition) { | |
| 81 | + $condition_to_check = $formattedCondition['conditions']; | |
| 82 | + $all_values = array_merge($all_values, $formattedCondition['values']); | |
| 83 | + } else { | |
| 84 | + $condition_to_check = null; | |
| 85 | + } | |
| 89 | 86 | |
| 90 | - $sql = "UPDATE $this->table_name | |
| 87 | + $sql = "UPDATE $this->table_name | |
| 91 | 88 | SET meta_value = ( CASE meta_key |
| 92 | 89 | $case_part |
| 93 | 90 | END |
| 94 | 91 | )"; |
| 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; | |
| 92 | + $sql .= ' ' . $condition_to_check; | |
| 93 | + $status = $this->execute($sql, $all_values)->getResult(); | |
| 94 | + if (is_wp_error($status) && 'result_empty' !== $status->get_error_code()) { | |
| 95 | + return $status; | |
| 96 | + } | |
| 97 | + return $updatedData; | |
| 98 | + } | |
| 99 | + | |
| 100 | + public function validQueryCondition($conditions) | |
| 101 | + { | |
| 102 | + foreach ($conditions as $index => $condition) { | |
| 103 | + if (is_object($condition)) { | |
| 104 | + if (empty($condition->field) || empty($condition->logic) || empty($condition->val) && !in_array($condition->val, ['0', 0, 0.0], true)) { | |
| 105 | + unset($conditions[$index], $conditions[$index + 1]); | |
| 99 | 106 | } |
| 100 | - return $updatedData; | |
| 107 | + } | |
| 101 | 108 | } |
| 109 | + return $conditions; | |
| 110 | + } | |
| 102 | 111 | |
| 103 | - public function getEntryMeta($formFields, $entries, $limit = null, $offset = null, $filter = null, $sortBy = null) | |
| 104 | - { | |
| 105 | - $selectedMeta = "`bitforms_form_entry_id` as entry_id,"; | |
| 106 | - $all_values = array(); | |
| 107 | - $metaChecker = 0; | |
| 108 | - $fieldCount = count($formFields); | |
| 109 | - $globalFilterString = ''; | |
| 110 | - $globalFilterValues = array(); | |
| 111 | - $formFieldsNames = array(); | |
| 112 | - foreach ($formFields as $fieldKey => $fieldDetails) { | |
| 113 | - $fieldFormat = $this->getFieldFormat($fieldDetails['key']); | |
| 114 | - $selectedMeta .= "GROUP_CONCAT( | |
| 112 | + public function sqlQryGenerateByFldCondition($conditions) | |
| 113 | + { | |
| 114 | + $dbHelper = new Helper(); | |
| 115 | + $sql = ''; | |
| 116 | + | |
| 117 | + $dateQuery = $dbHelper->dateQueryList(); | |
| 118 | + | |
| 119 | + $validCondtions = $this->validQueryCondition($conditions); | |
| 120 | + | |
| 121 | + foreach ($validCondtions as $condition) { | |
| 122 | + if (is_object($condition)) { | |
| 123 | + if (is_array($condition->val)) { | |
| 124 | + $value = $dbHelper->arrValueModifyByLogic($condition->logic, $condition->val); | |
| 125 | + } else { | |
| 126 | + $value = $dbHelper->strValueModifyByLogic($condition->logic, $condition->val); | |
| 127 | + } | |
| 128 | + | |
| 129 | + $operator = $dbHelper->convertToSqlOperator($condition->logic); | |
| 130 | + | |
| 131 | + if (isset($dateQuery[$condition->val])) { | |
| 132 | + $sql .= $dbHelper->fieldQueryByDate($condition->field, $operator, $value, $condition->logic); | |
| 133 | + } else { | |
| 134 | + if (!is_int($value)) { | |
| 135 | + $value = "'" . $value . "'"; | |
| 136 | + } | |
| 137 | + | |
| 138 | + $sql .= "`$condition->field` $operator $value"; | |
| 139 | + } | |
| 140 | + } else { | |
| 141 | + $sql .= ' ' . $condition; | |
| 142 | + } | |
| 143 | + } | |
| 144 | + | |
| 145 | + return trim($sql); | |
| 146 | + } | |
| 147 | + | |
| 148 | + public function queryRecount($selectedMeta, $groupedCondition, $orderCondition, $all_values) | |
| 149 | + { | |
| 150 | + $entry_table = $this->app_db->prefix . 'bitforms_form_entries'; | |
| 151 | + $sql = 'SELECT count(*) as count FROM ('; | |
| 152 | + $sql .= "SELECT $selectedMeta FROM `$this->table_name` em"; | |
| 153 | + $sql .= " INNER JOIN $entry_table e on e.id = em.bitforms_form_entry_id "; | |
| 154 | + $sql .= $groupedCondition . $orderCondition; | |
| 155 | + $sql .= ') as retrievedData'; | |
| 156 | + $countResult = $this->execute($sql, $all_values)->getResult(); | |
| 157 | + return $countResult[0]->count; | |
| 158 | + } | |
| 159 | + | |
| 160 | + public function selectedEntryMeta($formFields, $fieldCount) | |
| 161 | + { | |
| 162 | + $all_values = []; | |
| 163 | + $formFieldsNames = []; | |
| 164 | + $metaChecker = 0; | |
| 165 | + $selectedMeta = '`bitforms_form_entry_id` as entry_id,'; | |
| 166 | + $selectedMeta .= "e.user_id as '__user_id',"; | |
| 167 | + $selectedMeta .= "e.user_ip as '__user_ip',"; | |
| 168 | + $selectedMeta .= "e.status as '__entry_status',"; | |
| 169 | + $selectedMeta .= "e.user_location as '__user_location',"; | |
| 170 | + $selectedMeta .= "e.user_device as '__user_device',"; | |
| 171 | + $selectedMeta .= "e.referer as '__referer',"; | |
| 172 | + $selectedMeta .= "e.created_at as '__created_at',"; | |
| 173 | + $selectedMeta .= "e.updated_at as '__updated_at'"; | |
| 174 | + | |
| 175 | + if ($fieldCount > 0) { | |
| 176 | + $selectedMeta .= ','; | |
| 177 | + } | |
| 178 | + | |
| 179 | + foreach ($formFields as $fieldDetails) { | |
| 180 | + $fieldFormat = $this->getFieldFormat($fieldDetails['key']); | |
| 181 | + $selectedMeta .= "GROUP_CONCAT( | |
| 115 | 182 | CASE |
| 116 | 183 | `meta_key` |
| 117 | 184 | WHEN '$fieldFormat' THEN `meta_value` |
| 118 | 185 | END |
| 119 | 186 | ) AS '$fieldFormat'"; |
| 120 | - $metaChecker += 1; | |
| 121 | - $all_values[] = $fieldDetails['key']; | |
| 122 | - $all_values[] = $fieldDetails['key']; | |
| 123 | - $formFieldsNames[] = $fieldDetails['key']; | |
| 124 | - if ($metaChecker < $fieldCount) { | |
| 125 | - $selectedMeta .= ","; | |
| 126 | - } | |
| 187 | + $metaChecker += 1; | |
| 188 | + $all_values[] = $fieldDetails['key']; | |
| 189 | + $all_values[] = $fieldDetails['key']; | |
| 190 | + $formFieldsNames[] = $fieldDetails['key']; | |
| 191 | + if ($metaChecker < $fieldCount) { | |
| 192 | + $selectedMeta .= ','; | |
| 193 | + } | |
| 194 | + //#unused code commented by me## | |
| 195 | + // if ( !empty( $filter['global'] ) ) { | |
| 196 | + // $globalFilterString .= " `" . $fieldDetails['key'] . "` LIKE '%%" . $this->getFieldFormat( $filter['global'] ) . "%%' "; | |
| 197 | + // if ( $metaChecker < $fieldCount ) { | |
| 198 | + // $globalFilterString .= " OR "; | |
| 199 | + // } | |
| 200 | + // $globalFilterValues[] = $filter['global']; | |
| 201 | + // } | |
| 202 | + } | |
| 127 | 203 | |
| 128 | - if (!empty($filter['global'])) { | |
| 129 | - $globalFilterString .= " `" . $fieldDetails['key'] . "` LIKE '%%" . $this->getFieldFormat($filter['global']) . "%%' "; | |
| 130 | - if ($metaChecker < $fieldCount) { | |
| 131 | - $globalFilterString .= " OR "; | |
| 132 | - } | |
| 133 | - $globalFilterValues[] = $filter['global']; | |
| 134 | - } | |
| 204 | + return [ | |
| 205 | + 'selected_meta' => $selectedMeta, | |
| 206 | + 'form_fields_names' => $formFieldsNames, | |
| 207 | + 'all_values' => $all_values, | |
| 208 | + ]; | |
| 209 | + } | |
| 210 | + | |
| 211 | + public function groupedCondition($condition, $all_values, $fieldConditions) | |
| 212 | + { | |
| 213 | + $isFldCondition = false; | |
| 214 | + $formattedCondition = $this->getFormatedCondition($condition); | |
| 215 | + if ($formattedCondition) { | |
| 216 | + $groupedCondition = $formattedCondition['conditions'] . 'GROUP BY | |
| 217 | + `bitforms_form_entry_id` '; | |
| 218 | + $all_values = array_merge($all_values, $formattedCondition['values']); | |
| 219 | + } else { | |
| 220 | + $groupedCondition = null; | |
| 221 | + } | |
| 222 | + //#unused code commented by me## | |
| 223 | + //$isRecount = false; | |
| 224 | + | |
| 225 | + // if ( !empty( $filter['field'] ) ) { | |
| 226 | + // $isRecount = true; | |
| 227 | + // $filterFieldCount = count( $filter['field'] ); | |
| 228 | + // $filterFieldChecker = 0; | |
| 229 | + // if ( $filterFieldCount > 0 ) { | |
| 230 | + // $groupedCondition .= " HAVING "; | |
| 231 | + // } | |
| 232 | + // foreach ( $filter['field'] as $filterFieldKey => $filterFieldDetails ) { | |
| 233 | + // $groupedCondition .= " `$filterFieldDetails->id` ='%%" . $this->getFieldFormat( $filterFieldDetails->value ) . "%%'"; | |
| 234 | + // $all_values[] = $filterFieldDetails->value; | |
| 235 | + // if ( $filterFieldChecker < $filterFieldCount ) { | |
| 236 | + // $groupedCondition .= " AND "; | |
| 237 | + // } | |
| 238 | + // } | |
| 239 | + // } | |
| 240 | + | |
| 241 | + // if ( !empty( $filter['global'] ) && !empty( $globalFilterString ) && !empty( $globalFilterValues ) ) { | |
| 242 | + // $isRecount = true; | |
| 243 | + // if ( !empty( $filter['field'] ) ) { | |
| 244 | + // $groupedCondition .= " AND (" . $globalFilterString . ") "; | |
| 245 | + // } else { | |
| 246 | + // $groupedCondition .= " HAVING $globalFilterString "; | |
| 247 | + // } | |
| 248 | + // $offset = 0; | |
| 249 | + // $all_values = array_merge( $all_values, $globalFilterValues ); | |
| 250 | + // } | |
| 251 | + | |
| 252 | + // if ( !empty( $dateBetweenFilter ) && !empty( $dateBetweenFilter->start_date ) && !empty( $dateBetweenFilter->end_date ) ) { | |
| 253 | + // if ( strpos( $groupedCondition, 'HAVING' ) !== false ) { | |
| 254 | + // $groupedCondition .= " AND `__created_at` BETWEEN '" . $dateBetweenFilter->start_date . "' AND '" . $dateBetweenFilter->end_date . "' "; | |
| 255 | + // } else { | |
| 256 | + // $groupedCondition .= " HAVING `__created_at` BETWEEN '" . $dateBetweenFilter->start_date . "' AND '" . $dateBetweenFilter->end_date . "' "; | |
| 257 | + // } | |
| 258 | + // } | |
| 259 | + | |
| 260 | + $sqlQryByFldCondtion = $this->sqlQryGenerateByFldCondition($fieldConditions); | |
| 261 | + | |
| 262 | + if (!empty($sqlQryByFldCondtion)) { | |
| 263 | + $isFldCondition = true; | |
| 264 | + if (false !== strpos($groupedCondition, 'HAVING')) { | |
| 265 | + $groupedCondition .= ' AND (' . $sqlQryByFldCondtion . ') '; | |
| 266 | + } else { | |
| 267 | + $groupedCondition .= ' HAVING (' . $sqlQryByFldCondtion . ') '; | |
| 268 | + } | |
| 269 | + } | |
| 270 | + return [ | |
| 271 | + 'groupedCondition' => $groupedCondition, | |
| 272 | + 'all_values' => $all_values, | |
| 273 | + 'isFldCondition' => $isFldCondition, | |
| 274 | + ]; | |
| 275 | + } | |
| 276 | + | |
| 277 | + public function orderCondition($formFieldsNames, $sortBy) | |
| 278 | + { | |
| 279 | + $orderCondition = null; | |
| 280 | + if (!empty($sortBy)) { | |
| 281 | + $sortableFieldCount = count($sortBy); | |
| 282 | + $sortableFieldChecker = 0; | |
| 283 | + if ($sortableFieldCount > 0) { | |
| 284 | + $orderCondition .= ' ORDER BY '; | |
| 285 | + } | |
| 286 | + $orderList = ''; | |
| 287 | + foreach ($sortBy as $sortableFieldKey => $sortableFieldDetails) { | |
| 288 | + // $orderCondition .=" ".$this->getFieldFormat($sortableFieldDetails->id); | |
| 289 | + $sortableFieldChecker += 1; | |
| 290 | + if (in_array($sortableFieldDetails->id, $formFieldsNames)) { | |
| 291 | + $orderList .= " `$sortableFieldDetails->id` "; | |
| 292 | + $orderFollow = $sortableFieldDetails->desc ? ' DESC ' : ' ASC '; | |
| 293 | + $orderList .= ' ' . $orderFollow; | |
| 294 | + if ($sortableFieldChecker < $sortableFieldCount) { | |
| 295 | + $orderList .= ', '; | |
| 296 | + } | |
| 135 | 297 | } |
| 136 | - $entryIDs = array(); | |
| 137 | - $entryCount = count($entries); | |
| 138 | - $paginateEntry = empty($sortBy) && empty($filter['field']) && empty($filter['global']); | |
| 139 | - $entries = $paginateEntry ? array_slice($entries, $offset, $limit) : $entries; | |
| 140 | - foreach ($entries as $entryKey => $entryDetail) { | |
| 141 | - $entryIDs[] = $entryDetail->id; | |
| 142 | - } | |
| 143 | - if (empty($entryIDs)) { | |
| 144 | - return array( | |
| 145 | - 'count' => 0, | |
| 146 | - 'entries' => [], | |
| 147 | - ); | |
| 148 | - } | |
| 149 | - $condition['bitforms_form_entry_id'] = $entryIDs; | |
| 150 | - $formattedCondition = $this->getFormatedCondition($condition); | |
| 151 | - if ($formattedCondition) { | |
| 152 | - $groupedCondition = $formattedCondition['conditions'] . " GROUP BY | |
| 153 | - `bitforms_form_entry_id` "; | |
| 154 | - $all_values = array_merge($all_values, $formattedCondition['values']); | |
| 155 | - } else { | |
| 156 | - $groupedCondition = null; | |
| 157 | - } | |
| 158 | - $isRecount = false; | |
| 159 | - if (!empty($filter['field'])) { | |
| 160 | - $isRecount = true; | |
| 161 | - $filterFieldCount = count($filter['field']); | |
| 162 | - $filterFieldChecker = 0; | |
| 163 | - if ($filterFieldCount > 0) { | |
| 164 | - $groupedCondition .= " HAVING "; | |
| 165 | - } | |
| 166 | - foreach ($filter['field'] as $filterFieldKey => $filterFieldDetails) { | |
| 167 | - $groupedCondition .= " `$filterFieldDetails->id` ='%%" . $this->getFieldFormat($filterFieldDetails->value) . "%%'"; | |
| 168 | - $all_values[] = $filterFieldDetails->value; | |
| 169 | - if ($filterFieldChecker < $filterFieldCount) { | |
| 170 | - $groupedCondition .= " AND "; | |
| 171 | - } | |
| 172 | - } | |
| 173 | - } | |
| 174 | - if (!empty($filter['global']) && !empty($globalFilterString) && !empty($globalFilterValues)) { | |
| 175 | - $isRecount = true; | |
| 176 | - if (!empty($filter['field'])) { | |
| 177 | - $groupedCondition .= " AND (" . $globalFilterString . ") "; | |
| 178 | - } else { | |
| 179 | - $groupedCondition .= " HAVING $globalFilterString "; | |
| 180 | - } | |
| 181 | - $offset = 0; | |
| 182 | - $all_values = array_merge($all_values, $globalFilterValues); | |
| 183 | - } | |
| 298 | + } | |
| 299 | + if (empty($orderList)) { | |
| 184 | 300 | $orderCondition = null; |
| 185 | - if (!empty($sortBy)) { | |
| 186 | - $sortableFieldCount = count($sortBy); | |
| 187 | - $sortableFieldChecker = 0; | |
| 188 | - if ($sortableFieldCount > 0) { | |
| 189 | - $orderCondition .= " ORDER BY "; | |
| 190 | - } | |
| 191 | - $orderList = ''; | |
| 192 | - foreach ($sortBy as $sortableFieldKey => $sortableFieldDetails) { | |
| 193 | - // $orderCondition .=" ".$this->getFieldFormat($sortableFieldDetails->id); | |
| 194 | - $sortableFieldChecker += 1; | |
| 195 | - if (in_array($sortableFieldDetails->id, $formFieldsNames)) { | |
| 196 | - $orderList .= " `$sortableFieldDetails->id` "; | |
| 197 | - $orderFollow = $sortableFieldDetails->desc ? ' DESC ' : ' ASC '; | |
| 198 | - $orderList .= " " . $orderFollow; | |
| 199 | - if ($sortableFieldChecker < $sortableFieldCount) { | |
| 200 | - $orderList .= ", "; | |
| 201 | - } | |
| 202 | - } | |
| 203 | - } | |
| 204 | - if (empty($orderList)) { | |
| 205 | - $orderCondition = null; | |
| 206 | - } else { | |
| 207 | - $orderCondition .= $orderList; | |
| 208 | - } | |
| 209 | - } | |
| 210 | - $orderCondition .= empty($orderCondition) ? " ORDER BY `bitforms_form_entry_id` DESC " : ",`bitforms_form_entry_id` DESC "; | |
| 211 | - $paginate = null; | |
| 212 | - if (!\is_null($limit) && !$paginateEntry) { | |
| 213 | - $limit = \intval($limit); | |
| 214 | - $paginate .= " LIMIT $limit "; | |
| 215 | - } | |
| 216 | - if (!\is_null($offset) && !$paginateEntry) { | |
| 217 | - $offset = \intval($offset); | |
| 218 | - $paginate .= " OFFSET $offset "; | |
| 219 | - } | |
| 220 | - $sql = "SELECT $selectedMeta FROM `$this->table_name`" | |
| 221 | - . $groupedCondition . $orderCondition . $paginate; | |
| 222 | - // echo $sql; | |
| 223 | - $result = $this->execute($sql, $all_values)->getResult(); | |
| 224 | - if (is_wp_error($result) && $result->get_error_code() === 'result_empty') { | |
| 225 | - return array( | |
| 226 | - 'count' => $paginateEntry ? $entryCount : 0, | |
| 227 | - 'entries' => [], | |
| 228 | - ); | |
| 229 | - } | |
| 230 | - // var_dump('test'); | |
| 231 | - if ($isRecount) { | |
| 232 | - $sql = "SELECT count(*) as count FROM ("; | |
| 233 | - $sql .= "SELECT $selectedMeta FROM `$this->table_name`"; | |
| 234 | - $sql .= $groupedCondition . $orderCondition; | |
| 235 | - $sql .= ") as retrievedData"; | |
| 236 | - $countResult = $this->execute($sql, $all_values)->getResult(); | |
| 237 | - if (!(is_wp_error($result) && $result->get_error_code() === 'result_empty')) { | |
| 238 | - $entryCount = $countResult[0]->count; | |
| 239 | - } | |
| 240 | - } | |
| 241 | - $resultedEntries = array( | |
| 242 | - 'count' => $entryCount, | |
| 243 | - 'entries' => $result, | |
| 244 | - ); | |
| 245 | - return $resultedEntries; | |
| 301 | + } else { | |
| 302 | + $orderCondition .= $orderList; | |
| 303 | + } | |
| 246 | 304 | } |
| 305 | + $orderCondition .= empty($orderCondition) ? ' ORDER BY `bitforms_form_entry_id` DESC ' : ',`bitforms_form_entry_id` DESC '; | |
| 306 | + return $orderCondition; | |
| 307 | + } | |
| 247 | 308 | |
| 248 | - public function getExportEntry($formFields, $entries, $limit = null, $sortBy = null, $sortByField = null, $filter = null) | |
| 309 | + public function isEntryMetaExist($conditions) | |
| 310 | + { | |
| 311 | + $existMetaData = $this->get( | |
| 312 | + [ | |
| 313 | + 'meta_key', | |
| 314 | + 'meta_value', | |
| 315 | + ], | |
| 316 | + $conditions | |
| 317 | + ); | |
| 318 | + if (!is_wp_error($existMetaData) && count($existMetaData) > 0) { | |
| 319 | + return true; | |
| 320 | + } | |
| 321 | + return false; | |
| 322 | + } | |
| 323 | + | |
| 324 | + public function getEntryMeta($formFields, $entries, $limit = null, $offset = null, $filter = null, $sortBy = null, $fieldConditions = null, $dateBetweenFilter = null) | |
| 325 | + { | |
| 326 | + $entry_table = $this->app_db->prefix . 'bitforms_form_entries'; | |
| 327 | + $fieldCount = count($formFields); | |
| 328 | + $getSelectedMetaFldValue = $this->selectedEntryMeta($formFields, $fieldCount); | |
| 329 | + | |
| 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[] = $entryDetail->id; | |
| 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, $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 | + private function csvInjectionPrevent($value) | |
| 249 | 399 | { |
| 250 | - $selectedEntryMeta = null; | |
| 251 | - $fieldCount = count($formFields); | |
| 252 | - $metaChecker = 0; | |
| 253 | - $all_values = array(); | |
| 254 | - // $selected_fields = ['bf29-1-','bf29-2-','bf29-6']; | |
| 255 | - $formFieldsNames = array(); | |
| 256 | - foreach ($formFields as $fieldKey => $fieldDetails) { | |
| 257 | - $fieldFormat = $this->getFieldFormat($fieldDetails['key']); | |
| 258 | - $selectedEntryMeta .= "GROUP_CONCAT( | |
| 400 | + $formula = ['=', '-', '+', '@', "\t", "\r"]; | |
| 401 | + $valueFilter = preg_replace('/[\]["]/i', '', $value); | |
| 402 | + if (\in_array(substr($value, 0, 1), $formula, true)) { | |
| 403 | + $valueFilter = "'" . trim($valueFilter); | |
| 404 | + } | |
| 405 | + | |
| 406 | + return $valueFilter; | |
| 407 | + } | |
| 408 | + | |
| 409 | + public function getExportEntry($formFields, $entries, $formId, $fieldLabels, $limit = null, $sortBy = null, $sortByField = null) | |
| 410 | + { | |
| 411 | + $entry_table = $this->app_db->prefix . 'bitforms_form_entries'; | |
| 412 | + $selectedEntryMeta = '`bitforms_form_entry_id` as entry_id,'; | |
| 413 | + $selectedEntryMeta .= "e.user_id as '__user_id',"; | |
| 414 | + $selectedEntryMeta .= "e.status as '__entry_status',"; | |
| 415 | + $selectedEntryMeta .= "e.user_ip as '__user_ip',"; | |
| 416 | + $selectedEntryMeta .= "e.user_location as '__user_location',"; | |
| 417 | + $selectedEntryMeta .= "e.user_device as '__user_device',"; | |
| 418 | + $selectedEntryMeta .= "e.referer as '__referer',"; | |
| 419 | + $selectedEntryMeta .= "e.created_at as '__created_at',"; | |
| 420 | + $selectedEntryMeta .= "e.updated_at as '__updated_at',"; | |
| 421 | + $metaChecker = 0; | |
| 422 | + | |
| 423 | + $entryInfo = ['__user_id', '__user_ip', /* '__user_location', */'__user_device', | |
| 424 | + '__referer', '__created_at', '__updated_at']; | |
| 425 | + $all_values = []; | |
| 426 | + if ([] === $formFields) { | |
| 427 | + $data = [ | |
| 428 | + 'count' => 0, | |
| 429 | + 'entries' => [], | |
| 430 | + ]; | |
| 431 | + wp_send_json_success($data, 200); | |
| 432 | + } | |
| 433 | + $fieldCount = count($formFields) - count(array_intersect($formFields, $entryInfo)); | |
| 434 | + $formFieldsNames = []; | |
| 435 | + foreach ($formFields as $fldKey) { | |
| 436 | + $formFieldsNames[] = $fldKey; | |
| 437 | + if (in_array($fldKey, $entryInfo)) { | |
| 438 | + continue; | |
| 439 | + } | |
| 440 | + $fieldFormat = $this->getFieldFormat($fldKey); | |
| 441 | + $selectedEntryMeta .= "GROUP_CONCAT( | |
| 259 | 442 | CASE |
| 260 | 443 | `meta_key` |
| 261 | 444 | WHEN '$fieldFormat' THEN `meta_value` |
| 262 | 445 | END |
| 263 | 446 | ) AS '$fieldFormat'"; |
| 264 | - $metaChecker += 1; | |
| 265 | - $all_values[] = $fieldDetails['key']; | |
| 266 | - $all_values[] = $fieldDetails['key']; | |
| 267 | - $formFieldsNames[] = $fieldDetails['key']; | |
| 268 | - if ($metaChecker < $fieldCount) { | |
| 269 | - $selectedEntryMeta .= ","; | |
| 270 | - } | |
| 447 | + $metaChecker += 1; | |
| 448 | + $all_values[] = $fldKey; | |
| 449 | + $all_values[] = $fldKey; | |
| 450 | + if ($metaChecker < $fieldCount) { | |
| 451 | + $selectedEntryMeta .= ','; | |
| 271 | 452 | } |
| 272 | - $entryIDs = array(); | |
| 273 | - foreach ($entries as $entryKey => $entryDetail) { | |
| 274 | - $entryIDs[] = $entryDetail->id; | |
| 453 | + } | |
| 454 | + $entryIDs = []; | |
| 455 | + foreach ($entries as $entryDetail) { | |
| 456 | + $entryIDs[] = $entryDetail->id; | |
| 457 | + } | |
| 458 | + if (empty($entryIDs)) { | |
| 459 | + return [ | |
| 460 | + 'count' => 0, | |
| 461 | + 'entries' => [], | |
| 462 | + ]; | |
| 463 | + } | |
| 464 | + $condition['bitforms_form_entry_id'] = $entryIDs; | |
| 465 | + $formattedCondition = $this->getFormatedCondition($condition); | |
| 466 | + $groupedCondition = null; | |
| 467 | + if ($formattedCondition) { | |
| 468 | + $groupedCondition = $formattedCondition['conditions'] . ' GROUP BY | |
| 469 | + `bitforms_form_entry_id` '; | |
| 470 | + $all_values = array_merge($all_values, $formattedCondition['values']); | |
| 471 | + } | |
| 472 | + $order = \is_null($sortBy) ? 'DESC ' : "$sortBy"; | |
| 473 | + $orderField = \is_null($sortByField) ? 'bitforms_form_entry_id' : "`$sortByField`"; | |
| 474 | + | |
| 475 | + $orderCondition = "ORDER BY $orderField $order "; | |
| 476 | + if (!\is_null($limit)) { | |
| 477 | + $limitInt = \intval($limit); | |
| 478 | + $limit = " LIMIT $limitInt "; | |
| 479 | + } | |
| 480 | + $sql = "SELECT $selectedEntryMeta FROM `$this->table_name` em"; | |
| 481 | + $sql .= " INNER JOIN $entry_table e on e.id = em.bitforms_form_entry_id "; | |
| 482 | + $sql .= $groupedCondition . $orderCondition . $limit; | |
| 483 | + | |
| 484 | + $result = $this->execute($sql, $all_values)->getResult(); | |
| 485 | + $allData = []; | |
| 486 | + $entry_id = 'entry_id'; | |
| 487 | + $users = get_users(['fields' => ['ID', 'display_name']]); | |
| 488 | + $userNames = []; | |
| 489 | + foreach ($users as $key => $value) { | |
| 490 | + $userNames[$value->ID] = $value->display_name; | |
| 491 | + } | |
| 492 | + foreach ($result as $key => $value) { | |
| 493 | + foreach ($formFieldsNames as $formFieldName) { | |
| 494 | + $allData[$key]['entry_id'] = preg_replace('/[\]["]/i', '', $value->$entry_id); | |
| 495 | + if ('__user_id' === $formFieldName && intval($value->$formFieldName) > 0) { | |
| 496 | + $allData[$key][$formFieldName] = $userNames[$value->$formFieldName]; | |
| 497 | + } elseif ('__user_ip' === $formFieldName) { | |
| 498 | + $allData[$key][$formFieldName] = long2ip($value->$formFieldName); | |
| 499 | + } else { | |
| 500 | + $allData[$key][$formFieldName] = preg_replace('/[\]["]/i', '', $value->$formFieldName); | |
| 501 | + } | |
| 275 | 502 | } |
| 276 | - if (empty($entryIDs)) { | |
| 277 | - return array( | |
| 278 | - 'count' => 0, | |
| 279 | - 'entries' => [], | |
| 280 | - ); | |
| 281 | - } | |
| 282 | - $condition['bitforms_form_entry_id'] = $entryIDs; | |
| 283 | - $formattedCondition = $this->getFormatedCondition($condition); | |
| 284 | - $groupedCondition = null; | |
| 285 | - if ($formattedCondition) { | |
| 286 | - $groupedCondition = $formattedCondition['conditions'] . " GROUP BY | |
| 287 | - `bitforms_form_entry_id` "; | |
| 288 | - $all_values = array_merge($all_values, $formattedCondition['values']); | |
| 289 | - } | |
| 290 | - $order = \is_null($sortBy) ? "DESC " : "$sortBy"; | |
| 291 | - $orderField = \is_null($sortByField) ? "bitforms_form_entry_id " : "`$sortByField`"; | |
| 292 | - $orderCondition = "ORDER BY $orderField $order "; | |
| 293 | - if (!\is_null($limit)) { | |
| 294 | - $limitInt = \intval($limit); | |
| 295 | - $limit = " LIMIT $limitInt "; | |
| 296 | - } | |
| 297 | - $sql = "SELECT $selectedEntryMeta FROM `$this->table_name`" | |
| 298 | - . $groupedCondition . $orderCondition . $limit; | |
| 299 | - $result = $this->execute($sql, $all_values)->getResult(); | |
| 300 | - $allData = []; | |
| 301 | - foreach ($result as $key => $value) { | |
| 302 | - foreach ($formFieldsNames as $formFieldName) { | |
| 303 | - $allData[$key][$formFieldName] = preg_replace('/[\]["]/i', '', $value->$formFieldName); | |
| 503 | + } | |
| 504 | + | |
| 505 | + if (is_wp_error($result)) { | |
| 506 | + wp_send_json_error('Internal server error', 500); | |
| 507 | + } else { | |
| 508 | + foreach ($fieldLabels as $field) { | |
| 509 | + foreach ($allData as $index => $entry) { | |
| 510 | + if (array_key_exists($field['key'], $entry) && 'file-up' === $field['type']) { | |
| 511 | + $key = $field['key']; | |
| 512 | + if (empty($entry[$key])) { | |
| 513 | + continue; | |
| 514 | + } | |
| 515 | + $_upload_dir = BITFORMS_UPLOAD_DIR . DIRECTORY_SEPARATOR . $formId . DIRECTORY_SEPARATOR . $entry['entry_id']; | |
| 516 | + if (is_array(explode(',', $entry[$key]))) { | |
| 517 | + $fileData = []; | |
| 518 | + foreach (explode(',', $entry[$key]) as $file) { | |
| 519 | + $uploadedFile = explode('_', $file); | |
| 520 | + $path = "bitforms/bitforms-file/?formID=$formId&entryID=" . $entry['entry_id'] . "&fileID=$uploadedFile[0]"; | |
| 521 | + if (file_exists($_upload_dir . DIRECTORY_SEPARATOR . $uploadedFile[0])) { | |
| 522 | + $fileData[] = site_url($path, null); | |
| 523 | + } | |
| 524 | + } | |
| 525 | + $allData[$index][$key] = implode(',', $fileData); | |
| 526 | + } else { | |
| 527 | + $uploadedFile = explode('_', $entry[$key]); | |
| 528 | + $path = "bitforms/bitforms-file/?formID=$formId&entryID=" . $entry['entry_id'] . '&fileID=' . $uploadedFile[0]; | |
| 529 | + if (file_exists($_upload_dir . DIRECTORY_SEPARATOR . $uploadedFile[0])) { | |
| 530 | + $allData[$index][$key] = site_url($path, null); | |
| 531 | + } | |
| 532 | + } | |
| 304 | 533 | } |
| 534 | + } | |
| 305 | 535 | } |
| 306 | - if (is_wp_error($result)) { | |
| 307 | - wp_send_json_error('Internal server error', 500); | |
| 308 | - } else { | |
| 309 | - wp_send_json_success($allData, 200); | |
| 310 | - } | |
| 536 | + wp_send_json_success($allData, 200); | |
| 537 | + } | |
| 311 | 538 | } |
| 312 | 539 | } |
