X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FTicketSystem.pm;h=c973c8802dc92da5745a59817a1e549f01be9ecb;hp=9d11ff47bbec5de4e775af49bbaf5d49c1cb6ca9;hb=a36e0f8a0f69349dafaa16d1d2d57dfb6e5dbc85;hpb=cd34706f94273cf381952f57c5fcb7d6910fbae0 diff --git a/FS/FS/TicketSystem.pm b/FS/FS/TicketSystem.pm index 9d11ff47b..c973c8802 100644 --- a/FS/FS/TicketSystem.pm +++ b/FS/FS/TicketSystem.pm @@ -4,6 +4,7 @@ use strict; use vars qw( $conf $system $AUTOLOAD ); use FS::Conf; use FS::UID qw( dbh driver_name ); +use FS::Record qw( dbdef ); FS::UID->install_callback( sub { $conf = new FS::Conf; @@ -27,6 +28,58 @@ sub AUTOLOAD { $self->$sub(@_); } +# Our schema changes +my %columns = ( + Tickets => { + WillResolve => { type => 'timestamp', null => 1, default => '', }, + }, + CustomFields => { + Required => { type => 'integer', default => 0, null => 0 }, + }, +); + +sub _upgrade_schema { + my $system = FS::Conf->new->config('ticket_system'); + return if !defined($system) || $system ne 'RT_Internal'; + my ($class, %opts) = @_; + + my $dbh = dbh; + my @sql; + my $case = driver_name eq 'mysql' ? sub {@_} : sub {map lc, @_}; + foreach my $tablename (keys %columns) { + my $table = dbdef->table(&$case($tablename)); + if ( !$table ) { + warn + "$tablename table does not exist. Your RT installation is incomplete.\n"; + next; + } + foreach my $colname (keys %{ $columns{$tablename} }) { + if ( !$table->column(&$case($colname)) ) { + my $col = new DBIx::DBSchema::Column { + table_obj => $table, + name => &$case($colname), + %{ $columns{$tablename}->{$colname} } + }; + $col->table_obj($table); + my ($alter, $postalter) = $col->sql_add_column($dbh); + foreach (@$alter) { + push @sql, "ALTER TABLE $tablename $_;"; + } + push @sql, @$postalter; + } + } #foreach $colname + } #foreach $tablename + + return if !@sql; + warn "Upgrading RT schema:\n"; + foreach my $statement (@sql) { + warn "$statement\n"; + $dbh->do( $statement ) + or die "Error: ". $dbh->errstr. "\n executing: $statement"; + } + return; +} + sub _upgrade_data { return if !defined($system) || $system ne 'RT_Internal'; my ($class, %opts) = @_; @@ -35,8 +88,36 @@ sub _upgrade_data { FS::TicketSystem->init; my $session = FS::TicketSystem->session(); - my $CurrentUser = $session->{'CurrentUser'} - or die 'freeside-upgrade must run as a valid RT user'; + # bypass RT ACLs--we're going to do lots of things + my $CurrentUser = $RT::SystemUser; + + my $dbh = dbh; + + # selfservice and cron users + foreach my $username ('%%%SELFSERVICE_USER%%%', 'fs_daily') { + my $User = RT::User->new($CurrentUser); + $User->Load($username); + if (!defined($User->Id)) { + my ($val, $msg) = $User->Create( + 'Name' => $username, + 'Gecos' => $username, + 'Privileged' => 1, + # any other fields needed? + ); + die $msg if !$val; + } + my $Principal = $User->PrincipalObj; # can this ever fail? + my @rights = ( qw(ShowTicket SeeQueue ModifyTicket ReplyToTicket + CreateTicket SeeCustomField) ); + foreach (@rights) { + next if $Principal->HasRight( 'Right' => $_, Object => $RT::System ); + my ($val, $msg) = $Principal->GrantRight( + 'Right' => $_, + 'Object' => $RT::System, + ); + die $msg if !$val; + } + } #foreach $username # EscalateQueue custom field and friends my $CF = RT::CustomField->new($CurrentUser); @@ -61,7 +142,8 @@ sub _upgrade_data { # Load from RT data file our (@Groups, @Users, @ACL, @Queues, @ScripActions, @ScripConditions, - @Templates, @CustomFields, @Scrips, @Attributes, @Initial, @Final); + @Templates, @CustomFields, @Scrips, @Attributes, @Initial, @Final, + %Delete_Scrips); my $datafile = '%%%RT_PATH%%%/etc/initialdata'; eval { require $datafile }; if ( $@ ) { @@ -69,18 +151,37 @@ sub _upgrade_data { return; } - # Cache existing ScripCondition, ScripAction, and Template IDs - my $search = RT::ScripConditions->new($CurrentUser); - $search->UnLimit; - my %condition = map { lc($_->Name), $_->Id } @{ $search->ItemsArrayRef }; - - $search = RT::ScripActions->new($CurrentUser); - $search->UnLimit; - my %action = map { lc($_->Name), $_->Id } @{ $search->ItemsArrayRef }; + # Cache existing ScripCondition, ScripAction, and Template IDs. + # Complicated because we don't want to just step on multiple IDs + # with the same name. + my $cachify = sub { + my ($class, $hash) = @_; + my $search = $class->new($CurrentUser); + $search->UnLimit; + while ( my $item = $search->Next ) { + if ( $class =~ /Template/ ) { + # template names can be duplicated in different queues, and they are. + my $queue = $item->QueueObj->Name || '0'; + my $subhash = $hash->{$queue} ||= {}; + $subhash->{lc($item->Name)} = $item->Id; + } else { + # then duplicate names are allowed; they just have different ids + my $ids = $hash->{lc($item->Name)} ||= []; + if ( $item->Creator == 1 ) { # RT::SystemUser + unshift @$ids, $item->Id; + } + else { + push @$ids, $item->Id; + } + } + } + }; - $search = RT::Templates->new($CurrentUser); - $search->UnLimit; - my %template = map { lc($_->Name), $_->Id } @{ $search->ItemsArrayRef }; + my (%condition, %action, %template); + &$cachify('RT::ScripConditions', \%condition); # condition name -> [ ids ] + # with the id of the system-created object first, if there is one + &$cachify('RT::ScripActions', \%action); # action name -> [ ids ] + &$cachify('RT::Templates', \%template); # queue name -> tmpl name -> id # ScripConditions my $ScripCondition = RT::ScripCondition->new($CurrentUser); @@ -89,7 +190,7 @@ sub _upgrade_data { next if exists( $condition{ lc($sc->{Name}) } ); my ($val, $msg) = $ScripCondition->Create( %$sc ); die $msg if !$val; - $condition{ lc($ScripCondition->Name) } = $ScripCondition->Id; + $condition{ lc($ScripCondition->Name) } = [ $ScripCondition->Id ]; } # ScripActions @@ -99,50 +200,226 @@ sub _upgrade_data { next if exists( $action{ lc($sa->{Name}) } ); my ($val, $msg) = $ScripAction->Create( %$sa ); die $msg if !$val; - $action{ lc($ScripAction->Name) } = $ScripAction->Id; + $action{ lc($ScripAction->Name) } = [ $ScripAction->Id ]; } + $DB::single = 1; # Templates my $Template = RT::Template->new($CurrentUser); foreach my $t (@Templates) { # $t: Queue, Name, Description, Content - next if exists( $template{ lc($t->{Name}) } ); + next if exists( $template{ $t->{Queue} }->{ lc($t->{Name}) } ); my ($val, $msg) = $Template->Create( %$t ); die $msg if !$val; - $template{ lc($Template->Name) } = $Template->Id; + $template{ $t->{Queue} }->{ lc($Template->Name) } = [ $Template->Id ]; } # Scrips + # Scrips can no longer be deleted, so we'll count them as existing + # if they're applied to the global queue, or if they're not applied to + # _any_ queue. + + my %scrip; # $scrips{condition}{action}{template} = id + foreach my $criterion ('LimitToGlobal', 'LimitToNotAdded') { + my $search = RT::Scrips->new($CurrentUser); + $search->$criterion; + + while (my $item = $search->Next) { + my ($c, $a, $t) = map {lc $item->$_->Name} + ('ScripConditionObj', 'ScripActionObj', 'TemplateObj'); + if ( exists $scrip{$c}{$a} and $item->Creator == 1 ) { + warn "Deleting duplicate scrip $c $a [$t]\n"; + my ($val, $msg) = $item->Delete; + warn "error deleting scrip: $msg\n" if !$val; + } + elsif ( exists $Delete_Scrips{$c}{$a}{$t} and $item->Creator == 1 ) { + warn "Deleting obsolete scrip $c $a [$t]\n"; + my ($val, $msg) = $item->Delete; + warn "error deleting scrip: $msg\n" if !$val; + } + else { + $scrip{$c}{$a} = $item->id; + } + } + } + my $Scrip = RT::Scrip->new($CurrentUser); foreach my $s ( @Scrips ) { my $desc = $s->{'Description'}; + # the condition, action, and template _names_ my ($c, $a, $t) = map lc, @{ $s }{'ScripCondition', 'ScripAction', 'Template'}; - if ( !$condition{$c} ) { - warn "ScripCondition '$c' not found.\n"; - next; - } - if ( !$action{$a} ) { - warn "ScripAction '$a' not found.\n"; - next; - } - if ( !$template{$t} ) { - warn "Template '$t' not found.\n"; - next; - } - my %param = ( - ScripCondition => $condition{$c}, - ScripAction => $action{$a}, - Template => $template{$t}, - Queue => 0, - ); - $Scrip->LoadByCols(%param); - if (!defined($Scrip->Id)) { - my ($val, $msg) = $Scrip->Create(%param, Description => $desc); + + if ( exists($scrip{$c}{$a}) ) { + $Scrip->Load( $scrip{$c}{$a} ); + } else { # need to create it + + if ( !exists($condition{$c}) ) { + warn "ScripCondition '$c' not found.\n"; + next; + } + if ( !exists($action{$a}) ) { + warn "ScripAction '$a' not found.\n"; + next; + } + if ( !exists($template{'0'}{$t}) ) { + # a global template with this name has to exist, at least + warn "Template '$t' not found.\n"; + next; + } + my %new_param = ( + ScripCondition => $condition{$c}->[0], + ScripAction => $action{$a}->[0], + Template => $t, # scrips.template is now the name, not the id + Queue => 0, + Description => $desc, + ); + warn "Creating scrip: $c $a [$t]\n"; + my ($val, $msg) = $Scrip->Create(%new_param); die $msg if !$val; - } + + } #if $scrip{...} + # set the Immutable attribute on them if needed + # no longer needed; you can't delete scrips through the UI anyway, only + # disable them + #if ( !$Scrip->FirstAttribute('Immutable') ) { + # my ($val, $msg) = + # $Scrip->SetAttribute(Name => 'Immutable', Content => '1'); + # die $msg if !$val; + #} + } #foreach (@Scrips) + # one-time fix: accumulator fields (support time, etc.) that had values + # entered on ticket creation need OCFV records attached to their Create + # transactions + my $sql = 'SELECT first_ocfv.ObjectId, first_ocfv.Created, Content '. + 'FROM ObjectCustomFieldValues as first_ocfv '. + 'JOIN ('. + # subquery to get the first OCFV with a certain name for each ticket + 'SELECT min(ObjectCustomFieldValues.Id) AS Id '. + 'FROM ObjectCustomFieldValues '. + 'JOIN CustomFields '. + 'ON (ObjectCustomFieldValues.CustomField = CustomFields.Id) '. + 'WHERE ObjectType = \'RT::Ticket\' '. + 'AND CustomFields.Name = ? '. + 'GROUP BY ObjectId'. + ') AS first_ocfv_id USING (Id) '. + 'JOIN ('. + # subquery to get the first transaction date for each ticket + # other than the Create + 'SELECT ObjectId, min(Created) AS Created FROM Transactions '. + 'WHERE ObjectType = \'RT::Ticket\' '. + 'AND Type != \'Create\' '. + 'GROUP BY ObjectId'. + ') AS first_txn ON (first_ocfv.ObjectId = first_txn.ObjectId) '. + # where the ticket custom field acquired a value before any transactions + # on the ticket (i.e. it was set on ticket creation) + 'WHERE first_ocfv.Created < first_txn.Created '. + # and we haven't already fixed the ticket + 'AND NOT EXISTS('. + 'SELECT 1 FROM Transactions JOIN ObjectCustomFieldValues '. + 'ON (Transactions.Id = ObjectCustomFieldValues.ObjectId) '. + 'JOIN CustomFields '. + 'ON (ObjectCustomFieldValues.CustomField = CustomFields.Id) '. + 'WHERE ObjectCustomFieldValues.ObjectType = \'RT::Transaction\' '. + 'AND CustomFields.Name = ? '. + 'AND Transactions.Type = \'Create\''. + 'AND Transactions.ObjectType = \'RT::Ticket\''. + 'AND Transactions.ObjectId = first_ocfv.ObjectId'. + ')'; + #whew + + # prior to this fix, the only name an accumulate field could possibly have + # was "Support time". + my $sth = $dbh->prepare($sql); + $sth->execute('Support time', 'Support time'); + my $rows = $sth->rows; + warn "Fixing support time on $rows rows...\n" if $rows > 0; + while ( my $row = $sth->fetchrow_arrayref ) { + my ($tid, $created, $content) = @$row; + my $Txns = RT::Transactions->new($CurrentUser); + $Txns->Limit(FIELD => 'ObjectId', VALUE => $tid); + $Txns->Limit(FIELD => 'ObjectType', VALUE => 'RT::Ticket'); + $Txns->Limit(FIELD => 'Type', VALUE => 'Create'); + my $CreateTxn = $Txns->First; + if ($CreateTxn) { + my ($val, $msg) = $CreateTxn->AddCustomFieldValue( + Field => 'Support time', + Value => $content, + RecordTransaction => 0, + ); + warn "Error setting transaction support time: $msg\n" unless $val; + } else { + warn "Create transaction not found for ticket $tid.\n"; + } + } + + my $cve_2013_3373_sql = ''; + if ( driver_name =~ /^Pg/i ) { + $cve_2013_3373_sql = q( + UPDATE Tickets SET Subject = REPLACE(Subject,E'\n','') + ); + } elsif ( driver_name =~ /^mysql/i ) { + $cve_2013_3373_sql = q( + UPDATE Tickets SET Subject = REPLACE(Subject,'\n',''); + ); + } else { + warn "WARNING: Don't know how to update RT Ticket Subjects for your database driver for CVE-2013-3373"; + } + if ( $cve_2013_3373_sql ) { + my $cve_2013_3373_sth = $dbh->prepare($cve_2013_3373_sql) + or die $dbh->errstr; + $cve_2013_3373_sth->execute + or die $cve_2013_3373_sth->errstr; + } + + # Remove dangling customer links, if any + my %target_pkey = ('cust_main' => 'custnum', 'cust_svc' => 'svcnum'); + for my $table (keys %target_pkey) { + my $pkey = $target_pkey{$table}; + my $rows = $dbh->do( + "DELETE FROM Links WHERE id IN( + SELECT id FROM ( + SELECT Links.id FROM Links LEFT JOIN $table ON (Links.Target = + 'freeside://freeside/$table/' || $table.$pkey) + WHERE Links.Target like 'freeside://freeside/$table/%' + AND $table.$pkey IS NULL + ) AS x + )" + ) or die $dbh->errstr; + warn "Removed $rows dangling ticket-$table links\n" if $rows > 0; + } + + # Fix ticket transactions on the Time* fields where the NewValue (or + # OldValue, though this is not known to happen) is an empty string + foreach (qw(newvalue oldvalue)) { + my $rows = $dbh->do( + "UPDATE Transactions SET $_ = '0' WHERE ObjectType='RT::Ticket' AND ". + "Field IN ('TimeWorked', 'TimeEstimated', 'TimeLeft') AND $_ = ''" + ) or die $dbh->errstr; + warn "Fixed $rows transactions with empty time values\n" if $rows > 0; + } + + # One-time fix: We've created a "BulkUpdateTickets" access right; grant + # it to all auth'd users initially. + eval "use FS::upgrade_journal;"; + my $upgrade = 'RT_add_BulkUpdateTickets_ACL'; + if (!FS::upgrade_journal->is_done($upgrade)) { + my $groups = RT::Groups->new(RT->SystemUser); + $groups->LimitToEnabled; + $groups->LimitToSystemInternalGroups; + $groups->Limit(FIELD => 'Type', VALUE => 'Privileged', OPERATOR => '='); + my $group = $groups->First + or die "No RT internal group found for Privileged users"; + my ($val, $msg) = $group->PrincipalObj->GrantRight( + Right => 'BulkUpdateTickets', Object => RT->System + ); + die "Couldn't grant BulkUpdateTickets right to all users: $msg\n" + if !$val; + FS::upgrade_journal->set_done($upgrade); + } + return; }