server.php 5.46 KB
Newer Older
1 2 3 4 5 6 7
<?php
require_once 'dbconnection.php';
global $conn;
$ID = $_REQUEST['ID'];
date_default_timezone_set("Europe/Amsterdam");
$time = date("H:i:sa");
$date = date("Y-m-d");
8
$last_id;
9

10 11
error_log("\nserver.php");

S.Holzhauer's avatar
S.Holzhauer committed
12
//if the ID is the server ID
13
if ($ID == "IRPS001" ){
S.Holzhauer's avatar
S.Holzhauer committed
14
	//select all values from Camera table to check all elder activities
15 16 17
	$sql = "SELECT * 
			FROM Camera;";
	$result = $conn->query($sql);
S.Holzhauer's avatar
S.Holzhauer committed
18
	//loop every elder.
19 20 21
	while ($row = $result->fetch_assoc()){
		$OuderID = $row["AlleenstaandeOuder_OuderID"];
		
S.Holzhauer's avatar
S.Holzhauer committed
22 23 24 25 26
		/*select everything from this elder in the activity table
		 * with status 0 (false)
		 * starttime earlier then current time
		 * and endtime earlier or the same. Other activities haven't started yet.
		 */
27 28 29
		$sql = "SELECT * 
				FROM Activiteiten 
				WHERE OuderID='$OuderID' 
S.Holzhauer's avatar
S.Holzhauer committed
30
				AND StartTime < '$time'
31 32 33
				AND EndTime <= '$time'
				AND Status = '0';";
		$result = $conn->query($sql);
S.Holzhauer's avatar
S.Holzhauer committed
34
		//loop trough every activity
S.Holzhauer's avatar
S.Holzhauer committed
35
		while ($row = $result->fetch_assoc()){
36 37 38 39
			$starttime = $row['StartTime'];
			$endtime = $row['EndTime'];
			$Activity = $row['Activity'];
			$daydate = $row['DayDate'];
S.Holzhauer's avatar
S.Holzhauer committed
40
			//if the movement should be 0 and the status is 0 then it's a success scenario so status is updated to 1 (true/succes)
41 42 43 44 45 46 47 48 49 50
			if ($row['Movement'] == 0){
				$sql = "UPDATE Activiteiten
						SET Status = '1'
						WHERE StartTime = '$starttime'
						AND EndTime = '$endtime'
						AND Activity = '$Activity'
						AND DayDate = '$daydate'
						AND OuderID = '$OuderID';";
				$conn->query($sql);
			}
S.Holzhauer's avatar
S.Holzhauer committed
51
			//if the movement shoudl be 1 but status is 0 there is a fail
52 53 54 55 56 57
			if ($row['Movement'] == 1){
				$message = "The scheduled activity:
							\n Activity: '$Activity'
							\n StartTime: '$starttime'
							\n EndTime: '$endtime'
							\n Did not register today ('$daydate'). We recommend taking action.";
S.Holzhauer's avatar
S.Holzhauer committed
58 59 60
				//the notification is added to the database
				$sql = "INSERT INTO Notificatie (`Date`,`Time`,`Message`, `Title`)
						VALUES ('$daydate','$time','$message','Failed Activity';";
61 62 63 64
				$result = $conn->query($sql);
				if ($result === TRUE) {
					$last_id = $conn->insert_id;
					
S.Holzhauer's avatar
S.Holzhauer committed
65 66 67 68 69 70 71
					//see what accounts are connected to this elder.
					$sql = "SELECT Account_AccountID FROM Account_has_AlleenstaandeOuder WHERE AlleenstaandeOuder_OuderID='$OuderID'";
					$result = $conn->query($sql);
					if ($result->num_rows > 0){
						while($result && ($row = $result->fetch_assoc()))
						{
							$AccountID=$row["Account_AccountID"];
S.Holzhauer's avatar
S.Holzhauer committed
72
						
S.Holzhauer's avatar
S.Holzhauer committed
73 74
							//Get the email adres connected to the account
							$sql = "SELECT Emailadres FROM Account WHERE AccountID='$AccountID'";
S.Holzhauer's avatar
S.Holzhauer committed
75
							$result = $conn->query($sql);
S.Holzhauer's avatar
S.Holzhauer committed
76 77 78 79 80 81 82
							if ($result->num_rows > 0) {
								// output data of each row
								$row = $result->fetch_assoc();
								//Binding the return value to $Email
								$Email=$row["Emailadres"];
							} else {
								echo "0 results";
S.Holzhauer's avatar
S.Holzhauer committed
83
							}
S.Holzhauer's avatar
S.Holzhauer committed
84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
								
							//Get the Name connected to the account
							$sql = "SELECT Voorletters,Achternaam FROM Account WHERE AccountID='$AccountID'";
							$result = $conn->query($sql);
							if ($result->num_rows > 0) {
								// output data of each row
								$row = $result->fetch_assoc();
								//Binding the return to $AcName
								$AcName=$row["Voorletters"] . " " . $row["Achternaam"];
							} else {
								echo "0 results";
							}
								
							//Connect the previous made notification to the appropriat accounts
							$sql = "INSERT INTO Account_has_Notificatie (Notificatie_NotificatieID,Account_AccountID,Status) 
									VALUES ('$last_id','$AccountID',0)";
							$result = $conn->query($sql);
S.Holzhauer's avatar
S.Holzhauer committed
101
							
S.Holzhauer's avatar
S.Holzhauer committed
102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123
							//see how many notifications an account already has.
							$sql = "SELECT *
									FROM Account_has_Notificatie
									WHERE Account_AccountID = $AccountID;";
							$result = $conn->query($sql);
							//if there are more then 1 notifications this means that multiple activities have failed and thus a email is send.
							if ($result->num_rows > 1){
								$sql = "INSERT INTO Berichten (Time,Message,Subject,Date) 
										VALUES ('$time','$message','Second Failed Activity','$date')";
								$result = $conn->query($sql);
								if ($result === TRUE) {
									$last_id = $conn->insert_id;
								}
								//sending the mail, $Email is initialized previously, $Msg is created above & $headers changes the sender information to irp@stienos.nl.
								$headers = 'From: <activityalert@irp.nl>';
								mail($Email,"Camera Alert","Beste $AcName $message",$headers);
								
								$sql = "INSERT INTO Account_has_Berichten (Berichten_BerichtID,Account_AccountID,Status)
										VALUES ($last_id,$AccountID,0";
								$conn-query($sql);
							}
								
S.Holzhauer's avatar
S.Holzhauer committed
124
						}
125 126 127 128 129 130
					}
				}
			}
		}
	}
}
S.Holzhauer's avatar
S.Holzhauer committed
131
//if the ID is not null it comes from a camera and thus the status for the current event is updated.
132 133 134 135 136
if ($ID != NULL){
	$sql = "SELECT AlleenstaandeOuder_OuderID
			FROM Camera
			WHERE CameraID = '$ID';";
	$result = $conn->query($sql);
S.Holzhauer's avatar
S.Holzhauer committed
137 138 139 140
	//if no camera with the given ID is found a error entrie is created.
	if ($result == FALSE){
		error_log("No camera with '$ID' is registerd.");
	}
S.Holzhauer's avatar
S.Holzhauer committed
141
	$row = $result->fetch_assoc();
142 143 144 145 146 147 148 149 150
	$OuderID = $row['AlleenstaandeOuder_OuderID'];
	
	$sql = "UPDATE Activiteiten
			SET Status = 1
			WHERE StartTime <= '$time'
			AND EndTime >= '$time'
			AND DayDate = '$date';";
	$conn->query($sql);
}
S.Holzhauer's avatar
S.Holzhauer committed
151
//if the ID is null error_log
152 153 154 155
if ($ID == NULL){
	error_log("unrecognized call to server.php");
}
?>