/
statistics.php
124 lines (108 loc) · 4.78 KB
/
statistics.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
<?php
namespace Concrete\Block\Form;
use Concrete\Core\Support\Facade\Application;
class Statistics
{
public static $sortChoices = ['newest' => 'created DESC', 'chrono' => 'created'];
/**
* Gets the total number of submissions.
*
* @param string $date set to a specific day (eg '2014-09-14') to retrieve the submissions in that day
* @param string $dateTimezone The timezone of the $date parameter (acceptable values: 'user', 'system', 'app' or any valid PHP timezone identifier)
*
* @return int
*/
public static function getTotalSubmissions($date = null, $dateTimezone = 'user')
{
if ($date) {
return static::getTotalSubmissionsBetween("{$date} 00:00:00", "{$date} 23:59:59", $dateTimezone);
}
return static::getTotalSubmissionsBetween();
}
/**
* Gets the total number of submissions in specific date/time ranges.
*
* @param string|int|\DateTime $fromDate The start of the period (if empty: from ever). Inclusive. Example: '2014-09-14 08:00:00'.
* @param string|int|\DateTime $toDate The end of the period (if empty: for ever). Inclusive. Example: '2014-09-14 08:00:00'.
* @param string $dateTimezone The timezone of the $dateFrom and $dateTo parameter (acceptable values: 'user', 'system', 'app' or any valid PHP timezone identifier)
* @param mixed $datesTimezone
*
* @return number
*/
public static function getTotalSubmissionsBetween($fromDate = null, $toDate = null, $datesTimezone = 'user')
{
$app = Application::getFacadeApplication();
$db = $app->make('database/connection');
$dh = $app->make('helper/date');
// @var $dh \Concrete\Core\Localization\Service\Date
if ($fromDate) {
$fromDate = $dh->toDB($fromDate, $datesTimezone);
}
if ($toDate) {
$toDate = $dh->toDB($toDate, $datesTimezone);
}
$where = '';
$q = [];
if ($fromDate && $toDate) {
$where = ' where created between ? and ?';
$q[] = $fromDate;
$q[] = $toDate;
} elseif ($fromDate) {
$where = ' where created >= ?';
$q[] = $fromDate;
} elseif ($toDate) {
$where = ' where created <= ?';
$q[] = $toDate;
}
$count = $db->fetchColumn('select count(asID) from btFormAnswerSet' . $where, $q);
return empty($count) ? 0 : (int) $count;
}
public static function loadSurveys($MiniSurvey)
{
$app = Application::getFacadeApplication();
$db = $app->make('database/connection');
$dh = $app->make('date');
$now = $dh->getOverridableNow();
return $db->query('SELECT s.* FROM ' . $MiniSurvey->btTable . ' AS s, Blocks AS b, BlockTypes AS bt
WHERE s.bID=b.bID AND b.btID=bt.btID AND bt.btHandle="form" AND EXISTS (
SELECT 1 FROM CollectionVersionBlocks cvb
INNER JOIN CollectionVersions cv ON cvb.cID=cv.cID AND cvb.cvID=cv.cvID AND 1=cv.cvIsApproved AND (cv.cvPublishDate IS NULL OR cv.cvPublishDate <= ?) AND (cv.cvPublishEndDate IS NULL OR cv.cvPublishEndDate >= ?)
INNER JOIN Pages p ON cv.cID = p.cID
WHERE cvb.bID=s.bID AND p.cIsActive=1
)', [$now, $now]);
}
public static function buildAnswerSetsArray($questionSet, $orderBy = '', $limit = '')
{
$app = Application::getFacadeApplication();
$db = $app->make('database/connection');
if ((strlen(trim($limit)) > 0) && (!strstr(strtolower($limit), 'limit'))) {
$limit = ' LIMIT ' . $limit;
}
if ((strlen(trim($orderBy)) > 0) && array_key_exists($orderBy, self::$sortChoices)) {
$orderBySQL = self::$sortChoices[$orderBy];
} else {
$orderBySQL = self::$sortChoices['newest'];
}
//get answers sets
$sql = 'SELECT * FROM btFormAnswerSet AS aSet ' .
'WHERE aSet.questionSetId=' . $questionSet . ' ORDER BY ' . $orderBySQL . ' ' . $limit;
$answerSetsRS = $db->query($sql);
//load answers into a nicer multi-dimensional array
$answerSets = [];
$answerSetIds = [0];
while ($answer = $answerSetsRS->fetch()) {
//answer set id - question id
$answerSets[$answer['asID']] = $answer;
$answerSetIds[] = $answer['asID'];
}
//get answers
$sql = 'SELECT * FROM btFormAnswers AS a WHERE a.asID IN (' . implode(',', $answerSetIds) . ')';
$answersRS = $db->query($sql);
//load answers into a nicer multi-dimensional array
while ($answer = $answersRS->fetch()) {
//answer set id - question id
$answerSets[$answer['asID']]['answers'][$answer['msqID']] = $answer;
}
return $answerSets;
}
}