//public static VIDEO_FILE SelectVideoFileById(decimal videoId)
//{
// var entities = new PSsqmEntities();
// return (from i in entities.VIDEO_FILE where i.VIDEO_ID == videoId select i).FirstOrDefault();
//}
/// <summary>
/// Delete all information for a specific video.
/// </summary>
/// <param name="videoId"></param>
/// <param name="fileName"></param> Filename specified on the video record. Must be provided for file extension info.
/// <returns></returns>
public static int DeleteVideo(decimal videoId, string fileName)
{
int status = 0;
string delCmd = " IN (" + videoId + ") ";
using (PSsqmEntities ctx = new PSsqmEntities())
{
try
{
// delete all attachments
List <decimal> attachmentIds = (from a in ctx.VIDEO_ATTACHMENT
where a.VIDEO_ID == videoId
select a.VIDEO_ATTACH_ID).ToList();
if (attachmentIds != null && attachmentIds.Count > 0)
{
status = ctx.ExecuteStoreCommand("DELETE FROM VIDEO_ATTACHMENT_FILE WHERE VIDEO_ATTACH_ID IN (" + String.Join(",", attachmentIds) + ")");
status = ctx.ExecuteStoreCommand("DELETE FROM VIDEO_ATTACHMENT WHERE VIDEO_ATTACH_ID IN (" + String.Join(",", attachmentIds) + ")");
}
// delete the video from the Azure blob
// Retrieve storage account from connection string.
List <SETTINGS> sets = SQMSettings.SelectSettingsGroup("MEDIA_UPLOAD", "");
string storageContainer = sets.Find(x => x.SETTING_CD == "STORAGE_CONTAINER").VALUE.ToString();
string storageURL = sets.Find(x => x.SETTING_CD == "STORAGE_URL").VALUE.ToString();
string storageQueryString = sets.Find(x => x.SETTING_CD == "STORAGE_QUERY").VALUE.ToString();
string fileType = Path.GetExtension(fileName);
try
{
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
CloudConfigurationManager.GetSetting("StorageConnectionString"));
// Create the blob client.
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
// Retrieve reference to a previously created container.
CloudBlobContainer container = blobClient.GetContainerReference(storageContainer);
// Retrieve reference to a blob named "myblob.txt".
CloudBlockBlob blockBlob = container.GetBlockBlobReference(videoId.ToString() + fileType);
// Delete the blob.
blockBlob.Delete();
}
catch (Exception videoEx)
{
// what to do if there is an exception? Just log the exception for now
SQMLogger.LogException(videoEx);
}
// delete the video header
status = ctx.ExecuteStoreCommand("DELETE FROM VIDEO WHERE VIDEO_ID" + delCmd);
}
catch (Exception ex)
{
SQMLogger.LogException(ex);
}
}
return(status);
}
public int UpdateCausation(decimal incidentID)
{
int status = 0;
using (PSsqmEntities ctx = new PSsqmEntities())
{
status = ctx.ExecuteStoreCommand("DELETE FROM INCFORM_CAUSATION WHERE INCIDENT_ID = " + incidentID.ToString());
if (!string.IsNullOrEmpty(ddlCausation.SelectedValue))
{
INCFORM_CAUSATION causation = new INCFORM_CAUSATION();
causation.INCIDENT_ID = incidentID;
causation.CAUSEATION_CD = ddlCausation.SelectedValue;
causation.TEAM_LIST = tbTeam.Text.Trim();
causation.LAST_UPD_BY = SessionManager.UserContext.UserName();
causation.LAST_UPD_DT = WebSiteCommon.LocalTime(DateTime.UtcNow, IncidentLocationTZ);
ctx.AddToINCFORM_CAUSATION(causation);
status = ctx.SaveChanges();
EHSIncidentMgr.UpdateIncidentStatus(incidentID, IncidentStepStatus.rootcauseComplete, WebSiteCommon.LocalTime(DateTime.UtcNow, IncidentLocationTZ));
}
else
{
}
}
return(status);
}
protected int SaveRootCauses(decimal incidentId, List <INCFORM_ROOT5Y> itemList)
{
PSsqmEntities entities = new PSsqmEntities();
int status = 0;
using (var ctx = new PSsqmEntities())
{
ctx.ExecuteStoreCommand("DELETE FROM INCFORM_ROOT5Y WHERE INCIDENT_ID = {0}", incidentId);
}
int seq = 0;
foreach (INCFORM_ROOT5Y item in itemList)
{
var newItem = new INCFORM_ROOT5Y();
if (!string.IsNullOrEmpty(item.ITEM_DESCRIPTION))
{
seq = seq + 1;
newItem.INCIDENT_ID = incidentId;
newItem.ITEM_SEQ = seq;
newItem.ITEM_DESCRIPTION = item.ITEM_DESCRIPTION;
newItem.LAST_UPD_BY = SessionManager.UserContext.Person.FIRST_NAME + " " + SessionManager.UserContext.Person.LAST_NAME;
newItem.LAST_UPD_DT = DateTime.Now;
entities.AddToINCFORM_ROOT5Y(newItem);
status = entities.SaveChanges();
}
}
return(status);
}
private int SaveContainment(decimal incidentId, List <INCFORM_CONTAIN> itemList)
{
PSsqmEntities entities = new PSsqmEntities();
int status = 0;
using (var ctx = new PSsqmEntities())
{
ctx.ExecuteStoreCommand("DELETE FROM INCFORM_CONTAIN WHERE INCIDENT_ID = {0}", incidentId);
}
int seq = 0;
foreach (INCFORM_CONTAIN item in itemList)
{
var newItem = new INCFORM_CONTAIN();
if (!string.IsNullOrEmpty(item.ITEM_DESCRIPTION))
{
seq = seq + 1;
newItem.INCIDENT_ID = incidentId;
newItem.ITEM_SEQ = seq;
newItem.ITEM_DESCRIPTION = item.ITEM_DESCRIPTION;
newItem.ASSIGNED_PERSON_ID = item.ASSIGNED_PERSON_ID;
newItem.START_DATE = item.START_DATE;
newItem.LAST_UPD_BY = SessionManager.UserContext.Person.FIRST_NAME + " " + SessionManager.UserContext.Person.LAST_NAME;
newItem.LAST_UPD_DT = DateTime.Now;
entities.AddToINCFORM_CONTAIN(newItem);
status = entities.SaveChanges();
}
}
return(status);
}
protected void btnConvert_Click(object sender, EventArgs e)
{
// convert preventative actions to new format
int status = 0;
PSsqmEntities ctx = new PSsqmEntities();
status = ctx.ExecuteStoreCommand("update incident set INCFORM_LAST_STEP_COMPLETED = 130 where incident_id in (select i.incident_id from incident i, INCIDENT_ANSWER a where i.ISSUE_TYPE_ID = 13 and i.INCIDENT_ID = a.INCIDENT_ID and a.INCIDENT_QUESTION_ID = 93 and a.ANSWER_VALUE = 'In Progress')");
status = ctx.ExecuteStoreCommand("update incident set INCFORM_LAST_STEP_COMPLETED = 135 where incident_id in (select i.incident_id from incident i, INCIDENT_ANSWER a where i.ISSUE_TYPE_ID = 13 and i.INCIDENT_ID = a.INCIDENT_ID and a.INCIDENT_QUESTION_ID = 93 and a.ANSWER_VALUE = 'Closed')");
status = ctx.ExecuteStoreCommand("update incident set INCFORM_LAST_STEP_COMPLETED = 155 where incident_id in (select i.incident_id from incident i, INCIDENT_ANSWER a where i.ISSUE_TYPE_ID = 13 and i.INCIDENT_ID = a.INCIDENT_ID and a.INCIDENT_QUESTION_ID = 88 and a.ANSWER_VALUE = 'Yes')");
status = ctx.ExecuteStoreCommand("update incident set INCFORM_LAST_STEP_COMPLETED = 155 where incident_id in (select i.incident_id from incident i, INCIDENT_ANSWER a where i.ISSUE_TYPE_ID = 13 and i.INCIDENT_ID = a.INCIDENT_ID and a.INCIDENT_QUESTION_ID = 88 and a.ANSWER_VALUE = 'Awaiting Funding')");
status = ctx.ExecuteStoreCommand("update incident set INCFORM_LAST_STEP_COMPLETED = 100 where ISSUE_TYPE_ID = 13 and INCFORM_LAST_STEP_COMPLETED is null");
}
protected void rptAction_ItemCommand(object source, RepeaterCommandEventArgs e)
{
List <TASK_STATUS> actionList = GetActionListFromGrid();
if (e.CommandArgument.ToString() == "ActionType")
{
Panel pnl = (Panel)e.Item.FindControl("pnlActionType");
if (pnl.Visible)
{
pnl.Visible = false;
pnl = (Panel)e.Item.FindControl("pnlActionTypeSelect");
pnl.Visible = true;
HiddenField hf = (HiddenField)e.Item.FindControl("hfActionType");
XLAT xlat = XLATList.Where(l => l.XLAT_GROUP == "ACTION_CATEGORY" && l.XLAT_CODE == hf.Value).FirstOrDefault();
if (xlat != null)
{
Label lb = (Label)e.Item.FindControl("lblActionType");
lb.Text = xlat.DESCRIPTION;
}
}
else
{
pnl.Visible = true;
pnl = (Panel)e.Item.FindControl("pnlActionTypeSelect");
pnl.Visible = false;
}
}
if (e.CommandArgument.ToString() == "Delete")
{
int delId = e.Item.ItemIndex;
TASK_STATUS action = actionList.ElementAt(delId);
if (action != null)
{
if (action.TASK_ID > 0) // only delete existing actions
{
using (PSsqmEntities entities = new PSsqmEntities())
{
entities.ExecuteStoreCommand("DELETE FROM TASK_STATUS WHERE TASK_ID = " + action.TASK_ID.ToString());
}
}
actionList.Remove(action);
if (actionList.Count == 0)
{
actionList.Add(EHSIncidentMgr.CreateEmptyTask(ActionIncident.INCIDENT_ID, ((int)SysPriv.action).ToString(), 1, WebSiteCommon.LocalTime(DateTime.UtcNow, IncidentLocationTZ)));
}
}
rptAction.DataSource = actionList;
rptAction.DataBind();
decimal incidentId = (IsEditContext) ? IncidentId : NewIncidentId;
}
}
public static int UpdateVideoAttachmentRecordID(PSsqmEntities entities, int recordType, string sessionID, decimal recordID)
{
int status = 0;
try
{
status = entities.ExecuteStoreCommand("UPDATE VIDEO_ATTACHMENT SET VIDEO_ID = " + recordID.ToString() + " WHERE RECORD_TYPE = " + recordType.ToString() + " AND SESSION_ID = '" + sessionID + "'");
}
catch (Exception e)
{
//SQMLogger.LogException(e);
}
return(status);
}
public static int DeleteIncident(decimal incidentId)
{
int status = 0;
string delCmd = " IN (" + incidentId + ") ";
using (PSsqmEntities ctx = new PSsqmEntities())
{
try
{
decimal probCaseId = (from po in ctx.PROB_OCCUR where po.INCIDENT_ID == incidentId select po.PROBCASE_ID).FirstOrDefault();
if (probCaseId > 0)
{
status = ProblemCase.DeleteProblemCase(probCaseId);
}
List <decimal> attachmentIds = (from a in ctx.ATTACHMENT where a.RECORD_TYPE == 40 && a.RECORD_ID == incidentId
select a.ATTACHMENT_ID).ToList();
if (attachmentIds != null && attachmentIds.Count > 0)
{
status = ctx.ExecuteStoreCommand("DELETE FROM ATTACHMENT_FILE WHERE ATTACHMENT_ID IN (" + String.Join(",", attachmentIds) + ")");
status = ctx.ExecuteStoreCommand("DELETE FROM ATTACHMENT WHERE ATTACHMENT_ID IN (" + String.Join(",", attachmentIds) + ")");
}
status = ctx.ExecuteStoreCommand("DELETE FROM PROB_OCCUR WHERE INCIDENT_ID" + delCmd);
status = ctx.ExecuteStoreCommand("DELETE FROM INCIDENT_ANSWER WHERE INCIDENT_ID" + delCmd);
status = ctx.ExecuteStoreCommand("DELETE FROM INCIDENT WHERE INCIDENT_ID" + delCmd);
}
catch (Exception ex)
{
SQMLogger.LogException(ex);
}
}
return(status);
}
public int AddUpdateINCFORM_APPROVAL(decimal incidentId)
{
var itemList = new List <INCFORM_APPROVAL>();
int status = 0;
using (PSsqmEntities ctx = new PSsqmEntities())
{
status = ctx.ExecuteStoreCommand("DELETE FROM INCFORM_APPROVAL WHERE INCIDENT_ID = " + incidentId.ToString());
foreach (RepeaterItem item in rptApprovals.Items)
{
HiddenField hf = (HiddenField)item.FindControl("hfItemSeq");
Label lba = (Label)item.FindControl("lbApprover");
Label lbm = (Label)item.FindControl("lbApproveMessage");
Label lb = (Label)item.FindControl("lbItemSeq");
Label lbjobd = (Label)item.FindControl("lbApproverJob");
CheckBox cba = (CheckBox)item.FindControl("cbIsAccepted");
RadDatePicker rda = (RadDatePicker)item.FindControl("rdpAcceptDate");
if (cba.Checked == true)
{
INCFORM_APPROVAL approval = new INCFORM_APPROVAL();
approval.INCIDENT_ID = incidentId;
approval.ITEM_SEQ = Convert.ToInt32(hf.Value);
approval.IsAccepted = true;
approval.APPROVAL_MESSAGE = lbm.Text;
approval.APPROVER_TITLE = lbjobd.Text;
approval.APPROVAL_DATE = rda.SelectedDate;
hf = (HiddenField)item.FindControl("hfPersonID");
if (string.IsNullOrEmpty(hf.Value) || hf.Value == "0")
{
approval.APPROVER_PERSON_ID = SessionManager.UserContext.Person.PERSON_ID;
approval.APPROVER_PERSON = SessionManager.UserContext.UserName();
}
else
{
approval.APPROVER_PERSON_ID = Convert.ToDecimal(hf.Value);
approval.APPROVER_PERSON = lba.Text;
}
ctx.AddToINCFORM_APPROVAL(approval);
}
}
status = ctx.SaveChanges();
}
return(status);
}
private int SaveLostTime(decimal incidentId, List <INCFORM_LOSTTIME_HIST> itemList)
{
int status = 0;
PSsqmEntities entities = new PSsqmEntities();
using (var ctx = new PSsqmEntities())
{
ctx.ExecuteStoreCommand("DELETE FROM INCFORM_LOSTTIME_HIST WHERE INCIDENT_ID = {0}", incidentId);
}
int seq = 0;
foreach (INCFORM_LOSTTIME_HIST item in itemList)
{
var newItem = new INCFORM_LOSTTIME_HIST();
if (!String.IsNullOrEmpty(item.WORK_STATUS) && item.WORK_STATUS != "")
{
newItem.INCIDENT_ID = incidentId;
newItem.ITEM_DESCRIPTION = item.ITEM_DESCRIPTION;
newItem.WORK_STATUS = item.WORK_STATUS;
newItem.BEGIN_DT = item.BEGIN_DT;
newItem.RETURN_TOWORK_DT = item.RETURN_TOWORK_DT;
newItem.NEXT_MEDAPPT_DT = item.NEXT_MEDAPPT_DT;
newItem.RETURN_EXPECTED_DT = item.RETURN_EXPECTED_DT;
newItem.LAST_UPD_BY = SessionManager.UserContext.Person.FIRST_NAME + " " + SessionManager.UserContext.Person.LAST_NAME;
newItem.LAST_UPD_DT = WebSiteCommon.LocalTime(DateTime.UtcNow, IncidentLocationTZ);
entities.AddToINCFORM_LOSTTIME_HIST(newItem);
status = entities.SaveChanges();
}
}
if (seq > 0)
{
EHSIncidentMgr.UpdateIncidentStatus(incidentId, IncidentStepStatus.workstatus, WebSiteCommon.LocalTime(DateTime.UtcNow, IncidentLocationTZ));
}
/*
* if (status > -1)
* {
* EHSNotificationMgr.NotifyIncidentStatus(WorkStatusIncident, ((int)SysPriv.update).ToString(), "Work status updated");
* }
*/
return(status);
}
public static int UpdateVideoAttachmentDisplayType(decimal videoAttachID, decimal displayType)
{
int status = 0;
try
{
using (PSsqmEntities entities = new PSsqmEntities())
{
status = entities.ExecuteStoreCommand("UPDATE VIDEO_ATTACHMENT SET DISPLAY_TYPE = " + displayType + " WHERE VIDEO_ATTACH_ID = " + videoAttachID);
}
}
catch (Exception e)
{
//SQMLogger.LogException(e);
}
return(status);
}
public static int DeleteAuditScheduler(decimal auditScheduleId)
{
int status = 0;
string delCmd = " IN (" + auditScheduleId + ") ";
using (PSsqmEntities ctx = new PSsqmEntities())
{
try
{
status = ctx.ExecuteStoreCommand("DELETE FROM AUDIT_SCHEDULER WHERE AUDIT_SCHEDULER_ID" + delCmd);
}
catch (Exception ex)
{
SQMLogger.LogException(ex);
}
}
return(status);
}
private int SaveLostTime(decimal incidentId, List <INCFORM_LOSTTIME_HIST> itemList)
{
int status = 0;
PSsqmEntities entities = new PSsqmEntities();
using (var ctx = new PSsqmEntities())
{
ctx.ExecuteStoreCommand("DELETE FROM INCFORM_LOSTTIME_HIST WHERE INCIDENT_ID = {0}", incidentId);
}
int seq = 0;
foreach (INCFORM_LOSTTIME_HIST item in itemList)
{
var newItem = new INCFORM_LOSTTIME_HIST();
if (!String.IsNullOrEmpty(item.WORK_STATUS) && item.WORK_STATUS != "[Select One]")
{
seq = seq + 1;
newItem.INCIDENT_ID = incidentId;
newItem.ITEM_SEQ = seq;
newItem.ITEM_DESCRIPTION = item.ITEM_DESCRIPTION;
newItem.WORK_STATUS = item.WORK_STATUS;
newItem.BEGIN_DT = item.BEGIN_DT;
newItem.RETURN_TOWORK_DT = item.RETURN_TOWORK_DT;
newItem.NEXT_MEDAPPT_DT = item.NEXT_MEDAPPT_DT;
newItem.RETURN_EXPECTED_DT = item.RETURN_EXPECTED_DT;
newItem.LAST_UPD_BY = SessionManager.UserContext.Person.FIRST_NAME + " " + SessionManager.UserContext.Person.LAST_NAME;
newItem.LAST_UPD_DT = DateTime.Now;
entities.AddToINCFORM_LOSTTIME_HIST(newItem);
status = entities.SaveChanges();
}
}
return(status);
}
protected int SaveRootCauses(decimal incidentId, List <INCFORM_ROOT5Y> itemList)
{
PSsqmEntities entities = new PSsqmEntities();
int status = 0;
using (var ctx = new PSsqmEntities())
{
ctx.ExecuteStoreCommand("DELETE FROM INCFORM_ROOT5Y WHERE INCIDENT_ID = {0}", incidentId);
}
int seq = 0;
foreach (INCFORM_ROOT5Y item in itemList)
{
var newItem = new INCFORM_ROOT5Y();
if (!string.IsNullOrEmpty(item.ITEM_DESCRIPTION))
{
seq = seq + 1;
newItem.INCIDENT_ID = incidentId;
newItem.ITEM_SEQ = seq;
newItem.ITEM_TYPE = item.ITEM_TYPE;
newItem.PROBLEM_SERIES = item.PROBLEM_SERIES;
newItem.ITEM_DESCRIPTION = item.ITEM_DESCRIPTION;
newItem.IS_ROOTCAUSE = item.IS_ROOTCAUSE;
newItem.LAST_UPD_BY = SessionManager.UserContext.Person.FIRST_NAME + " " + SessionManager.UserContext.Person.LAST_NAME;
newItem.LAST_UPD_DT = WebSiteCommon.LocalTime(DateTime.UtcNow, IncidentLocationTZ);
entities.AddToINCFORM_ROOT5Y(newItem);
status = entities.SaveChanges();
}
}
if (seq > 0)
{
EHSIncidentMgr.UpdateIncidentStatus(incidentId, IncidentStepStatus.rootcause, WebSiteCommon.LocalTime(DateTime.UtcNow, IncidentLocationTZ));
}
return(status);
}
private int SaveActions(decimal incidentId, List <INCFORM_ACTION> itemList)
{
PSsqmEntities entities = new PSsqmEntities();
int status = 0;
using (var ctx = new PSsqmEntities())
{
ctx.ExecuteStoreCommand("DELETE FROM INCFORM_ACTION WHERE INCIDENT_ID = {0}", incidentId);
}
int seq = 0;
foreach (INCFORM_ACTION item in itemList)
{
var newItem = new INCFORM_ACTION();
if (!string.IsNullOrEmpty(item.ITEM_DESCRIPTION))
{
seq = seq + 1;
newItem.INCIDENT_ID = incidentId;
newItem.ITEM_SEQ = seq;
newItem.ITEM_DESCRIPTION = item.ITEM_DESCRIPTION;
newItem.ASSIGNED_PERSON_ID = item.ASSIGNED_PERSON_ID;
newItem.START_DATE = item.START_DATE;
newItem.COMPLETION_DATE = item.COMPLETION_DATE;
newItem.IsCompleted = item.IsCompleted;
newItem.LAST_UPD_BY = SessionManager.UserContext.Person.FIRST_NAME + " " + SessionManager.UserContext.Person.LAST_NAME;
newItem.LAST_UPD_DT = DateTime.Now;
entities.AddToINCFORM_ACTION(newItem);
status = entities.SaveChanges();
DateTime dueDate = newItem.START_DATE.HasValue ? (DateTime)newItem.START_DATE : DateTime.Now.AddDays(2);
EHSIncidentMgr.CreateOrUpdateTask(incidentId, (decimal)item.ASSIGNED_PERSON_ID, 40, dueDate, item.ITEM_DESCRIPTION);
}
}
return(status);
}
public static int DeleteProblemCase(decimal probcaseID)
{
int status = 0;
string delCmd = " IN (" + probcaseID.ToString() + ") ";
using (PSsqmEntities ctx = new PSsqmEntities())
{
try
{
status = ctx.ExecuteStoreCommand("DELETE FROM TASK WHERE TASKLIST_ID IN (SELECT TASKLIST_ID FROM TASKLIST WHERE RECORD_TYPE = 21 AND RECORD_ID " + delCmd + " )");
status = ctx.ExecuteStoreCommand("DELETE FROM TASKLIST WHERE RECORD_TYPE = 21 AND RECORD_ID " + delCmd);
status = ctx.ExecuteStoreCommand("DELETE FROM PROB_CLOSE WHERE PROBCASE_ID" + delCmd);
status = ctx.ExecuteStoreCommand("DELETE FROM PROB_PREVENT_LIST WHERE PROBCASE_ID" + delCmd);
status = ctx.ExecuteStoreCommand("DELETE FROM PROB_PREVENT WHERE PROBCASE_ID" + delCmd);
status = ctx.ExecuteStoreCommand("DELETE FROM PROB_VERIFY_VERS WHERE PROBCASE_ID" + delCmd);
status = ctx.ExecuteStoreCommand("DELETE FROM PROB_VERIFY WHERE PROBCASE_ID" + delCmd);
status = ctx.ExecuteStoreCommand("DELETE FROM PROB_CAUSE_ACTION_VERS WHERE PROBCASE_ID" + delCmd);
status = ctx.ExecuteStoreCommand("DELETE FROM PROB_CAUSE_ACTION WHERE PROBCASE_ID" + delCmd);
status = ctx.ExecuteStoreCommand("DELETE FROM PROB_CAUSE_STEP WHERE PROBCASE_ID" + delCmd);
status = ctx.ExecuteStoreCommand("DELETE FROM PROB_CAUSE WHERE PROBCASE_ID" + delCmd);
status = ctx.ExecuteStoreCommand("DELETE FROM PROB_CONTAIN_ACTION WHERE PROBCASE_ID" + delCmd);
status = ctx.ExecuteStoreCommand("DELETE FROM PROB_CONTAIN WHERE PROBCASE_ID" + delCmd);
status = ctx.ExecuteStoreCommand("DELETE FROM PROB_RISK WHERE PROBCASE_ID" + delCmd);
status = ctx.ExecuteStoreCommand("DELETE FROM PROB_DEFINE WHERE PROBCASE_ID" + delCmd);
status = ctx.ExecuteStoreCommand("DELETE FROM PROB_OCCUR WHERE PROBCASE_ID" + delCmd);
status = ctx.ExecuteStoreCommand("DELETE FROM PROB_CASE WHERE PROBCASE_ID" + delCmd);
}
catch (Exception ex)
{
// SQMLogger.LogException(ex);
}
}
return(status);
}
/// <summary>
/// Delete a ALL Video and attachment records for a specific source (Incident/Audit)
/// </summary>
/// <param name="sourceId"></param>
/// <param name="sourceType"></param>
/// <param name="sourceStep"></param> This is the question Id for Audit videos. If left blank, all videos for a specific audit will be deleted
/// <returns></returns>
public static int DeleteAllSourceVideos(decimal sourceId, int sourceType, string sourceStep)
{
int status = 0;
var videos = new List <VIDEO>();
using (PSsqmEntities ctx = new PSsqmEntities())
{
try
{
// select all videos for a source Id & source type
if (sourceStep.Trim().Length > 0)
{
videos = (from i in ctx.VIDEO
where (i.SOURCE_ID == sourceId && i.SOURCE_TYPE == sourceType && i.SOURCE_STEP == sourceStep.Trim())
orderby i.VIDEO_ID descending
select i).ToList();
}
else
{
videos = (from i in ctx.VIDEO
where (i.SOURCE_ID == sourceId && i.SOURCE_TYPE == sourceType)
orderby i.VIDEO_ID descending
select i).ToList();
}
decimal[] ids = videos.Select(v => v.VIDEO_ID).Distinct().ToArray();
// delete all attachments for all the videos
List <decimal> attachmentIds = (from a in ctx.VIDEO_ATTACHMENT
where ids.Contains(a.VIDEO_ID)
select a.VIDEO_ATTACH_ID).ToList();
if (attachmentIds != null && attachmentIds.Count > 0)
{
status = ctx.ExecuteStoreCommand("DELETE FROM VIDEO_ATTACHMENT_FILE WHERE VIDEO_ATTACH_ID IN (" + String.Join(",", attachmentIds) + ")");
status = ctx.ExecuteStoreCommand("DELETE FROM VIDEO_ATTACHMENT WHERE VIDEO_ATTACH_ID IN (" + String.Join(",", attachmentIds) + ")");
}
// delete the videos from the Azure blob
// Retrieve storage account from connection string.
List <SETTINGS> sets = SQMSettings.SelectSettingsGroup("MEDIA_UPLOAD", "");
string storageContainer = sets.Find(x => x.SETTING_CD == "STORAGE_CONTAINER").VALUE.ToString();
string storageURL = sets.Find(x => x.SETTING_CD == "STORAGE_URL").VALUE.ToString();
string storageQueryString = sets.Find(x => x.SETTING_CD == "STORAGE_QUERY").VALUE.ToString();
foreach (VIDEO video in videos)
{
string fileType = Path.GetExtension(video.FILE_NAME);
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
CloudConfigurationManager.GetSetting("StorageConnectionString"));
// Create the blob client.
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
// Retrieve reference to a previously created container.
CloudBlobContainer container = blobClient.GetContainerReference(storageContainer);
// Retrieve reference to a blob named "myblob.txt".
CloudBlockBlob blockBlob = container.GetBlockBlobReference(video.VIDEO_ID.ToString() + fileType);
// Delete the blob.
blockBlob.Delete();
}
// delete the video headers
if (sourceStep.Trim().Length > 0)
{
status = ctx.ExecuteStoreCommand("DELETE FROM VIDEO WHERE SOURCE_ID = " + sourceId + " AND SOURCE_TYPE = " + sourceType + " AND SOURCE_STEP = '" + sourceStep.Trim() + "'");
}
else
{
status = ctx.ExecuteStoreCommand("DELETE FROM VIDEO WHERE SOURCE_ID = " + sourceId + " AND SOURCE_TYPE = " + sourceType);
}
}
catch (Exception ex)
{
SQMLogger.LogException(ex);
}
}
return(status);
}
protected void Delete_Click(object sender, EventArgs e)
{
localCtx.ExecuteStoreCommand("DELETE FROM INCFORM_ALERT WHERE INCIDENT_ID = {0}", IncidentId.ToString());
}