FS/FS/TicketSystem/RT_External.pm

   1 package FS::TicketSystem::RT_External;
   2
   3 use strict;
   4 use vars qw( $conf $default_queueid
   5              $priority_field $priority_field_queue $field );
   6 use URI::Escape;
   7 use FS::UID;
   8
   9 install_callback FS::UID sub {
  10   my $conf = new FS::Conf;
  11   $default_queueid = $conf->config('ticket_system-default_queueid');
  12   $priority_field =
  13     $conf->config('ticket_system-custom_priority_field');
  14   if ( $priority_field ) {
  15     $priority_field_queue =
  16       $conf->config('ticket_system-custom_priority_field_queue');
  17     $field = $priority_field_queue
  18                   ? $priority_field_queue. '.%7B'. $priority_field. '%7D'
  19                   : $priority_field;
  20   } else {
  21     $priority_field_queue = '';
  22     $field = '';
  23   }
  24 };
  25
  26 sub num_customer_tickets {
  27   my( $self, $custnum, $priority, $dbh ) = @_;
  28
  29   #$dbh ||= create one from some config options
  30
  31   my( $from_sql, @param) = $self->_from_customer( $custnum, $priority );
  32
  33   my $sql = "select count(*) $from_sql";
  34   my $sth = $dbh->prepare($sql) or die $dbh->errstr. " preparing $sql";
  35   $sth->execute(@param)         or die $sth->errstr. " executing $sql";
  36
  37   $sth->fetchrow_arrayref->[0];
  38
  39 }
  40
  41 sub customer_tickets {
  42   my( $self, $custnum, $limit, $priority, $dbh ) = @_;
  43   $limit ||= 0;
  44
  45   #$dbh ||= create one from some config options
  46
  47   my( $from_sql, @param) = $self->_from_customer( $custnum, $priority );
  48   my $sql = "select tickets.*, queues.name".
  49             ( length($priority) ? ", ticketcustomfieldvalues.content" : '' ).
  50             " $from_sql order by priority desc limit $limit";
  51   my $sth = $dbh->prepare($sql) or die $dbh->errstr. "preparing $sql";
  52   $sth->execute(@param)         or die $sth->errstr. "executing $sql";
  53
  54   #munge column names???  #httemplate/view/cust_main/tickets.html has column
  55   #names that might not make sense now...
  56   $sth->fetchall_arrayref({});
  57
  58 }
  59
  60 sub _from_customer {
  61   my( $self, $custnum, $priority ) = @_;
  62
  63   my @param = ();
  64   my $join = '';
  65   my $where = '';
  66   if ( defined($priority) ) {
  67
  68     my $queue_sql = " customfields.queue = ( select id from queues
  69                                               where queues.name = ? )
  70                       or ( ? = '' and customfields.queue = 0 )";
  71
  72     if ( length($priority) ) {
  73       #$where = "
  74       #  and ? = ( select content from TicketCustomFieldValues
  75       #             where ticket = tickets.id
  76       #               and customfield = ( select id from customfields
  77       #                                    where name = ?
  78       #                                      and ( $queue_sql )
  79       #                                 )
  80       #          )
  81       #";
  82       push @param, $priority;
  83
  84       $join = "join TicketCustomFieldValues
  85                  on ( tickets.id = TicketCustomFieldValues.ticket )";
  86
  87       $where = "and content = ?
  88                 and customfield = ( select id from customfields
  89                                      where name = ?
  90                                        and ( $queue_sql )
  91                                   )
  92                ";
  93     } else {
  94       $where =
  95                "and 0 = ( select count(*) from TicketCustomFieldValues
  96                            where ticket = tickets.id
  97                              and customfield = ( select id from customfields
  98                                                   where name = ?
  99                                                     and ( $queue_sql )
 100                                                )
 101                         )
 102                ";
 103     }
 104     push @param, $priority_field,
 105                  $priority_field_queue,
 106                  $priority_field_queue;
 107   }
 108
 109   my $sql = "
 110                     from tickets
 111                     join queues on ( tickets.queue = queues.id )
 112                     join links on ( tickets.id = links.localbase )
 113                     $join
 114        where ( status = 'new' or status = 'open' or status = 'stalled' )
 115          and target = 'freeside://freeside/cust_main/$custnum'
 116          $where
 117   ";
 118
 119   ( $sql, @param );
 120
 121 }
 122
 123 sub href_customer_tickets {
 124   my( $self, $custnum, $priority ) = @_;
 125
 126   #i snarfed this from an RT bookmarked search, it could be unescaped in the
 127   #source for readability and run through uri_escape
 128   my $href =
 129     'Search/Results.html?Order=ASC&Query=%20MemberOf%20%3D%20%27freeside%3A%2F%2Ffreeside%2Fcust_main%2F'.
 130     $custnum.
 131     '%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'
 132   ;
 133
 134   if ( defined($priority) && $field && $priority_field_queue ) {
 135     $href .= 'AND%20Queue%20%3D%20%27'. $priority_field_queue. '%27%20';
 136   }
 137   if ( defined($priority) && $field ) {
 138     $href .= '%20AND%20%27CF.'. $field. '%27%20%3D%20%27'. $priority. '%27%20';
 139   }
 140
 141   $href .= '&Rows=100'.
 142            '&OrderBy=id&Page=1'.
 143            '&Format=%27%20%20%20%3Cb%3E%3Ca%20href%3D%22%2Ffreeside%2Frt%2FTicket%2FDisplay.html%3Fid%3D__id__%22%3E__id__%3C%2Fa%3E%3C%2Fb%3E%2FTITLE%3A%23%27%2C%20%0A%27%3Cb%3E%3Ca%20href%3D%22%2Ffreeside%2Frt%2FTicket%2FDisplay.html%3Fid%3D__id__%22%3E__Subject__%3C%2Fa%3E%3C%2Fb%3E%2FTITLE%3ASubject%27%2C%20%0A%27__Status__%27%2C%20';
 144
 145   if ( defined($priority) && $field ) {
 146     $href .= '%0A%27__CustomField.'. $field. '__%2FTITLE%3ASeverity%27%2C%20';
 147   }
 148
 149   $href .= '%0A%27__QueueName__%27%2C%20%0A%27__OwnerName__%27%2C%20%0A%27__Priority__%27%2C%20%0A%27__NEWLINE__%27%2C%20%0A%27%27%2C%20%0A%27%3Csmall%3E__Requestors__%3C%2Fsmall%3E%27%2C%20%0A%27%3Csmall%3E__CreatedRelative__%3C%2Fsmall%3E%27%2C';
 150
 151   if ( defined($priority) && $field ) {
 152     $href .=   '%20%0A%27__-__%27%2C';
 153   }
 154
 155   $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';
 156
 157   $href;
 158 }
 159
 160
 161 sub href_new_ticket {
 162   my( $self, $custnum, $requestors ) = @_;
 163   'Ticket/Create.html?'.
 164     "Queue=$default_queueid".
 165     "&new-MemberOf=freeside://freeside/cust_main/$custnum".
 166     ( $requestors ? '&Requestors='. uri_escape($requestors) : '' )
 167     ;
 168 }
 169
 170 sub href_ticket {
 171   my($self, $ticketnum) = @_;
 172   'Ticket/Display.html?id='.$ticketnum;
 173 }
 174
 175 1;
 176