Context Navigation

source: extensions/EStat/estat_ajax_export.php @ 17737

Last change on this file since 17737 was 17737, checked in by grum, 12 years ago
First commit for EStat files
Property svn:executable set to ``*
File size: 43.6 KB

Rev	Line
[17737]	1	<?php
	2	/*
	3	* -----------------------------------------------------------------------------
	4	* Plugin Name: EStat
	5	* -----------------------------------------------------------------------------
	6	* Author : Grum
	7	* email : grum@piwigo.org
	8	* website : http://www.grum.fr
	9	* PWG user : http://forum.piwigo.org/profile.php?id=3706
	10	*
	11	* << May the Little SpaceFrog be with you ! >>
	12	*
	13	* -----------------------------------------------------------------------------
	14	*
	15	* See main.inc.php for release information
	16	*
	17	* manage all the ajax requests relative to file exports
	18	* => very similar to EStat_Ajax functions for the following services:
	19	* admin.export.history <=> admin.stat.history
	20	* admin.export.ip <=> admin.stat.ip
	21	* admin.export.category <=> admin.stat.category
	22	* admin.export.image <=> admin.stat.image
	23	*
	24	* the admin.export.loadFile service is used to download produced files
	25	* -----------------------------------------------------------------------------
	26	*/
	27
	28	// in this case, PHPWG_ROOT_PATH must be declared as an absolute path...
	29	define('PHPWG_ROOT_PATH',dirname(dirname(dirname(__FILE__))).'/');
	30	if(!defined('AJAX_CALL')) define('AJAX_CALL', true);
	31
	32	/*
	33	* set ajax module in admin mode if request is used for admin interface
	34	*/
	35	if(!isset($_REQUEST['ajaxfct'])) $_REQUEST['ajaxfct']='';
	36	if(preg_match('/^admin\./i', $_REQUEST['ajaxfct'])) define('IN_ADMIN', true);
	37
	38	// the common.inc.php file loads all the main.inc.php plugins files
	39	include_once(PHPWG_ROOT_PATH.'include/common.inc.php' );
	40	include_once(PHPWG_PLUGINS_PATH.'GrumPluginClasses/classes/GPCAjax.class.inc.php');
	41	include_once(PHPWG_PLUGINS_PATH.'GrumPluginClasses/classes/GPCUserAgent.class.inc.php');
	42	include_once(PHPWG_PLUGINS_PATH.'GrumPluginClasses/classes/GPCExport.class.inc.php');
	43	include_once('estat_root.class.inc.php');
	44
	45	load_language('plugin.lang', ESTAT_PATH);
	46
	47
	48	class EStat_AjaxExport extends EStat_root
	49	{
	50	/**
	51	* constructor
	52	*/
	53	public function __construct($prefixeTable, $filelocation)
	54	{
	55	parent::__construct($prefixeTable, $filelocation);
	56	$this->loadConfig();
	57	$this->checkRequest();
	58	$this->returnAjaxContent();
	59	}
	60
	61	/**
	62	* check the $_REQUEST values and set default values
	63	*
	64	*/
	65	protected function checkRequest()
	66	{
	67	global $user;
	68
	69	if(!isset($_REQUEST['errcode'])) $_REQUEST['errcode']='';
	70	GPCAjax::checkToken();
	71
	72	// check if asked function is valid
	73	if(!($_REQUEST[GPC_AJAX]=='admin.export.history' or
	74	$_REQUEST[GPC_AJAX]=='admin.export.ip' or
	75	$_REQUEST[GPC_AJAX]=='admin.export.category' or
	76	$_REQUEST[GPC_AJAX]=='admin.export.image' or
	77	$_REQUEST[GPC_AJAX]=='admin.export.loadFile'
	78	)) $_REQUEST[GPC_AJAX]='';
	79
	80	if(preg_match('/^admin\./i', $_REQUEST['ajaxfct']) and !is_admin()) $_REQUEST['ajaxfct']='';
	81
	82	if($_REQUEST[GPC_AJAX]!='')
	83	{
	84	// name, format & compression common to all requests
	85	if(!isset($_REQUEST['name'])) $_REQUEST['name']='';
	86	if(!isset($_REQUEST['format'])) $_REQUEST['format']='csv';
	87	if(!isset($_REQUEST['compression'])) $_REQUEST['compression']='none';
	88
	89	if(!($_REQUEST['format']=='csv' or
	90	$_REQUEST['format']=='ods' or
	91	$_REQUEST['format']=='sqlite')) $_REQUEST['format']='csv';
	92
	93	if(!($_REQUEST['compression']=='none' or
	94	$_REQUEST['compression']=='zip' or
	95	$_REQUEST['compression']=='gz' or
	96	$_REQUEST['compression']=='bz2')) $_REQUEST['format']='none';
	97
	98	if(preg_match('/^([a-z0-9\._]\|-)+$/i', $_REQUEST['name'])!=1) $_REQUEST['name']='export_'.date('Ymd-His');
	99
	100	switch($_REQUEST[GPC_AJAX])
	101	{
	102	case 'admin.export.history':
	103	if(!isset($_REQUEST['period'])) $_REQUEST['period']='';
	104
	105	if(!isset($_REQUEST['sort']))
	106	$_REQUEST['sort']=array(
	107	array('direction' => 'A', 'id' => 'date')
	108	);
	109
	110	if(!isset($_REQUEST['filter'])) $_REQUEST['filter']=array();
	111
	112	if($_REQUEST['period']=='') $_REQUEST['ajaxfct']='';
	113	break;
	114	case 'admin.export.ip':
	115	if(!isset($_REQUEST['period'])) $_REQUEST['period']='a';
	116
	117	if(!isset($_REQUEST['group'])) $_REQUEST['group']=array();
	118
	119	if(!isset($_REQUEST['sort']))
	120	$_REQUEST['sort']=array(
	121	array('direction' => 'A', 'id' => 'ip')
	122	);
	123
	124	if(!isset($_REQUEST['additionalFilter'])) $_REQUEST['additionalFilter']=array();
	125	if(!isset($_REQUEST['filter'])) $_REQUEST['filter']=array();
	126
	127	if($_REQUEST['period']=='') $_REQUEST['ajaxfct']='';
	128	$_REQUEST['filter']['additionalFilter']=$_REQUEST['additionalFilter'];
	129	break;
	130	case 'admin.export.category':
	131	case 'admin.export.image':
	132	if(!isset($_REQUEST['period'])) $_REQUEST['period']='a';
	133
	134	if(!isset($_REQUEST['group'])) $_REQUEST['group']=array();
	135
	136	if(!isset($_REQUEST['sort']))
	137	$_REQUEST['sort']=array(
	138	array('direction' => 'A', 'id' => 'nbVisits')
	139	);
	140
	141	if(!isset($_REQUEST['additionalFilter'])) $_REQUEST['additionalFilter']=array();
	142	if(!isset($_REQUEST['filter'])) $_REQUEST['filter']=array();
	143
	144	if($_REQUEST['period']=='') $_REQUEST['ajaxfct']='';
	145	$_REQUEST['filter']['additionalFilter']=$_REQUEST['additionalFilter'];
	146	break;
	147	case 'admin.export.loadFile':
	148	break;
	149	default:
	150	$_REQUEST[GPC_AJAX]='';
	151	break;
	152	}
	153
	154	}
	155	} //checkRequest
	156
	157
	158	/**
	159	* return ajax content
	160	*/
	161	protected function returnAjaxContent()
	162	{
	163	$result="KO";
	164	switch($_REQUEST[GPC_AJAX])
	165	{
	166	case 'admin.export.history':
	167	$result=$this->ajax_estat_export_admin_statHistory($_REQUEST['period'], $_REQUEST['filter'], $_REQUEST['sort'], $_REQUEST['format'], $_REQUEST['compression'], $_REQUEST['name']);
	168	break;
	169	case 'admin.export.ip':
	170	$result=$this->ajax_estat_export_admin_statIP($_REQUEST['period'], $_REQUEST['filter'], $_REQUEST['sort'], $_REQUEST['group'], $_REQUEST['format'], $_REQUEST['compression'], $_REQUEST['name']);
	171	break;
	172	case 'admin.export.category':
	173	$result=$this->ajax_estat_export_admin_statCategory($_REQUEST['period'], $_REQUEST['filter'], $_REQUEST['sort'], $_REQUEST['group'], $_REQUEST['format'], $_REQUEST['compression'], $_REQUEST['name']);
	174	break;
	175	case 'admin.export.image':
	176	$result=$this->ajax_estat_export_admin_statImage($_REQUEST['period'], $_REQUEST['filter'], $_REQUEST['sort'], $_REQUEST['group'], $_REQUEST['format'], $_REQUEST['compression'], $_REQUEST['name']);
	177	break;
	178	case 'admin.export.loadFile':
	179	$result=$this->ajax_estat_export_admin_loadFile($_REQUEST['name']);
	180	break;
	181	}
	182	if($result!='KO')
	183	{
	184	$result='OK:'.$this->getAdminLink('ajaxExport')."?ajaxfct=admin.export.loadFile&token=".get_pwg_token()."&name=".basename($result);
	185	}
	186	GPCAjax::returnResult($result);
	187	}
	188
	189
	190	/------------------------------------------------------------------------
	191	*
	192	* ADMIN FUNCTIONS
	193	*
	194	----------------------------------------------------------------------- /
	195
	196	/**
	197	* return the logs events from a period
	198	*
	199	* @param String $period : the period to be processed 'YYYYMM'
	200	* @param Array $filter : filter options
	201	* @param Integer $pageNumber : page number to return
	202	* @return String : an array in json format
	203	* (Array) events properties
	204	* 'date' => (String) date time in 'YYYY-MM-DD HH:II:SS' format
	205	* 'IpUserId' => (String) IP or user login
	206	* 'country' => (String) country code (ISO 3366-1)
	207	* 'catId' => (String) name of the album
	208	* 'imageId' => (String) name of image
	209	* 'tags' => (String) tags list
	210	* 'section' => (String) section name
	211	* 'uaEngine' => (String) browser: engine+version
	212	* 'uaBrowser' => (String) browser name+version
	213	* 'uaOS' => (String) browser os: name+version
	214	* 'uaType' => (String) browser type: name
	215	* 'userAgent' => (String) user agent string
	216	*/
	217	protected function ajax_estat_export_admin_statHistory($period, $filterPost, $sort, $format, $compression, $name)
	218	{
	219	global $lang;
	220
	221	$year=substr($period,0,4);
	222	$month=substr($period,4,2);
	223
	224	$filter=array();
	225	foreach($filterPost as $val)
	226	{
	227	$filter[$val['id']]=$val;
	228	}
	229
	230	// open db file for the period
	231	$monthStatFile=new StatDBMonth($this->fileStatDir, self::FILE_MONTH);
	232	$monthStatFile->setPeriod($year, $month);
	233	$monthStatFile->open(ASDF_OPEN_READ);
	234
	235	// get history & total number of items
	236	$result=$monthStatFile->getLogs(ASDF_GET_ROWS, 0, 0, $filter, $sort);
	237	$monthStatFile->close();
	238
	239	// prepare an EStat_IdList to buid a list of unique Id
	240	$idList=new EStat_IdList(array('catId', 'imageId', 'tagsId', 'userId', 'IPadress'));
	241	// $idAssoc will get the label associated to the id
	242	$idAssoc=array(
	243	'catId'=>array(),
	244	'imageId'=>array(),
	245	'tagsId'=>array(),
	246	'userId'=>array(),
	247	'IPadress'=>array()
	248	);
	249
	250	// build list of unique Id
	251	foreach($result as $row)
	252	{
	253	$idList->addItems(
	254	array(
	255	'catId' => $row['catId'],
	256	'imageId' => $row['imageId'],
	257	'tagsId' => $row['tagsId'],
	258	'userId' => $row['userId'],
	259	'IPadress' => $row['IPadress']
	260	)
	261	);
	262	}
	263
	264
	265	if(count($idList->getItems('catId')) > 0)
	266	$this->prepareIdList($idAssoc, 'catId', "SELECT id, name FROM ".CATEGORIES_TABLE." WHERE id IN (".implode(',', $idList->getItems('catId')).") ORDER BY id;");
	267
	268	if(count($idList->getItems('imageId')) > 0)
	269	$this->prepareIdList($idAssoc, 'imageId', "SELECT id, file AS name, path FROM ".IMAGES_TABLE." WHERE id IN (".implode(',', $idList->getItems('imageId')).") ORDER BY id;");
	270
	271	if(count($idList->getItems('tagsId')) > 0)
	272	$this->prepareIdList($idAssoc, 'tagsId', "SELECT id, name FROM ".TAGS_TABLE." WHERE id IN (".implode(',', $idList->getItems('tagsId')).") ORDER BY id;");
	273
	274	if(count($idList->getItems('userId')) > 0)
	275	$this->prepareIdList($idAssoc, 'userId', "SELECT id, username AS name FROM ".USERS_TABLE." WHERE id IN (".implode(',', $idList->getItems('userId')).") ORDER BY id;");
	276
	277	// for reverse DNS
	278	if($this->config['logs.ipCountry']=='y')
	279	$this->getIpInfos($idAssoc, $idList->getItems('IPadress'));
	280
	281	$data=array();
	282	// complete the data
	283	foreach($result as $row)
	284	{
	285	$ipInfo=$this->getId($idAssoc, 'IPadress', $row['IPadress'], '?');
	286
	287	$userAgentNfo=GPCUserAgent::getProperties(
	288	array(
	289	UA_DATA_BROWSER => $row['uaBrowser'],
	290	UA_DATA_BROWSER_TYPE => $row['uaType'],
	291	UA_DATA_OS => $row['uaOS'],
	292	UA_DATA_ENGINE => $row['uaEngine']
	293	)
	294	);
	295
	296	if($userAgentNfo[UA_DATA_ENGINE_NAME]=='Unknown') $userAgentNfo[UA_DATA_ENGINE_NAME]='ua_Unknown';
	297	if($userAgentNfo[UA_DATA_BROWSER_NAME]=='Unknown') $userAgentNfo[UA_DATA_BROWSER_NAME]='ua_Unknown';
	298	if($userAgentNfo[UA_DATA_OS_NAME]=='Unknown') $userAgentNfo[UA_DATA_OS_NAME]='ua_Unknown';
	299
	300	$country=l10n('estat_country_'.strtoupper($ipInfo['country']));
	301	if(isset($lang[$country]))
	302	{
	303	$country=$lang[$country];
	304	}
	305	else
	306	{
	307	$country='';
	308	}
	309
	310	//$userAgent = UserAgent::parse($row['userAgent']);
	311	$data[]=array(
	312	'date' => date('Y-m-d H:i:s', $row['date']),
	313	'IP' => $row['IPadress'],
	314	'UserId' => $row['userId'],
	315	'UserName' => $this->getId($idAssoc, 'userId', $row['userId'], '?', 'name'),
	316	'countryId' => $ipInfo['country'],
	317	'country' => $country,
	318	'catId' => $row['catId'],
	319	'catName' => GPCCore::getUserLanguageDesc($this->getId($idAssoc, 'catId', $row['catId'], ($row['catId']==0)?'-':'?')),
	320	'imageId' => $row['imageId'],
	321	'imageName' => $this->getId($idAssoc, 'imageId', $row['imageId'], ($row['imageId']==0)?'-':'?', 'name'),
	322	'tags' => $this->getId($idAssoc, 'tagsId', $row['tagsId'], '', 'name'),
	323	'section' => l10n($row['section']),
	324	'uaEngineId' => $row['uaEngine'],
	325	'uaEngineName' => l10n($userAgentNfo[UA_DATA_ENGINE_NAME]),
	326	'uaEngineVersion' => $row['uaEngineVersion'],
	327	'uaBrowserId' => $row['uaBrowser'],
	328	'uaBrowserName' => l10n($userAgentNfo[UA_DATA_BROWSER_NAME]),
	329	'uaBrowserVersion' => $row['uaBrowserVersion'],
	330	'uaOSId' => $row['uaOS'],
	331	'uaOSName' => l10n($userAgentNfo[UA_DATA_OS_NAME]),
	332	'uaOSVersion' => $row['uaOSVersion'],
	333	'uaTypeId' => $row['uaType'],
	334	'uaType' => l10n('ua_'.$userAgentNfo[UA_DATA_BROWSER_TYPE_NAME]),
	335	'userAgent' => $row['userAgent']
	336	);
	337	}
	338
	339
	340	switch($format)
	341	{
	342	case 'csv':
	343	$extension='.csv';
	344	$export=new GPCCSVExport();
	345	$export->setOptions(
	346	array(
	347	'separator' => ';',
	348	'decimalDot' => ',',
	349	'useQuotes' => true,
	350	'lineFeed' => 'unix'
	351	)
	352	);
	353	$export->setColumnsDef(
	354	array(
	355	array('name' => l10n('estat_datetime')),
	356	array('name' => l10n('estat_ipAdress')),
	357	array('name' => l10n('estat_userId'), 'type' => 'INTEGER'),
	358	array('name' => l10n('estat_userName')),
	359	array('name' => l10n('estat_countryId')),
	360	array('name' => l10n('estat_country')),
	361	array('name' => l10n('estat_categoryId'), 'type' => 'INTEGER'),
	362	array('name' => l10n('estat_category')),
	363	array('name' => l10n('estat_imageId'), 'type' => 'INTEGER'),
	364	array('name' => l10n('estat_image')),
	365	array('name' => l10n('estat_tags')),
	366	array('name' => l10n('estat_section')),
	367	array('name' => l10n('estat_engineId'), 'type' => 'INTEGER'),
	368	array('name' => l10n('estat_engine')),
	369	array('name' => l10n('estat_engineVersion')),
	370	array('name' => l10n('estat_browserId'), 'type' => 'INTEGER'),
	371	array('name' => l10n('estat_browser')),
	372	array('name' => l10n('estat_browserVersion')),
	373	array('name' => l10n('estat_osId'), 'type' => 'INTEGER'),
	374	array('name' => l10n('estat_os')),
	375	array('name' => l10n('estat_osVersion')),
	376	array('name' => l10n('estat_typeId'), 'type' => 'INTEGER'),
	377	array('name' => l10n('estat_type')),
	378	array('name' => l10n('estat_userAgentString'))
	379	)
	380	);
	381	break;
	382	case 'ods':
	383	$extension='.ods';
	384	$export=new GPCODSExport();
	385	$export->setOptions(
	386	array(
	387	'sheetName' => l10n('estat_history'),
	388	'fileTitle' => '',
	389	'fileSubject' => sprintf(l10n('estat_fileODSSubject'), l10n('estat_history')),
	390	'keywords' => '',
	391	'comments' => sprintf(l10n('estat_fileODSComments_1'), date('Y-m-d@H:i:s'), ESTAT_VERSION, PHPWG_VERSION)
	392	)
	393	);
	394	$export->setColumnsDef(
	395	array(
	396	array('name' => l10n('estat_datetime')),
	397	array('name' => l10n('estat_ipAdress')),
	398	array('name' => l10n('estat_userId'), 'type' => 'INTEGER'),
	399	array('name' => l10n('estat_userName')),
	400	array('name' => l10n('estat_countryId')),
	401	array('name' => l10n('estat_country')),
	402	array('name' => l10n('estat_categoryId'), 'type' => 'INTEGER'),
	403	array('name' => l10n('estat_category')),
	404	array('name' => l10n('estat_imageId'), 'type' => 'INTEGER'),
	405	array('name' => l10n('estat_image')),
	406	array('name' => l10n('estat_tags')),
	407	array('name' => l10n('estat_section')),
	408	array('name' => l10n('estat_engineId'), 'type' => 'INTEGER'),
	409	array('name' => l10n('estat_engine')),
	410	array('name' => l10n('estat_engineVersion')),
	411	array('name' => l10n('estat_browserId'), 'type' => 'INTEGER'),
	412	array('name' => l10n('estat_browser')),
	413	array('name' => l10n('estat_browserVersion')),
	414	array('name' => l10n('estat_osId'), 'type' => 'INTEGER'),
	415	array('name' => l10n('estat_os')),
	416	array('name' => l10n('estat_osVersion')),
	417	array('name' => l10n('estat_typeId'), 'type' => 'INTEGER'),
	418	array('name' => l10n('estat_type')),
	419	array('name' => l10n('estat_userAgentString'))
	420	)
	421	);
	422	break;
	423	case 'sqlite':
	424	$extension='.db';
	425	$export=new GPCSQLiteExport();
	426	$export->setOptions(
	427	array(
	428	'tableName' => 'history'
	429	)
	430	);
	431	$export->setColumnsDef(
	432	array(
	433	array('name' => 'datetime'),
	434	array('name' => 'ipAdress'),
	435	array('name' => 'userId', 'type' => 'INTEGER'),
	436	array('name' => 'userName'),
	437	array('name' => 'countryId'),
	438	array('name' => 'country'),
	439	array('name' => 'categoryId', 'type' => 'INTEGER'),
	440	array('name' => 'category'),
	441	array('name' => 'imageId', 'type' => 'INTEGER'),
	442	array('name' => 'image'),
	443	array('name' => 'tags'),
	444	array('name' => 'section'),
	445	array('name' => 'engineId', 'type' => 'INTEGER'),
	446	array('name' => 'engine'),
	447	array('name' => 'engineVersion'),
	448	array('name' => 'browserId', 'type' => 'INTEGER'),
	449	array('name' => 'browser'),
	450	array('name' => 'browserVersion'),
	451	array('name' => 'osId', 'type' => 'INTEGER'),
	452	array('name' => 'os'),
	453	array('name' => 'osVersion'),
	454	array('name' => 'typeId', 'type' => 'INTEGER'),
	455	array('name' => 'type'),
	456	array('name' => 'userAgentString')
	457	)
	458	);
	459	break;
	460	}
	461	$export->setFileName($name.$extension);
	462	$export->setFileDir(GPCCore::getPiwigoSystemPath().'/'.PWG_LOCAL_DIR.self::EXPORT_DIRECTORY);
	463
	464	if($export->write($data))
	465	{
	466	$file=GPCCore::getPiwigoSystemPath().'/'.PWG_LOCAL_DIR.self::EXPORT_DIRECTORY.$export->getFileName();
	467
	468	if($compression!='none')
	469	{
	470	$compressedFile=$this->compress($file, $compression);
	471	unlink($file);
	472	$file=$compressedFile;
	473	}
	474	if(file_exists($file))
	475	{
	476	return($file);
	477	}
	478	}
	479	return('KO');
	480	}
	481
	482
	483
	484
	485	/**
	486	* return the detail for IP from a period
	487	*
	488	* @param String $period : the period to be processed
	489	* 'allYear' => process all year
	490	* 'y-YYYY' => process year YYYY
	491	* 'ym-YYYYMM' => process year/month YYYY/MM
	492	* @param Array $filterPost : filter options
	493	* @param Array $sort: sort options
	494	* @param Array $group: grouped items; could be:
	495	* 'IPadress'
	496	* 'country'
	497	* 'uaType'
	498	* @param Integer $pageNumber : page number to return
	499	* @return String : an array in json format
	500	* (Array)
	501	* 'rows' => (Array)
	502	* 'IPadress' => (String)
	503	* 'country' => (String)
	504	* 'uaType' => (String)
	505	* 'nbVisits' => (Integer)
	506	* 'total' => (Array)
	507	* 'IPadress' => (String) [empty]
	508	* 'country' => (String) [empty]
	509	* 'uaType' => (String) [empty]
	510	* 'nbVisits' => (Integer)
	511	* 'nbItems' => (Integer)
	512	*/
	513	protected function ajax_estat_export_admin_statIP($period, $filterPost, $sort, $group, $format, $compression, $name)
	514	{
	515	global $lang;
	516
	517	$year=null;
	518	$month=null;
	519
	520	$fields=array(
	521	'rows' => array_diff(array('uaType', 'IPadress', 'country'), $group),
	522	'total' => array()
	523	);
	524	$filter=array();
	525
	526	if(substr($period,0,3)=='ym-')
	527	{
	528	$year=substr($period,3,4)*1;
	529	$month=substr($period,7,2)*1;
	530	}
	531	elseif(substr($period,0,2)=='y-')
	532	{
	533	$year=substr($period,2,4)*1;
	534	}
	535
	536	foreach($filterPost as $key => $val)
	537	{
	538	if($key==='additionalFilter')
	539	{
	540	if(isset($val['catId']))
	541	$filter['catId']=$this->buildCatIdFilter($val['catId']);
	542	}
	543	else
	544	{
	545	$filter[$val['id']]=$val;
	546	}
	547	}
	548
	549	if($year!=null)
	550	$filter['year']=array('operator' => '=', 'value' => $year);
	551	if($month!=null)
	552	$filter['month']=array('operator' => '=', 'value' => $month);
	553
	554	// open db file for the period
	555
	556
	557	$statFile=new StatDBGlobal($this->fileStatDir, self::FILE_GLOBAL);
	558
	559	$statFile->open(ASDF_OPEN_READ);
	560
	561	// get stats
	562	$result=$statFile->getStatIP(ASDF_GET_ROWS, 0, 0, $fields['rows'], $filter, $sort);
	563	$statFile->close();
	564
	565	$data=array();
	566	foreach($result as $row)
	567	{
	568	if(isset($row['uaType']))
	569	{
	570	$userAgentNfo=GPCUserAgent::getProperties(
	571	array(
	572	UA_DATA_BROWSER_TYPE => $row['uaType']
	573	)
	574	);
	575	$uaType=l10n('ua_'.$userAgentNfo[UA_DATA_BROWSER_TYPE_NAME]);
	576	}
	577	else
	578	{
	579	$row['uaType']='';
	580	$uaType='';
	581	}
	582
	583	if(isset($row['country']))
	584	{
	585	$country='estat_country_'.strtoupper($row['country']);
	586	if(isset($lang[$country]))
	587	{
	588	$country=$lang[$country];
	589	}
	590	else
	591	{
	592	$country='';
	593	}
	594	}
	595	else
	596	{
	597	$country='';
	598	$row['country']='';
	599	}
	600
	601	if(!isset($row['IPadress']))
	602	{
	603	$row['IPadress']='';
	604	}
	605
	606	$data[]=array(
	607	$row['IPadress'],
	608	$row['country'],
	609	$country,
	610	$row['uaType'],
	611	$uaType,
	612	$row['nbVisits']
	613	);
	614	}
	615
	616
	617
	618	switch($format)
	619	{
	620	case 'csv':
	621	$extension='.csv';
	622	$export=new GPCCSVExport();
	623	$export->setOptions(
	624	array(
	625	'separator' => ';',
	626	'decimalDot' => ',',
	627	'useQuotes' => true,
	628	'lineFeed' => 'unix'
	629	)
	630	);
	631	$export->setColumnsDef(
	632	array(
	633	array('name' => l10n('estat_ipAdress')),
	634	array('name' => l10n('estat_countryID')),
	635	array('name' => l10n('estat_country')),
	636	array('name' => l10n('estat_typeId'), 'type' => 'INTEGER'),
	637	array('name' => l10n('estat_type')),
	638	array('name' => l10n('estat_visits'), 'type' => 'INTEGER')
	639	)
	640	);
	641	break;
	642	case 'ods':
	643	$extension='.ods';
	644	$export=new GPCODSExport();
	645	$export->setOptions(
	646	array(
	647	'sheetName' => l10n('estat_ipAdress'),
	648	'fileTitle' => '',
	649	'fileSubject' => sprintf(l10n('estat_fileODSSubject'), l10n('estat_by_ip')),
	650	'keywords' => '',
	651	'comments' => sprintf(l10n('estat_fileODSComments_1'), date('Y-m-d@H:i:s'), ESTAT_VERSION, PHPWG_VERSION)
	652	)
	653	);
	654	$export->setColumnsDef(
	655	array(
	656	array('name' => l10n('estat_ipAdress')),
	657	array('name' => l10n('estat_countryId')),
	658	array('name' => l10n('estat_country')),
	659	array('name' => l10n('estat_typeId'), 'type' => 'INTEGER'),
	660	array('name' => l10n('estat_type')),
	661	array('name' => l10n('estat_visits'), 'type' => 'INTEGER')
	662	)
	663	);
	664	break;
	665	case 'sqlite':
	666	$extension='.db';
	667	$export=new GPCSQLiteExport();
	668	$export->setOptions(
	669	array(
	670	'tableName' => 'history'
	671	)
	672	);
	673	$export->setColumnsDef(
	674	array(
	675	array('name' => 'ipAdress'),
	676	array('name' => 'countryId'),
	677	array('name' => 'country'),
	678	array('name' => 'typeId', 'type' => 'INTEGER'),
	679	array('name' => 'type'),
	680	array('name' => 'visits', 'type' => 'INTEGER')
	681	)
	682	);
	683	break;
	684	}
	685	$export->setFileName($name.$extension);
	686	$export->setFileDir(GPCCore::getPiwigoSystemPath().'/'.PWG_LOCAL_DIR.self::EXPORT_DIRECTORY);
	687
	688	if($export->write($data))
	689	{
	690	$file=GPCCore::getPiwigoSystemPath().'/'.PWG_LOCAL_DIR.self::EXPORT_DIRECTORY.$export->getFileName();
	691
	692	if($compression!='none')
	693	{
	694	$compressedFile=$this->compress($file, $compression);
	695	unlink($file);
	696	$file=$compressedFile;
	697	}
	698	if(file_exists($file))
	699	{
	700	return($file);
	701	}
	702	}
	703	return('KO');
	704	}
	705
	706
	707
	708
	709	/**
	710	* return the detail for albums/categories from a period
	711	*
	712	* @param String $period : the period to be processed
	713	* 'allYear' => process all year
	714	* 'y-YYYY' => process year YYYY
	715	* 'ym-YYYYMM' => process year/month YYYY/MM
	716	* @param Array $filterPost : filter options
	717	* @param Array $sort: sort options
	718	* @param Array $group: grouped items; could be:
	719	* 'uaType'
	720	* @return String : an array in json format
	721	* (Array)
	722	* 'rows' => (Array)
	723	* 'category' => (String)
	724	* 'uaType' => (String)
	725	* 'nbVisits' => (Integer)
	726	* 'total' => (Array)
	727	* 'category' => (String) [empty]
	728	* 'uaType' => (String) [empty]
	729	* 'nbVisits' => (Integer)
	730	* 'nbItems' => (Integer)
	731	*/
	732	protected function ajax_estat_export_admin_statCategory($period, $filterPost, $sort, $group, $format, $compression, $name)
	733	{
	734	global $lang;
	735
	736	$year=null;
	737	$month=null;
	738
	739	$fields=array(
	740	'rows' => array_diff(array('catId', 'uaType'), $group),
	741	'total' => array()
	742	);
	743	$filter=array();
	744
	745	if(substr($period,0,3)=='ym-')
	746	{
	747	$year=substr($period,3,4)*1;
	748	$month=substr($period,7,2)*1;
	749	}
	750	elseif(substr($period,0,2)=='y-')
	751	{
	752	$year=substr($period,2,4)*1;
	753	}
	754
	755	foreach($filterPost as $key => $val)
	756	{
	757	if($key==='additionalFilter')
	758	{
	759	if(isset($val['catId']))
	760	$filter['catId']=$this->buildCatIdFilter($val['catId']);
	761	}
	762	else
	763	{
	764	$filter[$val['id']]=$val;
	765	}
	766	}
	767
	768	if($year!=null)
	769	$filter['year']=array('operator' => '=', 'value' => $year);
	770	if($month!=null)
	771	$filter['month']=array('operator' => '=', 'value' => $month);
	772
	773	// open db file for the period
	774
	775
	776	$statFile=new StatDBGlobal($this->fileStatDir, self::FILE_GLOBAL);
	777
	778	$statFile->open(ASDF_OPEN_READ);
	779
	780	// get stats
	781	$result=$statFile->getStatCat(ASDF_GET_ROWS, 0, 0, $fields['rows'], $filter, $sort);
	782	$statFile->close();
	783
	784	// prepare categories&images properties
	785	$idList=new EStat_IdList(array('catId', 'imageId'));
	786	$idAssoc=array(
	787	'catId'=>array(),
	788	'imageId'=>array()
	789	);
	790
	791	// first, build category_id list
	792	foreach($result as $row)
	793	{
	794	$idList->addItems(array('catId' => $row['catId']));
	795	}
	796
	797	// get all needed properties for each category_id
	798	if(count($idList->getItems('catId')) > 0)
	799	$this->prepareIdList($idAssoc, 'catId', "SELECT id, name FROM ".CATEGORIES_TABLE." WHERE id IN (".implode(',', $idList->getItems('catId')).") ORDER BY id;");
	800
	801	if(count($idList->getItems('imageId')) > 0)
	802	$this->prepareIdList($idAssoc, 'imageId', "SELECT id, file AS name, path FROM ".IMAGES_TABLE." WHERE id IN (".implode(',', $idList->getItems('imageId')).") ORDER BY id;");
	803
	804	$data=array();
	805	foreach($result as $row)
	806	{
	807	if(isset($row['uaType']))
	808	{
	809	$userAgentNfo=GPCUserAgent::getProperties(
	810	array(
	811	UA_DATA_BROWSER_TYPE => $row['uaType']
	812	)
	813	);
	814	$uaType=l10n('ua_'.$userAgentNfo[UA_DATA_BROWSER_TYPE_NAME]);
	815	}
	816	else
	817	{
	818	$row['uaType']='';
	819	$uaType='';
	820	}
	821
	822
	823	if($row['catId']==0)
	824	{
	825	$categoryNfo=l10n('estat_special_category');
	826	}
	827	elseif($this->getId($idAssoc, 'catId', $row['catId'], '', 'name')=='')
	828	{
	829	$categoryNfo=l10n('estat_deleted_category');
	830	}
	831	else
	832	{
	833	$categoryNfo=$this->getId($idAssoc, 'catId', $row['catId'], '', 'name');
	834	}
	835
	836	$data[]=array(
	837	$row['catId'],
	838	$categoryNfo,
	839	$row['uaType'],
	840	$uaType,
	841	$row['nbVisits']
	842	);
	843	}
	844
	845
	846
	847	switch($format)
	848	{
	849	case 'csv':
	850	$extension='.csv';
	851	$export=new GPCCSVExport();
	852	$export->setOptions(
	853	array(
	854	'separator' => ';',
	855	'decimalDot' => ',',
	856	'useQuotes' => true,
	857	'lineFeed' => 'unix'
	858	)
	859	);
	860	$export->setColumnsDef(
	861	array(
	862	array('name' => l10n('estat_categoryId')),
	863	array('name' => l10n('estat_category')),
	864	array('name' => l10n('estat_typeId'), 'type' => 'INTEGER'),
	865	array('name' => l10n('estat_type')),
	866	array('name' => l10n('estat_visits'), 'type' => 'INTEGER')
	867	)
	868	);
	869	break;
	870	case 'ods':
	871	$extension='.ods';
	872	$export=new GPCODSExport();
	873	$export->setOptions(
	874	array(
	875	'sheetName' => l10n('estat_ipAdress'),
	876	'fileTitle' => '',
	877	'fileSubject' => sprintf(l10n('estat_fileODSSubject'), l10n('estat_by_ip')),
	878	'keywords' => '',
	879	'comments' => sprintf(l10n('estat_fileODSComments_1'), date('Y-m-d@H:i:s'), ESTAT_VERSION, PHPWG_VERSION)
	880	)
	881	);
	882	$export->setColumnsDef(
	883	array(
	884	array('name' => l10n('estat_categoryId')),
	885	array('name' => l10n('estat_category')),
	886	array('name' => l10n('estat_typeId'), 'type' => 'INTEGER'),
	887	array('name' => l10n('estat_type')),
	888	array('name' => l10n('estat_visits'), 'type' => 'INTEGER')
	889	)
	890	);
	891	break;
	892	case 'sqlite':
	893	$extension='.db';
	894	$export=new GPCSQLiteExport();
	895	$export->setOptions(
	896	array(
	897	'tableName' => 'history'
	898	)
	899	);
	900	$export->setColumnsDef(
	901	array(
	902	array('name' => 'albumId'),
	903	array('name' => 'album'),
	904	array('name' => 'typeId', 'type' => 'INTEGER'),
	905	array('name' => 'type'),
	906	array('name' => 'visits', 'type' => 'INTEGER')
	907	)
	908	);
	909	break;
	910	}
	911	$export->setFileName($name.$extension);
	912	$export->setFileDir(GPCCore::getPiwigoSystemPath().'/'.PWG_LOCAL_DIR.self::EXPORT_DIRECTORY);
	913
	914	if($export->write($data))
	915	{
	916	$file=GPCCore::getPiwigoSystemPath().'/'.PWG_LOCAL_DIR.self::EXPORT_DIRECTORY.$export->getFileName();
	917
	918	if($compression!='none')
	919	{
	920	$compressedFile=$this->compress($file, $compression);
	921	unlink($file);
	922	$file=$compressedFile;
	923	}
	924	if(file_exists($file))
	925	{
	926	return($file);
	927	}
	928	}
	929	return('KO');
	930	}
	931
	932
	933
	934
	935
	936	/**
	937	* return the detail for images from a period
	938	*
	939	* @param String $period : the period to be processed
	940	* 'allYear' => process all year
	941	* 'y-YYYY' => process year YYYY
	942	* 'ym-YYYYMM' => process year/month YYYY/MM
	943	* @param Array $filterPost : filter options
	944	* @param Array $sort: sort options
	945	* @param Array $group: grouped items; could be:
	946	* 'uaType'
	947	* @param Integer $pageNumber : page number to return
	948	* @return String : an array in json format
	949	* (Array)
	950	* 'rows' => (Array)
	951	* 'category' => (String)
	952	* 'uaType' => (String)
	953	* 'nbVisits' => (Integer)
	954	* 'total' => (Array)
	955	* 'category' => (String) [empty]
	956	* 'uaType' => (String) [empty]
	957	* 'nbVisits' => (Integer)
	958	* 'nbItems' => (Integer)
	959	*/
	960	protected function ajax_estat_export_admin_statImage($period, $filterPost, $sort, $group, $format, $compression, $name)
	961	{
	962	global $lang;
	963
	964	$year=null;
	965	$month=null;
	966
	967	$fields=array(
	968	'rows' => array_diff(array('imageId', 'catId', 'uaType'), $group),
	969	'total' => array()
	970	);
	971	$filter=array();
	972
	973	if(substr($period,0,3)=='ym-')
	974	{
	975	$year=substr($period,3,4)*1;
	976	$month=substr($period,7,2)*1;
	977	}
	978	elseif(substr($period,0,2)=='y-')
	979	{
	980	$year=substr($period,2,4)*1;
	981	}
	982
	983	foreach($filterPost as $key => $val)
	984	{
	985	if($key==='additionalFilter')
	986	{
	987	if(isset($val['catId']))
	988	$filter['catId']=$this->buildCatIdFilter($val['catId']);
	989	}
	990	else
	991	{
	992	$filter[$val['id']]=$val;
	993	}
	994	}
	995
	996	if($year!=null)
	997	$filter['year']=array('operator' => '=', 'value' => $year);
	998	if($month!=null)
	999	$filter['month']=array('operator' => '=', 'value' => $month);
	1000
	1001	// open db file for the period
	1002	$statFile=new StatDBGlobal($this->fileStatDir, self::FILE_GLOBAL);
	1003	$statFile->open(ASDF_OPEN_READ);
	1004
	1005	// get stats
	1006	$result=$statFile->getStatImages(ASDF_GET_ROWS, 0, 0, $fields['rows'], $filter, $sort);
	1007	$statFile->close();
	1008
	1009	// prepare categories&images properties
	1010	$idList=new EStat_IdList(array('catId', 'imageId'));
	1011	$idAssoc=array(
	1012	'catId'=>array(),
	1013	'imageId'=>array()
	1014	);
	1015
	1016	// first, build category_id list
	1017	foreach($result as $row)
	1018	{
	1019	$idList->addItems(array('catId' => $row['catId']));
	1020	$idList->addItems(array('imageId' => $row['imageId']));
	1021	}
	1022
	1023	// get all needed properties for each category_id
	1024	if(count($idList->getItems('catId')) > 0)
	1025	$this->prepareIdList($idAssoc, 'catId', "SELECT id, name FROM ".CATEGORIES_TABLE." WHERE id IN (".implode(',', $idList->getItems('catId')).") ORDER BY id;");
	1026
	1027	if(count($idList->getItems('imageId')) > 0)
	1028	$this->prepareIdList($idAssoc, 'imageId', "SELECT id, file AS name, path FROM ".IMAGES_TABLE." WHERE id IN (".implode(',', $idList->getItems('imageId')).") ORDER BY id;");
	1029
	1030	$data=array();
	1031	foreach($result as $row)
	1032	{
	1033	if(isset($row['uaType']))
	1034	{
	1035	$userAgentNfo=GPCUserAgent::getProperties(
	1036	array(
	1037	UA_DATA_BROWSER_TYPE => $row['uaType']
	1038	)
	1039	);
	1040	$uaType=l10n('ua_'.$userAgentNfo[UA_DATA_BROWSER_TYPE_NAME]);
	1041	}
	1042	else
	1043	{
	1044	$row['uaType']='';
	1045	$uaType='';
	1046	}
	1047
	1048	if($row['catId']==0)
	1049	{
	1050	$catName=l10n('estat_special_category');
	1051	}
	1052	elseif($this->getId($idAssoc, 'catId', $row['catId'], '', 'name')=='')
	1053	{
	1054	$catName=l10n('estat_deleted_category').' ['.$row['catId'].']';
	1055	}
	1056	else
	1057	{
	1058	$catName=$this->getId($idAssoc, 'catId', $row['catId'], '', 'name');
	1059	}
	1060
	1061	$imageNfo=$this->getId($idAssoc, 'imageId', $row['imageId'], '', 'name');
	1062	if($imageNfo!='')
	1063	{
	1064	$imageName=$imageNfo;
	1065	}
	1066	else
	1067	{
	1068	$imageName=l10n('estat_deleted_image');
	1069	}
	1070
	1071
	1072	$data[]=array(
	1073	$row['catId'],
	1074	$catName,
	1075	$row['imageId'],
	1076	$imageName,
	1077	$row['uaType'],
	1078	$uaType,
	1079	$row['nbVisits']
	1080	);
	1081	}
	1082
	1083
	1084
	1085	switch($format)
	1086	{
	1087	case 'csv':
	1088	$extension='.csv';
	1089	$export=new GPCCSVExport();
	1090	$export->setOptions(
	1091	array(
	1092	'separator' => ';',
	1093	'decimalDot' => ',',
	1094	'useQuotes' => true,
	1095	'lineFeed' => 'unix'
	1096	)
	1097	);
	1098	$export->setColumnsDef(
	1099	array(
	1100	array('name' => l10n('estat_categoryId')),
	1101	array('name' => l10n('estat_category')),
	1102	array('name' => l10n('estat_imageId')),
	1103	array('name' => l10n('estat_image')),
	1104	array('name' => l10n('estat_typeId'), 'type' => 'INTEGER'),
	1105	array('name' => l10n('estat_type')),
	1106	array('name' => l10n('estat_visits'), 'type' => 'INTEGER')
	1107	)
	1108	);
	1109	break;
	1110	case 'ods':
	1111	$extension='.ods';
	1112	$export=new GPCODSExport();
	1113	$export->setOptions(
	1114	array(
	1115	'sheetName' => l10n('estat_ipAdress'),
	1116	'fileTitle' => '',
	1117	'fileSubject' => sprintf(l10n('estat_fileODSSubject'), l10n('estat_by_ip')),
	1118	'keywords' => '',
	1119	'comments' => sprintf(l10n('estat_fileODSComments_1'), date('Y-m-d@H:i:s'), ESTAT_VERSION, PHPWG_VERSION)
	1120	)
	1121	);
	1122	$export->setColumnsDef(
	1123	array(
	1124	array('name' => l10n('estat_categoryId')),
	1125	array('name' => l10n('estat_category')),
	1126	array('name' => l10n('estat_imageId')),
	1127	array('name' => l10n('estat_image')),
	1128	array('name' => l10n('estat_typeId'), 'type' => 'INTEGER'),
	1129	array('name' => l10n('estat_type')),
	1130	array('name' => l10n('estat_visits'), 'type' => 'INTEGER')
	1131	)
	1132	);
	1133	break;
	1134	case 'sqlite':
	1135	$extension='.db';
	1136	$export=new GPCSQLiteExport();
	1137	$export->setOptions(
	1138	array(
	1139	'tableName' => 'history'
	1140	)
	1141	);
	1142	$export->setColumnsDef(
	1143	array(
	1144	array('name' => 'albumId'),
	1145	array('name' => 'album'),
	1146	array('name' => 'imageId'),
	1147	array('name' => 'image'),
	1148	array('name' => 'typeId', 'type' => 'INTEGER'),
	1149	array('name' => 'type'),
	1150	array('name' => 'visits', 'type' => 'INTEGER')
	1151	)
	1152	);
	1153	break;
	1154	}
	1155	$export->setFileName($name.$extension);
	1156	$export->setFileDir(GPCCore::getPiwigoSystemPath().'/'.PWG_LOCAL_DIR.self::EXPORT_DIRECTORY);
	1157
	1158	if($export->write($data))
	1159	{
	1160	$file=GPCCore::getPiwigoSystemPath().'/'.PWG_LOCAL_DIR.self::EXPORT_DIRECTORY.$export->getFileName();
	1161
	1162	if($compression!='none')
	1163	{
	1164	$compressedFile=$this->compress($file, $compression);
	1165	unlink($file);
	1166	$file=$compressedFile;
	1167	}
	1168	if(file_exists($file))
	1169	{
	1170	return($file);
	1171	}
	1172	}
	1173	return('KO');
	1174	}
	1175
	1176
	1177	/**
	1178	* used for file download
	1179	* stop the script and returns the file content
	1180	*
	1181	* @param String $name: file to upload
	1182	* @param Booelan $deleteFile: is true, given file is deleted after content is returned
	1183	* @return String: the file content with http header
	1184	*/
	1185	private function ajax_estat_export_admin_loadFile($name, $deleteFile=true)
	1186	{
	1187	$file=GPCCore::getPiwigoSystemPath().'/'.PWG_LOCAL_DIR.self::EXPORT_DIRECTORY.$name;
	1188	if(!file_exists($file))
	1189	do_error(404, "Requested file not readable - $name");
	1190
	1191	$gmtMtime=gmdate('D, d M Y H:i:s', filemtime($file)).' GMT';
	1192
	1193	$infos=pathinfo($file, PATHINFO_EXTENSION);
	1194
	1195	switch($infos)
	1196	{
	1197	case 'csv':
	1198	$mimeType='text/csv';
	1199	break;
	1200	case 'ods':
	1201	$mimeType='application/vnd.oasis.opendocument.spreadsheet';
	1202	break;
	1203	case 'db';
	1204	$mimeType='application/octet-stream';
	1205	break;
	1206	case 'gz':
	1207	$mimeType='application/gzip';
	1208	break;
	1209	case 'bz2':
	1210	$mimeType='application/zip';
	1211	break;
	1212	case 'zip':
	1213	$mimeType='application/zip';
	1214	break;
	1215	default:
	1216	$mimeType='application/octet-stream';
	1217	break;
	1218	}
	1219
	1220	$httpHeaders=array(
	1221	'Content-Length: '.@filesize($file),
	1222	'Last-Modified: '.$gmtMtime,
	1223	'Content-Type: '.$mimeType,
	1224	'Content-Disposition: attachment; filename="'.basename($file).'";',
	1225	'Content-Transfer-Encoding: binary',
	1226	);
	1227
	1228	foreach ($httpHeaders as $header)
	1229	{
	1230	header($header);
	1231	}
	1232
	1233	$content=@readfile($file);
	1234	if($deleteFile) unlink($file);
	1235	die($content);
	1236	} // ajax_estat_export_admin_loadFile
	1237
	1238
	1239	/* ------------------------------------------------------------------------------------------------
	1240	-- private method
	1241	------------------------------------------------------------------------------------------------ */
	1242
	1243	/**
	1244	* compress the file, according to the asked method
	1245	* the file will be compressed in the same directory than the source file
	1246	*
	1247	* @param String $file: file to compress
	1248	* @return String: compressed file name, or empty string if something wrong
	1249	*/
	1250	private function compress($file, $method)
	1251	{
	1252	if(!file_exists($file)) return('');
	1253	$infos=pathinfo($file);
	1254
	1255	switch($method)
	1256	{
	1257	case 'zip':
	1258	$compressedFile=$infos['dirname'].'/'.basename($infos['basename'], '.'.$infos['extension']).'.zip';
	1259	if(GPCCompress::zip($file, $compressedFile, '', $infos['dirname']))
	1260	return($compressedFile);
	1261	break;
	1262	case 'gz':
	1263	$compressedFile=$file.'.gz';
	1264	if(GPCCompress::gzip($file, $compressedFile))
	1265	return($compressedFile);
	1266	break;
	1267	case 'bz2':
	1268	$compressedFile=$file.'.bz2';
	1269	if(GPCCompress::bzip2($file, $compressedFile))
	1270	return($compressedFile);
	1271	break;
	1272	}
	1273	return('');
	1274	} //compress
	1275
	1276	} //class EStat_AjaxExport
	1277
	1278
	1279
	1280
	1281
	1282
	1283	$returned=new EStat_AjaxExport($prefixeTable, __FILE__);
	1284	?>

Note: See TracBrowser for help on using the repository browser.

Download in other formats: