<?php
	include('/var/www/html/uclit/protected/common/login/check.php');
	include('/var/www/html/uclit/protected/common/css_login/css_head2.php');
	include('/var/www/html/uclit/protected/connections/db_help.php');
		
	//Select a database
	$db = mysql_select_db('helpcalls', $helpConn) or die("Couldn't select helpcalls database ");
	
	$today = "";
	$today = date("Y-m-d");
	$datetimenow = date('Y-m-d')." ".strftime("%H:%M:%S");
	
	$help_id = $_POST['help_id'];
	$completebox = $_POST['completebox'];
	$desctext = $_POST['desctext'];
	$restext = $_POST['restext'];
	$technician = $_POST['technician'];
	$new_tech = $_POST['new_tech'];

	$help_user_id = "";
	$erma = "erma.fritsche@uc.edu";

	$sentmsgRespond = "";
	if($completebox == true)
	{
		$dbConn = mysql_select_db('helpcalls', $helpConn) or die("Couldn't select database");
		//sql statement where completed box = 1
		$query= "UPDATE log 
				 SET Completed='1', Help_Desc='$desctext', Help_Resolution='$restext', Help_Tech='$technician', Date_Completed='$today' 
				 WHERE Help_ID='$help_id'" ;
		$resultUpdate = mysql_query($query);

		$query_email = "SELECT Email, Help_User, Help_Desc, Help_User_ID 
						FROM log 
						WHERE Help_ID = '$help_id'";
		$sql_result_email = mysql_query($query_email, $helpConn) or die("Couldn't execute query.<p>$query_email</p>");

		$help_desc2 = "";

		while($row = mysql_fetch_array($sql_result_email))
 		{
 			$help_email = $row['Email'];
			$help_user = $row['Help_User'];
			$help_user_id = $row['Help_User_ID'];
			$help_desc2 = $row['Help_Desc'];
		}

		if($help_user_id != "0")
		{
			//sending email to head department by get her email address
			$dbConn = mysql_select_db('uclit_db', $uclit_dbConn) or die("Couldn't select database uclit_db.");
			$query_head_dept_id = "SELECT departments.dept_head_id, departments.dept_id 
								   FROM personnel 
								   INNER JOIN departments ON personnel.dept_id = departments.dept_id 
								   WHERE personnel.personnel_id = '$help_user_id'";
			$sql_result_id = mysql_query($query_head_dept_id, $uclit_dbConn) or die("Couldn't execute query.<p>$query_head_dept_id</p>");
			while($row = mysql_fetch_array($sql_result_id))
 			{
 				$head_dept_id = $row['dept_head_id'];
				$dept_id = $row['dept_id'];
			}
			
			//$head_dept_id = mysql_result($sql_result_id, 0);
			
			$query_head_email = "SELECT e_mail FROM personnel WHERE personnel_id = '$head_dept_id'";
			$sql_result_email = mysql_query($query_head_email, $uclit_dbConn) or die("Couldn't execute query.<p>$query_head_dept_email</p>");
			$head_dept_email = mysql_result($sql_result_email, 0);
			
			//check if you user is the head department himself
			if($head_dept_email == $help_email)
			{
				//respond only to the user because the user and head_dept are the same person
				$mail_to = $help_email;
				$sentmsgRespondUser = "true";
				$sentmsgRespondHead = "false";
			}
			else
			{
				//combine the user email and the head department email in one line 
				$mail_to = $help_email;
				
				if ($help_email != $head_dept_email)
				{
					$mail_to .= ",";
					$mail_to .= $head_dept_email;
				}			

				if ($dept_id == '35')
				{
					$mail_to .= ",bachpl@ucmail.uc.edu, wattsdc@ucmail.uc.edu";
				}
				if ($dept_id == '9')
				{
					$mail_to .= ",david.ball@uc.edu, braunlml@ucmail.uc.edu";
				}

				if ($dept_id == '13')
				{
					$mail_to .= ",sara.harper@uc.edu, elizabeth.meyer@uc.edu";
				}

				if ($dept_id == '9' or $dept_id == '45' or $dept_id == '42' or $dept_id == '35' or $dept_id == '31' or $dept_id == '20' or $dept_id == '19' or $dept_id == '7' or $dept_id == '10' or $dept_id == '8' or $dept_id == '12' or $dept_id == '13' or $dept_id == '15' or $dept_id == '17')
				{
					$mail_to .= ",cheryl.albrecht@uc.edu,irma.leckinger@uc.edu";
				}
				
				if ($dept_id == '3' or $dept_id == '33' or $dept_id == '43')
				{
					$mail_to .= ",dan.gottlieb@uc.edu";
				}
				
				if ($dept_id == '16')
				{
					$mail_to .= ",leslie.schick@uc.edu";
				}
	
				$sentmsgRespondUser = "true";
				$sentmsgRespondHead = "true";
			}
			$mail_subj = "Library Technology Services (Help Call Completed)";
			$mail_text = "We completed the following help call:\n$help_desc2\n\nResolution:\n$restext\n\nSend us an 
				email if you have any questions <libhelp@uc.edu>\nor drop us a help call at: 
				http://ul.uc.edu/uclit/protected/general/show_addhelp.php\nThanks a lot!\n\nLibrary Technology Services.";
			$mail_header = "From: libhelp@uc.edu\r\n";
			$mail_header .= "X-Mailer: server\r\n";
			$mail_header .= "Reply To: libhelp@uc.edu\r\n";
			$sendmail_param = "libhelp@uc.edu";
			mail("$mail_to","$mail_subj","$mail_text","$mail_header","-f$sendmail_param");
			//send to erma all completed help call
			mail("$erma","$mail_subj","Completed Help Call for:\n$help_desc2\n$restext\n\nEmail(s) sent to: 
				$mail_to","$mail_header","-f$sendmail_param");
		}
		else
		{
			$mail_to = $help_email;
			$mail_subj = "Library Technology Services (Help Call Completed)";
			$mail_text = "We completed the following help call:\n$help_desc2\n\nResolution:\n$restext\n\nSend us an 
				email if you have any questions <libhelp@uc.edu>\nor drop us a help call at: 
				http://ul.uc.edu/uclit/protected/general/show_addhelp.php\nThanks a lot!\n\nLibrary Technology Services.";
			$mail_header = "From: libhelp@uc.edu\r\n";
			$mail_header .= "X-Mailer: server\r\n";
			$mail_header .= "Reply To: libhelp@uc.edu\r\n";
			$sendmail_param = "libhelp@uc.edu";
			mail("$mail_to","$mail_subj","$mail_text","$mail_header","-f$sendmail_param");
			
			//send to erma all completed help call
			mail("$erma","$mail_subj","Completed Help Call for:\n$help_desc2\n$restext\n\nEmail(s) sent to: 
				$mail_to","$mail_header","-f$sendmail_param");

			$sentmsgRespondUser = "true";
		}
	}
	else
	{
		$dbConn = mysql_select_db('helpcalls', $helpConn) or die("Couldn't select database");
		//sql statement where completed box = 0
		$query= "UPDATE log 
				 SET Completed='0', Help_Desc='$desctext', Help_resolution='$restext', Help_Tech='$technician', date_completed='$today' 
				 WHERE help_id='$help_id'" ;
		$my_resultUpdate = mysql_query($query);
				//get technician email and notify him
		if($new_tech == "true")
		{
			$dbConn = mysql_select_db('uclit_db', $uclit_dbConn) or die("Couldn't select database");
			$last_name = explode(" ", $technician);
			
			$query_tech_email = "SELECT e_mail FROM personnel WHERE last_name = '$last_name[1]'";		
			$sql_tech_email = mysql_query($query_tech_email, $uclit_dbConn) or die("Couldn't execute query.<p>$query_tech_email</p>");
			while($row = mysql_fetch_array($sql_tech_email))
			{
				$tech_email = $row['e_mail'];
			}
			if($last_name[1] == "Johnson" && $last_name[0] == "Brian")
				$tech_email = "libhelp@uc.edu";
			elseif($last_name[1] =="Pons")
				$tech_email = "lisa.haitz@uc.edu";
			if($tech_email != "")
			{
				//echo "$tech_email<br />";
				$mail_to = $tech_email;
				$mail_subj = "Library Information Technology Services (New Help Call )";
				$mail_text = "You have new help call.  
					Please visit:\nhttp://ul.uc.edu/uclit/protected/itstaff/show_tech.php to review you help call.  
					Thanks a lot!\n\nLibrary Information Technology Services.";
				$mail_header = "From: libhelp@uc.edu\r\n";
				$mail_header .= "X-Mailer: server\r\n";
				$mail_header .= "Reply To: libhelp@uc.edu\r\n";
				$sendmail_param = "libhelp@uc.edu";
	
				mail("$mail_to","$mail_subj","$mail_text","$mail_header","-f$sendmail_param");
				$sentmsgRespondTech = "true";
			}
		}
	}
	//select database
	$db = mysql_select_db('helpcalls', $helpConn) or die("Couldn't select helpcalls database.");
	
	//run query sql statement		
	$result = @mysql_query($query, $helpConn) or die("Couldn't execute query.<p>$query</p>");
	
	if (!$result) 
	{
		echo"<P>Couldn't update record!";
		exit();
	} 
	 
	
	//sql to statement to log
	$sql_useraccess = "INSERT INTO HelpcallsUserAccessLog VALUES ('', '$suser', '$query', '$datetimenow')";
	//log user access
	@mysql_query($sql_useraccess, $helpConn);

	echo "
	<p>Your record has been updated.

	";
	if($sentmsgRespondUser == "true")
		echo "<br />An email was sent to: $help_email";
	if($sentmsgRespondHead == "true")
		echo "<br />An email was sent to department head: $mail_to";
	if($sentmsgRespondTech == "true")
		echo "<br />An email has notified the technician: $tech_email";

	echo "
	</p> 
	<br />
	<a href='http://ul.uc.edu/uclit/protected/itstaff/show_tech.php'>Return to Select a Tech</a>";
		
	include('/var/www/html/uclit/protected/common/css_login/css_foot2.php'); 
?>