/// <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();
}
