$richiestePerPagina = Database::ask("SELECT cartella_pagina, COUNT (cartella_pagina) AS richieste FROM get \n\t\t\t\t\t\tGROUP BY (cartella_pagina) ORDER BY richieste DESC LIMIT 20", []);
$richiestePerPaginaJSON = json_encode($richiestePerPagina);
echo $richiestePerPaginaJSON;
} elseif ($graficoRichiesto == "story") {
$array_finale = array();
$data = Database::ask("SELECT first_page, GROUP_CONCAT(cartella_pagina || '-' || conta) as cartelle_pagina, sum(conta) from (\n\tselect '/' || substr(replace(refferrer, 'http://atletica.me/', ''), 0, instr(replace(refferrer, 'http://atletica.me/', ''), '/') +1) as first_page, cartella_pagina, count(*) as conta\n\tfrom get as g1\n\twhere refferrer like 'http://atletica.me%' and cartella_pagina not like '%.%' and cartella_pagina not like '%-%'\n\t and first_page not like '%.%' and first_page not like '%-%'\n\tgroup by substr(replace(refferrer, 'http://atletica.me', ''), 0, instr(replace(refferrer, 'http://atletica.me/', ''), '/') +1), cartella_pagina\n\torder by count(*) desc \n\t)\n\tgroup by first_page\n\torder by sum(conta) desc\n\tlimit 0,10", []);
$i = 0;
foreach ($data as $cartella) {
$arr = explode(",", $cartella["cartelle_pagina"]);
$j = 0;
$array_finale[$i] = array();
foreach ($arr as $obj) {
$po = explode("-", $obj);
$array_finale[$i][$j]["cartella_partenza"] = $cartella["first_page"];
$array_finale[$i][$j]["cartella_destinazione"] = $po[0];
$array_finale[$i][$j]["occorrenze"] = $po[1];
$j++;
if ($j > 9) {
break;
}
}
$i++;
}
$dataJSON = json_encode($array_finale);
echo $dataJSON;
} elseif ($graficoRichiesto == "firstPage") {
$arr_data = array('site_name' => $nome_sito . '%');
$query = "\tSELECT cartella_pagina, count(*) as occorrenze\n\t\t\t\tFROM get \n\t\t\t\tWHERE refferrer NOT LIKE :site_name \n\t\t\t\tGROUP BY cartella_pagina \n\t\t\t\tORDER BY count(*) DESC\n\t\t\t\tLIMIT 0,10";
$data = Database::ask($query, $arr_data);
echo json_encode($data);
}
