///////////////////////////////////////////////////////////////////////
public static DataSet get_dataset(SQLString sql)
{
if (Util.get_setting("LogSqlEnabled", "1") == "1")
{
Util.write_to_log("sql=\n" + sql);
}
DataSet ds = new DataSet();
using (SqlConnection conn = GetConnection())
{
using (SqlDataAdapter da = new SqlDataAdapter( sql.ToString(), conn))
{
da.SelectCommand.Parameters.AddRange(sql.GetParameters().ToArray());
System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
da.Fill(ds);
stopwatch.Stop();
log_stopwatch_time(stopwatch);
conn.Close(); // redundant, but just to be clear
return ds;
}
}
}
public SQLString Append(SQLString toAppend)
{
_value += toAppend.ToString();
foreach (var param in toAppend.GetParameters())
_parameters.Add(param);
return this;
}
public SQLString Append(SQLString toAppend)
{
_value += toAppend.ToString();
foreach (var param in toAppend.GetParameters())
{
_parameters.Add(param);
}
return(this);
}
///////////////////////////////////////////////////////////////////////
public static void apply_post_insert_rules(int bugid)
{
var sql = new SQLString(Util.get_setting("UpdateBugAfterInsertBugAspxSql", ""));
if (!string.IsNullOrEmpty(sql.ToString()))
{
sql = sql.AddParameterWithValue("@BUGID", Convert.ToString(bugid));
btnet.DbUtil.execute_nonquery(sql);
}
}
///////////////////////////////////////////////////////////////////////
public static void execute_nonquery_without_logging(SQLString sql)
{
using (SqlConnection conn = GetConnection())
{
SqlCommand cmd = new SqlCommand(sql.ToString(), conn);
cmd.Parameters.AddRange(sql.GetParameters().ToArray());
cmd.ExecuteNonQuery();
conn.Close(); // redundant, but just to be clear
}
}
///////////////////////////////////////////////////////////////////////
public static void execute_nonquery(SQLString sql)
{
if (Util.get_setting("LogSqlEnabled", "1") == "1")
{
Util.write_to_log("sql=\n" + sql);
}
using (SqlConnection conn = GetConnection())
{
SqlCommand cmd = new SqlCommand(sql.ToString(), conn);
cmd.Parameters.AddRange(sql.GetParameters().ToArray());
cmd.ExecuteNonQuery();
conn.Close(); // redundant, but just to be clear
}
}
///////////////////////////////////////////////////////////////////////
public static object execute_scalar(SQLString sql)
{
if (Util.get_setting("LogSqlEnabled", "1") == "1")
{
Util.write_to_log("sql=\n" + sql);
}
using (SqlConnection conn = GetConnection())
{
object returnValue;
SqlCommand cmd = new SqlCommand(sql.ToString(), conn);
cmd.Parameters.AddRange(sql.GetParameters().ToArray());
returnValue = cmd.ExecuteScalar();
conn.Close(); // redundant, but just to be clear
return returnValue;
}
}
///////////////////////////////////////////////////////////////////////
public static SqlDataReader execute_reader(SQLString sql, CommandBehavior behavior)
{
if (Util.get_setting("LogSqlEnabled", "1") == "1")
{
Util.write_to_log("sql=\n" + sql);
}
SqlConnection conn = GetConnection();
try
{
using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
{
cmd.Parameters.AddRange(sql.GetParameters().ToArray());
return cmd.ExecuteReader(behavior | CommandBehavior.CloseConnection);
}
}
catch
{
conn.Close();
throw;
}
}
///////////////////////////////////////////////////////////////////////
public static void apply_post_insert_rules(int bugid)
{
var sql = new SQLString(Util.get_setting("UpdateBugAfterInsertBugAspxSql", ""));
if (!string.IsNullOrEmpty(sql.ToString()))
{
sql = sql.AddParameterWithValue("@BUGID", Convert.ToString(bugid));
btnet.DbUtil.execute_nonquery(sql);
}
}
///////////////////////////////////////////////////////////////////////
public static SQLString alter_sql_per_project_permissions(SQLString sql, IIdentity identity)
{
int userId = identity.GetUserId();
int organizationId = identity.GetOrganizationId();
string project_permissions_sql;
string dpl = Util.get_setting("DefaultPermissionLevel","2");
if (dpl == "0")
{
project_permissions_sql = @" (bugs.bg_project in (
select pu_project
from project_user_xref
where pu_user = $user
and pu_permission_level > 0)) ";
}
else
{
project_permissions_sql = @" (bugs.bg_project not in (
select pu_project
from project_user_xref
where pu_user = $user
and pu_permission_level = 0)) ";
}
if (identity.GetCanOnlySeeOwnReportedBugs())
{
project_permissions_sql += @"
and bugs.bg_reported_user = $user ";
}
else
{
if (identity.GetOtherOrgsPermissionLevels() == 0)
{
project_permissions_sql += @"
and bugs.bg_org = $user.org ";
}
}
project_permissions_sql
= project_permissions_sql.Replace("$user.org",Convert.ToString(organizationId));
project_permissions_sql
= project_permissions_sql.Replace("$user",Convert.ToString(userId));
// Figure out where to alter sql for project permissions
// I've tried lots of different schemes over the years....
int alter_here_pos = sql.ToString().IndexOf("$ALTER_HERE"); // places - can be multiple - are explicitly marked
if (alter_here_pos != -1)
{
return new SQLString(sql.ToString().Replace("$ALTER_HERE", "/* ALTER_HERE */ " + project_permissions_sql), sql.GetParameters());
}
else
{
string bug_sql;
var rawSQL = sql.ToString();
int where_pos = rawSQL.IndexOf("WhErE"); // first look for a "special" where, case sensitive, in case there are multiple where's to choose from
if (where_pos == -1)
where_pos = rawSQL.ToUpper().IndexOf("WHERE");
int order_pos = rawSQL.IndexOf("/*ENDWHR*/"); // marker for end of the where statement
if (order_pos == -1)
order_pos = rawSQL.ToUpper().LastIndexOf("ORDER BY");
if (order_pos < where_pos)
order_pos = -1; // ignore an order by that occurs in a subquery, for example
if (where_pos != -1 && order_pos != -1)
{
// both WHERE and ORDER BY clauses
bug_sql = rawSQL.Substring(0, where_pos + 5)
+ " /* altered - both */ ( "
+ rawSQL.Substring(where_pos + 5, order_pos - (where_pos + 5))
+ " ) AND ( "
+ project_permissions_sql
+ " ) "
+ rawSQL.Substring(order_pos);
}
else if (order_pos == -1 && where_pos == -1)
{
// Neither
bug_sql = rawSQL + " /* altered - neither */ WHERE " + project_permissions_sql;
}
else if (order_pos == -1)
{
// WHERE, without order
bug_sql = rawSQL.Substring(0, where_pos + 5)
+ " /* altered - just where */ ( "
+ rawSQL.Substring(where_pos + 5)
+ " ) AND ( "
+ project_permissions_sql + " )";
}
else
{
// ORDER BY, without WHERE
bug_sql = rawSQL.Substring(0, order_pos)
+ " /* altered - just order by */ WHERE "
+ project_permissions_sql
+ rawSQL.Substring(order_pos);
}
return new SQLString(bug_sql, sql.GetParameters());
}
}
///////////////////////////////////////////////////////////////////////
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");
}
}
///////////////////////////////////////////////////////////////////////
public static SQLString alter_sql_per_project_permissions(SQLString sql, IIdentity identity)
{
int userId = identity.GetUserId();
int organizationId = identity.GetOrganizationId();
string project_permissions_sql;
string dpl = Util.get_setting("DefaultPermissionLevel", "2");
if (dpl == "0")
{
project_permissions_sql = @" (bugs.bg_project in (
select pu_project
from project_user_xref
where pu_user = $user
and pu_permission_level > 0)) " ;
}
else
{
project_permissions_sql = @" (bugs.bg_project not in (
select pu_project
from project_user_xref
where pu_user = $user
and pu_permission_level = 0)) " ;
}
if (identity.GetCanOnlySeeOwnReportedBugs())
{
project_permissions_sql += @"
and bugs.bg_reported_user = $user " ;
}
else
{
if (identity.GetOtherOrgsPermissionLevels() == 0)
{
project_permissions_sql += @"
and bugs.bg_org = $user.org " ;
}
}
project_permissions_sql
= project_permissions_sql.Replace("$user.org", Convert.ToString(organizationId));
project_permissions_sql
= project_permissions_sql.Replace("$user", Convert.ToString(userId));
// Figure out where to alter sql for project permissions
// I've tried lots of different schemes over the years....
int alter_here_pos = sql.ToString().IndexOf("$ALTER_HERE"); // places - can be multiple - are explicitly marked
if (alter_here_pos != -1)
{
return(new SQLString(sql.ToString().Replace("$ALTER_HERE", "/* ALTER_HERE */ " + project_permissions_sql), sql.GetParameters()));
}
else
{
string bug_sql;
var rawSQL = sql.ToString();
int where_pos = rawSQL.IndexOf("WhErE"); // first look for a "special" where, case sensitive, in case there are multiple where's to choose from
if (where_pos == -1)
{
where_pos = rawSQL.ToUpper().IndexOf("WHERE");
}
int order_pos = rawSQL.IndexOf("/*ENDWHR*/"); // marker for end of the where statement
if (order_pos == -1)
{
order_pos = rawSQL.ToUpper().LastIndexOf("ORDER BY");
}
if (order_pos < where_pos)
{
order_pos = -1; // ignore an order by that occurs in a subquery, for example
}
if (where_pos != -1 && order_pos != -1)
{
// both WHERE and ORDER BY clauses
bug_sql = rawSQL.Substring(0, where_pos + 5)
+ " /* altered - both */ ( "
+ rawSQL.Substring(where_pos + 5, order_pos - (where_pos + 5))
+ " ) AND ( "
+ project_permissions_sql
+ " ) "
+ rawSQL.Substring(order_pos);
}
else if (order_pos == -1 && where_pos == -1)
{
// Neither
bug_sql = rawSQL + " /* altered - neither */ WHERE " + project_permissions_sql;
}
else if (order_pos == -1)
{
// WHERE, without order
bug_sql = rawSQL.Substring(0, where_pos + 5)
+ " /* altered - just where */ ( "
+ rawSQL.Substring(where_pos + 5)
+ " ) AND ( "
+ project_permissions_sql + " )";
}
else
{
// ORDER BY, without WHERE
bug_sql = rawSQL.Substring(0, order_pos)
+ " /* altered - just order by */ WHERE "
+ project_permissions_sql
+ rawSQL.Substring(order_pos);
}
return(new SQLString(bug_sql, sql.GetParameters()));
}
}
