internal Dictionary <string, DatabaseRow> FetchAllDatabaseRowsFromSpreadsheet(string familyNamesColumn)
{
var familyNames = SpreadsheetHandler.GetValuesFromRange(_service, _spreadsheet.Id, $"{_spreadsheet.DatabaseSheet.Name}!{familyNamesColumn}:{familyNamesColumn}");
var characterNames = SpreadsheetHandler.GetValuesFromRange(_service, _spreadsheet.Id, $"{_spreadsheet.DatabaseSheet.Name}!{familyNamesColumn}:{familyNamesColumn}");
if (familyNames.Count == 0)
{
return(null);
}
var rowDictionary = new Dictionary <string, DatabaseRow>();
for (var i = 0; i < familyNames.Count; i++)
{
try
{
var familyName = familyNames[i][0].ToString();
var characterName = characterNames[i][0].ToString();
var member = new DatabaseMember(familyName, characterName);
var databaseRow = new DatabaseRow(i + 1, member);
rowDictionary.TryAdd(familyName, databaseRow);
}
catch { continue; }
}
_spreadsheet.DatabaseSheet.DatabaseRows = rowDictionary;
return(rowDictionary);
}
//TODO: How do I make this proper async
internal async void Signup(string familyName, string value, string signupMessage)
{
var row = _spreadsheet.DatabaseSheet.DatabaseRows.GetValueOrDefault(familyName);
if (row == null)
{
return;
}
string columnHeader = null;
var weekDay = new Weekday(DayEnum.Sunday);
do
{
if (signupMessage.IndexOf(weekDay.ToString()) > -1)
{
columnHeader = weekDay.ToString();
}
weekDay.NextDay();
} while (weekDay.Day != DayEnum.Sunday);
if (columnHeader == null)
{
return;
}
var column = _spreadsheet.DatabaseSheet.DatabaseColumns.GetValueOrDefault(columnHeader);
if (column == null)
{
return;
}
SpreadsheetHandler.UpdateCell(_service, _spreadsheet.Id, value, $"{_spreadsheet.DatabaseSheet.Name}!{column.ColumnLetters}{row.RowNumber}");
}
internal void ClearColumns(Dictionary <string, DatabaseColumn> columnsToReset)
{
foreach (var i in columnsToReset)
{
var column = i.Value;
SpreadsheetHandler.ClearRange(_service, _spreadsheet.Id, $"{_spreadsheet.DatabaseSheet.Name}!{column.ColumnLetters}{_spreadsheet.DatabaseSheet.ColumnHeadersRow + 2}:{column.ColumnLetters}");
}
}
internal IList <IList <object> > GetColumnValues(DatabaseSheet sheet, string column)
{
var values = SpreadsheetHandler.GetValuesFromRange(_service, _spreadsheet.Id, $"{sheet.Name}!{column}:{column}");
if (values.Count == 0)
{
Console.WriteLine("No data in range found");
return(null);
}
return(values);
}
internal DatabaseField PopulateFieldWithTheirSpreadsheetValue(SheetsService service, string spreadsheetId, DatabaseField databaseField)
{
var values = SpreadsheetHandler.GetValuesFromRange(service, spreadsheetId, databaseField.CellReference);
if (values == null)
{
return(null);
}
var flattenedValues = FlattenSpreadsheetValues(values);
databaseField.CellValue = flattenedValues.FirstOrDefault();
return(databaseField);
}
internal IList <string> GetRowValues(DatabaseSheet sheet, int row)
{
var values = SpreadsheetHandler.GetValuesFromRange(_service, _spreadsheet.Id, $"{sheet.Name}!{row}:{row}");
if (values.Count == 0)
{
Console.WriteLine("No data in range found");
return(null);
}
var result = values.SelectMany(i => i).ToList();
var stringResults = result.Select(r => r.ToString()).ToList();
return(stringResults);
}
internal bool RemoveMember(string familyName)
{
var row = _spreadsheet.DatabaseSheet.DatabaseRows.GetValueOrDefault(familyName);
if (row == null)
{
return(false);
}
SpreadsheetHandler.DeleteRow(_service, _spreadsheet.Id, row.RowNumber);
//Update rows
_supportMethods.FetchAllDatabaseRowsFromSpreadsheet(_spreadsheet.DatabaseSheet.FamilyNamesColumn);
return(true);
}
internal List <object> GetPartyMembers(string partyName)
{
var column = _spreadsheet.PartiesSheet.DatabaseColumns.GetValueOrDefault(partyName);
if (column == null)
{
return(null);
}
var partyMembers = SpreadsheetHandler.GetValuesFromRange(_service, _spreadsheet.Id, $"{_spreadsheet.PartiesSheet}!{column.ColumnLetters}{_spreadsheet.PartiesSheet.ColumnHeadersRow + 1}:{column.ColumnLetters}");
var result = partyMembers.SelectMany(i => i).ToList();
return(result);
}
internal DatabaseMember AddMember(string familyName, string characterName)
{
//TODO: throw?
if (_supportMethods.MemberExists(familyName))
{
return(null);
}
var member = new DatabaseMember(familyName, characterName);
var values = _supportMethods.ParseToConsecutiveSpreadsheetValues(new List <string> {
familyName, characterName
});
SpreadsheetHandler.NewMember(_service, _spreadsheet.Id, _spreadsheet.DatabaseSheet, values);
//Update rows
_supportMethods.FetchAllDatabaseRowsFromSpreadsheet(_spreadsheet.DatabaseSheet.FamilyNamesColumn);
return(member);
}
internal Dictionary <string, DatabaseField> PopulateConsecutiveFieldsWithTheirSpreadsheetValues(SheetsService service, string spreadsheetId, Dictionary <string, DatabaseField> fields)
{
var sheet = fields.FirstOrDefault().Value.Sheet;
var startColumn = fields.FirstOrDefault().Value.Column;
var endColumn = fields.LastOrDefault().Value.Column;
var row = fields.FirstOrDefault().Value.Row;
var values = SpreadsheetHandler.GetValuesFromRange(service, spreadsheetId, $"{sheet.Name}!{startColumn.ColumnLetters}{row.RowNumber}:{endColumn.ColumnLetters}{row.RowNumber}");
if (values == null)
{
return(null);
}
var flattenedValues = FlattenSpreadsheetValues(values);
for (var i = 0; i < flattenedValues.Count(); i++)
{
fields.Values.ElementAt(i).CellValue = flattenedValues[i];
}
return(fields);
}
internal void UpdateField(DatabaseField field)
{
SpreadsheetHandler.UpdateCell(_service, _spreadsheet.Id, field.CellValue, field.CellReference);
}
//TODO: Better system for "string addOrRemove"
internal DatabaseField ChangeActivity(string familyName, string columnHeader, string addOrRemove)
{
var row = _spreadsheet.DatabaseSheet.DatabaseRows.GetValueOrDefault(familyName);
if (row == null)
{
return(null);
}
var column = _supportMethods
.GetColumnsOfSection(_spreadsheet.DatabaseSheet.DatabaseColumns, DatabaseColumn.ColumnSectionEnum.Activity)
.GetValueOrDefault(columnHeader);
if (column == null)
{
return(null);
}
var cellOldValue = SpreadsheetHandler.GetValuesFromRange(_service, _spreadsheet.Id, $"{_spreadsheet.DatabaseSheet.Name}!{column.ColumnLetters}{row.RowNumber}");
var field = new DatabaseField(_spreadsheet.DatabaseSheet, column, row);
int newValue;
if (addOrRemove == "add")
{
if (cellOldValue == null)
{
newValue = 1;
}
else
{
newValue = Convert.ToInt32(cellOldValue[0][0]) + 1;
}
field.CellValue = newValue.ToString();
}
if (addOrRemove == "remove")
{
if (cellOldValue == null)
{
return(null);
}
else
{
newValue = Convert.ToInt32(cellOldValue[0][0]) - 1;
}
//If 0 put blank
if (newValue == 0)
{
field.CellValue = "";
}
else
{
field.CellValue = newValue.ToString();
}
}
SpreadsheetHandler.UpdateCell(_service, _spreadsheet.Id, field.CellValue, $"{_spreadsheet.DatabaseSheet.Name}!{column.ColumnLetters}{row.RowNumber}");
column = _spreadsheet.DatabaseSheet.DatabaseColumns.GetValueOrDefault("Activity last updated");
if (column == null)
{
return(null);
}
SpreadsheetHandler.UpdateCell(_service, _spreadsheet.Id, DateTime.Now.ToString(), $"{_spreadsheet.DatabaseSheet.Name}!{column.ColumnLetters}{row.RowNumber}");
return(field);
}