#!/usr/bin/perl
# Borra ticket(s) de la DB dado el TID o numero(s) de ticket
# Escrito por Alan <lameventanas at gmail dot com>

use strict;
use warnings;
use DBI;

if ($#ARGV < 0) {
	print STDERR "Syntax: ticketrm.pl <tid or ticket number> [...]\n";
	exit 1;
}

# Cambiar el IP para conectarse a otra maquina:
my $DBNAME = 'host=127.0.0.1;db=otrs;';
my $DBUSER = 'otrs';
my $DBPASS = readPassword('Database');

my $QGetTicket = 'select id from ticket where tn = ?';

my @RMQUERIES = (
	{
	query => 'delete from article_plain where article_id = (select id from article where ticket_id = ?)',
	desc => 'Deleting from article_plain',
	},
	{
	query => 'delete from article_attachment where article_id = (select id from article where ticket_id = ?)',
	desc => 'Deleting from article_attachment',
	},
	{
	query => 'delete from article where ticket_id = ?',
	desc => 'Deleting from article',
	},
	{
	query => 'delete from ticket where id = ?',
	desc => 'Deleting from ticket',
	}
);

my $dbh = DBI->connect('DBI:mysql:'.$DBNAME, $DBUSER, $DBPASS, {RaiseError => 0, PrintError => 1}) or die "Error connecting to DB $DBNAME: ".$DBI::errstr;

my @ticket;
my $sth;

foreach my $arg (@ARGV) {
	if ($arg =~/^\d{16}$/) {
		# numero de ticket
		$sth = $dbh->prepare($QGetTicket) or die "Error preparing query: ".$dbh->errstr;
		$sth->execute($arg) or die "Error executing query: ".$dbh->errstr;
		my $row = $sth->fetchrow_arrayref();
		if ($row) {
			print "Canonized ticket $arg to $row->[0]\n";
			push(@ticket, $row->[0]);
			$sth->finish();
		} else {
			print "Couldn't find a ticket matching $arg\n";
		}
	} elsif ($arg =~/^\d+$/) {
		# tid
		push(@ticket, $arg);
	} else {
		print STDERR "Syntax: ticketrm.pl <tid or ticket number> [...]\n";
		exit 1;
	}
}

# tratamos de usar transacciones
foreach my $tid (@ticket) {
	print "Removing ticket $tid\n";
	$dbh->{AutoCommit} = 0;
	foreach my $query (@RMQUERIES) {
		print " - ",$query->{'desc'},"\n";
		$sth = $dbh->prepare($query->{'query'}) or die "Error preparing query: ".$DBI::errstr;
		$sth->execute($tid) or die "Error executing query: ".$DBI::errstr;
	}
	$dbh->commit;
}
exit 0;

# pide la clave
sub readPassword {
	# Comentar esto si no es linux:
	system 'stty -echo';
	print STDERR shift().' Password: ';
	chomp($_=<STDIN>);
	# Comentar esto si no es linux:
	system 'stty echo';
	print STDERR "\n";
	return $_;
}