WebSVN – cheapmusic – /www/seopanel/controllers/analytics.ctrl.php

<?php

/***************************************************************************
 *   Copyright (C) 2009-2011 by Geo Varghese(www.seopanel.in)      *
 *   sendtogeo@gmail.com                                                                                                   *
 *                                                                         *
 *   This program is free software; you can redistribute it and/or modify  *
 *   it under the terms of the GNU General Public License as published by  *
 *   the Free Software Foundation; either version 2 of the License, or     *
 *   (at your option) any later version.                                   *
 *                                                                         *
 *   This program is distributed in the hope that it will be useful,       *
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
 *   GNU General Public License for more details.                          *
 *                                                                         *
 *   You should have received a copy of the GNU General Public License     *
 *   along with this program; if not, write to the                         *
 *   Free Software Foundation, Inc.,                                       *
 *   59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.             *
 ***************************************************************************/

// include google api module
include_once(SP_CTRLPATH . "/googleapi.ctrl.php");

// class defines all google analytics api controller functions
class AnalyticsController extends GoogleAPIController {
        
    var $spTextGA;
    var $metrics;
        var $metricList;
        var $defaultMetricName = "users";
        var $dimensionName = "source";
        
        function __construct() {
                parent::__construct();
                $this->spTextGA = $this->getLanguageTexts('analytics', $_SESSION['lang_code']);
                $this->set('spTextGA', $this->spTextGA);
                $this->metrics = array(
                    'users' => $this->spTextGA['Users'],
                    'newUsers' => $this->spTextGA['New Users'],
                    'sessions' => $this->spTextGA['Sessions'],
                    'bounceRate' => $this->spTextGA['Bounce Rate'],
                    'avgSessionDuration' => $this->spTextGA['Avg. Session Duration'],
                    'goalCompletionsAll' => $this->spTextGA['Goal Completions'],
                );
                
                
                $this->set('metricColList', $this->metrics);
                $this->metricList = array_keys($this->metrics);
        }
        
        /*
         * function to get analytics query result
         */
        function getAnalyticsResults($userId, $VIEW_ID, $startDate, $endDate) {
                $result = array('status' => false);
                
                if (empty($VIEW_ID)) {
                    $result['msg'] = $this->spTextGA['view_id_not_found_error'];
                    $alertCtler = new AlertController();
                    $alertInfo = array(
                        'alert_subject' => $this->spTextGA['view_id_not_found_error'],
                        'alert_message' => "",
                        'alert_url' => SP_WEBPATH ."/admin-panel.php",
                        'alert_type' => "danger",
                        'alert_category' => "reports",
                    );
                    $alertCtler->createAlert($alertInfo, $userId);
                    return $result;
                }
                
                try {
                        
                $client = $this->getAuthClient($userId);
                
                // check whether client created successfully
                if (!is_object($client)) {
                    $result['msg'] = $client;
                    return $result;
                }
                
                $analytics = new Google_Service_AnalyticsReporting($client);
                
                // Create the DateRange object.
                $dateRange = new Google_Service_AnalyticsReporting_DateRange();
                $dateRange->setStartDate($startDate);
                $dateRange->setEndDate($endDate);
                
                // Create the Metrics object list
                $metricObjList = [];
                foreach ($this->metricList as $metricName) {
                        $sessions = new Google_Service_AnalyticsReporting_Metric();
                        $sessions->setExpression("ga:$metricName");
                        $sessions->setAlias($metricName);
                        $metricObjList[] = $sessions;
                }
                
                // Create the dimension.
                $dimension = new Google_Service_AnalyticsReporting_Dimension();
                $dimension->setName("ga:$this->dimensionName");
                
                // Create the Ordering.                 
                $ordering = new Google_Service_AnalyticsReporting_OrderBy();
                $ordering->setFieldName("ga:$this->defaultMetricName");
                $ordering->setOrderType("VALUE");
                $ordering->setSortOrder("DESCENDING");
                
                // Create the ReportRequest object.
                $request = new Google_Service_AnalyticsReporting_ReportRequest();
                $request->setViewId($VIEW_ID);
                $request->setDateRanges($dateRange);
                $request->setMetrics($metricObjList);
                $request->setDimensions(array($dimension));
                $request->setOrderBys($ordering);
                
                $body = new Google_Service_AnalyticsReporting_GetReportsRequest();
                $body->setReportRequests( array( $request) );
                $res = $analytics->reports->batchGet( $body );                  
                $resultList = $this->formatResult($res);
                
                $result['status'] = true;
                $result['resultList'] = $resultList;                    
                } catch (Exception $e) {
                    $err = $e->getMessage();
                    $result['msg'] = "Error: search query analytics - $err";
                }
                
                return $result;
                
        }
        
        function formatMetricValue($value, $metricName) {
                
                if ($metricName == "bounceRate") {
                        $value = round($value, 2);
                }
                        
                if ($metricName == "avgSessionDuration") {
                        $value = round(($value/60), 2);
                }
                
                return $value;
        }
        
        function formatResult($reports) {
                $resultList = array();
                
                // loop through the reports
                for ( $reportIndex = 0; $reportIndex < count( $reports ); $reportIndex++ ) {
                        $report = $reports[ $reportIndex ];
                        
                        // get total value
                        $totals = $report->getData()->getTotals();
                        $values = $totals[0]->getValues();
                        $resultList['total'] = [];
                        foreach ($this->metricList as $i => $metricName) {
                                $resultList['total'][$metricName] = $this->formatMetricValue($values[$i], $metricName);
                        }
                        
                        // get dimension type value
                        $rows = $report->getData()->getRows();
                        for ( $rowIndex = 0; $rowIndex < count($rows); $rowIndex++) {
                                $row = $rows[$rowIndex];
                                $dimensions = $row->getDimensions();
                                $metrics = $row->getMetrics();
                                $values = $metrics[0]->getValues();
                                
                                // find metric values
                                $resultList[$dimensions[0]] = [];
                                foreach ($this->metricList as $i => $metricName) {
                                        $resultList[$dimensions[0]][$metricName] = $this->formatMetricValue($values[$i], $metricName);
                                }
                        }
                }
                
                return $resultList;
        }
        
        function getAnalyticsSourceList() {
            $sourceList = [];
            $list = $this->dbHelper->getAllRows("analytic_sources");
            foreach ($list as $listInfo) {
                $sourceList[$listInfo['source_name']] = $listInfo['id'];
            }
            
            return $sourceList;
        }
        
        function generateSource($sourceName) {
            $sourceId = false;
            if ($this->dbHelper->insertRow("analytic_sources", array("source_name" => $sourceName))) {
                $sourceId = $this->db->getMaxId("analytic_sources");
            }
            
            return $sourceId;
        }
        
        /*
         * function to store website results
         */
        function storeWebsiteAnalytics($websiteId, $reportDate) {
                $websiteId = intval($websiteId);
                $websiteCtrler = new WebsiteController();
                $websiteInfo = $websiteCtrler->__getWebsiteInfo($websiteId);
                
                // query results from api and verify no error occured
                $result = $this->getAnalyticsResults($websiteInfo['user_id'], $websiteInfo['analytics_view_id'], $reportDate, $reportDate);
                if ($result['status']) {
                    $sourceList = $this->getAnalyticsSourceList();
                                
                        // loop through the result list
                        foreach ($result['resultList'] as $sourceName => $reportInfo) {
                            
                            // generate source list, if not set it yet
                            if (!isset($sourceList[$sourceName])) {
                                $sourceId = $this->generateSource($sourceName);
                            } else {
                                $sourceId = $sourceList[$sourceName];
                            }
        
                            if (!empty($sourceId)) {
                                $this->insertWebsiteAnalytics($websiteId, $sourceId, $reportInfo, $reportDate);
                            } else {
                                $result['msg'] .= "Error: Analytics source id not found";
                            }
                        }
                }
        
                return $result;
        
        }
        
        /*
         * function to insert website analytics
         */
        function insertWebsiteAnalytics($websiteId, $sourceId, $reportInfo, $resultDate, $clearExisting = true) {
                $websiteId = intval($websiteId);
                $sourceId = intval($sourceId);
                $resultDate = addslashes($resultDate);
        
                if ($clearExisting) {
                        $whereCond = "website_id=$websiteId and report_date='$resultDate' and source_id='$sourceId'";
                        $this->dbHelper->deleteRows('website_analytics', $whereCond);
                }
                
                $reportInfo['website_id'] = $websiteId;
                $reportInfo['source_id'] = $sourceId;
                $reportInfo['report_date'] = $resultDate;
                $this->dbHelper->insertRow('website_analytics', $reportInfo);
        }

        // func to show quick checker
        function viewQuickChecker($searchInfo='') {     
                $userId = isLoggedIn();
                $websiteController = New WebsiteController();
                $websiteList = $websiteController->__getAllWebsites($userId, true);
                $this->set('websiteList', $websiteList);
                $websiteId = empty ($searchInfo['website_id']) ? $websiteList[0]['id'] : intval($searchInfo['website_id']);
                $this->set('websiteId', $websiteId);
                $this->set('fromTime', date('Y-m-d', strtotime('-1 days')));
                $this->set('toTime', date('Y-m-d'));
                $this->render('analytics/quick_checker');
        }

        // func to do quick report
        function doQuickChecker($searchInfo = '') {
        
                if (!empty($searchInfo['website_id'])) {
                        $websiteId = intval($searchInfo['website_id']);
                        $websiteController = New WebsiteController();
                        $websiteInfo = $websiteController->__getWebsiteInfo($websiteId);
                        $this->set('websiteInfo', $websiteInfo);                        
                        
                        if (!empty($websiteInfo['url'])) {
                                $reportStartDate = !empty($searchInfo['from_time']) ? $searchInfo['from_time'] : date('Y-m-d', strtotime('-1 days'));
                                $reportEndDate = !empty($searchInfo['to_time']) ? $searchInfo['to_time'] : date('Y-m-d');
                                                                
                                // query results from api and verify no error occured
                                $result = $this->getAnalyticsResults($websiteInfo['user_id'], $websiteInfo['analytics_view_id'], $reportStartDate, $reportEndDate);
                                        
                                // if status is success
                                if ($result['status']) {
                                        $websiteReport = array_shift($result['resultList']);
                                        $sourceReport = $result['resultList'];
                                        $this->set('websiteReport', $websiteReport);
                                        $this->set('sourceReport', $sourceReport);
                                        
                                        $this->set('searchInfo', $searchInfo);
                                        $this->render('analytics/quick_checker_results');
                                        return true;
                                        
                                }
                        }
                } 
                
                $errorMsg = !empty($result['msg']) ? $result['msg'] : "Internal error occured while accessing webmaster tools."; 
                showErrorMsg($errorMsg);                
        }

        // function check whether analytics reports already saved
        function isReportsExists($websiteId, $resultDate) {
                $websiteId = intval($websiteId);
                $resultDate = addslashes($resultDate);
                $whereCond = "website_id=$websiteId and report_date='$resultDate'";
                $info = $this->dbHelper->getRow("website_analytics", $whereCond, "website_id");
                return !empty($info['website_id']) ? true : false;
        }
        
        # func to show analytics report summary
        function viewAnalyticsSummary($searchInfo = '', $summaryPage = false, $cronUserId=false) {
            
            $userId = !empty($cronUserId) ? $cronUserId : isLoggedIn();
            $this->set('summaryPage', $summaryPage);
            $this->set('searchInfo', $searchInfo);
            $this->set('cronUserId', $cronUserId);
            
            $exportVersion = false;
            switch($searchInfo['doc_type']){
                
                case "export":
                    $exportVersion = true;
                    $exportContent = "";
                    break;
                    
                case "pdf":
                    $this->set('pdfVersion', true);
                    break;
                    
                case "print":
                    $this->set('printVersion', true);
                    break;
            }
            
            $fromTime = !empty($searchInfo['from_time']) ? addslashes($searchInfo['from_time']) : date('Y-m-d', strtotime('-2 days'));
            $toTime = !empty($searchInfo['to_time']) ? addslashes($searchInfo['to_time']) : date('Y-m-d', strtotime('-1 days'));
            $this->set('fromTime', $fromTime);
            $this->set('toTime', $toTime);
            
            $websiteController = New WebsiteController();
            $wList = $websiteController->__getAllWebsites($userId, true);
            $websiteList = [];
            foreach ($wList as $wInfo) {
                $websiteList[$wInfo['id']] = $wInfo;
            }
            
            $websiteList = count($websiteList) ? $websiteList : array(0);
            $this->set('websiteList', $websiteList);
            $websiteId = intval($searchInfo['website_id']);
            $this->set('websiteId', $websiteId);
            
            // to find order col
            if (!empty($searchInfo['order_col'])) {
                $orderCol = $searchInfo['order_col'];
                $orderVal = getOrderByVal($searchInfo['order_val']);
            } else {
                $orderCol = "users";
                $orderVal = 'DESC';
            }
            
            $this->set('orderCol', $orderCol);
            $this->set('orderVal', $orderVal);
            $scriptName = $summaryPage ? "archive.php" : "analytics.php";
            $scriptPath = SP_WEBPATH . "/$scriptName?sec=viewAnalyticsSummary&website_id=$websiteId";
            $scriptPath .= "&from_time=$fromTime&to_time=$toTime&search_name=" . $searchInfo['search_name'];
            $scriptPath .= "&order_col=$orderCol&order_val=$orderVal&report_type=analytics-reports";
            
            $conditions = !empty($searchInfo['search_name']) ? " and k.source_name like '%".addslashes($searchInfo['search_name'])."%'" : "";
            
            // set website id to get exact keywords of a user
            if (!empty($websiteId)) {
                $conditions .= " and r.website_id=$websiteId";
            } else {
                $conditions .= " and r.website_id in (".implode(',', array_keys($websiteList)).")";
            }
            
            $analyticsCols = implode(",", array_keys($this->metrics));      
            $sql = "select k.id,k.source_name,r.website_id,$analyticsCols 
            from analytic_sources k, website_analytics r 
            where k.id=r.source_id $conditions and r.report_date='$toTime'
            order by " . addslashes($orderCol) . " " . addslashes($orderVal);
            
            if ($orderCol != 'users') $sql .= ", users";
            
            // pagination setup, if not from cron job email send function, pdf and export action
            if (!in_array($searchInfo['doc_type'], array("pdf", "export"))) {
                $this->db->query($sql, true);
                $this->paging->setDivClass('pagingdiv');
                $this->paging->loadPaging($this->db->noRows, SP_PAGINGNO);
                $pagingDiv = $this->paging->printPages($scriptPath, '', 'scriptDoLoad', 'content', "");
                $this->set('pagingDiv', $pagingDiv);
                $this->set('pageNo', $searchInfo['pageno']);
                $sql .= " limit ".$this->paging->start .",". $this->paging->per_page;
            }
            
            # set report list
            $baseReportList = $this->db->select($sql);
            $this->set('baseReportList', $baseReportList);
            $this->set('colList', $this->colList);
            
            // if keywords existing
            if (!empty($baseReportList)) {
                
                $sourceIdList = array();
                foreach ($baseReportList as $info) {
                    $sourceIdList[] = $info['id'];
                }
                
                $sql = "select k.id,k.source_name,r.website_id, $analyticsCols
                        from analytic_sources k, website_analytics r where k.id=r.source_id
                        $conditions and r.report_date='$fromTime'";
                $sql .= " and k.id in(" . implode(",", $sourceIdList) . ")";
                $reportList = $this->db->select($sql);
                $compareReportList = array();
                
                foreach ($reportList as $info) {
                    $compareReportList[$info['website_id']][$info['id']] = $info;
                }
                
                $this->set('compareReportList', $compareReportList);
            }
            
            if ($exportVersion) {
                $spText = $_SESSION['text'];
                $reportHeading =  $this->spTextTools['Website Analytics Summary']."($fromTime - $toTime)";
                $exportContent .= createExportContent( array('', $reportHeading, ''));
                $exportContent .= createExportContent( array());
                $headList = array($spText['common']['Website'], $spText['common']['Source']);
                
                $pTxt = str_replace("-", "/", substr($fromTime, -5));
                $cTxt = str_replace("-", "/", substr($toTime, -5));
                foreach ($this->metrics as $colKey => $colLabel) {
                    if ($colKey == 'name') continue;
                    $headList[] = $colLabel . "($pTxt)";
                    $headList[] = $colLabel . "($cTxt)";
                    $headList[] = $colLabel . "(+/-)";
                }
                
                $exportContent .= createExportContent($headList);
                foreach($baseReportList as $listInfo){
                    
                    $valueList = array($websiteList[$listInfo['website_id']]['url'], $listInfo['source_name']);
                    foreach ($this->metrics as $colName => $colVal) {
                        if ($colName == 'name') continue;
                        
                        $currRank = isset($listInfo[$colName]) ? $listInfo[$colName] : 0;
                        $prevRank = isset($compareReportList[$listInfo['website_id']][$listInfo['id']][$colName]) ? $compareReportList[$listInfo['website_id']][$listInfo['id']][$colName] : 0;
                        $rankDiff = "";
                        
                        // if both ranks are existing
                        if ($prevRank != '' && $currRank != '') {
                            $rankDiff = $currRank - $prevRank;
                            if ($colName == 'bounceRate') $rankDiff = $rankDiff * -1;
                        }
                        
                        $valueList[] = $prevRank;
                        $valueList[] = $currRank;
                        $valueList[] = $rankDiff;
                    }
                    
                    $exportContent .= createExportContent( $valueList);
                }
                
                if ($summaryPage) {
                    return $exportContent;
                } else {
                    exportToCsv('analytics_summary', $exportContent);
                }
                
            } else {
                
                // if pdf export
                if ($summaryPage) {
                    return $this->getViewContent('analytics/analytics_summary');
                } else {
                    // if pdf export
                    if ($searchInfo['doc_type'] == "pdf") {
                        exportToPdf($this->getViewContent('analytics/analytics_summary'), "analytics_summary_$fromTime-$toTime.pdf");
                    } else {
                        $this->set('searchInfo', $searchInfo);
                        $this->render('analytics/analytics_summary');
                    }
                }
                
            }
        }
        
        function __getWebsiteSourceList($websiteId) {
            $websiteId = intval($websiteId);
            $sql = "select * from analytic_sources where 
            id in (select distinct source_id from website_analytics where website_id=$websiteId)
            order by source_name";
            $sourceList = $this->db->select($sql);
            return $sourceList;
        }
        
        // func to show analytics reports
        function viewAnalyticsReports($searchInfo = '') {
            
            $userId = isLoggedIn();
            
            if (!empty ($searchInfo['from_time'])) {
                $fromTimeDate = addslashes($searchInfo['from_time']);
            } else {
                $fromTimeDate = date('Y-m-d', strtotime('-17 days'));
            }
            
            if (!empty ($searchInfo['to_time'])) {
                $toTimeDate = addslashes($searchInfo['to_time']);
            } else {
                $toTimeDate = date('Y-m-d', strtotime('-1 days'));
            }
            
            $this->set('fromTime', $fromTimeDate);
            $this->set('toTime', $toTimeDate);
            
            $websiteController = New WebsiteController();
            $websiteList = $websiteController->__getAllWebsites($userId, true);
            $this->set('websiteList', $websiteList);
            $websiteId = empty ($searchInfo['website_id']) ? $websiteList[0]['id'] : intval($searchInfo['website_id']);
            $this->set('websiteId', $websiteId);
            
            $sourceList = $this->__getWebsiteSourceList($websiteId);
            $this->set('sourceList', $sourceList);
            $sourceId = empty ($searchInfo['source_id']) ? $sourceList[0]['id'] : $searchInfo['source_id'];
            $this->set('sourceId', $sourceId);
            
            $conditions = " and s.website_id=$websiteId";
            $conditions .= empty ($sourceId) ? "" : " and s.source_id=$sourceId";
            $sql = "select s.* from website_analytics s
                  where report_date>='$fromTimeDate' and report_date<='$toTimeDate' $conditions
                  order by s.report_date";
            $reportList = $this->db->select($sql);
            
            $colList = array_keys($this->metrics);
            $prevRank = [];
            $rankDiff = [];
            foreach ($colList as $col) {
                $prevRank[$col] = 0;
            }
            
            // loop through rank
            foreach ($reportList as $key => $repInfo) {
                
                // exclude first row
                if ($key) {
                foreach ($colList as $col) {
                    $rankDiff[$col] = '';
                }
           
                foreach ($colList as $col) {
                    $rankDiff[$col] = round($repInfo[$col] - $prevRank[$col], 2);
                    if (empty($rankDiff[$col])) {
                        continue;
                    }
                    
                    if ($col == "bounceRate" ) {
                        $rankDiff[$col] = $rankDiff[$col] * -1;
                    }
                    
                    $rankClass = ($rankDiff[$col] > 0) ? 'green' : 'red';
                    $rankDiff[$col] = "<font class='$rankClass'>($rankDiff[$col])</font>";
                    $reportList[$key]['rank_diff_'.$col] = empty($rankDiff[$col]) ? '' : $rankDiff[$col];
                }
            }
                
                foreach ($colList as $col) {
                    $prevRank[$col] = $repInfo[$col];
                }               
            }
            
            $this->set('list', array_reverse($reportList, true));
            $this->render('analytics/analytics_reports');
            
        }
        
        // func to show analytics reports in graph
        function viewAnalyticsGraphReports($searchInfo = '') {
            $userId = isLoggedIn();
            
            if (!empty ($searchInfo['from_time'])) {
                $fromTimeDate = addslashes($searchInfo['from_time']);
            } else {
                $fromTimeDate = date('Y-m-d', strtotime('-17 days'));
            }
            
            if (!empty ($searchInfo['to_time'])) {
                $toTimeDate = addslashes($searchInfo['to_time']);
            } else {
                $toTimeDate = date('Y-m-d', strtotime('-1 days'));
            }
            
            $this->set('fromTime', $fromTimeDate);
            $this->set('toTime', $toTimeDate);
            
            $websiteController = New WebsiteController();
            $websiteList = $websiteController->__getAllWebsites($userId, true);
            $this->set('websiteList', $websiteList);
            $websiteId = empty ($searchInfo['website_id']) ? $websiteList[0]['id'] : intval($searchInfo['website_id']);
            $this->set('websiteId', $websiteId);
            
            $sourceList = $this->__getWebsiteSourceList($websiteId);
            $this->set('sourceList', $sourceList);
            $sourceId = empty ($searchInfo['source_id']) ? $sourceList[0]['id'] : $searchInfo['source_id'];
            $this->set('sourceId', $sourceId);
            
            $conditions = " and s.website_id=$websiteId";
            $conditions .= empty ($sourceId) ? "" : " and s.source_id=$sourceId";
            $sql = "select s.* from website_analytics s
                  where report_date>='$fromTimeDate' and report_date<='$toTimeDate' $conditions
                  order by s.report_date";
            $reportList = $this->db->select($sql);
            
            // if reports not empty
            $colList = $this->metrics;
            $this->set('colList', $colList);
            $this->set('searchInfo', $searchInfo);
            
            $graphColList = array();
            if (!empty($searchInfo['attr_type'])) {
                $graphColList[$searchInfo['attr_type']] = $colList[$searchInfo['attr_type']];
                if ($searchInfo['attr_type'] == 'bounceRate') { 
                    $this->set('reverseDir', true);
                }
            } else {
                $graphColList = $colList;
                unset($graphColList['bounceRate']);
            }
            
            if (!empty($reportList)) {
                
                $dataArr = "['Date', '" . implode("', '", array_values($graphColList)) . "']";
                
                // loop through data list
                foreach ($reportList as $dataInfo) {
                    
                    $valStr = "";
                    foreach ($graphColList as $seId => $seVal) {
                        $valStr .= ", ";
                        $valStr .= !empty($dataInfo[$seId]) ? $dataInfo[$seId] : 0;
                    }
                    
                    $dataArr .= ", ['{$dataInfo['report_date']}' $valStr]";
                }
                
                $this->set('dataArr', $dataArr);
                $this->set('graphTitle', $this->spTextTools['Website Analytics Summary']);
                $graphContent = $this->getViewContent('report/graph');
            } else {
                $graphContent = showErrorMsg($_SESSION['text']['common']['No Records Found'], false, true);
            }
            
            // get graph content
            $this->set('graphContent', $graphContent);
            $this->render('analytics/graphicalreport');
            
        }
        
        // func to show keyword select box
        function showSourceSelectBox($websiteId){
            $websiteId = intval($websiteId);
            $this->set('sourceList', $this->__getWebsiteSourceList($websiteId));
            $this->render('analytics/source_select_box', 'ajax');
        }
        
}
?>
Subversion Repositories cheapmusic

(root)/www/seopanel/controllers/analytics.ctrl.php – Rev 103
