<?php
if ( ! defined( 'ABSPATH' ) ) exit; 
class wsmDatabase{
    private $wsmDB,$tablePrefix,$arrTables,$arrCachedStats;
    private $arrInsertLogVisit=array(),$arrInsertLogUniqueVisit=array();
    function __construct(){
        global $wpdb,$arrCashedStats;
        $this->wsmDB=$wpdb;
        $this->tablePrefix=$this->wsmDB->base_prefix.WSM_PREFIX;
        $this->arrTables=get_option(WSM_PREFIX.'_tables');
        $this->arrInsertLogUniqueVisit=array('siteId','visitorId','visitLastActionTime','configId','ipAddress','userId','firstActionVisitTime','daysSinceFirstVisit','returningVisitor','visitCount','visitEntryURLId','visitExitURLId','visitTotalActions','refererUrlId','browserLang','browserId','deviceType','oSystemId','currentLocalTime','daysSinceLastVisit','totalTimeVisit','resolutionId','cookie','director','flash','gears','java','pdf','quicktime','realplayer','silverlight','windowsmedia','city','countryId','latitude','longitude','regionId');
        $this->arrInsertLogVisit=array('siteId','visitorId','visitId','refererUrlId','serverTime','timeSpentRef','URLId','keyword');
        $this->arrCachedStats=$arrCashedStats;
        //$this->fnCorrectDatabaseTables();
        //print_r($this->arrTables);
    }
    function fnLogError($extra=''){
        if ($this->wsmDB->last_error) {
            $error=$this->wsmDB->last_query.PHP_EOL.$this->wsmDB->last_error.PHP_EOL;            
            if($extra!='' && is_array($extra)){
              $error.=print_r($extra,true);
            }else{
              $error.=$extra;  
            }
             wsmFNUpdateLogFile('MySQL ERROR',$error); 
        }
    }
    function fnInsertNewUniqueVisit($properties){
        $fields=implode(',',$this->arrInsertLogUniqueVisit);
        $sql  = "INSERT INTO {$this->tablePrefix}{$this->arrTables['LOG_UNIQUE']} ($fields) VALUES (";
        foreach($this->arrInsertLogUniqueVisit as $key){
			
			if(isset($properties[$key]))
			{
				if($key=='visitorId' || $key=='configId'){
					$sql.="'".$properties[$key]."',";
				}else if(is_numeric($properties[$key]) || $properties[$key]=='0'){
					$sql.=$properties[$key].',';
				}else if(!isset($properties[$key]) || is_null($properties[$key])){
					$sql.="'',";
				}else{
					$sql.= isset($properties[$key]) ?  "'".addslashes($properties[$key])."'," : "'',";
				}
			}else{
				$sql.="'',";
			}
        }
        $sql=rtrim($sql,',').')';
        //echo '<br>'.$sql;
        $this->wsmDB->query($sql);
        $this->fnLogError();
        return $this->wsmDB->insert_id;
    }
    function fnInsertNewVisit($properties){
       
        $fields=implode(',',$this->arrInsertLogVisit);
        $sql  = "INSERT INTO {$this->tablePrefix}{$this->arrTables['LOG_VISIT']} ($fields) VALUES (";
        if(isset($properties['visitId']) && $properties['visitId']!=0){            
            foreach($this->arrInsertLogVisit as $key){ 
                if($key=='visitorId'){
                    $sql.="'".$properties[$key]."',";
                }else if(is_numeric($properties[$key]) || $properties[$key]=='0'){
                    $sql.=$properties[$key].',';
                }else{
                    $sql.="'".addslashes($properties[$key])."',";
                }            
            }
                        
            $sql=rtrim($sql,',').')';
            //echo '<br>'.$sql;
            $this->wsmDB->query($sql);        
            $this->fnLogError($properties);
            return $this->wsmDB->insert_id;
        }
    }
    function fnGetLastLinkVisited($visitId){
        $sqlQuery="SELECT id FROM {$this->tablePrefix}{$this->arrTables['LOG_VISIT']} WHERE visitId={$visitId} ORDER BY id DESC LIMIT 1";
        $id=$this->wsmDB->get_var($sqlQuery);
        return $id;
    }
    function fnIsNotDuplicateLinkVisit($properties,$urlId){
        $sqlQuery="SELECT * FROM {$this->tablePrefix}{$this->arrTables['LOG_VISIT']} WHERE visitId={$properties['visitId']} AND refererUrlId={$properties['refererUrlId']} AND URLId={$urlId} ORDER BY id DESC LIMIT 1";
        $result=$this->wsmDB->get_row($sqlQuery,ARRAY_A);
        if($result==null){
            return 'yes';
        }else{
            $firstTime=strtotime($result['serverTime']);
            $secondTime=time();
            $differenceInSeconds = $secondTime - $firstTime;
            if($differenceInSeconds>60){
                return 'yes';
            }else{
                return $result['id'];
            }
        }
        return 'yes';
    }
    function fnUpdateExistingLinkVisit($properties, $linkId){
        $sqlQuery  = "UPDATE {$this->tablePrefix}{$this->arrTables['LOG_VISIT']} SET timeSpentRef=".$properties['timeSpentRef'];
        $sqlQuery .= " WHERE id = $linkId";
        $this->wsmDB->query($sqlQuery);
        $this->fnLogError();
    }
    function fnUpdateExistingVisit($properties,$visitId){
        $sqlQuery  = "UPDATE {$this->tablePrefix}{$this->arrTables['LOG_UNIQUE']} SET ";
        foreach($properties as $key=>$value){
            if($key=='timeSpentRef' || $key=='URLId' || $key=='keyword' || $key=='visitorId'){
                continue;
            }
            $sqlQuery .=$key."=";
            if(is_numeric($value) || $key=='visitTotalActions'){
                $sqlQuery.=$value.",";
            }else{
                $sqlQuery.="'".addslashes($value)."',";
            }
        }
        $sqlQuery=rtrim($sqlQuery,',');
        $sqlQuery .= " WHERE id = $visitId";
        $this->wsmDB->query($sqlQuery);
        $this->fnLogError();
    }
    function fnGetColumnsOfLogUniqueVisit(){
        $sql='SHOW COLUMNS FROM '.$this->tablePrefix.$this->arrTables['LOG_UNIQUE'];
        $result=$this->wsmDB->get_results($sql,ARRAY_A);
        return $result;
    }
    function fnGetColumnsOfLogVisit(){
        $sql='SHOW COLUMNS FROM '.$this->tablePrefix.$this->arrTables['LOG_VISIT'];
        $result=$this->wsmDB->get_results($sql,ARRAY_A);
        return $result;
    }
    function fnGetCountryIdByCode($code){
        $id=0;
        if(isset($code) && $code!=''){
            $sql= $this->wsmDB->prepare('SELECT id FROM '.$this->tablePrefix.$this->arrTables['COUNTRY'].' WHERE alpha2Code = %s',$code);
            $id=$this->wsmDB->get_var($sql);
            if(is_null($id) || $id==''){
                $id=0;
            }
        }
        return $id;
    }
    function fnGetAllCountries(){
        $sql='SELECT * FROM '.$this->tablePrefix.$this->arrTables['COUNTRY'].' ORDER BY name';
        $results=$this->wsmDB->get_results($sql,ARRAY_A);
        return $results;
    }
    function fnGetRegionIdByCode($code){
        $id=0;
        if(isset($code) && $code !=''){
            $sql= $this->wsmDB->prepare('SELECT id FROM '.$this->tablePrefix.$this->arrTables['RG'].' WHERE code = %s',$code);
            $id=$this->wsmDB->get_var($sql);
            if(is_null($id) || $id==''){
                $id=0;
            }
        }
        return $id;
    }
    function fnFindVisitorById($idVisitor,$lookBackSec,$lookAheadSec ){
        $sql='SELECT * FROM '.$this->tablePrefix.$this->arrTables['LOG_UNIQUE'].' WHERE visitorId="'.$idVisitor.'" AND visitLastActionTime>="'.$lookBackSec.'" AND visitLastActionTime<="'.$lookAheadSec.'" ORDER BY visitLastActionTime DESC LIMIT 1';
        $row=$this->wsmDB->get_row($sql,ARRAY_A);
        if(is_null($row)){
            $row=0;
        }
        return $row;
    }
    function fnFindVisitorByConfigId($configId,$lookAheadSec, $lookBackSec){
        $sql='SELECT * FROM '.$this->tablePrefix.$this->arrTables['LOG_UNIQUE'].' WHERE configId="'.$configId.'" AND visitLastActionTime>="'.$lookAheadSec.'" AND visitLastActionTime<="'.$lookBackSec.'" ORDER BY visitLastActionTime DESC LIMIT 1';
        $row=$this->wsmDB->get_row($sql,ARRAY_A);
        if(is_null($row)){
            $row=0;
        }
        return $row;
    }
    function fnGetBrowserIDByTitle($title=''){
        $id=0;
        if(isset($title) && $title !=''){
            $sql= $this->wsmDB->prepare('SELECT id FROM '.$this->tablePrefix.$this->arrTables['BROW'].' WHERE name like %s','%'.$title.'%');
            $id=$this->wsmDB->get_var($sql);
            if(is_null($id) || $id==''){
                $id=0;
                $newSql='INSERT INTO '.$this->tablePrefix.$this->arrTables['BROW'].' (name) VALUES ("'.$title.'")';
                $this->wsmDB->query($newSql);
                $this->fnLogError();
                $id=$this->wsmDB->insert_id;
            }
        }
        return $id;
    }
    function fnGetOSIDByTitle($title=''){
        $id=0;
        if(isset($title) && $title !=''){
            $sql= $this->wsmDB->prepare('SELECT id FROM '.$this->tablePrefix.$this->arrTables['OS'].' WHERE name like %s','%'.$title.'%');
            $id=$this->wsmDB->get_var($sql);
            if(is_null($id) || $id==''){
                $id=0;
                $newSql='INSERT INTO '.$this->tablePrefix.$this->arrTables['OS'].' (name) VALUES ("'.$title.'")';
                $this->wsmDB->query($newSql);
                $this->fnLogError();
                $id=$this->wsmDB->insert_id;
            }
        }
        return $id;
    }
    function fnGetResolutionIDByTitle($title=''){
        $id=0;
        if(isset($title) && $title !=''){
            $sql= $this->wsmDB->prepare('SELECT id FROM '.$this->tablePrefix.$this->arrTables['RSOL'].' WHERE name = %s',$title);
            $id=$this->wsmDB->get_var($sql);
            if(is_null($id) || $id==''){
                $id=0;
                $newSql='INSERT INTO '.$this->tablePrefix.$this->arrTables['RSOL'].' (name) VALUES ("'.$title.'")';
                $this->wsmDB->query($newSql);
                $this->fnLogError();
                $id=$this->wsmDB->insert_id;
            }
        }
        return $id;
    }
    function fnGetURLogID($arrLog){
        $id=0;
		$sql = '';
        if(isset($arrLog['url']) && $arrLog['url']!=''){
            $arrURL=$this->fnReturnURLElements($arrLog['url']);
            if($arrURL['url']!='' && $arrURL['url']!='0'){
				
				
				if(isset($arrURL['hash']) && isset($arrLog['pageId']))
                if(trim($arrLog['pageId'])!='' && trim($arrURL['hash']) !=''){
                    $sql= $this->wsmDB->prepare('SELECT * FROM '.$this->tablePrefix.$this->arrTables['LOG_URL'].' WHERE pageId = %d OR  hash=%s',$arrLog['pageId'],$arrURL['hash']);
                } else{
                    $sql= $this->wsmDB->prepare('SELECT * FROM '.$this->tablePrefix.$this->arrTables['LOG_URL'].' WHERE hash = %s',$arrURL['hash']);
                }
				
				
				if(isset($sql))
				{
                $rowResult=$this->wsmDB->get_row($sql,ARRAY_A);
                // print_r($rowResult);
					if(is_null($rowResult) || $rowResult==null){
						$this->wsmDB->insert(
							$this->tablePrefix.$this->arrTables['LOG_URL'],
							array(
								'pageId' => isset($arrLog['pageId'])?$arrLog['pageId']:'',
								'title' => isset($arrLog['title'])?$arrLog['title']:'',
								'hash' => $arrURL['hash'],
								'protocol' => $arrURL['protocol'] ,
								'url' => $arrURL['url']
							),
							array(
								'%s',
								'%s',
								'%s',
								'%s',
								'%s'
							)
						);
						$id=$this->wsmDB->insert_id;
					}else{
						$pageId=$arrLog['pageId']!=''?$arrLog['pageId']:($rowResult['pageId']!=''?$rowResult['pageId']:null);
						$this->wsmDB->update(
							$this->tablePrefix.$this->arrTables['LOG_URL'],
							array(
								'pageId' => $pageId,
								'title' => ($arrLog['title']=='')?$rowResult['title']:$rowResult['title']
								),
							array( 'id' => $rowResult['id'] ),
							array(
								'%s',
								'%s'
							),
							array( '%d' )
						);
						$id=$rowResult['id'];
					}
				}
            }
        }
       // echo '<br>'.$this->wsmDB->last_query;
        return $id;
    }
    function fnGetRefLogID($url){
        $id=0;
        if($url!=''){
            $arrURL=$this->fnReturnURLElements($url);
            /*$refURL=get_transient('wsm_'.wsmGetVisitorIdFromCookie());
            if(!$refURL){
                $refURL=$arrURL['url'];
            }*/
            if($arrURL['url']!='' && $arrURL['url']!='0' && isset($arrURL['hash'])){
                $sql= $this->wsmDB->prepare('SELECT id FROM '.$this->tablePrefix.$this->arrTables['LOG_URL'].' WHERE hash = %s',$arrURL['hash']);
                $id=$this->wsmDB->get_var($sql);
                if(is_null($id) || $id==''){
					
					$myurl = !empty($arrURL['url']) ? $this->fnGetToolBarID($arrURL['url']) : 0;
					
                    $this->wsmDB->insert(
                        $this->tablePrefix.$this->arrTables['LOG_URL'],
                        array(
                            'hash' => $arrURL['hash'],
                            'protocol' => $arrURL['protocol'] ,                            
                            'url' => $arrURL['url'],
                            'searchEngine'=>$this->fnGetSearchEngineID($arrURL['url']),
                            'toolBar'=>$myurl
                        ),
                        array(
                            '%s',
                            '%s',
                            '%s',
                            '%d',
                            '%d'
                        )
                    );
                    $id=$this->wsmDB->insert_id;
                }
            }
        }
        return $id;
    }
    function fnUpdateURLParameters($id,$arrParam){
        if(is_array($arrParam) && count($arrParam)>0){
            $this->wsmDB->update(
                $this->tablePrefix.$this->arrTables['LOG_URL'],
                $arrParam,
                array( 'id' => $id ),
                array_values(array_map(function ($k,$v){if(is_numeric($v)){return '%d';}else{return '%s';}},array_keys($arrParam),$arrParam)),
                array( '%d' )
            );
        }
    }
    function fnReturnURLElements($url){
        //$url=strtolower($url);
        $url = rtrim($url,"/");
        $arrURL=parse_url($url);
        $arrURL['host']=str_replace('www.','',$arrURL['host']);        
        $newURL=str_replace($arrURL['scheme'].'://','',$url); 
        $newURL=str_replace('www.','',$newURL);              
        $hash=substr(md5($newURL),0,16);
        return array('protocol'=>$arrURL['scheme'].'://','url'=>$newURL,'hash'=>$hash);
    }
    function fnGetSearchEngineID($url){
		global $wpdb;
        $id=0;
		//$url = sanitize_text_field($url);
		$url = addslashes($url);
		
		if(!empty($url))
		{
			$sql="SELECT id FROM {$this->tablePrefix}{$this->arrTables['SE']} WHERE  '%s' LIKE CONCAT('%',CONCAT(name,'%'))";
			$id=$this->wsmDB->get_var($wpdb->prepare($sql, $url));
		}
		



        if(is_null($id) || $id==''){
            $id=0;
        }
        return $id;
    }
    function fnGetToolBarID($url){
		global $wpdb;
		
		$url = sanitize_text_field($url);
		if(!empty($url))
		{
			$id=0;
			$sql="SELECT id FROM {$this->tablePrefix}{$this->arrTables['TOOL']} WHERE  '%s' LIKE CONCAT('%',CONCAT(name,'%'))";
			$id=$this->wsmDB->get_var($wpdb->prepare($sql, $url));
			if(is_null($id) || $id==''){
				$id=0;
			}
			return $id;
		}else
		{
			return 0;
		}
    }
    function fnGetSearchEngineList(){
        if(isset($this->arrCachedStats['searchEngines']) && count($this->arrCachedStats['searchEngines'])>0){
            return $this->arrCachedStats['searchEngines'];
        }
        $sql= 'SELECT id,name FROM '.$this->tablePrefix.$this->arrTables['SE'];
        $arrResult=$this->wsmDB->get_results($sql,ARRAY_A);
        $this->arrCachedStats['searchEngines']=$arrResult;
        return $arrResult;
    }
    function fnGetToolBarList(){
        $sql= 'SELECT id,name FROM '.$this->tablePrefix.$this->arrTables['TOOL'];
        $arrResult=$this->wsmDB->get_results($sql,ARRAY_A);
        return $arrResult;
    }
    function fnGetMostActiveVisitors($limit=""){
        if(isset($this->arrCachedStats['mostActiveVisitors']) && is_array($this->arrCachedStats['mostActiveVisitors']) && count($this->arrCachedStats['mostActiveVisitors'])>0){
          return $this->arrCachedStats['mostActiveVisitors'];
        }
        $newTimeZone=wsmCurrentGetTimezoneOffset();
        $visitLastActionTime="CONVERT_TZ(VI.visitLastActionTime,'+00:00','".$newTimeZone."')";
        $serverTime="CONVERT_TZ(VI.serverTime,'+00:00','".$newTimeZone."')";
        $currentDate=wsmGetCurrentDateByTimeZone();
        $sqlQuery="SELECT VI.visitId, VI.ipAddress,VI.hits, VI.city, VI.alpha2Code,VI.country, VI.browser ,VI.osystem, VI.deviceType, VI.title, VI.url,VI.latitude, VI.longitude, VI.resolution, VI.refUrl";
        $sqlQuery.=",TIMEDIFF('{$currentDate}',{$visitLastActionTime}) as timeDiff";        
        $sqlQuery.=" FROM {$this->tablePrefix}_visitorInfo VI";        
        $sqlQuery.=" WHERE {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.WSM_ONLINE_SESSION.' minutes')."'";
        $sqlQuery.=" ORDER BY VI.hits DESC";
        
        if($limit!='')
			$sqlQuery.=" limit 0, $limit";
			
        $arrResult=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
        $this->arrCachedStats['mostActiveVisitors']=$arrResult;
        return $arrResult;
    }
    function fnGetActiveVisitorsCount($groupBy='country'){        
        if(isset($this->arrCachedStats['activeVisitorsCount'.$groupBy]) && is_array($this->arrCachedStats['activeVisitorsCount'.$groupBy]) && count($this->arrCachedStats['activeVisitorsCount'.$groupBy])>0){
          return $this->arrCachedStats['activeVisitorsCount'.$groupBy];
        }
        $newTimeZone=wsmCurrentGetTimezoneOffset();
        $visitLastActionTime="CONVERT_TZ(visitLastActionTime,'+00:00','".$newTimeZone."')";        
        $currentDate=wsmGetCurrentDateByTimeZone();
        $sqlQuery="SELECT COUNT(visitId) as visitors,alpha2Code,country";
        $groupByQuery=' GROUP BY country';
        if($groupBy=='city'){
            $sqlQuery.=',city';
            $groupByQuery.=',city';
        }
        $sqlQuery.=' FROM '.$this->tablePrefix.'_visitorInfo';
         $sqlQuery.=" WHERE {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.WSM_ONLINE_SESSION.' minutes')."'";
        $sqlQuery.=$groupByQuery.' ORDER BY visitors DESC';
        $arrResult=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
        $this->arrCachedStats['activeVisitorsCount'.$groupBy]=$arrResult;
        return $arrResult;
    }
    function fnGetTotalVisitorsCount($condition="",$arrParam=array()){
        $sqlQuery="SELECT COUNT(*) FROM {$this->tablePrefix}{$this->arrTables['LOG_UNIQUE']} ";
		$sqlReportQuery = "SELECT SUM(total_visitors) FROM {$this->tablePrefix}_datewise_report WHERE normal = 1 ";
		$whereCondition = '';
        $visitLastActionTime="CONVERT_TZ(visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";

        if( isset( $arrParam['searchengine'] ) && $arrParam['searchengine'] ){
           $sqlQuery .= " LEFT JOIN {$this->tablePrefix}_url_log on {$this->tablePrefix}_url_log.id =   {$this->tablePrefix}{$this->arrTables['LOG_UNIQUE']}.refererUrlId ";
			$sqlReportQuery = "SELECT SUM(total_visitors) FROM {$this->tablePrefix}_datewise_report WHERE 1 = 1 ";
        }
		
		$sqlQuery .= " WHERE 1=1 ";
        switch($condition){
            case 'Hour':
                $whereCondition.="AND {$visitLastActionTime} >= '".wsmGetDateByInterval('-1 hour')."'";
                break;
            case 'Today':
                $whereCondition.="AND {$visitLastActionTime} >= '".wsmGetCurrentDateByTimeZone('Y-m-d 00:00:00')."'";
                break;
            case 'Last2Months':
                $whereCondition.="AND {$visitLastActionTime} >= '".wsmGetDateByInterval('-2 months','Y-m-d 00:00:00')."'";
                break;
            case 'Online':
                $whereCondition.="AND {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.WSM_ONLINE_SESSION.' minutes')."'";
                break;
            case '7dayBeforeHour':
                $whereCondition.="AND {$visitLastActionTime} >= '".wsmGetDateByInterval('-7 days','Y-m-d '.wsmGetCurrentDateByTimeZone('H').':00:00')."' AND {$visitLastActionTime}<='".wsmGetDateByInterval('-7 days','Y-m-d '.wsmGetCurrentDateByTimeZone('H').':59:59')."'";
                break;
            case '14dayBeforeHour':
                $whereCondition.="AND {$visitLastActionTime} >= '".wsmGetDateByInterval('-14 days','Y-m-d '.wsmGetCurrentDateByTimeZone('H').':00:00')."' AND {$visitLastActionTime}<='".wsmGetDateByInterval('-14 days','Y-m-d '.wsmGetCurrentDateByTimeZone('H').':59:59')."'";
                break;
            case '7dayBefore':
                $whereCondition.="AND {$visitLastActionTime} >= '".wsmGetDateByInterval('-7 days','Y-m-d 00:00:00')."' AND {$visitLastActionTime}<='".wsmGetDateByInterval('-7 days','Y-m-d 23:59:59')."'";
                break;
            case '14dayBefore':
                $whereCondition.="AND {$visitLastActionTime} >= '".wsmGetDateByInterval('-14 days','Y-m-d 00:00:00')."' AND {$visitLastActionTime}<='".wsmGetDateByInterval('-14 days','Y-m-d 23:59:59')."'";
            break;
            case 'Compare':
                if(isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date'])){
                    $whereCondition.="AND {$visitLastActionTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['date'].' 23:59:59'."'";
                }
            break;
            case 'Normal':
            case 'Range':
                if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                    
                     $whereCondition.="AND {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";  
                }                
            break;
            default:
                if($condition!='' && is_numeric($condition)){
                    $whereCondition.=" AND {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
                }
                if(wsmValidateDateTime($condition)){
                    $whereCondition.=" AND {$visitLastActionTime} >= '".$condition.' 00:00:00'."'";
                }
                break;
        }
		$sqlQuery .= $whereCondition;
		$sqlReportQuery .= $whereCondition;
		
        if( isset( $arrParam['searchengine'] ) && $arrParam['searchengine'] ){
            $sqlQuery .= ' AND ( ';
            $searchEngineResult = $this->fnGetSearchEngine();
            if( $searchEngineResult ){
                $sepeartor = '';
                foreach( $searchEngineResult as $searchEngine ){
                    $sqlQuery .= " $sepeartor {$this->tablePrefix}_url_log.url LIKE '%".$searchEngine['name']."%' ";
                    $sepeartor = ' OR ';
                }
            }
            $sqlQuery .= ' ) ';
			$sqlReportQuery .= ' AND search_engine != "" ';
        }
		//echo $sqlQuery.'<br />';
		$sqlReportQuery = str_replace('visitLastActionTime','date', $sqlReportQuery);
		//echo $sqlReportQuery.'<br/>';
		$count=$this->wsmDB->get_var($sqlReportQuery);
		if( is_null( $count ) || $count == 0 ){
			//echo "in<br/>";
	        $count=$this->wsmDB->get_var($sqlQuery);
	        if(is_null($count)){
	            $count=0;
	        }
		}else{
	        if($condition!='' && is_numeric($condition)){
	            $whereCondition = "";
	        }	
        
			/* to get current date data */
			if($whereCondition=="")
			{
				$count +=$this->fnGetTotalVisitorsCount('Today');	
			}
		}
        
        return $count;
    } 
    function fnGetReferralTotalVisitorsCountByRefURL($condition="",$arrParam=array()){               
        $sqlQuery="SELECT DISTINCT LU.visitorId FROM {$this->tablePrefix}{$this->arrTables['LOG_UNIQUE']} LU LEFT JOIN {$this->tablePrefix}{$this->arrTables['LOG_URL']} UL ON LU.refererUrlId=UL.id WHERE UL.url LIKE '{$arrParam['refUrl']}%' AND";
        $visitLastActionTime="CONVERT_TZ(LU.visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
        switch($condition){            
            case 'Compare':
                if(isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date'])){
                    $sqlQuery.=" {$visitLastActionTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['date'].' 23:59:59'."'";
                }
            break;
            case 'Normal':
            case 'Range':
                if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                    
                     $sqlQuery.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";  
                }                
            break;
            default:
                if($condition!='' && is_numeric($condition)){
                    $sqlQuery.="  {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
                }
                if(wsmValidateDateTime($condition)){
                    $sqlQuery.="  {$visitLastActionTime} >= '".$condition.' 00:00:00'."'";
                }
                break;
        } 
//        echo $sqlQuery.'<br />';
        $result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);        
        $count=$this->wsmDB->num_rows;
        if(is_null($count)){
            $count=0;
        }
        return $count;
    } 
    function fnGetReferralTotalVisitorsCount($condition="",$arrParam=array()){
        $protocol='http://';
        if (is_ssl()) {
            $protocol='https://';
        }
		$whereCondition = '';
        $homeURL=str_replace('www.','',site_url());     
        $homeURL=str_replace($protocol,'',$homeURL);
        
        $sqlQuery="SELECT DISTINCT LU.visitorId FROM {$this->tablePrefix}{$this->arrTables['LOG_UNIQUE']} LU LEFT JOIN {$this->tablePrefix}{$this->arrTables['LOG_URL']} UL ON LU.refererUrlId=UL.id WHERE LU.refererUrlId<>0 AND UL.url NOT LIKE '{$homeURL}%' AND";
		
		$sqlReportQuery = "SELECT SUM( DR.total_visitors ) FROM {$this->tablePrefix}_datewise_report DR LEFT JOIN {$this->tablePrefix}{$this->arrTables['LOG_URL']} UL ON DR.url_id=UL.id  WHERE UL.url NOT LIKE '{$homeURL}%' AND";
		
		
        $visitLastActionTime="CONVERT_TZ(LU.visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
        switch($condition){            
            case 'Compare':
                if(isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date'])){
                    $whereCondition.=" {$visitLastActionTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['date'].' 23:59:59'."'";
                }
            break;
            case 'Normal':
            case 'Range':
                if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                    
                     $whereCondition.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";  
                }                
            break;
            default:
                if($condition!='' && is_numeric($condition)){
                    $whereCondition.="  {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
                }
                if(wsmValidateDateTime($condition)){
                    $whereCondition.="  {$visitLastActionTime} >= '".$condition.' 00:00:00'."'";
                }
                break;
        }
		
		$sqlReportQuery .= $whereCondition; 
		$sqlQuery .= $whereCondition;
		
        if( isset( $arrParam['searchengine'] ) && $arrParam['searchengine'] ){
            $sqlQuery .= ' AND ( ';
            $searchEngineResult = $this->fnGetSearchEngine();
            if( $searchEngineResult ){
                $sepeartor = '';
                foreach( $searchEngineResult as $searchEngine ){
                    $sqlQuery .= " $sepeartor UL.url LIKE '%".$searchEngine['name']."%' ";
                    $sepeartor = ' OR ';
                }
            }
            $sqlQuery .= ' ) ';
			$sqlReportQuery .= ' AND DR.search_engine != "" ';
        }
//		echo $sqlQuery.'<br />';
		$sqlReportQuery = str_replace('LU.visitLastActionTime','DR.date', $sqlReportQuery);
		//echo $sqlReportQuery.'<br />';
        $count=$this->wsmDB->get_var($sqlReportQuery);
        if(is_null($count)){
	        $result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);        
	        $count=$this->wsmDB->num_rows;
	        if(is_null($count)){
	            $count=0;
	        }
		}
        
        return $count;
    }
    function fnGetFirstTimeVisitorCount($condition="",$arrParam=array()){
        $sqlQuery="SELECT COUNT(visitorId) FROM {$this->tablePrefix}_uniqueVisitors ";
		$sqlReportQuery = "SELECT SUM(total_first_time_visitors) FROM {$this->tablePrefix}_datewise_report WHERE normal=1 ";
		$whereCondition = '';

        if( isset( $arrParam['searchengine'] ) && $arrParam['searchengine'] ){
           $sqlQuery .= " LEFT JOIN {$this->tablePrefix}_url_log on {$this->tablePrefix}_url_log.id =   {$this->tablePrefix}_uniqueVisitors.refererUrlId ";
		   $sqlReportQuery = "SELECT SUM(total_first_time_visitors) FROM {$this->tablePrefix}_datewise_report WHERE 1=1 ";
        }
		$sqlQuery .= ' WHERE 1=1 ';
		
        $firstVisitTime="CONVERT_TZ(firstVisitTime,'+00:00','".WSM_TIMEZONE."')";
        switch($condition){
            case 'Today':
                $whereCondition.="AND {$firstVisitTime} >= '".wsmGetCurrentDateByTimeZone('Y-m-d 00:00:00')."'";
                break;
            case 'Last2Months':
                $whereCondition.="AND {$firstVisitTime} >= '".wsmGetDateByInterval('-2 months','Y-m-d 00:00:00')."'";
                break;
            case 'Compare':
                if(isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date'])){
                    $whereCondition.="AND {$firstVisitTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$firstVisitTime}<='".$arrParam['date'].' 23:59:59'."'";
                }
            break; 
            case 'Normal':           
            case 'Range':            
                if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){
                     $whereCondition.="AND {$firstVisitTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$firstVisitTime}<='".$arrParam['to'].' 23:59:59'."'";
                }
               
            break;
            default:
                if($condition!='' && is_numeric($condition)){
                    $whereCondition.=" AND {$firstVisitTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
                }                
                break;
	    }
		$sqlQuery .= $whereCondition;
		$sqlReportQuery .= $whereCondition;
		
 
        if( isset( $arrParam['searchengine'] ) && $arrParam['searchengine'] ){
            $sqlQuery .= ' AND ( ';
            $searchEngineResult = $this->fnGetSearchEngine();
            if( $searchEngineResult ){
                $sepeartor = '';
                foreach( $searchEngineResult as $searchEngine ){
                    $sqlQuery .= " $sepeartor {$this->tablePrefix}_url_log.url LIKE '%".$searchEngine['name']."%' ";
                    $sepeartor = ' OR ';
                }
            }
            $sqlQuery .= ' ) ';
			$sqlReportQuery .= ' AND search_engine != "" ';
        }
		//echo $sqlQuery.'<br />';
		$sqlReportQuery = str_replace('firstVisitTime','date', $sqlReportQuery);
		//echo $sqlReportQuery;
		$count=$this->wsmDB->get_var($sqlReportQuery);
		if( is_null( $count ) || $count == 0 ){
	        $count=$this->wsmDB->get_var($sqlQuery);
	        if(is_null($count)){
	            $count=0;
	        }
		}else{
        	if($condition!='' && is_numeric($condition)){
            	$whereCondition = "";
        	}
			/* to get current date data */
			if($whereCondition=="")
			{
				$count +=$this->fnGetFirstTimeVisitorCount('Today');	
			}
			
		}
        return $count;
    }
    function fnGetReferralFirstTimeVisitorCount($condition="",$arrParam=array()){
        $protocol='http://';
        if (is_ssl()) {
            $protocol='https://';
        }
        $homeURL=str_replace('www.','',site_url());     
        $homeURL=str_replace($protocol,'',$homeURL);
        $sqlQuery="SELECT COUNT(visitorId) FROM {$this->tablePrefix}_uniqueVisitors UV LEFT JOIN {$this->tablePrefix}{$this->arrTables['LOG_URL']} UL ON UL.id=UV.refererUrlId WHERE UV.refererUrlId<>0 AND UL.url NOT LIKE '{$homeURL}%' ";
		
		$sqlReportQuery = "SELECT SUM( DR.total_first_time_visitors ) FROM {$this->tablePrefix}_datewise_report DR LEFT JOIN {$this->tablePrefix}{$this->arrTables['LOG_URL']} UL ON DR.url_id=UL.id  WHERE UL.url NOT LIKE '{$homeURL}%' ";
		$whereCondition = '';
		
        $firstVisitTime="CONVERT_TZ(firstVisitTime,'+00:00','".WSM_TIMEZONE."')";
        switch($condition){
            case 'Today':
                $whereCondition.="AND {$firstVisitTime} >= '".wsmGetCurrentDateByTimeZone('Y-m-d 00:00:00')."'";
                break;
            case 'Last2Months':
                $whereCondition.="AND {$firstVisitTime} >= '".wsmGetDateByInterval('-2 months','Y-m-d 00:00:00')."'";
                break;
            case 'Compare':
                if(isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date'])){
                    $whereCondition.="AND {$firstVisitTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$firstVisitTime}<='".$arrParam['date'].' 23:59:59'."'";
                }
            break; 
            case 'Normal':           
            case 'Range':            
                if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){
                     $whereCondition.="AND {$firstVisitTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$firstVisitTime}<='".$arrParam['to'].' 23:59:59'."'";
                }
               
            break;
            default:
                if($condition!='' && is_numeric($condition)){
                    $whereCondition.=" AND {$firstVisitTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
                }                
                break;
        }        
		
		$sqlQuery .= $whereCondition; 
		$sqlReportQuery .= $whereCondition; 
		   
        if( isset( $arrParam['searchengine'] ) && $arrParam['searchengine'] ){
            $sqlQuery .= ' AND ( ';
            $searchEngineResult = $this->fnGetSearchEngine();
            if( $searchEngineResult ){
                $sepeartor = '';
                foreach( $searchEngineResult as $searchEngine ){
                    $sqlQuery .= " $sepeartor UL.url LIKE '%".$searchEngine['name']."%' ";
                    $sepeartor = ' OR ';
                }
            }
            $sqlQuery .= ' ) ';
			$sqlReportQuery .= ' AND DR.search_engine != "" ';
        }
//		echo $sqlQuery.'<br />';

		$sqlReportQuery = str_replace('firstVisitTime','DR.date', $sqlReportQuery);
		//echo $sqlReportQuery.'<br />';
        $count=$this->wsmDB->get_var($sqlReportQuery);
        if(is_null($count)){
	        $count=$this->wsmDB->get_var($sqlQuery);
	        if(is_null($count)){
	            $count=0;
	        }
		}
        
        return $count;
    }
    function fnGetTotalPageViewCount($condition="",$arrParam=array()){
        $count=0;          
        $sqlQuery="SELECT SUM(totalViews) FROM {$this->tablePrefix}_pageViews ";
		$sqlReportQuery = "SELECT SUM(total_page_views) FROM {$this->tablePrefix}_datewise_report WHERE normal = 1 ";
        $visitLastActionTime="CONVERT_TZ(visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
		$whereCondition = '';
        if( isset( $arrParam['searchengine'] ) && $arrParam['searchengine'] ){
            $sqlQuery .= " LEFT JOIN {$this->tablePrefix}_url_log on {$this->tablePrefix}_url_log.id =   {$this->tablePrefix}_pageViews.refererUrlId ";
		
			$sqlReportQuery = "SELECT SUM(total_page_views) FROM {$this->tablePrefix}_datewise_report WHERE 1 = 1 ";
        }
        
		$sqlQuery .= "WHERE 1 = 1 ";
        switch($condition){
            case 'Today':
                $whereCondition.="AND {$visitLastActionTime} >= '".wsmGetCurrentDateByTimeZone('Y-m-d 00:00:00')."'";
            break;
            case 'Last2Months':
                $whereCondition.="AND {$visitLastActionTime} >= '".wsmGetDateByInterval('-2 months','Y-m-d 00:00:00')."'";
            break;              
            case 'Normal':        
            case 'Range':
                if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                                      
                     $whereCondition.="AND {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
                }               
            break;
            case 'Compare':
                if((isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date']))){
                    $whereCondition.="AND {$visitLastActionTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['date'].' 23:59:59'."'";
                }
            break;
            default:
                if($condition!='' && is_numeric($condition)){
                    $whereCondition.="AND {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
                }
            break;
        }
		$sqlQuery .= $whereCondition;
		$sqlReportQuery .= $whereCondition;
        if( isset( $arrParam['searchengine'] ) && $arrParam['searchengine'] ){
            $sqlQuery .= ' AND ( ';
            $searchEngineResult = $this->fnGetSearchEngine();
            if( $searchEngineResult ){
                $sepeartor = '';
                foreach( $searchEngineResult as $searchEngine ){
                    $sqlQuery .= " $sepeartor {$this->tablePrefix}_url_log.url LIKE '%".$searchEngine['name']."%' ";
                    $sepeartor = ' OR ';
                }
            }
            $sqlQuery .= ' ) ';
			$sqlReportQuery .= ' AND search_engine != "" ';
        }
        //echo $sqlQuery.'<br>';    
		$sqlReportQuery = str_replace('visitLastActionTime','date', $sqlReportQuery);
		$count=$this->wsmDB->get_var($sqlReportQuery);
		if( is_null( $count ) || $count == 0 ){
			
	        $count=$this->wsmDB->get_var($sqlQuery);
	        if(is_null($count)){
	            $count=0;
	        }
		}else{
	        if($condition!='' && is_numeric($condition)){
	            $whereCondition = "";
	        }
			/* to get current date data */
			if($whereCondition=="")
			{
				$count += $this->fnGetTotalPageViewCount('Today');	
			}	
		}
		
        return $count;
    }
    /*
     * Get serch engine list
     */
    function fnGetSearchEngine(){
        $sqlQuery = "SELECT name as oName, LCASE(replace(name, ' ','')) AS name FROM `{$this->tablePrefix}_searchEngines`";
        $arrResult=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
        return $arrResult;
    }
    /**
	* Get total visitors base on search engine
	*/	
	function fnGetTotalUserSeachEngineWise($condition, $arrParam = array()){
		$resultSet = array();
        $searchEngineResult = $this->fnGetSearchEngine();
        if( $searchEngineResult ){
            $sepeartor = '';
			$sqlQuery = '';
			$visitLastActionTime="CONVERT_TZ(PV.visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
	        switch($condition){
	            case 'Today':
	                $sqlQuery.="WHERE {$visitLastActionTime} >= '".wsmGetCurrentDateByTimeZone('Y-m-d 00:00:00')."'";
	            break;
	            case 'Last2Months':
	                $sqlQuery.="WHERE {$visitLastActionTime} >= '".wsmGetDateByInterval('-2 months','Y-m-d 00:00:00')."'";
	            break;              
	            case 'Normal':        
	            case 'Range':
	                if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                                      
	                     $sqlQuery.="WHERE {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
	                }               
	            break;
	            case 'Compare':
	                if((isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date']))){
	                    $sqlQuery.="WHERE {$visitLastActionTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['date'].' 23:59:59'."'";
	                }
	            break;
	            default:
	                if($condition!='' && is_numeric($condition)){
	                    $sqlQuery.="WHERE {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
	                }
	            break;
	        }
            foreach( $searchEngineResult as $searchEngine ){
				/*$dataQuery = "select count(*) AS total from `{$this->tablePrefix}_pageViews` PV 
							 LEFT JOIN `{$this->tablePrefix}_url_log` UL ON UL.id=PV.refererUrlId 
							 $sqlQuery AND UL.url IS NOT NULL AND UL.url  LIKE '%".$searchEngine['name']."%' ";*/
				$dataQuery = "select count(*) AS total from `{$this->tablePrefix}_pageViews` PV 
							 JOIN `{$this->tablePrefix}_url_log` UL ON UL.id=PV.refererUrlId 
							 $sqlQuery AND UL.url IS NOT NULL AND UL.url  LIKE '%".$searchEngine['name']."%' ";
				//echo $dataQuery."<br/>";			 
				$count=$this->wsmDB->get_var($dataQuery);
		        if(is_null($count)){
		            $count=0;
		        }
		        if($count > 0)
					$resultSet[ $searchEngine['oName'] ] = $count;
            }
			arsort($resultSet);
			return $resultSet;
        }	
	}
	
	
	/*
	 *  Get total visitors base on serach engine from datewise report
	 */
	 
	 /**
	* Get total visitors base on search engine
	*/	
	function fnGetTotalUserBySeachEngineWise($condition, $arrParam = array()){
		$resultSet = array();
        $searchEngineResult = $this->fnGetSearchEngine();
        if( $searchEngineResult ){
            $sepeartor = '';
			$sqlQuery = '';
			$visitLastActionTime="CONVERT_TZ(PV.visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
	        switch($condition){
	            case 'Today':
	                $sqlQuery.="WHERE {$visitLastActionTime} >= '".wsmGetCurrentDateByTimeZone('Y-m-d 00:00:00')."'";
	            break;
	            case 'Last2Months':
	                $sqlQuery.="WHERE {$visitLastActionTime} >= '".wsmGetDateByInterval('-2 months','Y-m-d 00:00:00')."'";
	            break;              
	            case 'Normal':        
	            case 'Range':
	                if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                                      
	                     $sqlQuery.="WHERE {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
	                }               
	            break;
	            case 'Compare':
	                if((isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date']))){
	                    $sqlQuery.="WHERE {$visitLastActionTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['date'].' 23:59:59'."'";
	                }
	            break;
	            default:
	                if($condition!='' && is_numeric($condition)){
	                    $sqlQuery.="WHERE {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
	                }
	            break;
	        }
	        
	        $dataQuery = "select count(*) AS total from `{$this->tablePrefix}_pageViews` PV 
							 LEFT JOIN `{$this->tablePrefix}_url_log` UL ON UL.id=PV.refererUrlId 
							 $sqlQuery AND UL.url IS NOT NULL AND UL.url  LIKE '%".$searchEngine['name']."%' ";
			
				$dataReportQuery = "select distinct(SE.name) as sname,sum(total_page_views) as total from {$this->tablePrefix}_datewise_report DR LEFT JOIN {$this->tablePrefix}_url_log UL ON UL.id=DR.url_id LEFT JOIN {$this->tablePrefix}_searchEngines SE on UL.url  LIKE CONCAT ('%', SE.name, '%') $sqlQuery AND UL.url IS NOT NULL and SE.name IS NOT NULL group by SE.name order by total desc";
				
				$dataReportQuery = str_replace('PV.visitLastActionTime','DR.date', $dataReportQuery);
				//echo $dataQuery."<br/>";																				
				//echo $dataReportQuery."<br/><br/>";
				$allResult=$this->wsmDB->get_results($dataReportQuery,ARRAY_A);
				//echo "<br />";
		        if( !$allResult )
		        {
					return $this->fnGetTotalUserSeachEngineWise($condition, $arrParam = array());
				}
				else
				{
					foreach($allResult as $res)	
					{
						$resultSet[$res['sname']] = $res['total'];
					}
					arsort($resultSet);
					return $resultSet;
				}
        }	
	}
	
    function fnGetReferralListDetails($condition="",$arrParam=array()){
        $protocol='http://';
        if (is_ssl()) {
            $protocol='https://';
        }
        $homeURL=str_replace('www.','',site_url());   
        $arrReferralList=array();          
        $visitLastActionTime="CONVERT_TZ(visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
        $sqlQuery="select * from {$this->tablePrefix}_visitorInfo WHERE refUrl IS NOT NULL AND refUrl NOT LIKE '{$homeURL}%' AND ";
        switch($condition){                      
            case 'Normal':        
            case 'Range':
                if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                                      
                     $sqlQuery.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
                }
            break;
            case 'Compare':
                if((isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date']))){
                    $sqlQuery.=" {$visitLastActionTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['date'].' 23:59:59'."'";
                }
            break;
            default:
                if($condition!='' && is_numeric($condition)){
                    $sqlQuery.=" {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
                }
            break;
        }
        $sqlQuery.=" ORDER by refUrl ASC";
        //echo $sqlQuery;
        $cPage=isset($arrParam['currentPage'])?$arrParam['currentPage']:1;
        $offset=($cPage-1)*WSM_PAGE_LIMIT;
        //echo $sqlQuery.=" LIMIT {$offset},".WSM_PAGE_LIMIT;
        $arrResult=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
        if(count($arrResult)>0){
            foreach($arrResult as $visitInfo){
                $resultURL = parse_url($visitInfo['refUrl']);
                $hostName=$resultURL['host'];
                $arrReferralList[$hostName][]=$visitInfo;                
            }
        }
        array_multisort(array_map('count', $arrReferralList), SORT_DESC, $arrReferralList);
        return $arrReferralList;           
    }
    function fnGetReferralList($condition="",$arrParam=array()){
		
        $protocol='http://';
        if (is_ssl()) {
            $protocol='https://';
        }
        $homeURL=str_replace($protocol.'www.','',site_url());   
        $arrReferralList=array();          
        $visitLastActionTime="CONVERT_TZ(PV.visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
        
        $sqlQuery="select UL.id, SUBSTRING_INDEX(UL.url,'/',1) as refUrl, SE.name AS searchEngine, count(*) AS total, sum( PV.totalViews) as totalPageViews, PV.keyword from  {$this->tablePrefix}_pageViews PV LEFT JOIN {$this->tablePrefix}{$this->arrTables['LOG_URL']} UL ON UL.id=PV.refererUrlId LEFT JOIN {$this->tablePrefix}_searchEngines SE  ON SE.id=UL.searchEngine WHERE UL.url IS NOT NULL AND UL.url NOT LIKE '{$homeURL}%' AND ";
        
        $sqlReportQuery = "select UL.id,SUBSTRING_INDEX(UL.url,'/',1) as refUrl, count(*) AS total, sum(DR.total_page_views) as totalPageViews,max(LV.keyword) from {$this->tablePrefix}_datewise_report DR LEFT JOIN {$this->tablePrefix}_url_log UL ON UL.id=DR.url_id LEFT JOIN {$this->tablePrefix}_logVisit LV on LV.URLId = UL.id WHERE UL.url IS NOT NULL AND UL.url NOT LIKE '{$homeURL}%' AND ";
        
        switch($condition){                      
            case 'Normal': 
            case 'Range':
                if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){
					$whereCondition = " {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
                   
                }
            break;
            case 'Compare':
                if((isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date']))){
					$whereCondition = "{$visitLastActionTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['date'].' 23:59:59'."'";
                   
                }
            break;
            default:
                if($condition!='' && is_numeric($condition)){
                    $whereCondition.=" {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
                }
            break;
        }
			
			
		$sqlQuery .= $whereCondition;
		$sqlReportQuery .= $whereCondition;
			
        if( isset( $arrParam['searchengine'] ) && $arrParam['searchengine']!='' ){
            $sqlQuery .= ' AND ( ';
            $sqlReportQuery .= ' AND ( ';
            $searchEngineResult = $this->fnGetSearchEngine();
            if( $searchEngineResult ){
                $sepeartor = '';
                foreach( $searchEngineResult as $searchEngine ){
                    $sqlQuery .= " $sepeartor UL.url LIKE '%".$searchEngine['name']."%' ";
                    $sqlReportQuery .= " $sepeartor UL.url LIKE '%".$searchEngine['name']."%' ";
                    $sepeartor = ' OR ';
                }
            }
            $sqlQuery .= ' ) ';
            $sqlReportQuery .= ' ) ';
            //$sqlReportQuery .= ' AND search_engine != "" ';
        }
        
        $sqlQuery.=" GROUP BY refUrl ORDER by total DESC";
        $sqlReportQuery.=" GROUP BY refUrl ORDER by totalPageViews DESC";
        
		$sqlReportQuery = str_replace('PV.visitLastActionTime','DR.date', $sqlReportQuery);
		
		//echo $sqlQuery."<br/>";
		//echo $sqlReportQuery."<br/>";
		
		
        $mainQuery = 0;
		$allResult=$this->wsmDB->get_results($sqlReportQuery,ARRAY_A);		
		if( !$allResult){
			$mainQuery=1;
			$allResult=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
		}
		
		$totalRecords=$this->wsmDB->num_rows;
        $cPage=isset($arrParam['currentPage'])?$arrParam['currentPage']:1;
        $offset=($cPage-1)*WSM_PAGE_LIMIT;
        $sqlReportQuery.=" LIMIT {$offset},".WSM_PAGE_LIMIT;
        if($mainQuery==1){
			$sqlQuery.=" LIMIT {$offset},".WSM_PAGE_LIMIT;
			$allResult=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
		}
		else
			$allResult=$this->wsmDB->get_results($sqlReportQuery,ARRAY_A);
        
        $arrResult['data']=$allResult;
        if(isset($arrParam['currentPage']) && isset($arrParam['adminURL'])){
            $arrResult['pagination']=wsmFnGetPagination($totalRecords,$arrParam['currentPage'],$arrParam['adminURL'],10);
        }
        return $arrResult;           
    }
    function fnGetListOfUrlsByReferral($condition="",$arrParam=array()){
        $visitLastActionTime="CONVERT_TZ(PV.visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
        
		$refURL = sanitize_url($arrParam['refUrl']);
		$refURL = str_ireplace ("'", "", $refURL);
		
        $sqlQuery="select PV.refererUrlId, UL2.title, CONCAT(UL2.protocol,UL2.url) as fullURL, COUNT(PV.visitId) as totalViews  from {$this->tablePrefix}_pageViews PV LEFT JOIN {$this->tablePrefix}{$this->arrTables['LOG_URL']} UL ON UL.id=PV.refererUrlId  LEFT JOIN {$this->tablePrefix}{$this->arrTables['LOG_URL']} UL2 ON UL2.id=PV.URLId WHERE UL.url LIKE '{$refURL}%' AND "; 
        
        $sqlReportQuery="select LV.refererUrlId, UL2.title, CONCAT(UL2.protocol,UL2.url) as fullURL, sum(PV.total_page_views) as totalViews  from {$this->tablePrefix}_datewise_report PV Left JOIN {$this->tablePrefix}_url_log UL ON UL.id=PV.url_id LEFT JOIN {$this->tablePrefix}_url_log UL2 ON UL2.id=PV.url_id LEFT JOIN {$this->tablePrefix}_logVisit LV on LV.URLId = UL.id WHERE UL.url LIKE '{$refURL}%' AND "; 
        
        
        //$sqlQuery="select CONCAT(UL.host,UL.url) as fullUrl, totalViews  from  {$this->tablePrefix}_pageViews PV LEFT JOIN {$this->tablePrefix}{$this->arrTables['LOG_URL']} UL ON UL.id=PV.refererUrlId AND UL.id=PV.URLId WHERE UL.url LIKE '{$arrParam['refUrl']}%' AND ";
        switch($condition){                      
            case 'Normal':        
            case 'Range':
                if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                                      
                     $sqlQuery.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
                     $sqlReportQuery.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
                }
            break;
            case 'Compare':
                if((isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date']))){
                    $sqlQuery.=" {$visitLastActionTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['date'].' 23:59:59'."'";
                    $sqlReportQuery.=" {$visitLastActionTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['date'].' 23:59:59'."'";
                }
            break;
            default:
                if($condition!='' && is_numeric($condition)){
                    $sqlQuery.=" {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
                    $sqlReportQuery.=" {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
                }
            break;
        }    
		$sqlReportQuery = str_replace('PV.visitLastActionTime','PV.date', $sqlReportQuery);
		
        $sqlQuery.=" GROUP BY UL2.url ORDER BY totalViews DESC";    
        $sqlReportQuery.=" GROUP BY UL2.url ORDER BY totalViews DESC";    
        
        //echo $sqlQuery."<br/>";
        //echo $sqlReportQuery."<br/>";
        
        $allRecords=$this->wsmDB->get_results($sqlReportQuery,ARRAY_A);        
        if(!$allRecords)
			$allRecords=$this->wsmDB->get_results($sqlQuery,ARRAY_A);        
			
        return $allRecords;
    }
    function fnGetTotalReferralsByRefURL($condition="",$arrParam=array()){        
        $visitLastActionTime="CONVERT_TZ(PV.visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
        $sqlQuery="select count(*) AS total from  {$this->tablePrefix}_pageViews PV LEFT JOIN {$this->tablePrefix}{$this->arrTables['LOG_URL']} UL ON UL.id=PV.refererUrlId WHERE UL.url LIKE '{$arrParam['refUrl']}%' AND ";
        switch($condition){                      
            case 'Normal':        
            case 'Range':
                if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                                      
                     $sqlQuery.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
                }
            break;
            case 'Compare':
                if((isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date']))){
                    $sqlQuery.=" {$visitLastActionTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['date'].' 23:59:59'."'";
                }
            break;
            default:
                if($condition!='' && is_numeric($condition)){
                    $sqlQuery.=" {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
                }
            break;
        }        
        $totalRecords=$this->wsmDB->get_var($sqlQuery);        
        return $totalRecords;
    }
    function fnGetReferralTotalPageViewCount($condition="",$arrParam=array()){
        $count=0; 
        $protocol='http://';
        if (is_ssl()) {
            $protocol='https://';
        }
        $homeURL=str_replace('www.','',site_url());     
        $homeURL=str_replace($protocol,'',$homeURL);  
        //$arrSearchEngines=$this->fnGetSearchEngineList();       
      //  $sqlQuery="SELECT COUNT(DISTINCT LU.refererUrlId) FROM {$this->tablePrefix}{$this->arrTables['LOG_UNIQUE']} LU LEFT JOIN {$this->tablePrefix}{$this->arrTables['LOG_URL']} UL ON LU.refererUrlId=UL.id WHERE LU.refererUrlId!=0 AND UL.url NOT LIKE '{$homeURL}%' AND";
        $sqlQuery="SELECT SUM(totalViews) FROM {$this->tablePrefix}_pageViews PV LEFT JOIN {$this->tablePrefix}{$this->arrTables['LOG_URL']} UL ON PV.refererUrlId=UL.id WHERE PV.refererUrlId!=0 AND UL.url NOT LIKE '{$homeURL}%' AND";

        if( isset( $arrParam['searchengine'] ) && $arrParam['searchengine'] ){
			$sqlReportQuery = "SELECT SUM( DR.total_page_views ) AS pageViews FROM {$this->tablePrefix}_datewise_report DR WHERE ";
		}else{
			$sqlReportQuery = "SELECT SUM( DR.total_page_views ) AS pageViews FROM {$this->tablePrefix}_datewise_report DR LEFT JOIN {$this->tablePrefix}{$this->arrTables['LOG_URL']} UL ON DR.url_id=UL.id  WHERE DR.url_id!=0 AND UL.url NOT LIKE '{$homeURL}%' AND";
		}
        $visitLastActionTime="CONVERT_TZ(PV.visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
		$whereCondition = '';
		if( isset( $arrParam['searchengine'] ) && $arrParam['searchengine'] ){
//            $sqlQuery .= " LEFT JOIN {$this->tablePrefix}_url_log on {$this->tablePrefix}_url_log.id = PV.refererUrlId ";
        }
        
        switch($condition){                      
            case 'Normal':        
            case 'Range':
                if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                                      
                     $whereCondition.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
                }               
            break;
            case 'Compare':
                if((isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date']))){
                    $whereCondition.=" {$visitLastActionTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['date'].' 23:59:59'."'";
                }
            break;
            default:
                if($condition!='' && is_numeric($condition)){
                    $whereCondition.=" {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
                }
            break;
        }                
		$sqlQuery .= $whereCondition;
		$sqlReportQuery .= $whereCondition;
        if( isset( $arrParam['searchengine'] ) && $arrParam['searchengine'] ){
            $sqlQuery .= ' AND ( ';
            $searchEngineResult = $this->fnGetSearchEngine();
            if( $searchEngineResult ){
                $sepeartor = '';
                foreach( $searchEngineResult as $searchEngine ){
                    $sqlQuery .= " $sepeartor UL.url LIKE '%".$searchEngine['name']."%' ";
                    $sepeartor = ' OR ';
                }
            }
            $sqlQuery .= ' ) ';
			$sqlReportQuery .= ' AND DR.search_engine != "" ';
        }
		
		$sqlReportQuery = str_replace('PV.visitLastActionTime','DR.date', $sqlReportQuery);
		
        $count=$this->wsmDB->get_var($sqlReportQuery);
        if(is_null($count) || $count == 0){
	        $count=$this->wsmDB->get_var($sqlQuery);
	        if(is_null($count)){
	            $count=0;
	        }
        }
        
        return $count;
    }
    function fnGetTotalVisitorsByCountries($limit=0,$countryId=''){
        $count=0;
        $sqlQuery="SELECT PV.countryId, C.name, count(*) as visitors FROM {$this->tablePrefix}_logUniqueVisit PV LEFT JOIN {$this->tablePrefix}_countries C ON PV.countryId=C.id WHERE 1";
        
		$sqlReportQuery = "SELECT DR.country AS countryId, C.name, SUM( DR.total_visitors ) AS visitors FROM {$this->tablePrefix}_datewise_report DR LEFT JOIN {$this->tablePrefix}_countries C ON DR.country=C.id  WHERE DR.country > 0 ";
		
        if($countryId!='' && is_numeric($countryId)){
            $sqlQuery.=" AND PV.countryId={$countryId}";
			$sqlReportQuery .= " AND DR.country={$countryId}";
        }else{
            $sqlQuery.=" GROUP BY PV.countryId";
			$sqlReportQuery .= " GROUP BY countryId";
        }
        $sqlQuery.=" ORDER BY visitors DESC";
        $sqlReportQuery.=" ORDER BY visitors DESC";
        if($limit!=0){
            $sqlQuery.=" LIMIT 0,{$limit}";
            $sqlReportQuery.=" LIMIT 0,{$limit}";
        }

		//echo $sqlQuery ."<br/>";
		//echo $sqlReportQuery ."<br/>";
		$result=$this->wsmDB->get_results($sqlReportQuery,ARRAY_A);
		if( !$result ){
			$result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
		}
		$result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
        return $result;
    }
    function fnGetTotalPageViewsByCountries($limit=0,$countryId=''){
        $count=0;
        $sqlQuery="SELECT PV.countryId, C.name, SUM(totalViews) as pageViews FROM {$this->tablePrefix}_pageViews PV LEFT JOIN {$this->tablePrefix}_countries C ON PV.countryId=C.id WHERE 1";
		$sqlReportQuery = "SELECT DR.country AS countryId,   C.name, SUM( DR.total_page_views ) AS pageViews FROM {$this->tablePrefix}_datewise_report DR LEFT JOIN {$this->tablePrefix}_countries C ON DR.country=C.id  WHERE DR.country > 0 ";
		
        if($countryId!='' && is_numeric($countryId)){
            $sqlQuery.=" AND PV.countryId={$countryId}";
			$sqlReportQuery .= " AND DR.country={$countryId}";
        }else{
            $sqlQuery.=" GROUP BY PV.countryId";
			$sqlReportQuery .= " GROUP BY countryId";
        }
        $sqlQuery.=" ORDER BY pageViews DESC";
        $sqlReportQuery.=" ORDER BY pageViews DESC";
        if($limit!=0){
            $sqlQuery.=" LIMIT 0,{$limit}";
            $sqlReportQuery.=" LIMIT 0,{$limit}";
        }

		//echo $sqlReportQuery;
		$result=$this->wsmDB->get_results($sqlReportQuery,ARRAY_A);
		if( !$result ){
			$result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
		}
		$result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
        return $result;
    }
    function fnGetTotalPageViewsByRegion($regionId=""){
        $sqlQuery="SELECT PV.regionId, R.name, SUM(totalViews) as pageViews FROM {$this->tablePrefix}_pageViews PV LEFT JOIN {$this->tablePrefix}_regions R ON PV.regionId=R.id WHERE 1";
        if($regionId!='' && is_numeric($regionId)){
            $sqlQuery.=" AND PV.regionId={$countryId}";
        }else{
            $sqlQuery.=" GROUP BY PV.regionId";
        }
        $result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
        return $result;
    }
    function fnGetTodaysPageViewsByHour($hour=""){
		global $wpdb;
        //$sqlQuery="SELECT hour, pageViews FROM {$this->tablePrefix}_hourWisePageViews";
		$hourCondition = '';
		$timezone = wsmCurrentGetTimezoneOffset();
		$date = wsmGetCurrentUTCDate('Y-m-d');
        if($hour!=""){
			$hourCondition = " AND hour(convert_tz(`{$this->tablePrefix}_pageViews`.`visitLastActionTime`,'+00:00','$timezone')) = ".sanitize_text_field($hour);
            //$sqlQuery.=" WHERE hour=".$hour;
            //$result=$this->wsmDB->get_row($sqlQuery,ARRAY_A);
            //return $result['pageViews'];
        }
		$sqlQuery = $wpdb->prepare( "select hour(convert_tz(`{$this->tablePrefix}_pageViews`.`visitLastActionTime`,'+00:00','%s')) AS `hour`,sum(`{$this->tablePrefix}_pageViews`.`totalViews`) AS `pageViews` from `{$this->tablePrefix}_pageViews` where (convert_tz(`{$this->tablePrefix}_pageViews`.`visitLastActionTime`,'+00:00','%s') >= convert_tz(%s,'+00:00','%s' ) ) $hourCondition group by hour(convert_tz(`{$this->tablePrefix}_pageViews`.`visitLastActionTime`,'+00:00','%s')) ", $timezone, $timezone, $date.' 00:00:00', $timezone, $timezone );

		if($hour!=""){
			$result=$this->wsmDB->get_row($sqlQuery,ARRAY_A);
			return $result['pageViews'];
		}
        $result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
        $retArray=wsmFormatHourlyStats('pageViews',$result);
        return $retArray;
    }
    function fnGetTodaysVisitorsByHour($condition="",$hour=""){
		global $wpdb;
        $viewName=$this->tablePrefix.'_hourWiseVisitors';
        if($condition=='FirstTime'){
            $viewName=$this->tablePrefix.'_hourWiseFirstVisitors';
        }
        //$sqlQuery="SELECT hour,visitors FROM {$viewName}";
		$hourCondition = '';

		$timezone = wsmCurrentGetTimezoneOffset();
		$date = wsmGetCurrentUTCDate('Y-m-d');
        if($hour!=""){
			$hourCondition = " AND hour(convert_tz(`{$this->tablePrefix}_logUniqueVisit`.`firstActionVisitTime`,'+00:00','$timezone')) = ".sanitize_text_field($hour) ;
			if($condition=='FirstTime'){
				$hourCondition = " AND hour(convert_tz(`{$this->tablePrefix}_uniqueVisitors`.`firstVisitTime`,'+00:00','$timezone')) = ".sanitize_text_field($hour);
			}
            //$sqlQuery.=" WHERE hour=".$hour;
            //$result=$this->wsmDB->get_row($sqlQuery,ARRAY_A);
            //return $result['visitors'];
        }

		$sqlQuery = $wpdb->prepare("select hour(convert_tz(`{$this->tablePrefix}_logUniqueVisit`.`firstActionVisitTime`,'+00:00','%s')) AS `hour`,count(0) AS `visitors` from `{$this->tablePrefix}_logUniqueVisit` where (convert_tz(`{$this->tablePrefix}_logUniqueVisit`.`firstActionVisitTime`,'+00:00','%s') >= convert_tz(%s,'+00:00','%s') ) $hourCondition group by hour(convert_tz(`{$this->tablePrefix}_logUniqueVisit`.`firstActionVisitTime`,'+00:00','%s'))", $timezone, $timezone, $date.' 00:00:00', $timezone, $timezone );
		if($condition=='FirstTime'){
			$sqlQuery = $wpdb->prepare("select hour(convert_tz(`{$this->tablePrefix}_uniqueVisitors`.`firstVisitTime`,'+00:00','%s')) AS `hour`,count(0) AS `visitors` from `{$this->tablePrefix}_uniqueVisitors` where (convert_tz(`{$this->tablePrefix}_uniqueVisitors`.`firstVisitTime`,'+00:00','%s') >= convert_tz(%s,'+00:00','%s') ) $hourCondition group by hour(convert_tz(`{$this->tablePrefix}_uniqueVisitors`.`firstVisitTime`,'+00:00','%s'))", $timezone, $timezone, $date.' 00:00:00', $timezone,  $timezone );
		}
		if($hour!=""){
			$result=$this->wsmDB->get_row($sqlQuery,ARRAY_A);
			return $result['visitors'];
		}
        $result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
		$retArray=wsmFormatHourlyStats('visitors',$result);
        return $retArray;
    }
    function fnGetTodaysBounceRateByHour($hour=""){
        $viewName=$this->tablePrefix.'_hourWiseBounceRate';
        $sqlQuery="SELECT * FROM {$viewName}";
        if($hour!=""){
            $sqlQuery.=" WHERE hour=".$hour;
            $result=$this->wsmDB->get_row($sqlQuery,ARRAY_A);
			if(isset($result))
			{
            return $result['bRateVisitors'];
			}else{
			return null;
			}
        }

        $result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
        $retArray=wsmFormatHourlyStats('bounceRate',$result);
        return $retArray;
    }
    function fnGetDayWisePageViewsByNumberOfDays($days=30, $where = array()){
		
		/*$sqlQuery = "SELECT date as recordDate, total_page_views AS pageViews FROM {$this->tablePrefix}_datewise_report WHERE normal = 1 AND date >= '".wsmGetDateByInterval('-'.$days.' days','Y-m-d')."' AND date <='".wsmGetCurrentDateByTimeZone('Y-m-d')."' ORDER BY date ASC";
		$result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);*/
		//if( !$result || !$days ){
	        $sqlQuery="SELECT recordDate, pageViews FROM {$this->tablePrefix}_dateWisePageViews WHERE ";
	        $sqlQuery.="recordDate >= '".wsmGetDateByInterval('-'.$days.' days','Y-m-d')."' AND recordDate<='".wsmGetCurrentDateByTimeZone('Y-m-d')."' ORDER BY recordDate ASC";
	        $result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
		//}
		
        $retArray=array();
        if(count($result)<$days){
            /*for($i=$days;$i>0;$i--){
                $date=wsmGetDateByInterval('-'.($i).' days','Y-m-d');
                $retArray[$date]=0;
            }*/
        }
        foreach($result as $key=>$row){
            $retArray[$row['recordDate']]=(int)$row['pageViews'];
        }      
        return $retArray;
    }
    function fnGetDayWiseFirstTimeVisitorCount($days=30, $where = array()){
		$sqlQuery = "SELECT date AS firstVisitTime, total_first_time_visitors AS visitors FROM {$this->tablePrefix}_datewise_report WHERE normal = 1 AND date >= '".wsmGetDateByInterval('-'.$days.' days','Y-m-d')."' AND date <='".wsmGetCurrentDateByTimeZone('Y-m-d')."' ORDER BY date ASC";
		$result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
		if( !$result ){
	        $sqlQuery="SELECT recordDate as firstVisitTime, visitors FROM {$this->tablePrefix}_dateWiseFirstVisitors WHERE 1 ";
	        $sqlQuery.="AND recordDate >= '".wsmGetDateByInterval('-'.$days.' days','Y-m-d')."' AND recordDate<='".wsmGetCurrentDateByTimeZone('Y-m-d')."'  ORDER BY recordDate ASC";
	        $result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
		}
		/*echo $sqlQuery."<br/>";*/
        $retArray=array();
        if(count($result)<$days){
            /*for($i=$days;$i>0;$i--){
                $date=wsmGetDateByInterval('-'.($i).' days','Y-m-d');
                $retArray[$date]=0;
            }*/
        }
        foreach($result as $key=>$row){
            $retArray[$row['firstVisitTime']]=(int)$row['visitors'];
        }
        return $retArray;
    }
    function fnGetDayWiseVisitorsByNumberOfDays($days=30, $where = array()){
		/*$sqlQuery = "SELECT date, total_visitors AS visitors FROM {$this->tablePrefix}_datewise_report WHERE normal = 1 AND date >= '".wsmGetDateByInterval('-'.$days.' days','Y-m-d')."' AND date <='".wsmGetCurrentDateByTimeZone('Y-m-d')."' ORDER BY date ASC";
		$result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);*/
		
		//if( !$result ){
	        $sqlQuery="SELECT recordDate as date, visitors FROM {$this->tablePrefix}_dateWiseVisitors WHERE ";
	        $sqlQuery.="recordDate >= '".wsmGetDateByInterval('-'.$days.' days','Y-m-d')."' AND recordDate<='".wsmGetCurrentDateByTimeZone('Y-m-d')."'  ORDER BY recordDate ASC";
	        $result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
		//}
		/*echo $sqlQuery."<br/>";*/
        $retArray=array();
        if(count($result)<$days){
            /*for($i=$days;$i>0;$i--){
                $date=wsmGetDateByInterval('-'.($i).' days','Y-m-d');
                $retArray[$date]=0;
            }*/
        }
        foreach($result as $key=>$row){
            $retArray[$row['date']]=(int)$row['visitors'];
        }
        return $retArray;
    }
    function fnGetDayWiseBounceRateByNumberOfDays($days=30, $where = array()){
		$sqlQuery = "SELECT date AS recordDate, ( (total_bounce/total_visitors) * 100 ) AS bRateVisitors FROM {$this->tablePrefix}_datewise_report WHERE normal = 1 AND date >= '".wsmGetDateByInterval('-'.$days.' days','Y-m-d')."' AND date <='".wsmGetCurrentDateByTimeZone('Y-m-d')."' ORDER BY date ASC";
		$result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
		if( !$result ){
	        $sqlQuery="SELECT * FROM {$this->tablePrefix}_dateWiseBounceRate WHERE 1 ";
	        $sqlQuery.="AND recordDate >= '".wsmGetDateByInterval('-'.$days.' days','Y-m-d')."' AND recordDate<='".wsmGetCurrentDateByTimeZone('Y-m-d')."'  ORDER BY recordDate ASC";
	        $result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
		}
        
        $retArray=array();
        if(count($result)<$days){
            for($i=$days;$i>0;$i--){
                $date=wsmGetDateByInterval('-'.($i).' days','Y-m-d');
                $retArray[$date]=0;
            }
        }
        foreach($result as $key=>$row){
            $retArray[$row['recordDate']]=(int)$row['bRateVisitors'];
        }
        return $retArray;
    }
    function fnGetBounceRateByDate($date){
        $sqlQuery="SELECT * FROM {$this->tablePrefix}_dateWiseBounceRate WHERE recordDate='".$date."'";
        $result=$this->wsmDB->get_row($sqlQuery,ARRAY_A);
        if($result){
            return (int)$result['bRateVisitors'];
        }
        return 0;
    }
    function fnGetAverageVisitLength($condition=""){
        $visitLastActionTime="CONVERT_TZ(visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
        $sqlQuery="SELECT AVG(TIMESTAMPDIFF(SECOND,firstActionVisitTime,visitLastActionTime)) as avgTotalLength FROM {$this->tablePrefix}{$this->arrTables['LOG_UNIQUE']} ";
        switch($condition){
            case 'Today':
                $sqlQuery.="WHERE {$visitLastActionTime} >= '".wsmGetCurrentDateByTimeZone('Y-m-d 00:00:00')."'";
                break;
            case 'Last2Months':
                $sqlQuery.="WHERE {$visitLastActionTime} >= '".wsmGetDateByInterval('-2 months','Y-m-d 00:00:00')."'";
                break;
            default:
                break;
        }
        $time=$this->wsmDB->get_var($sqlQuery);
        return $time;
    }
    function fnGetHourlyReportByDateNameTimeZone($date,$name = '',$newTimeZone = ''){
		
		if( empty( $name ) ){
			$sqlQuery = 'SELECT hour, total_page_views, total_visitors, total_first_time_visitors, total_bounce FROM '.$this->tablePrefix.'_datewise_report WHERE hour > 0 AND date = "'.$date.'" ORDER BY hour ASC';
			$result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);          
			if( $result ){
				return $result;
			}	
		}
		
        $sqlQuery='SELECT * FROM '.$this->tablePrefix.'_dailyHourlyReport WHERE name="'.$name.'" AND reportDate="'.$date.'" AND timezone="'.$newTimeZone.'"';
        $result=$this->wsmDB->get_row($sqlQuery,ARRAY_A);
        $startDateTime=$date.' 00:00:00';
        $endDateTime=$date.' 23:59:59';
        $visitLastActionTime="CONVERT_TZ(visitLastActionTime,'+00:00','".$newTimeZone."')";
        $action='insert';
        $updateId=0;
        if($this->wsmDB->num_rows > 0){
            $arrResult=unserialize($result['content']);
            if(count($arrResult)<24){
                $action='update';
                $updateId=$result['id'];
            }else{
                return $arrResult;
            }
        }
        $sql='';
        switch($name){
            case 'hourWisePageViews':
                $sql="SELECT HOUR({$visitLastActionTime}) as hour, SUM(totalViews) as pageViews FROM ".$this->tablePrefix."_pageViews WHERE {$visitLastActionTime} >= '".$startDateTime."' AND {$visitLastActionTime} <= '".$endDateTime."' GROUP BY HOUR({$visitLastActionTime})";
                break;
            case 'hourWiseBounce':
                $sql="SELECT HOUR({$visitLastActionTime}) as hour, COUNT(*) as bounce FROM ".$this->tablePrefix."_bounceVisits WHERE {$visitLastActionTime} >= '".$startDateTime."' AND {$visitLastActionTime} <= '".$endDateTime."' GROUP BY HOUR({$visitLastActionTime})";
                break;
            case 'hourWiseVisitors':
                $sql="SELECT HOUR({$visitLastActionTime}) as hour, COUNT(*) as visitors FROM ".$this->tablePrefix."_logUniqueVisit WHERE {$visitLastActionTime} >= '".$startDateTime."' AND {$visitLastActionTime} <= '".$endDateTime."' GROUP BY HOUR({$visitLastActionTime})";
                break;
            case 'hourWiseFirstVisitors':
                $firstVisitTime="CONVERT_TZ(firstVisitTime,'+00:00','".$newTimeZone."')";
                $sql="SELECT HOUR({$firstVisitTime}) as hour, COUNT(*) as visitors FROM ".$this->tablePrefix."_uniqueVisitors WHERE {$firstVisitTime} >= '".$startDateTime."' AND {$firstVisitTime} <= '".$endDateTime."' GROUP BY HOUR({$firstVisitTime})";
                break;
            case 'hourWiseBounceRate':
                $sql="SELECT hwb.hour, hwb.bounce, hwp.pageViews, hwv.visitors, ((hwb.bounce/hwp.pageViews)*100) AS bRatePageViews, ((hwb.bounce/hwv.visitors)*100) AS bRateVisitors FROM ".$this->tablePrefix."_hourWiseBounce hwb LEFT JOIN ".$this->tablePrefix."_hourWisePageViews hwp ON hwb.hour=hwp.hour LEFT JOIN ".$this->tablePrefix."_hourWiseVisitors hwv ON hwb.hour=hwv.hour";
                break;
        }
        if($sql!=''){
            $newResult=$this->wsmDB->get_results($sql,ARRAY_A);
            if($this->wsmDB->num_rows > 0){
                $sResult=serialize($newResult);
                if($action=='update' && $updateId!=0){
                    $newSql="UPDATE {$this->tablePrefix}_dailyHourlyReport SET content='{$sResult}' WHERE id={$updateId}";
                }else{
                     $newSql="INSERT INTO {$this->tablePrefix}_dailyHourlyReport (name, reportDate, content, timezone) VALUES ('{$name}','{$date}','{$sResult}','{$newTimeZone}')";
                     $this->fnLogError();
                }
                $this->wsmDB->query($newSql);
                return $newResult;
            }
        }
        return false;
    } 
    function fnGetMonthlyReportByYear($year){
        if(isset($this->arrCachedStats['monthly_'.$year]) && is_array($this->arrCachedStats['monthly_'.$year]) && count($this->arrCachedStats['monthly_'.$year])>0){
            return $this->arrCachedStats['monthly_'.$year];
        }       
        $newTimeZone=wsmCurrentGetTimezoneOffset();
        $arrLineData=array('pageViews'=>array(),'visitors'=>array(),'firstTime'=>array(),'Bounce'=>array(),'ppv'=>array(),'newVisitor'=>array(),'avgOnline'=>array(),'XLabels'=>array());
		
		$allInfo = $this->fnGetMonthlyReportByYearNameTimeZone( $year );
		$pageViews = $visitors = $firstTimeVisitors = $bounceRate = $arrKeys = array();
		if( $allInfo ){
			foreach( $allInfo as $info ){
				if( $info['month'] - 1 > count($visitors)  ){
					for( $i = 0; $i < ( $info['month'] - 1 ) ; $i++ ){
						$visitors[ $i ] = 0;
						$pageViews[ $i ] = 0;
						$firstTimeVisitors[ $i ] = 0;
						$bounceRate[ $i ] = 0;	
					}
				}
				$visitors[ $info['month'] ] = $info['total_visitors'];
				$pageViews[ $info['month'] ] = $info['total_page_views'];
				$firstTimeVisitors[ $info['month'] ] = $info['total_first_time_visitors'];
				$bounceRate[ $info['month'] ] = $info['total_bounce'];
			}
		}else{
	        $visitors=$this->fnGetMonthlyReportByYearNameTimeZone($year,'monthWiseVisitors',$newTimeZone);
	        $pageViews=$this->fnGetMonthlyReportByYearNameTimeZone($year,'monthWisePageViews',$newTimeZone);
	        $firstTimeVisitors=$this->fnGetMonthlyReportByYearNameTimeZone($year,'monthWiseFirstVisitors',$newTimeZone);
	        $bounceRate=$this->fnGetMonthlyReportByYearNameTimeZone($year,'monthWiseBounceRate',$newTimeZone);
		}
	    $arrKeys=array_keys($pageViews);	
		
        for($i=0;$i<12;$i++){
			if( $allInfo ){
				$j = $i + 1;
				if( $arrKeys[ $i ] != $j ){
					$visitors[ $i ] = 0;
					$pageViews[ $i ] = 0;
					$firstTimeVisitors[ $i ] = 0;
					$bounceRate[ $i ] = 0;
				}else{
					$visitors[ $i ] = $visitors[ $arrKeys[ $j ] ];
					$pageViews[ $i ] = $pageViews[ $arrKeys[ $j ] ];
					$firstTimeVisitors[ $i ] = $firstTimeVisitors[ $arrKeys[ $j ] ];
					$bounceRate[ $i ] = $bounceRate[ $arrKeys[ $j ] ];
				}
			}
			
            array_push($arrLineData['pageViews'],$pageViews[$arrKeys[$i]]);
            array_push($arrLineData['visitors'],$visitors[$arrKeys[$i]]);
            array_push($arrLineData['firstTime'],$firstTimeVisitors[$arrKeys[$i]]);
            array_push($arrLineData['Bounce'],(float)number_format_i18n($bounceRate[$arrKeys[$i]],2));
            $arrAddStats=wsmFnStatCalculations('Montly',$pageViews[$arrKeys[$i]],$firstTimeVisitors[$arrKeys[$i]],$visitors[$arrKeys[$i]]);            
            
            array_push($arrLineData['ppv'],(float)number_format_i18n($arrAddStats['ppv'],2));
            array_push($arrLineData['newVisitor'],(float)number_format_i18n($arrAddStats['newVisitor'],2));
            array_push($arrLineData['avgOnline'],(float)number_format_i18n($arrAddStats['avgOnline'],2));
            array_push($arrLineData['XLabels'],(string)($i+1));
        }
        $this->arrCachedStats['monthly_'.$year]=$arrLineData;
        return $arrLineData;
    }
    function fnGetMonthlyStatsByRange($fromYear,$toYear){
        $newTimeZone=  new DateTimeZone(wsmGetTimezoneString());
        $fromDate=$fromYear.'-01-01';
        $toDate=$toYear.'12-31';
        $begin = new DateTime( $fromDate,$newTimeZone );
        $end = new DateTime( $toDate,$newTimeZone );
        $end = $end->modify( '+1 year' ); 
        $interval = new DateInterval('P1Y');
        $daterange = new DatePeriod($begin, $interval ,$end);
        $arrDailyStats=array();
        foreach($daterange as $date){
             $year=$date->format('Y');
             $arrStats=$this->fnGetMonthlyReportByYear($year);
             array_push($arrDailyStats,array('date'=>$date->format("Y"),'stats'=>$arrStats));
        }
        return $arrDailyStats;
    }
    function fnGetTotalStatsByMonth($name,$recordMonth=""){
        if($recordMonth==""){
            $recordMonth=wsmGetCurrentDateByTimeZone('Y-m');
        }        
        switch($name){
            case 'monthWisePageViews':                
                $sql="SELECT pageViews FROM {$this->tablePrefix}_monthWisePageViews WHERE recordMonth='".$recordMonth."'";
                $columnName='pageViews';
                break;
            case 'monthWiseVisitors':                
                $sql="SELECT visitors FROM {$this->tablePrefix}_monthWiseVisitors WHERE recordMonth='".$recordMonth."'";  
                $columnName='visitors';
                break;
            case 'monthWiseFirstVisitors':
                $sql="SELECT visitors FROM {$this->tablePrefix}_monthWiseFirstVisitors WHERE recordMonth='".$recordMonth."'";
                $columnName='visitors';
                break;
            case 'monthWiseBounceRate':
                $sql="SELECT bRateVisitors FROM {$this->tablePrefix}_monthWiseBounceRate WHERE recordMonth = '".$recordMonth."'"; 
                $columnName='bRateVisitors';             
                break;
        }        
        $returnCount=0;       
        $count=$this->wsmDB->get_var($sql);
        if(!is_null($count)){
            $returnCount=$count;
        }        
        return $returnCount;  
    }
    function fnGetMonthlyReportByYearNameTimeZone($year,$name = '',$newTimeZone = ''){    
		
		$sqlQuery = 'SELECT DATE_FORMAT( date, "%c" ) AS month, total_page_views, total_visitors, total_first_time_visitors, total_bounce FROM '.$this->tablePrefix.'_monthwise_report WHERE normal = 1 AND YEAR(date) = '.$year.' ORDER BY date ASC';
		$result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);          
		if( $result ){
			return $result;
		}
		
        $sqlQuery='SELECT * FROM '.$this->tablePrefix.'_yearlyMonthlyReport WHERE name="'.$name.'" AND reportYear="'.$year.'" AND timezone="'.$newTimeZone.'"';
        $result=$this->wsmDB->get_row($sqlQuery,ARRAY_A);          
        $currentDate=wsmGetCurrentDateByTimeZone('Y-m-d');
        $arrDate=explode('-',$currentDate);
        $arrResult=array();                
        if($this->wsmDB->num_rows > 0){
            $arrResult=unserialize($result['content']);
            if($year==$arrDate[0]){
                $currentMonth=$arrDate[0].'-'.$arrDate[1];
                $cMonthStats=$this->fnGetTotalStatsByMonth($name,$currentMonth);
                $arrResult[$currentMonth]=$cMonthStats;
            }
            return $arrResult;
        }
        $maxMonth=$year==$arrDate[0]?$arrDate[1]:12;
        for($i=0;$i<12;$i++){
            $month=$year.'-'.sprintf('%02d', ($i+1));
            $cMonthStats=$this->fnGetTotalStatsByMonth($name,$month);
            $arrResult[$month]=$cMonthStats;
        }
        if(count($arrResult)>0){
            $sResult=serialize($arrResult);
            $newSql="INSERT INTO {$this->tablePrefix}_yearlyMonthlyReport (name, reportYear, content, timezone) VALUES ('{$name}','{$year}','{$sResult}','{$newTimeZone}')";
            $this->wsmDB->query($newSql);
            $this->fnLogError();
            return $arrResult;
        }        
        return false;
    }
    function fnGetDailyReportByMonth($yearMonth){
		
        if(isset($this->arrCachedStats['daily_'.$yearMonth]) && is_array($this->arrCachedStats['daily_'.$yearMonth]) && count($this->arrCachedStats['daily_'.$yearMonth])>0){
            return $this->arrCachedStats['daily_'.$yearMonth];
        }
        $newTimeZone=wsmCurrentGetTimezoneOffset();
        $arrLineData=array('pageViews'=>array(),'visitors'=>array(),'firstTime'=>array(),'Bounce'=>array(),'ppv'=>array(),'newVisitor'=>array(),'avgOnline'=>array(),'XLabels'=>array());
		$allInfo = $this->fnGetDailyReportByMonthNameTimeZone( $yearMonth );
		if( $allInfo ){
			$checkFirstRow = true;
			foreach( $allInfo as $row ){
				if( $checkFirstRow ){
					$checkFirstRow = false;
					$currentDate = date('j', strtotime($row['date']));
					$currentMonthYear = date('Y-m', strtotime($row['date']));
					if( $currentDate > 1 ){
						for( $i = 1; $i <= $currentDate; $i++ ){
							$date = $currentMonthYear.( $i < 10 ? '0'.$i : $i );
							$visitors[ $date ] = 0;
							$pageViews[ $date ] = 0;
							$firstTimeVisitors[ $date ] = 0;
							$bounceRate[ $date ] = 0;
						}	
					}
				}
				
				$visitors[ $row['date'] ] = $row['total_visitors'];
				$pageViews[ $row['date'] ] = $row['total_page_views'];
				$firstTimeVisitors[ $row['date'] ] = $row['total_first_time_visitors'];
				$bounceRate[ $row['date'] ] = $row['total_bounce'];
			}
		}else{
	        /*$visitors=$this->fnGetDailyReportByMonthNameTimeZone($yearMonth,'dayWiseVisitors',$newTimeZone);
	        $pageViews=$this->fnGetDailyReportByMonthNameTimeZone($yearMonth,'dayWisePageViews',$newTimeZone);
	        $firstTimeVisitors=$this->fnGetDailyReportByMonthNameTimeZone($yearMonth,'dayWiseFirstVisitors',$newTimeZone);
	        $bounceRate=$this->fnGetDailyReportByMonthNameTimeZone($yearMonth,'dayWiseBounceRate',$newTimeZone);	*/
		}
        
        $arrKeys= is_array($pageViews) ? array_keys($pageViews) : $pageViews ;
        $noOfDays=wsmGetDateByTimeStamp('t',strtotime($yearMonth.'-01'));
        for($i=0;$i<$noOfDays;$i++){
            array_push($arrLineData['pageViews'],$pageViews[$arrKeys[$i]]);
            array_push($arrLineData['visitors'],$visitors[$arrKeys[$i]]);
            array_push($arrLineData['firstTime'],$firstTimeVisitors[$arrKeys[$i]]);
            array_push($arrLineData['Bounce'],(float)number_format_i18n($bounceRate[$arrKeys[$i]],2));
            $arrAddStats=wsmFnStatCalculations('Daily',$pageViews[$arrKeys[$i]],$firstTimeVisitors[$arrKeys[$i]],$visitors[$arrKeys[$i]]);            
            
            array_push($arrLineData['ppv'],(float)number_format_i18n($arrAddStats['ppv'],2));
            array_push($arrLineData['newVisitor'],(float)number_format_i18n($arrAddStats['newVisitor'],2));
            array_push($arrLineData['avgOnline'],(float)number_format_i18n($arrAddStats['avgOnline'],2));
            array_push($arrLineData['XLabels'],(string)($i+1));
        }
        $this->arrCachedStats['daily_'.$yearMonth]=$arrLineData;
        return $arrLineData;
    }   
    function fnGetDailyReportByMonthNameTimeZone($yearMonth,$name = '',$newTimeZone = ''){
		
		if( empty( $name ) ){
			$sqlQuery = 'SELECT dr.`date`, dr.total_page_views, dr.total_visitors, dr.total_first_time_visitors, dr.total_bounce FROM '.$this->tablePrefix.'_monthwise_report AS dr WHERE date_format(dr.date,"%Y-%m") = "'.$yearMonth.'"  AND dr.hour > 0';
			$result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
			if( $result ){
				return $result;
			}else{
				$sqlQuery = 'SELECT dr.`date`, sum(dr.total_page_views) AS total_page_views , sum(dr.total_visitors) AS total_visitors, sum(dr.total_first_time_visitors) AS total_first_time_visitors , sum(dr.total_bounce) AS total_bounce FROM '.$this->tablePrefix.'_datewise_report AS dr WHERE date_format(dr.date,"%Y-%m") = "'.$yearMonth.'"  AND dr.hour > 0 GROUP BY dr.date';
				$result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
				if( $result ){
					return $result;
				}
			}	
		}
		
        $sqlQuery='SELECT * FROM '.$this->tablePrefix.'_monthlyDailyReport WHERE name="'.$name.'" AND reportMonthYear="'.$yearMonth.'" AND timezone="'.$newTimeZone.'"';
        $result=$this->wsmDB->get_row($sqlQuery,ARRAY_A);  
        $startDateTime=$yearMonth.'-01';
        $requestedMonthEnd=wsmGetDateByTimeStamp('Y-m-t',strtotime($startDateTime));
        $arrMonthEnd=explode('-',$requestedMonthEnd);
        $noOfDays=$arrMonthEnd[2];
        $endDateTime=$yearMonth.'-'.$noOfDays;
        $currentMonthEnd=wsmGetCurrentDateByTimeZone('Y-m-t');
        $visitLastActionTime="CONVERT_TZ(visitLastActionTime,'+00:00','".$newTimeZone."')";
        $action='insert';
        $updateId=0;
        if($this->wsmDB->num_rows > 0 && ($requestedMonthEnd!=$currentMonthEnd)){
            $arrResult=unserialize($result['content']);
            return $arrResult;
        }
        $sql='';
        $columnName='';
        switch($name){
            case 'dayWisePageViews':                
                $sql="SELECT recordDate, pageViews FROM {$this->tablePrefix}_dateWisePageViews WHERE recordDate >= '".$startDateTime."' AND recordDate<='".$endDateTime."'";
                $columnName='pageViews';
                break;
            case 'dayWiseVisitors':                
                $sql="SELECT recordDate, visitors FROM {$this->tablePrefix}_dateWiseVisitors WHERE recordDate >= '".$startDateTime."' AND recordDate<='".$endDateTime."'";
                $columnName='visitors';
                break;
            case 'dayWiseFirstVisitors':
                $sql="SELECT recordDate, visitors FROM {$this->tablePrefix}_dateWiseFirstVisitors WHERE 1 AND recordDate >= '".$startDateTime."' AND recordDate<='".$endDateTime."'";
                $columnName='visitors';
                break;
            case 'dayWiseBounceRate':
                $sql="SELECT recordDate, bRateVisitors FROM {$this->tablePrefix}_dateWiseBounceRate WHERE 1 AND recordDate >= '".$startDateTime."' AND recordDate<='".$endDateTime."'";   
                $columnName='bRateVisitors';             
                break;
        }
       
        if($sql!=''){            
            $retArray=array();
            for($i=0;$i<$noOfDays;$i++){
                $date=$yearMonth.'-'.sprintf('%02d',($i+1));
                $retArray[$date]=0;
            }        
            $newResult=$this->wsmDB->get_results($sql,ARRAY_A);            
            if($this->wsmDB->num_rows > 0){                              
                foreach($newResult as $key=>$row){
                    $retArray[$row['recordDate']]=(int)$row[$columnName];
                }               
                $sResult=serialize($retArray);
                if($requestedMonthEnd!=$currentMonthEnd){                   
                     $newSql="INSERT INTO {$this->tablePrefix}_monthlyDailyReport (name, reportMonthYear, content, timezone) VALUES ('{$name}','{$yearMonth}','{$sResult}','{$newTimeZone}')";
                     $this->wsmDB->query($newSql);
                     $this->fnLogError();
                }                                
            }
            return $retArray;
        }
        return false;
    }
    function fnGetHistoricalDayStatsByDays($days, $where = array()){
        if(isset($this->arrCachedStats['daily']) && is_array($this->arrCachedStats['daily']) && count($this->arrCachedStats['daily'])>0){
            //return $this->arrCachedStats['daily'];
        }
        $arrLineData=array('pageViews'=>array(),'visitors'=>array(),'firstTimeVisitors'=>array(),'Bounce'=>array(),'ppv'=>array(),'newVisitor'=>array(),'avgOnline'=>array());
        $pageViews=$this->fnGetDayWisePageViewsByNumberOfDays($days, $where );
        $visitors=$this->fnGetDayWiseVisitorsByNumberOfDays($days, $where );
        $firstTimeVisitors=$this->fnGetDayWiseFirstTimeVisitorCount($days, $where );
        $bounceRate=$this->fnGetDayWiseBounceRateByNumberOfDays($days, $where );
		
        $currentDayPageViews=$this->fnGetDayWisePageViewsByNumberOfDays(0, $where );
        $currentDayVisitors=$this->fnGetDayWiseVisitorsByNumberOfDays(0, $where );
        $currentDayFirstTimeVisitors=$this->fnGetDayWiseFirstTimeVisitorCount(0, $where );
        $currentDayBounceRate=$this->fnGetDayWiseBounceRateByNumberOfDays(0, $where );
		
		$pageViews = array_merge( $pageViews, $currentDayPageViews );		
		$visitors = array_merge( $visitors, $currentDayVisitors );		
		$firstTimeVisitors = array_merge( $firstTimeVisitors, $currentDayFirstTimeVisitors );		
		$bounceRate = array_merge( $bounceRate, $currentDayBounceRate );		
		
        //$pageViews=array_reverse($pageViews,true);
        //$visitors=array_reverse($visitors,true);
        //$firstTimeVisitors=array_reverse($firstTimeVisitors,true);
        $arrKeys=array_keys($pageViews);
        for($i=0;$i<=$days;$i++){
            @array_push($arrLineData['pageViews'],array($arrKeys[$i],$pageViews[$arrKeys[$i]]));
            @array_push($arrLineData['visitors'],array($arrKeys[$i],$visitors[$arrKeys[$i]]));
            @array_push($arrLineData['firstTimeVisitors'],array($arrKeys[$i],$firstTimeVisitors[$arrKeys[$i]]));
            @array_push($arrLineData['Bounce'],array($arrKeys[$i],(float)number_format_i18n($bounceRate[$arrKeys[$i]],2)));
            //$arrAddStats=wsmFnStatCalculations('Daily',$pageViews[$arrKeys[$i]],$firstTimeVisitors[$arrKeys[$i]],$visitors[$arrKeys[$i]]);            
            if(isset($arrAddStats['ppv']))
			{
            @array_push($arrLineData['ppv'],array($arrKeys[$i],(float)number_format_i18n($arrAddStats['ppv'],2)));
            @array_push($arrLineData['newVisitor'],array($arrKeys[$i],(float)number_format_i18n($arrAddStats['newVisitor'],2)));
            @array_push($arrLineData['avgOnline'],array($arrKeys[$i],(float)number_format_i18n($arrAddStats['avgOnline'],2)));
			}
        }
        $this->arrCachedStats['daily']=$arrLineData;
        return $arrLineData;
    }
    function fnGetHistoricalHourlyStatsByDay($day){
        $startDateTime=wsmGetDateByInterval('-'.$day.' days','Y-m-d');
        $newTimeZone=wsmCurrentGetTimezoneOffset();
        $arrStats=array('pageViews'=>array(),'visitors'=>array(),'firstTime'=>array());
        $pageViews=$this->fnGetHourlyReportByDateNameTimeZone($startDateTime,'hourWisePageViews',$newTimeZone);
        $visitors=$this->fnGetHourlyReportByDateNameTimeZone($startDateTime,'hourWiseVisitors',$newTimeZone);
        $firstTime=$this->fnGetHourlyReportByDateNameTimeZone($startDateTime,'hourWiseFirstVisitors',$newTimeZone);
        $arrStats['pageViews']=wsmFormatHourlyStats('pageViews',$pageViews);
        $arrStats['visitors']=wsmFormatHourlyStats('visitors',$visitors);
        $arrStats['firstTime']=wsmFormatHourlyStats('visitors',$firstTime);
        return $arrStats;
    } 
    function fnGetHistoricalHourlyStatsByDate($date){
        if(isset($this->arrCachedStats['hourlyDate_'.$date]) && is_array($this->arrCachedStats['hourlyDate_'.$date]) && count($this->arrCachedStats['hourlyDate_'.$date])>0){
            //return $this->arrCachedStats['hourlyDate_'.$date];
        }
        $startDateTime=$date;
        $newTimeZone=wsmCurrentGetTimezoneOffset();
        $arrStats=array('pageViews'=>array(),'visitors'=>array(),'firstTime'=>array());
		$allInfo = $this->fnGetHourlyReportByDateNameTimeZone( $startDateTime );
		if( $allInfo ){
			foreach( $allInfo as $key => $row ){
				$arrStats['pageViews'][]['pageViews'] = $row['total_page_views'];	
				$arrStats['visitors'][]['visitors'] = $row['total_visitors'];	
				$arrStats['firstTime'][]['visitors'] = $row['total_first_time_visitors'];	
				$arrStats['bounce'][]['bounce'] = $row['total_bounce'];	
			}
		}else{
	        $pageViews=$this->fnGetHourlyReportByDateNameTimeZone($startDateTime,'hourWisePageViews',$newTimeZone);
	        $visitors=$this->fnGetHourlyReportByDateNameTimeZone($startDateTime,'hourWiseVisitors',$newTimeZone);
	        $firstTime=$this->fnGetHourlyReportByDateNameTimeZone($startDateTime,'hourWiseFirstVisitors',$newTimeZone);
	        $bounce=$this->fnGetHourlyReportByDateNameTimeZone($startDateTime,'hourWiseBounce',$newTimeZone);
			
	        $arrStats['pageViews']=wsmFormatHourlyStats('pageViews',$pageViews);
	        $arrStats['visitors']=wsmFormatHourlyStats('visitors',$visitors);
	        $arrStats['firstTime']=wsmFormatHourlyStats('visitors',$firstTime);
	        $arrStats['bounce']=wsmFormatHourlyStats('bounce',$bounce);
		}
        $arrData=array('pageViews'=>array(),'visitors'=>array(),'firstTime'=>array(),'Bounce'=>array(),'ppv'=>array(),'newVisitor'=>array(),'avgOnline'=>array(),'XLabels'=>array());
        for($j=0;$j<24;$j++){
			 $bounceRate = 0;
            $arrStats['pageViews'][$j]['pageViews']=(int)$arrStats['pageViews'][$j]['pageViews'];
            $arrStats['visitors'][$j]['visitors']=(int)$arrStats['visitors'][$j]['visitors'];
            $arrStats['firstTime'][$j]['visitors']=(int)$arrStats['firstTime'][$j]['visitors'];
            
            if($arrStats['bounce'][$j]['bounce']>0 && $arrStats['visitors'][$j]['visitors']>0){
                $bounceRate=($arrStats['bounce'][$j]['bounce']/$arrStats['visitors'][$j]['visitors'])*100;
            }
            $arrAddStats=wsmFnStatCalculations('Hourly',$arrStats['pageViews'][$j]['pageViews'],$arrStats['firstTime'][$j]['visitors'],$arrStats['visitors'][$j]['visitors']);  
            array_push($arrData['visitors'],$arrStats['visitors'][$j]['visitors']);
            array_push($arrData['pageViews'],$arrStats['pageViews'][$j]['pageViews']);
            array_push($arrData['firstTime'],$arrStats['firstTime'][$j]['visitors']);
            array_push($arrData['Bounce'],(float)number_format_i18n($bounceRate,2));
            array_push($arrData['ppv'],(float)number_format_i18n($arrAddStats['ppv'],2));
            array_push($arrData['newVisitor'],(float)number_format_i18n($arrAddStats['newVisitor'],2));
            array_push($arrData['avgOnline'],(float)number_format_i18n($arrAddStats['avgOnline'],2));
            array_push($arrData['XLabels'],(string)$j);             
        }
        $this->arrCachedStats['hourlyDate_'.$date]=$arrData;
        return $arrData;
    }    
    function fnGetCurrentHourStats(){
        $h=wsmGetCurrentDateByTimeZone('H');
        $arrCurrentStats=array('pageViews'=>0,'visitors'=>0,'firstTime'=>0,'Bounce'=>0,'ppv'=>0,'newVisitor'=>0,'avgOnline'=>0);
        $arrCurrentStats['pageViews']=intval($this->fnGetTodaysPageViewsByHour($h));
        $arrCurrentStats['firstTime']=intval($this->fnGetTodaysVisitorsByHour('FirstTime',$h));
        $arrCurrentStats['visitors']=intval($this->fnGetTodaysVisitorsByHour(null,$h));
        $arrCurrentStats['Bounce']=number_format_i18n($this->fnGetTodaysBounceRateByHour($h),0);
        $arrAddStats=wsmFnStatCalculations('Hourly',$arrCurrentStats['pageViews'],$arrCurrentStats['firstTime'],$arrCurrentStats['visitors']);
        $arrCurrentStats['ppv']=number_format_i18n($arrAddStats['ppv'],2);
        $arrCurrentStats['newVisitor']=number_format_i18n($arrAddStats['newVisitor'],2);
        $arrCurrentStats['avgOnline']=number_format_i18n($arrAddStats['avgOnline'],2);        
        return $arrCurrentStats;
    }
    function fnGetCurrentDayStats(){
        $arrCurrentStats=array('pageViews'=>0,'visitors'=>0,'firstTime'=>0,'ppv'=>0,'newVisitor'=>0,'avgOnline'=>0,'Bounce'=>0);
        $todayPageViews=$this->fnGetTotalPageViewCount('Today');
        $todayVisitors=$this->fnGetTotalVisitorsCount('Today');
        $firstTimeVisitors=$this->fnGetFirstTimeVisitorCount('Today');
        $arrAddStats=wsmFnStatCalculations('Daily',$todayPageViews,$firstTimeVisitors,$todayVisitors);        
        $arrCurrentStats['pageViews']=number_format_i18n($todayPageViews,0);
        $arrCurrentStats['visitors']=number_format_i18n($todayVisitors,0);
        $arrCurrentStats['firstTime']=number_format_i18n($firstTimeVisitors,0);
        $arrCurrentStats['ppv']=number_format_i18n($arrAddStats['ppv'],2);
        $arrCurrentStats['newVisitor']=number_format_i18n($arrAddStats['newVisitor'],2);
        $arrCurrentStats['avgOnline']=number_format_i18n($arrAddStats['avgOnline'],2);
        $arrCurrentStats['Bounce']=$this->fnGetBounceRateByDate(wsmGetCurrentDateByTimeZone('Y-m-d'));
        return $arrCurrentStats;
    }
    function fnGetCurrentDayHourlyStats(){
        $arrCommon=array('pageViews'=>array(),'visitors'=>array(),'firstTime'=>array());
        $arrAdd=array('Bounce'=>array(),'ppv'=>array(),'newVisitor'=>array(),'avgOnline'=>array(),'XLabels'=>array(),'forecast'=>$arrCommon);
        $arrChartStats=array('today'=>array_merge($arrCommon,$arrAdd),'yesterday'=>$arrCommon,'day7before'=>$arrCommon,'day14before'=>$arrCommon);
        $arrTodaysStats=array_merge($arrCommon,array('Bounce'=>array()));
        $arrTodaysStats['pageViews']=$this->fnGetTodaysPageViewsByHour();
        $arrTodaysStats['firstTime']=$this->fnGetTodaysVisitorsByHour('FirstTime');
        $arrTodaysStats['visitors']=$this->fnGetTodaysVisitorsByHour();
        $arrTodaysStats['Bounce']=$this->fnGetTodaysBounceRateByHour();
        $arrTodaysStats['forecast']=$this->fnGetTodaysForeCastData();

        $arr1DayBefore=$this->fnGetHistoricalHourlyStatsByDay(1);
        $arr7DayBefore=$this->fnGetHistoricalHourlyStatsByDay(7);
        $arr14DayBefore=$this->fnGetHistoricalHourlyStatsByDay(14);
        for($i=0; $i<24; ++$i){
            $ppv=$newVisitor=$avgOnline=0;
            $h=wsmGetCurrentDateByTimeZone('H');
            if($i>$h){
                $tFirstTime=(int)$arrTodaysStats['forecast']['firstTime'][$i];
                $tVisitors=(int)$arrTodaysStats['forecast']['visitors'][$i];
                $tPageViews=(int)$arrTodaysStats['forecast']['pageViews'][$i];
            }else{
                $tFirstTime=(int)$arrTodaysStats['firstTime'][$i]['visitors'];
                $tVisitors=(int)$arrTodaysStats['visitors'][$i]['visitors'];
                $tPageViews=(int)$arrTodaysStats['pageViews'][$i]['pageViews'];
            }
            array_push($arrChartStats['today']['firstTime'],$tFirstTime);
            array_push($arrChartStats['today']['visitors'],$tVisitors);
            array_push($arrChartStats['today']['pageViews'],$tPageViews);
            array_push($arrChartStats['today']['Bounce'],(int)$arrTodaysStats['Bounce'][$i]['bounceRate']);
            array_push($arrChartStats['today']['XLabels'],(string)$i);
            $arrAddStats=wsmFnStatCalculations('Hourly',$tPageViews,$tFirstTime,$tVisitors);            
            array_push($arrChartStats['today']['ppv'],(float)number_format_i18n($arrAddStats['ppv'],2));
            array_push($arrChartStats['today']['newVisitor'],(float)number_format_i18n($arrAddStats['newVisitor'],2));
            array_push($arrChartStats['today']['avgOnline'],(float)number_format_i18n($arrAddStats['avgOnline'],2));

            array_push($arrChartStats['yesterday']['pageViews'],(int)$arr1DayBefore['pageViews'][$i]['pageViews']);
            array_push($arrChartStats['yesterday']['visitors'],(int)$arr1DayBefore['visitors'][$i]['visitors']);
            array_push($arrChartStats['yesterday']['firstTime'],(int)$arr1DayBefore['firstTime'][$i]['visitors']);

            array_push($arrChartStats['day7before']['pageViews'],(int)$arr7DayBefore['pageViews'][$i]['pageViews']);
            array_push($arrChartStats['day7before']['visitors'],(int)$arr7DayBefore['visitors'][$i]['visitors']);
            array_push($arrChartStats['day7before']['firstTime'],(int)$arr7DayBefore['firstTime'][$i]['visitors']);

            array_push($arrChartStats['day14before']['pageViews'],(int)$arr14DayBefore['pageViews'][$i]['pageViews']);
            array_push($arrChartStats['day14before']['visitors'],(int)$arr14DayBefore['visitors'][$i]['visitors']);
            array_push($arrChartStats['day14before']['firstTime'],(int)$arr14DayBefore['firstTime'][$i]['visitors']);
        }
        return $arrChartStats;
    }
    function fnGetLastDaysHourlyAverageStats($cDate="",$noOfDays=7){
        if($cDate=""){
            $cDate=wsmGetCurrentDateByTimeZone();
        }
        $arrStats=array('pageViews'=>array(),'visitors'=>array(),'firstTime'=>array());
        $arrAverage=array('pageViews'=>array(),'visitors'=>array(),'firstTime'=>array());
        for($i=0;$i<=$noOfDays;++$i){
            $arrDayBefore=$this->fnGetHistoricalHourlyStatsByDay($i);
            for($j=0; $j<24; ++$j){
                $arrStats['pageViews'][$j][$i]=(int)$arrDayBefore['pageViews'][$j]['pageViews'];
                $arrStats['visitors'][$j][$i]=(int)$arrDayBefore['visitors'][$j]['visitors'];
                $arrStats['firstTime'][$j][$i]=(int)$arrDayBefore['firstTime'][$j]['visitors'];
            }
        }
       /* for($j=0; $j<24; ++$j){
            $arrAverage['pageViews'][$j]=round(wsmGetAverageOfArray($arrStats['pageViews'][$j]));
            $arrAverage['visitors'][$j]=round(wsmGetAverageOfArray($arrStats['visitors'][$j]));
            $arrAverage['firstTime'][$j]=round(wsmGetAverageOfArray($arrStats['firstTime'][$j]));
        }*/
        return ($arrStats);
    }
    function fnGetDailyStatsByMonthRange($fromMonthYear,$toMonthYear){
        $newTimeZone=  new DateTimeZone(wsmGetTimezoneString());
        $fromDate=$fromMonthYear.'-01';
        $toDate=$toMonthYear.'-01';
        $begin = new DateTime( $fromDate,$newTimeZone );
        $end = new DateTime( $toDate,$newTimeZone );
        $end = $end->modify( '+1 month' ); 
        $interval = new DateInterval('P1M');
        $daterange = new DatePeriod($begin, $interval ,$end);
        $arrDailyStats=array();
         foreach($daterange as $date){
             $monthYear=$date->format('Y-m');
             $arrStats=$this->fnGetDailyReportByMonth($monthYear);
             array_push($arrDailyStats,array('date'=>$date->format("F Y"),'stats'=>$arrStats));
        }
        return $arrDailyStats;
    }
    function fnGetHourlyStatsByDateRange($fromDate,$toDate){
        $newTimeZone=  new DateTimeZone(wsmGetTimezoneString());
        $begin = new DateTime( $fromDate,$newTimeZone );
        $end = new DateTime( $toDate,$newTimeZone );
        $end = $end->modify( '+1 day' ); 
        $interval = new DateInterval('P1D');
        $daterange = new DatePeriod($begin, $interval ,$end);
        $arrHourlyStats=array();
        foreach($daterange as $date){
            $arrStats=$this->fnGetHistoricalHourlyStatsByDate($date->format('Y-m-d'));
            array_push($arrHourlyStats,array('date'=>$date->format("d F Y"),'stats'=>$arrStats));
        }
        return $arrHourlyStats;
    }
    function fnGetTodaysForeCastData(){
        if(isset($this->arrCachedStats['todayHourly']) && is_array($this->arrCachedStats['todayHourly']) && count($this->arrCachedStats['todayHourly'])>0){
            return $this->arrCachedStats['todayHourly'];
        }
        $noOfDays= $this->fnGetNDayFromFirstVisitActionTime();
        $arrAverage=$this->fnGetLastDaysHourlyAverageStats($noOfDays);
        
        $arrForeCast=array('pageViews'=>array(),'visitors'=>array(),'firstTime'=>array());
        $xArray=array_keys($arrAverage['pageViews'][0]);
        
       
        for($i=0;$i<24;$i++){
            $arrForeCast['firstTime'][$i]=round(wsmFnCalculateForeCastData($xArray,$arrAverage['firstTime'][$i],count($arrAverage['firstTime'][$i])+1));
            $arrForeCast['visitors'][$i]=round(wsmFnCalculateForeCastData($xArray,$arrAverage['visitors'][$i],count($arrAverage['visitors'][$i])+1));
            $arrForeCast['pageViews'][$i]=round(wsmFnCalculateForeCastData($xArray,$arrAverage['pageViews'][$i],count($arrAverage['pageViews'][$i])+1));
        }
        
       /*
        //$rArray=array_keys($arrAverage['pageViews'][0]);
        $h=wsmGetCurrentDateByTimeZone('H');
        if($h>1){
            //$rArray=array_slice($xArray,$h);
            //array_splice($xArray,$h);
           // array_splice($arrAverage['firstTime'],$h);
           // array_splice($arrAverage['visitors'],$h);
           // array_splice($arrAverage['pageViews'],$h);
        }
        $arrForeCast['firstTime']=array_fill(0,$h,0);
        $arrForeCast['visitors']=array_fill(0,$h,0);
        $arrForeCast['pageViews']=array_fill(0,$h,0);
          echo '<pre>';
        print_r($arrForeCast);
        echo '</pre>';

        $arrForeCast['firstTime']=array_merge($arrForeCast['firstTime'],array_map('round',wsmFnCalculateForeCastData($xArray,$arrAverage['firstTime'],$rArray)));
        $arrForeCast['visitors']=array_merge($arrForeCast['visitors'],array_map('round',wsmFnCalculateForeCastData($xArray,$arrAverage['visitors'],$rArray)));
        $arrForeCast['pageViews']=array_merge($arrForeCast['pageViews'],array_map('round',wsmFnCalculateForeCastData($xArray,$arrAverage['pageViews'],$rArray)));*/
        $this->arrCachedStats['todayHourly']= $arrForeCast;
        return $arrForeCast;
    }
    function fnGetFirstVisitDate(){
        $sql='SELECT MIN(visitLastActionTime) FROM '.$this->tablePrefix.$this->arrTables['LOG_UNIQUE'];
        $date=$this->wsmDB->get_var($sql);
        return $date;
    }
    function fnGetNDayFromFirstVisitActionTime($total=false){
        $date=$this->fnGetFirstVisitDate();
        $noOfDays=7;
        $diffInDays=7;
        if(!is_null($date) || $date!=''){
            $diffInDays=wsmDateDifference($date);
        }
        if($diffInDays<$noOfDays){
            $noOfDays=$diffInDays;
        }
        if($total){
            return $diffInDays;
        }
        return $noOfDays;
    }
    function fnGetRecentVisitedPages($limit=10){
       /* if(isset($this->arrCachedStats['recentPages']) && is_array($this->arrCachedStats['recentPages']) && count($this->arrCachedStats['recentPages'])>0){
            return $this->arrCachedStats['recentPages'];
        }*/
        $newTimeZone=wsmCurrentGetTimezoneOffset();
        $serverTime="CONVERT_TZ(serverTime,'+00:00','".$newTimeZone."')";
        $currentDate=wsmGetCurrentDateByTimeZone();
        $sql='SELECT *,TIMEDIFF("'.$currentDate.'",'.$serverTime.') as timeDiff FROM '.$this->tablePrefix.'_visitorInfo';
        $sql.=' WHERE '.$serverTime.' >= "'.wsmGetDateByInterval('-'.WSM_ONLINE_SESSION.' minutes').'"';
        $sql.=' ORDER BY serverTime DESC limit 0,'.$limit;        
        $result=$this->wsmDB->get_results($sql,ARRAY_A);
        return $result;
    }
    function fnGetTotalBrowsingPages(){
        $newTimeZone=wsmCurrentGetTimezoneOffset();        
        $serverTime="CONVERT_TZ(serverTime,'+00:00','".$newTimeZone."')";
        $sql='SELECT COUNT(DISTINCT urlId) FROM '.$this->tablePrefix.'_visitorInfo';
        $sql.=' WHERE '.$serverTime.' >= "'.wsmGetDateByInterval('-'.WSM_ONLINE_SESSION.' minutes').'"';
        //echo $sql.=' GROUP by URLId';                
        $result=$this->wsmDB->get_var($sql);
        return $result;
    }
    function fnGetPopularPages($limit=10){
        /*if(isset($this->arrCachedStats['recentPages']) && is_array($this->arrCachedStats['recentPages']) && count($this->arrCachedStats['recentPages'])>0){
            return $this->arrCachedStats['recentPages'];
        }*/
        $newTimeZone=wsmCurrentGetTimezoneOffset();
        $serverTime="CONVERT_TZ(VI.serverTime,'+00:00','".$newTimeZone."')";
        $sql='SELECT VI.URLId, VI.title, VI.url as fullURL, sum(VI.hits) as stotalViews, VI.visitLastActionTime FROM '.$this->tablePrefix.'_visitorInfo VI';
        $sql.=' WHERE '.$serverTime.' >= "'.wsmGetDateByInterval('-'.WSM_ONLINE_SESSION.' minutes').'"';
        $sql.=' GROUP by VI.URLId ORDER BY stotalViews DESC limit 0,'.$limit;                
        $result=$this->wsmDB->get_results($sql,ARRAY_A);
        return $result;
    }
    function fnGetPopularReferrers($limit=10){
        /*if(isset($this->arrCachedStats['recentPages']) && is_array($this->arrCachedStats['recentPages']) && count($this->arrCachedStats['recentPages'])>0){
            return $this->arrCachedStats['recentPages'];
        }*/
        $newTimeZone=wsmCurrentGetTimezoneOffset();
        $visitLastActionTime="CONVERT_TZ(visitLastActionTime,'+00:00','".$newTimeZone."')";
        $sql='SELECT LU.refererUrlId, CONCAT(UL.protocol,UL.url) as fullURL, '.$visitLastActionTime.' as visitLastActionTime,COUNT(LU.refererUrlId) AS totalReferrers FROM '.$this->tablePrefix.'_logUniqueVisit  LU  LEFT JOIN '.$this->tablePrefix.'_url_log UL ON LU.refererUrlId=UL.id  WHERE LU.refererUrlId!=0';
       // $sql='SELECT * FROM '.$this->tablePrefix.'_popularReferrers ORDER BY totalReferrers DESC';
        $sql.=' AND '.$visitLastActionTime.' >= "'.wsmGetDateByInterval('-'.WSM_ONLINE_SESSION.' minutes').'"';        
        $sql.='GROUP BY LU.refererUrlId ORDER BY totalReferrers DESC, visitLastActionTime  DESC limit 0,'.$limit;        
        $result=$this->wsmDB->get_results($sql,ARRAY_A);
        return $result;
    }
    function fnCorrectDatabaseTables(){
        $sql='SELECT * FROM '.$this->tablePrefix.$this->arrTables['LOG_URL'].' WHERE title IS NULL OR searchEngine=0 OR searchEngine IS NULL or toolBar=0 OR toolBar IS NULL';
        $result=$this->wsmDB->get_results($sql,ARRAY_A);
        $arrParam=array();
        $arrSiteURL=$this->fnReturnURLElements(site_url());
        foreach($result as $row){
            if($row['pageId']!=0 AND !is_null($row['pageId'])){
                $arrParam['title']=get_the_title( $row['pageId'] );
            }
            if($row['searchEngine']==0 OR is_null($row['searchEngine'])){
                $arrParam['searchEngine']=$this->fnGetSearchEngineID($row['url']);
            }
            if($row['toolBar']==0 OR is_null($row['toolBar'])){
				
				$myurl = !empty($row['url']) ? $this->fnGetToolBarID($row['url']) : 0;
                $arrParam['toolBar']= $myurl;
            }
            if($row['pageId']==0 OR is_null($row['pageId'])){
                $fullURL=$row['protocol'].$row['url'];
                if (strpos($row['protocol'].$row['url'],$arrSiteURL['url']) > -1) {
                    $pageId=wsmUrlToPostid($fullURL);
                    if($pageId!=0){
                        $arrParam['pageId']=wsmUrlToPostid($fullURL);
                    }
                }
            }
            $this->fnUpdateURLParameters($row['id'],$arrParam);
        }
        $sql='SELECT id,CONCAT(protocol,url) as fullURL FROM '.$this->tablePrefix.$this->arrTables['LOG_URL'].' WHERE url LIKE "%'.$arrSiteURL['url'].'%" and (pageId IS NULL OR pageId=0)';
        $result=$this->wsmDB->get_results($sql,ARRAY_A);
        foreach($result as $row){
            $pageId=wsmUrlToPostid($row['fullURL']);
            if($pageId!=0){
                $this->fnUpdateURLParameters($row['id'],array('pageId'=>$pageId));
            }
        }
    }
    
    /**
     * get referral site states
     */
    function getReferralSiteStats( $condition, $arrRequest ){

        $visitLastActionTime="CONVERT_TZ(PV.visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
        $conditional_query = '';
        //$sqlQuery="select date_format( PV.visitLastActionTime, '%Y-%m-%d') AS accessDate,  count( PV. visitId) AS total_visitors,  SUM(PV.totalViews)  AS total_page_views from  {$this->tablePrefix}_pageViews PV WHERE uv.refererUrlId = ".$arrRequest['id']." AND ";
		//if( isset($arrRequest['searchengine']) && $arrRequest['searchengine'] ){
			$arrRequest['from'] = wsmGetDateByInterval('-1 Month','Y-m-d');
		//}
		$arrParam = $arrRequest;
        switch($condition){                      
            case 'Normal':        
            case 'Range':
                if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                                      
                     $conditional_query.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
                }
            break;
            case 'Compare':
                if((isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date']))){
                    $conditional_query.=" {$visitLastActionTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['date'].' 23:59:59'."'";
                }
            break;
            default:
                if($condition!='' && is_numeric($condition)){
                    $conditional_query.=" {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
                }
            break;
        }
        //$sqlQuery.="GROUP BY accessDate";
        //*/
        
        $sql = 'select date_format( PV.visitLastActionTime, "%Y-%m-%d") AS accessDate,  count( PV. visitId) AS total_visitors,  SUM(PV.totalViews)  AS total_page_views from '.$this->tablePrefix.'_pageViews PV WHERE PV.refererUrlId = '.$arrRequest['id'].'  AND '.$conditional_query.' GROUP BY accessDate order by accessDate DESC';
       // echo $sql."<br>";
        $referralUrlData = $this->wsmDB->get_results($sql,ARRAY_A);
        
        $conditional_query = str_replace( 'PV.visitLastActionTime', 'uv.firstVisitTime', $conditional_query ); //"CONVERT_TZ(uv.firstVisitTime,'+00:00','".WSM_TIMEZONE."')";
        $sql = 'select date_format( uv.firstVisitTime, "%Y-%m-%d") AS accessDate, count( uv.id ) AS total_unique_visitors from '.$this->tablePrefix.'_uniqueVisitors uv WHERE uv.refererUrlId = '.$arrRequest['id'].' AND '.$conditional_query.' GROUP BY accessDate order by accessDate DESC';
        
        //echo $sql."<br>";
        $referralUrlData2 = $this->wsmDB->get_results($sql,ARRAY_A);
        
        $dataResult = array();
        
        foreach( $referralUrlData as $data ){
            $dataResult[ $data['accessDate'] ] = array( 'total_visitors' => $data['total_visitors'], 'total_page_views' => $data['total_page_views'] );   
        }
        
        foreach( $referralUrlData2 as $data ){
            if( key_exists( $data['accessDate'], $dataResult )  ){
                $dataResult[ $data['accessDate'] ]['total_unique_visitors'] = $data['total_unique_visitors'];
            }else{
                $dataResult[ $data['accessDate'] ]['total_unique_visitors'] = 0;
            }
        }
        
        return $dataResult;
        
    }
	
	/*
	*	Get first and last access date
	*/
	function getReferralSiteStartEndVisit( $id ){
		$visitLastActionTime="CONVERT_TZ(PV.visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
		$result = array( 'first_visit' => '', 'last_visit' => '' );
        $sql = 'select '.$visitLastActionTime.' AS accessDate from '.$this->tablePrefix.'_pageViews PV WHERE PV.refererUrlId = '.$id.' order by accessDate ASC LIMIT 0,1';
		$data = $this->wsmDB->get_var($sql);
		if( $data ){
			$result['first_visit'] = date('d M Y', strtotime( $data ) );
		}
        $sql = 'select '.$visitLastActionTime.' AS accessDate from '.$this->tablePrefix.'_pageViews PV WHERE PV.refererUrlId = '.$id.' order by accessDate DESC LIMIT 0,1';
		$data = $this->wsmDB->get_var($sql);
		if( $data ){
			$result['last_visit'] = date('d M Y', strtotime( $data ) );
		}
		return $result;
	}
	
	/**
	* Get visitor's info
	*/
	function getVisitorsInfo( $condition, $arrRequest ){

        $visitLastActionTime="CONVERT_TZ(LU.visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
        $conditional_query = '';
		
		$arrParam = $arrRequest;
        /*switch($condition){                      
            case 'Normal':        
            case 'Range':
                if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                                      
                     $conditional_query.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
                }
            break;
            case 'Compare':
                if((isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date']))){
                    $conditional_query.=" {$visitLastActionTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['date'].' 23:59:59'."'";
                }
            break;
            default:
                if($condition!='' && is_numeric($condition)){
                    $conditional_query.=" {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
                }
            break;
        }*/
            if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                                      
                 $conditional_query.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
            }
		$data = array();
		$sql = 'SELECT LU.oSystemId AS id, OS.name, LU.deviceType, count(*) as total from '.$this->tablePrefix.'_logUniqueVisit LU 
		left join '.$this->tablePrefix.'_oSystems OS on LU.oSystemId = OS.id WHERE '.$conditional_query.' AND LU.oSystemId > 0 and OS.name is not null and OS.name!="-" group by LU.oSystemId order by total desc';

		$sqlOSReportQuery = 'SELECT LU.operating_system AS id, OS.name, sum(total_page_views) as total from '.$this->tablePrefix.'_datewise_report LU left join '.$this->tablePrefix.'_oSystems OS on OS.id = LU.operating_system WHERE '.$conditional_query.' and OS.name is not null and OS.name!="-" AND LU.operating_system > 0 group by LU.operating_system order by total desc';
		$sqlOSReportQuery = str_replace( 'LU.visitLastActionTime', 'LU.date', $sqlOSReportQuery ); 	
		//echo $sql."<br/>";
		//echo $sqlOSReportQuery."<br/>";
		$osResult=$this->wsmDB->get_results($sqlOSReportQuery,ARRAY_A);
		if( !$osResult ){
			$osResult = $this->wsmDB->get_results($sql,ARRAY_A);
		}
		$data[__('OS','wp-stats-manager')] = $osResult;
		
		$sql = 'SELECT LU.browserId AS id, WB.name, count(*) as total from '.$this->tablePrefix.'_logUniqueVisit LU 
		left join '.$this->tablePrefix.'_browsers WB on LU.browserId = WB.id WHERE '.$conditional_query.' AND LU.browserId > 0 and WB.name is not null and WB.name!="-" group by LU.browserId order by total desc';
		
		$sqlBroReportQuery = 'SELECT LU.browser AS id, WB.name, sum(total_page_views) as total from '.$this->tablePrefix.'_datewise_report LU left join '.$this->tablePrefix.'_browsers WB on LU.browser = WB.id WHERE '.$conditional_query.' AND LU.browser > 0 and WB.name is not null and WB.name!="-" group by LU.browser order by total desc';
		$sqlBroReportQuery = str_replace( 'LU.visitLastActionTime', 'LU.date', $sqlBroReportQuery ); 	
		//echo $sqlBroReportQuery."<br/>";
		$broResult=$this->wsmDB->get_results($sqlBroReportQuery,ARRAY_A);
		if( !$broResult ){
			$broResult = $this->wsmDB->get_results($sql,ARRAY_A);
		}
		$data[__('Browser','wp-stats-manager')] = $broResult;
		 
		$sql = 'SELECT LU.resolutionId AS id, LU.deviceType, WR.name, count(*) as total from '.$this->tablePrefix.'_logUniqueVisit LU 
		left join '.$this->tablePrefix.'_resolutions WR on LU.resolutionId = WR.id WHERE '.$conditional_query.' AND LU.resolutionId > 0 and WR.name is not null and WR.name!="-" group by LU.resolutionId order by total desc';
		
		$sqlResReportQuery = 'SELECT DR.screen AS id, LU.deviceType, WR.name, sum(total_page_views) as total from '.$this->tablePrefix.'_datewise_report DR 
		left join '.$this->tablePrefix.'_resolutions WR on DR.screen = WR.id left join '.$this->tablePrefix.'_logUniqueVisit LU on LU.id=DR.screen WHERE '.$conditional_query.' AND DR.screen > 0 and WR.name is not null and WR.name!="-" group by DR.screen order by total desc';
		$sqlResReportQuery = str_replace( 'LU.visitLastActionTime', 'DR.date', $sqlResReportQuery ); 
		if( ! current_user_can('edit_others_pages') ){
			$sqlResReportQuery .= ' LIMIT 0, 10 ';
		}
		//echo $sqlResReportQuery."<br/>";
		$resResult=$this->wsmDB->get_results($sqlResReportQuery,ARRAY_A);
		if( !$resResult ){
			$resResult = $this->wsmDB->get_results($sql,ARRAY_A);
		}
		$data[__('Screen Resolution','wp-stats-manager')] = $resResult;
		return $data;
			 
	}

    function fnGetReferralTotalVisitorsCountByOSBrowser($condition="",$arrParam=array()){               

        $visitLastActionTime="CONVERT_TZ(LU.visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
        $conditional_query = '';		
		//$arrParam = $arrRequest;
        /*switch($condition){                      
            case 'Normal':        
            case 'Range':
                if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                                      
                     $conditional_query.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
                }
            break;
            case 'Compare':
                if((isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date']))){
                    $conditional_query.=" {$visitLastActionTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['date'].' 23:59:59'."'";
                }
            break;
            default:
                if($condition!='' && is_numeric($condition)){
                    $conditional_query.=" {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
                }
            break;
        }*/
            if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                                      
                 $conditional_query.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
            }
		$data = array();
      
		if( isset( $arrParam['rtype'] ) ){
		    switch( $arrParam['rtype'] ){
			    case 'OS':
				    $conditional_query .= ' AND LU.oSystemId = '.$arrParam['id'];
				    break;
			    case 'Browser':
				    $conditional_query .= ' AND LU.browserId = '.$arrParam['id'];
				    break;
			    case 'Screen Resolution':
				    $conditional_query .= ' AND LU.resolutionId = '.$arrParam['id'];
				    break;
		    }
        }
		$sql = 'SELECT LU.* from '.$this->tablePrefix.'_logUniqueVisit LU WHERE '.$conditional_query;
	  // echo $sql.'<br />';
        $result=$this->wsmDB->get_results($sql,ARRAY_A);        
        $count=$this->wsmDB->num_rows;
        if(is_null($count)){
            $count=0;
        }
        return $count;
    }
	
	
	/*
	*	Get first and last access date by visitor device
	*/
	function getReferralDeviceStartEndVisit( $id, $type ){
      
		if( isset( $type ) ){
		    switch( $type ){
			    case 'OS':
				    $conditional_query .= ' LU.oSystemId = '.$id;
				    break;
			    case 'Browser':
				    $conditional_query .= '  LU.browserId = '.$id;
				    break;
			    case 'Screen Resolution':
				    $conditional_query .= '  LU.resolutionId = '.$id;
				    break;
		    }
        }
		
		$result = array( 'first_visit' => '', 'last_visit' => '' );
		$sql = 'SELECT LU.firstActionVisitTime AS accessDate from '.$this->tablePrefix.'_logUniqueVisit LU WHERE '.$conditional_query.' order by accessDate ASC LIMIT 0,1';
		$data = $this->wsmDB->get_var($sql);
		if( $data ){
			$result['first_visit'] = date('d M Y', strtotime( $data ) );
		}
		$sql = 'SELECT LU.firstActionVisitTime AS accessDate from '.$this->tablePrefix.'_logUniqueVisit LU WHERE '.$conditional_query.' order by accessDate DESC LIMIT 0,1';
		$data = $this->wsmDB->get_var($sql);
		if( $data ){
			$result['last_visit'] = date('d M Y', strtotime( $data ) );
		}
		return $result;
	}

    function fnGetReferralTotalVisitorsCountByBroswerOS($condition="",$arrParam=array()){
             
        $sqlQuery="SELECT DISTINCT LU.visitorId FROM {$this->tablePrefix}{$this->arrTables['LOG_UNIQUE']} LU WHERE 1  AND ";
        $visitLastActionTime="CONVERT_TZ(LU.visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
        switch($condition){            
            case 'Compare':
                if(isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date'])){
                    $sqlQuery.=" {$visitLastActionTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['date'].' 23:59:59'."'";
                }
            break;
            case 'Normal':
            case 'Range':
                if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                    
                     $sqlQuery.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";  
                }                
            break;
            default:
                if($condition!='' && is_numeric($condition)){
                    $sqlQuery.="  {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
                }
                if(wsmValidateDateTime($condition)){
                    $sqlQuery.="  {$visitLastActionTime} >= '".$condition.' 00:00:00'."'";
                }
                break;
        } 
		if( isset( $arrParam['rtype'] ) ){
			$id = 0;
			$compare = '>';
			if( isset( $arrParam['id'] ) ){
				$id = $arrParam['id'];
				$compare = '=';
			}

			switch( $arrParam['rtype'] ){
				case 'OS':
					$sqlQuery .= ' AND LU.oSystemId '.$compare.$id;
					break;
				case 'Browser':
					$sqlQuery .= ' AND LU.browserId '.$compare.$id;
					break;
				case 'Screen Resolution':
					$sqlQuery .= ' AND LU.resolutionId '.$compare.$id;
					break;
			}
		}
 
		//echo $sqlQuery.'<br />';
        $result=$this->wsmDB->get_results($sqlQuery,ARRAY_A);        
        $count=$this->wsmDB->num_rows;
        if(is_null($count)){
            $count=0;
        }
        return $count;
    }
	
	function fnGetReferralKeywords( $arrParam=array() ){
		$sqlQuery = "SELECT LV.id, LV.keyword, UL.protocol, UL.url , LV.serverTime, LUV.ipAddress
						FROM {$this->tablePrefix}{$this->arrTables['LOG_VISIT']} AS LV 
						LEFT JOIN {$this->tablePrefix}{$this->arrTables['LOG_URL']} AS UL ON LV.refererUrlId = UL.id 
						LEFT JOIN {$this->tablePrefix}{$this->arrTables['LOG_UNIQUE']} AS LUV ON LUV.id = LV.visitId
						WHERE UL.searchEngine > 0 AND LV.keyword != '-' AND LV.keyword != ''
						ORDER BY UL.id DESC";
		$sqlCountQuery = "SELECT COUNT(*) FROM {$this->tablePrefix}{$this->arrTables['LOG_VISIT']} AS LV 
						LEFT JOIN {$this->tablePrefix}{$this->arrTables['LOG_URL']} AS UL ON LV.refererUrlId = UL.id 
						LEFT JOIN {$this->tablePrefix}{$this->arrTables['LOG_UNIQUE']} AS LUV ON LUV.id = LV.visitId
						WHERE UL.searchEngine > 0 AND LV.keyword != '-' AND LV.keyword != ''
						ORDER BY UL.id DESC";
		//$allResult=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
		$totalRecords=$this->wsmDB->get_var($sqlCountQuery);
		$cPage=isset($arrParam['currentPage'])?$arrParam['currentPage']:1;
		$offset=($cPage-1)*WSM_PAGE_LIMIT;
		$sqlQuery.=" LIMIT {$offset},".WSM_PAGE_LIMIT;
		$arrResult['data']=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
		if(isset($arrParam['currentPage']) && isset($arrParam['adminURL'])){
			$arrResult['pagination']=wsmFnGetPagination($totalRecords,$arrParam['currentPage'],$arrParam['adminURL']);
		}
		return $arrResult;           
	}
	
	function getReferralOSStats( $condition = 'Normal', $arrRequest = array() ){

        $visitLastActionTime="CONVERT_TZ(PV.visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
        $conditional_query = '';
        //$sqlQuery="select date_format( PV.visitLastActionTime, '%Y-%m-%d') AS accessDate,  count( PV. visitId) AS total_visitors,  SUM(PV.totalViews)  AS total_page_views from  {$this->tablePrefix}_pageViews PV WHERE uv.refererUrlId = ".$arrRequest['id']." AND ";
		//if( isset($arrRequest['searchengine']) && $arrRequest['searchengine'] ){
			$arrRequest['from'] = wsmGetDateByInterval('-1 Month','Y-m-d');
		//}
		
		$arrParam = $arrRequest;
        switch($condition){                      
            case 'Normal':        
            case 'Range':
                if( (isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from']) ) && 
					( isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']) )
				)
				{          
                     $conditional_query.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
                }
            break;
            case 'Compare':
                if((isset($arrParam['date']) && $arrParam['date']!='' && wsmValidateDateTime($arrParam['date']))){
                    $conditional_query.=" {$visitLastActionTime} >= '".$arrParam['date'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['date'].' 23:59:59'."'";
                }
            break;
            default:
                if($condition!='' && is_numeric($condition)){
                    $conditional_query.=" {$visitLastActionTime} >= '".wsmGetDateByInterval('-'.$condition.' days','Y-m-d 00:00:00')."'";
                }
            break;
        }
        //$sqlQuery.="GROUP BY accessDate";
        //*/
		$sqlQuery = '';
		if( isset( $arrParam['rtype'] ) ){
			$id = 0;
			$compare = '>';
			if( isset( $arrParam['id'] ) ){
				$id = $arrParam['id'];
				$compare = '=';
			}
			switch( $arrParam['rtype'] ){
			case 'OS':
				$sqlQuery .= ' AND LU.oSystemId '.$compare.$id;
				break;
			case 'Browser':
				$sqlQuery .= ' AND LU.browserId '.$compare.$id;
				break;
			case 'Screen Resolution':
				$sqlQuery .= ' AND LU.resolutionId '.$compare.$id;
				break;
			}
		}
		
        $sql = 'select date_format( PV.visitLastActionTime, "%Y-%m-%d") AS accessDate,  count( PV. visitId) AS total_visitors,  
SUM(PV.totalViews)  AS total_page_views from '.$this->tablePrefix.'_pageViews AS PV LEFT JOIN '.$this->tablePrefix.'_logUniqueVisit AS LU on PV.visitid = LU.id WHERE '.$conditional_query.$sqlQuery.' GROUP BY accessDate order by accessDate DESC';

        $referralUrlData = $this->wsmDB->get_results($sql,ARRAY_A);
        
        $conditional_query = str_replace( 'PV.visitLastActionTime', 'UV.firstVisitTime', $conditional_query ); 
		
		$sql = 'select date_format( UV.firstVisitTime, "%Y-%m-%d") AS accessDate, count( UV.id ) AS total_unique_visitors 
from '.$this->tablePrefix.'_uniqueVisitors AS UV LEFT JOIN '.$this->tablePrefix.'_logUniqueVisit AS LU on UV.id = LU.id WHERE '.$conditional_query.$sqlQuery.' GROUP BY accessDate order by accessDate DESC';
		
        $referralUrlData2 = $this->wsmDB->get_results($sql,ARRAY_A);
        
        $dataResult = array();
        
        foreach( $referralUrlData as $data ){
            $dataResult[ $data['accessDate'] ] = array( 'total_visitors' => $data['total_visitors'], 'total_page_views' => $data['total_page_views'] );   
        }
        
        foreach( $referralUrlData2 as $data ){
            if( key_exists( $data['accessDate'], $dataResult )  ){
                $dataResult[ $data['accessDate'] ]['total_unique_visitors'] = $data['total_unique_visitors'];
            }else{
                $dataResult[ $data['accessDate'] ]['total_unique_visitors'] = 0;
            }
        }
		return $dataResult;
	}
	
	function getGeoLocationInfo( $condition, $arrRequest ){

        $visitLastActionTime="CONVERT_TZ(LUV.visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
        $conditional_query = '';
		
		$arrParam = $arrRequest;
        
        if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                                      
             $conditional_query.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
        }
		//LEFT JOIN {$this->tablePrefix}_uniqueVisitors AS UV ON UV.id = LUV.id 
		if( isset( $arrParam['location'] ) ){
			
			$sql = "select LUV.city AS name,  WC.alpha2Code,  LUV3.total_visitors,  SUM(PV.totalViews)  AS total_page_views, LUV2.total_unique_visitors from {$this->tablePrefix}_pageViews AS PV 
					LEFT JOIN {$this->tablePrefix}_logUniqueVisit AS LUV on PV.visitid = LUV.id 
					LEFT JOIN {$this->tablePrefix}_countries AS WC ON WC.id = LUV.countryId 
					LEFT JOIN ( select  LUV.city, count( UV.id ) AS total_unique_visitors 
					from {$this->tablePrefix}_uniqueVisitors AS UV 
					LEFT JOIN {$this->tablePrefix}_logUniqueVisit AS LUV on UV.id = LUV.id WHERE $conditional_query AND LUV.city != '' GROUP BY LUV.city ) AS LUV2 ON LUV2.city = LUV.city
					LEFT JOIN ( select LUV.city, count( LUV.id ) AS total_visitors 
					from {$this->tablePrefix}_logUniqueVisit AS LUV WHERE $conditional_query AND LUV.city != '' GROUP BY LUV.city ) AS LUV3 ON LUV3.city = LUV.city    
					WHERE $conditional_query AND LUV.city != '' GROUP BY LUV.city ORDER BY total_visitors DESC";
			
		}else{
				$sql = "select WC.id AS countryId, WC.name,  WC.alpha2Code,  LUV3.total_visitors,  						SUM(PV.totalViews)  AS total_page_views, LUV2.total_unique_visitors 
						FROM {$this->tablePrefix}_pageViews AS PV 
						LEFT JOIN {$this->tablePrefix}_logUniqueVisit AS LUV on PV.visitid = LUV.id 
						LEFT JOIN {$this->tablePrefix}_countries AS WC ON WC.id = LUV.countryId 
						LEFT JOIN ( select LUV.countryId, count( UV.id ) AS total_unique_visitors 
						FROM {$this->tablePrefix}_uniqueVisitors AS UV LEFT JOIN {$this->tablePrefix}_logUniqueVisit AS LUV on UV.id = LUV.id 
						WHERE $conditional_query AND LUV.countryId > 0 GROUP BY countryId ) AS LUV2 ON LUV2.countryId = LUV.countryId 
						LEFT JOIN ( select LUV.countryId, count( LUV.id ) AS total_visitors 
						FROM {$this->tablePrefix}_logUniqueVisit AS LUV
						WHERE $conditional_query AND LUV.countryId > 0 GROUP BY countryId ) AS LUV3 ON LUV3.countryId = LUV.countryId 
						WHERE $conditional_query AND LUV.countryId > 0 
						GROUP BY LUV.countryId ORDER BY total_visitors DESC";

		}
		
		
		if( isset( $arrParam['limit'] ) ){
			$sql .= ' LIMIT 0, '.$arrParam['limit'];	
		}		
		//echo $sql;
		$result = $this->wsmDB->get_results($sql,ARRAY_A);
		return $result;
	}
	
	function fnGetReferralTotalVisitorsCountByCountry( $arrParam  ){
        $visitLastActionTime="CONVERT_TZ(LUV.visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
        $conditional_query = '';
        if((isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from'])) && (isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']))){                                      
             $conditional_query.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
        }
		$compare = '=';
		if( isset( $arrParam['compare'] ) && $arrParam['compare'] ){
			$compare = $arrParam['compare'];
		}
		if( isset( $arrParam['location'] ) ){		
			$city = $arrParam['city'] ? $compare."'$arrParam[city]'" : $compare.'\'\'';
			$sql = "SELECT LUV.id FROM {$this->tablePrefix}_logUniqueVisit AS LUV 
				WHERE $conditional_query AND LUV.city $city";
		}else{
			$sql = "SELECT LUV.id FROM {$this->tablePrefix}_logUniqueVisit AS LUV 
				WHERE $conditional_query AND LUV.countryId $compare $arrParam[countryId]";
		}
		
		$result = $this->wsmDB->get_results($sql,ARRAY_A);
        $count=$this->wsmDB->num_rows;
        if(is_null($count)){
            $count=0;
        }
        return $count;
	
	}
	
	function getReferralCountryStats( $arrRequest ){
        $visitLastActionTime="CONVERT_TZ(PV.visitLastActionTime,'+00:00','".WSM_TIMEZONE."')";
        $conditional_query = '';
		
		$arrParam = $arrRequest;
        if( (isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from']) ) && 
			( isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']) )
		){          
             $conditional_query.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
        }
		
		$whereCondition = '';
		
		if( isset( $arrParam['city'] ) ){
			$whereCondition = 'LU.city = "'.$arrParam['city'].'"';
		}else{
			$whereCondition = 'LU.countryId = '.$arrParam['countryId'].'';	
		}
		
        $sql = 'select date_format( PV.visitLastActionTime, "%Y-%m-%d") AS accessDate,  count( PV. visitId) AS total_visitors,  
SUM(PV.totalViews)  AS total_page_views from '.$this->tablePrefix.'_pageViews AS PV LEFT JOIN '.$this->tablePrefix.'_logUniqueVisit AS LU on PV.visitid = LU.id WHERE '.$conditional_query.' AND '.$whereCondition.' GROUP BY accessDate order by accessDate DESC';

        $referralUrlData = $this->wsmDB->get_results($sql,ARRAY_A);
        
        $conditional_query = str_replace( 'PV.visitLastActionTime', 'UV.firstVisitTime', $conditional_query ); 
		
		$sql = 'select date_format( UV.firstVisitTime, "%Y-%m-%d") AS accessDate, count( UV.id ) AS total_unique_visitors 
from '.$this->tablePrefix.'_uniqueVisitors AS UV LEFT JOIN '.$this->tablePrefix.'_logUniqueVisit AS LU on UV.id = LU.id WHERE '.$conditional_query.' AND  '.$whereCondition.' GROUP BY accessDate order by accessDate DESC';
		
        $referralUrlData2 = $this->wsmDB->get_results($sql,ARRAY_A);
        
        $dataResult = array();
        
        foreach( $referralUrlData as $data ){
            $dataResult[ $data['accessDate'] ] = array( 'total_visitors' => $data['total_visitors'], 'total_page_views' => $data['total_page_views'] );   
        }
        
        foreach( $referralUrlData2 as $data ){
            if( key_exists( $data['accessDate'], $dataResult )  ){
                $dataResult[ $data['accessDate'] ]['total_unique_visitors'] = $data['total_unique_visitors'];
            }else{
                $dataResult[ $data['accessDate'] ]['total_unique_visitors'] = 0;
            }
        }
		return $dataResult;
	}

	function getReferralCountryStartEndVisit( $id, $where = 'countryId' ){
      
		$result = array( 'first_visit' => '', 'last_visit' => '' );
		$sql = 'SELECT LU.firstActionVisitTime AS accessDate from '.$this->tablePrefix.'_logUniqueVisit LU WHERE LU.'.$where.' = "'.$id.'" order by accessDate ASC LIMIT 0,1';
		$data = $this->wsmDB->get_var($sql);
		if( $data ){
			$result['first_visit'] = date('d M Y', strtotime( $data ) );
		}
		$sql = 'SELECT LU.firstActionVisitTime AS accessDate from '.$this->tablePrefix.'_logUniqueVisit LU WHERE LU.'.$where.' = "'.$id.'" order by accessDate DESC LIMIT 0,1';
		$data = $this->wsmDB->get_var($sql);
		if( $data ){
			$result['last_visit'] = date('d M Y', strtotime( $data ) );
		}
		return $result;
	}
	
	function getContentByURLStats( $arrRequest, $limit = 50 ){

		//define( WSM_PAGE_LIMIT, $limit );
		
        $visitLastActionTime="CONVERT_TZ(LV.serverTime,'+00:00','".WSM_TIMEZONE."')";
        $conditional_query = '';
		
		$arrParam = $arrRequest;
        if( (isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from']) ) && 
			( isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']) )
		){          
             $conditional_query.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
        }
		
		$site_url =  str_replace( array(  'http://www.', 'https://wwww.', 'http://', 'https://','www.'), array(), site_url() );
		$where_condition = 'UL.url LIKE  "'.$site_url.'%"';
		if( isset($arrParam['id']) ){
			$where_condition = ' UL.id = '. $arrParam['id'];
		}
		if( isset($arrParam['search']) && $arrParam['search'] ){
			$where_condition = ' UL.title LIKE "%'. $arrParam['search'] .'%"';
		} 
		$order = 'DESC';
		if( isset( $_GET['order'] ) ){
			$order =  sanitize_text_field($_GET['order']);
		}
		$sqlQuery = 'SELECT UL.id, UL.pageId, UL.protocol, UL.title, count( UL.id ) AS hits, UL.url FROM '.$this->tablePrefix.'_url_log AS UL 
					LEFT JOIN '.$this->tablePrefix.'_logVisit AS LV ON LV.URLId = UL.id
					WHERE '.$conditional_query.' AND '.$where_condition.' GROUP BY UL.id ORDER BY hits '.$order;

        $allResult=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
        $totalRecords=$this->wsmDB->num_rows;
		$arrResult['totalRecords'] = $totalRecords;
        $cPage=isset($arrParam['currentPage'])?$arrParam['currentPage']:1;
        $offset=($cPage-1)*$limit;
        $sqlQuery.=" LIMIT {$offset},".$limit;
        $arrResult['data']=$this->wsmDB->get_results($sqlQuery,ARRAY_A);
        if(isset($arrParam['currentPage']) && isset($arrParam['adminURL']) && $totalRecords > $limit ){
            $arrResult['pagination']=wsmFnGetPagination($totalRecords,$arrParam['currentPage'],$arrParam['adminURL'], $limit );
        }
        return $arrResult;           
		
	}
	
	function getContentByURLVisitors( $arrRequest ){
        $visitLastActionTime="CONVERT_TZ(firstActionVisitTime,'+00:00','".WSM_TIMEZONE."')";
        $conditional_query = '';
		
		$arrParam = $arrRequest;
        if( (isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from']) ) && 
			( isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']) )
		){          
             $conditional_query.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
        }
		
		$sqlQuery 	= 	'SELECT count(visitorId) As visitors, Count(Distinct visitorId) As newVisitors FROM '.$this->tablePrefix.'_logUniqueVisit WHERE visitEntryURLId='. $arrParam['id'] .' AND '.$conditional_query;
		//echo $sqlQuery.'<br />';
		$result		=	$this->wsmDB->get_row($sqlQuery,ARRAY_A);
		return $result;
	}
	
	function getContentByURLTotalRecords( $arrRequest ){
		$limit = 100;
        $visitLastActionTime="CONVERT_TZ(LV.serverTime,'+00:00','".WSM_TIMEZONE."')";
        $conditional_query = '';
		
		$arrParam = $arrRequest;
        if( (isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from']) ) && 
			( isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']) )
		){          
             $conditional_query.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
        }
		$site_url =  str_replace( array(  'http://www.', 'https://wwww.', 'http://', 'https://','www.'), array(), site_url() );
		
		$sqlQuery = 'SELECT count( UL.id ) AS total FROM '.$this->tablePrefix.'_url_log AS UL 
					LEFT JOIN '.$this->tablePrefix.'_logVisit AS LV ON LV.URLId = UL.id
					WHERE '.$conditional_query;
		
		if( isset( $arrParam['id'] ) ){
			$sqlQuery .= ' AND UL.id ='.$arrParam['id'];
		}
//		echo $sqlQuery.'<br />';
        $allResult=$this->wsmDB->get_row($sqlQuery,ARRAY_A);
//		print_r($allResult);
//echo $allResult['total'];
        return $allResult['total'];           
		
	}

	function getContentURLDayWiseStats( $arrRequest ){
        $visitLastActionTime="CONVERT_TZ(firstActionVisitTime,'+00:00','".WSM_TIMEZONE."')";
        $serverTime="CONVERT_TZ(serverTime,'+00:00','".WSM_TIMEZONE."')";
        $conditional_query = $conditional_query2 = '';
		
		$arrParam = $arrRequest;
        if( (isset($arrParam['from']) && $arrParam['from']!='' && wsmValidateDateTime($arrParam['from']) ) && 
			( isset($arrParam['to']) && $arrParam['to']!='' && wsmValidateDateTime($arrParam['to']) )
		){          
             $conditional_query.=" {$visitLastActionTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$visitLastActionTime}<='".$arrParam['to'].' 23:59:59'."'";
             $conditional_query2.=" {$serverTime} >= '".$arrParam['from'].' 00:00:00'."' AND {$serverTime}<='".$arrParam['to'].' 23:59:59'."'";
        }
		
		$sqlQuery = 'SELECT date_format( serverTime, "%Y-%m-%d") AS accessDate, count( URLId ) AS hits FROM '.$this->tablePrefix.'_logVisit WHERE '.$conditional_query2.' AND URLId = '. $arrParam['id'] .' GROUP BY accessDate ORDER BY accessDate DESC';
		
		$referralUrlData		=	$this->wsmDB->get_results($sqlQuery,ARRAY_A);
		
		//print_r($referralUrlData);
		$sqlQuery 	= 	'SELECT date_format( firstActionVisitTime, "%Y-%m-%d") AS accessDate, count(visitorId) As visitors, Count(Distinct visitorId) As newVisitors FROM '.$this->tablePrefix.'_logUniqueVisit WHERE visitEntryURLId='. $arrParam['id'] .' AND '.$conditional_query.' GROUP BY accessDate ORDER BY accessDate DESC';
		
		$referralUrlData2		=	$this->wsmDB->get_results($sqlQuery,ARRAY_A);
		
        
        $dataResult = array();
        
        foreach( $referralUrlData2 as $data ){
            $dataResult[ $data['accessDate'] ] = array( 'total_visitors' => $data['visitors'], 'total_unique_visitors' => $data['newVisitors'] );   
        }
        
        foreach( $referralUrlData as $data ){
            if( key_exists( $data['accessDate'], $dataResult )  ){
                $dataResult[ $data['accessDate'] ]['total_page_views'] = $data['hits'];
            }else{
                $dataResult[ $data['accessDate'] ]['total_page_views'] = 0;
            }
        }
		return $dataResult;
	}

}
