#!/usr/bin/perl -w
use WWW::Mechanize;
use LWP::UserAgent;
use LWP::Authen::Ntlm;
use Storable;
use DBI;
use Parm_file;
use LWP::Debug qw(+);

my $URL_Parm="https://sharepoint.com";

&Parm_file::InitEnv();#parameter file for credentials#

$mech = WWW::Mechanize->new(autocheck=>1,keep_alive=>1);
$mech->agent_alias( 'Windows IE 6' );
$mech->credentials("sharepoint.com:443",'',$ENV{'UserId_Parm'}, $ENV{'Password_Parm'});
$mech->get($URL_Parm);
my $html = $mech->content;

@names = qw(file1 file2 file3);
# COUNTS EACH ELEMENT OF THE ARRAY
$n = 0;

while ($names[$n]) {
print "\n";
print "Searching for $names[$n].xls;\n";
my $startPos = index($html,">$names[$n]<img");

if ($startPos > 0) {
my $startPos1 = index($html,"<NOBR>",$startPos);
my $endPos = index($html,"</NOBR>",$startPos1);

my $filedateOn= substr($html,$startPos1+6,$endPos-$startPos1-6);

my $sth;
my $rows;

#Connecting to the database with the credentials
my $dbh= DBI->connect("DBI:ODBC:$ENV{'DSN_Parm_DB'}","$ENV{'UserId_Parm_DB'}","$ENV{'Password_Parm_DB'}");
my $sqlStatement= "SELECT FILE_DATE FROM (SELECT MAX(FILE_DATE)FILE_DATE FROM Table WHERE FILE_NAME='$names[$n]')A WHERE A.FILE_DATE < '$filedateOn' ";

$sth = $dbh->prepare($sqlStatement);
$sth->execute();

my @row = $sth->fetchrow_array;

if ($sth->rows eq 0) {

print "file $names[$n].xls exists\n";
print "so file is not going to move this time\n";

} else {

print "Lets get move the file now.\n";

my $urlFile = "https://sharepoint.com/$names[$n].xls";
$mech->get($urlFile); 
$filename="$ENV{'FilePath'}$names[$n].xls";
$mech->save_content($filename);

print "\n";
print "file $names[$n].xls was moved to $ENV{'FilePath_Parm'}\n";

my $sqlStatement= "INSERT INTO [Table] ([FILE_NAME],[FILE_DATE],[STATUS],[MESSAGE])VALUES('$names[$n]','$filedateOn','SUCCESS','File transfer has been completed') ";
$sth = $dbh->prepare($sqlStatement);
$sth->execute();

print "\n";
print "Record has been inserted in table with file name and date time\n";

}
}

$n++;

}