webpage to statistic OTRS time
March 27th, 2014 Posted in Linux, PHP & PW最近好无聊啊，被老板要求统计OTRS里面所用的时间，OTRS本身的PDF报靠可读性不是很友好，而且，这个时间统计本身是不支持的，就开始自己动手写一个了。。。
由于SQL比较长，我就先做了几个视图。。。
time_stats视图（所有时间）：
|
1 |
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视图（最近三个月内）：
|
1 |
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个月内）:
|
1 |
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视图(上个月)：
|
1 |
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视图(上周)：
|
1 |
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视图(本月)：
|
1 |
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视图（本周）:
|
1 |
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代码。。。。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 |
<?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代码了。