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);
}
}
