FS/FS/TicketSystem/RT_External.pm

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