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 PushDataToXero(UsageManagementRequest model)
{
//var result = new List<UsageManagementModel>();
try
{
var result = _usageManagementFactory.PushDataToXero(model);
//var result = true;
if (result)
{
//TempData["Success"] = "Added Successfully!";
return(Json(new { success = true, responseText = "Your data successfully sent!" }, JsonRequestBehavior.AllowGet));
//return View(model);
}
//return Content("Data added successfully");
else
{
//return Content ("Push data failed");
return(Json(new { success = false, responseText = "Your data is push failed!" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception e)
{
_logger.Error(e);
//return new HttpStatusCodeResult(400, e.Message);
return(Json(new { success = false, responseText = "Your data is push failed!" }, JsonRequestBehavior.AllowGet));
}
}
//For Old
public UsageManagementModel CalUsageManagementByIngredient(UsageManagementRequest request, string ingredientId)
{
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);
UsageManagementModel usageManagementModel = new 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, ingredientId);
var lstrecipeModifier = GetRecipeModifier(request.StoreId, ingredientId);
lstRecipeItems.AddRange(lstrecipeModifier);
List <ItemSaleForIngredientModel> _lstDetailTmp = new List <ItemSaleForIngredientModel>();
if (lstRecipeItems != null && lstRecipeItems.Count > 0)
{
lstRecipeItems = lstRecipeItems.Where(ww => lstItemIdSale.Contains(ww.ItemId)).ToList();
var lstItemIdInrecipe = lstRecipeItems.Select(ss => ss.ItemId).Distinct().ToList();
//Group item sale
var lstItemSaleHaveRecipe = lstSale.Where(ww => lstItemIdInrecipe.Contains(ww.ItemId)).ToList();
if (lstItemSaleHaveRecipe != null && lstItemSaleHaveRecipe.Count > 0)
{
UsageManagementDetailModel detail = null;
int indexChildPage = 1;
foreach (var busDay in _lstBusDayAllStore)
{
var lstDish = lstItemSaleHaveRecipe.Where(ww => ww.CreatedDate >= busDay.DateFrom && ww.CreatedDate <= busDay.DateTo).ToList();
if (lstDish != null && lstDish.Count > 0)
{
var lstDishGroup = lstDish.GroupBy(gg => gg.ItemId);
foreach (var dish in lstDishGroup)
{
var recipe = lstRecipeItems.Where(ww => ww.ItemId == dish.Key).FirstOrDefault();
if (recipe != null)
{
detail = new UsageManagementDetailModel();
detail.BusinessDay = busDay.DateDisplay;
detail.Index = indexChildPage;
detail.ItemName = dish.Select(ss => ss.ItemName).FirstOrDefault();
detail.Qty = dish.Sum(ss => ss.Quantity);
detail.Usage = detail.Qty * recipe.BaseUsage;
usageManagementModel.ListDetail.Add(detail);
indexChildPage++;
}
}
}
}
}
}
}
return(usageManagementModel);
}
// GET: IngUsageManagement
public ActionResult Index()
{
UsageManagementRequest model = new UsageManagementRequest();
model.DateFrom = DateTime.Now;
model.DateTo = DateTime.Now;
ViewBag.ListStore = GetListStore();
return(View(model));
}
public bool PushDataToXero(UsageManagementRequest request)
{
bool result = true;
if (IsPush(request.DateTo, request.StoreId))
{
//List<UsageManagementModel> lstCalResult = CalUsageManagementwithoutDetail(request);
List <UsageManagementModel> lstCalResult = GetUsageManagement(request);
//lstCalResult.Add(new UsageManagementModel()
//{
// Code = "008",
// Usage = 2
//});
try
{
IngredientSyncRequestDTO model = new IngredientSyncRequestDTO();
model.AppRegistrationId = Commons.XeroRegistrationAppId;
model.AccessToken = Commons.XeroAccessToken;
model.StoreId = request.StoreId;
var lstIngredient = _ingredientFactory.GetIngredient(null);
if (lstIngredient != null && lstIngredient.Count > 0)
{
lstIngredient = lstIngredient.Where(ww => !string.IsNullOrEmpty(ww.XeroId)).ToList();
if (lstIngredient != null && lstIngredient.Count > 0)
{
foreach (var item in lstCalResult)
{
var obj = lstIngredient.Where(ww => ww.Code.ToUpper().Equals(item.Code.ToUpper())).FirstOrDefault();
if (obj != null)
{
model.Items.Add(new IngredientUsageSyncItem()
{
Id = obj.XeroId,
Code = obj.Code,
QuantityUsed = (decimal)item.Usage
});
}
}
if (model.Items != null && model.Items.Count > 0)
{
result = XeroFactory.SyncIngredientsUsageToXero(request.DateTo, request.StoreId, model).Result;
}
}
}
}
catch (Exception ex)
{
result = false;
_logger.Error(ex);
}
}
return(result);
}
//For Old not use
public List <UsageManagementModel> CalUsageManagementwithoutDetail(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);
//List Sale
var lstSale = GetDataSale(request);
if (lstSale != null && lstSale.Count > 0)
{
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;
}
}
var lstUsageGroupIngredient = lstRecipeItems.GroupBy(gg => gg.IngredientId);
UsageManagementModel usageManagementModel = null;
int indexPage = 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);
result.Add(usageManagementModel);
indexPage++;
}
}
}
result = result.Where(ww => ww.Usage > 0).ToList();
return(result);
}
public bool PushDataToXero(UsageManagementRequest request, List <UsageManagementModel> lstUsages)
{
bool result = true;
//if (IsPush(request.DateTo, request.StoreId))
//{
//List<UsageManagementModel> lstCalResult = CalUsageManagementwithoutDetail(request);
try
{
IngredientSyncRequestDTO model = new IngredientSyncRequestDTO();
model.AppRegistrationId = Commons.XeroRegistrationAppId;
model.AccessToken = Commons.XeroAccessToken;
model.StoreId = request.StoreId;
var lstIngredient = XeroFactory.GetIngredientsFromXero(new Models.Xero.XeroBaseModel()
{
AppRegistrationId = Commons.XeroRegistrationAppId,
AccessToken = Commons.XeroAccessToken,
StoreId = request.StoreId
}).Result;
if (lstIngredient.Success)
{
foreach (var item in lstUsages)
{
var obj = lstIngredient.Data.Items.Where(ww => ww.Code.ToUpper().Equals(item.Code.ToUpper())).FirstOrDefault();
if (obj != null)
{
model.Items.Add(new IngredientUsageSyncItem()
{
Id = obj.ID,
Code = obj.Code,
QuantityUsed = (decimal)item.Usage
});
}
}
if (model.Items != null && model.Items.Count > 0)
{
NSLog.Logger.Info("PushDataToXero Data", model.Items);
result = XeroFactory.SyncIngredientsUsageToXero(request.DateTo, request.StoreId, model).Result;
}
}
}
catch (Exception ex)
{
result = false;
_logger.Error(ex);
}
//}
return(result);
}
public ActionResult Search(UsageManagementRequest model)
{
var result = new List <UsageManagementModel>();
try
{
result = _usageManagementFactory.GetUsageManagement(model);
}
catch (Exception e)
{
_logger.Error(e);
return(new HttpStatusCodeResult(400, e.Message));
}
return(PartialView("_ListData", result));
}
private List <StockUsageModel> GetDataSale(UsageManagementRequest request)
{
using (var cxt = new NuWebContext())
{
var lstData = (from s in cxt.I_StockUsage
where s.StoreId == request.StoreId && s.CreatedDate >= request.DateFrom && s.CreatedDate <= request.DateTo
//group s by new{ s.ItemId, s.ItemName} into st
select new StockUsageModel
{
ItemId = s.ItemId,
ItemName = s.ItemName,
Quantity = s.Quantity,
CreatedDate = s.CreatedDate
}).ToList();
return(lstData);
}
}
public ActionResult Export(UsageManagementRequest model)
{
try
{
//IngredientModel model = new IngredientModel();
XLWorkbook wb = new XLWorkbook();
var wsdata = wb.Worksheets.Add("Usage");
var data = _stockUsageFactory.Export(ref wsdata, model);
if (!data.IsOk)
{
ModelState.AddModelError("Usage", data.Message);
return(View(data));
}
ViewBag.wb = wb;
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.Charset = UTF8Encoding.UTF8.WebName;
Response.ContentEncoding = UTF8Encoding.UTF8;
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string fileName = CommonHelper.GetExportFileName("UsageManagement").Replace(" ", "_");
Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.xlsx", fileName));
using (var memoryStream = new System.IO.MemoryStream())
{
wb.SaveAs(memoryStream);
memoryStream.WriteTo(HttpContext.Response.OutputStream);
memoryStream.Close();
}
HttpContext.Response.End();
ViewBag.IsSuccess = true;
return(View(model));
}
catch (Exception ex)
{
_logger.Error(ex);
return(new HttpStatusCodeResult(400, ex.Message));
}
}
public ResultModels Export(ref IXLWorksheet wsexcel, UsageManagementRequest request)
{
var result = new ResultModels();
try
{
using (var cxt = new NuWebContext())
{
wsexcel.Cell("A" + 1).Value = "Usage Management";
wsexcel.Row(1).Style.Font.SetBold(true);
wsexcel.Row(1).Height = 25;
wsexcel.Range(1, 1, 1, 5).Merge();
//Date
string date = string.Format("Date: {0}", request.DateFrom.ToString("MM/dd/yyyy"));
if (request.DateFrom.Date != request.DateTo.Date)
{
date = string.Format("Date from {0} to {1}", request.DateFrom.ToString("MM/dd/yyyy"), request.DateTo.ToString("MM/dd/yyyy"));
}
wsexcel.Cell("A" + 2).Value = date;
wsexcel.Row(2).Style.Font.SetBold(true);
wsexcel.Row(2).Height = 16;
wsexcel.Range(2, 1, 2, 5).Merge();
string[] lstHeaders = new string[] {
"Index", "Ingredient Code", "Ingredient Name", "Base UOMs", "Usage"
};
int row = 3;
//Add header to excel
for (int i = 1; i <= lstHeaders.Length; i++)
{
wsexcel.Cell(row, i).Value = lstHeaders[i - 1];
wsexcel.Row(row).Style.Font.SetBold(true);
}
int cols = lstHeaders.Length;
row = 4;
//Get list data
var lstData = _usageManagementFactory.GetUsageManagement(request); //CalUsageManagementwithoutDetail(request);
if (lstData != null && lstData.Count > 0)
{
foreach (var item in lstData)
{
wsexcel.Cell("A" + row).Value = item.Index;
wsexcel.Cell("B" + row).Value = item.Code;
wsexcel.Cell("C" + row).Value = item.Name;
wsexcel.Cell("D" + row).Value = item.UOMName;
wsexcel.Cell("E" + row).Value = item.Usage;
row++;
}
}
wsexcel.Range("E2", "E" + row).Style.NumberFormat.Format = "#,##0.0000";
BaseFactory.FormatExcelExport(wsexcel, row, cols);
result.IsOk = true;
}
}
catch (Exception ex)
{
result.IsOk = false;
result.Message = ex.Message;
_logger.Error(ex);
}
return(result);
}
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);
}