static string OverdueTaskNotifications(DateTime currentTime) { string nextStep = ""; DateTime openFromDate = new DateTime(2000, 1, 1); DateTime localTime; int execAtHour = 10; // set exec time default to 5 am EST WriteLine("OVERDUE TASK NOTIFICATIONS Started: " + DateTime.UtcNow.ToString("hh:mm MM/dd/yyyy")); SETTINGS setting = null; List <SETTINGS> sets = SQMSettings.SelectSettingsGroup("AUTOMATE", ""); try { // execute at specified hour of day (e.g. TASKNOTIFY_TIME == 01:00) if ((setting = sets.Where(x => x.SETTING_CD == "TASKNOTIFY_TIME").FirstOrDefault()) != null) { // throwing an error here if the setting format was incorrect will be helpful for debugging execAtHour = int.Parse(WebSiteCommon.SplitString(setting.VALUE, ':').First()); } entities = new PSsqmEntities(); List <PLANT> plantList = SQMModelMgr.SelectPlantList(entities, 1, 0); PLANT plant = null; List <TaskItem> openTaskList = TaskMgr.SelectOpenTasks(currentTime, openFromDate); if (openTaskList.Count > 0) { WriteLine("Open Tasks ..."); foreach (TaskItem taskItem in openTaskList) { try { if (taskItem.Task.RECORD_TYPE == (int)TaskRecordType.HealthSafetyIncident) { INCIDENT incident = EHSIncidentMgr.SelectIncidentById(entities, taskItem.Task.RECORD_ID); if (incident != null && (plant = plantList.Where(l => l.PLANT_ID == incident.DETECT_PLANT_ID).FirstOrDefault()) != null) { if (execAtHour == WebSiteCommon.LocalTime(currentTime, plant.LOCAL_TIMEZONE).Hour) { WriteLine("Task: " + taskItem.Task.TASK_ID.ToString() + " RecordType: " + taskItem.Task.RECORD_TYPE.ToString() + " " + "RecordID:" + taskItem.Task.RECORD_ID.ToString() + " Status = " + taskItem.Task.STATUS); // notify assigned person and escalation person if over-over due EHSNotificationMgr.NotifyIncidentTaskStatus(incident, taskItem, ((int)SysPriv.action).ToString()); System.Threading.Thread.Sleep(timer); //will wait for 2 seconds to allow Google Mail to process email requests if (taskItem.Taskstatus >= SQM.Website.TaskStatus.Overdue) { // send to notification list for plant, BU, ... //EHSNotificationMgr.NotifyIncidentStatus(incident, taskItem.Task.TASK_STEP, ((int)SysPriv.notify).ToString(), ""); //System.Threading.Thread.Sleep(timer); //will wait for 2 seconds to allow Google Mail to process email requests } } } else { WriteLine("Task: " + taskItem.Task.TASK_ID.ToString() + " RecordType: " + taskItem.Task.RECORD_TYPE.ToString() + " " + "RecordID:" + taskItem.Task.RECORD_ID.ToString() + " Status = " + taskItem.Task.STATUS + " This task does not have a corresponding incident or plant record"); } } else if (taskItem.Task.RECORD_TYPE == (int)TaskRecordType.PreventativeAction) { INCIDENT incident = EHSIncidentMgr.SelectIncidentById(entities, taskItem.Task.RECORD_ID); if (incident != null && (plant = plantList.Where(l => l.PLANT_ID == incident.DETECT_PLANT_ID).FirstOrDefault()) != null) { if (execAtHour == WebSiteCommon.LocalTime(currentTime, plant.LOCAL_TIMEZONE).Hour) { WriteLine("Task: " + taskItem.Task.TASK_ID.ToString() + " RecordType: " + taskItem.Task.RECORD_TYPE.ToString() + " " + "RecordID:" + taskItem.Task.RECORD_ID.ToString() + " Status = " + taskItem.Task.STATUS); // notify assigned person and escalation person if over-over due EHSNotificationMgr.NotifyPrevActionTaskStatus(incident, taskItem, ((int)SysPriv.action).ToString()); System.Threading.Thread.Sleep(timer); //will wait for 2 seconds to allow Google Mail to process email requests } } else { WriteLine("Task: " + taskItem.Task.TASK_ID.ToString() + " RecordType: " + taskItem.Task.RECORD_TYPE.ToString() + " " + "RecordID:" + taskItem.Task.RECORD_ID.ToString() + " Status = " + taskItem.Task.STATUS + " This task does not have a corresponding preventative action or plant record"); } } else if (taskItem.Task.RECORD_TYPE == (int)TaskRecordType.Audit) { AUDIT audit = EHSAuditMgr.SelectAuditById(entities, taskItem.Task.RECORD_ID); if (audit != null && (plant = plantList.Where(l => l.PLANT_ID == audit.DETECT_PLANT_ID).FirstOrDefault()) != null) { if (execAtHour == WebSiteCommon.LocalTime(currentTime, plant.LOCAL_TIMEZONE).Hour) { WriteLine("Task: " + taskItem.Task.TASK_ID.ToString() + " RecordType: " + taskItem.Task.RECORD_TYPE.ToString() + " " + "RecordID:" + taskItem.Task.RECORD_ID.ToString() + " Status = " + taskItem.Task.STATUS); if (taskItem.Task.TASK_STEP == "0") { EHSNotificationMgr.NotifyAuditStatus(audit, taskItem); System.Threading.Thread.Sleep(timer); //will wait for 2 seconds to allow Google Mail to process email requests } else { EHSNotificationMgr.NotifyAuditTaskStatus(audit, taskItem, ((int)SysPriv.action).ToString()); System.Threading.Thread.Sleep(timer); //will wait for 2 seconds to allow Google Mail to process email requests } } } else { WriteLine("Task: " + taskItem.Task.TASK_ID.ToString() + " RecordType: " + taskItem.Task.RECORD_TYPE.ToString() + " " + "RecordID:" + taskItem.Task.RECORD_ID.ToString() + " Status = " + taskItem.Task.STATUS + " This task does not have a corresponding audit or plant record"); } } } catch (Exception ex) { WriteLine("Error: " + ex.ToString()); } } } } catch (Exception ex) { WriteLine("OVERDUE TASK NOTIFICATIONS Error - " + ex.ToString()); } WriteLine("OVERDUE TASK NOTIFICATIONS Completed: " + DateTime.UtcNow.ToString("hh:mm MM/dd/yyyy")); return(nextStep); }
static string ProcessIncidents() { PSsqmEntities entities = new PSsqmEntities(); SETTINGS setting = null; int workdays = 7; int rollupMonthsAhead = 0; string nextStep = ""; DateTime fromDate = DateTime.UtcNow.AddMonths(-11); // set the incident 'select from' date. TODO: get this from SETTINGS table // set end date to end of current month to clear spurrious entries ? DateTime rollupToDate = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.DaysInMonth(DateTime.UtcNow.Year, DateTime.UtcNow.Month)); WriteLine("INCIDENT Rollup Started: " + DateTime.UtcNow.ToString("hh:mm MM/dd/yyyy")); List <SETTINGS> sets = SQMSettings.SelectSettingsGroup("AUTOMATE", ""); // ABW 20140805 try { setting = sets.Where(x => x.SETTING_CD == "ROLLUP_WORKDAYS").FirstOrDefault(); if (setting != null && !string.IsNullOrEmpty(setting.VALUE)) { if (!int.TryParse(setting.VALUE, out workdays)) { workdays = 7; } } setting = sets.Where(x => x.SETTING_CD == "ROLLUP_NEXTPAGE").FirstOrDefault(); if (setting != null && !string.IsNullOrEmpty(setting.VALUE) && setting.VALUE.Length > 1) { nextStep = setting.VALUE; } // fetch all incidents occurring after the minimum reporting date List <INCIDENT> incidentList = (from i in entities.INCIDENT.Include("INCFORM_INJURYILLNESS") where i.ISSUE_TYPE_ID != (decimal)EHSIncidentTypeId.PreventativeAction && i.INCIDENT_DT >= fromDate && i.DETECT_PLANT_ID > 0 select i).OrderBy(l => l.DETECT_PLANT_ID).ThenBy(l => l.INCIDENT_DT).ToList(); List <PLANT> plantList = SQMModelMgr.SelectPlantList(entities, 1, 0); PLANT plant = null; // fetch all the plant accounting records for the target timespan DateTime minDate = incidentList.Select(l => l.INCIDENT_DT).Min(); minDate = minDate.AddMonths(-1); PLANT_ACCOUNTING pa = null; List <PLANT_ACCOUNTING> paList = (from a in entities.PLANT_ACCOUNTING where EntityFunctions.CreateDateTime(a.PERIOD_YEAR, a.PERIOD_MONTH, 1, 0, 0, 0) >= minDate && EntityFunctions.CreateDateTime(a.PERIOD_YEAR, a.PERIOD_MONTH, 1, 0, 0, 0) <= rollupToDate select a).OrderBy(l => l.PLANT_ID).ThenBy(l => l.PERIOD_YEAR).ThenBy(l => l.PERIOD_MONTH).ToList(); List <EHSIncidentTimeAccounting> summaryList = new List <EHSIncidentTimeAccounting>(); foreach (INCIDENT incident in incidentList) { WriteLine("Incident ID: " + incident.INCIDENT_ID.ToString() + " Occur Date: " + Convert.ToDateTime(incident.INCIDENT_DT).ToShortDateString()); incident.INCFORM_CAUSATION.Load(); if (incident.ISSUE_TYPE_ID == (decimal)EHSIncidentTypeId.InjuryIllness) { incident.INCFORM_LOSTTIME_HIST.Load(); } plant = plantList.Where(l => l.PLANT_ID == (decimal)incident.DETECT_PLANT_ID).FirstOrDefault(); summaryList = EHSIncidentMgr.SummarizeIncidentAccounting(summaryList, EHSIncidentMgr.CalculateIncidentAccounting(incident, plant.LOCAL_TIMEZONE, workdays)); } plant = null; PLANT_ACTIVE pact = null; DateTime periodDate; foreach (PLANT_ACCOUNTING pah in paList.OrderBy(l => l.PLANT_ID).ToList()) { if (pact == null || pact.PLANT_ID != pah.PLANT_ID) { pact = (from a in entities.PLANT_ACTIVE where a.PLANT_ID == pah.PLANT_ID && a.RECORD_TYPE == (int)TaskRecordType.HealthSafetyIncident select a).SingleOrDefault(); } //if (pact != null && pact.EFF_END_DATE.HasValue && new DateTime(pah.PERIOD_YEAR, pah.PERIOD_MONTH, 1).Date >= ((DateTime)pact.EFF_START_DATE).Date) if (pact != null && pact.EFF_START_DATE.HasValue && new DateTime(pah.PERIOD_YEAR, pah.PERIOD_MONTH, 1).Date >= ((DateTime)pact.EFF_START_DATE).Date) { pah.TIME_LOST = pah.TOTAL_DAYS_RESTRICTED = 0; pah.TIME_LOST_CASES = pah.RECORDED_CASES = pah.FIRST_AID_CASES = 0; } } plant = null; pact = null; foreach (EHSIncidentTimeAccounting period in summaryList.OrderBy(l => l.PlantID).ThenBy(l => l.PeriodYear).ThenBy(l => l.PeriodMonth).ToList()) { if (plant == null || plant.PLANT_ID != period.PlantID) { plant = plantList.Where(l => l.PLANT_ID == period.PlantID).FirstOrDefault(); pact = (from a in entities.PLANT_ACTIVE where a.PLANT_ID == plant.PLANT_ID && a.RECORD_TYPE == (int)TaskRecordType.HealthSafetyIncident select a).SingleOrDefault(); } periodDate = new DateTime(period.PeriodYear, period.PeriodMonth, 1); if (pact != null && pact.EFF_START_DATE.HasValue && periodDate >= pact.EFF_START_DATE) { // write PLANT_ACCOUNTING metrics if ((pa = paList.Where(l => l.PLANT_ID == period.PlantID && l.PERIOD_YEAR == period.PeriodYear && l.PERIOD_MONTH == period.PeriodMonth).FirstOrDefault()) == null) { paList.Add((pa = new PLANT_ACCOUNTING())); pa.PLANT_ID = period.PlantID; pa.PERIOD_YEAR = period.PeriodYear; pa.PERIOD_MONTH = period.PeriodMonth; } pa.TIME_LOST = period.LostTime; pa.TOTAL_DAYS_RESTRICTED = period.RestrictedTime; pa.TIME_LOST_CASES = period.LostTimeCase; pa.RECORDED_CASES = period.RecordableCase; pa.FIRST_AID_CASES = period.FirstAidCase; pa.LAST_UPD_DT = DateTime.UtcNow; pa.LAST_UPD_BY = "automated"; EHSModel.UpdatePlantAccounting(entities, pa); } } WriteLine("INCIDENT Rollup Completed: " + DateTime.UtcNow.ToString("hh:mm MM/dd/yyyy")); } catch (Exception ex) { WriteLine("INCIDENT RollUp Error: " + ex.ToString()); } return(nextStep); }
AlertData PopulateByIncidentId(decimal iid) { AlertData d = new AlertData(); var entities = new PSsqmEntities(); d.incident = EHSIncidentMgr.SelectIncidentById(entities, iid); if (d.incident != null) { try { string plantName = EHSIncidentMgr.SelectPlantNameById((decimal)d.incident.DETECT_PLANT_ID); d.incidentLocation = plantName; d.incidentNumber = WebSiteCommon.FormatID(iid, 6); string incidentType = EHSIncidentMgr.SelectIncidentTypeByIncidentId(iid); decimal incidentTypeId = EHSIncidentMgr.SelectIncidentTypeIdByIncidentId(iid); decimal companyId = d.incident.DETECT_COMPANY_ID; var questions = EHSIncidentMgr.SelectIncidentQuestionList(incidentTypeId, companyId, 0); questions.AddRange(EHSIncidentMgr.SelectIncidentQuestionList(incidentTypeId, companyId, 1)); d.answerList = EHSIncidentMgr.GetIncidentAnswerList(d.incident.INCIDENT_ID); INCIDENT_ANSWER answer = null; // Date/Time d.incidentDate = d.incident.INCIDENT_DT.ToShortDateString(); if ((answer = d.answerList.Where(a => a.INCIDENT_QUESTION_ID == (decimal)EHSQuestionId.TimeOfDay).SingleOrDefault()) != null) { if (!string.IsNullOrEmpty(answer.ANSWER_VALUE)) { d.incidentTime = Convert.ToDateTime(answer.ANSWER_VALUE).ToShortTimeString(); } } if ((answer = d.answerList.Where(a => a.INCIDENT_QUESTION_ID == (decimal)EHSQuestionId.Shift).SingleOrDefault()) != null) { if (!string.IsNullOrEmpty(SQMBasePage.GetXLAT(reportXLAT, "SHIFT", answer.ANSWER_VALUE).DESCRIPTION)) { answer.ANSWER_VALUE = SQMBasePage.GetXLAT(reportXLAT, "SHIFT", answer.ANSWER_VALUE).DESCRIPTION; } } if ((answer = d.answerList.Where(a => a.INCIDENT_QUESTION_ID == (decimal)EHSQuestionId.Department).SingleOrDefault()) != null) { d.incidentDept = answer.ANSWER_VALUE; decimal deptID = 0; if (decimal.TryParse(answer.ANSWER_VALUE, out deptID)) { DEPARTMENT dept = SQMModelMgr.LookupDepartment(entities, deptID); if (dept != null) { d.incidentDept = dept.DEPT_NAME; } } } // Incident Type d.incidentType = incidentType; // Description d.incidentDescription = d.incident.DESCRIPTION; d.detectPerson = SQMModelMgr.LookupPerson(entities, (decimal)d.incident.CREATE_PERSON, "", false); if (d.detectPerson != null) { d.supervisorPerson = SQMModelMgr.LookupPersonByEmpID(entities, d.detectPerson.SUPV_EMP_ID); } if (d.incident.ISSUE_TYPE_ID == (decimal)EHSIncidentTypeId.InjuryIllness) { if (d.incident.INCFORM_INJURYILLNESS.INVOLVED_PERSON_ID.HasValue) { d.involvedPerson = SQMModelMgr.LookupPerson(entities, (decimal)d.incident.INCFORM_INJURYILLNESS.INVOLVED_PERSON_ID, "", false); if (d.involvedPerson != null) { if (d.incident.INCFORM_INJURYILLNESS.SUPERVISOR_PERSON_ID.HasValue) // supervisor was known { d.supervisorPerson = SQMModelMgr.LookupPerson(entities, (decimal)d.incident.INCFORM_INJURYILLNESS.SUPERVISOR_PERSON_ID, "", false); } else { // get current supervisor d.supervisorPerson = SQMModelMgr.LookupPersonByEmpID(entities, d.involvedPerson.SUPV_EMP_ID); } } } else { d.involvedPerson = new PERSON(); d.involvedPerson.FIRST_NAME = d.incident.INCFORM_INJURYILLNESS.INVOLVED_PERSON_NAME; } if (d.incident.INCFORM_INJURYILLNESS.DEPT_ID.HasValue) { DEPARTMENT dept = SQMModelMgr.LookupDepartment(entities, (decimal)d.incident.INCFORM_INJURYILLNESS.DEPT_ID); if (dept != null) { d.incidentDept = dept.DEPT_NAME; } } else { d.incidentDept = d.incident.INCFORM_INJURYILLNESS.DEPARTMENT; } } // Containment foreach (INCFORM_CONTAIN cc in EHSIncidentMgr.GetContainmentList(iid, null, false)) { if (cc.ASSIGNED_PERSON_ID.HasValue) { cc.ASSIGNED_PERSON = SQMModelMgr.FormatPersonListItem(SQMModelMgr.LookupPerson((decimal)cc.ASSIGNED_PERSON_ID, "")); } d.containList.Add(cc); } // Root Cause(s) d.root5YList = EHSIncidentMgr.GetRootCauseList(iid).Where(l => !string.IsNullOrEmpty(l.ITEM_DESCRIPTION)).ToList(); if (d.root5YList != null && d.root5YList.Count > 0) { d.incident.INCFORM_CAUSATION.Load(); if (d.incident.INCFORM_CAUSATION != null && d.incident.INCFORM_CAUSATION.Count > 0) { d.causation = d.incident.INCFORM_CAUSATION.ElementAt(0); } } // Corrective Actions foreach (TASK_STATUS ac in EHSIncidentMgr.GetCorrectiveActionList(iid, null, false)) { if (ac.RESPONSIBLE_ID.HasValue) { ac.COMMENTS = SQMModelMgr.FormatPersonListItem(SQMModelMgr.LookupPerson((decimal)ac.RESPONSIBLE_ID, "")); } d.actionList.Add(ac); } var files = (from a in entities.ATTACHMENT where (a.RECORD_ID == iid && a.RECORD_TYPE == 40 && a.DISPLAY_TYPE > 0) && (a.FILE_NAME.ToLower().Contains(".jpg") || a.FILE_NAME.ToLower().Contains(".jpeg") || a.FILE_NAME.ToLower().Contains(".gif") || a.FILE_NAME.ToLower().Contains(".png") || a.FILE_NAME.ToLower().Contains(".bmp")) orderby a.RECORD_TYPE, a.FILE_NAME select new { Data = (from f in entities.ATTACHMENT_FILE where f.ATTACHMENT_ID == a.ATTACHMENT_ID select f.ATTACHMENT_DATA).FirstOrDefault(), Description = (string.IsNullOrEmpty(a.FILE_DESC)) ? "" : a.FILE_DESC, }).ToList(); d.approvalList = EHSIncidentMgr.GetApprovalList(entities, (decimal)d.incident.ISSUE_TYPE_ID, 10.0m, iid, null, 0); if (files.Count > 0) { d.photoData = new List <byte[]>(); d.photoCaptions = new List <string>(); foreach (var f in files) { d.photoData.Add(f.Data); d.photoCaptions.Add(f.Description); } } } catch { } } return(d); }
protected void Page_Load(object sender, EventArgs e) { string pageMode = ""; if (!string.IsNullOrEmpty(Request.QueryString["m"])) // .../...aspx?p=xxxxx { pageMode = Request.QueryString["m"].ToLower(); // page mode (web == running manually from the menu) } if (IsPostBack) { if (pageMode != "web") { System.Web.UI.ScriptManager.RegisterStartupScript(this, GetType(), "closePage", "window.onunload = CloseWindow();", true); } return; } output = new StringBuilder(); bool validIP = true; DateTime thisPeriod = DateTime.UtcNow; decimal updateIndicator = thisPeriod.Ticks; decimal locationID = 0; WriteLine("Started: " + DateTime.UtcNow.ToString("hh:mm MM/dd/yyyy")); try { string currentIP = GetIPAddress(); List <SETTINGS> sets = SQMSettings.SelectSettingsGroup("AUTOMATE", ""); // ABW 20140805 string strValidIP = sets.Find(x => x.SETTING_CD == "ValidIP").VALUE.ToString(); /* * if (strValidIP.Equals(currentIP)) * { * WriteLine("Main Incident RollUp being accessed from a valid IP address " + currentIP); * validIP = true; * * if (Request.QueryString["validation"] != null) * { * if (Request.QueryString["validation"].ToString().Equals("Vb12M11a4")) * validIP = true; * } * else * { * WriteLine("Main Incident RollUp requested from incorrect source."); * validIP = false; * } * } * else * { * WriteLine("Main Incident RollUp being accessed from invalid IP address " + currentIP); * validIP = false; * } */ } catch (Exception ex) { validIP = false; WriteLine("Main Email Notification Error validating IP Address: " + ex.ToString()); } // make sure this code is NOT moved to production validIP = true; if (!validIP) { WriteLine("Main Incident RollUp Invalid IP Address"); ltrStatus.Text = output.ToString().Replace("\n", "<br/>"); WriteLogFile(); System.Web.UI.ScriptManager.RegisterStartupScript(this, GetType(), "closePage", "window.onunload = CloseWindow();", true); return; } try { PSsqmEntities entities = new PSsqmEntities(); List <TaskItem> openAuditList = TaskMgr.SelectOpenAudits(DateTime.UtcNow, DateTime.UtcNow); if (openAuditList.Count > 0) { WriteLine("Open Audits ..."); foreach (TaskItem taskItem in openAuditList) { WriteLine("Audit: " + taskItem.Task.RECORD_ID.ToString() + " Status = " + taskItem.Task.STATUS); AUDIT audit = EHSAuditMgr.SelectAuditById(entities, taskItem.Task.RECORD_ID); if (audit != null) { EHSNotificationMgr.NotifyAuditStatus(audit, taskItem); } } } List <TaskItem> openTaskList = TaskMgr.SelectOpenTasks(DateTime.UtcNow, DateTime.UtcNow); if (openTaskList.Count > 0) { WriteLine("Open Tasks ..."); foreach (TaskItem taskItem in openTaskList) { WriteLine("Task: " + taskItem.Task.TASK_ID.ToString() + " RecordType: " + taskItem.Task.RECORD_TYPE.ToString() + " " + "RecordID:" + taskItem.Task.RECORD_ID.ToString() + " Status = " + taskItem.Task.STATUS); if (taskItem.Task.RECORD_TYPE == (int)TaskRecordType.HealthSafetyIncident) { INCIDENT incident = EHSIncidentMgr.SelectIncidentById(entities, taskItem.Task.RECORD_ID); if (incident != null) { // notify assigned person and escalation person if over-over due EHSNotificationMgr.NotifyIncidentTaskStatus(incident, taskItem, ((int)SysPriv.action).ToString()); if (taskItem.Taskstatus >= TaskStatus.Overdue) { // send to notification list for plant, BU, ... EHSNotificationMgr.NotifyIncidentStatus(incident, taskItem.Task.TASK_STEP, ((int)SysPriv.notify).ToString(), ""); } } } else if (taskItem.Task.RECORD_TYPE == (int)TaskRecordType.PreventativeAction) { INCIDENT incident = EHSIncidentMgr.SelectIncidentById(entities, taskItem.Task.RECORD_ID); if (incident != null) { // notify assigned person and escalation person if over-over due EHSNotificationMgr.NotifyPrevActionTaskStatus(incident, taskItem, ((int)SysPriv.action).ToString()); } } else if (taskItem.Task.RECORD_TYPE == (int)TaskRecordType.Audit) { AUDIT audit = EHSAuditMgr.SelectAuditById(entities, taskItem.Task.RECORD_ID); if (audit != null) { EHSNotificationMgr.NotifyAuditTaskStatus(audit, taskItem, ((int)SysPriv.action).ToString()); } } } } } catch (Exception ex) { WriteLine("Main Email Notificaion Error - " + ex.ToString()); } WriteLine(""); WriteLine("Completed: " + DateTime.UtcNow.ToString("hh:mm MM/dd/yyyy")); ltrStatus.Text = output.ToString().Replace("\n", "<br/>"); WriteLogFile(); System.Web.UI.ScriptManager.RegisterStartupScript(this, GetType(), "closePage", "window.onunload = CloseWindow();", true); }
AlertData PopulateByIncidentId(decimal iid) { AlertData d = new AlertData(); var entities = new PSsqmEntities(); var incident = EHSIncidentMgr.SelectIncidentById(entities, iid); if (incident != null) { string plantName = EHSIncidentMgr.SelectPlantNameById((decimal)incident.DETECT_PLANT_ID); d.incidentLocation = String.Format("Location: {0}", plantName); d.incidentNumber = String.Format("Incident #: {0}", iid); string incidentType = EHSIncidentMgr.SelectIncidentTypeByIncidentId(iid); decimal incidentTypeId = EHSIncidentMgr.SelectIncidentTypeIdByIncidentId(iid); decimal companyId = incident.DETECT_COMPANY_ID; var questions = EHSIncidentMgr.SelectIncidentQuestionList(incidentTypeId, companyId, 0); questions.AddRange(EHSIncidentMgr.SelectIncidentQuestionList(incidentTypeId, companyId, 1)); // Date/Time d.incidentDateTime = incident.INCIDENT_DT.ToLongDateString(); var timeQuestion = questions.FirstOrDefault(q => q.QuestionId == (decimal)EHSQuestionId.TimeOfDay); if (timeQuestion != null) { string timeAnswer = (from a in entities.INCIDENT_ANSWER where a.INCIDENT_ID == incident.INCIDENT_ID && a.INCIDENT_QUESTION_ID == (decimal)EHSQuestionId.TimeOfDay select a.ANSWER_VALUE).FirstOrDefault(); if (!string.IsNullOrEmpty(timeAnswer)) { d.incidentDateTime += " " + Convert.ToDateTime(timeAnswer).ToShortTimeString(); } } // Incident Type d.incidentType = incidentType; // Description d.incidentDescription = incident.DESCRIPTION; // Root Cause(s) var rootCauseQuestion = questions.FirstOrDefault(q => q.QuestionId == (decimal)EHSQuestionId.RootCause); if (rootCauseQuestion != null) { string rootCauseAnswer = (from a in entities.INCIDENT_ANSWER where a.INCIDENT_ID == iid && a.INCIDENT_QUESTION_ID == (decimal)EHSQuestionId.RootCause select a.ANSWER_VALUE).FirstOrDefault(); if (!string.IsNullOrEmpty(rootCauseAnswer)) { d.incidentRootCause = new List <string> { rootCauseAnswer } } ; } // Containment var containmentQuestion = questions.FirstOrDefault(q => q.QuestionId == (decimal)EHSQuestionId.Containment); if (containmentQuestion != null) { string containmentAnswer = (from a in entities.INCIDENT_ANSWER where a.INCIDENT_ID == iid && a.INCIDENT_QUESTION_ID == (decimal)EHSQuestionId.Containment select a.ANSWER_VALUE).FirstOrDefault(); if (!string.IsNullOrEmpty(containmentAnswer)) { d.incidentContainment = new List <string> { containmentAnswer } } ; } // Corrective Actions var correctiveQuestion = questions.FirstOrDefault(q => q.QuestionId == (decimal)EHSQuestionId.CorrectiveActions); if (correctiveQuestion != null) { string correctiveAnswer = (from a in entities.INCIDENT_ANSWER where a.INCIDENT_ID == iid && a.INCIDENT_QUESTION_ID == (decimal)EHSQuestionId.CorrectiveActions select a.ANSWER_VALUE).FirstOrDefault(); if (!string.IsNullOrEmpty(correctiveAnswer)) { d.incidentCorrectiveActions = new List <string> { correctiveAnswer } } ; } var files = (from a in entities.ATTACHMENT where (a.RECORD_ID == iid && a.RECORD_TYPE == 40 && a.DISPLAY_TYPE > 0) && (a.FILE_NAME.ToLower().Contains(".jpg") || a.FILE_NAME.ToLower().Contains(".jpeg") || a.FILE_NAME.ToLower().Contains(".gif") || a.FILE_NAME.ToLower().Contains(".png") || a.FILE_NAME.ToLower().Contains(".bmp")) orderby a.RECORD_TYPE, a.FILE_NAME select new { Data = (from f in entities.ATTACHMENT_FILE where f.ATTACHMENT_ID == a.ATTACHMENT_ID select f.ATTACHMENT_DATA).FirstOrDefault(), Description = (string.IsNullOrEmpty(a.FILE_DESC)) ? "" : a.FILE_DESC, }).ToList(); if (files.Count > 0) { d.photoData = new List <byte[]>(); d.photoCaptions = new List <string>(); foreach (var f in files) { d.photoData.Add(f.Data); d.photoCaptions.Add(f.Description); } } } return(d); }
AlertData PopulateByProblemCaseId(decimal pcid) { AlertData d = new AlertData(); var entities = new PSsqmEntities(); PROB_CASE probCase = ProblemCase.LookupCase(entities, pcid); if (probCase != null) { List <INCIDENT> incidentList = ProblemCase.LookupProbIncidentList(entities, probCase); if (incidentList.Count > 0) { var incident = incidentList[0]; string plantName = EHSIncidentMgr.SelectPlantNameById((decimal)incident.DETECT_PLANT_ID); d.incidentLocation = String.Format("Location: {0}", plantName); d.incidentNumber = String.Format("Incident #: {0}", incident.INCIDENT_ID); string incidentType = EHSIncidentMgr.SelectIncidentTypeByIncidentId(incident.INCIDENT_ID); decimal incidentTypeId = EHSIncidentMgr.SelectIncidentTypeIdByIncidentId(incident.INCIDENT_ID); decimal companyId = incident.DETECT_COMPANY_ID; var questions = EHSIncidentMgr.SelectIncidentQuestionList(incidentTypeId, companyId, 0); questions.AddRange(EHSIncidentMgr.SelectIncidentQuestionList(incidentTypeId, companyId, 1)); d.incidentDateTime = incidentList[0].INCIDENT_DT.ToLongDateString(); var timeQuestion = questions.FirstOrDefault(q => q.QuestionId == 5); if (timeQuestion != null) { string timeAnswer = (from a in entities.INCIDENT_ANSWER where a.INCIDENT_ID == incident.INCIDENT_ID && a.INCIDENT_QUESTION_ID == 5 select a.ANSWER_VALUE).FirstOrDefault(); if (!string.IsNullOrEmpty(timeAnswer)) { d.incidentDateTime += " " + Convert.ToDateTime(timeAnswer).ToShortTimeString(); } } d.incidentType = incidentType; d.incidentDescription = probCase.DESC_LONG; // Root Cause(s) List <PROB_CAUSE_STEP> probCauses = (from i in entities.PROB_CAUSE_STEP where i.PROBCASE_ID == pcid && i.IS_ROOTCAUSE == true select i).ToList(); if (probCauses.Count > 0) { d.incidentRootCause = (from rc in probCauses select rc.HOW_CONFIRMED).ToList(); } // Containment var containment = (from i in entities.PROB_CONTAIN where i.PROBCASE_ID == pcid select new { Recommendation = i.CONTAINMENT_DESC, Action = i.INITIAL_ACTION, Results = i.INITIAL_RESULTS }).FirstOrDefault(); var actions = (from i in entities.PROB_CONTAIN_ACTION where i.PROBCASE_ID == pcid select i.ACTION_ITEM).ToList(); if (containment != null) { d.incidentContainment = new List <string> (); //if (!string.IsNullOrEmpty(containment.Recommendation)) //{ // d.incidentContainment.Add("RECOMMENDATION: " + containment.Recommendation); // d.incidentContainment.Add(" "); //} if (actions != null) { string strActions; int i = 1; foreach (var actionItem in actions) { strActions = i++ + ") " + actionItem; d.incidentContainment.Add(strActions); } } if (!string.IsNullOrEmpty(containment.Results)) { d.incidentContainment.Add(" "); d.incidentContainment.Add("RESULTS: " + containment.Results); } } // Corrective Actions var correctiveActions = (from i in entities.PROB_CAUSE_ACTION where i.PROBCASE_ID == pcid select i.ACTION_DESC).ToList(); if (correctiveActions.Count() > 0) { d.incidentCorrectiveActions = new List <string>(); int i = 1; foreach (var ca in correctiveActions) { d.incidentCorrectiveActions.Add(i++ + ") " + ca); } } // Photos var files = (from a in entities.ATTACHMENT where ((a.RECORD_ID == incident.INCIDENT_ID && a.RECORD_TYPE == 40) || (a.RECORD_ID == pcid && a.RECORD_TYPE == 21)) && (a.DISPLAY_TYPE > 0) && (a.FILE_NAME.ToLower().Contains(".jpg") || a.FILE_NAME.ToLower().Contains(".jpeg") || a.FILE_NAME.ToLower().Contains(".gif") || a.FILE_NAME.ToLower().Contains(".png") || a.FILE_NAME.ToLower().Contains(".bmp")) orderby a.RECORD_TYPE, a.FILE_NAME select new { Data = (from f in entities.ATTACHMENT_FILE where f.ATTACHMENT_ID == a.ATTACHMENT_ID select f.ATTACHMENT_DATA).FirstOrDefault(), Description = (string.IsNullOrEmpty(a.FILE_DESC)) ? "" : a.FILE_DESC, }).ToList(); if (files.Count > 0) { d.photoData = new List <byte[]>(); d.photoCaptions = new List <string>(); foreach (var f in files) { d.photoData.Add(f.Data); d.photoCaptions.Add(f.Description); } } } } return(d); }
protected void Page_Load(object sender, EventArgs e) { string pageMode = ""; if (!string.IsNullOrEmpty(Request.QueryString["m"])) // .../...aspx?p=xxxxx { pageMode = Request.QueryString["m"].ToLower(); // page mode (web == running manually from the menu) } if (IsPostBack) { if (pageMode != "web") { System.Web.UI.ScriptManager.RegisterStartupScript(this, GetType(), "closePage", "window.onunload = CloseWindow();", true); } return; } output = new StringBuilder(); SETTINGS setting = null; bool validIP = true; int workdays = 7; string pageURI = HttpContext.Current.Request.Url.AbsoluteUri; string nextPage = ""; PLANT_ACCOUNTING pa = null; fromDate = DateTime.UtcNow.AddMonths(-11); // set the incident 'select from' date. TODO: get this from SETTINGS table // set end date to end of current month to clear spurrious entries ? DateTime rollupToDate = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.DaysInMonth(DateTime.UtcNow.Year, DateTime.UtcNow.Month)); WriteLine("Incident Rollup Started: " + DateTime.UtcNow.ToString("hh:mm MM/dd/yyyy")); WriteLine(pageURI); try { string currentIP = GetIPAddress(); List <SETTINGS> sets = SQMSettings.SelectSettingsGroup("AUTOMATE", ""); // ABW 20140805 string strValidIP = sets.Find(x => x.SETTING_CD == "ValidIP").VALUE.ToString(); setting = sets.Where(x => x.SETTING_CD == "ROLLUP_WORKDAYS").FirstOrDefault(); if (setting != null && !string.IsNullOrEmpty(setting.VALUE)) { if (!int.TryParse(setting.VALUE, out workdays)) { workdays = 7; } } setting = sets.Where(x => x.SETTING_CD == "ROLLUP_NEXTPAGE").FirstOrDefault(); if (setting != null && !string.IsNullOrEmpty(setting.VALUE) && setting.VALUE.Length > 1) { nextPage = setting.VALUE; } /* * int rollupMonthsAhead = 0; * setting = sets.Where(x => x.SETTING_CD == "ROLLUP_MONTHS_AHEAD").FirstOrDefault(); * if (setting != null && !string.IsNullOrEmpty(setting.VALUE)) * { * int.TryParse(setting.VALUE, out rollupMonthsAhead); * rollupToDate = rollupToDate.AddMonths(rollupMonthsAhead); * } */ /* * if (strValidIP.Equals(currentIP)) * { * WriteLine("Main Incident RollUp being accessed from a valid IP address " + currentIP); * validIP = true; * * if (Request.QueryString["validation"] != null) * { * if (Request.QueryString["validation"].ToString().Equals("Vb12M11a4")) * validIP = true; * } * else * { * WriteLine("Main Incident RollUp requested from incorrect source."); * validIP = false; * } * } * else * { * WriteLine("Main Incident RollUp being accessed from invalid IP address " + currentIP); * validIP = false; * } */ } catch (Exception ex) { validIP = false; WriteLine("Main Incident RollUp Error validating IP Address: " + ex.ToString()); } // make sure this code is NOT moved to production //validIP = true; if (!validIP) { WriteLine("Main Incident RollUp Invalid IP Address"); ltrStatus.Text = output.ToString().Replace("\n", "<br/>"); WriteLogFile(); if (pageMode != "web") { System.Web.UI.ScriptManager.RegisterStartupScript(this, GetType(), "closePage", "window.onunload = CloseWindow();", true); } return; } try { PSsqmEntities entities = new PSsqmEntities(); // fetch all incidents occurring after the minimum reporting date List <INCIDENT> incidentList = (from i in entities.INCIDENT.Include("INCFORM_INJURYILLNESS") where i.ISSUE_TYPE_ID != (decimal)EHSIncidentTypeId.PreventativeAction && i.INCIDENT_DT >= fromDate && i.DETECT_PLANT_ID > 0 select i).OrderBy(l => l.DETECT_PLANT_ID).ThenBy(l => l.INCIDENT_DT).ToList(); List <PLANT> plantList = SQMModelMgr.SelectPlantList(entities, 1, 0); PLANT plant = null; // fetch all the plant accounting records for the target timespan DateTime minDate = incidentList.Select(l => l.INCIDENT_DT).Min(); minDate = minDate.AddMonths(-1); List <PLANT_ACCOUNTING> paList = (from a in entities.PLANT_ACCOUNTING where EntityFunctions.CreateDateTime(a.PERIOD_YEAR, a.PERIOD_MONTH, 1, 0, 0, 0) >= minDate && EntityFunctions.CreateDateTime(a.PERIOD_YEAR, a.PERIOD_MONTH, 1, 0, 0, 0) <= rollupToDate select a).OrderBy(l => l.PLANT_ID).ThenBy(l => l.PERIOD_YEAR).ThenBy(l => l.PERIOD_MONTH).ToList(); List <EHSIncidentTimeAccounting> summaryList = new List <EHSIncidentTimeAccounting>(); foreach (INCIDENT incident in incidentList) { WriteLine("Incident ID: " + incident.INCIDENT_ID.ToString() + " Occur Date: " + Convert.ToDateTime(incident.INCIDENT_DT).ToShortDateString()); incident.INCFORM_CAUSATION.Load(); if (incident.ISSUE_TYPE_ID == (decimal)EHSIncidentTypeId.InjuryIllness) { incident.INCFORM_LOSTTIME_HIST.Load(); } plant = plantList.Where(l => l.PLANT_ID == (decimal)incident.DETECT_PLANT_ID).FirstOrDefault(); summaryList = EHSIncidentMgr.SummarizeIncidentAccounting(summaryList, EHSIncidentMgr.CalculateIncidentAccounting(incident, plant.LOCAL_TIMEZONE, workdays)); } plant = null; PLANT_ACTIVE pact = null; DateTime periodDate; foreach (PLANT_ACCOUNTING pah in paList.OrderBy(l => l.PLANT_ID).ToList()) { if (pact == null || pact.PLANT_ID != pah.PLANT_ID) { pact = (from a in entities.PLANT_ACTIVE where a.PLANT_ID == pah.PLANT_ID && a.RECORD_TYPE == (int)TaskRecordType.HealthSafetyIncident select a).SingleOrDefault(); } //if (pact != null && pact.EFF_END_DATE.HasValue && new DateTime(pah.PERIOD_YEAR, pah.PERIOD_MONTH, 1).Date >= ((DateTime)pact.EFF_START_DATE).Date) if (pact != null && pact.EFF_START_DATE.HasValue && new DateTime(pah.PERIOD_YEAR, pah.PERIOD_MONTH, 1).Date >= ((DateTime)pact.EFF_START_DATE).Date) { pah.TIME_LOST = pah.TOTAL_DAYS_RESTRICTED = 0; pah.TIME_LOST_CASES = pah.RECORDED_CASES = pah.FIRST_AID_CASES = 0; } } plant = null; pact = null; foreach (EHSIncidentTimeAccounting period in summaryList.OrderBy(l => l.PlantID).ThenBy(l => l.PeriodYear).ThenBy(l => l.PeriodMonth).ToList()) { if (plant == null || plant.PLANT_ID != period.PlantID) { plant = plantList.Where(l => l.PLANT_ID == period.PlantID).FirstOrDefault(); pact = (from a in entities.PLANT_ACTIVE where a.PLANT_ID == plant.PLANT_ID && a.RECORD_TYPE == (int)TaskRecordType.HealthSafetyIncident select a).SingleOrDefault(); } periodDate = new DateTime(period.PeriodYear, period.PeriodMonth, 1); if (pact != null && pact.EFF_START_DATE.HasValue && periodDate >= pact.EFF_START_DATE) { // write PLANT_ACCOUNTING metrics if ((pa = paList.Where(l => l.PLANT_ID == period.PlantID && l.PERIOD_YEAR == period.PeriodYear && l.PERIOD_MONTH == period.PeriodMonth).FirstOrDefault()) == null) { paList.Add((pa = new PLANT_ACCOUNTING())); pa.PLANT_ID = period.PlantID; pa.PERIOD_YEAR = period.PeriodYear; pa.PERIOD_MONTH = period.PeriodMonth; } pa.TIME_LOST = period.LostTime; pa.TOTAL_DAYS_RESTRICTED = period.RestrictedTime; pa.TIME_LOST_CASES = period.LostTimeCase; pa.RECORDED_CASES = period.RecordableCase; pa.FIRST_AID_CASES = period.FirstAidCase; pa.LAST_UPD_DT = DateTime.UtcNow; pa.LAST_UPD_BY = "automated"; EHSModel.UpdatePlantAccounting(entities, pa); } } } catch (Exception ex) { WriteLine("Main Incident RollUp Error - " + ex.ToString()); } WriteLine(""); WriteLine("Completed: " + DateTime.UtcNow.ToString("hh:mm MM/dd/yyyy")); ltrStatus.Text = output.ToString().Replace("\n", "<br/>"); WriteLogFile(); try { if (!string.IsNullOrEmpty(nextPage)) { int s1 = pageURI.LastIndexOf('/'); int s2 = pageURI.LastIndexOf('.') > -1 ? pageURI.LastIndexOf('.') : pageURI.Length; string nextPageURI = pageURI.Substring(0, s1 + 1) + nextPage + pageURI.Substring(s2, pageURI.Length - s2); Response.Redirect(nextPageURI); } } catch (Exception ex) { output = new StringBuilder(); WriteLine("RollUp Redirect Error - " + ex.ToString()); WriteLogFile(); } if (pageMode != "web") { System.Web.UI.ScriptManager.RegisterStartupScript(this, GetType(), "closePage", "window.onunload = CloseWindow();", true); } }