public List <UsageManagementModel> GetUsageManagement(UsageManagementRequest request)
{
var result = new List <UsageManagementModel>();
request.DateFrom = new DateTime(request.DateFrom.Year, request.DateFrom.Month, request.DateFrom.Day, 0, 0, 0);
request.DateTo = new DateTime(request.DateTo.Year, request.DateTo.Month, request.DateTo.Day, 23, 59, 59);
BaseFactory _baseFactory = new BaseFactory();
using (var cxt = new NuWebContext())
{
//get business day
var _lstBusDayAllStore = _baseFactory.GetBusinessDays(request.DateFrom, request.DateTo, request.StoreId, request.Mode);
if (_lstBusDayAllStore != null && _lstBusDayAllStore.Any())
{
var dFrom = _lstBusDayAllStore.Min(ss => ss.DateFrom);
var dTo = _lstBusDayAllStore.Max(ss => ss.DateTo);
int index = 1;
var query = (from u in cxt.I_UsageManagement
join d in cxt.I_UsageManagementDetail on u.Id equals d.UsageManagementId
join i in cxt.I_Ingredient on d.IngredientId equals i.Id
where u.DateFrom >= dFrom && u.DateTo <= dTo
select new { d, i });
if (query != null && query.Any())
{
var lstTmp = new List <UsageManagementModel>();
UsageManagementModel obj = null;
foreach (var item in query)
{
obj = new UsageManagementModel();
//obj.Index = index;
obj.Id = item.d.Id;
obj.Code = item.i.Code;
obj.Name = item.i.Name;
obj.UOMName = item.i.BaseUOMName;
obj.Usage = item.d.Usage;
lstTmp.Add(obj);
//index++;
}
var lstGroupIngrdient = lstTmp.GroupBy(gg => gg.Code);
foreach (var item in lstGroupIngrdient)
{
obj = new UsageManagementModel();
obj.Index = index;
obj.ListUsageManagementDetailId = string.Join("|", item.Select(ss => ss.Id));
obj.Code = item.Key;
obj.Name = item.Select(ss => ss.Name).FirstOrDefault();
obj.UOMName = item.Select(ss => ss.UOMName).FirstOrDefault();
obj.Usage = item.Sum(ss => ss.Usage);
result.Add(obj);
index++;
}
//result = result.OrderBy(oo => oo.Index).ToList();
}
}
}
return(result);
}
public ActionResult Report_Current(RPHourlyItemizedSalesModels model)
{
try
{
var _lstCateChecked = new List <RFilterCategoryModel>();
var _lstSetChecked = new List <RFilterCategoryModel>();
if (model.ToTime == new TimeSpan(0, 0, 0))
{
model.ToTime = new TimeSpan(23, 59, 59);
}
DateTime dFrom = new DateTime(model.FromDate.Year, model.FromDate.Month, model.FromDate.Day, model.FromTime.Hours, model.FromTime.Minutes, 0)
, dTo = new DateTime(model.ToDate.Year, model.ToDate.Month, model.ToDate.Day, model.ToTime.Hours, model.ToTime.Minutes, 59);
if (model.FromDate > model.ToDate)
{
ModelState.AddModelError("FromDate", CurrentUser.GetLanguageTextFromKey("From Date must be less than To Date."));
}
if (dFrom > dTo)
{
ModelState.AddModelError("FromTime", CurrentUser.GetLanguageTextFromKey("From Time must be less than To Time."));
}
else if (model.Type == Commons.TypeCompanySelected) //Company
{
if (model.ListCompanys == null)
{
ModelState.AddModelError("ListCompanys", CurrentUser.GetLanguageTextFromKey("Please choose company."));
}
}
else //Store
{
if (model.ListStores == null)
{
ModelState.AddModelError("ListStores", CurrentUser.GetLanguageTextFromKey("Please choose store."));
}
}
//ListCategories
if (model.ListStoreCate != null)
{
foreach (var item in model.ListStoreCate)
{
model.ListCategories.AddRange(item.ListCategoriesSel);
}
}
// Get list categories selected
#region List categories old
//var lstChildCheck = new List<RFilterCategoryModel>();
//if (model.ListCategories != null && model.ListCategories.Count > 0)
//{
// _lstCateChecked = model.ListCategories.Where(ww => ww.Checked).ToList();
// var lstCateChild = model.ListCategories.Where(ww => ww.ListChilds != null && ww.ListChilds.Count > 0).ToList();
// foreach (var item in lstCateChild)
// {
// //_lstCateChecked.AddRange(item.ListChilds.Where(ww => ww.Checked));
// _categoriesFactory.GetCategoryCheck(ref lstChildCheck, item.ListChilds);
// }
// _lstCateChecked.AddRange(lstChildCheck);
//}
//else
//{
// _lstCateChecked = GetListCategories(model.Type == 1 ? model.ListCompanys : model.ListStores, model.Type);
// _lstCateChecked.ForEach(x =>
// {
// x.Checked = true;
// if (x.ListChilds != null && x.ListChilds.Count > 0)
// {
// x.ListChilds.ForEach(z =>
// {
// z.Checked = true;
// });
// };
// });
// //====
// var lstCateChild = _lstCateChecked.Where(ww => ww.ListChilds != null && ww.ListChilds.Count > 0).ToList();
// foreach (var item in lstCateChild)
// {
// //_lstCateChecked.AddRange(item.ListChilds.Where(ww => ww.Checked));
// _categoriesFactory.GetCategoryCheck(ref lstChildCheck, item.ListChilds);
// }
// _lstCateChecked.AddRange(lstChildCheck);
//}
#endregion End list categories old
#region List categories V1, updated 03212018, get all parent & child category selected
if (model.ListCategories != null && model.ListCategories.Any())
{
_categoriesFactory.GetCategoryCheck_V1(ref _lstCateChecked, model.ListCategories);
}
else
{
var listCategories = GetListCategories(model.Type == 1 ? model.ListCompanys : model.ListStores, model.Type);
_categoriesFactory.GetCategoryCheck_V1(ref _lstCateChecked, listCategories, true);
}
#endregion End list categories V1
/*ListSetMenu*/
if (model.ListStoreSetMenu != null)
{
foreach (var item in model.ListStoreSetMenu)
{
model.ListSetMenu.AddRange(item.ListSetMenuSel);
}
}
if (model.ListSetMenu != null && model.ListSetMenu.Count > 0)
{
_lstSetChecked = model.ListSetMenu.Where(ww => ww.Checked).ToList();
var lstSetChild = model.ListSetMenu.Where(ww => ww.ListChilds != null && ww.ListChilds.Count > 0).ToList();
foreach (var item in lstSetChild)
{
_lstSetChecked.AddRange(item.ListChilds.Where(ww => ww.Checked));
}
}
else
{
_lstSetChecked = GetListSetMenus(model.Type == 1 ? model.ListCompanys : model.ListStores, model.Type);
_lstSetChecked.ForEach(x =>
{
x.Checked = true;
if (x.ListChilds != null && x.ListChilds.Count > 0)
{
x.ListChilds.ForEach(z =>
{
z.Checked = true;
});
}
;
});
//=======
var lstSetChild = _lstSetChecked.Where(ww => ww.ListChilds != null && ww.ListChilds.Count > 0).ToList();
foreach (var item in lstSetChild)
{
_lstSetChecked.AddRange(item.ListChilds.Where(ww => ww.Checked));
}
}
if (_lstCateChecked == null)
{
_lstCateChecked = new List <RFilterCategoryModel>();
}
if (_lstSetChecked == null)
{
_lstSetChecked = new List <RFilterCategoryModel>();
}
if (!ModelState.IsValid)
{
Response.StatusCode = (int)HttpStatusCode.BadRequest;
return(View("Index", model));
}
var _lstCateCheckedId = _lstCateChecked.Select(ss => ss.Id).ToList();
var _lstSetCheckedId = _lstSetChecked.Select(ss => ss.Id).ToList();
// Get list stores selected
var _lstStoreCate = _lstCateChecked.Select(ss => ss.StoreId).Distinct().ToList();
var _lstStoreSet = _lstSetChecked.Select(ss => ss.StoreId).Distinct().ToList();
// Get ListStores, all stores selected from list cate, list set
var lstStoresCateSet = _lstStoreCate.Concat(_lstStoreSet).Distinct().ToList();
//Get Selected Store
List <StoreModels> lstStores = new List <StoreModels>();
//if (model.Type == Commons.TypeCompanySelected) //Company
//{
// lstStores = model.GetSelectedStoreCompany();
// lstStores = lstStores.Where(w => lstStoresCateSet.Contains(w.Id)).ToList();
// model.ListStores = lstStoresCateSet;
// //model.ListStores = lstStores.Select(ss => ss.Id).ToList();
//}
//else //Store
//{
// List<SelectListItem> vbStore = ViewBag.Stores;
// lstStores = model.GetSelectedStore(vbStore);
// lstStores = lstStores.Where(w => lstStoresCateSet.Contains(w.Id)).ToList();
// model.ListStores = lstStoresCateSet;
//}
///////======= Updated 072018
if (model.Type == Commons.TypeCompanySelected) //Company
{
lstStores = listStoresInfoSession.Where(ww => model.ListCompanys.Contains(ww.CompanyId)).ToList();
model.ListStores = lstStores.Select(ss => ss.Id).ToList();
}
else //Store
{
lstStores = listStoresInfoSession.Where(ww => model.ListStores.Contains(ww.Id)).ToList();
}
//End Get Selected Store
HourlyItemizedSalesReportFactory factory = new HourlyItemizedSalesReportFactory();
DateTime fromDate = model.FromDate;
DateTime toDate = model.ToDate;
TimeSpan fromTime = model.FromTime;
TimeSpan toTime = model.ToTime;
//model.FromDate = DateTimeHelper.SetFromDate(model.FromDate, fromTime);
//model.ToDate = DateTimeHelper.SetToDate(model.ToDate, toTime);
if (model.FromTime.Hours == 0 && model.FromTime.Minutes == 0 && model.ToTime.Hours == 0 && model.ToTime.Minutes == 0)
{
model.FromDate = new DateTime(model.FromDate.Year, model.FromDate.Month, model.FromDate.Day, 0, 0, 0);
model.ToDate = new DateTime(model.ToDate.Year, model.ToDate.Month, model.ToDate.Day, 23, 59, 59);
}
else
{
model.FromDate = new DateTime(model.FromDate.Year, model.FromDate.Month, model.FromDate.Day, model.FromTime.Hours, model.FromTime.Minutes, 0);
model.ToDate = new DateTime(model.ToDate.Year, model.ToDate.Month, model.ToDate.Day, model.ToTime.Hours, model.ToTime.Minutes, 59);
}
DateTime _dToFilter = model.ToDate;
XLWorkbook wb = new XLWorkbook();
//Get business day
var _lstBusDayAllStore = _baseFactory.GetBusinessDays(model.FromDate, model.ToDate, model.ListStores, model.Mode);
if (_lstBusDayAllStore == null || !_lstBusDayAllStore.Any())
{
IXLWorksheet ws = wb.Worksheets.Add("Hourly_Itemized_Sales_Report");
// Set header report
ReportFactory reportFactory = new ReportFactory();
reportFactory.CreateReportHeaderNew(ws, 8, model.FromDate, _dToFilter, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Hourly Itemized Sales Report").ToUpper());
// Format header report
ws.Range(1, 1, 4, 8).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 4, 8).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
else
{
model.ToDate = _lstBusDayAllStore.Max(aa => aa.DateTo);
#region Report with old DB
// Get list items by category, datetime, storeId
var listTemp = factory.GetDataHour(model, _lstStoreCate, _lstStoreSet, _lstCateCheckedId, _lstSetCheckedId);
// Get list MISC
DiscountAndMiscReportFactory miscFactory = new DiscountAndMiscReportFactory();
var listDiscountMisc = miscFactory.GetReceiptDiscountAndMiscByDateTime(model.FromDate, model.ToDate, model.ListStores, model.Mode);
// Export data
wb = factory.Export(listTemp, model.FromDate, _dToFilter,
_lstCateChecked, _lstSetChecked, listDiscountMisc, lstStores, model.Mode, _lstBusDayAllStore, model);
#endregion End report with old DB
#region Report with new DB
// Get list items by category, datetime, storeId
//var listTemp = factory.GetDataHour_NewDB(model, _lstStoreCate, _lstStoreSet, _lstCateCheckedId, _lstSetCheckedId);
// Get list MISC
//DiscountAndMiscReportFactory miscFactory = new DiscountAndMiscReportFactory();
//var listDiscountMisc = miscFactory.GetReceiptDiscountAndMiscByDateTime(model.FromDate, model.ToDate, model.ListStores, model.Mode);
// Export data
//wb = factory.Export_NewDB(listTemp, model.FromDate, _dToFilter,
// _lstCateChecked, _lstSetChecked, listDiscountMisc, lstStores, model.Mode, _lstBusDayAllStore);
#endregion End report with new DB
}
string sheetName = string.Format("Report_Hourly_Itemized_Sales_{0}", DateTime.Now.ToString("MMddyyyy")).Replace(" ", "_");
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
//HttpContext.Response.Clear();
Response.Charset = UTF8Encoding.UTF8.WebName;
Response.ContentEncoding = UTF8Encoding.UTF8;
if (model.FormatExport.Equals(Commons.Html))
{
Response.AddHeader("content-disposition", string.Format(@"attachment;filename={0}.html", sheetName));
}
else
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", string.Format(@"attachment;filename={0}.xlsx", sheetName));
}
using (var memoryStream = new System.IO.MemoryStream())
{
wb.SaveAs(memoryStream);
if (model.FormatExport.Equals(Commons.Html))
{
Workbook workbook = new Workbook();
workbook.LoadFromStream(memoryStream);
//convert Excel to HTML
Worksheet sheet = workbook.Worksheets[0];
using (var ms = new MemoryStream())
{
sheet.SaveToHtml(ms);
ms.WriteTo(HttpContext.Response.OutputStream);
ms.Close();
}
}
else
{
memoryStream.WriteTo(HttpContext.Response.OutputStream);
}
memoryStream.Close();
}
HttpContext.Response.End();
return(RedirectToAction("Index"));
}
catch (Exception ex)
{
_logger.Error("Hourly Itemized Sales Report Error: " + ex);
return(new HttpStatusCodeResult(400, ex.Message));
}
}
public List <UsageManagementModel> CalUsageManagement(UsageManagementRequest request)
{
var result = new List <UsageManagementModel>();
//List Sale
var lstSale = GetDataSale(request);
if (lstSale != null && lstSale.Count > 0)
{
//get all business day
var _lstBusDayAllStore = _baseFactory.GetBusinessDays(request.DateFrom, request.DateTo, request.StoreId, request.Mode);
var lstItemIdSale = lstSale.Select(ss => ss.ItemId).Distinct().ToList();
var lstRecipeItems = GetRecipeItem(request.StoreId);
var lstrecipeModifier = GetRecipeModifier(request.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;
_lstDetailTmp.Add(new ItemSaleForIngredientModel()
{
IngredientId = item.IngredientId,
ItemId = subItem.ItemId,
ItemName = subItem.ItemName,
BaseUsage = item.BaseUsage,
CreatedDate = subItem.CreatedDate,
Qty = subItem.Quantity
});
}
}
var lstUsageGroupIngredient = lstRecipeItems.GroupBy(gg => gg.IngredientId);
UsageManagementModel usageManagementModel = null;
UsageManagementDetailModel detail = null;
int indexPage = 1, indexChildPage = 1;
foreach (var item in lstUsageGroupIngredient)
{
usageManagementModel = new UsageManagementModel();
usageManagementModel.Index = indexPage;
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);
indexChildPage = 1;
foreach (var busDay in _lstBusDayAllStore)
{
var lstDish = _lstDetailTmp.Where(ww => ww.IngredientId == usageManagementModel.Id &&
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)
{
detail = new UsageManagementDetailModel();
detail.BusinessDay = busDay.DateDisplay;
detail.Index = indexChildPage;
detail.ItemName = dish.Select(ss => ss.ItemName).FirstOrDefault();
detail.Qty = dish.Sum(ss => ss.Qty);
detail.Usage = detail.Qty * dish.Select(ss => ss.BaseUsage).FirstOrDefault();
usageManagementModel.ListDetail.Add(detail);
indexChildPage++;
}
}
}
result.Add(usageManagementModel);
indexPage++;
}
}
}
return(result);
}
public ActionResult Report(ItemizedSalesAnalysisReportModel model)
{
try
{
var _lstCateChecked = new List <RFilterCategoryModel>();
var _lstSetChecked = new List <RFilterCategoryModel>();
model.FilterType = (int)Commons.EFilterType.OnDay;
if (model.EndTime.Hours == 0 && model.EndTime.Minutes == 0)
{
model.EndTime = new TimeSpan(23, 59, 59);
if (model.StartTime.Hours == 0 && model.StartTime.Minutes == 0)
{
model.FilterType = (int)Commons.EFilterType.None;
}
}
else if (model.StartTime > model.EndTime)
{
model.FilterType = (int)Commons.EFilterType.Days;
}
DateTime dFrom = new DateTime(model.FromDate.Year, model.FromDate.Month, model.FromDate.Day, model.StartTime.Hours, model.StartTime.Minutes, 0)
, dTo = new DateTime(model.ToDate.Year, model.ToDate.Month, model.ToDate.Day, model.EndTime.Hours, model.EndTime.Minutes, 0);
if (dFrom >= dTo)
{
ModelState.AddModelError("FromDate", CurrentUser.GetLanguageTextFromKey("From Date must be less than To Date."));
}
else
{
dTo = dTo.AddSeconds(59);
}
if (model.Type == Commons.TypeCompanySelected) //Company
{
if (model.ListCompanys == null)
{
ModelState.AddModelError("ListCompanys", CurrentUser.GetLanguageTextFromKey("Please choose company."));
}
}
else //Store
{
if (model.ListStores == null)
{
ModelState.AddModelError("ListStores", CurrentUser.GetLanguageTextFromKey("Please choose store."));
}
}
if (model.ListStoreCate != null)
{
model.ListCategories.AddRange(model.ListStoreCate.SelectMany(ss => ss.ListCategoriesSel).ToList());
}
//// Get list categories
#region List categories V1, updated 03212018, get all parent & child categories selected
if (model.ListCategories != null && model.ListCategories.Any())
{
_categoriesFactory.GetCategoryCheck_V1(ref _lstCateChecked, model.ListCategories);
}
else
{
var listCategories = GetListCategories(model.Type == 1 ? model.ListCompanys : model.ListStores, model.Type);
_categoriesFactory.GetCategoryCheck_V1(ref _lstCateChecked, listCategories, true);
}
#endregion End list categories V1, updated 03212018, get all parent & child categories selected
if (model.ListStoreSetMenu != null)
{
model.ListSetMenu.AddRange(model.ListStoreSetMenu.SelectMany(ss => ss.ListSetMenuSel).ToList());
}
if (model.ListSetMenu != null && model.ListSetMenu.Any())
{
_lstSetChecked = model.ListSetMenu.Where(ww => ww.Checked).ToList();
_lstSetChecked.AddRange(model.ListSetMenu.Where(ww => ww.ListChilds != null && ww.ListChilds.Any())
.SelectMany(ss => ss.ListChilds.Where(w => w.Checked).ToList()).ToList());
}
else
{
// Select all SetMenu
_lstSetChecked = GetListSetMenus(model.Type == 1 ? model.ListCompanys : model.ListStores, model.Type);
_lstSetChecked.AddRange(_lstSetChecked.Where(ww => ww.ListChilds != null && ww.ListChilds.Any())
.SelectMany(ss => ss.ListChilds).ToList());
}
if (_lstCateChecked == null)
{
_lstCateChecked = new List <RFilterCategoryModel>();
}
if (_lstSetChecked == null)
{
_lstSetChecked = new List <RFilterCategoryModel>();
}
if (!ModelState.IsValid)
{
Response.StatusCode = (int)HttpStatusCode.BadRequest;
return(View("Index", model));
}
var _lstCateCheckedId = _lstCateChecked.Select(ss => ss.Id).ToList();
var _lstSetCheckedId = _lstSetChecked.Select(ss => ss.Id).ToList();
// Get list store selected
var _lstStoreCate = _lstCateChecked.Select(ss => ss.StoreId).Distinct().ToList();
var _lstStoreSet = _lstSetChecked.Select(ss => ss.StoreId).Distinct().ToList();
var _lstStoresCateSet = _lstStoreCate.Concat(_lstStoreSet).Distinct().ToList();
//Get Selected Store
List <StoreModels> lstStores = new List <StoreModels>();
//if (model.Type == Commons.TypeCompanySelected) //Company
//{
// lstStores = model.GetSelectedStoreCompany();
// lstStores = lstStores.Where(ww => _lstStoresCateSet.Contains(ww.Id)).ToList();
// model.ListStores = _lstStoresCateSet;
//}
//else //Store
//{
// List<SelectListItem> vbStore = ViewBag.Stores;
// lstStores = model.GetSelectedStore(vbStore);
// lstStores = lstStores.Where(ww => _lstStoresCateSet.Contains(ww.Id)).ToList();
// model.ListStores = _lstStoresCateSet;
//}
///////======= Updated 072018
if (model.Type == Commons.TypeCompanySelected) //Company
{
lstStores = listStoresInfoSession.Where(ww => model.ListCompanys.Contains(ww.CompanyId)).ToList();
model.ListStores = lstStores.Select(ss => ss.Id).ToList();
}
else //Store
{
lstStores = listStoresInfoSession.Where(ww => model.ListStores.Contains(ww.Id)).ToList();
}
model.FromDate = new DateTime(model.FromDate.Year, model.FromDate.Month, model.FromDate.Day, 0, 0, 0);
model.ToDate = new DateTime(model.ToDate.Year, model.ToDate.Month, model.ToDate.Day, 23, 59, 59);
model.FromDateFilter = dFrom;
model.ToDateFilter = dTo;
BaseFactory _baseFactory = new BaseFactory();
ItemizedSalesAnalysisReportDetailFactory factory = new ItemizedSalesAnalysisReportDetailFactory();
var _lstBusDayAllStore = _baseFactory.GetBusinessDays(model.FromDate, model.ToDate, model.ListStores, model.Mode);
if (_lstBusDayAllStore != null && _lstBusDayAllStore.Any())
{
model.ToDate = _lstBusDayAllStore.Max(aa => aa.DateTo);
model.FromDate = _lstBusDayAllStore.Min(aa => aa.DateFrom);
var lstBusinessId = _lstBusDayAllStore.Select(ss => ss.Id).ToList();
// Get data
//#region DATA OF REPORT
//var lstItemizeds = factory.GetData(model.FromDate, model.ToDate, model.ListStores, _lstStoreCate, _lstStoreSet, _lstCateCheckedId, _lstSetCheckedId, model.Mode);
//if (lstItemizeds != null && lstItemizeds.Any())
//{
// switch (model.FilterType)
// {
// case (int)Commons.EFilterType.OnDay:
// lstItemizeds = lstItemizeds.Where(w => w.CreatedDate.TimeOfDay >= model.FromDateFilter.TimeOfDay && w.CreatedDate.TimeOfDay <= model.ToDateFilter.TimeOfDay).ToList();
// break;
// case (int)Commons.EFilterType.Days:
// lstItemizeds = lstItemizeds.Where(w => w.CreatedDate.TimeOfDay >= model.FromDateFilter.TimeOfDay || w.CreatedDate.TimeOfDay <= model.ToDateFilter.TimeOfDay).ToList();
// break;
// }
//}
//if(lstItemizeds == null)
//{
// lstItemizeds = new List<ItemizedSalesAnalysisReportDetailModels>();
//}
//DiscountAndMiscReportFactory miscFactory = new DiscountAndMiscReportFactory();
//var listMiscDiscount = miscFactory.GetReceiptDiscountAndMisc(model.FromDate, model.ToDate, model.ListStores, model.Mode, model.FromDateFilter, model.ToDateFilter, model.FilterType);
//if (listMiscDiscount == null)
//{
// listMiscDiscount = new List<DiscountAndMiscReportModels>();
//}
//listMiscDiscount.ForEach(ss => ss.DiscountValue = 0);
//#endregion
//DiscountDetailsReportFactory discountDetailFactory = new DiscountDetailsReportFactory();
//var lstDiscount = discountDetailFactory.GetDiscountTotal(model.ListStores, model.FromDate, model.ToDate, model.Mode, model.FromDateFilter, model.ToDateFilter, model.FilterType);
//if (lstDiscount != null && lstDiscount.Any())
//{
// listMiscDiscount.AddRange(lstDiscount);
//}
//Export excel
//XLWorkbook wb = factory.ExportExcel(lstItemizeds, model, lstStores, listMiscDiscount);
XLWorkbook wb = factory.ExportExcel_New(model, lstStores, _lstStoreCate, _lstStoreSet, _lstCateCheckedId, _lstSetCheckedId);
ViewBag.wb = wb;
string sheetName = string.Format("Report_Itemized_Sales_Detail_{0}", DateTime.Now.ToString("MMddyyyy")).Replace(" ", "_");
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
Response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
if (model.FormatExport.Equals(Commons.Html))
{
Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.html", sheetName));
}
else
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.xlsx", sheetName));
}
using (var memoryStream = new MemoryStream())
{
wb.SaveAs(memoryStream);
if (model.FormatExport.Equals(Commons.Html))
{
Workbook workbook = new Workbook();
workbook.LoadFromStream(memoryStream);
//convert Excel to HTML
Worksheet sheet = workbook.Worksheets[0];
using (var ms = new MemoryStream())
{
sheet.SaveToHtml(ms);
ms.WriteTo(HttpContext.Response.OutputStream);
ms.Close();
}
}
else
{
memoryStream.WriteTo(HttpContext.Response.OutputStream);
}
memoryStream.Close();
}
HttpContext.Response.End();
return(View("Index", model));
}
else
{
//Export excel
XLWorkbook wb = factory.ExportExcelEmpty(model);
//var ws = wb.Worksheets.Add("Itemized_Sales_Detail_Report");
ViewBag.wb = wb;
string sheetName = string.Format("Report_Itemized_Sales_Detail_{0}", DateTime.Now.ToString("MMddyyyy")).Replace(" ", "_");
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
Response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
if (model.FormatExport.Equals(Commons.Html))
{
Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.html", sheetName));
}
else
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.xlsx", sheetName));
}
using (var memoryStream = new MemoryStream())
{
wb.SaveAs(memoryStream);
if (model.FormatExport.Equals(Commons.Html))
{
Workbook workbook = new Workbook();
workbook.LoadFromStream(memoryStream);
//convert Excel to HTML
Worksheet sheet = workbook.Worksheets[0];
using (var ms = new MemoryStream())
{
sheet.SaveToHtml(ms);
ms.WriteTo(HttpContext.Response.OutputStream);
ms.Close();
}
}
else
{
memoryStream.WriteTo(HttpContext.Response.OutputStream);
}
memoryStream.Close();
}
HttpContext.Response.End();
return(View("Index", model));
}
}
catch (Exception ex)
{
_logger.Error("Itemized Sales Analysis Detail Report Error: " + ex);
return(new HttpStatusCodeResult(400, ex.Message));
}
}
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);
}
public XLWorkbook Report(BaseReportModel model, List <StoreModels> lstStore)
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Daily_Transactions_Report");
CreateReportHeaderNew(ws, 17, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Daily Transactions Report").ToUpper());
ws.Range(1, 1, 4, 17).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 4, 17).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 4, 17).Style.Border.InsideBorderColor = XLColor.FromHtml("#000000");
ws.Range(1, 1, 4, 17).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();
List <DailyTransactionsReportModels> lstDataInDate = null;
for (int d = 0; d < businessInStore.Count; d++)
{
//List<DailyTransactionsReportModels> lstDataInDate =
// ingredients.Where(m =>
// m.StartedOn >= businessInStore[d].DateFrom
// && m.ClosedOn <= businessInStore[d].DateTo
// && m.StoreId == storeId)
// .OrderBy(m => m.TypeName).ThenBy(m => m.IngredientName).ToList();
lstDataInDate =
ingredients.Where(m =>
m.BusinessId == businessInStore[d].Id
&& m.StoreId == storeId)
.OrderBy(m => m.TypeName).ThenBy(m => m.IngredientName).ToList();
listDataSumary.AddRange(lstDataInDate);
// Business day
if (lstDataInDate.Count > 0)
{
ws.Range(row, 1, row, 17).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Business day") + ": " + _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Open") + " " + businessInStore[d].DateFrom.ToString("MM/dd/yyyy - HH:mm") + " " + _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Close") + " " + businessInStore[d].DateTo.ToString("MM/dd/yyyy - HH:mm"));
ws.Range(row, 1, row, 17).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Range(row, 1, row++, 17).Style.Font.Bold = true;
int rowTbl = row;
for (int j = 0; j < lstDataInDate.Count; j++)
{
ws.Cell("A" + row).Value = "'" + lstDataInDate[j].IngredientCode;
ws.Cell("B" + row).Value = lstDataInDate[j].IngredientName;
ws.Cell("C" + row).Value = lstDataInDate[j].BaseUOMName;
ws.Cell("D" + row).Value = lstDataInDate[j].TypeName;
ws.Cell("E" + row).Value = lstDataInDate[j].OpenBal;
ws.Cell("F" + row).Value = lstDataInDate[j].Received;
ws.Cell("G" + row).Value = lstDataInDate[j].TransferIn;
ws.Cell("H" + row).Value = lstDataInDate[j].Return;
ws.Cell("I" + row).Value = lstDataInDate[j].TransferOut;
ws.Cell("J" + row).Value = lstDataInDate[j].Damage;
ws.Cell("K" + row).Value = lstDataInDate[j].Wast;
ws.Cell("L" + row).Value = lstDataInDate[j].Others;
ws.Cell("M" + row).Value = lstDataInDate[j].UseForSelfMade;
ws.Cell("N" + row).Value = lstDataInDate[j].Sales;
ws.Cell("O" + row).Value = lstDataInDate[j].AutoCloseBal;//auto
ws.Cell("P" + row).Value = lstDataInDate[j].CloseBal;
ws.Cell("Q" + row++).Value = lstDataInDate[j].AdjustValue;
}
ws.Range(rowTbl, 5, row - 1, 17).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Range(rowTbl, 5, row - 1, 17).Style.NumberFormat.Format = "#,##0.000";
}
}
}
ws.Range(startRow, 1, row - 1, 17).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(startRow, 1, row - 1, 17).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Range(startRow, 1, row - 1, 17).Style.Border.InsideBorderColor = XLColor.FromHtml("#000000");
ws.Range(startRow, 1, row - 1, 17).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, 25
};
for (int y = 0; y < lstWidCol.Count; y++)
{
ws.Column(y + 1).Width = lstWidCol[y];
}
}
return(wb);
}
