connect_errno) {
die('Failed to connect to the database server - ' . $mysqli->connect_error);
}
#
# The db querys
#
$result = $mysqli->query('SET time_zone="+00:00"');
if (!$result) {
die('ERROR - TZ Statement');
}
// construct a new query for the data - first the visits and page views as a cumulative total by month/year
$query = 'SELECT
server_time,
cumulative_visits +308,
cumulative_page_views +154
FROM (
WITH monthlydata AS (
SELECT
UNIX_TIMESTAMP(DATE_FORMAT(DATE(server_time), "%Y-%m-01")) AS server_time,
COUNT(DISTINCT(idvisit)) AS visits,
COUNT(idvisitor) AS page_views
FROM
mtys_log_link_visit_action
GROUP BY
YEAR(server_time),
MONTH(server_time)
)
SELECT
server_time,
SUM(visits) over (ORDER BY server_time) AS cumulative_visits,
SUM(page_views) over (ORDER BY server_time) AS cumulative_page_views
FROM monthlydata) AS cumulativeData';
$result = $mysqli->query($query);
if (!$result) {
die('ERROR - Bad Select Statement: ' . $mysqli->error . '
' . $query);
}
// import the rows and put the data into arrays
while($row = $result->fetch_array()) {
$valCumVisits[] = array((float)$row[0]*1000, (float)$row[1]);
$valCumPageViews[] = array((float)$row[0]*1000, (float)$row[2]);
}
// construct a new query for the data - distinct count of visitors as a cumulative total by month/year
$query = 'SELECT
UNIX_TIMESTAMP(DATE_FORMAT(DATE(fst_date), "%Y-%m-01")) AS server_time,
MAX(cum_visitors)
FROM (
SELECT
*, COUNT(*) OVER (ORDER BY fst_date) cum_visitors
FROM (
SELECT idvisitor, MIN(server_time) fst_date FROM mtys_log_link_visit_action GROUP BY idvisitor
) t1
) t2
GROUP BY MONTH(fst_date), YEAR(fst_date)
ORDER BY fst_date';
$result = $mysqli->query($query);
if (!$result) {
die('ERROR - Bad Select Statement: ' . $mysqli->error . '
' . $query);
}
// import the rows and put the data into arrays
while($row = $result->fetch_array()) {
$valCumVisitors[] = array((float)$row[0]*1000, (float)$row[1]);
}
// close connection
$mysqli->close();
$rows = array($valCumVisits, $valCumPageViews, $valCumVisitors);
header('Content-type: text/json');
header('Cache-Control: private');
echo json_encode($rows);
?>