- <?php
-
- /***
- * CONFIGURATION - SET THESE
- */
-
- $folder = "/mnt/hgfs/PentestOS/RootShell/cal_xls/"; // the path to the folder containing the XLS files dump
- $user = "Ross Marks"; // name in column A to search for
-
-
- /***
- * MAIN PROGRAM DO NOT EDIT BELOW
- */
- use Shuchkin\SimpleXLSX;
- $usermap = ""; // to store which sheet contains user details
- $concatMap = array(); // to store job lengths
-
- require_once __DIR__.'/SimpleXLSX.php';
-
- // find latest file
- $files = glob($folder."*.xlsx");
- $files = array_combine($files, array_map("filemtime", $files));
- arsort($files);
- $latest_file = key($files);
-
- if ( $xlsx = SimpleXLSX::parse($latest_file) ) {
-
- // first find the users sheet
- for($i = 0; $i <= sizeof($xlsx->sheetNames()); $i++){
- //echo $i." : ".$xlsx->sheetName($i)."\n";
- if($xlsx->sheetName($i) == "User Map"){
- $usermap = $i;
- //echo "Users sheet: $usermap\n";
- }
- }
- //echo "User: ".$user."\n";
- $ical = "BEGIN:VCALENDAR
- VERSION:2.0
- PRODID:-//$user, Inc.//EN";
-
-
-
- // all other sheets != usermap
- for($i = 0; $i <= sizeof($xlsx->sheetNames())-1; $i++){
- if($xlsx->sheetName($i) != "User Map"){
-
- // populate map of jobs > 1 day long
- foreach( $xlsx->worksheet($i)->mergeCells->mergeCell as $c ) {
- $vals = explode(":", $c['ref']);
- $concatMap[$i.'-'.$vals[0]] = $vals[1];
- }
-
- //echo "### ".$xlsx->sheetName($i)." ###\n";
-
- foreach( $xlsx->rowsEx($i) as $rowsEx){
- for($j=1; $j<= sizeof($rowsEx)-2; $j++){
-
- if(trim($rowsEx[0]['value']) == $user){ // found user
-
-
- if(trim($rowsEx[$j]['value']) <> "" && trim($rowsEx[$j]['value']) <> "."){ // if contains data
- //echo $j." ".$xlsx->sheetName($i)." : " .$rowsEx[$j]['value']."\n";
- //print_r($rowsEx[$j]);
- $diff = 1;
- if(isset($concatMap[$i."-" .$rowsEx[$j]['name']])){
- //echo "more than 1 day! \n";
- $diff += getDiff($i."-" .$rowsEx[$j]['name'], $concatMap[$i."-" .$rowsEx[$j]['name']]);
- //echo $diff."\n";
- }
-
- $ical .= "
- BEGIN:VEVENT
- UID:" .md5(uniqid(mt_rand(), true))."
- DTSTAMP:".gmdate("Ymd")."T".gmdate("His")."Z
- DTSTART:".date("Ymd", strtotime($j." ".$xlsx->sheetName($i)));
-
- if($diff >= 1)
- $ical .= "\nDTEND:".date("Ymd", strtotime($j." ".$xlsx->sheetName($i) ." +$diff day"));
-
- $ical .= "
- SUMMARY:Booking for ".$rowsEx[$j]['value']."
- END:VEVENT";
-
-
- }
-
- }
- }
- }
- }
- }
-
-
- $ical .= "
- END:VCALENDAR";
-
- header("Content-type: text/calendar; charset=utf-8");
- header("Content-Disposition: inline; filename=calendar.ics");
- echo $ical;
-
- } else {
- echo SimpleXLSX::parseError();
- }
-
- function getDiff($start, $end){
- $start = preg_replace("/[^A-Z]+/", "", $start);
- $end = preg_replace("/[^A-Z]+/", "", $end);
- $s1 = alphabet_to_number($start);
- $e1 = alphabet_to_number($end);
- return $e1 - $s1;
- //return 2;
- }
- function alphabet_to_number($string) {
- $string = strtoupper($string);
- $length = strlen($string);
- $number = 0;
- $level = 1;
- while ($length >= $level ) {
- $char = $string[$length - $level];
- $c = ord($char) - 64;
- $number += $c * (26 ** ($level-1));
- $level++;
- }
- return $number;
- }
- ?>