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