/// <summary> /// Loads values from sheet, with all info /// </summary> /// <returns>List of all food from sheet</returns> public List <Food> LoadAllFoods() { ValueRange sheetData = GoogleSheetService.GetRows(SheetId, FoodListSheet); IEnumerable <Food> foods = new List <Food>(); // Null and empty check if (!(sheetData?.Values?.Any() == true)) { return(foods.ToList()); } var foodData = sheetData.Values.Skip(1); foods = foodData.Select(item => new Food() { Name = item[0].ToString(), Description = item[1].ToString(), Price = decimal.Parse(item[2].ToString()), Type = GetFoodType(item[3].ToString()), Restaurant = Restaurant, IsInactive = false }); return(foods.ToList()); }
/// <summary> /// Populate Kasa tab /// </summary> /// <param name="customerList">List of <see cref="Customer"/></param> public void WriteKasaTab(List <Customer> customerList) { if (customerList == null) { return; } List <object> header = new List <object> { "Id", "Ime i prezime", "Suma" }; ValueRange kasaData = new ValueRange { Values = new List <IList <object> > { header } }; foreach (var customer in customerList) { kasaData.Values.Add(new List <object> { customer.Id, customer.Name, customer.Orders.Where(o => o.Meal.Foods[0].Restaurant.Name == Restaurant.Name).Sum(o => o.Price) }); } GoogleSheetService.Clear(SheetId, _kasaSheet); GoogleSheetService.Update(kasaData, SheetId, _kasaSheet); }
public override void WriteMenu(List <Food> foods) { var header = new List <object> { "Naziv jela", "Opis", "Cena", "Tip" }; var foodRange = new ValueRange { Values = new List <IList <object> > { header } }; foreach (var food in foods) { foodRange.Values.Add(new List <object> { food.Name, food.Description, food.Price, GetLocalFoodType(food.Type) }); } // Clear sheet and write new data GoogleSheetService.Clear(_sheetId, FoodListSheet); GoogleSheetService.Update(foodRange, _sheetId, FoodListSheet); }
public void CanReadData() { //Arrange var watchList = new List<StockWatchList>(); var service = new GoogleSheetService(); var credentialConfig = $"{Path.GetDirectoryName(TestContext.CurrentContext.TestDirectory)}/debug/credentials.json"; var request = new GoogleSheetRequest { Range = "Sheet1!A2:F", SpreadSheetId = "1gPXVVkqWfFsW4utuJjo-qQRblSZ6-H7CyYN8WHfUxVk", ApplicationName = "StockWatchList", Scopes = new []{ SheetsService.Scope.SpreadsheetsReadonly }, Credentials = credentialConfig }; //Act var values = service.ReadData(request); if (values != null && values.Count > 0) { //Loop each row foreach (var row in values) { watchList.Add(new StockWatchList { StockCode = row[0].ToString(), Entry = Convert.ToDecimal(row[1]), DateEntered = Convert.ToDateTime(row[5]) }); } } //Asserts Assert.That(watchList, Is.Not.Null); Assert.That(watchList.Count, Is.GreaterThan(0)); }
private IEnumerable <Food> AaMenu() { const string range = AlwaysAvailableSheet + "!A2:A1000"; ValueRange sheetData = GoogleSheetService.GetColumns(_sheetId, range); // Null and empty check if (!(sheetData?.Values?.Any() == true)) { return(new List <Food>()); } return(sheetData.Values.First().Select(f => new Food { Name = f.ToString(), Restaurant = _restaurant }).ToList()); }
public ActionResult Ingest(FormIngestionViewModel model) { GoogleSheetService srv = new GoogleSheetService(model.SpreadSheetId, Db); if (model.Button == "Ingest") { //All settings have been specified. Ready to create the form. Form form = srv.CreateForm(model); if (form != null) { Db.FormTemplates.Add(form); Db.SaveChanges(); return(RedirectToAction("Edit", new { id = form.Id })); } model.ColumnHeadings = srv.GetColumnHeadings(model.DataSheet); return(View(model)); } else { //Settings are being specified if (string.IsNullOrEmpty(model.SpreadSheetId)) { return(View(model)); } if (string.IsNullOrEmpty(model.DataSheet)) { ViewBag.SheetList = srv.GetSheetNames(); return(View(model)); } model.ColumnHeadings = srv.GetColumnHeadings(model.DataSheet); if (model.PreContextColumns.Count < model.PreContextColumnCount) { for (int i = model.PreContextColumns.Count; i < model.PreContextColumnCount; ++i) { model.PreContextColumns.Add(""); } } return(View(model)); } }
/// <summary> /// Get food from daily menu for today /// </summary> /// <returns>List of today available food</returns> private IEnumerable <Food> DailyMenu() { const string range = DailyMenuSheet + "!A3:A100"; ValueRange sheetData = GoogleSheetService.GetColumns(_sheetId, range); // Null and empty check if (!(sheetData?.Values?.Any() == true)) { return(new List <Food>()); } else { var result = sheetData.Values.First().Select(f => new Food { Name = f.ToString(), Restaurant = _restaurant }).ToList(); return(result); } }
/// <summary> /// Populate Orders tab in sheet with new order data /// </summary> /// <param name="orders">List of orders to write</param> public void PlaceOrders(List <Order> orders) { if (orders == null) { throw new ArgumentNullException(nameof(orders)); } var header = new List <object> { "Jelo", "Komada", "Cena", "Cena Ukupno", "Narucili" }; var orderRange = new ValueRange { Values = new List <IList <object> > { header } }; List <Food> listOFOrderdFood = new List <Food>(); foreach (var order in orders) { foreach (var food in order.Meal.Foods) { listOFOrderdFood.Add(food); } } var distinctFood = listOFOrderdFood.GroupBy(f => f.Id).Select(o => o.FirstOrDefault()); int rowCounter = 2; // First row with orders, used for formula foreach (var food in distinctFood) { var customerList = new List <object>(); var formatedData = new List <object>(); foreach (var order in orders) { if (order.Meal.Foods.FirstOrDefault(f => f.Name == food.Name) != null) { if (!string.IsNullOrEmpty(order.Note)) { customerList.Add(order.Customer.Name + "(" + order.Note + ")"); } else { customerList.Add(order.Customer.Name); } } } formatedData.Add(food.Name); formatedData.Add(customerList.Count); formatedData.Add(food.Price); formatedData.Add($"=B{rowCounter}*C{rowCounter}"); // Add formula to sum rowCounter++; formatedData.AddRange(customerList); orderRange.Values.Add(formatedData); } GoogleSheetService.Clear(SheetId, OrdersSheet); GoogleSheetService.Update(orderRange, SheetId, OrdersSheet); }
/// <summary> /// Setup daily menu sheet, making today first column /// </summary> public void DnevniMenuSheetSetup() { // Get data ValueRange sheetData = GoogleSheetService.GetColumns(SheetId, DailyMenuSheet); DateTime dateCounter = DateTime.Today; var sheetValues = sheetData.Values; var dayOfWeek = GetLocalDayName(dateCounter.DayOfWeek); int today = 0; for (int i = 0; i < sheetValues.Count; i++) { if (sheetValues[i][0].ToString() == dayOfWeek) { today = i; } } ValueRange updatedRange = new ValueRange { Values = new List <IList <object> >() }; const int daysToAdd = 0; // Insert today and after for (int i = today; i < sheetValues.Count; i++) { sheetValues[i][1] = dateCounter.AddDays(daysToAdd).ToString("dd-MM-yyyy"); updatedRange.Values.Add(sheetValues[i]); if (dateCounter.DayOfWeek == DayOfWeek.Friday) { dateCounter = dateCounter.AddDays(3); } else { dateCounter = dateCounter.AddDays(1); } } // Insert before today for (int k = 0; k < today; k++) { sheetValues[k][1] = dateCounter.AddDays(daysToAdd).ToString("dd-MM-yyyy"); updatedRange.Values.Add(sheetValues[k]); if (dateCounter.DayOfWeek == DayOfWeek.Friday) { dateCounter = dateCounter.AddDays(3); } else { dateCounter = dateCounter.AddDays(1); } } // Transpose values ValueRange formatedRange = new ValueRange { Values = new List <IList <object> >() }; bool empty = true; int rowNum = 0; do { empty = true; List <object> row = new List <object>(); for (int i = 0; i < updatedRange.Values.Count; i++) { if (updatedRange.Values[i].Count > rowNum) { row.Add(updatedRange.Values[i][rowNum].ToString()); empty = false; } else { row.Add(string.Empty); } } if (!empty) { formatedRange.Values.Add(row); rowNum++; } }while (!empty); GoogleSheetService.Clear(SheetId, DailyMenuSheet); GoogleSheetService.Update(formatedRange, SheetId, DailyMenuSheet); }
public HomeController() { _gService = new GoogleSheetService(); _azService = new AzurePortalService(); }