public List <IngredientsUsageModels> GetListIngredientUsage(IngredientsUsageRequestViewModels model)
{
var lstResult = new List <IngredientsUsageModels>();
if (string.IsNullOrEmpty(model.StoreId))
{
return(lstResult);
}
using (NuWebContext cxt = new NuWebContext())
{
try
{
lstResult = (from h in cxt.I_StockCount
join d in cxt.I_StockCountDetail on h.Id equals d.StockCountId
from i in cxt.I_Ingredient.Where(ww => ww.Id == d.IngredientId).DefaultIfEmpty()
where h.StoreId == model.StoreId && DbFunctions.TruncateTime(h.StockCountDate) >= DbFunctions.TruncateTime(model.ApplyFrom) &&
DbFunctions.TruncateTime(h.StockCountDate) <= DbFunctions.TruncateTime(model.ApplyTo)
//&& i.Id == "d470f8bd-dd22-437e-9705-3d916999050e" && h.BusinessId == "b0379b38-169d-4508-bd08-ce1dd7f950a7"
&& h.Status == (int)Commons.EStockCountStatus.Approved
select new IngredientsUsageModels()
{
Id = d.Id,
Date = h.StockCountDate,
StoreId = h.StoreId,
IngredientId = d.IngredientId,
IngredientCode = i.Code,
IngredientName = i.Name,
OpenBal = d.OpenBal.HasValue ? d.OpenBal.Value : 0,
CloseBal = d.CloseBal,
BusinessId = h.BusinessId,
StartedOn = h.StartedOn,
ClosedOn = h.ClosedOn,
AutoCloseBal = d.AutoCloseBal
}).Distinct().ToList();
//Check Stock In
//var lstIngredientId = lstResult.Select(ss => ss.IngredientId).Distinct().ToList();
//if (lstIngredientId != null && lstIngredientId.Any())
//{
var lstBusinessIds = lstResult.Select(ss => ss.BusinessId).Distinct().ToList();
var lstStockIn = GetStockInOut(model.StoreId, lstBusinessIds);
//lstStockIn = lstStockIn.Where(ww => ww.IngredientId == "38107504-78c4-46a9-831e-5688832c591e").ToList();
foreach (var item in lstResult)
{
var lstObj = lstStockIn.Where(ww => ww.IngredientId == item.IngredientId && ww.BusinessId == item.BusinessId &&
ww.StoreId == item.StoreId).ToList();
if (lstObj != null && lstObj.Any())
{
item.StockIn = lstObj.Sum(ss => ss.StockIn);
item.StockOut = lstObj.Sum(ss => ss.StockOut);
}
if (item.StartedOn.HasValue)
{
item.BusinessDayDisplay = item.StartedOn.Value.ToString("MM/dd/yyyy HH:mm") + " - ";
if (item.ClosedOn.HasValue && item.ClosedOn.Value != Commons.MinDate)
{
item.BusinessDayDisplay += (item.ClosedOn.HasValue ? item.ClosedOn.Value.ToString("MM/dd/yyyy HH:mm") : "");
}
}
}
//}
//Check usage
var lstUsage = (from d in cxt.I_UsageManagementDetail
from h in cxt.I_UsageManagement.Where(ww => ww.Id == d.UsageManagementId).DefaultIfEmpty()
join i in cxt.I_Ingredient on d.IngredientId equals i.Id
where h.StoreId == model.StoreId
// && DbFunctions.TruncateTime(h.DateFrom) >= DbFunctions.TruncateTime(model.ApplyFrom)
//&& DbFunctions.TruncateTime(h.DateFrom) <= DbFunctions.TruncateTime(model.ApplyTo)
&& lstBusinessIds.Contains(h.BusinessId)
select new UsageManagementDetailModel()
{
ItemId = d.IngredientId,
Usage = d.Usage,
ApplyDate = h.DateFrom,
BusinessId = h.BusinessId,
IsSeftMade = i.IsSelfMode,
StockAble = i.StockAble.HasValue? i.StockAble.Value: false
}).ToList();
//if (lstUsage != null && lstUsage.Any())
//{
// foreach (var item in lstResult)
// {
// var obj = lstUsage.Where(ww => ww.ItemId == item.IngredientId
// && item.Date.Date == ww.ApplyDate.Date).FirstOrDefault();
// item.Sales = obj != null ? obj.Usage : 0;
// }
//}
if (lstUsage == null)
{
lstUsage = new List <UsageManagementDetailModel>();
}
var lstTmpId = lstUsage.Where(ww => ww.IsSeftMade && !ww.StockAble).Select(ss => ss.ItemId).ToList();
if (lstTmpId != null && lstTmpId.Any())
{
//check ingredient dependent
RecipeFactory _recipeFactory = new RecipeFactory();
var _objIngredientDependent = _recipeFactory.GetRecipesByIngredientSeftMade(lstTmpId);
var lstIngredientSMNotStockAble = lstUsage.Where(ww => ww.IsSeftMade && !ww.StockAble).ToList();
var businessIds = lstIngredientSMNotStockAble.Select(ss => ss.BusinessId).Distinct().ToList();
foreach (var item in businessIds)
{
var tmp = lstIngredientSMNotStockAble.Where(ww => ww.BusinessId == item).ToList();
foreach (var subitem in tmp)
{
var obj = _objIngredientDependent.ListChilds.Where(ww => ww.MixtureIngredientId == subitem.ItemId).ToList();
foreach (var item1 in obj)
{
lstUsage.Add(new UsageManagementDetailModel()
{
ItemId = item1.Id,
Usage = (item1.BaseUsage * subitem.Usage),
BusinessId = item
});
}
//obj.ForEach(ss => ss.BaseUsage = ss.BaseUsage * subitem.Usage);
}
//var lstGroup = _objIngredientDependent.ListChilds.GroupBy(gg => gg.Id);
//foreach (var item1 in lstGroup)
//{
// lstUsage.Add(new UsageManagementDetailModel()
// {
// ItemId = item1.Key,
// Usage = item1.Sum(ss => ss.BaseUsage),
// BusinessId = item
// });
//}
}
lstUsage = lstUsage.Where(ww => !lstTmpId.Contains(ww.ItemId)).ToList();
}
//Check allocation
var lstAllocation = (from d in cxt.I_AllocationDetail
from h in cxt.I_Allocation.Where(ww => ww.Id == d.AllocationId).DefaultIfEmpty()
where h.StoreId == model.StoreId &&
lstBusinessIds.Contains(h.BusinessId)
// && DbFunctions.TruncateTime(h.ApplyDate) >= DbFunctions.TruncateTime(model.ApplyFrom)
//&& DbFunctions.TruncateTime(h.ApplyDate) <= DbFunctions.TruncateTime(model.ApplyTo)
select new IngredientsUsageModels()
{
IngredientId = d.IngredientId,
StoreId = h.StoreId,
Damage = d.Damage,
Wast = d.Wast,
Others = d.Others,
BusinessId = h.BusinessId
}).ToList();
if (lstAllocation == null)
{
lstAllocation = new List <IngredientsUsageModels>();
}
//lst dataEntry
var lstDataEntry = (from d in cxt.I_DataEntryDetail
join h in cxt.I_DataEntry on d.DataEntryId equals h.Id
where h.StoreId == model.StoreId &&
lstBusinessIds.Contains(h.BusinessId)
//&& DbFunctions.TruncateTime(h.EntryDate) >= DbFunctions.TruncateTime(model.ApplyFrom)
//&& DbFunctions.TruncateTime(h.EntryDate) <= DbFunctions.TruncateTime(model.ApplyTo)
select new IngredientsUsageModels()
{
IngredientId = d.IngredientId,
StoreId = h.StoreId,
Damage = d.Damage.HasValue? d.Damage.Value:0,
Wast = d.Wastage.HasValue? d.Wastage.Value:0,
Others = d.OrderQty.HasValue? d.OrderQty.Value:0,
BusinessId = h.BusinessId
}).ToList();
if (lstDataEntry == null)
{
lstDataEntry = new List <IngredientsUsageModels>();
}
var allocations = new List <IngredientsUsageModels>();
var dataEntrys = new List <IngredientsUsageModels>();
decimal openBal = 0; decimal stockIn = 0; decimal closeBal = 0; decimal stockOut = 0;
foreach (var item in lstResult)
{
var obj = (decimal)lstUsage.Where(ww => ww.ItemId == item.IngredientId
//&& item.Date.Date == ww.ApplyDate.Date).FirstOrDefault();
&& item.BusinessId == ww.BusinessId).Sum(ss => ss.Usage);
item.Sales = Math.Round((double)obj, 2);
allocations = lstAllocation.Where(ww => ww.IngredientId == item.IngredientId && ww.StoreId == item.StoreId && ww.BusinessId == item.BusinessId).ToList();
if (allocations != null && allocations.Any())
{
item.Damage = allocations.Sum(ss => ss.Damage);
item.Wast = allocations.Sum(ss => ss.Wast);
item.Others = allocations.Sum(ss => ss.Others);
item.IsExistAllocation = true;
}
decimal.TryParse(item.OpenBal.ToString(), out openBal);
decimal.TryParse(item.StockIn.ToString(), out stockIn);
decimal.TryParse(item.CloseBal.ToString(), out closeBal);
decimal.TryParse(item.StockOut.ToString(), out stockOut);
item.ActualSold = (double)((openBal + stockIn) - closeBal - stockOut);
//item.IsAllocation = Math.Abs(item.ActualSold - item.Sales) != (item.Damage + item.Wast + item.Others);//(item.ActualSold != item.Sales);
item.IsAllocation = (item.ActualSold != item.Sales);
//if (!item.IsExistAllocation)
//{
//item.Damage = 0;
//item.Wast = 0;
//item.Others = 0;
//
//dataEntrys = lstDataEntry.Where(ww => ww.IngredientId == item.IngredientId && ww.StoreId == item.StoreId && ww.BusinessId == item.BusinessId).ToList();
//if (dataEntrys != null && dataEntrys.Any())
//{
// item.Damage = dataEntrys.Sum(ss => ss.Damage);
// item.Wast = dataEntrys.Sum(ss => ss.Wast);
// item.Others = dataEntrys.Sum(ss => ss.Others);
//}
//}
item.VarianceQty = Math.Abs((double)((decimal)item.ActualSold - (decimal)item.Sales));
//item.IsAllocation = true;
item.DateDisplay = item.Date.ToString("MM/dd/yyyy");
//item.IsAllocation = true;
item.Adjust = item.AutoCloseBal.HasValue ? Math.Round((item.CloseBal - item.AutoCloseBal.Value), 4) : 0;
}
return(lstResult);
}
catch (Exception ex)
{
_logger.Error(ex.Message);
return(lstResult);
}
}
}
public List <DailyTransactionsReportModels> GetListIngredientUsage(BaseReportModel model, string StoreId, List <string> lstBusinessInputIds)
{
//lstBusinessInputIds = new List<string>();
//lstBusinessInputIds.Add("c382a128-18be-408b-9957-957c0b134031");
var lstResult = new List <DailyTransactionsReportModels>();
if (string.IsNullOrEmpty(StoreId))
{
return(lstResult);
}
using (NuWebContext cxt = new NuWebContext())
{
try
{
lstResult = (from h in cxt.I_StockCount
//from d in cxt.I_StockCountDetail.Where(ww => ww.StockCountId == h.Id).DefaultIfEmpty()
join d in cxt.I_StockCountDetail on h.Id equals d.StockCountId
from i in cxt.I_Ingredient.Where(ww => ww.Id == d.IngredientId).DefaultIfEmpty()
join baseUOM in cxt.I_UnitOfMeasure on i.BaseUOMId equals baseUOM.Id
where h.StoreId == StoreId &&
lstBusinessInputIds.Contains(h.BusinessId) &&
h.Status == (int)Commons.EStockCountStatus.Approved
select new DailyTransactionsReportModels()
{
Id = d.Id,
Date = h.StockCountDate,
StoreId = h.StoreId,
IngredientId = d.IngredientId,
IngredientCode = i.Code,
IngredientName = i.Name,
BaseUOMName = baseUOM.Name,
TypeName = i.IsPurchase ? "Purchase" : "Self-made",
OpenBal = d.OpenBal.HasValue ? d.OpenBal.Value : 0,
CloseBal = d.CloseBal,
BusinessId = h.BusinessId,
StartedOn = h.StartedOn,
ClosedOn = h.ClosedOn,
AutoCloseBal = d.AutoCloseBal
}).Distinct().ToList();
//Check Stock In
var lstBusinessIds = lstResult.Select(ss => ss.BusinessId).Distinct().ToList();
//Get received
var queryReceived = (from d in cxt.I_ReceiptNoteDetail
from h in cxt.I_ReceiptNote.Where(ww => ww.Id == d.ReceiptNoteId).DefaultIfEmpty()
from p in cxt.I_Purchase_Order_Detail.Where(ww => ww.Id == d.PurchaseOrderDetailId).DefaultIfEmpty()
where h.StoreId == StoreId && lstBusinessIds.Contains(h.BusinessId)
select new DailyTransactionsReportModels()
{
BusinessId = h.BusinessId,
IngredientId = p.IngredientId,
StockIn = d.BaseReceivingQty.HasValue ? d.BaseReceivingQty.Value : 0,
Received = d.BaseReceivingQty.HasValue ? d.BaseReceivingQty.Value : 0,
StoreId = h.StoreId,
}).ToList();
//Get TransferIn
var queryTransferIn = (from d in cxt.I_Stock_Transfer_Detail
from h in cxt.I_Stock_Transfer.Where(ww => ww.Id == d.StockTransferId).DefaultIfEmpty()
where h.ReceiveStoreId == StoreId && lstBusinessIds.Contains(h.BusinessReceiveId)
select new DailyTransactionsReportModels()
{
BusinessId = h.BusinessReceiveId,
IngredientId = d.IngredientId,
StockIn = d.ReceiveBaseQty.HasValue ? d.ReceiveBaseQty.Value : 0,
TransferIn = d.ReceiveBaseQty.HasValue ? d.ReceiveBaseQty.Value : 0,
StoreId = h.ReceiveStoreId,
}).ToList();
//return
var queryReturn = (from d in cxt.I_Return_Note_Detail
join h in cxt.I_Return_Note on d.ReturnNoteId equals h.Id
join rd in cxt.I_ReceiptNoteDetail on d.ReceiptNoteDetailId equals rd.Id
join pd in cxt.I_Purchase_Order_Detail on rd.PurchaseOrderDetailId equals pd.Id
join rh in cxt.I_ReceiptNote on h.ReceiptNoteId equals rh.Id
where lstBusinessIds.Contains(h.BusinessId) && rh.StoreId == StoreId
select new DailyTransactionsReportModels()
{
IngredientId = pd.IngredientId,
StockOut = d.ReturnBaseQty.HasValue ? d.ReturnBaseQty.Value : 0,
Return = d.ReturnBaseQty.HasValue ? d.ReturnBaseQty.Value : 0,
BusinessId = h.BusinessId,
StoreId = rh.StoreId
}).ToList();
//Transfer out
var queryTransfer = (from d in cxt.I_Stock_Transfer_Detail
from h in cxt.I_Stock_Transfer.Where(ww => ww.Id == d.StockTransferId).DefaultIfEmpty()
where h.IssueStoreId == StoreId && lstBusinessIds.Contains(h.BusinessId)
select new DailyTransactionsReportModels()
{
BusinessId = h.BusinessId,
IngredientId = d.IngredientId,
StockOut = d.IssueBaseQty.HasValue ? d.IssueBaseQty.Value : 0,
TransferOut = d.IssueBaseQty.HasValue ? d.IssueBaseQty.Value : 0,
StoreId = h.IssueStoreId,
}).ToList();
//data entry
var queryDataEntry = (from d in cxt.I_DataEntryDetail
from h in cxt.I_DataEntry.Where(ww => ww.Id == d.DataEntryId).DefaultIfEmpty()
where h.StoreId == StoreId && lstBusinessIds.Contains(h.BusinessId)
select new DailyTransactionsReportModels()
{
BusinessId = h.BusinessId,
IngredientId = d.IngredientId,
StockOut = (d.Damage.HasValue ? d.Damage.Value : 0) + (d.Wastage.HasValue ? d.Wastage.Value : 0)
+ (d.OrderQty.HasValue ? d.OrderQty.Value : 0),
Damage = d.Damage.HasValue ? d.Damage.Value : 0,
Wast = d.Wastage.HasValue ? d.Wastage.Value : 0,
Others = d.OrderQty.HasValue ? d.OrderQty.Value : 0,
StoreId = h.StoreId
}).ToList();
//Receive Note Safe-Made
var queryRNSeftMade = (from d in cxt.I_ReceiptNoteForSeftMadeDetail
join h in cxt.I_ReceiptNoteForSeftMade on d.ReceiptNoteId equals h.Id
join i in cxt.I_Ingredient on d.IngredientId equals i.Id
where lstBusinessIds.Contains(h.BusinessId) && h.StoreId == StoreId
select new IngredientsUsageModels()
{
Id = h.Id,
BusinessId = h.BusinessId,
IngredientId = d.IngredientId,
StockIn = d.BaseReceivingQty.HasValue ? d.BaseReceivingQty.Value : 0,
StoreId = h.StoreId,
IsSeftMade = i.IsSelfMode,
IsStockAble = i.StockAble.HasValue ? i.StockAble.Value : false
}).ToList();
//Get stock out for Receive Note Safe-Made dependent
var queryRNSeftMadeDepens = (from dd in cxt.I_ReceiptNoteForSeftMadeDependentDetail
join d in cxt.I_ReceiptNoteForSeftMadeDetail on dd.RNSelfMadeDetailId equals d.Id
join h in cxt.I_ReceiptNoteForSeftMade on d.ReceiptNoteId equals h.Id
join i in cxt.I_Ingredient on d.IngredientId equals i.Id
where lstBusinessIds.Contains(h.BusinessId) && h.StoreId == StoreId
select new IngredientsUsageModels()
{
Id = h.Id,
BusinessId = h.BusinessId,
IngredientId = dd.IngredientId,
StockOut = dd.StockOutQty,
StoreId = h.StoreId
}).ToList();
//Check usage
var lstUsage = (from d in cxt.I_UsageManagementDetail
from h in cxt.I_UsageManagement.Where(ww => ww.Id == d.UsageManagementId).DefaultIfEmpty()
join i in cxt.I_Ingredient on d.IngredientId equals i.Id
where h.StoreId == StoreId &&
lstBusinessIds.Contains(h.BusinessId)
select new UsageManagementDetailModel()
{
ItemId = d.IngredientId,
Usage = d.Usage,
ApplyDate = h.DateFrom,
BusinessId = h.BusinessId,
IsSeftMade = i.IsSelfMode,
StockAble = i.StockAble.HasValue ? i.StockAble.Value : false
}).ToList();
if (lstUsage == null)
{
lstUsage = new List <UsageManagementDetailModel>();
}
var lstTmpId = lstUsage.Where(ww => ww.IsSeftMade && !ww.StockAble).Select(ss => ss.ItemId).ToList();
if (lstTmpId != null && lstTmpId.Any())
{
//check ingredient dependent
RecipeFactory _recipeFactory = new RecipeFactory();
var _objIngredientDependent = _recipeFactory.GetRecipesByIngredientSeftMade(lstTmpId);
var lstIngredientSMNotStockAble = lstUsage.Where(ww => ww.IsSeftMade && !ww.StockAble).ToList();
var businessIds = lstIngredientSMNotStockAble.Select(ss => ss.BusinessId).Distinct().ToList();
foreach (var item in businessIds)
{
var tmp = lstIngredientSMNotStockAble.Where(ww => ww.BusinessId == item).ToList();
foreach (var subitem in tmp)
{
var obj = _objIngredientDependent.ListChilds.Where(ww => ww.MixtureIngredientId == subitem.ItemId).ToList();
foreach (var item1 in obj)
{
lstUsage.Add(new UsageManagementDetailModel()
{
ItemId = item1.Id,
Usage = (item1.BaseUsage * subitem.Usage),
BusinessId = item
});
}
//obj.ForEach(ss => ss.BaseUsage = ss.BaseUsage * subitem.Usage);
}
}
lstUsage = lstUsage.Where(ww => !lstTmpId.Contains(ww.ItemId)).ToList();
}
//Check allocation
var lstAllocation = (from d in cxt.I_AllocationDetail
from h in cxt.I_Allocation.Where(ww => ww.Id == d.AllocationId).DefaultIfEmpty()
where h.StoreId == StoreId &&
lstBusinessIds.Contains(h.BusinessId)
select new DailyTransactionsReportModels()
{
IngredientId = d.IngredientId,
StoreId = h.StoreId,
Damage = d.Damage,
Wast = d.Wast,
Others = d.Others,
BusinessId = h.BusinessId
}).ToList();
if (lstAllocation == null)
{
lstAllocation = new List <DailyTransactionsReportModels>();
}
var allocations = new List <DailyTransactionsReportModels>();
var dataEntrys = new List <DailyTransactionsReportModels>();
//decimal openBal = 0; decimal stockIn = 0; decimal closeBal = 0; decimal stockOut = 0;
foreach (var item in lstResult)
{
double obj = (double)lstUsage.Where(ww => ww.ItemId == item.IngredientId &&
item.BusinessId == ww.BusinessId).Sum(ss => (decimal)ss.Usage);
item.Sales = Math.Round(obj, 2);
//received
item.Received = (double)queryReceived.Where(ww => ww.IngredientId == item.IngredientId &&
item.BusinessId == ww.BusinessId).Sum(ss => (decimal)ss.Received);
item.Received += (double)queryRNSeftMade.Where(ww => ww.IngredientId == item.IngredientId &&
item.BusinessId == ww.BusinessId).Sum(ss => (decimal)ss.StockIn);
//Transfer In
item.TransferIn = (double)queryTransferIn.Where(ww => ww.IngredientId == item.IngredientId &&
item.BusinessId == ww.BusinessId).Sum(ss => (decimal)ss.TransferIn);
//return
item.Return = (double)queryReturn.Where(ww => ww.IngredientId == item.IngredientId &&
item.BusinessId == ww.BusinessId).Sum(ss => (decimal)ss.Return);
//Transfer out
item.TransferOut = (double)queryTransfer.Where(ww => ww.IngredientId == item.IngredientId &&
item.BusinessId == ww.BusinessId).Sum(ss => (decimal)ss.TransferOut);
item.UseForSelfMade = queryRNSeftMadeDepens.Where(ww => ww.IngredientId == item.IngredientId && item.BusinessId == ww.BusinessId).Sum(ss => (decimal)ss.StockOut);
allocations = lstAllocation.Where(ww => ww.IngredientId == item.IngredientId && ww.StoreId == item.StoreId &&
ww.BusinessId == item.BusinessId).ToList();
if (allocations != null && allocations.Any())
{
item.Damage = (double)allocations.Sum(ss => (decimal)ss.Damage);
item.Wast = (double)allocations.Sum(ss => (decimal)ss.Wast);
item.Others = (double)allocations.Sum(ss => (decimal)ss.Others);
item.IsExistAllocation = true;
}
//decimal.TryParse(item.OpenBal.ToString(), out openBal);
//decimal.TryParse(item.StockIn.ToString(), out stockIn);
//decimal.TryParse(item.CloseBal.ToString(), out closeBal);
//decimal.TryParse(item.StockOut.ToString(), out stockOut);
//if (!item.IsExistAllocation)
//{
dataEntrys = queryDataEntry.Where(ww => ww.IngredientId == item.IngredientId && ww.StoreId == item.StoreId && ww.BusinessId == item.BusinessId).ToList();
if (dataEntrys != null && dataEntrys.Any())
{
item.Damage += (double)dataEntrys.Sum(ss => (decimal)ss.Damage);
item.Wast += (double)dataEntrys.Sum(ss => (decimal)ss.Wast);
item.Others += (double)dataEntrys.Sum(ss => (decimal)ss.Others);
}
//}
//item.DateDisplay = item.Date.ToString("MM/dd/yyyy");
//item.CloseBal = item.OpenBal + item.Received + item.TransferIn - item.Return - item.TransferOut - item.Damage - item.Wast - item.Others - item.Sales;
}
return(lstResult);
}
catch (Exception ex)
{
_logger.Error(ex.Message);
return(lstResult);
}
}
}