#!c:\perl\bin\perl.exe
#anniversaryweek.pl
#
#####################################################################################################
#
#
#########################################Header Section##############################################
#
#---------------Use and Require-----------------------------
use DBI;
use Time::Local;
use strict;
use warnings;

#----------Declare variables, create objects-------------
my $dir1 = "./data";

my $dir2 = "DIRECTORY_LOCATION";

my @excludeAnn = ();

my $anniverWeek= "$dir2/anniverTEST.htm";
#$anniverWeek= "$dir2/test.htm";

my $dbh; my $sql; my $sqlH; my $sql2;
my @monMdayArray; my @monArray; my @mdayArray; my @wdayArray;

#########################################################################################
#
#
#############################Main Logic Section###############################################
if ($ARGV[0]) {
	if ($ARGV[0] eq "/p") {
		&getDate;
		&buildQuery;
    print "Built\n";
    &dbConnect;
    print "Connected\n";
    &executeQuery;
    print "Executed\n";
    &writeOut;
    print "Written\n";
    &dbClose;
    print "Closed\n";
    &dbDisconnect;
    print "Disconnected\n";
    exit;
		} else {
		&writeHelp;
		exit;
	}
} else {
	&writeHelp;
	exit;
}

#########################################################################################
#
#
##############################Subroutine Section#########################################

#--------------------Get Current Day--------------------
sub getDate {
  
  my @argTimeArray;
  my $theLocalTime;
  
  if ($ARGV[1]) {
    @argTimeArray = split /\//, $ARGV[1];
    $theLocalTime = timelocal(0, 0, 0, $argTimeArray[1], ($argTimeArray[0]-1), $argTimeArray[2]);
  } else {
    $theLocalTime = time;
  }

        (my $sec0,my $min0,my $hour0,my $mday0,my $mon0,my $year0,my $wday0,my $yday0,my $isdst0) = localtime($theLocalTime+86400);
        (my $sec1,my $min1,my $hour1,my $mday1,my $mon1,my $year1,my $wday1,my $yday1,my $isdst1) = localtime($theLocalTime+172800);
        (my $sec2,my $min2,my $hour2,my $mday2,my $mon2,my $year2,my $wday2,my $yday2,my $isdst2) = localtime($theLocalTime+259200);
        (my $sec3,my $min3,my $hour3,my $mday3,my $mon3,my $year3,my $wday3,my $yday3,my $isdst3) = localtime($theLocalTime+345600);
        (my $sec4,my $min4,my $hour4,my $mday4,my $mon4,my $year4,my $wday4,my $yday4,my $isdst4) = localtime($theLocalTime+432000);
        (my $sec5,my $min5,my $hour5,my $mday5,my $mon5,my $year5,my $wday5,my $yday5,my $isdst5) = localtime($theLocalTime+518400);
        (my $sec6,my $min6,my $hour6,my $mday6,my $mon6,my $year6,my $wday6,my $yday6,my $isdst6) = localtime($theLocalTime+604800);

        @monArray =  ($mon0,$mon1,$mon2,$mon3,$mon4,$mon5,$mon6);
        @mdayArray = ($mday0,$mday1,$mday2,$mday3,$mday4,$mday5,$mday6);
        @wdayArray = ($wday0,$wday1,$wday2,$wday3,$wday4,$wday5,$wday6);
        
        my $i;
        
        for ($i=0; $i<(scalar(@monArray)); $i++) {

	        SWITCH: {
	                if ($monArray[$i] eq 0) {$monArray[$i]="01"; last SWITCH;}
	                if ($monArray[$i] eq 1) {$monArray[$i]="02"; last SWITCH;}
	                if ($monArray[$i] eq 2) {$monArray[$i]="03"; last SWITCH;}
	                if ($monArray[$i] eq 3) {$monArray[$i]="04"; last SWITCH;}
	                if ($monArray[$i] eq 4) {$monArray[$i]="05"; last SWITCH;}
	                if ($monArray[$i] eq 5) {$monArray[$i]="06"; last SWITCH;}
	                if ($monArray[$i] eq 6) {$monArray[$i]="07"; last SWITCH;}
	                if ($monArray[$i] eq 7) {$monArray[$i]="08"; last SWITCH;}
	                if ($monArray[$i] eq 8) {$monArray[$i]="09"; last SWITCH;}
	                if ($monArray[$i] eq 9) {$monArray[$i]="10"; last SWITCH;}
	                if ($monArray[$i] eq 10) {$monArray[$i]="11"; last SWITCH;}
	                if ($monArray[$i] eq 11) {$monArray[$i]="12"; last SWITCH;}
  	      }
        }
        
        for ($i=0; $i<(scalar(@mdayArray)); $i++) {
          if (length $mdayArray[$i] eq 1) {$mdayArray[$i] = "0".$mdayArray[$i];}
        }
        
        for ($i=0; $i<(scalar(@mdayArray)); $i++) {
          $monMdayArray[$i] = $monArray[$i]."/".$mdayArray[$i];
        }
        
        for ($i=0; $i<(scalar(@wdayArray)); $i++) {
	        SWITCH: {
	                if ($wdayArray[$i] eq 0) {$wdayArray[$i]="Sunday"; last SWITCH;}
	                if ($wdayArray[$i] eq 1) {$wdayArray[$i]="Monday"; last SWITCH;}
	                if ($wdayArray[$i] eq 2) {$wdayArray[$i]="Tuesday"; last SWITCH;}
	                if ($wdayArray[$i] eq 3) {$wdayArray[$i]="Wednesday"; last SWITCH;}
	                if ($wdayArray[$i] eq 4) {$wdayArray[$i]="Thursday"; last SWITCH;}
	                if ($wdayArray[$i] eq 5) {$wdayArray[$i]="Friday"; last SWITCH;}
	                if ($wdayArray[$i] eq 6) {$wdayArray[$i]="Saturday"; last SWITCH;}
  	      }
        }
        
}

#-----------End of Calulation------------------#


#--------------------Build Query----------------------------
sub buildQuery {
        $sql = "SELECT facility, department, name, soc, anniversary, years "
        ."FROM EMPFILE "
        ."WHERE ((anniversary LIKE '".($monArray[0]."/".$mdayArray[0])."') OR "
        ."(anniversary LIKE '".($monArray[1]."/".$mdayArray[1])."') OR "
        ."(anniversary LIKE '".($monArray[2]."/".$mdayArray[2])."') OR "
        ."(anniversary LIKE '".($monArray[3]."/".$mdayArray[3])."') OR "
        ."(anniversary LIKE '".($monArray[4]."/".$mdayArray[4])."') OR "
        ."(anniversary LIKE '".($monArray[5]."/".$mdayArray[5])."') OR "
        ."(anniversary LIKE '".($monArray[6]."/".$mdayArray[6])."')) "
        ."ORDER BY facility, years DESC";       
}

#--------------------Connect to Database--------------------
sub dbConnect {
        $dbh = DBI->connect("DBI:CSV:f_dir=$dir1;csv_sep_char=\011");
        $dbh->{'RaiseError'} =0;
        $@ = '';
}

#--------------------Execute Query--------------------------
sub executeQuery {
	
        eval{$sqlH = $dbh->prepare($sql);};
        if ($@) {print "Database Error: " . $dbh->errstr();exit;}
        print "Prepared\n";
        eval{$sqlH->execute();};
        #eval{$sqlH->execute($monArray[1], $mdayArray[1], $monArray[2], $mdayArray[2], $monArray[3], $mdayArray[3], 
        #										$monArray[4], $mdayArray[4], $monArray[5], $mdayArray[5], $monArray[6], $mdayArray[6]);};
        if ($@) {print "Database Error: " . $dbh->errstr();exit;}
}

#--------------------Case Conversion------------------------
sub caseConvert {
    my $j;
    my $returnStr;
    for ($j=0; $j<@_; $j++) {
		$_[$j] = lc ($_[$j]);
		$_[$j] = ucfirst ($_[$j]);
		if ($j ne 0) {
			$returnStr = $returnStr." ".$_[$j];
		} else {
			$returnStr = $_[$j];
		}
	}
	return $returnStr;
}


#-------------------Bulk Write------------------------------
sub writeOut {

  open (NEWOUTFILE, ">$anniverWeek")||die "Error: ".$!;

    print NEWOUTFILE "<link rel=\"stylesheet\" href=\"/css/styles.css\">\n";
    
    print NEWOUTFILE "<table width=\"100%\" cellspacing=\"2\" cellpadding=\"2\">\n"
		."<tr><td class=\"defaultFont\">\n"
		."<div align=\"center\"><a href=\"#\" onClick=\"window.close()\">Close Window</a>\n"
		."</div></font></tr></td></table>\n";

    print NEWOUTFILE "<table align=\"center\" border=\"1\" bordercolor=\"#CCCCCC\" cellspacing=\"0\" cellpadding=\"2\">\n"
               ."  <tr>\n"
               ."  <th colspan=\"4\" bgcolor=\"#006633\"><font class=\"defaultFont\" color=\"#FFFFFF\">"
               #."Anniversarys For This Week \n"
               ."Service Anniversaries For $monMdayArray[0] through $monMdayArray[6]\n"
               ."</font></th>\n"
               ."  </tr>\n";	
	
	print NEWOUTFILE "  <tr align=\"left\">\n"
               ."    <td><font class=\"defaultFont\"><b>Name</b></font></td>\n"
               ."    <td><font class=\"defaultFont\"><b>Dept</b></font></td>\n"
               ."    <td align=\"right\"><font class=\"defaultFont\"><b>Years</b></font></td>\n"
               ."    <td align=\"right\"><font class=\"defaultFont\"><b>Weekday</b></font></td>\n"
               ."  </tr>";

  my $row;
  my $facilPrev;
  #my $facilTemp;

  WRITEBDAY: while ($row = $sqlH->fetchrow_hashref) {
    my $facilTemp = $row->{facility};
    my $deptTemp  = $row->{department};
    my $nameTemp  = $row->{name};
    my $socTemp   = $row->{soc};
    my $anniversary = $row->{anniversary};
    my $yearsTemp = $row->{years};
    $yearsTemp = $yearsTemp+1;

   
    foreach my $excludeAnnTemp (@excludeAnn) {
      if ($excludeAnnTemp eq $socTemp) {next WRITEBDAY;}
    }
    
    my @facilArrayTemp = split / /, $facilTemp;
    my @deptArrayTemp = split / /, $deptTemp;
    my @nameArrayTemp = split / /, $nameTemp;

    $facilTemp = &caseConvert(@facilArrayTemp);
    $deptTemp = &caseConvert(@deptArrayTemp);
    $nameTemp = &caseConvert(@nameArrayTemp);
    
    my $i;
    my $j;
    
    for ($i=0; $i<(scalar(@monMdayArray)); $i++) {
      if ($anniversary eq $monMdayArray[$i]) {$j=$i;}
    }

    if ($facilPrev ne $facilTemp) {
    	if ($facilPrev) {print NEWOUTFILE "<tr><td colspan=\"4\">&nbsp;</td></tr>\n";}
    	$facilPrev = $facilTemp;
    	print NEWOUTFILE "  <tr>\n"
    	                ."    <td nowrap colspan=\"4\"><font class=\"defaultFont\"><u>$facilTemp</u></font></td>\n"
    	                ."  </tr>\n";
    }

    print NEWOUTFILE "  <tr>\n"
                    ."    <td nowrap><font class=\"defaultFont\">$nameTemp</font></td>\n"
                    ."    <td nowrap><font class=\"defaultFont\">$deptTemp</font></td>\n"
                    ."    <td align=\"right\"><font class=\"defaultFont\">$yearsTemp</font></td>\n"
                    ."    <td align=\"right\"><font class=\"defaultFont\">$wdayArray[$j]</font></td>\n"
                    ."  </tr>\n";
    }
 		
	 #print NEWOUTFILE "</td></tr>\n";		

 	 print NEWOUTFILE "</table>\n";
   close NEWOUTFILE;
}


#-------------------Close Database Connection---------------
sub dbClose {
        $sqlH->finish();
}

#-------------------Close Database Connection---------------
sub dbDisconnect {
        $dbh->disconnect();
}