1# -- 2# Copyright (C) 2001-2020 OTRS AG, https://otrs.com/ 3# -- 4# This software comes with ABSOLUTELY NO WARRANTY. For details, see 5# the enclosed file COPYING for license information (GPL). If you 6# did not receive this file, see https://www.gnu.org/licenses/gpl-3.0.txt. 7# -- 8 9package Kernel::System::Ticket::TicketSearch; 10 11use strict; 12use warnings; 13 14use Kernel::System::VariableCheck qw(IsArrayRefWithData IsStringWithData); 15 16our $ObjectManagerDisabled = 1; 17 18=head1 NAME 19 20Kernel::System::Ticket::TicketSearch - ticket search lib 21 22=head1 DESCRIPTION 23 24All ticket search functions. 25 26 27=head2 TicketSearch() 28 29To find tickets in your system. 30 31 my @TicketIDs = $TicketObject->TicketSearch( 32 # result (required) 33 Result => 'ARRAY' || 'HASH' || 'COUNT', 34 35 # result limit 36 Limit => 100, 37 38 # Use TicketSearch as a ticket filter on a single ticket, 39 # or a predefined ticket list 40 TicketID => 1234, 41 TicketID => [1234, 1235], 42 43 # ticket number (optional) as STRING or as ARRAYREF 44 TicketNumber => '%123546%', 45 TicketNumber => ['%123546%', '%123666%'], 46 47 # ticket title (optional) as STRING or as ARRAYREF 48 Title => '%SomeText%', 49 Title => ['%SomeTest1%', '%SomeTest2%'], 50 51 Queues => ['system queue', 'other queue'], 52 QueueIDs => [1, 42, 512], 53 54 # use also sub queues of Queue|Queues in search 55 UseSubQueues => 0, 56 57 # You can use types like normal, ... 58 Types => ['normal', 'change', 'incident'], 59 TypeIDs => [3, 4], 60 61 # You can use states like new, open, pending reminder, ... 62 States => ['new', 'open'], 63 StateIDs => [3, 4], 64 65 # (Open|Closed) tickets for all closed or open tickets. 66 StateType => 'Open', 67 68 # You also can use real state types like new, open, closed, 69 # pending reminder, pending auto, removed and merged. 70 StateType => ['open', 'new'], 71 StateTypeIDs => [1, 2, 3], 72 73 Priorities => ['1 very low', '2 low', '3 normal'], 74 PriorityIDs => [1, 2, 3], 75 76 Services => ['Service A', 'Service B'], 77 ServiceIDs => [1, 2, 3], 78 79 SLAs => ['SLA A', 'SLA B'], 80 SLAIDs => [1, 2, 3], 81 82 Locks => ['unlock'], 83 LockIDs => [1, 2, 3], 84 85 OwnerIDs => [1, 12, 455, 32] 86 87 ResponsibleIDs => [1, 12, 455, 32] 88 89 WatchUserIDs => [1, 12, 455, 32] 90 91 # CustomerID (optional) as STRING or as ARRAYREF 92 CustomerID => '123', 93 CustomerID => ['123', 'ABC'], 94 95 # CustomerIDRaw (optional) as STRING or as ARRAYREF 96 # CustomerID without QueryCondition checking 97 #The raw value will be used if is set this parameter 98 CustomerIDRaw => '123 + 345', 99 CustomerIDRaw => ['123', 'ABC','123 && 456','ABC % efg'], 100 101 # CustomerUserLogin (optional) as STRING as ARRAYREF 102 CustomerUserLogin => 'uid123', 103 CustomerUserLogin => ['uid123', 'uid777'], 104 105 # CustomerUserLoginRaw (optional) as STRING as ARRAYREF 106 #The raw value will be used if is set this parameter 107 CustomerUserLoginRaw => 'uid', 108 CustomerUserLoginRaw => 'uid + 123', 109 CustomerUserLoginRaw => ['uid - 123', 'uid # 777 + 321'], 110 111 # create ticket properties (optional) 112 CreatedUserIDs => [1, 12, 455, 32] 113 CreatedTypes => ['normal', 'change', 'incident'], 114 CreatedTypeIDs => [1, 2, 3], 115 CreatedPriorities => ['1 very low', '2 low', '3 normal'], 116 CreatedPriorityIDs => [1, 2, 3], 117 CreatedStates => ['new', 'open'], 118 CreatedStateIDs => [3, 4], 119 CreatedQueues => ['system queue', 'other queue'], 120 CreatedQueueIDs => [1, 42, 512], 121 122 # DynamicFields 123 # At least one operator must be specified. Operators will be connected with AND, 124 # values in an operator with OR. 125 # You can also pass more than one argument to an operator: ['value1', 'value2'] 126 DynamicField_FieldNameX => { 127 Empty => 1, # will return dynamic fields without a value 128 # set to 0 to search fields with a value present 129 Equals => 123, 130 Like => 'value*', # "equals" operator with wildcard support 131 GreaterThan => '2001-01-01 01:01:01', 132 GreaterThanEquals => '2001-01-01 01:01:01', 133 SmallerThan => '2002-02-02 02:02:02', 134 SmallerThanEquals => '2002-02-02 02:02:02', 135 } 136 137 # User ID for searching tickets by ticket flags (defaults to UserID) 138 TicketFlagUserID => 1, 139 140 # search for ticket flags 141 TicketFlag => { 142 Seen => 1, 143 } 144 145 # search for ticket flag that is absent, or a different value than the 146 # one given: 147 NotTicketFlag => { 148 Seen => 1, 149 }, 150 151 # User ID for searching tickets by article flags (defaults to UserID) 152 ArticleFlagUserID => 1, 153 154 155 # search for tickets by the presence of flags on articles 156 ArticleFlag => { 157 Important => 1, 158 }, 159 160 # article stuff (optional) 161 MIMEBase_From => '%spam@example.com%', 162 MIMEBase_To => '%service@example.com%', 163 MIMEBase_Cc => '%client@example.com%', 164 MIMEBase_Subject => '%VIRUS 32%', 165 MIMEBase_Body => '%VIRUS 32%', 166 167 # attachment stuff (optional, applies only for ArticleStorageDB) 168 AttachmentName => '%anyfile.txt%', 169 170 # use full article text index if configured (optional, default off) 171 FullTextIndex => 1, 172 173 # article content search (AND or OR for From, To, Cc, Subject and Body) (optional) 174 ContentSearch => 'AND', 175 176 # article content search prefix (for From, To, Cc, Subject and Body) (optional) 177 ContentSearchPrefix => '*', 178 179 # article content search suffix (for From, To, Cc, Subject and Body) (optional) 180 ContentSearchSuffix => '*', 181 182 # content conditions for From,To,Cc,Subject,Body 183 # Title,CustomerID and CustomerUserLogin (all optional) 184 ConditionInline => 1, 185 186 # articles created more than 60 minutes ago (article older than 60 minutes) (optional) 187 ArticleCreateTimeOlderMinutes => 60, 188 # articles created less than 120 minutes ago (article newer than 60 minutes) (optional) 189 ArticleCreateTimeNewerMinutes => 120, 190 191 # articles with create time after ... (article newer than this date) (optional) 192 ArticleCreateTimeNewerDate => '2006-01-09 00:00:01', 193 # articles with created time before ... (article older than this date) (optional) 194 ArticleCreateTimeOlderDate => '2006-01-19 23:59:59', 195 196 # tickets created more than 60 minutes ago (ticket older than 60 minutes) (optional) 197 TicketCreateTimeOlderMinutes => 60, 198 # tickets created less than 120 minutes ago (ticket newer than 120 minutes) (optional) 199 TicketCreateTimeNewerMinutes => 120, 200 201 # tickets with create time after ... (ticket newer than this date) (optional) 202 TicketCreateTimeNewerDate => '2006-01-09 00:00:01', 203 # tickets with created time before ... (ticket older than this date) (optional) 204 TicketCreateTimeOlderDate => '2006-01-19 23:59:59', 205 206 # ticket history entries that created more than 60 minutes ago (optional) 207 TicketChangeTimeOlderMinutes => 60, 208 # ticket history entries that created less than 120 minutes ago (optional) 209 TicketChangeTimeNewerMinutes => 120, 210 211 # ticket history entry create time after ... (ticket history entries newer than this date) (optional) 212 TicketChangeTimeNewerDate => '2006-01-09 00:00:01', 213 # ticket history entry create time before ... (ticket history entries older than this date) (optional) 214 TicketChangeTimeOlderDate => '2006-01-19 23:59:59', 215 216 # tickets changed more than 60 minutes ago (optional) 217 TicketLastChangeTimeOlderMinutes => 60, 218 # tickets changed less than 120 minutes ago (optional) 219 TicketLastChangeTimeNewerMinutes => 120, 220 221 # tickets with changed time after ... (ticket changed newer than this date) (optional) 222 TicketLastChangeTimeNewerDate => '2006-01-09 00:00:01', 223 # tickets with changed time before ... (ticket changed older than this date) (optional) 224 TicketLastChangeTimeOlderDate => '2006-01-19 23:59:59', 225 226 # tickets closed more than 60 minutes ago (optional) 227 TicketCloseTimeOlderMinutes => 60, 228 # tickets closed less than 120 minutes ago (optional) 229 TicketCloseTimeNewerMinutes => 120, 230 231 # tickets with closed time after ... (ticket closed newer than this date) (optional) 232 TicketCloseTimeNewerDate => '2006-01-09 00:00:01', 233 # tickets with closed time before ... (ticket closed older than this date) (optional) 234 TicketCloseTimeOlderDate => '2006-01-19 23:59:59', 235 236 # tickets with last close time more than 60 minutes ago (optional) 237 TicketLastCloseTimeOlderMinutes => 60, 238 # tickets with last close time less than 120 minutes ago (optional) 239 TicketLastCloseTimeNewerMinutes => 120, 240 241 # tickets with last close time after ... (ticket last close newer than this date) (optional) 242 TicketLastCloseTimeNewerDate => '2006-01-09 00:00:01', 243 # tickets with last close time before ... (ticket last close older than this date) (optional) 244 TicketLastCloseTimeOlderDate => '2006-01-19 23:59:59', 245 246 # tickets with pending time of more than 60 minutes ago (optional) 247 TicketPendingTimeOlderMinutes => 60, 248 # tickets with pending time of less than 120 minutes ago (optional) 249 TicketPendingTimeNewerMinutes => 120, 250 251 # tickets with pending time after ... (optional) 252 TicketPendingTimeNewerDate => '2006-01-09 00:00:01', 253 # tickets with pending time before ... (optional) 254 TicketPendingTimeOlderDate => '2006-01-19 23:59:59', 255 256 # you can use all following escalation options with this four different ways of escalations 257 # TicketEscalationTime... 258 # TicketEscalationUpdateTime... 259 # TicketEscalationResponseTime... 260 # TicketEscalationSolutionTime... 261 262 # ticket escalation time of more than 60 minutes ago (optional) 263 TicketEscalationTimeOlderMinutes => -60, 264 # ticket escalation time of less than 120 minutes ago (optional) 265 TicketEscalationTimeNewerMinutes => -120, 266 267 # tickets with escalation time after ... (optional) 268 TicketEscalationTimeNewerDate => '2006-01-09 00:00:01', 269 # tickets with escalation time before ... (optional) 270 TicketEscalationTimeOlderDate => '2006-01-09 23:59:59', 271 272 # search in archive (optional) 273 # if archiving is on, if not specified the search processes unarchived only 274 # 'y' searches archived tickets, 'n' searches unarchived tickets 275 # if specified together all tickets are searched 276 ArchiveFlags => ['y', 'n'], 277 278 # OrderBy and SortBy (optional) 279 OrderBy => 'Down', # Down|Up 280 SortBy => 'Age', # Created|Owner|Responsible|CustomerID|State|TicketNumber|Queue|Priority|Age|Type|Lock 281 # Changed|Title|Service|SLA|PendingTime|EscalationTime 282 # EscalationUpdateTime|EscalationResponseTime|EscalationSolutionTime 283 # DynamicField_FieldNameX 284 285 # OrderBy and SortBy as ARRAY for sub sorting (optional) 286 OrderBy => ['Down', 'Up'], 287 SortBy => ['Priority', 'Age'], 288 289 # user search (UserID is required) 290 UserID => 123, 291 Permission => 'ro' || 'rw', 292 293 # customer search (CustomerUserID is required) 294 CustomerUserID => 123, 295 Permission => 'ro' || 'rw', 296 297 # CacheTTL, cache search result in seconds (optional) 298 CacheTTL => 60 * 15, 299 ); 300 301Returns: 302 303Result: 'ARRAY' 304 305 @TicketIDs = ( 1, 2, 3 ); 306 307Result: 'HASH' 308 309 %TicketIDs = ( 310 1 => '2010102700001', 311 2 => '2010102700002', 312 3 => '2010102700003', 313 ); 314 315Result: 'COUNT' 316 317 $TicketIDs = 123; 318 319=cut 320 321sub TicketSearch { 322 my ( $Self, %Param ) = @_; 323 324 my $Result = $Param{Result} || 'HASH'; 325 my $OrderBy = $Param{OrderBy} || 'Down'; 326 my $SortBy = $Param{SortBy} || 'Age'; 327 my $Limit = $Param{Limit} || 10000; 328 329 if ( !$Param{ContentSearch} ) { 330 $Param{ContentSearch} = 'AND'; 331 } 332 333 my %SortOptions = ( 334 Owner => 'st.user_id', 335 Responsible => 'st.responsible_user_id', 336 CustomerID => 'st.customer_id', 337 State => 'st.ticket_state_id', 338 Lock => 'st.ticket_lock_id', 339 Ticket => 'st.tn', 340 TicketNumber => 'st.tn', 341 Title => 'st.title', 342 Queue => 'sq.name', 343 Type => 'st.type_id', 344 Priority => 'st.ticket_priority_id', 345 Age => 'st.create_time', 346 Created => 'st.create_time', 347 Changed => 'st.change_time', 348 Service => 'st.service_id', 349 SLA => 'st.sla_id', 350 PendingTime => 'st.until_time', 351 TicketEscalation => 'st.escalation_time', 352 EscalationTime => 'st.escalation_time', 353 EscalationUpdateTime => 'st.escalation_update_time', 354 EscalationResponseTime => 'st.escalation_response_time', 355 EscalationSolutionTime => 'st.escalation_solution_time', 356 ); 357 358 # check required params 359 if ( !$Param{UserID} && !$Param{CustomerUserID} ) { 360 $Kernel::OM->Get('Kernel::System::Log')->Log( 361 Priority => 'error', 362 Message => 'Need UserID or CustomerUserID params for permission check!', 363 ); 364 return; 365 } 366 367 # check types of given arguments 368 ARGUMENT: 369 for my $Key ( 370 qw( 371 Types TypeIDs CreatedTypes CreatedTypeIDs States StateIDs CreatedStates CreatedStateIDs StateTypeIDs 372 Locks LockIDs OwnerIDs ResponsibleIDs CreatedUserIDs Queues QueueIDs CreatedQueues CreatedQueueIDs 373 Priorities PriorityIDs CreatedPriorities CreatedPriorityIDs Services ServiceIDs SLAs SLAIDs WatchUserIDs 374 ) 375 ) 376 { 377 next ARGUMENT if !$Param{$Key}; 378 next ARGUMENT if ref $Param{$Key} eq 'ARRAY' && @{ $Param{$Key} }; 379 380 # log error 381 $Kernel::OM->Get('Kernel::System::Log')->Log( 382 Priority => 'error', 383 Message => "The given param '$Key' is invalid or an empty array reference!", 384 ); 385 return; 386 } 387 388 # get database object 389 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 390 391 # quote id array elements 392 ARGUMENT: 393 for my $Key ( 394 qw( 395 TypeIDs CreatedTypeIDs StateIDs CreatedStateIDs StateTypeIDs LockIDs OwnerIDs ResponsibleIDs CreatedUserIDs 396 QueueIDs CreatedQueueIDs PriorityIDs CreatedPriorityIDs ServiceIDs SLAIDs WatchUserIDs 397 ) 398 ) 399 { 400 next ARGUMENT if !$Param{$Key}; 401 402 # quote elements 403 for my $Element ( @{ $Param{$Key} } ) { 404 if ( !defined $DBObject->Quote( $Element, 'Integer' ) ) { 405 406 # log error 407 $Kernel::OM->Get('Kernel::System::Log')->Log( 408 Priority => 'error', 409 Message => "The given param '$Element' in '$Key' is invalid!", 410 ); 411 return; 412 } 413 } 414 } 415 416 my $TicketDynamicFields = []; 417 my $ArticleDynamicFields = []; 418 my %ValidDynamicFieldParams; 419 my %TicketDynamicFieldName2Config; 420 my %ArticleDynamicFieldName2Config; 421 422 # Only fetch DynamicField data if a field was requested for searching or sorting 423 my $ParamCheckString = ( join '', keys %Param ) || ''; 424 425 if ( ref $Param{SortBy} eq 'ARRAY' ) { 426 $ParamCheckString .= ( join '', @{ $Param{SortBy} } ); 427 } 428 elsif ( ref $Param{SortBy} ne 'HASH' ) { 429 $ParamCheckString .= $Param{SortBy} || ''; 430 } 431 432 if ( $ParamCheckString =~ m/DynamicField_/smx ) { 433 434 # get dynamic field object 435 my $DynamicFieldObject = $Kernel::OM->Get('Kernel::System::DynamicField'); 436 437 # Check all configured ticket dynamic fields 438 $TicketDynamicFields = $DynamicFieldObject->DynamicFieldListGet( 439 ObjectType => 'Ticket', 440 ); 441 442 for my $DynamicField ( @{$TicketDynamicFields} ) { 443 $ValidDynamicFieldParams{ "DynamicField_" . $DynamicField->{Name} } = 1; 444 $TicketDynamicFieldName2Config{ $DynamicField->{Name} } = $DynamicField; 445 } 446 447 # Check all configured article dynamic fields 448 $ArticleDynamicFields = $DynamicFieldObject->DynamicFieldListGet( 449 ObjectType => 'Article', 450 ); 451 452 for my $DynamicField ( @{$ArticleDynamicFields} ) { 453 $ValidDynamicFieldParams{ "DynamicField_" . $DynamicField->{Name} } = 1; 454 $ArticleDynamicFieldName2Config{ $DynamicField->{Name} } = $DynamicField; 455 } 456 } 457 458 # check sort/order by options 459 my @SortByArray = ( ref $SortBy eq 'ARRAY' ? @{$SortBy} : ($SortBy) ); 460 my %LookupSortByArray = map { $_ => 1 } @SortByArray; 461 my @OrderByArray = ( ref $OrderBy eq 'ARRAY' ? @{$OrderBy} : ($OrderBy) ); 462 463 for my $Count ( 0 .. $#SortByArray ) { 464 if ( 465 !$SortOptions{ $SortByArray[$Count] } 466 && !$ValidDynamicFieldParams{ $SortByArray[$Count] } 467 ) 468 { 469 $Kernel::OM->Get('Kernel::System::Log')->Log( 470 Priority => 'error', 471 Message => 'Need valid SortBy (' . $SortByArray[$Count] . ')!', 472 ); 473 return; 474 } 475 if ( $OrderByArray[$Count] ne 'Down' && $OrderByArray[$Count] ne 'Up' ) { 476 $Kernel::OM->Get('Kernel::System::Log')->Log( 477 Priority => 'error', 478 Message => 'Need valid OrderBy (' . $OrderByArray[$Count] . ')!', 479 ); 480 return; 481 } 482 } 483 484 # create sql 485 my $SQLSelect; 486 if ( $Result eq 'COUNT' ) { 487 $SQLSelect = 'SELECT COUNT(DISTINCT(st.id))'; 488 } 489 else { 490 $SQLSelect = 'SELECT DISTINCT st.id, st.tn'; 491 } 492 493 my $SQLFrom = ' FROM ticket st '; 494 495 my $ArticleObject = $Kernel::OM->Get('Kernel::System::Ticket::Article'); 496 497 # check for needed article table join 498 my $ArticleTableJoined = 0; 499 500 # check for needed article search index table join 501 if ( $ArticleObject->ArticleSearchIndexSQLJoinNeeded( SearchParams => \%Param ) ) { 502 $SQLFrom .= ' INNER JOIN article art ON st.id = art.ticket_id '; 503 $SQLFrom .= $ArticleObject->ArticleSearchIndexSQLJoin( SearchParams => \%Param ); 504 $ArticleTableJoined = 1; 505 } 506 507 # Use also history table if required 508 # Create a inner join for each param and register it. 509 my %TicketHistoryJoins = (); 510 ARGUMENT: 511 for my $Key ( sort keys %Param ) { 512 if ( 513 $Param{$Key} 514 && $Key =~ /^(Ticket(Last)?(Close|Change)Time(Newer|Older)(Date|Minutes)|Created.+?)/ 515 ) 516 { 517 my $THRef = $Self->_TicketHistoryReferenceForSearchArgument( 518 Argument => $Key, 519 ); 520 return if !$THRef; 521 522 next ARGUMENT if $TicketHistoryJoins{$THRef}; 523 524 $TicketHistoryJoins{$THRef} = 1; 525 $SQLFrom .= sprintf 526 'INNER JOIN ticket_history %s ON st.id = %s.ticket_id ', 527 $THRef, $THRef; 528 } 529 } 530 531 # add ticket watcher table 532 if ( $Param{WatchUserIDs} ) { 533 $SQLFrom .= 'INNER JOIN ticket_watcher tw ON st.id = tw.ticket_id '; 534 } 535 536 my $SQLExt = ' WHERE 1=1'; 537 538 # Limit the search to just one (or a list) TicketID (used by the GenericAgent 539 # to filter for events on single tickets with the job's ticket filter). 540 if ( IsStringWithData( $Param{TicketID} ) || IsArrayRefWithData( $Param{TicketID} ) ) { 541 542 my $SQLQueryInCondition = $Kernel::OM->Get('Kernel::System::DB')->QueryInCondition( 543 Key => 'st.id', 544 Values => ref $Param{TicketID} eq 'ARRAY' ? $Param{TicketID} : [ $Param{TicketID} ], 545 QuoteType => 'Integer', 546 BindMode => 0, 547 ); 548 $SQLExt .= ' AND ( ' . $SQLQueryInCondition . ' ) '; 549 } 550 551 # add ticket flag table 552 if ( $Param{TicketFlag} ) { 553 my $Index = 1; 554 for my $Key ( sort keys %{ $Param{TicketFlag} } ) { 555 $SQLFrom .= "INNER JOIN ticket_flag tf$Index ON st.id = tf$Index.ticket_id "; 556 $Index++; 557 } 558 } 559 560 # add article and article_flag tables 561 if ( $Param{ArticleFlag} ) { 562 my $Index = 1; 563 for my $Key ( sort keys %{ $Param{ArticleFlag} } ) { 564 $SQLFrom .= "INNER JOIN article ataf$Index ON st.id = ataf$Index.ticket_id "; 565 $SQLFrom .= 566 "INNER JOIN article_flag taf$Index ON ataf$Index.id = taf$Index.article_id "; 567 $Index++; 568 } 569 } 570 571 if ( $Param{NotTicketFlag} ) { 572 my $TicketFlagUserID = $Param{TicketFlagUserID} || $Param{UserID}; 573 return if !defined $TicketFlagUserID; 574 575 my $Index = 1; 576 for my $Key ( sort keys %{ $Param{NotTicketFlag} } ) { 577 $SQLFrom .= "LEFT JOIN ticket_flag ntf$Index ON st.id = ntf$Index.ticket_id " 578 . " AND ntf$Index.ticket_key = '" . $DBObject->Quote($Key) . "'" 579 . " AND ntf$Index.create_by = " 580 . $DBObject->Quote( $TicketFlagUserID, 'Integer' ) 581 . ' '; 582 $Index++; 583 } 584 } 585 586 # current type lookup 587 if ( $Param{Types} ) { 588 589 # get type object 590 my $TypeObject = $Kernel::OM->Get('Kernel::System::Type'); 591 592 for my $Type ( @{ $Param{Types} } ) { 593 594 # lookup type id 595 my $TypeID = $TypeObject->TypeLookup( 596 Type => $Type, 597 ); 598 return if !$TypeID; 599 push @{ $Param{TypeIDs} }, $TypeID; 600 } 601 } 602 603 # type ids 604 if ( $Param{TypeIDs} ) { 605 my $SQLQueryInCondition = $Kernel::OM->Get('Kernel::System::DB')->QueryInCondition( 606 Key => 'st.type_id', 607 Values => $Param{TypeIDs}, 608 QuoteType => 'Integer', 609 BindMode => 0, 610 ); 611 $SQLExt .= ' AND ( ' . $SQLQueryInCondition . ' ) '; 612 } 613 614 # created types lookup 615 if ( $Param{CreatedTypes} ) { 616 617 # get type object 618 my $TypeObject = $Kernel::OM->Get('Kernel::System::Type'); 619 620 for my $Type ( @{ $Param{CreatedTypes} } ) { 621 622 # lookup type id 623 my $TypeID = $TypeObject->TypeLookup( 624 Type => $Type, 625 ); 626 627 return if !$TypeID; 628 629 push @{ $Param{CreatedTypeIDs} }, $TypeID; 630 } 631 } 632 633 # created type ids 634 if ( $Param{CreatedTypeIDs} ) { 635 636 # lookup history type id 637 my $HistoryTypeID = $Self->HistoryTypeLookup( 638 Type => 'NewTicket', 639 ); 640 641 if ($HistoryTypeID) { 642 my $THRef = $Self->_TicketHistoryReferenceForSearchArgument( 643 Argument => 'CreatedTypeIDs', 644 ); 645 return if !$THRef; 646 647 my $SQLQueryInCondition = $Kernel::OM->Get('Kernel::System::DB')->QueryInCondition( 648 Key => "${ THRef }.type_id", 649 Values => $Param{CreatedTypeIDs}, 650 QuoteType => 'Integer', 651 BindMode => 0, 652 ); 653 $SQLExt .= ' AND ( ' . $SQLQueryInCondition . ' ) '; 654 $SQLExt .= " AND ${ THRef }.history_type_id = $HistoryTypeID "; 655 } 656 } 657 658 # current state lookup 659 if ( $Param{States} ) { 660 661 # get state object 662 my $StateObject = $Kernel::OM->Get('Kernel::System::State'); 663 664 for my $State ( @{ $Param{States} } ) { 665 666 # get state data 667 my %StateData = $StateObject->StateGet( 668 Name => $State, 669 ); 670 671 return if !%StateData; 672 673 push @{ $Param{StateIDs} }, $StateData{ID}; 674 } 675 } 676 677 # state ids 678 if ( $Param{StateIDs} ) { 679 my $SQLQueryInCondition = $Kernel::OM->Get('Kernel::System::DB')->QueryInCondition( 680 Key => 'st.ticket_state_id', 681 Values => $Param{StateIDs}, 682 QuoteType => 'Integer', 683 BindMode => 0, 684 ); 685 $SQLExt .= ' AND ( ' . $SQLQueryInCondition . ' ) '; 686 } 687 688 # created states lookup 689 if ( $Param{CreatedStates} ) { 690 691 # get state object 692 my $StateObject = $Kernel::OM->Get('Kernel::System::State'); 693 694 for my $State ( @{ $Param{CreatedStates} } ) { 695 696 # get state data 697 my %StateData = $StateObject->StateGet( 698 Name => $State, 699 ); 700 701 return if !%StateData; 702 703 push @{ $Param{CreatedStateIDs} }, $StateData{ID}; 704 } 705 } 706 707 # created state ids 708 if ( $Param{CreatedStateIDs} ) { 709 710 # lookup history type id 711 my $HistoryTypeID = $Self->HistoryTypeLookup( 712 Type => 'NewTicket', 713 ); 714 715 if ($HistoryTypeID) { 716 my $THRef = $Self->_TicketHistoryReferenceForSearchArgument( 717 Argument => 'CreatedStateIDs', 718 ); 719 return if !$THRef; 720 721 my $SQLQueryInCondition = $Kernel::OM->Get('Kernel::System::DB')->QueryInCondition( 722 Key => "${ THRef }.state_id", 723 Values => $Param{CreatedStateIDs}, 724 QuoteType => 'Integer', 725 BindMode => 0, 726 ); 727 $SQLExt .= ' AND ( ' . $SQLQueryInCondition . ' ) '; 728 729 $SQLExt .= " AND ${ THRef }.history_type_id = $HistoryTypeID "; 730 } 731 } 732 733 # current ticket state type 734 # NOTE: Open and Closed are not valid state types. It's for compat. 735 # Open -> All states which are grouped as open (new, open, pending, ...) 736 # Closed -> All states which are grouped as closed (closed successful, closed unsuccessful) 737 if ( $Param{StateType} && $Param{StateType} eq 'Open' ) { 738 my @ViewableStateIDs = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType( 739 Type => 'Viewable', 740 Result => 'ID', 741 ); 742 $SQLExt .= " AND st.ticket_state_id IN ( ${\(join ', ', sort @ViewableStateIDs)} ) "; 743 } 744 elsif ( $Param{StateType} && $Param{StateType} eq 'Closed' ) { 745 my @ViewableStateIDs = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType( 746 Type => 'Viewable', 747 Result => 'ID', 748 ); 749 $SQLExt .= " AND st.ticket_state_id NOT IN ( ${\(join ', ', sort @ViewableStateIDs)} ) "; 750 } 751 752 # current ticket state type 753 elsif ( $Param{StateType} ) { 754 my @StateIDs = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType( 755 StateType => $Param{StateType}, 756 Result => 'ID', 757 ); 758 return if !$StateIDs[0]; 759 $SQLExt .= " AND st.ticket_state_id IN ( ${\(join ', ', sort {$a <=> $b} @StateIDs)} ) "; 760 } 761 762 if ( $Param{StateTypeIDs} ) { 763 764 # get state object 765 my $StateObject = $Kernel::OM->Get('Kernel::System::State'); 766 767 my %StateTypeList = $StateObject->StateTypeList( 768 UserID => $Param{UserID} || 1, 769 ); 770 my @StateTypes = map { $StateTypeList{$_} } @{ $Param{StateTypeIDs} }; 771 my @StateIDs = $StateObject->StateGetStatesByType( 772 StateType => \@StateTypes, 773 Result => 'ID', 774 ); 775 776 return if !$StateIDs[0]; 777 778 $SQLExt .= " AND st.ticket_state_id IN ( ${\(join ', ', sort {$a <=> $b} @StateIDs)} ) "; 779 } 780 781 # current lock lookup 782 if ( $Param{Locks} ) { 783 784 for my $Lock ( @{ $Param{Locks} } ) { 785 786 # lookup lock id 787 my $LockID = $Kernel::OM->Get('Kernel::System::Lock')->LockLookup( 788 Lock => $Lock, 789 ); 790 791 return if !$LockID; 792 793 push @{ $Param{LockIDs} }, $LockID; 794 } 795 } 796 797 # lock ids 798 if ( $Param{LockIDs} ) { 799 my $SQLQueryInCondition = $Kernel::OM->Get('Kernel::System::DB')->QueryInCondition( 800 Key => 'st.ticket_lock_id', 801 Values => $Param{LockIDs}, 802 QuoteType => 'Integer', 803 BindMode => 0, 804 ); 805 $SQLExt .= ' AND ( ' . $SQLQueryInCondition . ' ) '; 806 } 807 808 # current owner user ids 809 if ( $Param{OwnerIDs} ) { 810 my $SQLQueryInCondition = $Kernel::OM->Get('Kernel::System::DB')->QueryInCondition( 811 Key => 'st.user_id', 812 Values => $Param{OwnerIDs}, 813 QuoteType => 'Integer', 814 BindMode => 0, 815 ); 816 $SQLExt .= ' AND ( ' . $SQLQueryInCondition . ' ) '; 817 } 818 819 # current responsible user ids 820 if ( $Param{ResponsibleIDs} ) { 821 my $SQLQueryInCondition = $Kernel::OM->Get('Kernel::System::DB')->QueryInCondition( 822 Key => 'st.responsible_user_id', 823 Values => $Param{ResponsibleIDs}, 824 QuoteType => 'Integer', 825 BindMode => 0, 826 ); 827 $SQLExt .= ' AND ( ' . $SQLQueryInCondition . ' ) '; 828 } 829 830 # created user ids 831 if ( $Param{CreatedUserIDs} ) { 832 833 # lookup history type id 834 my $HistoryTypeID = $Self->HistoryTypeLookup( 835 Type => 'NewTicket', 836 ); 837 838 if ($HistoryTypeID) { 839 my $THRef = $Self->_TicketHistoryReferenceForSearchArgument( 840 Argument => 'CreatedUserIDs', 841 ); 842 return if !$THRef; 843 844 my $SQLQueryInCondition = $Kernel::OM->Get('Kernel::System::DB')->QueryInCondition( 845 Key => "${ THRef }.create_by", 846 Values => $Param{CreatedUserIDs}, 847 QuoteType => 'Integer', 848 BindMode => 0, 849 ); 850 $SQLExt .= ' AND ( ' . $SQLQueryInCondition . ' ) '; 851 852 $SQLExt .= " AND ${ THRef }.history_type_id = $HistoryTypeID "; 853 } 854 } 855 856 # current queue lookup 857 if ( $Param{Queues} ) { 858 859 # get queue object 860 my $QueueObject = $Kernel::OM->Get('Kernel::System::Queue'); 861 862 for my $Queue ( @{ $Param{Queues} } ) { 863 864 # lookup queue id 865 my $QueueID = $QueueObject->QueueLookup( 866 Queue => $Queue, 867 ); 868 869 return if !$QueueID; 870 871 push @{ $Param{QueueIDs} }, $QueueID; 872 } 873 } 874 875 # current sub queue ids 876 if ( $Param{UseSubQueues} && $Param{QueueIDs} ) { 877 878 # get queue object 879 my $QueueObject = $Kernel::OM->Get('Kernel::System::Queue'); 880 881 my @SubQueueIDs; 882 my %Queues = $QueueObject->GetAllQueues(); 883 884 for my $QueueID ( @{ $Param{QueueIDs} } ) { 885 886 my $Queue = $QueueObject->QueueLookup( QueueID => $QueueID ); 887 888 for my $QueuesID ( sort keys %Queues ) { 889 if ( $Queues{$QueuesID} =~ /^\Q$Queue\E::/i ) { 890 push @SubQueueIDs, $QueuesID; 891 } 892 } 893 } 894 895 push @{ $Param{QueueIDs} }, @SubQueueIDs; 896 } 897 898 # current queue ids 899 if ( $Param{QueueIDs} ) { 900 901 my $SQLQueryInCondition = $Kernel::OM->Get('Kernel::System::DB')->QueryInCondition( 902 Key => 'st.queue_id', 903 Values => $Param{QueueIDs}, 904 QuoteType => 'Integer', 905 BindMode => 0, 906 ); 907 $SQLExt .= ' AND ( ' . $SQLQueryInCondition . ' ) '; 908 } 909 910 # created queue lookup 911 if ( $Param{CreatedQueues} ) { 912 913 # get queue object 914 my $QueueObject = $Kernel::OM->Get('Kernel::System::Queue'); 915 916 for my $Queue ( @{ $Param{CreatedQueues} } ) { 917 918 # lookup queue id 919 my $QueueID = $QueueObject->QueueLookup( 920 Queue => $Queue, 921 ); 922 923 return if !$QueueID; 924 925 push @{ $Param{CreatedQueueIDs} }, $QueueID; 926 } 927 } 928 929 # created queue ids 930 if ( $Param{CreatedQueueIDs} ) { 931 932 # lookup history type id 933 my $HistoryTypeID = $Self->HistoryTypeLookup( 934 Type => 'NewTicket', 935 ); 936 937 if ($HistoryTypeID) { 938 my $THRef = $Self->_TicketHistoryReferenceForSearchArgument( 939 Argument => 'CreatedQueueIDs', 940 ); 941 return if !$THRef; 942 943 my $SQLQueryInCondition = $Kernel::OM->Get('Kernel::System::DB')->QueryInCondition( 944 Key => "${ THRef }.queue_id", 945 Values => $Param{CreatedQueueIDs}, 946 QuoteType => 'Integer', 947 BindMode => 0, 948 ); 949 $SQLExt .= ' AND ( ' . $SQLQueryInCondition . ' ) '; 950 951 $SQLExt .= " AND ${ THRef }.history_type_id = $HistoryTypeID "; 952 } 953 } 954 955 my %GroupList; 956 957 # user groups 958 if ( $Param{UserID} && $Param{UserID} != 1 ) { 959 960 # get users groups 961 %GroupList = $Kernel::OM->Get('Kernel::System::Group')->PermissionUserGet( 962 UserID => $Param{UserID}, 963 Type => $Param{Permission} || 'ro', 964 ); 965 966 # return if we have no permissions 967 return if !%GroupList; 968 969 # add groups to query 970 $SQLExt .= ' AND sq.group_id IN (' . join( ',', sort keys %GroupList ) . ') '; 971 } 972 973 # customer groups 974 if ( $Param{CustomerUserID} ) { 975 976 %GroupList = $Kernel::OM->Get('Kernel::System::CustomerGroup')->GroupMemberList( 977 UserID => $Param{CustomerUserID}, 978 Type => $Param{Permission} || 'ro', 979 Result => 'HASH', 980 ); 981 982 # return if we have no permissions 983 return if !%GroupList; 984 985 # get all customer ids 986 my @CustomerIDs = $Kernel::OM->Get('Kernel::System::CustomerUser')->CustomerIDs( 987 User => $Param{CustomerUserID}, 988 ); 989 990 # prepare combination of customer<->group access 991 992 # add default combination first ( CustomerIDs + CustomerUserID <-> rw access groups ) 993 # this group will always be added (ensures previous behavior) 994 my @CustomerGroupPermission; 995 push @CustomerGroupPermission, { 996 CustomerIDs => \@CustomerIDs, 997 CustomerUserID => $Param{CustomerUserID}, 998 GroupIDs => [ sort keys %GroupList ], 999 }; 1000 1001 # add all combinations based on group access for other CustomerIDs (if available) 1002 # only active if customer group support and extra permission context are enabled 1003 my $CustomerGroupObject = $Kernel::OM->Get('Kernel::System::CustomerGroup'); 1004 my $ExtraPermissionContext = $CustomerGroupObject->GroupContextNameGet( 1005 SysConfigName => '100-CustomerID-other', 1006 ); 1007 if ( $Kernel::OM->Get('Kernel::Config')->Get('CustomerGroupSupport') && $ExtraPermissionContext ) { 1008 1009 # add lookup for CustomerID 1010 my %CustomerIDsLookup = map { $_ => $_ } @CustomerIDs; 1011 1012 # for all CustomerIDs get groups with access to other CustomerIDs 1013 my %ExtraPermissionGroups; 1014 CUSTOMERID: 1015 for my $CustomerID (@CustomerIDs) { 1016 my %CustomerIDExtraPermissionGroups = $CustomerGroupObject->GroupCustomerList( 1017 CustomerID => $CustomerID, 1018 Type => $Param{Permission} || 'ro', 1019 Context => $ExtraPermissionContext, 1020 Result => 'HASH', 1021 ); 1022 next CUSTOMERID if !%CustomerIDExtraPermissionGroups; 1023 1024 # add to groups 1025 %ExtraPermissionGroups = ( 1026 %ExtraPermissionGroups, 1027 %CustomerIDExtraPermissionGroups, 1028 ); 1029 } 1030 1031 # add all unique accessible Group<->Customer combinations to query 1032 # for performance reasons all groups corresponsing with a unique customer id combination 1033 # will be combined into one part 1034 my %CustomerIDCombinations; 1035 GROUPID: 1036 for my $GroupID ( sort keys %ExtraPermissionGroups ) { 1037 my @ExtraCustomerIDs = $CustomerGroupObject->GroupCustomerList( 1038 GroupID => $GroupID, 1039 Type => $Param{Permission} || 'ro', 1040 Result => 'ID', 1041 ); 1042 next GROUPID if !@ExtraCustomerIDs; 1043 1044 # exclude own CustomerIDs for performance reasons 1045 my @MergedCustomerIDs = grep { !$CustomerIDsLookup{$_} } @ExtraCustomerIDs; 1046 next GROUPID if !@MergedCustomerIDs; 1047 1048 # remember combination 1049 my $CustomerIDString = join ',', sort @MergedCustomerIDs; 1050 if ( !$CustomerIDCombinations{$CustomerIDString} ) { 1051 $CustomerIDCombinations{$CustomerIDString} = { 1052 CustomerIDs => \@MergedCustomerIDs, 1053 }; 1054 } 1055 push @{ $CustomerIDCombinations{$CustomerIDString}->{GroupIDs} }, $GroupID; 1056 } 1057 1058 # add to query combinations 1059 push @CustomerGroupPermission, sort values %CustomerIDCombinations; 1060 } 1061 1062 # prepare LOWER call depending on database 1063 my $Lower = ''; 1064 if ( $DBObject->GetDatabaseFunction('CaseSensitive') ) { 1065 $Lower = 'LOWER'; 1066 } 1067 1068 # now add all combinations to query: 1069 # this will compile a search restriction based on customer_id/customer_user_id and group 1070 # and will match if any of the permission combination is met 1071 # a permission combination could be: 1072 # ( <CustomerUserID> OR <CUSTOMERID1> ) AND ( <GROUPID1> ) 1073 # or 1074 # ( <CustomerID1> OR <CUSTOMERID2> OR <CUSTOMERID3> ) AND ( <GROUPID1> OR <GROUPID2> ) 1075 $SQLExt .= ' AND ('; 1076 my $CustomerGroupSQL = ''; 1077 ENTRY: 1078 for my $Entry (@CustomerGroupPermission) { 1079 $CustomerGroupSQL .= $CustomerGroupSQL ? ' OR (' : '('; 1080 1081 my $CustomerIDsSQL; 1082 if ( IsArrayRefWithData( $Entry->{CustomerIDs} ) ) { 1083 $CustomerIDsSQL = 1084 $Lower . '(st.customer_id) IN (' 1085 . join( 1086 ',', 1087 map { 1088 "$Lower('" . $DBObject->Quote($_) . "')" 1089 } @{ 1090 $Entry->{CustomerIDs} 1091 } 1092 ) 1093 . ')'; 1094 } 1095 1096 my $CustomerUserIDSQL; 1097 if ( $Entry->{CustomerUserID} ) { 1098 $CustomerUserIDSQL = 'st.customer_user_id = ' . "'" . $DBObject->Quote( $Param{CustomerUserID} ) . "'"; 1099 } 1100 1101 if ( $CustomerIDsSQL && $CustomerUserIDSQL ) { 1102 $CustomerGroupSQL .= '( ' . $CustomerIDsSQL . ' OR ' . $CustomerUserIDSQL . ' )'; 1103 } 1104 elsif ($CustomerIDsSQL) { 1105 $CustomerGroupSQL .= $CustomerIDsSQL; 1106 } 1107 elsif ($CustomerUserIDSQL) { 1108 $CustomerGroupSQL .= $CustomerUserIDSQL; 1109 } 1110 else { 1111 next ENTRY; 1112 } 1113 1114 $CustomerGroupSQL .= ' AND sq.group_id IN (' . join( ',', @{ $Entry->{GroupIDs} } ) . ') )'; 1115 } 1116 $SQLExt .= $CustomerGroupSQL . ') '; 1117 } 1118 1119 # current priority lookup 1120 if ( $Param{Priorities} ) { 1121 1122 # get priority object 1123 my $PriorityObject = $Kernel::OM->Get('Kernel::System::Priority'); 1124 1125 for my $Priority ( @{ $Param{Priorities} } ) { 1126 1127 # lookup priority id 1128 my $PriorityID = $PriorityObject->PriorityLookup( 1129 Priority => $Priority, 1130 ); 1131 1132 return if !$PriorityID; 1133 1134 push @{ $Param{PriorityIDs} }, $PriorityID; 1135 } 1136 } 1137 1138 # priority ids 1139 if ( $Param{PriorityIDs} ) { 1140 1141 my $SQLQueryInCondition = $Kernel::OM->Get('Kernel::System::DB')->QueryInCondition( 1142 Key => 'st.ticket_priority_id', 1143 Values => $Param{PriorityIDs}, 1144 QuoteType => 'Integer', 1145 BindMode => 0, 1146 ); 1147 $SQLExt .= ' AND ( ' . $SQLQueryInCondition . ' ) '; 1148 } 1149 1150 # created priority lookup 1151 if ( $Param{CreatedPriorities} ) { 1152 1153 # get priority object 1154 my $PriorityObject = $Kernel::OM->Get('Kernel::System::Priority'); 1155 1156 for my $Priority ( @{ $Param{CreatedPriorities} } ) { 1157 1158 # lookup priority id 1159 my $PriorityID = $PriorityObject->PriorityLookup( 1160 Priority => $Priority, 1161 ); 1162 1163 return if !$PriorityID; 1164 1165 push @{ $Param{CreatedPriorityIDs} }, $PriorityID; 1166 } 1167 } 1168 1169 # created priority ids 1170 if ( $Param{CreatedPriorityIDs} ) { 1171 1172 # lookup history type id 1173 my $HistoryTypeID = $Self->HistoryTypeLookup( 1174 Type => 'NewTicket', 1175 ); 1176 1177 if ($HistoryTypeID) { 1178 my $THRef = $Self->_TicketHistoryReferenceForSearchArgument( 1179 Argument => 'CreatedPriorityIDs', 1180 ); 1181 return if !$THRef; 1182 1183 my $SQLQueryInCondition = $Kernel::OM->Get('Kernel::System::DB')->QueryInCondition( 1184 Key => "${ THRef }.priority_id", 1185 Values => $Param{CreatedPriorityIDs}, 1186 QuoteType => 'Integer', 1187 BindMode => 0, 1188 ); 1189 $SQLExt .= ' AND ( ' . $SQLQueryInCondition . ' ) '; 1190 1191 $SQLExt .= " AND ${ THRef }.history_type_id = $HistoryTypeID "; 1192 } 1193 } 1194 1195 # current service lookup 1196 if ( $Param{Services} ) { 1197 1198 # get service object 1199 my $ServiceObject = $Kernel::OM->Get('Kernel::System::Service'); 1200 1201 for my $Service ( @{ $Param{Services} } ) { 1202 1203 # lookup service id 1204 my $ServiceID = $ServiceObject->ServiceLookup( 1205 Name => $Service, 1206 ); 1207 1208 return if !$ServiceID; 1209 1210 push @{ $Param{ServiceIDs} }, $ServiceID; 1211 } 1212 } 1213 1214 # service ids 1215 if ( $Param{ServiceIDs} ) { 1216 my $SQLQueryInCondition = $Kernel::OM->Get('Kernel::System::DB')->QueryInCondition( 1217 Key => 'st.service_id', 1218 Values => $Param{ServiceIDs}, 1219 QuoteType => 'Integer', 1220 BindMode => 0, 1221 ); 1222 $SQLExt .= ' AND ( ' . $SQLQueryInCondition . ' ) '; 1223 } 1224 1225 # current sla lookup 1226 if ( $Param{SLAs} ) { 1227 1228 # get sla object 1229 my $SLAObject = $Kernel::OM->Get('Kernel::System::SLA'); 1230 1231 for my $SLA ( @{ $Param{SLAs} } ) { 1232 1233 # lookup sla id 1234 my $SLAID = $SLAObject->SLALookup( 1235 Name => $SLA, 1236 ); 1237 1238 return if !$SLAID; 1239 1240 push @{ $Param{SLAIDs} }, $SLAID; 1241 } 1242 } 1243 1244 # sla ids 1245 if ( $Param{SLAIDs} ) { 1246 my $SQLQueryInCondition = $Kernel::OM->Get('Kernel::System::DB')->QueryInCondition( 1247 Key => 'st.sla_id', 1248 Values => $Param{SLAIDs}, 1249 QuoteType => 'Integer', 1250 BindMode => 0, 1251 ); 1252 $SQLExt .= ' AND ( ' . $SQLQueryInCondition . ' ) '; 1253 } 1254 1255 # watch user ids 1256 if ( $Param{WatchUserIDs} ) { 1257 my $SQLQueryInCondition = $Kernel::OM->Get('Kernel::System::DB')->QueryInCondition( 1258 Key => 'tw.user_id', 1259 Values => $Param{WatchUserIDs}, 1260 QuoteType => 'Integer', 1261 BindMode => 0, 1262 ); 1263 $SQLExt .= ' AND ( ' . $SQLQueryInCondition . ' ) '; 1264 } 1265 1266 # add ticket flag extension 1267 if ( $Param{TicketFlag} ) { 1268 1269 my $TicketFlagUserID = $Param{TicketFlagUserID} || $Param{UserID}; 1270 return if !defined $TicketFlagUserID; 1271 1272 my $Index = 1; 1273 for my $Key ( sort keys %{ $Param{TicketFlag} } ) { 1274 my $Value = $Param{TicketFlag}->{$Key}; 1275 return if !defined $Value; 1276 1277 $SQLExt .= " AND tf$Index.ticket_key = '" . $DBObject->Quote($Key) . "'"; 1278 $SQLExt .= " AND tf$Index.ticket_value = '" . $DBObject->Quote($Value) . "'"; 1279 $SQLExt .= " AND tf$Index.create_by = " 1280 . $DBObject->Quote( $TicketFlagUserID, 'Integer' ); 1281 1282 $Index++; 1283 } 1284 } 1285 1286 # add article flag extension 1287 if ( $Param{ArticleFlag} ) { 1288 my $ArticleFlagUserID = $Param{ArticleFlagUserID} || $Param{UserID}; 1289 return if !defined $ArticleFlagUserID; 1290 1291 my $Index = 1; 1292 for my $Key ( sort keys %{ $Param{ArticleFlag} } ) { 1293 my $Value = $Param{ArticleFlag}->{$Key}; 1294 return if !defined $Value; 1295 1296 $SQLExt .= " AND taf$Index.article_key = '" . $DBObject->Quote($Key) . "'"; 1297 $SQLExt .= " AND taf$Index.article_value = '" . $DBObject->Quote($Value) . "'"; 1298 $SQLExt .= " AND taf$Index.create_by = " 1299 . $DBObject->Quote( $ArticleFlagUserID, 'Integer' ); 1300 1301 $Index++; 1302 } 1303 } 1304 1305 if ( $Param{NotTicketFlag} ) { 1306 my $Index = 1; 1307 for my $Key ( sort keys %{ $Param{NotTicketFlag} } ) { 1308 my $Value = $Param{NotTicketFlag}->{$Key}; 1309 return if !defined $Value; 1310 1311 $SQLExt .= " AND (ntf$Index.ticket_value IS NULL " 1312 . "OR ntf$Index.ticket_value <> '" . $DBObject->Quote($Value) . "')"; 1313 1314 $Index++; 1315 } 1316 } 1317 1318 # other ticket stuff 1319 my %FieldSQLMap = ( 1320 TicketNumber => 'st.tn', 1321 Title => 'st.title', 1322 CustomerID => 'st.customer_id', 1323 CustomerIDRaw => 'st.customer_id', 1324 CustomerUserLogin => 'st.customer_user_id', 1325 CustomerUserLoginRaw => 'st.customer_user_id', 1326 ); 1327 1328 ATTRIBUTE: 1329 for my $Key ( sort keys %FieldSQLMap ) { 1330 1331 next ATTRIBUTE if !defined $Param{$Key}; 1332 1333 next ATTRIBUTE if ( ( $Key eq 'CustomerID' ) && ( defined $Param{CustomerIDRaw} ) ); 1334 next ATTRIBUTE 1335 if ( ( $Key eq 'CustomerUserLogin' ) && ( defined $Param{CustomerUserLoginRaw} ) ); 1336 1337 # if it's no ref, put it to array ref 1338 if ( ref $Param{$Key} eq '' ) { 1339 $Param{$Key} = [ $Param{$Key} ]; 1340 } 1341 1342 # proccess array ref 1343 my $Used = 0; 1344 1345 VALUE: 1346 for my $Value ( @{ $Param{$Key} } ) { 1347 1348 next VALUE if !defined $Value || !length $Value; 1349 1350 # replace wild card search 1351 if ( 1352 $Key ne 'CustomerIDRaw' 1353 && $Key ne 'CustomerUserLoginRaw' 1354 ) 1355 { 1356 $Value =~ s/\*/%/gi; 1357 } 1358 1359 # check search attribute, we do not need to search for * 1360 next VALUE if $Value =~ /^\%{1,3}$/; 1361 1362 if ( !$Used ) { 1363 $SQLExt .= ' AND ('; 1364 $Used = 1; 1365 } 1366 else { 1367 $SQLExt .= ' OR '; 1368 } 1369 1370 # add * to prefix/suffix on title search 1371 my %ConditionFocus; 1372 if ( $Param{ConditionInline} && $Key eq 'Title' ) { 1373 $ConditionFocus{Extended} = 1; 1374 if ( $Param{ContentSearchPrefix} ) { 1375 $ConditionFocus{SearchPrefix} = $Param{ContentSearchPrefix}; 1376 } 1377 if ( $Param{ContentSearchSuffix} ) { 1378 $ConditionFocus{SearchSuffix} = $Param{ContentSearchSuffix}; 1379 } 1380 } 1381 1382 if ( $Key eq 'CustomerIDRaw' || $Key eq 'CustomerUserLoginRaw' ) { 1383 $SQLExt .= " $FieldSQLMap{$Key}= '" . $DBObject->Quote($Value) . "'"; 1384 } 1385 else { 1386 1387 # use search condition extension 1388 $SQLExt .= $DBObject->QueryCondition( 1389 Key => $FieldSQLMap{$Key}, 1390 Value => $Value, 1391 %ConditionFocus, 1392 ); 1393 } 1394 } 1395 if ($Used) { 1396 $SQLExt .= ')'; 1397 } 1398 } 1399 1400 # Search article attributes. 1401 if ($ArticleTableJoined) { 1402 1403 $SQLExt .= $ArticleObject->ArticleSearchIndexWhereCondition( SearchParams => \%Param ); 1404 1405 # Restrict search from customers to only customer articles. 1406 if ( $Param{CustomerUserID} ) { 1407 $SQLExt .= ' AND art.is_visible_for_customer = 1 '; 1408 } 1409 } 1410 1411 # Remember already joined tables for sorting. 1412 my %DynamicFieldJoinTables; 1413 my $DynamicFieldJoinCounter = 1; 1414 1415 # get dynamic field backend object 1416 my $DynamicFieldBackendObject = $Kernel::OM->Get('Kernel::System::DynamicField::Backend'); 1417 1418 DYNAMIC_FIELD: 1419 for my $DynamicField ( @{$TicketDynamicFields}, @{$ArticleDynamicFields} ) { 1420 my $SearchParam = delete $Param{ "DynamicField_" . $DynamicField->{Name} }; 1421 1422 next DYNAMIC_FIELD if ( !$SearchParam ); 1423 next DYNAMIC_FIELD if ( ref $SearchParam ne 'HASH' ); 1424 1425 my $NeedJoin; 1426 my $QueryForEmptyValues = 0; 1427 1428 for my $Operator ( sort keys %{$SearchParam} ) { 1429 1430 my @SearchParams = ( ref $SearchParam->{$Operator} eq 'ARRAY' ) 1431 ? @{ $SearchParam->{$Operator} } 1432 : ( $SearchParam->{$Operator} ); 1433 1434 my $SQLExtSub = ' AND ('; 1435 my $Counter = 0; 1436 TEXT: 1437 for my $Text (@SearchParams) { 1438 next TEXT if ( !defined $Text || $Text eq '' ); 1439 1440 $Text =~ s/\*/%/gi; 1441 1442 # check search attribute, we do not need to search for * 1443 next TEXT if $Text =~ /^\%{1,3}$/; 1444 1445 # skip validation for empty values 1446 if ( $Operator ne 'Empty' ) { 1447 1448 # validate data type 1449 my $ValidateSuccess = $DynamicFieldBackendObject->ValueValidate( 1450 DynamicFieldConfig => $DynamicField, 1451 Value => $Text, 1452 NoValidateRegex => 1, 1453 UserID => $Param{UserID} || 1, 1454 ); 1455 if ( !$ValidateSuccess ) { 1456 $Kernel::OM->Get('Kernel::System::Log')->Log( 1457 Priority => 'error', 1458 Message => 1459 "Search not executed due to invalid value '" 1460 . $Text 1461 . "' on field '" 1462 . $DynamicField->{Name} 1463 . "'!", 1464 ); 1465 return; 1466 } 1467 } 1468 1469 if ($Counter) { 1470 $SQLExtSub .= ' OR '; 1471 } 1472 1473 # Empty => 1 requires a LEFT JOIN. 1474 if ( $Operator eq 'Empty' && $Text ) { 1475 $SQLExtSub .= $DynamicFieldBackendObject->SearchSQLGet( 1476 DynamicFieldConfig => $DynamicField, 1477 TableAlias => "dfvEmpty$DynamicFieldJoinCounter", 1478 Operator => $Operator, 1479 SearchTerm => $Text, 1480 ); 1481 $QueryForEmptyValues = 1; 1482 } 1483 else { 1484 $SQLExtSub .= $DynamicFieldBackendObject->SearchSQLGet( 1485 DynamicFieldConfig => $DynamicField, 1486 TableAlias => "dfv$DynamicFieldJoinCounter", 1487 Operator => $Operator, 1488 SearchTerm => $Text, 1489 ); 1490 } 1491 1492 $Counter++; 1493 } 1494 $SQLExtSub .= ')'; 1495 if ($Counter) { 1496 $SQLExt .= $SQLExtSub; 1497 $NeedJoin = 1; 1498 } 1499 } 1500 1501 if ($NeedJoin) { 1502 1503 # Join the table for this dynamic field 1504 if ( $DynamicField->{ObjectType} eq 'Ticket' ) { 1505 1506 if ($QueryForEmptyValues) { 1507 1508 # Use LEFT JOIN to allow for null values. 1509 $SQLFrom .= "LEFT JOIN dynamic_field_value dfvEmpty$DynamicFieldJoinCounter 1510 ON (st.id = dfvEmpty$DynamicFieldJoinCounter.object_id 1511 AND dfvEmpty$DynamicFieldJoinCounter.field_id = " . 1512 $DBObject->Quote( $DynamicField->{ID}, 'Integer' ) . ") "; 1513 } 1514 else { 1515 $SQLFrom .= "INNER JOIN dynamic_field_value dfv$DynamicFieldJoinCounter 1516 ON (st.id = dfv$DynamicFieldJoinCounter.object_id 1517 AND dfv$DynamicFieldJoinCounter.field_id = " . 1518 $DBObject->Quote( $DynamicField->{ID}, 'Integer' ) . ") "; 1519 } 1520 } 1521 elsif ( $DynamicField->{ObjectType} eq 'Article' ) { 1522 1523 if ( !$ArticleTableJoined ) { 1524 $SQLFrom .= ' INNER JOIN article art ON st.id = art.ticket_id '; 1525 $ArticleTableJoined = 1; 1526 } 1527 1528 if ($QueryForEmptyValues) { 1529 1530 # Use LEFT JOIN to allow for null values. 1531 $SQLFrom .= "LEFT JOIN dynamic_field_value dfvEmpty$DynamicFieldJoinCounter 1532 ON (art.id = dfvEmpty$DynamicFieldJoinCounter.object_id 1533 AND dfvEmpty$DynamicFieldJoinCounter.field_id = " . 1534 $DBObject->Quote( $DynamicField->{ID}, 'Integer' ) . ") "; 1535 } 1536 else { 1537 $SQLFrom .= "INNER JOIN dynamic_field_value dfv$DynamicFieldJoinCounter 1538 ON (art.id = dfv$DynamicFieldJoinCounter.object_id 1539 AND dfv$DynamicFieldJoinCounter.field_id = " . 1540 $DBObject->Quote( $DynamicField->{ID}, 'Integer' ) . ") "; 1541 } 1542 1543 } 1544 1545 $DynamicFieldJoinTables{ $DynamicField->{Name} } = "dfv$DynamicFieldJoinCounter"; 1546 1547 $DynamicFieldJoinCounter++; 1548 } 1549 } 1550 1551 # catch searches for non-existing dynamic fields 1552 PARAMS: 1553 for my $Key ( sort keys %Param ) { 1554 1555 # Only look at fields which start with DynamicField_ and contain a substructure that is meant for searching. 1556 # It could happen that similar scalar parameters are sent to this method, that should be ignored 1557 # (see bug#13412). 1558 next PARAMS if !ref $Param{$Key}; 1559 next PARAMS if $Key !~ /^DynamicField_(.*)$/; 1560 1561 my $DynamicFieldName = $1; 1562 $Kernel::OM->Get('Kernel::System::Log')->Log( 1563 Priority => 'Error', 1564 Message => qq[No such dynamic field "$DynamicFieldName" (or it is inactive)], 1565 ); 1566 1567 return; 1568 } 1569 1570 # get time object 1571 # remember current time to prevent searches for future timestamps 1572 my $DateTimeObject = $Kernel::OM->Create('Kernel::System::DateTime'); 1573 1574 # get articles created older/newer than x minutes or older/newer than a date 1575 my %ArticleTime = ( 1576 ArticleCreateTime => "art.create_time", 1577 ); 1578 for my $Key ( sort keys %ArticleTime ) { 1579 1580 # get articles created older than x minutes 1581 if ( defined $Param{ $Key . 'OlderMinutes' } ) { 1582 1583 $Param{ $Key . 'OlderMinutes' } ||= 0; 1584 1585 my $Time = $Kernel::OM->Create('Kernel::System::DateTime'); 1586 $Time->Subtract( Minutes => $Param{ $Key . 'OlderMinutes' } ); 1587 1588 $SQLExt .= sprintf( " AND ( %s <= '%s' )", $ArticleTime{$Key}, $Time->ToString() ); 1589 } 1590 1591 # get articles created newer than x minutes 1592 if ( defined $Param{ $Key . 'NewerMinutes' } ) { 1593 1594 $Param{ $Key . 'NewerMinutes' } ||= 0; 1595 1596 my $Time = $Kernel::OM->Create('Kernel::System::DateTime'); 1597 $Time->Subtract( Minutes => $Param{ $Key . 'NewerMinutes' } ); 1598 1599 $SQLExt .= sprintf( " AND ( %s >= '%s' )", $ArticleTime{$Key}, $Time->ToString() ); 1600 } 1601 1602 # get articles created older than xxxx-xx-xx xx:xx date 1603 my $CompareOlderNewerDate; 1604 if ( $Param{ $Key . 'OlderDate' } ) { 1605 if ( 1606 $Param{ $Key . 'OlderDate' } 1607 !~ /(\d\d\d\d)-(\d\d|\d)-(\d\d|\d) (\d\d|\d):(\d\d|\d):(\d\d|\d)/ 1608 ) 1609 { 1610 $Kernel::OM->Get('Kernel::System::Log')->Log( 1611 Priority => 'error', 1612 Message => "Invalid time format '" . $Param{ $Key . 'OlderDate' } . "'!", 1613 ); 1614 return; 1615 } 1616 1617 my $SystemTime = $Kernel::OM->Create( 1618 'Kernel::System::DateTime', 1619 ObjectParams => { 1620 Year => $1, 1621 Month => $2, 1622 Day => $3, 1623 Hour => $4, 1624 Minute => $5, 1625 Second => $6, 1626 } 1627 ); 1628 1629 if ( !$SystemTime ) { 1630 $Kernel::OM->Get('Kernel::System::Log')->Log( 1631 Priority => 'error', 1632 Message => 1633 "Search not executed due to invalid time '" 1634 . $Param{ $Key . 'OlderDate' } . "'!", 1635 ); 1636 return; 1637 } 1638 $CompareOlderNewerDate = $SystemTime; 1639 1640 $SQLExt .= " AND ($ArticleTime{$Key} <= '" . $Param{ $Key . 'OlderDate' } . "')"; 1641 1642 } 1643 1644 # get articles created newer than xxxx-xx-xx xx:xx date 1645 if ( $Param{ $Key . 'NewerDate' } ) { 1646 if ( 1647 $Param{ $Key . 'NewerDate' } 1648 !~ /(\d\d\d\d)-(\d\d|\d)-(\d\d|\d) (\d\d|\d):(\d\d|\d):(\d\d|\d)/ 1649 ) 1650 { 1651 $Kernel::OM->Get('Kernel::System::Log')->Log( 1652 Priority => 'error', 1653 Message => "Invalid time format '" . $Param{ $Key . 'NewerDate' } . "'!", 1654 ); 1655 return; 1656 } 1657 1658 # convert param date to system time 1659 my $SystemTime = $Kernel::OM->Create( 1660 'Kernel::System::DateTime', 1661 ObjectParams => { 1662 1663 Year => $1, 1664 Month => $2, 1665 Day => $3, 1666 Hour => $4, 1667 Minute => $5, 1668 Second => $6, 1669 } 1670 ); 1671 if ( !$SystemTime ) { 1672 $Kernel::OM->Get('Kernel::System::Log')->Log( 1673 Priority => 'error', 1674 Message => 1675 "Search not executed due to invalid time '" 1676 . $Param{ $Key . 'NewerDate' } . "'!", 1677 ); 1678 return; 1679 } 1680 1681 # don't execute queries if newer date is after current date 1682 return if $SystemTime > $DateTimeObject; 1683 1684 # don't execute queries if older/newer date restriction show now valid timeframe 1685 return if $CompareOlderNewerDate && $SystemTime > $CompareOlderNewerDate; 1686 1687 $SQLExt .= " AND ($ArticleTime{$Key} >= '" . $Param{ $Key . 'NewerDate' } . "')"; 1688 } 1689 } 1690 1691 # get tickets created/escalated older/newer than x minutes 1692 my %TicketTime = ( 1693 TicketCreateTime => 'st.create_time', 1694 TicketEscalationTime => 'st.escalation_time', 1695 TicketEscalationUpdateTime => 'st.escalation_update_time', 1696 TicketEscalationResponseTime => 'st.escalation_response_time', 1697 TicketEscalationSolutionTime => 'st.escalation_solution_time', 1698 ); 1699 for my $Key ( sort keys %TicketTime ) { 1700 1701 # get tickets created or escalated older than x minutes 1702 if ( defined $Param{ $Key . 'OlderMinutes' } ) { 1703 1704 $Param{ $Key . 'OlderMinutes' } ||= 0; 1705 1706 # exclude tickets with no escalation 1707 if ( $Key =~ m{ \A TicketEscalation }xms ) { 1708 $SQLExt .= " AND $TicketTime{$Key} != 0"; 1709 } 1710 1711 my $Time = $DateTimeObject->Clone(); 1712 $Time->Subtract( Minutes => $Param{ $Key . 'OlderMinutes' } ); 1713 1714 my $TargetTime = $Key eq 'TicketCreateTime' ? $Time->ToString() : $Time->ToEpoch(); 1715 1716 $SQLExt .= sprintf( " AND ( %s <= '%s' )", $TicketTime{$Key}, $TargetTime ); 1717 } 1718 1719 # get tickets created or escalated newer than x minutes 1720 if ( defined $Param{ $Key . 'NewerMinutes' } ) { 1721 1722 $Param{ $Key . 'NewerMinutes' } ||= 0; 1723 1724 # exclude tickets with no escalation 1725 if ( $Key =~ m{ \A TicketEscalation }xms ) { 1726 $SQLExt .= " AND $TicketTime{$Key} != 0"; 1727 } 1728 1729 my $Time = $Kernel::OM->Create('Kernel::System::DateTime'); 1730 $Time->Subtract( Minutes => $Param{ $Key . 'NewerMinutes' } ); 1731 1732 my $TargetTime = $Key eq 'TicketCreateTime' ? $Time->ToString() : $Time->ToEpoch(); 1733 1734 $SQLExt .= sprintf( " AND ( %s >= '%s' )", $TicketTime{$Key}, $TargetTime ); 1735 } 1736 } 1737 1738 # get tickets created/escalated older/newer than xxxx-xx-xx xx:xx date 1739 for my $Key ( sort keys %TicketTime ) { 1740 1741 # get tickets created/escalated older than xxxx-xx-xx xx:xx date 1742 my $CompareOlderNewerDate; 1743 if ( $Param{ $Key . 'OlderDate' } ) { 1744 1745 # check time format 1746 if ( 1747 $Param{ $Key . 'OlderDate' } 1748 !~ /\d\d\d\d-(\d\d|\d)-(\d\d|\d) (\d\d|\d):(\d\d|\d):(\d\d|\d)/ 1749 ) 1750 { 1751 $Kernel::OM->Get('Kernel::System::Log')->Log( 1752 Priority => 'error', 1753 Message => "Invalid time format '" . $Param{ $Key . 'OlderDate' } . "'!", 1754 ); 1755 return; 1756 } 1757 1758 # exclude tickets with no escalation 1759 if ( $Key =~ m{ \A TicketEscalation }xms ) { 1760 $SQLExt .= " AND $TicketTime{$Key} != 0"; 1761 } 1762 my $Time = $Kernel::OM->Create( 1763 'Kernel::System::DateTime', 1764 ObjectParams => { 1765 String => $Param{ $Key . 'OlderDate' }, 1766 } 1767 ); 1768 1769 if ( !$Time ) { 1770 $Kernel::OM->Get('Kernel::System::Log')->Log( 1771 Priority => 'error', 1772 Message => 1773 "Search not executed due to invalid time '" 1774 . $Param{ $Key . 'OlderDate' } . "'!", 1775 ); 1776 return; 1777 } 1778 $CompareOlderNewerDate = $Time; 1779 1780 my $TargetTime = $Key eq 'TicketCreateTime' ? $Time->ToString() : $Time->ToEpoch(); 1781 1782 $SQLExt .= sprintf( " AND ( %s <= '%s' )", $TicketTime{$Key}, $TargetTime ); 1783 } 1784 1785 # get tickets created/escalated newer than xxxx-xx-xx xx:xx date 1786 if ( $Param{ $Key . 'NewerDate' } ) { 1787 if ( 1788 $Param{ $Key . 'NewerDate' } 1789 !~ /\d\d\d\d-(\d\d|\d)-(\d\d|\d) (\d\d|\d):(\d\d|\d):(\d\d|\d)/ 1790 ) 1791 { 1792 $Kernel::OM->Get('Kernel::System::Log')->Log( 1793 Priority => 'error', 1794 Message => "Invalid time format '" . $Param{ $Key . 'NewerDate' } . "'!", 1795 ); 1796 return; 1797 } 1798 1799 # exclude tickets with no escalation 1800 if ( $Key =~ m{ \A TicketEscalation }xms ) { 1801 $SQLExt .= " AND $TicketTime{$Key} != 0"; 1802 } 1803 my $Time = $Kernel::OM->Create( 1804 'Kernel::System::DateTime', 1805 ObjectParams => { 1806 String => $Param{ $Key . 'NewerDate' }, 1807 } 1808 ); 1809 if ( !$Time ) { 1810 $Kernel::OM->Get('Kernel::System::Log')->Log( 1811 Priority => 'error', 1812 Message => 1813 "Search not executed due to invalid time '" 1814 . $Param{ $Key . 'NewerDate' } . "'!", 1815 ); 1816 return; 1817 } 1818 1819 # don't execute queries if newer date is after current date 1820 return if $Time > $DateTimeObject; 1821 1822 # don't execute queries if older/newer date restriction show now valid timeframe 1823 return if $CompareOlderNewerDate && $Time > $CompareOlderNewerDate; 1824 1825 my $TargetTime = $Key eq 'TicketCreateTime' ? $Time->ToString() : $Time->ToEpoch(); 1826 1827 $SQLExt .= sprintf( " AND ( %s >= '%s' )", $TicketTime{$Key}, $TargetTime ); 1828 } 1829 } 1830 1831 # get tickets changed older than x minutes 1832 if ( defined $Param{TicketChangeTimeOlderMinutes} ) { 1833 1834 $Param{TicketChangeTimeOlderMinutes} ||= 0; 1835 1836 my $TimeStamp = $Kernel::OM->Create('Kernel::System::DateTime'); 1837 $TimeStamp->Subtract( Minutes => $Param{TicketChangeTimeOlderMinutes} ); 1838 1839 $Param{TicketChangeTimeOlderDate} = $TimeStamp->ToString(); 1840 } 1841 1842 # get tickets changed newer than x minutes 1843 if ( defined $Param{TicketChangeTimeNewerMinutes} ) { 1844 1845 $Param{TicketChangeTimeNewerMinutes} ||= 0; 1846 1847 my $TimeStamp = $Kernel::OM->Create('Kernel::System::DateTime'); 1848 $TimeStamp->Subtract( Minutes => $Param{TicketChangeTimeNewerMinutes} ); 1849 1850 $Param{TicketChangeTimeNewerDate} = $TimeStamp->ToString(); 1851 } 1852 1853 # get tickets based on ticket history changed older than xxxx-xx-xx xx:xx date 1854 my $CompareChangeTimeOlderNewerDate; 1855 if ( $Param{TicketChangeTimeOlderDate} ) { 1856 1857 # check time format 1858 if ( 1859 $Param{TicketChangeTimeOlderDate} 1860 !~ /\d\d\d\d-(\d\d|\d)-(\d\d|\d) (\d\d|\d):(\d\d|\d):(\d\d|\d)/ 1861 ) 1862 { 1863 $Kernel::OM->Get('Kernel::System::Log')->Log( 1864 Priority => 'error', 1865 Message => "Invalid time format '$Param{TicketChangeTimeOlderDate}'!", 1866 ); 1867 return; 1868 } 1869 1870 my $Time = $Kernel::OM->Create( 1871 'Kernel::System::DateTime', 1872 ObjectParams => { 1873 String => $Param{TicketChangeTimeOlderDate}, 1874 } 1875 ); 1876 1877 if ( !$Time ) { 1878 $Kernel::OM->Get('Kernel::System::Log')->Log( 1879 Priority => 'error', 1880 Message => 1881 "Search not executed due to invalid time '" 1882 . $Param{TicketChangeTimeOlderDate} . "'!", 1883 ); 1884 return; 1885 } 1886 $CompareChangeTimeOlderNewerDate = $Time; 1887 1888 my $THRef = $Self->_TicketHistoryReferenceForSearchArgument( 1889 Argument => 'TicketChangeTimeOlderDate', 1890 ); 1891 return if !$THRef; 1892 1893 $SQLExt .= " AND ${ THRef }.create_time <= '" 1894 . $DBObject->Quote( $Param{TicketChangeTimeOlderDate} ) . "'"; 1895 } 1896 1897 # get tickets based on ticket history changed newer than xxxx-xx-xx xx:xx date 1898 if ( $Param{TicketChangeTimeNewerDate} ) { 1899 if ( 1900 $Param{TicketChangeTimeNewerDate} 1901 !~ /\d\d\d\d-(\d\d|\d)-(\d\d|\d) (\d\d|\d):(\d\d|\d):(\d\d|\d)/ 1902 ) 1903 { 1904 $Kernel::OM->Get('Kernel::System::Log')->Log( 1905 Priority => 'error', 1906 Message => "Invalid time format '$Param{TicketChangeTimeNewerDate}'!", 1907 ); 1908 return; 1909 } 1910 1911 my $Time = $Kernel::OM->Create( 1912 'Kernel::System::DateTime', 1913 ObjectParams => { 1914 String => $Param{TicketChangeTimeNewerDate}, 1915 } 1916 ); 1917 1918 if ( !$Time ) { 1919 $Kernel::OM->Get('Kernel::System::Log')->Log( 1920 Priority => 'error', 1921 Message => 1922 "Search not executed due to invalid time '" 1923 . $Param{TicketChangeTimeNewerDate} . "'!", 1924 ); 1925 return; 1926 } 1927 1928 # don't execute queries if newer date is after current date 1929 return if $Time > $DateTimeObject; 1930 1931 # don't execute queries if older/newer date restriction show now valid timeframe 1932 return if $CompareChangeTimeOlderNewerDate && $Time > $CompareChangeTimeOlderNewerDate; 1933 1934 my $THRef = $Self->_TicketHistoryReferenceForSearchArgument( 1935 Argument => 'TicketChangeTimeNewerDate', 1936 ); 1937 return if !$THRef; 1938 1939 $SQLExt .= " AND ${ THRef }.create_time >= '" 1940 . $DBObject->Quote( $Param{TicketChangeTimeNewerDate} ) . "'"; 1941 } 1942 1943 # get tickets changed older than x minutes 1944 if ( defined $Param{TicketLastChangeTimeOlderMinutes} ) { 1945 1946 $Param{TicketLastChangeTimeOlderMinutes} ||= 0; 1947 1948 my $TimeStamp = $DateTimeObject->Clone(); 1949 $TimeStamp->Subtract( Minutes => $Param{TicketLastChangeTimeOlderMinutes} ); 1950 1951 $Param{TicketLastChangeTimeOlderDate} = $TimeStamp->ToString(); 1952 } 1953 1954 # get tickets changed newer than x minutes 1955 if ( defined $Param{TicketLastChangeTimeNewerMinutes} ) { 1956 1957 $Param{TicketLastChangeTimeNewerMinutes} ||= 0; 1958 1959 my $TimeStamp = $DateTimeObject->Clone(); 1960 $TimeStamp->Subtract( Minutes => $Param{TicketLastChangeTimeNewerMinutes} ); 1961 1962 $Param{TicketLastChangeTimeNewerDate} = $TimeStamp->ToString(); 1963 } 1964 1965 # get tickets changed older than xxxx-xx-xx xx:xx date 1966 my $CompareLastChangeTimeOlderNewerDate; 1967 if ( $Param{TicketLastChangeTimeOlderDate} ) { 1968 1969 # check time format 1970 if ( 1971 $Param{TicketLastChangeTimeOlderDate} 1972 !~ /\d\d\d\d-(\d\d|\d)-(\d\d|\d) (\d\d|\d):(\d\d|\d):(\d\d|\d)/ 1973 ) 1974 { 1975 $Kernel::OM->Get('Kernel::System::Log')->Log( 1976 Priority => 'error', 1977 Message => "Invalid time format '$Param{TicketLastChangeTimeOlderDate}'!", 1978 ); 1979 return; 1980 } 1981 1982 my $Time = $Kernel::OM->Create( 1983 'Kernel::System::DateTime', 1984 ObjectParams => { 1985 String => $Param{TicketLastChangeTimeOlderDate}, 1986 } 1987 ); 1988 1989 if ( !$Time ) { 1990 $Kernel::OM->Get('Kernel::System::Log')->Log( 1991 Priority => 'error', 1992 Message => 1993 "Search not executed due to invalid time '" 1994 . $Param{TicketLastChangeTimeOlderDate} . "'!", 1995 ); 1996 return; 1997 } 1998 $CompareLastChangeTimeOlderNewerDate = $Time; 1999 2000 $SQLExt .= " AND st.change_time <= '" 2001 . $DBObject->Quote( $Param{TicketLastChangeTimeOlderDate} ) . "'"; 2002 } 2003 2004 # get tickets changed newer than xxxx-xx-xx xx:xx date 2005 if ( $Param{TicketLastChangeTimeNewerDate} ) { 2006 if ( 2007 $Param{TicketLastChangeTimeNewerDate} 2008 !~ /\d\d\d\d-(\d\d|\d)-(\d\d|\d) (\d\d|\d):(\d\d|\d):(\d\d|\d)/ 2009 ) 2010 { 2011 $Kernel::OM->Get('Kernel::System::Log')->Log( 2012 Priority => 'error', 2013 Message => "Invalid time format '$Param{TicketLastChangeTimeNewerDate}'!", 2014 ); 2015 return; 2016 } 2017 2018 my $Time = $Kernel::OM->Create( 2019 'Kernel::System::DateTime', 2020 ObjectParams => { 2021 String => $Param{TicketLastChangeTimeNewerDate}, 2022 } 2023 ); 2024 2025 if ( !$Time ) { 2026 $Kernel::OM->Get('Kernel::System::Log')->Log( 2027 Priority => 'error', 2028 Message => 2029 "Search not executed due to invalid time '" 2030 . $Param{TicketLastChangeTimeNewerDate} . "'!", 2031 ); 2032 return; 2033 } 2034 2035 # don't execute queries if newer date is after current date 2036 return if $Time > $DateTimeObject; 2037 2038 # don't execute queries if older/newer date restriction show now valid timeframe 2039 return 2040 if $CompareLastChangeTimeOlderNewerDate && $Time > $CompareLastChangeTimeOlderNewerDate; 2041 2042 $SQLExt .= " AND st.change_time >= '" 2043 . $DBObject->Quote( $Param{TicketLastChangeTimeNewerDate} ) . "'"; 2044 } 2045 2046 # get tickets closed older than x minutes 2047 if ( defined $Param{TicketCloseTimeOlderMinutes} ) { 2048 2049 $Param{TicketCloseTimeOlderMinutes} ||= 0; 2050 2051 my $TimeStamp = $DateTimeObject->Clone(); 2052 $TimeStamp->Subtract( Minutes => $Param{TicketCloseTimeOlderMinutes} ); 2053 2054 $Param{TicketCloseTimeOlderDate} = $TimeStamp->ToString(); 2055 } 2056 2057 # get tickets closed newer than x minutes 2058 if ( defined $Param{TicketCloseTimeNewerMinutes} ) { 2059 2060 $Param{TicketCloseTimeNewerMinutes} ||= 0; 2061 2062 my $TimeStamp = $DateTimeObject->Clone(); 2063 $TimeStamp->Subtract( Minutes => $Param{TicketCloseTimeNewerMinutes} ); 2064 2065 $Param{TicketCloseTimeNewerDate} = $TimeStamp->ToString(); 2066 } 2067 2068 # get tickets closed older than xxxx-xx-xx xx:xx date 2069 my $CompareCloseTimeOlderNewerDate; 2070 if ( $Param{TicketCloseTimeOlderDate} ) { 2071 my $THRef = $Self->_TicketHistoryReferenceForSearchArgument( 2072 Argument => 'TicketCloseTimeOlderDate', 2073 ); 2074 return if !$THRef; 2075 2076 # check time format 2077 if ( 2078 $Param{TicketCloseTimeOlderDate} 2079 !~ /\d\d\d\d-(\d\d|\d)-(\d\d|\d) (\d\d|\d):(\d\d|\d):(\d\d|\d)/ 2080 ) 2081 { 2082 $Kernel::OM->Get('Kernel::System::Log')->Log( 2083 Priority => 'error', 2084 Message => "Invalid time format '$Param{TicketCloseTimeOlderDate}'!", 2085 ); 2086 return; 2087 } 2088 2089 my $Time = $Kernel::OM->Create( 2090 'Kernel::System::DateTime', 2091 ObjectParams => { 2092 String => $Param{TicketCloseTimeOlderDate}, 2093 } 2094 ); 2095 2096 if ( !$Time ) { 2097 $Kernel::OM->Get('Kernel::System::Log')->Log( 2098 Priority => 'error', 2099 Message => 2100 "Search not executed due to invalid time '" 2101 . $Param{TicketCloseTimeOlderDate} . "'!", 2102 ); 2103 return; 2104 } 2105 $CompareCloseTimeOlderNewerDate = $Time; 2106 2107 # get close state ids 2108 my @List = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType( 2109 StateType => ['closed'], 2110 Result => 'ID', 2111 ); 2112 my @StateID = ( $Self->HistoryTypeLookup( Type => 'NewTicket' ) ); 2113 push( @StateID, $Self->HistoryTypeLookup( Type => 'StateUpdate' ) ); 2114 if (@StateID) { 2115 $SQLExt .= sprintf( 2116 " AND %s.history_type_id IN (%s) AND %s.state_id IN (%s) AND %s.create_time <= '%s'", 2117 $THRef, 2118 ( join ', ', sort @StateID ), 2119 $THRef, 2120 ( join ', ', sort @List ), 2121 $THRef, 2122 $DBObject->Quote( $Param{TicketCloseTimeOlderDate} ) 2123 ); 2124 } 2125 } 2126 2127 # get tickets closed newer than xxxx-xx-xx xx:xx date 2128 if ( $Param{TicketCloseTimeNewerDate} ) { 2129 my $THRef = $Self->_TicketHistoryReferenceForSearchArgument( 2130 Argument => 'TicketCloseTimeNewerDate', 2131 ); 2132 return if !$THRef; 2133 2134 if ( 2135 $Param{TicketCloseTimeNewerDate} 2136 !~ /\d\d\d\d-(\d\d|\d)-(\d\d|\d) (\d\d|\d):(\d\d|\d):(\d\d|\d)/ 2137 ) 2138 { 2139 $Kernel::OM->Get('Kernel::System::Log')->Log( 2140 Priority => 'error', 2141 Message => "Invalid time format '$Param{TicketCloseTimeNewerDate}'!", 2142 ); 2143 return; 2144 } 2145 2146 my $Time = $Kernel::OM->Create( 2147 'Kernel::System::DateTime', 2148 ObjectParams => { 2149 String => $Param{TicketCloseTimeNewerDate}, 2150 } 2151 ); 2152 2153 if ( !$Time ) { 2154 $Kernel::OM->Get('Kernel::System::Log')->Log( 2155 Priority => 'error', 2156 Message => 2157 "Search not executed due to invalid time '" 2158 . $Param{TicketCloseTimeNewerDate} . "'!", 2159 ); 2160 return; 2161 } 2162 2163 # don't execute queries if newer date is after current date 2164 return if $Time > $DateTimeObject; 2165 2166 # don't execute queries if older/newer date restriction show now valid timeframe 2167 return if $CompareCloseTimeOlderNewerDate && $Time > $CompareCloseTimeOlderNewerDate; 2168 2169 # get close state ids 2170 my @List = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType( 2171 StateType => ['closed'], 2172 Result => 'ID', 2173 ); 2174 my @StateID = ( $Self->HistoryTypeLookup( Type => 'NewTicket' ) ); 2175 push( @StateID, $Self->HistoryTypeLookup( Type => 'StateUpdate' ) ); 2176 if (@StateID) { 2177 $SQLExt .= sprintf( 2178 " AND %s.history_type_id IN (%s) AND %s.state_id IN (%s) AND %s.create_time >= '%s'", 2179 $THRef, 2180 ( join ', ', sort @StateID ), 2181 $THRef, 2182 ( join ', ', sort @List ), 2183 $THRef, 2184 $DBObject->Quote( $Param{TicketCloseTimeNewerDate} ) 2185 ); 2186 } 2187 } 2188 2189 # Get tickets last closed older than x minutes. 2190 if ( defined $Param{TicketLastCloseTimeOlderMinutes} ) { 2191 2192 $Param{TicketLastCloseTimeOlderMinutes} ||= 0; 2193 2194 my $TimeStamp = $DateTimeObject->Clone(); 2195 $TimeStamp->Subtract( Minutes => $Param{TicketLastCloseTimeOlderMinutes} ); 2196 2197 $Param{TicketLastCloseTimeOlderDate} = $TimeStamp->ToString(); 2198 } 2199 2200 # Get tickets last closed newer than x minutes. 2201 if ( defined $Param{TicketLastCloseTimeNewerMinutes} ) { 2202 2203 $Param{TicketLastCloseTimeNewerMinutes} ||= 0; 2204 2205 my $TimeStamp = $DateTimeObject->Clone(); 2206 $TimeStamp->Subtract( Minutes => $Param{TicketLastCloseTimeNewerMinutes} ); 2207 2208 $Param{TicketLastCloseTimeNewerDate} = $TimeStamp->ToString(); 2209 } 2210 2211 # Get tickets last closed older than xxxx-xx-xx xx:xx date. 2212 my $CompareLastCloseTimeOlderNewerDate; 2213 if ( $Param{TicketLastCloseTimeOlderDate} ) { 2214 my $THRef = $Self->_TicketHistoryReferenceForSearchArgument( 2215 Argument => 'TicketLastCloseTimeOlderDate', 2216 ); 2217 return if !$THRef; 2218 2219 # Check time format. 2220 if ( 2221 $Param{TicketLastCloseTimeOlderDate} 2222 !~ /\d\d\d\d-(\d\d|\d)-(\d\d|\d) (\d\d|\d):(\d\d|\d):(\d\d|\d)/ 2223 ) 2224 { 2225 $Kernel::OM->Get('Kernel::System::Log')->Log( 2226 Priority => 'error', 2227 Message => "Invalid time format '$Param{TicketLastCloseTimeOlderDate}'!", 2228 ); 2229 return; 2230 } 2231 2232 my $Time = $Kernel::OM->Create( 2233 'Kernel::System::DateTime', 2234 ObjectParams => { 2235 String => $Param{TicketLastCloseTimeOlderDate}, 2236 } 2237 ); 2238 2239 if ( !$Time ) { 2240 $Kernel::OM->Get('Kernel::System::Log')->Log( 2241 Priority => 'error', 2242 Message => 2243 "Search not executed due to invalid time '" 2244 . $Param{TicketLastCloseTimeOlderDate} . "'!", 2245 ); 2246 return; 2247 } 2248 $CompareLastCloseTimeOlderNewerDate = $Time; 2249 2250 # Get close state ids. 2251 my @List = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType( 2252 StateType => ['closed'], 2253 Result => 'ID', 2254 ); 2255 my @StateID = ( $Self->HistoryTypeLookup( Type => 'NewTicket' ) ); 2256 push( @StateID, $Self->HistoryTypeLookup( Type => 'StateUpdate' ) ); 2257 if (@StateID) { 2258 $SQLExt .= sprintf( 2259 " AND %s.history_type_id IN (%s) AND %s.state_id IN (%s) AND " 2260 . "%s.create_time <= '%s' AND " 2261 . "%s.create_time IN " 2262 . "(" 2263 . "SELECT lco1.create_time " 2264 . "FROM ticket_history lco1 " 2265 . "INNER JOIN " 2266 . "(" 2267 . "SELECT ticket_id, MAX(create_time) AS max_time " 2268 . "FROM ticket_history " 2269 . "WHERE history_type_id IN (%s) AND state_id IN (%s) " 2270 . "GROUP BY ticket_id " 2271 . ") lco2 " 2272 . "ON lco1.ticket_id = lco2.ticket_id " 2273 . "AND lco1.create_time = lco2.max_time " 2274 . ") ", 2275 $THRef, 2276 ( join ', ', sort @StateID ), 2277 $THRef, 2278 ( join ', ', sort @List ), 2279 $THRef, 2280 $DBObject->Quote( $Param{TicketLastCloseTimeOlderDate} ), 2281 $THRef, 2282 ( join ', ', sort @StateID ), 2283 ( join ', ', sort @List ) 2284 ); 2285 } 2286 } 2287 2288 # Get tickets last closed newer than xxxx-xx-xx xx:xx date. 2289 if ( $Param{TicketLastCloseTimeNewerDate} ) { 2290 my $THRef = $Self->_TicketHistoryReferenceForSearchArgument( 2291 Argument => 'TicketLastCloseTimeNewerDate', 2292 ); 2293 return if !$THRef; 2294 2295 if ( 2296 $Param{TicketLastCloseTimeNewerDate} 2297 !~ /\d\d\d\d-(\d\d|\d)-(\d\d|\d) (\d\d|\d):(\d\d|\d):(\d\d|\d)/ 2298 ) 2299 { 2300 $Kernel::OM->Get('Kernel::System::Log')->Log( 2301 Priority => 'error', 2302 Message => "Invalid time format '$Param{TicketLastCloseTimeNewerDate}'!", 2303 ); 2304 return; 2305 } 2306 2307 my $Time = $Kernel::OM->Create( 2308 'Kernel::System::DateTime', 2309 ObjectParams => { 2310 String => $Param{TicketLastCloseTimeNewerDate}, 2311 } 2312 ); 2313 2314 if ( !$Time ) { 2315 $Kernel::OM->Get('Kernel::System::Log')->Log( 2316 Priority => 'error', 2317 Message => 2318 "Search not executed due to invalid time '" 2319 . $Param{TicketLastCloseTimeNewerDate} . "'!", 2320 ); 2321 return; 2322 } 2323 2324 # Don't execute queries if newer date is after current date. 2325 return if $Time > $DateTimeObject; 2326 2327 # Don't execute queries if older/newer date restriction show now valid timeframe. 2328 return if $CompareLastCloseTimeOlderNewerDate && $Time > $CompareLastCloseTimeOlderNewerDate; 2329 2330 # Get close state ids. 2331 my @List = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType( 2332 StateType => ['closed'], 2333 Result => 'ID', 2334 ); 2335 my @StateID = ( $Self->HistoryTypeLookup( Type => 'NewTicket' ) ); 2336 push( @StateID, $Self->HistoryTypeLookup( Type => 'StateUpdate' ) ); 2337 if (@StateID) { 2338 $SQLExt .= sprintf( 2339 " AND %s.history_type_id IN (%s) AND %s.state_id IN (%s) AND " 2340 . "%s.create_time >= '%s' AND " 2341 . "%s.create_time IN " 2342 . "(" 2343 . "SELECT lcn1.create_time " 2344 . "FROM ticket_history lcn1 " 2345 . "INNER JOIN " 2346 . "(" 2347 . "SELECT ticket_id, MAX(create_time) AS max_time " 2348 . "FROM ticket_history " 2349 . "WHERE history_type_id IN (%s) AND state_id IN (%s) " 2350 . "GROUP BY ticket_id " 2351 . ") lcn2 " 2352 . "ON lcn1.ticket_id = lcn2.ticket_id " 2353 . "AND lcn1.create_time = lcn2.max_time " 2354 . ") ", 2355 $THRef, 2356 ( join ', ', sort @StateID ), 2357 $THRef, 2358 ( join ', ', sort @List ), 2359 $THRef, 2360 $DBObject->Quote( $Param{TicketLastCloseTimeNewerDate} ), 2361 $THRef, 2362 ( join ', ', sort @StateID ), 2363 ( join ', ', sort @List ) 2364 ); 2365 } 2366 } 2367 2368 # check if only pending states are used 2369 if ( 2370 defined $Param{TicketPendingTimeOlderMinutes} 2371 || defined $Param{TicketPendingTimeNewerMinutes} 2372 || $Param{TicketPendingTimeOlderDate} 2373 || $Param{TicketPendingTimeNewerDate} 2374 ) 2375 { 2376 2377 # get pending state ids 2378 my @List = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType( 2379 StateType => [ 'pending reminder', 'pending auto' ], 2380 Result => 'ID', 2381 ); 2382 if (@List) { 2383 $SQLExt .= " AND st.ticket_state_id IN (${\(join ', ', sort @List)}) "; 2384 } 2385 } 2386 2387 # get tickets pending older than x minutes 2388 if ( defined $Param{TicketPendingTimeOlderMinutes} ) { 2389 2390 $Param{TicketPendingTimeOlderMinutes} ||= 0; 2391 2392 my $TimeStamp = $Kernel::OM->Create('Kernel::System::DateTime'); 2393 2394 $TimeStamp->Subtract( Minutes => $Param{TicketPendingTimeOlderMinutes} ); 2395 2396 $Param{TicketPendingTimeOlderDate} = $TimeStamp->ToString(); 2397 } 2398 2399 # get tickets pending newer than x minutes 2400 if ( defined $Param{TicketPendingTimeNewerMinutes} ) { 2401 2402 $Param{TicketPendingTimeNewerMinutes} ||= 0; 2403 2404 my $TimeStamp = $DateTimeObject->Clone(); 2405 $TimeStamp->Subtract( Minutes => $Param{TicketPendingTimeNewerMinutes} ); 2406 2407 $Param{TicketPendingTimeNewerDate} = $TimeStamp->ToString(); 2408 } 2409 2410 # get pending tickets older than xxxx-xx-xx xx:xx date 2411 my $ComparePendingTimeOlderNewerDate; 2412 if ( $Param{TicketPendingTimeOlderDate} ) { 2413 2414 # check time format 2415 if ( 2416 $Param{TicketPendingTimeOlderDate} 2417 !~ /\d\d\d\d-(\d\d|\d)-(\d\d|\d) (\d\d|\d):(\d\d|\d):(\d\d|\d)/ 2418 ) 2419 { 2420 $Kernel::OM->Get('Kernel::System::Log')->Log( 2421 Priority => 'error', 2422 Message => "Invalid time format '$Param{TicketPendingTimeOlderDate}'!", 2423 ); 2424 return; 2425 } 2426 2427 my $TimeStamp = $Kernel::OM->Create( 2428 'Kernel::System::DateTime', 2429 ObjectParams => { 2430 String => $Param{TicketPendingTimeOlderDate}, 2431 } 2432 ); 2433 2434 if ( !$TimeStamp ) { 2435 $Kernel::OM->Get('Kernel::System::Log')->Log( 2436 Priority => 'error', 2437 Message => 2438 "Search not executed due to invalid time '" 2439 . $Param{TicketPendingTimeOlderDate} . "'!", 2440 ); 2441 return; 2442 } 2443 $ComparePendingTimeOlderNewerDate = $TimeStamp; 2444 2445 $SQLExt .= " AND st.until_time <= " . $TimeStamp->ToEpoch(); 2446 } 2447 2448 # get pending tickets newer than xxxx-xx-xx xx:xx date 2449 if ( $Param{TicketPendingTimeNewerDate} ) { 2450 if ( 2451 $Param{TicketPendingTimeNewerDate} 2452 !~ /\d\d\d\d-(\d\d|\d)-(\d\d|\d) (\d\d|\d):(\d\d|\d):(\d\d|\d)/ 2453 ) 2454 { 2455 $Kernel::OM->Get('Kernel::System::Log')->Log( 2456 Priority => 'error', 2457 Message => "Invalid time format '$Param{TicketPendingTimeNewerDate}'!", 2458 ); 2459 return; 2460 } 2461 2462 my $TimeStamp = $Kernel::OM->Create( 2463 'Kernel::System::DateTime', 2464 ObjectParams => { 2465 String => $Param{TicketPendingTimeNewerDate}, 2466 } 2467 ); 2468 2469 if ( !$TimeStamp ) { 2470 $Kernel::OM->Get('Kernel::System::Log')->Log( 2471 Priority => 'error', 2472 Message => 2473 "Search not executed due to invalid time '" 2474 . $Param{TicketPendingTimeNewerDate} . "'!", 2475 ); 2476 return; 2477 } 2478 2479 # don't execute queries if older/newer date restriction show now valid timeframe 2480 return 2481 if $ComparePendingTimeOlderNewerDate && $TimeStamp > $ComparePendingTimeOlderNewerDate; 2482 2483 $SQLExt .= " AND st.until_time >= " . $TimeStamp->ToEpoch(); 2484 } 2485 2486 # archive flag 2487 if ( $Kernel::OM->Get('Kernel::Config')->Get('Ticket::ArchiveSystem') ) { 2488 2489 # if no flag is given, only search for not archived ticket 2490 if ( !$Param{ArchiveFlags} ) { 2491 $Param{ArchiveFlags} = ['n']; 2492 } 2493 2494 # prepare search with archive flags, check arguments 2495 if ( ref $Param{ArchiveFlags} ne 'ARRAY' ) { 2496 $Kernel::OM->Get('Kernel::System::Log')->Log( 2497 Priority => 'error', 2498 Message => "Invalid attribute ArchiveFlags '$Param{ArchiveFlags}'!", 2499 ); 2500 return; 2501 } 2502 2503 # prepare options 2504 my %Options; 2505 for my $Key ( @{ $Param{ArchiveFlags} } ) { 2506 $Options{$Key} = 1; 2507 } 2508 2509 # search for archived 2510 if ( $Options{y} && !$Options{n} ) { 2511 $SQLExt .= ' AND archive_flag = 1'; 2512 } 2513 2514 # search for not archived 2515 elsif ( !$Options{y} && $Options{n} ) { 2516 $SQLExt .= ' AND archive_flag = 0'; 2517 } 2518 } 2519 2520 # database query for sort/order by option 2521 if ( $Result ne 'COUNT' ) { 2522 $SQLExt .= ' ORDER BY'; 2523 for my $Count ( 0 .. $#SortByArray ) { 2524 if ( $Count > 0 ) { 2525 $SQLExt .= ','; 2526 } 2527 2528 # sort by dynamic field 2529 if ( $ValidDynamicFieldParams{ $SortByArray[$Count] } ) { 2530 my ($DynamicFieldName) = $SortByArray[$Count] =~ m/^DynamicField_(.*)$/smx; 2531 2532 my $DynamicField = $TicketDynamicFieldName2Config{$DynamicFieldName} || 2533 $ArticleDynamicFieldName2Config{$DynamicFieldName}; 2534 2535 # If the table was already joined for searching, we reuse it. 2536 if ( !$DynamicFieldJoinTables{$DynamicFieldName} ) { 2537 2538 if ( $TicketDynamicFieldName2Config{$DynamicFieldName} ) { 2539 2540 # Join the table for this dynamic field; use a left outer join in this case. 2541 # With an INNER JOIN we'd limit the result set to tickets which have an entry 2542 # for the DF which is used for sorting. 2543 $SQLFrom 2544 .= " LEFT OUTER JOIN dynamic_field_value dfv$DynamicFieldJoinCounter 2545 ON (st.id = dfv$DynamicFieldJoinCounter.object_id 2546 AND dfv$DynamicFieldJoinCounter.field_id = " . 2547 $DBObject->Quote( $DynamicField->{ID}, 'Integer' ) . ") "; 2548 } 2549 elsif ( $ArticleDynamicFieldName2Config{$DynamicFieldName} ) { 2550 if ( !$ArticleTableJoined ) { 2551 $SQLFrom .= ' INNER JOIN article art ON st.id = art.ticket_id '; 2552 $ArticleTableJoined = 1; 2553 } 2554 2555 $SQLFrom 2556 .= " LEFT OUTER JOIN dynamic_field_value dfv$DynamicFieldJoinCounter 2557 ON (art.id = dfv$DynamicFieldJoinCounter.object_id 2558 AND dfv$DynamicFieldJoinCounter.field_id = " . 2559 $DBObject->Quote( $DynamicField->{ID}, 'Integer' ) . ") "; 2560 } 2561 2562 $DynamicFieldJoinTables{ $DynamicField->{Name} } = "dfv$DynamicFieldJoinCounter"; 2563 2564 $DynamicFieldJoinCounter++; 2565 } 2566 2567 my $SQLOrderField = $DynamicFieldBackendObject->SearchSQLOrderFieldGet( 2568 DynamicFieldConfig => $DynamicField, 2569 TableAlias => $DynamicFieldJoinTables{$DynamicFieldName}, 2570 ); 2571 2572 $SQLSelect .= ", $SQLOrderField "; 2573 $SQLExt .= " $SQLOrderField "; 2574 } 2575 elsif ( 2576 $SortByArray[$Count] eq 'Owner' 2577 || $SortByArray[$Count] eq 'Responsible' 2578 ) 2579 { 2580 # Include first name, last name and login in select. 2581 $SQLSelect 2582 .= ', ' . $SortOptions{ $SortByArray[$Count] } 2583 . ', u.first_name, u.last_name, u.login '; 2584 2585 # Join the users table on user's ID. 2586 $SQLFrom 2587 .= ' JOIN users u ' 2588 . ' ON ' . $SortOptions{ $SortByArray[$Count] } . ' = u.id '; 2589 2590 my $FirstnameLastNameOrder = $Kernel::OM->Get('Kernel::Config')->Get('FirstnameLastnameOrder') || 0; 2591 my $OrderBySuffix = $OrderByArray[$Count] eq 'Up' ? 'ASC' : 'DESC'; 2592 2593 # Sort by configured first and last name order. 2594 if ( $FirstnameLastNameOrder eq '1' || $FirstnameLastNameOrder eq '6' ) { 2595 $SQLExt .= " u.last_name $OrderBySuffix, u.first_name "; 2596 } 2597 elsif ( $FirstnameLastNameOrder eq '2' ) { 2598 $SQLExt .= " u.first_name $OrderBySuffix, u.last_name $OrderBySuffix, u.login "; 2599 } 2600 elsif ( $FirstnameLastNameOrder eq '3' || $FirstnameLastNameOrder eq '7' ) { 2601 $SQLExt .= " u.last_name $OrderBySuffix, u.first_name $OrderBySuffix, u.login "; 2602 } 2603 elsif ( $FirstnameLastNameOrder eq '4' ) { 2604 $SQLExt .= " u.login $OrderBySuffix, u.first_name $OrderBySuffix, u.last_name "; 2605 } 2606 elsif ( $FirstnameLastNameOrder eq '5' || $FirstnameLastNameOrder eq '8' ) { 2607 $SQLExt .= " u.login $OrderBySuffix, u.last_name $OrderBySuffix, u.first_name "; 2608 } 2609 else { 2610 $SQLExt .= " u.first_name $OrderBySuffix, u.last_name "; 2611 } 2612 } 2613 elsif ( 2614 $SortByArray[$Count] eq 'EscalationUpdateTime' 2615 || $SortByArray[$Count] eq 'EscalationResponseTime' 2616 || $SortByArray[$Count] eq 'EscalationSolutionTime' 2617 || $SortByArray[$Count] eq 'EscalationTime' 2618 || $SortByArray[$Count] eq 'PendingTime' 2619 ) 2620 { 2621 2622 # Tickets with no Escalation or Pending time have '0' as value in the according ticket columns. 2623 # When sorting by these columns always place ticket's with '0' value on the end, no matter order by. 2624 if ( $Kernel::OM->Get('Kernel::System::DB')->{'DB::Type'} eq 'mysql' ) { 2625 2626 # For MySQL create SQL order by query 'ORDER BY column_value = 0, column_value ASC/DESC'. 2627 $SQLSelect .= ', ' . $SortOptions{ $SortByArray[$Count] }; 2628 $SQLExt 2629 .= ' ' . $SortOptions{ $SortByArray[$Count] }; 2630 } 2631 else { 2632 2633 # For PostgreSQL and Oracle transform selected 0 values to NULL and use 'NULLS LAST' 2634 # in the end of SQL query. 2635 $SQLSelect .= ', ' . $SortOptions{ $SortByArray[$Count] } . ' AS order_value '; 2636 $SQLExt .= ' order_value '; 2637 } 2638 } 2639 else { 2640 2641 # Regular sort. 2642 $SQLSelect .= ', ' . $SortOptions{ $SortByArray[$Count] }; 2643 $SQLExt .= ' ' . $SortOptions{ $SortByArray[$Count] }; 2644 } 2645 2646 if ( $OrderByArray[$Count] eq 'Up' ) { 2647 $SQLExt .= ' ASC'; 2648 } 2649 else { 2650 $SQLExt .= ' DESC'; 2651 } 2652 } 2653 } 2654 2655 # Add only the sql join for the queue table, if columns from the queue table exists in the sql statement. 2656 if ( %GroupList || $LookupSortByArray{Queue} ) { 2657 $SQLFrom .= ' INNER JOIN queue sq ON sq.id = st.queue_id '; 2658 } 2659 2660 # check cache 2661 my $CacheObject; 2662 if ( ( $ArticleTableJoined && $Param{FullTextIndex} ) || $Param{CacheTTL} ) { 2663 $CacheObject = $Kernel::OM->Get('Kernel::System::Cache'); 2664 my $CacheData = $CacheObject->Get( 2665 Type => 'TicketSearch', 2666 Key => $SQLSelect . $SQLFrom . $SQLExt . $Result . $Limit, 2667 ); 2668 2669 if ( defined $CacheData ) { 2670 if ( ref $CacheData eq 'HASH' ) { 2671 return %{$CacheData}; 2672 } 2673 elsif ( ref $CacheData eq 'ARRAY' ) { 2674 return @{$CacheData}; 2675 } 2676 elsif ( ref $CacheData eq '' ) { 2677 return $CacheData; 2678 } 2679 $Kernel::OM->Get('Kernel::System::Log')->Log( 2680 Priority => 'error', 2681 Message => 'Invalid ref ' . ref($CacheData) . '!' 2682 ); 2683 return; 2684 } 2685 } 2686 2687 # database query 2688 my %Tickets; 2689 my @TicketIDs; 2690 my $Count; 2691 2692 return if !$DBObject->Prepare( 2693 SQL => $SQLSelect . $SQLFrom . $SQLExt, 2694 Limit => $Limit 2695 ); 2696 while ( my @Row = $DBObject->FetchrowArray() ) { 2697 $Count = $Row[0]; 2698 $Tickets{ $Row[0] } = $Row[1]; 2699 push @TicketIDs, $Row[0]; 2700 } 2701 2702 # return COUNT 2703 if ( $Result eq 'COUNT' ) { 2704 if ($CacheObject) { 2705 $CacheObject->Set( 2706 Type => 'TicketSearch', 2707 Key => $SQLSelect . $SQLFrom . $SQLExt . $Result . $Limit, 2708 Value => $Count, 2709 TTL => $Param{CacheTTL} || 60 * 4, 2710 ); 2711 } 2712 return $Count; 2713 } 2714 2715 # return HASH 2716 elsif ( $Result eq 'HASH' ) { 2717 if ($CacheObject) { 2718 $CacheObject->Set( 2719 Type => 'TicketSearch', 2720 Key => $SQLSelect . $SQLFrom . $SQLExt . $Result . $Limit, 2721 Value => \%Tickets, 2722 TTL => $Param{CacheTTL} || 60 * 4, 2723 ); 2724 } 2725 return %Tickets; 2726 } 2727 2728 # return ARRAY 2729 else { 2730 if ($CacheObject) { 2731 $CacheObject->Set( 2732 Type => 'TicketSearch', 2733 Key => $SQLSelect . $SQLFrom . $SQLExt . $Result . $Limit, 2734 Value => \@TicketIDs, 2735 TTL => $Param{CacheTTL} || 60 * 4, 2736 ); 2737 } 2738 return @TicketIDs; 2739 } 2740} 2741 2742=head2 TicketCountByAttribute() 2743 2744Returns count of tickets per value for a specific attribute. 2745 2746 my $TicketCount = $TicketObject->TicketCountByAttribute( 2747 Attribute => 'ServiceID', 2748 TicketIDs => [ 1, 2, 3 ], 2749 ); 2750 2751Returns: 2752 2753 $TicketCount = { 2754 Attribute_Value_1 => 1, 2755 Attribute_Value_2 => 3, 2756 ... 2757 }; 2758 2759=cut 2760 2761sub TicketCountByAttribute { 2762 my ( $Self, %Param ) = @_; 2763 2764 if ( !$Param{Attribute} ) { 2765 $Kernel::OM->Get('Kernel::System::Log')->Log( 2766 Priority => 'error', 2767 Message => 'Need Attribute!', 2768 ); 2769 return; 2770 } 2771 2772 # Check supported attributes. 2773 my $Attribute = $Param{Attribute}; 2774 my %AttributeToDatabase = ( 2775 Lock => 'ticket_lock_id', 2776 LockID => 'ticket_lock_id', 2777 Queue => 'queue_id', 2778 QueueID => 'queue_id', 2779 Priority => 'ticket_priority_id', 2780 PriorityID => 'ticket_priority_id', 2781 Service => 'service_id', 2782 ServiceID => 'service_id', 2783 SLA => 'sla_id', 2784 SLAID => 'sla_id', 2785 State => 'ticket_state_id', 2786 StateID => 'ticket_state_id', 2787 Type => 'type_id', 2788 TypeID => 'type_id', 2789 ); 2790 if ( !$AttributeToDatabase{$Attribute} ) { 2791 $Kernel::OM->Get('Kernel::System::Log')->Log( 2792 Priority => 'error', 2793 Message => "No matching database colum found for Attribute '$Attribute'!", 2794 ); 2795 return; 2796 } 2797 my $DatabaseColumn = $AttributeToDatabase{$Attribute}; 2798 2799 # Nothing to do. 2800 return {} if !IsArrayRefWithData( $Param{TicketIDs} ); 2801 my @BindTicketIDs = map { \$_ } @{ $Param{TicketIDs} }; 2802 2803 # Prepare value-type attributes. 2804 my %AttributeValueLookup; 2805 if ( $Attribute eq 'Lock' ) { 2806 %AttributeValueLookup = $Kernel::OM->Get('Kernel::System::Lock')->LockList( UserID => 1 ); 2807 } 2808 elsif ( $Attribute eq 'Queue' ) { 2809 %AttributeValueLookup = $Kernel::OM->Get('Kernel::System::Queue')->QueueList( Valid => 0 ); 2810 } 2811 elsif ( $Attribute eq 'Priority' ) { 2812 %AttributeValueLookup = $Kernel::OM->Get('Kernel::System::Priority')->PriorityList( Valid => 0 ); 2813 } 2814 elsif ( $Attribute eq 'Service' ) { 2815 %AttributeValueLookup = $Kernel::OM->Get('Kernel::System::Service')->ServiceList( 2816 Valid => 0, 2817 UserID => 1, 2818 ); 2819 } 2820 elsif ( $Attribute eq 'SLA' ) { 2821 %AttributeValueLookup = $Kernel::OM->Get('Kernel::System::SLA')->SLAList( 2822 Valid => 0, 2823 UserID => 1, 2824 ); 2825 } 2826 elsif ( $Attribute eq 'State' ) { 2827 %AttributeValueLookup = $Kernel::OM->Get('Kernel::System::State')->StateList( 2828 Valid => 0, 2829 UserID => 1, 2830 ); 2831 } 2832 elsif ( $Attribute eq 'Type' ) { 2833 %AttributeValueLookup = $Kernel::OM->Get('Kernel::System::Type')->TypeList( Valid => 0 ); 2834 } 2835 my $AttributeType = %AttributeValueLookup ? 'Value' : 'ID'; 2836 2837 # Split IN statement with more than 900 elements in more statements combined with OR 2838 # because Oracle doesn't support more than 1000 elements in one IN statement. 2839 my @TicketIDs = @BindTicketIDs; 2840 my @SQLStrings; 2841 while ( scalar @TicketIDs ) { 2842 2843 # Remove section in the array. 2844 my @TicketIDsPart = splice @TicketIDs, 0, 900; 2845 2846 my $TicketIDString = join ',', ('?') x scalar @TicketIDsPart; 2847 2848 # Add new statement. 2849 push @SQLStrings, "id IN ($TicketIDString)"; 2850 } 2851 2852 my $SQLString = join ' OR ', @SQLStrings; 2853 2854 # Get count from database. 2855 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 2856 return if !$DBObject->Prepare( 2857 SQL => 2858 'SELECT COUNT(*), ' . $DatabaseColumn 2859 . ' FROM ticket' 2860 . ' WHERE ' . $SQLString 2861 . ' AND ' . $DatabaseColumn . ' IS NOT NULL' 2862 . ' GROUP BY ' . $DatabaseColumn, 2863 Bind => \@BindTicketIDs, 2864 Limit => 10_000, 2865 ); 2866 my %AttributeCount; 2867 while ( my @Row = $DBObject->FetchrowArray() ) { 2868 $AttributeCount{ $Row[1] } = $Row[0]; 2869 } 2870 2871 # No conversion necessary. 2872 return \%AttributeCount if $AttributeType eq 'ID'; 2873 2874 # Convert database IDs to values, skip entries with unknown value lookup. 2875 my %AttributeCountConverted = map { $AttributeValueLookup{$_} => $AttributeCount{$_} } 2876 grep { $AttributeValueLookup{$_} } sort keys %AttributeCount; 2877 return \%AttributeCountConverted; 2878} 2879 2880=head1 PRIVATE INTERFACE 2881 2882=head2 _TicketHistoryReferenceForSearchArgument 2883 2884Returns the ticket history reference to the given search argument. 2885 2886 my $Self->_TicketHistoryReferenceForSearchArgument( 2887 Argument => '...' # argument name 2888 ); 2889 2890Result 2891 C<undef> - in case the argument is not mapped 2892 string - the ticket history reference name 2893 2894=cut 2895 2896sub _TicketHistoryReferenceForSearchArgument { 2897 my ( $Self, %Param ) = @_; 2898 2899 # Column to TicketHistory table reference map 2900 my %ArgumentTableMap = ( 2901 2902 # Ticket create columns reference. 2903 CreatedStates => 'th0', 2904 CreatedStateIDs => 'th0', 2905 CreatedQueues => 'th0', 2906 CreatedQueueIDs => 'th0', 2907 CreatedPriorities => 'th0', 2908 CreatedPriorityIDs => 'th0', 2909 CreatedTypes => 'th0', 2910 CreatedTypeIDs => 'th0', 2911 CreatedUserIDs => 'th0', 2912 2913 # Ticket change columns reference. 2914 TicketChangeTimeNewerDate => 'th1', 2915 TicketChangeTimeNewerMinutes => 'th1', 2916 TicketChangeTimeOlderDate => 'th1', 2917 TicketChangeTimeOlderMinutes => 'th1', 2918 TicketLastChangeTimeNewerDate => 'th1', 2919 TicketLastChangeTimeNewerMinutes => 'th1', 2920 TicketLastChangeTimeOlderDate => 'th1', 2921 TicketLastChangeTimeOlderMinutes => 'th1', 2922 2923 # Ticket close columns reference. 2924 TicketCloseTimeNewerDate => 'th2', 2925 TicketCloseTimeNewerMinutes => 'th2', 2926 TicketCloseTimeOlderDate => 'th2', 2927 TicketCloseTimeOlderMinutes => 'th2', 2928 TicketLastCloseTimeNewerDate => 'th2', 2929 TicketLastCloseTimeNewerMinutes => 'th2', 2930 TicketLastCloseTimeOlderDate => 'th2', 2931 TicketLastCloseTimeOlderMinutes => 'th2', 2932 ); 2933 2934 my $Argument = $Param{Argument}; 2935 2936 # Check if the column is mapped 2937 my $Table = $ArgumentTableMap{$Argument}; 2938 if ( !$Table ) { 2939 $Kernel::OM->Get('Kernel::System::Log')->Log( 2940 Message => "TicketSearch :: no table_history map for argument '${ Argument }'", 2941 Priority => 'error', 2942 ); 2943 return; 2944 } 2945 2946 return $Table; 2947} 2948 29491; 2950 2951=head1 TERMS AND CONDITIONS 2952 2953This software is part of the OTRS project (L<https://otrs.org/>). 2954 2955This software comes with ABSOLUTELY NO WARRANTY. For details, see 2956the enclosed file COPYING for license information (GPL). If you 2957did not receive this file, see L<https://www.gnu.org/licenses/gpl-3.0.txt>. 2958 2959=cut 2960