public List <DetailItemizedSalesAnalysisReportHeaderModels> GetData(BaseReportModel model, List <string> listCategoryIds)
{
using (var cxt = new NuWebContext())
{
var lstData = (from tb in cxt.R_DetailItemizedSalesAnalysisReportHeader
where model.ListStores.Contains(tb.StoreId) &&
listCategoryIds.Contains(tb.CategoryId) &&
(tb.CreatedDate >= model.FromDate && tb.CreatedDate <= model.ToDate)
orderby tb.CategoryId, tb.ItemId, tb.CreatedDate
group tb by new
{
StoreId = tb.StoreId,
CategoryId = tb.CategoryId,
CategoryName = tb.CategoryName,
ItemId = tb.ItemId,
ItemName = tb.ItemName,
CreatedDate = EntityFunctions.TruncateTime(tb.CreatedDate),
Price = tb.Price,
ItemTypeId = tb.ItemTypeId
} into g
select new DetailItemizedSalesAnalysisReportHeaderModels
{
StoreId = g.Key.StoreId,
CategoryId = g.Key.CategoryId,
CategoryName = g.Key.CategoryName,
ItemId = g.Key.ItemId,
ItemName = g.Key.ItemName,
CreatedDate = g.Key.CreatedDate.Value,
Qty = g.Sum(x => x.Qty),
Price = g.Max(x => x.Price),
ItemTypeId = g.Key.ItemTypeId
}).ToList();
return(lstData);
}
}
public List <RefundReportDTO> GetListRefundWithoutDetail(BaseReportModel model)
{
var result = new List <RefundReportDTO>();
using (var cxt = new NuWebContext())
{
result = (from r in cxt.R_Refund
where r.CreatedDate >= model.FromDate && r.CreatedDate <= model.ToDate && model.ListStores.Contains(r.StoreId)
//&& r.IsGiftCard.HasValue && r.IsGiftCard.Value
select new RefundReportDTO()
{
Id = r.Id,
BusinessDayId = r.BusinessDayId,
CreatedDate = r.CreatedDate,
Description = r.Description,
StoreId = r.StoreId,
TotalRefund = r.TotalRefund,
Promotion = r.Promotion,
ServiceCharged = r.ServiceCharged,
OrderId = r.OrderId,
IsGiftCard = r.IsGiftCard.HasValue ? r.IsGiftCard.Value : false,
}).ToList();
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="lstStoreIds"></param>
/// <param name="typeId">1: Company; 2: Store</param>
/// <returns></returns>
public ActionResult GetPaymentMethods(List <string> lstStoreIds, int typeId)
{
BaseReportModel model = new BaseReportModel();
model.ListPaymentMethod = GetListPaymentMethods(lstStoreIds, typeId);
/*Editor by Trongntn 10-07-2017*/
model.ListStorePay = model.ListPaymentMethod
.GroupBy(x => new { StoreId = x.StoreId, StoreName = x.StoreName })
.Select(x => new StorePay
{
StoreID = x.Key.StoreId,
StoreName = x.Key.StoreName,
ListPaymentMethodSel = new List <RFilterChooseExtBaseModel>()
}).ToList();
int OffSet = 0;
model.ListStorePay.ForEach(x =>
{
x.OffSet = OffSet++;
x.ListPaymentMethodSel = model.ListPaymentMethod.Where(z => z.StoreId.Equals(x.StoreID)).ToList();
});
return(PartialView("_FilterPaymentMethod", model));
}
public AcroFieldsWorksheet(BaseReportModel reportModel)
{
var acroFieldReportModel = (AcroFieldReportModel)reportModel;
sheetName = acroFieldReportModel.SheetName;
columnNames = CreateColumnNames();
acroFields = acroFieldReportModel.AcroFields;
}
// GET: AuditTrailReport
public ActionResult Index()
{
//string areas = ControllerContext.RouteData.DataTokens["area"].ToString();
BaseReportModel model = new BaseReportModel();
return(View(model));
}
public ComparePdfWorksheet(BaseReportModel reportModel)
{
this.reportModel = (ComparePdfReportModel)reportModel;
List <string> documentDates = this.reportModel.FileNameWithDate.Select(x => x.Item2).ToList();
sheetName = "ComparePdf";
columnNames = CreateColumnNames(documentDates);
}
public BaseReportModel <Q> MyPaging <T, Q>(IQueryable <T> source, int CurrentPageIndex, Func <T, Q> selectFunc)
{
var model = new BaseReportModel <Q>();
model.TotalCount = source.Count();
model.CurrentPageIndex = CurrentPageIndex;
model.PageCount = model.TotalCount / model.OnePageCount + (model.TotalCount % model.OnePageCount > 0 ? 1 : 0);
model.ItemList = source.Skip(model.CurrentPageIndex * model.OnePageCount).Take(model.PageCount).Select(selectFunc).AsEnumerable();
return(model);
}
public List <CashInOutReportModels> GetDataReport(BaseReportModel input, int typeCash)
{
var lstReturns = new List <CashInOutReportModels>();
//DateTimeHelper.GetDateTime(ref input);
var cxt = new NuWebContext();
var query = from data in cxt.R_CashInOutReport
//from store in cxt.G_Store.Where(ww => ww.Id == data.StoreId).DefaultIfEmpty()
where data.CreatedDate >= input.FromDate && data.CreatedDate <= input.ToDate &&
input.ListStores.Contains(data.StoreId) && data.CashType == typeCash &&
data.Mode == input.Mode
orderby data.CreatedDate
select new { data };
lstReturns = query
.Select(ss => new CashInOutReportModels()
{
StoreId = ss.data.StoreId,
//StoreName = ss.store != null ? ss.store.Name : "",
CreatedDate = ss.data.CreatedDate,
DrawerId = ss.data.DrawerId,
DrawerName = ss.data.DrawerName,
CashValue = ss.data.CashValue,
StartOn = ss.data.StartOn,
EndOn = ss.data.EndOn,
ShiftStartOn = ss.data.ShiftStartOn,
ShiftEndOn = ss.data.ShiftEndOn,
UserName = ss.data.UserName,
CashType = typeCash,
Reason = ss.data.Reason
}).ToList();
if (lstReturns != null && lstReturns.Any())
{
lstReturns.ForEach(o =>
{
if (!string.IsNullOrEmpty(o.Reason))
{
var _Split = o.Reason.Split('|').ToList();
if (_Split.Count > 0)
{
o.Reason = _Split[0];
}
if (_Split.Count > 1)
{
o.Remark = _Split[1];
}
}
});
}
return(lstReturns);
}
// GET: BaseReport
public ActionResult LoadEmployee(List <string> listData, int type)
{
BaseReportModel model = new BaseReportModel();
try
{
//UserFactory factory = new UserFactory();
//List<string> StoreId = listData;
//if (type == Commons.TypeCompanySelected)
//{
// StoreId = GetSelectedStoreIDCompany(listData);
//}
//List<UserModels> lstData = factory.GetAllEmployee(StoreId);
//if (lstData != null)
//{
// for (int i = 0; i < lstData.Count; i++)
// {
// model.ListEmployees.Add(new RPEmployeeItemModels()
// {
// ID = lstData[i].UserId,
// Name = lstData[i].Name,
// StoreId = lstData[i].StoreId,
// StoreName = lstData[i].StoreName
// });
// }
//}
model.ListEmployees = GetListEmployee(listData, type, false);
/*Editor by Trongntn 10-07-2017*/
model.ListStoreEmpl = model.ListEmployees
.GroupBy(x => new { StoreId = x.StoreId, StoreName = x.StoreName })
.Select(x => new StoreEmpl
{
StoreID = x.Key.StoreId,
StoreName = x.Key.StoreName,
ListEmployeesSel = new List <RPEmployeeItemModels>()
}).ToList();
int OffSet = 0;
model.ListStoreEmpl.ForEach(x =>
{
x.OffSet = OffSet++;
x.ListEmployeesSel = model.ListEmployees.Where(z => z.StoreId.Equals(x.StoreID)).ToList();
});
}
catch (Exception)
{
}
return(PartialView("_FilterEmployee", model));
}
public ActionResult Index(BaseReportModel model)
{
try
{
var recordsQuery = db.Records.Where(x => (x.Form.CampaignId == model.CampaignId || model.CampaignId == -1) && x.CreationDate >= model.StartDate && x.CreationDate <= model.EndDate && !x.IsDeleted);
var contactsQuery = db.Contacts.Where(x => (x.Project.CampaignId == model.CampaignId || model.CampaignId == -1) && x.CreationDate >= model.StartDate && x.CreationDate <= model.EndDate && !x.IsDeleted);
var totalSeconds = recordsQuery
.Sum(x => x.Duration);
var totalHours = decimal.Round((decimal)totalSeconds / 3600, 2);
var report = new DailyActivityReportViewModel
{
TotalHours = totalHours.ToString(),
TotalSuccesses = recordsQuery.Where(x => x.CallCode.IsSuccess).Count(),
TotalDials = recordsQuery.Count(),
ContactsCompleted = recordsQuery.Where(x => x.Contact.IsFinalized).Count(),
ContactsInCallBack = recordsQuery.Where(x => x.CallCode.Behavior == Utilities.EOCBehaviors.CallBack).Count(),
ContactsInHold = recordsQuery.Where(x => x.CallCode.Behavior == Utilities.EOCBehaviors.FinalizeRecord).Count(),
DialsPerSuccess = recordsQuery.Where(x => x.CallCode.IsSuccess).Count() == 0 ? 0 : recordsQuery.Count() / recordsQuery.Where(x => x.CallCode.IsSuccess).Count(),
TotalContacts = contactsQuery.Count(),
ContactRemaining = contactsQuery.Where(x => x.AgentId == null).Count(),
CallCodes = db.Records.Where(x => x.Form.CampaignId == model.CampaignId || model.CampaignId == -1).GroupBy(info => info.CallCode)
.Select(group => new CallCodeModel
{
Code = group.Key.Code,
Name = group.Key.Name,
Count = group.Count()
}).ToList(),
StartDate = model.StartDate,
EndDate = model.EndDate
};
var Campaigns = db.Campaigns.Where(x => !x.IsDeleted).ToList();
Campaigns.Insert(0, new Campaign {
Id = -1, Identifier = "All Campaigns"
});
ViewBag.CampaignId = new SelectList(Campaigns, "Id", "Identifier", model.CampaignId);
return(View(report));
}
catch (Exception ex)
{
AddAlert("Oops, Something Happened!", this.GetType().ToString(), "Index-Dashboard", AlertType.error, ex);
return(View());
}
}
public List <DetailItemizedSalesAnalysisReportHeaderModels> GetListCategory(BaseReportModel model)
{
using (var cxt = new NuWebContext())
{
var lstData = (from tb in cxt.R_DetailItemizedSalesAnalysisReportHeader
where model.ListStores.Contains(tb.StoreId)
group tb by new { CategoryId = tb.CategoryId, CategoryName = tb.CategoryName } into g
select new DetailItemizedSalesAnalysisReportHeaderModels
{
CategoryId = g.Key.CategoryId,
CategoryName = g.Key.CategoryName
}).ToList();
return(lstData);
}
}
public List <DiscountAndMiscReportModels> GetMiscs(BaseReportModel model)
{
using (var cxt = new NuWebContext())
{
//var lstData = new List<DiscountAndMiscReportModels>();
var lstData = (from m in cxt.R_DiscountAndMiscReport
where model.ListStores.Contains(m.StoreId) && m.CreatedDate >= model.FromDate && m.CreatedDate <= model.ToDate &&
m.Mode == model.Mode &&
m.MiscValue > 0
select new DiscountAndMiscReportModels
{
StoreId = m.StoreId,
CreatedDate = m.CreatedDate,
MiscValue = m.MiscValue
}).ToList();
return(lstData);
}
}
public List <PaymentModels> GetDataPaymentItemsByGCForHourlySale(BaseReportModel model, List <string> lstGCId)
{
using (var cxt = new NuWebContext())
{
var lstData = cxt.G_PaymentMenthod.Where(ww => model.ListStores.Contains(ww.StoreId) &&
(ww.CreatedDate >= model.FromDate &&
ww.CreatedDate <= model.ToDate) && ww.Mode == model.Mode &&
(lstGCId.Contains(ww.PaymentId) || ww.PaymentCode == (int)Commons.EPaymentCode.GiftCard))
.GroupBy(gg => new { StoreId = gg.StoreId, Time = (int?)SqlFunctions.DatePart("HH", gg.CreatedDate) })
.Select(tb => new PaymentModels
{
StoreId = tb.Key.StoreId,
Time = tb.Key.Time.HasValue ? tb.Key.Time.Value : 0,
Amount = tb.Sum(ss => ss.Amount)
}).ToList();
return(lstData);
}
}
public List <OrderTipModels> GetDataTips(BaseReportModel model)
{
using (var cxt = new NuWebContext())
{
var lstData = (from tb in cxt.G_OrderTip
where model.ListStores.Contains(tb.StoreId) &&
(tb.CreatedDate >= model.FromDate &&
tb.CreatedDate <= model.ToDate)
select new OrderTipModels
{
StoreId = tb.StoreId,
CreatedDate = tb.CreatedDate,
PaymentId = tb.PaymentId,
PaymentName = tb.PaymentName,
Amount = tb.Amount,
OrderId = tb.OrderId
}).ToList();
return(lstData);
}
}
public List <NoSaleDetailReportModels> GetData(BaseReportModel model, string StoreId)
{
using (NuWebContext cxt = new NuWebContext())
{
var lstData = (from tb in cxt.R_NoSaleDetailReport
where tb.StoreId == StoreId &&
(tb.CreatedDate >= model.FromDate && tb.CreatedDate <= model.ToDate)
orderby tb.CreatedDate
select new NoSaleDetailReportModels
{
CashierId = tb.CashierId,
CashierName = tb.CashierName,
CreatedDate = tb.CreatedDate,
DrawerId = tb.DrawerId,
DrawerName = tb.DrawerName,
Reason = tb.Reason,
StoreId = tb.StoreId,
}).ToList();
return(lstData);
}
}
public List <PaymentModels> GetDataPaymentItemsByGC(BaseReportModel model, List <string> lstGCId)
{
using (var cxt = new NuWebContext())
{
var lstData = (from tb in cxt.G_PaymentMenthod
where model.ListStores.Contains(tb.StoreId) &&
(tb.CreatedDate >= model.FromDate && tb.CreatedDate <= model.ToDate) &&
tb.Mode == model.Mode &&
(lstGCId.Contains(tb.PaymentId) || tb.PaymentCode == (int)Commons.EPaymentCode.GiftCard)
//&& (tb.IsInclude ==null ||(tb.IsInclude.HasValue && tb.IsInclude.Value))
select new PaymentModels
{
StoreId = tb.StoreId,
CreatedDate = tb.CreatedDate,
PaymentId = tb.PaymentId,
PaymentName = tb.PaymentName,
Amount = tb.Amount,
OrderId = tb.OrderId,
IsInclude = tb.IsInclude
}).ToList();
return(lstData);
}
}
public ActionResult GetBaseReport(BaseReportModel model)
{
StatementSearchCriteria criteria = new StatementSearchCriteria();
criteria.CountView = null;
criteria.CreateDateFrom = model.DateBaseReportFrom;
criteria.CreateDateTo = model.DateBaseReportTo.AddDays(1);
criteria.LastStatusDateFrom = model.BaseReportStatusDateFrom;
if (model.BaseReportStatusDateTo.HasValue)
{
criteria.LastStatusDateTo = model.BaseReportStatusDateTo.Value.AddDays(1);
}
criteria.LastStatementStatusID = model.StatusID;
List <Entities.Report.BaseReport> list = reportBusinessLogic.GetBaseReport(criteria);
List <ReferenceItem> listSubjectInsurance = ReferencesProvider.GetReferenceItems(Constants.RefSubjectInsurance);
List <ReferenceItem> listStatus = ReferencesProvider.GetReferenceItems(Constants.RefStatus);
List <User> listUser = userBusinessLogic.Find(" ");
PrintedForms.BaseReport printedForm = new PrintedForms.BaseReport(list, listSubjectInsurance, listStatus, listUser);
return(File(printedForm.GetExcel(),
System.Net.Mime.MediaTypeNames.Application.Octet, "Отчет по обращениям.xlsx"));
}
public JobCardViewView(BaseReportModel baseReportModel) : base(baseReportModel)
{
}
public ActionResult Report(BaseReportModel reportModel)
{
try
{
if (reportModel.FromDate > reportModel.ToDate)
{
ModelState.AddModelError("FromDate", CurrentUser.GetLanguageTextFromKey("From Date must be less than To Date."));
}
else if (reportModel.Type == Commons.TypeCompanySelected) //Company
{
if (reportModel.ListCompanys == null)
{
ModelState.AddModelError("ListCompanys", CurrentUser.GetLanguageTextFromKey("Please choose company."));
}
}
else //Store
{
if (reportModel.ListStores == null)
{
ModelState.AddModelError("ListStores", CurrentUser.GetLanguageTextFromKey("Please choose store."));
}
}
if (!ModelState.IsValid)
{
return(View("Index", reportModel));
}
//Get Selected Store
List <StoreModels> lstStore = new List <StoreModels>();
////if (reportModel.Type == Commons.TypeCompanySelected) //Company
////{
//// lstStore = reportModel.GetSelectedStoreCompany();
////}
////else //Store
////{
//// List<SelectListItem> vbStore = ViewBag.Stores;
//// lstStore = reportModel.GetSelectedStore(vbStore);
////}
///////======= Updated 072018
if (reportModel.Type == Commons.TypeCompanySelected) //Company
{
lstStore = listStoresInfoSession.Where(ww => reportModel.ListCompanys.Contains(ww.CompanyId)).ToList();
reportModel.ListStores = lstStore.Select(ss => ss.Id).ToList();
}
else //Store
{
lstStore = listStoresInfoSession.Where(ww => reportModel.ListStores.Contains(ww.Id)).ToList();
}
//End Get Selected Store
DetailItemizedSalesAnalysisReportHeaderFactory factory = new DetailItemizedSalesAnalysisReportHeaderFactory();
DateTimeHelper.GetDateTime(ref reportModel);
var categorys = factory.GetListCategory(reportModel);
List <string> listCategoryIds = (from c in categorys select c.CategoryId).ToList <string>();
var data = factory.GetData(reportModel, listCategoryIds);
List <DateTime> listDates = new List <DateTime>();
foreach (var item in data)
{
listDates.Add(item.CreatedDate);
}
//List<DetailItemizedModel> listStores = new List<DetailItemizedModel>();
//#region add dish to listStore
//foreach (var item in listDishTemp)
//{
// listDates.Add(item.DateCreated.Value);
// DetailItemizedModel valueCompare = new DetailItemizedModel()
// {
// DateCreated = item.DateCreated.Value,
// StoreIndex = item.StoreIndex,
// StoreName = item.StoreName,
// CateID = item.CategoryID,
// CateName = item.CatName,
// ItemID = item.DishID,
// ItemName = item.DishName,
// TypeID = 1,
// ItemQuantity = (item.DishQuantity == null) ? 0 : item.DishQuantity.Value,
// ItemPrice = item.DishPrice
// };
// int indexStore = IsExistItem(listStores, valueCompare, "store");
// if (indexStore == -1)
// {
// #region add new store
// DetailItemizedModel storeItem = new DetailItemizedModel()
// {
// StoreIndex = item.StoreIndex,
// StoreName = item.StoreName,
// ListChilds = new List<DetailItemizedModel>()
// };
// //add new category
// DetailItemizedModel cateItem = new DetailItemizedModel()
// {
// CateID = item.CategoryID,
// CateName = item.CatName,
// TypeID = 1,
// ListChilds = new List<DetailItemizedModel>()
// };
// //add new dish or setmenu
// DetailItemizedModel dishItem = new DetailItemizedModel()
// {
// ItemID = item.DishID,
// ItemName = item.DishName,
// ItemPrice = item.DishPrice,
// TypeID = valueCompare.TypeID,
// ListChilds = new List<DetailItemizedModel>(),
// ListSetChilds = new List<DetailItemizedModel>()
// };
// //add new date - quantity
// dishItem.ListChilds.Add(new DetailItemizedModel()
// {
// DateCreated = item.DateCreated.Value,
// ItemQuantity = item.DishQuantity.Value
// });
// cateItem.ListChilds.Add(dishItem);
// storeItem.ListChilds.Add(cateItem);
// listStores.Add(storeItem);
// #endregion add new store
// }
// else
// {
// int indexCate = IsExistItem(listStores[indexStore].ListChilds, valueCompare, "category");
// if (indexCate == -1)
// {
// #region add new category.
// //add new category
// DetailItemizedModel cateItem = new DetailItemizedModel()
// {
// CateID = item.CategoryID,
// CateName = item.CatName,
// TypeID = 1,
// ListChilds = new List<DetailItemizedModel>()
// };
// //add new dish or setmenu
// DetailItemizedModel dishItem = new DetailItemizedModel()
// {
// ItemID = item.DishID,
// ItemName = item.DishName,
// ItemPrice = item.DishPrice,
// TypeID = valueCompare.TypeID,
// ListChilds = new List<DetailItemizedModel>(),
// ListSetChilds = new List<DetailItemizedModel>()
// };
// //add new date - quantity
// dishItem.ListChilds.Add(new DetailItemizedModel()
// {
// DateCreated = item.DateCreated.Value,
// ItemQuantity = item.DishQuantity.Value
// });
// cateItem.ListChilds.Add(dishItem);
// listStores[indexStore].ListChilds.Add(cateItem);
// #endregion
// }
// else
// {
// int indexDishMenu = IsExistItem(listStores[indexStore].ListChilds[indexCate].ListChilds, valueCompare, "dishsetmenu");
// if (indexDishMenu == -1)
// {
// #region add new dish or setmenu.
// DetailItemizedModel dishItem = new DetailItemizedModel()
// {
// ItemID = item.DishID,
// ItemName = item.DishName,
// ItemPrice = item.DishPrice,
// TypeID = valueCompare.TypeID,
// ListChilds = new List<DetailItemizedModel>(),
// ListSetChilds = new List<DetailItemizedModel>()
// };
// //add new date - quantity
// dishItem.ListChilds.Add(new DetailItemizedModel()
// {
// DateCreated = item.DateCreated.Value,
// ItemQuantity = item.DishQuantity.Value
// });
// listStores[indexStore].ListChilds[indexCate].ListChilds.Add(dishItem);
// #endregion
// }
// else
// {
// //add new date - quantity
// listStores[indexStore].ListChilds[indexCate].ListChilds[indexDishMenu].ListChilds.Add(new DetailItemizedModel()
// {
// DateCreated = item.DateCreated.Value,
// ItemQuantity = item.DishQuantity.Value
// });
// }
// }
// }
//}
//#endregion
//#region add setmenu to listStore
//foreach (var item in querySetMenus)
//{
// listDates.Add(item.DateCreated.Value);
// DetailItemizedModel valueCompare = new DetailItemizedModel()
// {
// DateCreated = item.DateCreated.Value,
// StoreIndex = item.StoreIndex,
// StoreName = item.StoreName,
// CateID = "SetMenu",
// CateName = "SetMenu",
// ItemID = item.SetID,
// ItemName = item.SetName,
// TypeID = 4,
// ItemQuantity = item.SetQuantity.Value,
// ItemPrice = item.SetPrice.Value
// };
// int indexStore = IsExistItem(listStores, valueCompare, "store");
// //if store is not exist => add new store
// if (indexStore == -1)
// {
// #region add new store
// DetailItemizedModel storeItem = new DetailItemizedModel()
// {
// StoreIndex = item.StoreIndex,
// StoreName = item.StoreName,
// ListChilds = new List<DetailItemizedModel>()
// };
// //add new category
// DetailItemizedModel cateItem = new DetailItemizedModel()
// {
// CateID = "SetMenu",
// CateName = "SetMenu",
// TypeID = 4,
// ListChilds = new List<DetailItemizedModel>()
// };
// //add new dish or setmenu
// DetailItemizedModel setmenuItem = new DetailItemizedModel()
// {
// ItemID = item.SetID,
// ItemName = item.SetName,
// ItemPrice = item.SetPrice.Value,
// TypeID = valueCompare.TypeID,
// ListChilds = new List<DetailItemizedModel>(),
// ListSetChilds = new List<DetailItemizedModel>()
// };
// //add new date - quantity
// DetailItemizedModel dishItem = new DetailItemizedModel()
// {
// DateCreated = item.DateCreated.Value,
// ItemQuantity = item.SetQuantity.Value
// };
// dishItem.ReceiptDetailIDs.Add(item.ReceiptDetailID);
// setmenuItem.ListChilds.Add(dishItem);
// cateItem.ListChilds.Add(setmenuItem);
// storeItem.ListChilds.Add(cateItem);
// listStores.Add(storeItem);
// #endregion add new store
// }
// else
// {
// int indexCate = IsExistItem(listStores[indexStore].ListChilds, valueCompare, "category");
// //if category is not exist => add new category
// if (indexCate == -1)
// {
// #region add new category
// //add new category
// DetailItemizedModel cateItem = new DetailItemizedModel()
// {
// CateID = "SetMenu",
// CateName = "SetMenu",
// TypeID = 4,
// ListChilds = new List<DetailItemizedModel>()
// };
// //add new dish or setmenu
// DetailItemizedModel setmenuItem = new DetailItemizedModel()
// {
// ItemID = item.SetID,
// ItemName = item.SetName,
// ItemPrice = item.SetPrice.Value,
// TypeID = valueCompare.TypeID,
// ListChilds = new List<DetailItemizedModel>(),
// ListSetChilds = new List<DetailItemizedModel>()
// };
// //add new date - quantity
// DetailItemizedModel dishItem = new DetailItemizedModel()
// {
// DateCreated = item.DateCreated.Value,
// ItemQuantity = item.SetQuantity.Value
// };
// dishItem.ReceiptDetailIDs.Add(item.ReceiptDetailID);
// setmenuItem.ListChilds.Add(dishItem);
// cateItem.ListChilds.Add(setmenuItem);
// listStores[indexStore].ListChilds.Add(cateItem);
// #endregion
// }
// else
// {
// int indexSetMenu = IsExistItem(listStores[indexStore].ListChilds[indexCate].ListChilds, valueCompare, "dishsetmenu");
// if (indexSetMenu == -1)
// {
// #region add new dish or setmenu.
// DetailItemizedModel setmenuItem = new DetailItemizedModel()
// {
// ItemID = item.SetID,
// ItemName = item.SetName,
// ItemPrice = item.SetPrice.Value,
// TypeID = valueCompare.TypeID,
// ListChilds = new List<DetailItemizedModel>(),
// ListSetChilds = new List<DetailItemizedModel>()
// };
// //add new date - quantity
// DetailItemizedModel dishItem = new DetailItemizedModel()
// {
// DateCreated = item.DateCreated.Value,
// ItemQuantity = item.SetQuantity.Value
// };
// dishItem.ReceiptDetailIDs.Add(item.ReceiptDetailID);
// setmenuItem.ListChilds.Add(dishItem);
// listStores[indexStore].ListChilds[indexCate].ListChilds.Add(setmenuItem);
// #endregion
// }
// else
// {
// int indexDate = IsExistItem(listStores[indexStore].ListChilds[indexCate].ListChilds[indexSetMenu].ListChilds, valueCompare, "date");
// if (indexDate == -1)
// {
// //add new date - quantity
// DetailItemizedModel dishItem = new DetailItemizedModel()
// {
// DateCreated = item.DateCreated.Value,
// ItemQuantity = item.SetQuantity.Value
// };
// dishItem.ReceiptDetailIDs.Add(item.ReceiptDetailID);
// listStores[indexStore].ListChilds[indexCate].ListChilds[indexSetMenu].ListChilds.Add(dishItem);
// }
// else
// {
// if (listStores[indexStore].ListChilds[indexCate].ListChilds[indexSetMenu].ListChilds[indexDate].ReceiptDetailIDs.IndexOf(item.ReceiptDetailID) < 0)
// {
// listStores[indexStore].ListChilds[indexCate].ListChilds[indexSetMenu].ListChilds[indexDate].ItemQuantity += item.SetQuantity.Value;
// listStores[indexStore].ListChilds[indexCate].ListChilds[indexSetMenu].ListChilds[indexDate].ReceiptDetailIDs.Add(item.ReceiptDetailID);
// }
// }
// }
// }
// }
//}
//#endregion
//#region add dish to setmenu of listStore
//foreach (var item in querySetMenus)
//{
// DetailItemizedModel valueCompare = new DetailItemizedModel()
// {
// DateCreated = item.DateCreated.HasValue ? item.DateCreated.Value : DateTime.MinValue,
// StoreIndex = item.StoreIndex,
// StoreName = item.StoreName,
// CateID = "SetMenu",
// CateName = "SetMenu",
// ItemID = item.SetID,
// ItemName = item.SetName,
// TypeID = 4,
// ItemQuantity = item.DishQuantity.HasValue ? item.DishQuantity.Value : 0,
// ItemPrice = item.SetPrice.HasValue ? item.SetPrice.Value : 0
// };
// DetailItemizedModel valueCompareDish = new DetailItemizedModel()
// {
// DateCreated = item.DateCreated.HasValue ? item.DateCreated.Value : DateTime.MinValue,
// StoreIndex = item.StoreIndex,
// StoreName = item.StoreName,
// ItemID = item.DishID,
// ItemName = item.DishName,
// TypeID = 41,
// ItemQuantity = item.DishQuantity.HasValue ? item.DishQuantity.Value : 0,
// ItemPrice = item.DishPrice
// };
// int indexStore = IsExistItem(listStores, valueCompare, "store");
// int indexCate = IsExistItem(listStores[indexStore].ListChilds, valueCompare, "category");
// int indexDishMenu = IsExistItem(listStores[indexStore].ListChilds[indexCate].ListChilds, valueCompare, "dishsetmenu");
// int indexSetChild = IsExistItem(listStores[indexStore].ListChilds[indexCate].ListChilds[indexDishMenu].ListSetChilds, valueCompareDish, "setchild");
// if (indexSetChild == -1)
// {
// //add new setmenu child
// listStores[indexStore].ListChilds[indexCate].ListChilds[indexDishMenu].ListSetChilds.Add(new DetailItemizedModel()
// {
// CateID = item.CatID,//
// CateName = item.CatName,//
// ItemID = item.DishID,
// ItemName = item.DishName,
// ItemPrice = item.DishPrice,
// TypeID = valueCompareDish.TypeID,
// ListChilds = new List<DetailItemizedModel>()
// });
// //add new date - quantity
// listStores[indexStore].ListChilds[indexCate].ListChilds[indexDishMenu].ListSetChilds.Last().ListChilds.Add(new DetailItemizedModel()
// {
// DateCreated = item.DateCreated.HasValue ? item.DateCreated.Value : DateTime.MinValue,
// ItemQuantity = item.DishQuantity.HasValue ? item.DishQuantity.Value : 0
// });
// }
// else
// {
// //add new date - quantity
// DetailItemizedModel child = listStores[indexStore].ListChilds[indexCate].ListChilds[indexDishMenu].ListSetChilds[indexSetChild].ListChilds.Where(c => c.DateCreated == item.DateCreated).FirstOrDefault();
// if (child != null)
// child.ItemQuantity += item.DishQuantity.HasValue ? item.DishQuantity.Value : 0;
// else
// listStores[indexStore].ListChilds[indexCate].ListChilds[indexDishMenu].ListSetChilds[indexSetChild].ListChilds.Add(new DetailItemizedModel()
// {
// DateCreated = item.DateCreated.HasValue ? item.DateCreated.Value : DateTime.MinValue,
// ItemQuantity = item.DishQuantity.HasValue ? item.DishQuantity.Value : 0
// });
// }
//}
//#endregion
listDates = listDates.Distinct().Select(item => item).ToList();
listDates = listDates.OrderBy(item => item).ToList();
XLWorkbook wb = factory.ExportExcel(reportModel, listDates, data, lstStore);
string sheetName = string.Format("Report_Detail_Itemized_Sales_Analysis_{0}", DateTime.Now.ToString("MMddyyyy")).Replace(" ", "_");
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
//HttpContext.Response.Clear();
Response.Charset = System.Text.Encoding.UTF8.WebName;
Response.ContentEncoding = System.Text.Encoding.UTF8;
if (reportModel.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 (reportModel.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("Detail Itemized Sales Analysis Report Error: " + ex);
return(new HttpStatusCodeResult(400, ex.Message));
}
}
// GET: DetailItemizedSalesAnalysisReport
public ActionResult Index()
{
BaseReportModel model = new BaseReportModel();
return(View(model));
}
public ActionResult Report(BaseReportModel model)
{
try
{
if (model.FromDate > model.ToDate)
{
ModelState.AddModelError("FromDate", CurrentUser.GetLanguageTextFromKey("From Date must be less than To Date."));
}
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."));
}
}
//if (model.ListPaymentMethod == null)
//{
// ModelState.AddModelError("ListPaymentMethod", "Please choose payment method.");
//}
//else
//{
// var lstPayments = model.ListPaymentMethod.Select(ss => ss.Checked).ToList();
// if (lstPayments == null || lstPayments.Count == 0)
// {
// ModelState.AddModelError("ListPaymentMethod", "Please choose payment method.");
// }
//}
/*Editor by Trongntn 10-07-2017*/
if (model.ListStorePay != null)
{
foreach (var item in model.ListStorePay)
{
foreach (var itemPay in item.ListPaymentMethodSel)
{
RFilterChooseExtBaseModel payment = new RFilterChooseExtBaseModel();
payment.Checked = itemPay.Checked;
payment.Id = itemPay.Id;
payment.Name = itemPay.Name;
payment.Code = itemPay.Code;
payment.StoreId = itemPay.StoreId;
payment.StoreName = itemPay.StoreName;
payment.ListChilds = itemPay.ListChilds;
model.ListPaymentMethod.Add(payment);
}
}
}
if (model.ListPaymentMethod == null || !model.ListPaymentMethod.Any())
{
model.ListPaymentMethod = GetListPaymentMethods(model.Type == 1 ? model.ListCompanys : model.ListStores, model.Type);
model.ListPaymentMethod.ForEach(x =>
{
x.Checked = true;
if (x.ListChilds != null && x.ListChilds.Count > 0)
{
x.ListChilds.ForEach(z =>
{
z.Checked = true;
});
}
;
});
}
if (!ModelState.IsValid)
{
return(View("Index", model));
}
//Get Selected Store
List <StoreModels> lstStore = new List <StoreModels>();
//if (model.Type == Commons.TypeCompanySelected) //Company
//{
// lstStore = model.GetSelectedStoreCompany();
// model.ListStores = lstStore.Select(ss => ss.Id).ToList();
// //filter Check
// var lstStoreId = model.ListPaymentMethod.Where(ww=>ww.Checked).Select(ss => ss.StoreId).Distinct().ToList();
// if (lstStoreId != null && lstStoreId.Any())
// {
// lstStore = lstStore.Where(ww => lstStoreId.Contains(ww.Id)).ToList();
// model.ListStores = model.ListStores.Where(ww => lstStoreId.Contains(ww)).ToList();
// }
//}
//else //Store
//{
// List<SelectListItem> vbStore = ViewBag.Stores;
// lstStore = model.GetSelectedStore(vbStore);
//}
///////======= Updated 072018
if (model.Type == Commons.TypeCompanySelected) //Company
{
lstStore = listStoresInfoSession.Where(ww => model.ListCompanys.Contains(ww.CompanyId)).ToList();
model.ListStores = lstStore.Select(ss => ss.Id).ToList();
}
else //Store
{
lstStore = listStoresInfoSession.Where(ww => model.ListStores.Contains(ww.Id)).ToList();
}
//filter with list payment method
var lstStoreId = model.ListPaymentMethod.Where(ww => ww.Checked).Select(ss => ss.StoreId).Distinct().ToList();
if (lstStoreId != null && lstStoreId.Any())
{
lstStore = lstStore.Where(ww => lstStoreId.Contains(ww.Id)).ToList();
model.ListStores = model.ListStores.Where(ww => lstStoreId.Contains(ww)).ToList();
}
//End Get Selected Store
ReceiptsbyPaymentMethodsReportFactory factory = new ReceiptsbyPaymentMethodsReportFactory();
DateTimeHelper.GetDateTime(ref model);
//Export excel
#region Report old DB
XLWorkbook wb = factory.ExportExcel(model, lstStore);
//Report with Credit Note
//XLWorkbook wb = factory.ExportExcel_WithCreditNote(model, lstStore);
#endregion
#region Report new DB
//XLWorkbook wb = factory.ExportExcel_NewDB(model, lstStore);
#endregion
ViewBag.wb = wb;
string sheetName = string.Format("Report_Receipts_By_Payment_Method_{0}", DateTime.Now.ToString("MMddyyyy"));
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
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.Replace(" ", "_")));
}
else
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.xlsx", sheetName.Replace(" ", "_")));
}
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("Receipt By Payment Report Error: " + ex);
return(new HttpStatusCodeResult(400, ex.Message));
}
}
public XLWorkbook Report(BaseReportModel viewmodel, List <CategoryOnStore> ListCatgoryOnstoreChoose, List <Modifier> ListModifierChoose)
{
string sheetName = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("ModifiersviewReport");
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add(sheetName);
var totalheader = (ListModifierChoose.Count * 2) + 2;
var totalModifier = ListModifierChoose.Count * 2;
//Format Header
CreateReportHeaderNew(ws, totalheader, viewmodel.FromDateFilter, viewmodel.ToDateFilter, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Detail Itemized Sales Analysis Report"));
ws.Range(5, 1, 6, 1).Merge(true).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Item ID"));
ws.Range(5, 2, 6, 2).Merge(true).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Dishes"));
int columnStart = 3;
int columnEnd = 4;
foreach (var item in ListModifierChoose)
{
ws.Range(5, columnStart, 5, columnEnd).Merge(true).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey(item.ModifierName.ToString()));
ws.Column(columnStart).Width = 25;
ws.Column(columnEnd).Width = 25;
ws.Cell(6, columnStart).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Quantity"));
ws.Cell(6, columnEnd).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Amount"));
columnStart += 2;
columnEnd += 2;
}
ws.Column(1).Width = 20;
ws.Column(2).Width = 30;
ws.Range(5, 1, 6, totalheader).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range(5, 1, 6, totalheader).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Range(5, 1, 6, totalheader).Style.Font.SetBold(true);
ws.Row(5).Height = 60;
// end format
int row = 6;
List <SumAmount> ListSumAmount = new List <SumAmount>();
SumAmount sumAmount = null;
//body
foreach (var itemStore in ListCatgoryOnstoreChoose)
{
ws.Range(++row, 1, row, totalheader).Merge(true).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey(itemStore.StoreName)).Style.Fill.SetBackgroundColor(XLColor.FromHtml(Commons.BgColorStore)).Font.SetBold(true);;
foreach (var itemCategory in itemStore.ListCategory)
{
ws.Range(++row, 1, row, totalheader).Merge(true).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey(itemCategory.CategoryName)).Style.Fill.SetBackgroundColor(XLColor.FromHtml(Commons.BgColorHeader)).Font.SetBold(true);;
foreach (var itemDish in itemCategory.ListDish)
{
row++;
ws.Range(row, 1, row, 1).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey(itemDish.DishId));
ws.Range(row, 2, row, 2).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey(itemDish.DishName));
ws.Cell(row, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
int cols = totalheader - 2;
foreach (var item in itemDish.ListModifierOnDish)
{
cols--;
ws.Cell(row, totalheader - cols).SetValue(item.DishQuantity).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
cols--;
ws.Cell(row, totalheader - cols).SetValue(item.DishAmount).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
sumAmount = new SumAmount();
sumAmount.ModifierId = item.ModifierId;
sumAmount.TotalAmount += item.DishAmount;
ListSumAmount.Add(sumAmount);
}
}
}
}
var lstGroupAmount = ListSumAmount.GroupBy(x => x.ModifierId);
List <SumAmount> ListSumAllAmount = new List <SumAmount>();
SumAmount sumAllAmounts = null;
foreach (var item in lstGroupAmount)
{
sumAllAmounts = new SumAmount();
sumAllAmounts.ModifierId = item.Key;
sumAllAmounts.TotalAmount = item.Select(x => x.TotalAmount).Sum();
ListSumAllAmount.Add(sumAllAmounts);
}
//Total
columnStart = 3;
columnEnd = columnStart + 1;
ws.Range(++row, 1, row, 2).Merge(true).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("TOTAL")).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
for (int i = 0; i < ListSumAllAmount.Count; i++)
{
ws.Range(row, columnStart, row, columnEnd).Merge().SetValue(Math.Round(ListSumAllAmount[i].TotalAmount, 2)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
columnStart += 2;
columnEnd += 2;
}
//format body
ws.Range(1, 1, row, totalheader).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, row, totalheader).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Range(row, 1, row, totalheader).Style.Font.SetBold(true);
//endbody
return(wb);
}
public PrintedFormsModel()
{
BaseReport = new BaseReportModel();
}
public VendReportView(BaseReportModel baseReportModel) : base(baseReportModel)
{
}
public XLWorkbook ExportExcel(BaseReportModel model, List <DateTime> listDates, List <DetailItemizedSalesAnalysisReportHeaderModels> data, List <StoreModels> lstStore)
{
XLWorkbook wb = new XLWorkbook();
XLColor backgroundTitle = XLColor.FromHtml("#d9d9d9");
XLColor outsideBorderColor = XLColor.FromHtml("#000000");
XLColor insideBorderColor = XLColor.FromHtml("#000000");
//Create worksheet
IXLWorksheet ws = wb.Worksheets.Add("Detail_Itemized_Sales_Analysis" /*_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Detail_Itemized_Sales_Analysis")*/);
int startRow = 3;
int row = startRow;
int maxCol = 3 + listDates.Count;
int startCol = 4;
List <int> listCenterStyles = new List <int>();
listCenterStyles.Add(1);
CreateReportHeader(ws, maxCol, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Detail Itemized Sales Analysis"));
//List<DetailItemizedSalesAnalysisReportHeaderModels> listStores = (from s in data
// group s by s.StoreId into g
// select new DetailItemizedSalesAnalysisReportHeaderModels
// {
// StoreId = g.Key,
// }).ToList();
if (model.ListStores.Count == 0)
{
ws.Cell("A4").Value = string.Format(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("NO DATA"));
return(wb);
}
//table
row++;
ws.Cell("A" + row).Value = string.Format(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Item Name"));
ws.Cell("B" + row).Value = string.Format(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Total"));
ws.Cell("C" + row).Value = string.Format(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Price"));
ws.Range(row, 1, row, maxCol).Style.Fill.BackgroundColor = backgroundTitle;
ws.Range(row, 1, row, maxCol).Style.Font.SetBold(true);
ws.Range(row, 1, row, maxCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
for (int i = 0; i < listDates.Count; i++)
{
ws.Cell(row, 4 + i).SetValue(listDates[i]);
ws.Cell(row, 4 + i).Style.DateFormat.Format = "mm/dd";
}
row++;
string storeName = string.Empty, storeId = string.Empty;
//table content
foreach (StoreModels store in lstStore)
{
//store name
storeName = store.Name;
storeId = store.Id;
//category name
List <DetailItemizedSalesAnalysisReportHeaderModels> ListChilds = (from c in data
where c.StoreId == storeId
orderby c.ItemTypeId, c.CategoryName
group c by new
{
CategoryId = c.CategoryId,
CategoryName = c.CategoryName,
ItemTypeId = c.ItemTypeId // trongntn
} into g
select new DetailItemizedSalesAnalysisReportHeaderModels
{
CategoryId = g.Key.CategoryId,
CategoryName = g.Key.CategoryName,
ItemTypeId = g.Key.ItemTypeId
}).ToList();
if (ListChilds.Count > 0)
{
ws.Range(row, 1, row, maxCol).Merge().Value = string.Format("{0}", storeName);
ws.Range(row, 1, row, maxCol).Style.Font.SetBold(true);
ws.Range(row, 1, row, maxCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
row++;
}
foreach (DetailItemizedSalesAnalysisReportHeaderModels cate in ListChilds /*store.ListChilds.OrderBy(c => c.TypeID).ThenBy(c => c.CateName)*/)
{
if (cate.ItemTypeId != 4) // Category for Dish
{
ws.Range(row, 1, row, maxCol).Merge().Value = string.Format(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Category") + ": {0}", cate.CategoryName);
ws.Range(row, 1, row, maxCol).Style.Font.SetBold(true);
ws.Range(row, 1, row, maxCol).Style.Fill.BackgroundColor = XLColor.FromHtml("#d9d9d9");
row++;
}
//=====
List <DetailItemizedSalesAnalysisReportHeaderModels> ListdisSet = (from ds in data
where ds.StoreId == storeId && ds.CategoryId == cate.CategoryId
orderby ds.ItemTypeId, ds.ItemName
group ds by new
{
ItemId = ds.ItemId,
ItemName = ds.ItemName,
Price = ds.Price,
ItemTypeId = ds.ItemTypeId
} into g
select new DetailItemizedSalesAnalysisReportHeaderModels
{
ItemId = g.Key.ItemId,
ItemName = g.Key.ItemName,
Qty = g.Sum(x => x.Qty),
Price = g.Key.Price,
ItemTypeId = g.Key.ItemTypeId
}).ToList();
foreach (DetailItemizedSalesAnalysisReportHeaderModels disSet in ListdisSet /*cate.ListChilds.OrderBy(c => c.TypeID).ThenBy(c => c.ItemName)*/)
{
//dish or setmenu data
if (disSet.ItemTypeId == 4)
{
//set font and background color for row setmenu
row++;
ws.Row(row).Style.Font.SetFontColor(XLColor.Red);
ws.Range(row, 1, row, maxCol).Style.Fill.BackgroundColor = XLColor.FromHtml("#d9d9d9");
}
//item name
ws.Cell(row, 1).Value = string.Format("{0}", disSet.ItemName);
ws.Range(row, 1, row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
//item price
ws.Cell(row, 3).SetValue(string.Format("${0:0.00}", disSet.Price));
ws.Range(row, 3, row, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
double total = 0;
//quantity
List <DetailItemizedSalesAnalysisReportHeaderModels> ListQty = (from q in data
where q.StoreId == storeId &&
q.CategoryId == cate.CategoryId &&
q.ItemId == disSet.ItemId &&
q.Price == disSet.Price
orderby q.CreatedDate
select new DetailItemizedSalesAnalysisReportHeaderModels
{
CreatedDate = q.CreatedDate,
Qty = q.Qty
}).ToList();
foreach (DetailItemizedSalesAnalysisReportHeaderModels dateQty in ListQty /*disSet.ListChilds.OrderBy(item => item.DateCreated)*/)
{
ws.Cell(row, startCol + GetDateIndex(listDates, dateQty.CreatedDate)).Value = string.Format("{0}", dateQty.Qty);
ws.Range(row, 1, row, startCol + GetDateIndex(listDates, dateQty.CreatedDate)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
total += dateQty.Qty;
}
//total
ws.Cell(row, 2).Value = string.Format("{0}", total);
ws.Range(row, 1, row, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
row++;
//if set menu has child -> setmenu child
List <DetailItemizedSalesAnalysisReportHeaderModels> ListSetChilds = (from sc in data
where sc.StoreId == storeId &&
sc.CategoryId == cate.CategoryId &&
sc.ItemId == disSet.ItemId &&
sc.ItemTypeId == 4
orderby sc.CategoryName, sc.ItemName
select new DetailItemizedSalesAnalysisReportHeaderModels
{
ItemName = sc.ItemName,
Price = sc.Price
}).ToList();
if (disSet.ItemTypeId == 4 && /*disSet.ListSetChilds*/ ListSetChilds != null && /*disSet.ListSetChilds.Count*/ ListSetChilds.Count > 0)
{
foreach (DetailItemizedSalesAnalysisReportHeaderModels setChild in ListSetChilds /*disSet.ListSetChilds.OrderBy(c => c.CateName).ThenBy(c => c.ItemName)*/)
{
ws.Cell(row, 1).Value = string.Format("{0}", setChild.ItemName);
ws.Cell(row, 3).SetValue(string.Format("${0:0.00}", setChild.Price));
total = 0;
//=====
List <DetailItemizedSalesAnalysisReportHeaderModels> scListChilds = (from c in data
where c.StoreId == storeId &&
c.CategoryId == cate.CategoryId &&
c.ItemId == disSet.ItemId &&
c.ItemName == setChild.ItemName && //
c.ItemTypeId == 4
orderby c.CreatedDate
select new DetailItemizedSalesAnalysisReportHeaderModels
{
CreatedDate = c.CreatedDate,
Qty = c.Qty
}).ToList();
foreach (DetailItemizedSalesAnalysisReportHeaderModels dateQty in scListChilds /*setChild.ListChilds.OrderBy(item => item.DateCreated)*/)
{
ws.Cell(row, startCol + GetDateIndex(listDates, dateQty.CreatedDate)).Value = string.Format("{0}", dateQty.Qty);
total += dateQty.Qty;
}
ws.Cell(row, 2).Value = string.Format("{0}", total);
ws.Range(row, 1, row, maxCol).Style.Fill.BackgroundColor = XLColor.FromHtml("#d9d9d9");
row++;
}
}
}
}
}
ws.Range(startRow, 1, row - 1, maxCol).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(startRow, 1, row - 1, maxCol).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Range(startRow, 1, row - 1, maxCol).Style.Border.OutsideBorderColor = outsideBorderColor;
ws.Range(startRow, 1, row - 1, maxCol).Style.Border.InsideBorderColor = insideBorderColor;
//set width of cells
ws.Column(1).Width = 35;
ws.Columns(2, maxCol).Width = 10;
return(wb);
}
public OrgReportView(BaseReportModel baseReportModel) : base(baseReportModel)
{
}
public ActionResult Report(BaseReportModel model)
{
try
{
if (model.FromDate > model.ToDate)
{
ModelState.AddModelError("FromDate", CurrentUser.GetLanguageTextFromKey("From Date must be less than To Date."));
}
//if (model.ListEmployees.All(x => x.Checked == false) || model.ListEmployees.Count() == 0)
//{
// ModelState.AddModelError("ListEmployees", "Please choose employee.");
// return View("Index", model);
//}
//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."));
}
}
if (!ModelState.IsValid)
{
return(View("Index", model));
}
/*Editor by Trongntn 10-07-2017*/
if (model.ListStoreEmpl != null)
{
foreach (var item in model.ListStoreEmpl)
{
foreach (var itemEmpl in item.ListEmployeesSel)
{
model.ListEmployees.Add(new RPEmployeeItemModels
{
Checked = itemEmpl.Checked,
ID = itemEmpl.ID,
Name = itemEmpl.Name,
StoreId = itemEmpl.StoreId,
StoreName = itemEmpl.StoreName
});
}
}
}
if (model.ListEmployees.All(x => x.Checked == false) || model.ListEmployees.Count() == 0)
{
model.ListEmployees = _BaseReportController.GetListEmployee(model.Type == 1 ? model.ListCompanys : model.ListStores, model.Type, true);
}
//Get Selected Store
List <StoreModels> lstStore = new List <StoreModels>();
//if (model.Type == Commons.TypeCompanySelected) //Company
//{
// lstStore = model.GetSelectedStoreCompany();
// model.ListStores = lstStore.Select(ss => ss.Id).ToList();
//}
//else //Store
//{
// List<SelectListItem> vbStore = ViewBag.Stores;
// lstStore = model.GetSelectedStore(vbStore);
//}
///////======= Updated 072018
if (model.Type == Commons.TypeCompanySelected) //Company
{
lstStore = listStoresInfoSession.Where(ww => model.ListCompanys.Contains(ww.CompanyId)).ToList();
model.ListStores = lstStore.Select(ss => ss.Id).ToList();
}
else //Store
{
lstStore = listStoresInfoSession.Where(ww => model.ListStores.Contains(ww.Id)).ToList();
}
//End Get Selected Store
TimeClockReportFactory factory = new TimeClockReportFactory();
XLWorkbook wb = factory.ExportExcelSummary(model, lstStore);
ViewBag.wb = wb;
string sheetName = string.Format("Report_Time_Clock_Summary_{0}", DateTime.Now.ToString("MMddyyyy"));
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.Replace(" ", "_")));
}
else
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.xlsx", sheetName.Replace(" ", "_")));
}
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("Time Clock Summary Report Error: " + ex);
return(new HttpStatusCodeResult(400, ex.Message));
}
}
public ActionResult Report(BaseReportModel model)
{
try
{
DailyReceiptReportFactory factory = new DailyReceiptReportFactory();
if (model.FromDate > model.ToDate)
{
ModelState.AddModelError("FromDate", CurrentUser.GetLanguageTextFromKey("From Date must be less than To Date."));
}
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."));
}
}
if (!ModelState.IsValid)
{
return(View("Index", model));
}
//Get Selected Store
List <StoreModels> lstStore = new List <StoreModels>();
////if (model.Type == Commons.TypeCompanySelected) //Company
////{
//// lstStore = model.GetSelectedStoreCompany();
//// model.ListStores = lstStore.Select(ss => ss.Id).ToList();
////}
////else //Store
////{
//// List<SelectListItem> vbStore = ViewBag.Stores;
//// lstStore = model.GetSelectedStore(vbStore);
////}
///////======= Updated 072018
if (model.Type == Commons.TypeCompanySelected) //Company
{
lstStore = listStoresInfoSession.Where(ww => model.ListCompanys.Contains(ww.CompanyId)).ToList();
model.ListStores = lstStore.Select(ss => ss.Id).ToList();
}
else //Store
{
lstStore = listStoresInfoSession.Where(ww => model.ListStores.Contains(ww.Id)).ToList();
}
//End Get Selected Store
//Get data by filter search
DateTimeHelper.GetDateTime(ref model);
//Export excel
XLWorkbook wb = factory.ExportExcel_New(model, lstStore);
//Updated 03302018, Report with refund gift card
//XLWorkbook wb = factory.ExportExcel_WithCreditNote(model, lstStore);
// Report with new DB
//XLWorkbook wb = factory.ExportExcel_NewDB(model, lstStore);
ViewBag.wb = wb;
string sheetName = string.Format("Report_Daily_Receipt_{0}", DateTime.Now.ToString("MMddyyyy"));
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.Charset = System.Text.Encoding.UTF8.WebName;
Response.ContentEncoding = System.Text.Encoding.UTF8;
if (model.FormatExport.Equals(Commons.Html))
{
Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.html", sheetName.Replace(" ", "_")));
}
else
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.xlsx", sheetName.Replace(" ", "_")));
}
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("Daily Receipt Report Error: " + ex);
return(new HttpStatusCodeResult(400, ex.Message));
}
}
public ConReportView(BaseReportModel baseReportModel) : base(baseReportModel)
{
}
public ScrReportView(BaseReportModel baseReportModel) : base(baseReportModel)
{
}