1 package FS::TicketSystem;
4 use vars qw( $conf $system $AUTOLOAD );
6 use FS::UID qw( dbh driver_name );
7 use FS::Record qw( dbdef );
9 FS::UID->install_callback( sub {
11 $system = $conf->config('ticket_system');
20 my $conf = new FS::Conf;
21 die "FS::TicketSystem::$AUTOLOAD called, but no ticket system configured\n"
24 eval "use FS::TicketSystem::$system;";
34 WillResolve => { type => 'timestamp', null => 1, default => '', },
37 Required => { type => 'integer', default => 0, null => 0 },
42 my $system = FS::Conf->new->config('ticket_system');
43 return if !defined($system) || $system ne 'RT_Internal';
44 my ($class, %opts) = @_;
48 my $case = driver_name eq 'mysql' ? sub {@_} : sub {map lc, @_};
49 foreach my $tablename (keys %columns) {
50 my $table = dbdef->table(&$case($tablename));
53 "$tablename table does not exist. Your RT installation is incomplete.\n";
56 foreach my $colname (keys %{ $columns{$tablename} }) {
57 if ( !$table->column(&$case($colname)) ) {
58 my $col = new DBIx::DBSchema::Column {
60 name => &$case($colname),
61 %{ $columns{$tablename}->{$colname} }
63 $col->table_obj($table);
64 my ($alter, $postalter) = $col->sql_add_column($dbh);
66 push @sql, "ALTER TABLE $tablename $_;";
68 push @sql, @$postalter;
74 warn "Upgrading RT schema:\n";
75 foreach my $statement (@sql) {
77 $dbh->do( $statement )
78 or die "Error: ". $dbh->errstr. "\n executing: $statement";
84 return if !defined($system) || $system ne 'RT_Internal';
85 my ($class, %opts) = @_;
87 # go ahead and use the RT API for this
89 FS::TicketSystem->init;
90 my $session = FS::TicketSystem->session();
91 # bypass RT ACLs--we're going to do lots of things
92 my $CurrentUser = $RT::SystemUser;
96 # selfservice and cron users
97 foreach my $username ('%%%SELFSERVICE_USER%%%', 'fs_daily') {
98 my $User = RT::User->new($CurrentUser);
99 $User->Load($username);
100 if (!defined($User->Id)) {
101 my ($val, $msg) = $User->Create(
103 'Gecos' => $username,
105 # any other fields needed?
109 my $Principal = $User->PrincipalObj; # can this ever fail?
110 my @rights = ( qw(ShowTicket SeeQueue ModifyTicket ReplyToTicket
111 CreateTicket SeeCustomField) );
113 next if $Principal->HasRight( 'Right' => $_, Object => $RT::System );
114 my ($val, $msg) = $Principal->GrantRight(
116 'Object' => $RT::System,
122 # EscalateQueue custom field and friends
123 my $CF = RT::CustomField->new($CurrentUser);
124 $CF->Load('EscalateQueue');
125 if (!defined($CF->Id)) {
126 my ($val, $msg) = $CF->Create(
127 'Name' => 'EscalateQueue',
130 'LookupType' => 'RT::Queue',
131 'Description' => 'Escalate to Queue',
132 'ValuesClass' => 'RT::CustomFieldValues::Queues', #magic!
135 my $OCF = RT::ObjectCustomField->new($CurrentUser);
136 ($val, $msg) = $OCF->Create(
137 'CustomField' => $CF->Id,
143 # Load from RT data file
144 our (@Groups, @Users, @ACL, @Queues, @ScripActions, @ScripConditions,
145 @Templates, @CustomFields, @Scrips, @Attributes, @Initial, @Final,
147 my $datafile = '%%%RT_PATH%%%/etc/initialdata';
148 eval { require $datafile };
150 warn "Couldn't load RT data from '$datafile': $@\n(skipping)\n";
154 # Cache existing ScripCondition, ScripAction, and Template IDs.
155 # Complicated because we don't want to just step on multiple IDs
156 # with the same name.
158 my ($class, $hash) = @_;
159 my $search = $class->new($CurrentUser);
161 while ( my $item = $search->Next ) {
162 if ( $class =~ /Template/ ) {
163 # template names can be duplicated in different queues, and they are.
164 my $queue = $item->QueueObj->Name || '0';
165 my $subhash = $hash->{$queue} ||= {};
166 $subhash->{lc($item->Name)} = $item->Id;
168 # then duplicate names are allowed; they just have different ids
169 my $ids = $hash->{lc($item->Name)} ||= [];
170 if ( $item->Creator == 1 ) { # RT::SystemUser
171 unshift @$ids, $item->Id;
174 push @$ids, $item->Id;
180 my (%condition, %action, %template);
181 &$cachify('RT::ScripConditions', \%condition); # condition name -> [ ids ]
182 # with the id of the system-created object first, if there is one
183 &$cachify('RT::ScripActions', \%action); # action name -> [ ids ]
184 &$cachify('RT::Templates', \%template); # queue name -> tmpl name -> id
187 my $ScripCondition = RT::ScripCondition->new($CurrentUser);
188 foreach my $sc (@ScripConditions) {
189 # $sc: Name, Description, ApplicableTransTypes, ExecModule, Argument
190 next if exists( $condition{ lc($sc->{Name}) } );
191 my ($val, $msg) = $ScripCondition->Create( %$sc );
193 $condition{ lc($ScripCondition->Name) } = [ $ScripCondition->Id ];
197 my $ScripAction = RT::ScripAction->new($CurrentUser);
198 foreach my $sa (@ScripActions) {
199 # $sa: Name, Description, ExecModule, Argument
200 next if exists( $action{ lc($sa->{Name}) } );
201 my ($val, $msg) = $ScripAction->Create( %$sa );
203 $action{ lc($ScripAction->Name) } = [ $ScripAction->Id ];
208 my $Template = RT::Template->new($CurrentUser);
209 foreach my $t (@Templates) {
210 # $t: Queue, Name, Description, Content
211 next if exists( $template{ $t->{Queue} }->{ lc($t->{Name}) } );
212 my ($val, $msg) = $Template->Create( %$t );
214 $template{ $t->{Queue} }->{ lc($Template->Name) } = [ $Template->Id ];
218 # Scrips can no longer be deleted, so we'll count them as existing
219 # if they're applied to the global queue, or if they're not applied to
222 my %scrip; # $scrips{condition}{action}{template} = id
223 foreach my $criterion ('LimitToGlobal', 'LimitToNotAdded') {
224 my $search = RT::Scrips->new($CurrentUser);
227 while (my $item = $search->Next) {
228 my ($c, $a, $t) = map {lc $item->$_->Name}
229 ('ScripConditionObj', 'ScripActionObj', 'TemplateObj');
230 if ( exists $scrip{$c}{$a} and $item->Creator == 1 ) {
231 warn "Deleting duplicate scrip $c $a [$t]\n";
232 my ($val, $msg) = $item->Delete;
233 warn "error deleting scrip: $msg\n" if !$val;
235 elsif ( exists $Delete_Scrips{$c}{$a}{$t} and $item->Creator == 1 ) {
236 warn "Deleting obsolete scrip $c $a [$t]\n";
237 my ($val, $msg) = $item->Delete;
238 warn "error deleting scrip: $msg\n" if !$val;
241 $scrip{$c}{$a} = $item->id;
246 my $Scrip = RT::Scrip->new($CurrentUser);
247 foreach my $s ( @Scrips ) {
248 my $desc = $s->{'Description'};
249 # the condition, action, and template _names_
250 my ($c, $a, $t) = map lc,
251 @{ $s }{'ScripCondition', 'ScripAction', 'Template'};
253 if ( exists($scrip{$c}{$a}) ) {
254 $Scrip->Load( $scrip{$c}{$a} );
255 } else { # need to create it
257 if ( !exists($condition{$c}) ) {
258 warn "ScripCondition '$c' not found.\n";
261 if ( !exists($action{$a}) ) {
262 warn "ScripAction '$a' not found.\n";
265 if ( !exists($template{'0'}{$t}) ) {
266 # a global template with this name has to exist, at least
267 warn "Template '$t' not found.\n";
271 ScripCondition => $condition{$c}->[0],
272 ScripAction => $action{$a}->[0],
273 Template => $t, # scrips.template is now the name, not the id
275 Description => $desc,
277 warn "Creating scrip: $c $a [$t]\n";
278 my ($val, $msg) = $Scrip->Create(%new_param);
282 # set the Immutable attribute on them if needed
283 # no longer needed; you can't delete scrips through the UI anyway, only
285 #if ( !$Scrip->FirstAttribute('Immutable') ) {
287 # $Scrip->SetAttribute(Name => 'Immutable', Content => '1');
293 # one-time fix: accumulator fields (support time, etc.) that had values
294 # entered on ticket creation need OCFV records attached to their Create
296 my $sql = 'SELECT first_ocfv.ObjectId, first_ocfv.Created, Content '.
297 'FROM ObjectCustomFieldValues as first_ocfv '.
299 # subquery to get the first OCFV with a certain name for each ticket
300 'SELECT min(ObjectCustomFieldValues.Id) AS Id '.
301 'FROM ObjectCustomFieldValues '.
302 'JOIN CustomFields '.
303 'ON (ObjectCustomFieldValues.CustomField = CustomFields.Id) '.
304 'WHERE ObjectType = \'RT::Ticket\' '.
305 'AND CustomFields.Name = ? '.
307 ') AS first_ocfv_id USING (Id) '.
309 # subquery to get the first transaction date for each ticket
310 # other than the Create
311 'SELECT ObjectId, min(Created) AS Created FROM Transactions '.
312 'WHERE ObjectType = \'RT::Ticket\' '.
313 'AND Type != \'Create\' '.
315 ') AS first_txn ON (first_ocfv.ObjectId = first_txn.ObjectId) '.
316 # where the ticket custom field acquired a value before any transactions
317 # on the ticket (i.e. it was set on ticket creation)
318 'WHERE first_ocfv.Created < first_txn.Created '.
319 # and we haven't already fixed the ticket
321 'SELECT 1 FROM Transactions JOIN ObjectCustomFieldValues '.
322 'ON (Transactions.Id = ObjectCustomFieldValues.ObjectId) '.
323 'JOIN CustomFields '.
324 'ON (ObjectCustomFieldValues.CustomField = CustomFields.Id) '.
325 'WHERE ObjectCustomFieldValues.ObjectType = \'RT::Transaction\' '.
326 'AND CustomFields.Name = ? '.
327 'AND Transactions.Type = \'Create\''.
328 'AND Transactions.ObjectType = \'RT::Ticket\''.
329 'AND Transactions.ObjectId = first_ocfv.ObjectId'.
333 # prior to this fix, the only name an accumulate field could possibly have
334 # was "Support time".
335 my $sth = $dbh->prepare($sql);
336 $sth->execute('Support time', 'Support time');
337 my $rows = $sth->rows;
338 warn "Fixing support time on $rows rows...\n" if $rows > 0;
339 while ( my $row = $sth->fetchrow_arrayref ) {
340 my ($tid, $created, $content) = @$row;
341 my $Txns = RT::Transactions->new($CurrentUser);
342 $Txns->Limit(FIELD => 'ObjectId', VALUE => $tid);
343 $Txns->Limit(FIELD => 'ObjectType', VALUE => 'RT::Ticket');
344 $Txns->Limit(FIELD => 'Type', VALUE => 'Create');
345 my $CreateTxn = $Txns->First;
347 my ($val, $msg) = $CreateTxn->AddCustomFieldValue(
348 Field => 'Support time',
350 RecordTransaction => 0,
352 warn "Error setting transaction support time: $msg\n" unless $val;
354 warn "Create transaction not found for ticket $tid.\n";
358 my $cve_2013_3373_sql = '';
359 if ( driver_name =~ /^Pg/i ) {
360 $cve_2013_3373_sql = q(
361 UPDATE Tickets SET Subject = REPLACE(Subject,E'\n','')
363 } elsif ( driver_name =~ /^mysql/i ) {
364 $cve_2013_3373_sql = q(
365 UPDATE Tickets SET Subject = REPLACE(Subject,'\n','');
368 warn "WARNING: Don't know how to update RT Ticket Subjects for your database driver for CVE-2013-3373";
370 if ( $cve_2013_3373_sql ) {
371 my $cve_2013_3373_sth = $dbh->prepare($cve_2013_3373_sql)
373 $cve_2013_3373_sth->execute
374 or die $cve_2013_3373_sth->errstr;
377 # Remove dangling customer links, if any
378 my %target_pkey = ('cust_main' => 'custnum', 'cust_svc' => 'svcnum');
379 for my $table (keys %target_pkey) {
380 my $pkey = $target_pkey{$table};
382 "DELETE FROM Links WHERE id IN(
384 SELECT Links.id FROM Links LEFT JOIN $table ON (Links.Target =
385 'freeside://freeside/$table/' || $table.$pkey)
386 WHERE Links.Target like 'freeside://freeside/$table/%'
387 AND $table.$pkey IS NULL
390 ) or die $dbh->errstr;
391 warn "Removed $rows dangling ticket-$table links\n" if $rows > 0;
394 # Fix ticket transactions on the Time* fields where the NewValue (or
395 # OldValue, though this is not known to happen) is an empty string
396 foreach (qw(newvalue oldvalue)) {
398 "UPDATE Transactions SET $_ = '0' WHERE ObjectType='RT::Ticket' AND ".
399 "Field IN ('TimeWorked', 'TimeEstimated', 'TimeLeft') AND $_ = ''"
400 ) or die $dbh->errstr;
401 warn "Fixed $rows transactions with empty time values\n" if $rows > 0;