December 2004 - dev - OTRS mailing lists

Cosmetic HTML patch
by Kristoffer Gleditsch 15 Dec '04

15 Dec '04

Hi, Stumbled across some HTML buglets while fixing something else. This small patch fixes some syntax errors (<input ...> -> <input .../>) and removed a spurious tag: Index: Kernel/Output/HTML/Standard/AgentCompose.dtl =================================================================== --- Kernel/Output/HTML/Standard/AgentCompose.dtl (revision 97) +++ Kernel/Output/HTML/Standard/AgentCompose.dtl (working copy) @@ -158,9 +158,14 @@  <tr><td>$QData{"Filename"}</td><td>$QData{"Filesize"}</td><td align="right"><input type="submit" name="AttachmentDelete$QData{"FileID"}" value="$Text{"Delete"}"></td></tr>  - <tr><td colspan="2"> - <input name="file_upload" type="file" size="30" class="fixed"></td><td align="right"><input type="submit" name="AttachmentUpload" value="$Text{"Attach"}"><a name="attachment"> - </td></tr> + <tr> + <td colspan="2"> + <input name="file_upload" type="file" size="30" class="fixed" /> + </td> + <td align="right"> + <input type="submit" name="AttachmentUpload" value="$Text{"Attach"}" class="fixed" /> + </td> + </tr> </table> </td> </tr> Regards, -- Kristoffer.

1 0

Adding Additional 'ticket' Fields
by Graeme Brough - Sun Remote Support Centre 13 Dec '04

13 Dec '04

Hello, I am relatively new to PERL, so please accept my apologies if the answer to my question is simple or obvious. I am currently tuning OTRS 1.2 to be more specific to our local environment, and wish to add additional customer information fields via the 'ticket' SQL table. I have added the required columns to the table, but am encountering problems in getting OTRS to read from and write to these columns. I have amended amended various modules and dtl files but to no avail. Also I noted that in 'AgentEmailNew.dtl' the Customer ID is held in a variable named 'CustomerId', however by the time 'Ticket.pm is called, Customer Name has been passed to a CGI Param named 'No' and I cannot locate the transition. Please can anyone explain the correct procedure for the addition of the new fields and how 'Customer ID' is passed to '$Param{No}'. Many thanks in advance. Regards, Graeme ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

1 0

OTRS Report module
by Damir Dzeko 10 Dec '04

10 Dec '04

Hi, I wrote a report module for OTRS. Here's what README file provided in tar.gz in attached to this message says. Report module for OTRS (Ticket Timings) --------------------------------------- Created by Damir Dzeko, based on code of AgentUtilities module written by Martin Edenhofer, the author of OTRS, with a lot of help of Edgar Meij. Unpack this where appropriate. I've used slightly modified version of Standard theme called Redwood and so the template files and icons are located there. Feel free to move them wherever you need them. You'll need to manually edit files: Kernel/Config/Defaults.pm Kernel/Config/ModulesCustom.pm Parts that need to be added/edited are provided in files: Kernel/Config/Defaults.pm.Report Kernel/Config/ModulesCustom.pm.Report which can later be removed. For date calculations module Date::Manip is used. It is not provided and you'll have to install it manually either from CPAN or as a package in your favourite software distribution. Me being a user of Debian GNU/Linux did it with: apt-get install libdate-manip-perl For business time calculations (workhours and workdays) Date::Manip needs to know of holidays in your country. I live in Croatia and so I wrote the .DateManip.cnf.hr which you can modify and use. You'll need to rename it to .DateManip.cnf and place in the home-directory of user who will be running scripts. On Debian systems such user is called www-data. So I placed .DateManip.cnf in it's home: /var/www. For you it might be different. I refer you to read documentation of Date::Manip. User who will be accessing the module through web-interface needs to have sufficient privileges in stats group or else OTRS wont even show him the icon/link to module. Business logic is located in the module itself: Kernel/Modules/Report.pm. There are few things to consider there. I used very simple logic that calculates three timings: - total time the ticket got updated (from first to last history record) - time the customer spent waiting for first real answer from agent - total time customer spent waiting for answer from agent All three timings are returned as both real time (real wallclock minutes) and business minutes. A day of business is calculated as 8 hours (hard- coded) if the period falls into one working week (5 days). For larger periods a day is 24 hours (converted to minutes that is 1440). I did not cover phone-calls nor cases when agent opens ticket (and many other strange cases when ticket gets reopened, ...). I encourage you to do it yourself by modifying function TicketTimes(). Happy hacking, Damir

2 1

Freetext search problem
by Matija Grabnar 09 Dec '04

09 Dec '04

I have noticed that the way the free text seach is written currently, you can't find a text unless you know exactly in which field it was written. For instance: if a ticket has a freetexts (I wrote as name = value, I hope it's not too confusing) freetext1. foo=bla freetext2. bar=blu freetext3. baz=yadda And you search for yadda (or even baz = yadda) the search will return nothing, UNLESS you remember (and have a sitewide rule) that baz is always in the third field, and thus enter the search parameters into the THIRD field of the search page. I want to fix that, and make it so that if you look for a freetext, it will be found regardless in which of the freetexts it can be found. I am good enough with Perl that I can fix that and submit a patch, but I would like to know if any developers have some ideas about free text that I need to take into account.

1 0

A few stats patches
by Kristoffer Gleditsch 06 Dec '04

06 Dec '04

Hi! I've had to make some small changes to a few files in order to make things run smoothly in our environment. Take'em if you want'em. :-) * Use Days_in_Month instead of assuming all months have 31 days. I don't know about MySQL/PostgreSQL, but Oracle objects to dates like 2004-11-31: * Typo ("History") * Don't call TicketGet on undef TicketID's: Regards, -- Kristoffer.

1 0

Article free text not displaying
by Damir Dzeko 05 Dec '04

05 Dec '04

Hi, I've noticed and corrected a bug with Article FreeKey/FreeText not displaying in CustomerZoom.pm (CustomerTicketZoom.dtl) page. In mentioned module replace FreeValue# with FreeText# # do some strips && quoting foreach (qw(To Cc From Subject FreeKey1 FreeKey2 FreeKey3 FreeText1 FreeText2 FreeText3)) { # charset encode $Param{"Article::$_"} = $Self->{LayoutObject}->{LanguageObject}->CharsetConvert( Text => $Article{$_}, From => $Article{ContentCharset}, ); } And in template do the same thing. I also added CSS style for article attributes. <dtl if ($Data{"Article::FreeKey1"} ne "") { $Data{"ArticleFreeString1"} = "<tr><td valign="top" class="afttab"><b>$QData{"Article::FreeKey1"}:</b></td><td class="afttab"> $QData{"Article::FreeText1"}</td></tr>"; }> <dtl if ($Data{"Article::FreeKey2"} ne "") { $Data{"ArticleFreeString2"} = "<tr><td valign="top" class="afttab"><b>$QData{"Article::FreeKey2"}:</b></td><td class="afttab"> $QData{"Article::FreeText2"}</td></tr>"; }> <dtl if ($Data{"Article::FreeKey3"} ne "") { $Data{"ArticleFreeString3"} = "<tr><td valign="top" class="afttab"><b>$QData{"Article::FreeKey3"}:</b></td><td class="afttab"> $QData{"Article::FreeText3"}</td></tr>"; }> $Data{"ArticleFreeString1"} $Data{"ArticleFreeString2"} $Data{"ArticleFreeString3"} # -- The style I called afttab (article free text table), and defined it as: .afttab { background-color: #eeffee; color:#008000; border: 0px; } in customer-css.dtl, but I'd rather leave that to graph' designer. :) Cheers, D.

1 0

eval and variables in templates
by Damir Dzeko 05 Dec '04

05 Dec '04

Hi, I've just found out a thing with templates. This does not work as advertised: <dtl if ($Data{"TicketLink6"} ne "") { $Data{"TicketLinkString6"} = "<a href="$Env{"Baselink"}Action=AgentZoom&TicketID=$Data{"TicketLinkID6"}">$Data{"TicketLink6"}</a> "; }> <dtl if ($Data{"TicketLink7"} ne "") { $Data{"TicketLinkString7"} = "<a href="$Env{"Baselink"}Action=AgentZoom&TicketID=$Data{"TicketLinkID7"}">$Data{"TicketLink7"}</a> "; }> # build one link string <dtl set $Data{"TicketLinkString"} = "$Data{"TicketLinkString1"}$Data{"TicketLinkString2"}$Data{"TicketLinkString3"}$Data{"TicketLinkString4"}$Data{"TicketLinkString5"}$Data{"TicketLinkString6"}$Data{"TicketLinkString7"}"> # show link row if needed <dtl if ($Data{"TicketLinkString"} ne "") { $Data{"TicketLinkStringTable"} = "<tr valign="top"><td><b>$Text{"Link"}:</b></td><td>$Data{"TicketLinkString"}</td></tr>"; }> $Data{"TicketLinkStringTable"} It always shows a row, even when it's not _needed_. That is because in template evaluation it gets eval'ed as: '$Data{"TicketLinkString1"}$Data{"TicketLinkString2"}$Data{"TicketLinkString3"}$Data{"TicketLinkString4"}$Data{"TicketLinkString5"}$Data{"TicketLinkString6"}$Data{"TicketLinkString7"}' ne '' which yields true. Et voila - you've got permanent "Link:" in AgentZoomStatus page. I guess that's because variable replacement happens after eval phase. Kind Regards, Damir

1 1

Re: [otrs] Root has lost admin permissions
by Damir Dzeko 04 Dec '04

04 Dec '04

Thomas Earl wrote: > I mistakenly removed permissions from root (and not given it to any other > users) to the admin section. > > I fixed it this morning by dropping and rebuilding the otrs database. You could have fixed it using some other account which has admin privileges. If not - only by editing database manually. It's a bit involving. When you connect to database you should issue command: SELECT id FROM groups WHERE name = 'admin'; And it would return you ID of 'admin' group, which is 2 on my system, and I presume it is the same on all other clean installs of version 1.3.x. Then you would add missing record using INSERT INTO group_user (user_id, group_id, permission_key, permission_value, create_by, create_time, change_by, change_time) VALUES (1, 2, 'rw', 1, 1, current_timestamp, 1, current_timestamp); or update existing one with UPDATE group_user SET permission_value = 1 WHERE user_id = 1 AND group_id = 2 AND permission_key = 'rw'; Maybe it would be a good idea to create a script which could be used in emergencies such as this. I wrote one (attached). Not thoroughly tested. I give it as a contribution to OTRS project. Cheers, D. #!/usr/bin/perl -w # -- # FixAdminPrivs.pl - return admin privileges to given user # -- # $Id:$ # -- # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA # -- # use ../ as lib location use File::Basename; use FindBin qw($RealBin); use lib dirname($RealBin); use lib dirname($RealBin)."/Kernel/cpan-lib"; use strict; use vars qw($VERSION); $VERSION = '$Revision: 0.01 $'; $VERSION =~ s/^\$.*:\W(.*)\W.+?$/$1/; use Kernel::Config; use Kernel::System::Log; use Kernel::System::DB; use Kernel::System::User; use Kernel::System::Group; use Getopt::Long qw(:config no_ignore_case); my $Debug = $ENV{OTRS_DEBUG} || 0; $| = 1; # unbuffered output use constant DEFAULT_USER => 'root@localhost'; # Name of this executable file # (without directory and extensions) my $ProgramName; ($ProgramName = $0) =~ s!.*/!!; # strip dir $ProgramName =~ s!\.[^.]+$!!; # strip last ext # -- # common objects # -- my %CommonObject = (); $CommonObject{ConfigObject} = Kernel::Config->new(); $CommonObject{LogObject} = Kernel::System::Log->new( LogPrefix => "OTRS-$ProgramName", %CommonObject, ); $CommonObject{DBObject} = Kernel::System::DB->new(%CommonObject); $CommonObject{UserObject} = Kernel::System::User->new(%CommonObject); $CommonObject{GroupObject} = Kernel::System::Group->new(%CommonObject); # preserve original args my $args = join(' ', @ARGV); my ($username, $password, $addprivs, $addgroup, $help); # set default values $username = DEFAULT_USER; $args or usage(); # check args GetOptions ('username|u=s' => \$username, 'password|p=s' => \$password, 'addprivs|a' => \$addprivs, 'addgroup|g' => \$addgroup, 'help|h' => \$help, ) || die "Error in options (use --help)\n"; $help and usage(); my $userTable = $CommonObject{ConfigObject}->Get('DatabaseUserTable') || 'system_user'; my $userTable_UserID = $CommonObject{ConfigObject}->Get('DatabaseUserTableUserID') || 'id'; my $userTable_Login = $CommonObject{ConfigObject}->Get('DatabaseUserTableUser') || 'login'; die "Invalid characters in username!\n" if ($username and $username !~ /[-\.\w\@]+/); # first - validate user if it was accidentally set invalid my $SQL = "UPDATE $userTable SET valid_id = 1". " WHERE $userTable_UserID = 1 OR $userTable_Login = '$username'"; $CommonObject{DBObject}->Do(SQL => $SQL) or die "Failed to validate admin user record!\n"; # now get the admin user's data my $user_id = $CommonObject{UserObject}->GetUserIdByName( User => $username, ); if (! $user_id) { print STDERR "User '$username' not found in database!\n"; # user not there -- insert it my %Param = ( Firstname => 'Admin', Lastname => 'OTRS', Login => $username, Pw => '/', ValidID => 1, UserID => 1, Email => $username, ); print STDERR "Inserting user '$username' record in database...\n"; $user_id = $CommonObject{UserObject}->UserAdd(%Param); die "UserAdd failed!\n" if (! $user_id); print STDERR " inserted under user_id = $user_id\n"; # now set the user's id to 1 $SQL = "DELETE FROM $userTable WHERE id = 1"; $CommonObject{DBObject}->Do(SQL => $SQL) or die "Failed to delete old admin user record!\n"; $SQL = "UPDATE $userTable SET $userTable_UserID = 1 WHERE $userTable_UserID = $user_id"; $CommonObject{DBObject}->Do(SQL => $SQL) or die "Failed to reset admin user id to 1!\n"; print STDERR " replaced user_id = $user_id with user_id = 1\n"; $user_id = 1; } # set password? if ($password) { $CommonObject{UserObject}->SetPassword( UserLogin => $username, PW => $password ) or die "Failed to set password for '$username'!\n"; print STDERR "Changed password of user '$username'.\n"; } my $group_id; if ($addprivs) { # check admin group my %AdminGroup = GroupGet(\%CommonObject, Name => 'admin'); if (scalar(keys(%AdminGroup))) { $group_id = $AdminGroup{ID}; } else { if ($addgroup) { $group_id = $CommonObject{GroupObject}->GroupAdd( Name => 'admin', ValidID => 1, UserID => $user_id, ) or die "Failed to add 'admin' group!\n"; } else { die "Cannot retrieve data for admin group.\nUse --addgroup to fix that!\n"; } } # # delete old privilege record # $SQL = "DELETE FROM group_user ". # " WHERE user_id = $user_id ". # " AND group_id = $group_id ". # " AND permission_key = 'rw' "; # $CommonObject{DBObject}->Do(SQL => $SQL) # or die "Failed to delete old permission record!\n"; # recreate privilege record $CommonObject{GroupObject}->GroupMemberAdd( UID => $user_id, GID => $group_id, UserID => $user_id, Permission => { 'rw' => 1}, ) or die "Failed to add admin 'rw' permission to user '$username'!\n"; } exit(0); sub GroupGet { my $cObj = shift; my %Param = @_; # check needed stuff if (!$Param{Name} and !$Param{ID}) { $cObj->{LogObject}->Log(Priority => 'error', Message => "Need Group Name or ID!"); return; } # db quote foreach (keys %Param) { $Param{$_} = $cObj->{DBObject}->Quote($Param{$_}); } # sql my $where; if ($Param{ID}) { $where = " id = $Param{ID}"; $Param{Name} = undef; } else { $where = " name = '$Param{Name}' "; } my $SQL = "SELECT id, name, valid_id, comments ". " FROM ". " groups ". " WHERE ". " $where"; if ($cObj->{DBObject}->Prepare(SQL => $SQL)) { my %GroupData = (); while (my @Data = $cObj->{DBObject}->FetchrowArray()) { %GroupData = ( ID => $Data[0], Name => $Data[1], Comment => $Data[3], ValidID => $Data[2], ); } return %GroupData; } else { return; } } # -- # show usage # -- sub usage { print STDERR " $ProgramName -- OTRS - Admin Privilege Fixup Script usage: $ProgramName [options] options: -a | --addprivs -u | --username <admin_username> -p | --password <admin_password> -h | --help Gives interactive session if no script specified. \n"; exit (1); } # --

1 0

Unicode encoding problems
by Kristoffer Gleditsch 03 Dec '04

03 Dec '04

Hi! Our OTRS 1.3.2 installation runnig on Oracle is using UTF-8 as default charset, since our customer has users from several different charsets (and countries). :) We've had some problems with sending Email in UTF-8. When the characters in the email were translatable to latin-1 (or perhaps latin-15 - didn't test with €), the body of the email came through as latin-1, even though the headers insisted it was UTF-8. When the email contained characters that wouldn't translate to latin-1 (greek, chinese, czech...) it would croak with the message: Wide character in syswrite at /usr/lib/perl5/5.8.3/Net/Cmd.pm line 430.! I tried forcing it to use quoted printable, but that only seemed to move the problem: Wide character in subroutine entry at /home/virtual-http/www.elkjop.int/otrs/Kernel/cpan-lib/MIME/Decoder/QuotedPrint.pm line 74.! After a bit of digging around, Googling, and doing semi-random changes here and there, I stumbled over the Encode module, and the attached patch seems to fix our problem: I don't know if anyone else has this problem as well, or if it's our somewhat non-standard Oracle setup which is causing it, but if anyone else is seeing the same problem, it might be worth a shot. (Some of the changes in the patch is a bit unrelated to this problem, though. The "'" in the comment had to go because it messed up my editors Perl mode, and it seemed like a good idea to use the default charset as default. The most interesting bit is at the bottom.) Regards, -- Kristoffer.

3 4

Small gotchas wrt. Oracle and SQL syntax
by Kristoffer Gleditsch 01 Dec '04

01 Dec '04

Hi, Stumbled over some small gotchas regarding SQL syntax and Oracle 9 today. Here's the error message: ORA-00933: SQL command not properly ended (DBD ERROR: error possibly near <*> indicator at char 26 in 'SELECT st.id FROM ticket <*>as st WHERE st.ticket_state_id IN ( 7, 8 ) '), SQL: 'SELECT st.id FROM ticket as st WHERE st.ticket_state_id IN ( 7, 8 ) ' The problem is the "From column as foo" construct. The "as" in there is optional in PostgreSQL and MySQL (according to the web documentation), but not allowed in Oracle. So I removed them: Regards, -- Kristoffer.

1 0