webpage to statistic OTRS time
March 27th, 2014 Posted in Linux, PHP & PW最近好无聊啊，被老板要求统计OTRS里面所用的时间，OTRS本身的PDF报靠可读性不是很友好，而且，这个时间统计本身是不支持的，就开始自己动手写一个了。。。
由于SQL比较长，我就先做了几个视图。。。
time_stats视图（所有时间）：
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`)) group by `u`.`id`,`t`.`type_id`
time_stats_3mons视图（最近三个月内）：
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_3mons` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (`ta`.`create_time` between (now() - interval 3 month) and now())) group by `u`.`id`,`t`.`type_id`
time_stats_6mons视图(最近6个月内）:
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_6mons` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (`ta`.`create_time` between (now() - interval 6 month) and now())) group by `u`.`id`,`t`.`type_id`
time_stats_lastmon视图(上个月)：
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_lastmon` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (date_format(`ta`.`create_time`,'%y-%m') = date_format((curdate() - interval 1 month),'%y-%m'))) group by `u`.`id`,`t`.`type_id`
time_stats_lastweek视图(上周)：
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_lastweek` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (yearweek(`ta`.`create_time`,0) = (yearweek(now(),0) - 1))) group by `u`.`id`,`t`.`type_id`
time_stats_thismon视图(本月)：
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_thismon` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (date_format(`ta`.`create_time`,'%y-%m') = date_format(now(),'%y-%m'))) group by `u`.`id`,`t`.`type_id`
time_stats_thisweek视图（本周）:
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_thismon` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (date_format(`ta`.`create_time`,'%y-%m') = date_format(now(),'%y-%m'))) group by `u`.`id`,`t`.`type_id`
然后就是php代码。。。。
<?php
$remote_ip = $_SERVER["REMOTE_ADDR"];
$page = $_SERVER["SCRIPT_NAME"];
$ips=array(
'10.0.0.1', //Sample King IP
'10.0.0.2', //Brutex IP
'10.0.0.3',
);
if(!in_array($remote_ip,$ips)){
die("<h1>Permession Denied!</h1>");
}
?>
<?php
echo "<!DOCTYPE html>\n";
echo "<html>\n";
echo "<head>\n";
echo "<title>OTRS Time Stats</title>\n";
echo "<style>\ntable,th,td\n{\n\tborder:1px solid black;\n\tborder-collapse:collapse\n}\n</style>\n";
echo "</head>\n";
echo "<body>\n";
?>
<?php
$times = array(
"time_stats_thisweek" => "This week",
"time_stats_lastweek" => "Last week",
"time_stats_thismon" => "This month",
"time_stats_lastmon" => "Last month",
"time_stats_3mons" => "Latest 3 months",
"time_stats_6mons" => "Latest 6 months",
"time_stats" => "All time",
);
$timeperiod = $_POST['timeperiod'];
if(!array_key_exists($timeperiod,$times)){
echo "<h2>Select a time period you want to query:</h2>\n";
echo "<form action=\"$page\" method=\"post\">\n";
echo "<select name=\"timeperiod\">\n";
foreach($times as $key => $val){
echo "\t\t<option value=\"$key\">".$val."</option>\n";
}
echo "\t</select>\n";
echo "<input type=submit>\n</form>";
}else{
?>
<?php
$tablename = $timeperiod;
$conn = @mysql_connect("localhost","readonly","readonly") or die("Cannot connect to DB");
mysql_select_db("otrs",$conn);
$user_list = mysql_query("select distinct name from $tablename order by uid",$conn);
while($row = mysql_fetch_array($user_list)){
//$newuids[] = $row['uid'];
$newusers[] = $row['name'];
}
$type_list = mysql_query("select distinct type,tid from $tablename order by tid",$conn);
while($row = mysql_fetch_array($type_list)){
$newtids[] = $row['tid'];
$newtypes[] = $row['type'];
}
echo "<h3>Time Statistics for <em><font color=red>".$times["$tablename"]."</font></em></h3>\n";
echo "<table border=1>\n\t<tr><td> </td>";
foreach($newtypes as $val){
echo "<td>".$val."</td>";
}
echo "<td><b>Sum</b></td></tr>\n";
foreach($newusers as $val){
echo "\t<tr><td>$val</td>";
$timesum = 0;
foreach($newtids as $val2){
$time_res = mysql_query("select minutes from $tablename where tid=$val2 and name='$val'",$conn);
#$time_res = mysql_query("select round(minutes/60,1) as minutes from $tablename where tid=$val2 and name='$val'",$conn);
if(!time_res){
echo "<td> </td>";
}else{
$time = mysql_result($time_res,0);
echo "<td>".round($time/60,1)."</td>";
$timesum = $timesum+$time;
}
}
echo "<td>".round($timesum/60,1)."</td></tr>\n";
}
echo "\t<tr><td><b>Total</b></td>";
foreach($newtids as $val){
$time_res = mysql_query("select round(sum(minutes)/60,1) as minutes from $tablename where tid=$val",$conn);
if(!time_res){
echo "<td> </td>";
}else{
$time = mysql_result($time_res,0);
echo "<td>".$time."</td>";
}
}
$timesum_res = mysql_query("select round(sum(minutes)/60,1) as minutes from $tablename",$conn);
$timesum = mysql_result($timesum_res,0);
echo "<td>".$timesum."</td></tr>\n";
echo "</table>\n";
echo "<br>\n";
echo "<div><a href=\"".$page."\">Back</a></div>\n";
mysql_close($conn);
?>
<?php
}
?>
<?php
echo "</body>\n";
echo "</html>\n";
?>
One Response to “webpage to statistic OTRS time”
By xuyi on Apr 17, 2014
终于我也能看懂php代码了。