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;
}
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);
}
protected decimal CreateReAudit(decimal auditId)
{
decimal reauditId = 0;
PSsqmEntities entities = new PSsqmEntities();
AUDIT oldAudit = EHSAuditMgr.SelectAuditById(entities, auditId);
if (oldAudit != null)
{
// create the copy
PLANT auditPlant = SQMModelMgr.LookupPlant((decimal)oldAudit.DETECT_PLANT_ID);
DateTime localTime = WebSiteCommon.LocalTime(DateTime.UtcNow, auditPlant.LOCAL_TIMEZONE);
AUDIT reAudit = new AUDIT()
{
DETECT_COMPANY_ID = Convert.ToDecimal(oldAudit.DETECT_COMPANY_ID),
DETECT_BUS_ORG_ID = oldAudit.DETECT_BUS_ORG_ID,
DETECT_PLANT_ID = oldAudit.DETECT_PLANT_ID,
DEPT_ID = oldAudit.DEPT_ID,
AUDIT_TYPE = oldAudit.AUDIT_TYPE,
CREATE_DT = localTime,
CREATE_BY = SessionManager.UserContext.Person.FIRST_NAME + " " + SessionManager.UserContext.Person.LAST_NAME,
DESCRIPTION = oldAudit.DESCRIPTION,
CREATE_PERSON = SessionManager.UserContext.Person.PERSON_ID, // do we want to set this to admin?
AUDIT_DT = localTime,
AUDIT_TYPE_ID = oldAudit.AUDIT_TYPE_ID,
AUDIT_PERSON = SessionManager.UserContext.Person.PERSON_ID,
CURRENT_STATUS = "A",
PERCENT_COMPLETE = 0,
TOTAL_SCORE = 0,
AUDITING_ID = auditId
};
entities.AddToAUDIT(reAudit);
entities.SaveChanges();
reauditId = reAudit.AUDIT_ID;
AUDIT_ANSWER answer = null;
List <EHSAuditQuestion> questions = EHSAuditMgr.SelectAuditQuestionList(reAudit.AUDIT_TYPE_ID, 0, auditId);
foreach (var q in questions)
{
answer = new AUDIT_ANSWER()
{
AUDIT_ID = reauditId,
AUDIT_QUESTION_ID = q.QuestionId,
ANSWER_VALUE = "",
ORIGINAL_QUESTION_TEXT = q.QuestionText,
//COMMENT = q.AnswerComment
};
entities.AddToAUDIT_ANSWER(answer);
}
entities.SaveChanges();
}
return(reauditId);
}
public int UpdateCausation(decimal incidentID)
{
int status = 0;
using (PSsqmEntities ctx = new PSsqmEntities())
{
status = ctx.ExecuteStoreCommand("DELETE FROM INCFORM_CAUSATION WHERE INCIDENT_ID = " + incidentID.ToString());
if (!string.IsNullOrEmpty(ddlCausation.SelectedValue))
{
INCFORM_CAUSATION causation = new INCFORM_CAUSATION();
causation.INCIDENT_ID = incidentID;
causation.CAUSEATION_CD = ddlCausation.SelectedValue;
causation.TEAM_LIST = tbTeam.Text.Trim();
causation.LAST_UPD_BY = SessionManager.UserContext.UserName();
causation.LAST_UPD_DT = WebSiteCommon.LocalTime(DateTime.UtcNow, IncidentLocationTZ);
ctx.AddToINCFORM_CAUSATION(causation);
status = ctx.SaveChanges();
EHSIncidentMgr.UpdateIncidentStatus(incidentID, IncidentStepStatus.rootcauseComplete, WebSiteCommon.LocalTime(DateTime.UtcNow, IncidentLocationTZ));
}
else
{
}
}
return(status);
}
public static CURRENCY_XREF InsertRate(string currencyCode, int year, int month, double rate)
{
CURRENCY_XREF xref;
using (PSsqmEntities entities = new PSsqmEntities())
{
try
{
xref = (from x in entities.CURRENCY_XREF
where (x.EFF_YEAR == year && x.EFF_MONTH == month && x.CURRENCY_CODE == currencyCode)
select x).Single();
}
catch
{
xref = new CURRENCY_XREF();
xref.EFF_YEAR = year;
xref.EFF_MONTH = month;
entities.AddToCURRENCY_XREF(xref);
}
xref.CURRENCY_CODE = currencyCode;
xref.BASE_CURRENCY_RATE = (decimal)rate;
entities.SaveChanges();
}
return(xref);
}
public static DOCUMENT Add(String filename, String description, decimal?display_type, string docScope, decimal recordID, Stream file)
{
DOCUMENT ret = null;
try
{
using (PSsqmEntities entities = new PSsqmEntities())
{
DOCUMENT d = new DOCUMENT();
d.FILE_NAME = filename;
d.FILE_DESC = description;
//To-do: what do we do when company_id is not set, like when they choose this
// from the Business Org master screen?
d.COMPANY_ID = SessionManager.EffLocation.Company.COMPANY_ID;
d.OWNER_ID = SessionManager.UserContext.Person.PERSON_ID;
d.RECORD_ID = recordID;
d.UPLOADED_BY = SessionManager.UserContext.Person.FIRST_NAME + " " + SessionManager.UserContext.Person.LAST_NAME;
d.UPLOADED_DT = WebSiteCommon.CurrentUTCTime();
d.LANGUAGE_ID = (int)SessionManager.UserContext.Person.PREFERRED_LANG_ID;
d.DOCUMENT_SCOPE = docScope;
if (display_type.HasValue)
{
d.DISPLAY_TYPE = display_type.Value;
}
if (d.DOCUMENT_FILE == null)
{
d.DOCUMENT_FILE = new DOCUMENT_FILE();
}
//read in the file contents
file.Seek(0, SeekOrigin.Begin);
byte[] bytearray = new byte[file.Length];
int count = 0;
while (count < file.Length)
{
bytearray[count++] = Convert.ToByte(file.ReadByte());
}
d.DOCUMENT_FILE.DOCUMENT_DATA = bytearray;
d.FILE_SIZE = file.Length;
// d.DISPLAY_TYPE = Path.GetExtension(filename);
entities.AddToDOCUMENT(d);
entities.SaveChanges();
ret = d;
}
}
catch (Exception e)
{
//SQMLogger.LogException(e);
ret = null;
}
return(ret);
}
private int SaveContainment(decimal incidentId, List <INCFORM_CONTAIN> itemList)
{
PSsqmEntities entities = new PSsqmEntities();
int status = 0;
using (var ctx = new PSsqmEntities())
{
ctx.ExecuteStoreCommand("DELETE FROM INCFORM_CONTAIN WHERE INCIDENT_ID = {0}", incidentId);
}
int seq = 0;
foreach (INCFORM_CONTAIN item in itemList)
{
var newItem = new INCFORM_CONTAIN();
if (!string.IsNullOrEmpty(item.ITEM_DESCRIPTION))
{
seq = seq + 1;
newItem.INCIDENT_ID = incidentId;
newItem.ITEM_SEQ = seq;
newItem.ITEM_DESCRIPTION = item.ITEM_DESCRIPTION;
newItem.ASSIGNED_PERSON_ID = item.ASSIGNED_PERSON_ID;
newItem.START_DATE = item.START_DATE;
newItem.LAST_UPD_BY = SessionManager.UserContext.Person.FIRST_NAME + " " + SessionManager.UserContext.Person.LAST_NAME;
newItem.LAST_UPD_DT = DateTime.Now;
entities.AddToINCFORM_CONTAIN(newItem);
status = entities.SaveChanges();
}
}
return(status);
}
public static int UpdateTargetCalcList(PSsqmEntities ctx, List <PERSPECTIVE_TARGET_CALC> tcList, string updateBy)
{
int status = 0;
try
{
for (int n = 0; n < tcList.Count; n++)
{
PERSPECTIVE_TARGET_CALC tc = tcList[n];
if (tc.EntityState != EntityState.Unchanged)
{
tc = (PERSPECTIVE_TARGET_CALC)SQMModelMgr.SetObjectTimestamp(tc, updateBy, tc.EntityState);
if (tc.EntityState == EntityState.Detached)
{
ctx.AddToPERSPECTIVE_TARGET_CALC(tc);
}
}
}
status = ctx.SaveChanges();
}
catch (Exception e)
{
// SQMLogger.LogException(e);
}
return(status);
}
public static void TryClosePrevention(decimal incidentId, decimal personId)
{
var entities = new PSsqmEntities();
INCIDENT incident = SelectIncidentById(entities, incidentId);
bool shouldUpdateAuditPerson = true;
if (ShouldPreventionClose(incident))
{
incident.CLOSE_DATE = DateTime.Now;
incident.CLOSE_PERSON = personId;
SetTaskComplete(incidentId, 45);
shouldUpdateAuditPerson = false;
}
else
{
incident.CLOSE_DATE = null;
}
if (ShouldPreventionCloseAudited(incident))
{
incident.CLOSE_DATE_DATA_COMPLETE = DateTime.Now;
if (shouldUpdateAuditPerson == true)
{
incident.AUDIT_PERSON = personId;
}
}
else
{
incident.CLOSE_DATE_DATA_COMPLETE = null;
}
entities.SaveChanges();
}
public static void TryCloseIncident(decimal incidentId)
{
var entities = new PSsqmEntities();
INCIDENT incident = SelectIncidentById(entities, incidentId);
if (ShouldIncidentReportClose(incident))
{
incident.CLOSE_DATE = DateTime.Now;
SetTaskComplete(incidentId, 40);
}
else
{
incident.CLOSE_DATE = null;
}
if (ShouldIncidentCloseDataComplete(incident))
{
incident.CLOSE_DATE_DATA_COMPLETE = DateTime.Now;
}
else
{
incident.CLOSE_DATE_DATA_COMPLETE = null;
}
entities.SaveChanges();
}
protected int SaveRootCauses(decimal incidentId, List <INCFORM_ROOT5Y> itemList)
{
PSsqmEntities entities = new PSsqmEntities();
int status = 0;
using (var ctx = new PSsqmEntities())
{
ctx.ExecuteStoreCommand("DELETE FROM INCFORM_ROOT5Y WHERE INCIDENT_ID = {0}", incidentId);
}
int seq = 0;
foreach (INCFORM_ROOT5Y item in itemList)
{
var newItem = new INCFORM_ROOT5Y();
if (!string.IsNullOrEmpty(item.ITEM_DESCRIPTION))
{
seq = seq + 1;
newItem.INCIDENT_ID = incidentId;
newItem.ITEM_SEQ = seq;
newItem.ITEM_DESCRIPTION = item.ITEM_DESCRIPTION;
newItem.LAST_UPD_BY = SessionManager.UserContext.Person.FIRST_NAME + " " + SessionManager.UserContext.Person.LAST_NAME;
newItem.LAST_UPD_DT = DateTime.Now;
entities.AddToINCFORM_ROOT5Y(newItem);
status = entities.SaveChanges();
}
}
return(status);
}
public static void CheckForUpdates(string baseCurrencyCode)
{
entities = new PSsqmEntities();
//var currencyCodes = (from c in entities.CURRENCY where c.CURRENCY_CODE != baseCurrencyCode select c.CURRENCY_CODE).ToList();
var currencyCodes = (from c in entities.CURRENCY select c.CURRENCY_CODE).ToList();
DateTime lastMonth = DateTime.Now.AddMonths(-1);
foreach (string quoteCurrencyCode in currencyCodes)
{
DateTime latestDate = new DateTime(2000, 1, 1);
CURRENCY_XREF latestEntry = (from cx in entities.CURRENCY_XREF
where cx.CURRENCY_CODE == quoteCurrencyCode
orderby cx.EFF_YEAR descending, cx.EFF_MONTH descending
select cx).FirstOrDefault();
if (latestEntry != null)
{
latestDate = new DateTime(latestEntry.EFF_YEAR, latestEntry.EFF_MONTH, 1);
}
if (latestDate < lastMonth)
{
UpdateCurrency(baseCurrencyCode, quoteCurrencyCode, latestDate);
}
}
entities.SaveChanges();
}
public int AddUpdateINCFORM_ALERT(decimal incidentId)
{
lblStatusMsg.Visible = false;
int status = 0;
bool allFieldsComplete = true;
localCtx = new PSsqmEntities();
INCFORM_ALERT incidentAlert = EHSIncidentMgr.LookupIncidentAlert(localCtx, IncidentId);
if (incidentAlert == null) // new alert
{
incidentAlert = new INCFORM_ALERT();
incidentAlert.INCIDENT_ID = LocalIncident.INCIDENT_ID;
incidentAlert.ALERT_TYPE = ((int)TaskRecordType.HealthSafetyIncident).ToString();
incidentAlert.CREATE_DT = DateTime.UtcNow;
incidentAlert.CREATE_BY = SessionManager.UserContext.UserName();
localCtx.AddToINCFORM_ALERT(incidentAlert);
lblAlertStatus.Text = XLATList.Where(l => l.XLAT_GROUP == "TASK_STATUS" && l.XLAT_CODE == "0").FirstOrDefault().DESCRIPTION;
}
SaveAttachments(incidentId);
incidentAlert.LOCATION_LIST = "";
foreach (RadComboBoxItem item in ddlLocations.Items.Where(i => i.Checked == true && i.Value.Contains("BU") == false).ToList())
{
incidentAlert.LOCATION_LIST += string.IsNullOrEmpty(incidentAlert.LOCATION_LIST) ? item.Value : (item.Value + ",");
}
incidentAlert.ALERT_GROUP = "";
foreach (RadComboBoxItem item in ddlNotifyGroup.Items.Where(i => i.Checked == true).ToList())
{
incidentAlert.ALERT_GROUP += string.IsNullOrEmpty(incidentAlert.ALERT_GROUP) ? item.Value : (item.Value + ",");
}
incidentAlert.RESPONSIBLE_GROUP = ddlResponsibleGroup.SelectedValue;
// incidentAlert.ALERT_DESC = tbAlertDesc.Text;
incidentAlert.COMMENTS = tbComments.Text;
incidentAlert.DUE_DT = rdpDueDate.SelectedDate;
// Update CEO-Comments value.
incidentAlert.CEO_COMMENTS = tbCeoComments.Text.Trim();
// send general notifications
if (incidentAlert.INCIDENT_ALERT_ID < 1)
{
EHSNotificationMgr.NotifyIncidentAlert(LocalIncident, ((int)SysPriv.notify).ToString(), "380",
ddlLocations.Items.Where(i => i.Checked == true && i.Value.Contains("BU") == false).Select(i => Convert.ToDecimal(i.Value)).ToList());
}
List <TASK_STATUS> alertTaskList = UpdateAlertTaskList(EHSIncidentMgr.GetAlertTaskList(localCtx, LocalIncident.INCIDENT_ID));
status = localCtx.SaveChanges();
// send specific task assignments
EHSNotificationMgr.NotifyIncidentAlertTaskAssignment(LocalIncident, alertTaskList.Where(l => l.RESPONSIBLE_ID.HasValue).ToList());
return(status);
}
public static UOM UpdateUOM(PSsqmEntities entities, UOM uom, decimal convertToUOMID, double conversionFactor)
{
UOM ret;
try
{
if (uom.UOM_ID > 0)
{
ret = (from u in entities.UOM.Include("UOM_XREF") where u.UOM_ID == uom.UOM_ID select u).Single();
if (ret != null)
{
ret = (UOM)SQMModelMgr.CopyObjectValues(ret, uom, false);
ret.UOM_XREF.FirstOrDefault().UOM_TO = convertToUOMID;
ret.UOM_XREF.FirstOrDefault().CONVERSION = Convert.ToDecimal(conversionFactor);
entities.SaveChanges();
}
}
else
{
entities.AddToUOM(uom);
entities.SaveChanges();
if (convertToUOMID > 0 && uom.UOM_ID > 0)
{
UOM_XREF xref = new UOM_XREF();
xref.UOM_FROM = uom.UOM_ID;
xref.UOM_TO = convertToUOMID;
xref.CONVERSION = Convert.ToDecimal(conversionFactor);
xref.OPERATOR = "MULT";
entities.AddToUOM_XREF(xref);
entities.SaveChanges();
}
}
ret = uom;
}
catch
{
ret = null;
}
return(ret);
}
public static void CloseIncident(decimal incidentId)
{
var entities = new PSsqmEntities();
var incident = (from i in entities.INCIDENT where i.INCIDENT_ID == incidentId select i).FirstOrDefault();
if (incident != null)
{
incident.CLOSE_DATE = DateTime.Now;
entities.SaveChanges();
}
}
protected AUDIT_SCHEDULER CreateNewAuditScheduler()
{
decimal auditScheduleId = 0;
PLANT auditPlant = SQMModelMgr.LookupPlant(Convert.ToDecimal(hdnAuditLocation.Value.ToString()));
var newAuditScheduler = new AUDIT_SCHEDULER()
{
DAY_OF_WEEK = Convert.ToInt32(rddlDayOfWeek.SelectedValue.ToString()),
INACTIVE = false,
JOBCODE_CD = rddlAuditJobcodes.SelectedValue.ToString(),
PLANT_ID = auditPlant.PLANT_ID,
CREATE_DT = DateTime.Now,
CREATE_PERSON = SessionManager.UserContext.Person.PERSON_ID,
AUDIT_TYPE_ID = auditTypeId
};
entities.AddToAUDIT_SCHEDULER(newAuditScheduler);
entities.SaveChanges();
auditScheduleId = newAuditScheduler.AUDIT_SCHEDULER_ID;
return(newAuditScheduler);
}
public static void CloseAudit(decimal auditId)
{
var entities = new PSsqmEntities();
var audit = (from i in entities.AUDIT where i.AUDIT_ID == auditId select i).FirstOrDefault();
if (audit != null)
{
audit.CLOSE_DATE = DateTime.Now;
entities.SaveChanges();
}
}
protected void rbSave_Click(object sender, EventArgs e)
{
int currentMonth = ((DateTime)radPeriodSelect.SelectedDate).Month;
int currentYear = ((DateTime)radPeriodSelect.SelectedDate).Year;
foreach (RepeaterItem ri in rptCurrency.Items)
{
HiddenField hf = (HiddenField)ri.FindControl("hfCurrencyCode");
RadNumericTextBox tb = (RadNumericTextBox)ri.FindControl("tbRate");
if (hf != null && tb != null)
{
string currencyCode = hf.Value;
//decimal rate = 0;
//Decimal.TryParse(tb.Text, out rate);
double rateVal = 0;
Double.TryParse(tb.Text, out rateVal);
if (rateVal > 0)
{
//rate = (decimal)rateVal;
var entities = new PSsqmEntities();
var queryObject = (from cx in entities.CURRENCY_XREF where cx.EFF_YEAR == currentYear &&
cx.EFF_MONTH == currentMonth &&
cx.CURRENCY_CODE == currencyCode
select cx).FirstOrDefault();
if (queryObject == null)
{
CURRENCY_XREF cur = new CURRENCY_XREF()
{
EFF_YEAR = currentYear,
EFF_MONTH = currentMonth,
CURRENCY_CODE = currencyCode,
BASE_CURRENCY_RATE = (decimal)rateVal
};
entities.CURRENCY_XREF.AddObject(cur);
}
else
{
queryObject.BASE_CURRENCY_RATE = (decimal)rateVal;
}
entities.SaveChanges();
}
}
}
lblMessage.Visible = true;
lblMessage.Text = "<div style=\"padding: 12px 0;\">Changes have been saved.</div>";
}
private int SaveLostTime(decimal incidentId, List <INCFORM_LOSTTIME_HIST> itemList)
{
int status = 0;
PSsqmEntities entities = new PSsqmEntities();
using (var ctx = new PSsqmEntities())
{
ctx.ExecuteStoreCommand("DELETE FROM INCFORM_LOSTTIME_HIST WHERE INCIDENT_ID = {0}", incidentId);
}
int seq = 0;
foreach (INCFORM_LOSTTIME_HIST item in itemList)
{
var newItem = new INCFORM_LOSTTIME_HIST();
if (!String.IsNullOrEmpty(item.WORK_STATUS) && item.WORK_STATUS != "")
{
newItem.INCIDENT_ID = incidentId;
newItem.ITEM_DESCRIPTION = item.ITEM_DESCRIPTION;
newItem.WORK_STATUS = item.WORK_STATUS;
newItem.BEGIN_DT = item.BEGIN_DT;
newItem.RETURN_TOWORK_DT = item.RETURN_TOWORK_DT;
newItem.NEXT_MEDAPPT_DT = item.NEXT_MEDAPPT_DT;
newItem.RETURN_EXPECTED_DT = item.RETURN_EXPECTED_DT;
newItem.LAST_UPD_BY = SessionManager.UserContext.Person.FIRST_NAME + " " + SessionManager.UserContext.Person.LAST_NAME;
newItem.LAST_UPD_DT = WebSiteCommon.LocalTime(DateTime.UtcNow, IncidentLocationTZ);
entities.AddToINCFORM_LOSTTIME_HIST(newItem);
status = entities.SaveChanges();
}
}
if (seq > 0)
{
EHSIncidentMgr.UpdateIncidentStatus(incidentId, IncidentStepStatus.workstatus, WebSiteCommon.LocalTime(DateTime.UtcNow, IncidentLocationTZ));
}
/*
* if (status > -1)
* {
* EHSNotificationMgr.NotifyIncidentStatus(WorkStatusIncident, ((int)SysPriv.update).ToString(), "Work status updated");
* }
*/
return(status);
}
public int AddUpdateINCFORM_APPROVAL(decimal incidentId)
{
var itemList = new List <INCFORM_APPROVAL>();
int status = 0;
using (PSsqmEntities ctx = new PSsqmEntities())
{
status = ctx.ExecuteStoreCommand("DELETE FROM INCFORM_APPROVAL WHERE INCIDENT_ID = " + incidentId.ToString());
foreach (RepeaterItem item in rptApprovals.Items)
{
HiddenField hf = (HiddenField)item.FindControl("hfItemSeq");
Label lba = (Label)item.FindControl("lbApprover");
Label lbm = (Label)item.FindControl("lbApproveMessage");
Label lb = (Label)item.FindControl("lbItemSeq");
Label lbjobd = (Label)item.FindControl("lbApproverJob");
CheckBox cba = (CheckBox)item.FindControl("cbIsAccepted");
RadDatePicker rda = (RadDatePicker)item.FindControl("rdpAcceptDate");
if (cba.Checked == true)
{
INCFORM_APPROVAL approval = new INCFORM_APPROVAL();
approval.INCIDENT_ID = incidentId;
approval.ITEM_SEQ = Convert.ToInt32(hf.Value);
approval.IsAccepted = true;
approval.APPROVAL_MESSAGE = lbm.Text;
approval.APPROVER_TITLE = lbjobd.Text;
approval.APPROVAL_DATE = rda.SelectedDate;
hf = (HiddenField)item.FindControl("hfPersonID");
if (string.IsNullOrEmpty(hf.Value) || hf.Value == "0")
{
approval.APPROVER_PERSON_ID = SessionManager.UserContext.Person.PERSON_ID;
approval.APPROVER_PERSON = SessionManager.UserContext.UserName();
}
else
{
approval.APPROVER_PERSON_ID = Convert.ToDecimal(hf.Value);
approval.APPROVER_PERSON = lba.Text;
}
ctx.AddToINCFORM_APPROVAL(approval);
}
}
status = ctx.SaveChanges();
}
return(status);
}
public static PERSPECTIVE_TARGET_CALC UpdateTargetCalc(PSsqmEntities ctx, PERSPECTIVE_TARGET_CALC tc, string updateBy)
{
PERSPECTIVE_TARGET_CALC ret = null;
tc = (PERSPECTIVE_TARGET_CALC)SQMModelMgr.SetObjectTimestamp(tc, updateBy, tc.EntityState);
if (tc.EntityState == EntityState.Detached)
{
ctx.AddToPERSPECTIVE_TARGET_CALC(tc);
}
if (ctx.SaveChanges() > 0)
{
ret = tc;
}
return(ret);
}
public static PERSPECTIVE_TARGET UpdateTarget(PSsqmEntities ctx, PERSPECTIVE_TARGET target, string updateBy)
{
PERSPECTIVE_TARGET ret = null;
target = (PERSPECTIVE_TARGET)SQMModelMgr.SetObjectTimestamp(target, updateBy, target.EntityState);
if (target.EntityState == EntityState.Detached)
{
ctx.AddToPERSPECTIVE_TARGET(target);
}
if (ctx.SaveChanges() > 0)
{
ret = target;
}
return(ret);
}
protected VIDEO UpdateVideo(decimal videoId)
{
// we are not going to let them update any of this info.
VIDEO video = (from i in entities.VIDEO where i.VIDEO_ID == videoId select i).FirstOrDefault();
if (video != null)
{
video.TITLE = tbTitle.Text.ToString();
video.DESCRIPTION = tbDescription.Text.ToString();
video.VIDEO_TYPE = ddlVideoType.SelectedValue.ToString();
video.VIDEO_AVAILABILITY = ddlAvailability.SelectedValue.ToString();
video.VIDEO_STATUS = rcbStatusSelect.SelectedValue.ToString();
video.RELEASE_REQUIRED = cbReleaseForms.Checked;
video.TEXT_ADDED = cbVideoText.Checked;
video.SPEAKER_AUDIO = cbSpeakerAudio.Checked;
entities.SaveChanges();
}
return(video);
}
private int SaveLostTime(decimal incidentId, List <INCFORM_LOSTTIME_HIST> itemList)
{
int status = 0;
PSsqmEntities entities = new PSsqmEntities();
using (var ctx = new PSsqmEntities())
{
ctx.ExecuteStoreCommand("DELETE FROM INCFORM_LOSTTIME_HIST WHERE INCIDENT_ID = {0}", incidentId);
}
int seq = 0;
foreach (INCFORM_LOSTTIME_HIST item in itemList)
{
var newItem = new INCFORM_LOSTTIME_HIST();
if (!String.IsNullOrEmpty(item.WORK_STATUS) && item.WORK_STATUS != "[Select One]")
{
seq = seq + 1;
newItem.INCIDENT_ID = incidentId;
newItem.ITEM_SEQ = seq;
newItem.ITEM_DESCRIPTION = item.ITEM_DESCRIPTION;
newItem.WORK_STATUS = item.WORK_STATUS;
newItem.BEGIN_DT = item.BEGIN_DT;
newItem.RETURN_TOWORK_DT = item.RETURN_TOWORK_DT;
newItem.NEXT_MEDAPPT_DT = item.NEXT_MEDAPPT_DT;
newItem.RETURN_EXPECTED_DT = item.RETURN_EXPECTED_DT;
newItem.LAST_UPD_BY = SessionManager.UserContext.Person.FIRST_NAME + " " + SessionManager.UserContext.Person.LAST_NAME;
newItem.LAST_UPD_DT = DateTime.Now;
entities.AddToINCFORM_LOSTTIME_HIST(newItem);
status = entities.SaveChanges();
}
}
return(status);
}
protected int SaveRootCauses(decimal incidentId, List <INCFORM_ROOT5Y> itemList)
{
PSsqmEntities entities = new PSsqmEntities();
int status = 0;
using (var ctx = new PSsqmEntities())
{
ctx.ExecuteStoreCommand("DELETE FROM INCFORM_ROOT5Y WHERE INCIDENT_ID = {0}", incidentId);
}
int seq = 0;
foreach (INCFORM_ROOT5Y item in itemList)
{
var newItem = new INCFORM_ROOT5Y();
if (!string.IsNullOrEmpty(item.ITEM_DESCRIPTION))
{
seq = seq + 1;
newItem.INCIDENT_ID = incidentId;
newItem.ITEM_SEQ = seq;
newItem.ITEM_TYPE = item.ITEM_TYPE;
newItem.PROBLEM_SERIES = item.PROBLEM_SERIES;
newItem.ITEM_DESCRIPTION = item.ITEM_DESCRIPTION;
newItem.IS_ROOTCAUSE = item.IS_ROOTCAUSE;
newItem.LAST_UPD_BY = SessionManager.UserContext.Person.FIRST_NAME + " " + SessionManager.UserContext.Person.LAST_NAME;
newItem.LAST_UPD_DT = WebSiteCommon.LocalTime(DateTime.UtcNow, IncidentLocationTZ);
entities.AddToINCFORM_ROOT5Y(newItem);
status = entities.SaveChanges();
}
}
if (seq > 0)
{
EHSIncidentMgr.UpdateIncidentStatus(incidentId, IncidentStepStatus.rootcause, WebSiteCommon.LocalTime(DateTime.UtcNow, IncidentLocationTZ));
}
return(status);
}
private int SaveActions(decimal incidentId, List <INCFORM_ACTION> itemList)
{
PSsqmEntities entities = new PSsqmEntities();
int status = 0;
using (var ctx = new PSsqmEntities())
{
ctx.ExecuteStoreCommand("DELETE FROM INCFORM_ACTION WHERE INCIDENT_ID = {0}", incidentId);
}
int seq = 0;
foreach (INCFORM_ACTION item in itemList)
{
var newItem = new INCFORM_ACTION();
if (!string.IsNullOrEmpty(item.ITEM_DESCRIPTION))
{
seq = seq + 1;
newItem.INCIDENT_ID = incidentId;
newItem.ITEM_SEQ = seq;
newItem.ITEM_DESCRIPTION = item.ITEM_DESCRIPTION;
newItem.ASSIGNED_PERSON_ID = item.ASSIGNED_PERSON_ID;
newItem.START_DATE = item.START_DATE;
newItem.COMPLETION_DATE = item.COMPLETION_DATE;
newItem.IsCompleted = item.IsCompleted;
newItem.LAST_UPD_BY = SessionManager.UserContext.Person.FIRST_NAME + " " + SessionManager.UserContext.Person.LAST_NAME;
newItem.LAST_UPD_DT = DateTime.Now;
entities.AddToINCFORM_ACTION(newItem);
status = entities.SaveChanges();
DateTime dueDate = newItem.START_DATE.HasValue ? (DateTime)newItem.START_DATE : DateTime.Now.AddDays(2);
EHSIncidentMgr.CreateOrUpdateTask(incidentId, (decimal)item.ASSIGNED_PERSON_ID, 40, dueDate, item.ITEM_DESCRIPTION);
}
}
return(status);
}
protected void btnClose_Click(object sender, EventArgs e)
{
PSsqmEntities entities = new PSsqmEntities();
if (IncidentId != null)
{
INCIDENT incident = (from i in entities.INCIDENT where i.INCIDENT_ID == IncidentId select i).FirstOrDefault();
if (incident.CLOSE_DATE == null || incident.CLOSE_DATE_DATA_COMPLETE == null)
{
incident.CLOSE_DATE = DateTime.Now;
incident.CLOSE_DATE_DATA_COMPLETE = DateTime.Now;
}
else
{
incident.CLOSE_DATE = null; // Reopen
incident.CLOSE_DATE_DATA_COMPLETE = null;
}
entities.SaveChanges();
}
Response.Redirect("EHS_Incidents.aspx");
}
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);
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
var verifications = new List <INCIDENT_VERIFICATION>();
if (tbInstructions.Text.Trim().Length == 0 || !rdpDueDate.SelectedDate.HasValue)
{
lblRequired.Visible = true;
return;
}
lblRequired.Visible = false;
PSsqmEntities entities = new PSsqmEntities();
if (IsEditContext == true)
{
if (IncidentId != null)
{
// Update description, date
INCIDENT incident = (from i in entities.INCIDENT where i.INCIDENT_ID == IncidentId select i).FirstOrDefault();
incident.DESCRIPTION = tbInstructions.Text;
if (rdpDueDate.SelectedDate.HasValue)
{
incident.INCIDENT_DT = (DateTime)rdpDueDate.SelectedDate;
}
if (!string.IsNullOrEmpty(rcbCases.SelectedValue))
{
incident.VERIFY_PROBCASE_ID = Convert.ToDecimal(rcbCases.SelectedValue);
}
entities.SaveChanges();
// Add notified people and plants to database
foreach (GridViewRow gvr in gvPreventLocationsList.Rows)
{
decimal plantId = (decimal)gvPreventLocationsList.DataKeys[gvr.RowIndex].Value;
if (plantId != null)
{
RadGrid currentGridView = (RadGrid)gvr.FindControl("rgPlantContacts");
foreach (GridDataItem item in currentGridView.Items)
{
decimal personId = (decimal)item.GetDataKeyValue("PERSON_ID");
if (personId != null)
{
var incidentVerification = (from iv in entities.INCIDENT_VERIFICATION
where iv.INCIDENT_ID == IncidentId &&
iv.PLANT_ID == plantId &&
iv.PERSON_ID == personId
select iv).FirstOrDefault();
if (item.Selected == true)
{
var newVerification = new INCIDENT_VERIFICATION()
{
INCIDENT_ID = IncidentId,
PLANT_ID = plantId,
PERSON_ID = personId,
DATE_NOTIFIED = DateTime.Now
};
// Add to list to use for emails
verifications.Add(newVerification);
// Add to database if it does not exist
if (incidentVerification == null)
{
entities.INCIDENT_VERIFICATION.AddObject(newVerification);
entities.SaveChanges();
}
}
else
{
// Delete if exists
if (incidentVerification != null)
{
entities.INCIDENT_VERIFICATION.DeleteObject(incidentVerification);
entities.SaveChanges();
}
}
}
}
}
}
}
}
else // Is add context
{
decimal verifyProbcaseId = 0;
if (!string.IsNullOrEmpty(rcbCases.SelectedValue))
{
verifyProbcaseId = Convert.ToDecimal(rcbCases.SelectedValue);
}
// Add incident to database
var incident = new INCIDENT()
{
DETECT_COMPANY_ID = SessionManager.UserContext.WorkingLocation.Company.COMPANY_ID,
DETECT_BUS_ORG_ID = SessionManager.UserContext.WorkingLocation.BusinessOrg.BUS_ORG_ID,
DETECT_PLANT_ID = SessionManager.UserContext.WorkingLocation.Plant.PLANT_ID,
INCIDENT_TYPE = "EHS",
CREATE_DT = DateTime.Now,
CREATE_BY = SessionManager.UserContext.Person.FIRST_NAME + " " + SessionManager.UserContext.Person.LAST_NAME,
DESCRIPTION = tbInstructions.Text,
CREATE_PERSON = SessionManager.UserContext.Person.PERSON_ID,
INCIDENT_DT = rdpDueDate.SelectedDate.Value,
ISSUE_TYPE = "Prevention Verification",
ISSUE_TYPE_ID = 10,
VERIFY_PROBCASE_ID = verifyProbcaseId
};
entities.INCIDENT.AddObject(incident);
entities.SaveChanges();
decimal incidentId = incident.INCIDENT_ID;
// Add notified people and plants to database
foreach (GridViewRow gvr in gvPreventLocationsList.Rows)
{
decimal plantId = (decimal)gvPreventLocationsList.DataKeys[gvr.RowIndex].Value;
if (plantId != null)
{
RadGrid currentGridView = (RadGrid)gvr.FindControl("rgPlantContacts");
foreach (GridDataItem item in currentGridView.Items)
{
decimal personId = (decimal)item.GetDataKeyValue("PERSON_ID");
if (personId != null)
{
if (item.Selected == true)
{
var incidentVerification = new INCIDENT_VERIFICATION()
{
INCIDENT_ID = incidentId,
PLANT_ID = plantId,
PERSON_ID = personId,
DATE_NOTIFIED = DateTime.Now
};
verifications.Add(incidentVerification);
entities.INCIDENT_VERIFICATION.AddObject(incidentVerification);
entities.SaveChanges();
}
}
}
}
}
}
// Send email(s)
foreach (var v in verifications)
{
var thisVerification = v;
PERSON emailPerson = (from p in entities.PERSON where p.PERSON_ID == thisVerification.PERSON_ID select p).FirstOrDefault();
string emailSubject = SessionManager.PrimaryCompany().COMPANY_NAME + " Issue Acknowledgement Notification"; // AW20140129 - use company name variable instead of hard coding.
string path = "http://" + HttpContext.Current.Request.Url.Authority + "/EHS/EHS_Incident_Verification.aspx";
path += string.Format("?inid={0}&plid={1}&peid={2}", v.INCIDENT_ID, v.PLANT_ID, emailPerson.PERSON_ID);
var sb = new StringBuilder();
sb.AppendLine("<p>You have been sent an issue acknowledgement notification from " + SessionManager.PrimaryCompany().COMPANY_NAME + ".</p>");
sb.AppendLine();
sb.AppendLine("<p><b>DETAILS</b></p>");
sb.AppendLine();
sb.AppendLine("<p>Date: " + rdpDueDate.SelectedDate.Value.ToShortDateString() + "</p>");
sb.AppendLine();
sb.AppendLine("<p>Instructions: " + tbInstructions.Text + "</p>");
sb.AppendLine();
sb.AppendLine("<p>Please go here to acknowledge receipt of this issue:<br/>");
sb.AppendLine("<a href=\"" + path + "\">" + path + "</a></p>");
sb.AppendLine(); // AW20140129
sb.AppendLine(); // AW20140129
sb.AppendLine("Please Do Not Reply To This Message"); // AW20140129
string emailBody = sb.ToString();
string emailAddress = emailPerson.EMAIL;
WebSiteCommon.SendEmail(emailAddress, emailSubject, emailBody, "");
}
Response.Redirect("EHS_Incidents.aspx");
}
static void ScheduleAllAudits()
{
List <SETTINGS> sets = SQMSettings.SelectSettingsGroup("AUTOMATE", ""); // ABW 20140805
int startRangeHours = 04;
int startRangeMinutes = 45;
int endRangeHours = 05;
int endRangeMinutes = 15;
try
{
startRange = sets.Find(x => x.SETTING_CD == "AuditScheduleStart").VALUE.ToString();
startRangeHours = Convert.ToInt16(startRange.Substring(0, 2));
startRangeMinutes = Convert.ToInt16(startRange.Substring(3, 2));
}
catch { }
try
{
endRange = sets.Find(x => x.SETTING_CD == "AuditScheduleEnd").VALUE.ToString();
endRangeHours = Convert.ToInt16(endRange.Substring(0, 2));
endRangeMinutes = Convert.ToInt16(endRange.Substring(3, 2));
}
catch { }
List <AUDIT_SCHEDULER> scheduler = EHSAuditMgr.SelectActiveAuditSchedulers(0, null); // currently, we will select all schedules for all plants
AUDIT audit = null;
List <EHSAuditQuestion> questions = null;
AUDIT_ANSWER answer = null;
decimal auditId = 0;
TimeSpan start = new TimeSpan(startRangeHours, startRangeMinutes, 0);
TimeSpan end = new TimeSpan(endRangeHours, endRangeMinutes, 0);
WriteLine("Audits will be created for locations with a local time of " + startRangeHours + ":" + startRangeMinutes + " through " + endRangeHours + ":" + endRangeMinutes);
foreach (AUDIT_SCHEDULER schedule in scheduler)
{
AUDIT_TYPE audittype = EHSAuditMgr.SelectAuditTypeById(entities, (decimal)schedule.AUDIT_TYPE_ID);
// check that the audit is still active
if (audittype != null)
{
if (!audittype.INACTIVE)
{
// ABW 1/5/16 - changing the scheduler from scheduling one week of audits to creating audits that are to be scheduled that day.
// All audits will be scheduled at 5am local plant time for the day.
//WriteLine("");
//WriteLine("The following " + type.TITLE + " assessments were created for Assessment Scheduler " + schedule.AUDIT_SCHEDULER_ID + ": ");
//// determine the date to schedule, by finding the next occurance of the selected day of the week after the current day
//DateTime auditDate = DateTime.Today;
//while ((int)auditDate.DayOfWeek != schedule.DAY_OF_WEEK)
//{
// auditDate = auditDate.AddDays(1);
//}
// get the plant
PLANT auditPlant = SQMModelMgr.LookupPlant((decimal)schedule.PLANT_ID);
// check the local plant time to see if it is almost 5am. If so, schedule the audit. If not, do nothing.
DateTime localTime = WebSiteCommon.LocalTime(DateTime.UtcNow, auditPlant.LOCAL_TIMEZONE);
if ((int)localTime.DayOfWeek == schedule.DAY_OF_WEEK && ((localTime.TimeOfDay > start) && (localTime.TimeOfDay < end)))
{
WriteLine("");
WriteLine("The following " + audittype.TITLE + " assessments were created for Assessment Scheduler " + schedule.AUDIT_SCHEDULER_ID + ": ");
// for the location, select all people that should get the audit
List <PERSON> auditors = SQMModelMgr.SelectPlantPrivgroupPersonList(auditPlant.PLANT_ID, new string[1] {
schedule.JOBCODE_CD
}, true);
foreach (PERSON person in auditors)
{
// check to see if there is already an audit for this plant/type/date/person
audit = EHSAuditMgr.SelectAuditForSchedule(auditPlant.PLANT_ID, audittype.AUDIT_TYPE_ID, person.PERSON_ID, localTime);
if (audit == null)
{
// create audit header
auditId = 0;
audit = new AUDIT()
{
DETECT_COMPANY_ID = Convert.ToDecimal(auditPlant.COMPANY_ID),
DETECT_BUS_ORG_ID = auditPlant.BUS_ORG_ID,
DETECT_PLANT_ID = auditPlant.PLANT_ID,
AUDIT_TYPE = "EHS",
CREATE_DT = localTime,
CREATE_BY = "Automated Scheduler",
DESCRIPTION = audittype.TITLE,
// CREATE_PERSON = SessionManager.UserContext.Person.PERSON_ID, // do we want to set this to admin?
AUDIT_DT = localTime,
AUDIT_TYPE_ID = audittype.AUDIT_TYPE_ID,
AUDIT_PERSON = person.PERSON_ID,
CURRENT_STATUS = "A",
PERCENT_COMPLETE = 0,
TOTAL_SCORE = 0
};
entities.AddToAUDIT(audit);
entities.SaveChanges();
auditId = audit.AUDIT_ID;
// create audit answer records
questions = EHSAuditMgr.SelectAuditQuestionListByType(audittype.AUDIT_TYPE_ID);
foreach (var q in questions)
{
answer = new AUDIT_ANSWER()
{
AUDIT_ID = auditId,
AUDIT_QUESTION_ID = q.QuestionId,
ANSWER_VALUE = "",
ORIGINAL_QUESTION_TEXT = q.QuestionText,
//COMMENT = q.AnswerComment
};
entities.AddToAUDIT_ANSWER(answer);
}
entities.SaveChanges();
// create task record for their calendar
EHSAuditMgr.CreateOrUpdateTask(auditId, person.PERSON_ID, 50, localTime.AddDays(audittype.DAYS_TO_COMPLETE), "A", 0);
// send an email
EHSNotificationMgr.NotifyOnAuditCreate(auditId, person.PERSON_ID);
System.Threading.Thread.Sleep(timer); //will wait for 2 seconds to allow Google Mail to process email requests
WriteLine(person.LAST_NAME + ", " + person.FIRST_NAME + " - assessment added");
}
else
{
// ABW 1/5/16 - Since this will be running once every hour now, we don't want to see this message
//WriteLine(person.LAST_NAME + ", " + person.FIRST_NAME + " - assessment already exists for this date");
}
}
}
else
{
// ABW 1/5/16 - Do we need to write any message out to explaing why the audit wasn't created? I don't think so
//WriteLine("Assessment Type " + schedule.AUDIT_TYPE_ID + " - assessment already exists for this date OR is not scheduled to be created");
}
}
else
{
WriteLine("Assessment Type " + schedule.AUDIT_TYPE_ID + " inactive. Assessments not created for Scheduler Record " + schedule.AUDIT_SCHEDULER_ID.ToString());
}
}
else
{
WriteLine("Assessment Type " + schedule.AUDIT_TYPE_ID + " not found. Assessments not created for Scheduler Record " + schedule.AUDIT_SCHEDULER_ID.ToString());
}
}
}
