Monthly Archives: March 2014

webpage to statistic OTRS time

最近好无聊啊,被老板要求统计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>&nbsp;</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>&nbsp;</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>&nbsp;</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";
?>

mysqldump at replica master

一直以来,数据库备份就是我最头痛的问题,特别是从库完全挂掉的时候,以前总是人把所有的程序完全停止,现在好了,终算让我看到这个参数了

mysqldump --master-data=1 --all-databases > all-databases.sql

–lock-all-tables:在备份全过程中锁住所有的表,默认不启用。当启用时会自动关闭–single-transaction和–lock-tables参数。

–master-data:当值为0时,什么也不做。当值为1时,在SQL中自动加上CHANGE MASTER命令,当值为2时自动加上CHANGE MASTER命令,但是被注释的,导入时并不会自动执行。启用这个参数时如果没有启用–single-transaction,就会自动启用–lock-all-tables,当然了–lock-tables永远都是关闭的。

-x, --lock-all-tables
                      Locks all tables across all databases. This is achieved
                      by taking a global read lock for the duration of the
                      whole dump. Automatically turns --single-transaction and
                      --lock-tables off.
  --master-data[=#]   This causes the binary log position and filename to be
                      appended to the output. If equal to 1, will print it as a
                      CHANGE MASTER command; if equal to 2, that command will
                      be prefixed with a comment symbol. This option will turn
                      --lock-all-tables on, unless --single-transaction is
                      specified too (in which case a global read lock is only
                      taken a short time at the beginning of the dump; don't
                      forget to read about --single-transaction below). In all
                      cases, any action on logs will happen at the exact moment
                      of the dump. Option automatically turns --lock-tables
                      off.