my ($Type, $Subject, $AllComments, $OldValue, $NewValue, $UserId, $AllText) = ('', '', '', '', '', 0, '');

$Type = $self->TransactionObj->Type;
$Subject = $self->TicketObj->Subject;
$OldValue = $self->TransactionObj->OldValue;
$NewValue = $self->TransactionObj->NewValue;
$UserId = $self->TransactionObj->Creator;

if ($Type eq 'Status' && $NewValue eq 'resolved') {
	my $txns = $self->TicketObj->Transactions;
	$txns->OrderByCols(
	  { FIELD => 'Created', ORDER => 'ASC' },
	  { FIELD => 'id', ORDER => 'ASC' },
	);
	$txns->Limit(FIELD => 'Type', VALUE => 'Comment');
	$txns->LimitToTicket ($self->TicketObj->Id);
	$txns->RowsPerPage(100);
	while (my $txn = $txns->Next) {
		$AllComments .= "\n\n" . $txn->Content;
	}
}

$AllText = $Subject . "\n\n" . $AllComments;

my ($defsth, $defref, $progsth, $progref);

$defsth = $RT::Handle->dbh->prepare ('SELECT `Achievement`,`Target`,`Clear`,`Code` FROM `AchievementsDef`');
$progsth = $RT::Handle->dbh->prepare ('SELECT `Count` FROM `AchievementsProgress` WHERE `UserId`=? AND `Achievement`=?');
my $progex = 0;

$defsth->execute ();
while ($defref = $defsth->fetchrow_arrayref ()) {
	my $testresult = eval $defref->[3] ? 1 : 0;
	next if ($testresult != 1);
	$progex = 1;
	my $interval = '10 YEAR';
	$interval = '1 HOUR' if ($defref->[2] eq 'Hourly');
	$interval = '1 DAY' if ($defref->[2] eq 'Daily');
	$interval = '1 WEEK' if ($defref->[2] eq 'Weekly');
	$interval = '1 MONTH' if ($defref->[2] eq 'Monthly');
	$interval = '1 YEAR' if ($defref->[2] eq 'Annually');
	$RT::Handle->dbh->do ('DELETE FROM `AchievementsProgress` WHERE `UserId`=? AND `Achievement`=? AND `ClearNext` <= NOW()', undef, $UserId, $defref->[0]);
	$progsth->execute ($UserId, $defref->[0]);
	$progref = $progsth->fetchrow_arrayref ();
	my $progcount = 0;
	if ((not defined $progref) || (not defined $progref->[0])) {
		$RT::Handle->dbh->do ('INSERT INTO `AchievementsProgress` (`UserId`,`Achievement`,`Count`,`ClearNext`) VALUES (?,?,?,DATE_ADD(NOW(), INTERVAL '.$interval.'))', undef, $UserId, $defref->[0], 1);
		$progcount = 1;
	} else {
		$progcount = $progref->[0] + 1;
		$RT::Handle->dbh->do ('UPDATE `AchievementsProgress` SET `Count`=? WHERE `UserId`=? AND `Achievement`=?', undef, $progcount, $UserId, $defref->[0]);
	}
	next if ($progcount < $defref->[1]);
	$RT::Handle->dbh->do ('INSERT INTO `AchievementsEarned` (`UserId`,`Achievement`,`When`) VALUES (?,?,NOW())', undef, $UserId, $defref->[0]);
	$RT::Handle->dbh->do ('DELETE FROM `AchievementsProgress` WHERE `UserId`=? AND `Achievement`=?', undef, $UserId, $defref->[0]);
}
$defsth->finish ();
$progsth->finish () if ($progex);
