/
videoanalytics_api.php
executable file
·175 lines (117 loc) · 3.53 KB
/
videoanalytics_api.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
<?php
/**
retorna un llistat amb les sessions disponibles
*/
function va_get_sessions(){
global $wpdb;
$options = get_option( 'videoanalytics_options' );
$table_name = $wpdb->prefix . $options['db_table'];
$sql="SELECT *,count(*) as t FROM $table_name group by rndk order by ti desc";
$posts = $wpdb->get_results($sql);
return $posts;
}
/**
retorna les dades d'una sessió en concret
*/
function va_session_get($session_id,$track=""){
global $wpdb;
$options = get_option( 'videoanalytics_options' );
$table_name = $wpdb->prefix . $options['db_table'];
$sql="SELECT * FROM $table_name where rndk=".$session_id;
if($track!="") $sql.=" and video='$track' ";
$sql.=" order by ta asc";
$posts = $wpdb->get_results($sql);
$res=array();
foreach($posts as $p){
//ta és el de l'acció
$p->ts= (new DateTime($p->ta))->getTimestamp();
unset($p->rndk);
unset($p->ti);
unset($p->ta);
$res[]=$p;
}
return $res;
}
/**
retorna un llistat amb tots els videos disponibles
*/
function va_get_videos($from="",$to=""){
global $wpdb;
$options = get_option( 'videoanalytics_options' );
$table_name = $wpdb->prefix . $options['db_table'];
if($from=="" && $to==""){
$sql="SELECT video,count(*) as t FROM $table_name group by video order by t desc";
}
$posts = $wpdb->get_results($sql);
return $posts;
}
/**
retorna totes les sessions d'un video concret
*/
function va_get_sessions_video($video){
global $wpdb;
$options = get_option( 'videoanalytics_options' );
$table_name = $wpdb->prefix . $options['db_table'];
$sql="SELECT * FROM $table_name WHERE video='$video' order by rndk, ta asc";
$posts = $wpdb->get_results($sql);
$res=array();
foreach($posts as $p){
//ta és el de l'acció
$p->ts= (new DateTime($p->ta))->getTimestamp();
unset($p->video);
unset($p->ti);
unset($p->ta);
$res[]=$p;
}
return $res;
}
/**
retorna els limits temporals de la serie
*/
function va_get_dates(){
}
//todo en un periode de temps
/**
crides a la api, retornen json
*/
function api($method,$params){
switch($method){
case "sessions_videos":
$posts=va_get_sessions_video($params['video']);
print json_encode($posts);
break;
case "session":
$session=va_session_get($params['rndk']);
print json_encode($session);
break;
}
}
/**
demanar durada video a vimeo
*/
//test
//$duration=videoanalytics_get_duration('06507_m2_exercici6');
//print "duration : ".$duration;
function videoanalytics_get_duration($video_name){
global $wpdb;
$options = get_option( 'videoanalytics_options' );
//primer mirar si existeix a la base de dades
$table_name = $wpdb->prefix . "videoanalytics_video";
$query="SELECT * FROM $table_name WHERE video='".$video_name."'";
$results=$wpdb->get_results($query);
if(count($results)>0){
return $results[0]->duration;
}
//en cas contrari demanar a vimeo i guardar resultat
//com sembla que no es pot demanar a vimeo s'estima amb aquesta query
$table_name_main = $wpdb->prefix . $options['db_table'];
$query="SELECT * FROM $table_name_main WHERE video='".$video_name."' and act='pausa' order by params desc";
$results=$wpdb->get_results($query);
if(count($results)>0){
$timestamp = date('Y-m-d G:i:s');
$duration=$results[0]->params;
$query="INSERT INTO $table_name (video,duration,updated) values ('$video_name',$duration, '$timestamp')";
$wpdb->query($query);
return $duration;
}
}