search.aspx

Bug管理系统

<%@ Page language="C#"%>
<!--
Copyright 2002-2005 Corey Trager
Distributed under the terms of the GNU General Public License
-->
<!-- #include file = "inc.aspx" -->

<script language="C#" runat="server">

DbUtil dbutil;
Security security;
bool show_udf;

///////////////////////////////////////////////////////////////////////
string build_where(string where, string clause)
{
	if (clause == "")
	{
		return where;
	}

	string sql = "";

	if (where == "")
	{
		sql = " where ";
		sql += clause;
	}
	else
	{
		sql = where;
		sql += "and ";
		sql += clause;
	}

	return sql;
}


///////////////////////////////////////////////////////////////////////
string build_clause_from_listbox(ListBox lb, string column_name)
{

	string clause = "";
	foreach (ListItem li in lb.Items)
	{
		if (li.Selected)
		{
			if (clause == "")
			{
				clause += column_name + " in (";
			}
			else
			{
				clause += ",";
			}
			clause += li.Value;
		}
	}

	if (clause != "")
	{
		clause += ") ";
	}

	return clause;

}

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

	string select = @"select isnull(pr_background_color,'#ffffff') [color], 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]
		";

	if (show_udf)
	{
		string udf_name = Util.get_setting("UserDefinedBugAttributeName","YourAttribute");
		select += ", udf_name [" + udf_name + "]";
	}


	string custom_cols_sql = "";

	// determine custom fields
	DataSet ds_custom_cols = Util.get_custom_columns(dbutil);
	foreach (DataRow drcc in ds_custom_cols.Tables[0].Rows)
	{
		custom_cols_sql +=  ",[" + drcc["name"].ToString() + "]";
	}

	select += custom_cols_sql;

	select += @"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 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
		";

	if (show_udf)
	{
		select += "left outer join user_defined_attribute on udf_id = bg_user_defined_attribute";
	}


	string where = "";

	string reported_by_clause = build_clause_from_listbox (reported_by, "bg_reported_user");
	string assigned_to_clause = build_clause_from_listbox (assigned_to, "bg_assigned_to_user");
	string project_clause = build_clause_from_listbox (project, "bg_project");
	string category_clause = build_clause_from_listbox (category, "bg_category");
	string priority_clause = build_clause_from_listbox (priority, "bg_priority");
	string status_clause = build_clause_from_listbox (status, "bg_status");
	string udf_clause = "";

	if (show_udf)
	{
		udf_clause = build_clause_from_listbox(udf, "bg_user_defined_attribute");
	}


	// SQL "LIKE" uses [, %, and _ in a special way

	string like_string = like.Value.Replace("'", "''");
	like_string = like_string.Replace("[","[[]");
	like_string = like_string.Replace("%","[%]");
	like_string = like_string.Replace("_","[_]");

	string like2_string = like2.Value.Replace("'","''");
	like2_string = like2_string.Replace("[","[[]");
	like2_string = like2_string.Replace("%","[%]");
	like2_string = like2_string.Replace("_","[_]");

	string desc_clause = "";
	if (like.Value != "") {
		desc_clause = " bg_short_desc like";
		desc_clause += " '%" + like_string + "%'\n";
	}

	string comments_clause = "";
	if (like2.Value != "") {
		comments_clause = " bg_id in (select bc_bug from bug_comments where bc_comment like";
		comments_clause += " '%" + like2_string + "%')\n";
	}


	string comments_since_clause = "";
	if (comments_since.Value != "") {
		comments_since_clause = " bg_id in (select bc_bug from bug_comments where bc_type = 'comment' and bc_date > '";
		comments_since_clause += Util.format_local_date_into_db_format(comments_since.Value).Replace(" 12:00:00","");
		comments_since_clause += "')\n";
	}

	string from_clause = "";
	if (from_date.Value != "")
	{
		from_clause = " bg_reported_date >= '" + Util.format_local_date_into_db_format(from_date.Value).Replace(" 12:00:00","") + "'\n";
	}

	string to_clause = "";
	if (to_date.Value != "")
	{
		to_clause = " bg_reported_date <= '" + Util.format_local_date_into_db_format(to_date.Value).Replace(" 00:00:00","") + " 23:59:59'\n";
	}

	string lu_from_clause = "";
	if (lu_from_date.Value != "")
	{
		lu_from_clause = " bg_last_updated_date >= '" + Util.format_local_date_into_db_format(lu_from_date.Value).Replace(" 12:00:00","") + "'\n";
	}

	string lu_to_clause = "";
	if (lu_to_date.Value != "")
	{
		lu_to_clause = " bg_last_updated_date <= '" + Util.format_local_date_into_db_format(lu_to_date.Value).Replace(" 00:00:00","") + " 23:59:59'\n";
	}


	where = build_where(where, reported_by_clause);
	where = build_where(where, assigned_to_clause);
	where = build_where(where, project_clause);
	where = build_where(where, category_clause);
	where = build_where(where, priority_clause);
	where = build_where(where, status_clause);
	where = build_where(where, desc_clause);
	where = build_where(where, comments_clause);
	where = build_where(where, comments_since_clause);
	where = build_where(where, from_clause);
	where = build_where(where, to_clause);
	where = build_where(where, lu_from_clause);
	where = build_where(where, lu_to_clause);

	if (show_udf)
	{
		where = build_where(where, udf_clause);
	}

	sql = select + where + " order by bg_id desc";

	sql = Util.alter_sql_per_project_permissions(sql, security.this_usid);

	DataSet ds = dbutil.get_dataset(sql);
	dv = new DataView (ds.Tables[0]);
	Session["bugs"] = dv;
}

///////////////////////////////////////////////////////////////////////
void Page_Load(Object sender, EventArgs e)
{

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


	title.InnerText = Util.get_setting("AppTitle","BugTracker.NET") + " - "
		+ "search";

	show_udf = (Util.get_setting("ShowUserDefinedBugAttribute","1") == "1");

	if (!IsPostBack)
	{
		load_drop_downs();
	}
	else
	{
		if (hit_submit_button.Value == "1")
		{
			do_query();
		}
		else {
			dv = (DataView) Session["bugs"];
			if (dv == null)
			{
				do_query();
			}
			sort_dataview();
		}
	}

	hit_submit_button.Value = "0";

	if (Util.get_setting("HideSql", "0") == "1")
	{
		visible_sql_label.Style["display"] = "none";
		visible_sql_text.Style["display"] = "none";
	}

}

///////////////////////////////////////////////////////////////////////
void load_drop_downs()
{

	if (Util.get_setting("DefaultPermissionLevel","2") == "0")
	{
		// only show users who have explicit permission
		// for projects that this user has permissions for

		sql = @"select us_id, 
		case when $fullnames then us_lastname + ', ' + us_firstname else us_username end us_username 
		from users
		where us_id in
			(select pu_user from project_user_xref
			where pu_project in
				(select pu_project from project_user_xref
				where pu_user = $us
				and pu_permission_level <> 0
				)
			)
		order by us_username";
	}
	else
	{
		// show users UNLESS they have been explicitly excluded
		// from all the projects the viewer is able to view

		// the cartesian join in the first select is intentional

		sql=@"select  pj_id, us_id, 
		case when $fullnames then us_lastname + ', ' + us_firstname else us_username end us_username 
		into #temp
		from projects, users
		where pj_id not in
		(
			select pu_project from project_user_xref
			where pu_permission_level = 0 and pu_user = $us
		)
		select distinct us_id, us_username from #temp
		left outer join project_user_xref on pj_id = pu_project
		and us_id = pu_user
		where isnull(pu_permission_level,2) <> 0
		order by us_username
		drop table #temp";


	}

	if (Util.get_setting("UseFullNames","0") == "0")
	{
		// false condition
		sql = sql.Replace("$fullnames","0 = 1");
	}
	else
	{
		// true condition
		sql = sql.Replace("$fullnames","1 = 1");
	}
	sql = sql.Replace("$us",Convert.ToString(security.this_usid));

	reported_by.DataSource = dbutil.get_dataview(sql);
	reported_by.DataTextField = "us_username";
	reported_by.DataValueField = "us_id";
	reported_by.DataBind();


	// only show projects where user has permissions
	if (security.this_is_admin)
	{
		sql = "select pj_id, pj_name from projects order by pj_name";
	}
	else
	{
		sql = @"select pj_id, pj_name
			from projects
			left outer join project_user_xref on pj_id = pu_project
			and pu_user = $us
			where isnull(pu_permission_level,$dpl) <> 0
			order by pj_name";

		sql = sql.Replace("$us",Convert.ToString(security.this_usid));
		sql = sql.Replace("$dpl", Util.get_setting("DefaultPermissionLevel","2"));
	}

	project.DataSource = dbutil.get_dataview(sql);
	project.DataTextField = "pj_name";
	project.DataValueField = "pj_id";
	project.DataBind();
	project.Items.Insert(0, new ListItem("[no project]", "0"));


	category.DataSource =
		dbutil.get_dataview("select ct_id, ct_name from categories order by ct_sort_seq, ct_name");
	category.DataTextField = "ct_name";
	category.DataValueField = "ct_id";
	category.DataBind();
	category.Items.Insert(0, new ListItem("[no category]", "0"));

	priority.DataSource =
		dbutil.get_dataview("select pr_id, pr_name from priorities order by pr_sort_seq, pr_name");
	priority.DataTextField = "pr_name";
	priority.DataValueField = "pr_id";
	priority.DataBind();
	priority.Items.Insert(0, new ListItem("[no priority]", "0"));

	status.DataSource =
		dbutil.get_dataview("select st_id, st_name from statuses order by st_sort_seq, st_name");
	status.DataTextField = "st_name";
	status.DataValueField = "st_id";
	status.DataBind();
	status.Items.Insert(0, new ListItem("[no status]", "0"));

	assigned_to.DataSource = reported_by.DataSource;
	assigned_to.DataTextField = "us_username";
	assigned_to.DataValueField = "us_id";
	assigned_to.DataBind();
	assigned_to.Items.Insert(0, new ListItem("[not assigned]", "0"));

	if (show_udf)
	{
		udf.DataSource =
			dbutil.get_dataview("select udf_id, udf_name from user_defined_attribute order by udf_sort_seq, udf_name");
		udf.DataTextField = "udf_name";
		udf.DataValueField = "udf_id";
		udf.DataBind();
		udf.Items.Insert(0, new ListItem("[none]", "0"));
	}





}

void Page_Unload(Object sender, EventArgs e)
{
	if (dbutil != null) {dbutil.close();}
}


</script>
<!-- #include file = "inc_bugs.aspx" -->

<html>
<head>
<title id="title" runat="server">btnet search</title>
<link rel="StyleSheet" href="btnet.css" type="text/css">
<script type="text/javascript" language="JavaScript" src="overlib_mini.js"></script>
<script type="text/javascript" language="JavaScript" src="calendar.js"></script>


<script>

<% if (security.this_is_admin) { %>

function select_all(sel)
{
	var frm = document.getElementById("massform");
	for (var i = 0; i < frm.elements.length; i++)
	{
		var varname = frm.elements[i].name;
		if (!isNaN(parseInt(varname)))
		{
			frm.elements[i].checked = sel;
		}
	}
}

function validate_mass()
{

	var at_least_one_selected = false;

	// make sure at least one item is selected	
	var frm = document.getElementById("massform");
	for (var i = 0; i < frm.elements.length; i++)
	{
		var varname = frm.elements[i].name;
		if (!isNaN(parseInt(varname)))
		{
			var checkbox = frm.elements[i];
			if (checkbox.checked == true)
			{
				at_least_one_selected = true;
				break;
			}
		}
	}
	
	if (!at_least_one_selected)
	{
		alert ("No items selected for mass update/delete.");
		return false;
	}
	
	if (frm.mass_project.selectedIndex==0
	&& frm.mass_category.selectedIndex==0
	&& frm.mass_priority.selectedIndex==0
	&& frm.mass_assigned_to.selectedIndex==0
	&& frm.mass_status.selectedIndex==0)
	{
		if (!frm.mass_delete.checked)	
		{
			alert ("No updates were specified and delete wasn't checked.  Please specify updates or delete.");
			return false;
		}
	}
	else
	{
		if (frm.mass_delete.checked)	
		{
			alert ("Both updates and delete were specified.   Please select one or the other.");
			return false;
		}
	}

	return true;
}

function load_one_massedit_select(from_id, to_id)