Can good old timesheets be generated from bugzilla database? - Mozilla

This is a discussion on Can good old timesheets be generated from bugzilla database? - Mozilla ; Each programmer in the end of each day reports his "hours worked" in BZ. I want in the end of each month check these for mistakes. Thanks in advance for help!...

+ Reply to Thread
Results 1 to 6 of 6

Thread: Can good old timesheets be generated from bugzilla database?

  1. Can good old timesheets be generated from bugzilla database?

    Each programmer in the end of each day reports his "hours worked" in BZ.
    I want in the end of each month check these for mistakes.

    Thanks in advance for help!

  2. RE: Can good old timesheets be generated from bugzilla database?

    Quoting Eugene Korolev:
    > Each programmer in the end of each day reports his "hours worked" in
    > BZ.
    > I want in the end of each month check these for mistakes.


    Outside of the normal Bugzilla access, but:
    SELECT p.login_name, sum(l.work_time)
    FROM profiles AS p
    LEFT OUTER JOIN longdescs AS l
    ON p.user_id = l.who
    GROUP BY p.user_id
    WHERE l.bug_when >= 'YYYY-M1-01 00:00:00'
    AND l.bug_when < 'YYYY-M2-01 00:00:00';

  3. RE: Can good old timesheets be generated from bugzilla database?

    Quoting Tosh, Michael J:
    > Quoting Eugene Korolev:
    > > Each programmer in the end of each day reports his "hours worked"
    > > in BZ. I want in the end of each month check these for mistakes.


    Ignore my last email, couple errors in it...

    SELECT p.login_name, sum(l.work_time)
    FROM profiles AS p
    LEFT OUTER JOIN longdescs AS l
    ON p.userid = l.who
    WHERE l.bug_when >= '2008-04-01 00:00:00'
    AND l.bug_when < '2008-05-01 00:00:00'
    GROUP BY p.userid;

    Using some clever joins, you could essentially do a week-by-week report
    too. See below. Obviously the weeks could be better defined, and MySQL
    has some interesting date functions to extract weeks. You could map
    week1.bug_when to the first week of the given month, ... I just don't
    have time to do it for you. :-)

    SELECT p.login_name,
    sum(week1.work_time) AS week1,
    sum(week2.work_time) AS week2,
    sum(week3.work_time) AS week3,
    sum(week4.work_time) AS week4,
    sum(week5.work_time) AS week5
    FROM profiles AS p

    LEFT OUTER JOIN longdescs AS week1
    ON p.userid = week1.who
    LEFT OUTER JOIN longdescs AS week2
    ON p.userid = week2.who
    LEFT OUTER JOIN longdescs AS week3
    ON p.userid = week3.who
    LEFT OUTER JOIN longdescs AS week4
    ON p.userid = week4.who
    LEFT OUTER JOIN longdescs AS week5
    ON p.userid = week5.who

    WHERE week1.bug_when >= '2008-04-01 00:00:00' AND week1.bug_when <
    '2008-04-08 00:00:00'
    AND week2.bug_when >= '2008-04-08 00:00:00' AND week2.bug_when <
    '2008-04-15 00:00:00'
    AND week3.bug_when >= '2008-04-15 00:00:00' AND week3.bug_when <
    '2008-04-22 00:00:00'
    AND week4.bug_when >= '2008-04-22 00:00:00' AND week4.bug_when <
    '2008-04-29 00:00:00'
    AND week5.bug_when >= '2008-04-29 00:00:00' AND week5.bug_when <
    '2008-05-01 00:00:00'
    GROUP BY p.userid;

  4. Re: Can good old timesheets be generated from bugzilla database?

    Michael, thanks a lot for your info.

    What I am looking for is a kind of small extension to Bugzilla,
    which allows to see the hours worked in a timesheet format:
    who, date, hours, bug summary, bug comment.

    I wonder if somebody already made such an extension.

    Tosh, Michael J wrote:

    > Quoting Tosh, Michael J:


    >> Quoting Eugene Korolev:


    >>> Each programmer in the end of each day reports his "hours worked"
    >>> in BZ. I want in the end of each month check these for mistakes.

    >
    > SELECT p.login_name, sum(l.work_time)
    > FROM profiles AS p
    > LEFT OUTER JOIN longdescs AS l
    > ON p.userid = l.who
    > WHERE l.bug_when >= '2008-04-01 00:00:00'
    > AND l.bug_when < '2008-05-01 00:00:00'
    > GROUP BY p.userid;


  5. Re: Can good old timesheets be generated from bugzilla database?

    Hello,

    I developed a little application that generates a hours-worked oriented
    report, that may fulfil most of your requirements. You can find a
    screenshot here, among others:

    http://albums.jujunie.com/v/software...esult.png.html

    This screenshot represents a daily view with the maximum of details. You
    can choose other grouping periods (Weekly, Monthly...) and reduce the
    number of axis (X and Y) allowing you to have a consolidated view.

    The documentation of the current release:

    http://www.jujunie.com/documentation...egration-main/

    The "jujunie-integration" project is registered on Freshmeat.

    Unfortunatly, this is not a "little extension", but a J2EE application
    that needs to be deployed on a Web Container such Apache Tomcat.

    Regards,

    Julien BETI.




    -------- Original Message --------
    Subject: Re: Can good old timesheets be generated from bugzilla database?
    From: Eugene Korolev
    To: support-bugzilla@lists.mozilla.org
    Date: Wed Apr 30 2008 08:51:20 GMT+0200 (CEST)

    > Michael, thanks a lot for your info.
    >
    > What I am looking for is a kind of small extension to Bugzilla,
    > which allows to see the hours worked in a timesheet format:
    > who, date, hours, bug summary, bug comment.
    >
    > I wonder if somebody already made such an extension.
    >
    > Tosh, Michael J wrote:
    >
    >> Quoting Tosh, Michael J:

    >
    >>> Quoting Eugene Korolev:

    >
    >>>> Each programmer in the end of each day reports his "hours worked"
    >>>> in BZ. I want in the end of each month check these for mistakes.

    >> SELECT p.login_name, sum(l.work_time)
    >> FROM profiles AS p
    >> LEFT OUTER JOIN longdescs AS l
    >> ON p.userid = l.who
    >> WHERE l.bug_when >= '2008-04-01 00:00:00'
    >> AND l.bug_when < '2008-05-01 00:00:00'
    >> GROUP BY p.userid;

    > _______________________________________________
    > support-bugzilla mailing list
    > support-bugzilla@lists.mozilla.org
    > https://lists.mozilla.org/listinfo/support-bugzilla
    > PLEASE put support-bugzilla@lists.mozilla.org in the To: field when you reply.
    >


  6. Re: Can good old timesheets be generated from bugzilla database?

    On Wed, 30 Apr 2008, Julien BETI wrote:

    > Hello,
    >
    > I developed a little application that generates a hours-worked oriented
    > report, that may fulfil most of your requirements. You can find a
    > screenshot here, among others:
    >
    > http://albums.jujunie.com/v/software...esult.png.html
    >
    > This screenshot represents a daily view with the maximum of details. You
    > can choose other grouping periods (Weekly, Monthly...) and reduce the
    > number of axis (X and Y) allowing you to have a consolidated view.
    >
    > The documentation of the current release:
    >
    > http://www.jujunie.com/documentation...egration-main/
    >
    > The "jujunie-integration" project is registered on Freshmeat.
    >
    > Unfortunatly, this is not a "little extension", but a J2EE application
    > that needs to be deployed on a Web Container such Apache Tomcat.
    >
    > Regards,
    >
    > Julien BETI.
    >


    Here's a shell script we've been working on...

    This REQUIRES, that you update the status whiteboard/latest field at the
    same time you enter the time. It works with 2.20 and 3.0.2.

    START_DATE=`date -d "Last Sunday" "+%F"`
    END_DATE=`date -d "Friday" "+%F"`
    BUGUSER=
    mysql -u --password= << EOLSQL
    use ;
    SELECT
    CONCAT(t5.rep_platform,'|',t1.bug_id,'|',t5.short_ desc,'|',t3.added,'|',t2.added,'|',t2.bug_when)
    FROM longdescs t1, bugs_activity t2, bugs_activity t3, profiles t4, bugs
    t5
    WHERE
    t1.bug_id = t2.bug_id
    and t1.bug_id = t3.bug_id
    and t1.bug_id = t5.bug_id
    and t2.who = t4.userid
    and t1.bug_id = t3.bug_id
    and t1.work_time > 0
    and t2.bug_when >= "$START_DATE 00:00:00"
    and t2.bug_when <= "$END_DATE 23:59:59"
    and t3.bug_when >= "$START_DATE 00:00:00"
    and t3.bug_when <= "$END_DATE 23:59:59"
    and t2.bug_id = t3.bug_id
    and t2.bug_when = t3.bug_when
    and t2.who = t3.who
    and t4.login_name = "$BUGUSER"

    and then some perl to format the output. We'll eventually integrate it
    with the "Summarize Time" button.

    ------------------------------------------------------------------------
    Jim Wildman, CISSP, RHCE jim@rossberry.com http://www.rossberry.com
    "Society in every state is a blessing, but Government, even in its best
    state, is a necessary evil; in its worst state, an intolerable one."
    Thomas Paine

+ Reply to Thread