#
#
# This script is used by passwordExpiry metric to send out the list of db user accounts whose passwords are/to be expired to 
# emd_to_email_address defined in emd.properties.
# This script depends on the following other email related values passed down from metric:
# emd_from_email_address, em_email_gateway, emd_to_email_address.
#
# Please ensure that above properties are set with valid values in emd.properties file.

use strict;
use Oraperl;
use Net::SMTP;
use DBI;

require "emd_common.pl";
require "semd_common.pl";

if($#ARGV != 4)
{
  EMD_PERL_ERROR("pwdexpiry: number of args is $#ARGV \n");
  EMD_PERL_ERROR("pwdexpiry: Invalid number of arguments passed");
  die "em_error= Invalid number of arguments passed \n";
}

my $siteUrl       = shift(@ARGV);
my $toEmail       = shift(@ARGV);
my $fromEmail       = shift(@ARGV);
my $gateway       = shift(@ARGV);
my $emdVersion       = shift(@ARGV);
#  EMD_PERL_ERROR("siteUrl value : $siteUrl");
#  EMD_PERL_ERROR("toEmail value : $toEmail");
#  EMD_PERL_ERROR("fromEmail value : $fromEmail");
#  EMD_PERL_ERROR("gateway value : $gateway");
#  EMD_PERL_ERROR("emdVersion value : $emdVersion");

my @fetch_row;
my $lda;
my $audit_trail_sql;
my $audit_trail_cur;
my $audit_trail_value;

my %stdinArgs = get_stdinvars();
my $username = $stdinArgs{"EM_TARGET_USERNAME"};
my $password = $stdinArgs{"EM_TARGET_PASSWORD"};
my $address = $ENV{EM_TARGET_ADDRESS};
my $targetName = $ENV{EM_TARGET_NAME};
my $targetType = $ENV{EM_TARGET_TYPE};

my $role = $ENV{EM_TARGET_ROLE};

my $mode = 0;
if($role =~ /SYSDBA/i)
{
  $mode = 2;
}
elsif($role =~ /SYSOPER/i)
{
  $mode = 4;
}

# --------------------------------------------------------------------
# +++ Establish Target DB Connection
# --------------------------------------------------------------------

$lda = DBI->connect('dbi:Oracle:', "$username@".$address, "$password",
    {ora_session_mode => $mode, PrintError => 0, RaiseError => 0})
    or die (filterOraError("em_error=Could not connect to $username/$address: $DBI::errstr\n", $DBI::err)); 
register_metric_call($lda);

# ---------------------------------
#  +++ Get expired accounts
# ---------------------------------

$audit_trail_sql = "SELECT rownum,username,days from (SELECT username,expiry_date-sysdate days ".
                   "FROM dba_users ".
                   "WHERE expiry_date-sysdate<=14 AND LOCK_DATE IS NULL ". " ORDER by days) ";

$audit_trail_cur = $lda->prepare($audit_trail_sql)
   or die (filterOraError("em_error=prepare($audit_trail_sql): $DBI::errstr\n", $DBI::err)); 
$audit_trail_cur->execute()
   or die (filterOraError("em_error=audit_trail_cur->execute(): $DBI::errstr\n", $DBI::err)); 

#@fetch_row = $audit_trail_cur->fetchrow_array();
#$audit_trail_value = $fetch_row[0];

my $expiryList="";
my $header1 = sprintf("     %-4s | %-40s | %-20s ",'No.','Username','Expires in (days)');
my $header2 = "    ------|------------------------------------------|----------------------";
my $header3 = "    ------------------------------------------------------------------------";
$expiryList .= $header3."\n";
$expiryList .= $header1."\n";
$expiryList .= $header2;
my $fmtline="";
my $cnt=0;
while (my ($rownum, $user_name, $days) = 
    $audit_trail_cur->fetchrow_array())  # keep fetching until 
                             # there's nothing left
{
    $fmtline = sprintf("    %5d | %-40s | %6.0f",$rownum,$user_name,$days); 
    $expiryList .= "\n" . $fmtline;
    $cnt += 1;
}

if ($cnt == 0)
{
  EMD_PERL_ERROR("pwdexpiry: No accounts are expiring. So email ignored.");
  exit(0);

}
$expiryList .= "\n".$header3;



#--------------------------
#  ++ Disconnect
#------------------------------

        $lda->disconnect
            or warn "disconnect $DBI::errstr\n";


# Sends e-mail
# 1st arg: gateway
# 2nd arg: to address
# 3rd arg: from address
# 4th arg: subject
# 5th arg: body
sub sendMail
{
  my $smtp;
  my $gateway = $_[0];
  my $toEmail = $_[1];
  my $fromEmail = $_[2];
  my $subject = $_[3];
  my $body = $_[4];
  $smtp = Net::SMTP->new($gateway);
  $smtp->mail($fromEmail);
  $smtp->to($toEmail);
  $smtp->data();
  $smtp->datasend("Subject: $subject\n");
  $smtp->datasend("$body");
  $smtp->dataend();
  $smtp->quit();

  EMD_PERL_DEBUG("pwdexpiry: Message Sent");
}

#my $toEmail = "$ENV{emd_email_address}";
#my $fromEmail ="$ENV{emd_from_email_address}";
#my $gateway = "$ENV{emd_email_gateway}";
#my $siteUrl ="$ENV{REPOSITORY_URL}";
#my $emdVersion = "$ENV{agentVersion}";
my $rest;


#
$_ = $siteUrl;
s/upload\///;
($siteUrl, $rest) = split(' ', $_, 2);

if(!defined $toEmail || $toEmail eq "")
{
  EMD_PERL_ERROR("pwdexpiry: emd_email_address is not correctly defined");
  die "em_error=please set the e-mail address to correct the issue \n";
}

#if(!defined $emdVersion || $emdVersion eq "")
#{
#  EMD_PERL_ERROR("pwdexpiry: agentVersion is not correctly defined");
#  die "em_error = agentVersion is not correctly defined \n";
#}
#if($emdVersion !~ /10\.2.*/)
#{
#  EMD_PERL_ERROR("dbdown: This script should only run with agent version = 10.2 (11g dbcontrol)");
#  exit(-1);
#}

chomp($fromEmail);
chomp($toEmail);

if(!defined $gateway || $gateway eq "")
{
  EMD_PERL_WARN("pwdexpiry: emd_email_gateway is not correctly defined");
  EMD_PERL_WARN("pwdexpiry: using mailhost as default");
  die "em_error = Please set the email gateway correctly.\n ";
}

if(!defined $fromEmail || $fromEmail eq "")
{
  EMD_PERL_WARN("pwdexpiry: emd_from_email_address is not correctly defined");
  EMD_PERL_WARN("pwdexpiry: using nobody as default");
  die "em_error = from email address is not correctly defined \n";
}

my $subject="Enterprise Manager Alert: Database Users passwords are expiring.";
my $errorMsg="The following database users have passwords that are expired or will expire soon:\n\n$expiryList.\n\nYou can change the passwords by clicking on this link:  $siteUrl"."console/database/security/advanced/pwdexpiry?event=listusers&target=$targetName&type=$targetType\n";

EMD_PERL_ERROR("pwdexpiry: subject=$subject");
EMD_PERL_ERROR("pwdexpiry: $errorMsg");
EMD_PERL_ERROR("pwdexpiry: toemail=$toEmail");
EMD_PERL_ERROR("pwdexpiry: fromEmail=$fromEmail");
EMD_PERL_ERROR("pwdexpiry: siteUrl=$siteUrl");
EMD_PERL_ERROR("pwdexpiry: gateway=$gateway");

my $body = scalar localtime(time) . "\n\n$errorMsg\n";

sendMail($gateway, $toEmail, $fromEmail, $subject, $body);