public static void Register()
{
using (SqlServer database = new SqlServer(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
database.ExecuteProcedure("dbo.RegisterUser");
}
}
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 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 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 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;
}
