use Win32::ODBC;
use Time::Local;
use Getopt::Std;




my $cString = sprintf("Driver=SQL Server;Server=szrh721;Database=pagesSys_5701SR4;Trusted Connection=yes");   
my $filename="C:\\Perl\\scriptt\\DB.txt";
#my $filename="P:\\DB.txt";
unlink($filename);
$dbQuery = "BEGIN

SET NOCOUNT ON

DECLARE
\@V_BATCHJOBID INT ,
\@V_jobaccountid INT ,
\@V_jobaccountstatusid INT,
\@V_PCName VARCHAR(50),
\@V_JobName VARCHAR(50),
\@V_Description VARCHAR(50),
\@V_ErrorMessage VARCHAR(3000),
\@V_StatusTimeStamp DATETIME ,
\@V_Flag_Stuck VARCHAR(2),
\@V_Limit varchar(2)
-- One hour
SET \@V_Limit =1


DECLARE CUR_PEND CURSOR FOR 
SELECT  JobType.BATCHJOBID,
	PCStatus.jobaccountid,
	CurrStatus.jobaccountstatusid,
	JobType.PCName,
        JobType.Name,
        PCStatus.Description,
        PCStatus.ErrorMessage,
        PCStatus.StatusTimeStamp
  FROM (SELECT BJ.PCName, BJ.BATCHJOBID AS BATCHJOBID ,BJ.Name as JobName, BJT.Name, BJT.TimeLimit
          FROM BatchJob BJ (nolock), BatchJobType BJT (nolock)
         WHERE BJ.BatchJobTypeID = BJT.BatchJobTypeID
           AND (BJ.BatchJobStatusID = 1 OR BJ.BatchJobStatusID = 3)
           AND BJ.BatchJobID =
               (SELECT Min(BJ2.BatchJobID)
                  FROM BatchJob BJ2 (nolock)
                 WHERE BJ2.PCName = BJ.PCName
                   and (BJ.BatchJobStatusID = 1 OR BJ.BatchJobStatusID = 3))) JobType
  LEFT OUTER JOIN (SELECT Distinct Jas.PCName,JAS.jobaccountstatusid,JAS.jobaccountid,
                                   Acs.Description,
                                   Jas.StatusTimeStamp,
                                   Jas.ErrorMessage
                     FROM AccountStatus Acs (nolock), JobAccountStatus Jas (nolock)
                    WHERE Jas.StatusID = Acs.StatusID
                      AND Jas.StatusTimeStamp =
                          (SELECT Max(Jas2.StatusTimeStamp)
                             FROM JobAccountStatus Jas2 (nolock)
                            WHERE Jas.PCName = Jas2.PCName)) PCStatus ON JobType.PCName =
                                                                         PCStatus.PCName
  LEFT OUTER JOIN (SELECT BJ.PCName, Count(*) as c
                     FROM BatchJob BJ (nolock)
                    WHERE (BJ.BatchJobStatusID = 1 OR
                          BJ.BatchJobStatusID = 3)
                    Group by PCName) Assigned ON JobType.PCName =
                                                 Assigned.PCName
 JOIN (select jobaccountstatusid from JobAccountStatus(nolock)) CurrStatus 
on CurrStatus.jobaccountstatusid=PCStatus.jobaccountstatusid
OPEN CUR_PEND

FETCH NEXT FROM CUR_PEND INTO 
\@V_BATCHJOBID  ,
\@V_jobaccountid ,
\@V_jobaccountstatusid,
\@V_PCName ,
\@V_JobName ,
\@V_Description ,
\@V_ErrorMessage ,
\@V_StatusTimeStamp 

	WHILE \@\@FETCH_STATUS = 0
	BEGIN
     --- Find current pending reports
SET \@V_Flag_Stuck= (SELECT COUNT(b.BatchJobID)
 FROM   BatchJob b (nolock)
 WHERE  b.NextDate <= getdate()
 AND b.Active = 1
 AND b.BatchJobStatusID <= 3 
 AND B.BATCHJOBID <> \@V_BATCHJOBID)



-- FORMAT FOR OUTPUT
/*
       select @v_batchjobid as Batchjobid,
	      @V_StatusTimeStamp as VisibleTime,
	      BJ2.STARTDATE as ActualStartTime,
	      @V_PCName as PCNAME,
	      datediff(mi,BJ2.STARTDATE,getdate())as RunTime from  BatchJob BJ2 
	      WHERE BJ2.BATCHJOBID= @V_BATCHJOBID 
              and  datediff(mi,BJ2.STARTDATE,getdate()) > 2 */

 select  'JOB ID: '+cast(\@v_batchjobid as varchar(12))+
	 ' STARTED AT: '+  CAST(  \@V_StatusTimeStamp AS VARCHAR(20))+
	 ' ON '+    \@V_PCName +
	 ' HAS BEEN RUNNING FOR MORE THAN '+  CAST(  datediff(hh,BJ2.STARTDATE,getdate()) AS VARCHAR(8))+
         ' Hour(s). CURRENTLY '+
          CAST (\@V_Flag_Stuck AS VARCHAR(10)) +
         ' WAITING ' as STATUS from  BatchJob BJ2 (nolock)
	      WHERE BJ2.BATCHJOBID= \@V_BATCHJOBID 
              and  datediff(mi,BJ2.STARTDATE,getdate()) >= \@V_LIMIT



	FETCH NEXT FROM CUR_PEND INTO 
	\@V_BATCHJOBID  ,
	\@V_jobaccountid ,
	\@V_jobaccountstatusid,
	\@V_PCName ,
	\@V_JobName ,
	\@V_Description ,
	\@V_ErrorMessage ,
	\@V_StatusTimeStamp

        END

CLOSE CUR_PEND
DEALLOCATE CUR_PEND

END";


############################################################
# connect
my $Conn;
$Conn = new Win32::ODBC($cString);
die "Unable to connect to database" . Win32::ODBC::Error(  ) . "\n"
  unless (defined $Conn);

#printf("Using %s database on %s \n\n", $configData->{"db"}, $configData->{"server"} );

#printf("%s\n\n", $sql);

if( $Conn->Sql($dbQuery ) )  
  {
  print "Query failed. \n";
  print "Error: " . $Conn->Error() . "\n";
  print $dbQuery;
  $Conn->Close();
  exit(1);
  }    
  
  my $temp="";

  open(Spool, ">>$filename") or die "Error opening $filename\n";
 
while( $Conn->FetchRow() ) 
{
     my $Temp =$Conn->Data("STATUS");
     #print "Value is $Temp";
     print Spool "This line goes to the file.\n"; #Testing 
     printf(Spool "%s",$Conn->Data("STATUS" ));
 }
     
    $Conn->Close();
    close Spool;
   
   
   ###Read Spool file if it has something to say
   
   open(Spool, "> $filename") or die "Error opening $filename\n";
    while (<spool>) {}
    my $lineCtr =  $.;
   print $lineCtr;
   
   if (@linctr >0) {
   #Send Email using VB code
   
   system("start C:\\My-Scripts\\Email_Batch.vbs"); 
   
 }
    
   #unlink($filename);
   exit(0);