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));
}
