public void Process()
{
try
{
UserCredential credential = GoogleSheetUtils.CreateGoogleCredential();
// Create Google Sheets API service.
using (var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
}))
{
service.HttpClient.Timeout = TimeSpan.FromMinutes(1);
ImportMainLog(service);
ImportDayLog(service);
ImportMain2Log(service);
}
}
catch (Exception exc)
{
Message("LogProcessor.Process EXC: " + exc.Message);
}
}
private void ImportMainLog(SheetsService service)
{
Message("Main log");
string spreadsheetId = System.Configuration.ConfigurationManager.AppSettings["spreadsheetId"];
string inputPath = System.Configuration.ConfigurationManager.AppSettings["InputPath"];
string filePrefix = System.Configuration.ConfigurationManager.AppSettings["FilePrefix"];
int sheetIdFrom = int.Parse(System.Configuration.ConfigurationManager.AppSettings["From"]);
int sheetIdTo = int.Parse(System.Configuration.ConfigurationManager.AppSettings["To"]);
Message(string.Format("From {0} to {1}", sheetIdFrom, sheetIdTo));
var files = System.IO.Directory.EnumerateFiles(inputPath, filePrefix + "*.txt");
Message(string.Format("Found {0} in {1}", files.Count(), inputPath + filePrefix));
foreach (var file in files)
{
Message(string.Format("Processing {0}", file));
System.IO.FileInfo fi = new FileInfo(file);
string sheetName = fi.Name.Replace(filePrefix, "").Replace(".txt", "");
int sheetId = int.Parse(sheetName);
if (sheetId < sheetIdFrom || sheetId > sheetIdTo)
{
continue;
}
string range = sheetName + "!A:AR";
var spreadsheet = service.Spreadsheets.Get(spreadsheetId).Execute();
var sheet = spreadsheet.Sheets.FirstOrDefault(x => x.Properties.Title == sheetName);
if (sheet == null)
{
sheet = GoogleSheetUtils.CreateSheet(service, spreadsheetId, sheetName, ref spreadsheet);
}
if (sheet == null)
{
Message(string.Format("Unable to get sheet {0}", sheetName));
continue;
}
Message(string.Format("Using sheet {0}", sheetName));
string inputData;
using (var stream = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
using (var reader = new StreamReader(stream))
{
inputData = reader.ReadToEnd();
reader.Close();
}
if (string.IsNullOrEmpty(inputData))
{
Message(string.Format("File empty!"));
continue;
}
var inputLines = inputData.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
Message(string.Format("Loaded lines {0}", inputLines.Length));
var rangeValues = service.Spreadsheets.Values.Get(spreadsheetId, range).Execute();
Message(string.Format("Spreadsheets.Values.Get"));
var rangeLines = 0;
if (rangeValues.Values != null)
{
rangeLines = rangeValues.Values.Count;
}
Message(string.Format("Range lines {0}", rangeLines));
var newData = new List <IList <object> >();
for (int i = rangeLines; i < inputLines.Length; i++)
{
var splitedInputLine = inputLines[i].Split(";".ToCharArray());
var date = DateTime.ParseExact(splitedInputLine[0], "dd.MM.yy", CultureInfo.InvariantCulture);
var time = DateTime.ParseExact(splitedInputLine[1], "HH:mm", CultureInfo.InvariantCulture);
var timeOnly = time - time.Date;
var values = splitedInputLine.Skip(2).Select(x => decimal.Parse(x)).Cast <object>().ToList();
var data = new List <object>();
data.Add(splitedInputLine[0]);
data.Add(splitedInputLine[1]);
data.AddRange(values);
newData.Add(data);
}
if (newData.Count > 0)
{
Message("Sending...");
ValueRange vr = new ValueRange();
vr.Values = newData;
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(vr, spreadsheetId, range);
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
var response = request.Execute();
Message(string.Format("DONE"));
}
else
{
Message(string.Format("No new data."));
}
}
}
private void ImportDayLog(SheetsService service)
{
Message("Day log");
string spreadsheetId = System.Configuration.ConfigurationManager.AppSettings["DailySpreadsheetId"];
string inputPath = System.Configuration.ConfigurationManager.AppSettings["DailyFile"];
Message(string.Format("Processing day file {0}", inputPath));
string sheetName = "dayfile";
string range = sheetName + "!A:AT";
var spreadsheet = service.Spreadsheets.Get(spreadsheetId).Execute();
var sheet = spreadsheet.Sheets.FirstOrDefault(x => x.Properties.Title == sheetName);
if (sheet == null)
{
sheet = GoogleSheetUtils.CreateSheet(service, spreadsheetId, sheetName, ref spreadsheet);
}
if (sheet == null)
{
Message(string.Format("Unable to get sheet {0}", sheetName));
return;
}
string inputData;
using (var stream = new FileStream(inputPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
using (var reader = new StreamReader(stream))
{
inputData = reader.ReadToEnd();
reader.Close();
}
if (string.IsNullOrEmpty(inputData))
{
Message(string.Format("File empty!"));
return;
}
var inputLines = inputData.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
Message(string.Format("Loaded lines {0}", inputLines.Length));
var rangeValues = service.Spreadsheets.Values.Get(spreadsheetId, range).Execute();
Message(string.Format("Spreadsheets.Values.Get"));
var rangeLines = 0;
if (rangeValues.Values != null)
{
rangeLines = rangeValues.Values.Count;
}
Message(string.Format("Range lines {0}", rangeLines));
var newData = new List <IList <object> >();
for (int i = rangeLines; i < inputLines.Length; i++)
{
var splitedInputLine = inputLines[i].Split(";".ToCharArray());
var date = DateTime.ParseExact(splitedInputLine[0], "dd.MM.yy", CultureInfo.InvariantCulture);
// var time = DateTime.ParseExact(splitedInputLine[1], "HH:mm", CultureInfo.InvariantCulture);
// var timeOnly = time - time.Date;
//var values = splitedInputLine.Skip(2).Select(x => decimal.Parse(x)).Cast<object>().ToList();
var values = splitedInputLine.Skip(1).Select(x => x).Cast <object>().ToList();
var data = new List <object>();
data.Add(splitedInputLine[0]);
data.Add(decimal.Parse(splitedInputLine[1]));
data.Add(decimal.Parse(splitedInputLine[2]));
data.Add(splitedInputLine[3]);
data.Add(decimal.Parse(splitedInputLine[4]));
data.Add(splitedInputLine[5]);
data.Add(decimal.Parse(splitedInputLine[6]));
data.Add(splitedInputLine[7]);
data.Add(decimal.Parse(splitedInputLine[8]));
data.Add(splitedInputLine[9]);
data.Add(decimal.Parse(splitedInputLine[10]));
data.Add(splitedInputLine[11]);
data.Add(decimal.Parse(splitedInputLine[12]));
data.Add(splitedInputLine[13]);
data.Add(decimal.Parse(splitedInputLine[14]));
data.Add(decimal.Parse(splitedInputLine[15]));
data.Add(decimal.Parse(splitedInputLine[16]));
data.Add(decimal.Parse(splitedInputLine[17]));
data.Add(splitedInputLine[18]);
data.Add(decimal.Parse(splitedInputLine[19]));
data.Add(splitedInputLine[20]);
data.Add(decimal.Parse(splitedInputLine[21]));
data.Add(splitedInputLine[22]);
data.Add(decimal.Parse(splitedInputLine[23]));
data.Add(decimal.Parse(splitedInputLine[24]));
data.Add(decimal.Parse(splitedInputLine[25]));
data.Add(splitedInputLine[26]);
data.Add(decimal.Parse(splitedInputLine[27]));
data.Add(splitedInputLine[28]);
data.Add(decimal.Parse(splitedInputLine[29]));
data.Add(splitedInputLine[30]);
data.Add(decimal.Parse(splitedInputLine[31]));
data.Add(splitedInputLine[32]);
data.Add(decimal.Parse(splitedInputLine[33]));
data.Add(splitedInputLine[34]);
data.Add(decimal.Parse(splitedInputLine[35]));
data.Add(splitedInputLine[36]);
data.Add(decimal.Parse(splitedInputLine[37]));
data.Add(splitedInputLine[38]);
data.Add(decimal.Parse(splitedInputLine[39]));
data.Add(decimal.Parse(splitedInputLine[40]));
data.Add(decimal.Parse(splitedInputLine[41]));
data.Add(decimal.Parse(splitedInputLine[42]));
data.Add(splitedInputLine[43]);
data.Add(decimal.Parse(splitedInputLine[44]));
data.Add(splitedInputLine[45]);
//data.Add(splitedInputLine[1]);
//data.AddRange(values);
newData.Add(data);
}
if (newData.Count > 0)
{
Message("Sending...");
ValueRange vr = new ValueRange();
vr.Values = newData;
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(vr, spreadsheetId, range);
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
var response = request.Execute();
Message(string.Format("DONE"));
}
else
{
Message(string.Format("No new data."));
}
}
private void ImportAirDavis(SheetsService service)
{
Message("Air");
string spreadsheetId = System.Configuration.ConfigurationManager.AppSettings["AirSpreadsheetId"];
var airData = GetAirData();
if (airData == null)
{
Message("Air no data received");
return;
}
string sheetName = $"Air{DateTime.Now.Year}-{DateTime.Now.Month:00}";
string range = sheetName + "!A:AR";
var spreadsheet = service.Spreadsheets.Get(spreadsheetId).Execute();
var sheet = spreadsheet.Sheets.FirstOrDefault(x => x.Properties.Title == sheetName);
if (sheet == null)
{
sheet = GoogleSheetUtils.CreateSheet(service, spreadsheetId, sheetName, ref spreadsheet);
}
if (sheet == null)
{
Message(string.Format("Unable to get sheet {0}", sheetName));
return;
}
Message(string.Format("Using sheet {0}", sheetName));
var rangeValues = service.Spreadsheets.Values.Get(spreadsheetId, range).Execute();
var newData = new List <IList <object> >();
if (rangeValues.Values == null)
{
var header = new List <object>();
header.Add("Date");
header.Add("Time");
header.Add("Temp");
header.Add("Hum");
header.Add("PM 1");
header.Add("PM 2.5");
header.Add("PM 2.5 Last 1 Hr");
header.Add("PM 10");
header.Add("PM 10 Last 1 Hr");
header.Add("AQI");
header.Add("1 Hour AQI");
newData.Add(header);
}
var airDataRecord = airData[0];
var received = DateTimeOffset.FromUnixTimeMilliseconds(airDataRecord.lastReceived.Value).ToLocalTime();
var data = new List <object>();
data.Add(received.ToString("dd.MM.yyyy"));
data.Add(received.ToString("HH:mm"));
data.Add(GetDecimalFromJson(airDataRecord.currConditionValues.First(x => x.sensorDataName == "Temp").value));
data.Add(GetDecimalFromJson(airDataRecord.currConditionValues.First(x => x.sensorDataName == "Hum").value));
data.Add(GetDecimalFromJson(airDataRecord.currConditionValues.First(x => x.sensorDataName == "PM 1").value));
data.Add(GetDecimalFromJson(airDataRecord.currConditionValues.First(x => x.sensorDataName == "PM 2.5").value));
data.Add(GetDecimalFromJson(airDataRecord.currConditionValues.First(x => x.sensorDataName == "PM 2.5 Last 1 Hr").value));
data.Add(GetDecimalFromJson(airDataRecord.currConditionValues.First(x => x.sensorDataName == "PM 10").value));
data.Add(GetDecimalFromJson(airDataRecord.currConditionValues.First(x => x.sensorDataName == "PM 10 Last 1 Hr").value));
data.Add(GetDecimalFromJson(airDataRecord.currConditionValues.First(x => x.sensorDataName == "AQI").value));
data.Add(GetDecimalFromJson(airDataRecord.currConditionValues.First(x => x.sensorDataName == "1 Hour AQI").value));
newData.Add(data);
if (newData.Count > 0)
{
Message("Sending...");
ValueRange vr = new ValueRange();
vr.Values = newData;
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(vr, spreadsheetId, range);
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
var response = request.Execute();
Message(string.Format("DONE"));
}
else
{
Message(string.Format("No new data."));
}
}
