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