· 4 years ago · Mar 15, 2021, 12:28 AM
1<html>
2 <head>
3 <!--Load the AJAX API-->
4 <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
5<?php
6echo '<h1> Student emotions by classroom ID for week</h1> <h3>This should run once a week, at the same time</h3>';
7require 'sql.php';
8$connection = sql::connect();
9
10/*
11
12 Check get variable date to see date range
13 If nothing set or invalid value, use day
14
15*/
16
17if(isset($_GET['date']) && $_GET['date'] == "month")
18{
19 $date1 = "DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW();";
20 $date2 = "DATE_SUB(NOW(), INTERVAL 1 MONTH), NOW());";
21 $date3 = "Monthly";
22}
23elseif (isset($_GET['date']) && $_GET['date'] == "week")
24{
25 $date1 = "DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW();";
26 $date2 = "DATE_SUB(NOW(), INTERVAL 7 DAY), NOW());";
27 $date3 = "Weekly";
28}
29else
30{
31 $date1 = "DATE_SUB(NOW(), INTERVAL 1 DAY) AND NOW();";
32 $date2 = "DATE_SUB(NOW(), INTERVAL 1 DAY), NOW());";
33 $date3 = "Daily";
34}
35
36/*
37 Check get value of ID, default to 1 if not numeric
38
39 Will need better input check if using get, but will eventually phase out get for this script.
40*/
41if(is_numeric($_GET['ID']))
42{
43 $classroomID = $_GET['ID'];
44}
45else
46{
47 $classroomID = 1;
48}
49
50/*
51
52 Get scene information from database
53
54 Scene information then assigned to an array
55
56*/
57
58$scenes = $connection->prepare("SELECT * FROM Scenes");
59$scenes->execute();
60
61foreach($scenes as $s) // get scene names from database and assign to array
62{
63
64 $sceneTime[$s['sceneID']] = 0;
65 $sceneName[$s['sceneID']] = $s['sceneName'];
66}
67
68
69
70/*
71
72 Get students that in the desired input classroomID from database
73
74
75*/
76$students = $connection->prepare("SELECT Classrooms.classroomName, UsersClassrooms.userID, Classrooms.classroomID, Users.email FROM Classrooms
77 INNER JOIN UsersClassrooms on Classrooms.classroomID = UsersClassrooms.classroomID
78 INNER JOIN Users on UsersClassrooms.userID = Users.userID
79 WHERE Classrooms.classroomID = :classID
80 "); //Grab users info associated with that classroomID
81
82 $students->bindParam(":classID", $classroomID);
83 $students->execute();
84
85/*
86
87 Now cycle through each returned student in that clasroom
88
89*/
90foreach($students as $key => $st) //cycle though each student
91{
92 $score = 0; //total selscore
93 $totalTime = 0; //total time spent on every interaction
94
95 /*
96
97 For every student, grab their interactions
98
99 This uses $date1, which is either 3 values predefined within if/else if at the top of script
100
101 */
102
103 $interactions = $connection->prepare("SELECT Users.email, Users.age, Interactions.totalTime, Scenes.sceneName, Scenes.sceneSELMultiplier, Classrooms.classroomName, Teacher.email, Scenes.sceneID FROM Interactions
104 INNER JOIN UserInteractions ON UserInteractions.interactionID = Interactions.interactionID
105 INNER JOIN Users ON UserInteractions.userID = Users.userID
106 INNER JOIN Scenes ON UserInteractions.sceneID = Scenes.sceneID
107 INNER JOIN UsersClassrooms ON UserInteractions.userID = UsersClassrooms.userID
108 INNER JOIN Classrooms ON UsersClassrooms.ClassroomID = Classrooms.classroomID
109 INNER JOIN Users Teacher ON Teacher.userID = Classrooms.classroomTeacherID
110 WHERE Users.userID = :userID
111 AND Interactions.dateOfInteraction BETWEEN $date1"); //get each student's interactions from database
112 $interactions->bindParam(":userID", $st['userID']);
113 $interactions->execute();
114
115 /*
116
117 For every interaction that the student is associated with, all within date interval, figure out most viewed scene/model and SELscore
118
119 SELscore are calculated by summing the total time on each model, multiplied by the SELmultiplier defined in SQL
120
121 Most viewed model is calcuated by adding view times to an array of scene times, than gets the key of the max value to identify which scene
122
123 */
124
125 foreach($interactions as $result) //cycle through each interaction, adding to total time and increasing scene time
126 {
127 $totalTime += $result[2];
128 $sceneTime[$result[7]] += $result[2];
129 $score += ($result[4] * $result[2]);
130 }
131
132 $maxelement = 1; // used to get scene with most time.
133 echo '<h3>User ID: '.$st['userID'].'</h3>';
134
135 foreach($sceneTime as $key => $time) //get scene with max time
136 {
137 echo 'The user spent ' . $time . ' seconds on scene ' . $sceneName[$key] . '<br />';
138
139 if($time > $sceneTime[$maxelement])
140 {
141 $maxelement = $key;
142 }
143 }
144
145 echo '<br /> <br />';
146 echo 'Total time is: ' . $totalTime . '<br />';
147 echo 'Most used scene for this user is ' . $sceneName[$maxelement] . ' with a time of ' . $sceneTime[$maxelement].' seconds<br />'; //log most used time here, probably put in sql table?
148
149
150 $studentMostUsedScene[$st['userID']] = $maxelement; //save information to array
151 $studentMostUsedTime[$st['userID']] = $sceneTime[$maxelement];//save information to array
152
153 $studentScore[$st['userID']] = floatval($score);
154
155 for($x = 1; $x < count($sceneTime); $x++)
156 {
157 $sceneTime[$x] = 0; //reset scenetimes for next student
158 }
159 echo '<hr>';
160
161}
162echo '<hr><hr>';
163
164/*
165
166 Now to save these calculations in the database
167
168 Currently saving all monthly, weekly and daily values to same table with date ranges
169 May break it into 3 tables on time interval
170
171 Uses $date2 in sql query, predfined in an if/elseif on top of script
172*/
173$aggregates = $connection->prepare("INSERT INTO selScoresUsers$date3 (userID, score, emotion, startDate, endDate) VALUES (:userID, :score, :emotion, $date2");
174foreach($studentMostUsedScene as $key => $scene) //cycle through array of saved information.
175{
176
177 echo 'The user of ID: '. $key . ' was on this scene the most: ' . $sceneName[$scene] . ' with a total time of : ' . $studentMostUsedTime[$key].'<br />';
178 $aggregates->bindParam(":userID", $key, PDO::PARAM_INT);
179 $aggregates->bindParam(":score", $studentScore[$key]);
180 $aggregates->bindParam(":emotion", $scene, PDO::PARAM_INT);
181 //$aggregates->execute();
182}
183
184
185/*
186
187 Get most used emotion by using key() on most used scene array
188 Get class SEL score total by summing the student score array
189
190*/
191
192$most_frequent = key($studentMostUsedScene);
193$class_total = array_sum($studentScore);
194var_dump($studentMostUsedScene);
195
196foreach($studentMostUsedScene as $scene)
197{
198 $countOfScene[$scene] = 0;
199}
200foreach($studentMostUsedScene as $mskey => $scene)
201{
202 $countOfScene[$scene]++;
203
204}
205
206echo " <script>
207
208 // Load the Visualization API and the corechart package.
209 google.charts.load('current', {'packages':['corechart']});
210
211 // Set a callback to run when the Google Visualization API is loaded.
212 google.charts.setOnLoadCallback(drawChart);
213
214 // Callback that creates and populates a data table,
215 // instantiates the pie chart, passes in the data and
216 // draws it.
217 function drawChart() {
218
219 // Create the data table.
220 var data = new google.visualization.DataTable();
221 data.addColumn('string', 'Model/Scene');
222 data.addColumn('number', 'Number of Students');
223 data.addRows([";
224
225 $countval = 0;
226 foreach($countOfScene as $key => $value)
227 {
228 if($countval > 0)
229 {
230 echo ",";
231 }
232 echo "['".$sceneName[$key] ."', ".floatval($value)."]";
233 $countval++;
234 }
235
236echo "]);
237
238 // Set chart options
239 var options = {'title':'How Much Pizza I Ate Last Night',
240 'width':400,
241 'height':300};
242
243 // Instantiate and draw our chart, passing in some options.
244 var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
245 chart.draw(data, options);
246 }
247 </script>
248 </head>";
249
250
251$classUpdate = $connection->prepare("INSERT INTO selScoresClassroom$date3 (classroomID, score, emotion, startDate, endDate) VALUES (:classroomID, :score, :emotion, $date2");
252$classUpdate->bindParam(":classroomID", $classroomID, PDO::PARAM_INT);
253$classUpdate->bindParam(":score", $class_total);
254$classUpdate->bindParam(":emotion", $most_frequent, PDO::PARAM_INT);
255//$classUpdate->execute();
256
257echo '<hr>';
258echo 'Reporting a score of '. $class_total .' and a most viewed emotion of ' . $sceneName[$most_frequent] . ' for the class of ID ' . $classroomID;
259
260 ?>
261 <body>
262 <!--Div that will hold the pie chart-->
263 <div id="chart_div"></div>
264 </body>
265</html>