#!/usr/local/perl510/bin/perl

use feature ":5.10"; # Use all new features in Perl 5.10
use strict;  use warnings;     use Data::Dumper;
use FindBin; use Getopt::Long; use IO::File;
use DBIx::Simple;  

my $DEBUG = 1;
my $TEST  = 0;
my $TEST_TO_DATE = 0;

my %db = (
	cfg_file	=> '.inet_audit.db.cfg',
	username	=> undef,
	password	=> undef,		
	);

my @full_network_access;  	# From CsicoSecure (via Oracle)

#//////////////////////////////////////////////////////////////
sub options  {
	my $numopts = @ARGV;
	Getopt::Long::Configure('bundling');
	GetOptions(		
		'D|debug+'	=> \$DEBUG,
		'T|test=i'	=> \$TEST,
		'2|test_to_date+'	=> \$TEST_TO_DATE,
		);
	return;
	}

#///////////////////////////////////////////////////////////
# Read DB Cfg file (i.e. username and password)
sub get_db_cfg  {
	# Sets the cfg file path and then reads in the username and password).
	# Sets the following: $db{cfg_file}, db{username} $db{password} 
	return;
	}

#///////////////////////////////////////////////////////////
sub get_users_from_db {

   	my @dt = split q{ }, uc scalar localtime; 

	my $timestamp =  
			   $TEST==1 ?  'sysdate'
			:  $TEST==2  ?  sprintf "'%02s-%03s-%04d %08s'", $dt[2], $dt[1], $dt[4], $dt[3]
			:  $TEST==3  ?  sprintf "'%02s-%03s-%02d %08s'", $dt[2], $dt[1], substr($dt[4],2), $dt[3]
			:  $TEST==4  ?  sprintf "'%02s-%03s-%02d %05s'", $dt[2], $dt[1], substr($dt[4],2), substr($dt[3],0,5)
			:  $TEST==5  ?  sprintf "'%02s-%03s-%04d %05s'", $dt[2], $dt[1], $dt[4], substr($dt[3],0,5)
			:  $TEST==6  ?  sprintf "'%02s-%03s-%04d'",      $dt[2], $dt[1], $dt[4]
			:  $TEST==7  ?  sprintf "'%02s-%03s-%02d'",      $dt[2], $dt[1], substr($dt[4],2)
			:  $TEST==8  ?  time
			:  $TEST==9  ?  "TO_DATE('".time."')"
			:  'nothing you have seen before!! :-)'
			;
	
	if( $TEST >= 1 ) {
		if( $TEST_TO_DATE ) { $TEST = 2 }
		else                { $TEST = 1 }
		}

	my @sql = (
qq{SELECT user_id FROM ACS.USERS WHERE group_name = 'Full Network Access'}, # Works. The rest fail
qq{SELECT user_id FROM ACS.USERS WHERE group_name = 'Full Network Access' AND update_timestamp LIKE $timestamp},
qq{SELECT user_id FROM ACS.USERS WHERE group_name = 'Full Network Access' AND update_timestamp LIKE TO_DATE($timestamp)},
);

	say "\$sql[$TEST]=$sql[$TEST]" if $DEBUG;
	
	eval {
		my $dbh = DBIx::Simple->connect( 'DBI:Oracle:nms', $db{username}, $db{password}, 
			{ RaiseError => 1, AutoCommit => 1} );
		@full_network_access = $dbh->query( $sql[$TEST] )->flat;
		};		

	print Data::Dumper->Dump([\@full_network_access, ],['full_network_access']) if $DEBUG;
		
	if( $@ ) { die "SQL ERROR: $@"; }
	return;
	}

#///////////////////////////////////////////////////////////
MAIN: {
	options();
	get_db_cfg();
	get_users_from_db();
	exit 0;
	}