#15 summary reports with hyperlinks -- CODE ATTACHED

open
nobody
None
5
2009-11-05
2006-05-01
Anonymous
No

I added a new type of query to the main page that
displays a chart of bugs, for example where the rows
are projects and columns are status. Each cell
displays the number of bugs, and these numbers are all
links to queries. For example, if the row "Project1"
for column "fixed" shows the number 15, clicking on
the 15 brings up a page showing the 15 Project1 fixed
bugs.

USAGE: Select from drop list on main page from the
following:
"All bugs by engineer"
"All bugs by priority"
"All bugs by status"

CUSTOMIZATIONS:
I have added a field on the users table
called 'us_dev_perm' to indicate whether a user is a
developer or not.
The quickest way to get this working with any
BugTracker implementation is to change 'us_dev_perm'
to 'us_admin' in both bugs.aspx and search_report.aspx.

Or modify the ShowAllByDeveloper function in bugs.aspx
to work with your implementation, or delete the
function (you will still need to modify
search_report.aspx if you delete the
ShowAllByDeveloper function)

Discussion

  • alanbelanger

    alanbelanger - 2006-05-01

    Logged In: YES
    user_id=1514309

    Oops, forgot to log on when posting. Contact me with
    questions/comments!
    alan.belanger@comcast.net

     
  • alanbelanger

    alanbelanger - 2006-05-01

    Logged In: YES
    user_id=1514309

    ////////////////////////////////////////////////////////////
    ///////////
    void ShowAllByStatus()
    {
    Util.do_not_cache(Response);
    dbutil = new DbUtil();
    security = new Security();
    security.check_security(dbutil, Request, Response,
    Security.ANY_USER_OK);

    Response.Write ("<div class=align><table border=1
    bgcolor=white>");
    Response.Write ("<tr><td><b>Project</b></td>");

    sql = @"select st\_name from statuses order by
    

    st_sort_seq";
    DataSet ds = dbutil.get_dataset (sql);
    foreach (DataRow dr in ds.Tables[0].Rows){
    Response.Write ("<td><b>" + Convert.ToString
    (dr["st_name"]) + "</b></td>");
    }
    Response.Write ("</tr>");

    //get count of all sorted by project, then status
    sql = @"select pj\_name, st\_name \[status\] ,count
    

    (bg_status) [count]
    from projects
    CROSS JOIN statuses
    LEFT OUTER JOIN bugs
    on bg_project = pj_id and
    bg_status = st_id
    where st_name is not null
    and pj_name is not null
    group by pj_name, st_name,
    st_sort_seq
    order by pj_name,
    st_sort_seq";
    ds = dbutil.get_dataset (sql);
    String szProj = "";
    int nFirst = 1;
    foreach (DataRow dr in ds.Tables[0].Rows){
    if (szProj != Convert.ToString(dr
    ["pj_name"]))
    {
    if (nFirst == 0)
    {
    Response.Write ("</tr>");
    }
    szProj = Convert.ToString(dr
    ["pj_name"]);
    Response.Write ("<tr><td>" + szProj
    + "</td>");
    nFirst = 0;
    }
    String szStatusString = Convert.ToString(dr
    ["status"]);
    Response.Write ("<td><a target=blank
    href=search_report.aspx?stat=" + szStatusString.Replace
    (' ','+') + "&proj=" + szProj.Replace(' ','+') + ">" +
    Convert.ToString(dr["count"]) + "</a></td>");
    }
    Response.Write ("</tr><tr><td
    bgcolor='#FFCC88'>Total</td>");

    sql = @"select st\_name \[status\], count\(bg\_status\)
    

    [count]
    from statuses
    FULL OUTER JOIN bugs
    on st_id = bg_status
    where st_name is not null
    group by st_name, st_sort_seq order by
    st_sort_seq";
    DataSet dsTotals = dbutil.get_dataset (sql);
    foreach (DataRow drTotals in dsTotals.Tables
    [0].Rows){
    int nCount = Convert.ToInt32
    (drTotals["count"]);
    Response.Write("<td
    bgcolor='#FFCC88'>" + Convert.ToString(nCount) + "</td>");
    }
    Response.Write ("</table></div>");
    }

     
  • alanbelanger

    alanbelanger - 2006-05-01

    Logged In: YES
    user_id=1514309

    ////////////////////////////////////////////////////////////
    ///////////
    void do_query()
    {
    prev_sort.Value = "-1";
    prev_dir.Value = "ASC";
    new_page.Value = "0";
    ArrayList arrPrior = new ArrayList();
    ArrayList arrEngine = new ArrayList();

    string var = Request.QueryString\["stat"\];
    if \(var == null\)
    \{
        szType = "";
    \}
    else
    \{
        szType = var.Replace\('+',' '\);
    \}
    
    string var2 = Request.QueryString\["proj"\];
    if \(var2 == null\)
    \{
        szProj = "";
    \}
    else
    \{
        szProj = var2.Replace\('+',' '\);
    \}
    
    sql = @"select pr\_name from dbo.priorities";
    DataSet ds = dbutil.get\_dataset\(sql\);
    foreach \(DataRow dr in ds.Tables\[0\].Rows\)\{
        arrPrior.Add\(Convert.ToString\(dr
    

    ["pr_name"]));
    }

    sql = @"select us\_username from users where
    

    us_dev_perm = 1";
    ds = dbutil.get_dataset(sql);
    foreach (DataRow dr in ds.Tables[0].Rows){
    arrEngine.Add(Convert.ToString(dr
    ["us_username"]));
    }

    if \(arrPrior.Contains\(szType\) == true\)
    \{
        sql = @"select isnull
    

    (pr_background_color,'#ffffff'), bg_id [id], bg_short_desc
    [desc], pj_name [project], ct_name [category],
    rpt.us_username [reported by], bg_reported_date [reported
    on], pr_name [priority], asg.us_username [assigned to],
    st_name [status], lu.us_username [last updated by],
    bg_last_updated_date [last updated on] from bugs left outer
    join users rpt on rpt.us_id = bg_reported_user left outer
    join users asg on asg.us_id = bg_assigned_to_user left
    outer join users lu on lu.us_id = bg_last_updated_user left
    outer join projects on pj_id = bg_project left outer join
    categories on ct_id = bg_category left outer join
    priorities on pr_id = bg_priority left outer join statuses
    on st_id = bg_status where (bg_status <> (select st_id from
    statuses where st_name = 'as designed') and bg_status <>
    (select st_id from statuses where st_name = 'closed') and
    bg_status <> (select st_id from statuses where st_name
    = 'duplicate') and bg_status <> (select st_id from statuses
    where st_name = 'suspended')) and bg_project = (select
    pj_id from projects where pj_name = '" + szProj.Replace
    ('+', ' ') + "') and bg_priority = (select pr_id from
    priorities where pr_name = '" + szType.Replace('+', ' ')
    + "') order by bg_id desc";
    }
    else if (arrEngine.Contains(szProj) == true)
    {
    sql = @"select isnull
    (pr_background_color,'#ffffff'), bg_id [id], bg_short_desc
    [desc], pj_name [project], ct_name [category],
    rpt.us_username [reported by], bg_reported_date [reported
    on], pr_name [priority], asg.us_username [assigned to],
    st_name [status], lu.us_username [last updated by],
    bg_last_updated_date [last updated on] from bugs left outer
    join users rpt on rpt.us_id = bg_reported_user left outer
    join users asg on asg.us_id = bg_assigned_to_user left
    outer join users lu on lu.us_id = bg_last_updated_user left
    outer join projects on pj_id = bg_project left outer join
    categories on ct_id = bg_category left outer join
    priorities on pr_id = bg_priority left outer join statuses
    on st_id = bg_status where bg_status = (select st_id from
    statuses where st_name = '" + szType.Replace('+', ' ')
    + "') and bg_assigned_to_user = (select us_id from users
    where us_username = '" + szProj.Replace('+', ' ') + "')
    order by bg_id desc";
    }
    else
    {
    sql = @"select isnull
    (pr_background_color,'#ffffff'), bg_id [id], bg_short_desc
    [desc], pj_name [project], ct_name [category],
    rpt.us_username [reported by], bg_reported_date [reported
    on], pr_name [priority], asg.us_username [assigned to],
    st_name [status], lu.us_username [last updated by],
    bg_last_updated_date [last updated on] from bugs left outer
    join users rpt on rpt.us_id = bg_reported_user left outer
    join users asg on asg.us_id = bg_assigned_to_user left
    outer join users lu on lu.us_id = bg_last_updated_user left
    outer join projects on pj_id = bg_project left outer join
    categories on ct_id = bg_category left outer join
    priorities on pr_id = bg_priority left outer join statuses
    on st_id = bg_status where bg_status = (select st_id from
    statuses where st_name = '" + szType.Replace('+', ' ')
    + "') and bg_project = (select pj_id from projects where
    pj_name = '" + szProj.Replace('+', ' ') + "') order by
    bg_id desc";
    }
    ds = dbutil.get_dataset(sql);
    dv = new DataView (ds.Tables[0]);
    Session["bugs"] = dv;
    bug_count.Text = Convert.ToString(dv.Count);

    }

     
  • Corey Trager

    Corey Trager - 2006-10-05
    • assigned_to: nobody --> ctrager
     
  • Corey Trager

    Corey Trager - 2008-10-25
    • priority: 5 --> 2
     
  • Corey Trager

    Corey Trager - 2009-11-05
    • assigned_to: ctrager --> nobody
     
  • Corey Trager

    Corey Trager - 2009-11-05
    • priority: 2 --> 5