<?php

// Change weigting to array, set day of year past Jan 1, 2015 and then element for each dept
// day[255][Fab] = #of punches / day[255][Canvas] = #of punches .....
// track day of first punch [255] to day of last punch [283]
// Now analyze each day to determine what Dept Boat is in.  Compress list by dropping days off
// Push onto days array array_push($days) = $day with > 1 punch in any department.
// Now working elements in the days array, looking at day-1 and day+1 determine which dept 
// boat is in.  Fab 8 10 4 0  Canvas 0 1 2 5  Days 1-3 Fab, Day 4 Paint
// ignore 1-2 puch if day before is 0 and day after is 0
// allow overlap if day before has count and day after does not and visa versa for next department
// dept1  15 12 3 0 0   dept2 0 0 2 4 8  = overlap on day 3 3/2

// This report is designed to run on a Monday morning so it will pull all boats completed in the
// last 2 weeks. 

// the cronjob tests if this is an even or odd week of the year and only runs the report on those weeks
// every once is a while there is a 53 week year which will switch if we do the report on odd or even weeks
//
// Years 2015-2018 even weeks -eq 0
// Years 2019-2024 odd weeks  -eq 1
// Years 2025-???? even weeks -eq 0
//
// 15 07 * * 1 test $(($(date +\%W)\%2)) -eq 0 && /usr/bin/wget -q -O temp.txt http://127.0.0.1/timeforce/lag/bob.php


$holidays=array("2015-01-01","2015-05-25","2015-07-03","2015-09-07","2015-11-26","2015-11-27","2015-12-25","2016-01-01","2016-05-30","2016-06-04","2016-09-05","2016-11-24","2016-11-25","2016-11-26");
require_once('workdays.php');
require_once('vendor/autoload.php');

// Connection data (server_address, database, name, poassword)
$hostdb = '10.10.200.26';
$namedb = 'qqest';
$userdb = 'sa';
$passdb = '*****';
$lag = array();

$startDate = date_format(date_sub(date_create(date("Y-m-d")),date_interval_create_from_date_string("180 days")),"Y-m-d"); 
$endDate = date("Y-m-d");
$cutoffDate = date_format(date_sub(date_create(date("Y-m-d")),date_interval_create_from_date_string("15 days")),"Y-m-d");


try {
  // Connect and create the PDO object
  $conn = new PDO("dblib:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  $sql =  "SELECT job.jobname, twp.workingpunch_ts, twp.department_id, twp.task_id, 
             CASE task_id 
			 		WHEN 44 THEN 'Fab' 
					WHEN 50 THEN 'Canvas/Floorboards' 
					WHEN 53 THEN 'Paint' 
					WHEN 60 THEN 'Outfitting' 
					WHEN 61 THEN 'Canvas/Floorboards' 
					WHEN 74 THEN 'Fab' 
					WHEN 73 THEN 'Canvas/Floorboards' 
					WHEN 75 THEN 'Paint' 
					WHEN 76 THEN 'Outfitting' 
					WHEN 78 THEN 'Canvas/Floorboards' 
                  ELSE '' END AS dept
             FROM job
       INNER JOIN timeWorkingPunch twp ON job.job_id = twp.job_id 
           WHERE (   JobName LIKE '% 212' OR  JobName LIKE '% 213' 
                  OR JobName LIKE '% 313' OR  JobName LIKE '% 314' 
                  OR JobName LIKE '% 414' OR  JobName LIKE '% 415' 
                  OR JobName LIKE '% 515' OR  JobName LIKE '% 516' 
                  OR JobName LIKE '% 616' OR  JobName LIKE '% 617' ) 
             AND start_ts > '$startDate 23:00:00'
        ORDER BY twp.job_id, twp.workingpunch_ts";
		// Order by boat# then punch order

// Define and perform the SQL SELECT query
//  $sql = "SELECT * FROM job WHERE job_id IN (489, 498, 5759)";
  $result = $conn->query($sql);

  // If the SQL query is succesfully performed ($result not false)
  if($result !== false) {
    $cols = $result->columnCount();           // Number of returned columns

    $jobname = "";  // set current boat to none

    // Parse the result set
    foreach($result as $row) {
        if ($jobname != $row['jobname']) {		// Boat # has changed start entries for next boat
			$jobname = $row['jobname'];
   	        $taskcount = 0;						// for debouncing punch (dont change dept for 1 punch)
	        $dept = $row['dept'];				// Set starting dept, should be fab
			$lastdate = date('Y-m-d',strtotime($row['workingpunch_ts']));
			$firstdate = date('Y-m-d',strtotime($row['workingpunch_ts']));
			$f2c = -1;							// Fab to Canvas
			$c2p = -1;							// Canvas to Paint
			$p2o = -1;							// Paint to Outfitting
		}
		switch ($dept) {						// Process for current department
			case "Fab":
				if ($row['dept'] == 'Canvas/Floorboards') {
					if ($taskcount == 1) {		// 2nd Punch into canvas
						$dept = $row['dept'];	// Switch to canvas - count Fab 2 Canvas lag
						$f2c = ceil(getWorkingDays($lastdate,$firstdate,$holidays));
						$ldf = $lastdate;		// Set last day in fab
						$fdc = $firstdate;		// Set first day in canvas
						$taskcount = 0;			// Reset debounce
					} else if ($taskcount == 0) {	// 1st Punch into canvas
						$firstdate = date('Y-m-d',strtotime($row['workingpunch_ts']));
						$taskcount++;			// Record possible first date
					} else {					// Next puch was still fab
						$taskcount = 0;			// reset debounce counter, still in fab
					}
				} else {						// still in fab - keep rolling end date up
					$lastdate = date('Y-m-d',strtotime($row['workingpunch_ts']));
				}
				break;
			case "Canvas/Floorboards":
				if ($row['dept'] == 'Paint') {
					if ($taskcount == 1) {
						$dept = $row['dept'];
						$c2p = ceil(getWorkingDays($lastdate,$firstdate,$holidays));
						$ldc = $lastdate;
						$fdp = $firstdate;
						$taskcount = 0;
					} else if ($taskcount == 0) {
						$firstdate = date('Y-m-d',strtotime($row['workingpunch_ts']));
						$taskcount++;
					} else {
						$taskcount = 0;
					}
				} else {
					$lastdate = date('Y-m-d',strtotime($row['workingpunch_ts']));
				}
				break;
			case "Paint":
				if ($row['dept'] == 'Outfitting') {	// When paint is done output lagtime for current boat
					if ($taskcount == 1) {
						$dept = $row['dept'];
						$p2o = ceil(getWorkingDays($lastdate,$firstdate,$holidays));
						$ldp = $lastdate;
						$fdo = $firstdate;
						if($f2c > -1 && $c2p > -1 && $p2o > -1 ) {
							if($fdo > $cutoffDate) {
//								echo substr($jobname,0,5).",$ldf,$fdc,$f2c,$ldc,$fdp,$c2p,$ldp,$fdo,$p2o\n";	// << OUTPUT RESULTS
								$lag[] = [ 
									'boat' => substr($jobname,0,5),
									'ldf' => $ldf,
									'fdc' => $fdc,
									'f2c' => $f2c,
									'ldc' => $ldc,
									'fdp' => $fdp,
									'c2p' => $c2p,
									'ldp' => $ldp,
									'fdo' => $fdo,
									'p2o' => $p2o
								];
							}
						}
						$taskcount = 0;
					} else if ($taskcount == 0) {
						$firstdate = date('Y-m-d',strtotime($row['workingpunch_ts']));
						$taskcount++;
					} else {
						$taskcount =0;
					}
				} else {
					$lastdate = date('Y-m-d',strtotime($row['workingpunch_ts']));
				}
				break;
			case "Outfitting":					// skip outfitting
				break;
		}
    }
  }

  $conn = null;        // Disconnect

// bubble sort in boat # order
if ( count($lag) > 1 ) {
    $sorted = false;
    while (false === $sorted) {
        $sorted = true;
        for ($i = 0; $i < count($lag)-1; ++$i) {
            $current = $lag[$i];
            $next = $lag[$i+1];
            if ($next['boat'] < $current['boat']) {
                $lag[$i] = $next;
                $lag[$i+1] = $current;
                $sorted = false;
            }
        }
    }
}

// bubble sort in first day outfitting
if ( count($lag) > 1 ) {
    $sorted = false;
    while (false === $sorted) {
        $sorted = true;
        for ($i = 0; $i < count($lag)-1; ++$i) {
            $current = $lag[$i];
            $next = $lag[$i+1];
            if ($next['fdo'] < $current['fdo']) {
                $lag[$i] = $next;
                $lag[$i+1] = $current;
                $sorted = false;
            }
        }
    }
}


	$spreadsheet = getcwd()."/LagTime ".$endDate.".xlsx";
	// Delete File if exists
	$files = glob( getcwd()."/LagTime*"); // get all file names
	foreach($files as $file){ // iterate files
	if(is_file($file))
		unlink($file); // delete file
	}


	// Setup Excel

	$objPHPExcel = new PHPExcel();

	$objPHPExcel->getProperties()->setCreator("Fred W")
		->setLastModifiedBy("Fred W")
		->setTitle("Lag Time Report for ".$endDate)
		->setSubject("Lag Time Report for " .$endDate)
		->setDescription("Lagtime Report in Office 2007 XLSX, generated using PHP classes.")
		->setKeywords("lagtime")
		->setCategory("Lagtime");

	$objPHPExcel->setActiveSheetIndex(0);
	// Set Paper Type
	$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
	$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER);
	$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);
	// Set Header and Footer
	$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&CNRB '.$endDate);
	$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&CPage &P of &N');

	$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
	$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);

	$objPHPExcel->getActiveSheet()->getPageMargins()->setTop(.76);
	$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.5);
	$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.5);
	$objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(.75);

	// Set Table Header
	$objPHPExcel->getActiveSheet()
		->setCellValue('A1', "Boat")
		->setCellValue('B1', "Lastday\nFab")
		->setCellValue('C1', "Firstday\nCanvas")
		->setCellValue('D1', "Fab to\nCanvas\nLag")
		->setCellValue('E1', "Lastday\nCanvas")
		->setCellValue('F1', "Firstday\nPaint")
		->setCellValue('G1', "Canvas to\nPaint Lag")
		->setCellValue('H1', "Lastday\nPaint")
		->setCellValue('I1', "Firstday\nOutfitting")
		->setCellValue('J1', "Paint to\nOutfitting\nLag");

	$i = 2;
	foreach($lag as $row) {
		$objPHPExcel->getActiveSheet()
			->setCellValue("A$i", $row['boat']) 
			->setCellValue("B$i", $row['ldf']) 
			->setCellValue("C$i", $row['fdc']) 
			->setCellValue("D$i", $row['f2c']) 
			->setCellValue("E$i", $row['ldc']) 
			->setCellValue("F$i", $row['fdc']) 
			->setCellValue("G$i", $row['c2p']) 
			->setCellValue("H$i", $row['ldp']) 
			->setCellValue("I$i", $row['fdo']) 
			->setCellValue("J$i", $row['p2o']);
		$i++;
	}
	$i--;

	// Thats all she wrote
	$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(35.05);
	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10.2602040816327);
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12.6887755102041);
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12.6887755102041);
	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10.2602040816327);
	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12.6887755102041);
	$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12.6887755102041);
	$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10.2602040816327);
	$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12.6887755102041);
	$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(12.6887755102041);
	$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10.2602040816327);
	$objPHPExcel->getActiveSheet()->getStyle("1:1")->getFont()->setBold(true);

	$objPHPExcel->getActiveSheet()->getStyle("A")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("D")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("G")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("J")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("A1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("B1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("C1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("D1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("E1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("F1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("G1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("H1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("I1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("J1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


	$objPHPExcel->getActiveSheet()->getStyle("1:1")->getFont()->setBold(true);
	$objPHPExcel->getActiveSheet()->getStyle("A1:J1")->getFont()->setBold(true);

	$objConditional1 = new PHPExcel_Style_Conditional();
	$objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_EXPRESSION)
		->addCondition('ISEVEN(ROW())');
	$objConditional1->getStyle()->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getEndColor()->setARGB('FFCCCCCC');   

	$conditionalStyles = $objPHPExcel->getActiveSheet()->getStyle('B2')->getConditionalStyles();
	array_push($conditionalStyles, $objConditional1);
	$objPHPExcel->getActiveSheet()->getStyle("A1:J$i")->setConditionalStyles($conditionalStyles);


	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10.2602040816327);
	$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
	$objWriter->save($spreadsheet);



	// Lets mail out the results!!!!!
	$mail = new PHPMailer;

	//$mail->SMTPDebug = 3;                                               // Enable verbose debug output

	$mail->isSMTP();                                                      // Set mailer to use SMTP
	$mail->Host = '***************-com.mail.protection.outlook.com';      // Specify main and backup SMTP servers
	$mail->Port = 25;                                                     // TCP port to connect to

	$mail->setFrom('fredw@*********.com', 'Fred W');
	$mail->addAddress('kristis@*********.com', 'Kristi S');                    // Add a recipient
	$mail->addCC('jayc@*********.com', 'Jay C');
	$mail->addBCC('fredw@*********.com', 'Fred W');

	$mail->addAttachment($spreadsheet);                                   // Add attachments
	$mail->isHTML(true);                                                  // Set email format to HTML

	$mail->Subject = 'Lag Time Report '.$endDate;
	$mail->Body    = <<<EOD
Hi Kristi,
<p>Here is the Lag Time Report for $endDate</p>
<font color="#888888"><br>
<br>
-- <br>
Company Name
Fred W- Computer Support Specialist<br>
Phone: <a href="tel:555-555-5555" value="+15555555555" target="_blank">555-555-5555</a> 
Fax: <a href="tel:555-555-5555" value="+15555555555" target="_blank">555-555-55555</a><br>
Email: <a href="mailto:fredw@**********.com" target="_blank"><span class="il">fredw@**********.com</span></a><br>
<br>
</font>
EOD;
	$mail->AltBody = <<<EOD
Hi Kristi,

Here is the Lag Time Report for $endDate

--
Company Name 
Fred W - Computer Support Specialist
Phone: 555-555-5555 Fax: 555-555-5555
Email: fredw@********.com
EOD;

	if(!$mail->send()) {
	    echo "Message could not be sent.\n";
	    echo 'Mailer Error: ' . $mail->ErrorInfo ."\n";
	} else {
	    echo "Message has been sent\n";
	}

}
catch(PDOException $e) {

}

?>