public virtual List <StoreModels> GetStoresByName(string name)//店铺查询 { string sqlStr = "select * from m_store where s_storeCName like '%" + name + "%' or s_storeEName like '%" + name + "%' "; DataTable dt = SqlSeverProvider.ExecuteQuery(sqlStr); List <StoreModels> listStores = new List <StoreModels>(); StoreModels store = null; if (dt != null) { foreach (DataRow row in dt.Rows) { store = new StoreModels { storeID = (int)row["s_storeId"], areaKeyID = (int)row["s_areaKeyId"], storeCName = (string)(row["s_storeCName"] == DBNull.Value ? string.Empty : row["s_storeCName"]), storeEName = (string)(row["s_storeEName"] == DBNull.Value ? string.Empty : row["s_storeEName"]), brandsID = (int)row["s_brandsId"], address = (string)(row["s_address"] == DBNull.Value ? string.Empty : row["s_address"]), phoneNumber = (string)(row["s_phoneNumber"] == DBNull.Value ? string.Empty : row["s_phoneNumber"]), arrivalNum = (int)row["s_arrivalNum"] }; listStores.Add(store); } return(listStores); } return(null); }
public virtual StoreModels GetById(int id) { string sqlStr = "select * from m_store where s_storeId='" + id + "'"; SqlDataReader dr = SqlSeverProvider.ExecuteReader(sqlStr); StoreModels store = null; if (dr.Read()) { store = new StoreModels { storeID = dr.GetInt32(0), areaKeyID = dr.GetInt32(1), storeCName = dr.GetString(2), storeEName = dr.GetString(3), categoryID = dr.GetInt32(4), logo = dr.GetString(5), brandsID = dr.GetInt32(6), address = dr.GetString(7), phoneNumber = dr.GetString(8), arrivalNum = dr.GetInt32(9) }; dr.Close(); return(store); } return(null); }
//Edit existing store public async Task <StoreModels> editStore(StoreModels store) { _db.Entry(store).State = EntityState.Modified; await _db.SaveChangesAsync(); return(store); }
/// <summary> /// 分类查询商铺 /// </summary> /// <param name="cateID"></param> /// <param name="tradeAreaId"></param> /// <returns></returns> public virtual List <StoreModels> GetStoresByCategory(int cateID, int tradeAreaId) { string sqlStr = "dbo.dz_Store_GetSpecialStore"; SqlParameter[] parm = { new SqlParameter("@TradeAreaId", tradeAreaId), new SqlParameter("@CategoryId", cateID) }; DataTable dt = SqlSeverProvider.ExecuteQuery(sqlStr, CommandType.StoredProcedure, parm); List <StoreModels> listStores = new List <StoreModels>(); StoreModels store = null; if (dt != null) { foreach (DataRow row in dt.Rows) { store = new StoreModels { storeID = (int)row["s_storeId"], areaKeyID = (int)row["s_areaKeyId"], storeCName = (string)(row["s_storeCName"] == DBNull.Value ? string.Empty : row["s_storeCName"]), storeEName = (string)(row["s_storeEName"] == DBNull.Value ? string.Empty : row["s_storeEName"]), brandsID = (int)row["s_brandsId"], address = (string)(row["s_address"] == DBNull.Value ? string.Empty : row["s_address"]), phoneNumber = (string)(row["s_phoneNumber"] == DBNull.Value ? string.Empty : row["s_phoneNumber"]), arrivalNum = (int)row["s_arrivalNum"] }; listStores.Add(store); } return(listStores); } return(null); }
public async Task <ActionResult> Create(StoreModels store) { try { if (ModelState.IsValid) { //Add new store await storeRepository.createStore(store); //Add date on last input var storeLast = await storeRepository.result(); storeLast.Date = DateTime.Now; storeRepository.SaveData(); //Create new log await storeRepository.log(storeLast.Name, storeLast.Date, storeLast.Location); return(RedirectToAction("Index")); } return(View(store)); } catch (Exception e) { Console.WriteLine("{0} Exception caught.", e); } return(View()); }
//Create new store public async Task <StoreModels> createStore(StoreModels store) { _db.StoreModels.Add(store); await _db.SaveChangesAsync(); return(store); }
// GET: Store public ActionResult Index() { var store = new StoreModels(); var result = store.GetList(); return(View(result)); }
public virtual void Add(StoreModels store) { string sqlStr = string.Format("insert into m_store values({0},'{1}','{2}',{3},'{4}',{5},'{6}','{7}',{8})", store.areaKeyID, store.storeCName, store.storeEName, store.categoryID, store.logo, store.brandsID, store.address, store.phoneNumber, store.arrivalNum); SqlSeverProvider.ExecuteNonQuery(sqlStr); }
public ActionResult DeleteConfirmed(int id) { StoreModels storeModels = db.StoreModels.Find(id); db.StoreModels.Remove(storeModels); db.SaveChanges(); return(RedirectToAction("Index")); }
//Delete some store public async Task <StoreModels> deleteStore(int?id) { StoreModels store = await _db.StoreModels.FindAsync(id); _db.StoreModels.Remove(store); await _db.SaveChangesAsync(); return(store); }
public ActionResult AddStore() { _stores = new Stores(); StoreModels store = new StoreModels(); store.IntervalList = GenerateSelectListItems(intervalsList, ""); store.OrderStatus = _stores.GetPublicOrdersStatus(); return(View(store)); }
public ActionResult Edit([Bind(Include = "Id,city,address,zipcode,geoLat,getLong")] StoreModels storeModels) { if (ModelState.IsValid) { db.Entry(storeModels).State = EntityState.Modified; db.SaveChanges(); return(RedirectToAction("Index")); } return(View(storeModels)); }
public ActionResult Create([Bind(Include = "Id,city,address,zipcode,geoLat,getLong")] StoreModels storeModels) { if (ModelState.IsValid) { db.StoreModels.Add(storeModels); db.SaveChanges(); return(RedirectToAction("Index")); } return(View(storeModels)); }
//Search and Paging public async Task <object> pageCount(int pageSize, StoreModels store) { int pageCount = store.Child.Count(); int pages = pageCount / pageSize; //ViewBag.pageCount = pages; int rest = pageCount % pageSize; if (rest < 10) { pages = pages + 1; ViewBag.pageCount = pages; } return(ViewBag.pageCount); }
public ActionResult EditStore(string id) { _stores = new Stores(); StoreDto dto = _stores.GetStoreById(int.Parse(id)); if (dto != null) { StoreModels model = new StoreModels(); model.StoreId = int.Parse(id); model.ApiKey = dto.ApiKey; model.StoreName = dto.Name; model.Url = dto.Url; model.IntervalList = GenerateSelectListItems(intervalsList, dto.Interval.ToString()); model.OrderStatusIds = _stores.GetAllPublicOrderStatusByStore(dto.Id); model.CustomOrderStatus = _stores.GetNonPublicOrderStatusByStore(dto.Id); List <CustomShipmentsDto> list = _stores.GetCustomShipmentByStoreId(dto.Id); model.CustomShipments = list; model.OrderStatus = _stores.GetPublicOrdersStatus(); foreach (var orde in model.OrderStatusIds) { model.listOfIds += orde + ","; } if (model.listOfIds != null && !model.listOfIds.Equals("")) { model.listOfIds = model.listOfIds.Remove(model.listOfIds.Count() - 1); } int counter = 0; foreach (var customShipmentsDto in list) { if (counter == 0) { model.CustomShip1 = customShipmentsDto.Name; } if (counter == 1) { model.CustomShip2 = customShipmentsDto.Name; } if (counter == 2) { model.CustomShip3 = customShipmentsDto.Name; } counter++; } return(View(model)); } return(RedirectToAction("Index", "Store")); }
// GET: Store/Delete/5 public ActionResult Delete(int?id) { if (id == null) { return(new HttpStatusCodeResult(HttpStatusCode.BadRequest)); } StoreModels storeModels = db.StoreModels.Find(id); if (storeModels == null) { return(HttpNotFound()); } return(View(storeModels)); }
public HttpResponseMessage GetStoreByEmail([FromUri] StoreModels store) { string email = store.Email; if (email == null) { return(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Not found")); } List <StoreModels> stores = (from k in _db.StoreModels where k.Email == email select k).ToList(); HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, stores); return(response); }
public HttpResponseMessage GetStoreByLocation([FromUri] StoreModels store) { string name = store.Location; if (name == null) { return(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Not found")); } List <StoreModels> stores = (from k in _db.StoreModels where k.Location == name select k).ToList(); HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, stores); return(response); }
public HttpResponseMessage GetStoreByQuantity([FromUri] StoreModels store) { int?qop = store.QoP; if (qop == null) { return(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Not found")); } List <StoreModels> stores = (from k in _db.StoreModels where k.QoP == qop select k).ToList(); HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, stores); return(response); }
public HttpResponseMessage GetStoreByTelephoneNumber([FromUri] StoreModels store) { string telephone = store.Telephone; if (telephone == null) { return(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Not found")); } List <StoreModels> stores = (from k in _db.StoreModels where k.Telephone == telephone select k).ToList(); HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, stores); return(response); }
public HttpResponseMessage GetStoreByZipCode([FromUri] StoreModels store) { int?zipCode = store.ZipCode; if (zipCode == null) { return(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Not found")); } List <StoreModels> stores = (from k in _db.StoreModels where k.ZipCode == zipCode select k).ToList(); HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, stores); return(response); }
public HttpResponseMessage Get([FromUri] StoreModels store) { string name = store.Name; if (name == null) { return(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Not found")); } var storeSearch = store.Child.FirstOrDefault((p) => p.Name == name); // var abcd = store.Ascending; HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, storeSearch); return(response); }
public async Task <ActionResult> Edit(StoreModels store) { try { if (ModelState.IsValid) { //Modify store, return store await storeRepository.editStore(store); return(RedirectToAction("Index")); } return(View(store)); } catch (Exception e) { Console.WriteLine("{0} Exception caught.", e); } return(View()); }
public virtual void Update(int id, StoreModels newStore) { string sqlStr = string.Format("update m_store set s_areaKeyId="); SqlSeverProvider.ExecuteNonQuery(sqlStr); }
public ActionResult GetStoreById(int id) { StoreModels store = _StoreManager.GetById(id); return(Json(store, JsonRequestBehavior.AllowGet)); }
public ActionResult AddStore(StoreModels store) { _stores = new Stores(); if (ModelState.IsValid) { if (!Utilities.RegularExpressions.UrlIsValid(store.Url)) { ModelState.AddModelError("URL", "Invalid Url"); store.IntervalList = GenerateSelectListItems(intervalsList, store.DefaultInterval); store.OrderStatus = _stores.GetPublicOrdersStatus(); return(View(store)); } if (!Utilities.RegularExpressions.ApiKeyIsvalid(store.ApiKey)) { store.IntervalList = GenerateSelectListItems(intervalsList, store.DefaultInterval); store.OrderStatus = _stores.GetPublicOrdersStatus(); ModelState.AddModelError("ApiKey", "The API KEY must be a 32 characters long and ONLY numbers"); return(View(store)); } string[] CharList = Request.Form["rdOrderStatus"].Split(','); if (CharList.Length == 0 && store.CustomOrderStatus.Equals("")) { store.IntervalList = GenerateSelectListItems(intervalsList, store.DefaultInterval); store.OrderStatus = _stores.GetPublicOrdersStatus(); ModelState.AddModelError("OrderStatus", "You need to select at least one Order Status or create your custom one"); return(View(store)); } List <string> orderStatusList = new List <string>(); foreach (string item in CharList) { orderStatusList.Add(item); } StoreDto storeDto = new StoreDto(); storeDto.Name = store.StoreName; storeDto.ApiKey = store.ApiKey; storeDto.Url = store.Url; storeDto.Interval = int.Parse(store.DefaultInterval); string customOrderStatus = ""; if (store.CustomOrderStatus != null && !store.CustomOrderStatus.Equals("")) { string[] customStatuses = store.CustomOrderStatus.Split(';'); foreach (var stat in customStatuses) { customOrderStatus = _stores.AddCustomOrderStatus(stat).ToString(); if (!customOrderStatus.Equals("")) { orderStatusList.Add(customOrderStatus); } customOrderStatus = ""; } } int storeId = _stores.AddStore(storeDto); if (storeId != 0) { _stores.AddOrderStatusByStoreId(orderStatusList, storeId); List <string> customShipments = new List <string>(); if (store.CustomShip1 != null && !store.CustomShip1.Equals("")) { customShipments.Add(store.CustomShip1); } if (store.CustomShip2 != null && !store.CustomShip2.Equals("")) { customShipments.Add(store.CustomShip2); } if (store.CustomShip3 != null && !store.CustomShip3.Equals("")) { customShipments.Add(store.CustomShip3); } if (customShipments.Count > 0) { _stores.CreateCustomShipments(customShipments, storeId); } return(RedirectToAction("Index", "Store")); } return(RedirectToAction("Index", "Store")); } else { store.IntervalList = GenerateSelectListItems(intervalsList, store.DefaultInterval); store.OrderStatus = _stores.GetPublicOrdersStatus(); return(View(store)); } }
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); }
//Find some store public async Task <StoreModels> findStore(int?id) { StoreModels store = await _db.StoreModels.FindAsync(id); return(store); }
public List <SelectListItem> GetListStoreForMerchantExtend(string hostUrl, List <string> lstOrgId, List <MerchantExtendModel> lstMerchantExtends, ref List <StoreModels> listStoreReturn) { List <SelectListItem> listStores = new List <SelectListItem>(); List <StoreModels> listData = new List <StoreModels>(); try { GetStoreWeb2Request paraBody = new GetStoreWeb2Request(); paraBody.ListOrgID = lstOrgId; var result = (ResponseApiModels)ApiResponse.Post <ResponseApiModels>(Commons.Store_Get_Web2, null, paraBody); dynamic data = result.Data; var lstC = data["ListStore"]; var lstContent = JsonConvert.SerializeObject(lstC); listData = JsonConvert.DeserializeObject <List <StoreModels> >(lstContent); //listData.ForEach(ss => ss.HostUrlExtend = hostUrl); foreach (var item in listData) { item.HostUrlExtend = hostUrl; item.NameExtend = string.Format("{0} in {1}", item.Name, item.OrganizationName); } //for extend foreach (var item in lstMerchantExtends) { paraBody = new GetStoreWeb2Request(); paraBody.ListStoreID = item.ListStoreIds; result = (ResponseApiModels)ApiResponse.PostWithoutHostConfig <ResponseApiModels>(item.HostApiURL + "/" + Commons.Store_Get_Web2, null, paraBody); data = result.Data; lstC = data["ListStore"]; lstContent = JsonConvert.SerializeObject(lstC); List <StoreModels> tmp = JsonConvert.DeserializeObject <List <StoreModels> >(lstContent); foreach (var subitem in tmp) { subitem.HostUrlExtend = item.HostApiURL; subitem.NameExtend = string.Format("{0} in {1}", subitem.Name, subitem.OrganizationName); } //tmp.ForEach(ss => ss.HostUrlExtend = item.HostApiURL, SS); listData.AddRange(tmp); } var currentUser = (UserSession)System.Web.HttpContext.Current.Session["User"]; if (currentUser != null) { listData = listData.Where(x => currentUser.ListStoreID.Contains(x.Id)).ToList(); } listStoreReturn = listData; listStores = listData.Select(ss => new SelectListItem() { Value = ss.Id, Text = ss.Name }).ToList(); //for FJ Daily Sales report StoreModels stall14 = listData.Where(ww => ww.StoreCode == Commons.Stall14StoreCode).FirstOrDefault(); if (stall14 != null) { Commons.Stall14StoreId = stall14.Id; Commons.Stall14StoreName = stall14.Name; } return(listStores); } catch (Exception e) { _logger.Error("Get store extend list: " + e); return(listStores); } }
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); }