Cubecell Sketch for Lorawan

#include "LoRaWan_APP.h"
#include "Arduino.h"
#include <OneWire.h>
OneWire  ds(GPIO5);  // on pin GPIO1 PIN 6 (a 4.7K resistor is necessary)  for the temperature sensor
/*
   set LoraWan_RGB to Active,the RGB active in loraWan
   RGB red means sending;
   RGB purple means joined done;
   RGB blue means RxWindow1;
   RGB yellow means RxWindow2;
   RGB green means received done;
*/


//OTAA parameters (check here https://account.thethingsnetwork.org/)
uint8_t devEui[] = { 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 };
uint8_t appEui[] = { 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 };
uint8_t appKey[] = {  0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 };



/* ABP para*/
uint8_t nwkSKey[] = { 0x15, 0xb1, 0xd0, 0xef, 0xa4, 0x63, 0xdf, 0xbe, 0x3d, 0x11, 0x18, 0x1e, 0x1e, 0xc7, 0xda, 0x85 };
uint8_t appSKey[] = { 0xd7, 0x2c, 0x78, 0x75, 0x8c, 0xdc, 0xca, 0xbf, 0x55, 0xee, 0x4a, 0x77, 0x8d, 0x16, 0xef, 0x67 };
uint32_t devAddr =  ( uint32_t )0x007e6ae1;

/*LoraWan channelsmask, default channels 0-7*/
uint16_t userChannelsMask[6] = { 0x00FF, 0x0000, 0x0000, 0x0000, 0x0000, 0x0000 };

/*LoraWan region, select in arduino IDE tools*/
LoRaMacRegion_t loraWanRegion = ACTIVE_REGION;

/*LoraWan Class, Class A and Class C are supported*/
DeviceClass_t  loraWanClass = LORAWAN_CLASS;

/*the application data transmission duty cycle.  value in [ms].*/
uint32_t appTxDutyCycle = 60000;

/*OTAA or ABP*/
bool overTheAirActivation = LORAWAN_NETMODE;

/*ADR enable*/
bool loraWanAdr = LORAWAN_ADR;

/* set LORAWAN_Net_Reserve ON, the node could save the network info to flash, when node reset not need to join again */
bool keepNet = LORAWAN_NET_RESERVE;

/* Indicates if the node is sending confirmed or unconfirmed messages */
bool isTxConfirmed = LORAWAN_UPLINKMODE;

/* Application port */
uint8_t appPort = 2;
/*!
  Number of trials to transmit the frame, if the LoRaMAC layer did not
  receive an acknowledgment. The MAC performs a datarate adaptation,
  according to the LoRaWAN Specification V1.0.2, chapter 18.4, according
  to the following table:

  Transmission nb | Data Rate
  ----------------|-----------
  1 (first)       | DR
  2               | DR
  3               | max(DR-1,0)
  4               | max(DR-1,0)
  5               | max(DR-2,0)
  6               | max(DR-2,0)
  7               | max(DR-3,0)
  8               | max(DR-3,0)

  Note, that if NbTrials is set to 1 or 2, the MAC will not decrease
  the datarate, in case the LoRaMAC layer did not receive an acknowledgment
*/
uint8_t confirmedNbTrials = 4;


uint32_t packet_number = 0;


byte msb ;
byte lsb;

uint32_t battery_voltage = 0;
int temperature;

void setup() {
  Serial.begin(115200);
  enableAt();
  deviceState = DEVICE_STATE_INIT;
  LoRaWAN.ifskipjoin();

  Serial.println("Hello");
}

void loop()
{
  switch ( deviceState )
  {
    case DEVICE_STATE_INIT:
      {
        getDevParam();
        printDevParam();
        LoRaWAN.init(loraWanClass, loraWanRegion);
        deviceState = DEVICE_STATE_JOIN;
        break;
      }

    case DEVICE_STATE_JOIN:
      {
        LoRaWAN.join();
        break;
      }

    case DEVICE_STATE_SEND:
      {
        prepareTxFrame( appPort );
        LoRaWAN.send();
        deviceState = DEVICE_STATE_CYCLE;
        break;
      }

    case DEVICE_STATE_CYCLE:
      {
        // Schedule next packet transmission
        txDutyCycleTime = appTxDutyCycle; // here we could add a random number
        Serial.println("");
        Serial.print("Going into sleep mode for ");
        Serial.print(txDutyCycleTime / 1000);
        Serial.println(" seconds");
        Serial.println("");
        Serial.print("Packet number ");
        Serial.println(packet_number);
        delay(1000); // to give the time to print the above
        LoRaWAN.cycle(txDutyCycleTime);
        deviceState = DEVICE_STATE_SLEEP;
        break;
      }

    case DEVICE_STATE_SLEEP:
      {
        LoRaWAN.sleep();
        break;
      }

    default:
      {
        deviceState = DEVICE_STATE_INIT;
        break;
      }
  }
}

/* Prepares the payload of the frame */
static void prepareTxFrame( uint8_t port )
{
  /*appData size is LORAWAN_APP_DATA_MAX_SIZE which is defined in "commissioning.h".
    appDataSize max value is LORAWAN_APP_DATA_MAX_SIZE.
    if enabled AT, don't modify LORAWAN_APP_DATA_MAX_SIZE, it may cause system hanging or failure.
    if disabled AT, LORAWAN_APP_DATA_MAX_SIZE can be modified, the max value is reference to lorawan region and SF.
    for example, if use REGION_CN470,
    the max value for different DR can be found in MaxPayloadOfDatarateCN470 refer to DataratesCN470 and BandwidthsCN470 in "RegionCN470.h".
  */
  appDataSize = 6; //appDataSize is 128
  //encode temperature
  get_temperature();
  msb = (temperature) >> 8;
  lsb = temperature & 0x00FF;
  appData[0] = msb;
  appData[1] = lsb;

  //encode packet_number
  packet_number = packet_number + 1;
  msb = (packet_number) >> 8;
  lsb = packet_number & 0x00FF;
  appData[2] = msb;
  appData[3] = lsb;

  //encode Battery voltage
  battery_voltage = BoardGetBatteryVoltage();
  msb = (battery_voltage) >> 8;
  lsb = battery_voltage & 0x00FF;
  appData[4] = msb;
  appData[5] = lsb;
}

// TEMPERATURE
void get_temperature() {
  Serial.println("Getting temperature");
  byte i;
  byte present = 0;
  byte type_s;
  byte data[12];
  byte addr[8];


  while ( !ds.search(addr)) {
    ds.reset_search();
    delay(250);
  }
  ds.reset();
  ds.select(addr);
  ds.write(0x44, 1);        // start conversion, with parasite power on at the end
  delay(1000);     // maybe 750ms is enough, maybe not
  // we might do a ds.depower() here, but the reset will take care of it.
  present = ds.reset();
  ds.select(addr);
  ds.write(0xBE);         // Read Scratchpad
  for ( i = 0; i < 9; i++) {           // we need 9 bytes
    data[i] = ds.read();
  }
  int16_t raw = (data[1] << 8) | data[0];
  raw = raw << 3; // 9 bit resolution default
  if (data[7] == 0x10) {
    // "count remain" gives full 12 bit resolution
    raw = (raw & 0xFFF0) + 12 - data[6];
  }
  temperature = (float)raw / 16.0 * 10;
  Serial.print("Temperature x 10 = ");
  Serial.print(temperature);
  Serial.println(" Celsius, ");

}

/*  get the BatteryVoltage in mV. */
uint32_t BoardGetBatteryVoltage(void)
{
  float temp = 0;
  uint16_t volt;
  uint8_t pin;
  pin = ADC;
#if defined(CubeCell_Board) || defined(CubeCell_Capsule) || defined(CubeCell_BoardPlus) || defined(CubeCell_BoardPRO) || defined(CubeCell_GPS) || defined(CubeCell_HalfAA)
  /*
     have external 10K VDD pullup resistor
     connected to VBAT_ADC_CTL pin
  */
  pinMode(VBAT_ADC_CTL, OUTPUT);
  digitalWrite(VBAT_ADC_CTL, LOW);
#endif
  for (int i = 0; i < 50; i++) // read 50 times and get average
    temp += analogReadmV(pin);
  volt = temp / 50;
#if defined(CubeCell_Board) || defined(CubeCell_Capsule) || defined(CubeCell_BoardPlus) || defined(CubeCell_BoardPRO) || defined(CubeCell_GPS) || defined(CubeCell_HalfAA)
  pinMode(VBAT_ADC_CTL, INPUT);
#endif
  volt = volt * 2;
  return volt;
}

Python Code

#!/usr/bin/python3
# -*- coding: utf-8 -*-
import dateutil.parser as dp
import time
import datetime
import mysql.connector

db_user="root"
db_password="g@x6^.3Qa.wYgF" # dummy password
db_host="localhost"


global previous_seconds_since_epoch

previous_seconds_since_epoch = int(time.time())


# https://www.thethingsnetwork.org/forum/t/a-python-program-to-listen-to-your-devices-with-mqtt/9036/6
# Get data from MQTT server
# Run this with python 3, install paho.mqtt prior to use

import paho.mqtt.client as mqtt
import json

JoinEUI = "0000000000000000"
Application_ID = "my-cubecell-application"
API_Key = "NNSXS.FAKE6JK6RT7VB4DOQWXBQMKP34V3RYKIBXBFBA.7GE3SLF5KRV5BIRPH5CAKLZWLATD5YQSMIWQ7OUZEOKTVCY6PJZQ"


# gives connection message
def on_connect(mqttc, mosq, obj, rc):
    print("Connected with result code:" + str(rc))
    # subscribe for all devices of user
    #mqttc.subscribe('+/devices/+/up')
    mqttc.subscribe('v3/+/devices/+/up')

# gives message from device
def on_message(mqttc, obj, msg):
    global previous_seconds_since_epoch
    try:
       # print(msg.payload)
        x = json.loads(msg.payload.decode('utf-8'))
        #print(json.dumps(x, indent=1, sort_keys=True))
        # ISO 8601 UTC timestamp at which the message has been received by the Application Server
        received_at = x["received_at"]
        Received_at_ISO =received_at
        print (received_at)
        parsed_t = dp.parse(received_at)
        # print (parsed_t)
        seconds_since_epoch = parsed_t.timestamp()
        Received_at_epoch=seconds_since_epoch
        print (seconds_since_epoch)
        print(datetime.datetime.fromtimestamp(seconds_since_epoch))
        temp = datetime.datetime.fromtimestamp(seconds_since_epoch).strftime('%H:%M:%S')
        print("Time: ", temp)
        delta_t = round(seconds_since_epoch - previous_seconds_since_epoch, 2)
        print("Seconds since last message:", delta_t)
        previous_seconds_since_epoch = seconds_since_epoch


        uplink_message=x["uplink_message"]['decoded_payload']['data']
        #print (uplink_message)
        Battery=uplink_message['battery']
        print ("Battery: ", Battery)
        Counter=uplink_message['counter']
        print ("Counter: ",Counter)
        Temperature=uplink_message['temperature']
        print ("temperature: ", Temperature, " Celsius")
        print("")
        mydb = Connect_To_Database("cubecell_grenier")
        mycursor = mydb.cursor()
        sql = "INSERT INTO Cubecell_in_grenier (Received_at_ISO,Received_at_epoch,Battery,Temperature,Counter) VALUES (%s, %s,%s,%s, %s)"
        val = (Received_at_ISO,Received_at_epoch,Battery,Temperature,Counter)
        mycursor.execute(sql, val)
        mydb.commit()
        mydb.close()
        print("packet written to local database")


    except Exception as e:
        print(e)
        pass

def on_publish(mosq, obj, mid):
    print("mid: " + str(mid))

def on_subscribe(mosq, obj, mid, granted_qos):
    print("Subscribed: " + str(mid) + " " + str(granted_qos))

def on_log(mqttc, obj, level, buf):
    print("message:" + str(buf))
    print("userdata:" + str(obj))


def Create_Local_Data_Base(local_database_name):
    mydb = mysql.connector.connect(
        host=db_host,
        user=db_user,
        password=db_password,
    )
    mycursor = mydb.cursor()
    mycursor.execute("CREATE DATABASE " + local_database_name)
    mydb.close()

    mydb = mysql.connector.connect(
        host=db_host,
        user=db_user,
        password=db_password,
        database=local_database_name
    )
    mycursor = mydb.cursor()

    # https://www.w3schools.com/sql/sql_datatypes.asp

    mycursor.execute("CREATE TABLE Cubecell_in_grenier (\
                  Received_at_ISO  TINYTEXT,\
                  Received_at_epoch INT,\
                  Battery DECIMAL(5, 3),\
                  Temperature DECIMAL(3, 1),\
                  Counter INT)")
    print("Database " + local_database_name + " successfully created")
    mydb.close()
def Delete_Local_Data_Base(local_database_name):
    mydb = mysql.connector.connect(
        host=db_host,
        user=db_user,
        password=db_password,
    )
    mycursor = mydb.cursor()
    try:
        mycursor.execute("DROP DATABASE " + local_database_name)
        print("Database " + local_database_name + " successfully deleted")
    except:
        print("Database " + local_database_name + " does not exist, nothing deleted")
    mydb.close()
def Connect_To_Database(local_database_name):
    mydb = mysql.connector.connect(
        host="127.0.0.1",
        user=db_user,
        password=db_password,
        database=local_database_name
    )
    return mydb

#Create_Local_Data_Base("cubecell_grenier")
#Delete_Local_Data_Base("cubecell_grenier")

mqttc = mqtt.Client()
# Assign event callbacks
mqttc.on_connect = on_connect
mqttc.on_message = on_message

mqttc.username_pw_set(Application_ID, API_Key)
mqttc.connect("eu1.cloud.thethings.network", 1883, 60)

# and listen to server
run = True
while run:
    mqttc.loop()