<?php
/**
* @author George Mathew K <hide@address.com>
* @since 1.8.0
* @package trafficanalyzer
* @license GPL V2
*/
class TA_Resource {
/**
* eg: array(
* array (
* "id"=>"12",
* "type"=>"post",
* "title"=>"Traffic Analyzer",
* "count"=>23,
* "period"=>"12-Oct-2011",
* "period_ts"=>12344333
* )
* )
* @var array
*/
private $resources=array();
private $total=array();
/**
* Constructor function
* If array id is not empty, then the property $resources will be populated with that ids
* @param int $num Requested number of resources
* @param string $order Requested is top or bottom. For top $order is desc and for bottom it is asc
* @param TA_Period $period Views in this period only
* @param Array array of specific ids
*/
public function __construct($num=0,$order="desc",TA_Period $period,$id=array(),$screen="views"){
global $wpdb;
if($num>0)
$limit = " limit $num ";
else
$limit = "";
$where_cond_fields = $this->get_required_resource_id($num, $order, $period);
if(!empty($where_cond_fields))
$where_cond = " and visits.resource in ( " . $where_cond_fields . " ) ";
else
$where_cond = " ";
$where_cond .= $this->where_cond_date_range($period);
$sql = " select ". $this->get_select_fields($period) .
" from ".$wpdb->prefix."tanalyzer_visits visits left outer join ".$wpdb->prefix."posts posts on ( visits.resource = posts.id) " .
" left outer join ".$wpdb->prefix."tanalyzer_resources resources on ( visits.resource = resources.resource_id) " .
" where 1=1 " .
" $where_cond " .
" " . ta_user_agent_where() . " " .
" " . $this->group_by_clause() ." " .
" order by cnt $order " ;
if(!empty($id)) {
$id_string = implode(",",$id);
$sql = " select ". $this->get_select_fields($period) .
" from ".$wpdb->prefix."tanalyzer_visits visits left outer join ".$wpdb->prefix."posts posts on ( visits.resource = posts.id) " .
" left outer join ".$wpdb->prefix."tanalyzer_resources resources on ( visits.resource = resources.resource_id) " .
" where visits.resource in ( $id_string ) " .
" $where_cond " .
" " . ta_user_agent_where() . " " .
" " . $this->group_by_clause() ." " .
" order by cnt $order " ;
}
$date_start_obj = new DateTime($period->get_start_date());
$date_end_obj = new DateTime($period->get_end_date());
$date_start_ts = strtotime($period->get_start_date());
$date_end_ts = strtotime($period->get_end_date());
$interval_ts = 24*60*60;
switch($period->get_periodicity()){
case 'w':
$interval_ts = 6 * 24 * 60 * 60;
break;
case 'm':
$interval_ts = ( date('t',$date_start_ts) -1 ) * 24 * 60 * 60 ;
break;
}
for(; $date_start_ts <$date_end_ts ; $date_start_ts = $date_start_ts + $interval_ts ) {
switch($period->get_periodicity()){
case 'd' :
$period_format = date("d-M-Y",$date_start_ts);
break;
case 'w':
$period_format = date("W-Y",$date_start_ts);
break;
case 'm':
$period_format = date("M-Y",$date_start_ts);
break;
}
$this->resources[]=array(
'id'=>'',
'type'=>'',
'title'=>'',
'count'=>'',
'period'=>$period_format,
'period_ts'=>strtotime(ta_get_first_day_from_label($period_format,$period->get_periodicity()))
);
}
if($screen=='views'){
$local_tzone = cur_tzone(); // Getting current MySQL Time Zone
set_tzone(); // Overwriting the MySQL Time Zone with WordPress Time Zone
$result = $wpdb->get_results($sql);
restore_tzone($local_tzone); // Restoring MySQL Time Zone
}
foreach($result as $row){
$this->resources[]=array(
'id'=>$row->resource_id,
'type'=>$row->type,
'title'=>$row->resource_title,
'count'=>$row->cnt,
'period'=>$row->period,
'period_ts'=>strtotime(ta_get_first_day_from_label($row->period,$period->get_periodicity()))
);
}
/*
* Sort $this->resources on period_ts , count
*/
$period_ts = array();
$count = array();
foreach($this->resources as $key=>$row){
$period_ts[$key] = $row['period_ts'];
$count[$key] = $row['count'];
}
if($order="asc"){
if(is_array($this->resources))
array_multisort($period_ts,SORT_ASC,$count,SORT_ASC,$this->resources);
}else{
if(is_array($this->resources))
array_multisort($period_ts,SORT_ASC,$count,SORT_DESC,$this->resources);
}
/*
* Finding total view of the argument period(date,week, or month)
*/
$this->find_total($period);
}
public function get_required_resource_id($num,$order,$period){
global $wpdb;
$id_string = "";
if($num==0)
$limit = " ";
else
$limit = " limit $num ";
$sql = " select visits.resource res_id" ;
$sql .= " from $wpdb->prefix"."tanalyzer_visits visits ";
$sql .= " left outer join $wpdb->prefix"."posts posts ";
$sql .= " on ( visits.resource = posts.id ) ";
$sql .= " left outer join $wpdb->prefix"."tanalyzer_resources resources ";
$sql .= " on ( visits.resource = resources.resource_id ) ";
$sql .= " where date(vtime) between '".$period->get_start_date()."' and '". $period->get_end_date()."'";
$sql .= ta_user_agent_where();
$sql .= " group by res_id ";
$sql .= " order by count(*) $order";
$sql .= " $limit " ;
$local_tzone = cur_tzone(); // Getting current MySQL Time Zone
set_tzone(); // Overwriting the MySQL Time Zone with WordPress Time Zone
$result = $wpdb->get_results($sql);
restore_tzone($local_tzone); // Restoring MySQL Time Zone
foreach($result as $row){
$id[] = $row->res_id;
}
if(is_array($id))
$id_string = implode(",",$id);
return $id_string;
}
/**
*
* @since 1.8.0
* @param TA_Period $period
* @return string select fields in comma separated string
*/
public function get_select_fields(TA_Period $period){
$fields="";
switch($period->get_periodicity()){
case 'a':
$fields = " '' as period, " ; // Period
$fields .= " case when posts.post_title = '' then resources.resource_title when posts.post_title is null then resources.resource_title else posts.post_title end resource_title ," ; // Resource Title
$fields .= " case when posts.post_title = '' then resources.resource_id when posts.post_title is null then resources.resource_id else posts.id end resource_id ," ; // Resource ID
$fields .= " visits.resource_type as type, " ; // Resource Type
$fields .= " count(visits.resource) cnt " ; // Resource Count
break;
case 'd':
$fields = " date_format(vtime,'%d-%b-%Y') as period, " ; // Period
$fields .= " case when posts.post_title = '' then resources.resource_title when posts.post_title is null then resources.resource_title else posts.post_title end resource_title ," ; // Resource Title
$fields .= " case when posts.post_title = '' then resources.resource_id when posts.post_title is null then resources.resource_id else posts.id end resource_id ," ; // Resource ID
$fields .= " visits.resource_type as type, " ; // Resource Type
$fields .= " count(visits.resource) cnt " ; // Resource Count
break;
case 'w':
$fields = " date_format(vtime,'%u-%Y') as period, " ; // Period
$fields .= " case when posts.post_title = '' then resources.resource_title when posts.post_title is null then resources.resource_title else posts.post_title end resource_title," ; // Resource Title
$fields .= " case when posts.post_title = '' then resources.resource_id when posts.post_title is null then resources.resource_id else posts.id end resource_id," ; // Resource ID
$fields .= " visits.resource_type as type, " ; // Resource Type
$fields .= " count(visits.resource) cnt " ; // Resource Count
break;
case 'm':
$fields = " date_format(vtime,'%b-%Y') as period, " ; // Period
$fields .= " case when posts.post_title = '' then resources.resource_title when posts.post_title is null then resources.resource_title else posts.post_title end resource_title," ; // Resource Title
$fields .= " case when posts.post_title = '' then resources.resource_id when posts.post_title is null then resources.resource_id else posts.id end resource_id," ; // Resource ID
$fields .= " visits.resource_type as type, " ; // Resource Type
$fields .= " count(visits.resource) cnt " ; // Resource Count
break;
}
return $fields;
}
/**
*
* @since 2.0.0
* @param TA_Period $period
* @return string select fields in comma separated string
*/
public function get_select_fields_visits(TA_Period $period){
$fields="";
switch($period->get_periodicity()){
case 'd' || 'a':
$fields = " date_format(vtime,'%d-%b-%Y') as period, " ; // Period
$fields .= " 'Visits' resource_title ," ; // Resource Title
$fields .= " '-1' resource_id ," ; // Resource ID
$fields .= " 'none' as type, " ; // Resource Type
$fields .= " count(wpta_cookie) cnt " ; // Resource Count
break;
case 'w':
$fields = " date_format(vtime,'%u-%Y') as period, " ; // Period
$fields .= " 'Visits' resource_title ," ; // Resource Title
$fields .= " '-1' resource_id ," ; // Resource ID
$fields .= " 'none' as type, " ; // Resource Type
$fields .= " count(wpta_cookie) cnt " ; // Resource Count
break;
case 'm':
$fields = " date_format(vtime,'%b-%Y') as period, " ; // Period
$fields .= " 'Visits' resource_title ," ; // Resource Title
$fields .= " '-1' resource_id ," ; // Resource ID
$fields .= " 'none' as type, " ; // Resource Type
$fields .= " count(wpta_cookie) cnt " ; // Resource Count
break;
}
return $fields;
}
/**
* Returns the where clause
* @since 1.8.0
* @param TA_Period $period
*/
public function where_clause(TA_Period $period){
$where = " and visits.vtime between '".$period->get_start_date()."' and '".$period->get_end_date()."' ";
return $where;
}
/**
* Returns the group by clause
* @since 1.8.0
*/
public function group_by_clause(){
$group = " group by period,visits.resource" ;
return $group;
}
/**
*
* Finds the total view for each item in the specified period and stores in the array $total
* @param TA_Period $period
*/
public function find_total(TA_Period $period){
global $wpdb;
$field = "";
$group_by = "";
switch($period->get_periodicity()){
case 'a':
$field = " '' as period , ";
break;
case 'd':
$field = "date_format(vtime,'%d-%b-%Y') as period ,";
$group_by = " group by period ";
break;
case 'm':
$field = "date_format(vtime,'%b-%Y') as period , ";
$group_by = " group by period ";
break;
case 'w':
$field = "date_format(vtime,'%u-%Y') as period , ";
$group_by = " group by period ";
break;
}
$sql = " select $field count(*) as cnt ";
$sql .= " from $wpdb->prefix"."tanalyzer_visits visits " ;
$sql .= " left outer join $wpdb->prefix"."posts posts " ;
$sql .= " on ( visits.resource = posts.id ) " ;
$sql .= " left outer join $wpdb->prefix"."tanalyzer_resources resources ";
$sql .= " on ( resources.resource_id = visits.resource ) " ;
$sql .= " where date(vtime) between '".$period->get_start_date()."' and '". $period->get_end_date() . "'";
$sql .= ta_user_agent_where();
$sql .= $group_by;
//echo $sql;
$local_tzone = cur_tzone(); // Getting current MySQL Time Zone
set_tzone(); // Overwriting the MySQL Time Zone with WordPress Time Zone
$result = $wpdb->get_results($sql);
restore_tzone($local_tzone); // Restoring MySQL Time Zone
$this->total=array();
foreach($result as $row){
$this->total["'".$row->period."'"]=$row->cnt;
}
}
/**
*
* @return array $this->total
* @since 1.8.0
*/
public function get_total($key=""){
if(!empty($key)){
if(isset($this->total["'".$key."'"]))
return $this->total["'".$key."'"];
else
return 0;
}else{ // Finds all total
$total=0;
foreach($this->total as $value){
$total .= $value;
}
return $total;
}
}
/**
* Returns the array $resources
* @since 1.8.0
*
*/
public function get_resource(){
return $this->resources;
}
/**
* @return boolean true if line chart else pie chart
* @since 1.8.0
*/
public function is_multi(){
foreach($this->resources as $row){
$period["'".$row['period']."'"] = $row['period'];
if(count($period)>1)
return true;
}
return false;
}
/**
*
* @return boolean true if no data
* @since 1.8.0
*/
public function is_no_data(){
foreach($this->resources as $row){
if($row['title']!="")
return false;
}
return true;
}
/**
* @param TA_Period $period
* @return string Returning conditions for the date range
*/
public function where_cond_date_range(TA_Period $period){
$sdate = $period->get_start_date();
$edate = $period->get_end_date();
$where_cond = " and date(vtime) between '".$sdate."' and '".$edate."' ";
return $where_cond;
}
}