///////////////////////////////////////////////////////////////////////
static DataSet get_text_custom_cols()
{
DataSet ds_custom_fields = DbUtil.get_dataset(@"
/* get searchable cols */
select sc.name
from syscolumns sc
inner join systypes st on st.xusertype = sc.xusertype
inner join sysobjects so on sc.id = so.id
where so.name = 'bugs'
and st.[name] <> 'sysname'
and sc.name not in ('rowguid',
'bg_id',
'bg_short_desc',
'bg_reported_user',
'bg_reported_date',
'bg_project',
'bg_org',
'bg_category',
'bg_priority',
'bg_status',
'bg_assigned_to_user',
'bg_last_updated_user',
'bg_last_updated_date',
'bg_user_defined_attribute',
'bg_project_custom_dropdown_value1',
'bg_project_custom_dropdown_value2',
'bg_project_custom_dropdown_value3',
'bg_tags')
and st.[name] in ('nvarchar','varchar')
and sc.length > 30");
return(ds_custom_fields);
}
///////////////////////////////////////////////////////////////////////
protected void Page_Load(Object sender, EventArgs e)
{
Util.do_not_cache(Response);
Page.Header.Title = Util.get_setting("AppTitle", "BugTracker.NET") + " - "
+ "edit dashboard";
if (User.IsInRole(BtnetRoles.Admin) || User.Identity.GetCanUseReports())
{
//
}
else
{
Response.Write("You are not allowed to use this page.");
Response.End();
}
ses = (string)Session.SessionID;
var sql = new SQLString(@"
select ds_id, ds_col, ds_row, ds_chart_type, rp_desc
from dashboard_items ds
inner join reports on rp_id = ds_report
where ds_user = @user
order by ds_col, ds_row");
sql = sql.AddParameterWithValue("user", Convert.ToString(User.Identity.GetUserId()));
ds = DbUtil.get_dataset(sql);
}
///////////////////////////////////////////////////////////////////////
// Send the emails in the queue
protected static void actually_send_the_emails()
{
Util.write_to_log("actually_send_the_emails");
string sql = @"select * from queued_notifications where qn_status = N'not sent' and qn_retries < 3";
// create a new one, just in case there would be multithreading issues...
// get the pending notifications
DataSet ds = DbUtil.get_dataset(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
string err = "";
try
{
string to = (string)dr["qn_to"];
Util.write_to_log("sending email to " + to);
// try to send it
err = Email.send_email(
(string)dr["qn_to"],
(string)dr["qn_from"],
"", // cc
(string)dr["qn_subject"],
(string)dr["qn_body"],
BtnetMailFormat.Html);
if (err == "")
{
sql = "delete from queued_notifications where qn_id = $qn_id";
}
}
catch (Exception e)
{
err = e.Message;
if (e.InnerException != null)
{
err += "; ";
err += e.InnerException.Message;
}
}
if (err != "")
{
sql = "update queued_notifications set qn_retries = qn_retries + 1, qn_last_exception = N'$ex' where qn_id = $qn_id";
sql = sql.Replace("$ex", err.Replace("'", "''"));
}
sql = sql.Replace("$qn_id", Convert.ToString(dr["qn_id"]));
// update the row or delete the row
DbUtil.execute_nonquery(sql);
}
}
///////////////////////////////////////////////////////////////////////
public static DataSet get_bug_posts(int bugid, bool external_user, bool history_inline)
{
string sql = @"
/* 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 += "\n and a.bp_type <> 'update'";
}
if (external_user)
{
sql += "\n and a.bp_hidden_from_external_users = 0";
}
sql += "\n order by a.bp_id ";
sql += Util.get_setting("CommentSortOrder","desc");
sql += ", ba.bp_parent, ba.bp_id";
sql = sql.Replace("$id", Convert.ToString(bugid));
return DbUtil.get_dataset(sql);
}
///////////////////////////////////////////////////////////////////////
public static DataSet get_custom_columns()
{
DataSet ds = (DataSet)context.Application["custom_columns_dataset"];
if (ds != null)
{
return(ds);
}
else
{
ds = DbUtil.get_dataset(@"
/* custom columns */ select sc.name, st.[name] [datatype],
case when st.[name] = 'nvarchar' or st.[name] = 'nchar' then sc.length/2 else sc.length end as [length],
sc.xprec, sc.xscale, sc.isnullable,
mm.text [default value],
dflts.name [default name],
isnull(ccm_dropdown_type,'') [dropdown type],
isnull(ccm_dropdown_vals,'') [vals],
isnull(ccm_sort_seq, sc.colorder) [column order],
sc.colorder
from syscolumns sc
inner join systypes st on st.xusertype = sc.xusertype
inner join sysobjects so on sc.id = so.id
left outer join syscomments mm on sc.cdefault = mm.id
left outer join custom_col_metadata on ccm_colorder = sc.colorder
left outer join sysobjects dflts on dflts.id = mm.id
where so.name = 'bugs'
and st.[name] <> 'sysname'
and sc.name not in ('rowguid',
'bg_id',
'bg_short_desc',
'bg_reported_user',
'bg_reported_date',
'bg_project',
'bg_org',
'bg_category',
'bg_priority',
'bg_status',
'bg_assigned_to_user',
'bg_last_updated_user',
'bg_last_updated_date',
'bg_user_defined_attribute',
'bg_project_custom_dropdown_value1',
'bg_project_custom_dropdown_value2',
'bg_project_custom_dropdown_value3',
'bg_tags')
order by sc.id, isnull(ccm_sort_seq,sc.colorder)");
context.Application["custom_columns_dataset"] = ds;
return(ds);
}
}
///////////////////////////////////////////////////////////////////////
protected static void write_relationships(HttpResponse Response, int bugid)
{
string sql = @"select bg_id [id],
bg_short_desc [desc],
re_type [comment],
case
when re_direction = 0 then ''
when re_direction = 2 then 'child of $bg'
else 'parent of $bg' end [parent/child]
from bug_relationships
inner join bugs on re_bug2 = bg_id
where re_bug1 = $bg
order by 1";
sql = sql.Replace("$bg", Convert.ToString(bugid));
DataSet ds_relationships = DbUtil.get_dataset(sql);
if (ds_relationships.Tables[0].Rows.Count > 0)
{
Response.Write ("<b>Relationships</b><p><table border=1 class=datat><tr>");
Response.Write ("<td class=datah valign=bottom>id</td>");
Response.Write ("<td class=datah valign=bottom>desc</td>");
Response.Write ("<td class=datah valign=bottom>comment</td>");
Response.Write ("<td class=datah valign=bottom>parent/child</td>");
foreach (DataRow dr_relationships in ds_relationships.Tables[0].Rows)
{
Response.Write ("<tr>");
Response.Write ("<td class=datad valign=top align=right>");
Response.Write (Convert.ToString((int) dr_relationships["id"]));
Response.Write ("<td class=datad valign=top>");
Response.Write (Convert.ToString(dr_relationships["desc"]));
Response.Write ("<td class=datad valign=top>");
Response.Write (Convert.ToString(dr_relationships["comment"]));
Response.Write ("<td class=datad valign=top>");
Response.Write (Convert.ToString(dr_relationships["parent/child"]));
}
Response.Write ("</table><p>");
}
}
///////////////////////////////////////////////////////////////////////
public static void delete_bug(int bugid)
{
// delete attachements
string id = Convert.ToString(bugid);
string upload_folder = Util.get_upload_folder();
string sql = @"select bp_id, bp_file from bug_posts where bp_type = 'file' and bp_bug = $bg";
sql = sql.Replace("$bg", id);
DataSet ds = DbUtil.get_dataset(sql);
if (upload_folder != null && upload_folder != "")
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
// create path
StringBuilder path = new StringBuilder(upload_folder);
path.Append("\\");
path.Append(id);
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());
}
}
}
// delete the database entries
sql = @"
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 = $bg
delete from bug_posts where bp_bug = $bg
delete from bug_subscriptions where bs_bug = $bg
delete from bug_relationships where re_bug1 = $bg
delete from bug_relationships where re_bug2 = $bg
delete from bug_user where bu_bug = $bg
delete from bug_tasks where tsk_bug = $bg
delete from bugs where bg_id = $bg";
sql = sql.Replace("$bg", id);
DbUtil.execute_nonquery(sql);
}
public static void threadproc_votes(object obj)
{
Util.write_to_log("threadproc_votes");
try
{
System.Web.HttpApplicationState app = (System.Web.HttpApplicationState)obj;
// Because "create view" wants to be the first in a batch, it won't work in setup.sql.
// So let's just run it here every time.
string sql = @"
if exists (select * from dbo.sysobjects where id = object_id(N'[votes_view]'))
drop view [votes_view]";
DbUtil.execute_nonquery(sql);
sql = @"
create view votes_view as
select bu_bug as vote_bug, sum(bu_vote) as vote_total
from bug_user
group by bu_bug
having sum(bu_vote) > 0";
DbUtil.execute_nonquery(sql);
sql = @"
select bu_bug, count(1)
from bug_user
where bu_vote = 1
group by bu_bug";
DataSet ds = DbUtil.get_dataset(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
app[Convert.ToString(dr[0])] = (int)dr[1];
}
}
catch (Exception ex)
{
Util.write_to_log("exception in threadproc_votes:" + ex.Message);
}
}
//*************************************************************
static void threadproc_pop3(object obj)
{
//System.Web.HttpApplication app = (System.Web.HttpApplication)obj;
while (true)
{
int Pop3FetchIntervalInMinutes = Convert.ToInt32(Util.get_setting("Pop3FetchIntervalInMinutes", "15"));
try
{
// get all the projects that have been associated with pop3 usernames
string sql = @"select pj_id, pj_pop3_username, pj_pop3_password from projects where pj_enable_pop3 = 1";
DataSet ds = DbUtil.get_dataset(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Util.write_to_log("pop3:processing project " + Convert.ToString(dr["pj_id"]) + " using account " + dr["pj_pop3_username"]);
bool result = fetch_messages(
(string)dr["pj_pop3_username"],
(string)dr["pj_pop3_password"],
(int)dr["pj_id"]);
if (!result)
{
Util.write_to_log("pop3:exiting thread because error count has reached the limit");
return;
}
}
}
catch (Exception e)
{
Util.write_to_log("pop3:exception in threadproc_pop3:");
Util.write_to_log(e.Message);
Util.write_to_log(e.StackTrace);
return;
}
System.Threading.Thread.Sleep(Pop3FetchIntervalInMinutes * 60 * 1000);
}
}
public static void threadproc_tags(object obj)
{
try
{
System.Web.HttpApplicationState app = (System.Web.HttpApplicationState)obj;
SortedDictionary <string, List <int> > tags = new SortedDictionary <string, List <int> >();
// update the cache
DataSet ds = DbUtil.get_dataset("select bg_id, bg_tags from bugs where isnull(bg_tags,'') <> ''");
foreach (DataRow dr in ds.Tables[0].Rows)
{
string[] labels = Util.split_string_using_commas((string)dr[1]);
// for each tag label, build a list of bugids that have that label
for (int i = 0; i < labels.Length; i++)
{
string label = normalize_tag(labels[i]);
if (label != "")
{
if (!tags.ContainsKey(label))
{
tags[label] = new List <int>();
}
tags[label].Add((int)dr[0]);
}
}
}
app["tags"] = tags;
}
catch (Exception ex)
{
Util.write_to_log("exception in threadproc_tags:" + ex.Message);
}
}
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 DataTable get_related_users(Security security, bool force_full_names)
{
string sql = "";
if (Util.get_setting("DefaultPermissionLevel", "2") == "0")
{
// only show users who have explicit permission
// for projects that this user has permissions for
sql = @"
/* get related users 1 */
select us_id,
case when $fullnames 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 = $user.usid
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 <> $user.org
end
$limit_users
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 = @"
/* get related users 2 */
select pj_id, us_id,
case when $fullnames 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 = $user.usid
)
$limit_users
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 = $user.org
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 (Util.get_setting("LimitUsernameDropdownsInSearch", "0") == "1")
{
string sql_limit_user_names = @"
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";
sql = sql.Replace("$limit_users", sql_limit_user_names);
}
else
{
sql = sql.Replace("$limit_users", "");
}
if (force_full_names || Util.get_setting("UseFullNames", "0") == "1")
{
// true condition
sql = sql.Replace("$fullnames", "1 = 1");
}
else
{
// false condition
sql = sql.Replace("$fullnames", "0 = 1");
}
sql = sql.Replace("$user.usid", Convert.ToString(security.user.usid));
sql = sql.Replace("$user.org", Convert.ToString(security.user.org));
sql = sql.Replace("$og_external_user", Convert.ToString(security.user.external_user ? 1 : 0));
sql = sql.Replace("$og_other_orgs_permission_level", Convert.ToString(security.user.other_orgs_permission_level));
return(DbUtil.get_dataset(sql).Tables[0]);
}
///////////////////////////////////////////////////////////////////////
public static DataSet get_all_tasks(Security security, int bugid)
{
string sql = "select ";
if (bugid == 0)
{
sql += @"
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 += "tsk_id [task<br>id], tsk_description [task<br>description] ";
if (Util.get_setting("ShowTaskAssignedTo", "1") == "1")
{
sql += ", task_users.us_username [task<br>assigned to]";
}
if (Util.get_setting("ShowTaskPlannedStartDate", "1") == "1")
{
sql += ", tsk_planned_start_date [planned start]";
}
if (Util.get_setting("ShowTaskActualStartDate", "1") == "1")
{
sql += ", tsk_actual_start_date [actual start]";
}
if (Util.get_setting("ShowTaskPlannedEndDate", "1") == "1")
{
sql += ", tsk_planned_end_date [planned end]";
}
if (Util.get_setting("ShowTaskActualEndDate", "1") == "1")
{
sql += ", tsk_actual_end_date [actual end]";
}
if (Util.get_setting("ShowTaskPlannedDuration", "1") == "1")
{
sql += ", tsk_planned_duration [planned<br>duration]";
}
if (Util.get_setting("ShowTaskActualDuration", "1") == "1")
{
sql += ", tsk_actual_duration [actual<br>duration]";
}
if (Util.get_setting("ShowTaskDurationUnits", "1") == "1")
{
sql += ", tsk_duration_units [duration<br>units]";
}
if (Util.get_setting("ShowTaskPercentComplete", "1") == "1")
{
sql += ", tsk_percent_complete [percent<br>complete]";
}
if (Util.get_setting("ShowTaskStatus", "1") == "1")
{
sql += ", task_statuses.st_name [task<br>status]";
}
if (Util.get_setting("ShowTaskSortSequence", "1") == "1")
{
sql += ", tsk_sort_sequence [seq]";
}
sql += @"
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 += Util.alter_sql_per_project_permissions("select bg_id from bugs", security);
}
else
{
sql += Convert.ToString(bugid);
}
sql += @"
)
order by tsk_sort_sequence, tsk_id";
DataSet ds = DbUtil.get_dataset(sql);
return(ds);
}
///////////////////////////////////////////////////////////////////////
// This used to send the emails, but not now. Now it just queues
// the emails to be sent, then spawns a thread to send them.
public static void send_notifications(int insert_or_update, // The implementation
int bugid,
Security security,
int just_to_this_userid,
bool status_changed,
bool assigned_to_changed,
int prev_assigned_to_user)
{
// If there's something worth emailing about, then there's
// probably something worth updating the index about.
// Really, though, we wouldn't want to update the index if it were
// just the status that were changing...
if (Util.get_setting("EnableLucene", "1") == "1")
{
MyLucene.update_lucene_index(bugid);
}
bool notification_email_enabled = (Util.get_setting("NotificationEmailEnabled", "1") == "1");
if (!notification_email_enabled)
{
return;
}
// MAW -- 2006/01/27 -- Determine level of change detected
int changeLevel = 0;
if (insert_or_update == INSERT)
{
changeLevel = 1;
}
else if (status_changed)
{
changeLevel = 2;
}
else if (assigned_to_changed)
{
changeLevel = 3;
}
else
{
changeLevel = 4;
}
string sql;
if (just_to_this_userid > 0)
{
sql = @"
/* get notification email for just one user */
select us_email, us_id, us_admin, og.*
from bug_subscriptions
inner join users on bs_user = us_id
inner join orgs og on us_org = og_id
inner join bugs on bg_id = bs_bug
left outer join project_user_xref on pu_user = us_id and pu_project = bg_project
where us_email is not null
and us_enable_notifications = 1
-- $status_change
and us_active = 1
and us_email <> ''
and
case
when
us_org <> bg_org
and og_other_orgs_permission_level < 2
and og_other_orgs_permission_level < isnull(pu_permission_level,$dpl)
then og_other_orgs_permission_level
else
isnull(pu_permission_level,$dpl)
end <> 0
and bs_bug = $id
and us_id = $just_this_usid";
sql = sql.Replace("$just_this_usid", Convert.ToString(just_to_this_userid));
}
else
{
// MAW -- 2006/01/27 -- Added different notifications if reported or assigned-to
sql = @"
/* get notification emails for all subscribers */
select us_email, us_id, us_admin, og.*
from bug_subscriptions
inner join users on bs_user = us_id
inner join orgs og on us_org = og_id
inner join bugs on bg_id = bs_bug
left outer join project_user_xref on pu_user = us_id and pu_project = bg_project
where us_email is not null
and us_enable_notifications = 1
-- $status_change
and us_active = 1
and us_email <> ''
and ( ($cl <= us_reported_notifications and bg_reported_user = bs_user)
or ($cl <= us_assigned_notifications and bg_assigned_to_user = bs_user)
or ($cl <= us_assigned_notifications and $pau = bs_user)
or ($cl <= us_subscribed_notifications))
and
case
when
us_org <> bg_org
and og_other_orgs_permission_level < 2
and og_other_orgs_permission_level < isnull(pu_permission_level,$dpl)
then og_other_orgs_permission_level
else
isnull(pu_permission_level,$dpl)
end <> 0
and bs_bug = $id
and (us_id <> $us or isnull(us_send_notifications_to_self,0) = 1)";
}
sql = sql.Replace("$cl", changeLevel.ToString());
sql = sql.Replace("$pau", prev_assigned_to_user.ToString());
sql = sql.Replace("$id", Convert.ToString(bugid));
sql = sql.Replace("$dpl", Util.get_setting("DefaultPermissionLevel", "2"));
sql = sql.Replace("$us", Convert.ToString(security.user.usid));
DataSet ds_subscribers = DbUtil.get_dataset(sql);
if (ds_subscribers.Tables[0].Rows.Count > 0)
{
bool added_to_queue = false;
// Get bug html
DataRow bug_dr = Bug.get_bug_datarow(bugid, security);
string from = Util.get_setting("NotificationEmailFrom", "");
// Format the subject line
string subject = Util.get_setting("NotificationSubjectFormat", "$THING$:$BUGID$ was $ACTION$ - $SHORTDESC$ $TRACKINGID$");
subject = subject.Replace("$THING$", Util.capitalize_first_letter(Util.get_setting("SingularBugLabel", "bug")));
string action = "";
if (insert_or_update == INSERT)
{
action = "added";
}
else
{
action = "updated";
}
subject = subject.Replace("$ACTION$", action);
subject = subject.Replace("$BUGID$", Convert.ToString(bugid));
subject = subject.Replace("$SHORTDESC$", (string)bug_dr["short_desc"]);
string tracking_id = " (";
tracking_id += Util.get_setting("TrackingIdString", "DO NOT EDIT THIS:");
tracking_id += Convert.ToString(bugid);
tracking_id += ")";
subject = subject.Replace("$TRACKINGID$", tracking_id);
subject = subject.Replace("$PROJECT$", (string)bug_dr["current_project"]);
subject = subject.Replace("$ORGANIZATION$", (string)bug_dr["og_name"]);
subject = subject.Replace("$CATEGORY$", (string)bug_dr["category_name"]);
subject = subject.Replace("$PRIORITY$", (string)bug_dr["priority_name"]);
subject = subject.Replace("$STATUS$", (string)bug_dr["status_name"]);
subject = subject.Replace("$ASSIGNED_TO$", (string)bug_dr["assigned_to_username"]);
// send a separate email to each subscriber
foreach (DataRow dr in ds_subscribers.Tables[0].Rows)
{
string to = (string)dr["us_email"];
// Create a fake response and let the code
// write the html to that response
System.IO.StringWriter writer = new System.IO.StringWriter();
HttpResponse my_response = new HttpResponse(writer);
my_response.Write("<html>");
my_response.Write("<base href=\"" +
Util.get_setting("AbsoluteUrlPrefix", "http://127.0.0.1/") + "\"/>");
// create a security rec for the user receiving the email
Security sec2 = new Security();
// fill in what we know is needed downstream
sec2.user.is_admin = Convert.ToBoolean(dr["us_admin"]);
sec2.user.external_user = Convert.ToBoolean(dr["og_external_user"]);
sec2.user.tags_field_permission_level = (int)dr["og_tags_field_permission_level"];
sec2.user.category_field_permission_level = (int)dr["og_category_field_permission_level"];
sec2.user.priority_field_permission_level = (int)dr["og_priority_field_permission_level"];
sec2.user.assigned_to_field_permission_level = (int)dr["og_assigned_to_field_permission_level"];
sec2.user.status_field_permission_level = (int)dr["og_status_field_permission_level"];
sec2.user.project_field_permission_level = (int)dr["og_project_field_permission_level"];
sec2.user.org_field_permission_level = (int)dr["og_org_field_permission_level"];
sec2.user.udf_field_permission_level = (int)dr["og_udf_field_permission_level"];
DataSet ds_custom = Util.get_custom_columns();
foreach (DataRow dr_custom in ds_custom.Tables[0].Rows)
{
string bg_name = (string)dr_custom["name"];
string og_name = "og_"
+ (string)dr_custom["name"]
+ "_field_permission_level";
object obj = dr[og_name];
if (Convert.IsDBNull(obj))
{
sec2.user.dict_custom_field_permission_level[bg_name] = Security.PERMISSION_ALL;
}
else
{
sec2.user.dict_custom_field_permission_level[bg_name] = (int)dr[og_name];
}
}
PrintBug.print_bug(
my_response,
bug_dr,
sec2,
true, // include style
false, // images_inline
true, // history_inline
true); // internal_posts
// at this point "writer" has the bug html
sql = @"
delete from queued_notifications where qn_bug = $bug and qn_to = N'$to'
insert into queued_notifications
(qn_date_created, qn_bug, qn_user, qn_status, qn_retries, qn_to, qn_from, qn_subject, qn_body, qn_last_exception)
values (getdate(), $bug, $user, N'not sent', 0, N'$to', N'$from', N'$subject', N'$body', N'')";
sql = sql.Replace("$bug", Convert.ToString(bugid));
sql = sql.Replace("$user", Convert.ToString(dr["us_id"]));
sql = sql.Replace("$to", to.Replace("'", "''"));
sql = sql.Replace("$from", from.Replace("'", "''"));
sql = sql.Replace("$subject", subject.Replace("'", "''"));
sql = sql.Replace("$body", writer.ToString().Replace("'", "''"));
DbUtil.execute_nonquery_without_logging(sql);
added_to_queue = true;
} // end loop through ds_subscribers
if (added_to_queue)
{
// spawn a worker thread to send the emails
System.Threading.Thread thread = new System.Threading.Thread(threadproc_notifications);
thread.Start();
}
} // if there are any subscribers
}
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
Util.write_to_log("copy_user creating " + username + " from template user " + template_username);
StringBuilder org_columns = new StringBuilder();
string sql = "";
if (use_domain_as_org_name)
{
sql = @" /* 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 = 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 = @"
/* 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 = N'$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 = N'$domain'
IF @org_id = -1
BEGIN
insert into orgs
(
og_name,
og_domain
$ORG_COLUMNS
)
select
N'$domain',
N'$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 = '******')
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
N'$username', N'$email', N'$firstname', N'$lastname', N'$signature', $salt, N'$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.Replace("$username", username.Replace("'", "''"));
sql = sql.Replace("$email", email.Replace("'", "''"));
sql = sql.Replace("$firstname", firstname.Replace("'", "''"));
sql = sql.Replace("$lastname", lastname.Replace("'", "''"));
sql = sql.Replace("$signature", signature.Replace("'", "''"));
sql = sql.Replace("$salt", Convert.ToString(salt));
sql = sql.Replace("$password", password);
sql = sql.Replace("$template_user", template_username.Replace("'", "''"));
sql = sql.Replace("$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.Replace("$domain", email_parts[1].Replace("'", "''"));
}
else
{
sql = sql.Replace("$domain", email.Replace("'", "''"));
}
sql = sql.Replace("$ORG_COLUMNS", org_columns.ToString());
return(Convert.ToInt32(DbUtil.execute_scalar(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;
}
}
}
///////////////////////////////////////////////////////////////////////
// create a new index
static void threadproc_build(object obj)
{
lock (my_lock)
{
try
{
System.Web.HttpApplicationState app = (System.Web.HttpApplicationState)obj;
Util.write_to_log("started creating Lucene index using folder " + MyLucene.index_path);
Lucene.Net.Index.IndexWriter writer = new Lucene.Net.Index.IndexWriter(index_path, anal, true);
string sql = @"
select bg_id,
$custom_cols
isnull(bg_tags,'') bg_tags,
bg_short_desc
from bugs";
DataSet ds_text_custom_cols = get_text_custom_cols();
sql = sql.Replace("$custom_cols", get_text_custom_cols_names(ds_text_custom_cols));
// index the bugs
DataSet ds = DbUtil.get_dataset(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
// desc
writer.AddDocument(MyLucene.create_doc(
(int)dr["bg_id"],
0,
"desc",
(string)dr["bg_short_desc"]));
// tags
string tags = (string)dr["bg_tags"];
if (tags != "")
{
writer.AddDocument(MyLucene.create_doc(
(int)dr["bg_id"],
0,
"tags",
tags));
}
// custom text fields
foreach (DataRow dr_custom_col in ds_text_custom_cols.Tables[0].Rows)
{
string name = (string)dr_custom_col["name"];
string val = Convert.ToString(dr[name]);
if (val != "")
{
writer.AddDocument(MyLucene.create_doc(
(int)dr["bg_id"],
0,
name.Replace("'", "''"),
val));
}
}
}
// index the bug posts
ds = DbUtil.get_dataset(@"
select bp_bug, bp_id,
isnull(bp_comment_search,bp_comment) [text]
from bug_posts
where bp_type <> 'update'
and bp_hidden_from_external_users = 0");
foreach (DataRow dr in ds.Tables[0].Rows)
{
writer.AddDocument(MyLucene.create_doc(
(int)dr["bp_bug"],
(int)dr["bp_id"],
"post",
(string)dr["text"]));
}
writer.Optimize();
writer.Close();
Util.write_to_log("done creating Lucene index");
}
catch (Exception e)
{
Util.write_to_log("exception building Lucene index: " + e.Message);
Util.write_to_log(e.StackTrace);
}
}
}
// update an existing index
static void threadproc_update(object obj)
{
// just to be safe, make the worker threads wait for each other
//System.Console.Beep(540, 20);
lock (my_lock) // prevent contention between searching and writing?
{
//System.Console.Beep(840, 20);
try
{
if (searcher != null)
{
try
{
searcher.Close();
}
catch (Exception e)
{
Util.write_to_log("Exception closing lucene searcher:" + e.Message);
Util.write_to_log(e.StackTrace);
}
searcher = null;
}
Lucene.Net.Index.IndexModifier modifier = new Lucene.Net.Index.IndexModifier(index_path, anal, false);
// same as buid, but uses "modifier" instead of write.
// uses additional "where" clause for bugid
int bug_id = (int)obj;
Util.write_to_log("started updating Lucene index using folder " + MyLucene.index_path);
modifier.DeleteDocuments(new Lucene.Net.Index.Term("bg_id", Convert.ToString(bug_id)));
string sql = @"
select bg_id,
$custom_cols
isnull(bg_tags,'') bg_tags,
bg_short_desc
from bugs where bg_id = $bugid";
sql = sql.Replace("$bugid", Convert.ToString(bug_id));
DataSet ds_text_custom_cols = get_text_custom_cols();
sql = sql.Replace("$custom_cols", get_text_custom_cols_names(ds_text_custom_cols));
// index the bugs
DataRow dr = DbUtil.get_datarow(sql);
modifier.AddDocument(MyLucene.create_doc(
(int)dr["bg_id"],
0,
"desc",
(string)dr["bg_short_desc"]));
// tags
string tags = (string)dr["bg_tags"];
if (tags != "")
{
modifier.AddDocument(MyLucene.create_doc(
(int)dr["bg_id"],
0,
"tags",
tags));
}
// custom text fields
foreach (DataRow dr_custom_col in ds_text_custom_cols.Tables[0].Rows)
{
string name = (string)dr_custom_col["name"];
string val = Convert.ToString(dr[name]);
if (val != "")
{
modifier.AddDocument(MyLucene.create_doc(
(int)dr["bg_id"],
0,
name.Replace("'", "''"),
val));
}
}
// index the bug posts
DataSet ds = DbUtil.get_dataset(@"
select bp_bug, bp_id,
isnull(bp_comment_search,bp_comment) [text]
from bug_posts
where bp_type <> 'update'
and bp_hidden_from_external_users = 0
and bp_bug = " + Convert.ToString(bug_id));
foreach (DataRow dr2 in ds.Tables[0].Rows)
{
modifier.AddDocument(MyLucene.create_doc(
(int)dr2["bp_bug"],
(int)dr2["bp_id"],
"post",
(string)dr2["text"]));
}
modifier.Flush();
modifier.Close();
Util.write_to_log("done updating Lucene index");
}
catch (Exception e)
{
Util.write_to_log("exception updating Lucene index: " + e.Message);
Util.write_to_log(e.StackTrace);
}
}
}