Location: PHPKode > projects > ConPortal > conportal/inc/punches/db.php
<?php
/*
 *  ConPortal - Pomona College ITS & Bucknell University ISR scheduling appplication
 *  Copyright (C) 2005-2007  Pomona College, Bucknell University
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of version 2 of the GNU General Public License
 *  as published by the Free Software Foundation.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
 */

/*
Implemented in this file
	getAlbanyId
	getThursdayOption
	punchIn
	punchOut
	isPunchedIn
	getPunchedIn
	getPunchesForUser
	getPunchDetails
	getPunchDetailsForArray
	checkIP
	getUnusualPunchesForDateRange
	addPunchForUser
	modifyPunchForUser
	getTotalTimeWorked
	findMostRecentShift	
	findShiftQuery

 */

function getAlbanyId($netid) { // real half-assed ldap query, someone should find a real way to do this
	$albanyid = `CONFIDENTIAL ldaplist -l passwd $netid | grep employeeNumber | sed s/employeeNumber://`;
	return $albanyid;
}

function getThursdayOption() { // grabs each thursday from the database for timesheet management
	$option = array();
	$d = safeQuery("select CURDATE()");
	$cur = mysql_fetch_row($d);
	$a = preg_split('/-/',$cur[0]);

	$curday = mktime(0, 0, 0, (int)ltrim($a[1], '0') , (int)ltrim($a[2], '0') , (int)$a[0]);
	
	$result = safeQuery("select * from thursday");
	while($row = mysql_fetch_assoc($result)) {
		$b = preg_split('/-/',$row['start']);
		$m_start = mktime(0, 0, 0, (int)ltrim($b[1], '0') , (int)ltrim($b[2], '0') , (int)$b[0]);
		$m_end = strtotime("+ 14 days", $m_start);
		
		if ($curday >= strtotime("+ 3 days",$m_start) && $curday <= strtotime("+ 3 days", $m_end)) {		
			$option[] = "<option value=\"" . $row['start'] . "\" selected >Week of " . $row['start'] . "</option>\n";
		}
		else {
			$option[] = "<option value=\"" . $row['start'] . "\">Week of " . $row['start'] . "</option>\n";
		}
		$last = $row['start'];
	}
	return $option;
}


function punchIn($userId)
{
		
	if(checkIP())
	{
		$result = safeQuery("INSERT INTO punches (userid, punched_in, login_ip) VALUES (". $userId .", 1,'" . $_SERVER['REMOTE_ADDR'] ."')");
		return TRUE;
	}
	return FALSE;
}
	
//function punchOut
function punchOut($userId)
{
	if(checkIP())
	{
		if(isset($_POST['fix_punch']) )
		{
		$row = findMostRecentShift($userId);
	
		$result1 = safeQuery("UPDATE punches SET punched_in = 0, logout_ip = \"" . $_SERVER['REMOTE_ADDR'] . "\", time_out = TIMESTAMP(\"". $row['end_date'] . "\", \"" . $row['end_time'] . "\") WHERE userid = " . $userId . " AND punched_in = 1");	

		//cleanup
		unset($_POST['fix_punch']);
		
		return TRUE;
		}
		else{
		$result1 = safeQuery("UPDATE punches SET time_out = NOW(), punched_in = 0, logout_ip = \"" . $_SERVER['REMOTE_ADDR'] . "\"  WHERE userid = " . $userId . " AND punched_in = 1");
			
		return TRUE;			
		}
	}
	else
	{
		return FALSE;		
	}
		
}
	
function getPunchedIn()
{
	//$result = safeQuery("SELECT users.first, users.last, users.username, punches.time_in FROM users, punches WHERE punches.punched_in =1 AND punches.userid = users.pid");  
	$result = safeQuery("SELECT users.first, users.last, users.username, users.active, punches.time_in, ip_list.description FROM users, punches, ip_list WHERE punches.punched_in =1 AND punches.userid = users.pid and punches.login_ip = ip_list.addresses and users.active = 1");  

	$a = array();
	while ($row = mysql_fetch_assoc($result))
		$a[] = $row;
	return $a;
}

function isPunchedIn($userId)
{
	$result = array();
	$result =  safeQuery("SELECT time_in FROM punches WHERE punched_in = 1 AND userid = " . $userId);
	if(mysql_fetch_assoc($result))
		return TRUE;
	else
		return FALSE;
}
/*	
function getPunchesForUser($userId, $startDate, $endDate)
{
	$result = safeQuery("SELECT * FROM punches WHERE userid = " . $userId 
		. " AND time_in <= FROM_UNIXTIME(" . $startDate . ") AND time_out >= 
			FROM_UNIXTIME(" . $endDate . ")");
	$a = array();
	while ($row = mysql_fetch_assoc($result))
		$a[] = $row;
	return $a;
	}
*/

function getPunchesForDateRangeAndUser($userPid, $startDate, $endDate )
{
		$result = safeQuery("SELECT pid, time_in, time_out, TIMEDIFF(time_out, time_in) as time_worked " . 
		"FROM punches WHERE userid = %d AND " .
		"time_in <= FROM_UNIXTIME(%d) AND (time_out >= FROM_UNIXTIME(%d) OR time_out = \"0000-00-00 00:00:00\") ORDER BY 	time_in", $userPid, $endDate, $startDate);
	$a = array();
	while ($row = mysql_fetch_assoc($result))
	{
			$a[] = $row['pid'];
	}
	return $a;
}
	

/*
 * master punches query, based on getShiftDetails in /inc/shifts/db.php
 * all other functions should use this (or getPunchDetailsForArray, below) to
 * retrieve punch data.  NEVER, NEVER write "select * from punches"!
 */
function getPunchDetails($pid)
{
	$result = safeQuery("SELECT pid, userid, time_in, time_out, TIMEDIFF(time_out, time_in) as time_worked " .
		"FROM punches WHERE pid = %d", $pid);
	return mysql_fetch_assoc($result);
}

/*
 * getPunchDetailsForArray - returns details for each in an array of punch PIDs.
 */
function getPunchDetailsForArray($array)
{
	$a = array();
	foreach ($array as $pid)
		$a[] = getPunchDetails($pid);
	return $a;
}

/*checkIP - checks the user's IP address against known good ones in the database.
Returns true if there is a match, or false otherwise */
function checkIP()
{
	$result = array();
	$result =  safeQuery("SELECT addresses FROM ip_list WHERE addresses = \"" . $_SERVER['REMOTE_ADDR'] . "\"");
	if(mysql_fetch_assoc($result))
		return TRUE;
	else
		return FALSE;
}


/* generate unusual logout ip list 

even better query, gets names & dates
SELECT first, last, time_out, logout_ip FROM punches, users WHERE punches.pid NOT IN (SELECT punches.pid FROM ip_list, punches WHERE logout_ip = addresses) AND users.pid = punches.userid ORDER BY time_out */

function getUnusualPunchesForDateRange($startDate, $endDate )
{
	$result = safeQuery("SELECT first, last, time_in, time_out, login_ip, logout_ip FROM punches, users WHERE punches.pid NOT IN (SELECT punches.pid FROM ip_list, punches WHERE logout_ip = description AND login_ip = description) AND users.pid = punches.userid AND time_in <= FROM_UNIXTIME(%d) AND time_out >= FROM_UNIXTIME(%d) ORDER BY time_out", $endDate, $startDate);

	$a = array();
	while ($row = mysql_fetch_assoc($result))
	{
			$a[] = $row;
	}
	return $a;
}
	
function addPunchForUser($user, $timeIn, $timeOut)
{
	//first, check if this is a punch in & out, or just punch in
	if($timeOut == "0000-00-00 00:00:00")
	{
		$result = safeQuery("INSERT INTO punches (userid, time_in, punched_in, login_ip) VALUES (". $user .", \"" . $timeIn . "\", 1, '127.0.0.1')");
	}
	else
	{
		//Still doing 2 queries because I'm chickenshit.  
		$result = safeQuery("INSERT INTO punches (userid, time_in, time_out, punched_in, logout_ip) VALUES (". $user .", \"" . $timeIn . "\",  \"" . $timeOut . "\",  0, '127.0.0.1')");
	}
	
	if($result) 
		return TRUE;
	else
		return FALSE;
}

function modifyPunchForUser($pid, $timeIn, $timeOut)
{
		//Perform the update
		$result1 = safeQuery("UPDATE punches SET time_in = \"" . $timeIn . "\", time_out = \"" . $timeOut . "\", login_ip = '127.0.0.1', logout_ip = '127.0.0.1' WHERE pid = " . $pid);
		//if timeOut isn't still all zeros, we know that the user should be punched out.   
		if(!($timeOut == "0000-00-00 00:00:00"))
		{
			$result2 = safeQuery("UPDATE punches SET  punched_in = 0 WHERE pid = " . $pid);
		}
		else //else better make sure they're punched in!
		{
			$result2 = safeQuery("UPDATE punches SET  punched_in = 1 WHERE pid = " . $pid);
		}
		//if I were a better coder, I'd give something back.  Alas!
		return;
}

/*note the blecherousness in the sql statement - turns out using SUM on 'time' values
gives rather odd results... I believe it implicitly casts to some kind of bizzaro-seconds (not regular seconds!) or something. In any case, this code should work.  

UPDATE: This function may be obsolete now; see sum_time in /punches/display.php */
function getTotalTimeWorked($user, $start, $end)
{
		$result = safeQuery("SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(time_out, time_in)))) " . 
		"FROM punches WHERE userid = %d AND " .
		"time_in <= FROM_UNIXTIME(%d) AND time_out >= FROM_UNIXTIME(%d)",
		$user, $end, $start);
		
		return mysql_result($result, 0);
}

function findMostRecentShift($userId)
{
	//Ok, this is not straightforward, given that shifts and punches are quite different things, but it should work...
	
	//I've left in code that writes the results of the fix to a file, but commented it out
	//$handle = fopen("/home/accounts/projects/conportal/OHNOESLOG.txt", "a");
	
	//$writestr = "File open: " . date('l dS \of F Y h:i:s A') . " for userId " . $userId . "\n"; 	
	//fwrite($handle, $writestr);
	

	//first figure out the day of the week, and if necessary change it so it reflects how the database thinks of the days of the week...
	$d_o_w = date("w");
	if($d_o_w == 0)
	{
		$d_o_w = 7;
	}
	
	//keep track of how many days back we've gone
	$daysBack = 0;
	
	//do the first query
	$result = findShiftQuery($userId, $d_o_w, $daysBack, $handle);

	//while the query returns no result, keep walking backwards through the list; limited to 7 days.  
	while((mysql_num_rows($result) == 0) && ($daysBack <7))
	{
		//move day of week back, set to Sunday if need be.  Increment $daysback
		$d_o_w--;
		if($d_o_w == 0)
		{
			$d_o_w = 7;
		}
		$daysBack++;
		
		//query again!
		$result = findShiftQuery($userId, $d_o_w, $daysBack, $handle);
	}

	$row = mysql_fetch_assoc($result);
	
	//$a1 = var_export($row, true);
	//fwrite($handle, "Row: " . $a1  . "\n");

	//perform magic to make end_date match the most recent day

	$dayQuery = safeQuery("SELECT DATE(NOW() - INTERVAL " . $daysBack . " DAY)");
	//$a2 = var_export($dayQuery, true);
	//fwrite($handle, "dayQuery: " . $a2 . "\n");
	
	$shiftDate = mysql_result($dayQuery,0,0);
	//$a3 = print_r($shiftDate);
	//fwrite($handle, "shiftDate: " . $shiftDate . "\n");
	$row['end_date'] = $shiftDate;
	//$a4 = var_export($row, true);
	//fwrite($handle, "Modified Row: " . $a4. "\n");
	//var_dump($row);
	//fwrite($handle, "File closing.\n");
	//fclose($handle);

	return $row;
	
	
}

function findShiftQuery($userId, $d_o_w, $daysBack, $handle)
{
	if($daysBack > 0)
	{
		$qstring = "SELECT end_date, end_time FROM shifts WHERE " .
		"owner = " . $userId . " AND day_of_week = " . $d_o_w . " AND " . 
		"end_date > (NOW() - INTERVAL 7 DAY)  AND " .
		"start_date < NOW() ORDER BY end_time DESC";
	}
	else //make sure we only pick end times that are earlier than right now!
	{
		$qstring = "SELECT end_date, end_time FROM shifts WHERE " .
		"owner = " . $userId . " AND day_of_week = " . $d_o_w . " AND " . 
		"end_time < NOW() AND end_date > (NOW() - INTERVAL 7 DAY)  AND " .
		"start_date < NOW() ORDER BY end_time DESC";
	}
	$result = safeQuery($qstring);

	//fwrite($handle, "Query string: " . $qstring . "\n");	

	return $result;
}

function getIPlist()
{
	$result = safeQuery("SELECT * FROM ip_list");

	$a = array();
	while ($row = mysql_fetch_assoc($result))
	{
			$a[] = $row;
	}
	return $a;
}

function deleteIP($ip)
{
	$result = safeQuery("DELETE FROM ip_list WHERE addresses = \"" . $ip ."\"");
	return $result;
}

function addIP($ip, $desc)
{
	$result = safeQuery("INSERT INTO ip_list (addresses ,description) 
			VALUES (\"" . $ip . "\",\"" . $desc . "\")");
	return $result;
}

?>
Return current item: ConPortal