public void Dispose()
{
if (_service != null)
{
_service.Dispose();
}
}
private void OnConfigurationChange(BotConfiguration newConfiguration, string value)
{
SheetsService oldService = this.Service;
this.InitializeService();
oldService?.Dispose();
}
private bool disposedValue = false; // To detect redundant calls
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
service.Dispose();
}
disposedValue = true;
}
}
void Dispose(bool disposing)
{
if (IsDisposed)
{
return;
}
if (disposing)
{
_service?.Dispose();
_service = null;
_credents = null;
IsDisposed = true;
}
}
// removes or clears a row depending on the deleteRows switch setting
private void DeleteEntry(string row)
{
SheetsService service = GetServiceAccount();
if (deleteRows)
{
/* this code deletes the record (row) completely */
Request RequestBody = new Request()
{
DeleteDimension = new DeleteDimensionRequest()
{
Range = new DimensionRange()
{
SheetId = mySheetID,
Dimension = "ROWS",
StartIndex = Convert.ToInt32(row) - 1,
EndIndex = Convert.ToInt32(row)
}
}
};
List <Request> RequestContainer = new List <Request>();
RequestContainer.Add(RequestBody);
BatchUpdateSpreadsheetRequest DeleteRequest = new BatchUpdateSpreadsheetRequest();
DeleteRequest.Requests = RequestContainer;
// service.Spreadsheets.BatchUpdate(DeleteRequest, spreadsheetId);
SpreadsheetsResource.BatchUpdateRequest Deletion = new SpreadsheetsResource.BatchUpdateRequest(service, DeleteRequest, spreadsheetId);
Deletion.Execute();
/* end of delete code */
}
else
{
/* this code clears the entry */
var range = mySheet + "!A" + row + ":F" + row;
var requestBody = new ClearValuesRequest();
var deleteRequest = service.Spreadsheets.Values.Clear(requestBody, spreadsheetId, range);
var deleteReponse = deleteRequest.Execute();
}
service.Dispose();
}
private bool disposedValue = false; // To detect redundant calls
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
service.Dispose();
}
// TODO: free unmanaged resources (unmanaged objects) and override a finalizer below.
// TODO: set large fields to null.
service = null;
disposedValue = true;
}
}
/*
* private void ReadSheet()
* {
* int rowCnt = 3;
*
* SheetsService service = GetServiceAccount();
* // string range = "Inventory By Location!A" + rowCnt + ":C50";
* string range = mySheet + "!A3:C"; // I start on A3 because the sheet has blank fields in A1-A2 (throws exception)
* // string range = mySheet + "!A5:C100"; // alternate selection
* SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range);
* ValueRange response = request.Execute();
*
* IList<IList<object>> values = response.Values;
*
* System.Text.StringBuilder HTMLtable = new System.Text.StringBuilder();
* HTMLtable.AppendLine("<table style=\"width:500px; padding:5px;\">");
* HTMLtable.AppendLine("<tr><th>Row</th><th>Location</th><th>SKU</th><th>QTY</th></tr>");
* // read data until cells are null after last record which throws exception. If any cells in the sheet are blank, an exception is
* // thrown and reading stops. Make sure the sheet has no blank fields in the data being read. A blank field will stop the reading.
* // If the read does NOT return all your records, the next record in the list probably has a blank field.
*
* if (values != null && values.Count > 0)
* {
* foreach (var row in values)
* {
* try
* {
* HTMLtable.AppendLine("<tr><td>" + rowCnt++ + "</td><td>" + row[0] + "</td><td>" + row[1] + "</td><td>" + row[2] + "</td></tr>");
* }
* catch (Exception)
* {
* break; //stop reading
* }
* }
* }
* else
* {
* HTMLtable.AppendLine("<tr><th>No data found.</th></tr>");
* }
*
* HTMLtable.AppendLine("</table>");
* MyMessage.Text = HTMLtable.ToString();
*
* service.Dispose();
* }
*/
// Updates a google sheet cell (SheetField), with a new value, eg. UpdateEntry("A0","my value")
private void UpdateEntry(string SheetField, string newVal)
{
SheetsService service = GetServiceAccount();
var range = mySheet + "!" + SheetField;
var valueRange = new ValueRange();
var oblist = new List <object>()
{
newVal
};
valueRange.Values = new List <IList <object> > {
oblist
};
var updateRequest = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
var appendReponse = updateRequest.Execute();
service.Dispose();
}
// creates a new row on a google spreadsheet
private void CreateEntry()
{
SheetsService service = GetServiceAccount();
var range = mySheet + "!A:F";
var valueRange = new ValueRange();
var oblist = new List <object>()
{
LocationBox.Text, SKUbox.Text, QBox.Text, "inserted", "via", "C#"
};
valueRange.Values = new List <IList <object> > {
oblist
};
var appendRequest = service.Spreadsheets.Values.Append(valueRange, spreadsheetId, range);
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var appendReponse = appendRequest.Execute();
service.Dispose();
}
public static void Reload()
{
service?.Dispose();
timer?.Dispose();
if (!Config.sheetsEnabled)
{
return;
}
UserCredential credential;
using (FileStream stream = new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
{
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(GoogleClientSecrets.Load(stream).Secrets, scopes, "SupportBoi", CancellationToken.None, new FileDataStore("token.json", true)).Result;
Console.WriteLine("Google credential file saved to 'token.json'");
}
// Create Google Sheets API service.
service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = "SupportBoi"
});
SpreadsheetsResource.GetRequest request = service.Spreadsheets.Get(Config.spreadsheetID);
Spreadsheet spreadsheet = request.Execute();
if (spreadsheet == null)
{
Console.WriteLine();
throw new ArgumentException("ERROR: Could not find a Google Sheets Spreadsheet with provided ID.");
}
timer = new Timer(RunJobs, null, 1000, Timeout.Infinite);
}
private void doDBUpdateWork(BackgroundWorker worker, DoWorkEventArgs e)
{
int pc = 1; // progress count
SheetsService service = null;
string outputFileName = string.Empty;
bool preWorkResult = preWork(ref service, ref outputFileName, worker, e, ref pc);
if (!preWorkResult)
{
return;
}
Spreadsheets.Operator[] operators = SpreadsheetsData.Operators;
Spreadsheets.SheetsRanges allRanges = SpreadsheetsData.AllRanges;
StringBuilder sb = new StringBuilder();
sb.AppendFormat(StartFormMessages.Default.DataVerifyOutputHeaderFormat,
DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString());
sb.AppendLine();
if (worker.CancellationPending)
{
e.Cancel = true;
}
worker.ReportProgress(pc++, "Creating DB update report file...");
string reportFileName = createFileName(
Settings.Default.UpdateReportFileNameFormat,
"UpdateReportFileNameFormat", ".txt");
if (reportFileName == string.Empty)
{
return;
}
if (worker.CancellationPending)
{
e.Cancel = true;
}
worker.ReportProgress(pc++, "Creating DB file backup...");
// create db backup
if (Settings.Default.CreateDBBackup)
{
string dbBackupFileName = createFileName(
Settings.Default.DBBackupFilenameFormat,
"DBBackupFilenameFormat",
Path.GetExtension(Path.GetFileName(Settings.Default.AccessDbPath)));
if (dbBackupFileName == string.Empty)
{
return;
}
try
{
File.Copy(Settings.Default.AccessDbPath, dbBackupFileName, true);
}
catch (Exception ex)
{
worker.ReportProgress(0, string.Format(
StartFormMessages.Default.UnableCreateDBCopy,
Settings.Default.AccessDbPath,
dbBackupFileName));
LogWriter.Log(ex);
return;
}
}
// do the main job and write processing info file
if (worker.CancellationPending)
{
e.Cancel = true;
}
worker.ReportProgress(pc++, "Starting the data update work:");
try
{
ControllerUpdate.DoControll(service, operators, allRanges, sb);
}
catch (DbUpdateException dbuEx)
{
worker.ReportProgress(0,
StartFormMessages.Default.RollbackFailureError);
LogWriter.Log(dbuEx);
}
catch (Exception ex)
{
string errorMessage = StartFormMessages.Default.UnexpectedExceptionHeader + ex.Message;
worker.ReportProgress(0, errorMessage);
sb.AppendLine(errorMessage);
LogWriter.Log(ex);
}
finally
{
service.Dispose();
using (StreamWriter sw = new StreamWriter(outputFileName))
{
try
{
sw.Write(sb);
}
catch (Exception ex)
{
worker.ReportProgress(0,
string.Format(
StartFormMessages.Default.UnableWriteUpdateInfoFile,
outputFileName)
+ ex.Message);
LogWriter.Log(ex);
}
}
}
// create report
StringBuilder sbReport = ReportCreator.CreateTxt(
ControllerUpdate.GetInspections());
using (StreamWriter sw = new StreamWriter(reportFileName))
{
try
{
sw.Write(sbReport);
}
catch (Exception ex)
{
worker.ReportProgress(0,
string.Format(
StartFormMessages.Default.UnableWriteUpdateReportFile,
reportFileName)
+ ex.Message);
LogWriter.Log(ex);
return;
}
}
e.Result = string.Format(StartFormMessages.Default.UpdateDoneMessage, outputFileName);
}
public void Dispose()
{
sheetsService.Dispose();
}
private void doDataVerifyWork(BackgroundWorker worker, DoWorkEventArgs e)
{
int pc = 1; // progress count
SheetsService service = null;
string outputFileName = string.Empty;
bool preWorkResult = preWork(ref service, ref outputFileName, worker, e, ref pc);
if (!preWorkResult)
{
return;
}
Spreadsheets.Operator[] operators = SpreadsheetsData.Operators;
Spreadsheets.SheetsRanges allRanges = SpreadsheetsData.AllRanges;
StringBuilder sb = new StringBuilder();
sb.AppendFormat(StartFormMessages.Default.DataVerifyOutputHeaderFormat,
DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString());
sb.AppendLine();
// do the main job and write processing info file
if (worker.CancellationPending)
{
e.Cancel = true;
}
worker.ReportProgress(pc++, "Starting the data verify work:");
try
{
// the main work
ControllerVerify.DoControll(service, operators, allRanges, sb);
}
catch (Exception ex)
{
// all exceptions are caught and logged into sb
// this is for some unexpected exception
worker.ReportProgress(0, StartFormMessages.Default.UnexpectedExceptionHeader + ex.Message);
LogWriter.Log(ex);
return;
}
finally
{
service.Dispose();
using (StreamWriter sw = new StreamWriter(outputFileName))
{
try
{
sw.Write(sb);
}
catch (Exception wEx)
{
worker.ReportProgress(0, string.Format(
StartFormMessages.Default.UnableWriteVerifyInfoFile,
outputFileName));
LogWriter.Log(wEx);
}
}
}
e.Result = string.Format(StartFormMessages.Default.VerifyDoneMessage, outputFileName);
}
public void MyBindSheetData(string sortStr)
{
int rowCnt = 3;
DataTable dt = new DataTable();
// Define the columns of the table.
dt.Columns.Add(new DataColumn("Row", typeof(Int32)));
dt.Columns.Add(new DataColumn("Location", typeof(String)));
dt.Columns.Add(new DataColumn("SKU", typeof(String)));
dt.Columns.Add(new DataColumn("QTY", typeof(Double)));
SheetsService service = GetServiceAccount();
string range = mySheet + "!A" + rowCnt + ":C";
// I start on A3 because the sheet has blank fields in A1-A2 (throws exception)
// string range = mySheet + "!A5:C100"; // alternate selection
SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range);
ValueRange response = request.Execute();
IList <IList <object> > values = response.Values;
// read data until cells are null after last record which throws exception. If any cells in the sheet are blank, an exception is
// thrown and reading stops. Make sure the sheet has no blank fields in the data being read. A blank field will stop the reading.
// If the read does NOT return all your records, the next record in the list probably has a blank field.
if (values != null && values.Count > 0)
{
foreach (var row in values)
{
try // try is NOT needed if you limit the range to cells with NO blank fields, A5:C100 instead of A5:C
{
DataRow dr = dt.NewRow();
dr[0] = rowCnt.ToString();
dr[1] = row[0].ToString();
dr[2] = row[1].ToString();
dr[3] = row[2].ToString();
dt.Rows.Add(dr);
rowCnt++;
}
catch (Exception)
{
break; // exception thrown for blank cells or EOF
}
}
}
else
{
DataRow dr = dt.NewRow();
dr[0] = 0;
dr[1] = "No data found.";
dr[2] = "No data found.";
dr[3] = 0.0;
dt.Rows.Add(dr);
}
service.Dispose();
DataView dv = new DataView(dt); // use DataView to sort records then bind to the view instead of the table
dv.Sort = sortStr;
MySheetGrid.DataSource = dv;
// MySheetGrid.DataSource = dt; // comment this line out if using the DataView for sorting
MySheetGrid.DataBind();
dt.Dispose();
}
public async Task <int> Extract()
{
int blin = _settings.BacklinkIndex;
int dlin = _settings.DomainIndex;
string dom = _settings.SingleDomname;
if (dom != null)
{
dlin = -1;
}
SheetsService service;
string sheet = "Sheet1";
string[] Scopes = { SheetsService.Scope.Spreadsheets };
string ApplicationName = "ValidateLinks";
GoogleCredential credential;
using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
{
credential = GoogleCredential.FromStream(stream)
.CreateScoped(Scopes);
}
service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
var range = $"{sheet}!A:G";
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(_settings.OpenDataString, range);
var response = request.Execute();
IList <IList <object> > values = response.Values;
var dt = new DataTable();
if (values != null && values.Count > 0)
{
for (int i = 0; i < values.Count; i++)
{
IList <object> row = values[i];
List <object> f = row.AsEnumerable().ToList <object>();
for (int j = 0; j < f.Count; j++)
{
if (i == 0)
{
dt.Columns.Add(f[j].ToString());
}
if (i != 0)
{
if (j == 0)
{
DataRow dr = dt.NewRow();
dr[j] = f[j];
dt.Rows.Add(dr);
}
else
{
try
{
DataRow dr = dt.Rows[i - 1];
dr[j] = f[j];
}
catch
{
break;
}
}
}
}
}
}
else
{
MessageBox.Show("Error...");
}
dt.TableName = "links";
int len = dt.Rows.Count;
int clen = dt.Columns.Count;
int counter = 0;
for (int i = 0; i < len; i++)
{
if (!string.IsNullOrEmpty(dt.Rows[i][dlin].ToString()) && !string.IsNullOrEmpty(dt.Rows[i][blin].ToString()) && dt.Rows[i][blin].ToString() != "n/a")
{
var arr = dt.Rows[i].ItemArray;
DataBacklinkRow data = new DataBacklinkRow()
{
Domain = dt.Rows[i][blin].ToString(), Url = dt.Rows[i][dlin].ToString(), Position = counter
};
DataListedInfo info = new DataListedInfo()
{
DataBacklinkRowID = counter, Text = Newtonsoft.Json.JsonConvert.SerializeObject(arr.Where((x, j) => j != dlin && j != blin))
};
await _infoServ.CreateDataListedInfo(info);
await _serv.CreateDataBacklinkRow(data);
counter++;
}
}
service.Dispose();
return(counter);
}
private async Task PublishValues(CancellationToken cancellationToken)
{
var temperatures = GetTemperatures();
UserCredential credential;
using (var stream = new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
{
string credPath = "token.json";
credential = await GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
cancellationToken,
new FileDataStore(credPath, true));
_logger.LogInformation("Credential file saved to: {0}", credPath);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
string spreadsheetId = "1RCRFRNucJB-fs4jyhIlPMfHnMOIv7xx81h1oeZx0j-g";
// Temperatures
ValueRange valueRange = new ValueRange {
Values = new List <IList <object> >()
};
foreach (var temp in temperatures)
{
valueRange.Values.Add(new List <object> {
temp.Inside, temp.Outside, temp.Timestamp.ToString()
});
}
var request = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, "A1:C10");
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
await request.ExecuteAsync(cancellationToken);
// Settings
var response = await service.Spreadsheets.Values.Get(spreadsheetId, "E4:E14").ExecuteAsync(cancellationToken);
IList <IList <Object> > values = response.Values;
if (values != null && values.Count > 0)
{
_logger.LogInformation("Updating settings....");
var settings = _settingsMgmt.GetSettings();
settings.TargetTemperature = float.Parse(values[0][0].ToString().Replace(".", CultureInfo.CurrentCulture.NumberFormat.CurrencyDecimalSeparator));
settings.PollingInterval = int.Parse(values[1][0].ToString());
settings.PollingSensorsPath = values[2][0].ToString();
settings.PollingTemperatureFile = values[3][0].ToString();
settings.SensorInsideId = values[4][0].ToString();
settings.SensorOutsideId = values[5][0].ToString();
settings.PinCool = int.Parse(values[6][0].ToString());
settings.PinHeat = int.Parse(values[7][0].ToString());
settings.Diff = float.Parse(values[8][0].ToString().Replace(".", CultureInfo.CurrentCulture.NumberFormat.CurrencyDecimalSeparator));
settings.PublishInterval = int.Parse(values[9][0].ToString());
settings.ThermostatInterval = int.Parse(values[10][0].ToString());
_settingsMgmt.UpdateSettings(settings);
}
// ON-OFF
ValueRange valueOnOff = new ValueRange {
Values = new List <IList <object> >()
};
switch (_thermostatMgmt.CurrentMode)
{
case Mode.Cool:
valueOnOff.Values.Add(new List <object> {
"ON"
});
valueOnOff.Values.Add(new List <object> {
"OFF"
});
break;
case Mode.Heat:
valueOnOff.Values.Add(new List <object> {
"OFF"
});
valueOnOff.Values.Add(new List <object> {
"ON"
});
break;
default:
valueOnOff.Values.Add(new List <object> {
"OFF"
});
valueOnOff.Values.Add(new List <object> {
"OFF"
});
break;
}
request = service.Spreadsheets.Values.Update(valueOnOff, spreadsheetId, "G1:2");
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
await request.ExecuteAsync(cancellationToken);
service.Dispose();
}
public void Dispose() => service.Dispose();
