#!/usr/bin/perl -w
#
# MySQL purge tool
#
#

use strict;
use DBI();
use DateTime::Format::Duration;
use DateTime::Format::MySQL;
use DateTime::Duration;

my $dbh;
my $mysql_host = '10.10.10.10';
my $mysql_db = 'DATABASE';
my $mysql_user = 'USER';
my $mysql_pass = 'PASSWORD';
my $dsn = "dbi:mysql:database=$mysql_db;host=$mysql_host";
my $debug_flag=0;

$dbh = DBI->connect($dsn, $mysql_user, $mysql_pass)
    or die "[-] can't connect to $dsn: $DBI::errstr";

print "[+] connection sucessfull to $dsn\n";

# GET NUMBER OF DAYS

my $query = "select SYSDATE()";
my $num; my $hndl; my $aa; my $x; my $y = 0; my $z; my $zz; my $s_date; my $s_duration; my $s_expire;
my $bb; my $cc; my $dd; my $ee; my $ff;

($hndl,$num) = select_query($query);
if ($num) 
{
	$hndl->execute();
	while(($x) = $hndl->fetchrow_array){ $s_date = $x};
}

print "[+] curent date: <$s_date>\n";

my $purge_table = 'TB_RESOURCE';
my $output_table = '';
my $purge_field = 'RESOURCE_VALUE';
my $match_field = 'RESOURCE';
my $match_field2 = 'CONTEXT';

$query = "select $purge_field from $purge_table where $match_field='purgeTime' and $match_field2='GENERAL'";

($hndl,$num) = select_query($query);
if ($num) 
{
	$hndl->execute();
	while(($x) = $hndl->fetchrow_array){ $y = $x};
}

print "[+] number of days to row expire: $y\n";
$s_expire = $y;

$purge_table = 'TB_PROVISIONING';
$output_table = '';
$purge_field = 'DT_LINK_GENERATION';
my $purge_field2 = 'ID_PROVISIONING';

#$query = "select NUM_RETRY,ID_PROVISIONING,NETPHONE,SOFTPHONE,DT_LINK_GENERATION from $purge_table where $purge_field is not null";
$query = "select NUM_RETRY,ID_PROVISIONING,NETPHONE,SOFTPHONE,DT_LINK_GENERATION,DOCUMENT_TYPE, DOCUMENT_NUMBER, EMAIL, FK_INSTANCE, FK_CNL from $purge_table";

($hndl,$num) = select_query($query);

if ($num) 
{
	$hndl->execute();
	while(($aa, $x, $y, $z, $zz, $bb, $cc, $dd, $ee, $ff) = $hndl->fetchrow_array)
	{
		if (!$aa)	{$aa = '0'};	# NUM_RETRY
		if (!$x)	{$x = ''};	# ID_PROVISIONING
		if (!$y)	{$y = ''};	# NETPHONE
		if (!$z)	{$z = ''};	# SOFTPHONE
		if (!$zz)	{$zz = '2008-11-11 00:00:00'};	# DT_LINK_GENERATION
		if (!$bb)	{$bb = ''};	# DOCUMENT_TYPE
		if (!$cc)	{$cc = ''};	# DOCUMENT_NUMBER
		if (!$dd)	{$dd = ''};	# EMAIL
		if (!$ee)	{$ee = ''};	# FK_INSTANCE
		if (!$ff)	{$ff = ''};	# FK_CNL

		my $start_date;# = DateTime::Format::MySQL->parse_datetime("$zz");
		my $end_date;# = DateTime::Format::MySQL->parse_datetime("$s_date");

#		my $durat = $end_date->delta_days( $start_date );
#		$s_duration = $durat->delta_days;
exit(0);
		if ($debug_flag) {print "s_duration = $s_duration | s_expire = $s_expire\n";}
		if ($s_duration > $s_expire) {

			print "|$y|$z|";

			if ($aa == '0') {
			#	select_query("DELETE FROM $purge_table WHERE $purge_field2=\"$x\"\n");
			#	select_query('COMMIT');
			#	select_query("INSERT INTO TB_EXPIRED_PROVISIONING VALUES (NULL, \"$y\", \"$z\", \"$bb\",\"$cc\",\"$dd\",\"$zz\",\"$ee\",\"$ff\", \"$s_date\")");
			#	select_query('COMMIT');
				print "0|";
			}
			print "1|\n";
		}
	}
}

if (!$num) 
{
	print "yeah, no $num\n";
}

exit(0);

sub select_query
	{
	    my $query = $_[0];
	    my $sth = $dbh->prepare($query);
	    my $i=0;
	    my $hashref;
	    $sth->execute();
    return $sth, $sth->rows;    #return the handle
}