#!/usr/bin/perl -w
use strict;
use warnings;
use DBI;
use Net::LDAP;
#use List::Compare;

my ($uid) = $ARGV[0];

if ($#ARGV > 0 ) {
        print " \n";
        print "\nUsage: SCRIPTNAME.pl UID (BJB UID- use the Uppercase 'U' letter)\n";
exit;
}
#
                                ##### DB Related #################
my $dbdriver = "Oracle";
my $dbname = "xxx";
my $dbhost="xxx";
my $dbport="1521";
my $dbdsn = "xxx";
my $dbuserid = 'xxx';
my $dbpassword = 'xxx';

my $dbh = DBI->connect($dbdsn, $dbuserid, $dbpassword ) or die $DBI::errstr;

my $sth = $dbh->prepare("SELECT users.id,users.displayname,users.enabled,users.name,regions.name AS defaultRegion FROM regions JOIN users ON users.regionid = regions.id WHERE users.regionid = regions.id") or die "Can't prepare statement: $DBI::errstr";
$sth->execute();

## Display any printed messages from the sql query result
if (defined($dbh->err)) {
        if ($dbh->err eq 0) {
                print "Warning message : ", $dbh->errstr, "\n";
        } elsif ($dbh->err eq '') {
                print "Informational message : ", $dbh->errstr, "\n";
        }
}
print " \n";
print " \n";
print "##############  Database Query Results ##################\n";
## Print rows affected
printf "Rows affected: %s\n",$sth->rows,"\n";
## Print the column name.
#print "$sth->{NAME}->[0]\n";
my $nf = $sth->{NUM_OF_FIELDS};
print "This statement returns $nf fields\n";
for ( my $i = 0; $i < $nf; $i++ ) {
        print "$sth->{NAME}->[$i]\t";
}
print " \n";

## Fetch and display the result set value.
while ( my @row = $sth->fetchrow_array ) {
        foreach (@row) {$_ = '' unless defined}; # change all NULLs to empty strings
        print "@row\t\n";
}

## Hashmap for the DB 
my $dbUsers = {};
$sth->execute();
while( my @row = $sth->fetchrow_array() ) {
  $dbUsers->{$row[0]} = {
    id => $row[0],
    displayName => $row[1],
    enabled => $row[2],
    name => $row[3],
    defaultRegionId => $row[4]
  };
}

foreach my $uid (keys %$dbUsers) {
  print $dbUsers->{$uid}->{'displayName'} . "\n";
  print $dbUsers->{$uid}->{'name'} . "\n";
  print $dbUsers->{$uid}->{'id'} . "\n";
  print $dbUsers->{$uid}->{'enabled'} . "\n";
  print $dbUsers->{$uid}->{'defaultRegionId'} . "\n";
}

$sth->finish();
$dbh->disconnect;


                                ######## LDAP Related ##############

my $server = "xxx";
my $ldap = Net::LDAP->new( $server ) or die $@;

my $ldapuser_dn = "xxx";
my $ldappass = "xxx";
$ldap->bind( $ldapuser_dn,  password => $ldappass );

my $result = $ldap->search(
# Searching for all the users from AD group
        base   => 'DC=xxx,DC=xxx',
        filter => "(&(CN=xxx))",
        attrs  => ['member']
);
die $result->error if $result->code;

my $ldapUsers = {};

foreach my $entry ($result->entries) {
        print $entry;
        print "---";
        my @members = $entry->get_value('member');
        printf "COUNT: %s\n", scalar @members;

        foreach my $member (@members) {
                my $user = $ldap->search(
                  base   => $member,
                  scope  => 'base',
                  filter => 'cn=*'
                );
                die $user->error if $user->code;
                foreach my $entry ($user->entries) {
                  my $id = $entry->get_value('sAMAccountName');
                  my $id2 = substr $id, 1;
                  $ldapUsers->{$id2} = {
                        id => $id2,
                        name => $entry->get_value('sAMAccountName'),
                        displayName => $entry->get_value('displayName'),
                        defaulteRegion => $entry->get_value('extensionAttribute1')
                  };
                }
        }
}

foreach my $uid (keys %$ldapUsers) {
        print $ldapUsers->{$uid}->{'displayName'} . "\n";
        print $ldapUsers->{$uid}->{'name'} . "\n";
        print $ldapUsers->{$uid}->{'id'} . "\n";
        print $ldapUsers->{$uid}->{'defaulteRegion'} . "\n";
}

print "====================END===========================\n";
#$ldap->unbind;

##I am looking for the code here to compare the "name" from Ldap and DB, if the ldap user is not present in the DB, then add him to DB.. 

## DB query looks like below
insert into users (id, name, displayname, regionid) values ('123456', 'U123457', 'Doe, John', select id from regions where name='$user->{'defaultRegion'}')

##################