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); }
static public List <Extend> List(DateTime?Date, Bll.ScoreCardMetric.CalcStringFormula csf) { using (LinqMicajahDataContext dc = new LinqMicajahDataContext()) { var r = from d in dc.ScoreCardDashboard join cm in dc.ScoreCardMetric on new { d.InstanceId, d.ScoreCardMetricID, Status = (bool?)true } equals new { cm.InstanceId, cm.ScoreCardMetricID, cm.Status } join p in dc.ScoreCardPeriod on d.ScoreCardPeriodID equals p.ScoreCardPeriodID join o in dc.ViewnameOrgLocation on new { InstanceId = (Guid?)cm.InstanceId, cm.OrgLocationID } equals new { o.InstanceId, o.OrgLocationID } 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 _i in dc.PerformanceIndicator on new { LinqMicajahDataContext.InstanceId, cm.PerformanceIndicatorId, Status = (bool?)true } equals new { _i.InstanceId, PerformanceIndicatorId = (Guid?)_i.PerformanceIndicatorID, _i.Status } into __i //join _m in dc.Metric on new { LinqMicajahDataContext.InstanceId, cm.MetricID, Status = (bool?)true } equals new { _m.InstanceId, _m.MetricID, _m.Status } into __m //join _v in dc.ScoreCardValue on new { LinqMicajahDataContext.InstanceId, cm.ScoreCardMetricID, Status = (bool?)true } equals new { _v.InstanceId, _v.ScoreCardMetricID, _v.Status } into __v from m in __m.DefaultIfEmpty() from i in __i.DefaultIfEmpty() //from v in __v.DefaultIfEmpty() where d.UserId == LinqMicajahDataContext.LogedUserId && d.InstanceId == LinqMicajahDataContext.InstanceId && d.Status == true select new Extend { InstanceId = d.InstanceId, ScoreCardDashboardID = d.ScoreCardDashboardID, ScoreCardMetricID = d.ScoreCardMetricID, MetricID = cm.MetricID, PerformanceIndicatorId = cm.PerformanceIndicatorId, UomID = cm.MetricID == null ? (i.UnitOfMeasureID) : (m.UnitOfMeasureID == null ? m.InputUnitOfMeasureID : m.UnitOfMeasureID), AltUomID = cm.MetricID == null ? i.AltUnitOfMeasureID : null, OrgLocationID = cm.OrgLocationID, ScoreCardPeriodID = d.ScoreCardPeriodID == null ? 1 : (int)d.ScoreCardPeriodID, Status = d.Status, Created = d.Created, Updated = d.Updated, MinValue = d.MinValue, MaxValue = d.MaxValue, BaselineValue = d.BaselineValue, Breakpoint1Value = d.Breakpoint1Value, Breakpoint2Value = d.Breakpoint2Value, BaselineValueLabel = d.BaselineValueLabel, Breakpoint1ValueLabel = d.Breakpoint1ValueLabel, Breakpoint2ValueLabel = d.Breakpoint2ValueLabel, MetricName = m.MetricID != null ? m.Name : "PI: " + i.Name, MetricFrequencyID = m.FrequencyID == null ? 0 : m.FrequencyID, MetricNODecPlaces = cm.MetricID == null ? (int)i.DecimalPlaces :m.NODecPlaces, GrowUpIsGood = m.GrowUpIsGood, //CurrentValue = v.CurrentValue, //PreviousValue = v.PreviousValue, ScoreCardPeriodName = p.Name, OrgLocationName = o.FullName }; DateTime BaseDate = Date == null ? DateTime.Today : (DateTime)Date; var l = r.ToList(); foreach (var v in l) { if (v.ScoreCardPeriodID == null) { continue; } ScoreCardMetric.PeriodDate p1; ScoreCardMetric.PeriodDate p2; ScoreCardMetric.GetPeriodDate((int)v.ScoreCardPeriodID, v.MetricFrequencyID, BaseDate, out p1, out p2); if (v.MetricID != null) { v.CurrentValue = ScoreCardMetric.CalculateTotalValue(dc, LinqMicajahDataContext.OrganizationId, LinqMicajahDataContext.InstanceId, (Guid)v.MetricID, v.OrgLocationID, v.UomID, p1.Begin, p1.End, true, ScoreCardMetric.enTotalValueType.Sum, false); if (p2 != null) { v.PreviousValue = ScoreCardMetric.CalculateTotalValue(dc, LinqMicajahDataContext.OrganizationId, LinqMicajahDataContext.InstanceId, (Guid)v.MetricID, v.OrgLocationID, v.UomID, p2.Begin, p2.End, true, ScoreCardMetric.enTotalValueType.Sum, false); } } else { if (csf != null) { List <Guid> ogl = new List <Guid>(); ogl.Add(v.OrgLocationID == null ? Guid.Empty : (Guid)v.OrgLocationID); List <string> ResultFormula = Bll.PerformanceIndicator.GetFormulasWithRealValues(dc, p1.Begin, p1.End, ogl, (Guid)v.PerformanceIndicatorId, v.UomID == null ? Guid.Empty : (Guid)v.UomID); v.CurrentValue = csf(ResultFormula); if (v.CurrentValue == 0) { v.CurrentValue = null; } if (p2 != null) { ResultFormula = Bll.PerformanceIndicator.GetFormulasWithRealValues(dc, p2.Begin, p2.End, ogl, (Guid)v.PerformanceIndicatorId, v.UomID == null ? Guid.Empty : (Guid)v.UomID); v.PreviousValue = csf(ResultFormula); if (v.PreviousValue == null) { v.PreviousValue = null; } } } } ScoreCardMetric.Fill(v); } return(l); } }