API tools faq
paste
fwarren

Lag Time Report

fwarren
Jan 5th, 2016
406
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 15.77 KB | None | 0 0
raw download clone embed print report
  1. <?php
  2.  
  3. // Change weigting to array, set day of year past Jan 1, 2015 and then element for each dept
  4. // day[255][Fab] = #of punches / day[255][Canvas] = #of punches .....
  5. // track day of first punch [255] to day of last punch [283]
  6. // Now analyze each day to determine what Dept Boat is in.  Compress list by dropping days off
  7. // Push onto days array array_push($days) = $day with > 1 punch in any department.
  8. // Now working elements in the days array, looking at day-1 and day+1 determine which dept
  9. // boat is in.  Fab 8 10 4 0  Canvas 0 1 2 5  Days 1-3 Fab, Day 4 Paint
  10. // ignore 1-2 puch if day before is 0 and day after is 0
  11. // allow overlap if day before has count and day after does not and visa versa for next department
  12. // dept1  15 12 3 0 0   dept2 0 0 2 4 8  = overlap on day 3 3/2
  13.  
  14. // This report is designed to run on a Monday morning so it will pull all boats completed in the
  15. // last 2 weeks.
  16.  
  17. // the cronjob tests if this is an even or odd week of the year and only runs the report on those weeks
  18. // every once is a while there is a 53 week year which will switch if we do the report on odd or even weeks
  19. //
  20. // Years 2015-2018 even weeks -eq 0
  21. // Years 2019-2024 odd weeks  -eq 1
  22. // Years 2025-???? even weeks -eq 0
  23. //
  24. // 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
  25.  
  26.  
  27. $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");
  28. require_once('workdays.php');
  29. require_once('vendor/autoload.php');
  30.  
  31. // Connection data (server_address, database, name, poassword)
  32. $hostdb = '10.10.200.26';
  33. $namedb = 'qqest';
  34. $userdb = 'sa';
  35. $passdb = '*****';
  36. $lag = array();
  37.  
  38. $startDate = date_format(date_sub(date_create(date("Y-m-d")),date_interval_create_from_date_string("180 days")),"Y-m-d");
  39. $endDate = date("Y-m-d");
  40. $cutoffDate = date_format(date_sub(date_create(date("Y-m-d")),date_interval_create_from_date_string("15 days")),"Y-m-d");
  41.  
  42.  
  43. try {
  44.   // Connect and create the PDO object
  45.   $conn = new PDO("dblib:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  46.   $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8
  47.  
  48.   $sql =  "SELECT job.jobname, twp.workingpunch_ts, twp.department_id, twp.task_id,
  49.             CASE task_id
  50.                     WHEN 44 THEN 'Fab'
  51.                     WHEN 50 THEN 'Canvas/Floorboards'
  52.                     WHEN 53 THEN 'Paint'
  53.                     WHEN 60 THEN 'Outfitting'
  54.                     WHEN 61 THEN 'Canvas/Floorboards'
  55.                     WHEN 74 THEN 'Fab'
  56.                     WHEN 73 THEN 'Canvas/Floorboards'
  57.                     WHEN 75 THEN 'Paint'
  58.                     WHEN 76 THEN 'Outfitting'
  59.                     WHEN 78 THEN 'Canvas/Floorboards'
  60.                  ELSE '' END AS dept
  61.             FROM job
  62.       INNER JOIN timeWorkingPunch twp ON job.job_id = twp.job_id
  63.           WHERE (   JobName LIKE '% 212' OR  JobName LIKE '% 213'
  64.                  OR JobName LIKE '% 313' OR  JobName LIKE '% 314'
  65.                  OR JobName LIKE '% 414' OR  JobName LIKE '% 415'
  66.                  OR JobName LIKE '% 515' OR  JobName LIKE '% 516'
  67.                  OR JobName LIKE '% 616' OR  JobName LIKE '% 617' )
  68.             AND start_ts > '$startDate 23:00:00'
  69.        ORDER BY twp.job_id, twp.workingpunch_ts";
  70.         // Order by boat# then punch order
  71.  
  72. // Define and perform the SQL SELECT query
  73. //  $sql = "SELECT * FROM job WHERE job_id IN (489, 498, 5759)";
  74.   $result = $conn->query($sql);
  75.  
  76.   // If the SQL query is succesfully performed ($result not false)
  77.   if($result !== false) {
  78.     $cols = $result->columnCount();           // Number of returned columns
  79.  
  80.     $jobname = "";  // set current boat to none
  81.  
  82.     // Parse the result set
  83.     foreach($result as $row) {
  84.         if ($jobname != $row['jobname']) {      // Boat # has changed start entries for next boat
  85.             $jobname = $row['jobname'];
  86.             $taskcount = 0;                     // for debouncing punch (dont change dept for 1 punch)
  87.             $dept = $row['dept'];               // Set starting dept, should be fab
  88.             $lastdate = date('Y-m-d',strtotime($row['workingpunch_ts']));
  89.             $firstdate = date('Y-m-d',strtotime($row['workingpunch_ts']));
  90.             $f2c = -1;                          // Fab to Canvas
  91.             $c2p = -1;                          // Canvas to Paint
  92.             $p2o = -1;                          // Paint to Outfitting
  93.         }
  94.         switch ($dept) {                        // Process for current department
  95.             case "Fab":
  96.                 if ($row['dept'] == 'Canvas/Floorboards') {
  97.                     if ($taskcount == 1) {      // 2nd Punch into canvas
  98.                         $dept = $row['dept'];   // Switch to canvas - count Fab 2 Canvas lag
  99.                         $f2c = ceil(getWorkingDays($lastdate,$firstdate,$holidays));
  100.                         $ldf = $lastdate;       // Set last day in fab
  101.                         $fdc = $firstdate;      // Set first day in canvas
  102.                         $taskcount = 0;         // Reset debounce
  103.                     } else if ($taskcount == 0) {   // 1st Punch into canvas
  104.                         $firstdate = date('Y-m-d',strtotime($row['workingpunch_ts']));
  105.                         $taskcount++;           // Record possible first date
  106.                     } else {                    // Next puch was still fab
  107.                         $taskcount = 0;         // reset debounce counter, still in fab
  108.                     }
  109.                 } else {                        // still in fab - keep rolling end date up
  110.                     $lastdate = date('Y-m-d',strtotime($row['workingpunch_ts']));
  111.                 }
  112.                 break;
  113.             case "Canvas/Floorboards":
  114.                 if ($row['dept'] == 'Paint') {
  115.                     if ($taskcount == 1) {
  116.                         $dept = $row['dept'];
  117.                         $c2p = ceil(getWorkingDays($lastdate,$firstdate,$holidays));
  118.                         $ldc = $lastdate;
  119.                         $fdp = $firstdate;
  120.                         $taskcount = 0;
  121.                     } else if ($taskcount == 0) {
  122.                         $firstdate = date('Y-m-d',strtotime($row['workingpunch_ts']));
  123.                         $taskcount++;
  124.                     } else {
  125.                         $taskcount = 0;
  126.                     }
  127.                 } else {
  128.                     $lastdate = date('Y-m-d',strtotime($row['workingpunch_ts']));
  129.                 }
  130.                 break;
  131.             case "Paint":
  132.                 if ($row['dept'] == 'Outfitting') { // When paint is done output lagtime for current boat
  133.                     if ($taskcount == 1) {
  134.                         $dept = $row['dept'];
  135.                         $p2o = ceil(getWorkingDays($lastdate,$firstdate,$holidays));
  136.                         $ldp = $lastdate;
  137.                         $fdo = $firstdate;
  138.                         if($f2c > -1 && $c2p > -1 && $p2o > -1 ) {
  139.                             if($fdo > $cutoffDate) {
  140. //                              echo substr($jobname,0,5).",$ldf,$fdc,$f2c,$ldc,$fdp,$c2p,$ldp,$fdo,$p2o\n";    // << OUTPUT RESULTS
  141.                                 $lag[] = [
  142.                                     'boat' => substr($jobname,0,5),
  143.                                     'ldf' => $ldf,
  144.                                     'fdc' => $fdc,
  145.                                     'f2c' => $f2c,
  146.                                     'ldc' => $ldc,
  147.                                     'fdp' => $fdp,
  148.                                     'c2p' => $c2p,
  149.                                     'ldp' => $ldp,
  150.                                     'fdo' => $fdo,
  151.                                     'p2o' => $p2o
  152.                                 ];
  153.                             }
  154.                         }
  155.                         $taskcount = 0;
  156.                     } else if ($taskcount == 0) {
  157.                         $firstdate = date('Y-m-d',strtotime($row['workingpunch_ts']));
  158.                         $taskcount++;
  159.                     } else {
  160.                         $taskcount =0;
  161.                     }
  162.                 } else {
  163.                     $lastdate = date('Y-m-d',strtotime($row['workingpunch_ts']));
  164.                 }
  165.                 break;
  166.             case "Outfitting":                  // skip outfitting
  167.                 break;
  168.         }
  169.     }
  170.   }
  171.  
  172.   $conn = null;        // Disconnect
  173.  
  174. // bubble sort in boat # order
  175. if ( count($lag) > 1 ) {
  176.     $sorted = false;
  177.     while (false === $sorted) {
  178.         $sorted = true;
  179.         for ($i = 0; $i < count($lag)-1; ++$i) {
  180.             $current = $lag[$i];
  181.             $next = $lag[$i+1];
  182.             if ($next['boat'] < $current['boat']) {
  183.                 $lag[$i] = $next;
  184.                 $lag[$i+1] = $current;
  185.                 $sorted = false;
  186.             }
  187.         }
  188.     }
  189. }
  190.  
  191. // bubble sort in first day outfitting
  192. if ( count($lag) > 1 ) {
  193.     $sorted = false;
  194.     while (false === $sorted) {
  195.         $sorted = true;
  196.         for ($i = 0; $i < count($lag)-1; ++$i) {
  197.             $current = $lag[$i];
  198.             $next = $lag[$i+1];
  199.             if ($next['fdo'] < $current['fdo']) {
  200.                 $lag[$i] = $next;
  201.                 $lag[$i+1] = $current;
  202.                 $sorted = false;
  203.             }
  204.         }
  205.     }
  206. }
  207.  
  208.  
  209.     $spreadsheet = getcwd()."/LagTime ".$endDate.".xlsx";
  210.     // Delete File if exists
  211.     $files = glob( getcwd()."/LagTime*"); // get all file names
  212.     foreach($files as $file){ // iterate files
  213.     if(is_file($file))
  214.         unlink($file); // delete file
  215.     }
  216.  
  217.  
  218.     // Setup Excel
  219.  
  220.     $objPHPExcel = new PHPExcel();
  221.  
  222.     $objPHPExcel->getProperties()->setCreator("Fred W")
  223.         ->setLastModifiedBy("Fred W")
  224.         ->setTitle("Lag Time Report for ".$endDate)
  225.         ->setSubject("Lag Time Report for " .$endDate)
  226.         ->setDescription("Lagtime Report in Office 2007 XLSX, generated using PHP classes.")
  227.         ->setKeywords("lagtime")
  228.         ->setCategory("Lagtime");
  229.  
  230.     $objPHPExcel->setActiveSheetIndex(0);
  231.     // Set Paper Type
  232.     $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
  233.     $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER);
  234.     $objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);
  235.     // Set Header and Footer
  236.     $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&CNRB '.$endDate);
  237.     $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&CPage &P of &N');
  238.  
  239.     $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
  240.     $objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
  241.  
  242.     $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(.76);
  243.     $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.5);
  244.     $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.5);
  245.     $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(.75);
  246.  
  247.     // Set Table Header
  248.     $objPHPExcel->getActiveSheet()
  249.         ->setCellValue('A1', "Boat")
  250.         ->setCellValue('B1', "Lastday\nFab")
  251.         ->setCellValue('C1', "Firstday\nCanvas")
  252.         ->setCellValue('D1', "Fab to\nCanvas\nLag")
  253.         ->setCellValue('E1', "Lastday\nCanvas")
  254.         ->setCellValue('F1', "Firstday\nPaint")
  255.         ->setCellValue('G1', "Canvas to\nPaint Lag")
  256.         ->setCellValue('H1', "Lastday\nPaint")
  257.         ->setCellValue('I1', "Firstday\nOutfitting")
  258.         ->setCellValue('J1', "Paint to\nOutfitting\nLag");
  259.  
  260.     $i = 2;
  261.     foreach($lag as $row) {
  262.         $objPHPExcel->getActiveSheet()
  263.             ->setCellValue("A$i", $row['boat'])
  264.             ->setCellValue("B$i", $row['ldf'])
  265.             ->setCellValue("C$i", $row['fdc'])
  266.             ->setCellValue("D$i", $row['f2c'])
  267.             ->setCellValue("E$i", $row['ldc'])
  268.             ->setCellValue("F$i", $row['fdc'])
  269.             ->setCellValue("G$i", $row['c2p'])
  270.             ->setCellValue("H$i", $row['ldp'])
  271.             ->setCellValue("I$i", $row['fdo'])
  272.             ->setCellValue("J$i", $row['p2o']);
  273.         $i++;
  274.     }
  275.     $i--;
  276.  
  277.     // Thats all she wrote
  278.     $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(35.05);
  279.     $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10.2602040816327);
  280.     $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12.6887755102041);
  281.     $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12.6887755102041);
  282.     $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10.2602040816327);
  283.     $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12.6887755102041);
  284.     $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12.6887755102041);
  285.     $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10.2602040816327);
  286.     $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12.6887755102041);
  287.     $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(12.6887755102041);
  288.     $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10.2602040816327);
  289.     $objPHPExcel->getActiveSheet()->getStyle("1:1")->getFont()->setBold(true);
  290.  
  291.     $objPHPExcel->getActiveSheet()->getStyle("A")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  292.     $objPHPExcel->getActiveSheet()->getStyle("D")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  293.     $objPHPExcel->getActiveSheet()->getStyle("G")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  294.     $objPHPExcel->getActiveSheet()->getStyle("J")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  295.     $objPHPExcel->getActiveSheet()->getStyle("A1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  296.     $objPHPExcel->getActiveSheet()->getStyle("B1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  297.     $objPHPExcel->getActiveSheet()->getStyle("C1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  298.     $objPHPExcel->getActiveSheet()->getStyle("D1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  299.     $objPHPExcel->getActiveSheet()->getStyle("E1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  300.     $objPHPExcel->getActiveSheet()->getStyle("F1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  301.     $objPHPExcel->getActiveSheet()->getStyle("G1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  302.     $objPHPExcel->getActiveSheet()->getStyle("H1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  303.     $objPHPExcel->getActiveSheet()->getStyle("I1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  304.     $objPHPExcel->getActiveSheet()->getStyle("J1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  305.  
  306.  
  307.     $objPHPExcel->getActiveSheet()->getStyle("1:1")->getFont()->setBold(true);
  308.     $objPHPExcel->getActiveSheet()->getStyle("A1:J1")->getFont()->setBold(true);
  309.  
  310.     $objConditional1 = new PHPExcel_Style_Conditional();
  311.     $objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_EXPRESSION)
  312.         ->addCondition('ISEVEN(ROW())');
  313.     $objConditional1->getStyle()->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getEndColor()->setARGB('FFCCCCCC');  
  314.  
  315.     $conditionalStyles = $objPHPExcel->getActiveSheet()->getStyle('B2')->getConditionalStyles();
  316.     array_push($conditionalStyles, $objConditional1);
  317.     $objPHPExcel->getActiveSheet()->getStyle("A1:J$i")->setConditionalStyles($conditionalStyles);
  318.  
  319.  
  320.     $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10.2602040816327);
  321.     $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
  322.     $objWriter->save($spreadsheet);
  323.  
  324.  
  325.  
  326.     // Lets mail out the results!!!!!
  327.     $mail = new PHPMailer;
  328.  
  329.     //$mail->SMTPDebug = 3;                                               // Enable verbose debug output
  330.  
  331.     $mail->isSMTP();                                                      // Set mailer to use SMTP
  332.     $mail->Host = '***************-com.mail.protection.outlook.com';      // Specify main and backup SMTP servers
  333.     $mail->Port = 25;                                                     // TCP port to connect to
  334.  
  335.     $mail->setFrom('fredw@*********.com', 'Fred W');
  336.     $mail->addAddress('kristis@*********.com', 'Kristi S');                    // Add a recipient
  337.     $mail->addCC('jayc@*********.com', 'Jay C');
  338.     $mail->addBCC('fredw@*********.com', 'Fred W');
  339.  
  340.     $mail->addAttachment($spreadsheet);                                   // Add attachments
  341.     $mail->isHTML(true);                                                  // Set email format to HTML
  342.  
  343.     $mail->Subject = 'Lag Time Report '.$endDate;
  344.     $mail->Body    = <<<EOD
  345. Hi Kristi,
  346. <p>Here is the Lag Time Report for $endDate</p>
  347. <font color="#888888"><br>
  348. <br>
  349. -- <br>
  350. Company Name
  351. Fred W- Computer Support Specialist<br>
  352. Phone: <a href="tel:555-555-5555" value="+15555555555" target="_blank">555-555-5555</a>
  353. Fax: <a href="tel:555-555-5555" value="+15555555555" target="_blank">555-555-55555</a><br>
  354. Email: <a href="mailto:fredw@**********.com" target="_blank"><span class="il">fredw@**********.com</span></a><br>
  355. <br>
  356. </font>
  357. EOD;
  358.     $mail->AltBody = <<<EOD
  359. Hi Kristi,
  360.  
  361. Here is the Lag Time Report for $endDate
  362.  
  363. --
  364. Company Name
  365. Fred W - Computer Support Specialist
  366. Phone: 555-555-5555 Fax: 555-555-5555
  367. Email: fredw@********.com
  368. EOD;
  369.  
  370.     if(!$mail->send()) {
  371.         echo "Message could not be sent.\n";
  372.         echo 'Mailer Error: ' . $mail->ErrorInfo ."\n";
  373.     } else {
  374.         echo "Message has been sent\n";
  375.     }
  376.  
  377. }
  378. catch(PDOException $e) {
  379.  
  380. }
  381.  
  382. ?>
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!