public function getAverageResponseTi me account_id from_date null to_d

 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
public function getAverageResponseTime($account_id, $from_date=null, $to_date=null) {
$sql = "SELECT avg(timestampdiff(MINUTE, surveys.created, responses.created)) as avg_time "
. "FROM responses "
. "JOIN survey_questions on (question_id = survey_questions.id) "
. "JOIN surveys on (survey_id=surveys.id) AND surveys.is_sent >0 "
. "JOIN users on (user_id = users.id) "
. "JOIN accounts on (account_id = accounts.id)";
$bind = array();
$condition_str = "WHERE ";
if($account_id > 0){
$sql .= "$condition_str accounts.id = ?";
array_push($bind, $account_id);
$condition_str = " AND ";
}
if ($from_date != null) {
$sql .= $condition_str." surveys.created >= ?";
array_push($bind, $from_date->format('Y-m-d 23:59:59'));
$condition_str = " AND ";
}
if ($to_date != null) {
$sql .= $condition_str."surveys.created <= ?";
array_push($bind, $to_date->format('Y-m-d 23:59:59'));
}
$stmt = $this->db->query($sql,$bind);
$result = $stmt->fetch();
return $result['avg_time'];
}