public static VIDEO_ATTACHMENT DeleteVideoAttachment(decimal videoAttachID) { VIDEO_ATTACHMENT this_at = null; try { using (PSsqmEntities entities = new PSsqmEntities()) { VIDEO_ATTACHMENT_FILE this_atfile = (from d in entities.VIDEO_ATTACHMENT_FILE where (d.VIDEO_ATTACH_ID == videoAttachID) select d).Single(); if (this_atfile != null) { entities.DeleteObject(this_atfile); entities.SaveChanges(); } this_at = (from d in entities.VIDEO_ATTACHMENT where (d.VIDEO_ATTACH_ID == videoAttachID) select d).Single(); if (this_at != null) { entities.DeleteObject(this_at); entities.SaveChanges(); } } } catch (Exception e) { //SQMLogger.LogException(e); } return(this_at); }
public static void Delete(decimal Document_ID) { try { using (PSsqmEntities entities = new PSsqmEntities()) { DOCUMENT_FILE this_docfile = (from d in entities.DOCUMENT_FILE where (d.DOCUMENT_ID == Document_ID) select d).Single(); if (this_docfile != null) { entities.DeleteObject(this_docfile); entities.SaveChanges(); } DOCUMENT this_doc = (from d in entities.DOCUMENT where (d.DOCUMENT_ID == Document_ID) select d).Single(); if (this_doc != null) { entities.DeleteObject(this_doc); entities.SaveChanges(); } } } catch (Exception e) { //SQMLogger.LogException(e); } return; }
static void UpdateOrdinalData(PSsqmEntities entities, EHS_DATA ehs_data, dynamic types, Dictionary <string, int> type_data) { if (type_data != null) // might be null if ordinal data type not activated in the FIRSTAID-ORDINALS or RECORDABLE-ORDINALS SETTINGS table { foreach (var t in types) { string group = t.XLAT_GROUP; string code = t.XLAT_CODE; var data = entities.EHS_DATA_ORD.FirstOrDefault(d => d.DATA_ID == ehs_data.DATA_ID && d.XLAT_GROUP == group && d.XLAT_CODE == code); if (type_data.ContainsKey(code)) { if (data == null) { entities.EHS_DATA_ORD.AddObject(new EHS_DATA_ORD() { EHS_DATA = ehs_data, XLAT_GROUP = t.XLAT_GROUP, XLAT_CODE = t.XLAT_CODE, VALUE = type_data[code] }); } else { data.VALUE = type_data[code]; } } else if (data != null) { entities.DeleteObject(data); } } } }
protected List <TASK_STATUS> UpdateAlertTaskList(List <TASK_STATUS> alertTaskList) { // create task for each location and/or update existing task TASK_STATUS task = null; int taskSeq = 0; bool isNew = false; RadComboBox ddl = null; HiddenField hf = null; foreach (GridDataItem item in rgAlertTaskList.Items) { hf = (HiddenField)item.FindControl("hfTaskID"); ddl = (RadComboBox)item.FindControl("ddlResponsible"); if (string.IsNullOrEmpty(hf.Value)) // create new { isNew = true; task = new TASK_STATUS(); hf = (HiddenField)item.FindControl("hfLocationTZ"); task = EHSIncidentMgr.CreateEmptyTask(LocalIncident.INCIDENT_ID, ((int)SysPriv.notify).ToString(), ++taskSeq, WebSiteCommon.LocalTime(DateTime.UtcNow, hf.Value)); task.RECORD_TYPE = (int)TaskRecordType.HealthSafetyIncident; hf = (HiddenField)item.FindControl("hfLocation"); task.RECORD_SUBID = Convert.ToDecimal(hf.Value); task.RECORD_ID = LocalIncident.INCIDENT_ID; alertTaskList.Add(task); localCtx.AddToTASK_STATUS(task); } else { isNew = false; task = alertTaskList.Where(l => l.TASK_ID.ToString() == hf.Value).FirstOrDefault(); // existing } // task.DESCRIPTION = tbAlertDesc.Text; task.DETAIL = tbComments.Text; task.DUE_DT = rdpDueDate.SelectedDate; if (!string.IsNullOrEmpty(ddl.SelectedValue)) { task.RESPONSIBLE_ID = Convert.ToDecimal(ddl.SelectedValue); } else { alertTaskList.Remove(task); localCtx.DeleteObject(task); } } return(alertTaskList); }
static void UpdateOrdinalData(PSsqmEntities entities, decimal dataID, dynamic types, Dictionary <string, dynamic> type_data) { foreach (var t in types) { string group = t.XLAT_GROUP; string code = t.XLAT_CODE; var data = entities.EHS_DATA_ORD.FirstOrDefault(d => d.DATA_ID == dataID && d.XLAT_GROUP == group && d.XLAT_CODE == code); string description = t.DESCRIPTION; if (type_data.Keys.Any(k => k.Contains("&") || k.Contains("<") || k.Contains(">"))) { description = description.Replace("&", "&").Replace("<", "<").Replace(">", ">"); } var value = type_data[description]; if (value.ContainsKey("value")) { if (data == null) { entities.EHS_DATA_ORD.AddObject(new EHS_DATA_ORD() { DATA_ID = dataID, XLAT_GROUP = t.XLAT_GROUP, XLAT_CODE = t.XLAT_CODE, VALUE = value["value"] }); } else { data.VALUE = value["value"]; } } else if (data != null) { entities.DeleteObject(data); } } }
static string ProcessEHSData() { string nextStep = ""; WriteLine("EHSDATA Rollup Started: " + DateTime.UtcNow.ToString("hh:mm MM/dd/yyyy")); List <SETTINGS> sets = SQMSettings.SelectSettingsGroup("AUTOMATE", "TASK"); // ABW 20140805 try { using (var entities = new PSsqmEntities()) { long updateIndicator = DateTime.UtcNow.Ticks; SETTINGS setting = null; // get any AUTOMATE settings sets = SQMSettings.SelectSettingsGroup("AUTOMATE", "TASK"); DateTime rollupFromDate = DateTime.UtcNow.AddMonths(-11); // this should be a setting // 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)); /* * 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); * } */ List <EHS_MEASURE> measureList = (from m in entities.EHS_MEASURE select m).ToList(); decimal plantManagerAuditsMeasureID = measureList.First(m => m.MEASURE_CD == "S30003").MEASURE_ID; decimal ehsAuditsMeasureID = measureList.First(m => m.MEASURE_CD == "S30001").MEASURE_ID; decimal supervisorAuditsMeasureID = measureList.First(m => m.MEASURE_CD == "S30002").MEASURE_ID; decimal plantManagerAuditsCreatedMeasureID = measureList.First(m => m.MEASURE_CD == "S3C003").MEASURE_ID; decimal ehsAuditsCreatedMeasureID = measureList.First(m => m.MEASURE_CD == "S3C001").MEASURE_ID; decimal supervisorAuditsCreatedMeasureID = measureList.First(m => m.MEASURE_CD == "S3C002").MEASURE_ID; decimal allAuditsCreatedMeasureID = measureList.FirstOrDefault(m => m.MEASURE_CD == "S3C000").MEASURE_ID; var auditDailyMeasureIDs = new List <decimal>() { plantManagerAuditsMeasureID, ehsAuditsMeasureID, supervisorAuditsMeasureID, }; var auditMonthlyMeasureIDs = new List <decimal>() { plantManagerAuditsCreatedMeasureID, ehsAuditsCreatedMeasureID, supervisorAuditsCreatedMeasureID, allAuditsCreatedMeasureID }; decimal nearMissMeasureID = measureList.First(m => m.MEASURE_CD == "S20002").MEASURE_ID; decimal firstAidMeasureID = measureList.First(m => m.MEASURE_CD == "S20003").MEASURE_ID; decimal recordableMeasureID = measureList.First(m => m.MEASURE_CD == "S20004").MEASURE_ID; decimal lostTimeCaseMeasureID = measureList.First(m => m.MEASURE_CD == "S20005").MEASURE_ID; decimal fatalityMeasureID = measureList.First(m => m.MEASURE_CD == "S20006").MEASURE_ID; decimal otherIncidentsID = measureList.First(m => m.MEASURE_CD == "S20001").MEASURE_ID; decimal closedInvestigationMeasureID = measureList.First(m => m.MEASURE_CD == "S20007").MEASURE_ID; var incidentMeasureIDs = new List <decimal>() { nearMissMeasureID, firstAidMeasureID, recordableMeasureID, lostTimeCaseMeasureID, fatalityMeasureID, otherIncidentsID, closedInvestigationMeasureID }; decimal timeLostMeasureID = entities.EHS_MEASURE.First(m => m.MEASURE_CD == "S60001").MEASURE_ID; decimal timeRestrictedMeasureID = entities.EHS_MEASURE.First(m => m.MEASURE_CD == "S60003").MEASURE_ID; var incidentMonthlyMeasureIDs = new List <decimal>() { timeLostMeasureID, timeRestrictedMeasureID }; decimal injuryIllnessIssueTypeID = entities.INCIDENT_TYPE.First(i => i.TITLE == "Injury/Illness").INCIDENT_TYPE_ID; decimal nearMissIssueTypeID = entities.INCIDENT_TYPE.First(i => i.TITLE == "Near Miss").INCIDENT_TYPE_ID; List <PLANT> plantList = SQMModelMgr.SelectPlantList(entities, 1, 0).Where(l => l.STATUS == "A").ToList(); PLANT_ACTIVE pact = null; var createdAudits = (from a in entities.AUDIT where a.AUDIT_DT >= rollupFromDate && new decimal[3] { 1, 2, 3 }.Contains(a.AUDIT_TYPE_ID) select a).ToList(); //var incidents = (from i in entities.INCIDENT where i.INCIDENT_DT >= rollupFromDate && (i.ISSUE_TYPE_ID == injuryIllnessIssueTypeID || i.ISSUE_TYPE_ID == nearMissIssueTypeID) select i).ToList(); var incidents = (from i in entities.INCIDENT.Include("INCFORM_INJURYILLNESS") where i.INCIDENT_DT >= rollupFromDate && (i.ISSUE_TYPE_ID == injuryIllnessIssueTypeID || i.ISSUE_TYPE_ID == nearMissIssueTypeID) select i).ToList(); // AUDITS foreach (var plant in plantList) { pact = (from a in entities.PLANT_ACTIVE where a.PLANT_ID == plant.PLANT_ID && a.RECORD_TYPE == (int)TaskRecordType.Audit select a).SingleOrDefault(); if (pact != null && pact.EFF_START_DATE.HasValue) // plant is active { var auditsForPlant = createdAudits.Where(a => a.DETECT_PLANT_ID == plant.PLANT_ID && a.CURRENT_STATUS != "I"); // new timespan logic DateTime fromDate = rollupFromDate > (DateTime)pact.EFF_START_DATE ? rollupFromDate : (DateTime)pact.EFF_START_DATE; DateTime toDate = pact.EFF_END_DATE.HasValue && (DateTime)pact.EFF_END_DATE < rollupToDate ? (DateTime)pact.EFF_END_DATE : rollupToDate; WriteLine("AUDIT Rollup For Plant " + pact.PLANT_ID + " from date = " + fromDate.ToShortDateString() + " to date = " + toDate.ToShortDateString()); // loop by month to get audits created for month and then by day to calculate completions for (DateTime dt = fromDate; dt <= toDate; dt = dt.AddMonths(1)) { int plantManagerAuditsCreatedMonth = 0; int ehsAuditsCreatedMonth = 0; int supervisorAuditsCreatedMonth = 0; int auditsCreatedMonth = 0; for (var currDate = new System.DateTime(dt.Year, dt.Month, 1); currDate <= new System.DateTime(dt.Year, dt.Month, DateTime.DaysInMonth(dt.Year, dt.Month)); currDate = currDate.AddDays(1)) { int plantManagerAudits = 0; int ehsAudits = 0; int supervisorAudits = 0; // fetch audits scheduled for this day var auditsForDay = auditsForPlant.Where(a => TruncateTime(a.AUDIT_DT) == currDate.Date); if (auditsForDay.Any()) { auditsCreatedMonth += auditsForDay.Count(); plantManagerAuditsCreatedMonth += auditsForDay.Count(a => a.AUDIT_TYPE_ID == 1); ehsAuditsCreatedMonth += auditsForDay.Count(a => a.AUDIT_TYPE_ID == 2); supervisorAuditsCreatedMonth += auditsForDay.Count(a => a.AUDIT_TYPE_ID == 3); } // get audits scheduled for this day that have been completed var closedAuditsForDay = auditsForDay.Where(a => a.CLOSE_DATE_DATA_COMPLETE != null); if (closedAuditsForDay.Any()) { plantManagerAudits = closedAuditsForDay.Count(a => a.AUDIT_TYPE_ID == 1); ehsAudits = closedAuditsForDay.Count(a => a.AUDIT_TYPE_ID == 2); supervisorAudits = closedAuditsForDay.Count(a => a.AUDIT_TYPE_ID == 3); } var dailyData = EHSDataMapping.SelectEHSDataPeriodList(entities, plant.PLANT_ID, currDate, auditDailyMeasureIDs, true, updateIndicator); EHSDataMapping.SetEHSDataValue(dailyData, plantManagerAuditsMeasureID, plantManagerAudits, updateIndicator); EHSDataMapping.SetEHSDataValue(dailyData, ehsAuditsMeasureID, ehsAudits, updateIndicator); EHSDataMapping.SetEHSDataValue(dailyData, supervisorAuditsMeasureID, supervisorAudits, updateIndicator); foreach (var data in dailyData) { if (data.EntityState == EntityState.Detached && data.VALUE != 0) { entities.EHS_DATA.AddObject(data); } else if (data.EntityState != EntityState.Detached && data.VALUE == 0) { entities.DeleteObject(data); } } } var monthlyData = EHSDataMapping.SelectEHSDataPeriodList(entities, plant.PLANT_ID, new DateTime(dt.Year, dt.Month, 1), auditMonthlyMeasureIDs, true, updateIndicator); EHSDataMapping.SetEHSDataValue(monthlyData, plantManagerAuditsCreatedMeasureID, plantManagerAuditsCreatedMonth, updateIndicator); EHSDataMapping.SetEHSDataValue(monthlyData, supervisorAuditsCreatedMeasureID, supervisorAuditsCreatedMonth, updateIndicator); EHSDataMapping.SetEHSDataValue(monthlyData, ehsAuditsCreatedMeasureID, ehsAuditsCreatedMonth, updateIndicator); EHSDataMapping.SetEHSDataValue(monthlyData, allAuditsCreatedMeasureID, auditsCreatedMonth, updateIndicator); foreach (var data in monthlyData) { if (data.EntityState == EntityState.Detached && data.VALUE != 0) { entities.EHS_DATA.AddObject(data); } else if (data.EntityState != EntityState.Detached && data.VALUE == 0) { entities.DeleteObject(data); } } entities.SaveChanges(); } } entities.SaveChanges(); } // INCIDENTS foreach (var plant in plantList) { pact = (from a in entities.PLANT_ACTIVE where a.PLANT_ID == plant.PLANT_ID && a.RECORD_TYPE == (int)TaskRecordType.HealthSafetyIncident select a).SingleOrDefault(); if (pact != null && pact.EFF_START_DATE.HasValue) // plant is active { var incidentsForPlant = incidents.Where(i => i.DETECT_PLANT_ID == plant.PLANT_ID); //var minDate = new[] { pact.EFF_START_DATE, incidentsForPlant.Min(i => (DateTime?)i.INCIDENT_DT) }.Max(); //var maxDate = new[] { pact.EFF_END_DATE, incidentsForPlant.Max(i => (DateTime?)i.INCIDENT_DT) }.Min(); // new timespan logic DateTime fromDate = rollupFromDate > (DateTime)pact.EFF_START_DATE ? rollupFromDate : (DateTime)pact.EFF_START_DATE; DateTime toDate = pact.EFF_END_DATE.HasValue && (DateTime)pact.EFF_END_DATE < rollupToDate ? (DateTime)pact.EFF_END_DATE : rollupToDate; WriteLine("INCIDENT Rollup For Plant " + pact.PLANT_ID + " from date = " + fromDate.ToShortDateString() + " to date = " + toDate.ToShortDateString()); for (var currDate = fromDate; currDate <= toDate; currDate = currDate.AddDays(1)) { int nearMisses = 0; int firstAidCases = 0; int recordables = 0; int lostTimeCases = 0; int fatalities = 0; int otherIncidents = 0; int closedInvestigations = 0; var firstAidOrdinals = new Dictionary <string, Dictionary <string, int> >() { { "type", null }, { "bodyPart", null }, { "rootCause", null }, { "tenure", null }, { "daysToClose", null } }; var recordableOrdinals = new Dictionary <string, Dictionary <string, int> >() { { "type", null }, { "bodyPart", null }, { "rootCause", null }, { "tenure", null }, { "daysToClose", null } }; var incidentsForDay = incidentsForPlant.Where(i => TruncateTime(i.INCIDENT_DT) == currDate.Date); //if (incidentsForDay.Any()) if (incidentsForDay.Count() > 0) { var firstAidIncidents = incidentsForDay.Where(i => i.ISSUE_TYPE_ID == injuryIllnessIssueTypeID && i.INCFORM_INJURYILLNESS != null && i.INCFORM_INJURYILLNESS.FIRST_AID); var recordableIncidents = incidentsForDay.Where(i => i.ISSUE_TYPE_ID == injuryIllnessIssueTypeID && i.INCFORM_INJURYILLNESS != null && i.INCFORM_INJURYILLNESS.RECORDABLE); otherIncidents = incidentsForDay.Where(i => i.ISSUE_TYPE_ID != injuryIllnessIssueTypeID).Count(); // Basic data nearMisses = incidentsForDay.Count(i => i.ISSUE_TYPE_ID == nearMissIssueTypeID); firstAidCases = firstAidIncidents.Count(); recordables = recordableIncidents.Count(); lostTimeCases = incidentsForDay.Count(i => i.ISSUE_TYPE_ID == injuryIllnessIssueTypeID && i.INCFORM_INJURYILLNESS != null && i.INCFORM_INJURYILLNESS.LOST_TIME); fatalities = incidentsForDay.Count(i => i.ISSUE_TYPE_ID == injuryIllnessIssueTypeID && i.INCFORM_INJURYILLNESS != null && i.INCFORM_INJURYILLNESS.FATALITY.HasValue && i.INCFORM_INJURYILLNESS != null && i.INCFORM_INJURYILLNESS.FATALITY.Value); closedInvestigations = incidentsForDay.Count(i => i.CLOSE_DATE.HasValue); // First Aid ordinals // check which ordinal data we wish to capture SETTINGS setFirstAid = sets.Where(s => s.SETTING_CD == "FIRSTAID-ORDINALS").FirstOrDefault(); if (setFirstAid != null && setFirstAid.VALUE.Contains("type")) { firstAidOrdinals["type"] = firstAidIncidents.GroupBy(i => i.INCFORM_INJURYILLNESS.INJURY_TYPE).ToDictionary(t => t.Key ?? "", t => t.Count()); } if (setFirstAid != null && setFirstAid.VALUE.Contains("bodyPart")) { firstAidOrdinals["bodyPart"] = firstAidIncidents.GroupBy(i => i.INCFORM_INJURYILLNESS.INJURY_BODY_PART).ToDictionary(b => b.Key ?? "", b => b.Count()); } if (setFirstAid != null && setFirstAid.VALUE.Contains("rootCause")) { firstAidOrdinals["rootCause"] = firstAidIncidents.SelectMany(i => i.INCFORM_CAUSATION).GroupBy(c => c.CAUSEATION_CD).ToDictionary(c => c.Key ?? "", c => c.Count()); } if (setFirstAid != null && setFirstAid.VALUE.Contains("tenure")) { firstAidOrdinals["tenure"] = firstAidIncidents.GroupBy(i => i.INCFORM_INJURYILLNESS.JOB_TENURE).ToDictionary(t => t.Key ?? "", t => t.Count()); } if (setFirstAid != null && setFirstAid.VALUE.Contains("daysToClose")) { firstAidOrdinals["daysToClose"] = firstAidIncidents.Where(i => i.CLOSE_DATE.HasValue).Select(i => ((TimeSpan)(i.INCIDENT_DT - i.CLOSE_DATE)).Days).Select(d => entities.XLAT_DAYS_TO_CLOSE_TRANS.FirstOrDefault(x => (x.MIN_DAYS.HasValue ? d >= x.MIN_DAYS : true) && (x.MAX_DAYS.HasValue ? d <= x.MAX_DAYS : true)).XLAT_CODE).GroupBy(x => x).ToDictionary(x => x.Key ?? "", x => x.Count()); /* * firstAidOrdinals["daysToClose"] = firstAidIncidents.Where(i => i.CLOSE_DATE.HasValue).Select(i => * EntityFunctions.DiffDays(i.INCIDENT_DT, i.CLOSE_DATE)).Select(d => entities.XLAT_DAYS_TO_CLOSE_TRANS.FirstOrDefault(x => * (x.MIN_DAYS.HasValue ? d >= x.MIN_DAYS : true) && (x.MAX_DAYS.HasValue ? d <= x.MAX_DAYS : true)).XLAT_CODE).GroupBy(x => x).ToDictionary(x => * x.Key ?? "", x => x.Count()); */ } // Recordable ordinals // check which ordinal data we wish to capture SETTINGS setRecordable = sets.Where(s => s.SETTING_CD == "RECORDABLE-ORDINALS").FirstOrDefault(); if (setRecordable != null && setRecordable.VALUE.Contains("type")) { recordableOrdinals["type"] = recordableIncidents.GroupBy(i => i.INCFORM_INJURYILLNESS.INJURY_TYPE).ToDictionary(t => t.Key ?? "", t => t.Count()); } if (setRecordable != null && setRecordable.VALUE.Contains("bodyPart")) { recordableOrdinals["bodyPart"] = recordableIncidents.GroupBy(i => i.INCFORM_INJURYILLNESS.INJURY_BODY_PART).ToDictionary(b => b.Key ?? "", b => b.Count()); } if (setRecordable != null && setRecordable.VALUE.Contains("rootCause")) { recordableOrdinals["rootCause"] = recordableIncidents.SelectMany(i => i.INCFORM_CAUSATION).GroupBy(c => c.CAUSEATION_CD).ToDictionary(c => c.Key ?? "", c => c.Count()); } if (setRecordable != null && setRecordable.VALUE.Contains("tenure")) { recordableOrdinals["tenure"] = recordableIncidents.GroupBy(i => i.INCFORM_INJURYILLNESS.JOB_TENURE).ToDictionary(t => t.Key ?? "", t => t.Count()); } if (setRecordable != null && setRecordable.VALUE.Contains("daysToClose")) { recordableOrdinals["daysToClose"] = recordableIncidents.Where(i => i.CLOSE_DATE.HasValue).Select(i => ((TimeSpan)(i.INCIDENT_DT - i.CLOSE_DATE)).Days).Select(d => entities.XLAT_DAYS_TO_CLOSE_TRANS.FirstOrDefault(x => (x.MIN_DAYS.HasValue ? d >= x.MIN_DAYS : true) && (x.MAX_DAYS.HasValue ? d <= x.MAX_DAYS : true)).XLAT_CODE).GroupBy(x => x).ToDictionary(x => x.Key ?? "", x => x.Count()); /* * recordableOrdinals["daysToClose"] = recordableIncidents.Where(i => i.CLOSE_DATE.HasValue).Select(i => * EntityFunctions.DiffDays(i.INCIDENT_DT, i.CLOSE_DATE)).Select(d => entities.XLAT_DAYS_TO_CLOSE_TRANS.FirstOrDefault(x => * (x.MIN_DAYS.HasValue ? d >= x.MIN_DAYS : true) && (x.MAX_DAYS.HasValue ? d <= x.MAX_DAYS : true)).XLAT_CODE).GroupBy(x => x).ToDictionary(x => * x.Key ?? "", x => x.Count()); */ } } var dataList = EHSDataMapping.SelectEHSDataPeriodList(entities, plant.PLANT_ID, currDate, incidentMeasureIDs, true, updateIndicator); EHSDataMapping.SetEHSDataValue(dataList, nearMissMeasureID, nearMisses, updateIndicator); EHSDataMapping.SetEHSDataValue(dataList, firstAidMeasureID, firstAidCases, updateIndicator); EHSDataMapping.SetEHSDataValue(dataList, recordableMeasureID, recordables, updateIndicator); EHSDataMapping.SetEHSDataValue(dataList, lostTimeCaseMeasureID, lostTimeCases, updateIndicator); EHSDataMapping.SetEHSDataValue(dataList, fatalityMeasureID, fatalities, updateIndicator); EHSDataMapping.SetEHSDataValue(dataList, otherIncidentsID, otherIncidents, updateIndicator); EHSDataMapping.SetEHSDataValue(dataList, closedInvestigationMeasureID, closedInvestigations, updateIndicator); foreach (var data in dataList) { if (data.VALUE != 0) { if (data.EntityState == EntityState.Detached) { entities.EHS_DATA.AddObject(data); } if (incidentsForDay.Any()) { if (data.MEASURE_ID == firstAidMeasureID) { UpdateOrdinalData(entities, data, firstAidOrdinals); } else if (data.MEASURE_ID == recordableMeasureID) { UpdateOrdinalData(entities, data, recordableOrdinals); } } } else if (data.EntityState != EntityState.Detached && data.VALUE == 0) { if (data.MEASURE_ID == firstAidMeasureID) { foreach (var key in firstAidOrdinals.Keys.ToArray()) { firstAidOrdinals[key] = new Dictionary <string, int>(); } UpdateOrdinalData(entities, data, firstAidOrdinals); } else if (data.MEASURE_ID == recordableMeasureID) { foreach (var key in recordableOrdinals.Keys.ToArray()) { recordableOrdinals[key] = new Dictionary <string, int>(); } UpdateOrdinalData(entities, data, recordableOrdinals); } entities.DeleteObject(data); } } } // MONTHLY INCIDENTS (from PLANT_ACCOUNTING) var accountingForPlant = entities.PLANT_ACCOUNTING.Where(a => a.PLANT_ID == plant.PLANT_ID); List <EHS_DATA> ehsdataList; for (var currDate = fromDate; currDate <= toDate; currDate = currDate.AddDays(1)) { decimal timeLost = 0; decimal timeRestricted = 0; if (currDate.Day == 1) { // get or create data records for the 1st day of the month ehsdataList = EHSDataMapping.SelectEHSDataPeriodList(entities, plant.PLANT_ID, currDate, incidentMonthlyMeasureIDs, true, updateIndicator); var accountingForMonth = accountingForPlant.FirstOrDefault(a => a.PERIOD_YEAR == currDate.Year && a.PERIOD_MONTH == currDate.Month); if (accountingForMonth != null) { timeLost = accountingForMonth.TIME_LOST ?? 0; timeRestricted = accountingForMonth.TOTAL_DAYS_RESTRICTED ?? 0; } EHSDataMapping.SetEHSDataValue(ehsdataList, timeLostMeasureID, timeLost, updateIndicator); EHSDataMapping.SetEHSDataValue(ehsdataList, timeRestrictedMeasureID, timeRestricted, updateIndicator); WriteLine("ACCOUNTING Rollup For Plant " + pact.PLANT_ID + " date = " + currDate.ToShortDateString()); } else { // get any spurrious data that might have been entered manually. we will want to delete these ehsdataList = EHSDataMapping.SelectEHSDataPeriodList(entities, plant.PLANT_ID, currDate, incidentMonthlyMeasureIDs, false, updateIndicator); } foreach (var data in ehsdataList) { if (data.EntityState == EntityState.Detached && data.VALUE.HasValue && currDate.Day == 1) { entities.EHS_DATA.AddObject(data); } else if (data.EntityState != EntityState.Detached && currDate.Day != 1) { entities.DeleteObject(data); } } } } entities.SaveChanges(); } entities.SaveChanges(); } } catch (Exception ex) { output.AppendFormat("EHS Data RollUp Error - {0}", ex); } return(nextStep); }
public static void SaveMonthlyData(decimal plantID, DateTime day, Dictionary <string, string> allData) { using (var entities = new PSsqmEntities()) { var measures = from m in entities.EHS_MEASURE where m.MEASURE_CATEGORY == "SAFE" && m.MEASURE_SUBCATEGORY == "SAFE1" && m.STATUS == "A" && m.FREQUENCY == "M" select new { m.MEASURE_ID, m.DATA_TYPE }; var startOfMonth = new DateTime(day.Year, day.Month, 1); foreach (var measure in measures) { string text = allData[measure.MEASURE_ID.ToString()]; bool hasText = !string.IsNullOrWhiteSpace(text); // We determine if we need to add a new entry into the database by looking for if there is any data. bool addNew = true; var data = entities.EHS_DATA.FirstOrDefault(d => EntityFunctions.TruncateTime(d.DATE) == startOfMonth.Date && d.PLANT_ID == plantID && d.MEASURE_ID == measure.MEASURE_ID); if (data != null) { addNew = false; // Check if this was inserted by an automated process, and ignore any changes if so. if (!data.UPDATE_IND.HasValue || data.UPDATE_IND.Value == 0) { // If we had some text in the RadTextBox, then we'll update the entry, otherwise we'll delete it. if (hasText) { if (measure.DATA_TYPE == "V" || measure.DATA_TYPE == "F") { string newValue = Regex.Replace(text, "[^0-9]", ""); data.VALUE = decimal.Parse(newValue); } else if (measure.DATA_TYPE == "A" || measure.DATA_TYPE == "Y") { data.ATTRIBUTE = text; } } else { entities.DeleteObject(data); } } } // This will only add a new entry if there was no entry found already and we had some text in the RadTextBox. if (addNew && hasText) { var newData = new EHS_DATA() { MEASURE_ID = measure.MEASURE_ID, PLANT_ID = plantID, DATE = startOfMonth }; if (measure.DATA_TYPE == "V") { string newValue = Regex.Replace(text, "[^0-9]", ""); newData.VALUE = decimal.Parse(newValue); } else if (measure.DATA_TYPE == "A" || measure.DATA_TYPE == "Y") { newData.ATTRIBUTE = text; } entities.EHS_DATA.AddObject(newData); } } // Save the changes we made to the database. This has to be done before the Plant Accounting stuff so there is data to find. entities.SaveChanges(); // Add the time worked and time lost items to the Plant Accounting table. var dataTimeWorked = entities.EHS_DATA.FirstOrDefault(d => EntityFunctions.TruncateTime(d.DATE) == startOfMonth.Date && d.PLANT_ID == plantID && d.EHS_MEASURE.MEASURE_CD == "S60002"); var dataTimeLost = entities.EHS_DATA.FirstOrDefault(d => EntityFunctions.TruncateTime(d.DATE) == startOfMonth.Date && d.PLANT_ID == plantID && d.EHS_MEASURE.MEASURE_CD == "S60001"); var pa = EHSModel.LookupPlantAccounting(entities, plantID, day.Year, day.Month, true); pa.TIME_WORKED = dataTimeWorked == null ? null : dataTimeWorked.VALUE; pa.TIME_LOST = dataTimeLost == null ? null : dataTimeLost.VALUE; EHSModel.UpdatePlantAccounting(entities, pa); } }
public static void SaveWeeklyData(decimal plantID, DateTime day, Dictionary <string, string> allData) { using (var entities = new PSsqmEntities()) { var measures = from m in entities.EHS_MEASURE where m.MEASURE_CATEGORY == "SAFE" && m.MEASURE_SUBCATEGORY == "SAFE1" && m.STATUS == "A" && m.FREQUENCY == "W" select new { m.MEASURE_ID, m.DATA_TYPE }; var startOfWeek = FirstDayOfWeek(day, calendar, calendarWeekRule, firstDayOfWeek); var endOfWeek = startOfWeek.AddDays(6); foreach (var measure in measures) { string text = allData[measure.MEASURE_ID.ToString()]; bool hasText = !string.IsNullOrWhiteSpace(text); // We determine if we need to add a new entry into the database by looking for if there is any data. bool addNew = true; var data = entities.EHS_DATA.FirstOrDefault(d => EntityFunctions.TruncateTime(d.DATE) == endOfWeek.Date && d.PLANT_ID == plantID && d.MEASURE_ID == measure.MEASURE_ID); if (data != null) { addNew = false; // Check if this was inserted by an automated process, and ignore any changes if so. if (!data.UPDATE_IND.HasValue || data.UPDATE_IND.Value == 0) { // If we had some text in the RadTextBox, then we'll update the entry, otherwise we'll delete it. if (hasText) { if (measure.DATA_TYPE == "V" || measure.DATA_TYPE == "F") { data.VALUE = decimal.Parse(text); } else if (measure.DATA_TYPE == "A" || measure.DATA_TYPE == "Y") { data.ATTRIBUTE = text; } } else { entities.DeleteObject(data); } } } // This will only add a new entry if there was no entry found already and we had some text in the RadTextBox. if (addNew && hasText) { var newData = new EHS_DATA() { MEASURE_ID = measure.MEASURE_ID, PLANT_ID = plantID, DATE = endOfWeek }; if (measure.DATA_TYPE == "V") { newData.VALUE = decimal.Parse(text); } else if (measure.DATA_TYPE == "A" || measure.DATA_TYPE == "Y") { newData.ATTRIBUTE = text; } entities.EHS_DATA.AddObject(newData); } } // Save the changes we made to the database. entities.SaveChanges(); } }
public static void SaveDailyData(decimal plantID, DateTime day, Dictionary <string, Dictionary <string, dynamic> > allData) { using (var entities = new PSsqmEntities()) { var measures = from m in entities.EHS_MEASURE where m.MEASURE_CATEGORY == "SAFE" && m.MEASURE_SUBCATEGORY == "SAFE1" && m.STATUS == "A" && m.FREQUENCY == "D" select new { m.MEASURE_ID, m.DATA_TYPE }; var startOfWeek = FirstDayOfWeek(day, calendar, calendarWeekRule, firstDayOfWeek); var currentDay = startOfWeek; for (int i = 0; i < 7; ++i, currentDay = currentDay.AddDays(1)) { string dayName = currentDay.ToString("ddd"); var dayData = entities.EHS_DATA.Where(d => EntityFunctions.TruncateTime(d.DATE) == currentDay.Date && d.PLANT_ID == plantID); foreach (var measure in measures) { bool measureIsValue = measure.DATA_TYPE == "V" || measure.DATA_TYPE == "F" || measure.DATA_TYPE == "O"; var measure_data = allData[dayName + "|" + measure.MEASURE_ID]; string text = measure_data["value"]; bool hasText = !string.IsNullOrWhiteSpace(text); // We determine if we need to add a new entry into the database by looking for if there is any data. bool addNew = true; if (dayData.Any()) { var data = dayData.FirstOrDefault(d => d.MEASURE_ID == measure.MEASURE_ID); if (data != null) { addNew = false; // Check if this was inserted by an automated process, and ignore any changes if so. if (!data.UPDATE_IND.HasValue || data.UPDATE_IND.Value == 0) { // If we had some text in the RadTextBox, then we'll update the entry, otherwise we'll delete it. if (hasText) { if (measureIsValue) { data.VALUE = decimal.Parse(text); } else if (measure.DATA_TYPE == "A" || measure.DATA_TYPE == "Y") { data.ATTRIBUTE = text; } if (measure_data.ContainsKey("ordinal")) { UpdateOrdinalData(entities, data.DATA_ID, measure_data["ordinal"]); } } else { entities.DeleteObject(data); } } } } // This will only add a new entry if there was no entry found already and we had some text in the RadTextBox. if (addNew && hasText) { var newData = new EHS_DATA() { MEASURE_ID = measure.MEASURE_ID, PLANT_ID = plantID, DATE = currentDay }; if (measureIsValue) { newData.VALUE = decimal.Parse(text); } else if (measure.DATA_TYPE == "A" || measure.DATA_TYPE == "Y") { newData.ATTRIBUTE = text; } entities.EHS_DATA.AddObject(newData); if (measure_data.ContainsKey("ordinal")) { AddOrdinalData(entities, newData, measure_data["ordinal"]); } } } } // Save the changes we made to the database. This has to be done before the Plant Accounting stuff so there is data to find. entities.SaveChanges(); // Add the recorded cases and time lost cases to the Plant Accounting table. var endOfWeek = startOfWeek.AddDays(6); DateTime startOfMonth, startOfNextMonth; PLANT_ACCOUNTING pa; // This checks for a week that crosses over a month. if (startOfWeek.Month != endOfWeek.Month) { startOfMonth = new DateTime(startOfWeek.Year, startOfWeek.Month, 1); startOfNextMonth = startOfMonth.AddMonths(1); pa = EHSModel.LookupPlantAccounting(entities, plantID, startOfMonth.Year, startOfMonth.Month, true); pa.RECORDED_CASES = entities.EHS_DATA.Where(d => EntityFunctions.TruncateTime(d.DATE) >= startOfMonth.Date && EntityFunctions.TruncateTime(d.DATE) < startOfNextMonth && d.PLANT_ID == plantID && d.EHS_MEASURE.MEASURE_CD == "S20004").Sum(o => o.VALUE) ?? null; pa.TIME_LOST_CASES = entities.EHS_DATA.Where(d => EntityFunctions.TruncateTime(d.DATE) >= startOfMonth.Date && EntityFunctions.TruncateTime(d.DATE) < startOfNextMonth && d.PLANT_ID == plantID && d.EHS_MEASURE.MEASURE_CD == "S20005").Sum(o => o.VALUE) ?? null; EHSModel.UpdatePlantAccounting(entities, pa, false); } startOfMonth = new DateTime(endOfWeek.Year, endOfWeek.Month, 1); startOfNextMonth = startOfMonth.AddMonths(1); pa = EHSModel.LookupPlantAccounting(entities, plantID, startOfMonth.Year, startOfMonth.Month, true); pa.RECORDED_CASES = entities.EHS_DATA.Where(d => EntityFunctions.TruncateTime(d.DATE) >= startOfMonth.Date && EntityFunctions.TruncateTime(d.DATE) < startOfNextMonth && d.PLANT_ID == plantID && d.EHS_MEASURE.MEASURE_CD == "S20004").Sum(o => o.VALUE) ?? null; pa.TIME_LOST_CASES = entities.EHS_DATA.Where(d => EntityFunctions.TruncateTime(d.DATE) >= startOfMonth.Date && EntityFunctions.TruncateTime(d.DATE) < startOfNextMonth && d.PLANT_ID == plantID && d.EHS_MEASURE.MEASURE_CD == "S20005").Sum(o => o.VALUE) ?? null; EHSModel.UpdatePlantAccounting(entities, pa); } }