Hi Renee, sorry, I´ve forgot (I have a definition of UserList in sub Params) - here is the complete source: # -- # Kernel/System/Stats/Static/SomeExampleStats.pm - stats module # Copyright (C) 2001-2009 OTRS AG, http://otrs.org/ # -- # $Id: module-format.xml,v 1.26 2009/01/06 10:34:20 martin Exp $ # -- # This software comes with ABSOLUTELY NO WARRANTY. For details, see # the enclosed file COPYING for license information (GPL). If you # did not receive this file, see http://www.gnu.org/licenses/gpl-2.0.txt. # -- package Kernel::System::Stats::Static::AccountingPerAgent; use strict; use warnings; sub new { my ( $Type, %Param ) = @_; # allocate new hash for object my $Self = {}; bless( $Self, $Type ); # get common objects for ( keys %Param ) { $Self->{$_} = $Param{$_}; } # check all needed objects for (qw(DBObject ConfigObject LogObject UserObject)) { die "Got no $_" if ( !$Self->{$_} ); } return $Self; } sub Param { my $Self = shift; my @Params = (); # get current time my ($s,$m,$h, $D,$M,$Y) = $Self->{TimeObject}->SystemTime2Date( SystemTime => $Self->{TimeObject}->SystemTime(), ); # get one month bevore if ($M == 1) { $M = 12; $Y = $Y - 1; } else { $M = $M -1; } # create possible time selections my %Year = (); foreach ($Y-10..$Y+1) { $Year{$_} = $_; } my %Month = (); foreach (1..12) { my $Tmp = sprintf("%02d", $_); $Month{$_} = $Tmp; } my %UserList = $Self->{UserObject}->UserList( Type => 'Short', Valid => 0, ); push (@Params, { Frontend => 'User', Name => 'User', Multiple => 0, Size => 0, Data => { %UserList, }, }, ); push (@Params, { Frontend => 'Year', Name => 'Year', Multiple => 0, Size => 0, SelectedID => $Y, Data => { %Year, }, }, ); push (@Params, { Frontend => 'Month', Name => 'Month', Multiple => 0, Size => 0, SelectedID => $M, Data => { %Month, }, }, ); return @Params; } sub Run { my ( $Self, %Param ) = @_; my (@Data, $Title); $Title = "Auswertung monatliche Zeiteinheiten Gesamt - $Param{Year}-$Param{Month}"; my @HeadData = ('Ersteller', 'Zeiteinheit', 'Ticket-ID', 'Kunden-ID', 'Agent', 'Beschreibung'); #get the time period my $Year = $Param{Year}; my $Month = $Param{Month}; my $User = $Param{UserList}; #my ($DBObject, $Select ); my $Select = "SELECT ta.create_by, ta.time_unit, ta.ticket_id, t.customer_id, u.login, t.title FROM ticket t, time_accounting ta, users u WHERE t.id = ta.ticket_id AND (ta.change_by = u.id AND ( month( ta.change_time ) = $Month AND year( ta.change_time ) = $Year ) AND ( t.queue_id IN ( 5, 6, 8, 9, 10, 11, 12 ) ) AND ( u.login = $User ) )"; $Self->{DBObject}->Prepare(SQL => $Select); #Read SQL result and put it in @Data while ( my @Row = $Self->{DBObject}->FetchrowArray() ) { push (@Data, [@Row]); }; return ([$Title],[@HeadData], @Data); } 1; -------- Original-Nachricht --------

Datum: Wed, 26 Aug 2009 08:31:44 +0200 Von: "Renee Bäcker" An: "User questions and discussions about OTRS." Betreff: Re: [otrs] Stats-Module and problems with @Params

Petrucci schrieb:

...

Hi, push (@Params, { Frontend => 'User', Name => 'User', Multiple => 0, Size => 0, Data => { %UserList, },

and in sub Run():

my $User = $Param{UserList};

but it fails, cause $User is empty, why?:

Because you said, that the selectbox has the name "User" and not "UserList". You said it with "Name => 'User'"

BTW: You should not use the Variable in the SQL statement. Use the Bind mechanism instead as this will improve security (any special characters are quoted automatically).

Instead of

$Self->{DBObject}->Prepare( SQL => " ... UserLogin = $User" );

do this:

$Self->{DBObject}->Prepare( SQL => " ... UserLogin = ?", Bind => [ \$User ], );

Cheers, Renée

--

Perl-Magazin: http://perl-magazin.de Perl-Nachrichten: http://perl-nachrichten.de --------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

NEW! ENTERPRISE SUBSCRIPTION - Get more information NOW! http://www.otrs.com/en/support/enterprise-subscription/

-- Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3 - sicherer, schneller und einfacher! http://portal.gmx.net/de/go/chbrowser

Hi Renee, sorry, did: sub Param { ... my %UserList = $Self->{UserObject}->UserList( Type => 'Short', Valid => 0, ); push (@Params, { Frontend => 'User', Name => 'UserList', Multiple => 0, Size => 0, #SelectedID => $U, Data => { %UserList, }, }, ); } sub Run { ... my $User = $Param{UserList}; ... } but it still doesn´t work... /var/log/otrs.log: [Wed Aug 26 09:26:28 2009][Error][Kernel::System::Stats::Static::AccountingPerAgent::Run][145] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') )' at line 19, SQL: 'SELECT ta.create_by, ta.time_unit, ta.ticket_id, t.customer_id, u.login, t.title FROM ticket t, time_accounting ta, users u WHERE t.id = ta.ticket_id AND (ta.change_by = u.id AND ( month( ta.change_time ) = 7 AND year( ta.change_time ) = 2009 ) AND ( t.queue_id IN ( 5, 6, 8, 9, 10, 11, 12 ) ) AND ( u.login = ) )' -------- Original-Nachricht --------

Datum: Wed, 26 Aug 2009 09:18:31 +0200 Von: "Renee Bäcker" An: "User questions and discussions about OTRS." Betreff: Re: [otrs] Stats-Module and problems with @Params

Hi Guenther,

you already posted the code where you defined the Variable %UserList, but that is irrelevant. The important part for the HTML form creation is what's in @Params.

There you have

...

push (@Params, { Frontend => 'User', Name => 'User', Multiple => 0, Size => 0, Data => { %UserList, }, }, );

This adds a field to the form where the field has a label 'User' (Frontend => 'User'). The name of the field is also 'User' (Name => 'User'). The is the name of the parameter that the request sends to the script.

You have two ways to make your script work:

Either

push @Params, { Frontend => 'User', Name => 'UserList', ... }

*OR*

my $User = $Params{User};

Cheers, Renée

Please use the Bind mechanism I described in my last mail...

--

Perl-Magazin: http://perl-magazin.de Perl-Nachrichten: http://perl-nachrichten.de --------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

NEW! ENTERPRISE SUBSCRIPTION - Get more information NOW! http://www.otrs.com/en/support/enterprise-subscription/

-- GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT! Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01

guenther.rasch@gmx.de schrieb:

Hi Renee,

short question, is that right (u.login = ?...):

Yes, that's correct. I have used your module, made the changes I proposed and everything runs fine. So there seems to be another problem. Do you have replaced the old module for your installation? Does you OTRS instance run under mod_perl? If so, can you force Apache to reload the stats module? Cheers, Renée

$Self->{DBObject}->Prepare(SQL => "SELECT ta.create_by, ta.time_unit, ta.ticket_id, t.customer_id, u.login, t.title FROM ticket t, time_accounting ta, users u WHERE t.id = ta.ticket_id AND (ta.change_by = u.id AND ( month( ta.change_time ) = $Month AND year( ta.change_time ) = $Year ) AND ( t.queue_id IN ( 5, 6, 8, 9, 10, 11, 12 ) ) AND ( u.login = ? ) )", Bind => [ \$User ], );

Regards Günther

-- Perl-Magazin: http://perl-magazin.de Perl-Nachrichten: http://perl-nachrichten.de

guenther.rasch@gmx.de schrieb:

I´ve restarte Apache, but still the csv-file is empty, but no logging entry in /var/log/otrs.log anymore...!

Then you have to check whether your SQL does the right thing or not ;-) Renee -- Perl-Magazin: http://perl-magazin.de Perl-Nachrichten: http://perl-nachrichten.de