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<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 static void Register()
{
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
database.ExecuteProcedure("dbo.RegisterUser");
}
}
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 CompleteScheduledRoutine(int ScheduleID)
{
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("@ScheduleID", SqlDbType.Int) { Value = ScheduleID });
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
database.ExecuteProcedure("dbo.ScheduledRoutineComplete", parameters);
}
}
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 void AddCommentToScheduledRoutine(int? ScheduleID, string UserComment, int? ParentID)
{
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
List<SqlParameter> parameters = new List<SqlParameter>();
if (ScheduleID != null)
parameters.Add(new System.Data.SqlClient.SqlParameter("@ScheduleID", SqlDbType.Int) { Value = ScheduleID });
parameters.Add(new System.Data.SqlClient.SqlParameter("@Comment", SqlDbType.NVarChar) { Value = UserComment.Replace("'", "''") });
if (ParentID != null)
parameters.Add(new System.Data.SqlClient.SqlParameter("@ParentCommentID", SqlDbType.Int) { Value = ParentID});
database.ExecuteProcedure("dbo.ScheduledRoutineCommentAdd", parameters);
}
}
public void AddTeam(string TeamName)
{
try
{
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new System.Data.SqlClient.SqlParameter("@TeamName", SqlDbType.NVarChar) { Value = TeamName });
database.ExecuteProcedure("dbo.TeamAdd", parameters);
}
}
catch (Exception e)
{
new EventLogger.EventLogger("Routine Management", "Application").WriteException(e);
}
}
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 int GetPrivilege()
{
int p = 1;
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
p = int.Parse(database.GetValue("dbo.PrivilegeGet", new List<System.Data.SqlClient.SqlParameter>()));
}
return p;
}
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 string GetUserName()
{
string n = "";
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
n = database.GetValue("dbo.UserNameGet", new List<System.Data.SqlClient.SqlParameter>()).ToString();
}
return n;
}
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 SaveScheduledRoutine(int scheduleID)
{
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
List<SqlParameter> parameters = new List<SqlParameter>();
DataTable fieldValues = new DataTable();
fieldValues.Columns.Add("ChechsheetID", typeof(int));
fieldValues.Columns.Add("Value", typeof(string));
fieldValues.Columns.Add("Editable", typeof(string));
for (int i = 0; i < Checksheets.Count; i++)
{
ChecksheetModel cs = Checksheets[i];
foreach (Record r in cs.Records)
{
if (r != cs.Records[0])
{
foreach (FieldValue fv in r.FieldValues)
{
DataRow fvNewRow = fieldValues.NewRow();
fvNewRow["ChechsheetID"] = 0;
if (fv.Value != null)
{
fvNewRow["Value"] = fv.Value.Replace("'", "''");
}
fvNewRow["Editable"] = fv.Editable.ToString();
fieldValues.Rows.Add(fvNewRow);
}
}
}
}
parameters.Add(new SqlParameter("@ScheduleID", SqlDbType.Int) { Value = scheduleID });
parameters.Add(new SqlParameter("@FieldValuesT", SqlDbType.Structured) { TypeName = "dbo.FieldValueList", Value = fieldValues });
database.ExecuteProcedure("dbo.ScheduledRoutineSave", parameters);
}
}
public bool Save()
{
//convert the routine into data tables and send to sql
bool success = true;
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
DataTable checksheets = new DataTable();
DataTable records = new DataTable();
DataTable fields = new DataTable();
DataTable fieldValues = new DataTable();
List<SqlParameter> parameters = new List<SqlParameter>();
checksheets.Columns.Add("ID", typeof(string));
checksheets.Columns.Add("Name", typeof(string));
checksheets.Columns.Add("Description", typeof(string));
records.Columns.Add("ChechsheetID", typeof(int));
records.Columns.Add("Name", typeof(string));
fields.Columns.Add("ChechsheetID", typeof(int));
fields.Columns.Add("Name", typeof(string));
fields.Columns.Add("TypeID", typeof(int));
fieldValues.Columns.Add("ChechsheetID", typeof(int));
fieldValues.Columns.Add("Value", typeof(string));
fieldValues.Columns.Add("Editable", typeof(string));
for (int i = 0; i < Checksheets.Count; i++)
{
ChecksheetModel cs = Checksheets[i];
string csID = (i + 1).ToString();
DataRow csNewRow = checksheets.NewRow();
csNewRow["ID"] = csID;
csNewRow["Name"] = cs.Name.Replace("'", "''");
csNewRow["Description"] = "";//cs.Description.Replace("'", "''");
checksheets.Rows.Add(csNewRow);
foreach (Field f in cs.Fields)
{
DataRow newRow = fields.NewRow();
newRow["ChechsheetID"] = csID;
newRow["Name"] = f.Name.Replace("'", "''");
newRow["TypeID"] = f.TypeID;
fields.Rows.Add(newRow);
}
foreach (Record r in cs.Records)
{
DataRow newRow = records.NewRow();
newRow["ChechsheetID"] = csID;
newRow["Name"] = r.Name.Replace("'", "''");
records.Rows.Add(newRow);
if (r != cs.Records[0])
{
foreach (FieldValue fv in r.FieldValues)
{
DataRow fvNewRow = fieldValues.NewRow();
fvNewRow["ChechsheetID"] = csID;
if (fv.Value != null)
{
fvNewRow["Value"] = fv.Value.Replace("'", "''");
}
fvNewRow["Editable"] = fv.Editable.ToString();
fieldValues.Rows.Add(fvNewRow);
}
}
}
}
int? id;
if(ID > 0)
id = ID;
else
id = null;
parameters.Add(new SqlParameter("@RoutineID", SqlDbType.Int) { Value = (object) id ?? DBNull.Value});
parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar) { Value = Name.Replace("'", "''") });
parameters.Add(new SqlParameter("@Description", SqlDbType.NVarChar) { Value = Description.Replace("'", "''") });
parameters.Add(new SqlParameter("@Checksheets", SqlDbType.Structured) { TypeName = "dbo.ChecksheetList", Value = checksheets });
parameters.Add(new SqlParameter("@Records", SqlDbType.Structured) { TypeName = "dbo.RecordList", Value = records });
parameters.Add(new SqlParameter("@Fields", SqlDbType.Structured) { TypeName = "dbo.FieldList", Value = fields });
parameters.Add(new SqlParameter("@FieldValues", SqlDbType.Structured) { TypeName = "dbo.FieldValueList", Value = fieldValues });
parameters.Add(new SqlParameter("@Area", SqlDbType.NVarChar) { Value = Area.ToString().Replace("'", "''") });
database.ExecuteProcedure("dbo.RoutineSave", parameters);
}
return success;
}
public void LoadScheduledRoutine(int ScheduleID)
{
//execute procedure to get a routine and convert data tables to C# model
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("@ScheduleID", SqlDbType.Int) { Value = ScheduleID });
using (DataSet routine = database.GetDataSet("dbo.ScheduledRoutineGet", parameters))
{
DataTable checksheets = routine.Tables[1];
DataTable fields = routine.Tables[2];
DataTable records = routine.Tables[3];
DataTable fieldvalues = routine.Tables[4];
int fieldCounter = 0;
int recordCounter = 0;
int fieldValueCounter = 0;
this.ID = int.Parse(routine.Tables[0].Rows[0]["ID"].ToString());
Name = routine.Tables[0].Rows[0]["Name"].ToString();
Description = routine.Tables[0].Rows[0]["Description"].ToString();
Completed = routine.Tables[5].Rows[0]["Completed"].ToString();
foreach (DataRow csRow in checksheets.Rows)
{
int numFields = int.Parse(csRow["FieldCount"].ToString());
int numRecords = int.Parse(csRow["RecordCount"].ToString());
ChecksheetModel checksheet = new ChecksheetModel();
checksheet.Name = csRow["ChecksheetName"].ToString();
for (int i = 0; i < numFields; i++)
{
Field field = new Field();
field.Name = fields.Rows[fieldCounter]["FieldName"].ToString();
field.TypeID = int.Parse(fields.Rows[fieldCounter++]["FieldTypeID"].ToString());
checksheet.Fields.Add(field);
}
for (int i = 0; i < numRecords; i++)
{
Record record = new Record();
record.Name = records.Rows[recordCounter++]["RecordName"].ToString();
if (i > 0)
{
for (int j = 0; j < numFields; j++)
{
FieldValue fieldValue = new FieldValue();
fieldValue.Editable = fieldvalues.Rows[fieldValueCounter]["Editable"].ToString() == "true";
fieldValue.Value = fieldvalues.Rows[fieldValueCounter++]["Value"].ToString();
record.FieldValues.Add(fieldValue);
}
}
checksheet.Records.Add(record);
}
Checksheets.Add(checksheet);
}
}
}
}
public static string ValidateRoutineName(string routinename, string area)
{
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("RoutineName", SqlDbType.NVarChar) { Value = routinename.Replace("'", "''") });
parameters.Add(new SqlParameter("Area", SqlDbType.NVarChar) { Value = area.Replace("'", "''") });
return database.GetValue("dbo.ValidateRoutineName", parameters);
}
}
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;
}
public void SaveScheduledRoutine()
{
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("@Routine", SqlDbType.NVarChar) { Value = Routine.Replace("'", "''") });
parameters.Add(new SqlParameter("@Team", SqlDbType.NVarChar) { Value = AssignedTeam.Replace("'", "''") });
if (AssignedUser != null)
parameters.Add(new SqlParameter("@User", SqlDbType.NVarChar) { Value = AssignedUser.Replace("'", "''") });
parameters.Add(new SqlParameter("@DateFor", SqlDbType.DateTime) { Value = DueOn.Replace("'", "''") });
parameters.Add(new SqlParameter("@Rate", SqlDbType.Int) { Value = Rate });
parameters.Add(new SqlParameter("@Period", SqlDbType.NVarChar) { Value = Period.Replace("'", "''") });
parameters.Add(new SqlParameter("@Number", SqlDbType.Int) { Value = Number });
database.ExecuteProcedure("dbo.ScheduleRoutine", parameters);
}
//send notification
if (AssignedUser != null)
{
new Notification(AssignedUser.Replace("'", "''"), "You have been allocated a Routine to complete").Send();
}
}
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()
));
}
}
}
}