public List <BusinessDayDisplayModels> GetBusinessDays(DateTime dFrom, DateTime dTo, string storeId, int mode)
{
var result = new List <BusinessDayDisplayModels>();
using (var cxt = new NuWebContext())
{
var query = cxt.G_BusinessDay.Where(ww => ww.StartedOn >= dFrom &&
ww.StartedOn <= dTo && ww.StoreId == storeId && ww.Mode == mode)
.ToList();
foreach (var item in query)
{
var obj = new BusinessDayDisplayModels();
obj.DateFrom = item.StartedOn;
obj.StoreId = item.StoreId;
obj.Id = item.Id;
if (item.ClosedOn == Commons.MinDate)
{
obj.DateTo = new DateTime(item.StartedOn.Year, item.StartedOn.Month, item.StartedOn.Day, 23, 59, 59);
obj.DateDisplay = item.StartedOn.ToString("dd/MM/yyyy HH:mm") + " - ";
}
else
{
obj.DateTo = item.ClosedOn;
obj.DateDisplay = item.StartedOn.ToString("dd/MM/yyyy HH:mm") + " - " + item.ClosedOn.ToString("dd/MM/yyyy HH:mm");
}
result.Add(obj);
}
}
return(result);
}
public List <BusinessDayDisplayModels> GetBusinessDays_Old(DateTime dFrom, DateTime dTo, List <string> lstStoreId, int mode)
{
var result = new List <BusinessDayDisplayModels>();
using (var cxt = new NuWebContext())
{
var query = cxt.G_BusinessDay.Where(ww => ((ww.StartedOn >= dFrom &&
ww.StartedOn <= dTo) ||
(DbFunctions.TruncateTime(ww.StartedOn) <= DbFunctions.TruncateTime(dFrom) && DbFunctions.TruncateTime(ww.ClosedOn) >= DbFunctions.TruncateTime(dTo)) ||
(DbFunctions.TruncateTime(ww.ClosedOn) == DbFunctions.TruncateTime(dFrom))) &&
lstStoreId.Contains(ww.StoreId) && ww.Mode == mode)
.ToList();
foreach (var item in query)
{
var obj = new BusinessDayDisplayModels();
obj.DateFrom = item.StartedOn;
obj.StoreId = item.StoreId;
obj.Id = item.Id;
if (item.ClosedOn == Commons.MinDate)
{
obj.DateTo = new DateTime(item.StartedOn.Year, item.StartedOn.Month, item.StartedOn.Day, 23, 59, 59);
obj.DateDisplay = item.StartedOn.ToString("dd/MM/yyyy HH:mm") + " - ";
}
else
{
obj.DateTo = item.ClosedOn;
obj.DateDisplay = item.StartedOn.ToString("dd/MM/yyyy HH:mm") + " - " + item.ClosedOn.ToString("dd/MM/yyyy HH:mm");
}
result.Add(obj);
}
}
result = result.OrderBy(oo => oo.DateFrom).ToList();
return(result);
}
public async void CalUsageManagementForInventory(string companyId, List <StockUsageModel> lstSale, string businessDayId, string storeId, DateTime dFrom, DateTime dTo)
{
var result = new List <UsageManagementModel>();
if (lstSale != null && lstSale.Count > 0)
{
var lstItemIdSale = lstSale.Select(ss => ss.ItemId).Distinct().ToList();
var lstRecipeItems = GetRecipeItem(storeId);
var lstrecipeModifier = GetRecipeModifier(storeId);
lstRecipeItems.AddRange(lstrecipeModifier);
List <ItemSaleForIngredientModel> _lstDetailTmp = new List <ItemSaleForIngredientModel>();
if (lstRecipeItems != null && lstRecipeItems.Count > 0)
{
lstRecipeItems = lstRecipeItems.Where(ww => lstItemIdSale.Contains(ww.ItemId)).ToList();
foreach (var item in lstRecipeItems)
{
var lstSaleItem = lstSale.Where(ww => ww.ItemId == item.ItemId).ToList();
foreach (var subItem in lstSaleItem)
{
item.Usage += item.BaseUsage * subItem.Quantity;
}
}
var lstUsageGroupIngredient = lstRecipeItems.GroupBy(gg => gg.IngredientId);
UsageManagementModel usageManagementModel = null;
BusinessDayDisplayModels busDay = new BusinessDayDisplayModels();
busDay.StoreId = storeId;
busDay.DateDisplay = dFrom.ToString("dd/MM/yyyy HH:mm") + " - " + dTo.ToString("dd/MM/yyyy HH:mm");
busDay.DateFrom = dFrom;
busDay.DateTo = dTo;
foreach (var item in lstUsageGroupIngredient)
{
usageManagementModel = new UsageManagementModel();
usageManagementModel.Id = item.Key;
usageManagementModel.Code = item.Select(ss => ss.IngredientCode).FirstOrDefault();
//usageManagementModel.Name = item.Select(ss => ss.IngredientName).FirstOrDefault();
//usageManagementModel.UOMName = item.Select(ss => ss.UOMName).FirstOrDefault();
usageManagementModel.Usage = item.Sum(ss => ss.Usage);
usageManagementModel.ListDetail = CalUsageManagementByIngredientForInventory(lstSale, lstRecipeItems, busDay, usageManagementModel.Id);
result.Add(usageManagementModel);
}
}
}
result = result.Where(ww => ww.Usage > 0).ToList();
_usageManagementFactory.SaveUsageManagement(companyId, storeId, businessDayId, dFrom, dTo, result);
//return result;
}
public List <BusinessDayDisplayModels> GetBusinessDays(DateTime dFrom, DateTime dTo, List <string> lstStoreId, int mode)
{
var result = new List <BusinessDayDisplayModels>();
using (var cxt = new NuWebContext())
{
//var query = cxt.G_BusinessDay.Where(ww =>
// ww.StartedOn >= dFrom && ww.StartedOn <= dTo
// && lstStoreId.Contains(ww.StoreId) && ww.Mode == mode)
// .ToList();
var query = (from b in cxt.G_BusinessDay.AsNoTracking()
where b.StartedOn >= dFrom && b.StartedOn <= dTo &&
lstStoreId.Contains(b.StoreId) && b.Mode == mode
select new BusinessDayModels()
{
StartedOn = b.StartedOn,
StoreId = b.StoreId,
ClosedOn = b.ClosedOn,
Id = b.Id
}).ToList();
foreach (var item in query)
{
var obj = new BusinessDayDisplayModels();
obj.DateFrom = item.StartedOn;
obj.StoreId = item.StoreId;
obj.Id = item.Id;
if (item.ClosedOn == Commons.MinDate)
{
obj.DateTo = new DateTime(item.StartedOn.Year, item.StartedOn.Month, item.StartedOn.Day, 23, 59, 59);
obj.DateDisplay = item.StartedOn.ToString("dd/MM/yyyy HH:mm") + " - ";
}
else
{
obj.DateTo = item.ClosedOn;
obj.DateDisplay = item.StartedOn.ToString("dd/MM/yyyy HH:mm") + " - " + item.ClosedOn.ToString("dd/MM/yyyy HH:mm");
}
result.Add(obj);
}
}
result = result.OrderBy(oo => oo.DateFrom).ToList();
return(result);
}
public List <UsageManagementDetailModel> CalUsageManagementByIngredientForInventory(List <StockUsageModel> lstSale,
List <RecipeItemModel> lstRecipeItems, BusinessDayDisplayModels busDay, string ingredientId)
{
List <UsageManagementDetailModel> result = new List <UsageManagementDetailModel>();
var lstItemIdSale = lstSale.Select(ss => ss.ItemId).Distinct().ToList();
List <ItemSaleForIngredientModel> _lstDetailTmp = new List <ItemSaleForIngredientModel>();
lstRecipeItems = lstRecipeItems.Where(ww => ww.IngredientId == ingredientId).ToList();
if (lstRecipeItems != null && lstRecipeItems.Count > 0)
{
lstRecipeItems = lstRecipeItems.Where(ww => lstItemIdSale.Contains(ww.ItemId)).ToList();
var lstItemIdInrecipe = lstRecipeItems.Select(ss => ss.ItemId).Distinct().ToList();
//Group item sale
var lstItemSaleHaveRecipe = lstSale.Where(ww => lstItemIdInrecipe.Contains(ww.ItemId)).ToList();
if (lstItemSaleHaveRecipe != null && lstItemSaleHaveRecipe.Count > 0)
{
UsageManagementDetailModel detail = null;
var lstDish = lstItemSaleHaveRecipe.Where(ww => ww.CreatedDate >= busDay.DateFrom && ww.CreatedDate <= busDay.DateTo).ToList();
if (lstDish != null && lstDish.Count > 0)
{
var lstDishGroup = lstDish.GroupBy(gg => gg.ItemId);
foreach (var dish in lstDishGroup)
{
var recipe = lstRecipeItems.Where(ww => ww.ItemId == dish.Key).FirstOrDefault();
if (recipe != null)
{
detail = new UsageManagementDetailModel();
detail.BusinessDay = busDay.DateDisplay;
detail.ItemId = dish.Key;
detail.ItemName = dish.Select(ss => ss.ItemName).FirstOrDefault();
detail.Qty = dish.Sum(ss => ss.Quantity);
detail.Usage = detail.Qty * recipe.BaseUsage;
result.Add(detail);
}
}
}
}
}
return(result);
}
public XLWorkbook Report(BaseReportModel model, List <StoreModels> lstStore)
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Monthly_Transactions_Report");
CreateReportHeaderNew(ws, 15, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Monthly Transactions Report").ToUpper());
ws.Range(1, 1, 4, 15).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 4, 15).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 4, 15).Style.Border.InsideBorderColor = XLColor.FromHtml("#000000");
ws.Range(1, 1, 4, 15).Style.Border.OutsideBorderColor = XLColor.FromHtml("#000000");
var _lstBusDayAllStore = _baseFactory.GetBusinessDays(model.FromDate, model.ToDate, model.ListStores, model.Mode);
int row = 5;
string storeName = string.Empty, storeId = string.Empty;
for (int i = 0; i < lstStore.Count; i++)
{
//Get StoreName
StoreModels store = lstStore[i];
storeName = store.Name;
storeId = store.Id;
//header report
FormatStoreHeader(storeName, ref ws, ref row);
row++;
int startRow = row;
var businessInStoreId = _lstBusDayAllStore.Where(ww => ww.StoreId == storeId).Select(ss => ss.Id).ToList();
List <DailyTransactionsReportModels> ingredients = null;
if (businessInStoreId != null && businessInStoreId.Any())
{
ingredients = GetListIngredientUsage(model, storeId, businessInStoreId);
}
var listDataSumary = new List <DailyTransactionsReportModels>();
if (ingredients != null && ingredients.Count > 0)
{
//Get all business day in store
var businessInStore = _lstBusDayAllStore.Where(ww => ww.StoreId == storeId).ToList();
// Group by year, month of business day
//var businessMonthInStore = businessInStore.GroupBy(r => new { r.DateFrom.Year, r.DateFrom.Month })
// .Select(x => new {
// year = x.Key.Year,
// month = x.Key.Month,
// startDateFrom = x.Select(r => r.DateFrom).FirstOrDefault(),
// endDateFrom = x.Select(r => r.DateFrom).LastOrDefault(),
// endDateTo = x.Select(r => r.DateTo).LastOrDefault(),
// }).OrderBy(x => x.year).ThenBy(x => x.month).ToList();
var businessMonthInStore = businessInStore.GroupBy(r => new { Year = r.DateFrom.Year, Month = r.DateFrom.Month })
.OrderBy(x => x.Key.Year).ThenBy(x => x.Key.Month).ToList();
BusinessDayDisplayModels minBD = null;
BusinessDayDisplayModels maxBD = null;
List <DailyTransactionsReportModels> lstDataInMonthOpenBal = null;
List <string> lstBusinessIds = new List <string>();
for (int d = 0; d < businessMonthInStore.Count; d++)
{
minBD = businessMonthInStore[d].OrderBy(oo => oo.DateFrom).FirstOrDefault();
maxBD = businessMonthInStore[d].OrderBy(oo => oo.DateFrom).LastOrDefault();
var listOpenBals = ingredients.Where(ww => ww.BusinessId == minBD.Id && ww.StoreId == storeId).ToList();
lstBusinessIds = businessMonthInStore[d].Select(ss => ss.Id).ToList();
lstDataInMonthOpenBal = ingredients.Where(m => lstBusinessIds.Contains(m.BusinessId) && m.StoreId == storeId).ToList();
List <DailyTransactionsReportModels> lstDataInMonth = ingredients.Where(m =>
lstBusinessIds.Contains(m.BusinessId) && m.StoreId == storeId)
.GroupBy(m => new { m.IngredientCode, m.IngredientName, m.BaseUOMName, m.TypeName })
.Select(mm => new DailyTransactionsReportModels()
{
IngredientCode = mm.Key.IngredientCode,
IngredientName = mm.Key.IngredientName,
BaseUOMName = mm.Key.BaseUOMName,
TypeName = mm.Key.TypeName,
OpenBal = listOpenBals.Where(ww => ww.IngredientCode == mm.Key.IngredientCode && ww.TypeName == mm.Key.TypeName)
.Select(m => m.OpenBal).FirstOrDefault(),
Received = mm.Sum(m => m.Received),
TransferIn = mm.Sum(m => m.TransferIn),
TransferOut = mm.Sum(m => m.TransferOut),
Return = mm.Sum(m => m.Return),
Damage = mm.Sum(m => m.Damage),
Wast = mm.Sum(m => m.Wast),
Others = mm.Sum(m => m.Others),
UseForSelfMade = mm.Sum(m => m.UseForSelfMade),
Sales = mm.Sum(m => m.Sales)
}).OrderBy(mm => mm.TypeName).ThenBy(mm => mm.IngredientName).ToList();
listDataSumary.AddRange(lstDataInMonth);
// Business day
if (lstDataInMonth != null && lstDataInMonth.Any())
{
// Name of month
ws.Range(row, 1, row, 15).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey(CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(businessMonthInStore[d].Key.Month)));
ws.Range(row, 1, row, 15).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range(row, 1, row++, 15).Style.Fill.BackgroundColor = XLColor.FromHtml("#ffcc99");
// Infomation of business day in month
//var startDateFrom = businessMonthInStore[d].startDateFrom.ToString("MM/dd/yyyy");
//var endDateFrom = businessMonthInStore[d].endDateTo.ToString("MM/dd/yyyy");
if (minBD.DateFrom.Date == maxBD.DateTo.Date)
{
ws.Range(row, 1, row, 15).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Date") + ": " + minBD.DateFrom.ToString("MM/dd/yyyy"));
}
else
{
ws.Range(row, 1, row, 15).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Date")
+ ": " + _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("From") + " " + minBD.DateFrom.ToString("MM/dd/yyyy") + " "
+ _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("To") + " " + maxBD.DateTo.ToString("MM/dd/yyyy"));
}
ws.Range(row, 1, row, 15).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Range(row - 1, 1, row++, 15).Style.Font.Bold = true;
decimal closeBal = 0;
int rowTbl = row;
for (int j = 0; j < lstDataInMonth.Count; j++)
{
closeBal = (decimal)lstDataInMonth[j].OpenBal + (decimal)lstDataInMonth[j].Received + (decimal)lstDataInMonth[j].TransferIn - (decimal)lstDataInMonth[j].Return - (decimal)lstDataInMonth[j].TransferOut - (decimal)lstDataInMonth[j].Damage - (decimal)lstDataInMonth[j].Wast - (decimal)lstDataInMonth[j].Others - lstDataInMonth[j].UseForSelfMade - (decimal)lstDataInMonth[j].Sales;
ws.Cell("A" + row).Value = "'" + lstDataInMonth[j].IngredientCode;
ws.Cell("B" + row).Value = lstDataInMonth[j].IngredientName;
ws.Cell("C" + row).Value = lstDataInMonth[j].BaseUOMName;
ws.Cell("D" + row).Value = lstDataInMonth[j].TypeName;
ws.Cell("E" + row).Value = lstDataInMonth[j].OpenBal;
ws.Cell("F" + row).Value = lstDataInMonth[j].Received;
ws.Cell("G" + row).Value = lstDataInMonth[j].TransferIn;
ws.Cell("H" + row).Value = lstDataInMonth[j].Return;
ws.Cell("I" + row).Value = lstDataInMonth[j].TransferOut;
ws.Cell("J" + row).Value = lstDataInMonth[j].Damage;
ws.Cell("K" + row).Value = lstDataInMonth[j].Wast;
ws.Cell("L" + row).Value = lstDataInMonth[j].Others;
ws.Cell("M" + row).Value = lstDataInMonth[j].UseForSelfMade;
ws.Cell("N" + row).Value = lstDataInMonth[j].Sales;
ws.Cell("O" + row++).Value = closeBal;
}
ws.Range(rowTbl, 5, row - 1, 15).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Range(rowTbl, 5, row - 1, 15).Style.NumberFormat.Format = "#,##0.000";
}
}
}
ws.Range(startRow, 1, row - 1, 15).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(startRow, 1, row - 1, 15).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Range(startRow, 1, row - 1, 15).Style.Border.InsideBorderColor = XLColor.FromHtml("#000000");
ws.Range(startRow, 1, row - 1, 15).Style.Border.OutsideBorderColor = XLColor.FromHtml("#000000");
row += 2;
// Summary
int rowSum = row;
ws.Range(row, 1, row, 11).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Summary").ToUpper());
ws.Range(row, 1, row++, 11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Cell("A" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Ingredient Code");
ws.Cell("B" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Ingredient Name");
ws.Cell("C" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Base UOM");
ws.Cell("D" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Type");
ws.Cell("E" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Open Balance");
ws.Cell("F" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Purchase");
ws.Cell("G" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Transfer");
ws.Cell("H" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Data Entry");
ws.Cell("I" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Used for Self Made Ingredient");
ws.Cell("I" + row).Style.Alignment.WrapText = true;
ws.Cell("J" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Sale");
ws.Cell("K" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Close Balance");
ws.Range(row - 1, 1, row, 11).Style.Fill.BackgroundColor = XLColor.FromHtml("#d9d9d9");
ws.Range(row, 1, row, 11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range(row, 1, row, 11).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Range(row - 1, 1, row++, 11).Style.Font.Bold = true;
var listSumary = listDataSumary.GroupBy(o => new { o.IngredientCode, o.IngredientName, o.BaseUOMName, o.TypeName })
.Select(o => new DailyTransactionsReportModels()
{
IngredientCode = o.Key.IngredientCode,
IngredientName = o.Key.IngredientName,
BaseUOMName = o.Key.BaseUOMName,
TypeName = o.Key.TypeName
}).OrderBy(o => o.TypeName).ThenBy(o => o.IngredientName).ToList();
decimal openBalance = 0;
decimal purchase = 0;
decimal transfer = 0;
decimal dataEntry = 0;
decimal sale = 0;
decimal usaForSelfMade = 0;
int rowTblSum = row;
foreach (var data in listSumary)
{
var listChild = listDataSumary.Where(o => o.IngredientCode == data.IngredientCode &&
o.IngredientName == data.IngredientName && o.BaseUOMName == data.BaseUOMName).OrderBy(o => o.StartedOn).ToList();
openBalance = listChild.Select(o => (decimal)o.OpenBal).FirstOrDefault();
purchase = listChild.Sum(o => (decimal)o.Received - (decimal)o.Return);
transfer = listChild.Sum(o => (decimal)o.TransferIn - (decimal)o.TransferOut);
dataEntry = listChild.Sum(o => (decimal)o.Damage + (decimal)o.Wast + (decimal)o.Others);
sale = listChild.Sum(o => (decimal)o.Sales);
usaForSelfMade = listChild.Sum(o => (decimal)o.UseForSelfMade);
ws.Cell("A" + row).Value = "'" + data.IngredientCode;
ws.Cell("B" + row).Value = data.IngredientName;
ws.Cell("C" + row).Value = data.BaseUOMName;
ws.Cell("D" + row).Value = data.TypeName;
ws.Cell("E" + row).Value = openBalance;
ws.Cell("F" + row).Value = purchase;
ws.Cell("G" + row).Value = transfer;
ws.Cell("H" + row).Value = dataEntry;
ws.Cell("I" + row).Value = usaForSelfMade;
ws.Cell("J" + row).Value = sale;
ws.Cell("K" + row++).Value = (openBalance + purchase + transfer - dataEntry - sale - usaForSelfMade);
}
ws.Range(rowSum, 1, row - 1, 11).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(rowSum, 1, row - 1, 11).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Range(rowSum, 1, row - 1, 11).Style.Border.InsideBorderColor = XLColor.FromHtml("#000000");
ws.Range(rowSum, 1, row - 1, 11).Style.Border.OutsideBorderColor = XLColor.FromHtml("#000000");
ws.Range(rowTblSum, 5, row - 1, 11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Range(rowTblSum, 5, row - 1, 11).Style.NumberFormat.Format = "#,##0.000";
row++;
List <int> lstWidCol = new List <int>()
{
20, 20, 20, 20, 18, 18, 18, 18, 30, 18, 18, 18, 30, 18, 20
};
for (int y = 0; y < lstWidCol.Count; y++)
{
ws.Column(y + 1).Width = lstWidCol[y];
}
}
return(wb);
}
