genericInterface:TicketSearch ignores parameter

[andreK]

Hi,
I am using otrs in version 3.1.7 and I still have some problems to search tickets via the generic interface when I use search-criteria like TicketCreateTimeNewerDate or TicketCloseTimeOlderMinutes. Search tickets by status or by queue works as expected but when I add a "where-clause" like 'TicketCreateTimeNewerDate' => '2012-07-30 00:00:00' the parameter TicketCreateTimeNewerDate seems to be totally ignored.

the debugger shows that this is incoming:

Code: Select all

$VAR1 = {
  'OrderBy' => 'Down',
  'Password' => 'xyz',
  'SortBy' => 'Age',
  'StateIDs' => '1',
  'TicketCreateTimeNewerDate' => '2012-07-30 00:00:00',
  'UserLogin' => 'abc'
};

In the API are several parameters that works as expected and some other seems to be totaly ingored...If possible I want to use the genericInterface instead of writing some SQL-Connections but the return value of the genericInterface is not correct. Alle ticket-IDs with stateId == 1 are returned it does not matter when they are created.

Any ideas why the parameter 'TicketCreateTimeNewerDate' is ignored in Kernel::GenericInterface::Operation::Ticket::TicketSearch? Or have someone an (running) example of a TicketSearch with where-criterias like TicketCreateTimeNewerDate??
Any help is appreciated...
BR
andre

[artjoms15]

Hello. I already posted on the forums once and posted in bugtracker a ready solution, but ohh well. Will do it one more time:
OTRS 3.1.x
TicketSearch.pm approx. line 1290

Code: Select all

         );
            return;
        }

        $SQLExt .= " AND th.create_time <= '"
            . $Self->{DBObject}->Quote( $Param{TicketChangeTimeOlderDate} ) 
			. "' AND th.ticket_id NOT IN (SELECT ticket_history.ticket_id FROM ticket_history where ticket_history.create_time > '"
            . $Self->{DBObject}->Quote( $Param{TicketChangeTimeOlderDate} ) . "')";
    }

An sql statement shoul look like this with "NOT IN" clause added. Cause think logically, if you want to retrieve tickets with change date BEFORE Date X, then you don' t want to get dates which are further that date. It goes to all queries which operates with ticket_history table, cause there are recurring articles appearing.
Although I checked on my machine and the call to "TicketCreateTimeNewerDate" is working fine for me. Don' t know where is the problem Well try with my TicketSearch module maybe it will work

[andreK]

hello, thanks for sharing the code again. I have not found it before...
I replaced my ticketsearch with your version but it still does not work. Since I am not familiar with otrs nor with perl it is little bit difficult to find the correct places in the code. I am using the soap-interface (genericInterface) which I expected in Kernel::GenericInterface::Operation::Ticket::TicketSearch. Your ticketSearch is in Kernel::System::TicketSearch.
I think I have to spent more time with perl and otrs to complete my java-client...
thank you for your answer

[artjoms15]

Well, don't know then, what is your problem. If I may point you then look at TicketSearch.pm at approx line:

Code: Select all

     # get tickets created/escalated newer than xxxx-xx-xx xx:xx date
        if ( $Param{ $Key . 'NewerDate' } ) {
            if (
                $Param{ $Key . 'NewerDate' }
                !~ /\d\d\d\d-(\d\d|\d)-(\d\d|\d) (\d\d|\d):(\d\d|\d):(\d\d|\d)/
                )
            {
                $Self->{LogObject}->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 = $Self->{TimeObject}->TimeStamp2SystemTime(
                String => $Param{ $Key . 'NewerDate' },
            );
            $SQLExt .= " AND $TicketTime{$Key} >= $Time";
        }
    }
            }

These lines are responsible for retrieving newer created/escalated tickets, and it seems legit to me. BUT!!! Make sure that you have defined value $Time, because it is created from NewerMinutes call

[andreK]

After spending some more hours with debugging and testing I found the problem or at least a soltuion. Your last code-snippet shows me the way. Thank you.

The problem is in the genericInterface in kernel/genericInterface/operation/ticket/ticketSearch.pm. For some reason the parameter TicketCreateTimeNewerDate get lost. When you modify the file kernel/genericInterface/operation/ticket/ticketSearch.pm by the parameter will be used in the system/ticketSearch.pm

I added the name TicketCreateTimeNewerDate at kernel/genericInterface/operation/ticket/ticketSearch.pm apporx line:

Code: Select all

sub _GetParams {
    my ( $Self, %Param ) = @_;

    # get single params
    my %GetParam;

    for my $Item (
        qw(TicketNumber Title From To Cc Subject Body
        Agent ResultForm TimeSearchType ChangeTimeSearchType CloseTimeSearchType UseSubQueues
        ArticleTimeSearchType SearchInArchive
        Fulltext ShownAttributes
        ArticleCreateTimePointFormat ArticleCreateTimePoint
        ArticleCreateTimePointStart
        ArticleCreateTimeStart ArticleCreateTimeStartDay ArticleCreateTimeStartMonth
        ArticleCreateTimeStartYear
        ArticleCreateTimeStop ArticleCreateTimeStopDay ArticleCreateTimeStopMonth
        ArticleCreateTimeStopYear
       TicketCreateTimeNewerDate
..............

After this modification the genericinterface returns only tickets created after the specified date.