public System.Data.Objects.ObjectResult <StatusReportBySI_Result> GetStatusReportBySI(int?hubID) { /* * * select * c.CommodityID, * pc.Value as ProjectCode, * c.Name as CommodityName, * uncomm.SINumber, * isnull(uncomm.UncommitedStock,0) - (ISNULL(commitedToOthers.CommitedBalance, 0) + ISNULL(commitedToFDP.CommitedBalance,0)) UncommitedStock, * isnull(commitedToFDP.CommitedBalance,0) + isnull(commitedToOthers.CommitedBalance,0) CommitedBalance, * isnull(uncomm.UncommitedStock,0) as TotalStockOnHand, * receiveAllocated.AllocatedBalance AllocatedToHub, * received.ReceivedBalance, * (case when receiveAllocated.AllocatedBalance = 0 then 1 else received.ReceivedBalance / receiveAllocated.AllocatedBalance end) * 100 as PercentageReceived * from * * ( * * * * left join * ( * select raBeforeAggregation.CommodityID, raBeforeAggregation.ShippingInstructionID, sum(raBeforeAggregation.AllocatedBalance) as AllocatedBalance * from * (select * case when (select top 1 c.ParentID from Commodity c where CommodityID = t.CommodityID) is null then t.CommodityID else (select top 1 c.ParentID from Commodity c where c.CommodityID = t.CommodityID) end as CommodityID , * si.ShippingInstructionID , * t.QuantityInMT AllocatedBalance * * from [ReceiptAllocation] t join ShippingInstruction si on t.SINumber = si.Value * where t.QuantityInMT > 0 and t.HubID = @Hub * ) raBeforeAggregation * group by raBeforeAggregation.ShippingInstructionID, raBeforeAggregation.CommodityID * ) receiveAllocated * on received.CommodityID = receiveAllocated.CommodityID and received.ShippingInstructionID = receiveAllocated.ShippingInstructionID * * * join Commodity c on uncomm.CommodityID = c.CommodityID * join ProjectCode pc on received.ProjectCodeID = pc.ProjectCodeID * * END * */ #region Com //( from transaction in _context.Transactions where (transaction.LedgerID==2 && transaction.QuantityInMT > 0 && transaction.HubID==hubID ) // group transaction by new {transaction.ShippingInstructionID,CommodityID=transaction.ParentCommodityID,transaction.ProjectCodeID} into received // select new { received.Key.CommodityID,received.Key.ShippingInstructionID ,received.Key.ProjectCodeID, ReceivedBalance=received.Sum(x=>x.QuantityInMT) } ) // join t1 in // (from transaction1 in _context.Transactions join shippingInstruction in _context.ShippingInstructions on transaction1.ShippingInstructionID equals shippingInstruction.ShippingInstructionID // where transaction1.LedgerID==2 && transaction1.HubID==hubID group transaction1 by new {transaction1.ShippingInstructionID,transaction1.ParentCommodityID,shippingInstruction.Value ,transaction1.ProjectCodeID} into uncomm // select new {SINumber=uncomm.Key.Value,uncomm.Key.ShippingInstructionID,uncomm.Key.ProjectCodeID ,CommodityID=uncomm.Key.ParentCommodityID,UncommitedStock=uncomm.Sum(x=>x.QuantityInMT) }).ToList() // on new {received.Key.CommodityID,received.Key.ShippingInstructionID,received.Key.ProjectCodeID }equals new{t1.CommodityID,t1.ShippingInstructionID,t1.ProjectCodeID} // join t2 in // from dispatchAllocation in _context.DispatchAllocations where dispatchAllocation.HubID==hubID && dispatchAllocation.ShippingInstructionID !=null // group dispatchAllocation by new {dispatchAllocation.ShippingInstructionID,dispatchAllocation.CommodityID,dispatchAllocation.ProjectCodeID} into commitedtoFDP // select new //{ // commitedtoFDP.Key.CommodityID, // commitedtoFDP.Key.ProjectCodeID, // commitedtoFDP.Key.ShippingInstructionID, // CommitedBalance = commitedtoFDP.Sum(x => x.Amount/10) //} // on new {received.Key.CommodityID,received.Key.ShippingInstructionID,received.Key.ProjectCodeID} equals new {t2.CommodityID,t2.ShippingInstructionID,t2.ProjectCodeID} // ) //join t3 in //(from otherDispatchAllocation in _context.OtherDispatchAllocations where otherDispatchAllocation.HubID==hubID // group otherDispatchAllocation by new {otherDispatchAllocation.ShippingInstructionID,otherDispatchAllocation.CommodityID,otherDispatchAllocation.ProjectCodeID} into commitedToOthers // select new {commitedToOthers.Key.CommodityID,commitedToOthers.Key.ProjectCodeID,commitedToOthers.Key.ShippingInstructionID,CommitedBalance=commitedToOthers.Sum(x=>x.QuantityInMT)}).ToList() //on new {} #endregion //(from y in (from x in (from transaction in _context.Transactions where (transaction.LedgerID==2 && transaction.QuantityInMT > 0 && transaction.HubID==hubID ) // group transaction by new {transaction.ShippingInstructionID,CommodityID=transaction.ParentCommodityID,transaction.ProjectCodeID} into received select new {received.Key.CommodityID,received.Key.ShippingInstructionID,received.Key.ProjectCodeID,RecivedBalance=received.Sum(x=>x.QuantityInMT)}) // join t1 in // ( from transaction1 in _context.Transactions join shippingInstruction in _context.ShippingInstructions on transaction1.ShippingInstructionID equals shippingInstruction.ShippingInstructionID // where transaction1.LedgerID==2 && transaction1.HubID==hubID group transaction1 by new {transaction1.ShippingInstructionID,transaction1.ParentCommodityID,shippingInstruction.Value ,transaction1.ProjectCodeID} into uncomm select new {SINumber=uncomm.Key.Value,uncomm.Key.ShippingInstructionID,uncomm.Key.ProjectCodeID,CommodityID=uncomm.Key.ParentCommodityID,UncommitedStock=uncomm.Sum(x=>x.QuantityInMT)} ) // on new {x.CommodityID,x.ShippingInstructionID,x.ProjectCodeID} equals new {t1.CommodityID,t1.ShippingInstructionID,t1.ProjectCodeID} // join t2 in // (from dispatchAllocation in _context.DispatchAllocations where dispatchAllocation.HubID==hubID && dispatchAllocation.ShippingInstructionID !=null // group dispatchAllocation by new {dispatchAllocation.ShippingInstructionID,dispatchAllocation.CommodityID,dispatchAllocation.ProjectCodeID} into commitedtoFDP select new {commitedtoFDP.Key.CommodityID,commitedtoFDP.Key.ProjectCodeID,commitedtoFDP.Key.ShippingInstructionID,CommitedBalance=commitedtoFDP.Sum(x=>x.Amount/10)}) // on new {x.CommodityID,x.ShippingInstructionID,x.ProjectCodeID} equals new {t2.CommodityID,t2.ShippingInstructionID,t2.ProjectCodeID} // join t3 in // (from otherDispatchAllocation in _context.OtherDispatchAllocations where otherDispatchAllocation.HubID==hubID // group otherDispatchAllocation by new {otherDispatchAllocation.ShippingInstructionID,otherDispatchAllocation.CommodityID,otherDispatchAllocation.ProjectCodeID} into commitedToOthers select new{commitedToOthers.Key.CommodityID,commitedToOthers.Key.ProjectCodeID,commitedToOthers.Key.ShippingInstructionID,CommitedBalance=commitedToOthers.Sum(x=>x.QuantityInMT)}) // on new {} // from receiptAllocation in _context.ReceiptAllocations // join shippingInstruction in _context.ShippingInstructions // on receiptAllocation.SINumber equals shippingInstruction.Value // where receiptAllocation.QuantityInMT > 0 && receiptAllocation.HubID==hubID // group receiptAllocation by new {receiptAllocation.ShippingInstructionID,receiptAllocation.CommodityID} into receiveAllocated // on new {received.Key.CommodityID,received.Key.ShippingInstructionID} equals new {recivedAllocated.key.CommodityID,recivedAllocated.Key.ShippingInstructionID} // join commodity in _context.Commodity on uncomm.CommodityI equals commodity.CommodityID // join projectCode in _contex.ProjectCode on received.ProjectCodeID equals projectcode.ProjectCodeID // // select // //c.CommodityID, // //pc.Value as ProjectCode, // //c.Name as CommodityName, // //uncomm.SINumber, // //isnull(uncomm.UncommitedStock,0) - (ISNULL(commitedToOthers.CommitedBalance, 0) + ISNULL(commitedToFDP.CommitedBalance,0)) UncommitedStock, // //isnull(commitedToFDP.CommitedBalance,0) + isnull(commitedToOthers.CommitedBalance,0) CommitedBalance, // //isnull(uncomm.UncommitedStock,0) as TotalStockOnHand, // //receiveAllocated.AllocatedBalance AllocatedToHub, // //received.ReceivedBalance, // //(case when receiveAllocated.AllocatedBalance = 0 then 1 else received.ReceivedBalance / receiveAllocated.AllocatedBalance end) * 100 as PercentageReceived // ) return(_context.GetStatusReportBySI(hubID)); }