<?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) {
}
?>
Comments