#!/usr/bin/perl
use DBI;
use DBD::ODBC;
use XML::Simple;
use Data::Dumper;
use Net::FTP;
use Getopt::Std;
$|=1;

%opt=();
getopts("ahf:",\%opt);

if ($opt{h}) {
	print ("Usage: mssql.pl [-a] [-h] [-f filename]\n");
	print ("   -a: all files on the ftp server\n");
	print ("   -h: help (this text)\n");
	print ("   -f: specify a local filename to process\n");
	print ("   no options: process just the most recent file from the ftp server\n");
	exit(0);
}

$dbname = "TroyODBC";
$redemption_table_name = "Loyalty_Redemption";
$issuance_table_name = "Loyalty_Issuance";
$ftp_host = "ftp.metrosplash.com";
$ftp_user = "mssg-ruttersgroup";
$ftp_passwd = "i81F6hnb";
$ftp_dir = "/frommssg/rewardactivity";

$dbh = DBI-> connect("dbi:ODBC:$dbname");

sub parse_and_insert {
	($filename) = @_;
	print "Reading/Parsing XML file: $filename\n";
	$ref = XMLin($filename);
#	open(REPORT,">xml_report.txt");

#	print "DBD::ODBC driver version: ".$DBD::ODBC::VERSION."\n";
#	print "MSSQL Database name: $dbname\n";

	print "Dumping data into the $issuance_table_name table: ";
	$num_processed = 0;
	foreach $xans (@{$ref->{Issued}->{IssuanceTransaction}}) {
		$mnum = $xans->{MemberNumber};
		$pan = $xans->{PAN};
		$suuid = $xans->{StoreUUID};
		$tdate = $xans->{TransactionDate};
		$tdate =~ s/T/ /g;
		$tuuid = $xans->{TransactionUUID};
		if (ref($xans->{Reward}) eq 'ARRAY') {
			foreach $reward (@{$xans->{Reward}}) {
				$xdate = $reward->{ExpirationDate};
				$xdate =~ s/T/ /g;
				$pdesc = $reward->{PromotionDescription};
				$pdesc =~ s/'/''/g;
				$ptype = $reward->{PromotionType};
				$rewamt = $reward->{RewardAmount};
				$rwtyp = $reward->{RewardType};
				$rewuuid = $reward->{RewardUUID};
				$vol = $reward->{VolumeLimit};
				$gtin = $reward->{ContributingProducts}->{Product}->{GTIN};
#					print REPORT "Loy_Iss: $mnum|$pan|$suuid|$tdate|$tuuid|$xdate|$pdesc|$ptype|$rewamt|$rwtyp|$rewuuid|$vol|$gtin\n";
					$sql = "insert into $issuance_table_name values ('$mnum','$pan','$suuid','$tdate','$tuuid','$xdate','$pdesc','$ptype',$rewamt,'$rwtyp','$rewuuid',$vol,'$gtin')";
					$sth = $dbh->prepare($sql);
					$sth->execute();
					$num_processed++;
			}
		} else {
			$xdate = $xans->{Reward}->{ExpirationDate};
			$xdate =~ s/T/ /g;
			$pdesc = $xans->{Reward}->{PromotionDescription};
			$pdesc =~ s/'/''/g;
			$ptype = $xans->{Reward}->{PromotionType};
			$rewamt = $xans->{Reward}->{RewardAmount};
			$rwtyp = $xans->{Reward}->{RewardType};
			$rewuuid = $xans->{Reward}->{RewardUUID};
			$vol = $xans->{Reward}->{VolumeLimit};
			$gtin = $xans->{Reward}->{ContributingProducts}->{Product}->{GTIN};
#			print REPORT "Loy_Iss: $mnum|$pan|$suuid|$tdate|$tuuid|$xdate|$pdesc|$ptype|$rewamt|$rwtyp|$rewuuid|$vol|$gtin\n";
			$sql = "insert into $issuance_table_name values ('$mnum','$pan','$suuid','$tdate','$tuuid','$xdate','$pdesc','$ptype',$rewamt,'$rwtyp','$rewuuid',$vol,'$gtin')";
			$sth = $dbh->prepare($sql);
			$sth->execute();
			$num_processed++;
		}
	}
	print "$num_processed records\n";

	print "Dumping data into the $redemption_table_name table: ";
	$num_processed = 0;
	foreach $xans (@{$ref->{Redeemed}->{RedemptionTransaction}}) {
		$dpric = $xans->{DiscountPrice};
		$mnum = $xans->{MemberNumber};
		$pan = $xans->{PAN};
		$rdtyp = $xans->{RedemptionType};
		$rwtyp = $xans->{RewardType};
		$stotl = $xans->{SaleTotal};
		$suuid = $xans->{StoreUUID};
		$spric = $xans->{StreetPrice};
		$tdate = $xans->{TransactionDate};
		$tdate =~ s/T/ /g;
		$tuuid = $xans->{TransactionUUID};
		$vol = $xans->{VolumeDelivered};
		$vlim = $xans->{VolumeLimit};
		if (ref($xans->{Redemption}) eq 'ARRAY') {
			foreach $redemp (@{$xans->{Redemption}}) {
				$istore = $redemp->{IssuanceStoreUUID};
				$rewamt = $redemp->{RedemptionAmount};
				$rewuuid = $redemp->{RewardUUID};
#				print REPORT "Loy_Red: $dpric|$mnum|$pan|$rdtyp|$rwtyp|$stotl|$suuid|$spric|$tdate|$tuuid|$vol|$vlim|$istore|$rewamt|$rewuuid\n";
				$sql = "insert into $redemption_table_name values ($dpric,$mnum,'$pan','$rdtyp','$rwtyp',$stotl,'$suuid',$spric,'$tdate','$tuuid',$vol,$vlim,'$istore',$rewamt,'$rewuuid')";
				$sth = $dbh->prepare($sql);
				$sth->execute();
				$num_processed++;
			}
		} else {
			$istore = $xans->{Redemption}->{IssuanceStoreUUID};
			$rewamt = $xans->{Redemption}->{RedemptionAmount};
			$rewuuid = $xans->{Redemption}->{RewardUUID};
#			print REPORT "Loy_Red: $dpric|$mnum|$pan|$rdtyp|$rwtyp|$stotl|$suuid|$spric|$tdate|$tuuid|$vol|$vlim|$istore|$rewamt|$rewuuid\n";
			$sql = "insert into $redemption_table_name values ($dpric,$mnum,'$pan','$rdtyp','$rwtyp',$stotl,'$suuid',$spric,'$tdate','$tuuid',$vol,$vlim,'$istore',$rewamt,'$rewuuid')";
			$sth = $dbh->prepare($sql);
			$sth->execute();
			$num_processed++;
		}
	}
#	close REPORT;
	print "$num_processed records\n";
}

$max_time = 0;
$max_name = "";
$ftp = Net::FTP->new($ftp_host, Debug => 0) or die "can't connect: $@";
$ftp->login($ftp_user,$ftp_passwd) or die "can't login", $ftp->message;
$ftp->cwd($ftp_dir) or die "can't cwd", $ftp->message;
@files = $ftp->ls("RewardActivity*.xml");
if ($opt{a}) {
	foreach $file (@files) {
		$mtime = $ftp->mdtm($file);
		if ($mtime > 1) {
#			print ("$file: $mtime\n");
			if (!-e $file) {
				print ("FTP: downloading $file\n");
				$ftp->get($file);
			}
		}
	}
	foreach $file (@files) {
		$mtime = $ftp->mdtm($file);
		if ($mtime > 1) {
#			print ("$file: $mtime\n");
			print "------------------------------------------------------------------------------\n";
			parse_and_insert($file);
		}
	}
} elsif ($opt{f}) {
	parse_and_insert($opt{f});
} else {
	foreach $file (@files) {
		$mtime = $ftp->mdtm($file);
		if ($mtime > 1) {
#			print ("$file: $mtime\n");
			if ($mtime > $max_time) {
				$max_time = $mtime;
				$max_name = $file;
			}
		}
	}
	if (!-e $max_name) {	# check for an existing file (aka, already processed)
		print ("FTP: downloading $max_name\n");
		$ftp->get($max_name);
		parse_and_insert($max_name);
	} else {
		print ("Skipping: $max_name - already have it locally - don't want to process it again (dupes)\n");
	}
}
$ftp->quit;