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