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