<?phprequire('config.inc.php');date_default_timezone_set("Asia/Shanghai");header("Content-type: text/html; charset=utf-8");$str_from=$_POST["from"].' 00:00:00';$str_to=$_POST["to"].' 00:00:00';$from_time=strtotime($str_from);$to_time=strtotime($str_to);if($from_time>=$to_time){echo'起始时间必须小于截止时间';return;}$conn=mysqli_connect($_CFG['mysql_host'],$_CFG['mysql_user'],$_CFG['mysql_pwd'],$_CFG['mysql_db']);if(mysqli_connect_errno($conn)){echo"mysql连接失败!".mysqli_connect_error();return;}$KEY_startGame="\"startGame\"";//启动游戏$KEY_share="\"share\"";//分享游戏$KEY_video="\"reliveVideo\"";//视频观看$Pi_2=2;echo'<p>开始查询, 操作时间 '.date("Y-m-d H:i:s",time()).'</p>';$strsql="SELECT COUNT(1) FROM (SELECT COUNT(1) AS AU FROM `knocking_statis` WHERE `time` >= $from_time AND `time` < $to_time AND `key` = $KEY_startGame GROUP BY `role_id`) TAU";//var_dump($strsql);// 活跃值查询$result=mysqli_query($conn,"SELECT COUNT(1) FROM (SELECT COUNT(1) AS AU FROM `knocking_statis` WHERE `time` >= $from_time AND `time` < $to_time AND `key` = $KEY_startGame GROUP BY `role_id`) TAU");$row=mysqli_fetch_row($result);$AU=$row[0];// 总启动次数$result=mysqli_query($conn,"SELECT SUM(AU) FROM (SELECT COUNT(1) AS AU FROM `knocking_statis` WHERE `time` >= $from_time AND `time` < $to_time AND `key` = $KEY_startGame GROUP BY `role_id`) TAU");$row=mysqli_fetch_row($result);$startCount=$row[0];// 总分享次数$result=mysqli_query($conn,"SELECT SUM(AU) FROM (SELECT COUNT(1) AS AU FROM `knocking_statis` WHERE `time` >= $from_time AND `time` < $to_time AND `pi` = $Pi_2 AND `key` = $KEY_share GROUP BY `role_id`) TAU");$row=mysqli_fetch_row($result);$shareCount=$row[0];// 总分享人数$result=mysqli_query($conn,"SELECT COUNT(1) FROM (SELECT COUNT(1) AS AU FROM `knocking_statis` WHERE `time` >= $from_time AND `time` < $to_time AND `pi` = $Pi_2 AND `key` = $KEY_share GROUP BY `role_id`) TAU");$row=mysqli_fetch_row($result);$sharePlayer=$row[0];// 完成视频观看次数$result=mysqli_query($conn,"SELECT SUM(AU) FROM (SELECT COUNT(1) AS AU FROM `knocking_statis` WHERE `time` >= $from_time AND `time` < $to_time AND `pi` = $Pi_2 AND `key` = $KEY_video GROUP BY `role_id`) TAU");$row=mysqli_fetch_row($result);$videoCount=$row[0];echo'基础统计<br>';echo'<table border="1" cellpadding="0" cellspacing="0">';echo'<tr>';echo'<th>活跃值</th>';echo'<td align="center">';echo$AU;echo'</td>';echo'</tr>';echo'<tr>';echo'<th>启动次数</th>';echo'<td align="center">';echo$startCount;echo'</td>';echo'</tr>';echo'<tr>';echo'<th>完成分享次数</th>';echo'<td align="center">';echo$shareCount;echo'</td>';echo'</tr>';echo'<tr>';echo'<th>完成分享人数</th>';echo'<td align="center">';echo$sharePlayer;echo'</td>';echo'</tr>';echo'<tr>';echo'<th>视频观看次数</th>';echo'<td align="center">';echo$videoCount;echo'</td>';echo'</tr>';echo'</table>';echo'<p>查询完成, 操作时间 '.date("Y-m-d H:i:s",time()).'</p>';//==============================echo'<br>分享汇总<br>';// ====================================================// 分享汇总$statelist=array();$result=mysqli_query($conn,"SELECT `pi` FROM `knocking_statis` WHERE `key` = $KEY_share GROUP BY `pi`");if(mysqli_num_rows($result)>0){while($row=mysqli_fetch_row($result)){array_push($statelist,$row);}}$shareResult=array();for($i=0;$i<sizeof($statelist);++$i){$spi=$statelist[$i][0];$pi=(int)$spi;//var_dump($pi); $strsql="SELECT ps as tps, (SELECT COUNT(1) FROM `knocking_statis` WHERE `time` >= $from_time AND `time` < $to_time AND ps = tps AND `key` = $KEY_share AND `pi` = $pi) AS Number from `knocking_statis` WHERE `time` >= $from_time AND `time` < $to_time AND `key` = $KEY_share AND `pi` = $pi GROUP BY ps ORDER BY Number DESC";//var_dump($strsql);$result=mysqli_query($conn,$strsql);if(mysqli_num_rows($result)>0){while($row=mysqli_fetch_row($result)){$rowkey=$row[0];if(array_key_exists($rowkey,$shareResult)==false){$shareResult[$rowkey]=array('s0'=>0,'s1'=>0,'s2'=>0);//var_dump($shareResult);//echo "add item to shareResult";}$shareResult[$rowkey]['s'.$spi]=$row[1];}}}//var_dump($shareResult);if(empty($shareResult)==false){echo'<table border="1" cellpadding="0" cellspacing="0">';echo'<tr>';echo'<th></th>';echo'<th>分享点</th>';//echo '<th>Number</th>'; echo'<th>S0</th>';echo'<th>S1</th>';echo'<th>完成分享(S2)</th>';echo'<th>完成占比</th>';echo'</tr>';$index=1;foreach($shareResultas$key=>$value){echo'<tr>';echo'<td align="center">';echo$index++;echo'</td>';echo'<td bgcolor="#FFFFFF" align="center">';if($key==''){echo'无';}else{echo$key;}echo'</td>';foreach($valueas$ikey=>$ivalue){echo'<td bgcolor="#FFFFFF" align="center">';echo$ivalue;echo'</td>';}echo'<td bgcolor="#FFFFFF" align="center">';$percentage=$shareCount==0?0:$value['s2']/$shareCount;echosprintf("%.2f%%",$percentage*100);echo'</td>';echo'</tr>';}echo'</table>';echo'总计分享 '.$shareCount;echo'<p>查询完成, 操作时间 '.date("Y-m-d H:i:s",time()).'</p>';}else{echo'分享汇总记录为空!';}/*$strsql = "SELECT ps as tps, (SELECT COUNT(1) FROM `knocking_statis` WHERE `time` >= $from_time AND `time` < $to_time AND ps = tps AND `key` = $KEY_share AND `pi` = $Pi_2) AS Number from `knocking_statis` WHERE `time` >= $from_time AND `time` < $to_time AND `key` = $KEY_share AND `pi` = $Pi_2 GROUP BY ps ORDER BY Number DESC";
$result = mysqli_query($conn, $strsql);
if (mysqli_num_rows($result) > 0) {
echo '<table border="1" cellpadding="0" cellspacing="0">';
echo '<tr>';
echo '<th>Order</th>';
echo '<th>From</th>';
echo '<th>Number</th>';
echo '<th>Percent</th>';
echo '</tr>';
$index = 1;
while ($row=mysqli_fetch_row($result)) {
echo '<tr>';
echo '<td align="center">';
echo $index++;
echo '</td>';
echo '<td bgcolor="#FFFFFF" align="center">';
if ($row[0] == '') {
echo '无';
}
else {
echo $row[0];
}
echo '</td>';
echo '<td bgcolor="#FFFFFF" align="center">';
echo $row[1];
echo '</td>';
echo '<td bgcolor="#FFFFFF" align="center">';
$percentage = $shareCount == 0 ? 0 : $row[1]/$shareCount;
echo sprintf("%.2f%%",$percentage * 100);
echo '</td>';
echo '</tr>';
}
echo '</table>';
echo '总计分享 '.$shareCount;
echo '<p>查询完成, 操作时间 '.date("Y-m-d H:i:s",time()).'</p>';
} else {
echo '分享汇总记录为空!';
}*/// ====================================================// 视频汇总// //==============================echo'<br>视频汇总<br>';$statelist=array();$result=mysqli_query($conn,"SELECT `pi` FROM `knocking_statis` WHERE `key` = $KEY_video GROUP BY `pi`");if(mysqli_num_rows($result)>0){while($row=mysqli_fetch_row($result)){array_push($statelist,$row);}}$shareResult=array();for($i=0;$i<sizeof($statelist);++$i){$spi=$statelist[$i][0];$pi=(int)$spi;//var_dump($pi); $strsql="SELECT ps as tps, (SELECT COUNT(1) FROM `knocking_statis` WHERE `time` >= $from_time AND `time` < $to_time AND ps = tps AND `key` = $KEY_video AND `pi` = $pi) AS Number from `knocking_statis` WHERE `time` >= $from_time AND `time` < $to_time AND `key` = $KEY_video AND `pi` = $pi GROUP BY ps ORDER BY Number DESC";//var_dump($strsql);$result=mysqli_query($conn,$strsql);if(mysqli_num_rows($result)>0){while($row=mysqli_fetch_row($result)){$rowkey=$row[0];if(array_key_exists($rowkey,$shareResult)==false){$shareResult[$rowkey]=array('s0'=>0,'s1'=>0,'s2'=>0);//var_dump($shareResult);//echo "add item to shareResult";}$shareResult[$rowkey]['s'.$spi]=$row[1];}}}//var_dump($shareResult);if(empty($shareResult)==false){echo'<table border="1" cellpadding="0" cellspacing="0">';echo'<tr>';echo'<th></th>';echo'<th>观看点</th>';//echo '<th>Number</th>'; echo'<th>S0</th>';echo'<th>S1</th>';echo'<th>完成观看(S2)</th>';echo'<th>完成占比</th>';echo'</tr>';$index=1;foreach($shareResultas$key=>$value){echo'<tr>';echo'<td align="center">';echo$index++;echo'</td>';echo'<td bgcolor="#FFFFFF" align="center">';if($key==''){echo'无';}else{echo$key;}echo'</td>';foreach($valueas$ikey=>$ivalue){echo'<td bgcolor="#FFFFFF" align="center">';echo$ivalue;echo'</td>';}echo'<td bgcolor="#FFFFFF" align="center">';if($videoCount>0){$percentage=$shareCount==0?0:$value['s2']/$videoCount;}else{$percentage=$shareCount==0;}echosprintf("%.2f%%",$percentage*100);echo'</td>';echo'</tr>';}echo'</table>';echo'总计完成观看 '.$videoCount;echo'<p>查询完成, 操作时间 '.date("Y-m-d H:i:s",time()).'</p>';}else{echo'视频汇总记录为空!';}mysqli_free_result($result);mysqli_close($conn);