util.cs

Bug管理系统

					left outer join project_user_xref on pj_id = pu_project
						and pu_user = $us
					where bg_id = $id";

			if (ds_custom_cols.Tables[0].Rows.Count == 0)
			{
				sql = sql.Replace("$custom_cols_placeholder","");
			}
			else
			{
				string custom_cols_sql = "";
				
				foreach (DataRow drcc in ds_custom_cols.Tables[0].Rows)
				{
					custom_cols_sql +=  ",[" + drcc["name"].ToString() + "]";
					
				}					
				sql = sql.Replace("$custom_cols_placeholder", custom_cols_sql);
			}
			
			sql = sql.Replace("$id", Convert.ToString(bugid));
			sql = sql.Replace("$us", Convert.ToString(security.this_usid));
			sql = sql.Replace("$dpl", get_setting("DefaultPermissionLevel","2"));			

			return dbutil.get_datarow(sql);


		}
		
		///////////////////////////////////////////////////////////////////////
		public static DataSet get_bug_comments(int bugid,DbUtil dbutil)
		{
		
			string sql;		

			if (Util.get_setting("ShowHistoryWithComments", "0") == "1")
			{
				sql = @"select
					bc_bug,
					bc_comment,
					isnull(us_username,'') [us_username],
					isnull(us_lastname,'') + ', ' + isnull(us_firstname,'') [us_fullname],
					isnull(us_email,'') [us_email],
					bc_date,
					bc_id,
					bc_type,
					isnull(bc_email_from,'') [bc_email_from],
					isnull(bc_email_to,'') [bc_email_to],
					ba.*
					from bug_comments
					left outer join users on us_id = bc_user
					left outer join bug_attachments ba on ba_comment = bc_id				
					where bc_bug = $id
					order by bc_date " + Util.get_setting("CommentSortOrder","desc");

			}
			else
			{
				sql = @"select
					bc_bug,
					bc_comment,
					isnull(us_username,'') [us_username],
					isnull(us_lastname,'') + ', ' + isnull(us_firstname,'') [us_fullname],
					isnull(us_email,'') [us_email],
					bc_date,
					bc_id,
					bc_type,
					isnull(bc_email_from,'') [bc_email_from],
					isnull(bc_email_to,'') [bc_email_to],
					ba.*
					from bug_comments
					left outer join users on us_id = bc_user
					left outer join bug_attachments ba on ba_comment = bc_id					
					where bc_bug = $id
					and bc_type <> 'update'
					order by bc_date " + Util.get_setting("CommentSortOrder","desc");
			}

			sql = sql.Replace("$id", Convert.ToString(bugid));
			return dbutil.get_dataset(sql);
		
		}
		

		///////////////////////////////////////////////////////////////////////
		public static string capitalize_first_letter(string s)
		{
			if (s.Length > 0 && Util.get_setting("NoCapitalization","0") == "0")
			{
				return s.Substring(0,1).ToUpper() + s.Substring(1,s.Length-1);
			}
			return s;

		}


		///////////////////////////////////////////////////////////////////////
		public static string sanitize_integer(string s)
		{
			int n;
			string s2;
			try
			{
				n = Convert.ToInt32(s);
				s2 = Convert.ToString(n);
			}
			catch
			{
				throw (new Exception("Expected integer.  Possible SQL injection attempt?"));
				
			}
			
			return s;
		}


		///////////////////////////////////////////////////////////////////////
		public static bool is_numeric_datatype(string datatype)
		{
		
			if (datatype == "System.Int32"
			|| datatype == "System.Decimal"
			|| datatype == "System.Double"
			|| datatype == "System.Single"
			|| datatype == "System.UInt32"
			|| datatype == "System.Int64"
			|| datatype == "System.UInt64"
			|| datatype == "System.Int16"
			|| datatype == "System.UInt16")						
			{
				return true;
			}
			else
			{
				return false;
			}
			
		
		}
		
		///////////////////////////////////////////////////////////////////////
		public static string format_username(string username, string fullname)
		{
		
			if (Util.get_setting("UseFullNames","0") == "0")
			{
				return username;
			}
			else
			{
				return fullname;
			}
		}

	}
	
	///////////////////////////////////////////////////////////////////////
	///////////////////////////////////////////////////////////////////////
	// SortableHtmlTable
	///////////////////////////////////////////////////////////////////////
	///////////////////////////////////////////////////////////////////////
	public class SortableHtmlTable
	{

		///////////////////////////////////////////////////////////////////////
		public static void create_from_dataset (
			HttpResponse r,
			DataSet ds,
			string edit_url,
			string delete_url)
		{
			create_from_dataset(r, ds, edit_url, delete_url, true);
		}


		///////////////////////////////////////////////////////////////////////
		public static void create_from_dataset (
			HttpResponse r,
			DataSet ds,
			string edit_url,
			string delete_url,
			bool html_encode)
		{
			create_start_of_table(r);
			create_headings(r, ds, edit_url, delete_url);
			create_body(r, ds, edit_url, delete_url, html_encode);
			create_end_of_table(r);
		}

		///////////////////////////////////////////////////////////////////////
		public static void create_start_of_table (
			HttpResponse r)
		{

			r.Write ("\n<div id=wait class=please_wait>&nbsp;</div>\n");
			r.Write ("<div class=click_to_sort>click on column headings to sort</div>\n");
			r.Write ("<div id=myholder>\n");
			r.Write ("<table id=mytable class=sortable style=datat border=1 cellspacing=0 cellpadding=2>\n");

		}

		///////////////////////////////////////////////////////////////////////
		public static void create_end_of_table (
			HttpResponse r)
		{

			// data
			r.Write ("</table>\n");
			r.Write ("</div>\n");
			r.Write ("<div id=sortedby>&nbsp;</div>\n");

		}

		///////////////////////////////////////////////////////////////////////
		// headings
		///////////////////////////////////////////////////////////////////////
		public static void create_headings (
			HttpResponse r,
			DataSet ds,
			string edit_url,
			string delete_url)
		{

			r.Write ("<tr>\n");

			int db_column_count = 0;

			foreach (DataColumn dc in ds.Tables[0].Columns)
			{

				if ((edit_url != "" || delete_url != "")
				&& db_column_count == (ds.Tables[0].Columns.Count - 1))
				{
					if (edit_url != "")
					{
						r.Write ("<td class=datah valign=bottom>edit</td>");
					}
					if (delete_url != "")
					{
						r.Write ("<td class=datah valign=bottom>delete</td>");
					}

				}
				else
				{

					// determine data type
					string datatype = "";
					if (Util.is_numeric_datatype(dc.DataType.ToString()))
					{
						datatype = "num";
					}
					else if (dc.DataType.ToString() == "System.DateTime")
					{
						datatype = "date";
					}
					else
					{
						datatype = "str";
					}

					r.Write ("<td class=datah valign=bottom>\n");
					string s = "<a href='javascript: sort_by_col($col, \"$type\")'>";
					s = s.Replace("$col", Convert.ToString(db_column_count));
					s = s.Replace("$type", datatype);
					r.Write (s);
					r.Write (dc.ColumnName);
					r.Write ("</a>");
					//r.Write ("<br>");
					//r.Write (dc.DataType);
					r.Write ("</td>\n");

				}

				db_column_count++;

			}
			r.Write ("</tr>\n");

		}


		///////////////////////////////////////////////////////////////////////
		// body, data
		///////////////////////////////////////////////////////////////////////
		public static void create_body (
			HttpResponse r,
			DataSet ds,
			string edit_url,
			string delete_url,
			bool html_encode)
		{

			foreach (DataRow dr in ds.Tables[0].Rows)
			{
				r.Write ("<tr>");
				for(int i = 0; i < ds.Tables[0].Columns.Count; i++)
				{
					string datatype = ds.Tables[0].Columns[i].DataType.ToString();

					if ((edit_url != "" || delete_url != "")
					&& i == (ds.Tables[0].Columns.Count - 1))
					{
						if (edit_url != "")
						{
							r.Write ("<td class=datad><a href="
								+ edit_url + dr[ds.Tables[0].Columns.Count - 1] + ">edit</a></td>");
						}
						if (delete_url != "")
						{
							r.Write ("<td class=datad><a href="
								+ delete_url + dr[ds.Tables[0].Columns.Count - 1] + ">delete</a></td>");
						}
					}
					else
					{
						if (Util.is_numeric_datatype(datatype))
						{
							r.Write ("<td class=datad align=right>");
						}
						else
						{
							r.Write ("<td class=datad>");
						}
						if (dr[i].ToString() == "")
						{
							r.Write("&nbsp;");
						}
						else
						{
							if (datatype == "System.DateTime")
							{
								r.Write (Util.format_db_date(dr[i]));
							}
							else
							{
								if (html_encode)
								{
									r.Write (HttpUtility.HtmlEncode(dr[i].ToString()));
								}
								else
								{
									r.Write (dr[i]);
								}
							}
						}
						r.Write ("</td>");
					}

				}
				r.Write ("</tr>");
			}

		}
	}

	///////////////////////////////////////////////////////////////////////
	///////////////////////////////////////////////////////////////////////
	// DbUtil
	///////////////////////////////////////////////////////////////////////
	///////////////////////////////////////////////////////////////////////
	public class DbUtil {

		//public SqlConnection sqlconn;
		public string connection_string;

		///////////////////////////////////////////////////////////////////////
		public DataSet command_to_dataset(SqlCommand cmd)
		{

			DataSet ds = new DataSet();
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			da.Fill(ds);
			return ds;

		}
		///////////////////////////////////////////////////////////////////////
		public SqlCommand prep_stored_proc(string sql)
		{
			
			Util.write_to_log("sql=\n" + sql);
			SqlConnection conn = get_sqlconnection();
			SqlCommand cmd = new SqlCommand (sql, conn);
			cmd.CommandType = CommandType.StoredProcedure;
			return cmd;
		}

		///////////////////////////////////////////////////////////////////////
		public object execute_scalar(string sql)
		{
			Util.write_to_log("sql=\n" + sql);
			using (SqlConnection conn = get_sqlconnection())
			{
				SqlCommand cmd = new SqlCommand (sql, conn);
				return cmd.ExecuteScalar();
			}
		}

		///////////////////////////////////////////////////////////////////////
		public void execute_nonquery(string sql)
		{

			Util.write_to_log("sql=\n" + sql);
			using (SqlConnection conn = get_sqlconnection())
			{
				SqlCommand cmd = new SqlCommand (sql, conn);
				cmd.ExecuteNonQuery();
			}
		}

		///////////////////////////////////////////////////////////////////////
		public DataSet get_dataset(string sql)
		{

			Util.write_to_log("sql=\n" + sql);
			DataSet ds = new DataSet();
			using (SqlConnection conn = get_sqlconnection())
			{
				SqlDataAdapter da = new SqlDataAdapter(sql, conn);
				da.Fill(ds);
				return ds;
			}
		}

		///////////////////////////////////////////////////////////////////////
		public SqlConnection get_sqlconnection()
		{

		//	if (sqlconn == null || sqlconn.State != ConnectionState.Open) {
				connection_string = Util.get_setting("ConnectionString","MISSING CONNECTION STRING");
				SqlConnection sqlconn = new SqlConnection(connection_string);
				sqlconn.Open();
		//	}

			return sqlconn;

		}

		///////////////////////////////////////////////////////////////////////
		public DataView get_dataview(string sql)
		{
			Util.write_to_log("sql=\n" + sql);
			DataSet ds = get_dataset(sql);
			return new DataView (ds.Tables[0]);
		}


		///////////////////////////////////////////////////////////////////////
		public DataRow get_datarow(string sql)
		{
			DataSet ds = get_dataset(sql);
			if (ds.Tables[0].Rows.Count != 1) {
				return null;
			}
			else
			{
				return ds.Tables[0].Rows[0];
			}
		}

		///////////////////////////////////////////////////////////////////////
		public void close()
		{

			//if (sqlconn == null || sqlconn.State != ConnectionState.Open) return;

			//sqlconn.Close();
			//sqlconn = null;
		}

	}

}