public static List<FieldType> GetFieldTypes()
{
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
using (DataTable dt = database.GetDataTable("dbo.TypeGet", new List<SqlParameter>()))
{
List<FieldType> types = new List<FieldType>();
FieldType ft;
foreach (DataRow row in dt.Rows)
{
ft = new FieldType();
ft.ID = int.Parse(row["ID"].ToString());
ft.Name = row["Name"].ToString();
ft.HtmlType = row["HTMLType"].ToString();
types.Add(ft);
}
return types;
}
}
}
public string GetEventLog()
{
string ret = "";
List<Event> events = new List<Event>();
try
{
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
using (DataTable dt = database.GetDataTable("dbo.LogGet", new List<SqlParameter>()))
{
foreach (DataRow dr in dt.Rows)
{
Event e;
e.user = dr["User"].ToString();
e.message = dr["Message"].ToString();
e.date = dr["Date"].ToString();
events.Add(e);
}
}
ret = new JavaScriptSerializer().Serialize(Json(events).Data);
}
}
catch (Exception e)
{
new EventLogger.EventLogger("Routine Management", "Application").WriteException(e);
}
return ret;
}
public static List<string> GetAllUserNames()
{
List<string> ret = new List<string>();
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
using (DataTable dt = database.GetDataTable("dbo.UserNamesGet", new List<System.Data.SqlClient.SqlParameter>()))
{
foreach (DataRow dr in dt.Rows)
{
ret.Add(dr[0].ToString());
}
}
}
return ret;
}
public static List<string> GetAreas()
{
List<string> ret = new List<string>();
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
using (DataTable result = database.GetDataTable("dbo.AreaGet", new List<SqlParameter>()))
{
foreach (DataRow dr in result.Rows)
{
ret.Add(dr["Name"].ToString());
}
}
}
return ret;
}
public static List<string> GetTeamsByArea(string AreaName)
{
List<string> ret = new List<string>();
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("AreaName", SqlDbType.NVarChar) { Value = AreaName });
using (DataTable result = database.GetDataTable("dbo.TeamByAreaGet", parameters))
{
foreach (DataRow dr in result.Rows)
{
ret.Add(dr["Name"].ToString());
}
}
}
return ret;
}
public static List<Comment> LoadCommentsForSchedule(int ScheduleID)
{
List<Comment> ret = new List<Comment>();
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
List<Comment> allComments = new List<Comment>();
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new System.Data.SqlClient.SqlParameter("@ScheduleID", SqlDbType.Int) { Value = ScheduleID });
using (DataTable dt = database.GetDataTable("dbo.CommentsForScheduleGet", parameters))
{
foreach (DataRow dr in dt.Rows)
{
Comment c = new Comment();
c.ID = int.Parse(dr["ID"].ToString());
c.Text = dr["Comment"].ToString();
c.DateStamp = dr["DateStamp"].ToString();
c.UserName = dr["UserName"].ToString();
c.Reply = dr["Reply"].ToString();
int.TryParse(dr["ParentCommentID"].ToString(), out c.ParentID);
allComments.Add(c);
}
foreach (Comment c in allComments)
{
foreach (Comment cc in allComments)
{
if (c == cc)
{
continue;
}
if (cc.ParentID == c.ID)
{
c.Replies.Add(cc);
}
}
if (c.Replies.Count > 0 || c.Reply == "0")
{
ret.Add(c);
}
}
}
}
return ret;
}
public static void UpdateAccessLevel(string UserName, int AccessLevelID)
{
List<System.Data.SqlClient.SqlParameter> parameters = new List<System.Data.SqlClient.SqlParameter>();
parameters.Add(new System.Data.SqlClient.SqlParameter("@UserName", SqlDbType.NVarChar) { Value = UserName });
parameters.Add(new System.Data.SqlClient.SqlParameter("@AccessLevelID", SqlDbType.Int) { Value = AccessLevelID });
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
database.GetDataTable("dbo.UserAccessLevelUpdate", parameters);
}
}
public static UserAccessLevel GetUserAccessLevel(string UserName)
{
UserAccessLevel ret = new UserAccessLevel();
List<System.Data.SqlClient.SqlParameter> parameters = new List<System.Data.SqlClient.SqlParameter>();
parameters.Add(new System.Data.SqlClient.SqlParameter("@UserName", SqlDbType.NVarChar) { Value = UserName });
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
using (DataTable dt = database.GetDataTable("dbo.UserAccessLevelsGet", parameters))
{
DataRow r = dt.Rows[0];
ret.Reader = r["Reader"].ToString() == "1";
ret.Writer = r["Writer"].ToString() == "1";
ret.Editor = r["Editor"].ToString() == "1";
ret.TeamReader = r["Team Reader"].ToString() == "1";
ret.TeamWriter = r["Team Writer"].ToString() == "1";
ret.TeamEditor = r["Team Editor"].ToString() == "1";
ret.AreaReader = r["Area Reader"].ToString() == "1";
ret.AreaWriter = r["Area Writer"].ToString() == "1";
ret.AreaEditor = r["Area Editor"].ToString() == "1";
ret.Admin = r["Admin"].ToString() == "1";
}
}
return ret;
}
public void LoadSchedule()
{
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
using (DataTable result = database.GetDataTable("dbo.ScheduleGet", new List<SqlParameter>()))
{
foreach (DataRow dr in result.Rows)
{
ScheduledRoutines.Add(new ScheduledRoutine(
dr["ScheduleID"].ToString(),
dr["RoutineID"].ToString(),
dr["Area"].ToString(),
dr["AssignedTeam"].ToString(),
dr["AssignedUser"].ToString(),
dr["DueOn"].ToString(),
dr["CompletedOn"].ToString(),
dr["CompletedBy"].ToString(),
dr["Routine"].ToString()
));
}
}
}
}
public static List<AgendaRoutineModel> GetRoutineList()
{
List<AgendaRoutineModel> rl = new List<AgendaRoutineModel>();
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
using(DataTable dt = database.GetDataTable("dbo.RoutineListGet", new List<SqlParameter>()))
{
AgendaRoutineModel r;
foreach(DataRow row in dt.Rows)
{
r = new AgendaRoutineModel();
r.ID = int.Parse(row["ID"].ToString());
r.Name = row["Name"].ToString();
r.Description = row["Description"].ToString();
r.Area = row["AreaName"].ToString();
r.CreatedBy = row["UserName"].ToString();
r.CreatedOn = row["CreatedOn"].ToString();
rl.Add(r);
}
}
}
return rl;
}
