public static Extend Get(Guid MetricID, DateTime NormalizedDate, Guid OrgLocationID) { LinqMicajahDataContext dc = new LinqMicajahDataContext(); List <Micajah.Common.Bll.MeasureUnit> OrgUoMs = Mc_UnitsOfMeasure.GetOrganizationUoMs(); var MetricValue = from m in dc.Metric join f in dc.Frequency on m.FrequencyID equals f.FrequencyID join t in dc.MetricDataType on m.MetricDataTypeID equals t.MetricDataTypeID join _mv in dc.MetricValue on new { m.MetricID, LinqMicajahDataContext.InstanceId, m.FrequencyID, Date = NormalizedDate, Status = (bool?)true, OrgLocationID } equals new { _mv.MetricID, _mv.InstanceId, _mv.FrequencyID, _mv.Date, _mv.Status, _mv.OrgLocationID } into __mv join _c in dc.ViewnameMetricCategory on new { InstanceId = (Guid?)m.InstanceId, m.MetricCategoryID } equals new { _c.InstanceId, _c.MetricCategoryID } into __c from mv in __mv.DefaultIfEmpty() join _mvf in dc.Frequency on mv.FrequencyID equals _mvf.FrequencyID into __mvf join _mvt in dc.MetricDataType on mv.MetricDataTypeID equals _mvt.MetricDataTypeID into __mvt join _OrgLocName in dc.ViewnameOrgLocation on new { InstanceId = (Guid?)LinqMicajahDataContext.InstanceId, OrgLocationID = (Guid?)OrgLocationID } equals new { _OrgLocName.InstanceId, _OrgLocName.OrgLocationID } into __OrgLocName from OrgLocName in __OrgLocName.DefaultIfEmpty() from mvf in __mvf.DefaultIfEmpty() from mvt in __mvt.DefaultIfEmpty() from c in __c.DefaultIfEmpty() join _mnuom in dc.MetricOrgLocationUoM on new { m.InstanceId, m.MetricID, OrgLocationID } equals new { _mnuom.InstanceId, _mnuom.MetricID, _mnuom.OrgLocationID } into __mnuom from mnuom in __mnuom.DefaultIfEmpty() join _mnname in dc.MetricOrgLocationName on new { m.InstanceId, m.MetricID, OrgLocationID } equals new { _mnname.InstanceId, _mnname.MetricID, _mnname.OrgLocationID } into __mnname from mnname in __mnname.DefaultIfEmpty() where m.MetricID == MetricID && m.InstanceId == LinqMicajahDataContext.InstanceId && m.Status == true select new MetricValue.Extend { // Metric solid fields InstanceId = m.InstanceId, MetricID = m.MetricID, // Value fields MetricValueID = mv.MetricValueID == null ? Guid.Empty : mv.MetricValueID, OrgLocationID = mv.OrgLocationID == null ? OrgLocationID : mv.OrgLocationID, InputUserId = mv.InputUserId, ApproveUserId = mv.ApproveUserId, FrequencyID = mv.FrequencyID == null ? m.FrequencyID : mv.FrequencyID, Date = mv.Date == null ? FailureDate : mv.Date, MetricDataTypeID = mv.MetricDataTypeID == null ? m.MetricDataTypeID : mv.MetricDataTypeID, Value = mv.Value, ConvertedValue = mv.ConvertedValue, Notes = mv.Notes, Verified = mv.Verified == null ? false : mv.Verified, Approved = mv.MetricValueID == null ? false : mv.Approved, FilesAttached = mv.FilesAttached == null ? false : mv.FilesAttached, ReviewUpdated = mv.ReviewUpdated == null ? false : mv.ReviewUpdated, InputUnitOfMeasureID = mv.InputUnitOfMeasureID, UnitOfMeasureID = mv.UnitOfMeasureID, // Value Reference fields ValueFrequencyName = mvf.Name, ValueDataTypeName = mvt.Name, ApprovalStatus = mv.MetricValueID == null ? "Pending" : (mv.Approved == null ? "Under Review" : ((bool)mv.Approved ? "Approved" : "Pending")), Period = Frequency.GetPeriodName(NormalizedDate, m.FrequencyID), ValueInputUnitOfMeasureName = Metric.GetMeasureUnitName(OrgUoMs, mv.InputUnitOfMeasureID), ValueUnitOfMeasureName = Metric.GetMeasureUnitName(OrgUoMs, mv.UnitOfMeasureID), RelatedOrgLocationUoMRecordID = mnuom.MetricOrgLocationUoMID, OrgLocationUnitOfMeasureID = mnuom.InputUnitOfMeasureID, OrgLocationUnitOfMeasureName = Metric.GetMeasureUnitName(OrgUoMs, mnuom.InputUnitOfMeasureID), OrgLocationFullName = OrgLocationID == Guid.Empty ? LinqMicajahDataContext.OrganizationName : OrgLocName.FullName, RelatedOrgLocationNameRecordID = mnname.MetricOrgLocationNameID, MetricOrgLocationAlias = mnname.Alias, MetricOrgLocationCode = mnname.Code, // Metric fields MetricName = m.Name, MetricFrequencyID = m.FrequencyID, ActualMetricDataTypeID = m.MetricDataTypeID, MetricCategoryID = m.MetricCategoryID, MetricInputUnitOfMeasureID = m.InputUnitOfMeasureID, MetricUnitOfMeasureID = m.UnitOfMeasureID, NODecPlaces = m.NODecPlaces, NOMinValue = m.NOMinValue, NOMaxValue = m.NOMaxValue, FormulaCode = m.FormulaCode, Variable = m.Variable, Documentation = m.Documentation, Description = m.Notes, Definition = m.Definition, References = m.MetricReferences, AllowMetricCustomNames = m.AllowCustomNames, // Metric reference fields MetricFrequencyName = f.Name, MetricCategoryName = c.FullName, MetricDataTypeName = t.Name, MetricInputUnitOfMeasureName = Metric.GetMeasureUnitName(OrgUoMs, m.InputUnitOfMeasureID), MetricUnitOfMeasureName = Metric.GetMeasureUnitName(OrgUoMs, m.UnitOfMeasureID), IsCalculated = m.MetricTypeID == 2 }; return(MetricValue.FirstOrNull()); }
public static List <EntityValue> WorkList(int ValueCount, PageEntityID EntityPageInfo, Guid? @ApproverUserId, bool OrderByMetric) { LinqMicajahDataContext dc = new LinqMicajahDataContext(); Guid?EntityID = null; int SkipCount = 0; if (EntityPageInfo != null) { EntityID = EntityPageInfo.EntityID; SkipCount = EntityPageInfo.PageNumber * ValueCount; } ISingleResult <Sp_SelectApproverWorkListResult> V = dc.Sp_SelectApproverWorkList(LinqMicajahDataContext.InstanceId, ValueCount, SkipCount, EntityID, OrderByMetric, @ApproverUserId); List <EntityValue> MetricMetricValues = new List <EntityValue>(); EntityValue LastMetricMetricValue = null; List <Micajah.Common.Bll.MeasureUnit> OrgUoMs = Mc_UnitsOfMeasure.GetOrganizationUoMs(); int i = 0; foreach (var v in V) { if (LastMetricMetricValue == null || (OrderByMetric && v.MetricID != LastMetricMetricValue.EntityID) || (!OrderByMetric && v.OrgLocationID != LastMetricMetricValue.EntityID)) { i = 0; LastMetricMetricValue = new EntityValue(); // copy metric data LastMetricMetricValue.FrequencyID = OrderByMetric ? v.MetricFrequencyID : null; LastMetricMetricValue.PageCount = (SkipCount / ValueCount) + 1; LastMetricMetricValue.EntityID = OrderByMetric ? (Guid)v.MetricID : (Guid)v.OrgLocationID; LastMetricMetricValue.EntityName = OrderByMetric ? v.MetricName : v.OrgLocationFullName; LastMetricMetricValue.IsMoreValues = false; LastMetricMetricValue.MetricTypeID = (int)v.MetricTypeID; MetricMetricValues.Add(LastMetricMetricValue); } if (i == ValueCount) { LastMetricMetricValue.IsMoreValues = true; continue; } MetricValue.Extend LastMetricValue = new MetricValue.Extend(); // metric data LastMetricValue.InstanceId = (Guid)v.InstanceId; LastMetricValue.MetricID = (Guid)v.MetricID; LastMetricValue.NODecPlaces = v.NODecPlaces; LastMetricValue.MetricName = v.MetricName; LastMetricValue.MetricCategoryName = v.MetricCategoryFullName; // value data LastMetricValue.MetricValueID = (Guid)v.MetricValueID; LastMetricValue.FrequencyID = (int)v.ValueFrequencyID; LastMetricValue.ValueFrequencyName = v.ValueFrequencyName; LastMetricValue.Date = (DateTime)v.Date; LastMetricValue.Period = Frequency.GetPeriodName((DateTime)v.Date, LastMetricValue.FrequencyID, true); LastMetricValue.Value = v.Value; LastMetricValue.Approved = v.Approved; LastMetricValue.ReviewUpdated = (bool)v.ReviewUpdated; LastMetricValue.MissedCalc = (bool)v.MissedCalc; string title = String.Empty; if (v.MetricValueID == null || v.MetricValueID == Guid.Empty || String.IsNullOrEmpty(v.Value) /* || v.Status == false*/) //!!! add after fix stored procedure { LastMetricValue.ApprovalStatus = "Missing Value"; } else { if (v.MetricTypeID != 1) { title = "Calc value"; if (v.MissedCalc == true) { title += " | Some input values missed"; } } else { title = "Input value"; } LastMetricValue.ApprovalStatus = (v.Approved == null ? (v.ReviewUpdated == true ? "Under Review | Updated by Collector" : "Under Review") : ((bool)v.Approved ? "Approved" : "Pending")); } title = String.IsNullOrEmpty(title) ? String.Empty : "(" + title + ") "; LastMetricValue.Notes = title + v.Notes; LastMetricValue.FilesAttached = (bool)v.FilesAttached; LastMetricValue.ReviewUpdated = (bool)v.ReviewUpdated; LastMetricValue.OrgLocationID = (Guid)v.OrgLocationID; LastMetricValue.MetricDataTypeID = (int)v.ValueDataTypeID; LastMetricValue.InputUnitOfMeasureID = v.ValueInputUnitOfMeasureID; LastMetricValue.ValueInputUnitOfMeasureName = GetMeasureUnitAbbvr(OrgUoMs, LastMetricValue.InputUnitOfMeasureID); LastMetricValue.OrgLocationFullName = v.OrgLocationID == Guid.Empty ? LinqMicajahDataContext.OrganizationName : v.OrgLocationFullName; LastMetricValue.RelatedOrgLocationUoMRecordID = v.MetricOrgLocationUoMID; LastMetricValue.OrgLocationUnitOfMeasureID = v.OrgLocationUnitOfMeasureID; LastMetricValue.IsCalculated = v.MetricTypeID != 1; LastMetricMetricValue.EntityValues.Add(LastMetricValue); i++; } return(MetricMetricValues); }
public static MetricOrgValue List(int ValueCount, DateTime BaseDate, Guid ScoreCardMetricID, MetricTrac.Bll.ScoreCardMetric.CalcStringFormula Calculator) { LinqMicajahDataContext dc = new LinqMicajahDataContext(); var r = from cm in dc.ScoreCardMetric join _m in dc.Metric on new { LinqMicajahDataContext.InstanceId, cm.MetricID, Status = (bool?)true } equals new { _m.InstanceId, MetricID = (Guid?)_m.MetricID, _m.Status } into __m join o in dc.ViewnameOrgLocation on new { InstanceId = (Guid?)cm.InstanceId, cm.OrgLocationID } equals new { o.InstanceId, o.OrgLocationID } join _u in dc.MetricOrgLocationUoM on new { LinqMicajahDataContext.InstanceId, cm.MetricID, cm.OrgLocationID } equals new { _u.InstanceId, MetricID = (Guid?)_u.MetricID, OrgLocationID = (Guid?)_u.OrgLocationID } into __u join _pi in dc.PerformanceIndicator on new { LinqMicajahDataContext.InstanceId, cm.PerformanceIndicatorId, Status = (bool?)true } equals new { _pi.InstanceId, PerformanceIndicatorId = (Guid?)_pi.PerformanceIndicatorID, _pi.Status } into __pi from m in __m.DefaultIfEmpty() from u in __u.DefaultIfEmpty() from pi in __pi.DefaultIfEmpty() where cm.InstanceId == LinqMicajahDataContext.InstanceId && cm.ScoreCardMetricID == ScoreCardMetricID && cm.Status == true select new MetricOrgValue { Name = cm.MetricID == null ? pi.Name : m.Name, OrgLocationFullName = o.FullName, MetricID = cm.MetricID == null ? Guid.Empty : (Guid)cm.MetricID, OrgLocationID = cm.OrgLocationID == null ? Guid.Empty : (Guid)cm.OrgLocationID, //o.EntityNodeId, !!!! TODO fix this FrequencyID = m.MetricID == null ? 3 : m.FrequencyID, InputUnitOfMeasureID = u.MetricOrgLocationUoMID == null ? m.InputUnitOfMeasureID : u.InputUnitOfMeasureID, //u.InputUnitOfMeasureID == null ? m.InputUnitOfMeasureID : u.InputUnitOfMeasureID, UnitOfMeasureID = cm.MetricID == null ? pi.UnitOfMeasureID : m.UnitOfMeasureID, NODecPlaces = m.NODecPlaces, PerformanceIndicatorID = cm.PerformanceIndicatorId == null || pi.PerformanceIndicatorID == Guid.Empty ? null : (Guid?)pi.PerformanceIndicatorID }; var mo = r.FirstOrNull(); if (mo == null) { return(null); } DateTime EndDate = Frequency.GetNormalizedDate(mo.FrequencyID, BaseDate); ScoreCardMetric.Extend e = new ScoreCardMetric.Extend() { InstanceId = LinqMicajahDataContext.InstanceId, ScoreCardMetricID = ScoreCardMetricID, MetricID = mo.MetricID, OrgLocationID = mo.OrgLocationID, ScoreCardPeriodID = 1, MetricFrequencyID = mo.FrequencyID, PerformanceIndicatorId = mo.PerformanceIndicatorID, UomID = mo.UnitOfMeasureID }; mo.MetricValues = new List <Extend>(); for (int i = ValueCount - 1; i >= 0; i--) { DateTime dtBegin = Frequency.AddPeriod(EndDate, mo.FrequencyID, -i); DateTime dtEnd = Frequency.AddPeriod(dtBegin, mo.FrequencyID, 1); if (e.MetricID != null && e.MetricID != Guid.Empty) { e.CurrentValue = ScoreCardMetric.CalculateTotalValue(dc, LinqMicajahDataContext.OrganizationId, LinqMicajahDataContext.InstanceId, (Guid)e.MetricID, e.OrgLocationID, e.UomID, dtBegin, dtEnd, true, ScoreCardMetric.enTotalValueType.Sum, false); } else if (e.PerformanceIndicatorId != null) { var f = PerformanceIndicator.GetFormulasWithRealValues(dc, dtBegin, dtEnd, (new Guid[] { e.OrgLocationID == null ? Guid.Empty : (Guid)e.OrgLocationID }).ToList(), (Guid)e.PerformanceIndicatorId, e.UomID == null?Guid.Empty:(Guid)e.UomID); double v = Calculator(f); if (v != 0) { e.CurrentValue = v; } //e.PerformanceIndicatorId; } Extend mv = new Extend() { InstanceId = mo.InstanceId, MetricID = mo.MetricID, FrequencyID = mo.FrequencyID, Date = dtBegin, UnitOfMeasureID = e.UomID, InputUnitOfMeasureID = e.UomID, MetricDataTypeID = 1, DValue = e.CurrentValue, OrgLocationID = mo.OrgLocationID, Period = Frequency.GetPeriodName(dtBegin, mo.FrequencyID, true) }; mo.MetricValues.Add(mv); mo.UnitOfMeasureID = e.UomID; } return(mo); }
public static List <DistinctMetricOrgValue> AlertQueueList(LinqMicajahDataContext dc, DateTime NormalizedDate, int FrequencyID, Guid?MetricID, Guid?OrgLocationID, Guid?SelUserId, Guid? @ApproverUserId, bool ViewMode, bool OrderByMetric) { DateTime EndDate = Frequency.AddPeriod(NormalizedDate, FrequencyID, 1); ISingleResult <Sp_SelectUnderReviewMetricValuesResult> V = dc.Sp_SelectUnderReviewMetricValues(LinqMicajahDataContext.InstanceId, EndDate, ViewMode, FrequencyID, MetricID, OrgLocationID, SelUserId, @ApproverUserId, OrderByMetric); List <DistinctMetricOrgValue> MetricMetricValues = new List <DistinctMetricOrgValue>(); DistinctMetricOrgValue LastMetricMetricValue = null; List <Micajah.Common.Bll.MeasureUnit> OrgUoMs = Mc_UnitsOfMeasure.GetOrganizationUoMs(); int i = 0; int j = 0; DateTime NextDate = DateTime.MinValue; foreach (var v in V) { if (LastMetricMetricValue == null || v.MetricID != LastMetricMetricValue.MetricID || v.OrgLocationID != LastMetricMetricValue.OrgLocationID) { i = 0; j = 0; LastMetricMetricValue = new DistinctMetricOrgValue(); // copy metric data LastMetricMetricValue.InstanceId = (Guid)v.InstanceId; LastMetricMetricValue.MetricID = (Guid)v.MetricID; LastMetricMetricValue.Name = v.MetricName; LastMetricMetricValue.FrequencyID = (int)v.MetricFrequencyID; LastMetricMetricValue.MetricTypeID = (int)v.MetricTypeID; LastMetricMetricValue.MetricDataTypeID = (int)v.MetricDataTypeID; LastMetricMetricValue.NODecPlaces = v.NODecPlaces; LastMetricMetricValue.InputUnitOfMeasureID = v.MetricInputUnitOfMeasureID; LastMetricMetricValue.InputUnitOfMeasureName = GetMeasureUnitAbbvr(OrgUoMs, v.MetricInputUnitOfMeasureID); LastMetricMetricValue.OrgLocationID = (Guid)v.OrgLocationID; LastMetricMetricValue.OrgLocationFullName = v.OrgLocationID == Guid.Empty ? LinqMicajahDataContext.OrganizationName : v.OrgLocationFullName; LastMetricMetricValue.RelatedOrgLocationUoMRecordID = v.MetricOrgLocationUoMID; LastMetricMetricValue.OrgLocationUnitOfMeasureID = v.OrgLocationUnitOfMeasureID; LastMetricMetricValue.OrgLocationUnitOfMeasureName = GetMeasureUnitAbbvr(OrgUoMs, v.OrgLocationUnitOfMeasureID); LastMetricMetricValue.AllowCustomNames = (bool)v.AllowCustomNames; // find actual input measure unit for metric-org location pair LastMetricMetricValue.InputUnitOfMeasureName = (LastMetricMetricValue.RelatedOrgLocationUoMRecordID == null) ? LastMetricMetricValue.InputUnitOfMeasureName : LastMetricMetricValue.OrgLocationUnitOfMeasureName; LastMetricMetricValue.RelatedOrgLocationNameRecordID = v.MetricOrgLocationNameID; LastMetricMetricValue.MetricOrgLocationAlias = v.CustomMetricAlias; LastMetricMetricValue.MetricOrgLocationCode = v.CustomMetricCode; LastMetricMetricValue.IsPreviousValues = false; LastMetricMetricValue.IsNextValues = false; LastMetricMetricValue.PreviousDate = DateTime.MinValue; LastMetricMetricValue.NextDate = DateTime.MinValue; LastMetricMetricValue.DatesHeader = new List <DateHeader>(); MetricMetricValues.Add(LastMetricMetricValue); } if (v.ValuePosType == "A") // check for existed left values { j++; if (j == 2) { LastMetricMetricValue.NextDate = (DateTime)v.Date; } continue; } if (LastMetricMetricValue.MetricValues.Count == 6) // check for existed right values { LastMetricMetricValue.IsPreviousValues = true; continue; } if (j == 2) { LastMetricMetricValue.IsNextValues = true; } MetricValue.Extend LastMetricValue = new MetricValue.Extend(); // metric data LastMetricValue.InstanceId = (Guid)v.InstanceId; LastMetricValue.MetricID = (Guid)v.MetricID; LastMetricValue.NODecPlaces = v.NODecPlaces; // value data LastMetricValue.MetricValueID = (Guid)v.MetricValueID; LastMetricValue.FrequencyID = (int)v.MetricFrequencyID; LastMetricValue.Date = (DateTime)v.Date; LastMetricValue.Value = v.Value; LastMetricValue.Approved = v.Approved; LastMetricValue.FilesAttached = (bool)v.FilesAttached; LastMetricValue.ReviewUpdated = (bool)v.ReviewUpdated; LastMetricValue.OrgLocationID = (Guid)v.OrgLocationID; LastMetricValue.MetricDataTypeID = (int)v.ValueDataTypeID; LastMetricValue.InputUnitOfMeasureID = v.ValueInputUnitOfMeasureID; LastMetricValue.OrgLocationFullName = v.OrgLocationID == Guid.Empty ? LinqMicajahDataContext.OrganizationName : v.OrgLocationFullName; LastMetricValue.RelatedOrgLocationUoMRecordID = v.MetricOrgLocationUoMID; LastMetricValue.OrgLocationUnitOfMeasureID = v.OrgLocationUnitOfMeasureID; LastMetricValue.IsCalculated = v.MetricTypeID != 1; DateHeader h = new DateHeader(); h.Date = (DateTime)v.Date; h.sDate = Frequency.GetPeriodName((DateTime)v.Date, FrequencyID, true); LastMetricMetricValue.DatesHeader.Add(h); if (i == 1) { LastMetricMetricValue.PreviousDate = LastMetricValue.Date; } LastMetricMetricValue.MetricValues.Add(LastMetricValue); i++; } foreach (DistinctMetricOrgValue m in MetricMetricValues) { if (m.MetricValues.Count < 6) { int count = 6 - m.MetricValues.Count; for (int k = 1; k <= count; k++) { MetricValue.Extend AnotherMetricValue = new MetricValue.Extend(); // metric data AnotherMetricValue.InstanceId = LastMetricMetricValue.InstanceId; AnotherMetricValue.MetricID = LastMetricMetricValue.MetricID; AnotherMetricValue.NODecPlaces = LastMetricMetricValue.NODecPlaces; // value data AnotherMetricValue.MetricValueID = Guid.Empty; AnotherMetricValue.FrequencyID = FrequencyID; AnotherMetricValue.Date = DateTime.MinValue; AnotherMetricValue.Value = null; AnotherMetricValue.Approved = false; AnotherMetricValue.FilesAttached = false; AnotherMetricValue.ReviewUpdated = false; AnotherMetricValue.OrgLocationID = LastMetricMetricValue.OrgLocationID; AnotherMetricValue.MetricDataTypeID = LastMetricMetricValue.MetricDataTypeID; AnotherMetricValue.InputUnitOfMeasureID = LastMetricMetricValue.InputUnitOfMeasureID; AnotherMetricValue.OrgLocationFullName = LastMetricMetricValue.OrgLocationFullName; AnotherMetricValue.RelatedOrgLocationUoMRecordID = LastMetricMetricValue.RelatedOrgLocationUoMRecordID; AnotherMetricValue.OrgLocationUnitOfMeasureID = LastMetricMetricValue.OrgLocationUnitOfMeasureID; AnotherMetricValue.IsCalculated = LastMetricMetricValue.MetricTypeID != 1; DateHeader ah = new DateHeader(); ah.Date = DateTime.MinValue; ah.sDate = " "; m.DatesHeader.Add(ah); m.MetricValues.Add(AnotherMetricValue); } } } return(MetricMetricValues); }