///////////////////////////////////////////////////////////////////////
public static DataSet get_bug_posts(int bugid, bool external_user, bool history_inline)
{
SQLString sql = new SQLString(@"
/* get_bug_posts */
select
a.bp_bug,
a.bp_comment,
isnull(us_username,'') [us_username],
case rtrim(us_firstname)
when null then isnull(us_lastname, '')
when '' then isnull(us_lastname, '')
else isnull(us_lastname + ', ' + us_firstname,'')
end [us_fullname],
isnull(us_email,'') [us_email],
a.bp_date,
datediff(s,a.bp_date,getdate()) [seconds_ago],
a.bp_id,
a.bp_type,
isnull(a.bp_email_from,'') bp_email_from,
isnull(a.bp_email_to,'') bp_email_to,
isnull(a.bp_email_cc,'') bp_email_cc,
isnull(a.bp_file,'') bp_file,
isnull(a.bp_size,0) bp_size,
isnull(a.bp_content_type,'') bp_content_type,
a.bp_hidden_from_external_users,
isnull(ba.bp_file,'') ba_file, -- intentionally ba
isnull(ba.bp_id,'') ba_id, -- intentionally ba
isnull(ba.bp_size,'') ba_size, -- intentionally ba
isnull(ba.bp_content_type,'') ba_content_type -- intentionally ba
from bug_posts a
left outer join users on us_id = a.bp_user
left outer join bug_posts ba on ba.bp_parent = a.bp_id and ba.bp_bug = a.bp_bug
where a.bp_bug = @id
and a.bp_parent is null");
if (!history_inline)
{
sql.Append("\n and a.bp_type <> 'update'");
}
if (external_user)
{
sql.Append("\n and a.bp_hidden_from_external_users = 0");
}
sql.Append( "\n order by a.bp_id ");
sql.Append( btnet.Util.get_setting("CommentSortOrder","desc"));
sql.Append( ", ba.bp_parent, ba.bp_id");
sql = sql.AddParameterWithValue("@id", Convert.ToString(bugid));
return btnet.DbUtil.get_dataset(sql);
}
private void ApplyWhereClause(SQLString sqlString, BugQueryFilter[] filters)
{
if (filters != null && filters.Any())
{
sqlString.Append(" WHERE ");
List <string> conditions = new List <string>();
foreach (var filter in filters)
{
if (!_columnNames.Contains(filter.Column))
{
throw new ArgumentException("Invalid filter column: {0}", filter.Column);
}
string parameterName = GetCleanParameterName(filter.Column);
conditions.Add(string.Format("[{0}] = @{1}", filter.Column, parameterName));
}
sqlString.Append(string.Join(" AND ", conditions));
}
}
///////////////////////////////////////////////////////////////////////
public static int insert_comment(
int bugid,
int this_usid,
string comment_formated,
string comment_search,
string from,
string cc,
string content_type,
bool internal_only)
{
if (comment_formated != "")
{
var sql = new SQLString(@"
declare @now datetime
set @now = getdate()
insert into bug_posts
(bp_bug, bp_user, bp_date, bp_comment, bp_comment_search, bp_email_from, bp_email_cc, bp_type, bp_content_type,
bp_hidden_from_external_users)
values(
@id,
@us,
@now,
@comment_formatted,
@comment_search,
@from,
@cc,
@type,
@content_type,
@internal)
select scope_identity();");
if (from != null)
{
// Update the bugs timestamp here.
// We don't do it unconditionally because it would mess up the locking.
// The edit_bug.aspx page gets its snapshot timestamp from the update of the bug
// row, not the comment row, so updating the bug again would confuse it.
sql.Append(@"update bugs
set bg_last_updated_date = @now,
bg_last_updated_user = @us
where bg_id = @id");
sql = sql.AddParameterWithValue("@from", from);
sql = sql.AddParameterWithValue("@type", "received"); // received email
}
else
{
sql = sql.AddParameterWithValue("@from", null);
sql = sql.AddParameterWithValue("@type", "comment"); // bug comment
}
sql = sql.AddParameterWithValue("@id", Convert.ToString(bugid));
sql = sql.AddParameterWithValue("@us", Convert.ToString(this_usid));
sql = sql.AddParameterWithValue("@comment_formatted", comment_formated);
sql = sql.AddParameterWithValue("@comment_search", comment_search);
sql = sql.AddParameterWithValue("@content_type", content_type);
if (cc == null)
{
cc = "";
}
sql = sql.AddParameterWithValue("@cc", cc);
sql = sql.AddParameterWithValue("@internal", btnet.Util.bool_to_string(internal_only));
return Convert.ToInt32(btnet.DbUtil.execute_scalar(sql));
}
else
{
return 0;
}
}
///////////////////////////////////////////////////////////////////////
void load_dropdowns_for_insert()
{
load_dropdowns();
// Get the defaults
sql = new SQLString("\nselect top 1 pj_id from projects where pj_default = 1 order by pj_name;"); // 0
sql.Append("\nselect top 1 ct_id from categories where ct_default = 1 order by ct_name;"); // 1
sql.Append("\nselect top 1 pr_id from priorities where pr_default = 1 order by pr_name;"); // 2
sql.Append("\nselect top 1 st_id from statuses where st_default = 1 order by st_name;"); // 3
sql.Append("\nselect top 1 udf_id from user_defined_attribute where udf_default = 1 order by udf_name;"); // 4
DataSet ds_defaults = DbUtil.get_dataset(sql);
load_project_and_user_dropdown_for_insert(ds_defaults.Tables[0]);
load_other_dropdowns_and_select_defaults(ds_defaults);
}
public BugQueryResult ExecuteQuery(IIdentity identity, int start, int length, string orderBy, string sortDirection, bool idOnly, BugQueryFilter[] filters = null)
{
if (!string.IsNullOrEmpty(orderBy) && !_columnNames.Contains(orderBy))
{
throw new ArgumentException("Invalid order by column specified: {0}", orderBy);
}
bool hasFilters = filters != null && filters.Any();
string columnsToSelect = idOnly ? "id" : "*";
var innerSql = GetInnerSql(identity);
var countSql = string.Format("SELECT COUNT(1) FROM ({0}) t", GetInnerSql(identity));
SQLString sqlString = new SQLString(countSql);
sqlString.Append(";");
if (hasFilters)
{
sqlString.Append(countSql);
ApplyWhereClause(sqlString, filters);
sqlString.Append(";");
}
var bugsSql = string.Format("SELECT t.{0} FROM ({1}) t",columnsToSelect, innerSql);
sqlString.Append(bugsSql);
sqlString.Append(" WHERE id IN (");
var innerBugsSql = string.Format("SELECT t.id FROM ({0}) t", innerSql);
sqlString.Append(innerBugsSql);
ApplyWhereClause(sqlString, filters);
if (hasFilters)
{
foreach (var filter in filters)
{
sqlString.AddParameterWithValue(GetCleanParameterName(filter.Column), filter.Value);
}
}
sqlString.Append(" ORDER BY ");
sqlString.Append(BuildDynamicOrderByClause(orderBy, sortDirection));
sqlString.Append(" OFFSET @offset ROWS FETCH NEXT @page_size ROWS ONLY)");
int userId = identity.GetUserId();
sqlString.AddParameterWithValue("@ME", userId);
sqlString.AddParameterWithValue("page_size", length > 0 ? length : MaxLength);
sqlString.AddParameterWithValue("offset", start);
DataSet dataSet = DbUtil.get_dataset(sqlString);
var countUnfiltered = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
var countFiltered = hasFilters ? Convert.ToInt32(dataSet.Tables[1].Rows[0][0]) : countUnfiltered;
var bugDataTableIndex = hasFilters ? 2 : 1;
return new BugQueryResult
{
CountUnfiltered = countUnfiltered,
CountFiltered = countFiltered,
Data = dataSet.Tables[bugDataTableIndex]
};
}
///////////////////////////////////////////////////////////////////////
public static NewIds insert_bug(string short_desc, IIdentity identity, string tags, int projectid, int orgid, int categoryid, int priorityid, int statusid, int assigned_to_userid, int udfid, string comment_formated, string comment_search, string @from, string cc, string content_type, bool internal_only, SortedDictionary <string, string> hash_custom_cols, bool send_notifications)
{
if (short_desc.Trim() == "")
{
short_desc = "[No Description]";
}
if (assigned_to_userid == 0)
{
assigned_to_userid = btnet.Util.get_default_user(projectid);
}
var sql = new SQLString(@"insert into bugs
(bg_short_desc,
bg_tags,
bg_reported_user,
bg_last_updated_user,
bg_reported_date,
bg_last_updated_date,
bg_project,
bg_org,
bg_category,
bg_priority,
bg_status,
bg_assigned_to_user,
bg_user_defined_attribute)
values (@short_desc, @tags, @reported_user, @reported_user, getdate(), getdate(),
@project, @org,
@category, @priority, @status, @assigned_user, @udf)");
sql = sql.AddParameterWithValue("@short_desc", short_desc);
sql = sql.AddParameterWithValue("@tags", tags);
sql = sql.AddParameterWithValue("@reported_user", Convert.ToString(identity.GetUserId()));
sql = sql.AddParameterWithValue("@project", Convert.ToString(projectid));
sql = sql.AddParameterWithValue("@org", Convert.ToString(orgid));
sql = sql.AddParameterWithValue("@category", Convert.ToString(categoryid));
sql = sql.AddParameterWithValue("@priority", Convert.ToString(priorityid));
sql = sql.AddParameterWithValue("@status", Convert.ToString(statusid));
sql = sql.AddParameterWithValue("@assigned_user", Convert.ToString(assigned_to_userid));
sql = sql.AddParameterWithValue("@udf", Convert.ToString(udfid));
//TODO: Add custom columns
sql.Append("\nselect scope_identity()");
int bugid = Convert.ToInt32(btnet.DbUtil.execute_scalar(sql));
int postid = btnet.Bug.insert_comment(
bugid,
identity.GetUserId(),
comment_formated,
comment_search,
from,
cc,
content_type,
internal_only);
btnet.Bug.auto_subscribe(bugid);
if (send_notifications)
{
btnet.Bug.send_notifications(btnet.Bug.INSERT, bugid, identity);
}
return(new NewIds(bugid, postid));
}
///////////////////////////////////////////////////////////////////////
void load_users_dropdowns(int bugid)
{
// What's selected now? Save it before we refresh the dropdown.
string current_value = "";
if (IsPostBack)
{
current_value = assigned_to.SelectedItem.Value;
}
sql = new SQLString(@"
declare @project int
declare @assigned_to int
select @project = bg_project, @assigned_to = bg_assigned_to_user from bugs where bg_id = @bg_id");
// Load the user dropdown, which changes per project
// Only users explicitly allowed will be listed
if (Util.get_setting("DefaultPermissionLevel", "2") == "0")
{
sql.Append(@"
/* users this project */ select us_id, case when @fullnames=1 then us_lastname + ', ' + us_firstname else us_username end us_username
from users
inner join orgs on us_org = og_id
where us_active = 1
and og_can_be_assigned_to = 1
and (@og_other_orgs_permission_level <> 0 or @og_id = og_id or og_external_user = 0)
and us_id in
(select pu_user from project_user_xref
where pu_project = @project
and pu_permission_level <> 0)
order by us_username; ");
}
// Only users explictly DISallowed will be omitted
else
{
sql.Append(@"
/* users this project */ select us_id, case when @fullnames =1 then us_lastname + ', ' + us_firstname else us_username end us_username
from users
inner join orgs on us_org = og_id
where us_active = 1
and og_can_be_assigned_to = 1
and (@og_other_orgs_permission_level <> 0 or @og_id = og_id or og_external_user = 0)
and us_id not in
(select pu_user from project_user_xref
where pu_project = @project
and pu_permission_level = 0)
order by us_username; ");
}
sql.Append("\nselect st_id, st_name from statuses order by st_sort_seq, st_name");
sql.Append("\nselect isnull(@assigned_to,0) ");
sql = sql.AddParameterWithValue("og_id", User.Identity.GetOrganizationId());
sql = sql.AddParameterWithValue("og_other_orgs_permission_level", User.Identity.GetOtherOrgsPermissionLevels());
sql = sql.AddParameterWithValue("bg_id", bugid);
if (Util.get_setting("UseFullNames", "0") == "0")
{
// false condition
sql = sql.AddParameterWithValue("fullnames", 0);
}
else
{
// true condition
sql = sql.AddParameterWithValue("fullnames", 1);
}
DataSet dataSet = DbUtil.get_dataset(sql);
assigned_to.DataSource = new DataView((DataTable)dataSet.Tables[0]);
assigned_to.DataTextField = "us_username";
assigned_to.DataValueField = "us_id";
assigned_to.DataBind();
assigned_to.Items.Insert(0, new ListItem("[not assigned]", "0"));
status.DataSource = new DataView((DataTable)dataSet.Tables[1]);
status.DataTextField = "st_name";
status.DataValueField = "st_id";
status.DataBind();
status.Items.Insert(0, new ListItem("[no status]", "0"));
// by default, assign the entry to the same user to whom the bug is assigned to?
// or should it be assigned to the logged in user?
if (tsk_id == 0)
{
int default_assigned_to_user = (int)dataSet.Tables[2].Rows[0][0];
ListItem li = assigned_to.Items.FindByValue(Convert.ToString(default_assigned_to_user));
if (li != null)
{
li.Selected = true;
}
}
}
///////////////////////////////////////////////////////////////////////
protected void Page_Load(Object sender, EventArgs e)
{
Util.do_not_cache(Response);
if (User.IsInRole(BtnetRoles.Admin) || User.Identity.GetCanMassEditBugs())
{
//
}
else
{
Response.Write("You are not allowed to use this page.");
Response.End();
}
string list = "";
if (!IsPostBack)
{
Master.Menu.SelectedItem = "admin";
Page.Header.Title = Util.get_setting("AppTitle", "BugTracker.NET") + " - "
+ "massedit";
if (Request["mass_delete"] != null)
{
update_or_delete.Value = "delete";
}
else
{
update_or_delete.Value = "update";
}
// create list of bugs affected
foreach (string var in Request.QueryString)
{
if (Util.is_int(var))
{
if (list != "")
{
list += ",";
}
list += var;
};
}
bug_list.Value = list;
if (update_or_delete.Value == "delete")
{
update_or_delete.Value = "delete";
sql = new SQLString("delete bug_post_attachments from bug_post_attachments inner join bug_posts on bug_post_attachments.bpa_post = bug_posts.bp_id where bug_posts.bp_bug in (" + list + ")");
sql.Append("\ndelete from bug_posts where bp_bug in (" + list + ")");
sql.Append("\ndelete from bug_subscriptions where bs_bug in (" + list + ")");
sql.Append("\ndelete from bug_relationships where re_bug1 in (" + list + ")");
sql.Append("\ndelete from bug_relationships where re_bug2 in (" + list + ")");
sql.Append("\ndelete from bug_user where bu_bug in (" + list + ")");
sql.Append("\ndelete from bug_tasks where tsk_bug in (" + list + ")");
sql.Append("\ndelete from bugs where bg_id in (" + list + ")");
confirm_href.InnerText = "Confirm Delete";
}
else
{
update_or_delete.Value = "update";
sql = new SQLString("update bugs \nset ");
string updates = "";
string val;
val = Request["mass_project"];
if (val != "-1" && Util.is_int(val))
{
if (updates != "") { updates += ",\n"; }
updates += "bg_project = " + val;
}
val = Request["mass_org"];
if (val != "-1" && Util.is_int(val))
{
if (updates != "") { updates += ",\n"; }
updates += "bg_org = " + val;
}
val = Request["mass_category"];
if (val != "-1" && Util.is_int(val))
{
if (updates != "") { updates += ",\n"; }
updates += "bg_category = " + val;
}
val = Request["mass_priority"];
if (val != "-1" && Util.is_int(val))
{
if (updates != "") { updates += ",\n"; }
updates += "bg_priority = " + val;
}
val = Request["mass_assigned_to"];
if (val != "-1" && Util.is_int(val))
{
if (updates != "") { updates += ",\n"; }
updates += "bg_assigned_to_user = "******"mass_reported_by"];
if (val != "-1" && Util.is_int(val))
{
if (updates != "") { updates += ",\n"; }
updates += "bg_reported_user = "******"mass_status"];
if (val != "-1" && Util.is_int(val))
{
if (updates != "") { updates += ",\n"; }
updates += "bg_status = " + val;
}
sql.Append(updates + "\nwhere bg_id in (" + list + ")");
confirm_href.InnerText = "Confirm Update";
}
sql_text.InnerText = sql.ToString();
}
else // postback
{
list = bug_list.Value;
if (update_or_delete.Value == "delete")
{
string upload_folder = Util.get_upload_folder();
if (upload_folder != null)
{
// double check the bug_list
string[] ints = bug_list.Value.Split(',');
for (int i = 0; i < ints.Length; i++)
{
if (!btnet.Util.is_int(ints[i]))
{
Response.End();
}
}
var sql2 = new SQLString(@"select bp_bug, bp_id, bp_file from bug_posts where bp_type = 'file' and bp_bug in (" + bug_list.Value + ")");
DataSet ds = btnet.DbUtil.get_dataset(sql2);
foreach (DataRow dr in ds.Tables[0].Rows)
{
// create path
StringBuilder path = new StringBuilder(upload_folder);
path.Append("\\");
path.Append(Convert.ToString(dr["bp_bug"]));
path.Append("_");
path.Append(Convert.ToString(dr["bp_id"]));
path.Append("_");
path.Append(Convert.ToString(dr["bp_file"]));
if (System.IO.File.Exists(path.ToString()))
{
System.IO.File.Delete(path.ToString());
}
}
}
}
btnet.DbUtil.execute_nonquery(new SQLString(sql_text.InnerText));
Response.Redirect("search.aspx");
}
}
///////////////////////////////////////////////////////////////////////
protected void Page_Load(Object sender, EventArgs e)
{
Util.do_not_cache(Response);
if (User.IsInRole(BtnetRoles.Admin) || User.Identity.GetCanMassEditBugs())
{
//
}
else
{
Response.Write("You are not allowed to use this page.");
Response.End();
}
string list = "";
if (!IsPostBack)
{
Master.Menu.SelectedItem = "admin";
Page.Header.Title = Util.get_setting("AppTitle", "BugTracker.NET") + " - "
+ "massedit";
if (Request["mass_delete"] != null)
{
update_or_delete.Value = "delete";
}
else
{
update_or_delete.Value = "update";
}
// create list of bugs affected
foreach (string var in Request.QueryString)
{
if (Util.is_int(var))
{
if (list != "")
{
list += ",";
}
list += var;
}
;
}
bug_list.Value = list;
if (update_or_delete.Value == "delete")
{
update_or_delete.Value = "delete";
sql = new SQLString("delete bug_post_attachments from bug_post_attachments inner join bug_posts on bug_post_attachments.bpa_post = bug_posts.bp_id where bug_posts.bp_bug in (" + list + ")");
sql.Append("\ndelete from bug_posts where bp_bug in (" + list + ")");
sql.Append("\ndelete from bug_subscriptions where bs_bug in (" + list + ")");
sql.Append("\ndelete from bug_relationships where re_bug1 in (" + list + ")");
sql.Append("\ndelete from bug_relationships where re_bug2 in (" + list + ")");
sql.Append("\ndelete from bug_user where bu_bug in (" + list + ")");
sql.Append("\ndelete from bug_tasks where tsk_bug in (" + list + ")");
sql.Append("\ndelete from bugs where bg_id in (" + list + ")");
confirm_href.InnerText = "Confirm Delete";
}
else
{
update_or_delete.Value = "update";
sql = new SQLString("update bugs \nset ");
string updates = "";
string val;
val = Request["mass_project"];
if (val != "-1" && Util.is_int(val))
{
if (updates != "")
{
updates += ",\n";
}
updates += "bg_project = " + val;
}
val = Request["mass_org"];
if (val != "-1" && Util.is_int(val))
{
if (updates != "")
{
updates += ",\n";
}
updates += "bg_org = " + val;
}
val = Request["mass_category"];
if (val != "-1" && Util.is_int(val))
{
if (updates != "")
{
updates += ",\n";
}
updates += "bg_category = " + val;
}
val = Request["mass_priority"];
if (val != "-1" && Util.is_int(val))
{
if (updates != "")
{
updates += ",\n";
}
updates += "bg_priority = " + val;
}
val = Request["mass_assigned_to"];
if (val != "-1" && Util.is_int(val))
{
if (updates != "")
{
updates += ",\n";
}
updates += "bg_assigned_to_user = "******"mass_reported_by"];
if (val != "-1" && Util.is_int(val))
{
if (updates != "")
{
updates += ",\n";
}
updates += "bg_reported_user = "******"mass_status"];
if (val != "-1" && Util.is_int(val))
{
if (updates != "")
{
updates += ",\n";
}
updates += "bg_status = " + val;
}
sql.Append(updates + "\nwhere bg_id in (" + list + ")");
confirm_href.InnerText = "Confirm Update";
}
sql_text.InnerText = sql.ToString();
}
else // postback
{
list = bug_list.Value;
if (update_or_delete.Value == "delete")
{
string upload_folder = Util.get_upload_folder();
if (upload_folder != null)
{
// double check the bug_list
string[] ints = bug_list.Value.Split(',');
for (int i = 0; i < ints.Length; i++)
{
if (!btnet.Util.is_int(ints[i]))
{
Response.End();
}
}
var sql2 = new SQLString(@"select bp_bug, bp_id, bp_file from bug_posts where bp_type = 'file' and bp_bug in (" + bug_list.Value + ")");
DataSet ds = btnet.DbUtil.get_dataset(sql2);
foreach (DataRow dr in ds.Tables[0].Rows)
{
// create path
StringBuilder path = new StringBuilder(upload_folder);
path.Append("\\");
path.Append(Convert.ToString(dr["bp_bug"]));
path.Append("_");
path.Append(Convert.ToString(dr["bp_id"]));
path.Append("_");
path.Append(Convert.ToString(dr["bp_file"]));
if (System.IO.File.Exists(path.ToString()))
{
System.IO.File.Delete(path.ToString());
}
}
}
}
btnet.DbUtil.execute_nonquery(new SQLString(sql_text.InnerText));
Response.Redirect("search.aspx");
}
}
void update_project_user_xref()
{
System.Collections.Hashtable hash_projects = new System.Collections.Hashtable();
foreach (ListItem li in project_auto_subscribe.Items)
{
Project p = new Project();
p.id = Convert.ToInt32(li.Value);
hash_projects[p.id] = p;
if (li.Selected)
{
p.auto_subscribe = 1;
p.maybe_insert = true;
}
else
{
p.auto_subscribe = 0;
}
}
foreach (ListItem li in project_admin.Items)
{
Project p = (Project)hash_projects[Convert.ToInt32(li.Value)];
if (li.Selected)
{
p.admin = 1;
p.maybe_insert = true;
}
else
{
p.admin = 0;
}
}
RadioButton rb;
int permission_level;
int default_permission_level = Convert.ToInt32(Util.get_setting("DefaultPermissionLevel", "2"));
foreach (DataGridItem dgi in MyDataGrid.Items)
{
rb = (RadioButton)dgi.FindControl("none");
if (rb.Checked)
{
permission_level = 0;
}
else
{
rb = (RadioButton)dgi.FindControl("readonly");
if (rb.Checked)
{
permission_level = 1;
}
else
{
rb = (RadioButton)dgi.FindControl("reporter");
if (rb.Checked)
{
permission_level = 3;
}
else
{
permission_level = 2;
}
}
}
int pj_id = Convert.ToInt32(dgi.Cells[1].Text);
Project p = (Project)hash_projects[pj_id];
p.permission_level = permission_level;
if (permission_level != default_permission_level)
{
p.maybe_insert = true;
}
}
string projects = "";
foreach (Project p in hash_projects.Values)
{
if (p.maybe_insert)
{
if (projects != "")
{
projects += ",";
}
projects += Convert.ToString(p.id);
}
}
sql = new SQLString("");
// Insert new recs - we will update them later
// Downstream logic is now simpler in that it just deals with existing recs
if (projects != "")
{
sql.Append(String.Format(@"
insert into project_user_xref (pu_project, pu_user, pu_auto_subscribe)
select pj_id, @us, 0
from projects
where pj_id in ({0})
and pj_id not in (select pu_project from project_user_xref where pu_user = @us);", projects));
}
// First turn everything off, then turn selected ones on.
sql.Append(@"
update project_user_xref
set pu_auto_subscribe = 0,
pu_admin = 0,
pu_permission_level = @dpl
where pu_user = @us;");
projects = "";
foreach (Project p in hash_projects.Values)
{
if (p.auto_subscribe == 1)
{
if (projects != "")
{
projects += ",";
}
projects += Convert.ToString(p.id);
}
}
string auto_subscribe_projects = projects; // save for later
if (projects != "")
{
sql.Append(String.Format(@"
update project_user_xref
set pu_auto_subscribe = 1
where pu_user = @us
and pu_project in ({0});", projects));
}
if (User.IsInRole(BtnetRoles.Admin))
{
projects = "";
foreach (Project p in hash_projects.Values)
{
if (p.admin == 1)
{
if (projects != "")
{
projects += ",";
}
projects += Convert.ToString(p.id);
}
}
if (projects != "")
{
sql.Append(String.Format(@"
update project_user_xref
set pu_admin = 1
where pu_user = @us
and pu_project in ({0});", projects));
}
}
// update permission levels to 0
projects = "";
foreach (Project p in hash_projects.Values)
{
if (p.permission_level == 0)
{
if (projects != "")
{
projects += ",";
}
projects += Convert.ToString(p.id);
}
}
if (projects != "")
{
sql.Append(String.Format(@"
update project_user_xref
set pu_permission_level = 0
where pu_user = @us
and pu_project in ({0});", projects));
}
// update permission levels to 1
projects = "";
foreach (Project p in hash_projects.Values)
{
if (p.permission_level == 1)
{
if (projects != "")
{
projects += ",";
}
projects += Convert.ToString(p.id);
}
}
if (projects != "")
{
sql.Append(String.Format(@"
update project_user_xref
set pu_permission_level = 1
where pu_user = @us
and pu_project in ({0});", projects));
}
// update permission levels to 2
projects = "";
foreach (Project p in hash_projects.Values)
{
if (p.permission_level == 2)
{
if (projects != "")
{
projects += ",";
}
projects += Convert.ToString(p.id);
}
}
if (projects != "")
{
sql.Append(String.Format(@"
update project_user_xref
set pu_permission_level = 2
where pu_user = @us
and pu_project in ({0});", projects));
}
// update permission levels to 3
projects = "";
foreach (Project p in hash_projects.Values)
{
if (p.permission_level == 3)
{
if (projects != "")
{
projects += ",";
}
projects += Convert.ToString(p.id);
}
}
if (projects != "")
{
String.Format(@"
update project_user_xref
set pu_permission_level = 3
where pu_user = @us
and pu_project in ({0});", projects);
}
// apply subscriptions retroactively
if (retroactive.Checked)
{
sql = new SQLString(@"
delete from bug_subscriptions where bs_user = @us;");
if (auto_subscribe.Checked)
{
sql.Append(@"
insert into bug_subscriptions (bs_bug, bs_user)
select bg_id, @us from bugs;");
}
else
{
if (auto_subscribe_reported.Checked)
{
sql.Append(@"
insert into bug_subscriptions (bs_bug, bs_user)
select bg_id, @us from bugs where bg_reported_user = @us
and bg_id not in (select bs_bug from bug_subscriptions where bs_user = @us);");
}
if (auto_subscribe_own.Checked)
{
sql.Append(@"
insert into bug_subscriptions (bs_bug, bs_user)
select bg_id, @us from bugs where bg_assigned_to_user = @us
and bg_id not in (select bs_bug from bug_subscriptions where bs_user = @us);");
}
if (auto_subscribe_projects != "")
{
sql.Append(String.Format(@"
insert into bug_subscriptions (bs_bug, bs_user)
select bg_id, @us from bugs where bg_project in ({0})
and bg_id not in (select bs_bug from bug_subscriptions where bs_user = us);", projects));
}
}
}
sql = sql.AddParameterWithValue("us", Convert.ToString(id));
sql = sql.AddParameterWithValue("dpl", Convert.ToString(default_permission_level));
btnet.DbUtil.execute_nonquery(sql);
}
///////////////////////////////////////////////////////////////////////
public static DataTable get_related_users(IIdentity identity, bool force_full_names)
{
SQLString sql;
const string limitUsernameDropdownSql = @"
select isnull(bg_assigned_to_user,0) keep_me
into #temp2
from bugs
union
select isnull(bg_reported_user,0) from bugs
delete from #temp
where us_id not in (select keep_me from #temp2)
drop table #temp2
";
if (Util.get_setting("DefaultPermissionLevel", "2") == "0")
{
// only show users who have explicit permission
// for projects that this user has permissions for
sql = new SQLString(@"
/* get related users 1 */
select us_id,
case when @fullnames = 1 then
case when len(isnull(us_firstname,'') + ' ' + isnull(us_lastname,'')) > 1
then isnull(us_firstname,'') + ' ' + isnull(us_lastname,'')
else us_username end
else us_username end us_username,
isnull(us_email,'') us_email,
us_org,
og_external_user
into #temp
from users
inner join orgs on us_org = og_id
where us_id in
(select pu1.pu_user from project_user_xref pu1
where pu1.pu_project in
(select pu2.pu_project from project_user_xref pu2
where pu2.pu_user = @userid
and pu2.pu_permission_level <> 0
)
and pu1.pu_permission_level <> 0
)
if @og_external_user = 1 -- external
and @og_other_orgs_permission_level = 0 -- other orgs
begin
delete from #temp where og_external_user = 1 and us_org <> @userorg
end
");
if (Util.get_setting("LimitUsernameDropdownsInSearch", "0") == "1")
{
sql.Append(limitUsernameDropdownSql);
}
sql.Append(@"
select us_id, us_username, us_email from #temp order by us_username
drop table #temp");
}
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 = new SQLString(@"
/* get related users 2 */
select pj_id, us_id,
case when @fullnames = 1 then
case when len(isnull(us_firstname,'') + ' ' + isnull(us_lastname,'')) > 1
then isnull(us_firstname,'') + ' ' + isnull(us_lastname,'')
else us_username end
else us_username end us_username,
isnull(us_email,'') us_email
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 = @userid
)
");
if (Util.get_setting("LimitUsernameDropdownsInSearch", "0") == "1")
{
sql.Append(limitUsernameDropdownSql);
}
sql.Append(@"
if @og_external_user = 1 -- external
and @og_other_orgs_permission_level = 0 -- other orgs
begin
select distinct a.us_id, a.us_username, a.us_email
from #temp a
inner join users b on a.us_id = b.us_id
inner join orgs on b.us_org = og_id
where og_external_user = 0 or b.us_org = @userorg
order by a.us_username
end
else
begin
select distinct us_id, us_username, us_email
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
end
drop table #temp");
}
if (force_full_names || Util.get_setting("UseFullNames", "0") == "1")
{
// true condition
sql = sql.AddParameterWithValue("fullnames", 1);
}
else
{
// false condition
sql = sql.AddParameterWithValue("fullnames", 0);
}
sql = sql.AddParameterWithValue("userid", identity.GetUserId());
sql = sql.AddParameterWithValue("userorg", identity.GetOrganizationId());
sql = sql.AddParameterWithValue("og_external_user", identity.GetIsExternalUser() ? 1 : 0);
sql = sql.AddParameterWithValue("og_other_orgs_permission_level", identity.GetOtherOrgsPermissionLevels());
return(btnet.DbUtil.get_dataset(sql).Tables[0]);
}
///////////////////////////////////////////////////////////////////////
public static DataSet get_all_tasks(IIdentity identity, int bugid)
{
var sql = new SQLString("select ");
if (bugid == 0)
{
sql.Append(@"
bg_id as [id],
bg_short_desc as [description],
pj_name as [project],
ct_name as [category],
bug_statuses.st_name as [status],
bug_users.us_username as [assigned to],");
}
sql.Append("tsk_id [task<br>id], tsk_description [task<br>description] ");
if (btnet.Util.get_setting("ShowTaskAssignedTo", "1") == "1")
{
sql.Append(", task_users.us_username [task<br>assigned to]");
}
if (btnet.Util.get_setting("ShowTaskPlannedStartDate", "1") == "1")
{
sql.Append(", tsk_planned_start_date [planned start]");
}
if (btnet.Util.get_setting("ShowTaskActualStartDate", "1") == "1")
{
sql.Append(", tsk_actual_start_date [actual start]");
}
if (btnet.Util.get_setting("ShowTaskPlannedEndDate", "1") == "1")
{
sql.Append(", tsk_planned_end_date [planned end]");
}
if (btnet.Util.get_setting("ShowTaskActualEndDate", "1") == "1")
{
sql.Append(", tsk_actual_end_date [actual end]");
}
if (btnet.Util.get_setting("ShowTaskPlannedDuration", "1") == "1")
{
sql.Append(", tsk_planned_duration [planned<br>duration]");
}
if (btnet.Util.get_setting("ShowTaskActualDuration", "1") == "1")
{
sql.Append(", tsk_actual_duration [actual<br>duration]");
}
if (btnet.Util.get_setting("ShowTaskDurationUnits", "1") == "1")
{
sql.Append(", tsk_duration_units [duration<br>units]");
}
if (btnet.Util.get_setting("ShowTaskPercentComplete", "1") == "1")
{
sql.Append(", tsk_percent_complete [percent<br>complete]");
}
if (btnet.Util.get_setting("ShowTaskStatus", "1") == "1")
{
sql.Append(", task_statuses.st_name [task<br>status]");
}
if (btnet.Util.get_setting("ShowTaskSortSequence", "1") == "1")
{
sql.Append(", tsk_sort_sequence [seq]");
}
sql.Append(@"
from bug_tasks
inner join bugs on tsk_bug = bg_id
left outer join projects on bg_project = pj_id
left outer join categories on bg_category = ct_id
left outer join statuses bug_statuses on bg_status = bug_statuses.st_id
left outer join statuses task_statuses on tsk_status = task_statuses.st_id
left outer join users bug_users on bg_assigned_to_user = bug_users.us_id
left outer join users task_users on tsk_assigned_to_user = task_users.us_id
where tsk_bug in
(");
if (bugid == 0)
{
sql.Append(btnet.Util.alter_sql_per_project_permissions(new SQLString("select bg_id from bugs"), identity));
}
else
{
sql.Append(Convert.ToString(bugid));
}
sql.Append(@"
)
order by tsk_sort_sequence, tsk_id");
DataSet ds = btnet.DbUtil.get_dataset(sql);
return(ds);
}
public BugQueryResult ExecuteQuery(IIdentity identity, int start, int length, string orderBy, string sortDirection, bool idOnly, BugQueryFilter[] filters = null)
{
if (!string.IsNullOrEmpty(orderBy) && !_columnNames.Contains(orderBy))
{
throw new ArgumentException("Invalid order by column specified: {0}", orderBy);
}
bool hasFilters = filters != null && filters.Any();
string columnsToSelect = idOnly ? "id" : "*";
var innerSql = GetInnerSql(identity);
var countSql = string.Format("SELECT COUNT(1) FROM ({0}) t", GetInnerSql(identity));
SQLString sqlString = new SQLString(countSql);
sqlString.Append(";");
if (hasFilters)
{
sqlString.Append(countSql);
ApplyWhereClause(sqlString, filters);
sqlString.Append(";");
}
var bugsSql = string.Format("SELECT t.{0} FROM ({1}) t", columnsToSelect, innerSql);
sqlString.Append(bugsSql);
sqlString.Append(" WHERE id IN (");
var innerBugsSql = string.Format("SELECT t.id FROM ({0}) t", innerSql);
sqlString.Append(innerBugsSql);
ApplyWhereClause(sqlString, filters);
if (hasFilters)
{
foreach (var filter in filters)
{
sqlString.AddParameterWithValue(GetCleanParameterName(filter.Column), filter.Value);
}
}
sqlString.Append(" ORDER BY ");
sqlString.Append(BuildDynamicOrderByClause(orderBy, sortDirection));
sqlString.Append(" OFFSET @offset ROWS FETCH NEXT @page_size ROWS ONLY)");
int userId = identity.GetUserId();
sqlString.AddParameterWithValue("@ME", userId);
sqlString.AddParameterWithValue("page_size", length > 0 ? length : MaxLength);
sqlString.AddParameterWithValue("offset", start);
DataSet dataSet = DbUtil.get_dataset(sqlString);
var countUnfiltered = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
var countFiltered = hasFilters ? Convert.ToInt32(dataSet.Tables[1].Rows[0][0]) : countUnfiltered;
var bugDataTableIndex = hasFilters ? 2 : 1;
return(new BugQueryResult
{
CountUnfiltered = countUnfiltered,
CountFiltered = countFiltered,
Data = dataSet.Tables[bugDataTableIndex]
});
}
///////////////////////////////////////////////////////////////////////
void on_update()
{
Boolean good = validate();
if (good)
{
sql = new SQLString(@"update users set
us_firstname = @fn,
us_lastname = @ln,
us_bugs_per_page = @bp,
us_use_fckeditor = @fk,
us_enable_bug_list_popups = @pp,
us_email = @em,
us_enable_notifications = @en,
us_send_notifications_to_self = @ss,
us_reported_notifications = @rn,
us_assigned_notifications = @an,
us_subscribed_notifications = @sn,
us_auto_subscribe = @as,
us_auto_subscribe_own_bugs = @ao,
us_auto_subscribe_reported_bugs = @ar,
us_default_query = @dq,
us_signature = @sg
where us_id = @id");
sql = sql.AddParameterWithValue("fn", firstname.Value);
sql = sql.AddParameterWithValue("ln", lastname.Value);
sql = sql.AddParameterWithValue("bp", bugs_per_page.Value);
sql = sql.AddParameterWithValue("fk", Util.bool_to_string(use_fckeditor.Checked));
sql = sql.AddParameterWithValue("pp", Util.bool_to_string(enable_popups.Checked));
sql = sql.AddParameterWithValue("em", email.Value);
sql = sql.AddParameterWithValue("en", Util.bool_to_string(enable_notifications.Checked));
sql = sql.AddParameterWithValue("ss", Util.bool_to_string(send_to_self.Checked));
sql = sql.AddParameterWithValue("rn", reported_notifications.SelectedItem.Value);
sql = sql.AddParameterWithValue("an", assigned_notifications.SelectedItem.Value);
sql = sql.AddParameterWithValue("sn", subscribed_notifications.SelectedItem.Value);
sql = sql.AddParameterWithValue("as", Util.bool_to_string(auto_subscribe.Checked));
sql = sql.AddParameterWithValue("ao", Util.bool_to_string(auto_subscribe_own.Checked));
sql = sql.AddParameterWithValue("ar", Util.bool_to_string(auto_subscribe_reported.Checked));
sql = sql.AddParameterWithValue("dq", query.SelectedItem.Value);
sql = sql.AddParameterWithValue("sg", signature.InnerText);
sql = sql.AddParameterWithValue("id", Convert.ToString(id));
// update user
btnet.DbUtil.execute_nonquery(sql);
// update the password
if (pw.Value != "")
{
btnet.Util.update_user_password(id, pw.Value);
}
// Now update project_user_xref
// First turn everything off, then turn selected ones on.
sql = new SQLString(@"update project_user_xref
set pu_auto_subscribe = 0 where pu_user = @id");
sql = sql.AddParameterWithValue("id", Convert.ToString(id));
btnet.DbUtil.execute_nonquery(sql);
// Second see what to turn back on
string projects = "";
foreach (ListItem li in project_auto_subscribe.Items)
{
if (li.Selected)
{
if (projects != "")
{
projects += ",";
}
projects += Convert.ToInt32(li.Value);
}
}
// If we need to turn anything back on
if (projects != "")
{
sql = new SQLString(@"update project_user_xref
set pu_auto_subscribe = 1 where pu_user = @id and pu_project in ($projects)
insert into project_user_xref (pu_project, pu_user, pu_auto_subscribe)
select pj_id, @id, 1
from projects
where pj_id in (projects)
and pj_id not in (select pu_project from project_user_xref where pu_user = @id)");
sql = sql.AddParameterWithValue("id", Convert.ToString(id));
sql = sql.AddParameterWithValue("projects", projects);
btnet.DbUtil.execute_nonquery(sql);
}
// apply subscriptions retroactively
if (retroactive.Checked)
{
sql = new SQLString(@"delete from bug_subscriptions where bs_user = @id;");
if (auto_subscribe.Checked)
{
sql.Append(@"insert into bug_subscriptions (bs_bug, bs_user)
select bg_id, @id from bugs;");
}
else
{
if (auto_subscribe_reported.Checked)
{
sql.Append(@"insert into bug_subscriptions (bs_bug, bs_user)
select bg_id, @id from bugs where bg_reported_user = @id
and bg_id not in (select bs_bug from bug_subscriptions where bs_user = @id);");
}
if (auto_subscribe_own.Checked)
{
sql.Append(@"insert into bug_subscriptions (bs_bug, bs_user)
select bg_id, @id from bugs where bg_assigned_to_user = @id
and bg_id not in (select bs_bug from bug_subscriptions where bs_user = @id);");
}
if (projects != "")
{
sql.Append(@"insert into bug_subscriptions (bs_bug, bs_user)
select bg_id, @id from bugs where bg_project in (@projects)
and bg_id not in (select bs_bug from bug_subscriptions where bs_user = @id);");
}
}
sql = sql.AddParameterWithValue("id", Convert.ToString(id));
sql = sql.AddParameterWithValue("projects", projects);
btnet.DbUtil.execute_nonquery(sql);
}
msg.InnerText = "Your settings have been updated.";
}
else
{
msg.InnerText = "Your settings have not been updated.";
}
}
///////////////////////////////////////////////////////////////////////
protected void Page_Load(Object sender, EventArgs e)
{
Util.do_not_cache(Response);
Master.Menu.SelectedItem = "admin";
Page.Header.Title = Util.get_setting("AppTitle", "BugTracker.NET") + " - "
+ "edit user";
if (!User.IsInRole(BtnetRoles.Admin))
{
// Check if the current user is an admin for any project
sql = new SQLString(@"select pu_project
from project_user_xref
where pu_user = us
and pu_admin = 1" );
sql = sql.AddParameterWithValue("us", Convert.ToString(User.Identity.GetUserId()));
DataSet ds_projects = btnet.DbUtil.get_dataset(sql);
if (ds_projects.Tables[0].Rows.Count == 0)
{
Response.Write("You not allowed to add users.");
Response.End();
}
admin.Visible = false;
admin_label.Visible = false;
project_admin_label.Visible = false;
project_admin.Visible = false;
project_admin_help.Visible = false;
}
if (Request["copy"] != null && Request["copy"] == "y")
{
copy = true;
}
msg.InnerText = "";
string var = Request.QueryString["id"];
if (var == null)
{
id = 0;
// MAW -- 2006/01/27 -- Set default settings when adding a new user
auto_subscribe_own.Checked = true;
auto_subscribe_reported.Checked = true;
enable_popups.Checked = true;
reported_notifications.Items[4].Selected = true;
assigned_notifications.Items[4].Selected = true;
subscribed_notifications.Items[4].Selected = true;
}
else
{
id = Convert.ToInt32(var);
}
if (!IsPostBack)
{
if (!User.IsInRole(BtnetRoles.Admin))
{
// logged in user is a project level admin
// get values for permissions grid
// Table 0
sql = new SQLString(@"
select pj_id, pj_name,
isnull(a.pu_permission_level,@dpl) [pu_permission_level],
isnull(a.pu_auto_subscribe,0) [pu_auto_subscribe],
isnull(a.pu_admin,0) [pu_admin]
from projects
inner join project_user_xref project_admin on pj_id = project_admin.pu_project
and project_admin.pu_user = @this_usid
and project_admin.pu_admin = 1
left outer join project_user_xref a on pj_id = a.pu_project
and a.pu_user = @us
order by pj_name;" );
sql = sql.AddParameterWithValue("this_usid", Convert.ToString(User.Identity.GetUserId()));
}
else // user is a real admin
{
// Table 0
// populate permissions grid
sql = new SQLString(@"
select pj_id, pj_name,
isnull(pu_permission_level,@dpl) [pu_permission_level],
isnull(pu_auto_subscribe,0) [pu_auto_subscribe],
isnull(pu_admin,0) [pu_admin]
from projects
left outer join project_user_xref on pj_id = pu_project
and pu_user = @us
order by pj_name;" );
}
// Table 1
sql.Append(@"/* populate query dropdown */
declare @org int
set @org = null
select @org = us_org from users where us_id = @us
select qu_id, qu_desc
from queries
where (isnull(qu_user,0) = 0 and isnull(qu_org,0) = 0)
or isnull(qu_user,0) = @us
or isnull(qu_org,0) = isnull(@org,-1)
order by qu_desc;" );
// Table 2
if (User.IsInRole(BtnetRoles.Admin))
{
sql.Append(@"/* populate org dropdown 1 */
select og_id, og_name
from orgs
order by og_name;" );
}
else
{
if (User.Identity.GetOtherOrgsPermissionLevels() == PermissionLevel.All)
{
sql.Append(@"/* populate org dropdown 2 */
select og_id, og_name
from orgs
where og_non_admins_can_use = 1
order by og_name;" );
}
else
{
sql.Append(@"/* populate org dropdown 3 */
select 1; -- dummy" );
}
}
// Table 3
if (id != 0)
{
// get existing user values
sql.Append(@"
select
us_username,
isnull(us_firstname,'') [us_firstname],
isnull(us_lastname,'') [us_lastname],
isnull(us_bugs_per_page,10) [us_bugs_per_page],
us_use_fckeditor,
us_enable_bug_list_popups,
isnull(us_email,'') [us_email],
us_active,
us_admin,
us_enable_notifications,
us_send_notifications_to_self,
us_reported_notifications,
us_assigned_notifications,
us_subscribed_notifications,
us_auto_subscribe,
us_auto_subscribe_own_bugs,
us_auto_subscribe_reported_bugs,
us_default_query,
us_org,
isnull(us_signature,'') [us_signature],
isnull(us_forced_project,0) [us_forced_project],
us_created_user
from users
where us_id = @us" );
}
sql = sql.AddParameterWithValue("us", Convert.ToString(id));
sql = sql.AddParameterWithValue("dpl", Util.get_setting("DefaultPermissionLevel", "2"));
DataSet ds = btnet.DbUtil.get_dataset(sql);
// query dropdown
query.DataSource = ds.Tables[1].DefaultView;
query.DataTextField = "qu_desc";
query.DataValueField = "qu_id";
query.DataBind();
// forced project dropdown
forced_project.DataSource = ds.Tables[0].DefaultView;
forced_project.DataTextField = "pj_name";
forced_project.DataValueField = "pj_id";
forced_project.DataBind();
forced_project.Items.Insert(0, new ListItem("[no forced project]", "0"));
// org dropdown
if (User.IsInRole(BtnetRoles.Admin) ||
User.Identity.GetOtherOrgsPermissionLevels() == PermissionLevel.All)
{
org.DataSource = ds.Tables[2].DefaultView;
org.DataTextField = "og_name";
org.DataValueField = "og_id";
org.DataBind();
org.Items.Insert(0, new ListItem("[select org]", "0"));
}
else
{
int organizationId = User.Identity.GetOrganizationId();
int orgRow = ds.Tables[2].DefaultView.Find(organizationId);
org.Items.Insert(0, new ListItem((string)ds.Tables[2].Rows[orgRow]["og_name"], Convert.ToString(organizationId)));
}
// populate permissions grid
MyDataGrid.DataSource = ds.Tables[0].DefaultView;
MyDataGrid.DataBind();
// subscribe by project dropdown
project_auto_subscribe.DataSource = ds.Tables[0].DefaultView;
project_auto_subscribe.DataTextField = "pj_name";
project_auto_subscribe.DataValueField = "pj_id";
project_auto_subscribe.DataBind();
// project admin dropdown
project_admin.DataSource = ds.Tables[0].DefaultView;
project_admin.DataTextField = "pj_name";
project_admin.DataValueField = "pj_id";
project_admin.DataBind();
// add or edit?
if (id == 0)
{
sub.Value = "Create";
bugs_per_page.Value = "10";
active.Checked = true;
enable_notifications.Checked = true;
}
else
{
sub.Value = "Update";
// get the values for this existing user
DataRow dr = ds.Tables[3].Rows[0];
// check if project admin is allowed to edit this user
if (!User.IsInRole(BtnetRoles.Admin))
{
if (User.Identity.GetUserId() != (int)dr["us_created_user"])
{
Response.Write("You not allowed to edit this user, because you didn't create it.");
Response.End();
}
else if ((int)dr["us_admin"] == 1)
{
Response.Write("You not allowed to edit this user, because it is an admin.");
Response.End();
}
}
// select values in dropdowns
// select forced project
int current_forced_project = (int)dr["us_forced_project"];
foreach (ListItem li in forced_project.Items)
{
if (Convert.ToInt32(li.Value) == current_forced_project)
{
li.Selected = true;
break;
}
}
// Fill in this form
if (copy)
{
username.Value = "Enter username here";
firstname.Value = "";
lastname.Value = "";
email.Value = "";
signature.InnerText = "";
}
else
{
username.Value = (string)dr["us_username"];
firstname.Value = (string)dr["us_firstname"];
lastname.Value = (string)dr["us_lastname"];
email.Value = (string)dr["us_email"];
signature.InnerText = (string)dr["us_signature"];
}
bugs_per_page.Value = Convert.ToString(dr["us_bugs_per_page"]);
use_fckeditor.Checked = Convert.ToBoolean((int)dr["us_use_fckeditor"]);
enable_popups.Checked = Convert.ToBoolean((int)dr["us_enable_bug_list_popups"]);
active.Checked = Convert.ToBoolean((int)dr["us_active"]);
admin.Checked = Convert.ToBoolean((int)dr["us_admin"]);
enable_notifications.Checked = Convert.ToBoolean((int)dr["us_enable_notifications"]);
send_to_self.Checked = Convert.ToBoolean((int)dr["us_send_notifications_to_self"]);
reported_notifications.Items[(int)dr["us_reported_notifications"]].Selected = true;
assigned_notifications.Items[(int)dr["us_assigned_notifications"]].Selected = true;
subscribed_notifications.Items[(int)dr["us_subscribed_notifications"]].Selected = true;
auto_subscribe.Checked = Convert.ToBoolean((int)dr["us_auto_subscribe"]);
auto_subscribe_own.Checked = Convert.ToBoolean((int)dr["us_auto_subscribe_own_bugs"]);
auto_subscribe_reported.Checked = Convert.ToBoolean((int)dr["us_auto_subscribe_reported_bugs"]);
// org
foreach (ListItem li in org.Items)
{
if (Convert.ToInt32(li.Value) == (int)dr["us_org"])
{
li.Selected = true;
break;
}
}
// query
foreach (ListItem li in query.Items)
{
if (Convert.ToInt32(li.Value) == (int)dr["us_default_query"])
{
li.Selected = true;
break;
}
}
// select projects
foreach (DataRow dr2 in ds.Tables[0].Rows)
{
foreach (ListItem li in project_auto_subscribe.Items)
{
if (Convert.ToInt32(li.Value) == (int)dr2["pj_id"])
{
if ((int)dr2["pu_auto_subscribe"] == 1)
{
li.Selected = true;
}
else
{
li.Selected = false;
}
}
}
}
foreach (DataRow dr3 in ds.Tables[0].Rows)
{
foreach (ListItem li in project_admin.Items)
{
if (Convert.ToInt32(li.Value) == (int)dr3["pj_id"])
{
if ((int)dr3["pu_admin"] == 1)
{
li.Selected = true;
}
else
{
li.Selected = false;
}
}
}
}
} // add or edit
} // if !postback
else
{
on_update();
}
}
///////////////////////////////////////////////////////////////////////
public static DataSet get_all_tasks(IIdentity identity, int bugid)
{
var sql = new SQLString("select ");
if (bugid == 0)
{
sql.Append( @"
bg_id as [id],
bg_short_desc as [description],
pj_name as [project],
ct_name as [category],
bug_statuses.st_name as [status],
bug_users.us_username as [assigned to],");
}
sql.Append("tsk_id [task<br>id], tsk_description [task<br>description] ");
if (btnet.Util.get_setting("ShowTaskAssignedTo","1") == "1")
{
sql.Append(", task_users.us_username [task<br>assigned to]");
}
if (btnet.Util.get_setting("ShowTaskPlannedStartDate","1") == "1")
{
sql.Append(", tsk_planned_start_date [planned start]");
}
if (btnet.Util.get_setting("ShowTaskActualStartDate","1") == "1")
{
sql.Append(", tsk_actual_start_date [actual start]");
}
if (btnet.Util.get_setting("ShowTaskPlannedEndDate","1") == "1")
{
sql.Append(", tsk_planned_end_date [planned end]");
}
if (btnet.Util.get_setting("ShowTaskActualEndDate","1") == "1")
{
sql.Append(", tsk_actual_end_date [actual end]");
}
if (btnet.Util.get_setting("ShowTaskPlannedDuration","1") == "1")
{
sql.Append(", tsk_planned_duration [planned<br>duration]");
}
if (btnet.Util.get_setting("ShowTaskActualDuration","1") == "1")
{
sql.Append( ", tsk_actual_duration [actual<br>duration]");
}
if (btnet.Util.get_setting("ShowTaskDurationUnits","1") == "1")
{
sql.Append(", tsk_duration_units [duration<br>units]");
}
if (btnet.Util.get_setting("ShowTaskPercentComplete","1") == "1")
{
sql.Append(", tsk_percent_complete [percent<br>complete]");
}
if (btnet.Util.get_setting("ShowTaskStatus","1") == "1")
{
sql.Append(", task_statuses.st_name [task<br>status]");
}
if (btnet.Util.get_setting("ShowTaskSortSequence","1") == "1")
{
sql.Append(", tsk_sort_sequence [seq]");
}
sql.Append(@"
from bug_tasks
inner join bugs on tsk_bug = bg_id
left outer join projects on bg_project = pj_id
left outer join categories on bg_category = ct_id
left outer join statuses bug_statuses on bg_status = bug_statuses.st_id
left outer join statuses task_statuses on tsk_status = task_statuses.st_id
left outer join users bug_users on bg_assigned_to_user = bug_users.us_id
left outer join users task_users on tsk_assigned_to_user = task_users.us_id
where tsk_bug in
(");
if (bugid == 0)
{
sql.Append(btnet.Util.alter_sql_per_project_permissions(new SQLString("select bg_id from bugs"), identity));
}
else
{
sql.Append(Convert.ToString(bugid));
}
sql.Append(@"
)
order by tsk_sort_sequence, tsk_id");
DataSet ds = btnet.DbUtil.get_dataset(sql);
return ds;
}
void update_project_user_xref()
{
System.Collections.Hashtable hash_projects = new System.Collections.Hashtable();
foreach (ListItem li in project_auto_subscribe.Items)
{
Project p = new Project();
p.id = Convert.ToInt32(li.Value);
hash_projects[p.id] = p;
if (li.Selected)
{
p.auto_subscribe = 1;
p.maybe_insert = true;
}
else
{
p.auto_subscribe = 0;
}
}
foreach (ListItem li in project_admin.Items)
{
Project p = (Project)hash_projects[Convert.ToInt32(li.Value)];
if (li.Selected)
{
p.admin = 1;
p.maybe_insert = true;
}
else
{
p.admin = 0;
}
}
RadioButton rb;
int permission_level;
int default_permission_level = Convert.ToInt32(Util.get_setting("DefaultPermissionLevel", "2"));
foreach (DataGridItem dgi in MyDataGrid.Items)
{
rb = (RadioButton)dgi.FindControl("none");
if (rb.Checked)
{
permission_level = 0;
}
else
{
rb = (RadioButton)dgi.FindControl("readonly");
if (rb.Checked)
{
permission_level = 1;
}
else
{
rb = (RadioButton)dgi.FindControl("reporter");
if (rb.Checked)
{
permission_level = 3;
}
else
{
permission_level = 2;
}
}
}
int pj_id = Convert.ToInt32(dgi.Cells[1].Text);
Project p = (Project)hash_projects[pj_id];
p.permission_level = permission_level;
if (permission_level != default_permission_level)
{
p.maybe_insert = true;
}
}
string projects = "";
foreach (Project p in hash_projects.Values)
{
if (p.maybe_insert)
{
if (projects != "")
{
projects += ",";
}
projects += Convert.ToString(p.id);
}
}
sql = new SQLString("");
// Insert new recs - we will update them later
// Downstream logic is now simpler in that it just deals with existing recs
if (projects != "")
{
sql.Append(String.Format(@"
insert into project_user_xref (pu_project, pu_user, pu_auto_subscribe)
select pj_id, @us, 0
from projects
where pj_id in ({0})
and pj_id not in (select pu_project from project_user_xref where pu_user = @us);" , projects));
}
// First turn everything off, then turn selected ones on.
sql.Append(@"
update project_user_xref
set pu_auto_subscribe = 0,
pu_admin = 0,
pu_permission_level = @dpl
where pu_user = @us;" );
projects = "";
foreach (Project p in hash_projects.Values)
{
if (p.auto_subscribe == 1)
{
if (projects != "")
{
projects += ",";
}
projects += Convert.ToString(p.id);
}
}
string auto_subscribe_projects = projects; // save for later
if (projects != "")
{
sql.Append(String.Format(@"
update project_user_xref
set pu_auto_subscribe = 1
where pu_user = @us
and pu_project in ({0});" , projects));
}
if (User.IsInRole(BtnetRoles.Admin))
{
projects = "";
foreach (Project p in hash_projects.Values)
{
if (p.admin == 1)
{
if (projects != "")
{
projects += ",";
}
projects += Convert.ToString(p.id);
}
}
if (projects != "")
{
sql.Append(String.Format(@"
update project_user_xref
set pu_admin = 1
where pu_user = @us
and pu_project in ({0});" , projects));
}
}
// update permission levels to 0
projects = "";
foreach (Project p in hash_projects.Values)
{
if (p.permission_level == 0)
{
if (projects != "")
{
projects += ",";
}
projects += Convert.ToString(p.id);
}
}
if (projects != "")
{
sql.Append(String.Format(@"
update project_user_xref
set pu_permission_level = 0
where pu_user = @us
and pu_project in ({0});" , projects));
}
// update permission levels to 1
projects = "";
foreach (Project p in hash_projects.Values)
{
if (p.permission_level == 1)
{
if (projects != "")
{
projects += ",";
}
projects += Convert.ToString(p.id);
}
}
if (projects != "")
{
sql.Append(String.Format(@"
update project_user_xref
set pu_permission_level = 1
where pu_user = @us
and pu_project in ({0});" , projects));
}
// update permission levels to 2
projects = "";
foreach (Project p in hash_projects.Values)
{
if (p.permission_level == 2)
{
if (projects != "")
{
projects += ",";
}
projects += Convert.ToString(p.id);
}
}
if (projects != "")
{
sql.Append(String.Format(@"
update project_user_xref
set pu_permission_level = 2
where pu_user = @us
and pu_project in ({0});" , projects));
}
// update permission levels to 3
projects = "";
foreach (Project p in hash_projects.Values)
{
if (p.permission_level == 3)
{
if (projects != "")
{
projects += ",";
}
projects += Convert.ToString(p.id);
}
}
if (projects != "")
{
String.Format(@"
update project_user_xref
set pu_permission_level = 3
where pu_user = @us
and pu_project in ({0});" , projects);
}
// apply subscriptions retroactively
if (retroactive.Checked)
{
sql = new SQLString(@"
delete from bug_subscriptions where bs_user = @us;" );
if (auto_subscribe.Checked)
{
sql.Append(@"
insert into bug_subscriptions (bs_bug, bs_user)
select bg_id, @us from bugs;" );
}
else
{
if (auto_subscribe_reported.Checked)
{
sql.Append(@"
insert into bug_subscriptions (bs_bug, bs_user)
select bg_id, @us from bugs where bg_reported_user = @us
and bg_id not in (select bs_bug from bug_subscriptions where bs_user = @us);" );
}
if (auto_subscribe_own.Checked)
{
sql.Append(@"
insert into bug_subscriptions (bs_bug, bs_user)
select bg_id, @us from bugs where bg_assigned_to_user = @us
and bg_id not in (select bs_bug from bug_subscriptions where bs_user = @us);" );
}
if (auto_subscribe_projects != "")
{
sql.Append(String.Format(@"
insert into bug_subscriptions (bs_bug, bs_user)
select bg_id, @us from bugs where bg_project in ({0})
and bg_id not in (select bs_bug from bug_subscriptions where bs_user = us);" , projects));
}
}
}
sql = sql.AddParameterWithValue("us", Convert.ToString(id));
sql = sql.AddParameterWithValue("dpl", Convert.ToString(default_permission_level));
btnet.DbUtil.execute_nonquery(sql);
}
///////////////////////////////////////////////////////////////////////
public static DataTable get_related_users(IIdentity identity, bool force_full_names)
{
SQLString sql;
const string limitUsernameDropdownSql = @"
select isnull(bg_assigned_to_user,0) keep_me
into #temp2
from bugs
union
select isnull(bg_reported_user,0) from bugs
delete from #temp
where us_id not in (select keep_me from #temp2)
drop table #temp2
";
if (Util.get_setting("DefaultPermissionLevel","2") == "0")
{
// only show users who have explicit permission
// for projects that this user has permissions for
sql = new SQLString(@"
/* get related users 1 */
select us_id,
case when @fullnames = 1 then
case when len(isnull(us_firstname,'') + ' ' + isnull(us_lastname,'')) > 1
then isnull(us_firstname,'') + ' ' + isnull(us_lastname,'')
else us_username end
else us_username end us_username,
isnull(us_email,'') us_email,
us_org,
og_external_user
into #temp
from users
inner join orgs on us_org = og_id
where us_id in
(select pu1.pu_user from project_user_xref pu1
where pu1.pu_project in
(select pu2.pu_project from project_user_xref pu2
where pu2.pu_user = @userid
and pu2.pu_permission_level <> 0
)
and pu1.pu_permission_level <> 0
)
if @og_external_user = 1 -- external
and @og_other_orgs_permission_level = 0 -- other orgs
begin
delete from #temp where og_external_user = 1 and us_org <> @userorg
end
");
if (Util.get_setting("LimitUsernameDropdownsInSearch", "0") == "1")
{
sql.Append(limitUsernameDropdownSql);
}
sql.Append(@"
select us_id, us_username, us_email from #temp order by us_username
drop table #temp");
}
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= new SQLString(@"
/* get related users 2 */
select pj_id, us_id,
case when @fullnames = 1 then
case when len(isnull(us_firstname,'') + ' ' + isnull(us_lastname,'')) > 1
then isnull(us_firstname,'') + ' ' + isnull(us_lastname,'')
else us_username end
else us_username end us_username,
isnull(us_email,'') us_email
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 = @userid
)
");
if (Util.get_setting("LimitUsernameDropdownsInSearch", "0") == "1")
{
sql.Append(limitUsernameDropdownSql);
}
sql.Append(@"
if @og_external_user = 1 -- external
and @og_other_orgs_permission_level = 0 -- other orgs
begin
select distinct a.us_id, a.us_username, a.us_email
from #temp a
inner join users b on a.us_id = b.us_id
inner join orgs on b.us_org = og_id
where og_external_user = 0 or b.us_org = @userorg
order by a.us_username
end
else
begin
select distinct us_id, us_username, us_email
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
end
drop table #temp");
}
if (force_full_names || Util.get_setting("UseFullNames", "0") == "1")
{
// true condition
sql = sql.AddParameterWithValue("fullnames", 1);
}
else
{
// false condition
sql = sql.AddParameterWithValue("fullnames", 0);
}
sql = sql.AddParameterWithValue("userid",identity.GetUserId());
sql = sql.AddParameterWithValue("userorg",identity.GetOrganizationId());
sql = sql.AddParameterWithValue("og_external_user", identity.GetIsExternalUser() ? 1 : 0);
sql = sql.AddParameterWithValue("og_other_orgs_permission_level",identity.GetOtherOrgsPermissionLevels());
return btnet.DbUtil.get_dataset(sql).Tables[0];
}
public static int copy_user(
string username,
string email,
string firstname,
string lastname,
string signature,
int salt,
string password,
string template_username,
bool use_domain_as_org_name)
{
// get all the org columns
btnet.Util.write_to_log("copy_user creating " + username + " from template user " + template_username);
StringBuilder org_columns = new StringBuilder();
SQLString sql = new SQLString("");
if (use_domain_as_org_name)
{
sql.Append(@" /* get org cols */
select sc.name
from syscolumns sc
inner join sysobjects so on sc.id = so.id
where so.name = 'orgs'
and sc.name not in ('og_id', 'og_name', 'og_domain')");
DataSet ds = btnet.DbUtil.get_dataset(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
org_columns.Append(",");
org_columns.Append("[");
org_columns.Append(Convert.ToString(dr["name"]));
org_columns.Append("]");
}
}
sql = new SQLString(@"
/* copy user */
declare @template_user_id int
declare @template_org_id int
select @template_user_id = us_id,
@template_org_id = us_org
from users where us_username = @template_user
declare @org_id int
set @org_id = -1
IF @use_domain_as_org_name = 1
BEGIN
select @org_id = og_id from orgs where og_domain = @domain
IF @org_id = -1
BEGIN
insert into orgs
(
og_name,
og_domain
@ORG_COLUMNS
)
select
@domain,
@domain
@ORG_COLUMNS
from orgs where og_id = @template_org_id
select @org_id = scope_identity()
END
END
declare @new_user_id int
set @new_user_id = -1
IF NOT EXISTS (SELECT us_id FROM users WHERE us_username = @username)
BEGIN
insert into users
(us_username, us_email, us_firstname, us_lastname, us_signature, us_salt, us_password,
us_default_query,
us_enable_notifications,
us_auto_subscribe,
us_auto_subscribe_own_bugs,
us_auto_subscribe_reported_bugs,
us_send_notifications_to_self,
us_active,
us_bugs_per_page,
us_forced_project,
us_reported_notifications,
us_assigned_notifications,
us_subscribed_notifications,
us_use_fckeditor,
us_enable_bug_list_popups,
us_org)
select
@username, @email, @firstname, @lastname, @signature, @salt, @password,
us_default_query,
us_enable_notifications,
us_auto_subscribe,
us_auto_subscribe_own_bugs,
us_auto_subscribe_reported_bugs,
us_send_notifications_to_self,
1, -- active
us_bugs_per_page,
us_forced_project,
us_reported_notifications,
us_assigned_notifications,
us_subscribed_notifications,
us_use_fckeditor,
us_enable_bug_list_popups,
case when @org_id = -1 then us_org else @org_id end
from users where us_id = @template_user_id
select @new_user_id = scope_identity()
insert into project_user_xref
(pu_project, pu_user, pu_auto_subscribe, pu_permission_level, pu_admin)
select pu_project, @new_user_id, pu_auto_subscribe, pu_permission_level, pu_admin
from project_user_xref
where pu_user = @template_user_id
select @new_user_id
END
");
sql = sql.AddParameterWithValue("username", username);
sql = sql.AddParameterWithValue("email", email);
sql = sql.AddParameterWithValue("firstname", firstname);
sql = sql.AddParameterWithValue("lastname", lastname);
sql = sql.AddParameterWithValue("signature", signature);
sql = sql.AddParameterWithValue("salt", Convert.ToString(salt));
sql = sql.AddParameterWithValue("password", password);
sql = sql.AddParameterWithValue("template_user", template_username);
sql = sql.AddParameterWithValue("use_domain_as_org_name", Convert.ToString(use_domain_as_org_name ? "1" : "0"));
string[] email_parts = email.Split('@');
if (email_parts.Length == 2)
{
sql = sql.AddParameterWithValue("domain", email_parts[1]);
}
else
{
sql = sql.AddParameterWithValue("domain", email);
}
sql = sql.AddParameterWithValue("ORG_COLUMNS", org_columns.ToString());
return(Convert.ToInt32(btnet.DbUtil.execute_scalar(sql)));
}
public static int copy_user(
string username,
string email,
string firstname,
string lastname,
string signature,
int salt,
string password,
string template_username,
bool use_domain_as_org_name)
{
// get all the org columns
btnet.Util.write_to_log("copy_user creating " + username + " from template user " + template_username);
StringBuilder org_columns = new StringBuilder();
SQLString sql = new SQLString("");
if (use_domain_as_org_name)
{
sql.Append(@" /* get org cols */
select sc.name
from syscolumns sc
inner join sysobjects so on sc.id = so.id
where so.name = 'orgs'
and sc.name not in ('og_id', 'og_name', 'og_domain')");
DataSet ds = btnet.DbUtil.get_dataset(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
org_columns.Append(",");
org_columns.Append("[");
org_columns.Append(Convert.ToString(dr["name"]));
org_columns.Append("]");
}
}
sql = new SQLString(@"
/* copy user */
declare @template_user_id int
declare @template_org_id int
select @template_user_id = us_id,
@template_org_id = us_org
from users where us_username = @template_user
declare @org_id int
set @org_id = -1
IF @use_domain_as_org_name = 1
BEGIN
select @org_id = og_id from orgs where og_domain = @domain
IF @org_id = -1
BEGIN
insert into orgs
(
og_name,
og_domain
@ORG_COLUMNS
)
select
@domain,
@domain
@ORG_COLUMNS
from orgs where og_id = @template_org_id
select @org_id = scope_identity()
END
END
declare @new_user_id int
set @new_user_id = -1
IF NOT EXISTS (SELECT us_id FROM users WHERE us_username = @username)
BEGIN
insert into users
(us_username, us_email, us_firstname, us_lastname, us_signature, us_salt, us_password,
us_default_query,
us_enable_notifications,
us_auto_subscribe,
us_auto_subscribe_own_bugs,
us_auto_subscribe_reported_bugs,
us_send_notifications_to_self,
us_active,
us_bugs_per_page,
us_forced_project,
us_reported_notifications,
us_assigned_notifications,
us_subscribed_notifications,
us_use_fckeditor,
us_enable_bug_list_popups,
us_org)
select
@username, @email, @firstname, @lastname, @signature, @salt, @password,
us_default_query,
us_enable_notifications,
us_auto_subscribe,
us_auto_subscribe_own_bugs,
us_auto_subscribe_reported_bugs,
us_send_notifications_to_self,
1, -- active
us_bugs_per_page,
us_forced_project,
us_reported_notifications,
us_assigned_notifications,
us_subscribed_notifications,
us_use_fckeditor,
us_enable_bug_list_popups,
case when @org_id = -1 then us_org else @org_id end
from users where us_id = @template_user_id
select @new_user_id = scope_identity()
insert into project_user_xref
(pu_project, pu_user, pu_auto_subscribe, pu_permission_level, pu_admin)
select pu_project, @new_user_id, pu_auto_subscribe, pu_permission_level, pu_admin
from project_user_xref
where pu_user = @template_user_id
select @new_user_id
END
");
sql = sql.AddParameterWithValue("username", username);
sql = sql.AddParameterWithValue("email", email);
sql = sql.AddParameterWithValue("firstname", firstname);
sql = sql.AddParameterWithValue("lastname", lastname);
sql = sql.AddParameterWithValue("signature", signature);
sql = sql.AddParameterWithValue("salt", Convert.ToString(salt));
sql = sql.AddParameterWithValue("password", password);
sql = sql.AddParameterWithValue("template_user", template_username);
sql = sql.AddParameterWithValue("use_domain_as_org_name", Convert.ToString(use_domain_as_org_name ? "1" : "0"));
string[] email_parts = email.Split('@');
if (email_parts.Length == 2)
{
sql = sql.AddParameterWithValue("domain", email_parts[1]);
}
else
{
sql = sql.AddParameterWithValue("domain", email);
}
sql = sql.AddParameterWithValue("ORG_COLUMNS", org_columns.ToString());
return Convert.ToInt32(btnet.DbUtil.execute_scalar(sql));
}
///////////////////////////////////////////////////////////////////////
public static DataRow get_bug_datarow(
int bugid,
IIdentity identity)
{
var sql = new SQLString(@" /* get_bug_datarow */");
if (btnet.Util.get_setting("EnableSeen", "0") == "1")
{
sql.Append(@"
if not exists (select bu_bug from bug_user where bu_bug = @id and bu_user = @this_usid)
insert into bug_user (bu_bug, bu_user, bu_flag, bu_seen, bu_vote) values(@id, @this_usid, 0, 1, 0)
update bug_user set bu_seen = 1, bu_seen_datetime = getdate() where bu_bug = @id and bu_user = @this_usid and bu_seen <> 1");
}
sql.Append(@"
declare @svn_revisions int
declare @git_commits int
declare @hg_revisions int
declare @tasks int
declare @related int;
set @svn_revisions = 0
set @git_commits = 0
set @hg_revisions = 0
set @tasks = 0
set @related = 0");
if (btnet.Util.get_setting("EnableSubversionIntegration", "0") == "1")
{
sql.Append(@"
select @svn_revisions = count(1)
from svn_affected_paths
inner join svn_revisions on svnap_svnrev_id = svnrev_id
where svnrev_bug = @id;");
}
if (btnet.Util.get_setting("EnableGitIntegration", "0") == "1")
{
sql.Append(@"
select @git_commits = count(1)
from git_affected_paths
inner join git_commits on gitap_gitcom_id = gitcom_id
where gitcom_bug = @id;");
}
if (btnet.Util.get_setting("EnableMercurialIntegration", "0") == "1")
{
sql.Append(@"
select @hg_revisions = count(1)
from hg_affected_paths
inner join hg_revisions on hgap_hgrev_id = hgrev_id
where hgrev_bug = @id;");
}
if (btnet.Util.get_setting("EnableTasks", "0") == "1")
{
sql.Append(@"
select @tasks = count(1)
from bug_tasks
where tsk_bug = @id;");
}
if (btnet.Util.get_setting("EnableRelationships", "0") == "1")
{
sql.Append(@"
select @related = count(1)
from bug_relationships
where re_bug1 = @id;");
}
sql.Append(@"
select bg_id [id],
bg_short_desc [short_desc],
isnull(bg_tags,'') [bg_tags],
isnull(ru.us_username,'[deleted user]') [reporter],
isnull(ru.us_email,'') [reporter_email],
case rtrim(ru.us_firstname)
when null then isnull(ru.us_lastname, '')
when '' then isnull(ru.us_lastname, '')
else isnull(ru.us_lastname + ', ' + ru.us_firstname,'')
end [reporter_fullname],
bg_reported_date [reported_date],
datediff(s,bg_reported_date,getdate()) [seconds_ago],
isnull(lu.us_username,'') [last_updated_user],
case rtrim(lu.us_firstname)
when null then isnull(lu.us_lastname, '')
when '' then isnull(lu.us_lastname, '')
else isnull(lu.us_lastname + ', ' + lu.us_firstname,'')
end [last_updated_fullname],
bg_last_updated_date [last_updated_date],
isnull(bg_project,0) [project],
isnull(pj_name,'[no project]') [current_project],
isnull(bg_org,0) [organization],
isnull(bugorg.og_name,'') [og_name],
isnull(bg_category,0) [category],
isnull(ct_name,'') [category_name],
isnull(bg_priority,0) [priority],
isnull(pr_name,'') [priority_name],
isnull(bg_status,0) [status],
isnull(st_name,'') [status_name],
isnull(bg_user_defined_attribute,0) [udf],
isnull(udf_name,'') [udf_name],
isnull(bg_assigned_to_user,0) [assigned_to_user],
isnull(asg.us_username,'[not assigned]') [assigned_to_username],
case rtrim(asg.us_firstname)
when null then isnull(asg.us_lastname, '[not assigned]')
when '' then isnull(asg.us_lastname, '[not assigned]')
else isnull(asg.us_lastname + ', ' + asg.us_firstname,'[not assigned]')
end [assigned_to_fullname],
isnull(bs_user,0) [subscribed],
case
when
@this_org <> bg_org
and userorg.og_other_orgs_permission_level < 2
and userorg.og_other_orgs_permission_level < isnull(pu_permission_level,@dpl)
then userorg.og_other_orgs_permission_level
else
isnull(pu_permission_level,@dpl)
end [pu_permission_level],
isnull(bg_project_custom_dropdown_value1,'') [bg_project_custom_dropdown_value1],
isnull(bg_project_custom_dropdown_value2,'') [bg_project_custom_dropdown_value2],
isnull(bg_project_custom_dropdown_value3,'') [bg_project_custom_dropdown_value3],
@related [relationship_cnt],
@svn_revisions [svn_revision_cnt],
@git_commits [git_commit_cnt],
@hg_revisions [hg_commit_cnt],
@tasks [task_cnt],
getdate() [snapshot_timestamp]
from bugs
inner join users this_user on us_id = @this_usid
inner join orgs userorg on this_user.us_org = userorg.og_id
left outer join user_defined_attribute on bg_user_defined_attribute = udf_id
left outer join projects on bg_project = pj_id
left outer join orgs bugorg on bg_org = bugorg.og_id
left outer join categories on bg_category = ct_id
left outer join priorities on bg_priority = pr_id
left outer join statuses on bg_status = st_id
left outer join users asg on bg_assigned_to_user = asg.us_id
left outer join users ru on bg_reported_user = ru.us_id
left outer join users lu on bg_last_updated_user = lu.us_id
left outer join bug_subscriptions on bs_bug = bg_id and bs_user = @this_usid
left outer join project_user_xref on pj_id = pu_project
and pu_user = @this_usid
where bg_id = @id");
sql = sql.AddParameterWithValue("id", Convert.ToString(bugid));
sql = sql.AddParameterWithValue("this_usid", Convert.ToString(identity.GetUserId()));
sql = sql.AddParameterWithValue("this_org", Convert.ToString(identity.GetOrganizationId()));
sql = sql.AddParameterWithValue("dpl", Util.get_setting("DefaultPermissionLevel", "2"));
return(btnet.DbUtil.get_datarow(sql));
}
///////////////////////////////////////////////////////////////////////
void load_users_dropdowns(int bugid)
{
// What's selected now? Save it before we refresh the dropdown.
string current_value = "";
if (IsPostBack)
{
current_value = assigned_to.SelectedItem.Value;
}
sql = new SQLString(@"
declare @project int
declare @assigned_to int
select @project = bg_project, @assigned_to = bg_assigned_to_user from bugs where bg_id = @bg_id");
// Load the user dropdown, which changes per project
// Only users explicitly allowed will be listed
if (Util.get_setting("DefaultPermissionLevel", "2") == "0")
{
sql.Append(@"
/* users this project */ select us_id, case when @fullnames=1 then us_lastname + ', ' + us_firstname else us_username end us_username
from users
inner join orgs on us_org = og_id
where us_active = 1
and og_can_be_assigned_to = 1
and (@og_other_orgs_permission_level <> 0 or @og_id = og_id or og_external_user = 0)
and us_id in
(select pu_user from project_user_xref
where pu_project = @project
and pu_permission_level <> 0)
order by us_username; ");
}
// Only users explictly DISallowed will be omitted
else
{
sql.Append(@"
/* users this project */ select us_id, case when @fullnames =1 then us_lastname + ', ' + us_firstname else us_username end us_username
from users
inner join orgs on us_org = og_id
where us_active = 1
and og_can_be_assigned_to = 1
and (@og_other_orgs_permission_level <> 0 or @og_id = og_id or og_external_user = 0)
and us_id not in
(select pu_user from project_user_xref
where pu_project = @project
and pu_permission_level = 0)
order by us_username; ");
}
sql.Append("\nselect st_id, st_name from statuses order by st_sort_seq, st_name");
sql.Append("\nselect isnull(@assigned_to,0) ");
sql = sql.AddParameterWithValue("og_id", User.Identity.GetOrganizationId());
sql = sql.AddParameterWithValue("og_other_orgs_permission_level", User.Identity.GetOtherOrgsPermissionLevels());
sql = sql.AddParameterWithValue("bg_id", bugid);
if (Util.get_setting("UseFullNames", "0") == "0")
{
// false condition
sql = sql.AddParameterWithValue("fullnames", 0);
}
else
{
// true condition
sql = sql.AddParameterWithValue("fullnames", 1);
}
DataSet dataSet = DbUtil.get_dataset(sql);
assigned_to.DataSource = new DataView((DataTable)dataSet.Tables[0]);
assigned_to.DataTextField = "us_username";
assigned_to.DataValueField = "us_id";
assigned_to.DataBind();
assigned_to.Items.Insert(0, new ListItem("[not assigned]", "0"));
status.DataSource = new DataView((DataTable)dataSet.Tables[1]);
status.DataTextField = "st_name";
status.DataValueField = "st_id";
status.DataBind();
status.Items.Insert(0, new ListItem("[no status]", "0"));
// by default, assign the entry to the same user to whom the bug is assigned to?
// or should it be assigned to the logged in user?
if (tsk_id == 0)
{
int default_assigned_to_user = (int)dataSet.Tables[2].Rows[0][0];
ListItem li = assigned_to.Items.FindByValue(Convert.ToString(default_assigned_to_user));
if (li != null)
{
li.Selected = true;
}
}
}
///////////////////////////////////////////////////////////////////////
public static int insert_comment(
int bugid,
int this_usid,
string comment_formated,
string comment_search,
string from,
string cc,
string content_type,
bool internal_only)
{
if (comment_formated != "")
{
var sql = new SQLString(@"
declare @now datetime
set @now = getdate()
insert into bug_posts
(bp_bug, bp_user, bp_date, bp_comment, bp_comment_search, bp_email_from, bp_email_cc, bp_type, bp_content_type,
bp_hidden_from_external_users)
values(
@id,
@us,
@now,
@comment_formatted,
@comment_search,
@from,
@cc,
@type,
@content_type,
@internal)
select scope_identity();");
if (from != null)
{
// Update the bugs timestamp here.
// We don't do it unconditionally because it would mess up the locking.
// The edit_bug.aspx page gets its snapshot timestamp from the update of the bug
// row, not the comment row, so updating the bug again would confuse it.
sql.Append(@"update bugs
set bg_last_updated_date = @now,
bg_last_updated_user = @us
where bg_id = @id");
sql = sql.AddParameterWithValue("@from", from);
sql = sql.AddParameterWithValue("@type", "received"); // received email
}
else
{
sql = sql.AddParameterWithValue("@from", null);
sql = sql.AddParameterWithValue("@type", "comment"); // bug comment
}
sql = sql.AddParameterWithValue("@id", Convert.ToString(bugid));
sql = sql.AddParameterWithValue("@us", Convert.ToString(this_usid));
sql = sql.AddParameterWithValue("@comment_formatted", comment_formated);
sql = sql.AddParameterWithValue("@comment_search", comment_search);
sql = sql.AddParameterWithValue("@content_type", content_type);
if (cc == null)
{
cc = "";
}
sql = sql.AddParameterWithValue("@cc", cc);
sql = sql.AddParameterWithValue("@internal", btnet.Util.bool_to_string(internal_only));
return(Convert.ToInt32(btnet.DbUtil.execute_scalar(sql)));
}
else
{
return(0);
}
}
///////////////////////////////////////////////////////////////////////
public static DataRow get_bug_datarow(
int bugid,
IIdentity identity)
{
var sql = new SQLString(@" /* get_bug_datarow */");
if (btnet.Util.get_setting("EnableSeen", "0") == "1")
{
sql.Append(@"
if not exists (select bu_bug from bug_user where bu_bug = @id and bu_user = @this_usid)
insert into bug_user (bu_bug, bu_user, bu_flag, bu_seen, bu_vote) values(@id, @this_usid, 0, 1, 0)
update bug_user set bu_seen = 1, bu_seen_datetime = getdate() where bu_bug = @id and bu_user = @this_usid and bu_seen <> 1");
}
sql.Append(@"
declare @svn_revisions int
declare @git_commits int
declare @hg_revisions int
declare @tasks int
declare @related int;
set @svn_revisions = 0
set @git_commits = 0
set @hg_revisions = 0
set @tasks = 0
set @related = 0");
if (btnet.Util.get_setting("EnableSubversionIntegration", "0") == "1")
{
sql.Append(@"
select @svn_revisions = count(1)
from svn_affected_paths
inner join svn_revisions on svnap_svnrev_id = svnrev_id
where svnrev_bug = @id;");
}
if (btnet.Util.get_setting("EnableGitIntegration", "0") == "1")
{
sql.Append(@"
select @git_commits = count(1)
from git_affected_paths
inner join git_commits on gitap_gitcom_id = gitcom_id
where gitcom_bug = @id;");
}
if (btnet.Util.get_setting("EnableMercurialIntegration", "0") == "1")
{
sql.Append(@"
select @hg_revisions = count(1)
from hg_affected_paths
inner join hg_revisions on hgap_hgrev_id = hgrev_id
where hgrev_bug = @id;");
}
if (btnet.Util.get_setting("EnableTasks", "0") == "1")
{
sql.Append(@"
select @tasks = count(1)
from bug_tasks
where tsk_bug = @id;");
}
if (btnet.Util.get_setting("EnableRelationships", "0") == "1")
{
sql.Append(@"
select @related = count(1)
from bug_relationships
where re_bug1 = @id;");
}
sql.Append(@"
select bg_id [id],
bg_short_desc [short_desc],
isnull(bg_tags,'') [bg_tags],
isnull(ru.us_username,'[deleted user]') [reporter],
isnull(ru.us_email,'') [reporter_email],
case rtrim(ru.us_firstname)
when null then isnull(ru.us_lastname, '')
when '' then isnull(ru.us_lastname, '')
else isnull(ru.us_lastname + ', ' + ru.us_firstname,'')
end [reporter_fullname],
bg_reported_date [reported_date],
datediff(s,bg_reported_date,getdate()) [seconds_ago],
isnull(lu.us_username,'') [last_updated_user],
case rtrim(lu.us_firstname)
when null then isnull(lu.us_lastname, '')
when '' then isnull(lu.us_lastname, '')
else isnull(lu.us_lastname + ', ' + lu.us_firstname,'')
end [last_updated_fullname],
bg_last_updated_date [last_updated_date],
isnull(bg_project,0) [project],
isnull(pj_name,'[no project]') [current_project],
isnull(bg_org,0) [organization],
isnull(bugorg.og_name,'') [og_name],
isnull(bg_category,0) [category],
isnull(ct_name,'') [category_name],
isnull(bg_priority,0) [priority],
isnull(pr_name,'') [priority_name],
isnull(bg_status,0) [status],
isnull(st_name,'') [status_name],
isnull(bg_user_defined_attribute,0) [udf],
isnull(udf_name,'') [udf_name],
isnull(bg_assigned_to_user,0) [assigned_to_user],
isnull(asg.us_username,'[not assigned]') [assigned_to_username],
case rtrim(asg.us_firstname)
when null then isnull(asg.us_lastname, '[not assigned]')
when '' then isnull(asg.us_lastname, '[not assigned]')
else isnull(asg.us_lastname + ', ' + asg.us_firstname,'[not assigned]')
end [assigned_to_fullname],
isnull(bs_user,0) [subscribed],
case
when
@this_org <> bg_org
and userorg.og_other_orgs_permission_level < 2
and userorg.og_other_orgs_permission_level < isnull(pu_permission_level,@dpl)
then userorg.og_other_orgs_permission_level
else
isnull(pu_permission_level,@dpl)
end [pu_permission_level],
isnull(bg_project_custom_dropdown_value1,'') [bg_project_custom_dropdown_value1],
isnull(bg_project_custom_dropdown_value2,'') [bg_project_custom_dropdown_value2],
isnull(bg_project_custom_dropdown_value3,'') [bg_project_custom_dropdown_value3],
@related [relationship_cnt],
@svn_revisions [svn_revision_cnt],
@git_commits [git_commit_cnt],
@hg_revisions [hg_commit_cnt],
@tasks [task_cnt],
getdate() [snapshot_timestamp]
from bugs
inner join users this_user on us_id = @this_usid
inner join orgs userorg on this_user.us_org = userorg.og_id
left outer join user_defined_attribute on bg_user_defined_attribute = udf_id
left outer join projects on bg_project = pj_id
left outer join orgs bugorg on bg_org = bugorg.og_id
left outer join categories on bg_category = ct_id
left outer join priorities on bg_priority = pr_id
left outer join statuses on bg_status = st_id
left outer join users asg on bg_assigned_to_user = asg.us_id
left outer join users ru on bg_reported_user = ru.us_id
left outer join users lu on bg_last_updated_user = lu.us_id
left outer join bug_subscriptions on bs_bug = bg_id and bs_user = @this_usid
left outer join project_user_xref on pj_id = pu_project
and pu_user = @this_usid
where bg_id = @id");
sql = sql.AddParameterWithValue("id", Convert.ToString(bugid));
sql = sql.AddParameterWithValue("this_usid", Convert.ToString(identity.GetUserId()));
sql = sql.AddParameterWithValue("this_org", Convert.ToString(identity.GetOrganizationId()));
sql = sql.AddParameterWithValue("dpl", Util.get_setting("DefaultPermissionLevel", "2"));
return btnet.DbUtil.get_datarow(sql);
}
private void ApplyWhereClause(SQLString sqlString, BugQueryFilter[] filters)
{
if (filters != null && filters.Any())
{
sqlString.Append(" WHERE ");
List<string> conditions = new List<string>();
foreach (var filter in filters)
{
if (!_columnNames.Contains(filter.Column))
{
throw new ArgumentException("Invalid filter column: {0}", filter.Column);
}
string parameterName = GetCleanParameterName(filter.Column);
conditions.Add(string.Format("[{0}] = @{1}", filter.Column, parameterName));
}
sqlString.Append(string.Join(" AND ", conditions));
}
}
///////////////////////////////////////////////////////////////////////
public static NewIds insert_bug(string short_desc, IIdentity identity, string tags, int projectid, int orgid, int categoryid, int priorityid, int statusid, int assigned_to_userid, int udfid, string comment_formated, string comment_search, string @from, string cc, string content_type, bool internal_only, SortedDictionary<string, string> hash_custom_cols, bool send_notifications)
{
if (short_desc.Trim() == "")
{
short_desc = "[No Description]";
}
if (assigned_to_userid == 0)
{
assigned_to_userid = btnet.Util.get_default_user(projectid);
}
var sql = new SQLString(@"insert into bugs
(bg_short_desc,
bg_tags,
bg_reported_user,
bg_last_updated_user,
bg_reported_date,
bg_last_updated_date,
bg_project,
bg_org,
bg_category,
bg_priority,
bg_status,
bg_assigned_to_user,
bg_user_defined_attribute)
values (@short_desc, @tags, @reported_user, @reported_user, getdate(), getdate(),
@project, @org,
@category, @priority, @status, @assigned_user, @udf)");
sql = sql.AddParameterWithValue("@short_desc", short_desc);
sql = sql.AddParameterWithValue("@tags", tags);
sql = sql.AddParameterWithValue("@reported_user", Convert.ToString(identity.GetUserId()));
sql = sql.AddParameterWithValue("@project", Convert.ToString(projectid));
sql = sql.AddParameterWithValue("@org", Convert.ToString(orgid));
sql = sql.AddParameterWithValue("@category", Convert.ToString(categoryid));
sql = sql.AddParameterWithValue("@priority", Convert.ToString(priorityid));
sql = sql.AddParameterWithValue("@status", Convert.ToString(statusid));
sql = sql.AddParameterWithValue("@assigned_user", Convert.ToString(assigned_to_userid));
sql = sql.AddParameterWithValue("@udf", Convert.ToString(udfid));
//TODO: Add custom columns
sql.Append("\nselect scope_identity()");
int bugid = Convert.ToInt32(btnet.DbUtil.execute_scalar(sql));
int postid = btnet.Bug.insert_comment(
bugid,
identity.GetUserId(),
comment_formated,
comment_search,
from,
cc,
content_type,
internal_only);
btnet.Bug.auto_subscribe(bugid);
if (send_notifications)
{
btnet.Bug.send_notifications(btnet.Bug.INSERT, bugid, identity);
}
return new NewIds(bugid, postid);
}
///////////////////////////////////////////////////////////////////////
void on_update()
{
Boolean good = validate();
if (good)
{
sql = new SQLString(@"update users set
us_firstname = @fn,
us_lastname = @ln,
us_bugs_per_page = @bp,
us_use_fckeditor = @fk,
us_enable_bug_list_popups = @pp,
us_email = @em,
us_enable_notifications = @en,
us_send_notifications_to_self = @ss,
us_reported_notifications = @rn,
us_assigned_notifications = @an,
us_subscribed_notifications = @sn,
us_auto_subscribe = @as,
us_auto_subscribe_own_bugs = @ao,
us_auto_subscribe_reported_bugs = @ar,
us_default_query = @dq,
us_signature = @sg
where us_id = @id" );
sql = sql.AddParameterWithValue("fn", firstname.Value);
sql = sql.AddParameterWithValue("ln", lastname.Value);
sql = sql.AddParameterWithValue("bp", bugs_per_page.Value);
sql = sql.AddParameterWithValue("fk", Util.bool_to_string(use_fckeditor.Checked));
sql = sql.AddParameterWithValue("pp", Util.bool_to_string(enable_popups.Checked));
sql = sql.AddParameterWithValue("em", email.Value);
sql = sql.AddParameterWithValue("en", Util.bool_to_string(enable_notifications.Checked));
sql = sql.AddParameterWithValue("ss", Util.bool_to_string(send_to_self.Checked));
sql = sql.AddParameterWithValue("rn", reported_notifications.SelectedItem.Value);
sql = sql.AddParameterWithValue("an", assigned_notifications.SelectedItem.Value);
sql = sql.AddParameterWithValue("sn", subscribed_notifications.SelectedItem.Value);
sql = sql.AddParameterWithValue("as", Util.bool_to_string(auto_subscribe.Checked));
sql = sql.AddParameterWithValue("ao", Util.bool_to_string(auto_subscribe_own.Checked));
sql = sql.AddParameterWithValue("ar", Util.bool_to_string(auto_subscribe_reported.Checked));
sql = sql.AddParameterWithValue("dq", query.SelectedItem.Value);
sql = sql.AddParameterWithValue("sg", signature.InnerText);
sql = sql.AddParameterWithValue("id", Convert.ToString(id));
// update user
btnet.DbUtil.execute_nonquery(sql);
// update the password
if (pw.Value != "")
{
btnet.Util.update_user_password(id, pw.Value);
}
// Now update project_user_xref
// First turn everything off, then turn selected ones on.
sql = new SQLString(@"update project_user_xref
set pu_auto_subscribe = 0 where pu_user = @id" );
sql = sql.AddParameterWithValue("id", Convert.ToString(id));
btnet.DbUtil.execute_nonquery(sql);
// Second see what to turn back on
string projects = "";
foreach (ListItem li in project_auto_subscribe.Items)
{
if (li.Selected)
{
if (projects != "")
{
projects += ",";
}
projects += Convert.ToInt32(li.Value);
}
}
// If we need to turn anything back on
if (projects != "")
{
sql = new SQLString(@"update project_user_xref
set pu_auto_subscribe = 1 where pu_user = @id and pu_project in ($projects)
insert into project_user_xref (pu_project, pu_user, pu_auto_subscribe)
select pj_id, @id, 1
from projects
where pj_id in (projects)
and pj_id not in (select pu_project from project_user_xref where pu_user = @id)" );
sql = sql.AddParameterWithValue("id", Convert.ToString(id));
sql = sql.AddParameterWithValue("projects", projects);
btnet.DbUtil.execute_nonquery(sql);
}
// apply subscriptions retroactively
if (retroactive.Checked)
{
sql = new SQLString(@"delete from bug_subscriptions where bs_user = @id;");
if (auto_subscribe.Checked)
{
sql.Append(@"insert into bug_subscriptions (bs_bug, bs_user)
select bg_id, @id from bugs;" );
}
else
{
if (auto_subscribe_reported.Checked)
{
sql.Append(@"insert into bug_subscriptions (bs_bug, bs_user)
select bg_id, @id from bugs where bg_reported_user = @id
and bg_id not in (select bs_bug from bug_subscriptions where bs_user = @id);" );
}
if (auto_subscribe_own.Checked)
{
sql.Append(@"insert into bug_subscriptions (bs_bug, bs_user)
select bg_id, @id from bugs where bg_assigned_to_user = @id
and bg_id not in (select bs_bug from bug_subscriptions where bs_user = @id);" );
}
if (projects != "")
{
sql.Append(@"insert into bug_subscriptions (bs_bug, bs_user)
select bg_id, @id from bugs where bg_project in (@projects)
and bg_id not in (select bs_bug from bug_subscriptions where bs_user = @id);" );
}
}
sql = sql.AddParameterWithValue("id", Convert.ToString(id));
sql = sql.AddParameterWithValue("projects", projects);
btnet.DbUtil.execute_nonquery(sql);
}
msg.InnerText = "Your settings have been updated.";
}
else
{
msg.InnerText = "Your settings have not been updated.";
}
}
///////////////////////////////////////////////////////////////////////
void load_dropdowns()
{
// only show projects where user has permissions
// 0
sql = new SQLString(@"/* drop downs */ select pj_id, pj_name
from projects
left outer join project_user_xref on pj_id = pu_project
and pu_user = @us
where pj_active = 1
and isnull(pu_permission_level,@dpl) not in (0, 1)
order by pj_name;");
sql = sql.AddParameterWithValue("us", Convert.ToString(User.Identity.GetUserId()));
sql = sql.AddParameterWithValue("dpl", Util.get_setting("DefaultPermissionLevel", "2"));
// 1
sql.Append("\nselect og_id, og_name from orgs where og_active = 1 order by og_name;");
// 2
sql.Append("\nselect ct_id, ct_name from categories order by ct_sort_seq, ct_name;");
// 3
sql.Append("\nselect pr_id, pr_name from priorities order by pr_sort_seq, pr_name;");
// 4
sql.Append("\nselect st_id, st_name from statuses order by st_sort_seq, st_name;");
// 5
sql.Append("\nselect udf_id, udf_name from user_defined_attribute order by udf_sort_seq, udf_name;");
// do a batch of sql statements
DataSet ds_dropdowns = DbUtil.get_dataset(sql);
project.DataSource = ds_dropdowns.Tables[0];
project.DataTextField = "pj_name";
project.DataValueField = "pj_id";
project.DataBind();
if (Util.get_setting("DefaultPermissionLevel", "2") == "2")
{
project.Items.Insert(0, new ListItem("[no project]", "0"));
}
org.DataSource = ds_dropdowns.Tables[1];
org.DataTextField = "og_name";
org.DataValueField = "og_id";
org.DataBind();
org.Items.Insert(0, new ListItem("[no organization]", "0"));
category.DataSource = ds_dropdowns.Tables[2];
category.DataTextField = "ct_name";
category.DataValueField = "ct_id";
category.DataBind();
category.Items.Insert(0, new ListItem("[no category]", "0"));
priority.DataSource = ds_dropdowns.Tables[3];
priority.DataTextField = "pr_name";
priority.DataValueField = "pr_id";
priority.DataBind();
priority.Items.Insert(0, new ListItem("[no priority]", "0"));
status.DataSource = ds_dropdowns.Tables[4];
status.DataTextField = "st_name";
status.DataValueField = "st_id";
status.DataBind();
status.Items.Insert(0, new ListItem("[no status]", "0"));
udf.DataSource = ds_dropdowns.Tables[5];
udf.DataTextField = "udf_name";
udf.DataValueField = "udf_id";
udf.DataBind();
udf.Items.Insert(0, new ListItem("[none]", "0"));
}