Beispiel #1
0
        private void Client_MqttMsgPublishReceived(object sender, MqttMsgPublishEventArgs e)
        {
            var now = DateTime.Now;

            //Deserialize the message into json
            var jsonText = Encoding.ASCII.GetString(e.Message);
            var message  = Newtonsoft.Json.JsonConvert.DeserializeObject <LoraObject>(jsonText);

            //Decode from Base64
            byte[] decodedPayload = Convert.FromBase64String(message.payload_raw);

            //Log
            LogDecodedPayload(now, decodedPayload, message);

            //Decode payload
            Leo5TheThingNetworkPayload convertedPayload = Leo5TheThingNetworkPayloadConverter.Convert(decodedPayload);

            //Upload it to google sheet
            GoogleSheetWriter.Write(now, message, convertedPayload);
        }
        internal static void Write(DateTime now, LoraObject msg, Leo5TheThingNetworkPayload payloadInfo)
        {
            UserCredential credential;

            using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)) //You get this credential file from Google. It's not part of public github solution.
            {
                credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
                    GoogleClientSecrets.Load(stream).Secrets,
                    Scopes,
                    "user",
                    CancellationToken.None,
                    new FileDataStore(AppConstants.CredPath, true)).Result;

                //Console.WriteLine("Credential file saved to: " + CredPath);
            }

            // Create Google Sheets API service.
            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName       = ApplicationName,
            });

            SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(AppConstants.SpreadsheetId, AppConstants.Range);

            ValueRange sheetData           = request.Execute();
            IList <IList <object> > values = sheetData.Values;


            if (values == null || values.Count == 0)
            {
                File.AppendAllText(AppConstants.LogStorageFilePath, "GoogleSheetWriter: No data found.\n");
                //todo add return after first run
                //return;
            }


            //  var lastMessage = sheetData.Values[sheetData.Values.Count - 1];

            //if (((string)lastMessage[1] == msg.Time.ToString()  ) &&
            //    ((string)lastMessage[3] == msg.FCntUp.ToString()) &&
            //    ((string)lastMessage[4] == msg.FCntDn.ToString()))
            //{
            //    return;
            //}


            Thread.CurrentThread.CurrentCulture = new CultureInfo("de-CH");

            Gateway bestSignalGateway;
            string  bestSignalGateWayEUI;

            if (msg.metadata.gateways.Count > 1)
            {
                bestSignalGateway    = msg.metadata.gateways.OrderByDescending(item => item.rssi).First();
                bestSignalGateWayEUI = bestSignalGateway.gtw_id;
            }
            else
            {
                bestSignalGateway    = msg.metadata.gateways[0];
                bestSignalGateWayEUI = bestSignalGateway.gtw_id;
            }

            string gatewayDetails = JsonConvert.SerializeObject(msg);

            var newRow = new List <object>
            {
                msg.metadata.gateways[0].time,
                now.ToString("dd.MM.yyyy HH:mm:ss"),

                payloadInfo.FirstChannel.Type.ToString(),
                payloadInfo.FirstChannel.Value.ToString(CultureInfo.CurrentCulture),
                payloadInfo.SecondChannel.Type.ToString(),
                payloadInfo.SecondChannel.Value.ToString(CultureInfo.CurrentCulture),

                payloadInfo.BatteryVoltageInMilliVolt.ToString(CultureInfo.CurrentCulture),

                msg.counter.ToString(),
                msg.metadata.gateways.Count.ToString(),
                bestSignalGateWayEUI,


                bestSignalGateway.latitude,
                bestSignalGateway.longtitude,


                gatewayDetails
            };

            if (sheetData.Values.Count > AppConstants.MaxFields)
            {
                sheetData.Values.RemoveAt(1);
            }

            sheetData.Values.Add(newRow);

            SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest =
                service.Spreadsheets.Values.Update(sheetData, AppConstants.SpreadsheetId, sheetData.Range);
            updateRequest.ValueInputOption =
                SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;

            UpdateValuesResponse updateResponse = updateRequest.Execute();

            File.AppendAllText(AppConstants.LogStorageFilePath, $"{updateResponse.UpdatedCells}, {updateResponse.UpdatedColumns}, {updateResponse.UpdatedRows}\n");
        }