private IList<Record> GetCostsByDate(DateTimeIntervals intervalType, CostBarInfo barInfo, int startIdx, int count)
{
IList<Record> records = new List<Record>();
int currentYear = DateTime.Now.Year;
var startDate = new DateTime(currentYear, 1, 1);
startDate = AddInterval(startDate, startIdx, intervalType);
var currentInterval = startDate;
using (var context = new SoheilEdmContext())
{
var costRepository = new Repository<Cost>(context);
var costCenterRepository = new Repository<CostCenter>(context);
var ssamRepository = new Repository<StateStationActivityMachine>(context);
var ssaRepository = new Repository<StateStationActivity>(context);
var processRepository = new Repository<Process>(context);
var processReportRepository = new Repository<ProcessReport>(context);
var taskReportRepository = new Repository<TaskReport>(context);
var processOperatortRepository = new Repository<ProcessOperator>(context);
var stateStationRepository = new Repository<StateStation>(context);
var stateRepository = new Repository<State>(context);
var fpcRepository = new Repository<FPC>(context);
var productRepository = new Repository<Product>(context);
var costList = costRepository.GetAll().ToList();
var costCenterList = costCenterRepository.GetAll().ToList();
var ssamList = ssamRepository.GetAll().ToList();
var processList = processRepository.GetAll().ToList();
var processReportList = processReportRepository.GetAll().ToList();
var taskReportList = taskReportRepository.GetAll().ToList();
var processOperatortList = processOperatortRepository.GetAll().ToList();
var ssaList = ssaRepository.GetAll().ToList();
var stateStationList = stateStationRepository.GetAll().ToList();
var stateList = stateRepository.GetAll().ToList();
var fpcList = fpcRepository.GetAll().ToList();
var productList = productRepository.GetAll().ToList();
// List of all products in range
var indexList = new List<KeyValuePair<int, int>>();
for (int i = startIdx; i < count; i++)
{
indexList.AddRange(from taskReport in taskReportList
where taskReport.ReportStartDateTime >= currentInterval
&& taskReport.ReportStartDateTime < AddInterval(currentInterval, 1, intervalType)
select new KeyValuePair<int, int>(i, taskReport.Id));
indexList.Add(new KeyValuePair<int, int>(i, -1));
currentInterval = AddInterval(currentInterval, 1, intervalType);
}
// Products duration
var productDurationQuery = from index in indexList
from taskReport in taskReportList.Where(tr => tr.Id == index.Value).DefaultIfEmpty()
from processReport in processReportList.Where(pr => pr.TaskReport != null && taskReport != null && pr.TaskReport.Id == taskReport.Id).DefaultIfEmpty()
from process in processList.Where(p => processReport != null && processReport.Process != null && p.Id == processReport.Process.Id).DefaultIfEmpty()
from processOpr in processOperatortList.Where(po => process != null && po.Process != null && po.Process.Id == process.Id).DefaultIfEmpty()
from ssActivity in ssaList.Where(ssa => process != null && process.StateStationActivity != null && ssa.Id == process.StateStationActivity.Id).DefaultIfEmpty()
from ssaMachine in ssamList.Where(ssam => ssActivity != null && ssam.StateStationActivity != null && ssActivity.Id == ssam.StateStationActivity.Id).DefaultIfEmpty()
from stateStation in stateStationList.Where(ss => ssActivity != null && ssActivity.StateStation != null && ss.Id == ssActivity.StateStation.Id).DefaultIfEmpty()
from state in stateList.Where(s => stateStation != null && stateStation.State != null && s.Id == stateStation.State.Id).DefaultIfEmpty()
from fpc in fpcList.Where(f => state != null && state.FPC != null && state.FPC.Id == f.Id).DefaultIfEmpty()
from product in productList.Where(p => fpc != null && fpc.Product != null && p.Id == fpc.Product.Id).DefaultIfEmpty()
let productId = product == null ? -1 : product.Id
let stationId = stateStation == null ? -1 : (stateStation.Station == null ? -1 : stateStation.Station.Id)
let machineId = ssaMachine == null ? -1 : (ssaMachine.Machine == null ? -1 : ssaMachine.Machine.Id)
let activityId = ssActivity == null ? -1 : (ssActivity.Activity == null ? -1 : ssActivity.Activity.Id)
let operatorId = processOpr == null ? -1 : (processOpr.Operator == null ? -1 : processOpr.Operator.Id)
let start = taskReport == null ? DateTime.MinValue : taskReport.ReportStartDateTime
let end = taskReport == null ? DateTime.MinValue : taskReport.ReportEndDateTime
let duration = taskReport == null ? 0 : taskReport.ReportDurationSeconds
select new { interval = index.Key, productId, stationId, machineId, activityId, operatorId, start, end, duration };
// Cost centers totals
var productDurationList = productDurationQuery.ToList();
var productCostByStationQuery = from product in productDurationList
from cost in costList.Where(c => c.Station != null && c.Station.Id == product.stationId
&& c.Date >= product.start && c.Date < product.end).DefaultIfEmpty()
let productId = product == null ? -1 : product.productId
let stationId = product == null ? -1 : product.stationId
let productDuration = product == null ? 0 : product.duration
let stationCost = cost == null ? 0 : cost.CostValue
select new { product.interval, productId, stationId, productDuration, stationCost };
var costByStationList = productCostByStationQuery.ToList();
var sTotalByProductQuery = from station in costByStationList
group station by station.stationId into g
select new { g.Key, stationTotal = g.Sum(item => item.stationCost) };
var stationCostByProductQuery = from product in costByStationList
from station in sTotalByProductQuery.Where(s => s.Key == product.stationId).DefaultIfEmpty()
let productId = product == null ? -1 : product.productId
let stationId = product == null ? -1 : product.stationId
let productDuration = product == null ? 0 : product.productDuration
let stationCost = station == null ? 0 : station.stationTotal
select new { product.interval, productId, stationId, productDuration, stationCost };
var productCostByMachineQuery = from product in productDurationList
from cost in costList.Where(c => c.Machine != null && c.Machine.Id == product.machineId
&& c.Date >= product.start && c.Date < product.end).DefaultIfEmpty()
let productId = product == null ? -1 : product.productId
let machineId = product == null ? -1 : product.machineId
let productDuration = product == null ? 0 : product.duration
let machineCost = cost == null ? 0 : cost.CostValue
select new { product.interval, productId, machineId, productDuration, machineCost };
var costByMachineList = productCostByMachineQuery.ToList();
var mTotalByProductQuery = from machine in costByMachineList
group machine by machine.machineId into g
select new { g.Key, machineTotal = g.Sum(item => item.machineCost) };
var machineCostByProductQuery = from product in costByMachineList
from machine in mTotalByProductQuery.Where(s => s.Key == product.machineId).DefaultIfEmpty()
let productId = product == null ? -1 : product.productId
let machineId = product == null ? -1 : product.machineId
let productDuration = product == null ? 0 : product.productDuration
let machineCost = machine == null ? 0 : machine.machineTotal
select new { product.interval, productId, machineId, productDuration, machineCost };
var productCostByActivityQuery = from product in productDurationList
from cost in costList.Where(c => c.Activity != null && c.Activity.Id == product.activityId
&& c.Date >= product.start && c.Date < product.end).DefaultIfEmpty()
let productId = product == null ? -1 : product.productId
let activityId = product == null ? -1 : product.activityId
let productDuration = product == null ? 0 : product.duration
let activityCost = cost == null ? 0 : cost.CostValue
select new { product.interval, productId, activityId, productDuration, activityCost };
var costByActivityList = productCostByActivityQuery.ToList();
var aTotalByProductQuery = from activity in costByActivityList
group activity by activity.activityId into g
select new { g.Key, activityTotal = g.Sum(item => item.activityCost) };
var activityCostByProductQuery = from product in costByActivityList
from activity in aTotalByProductQuery.Where(s => s.Key == product.activityId).DefaultIfEmpty()
let productId = product == null ? -1 : product.productId
let activityId = product == null ? -1 : product.activityId
let productDuration = product == null ? 0 : product.productDuration
let activityCost = activity == null ? 0 : activity.activityTotal
select new { product.interval, productId, activityId, productDuration, activityCost };
var productCostByOperatorQuery = from product in productDurationList
from cost in costList.Where(c => c.Operator != null && c.Operator.Id == product.operatorId
&& c.Date >= product.start && c.Date < product.end).DefaultIfEmpty()
let productId = product == null ? -1 : product.productId
let operatorId = product == null ? -1 : product.operatorId
let productDuration = product == null ? 0 : product.duration
let operatorCost = cost == null ? 0 : cost.CostValue
select new { product.interval, productId, operatorId, productDuration, operatorCost };
var costByOperatorList = productCostByOperatorQuery.ToList();
var oTotalByProductQuery = from opr in costByOperatorList
group opr by opr.operatorId into g
select new { g.Key, operatorTotal = g.Sum(item => item.operatorCost) };
var operatorCostByProductQuery = from product in costByOperatorList
from opr in oTotalByProductQuery.Where(s => s.Key == product.operatorId).DefaultIfEmpty()
let productId = product == null ? -1 : product.productId
let operatorId = product == null ? -1 : product.operatorId
let productDuration = product == null ? 0 : product.productDuration
let operatorCost = opr == null ? 0 : opr.operatorTotal
select new { product.interval, productId, operatorId, productDuration, operatorCost };
var productCostByMiscQuery = from product in productDurationList
from costCenter in costCenterList.Where(cc=> cc.SourceType == (decimal) CostSourceType.Other).DefaultIfEmpty()
from cost in costList.Where(c=> c.CostCenter.Id == costCenter.Id && c.CostCenter != null && costCenter != null
&& c.Date >= product.start && c.Date < product.end).DefaultIfEmpty()
let productId = product == null ? -1 : product.productId
let productDuration = product == null ? 0 : product.duration
let miscCost = cost == null ? 0 : cost.CostValue
select new { product.interval, productId, productDuration, miscCost };
// Cost centers durations
var stationCostByProductList = stationCostByProductQuery.ToList();
var stationDurationQuery = from product in stationCostByProductList
group product by new { product.interval, product.stationId } into g
let stationDuration = g.Sum(item => item.productDuration)
select new { g.Key.interval, g.Key.stationId, stationDuration };
var machineCostByProductList = machineCostByProductQuery.ToList();
var machineDurationQuery = from product in machineCostByProductList
group product by new { product.interval, product.machineId } into g
let machineDuration = g.Sum(item => item.productDuration)
select new { g.Key.interval, g.Key.machineId, machineDuration };
var activityCostByProductList = activityCostByProductQuery.ToList();
var activityDurationQuery = from product in activityCostByProductList
group product by new { product.interval, product.activityId } into g
let activityDuration = g.Sum(item => item.productDuration)
select new { g.Key.interval, g.Key.activityId, activityDuration };
var operatorCostByProductList = operatorCostByProductQuery.ToList();
var operatorDurationQuery = from product in operatorCostByProductList
group product by new { product.interval, product.operatorId } into g
let operatorDuration = g.Sum(item => item.productDuration)
select new { g.Key.interval, g.Key.operatorId, operatorDuration };
var productCostByMiscList = productCostByMiscQuery.ToList();
var productSumDurationQuery = from product in productCostByMiscList
group product by new { product.interval, product.productId } into g
let productDuration = g.Sum(item => item.productDuration)
select new { g.Key.interval, g.Key.productId, productDuration };
// Products costs
var productStationCostQuery = from station in stationDurationQuery
from product in stationCostByProductList.Where(p => station != null && p.stationId == station.stationId && p.interval == station.interval).DefaultIfEmpty()
let stationDuration = station == null ? 0 : station.stationDuration
let productDuration = product == null ? 0 : product.productDuration
let totalCost = product == null ? 0 : product.stationCost
let reletiveCost = stationDuration == 0 ? 0 : totalCost * (productDuration / stationDuration)
select new { product.interval, product.productId, reletiveCost };
var productMachineCostQuery = from machine in machineDurationQuery
from product in machineCostByProductList.Where(p => machine != null && p.machineId == machine.machineId && p.interval == machine.interval).DefaultIfEmpty()
let machineDuration = machine == null ? 0 : machine.machineDuration
let productDuration = product == null ? 0 : product.productDuration
let totalCost = product == null ? 0 : product.machineCost
let reletiveCost = machineDuration == 0 ? 0 : totalCost * (productDuration / machineDuration)
select new { product.interval, product.productId, reletiveCost };
var productActivityCostQuery = from activity in activityDurationQuery
from product in activityCostByProductList.Where(p => activity != null && p.activityId == activity.activityId && p.interval == activity.interval).DefaultIfEmpty()
let activityDuration = activity == null ? 0 : activity.activityDuration
let productDuration = product == null ? 0 : product.productDuration
let totalCost = product == null ? 0 : product.activityCost
let reletiveCost = activityDuration == 0 ? 0 : totalCost * (productDuration / activityDuration)
select new { product.interval, product.productId, reletiveCost };
var productOperatorCostQuery = from opr in operatorDurationQuery
from product in operatorCostByProductList.Where(p => opr != null && p.operatorId == opr.operatorId && p.interval == opr.interval).DefaultIfEmpty()
let operatorDuration = opr == null ? 0 : opr.operatorDuration
let productDuration = product == null ? 0 : product.productDuration
let totalCost = product == null ? 0 : product.operatorCost
let reletiveCost = operatorDuration == 0 ? 0 : totalCost * (productDuration / operatorDuration)
select new { product.interval, product.productId, reletiveCost };
var intervalSumDurationQuery = from product in productCostByMiscList
group product by product.interval into g
let intervalDuration = g.Sum(item => item.productDuration)
let intervalCost = g.Sum(item => item.miscCost)
select new { interval = g.Key, intervalDuration, intervalCost };
var productMiscCostQuery = from product in productSumDurationQuery
from interval in intervalSumDurationQuery.Where(i => i.interval == product.interval).DefaultIfEmpty()
let miscCost = interval.intervalDuration == 0 ? 0 : interval.intervalCost * (product.productDuration / interval.intervalDuration)
select new { interval.interval, product.productId, miscCost };
// Interval total cost
var intervalTotalCost = from index in indexList
from sCost in productStationCostQuery.Where(s => s.productId == barInfo.Id && s.interval == index.Key).DefaultIfEmpty()
from mCost in productMachineCostQuery.Where(m => m.productId == barInfo.Id && m.interval == index.Key).DefaultIfEmpty()
from aCost in productActivityCostQuery.Where(a => a.productId == barInfo.Id && a.interval == index.Key).DefaultIfEmpty()
from oCost in productOperatorCostQuery.Where(o => o.productId == barInfo.Id && o.interval == index.Key).DefaultIfEmpty()
from iCost in productMiscCostQuery.Where(i=> i.productId == barInfo.Id && i.interval == index.Key).DefaultIfEmpty()
let psCost = sCost == null ? 0 : sCost.reletiveCost
let pmCost = mCost == null ? 0 : sCost.reletiveCost
let paCost = aCost == null ? 0 : sCost.reletiveCost
let poCost = oCost == null ? 0 : sCost.reletiveCost
let inCost = iCost == null ? 0 : iCost.miscCost
select new { index.Key, psCost, pmCost, paCost, poCost, inCost };
var query = from interval in intervalTotalCost
group interval by interval.Key into g
let productCost = g.Sum(item => item == null ? 0 : item.psCost + item.pmCost + item.poCost + item.paCost + item.inCost)
select new { interval = g.Key, value = productCost??0 };
var results = query.ToList();
for (int i = startIdx; i < count; i++)
{
var newRecord = new Record { Header = i.ToString(CultureInfo.InvariantCulture) };
foreach (var line in results)
{
if (line.interval == i)
{
newRecord.Id = barInfo.Id;
newRecord.Value = line.value;
newRecord.StartDate = barInfo.StartDate;
newRecord.EndDate = barInfo.EndDate;
}
}
records.Add(newRecord);
}
}
return records;
}
private IList<Record> GetPerformanceByActivities(IndexBarInfo indexInfo, int startIndex, int count)
{
IList<Record> records = new List<Record>();
using (var context = new SoheilEdmContext())
{
var processRepository = new Repository<Process>(context);
var processReportRepository = new Repository<ProcessReport>(context);
var taskReportRepository = new Repository<TaskReport>(context);
var processOperatortRepository = new Repository<ProcessOperator>(context);
var operatorRepository = new Repository<Operator>(context);
var ssaRepositoryRepository = new Repository<StateStationActivity>(context);
var activityRepository = new Repository<Activity>(context);
var stateStationRepository = new Repository<StateStation>(context);
var stationRepository = new Repository<Station>(context);
var stateRepository = new Repository<State>(context);
var fpcRepository = new Repository<FPC>(context);
var productRepository = new Repository<Product>(context);
var processList = processRepository.GetAll();
var processReportList = processReportRepository.GetAll();
var taskReportList = taskReportRepository.GetAll();
var processOperatortList = processOperatortRepository.GetAll();
var operatorList = indexInfo.OperatorId > 0 ? operatorRepository.Find(item => item.Id == indexInfo.OperatorId) : operatorRepository.GetAll();
var ssaList = ssaRepositoryRepository.GetAll();
var activityList = activityRepository.GetAll();
var stateStationList = stateStationRepository.GetAll();
var stationList = indexInfo.StationId > 0 ? stationRepository.Find(item => item.Id == indexInfo.StationId) : stationRepository.GetAll();
var stateList = stateRepository.GetAll();
var fpcList = fpcRepository.GetAll();
var productList = indexInfo.ProductId > 0 ? productRepository.Find(item => item.Id == indexInfo.ProductId) : productRepository.GetAll();
var indexList = activityList.Skip(startIndex).Take(count).Select((p, index) => new { interval = index, p.Id, p.Code, p.Name });
var activityQuery = from process in processList
from processReport in processReportList.Where(ssapr => ssapr.Process != null && process != null && ssapr.Process.Id == process.Id).DefaultIfEmpty()
from taskReport in taskReportList.Where(tr => tr.ReportStartDateTime >= indexInfo.StartDate && tr.ReportEndDateTime < indexInfo.EndDate).DefaultIfEmpty()
from ssActivity in ssaList.Where(ssa => process != null && process.StateStationActivity != null && ssa.Id == process.StateStationActivity.Id).DefaultIfEmpty()
from pOpr in processOperatortList.Where(po => po.Process != null && process != null && po.Process.Id == process.Id).DefaultIfEmpty()
from opr in operatorList.Where(o => pOpr != null && pOpr.Operator != null && pOpr.Operator.Id == o.Id).DefaultIfEmpty()
from stateStation in stateStationList.Where(ss => ssActivity != null && ssActivity.StateStation != null && ss.Id == ssActivity.StateStation.Id).DefaultIfEmpty()
from station in stationList.Where(s => stateStation != null && stateStation.Station != null && s.Id == stateStation.Station.Id).DefaultIfEmpty()
from state in stateList.Where(s => stateStation != null && stateStation.State != null && s.Id == stateStation.State.Id).DefaultIfEmpty()
from fpc in fpcList.Where(f => state != null && state.FPC != null && f.Id == state.FPC.Id).DefaultIfEmpty()
from product in productList.Where(p => fpc != null && fpc.Product != null && p.Id == fpc.Product.Id).DefaultIfEmpty()
let producedG1 = processReport == null ? 0 : processReport.ProducedG1
let targetCount = process == null ? 0 : process.TargetCount
let aId = ssActivity == null ? -1 : (ssActivity.Activity == null ? -1 : ssActivity.Activity.Id)
let prId = processReport == null ? -1 : processReport.Id
let trId = taskReport == null ? -1 : taskReport.Id
select new { pId = aId, prId, trId, producedG1, targetCount };
var query = from index in indexList
from activity in activityQuery.Where(a => a.pId == index.Id).DefaultIfEmpty()
group activity by new { index.Id, index.interval, index.Code, index.Name } into g
let sumPg = g.Sum(item => item == null ? 0 : item.producedG1)
let sumTc = g.Sum(item => item == null ? 0 : item.targetCount)
select new { g.Key, value = sumTc == 0 ? 0 : sumPg / sumTc };
for (int i = startIndex; i < count; i++)
{
var newRecord = new Record { Header = i.ToString(CultureInfo.InvariantCulture) };
foreach (var line in query)
{
if (line.Key.interval + startIndex == i)
{
newRecord.Value = line.value;
newRecord.Value = 50; //???
newRecord.StartDate = indexInfo.StartDate;
newRecord.EndDate = indexInfo.EndDate;
newRecord.Header = line.Key.Name;
}
}
records.Add(newRecord);
}
}
return records;
}
private IList<Record> GetCostsByProducts(CostBarInfo indexInfo, int startIdx, int count)
{
IList<Record> records = new List<Record>();
using (var context = new SoheilEdmContext())
{
var costRepository = new Repository<Cost>(context);
var costCenterRepository = new Repository<CostCenter>(context);
var machineRepository = new Repository<Machine>(context);
var ssamRepository = new Repository<StateStationActivityMachine>(context);
var ssaRepository = new Repository<StateStationActivity>(context);
var processRepository = new Repository<Process>(context);
var processReportRepository = new Repository<ProcessReport>(context);
var taskReportRepository = new Repository<TaskReport>(context);
var processOperatortRepository = new Repository<ProcessOperator>(context);
var operatorRepository = new Repository<Operator>(context);
var activityRepository = new Repository<Activity>(context);
var stateStationRepository = new Repository<StateStation>(context);
var stationRepository = new Repository<Station>(context);
var stateRepository = new Repository<State>(context);
var fpcRepository = new Repository<FPC>(context);
var productRepository = new Repository<Product>(context);
var costList = costRepository.GetAll().ToList();
var costCenterList = costCenterRepository.GetAll().ToList();
var machineList = machineRepository.GetAll().ToList();
var ssamList = ssamRepository.GetAll().ToList();
var processList = processRepository.GetAll().ToList();
var processReportList = processReportRepository.GetAll().ToList();
var taskReportList = taskReportRepository.GetAll().ToList();
var processOperatortList = processOperatortRepository.GetAll().ToList();
var operatorList = operatorRepository.GetAll().ToList();
var ssaList = ssaRepository.GetAll().ToList();
var activityList = activityRepository.GetAll().ToList();
var stateStationList = stateStationRepository.GetAll().ToList();
var stationList = stationRepository.GetAll().ToList();
var stateList = stateRepository.GetAll().ToList();
var fpcList = fpcRepository.GetAll().ToList();
var productList = productRepository.GetAll().ToList();
// List of all products in range
var indexList = productList.Skip(startIdx).Take(count).Select((p, index) => new { interval = index, p.Code, p.Id, p.Name });
// Cost lists
var stationQuery = from station in stationList
from cost in costList.Where(c => c.Station != null && station != null && c.Station.Id == station.Id).DefaultIfEmpty()
select new { station.Id, cost.CostValue };
var machineQuery = from machine in machineList
from cost in costList.Where(c => c.Machine != null && machine != null && c.Machine.Id == machine.Id).DefaultIfEmpty()
select new { machine.Id, cost.CostValue };
var activityQuery = from activity in activityList
from cost in costList.Where(c => c.Activity != null && activity != null && c.Activity.Id == activity.Id).DefaultIfEmpty()
select new { activity.Id, cost.CostValue };
var operatorQuery = from opr in operatorList
from cost in costList.Where(c => c.Operator != null && opr != null && c.Operator.Id == opr.Id).DefaultIfEmpty()
select new { opr.Id, cost.CostValue };
var miscQuery = from costCenter in costCenterList.Where(cc=> cc.SourceType == (decimal) CostSourceType.Other)
from cost in costList.Where(c=> c.CostCenter != null && costCenter != null
&& c.CostCenter.Id == costCenter.Id).DefaultIfEmpty()
select new { cost.Id, cost.CostValue };
// Total cost lists
var stationCostQuery = from station in stationQuery
group station by station.Id into g
select new { sId = g.Key, totalCost = g.Sum(item => item.CostValue ?? 0) };
var machineCostQuery = from machine in machineQuery
group machine by machine.Id into g
select new { mId = g.Key, totalCost = g.Sum(item => item.CostValue ?? 0) };
var activityCostQuery = from activity in activityQuery
group activity by activity.Id into g
select new { aId = g.Key, totalCost = g.Sum(item => item.CostValue ?? 0) };
var operatorCostQuery = from opr in operatorQuery
group opr by opr.Id into g
select new { oId = g.Key, totalCost = g.Sum(item => item.CostValue ?? 0) };
// Products duration
var productDurationQuery = from taskReport in taskReportList
from processReport in processReportList.Where(pr => pr.TaskReport != null && taskReport != null && pr.TaskReport.Id == taskReport.Id).DefaultIfEmpty()
from process in processList.Where(p => processReport != null && processReport.Process != null && p.Id == processReport.Process.Id).DefaultIfEmpty()
from processOpr in processOperatortList.Where(po => process != null && po.Process != null && po.Process.Id == process.Id).DefaultIfEmpty()
from ssActivity in ssaList.Where(ssa => process != null && process.StateStationActivity != null && ssa.Id == process.StateStationActivity.Id).DefaultIfEmpty()
from ssaMachine in ssamList.Where(ssam => ssActivity != null && ssam.StateStationActivity != null && ssActivity.Id == ssam.StateStationActivity.Id).DefaultIfEmpty()
from stateStation in stateStationList.Where(ss => ssActivity != null && ssActivity.StateStation != null && ss.Id == ssActivity.StateStation.Id).DefaultIfEmpty()
from state in stateList.Where(s => stateStation != null && stateStation.State != null && s.Id == stateStation.State.Id).DefaultIfEmpty()
from fpc in fpcList.Where(f => state != null && state.FPC != null && state.FPC.Id == f.Id).DefaultIfEmpty()
from product in productList.Where(p => fpc != null && fpc.Product != null && p.Id == fpc.Product.Id).DefaultIfEmpty()
let productId = product == null ? -1 : product.Id
let stationId = stateStation == null ? -1 : (stateStation.Station == null ? -1 : stateStation.Station.Id)
let machineId = ssaMachine == null ? -1 : (ssaMachine.Machine == null ? -1 : ssaMachine.Machine.Id)
let activityId = ssActivity == null ? -1 : (ssActivity.Activity == null ? -1 : ssActivity.Activity.Id)
let operatorId = processOpr == null ? -1 : (processOpr.Operator == null ? -1 : processOpr.Operator.Id)
let start = taskReport == null ? DateTime.MinValue : taskReport.ReportStartDateTime
let end = taskReport == null ? DateTime.MinValue : taskReport.ReportEndDateTime
let duration = taskReport == null ? 0 : taskReport.ReportDurationSeconds
select new { productId, stationId, machineId, activityId, operatorId, start, end, duration };
// Cost centers totals
var stationCostByProductQuery = from product in productDurationQuery
from cost in stationCostQuery.Where(c => c.sId == product.stationId).DefaultIfEmpty()
let productId = product == null ? -1 : product.productId
let stationId = product == null ? -1 : product.stationId
let productDuration = product == null ? 0 : product.duration
let stationCost = cost == null ? 0 : cost.totalCost
select new { productId, stationId, productDuration, stationCost };
var machineCostByProductQuery = from product in productDurationQuery
from cost in machineCostQuery.Where(c => c.mId == product.machineId).DefaultIfEmpty()
let productId = product == null ? -1 : product.productId
let machineId = product == null ? -1 : product.machineId
let productDuration = product == null ? 0 : product.duration
let machineCost = cost == null ? 0 : cost.totalCost
select new { productId, machineId, productDuration, machineCost };
var activityCostByProductQuery = from product in productDurationQuery
from cost in activityCostQuery.Where(c => c.aId == product.activityId).DefaultIfEmpty()
let productId = product == null ? -1 : product.productId
let activityId = product == null ? -1 : product.activityId
let productDuration = product == null ? 0 : product.duration
let activityCost = cost == null ? 0 : cost.totalCost
select new { productId, activityId, productDuration, activityCost };
var operatorCostByProductQuery = from product in productDurationQuery
from cost in operatorCostQuery.Where(c => c.oId == product.operatorId).DefaultIfEmpty()
let productId = product == null ? -1 : product.productId
let operatorId = product == null ? -1 : product.operatorId
let productDuration = product == null ? 0 : product.duration
let operatorCost = cost == null ? 0 : cost.totalCost
select new { productId, operatorId, productDuration, operatorCost };
// Cost centers durations
var stationDurationQuery = from product in stationCostByProductQuery
group product by product.stationId into g
let stationDuration = g.Sum(item => item.productDuration)
select new { stationId = g.Key, stationDuration };
var machineDurationQuery = from product in machineCostByProductQuery
group product by product.machineId into g
let machineDuration = g.Sum(item => item.productDuration)
select new { machineId = g.Key, machineDuration };
var activityDurationQuery = from product in activityCostByProductQuery
group product by product.activityId into g
let activityDuration = g.Sum(item => item.productDuration)
select new { activityId = g.Key, activityDuration };
var operatorDurationQuery = from product in operatorCostByProductQuery
group product by product.operatorId into g
let operatorDuration = g.Sum(item => item.productDuration)
select new { operatorId = g.Key, operatorDuration };
var productSumDurationQuery = from product in productDurationQuery
group product by product.productId into g
select new { g.Key, productDuration = g.Sum(item=> item.duration) };
// Products costs
var productStationCostQuery = from station in stationDurationQuery
from product in stationCostByProductQuery.Where(p => station != null && p.stationId == station.stationId).DefaultIfEmpty()
let stationDuration = station == null ? 0 : station.stationDuration
let productDuration = product == null ? 0 : product.productDuration
let totalCost = product == null ? 0 : product.stationCost
let reletiveCost = stationDuration == 0 ? 0 : totalCost * (productDuration / stationDuration)
select new { product.productId, reletiveCost };
var productMachineCostQuery = from machine in machineDurationQuery
from product in machineCostByProductQuery.Where(p => machine != null && p.machineId == machine.machineId).DefaultIfEmpty()
let machineDuration = machine == null ? 0 : machine.machineDuration
let productDuration = product == null ? 0 : product.productDuration
let totalCost = product == null ? 0 : product.machineCost
let reletiveCost = machineDuration == 0 ? 0 : totalCost * (productDuration / machineDuration)
select new { product.productId, reletiveCost };
var productActivityCostQuery = from activity in activityDurationQuery
from product in activityCostByProductQuery.Where(p => activity != null && p.activityId == activity.activityId).DefaultIfEmpty()
let activityDuration = activity == null ? 0 : activity.activityDuration
let productDuration = product == null ? 0 : product.productDuration
let totalCost = product == null ? 0 : product.activityCost
let reletiveCost = activityDuration == 0 ? 0 : totalCost * (productDuration / activityDuration)
select new { product.productId, reletiveCost };
var productOperatorCostQuery = from opr in operatorDurationQuery
from product in operatorCostByProductQuery.Where(p => opr != null && p.operatorId == opr.operatorId).DefaultIfEmpty()
let operatorDuration = opr == null ? 0 : opr.operatorDuration
let productDuration = product == null ? 0 : product.productDuration
let totalCost = product == null ? 0 : product.operatorCost
let reletiveCost = operatorDuration == 0 ? 0 : totalCost * (productDuration / operatorDuration)
select new { product.productId, reletiveCost };
var totalDuration = productSumDurationQuery.Sum(item => item == null ? 0 : item.productDuration);
var avgMiscCost = miscQuery.Sum(item => item == null ? 0 : item.CostValue ?? 0);
var productMiscCostQuery = from product in productSumDurationQuery
let miscCost = totalDuration == 0 || product == null ? 0 : avgMiscCost * (product.productDuration / totalDuration)
select new { productId = product.Key, miscCost };
// Product total cost
var productTotalCost = from index in indexList
from sCost in productStationCostQuery.Where(s => s.productId == index.Id).DefaultIfEmpty()
from mCost in productMachineCostQuery.Where(m => m.productId == index.Id).DefaultIfEmpty()
from aCost in productActivityCostQuery.Where(a => a.productId == index.Id).DefaultIfEmpty()
from oCost in productOperatorCostQuery.Where(o => o.productId == index.Id).DefaultIfEmpty()
from cCost in productMiscCostQuery.Where(c => c.productId == index.Id).DefaultIfEmpty()
let psCost = sCost == null ? 0 : sCost.reletiveCost
let pmCost = mCost == null ? 0 : sCost.reletiveCost
let paCost = aCost == null ? 0 : sCost.reletiveCost
let poCost = oCost == null ? 0 : sCost.reletiveCost
let ccCost = cCost == null ? 0 : cCost.miscCost
select new { index.Id, index.Code, index.Name, index.interval, psCost, pmCost, paCost, poCost, ccCost};
var query = from product in productTotalCost
group product by new { product.interval, product.Id, product.Name } into g
let productCost = g.Sum(item => item == null ? 0 : item.psCost + item.pmCost + item.poCost + item.paCost + item.ccCost)
select new { g.Key.interval, g.Key.Id, g.Key.Name, value = productCost };
var results = query.ToList();
for (int i = startIdx; i < count; i++)
{
var newRecord = new Record { Header = i.ToString(CultureInfo.InvariantCulture) };
foreach (var line in results)
{
if (line.interval + startIdx == i)
{
newRecord.Id = line.Id;
newRecord.Value = line.value;
newRecord.StartDate = indexInfo.StartDate;
newRecord.EndDate = indexInfo.EndDate;
newRecord.Header = line.Name;
}
}
records.Add(newRecord);
}
}
return records;
}
private IList<Record> GetPPMByDateTime(DateTimeIntervals intervalType, int startIndex, int count)
{
IList<Record> records = new List<Record>();
int currentYear = DateTime.Now.Year;
var startDate = new DateTime(currentYear, 1, 1);
startDate = AddInterval(startDate, startIndex, intervalType);
var currentInterval = startDate;
using (var context = new SoheilEdmContext())
{
var ssaRepository = new Repository<StateStationActivity>(context);
var processRepository = new Repository<Process>(context);
var defectionReportRepository = new Repository<DefectionReport>(context);
var processReportRepository = new Repository<ProcessReport>(context);
var taskReportRepository = new Repository<TaskReport>(context);
var ssaList = ssaRepository.GetAll();
var processList = processRepository.GetAll();
var defectionReportList = defectionReportRepository.GetAll();
var processReportList = processReportRepository.GetAll();
var taskReportList = taskReportRepository.GetAll();
var indexList = new List<KeyValuePair<int, int>>();
for (int i = startIndex; i < count; i++)
{
indexList.AddRange(from taskReport in taskReportList
where taskReport.ReportStartDateTime >= currentInterval
&& taskReport.ReportStartDateTime < AddInterval(currentInterval, 1, intervalType)
select new KeyValuePair<int, int>(i, taskReport.Id));
indexList.Add(new KeyValuePair<int, int>(i, -1));
currentInterval = AddInterval(currentInterval, 1, intervalType);
}
var drQuery = from ssActivity in ssaList
from process in processList.Where(pr => pr.StateStationActivity != null && ssActivity != null && pr.StateStationActivity.Id == ssActivity.Id).DefaultIfEmpty()
from processReport in processReportList
from defectionReport in defectionReportList.Where(dr => dr.ProcessReport != null && processReport != null && dr.ProcessReport.Id == processReport.Id).DefaultIfEmpty()
let trId = processReport == null ? -1 : 0
let prId = processReport == null ? -1 : processReport.Id
let cycleTime = ssActivity == null ? 0 : ssActivity.CycleTime
let producedG1 = processReport == null ? 0 : processReport.ProducedG1
let lostCount = defectionReport == null ? 0 : defectionReport.LostCount
let lostTime = defectionReport == null ? 0 : defectionReport.LostTime
let lostTotal = cycleTime == 0? lostCount : lostCount + lostTime/cycleTime
select new { trId, prId, producedG1, lostTotal };
var query = from index in indexList
from pr in drQuery.Where(dr => dr.trId == index.Value).DefaultIfEmpty()
group pr by index.Key into g
let sumPg1 = g.Sum(item => item == null ? 0 : item.producedG1)
let sumLc = g.Sum(item => item == null ? 0 : item.lostTotal)
select new { interval = g.Key, value = sumLc + sumPg1 == 0 ? 0 : sumLc / (sumLc + sumPg1) };
for (int i = startIndex; i < count; i++)
{
var newRecord = new Record { Header = i.ToString(CultureInfo.InvariantCulture) };
foreach (var line in query)
{
if (line.interval == i)
{
newRecord.Value = line.value;
newRecord.Value = 50; //???
newRecord.StartDate = AddInterval(startDate, i - startIndex, intervalType);
newRecord.EndDate = AddInterval(startDate, i - startIndex + 1, intervalType);
}
}
records.Add(newRecord);
}
}
return records;
}
private IList<Record> GetPerformanceByDateTime(DateTimeIntervals intervalType, int startIndex, int count)
{
IList<Record> records = new List<Record>();
int currentYear = DateTime.Now.Year;
var startDate = new DateTime(currentYear, 1, 1);
startDate = AddInterval(startDate, startIndex, intervalType);
var currentInterval = startDate;
using (var context = new SoheilEdmContext())
{
var processRepository = new Repository<Process>(context);
var processReportRepository = new Repository<ProcessReport>(context);
var taskReportRepository = new Repository<TaskReport>(context);
var processList = processRepository.GetAll();
var processReportList = processReportRepository.GetAll();
var taskReportList = taskReportRepository.GetAll();
var indexList = new List<KeyValuePair<int, int>>();
for (int i = startIndex; i < count; i++)
{
indexList.AddRange(from taskReport in taskReportList
where taskReport.ReportStartDateTime >= currentInterval
&& taskReport.ReportStartDateTime < AddInterval(currentInterval, 1, intervalType)
select new KeyValuePair<int, int>(i, taskReport.Id));
indexList.Add(new KeyValuePair<int, int>(i, -1));
currentInterval = AddInterval(currentInterval, 1, intervalType);
}
var processReportQuery = from process in processList
from processReport in processReportList.Where(ssapr => ssapr.Process != null && process != null && ssapr.Process.Id == process.Id).DefaultIfEmpty()
let producedG1 = processReport == null ? 0 : processReport.ProducedG1
let targetCount = process == null ? 0 : process.TargetCount
let prId = processReport == null ? -1 : processReport.Id
let trId = processReport == null ? -1 : (0)
select new { prId, trId, producedG1, targetCount };
var query = from index in indexList
from processReport in processReportQuery.Where(pr => pr.trId == index.Value).DefaultIfEmpty()
group processReport by index.Key into g
let sumPg = g.Sum(item => item == null ? 0 : item.producedG1)
let sumTc = g.Sum(item => item == null ? 0 : item.targetCount)
select new { interval = g.Key, value = sumTc == 0 ? 0 : sumPg / sumTc };
for (int i = startIndex; i < count; i++)
{
var newRecord = new Record { Header = i.ToString(CultureInfo.InvariantCulture) };
foreach (var line in query)
{
if (line.interval == i)
{
newRecord.Value = line.value;
newRecord.Value = 50; //???
newRecord.StartDate = AddInterval(startDate, i - startIndex, intervalType);
newRecord.EndDate = AddInterval(startDate, i - startIndex + 1, intervalType);
}
}
records.Add(newRecord);
}
}
return records;
}
private IList<Record> GetOEEByMachines(IndexBarInfo indexInfo, int startIndex, int count)
{
IList<Record> records = new List<Record>();
using (var context = new SoheilEdmContext())
{
var machineRepository = new Repository<Machine>(context);
var selectedMachineRepository = new Repository<SelectedMachine>(context);
var ssamRepository = new Repository<StateStationActivityMachine>(context);
var ssaRepository = new Repository<StateStationActivity>(context);
var processRepository = new Repository<Process>(context);
var processReportRepository = new Repository<ProcessReport>(context);
var taskReportRepository = new Repository<TaskReport>(context);
var machineList = machineRepository.GetAll();
var selectedMachineList = selectedMachineRepository.GetAll();
var ssamList = ssamRepository.GetAll();
var ssaList = ssaRepository.GetAll();
var processList = processRepository.GetAll();
var processReportList = processReportRepository.GetAll();
var taskReportList = taskReportRepository.GetAll();
var indexList = machineList.Skip(startIndex).Take(count).Select((m, index) => new { interval = index, m.Id, m.Code, m.Name });
var pQuery = from ssamachine in ssamList
from selmachine in selectedMachineList.Where(sm => ssamachine != null && sm.StateStationActivityMachine != null && sm.StateStationActivityMachine.Id == ssamachine.Id).DefaultIfEmpty()
from process in processList.Where(p => selmachine != null && selmachine.Process != null && p.Id == selmachine.Process.Id).DefaultIfEmpty()
from ssActivity in ssaList.Where(ssa=> process!=null && process.StateStationActivity!=null && process.StateStationActivity.Id == ssa.Id).DefaultIfEmpty()
let mId = ssamachine == null ? -1 : (ssamachine.Machine == null? -1 : ssamachine.Machine.Id)
let pId = process == null ? -1 : process.Id
let cycleTime = ssActivity == null ? 0 : ssActivity.CycleTime
let targetCount = process == null ? 0 : process.TargetCount
select new { pId, mId, targetCount, cycleTime };
var prQuery = from process in pQuery
from processReport in processReportList.Where(pr => process != null && pr.Process != null && pr.Process.Id == process.pId).DefaultIfEmpty()
from taskReport in taskReportList.Where(tr=> tr.ReportStartDateTime >= indexInfo.StartDate && tr.ReportEndDateTime < indexInfo.EndDate).DefaultIfEmpty()
let mId = process == null ? -1 : process.mId
let producedG1 = processReport == null ? 0 : processReport.ProducedG1
let cycleTime = process == null ? 0 : process.cycleTime
let targetCount = process == null ? 0 : process.targetCount
select new { mId, fValue = cycleTime * producedG1, bValue = cycleTime * targetCount };
var query = from machine in indexList
from pr in prQuery.Where(p => p.mId == machine.Id).DefaultIfEmpty()
group pr by new { machine.interval, machine.Id, machine.Code, machine.Name } into g
let sumf = g.Sum(item => item == null ? 0 : item.fValue)
let sumb = g.Sum(item => item == null ? 0 : item.bValue)
select new {g.Key.interval, mId = g.Key.Id, mCode = g.Key.Code, mName = g.Key.Name, value = sumb == 0 ? 0 : sumf / sumb };
for (int i = startIndex; i < count; i++)
{
var newRecord = new Record();
foreach (var line in query)
{
if (line.interval + startIndex == i)
{
newRecord.Value = line.value;
newRecord.StartDate = indexInfo.StartDate;
newRecord.EndDate = indexInfo.EndDate;
newRecord.Header = line.mName;
}
}
records.Add(newRecord);
}
}
return records;
}
private IList<Record> GetCostsByDate(DateTimeIntervals intervalType, CostType type, int startIdx, int count)
{
IList<Record> records = new List<Record>();
int currentYear = DateTime.Now.Year;
var startDate = new DateTime(currentYear, 1, 1);
startDate = AddInterval(startDate, startIdx, intervalType);
var currentInterval = startDate;
using (var context = new SoheilEdmContext())
{
var costRepository = new Repository<Cost>(context);
var costList = costRepository.GetAll();
var indexList = new List<KeyValuePair<int, double>>();
for (int i = startIdx; i < count; i++)
{
indexList.AddRange(from cost in costList
where cost.Date >= currentInterval
&& cost.Date < AddInterval(currentInterval, 1, intervalType)
&& (type == CostType.All || cost.CostType == (decimal) type)
select new KeyValuePair<int, double>(i, cost.CostValue?? 0));
indexList.Add(new KeyValuePair<int, double>(i, 0));
currentInterval = AddInterval(currentInterval, 1, intervalType);
}
var query = from index in indexList
group index by index.Key into g
let total = g.Sum(item => item.Value)
select new { interval = g.Key, value = total};
for (int i = startIdx; i < count; i++)
{
var newRecord = new Record { Header = i.ToString(CultureInfo.InvariantCulture) };
foreach (var line in query)
{
if (line.interval == i)
{
newRecord.Id = line.interval;
newRecord.Value = line.value;
newRecord.StartDate = AddInterval(startDate, i - startIdx, intervalType);
newRecord.EndDate = AddInterval(startDate, i - startIdx + 1, intervalType);
}
}
records.Add(newRecord);
}
}
return records;
}
private IList<Record> GetCostsByType(CostBarInfo indexInfo, CostType type, int startIdx, int count)
{
IList<Record> records = new List<Record>();
using (var context = new SoheilEdmContext())
{
var costRepository = new Repository<Cost>(context);
var costList = costRepository.Find(c=> c.CostCenter != null && c.CostCenter.Id == indexInfo.Id);
var indexList = costList.Skip(startIdx).Take(count).Select((c, index) => new { interval = index, c.Id, c.CostType, c.Description, c.Date, c.CostValue });
var query = from index in indexList.Where(idx => idx.Date >= indexInfo.StartDate && idx.Date < indexInfo.EndDate
&& (type == CostType.All || idx.CostType == (decimal)type))
select new {index.interval, index.Id, index.CostValue, index.Description };
var results = query.ToList();
for (int i = startIdx; i < count; i++)
{
if (i - startIdx >= results.Count)
{
records.Add(new Record());
}
else
{
var newRecord = new Record
{
Id = results[i - startIdx].Id,
Header = results[i - startIdx].Description,
Value = results[i - startIdx].CostValue ?? 0,
StartDate = indexInfo.StartDate,
EndDate = indexInfo.EndDate
};
records.Add(newRecord);
}
}
//foreach (var result in results)
//{
// var newRecord = new Record
// {
// Id = result.Id,
// Header = result.Description,
// Value = result.CostValue ?? 0,
// StartDate = indexInfo.StartDate,
// EndDate = indexInfo.EndDate
// };
// records.Add(newRecord);
//}
}
return records;
}
private IList<Record> GetCostsByCostCenters(CostBarInfo indexInfo, CostType type, int startIdx, int count)
{
IList<Record> records = new List<Record>();
using (var context = new SoheilEdmContext())
{
var costRepository = new Repository<Cost>(context);
var costCenterRepository = new Repository<CostCenter>(context);
var costList = costRepository.GetAll();
var costCenterList = costCenterRepository.GetAll();
var indexList = costCenterList.Skip(startIdx).Take(count).Select((cc, index) => new { interval = index, cc.Id, cc.Name });
var ccQuery = from index in indexList
from cost in costList.Where(c => c.CostCenter != null && c.CostCenter.Id == index.Id
&& c.Date >= indexInfo.StartDate && c.Date < indexInfo.EndDate
&& (type == (decimal)CostType.All || c.CostType == (decimal)type)).DefaultIfEmpty()
select new {index.interval, index.Id, index.Name , Value = cost == null ? 0 : cost.CostValue?? 0 };
var query = from costCenter in ccQuery
group costCenter by new {costCenter.interval, costCenter.Id, costCenter.Name } into g
let total = g.Sum(item => item.Value)
select new {g.Key.interval, g.Key.Id, g.Key.Name, value = total };
for (int i = startIdx; i < count; i++)
{
var newRecord = new Record { Header = i.ToString(CultureInfo.InvariantCulture) };
foreach (var line in query)
{
if (line.interval + startIdx == i)
{
newRecord.Id = line.Id;
newRecord.Value = line.value;
newRecord.StartDate = indexInfo.StartDate;
newRecord.EndDate = indexInfo.EndDate;
newRecord.Header = line.Name;
}
}
records.Add(newRecord);
}
}
return records;
}
private IList<Record> GetOperatorsEfficiency(OperatorBarInfo oprInfo, int startIndex, int count)
{
IList<Record> records = new List<Record>();
using (var context = new SoheilEdmContext())
{
var operatorRepository = new Repository<Operator>(context);
var operatorProcessReportRepository = new Repository<OperatorProcessReport>(context);
var processReportRepository = new Repository<ProcessReport>(context);
var processRepository = new Repository<Process>(context);
var ssaRepository = new Repository<StateStationActivity>(context);
var srRepository = new Repository<StoppageReport>(context);
var osrRepository = new Repository<OperatorStoppageReport>(context);
var drRepository = new Repository<DefectionReport>(context);
var odrRepository = new Repository<OperatorDefectionReport>(context);
var operatorList = operatorRepository.Find(item=> item.Status != (decimal) Status.Deleted);
var oprList = operatorProcessReportRepository.GetAll();
var processReportList = processReportRepository.GetAll();
var processList = processRepository.GetAll();
var ssaList = ssaRepository.GetAll();
var srList = srRepository.GetAll();
var osrList = osrRepository.GetAll();
var drList = drRepository.GetAll();
var odrList = odrRepository.GetAll();
var indexList = operatorList.Skip(startIndex).Take(count).Select((o, index) => new { interval = index, o.Id, o.Code, o.Name });
var oprQuery = from opr in oprList
from processReport in processReportList.Where(pr=> opr.ProcessReport != null && opr.ProcessReport.Id == pr.Id && pr.StartDateTime >= oprInfo.StartDate && pr.StartDateTime < oprInfo.EndDate).DefaultIfEmpty()
from process in processList.Where(p=> processReport != null && processReport.Process != null && p.Id == processReport.Process.Id).DefaultIfEmpty()
from ssActivity in ssaList.Where(ssa => process!=null && process.StateStationActivity != null && ssa.Id == process.StateStationActivity.Id).DefaultIfEmpty()
let oprId = opr == null ? -1 : opr.Id
let prId = opr == null ? -1 : opr.ProcessReport == null ? -1 : opr.ProcessReport.Id
let oId = opr == null ? -1 : opr.ProcessOperator.Operator == null ? -1 : opr.ProcessOperator.Operator.Id
let ct = ssActivity == null ? 0 : ssActivity.CycleTime
let productionTime = opr == null || ssActivity == null ? 0 : opr.OperatorProducedG1 * ct
let productionCount = opr == null || ssActivity == null ? 0 : opr.OperatorProducedG1
select new { oprId, prId, oId, productionTime, productionCount, ct };
var srQuery = from sReport in srList
from osReport in osrList.Where(osr => sReport != null && osr.StoppageReport != null && osr.StoppageReport.Id == sReport.Id)
from pReport in processReportList.Where(pr => sReport != null && sReport.ProcessReport != null && sReport.ProcessReport.Id == pr.Id && pr.StartDateTime >= oprInfo.StartDate && pr.StartDateTime < oprInfo.EndDate)
from opReport in oprList.Where(opr => pReport != null && opr.ProcessReport != null && opr.ProcessOperator != null && opr.ProcessOperator.Operator != null
&& opr.ProcessReport.Id == pReport.Id && (osReport == null || osReport.Operator == null || opr.ProcessOperator.Operator.Id == osReport.Operator.Id))
let oId = osReport == null ? -1 : osReport.Operator == null ? -1 : osReport.Operator.Id
let prId = pReport == null ? -1 : pReport.Id
select new { sReport.Id, oId, prId, sReport.LostCount, sReport.LostTime };
var sQuery = from opr in oprQuery
from sReport in srQuery.Where(sr => sr.oId == opr.oId && sr.prId == opr.prId).DefaultIfEmpty()
let oprId = opr == null ? -1 : opr.oprId
let ct = opr == null ? 0 : opr.ct
let lostTime = sReport == null ? 0 : (sReport.LostCount * ct) + sReport.LostTime
let lostCount = sReport == null ? 0 : ct == 0 ? 0 : sReport.LostCount + (sReport.LostTime / ct)
let prId = opr == null ? -1 : opr.prId
let oId = opr == null ? -1 : opr.oId
let productionTime = opr == null ? 0 : opr.productionTime
let productionCount = opr == null ? 0 : opr.productionCount
select new {oprId, oId, prId, lostTime, lostCount, productionTime, productionCount, ct };
var sgQuery = from s in sQuery
group s by new {s.oId, s.oprId, s.prId, s.productionTime, s.productionCount, s.ct}
into g
let stoppageTime = g.Any() ? g.Sum(item => item.lostTime) : 0
let stoppageCount = g.Any() ? g.Sum(item => item.lostCount) : 0
select new {g.Key.oId, g.Key.oprId, g.Key.prId, g.Key.productionTime, g.Key.productionCount, g.Key.ct, stoppageTime, stoppageCount};
var drQuery = from dReport in drList
from odReport in odrList.Where(odr=> dReport != null && odr.DefectionReport != null && odr.DefectionReport.Id == dReport.Id)
from pReport in processReportList.Where(pr => dReport != null && dReport.ProcessReport != null && dReport.ProcessReport.Id == pr.Id && pr.StartDateTime >= oprInfo.StartDate && pr.StartDateTime < oprInfo.EndDate)
from opReport in oprList.Where(opr => pReport != null && opr.ProcessReport != null && opr.ProcessOperator != null && opr.ProcessOperator.Operator != null && odReport != null && odReport.Operator != null && opr.ProcessReport.Id == pReport.Id && opr.ProcessOperator.Operator.Id == odReport.Operator.Id)
let oId = odReport == null ? -1 : odReport.Operator == null ? -1 : odReport.Operator.Id
let prId = pReport == null ? -1 : pReport.Id
select new { dReport.Id, oId, prId, dReport.LostCount, dReport.LostTime };
var dQuery = from sg in sgQuery
from dReport in drQuery.Where(dr => sg.oId == dr.oId).DefaultIfEmpty()
let oprId = sg == null ? -1 : sg.oprId
let ct = sg == null ? 0 : sg.ct
let lostTime = dReport == null ? 0 : (dReport.LostCount * ct) + dReport.LostTime
let lostCount = dReport == null ? 0 : ct == 0 ? 0 : dReport.LostCount + (dReport.LostTime / ct)
let prId = sg == null ? -1 : sg.prId
let oId = sg == null ? -1 : sg.oId
let productionTime = sg == null? 0 : sg.productionTime
let stoppageTime = sg == null ? 0 : sg.stoppageTime
let productionCount = sg == null ? 0 : sg.productionCount
let stoppageCount = sg == null ? 0 : sg.stoppageCount
select new { oId, oprId, prId, lostTime, lostCount, stoppageTime, productionTime, stoppageCount, productionCount, ct };
var dgQuery = from d in dQuery
group d by new { d.oId, d.oprId, d.prId, d.productionTime, d.stoppageTime, d.productionCount, d.stoppageCount, d.ct }
into g
let defectionTime = g.Any() ? g.Sum(item => item.lostTime) : 0
let defectionCount = g.Any() ? g.Sum(item => item.lostCount) : 0
select new { g.Key.oId, g.Key.oprId, g.Key.prId, g.Key.productionTime, g.Key.ct, g.Key.stoppageTime, defectionTime, g.Key.productionCount, g.Key.stoppageCount, defectionCount };
var prQuery = from opr in dgQuery
from processReport in processReportList.Where(pr => opr.prId == pr.Id && pr.StartDateTime >= oprInfo.StartDate && pr.StartDateTime < oprInfo.EndDate).DefaultIfEmpty()
group processReport by new { opr.oId, opr.oprId, operatorId = opr.oId, opr.productionTime, opr.stoppageTime, opr.defectionTime, opr.productionCount, opr.stoppageCount, opr.defectionCount } into g
let duration = g.Sum(item => item == null ? 0 : item.OperatorProcessReports.Count == 0 ? 0 : item.DurationSeconds / item.OperatorProcessReports.Count)
let target = g.Sum(item => item == null ? 0 : item.OperatorProcessReports.Count == 0 ? 0 : item.ProcessReportTargetPoint / item.OperatorProcessReports.Count)
select new { g.Key.oprId, g.Key.operatorId, g.Key.productionTime, g.Key.stoppageTime, g.Key.defectionTime, duration, g.Key.productionCount, g.Key.stoppageCount, g.Key.defectionCount, target };
var query = from oprt in indexList
from pr in prQuery.Where(p => p.operatorId == oprt.Id).DefaultIfEmpty()
group pr by new {oprt.interval, oprt.Id, oprt.Code, oprt.Name} into g
let targetTime = g.Any() ? g.Sum(item => item == null ? 0 : item.duration) : 0
let productionTime = g.Any() ? g.Sum(item => item == null ? 0 : item.productionTime) : 0
let stoppageTime = g.Any() ? g.Max(item => item == null ? 0 : item.stoppageTime) : 0
let defectionTime = g.Any() ? g.Max(item => item == null ? 0 : item.defectionTime) : 0
let targetCount = g.Any() ? g.Sum(item => item == null ? 0 : item.target) : 0
let productionCount = g.Any() ? g.Sum(item => item == null ? 0 : item.productionCount) : 0
let stoppageCount = g.Any() ? g.Max(item => item == null ? 0 : item.stoppageCount) : 0
let defectionCount = g.Any() ? g.Max(item => item == null ? 0 : item.defectionCount) : 0
select new { g.Key.interval, g.Key.Id, g.Key.Code, g.Key.Name, stoppageTime, defectionTime, productionTime, targetTime, stoppageCount, defectionCount, productionCount, targetCount };
var sortedQuery = query.OrderBy(item => item.targetTime == 0 ? item.productionTime : item.productionTime / item.targetTime).ToList();
for (int i = startIndex; i < count; i++)
{
var newRecord = new Record();
newRecord.Data = new List<object>(8) {0,0,0,0,0,0,0,0};
foreach (var line in sortedQuery)
{
if (line.interval + startIndex == i)
{
newRecord.Id = line.Id;
newRecord.Data = new List<object>
{
line.targetTime,
line.productionTime,
line.defectionTime,
line.stoppageTime,
line.targetCount,
line.productionCount,
line.defectionCount,
line.stoppageCount
};
newRecord.StartDate = oprInfo.StartDate;
newRecord.EndDate = oprInfo.EndDate;
newRecord.Header = line.Name;
}
}
records.Add(newRecord);
}
}
return records;
}
