<?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;
}
?>