X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FTicketSystem%2FRT_External.pm;h=cd6834c963f33974073b680a45c6aa1b134f5f9f;hb=2f3b4161da680cd0fff8c91dbdc50fe5e974610e;hp=f94e791b12de79bea28d68cb92a88e468d50e28b;hpb=10a6b7939ad3885ff00ff28ce26a63da958328bf;p=freeside.git diff --git a/FS/FS/TicketSystem/RT_External.pm b/FS/FS/TicketSystem/RT_External.pm index f94e791b1..cd6834c96 100644 --- a/FS/FS/TicketSystem/RT_External.pm +++ b/FS/FS/TicketSystem/RT_External.pm @@ -1,22 +1,26 @@ package FS::TicketSystem::RT_External; use strict; -use vars qw( $conf $default_queueid +use vars qw( $DEBUG $me $conf $dbh $default_queueid $external_url $priority_field $priority_field_queue $field - $dbh $external_url ); + ); use URI::Escape; use FS::UID qw(dbh); use FS::Record qw(qsearchs); use FS::cust_main; +$me = '[FS::TicketSystem::RT_External]'; +$DEBUG = 0; + FS::UID->install_callback( sub { - my $conf = new FS::Conf; + $conf = new FS::Conf; $default_queueid = $conf->config('ticket_system-default_queueid'); $priority_field = $conf->config('ticket_system-custom_priority_field'); if ( $priority_field ) { $priority_field_queue = $conf->config('ticket_system-custom_priority_field_queue'); + $field = $priority_field_queue ? $priority_field_queue. '.%7B'. $priority_field. '%7D' : $priority_field; @@ -35,6 +39,17 @@ FS::UID->install_callback( sub { $external_url = $conf->config('ticket_system-rt_external_url'); } + #kludge... should *use* the id... but good enough for now + if ( $priority_field_queue =~ /^(\d+)$/ ) { + my $id = $1; + my $sql = 'SELECT Name FROM Queues WHERE Id = ?'; + my $sth = $dbh->prepare($sql) or die $dbh->errstr. " preparing $sql"; + $sth->execute($id) or die $sth->errstr. " executing $sql"; + + $priority_field_queue = $sth->fetchrow_arrayref->[0]; + + } + } ); sub num_customer_tickets { @@ -43,6 +58,7 @@ sub num_customer_tickets { my( $from_sql, @param) = $self->_from_customer( $custnum, $priority ); my $sql = "SELECT COUNT(*) $from_sql"; + warn "$me $sql (@param)" if $DEBUG; my $sth = $dbh->prepare($sql) or die $dbh->errstr. " preparing $sql"; $sth->execute(@param) or die $sth->errstr. " executing $sql"; @@ -55,9 +71,12 @@ sub customer_tickets { $limit ||= 0; my( $from_sql, @param) = $self->_from_customer( $custnum, $priority ); - my $sql = "SELECT tickets.*, queues.name". - ( length($priority) ? ", objectcustomfieldvalues.content" : '' ). - " $from_sql ORDER BY priority DESC LIMIT $limit"; + my $sql="SELECT tickets.*, queues.name, ". + "position(tickets.status in 'newopenstalledresolvedrejecteddeleted')". + " AS svalue " . + ( length($priority) ? ", objectcustomfieldvalues.content" : '' ). + " $from_sql ORDER BY svalue, priority DESC, id DESC LIMIT $limit"; + warn "$me $sql (@param)" if $DEBUG; my $sth = $dbh->prepare($sql) or die $dbh->errstr. "preparing $sql"; $sth->execute(@param) or die $sth->errstr. "executing $sql"; @@ -86,7 +105,8 @@ sub _from_customer { ON ( CustomFields.id = ObjectCustomFields.CustomField ) WHERE LookupType = 'RT::Queue-RT::Ticket' AND name = ? - AND ( $queue_sql )"; + AND ( $queue_sql ) + )"; push @param, $priority_field, $priority_field_queue, @@ -108,6 +128,7 @@ sub _from_customer { ON ( tickets.id = ObjectCustomFieldValues.ObjectId )"; $where = " AND content = ? + AND ObjectCustomFieldValues.disabled != 1 AND ObjectType = 'RT::Ticket' AND $customfield_sql"; @@ -129,7 +150,7 @@ sub _from_customer { JOIN queues ON ( tickets.queue = queues.id ) JOIN links ON ( tickets.id = links.localbase ) $join - WHERE ( status = 'new' OR status = 'open' OR status = 'stalled' ) + WHERE ( ". join(' OR ', map "status = '$_'", $self->statuses ). " ) AND target = 'freeside://freeside/cust_main/$custnum' $where "; @@ -138,31 +159,44 @@ sub _from_customer { } +sub statuses { + #my $self = shift; + my @statuses = grep { ! /^\s*$/ } $conf->config('cust_main-ticket_statuses'); + @statuses = (qw( new open stalled )) unless scalar(@statuses); + @statuses; +} + sub href_customer_tickets { my( $self, $custnum, $priority ) = @_; - my $href = $self->baseurl; + #my $href = $self->baseurl; + + #i snarfed this from an RT bookmarked search, then unescaped (some of) it with + #perl -npe 's/%([0-9A-F]{2})/pack('C', hex($1))/eg;' - #i snarfed this from an RT bookmarked search, it could be unescaped in the - #source for readability and run through uri_escape - $href .= - 'Search/Results.html?Order=ASC&Query=%20MemberOf%20%3D%20%27freeside%3A%2F%2Ffreeside%2Fcust_main%2F'. - $custnum. - '%27%20%20AND%20%28%20Status%20%3D%20%27open%27%20%20OR%20Status%20%3D%20%27new%27%20%20OR%20Status%20%3D%20%27stalled%27%20%29%20' + my $href .= + "Search/Results.html?Order=ASC&". + "Query= MemberOf = 'freeside://freeside/cust_main/$custnum' ". + #" AND ( Status = 'open' OR Status = 'new' OR Status = 'stalled' )" + " AND ( ". join(' OR ', map "Status = '$_'", $self->statuses ). " ) " ; if ( defined($priority) && $field && $priority_field_queue ) { - $href .= 'AND%20Queue%20%3D%20%27'. $priority_field_queue. '%27%20'; + $href .= " AND Queue = '$priority_field_queue' "; } if ( defined($priority) && $field ) { - $href .= '%20AND%20%27CF.'. $field. '%27%20'; + $href .= " AND 'CF.$field' "; if ( $priority ) { - $href .= '%3D%20%27'. $priority. '%27%20'; + $href .= "= '$priority' "; } else { - $href .= 'IS%20%27NULL%27%20'; + $href .= "IS 'NULL' "; #this is "RTQL", not SQL } } + #$href = + uri_escape($href); + #eventually should unescape all of it... + $href .= '&Rows=100'. '&OrderBy=id&Page=1'. '&Format=%27%20%20%20%3Cb%3E%3Ca%20href%3D%22'. @@ -183,7 +217,10 @@ sub href_customer_tickets { $href .= '%20%0A%27%3Csmall%3E__ToldRelative__%3C%2Fsmall%3E%27%2C%20%0A%27%3Csmall%3E__LastUpdatedRelative__%3C%2Fsmall%3E%27%2C%20%0A%27%3Csmall%3E__TimeLeft__%3C%2Fsmall%3E%27'; - $href; + #$href = + #uri_escape($href); + + $self->baseurl. $href; } @@ -233,7 +270,8 @@ sub queue { my $sth = $dbh->prepare($sql) or die $dbh->errstr. " preparing $sql"; $sth->execute($queueid) or die $sth->errstr. " executing $sql"; - $sth->fetchrow_arrayref->[0]; + my $rows = $sth->fetchrow_arrayref; + $rows ? $rows->[0] : ''; } @@ -242,5 +280,53 @@ sub baseurl { $external_url; } +sub _retrieve_single_value { + my( $self, $sql ) = @_; + + warn "$me $sql" if $DEBUG; + my $sth = $dbh->prepare($sql) or die $dbh->errstr. "preparing $sql"; + $sth->execute or die $sth->errstr. "executing $sql"; + + my $arrayref = $sth->fetchrow_arrayref; + $arrayref ? $arrayref->[0] : $arrayref; +} + +sub transaction_creator { + my( $self, $transaction_id ) = @_; + + my $sql = "SELECT name from transactions JOIN users ON ". + "transactions.creator=users.id WHERE transactions.id = ". + $transaction_id; + + $self->_retrieve_single_value($sql); +} + +sub transaction_ticketid { + my( $self, $transaction_id ) = @_; + + my $sql = "SELECT objectid from transactions WHERE transactions.id = ". + $transaction_id; + + $self->_retrieve_single_value($sql); +} + +sub transaction_subject { + my( $self, $transaction_id ) = @_; + + my $sql = "SELECT subject from transactions JOIN tickets ON objectid=". + "tickets.id WHERE transactions.id = ". $transaction_id; + + $self->_retrieve_single_value($sql); +} + +sub transaction_status { + my( $self, $transaction_id ) = @_; + + my $sql = "SELECT status from transactions JOIN tickets ON objectid=". + "tickets.id WHERE transactions.id = ". $transaction_id; + + $self->_retrieve_single_value($sql); +} + 1;