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

const int INSERT = 1;
const int UPDATE = 2;
bool status_changed;


///////////////////////////////////////////////////////////////////////
int insert_bug(
	string short_desc,
	int this_usid,
	int projectid,
	int categoryid,
	int priorityid,
	int assigned_to_userid,
	int statusid,
	int udfid,
	string project_custom_dropdown_value1,
	string project_custom_dropdown_value2,
	string project_custom_dropdown_value3,
	string comments,
	string from,
	System.Collections.Hashtable hash_custom_cols)
{

	if (assigned_to_userid == 0)
	{
		assigned_to_userid = get_default_user(projectid);
	}

	sql = @"insert into bugs
			(bg_short_desc,
			bg_reported_user,
			bg_reported_date,
			bg_project,
			bg_category,
			bg_priority,
			bg_assigned_to_user,
			bg_status,
			bg_user_defined_attribute,
			bg_project_custom_dropdown_value1,
			bg_project_custom_dropdown_value2,
			bg_project_custom_dropdown_value3
			$custom_cols_placeholder1)
			values (N'$sd', $ru, getdate(), $pj, $ct, $pr, $au, $st, $udf,
			N'$pcd1',N'$pcd2',N'$pcd3' $custom_cols_placeholder2)";

	sql = sql.Replace("$sd", short_desc.Replace("'","''"));
	sql = sql.Replace("$ru", Convert.ToString(this_usid));
	sql = sql.Replace("$pj", Convert.ToString(projectid));
	sql = sql.Replace("$ct", Convert.ToString(categoryid));
	sql = sql.Replace("$pr", Convert.ToString(priorityid));
	sql = sql.Replace("$au", Convert.ToString(assigned_to_userid));
	sql = sql.Replace("$st", Convert.ToString(statusid));
	sql = sql.Replace("$udf", Convert.ToString(udfid));
	sql = sql.Replace("$pcd1", project_custom_dropdown_value1);
	sql = sql.Replace("$pcd2", project_custom_dropdown_value2);
	sql = sql.Replace("$pcd3", project_custom_dropdown_value3);

	if (hash_custom_cols == null)
	{
		sql = sql.Replace("$custom_cols_placeholder1","");
		sql = sql.Replace("$custom_cols_placeholder2","");
	}
	else
	{

		string custom_cols_sql1 = "";
		string custom_cols_sql2 = "";

		// try to guess if a field is a date
		Regex r1 = new Regex("[0-9][0-9]\\.[0-9][0-9]\\.[0-9][0-9][0-9][0-9]");
		Regex r2 = new Regex("[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]");
		Regex r3 = new Regex("[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]");
		Regex r4 = new Regex("[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]");


     	System.Collections.IDictionaryEnumerator custom_col = hash_custom_cols.GetEnumerator();
      	while (custom_col.MoveNext())
      	{

			custom_cols_sql1 += ",[" + custom_col.Key + "]";

			string custom_col_val = custom_col.Value.ToString();

			if (custom_col_val.Length == 10)
			{

				// see if the custom field is a date
				if (r1.IsMatch(custom_col_val)
				|| r2.IsMatch(custom_col_val)
				|| r3.IsMatch(custom_col_val)
				|| r4.IsMatch(custom_col_val))
				{
					custom_col_val = Util.format_local_date_into_db_format(custom_col_val);
				}
			}

			if (custom_col_val.Length == 0)
			{
				custom_cols_sql2 += ", null";
			}
			else
			{
				custom_cols_sql2 += ",N'"
					+ custom_col_val.Replace("'","''")
					+ "'";
			}

		}
		sql = sql.Replace("$custom_cols_placeholder1", custom_cols_sql1);
		sql = sql.Replace("$custom_cols_placeholder2", custom_cols_sql2);
	}



	sql += "\nselect @@IDENTITY";
	id = Convert.ToInt32(dbutil.execute_scalar(sql));

	insert_comment(id, this_usid, comments, from);

	auto_subscribe(id, projectid);

	send_notifications(INSERT,
		id,
		this_usid);

	return id;
}


///////////////////////////////////////////////////////////////////////
string send_notifications(int insert_or_update,
	int bugid,
	int this_usid)
{

	string result = "";

	bool notification_email_enabled = (btnet.Util.get_setting("NotificationEmailEnabled","1") == "1");
	if (notification_email_enabled)
	{

		if (insert_or_update == INSERT)
		{
			// get a list of the folks subscribing to notifications
			sql = @"select us_email
					from bug_subscriptions
					inner join users on bs_user = us_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 isnull(pu_permission_level,$dpl) <> 0
					and bs_bug = $id
					and (us_id <> $us or isnull(us_send_notifications_to_self,0) = 1)";
		}
		else
		{
			// get a list of the folks subscribing to notifications
			sql = @"select us_email
					from bug_subscriptions
					inner join users on bs_user = us_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 us_only_new_bug_notifications <> 1
					and isnull(pu_permission_level,$dpl) <> 0
					and bs_bug = $id
					and (us_id <> $us or isnull(us_send_notifications_to_self,0) = 1)";
		}


		if (status_changed || insert_or_update == INSERT)
		{
			sql = sql.Replace("$status_change","");
		}
		else
		{
			sql = sql.Replace("$status_change", " and isnull(us_only_status_change_notifications,0) = 0 ");
		}
		sql = sql.Replace("$id", Convert.ToString(bugid));
		sql = sql.Replace("$dpl", Util.get_setting("DefaultPermissionLevel","2"));
		sql = sql.Replace("$us", Convert.ToString(this_usid));


		DataSet subscribers = dbutil.get_dataset(sql);

		if (subscribers.Tables[0].Rows.Count > 0)
		{

			// Get bug html
			DataRow bug_dr = get_bug_datarow(bugid);

			// 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);
			print_bug(my_response, bug_dr);
			// at this point "writer" has the bug html


			string from = Util.get_setting("NotificationEmailFrom","");
			string subject;
			if (insert_or_update == INSERT)
			{
				subject = Util.capitalize_first_letter(Util.get_setting("SingularBugLabel","bug")) + " " + Convert.ToString(bugid) + " added : " + bug_dr["short_desc"];
			}
			else
			{
				subject = Util.capitalize_first_letter(Util.get_setting("SingularBugLabel","bug")) + " " + Convert.ToString(bugid) + " updated : " + bug_dr["short_desc"];
			}
			string to = "";

			if (Util.get_setting("SendJustOneEmail","1") == "1")
			{

				// send just one email, with a bunch of addresses

				foreach (DataRow dr in subscribers.Tables[0].Rows)
				{
					// Concat in a string for performance
					to += (string) dr["us_email"] + ";";
				}

				result += Util.send_email(
					to,
					from,
					"", // cc
					subject, writer.ToString(),
					System.Web.Mail.MailFormat.Html);

			}
			else
			{

				// send a separate email to each subscriber
				foreach (DataRow dr in subscribers.Tables[0].Rows)
				{
					to = (string) dr["us_email"];

					result += Util.send_email(
						to,
						from,
						"", // cc
						subject, writer.ToString(),
						System.Web.Mail.MailFormat.Html);
				}

			}
		}
	}

	return result;
}


///////////////////////////////////////////////////////////////////////
void auto_subscribe(int bugid, int projectid)
{


		// subscribe per auto_subscribe
		// subscribe project's default user
		// subscribe per-project auto_subscribers
		// subscribe per auto_subscribe_own_bugs
		sql = @"insert into bug_subscriptions (bs_bug, bs_user)
				select $id, us_id
				from users
				left outer join project_user_xref on pu_project = $pj and pu_user = us_id
				where us_auto_subscribe = 1
				and isnull(pu_permission_level,$dpl) <> 0
				and us_active = 1
				and us_id not in
				(select bs_user from bug_subscriptions
				where bs_bug = $id)

				insert into bug_subscriptions (bs_bug, bs_user)
				select $id, pj_default_user
				from projects
				inner join users on pj_default_user = us_id
				where pj_id = $pj
				and pj_default_user <> 0
				and pj_auto_subscribe_default_user = 1
				and us_active = 1
				and pj_default_user not in
				(select bs_user from bug_subscriptions
				where bs_bug = $id)

				insert into bug_subscriptions (bs_bug, bs_user)
				select $id, pu_user from project_user_xref
				inner join users on pu_user = us_id
				where pu_auto_subscribe = 1
				and isnull(pu_permission_level,$dpl) <> 0
				and us_active = 1
				and pu_project = $pj
				and pu_user not in
				(select bs_user from bug_subscriptions
				where bs_bug = $id)

				insert into bug_subscriptions (bs_bug, bs_user)
				select $id, us_id
				from users
				inner join bugs on bg_id = $id
				left outer join project_user_xref on pu_project = $pj and pu_user = us_id
				where ((us_auto_subscribe_own_bugs = 1 and bg_assigned_to_user = us_id)
					or 
					(us_auto_subscribe_reported_bugs = 1 and bg_reported_user = us_id))
				and isnull(pu_permission_level,$dpl) <> 0
				and us_active = 1
				and us_id not in
				(select bs_user from bug_subscriptions
				where bs_bug = $id)";

		sql = sql.Replace("$id", Convert.ToString(bugid));
		sql = sql.Replace("$pj", Convert.ToString(projectid));
		sql = sql.Replace("$dpl", Util.get_setting("DefaultPermissionLevel","2"));

		dbutil.execute_nonquery(sql);


}


///////////////////////////////////////////////////////////////////////
void insert_comment (int bugid, int this_usid, string comments, string from)
{

	if (comments != "")
	{
		sql = @"insert into bug_comments
				(bc_bug, bc_user, bc_date, bc_comment, bc_email_from, bc_type)
				values($id, $us, getdate(), N'$cm', N'$fr', N'$ty')";

		string s = comments.Replace("'", "''");

		sql = sql.Replace("$id", Convert.ToString(bugid));
		sql = sql.Replace("$us", Convert.ToString(this_usid));
		sql = sql.Replace("$cm", s);

		if (from != null)
		{
			sql = sql.Replace("$fr", from);
			sql = sql.Replace("$ty", "received"); // received email
		}
		else
		{
			sql = sql.Replace("N'$fr'", "null");
			sql = sql.Replace("$ty", "comment"); // bug comment
		}

		dbutil.execute_nonquery(sql);

	}

}


///////////////////////////////////////////////////////////////////////
int get_default_user(int projectid)
{

	if (projectid == 0) {return 0;}

	sql = @"select isnull(pj_default_user,0)
			from projects
			where pj_id = $pj";

	sql = sql.Replace("$pj", Convert.ToString(projectid));
	object obj = dbutil.execute_scalar(sql);

	if (obj != null)
	{
		return (int) obj;
	}
	else
	{
		return 0;
	}

}



</script>