1) { $s='s'; } else { $s=''; }
$list.="user_id #".$nfo[2][$i][1]." : ".$nfo[2][$i][0]." ".l10n("AStat_event$s")."";
}
$template_datas["ASTAT_DELETED_USER_NFO"] = sprintf(l10n('AStat_tools_deleted_user_nfo1'), $nfo[0], $nfo[1], $list);
$template_datas['AStat_deleted_user_submit'] = 'yes';
}
else
{
$template_datas["ASTAT_DELETED_USER_NFO"] = l10n('AStat_tools_deleted_user_nfo2');
}
// << DISPLAY DELETED USER NFO ---------------------------------------------
// >> DISPLAY GENERAL NFO --------------------------------------------------
$nfo = $this->tools_general_nfo();
if($nfo[0]>0)
{
$template_datas["ASTAT_GENERAL_NFO"] = sprintf(l10n('AStat_tools_general_nfo_nfo'),
$nfo[0],
$this->formatoctet($nfo[3]+$nfo[4], "A", " ", 2, true),
$this->formatoctet($nfo[3], "A", " ", 2, true),
$this->formatoctet($nfo[4], "A", " ", 2, true),
date(l10n('AStat_date_time_format'), strtotime($nfo[2])),
date(l10n('AStat_date_time_format'), strtotime($nfo[1])) );
}
$nfo=$this->purge_history_count_imageid0();
$template_datas["ASTAT_PURGE_HISTORY_IMAGE_NFO"] = sprintf(l10n('AStat_tools_purge_history_imageid0'), $nfo);
if($nfo==0)
{
$template_datas["ASTAT_PURGE_HISTORY_IMAGE_DISABLED"] = " disabled ";
}
$nfo=$this->purge_history_count_categoryid0();
$template_datas["ASTAT_PURGE_HISTORY_CATEGORY_NFO"] = sprintf(l10n('AStat_tools_purge_history_categoryid0'), $nfo);
if($nfo==0)
{
$template_datas["ASTAT_PURGE_HISTORY_CATEGORY_DISABLED"] = " disabled ";
}
// << GENERAL NFO ----------------------------------------------------------
if($action_result['result']!='')
{
if($action_result['result']=='true')
{
$value="";
}
else
{
$value="
";
}
$value.="".l10n($action_result['action'])."
".l10n($action_result['msg'])."
";
if($action_result['nfo']!='')
{
$value.="[".l10n($action_result['nfo'])."]
";
}
$template_datas["ASTAT_RESULT_OK"] = $value;
}
$template_datas["L_STAT_TITLE"] = l10n('AStat_tools_title');
$template->assign('datas', $template_datas);
$template->assign_var_from_handle('ASTAT_BODY_PAGE', 'body_page');
} // display_tools
/*
tools functions
*/
/*
tools : deleted_user
allow to force HISTORY_TABLE.user_id at 2 (guest) for records with user ident
doesn't exist anymore in the USERS_TABLE
Two usages :
- analyse : return infos about records wich need to be updated
* number of users
* number of records in HISTORY_TABLE
* the users list
- resynchro : update table
*/
private function tools_deleted_user($mode)
{
$returned = array(-1,0,'');
if($mode=='analyse')
{
$sql="SELECT count(id) as NbRecord, user_id ";
$sql.=" FROM ".HISTORY_TABLE;
$sql.=" WHERE ".HISTORY_TABLE.".user_id NOT IN (SELECT id FROM ".USERS_TABLE.") ";
$sql.=" GROUP BY user_id ORDER BY NbRecord";
$result=pwg_query($sql);
if($result)
{
$returned[0]=0;
while ($row = mysql_fetch_array($result))
{
$returned[2][$returned[0]][0] = $row[0];
$returned[2][$returned[0]][1] = $row[1];
$returned[0]++;
$returned[1]+=$row[0];
}
}
}
elseif($mode=='resynchro')
{
$sql="UPDATE ".HISTORY_TABLE." SET user_id = 2 ";
$sql.=" WHERE ".HISTORY_TABLE.".user_id NOT IN (SELECT id FROM ".USERS_TABLE.") ";
$result=pwg_query($sql);
if($result)
{ $returned[0]=1; }
}
return($returned);
}
/*
tools : deleted_picture
analyse history to find deleted pictures
Two functions :
- analyse : return infos
* number of pictures
* number of record in HISTORY_TABLE
* pictures list
- to0 : update picture ident to 0
*/
private function tools_deleted_picture($mode)
{
$returned = array(-1,0,'');
if($mode=='analyse')
{
$sql="SELECT count(id) as NbRecord, image_id ";
$sql.=" FROM ".HISTORY_TABLE;
$sql.=" WHERE ".HISTORY_TABLE.".image_id NOT IN (SELECT id FROM ".IMAGES_TABLE.") and image_id > 0 ";
$sql.=" GROUP BY image_id ORDER BY NbRecord";
$result=pwg_query($sql);
if($result)
{
$returned[0]=0;
while ($row = mysql_fetch_array($result))
{
$returned[2][$returned[0]][0] = $row[0];
$returned[2][$returned[0]][1] = $row[1];
$returned[0]++;
$returned[1]+=$row[0];
}
}
}
elseif($mode=='to0')
{
$sql="UPDATE ".HISTORY_TABLE."
SET image_id = 0
WHERE ".HISTORY_TABLE.".image_id > 0
AND ".HISTORY_TABLE.".image_id NOT IN (SELECT id FROM ".IMAGES_TABLE.")";
$result=pwg_query($sql);
if($result)
{
$returned[0]=1;
}
}
return($returned);
}
/*
tools : deleted_category
analyse history to find deleted categories
Two functions :
- analyse : return infos
* number of category
* number of record in HISTORY_TABLE
* catgories list
- to0 : update categories ident to 0
*/
private function tools_deleted_category($mode)
{
$returned = array(-1,0,'');
if($mode=='analyse')
{
$sql="SELECT count(id) as NbRecord, category_id ";
$sql.=" FROM ".HISTORY_TABLE;
$sql.=" WHERE ".HISTORY_TABLE.".category_id NOT IN (SELECT id FROM ".CATEGORIES_TABLE.") and category_id > 0";
$sql.=" GROUP BY category_id ORDER BY NbRecord";
$result=pwg_query($sql);
if($result)
{
$returned[0]=0;
while ($row = mysql_fetch_array($result))
{
$returned[2][$returned[0]][0] = $row[0];
$returned[2][$returned[0]][1] = $row[1];
$returned[0]++;
$returned[1]+=$row[0];
}
}
}
elseif($mode=='to0')
{
$sql="UPDATE ".HISTORY_TABLE."
SET category_id = NULL, section = 'deleted_cat'
WHERE ".HISTORY_TABLE.".category_id > 0
AND ".HISTORY_TABLE.".category_id NOT IN (SELECT id FROM ".CATEGORIES_TABLE.")";
$result=pwg_query($sql);
if($result)
{
$returned[0]=1;
}
}
return($returned);
}
/*
tools : general_nfo
return infos about historic
0 : nulber of records
1 : date of newest record
2 : date of oldesr record
3 : table size
4 : index size
*/
private function tools_general_nfo()
{
$returned = array(-1,'','',0,0);
$sql="SELECT count(id) AS NbRecord, MAX(concat(date,' ', time)) AS LastDate, MIN(concat(date,' ', time)) AS FirstDate ";
$sql.=" FROM ".HISTORY_TABLE;
$result=pwg_query($sql);
if($result)
{
$row = mysql_fetch_array($result);
if(is_array($row))
{
$returned = $row;
$sql="SHOW TABLE STATUS LIKE '".HISTORY_TABLE."';";
$result=pwg_query($sql);
if($result)
{
$row2=mysql_fetch_array($result);
array_push($returned, $row2['Data_length'], $row2['Index_length']);
}
}
}
return($returned);
}
/*
tools : do_purge_history
do a purge of history table :
- $purgetype='bydate' : purge all record wich date is less than given date
- $purgetype='byimageid0' : with and image_id = 0
------------------------------------------------------------------------------------ */
private function do_purge_history($date, $purgetype)
{
if($purgetype=='bydate')
{
$sql="DELETE FROM ".HISTORY_TABLE." WHERE date < '$date'";
}
elseif($purgetype=='byimageid0')
{
$sql="DELETE FROM ".HISTORY_TABLE." WHERE image_id = 0";
}
elseif($purgetype=='bycategoryid0')
{
$sql="DELETE FROM ".HISTORY_TABLE." WHERE category_id is null and section='deleted_cat'";
}
else
{
return(false);
}
$result=pwg_query($sql);
if($result)
{
$sql="OPTIMIZE TABLE ".HISTORY_TABLE;
$result=pwg_query($sql);
return($result);
}
return(false);
}
private function purge_history_count_imageid0()
{
$sql="SELECT COUNT(id) FROM ".HISTORY_TABLE." WHERE image_id = 0";
$result=pwg_query($sql);
if($result)
{
$row=mysql_fetch_array($result);
return($row[0]);
}
return(0);
}
private function purge_history_count_categoryid0()
{
$sql="SELECT COUNT(id) FROM ".HISTORY_TABLE." WHERE category_id is null and section = 'deleted_cat'" ;
$result=pwg_query($sql);
if($result)
{
$row=mysql_fetch_array($result);
return($row[0]);
}
return(0);
}
/*
tools : deleted_picture
> verify_AStat_picture_table_status :
> prepare_AStat_picture_table :
*/
private function verify_AStat_picture_table_status()
{
global $prefixeTable;
$sql="SHOW TABLE STATUS LIKE '".$prefixeTable."AStat_picture'";
$result=pwg_query($sql);
if($result)
{
if(mysql_num_rows($result)==1)
{
return(true);
}
}
return(false);
}
private function prepare_AStat_picture_table()
{
global $prefixeTable;
$sql="CREATE TABLE ".$prefixeTable."AStat_picture (PRIMARY KEY (id), KEY ifile(file))
SELECT id, file
FROM ".IMAGES_TABLE;
$result=pwg_query($sql);
if($result)
{
return(true);
}
return(false);
}
private function count_AStat_picture_table()
{
global $prefixeTable;
$returned=array(false,0,0);
if($this->verify_AStat_picture_table_status())
{
$sql="SELECT count(DISTINCT ".$prefixeTable."AStat_picture.id), count(".HISTORY_TABLE.".id)
FROM ".HISTORY_TABLE.", ".$prefixeTable."AStat_picture
WHERE ".HISTORY_TABLE.".image_id = ".$prefixeTable."AStat_picture.id
AND ".HISTORY_TABLE.".image_id NOT IN (SELECT id FROM ".IMAGES_TABLE.")
ORDER BY ".$prefixeTable."AStat_picture.id";
$result=pwg_query($sql);
if($result)
{
if(mysql_num_rows($result)>0)
{
$row=mysql_fetch_row($result);
$returned[0]=true;
$returned[1]=$row[0];
$returned[2]=$row[1];
}
}
}
return($returned);
}
private function apply_AStat_picture_table()
{
global $prefixeTable;
$returned=false;
$sql="CREATE TABLE ".$prefixeTable."AStat_picture2 (PRIMARY KEY (OldId))
SELECT AStat_tmp.id as OldId , ".IMAGES_TABLE.".id as NewId, ".IMAGES_TABLE.".storage_category_id as NewCatId
FROM (SELECT DISTINCT ".$prefixeTable."AStat_picture.*
FROM ".HISTORY_TABLE.", ".$prefixeTable."AStat_picture
WHERE ".HISTORY_TABLE.".image_id = ".$prefixeTable."AStat_picture.id
AND ".HISTORY_TABLE.".image_id NOT IN (SELECT id FROM ".IMAGES_TABLE.")
ORDER BY ".$prefixeTable."AStat_picture.id
) as AStat_tmp
LEFT JOIN ".IMAGES_TABLE." ON AStat_tmp.file = ".IMAGES_TABLE.".file";
$result=pwg_query($sql);
if($result)
{
$sql="UPDATE ".HISTORY_TABLE.", ".$prefixeTable."AStat_picture2, ".IMAGES_TABLE."
SET ".HISTORY_TABLE.".image_id = ".$prefixeTable."AStat_picture2.NewId,
".HISTORY_TABLE.".category_id = ".$prefixeTable."AStat_picture2.NewCatId
WHERE ".$prefixeTable."AStat_picture2.OldId = ".HISTORY_TABLE.".image_id";
$result=pwg_query($sql);
if($result)
{
$returned=true;
}
$sql="DROP TABLE IF EXISTS ".$prefixeTable."AStat_picture2";
$result=pwg_query($sql);
$sql="DROP TABLE IF EXISTS ".$prefixeTable."AStat_picture";
$result=pwg_query($sql);
}
return($returned);
}
/*
generics functions
*/
/*
this function make filter list and apply template
$selected : category_id of selected item ("" if none)
*/
private function make_filter_list($selected="")
{
global $template;
$template_datarows_values=array();
$template_datarows_labels=array();
$template_hiddenrows=array();
$template_datarows_values[]="";
$template_datarows_labels[]=l10n("AStat_nofilter");
$sql="SELECT id, name, global_rank FROM ".CATEGORIES_TABLE." order by global_rank";
$result = pwg_query($sql);
if($result)
{
while ($row = mysql_fetch_array($result))
{
$text=str_repeat(' ', substr_count($row['global_rank'], '.'));
$template_datarows_values[]=$row['id'];
$template_datarows_labels[]=$text.$row['name'];
}
}
$query=explode('&', $_SERVER['QUERY_STRING']);
foreach($query as $key => $value)
{
$nfo=explode('=', $value);
$template_hiddenrows[]=array(
'VALUE' => urldecode($nfo[1]),
'NAME' => $nfo[0]
);
}
$template->assign('ASTAT_LINK', $_SERVER['SCRIPT_NAME']);
$template->assign('f_AStat_catfilter_list_values', $template_datarows_values);
$template->assign('f_AStat_catfilter_list_labels', $template_datarows_labels);
$template->assign('f_AStat_catfilter_selected', $selected);
$template->assign('f_AStat_parameters', $template_hiddenrows);
} //make_filter_list
/*
this function make SELECT "WHERE" clause for filter list
$selected : category_id of selected item ("" if none)
*/
private function make_where_clause($catfilter)
{
$returned=array();
$sql="SELECT id, ".CATEGORIES_TABLE.".global_rank
FROM ".CATEGORIES_TABLE.",
(SELECT global_rank FROM ".CATEGORIES_TABLE." WHERE id = '".$catfilter."') as tmp1
WHERE ".CATEGORIES_TABLE.".global_rank LIKE CONCAT(tmp1.global_rank, '%')
ORDER BY ".CATEGORIES_TABLE.".global_rank";
$result = pwg_query($sql);
if($result)
{
while ($row = mysql_fetch_row($result))
{
$returned[]=$row[0];
}
}
return(implode(',', $returned));
}
/*
format text : $value
*/
private function format_link($value, $link)
{
return("$value");
}
/*
return true if IP adress is given
*/
private function is_IP($ip)
{ //basic test, maybe a pcre will be more appropriate...
$tmp=explode('.', $ip);
if(count($tmp)!=4)
{ return (false); }
for($i=0;$i<4;$i++)
{
if(!is_numeric($tmp[$i])) {Â return (false); }
}
return (true);
}
/*
change filename extension
*/
private function change_file_ext($file, $newext)
{ //filename can be for example
$tmp = explode('.', $file);
if(count($tmp)>1) { $tmp[count($tmp)-1] = $newext; }
return implode('.', $tmp);
}
/*
format number $octets with unit
$format = "A" : auto
"O" : o
"K" : Ko
"M" : Mo
"G" : Go
$thsep = thousand separator
$prec = number of decimal
$unitevis = true/false > renvoi l'unité ou non dans le résultat
*/
private function formatoctet($octets, $format="A", $thsep=" ", $prec=2, $unitevis=true)
{
if($format=="A")
{
if($octets<1024)
{ $format="O"; }
elseif($octets<1024000)
{ $format="K"; }
elseif($octets<1024000000)
{ $format="M"; }
else
{ $format="G"; }
}
switch($format)
{
case "O":
$unite="o"; $div=1;
break;
case "K":
$unite="Ko"; $div=1024;
break;
case "M":
$unite="Mo"; $div=1024000;
break;
case "G":
$unite="Go"; $div=1024000000;
break;
}
$retour=number_format($octets/$div, $prec, '.', $thsep);
if($unitevis)
{ $retour.=" ".$unite; }
return($retour);
}
} // AStat_AI class
?>