| 1 |
<?php |
| 2 |
|
| 3 |
namespace Depicter\Database\Repository; |
| 4 |
|
| 5 |
use Averta\Core\Utility\Arr; |
| 6 |
use Averta\Core\Utility\Data; |
| 7 |
use Depicter\Database\Entity\Lead; |
| 8 |
use Depicter\Database\Entity\LeadField; |
| 9 |
use Depicter\Utility\Sanitize; |
| 10 |
use TypeRocket\Database\SqlRaw; |
| 11 |
use \TypeRocket\Utility\Arr as TypeRocketArr; |
| 12 |
|
| 13 |
class LeadRepository |
| 14 |
{ |
| 15 |
|
| 16 |
/** |
| 17 |
* @var Lead Lead |
| 18 |
*/ |
| 19 |
private Lead $lead; |
| 20 |
|
| 21 |
/** |
| 22 |
* @var LeadField |
| 23 |
*/ |
| 24 |
private LeadField $leadField; |
| 25 |
|
| 26 |
|
| 27 |
public function __construct(){ |
| 28 |
$this->lead(); |
| 29 |
} |
| 30 |
|
| 31 |
/** |
| 32 |
* Access to an instance of Lead entity |
| 33 |
* |
| 34 |
* @return Lead |
| 35 |
*/ |
| 36 |
public function lead(): Lead{ |
| 37 |
try{ |
| 38 |
if( empty( $this->lead ) ){ |
| 39 |
$this->lead = Lead::new(); |
| 40 |
} |
| 41 |
} catch(\Exception $e){} |
| 42 |
|
| 43 |
return $this->lead; |
| 44 |
} |
| 45 |
|
| 46 |
/** |
| 47 |
* Access to an instance of LeadField |
| 48 |
* |
| 49 |
* @return LeadField |
| 50 |
*/ |
| 51 |
public function leadField(): LeadField{ |
| 52 |
try{ |
| 53 |
if( empty( $this->leadField ) ){ |
| 54 |
$this->leadField = LeadField::new(); |
| 55 |
} |
| 56 |
} catch(\Exception $e){} |
| 57 |
|
| 58 |
return $this->leadField; |
| 59 |
} |
| 60 |
|
| 61 |
/** |
| 62 |
* Removes a lead or leads by ID(s) |
| 63 |
* |
| 64 |
* @param mixed $id Can be an ID or list of comma separated IDs |
| 65 |
* |
| 66 |
* @return bool |
| 67 |
* @throws \Exception |
| 68 |
*/ |
| 69 |
public function delete( $id ) |
| 70 |
{ |
| 71 |
$succeed = false; |
| 72 |
|
| 73 |
if( is_array( $id ) ){ |
| 74 |
$ids = $id; |
| 75 |
} elseif( false !== strpos( $id, ',' ) ){ |
| 76 |
$ids = explode(',', $id ); |
| 77 |
} else { |
| 78 |
$ids = [$id]; |
| 79 |
} |
| 80 |
|
| 81 |
foreach( $ids as $id ){ |
| 82 |
$id = Sanitize::int( $id ); |
| 83 |
if( $lead = $this->lead()->findById( $id ) ){ |
| 84 |
$lead->delete(); |
| 85 |
\Depicter::leadFieldRepository()->deleteByLeadId( $id ); |
| 86 |
$succeed = true; |
| 87 |
} |
| 88 |
} |
| 89 |
|
| 90 |
return $succeed; |
| 91 |
} |
| 92 |
|
| 93 |
/** |
| 94 |
* Create a lead record |
| 95 |
* |
| 96 |
* @param int $sourceId Document ID |
| 97 |
* @param int $contentId The ID of form or survey |
| 98 |
* @param int $contentName The Name of form or survey |
| 99 |
* |
| 100 |
* @return mixed |
| 101 |
* @throws \Exception |
| 102 |
*/ |
| 103 |
public function create( $sourceId, $contentId, $contentName = '' ) { |
| 104 |
return $this->lead()->create([ |
| 105 |
'source_id' => $sourceId, |
| 106 |
'content_id' => $contentId, |
| 107 |
'content_name' => $contentName, |
| 108 |
'created_at' => $this->lead()->currentDateTime() |
| 109 |
]); |
| 110 |
} |
| 111 |
|
| 112 |
/** |
| 113 |
* Update a meta by relation, relation ID and meta key |
| 114 |
* |
| 115 |
* @param $id |
| 116 |
* @param array $fields |
| 117 |
* |
| 118 |
* @return mixed |
| 119 |
* @throws \Exception |
| 120 |
*/ |
| 121 |
public function update( $id, array $fields = [] ) { |
| 122 |
if ( empty( $fields ) ) { |
| 123 |
return false; |
| 124 |
} |
| 125 |
|
| 126 |
$lead = $this->lead()->findById( $id ); |
| 127 |
|
| 128 |
if ( $lead && $lead->count() ){ |
| 129 |
return $lead->first()->update($fields); |
| 130 |
} |
| 131 |
|
| 132 |
return false; |
| 133 |
} |
| 134 |
|
| 135 |
/** |
| 136 |
* Get meta value by relation, relation ID and meta key |
| 137 |
* |
| 138 |
* @param $id |
| 139 |
* |
| 140 |
* @return array |
| 141 |
* @throws \Exception |
| 142 |
*/ |
| 143 |
public function get( $id ): array{ |
| 144 |
$lead = Lead::new()->findById($id)->get(); |
| 145 |
|
| 146 |
return $lead ? $lead->first()->toArray() : []; |
| 147 |
} |
| 148 |
|
| 149 |
/** |
| 150 |
* @throws \Exception |
| 151 |
*/ |
| 152 |
public function getResults( $args = [] ){ |
| 153 |
|
| 154 |
// ensure args are valid |
| 155 |
$this->ensureValidArgs( $args ); |
| 156 |
|
| 157 |
// if |
| 158 |
if( ! $args['includeFields'] ){ |
| 159 |
return $this->getLeadsResults( $args ); |
| 160 |
} |
| 161 |
|
| 162 |
return $this->getJointResults( $args ); |
| 163 |
} |
| 164 |
|
| 165 |
/** |
| 166 |
* @throws \Exception |
| 167 |
*/ |
| 168 |
protected function getJointResults( $args ){ |
| 169 |
// apply query args and find lead ids |
| 170 |
$leads = $this->getLeadsResults( $args ); |
| 171 |
|
| 172 |
// skip if no lead found |
| 173 |
if( empty( $leads['leads'] ) ){ |
| 174 |
return []; |
| 175 |
} |
| 176 |
$leads = $leads['leads']; |
| 177 |
// filter found lead ids |
| 178 |
$foundLeadsIds = TypeRocketArr::pluck( $leads, 'id' ); |
| 179 |
$args['ids'] = $foundLeadsIds; |
| 180 |
|
| 181 |
// find all stored field names for above lead ids |
| 182 |
$leadFieldNames = \Depicter::leadFieldRepository()->getFieldNamesByLeadId( $foundLeadsIds ); |
| 183 |
$args['fieldNames'] = $leadFieldNames; |
| 184 |
|
| 185 |
// join fields with found lead records |
| 186 |
return $this->getAppendedFieldsToLeadResults( $args ); |
| 187 |
} |
| 188 |
|
| 189 |
/** |
| 190 |
* Retrieves leads results based on passed filters. |
| 191 |
* Finds field values for a search term and returns corresponding lead results |
| 192 |
* |
| 193 |
* @param array $args Filter options |
| 194 |
* |
| 195 |
* @return array |
| 196 |
* @throws \Exception |
| 197 |
*/ |
| 198 |
protected function getLeadsResults( $args ){ |
| 199 |
// Purpose of joining tables is being able to search in leadField values as well |
| 200 |
$leadTable = $this->lead()->getTable(); |
| 201 |
$leads = Lead::new()->select( |
| 202 |
"{$leadTable}.id", |
| 203 |
"{$leadTable}.source_id", |
| 204 |
"{$leadTable}.content_id", |
| 205 |
"{$leadTable}.content_name", |
| 206 |
"{$leadTable}.created_at", |
| 207 |
"lf.name as fieldName", |
| 208 |
"lf.value as fieldValue" |
| 209 |
)->join( "{$this->leadField()->getTable()} AS lf", "{$leadTable}.id", "=", "lf.lead_id" ); |
| 210 |
|
| 211 |
if( ! empty( $args['dateStart'] ) ){ |
| 212 |
$args['dateStart'] = $this->normalizeDateTime( $args['dateStart'], 'start' ); |
| 213 |
$leads->where( "{$leadTable}.created_at", '>=', $args['dateStart'] ); |
| 214 |
} |
| 215 |
|
| 216 |
if( ! empty( $args['dateEnd'] ) ){ |
| 217 |
$args['dateEnd'] = $this->normalizeDateTime( $args['dateEnd'], 'end' ); |
| 218 |
$leads->where( "{$leadTable}.created_at", '<=', $args['dateEnd'] ); |
| 219 |
} |
| 220 |
|
| 221 |
if( ! empty( $args['sources'] ) ){ |
| 222 |
$leads->where( "{$leadTable}.source_id", 'in', $args['sources'] ); |
| 223 |
} |
| 224 |
|
| 225 |
if( ! empty( $args['s'] ) ){ |
| 226 |
$search = "'%". $args['s'] ."%'"; |
| 227 |
$leads->appendRawWhere('AND', "( lf.value like {$search} OR {$leadTable}.content_name like {$search} )"); |
| 228 |
} |
| 229 |
|
| 230 |
if( ! empty( $args['orderBy'] ) && ! empty( $args['order'] ) ){ |
| 231 |
// Resolve ordering against known columns only, never raw request input. |
| 232 |
list( $orderByColumn, $orderDirection ) = $this->resolveOrder( $args, $leadTable, [ 'fieldName', 'fieldValue' ] ); |
| 233 |
$leads->orderBy( $orderByColumn, $orderDirection ); |
| 234 |
} |
| 235 |
|
| 236 |
$leads = $leads->groupBy("{$leadTable}.id"); |
| 237 |
$results = $this->paginate( $leads, $args ); |
| 238 |
|
| 239 |
return $results['leads'] ? [ |
| 240 |
'numberOfLeads' => $results['numberOfLeads'], |
| 241 |
'numberOfPages' => $results['numberOfPages'], |
| 242 |
'page' => $args['page'], |
| 243 |
'leads' => $results['leads']->toArray() |
| 244 |
] : []; |
| 245 |
} |
| 246 |
|
| 247 |
/** |
| 248 |
* Normalize date to include time |
| 249 |
* |
| 250 |
* @param string $date |
| 251 |
* @param string $type |
| 252 |
* |
| 253 |
* @return string |
| 254 |
*/ |
| 255 |
public function normalizeDateTime( string $date, $type = 'start' ){ |
| 256 |
if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)){ |
| 257 |
return $date . ' ' . ($type === 'start' ? '00:00:00' : '23:59:59'); |
| 258 |
} |
| 259 |
return $date; |
| 260 |
} |
| 261 |
|
| 262 |
|
| 263 |
/** |
| 264 |
* Apply pagination if possible |
| 265 |
* |
| 266 |
* @param Lead $leads |
| 267 |
* @param $args |
| 268 |
* |
| 269 |
* @return array|mixed |
| 270 |
*/ |
| 271 |
protected function paginate( $leads, $args = [] ){ |
| 272 |
$numberOfLeads = $leads ? $leads->count() : 0; |
| 273 |
if ( !empty( $args['perPage'] ) ) { |
| 274 |
$args['page'] = $args['page'] ?? 1; |
| 275 |
|
| 276 |
if ( $pager = $leads->paginate( $args['perPage'], $args['page'] ) ) { |
| 277 |
$leads = $pager->getResults(); |
| 278 |
} else { |
| 279 |
$leads = []; |
| 280 |
} |
| 281 |
} else { |
| 282 |
$leads = $leads ? $leads->findAll()->get() : []; |
| 283 |
} |
| 284 |
|
| 285 |
return [ |
| 286 |
'leads' => $leads, |
| 287 |
'numberOfPages' => !empty( $args['perPage'] ) ? ceil( $numberOfLeads / $args['perPage'] ) : 1, |
| 288 |
'numberOfLeads' => $numberOfLeads, |
| 289 |
]; |
| 290 |
} |
| 291 |
|
| 292 |
/** |
| 293 |
* Queries records of leads with specified fields |
| 294 |
* |
| 295 |
* @param $columns |
| 296 |
* |
| 297 |
* @return Lead |
| 298 |
* @throws \Exception |
| 299 |
*/ |
| 300 |
protected function select( $columns = [] ) { |
| 301 |
$entity = Lead::new(); |
| 302 |
$columns = !empty( $columns ) ? $columns : $entity->getTableColumns(); |
| 303 |
return $entity->select( $columns ); |
| 304 |
} |
| 305 |
|
| 306 |
/** |
| 307 |
* Ensure input arguments are valid |
| 308 |
* |
| 309 |
* @param $args |
| 310 |
*/ |
| 311 |
protected function ensureValidArgs( &$args ){ |
| 312 |
// ensure $args['sources'] exists and is an array |
| 313 |
Arr::ensureItemIsArray( $args, 'sources', [] ); |
| 314 |
|
| 315 |
// ensure $args['columns'] exists and is an array |
| 316 |
Arr::ensureItemIsArray( $args, 'columns', [] ); |
| 317 |
|
| 318 |
// ensure $args['ids'] exists and is an array |
| 319 |
Arr::ensureItemIsArray( $args, 'ids', [] ); |
| 320 |
|
| 321 |
// set defaults |
| 322 |
$args['order'] = $args['order'] ?? 'DESC'; |
| 323 |
$args['orderBy'] = $args['orderBy'] ?? 'id'; |
| 324 |
|
| 325 |
// whether to do a joint query for retrieving lead fields or not |
| 326 |
$args['includeFields'] = Data::isTrue( $args['includeFields'] ?? false ); |
| 327 |
// whether to skip custom fields for just use know form fields |
| 328 |
$args['skipCustomFields'] = Data::isTrue( $args['skipCustomFields'] ?? false ); |
| 329 |
|
| 330 |
// ensure valid column names are set |
| 331 |
if( empty( $args['columns'] ) ) { |
| 332 |
$args['columns'] = $this->lead()->getTableColumns(); |
| 333 |
} else { |
| 334 |
// ensure valid column names |
| 335 |
$args['columns'] = array_intersect( $args['columns'], $this->lead()->getTableColumns() ) ; |
| 336 |
} |
| 337 |
} |
| 338 |
|
| 339 |
/** |
| 340 |
* Builds and runs a joint query |
| 341 |
* |
| 342 |
* @param array $args Filter arguments |
| 343 |
* |
| 344 |
* @return array |
| 345 |
* @throws \Exception |
| 346 |
*/ |
| 347 |
private function getAppendedFieldsToLeadResults( $args ){ |
| 348 |
|
| 349 |
$LeadTableColumns = $args['columns']; |
| 350 |
$leadFieldTableColumns = $args['fieldNames'] ?? []; |
| 351 |
|
| 352 |
// drop custom lead fields from list of query columns |
| 353 |
if( $args['skipCustomFields'] ){ |
| 354 |
$leadFieldTableColumns = array_intersect( $leadFieldTableColumns, LeadFieldRepository::KNOWN_FORM_FIELD_NAMES ); |
| 355 |
} |
| 356 |
$leadFieldTableColumns = array_unique( $leadFieldTableColumns ); |
| 357 |
|
| 358 |
// prefix lead columns for joint query |
| 359 |
$columns = array_map( function( $column ){ |
| 360 |
return "l.{$column}"; |
| 361 |
}, $LeadTableColumns ); |
| 362 |
|
| 363 |
// prefix and add leadField columns for joint query. |
| 364 |
// Field names come from public form submissions, so they are never interpolated |
| 365 |
// into SQL: the compared value is bound and the alias is rebuilt from a safe |
| 366 |
// character set. |
| 367 |
$fieldAliases = []; |
| 368 |
foreach( $leadFieldTableColumns as $leadFieldTableColumn ){ |
| 369 |
$alias = $this->fieldAlias( $leadFieldTableColumn ); |
| 370 |
|
| 371 |
// drop unusable or colliding aliases instead of emitting broken SQL |
| 372 |
if( '' === $alias || isset( $fieldAliases[ $alias ] ) ){ |
| 373 |
continue; |
| 374 |
} |
| 375 |
$fieldAliases[ $alias ] = true; |
| 376 |
|
| 377 |
// using sqlRaw to bypass 'tickSqlName' filter |
| 378 |
$columns[] = new SqlRaw( sprintf( |
| 379 |
"MAX(IF(lf.name = %s, lf.value, NULL)) AS `%s`", |
| 380 |
$this->quote( $leadFieldTableColumn ), |
| 381 |
$alias |
| 382 |
) ); |
| 383 |
} |
| 384 |
|
| 385 |
$leads = Lead::new()->select( $columns ) |
| 386 |
->as( 'l' ) |
| 387 |
->join( "{$this->leadField()->getTable()} AS lf", "l.id", "=", "lf.lead_id" ) |
| 388 |
->where( "l.id", 'IN', $args['ids'] ); |
| 389 |
|
| 390 |
if( ! empty( $args['orderBy'] ) && ! empty( $args['order'] ) ){ |
| 391 |
// Resolve ordering against lead columns and the aliases actually emitted above. |
| 392 |
list( $orderByColumn, $orderDirection ) = $this->resolveOrder( $args, 'l', array_keys( $fieldAliases ) ); |
| 393 |
$leads->orderBy( $orderByColumn, $orderDirection ); |
| 394 |
} |
| 395 |
|
| 396 |
$leads = $leads->groupBy("l.id")->get(); |
| 397 |
|
| 398 |
return $leads ? $leads->toArray() : []; |
| 399 |
} |
| 400 |
|
| 401 |
/** |
| 402 |
* Quotes a value for safe inclusion in a raw SQL fragment. |
| 403 |
* |
| 404 |
* Mirrors TypeRocket\Database\Query::prepareValue(). The placeholder escape is |
| 405 |
* removed because the returned fragment is spliced into SQL that the query builder |
| 406 |
* runs itself, rather than being passed back through $wpdb::prepare(). |
| 407 |
* |
| 408 |
* @param mixed $value |
| 409 |
* |
| 410 |
* @return string |
| 411 |
*/ |
| 412 |
private function quote( $value ): string { |
| 413 |
global $wpdb; |
| 414 |
|
| 415 |
return $wpdb->remove_placeholder_escape( $wpdb->prepare( '%s', (string) $value ) ); |
| 416 |
} |
| 417 |
|
| 418 |
/** |
| 419 |
* Builds a safe column alias out of an untrusted lead field name. |
| 420 |
* |
| 421 |
* Lead field names are supplied by visitors submitting a form, so only a known |
| 422 |
* safe character set is allowed through. An empty result means the name cannot be |
| 423 |
* represented as a column and should be skipped. |
| 424 |
* |
| 425 |
* @param mixed $name |
| 426 |
* |
| 427 |
* @return string |
| 428 |
*/ |
| 429 |
private function fieldAlias( $name ): string { |
| 430 |
$alias = preg_replace( '/[^\p{L}\p{N}_\- ]/u', '', (string) $name ); |
| 431 |
|
| 432 |
// preg_replace() returns null on malformed UTF-8, fall back to an ASCII only pass |
| 433 |
if( null === $alias ){ |
| 434 |
$alias = preg_replace( '/[^A-Za-z0-9_\- ]/', '', (string) $name ); |
| 435 |
} |
| 436 |
|
| 437 |
return trim( (string) $alias ); |
| 438 |
} |
| 439 |
|
| 440 |
/** |
| 441 |
* Resolves order by / order arguments against a list of permitted columns. |
| 442 |
* |
| 443 |
* Anything unrecognised falls back to the lead ID, so request input never reaches |
| 444 |
* the ORDER BY clause verbatim. |
| 445 |
* |
| 446 |
* @param array $args Filter arguments |
| 447 |
* @param string $tableAlias Table name or alias holding the lead columns |
| 448 |
* @param array $extraAllowed Additional select aliases that may be ordered by |
| 449 |
* |
| 450 |
* @return array [ column, direction ] |
| 451 |
*/ |
| 452 |
private function resolveOrder( array $args, string $tableAlias, array $extraAllowed = [] ): array { |
| 453 |
$direction = 'ASC' === strtoupper( (string) ( $args['order'] ?? '' ) ) ? 'ASC' : 'DESC'; |
| 454 |
$orderBy = (string) ( $args['orderBy'] ?? '' ); |
| 455 |
|
| 456 |
if( in_array( $orderBy, $this->lead()->getTableColumns(), true ) ){ |
| 457 |
return [ "{$tableAlias}.{$orderBy}", $direction ]; |
| 458 |
} |
| 459 |
|
| 460 |
// aliases already use a safe character set, but may contain characters that |
| 461 |
// tickSqlName() would strip, so quote them explicitly |
| 462 |
if( in_array( $orderBy, $extraAllowed, true ) ){ |
| 463 |
return [ new SqlRaw( '`' . $orderBy . '`' ), $direction ]; |
| 464 |
} |
| 465 |
|
| 466 |
return [ "{$tableAlias}.id", $direction ]; |
| 467 |
} |
| 468 |
|
| 469 |
} |
| 470 |
|
