Implement functionality to export search results as xlsx

[aph]

I'm using OTRS 3.3.8 and would like to provide the possibility to export search results as Excel similar to the functionality in the version 4.0. An upgrade to 4.0 for this one functionality is not an option for me.

I compared AgentTicketSearch.pm in both the versions and tried to edit the relevant section 3.3 accordingly. I replaced the following in 3.3.8

Code: Select all

# #CSV output
        if ( $GetParam{ResultForm} eq 'CSV' ) {

            # create head (actual head and head for data fill)
            my @TmpCSVHead = @{ $Self->{Config}->{SearchCSVData} };
            my @CSVHead    = @{ $Self->{Config}->{SearchCSVData} };

            # include the selected dynamic fields in CVS results
            DYNAMICFIELD:
            for my $DynamicFieldConfig ( @{ $Self->{CSVDynamicField} } ) {
                next DYNAMICFIELD if !IsHashRefWithData($DynamicFieldConfig);
                next DYNAMICFIELD if !$DynamicFieldConfig->{Name};
                next DYNAMICFIELD if $DynamicFieldConfig->{Name} eq '';

                push @TmpCSVHead, 'DynamicField_' . $DynamicFieldConfig->{Name};
                push @CSVHead,    $DynamicFieldConfig->{Label};
            }

            my @CSVData;
            for my $TicketID (@ViewableTicketIDs) {

                # get first article data
                my %Data = $Self->{TicketObjectSearch}->ArticleFirstArticle(
                    TicketID      => $TicketID,
                    Extended      => 1,
                    DynamicFields => 1,
                );

                if ( !%Data ) {

                    # get ticket data instead
                    %Data = $Self->{TicketObjectSearch}->TicketGet(
                        TicketID      => $TicketID,
                        DynamicFields => 1,
                    );

                    # set missing information
                    $Data{Subject} = $Data{Title} || 'Untitled';
                    $Data{Body} = $Self->{LayoutObject}->{LanguageObject}->Get(
                        'This item has no articles yet.'
                    );
                    $Data{From} = '--';
                }

                for my $Key (qw(State Lock)) {
                    $Data{$Key} = $Self->{LayoutObject}->{LanguageObject}->Get( $Data{$Key} );
                }

                $Data{Age} = $Self->{LayoutObject}->CustomerAge( Age => $Data{Age}, Space => ' ' );

                # get whole article (if configured!)
                if ( $Self->{Config}->{SearchArticleCSVTree} ) {
                    my @Article = $Self->{TicketObjectSearch}->ArticleGet(
                        TicketID      => $TicketID,
                        DynamicFields => 0,
                    );

                    if ( $#Article == -1 ) {
                        $Data{ArticleTree}
                            .= 'This item has no articles yet.';
                    }
                    else
                    {
                        for my $Articles (@Article) {
                            if ( $Articles->{Body} ) {
                                $Data{ArticleTree}
                                    .= "\n-->||$Articles->{ArticleType}||$Articles->{From}||"
                                    . $Articles->{Created}
                                    . "||<--------------\n"
                                    . $Articles->{Body};
                            }
                        }
                    }
                }

                # customer info (customer name)
                if ( $Data{CustomerUserID} ) {
                    $Data{CustomerName} = $Self->{CustomerUserObject}->CustomerName(
                        UserLogin => $Data{CustomerUserID},
                    );
                }

                # user info
                my %UserInfo = $Self->{UserObject}->GetUserData(
                    User => $Data{Owner},
                );

                # merge row data
                my %Info = (
                    %Data,
                    %UserInfo,
                    AccountedTime =>
                        $Self->{TicketObjectSearch}
                        ->TicketAccountedTimeGet( TicketID => $TicketID ),
                );

                my @Data;
                for my $Header (@TmpCSVHead) {

                    # check if header is a dynamic field and get the value from dynamic field
                    # backend
                    if ( $Header =~ m{\A DynamicField_ ( [a-zA-Z\d]+ ) \z}xms ) {

                        # loop over the dynamic fields configured for CSV output
                        DYNAMICFIELD:
                        for my $DynamicFieldConfig ( @{ $Self->{CSVDynamicField} } ) {
                            next DYNAMICFIELD if !IsHashRefWithData($DynamicFieldConfig);
                            next DYNAMICFIELD if !$DynamicFieldConfig->{Name};

                            # skip all fields that does not match with current field name ($1)
                            # with out the 'DynamicField_' prefix
                            next DYNAMICFIELD if $DynamicFieldConfig->{Name} ne $1;

                            # get the value as for print (to correctly display)
                            my $ValueStrg = $Self->{BackendObject}->DisplayValueRender(
                                DynamicFieldConfig => $DynamicFieldConfig,
                                Value              => $Info{$Header},
                                HTMLOutput         => 0,
                                LayoutObject       => $Self->{LayoutObject},
                            );
                            push @Data, $ValueStrg->{Value};

                            # terminate the DYNAMICFIELD loop
                            last DYNAMICFIELD;
                        }
                    }

                    # otherwise retrieve data from article
                    else {
                        push @Data, $Info{$Header};
                    }
                }
                push @CSVData, \@Data;
            }

            # get Separator from language file
            my $UserCSVSeparator = $Self->{LayoutObject}->{LanguageObject}->{Separator};

            if ( $Self->{ConfigObject}->Get('PreferencesGroups')->{CSVSeparator}->{Active} ) {
                my %UserData = $Self->{UserObject}->GetUserData( UserID => $Self->{UserID} );
                $UserCSVSeparator = $UserData{UserCSVSeparator} if $UserData{UserCSVSeparator};
            }

            my %HeaderMap = (
                TicketNumber => 'Ticket Number',
                CustomerName => 'Customer Realname',
            );

            my @CSVHeadTranslated
                = map { $Self->{LayoutObject}->{LanguageObject}->Get( $HeaderMap{$_} || $_ ); }
                @CSVHead;

            my $CSV = $Self->{CSVObject}->Array2CSV(
                Head      => \@CSVHeadTranslated,
                Data      => \@CSVData,
                Separator => $UserCSVSeparator,
            );

            # return csv to download
            my $CSVFile = 'ticket_search';
            my ( $s, $m, $h, $D, $M, $Y ) = $Self->{TimeObject}->SystemTime2Date(
                SystemTime => $Self->{TimeObject}->SystemTime(),
            );
            $M = sprintf( "%02d", $M );
            $D = sprintf( "%02d", $D );
            $h = sprintf( "%02d", $h );
            $m = sprintf( "%02d", $m );
            return $Self->{LayoutObject}->Attachment(
                Filename    => $CSVFile . "_" . "$Y-$M-$D" . "_" . "$h-$m.csv",
                ContentType => "text/csv; charset=" . $Self->{LayoutObject}->{UserCharset},
                Content     => $CSV,
            );
        }

with

Code: Select all

# CSV and Excel output
        if (
            $GetParam{ResultForm} eq 'CSV'
            ||
            $GetParam{ResultForm} eq 'Excel'
            )
        {

            # create head (actual head and head for data fill)
            my @TmpCSVHead = @{ $Self->{Config}->{SearchCSVData} };
            my @CSVHead    = @{ $Self->{Config}->{SearchCSVData} };

            # include the selected dynamic fields in CVS results
            DYNAMICFIELD:
            for my $DynamicFieldConfig ( @{ $Self->{CSVDynamicField} } ) {
                next DYNAMICFIELD if !IsHashRefWithData($DynamicFieldConfig);
                next DYNAMICFIELD if !$DynamicFieldConfig->{Name};
                next DYNAMICFIELD if $DynamicFieldConfig->{Name} eq '';

                push @TmpCSVHead, 'DynamicField_' . $DynamicFieldConfig->{Name};
                push @CSVHead,    $DynamicFieldConfig->{Label};
            }

            my @CSVData;
            for my $TicketID (@ViewableTicketIDs) {

                # get first article data
                my %Data = $Self->{TicketObjectSearch}->ArticleFirstArticle(
                    TicketID      => $TicketID,
                    Extended      => 1,
                    DynamicFields => 1,
                );

                if ( !%Data ) {

                    # get ticket data instead
                    %Data = $Self->{TicketObjectSearch}->TicketGet(
                        TicketID      => $TicketID,
                        DynamicFields => 1,
                    );

                    # set missing information
                    $Data{Subject} = $Data{Title} || 'Untitled';
                    $Data{Body} = $Self->{LayoutObject}->{LanguageObject}->Get(
                        'This item has no articles yet.'
                    );
                    $Data{From} = '--';
                }

                for my $Key (qw(State Lock)) {
                    $Data{$Key} = $Self->{LayoutObject}->{LanguageObject}->Translate( $Data{$Key} );
                }

                $Data{Age} = $Self->{LayoutObject}->CustomerAge(
                    Age   => $Data{Age},
                    Space => ' '
                );

                # get whole article (if configured!)
                if ( $Self->{Config}->{SearchArticleCSVTree} ) {
                    my @Article = $Self->{TicketObjectSearch}->ArticleGet(
                        TicketID      => $TicketID,
                        DynamicFields => 0,
                    );

                    if ( $#Article == -1 ) {
                        $Data{ArticleTree}
                            .= 'This item has no articles yet.';
                    }
                    else
                    {
                        for my $Articles (@Article) {
                            if ( $Articles->{Body} ) {
                                $Data{ArticleTree}
                                    .= "\n-->||$Articles->{ArticleType}||$Articles->{From}||"
                                    . $Articles->{Created}
                                    . "||<--------------\n"
                                    . $Articles->{Body};
                            }
                        }
                    }
                }

                # customer info (customer name)
                if ( $Data{CustomerUserID} ) {
                    $Data{CustomerName} = $Self->{CustomerUserObject}->CustomerName(
                        UserLogin => $Data{CustomerUserID},
                    );
                }

                # user info
                my %UserInfo = $Self->{UserObject}->GetUserData(
                    User => $Data{Owner},
                );

                # merge row data
                my %Info = (
                    %Data,
                    %UserInfo,
                    AccountedTime =>
                        $Self->{TicketObjectSearch}->TicketAccountedTimeGet( TicketID => $TicketID ),
                );

                my @Data;
                for my $Header (@TmpCSVHead) {

                    # check if header is a dynamic field and get the value from dynamic field
                    # backend
                    if ( $Header =~ m{\A DynamicField_ ( [a-zA-Z\d]+ ) \z}xms ) {

                        # loop over the dynamic fields configured for CSV output
                        DYNAMICFIELD:
                        for my $DynamicFieldConfig ( @{ $Self->{CSVDynamicField} } ) {
                            next DYNAMICFIELD if !IsHashRefWithData($DynamicFieldConfig);
                            next DYNAMICFIELD if !$DynamicFieldConfig->{Name};

                            # skip all fields that does not match with current field name ($1)
                            # with out the 'DynamicField_' prefix
                            next DYNAMICFIELD if $DynamicFieldConfig->{Name} ne $1;

                            # get the value as for print (to correctly display)
                            my $ValueStrg = $Self->{BackendObject}->DisplayValueRender(
                                DynamicFieldConfig => $DynamicFieldConfig,
                                Value              => $Info{$Header},
                                HTMLOutput         => 0,
                                LayoutObject       => $Self->{LayoutObject},
                            );
                            push @Data, $ValueStrg->{Value};

                            # terminate the DYNAMICFIELD loop
                            last DYNAMICFIELD;
                        }
                    }

                    # otherwise retrieve data from article
                    else {
                        push @Data, $Info{$Header};
                    }
                }
                push @CSVData, \@Data;
            }

            # get Separator from language file
            my $UserCSVSeparator = $Self->{LayoutObject}->{LanguageObject}->{Separator};

            if ( $Self->{ConfigObject}->Get('PreferencesGroups')->{CSVSeparator}->{Active} ) {
                my %UserData = $Self->{UserObject}->GetUserData( UserID => $Self->{UserID} );
                $UserCSVSeparator = $UserData{UserCSVSeparator} if $UserData{UserCSVSeparator};
            }

            my %HeaderMap = (
                TicketNumber => 'Ticket Number',
                CustomerName => 'Customer Realname',
            );

            my @CSVHeadTranslated = map { $Self->{LayoutObject}->{LanguageObject}->Translate( $HeaderMap{$_} || $_ ); }
                @CSVHead;

            my $FileName = 'ticket_search';
            my ( $s, $m, $h, $D, $M, $Y ) = $Self->{TimeObject}->SystemTime2Date(
                SystemTime => $Self->{TimeObject}->SystemTime(),
            );
            $M = sprintf( "%02d", $M );
            $D = sprintf( "%02d", $D );
            $h = sprintf( "%02d", $h );
            $m = sprintf( "%02d", $m );

            # generate CSV output
            if ( $GetParam{ResultForm} eq 'CSV' ) {
                my $CSV = $Self->{CSVObject}->Array2CSV(
                    Head      => \@CSVHeadTranslated,
                    Data      => \@CSVData,
                    Separator => $UserCSVSeparator,
                );

                # return csv to download
                return $Self->{LayoutObject}->Attachment(
                    Filename    => $FileName . "_" . "$Y-$M-$D" . "_" . "$h-$m.csv",
                    ContentType => "text/csv; charset=" . $Self->{LayoutObject}->{UserCharset},
                    Content     => $CSV,
                );
            }

            # generate Excel output
            elsif ( $GetParam{ResultForm} eq 'Excel' ) {
                my $Excel = $Self->{CSVObject}->Array2CSV(
                    Head   => \@CSVHeadTranslated,
                    Data   => \@CSVData,
                    Format => 'Excel',
                );

                # return Excel to download
                return $Self->{LayoutObject}->Attachment(
                    Filename => $FileName . "_" . "$Y-$M-$D" . "_" . "$h-$m.xlsx",
                    ContentType =>
                        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                    Content => $Excel,
                );
            }
        }

I also added the option Excel under

Code: Select all

$Param{ResultFormStrg} = $Self->{LayoutObject}->BuildSelection

However, I get an ajax communication error and no hints in apache log.
EDIT: I get an xlsx generated, but the output is still a csv and not xlsx as I expected
Any pointers as to where I'm going wrong?

[pab]

Looks like you are just renaming the csv to xlsx, without changing the content. Changing just the content type is far from doing the trick.
Have a look at Excel::Writer::XLSX (or Spreadsheet::WriteExcel or similar) at cpan.org.

[aph]

Thanks for a quick reply. I had a look at the link you suggested. I somehow still don't get how the OTRS version 4 generates an xlsx file with a similar code? I don't see Excel::Writer::XLSX in AgentTicketSearch.pm of version 4.
Do I have to install a new perl module? On running the CheckModules script on the 4.0 I don't see any module indicating any XLS writer

[pab]

I have not looked at the v4 code, nor have I tried the search export. Are you sure that OTRS 4 is producing an genuine xlsx?

[aph]

Yes 4.0 is producing a genuine xlsx

[crythias]

For fun, maybe I'd throw the following in ... somwhere after TicketGet.

Code: Select all

$Data{TicketNumber} = "'" . $Data{TicketNumber};

[aph]

For some odd reason the csv output is not ; separated anymore, instead a comma appears as separator (independent of your suggestion crythias). I replaced the AgentTicketSearch.pm as well as CSV.pm with originals, but the problem still persists. I have also rebuilt config, restarted apache, cleared cache, also ran setPermissions

EDIT: This problem was resolved after I removed and added the field TicketNumber under Ticket::Frontend::AgentTicketSearch###SearchCSVData

[pab]

This has got nothing to do with crythias' tips for exercise and amusement. If you leave out the apostrophe excel will recognize your ticket number as a figure and round it down (in case you are using the default ticket number format consisting of 15 or 16 digits, depending on your system ID).

[aph]

I realized the problem with large numbers, that is why I wanted to change the csv output in the first place.I then wanted to go a step further and export the data as an xlsx instead of plain csv.

To your solution crythias. I placed $Data{TicketNumber} = "'" . $Data{TicketNumber}; after TicketGet as follows:

Code: Select all

 my @CSVData;
            for my $TicketID (@ViewableTicketIDs) {

                # get first article data
                my %Data = $Self->{TicketObjectSearch}->ArticleFirstArticle(
                    TicketID      => $TicketID,
                    Extended      => 1,
                    DynamicFields => 1,
                );

                if ( !%Data ) {

                    # get ticket data instead
                    %Data = $Self->{TicketObjectSearch}->TicketGet(
                        TicketID      => $TicketID,
                        DynamicFields => 1,
                    );
$Data{TicketNumber} = "'" . $Data{TicketNumber};

Yet no success

[jojo]

update to OTRS4 instead of breaking your system!

[aph]

Thanks.