# --
# Modified version of the work: Copyright (C) 2006-2023 KIX Service Software GmbH, https://www.kixdesk.com
# based on the original work of:
# Copyright (C) 2001-2023 OTRS AG, https://otrs.com/
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file LICENSE for license information (AGPL). If you
# did not receive this file, see https://www.gnu.org/licenses/agpl.txt.
# --
package Kernel::System::Ticket::TicketSearch;
use strict;
use warnings;
our $ObjectManagerDisabled = 1;
=head1 NAME
Kernel::System::Ticket::TicketSearch - ticket search lib
=head1 SYNOPSIS
All ticket search functions.
=over 4
=cut
=item TicketSearch()
To find tickets in your system.
my @TicketIDs = $TicketObject->TicketSearch(
# result (required)
Result => 'ARRAY' || 'HASH' || 'COUNT',
# result limit
Limit => 100,
# Use TicketSearch as a ticket filter on a single ticket,
# or a predefined ticket list
TicketID => 1234,
TicketID => [1234, 1235],
# ticket number (optional) as STRING or as ARRAYREF
TicketNumber => '%123546%',
TicketNumber => ['%123546%', '%123666%'],
# ticket title (optional) as STRING or as ARRAYREF
Title => '%SomeText%',
Title => ['%SomeTest1%', '%SomeTest2%'],
Queues => ['system queue', 'other queue'],
QueueIDs => [1, 42, 512],
# use also sub queues of Queue|Queues in search
UseSubQueues => 0,
# You can use types like normal, ...
Types => ['normal', 'change', 'incident'],
TypeIDs => [3, 4],
# You can use states like new, open, pending reminder, ...
States => ['new', 'open'],
StateIDs => [3, 4],
# (Open|Closed) tickets for all closed or open tickets.
StateType => 'Open',
# You also can use real state types like new, open, closed,
# pending reminder, pending auto, removed and merged.
StateType => ['open', 'new'],
StateTypeIDs => [1, 2, 3],
Priorities => ['1 very low', '2 low', '3 normal'],
PriorityIDs => [1, 2, 3],
Services => ['Service A', 'Service B'],
ServiceIDs => [1, 2, 3],
SLAs => ['SLA A', 'SLA B'],
SLAIDs => [1, 2, 3],
Locks => ['unlock'],
LockIDs => [1, 2, 3],
OwnerIDs => [1, 12, 455, 32]
ResponsibleIDs => [1, 12, 455, 32]
WatchUserIDs => [1, 12, 455, 32]
# CustomerID (optional) as STRING or as ARRAYREF
CustomerID => '123',
CustomerID => ['123', 'ABC'],
# CustomerIDRaw (optional) as STRING or as ARRAYREF
# CustomerID without QueryCondition checking
#The raw value will be used if is set this parameter
CustomerIDRaw => '123 + 345',
CustomerIDRaw => ['123', 'ABC','123 && 456','ABC % efg'],
# CustomerUserLogin (optional) as STRING as ARRAYREF
CustomerUserLogin => 'uid123',
CustomerUserLogin => ['uid123', 'uid777'],
# CustomerUserLoginRaw (optional) as STRING as ARRAYREF
#The raw value will be used if is set this parameter
CustomerUserLoginRaw => 'uid',
CustomerUserLoginRaw => 'uid + 123',
CustomerUserLoginRaw => ['uid - 123', 'uid # 777 + 321'],
# create ticket properties (optional)
CreatedUserIDs => [1, 12, 455, 32]
CreatedTypes => ['normal', 'change', 'incident'],
CreatedTypeIDs => [1, 2, 3],
CreatedPriorities => ['1 very low', '2 low', '3 normal'],
CreatedPriorityIDs => [1, 2, 3],
CreatedStates => ['new', 'open'],
CreatedStateIDs => [3, 4],
CreatedQueues => ['system queue', 'other queue'],
CreatedQueueIDs => [1, 42, 512],
# DynamicFields
# At least one operator must be specified. Operators will be connected with AND,
# values in an operator with OR.
# You can also pass more than one argument to an operator: ['value1', 'value2']
DynamicField_FieldNameX => {
Equals => 123,
Like => 'value*', # "equals" operator with wildcard support
GreaterThan => '2001-01-01 01:01:01',
GreaterThanEquals => '2001-01-01 01:01:01',
SmallerThan => '2002-02-02 02:02:02',
SmallerThanEquals => '2002-02-02 02:02:02',
}
# User ID for searching tickets by ticket flags (defaults to UserID)
TicketFlagUserID => 1,
# search for ticket flags
TicketFlag => {
Seen => 1,
}
# search for ticket flag that is absent, or a different value than the
# one given:
NotTicketFlag => {
Seen => 1,
},
# User ID for searching tickets by article flags (defaults to UserID)
ArticleFlagUserID => 1,
# search for tickets by the presence of flags on articles
ArticleFlag => {
Important => 1,
},
# article stuff (optional)
From => '%spam@example.com%',
To => '%service@example.com%',
Cc => '%client@example.com%',
Subject => '%VIRUS 32%',
Body => '%VIRUS 32%',
# attachment stuff (optional, applies only for ArticleStorageDB)
AttachmentName => '%anyfile.txt%',
# use full article text index if configured (optional, default off)
FullTextIndex => 1,
# article content search (AND or OR for From, To, Cc, Subject and Body) (optional)
ContentSearch => 'AND',
# article content search prefix (for From, To, Cc, Subject and Body) (optional)
ContentSearchPrefix => '*',
# article content search suffix (for From, To, Cc, Subject and Body) (optional)
ContentSearchSuffix => '*',
# content conditions for From,To,Cc,Subject,Body
# Title,CustomerID and CustomerUserLogin (all optional)
ConditionInline => 1,
# articles created more than 60 minutes ago (article older than 60 minutes) (optional)
ArticleCreateTimeOlderMinutes => 60,
# articles created less than 120 minutes ago (article newer than 60 minutes) (optional)
ArticleCreateTimeNewerMinutes => 120,
# articles with create time after ... (article newer than this date) (optional)
ArticleCreateTimeNewerDate => '2006-01-09 00:00:01',
# articles with created time before ... (article older than this date) (optional)
ArticleCreateTimeOlderDate => '2006-01-19 23:59:59',
# tickets created more than 60 minutes ago (ticket older than 60 minutes) (optional)
TicketCreateTimeOlderMinutes => 60,
# tickets created less than 120 minutes ago (ticket newer than 120 minutes) (optional)
TicketCreateTimeNewerMinutes => 120,
# tickets with create time after ... (ticket newer than this date) (optional)
TicketCreateTimeNewerDate => '2006-01-09 00:00:01',
# tickets with created time before ... (ticket older than this date) (optional)
TicketCreateTimeOlderDate => '2006-01-19 23:59:59',
# ticket history entries that created more than 60 minutes ago (optional)
TicketChangeTimeOlderMinutes => 60,
# ticket history entries that created less than 120 minutes ago (optional)
TicketChangeTimeNewerMinutes => 120,
# tickets changed more than 60 minutes ago (optional)
TicketLastChangeTimeOlderMinutes => 60,
# tickets changed less than 120 minutes ago (optional)
TicketLastChangeTimeNewerMinutes => 120,
# tickets with changed time after ... (ticket changed newer than this date) (optional)
TicketLastChangeTimeNewerDate => '2006-01-09 00:00:01',
# tickets with changed time before ... (ticket changed older than this date) (optional)
TicketLastChangeTimeOlderDate => '2006-01-19 23:59:59',
# ticket history entry create time after ... (ticket history entries newer than this date) (optional)
TicketChangeTimeNewerDate => '2006-01-09 00:00:01',
# ticket history entry create time before ... (ticket history entries older than this date) (optional)
TicketChangeTimeOlderDate => '2006-01-19 23:59:59',
# tickets closed more than 60 minutes ago (optional)
TicketCloseTimeOlderMinutes => 60,
# tickets closed less than 120 minutes ago (optional)
TicketCloseTimeNewerMinutes => 120,
# tickets with closed time after ... (ticket closed newer than this date) (optional)
TicketCloseTimeNewerDate => '2006-01-09 00:00:01',
# tickets with closed time before ... (ticket closed older than this date) (optional)
TicketCloseTimeOlderDate => '2006-01-19 23:59:59',
# tickets with pending time of more than 60 minutes ago (optional)
TicketPendingTimeOlderMinutes => 60,
# tickets with pending time of less than 120 minutes ago (optional)
TicketPendingTimeNewerMinutes => 120,
# tickets with pending time after ... (optional)
TicketPendingTimeNewerDate => '2006-01-09 00:00:01',
# tickets with pending time before ... (optional)
TicketPendingTimeOlderDate => '2006-01-19 23:59:59',
# you can use all following escalation options with this four different ways of escalations
# TicketEscalationTime...
# TicketEscalationUpdateTime...
# TicketEscalationResponseTime...
# TicketEscalationSolutionTime...
# ticket escalation time of more than 60 minutes ago (optional)
TicketEscalationTimeOlderMinutes => -60,
# ticket escalation time of less than 120 minutes ago (optional)
TicketEscalationTimeNewerMinutes => -120,
# tickets with escalation time after ... (optional)
TicketEscalationTimeNewerDate => '2006-01-09 00:00:01',
# tickets with escalation time before ... (optional)
TicketEscalationTimeOlderDate => '2006-01-09 23:59:59',
# search in archive (optional)
# if archiving is on, if not specified the search processes unarchived only
# 'y' searches archived tickets, 'n' searches unarchived tickets
# if specified together all tickets are searched
ArchiveFlags => ['y', 'n'],
# OrderBy and SortBy (optional)
OrderBy => 'Down', # Down|Up
SortBy => 'Age', # Created|Owner|Responsible|CustomerID|State|TicketNumber|Queue|Priority|Age|Type|Lock
# Changed|Title|Service|SLA|PendingTime|EscalationTime
# EscalationUpdateTime|EscalationResponseTime|EscalationSolutionTime
# DynamicField_FieldNameX
# OrderBy and SortBy as ARRAY for sub sorting (optional)
OrderBy => ['Down', 'Up'],
SortBy => ['Priority', 'Age'],
# user search (UserID is required)
UserID => 123,
Permission => 'ro' || 'rw',
# customer search (CustomerUserID is required)
CustomerUserID => 123,
Permission => 'ro' || 'rw',
# CacheTTL, cache search result in seconds (optional)
CacheTTL => 60 * 15,
);
Returns:
Result: 'ARRAY'
@TicketIDs = ( 1, 2, 3 );
Result: 'HASH'
%TicketIDs = (
1 => '2010102700001',
2 => '2010102700002',
3 => '2010102700003',
);
Result: 'COUNT'
$TicketIDs = 123;
=cut
sub TicketSearch {
my ( $Self, %Param ) = @_;
my $Result = $Param{Result} || 'HASH';
my $OrderBy = $Param{OrderBy} || 'Down';
my $SortBy = $Param{SortBy} || 'Age';
my $Limit = $Param{Limit} // 10000;
if ( !$Param{ContentSearch} ) {
$Param{ContentSearch} = 'AND';
}
my %SortOptions = (
Owner => 'st.user_id',
Responsible => 'st.responsible_user_id',
CustomerID => 'st.customer_id',
State => 'st.ticket_state_id',
Lock => 'st.ticket_lock_id',
Ticket => 'st.tn',
TicketNumber => 'st.tn',
Title => 'st.title',
FromTitle => 'st.title',
Queue => 'sq.name',
Type => 'st.type_id',
Priority => 'st.ticket_priority_id',
Age => 'st.create_time_unix',
Created => 'st.create_time',
Changed => 'st.change_time',
Service => 'st.service_id',
SLA => 'st.sla_id',
PendingTime => 'st.until_time',
TicketEscalation => 'st.escalation_time',
EscalationTime => 'st.escalation_time',
EscalationUpdateTime => 'st.escalation_update_time',
EscalationResponseTime => 'st.escalation_response_time',
EscalationSolutionTime => 'st.escalation_solution_time',
ID => 'st.id'
);
# check required params
if ( !$Param{UserID} && !$Param{CustomerUserID} ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => 'Need UserID or CustomerUserID params for permission check!',
);
return;
}
# check types of given arguments
ARGUMENT:
for my $Key (
qw(
Types TypeIDs CreatedTypes CreatedTypeIDs States StateIDs CreatedStates CreatedStateIDs StateTypeIDs
Locks LockIDs OwnerIDs ResponsibleIDs CreatedUserIDs Queues QueueIDs CreatedQueues CreatedQueueIDs
Priorities PriorityIDs CreatedPriorities CreatedPriorityIDs Services ServiceIDs SLAs SLAIDs WatchUserIDs
)
) {
next ARGUMENT if !$Param{$Key};
next ARGUMENT if ref $Param{$Key} eq 'ARRAY' && @{ $Param{$Key} };
# log error
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "The given param '$Key' is invalid or an empty array reference!",
);
return;
}
# get database object
my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
# quote id array elements
ARGUMENT:
for my $Key (
qw(
TypeIDs CreatedTypeIDs StateIDs CreatedStateIDs StateTypeIDs LockIDs OwnerIDs ResponsibleIDs CreatedUserIDs
QueueIDs CreatedQueueIDs PriorityIDs CreatedPriorityIDs ServiceIDs SLAIDs WatchUserIDs
)
) {
next ARGUMENT if !$Param{$Key};
# quote elements
for my $Element ( @{ $Param{$Key} } ) {
if ( $Element eq '_NONE_' ) {
$Element = 0;
}
if ( !defined $DBObject->Quote( $Element, 'Integer' ) ) {
# log error
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "The given param '$Element' in '$Key' is invalid!",
);
return;
}
}
}
my $TicketDynamicFields = [];
my $ArticleDynamicFields = [];
my %ValidDynamicFieldParams;
my %TicketDynamicFieldName2Config;
my %ArticleDynamicFieldName2Config;
# Only fetch DynamicField data if a field was requested for searching or sorting
my $ParamCheckString = ( join '', keys %Param ) || '';
if ( ref $Param{SortBy} eq 'ARRAY' ) {
$ParamCheckString .= ( join '', @{ $Param{SortBy} } );
}
elsif ( ref $Param{SortBy} ne 'HASH' ) {
$ParamCheckString .= $Param{SortBy} || '';
}
if ( $ParamCheckString =~ m/DynamicField_/smx ) {
# get dynamic field object
my $DynamicFieldObject = $Kernel::OM->Get('Kernel::System::DynamicField');
# Check all configured ticket dynamic fields
$TicketDynamicFields = $DynamicFieldObject->DynamicFieldListGet(
ObjectType => 'Ticket',
);
for my $DynamicField ( @{$TicketDynamicFields} ) {
$ValidDynamicFieldParams{ "DynamicField_" . $DynamicField->{Name} } = 1;
$TicketDynamicFieldName2Config{ $DynamicField->{Name} } = $DynamicField;
}
# Check all configured article dynamic fields
$ArticleDynamicFields = $DynamicFieldObject->DynamicFieldListGet(
ObjectType => 'Article',
);
for my $DynamicField ( @{$ArticleDynamicFields} ) {
$ValidDynamicFieldParams{ "DynamicField_" . $DynamicField->{Name} } = 1;
$ArticleDynamicFieldName2Config{ $DynamicField->{Name} } = $DynamicField;
}
}
# check sort/order by options
my @SortByArray;
my @OrderByArray;
if ( ref $SortBy eq 'ARRAY' ) {
@SortByArray = @{$SortBy};
@OrderByArray = @{$OrderBy};
}
else {
@SortByArray = ($SortBy);
@OrderByArray = ($OrderBy);
}
# add ID to sort/order
push(@SortByArray, 'ID');
push(@OrderByArray, 'Up');
for my $Count ( 0 .. $#SortByArray ) {
if (
!$SortOptions{ $SortByArray[$Count] }
&& !$ValidDynamicFieldParams{ $SortByArray[$Count] }
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => 'Need valid SortBy (' . $SortByArray[$Count] . ')!',
);
return;
}
if ( $OrderByArray[$Count] ne 'Down' && $OrderByArray[$Count] ne 'Up' ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => 'Need valid OrderBy (' . $OrderByArray[$Count] . ')!',
);
return;
}
}
# create sql
my $SQLSelect;
if ( $Result eq 'COUNT' ) {
$SQLSelect = 'SELECT COUNT(DISTINCT(st.id))';
}
else {
$SQLSelect = 'SELECT st.id, st.tn';
}
my $SQLFrom = ' FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id ';
my $ArticleJoinSQL = $Self->_ArticleIndexQuerySQL( Data => \%Param ) || '';
# sql, use also article table if needed
$SQLFrom .= $ArticleJoinSQL;
# attachment name search
if ( $Param{AttachmentName} ) {
# joins to article and article_attachments are needed, it can not use existing article joins
# otherwise the search will be limited to already matching articles
my $AttachmentJoinSQL = ' INNER JOIN article art_for_att ON st.id = art_for_att.ticket_id'
. ' INNER JOIN article_attachment_search att ON att.article_id = art_for_att.id ';
# SQL, use also article_attachment table if needed
$SQLFrom .= $AttachmentJoinSQL;
}
# use also history tables if required
my $PatternArg0 = '^Created.+?';
my $PatternArg1 = '^TicketChangeTime(Newer|Older)(Date|Minutes)';
my $PatternArg2 = '^TicketCloseTime(Newer|Older)(Date|Minutes)';
my $TableAdded0 = 0;
my $TableAdded1 = 0;
my $TableAdded2 = 0;
ARGUMENT:
for my $Key ( sort keys %Param ) {
if ( $Param{ $Key } ) {
if (
!$TableAdded0
&& $Key =~ /$PatternArg0/
) {
$SQLFrom .= 'INNER JOIN ticket_history th ON st.id = th.ticket_id ';
$TableAdded0 = 1;
}
elsif (
!$TableAdded1
&& $Key =~ /$PatternArg1/
) {
$SQLFrom .= 'INNER JOIN ticket_history th1 ON st.id = th1.ticket_id ';
$TableAdded1 = 1;
}
elsif (
!$TableAdded2
&& $Key =~ /$PatternArg2/
) {
$SQLFrom .= 'INNER JOIN ticket_history th2 ON st.id = th2.ticket_id ';
$TableAdded2 = 1;
}
}
}
# add ticket watcher table
if ( $Param{WatchUserIDs} ) {
$SQLFrom .= 'INNER JOIN ticket_watcher tw ON st.id = tw.ticket_id ';
}
my $SQLExt = ' WHERE 1=1';
# Limit the search to just one (or a list) TicketID (used by the GenericAgent
# to filter for events on single tickets with the job's ticket filter).
if ( $Param{TicketID} ) {
if (
ref( $Param{TicketID} ) eq 'ARRAY'
&& !defined( $Param{TicketID}->[0] )
) {
if ( $Result eq 'COUNT' ) {
return 0;
}
# return HASH
elsif ( $Result eq 'HASH' ) {
my %EmptyHash = ();
return %EmptyHash;
}
# return ARRAY
else {
my @EmptyArray = ();
return @EmptyArray;
}
}
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'st.id',
IDRef => ref( $Param{TicketID} ) && ref( $Param{TicketID} ) eq 'ARRAY'
? $Param{TicketID}
: [ $DBObject->Quote( $Param{TicketID}, 'Integer' ) ],
);
}
# add ticket flag table
if ( $Param{TicketFlag} ) {
my $Index = 1;
for my $Key ( sort keys %{ $Param{TicketFlag} } ) {
$SQLFrom .= "INNER JOIN ticket_flag tf$Index ON st.id = tf$Index.ticket_id ";
$Index++;
}
}
if ( $Param{NotTicketFlag} ) {
my $TicketFlagUserID = $Param{TicketFlagUserID} || $Param{UserID};
return if !defined $TicketFlagUserID;
my $Index = 1;
for my $Key ( sort keys %{ $Param{NotTicketFlag} } ) {
$SQLFrom .= "LEFT JOIN ticket_flag ntf$Index ON st.id = ntf$Index.ticket_id "
. " AND ntf$Index.ticket_key = '" . $DBObject->Quote($Key) . "'"
. " AND ntf$Index.create_by = "
. $DBObject->Quote( $TicketFlagUserID, 'Integer' )
. ' ';
$Index++;
}
}
# add ticket flag table
if ( $Param{ArticleFlag} ) {
my $Index = 1;
if ( !$ArticleJoinSQL ) {
$ArticleJoinSQL = ' INNER JOIN article art ON st.id = art.ticket_id ';
$SQLFrom .= $ArticleJoinSQL;
}
$SQLFrom .= "INNER JOIN article_flag af ON art.id = af.article_id ";
}
# ticket note
if ( $Param{TicketNotes} ) {
$SQLFrom .= ' INNER JOIN kix_ticket_notes ktn ON st.id = ktn.ticket_id ';
}
# ticket checklist
if ( $Param{TicketChecklistState} ) {
$SQLFrom .= "INNER JOIN kix_ticket_checklist ktc ON st.id = ktc.ticket_id ";
}
# current type lookup
if ( $Param{Types} ) {
# get type object
my $TypeObject = $Kernel::OM->Get('Kernel::System::Type');
for my $Type ( @{ $Param{Types} } ) {
# lookup type id
my $TypeID = $TypeObject->TypeLookup(
Type => $Type,
);
return if !$TypeID;
push @{ $Param{TypeIDs} }, $TypeID;
}
}
# type ids
if ( $Param{TypeIDs} ) {
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'st.type_id',
IDRef => $Param{TypeIDs},
);
}
# created types lookup
if ( $Param{CreatedTypes} ) {
# get type object
my $TypeObject = $Kernel::OM->Get('Kernel::System::Type');
for my $Type ( @{ $Param{CreatedTypes} } ) {
# lookup type id
my $TypeID = $TypeObject->TypeLookup(
Type => $Type,
);
return if !$TypeID;
push @{ $Param{CreatedTypeIDs} }, $TypeID;
}
}
# created type ids
if ( $Param{CreatedTypeIDs} ) {
# lookup history type id
my $HistoryTypeID = $Self->HistoryTypeLookup(
Type => 'NewTicket',
);
if ($HistoryTypeID) {
# create sql part
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'th.type_id',
IDRef => $Param{CreatedTypeIDs},
);
$SQLExt .= " AND th.history_type_id = $HistoryTypeID ";
}
}
# current state lookup
if ( $Param{States} ) {
# get state object
my $StateObject = $Kernel::OM->Get('Kernel::System::State');
for my $State ( @{ $Param{States} } ) {
# get state data
my %StateData = $StateObject->StateGet(
Name => $State,
);
return if !%StateData;
push @{ $Param{StateIDs} }, $StateData{ID};
}
}
# state ids
if ( $Param{StateIDs} ) {
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'st.ticket_state_id',
IDRef => $Param{StateIDs},
);
}
# created states lookup
if ( $Param{CreatedStates} ) {
# get state object
my $StateObject = $Kernel::OM->Get('Kernel::System::State');
for my $State ( @{ $Param{CreatedStates} } ) {
# get state data
my %StateData = $StateObject->StateGet(
Name => $State,
);
return if !%StateData;
push @{ $Param{CreatedStateIDs} }, $StateData{ID};
}
}
# created state ids
if ( $Param{CreatedStateIDs} ) {
# lookup history type id
my $HistoryTypeID = $Self->HistoryTypeLookup(
Type => 'NewTicket',
);
if ($HistoryTypeID) {
# create sql part
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'th.state_id',
IDRef => $Param{CreatedStateIDs},
);
$SQLExt .= " AND th.history_type_id = $HistoryTypeID ";
}
}
# current ticket state type
# NOTE: Open and Closed are not valid state types. It's for compat.
# Open -> All states which are grouped as open (new, open, pending, ...)
# Closed -> All states which are grouped as closed (closed successful, closed unsuccessful)
if ( $Param{StateType} && $Param{StateType} eq 'Open' ) {
my @ViewableStateIDs = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType(
Type => 'Viewable',
Result => 'ID',
);
$SQLExt .= " AND st.ticket_state_id IN ( ${\(join ', ', sort @ViewableStateIDs)} ) ";
}
elsif ( $Param{StateType} && $Param{StateType} eq 'Closed' ) {
my @ViewableStateIDs = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType(
Type => 'Viewable',
Result => 'ID',
);
$SQLExt .= " AND st.ticket_state_id NOT IN ( ${\(join ', ', sort @ViewableStateIDs)} ) ";
}
# current ticket state type
elsif ( $Param{StateType} ) {
my @StateIDs = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType(
StateType => $Param{StateType},
Result => 'ID',
);
return if !$StateIDs[0];
$SQLExt .= " AND st.ticket_state_id IN ( ${\(join ', ', sort {$a <=> $b} @StateIDs)} ) ";
}
if ( $Param{StateTypeIDs} ) {
# get state object
my $StateObject = $Kernel::OM->Get('Kernel::System::State');
my %StateTypeList = $StateObject->StateTypeList(
UserID => $Param{UserID} || 1,
);
my @StateTypes = map { $StateTypeList{$_} } @{ $Param{StateTypeIDs} };
my @StateIDs = $StateObject->StateGetStatesByType(
StateType => \@StateTypes,
Result => 'ID',
);
return if !$StateIDs[0];
$SQLExt .= " AND st.ticket_state_id IN ( ${\(join ', ', sort {$a <=> $b} @StateIDs)} ) ";
}
# current lock lookup
if ( $Param{Locks} ) {
for my $Lock ( @{ $Param{Locks} } ) {
# lookup lock id
my $LockID = $Kernel::OM->Get('Kernel::System::Lock')->LockLookup(
Lock => $Lock,
);
return if !$LockID;
push @{ $Param{LockIDs} }, $LockID;
}
}
# lock ids
if ( $Param{LockIDs} ) {
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'st.ticket_lock_id',
IDRef => $Param{LockIDs},
);
}
# current owner user ids
if ( $Param{OwnerIDs} ) {
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'st.user_id',
IDRef => $Param{OwnerIDs},
);
}
# current responsible user ids
if ( $Param{ResponsibleIDs} ) {
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'st.responsible_user_id',
IDRef => $Param{ResponsibleIDs},
);
}
# created user ids
if ( $Param{CreatedUserIDs} ) {
# lookup history type id
my $HistoryTypeID = $Self->HistoryTypeLookup(
Type => 'NewTicket',
);
if ($HistoryTypeID) {
# create sql part
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'th.create_by',
IDRef => $Param{CreatedUserIDs},
);
$SQLExt .= " AND th.history_type_id = $HistoryTypeID ";
}
}
# current queue lookup
if ( $Param{Queues} ) {
# get queue object
my $QueueObject = $Kernel::OM->Get('Kernel::System::Queue');
for my $Queue ( @{ $Param{Queues} } ) {
# lookup queue id
my $QueueID = $QueueObject->QueueLookup(
Queue => $Queue,
);
return if !$QueueID;
push @{ $Param{QueueIDs} }, $QueueID;
}
}
# current sub queue ids
if ( $Param{UseSubQueues} && $Param{QueueIDs} ) {
# get queue object
my $QueueObject = $Kernel::OM->Get('Kernel::System::Queue');
my @SubQueueIDs;
my %Queues = $QueueObject->GetAllQueues();
for my $QueueID ( @{ $Param{QueueIDs} } ) {
my $Queue = $QueueObject->QueueLookup( QueueID => $QueueID );
for my $QueuesID ( sort keys %Queues ) {
if ( $Queues{$QueuesID} =~ /^\Q$Queue\E::/i ) {
push @SubQueueIDs, $QueuesID;
}
}
}
push @{ $Param{QueueIDs} }, @SubQueueIDs;
}
# current queue ids
if ( $Param{QueueIDs} ) {
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'st.queue_id',
IDRef => $Param{QueueIDs},
);
}
# created queue lookup
if ( $Param{CreatedQueues} ) {
# get queue object
my $QueueObject = $Kernel::OM->Get('Kernel::System::Queue');
for my $Queue ( @{ $Param{CreatedQueues} } ) {
# lookup queue id
my $QueueID = $QueueObject->QueueLookup(
Queue => $Queue,
);
return if !$QueueID;
push @{ $Param{CreatedQueueIDs} }, $QueueID;
}
}
# created queue ids
if ( $Param{CreatedQueueIDs} ) {
# lookup history type id
my $HistoryTypeID = $Self->HistoryTypeLookup(
Type => 'NewTicket',
);
if ($HistoryTypeID) {
# create sql part
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'th.queue_id',
IDRef => $Param{CreatedQueueIDs},
);
$SQLExt .= " AND th.history_type_id = $HistoryTypeID ";
}
}
my %GroupList;
# user groups
if ( $Param{UserID} && $Param{UserID} != 1 ) {
# get users groups
%GroupList = $Kernel::OM->Get('Kernel::System::Group')->PermissionUserGet(
UserID => $Param{UserID},
Type => $Param{Permission} || 'ro',
);
# return if we have no permissions
return if !%GroupList;
}
# customer groups
elsif ( $Param{CustomerUserID} ) {
%GroupList = $Kernel::OM->Get('Kernel::System::CustomerGroup')->GroupMemberList(
UserID => $Param{CustomerUserID},
Type => $Param{Permission} || 'ro',
Result => 'HASH',
);
# return if we have no permissions
return if !%GroupList;
# get all customer ids
$SQLExt .= ' AND (';
my @CustomerIDs = $Kernel::OM->Get('Kernel::System::CustomerUser')->CustomerIDs(
User => $Param{CustomerUserID},
);
if (@CustomerIDs) {
my $Lower = '';
if ( $DBObject->GetDatabaseFunction('CaseSensitive') ) {
$Lower = 'LOWER';
}
$SQLExt .= "$Lower(st.customer_id) IN (";
my $Exists = 0;
for (@CustomerIDs) {
if ($Exists) {
$SQLExt .= ', ';
}
else {
$Exists = 1;
}
$SQLExt .= "$Lower('" . $DBObject->Quote($_) . "')";
}
$SQLExt .= ') OR ';
}
# get all own tickets
my $CustomerUserIDQuoted = $DBObject->Quote( $Param{CustomerUserID} );
$SQLExt .= "st.customer_user_id = '$CustomerUserIDQuoted') ";
}
# add group ids to sql string
if (%GroupList) {
my $GroupIDString = join ',', sort keys %GroupList;
$SQLExt .= " AND sq.group_id IN ($GroupIDString) ";
}
# current priority lookup
if ( $Param{Priorities} ) {
# get priority object
my $PriorityObject = $Kernel::OM->Get('Kernel::System::Priority');
for my $Priority ( @{ $Param{Priorities} } ) {
# lookup priority id
my $PriorityID = $PriorityObject->PriorityLookup(
Priority => $Priority,
);
return if !$PriorityID;
push @{ $Param{PriorityIDs} }, $PriorityID;
}
}
# priority ids
if ( $Param{PriorityIDs} ) {
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'st.ticket_priority_id',
IDRef => $Param{PriorityIDs},
);
}
# created priority lookup
if ( $Param{CreatedPriorities} ) {
# get priority object
my $PriorityObject = $Kernel::OM->Get('Kernel::System::Priority');
for my $Priority ( @{ $Param{CreatedPriorities} } ) {
# lookup priority id
my $PriorityID = $PriorityObject->PriorityLookup(
Priority => $Priority,
);
return if !$PriorityID;
push @{ $Param{CreatedPriorityIDs} }, $PriorityID;
}
}
# created priority ids
if ( $Param{CreatedPriorityIDs} ) {
# lookup history type id
my $HistoryTypeID = $Self->HistoryTypeLookup(
Type => 'NewTicket',
);
if ($HistoryTypeID) {
# create sql part
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'th.priority_id',
IDRef => $Param{CreatedPriorityIDs},
);
$SQLExt .= " AND th.history_type_id = $HistoryTypeID ";
}
}
# current service lookup
if ( $Param{Services} ) {
# get service object
my $ServiceObject = $Kernel::OM->Get('Kernel::System::Service');
for my $Service ( @{ $Param{Services} } ) {
# lookup service id
my $ServiceID = $ServiceObject->ServiceLookup(
Name => $Service,
);
return if !$ServiceID;
push @{ $Param{ServiceIDs} }, $ServiceID;
}
}
# service ids
if ( $Param{ServiceIDs} ) {
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'st.service_id',
IDRef => $Param{ServiceIDs},
);
}
# current sla lookup
if ( $Param{SLAs} ) {
# get sla object
my $SLAObject = $Kernel::OM->Get('Kernel::System::SLA');
for my $SLA ( @{ $Param{SLAs} } ) {
# lookup sla id
my $SLAID = $SLAObject->SLALookup(
Name => $SLA,
);
return if !$SLAID;
push @{ $Param{SLAIDs} }, $SLAID;
}
}
# sla ids
if ( $Param{SLAIDs} ) {
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'st.sla_id',
IDRef => $Param{SLAIDs},
);
}
# watch user ids
if ( $Param{WatchUserIDs} ) {
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'tw.user_id',
IDRef => $Param{WatchUserIDs},
);
}
# add ticket flag extension
if ( $Param{TicketFlag} ) {
my $TicketFlagUserID = $Param{TicketFlagUserID} || $Param{UserID};
return if !defined $TicketFlagUserID;
my $Index = 1;
for my $Key ( sort keys %{ $Param{TicketFlag} } ) {
my $Value = $Param{TicketFlag}->{$Key};
return if !defined $Value;
# neccessary to fix a bug caused by KIXTemplateWorkflows
if (
$Param{TicketFlagUserIDs}
&& ref( $Param{TicketFlagUserIDs} ) eq 'HASH'
&& $Param{TicketFlagUserIDs}->{$Key}
) {
$TicketFlagUserID = $Param{TicketFlagUserIDs}->{$Key};
}
return if !defined $TicketFlagUserID;
$SQLExt .= " AND tf$Index.ticket_key = '" . $DBObject->Quote($Key) . "'";
$SQLExt .= " AND tf$Index.ticket_value = '" . $DBObject->Quote($Value) . "'";
$SQLExt .= " AND tf$Index.create_by = "
. $DBObject->Quote( $TicketFlagUserID, 'Integer' );
$Index++;
}
}
if ( $Param{NotTicketFlag} ) {
my $Index = 1;
for my $Key ( sort keys %{ $Param{NotTicketFlag} } ) {
my $Value = $Param{NotTicketFlag}->{$Key};
return if !defined $Value;
$SQLExt .= " AND (ntf$Index.ticket_value IS NULL "
. "OR ntf$Index.ticket_value <> '" . $DBObject->Quote($Value) . "')";
$Index++;
}
}
# add article flag extension
if ( $Param{ArticleFlag} ) {
my $ArticleFlagUserID = $Param{ArticleFlagUserID} || $Param{UserID};
return if !defined $ArticleFlagUserID;
$SQLExt .=
" AND af.article_key = '" . $DBObject->Quote( $Param{ArticleFlag} ) . "'";
$SQLExt .=
" AND af.create_by = " . $DBObject->Quote($ArticleFlagUserID);
}
# ticket note
if ( $Param{TicketNotes} ) {
my $SearchPattern = $Param{ContentSearchPrefix}.$Param{TicketNotes}.$Param{ContentSearchSuffix};
$SearchPattern =~ s/\*/%/g;
$SQLExt .= " AND LOWER(ktn.note) LIKE LOWER('" . $DBObject->Quote( $SearchPattern ) . "')";
}
# ticket checklist
if ( $Param{TicketChecklistState} ) {
my @ChecklistItemStates = @{ $Param{TicketChecklistState} };
$SQLFrom .= " AND (";
my $Counter = 0;
for my $ChecklistItemState ( @ChecklistItemStates ) {
$ChecklistItemStates[$Counter] = " ktc.state LIKE LOWER('" . $DBObject->Quote( $ChecklistItemState ) . "')";
$Counter++;
}
$SQLFrom .= join ' OR ', @ChecklistItemStates;
$SQLFrom .= ")";
}
# other ticket stuff
my %FieldSQLMap = (
TicketNumber => 'st.tn',
Title => 'st.title',
CustomerID => 'st.customer_id',
CustomerIDRaw => 'st.customer_id',
CustomerUserLogin => 'st.customer_user_id',
CustomerUserLoginRaw => 'st.customer_user_id',
);
ATTRIBUTE:
for my $Key ( sort keys %FieldSQLMap ) {
next ATTRIBUTE if !defined $Param{$Key};
next ATTRIBUTE if ( ( $Key eq 'CustomerID' ) && ( defined $Param{CustomerIDRaw} ) );
next ATTRIBUTE
if ( ( $Key eq 'CustomerUserLogin' ) && ( defined $Param{CustomerUserLoginRaw} ) );
# if it's no ref, put it to array ref
if ( ref $Param{$Key} eq '' ) {
$Param{$Key} = [ $Param{$Key} ];
}
# proccess array ref
my $Used = 0;
VALUE:
for my $Value ( @{ $Param{$Key} } ) {
next VALUE if !$Value;
# replace wild card search
if (
$Key ne 'CustomerIDRaw'
&& $Key ne 'CustomerUserLoginRaw'
) {
$Value =~ s/\*/%/gi;
}
# check search attribute, we do not need to search for *
next VALUE if $Value =~ /^\%{1,3}$/;
if ( !$Used ) {
$SQLExt .= ' AND (';
$Used = 1;
}
else {
$SQLExt .= ' OR ';
}
if ( $Value eq '_NONE_' ) {
$SQLExt
.= " $FieldSQLMap{$Key} IS NULL OR"
. " $FieldSQLMap{$Key} LIKE ''";
next;
}
# add * to prefix/suffix on title search
my %ConditionFocus;
if ( $Param{ConditionInline} && $Key eq 'Title' ) {
$ConditionFocus{Extended} = 1;
if ( $Param{ContentSearchPrefix} ) {
$ConditionFocus{SearchPrefix} = $Param{ContentSearchPrefix};
}
if ( $Param{ContentSearchSuffix} ) {
$ConditionFocus{SearchSuffix} = $Param{ContentSearchSuffix};
}
}
if ( $Key eq 'CustomerIDRaw' || $Key eq 'CustomerUserLoginRaw' ) {
$SQLExt .= " $FieldSQLMap{$Key}= '" . $DBObject->Quote($Value) . "'";
}
else {
# use search condition extension
$SQLExt .= $DBObject->QueryCondition(
Key => $FieldSQLMap{$Key},
Value => $Value,
%ConditionFocus,
);
}
}
if ($Used) {
$SQLExt .= ')';
}
}
# search article attributes
my $ArticleIndexSQLExt = $Self->_ArticleIndexQuerySQLExt( Data => \%Param );
$SQLExt .= $ArticleIndexSQLExt;
my %CustomerArticleTypes;
my @CustomerArticleTypeIDs;
if ( $Param{CustomerUserID} ) {
%CustomerArticleTypes = $Self->ArticleTypeList(
Result => 'HASH',
Type => 'Customer',
);
@CustomerArticleTypeIDs = keys %CustomerArticleTypes;
}
# restrict search from customers to only customer articles
if ( $Param{CustomerUserID} && $ArticleIndexSQLExt ) {
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'art.article_type_id',
IDRef => \@CustomerArticleTypeIDs,
);
}
# attachment name search
if ( $Param{AttachmentName} ) {
$SQLExt .= ' AND ';
# replace wild card search
my $Key = 'att.filename';
my $Value = lc $Param{AttachmentName};
$Value =~ s/\*/%/gi;
# use search condition extension
$SQLExt .= $DBObject->QueryCondition(
Key => $Key,
Value => $Value,
SearchPrefix => $Param{ContentSearchPrefix},
SearchSuffix => $Param{ContentSearchSuffix},
CaseSensitive => 1,
);
# restrict search from customers to only customer articles
if ( $Param{CustomerUserID} ) {
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'art_for_att.article_type_id',
IDRef => \@CustomerArticleTypeIDs,
);
}
}
# Remember already joined tables for sorting.
my %DynamicFieldJoinTables;
my $DynamicFieldJoinCounter = 1;
# get dynamic field backend object
my $DynamicFieldBackendObject = $Kernel::OM->Get('Kernel::System::DynamicField::Backend');
DYNAMIC_FIELD:
for my $DynamicField ( @{$TicketDynamicFields}, @{$ArticleDynamicFields} ) {
my $SearchParam = $Param{ "DynamicField_" . $DynamicField->{Name} };
next DYNAMIC_FIELD if ( !$SearchParam );
next DYNAMIC_FIELD if ( ref $SearchParam ne 'HASH' );
my $NeedJoin;
for my $Operator ( sort keys %{$SearchParam} ) {
my @SearchParams = ( ref $SearchParam->{$Operator} eq 'ARRAY' )
? @{ $SearchParam->{$Operator} }
: ( $SearchParam->{$Operator} );
my $SQLExtSub = ' AND (';
my $Counter = 0;
TEXT:
for my $Text (@SearchParams) {
next TEXT if ( !defined $Text || $Text eq '' );
$Text =~ s/\*/%/gi;
# check search attribute, we do not need to search for *
next TEXT if $Text =~ /^\%{1,3}$/;
# validate data type
my $ValidateSuccess = $DynamicFieldBackendObject->ValueValidate(
DynamicFieldConfig => $DynamicField,
Value => $Text,
SearchValidation => 1,
UserID => $Param{UserID} || 1,
);
if ( !$ValidateSuccess ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid value '"
. $Text
. "' on field '"
. $DynamicField->{Name}
. "'!",
);
return;
}
if ($Counter) {
$SQLExtSub .= ' OR ';
}
$SQLExtSub .= $DynamicFieldBackendObject->SearchSQLGet(
DynamicFieldConfig => $DynamicField,
TableAlias => "dfv$DynamicFieldJoinCounter",
Operator => $Operator,
SearchTerm => $Text,
);
$Counter++;
}
$SQLExtSub .= ')';
if ($Counter) {
$SQLExt .= $SQLExtSub;
$NeedJoin = 1;
}
}
if ($NeedJoin) {
if ( $DynamicField->{ObjectType} eq 'Ticket' ) {
# Join the table for this dynamic field
$SQLFrom .= "INNER JOIN dynamic_field_value dfv$DynamicFieldJoinCounter"
. " ON (st.id = dfv$DynamicFieldJoinCounter.$DynamicField->{IdentifierDBAttribute}"
. " AND dfv$DynamicFieldJoinCounter.field_id = " .
$DBObject->Quote( $DynamicField->{ID}, 'Integer' ) . ") ";
}
elsif ( $DynamicField->{ObjectType} eq 'Article' ) {
if ( !$ArticleJoinSQL ) {
$ArticleJoinSQL = ' INNER JOIN article art ON st.id = art.ticket_id ';
$SQLFrom .= $ArticleJoinSQL;
}
$SQLFrom .= "INNER JOIN dynamic_field_value dfv$DynamicFieldJoinCounter"
. " ON (art.id = dfv$DynamicFieldJoinCounter.$DynamicField->{IdentifierDBAttribute}"
. " AND dfv$DynamicFieldJoinCounter.field_id = " .
$DBObject->Quote( $DynamicField->{ID}, 'Integer' ) . ") ";
}
$DynamicFieldJoinTables{ $DynamicField->{Name} } = "dfv$DynamicFieldJoinCounter";
$DynamicFieldJoinCounter++;
}
}
# get time object
my $TimeObject = $Kernel::OM->Get('Kernel::System::Time');
# remember current time to prevent searches for future timestamps
my $CurrentSystemTime = $TimeObject->SystemTime();
# get articles created older/newer than x minutes or older/newer than a date
my %ArticleTime = (
ArticleCreateTime => 'art.incoming_time',
);
for my $Key ( sort keys %ArticleTime ) {
# get articles created older than x minutes
if ( defined $Param{ $Key . 'OlderMinutes' } ) {
$Param{ $Key . 'OlderMinutes' } ||= 0;
my $Time = $TimeObject->SystemTime()
- ( $Param{ $Key . 'OlderMinutes' } * 60 );
$SQLExt .= " AND $ArticleTime{$Key} <= '$Time'";
}
# get articles created newer than x minutes
if ( defined $Param{ $Key . 'NewerMinutes' } ) {
$Param{ $Key . 'NewerMinutes' } ||= 0;
my $Time = $TimeObject->SystemTime()
- ( $Param{ $Key . 'NewerMinutes' } * 60 );
$SQLExt .= " AND $ArticleTime{$Key} >= '$Time'";
}
# get articles created older than xxxx-xx-xx xx:xx date
my $CompareOlderNewerDate;
if ( $Param{ $Key . 'OlderDate' } ) {
my $SystemTime;
if ( $Param{ $Key . 'OlderDate' } =~ /^(\d{4})-(\d{1,2})-(\d{1,2}) (\d{1,2}):(\d{1,2}):(\d{1,2})$/ ) {
# convert param date to system time
$SystemTime = $TimeObject->Date2SystemTime(
Year => $1,
Month => $2,
Day => $3,
Hour => $4,
Minute => $5,
Second => $6,
);
}
else {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '" . $Param{ $Key . 'OlderDate' } . "'!",
);
return;
}
if ( !$SystemTime ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{ $Key . 'OlderDate' } . "'!",
);
return;
}
$CompareOlderNewerDate = $SystemTime;
$SQLExt .= " AND $ArticleTime{$Key} <= '" . $SystemTime . "'";
}
# get articles created newer than xxxx-xx-xx xx:xx date
if ( $Param{ $Key . 'NewerDate' } ) {
my $SystemTime;
if ( $Param{ $Key . 'NewerDate' } =~ /^(\d{4})-(\d{1,2})-(\d{1,2}) (\d{1,2}):(\d{1,2}):(\d{1,2})$/ ) {
# convert param date to system time
$SystemTime = $TimeObject->Date2SystemTime(
Year => $1,
Month => $2,
Day => $3,
Hour => $4,
Minute => $5,
Second => $6,
);
}
else {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '" . $Param{ $Key . 'NewerDate' } . "'!",
);
return;
}
if ( !$SystemTime ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{ $Key . 'NewerDate' } . "'!",
);
return;
}
# don't execute queries if newer date is after current date
return if $SystemTime > $CurrentSystemTime;
# don't execute queries if older/newer date restriction show now valid timeframe
return if $CompareOlderNewerDate && $SystemTime > $CompareOlderNewerDate;
$SQLExt .= " AND $ArticleTime{$Key} >= '" . $SystemTime . "'";
}
}
# get tickets created/escalated older/newer than x minutes
my %TicketTime = (
TicketCreateTime => 'st.create_time_unix',
TicketEscalationTime => 'st.escalation_time',
TicketEscalationUpdateTime => 'st.escalation_update_time',
TicketEscalationResponseTime => 'st.escalation_response_time',
TicketEscalationSolutionTime => 'st.escalation_solution_time',
);
for my $Key ( sort keys %TicketTime ) {
# get tickets created or escalated older than x minutes
if ( defined $Param{ $Key . 'OlderMinutes' } ) {
$Param{ $Key . 'OlderMinutes' } ||= 0;
# exclude tickets with no escalation
if ( $Key =~ m{ \A TicketEscalation }xms ) {
$SQLExt .= " AND $TicketTime{$Key} != 0";
}
my $Time = $TimeObject->SystemTime();
$Time -= ( $Param{ $Key . 'OlderMinutes' } * 60 );
$SQLExt .= " AND $TicketTime{$Key} <= $Time";
}
# get tickets created or escalated newer than x minutes
if ( defined $Param{ $Key . 'NewerMinutes' } ) {
$Param{ $Key . 'NewerMinutes' } ||= 0;
# exclude tickets with no escalation
if ( $Key =~ m{ \A TicketEscalation }xms ) {
$SQLExt .= " AND $TicketTime{$Key} != 0";
}
my $Time = $TimeObject->SystemTime();
$Time -= ( $Param{ $Key . 'NewerMinutes' } * 60 );
$SQLExt .= " AND $TicketTime{$Key} >= $Time";
}
}
# get tickets created/escalated older/newer than xxxx-xx-xx xx:xx date
for my $Key ( sort keys %TicketTime ) {
# get tickets created/escalated older than xxxx-xx-xx xx:xx date
my $CompareOlderNewerDate;
if ( $Param{ $Key . 'OlderDate' } ) {
# check time format
if (
$Param{ $Key . 'OlderDate' } !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '" . $Param{ $Key . 'OlderDate' } . "'!",
);
return;
}
# exclude tickets with no escalation
if ( $Key =~ m{ \A TicketEscalation }xms ) {
$SQLExt .= " AND $TicketTime{$Key} != 0";
}
my $Time = $TimeObject->TimeStamp2SystemTime(
String => $Param{ $Key . 'OlderDate' },
);
if ( !$Time ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{ $Key . 'OlderDate' } . "'!",
);
return;
}
$CompareOlderNewerDate = $Time;
$SQLExt .= " AND $TicketTime{$Key} <= $Time";
}
# get tickets created/escalated newer than xxxx-xx-xx xx:xx date
if ( $Param{ $Key . 'NewerDate' } ) {
if (
$Param{ $Key . 'NewerDate' } !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '" . $Param{ $Key . 'NewerDate' } . "'!",
);
return;
}
# exclude tickets with no escalation
if ( $Key =~ m{ \A TicketEscalation }xms ) {
$SQLExt .= " AND $TicketTime{$Key} != 0";
}
my $Time = $TimeObject->TimeStamp2SystemTime(
String => $Param{ $Key . 'NewerDate' },
);
if ( !$Time ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{ $Key . 'NewerDate' } . "'!",
);
return;
}
# don't execute queries if newer date is after current date
return if $Time > $CurrentSystemTime;
# don't execute queries if older/newer date restriction show now valid timeframe
return if $CompareOlderNewerDate && $Time > $CompareOlderNewerDate;
$SQLExt .= " AND $TicketTime{$Key} >= $Time";
}
}
# get tickets changed older than x minutes
if ( defined $Param{TicketChangeTimeOlderMinutes} ) {
$Param{TicketChangeTimeOlderMinutes} ||= 0;
my $TimeStamp = $TimeObject->SystemTime();
$TimeStamp -= ( $Param{TicketChangeTimeOlderMinutes} * 60 );
$Param{TicketChangeTimeOlderDate} = $TimeObject->SystemTime2TimeStamp(
SystemTime => $TimeStamp,
);
}
# get tickets changed newer than x minutes
if ( defined $Param{TicketChangeTimeNewerMinutes} ) {
$Param{TicketChangeTimeNewerMinutes} ||= 0;
my $TimeStamp = $TimeObject->SystemTime();
$TimeStamp -= ( $Param{TicketChangeTimeNewerMinutes} * 60 );
$Param{TicketChangeTimeNewerDate} = $TimeObject->SystemTime2TimeStamp(
SystemTime => $TimeStamp,
);
}
# get tickets based on ticket history changed older than xxxx-xx-xx xx:xx date
my $CompareChangeTimeOlderNewerDate;
if ( $Param{TicketChangeTimeOlderDate} ) {
# check time format
if (
$Param{TicketChangeTimeOlderDate} !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '$Param{TicketChangeTimeOlderDate}'!",
);
return;
}
my $Time = $TimeObject->TimeStamp2SystemTime(
String => $Param{TicketChangeTimeOlderDate},
);
if ( !$Time ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{TicketChangeTimeOlderDate} . "'!",
);
return;
}
$CompareChangeTimeOlderNewerDate = $Time;
$SQLExt .= " AND th1.create_time <= '"
. $DBObject->Quote( $Param{TicketChangeTimeOlderDate} ) . "'";
}
# get tickets based on ticket history changed newer than xxxx-xx-xx xx:xx date
if ( $Param{TicketChangeTimeNewerDate} ) {
if (
$Param{TicketChangeTimeNewerDate} !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '$Param{TicketChangeTimeNewerDate}'!",
);
return;
}
my $Time = $TimeObject->TimeStamp2SystemTime(
String => $Param{TicketChangeTimeNewerDate},
);
if ( !$Time ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{TicketChangeTimeNewerDate} . "'!",
);
return;
}
# don't execute queries if newer date is after current date
return if $Time > $CurrentSystemTime;
# don't execute queries if older/newer date restriction show now valid timeframe
return if $CompareChangeTimeOlderNewerDate && $Time > $CompareChangeTimeOlderNewerDate;
$SQLExt .= " AND th1.create_time >= '"
. $DBObject->Quote( $Param{TicketChangeTimeNewerDate} ) . "'";
}
# get tickets changed older than x minutes
if ( defined $Param{TicketLastChangeTimeOlderMinutes} ) {
$Param{TicketLastChangeTimeOlderMinutes} ||= 0;
my $TimeStamp = $TimeObject->SystemTime();
$TimeStamp -= ( $Param{TicketLastChangeTimeOlderMinutes} * 60 );
$Param{TicketLastChangeTimeOlderDate} = $TimeObject->SystemTime2TimeStamp(
SystemTime => $TimeStamp,
);
}
# get tickets changed newer than x minutes
if ( defined $Param{TicketLastChangeTimeNewerMinutes} ) {
$Param{TicketLastChangeTimeNewerMinutes} ||= 0;
my $TimeStamp = $TimeObject->SystemTime();
$TimeStamp -= ( $Param{TicketLastChangeTimeNewerMinutes} * 60 );
$Param{TicketLastChangeTimeNewerDate} = $TimeObject->SystemTime2TimeStamp(
SystemTime => $TimeStamp,
);
}
# get tickets changed older than xxxx-xx-xx xx:xx date
my $CompareLastChangeTimeOlderNewerDate;
if ( $Param{TicketLastChangeTimeOlderDate} ) {
# check time format
if (
$Param{TicketLastChangeTimeOlderDate} !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '$Param{TicketLastChangeTimeOlderDate}'!",
);
return;
}
my $Time = $TimeObject->TimeStamp2SystemTime(
String => $Param{TicketLastChangeTimeOlderDate},
);
if ( !$Time ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{TicketLastChangeTimeOlderDate} . "'!",
);
return;
}
$CompareLastChangeTimeOlderNewerDate = $Time;
$SQLExt .= " AND st.change_time <= '"
. $DBObject->Quote( $Param{TicketLastChangeTimeOlderDate} ) . "'";
}
# get tickets changed newer than xxxx-xx-xx xx:xx date
if ( $Param{TicketLastChangeTimeNewerDate} ) {
if (
$Param{TicketLastChangeTimeNewerDate} !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '$Param{TicketLastChangeTimeNewerDate}'!",
);
return;
}
my $Time = $TimeObject->TimeStamp2SystemTime(
String => $Param{TicketLastChangeTimeNewerDate},
);
if ( !$Time ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{TicketLastChangeTimeNewerDate} . "'!",
);
return;
}
# don't execute queries if newer date is after current date
return if $Time > $CurrentSystemTime;
# don't execute queries if older/newer date restriction show now valid timeframe
return
if $CompareLastChangeTimeOlderNewerDate && $Time > $CompareLastChangeTimeOlderNewerDate;
$SQLExt .= " AND st.change_time >= '"
. $DBObject->Quote( $Param{TicketLastChangeTimeNewerDate} ) . "'";
}
# get tickets closed older than x minutes
if ( defined $Param{TicketCloseTimeOlderMinutes} ) {
$Param{TicketCloseTimeOlderMinutes} ||= 0;
my $TimeStamp = $TimeObject->SystemTime();
$TimeStamp -= ( $Param{TicketCloseTimeOlderMinutes} * 60 );
$Param{TicketCloseTimeOlderDate} = $TimeObject->SystemTime2TimeStamp(
SystemTime => $TimeStamp,
);
}
# get tickets closed newer than x minutes
if ( defined $Param{TicketCloseTimeNewerMinutes} ) {
$Param{TicketCloseTimeNewerMinutes} ||= 0;
my $TimeStamp = $TimeObject->SystemTime();
$TimeStamp -= ( $Param{TicketCloseTimeNewerMinutes} * 60 );
$Param{TicketCloseTimeNewerDate} = $TimeObject->SystemTime2TimeStamp(
SystemTime => $TimeStamp,
);
}
# get tickets closed older than xxxx-xx-xx xx:xx date
my $CompareCloseTimeOlderNewerDate;
if ( $Param{TicketCloseTimeOlderDate} ) {
# check time format
if (
$Param{TicketCloseTimeOlderDate} !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '$Param{TicketCloseTimeOlderDate}'!",
);
return;
}
my $Time = $TimeObject->TimeStamp2SystemTime(
String => $Param{TicketCloseTimeOlderDate},
);
if ( !$Time ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{TicketCloseTimeOlderDate} . "'!",
);
return;
}
$CompareCloseTimeOlderNewerDate = $Time;
# get close state ids
my @List = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType(
StateType => ['closed'],
Result => 'ID',
);
my @StateID = ( $Self->HistoryTypeLookup( Type => 'NewTicket' ) );
push( @StateID, $Self->HistoryTypeLookup( Type => 'StateUpdate' ) );
if (@StateID) {
$SQLExt .= " AND th2.history_type_id IN (${\(join ', ', sort @StateID)}) AND "
. " th2.state_id IN (${\(join ', ', sort @List)}) AND "
. "th2.create_time <= '"
. $DBObject->Quote( $Param{TicketCloseTimeOlderDate} ) . "'";
}
}
# get tickets closed newer than xxxx-xx-xx xx:xx date
if ( $Param{TicketCloseTimeNewerDate} ) {
if (
$Param{TicketCloseTimeNewerDate} !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '$Param{TicketCloseTimeNewerDate}'!",
);
return;
}
my $Time = $TimeObject->TimeStamp2SystemTime(
String => $Param{TicketCloseTimeNewerDate},
);
if ( !$Time ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{TicketCloseTimeNewerDate} . "'!",
);
return;
}
# don't execute queries if newer date is after current date
return if $Time > $CurrentSystemTime;
# don't execute queries if older/newer date restriction show now valid timeframe
return if $CompareCloseTimeOlderNewerDate && $Time > $CompareCloseTimeOlderNewerDate;
# get close state ids
my @List = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType(
StateType => ['closed'],
Result => 'ID',
);
my @StateID = ( $Self->HistoryTypeLookup( Type => 'NewTicket' ) );
push( @StateID, $Self->HistoryTypeLookup( Type => 'StateUpdate' ) );
if (@StateID) {
$SQLExt .= " AND th2.history_type_id IN (${\(join ', ', sort @StateID)}) AND "
. " th2.state_id IN (${\(join ', ', sort @List)}) AND "
. " th2.create_time >= '"
. $DBObject->Quote( $Param{TicketCloseTimeNewerDate} ) . "'";
}
}
# check if only pending states are used
if (
defined $Param{TicketPendingTimeOlderMinutes}
|| defined $Param{TicketPendingTimeNewerMinutes}
|| $Param{TicketPendingTimeOlderDate}
|| $Param{TicketPendingTimeNewerDate}
) {
# get pending states
my @PendingAutoStateIDs = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType(
Type => 'PendingAuto',
Result => 'ID',
);
my @PendingReminderStateIDs = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType(
Type => 'PendingReminder',
Result => 'ID',
);
my @PendingsStateIDs = (@PendingAutoStateIDs, @PendingReminderStateIDs);
if (@PendingsStateIDs) {
$SQLExt .= " AND st.ticket_state_id IN (${\(join ', ', sort @PendingsStateIDs)}) ";
}
}
# get tickets pending older than x minutes
if ( defined $Param{TicketPendingTimeOlderMinutes} ) {
$Param{TicketPendingTimeOlderMinutes} ||= 0;
my $TimeStamp = $TimeObject->SystemTime();
$TimeStamp -= ( $Param{TicketPendingTimeOlderMinutes} * 60 );
$Param{TicketPendingTimeOlderDate} = $TimeObject->SystemTime2TimeStamp(
SystemTime => $TimeStamp,
);
}
# get tickets pending newer than x minutes
if ( defined $Param{TicketPendingTimeNewerMinutes} ) {
$Param{TicketPendingTimeNewerMinutes} ||= 0;
my $TimeStamp = $TimeObject->SystemTime();
$TimeStamp -= ( $Param{TicketPendingTimeNewerMinutes} * 60 );
$Param{TicketPendingTimeNewerDate} = $TimeObject->SystemTime2TimeStamp(
SystemTime => $TimeStamp,
);
}
# get pending tickets older than xxxx-xx-xx xx:xx date
my $ComparePendingTimeOlderNewerDate;
if ( $Param{TicketPendingTimeOlderDate} ) {
# check time format
if (
$Param{TicketPendingTimeOlderDate} !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '$Param{TicketPendingTimeOlderDate}'!",
);
return;
}
my $TimeStamp = $TimeObject->TimeStamp2SystemTime(
String => $Param{TicketPendingTimeOlderDate},
);
if ( !$TimeStamp ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{TicketPendingTimeOlderDate} . "'!",
);
return;
}
$ComparePendingTimeOlderNewerDate = $TimeStamp;
$SQLExt .= " AND st.until_time <= $TimeStamp";
}
# get pending tickets newer than xxxx-xx-xx xx:xx date
if ( $Param{TicketPendingTimeNewerDate} ) {
if (
$Param{TicketPendingTimeNewerDate} !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '$Param{TicketPendingTimeNewerDate}'!",
);
return;
}
my $TimeStamp = $TimeObject->TimeStamp2SystemTime(
String => $Param{TicketPendingTimeNewerDate},
);
if ( !$TimeStamp ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{TicketPendingTimeNewerDate} . "'!",
);
return;
}
# don't execute queries if newer date is after current date
return if $TimeStamp > $CurrentSystemTime;
# don't execute queries if older/newer date restriction show now valid timeframe
return
if $ComparePendingTimeOlderNewerDate && $TimeStamp > $ComparePendingTimeOlderNewerDate;
$SQLExt .= " AND st.until_time >= $TimeStamp";
}
# archive flag
if ( $Kernel::OM->Get('Kernel::Config')->Get('Ticket::ArchiveSystem') ) {
# if no flag is given, only search for not archived ticket
if ( !$Param{ArchiveFlags} ) {
$Param{ArchiveFlags} = ['n'];
}
# prepare search with archive flags, check arguments
if ( ref $Param{ArchiveFlags} ne 'ARRAY' ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid attribute ArchiveFlags '$Param{ArchiveFlags}'!",
);
return;
}
# prepare options
my %Options;
for my $Key ( @{ $Param{ArchiveFlags} } ) {
$Options{$Key} = 1;
}
# search for archived
if ( $Options{y} && !$Options{n} ) {
$SQLExt .= ' AND archive_flag = 1';
}
# search for not archived
elsif ( !$Options{y} && $Options{n} ) {
$SQLExt .= ' AND archive_flag = 0';
}
}
# database query for sort/order by option
if ( $Result ne 'COUNT' ) {
$SQLExt .= ' ORDER BY';
for my $Count ( 0 .. $#SortByArray ) {
if ( $Count > 0 ) {
$SQLExt .= ',';
}
# sort by dynamic field
if ( $ValidDynamicFieldParams{ $SortByArray[$Count] } ) {
my ($DynamicFieldName) = $SortByArray[$Count] =~ m/^DynamicField_(.*)$/smx;
my $DynamicField = $TicketDynamicFieldName2Config{$DynamicFieldName} ||
$ArticleDynamicFieldName2Config{$DynamicFieldName};
# If the table was already joined for searching, we reuse it.
if ( !$DynamicFieldJoinTables{$DynamicFieldName} ) {
if ( $TicketDynamicFieldName2Config{$DynamicFieldName} ) {
# Join the table for this dynamic field; use a left outer join in this case.
# With an INNER JOIN we'd limit the result set to tickets which have an entry
# for the DF which is used for sorting.
$SQLFrom
.= " LEFT OUTER JOIN dynamic_field_value dfv$DynamicFieldJoinCounter"
. " ON (st.id = dfv$DynamicFieldJoinCounter.$DynamicField->{IdentifierDBAttribute}"
. " AND dfv$DynamicFieldJoinCounter.field_id = " .
$DBObject->Quote( $DynamicField->{ID}, 'Integer' ) . ") ";
}
elsif ( $ArticleDynamicFieldName2Config{$DynamicFieldName} ) {
if ( !$ArticleJoinSQL ) {
$ArticleJoinSQL = ' INNER JOIN article art ON st.id = art.ticket_id ';
$SQLFrom .= $ArticleJoinSQL;
}
$SQLFrom
.= " LEFT OUTER JOIN dynamic_field_value dfv$DynamicFieldJoinCounter"
. " ON (art.id = dfv$DynamicFieldJoinCounter.$DynamicField->{IdentifierDBAttribute}"
. " AND dfv$DynamicFieldJoinCounter.field_id = " .
$DBObject->Quote( $DynamicField->{ID}, 'Integer' ) . ") ";
}
$DynamicFieldJoinTables{ $DynamicField->{Name} } = "dfv$DynamicFieldJoinCounter";
$DynamicFieldJoinCounter++;
}
my $SQLOrderField = $DynamicFieldBackendObject->SearchSQLOrderFieldGet(
DynamicFieldConfig => $DynamicField,
TableAlias => $DynamicFieldJoinTables{$DynamicFieldName},
);
$SQLExt .= " $SQLOrderField ";
}
elsif (
$SortByArray[$Count] eq 'Owner'
|| $SortByArray[$Count] eq 'Responsible'
) {
# join the users table on user's id
$SQLFrom
.= ' JOIN users u '
. ' ON ' . $SortOptions{ $SortByArray[$Count] } . ' = u.id ';
# sort by first and last name
my $OrderBySuffix = $OrderByArray[$Count] eq 'Up' ? 'ASC' : 'DESC';
$SQLExt .= " u.first_name $OrderBySuffix, u.last_name ";
}
else {
# regular sort
$SQLExt .= ' ' . $SortOptions{ $SortByArray[$Count] };
}
if ( $OrderByArray[$Count] eq 'Up' ) {
$SQLExt .= ' ASC';
}
else {
$SQLExt .= ' DESC';
}
}
}
# check cache
my $CacheObject;
if ( ( $ArticleIndexSQLExt && $Param{FullTextIndex} ) || $Param{CacheTTL} ) {
$CacheObject = $Kernel::OM->Get('Kernel::System::Cache');
my $CacheData = $CacheObject->Get(
Type => 'TicketSearch',
Key => $SQLSelect . $SQLFrom . $SQLExt . $Result . $Limit,
);
if ( defined $CacheData ) {
if ( ref $CacheData eq 'HASH' ) {
return %{$CacheData};
}
elsif ( ref $CacheData eq 'ARRAY' ) {
return @{$CacheData};
}
elsif ( ref $CacheData eq '' ) {
return $CacheData;
}
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => 'Invalid ref ' . ref($CacheData) . '!'
);
return;
}
}
# database query
my %Tickets;
my @TicketIDs;
my $Count;
return
if !$DBObject->Prepare(
SQL => $SQLSelect . $SQLFrom . $SQLExt,
);
while ( my @Row = $DBObject->FetchrowArray() ) {
$Count = $Row[0];
push @TicketIDs, $Row[0] if ( !$Tickets{ $Row[0] } );
$Tickets{ $Row[0] } = $Row[1];
last if (
$Limit
&& scalar( @TicketIDs ) == $Limit
);
}
# return COUNT
if ( $Result eq 'COUNT' ) {
if ($CacheObject) {
$CacheObject->Set(
Type => 'TicketSearch',
Key => $SQLSelect . $SQLFrom . $SQLExt . $Result . $Limit,
Value => $Count,
TTL => $Param{CacheTTL} || 60 * 4,
);
}
return $Count;
}
# return HASH
elsif ( $Result eq 'HASH' ) {
if ($CacheObject) {
$CacheObject->Set(
Type => 'TicketSearch',
Key => $SQLSelect . $SQLFrom . $SQLExt . $Result . $Limit,
Value => \%Tickets,
TTL => $Param{CacheTTL} || 60 * 4,
);
}
return %Tickets;
}
# return ARRAY
else {
if ($CacheObject) {
$CacheObject->Set(
Type => 'TicketSearch',
Key => $SQLSelect . $SQLFrom . $SQLExt . $Result . $Limit,
Value => \@TicketIDs,
TTL => $Param{CacheTTL} || 60 * 4,
);
}
return @TicketIDs;
}
}
=item SearchStringStopWordsFind()
Find stop words within given search string.
my $StopWords = $TicketObject->SearchStringStopWordsFind(
SearchStrings => {
'Fulltext' => '(this AND is) OR test',
'From' => 'myself',
},
);
Returns Hashref with found stop words.
=cut
sub SearchStringStopWordsFind {
my ( $Self, %Param ) = @_;
# check needed stuff
for my $Key (qw(SearchStrings)) {
if ( !$Param{$Key} ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Need $Key!",
);
return;
}
}
my $StopWordRaw = $Kernel::OM->Get('Kernel::Config')->Get('Ticket::SearchIndex::StopWords') || {};
if ( !$StopWordRaw || ref $StopWordRaw ne 'HASH' ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid config option Ticket::SearchIndex::StopWords! "
. "Please reset the search index options to reactivate the factory defaults.",
);
return;
}
my %StopWord;
LANGUAGE:
for my $Language ( sort keys %{$StopWordRaw} ) {
if ( !$Language || !$StopWordRaw->{$Language} || ref $StopWordRaw->{$Language} ne 'ARRAY' ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid config option Ticket::SearchIndex::StopWords###$Language! "
. "Please reset this option to reactivate the factory defaults.",
);
next LANGUAGE;
}
WORD:
for my $Word ( @{ $StopWordRaw->{$Language} } ) {
next WORD if !defined $Word || !length $Word;
$Word = lc $Word;
$StopWord{$Word} = 1;
}
}
my $SearchIndexAttributes = $Kernel::OM->Get('Kernel::Config')->Get('Ticket::SearchIndex::Attribute');
my $WordLengthMin = $SearchIndexAttributes->{WordLengthMin} || 3;
my $WordLengthMax = $SearchIndexAttributes->{WordLengthMax} || 30;
my %StopWordsFound;
SEARCHSTRING:
for my $Key ( sort keys %{ $Param{SearchStrings} } ) {
my $SearchString = $Param{SearchStrings}->{$Key};
my %Result = $Kernel::OM->Get('Kernel::System::DB')->QueryCondition(
'Key' => '.', # resulting SQL is irrelevant
'Value' => $SearchString,
'BindMode' => 1,
);
next SEARCHSTRING if !%Result || ref $Result{Values} ne 'ARRAY' || !@{ $Result{Values} };
my %Words;
for my $Value ( @{ $Result{Values} } ) {
my @Words = split '\s+', $$Value;
for my $Word (@Words) {
$Words{ lc $Word } = 1;
}
}
@{ $StopWordsFound{$Key} }
= grep { $StopWord{$_} || length $_ < $WordLengthMin || length $_ > $WordLengthMax } sort keys %Words;
}
return \%StopWordsFound;
}
=item SearchStringStopWordsUsageWarningActive()
Checks if warnings for stop words in search strings are active or not.
my $WarningActive = $TicketObject->SearchStringStopWordsUsageWarningActive();
=cut
sub SearchStringStopWordsUsageWarningActive {
my ( $Self, %Param ) = @_;
my $ConfigObject = $Kernel::OM->Get('Kernel::Config');
my $SearchIndexModule = $ConfigObject->Get('Ticket::SearchIndexModule');
my $WarnOnStopWordUsage = $ConfigObject->Get('Ticket::SearchIndex::WarnOnStopWordUsage') || 0;
if (
$SearchIndexModule eq 'Kernel::System::Ticket::ArticleSearchIndex::StaticDB'
&& $WarnOnStopWordUsage
) {
return 1;
}
return 0;
}
=begin Internal:
=cut
=item _InConditionGet()
internal function to create an
AND table.column IN (values)
condition string from an array.
my $SQLPart = $TicketObject->_InConditionGet(
TableColumn => 'table.column',
IDRef => $ArrayRef,
);
=cut
sub _InConditionGet {
my ( $Self, %Param ) = @_;
# check needed stuff
for my $Key (qw(TableColumn IDRef)) {
if ( !$Param{$Key} ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Need $Key!",
);
return;
}
}
# sort ids to cache the SQL query
my @SortedIDs = sort { $a <=> $b } @{ $Param{IDRef} };
# quote values
SORTEDID:
for my $Value (@SortedIDs) {
next SORTEDID if !defined $Kernel::OM->Get('Kernel::System::DB')->Quote( $Value, 'Integer' );
}
# split IN statement with more than 900 elements in more statements combined with OR
# because Oracle doesn't support more than 1000 elements for one IN statement.
my @SQLStrings;
while ( scalar @SortedIDs ) {
# remove section in the array
my @SortedIDsPart = splice @SortedIDs, 0, 900;
# link together IDs
my $IDString = join ', ', @SortedIDsPart;
# add new statement
push @SQLStrings, " $Param{TableColumn} IN ($IDString) ";
}
my $SQL = '';
if (@SQLStrings) {
# combine statements
$SQL = join ' OR ', @SQLStrings;
# encapsulate conditions
$SQL = ' AND ( ' . $SQL . ' ) ';
}
return $SQL;
}
# nearly the same as TicketSearch but SOME search paramaters combined with OR
sub TicketSearchOR {
my ( $Self, %Param ) = @_;
my $Result = $Param{Result} || 'HASH';
my $OrderBy = $Param{OrderBy} || 'Down';
my $SortBy = $Param{SortBy} || 'Age';
my $Limit = $Param{Limit} || 10000;
if ( !$Param{ContentSearch} ) {
$Param{ContentSearch} = 'AND';
}
my %SortOptions = (
Owner => 'st.user_id',
Responsible => 'st.responsible_user_id',
CustomerID => 'st.customer_id',
State => 'st.ticket_state_id',
Lock => 'st.ticket_lock_id',
Ticket => 'st.tn',
TicketNumber => 'st.tn',
Title => 'st.title',
Queue => 'sq.name',
Type => 'st.type_id',
Priority => 'st.ticket_priority_id',
Age => 'st.create_time_unix',
Changed => 'st.change_time',
Service => 'st.service_id',
SLA => 'st.sla_id',
PendingTime => 'st.until_time',
TicketEscalation => 'st.escalation_time',
EscalationTime => 'st.escalation_time',
EscalationUpdateTime => 'st.escalation_update_time',
EscalationResponseTime => 'st.escalation_response_time',
EscalationSolutionTime => 'st.escalation_solution_time',
ID => 'st.id'
);
# check required params
if ( !$Param{UserID} && !$Param{CustomerUserID} ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => 'Need UserID or CustomerUserID params for permission check!',
);
return;
}
# check types of given arguments
ARGUMENT:
for my $Key (
qw(
Types TypeIDs CreatedTypes CreatedTypeIDs States StateIDs CreatedStates CreatedStateIDs StateTypeIDs
Locks LockIDs OwnerIDs ResponsibleIDs CreatedUserIDs Queues QueueIDs CreatedQueues CreatedQueueIDs
Priorities PriorityIDs CreatedPriorities CreatedPriorityIDs Services ServiceIDs SLAs SLAIDs WatchUserIDs
)
) {
next ARGUMENT if !$Param{$Key};
next ARGUMENT if ref $Param{$Key} eq 'ARRAY' && @{ $Param{$Key} };
# log error
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "The given param '$Key' is invalid or an empty array reference!",
);
return;
}
# get database object
my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
# quote id array elements
ARGUMENT:
for my $Key (
qw(
TypeIDs CreatedTypeIDs StateIDs CreatedStateIDs StateTypeIDs LockIDs OwnerIDs ResponsibleIDs CreatedUserIDs
QueueIDs CreatedQueueIDs PriorityIDs CreatedPriorityIDs ServiceIDs SLAIDs WatchUserIDs
)
) {
next ARGUMENT if !$Param{$Key};
# quote elements
for my $Element ( @{ $Param{$Key} } ) {
if ( $Element eq '_NONE_' ) {
$Element = 0;
}
if ( !defined $DBObject->Quote( $Element, 'Integer' ) ) {
# log error
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "The given param '$Element' in '$Key' is invalid!",
);
return;
}
}
}
my $TicketDynamicFields = [];
my $ArticleDynamicFields = [];
my %ValidDynamicFieldParams;
my %TicketDynamicFieldName2Config;
my %ArticleDynamicFieldName2Config;
# Only fetch DynamicField data if a field was requested for searching or sorting
my $ParamCheckString = ( join '', keys %Param ) || '';
if ( ref $Param{SortBy} eq 'ARRAY' ) {
$ParamCheckString .= ( join '', @{ $Param{SortBy} } );
}
elsif ( ref $Param{SortBy} ne 'HASH' ) {
$ParamCheckString .= $Param{SortBy} || '';
}
if ( $ParamCheckString =~ m/DynamicField_/smx ) {
# get dynamic field object
my $DynamicFieldObject = $Kernel::OM->Get('Kernel::System::DynamicField');
# Check all configured ticket dynamic fields
$TicketDynamicFields = $DynamicFieldObject->DynamicFieldListGet(
ObjectType => 'Ticket',
);
for my $DynamicField ( @{$TicketDynamicFields} ) {
$ValidDynamicFieldParams{ "DynamicField_" . $DynamicField->{Name} } = 1;
$TicketDynamicFieldName2Config{ $DynamicField->{Name} } = $DynamicField;
}
# Check all configured article dynamic fields
$ArticleDynamicFields = $DynamicFieldObject->DynamicFieldListGet(
ObjectType => 'Article',
);
for my $DynamicField ( @{$ArticleDynamicFields} ) {
$ValidDynamicFieldParams{ "DynamicField_" . $DynamicField->{Name} } = 1;
$ArticleDynamicFieldName2Config{ $DynamicField->{Name} } = $DynamicField;
}
}
# check sort/order by options
my @SortByArray;
my @OrderByArray;
if ( ref $SortBy eq 'ARRAY' ) {
@SortByArray = @{$SortBy};
@OrderByArray = @{$OrderBy};
}
else {
@SortByArray = ($SortBy);
@OrderByArray = ($OrderBy);
}
# add ID to sort/order
push(@SortByArray, 'ID');
push(@OrderByArray, 'Up');
for my $Count ( 0 .. $#SortByArray ) {
if (
!$SortOptions{ $SortByArray[$Count] }
&& !$ValidDynamicFieldParams{ $SortByArray[$Count] }
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => 'Need valid SortBy (' . $SortByArray[$Count] . ')!',
);
return;
}
if ( $OrderByArray[$Count] ne 'Down' && $OrderByArray[$Count] ne 'Up' ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => 'Need valid OrderBy (' . $OrderByArray[$Count] . ')!',
);
return;
}
}
# create sql
my $SQLSelect;
if ( $Result eq 'COUNT' ) {
$SQLSelect = 'SELECT COUNT(DISTINCT(st.id))';
}
else {
$SQLSelect = 'SELECT st.id, st.tn';
}
my $SQLFrom = ' FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id ';
my $ArticleJoinSQL = $Self->_ArticleIndexQuerySQL( Data => \%Param ) || '';
# sql, use also article table if needed
$SQLFrom .= $ArticleJoinSQL;
# attachment name search
if ( $Param{AttachmentName} ) {
# joins to article and article_attachments are needed, it can not use existing article joins
# otherwise the search will be limited to already matching articles
my $AttachmentJoinSQL = ' INNER JOIN article art_for_att ON st.id = art_for_att.ticket_id'
. ' INNER JOIN article_attachment_search att ON att.article_id = art_for_att.id ';
# SQL, use also article_attachment table if needed
$SQLFrom .= $AttachmentJoinSQL;
}
# use also history table if required
my $PatternArg0 = '^Created.+?';
my $PatternArg1 = '^TicketChangeTime(Newer|Older)(Date|Minutes)';
my $PatternArg2 = '^TicketCloseTime(Newer|Older)(Date|Minutes)';
my $TableAdded0 = 0;
my $TableAdded1 = 0;
my $TableAdded2 = 0;
ARGUMENT:
for my $Key ( sort keys %Param ) {
if ( $Param{ $Key } ) {
if (
!$TableAdded0
&& $Key =~ /$PatternArg0/
) {
$SQLFrom .= 'INNER JOIN ticket_history th ON st.id = th.ticket_id ';
$TableAdded0 = 1;
}
elsif (
!$TableAdded1
&& $Key =~ /$PatternArg1/
) {
$SQLFrom .= 'INNER JOIN ticket_history th1 ON st.id = th1.ticket_id ';
$TableAdded1 = 1;
}
elsif (
!$TableAdded2
&& $Key =~ /$PatternArg2/
) {
$SQLFrom .= 'INNER JOIN ticket_history th2 ON st.id = th2.ticket_id ';
$TableAdded2 = 1;
}
}
}
# add ticket watcher table
if ( $Param{WatchUserIDs} ) {
$SQLFrom .= 'INNER JOIN ticket_watcher tw ON st.id = tw.ticket_id ';
}
my $SQLExt = ' WHERE 1=1';
# Limit the search to just one TicketID (used by the GenericAgent
# to filter for events on single tickets with the job's ticket filter).
if ( $Param{TicketID} ) {
$SQLExt .= ' AND st.id = ' . $DBObject->Quote( $Param{TicketID}, 'Integer' );
}
# add ticket flag table
if ( $Param{TicketFlag} ) {
my $Index = 1;
for my $Key ( sort keys %{ $Param{TicketFlag} } ) {
$SQLFrom .= "INNER JOIN ticket_flag tf$Index ON st.id = tf$Index.ticket_id ";
$Index++;
}
}
my $SQLExtOR = '';
# current type lookup
if ( $Param{Types} ) {
# get type object
my $TypeObject = $Kernel::OM->Get('Kernel::System::Type');
for my $Type ( @{ $Param{Types} } ) {
# lookup type id
my $TypeID = $TypeObject->TypeLookup(
Type => $Type,
);
return if !$TypeID;
push @{ $Param{TypeIDs} }, $TypeID;
}
}
# type ids
if ( $Param{TypeIDs} ) {
$SQLExtOR .= $Self->_InConditionGetOR(
TableColumn => 'st.type_id',
IDRef => $Param{TypeIDs},
);
}
# created types lookup
if ( $Param{CreatedTypes} ) {
# get type object
my $TypeObject = $Kernel::OM->Get('Kernel::System::Type');
for my $Type ( @{ $Param{CreatedTypes} } ) {
# lookup type id
my $TypeID = $TypeObject->TypeLookup(
Type => $Type,
);
return if !$TypeID;
push @{ $Param{CreatedTypeIDs} }, $TypeID;
}
}
# created type ids
if ( $Param{CreatedTypeIDs} ) {
# lookup history type id
my $HistoryTypeID = $Self->HistoryTypeLookup(
Type => 'NewTicket',
);
if ($HistoryTypeID) {
# create sql part
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'th.type_id',
IDRef => $Param{CreatedTypeIDs},
);
$SQLExt .= " AND th.history_type_id = $HistoryTypeID ";
}
}
# current state lookup
if ( $Param{States} ) {
# get state object
my $StateObject = $Kernel::OM->Get('Kernel::System::State');
for my $State ( @{ $Param{States} } ) {
# get state data
my %StateData = $StateObject->StateGet(
Name => $State,
);
return if !%StateData;
push @{ $Param{StateIDs} }, $StateData{ID};
}
}
# state ids
if ( $Param{StateIDs} ) {
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'st.ticket_state_id',
IDRef => $Param{StateIDs},
);
}
# created states lookup
if ( $Param{CreatedStates} ) {
# get state object
my $StateObject = $Kernel::OM->Get('Kernel::System::State');
for my $State ( @{ $Param{CreatedStates} } ) {
# get state data
my %StateData = $StateObject->StateGet(
Name => $State,
);
return if !%StateData;
push @{ $Param{CreatedStateIDs} }, $StateData{ID};
}
}
# created state ids
if ( $Param{CreatedStateIDs} ) {
# lookup history type id
my $HistoryTypeID = $Self->HistoryTypeLookup(
Type => 'NewTicket',
);
if ($HistoryTypeID) {
# create sql part
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'th.state_id',
IDRef => $Param{CreatedStateIDs},
);
$SQLExt .= " AND th.history_type_id = $HistoryTypeID ";
}
}
# current ticket state type
# NOTE: Open and Closed are not valid state types. It's for compat.
# Open -> All states which are grouped as open (new, open, pending, ...)
# Closed -> All states which are grouped as closed (closed successful, closed unsuccessful)
if ( $Param{StateType} && $Param{StateType} eq 'Open' ) {
my @ViewableStateIDs = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType(
Type => 'Viewable',
Result => 'ID',
);
$SQLExt .= " AND st.ticket_state_id IN ( ${\(join ', ', sort @ViewableStateIDs)} ) ";
}
elsif ( $Param{StateType} && $Param{StateType} eq 'Closed' ) {
my @ViewableStateIDs = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType(
Type => 'Viewable',
Result => 'ID',
);
$SQLExt .= " AND st.ticket_state_id NOT IN ( ${\(join ', ', sort @ViewableStateIDs)} ) ";
}
# current ticket state type
elsif ( $Param{StateType} ) {
my @StateIDs = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType(
StateType => $Param{StateType},
Result => 'ID',
);
return if !$StateIDs[0];
$SQLExt .= " AND st.ticket_state_id IN ( ${\(join ', ', sort {$a <=> $b} @StateIDs)} ) ";
}
if ( $Param{StateTypeIDs} ) {
# get state object
my $StateObject = $Kernel::OM->Get('Kernel::System::State');
my %StateTypeList = $StateObject->StateTypeList(
UserID => $Param{UserID} || 1,
);
my @StateTypes = map { $StateTypeList{$_} } @{ $Param{StateTypeIDs} };
my @StateIDs = $StateObject->StateGetStatesByType(
StateType => \@StateTypes,
Result => 'ID',
);
return if !$StateIDs[0];
$SQLExt .= " AND st.ticket_state_id IN ( ${\(join ', ', sort {$a <=> $b} @StateIDs)} ) ";
}
# current lock lookup
if ( $Param{Locks} ) {
for my $Lock ( @{ $Param{Locks} } ) {
# lookup lock id
my $LockID = $Kernel::OM->Get('Kernel::System::Lock')->LockLookup(
Lock => $Lock,
);
return if !$LockID;
push @{ $Param{LockIDs} }, $LockID;
}
}
# lock ids
if ( $Param{LockIDs} ) {
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'st.ticket_lock_id',
IDRef => $Param{LockIDs},
);
}
# current owner user ids
if ( $Param{OwnerIDs} ) {
$SQLExtOR .= $Self->_InConditionGetOR(
TableColumn => 'st.user_id',
IDRef => $Param{OwnerIDs},
);
}
# current responsible user ids
if ( $Param{ResponsibleIDs} ) {
$SQLExtOR .= $Self->_InConditionGetOR(
TableColumn => 'st.responsible_user_id',
IDRef => $Param{ResponsibleIDs},
);
}
# created user ids
if ( $Param{CreatedUserIDs} ) {
# lookup history type id
my $HistoryTypeID = $Self->HistoryTypeLookup(
Type => 'NewTicket',
);
if ($HistoryTypeID) {
# create sql part
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'th.create_by',
IDRef => $Param{CreatedUserIDs},
);
$SQLExt .= " AND th.history_type_id = $HistoryTypeID ";
}
}
# current queue lookup
if ( $Param{Queues} ) {
# get queue object
my $QueueObject = $Kernel::OM->Get('Kernel::System::Queue');
for my $Queue ( @{ $Param{Queues} } ) {
# lookup queue id
my $QueueID = $QueueObject->QueueLookup(
Queue => $Queue,
);
return if !$QueueID;
push @{ $Param{QueueIDs} }, $QueueID;
}
}
# current sub queue ids
if ( $Param{UseSubQueues} && $Param{QueueIDs} ) {
# get queue object
my $QueueObject = $Kernel::OM->Get('Kernel::System::Queue');
my @SubQueueIDs;
my %Queues = $QueueObject->GetAllQueues();
for my $QueueID ( @{ $Param{QueueIDs} } ) {
my $Queue = $QueueObject->QueueLookup( QueueID => $QueueID );
for my $QueuesID ( sort keys %Queues ) {
if ( $Queues{$QueuesID} =~ /^\Q$Queue\E::/i ) {
push @SubQueueIDs, $QueuesID;
}
}
}
push @{ $Param{QueueIDs} }, @SubQueueIDs;
}
# current queue ids
if ( $Param{QueueIDs} ) {
$SQLExtOR .= $Self->_InConditionGetOR(
TableColumn => 'st.queue_id',
IDRef => $Param{QueueIDs},
);
}
# created queue lookup
if ( $Param{CreatedQueues} ) {
# get queue object
my $QueueObject = $Kernel::OM->Get('Kernel::System::Queue');
for my $Queue ( @{ $Param{CreatedQueues} } ) {
# lookup queue id
my $QueueID = $QueueObject->QueueLookup(
Queue => $Queue,
);
return if !$QueueID;
push @{ $Param{CreatedQueueIDs} }, $QueueID;
}
}
# created queue ids
if ( $Param{CreatedQueueIDs} ) {
# lookup history type id
my $HistoryTypeID = $Self->HistoryTypeLookup(
Type => 'NewTicket',
);
if ($HistoryTypeID) {
# create sql part
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'th.queue_id',
IDRef => $Param{CreatedQueueIDs},
);
$SQLExt .= " AND th.history_type_id = $HistoryTypeID ";
}
}
my @GroupIDs;
# user groups
if ( $Param{UserID} && $Param{UserID} != 1 ) {
# get users groups
@GroupIDs = $Kernel::OM->Get('Kernel::System::Group')->GroupMemberList(
UserID => $Param{UserID},
Type => $Param{Permission} || 'ro',
Result => 'ID',
);
# return if we have no permissions
return if !@GroupIDs;
}
# customer groups
elsif ( $Param{CustomerUserID} ) {
@GroupIDs = $Kernel::OM->Get('Kernel::System::CustomerGroup')->GroupMemberList(
UserID => $Param{CustomerUserID},
Type => $Param{Permission} || 'ro',
Result => 'ID',
);
# return if we have no permissions
return if !@GroupIDs;
# get all customer ids
$SQLExt .= ' AND (';
my @CustomerIDs = $Kernel::OM->Get('Kernel::System::CustomerUser')->CustomerIDs(
User => $Param{CustomerUserID},
);
if (@CustomerIDs) {
my $Lower = '';
if ( $DBObject->GetDatabaseFunction('CaseSensitive') ) {
$Lower = 'LOWER';
}
$SQLExt .= "$Lower(st.customer_id) IN (";
my $Exists = 0;
for (@CustomerIDs) {
if ($Exists) {
$SQLExt .= ', ';
}
else {
$Exists = 1;
}
$SQLExt .= "$Lower('" . $DBObject->Quote($_) . "')";
}
$SQLExt .= ') OR ';
}
# get all own tickets
my $CustomerUserIDQuoted = $DBObject->Quote( $Param{CustomerUserID} );
$SQLExt .= "st.customer_user_id = '$CustomerUserIDQuoted') ";
}
# add group ids to sql string
if (@GroupIDs) {
$SQLExt .= " AND sq.group_id IN (${\(join ', ' , sort {$a <=> $b} @GroupIDs)}) ";
}
# current priority lookup
if ( $Param{Priorities} ) {
# get priority object
my $PriorityObject = $Kernel::OM->Get('Kernel::System::Priority');
for my $Priority ( @{ $Param{Priorities} } ) {
# lookup priority id
my $PriorityID = $PriorityObject->PriorityLookup(
Priority => $Priority,
);
return if !$PriorityID;
push @{ $Param{PriorityIDs} }, $PriorityID;
}
}
# priority ids
if ( $Param{PriorityIDs} ) {
$SQLExtOR .= $Self->_InConditionGetOR(
TableColumn => 'st.ticket_priority_id',
IDRef => $Param{PriorityIDs},
);
}
# created priority lookup
if ( $Param{CreatedPriorities} ) {
# get priority object
my $PriorityObject = $Kernel::OM->Get('Kernel::System::Priority');
for my $Priority ( @{ $Param{CreatedPriorities} } ) {
# lookup priority id
my $PriorityID = $PriorityObject->PriorityLookup(
Priority => $Priority,
);
return if !$PriorityID;
push @{ $Param{CreatedPriorityIDs} }, $PriorityID;
}
}
# created priority ids
if ( $Param{CreatedPriorityIDs} ) {
# lookup history type id
my $HistoryTypeID = $Self->HistoryTypeLookup(
Type => 'NewTicket',
);
if ($HistoryTypeID) {
# create sql part
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'th.priority_id',
IDRef => $Param{CreatedPriorityIDs},
);
$SQLExt .= " AND th.history_type_id = $HistoryTypeID ";
}
}
# current service lookup
if ( $Param{Services} ) {
# get service object
my $ServiceObject = $Kernel::OM->Get('Kernel::System::Service');
for my $Service ( @{ $Param{Services} } ) {
# lookup service id
my $ServiceID = $ServiceObject->ServiceLookup(
Name => $Service,
);
return if !$ServiceID;
push @{ $Param{ServiceIDs} }, $ServiceID;
}
}
# service ids
if ( $Param{ServiceIDs} ) {
$SQLExtOR .= $Self->_InConditionGetOR(
TableColumn => 'st.service_id',
IDRef => $Param{ServiceIDs},
);
}
# current sla lookup
if ( $Param{SLAs} ) {
# get sla object
my $SLAObject = $Kernel::OM->Get('Kernel::System::SLA');
for my $SLA ( @{ $Param{SLAs} } ) {
# lookup sla id
my $SLAID = $SLAObject->SLALookup(
Name => $SLA,
);
return if !$SLAID;
push @{ $Param{SLAIDs} }, $SLAID;
}
}
# sla ids
if ( $Param{SLAIDs} ) {
$SQLExtOR .= $Self->_InConditionGetOR(
TableColumn => 'st.sla_id',
IDRef => $Param{SLAIDs},
);
}
# watch user ids
if ( $Param{WatchUserIDs} ) {
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'tw.user_id',
IDRef => $Param{WatchUserIDs},
);
}
# add ticket flag extension
if ( $Param{TicketFlag} ) {
my $TicketFlagUserID = $Param{TicketFlagUserID} || $Param{UserID};
return if !defined $TicketFlagUserID;
my $Index = 1;
for my $Key ( sort keys %{ $Param{TicketFlag} } ) {
my $Value = $Param{TicketFlag}->{$Key};
return if !defined $Value;
$SQLExt .= " AND tf$Index.ticket_key = '" . $DBObject->Quote($Key) . "'";
$SQLExt .= " AND tf$Index.ticket_value = '" . $DBObject->Quote($Value) . "'";
$SQLExt .= " AND tf$Index.create_by = "
. $DBObject->Quote( $TicketFlagUserID, 'Integer' );
$Index++;
}
}
if ( $Param{NotTicketFlag} ) {
my $Index = 1;
for my $Key ( sort keys %{ $Param{NotTicketFlag} } ) {
my $Value = $Param{NotTicketFlag}->{$Key};
return if !defined $Value;
$SQLExt .= " AND (ntf$Index.ticket_value IS NULL "
. "OR ntf$Index.ticket_value <> '" . $DBObject->Quote($Value) . "')";
$Index++;
}
}
# other ticket stuff
my %FieldSQLMap = (
TicketNumber => 'st.tn',
Title => 'st.title',
CustomerID => 'st.customer_id',
CustomerIDRaw => 'st.customer_id',
CustomerUserLogin => 'st.customer_user_id',
CustomerUserLoginRaw => 'st.customer_user_id',
);
ATTRIBUTE:
for my $Key ( sort keys %FieldSQLMap ) {
next ATTRIBUTE if !defined $Param{$Key};
next ATTRIBUTE if ( ( $Key eq 'CustomerID' ) && ( defined $Param{CustomerIDRaw} ) );
next ATTRIBUTE
if ( ( $Key eq 'CustomerUserLogin' ) && ( defined $Param{CustomerUserLoginRaw} ) );
# if it's no ref, put it to array ref
if ( ref $Param{$Key} eq '' ) {
$Param{$Key} = [ $Param{$Key} ];
}
# proccess array ref
my $Used = 0;
VALUE:
for my $Value ( @{ $Param{$Key} } ) {
next VALUE if !$Value;
# replace wild card search
$Value =~ s/\*/%/gi;
# check search attribute, we do not need to search for *
next VALUE if $Value =~ /^\%{1,3}$/;
if ( !$Used ) {
$SQLExt .= ' AND (';
$Used = 1;
}
else {
$SQLExt .= ' OR ';
}
if ( $Value eq '_NONE_' ) {
$SQLExt
.= " $FieldSQLMap{$Key} IS NULL OR"
. " $FieldSQLMap{$Key} LIKE ''";
next;
}
# add * to prefix/suffix on title search
my %ConditionFocus;
if ( $Param{ConditionInline} && $Key eq 'Title' ) {
$ConditionFocus{Extended} = 1;
if ( $Param{ContentSearchPrefix} ) {
$ConditionFocus{SearchPrefix} = $Param{ContentSearchPrefix};
}
if ( $Param{ContentSearchSuffix} ) {
$ConditionFocus{SearchSuffix} = $Param{ContentSearchSuffix};
}
}
if ( $Key eq 'CustomerIDRaw' || $Key eq 'CustomerUserLoginRaw' ) {
$SQLExt .= " $FieldSQLMap{$Key}= '" . $DBObject->Quote($Value) . "'";
}
else {
# use search condition extension
$SQLExt .= $DBObject->QueryCondition(
Key => $FieldSQLMap{$Key},
Value => $Value,
%ConditionFocus,
);
}
}
if ($Used) {
$SQLExt .= ')';
}
}
# search article attributes
my $ArticleIndexSQLExt = $Self->_ArticleIndexQuerySQLExt( Data => \%Param );
$SQLExt .= $ArticleIndexSQLExt;
my %CustomerArticleTypes;
my @CustomerArticleTypeIDs;
if ( $Param{CustomerUserID} ) {
%CustomerArticleTypes = $Self->ArticleTypeList(
Result => 'HASH',
Type => 'Customer',
);
@CustomerArticleTypeIDs = keys %CustomerArticleTypes;
}
# restrict search from customers to only customer articles
if ( $Param{CustomerUserID} && $ArticleIndexSQLExt ) {
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'art.article_type_id',
IDRef => \@CustomerArticleTypeIDs,
);
}
# attachment name search
if ( $Param{AttachmentName} ) {
$SQLExt .= ' AND ';
# replace wild card search
my $Key = 'att.filename';
my $Value = lc $Param{AttachmentName};
$Value =~ s/\*/%/gi;
# use search condition extension
$SQLExt .= $DBObject->QueryCondition(
Key => $Key,
Value => $Value,
SearchPrefix => $Param{ContentSearchPrefix},
SearchSuffix => $Param{ContentSearchSuffix},
CaseSensitive => 1,
);
# restrict search from customers to only customer articles
if ( $Param{CustomerUserID} ) {
$SQLExt .= $Self->_InConditionGet(
TableColumn => 'art_for_att.article_type_id',
IDRef => \@CustomerArticleTypeIDs,
);
}
}
# Remember already joined tables for sorting.
my %DynamicFieldJoinTables;
my $DynamicFieldJoinCounter = 1;
# get dynamic field backend object
my $DynamicFieldBackendObject = $Kernel::OM->Get('Kernel::System::DynamicField::Backend');
DYNAMIC_FIELD:
for my $DynamicField ( @{$TicketDynamicFields}, @{$ArticleDynamicFields} ) {
my $SearchParam = $Param{ "DynamicField_" . $DynamicField->{Name} };
next DYNAMIC_FIELD if ( !$SearchParam );
next DYNAMIC_FIELD if ( ref $SearchParam ne 'HASH' );
my $NeedJoin;
for my $Operator ( sort keys %{$SearchParam} ) {
my @SearchParams = ( ref $SearchParam->{$Operator} eq 'ARRAY' )
? @{ $SearchParam->{$Operator} }
: ( $SearchParam->{$Operator} );
my $SQLExtSub = ' AND (';
my $Counter = 0;
TEXT:
for my $Text (@SearchParams) {
next TEXT if ( !defined $Text || $Text eq '' );
$Text =~ s/\*/%/gi;
# check search attribute, we do not need to search for *
next TEXT if $Text =~ /^\%{1,3}$/;
# validate data type
my $ValidateSuccess = $DynamicFieldBackendObject->ValueValidate(
DynamicFieldConfig => $DynamicField,
Value => $Text,
SearchValidation => 1,
UserID => $Param{UserID} || 1,
);
if ( !$ValidateSuccess ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid value '"
. $Text
. "' on field '"
. $DynamicField->{Name}
. "'!",
);
return;
}
if ($Counter) {
$SQLExtSub .= ' OR ';
}
$SQLExtSub .= $DynamicFieldBackendObject->SearchSQLGet(
DynamicFieldConfig => $DynamicField,
TableAlias => "dfv$DynamicFieldJoinCounter",
Operator => $Operator,
SearchTerm => $Text,
);
$Counter++;
}
$SQLExtSub .= ')';
if ($Counter) {
$SQLExt .= $SQLExtSub;
$NeedJoin = 1;
}
}
if ($NeedJoin) {
if ( $DynamicField->{ObjectType} eq 'Ticket' ) {
# Join the table for this dynamic field
$SQLFrom .= "INNER JOIN dynamic_field_value dfv$DynamicFieldJoinCounter"
. " ON (st.id = dfv$DynamicFieldJoinCounter.$DynamicField->{IdentifierDBAttribute}"
. " AND dfv$DynamicFieldJoinCounter.field_id = " .
$DBObject->Quote( $DynamicField->{ID}, 'Integer' ) . ") ";
}
elsif ( $DynamicField->{ObjectType} eq 'Article' ) {
if ( !$ArticleJoinSQL ) {
$ArticleJoinSQL = ' INNER JOIN article art ON st.id = art.ticket_id ';
$SQLFrom .= $ArticleJoinSQL;
}
$SQLFrom .= "INNER JOIN dynamic_field_value dfv$DynamicFieldJoinCounter"
. " ON (art.id = dfv$DynamicFieldJoinCounter.$DynamicField->{IdentifierDBAttribute}"
. " AND dfv$DynamicFieldJoinCounter.field_id = " .
$DBObject->Quote( $DynamicField->{ID}, 'Integer' ) . ") ";
}
$DynamicFieldJoinTables{ $DynamicField->{Name} } = "dfv$DynamicFieldJoinCounter";
$DynamicFieldJoinCounter++;
}
}
# get time object
my $TimeObject = $Kernel::OM->Get('Kernel::System::Time');
# remember current time to prevent searches for future timestamps
my $CurrentSystemTime = $TimeObject->SystemTime();
# get articles created older/newer than x minutes or older/newer than a date
my %ArticleTime = (
ArticleCreateTime => 'art.incoming_time',
);
for my $Key ( sort keys %ArticleTime ) {
# get articles created older than x minutes
if ( defined $Param{ $Key . 'OlderMinutes' } ) {
$Param{ $Key . 'OlderMinutes' } ||= 0;
my $Time = $TimeObject->SystemTime()
- ( $Param{ $Key . 'OlderMinutes' } * 60 );
$SQLExtOR .= " OR $ArticleTime{$Key} >= '$Time'";
}
# get articles created newer than x minutes
if ( defined $Param{ $Key . 'NewerMinutes' } ) {
$Param{ $Key . 'NewerMinutes' } ||= 0;
my $Time = $TimeObject->SystemTime()
- ( $Param{ $Key . 'NewerMinutes' } * 60 );
$SQLExtOR .= " OR $ArticleTime{$Key} >= '$Time'";
}
# get articles created older than xxxx-xx-xx xx:xx date
my $CompareOlderNewerDate;
if ( $Param{ $Key . 'OlderDate' } ) {
my $SystemTime;
if ( $Param{ $Key . 'OlderDate' }= ~ /^(\d{4})-(\d{1,2})-(\d{1,2}) (\d{1,2}):(\d{1,2}):(\d{1,2})$/ ) {
# convert param date to system time
$SystemTime = $TimeObject->Date2SystemTime(
Year => $1,
Month => $2,
Day => $3,
Hour => $4,
Minute => $5,
Second => $6,
);
}
else {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '" . $Param{ $Key . 'OlderDate' } . "'!",
);
return;
}
if ( !$SystemTime ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{ $Key . 'OlderDate' } . "'!",
);
return;
}
$CompareOlderNewerDate = $SystemTime;
$SQLExtOR .= " OR $ArticleTime{$Key} <= '" . $SystemTime . "'";
}
# get articles created newer than xxxx-xx-xx xx:xx date
if ( $Param{ $Key . 'NewerDate' } ) {
my $SystemTime;
if ( $Param{ $Key . 'NewerDate' } =~ /^(\d{4})-(\d{1,2})-(\d{1,2}) (\d{1,2}):(\d{1,2}):(\d{1,2})$/ ) {
# convert param date to system time
$SystemTime = $TimeObject->Date2SystemTime(
Year => $1,
Month => $2,
Day => $3,
Hour => $4,
Minute => $5,
Second => $6,
);
}
else {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '" . $Param{ $Key . 'NewerDate' } . "'!",
);
return;
}
if ( !$SystemTime ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{ $Key . 'NewerDate' } . "'!",
);
return;
}
# don't execute queries if newer date is after current date
return if $SystemTime > $CurrentSystemTime;
# don't execute queries if older/newer date restriction show now valid timeframe
return if $CompareOlderNewerDate && $SystemTime > $CompareOlderNewerDate;
$SQLExtOR .= " OR $ArticleTime{$Key} >= '" . $SystemTime . "'";
}
}
# get tickets created/escalated older/newer than x minutes
my %TicketTime = (
TicketCreateTime => 'st.create_time_unix',
TicketEscalationTime => 'st.escalation_time',
TicketEscalationUpdateTime => 'st.escalation_update_time',
TicketEscalationResponseTime => 'st.escalation_response_time',
TicketEscalationSolutionTime => 'st.escalation_solution_time',
);
for my $Key ( sort keys %TicketTime ) {
# get tickets created or escalated older than x minutes
if ( defined $Param{ $Key . 'OlderMinutes' } ) {
$Param{ $Key . 'OlderMinutes' } ||= 0;
# exclude tickets with no escalation
if ( $Key =~ m{ \A TicketEscalation }xms ) {
$SQLExt .= " AND $TicketTime{$Key} != 0";
}
my $Time = $TimeObject->SystemTime();
$Time -= ( $Param{ $Key . 'OlderMinutes' } * 60 );
$SQLExt .= " AND $TicketTime{$Key} <= $Time";
}
# get tickets created or escalated newer than x minutes
if ( defined $Param{ $Key . 'NewerMinutes' } ) {
$Param{ $Key . 'NewerMinutes' } ||= 0;
# exclude tickets with no escalation
if ( $Key =~ m{ \A TicketEscalation }xms ) {
$SQLExt .= " AND $TicketTime{$Key} != 0";
}
my $Time = $TimeObject->SystemTime();
$Time -= ( $Param{ $Key . 'NewerMinutes' } * 60 );
$SQLExt .= " AND $TicketTime{$Key} >= $Time";
}
}
# get tickets created/escalated older/newer than xxxx-xx-xx xx:xx date
for my $Key ( sort keys %TicketTime ) {
# get tickets created/escalated older than xxxx-xx-xx xx:xx date
my $CompareOlderNewerDate;
if ( $Param{ $Key . 'OlderDate' } ) {
# check time format
if (
$Param{ $Key . 'OlderDate' } !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '" . $Param{ $Key . 'OlderDate' } . "'!",
);
return;
}
# exclude tickets with no escalation
if ( $Key =~ m{ \A TicketEscalation }xms ) {
$SQLExtOR .= " OR $TicketTime{$Key} != 0";
}
my $Time = $TimeObject->TimeStamp2SystemTime(
String => $Param{ $Key . 'OlderDate' },
);
if ( !$Time ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{ $Key . 'OlderDate' } . "'!",
);
return;
}
$CompareOlderNewerDate = $Time;
$SQLExtOR .= " OR $TicketTime{$Key} <= $Time";
}
# get tickets created/escalated newer than xxxx-xx-xx xx:xx date
if ( $Param{ $Key . 'NewerDate' } ) {
if (
$Param{ $Key . 'NewerDate' } !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '" . $Param{ $Key . 'NewerDate' } . "'!",
);
return;
}
# exclude tickets with no escalation
if ( $Key =~ m{ \A TicketEscalation }xms ) {
$SQLExtOR .= " OR $TicketTime{$Key} != 0";
}
my $Time = $TimeObject->TimeStamp2SystemTime(
String => $Param{ $Key . 'NewerDate' },
);
if ( !$Time ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{ $Key . 'NewerDate' } . "'!",
);
return;
}
# don't execute queries if newer date is after current date
return if $Time > $CurrentSystemTime;
# don't execute queries if older/newer date restriction show now valid timeframe
return if $CompareOlderNewerDate && $Time > $CompareOlderNewerDate;
$SQLExtOR .= " OR $TicketTime{$Key} >= $Time";
}
}
# get tickets changed older than x minutes
if ( defined $Param{TicketChangeTimeOlderMinutes} ) {
$Param{TicketChangeTimeOlderMinutes} ||= 0;
my $TimeStamp = $TimeObject->SystemTime();
$TimeStamp -= ( $Param{TicketChangeTimeOlderMinutes} * 60 );
$Param{TicketChangeTimeOlderDate} = $TimeObject->SystemTime2TimeStamp(
SystemTime => $TimeStamp,
);
}
# get tickets changed newer than x minutes
if ( defined $Param{TicketChangeTimeNewerMinutes} ) {
$Param{TicketChangeTimeNewerMinutes} ||= 0;
my $TimeStamp = $TimeObject->SystemTime();
$TimeStamp -= ( $Param{TicketChangeTimeNewerMinutes} * 60 );
$Param{TicketChangeTimeNewerDate} = $TimeObject->SystemTime2TimeStamp(
SystemTime => $TimeStamp,
);
}
# get tickets based on ticket history changed older than xxxx-xx-xx xx:xx date
my $CompareChangeTimeOlderNewerDate;
if ( $Param{TicketChangeTimeOlderDate} ) {
# check time format
if (
$Param{TicketChangeTimeOlderDate} !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '$Param{TicketChangeTimeOlderDate}'!",
);
return;
}
my $Time = $TimeObject->TimeStamp2SystemTime(
String => $Param{TicketChangeTimeOlderDate},
);
if ( !$Time ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{TicketChangeTimeOlderDate} . "'!",
);
return;
}
$CompareChangeTimeOlderNewerDate = $Time;
$SQLExtOR .= " OR th1.create_time <= '"
. $DBObject->Quote( $Param{TicketChangeTimeOlderDate} ) . "'";
}
# get tickets based on ticket history changed newer than xxxx-xx-xx xx:xx date
if ( $Param{TicketChangeTimeNewerDate} ) {
if (
$Param{TicketChangeTimeNewerDate} !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '$Param{TicketChangeTimeNewerDate}'!",
);
return;
}
my $Time = $TimeObject->TimeStamp2SystemTime(
String => $Param{TicketChangeTimeNewerDate},
);
if ( !$Time ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{TicketChangeTimeNewerDate} . "'!",
);
return;
}
# don't execute queries if newer date is after current date
return if $Time > $CurrentSystemTime;
# don't execute queries if older/newer date restriction show now valid timeframe
return if $CompareChangeTimeOlderNewerDate && $Time > $CompareChangeTimeOlderNewerDate;
$SQLExtOR .= " OR th1.create_time >= '"
. $DBObject->Quote( $Param{TicketChangeTimeNewerDate} ) . "'";
}
# get tickets changed older than x minutes
if ( defined $Param{TicketLastChangeTimeOlderMinutes} ) {
$Param{TicketLastChangeTimeOlderMinutes} ||= 0;
my $TimeStamp = $TimeObject->SystemTime();
$TimeStamp -= ( $Param{TicketLastChangeTimeOlderMinutes} * 60 );
$Param{TicketLastChangeTimeOlderDate} = $TimeObject->SystemTime2TimeStamp(
SystemTime => $TimeStamp,
);
}
# get tickets changed newer than x minutes
if ( defined $Param{TicketLastChangeTimeNewerMinutes} ) {
$Param{TicketLastChangeTimeNewerMinutes} ||= 0;
my $TimeStamp = $TimeObject->SystemTime();
$TimeStamp -= ( $Param{TicketLastChangeTimeNewerMinutes} * 60 );
$Param{TicketLastChangeTimeNewerDate} = $TimeObject->SystemTime2TimeStamp(
SystemTime => $TimeStamp,
);
}
# get tickets changed older than xxxx-xx-xx xx:xx date
my $CompareLastChangeTimeOlderNewerDate;
if ( $Param{TicketLastChangeTimeOlderDate} ) {
# check time format
if (
$Param{TicketLastChangeTimeOlderDate} !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '$Param{TicketLastChangeTimeOlderDate}'!",
);
return;
}
my $Time = $TimeObject->TimeStamp2SystemTime(
String => $Param{TicketLastChangeTimeOlderDate},
);
if ( !$Time ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{TicketLastChangeTimeOlderDate} . "'!",
);
return;
}
$CompareLastChangeTimeOlderNewerDate = $Time;
$SQLExt .= " OR st.change_time <= '"
. $DBObject->Quote( $Param{TicketLastChangeTimeOlderDate} ) . "'";
}
# get tickets changed newer than xxxx-xx-xx xx:xx date
if ( $Param{TicketLastChangeTimeNewerDate} ) {
if (
$Param{TicketLastChangeTimeNewerDate} !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '$Param{TicketLastChangeTimeNewerDate}'!",
);
return;
}
my $Time = $TimeObject->TimeStamp2SystemTime(
String => $Param{TicketLastChangeTimeNewerDate},
);
if ( !$Time ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{TicketLastChangeTimeNewerDate} . "'!",
);
return;
}
# don't execute queries if newer date is after current date
return if $Time > $CurrentSystemTime;
# don't execute queries if older/newer date restriction show now valid timeframe
return
if $CompareLastChangeTimeOlderNewerDate && $Time > $CompareLastChangeTimeOlderNewerDate;
$SQLExt .= " OR st.change_time >= '"
. $DBObject->Quote( $Param{TicketLastChangeTimeNewerDate} ) . "'";
}
# get tickets closed older than x minutes
if ( defined $Param{TicketCloseTimeOlderMinutes} ) {
$Param{TicketCloseTimeOlderMinutes} ||= 0;
my $TimeStamp = $TimeObject->SystemTime();
$TimeStamp -= ( $Param{TicketCloseTimeOlderMinutes} * 60 );
$Param{TicketCloseTimeOlderDate} = $TimeObject->SystemTime2TimeStamp(
SystemTime => $TimeStamp,
);
}
# get tickets closed newer than x minutes
if ( defined $Param{TicketCloseTimeNewerMinutes} ) {
$Param{TicketCloseTimeNewerMinutes} ||= 0;
my $TimeStamp = $TimeObject->SystemTime();
$TimeStamp -= ( $Param{TicketCloseTimeNewerMinutes} * 60 );
$Param{TicketCloseTimeNewerDate} = $TimeObject->SystemTime2TimeStamp(
SystemTime => $TimeStamp,
);
}
# get tickets closed older than xxxx-xx-xx xx:xx date
my $CompareCloseTimeOlderNewerDate;
if ( $Param{TicketCloseTimeOlderDate} ) {
# check time format
if (
$Param{TicketCloseTimeOlderDate} !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '$Param{TicketCloseTimeOlderDate}'!",
);
return;
}
my $Time = $TimeObject->TimeStamp2SystemTime(
String => $Param{TicketCloseTimeOlderDate},
);
if ( !$Time ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{TicketCloseTimeOlderDate} . "'!",
);
return;
}
$CompareCloseTimeOlderNewerDate = $Time;
# get close state ids
my @List = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType(
StateType => ['closed'],
Result => 'ID',
);
my @StateID = ( $Self->HistoryTypeLookup( Type => 'NewTicket' ) );
push( @StateID, $Self->HistoryTypeLookup( Type => 'StateUpdate' ) );
if (@StateID) {
$SQLExt .= " AND th2.history_type_id IN (${\(join ', ', sort @StateID)}) AND "
. " th2.state_id IN (${\(join ', ', sort @List)}) AND "
. "th2.create_time <= '"
. $DBObject->Quote( $Param{TicketCloseTimeOlderDate} ) . "'";
}
}
# get tickets closed newer than xxxx-xx-xx xx:xx date
if ( $Param{TicketCloseTimeNewerDate} ) {
if (
$Param{TicketCloseTimeNewerDate} !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '$Param{TicketCloseTimeNewerDate}'!",
);
return;
}
my $Time = $TimeObject->TimeStamp2SystemTime(
String => $Param{TicketCloseTimeNewerDate},
);
if ( !$Time ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{TicketCloseTimeNewerDate} . "'!",
);
return;
}
# don't execute queries if newer date is after current date
return if $Time > $CurrentSystemTime;
# don't execute queries if older/newer date restriction show now valid timeframe
return if $CompareCloseTimeOlderNewerDate && $Time > $CompareCloseTimeOlderNewerDate;
# get close state ids
my @List = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType(
StateType => ['closed'],
Result => 'ID',
);
my @StateID = ( $Self->HistoryTypeLookup( Type => 'NewTicket' ) );
push( @StateID, $Self->HistoryTypeLookup( Type => 'StateUpdate' ) );
if (@StateID) {
$SQLExt .= " AND th2.history_type_id IN (${\(join ', ', sort @StateID)}) AND "
. " th2.state_id IN (${\(join ', ', sort @List)}) AND "
. " th2.create_time >= '"
. $DBObject->Quote( $Param{TicketCloseTimeNewerDate} ) . "'";
}
}
# check if only pending states are used
if (
defined $Param{TicketPendingTimeOlderMinutes}
|| defined $Param{TicketPendingTimeNewerMinutes}
|| $Param{TicketPendingTimeOlderDate}
|| $Param{TicketPendingTimeNewerDate}
) {
# get pending states
my @PendingAutoStateIDs = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType(
Type => 'PendingAuto',
Result => 'ID',
);
my @PendingReminderStateIDs = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType(
Type => 'PendingReminder',
Result => 'ID',
);
my @PendingsStateIDs = (@PendingAutoStateIDs, @PendingReminderStateIDs);
if (@PendingsStateIDs) {
$SQLExt .= " AND st.ticket_state_id IN (${\(join ', ', sort @PendingsStateIDs)}) ";
}
}
# get tickets pending older than x minutes
if ( defined $Param{TicketPendingTimeOlderMinutes} ) {
$Param{TicketPendingTimeOlderMinutes} ||= 0;
my $TimeStamp = $TimeObject->SystemTime();
$TimeStamp -= ( $Param{TicketPendingTimeOlderMinutes} * 60 );
$Param{TicketPendingTimeOlderDate} = $TimeObject->SystemTime2TimeStamp(
SystemTime => $TimeStamp,
);
}
# get tickets pending newer than x minutes
if ( defined $Param{TicketPendingTimeNewerMinutes} ) {
$Param{TicketPendingTimeNewerMinutes} ||= 0;
my $TimeStamp = $TimeObject->SystemTime();
$TimeStamp -= ( $Param{TicketPendingTimeNewerMinutes} * 60 );
$Param{TicketPendingTimeNewerDate} = $TimeObject->SystemTime2TimeStamp(
SystemTime => $TimeStamp,
);
}
# get pending tickets older than xxxx-xx-xx xx:xx date
my $ComparePendingTimeOlderNewerDate;
if ( $Param{TicketPendingTimeOlderDate} ) {
# check time format
if (
$Param{TicketPendingTimeOlderDate} !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '$Param{TicketPendingTimeOlderDate}'!",
);
return;
}
my $TimeStamp = $TimeObject->TimeStamp2SystemTime(
String => $Param{TicketPendingTimeOlderDate},
);
if ( !$TimeStamp ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{TicketPendingTimeOlderDate} . "'!",
);
return;
}
$ComparePendingTimeOlderNewerDate = $TimeStamp;
$SQLExt .= " AND st.until_time <= $TimeStamp";
}
# get pending tickets newer than xxxx-xx-xx xx:xx date
if ( $Param{TicketPendingTimeNewerDate} ) {
if (
$Param{TicketPendingTimeNewerDate} !~ /^\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}$/
) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid time format '$Param{TicketPendingTimeNewerDate}'!",
);
return;
}
my $TimeStamp = $TimeObject->TimeStamp2SystemTime(
String => $Param{TicketPendingTimeNewerDate},
);
if ( !$TimeStamp ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message =>
"Search not executed due to invalid time '"
. $Param{TicketPendingTimeNewerDate} . "'!",
);
return;
}
# don't execute queries if newer date is after current date
return if $TimeStamp > $CurrentSystemTime;
# don't execute queries if older/newer date restriction show now valid timeframe
return
if $ComparePendingTimeOlderNewerDate && $TimeStamp > $ComparePendingTimeOlderNewerDate;
$SQLExt .= " AND st.until_time >= $TimeStamp";
}
# archive flag
if ( $Kernel::OM->Get('Kernel::Config')->Get('Ticket::ArchiveSystem') ) {
# if no flag is given, only search for not archived ticket
if ( !$Param{ArchiveFlags} ) {
$Param{ArchiveFlags} = ['n'];
}
# prepare search with archive flags, check arguments
if ( ref $Param{ArchiveFlags} ne 'ARRAY' ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Invalid attribute ArchiveFlags '$Param{ArchiveFlags}'!",
);
return;
}
# prepare options
my %Options;
for my $Key ( @{ $Param{ArchiveFlags} } ) {
$Options{$Key} = 1;
}
# search for archived
if ( $Options{y} && !$Options{n} ) {
$SQLExt .= ' AND archive_flag = 1';
}
# search for not archived
elsif ( !$Options{y} && $Options{n} ) {
$SQLExt .= ' AND archive_flag = 0';
}
}
$SQLExtOR =~ s/^ OR //;
$SQLExt .= ' AND ( ' . $SQLExtOR . ' ) ';
# database query for sort/order by option
if ( $Result ne 'COUNT' ) {
$SQLExt .= ' ORDER BY';
for my $Count ( 0 .. $#SortByArray ) {
if ( $Count > 0 ) {
$SQLExt .= ',';
}
# sort by dynamic field
if ( $ValidDynamicFieldParams{ $SortByArray[$Count] } ) {
my ($DynamicFieldName) = $SortByArray[$Count] =~ m/^DynamicField_(.*)$/smx;
my $DynamicField = $TicketDynamicFieldName2Config{$DynamicFieldName} ||
$ArticleDynamicFieldName2Config{$DynamicFieldName};
# If the table was already joined for searching, we reuse it.
if ( !$DynamicFieldJoinTables{$DynamicFieldName} ) {
if ( $TicketDynamicFieldName2Config{$DynamicFieldName} ) {
# Join the table for this dynamic field; use a left outer join in this case.
# With an INNER JOIN we'd limit the result set to tickets which have an entry
# for the DF which is used for sorting.
$SQLFrom
.= " LEFT OUTER JOIN dynamic_field_value dfv$DynamicFieldJoinCounter"
. " ON (st.id = dfv$DynamicFieldJoinCounter.$DynamicField->{IdentifierDBAttribute}"
. " AND dfv$DynamicFieldJoinCounter.field_id = " .
$DBObject->Quote( $DynamicField->{ID}, 'Integer' ) . ") ";
}
elsif ( $ArticleDynamicFieldName2Config{$DynamicFieldName} ) {
if ( !$ArticleJoinSQL ) {
$ArticleJoinSQL = ' INNER JOIN article art ON st.id = art.ticket_id ';
$SQLFrom .= $ArticleJoinSQL;
}
$SQLFrom
.= " LEFT OUTER JOIN dynamic_field_value dfv$DynamicFieldJoinCounter"
. " ON (art.id = dfv$DynamicFieldJoinCounter.$DynamicField->{IdentifierDBAttribute}"
. " AND dfv$DynamicFieldJoinCounter.field_id = " .
$DBObject->Quote( $DynamicField->{ID}, 'Integer' ) . ") ";
}
$DynamicFieldJoinTables{ $DynamicField->{Name} } = "dfv$DynamicFieldJoinCounter";
$DynamicFieldJoinCounter++;
}
my $SQLOrderField = $DynamicFieldBackendObject->SearchSQLOrderFieldGet(
DynamicFieldConfig => $DynamicField,
TableAlias => $DynamicFieldJoinTables{$DynamicFieldName},
);
$SQLSelect .= ", $SQLOrderField ";
$SQLExt .= " $SQLOrderField ";
}
else {
# regular sort
$SQLSelect .= ', ' . $SortOptions{ $SortByArray[$Count] };
$SQLExt .= ' ' . $SortOptions{ $SortByArray[$Count] };
}
if ( $OrderByArray[$Count] eq 'Up' ) {
$SQLExt .= ' ASC';
}
else {
$SQLExt .= ' DESC';
}
}
}
# check cache
my $CacheObject;
if ( ( $ArticleIndexSQLExt && $Param{FullTextIndex} ) || $Param{CacheTTL} ) {
$CacheObject = $Kernel::OM->Get('Kernel::System::Cache');
my $CacheData = $CacheObject->Get(
Type => 'TicketSearch',
Key => $SQLSelect . $SQLFrom . $SQLExt . $Result . $Limit,
);
if ( defined $CacheData ) {
if ( ref $CacheData eq 'HASH' ) {
return %{$CacheData};
}
elsif ( ref $CacheData eq 'ARRAY' ) {
return @{$CacheData};
}
elsif ( ref $CacheData eq '' ) {
return $CacheData;
}
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => 'Invalid ref ' . ref($CacheData) . '!'
);
return;
}
}
# database query
my %Tickets;
my @TicketIDs;
my $Count;
return
if !$DBObject->Prepare(
SQL => $SQLSelect . $SQLFrom . $SQLExt,
);
while ( my @Row = $DBObject->FetchrowArray() ) {
$Count = $Row[0];
push( @TicketIDs, $Row[0] ) if ( !$Tickets{ $Row[0] } );
$Tickets{ $Row[0] } = $Row[1];
last if (
$Limit
&& scalar( @TicketIDs ) == $Limit
);
}
# return COUNT
if ( $Result eq 'COUNT' ) {
if ($CacheObject) {
$CacheObject->Set(
Type => 'TicketSearch',
Key => $SQLSelect . $SQLFrom . $SQLExt . $Result . $Limit,
Value => $Count,
TTL => $Param{CacheTTL} || 60 * 4,
);
}
return $Count;
}
# return HASH
elsif ( $Result eq 'HASH' ) {
if ($CacheObject) {
$CacheObject->Set(
Type => 'TicketSearch',
Key => $SQLSelect . $SQLFrom . $SQLExt . $Result . $Limit,
Value => \%Tickets,
TTL => $Param{CacheTTL} || 60 * 4,
);
}
return %Tickets;
}
# return ARRAY
else {
if ($CacheObject) {
$CacheObject->Set(
Type => 'TicketSearch',
Key => $SQLSelect . $SQLFrom . $SQLExt . $Result . $Limit,
Value => \@TicketIDs,
TTL => $Param{CacheTTL} || 60 * 4,
);
}
return @TicketIDs;
}
}
=item _InConditionGetOR()
nearly the same as _InConditionGet but search paramaters combined with OR.
my $SQLPart = $TicketObject->_InConditionGetOR(
TableColumn => 'table.column',
IDRef => $ArrayRef,
);
=cut
sub _InConditionGetOR {
my ( $Self, %Param ) = @_;
# check needed stuff
for my $Key (qw(TableColumn IDRef)) {
if ( !$Param{$Key} ) {
$Kernel::OM->Get('Kernel::System::Log')->Log(
Priority => 'error',
Message => "Need $Key!",
);
return;
}
}
# sort ids to cache the SQL query
my @SortedIDs = sort { $a <=> $b } @{ $Param{IDRef} };
# quote values
for my $Value (@SortedIDs) {
return if !defined $Kernel::OM->Get('Kernel::System::DB')->Quote( $Value, 'Integer' );
}
# split IN statement with more than 900 elements in more statements combined with OR
# because Oracle doesn't support more than 1000 elements for one IN statement.
my @SQLStrings;
while ( scalar @SortedIDs ) {
# remove section in the array
my @SortedIDsPart = splice @SortedIDs, 0, 900;
# link together IDs
my $IDString = join ', ', @SortedIDsPart;
# add new statement
push @SQLStrings, " $Param{TableColumn} IN ($IDString) ";
}
my $SQL = '';
if (@SQLStrings) {
# combine statements
$SQL = join ' OR ', @SQLStrings;
# encapsulate conditions
$SQL = ' OR ( ' . $SQL . ' ) ';
}
return $SQL;
}
=item FilterPrepare()
Preparation of the filters into one.
my $PreparedFilter = $TicketObject->FilterPrepare(
FilterArray = [
{
QueueIDs => [...],
...
},
{
LockedIDs => undef,
...
},
...
]
);
Returns:
Result: 'HASH'
$PreparedFilter = {
QueueIDs => [...],
ServiceIDs => [...],
SortBy => 'TicketNumber',
UserID => '2',
....
};
Result: 'undef'
=cut
sub FilterPrepare {
my ( $Self, %Param ) = @_;
# prepare lookup hash
my %LookupAttributes = (
Locks => 'LockIDs',
Priorities => 'PriorityIDs',
Queues => 'QueueIDs',
Services => 'ServiceIDs',
SLAs => 'SLAIDs',
States => 'StateIDs',
Types => 'TypeIDs',
);
my %Prepared;
FILTER:
for my $Filter ( @{$Param{FilterArray}} ) {
next FILTER if !defined $Filter;
next FILTER if $Filter eq 'undef';
next FILTER if ref $Filter ne 'HASH';
ATTRIBUTE:
for my $Attribute ( sort keys %{$Filter} ) {
next ATTRIBUTE if !defined $Filter->{$Attribute};
next ATTRIBUTE if $Filter->{$Attribute} eq 'undef';
# check if attribute lookup is needed
if ( $LookupAttributes{ $Attribute } ) {
# prepare new filter
my $NewFilter = $Self->_FilterPrepareLookup(
Attribute => $Attribute,
Filter => $Filter->{ $Attribute }
);
# change current attribute
$Attribute = $LookupAttributes{ $Attribute };
# set new filter
$Filter->{ $Attribute } = $NewFilter;
}
if ( $Prepared{$Attribute} ) {
if ( ref $Filter->{$Attribute} eq 'ARRAY' ) {
my @Force = @{$Filter->{$Attribute}};
my @Curr;
my @NewArray;
if ( ref $Prepared{$Attribute} eq 'ARRAY' ) {
@Curr = @{$Prepared{$Attribute}};
}
else {
push(@Curr, $Prepared{$Attribute});
}
for my $Item ( @Force ) {
if ( grep({ $Item eq $_ } @Curr) ) {
push(@NewArray, $Item);
}
}
return if !scalar(@NewArray);
$Prepared{$Attribute} = \@NewArray;
}
elsif (
$Attribute =~ m/^DynamicField_/
&& ref $Filter->{$Attribute} eq 'HASH'
) {
for my $Operator ( keys( %{ $Filter->{$Attribute} } ) ) {
if ( $Prepared{$Attribute}->{$Operator} ) {
my @Force;
my @Curr;
my @NewArray;
if ( ref $Prepared{$Attribute}->{$Operator} eq 'ARRAY' ) {
@Curr = @{$Prepared{$Attribute}->{$Operator}};
}
else {
push(@Curr, $Prepared{$Attribute}->{$Operator});
}
if ( ref $Filter->{$Attribute}->{$Operator} eq 'ARRAY' ) {
@Force = @{$Filter->{$Attribute}->{$Operator}};
}
else {
push(@Force, $Filter->{$Attribute}->{$Operator});
}
for my $Item ( @Force ) {
if ( grep({ $Item eq $_ } @Curr) ) {
push(@NewArray, $Item);
}
}
return if !scalar(@NewArray);
$Prepared{$Attribute}->{$Operator} = \@NewArray;
}
else {
if ( ref $Filter->{$Attribute}->{$Operator} eq 'ARRAY' ) {
$Prepared{$Attribute}->{$Operator} = [];
push(@{$Prepared{$Attribute}->{$Operator}}, @{$Filter->{$Attribute}->{$Operator}});
}
else {
$Prepared{$Attribute}->{$Operator} = $Filter->{$Attribute}->{$Operator};
}
}
}
}
} else {
if ( ref $Filter->{$Attribute} eq 'ARRAY' ) {
$Prepared{$Attribute} = [];
push(@{$Prepared{$Attribute}}, @{$Filter->{$Attribute}});
}
elsif (
$Attribute =~ m/^DynamicField_/
&& ref $Filter->{$Attribute} eq 'HASH'
) {
for my $Operator ( keys( %{ $Filter->{$Attribute} } ) ) {
if ( ref $Filter->{$Attribute}->{$Operator} eq 'ARRAY' ) {
$Prepared{$Attribute}->{$Operator} = [];
push(@{$Prepared{$Attribute}->{$Operator}}, @{$Filter->{$Attribute}->{$Operator}});
}
else {
$Prepared{$Attribute}->{$Operator} = $Filter->{$Attribute}->{$Operator};
}
}
}
else {
$Prepared{$Attribute} = $Filter->{$Attribute};
}
}
}
}
return \%Prepared;
}
sub _FilterPrepareLookup {
my ( $Self, %Param ) = @_;
# declare new filter
my $NewFilter;
# check for filter ref ARRAY
if ( ref( $Param{Filter} ) eq 'ARRAY' ) {
# init new filter
$NewFilter = [];
# process filter entries
for my $Entry ( @{ $Param{Filter} } ) {
# check for attribute Locks
if ( $Param{Attribute} eq 'Locks' ) {
my $LockID = $Kernel::OM->Get('Kernel::System::Lock')->LockLookup(
Lock => $Entry
);
push( @{ $NewFilter }, $LockID );
}
# check for attribute Priorities
elsif ( $Param{Attribute} eq 'Priorities' ) {
my $PriorityID = $Kernel::OM->Get('Kernel::System::Priority')->PriorityLookup(
Priority => $Entry,
);
push( @{ $NewFilter }, $PriorityID );
}
# check for attribute Queues
elsif ( $Param{Attribute} eq 'Queues' ) {
my $QueueID = $Kernel::OM->Get('Kernel::System::Queue')->QueueLookup(
Queue => $Entry,
);
push( @{ $NewFilter }, $QueueID );
}
# check for attribute Services
elsif ( $Param{Attribute} eq 'Services' ) {
my $ServiceID = $Kernel::OM->Get('Kernel::System::Service')->ServiceLookup(
Name => $Entry,
);
push( @{ $NewFilter }, $ServiceID );
}
# check for attribute SLAs
elsif ( $Param{Attribute} eq 'SLAs' ) {
my $SLAID = $Kernel::OM->Get('Kernel::System::SLA')->SLALookup(
Name => $Entry,
);
push( @{ $NewFilter }, $SLAID );
}
# check for attribute States
elsif ( $Param{Attribute} eq 'States' ) {
my $StateID = $Kernel::OM->Get('Kernel::System::State')->StateLookup(
State => $Entry,
);
push( @{ $NewFilter }, $StateID );
}
# check for attribute Types
elsif ( $Param{Attribute} eq 'Types' ) {
my $TypeID = $Kernel::OM->Get('Kernel::System::Type')->TypeLookup(
Type => $Entry
);
push( @{ $NewFilter }, $TypeID );
}
}
}
# handle as scalar
else {
# check for attribute Locks
if ( $Param{Attribute} eq 'Locks' ) {
my $LockID = $Kernel::OM->Get('Kernel::System::Lock')->LockLookup(
Lock => $Param{Filter}
);
$NewFilter = $LockID;
}
# check for attribute Priorities
elsif ( $Param{Attribute} eq 'Priorities' ) {
my $PriorityID = $Kernel::OM->Get('Kernel::System::Priority')->PriorityLookup(
Priority => $Param{Filter},
);
$NewFilter = $PriorityID;
}
# check for attribute Queues
elsif ( $Param{Attribute} eq 'Queues' ) {
my $QueueID = $Kernel::OM->Get('Kernel::System::Queue')->QueueLookup(
Queue => $Param{Filter},
);
$NewFilter = $QueueID;
}
# check for attribute Services
elsif ( $Param{Attribute} eq 'Services' ) {
my $ServiceID = $Kernel::OM->Get('Kernel::System::Service')->ServiceLookup(
Name => $Param{Filter},
);
$NewFilter = $ServiceID;
}
# check for attribute SLAs
elsif ( $Param{Attribute} eq 'SLAs' ) {
my $SLAID = $Kernel::OM->Get('Kernel::System::SLA')->SLALookup(
Name => $Param{Filter},
);
$NewFilter = $SLAID;
}
# check for attribute States
elsif ( $Param{Attribute} eq 'States' ) {
my $StateID = $Kernel::OM->Get('Kernel::System::State')->StateLookup(
State => $Param{Filter},
);
$NewFilter = $StateID;
}
# check for attribute Types
elsif ( $Param{Attribute} eq 'Types' ) {
my $TypeID = $Kernel::OM->Get('Kernel::System::Type')->TypeLookup(
Type => $Param{Filter}
);
$NewFilter = $TypeID;
}
}
# return new filter
return $NewFilter;
}
1;
=end Internal:
=back
=head1 TERMS AND CONDITIONS
This software is part of the KIX project
(L).
This software comes with ABSOLUTELY NO WARRANTY. For details, see the enclosed file
LICENSE for license information (AGPL). If you did not receive this file, see
.
=cut