<?PHP
$ppconf = array();
global $ppconf;
$pplang = array();
$ppioREQUEST = array();

$PPLANTID = 0;
$PPSESSION = "";
$PPLANTCAPTION = "";
require_once "phppunch_version.inc.php";
require_once "lang/el.inc.php";
require_once "conf/configuration.inc.php";
include $ppconf["dbcscriptpath"];
require_once "ppmysql.inc.php";
require_once "ppio.inc.php";

$hdrnow = gmdate('D, d M Y H:i:s') . ' GMT';

header('Expires: ' . $hdrnow); // rfc2616 - Section 14.21
header('Last-Modified: ' . $hdrnow);
header('Cache-Control: no-store, no-cache, must-revalidate, pre-check=0, post-check=0, max-age=0'); // HTTP/1.1
header('Pragma: no-cache'); // HTTP/1.0

$PPLANTID = 0;
$PPLANTCAPTION = "";
mt_srand ((double) microtime() * 1000000.0);

// connect to database
$ppconf["mysqli_linkid"] = mysqli_connect($ppconf["mysqli_server"], $ppconf["mysqli_username"], $ppconf["mysqli_password"], $ppconf["mysqli_dbname"]) or header("Location: install.php");
/*
if (!$ppconf["mysqli_linkid"]) {
	die("<HTML><HEAD></HEAD><BODY><SPAN style=\"color:red\">".$pplang["DB_CONNERR_CONNECT"]."</SPAN><BR><BR>DB_CONNERR_CONNECT</BODY></HTML>");
}
if (!@mysqli_select_db($ppconf["mysqli_linkid"])) {
	die("<HTML><HEAD></HEAD><BODY><SPAN style=\"color:red\">".$pplang["DB_CONNERR_SELECTDB"]."</SPAN><BR><BR>DB_CONNERR_SELECTDB</BODY></HTML>");
}
*/
$dbname = $ppconf["mysqli_dbname"];

/* *** KAZ start 19-2-2014
return mp3 file if exists else random number */
function pp_mp3name($io){
    $result = "".rand(1, 8)."";
    $bc = trim(pp_postfield("barcode")); 
    $handle=opendir('./wavs');
    if (file_exists('./wavs/'.$io.$bc.'.mp3')) { $result = $bc;}
    $result = $io.$result;
    return $result;
}

function pp_loginbyhost() {
	global $ppconf, $dbname, $PPLANTID, $PPLANTCAPTION, $pplang;
	// echo "cookie: ".$_COOKIE["PPLANTCOOKIEAUTH"];
	if (isset($_COOKIE["PPLANTCOOKIEAUTH"])) {
		$cookie = trim($_COOKIE["PPLANTCOOKIEAUTH"]);
		if (strlen($cookie) == 64) {
			$sqlcmd = "SELECT * FROM $dbname.pplant WHERE PPLANT_CookieAuth='".ppaddslashes($cookie)."' LIMIT 1";
			$result = mysqli_query($ppconf["mysqli_linkid"], $sqlcmd);
			if ($myrow = mysqli_fetch_array($result)) {
				$PPLANTID = $myrow["PPLANT_ID"];
				$PPLANTCAPTION = $myrow["PPLANT_Caption"];
				$rc = "OK";
				mysqli_free_result($result);
				return $rc;
			}
			mysqli_free_result($result);
		}
	}
    $host = "unknown";
    /* update for PHP5.4+
	if (isset($_ENV["REMOTE_ADDR"])) $host=$_ENV["REMOTE_ADDR"];
    if (isset($HTTP_ENV_VARS["REMOTE_ADDR"])) $host=$HTTP_ENV_VARS["REMOTE_ADDR"];
    if (isset($HTTP_SERVER_VARS["REMOTE_ADDR"])) $host=$HTTP_SERVER_VARS["REMOTE_ADDR"];
	*/
	$host = $_SERVER['HTTP_HOST'];
  	$rc = "unknwown error";
    if ($host == "unknown") {
    	$rc = ("</HEAD><BODY>".$pplang["AUTH_NOHOST"]."</BODY></HTML>");
    }
	elseif (strlen($host) > 2) //It was 4 but server returns "::1"
	{
		$sqlcmd = "SELECT * FROM $dbname.pplant WHERE PPLANT_CurrentHost='".ppaddslashes($host)."' LIMIT 1";
		$result = mysqli_query($ppconf["mysqli_linkid"], $sqlcmd);
		if ($myrow = mysqli_fetch_array($result)) {
			$PPLANTID = $myrow["PPLANT_ID"];
			$PPLANTCAPTION = $myrow["PPLANT_Caption"];
			$rc = "OK";
		} else {
    		$rc = "</HEAD><BODY>".sprintf($pplang["AUTH_INVALIDHOST"], htmlspecialchars($host))."<BR><BR><B>".$pplang["AUTH_RETRYLATER"]."</B></BODY></HTML>";
		}
		mysqli_free_result($result);
	}
	return $rc;
}
function pp_loginbysession() {
	global $dbname, $PPSESSION, $PPLANTID, $PPLANTCAPTION;
	global $ppconf, $pplang;
	$PPLANTID = 0;
	if ($PPSESSION == "") {
		if (isset($_REQUEST["PPSESSION"]) && strlen($_REQUEST["PPSESSION"]) == 64) {
			$PPSESSION = pp_postfield("PPSESSION");
		}
	}	
	if ($PPSESSION != "") {
		$stmp = time();
		$stmp2 = $stmp - (int)$ppconf["login_maxidlesecs"];
		
		$sqlcmd = "SELECT * FROM $dbname.pplant WHERE PPLANT_AdmSession='".ppaddslashes($PPSESSION)."' AND PPLANT_AdmSessionStamp >= $stmp2 AND PPLANT_AdmSessionStamp <= $stmp LIMIT 1";
		$result = mysqli_query($ppconf["mysqli_linkid"], $sqlcmd);
		if ($myrow = mysqli_fetch_array($result)) {
			$PPLANTID = $myrow["PPLANT_ID"];
			$PPLANTCAPTION = $myrow["PPLANT_Caption"];
			$PPSESSION = $myrow["PPLANT_AdmSession"];
			$sqlcmd = "UPDATE $dbname.pplant SET PPLANT_AdmSessionStamp='$stmp' WHERE PPLANT_ID='$PPLANTID' LIMIT 1";
			mysqli_query($ppconf["mysqli_linkid"], $sqlcmd);
		} else {
			$PPSESSION = "";
		}
		mysqli_free_result($result);
	}
	if ($PPLANTID == 0) {
		pp_openhead();
		echo "</HEAD><BODY><B>".$pplang["ADM_TITLE"]."</B><HR>\n";
		echo "<FORM method=\"post\" action=\"".$ppconf["htroot"]."admin.php?login=1\">\n";
		echo ppformelemts();
		echo "<TABLE border=\"0\" cellpadding=\"0\" cellspacing=\"10\">";
		echo "<TR><TD>".$pplang["ADM_LOGIN_INST"].":</TD><TD><INPUT type=\"text\" name=\"mand\" size=\"20\" maxlength=\"32\"></TD></TR>\n";
		echo "<TR><TD>".$pplang["ADM_LOGIN_PASS"].":</TD><TD><INPUT type=\"password\" name=\"pwd\" size=\"10\" maxlength=\"20\"></TD></TR>\n";
		echo "<TR><TD></TD><TD><INPUT type=\"submit\" value=\"".$pplang["ADM_LOGIN_SUBMIT"]."\"></TD></TR>\n";
		echo "</TABLE>";
		echo "</FORM>";
		echo "<BR><A href=\"".$ppconf["htroot"]."index.php?void=0\">".$pplang["ADM_QUIT"]."</A>";
		die ("</BODY></HTML>");
	}
}

function pp_openhead() {
	global $ppconf, $pplang;
	echo "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 Transitional//EN\">\n";
	echo "<HTML><HEAD>\n";
	echo "<META NAME=\"ROBOTS\" CONTENT=\"NOINDEX, NOFOLLOW\">\n";
	echo "<META HTTP-EQUIV=\"Expires\" CONTENT=\"0\">\n";
	echo "<META http-equiv=Content-Language content=".$pplang["HTTP_LANG"].">\n";
	echo "<META http-equiv=Content-Type content=\"text/html; charset=utf-8\">\n";
	echo "<LINK rel=\"stylesheet\" href=\"".$ppconf["htroot"]."pmstyle.css\" type=\"text/css\" media=\"screen\" />\n";
	echo "<link href=\"calendar/calendar.css\" rel=\"stylesheet\" type=\"text/css\" />\n";
    echo "<script language=\"javascript\" src=\"calendar/calendar.js\"></script>\n";
}

function pp_admtitle() {
	global $ppconf, $pplang;
	global $PPLANTCAPTION, $PPSESSION;
	echo "<TABLE border=\"0\" cellpadding=\"0\" cellspacing=\"0\" width=\"100%\">";
	echo "<TR><TD align=left valign=top><BIG><B>".htmlspecialchars($PPLANTCAPTION)."</B></BIG><BR>".$pplang["ADM_TITLE"]."</TD>";
	echo "<TD align=right valign=top><A href=\"admin.php?PPSESSION=$PPSESSION\">".$pplang["ADM_MENU"]."</A><BR>";
	echo "<A href=\"admin.php?PPSESSION=$PPSESSION&amp;logout=1\">".$pplang["ADM_QUIT"]."</A></TD></TR>\n";
	echo "</TABLE><HR>\n";
}

function pp_contracttypeliteral($i) {
	global $pplang;
	$i = (int)$i;
	if (isset($pplang["CONTRACTTYPE_".$i])) {
		return $pplang["CONTRACTTYPE_".$i];
	} else {
		return "[".$i."]";
	}
}

function pp_numberofdays($date1, $date2) {
	$syear = substr($date1,0,4);
	$smonth = substr($date1,5,2);
	$sday = substr($date1,8,2);
	
	$eyear = substr($date2,0,4);
	$emonth = substr($date2,5,2);
	$eday = substr($date2,8,2);

	$diffs0 = gmmktime(0,0,0,$smonth, $sday, $syear);
	$diffs1 = gmmktime(0,0,0,$emonth, $eday, $eyear);
	$diff = round(($diffs1 - $diffs0) / (24*60*60));
	$diff += 1; // Anzahl Tage
	return $diff;
}

function pp_countholidays($date1, $date2, $cmpr = "<=") {
	global $ppconf, $dbname, $PPLANTID;
	$date1 = ppaddslashes($date1);
	$date2 = ppaddslashes($date2);
	$rc = 0;
	$sqlcmd = "SELECT COUNT(HOLIDAY_ID) AS cnt FROM $dbname.pholiday WHERE HOLIDAY_PlantPTR='$PPLANTID' AND HOLIDAY_Date >= '$date1' AND HOLIDAY_Date $cmpr '$date2'";
	$result = mysqli_query($ppconf["mysqli_linkid"], $sqlcmd);
	if ($row = mysqli_fetch_assoc($result)) {
		$rc = 0 + $row["cnt"];
	}
	mysqli_free_result($result);
	return $rc;
}

function pp_countvacationdays($workerid, $date1,$date2,$cmpr = "<=") {
	global $ppconf, $dbname, $PPLANTID;
	$date1 = ppaddslashes($date1);
	$date2 = ppaddslashes($date2);
	$workerid = ppaddslashes($workerid);
	$rc = 0;
	$sqlcmd = "SELECT pvacationday.VACDAY_Date, pholiday.HOLIDAY_ID FROM $dbname.pvacationday"
	." LEFT JOIN $dbname.pholiday ON pholiday.HOLIDAY_PlantPTR=pvacationday.VACDAY_PlantPTR AND pholiday.HOLIDAY_Date=pvacationday.VACDAY_Date"
	." WHERE VACDAY_PlantPTR='$PPLANTID' AND VACDAY_WorkerPTR='$workerid' AND VACDAY_Date >='$date1' AND VACDAY_Date $cmpr '$date2'";
	$row = array();
	$result = mysqli_query($ppconf["mysqli_linkid"], $sqlcmd);
	while ($row = mysqli_fetch_assoc($result)) {
		if (isset($row["HOLIDAY_ID"]) && $row["HOLIDAY_ID"] > 0) {
			// Dont count vacation on holiday
		} else {
			$rc += 1;
		}
		
		$row = array();
	}
	mysqli_free_result($result);
	return $rc;
}

function pp_countworkhours($workerid, $date1, $date2) {
	global $ppconf, $dbname, $PPLANTID;
	$rc = 0;
	$sqlcmd = "SELECT SUM(PR_Minutes) AS t FROM $dbname.ppresence WHERE PR_WorkerPTR='".ppaddslashes($workerid)."'"
	." AND PR_Come >= '".ppaddslashes($date1." 00-00-00")."'"
	." AND PR_Come <= '".ppaddslashes($date2." 23-59-59")."'";
	$tres = mysqli_query($ppconf["mysqli_linkid"], $sqlcmd);
	if ($trow = mysqli_fetch_assoc($tres)) {
		$rc = $trow["t"] / 60.0;
	}
	mysqli_free_result($tres);
	return $rc;
}

function pp_countsickhours($workerid, $date1, $date2) {
	global $ppconf, $dbname, $PPLANTID;
	$rc = 0;
	$sqlcmd = "SELECT SUM(SICKDAY_Minutes) AS t FROM $dbname.psickday WHERE SICKDAY_WorkerPTR='".ppaddslashes($workerid)."'"
	." AND SICKDAY_Date >= '".ppaddslashes($date1)."'"
	." AND SICKDAY_Date <= '".ppaddslashes($date2)."'";
	$tres = mysqli_query($ppconf["mysqli_linkid"], $sqlcmd);
	if ($trow = mysqli_fetch_assoc($tres)) {
		$rc = $trow["t"] / 60.0;
	}
	mysqli_free_result($tres);
	return $rc;
}

function pp_countadjustmenthours($workerid, $date1, $date2) {
	global $ppconf, $dbname, $PPLANTID;
	$rc = 0;
	$sqlcmd = "SELECT SUM(ADJ_Minutes) AS t FROM $dbname.padjustment WHERE ADJ_WorkerPTR='".ppaddslashes($workerid)."'"
	." AND ADJ_Date >= '".ppaddslashes($date1)."'"
	." AND ADJ_Date <= '".ppaddslashes($date2)."'";
	$tres = mysqli_query($ppconf["mysqli_linkid"], $sqlcmd);
	if ($trow = mysqli_fetch_assoc($tres)) {
		$rc = $trow["t"] / 60.0;
	}
	mysqli_free_result($tres);
	return $rc;
}

function pp_getcontractsaldo($contractid, $saldostamp) {
	global $dbname, $PPLANTID, $ppconf, $pplang;
	$res = array();
	$res["hours"] = 0.0;
	$res["htmllong"] = "";
	$res["htmlshort"] = "";
	
	$contractid = (int)$contractid;
	$sqlcmd = "SELECT pcontract.* FROM $dbname.pcontract INNER JOIN $dbname.pworker ON pworker.WORKER_ID=pcontract.CONTRACT_WorkerPTR "
	." AND WORKER_PlantPTR='$PPLANTID'"
	." WHERE CONTRACT_ID='$contractid' LIMIT 1";
	$cresult = mysqli_query($ppconf["mysqli_linkid"], $sqlcmd);
	if ($crow = mysqli_fetch_assoc($cresult)) {
		if ($crow["CONTRACT_Type"] == 1) {	// gesamtstunden p.a.
			// Abgeleistete Stunden Vertragsbeginn bis saldostamp/Vertragsende ermitteln
			
			$end = date("Y-m-d", $saldostamp);	// Uhrzeit schen, nur datum
			$saldostamp = strtotime($end." 00:00:00");
			
			$startstamp = strtotime($crow["CONTRACT_Start"]." 00:00:00");
			$endstamp = strtotime($crow["CONTRACT_End"]. " 00:00:00");
			if ($endstamp < $saldostamp) {
				$saldostamp = $endstamp;
			}
			$start = date("Y-m-d",$startstamp);
			$end = date("Y-m-d",$saldostamp);
								
			$syear = substr($start,0,4);
			$smonth = substr($start,5,2);
			$sday = substr($start,8,2);
			
			$eyear = substr($end,0,4);
			$emonth = substr($end,5,2);
			$eday = substr($end,8,2);
			
			// [int hour [, int minute [, int second [, int month [, int day [, int year
			$diffs0 = gmmktime(0,0,0,$smonth, $sday, $syear);
			$diffs1 = gmmktime(0,0,0,$emonth, $eday, $eyear);
			$diff = round(($diffs1 - $diffs0) / (24*60*60));
			$diff += 1; // Anzahl Tage
			
			$t_worked = pp_countworkhours($crow["CONTRACT_WorkerPTR"], $start, $end);
			$t_sick = pp_countsickhours($crow["CONTRACT_WorkerPTR"], $start, $end);
			$t_adjust = pp_countadjustmenthours($crow["CONTRACT_WorkerPTR"], $start, $end);
			$t_performed = $t_worked + $t_sick + $t_adjust;
			// Geleistete Stunden jetzt in t_performed
			
			$difftotal = pp_numberofdays($crow["CONTRACT_Start"], $crow["CONTRACT_End"]);
			if ($difftotal < 1) $difftotal = 1;
			$t_ref = ($diff / $difftotal) * $crow["CONTRACT_T1_DestHours"];
			
			$t_diff = $t_performed - $t_ref;
			$res["hours"] = 0.0 + number_format($t_diff, 1, ".","");
			
			$tarr = array(
				 "[hdif]" => number_format($t_diff, 1, $ppconf["decimalseparator"],"")
				,"[hperf]" => number_format($t_performed, 1, $ppconf["decimalseparator"],"")
				,"[hdest]" => number_format($crow["CONTRACT_T1_DestHours"], 1, $ppconf["decimalseparator"],"")
				,"[ddurtotal]" => round($difftotal)
				,"[ddurpast]" => round($diff)
				,"[hworked]" => number_format($t_worked, 1, $ppconf["decimalseparator"],"")
				,"[hsick]" => number_format($t_sick, 1, $ppconf["decimalseparator"],"")
				,"[hadj]" => number_format($t_adjust, 1, $ppconf["decimalseparator"],"")
			);
			$res["htmllong"] = strtr($pplang["CS_1_HTMLLONG"], $tarr);
			
			$res["htmlshort"] = number_format(abs($t_diff), 1, $ppconf["decimalseparator"],"");
			if ($t_diff < 0) {
				$res["htmlshort"] .= " ".$pplang["CS_MISSINGHOURS"];
			} else {
				$res["htmlshort"] .= " ".$pplang["CS_OVERHOURS"];
			}						
		} elseif ($crow["CONTRACT_Type"] == 2) {	// gesamtstunden p.a. - Urlaub
			$end = date("Y-m-d", $saldostamp);	// Uhrzeit, nur datum
			$saldostamp = strtotime($end." 00:00:00");
			
			$startstamp = strtotime($crow["CONTRACT_Start"]." 00:00:00");
			$endstamp = strtotime($crow["CONTRACT_End"]. " 00:00:00");
			if ($endstamp < $saldostamp) {
				$saldostamp = $endstamp;
			}
			$start = date("Y-m-d",$startstamp);
			$end = date("Y-m-d",$saldostamp);
			// Verbleibende Arbeitstage berechnen
			$remainingbrut = pp_numberofdays($end, $crow["CONTRACT_End"]) - 1;
			$remholidays = pp_countholidays($end, $crow["CONTRACT_End"]);
			
			$donevacation = pp_countvacationdays($crow["CONTRACT_WorkerPTR"], $crow["CONTRACT_Start"],$end,"<");
			$remvacation = $crow["CONTRACT_T2_VacationDays"] - $donevacation;
			
			$remainingnet = $remainingbrut - $remholidays - $remvacation;
			
			// Gesamtzahl Arbeitstage berechnen
			
			$totalbrut = pp_numberofdays($start, $crow["CONTRACT_End"]);
			$totalholidays = pp_countholidays($start, $crow["CONTRACT_End"]);
				
			$totalnet = $totalbrut - $totalholidays - $crow["CONTRACT_T2_VacationDays"];
			
			// Verstrichende Arbeitstage berechnen
			$pastbrut = pp_numberofdays($crow["CONTRACT_Start"],$end);
			$pastholidays = pp_countholidays($crow["CONTRACT_Start"], $end);
			$pastvacation = pp_countvacationdays($crow["CONTRACT_WorkerPTR"], $crow["CONTRACT_Start"], $end);
			
			// $pastnet = $totalnet - $remainingnet;
			$pastnet = $pastbrut - $pastholidays - $pastvacation;
			$remainingnet = $totalnet - $pastnet;
			
			if ($totalnet < 1) $totalnet = 1;
						
			$t_ref = ($pastnet / $totalnet) * $crow["CONTRACT_T2_DestHours"];
			$t_worked = pp_countworkhours($crow["CONTRACT_WorkerPTR"], $start, $end);
			$t_sick = pp_countsickhours($crow["CONTRACT_WorkerPTR"], $start, $end);
			$t_adjust = pp_countadjustmenthours($crow["CONTRACT_WorkerPTR"], $start, $end);
			$t_performed = $t_worked + $t_sick + $t_adjust;
			
			
			$t_diff = $t_performed - $t_ref;

			$res["hours"] = 0.0 + number_format($t_diff, 1, $ppconf["decimalseparator"],"");
			
			$tarr = array(
				 "[hdif]"=>number_format($t_diff, 1, $ppconf["decimalseparator"],"")
				,"[hperf]"=>number_format($t_performed, 1, $ppconf["decimalseparator"],"")
				,"[hdest]"=>number_format($crow["CONTRACT_T2_DestHours"], 1, $ppconf["decimalseparator"],"")
				,"[ddurtotal]"=>round($totalnet)
				,"[ddurpast]"=>round($pastnet)
				,"[ddurrem]"=>round($remainingnet)
				,"[hworked]"=>number_format($t_worked, 1, $ppconf["decimalseparator"],"")
				,"[hsick]"=>number_format($t_sick, 1, $ppconf["decimalseparator"],"")
				,"[hadj]"=>number_format($t_adjust, 1, $ppconf["decimalseparator"],"")
			);
			$res["htmllong"] = strtr($pplang["CS_2_HTMLLONG"], $tarr);	
			$res["htmlshort"] = number_format(abs($t_diff), 1, $ppconf["decimalseparator"],"");
			if ($t_diff < 0) {
				$res["htmlshort"] .= " ".$pplang["CS_MISSINGHOURS"];
			} else {
				$res["htmlshort"] .= " ".$pplang["CS_OVERHOURS"];
			}						

		}
	}
	mysqli_free_result($cresult);
	return $res;
}

function pp_drawjsreports() {
    	echo "<SCRIPT language=\"JavaScript\">\n";
	echo "function sv(w,d,m) {\n";
	echo " document.getElementById('fm').action='admin.php#MO' + m;\n";
	echo " document.getElementById('fm').dbaction.value='setvac';\n";
	echo " document.getElementById('fm').w.value=w;\n";
	echo " document.getElementById('fm').day.value=d;\n";
	echo " document.getElementById('fm').submit(); return false;\n";
	echo "}\n";
	echo "function cv(w,d,m) {\n";
	echo " document.getElementById('fm').action='admin.php#MO' + m;\n";
	echo " document.getElementById('fm').dbaction.value='clrvac';\n";
	echo " document.getElementById('fm').w.value=w;\n";
	echo " document.getElementById('fm').day.value=d;\n";
	echo " document.getElementById('fm').submit(); return false;\n";
	echo "}\n";
	echo "function ss(w,d,m) {\n";
	echo " document.getElementById('fm').action='admin.php#MO' + m;\n";
	echo " document.getElementById('fm').dbaction.value='setsick';\n";
	echo " document.getElementById('fm').w.value=w;\n";
	echo " document.getElementById('fm').day.value=d;\n";
	echo " document.getElementById('fm').submit(); return false;\n";
	echo "}\n";
	echo "function cs(w,d,m) {\n";
	echo " document.getElementById('fm').action='admin.php#MO' + m;\n";
	echo " document.getElementById('fm').dbaction.value='clrsick';\n";
	echo " document.getElementById('fm').w.value=w;\n";
	echo " document.getElementById('fm').day.value=d;\n";
	echo " document.getElementById('fm').submit(); return false;\n";
	echo "}\n";
	echo "</SCRIPT>";
}


?>
