use Win32::Clipboard;
use Net::FTP;

use strict;

#those files being processed are on AIX, where Oracle resides, so I first ftp them to my PC, where this script runs and toad runs
ftp_order_file($ARGV[0]);
#now read the file into this script, and it would be compared with indo in database.
my $original_orders = read_order_file($ARGV[0]);
#a sql query is formed based on the content of ftp'd files, and it is then posted to clipboard
post_order_query($original_orders);
#I would now go to toad, paste the query in clipboard to toad editor, and execute the query. The returned query result would be sent to clipboard. After the result is posted, I would come back, and hit enter, so the script would continue...
print "Hit enter to continue ...\n";
<STDIN>;
#the query result is parsed
my $database_orders = get_query_result();
#compare the two versions of facts, to see whether the info in the files are loaded correctly. 
diff($original_orders, $database_orders);
#this forms a query base on the error log created by my Oracle/Pro*c application, and check whether what described in that log matches the real situation in database.
post_asos_query($ARGV[0]);

sub post_asos_query {	
	my $order_file = shift;

	open(LOG, "<", "$order_file.log");
	my @log_lines = <LOG>;
	close(LOG);

	my $query = "select * from dcs3000.stor_auth_pref\n" .
				"where bitm_numb_dpac in (%s)\n" .
				"and orgu_numb_rpnt =\n" .
				"(select orgu_numb from dcs3000.orgu\n" . 
				"where sysdate between date_eff and date_inac\n" .
				"and user_intf_numb = %s)\n" .
				"and sysdate between date_eff and date_inac";
	my $start = 0;
	my $no_asos = {};
	foreach my $log_line (@log_lines) {
		chomp $log_line;
		if (substr($log_line, 0, 1) eq "-") {
			$start = 1;
		} else {
			if ($start) {
				if (!length($log_line)) {
					$start = 0;
				} else {
					my $item = substr($log_line, 61, 20);
					$item =~ s/ //g;
					my $store = substr($log_line, 95, 8);
					$store =~ s/ //g;
					push @{$no_asos->{$store}}, $item;
				}
			}
		}
	}

	my @queries;
	foreach my $store (keys %{$no_asos}) {
		push @queries, sprintf($query, join(",", @{$no_asos->{$store}}), $store);
	}
	my $real_query = join(" union ", @queries);
	my $clip = Win32::Clipboard();
	$clip->Set($real_query);
}

sub diff {
	my ($origin, $database) = @_;
	my $times;
	open(DIFF, ">", "$ARGV[0].diff");
	foreach my $order (sort keys %{$origin}) {
		foreach my $item (sort keys %{$origin->{$order}}) {
			if (defined($database->{$order}->{$item})) {
				if (!defined($times)) {
					$times = $database->{$order}->{$item} / $origin->{$order}->{$item};
					print "this order file has been loaded $times times\n";
				}
				if ($origin->{$order}->{$item} != ($database->{$order}->{$item} / $times)) {
					print "order = $order, item = $item, origin = $origin->{$order}->{$item}, database = $database->{$order}->{$item} / $times\n";
					print DIFF "order = $order, item = $item, origin = $origin->{$order}->{$item}, database = $database->{$order}->{$item} / $times\n";
				}
			} else {
				print "order = $order, item = $item, origin = $origin->{$order}->{$item}, database = undef\n";
				print DIFF "order = $order, item = $item, origin = $origin->{$order}->{$item}, database = undef\n";
			}
		}
	}
	close(DIFF);
}

sub ftp_order_file {
	my $order_file = shift;
	my $ftp = new Net::FTP("ofgaix2") || die "fail";
	$ftp->login("aseries", "aseries2");
	$ftp->cwd("/aa/a00/rel2.4/env/AATS1/GlomOrders");
	$ftp->get($order_file);
	$ftp->get("$order_file.log");
	$ftp->close();
}

sub read_order_file {
	my $order_file = shift;
	open(ORDER, "<", "$order_file");
	my @lines = <ORDER>;
	close(ORDER);

	my $original_orders = {};
	for (my $line_numb = 1; $line_numb <= $#lines; $line_numb ++) {
		if (length($lines[$line_numb]) > 60) {
			my $order = substr($lines[$line_numb], 47, 8);
			my $item = substr($lines[$line_numb], 158, 10);
			$item =~ s/ //g;
			my $quantity = substr($lines[$line_numb], 180, 13);
			$original_orders->{$order}->{$item} += $quantity;
		}
	}
	return $original_orders;
}

sub post_order_query {
	my $original_orders = shift;
	my $clip = Win32::Clipboard();
	my $order_query = "select h.extl_ordr_id, d.olin_numb, d.bitm_numb, d.ordq_orig, d.ordq_crnt\n" .
					  "from dcs3000.oom_ordr_hdr h, dcs3000.oom_olin d\n" .
					  "where h.extl_ordr_id in (%s)\n" .
					  "and h.ordr_numb = d.ordr_numb\n" .
					  "and d.time_row_crtn > sysdate - 2\n" .
					  "order by h.ordr_numb desc";
	my $real_order_query = sprintf($order_query, "\'" . join("\',\'", keys %{$original_orders}) . "\'");
	$clip->Set($real_order_query);
}

sub get_query_result {
	my $clip = Win32::Clipboard();
	my $dump = $clip->Get();
	my @database_lines = split "\r\n", $dump;
	my $splited = [];
	my $database_orders = {};
	for (my $line_numb = 1; $line_numb <= $#database_lines; $line_numb ++) {
		my @s = split(",", $database_lines[$line_numb]);
		push @{$splited}, \@s;
		$database_orders->{$s[0]}->{$s[2]} += $s[4];
	}
	return $database_orders;
}