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 my $ids = $hash->{lc($item->Name)} ||= [];
163 if ( $item->Creator == 1 ) { # RT::SystemUser
164 unshift @$ids, $item->Id;
167 push @$ids, $item->Id;
172 my (%condition, %action, %template);
173 &$cachify('RT::ScripConditions', \%condition);
174 &$cachify('RT::ScripActions', \%action);
175 &$cachify('RT::Templates', \%template);
176 # $condition{name} = [ ids... ]
177 # with the id of the system-created object first, if there is one
180 my $ScripCondition = RT::ScripCondition->new($CurrentUser);
181 foreach my $sc (@ScripConditions) {
182 # $sc: Name, Description, ApplicableTransTypes, ExecModule, Argument
183 next if exists( $condition{ lc($sc->{Name}) } );
184 my ($val, $msg) = $ScripCondition->Create( %$sc );
186 $condition{ lc($ScripCondition->Name) } = [ $ScripCondition->Id ];
190 my $ScripAction = RT::ScripAction->new($CurrentUser);
191 foreach my $sa (@ScripActions) {
192 # $sa: Name, Description, ExecModule, Argument
193 next if exists( $action{ lc($sa->{Name}) } );
194 my ($val, $msg) = $ScripAction->Create( %$sa );
196 $action{ lc($ScripAction->Name) } = [ $ScripAction->Id ];
200 my $Template = RT::Template->new($CurrentUser);
201 foreach my $t (@Templates) {
202 # $t: Queue, Name, Description, Content
203 next if exists( $template{ lc($t->{Name}) } );
204 my ($val, $msg) = $Template->Create( %$t );
206 $template{ lc($Template->Name) } = [ $Template->Id ];
210 my %scrip; # $scrips{condition}{action}{template} = id
211 my $search = RT::Scrips->new($CurrentUser);
212 $search->Limit(FIELD => 'Queue', VALUE => 0);
213 while (my $item = $search->Next) {
214 my ($c, $a, $t) = map {lc $item->$_->Name}
215 ('ScripConditionObj', 'ScripActionObj', 'TemplateObj');
216 if ( exists $scrip{$c}{$a} and $item->Creator == 1 ) {
217 warn "Deleting duplicate scrip $c $a [$t]\n";
218 my ($val, $msg) = $item->Delete;
219 warn "error deleting scrip: $msg\n" if !$val;
221 elsif ( exists $Delete_Scrips{$c}{$a}{$t} and $item->Creator == 1 ) {
222 warn "Deleting obsolete scrip $c $a [$t]\n";
223 my ($val, $msg) = $item->Delete;
224 warn "error deleting scrip: $msg\n" if !$val;
227 $scrip{$c}{$a} = $item->id;
230 my $Scrip = RT::Scrip->new($CurrentUser);
231 foreach my $s ( @Scrips ) {
232 my $desc = $s->{'Description'};
233 my ($c, $a, $t) = map lc,
234 @{ $s }{'ScripCondition', 'ScripAction', 'Template'};
236 if ( exists($scrip{$c}{$a}) ) {
237 $Scrip->Load( $scrip{$c}{$a} );
238 } else { # need to create it
240 if ( !exists($condition{$c}) ) {
241 warn "ScripCondition '$c' not found.\n";
244 if ( !exists($action{$a}) ) {
245 warn "ScripAction '$a' not found.\n";
248 if ( !exists($template{$t}) ) {
249 warn "Template '$t' not found.\n";
253 ScripCondition => $condition{$c}->[0],
254 ScripAction => $action{$a}->[0],
255 Template => $template{$t}->[0],
257 Description => $desc,
259 warn "Creating scrip: $c $a [$t]\n";
260 my ($val, $msg) = $Scrip->Create(%new_param);
264 # set the Immutable attribute on them if needed
265 if ( !$Scrip->FirstAttribute('Immutable') ) {
267 $Scrip->SetAttribute(Name => 'Immutable', Content => '1');
273 # one-time fix: accumulator fields (support time, etc.) that had values
274 # entered on ticket creation need OCFV records attached to their Create
276 my $sql = 'SELECT first_ocfv.ObjectId, first_ocfv.Created, Content '.
277 'FROM ObjectCustomFieldValues as first_ocfv '.
279 # subquery to get the first OCFV with a certain name for each ticket
280 'SELECT min(ObjectCustomFieldValues.Id) AS Id '.
281 'FROM ObjectCustomFieldValues '.
282 'JOIN CustomFields '.
283 'ON (ObjectCustomFieldValues.CustomField = CustomFields.Id) '.
284 'WHERE ObjectType = \'RT::Ticket\' '.
285 'AND CustomFields.Name = ? '.
287 ') AS first_ocfv_id USING (Id) '.
289 # subquery to get the first transaction date for each ticket
290 # other than the Create
291 'SELECT ObjectId, min(Created) AS Created FROM Transactions '.
292 'WHERE ObjectType = \'RT::Ticket\' '.
293 'AND Type != \'Create\' '.
295 ') AS first_txn ON (first_ocfv.ObjectId = first_txn.ObjectId) '.
296 # where the ticket custom field acquired a value before any transactions
297 # on the ticket (i.e. it was set on ticket creation)
298 'WHERE first_ocfv.Created < first_txn.Created '.
299 # and we haven't already fixed the ticket
301 'SELECT 1 FROM Transactions JOIN ObjectCustomFieldValues '.
302 'ON (Transactions.Id = ObjectCustomFieldValues.ObjectId) '.
303 'JOIN CustomFields '.
304 'ON (ObjectCustomFieldValues.CustomField = CustomFields.Id) '.
305 'WHERE ObjectCustomFieldValues.ObjectType = \'RT::Transaction\' '.
306 'AND CustomFields.Name = ? '.
307 'AND Transactions.Type = \'Create\''.
308 'AND Transactions.ObjectType = \'RT::Ticket\''.
309 'AND Transactions.ObjectId = first_ocfv.ObjectId'.
313 # prior to this fix, the only name an accumulate field could possibly have
314 # was "Support time".
315 my $sth = $dbh->prepare($sql);
316 $sth->execute('Support time', 'Support time');
317 my $rows = $sth->rows;
318 warn "Fixing support time on $rows rows...\n" if $rows > 0;
319 while ( my $row = $sth->fetchrow_arrayref ) {
320 my ($tid, $created, $content) = @$row;
321 my $Txns = RT::Transactions->new($CurrentUser);
322 $Txns->Limit(FIELD => 'ObjectId', VALUE => $tid);
323 $Txns->Limit(FIELD => 'ObjectType', VALUE => 'RT::Ticket');
324 $Txns->Limit(FIELD => 'Type', VALUE => 'Create');
325 my $CreateTxn = $Txns->First;
327 my ($val, $msg) = $CreateTxn->AddCustomFieldValue(
328 Field => 'Support time',
330 RecordTransaction => 0,
332 warn "Error setting transaction support time: $msg\n" unless $val;
334 warn "Create transaction not found for ticket $tid.\n";
338 my $cve_2013_3373_sql = '';
339 if ( driver_name =~ /^Pg/i ) {
340 $cve_2013_3373_sql = q(
341 UPDATE Tickets SET Subject = REPLACE(Subject,E'\n','')
343 } elsif ( driver_name =~ /^mysql/i ) {
344 $cve_2013_3373_sql = q(
345 UPDATE Tickets SET Subject = REPLACE(Subject,'\n','');
348 warn "WARNING: Don't know how to update RT Ticket Subjects for your database driver for CVE-2013-3373";
350 if ( $cve_2013_3373_sql ) {
351 my $cve_2013_3373_sth = $dbh->prepare($cve_2013_3373_sql)
353 $cve_2013_3373_sth->execute
354 or die $cve_2013_3373_sth->errstr;
357 # Remove dangling customer links, if any
358 my %target_pkey = ('cust_main' => 'custnum', 'cust_svc' => 'svcnum');
359 for my $table (keys %target_pkey) {
360 my $pkey = $target_pkey{$table};
362 "DELETE FROM Links WHERE id IN(
364 SELECT Links.id FROM Links LEFT JOIN $table ON (Links.Target =
365 'freeside://freeside/$table/' || $table.$pkey)
366 WHERE Links.Target like 'freeside://freeside/$table/%'
367 AND $table.$pkey IS NULL
370 ) or die $dbh->errstr;
371 warn "Removed $rows dangling ticket-$table links\n" if $rows > 0;
374 # Fix ticket transactions on the Time* fields where the NewValue (or
375 # OldValue, though this is not known to happen) is an empty string
376 foreach (qw(newvalue oldvalue)) {
378 "UPDATE Transactions SET $_ = '0' WHERE ObjectType='RT::Ticket' AND ".
379 "Field IN ('TimeWorked', 'TimeEstimated', 'TimeLeft') AND $_ = ''"
380 ) or die $dbh->errstr;
381 warn "Fixed $rows transactions with empty time values\n" if $rows > 0;