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 - return visits as cumulative total by month/year
$query = 'SELECT
server_time,
cumulative_return_visits,
cumulative_return_page_views,
cumulative_return_visitors
FROM (
WITH monthlydata AS (
SELECT
UNIX_TIMESTAMP(DATE_FORMAT(DATE(visit_last_action_time), "%Y-%m-01")) AS server_time,
COUNT(DISTINCT(idvisit)) AS return_visits,
SUM(visit_total_actions) AS return_page_views,
COUNT(DISTINCT(idvisitor)) AS return_visitors
FROM
mtys_log_visit
WHERE visitor_returning <> 0
GROUP BY
YEAR(visit_last_action_time),
MONTH(visit_last_action_time)
)
SELECT
server_time,
SUM(return_visits) OVER (ORDER BY server_time) AS cumulative_return_visits,
SUM(return_page_views) OVER (ORDER BY server_time) AS cumulative_return_page_views,
SUM(return_visitors) OVER (ORDER BY server_time) AS cumulative_return_visitors
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()) {
$valCumReturnVisits[] = array((float)$row[0]*1000, (float)$row[1]);
$valCumReturnPageViews[] = array((float)$row[0]*1000, (float)$row[2]);
$valCumReturnVisitors[] = array((float)$row[0]*1000, (float)$row[3]);
}
// close connection
$mysqli->close();
$rows = array($valCumReturnVisits, $valCumReturnPageViews, $valCumReturnVisitors);
header('Content-type: text/json');
header('Cache-Control: private');
echo json_encode($rows);
?>