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